dbcustomer.cs

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

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;

namespace CallCenter.OracleDAL {
	/// <summary>
	/// 客户信息数据库操作类
	/// </summary>
	public class DBCustomer:ICustomer {		
		public DBCustomer() {
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}
	
		public int addCustomer(CustomerInfo info) {
			if( info == null)
				throw new Exception("The Customer Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_customer");

			string addSql = "INSERT INTO customer(id,cname,csex,caddress,ccontact,cemail,cfax,ctype,ccomment,telnumber) VALUES(:id,:cname,:csex,:caddress,:ccontact,:cemail,:cfax,:ctype,:ccomment,:telnumber)";			
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":cname",OracleType.VarChar,60),
																 new OracleParameter(":csex",OracleType.VarChar,10),
																 new OracleParameter(":caddress",OracleType.VarChar,200),
																 new OracleParameter(":ccontact",OracleType.VarChar,100),
																 new OracleParameter(":cemail",OracleType.VarChar,60),
																 new OracleParameter(":cfax",OracleType.VarChar,60),
																 new OracleParameter(":ctype",OracleType.Int32,10),
																 new OracleParameter(":ccomment",OracleType.VarChar,500),
																 new OracleParameter(":telnumber",OracleType.VarChar,30),
			};
			param[0].Value = info.id;
			param[1].Value = info.cname;
			param[2].Value = info.csex;
			param[3].Value = info.caddress;
			param[4].Value = info.ccontact;
			param[5].Value = info.cemail;
			param[6].Value = info.cfax;
			param[7].Value = info.ctype;
			param[8].Value = info.ccomment;
			param[9].Value = info.ctelnumber;

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

		}
	
		public CustomerInfo getCustomerById(int id) {
			CustomerInfo info = new CustomerInfo();
			string selSql = "SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,ccomment,telnumber,position FROM view_customer_ctype 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.cname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.csex = dr.IsDBNull(2)?"":dr.GetString(2);
					info.caddress = dr.IsDBNull(3)?"":dr.GetString(3);
					info.ccontact = dr.IsDBNull(4)?"":dr.GetString(4);
					info.cemail = dr.IsDBNull(5)?"":dr.GetString(5);
					info.cfax = dr.IsDBNull(6)?"":dr.GetString(6);
					info.ctype = dr.IsDBNull(7)?0:dr.GetInt32(7);
					info.ctname = dr.IsDBNull(8)?"":dr.GetString(8);
					info.ccomment = dr.IsDBNull(9)?"":dr.GetString(9);
					info.ctelnumber = dr.IsDBNull(10)?"":dr.GetString(10);
					info.position = dr.IsDBNull(11)?0:dr.GetInt32(11);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return info;
		}
	
		public ArrayList getCustomerByIds(string ids) {
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,ccomment,telnumber,position FROM view_customer_ctype WHERE id IN ("+ids+")";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					CustomerInfo info = new CustomerInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					info.cname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.csex = dr.IsDBNull(2)?"":dr.GetString(2);
					info.caddress = dr.IsDBNull(3)?"":dr.GetString(3);
					info.ccontact = dr.IsDBNull(4)?"":dr.GetString(4);
					info.cemail = dr.IsDBNull(5)?"":dr.GetString(5);
					info.cfax = dr.IsDBNull(6)?"":dr.GetString(6);
					info.ctype = dr.IsDBNull(7)?0:dr.GetInt32(7);
					info.ctname = dr.IsDBNull(8)?"":dr.GetString(8);
					info.ccomment = dr.IsDBNull(9)?"":dr.GetString(9);
					info.ctelnumber = dr.IsDBNull(10)?"":dr.GetString(10);
					info.position = dr.IsDBNull(11)?0:dr.GetInt32(11);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}
			
		//		public ArrayList getCustomerByTAN(string telnumber, string cname)
		//		{
		//			ArrayList list = new ArrayList();
		//			string selSql = "SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,telnumber FROM view_customer_ctype_tel WHERE telnumber=:telnumber AND cname=:cname";
		//			OracleParameter [] param = new OracleParameter[]
		//				{
		//					new OracleParameter(":id",OracleType.Int32,10),
		//			};
		//			param[0].Value = ids;
		//			try
		//			{
		//				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
		//				while(dr.Read())
		//				{
		//					CustomerInfo info = new CustomerInfo();
		//					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
		//					info.cname = dr.IsDBNull(1)?"":dr.GetString(1);
		//					info.csex = dr.IsDBNull(2)?"":dr.GetString(2);
		//					info.caddress = dr.IsDBNull(3)?"":dr.GetString(3);
		//					info.ccontact = dr.IsDBNull(4)?"":dr.GetString(4);
		//					info.cemail = dr.IsDBNull(5)?"":dr.GetString(5);
		//					info.cfax = dr.IsDBNull(6)?"":dr.GetString(6);
		//					info.ctype = dr.IsDBNull(7)?0:dr.GetInt32(7);
		//					info.ctname = dr.IsDBNull(8)?"":dr.GetString(8);
		//
		//					list.Add(info);
		//				}
		//			}
		//			catch(Exception e)
		//			{
		//				throw e;
		//			}
		//			return list;
		//		}
	
		public ArrayList queryCustomer(CustomerInfo info) {
			ArrayList list = new ArrayList();
			StringBuilder selSql = new StringBuilder("SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,telnumber,ccomment,position FROM view_customer_ctype 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 telnumber = '"+info.ctelnumber.Replace("'","''")+"'");
			}
			if(0 != info.ctype) {
				selSql.Append(" AND ctype ="+info.ctype);
			}
			if(null != info.caddress && !"".Equals(info.caddress)) {
				selSql.Append(" AND caddress LIKE '%"+info.caddress.Replace("'","''")+"%'");
			}
			if(null != info.csex && !"".Equals(info.csex)) {
				selSql.Append(" AND csex = '"+info.csex+"'");
			}
			selSql.Append(" ORDER BY id DESC");
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql.ToString(),null);
				while(dr.Read()) {
					CustomerInfo ninfo = new CustomerInfo();
					ninfo.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					ninfo.cname = dr.IsDBNull(1)?"":dr.GetString(1);
					ninfo.csex = dr.IsDBNull(2)?"":dr.GetString(2);
					ninfo.caddress = dr.IsDBNull(3)?"":dr.GetString(3);
					ninfo.ccontact = dr.IsDBNull(4)?"":dr.GetString(4);
					ninfo.cemail = dr.IsDBNull(5)?"":dr.GetString(5);
					ninfo.cfax = dr.IsDBNull(6)?"":dr.GetString(6);
					ninfo.ctype = dr.IsDBNull(7)?0:dr.GetInt32(7);
					ninfo.ctname = dr.IsDBNull(8)?"":dr.GetString(8);
					ninfo.ctelnumber = dr.IsDBNull(9)?"":dr.GetString(9);
					ninfo.ccomment = dr.IsDBNull(10)?"":dr.GetString(10);
					ninfo.position = dr.IsDBNull(11)?0:dr.GetInt32(11);
					
					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 int updateCustomer(CustomerInfo info) {
			if( info == null)
				throw new Exception("The TelCustomer Object is null,Can't Save Null object!");
			
			string updateSql = "UPDATE customer SET id=:id,cname=:cname,csex=:csex,caddress=:caddress,ccontact=:ccontact,cemail=:cemail,cfax=:cfax,ctype=:ctype,ccomment=:ccomment,telnumber=:telnumber WHERE id=:id";
			OracleParameter [] param = new OracleParameter[] {					
																 new OracleParameter(":cname",OracleType.VarChar,60),
																 new OracleParameter(":csex",OracleType.VarChar,10),
																 new OracleParameter(":caddress",OracleType.VarChar,200),
																 new OracleParameter(":ccontact",OracleType.VarChar,100),
																 new OracleParameter(":cemail",OracleType.VarChar,60),
																 new OracleParameter(":cfax",OracleType.VarChar,60),
																 new OracleParameter(":ctype",OracleType.Int32,10),
																 new OracleParameter(":ccomment",OracleType.VarChar,1000),
																 new OracleParameter(":telnumber",OracleType.VarChar,30),
																 new OracleParameter(":id",OracleType.Int32,10),				
			};			
			param[0].Value = info.cname;
			param[1].Value = info.csex;
			param[2].Value = info.caddress;
			param[3].Value = info.ccontact;
			param[4].Value = info.cemail;
			param[5].Value = info.cfax;
			param[6].Value = info.ctype;
			param[7].Value = info.ccomment;
			param[8].Value = info.ctelnumber;
			param[9].Value = info.id;

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

		public ArrayList getCustomerType(){
			ArrayList list = new ArrayList();
			string selSql = "select id,tname from customertype";
		
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					CustomerInfo ninfo = new CustomerInfo();
					ninfo.ctype = dr.GetInt32(0);
					ninfo.ctname = dr.GetString(1);

					list.Add(ninfo);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}
	
		public DataSet getCustomerByIds_DataSet(string ids,string tableName) {
			string selSql = "SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,ccomment,telnumber,position FROM view_customer_ctype WHERE id IN ("+ids+")";
			
			try {
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public DataSet queryCustomer_DataSet(CustomerInfo info,string tableName) {
			ArrayList list = new ArrayList();
			StringBuilder selSql = new StringBuilder("SELECT id,cname,csex,caddress,ccontact,cemail,cfax,ctype,tname,telnumber,ccomment,position FROM view_customer_ctype 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 telnumber = '"+info.ctelnumber.Replace("'","''")+"'");
			}
			if(0 != info.ctype) {
				selSql.Append(" AND ctype ="+info.ctype);
			}
			if(null != info.caddress && !"".Equals(info.caddress)) {
				selSql.Append(" AND caddress LIKE '%"+info.caddress.Replace("'","''")+"%'");
			}
			if(null != info.csex && !"".Equals(info.csex)) {
				selSql.Append(" AND csex = '"+info.csex+"'");
			}
			selSql.Append(" ORDER BY id DESC");
			try {
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql.ToString(),null);
			}
			catch(Exception e) {
				throw e;
			}
		}

		public DataSet getCustomerType_DataSet(string tableName){
			string selSql = "select id,tname from customertype";
		
			try {
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e) {
				throw e;
			}
		}

		public ArrayList getCustomerFromOtherDB(CustomerInfo info){
			ArrayList list = new ArrayList();
			StringBuilder selSql = new StringBuilder("SELECT zxm,zxb,zjtzz,zlxdh FROM mz_grxx WHERE 1=1 ");
			if(null != info.cname && !"".Equals(info.cname)) {
				selSql.Append(" AND zxm LIKE '%"+info.cname.Replace("'","''")+"%'");
			}
			if(null != info.ctelnumber && !"".Equals(info.ctelnumber)) {
				selSql.Append(" AND zlxdh = '"+info.ctelnumber.Replace("'","''")+"'");
			}			
			if(null != info.caddress && !"".Equals(info.caddress)) {
				selSql.Append(" AND zjtzz LIKE '%"+info.caddress.Replace("'","''")+"%'");
			}
			if(null != info.csex && !"".Equals(info.csex)) {
				selSql.Append(" AND zxb = '"+info.csex+"'");
			}
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql.ToString(),null);
				while(dr.Read()) {
					CustomerInfo ninfo = new CustomerInfo();
					ninfo.cname = dr.IsDBNull(0)?"":dr.GetString(0);
					ninfo.csex = dr.IsDBNull(1)?"":dr.GetString(1);
					ninfo.caddress = dr.IsDBNull(2)?"":dr.GetString(2);
					ninfo.ccontact = dr.IsDBNull(3)?"":dr.GetString(3);
					ninfo.ctelnumber = dr.IsDBNull(3)?"":dr.GetString(3);
					
					list.Add(ninfo);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}
		
	}
}