protected void Page_Load(object sender, EventArgs e)
{
var accountId = Request.QueryString["accountId"];
if (!string.IsNullOrEmpty(accountId))
{
account = comBll.GetCompany(long.Parse(accountId));
}
if (account == null)
{
Response.Write("<script>alert('未获取到客户信息');window.close();</script>");
return;
}
accLocation = new DAL.crm_location_dal().GetLocationByAccountId(account.id);
if (accLocation != null)
{
var disDal = new DAL.d_district_dal();
if (accLocation.country_id != null)
{
var country = new DAL.d_country_dal().FindById((long)accLocation.country_id);
countryName = country != null ? country.country_name_display : "";
}
var province = disDal.FindById((long)accLocation.province_id);
provinceName = province != null ? province.name : "";
var city = disDal.FindById((long)accLocation.city_id);
cityName = city != null ? city.name : "";
var district = disDal.FindById((long)accLocation.district_id);
districtName = district != null ? district.name : "";
}
if (account.resource_id != null)
{
accMan = new DAL.sys_resource_dal().FindNoDeleteById((long)account.resource_id);
}
startDate = DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01");
endDate = startDate.AddMonths(1).AddDays(-1);
contractMoney = new DAL.crm_account_deduction_dal().GetContractSum(account.id, startDate, endDate);
projectMoney = new DAL.crm_account_deduction_dal().GetProjectSum(account.id, startDate, endDate);
if (account.classification_id != null)
{
accClass = new DAL.d_account_classification_dal().FindNoDeleteById((long)account.classification_id);
}
}
string GetHtml(List <crm_account> accountList)
{
StringBuilder htmlText = new StringBuilder();
foreach (var account in accountList)
{
htmlText.Append($"<div style='margin-left: calc(50 % -300px); margin-top:20px;margin-bottom:200px;'>");
htmlText.Append($"<table cellspacing='0' cellpadding='0' style='width: 600px;'><tr><td style='font-weight:600;'>{account.name}<br />");
crm_location accLocation = new DAL.crm_location_dal().GetLocationByAccountId(account.id);
if (accLocation != null)
{
var disDal = new DAL.d_district_dal();
d_country country = null;
if (accLocation.country_id != null)
{
country = new DAL.d_country_dal().FindById((long)accLocation.country_id);
}
var province = disDal.FindById((long)accLocation.province_id);
var city = disDal.FindById((long)accLocation.city_id);
var district = disDal.FindById((long)accLocation.district_id);
htmlText.Append($"{country?.country_name_display} {province?.name} {city?.name} {district?.name}");
htmlText.Append($"<br />{accLocation.address} {accLocation.additional_address}");
}
htmlText.Append($"</td><td rowspan='3' style='text-align:center; vertical-align: middle;width:40 %;min-width:240px;'>");
if (account.classification_id != null)
{
var accClass = new DAL.d_account_classification_dal().FindNoDeleteById((long)account.classification_id);
htmlText.Append($"<img src='..{accClass?.icon_path}' />");
}
htmlText.Append("</td></tr>");
sys_resource accMan = null;
if (account.resource_id != null)
{
accMan = userBll.GetResourceById((long)account.resource_id);
}
htmlText.Append($"<tr><td>{accMan?.name}<br /><br />{accMan.home_phone}</td></tr> ");
htmlText.Append($"<tr><td>报表周期<br /><br />{startDate.ToString("yyyy-MM-dd")} - {endDate.ToString("yyyy-MM-dd")}</td></tr> ");
#region 合同，项目 表格查询
string contractSql = $@"select ifnull(contract_name,'汇总'),sum(dollars) from
(select ifnull(contract_name, ' 无合同')contract_name, dollars, posted_date, account_id, account_manager_id, territory_id, contract_type_id
from v_posted_all a)t
where posted_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' and account_id = {account.id.ToString()}
GROUP BY contract_name with ROLLUP
";
if (!ContractByName)
{
contractSql = $@"select ifnull(contract_type_name,'汇总'),sum(dollars) from
(select dollars,posted_date,account_id,account_manager_id,territory_id,contract_type_id ,
ifnull((select name from d_general where id=a.contract_type_id),' 无合同')contract_type_name
from v_posted_all a)t
where posted_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' and account_id={account.id.ToString()}
GROUP BY contract_type_name with ROLLUP
";
}
htmlText.Append($"<tr><td colspan='2'><div style='width:100%;height:100%;'><br /><span class='Boild' style='font-size:11pt;'>财务回顾</span><br /><div style='padding-left:20px;'><br />");
htmlText.Append($"<span class='Boild'>合同</span><div style='margin-right:20px;'><table cellspacing ='0' cellpadding = '0' style = 'width:100%;' class='NoBoard'>");
var contractTable = comBll.GetTable(contractSql);
if (contractTable != null && contractTable.Rows.Count > 0)
{
foreach (DataRow item in contractTable.Rows)
{
htmlText.Append($"<tr><td style='text-align:left;padding-left:10px;'>{item[0]}</td><td style ='text-align:right;' > {item[1]} </td></tr>");
}
}
htmlText.Append(" </table></div><br /><br /><br /><br />");
htmlText.Append($"<span class='Boild'>项目</span><div style='margin-right:20px;'><table cellspacing ='0' cellpadding = '0' style = 'width:100%;' class='NoBoard'>");
var projectTable = comBll.GetTable($@"select ifnull(project_name,'汇总'),sum(dollars) from v_posted_all where posted_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' and account_id={account.id.ToString()} and project_name is not null
GROUP BY project_name with ROLLUP
");
if (projectTable != null && projectTable.Rows.Count > 0)
{
foreach (DataRow item in projectTable.Rows)
{
htmlText.Append($"<tr><td style='text-align:left;padding-left:10px;'>{item[0]}</td><td style ='text-align:right;' > {item[1]} </td></tr>");
}
}
htmlText.Append($"</table></div><br /><br /></div>");
#endregion
#region 工单按照优先级汇总
var priorityTicketTable = comBll.GetTable($@"select priority_type_name,count(1),round(avg(duaration_hours),2),round(avg(Business_Hours),2)
from(
select t.priority_type_id,(select name from d_general where id=t.priority_type_id)priority_type_name ,
f_get_sla_hours(null,(select id from sys_organization_location where is_default=1 and delete_time=0 limit 1),fs_unix2date(t.create_time), fs_unix2date(t.Date_Completed) )Business_Hours,
(t.Date_Completed - t.create_time)/1000/3600 duaration_hours
from sdk_task t where type_id=1809 and status_id=1894 and recurring_ticket_id is null and account_id={account.id.ToString()} and fs_unix2date(Date_Completed) between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}'
)t GROUP BY t.priority_type_name
");
if (priorityTicketTable != null && priorityTicketTable.Rows.Count > 0)
{
htmlText.Append($"<span class'Boild' style='font-size:11pt;'>工单按优先级汇总</span><div style='padding: 20px;'><table cellspacing='0' cellpadding='0' style='width: 100 %; border-collapse:collapse;'>");
htmlText.Append("<tr><td> 优先级 </td><td>工单数</td><td>占比</td><td>平均耗时</td><td>平均耗时(工作时间)</td></tr>");
foreach (DataRow item in priorityTicketTable.Rows)
{
htmlText.Append($"<tr><td>{item[0]}</td><td>{item[1]}</td><td>{item[2]}</td><td>{item[3]}</td></tr>");
}
htmlText.Append($"</table></div>");
}
htmlText.Append($"</div></td></tr></table>");
#endregion
htmlText.Append("<br /><br />");
var issuePriottyTable = comBll.GetTable($@"select Issue_Type_name ,Sub_Issue_Type_name ,sum(严重) ,sum(高) ,sum(中) ,sum(低),count(1)
from(
select Sub_Issue_Type_ID,(select name from d_general where id=Sub_Issue_Type_ID)Sub_Issue_Type_name,(select name from d_general where id=Issue_Type_ID)Issue_Type_name,
if(priority_type_id=1883,1,0)严重,if(priority_type_id=1884,1,0)高,if(priority_type_id=1885,1,0)中,if(priority_type_id=1886,1,0)低
from sdk_task t where type_id=1809 and status_id=1894 and recurring_ticket_id is null and account_id={account.id.ToString()} and fs_unix2date(Date_Completed) between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}'
)t GROUP BY Issue_Type_name ,Sub_Issue_Type_name with ROLLUP
");
if (issuePriottyTable != null && issuePriottyTable.Rows.Count > 0)
{
htmlText.Append($"<span class'Boild' style='font-weight:600;'>工单按问题类型和优先级汇总</span><br /><br /><div><table cellspacing='0' cellpadding='0' style='width:600px;border-collapse:collapse'>");
htmlText.Append($"<tr><td>问题类型</td><td>子问题类型</td><td>严重</td><td>高</td><td>中</td><td>低</td><td>汇总</td></tr>");
foreach (DataRow item in issuePriottyTable.Rows)
{
htmlText.Append($"<tr><td>{item[0]}</td><td>{item[1]}</td><td>{item[2]}</td><td>{item[3]}</td><td>{item[4]}</td><td>{item[5]}</td><td>{item[6]}</td></tr>");
}
htmlText.Append($"</table></div>");
}
htmlText.Append($"</div>");
}
return(htmlText.ToString());
}
protected string GetDetailByAccount(long accId)
{
StringBuilder html = new StringBuilder();
crm_account thisAccount = accBll.GetCompany(accId);
if (thisAccount == null)
{
return("");
}
//
html.Append("<div class='workspace'>");
#region 客户信息-地址信息
html.Append($"<p align='left' style='padding-left: 10px;' class='FieldLabels'>{thisAccount.name}");
if (!string.IsNullOrEmpty(thisAccount.phone))
{
html.Append($"<span style='font-weight:normal;'> {thisAccount.phone} </span ><br />");
}
else
{
html.Append($"<br />");
}
var location = new LocationBLL().GetLocationByAccountId(accId);
if (location != null)
{
if (!string.IsNullOrEmpty(location.address))
{
html.Append($"<span style='font-weight:normal;'>{location.address} </span ><br />");
}
if (!string.IsNullOrEmpty(location.additional_address))
{
html.Append($"<span style='font-weight:normal;'>{location.additional_address} </span ><br />");
}
var ddDal = new DAL.d_district_dal();
var thisPro = ddDal.FindById(location.province_id);
if (thisPro != null)
{
html.Append($"<span style='font-weight:normal;'>{thisPro.name} </span > ");
}
var thisCity = ddDal.FindById(location.city_id);
if (thisCity != null)
{
html.Append($"<span style='font-weight:normal;'>{thisCity.name} </span > ");
}
if (location.district_id != null)
{
var thisDis = ddDal.FindById((long)location.district_id);
if (thisDis != null)
{
html.Append($"<span style='font-weight:normal;'>{thisDis.name} </span ><br />");
}
}
if (location.country_id != null)
{
var thisCoun = new DAL.d_country_dal().FindById((long)location.country_id);
if (thisCoun != null)
{
html.Append($"<span style='font-weight:normal;'>{thisCoun.country_name_display} </span ><br />");
}
}
}
html.Append("</p>");
#endregion
html.Append("<div style='padding-left: 10px;'><table cellspacing='0' cellpadding='4' border='0' class='FieldLabels'><tbody>");
#region 联系人信息
html.Append("<tr><td align='left'>客户</td></tr>");
List <crm_contact> contactList = new ContactBLL().GetContactByCompany(accId);
if (contactList != null && contactList.Count > 0)
{
contactList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}-手机号 : {_.phone}</td></tr>");
});
}
#endregion
#region 商机信息
html.Append("<tr><td align='left' style='padding-top:20px;'>商机</td></tr>");
List <crm_opportunity> oppoList = new OpportunityBLL().GetOppoBySql($"SELECT id,name from crm_opportunity where delete_time =0 and account_id = {accId.ToString()} ");
// and status_id not in ({(int)DicEnum.OPPORTUNITY_STATUS.LOST},{(int)DicEnum.OPPORTUNITY_STATUS.CLOSED},{(int)DicEnum.OPPORTUNITY_STATUS.IMPLEMENTED})
if (oppoList != null && oppoList.Count > 0)
{
oppoList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 销售订单信息 销售订单没有name 使用商机name
html.Append("<tr><td align='left' style='padding-top:20px;'>销售订单</td></tr>");
List <crm_opportunity> saleList = accBll.GetBySql <crm_opportunity>($"SELECT s.id,p.name from crm_sales_order s INNER JOIN crm_opportunity p on s.opportunity_id = p.id where s.delete_time = 0 and p.delete_time =0 and p.account_id = {accId.ToString()} ");
if (saleList != null && saleList.Count > 0)
{
saleList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 采购订单信息
html.Append("<tr><td align='left' style='padding-top:20px;'>采购订单</td></tr>");
List <ivt_order> orderList = accBll.GetBySql <ivt_order>($"SELECT v.id from ivt_order v where v.delete_time = 0 and v.vendor_account_id = {accId.ToString()} ");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{(orderList!=null? orderList.Count:0)}个采购订单</td></tr>");
#endregion
#region 待办信息
html.Append("<tr><td align='left' style='padding-top:20px;'>待办</td></tr>");
List <com_activity> todoList = new ActivityBLL().GetToListBySql($"select id,name,description from com_activity where delete_time =0 and cate_id = {(int)DicEnum.ACTIVITY_CATE.TODO} and account_id = {accId.ToString()} and (status_id <> {(int)DicEnum.ACTIVITY_STATUS.COMPLETED} or status_id is null)");
if (todoList != null && todoList.Count > 0)
{
todoList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.description}</td></tr>");
});
}
#endregion
#region 活动信息
html.Append("<tr><td align='left' style='padding-top:20px;'>活动</td></tr>");
List <com_activity> actList = new ActivityBLL().GetToListBySql($"select id,name,description from com_activity where delete_time =0 and cate_id <> {(int)DicEnum.ACTIVITY_CATE.TODO} and account_id = {accId.ToString()} and (status_id <> {(int)DicEnum.ACTIVITY_STATUS.COMPLETED} or status_id is null)");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{(actList != null ? actList.Count : 0)}个活动</td></tr>");
#endregion
#region 配置项信息
html.Append("<tr><td align='left' style='padding-top:20px;'>配置项</td></tr>");
List <ivt_product> insProList = accBll.GetBySql <ivt_product>($"SELECT i.id,p.name from crm_installed_product i INNER JOIN ivt_product p on i.product_id = p.id and i.delete_time =0 and p.delete_time =0 and i.account_id = {accId.ToString()}");
if (insProList != null && insProList.Count > 0)
{
insProList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 项目信息
html.Append("<tr><td align='left' style='padding-top:20px;'>项目</td></tr>");
List <pro_project> proList = accBll.GetBySql <pro_project>($"SELECT id,name from pro_project where delete_time= 0 and account_id = {accId.ToString()}");
if (proList != null && proList.Count > 0)
{
proList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 费用信息
html.Append("<tr><td align='left' style='padding-top:20px;'>费用</td></tr>");
List <sdk_expense> expList = accBll.GetBySql <sdk_expense>($"SELECT id from sdk_expense where delete_time= 0 and account_id = {accId.ToString()}");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{(expList != null ? expList.Count : 0)}个费用</td></tr>");
#endregion
#region 工单信息
html.Append("<tr><td align='left' style='padding-top:20px;'>工单</td></tr>");
List <sdk_task> ticketList = accBll.GetBySql <sdk_task>($"SELECT id from sdk_task where type_id = 1809 and delete_time = 0 and account_id = {accId.ToString()}");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{(ticketList != null ? ticketList.Count : 0)}个工单</td></tr>");
#endregion
#region 调查问卷信息
#endregion
#region 任务或工单备注信息
html.Append("<tr><td align='left' style='padding-top:20px;'>任务或工单备注</td></tr>");
List <sdk_task> taskList = accBll.GetBySql <sdk_task>($"SELECT id from sdk_task where type_id = 1807 and delete_time = 0 and account_id = {accId.ToString()}");
List <com_activity> taskNoteList = accBll.GetBySql <com_activity>($"SELECT * from com_activity where delete_time =0 and cate_id = 1500 and account_id = {accId.ToString()}");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{((taskList != null ? taskList.Count : 0)+(taskNoteList != null ? taskNoteList.Count : 0))}个任务或工单备注</td></tr>");
#endregion
#region 备注信息
html.Append("<tr><td align='left' style='padding-top:20px;'>备注</td></tr>");
List <com_activity> noteList = accBll.GetBySql <com_activity>($"SELECT * from com_activity where delete_time =0 and cate_id = 31 and account_id = {accId.ToString()}");
if (noteList != null && noteList.Count > 0)
{
noteList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 合同信息
html.Append("<tr><td align='left' style='padding-top:20px;'>合同</td></tr>");
List <ctt_contract> contractList = accBll.GetBySql <ctt_contract>($"SELECT id, name from ctt_contract where delete_time = 0 and account_id = { accId.ToString()}");
if (contractList != null && contractList.Count > 0)
{
contractList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
#region 附件信息
html.Append("<tr><td align='left' style='padding-top:20px;'>附件</td></tr>");
List <com_attachment> attList = accBll.GetBySql <com_attachment>($"SELECT id from com_attachment where delete_time =0 and account_id = { accId.ToString()}");
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>这个客户有{(attList != null ? attList.Count : 0)}个附件</td></tr>");
#endregion
#region 务信息
html.Append("<tr><td align='left' style='padding-top:20px;'>服务</td></tr>");
List <ivt_service> serviceList = accBll.GetBySql <ivt_service>($"SELECT id,name from ivt_service where delete_time = 0 and vendor_account_id = { accId.ToString()}");
if (serviceList != null && serviceList.Count > 0)
{
serviceList.ForEach(_ =>
{
html.Append($"<tr><td style='padding-left: 10px; font-weight:normal;'>{_.name}</td></tr>");
});
}
#endregion
html.Append("</tbody></table></div>");
html.Append("</div>");
return(html.ToString());
}
