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 DBXsxx:IXsxx
	{
		public DBXsxx()
		{
			// 
			// TODO: 在此處添加構造函數邏輯
			//
		}
	
		#region IXsxx 成員
		public int getFphCount(string fph)
		{
			XsxxInfo info = new XsxxInfo();
			string selSql = "SELECT count(*) From xsxx WHERE fph=@fph";
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@fph",SqlDbType.VarChar,50),
			};
			param[0].Value = fph;
			try
			{
				int i = Convert.ToInt32(OraHelper.SqlserverExecuteScalar(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param));
				return i;
				
			}
			catch(Exception e)
			{
				throw e;
			}
		}

		public int addXsxx(XsxxInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
		
			//判斷發票號是否重復
			if(getFphCount(info.fph)>0)
			{
				return -1;
			}
		
			string addSql = "INSERT INTO xsxx(carku_ccbh,grxx_id,dwxx_id,jg,fph,clgz,xsrq,bz) VALUES(@carku_ccbh,@grxx_id,@dwxx_id,@jg,@fph,@clgz,@xsrq,@bz)";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@carku_ccbh",SqlDbType.VarChar,50),
					new SqlParameter("@grxx_id",SqlDbType.Int,4),
					new SqlParameter("@dwxx_id",SqlDbType.Int,4),
					new SqlParameter("@jg",SqlDbType.Decimal,9),
					new SqlParameter("@fph",SqlDbType.VarChar,50),
					new SqlParameter("@clgz",SqlDbType.VarChar,50),
					new SqlParameter("@xsrq",SqlDbType.DateTime,8),
					new SqlParameter("@bz",SqlDbType.VarChar,2000),
			};
			param[0].Value = info.carkuccbh;
			param[1].Value = info.grxxid;
			param[2].Value = info.dwxxid;
			param[3].Value = info.jg;
			param[4].Value = info.fph;
			param[5].Value = info.clgz;
			param[6].Value = info.xsrq;
			param[7].Value = info.bz;
			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
			return 1;
		}

		public int updateXsxx(XsxxInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
				
			//判斷身份證號是否重復
			//			if(info.sfzh.Equals(this.getGrxxByid(info.id).sfzh))
			//			{
			//				return -1;
			//			}
			string updateSql = "UPDATE Xsxx SET carku_ccbh=@carku_ccbh,grxx_id=@grxx_id,dwxx_id=@dwxx_id,jg=@jg,fph=@fph,clgz=@clgz,xsrq=@xsrq,bz=@bz WHERE id=@id";			
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@id",SqlDbType.Int,4),
					new SqlParameter("@carku_ccbh",SqlDbType.VarChar,50),
					new SqlParameter("@grxx_id",SqlDbType.Int,4),
					new SqlParameter("@dwxx_id",SqlDbType.Int,4),
					new SqlParameter("@jg",SqlDbType.Decimal,9),
					new SqlParameter("@fph",SqlDbType.VarChar,50),
					new SqlParameter("@clgz",SqlDbType.VarChar,50),
					new SqlParameter("@xsrq",SqlDbType.DateTime,8),
					new SqlParameter("@bz",SqlDbType.VarChar,2000),
			};
			param[0].Value = info.id;
			param[1].Value = info.carkuccbh;
			param[2].Value = info.grxxid;
			param[3].Value = info.dwxxid;
			param[4].Value = info.jg;
			param[5].Value = info.fph;
			param[6].Value = info.clgz;
			param[7].Value = info.xsrq;
			param[8].Value = info.bz;

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


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

		

		public XsxxInfo getXsxxByid(int id)
		{
			XsxxInfo info = new XsxxInfo();
			string selSql = "SELECT carku_ccbh,grxx_id,dwxx_id,jg,fph,clgz,xsrq,bz FROM xsxx WHERE id=@id";
			SqlParameter[] param = new SqlParameter[]
				{
					new SqlParameter("@id",SqlDbType.Int,4),
			};
			param[0].Value = id;
			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.id= dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.carkuccbh = dr.IsDBNull(1)?"":dr.GetString(1);
					info.grxxid = dr.IsDBNull(2)?0:dr.GetInt32(2);
					info.dwxxid = dr.IsDBNull(3)?0:dr.GetInt32(3);
					info.jg = dr.IsDBNull(4)?0:dr.GetDecimal(4);
					info.fph = dr.IsDBNull(5)?"":dr.GetString(5);
					info.clgz = dr.IsDBNull(6)?"":dr.GetString(6);
					info.xsrq = dr.IsDBNull(7)?DateTime.Today:dr.GetDateTime(7);
					info.bz = dr.IsDBNull(8)?"":dr.GetString(8);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

	
		public DataSet getALLXsxx_DataSet(string tableName,string idcol)
		{
			string selSql = "SELECT  id,carku_ccbh,grxx_id,dwxx_id,jg,fph,clgz,xsrq,bz FROM xsxx where "+idcol+"<>0";
			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 decimal getXsxxCount(int year,int imonth,string tablename,string colname)
		{
			XsxxInfo info = new XsxxInfo();
			string selSql;		
			if(colname=="數量")
			{
				if(tablename=="全部")
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year+" and month(xsrq)="+imonth;
				}
				else if(tablename=="個人銷售表")
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year+" and month(xsrq)="+imonth+" and dwxx_id=0";
				}
				else 
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year+" and month(xsrq)="+imonth+" and grxx_id=0";
				}
			
			}
			else
			{
				if(tablename=="全部")
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year+" and  month(xsrq)="+imonth;
				}
				else if(tablename=="個人銷售表")
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year+" and  month(xsrq)="+imonth+" and dwxx_id=0";
				}
				else
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year+" and  month(xsrq)="+imonth+" and grxx_id=0";
				}
			
			}
			decimal i;
			try
			{
				i = Convert.ToDecimal(OraHelper.SqlserverExecuteScalar(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql));
			}
			catch(Exception e)
			{
				throw e;
			}
			return i;
		}
		public decimal getTotalCount(int year,string tablename,string colname)
		{
			XsxxInfo info = new XsxxInfo();
			string selSql;		
			if(colname=="數量")
			{
				if(tablename=="全部")
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year;
				}
				else if(tablename=="個人銷售表")
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year+" and dwxx_id=0";
				}
				else 
				{
					selSql = "SELECT count(*) FROM xsxx WHERE year(xsrq)="+year+" and grxx_id=0";
				}
			
			}
			else
			{
				if(tablename=="全部")
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year;
				}
				else if(tablename=="個人銷售表")
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year+" and dwxx_id=0";
				}
				else
				{
					selSql = "SELECT sum(jg) FROM xsxx WHERE year(xsrq)="+year+" and grxx_id=0";
				}
			
			}
			decimal i;
			try
			{
				i = Convert.ToDecimal(OraHelper.SqlserverExecuteScalar(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql));
			}
			catch(Exception e)
			{
				throw e;
			}
			return i;
		}
		#endregion
	}
}