dbservicenote.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 DBServiceNote:ISPServiceNote
	{
		public DBServiceNote()
		{
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}
		#region ISPServiceNote 成员

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

			string updateSql = "UPDATE spservicenote SET sid=:sid,sname=:sname,wbid=:wbid,sdate=to_date(:sdate,'yyyy-mm-dd'),cid=:cid,cname=:cname,scontent=:scontent,grade=:grade,ccomment=:ccomment WHERE id=:id";
			OracleParameter [] param = new OracleParameter[]
				{					
					new OracleParameter(":sid",OracleType.Int32,10),
					new OracleParameter(":sname",OracleType.VarChar,100),
					new OracleParameter(":wbid",OracleType.Int32,10),
					new OracleParameter(":sdate",OracleType.VarChar,20),
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":cname",OracleType.VarChar,60),
					new OracleParameter(":scontent",OracleType.VarChar,1000),
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":ccomment",OracleType.VarChar,1000),
					new OracleParameter(":id",OracleType.Int32,10),
			};
			
			param[0].Value = info.sid;
			param[1].Value = info.sname;
			param[2].Value = info.wbid;
			param[3].Value = info.sdate;
			param[4].Value = info.cid;
			param[5].Value = info.cname;
			param[6].Value = info.scontent;
			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 SPServiceNoteInfo getSPServiceNoteById(int id)
		{
			SPServiceNoteInfo info = new SPServiceNoteInfo();
			string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade 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.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
					info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
					info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
					info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
					info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
					info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
					info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
					info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

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

		public void delSPServiceNoteBySIds(string sids)
		{
			string delSql = "DELETE FROM spservicenote WHERE sid IN("+sids+")";
			
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public ArrayList getSPServiceNoteBySId(int sid)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE sid=:sid ORDER BY sdate DESC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":sid",OracleType.Int32,10),
			};
			param[0].Value = sid;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(dr.Read())
				{
					SPServiceNoteInfo info = new SPServiceNoteInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
					info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
					info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
					info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
					info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
					info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
					info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
					info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.StackTrace);
				throw e;
			}
			return list;
		}

		public ArrayList getSPServiceNoteByWBId(int wbid)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE wbid=:wbid ORDER BY id ASC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":wbid",OracleType.Int32,10),
			};
			param[0].Value = wbid;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(dr.Read())
				{
					SPServiceNoteInfo info = new SPServiceNoteInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
					info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
					info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
					info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
					info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
					info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
					info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
					info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
					info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);

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

		public int addSPServiceNote(SPServiceNoteInfo info)
		{
			if( info == null)
				throw new Exception("The SPServiceNote Object is null,Can't Save Null object!");
			info.id = OraHelper.createSequence("se_spservicenote");
//			Console.WriteLine(info);
			string addSql = "INSERT INTO spservicenote(id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment) VALUES(:id,:sid,:sname,:wbid,to_date(:sdate,'yyyy-mm-dd'),:cid,:cname,:scontent,:grade,:ccomment)";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
					new OracleParameter(":sid",OracleType.Int32,10),
					new OracleParameter(":sname",OracleType.VarChar,100),
					new OracleParameter(":wbid",OracleType.Int32,10),
					new OracleParameter(":sdate",OracleType.VarChar,20),
					new OracleParameter(":cid",OracleType.Int32,10),
					new OracleParameter(":cname",OracleType.VarChar,60),
					new OracleParameter(":scontent",OracleType.VarChar,1000),
					new OracleParameter(":grade",OracleType.Int32,10),
					new OracleParameter(":ccomment",OracleType.VarChar,1000),
			};
			param[0].Value = info.id;
			param[1].Value = info.sid;
			param[2].Value = info.sname;
			param[3].Value = info.wbid;
			param[4].Value = info.sdate;
			param[5].Value = info.cid;
			param[6].Value = info.cname;
			param[7].Value = info.scontent;
			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 DataSet getSPServiceNoteByWBId_DataSet(int wbid,string tableName)
		{
			string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE wbid=:wbid ORDER BY id ASC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":wbid",OracleType.Int32,10),
			};
			param[0].Value = wbid;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		public DataSet getSPServiceNoteBySId_DataSet(int sid,string tableName)
		{
			string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE sid=:sid ORDER BY sdate DESC";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":sid",OracleType.Int32,10),
			};
			param[0].Value = sid;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message.ToString());
				Console.WriteLine(e.StackTrace);
				throw e;
			}
		}
		#endregion
	}
}