cm.cs

一套OA系统,使用SQL Server为后台数据库ASP.NET C#语言开发

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace UDS.Components
{
	/// <summary>
	/// CM 的摘要说明。
	/// </summary>
	public class CM
	{
		public CM(){}

		#region 添加客户
		/// <summary>
		/// 添加客户
		/// </summary>
		/// <param name="client"></param>
		/// <returns>返回客户id</returns>
		public int AddClinet(ClientInfo client)
		{
			Database data = new Database();
			SqlParameter[] prams = {
									    data.MakeInParam("@birthday",  SqlDbType.DateTime, 8, client.Birthday),
									    data.MakeInParam("@chieflinkmanid", SqlDbType.Int, 4, client.ChiefLinkmanID),
									    data.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, DateTime.Now),
									    data.MakeInParam("@AddmanID",SqlDbType.Int,4,client.AddManID),
									    data.MakeInParam("@ShortName",SqlDbType.VarChar,100,client.ClientShortName),
									    data.MakeInParam("@Name",SqlDbType.VarChar,100,client.ClientName),
									    data.MakeInParam("@ContactTimes",SqlDbType.Int,4,client.ContactTimes),
										data.MakeInParam("@SellPhase",SqlDbType.VarChar,100,client.SellPhase),
										data.MakeInParam("@BargainPrognosis",SqlDbType.VarChar,100,client.BargainPrognosis),
										data.MakeInParam("@Fee",SqlDbType.Int,4,client.Fee),
										data.MakeInParam("@Linkman_Telephone",SqlDbType.VarChar,100,client.Linkman_Telephone),
										data.MakeInParam("@CurStatus",SqlDbType.VarChar,100,client.CurStatus),
										data.MakeInParam("@Type",SqlDbType.VarChar,100,client.Type),
										data.MakeInParam("@Affiliatedarea",SqlDbType.VarChar,100,client.Affiliatedarea),
										data.MakeInParam("@URL",SqlDbType.VarChar,100,client.URL),
										data.MakeInParam("@ZIP",SqlDbType.VarChar,100,client.ZIP),
										data.MakeInParam("@Address",SqlDbType.VarChar,200,client.Address),
										data.MakeInParam("@CompanyProperty",SqlDbType.VarChar,100,client.EnterpriseType),
										data.MakeInParam("@Calling",SqlDbType.VarChar,2000,client.Calling),
										data.MakeInParam("@CompanySize",SqlDbType.VarChar,100,client.CompanySize),
										data.MakeInParam("@Money",SqlDbType.VarChar,100,client.Money),
										data.MakeInParam("@Operation",SqlDbType.VarChar,100,client.Operation),
										data.MakeInParam("@Introduce",SqlDbType.VarChar,1000,client.Introduce),
										data.MakeInParam("@ITGrade",SqlDbType.VarChar,1000,client.ITGrade),
										data.MakeInParam("@PCNumber",SqlDbType.Int,4,client.PCNumber),
										data.MakeInParam("@Net",SqlDbType.VarChar,100,client.Net),
										data.MakeInParam("@ITStaffs",SqlDbType.Int,4,client.ITStaffs),
										data.MakeInParam("@ITDepartment",SqlDbType.VarChar,100,client.ITDepartment),
										data.MakeInParam("@Principal",SqlDbType.VarChar,100,client.Principal),
										data.MakeInParam("@System",SqlDbType.VarChar,100,client.System),
										data.MakeInParam("@Customer",SqlDbType.VarChar,100,client.ClientSource),
										data.MakeInParam("@ClientInitiative",SqlDbType.VarChar,100,client.ClientInitiative)
										
			};
			return(data.RunProc ("sp_CM_AddClient",prams));
		}
		#endregion 

		#region 修改客户
		/// <summary>
		/// 修改客户
		/// </summary>
		/// <param name="client"></param>
		public void UpdateClient(ClientInfo client)
		{
			Database data = new Database();
			SqlParameter[] prams = {
									   data.MakeInParam("@ID",SqlDbType.Int,4,client.ID),
									   data.MakeInParam("@birthday",  SqlDbType.DateTime, 8, client.Birthday),
									   data.MakeInParam("@chieflinkmanid", SqlDbType.Int, 4, client.ChiefLinkmanID),
									   data.MakeInParam("@UpdateTime", SqlDbType.DateTime, 8, DateTime.Now),
									   data.MakeInParam("@AddmanID",SqlDbType.Int,4,client.AddManID),
									   data.MakeInParam("@ShortName",SqlDbType.VarChar,100,client.ClientShortName),
									   data.MakeInParam("@Name",SqlDbType.VarChar,100,client.ClientName),
									   data.MakeInParam("@ContactTimes",SqlDbType.Int,4,client.ContactTimes),
									   data.MakeInParam("@SellPhase",SqlDbType.VarChar,100,client.SellPhase),
									   data.MakeInParam("@BargainPrognosis",SqlDbType.VarChar,100,client.BargainPrognosis),
									   data.MakeInParam("@Fee",SqlDbType.Int,4,client.Fee),
									   data.MakeInParam("@Linkman_Telephone",SqlDbType.VarChar,100,client.Linkman_Telephone),
									   data.MakeInParam("@CurStatus",SqlDbType.VarChar,100,client.CurStatus),
									   data.MakeInParam("@Type",SqlDbType.VarChar,100,client.Type),
									   data.MakeInParam("@Affiliatedarea",SqlDbType.VarChar,100,client.Affiliatedarea),
									   data.MakeInParam("@URL",SqlDbType.VarChar,100,client.URL),
									   data.MakeInParam("@ZIP",SqlDbType.VarChar,100,client.ZIP),
									   data.MakeInParam("@Address",SqlDbType.VarChar,200,client.Address),
									   data.MakeInParam("@CompanyProperty",SqlDbType.VarChar,100,client.EnterpriseType),
									   data.MakeInParam("@Calling",SqlDbType.VarChar,2000,client.Calling),
									   data.MakeInParam("@CompanySize",SqlDbType.VarChar,100,client.CompanySize),
									   data.MakeInParam("@Money",SqlDbType.VarChar,100,client.Money),
									   data.MakeInParam("@Operation",SqlDbType.VarChar,100,client.Operation),
									   data.MakeInParam("@Introduce",SqlDbType.VarChar,1000,client.Introduce),
									   data.MakeInParam("@ITGrade",SqlDbType.VarChar,1000,client.ITGrade),
									   data.MakeInParam("@PCNumber",SqlDbType.Int,4,client.PCNumber),
									   data.MakeInParam("@Net",SqlDbType.VarChar,100,client.Net),
									   data.MakeInParam("@ITStaffs",SqlDbType.Int,4,client.ITStaffs),
									   data.MakeInParam("@ITDepartment",SqlDbType.VarChar,100,client.ITDepartment),
									   data.MakeInParam("@Principal",SqlDbType.VarChar,100,client.Principal),
									   data.MakeInParam("@System",SqlDbType.VarChar,100,client.System),
									   data.MakeInParam("@Customer",SqlDbType.VarChar,100,client.ClientSource),
									   data.MakeInParam("@ClientInitiative",SqlDbType.VarChar,100,client.ClientInitiative),
									   data.MakeInParam("@FirstContactTime",SqlDbType.DateTime,8,client.FirstContactTime),
									   data.MakeInParam("@NextContactTime",SqlDbType.DateTime,8,client.NextContactTime),
									   data.MakeInParam("@ContactTime",SqlDbType.DateTime,8,client.ContactTime)
			};
			data.RunProc ("sp_CM_UpdateClient",prams);
		}
		#endregion

		#region 删除客户
		/// <summary>
		/// 删除客户
		/// </summary>
		/// <param name="id">客户id</param>
		public void DelClient(int id)
		{
			Database data = new Database();
			SqlParameter[] prams = {
										data.MakeInParam("@clientid",SqlDbType.Int,4,id)
								   };
			data.RunProc("sp_CM_DelClient",prams);

		}
		#endregion

		#region 得到所有客户
		public SqlDataReader GetAllClient()
		{
			Database data = new Database();
			SqlDataReader dr = null;
			data.RunProc ("sp_CM_GetAllClient",out dr);
			return(dr);
		}
		#endregion

		#region 根据客户id得到联络人
		public SqlDataReader GetLinkmanFromClient(int clientid)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@clientid",  SqlDbType.Int, 4, clientid)
			};
			data.RunProc ("sp_CM_GetAllLinkmanFromClient",prams,out dr);
			return(dr);
		}
		#endregion

		#region 根据客户名称得到客户id
		public SqlDataReader GetClientIDByName(string clientname)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@name",  SqlDbType.VarChar, 100, clientname)
								   };
			data.RunProc ("sp_CM_GetClientIDByName",prams,out dr);
			return(dr);
		}
		#endregion

		#region 根据客户name得到客户信息
		public SqlDataReader GetClientInfoByName(string clientname)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@clientname",  SqlDbType.VarChar, 100, clientname)
								   };
			data.RunProc ("sp_CM_GetClientInfoByName",prams,out dr);
			return(dr);
		}
		#endregion

		#region 根据客户id得到客户信息(客户id=0得到全部客户)
		public SqlDataReader GetClientInfo(int clientid)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@clientid",  SqlDbType.Int, 4, clientid)
								   };
			data.RunProc ("sp_CM_GetClientInfo",prams,out dr);
			return(dr);
		}
		public ClientInfo GetClientAllInfo(int clientid)
		{
			ClientInfo client = new ClientInfo();
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@clientid",  SqlDbType.Int, 4, clientid)
								   };
			data.RunProc ("sp_CM_GetClientAllInfo",prams,out dr);
			while(dr.Read())
			{
				client.ID = Int32.Parse(dr["id"].ToString());
				client.ClientName = dr["Name"].ToString();
				client.ClientShortName = dr["ShortName"].ToString();
				client.ChiefLinkmanID = Int32.Parse(dr["ChiefLinkmanID"].ToString());
				client.Birthday = DateTime.Parse(dr["Birthday"].ToString());
				client.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
				client.ContactTimes = Int32.Parse(dr["ContactTimes"].ToString());
				client.SellPhase = dr["SellPhase"].ToString();
				client.BargainPrognosis = dr["BargainPrognosis"].ToString();
				client.Fee = Int32.Parse(dr["Fee"].ToString());
				client.AddManID = Int32.Parse(dr["AddManID"].ToString());
				client.Linkman_Telephone = dr["Linkman_Telephone"].ToString();
				client.CurStatus = dr["CurStatus"].ToString();
				client.Address = dr["address"].ToString();
				client.Affiliatedarea = dr["affiliatedarea"].ToString();
				client.Calling = dr["calling"].ToString();
				client.ClientInitiative = dr["ClientInitiative"].ToString();
				client.ClientSource = dr["customer"].ToString();
				client.ClientTrade = dr["calling"].ToString();
				client.ClientType = dr["type"].ToString();
				client.CompanyProperty = dr["companyproperty"].ToString();
				client.CompanySize = dr["companysize"].ToString();
				client.CurStatus = dr["curstatus"].ToString();
				client.Customer = dr["customer"].ToString();
				client.EnterpriseType = dr["CompanyProperty"].ToString();
				client.Introduce = dr["introduce"].ToString();
				client.ITDepartment = dr["itdepartment"].ToString();
				client.ITGrade = dr["itgrade"].ToString();
				client.ITStaffs = Int32.Parse(dr["itstaffs"].ToString());
				client.Linkman_Telephone = dr["Linkman_Telephone"].ToString();
				client.Money = dr["money"].ToString();
				client.Net = dr["Net"].ToString();
				client.Operation = dr["operation"].ToString();
				client.PCNumber = Int32.Parse(dr["pcnumber"].ToString());
				client.Principal = dr["principal"].ToString();
				client.SellPhase = dr["sellphase"].ToString();
				client.System = dr["system"].ToString();
				client.Type = dr["type"].ToString();
				client.URL = dr["URL"].ToString();
				client.ZIP = dr["ZIP"].ToString();

				if(dr["ContactTime"]!=DBNull.Value) 
					client.ContactTime = DateTime.Parse(dr["ContactTime"].ToString());
				else
					client.ContactTime = DateTime.Parse("1900-1-1");

				if(dr["firstcontacttime"]!=DBNull.Value) 
					client.FirstContactTime = DateTime.Parse(dr["firstcontacttime"].ToString());
				else
					client.FirstContactTime = DateTime.Parse("1900-1-1");

				if(dr["nextcontacttime"]!=DBNull.Value) 
					client.NextContactTime = DateTime.Parse(dr["nextcontacttime"].ToString());
				else
					client.NextContactTime = DateTime.Parse("1900-1-1");



			}
			return(client);
		}
		#endregion

		#region 根据协同人id得到客户信息
		public SqlDataReader GetClientInfoBycooperatorID(int cooperatorID)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@cooperatorID",  SqlDbType.Int, 4, cooperatorID)
								   };
			data.RunProc ("sp_CM_GetClientInfoByCooperatorID",prams,out dr);	
			return(dr);
		}
		#endregion

		#region 添加联系人
		public int AddLinkman(Linkman linkman)
		{
			Database data = new Database();
			SqlParameter[] prams = {
									    data.MakeInParam("@ClientID",  SqlDbType.Int, 4, linkman.ClientID),
									    data.MakeInParam("@name", SqlDbType.VarChar, 50,linkman.Name),
									    data.MakeInParam("@mobile", SqlDbType.VarChar,50, linkman.Mobile),
									    data.MakeInParam("@telephone", SqlDbType.VarChar,50, linkman.Telephone),
										data.MakeInParam("@position", SqlDbType.VarChar,50, linkman.Position),
										data.MakeInParam("@email",SqlDbType.VarChar,50,linkman.Email),
										data.MakeInParam("@gender",SqlDbType.Bit,1,linkman.Gender),
										data.MakeInParam("@description",SqlDbType.VarChar,50,linkman.Description),
										data.MakeInParam("@address",SqlDbType.VarChar,100,linkman.Address),
										data.MakeInParam("@family",SqlDbType.VarChar,200,linkman.Family),
			};
			return(data.RunProc ("sp_CM_AddLinkman",prams));
		}

		#endregion

		#region 修改联系人
		public void UpdateLinkman(Linkman linkman)
		{
			Database data = new Database();
			SqlParameter[] prams = {
									   data.MakeInParam("@ID",  SqlDbType.Int, 4, linkman.ID),
									   data.MakeInParam("@ClientID",  SqlDbType.Int, 4, linkman.ClientID),
									   data.MakeInParam("@name", SqlDbType.VarChar, 50,linkman.Name),
									   data.MakeInParam("@mobile", SqlDbType.VarChar,50, linkman.Mobile),
									   data.MakeInParam("@telephone", SqlDbType.VarChar,50, linkman.Telephone),
									   data.MakeInParam("@position", SqlDbType.VarChar,50, linkman.Position),
									   data.MakeInParam("@email",SqlDbType.VarChar,50,linkman.Email),
									   data.MakeInParam("@gender",SqlDbType.Bit,1,linkman.Gender),
									   data.MakeInParam("@description",SqlDbType.VarChar,50,linkman.Description),
									   data.MakeInParam("@address",SqlDbType.VarChar,100,linkman.Address),
									   data.MakeInParam("@family",SqlDbType.VarChar,200,linkman.Family),
			};
			data.RunProc ("sp_CM_UpdateLinkman",prams);

		}

		#endregion

		#region 删除联系人
		public bool DelLinkman(Linkman linkman)
		{
			Database data = new Database();
			SqlParameter[] prams = {
									   data.MakeInParam("@LinkmanID",  SqlDbType.Int, 4, linkman.ID),
									   
			};
			return(data.RunProc ("sp_CM_DelLinkman",prams)==0?true:false);
		}
		#endregion

		#region 得到所有联系人
		public SqlDataReader GetAllLinkman()
		{
			Database data = new Database();
			SqlDataReader dr = null;
			data.RunProc ("sp_CM_GetAllLinkman",out dr);
			return(dr);
		}

		public SqlDataReader GetAllContactLinkman()
		{
			Database data = new Database();
			SqlDataReader dr = null;
			data.RunProc ("sp_CM_GetAllContactLinkman",out dr);
			return(dr);
		}
		#endregion

		#region 根据id得到联系人
		public SqlDataReader GetLinkmanByID(string linkmanid)
		{
			Database data = new Database();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@LinkmanID",  SqlDbType.VarChar, 100, linkmanid)
								   };
			data.RunProc ("sp_CM_GetLinkmanByID",prams,out dr);
			return(dr);
		}

		public Linkman GetLinkmanStructByID(string linkmanid)
		{
			Database data = new Database();
			Linkman linkman = new Linkman();
			SqlDataReader dr = null;
			SqlParameter[] prams = {
									   data.MakeInParam("@LinkmanID",  SqlDbType.VarChar, 100, linkmanid)
								   };
			data.RunProc ("sp_CM_GetLinkmanByID",prams,out dr);
			while(dr.Read())
			{
				
				linkman.Address = dr["address"].ToString();
				linkman.ClientID = Int32.Parse(dr["clientid"].ToString());
				linkman.Description = dr["description"].ToString();
				linkman.Email = dr["email"].ToString();
				linkman.Family = dr["family"].ToString();
				linkman.Gender = Convert.ToBoolean(dr["gender"]);
				linkman.ID = Int32.Parse(dr["id"].ToString());
				linkman.Mobile = dr["mobile"].ToString();
				linkman.Name = dr["name"].ToString();
				linkman.Position = dr["position"].ToString();
				linkman.Telephone = dr["telephone"].ToString();
			}
			
			return(linkman);
		}
		#endregion

		#region 得到除了给定ids的其他联系人
		public SqlDataReader GetRemainLinkmen(string ids)
		{
			SqlDataReader dataReader = null;
			Database data = new Database();
			SqlParameter[] prams = {
									   data.MakeInParam("@ids",SqlDbType.VarChar,300,ids)
								   };

			try 
			{
				// run the stored procedure
				data.RunProc("sp_CM_GetRemainLinkmen", prams,out dataReader);
				return(dataReader);			
				
			}
			catch (Exception ex) 
			{
				Error.Log(ex.ToString());
				return null;
			}
			finally
			{
				data = null;
			}

		}
		#endregion

		#region 根据客户添加人得到与其有关的联系人
		public SqlDataReader GetLinkmenByClientAddmanID(int id)
		{
			SqlDataReader dataReader = null;
			Database data = new Database();
			SqlParameter[] prams = {
									   data.MakeInParam("@id",SqlDbType.Int,4,id)
								   };

			try 
			{
				// run the stored procedure
				data.RunProc("sp_CM_GetLinkmenByClientAddmanID", prams,out dataReader);
				return(dataReader);			
				
			}
			catch (Exception ex) 
			{
				Error.Log(ex.ToString());
				return null;
			}
			finally
			{
				data = null;
			}

		}

		#endregion

		#region 添加接触情况
		public int AddContact(Contact contact)
		{
			Database data = new Database();
			SqlParameter[] prams = {