dbworkbill.cs

汽车销售公司ERP进销存系统 汽车销售公司ERP进销存系统

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

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 工单基本信息数据库操作类
	/// </summary>
	public class DBWorkBill:IWorkBill
	{
		public DBWorkBill()
		{
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}
//private static String errorfile="F:\\net_project\\callcenter\\error.log";
		public int addWorkBill(WorkBillInfo info)
		{
			if( info == null)
				throw new Exception("The WorkBill Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_workbill");
			
			string addSql = "INSERT INTO workbill(id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment) VALUES(:id,:cid,:cname,:ctelnumber,:brief,:sid,:sname,sysdate,:state,:grade,:ccomment)";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":cname",OracleType.VarChar,60),
					new OracleParameter(":ctelnumber",OracleType.VarChar,30),
					new OracleParameter(":brief",OracleType.VarChar,100),
					new OracleParameter(":sid",OracleType.VarChar,60),
					new OracleParameter(":sname",OracleType.VarChar,60),
					//					new OracleParameter(":ndate",OracleType.VarChar),
					new OracleParameter(":state",OracleType.Int32,10),
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":ccomment",OracleType.VarChar,1000),				
			};
			param[0].Value = info.id;
			param[1].Value = info.cid;
			param[2].Value = info.cname;
			param[3].Value = info.ctelnumber;
			param[4].Value = info.brief;
			param[5].Value = info.sid;
			param[6].Value = info.sname;
			//			param[5].Value = info.ndate;
			param[7].Value = info.state;
			param[8].Value = info.grade;
			param[9].Value = info.comment;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.StackTrace);
				throw e;
			}
			finally				
			{
				;
			}
			return info.id;
		}

		public void updateWorkBill(WorkBillInfo info)
		{
			if( info == null)
				throw new Exception("The WorkBill Object is null,Can't Save Null object!");			

			string updateSql = "UPDATE workbill SET cid=:cid,cname=:cname,ctelnumber=:ctelnumber,brief=:brief,sid=:sid,sname=:sname,state=:state,grade=:grade,ccomment=:ccomment WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{					
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":cname",OracleType.VarChar,60),
					new OracleParameter(":ctelnumber",OracleType.VarChar,30),
					new OracleParameter(":brief",OracleType.VarChar,100),
					new OracleParameter(":sid",OracleType.VarChar,60),
					new OracleParameter(":sname",OracleType.VarChar,60),
					//new OracleParameter(":ndate",OracleType.VarChar),
					new OracleParameter(":state",OracleType.Int32,10),
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":ccomment",OracleType.VarChar,1000),
					new OracleParameter(":id",OracleType.Int32,10),
			};
			
			param[0].Value = info.cid;
			param[1].Value = info.cname;
			param[2].Value = info.ctelnumber;
			param[3].Value = info.brief;
			param[4].Value = info.sid;
			param[5].Value = info.sname;
			//param[4].Value = info.ndate;
			param[6].Value = info.state;
			param[7].Value = info.grade;
			param[8].Value = info.comment;
			param[9].Value = info.id;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}		
		}

		public int delWorkBill(string ids)
		{
			string delSql = "DELETE FROM workbill WHERE id IN("+ids+")";
			
			try
			{
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public WorkBillInfo getWorkBillById(int id)
		{
			WorkBillInfo info = new WorkBillInfo();
			string selSql = "SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment FROM workbill WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
			};
			param[0].Value = id;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.id = id;
					info.cid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.cname = dr.IsDBNull(2)?"":dr.GetString(2);
					info.ctelnumber = dr.IsDBNull(3)?"":dr.GetString(3);
					info.brief = dr.IsDBNull(4)?"":dr.GetString(4);
					info.sid = dr.IsDBNull(5)?"":dr.GetString(5);
					info.sname = dr.IsDBNull(6)?"":dr.GetString(6);
					DateTime date = dr.GetDateTime(7);
					info.ndate = date.ToShortDateString();
					info.state = dr.IsDBNull(8)?0:dr.GetInt32(8);
					info.grade = dr.IsDBNull(9)?0:dr.GetInt32(9);
					info.comment = dr.IsDBNull(10)?"":dr.GetString(10);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public ArrayList getWorkBillByCid(int cid)
		{
			ArrayList list = new ArrayList();			
			string selSql = "SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment FROM workbill WHERE cid=:cid ORDER BY ndate DESC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(dr.Read())
				{
					WorkBillInfo info = new WorkBillInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					info.cid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.cname = dr.IsDBNull(2)?"":dr.GetString(2);
					info.ctelnumber = dr.IsDBNull(3)?"":dr.GetString(3);
					info.brief = dr.IsDBNull(4)?"":dr.GetString(4);
					info.sid = dr.IsDBNull(5)?"":dr.GetString(5);
					info.sname = dr.IsDBNull(6)?"":dr.GetString(6);
					DateTime date = dr.GetDateTime(7);
					info.ndate = date.ToShortDateString();
					info.state = dr.IsDBNull(8)?0:dr.GetInt32(8);
					info.grade = dr.IsDBNull(9)?0:dr.GetInt32(9);
					info.comment = dr.IsDBNull(10)?"":dr.GetString(10);
					
					list .Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}

		public void changeWorkBillState(int id,int state)
		{					
			string updateSql = "UPDATE workbill SET state=:state WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{										
					new OracleParameter(":state",OracleType.Int32,10),					
					new OracleParameter(":id",OracleType.Int32,10),
			};
			param[0].Value = state;			
			param[1].Value = id;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}

		public void saveGradeACommnet(int id,int grade,string comment)
		{
			string updateSql = "UPDATE workbill SET grade=:grade,ccomment=:ccomment WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{										
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":ccomment",OracleType.VarChar,1000),
					new OracleParameter(":id",OracleType.Int32,10),
			};
			param[0].Value = grade;
			param[1].Value = comment==null?"":comment;
			param[2].Value = id;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.TargetSite);
				throw e;
			}
			finally				
			{
				;
			}
		}
	
		public ArrayList queryWorkBill(WorkBillInfo info)
		{
			ArrayList list = new ArrayList();
			StringBuilder selSql = new StringBuilder("SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment FROM workbill WHERE 1=1 ");
			//客户姓名
			if(null != info.cname && !"".Equals(info.cname))
			{
				selSql.Append(" AND cname LIKE '%"+info.cname.Replace("'","''")+"%'");
			}
			//客户来电
			if(null != info.ctelnumber && !"".Equals(info.ctelnumber))
			{
				selSql.Append(" AND ctelnumber = '"+info.ctelnumber.Replace("'","''")+"'");
			}
			//工单简介
			if(null != info.brief && !"".Equals(info.brief))
			{
				selSql.Append(" AND brief LIKE '%"+info.brief.Replace("'","''")+"%'");
			}
			//工单生成时间
			if(null != info.qadate && !"".Equals(info.qadate))
			{
				selSql.Append(" AND ndate >= to_date('"+info.qadate+"','yyyy-mm-dd hh24:mi:ss')");
			}
			//工单生成时间
			if(null != info.qbdate && !"".Equals(info.qbdate))
			{
				selSql.Append(" AND ndate <= to_date('"+info.qbdate+"','yyyy-mm-dd hh24:mi:ss')");
			}
			//工单状态
			if(0 != info.state)
			{
				selSql.Append(" AND state ="+info.state);
			}
			//满意度
			if(0 != info.grade)
			{
				selSql.Append(" AND grade ="+info.grade);
			}
			//操作员编码
			if(null != info.sid && !"".Equals(info.sid))
			{
				selSql.Append(" AND (sid = '"+info.sid.Replace(",","''")+"' OR id IN(SELECT wbid FROM wbcontent WHERE opid='"+info.sid.Replace(",","''")+"')) ");
			}
			selSql.Append(" ORDER BY id DESC");
//			TextWriter error = new StreamWriter(errorfile,true);
//			error.WriteLine(selSql.ToString());			
//			error.Close();
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql.ToString(),null);
				while(dr.Read())
				{
					WorkBillInfo ninfo = new WorkBillInfo();
					ninfo.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					ninfo.cid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					ninfo.cname = dr.IsDBNull(2)?"":dr.GetString(2);
					ninfo.ctelnumber = dr.IsDBNull(3)?"":dr.GetString(3);
					ninfo.brief = dr.IsDBNull(4)?"":dr.GetString(4);
					ninfo.sid = dr.IsDBNull(5)?"":dr.GetString(5);
					ninfo.sname = dr.IsDBNull(6)?"":dr.GetString(6);
					DateTime date = dr.GetDateTime(7);
					ninfo.ndate = date.ToShortDateString();
					ninfo.state = dr.IsDBNull(8)?0:dr.GetInt32(8);
					ninfo.grade = dr.IsDBNull(9)?0:dr.GetInt32(9);
					ninfo.comment = dr.IsDBNull(10)?"":dr.GetString(10);
					
					list .Add(ninfo);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.StackTrace);
				Console.WriteLine(selSql.ToString());
				Console.WriteLine(e.TargetSite);
				throw e;
			}
			return list;
		}

		public DataSet getWorkBillByCid_DataSet(int cid,string tableName)
		{
			string selSql = "SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment FROM workbill WHERE cid=:cid ORDER BY ndate DESC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		public DataSet queryWorkBill_DataSet(WorkBillInfo info,string tableName)
		{
//			StringBuilder selSql = new StringBuilder("SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment FROM workbill WHERE 1=1 ");
			StringBuilder selSql = new StringBuilder("SELECT id,cid,cname,ctelnumber,brief,sid,sname,ndate,state,grade,ccomment,wbstate,gshow FROM view_workbill_state_grade WHERE 1=1 ");
			//客户姓名
			if(null != info.cname && !"".Equals(info.cname))
			{
				selSql.Append(" AND cname LIKE '%"+info.cname.Replace("'","''")+"%'");
			}
			//客户来电
			if(null != info.ctelnumber && !"".Equals(info.ctelnumber))
			{
				selSql.Append(" AND ctelnumber = '"+info.ctelnumber.Replace("'","''")+"'");
			}
			//工单简介
			if(null != info.brief && !"".Equals(info.brief))
			{
				selSql.Append(" AND brief LIKE '%"+info.brief.Replace("'","''")+"%'");
			}
			//工单生成时间
			if(null != info.qadate && !"".Equals(info.qadate))
			{
				selSql.Append(" AND ndate >= to_date('"+info.qadate+"','yyyy-mm-dd hh24:mi:ss')");
			}
			//工单生成时间
			if(null != info.qbdate && !"".Equals(info.qbdate))
			{
				selSql.Append(" AND ndate <= to_date('"+info.qbdate+"','yyyy-mm-dd hh24:mi:ss')");
			}
			//工单状态
			if(0 != info.state)
			{
				selSql.Append(" AND state ="+info.state);
			}
			//满意度
			if(0 != info.grade)
			{
				selSql.Append(" AND grade ="+info.grade);
			}
			//操作员编码
			if(null != info.sid && !"".Equals(info.sid))
			{
				selSql.Append(" AND (sid = '"+info.sid.Replace(",","''")+"' OR id IN(SELECT wbid FROM wbcontent WHERE opid='"+info.sid.Replace(",","''")+"')) ");
			}
			selSql.Append(" ORDER BY id DESC");
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql.ToString(),null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
	}
}