private void btn_OK_Click(object sender, System.EventArgs e) { if(lbx_search.Items.Count>0) { string searchstring = ""; string tmp = ""; DateTime begintime = new DateTime(); DateTime endtime = DateTime.Now; foreach(ListItem lt in lbx_search.Items) { string [] searcharr = new string[2]; searcharr = lt.Value.Split(':'); switch(searcharr[0].Trim()) { case "������Ա����": UDS.Components.Staff staff = new UDS.Components.Staff(); SqlDataReader dr_staffid = staff.GetStaffIDByRealName(searcharr[1]); tmp = "UDS_CM_ClientContact.MarketmanID='"; while(dr_staffid.Read()) { tmp += dr_staffid["staff_id"] + "' or UDS_CM_ClientContact.MarketmanID='"; } dr_staffid.Close(); if(tmp=="UDS_CM_ContactInfo.MarketmanID='") searchstring = "1=2"; else { tmp = tmp.Substring(0,tmp.Length-37); searchstring += "(" + tmp + ") and "; } break; case "�ͻ�����": UDS.Components.CM cm = new UDS.Components.CM(); SqlDataReader dr_clientid = cm.GetClientIDByName(searcharr[1]); tmp = "UDS_CM_ClientContact.ClientID='"; while(dr_clientid.Read()) { tmp += dr_clientid["ID"] + "' or UDS_CM_ClientContact.ClientID='"; } dr_clientid.Close(); if(tmp=="UDS_CM_ClientContact.ClientID='") searchstring = "1=2"; else { tmp = tmp.Substring(0,tmp.Length-34); searchstring += "(" + tmp + ") and "; } break; case "�ͻ����": searchstring += "UDS_CM_ClientContact.ClientID=" + searcharr[1] + " and "; break; case "���۽�": switch(searcharr[1]) { case "����": searchstring += "patindex('%trace%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "���": searchstring += "patindex('%boot%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "��Ʒ�Ƽ�": searchstring += "patindex('%commend%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "������": searchstring += "patindex('%requirement%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "�����ύ": searchstring += "patindex('%submit%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "����̸��": searchstring += "patindex('%negotiate%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "��Ŀʵʩ": searchstring += "patindex('%actualize%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "���ٷ���": searchstring += "patindex('%traceservice%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "��β��": searchstring += "patindex('%last%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; } break; case "�ɽ�Ԥ��": searchstring += "UDS_CM_ClientContact.BargainPrognosis='" + searcharr[1] + "' and "; break; case "�ѽ�Ǣ����": searchstring += "UDS_CM_ClientContact.ContactTimes=" + searcharr[1] + " and "; break; case "��Ǣ����ְ��": searchstring += "UDS_CM_Linkman.[Position]='" + searcharr[1] + "' and "; break; case "�״ν�Ǣʱ��": searchstring += "UDS_CM_ClientInfo.FirstContactTime='" + searcharr[1] + "' and "; break; case "���һ�ν�Ǣʱ��": searchstring += "UDS_CM_ClientInfo.ContactTime='" + searcharr[1] + "' and "; break; case "�´�Լ��ʱ��": searchstring += "UDS_CM_ClientInfo.NextContactTime='" + searcharr[1] + "' and "; break; case "����������¼": //�õ�������ʼ��(��һ -- ����) begintime = DateTime.Now; switch((int)DateTime.Now.DayOfWeek) { case 0: begintime = begintime.AddDays(-6); break; default: begintime = begintime.AddDays(1-(int)DateTime.Now.DayOfWeek); break; } searchstring += "(UDS_CM_ClientContact.updatetime between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; case "����������¼": //�õ�������ʼ�� begintime = DateTime.Now; begintime = begintime.AddDays(1-DateTime.Now.Day); searchstring += "(UDS_CM_ClientContact.updatetime between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; } } searchstring = "select * from UDS_CM_ClientInfo where ID IN (select UDS_CM_ClientInfo.ID from UDS_CM_ClientContact left join UDS_CM_ClientContact_Linkman ON UDS_CM_ClientContact.ID=UDS_CM_ClientContact_Linkman.ContactID left join UDS_CM_Linkman ON UDS_CM_ClientContact_Linkman.LinkmanID=UDS_CM_ClientContact_Linkman.id,UDS_CM_ClientInfo where UDS_CM_ClientContact.clientid=UDS_CM_ClientInfo.id and " + searchstring; if(searchstring.EndsWith("and ")) { searchstring = searchstring.Substring(0,searchstring.Length-4); } searchstring += " group by UDS_CM_ClientInfo.ID)"; DataSet ds = new DataSet(); UDS.Components.Staff staff1 = new UDS.Components.Staff(); SqlDataReader dr_Staff = staff1.GetAllStaffs(); DataTable dt_Staff = UDS.Components.Tools.ConvertDataReaderToDataTable(dr_Staff); dt_Staff.TableName = "Staff"; ds.Tables.Add(dt_Staff); UDS.Components.Database db = new UDS.Components.Database(); SqlParameter[] prams = { db.MakeInParam("@SQL",SqlDbType.NText,5000,searchstring) }; SqlDataReader dr = null; db.RunProc("sp_RunSql",prams,out dr); DataTable dt = UDS.Components.Tools.ConvertDataReaderToDataTable(dr); dt.TableName = "Client"; ds.Tables.Add(dt); ltl_Client.Text = dt.Rows.Count.ToString(); ds.Relations.Add("ClientAddMan_Staff",ds.Tables["Client"].Columns["AddManID"],ds.Tables["Staff"].Columns["Staff_ID"],false); dgrd_ContactList.DataSource = dt.DefaultView; dgrd_ContactList.DataBind(); } }
private void btn_OK_Click(object sender, System.EventArgs e) { string searchbound = ""; searchstring = ""; UDS.Components.Staff mysubordinate = new UDS.Components.Staff(); SqlDataReader dr_mysubordinate = mysubordinate.GetStaffFromPosition(Server.UrlDecode(Request.Cookies["UserName"].Value), 2, 2); try { switch (ddl_SearchBound.SelectedValue) { //��ѯ����ͱ��� case "1": //���¼���Ա while (dr_mysubordinate.Read()) { searchbound += "'" + dr_mysubordinate["Staff_ID"].ToString() + "',"; } dr_mysubordinate.Close(); searchbound += "'" + Request.Cookies["UserID"].Value + "'"; break; //��ѯ���� case "2": searchbound += "'" + Request.Cookies["UserID"].Value + "'"; break; //��ѯ���� case "3": while (dr_mysubordinate.Read()) { searchbound += "'" + dr_mysubordinate["Staff_ID"].ToString() + "',"; } dr_mysubordinate.Close(); searchbound = (searchbound == "") ? "" : searchbound.Substring(0, searchbound.Length - 1); break; } } finally { dr_mysubordinate.Close(); dr_mysubordinate.Dispose();} #region ���ݲ�ѯ����������ѯ��� if(lbx_search.Items.Count>0) { DateTime begintime = new DateTime(); DateTime endtime = DateTime.Now; foreach(ListItem lt in lbx_search.Items) { string [] searcharr = new string[2]; searcharr = lt.Value.Split(':'); switch(searcharr[0].Trim()) { case "�ͻ�����": searchstring += "UDS_CM_ClientInfo.name like '%" + searcharr[1] + "%' and "; break; case "�ͻ�����": switch(searcharr[1]) { case "�ն��û�": searchstring += "patindex('%terminal%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "������": searchstring += "patindex('%channal%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "����ϵ": searchstring += "patindex('%social%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "ý�幫��": searchstring += "patindex('%media%',UDS_CM_ClientInfo.type)>0" + " and "; break; } break; case "������ҵ": switch(searcharr[1]) { case "���ز�": searchstring += "patindex('%realty%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "IT": searchstring += "patindex('%IT%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��ҵó��": searchstring += "patindex('%business%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����": searchstring += "patindex('%telecom%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����ͨѶ": searchstring += "patindex('%post%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��ѯ����": searchstring += "patindex('%refer%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����ҵ": searchstring += "patindex('%travel%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��ͨ����": searchstring += "patindex('%bus%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����֤ȯ": searchstring += "patindex('%stock%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����": searchstring += "patindex('%insurance%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "˰��": searchstring += "patindex('%tax%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "����ҵ": searchstring += "patindex('%make%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "�ҵ�": searchstring += "patindex('%he%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��װ": searchstring += "patindex('%clothe%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "ʳƷ": searchstring += "patindex('%food%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "ҽҩ": searchstring += "patindex('%medicine%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��е": searchstring += "patindex('%mechanism%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "��������": searchstring += "patindex('%auto%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; } break; case "��ҵ����": switch(searcharr[1]) { case "����": searchstring += "patindex('%government%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "��Ӫ": searchstring += "patindex('%contry%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "��Ӫ": searchstring += "patindex('%privateowned%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "����": searchstring += "patindex('%oversea%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "���й�˾": searchstring += "patindex('%stock%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; } break; case "�ͻ���Դ": switch(searcharr[1]) { case "����������ϵ": searchstring += "patindex('%sellman%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "��ǰ��ʶ": searchstring += "patindex('%familiar%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "���˽���": searchstring += "patindex('%introduce%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "�ͻ�������ϵ": searchstring += "patindex('%client%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; } break; case "�ͻ����": searchstring += "UDS_CM_ClientInfo.id='" + searcharr[1] + "' and "; break; case "��ϵ��": //searchstring_linkman = "select * from UDS_CM_ClientInfo,UDS_CM_Linkman where UDS_CM_ClientInfo.id=UDS_CM_Linkman.clientid and UDS_CM_Linkman.name='" + searcharr[1] + "'" + " and "; searchstring += "UDS_CM_Linkman.name='" + searcharr[1] + "' and "; break; case "�绰": searchstring += "UDS_CM_ClientInfo.telephone='" + searcharr[1] + "' and "; break; case "������Ա": UDS.Components.Staff staff = new UDS.Components.Staff(); SqlDataReader dr_staffid = staff.GetStaffIDByRealName(searcharr[1]); string tmp = "UDS_CM_ClientInfo.addmanid='"; while(dr_staffid.Read()) { tmp += dr_staffid["staff_id"] + "' or UDS_CM_ClientInfo.addmanid='"; } dr_staffid.Close(); if(tmp=="UDS_CM_ClientInfo.addmanid='") searchstring = "1=2"; else { tmp = tmp.Substring(0,tmp.Length-32); searchstring += "(" + tmp + ") and "; } break; case "�������": searchstring += "datediff(d,UDS_CM_ClientInfo.birthday,'" + searcharr[1] + "')=0 and "; break; case "���������ͻ�": //�õ�������ʼ��(��һ -- ����) begintime = DateTime.Now; switch((int)DateTime.Now.DayOfWeek) { case 0: begintime = begintime.AddDays(-6); break; default: begintime = begintime.AddDays(1-(int)DateTime.Now.DayOfWeek); break; } searchstring += "(UDS_CM_ClientInfo.birthday between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; case "���������ͻ�": //�õ�������ʼ�� begintime = DateTime.Now; begintime = begintime.AddDays(1-DateTime.Now.Day); searchstring += "(UDS_CM_ClientInfo.birthday between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; } } searchstring = "select UDS_CM_ClientInfo.*,UDS_CM_Linkman.Name AS Linkman from UDS_CM_ClientInfo left join UDS_CM_Linkman on UDS_CM_Linkman.clientid=UDS_CM_ClientInfo.id and UDS_CM_Linkman.ID=UDS_CM_ClientInfo.ChiefLinkmanID where " + searchstring; // if(searchstring.EndsWith("and ")) // { // searchstring = searchstring.Substring(0,searchstring.Length-4); // } #endregion searchstring += "UDS_CM_ClientInfo.AddmanID IN (" + searchbound + ")"; ViewState["searchstring"] = searchstring; // Response.Write(searchstring); // Response.End(); Bind(); //Response.Write(searchstring); } }
private void btn_OK_Click(object sender, System.EventArgs e) { string searchbound = ""; searchstring = ""; UDS.Components.Staff mysubordinate = new UDS.Components.Staff(); SqlDataReader dr_mysubordinate = mysubordinate.GetStaffFromPosition(Request.Cookies["Username"].Value, 2, 2); switch (ddl_SearchBound.SelectedValue) { //查询下属和本人 case "1": //绑定下级人员 while (dr_mysubordinate.Read()) { searchbound += "'" + dr_mysubordinate["Staff_ID"].ToString() + "',"; } dr_mysubordinate.Close(); searchbound += "'" + Request.Cookies["UserID"].Value + "'"; break; //查询本人 case "2": searchbound += "'" + Request.Cookies["UserID"].Value + "'"; break; //查询下属 case "3": while (dr_mysubordinate.Read()) { searchbound += "'" + dr_mysubordinate["Staff_ID"].ToString() + "',"; } dr_mysubordinate.Close(); searchbound = (searchbound == "")?"":searchbound.Substring(0, searchbound.Length - 1); break; } #region 根据查询条件产生查询语句 if (lbx_search.Items.Count > 0) { DateTime begintime = new DateTime(); DateTime endtime = DateTime.Now; foreach (ListItem lt in lbx_search.Items) { string [] searcharr = new string[2]; searcharr = lt.Value.Split(':'); switch (searcharr[0].Trim()) { case "客户名称": searchstring += "UDS_CM_ClientInfo.name like '%" + searcharr[1] + "%' and "; break; case "客户分类": switch (searcharr[1]) { case "终端用户": searchstring += "patindex('%terminal%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "渠道商": searchstring += "patindex('%channal%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "社会关系": searchstring += "patindex('%social%',UDS_CM_ClientInfo.type)>0" + " and "; break; case "媒体公关": searchstring += "patindex('%media%',UDS_CM_ClientInfo.type)>0" + " and "; break; } break; case "所处行业": switch (searcharr[1]) { case "房地产": searchstring += "patindex('%realty%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "IT": searchstring += "patindex('%IT%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "商业贸易": searchstring += "patindex('%business%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "电信": searchstring += "patindex('%telecom%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "邮政通讯": searchstring += "patindex('%post%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "咨询服务": searchstring += "patindex('%refer%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "旅游业": searchstring += "patindex('%travel%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "交通运输": searchstring += "patindex('%bus%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "金融证券": searchstring += "patindex('%stock%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "保险": searchstring += "patindex('%insurance%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "税务": searchstring += "patindex('%tax%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "制造业": searchstring += "patindex('%make%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "家电": searchstring += "patindex('%he%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "服装": searchstring += "patindex('%clothe%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "食品": searchstring += "patindex('%food%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "医药": searchstring += "patindex('%medicine%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "机械": searchstring += "patindex('%mechanism%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; case "汽车制造": searchstring += "patindex('%auto%',UDS_CM_ClientInfo.Calling)>0" + " and "; break; } break; case "企业性质": switch (searcharr[1]) { case "政府": searchstring += "patindex('%government%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "国营": searchstring += "patindex('%contry%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "民营": searchstring += "patindex('%privateowned%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "外资": searchstring += "patindex('%oversea%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; case "上市公司": searchstring += "patindex('%stock%',UDS_CM_ClientInfo.CompanyProperty)>0" + " and "; break; } break; case "客户来源": switch (searcharr[1]) { case "销售主动联系": searchstring += "patindex('%sellman%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "以前认识": searchstring += "patindex('%familiar%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "熟人介绍": searchstring += "patindex('%introduce%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; case "客户主动联系": searchstring += "patindex('%client%',UDS_CM_ClientInfo.Customer)>0" + " and "; break; } break; case "客户编号": searchstring += "UDS_CM_ClientInfo.id='" + searcharr[1] + "' and "; break; case "联系人": //searchstring_linkman = "select * from UDS_CM_ClientInfo,UDS_CM_Linkman where UDS_CM_ClientInfo.id=UDS_CM_Linkman.clientid and UDS_CM_Linkman.name='" + searcharr[1] + "'" + " and "; searchstring += "UDS_CM_Linkman.name='" + searcharr[1] + "' and "; break; case "电话": searchstring += "UDS_CM_ClientInfo.telephone='" + searcharr[1] + "' and "; break; case "销售人员": UDS.Components.Staff staff = new UDS.Components.Staff(); SqlDataReader dr_staffid = staff.GetStaffIDByRealName(searcharr[1]); string tmp = "UDS_CM_ClientInfo.addmanid='"; while (dr_staffid.Read()) { tmp += dr_staffid["staff_id"] + "' or UDS_CM_ClientInfo.addmanid='"; } dr_staffid.Close(); if (tmp == "UDS_CM_ClientInfo.addmanid='") { searchstring = "1=2"; } else { tmp = tmp.Substring(0, tmp.Length - 32); searchstring += "(" + tmp + ") and "; } break; case "添加日期": searchstring += "datediff(d,UDS_CM_ClientInfo.birthday,'" + searcharr[1] + "')=0 and "; break; case "本周新增客户": //得到本周起始日(周一 -- 周日) begintime = DateTime.Now; switch ((int)DateTime.Now.DayOfWeek) { case 0: begintime = begintime.AddDays(-6); break; default: begintime = begintime.AddDays(1 - (int)DateTime.Now.DayOfWeek); break; } searchstring += "(UDS_CM_ClientInfo.birthday between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; case "本月新增客户": //得到本月起始日 begintime = DateTime.Now; begintime = begintime.AddDays(1 - DateTime.Now.Day); searchstring += "(UDS_CM_ClientInfo.birthday between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; } } searchstring = "select UDS_CM_ClientInfo.*,UDS_CM_Linkman.Name AS Linkman from UDS_CM_ClientInfo left join UDS_CM_Linkman on UDS_CM_Linkman.clientid=UDS_CM_ClientInfo.id and UDS_CM_Linkman.ID=UDS_CM_ClientInfo.ChiefLinkmanID where " + searchstring; // if(searchstring.EndsWith("and ")) // { // searchstring = searchstring.Substring(0,searchstring.Length-4); // } #endregion searchstring += "UDS_CM_ClientInfo.AddmanID IN (" + searchbound + ")"; ViewState["searchstring"] = searchstring; // Response.Write(searchstring); // Response.End(); Bind(); //Response.Write(searchstring); } }
private void btn_OK_Click(object sender, System.EventArgs e) { if (lbx_search.Items.Count > 0) { string searchstring = ""; string tmp = ""; DateTime begintime = new DateTime(); DateTime endtime = DateTime.Now; foreach (ListItem lt in lbx_search.Items) { string [] searcharr = new string[2]; searcharr = lt.Value.Split(':'); switch (searcharr[0].Trim()) { case "销售人员姓名": UDS.Components.Staff staff = new UDS.Components.Staff(); SqlDataReader dr_staffid = staff.GetStaffIDByRealName(searcharr[1]); tmp = "UDS_CM_ClientContact.MarketmanID='"; while (dr_staffid.Read()) { tmp += dr_staffid["staff_id"] + "' or UDS_CM_ClientContact.MarketmanID='"; } dr_staffid.Close(); if (tmp == "UDS_CM_ContactInfo.MarketmanID='") { searchstring = "1=2"; } else { tmp = tmp.Substring(0, tmp.Length - 37); searchstring += "(" + tmp + ") and "; } break; case "客户名称": UDS.Components.CM cm = new UDS.Components.CM(); SqlDataReader dr_clientid = cm.GetClientIDByName(searcharr[1]); tmp = "UDS_CM_ClientContact.ClientID='"; while (dr_clientid.Read()) { tmp += dr_clientid["ID"] + "' or UDS_CM_ClientContact.ClientID='"; } dr_clientid.Close(); if (tmp == "UDS_CM_ClientContact.ClientID='") { searchstring = "1=2"; } else { tmp = tmp.Substring(0, tmp.Length - 34); searchstring += "(" + tmp + ") and "; } break; case "客户编号": searchstring += "UDS_CM_ClientContact.ClientID=" + searcharr[1] + " and "; break; case "销售阶段": switch (searcharr[1]) { case "跟踪": searchstring += "patindex('%trace%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "启动": searchstring += "patindex('%boot%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "产品推荐": searchstring += "patindex('%commend%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "需求定义": searchstring += "patindex('%requirement%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "方案提交": searchstring += "patindex('%submit%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "商务谈判": searchstring += "patindex('%negotiate%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "项目实施": searchstring += "patindex('%actualize%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "跟踪服务": searchstring += "patindex('%traceservice%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; case "收尾款": searchstring += "patindex('%last%',UDS_CM_ClientContact.curstatus)>=0" + " and "; break; } break; case "成交预估": searchstring += "UDS_CM_ClientContact.BargainPrognosis='" + searcharr[1] + "' and "; break; case "已接洽次数": searchstring += "UDS_CM_ClientContact.ContactTimes=" + searcharr[1] + " and "; break; case "接洽对象职务": searchstring += "UDS_CM_Linkman.[Position]='" + searcharr[1] + "' and "; break; case "首次接洽时间": searchstring += "UDS_CM_ClientInfo.FirstContactTime='" + searcharr[1] + "' and "; break; case "最后一次接洽时间": searchstring += "UDS_CM_ClientInfo.ContactTime='" + searcharr[1] + "' and "; break; case "下次约见时间": searchstring += "UDS_CM_ClientInfo.NextContactTime='" + searcharr[1] + "' and "; break; case "本周新增记录": //得到本周起始日(周一 -- 周日) begintime = DateTime.Now; switch ((int)DateTime.Now.DayOfWeek) { case 0: begintime = begintime.AddDays(-6); break; default: begintime = begintime.AddDays(1 - (int)DateTime.Now.DayOfWeek); break; } searchstring += "(UDS_CM_ClientContact.updatetime between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; case "本月新增记录": //得到本月起始日 begintime = DateTime.Now; begintime = begintime.AddDays(1 - DateTime.Now.Day); searchstring += "(UDS_CM_ClientContact.updatetime between '" + begintime.ToShortDateString() + "' and '" + endtime.ToShortDateString() + "') and "; break; } } searchstring = "select * from UDS_CM_ClientInfo where ID IN (select UDS_CM_ClientInfo.ID from UDS_CM_ClientContact left join UDS_CM_ClientContact_Linkman ON UDS_CM_ClientContact.ID=UDS_CM_ClientContact_Linkman.ContactID left join UDS_CM_Linkman ON UDS_CM_ClientContact_Linkman.LinkmanID=UDS_CM_ClientContact_Linkman.id,UDS_CM_ClientInfo where UDS_CM_ClientContact.clientid=UDS_CM_ClientInfo.id and " + searchstring; if (searchstring.EndsWith("and ")) { searchstring = searchstring.Substring(0, searchstring.Length - 4); } searchstring += " group by UDS_CM_ClientInfo.ID)"; DataSet ds = new DataSet(); UDS.Components.Staff staff1 = new UDS.Components.Staff(); SqlDataReader dr_Staff = staff1.GetAllStaffs(); DataTable dt_Staff = UDS.Components.Tools.ConvertDataReaderToDataTable(dr_Staff); dt_Staff.TableName = "Staff"; ds.Tables.Add(dt_Staff); UDS.Components.Database db = new UDS.Components.Database(); SqlParameter[] prams = { db.MakeInParam("@SQL", SqlDbType.NText, 5000, searchstring) }; SqlDataReader dr = null; db.RunProc("sp_RunSql", prams, out dr); DataTable dt = UDS.Components.Tools.ConvertDataReaderToDataTable(dr); dt.TableName = "Client"; ds.Tables.Add(dt); ltl_Client.Text = dt.Rows.Count.ToString(); ds.Relations.Add("ClientAddMan_Staff", ds.Tables["Client"].Columns["AddManID"], ds.Tables["Staff"].Columns["Staff_ID"], false); dgrd_ContactList.DataSource = dt.DefaultView; dgrd_ContactList.DataBind(); } }