agentwork.cs

人力资源管理系统 读者须安装好IIS和Microsoft Visual Stutio.NET相关开发环境

using System;
using System.IO;
using System.Web;
using System.Web.SessionState;
using System.Web.UI.WebControls;
using System.Collections;
using System.Xml;
using System.Data;
using System.Drawing;

namespace DataAccess
{
	/// <summary>
	/// Agentwork 的摘要说明。
	/// </summary>
	public class Agentwork
	{
		public Agentwork()
		{
		}

//		static private Env _env=null;
		//		static string WorkArea="./agents";
		//		static string PictureLink="./PictureLink";	//图片链接
		//		static string TechLink="./TechLink";		//技术链接
		//		static string ScriptLink = "./ScriptLink";	//Script链接
		//		static string TempletLink = "./TempletLink";	//模板链接
		//		static string CaseAttach = "./CaseAttach";	//事件附件
		//		static string PromotionLink = "./PromotionLink";	//Promotion链接
		//		static string XmlLink = "XmlLink";

//		static string ProfilePath="./Profiles";

		private static System.Data.DataTable _dtname=new System.Data.DataTable("Names");
		private static System.Data.DataTable _dtuser=new System.Data.DataTable("User");
		private static System.Data.DataTable _dtgrp =new System.Data.DataTable("Group");
		private static System.Data.DataTable _dtcard=new System.Data.DataTable("CardType");
		private static System.Data.DataTable _dtprov=new System.Data.DataTable("Province");
		private static System.Data.DataTable _dtpdtp=new System.Data.DataTable("ProdType");
		private static System.Data.DataTable _dtcate=new System.Data.DataTable("Category");

		private static  string sqladdr="Select spellid,provinceid,cityid,name,full_name,spell_code,zip,telephone,companyid,fun_getmailtype(companyid) mailtype from ems ";
		private static  string sqlprov="select provinceid,chinese from province order by provinceid";
		private static  string sqluser="select usrid,name,defgrp from usr order by usrid";
		private static	string sqlgrp ="select grpid,grpname,areacode,type from grp order by grpname";
		private static  string sqlcard="select cardtypeid,type1,type2,cardname,svalue from cardtype";
		private static  string sqlname="select tid,id,dsc from names t order by tid,id";
		private static	string sqlpdtp="select * from producttype order by prodrecid,typeid";
		private static	string sqlprod="select prodid,prodname,scode,unitprice from product where status='-1' ";
		private static	string sqlpayc="select * from card ";
		private static	string sqlodno="select contactid from orderhist ";
		private static	string sqlcate="select categoryid,category,item,linkid from category order by category,categoryid ";
		private static	string sqlback="select contactid from phone ";
		private static	string sqlblack="select id from phnblacklist where phn2='";//黑名单

		private static	string sqlreturn="select * from returnreceipt t where ";//回执单客户

		//通话结束必须选择产品的几种类型
		private static	string strcall="Order,inquire,accuse,add,cancel,latent,agency";

		//取SEQ
		private static	string seqsql1="SELECT SEQ";
		private static	string seqsql2=".NEXTVAL FROM DUAL";

		//媒体查询
		private static	string sqlmedia1=@"Select mediaplan.planid,
												mediacompany.name,
												product.prodname,
												promotion.dsc,
												To_Char(mediaplan.starttm, 'YYYY/MM/DD HH24:mi:ss') starttm,
												mediaplan.weekplan
											from mediaplan, mediacompany, promotion, product 
											";

		private static	string sqlmedia2=@"	union (Select mediaplan.planid,
														mediacompany.name,
														product.prodname,
														promotion.dsc,
														To_Char(mediaplan.starttm, 'YYYY/MM/DD HH24:mi:ss') starttm,
														mediaplan.weekplan
													from mediaplan, mediacompany, promotion, product
													where mediaplan.promotionid is null and
														MEDIAPLAN.PROMOTIONID = PROMOTION.PROMOTIONID(+) and
														MEDIAPLAN.COMPANY = MEDIACOMPANY.COMPANYID and
														PROMOTION.PRODID = PRODUCT.RECID(+)) order by starttm";


