using System;
using CallCenter.IDAL;
using CallCenter.Modules;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Collections;
using System.Text;
using System.IO;

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 
	/// </summary>
	public class DBCarku:ICarku
	{
		public DBCarku()
		{
			// 
			// TODO: 在此處添加構造函數邏輯
			//
		}
	
		#region ICarku 成員

		public int addCarku(CarkuInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
		
			//判斷出廠編號是否重復
			if(info.ccbh.Equals(this.getCarkuByCcbh(info.ccbh).ccbh))
			{
				return -1;
			}
		
			string addSql = "INSERT INTO carku(ccbh,state,ys,pp,clxz,ggxh,fdjxh,fdjhm,xxrq,scdw,rkrq,bz) VALUES(@ccbh,@state,@ys,@pp,@clxz,@ggxh,@fdjxh,@fdjhm,@xxrq,@scdw,@rkrq,@bz)";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@ccbh",SqlDbType.VarChar,100),
					new SqlParameter("@state",SqlDbType.VarChar,50),
					new SqlParameter("@ys",SqlDbType.VarChar,50),
					new SqlParameter("@pp",SqlDbType.VarChar,100),
					new SqlParameter("@clxz",SqlDbType.VarChar,100),
					new SqlParameter("@ggxh",SqlDbType.VarChar,200),
					new SqlParameter("@fdjxh",SqlDbType.VarChar,100),
					new SqlParameter("@fdjhm",SqlDbType.VarChar,100),
					new SqlParameter("@xxrq",SqlDbType.DateTime,8),
				new SqlParameter("@scdw",SqlDbType.VarChar,200),
				new SqlParameter("@rkrq",SqlDbType.DateTime,8),
				new SqlParameter("@bz",SqlDbType.VarChar,2000),
			};
			param[0].Value = info.ccbh;
			param[1].Value = info.state;
			param[2].Value = info.ys;
			param[3].Value = info.pp;
			param[4].Value = info.clxz;
			param[5].Value = info.ggxh;
			param[6].Value = info.fdjxh;
			param[7].Value = info.fdjhm;
			param[8].Value = info.xxrq;
			param[9].Value = info.scdw;
			param[10].Value = info.rkrq;
			param[11].Value = info.bz;

			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
			return 1;
		}

		public int updateCarkuState(string ccbh)
		{
			string updateSql = "UPDATE Carku SET state='已售' WHERE ccbh=@ccbh";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@ccbh",SqlDbType.VarChar,100),
			};
			param[0].Value = ccbh;
			try
			{
				return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}
		public int updateCarku(CarkuInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
				
			string updateSql = "UPDATE Carku SET state=@state,ys=@ys,pp=@pp,clxz=@clxz,ggxh=@ggxh,fdjxh=@fdjxh,fdjhm=@fdjhm,xxrq=@xxrq,scdw=@scdw,rkrq=@rkrq,bz=@bz WHERE ccbh=@ccbh";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@ccbh",SqlDbType.VarChar,100),
					new SqlParameter("@state",SqlDbType.VarChar,50),
					new SqlParameter("@ys",SqlDbType.VarChar,50),
					new SqlParameter("@pp",SqlDbType.VarChar,100),
					new SqlParameter("@clxz",SqlDbType.VarChar,100),
					new SqlParameter("@ggxh",SqlDbType.VarChar,200),
					new SqlParameter("@fdjxh",SqlDbType.VarChar,100),
					new SqlParameter("@fdjhm",SqlDbType.VarChar,100),
					new SqlParameter("@xxrq",SqlDbType.DateTime,8),
					new SqlParameter("@scdw",SqlDbType.VarChar,200),
					new SqlParameter("@rkrq",SqlDbType.DateTime,8),
					new SqlParameter("@bz",SqlDbType.VarChar,2000),
			};
			param[0].Value = info.ccbh;
			param[1].Value = info.state;
			param[2].Value = info.ys;
			param[3].Value = info.pp;
			param[4].Value = info.clxz;
			param[5].Value = info.ggxh;
			param[6].Value = info.fdjxh;
			param[7].Value = info.fdjhm;
			param[8].Value = info.xxrq;
			param[9].Value = info.scdw;
			param[10].Value = info.rkrq;
			param[11].Value = info.bz;

			try
			{
				return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}


		public int delCarku(string ccbhs)
		{
			string delSql = "DELETE FROM carku WHERE ccbh IN('"+ccbhs.Replace("'","''").Replace(",","','")+"')";
			
			try
			{
				int val=OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,delSql,null);
				return val;
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		

		public CarkuInfo getCarkuByCcbh(string ccbh)
		{
			CarkuInfo info = new CarkuInfo();
			string selSql = "SELECT ccbh,state,ys,pp,clxz,ggxh,fdjxh,fdjhm,xxrq,scdw,rkrq,bz FROM carku WHERE ccbh=@ccbh";
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@ccbh",SqlDbType.VarChar,100),
			};
			param[0].Value = ccbh;
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.ccbh = dr.IsDBNull(0)?"":dr.GetString(0);
					info.state = dr.IsDBNull(1)?"":dr.GetString(1);
					info.ys = dr.IsDBNull(2)?"":dr.GetString(2);
					info.pp = dr.IsDBNull(3)?"":dr.GetString(3);
					info.clxz = dr.IsDBNull(4)?"":dr.GetString(4);
					info.ggxh = dr.IsDBNull(5)?"":dr.GetString(5);
					info.fdjxh = dr.IsDBNull(6)?"":dr.GetString(6);
					info.fdjhm = dr.IsDBNull(7)?"":dr.GetString(7);
					info.xxrq = dr.IsDBNull(8)?DateTime.Today:dr.GetDateTime(8);
					info.scdw = dr.IsDBNull(9)?"":dr.GetString(9);
					info.rkrq = dr.IsDBNull(10)?DateTime.Today:dr.GetDateTime(10);
					info.bz = dr.IsDBNull(11)?"":dr.GetString(11);

				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

	
		public DataSet getALLCarku_DataSet(string tableName)
		{
			string selSql = "SELECT ccbh,state,ys,pp,clxz,ggxh,fdjxh,fdjhm,xxrq,scdw,rkrq,bz FROM carku";
			try
			{
				//return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
				return OraHelper.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		public DataSet getDic_DataSet(string tableName,string id,string name)
		{
			string selSql = "SELECT "+id+","+name+" FROM "+tableName;
			
			try
			{
				return OraHelper.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		public int getCount(string state)
		{
			string selSql = "SELECT count(*) From carku WHERE state=@state";
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@state",SqlDbType.VarChar,50),
			};
			param[0].Value = state;
			try
			{
				int i = Convert.ToInt32(OraHelper.SqlserverExecuteScalar(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param));
				return i;
				
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		#endregion
	}
}