private void BindSubordinate()
{
//绑定下级人员
UDS.Components.Staff staff = new UDS.Components.Staff();
SqlDataReader dr_mysubordinate = staff.GetStaffFromPosition(Request.Cookies["Username"].Value, 2, 2);
ddl_MySubordinate.DataSource = dr_mysubordinate;
ddl_MySubordinate.DataTextField = "realname";
ddl_MySubordinate.DataValueField = "staff_id";
ddl_MySubordinate.DataBind();
dr_mysubordinate.Close();
// 加入自己
SqlDataReader dr_me = staff.GetStaffInfo(long.Parse(Request.Cookies["UserID"].Value));
string myrealname = "";
while (dr_me.Read())
{
myrealname = dr_me["realname"].ToString();
}
dr_me.Close();
ListItem listitem = new ListItem();
listitem.Text = myrealname;
listitem.Value = Request.Cookies["UserID"].Value;
ddl_MySubordinate.Items.Add(listitem);
if (ddl_MySubordinate.Items.Count != 0)
{
foreach (ListItem li in ddl_MySubordinate.Items)
{
if (li.Value == userid)
{
li.Selected = true;
}
else
{
li.Selected = false;
}
}
}
else
{
ddl_MySubordinate.Visible = false;
}
}
private void BindSubordinate()
{
//���¼���Ա
UDS.Components.Staff staff = new UDS.Components.Staff();
SqlDataReader dr_mysubordinate = staff.GetStaffFromPosition(Server.UrlDecode(Request.Cookies["UserName"].Value), 2, 2);
try
{
ddl_MySubordinate.DataSource = dr_mysubordinate;
ddl_MySubordinate.DataTextField = "realname";
ddl_MySubordinate.DataValueField = "staff_id";
ddl_MySubordinate.DataBind();
}
finally
{
dr_mysubordinate.Close();
}
// �����Լ�
SqlDataReader dr_me = staff.GetStaffInfo(long.Parse(Request.Cookies["UserID"].Value));
string myrealname = "";
try
{
while (dr_me.Read())
{
myrealname = dr_me["realname"].ToString();
}
}
finally
{
dr_me.Close();
}
ListItem listitem = new ListItem();
listitem.Text = myrealname;
listitem.Value = Request.Cookies["UserID"].Value;
ddl_MySubordinate.Items.Add(listitem);
if(ddl_MySubordinate.Items.Count!=0)
{
foreach(ListItem li in ddl_MySubordinate.Items)
{
if(li.Value==userid)
li.Selected = true;
else
li.Selected = false;
}
}
else
{
ddl_MySubordinate.Visible = false;
}
}
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)
{
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);
}
}