		//业绩查询 
		private static	string sqlyjod=@"select a.orderid,a.contactid,a.paycontactid,a.getcontactid,a.crdt,
											a.mailtype,a.mailprice,a.prodprice,a.ordertype,a.status,a.bill,a.media,a.note,a.cardtype,a.cardnumber,b.dsc
										from orderhist a,topic b
										where a.contactid=b.contactid(+) and crusr = '";
		
		private static	string sqlyjad=@"select a.name, a.sex, b.zip, b.address,c.name city
										from contact a, address b,ems c
										where a.contactid = b.contactid and b.area=c.spellid and a.contactid = '";

		private static	string sqlyjct="select a.prodid,a.prodname,a.producttype,a.uprice,a.upnum,a.sprice,a.spnum,a.status,a.soldwith from orderdet a where orderid= '";

		//综合查询

		//查询订单
		private static	string sqlStoorder=@"select orderhist.orderid as 订单号,
												contact.contactid as 客户编号,
												contact.name as 客户姓名,
												ems.name as 城市,
												decode(phone.phn1, '0', '', phone.phn1) ||
												decode(phone.phn1, '', '', '0', '', '-') || phone.phn2 ||
												decode(phone.phn3, '', '', '0', '', '-') ||
												decode(phone.phn3, '0', '', phone.phn3) as 电话,
												address.address as 客户地址,
												orderhist.crusr as 员工号,
												orderhist.result as 订单反馈,
												orderhist.status as 订单状态,
												orderhist.crdt as 订购日期,
												orderhist.fbdt as 反馈日期,
												orderhist.mailtype as 订购方式,
												orderhist.paytype as 付款方式,
												orderhist.prodprice as 货款总额,
												orderhist.mailprice as 运费,
												orderhist.totalprice as 总金额,
												decode(orderhist.bill, '1', '需要', '不需要') as 要发票,
												orderhist.mdusr as 修改员工,
												orderhist.note as 订单备注,
												topic.dsc as 客户备注,
												orderhist.mailid as 邮件编号,
												orderhist.parcdt as 投递日期
											From orderhist, CONTACT, ems, PHONE, Address, Topic
											where orderhist.contactid = contact.contactid and
												orderhist.spellid = ems.spellid(+) and
												phone.phoneid =
												(select min(phoneid)
													from phone
													where phone.contactid = orderhist.getcontactid) and
												orderhist.contactid = address.contactid and
												topic.contactid(+) = contact.contactid ";

		//根据产品查客户
		private static	string sqlSpdtocu=@"select orderhist.orderid as 订单号,
												contact.contactid as 客户编号,
												contact.name as 客户姓名,
												orderdet.status as 产品状态,
												decode(contact.sex, '1', '男', '女') as 性别,
												ems.name as 城市,
												address.address as 地址,
												decode(phone.phn1, '0', '', phone.phn1) ||
												decode(phone.phn1, '', '', '0', '', '-') || phone.phn2 ||
												decode(phone.phn3, '', '', '0', '', '-') ||
												decode(phone.phn3, '0', '', phone.phn3) as 电话,
												/*contact.ages as 年龄,contact.income as 收入水平, card.dsc as 身份证,contact.occupation as 职业,*/
												orderhist.crdt as 订购日期,
												product.prodname as 产品名称
											from orderhist, orderdet, contact, address, phone, product, ems /*,card*/
											where orderhist.contactid = contact.contactid and
												orderhist.spellid = ems.spellid and
												phone.phoneid =
												(select min(phoneid)
													from phone
													where phone.contactid = orderhist.contactid) and
												orderhist.contactid = address.contactid and
												orderhist.OrderID = orderdet.OrderID
												/*and orderhist.contactid=card.contactid and card.type='001'*/
												and orderdet.prodid = product.prodid ";

