暫無描述

WorkOrderReportController.cs 129KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756
  1. using CallCenter.Utility;
  2. using CallCenter.Utility.Linq;
  3. using CallCenter.Utility.Time;
  4. using CallCenterApi.Common;
  5. using CallCenterApi.DB;
  6. using CallCenterApi.Interface.Controllers.Base;
  7. using CallCenterApi.Interface.Models.Dto;
  8. using CallCenterApi.Model;
  9. using System;
  10. using System.Collections.Generic;
  11. using System.Data;
  12. using System.Linq;
  13. using System.Text;
  14. using System.Web;
  15. using System.Web.Mvc;
  16. using static CallCenter.Utility.NPOIHelper;
  17. namespace CallCenterApi.Interface.Controllers.report
  18. {
  19. [Authority]
  20. public class WorkOrderReportController : BaseController
  21. {
  22. private readonly BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();
  23. private readonly BLL.T_Bus_WorkOrder workOrderBLL = new BLL.T_Bus_WorkOrder();
  24. private readonly BLL.T_Bus_AssignedInfo assignedInfoBLL = new BLL.T_Bus_AssignedInfo();
  25. private readonly BLL.T_Bus_Feedback feedbackBLL = new BLL.T_Bus_Feedback();
  26. private readonly BLL.T_Sys_DictionaryValue dictValueBLL = new BLL.T_Sys_DictionaryValue();
  27. private readonly BLL.T_Sys_Area areaBLL = new BLL.T_Sys_Area();
  28. private readonly BLL.T_Bus_VisitResult visitResultBLL = new BLL.T_Bus_VisitResult();
  29. private readonly BLL.T_Bus_DelayTime delayTimeBLL = new BLL.T_Bus_DelayTime();
  30. private readonly BLL.T_Sys_UserAccount userAccount = new BLL.T_Sys_UserAccount();
  31. private readonly BLL.T_Bus_SubmitSuper submitsuperBLL = new BLL.T_Bus_SubmitSuper();
  32. /// <summary>
  33. /// 处理数量统计
  34. /// </summary>
  35. /// <returns></returns>
  36. public ActionResult GetDeptCount(DateTime? start, DateTime? end, bool isExport = false)
  37. {
  38. if (start == null)
  39. {
  40. start = DateTime.Now;
  41. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  42. }
  43. if (end == null)
  44. {
  45. end = DateTime.Now;
  46. }
  47. Dictionary<string, string> paras = new Dictionary<string, string>();
  48. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  49. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  50. var obj = DbHelperSQL.RunProcedure("P_DeptDealReport", paras, "DeptDealReport");
  51. if (isExport)
  52. {
  53. var cols = new string[] { "单位名称", "数量" };
  54. new NPOIHelper().ExportToExcel($"处理数量统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  55. return Success("导出excel");
  56. }
  57. return Success("成功", obj);
  58. }
  59. /// <summary>
  60. /// 处理效率统计
  61. /// </summary>
  62. /// <returns></returns>
  63. public ActionResult GetDeptEfficiency1(DateTime? start, DateTime? end, bool isExport = false)
  64. {
  65. if (start == null)
  66. {
  67. start = DateTime.Now;
  68. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  69. }
  70. if (end == null)
  71. {
  72. end = DateTime.Now;
  73. }
  74. Dictionary<string, string> paras = new Dictionary<string, string>();
  75. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  76. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  77. var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport", paras, "DeptEfficiencyReport");
  78. if (isExport)
  79. {
  80. string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
  81. NPOIHelper npoi = new NPOIHelper();
  82. if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
  83. {
  84. return Success("导出成功");
  85. }
  86. else
  87. {
  88. return Error("导出失败");
  89. }
  90. }
  91. return Success("成功", obj);
  92. }
  93. public ActionResult GetDeptEfficiency(DateTime? start, DateTime? end, bool isExport = false,string source="")
  94. {
  95. if (start == null)
  96. {
  97. start = DateTime.Now;
  98. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  99. }
  100. if (end == null)
  101. {
  102. end = DateTime.Now;
  103. }
  104. Dictionary<string, string> paras = new Dictionary<string, string>();
  105. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  106. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  107. paras.Add("@source", source);
  108. var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport_1", paras, "DeptEfficiencyReport");
  109. var newSet = new DataSet();
  110. if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
  111. {
  112. obj.Tables[0].Columns.Add("rate", typeof(decimal));
  113. for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
  114. {
  115. int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
  116. int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
  117. int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
  118. int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
  119. timeoutcount = completedcount - timelycount;
  120. obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
  121. if (completedcount > 0)
  122. {
  123. timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
  124. obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
  125. obj.Tables[0].Rows[i]["rate"] = timelyrate;
  126. }
  127. }
  128. obj.Tables[0].DefaultView.Sort = "rate desc";
  129. newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
  130. newSet.Tables[0].Columns.Remove("rate");
  131. //obj.Tables[0] = obj.Tables[0].DefaultView.ToTable();
  132. }
  133. if (isExport)
  134. {
  135. string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
  136. NPOIHelper npoi = new NPOIHelper();
  137. if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
  138. {
  139. return Success("导出成功");
  140. }
  141. else
  142. {
  143. return Error("导出失败");
  144. }
  145. }
  146. return Success("成功", newSet);
  147. }
  148. /// <summary>
  149. /// 市民满意度评价
  150. /// </summary>
  151. /// <returns></returns>
  152. public ActionResult GetDeptSatisfied(DateTime? start, DateTime? end, bool isExport = false)
  153. {
  154. if (start == null)
  155. {
  156. start = DateTime.Now;
  157. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  158. }
  159. if (end == null)
  160. {
  161. end = DateTime.Now;
  162. }
  163. Dictionary<string, string> paras = new Dictionary<string, string>();
  164. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  165. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  166. var obj = DbHelperSQL.RunProcedure("P_DeptSatisfied", paras, "DeptSatisfied");
  167. if (isExport)
  168. {
  169. string[] cols = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
  170. new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  171. return Success("导出excel");
  172. }
  173. var ob = new
  174. {
  175. state = "success",
  176. message = "成功",
  177. DeptSatisfiedReport = obj ,
  178. };
  179. return Success("成功", ob );
  180. }
  181. /// <summary>
  182. /// 县区满意度评价
  183. /// </summary>
  184. /// <returns></returns>
  185. #region 20191022 调整多主办单位需调整
  186. public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
  187. {
  188. if (start == null)
  189. {
  190. start = DateTime.Now;
  191. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  192. }
  193. if (end == null)
  194. {
  195. end = DateTime.Now;
  196. }
  197. //var sql = "F_IsDelete=0 and F_IsSure=1 and F_State=1 and isnull(F_FeedbackTime,'')<>'' "
  198. // + "and datediff(day, F_FeedbackTime, @sdate) <= 0 and datediff(day, F_FeedbackTime, @edate) >= 0"
  199. // + "and F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder where F_WorkState = 9)";
  200. //var assignedInfoList = assignedInfoBLL.GetModelList(sql);
  201. //var workorderList = workOrderBLL.GetModelList("");
  202. //var deptList = departmentBLL.GetModelList(" F_State=0 AND F_PartentId=0 ");
  203. //foreach (var item in deptList)
  204. //{
  205. // var deptlist2 = departmentBLL.GetModelList(" F_State=0 AND F_PartentId in (select F_DeptId FROM T_Sys_Department where F_State=0 AND F_PartentId=" + item.F_DeptId + ") ");
  206. //}
  207. Dictionary<string, string> paras = new Dictionary<string, string>();
  208. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  209. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  210. var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");
  211. if (isExport)
  212. {
  213. string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
  214. new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  215. return Success("导出excel");
  216. }
  217. return Success("成功", obj);
  218. }
  219. //public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
  220. //{
  221. // if (start == null)
  222. // {
  223. // start = DateTime.Now;
  224. // start = new DateTime(start.Value.Year, start.Value.Month, 1);
  225. // }
  226. // if (end == null)
  227. // {
  228. // end = DateTime.Now;
  229. // }
  230. // //string sql = $"select * from dbo.GetDeptSatisfied('{start.Value.ToString("yyyy-MM-dd")}','{end.Value.ToString("yyyy-MM-dd")}') ORDER BY rate DESC";
  231. // //var dtall = DbHelperSQL.Query(sql).Tables[0];
  232. // Dictionary<string, string> paras = new Dictionary<string, string>();
  233. // paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  234. // paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  235. // var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");
  236. // if (isExport)
  237. // {
  238. // string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
  239. // new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  240. // return Success("导出excel");
  241. // }
  242. // return Success("成功", obj);
  243. // //int recordCount = 0;
  244. // //var dt = BLL.PagerBLL.GetListPager(
  245. // // "("+sql+") as v",
  246. // // "deptname",
  247. // // "*",
  248. // // "",
  249. // // "ORDER BY rate DESC",
  250. // // pageSize,
  251. // // pageIndex,
  252. // // true,
  253. // // out recordCount);
  254. // //var obj = new
  255. // //{
  256. // // state = "success",
  257. // // message = "成功",
  258. // // rows = dt,
  259. // // total = recordCount
  260. // //};
  261. // //return Content(obj.ToJson());
  262. //}
  263. #endregion
  264. /// <summary>
  265. /// 单位绩效考核
  266. /// </summary>
  267. /// <param name="start"></param>
  268. /// <param name="end"></param>
  269. /// <returns></returns>
  270. public ActionResult GetDeptAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="")
  271. {
  272. StringBuilder sb1 = new StringBuilder();
  273. StringBuilder sb2 = new StringBuilder();
  274. if (start == null)
  275. {
  276. start = DateTime.Now;
  277. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  278. }
  279. if (end == null)
  280. {
  281. end = DateTime.Now;
  282. }
  283. Dictionary<string, string> paras = new Dictionary<string, string>();
  284. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  285. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  286. paras.Add("@source", source);
  287. var obj = DbHelperSQL.RunProcedure("P_DeptAssessmentReport", paras, "DeptAssessmentReport");
  288. if (isExport)
  289. {
  290. string[] cols = new string[] { "单位名称", "已办总量", "及时量", "超时量", "超时时长(小时)", "延时量", "处理时长(小时)", "平均处理时长(小时)", "最大处理时长(小时)", "回访量", "市民满意", "市民不满意" };
  291. NPOIHelper npoi = new NPOIHelper();
  292. if (npoi.ExportToExcel("单位绩效考核报表", obj.Tables[0], cols) == "")
  293. {
  294. return Success("导出成功");
  295. }
  296. else
  297. {
  298. return Error("导出失败");
  299. }
  300. }
  301. return Success("成功", obj);
  302. }
  303. /// <summary>
  304. /// 坐席工作统计
  305. /// </summary>
  306. /// <param name="start"></param>
  307. /// <param name="end"></param>
  308. /// <returns></returns>
  309. public ActionResult GetUserAccountAssessment1(DateTime? start, DateTime? end, bool isExport = false)
  310. {
  311. StringBuilder sb1 = new StringBuilder();
  312. StringBuilder sb2 = new StringBuilder();
  313. if (start == null)
  314. {
  315. start = DateTime.Now;
  316. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  317. }
  318. if (end == null)
  319. {
  320. end = DateTime.Now;
  321. }
  322. Dictionary<string, string> paras = new Dictionary<string, string>();
  323. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  324. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  325. var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport", paras, "UserAssessmentReport");
  326. if (isExport)
  327. {
  328. string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量"};
  329. NPOIHelper npoi = new NPOIHelper();
  330. if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
  331. {
  332. return Success("导出成功");
  333. }
  334. else
  335. {
  336. return Error("导出失败");
  337. }
  338. }
  339. return Success("成功", obj);
  340. }
  341. public ActionResult GetUserAccountAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="",string name="")
  342. {
  343. StringBuilder sb1 = new StringBuilder();
  344. StringBuilder sb2 = new StringBuilder();
  345. if (start == null)
  346. {
  347. start = DateTime.Now;
  348. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  349. }
  350. if (end == null)
  351. {
  352. end = DateTime.Now;
  353. }
  354. Dictionary<string, string> paras = new Dictionary<string, string>();
  355. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  356. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  357. paras.Add("@source", source);
  358. paras.Add("@name", name);
  359. var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport_1", paras, "UserAssessmentReport");
  360. if (isExport)
  361. {
  362. string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量","呼出量" ,"回拨量"};
  363. NPOIHelper npoi = new NPOIHelper();
  364. if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
  365. {
  366. return Success("导出成功");
  367. }
  368. else
  369. {
  370. return Error("导出失败");
  371. }
  372. }
  373. return Success("成功", obj);
  374. }
  375. public ActionResult GetUserReturn(DateTime? start, DateTime? end, bool isExport = false)
  376. {
  377. StringBuilder sb1 = new StringBuilder();
  378. StringBuilder sb2 = new StringBuilder();
  379. if (start == null)
  380. {
  381. start = DateTime.Now;
  382. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  383. }
  384. if (end == null)
  385. {
  386. end = DateTime.Now;
  387. }
  388. Dictionary<string, string> paras = new Dictionary<string, string>();
  389. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  390. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  391. var obj = DbHelperSQL.RunProcedure("P_UserReturn", paras, "UserReturn");
  392. if (isExport)
  393. {
  394. string[] cols = new string[] { "姓名", "工单量", "回访量", "未回访量", "回访率" };
  395. NPOIHelper npoi = new NPOIHelper();
  396. if (npoi.ExportToExcel("坐席回访统计报表", obj.Tables[0], cols) == "")
  397. {
  398. return Success("导出成功");
  399. }
  400. else
  401. {
  402. return Error("导出失败");
  403. }
  404. }
  405. return Success("成功", obj);
  406. }
  407. public ActionResult WorkOrdrList(int isdc=0)
  408. {
  409. DataTable dt = new DataTable();
  410. string sql = " and F_IsDelete=0 ";
  411. int sourceArea = RequestString.GetInt("sourceArea", 0);
  412. int keyid = RequestString.GetInt("keyid", 0);
  413. int deptid = RequestString.GetInt("deptid", 0);
  414. int key = RequestString.GetInt("key", 0);
  415. int timetype = RequestString.GetInt("timetype", 0);
  416. int ishf = RequestString.GetInt("ishf", 0);
  417. int area = RequestString.GetInt("area", 0);
  418. int state = RequestString.GetInt("state", -1);
  419. int type = RequestString.GetInt("type", 0);
  420. int source = RequestString.GetInt("source", 0);
  421. string mobile = RequestString.GetQueryString("mobile");
  422. int dbdb = RequestString.GetInt("dbdb", 0);
  423. int db = RequestString.GetInt("db", 0);
  424. int ys = RequestString.GetInt("ys", 0);
  425. string dbusercode = RequestString.GetQueryString("dbusercode");
  426. string strpageindex = RequestString.GetQueryString("page");
  427. string strpagesize = RequestString.GetQueryString("pagesize");
  428. string usercode = RequestString.GetQueryString("usercode");
  429. string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
  430. string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
  431. int isRelease = RequestString.GetInt("isRelease", 0);
  432. int name= RequestString.GetInt("name", 0);
  433. int handling = RequestString.GetInt("handling", 0);
  434. int pageindex = 1;
  435. int pagesize = 10;
  436. if (strpageindex.Trim() != "")
  437. {
  438. pageindex = Convert.ToInt32(strpageindex);
  439. }
  440. if (strpagesize.Trim() != "")
  441. {
  442. pagesize = Convert.ToInt32(strpagesize);
  443. }
  444. if (!string .IsNullOrEmpty (usercode))
  445. {
  446. sql += " and F_CreateUser='"+ usercode + "' ";
  447. }
  448. string value = "";
  449. if (string .IsNullOrEmpty (strstarttime)&& string.IsNullOrEmpty(strendtime))
  450. {
  451. if (db > 0 || dbdb>0)
  452. {
  453. DateTime start;
  454. start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
  455. if (dbdb==5)
  456. {
  457. value += "and datediff(SS ,F_CreateTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
  458. value += " and datediff(SS ,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
  459. }
  460. else
  461. {
  462. value += "and datediff(SS ,SupervisorTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
  463. value += " and datediff(SS ,SupervisorTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
  464. }
  465. }
  466. else
  467. {
  468. if (timetype == 1)
  469. {
  470. sql += "and datediff(DAY ,a.F_CreateTime ,getdate())=0 ";
  471. }
  472. else if (timetype == 2)
  473. {
  474. sql += "and datediff(WEEK ,a.F_CreateTime ,getdate())=0 ";
  475. }
  476. else if (timetype == 4)
  477. {
  478. }
  479. else
  480. {
  481. sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
  482. }
  483. }
  484. }
  485. else
  486. {
  487. if (db>0 || dbdb > 0)
  488. {
  489. if (dbdb == 5)
  490. {
  491. if (!string.IsNullOrEmpty(strstarttime))
  492. {
  493. value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
  494. }
  495. if (!string.IsNullOrEmpty(strendtime))
  496. {
  497. value += " and datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
  498. }
  499. }
  500. else
  501. {
  502. if (!string.IsNullOrEmpty(strstarttime))
  503. {
  504. value += "and datediff(SS ,SupervisorTime,'" + strstarttime + "')<=0 ";
  505. }
  506. if (!string.IsNullOrEmpty(strendtime))
  507. {
  508. value += " and datediff(SS ,SupervisorTime,'" + strendtime + "')>=0";
  509. }
  510. }
  511. }
  512. else
  513. {
  514. if (!string.IsNullOrEmpty(strstarttime))
  515. {
  516. sql += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
  517. }
  518. if (!string.IsNullOrEmpty(strendtime))
  519. {
  520. sql += " and datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
  521. }
  522. }
  523. }
  524. if (dbdb>0)
  525. {
  526. sql += " and F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_IsDelete = 0 and F_State = 0 and (Supervisor ='' or Supervisor is null )"+ value + ") and F_WorkState != '9'";
  527. }
  528. if(db>0)
  529. {
  530. if (!string .IsNullOrEmpty (dbusercode))
  531. {
  532. value += "and Supervisor='" + dbusercode + "'";
  533. }
  534. if (db==1)
  535. {
  536. sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_IsDelete = 0 " + value + " and Supervisor !='' and Supervisor is not null )";
  537. }
  538. else if (db == 2)
  539. {
  540. sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 1 " + value + " )";
  541. }
  542. else if (db == 3)
  543. {
  544. sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 2 " + value + " ) ";
  545. }
  546. else if (db == 4)
  547. {
  548. sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 " + value + " ) and F_WorkState = '9'";
  549. }
  550. }
  551. if (!string .IsNullOrEmpty (mobile ))
  552. {
  553. sql += "and a.F_CusPhone = '" + mobile + "'";
  554. }
  555. if (area > 0)
  556. {
  557. sql += "and a.F_SourceArea='" + area + "'";
  558. }
  559. if (state>-1)
  560. {
  561. sql += "and a.F_WorkState='" + state + "'";
  562. }
  563. if (type > 0)
  564. {
  565. sql += "and a.F_InfoType='" + type + "'";
  566. }
  567. if (source > 0)
  568. {
  569. sql += "and a.F_InfoSource='" + source + "'";
  570. }
  571. if (isRelease>0)
  572. {
  573. if (isRelease==2)
  574. sql += " and (F_IsRelease =2 or F_IsRelease is null or F_IsRelease ='')";
  575. else
  576. sql += "and F_IsRelease ='"+ isRelease + "'";
  577. }
  578. if (ishf>0)
  579. {
  580. if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
  581. {
  582. value += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
  583. }
  584. else
  585. {
  586. if (!string.IsNullOrEmpty(strstarttime))
  587. {
  588. value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
  589. }
  590. }
  591. if (ishf==1)
  592. {
  593. sql += "and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult WITH(NOLOCK) " +
  594. "and F_IsDelete =0 "+ value + " ) ";
  595. }
  596. else
  597. {
  598. sql += "and F_WorkOrderId not in (select F_WorkOrderId from T_Bus_VisitResult WITH(NOLOCK) " +
  599. "and F_IsDelete =0 " + value + " and F_IsSms !=1 ) ";
  600. }
  601. }
  602. if (handling>0)
  603. {
  604. if (deptid <= 0)
  605. return Error("请选择部门");
  606. if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
  607. {
  608. sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
  609. }
  610. sql += "F_MainDeptId='" + deptid + "' and F_IsResult=0";
  611. switch (handling)
  612. {
  613. case 2:
  614. break;
  615. }
  616. }
  617. if (ys>0)
  618. {
  619. sql += " and F_InfoSource =2501";
  620. if (ys ==2)
  621. {
  622. //直办总量
  623. sql += "and F_IsResult=1 ";
  624. }
  625. else if (ys == 3)
  626. {
  627. //直办办结量
  628. sql += "and F_IsResult=1 and F_WorkState =9 ";
  629. }
  630. else if (ys == 4)
  631. {
  632. //转办总量
  633. sql += "and F_IsResult=0 ";
  634. }
  635. else if (ys == 5)
  636. {
  637. //转办办结量
  638. sql += "and F_IsResult=0 and F_WorkState =9 ";
  639. }
  640. else if (ys == 6)
  641. {
  642. //正常回复
  643. sql += " and F_WorkState =9 ";
  644. }
  645. else if (ys == 7)
  646. {
  647. //已办未果
  648. sql += " and FF_WorkState in (6,7)and F_IsProResult in(2,3) ";
  649. }
  650. else if (ys == 8)
  651. {
  652. //超期回复
  653. sql += " and F_DealTime >F_LimitTime and F_WorkState in(6,7,9) " +
  654. "and F_MainDeptId is not null and F_LimitTime is not null ";
  655. }
  656. else if (ys == 10)
  657. {
  658. //待审信件
  659. sql += " and F_WorkState in(1,11) ";
  660. }
  661. else if (ys == 11)
  662. {
  663. //正在办理
  664. sql += " and F_WorkState in(2,4,5,8) ";
  665. }
  666. else if (ys == 12)
  667. {
  668. //超期未果
  669. sql += " and F_LimitTime <getdate() and F_WorkState NOT IN (6,7,9) " +
  670. "and F_MainDeptId is not null and F_LimitTime is not null ";
  671. }
  672. else if (ys == 13)
  673. {
  674. //重置转发
  675. sql += " and F_WorkState=8 and F_Identification=0 ";
  676. }
  677. else if (ys == 14)
  678. {
  679. //驳回记录
  680. sql += " and F_WorkState=8 and F_Identification in(1,2) ";
  681. }
  682. else if (ys == 15)
  683. {
  684. //关联件数
  685. sql += " and F_IsRelease in (2,3) ";
  686. }
  687. else if (ys == 16)
  688. {
  689. //评议量
  690. sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment) ";
  691. }
  692. else if (ys == 17)
  693. {
  694. //评议满意量
  695. sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment where strWrkOrder_Cst_Ssf_Cd not like '%不满意%') ";
  696. }
  697. else if (ys == 18)
  698. {
  699. //评议不满意量
  700. sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment where strWrkOrder_Cst_Ssf_Cd like '%不满意%') ";
  701. }
  702. else if (ys == 19)
  703. {
  704. //回访量
  705. sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult ) ";
  706. }
  707. else if (ys == 20)
  708. {
  709. //回访满意量
  710. sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult where F_IsSatisfie=1 ) ";
  711. }
  712. else if (ys == 21)
  713. {
  714. //回访不满意量
  715. sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult where F_IsSatisfie=0 ) ";
  716. }
  717. }
  718. string cols = "";
  719. string order = "ORDER BY a.F_CreateTime DESC";
  720. if (name >0)
  721. {
  722. cols = "distinct F_CusName";
  723. order = "";
  724. }
  725. else if (db>0)
  726. {
  727. cols = " a.F_WorkOrderId,F_ComTitle,F_WorkState,F_MainDeptId,F_IsResult,a.F_CreateTime,dbo.GetUserName(a.F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDictionaryName(F_Key) KeyName,dbo.GetDeptNames(F_MainDeptId) as DeptName,F_CloseTime,F_LimitTime as LimitTime,F_ComContent,dbo.GetUserName((select top 1 Supervisor from T_Bus_RemindRecord where F_WorkOrderId=a.F_WorkOrderId order by F_Id desc)) as Supervisor";
  728. }
  729. else
  730. {
  731. cols = " a.F_WorkOrderId,F_ComTitle,F_WorkState,F_MainDeptId,F_IsResult,a.F_CreateTime,dbo.GetUserName(a.F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDictionaryName(F_Key) KeyName,dbo.GetDeptNames(F_MainDeptId) as DeptName,F_CloseTime,F_LimitTime as LimitTime,F_ComContent";
  732. }
  733. if (isdc > 0)
  734. {
  735. var top = " "; var orderby = " order by F_CreateTime desc";
  736. if (sql == " and F_IsDelete=0 ")
  737. {
  738. top = " top 1000 "; orderby += " desc ";
  739. }
  740. if (db == 1)
  741. {
  742. var dtdc = DbHelperSQL.Query(" select ROW_NUMBER() OVER(ORDER BY F_CreateTime desc) 编号, F_WorkOrderId 事项单号, dbo.GetDeptNames(F_MainDeptId ) 承办单位, "
  743. + "(case F_WorkState when 0 then '新工单' when 1 then '待交办' when 2 then '待查收' when 3 then '退回审核中' when 4 then '办理中' when 5 then '延时审核中' when 6 then '已办理' when 7 then '已审核' when 8 then '重办中' when 9 then '已完结' when 11 then '重办待交办' when 12 then '重办驳回' else '办理中' end) 督办情况,(case (select top 1 F_Mode from T_Bus_RemindRecord where F_WorkOrderId = a.F_WorkOrderId order by F_Id desc) when 0 then '现场' when 1 then '电话'else '未知' end ) 督办方式"
  744. + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + order).Tables[0];
  745. var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
  746. if (msg == "")
  747. {
  748. return Success("导出成功");
  749. }
  750. else
  751. {
  752. return Error("导出失败");
  753. }
  754. }
  755. else
  756. {
  757. var dtdc = DbHelperSQL.Query(" select ROW_NUMBER() OVER(ORDER BY F_CreateTime desc) 序号,F_CreateTime 日期, F_WorkOrderId 工单号,F_ComTitle 诉求标题,F_ComContent 诉求内容,dbo.GetDictionaryName(F_Key) 反映类别, "
  758. + " dbo.GetDeptNames(F_MainDeptId ) 主办单位, "
  759. + " (select top 1 F_Result from T_Bus_Feedback WITH(NOLOCK) where F_State=1 and F_Type!=3 and F_IsDelete=0 and F_WorkOrderId =a.F_WorkOrderId order by F_Id desc) 办理结果, "
  760. + " F_CusName 来电人姓名 ,F_CusPhone 来电人手机号 "
  761. + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + orderby).Tables[0];
  762. var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
  763. if (msg == "")
  764. {
  765. return Success("导出成功");
  766. }
  767. else
  768. {
  769. return Error("导出失败");
  770. }
  771. }
  772. }
  773. else
  774. {
  775. int recordCount = 0;
  776. dt = BLL.PagerBLL.GetListPager(
  777. "T_Bus_WorkOrder a WITH(NOLOCK)",
  778. "a.F_WorkOrderId",
  779. cols,
  780. sql,
  781. order,
  782. pagesize,
  783. pageindex,
  784. true,
  785. out recordCount);
  786. var obj = new
  787. {
  788. state = "success",
  789. message = "成功",
  790. rows = dt,
  791. total = recordCount
  792. };
  793. return Content(obj.ToJson());
  794. }
  795. }
  796. public ActionResult IncomingCalls (string date,int isdc=0)
  797. {
  798. if (string.IsNullOrEmpty(date))
  799. date = DateTime.Now.ToString("yyyy-MM-dd");
  800. var modelcalllist = new BLL
  801. .T_Call_CallRecords().GetModelList(" CallType =0 and DATEDIFF(DAY, BeginTime, '"+ date + "') = 0");
  802. var userlist = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag =1");
  803. int ldcount = 0;int jtcount = 0; int newldcount = 0; int newjtcount = 0;
  804. List<Incoming> incomings = new List<Incoming>();
  805. //incoming.time = "00:00-08:00";
  806. //incoming.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
  807. //).Count();
  808. //ldcount += incoming.IncomingCount;
  809. //incoming.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
  810. //&&x.CallState ==1 ).Count();
  811. //jtcount += incoming.Oncapacity;
  812. //var modelcalllists = modelcalllist.Where(x => x.UserCode != null && x.UserCode != "" && x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <=8).Select(x => x.UserCode).Distinct
  813. // ();
  814. //if (modelcalllists!=null&& modelcalllists.Count() >0)
  815. //{
  816. // incoming.seatsCount = modelcalllists.Count();
  817. // foreach (var it in modelcalllists)
  818. // {
  819. // var user = userlist.Where(x => x.F_UserCode == it);
  820. // if (user != null && user.Count() > 0)
  821. // incoming.seats += "," + user.FirstOrDefault().F_UserName;
  822. // else
  823. // incoming.seats += "," + it;
  824. // }
  825. // if (!string .IsNullOrEmpty(incoming.seats))
  826. // incoming.seats = incoming.seats.TrimStart(',');
  827. //}
  828. //else
  829. //{
  830. // incoming.seatsCount =0;
  831. // incoming.seats = "";
  832. //}
  833. //incomings.Add(incoming);
  834. int v = 24;
  835. if (DateTime .Parse (date ).Year ==DateTime .Now .Year &&
  836. DateTime.Parse(date).Month == DateTime.Now.Month && DateTime.Parse(date).Day == DateTime.Now.Day)
  837. {
  838. v = DateTime.Now.Hour;
  839. }
  840. for (int i=0;i < v; i ++)
  841. {
  842. Incoming incomin = new Incoming();
  843. int t = i + 1;
  844. string iss=i.ToString () ,tss=t .ToString ();
  845. if (i < 10)
  846. iss = "0" + i;
  847. if (t < 10)
  848. tss = "0" + t;
  849. incomin.time = iss + ":00" + "-" + tss + ":00";
  850. incomin.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i && x.PhoneType == "12345"
  851. ).Count();
  852. ldcount += incomin.IncomingCount;
  853. incomin.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i
  854. && x.CallState == 1 && x.PhoneType == "12345").Count();
  855. jtcount += incomin.Oncapacity;
  856. incomin.newIncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour == i
  857. ).Count();
  858. newldcount += incomin.newIncomingCount;
  859. incomin.newOncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour == i
  860. && x.CallState == 1 ).Count();
  861. newjtcount += incomin.newOncapacity;
  862. var modelcalllistss = modelcalllist.Where(x => x.UserCode != null&& x.UserCode != "" && x.BeginTime.Value.Hour==i&&x.PhoneType =="12345").Select(x => x.UserCode).Distinct
  863. ();
  864. if (modelcalllistss != null && modelcalllistss.Count() > 0)
  865. {
  866. incomin.seatsCount = modelcalllistss.Count();
  867. foreach (var it in modelcalllistss)
  868. {
  869. var user = userlist.Where(x => x.F_UserCode == it);
  870. if (user != null && user.Count() > 0)
  871. incomin.seats += "," + user.FirstOrDefault().F_UserName;
  872. else
  873. incomin.seats += "," + it;
  874. }
  875. if (!string.IsNullOrEmpty(incomin.seats))
  876. incomin.seats= incomin.seats.TrimStart(',');
  877. }
  878. else
  879. {
  880. incomin.seatsCount = 0;
  881. incomin.seats = "";
  882. }
  883. var modelcalllists = modelcalllist.Where(x => x.UserCode != null && x.UserCode != "" && x.BeginTime.Value.Hour == i && x.PhoneType != "12345").Select(x => x.UserCode).Distinct
  884. ();
  885. if (modelcalllists != null && modelcalllists.Count() > 0)
  886. {
  887. incomin.newseatsCount = modelcalllists.Count();
  888. foreach (var it in modelcalllists)
  889. {
  890. var user = userlist.Where(x => x.F_UserCode == it);
  891. if (user != null && user.Count() > 0)
  892. incomin.newseats += "," + user.FirstOrDefault().F_UserName;
  893. else
  894. incomin.newseats += "," + it;
  895. }
  896. if (!string.IsNullOrEmpty(incomin.newseats))
  897. incomin.newseats = incomin.newseats.TrimStart(',');
  898. }
  899. else
  900. {
  901. incomin.newseatsCount = 0;
  902. incomin.newseats = "";
  903. }
  904. incomings.Add(incomin);
  905. }
  906. Incoming incomint = new Incoming();
  907. incomint.time = "共计";
  908. incomint.IncomingCount = ldcount;
  909. incomint.Oncapacity = jtcount;
  910. incomint.newIncomingCount = newldcount;
  911. incomint.newOncapacity = newjtcount;
  912. incomings.Add(incomint);
  913. if (isdc >0)
  914. {
  915. new NPOIHelper().IncomingCalls(incomings);
  916. return Success("导出excel");
  917. }
  918. return Success("获取成功", incomings);
  919. }
  920. /// <summary>
  921. /// 逾期未回复统计
  922. /// </summary>
  923. /// <param name="date"></param>
  924. /// <param name="deptId"></param>
  925. /// <param name="pageIndex"></param>
  926. /// <param name="pageSize"></param>
  927. /// <returns></returns>
  928. public ActionResult GetOutTimeNotApply(DateTime? start, DateTime? end, int deptId = -1, int pageIndex = 1, int pageSize = 10, bool isExport = false)
  929. {
  930. if (start == null)
  931. {
  932. start = DateTime.Now;
  933. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  934. }
  935. if (end == null)
  936. {
  937. end = DateTime.Now;
  938. }
  939. var where = "";
  940. if (deptId > 0)
  941. {
  942. where = " AND F_MainDeptId=" + deptId;
  943. }
  944. string sql = $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 "+ where;
  945. if (isExport)
  946. {
  947. string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时时间" };
  948. var dtall = DbHelperSQL.Query("select * from V_Bus_OverNoBack where 1=1 "+sql).Tables[0];
  949. dtall.Columns.Remove("F_MainDeptId");
  950. dtall.Columns.Remove("F_CreateTime");
  951. new NPOIHelper().ExportToExcel($"逾期未回复统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
  952. return Success("导出excel");
  953. }
  954. int recordCount = 0;
  955. var dt = BLL.PagerBLL.GetListPager(
  956. "V_Bus_OverNoBack",
  957. "F_WorkOrderId",
  958. "*",
  959. sql,
  960. "ORDER BY F_CreateTime DESC",
  961. pageSize,
  962. pageIndex,
  963. true,
  964. out recordCount);
  965. var obj = new
  966. {
  967. state = "success",
  968. message = "成功",
  969. rows = dt,
  970. total = recordCount
  971. };
  972. return Content(obj.ToJson());
  973. }
  974. /// <summary>
  975. /// 重办不满意统计
  976. /// </summary>
  977. /// <param name="date"></param>
  978. /// <param name="pageIndex"></param>
  979. /// <param name="pageSize"></param>
  980. /// <returns></returns>
  981. public ActionResult GetNotSatisfied(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
  982. {
  983. if (start == null)
  984. {
  985. start = DateTime.Now;
  986. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  987. }
  988. if (end == null)
  989. {
  990. end = DateTime.Now;
  991. }
  992. string sql = $" and datediff(day,cjTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,cjTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
  993. if (isExport)
  994. {
  995. string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "创建时间", "处理结果", "处理时间", "回访结果", "回访时间" };
  996. var dtall = DbHelperSQL.Query("select * from V_Bus_ReloadNoSatisfie where 1=1 " + sql + " order by cjTime DESC ").Tables[0];
  997. dtall.Columns.Remove("F_Id");
  998. new NPOIHelper().ExportToExcel($"重办不满意统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
  999. return Success("导出excel");
  1000. }
  1001. int recordCount = 0;
  1002. var dt = BLL.PagerBLL.GetListPager(
  1003. "V_Bus_ReloadNoSatisfie",
  1004. "F_Id",
  1005. "*",
  1006. sql,
  1007. "ORDER BY cjTime DESC",
  1008. pageSize,
  1009. pageIndex,
  1010. true,
  1011. out recordCount);
  1012. var obj = new
  1013. {
  1014. state = "success",
  1015. message = "成功",
  1016. rows = dt,
  1017. total = recordCount
  1018. };
  1019. return Content(obj.ToJson());
  1020. }
  1021. /// <summary>
  1022. /// 延期统计
  1023. /// </summary>
  1024. /// <param name="start"></param>
  1025. /// <param name="end"></param>
  1026. /// <param name="pageIndex"></param>
  1027. /// <param name="pageSize"></param>
  1028. /// <param name="isExport"></param>
  1029. /// <returns></returns>
  1030. public ActionResult GetDelay(DateTime? start, DateTime? end, bool isExport = false)
  1031. {
  1032. if (start == null)
  1033. {
  1034. start = DateTime.Now;
  1035. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  1036. }
  1037. if (end == null)
  1038. {
  1039. end = DateTime.Now;
  1040. }
  1041. Dictionary<string, string> paras = new Dictionary<string, string>();
  1042. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  1043. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  1044. var obj = DbHelperSQL.RunProcedure("P_DeptDelayReport", paras, "DeptDelayReport");
  1045. if (isExport)
  1046. {
  1047. string[] cols = new string[] { "单位名称", "办理数量", "延时数量", "延时率" };
  1048. NPOIHelper npoi = new NPOIHelper();
  1049. if (npoi.ExportToExcel("延时分析统计报表", obj.Tables[0], cols) == "")
  1050. {
  1051. return Success("导出成功");
  1052. }
  1053. else
  1054. {
  1055. return Error("导出失败");
  1056. }
  1057. }
  1058. return Success("成功", obj);
  1059. }
  1060. /// <summary>
  1061. /// 通报批评
  1062. /// </summary>
  1063. /// <param name="start"></param>
  1064. /// <param name="end"></param>
  1065. /// <param name="pageIndex"></param>
  1066. /// <param name="pageSize"></param>
  1067. /// <param name="isExport"></param>
  1068. /// <returns></returns>
  1069. public ActionResult GetNotice(DateTime? start, DateTime? end, bool isExport = false)
  1070. {
  1071. if (start == null)
  1072. {
  1073. start = DateTime.Now;
  1074. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  1075. }
  1076. if (end == null)
  1077. {
  1078. end = DateTime.Now;
  1079. }
  1080. //string sql = "select * from dbo.GetNoticeReport('"+ start.Value.ToString("yyyy-MM-dd") + "','" + end.Value.ToString("yyyy-MM-dd") + "') ORDER BY zbcount+xbcount DESC";
  1081. //var dt = DbHelperSQL.Query(sql).Tables[0];
  1082. Dictionary<string, string> paras = new Dictionary<string, string>();
  1083. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  1084. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  1085. var obj = DbHelperSQL.RunProcedure("P_DeptNoticeReport", paras, "DeptNoticeReport");
  1086. if (isExport)
  1087. {
  1088. var cols = new string[] { "承办单位", "主办受理数量", "主办通报批评数量", "主办通报批评率", "协办受理数量", "协办通报批评数量", "协办通报批评率" };
  1089. new NPOIHelper().ExportToExcel($"通报批评{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  1090. return Success("导出excel");
  1091. }
  1092. return Success("成功", obj);
  1093. }
  1094. /// <summary>
  1095. /// 诉求受理对比
  1096. /// </summary>
  1097. /// <returns></returns>
  1098. public ActionResult GetAppeal1(DateTime? start, DateTime? end, bool isExport = false)
  1099. {
  1100. if (start == null)
  1101. {
  1102. start = DateTime.Now;
  1103. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  1104. }
  1105. if (end == null)
  1106. {
  1107. end = DateTime.Now;
  1108. }
  1109. Dictionary<string, string> paras = new Dictionary<string, string>();
  1110. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  1111. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  1112. var obj = DbHelperSQL.RunProcedure("P_UserAppealReport", paras, "UserAppealReport");
  1113. if (isExport)
  1114. {
  1115. var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
  1116. new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  1117. return Success("导出excel");
  1118. }
  1119. return Success("成功", obj);
  1120. }
  1121. public ActionResult GetAppeal(DateTime? start, DateTime? end, bool isExport = false
  1122. , string source = "")
  1123. {
  1124. if (start == null)
  1125. {
  1126. start = DateTime.Now;
  1127. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  1128. }
  1129. if (end == null)
  1130. {
  1131. end = DateTime.Now;
  1132. }
  1133. Dictionary<string, string> paras = new Dictionary<string, string>();
  1134. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  1135. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  1136. paras.Add("@source", source);
  1137. var obj = DbHelperSQL.RunProcedure("P_UserAppealReport_1", paras, "UserAppealReport");
  1138. var newSet = new DataSet();var newtable = new DataTable("UserAppealReport");
  1139. #region
  1140. newtable.Columns.Add(new DataColumn("username"));
  1141. newtable.Columns.Add(new DataColumn("count", typeof(int)));
  1142. newtable.Columns.Add(new DataColumn("mcount", typeof(int)));
  1143. newtable.Columns.Add(new DataColumn("mom"));
  1144. newtable.Columns.Add(new DataColumn("ycount", typeof(int)));
  1145. newtable.Columns.Add(new DataColumn("yoy"));
  1146. #endregion
  1147. if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
  1148. {
  1149. for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
  1150. {
  1151. int count = 0, mcount = 0, ycount = 0; double mom = 0, yoy = 0;
  1152. int.TryParse(obj.Tables[0].Rows[i]["count"].ToString(), out count);
  1153. int.TryParse(obj.Tables[0].Rows[i]["mcount"].ToString(), out mcount);
  1154. int.TryParse(obj.Tables[0].Rows[i]["ycount"].ToString(), out ycount);
  1155. if(mcount>0)
  1156. mom = double.Parse((count * 100).ToString()) / mcount;
  1157. if (ycount > 0)
  1158. yoy = double.Parse((count * 100).ToString()) / ycount;
  1159. #region
  1160. DataRow drNew = newtable.NewRow();
  1161. drNew["username"] = obj.Tables[0].Rows[i]["username"];
  1162. drNew["count"] = count;
  1163. drNew["mcount"] = mcount;
  1164. drNew["mom"] = mom > 0 ? mom.ToString("f") + "%" : "0";
  1165. drNew["ycount"] = ycount;
  1166. drNew["yoy"] = yoy > 0 ? yoy.ToString("f") + "%" : "0";
  1167. newtable.Rows.Add(drNew);
  1168. #endregion
  1169. }
  1170. newtable.DefaultView.Sort = "count desc";
  1171. newSet.Tables.Add(newtable.DefaultView.ToTable());
  1172. }
  1173. if (isExport)
  1174. {
  1175. var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
  1176. new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", newSet.Tables[0], cols);
  1177. return Success("导出excel");
  1178. }
  1179. return Success("成功", newSet);
  1180. }
  1181. /// <summary>
  1182. /// 获取督办报表
  1183. /// </summary>
  1184. /// <returns></returns>
  1185. public ActionResult GetDBReportform(string starttime,string endtime)
  1186. {
  1187. if (string .IsNullOrEmpty (starttime))
  1188. {
  1189. starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
  1190. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1191. }
  1192. else
  1193. {
  1194. if (string.IsNullOrEmpty(endtime))
  1195. {
  1196. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1197. }
  1198. }
  1199. Dictionary<string, string> paras = new Dictionary<string, string>();
  1200. paras.Add("@sdate", starttime);
  1201. paras.Add("@edate", endtime);
  1202. var obj1 = DbHelperSQL.RunProcedure("P_DBReportform_1", paras, "DBReportform").Tables[0];
  1203. string sqlarea = " select COUNT(1) from T_Bus_WorkOrder where F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type=1 and F_IsDelete=0 and F_State =0 and (Supervisor ='' or Supervisor is null and F_CreateTime between '" + starttime +
  1204. "' AND '" + endtime + "')) and F_WorkState !='9'and F_IsDelete =0 ";
  1205. DataTable dttype = DbHelperSQL.Query(sqlarea).Tables[0];
  1206. var obj = new
  1207. {
  1208. obj1,
  1209. total = dttype
  1210. };
  1211. return Content(obj.ToJson());
  1212. }
  1213. /// <summary>
  1214. /// 坐席状态统计
  1215. /// </summary>
  1216. /// <param name="starttime"></param>
  1217. /// <param name="endtime"></param>
  1218. /// <returns></returns>
  1219. public ActionResult GetZuoXiState(string starttime, string endtime)
  1220. {
  1221. if (string.IsNullOrEmpty(starttime))
  1222. {
  1223. starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
  1224. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1225. }
  1226. else
  1227. {
  1228. if (string.IsNullOrEmpty(endtime))
  1229. {
  1230. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1231. }
  1232. }
  1233. string sql = " a.OccurTime between '" + starttime + "' AND '" + endtime + "'";
  1234. string value = " TimeLogin between '" + starttime + "' AND '" + endtime + "'";
  1235. string value1 = " TimeLogout between '" + starttime + "' AND '" + endtime + "'";
  1236. string sqlarea = " select a.AgentId ,dbo.GetUserName(a.AgentId) as UserName,(select COUNT(1) from rep_agent_detail where "+ value + " and AgentId = a.AgentId and 1" +
  1237. " is not null ) as 签入 ,(select COUNT(1) from rep_agent_detail where "+ value1 + " and AgentId = a.AgentId and TimeLogout is not null ) as 签出,SUM(case State when 5 then 1 else 0 end) as 置忙 ,SUM(case State when 2 then 1 else 0 end) as 置闲 from dbo.rep_agent_state a where "+ sql + " group by AgentId ";
  1238. DataTable obj = DbHelperSQL.Query(sqlarea).Tables[0];
  1239. return Content(obj.ToJson());
  1240. }
  1241. /// <summary>
  1242. /// 坐席时间
  1243. /// </summary>
  1244. /// <param name="starttime"></param>
  1245. /// <param name="endtime"></param>
  1246. /// <returns></returns>
  1247. public ActionResult GetZuoXiTime (string starttime, string endtime,string usercode,int state=0)
  1248. {
  1249. if (string.IsNullOrEmpty(starttime))
  1250. {
  1251. starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
  1252. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1253. }
  1254. else
  1255. {
  1256. if (string.IsNullOrEmpty(endtime))
  1257. {
  1258. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1259. }
  1260. }
  1261. if (string.IsNullOrEmpty(usercode))
  1262. return Error("请选择坐席");
  1263. string sqlzuoxi = "";
  1264. if (state==0)
  1265. {
  1266. sqlzuoxi = "select TimeLogin from rep_agent_detail where TimeLogin between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "'and TimeLogin is not null ";
  1267. }
  1268. else if (state == 1)
  1269. {
  1270. sqlzuoxi = "select TimeLogout from rep_agent_detail where TimeLogout between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and TimeLogout is not null ";
  1271. }
  1272. else if (state == 2)
  1273. {
  1274. sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and State = 2 ";
  1275. }
  1276. else if (state == 3)
  1277. {
  1278. sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and State = 5 ";
  1279. }
  1280. DataTable obj = DbHelperSQL.Query(sqlzuoxi).Tables[0];
  1281. return Content(obj.ToJson());
  1282. }
  1283. /// <summary>
  1284. /// 获取绩效
  1285. /// </summary>
  1286. /// <param name="date"></param>
  1287. /// <param name="isdc"></param>
  1288. /// <returns></returns>
  1289. public ActionResult GetDeptDeptAchievements(string starttime, string endtime
  1290. , int type=0,int deptid=0)
  1291. {
  1292. if (string.IsNullOrEmpty(starttime))
  1293. {
  1294. starttime = DateTime.Now.AddMonths (-1).ToString("yyyy-MM") + "-21 00:00:00";
  1295. endtime = DateTime.Now.ToString("yyyy-MM") + "-20 00:00:00";
  1296. }
  1297. else
  1298. {
  1299. if (string.IsNullOrEmpty(endtime))
  1300. {
  1301. endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  1302. }
  1303. }
  1304. Dictionary<string, string> paras = new Dictionary<string, string>();
  1305. paras.Add("@sdate", starttime);
  1306. paras.Add("@edate", endtime);
  1307. paras.Add("@type", type.ToString ());
  1308. if (deptid>0)
  1309. paras.Add("@deptid", deptid.ToString ());
  1310. else
  1311. paras.Add("@deptid","");
  1312. var obj = DbHelperSQL.RunProcedure("P_DeptAchievements_1", paras, "DeptAchievements").Tables[0]; ;
  1313. return Content(obj.ToJson());
  1314. }
  1315. /// <summary>
  1316. /// 工单周报
  1317. /// 暂时无用,多主办单位不处理
  1318. /// </summary>
  1319. /// <param name="start"></param>
  1320. /// <param name="end"></param>
  1321. /// <param name="type"></param>
  1322. /// <param name="pageIndex"></param>
  1323. /// <param name="pageSize"></param>
  1324. /// <returns></returns>
  1325. public ActionResult GetWeaklyReport(DateTime? start, DateTime? end, int type = 0, int dateType = 0, int pageIndex = 1, int pageSize = 10, bool isExport = false)
  1326. {
  1327. StringBuilder sb1 = new StringBuilder();
  1328. StringBuilder sb2 = new StringBuilder();
  1329. StringBuilder sb3 = new StringBuilder();
  1330. var startDate = new DateTime();
  1331. var endDate = new DateTime();
  1332. var lastStartDate = new DateTime();
  1333. var lastEndDate = new DateTime();
  1334. var differ = 0;
  1335. var str = "";
  1336. switch (dateType)
  1337. {
  1338. //日
  1339. case 1:
  1340. if (start == null)
  1341. {
  1342. start = DateTime.Now.Date;
  1343. }
  1344. if (end == null)
  1345. {
  1346. end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
  1347. }
  1348. startDate = Convert.ToDateTime(start);
  1349. endDate = Convert.ToDateTime(end).AddDays(1).AddSeconds(-1);
  1350. differ = (endDate - startDate).Days + 1;
  1351. lastStartDate = startDate.AddDays(-differ);
  1352. lastEndDate = endDate.AddDays(-differ);
  1353. str = "日";
  1354. break;
  1355. //周
  1356. case 2:
  1357. if (start == null)
  1358. {
  1359. start = DateTime.Now.Date;
  1360. }
  1361. if (end == null)
  1362. {
  1363. end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
  1364. }
  1365. startDate = Convert.ToDateTime(start);
  1366. endDate = Convert.ToDateTime(end);
  1367. differ = 7 - (int)startDate.DayOfWeek;
  1368. startDate = DateTools.GetWeekFirstDayMon((DateTime)start);
  1369. endDate = DateTools.GetWeekLastDaySun((DateTime)end).AddDays(1).AddSeconds(-1);
  1370. differ = (endDate - startDate).Days + 1;
  1371. lastStartDate = DateTools.GetWeekFirstDayMon(startDate.AddDays(-1));
  1372. lastEndDate = DateTools.GetWeekLastDaySun(lastStartDate).AddDays(1).AddSeconds(-1);
  1373. str = "周";
  1374. break;
  1375. //月
  1376. case 3:
  1377. if (start == null)
  1378. {
  1379. start = DateTime.Now.Date;
  1380. }
  1381. if (end == null)
  1382. {
  1383. end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
  1384. }
  1385. startDate = Convert.ToDateTime(start);
  1386. endDate = Convert.ToDateTime(end);
  1387. differ = startDate.Day;
  1388. startDate = startDate.AddDays(-differ + 1);
  1389. endDate = endDate.AddDays(DateTime.DaysInMonth(startDate.Year, startDate.Month) - differ).AddDays(1).AddSeconds(-1);
  1390. //differ = (endDate - startDate). + 1;
  1391. lastStartDate = startDate.AddMonths(-1);
  1392. lastEndDate = lastStartDate.AddDays(DateTime.DaysInMonth(lastStartDate.Year, lastStartDate.Month) - 1).AddDays(1).AddSeconds(-1);
  1393. str = "月";
  1394. break;
  1395. default:
  1396. break;
  1397. }
  1398. //type 查询类型 type=1 按区域;type=2 按部门; type=3 按工单类型
  1399. switch (type)
  1400. {
  1401. case 1:
  1402. var areaList = areaBLL.DataTableToList(areaBLL.GetList(" F_State=0 ").Tables[0]);
  1403. var areaList1 = new BLL.T_Bus_WorkOrderReport().DataTableToAreaList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_SourceArea,B.F_AreaName, row_number() over(partition by B.F_AreaId order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_Area B ON A.F_SourceArea = B.F_AreaId WHERE datediff(s, '{startDate}', A.F_CreateTime) > 0 and datediff(s, '{endDate}', A.F_CreateTime) < 0 and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND A.F_SourceArea > 0").Tables[0]);
  1404. var areaList2 = new BLL.T_Bus_WorkOrderReport().DataTableToAreaList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_SourceArea,B.F_AreaName, row_number() over(partition by B.F_AreaId order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_Area B ON A.F_SourceArea = B.F_AreaId WHERE datediff(s, '{lastStartDate}', A.F_CreateTime) > 0 and datediff(s, '{lastEndDate}', A.F_CreateTime) < 0 and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND A.F_SourceArea > 0").Tables[0]);
  1405. List<DeptWeaklyReportDto> areaWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
  1406. areaList.ForEach(x =>
  1407. {
  1408. var query = areaList1.Where(y => x.F_AreaId == y.FieldId).ToList();
  1409. var assignedCount = query.Count();
  1410. var lastAssignedcount = areaList2.Where(y => x.F_AreaId == y.FieldId).Count();
  1411. var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
  1412. var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
  1413. var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
  1414. areaWeaklyReportDtoList.Add(new DeptWeaklyReportDto
  1415. {
  1416. name = x.F_AreaName,
  1417. assignedcount = assignedCount,
  1418. countrate = countRate,
  1419. completedcount = completedCount,
  1420. completedrate = completedRate
  1421. });
  1422. });
  1423. if (isExport)
  1424. {
  1425. var dt = areaWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
  1426. new NPOIHelper().ExportToExcel($"按区域{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "区域", "受理数量", "数量同比", "办结数量", "办结率" });
  1427. return Success("导出excel");
  1428. }
  1429. return Content(new
  1430. {
  1431. rows = areaWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
  1432. total = areaWeaklyReportDtoList.Count
  1433. }.ToJson());
  1434. case 2:
  1435. var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND F_DeptNameSpell=3 ").Tables[0]);
  1436. var deptList1 = new BLL.T_Bus_WorkOrderReport().DataTableToDeptList1(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,B.F_MainDeptId,C.F_DeptName , row_number() over(partition by B.F_MainDeptId order by A.F_Id desc)FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId=B.F_WorkOrderId LEFT JOIN T_Sys_Department C ON B.F_MainDeptId=C.F_DeptId AND C.F_DeptNameSpell=3 WHERE datediff(s,'{startDate}',A.F_CreateTime)>0 and datediff(s,'{endDate}',A.F_CreateTime)<0 and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND B.F_IsDelete=0 and B.F_IsSure in (0,1,2) and B.F_State in (0,1)").Tables[0]).DistinctBy(x => x.WorkOrderId).ToList();//DataTableToDeptList
  1437. var deptList2 = new BLL.T_Bus_WorkOrderReport().DataTableToDeptList1(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,B.F_MainDeptId,C.F_DeptName , row_number() over(partition by B.F_MainDeptId order by A.F_Id desc)FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId=B.F_WorkOrderId LEFT JOIN T_Sys_Department C ON B.F_MainDeptId=C.F_DeptId AND C.F_DeptNameSpell=3 WHERE datediff(s,'{lastStartDate}',A.F_CreateTime)>0 and datediff(s,'{lastEndDate}',A.F_CreateTime)<0 and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND B.F_IsDelete=0 and B.F_IsSure in (0,1,2) and B.F_State in (0,1)").Tables[0]).DistinctBy(x => x.WorkOrderId).ToList();//DataTableToDeptList
  1438. List<DeptWeaklyReportDto> deptWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
  1439. deptList.ForEach(x =>
  1440. {
  1441. var query = deptList1.Where(y => y.FieldId!=null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).ToList();
  1442. //var query = deptList1.Where(y => y.FieldId == x.F_DeptId).ToList();
  1443. var assignedCount = query.Count();
  1444. var lastAssignedcount = deptList2.Where(y => y.FieldId != null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).Count();
  1445. //var lastAssignedcount = deptList2.Where(y => y.FieldId == x.F_DeptId).Count();
  1446. var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
  1447. var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
  1448. var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
  1449. deptWeaklyReportDtoList.Add(new DeptWeaklyReportDto
  1450. {
  1451. name = x.F_DeptName,
  1452. assignedcount = assignedCount,
  1453. countrate = countRate,
  1454. completedcount = completedCount,
  1455. completedrate = completedRate
  1456. });
  1457. });
  1458. if (isExport)
  1459. {
  1460. var dt = deptWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
  1461. new NPOIHelper().ExportToExcel($"按单位{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "单位", "受理数量", "数量同比", "办结数量", "办结率" });
  1462. return Success("导出excel");
  1463. }
  1464. return Content(new
  1465. {
  1466. rows = deptWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
  1467. total = deptWeaklyReportDtoList.Count
  1468. }.ToJson());
  1469. case 3:
  1470. var typeList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State=0 and F_ItemId=2 ").Tables[0]);
  1471. var typeList1 = new BLL.T_Bus_WorkOrderReport().DataTableToTypeList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_InfoType,B.F_Value , row_number() over(partition by A.F_InfoType order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoType=B.F_ValueId WHERE datediff(s,'{startDate}',A.F_CreateTime)>0 and datediff(s,'{endDate}',A.F_CreateTime)<0 and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND B.F_State=0 and B.F_ItemId=2 ").Tables[0]);
  1472. var typeList2 = new BLL.T_Bus_WorkOrderReport().DataTableToTypeList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_InfoType,B.F_Value , row_number() over(partition by A.F_InfoType order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoType=B.F_ValueId WHERE datediff(s,'{lastStartDate}',A.F_CreateTime)>0 and datediff(s,'{lastEndDate}',A.F_CreateTime)<0 and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND B.F_State=0 and B.F_ItemId=2 ").Tables[0]);
  1473. List<DeptWeaklyReportDto> typeWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
  1474. typeList.ForEach(x =>
  1475. {
  1476. var query = typeList1.Where(y => y.FieldId == x.F_ValueId);
  1477. var assignedCount = query.Count();
  1478. var lastAssignedcount = typeList2.Where(y => y.FieldId == x.F_ValueId).Count();
  1479. var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
  1480. var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
  1481. var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
  1482. typeWeaklyReportDtoList.Add(new DeptWeaklyReportDto
  1483. {
  1484. name = x.F_Value,
  1485. assignedcount = assignedCount,
  1486. countrate = countRate,
  1487. completedcount = completedCount,
  1488. completedrate = completedRate
  1489. });
  1490. });
  1491. if (isExport)
  1492. {
  1493. var dt = typeWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
  1494. new NPOIHelper().ExportToExcel($"按工单类型{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单类型", "受理数量", "数量同比", "办结数量", "办结率" });
  1495. return Success("导出excel");
  1496. }
  1497. return Content(new
  1498. {
  1499. rows = typeWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
  1500. total = typeWeaklyReportDtoList.Count
  1501. }.ToJson());
  1502. case 4:
  1503. var sourceList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State = 0 and F_ItemId = 1").Tables[0]);
  1504. var sourceList1 = new BLL.T_Bus_WorkOrderReport().DataTableToSourceList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_InfoSource,B.F_Value , row_number() over(partition by A.F_InfoSource order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoSource = B.F_ValueId WHERE datediff(s, '{startDate}', A.F_CreateTime) > 0 and datediff(s, '{endDate}', A.F_CreateTime) < 0 and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND B.F_State = 0 and B.F_ItemId = 1").Tables[0]);
  1505. var sourceList2 = new BLL.T_Bus_WorkOrderReport().DataTableToSourceList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,A.F_WorkState,A.F_InfoSource,B.F_Value , row_number() over(partition by A.F_InfoSource order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoSource = B.F_ValueId WHERE datediff(s, '{lastStartDate}', A.F_CreateTime) > 0 and datediff(s, '{lastEndDate}', A.F_CreateTime) < 0 and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND B.F_State = 0 and B.F_ItemId = 1").Tables[0]);
  1506. List<DeptWeaklyReportDto> sourceWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
  1507. sourceList.ForEach(x =>
  1508. {
  1509. var query = sourceList1.Where(y => y.FieldId == x.F_ValueId).ToList();
  1510. var assignedCount = query.Count();
  1511. var lastAssignedcount = sourceList2.Where(y => y.FieldId == x.F_ValueId).Count();
  1512. var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
  1513. var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
  1514. var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
  1515. sourceWeaklyReportDtoList.Add(new DeptWeaklyReportDto
  1516. {
  1517. name = x.F_Value,
  1518. assignedcount = assignedCount,
  1519. countrate = countRate,
  1520. completedcount = completedCount,
  1521. completedrate = completedRate
  1522. });
  1523. });
  1524. if (isExport)
  1525. {
  1526. var dt = sourceWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
  1527. new NPOIHelper().ExportToExcel($"按工单来源{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单来源", "受理数量", "数量同比", "办结数量", "办结率" });
  1528. return Success("导出excel");
  1529. }
  1530. return Content(new
  1531. {
  1532. rows = sourceWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
  1533. total = sourceWeaklyReportDtoList.Count
  1534. }.ToJson());
  1535. default:
  1536. return Error("参数错误");
  1537. }
  1538. }
  1539. /// <summary>
  1540. /// 工单日周月报
  1541. /// </summary>
  1542. /// <param name="start"></param>
  1543. /// <param name="end"></param>
  1544. /// <param name="type"></param>
  1545. /// <param name="pageIndex"></param>
  1546. /// <param name="pageSize"></param>
  1547. /// <returns></returns>
  1548. public ActionResult GetDayWeekMonthReport(DateTime? date, int type = 0, int dateType = 0, bool isExport = false,string source="")
  1549. {
  1550. if (date == null)
  1551. {
  1552. date = DateTime.Now.Date;
  1553. }
  1554. Dictionary<string, string> paras = new Dictionary<string, string>();
  1555. paras.Add("@date", date.Value.ToString("yyyy-MM-dd"));
  1556. paras.Add("@datatype", type.ToString());
  1557. paras.Add("@datetype", dateType.ToString());
  1558. paras.Add("@source", source);
  1559. var obj = DbHelperSQL.RunProcedure("P_DayWeekMonthReport", paras, "DayWeekMonthReport");
  1560. if (isExport)
  1561. {
  1562. var cols = new string[] { "名称", "受理数量", "数量同比", "结案数量", "结案率" };
  1563. var start = obj.Tables[1].Rows[0]["startdate"].ToString();
  1564. var end = obj.Tables[1].Rows[0]["enddate"].ToString();
  1565. new NPOIHelper().ExportToExcel($"工单报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  1566. return Success("导出excel");
  1567. }
  1568. return Success("成功", obj);
  1569. }
  1570. /// <summary>
  1571. /// 电话中心受话情况统计表
  1572. /// </summary>
  1573. /// <param name="starttime/"></param>
  1574. /// <param name="endtime"></param>
  1575. /// <param name="usercode"></param>
  1576. /// <param name="group"></param>
  1577. /// <param name="isExport"></param>
  1578. /// <returns></returns>
  1579. public ActionResult CenterReception(DateTime? starttime, DateTime? endtime,string usercode="",string group="",
  1580. bool isExport = false)
  1581. {
  1582. DateTime date = DateTime.Now;
  1583. if (starttime == null)
  1584. {
  1585. starttime = new DateTime(date.Year, date.Month, 1);
  1586. }
  1587. if (endtime == null)
  1588. {
  1589. endtime = DateTime.Now;
  1590. }
  1591. Dictionary<string, string> paras = new Dictionary<string, string>();
  1592. paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1593. paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1594. paras.Add("@usercode", usercode);
  1595. paras.Add("@group", group);
  1596. var obj = DbHelperSQL.RunProcedure("P_CenterReception", paras, "CenterReception");
  1597. if (isExport)
  1598. {
  1599. string message = new NPOIHelper().CenterReception(obj);
  1600. if (message == "")
  1601. return Success("导出excel");
  1602. else
  1603. return Error(message);
  1604. }
  1605. return Success("成功", obj);
  1606. }
  1607. /// <summary>
  1608. /// 局委办件统计
  1609. /// </summary>
  1610. /// <param name="starttime"></param>
  1611. /// <param name="endtime"></param>
  1612. /// <param name="deptid"></param>
  1613. /// <param name="source"></param>
  1614. /// <param name="isExport"></param>
  1615. /// <returns></returns>
  1616. public ActionResult BureauHandling(DateTime? starttime, DateTime? endtime,int deptid =0, int source=0, bool isExport = false)
  1617. {
  1618. DateTime date = DateTime.Now;
  1619. if (starttime == null)
  1620. {
  1621. starttime = new DateTime(date.Year, date.Month, 1);
  1622. }
  1623. if (endtime == null)
  1624. {
  1625. endtime = DateTime.Now;
  1626. }
  1627. string dept = "";
  1628. if (deptid>0)
  1629. dept= deptid.ToString();
  1630. string infosource = "";
  1631. if (source > 0)
  1632. infosource = source.ToString();
  1633. Dictionary<string, string> paras = new Dictionary<string, string>();
  1634. paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1635. paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1636. paras.Add("@deptid", dept);
  1637. paras.Add("@source", infosource);
  1638. var obj = DbHelperSQL.RunProcedure("P_DeptBureauHandling", paras, "DeptBureauHandling");
  1639. if (isExport)
  1640. {
  1641. string message = new NPOIHelper().BureauHandling(obj);
  1642. if (message == "")
  1643. return Success("导出excel");
  1644. else
  1645. return Error(message);
  1646. }
  1647. return Success("成功", obj);
  1648. }
  1649. /// <summary>
  1650. /// 局委评议统计
  1651. /// </summary>
  1652. /// <param name="starttime"></param>
  1653. /// <param name="endtime"></param>
  1654. /// <param name="deptid"></param>
  1655. /// <param name="source"></param>
  1656. /// <param name="isExport"></param>
  1657. /// <returns></returns>
  1658. public ActionResult DeptReview(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
  1659. {
  1660. DateTime date = DateTime.Now;
  1661. if (starttime == null)
  1662. {
  1663. starttime = new DateTime(date.Year, date.Month, 1);
  1664. }
  1665. if (endtime == null)
  1666. {
  1667. endtime = DateTime.Now;
  1668. }
  1669. string dept = "";
  1670. if (deptid > 0)
  1671. dept = deptid.ToString();
  1672. string infosource = "";
  1673. if (source > 0)
  1674. infosource = source.ToString();
  1675. Dictionary<string, string> paras = new Dictionary<string, string>();
  1676. paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1677. paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1678. paras.Add("@deptid", dept);
  1679. paras.Add("@source", infosource);
  1680. var obj = DbHelperSQL.RunProcedure("P_DeptReview", paras, "DeptReview");
  1681. if (isExport)
  1682. {
  1683. var cols = new string[] { "序号", "承办单位", "办件总数", "评议率", "评议总数", "一次评议满意", "一次评议不满意"
  1684. , "一次评议满意率", "二次评议满意", "二次评议不满意", "二级评议满意率"};
  1685. new NPOIHelper().ExportToExcel("局委评议统计", obj.Tables[0], cols);
  1686. return Success("导出excel");
  1687. }
  1688. return Success("成功", obj);
  1689. }
  1690. /// <summary>
  1691. /// 政民互动综合统计
  1692. /// </summary>
  1693. /// <param name="starttime"></param>
  1694. /// <param name="endtime"></param>
  1695. /// <param name="deptid"></param>
  1696. /// <param name="source"></param>
  1697. /// <param name="isExport"></param>
  1698. /// <returns></returns>
  1699. public ActionResult Government(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
  1700. {
  1701. DateTime date = DateTime.Now;
  1702. if (starttime == null)
  1703. {
  1704. starttime = new DateTime(date.Year, date.Month, 1);
  1705. }
  1706. if (endtime == null)
  1707. {
  1708. endtime = DateTime.Now;
  1709. }
  1710. string dept = "";
  1711. if (deptid > 0)
  1712. dept = deptid.ToString();
  1713. string infosource = "";
  1714. if (source > 0)
  1715. infosource = source.ToString();
  1716. Dictionary<string, string> paras = new Dictionary<string, string>();
  1717. paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1718. paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
  1719. paras.Add("@deptid", dept);
  1720. paras.Add("@source", infosource);
  1721. var obj = DbHelperSQL.RunProcedure("P_Government", paras, "Government");
  1722. if (isExport)
  1723. {
  1724. string message = new NPOIHelper().Government(obj);
  1725. if (message == "")
  1726. return Success("导出excel");
  1727. else
  1728. return Error(message);
  1729. }
  1730. return Success("成功", obj);
  1731. }
  1732. /// <summary>
  1733. /// 归并热线每日数据统计
  1734. /// </summary>
  1735. /// <param name="starttime"></param>
  1736. /// <param name="endtime"></param>
  1737. /// <returns></returns>
  1738. public ActionResult MergerHotline(DateTime? starttime , DateTime ? endtime,bool isExport = false )
  1739. {
  1740. DateTime date = DateTime.Now;
  1741. if (starttime ==null )
  1742. {
  1743. starttime = new DateTime(date.Year, date.Month, 1);
  1744. }
  1745. if (endtime == null)
  1746. {
  1747. endtime = DateTime.Now;
  1748. }
  1749. List<MergerHot.MergerHotSource> MergerHotSource = new List<MergerHot.MergerHotSource>();
  1750. var source = new BLL.T_Sys_DictionaryValue().GetModelList
  1751. ("F_PrentId ='2498' and F_State =0");
  1752. string key = "";
  1753. foreach (var it in source)
  1754. {
  1755. key += ",'"+it.F_Value+"'" ;
  1756. }
  1757. var CallRecords = new BLL.T_Call_CallRecords().GetModelList("CallType=0 and BeginTime between '" + starttime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + endtime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and PhoneType in ("+ key .TrimStart (',') + ")");
  1758. if (source !=null && source .Count >0)
  1759. {
  1760. for (DateTime dt = starttime.Value; dt <= endtime.Value; dt = dt.AddDays(1))
  1761. {
  1762. MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
  1763. mergerHotSources.Data = dt.ToString("MM月dd日");
  1764. mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
  1765. for (int i = 0; i < source.Count + 3; i++)
  1766. {
  1767. if (i < source.Count)
  1768. {
  1769. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1770. if (source[i].F_Value == "12345")
  1771. {
  1772. mergerHot.sort = source.Count;
  1773. }
  1774. else
  1775. {
  1776. mergerHot.sort = i-1;
  1777. }
  1778. mergerHot.Source = source[i ].F_Value ;
  1779. mergerHot.Call = CallRecords.Where(x => x.BeginTime
  1780. .Value.Day ==dt .Day && x.PhoneType == source[i].F_Value).Count();
  1781. mergerHot.Connect = CallRecords.Where(x => x.BeginTime
  1782. .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
  1783. mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
  1784. .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
  1785. && x.UserCode != null).Count();
  1786. mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
  1787. .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
  1788. || x.UserCode == null)).Count();
  1789. if (mergerHot.Call > 0)
  1790. {
  1791. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1792. }
  1793. else
  1794. {
  1795. mergerHot.rate = "100%";
  1796. }
  1797. mergerHotSources.MergerHot.Add(mergerHot);
  1798. }
  1799. else if (i == source.Count)
  1800. {
  1801. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1802. mergerHot.Source = "当日小计";
  1803. mergerHot.sort = source.Count + 1;
  1804. mergerHot.Call = CallRecords.Where(x => x.BeginTime
  1805. .Value.Day == dt.Day).Count();
  1806. mergerHot.Connect = CallRecords.Where(x => x.BeginTime
  1807. .Value.Day == dt.Day && x.CallState == 1).Count();
  1808. mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
  1809. .Value.Day == dt.Day && x.CallState == 0 && x.UserCode != ""
  1810. && x.UserCode != null).Count();
  1811. mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
  1812. .Value.Day == dt.Day && x.CallState == 0 && (x.UserCode == ""
  1813. || x.UserCode == null)).Count();
  1814. if (mergerHot.Call>0)
  1815. {
  1816. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1817. }
  1818. else
  1819. {
  1820. mergerHot.rate = "100%";
  1821. }
  1822. mergerHotSources.MergerHot.Add(mergerHot);
  1823. }
  1824. else if (i == source.Count+1)
  1825. {
  1826. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1827. mergerHot.Source = "累计";
  1828. mergerHot.sort = source.Count+2;
  1829. mergerHot.Call = CallRecords.Where(x => x.BeginTime
  1830. .Value.Date >=starttime .Value.Date && x.BeginTime
  1831. .Value.Date <= dt.Date ).Count();
  1832. mergerHot.Connect = CallRecords.Where(x => x.BeginTime
  1833. .Value.Date >= starttime.Value.Date && x.BeginTime
  1834. .Value.Date <= dt.Date && x.CallState == 1 ).Count();
  1835. mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
  1836. .Value.Date >= starttime.Value.Date && x.BeginTime
  1837. .Value.Date <= dt.Date && x.CallState == 0 && x.UserCode != ""
  1838. && x.UserCode != null).Count();
  1839. mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
  1840. .Value.Date >= starttime.Value.Date && x.BeginTime
  1841. .Value.Date <= dt.Date && x.CallState == 0 && (x.UserCode == ""
  1842. || x.UserCode == null)).Count();
  1843. if (mergerHot.Call > 0)
  1844. {
  1845. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1846. }
  1847. else
  1848. {
  1849. mergerHot.rate = "100%";
  1850. }
  1851. mergerHotSources.MergerHot.Add(mergerHot);
  1852. }
  1853. else if (i == source.Count+2)
  1854. {
  1855. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1856. mergerHot.Source = "归并热线小计";
  1857. mergerHot.sort = source.Count-1;
  1858. mergerHot.Call = CallRecords.Where(x => x.BeginTime
  1859. .Value.Day == dt.Day&&x.PhoneType !="12345").Count();
  1860. mergerHot.Connect = CallRecords.Where(x => x.BeginTime
  1861. .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType != "12345").Count();
  1862. mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
  1863. .Value.Day == dt.Day && x.CallState == 0 && x.UserCode != ""
  1864. && x.UserCode != null && x.PhoneType != "12345").Count();
  1865. mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
  1866. .Value.Day == dt.Day && x.CallState == 0 && (x.UserCode == ""
  1867. || x.UserCode == null) && x.PhoneType != "12345").Count();
  1868. mergerHot.callback = CallRecords.Where(x => x.BeginTime
  1869. .Value.Day == dt.Day &&x .PhoneType != "12345" && x.Callback == 1).Count();
  1870. if (mergerHot.Call > 0)
  1871. {
  1872. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1873. }
  1874. else
  1875. {
  1876. mergerHot.rate = "100%";
  1877. }
  1878. mergerHotSources.MergerHot.Add(mergerHot);
  1879. }
  1880. }
  1881. mergerHotSources.MergerHot= mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList ();
  1882. MergerHotSource.Add(mergerHotSources);
  1883. }
  1884. }
  1885. if (MergerHotSource!=null && MergerHotSource .Count >0)
  1886. {
  1887. MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
  1888. mergerHotSources.Data = "合计";
  1889. mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
  1890. for (int i = 0; i < source.Count + 2; i++)
  1891. {
  1892. if (i < source.Count)
  1893. {
  1894. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1895. mergerHot.Source = source[i].F_Value;
  1896. mergerHot.Call = CallRecords.Where(x=> x.PhoneType == source[i].F_Value).Count();
  1897. mergerHot.Connect = CallRecords.Where(x => x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
  1898. mergerHot.Notconnected = CallRecords.Where(x => x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
  1899. && x.UserCode != null).Count();
  1900. mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
  1901. || x.UserCode == null)).Count();
  1902. if (source[i].F_Value=="12345")
  1903. {
  1904. mergerHot.sort = source.Count ;
  1905. }
  1906. else
  1907. {
  1908. mergerHot.sort =i -1;
  1909. }
  1910. if (mergerHot.Call > 0)
  1911. {
  1912. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1913. }
  1914. else
  1915. {
  1916. mergerHot.rate = "100%";
  1917. }
  1918. mergerHotSources.MergerHot.Add(mergerHot);
  1919. }
  1920. else if (i == source.Count)
  1921. {
  1922. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1923. mergerHot.Source ="当日小计";
  1924. mergerHot.sort = source.Count + 1;
  1925. mergerHot.Call = CallRecords.Count();
  1926. mergerHot.Connect = CallRecords.Where(x =>x.CallState == 1).Count();
  1927. mergerHot.Notconnected = CallRecords.Where(x => x.CallState == 0 && x.UserCode != ""
  1928. && x.UserCode != null).Count();
  1929. mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && (x.UserCode == ""
  1930. || x.UserCode == null)).Count();
  1931. if (mergerHot.Call > 0)
  1932. {
  1933. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1934. }
  1935. else
  1936. {
  1937. mergerHot.rate = "100%";
  1938. }
  1939. mergerHotSources.MergerHot.Add(mergerHot);
  1940. }
  1941. else if (i == source.Count+1)
  1942. {
  1943. MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
  1944. mergerHot.Source = "归并热线小计";
  1945. mergerHot.sort = source.Count - 1;
  1946. mergerHot.Call = CallRecords.Where(x => x.PhoneType != "12345").Count();
  1947. mergerHot.Connect = CallRecords.Where(x => x.PhoneType != "12345").Count();
  1948. mergerHot.Notconnected = CallRecords.Where(x =>x.CallState == 0 && x.UserCode != ""
  1949. && x.UserCode != null && x.PhoneType != "12345").Count();
  1950. mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && (x.UserCode == ""
  1951. || x.UserCode == null && x.PhoneType != "12345")).Count();
  1952. mergerHot.callback = CallRecords.Where(x => x.PhoneType != "12345" && x.Callback == 1).Count();
  1953. if (mergerHot.Call > 0)
  1954. {
  1955. mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
  1956. }
  1957. else
  1958. {
  1959. mergerHot.rate = "100%";
  1960. }
  1961. mergerHotSources.MergerHot.Add(mergerHot);
  1962. }
  1963. }
  1964. mergerHotSources.MergerHot = mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList();
  1965. MergerHotSource.Add(mergerHotSources);
  1966. }
  1967. if (isExport)
  1968. {
  1969. new NPOIHelper().ExportMergerToExcel(MergerHotSource);
  1970. return Success("导出excel");
  1971. }
  1972. return Success("获取成功", MergerHotSource);
  1973. }
  1974. /// <summary>
  1975. /// 调度工作量统计
  1976. /// </summary>
  1977. /// <param name="start"></param>
  1978. /// <param name="end"></param>
  1979. /// <param name="pageIndex"></param>
  1980. /// <param name="pageSize"></param>
  1981. /// <param name="isExport"></param>
  1982. /// <returns></returns>
  1983. public ActionResult GetWorkLoad1(DateTime? start, DateTime? end, bool isExport = false)
  1984. {
  1985. if (start == null)
  1986. {
  1987. start = DateTime.Now;
  1988. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  1989. }
  1990. if (end == null)
  1991. {
  1992. end = DateTime.Now;
  1993. }
  1994. Dictionary<string, string> paras = new Dictionary<string, string>();
  1995. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  1996. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  1997. var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport", paras, "LeaderAssessmentReport");
  1998. if (isExport)
  1999. {
  2000. var cols = new string[] { "姓名", "回退量", "交办量", "退回审核量", "延时审核量", "重办量", "结案量" };
  2001. new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  2002. return Success("导出excel");
  2003. }
  2004. return Success("成功", obj);
  2005. }
  2006. public ActionResult GetWorkLoad(DateTime? start, DateTime? end, bool isExport = false, string source = "")
  2007. {
  2008. if (start == null)
  2009. {
  2010. start = DateTime.Now;
  2011. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  2012. }
  2013. if (end == null)
  2014. {
  2015. end = DateTime.Now;
  2016. }
  2017. Dictionary<string, string> paras = new Dictionary<string, string>();
  2018. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  2019. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  2020. paras.Add("@source", source);
  2021. var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport_1", paras, "LeaderAssessmentReport");
  2022. if (isExport)
  2023. {
  2024. var cols = new string[] { "姓名", "回退量", "交办量", "退回审核量", "延时审核量", "重办量", "结案量" };
  2025. new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  2026. return Success("导出excel");
  2027. }
  2028. return Success("成功", obj);
  2029. }
  2030. /// <summary>
  2031. /// 获取未处理报表
  2032. /// </summary>
  2033. /// <param name="date"></param>
  2034. /// <param name="isdc"></param>
  2035. /// <returns></returns>
  2036. public ActionResult GetNoDealReport(DateTime? start, DateTime? end, int deptid = 0, int pageIndex = 1, int pageSize = 10, int isdc = 0)
  2037. {
  2038. if (start == null)
  2039. {
  2040. start = DateTime.Now;
  2041. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  2042. }
  2043. if (end == null)
  2044. {
  2045. end = DateTime.Now;
  2046. }
  2047. var where = "";
  2048. if (deptid > 0)
  2049. {
  2050. where = " AND F_MainDeptId=" + deptid;
  2051. }
  2052. string sql = $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 " + where;
  2053. if (isdc > 0)
  2054. {
  2055. string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时/剩余", "工单状态" };
  2056. var dtall = DbHelperSQL.Query("select * from V_Bus_NoDeal where 1=1 " + sql).Tables[0];
  2057. dtall.Columns.Remove("F_MainDeptId");
  2058. dtall.Columns.Remove("F_CreateTime");
  2059. dtall.Columns.Remove("F_WorkState");
  2060. new NPOIHelper().ExportToExcel($"单位未处理统计报表{start.Value.ToString("yyyyMMdd")}-{end.Value.ToString("yyyyMMdd")}", dtall, cols);
  2061. return Success("导出excel");
  2062. }
  2063. int recordCount = 0;
  2064. var dt = BLL.PagerBLL.GetListPager(
  2065. "V_Bus_NoDeal",
  2066. "F_WorkOrderId",
  2067. "*",
  2068. sql,
  2069. "ORDER BY F_CreateTime DESC",
  2070. pageSize,
  2071. pageIndex,
  2072. true,
  2073. out recordCount);
  2074. var obj = new
  2075. {
  2076. state = "success",
  2077. message = "成功",
  2078. rows = dt,
  2079. total = recordCount
  2080. };
  2081. return Content(obj.ToJson());
  2082. }
  2083. #region 20190618 zhengbingbing 新增处理量统计按照时间筛选
  2084. /// <summary>
  2085. /// 处理量统计
  2086. /// </summary>
  2087. /// <param name="sdate"></param>
  2088. /// <param name="edate"></param>
  2089. /// <param name="isExport"></param>
  2090. /// <returns></returns>
  2091. public ActionResult GetDeptDayReport1(string sdate, string edate,int isdept=1, bool isExport = false)
  2092. {
  2093. if (string.IsNullOrEmpty(sdate))
  2094. {
  2095. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2096. }
  2097. if (string.IsNullOrEmpty(edate))
  2098. {
  2099. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2100. }
  2101. if (User.F_RoleCode == "GLY")
  2102. {
  2103. var maindeptid = 0;
  2104. if (User.F_RoleCode == "WLDW")
  2105. {
  2106. maindeptid = User.F_DeptId;
  2107. }
  2108. else if (User.F_RoleCode == "EJWLDW")
  2109. {
  2110. maindeptid = User.F_DeptId;
  2111. isdept = 2;
  2112. }
  2113. }
  2114. Dictionary<string, string> paras = new Dictionary<string, string>();
  2115. paras.Add("@sdate", sdate);
  2116. paras.Add("@edate", edate);
  2117. paras.Add("@isdept", isdept.ToString ());
  2118. var obj = DbHelperSQL.RunProcedure("P_DeptDayReport", paras, "DeptDayReport");
  2119. if (isExport)
  2120. {
  2121. var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
  2122. new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
  2123. return Success("导出excel");
  2124. }
  2125. return Success("成功", obj);
  2126. }
  2127. public ActionResult GetDeptDayReport(string sdate, string edate, int isdept = 1, bool isExport = false,string source="")
  2128. {
  2129. if (string.IsNullOrEmpty(sdate))
  2130. {
  2131. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2132. }
  2133. if (string.IsNullOrEmpty(edate))
  2134. {
  2135. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2136. }
  2137. if (User.F_RoleCode != "GLY")
  2138. {
  2139. var maindeptid = 0;
  2140. if (User.F_RoleCode == "WLDW")
  2141. {
  2142. maindeptid = User.F_DeptId;
  2143. }
  2144. else if (User.F_RoleCode == "EJWLDW")
  2145. {
  2146. maindeptid = User.F_DeptId;
  2147. isdept = 2;
  2148. }
  2149. }
  2150. Dictionary<string, string> paras = new Dictionary<string, string>();
  2151. paras.Add("@sdate", sdate);
  2152. paras.Add("@edate", edate);
  2153. paras.Add("@isdept", isdept.ToString());
  2154. paras.Add("@source", source);
  2155. var obj = DbHelperSQL.RunProcedure("P_DeptDayReport_1", paras, "DeptDayReport");
  2156. if (obj!=null && obj.Tables[0]!=null && obj.Tables[0].Rows.Count > 0) {
  2157. for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
  2158. {
  2159. int acceptcount = 0, dealcount = 0, endcount = 0;double dealrate = 0, endrate = 0;
  2160. int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(),out acceptcount);
  2161. int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
  2162. int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
  2163. if(acceptcount>0)
  2164. {
  2165. dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
  2166. endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
  2167. obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
  2168. obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
  2169. }
  2170. }
  2171. }
  2172. if (isExport)
  2173. {
  2174. var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
  2175. new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
  2176. return Success("导出excel");
  2177. }
  2178. return Success("成功", obj);
  2179. }
  2180. #endregion
  2181. #region 20191104 zhengbingbing 增加三级单位统计项
  2182. /// <summary>
  2183. /// 三级单位处理量统计
  2184. /// </summary>
  2185. /// <param name="sdate"></param>
  2186. /// <param name="edate"></param>
  2187. /// <param name="isExport"></param>
  2188. /// <returns></returns>
  2189. public ActionResult GetDept3DayReport1(string sdate, string edate, bool isExport = false)
  2190. {
  2191. if (string.IsNullOrEmpty(sdate))
  2192. {
  2193. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2194. }
  2195. if (string.IsNullOrEmpty(edate))
  2196. {
  2197. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2198. }
  2199. int deptid = 0;
  2200. if (User.F_RoleCode != "GLY")
  2201. {
  2202. if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
  2203. {
  2204. deptid = User.F_DeptId;
  2205. }
  2206. }
  2207. Dictionary<string, string> paras = new Dictionary<string, string>();
  2208. paras.Add("@sdate", sdate);
  2209. paras.Add("@edate", edate);
  2210. paras.Add("@deptid", deptid.ToString());
  2211. var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport", paras, "Dept3DayReport");
  2212. if (isExport)
  2213. {
  2214. var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
  2215. new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
  2216. return Success("导出excel");
  2217. }
  2218. return Success("成功", obj);
  2219. }
  2220. public ActionResult GetDept3DayReport(string sdate, string edate, bool isExport = false,string source = "")
  2221. {
  2222. if (string.IsNullOrEmpty(sdate))
  2223. {
  2224. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2225. }
  2226. if (string.IsNullOrEmpty(edate))
  2227. {
  2228. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2229. }
  2230. int deptid = 0;
  2231. if (User.F_RoleCode != "GLY")
  2232. {
  2233. if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
  2234. {
  2235. deptid = User.F_DeptId;
  2236. }
  2237. }
  2238. Dictionary<string, string> paras = new Dictionary<string, string>();
  2239. paras.Add("@sdate", sdate);
  2240. paras.Add("@edate", edate);
  2241. paras.Add("@deptid", deptid.ToString());
  2242. paras.Add("@source", source);
  2243. var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport_1", paras, "Dept3DayReport");
  2244. if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
  2245. {
  2246. for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
  2247. {
  2248. int acceptcount = 0, dealcount = 0, endcount = 0; double dealrate = 0, endrate = 0;
  2249. int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(), out acceptcount);
  2250. int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
  2251. int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
  2252. if (acceptcount > 0)
  2253. {
  2254. dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
  2255. endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
  2256. obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
  2257. obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
  2258. }
  2259. }
  2260. }
  2261. if (isExport)
  2262. {
  2263. var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
  2264. new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
  2265. return Success("导出excel");
  2266. }
  2267. return Success("成功", obj);
  2268. }
  2269. /// <summary>
  2270. /// 三级单位满意度统计
  2271. /// </summary>
  2272. /// <param name="sdate"></param>
  2273. /// <param name="edate"></param>
  2274. /// <param name="isExport"></param>
  2275. /// <returns></returns>
  2276. public ActionResult GetDept3SatisfiedReport(string sdate, string edate, bool isExport = false, string source = "")
  2277. {
  2278. if (string.IsNullOrEmpty(sdate))
  2279. {
  2280. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2281. }
  2282. if (string.IsNullOrEmpty(edate))
  2283. {
  2284. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2285. }
  2286. int deptid = 0;
  2287. if (User.F_RoleCode != "GLY")
  2288. {
  2289. if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
  2290. {
  2291. deptid = User.F_DeptId;
  2292. }
  2293. }
  2294. Dictionary<string, string> paras = new Dictionary<string, string>();
  2295. paras.Add("@sdate", sdate);
  2296. paras.Add("@edate", edate);
  2297. paras.Add("@deptid", deptid.ToString());
  2298. paras.Add("@source", source);
  2299. var obj = DbHelperSQL.RunProcedure("P_Dept3SatisfiedReport", paras, "Dept3SatisfiedReport");
  2300. if (isExport)
  2301. {
  2302. string[] cols = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
  2303. new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(sdate).ToString("yyyyMMdd")}-{Convert.ToDateTime(edate).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  2304. return Success("导出excel");
  2305. }
  2306. return Success("成功", obj);
  2307. }
  2308. /// <summary>
  2309. /// 三级单位处理效率统计
  2310. /// </summary>
  2311. /// <param name="sdate"></param>
  2312. /// <param name="edate"></param>
  2313. /// <param name="isExport"></param>
  2314. /// <returns></returns>
  2315. public ActionResult GetDept3EfficiencyReport(string sdate, string edate, bool isExport = false, string source = "")
  2316. {
  2317. if (string.IsNullOrEmpty(sdate))
  2318. {
  2319. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2320. }
  2321. if (string.IsNullOrEmpty(edate))
  2322. {
  2323. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2324. }
  2325. int deptid = 0;
  2326. if (User.F_RoleCode != "GLY")
  2327. {
  2328. if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
  2329. {
  2330. deptid = User.F_DeptId;
  2331. }
  2332. }
  2333. Dictionary<string, string> paras = new Dictionary<string, string>();
  2334. paras.Add("@sdate", sdate);
  2335. paras.Add("@edate", edate);
  2336. paras.Add("@deptid", deptid.ToString());
  2337. paras.Add("@source", source);
  2338. var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport", paras, "Dept3EfficiencyReport");
  2339. if (isExport)
  2340. {
  2341. string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
  2342. NPOIHelper npoi = new NPOIHelper();
  2343. if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
  2344. {
  2345. return Success("导出成功");
  2346. }
  2347. else
  2348. {
  2349. return Error("导出失败");
  2350. }
  2351. }
  2352. return Success("成功", obj);
  2353. }
  2354. public ActionResult GetDept3EfficiencyReport1(string sdate, string edate, bool isExport = false)
  2355. {
  2356. if (string.IsNullOrEmpty(sdate))
  2357. {
  2358. sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
  2359. }
  2360. if (string.IsNullOrEmpty(edate))
  2361. {
  2362. edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
  2363. }
  2364. int deptid = 0;
  2365. if (User.F_RoleCode != "GLY")
  2366. {
  2367. if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
  2368. {
  2369. deptid = User.F_DeptId;
  2370. }
  2371. }
  2372. Dictionary<string, string> paras = new Dictionary<string, string>();
  2373. paras.Add("@sdate", sdate);
  2374. paras.Add("@edate", edate);
  2375. paras.Add("@deptid", deptid.ToString());
  2376. var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport_1", paras, "Dept3EfficiencyReport");
  2377. var newSet = new DataSet();
  2378. if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
  2379. {
  2380. obj.Tables[0].Columns.Add("rate", typeof(decimal));
  2381. for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
  2382. {
  2383. int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
  2384. int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
  2385. int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
  2386. int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
  2387. timeoutcount = completedcount - timelycount;
  2388. obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
  2389. if (completedcount > 0)
  2390. {
  2391. timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
  2392. obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
  2393. obj.Tables[0].Rows[i]["rate"] = timelyrate;
  2394. }
  2395. }
  2396. obj.Tables[0].DefaultView.Sort = "rate desc";
  2397. newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
  2398. newSet.Tables[0].Columns.Remove("rate");
  2399. }
  2400. if (isExport)
  2401. {
  2402. string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
  2403. NPOIHelper npoi = new NPOIHelper();
  2404. if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
  2405. {
  2406. return Success("导出成功");
  2407. }
  2408. else
  2409. {
  2410. return Error("导出失败");
  2411. }
  2412. }
  2413. return Success("成功", newSet);
  2414. }
  2415. #endregion
  2416. /// <summary>
  2417. /// 单位月统计
  2418. /// </summary>
  2419. /// <param name="date"></param>
  2420. /// <param name="isExport"></param>
  2421. /// <returns></returns>
  2422. public ActionResult GetDeptMonthReport(string date, bool isExport = false)
  2423. {
  2424. if (string.IsNullOrEmpty(date))
  2425. {
  2426. date = DateTime.Now.Date.ToString("yyyy-MM");
  2427. }
  2428. Dictionary<string, string> paras = new Dictionary<string, string>();
  2429. paras.Add("@date", date);
  2430. var obj = DbHelperSQL.RunProcedure("P_DeptMonthReport", paras, "DeptMonthReport");
  2431. if (isExport)
  2432. {
  2433. var cols = new string[] { "名称", "当月受理数量", "当月处理量", "往月处理量", "当月延期量", "往月延期量", "当月重办量", "往月重办量", "当月结案量", "当月办理率", "当月办结率" };
  2434. new NPOIHelper().ExportToExcel($"工单报表{date}", obj.Tables[0], cols);
  2435. return Success("导出excel");
  2436. }
  2437. return Success("成功", obj);
  2438. }
  2439. /// <summary>
  2440. /// 单位办理率统计
  2441. /// </summary>
  2442. /// <param name="start"></param>
  2443. /// <param name="end"></param>
  2444. /// <param name="pageIndex"></param>
  2445. /// <param name="pageSize"></param>
  2446. /// <param name="isExport"></param>
  2447. /// <returns></returns>
  2448. public ActionResult GetDeptEndRate(string month, int type = 0, bool isExport = false)
  2449. {
  2450. string start = "", end = "";
  2451. if (month != null)
  2452. {
  2453. var date = DateTime.Parse(month + "-01");
  2454. start = date.ToString("yyyy-MM-dd"); ;
  2455. end = date.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
  2456. }
  2457. Dictionary<string, string> paras = new Dictionary<string, string>();
  2458. paras.Add("@sdate", start);
  2459. paras.Add("@edate", end);
  2460. paras.Add("@type", type.ToString());
  2461. var obj = DbHelperSQL.RunProcedure("P_DeptEndRateReport", paras, "DeptEndRateReport");
  2462. if (isExport)
  2463. {
  2464. var cols = new string[] { "单位", "受理数量", "办理数量", "结案数量", "结案率" };
  2465. new NPOIHelper().ExportToExcel($"单位办理率统计{month}", obj.Tables[0], cols, 1);
  2466. return Success("导出excel");
  2467. }
  2468. return Success("成功", obj);
  2469. }
  2470. /// <summary>
  2471. /// 工单热点分布
  2472. /// </summary>
  2473. /// <param name="start"></param>
  2474. /// <param name="end"></param>
  2475. /// <param name="deptid"></param>
  2476. /// <param name="keyid"></param>
  2477. /// <param name="sourcearea"></param>
  2478. /// <param name="source"></param>
  2479. /// <returns></returns>
  2480. public ActionResult GetHotspotMap(DateTime? start, DateTime? end, int deptid = 0, int keyid = 0,int sourcearea = 0, int source=0)
  2481. {
  2482. if (start == null)
  2483. {
  2484. start = DateTime.Now;
  2485. //start = new DateTime(start.Value.Year, start.Value.Month, 1);
  2486. start = DateTime.Now.AddDays(-3);
  2487. }
  2488. if (end == null)
  2489. {
  2490. end = DateTime.Now;
  2491. }
  2492. var where = " and ISNUMERIC(F_Longitude)>0 and ISNUMERIC(F_Latitude)>0 ";
  2493. if (deptid > 0)
  2494. where += " and F_WorkOrderID in(select F_WorkOrderID from T_Bus_AssignedInfo where F_MainDeptId = '" + deptid + "' and F_IsSure in (0,1) and F_State=1 and F_IsDelete=0 )";
  2495. if (keyid > 0)
  2496. where += " and ','+F_Key+',' like '%," + keyid + ",%' ";
  2497. if (sourcearea > 0)
  2498. where += " and F_SourceArea='" + sourcearea+"' ";
  2499. if (source > 0)
  2500. where += " and F_InfoSource = '" + source + "' ";
  2501. string sql = $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 " + where;
  2502. var orderlist = workOrderBLL.GetList(" 1=1 "+sql).Tables[0];
  2503. var list = new List<string[]>();
  2504. foreach (DataRow dr in orderlist.Rows)
  2505. {
  2506. var longitude = dr["F_Longitude"].ToString();
  2507. var latitude = dr["F_Latitude"].ToString();
  2508. var title= dr["F_ComTitle"].ToString();
  2509. var content= dr["F_ComContent"].ToString();
  2510. var strobj = new string[] { longitude, latitude, title, content };
  2511. list.Add(strobj);
  2512. }
  2513. return Success("",list);
  2514. }
  2515. #region 20191121 zhengbingbing 新增质检工作统计:工单回访量、回访量、结案量、工单重办量、重办量
  2516. public ActionResult GetQualityAssessment(DateTime? start, DateTime? end, bool isExport = false)
  2517. {
  2518. if (start == null)
  2519. {
  2520. start = DateTime.Now;
  2521. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  2522. }
  2523. if (end == null)
  2524. {
  2525. end = DateTime.Now;
  2526. }
  2527. Dictionary<string, string> paras = new Dictionary<string, string>();
  2528. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  2529. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  2530. var wherevi = " and F_CreateTime between '"+ start.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '"+ end.Value.ToString("yyyy-MM-dd 23:59:59") + "'";
  2531. var whereja = " and F_CloseTime between '" + start.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '" + end.Value.ToString("yyyy-MM-dd 23:59:59") + "'";
  2532. string sql = "select '('+F_UserCode+')'+F_UserName 姓名" +
  2533. ",(select COUNT(1) from(select F_CreateUser, F_WorkOrderId from T_Bus_VisitResult where F_IsDelete = 0 "+ wherevi + " group by F_CreateUser, F_WorkOrderId) t where F_CreateUser = F_UserCode) 工单回访量" +
  2534. ",(select COUNT(1) from T_Bus_VisitResult where F_IsDelete = 0 " + wherevi + " and F_CreateUser = F_UserCode and F_IsDelete = 0) 回访量" +
  2535. ",(select COUNT(1) from T_Bus_WorkOrder where F_WorkState = 9 and F_IsDelete = 0 and F_CloseUser = F_UserCode "+ whereja + ") 结案量" +
  2536. ",(select COUNT(1) from(select F_CreateUser, F_WorkOrderId from T_Bus_Operation where F_IsDelete = 0 "+ wherevi + " and F_State = 11 and F_WorkOrderId in (select F_WorkOrderId from T_Bus_Operation where F_IsDelete = 0 and F_State = 8) group by F_CreateUser,F_WorkOrderId) tt where F_CreateUser = F_UserCode) 工单重办量" +
  2537. ",(select COUNT(1) from T_Bus_Operation where F_IsDelete = 0 and F_State = 11 and F_CreateUser = F_UserCode " + wherevi + ") 重办量" +
  2538. " from T_Sys_UserAccount where F_RoleId = 18 and F_DeleteFlag = 0 order by F_UserCode";
  2539. var obj = DbHelperSQL.Query(sql);
  2540. if (isExport)
  2541. {
  2542. string[] cols = new string[] { "姓名", "工单回访量", "回访量", "结案量", "工单重办量", "重办量" };
  2543. NPOIHelper npoi = new NPOIHelper();
  2544. if (npoi.ExportToExcel("质检工作统计报表", obj.Tables[0], cols) == "")
  2545. {
  2546. return Success("导出成功");
  2547. }
  2548. else
  2549. {
  2550. return Error("导出失败");
  2551. }
  2552. }
  2553. return Success("成功", obj);
  2554. }
  2555. #endregion
  2556. /// <summary>
  2557. ///
  2558. /// </summary>
  2559. /// <param name="start"></param>
  2560. /// <param name="end"></param>
  2561. /// <param name="isExport"></param>
  2562. /// <returns></returns>
  2563. public ActionResult GetAudithandling(DateTime? start, DateTime? end, bool isExport = false)
  2564. {
  2565. if (start == null)
  2566. {
  2567. start = DateTime.Now;
  2568. start = new DateTime(start.Value.Year, start.Value.Month, 1);
  2569. }
  2570. if (end == null)
  2571. {
  2572. end = DateTime.Now;
  2573. }
  2574. Dictionary<string, string> paras = new Dictionary<string, string>();
  2575. paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
  2576. paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
  2577. var obj = DbHelperSQL.RunProcedure("P_Audithandling", paras, "Audithandling");
  2578. if (isExport)
  2579. {
  2580. var cols = new string[] { "单位名称", "办理件数量", "退回件数量", "延时件数量","超期未果数量" };
  2581. new NPOIHelper().ExportToExcel($"审核办理工单统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
  2582. return Success("导出excel");
  2583. }
  2584. return Success("成功", obj);
  2585. }
  2586. }
  2587. }