第一:单独判断某个workno,创建的客群名称,客群详情
union
非自己创建客群,但有客户归属于workno得到结果
public void List_YXJH()
{
pagesize
//string rows = replaceDanger("rows");
当前页
//string page = replaceDanger("page");
//var sort = replaceDanger("sort") == "" ? "ZHXGRQ" : replaceDanger("sort");
//var order = replaceDanger("order") == "" ? "desc" : replaceDanger("order");
string where1 = "";
if (!string.IsNullOrEmpty(Request["TextBox1"]))
{
where1 += " and a.Name like '%" + replaceDanger("TextBox1").Trim() + "%'";
}
if (ui.RoleID.Equals(config.FHXTROLEGUID) || (ui.RoleID.Equals(config.GLYROLEGUID) && ui.UserType == "分行"))
{
//不用加条件,取整个行
}
else if (ui.RoleID.Equals(config.FHCPROLEGUID))
{
if (ui.FHCPType.Contains("理财") || ui.FHCPType.Contains("财富") || ui.FHCPType.Contains("私行"))
{
where1 += " and (1<>1";
if (ui.FHCPType.Contains("理财"))
{
where1 += " or a.Name like '%理财%'";
}
if (ui.FHCPType.Contains("财富"))
{
where1 += " or a.Name like '%财富%'";
}
if (ui.FHCPType.Contains("私行"))
{
where1 += " or a.Name like '%私行%'";
}
where1 += " or a.WorkNo='" + ui.UserName + "')";
}
}
else if (ui.RoleID.Equals(config.GLYROLEGUID) && ui.UserType != "分行")
{
where1 += " and NetWorkNo in (select name from split((select AllChildNetworkNO from wft_network where NetworkNO='" + ui.NetWorkNO + "'),','))";
}
else if (ui.RoleID == config.LCSROLEGUID || ui.RoleID == config.KGROLEGUID || ui.RoleID == config.XDROLEGUID)
{
where1 += " and FinancialPlannerWorkNO='" + ui.UserName + "'";
}
else
{
where1 += " and FinancialPlannerWorkNO in (select * from GetManagerList('" + ui.UserName + "'))";
}
// string sql = @"select a.ID,a.Name,a.WorkNO,a.Memo,convert(varchar,a.Addtime,120) Addtime,a.OrderID,
// sum(case when b.ID is not null then 1 else 0 end) counts,
// isnull(sum(case when b.ID is not null and b.ContactTime is not null then 1 end),0) complete,
// isnull(sum(case when b.ID is not null and b.ContactTime is null then 1 end),0) notComplete,
// case when sum(case when b.ID is not null then 1 else 0 end)=0 then 0 else isnull(sum(case when b.ContactTime is not null then 1 end),0)*100.00/sum(case when b.ID is not null then 1 else 0 end) end completePercent
// from WFT_CustomerGroup a left join WFT_CustomerGroupDetails b on a.ID=b.GroupID
// where type='1' @where1
// group by a.ID,a.Name,a.WorkNO,a.Memo,a.Addtime,a.OrderID
// order by isnull(a.OrderID,9999),a.Addtime desc".Replace("@where1", where1);
string sql = @"select * from
(
select a.ID,a.Name,a.WorkNO,a.Memo,convert(varchar,a.Addtime,120) Addtime,a.OrderID,
sum(case when b.ID is not null then 1 else 0 end) counts,
isnull(sum(case when b.ID is not null and b.ContactTime is not null then 1 end),0) complete,
isnull(sum(case when b.ID is not null and b.ContactTime is null then 1 end),0) notComplete,
case when sum(case when b.ID is not null then 1 else 0 end)=0 then 0 else isnull(sum(case when b.ContactTime is not null then 1 end),0)*100.00/sum(case when b.ID is not null then 1 else 0 end) end completePercent
from WFT_CustomerGroup a left join WFT_CustomerGroupDetails b on a.ID=b.GroupID
where type='1' and a.WorkNO='" + ui.UserName + @"'
group by a.ID,a.Name,a.WorkNO,a.Memo,a.Addtime,a.OrderID
union
select a.ID,a.Name,a.WorkNO,a.Memo,convert(varchar,a.Addtime,120) Addtime,a.OrderID,
sum(case when b.ID is not null then 1 else 0 end) counts,
isnull(sum(case when b.ID is not null and b.ContactTime is not null then 1 end),0) complete,
isnull(sum(case when b.ID is not null and b.ContactTime is null then 1 end),0) notComplete,
case when sum(case when b.ID is not null then 1 else 0 end)=0 then 0 else isnull(sum(case when b.ContactTime is not null then 1 end),0)*100.00/sum(case when b.ID is not null then 1 else 0 end) end completePercent
from WFT_CustomerGroup a left join WFT_CustomerGroupDetails b on a.ID=b.GroupID
where type='1' and a.WorkNO<>'" + ui.UserName + @"' @where1
group by a.ID,a.Name,a.WorkNO,a.Memo,a.Addtime,a.OrderID
) a order by isnull(a.OrderID,9999),a.Addtime desc".Replace("@where1", where1);
DataTable dt = conn.GetDataTable(sql);
JsonConvert<object> jc = new JsonConvert<object>();
Response.Write(jc.ToDataGrid(dt, dt.Rows.Count.ToString()));
}
第二:
exists()存在某条记录,某个结果,根据某个组的id
if (replaceDanger("kq") != "")
{
where += " and exists(select 1 from WFT_CustomerGroupDetails b1 where GroupID='" + replaceDanger("kq") + "' and b1.CustomerSerial=b.CustomerSerial)";
}
public void List_CallRecord() {
string rows = replaceDanger("rows");
//当前页
string page = replaceDanger("page");
string total = replaceDanger("total");
var sort = replaceDanger("sort") == "" ? "a.Addtime" : replaceDanger("sort"); //排序字段
if (sort == "Addtime" || sort == "CustomerName" || sort == "CustomerSerial" || sort == "CustomerMobile" || sort == "NetWorkNo")
{
sort = "b." + sort;
}
else if (sort == "CallType")
sort = "a.[Type]";
else if (sort == "通话状态")
sort = "a.[State]";
else if (sort == "CallAddTime")
sort = "a.AddTime";
else if (sort == "customerBankPatha")
sort = "a.BankPath";
else if (sort == "电话摘要")
sort = "a.FollowAbstract";
else if (sort == "摘要内容")
sort = "a.AbstractContent";
else if (sort == "Mobile")
sort = "a.Mobile";
else if (sort == "customerBankPatha")
sort = "d.BankPath";
else if (sort == "WorkNo")
sort = "a.WorkNo";
var order = replaceDanger("order") == "" ? "desc" : replaceDanger("order"); //排序顺序
string where = "1=1 ";//and len(b.CustomerName)<=4
string top = " top 1000 ";
if (!ui.RoleID.Equals(config.FHCPROLEGUID) && !ui.RoleID.Equals(config.FHXTROLEGUID))
{
//如果是支行管理员,就看不到,
if (ui.UserType.Contains("支行") && ui.RoleID == config.GLYROLEGUID)
{
//一级支行,二级支行,都是包括到网点级别
where += " and charindex(c.NetworkNO,(select AllChildNetworkNO from Tb_Common_User where WorkNO='" + ui.UserName + "'))>0";
}
else {
where += " and a.WorkNo = '" + ui.UserName + "'";
}
}
//if (!string.IsNullOrEmpty(Request["TextBox2"]) && string.IsNullOrEmpty(Request["TextBox3"]))
//{
// where += " and b.AddTime>='" + Request["TextBox2"] + "'";
//}
//else if (!string.IsNullOrEmpty(Request["TextBox3"]) && string.IsNullOrEmpty(Request["TextBox2"]))
//{
// where += " and b.AddTime<='" + Request["TextBox3"] + "'";
//}
//else if (!string.IsNullOrEmpty(Request["TextBox2"]) && !string.IsNullOrEmpty(Request["TextBox3"]))
//{
// where += " and (b.AddTime between '" + Request["TextBox2"] + "' and '" + Request["TextBox3"] + "')";
//}
if (!string.IsNullOrEmpty(Request["TextBox1"]))
{
where += " and (b.CustomerName like '%" + Request["TextBox1"] + "%' or b.CustomerIDcard like '%" + Request["TextBox1"] + "%' or b.CustomerSerial like '%" + Request["TextBox1"] + "%' or b.CustomerPhone like '%" + Request["TextBox1"] + "%')";
}
if (!string.IsNullOrEmpty(Request["LCS_txt"])) {
where += " and (c.RealName like '%" + Request["LCS_txt"] + "%' or c.WorkNo like '%" + Request["LCS_txt"] + "%')";
}
if (!string.IsNullOrEmpty(Request["Answer_txt"])) {
where += " and a.Type = '" + Request["Answer_txt"] + "'";
}
if (!string.IsNullOrEmpty(Request["CallState"])) {
where += " and a.State = '" + Request["CallState"] + "'";
}
if (!string.IsNullOrEmpty(Request["CustomerLevel"])) {
where += " and b.CustomerType in( '" + Request["CustomerLevel"].Replace(",","','") + "')";
}
if (!string.IsNullOrEmpty(Request["TextBox7"]))
{
where += " and a.AddTime>='" + Request["TextBox7"] + "'";
}
if (!string.IsNullOrEmpty(Request["TextBox8"]))
{
where += " and a.AddTime<='" + Request["TextBox8"] + " 23:59:59'";
}
//拨通时长
if (!string.IsNullOrEmpty(Request["btsc"]))
{
where += " and a.Times>=60";
}
where += getWangdianCondition("c.NetworkNo");
if (replaceDanger("kq") != "")
{
where += " and exists(select 1 from WFT_CustomerGroupDetails b1 where GroupID='" + replaceDanger("kq") + "' and b1.CustomerSerial=b.CustomerSerial)";
}
if (string.IsNullOrEmpty(Request["report"]))
{
string sql = @"declare @pagesize int;
declare @pageindex int;
set @pagesize = " + rows + @"
set @pageindex=" + page + @"
select * from(
select
ROW_NUMBER() over(order by @sort @order) as 'rownumber',
b.CustomerName,b.CustomerSerial,a.Mobile,
c.NetWorkNo EHRNetWorkNo,
case when charindex('/',c.BankPath)>0 then SUBSTRING(c.BankPath,0,charindex('/',c.BankPath)) end 员工分支行,
case when charindex('/',c.BankPath)>0 then reverse(substring(reverse(c.BankPath),1,charindex('/',reverse(c.BankPath)) - 1)) end 员工网点,
b.NetworkNo CUSNetWorkNo,
case when charindex('/',d.BankPath)>0 then SUBSTRING(d.BankPath,0,charindex('/',d.BankPath)) end 客户分支行,
case when charindex('/',d.BankPath)>0 then reverse(substring(reverse(d.BankPath),1,charindex('/',reverse(d.BankPath)) - 1)) end 客户网点,
convert(varchar(10),b.Addtime,120) Addtime,a.Times,
case when a.Type = 0 then '呼出' when a.Type = 1 then '呼入' else '' end CallType,
convert(varchar(20),a.AddTime,120) as CallAddTime,
a.WorkNo,c.RealName,a.ID,a.VideoFileUrl '本地录音文件',
a.FollowAbstract '电话摘要',
a.AbstractContent '摘要内容',
case when a.State = 0 then '未接' when a.State = 1 then '已接' end '通话状态'
from DW_PHONE a
left join WFT_Customer_All b on a.CustomerSerial = b.CustomerSerial
left join Tb_Common_User c on a.WorkNo = c.WorkNo
left join WFT_Network d on b.NetworkNo=d.NetworkNO
where @where
) as temp where rownumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize) order by rownumber".Replace("@where", where).Replace("@sort", sort).Replace("@order", order);
DataTable dt = conn.GetDataTable(sql);
JsonConvert<object> jc = new JsonConvert<object>();
if (page == "1")
{
total = conn.GetScalar(@"select count(*) from DW_PHONE a left join WFT_Customer_All b on a.CustomerSerial = b.CustomerSerial left join Tb_Common_User c on a.WorkNo = c.WorkNo left join WFT_Network d on b.NetWorkNo=d.NetworkNO where @where".Replace("@where", where));
if (Convert.ToInt32(total) > 1000) total = "1000";
}
Response.Write(jc.ToDataGrid(dt, total));
}
else {
string sql = @"select
b.CustomerName '客户姓名',b.CustomerSerial '客户号',a.Mobile '手机号',
a.Times '拨通时长',case when a.Type = 0 then '呼出' when a.Type = 1 then '呼入' else '' end '接听类型',
case when a.State = 0 then '未接' when a.State = 1 then '已接' end '通话状态',
a.WorkNo '员工号', c.RealName '员工姓名',
c.NetWorkNo 员工机构号,
case when charindex('/',c.BankPath)>0 then SUBSTRING(c.BankPath,0,charindex('/',c.BankPath)) end 员工分支行,
case when charindex('/',c.BankPath)>0 then reverse(substring(reverse(c.BankPath),1,charindex('/',reverse(c.BankPath)) - 1)) end 员工网点,
b.NetworkNo 客户机构号,
case when charindex('/',d.BankPath)>0 then SUBSTRING(d.BankPath,0,charindex('/',d.BankPath)) end 客户分支行,
case when charindex('/',d.BankPath)>0 then reverse(substring(reverse(d.BankPath),1,charindex('/',reverse(d.BankPath)) - 1)) end 客户网点,
a.FollowAbstract '电话摘要',
a.AbstractContent '摘要内容',
convert(varchar(20),a.AddTime,120) as '跟进时间'
from DW_PHONE a
left join WFT_Customer_All b on a.CustomerSerial = b.CustomerSerial
left join Tb_Common_User c on a.WorkNo = c.WorkNo
left join WFT_Network d on b.NetWorkNo=d.NetworkNO
where @where".Replace("@where", where);
DataTable dt_Data = conn.GetDataTable(sql, 120);
string fp = jj360.CommonMethods.ExportDataTable(dt_Data, Request["report"]);
Response.Write(fp);
}
}
/// <summary>