dbserviceprovider.cs

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

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

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 服务商信息数据库操作类
	/// </summary>
	public class DBServiceProvider:CallCenter.IDAL.IServiceProvider
	{
		public DBServiceProvider()
		{
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}
		#region IServiceProvider 成员

		public int addServiceProvider(ServiceProviderInfo info)
		{
			if( info == null)
				throw new Exception("The ServiceProvider Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_serviceprovider");
			string addSql = "INSERT INTO serviceprovider(id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad) VALUES(:id,:sname,:stype,to_date(:sdate,'yyyy-mm-dd'),:slinkname,:stelephone,:sfax,:semail,:sweb,:ctype,:ckeywords,:saddress,:position,:sbrief,:sgood,:sbad)";			
			OracleParameter [] param = new OracleParameter[]
							{
								new OracleParameter(":id",OracleType.Int32,10),
								new OracleParameter(":sname",OracleType.VarChar,60),
								new OracleParameter(":stype",OracleType.Int32,10),
								new OracleParameter(":sdate",OracleType.VarChar,20),
								new OracleParameter(":slinkname",OracleType.VarChar,60),
								new OracleParameter(":stelephone",OracleType.VarChar,60),
								new OracleParameter(":sfax",OracleType.VarChar,60),
								new OracleParameter(":semail",OracleType.VarChar,60),
								new OracleParameter(":sweb",OracleType.VarChar,60),
								new OracleParameter(":ctype",OracleType.VarChar,100),
								new OracleParameter(":ckeywords",OracleType.VarChar,200),
								new OracleParameter(":saddress",OracleType.VarChar,100),
								new OracleParameter(":position",OracleType.Int32,10),
								new OracleParameter(":sbrief",OracleType.VarChar,2000),
								new OracleParameter(":sgood",OracleType.Int32,10),
								new OracleParameter(":sbad",OracleType.Int32,10),
			};
			
			param[0].Value = info.id;
			param[1].Value = info.sname;
			param[2].Value = info.stype;
			param[3].Value = info.sdate;
			param[4].Value = info.slinkname;
			param[5].Value = info.stelephone;
			param[6].Value = info.sfax;
			param[7].Value = info.semail;
			param[8].Value = info.sweb;
			param[9].Value = info.ctype;
			param[10].Value = info.ckeywords;
			param[11].Value = info.saddress;
			param[12].Value = info.position;
			param[13].Value = info.sbrief;
			param[14].Value = info.sgood;
			param[15].Value = info.sbad;
						
			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 ServiceProviderInfo getServiceProviderById(int id)
		{
			ServiceProviderInfo info = new ServiceProviderInfo();
			string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type 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.sname=dr.IsDBNull(1)?"":dr.GetString(1);
					info.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
					info.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();										
					info.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
					info.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
					info.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
					info.semail=dr.IsDBNull(7)?"":dr.GetString(7);
					info.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
					info.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
					info.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
					info.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
					info.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
					info.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
					info.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
					info.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
					info.stname=dr.IsDBNull(16)?"":dr.GetString(16);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public ArrayList getServiceProviderByCkeywords(string keywords)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type WHERE ckeywords LIKE '%"+keywords.Replace("'","''")+"%'";
			
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read())
				{
					ServiceProviderInfo info = new ServiceProviderInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.sname=dr.IsDBNull(1)?"":dr.GetString(1);
					info.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
					info.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();										
					info.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
					info.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
					info.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
					info.semail=dr.IsDBNull(7)?"":dr.GetString(7);
					info.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
					info.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
					info.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
					info.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
					info.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
					info.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
					info.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
					info.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
					info.stname=dr.IsDBNull(16)?"":dr.GetString(16);
					
					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}

		public ArrayList queryServiceProvider(ServiceProviderInfo info)
		{
			ArrayList list = new ArrayList();
			StringBuilder selSql = new StringBuilder("SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type WHERE 1=1 ");
			//服务商名称
			if(null != info.sname && !"".Equals(info.sname))
			{
				selSql.Append(" AND sname LIKE '%"+info.sname.Replace("'","''")+"%'");
			}
			//服务商分类
			if(0 != info.stype)
			{
				selSql.Append(" AND stype ="+info.stype);
			}
			//联系人
			if(null != info.slinkname && !"".Equals(info.slinkname))
			{
				selSql.Append(" AND slinkname LIKE '%"+info.slinkname.Replace("'","''")+"%'");
			}
			//服务内容分类
			if(null != info.ctype && !"".Equals(info.ctype))
			{
				selSql.Append(" AND ctype LIKE '%,"+info.ctype+",%'");
			}
			//服务内容关键字
			if(null != info.ckeywords && !"".Equals(info.ckeywords))
			{
				selSql.Append(" AND ckeywords LIKE '%"+info.ckeywords.Replace("'","''")+"%'");
			}
			//服务商地址
			if(null != info.saddress && !"".Equals(info.saddress))
			{
				selSql.Append(" AND saddress LIKE '%"+info.saddress.Replace("'","''")+"%'");
			}
			selSql.Append(" ORDER BY id DESC");
			Console.WriteLine(selSql.ToString());
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql.ToString(),null);
				while(dr.Read())
				{
					ServiceProviderInfo ninfo = new ServiceProviderInfo();
					ninfo.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					ninfo.sname=dr.IsDBNull(1)?"":dr.GetString(1);
					ninfo.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
					ninfo.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();										
					ninfo.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
					ninfo.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
					ninfo.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
					ninfo.semail=dr.IsDBNull(7)?"":dr.GetString(7);
					ninfo.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
					ninfo.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
					ninfo.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
					ninfo.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
					ninfo.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
					ninfo.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
					ninfo.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
					ninfo.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
					ninfo.stname=dr.IsDBNull(16)?"":dr.GetString(16);
					//Console.WriteLine(ninfo.stname);
					list.Add(ninfo);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}

		public void changeServiceProviderGrade(int id, int gtype, int grade)
		{
			string updateSql = "";
			if(0==gtype)
			{
				updateSql = "UPDATE serviceprovider SET sbad=sbad+:grade WHERE id=:id";
			}
			else if(1==gtype)
			{
				updateSql = "UPDATE serviceprovider SET sgood=sgood+:grade WHERE id=:id";
			}
				OracleParameter [] param = new OracleParameter[]
				{	
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":id",OracleType.Int32,10),
				};

				param[0].Value = grade;
				param[1].Value = id;
									
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}