//支付查询
public string PayQuery(string dataString, string FormatResult, string callType)
{
string rowcontent, dataRow = "", sql = "", result = "", partsql = "";
List <string> rowList = new List <string>();
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = new DataTable();
try
{
RouteEntity routeEntity = JsonConvert.DeserializeObject <RouteEntity>(dataString);
//未支付 只看应付余额是否为0
if (routeEntity.FilterType == "-1")
{
partsql = " and Ffield0034 > 0 ";
}
//已支付
else if (routeEntity.FilterType == "1")
{
partsql = " and Ffield0034 = 0 ";
}
//另外情况全查
//else
//Ffield0006 可为空 Ffield0009//已付
sql = $"select Ffield0005 as PayType,Ffield0007 as PayCode ,Ffield0008 as Amount ,FApplyName as ApplyName,(Ffield0008-Ffield0034) as Paid,Ffield0034 as Balance from [yaodaibao].[dbo].[formmain_3460] where '{routeEntity.StartTime}' <= [FStart_Date] and [FStart_Date] <= '{routeEntity.EndTime}' and Ffield0006 in ('{routeEntity.EmployeeIds}') {partsql} order by FStart_Date desc";
dt = runner.ExecuteSql(sql);
foreach (DataRow item in dt.Rows)
{
rowcontent = "{\"Year\":\"" + DateTime.Now.ToString("yyyy") + "\",\"PayType\":\"" + item["PayType"] + "\",\"PayCode\":\"" + item["PayCode"] + "\",\"ApplyName\":\"" + item["ApplyName"] + "\",\"Paid\":\"" + item["Paid"] + "\",\"Amount\":\"" + item["Amount"] + "\",\"Balance\":\"" + item["Balance"] + "\",\"startTime\":\"" + routeEntity.StartTime + "\",\"endTime\":\"" + routeEntity.EndTime + "\"}";
rowList.Add(rowcontent);
}
dataRow = string.Join(",", rowList.ToArray());
//最后结果
result = string.Format(FormatResult, callType, "\"True\"", "\"\"", "{\"DataRow\":[" + dataRow + "]}");
}
catch (Exception err)
{
result = string.Format(FormatResult, callType, "\"False\"", err.Message, "");
}
return(result);
}
public string GetPersonPerReport(string dataString, string FormatResult, string callType)
{
//加,连接起前面的json字符串
string result = "", rdataRow, datarows, yearweek = "", panelRow = "", weekindex = ",\"FWeekIndex\":";
List <string> dataRowList = new List <string>();
//初始化状态
// result = string.Format(FormatResult, callType, "\"False\"", "", "");
//每个DataRow格式
string rowcontent = "{{\"dataSets\":[{{\"values\":[{{ \"value\": {0}, \"label\": \"\"}},{{ \"value\": {1}, \"label\":\"\"}}],\"label\":\"\",\"config\": {2}}}],\"name\": \"{3}\",\"Index\":\"{4}\",\"value\":\"{5}\",\"Count\":\"{6}\",\"startTime\":\"{7}\",\"endTime\":\"{8}\",\"valid\":\"VALID\",\"invalid\":\"INVALID\"}}";
//dataString = "{\"FWeekIndex\":\"10\",\"AuthCode\":\"1d340262-52e0-413f-b0e7-fc6efadc2ee5\",\"EmployeeID\":\"4255873149499886263\",\"BeginDate\":\"2020-08-05\",\"EndDate\":\"2020-08-31\"}";
try
{
//查询实体
RouteEntity routeEntity = JsonConvert.DeserializeObject <RouteEntity>(dataString);
weekindex += routeEntity.FWeekIndex;
weekindex += (",\"Year\":" + DateTime.Now.Year + "");
DateTime startTime, endTime;
//获取第一天和最后一天
Tuple <DateTime, DateTime> pertime = ReportHelper.GetPerTime(routeEntity.FWeekIndex);
//开始时间
startTime = pertime.Item1;
//结束时间
endTime = pertime.Item2;
//5-8使用
if (routeEntity.FWeekIndex != "-1000")
{
yearweek = ReportHelper.GetYearWithWeeks(routeEntity.FWeekIndex);
}
else
{
yearweek = routeEntity.FWeekIndex;
}
//自定义》1,签到，2,拜访，3,流程，4,支付,5,艾夫吉夫 6,销量 7,****，8，奖金
//目前有些数据没有，暂时跳过
for (int i = 1; i < 9; i++)
{
//主要区分返回格式 和时间查询问题
if (i == 1 || i == 2 || i == 3 || i == 4)
{
rdataRow = GetDataRow(i, rowcontent, routeEntity.EmployeeId, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"), "");
dataRowList.Add(rdataRow);
}
//罗盘下面四大类
else if (i == 5)
{
//进销存
panelRow += GetDataRow(i, rowcontent, routeEntity.EmployeeId, "", "", yearweek);
}
else if (i == 6)
{
panelRow += GetDataRow(i, rowcontent, routeEntity.EmployeeId, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"), "");
}
else
{
continue;
}
}
//加，拼接下面的json
datarows = string.Join(",", dataRowList.ToArray()) + ",";
//最后结果
result = string.Format(FormatResult, callType, "\"True\"", "\"\"", "{\"DataRow\":[" + datarows + "{" + panelRow + weekindex + "}" + "]}");
}
catch (Exception err)
{
result = string.Format(FormatResult, callType, "\"False\"", err.Message, "");
}
return(result);
}
public string GetPersonPerReport(string dataString, string FormatResult, string callType)
{
string sql = "", result = "", temptime;
result = string.Format(FormatResult, callType, "False", "", "");
string Rowcontent = "\"DataRow\": {{\"dataSets\":[{{\"values\":[{{ \"value\": {0}, \"label\": \"\"}},{{ \"value\": {1}, \"label\":\"\"}}],\"label\":\"\",\"config\": {2}}}],\"name\": \"{3}\",\"Index\":\"{4}\",\"value\":\"{5}\",\"Count\":\"{6}\",\"startTime\":\"{7}\",\"endTime\":\"{8}\"}}";
//dataString = "{\"FWeekIndex\":\"10\",\"AuthCode\":\"1d340262-52e0-413f-b0e7-fc6efadc2ee5\",\"EmployeeID\":\"4255873149499886263\",\"BeginDate\":\"2020-08-05\",\"EndDate\":\"2020-08-31\"}";
try
{
RouteEntity routeEntity = JsonConvert.DeserializeObject <RouteEntity>(dataString);
int rcount, okcount, per;
DateTime startTime, endTime;
switch (routeEntity.FWeekIndex)
{
//上月
case "-11":
temptime = Common.GetMonthTime(DateTime.Now.AddMonths(-1));
startTime = DateTime.Parse(temptime.Split('&')[0]);
endTime = DateTime.Parse(temptime.Split('&')[1]);
break;
//本月
case "10":
temptime = Common.GetMonthTime(DateTime.Now);
startTime = DateTime.Parse(temptime.Split('&')[0]);
endTime = DateTime.Parse(temptime.Split('&')[1]);
break;
//上周
case "-1":
startTime = Common.GetWeekFirstDayMon(DateTime.Now.AddDays(-7));
endTime = Common.GetWeekLastDaySun(DateTime.Now.AddDays(-7));
break;
//本周
case "0":
startTime = Common.GetWeekFirstDayMon(DateTime.Now);
endTime = Common.GetWeekLastDaySun(DateTime.Now);
break;
default:
throw new Exception();
}
sql = $"SELECT ISNULL(SUM([RouteCount]),0) RouteCount ,ISNULL(SUM([OKRouteCount]),0) OKRouteCount FROM [yaodaibao].[dbo].[RouteView] where '{startTime.ToString("yyyy-MM-dd")}' <= FDate and FDate <= '{ endTime.ToString("yyyy-MM-dd") }' and FEmployeeID = { routeEntity.EmployeeId}";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
//百分比
rcount = int.Parse(dt.Rows[0]["RouteCount"].ToString());
okcount = int.Parse(dt.Rows[0]["OKRouteCount"].ToString());
if (routeEntity.EmployeeId == "4255873149499886263")
{
rcount = 55;
okcount = 45;
}
if (rcount == 0)
{
per = 0;
}
else
{
per = okcount * 100 / rcount;
}
string routeconfig = Common.GetCompassConfigFromXml("Route").Replace("ColorPre", "#").Replace("Quot", "\"");
string tempresult = string.Format(Rowcontent, rcount.ToString(), okcount.ToString(), routeconfig, "签到", "1", per + "%", rcount.ToString(), startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"));
result = string.Format(FormatResult, callType, "True", "", tempresult);
}
catch (Exception err)
{
result = string.Format(FormatResult, callType, "False", err.Message, "");
}
return(result);
}
//自定义》1,签到，2,拜访，3,流程，4,待定,5,艾夫吉夫 6,丙戊酸钠 7,待支付金额，8，奖金
public string GetPersonChildData(string dataString, string FormatResult, string callType, string childtype)
{
int childType = int.Parse(childtype);
string sql = "", result = "", yearweek, weekindex = ",\"FWeekIndex\":";
//dataString = "{\"FWeekIndex\":\"-11\",\"AuthCode\":\"1d340262-52e0-413f-b0e7-fc6efadc2ee5\",\"EmployeeID\":\"4255873149499886263\",\"BeginDate\":\"2020-08-05\",\"EndDate\":\"2020-08-31\"}";
string rowcontent, dataRow = "";
List <string> rowList = new List <string>();
try
{
//查询实体
RouteEntity routeEntity = JsonConvert.DeserializeObject <RouteEntity>(dataString);
weekindex += routeEntity.FWeekIndex;
DateTime startTime, endTime;
Tuple <DateTime, DateTime> pertime = ReportHelper.GetPerTime(routeEntity.FWeekIndex);
//开始时间
startTime = pertime.Item1;
//结束时间
endTime = pertime.Item2;
//5-8使用
yearweek = ReportHelper.GetYearWithWeeks(routeEntity.FWeekIndex);
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = new DataTable();
switch (childType)
{
case 1: break;
case 2: break;
//流程
case 3:
sql = $"SELECT [FStart_Member_Name] as FStart_Member_Name, [FSubject] as FSubject ,[FStart_Date] as StartDate,[FCurrent_Member_Name] as CurrentMemberName FROM [yaodaibao].[dbo].[OAProcessStatus] where '{startTime}' <= [FStart_Date] and [FStart_Date] <= '{endTime}' and FState in ('流转中') and FStart_Member_ID in ({routeEntity.EmployeeIds}) order by FStart_Date desc";
dt = runner.ExecuteSql(sql);
foreach (DataRow item in dt.Rows)
{
rowcontent = "{\"Time\":\"" + DateTime.Parse(item["StartDate"].ToString()).ToString("yyyy年MM月yy日") + "\",\"Subject\":\"" + item["FSubject"] + "\",\"Code\":\"" + item["FSubject"] + "\",\"CurrentName\":\"" + item["CurrentMemberName"] + "\",\"Name\":\"" + item["FStart_Member_Name"] + "\",\"startTime\":\"" + startTime.ToString("yyyyMMdd") + "\",\"endTime\":\"" + endTime.ToString("yyyyMMdd") + "\",\"FWeekIndex\":\"" + routeEntity.FWeekIndex + "\"}";
rowList.Add(rowcontent);
}
break;
//支付
case 4:
//Ffield0006 可为空
sql = $"select Ffield0005 as PayType,Ffield0007 as PayCode ,Ffield0008 as Amount ,FApplyName as ApplyName,(Ffield0008-Ffield0034) as Paid,Ffield0034 as Balance from [yaodaibao].[dbo].[formmain_3460] where '{startTime}' <= [FStart_Date] and [FStart_Date] <= '{endTime}' and Ffield0006 in ('{routeEntity.EmployeeIds}') order by FStart_Date desc";
dt = runner.ExecuteSql(sql);
foreach (DataRow item in dt.Rows)
{
rowcontent = "{\"Year\":\"" + DateTime.Now.ToString("yyyy") + "\",\"PayType\":\"" + item["PayType"] + "\",\"PayCode\":\"" + item["PayCode"] + "\",\"ApplyName\":\"" + item["ApplyName"] + "\",\"Paid\":\"" + item["Paid"] + "\",\"Amount\":\"" + item["Amount"] + "\",\"Balance\":\"" + item["Balance"] + "\",\"startTime\":\"" + startTime.ToString("yyyyMMdd") + "\",\"endTime\":\"" + endTime.ToString("yyyyMMdd") + "\"}";
rowList.Add(rowcontent);
}
break;
case 5: break;
//销量
case 6:
//Ffield0014 可为空
sql = $"select [Ffield0002] as Hospital,[Ffield0008] as Total FROM [yaodaibao].[dbo].[formmain_6786] where Ffield0011 in ('招商') and '{startTime}' <= [FStart_Date] and [FStart_Date] <= '{endTime}' and Ffield0014 in ('{routeEntity.EmployeeIds}') order by FStart_Date desc";
dt = runner.ExecuteSql(sql);
string nextpage = "false";
//没有招商
if (dt.Rows.Count < 0)
{
sql = $"select [Ffield0002] as Hospital,[Ffield0008] as Total FROM [yaodaibao].[dbo].[formmain_6786] where '{startTime}' <= [FStart_Date] and [FStart_Date] <= '{endTime}' and Ffield0014 in ('{routeEntity.EmployeeIds}') order by FStart_Date desc";
dt = runner.ExecuteSql(sql);
}
//有招商
else
{
nextpage = "true";
}
foreach (DataRow item in dt.Rows)
{
rowcontent = "{\"nextpage\":\"" + nextpage + "\",\"Year\":\"" + DateTime.Now.ToString("yyyy") + "\",\"Hospital\":\"" + item["Hospital"] + "\",\"Total\":\"" + item["Total"] + "\",\"startTime\":\"" + startTime.ToString("yyyyMMdd") + "\",\"endTime\":\"" + endTime.ToString("yyyyMMdd") + "\",\"FWeekIndex\":\"" + routeEntity.FWeekIndex + "\"}";
rowList.Add(rowcontent);
}
break;
case 7: break;
case 8: break;
default:
break;
}
dataRow = string.Join(",", rowList.ToArray());
//最后结果
result = string.Format(FormatResult, callType, "\"True\"", "\"\"", "{\"DataRow\":[" + dataRow + "]}");
}
catch (Exception err)
{
result = string.Format(FormatResult, callType, "\"False\"", err.Message, "");
}
return(result);
}
