//支付查询 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); }