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();
}
}