		//根据订单查购物车
		private static	string sqlSodtoct=@"select orderdet.orderid 订单号,
												orderdet.prodid 产品编号,
												orderdet.status 状态,
												trunc(orderdet.fbdt) 反馈日期,
												decode(orderdet.reckoning, 'Y', '是', '否') 结帐状态,
												orderdet.uprice 单价,
												orderdet.upnum 单价数量,
												orderdet.sprice 优惠单价,
												orderdet.spnum 优惠数量,
												orderdet.soldwith 销售手段,
												trunc(orderdet.reckoningdt) 结帐日期,
												orderdet.payment 实际付款,
												orderdet.breason 退回原因,
												orderdet.mdusr 修改人
											from orderdet
											Where orderdet.orderid = '";

		//座席当天咨询电话 2005-8-10 cl
		private static	string sqltdcall="select endtm,ani,result,callnote,contactid from callhist ";
				


		//******************************************************************
		//座席相关属性 2005-6-9 陈立
		//******************************************************************

		static public Agent ag
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if( sess["Agent"] == null )
				{
					Agent a=new Agent();
					Agentwork.ag=a;
				}
				return (Agent)sess["Agent"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if(value==null)
					sess.Remove("Agent");
				else
					sess["Agent"]=value;
			}
		}


		//******************************************************************
		//客户相关属性 2005-5-31 陈立
		//******************************************************************

		static public Contact cu
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if( sess["Contact"] == null )
				{
					Contact con=new Contact();
					Agentwork.cu=con;
				}
				return (Contact)sess["Contact"];
			}

			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if(value==null)
					sess.Remove("Contact");
				else
					sess["Contact"]=value;
			}
		}

		static public Contact cupay
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if( sess["ContactPay"] == null )
				{
					Contact con=new Contact();
					Agentwork.cupay=con;
				}
				return (Contact)sess["ContactPay"];
			}

			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if(value==null)
				{
					sess.Remove("ContactPay");
				}
				else
				{
					sess["ContactPay"]=value;
				}
			}
		}

		static public Contact cuget
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if( sess["ContactGet"] == null )
				{
					Contact con=new Contact();
					Agentwork.cuget=con;
				}
				return (Contact)sess["ContactGet"];
			}

			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				if(value==null)
				{
					sess.Remove("ContactGet");
				}
				else
				{
					sess["ContactGet"]=value;
				}
			}
		}


		/// <summary>
		/// 客户标示
		/// con:订购人 pay:付款人 get:收货人
		/// </summary>
		static public string SelectedCon
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["SelectedCon"]==null)
					sess["SelectedCon"]="con";
				return (string)sess["SelectedCon"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["SelectedCon"]=value;
			}
		}


		/// <summary>
		/// 收货人付款人弹出页客户标示
		/// 根据此id显示客户信息
		/// </summary>
		static public string SelectedConID
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				return (string)sess["SelectedConID"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["SelectedConID"]=value;
			}
		}


		/// <summary>
		/// 订单管理页面显示
		/// 任务列表页面切换到工作平台时用
		/// </summary>
		static public int SelectedPage
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["SelectedPage"]==null)
					sess["SelectedPage"]=0;
				return (int)sess["SelectedPage"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["SelectedPage"]=value;
			}
		}

		//*****************************************************************
		//呼叫相关数据

		static public string STTM
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["STTM"]==null)
					sess["STTM"]="";
				return (string)sess["STTM"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["STTM"]=value;
			}
		}

		static public string CallType
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["CallType"]==null)
					sess["CallType"]="";
				return (string)sess["CallType"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["CallType"]=value;
			}
		}

		static public string Ani_Phn1
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["Ani_Phn1"]==null)
					sess["Ani_Phn1"]="";
				return (string)sess["Ani_Phn1"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["Ani_Phn1"]=value;
			}
		}

		static public string Ani_Phn2
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["Ani_Phn2"]==null)
					sess["Ani_Phn2"]="";
				return (string)sess["Ani_Phn2"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["Ani_Phn2"]=value;
			}
		}

		static public string LatentID
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["LatentID"]==null)
					sess["LatentID"]="";
				return (string)sess["LatentID"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["LatentID"]=value;
			}
		}

		static public string CanSearch
		{
			get
			{
				HttpSessionState sess=HttpContext.Current.Session;
				
				if(sess["CanSearch"]==null)
					sess["CanSearch"]="";
				return (string)sess["CanSearch"];
			}
			set
			{
				HttpSessionState sess=HttpContext.Current.Session;
				sess["CanSearch"]=value;
			}
		}

		//******************************************************************
		//公用静态变量 2005-5-31 陈立
		//******************************************************************

		#region Names表
		static public DataTable Names
		{
			get
			{
				IniNames();
				return  _dtname;
			}
		}

		static public void IniNames()
		{
			if(_dtname.Rows.Count==0)
			{
				OLEDB mydb=Agentwork.db;
				DataTable dt=new DataTable();
				mydb.FillTable(dt,sqlname);
				_dtname=dt;
			}
		}


		static public string GetSingleName(string tid,string id)
		{
			string dsc="";

			for(int i=0;i<Names.Rows.Count;i++)
			{
				if(Names.Rows[i]["tid"].ToString()==tid&&Names.Rows[i]["id"].ToString()==id)
				{
					dsc=Names.Rows[i]["dsc"].ToString();
					break;
				}

			}

			return dsc;

		}

		#endregion

		#region 证件

		static public DataTable CardType
		{
			get
			{
				IniCardType();
				return  _dtcard;
			}
		}

		static public void IniCardType()
		{
			if(_dtcard.Rows.Count==0)
			{
				OLEDB mydb=Agentwork.db;
				DataTable dt=new DataTable();
				mydb.FillTable(dt,sqlcard);
				dt.PrimaryKey=new System.Data.DataColumn[] {dt.Columns["cardtypeid"]};
				_dtcard=dt;
			}
		}

		static public bool IsRightCard(string cardtypeid,string cardnum,string date)
		{
			if(CardType.Rows.Count==0)return false;

			DataRow dr=CardType.Rows.Find(cardtypeid);
			if(dr==null)return false;

			bool bl=false;

			
			//号码验证
			if(dr["type1"].ToString()=="1")
			{
				if(date!=string.Empty)
				{
					//身份证有效期验证可填可不填
					bl=CheckedData.DataCheck(date+"-1",DataSort.Date);
					if(!bl)return false;

					bl=IsRightDate(date);
					if(!bl)return false;
				}

				//身份证
				if(cardnum.Length==15)
				{
					bl=CheckedData.DataCheck(cardnum,DataSort.Integer);
					return bl;

				}
				else if(cardnum.Length==18)
				{
					//验证成功
					bl=CheckedData.DataCheck(cardnum.Substring(0,17),DataSort.Integer);
					if(bl)
					{
						
						bl=CheckedData.DataCheck(cardnum.Substring(17,1),DataSort.Integer);
						if(bl)//以数字结尾
							return true;
						else if(cardnum.Substring(17,1).ToUpper()=="X")//以x结尾
							return true;
						else return false;
						
					}
					//最末位不为“X”及数字的
					else return false;
					
				}
				else
				{
					//位数不正确
					return false;
				}
			}
			else if(dr["type1"].ToString()=="2")
			{
				//信用卡

				bl=CheckedData.DataCheck(cardnum,DataSort.Integer);
				if(!bl)return false;

				//有效期验证(证件必须要有有效期且要大于当前月)
				bl=CheckedData.DataCheck(date+"-1",DataSort.Date);
				if(!bl)return false;

				bl=IsRightDate(date);
				if(!bl)return false;
			

				if(cardnum.Length>=16)
				{
					if(dr["type2"].ToString()!="10")
					{
						//国内卡
						string[] stv=dr["svalue"].ToString().Split(',');
						for(int j=0;j<stv.Length;j++)
						{
							if(cardnum.StartsWith(stv[j])==true)
							{
								//验证成功
								return true;
							}
						}

						return false;
					}
					else
					{
						//不知道国际卡，visa卡的规则
						return true;
					}
				}
				else
				{
					//卡号码不足16位
					return false;
				}
			}
			else
			{
				//保留

				return false;
			}

		}