| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756 |
- using CallCenter.Utility;
- using CallCenter.Utility.Linq;
- using CallCenter.Utility.Time;
- using CallCenterApi.Common;
- using CallCenterApi.DB;
- using CallCenterApi.Interface.Controllers.Base;
- using CallCenterApi.Interface.Models.Dto;
- using CallCenterApi.Model;
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using System.Text;
- using System.Web;
- using System.Web.Mvc;
- using static CallCenter.Utility.NPOIHelper;
- namespace CallCenterApi.Interface.Controllers.report
- {
- [Authority]
- public class WorkOrderReportController : BaseController
- {
- private readonly BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();
- private readonly BLL.T_Bus_WorkOrder workOrderBLL = new BLL.T_Bus_WorkOrder();
- private readonly BLL.T_Bus_AssignedInfo assignedInfoBLL = new BLL.T_Bus_AssignedInfo();
- private readonly BLL.T_Bus_Feedback feedbackBLL = new BLL.T_Bus_Feedback();
- private readonly BLL.T_Sys_DictionaryValue dictValueBLL = new BLL.T_Sys_DictionaryValue();
- private readonly BLL.T_Sys_Area areaBLL = new BLL.T_Sys_Area();
- private readonly BLL.T_Bus_VisitResult visitResultBLL = new BLL.T_Bus_VisitResult();
- private readonly BLL.T_Bus_DelayTime delayTimeBLL = new BLL.T_Bus_DelayTime();
- private readonly BLL.T_Sys_UserAccount userAccount = new BLL.T_Sys_UserAccount();
- private readonly BLL.T_Bus_SubmitSuper submitsuperBLL = new BLL.T_Bus_SubmitSuper();
- /// <summary>
- /// 处理数量统计
- /// </summary>
- /// <returns></returns>
- public ActionResult GetDeptCount(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_DeptDealReport", paras, "DeptDealReport");
- if (isExport)
- {
- var cols = new string[] { "单位名称", "数量" };
- new NPOIHelper().ExportToExcel($"处理数量统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 处理效率统计
- /// </summary>
- /// <returns></returns>
- public ActionResult GetDeptEfficiency1(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport", paras, "DeptEfficiencyReport");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- public ActionResult GetDeptEfficiency(DateTime? start, DateTime? end, bool isExport = false,string source="")
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport_1", paras, "DeptEfficiencyReport");
- var newSet = new DataSet();
- if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
- {
- obj.Tables[0].Columns.Add("rate", typeof(decimal));
- for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
- {
- int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
- int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
- int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
- int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
- timeoutcount = completedcount - timelycount;
- obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
-
- if (completedcount > 0)
- {
- timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
- obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
- obj.Tables[0].Rows[i]["rate"] = timelyrate;
- }
- }
- obj.Tables[0].DefaultView.Sort = "rate desc";
- newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
- newSet.Tables[0].Columns.Remove("rate");
- //obj.Tables[0] = obj.Tables[0].DefaultView.ToTable();
- }
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", newSet);
- }
-
- /// <summary>
- /// 市民满意度评价
- /// </summary>
- /// <returns></returns>
- public ActionResult GetDeptSatisfied(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_DeptSatisfied", paras, "DeptSatisfied");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
- new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- var ob = new
- {
- state = "success",
- message = "成功",
- DeptSatisfiedReport = obj ,
- };
- return Success("成功", ob );
-
- }
- /// <summary>
- /// 县区满意度评价
- /// </summary>
- /// <returns></returns>
- #region 20191022 调整多主办单位需调整
- public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- //var sql = "F_IsDelete=0 and F_IsSure=1 and F_State=1 and isnull(F_FeedbackTime,'')<>'' "
- // + "and datediff(day, F_FeedbackTime, @sdate) <= 0 and datediff(day, F_FeedbackTime, @edate) >= 0"
- // + "and F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder where F_WorkState = 9)";
- //var assignedInfoList = assignedInfoBLL.GetModelList(sql);
- //var workorderList = workOrderBLL.GetModelList("");
- //var deptList = departmentBLL.GetModelList(" F_State=0 AND F_PartentId=0 ");
- //foreach (var item in deptList)
- //{
- // 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 + ") ");
- //}
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");
- if (isExport)
- {
- string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
- new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- //public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
- //{
- // if (start == null)
- // {
- // start = DateTime.Now;
- // start = new DateTime(start.Value.Year, start.Value.Month, 1);
- // }
- // if (end == null)
- // {
- // end = DateTime.Now;
- // }
- // //string sql = $"select * from dbo.GetDeptSatisfied('{start.Value.ToString("yyyy-MM-dd")}','{end.Value.ToString("yyyy-MM-dd")}') ORDER BY rate DESC";
- // //var dtall = DbHelperSQL.Query(sql).Tables[0];
- // Dictionary<string, string> paras = new Dictionary<string, string>();
- // paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- // paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- // var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");
- // if (isExport)
- // {
- // string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
- // new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- // return Success("导出excel");
- // }
- // return Success("成功", obj);
- // //int recordCount = 0;
- // //var dt = BLL.PagerBLL.GetListPager(
- // // "("+sql+") as v",
- // // "deptname",
- // // "*",
- // // "",
- // // "ORDER BY rate DESC",
- // // pageSize,
- // // pageIndex,
- // // true,
- // // out recordCount);
- // //var obj = new
- // //{
- // // state = "success",
- // // message = "成功",
- // // rows = dt,
- // // total = recordCount
- // //};
- // //return Content(obj.ToJson());
- //}
- #endregion
-
- /// <summary>
- /// 单位绩效考核
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <returns></returns>
- public ActionResult GetDeptAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="")
- {
- StringBuilder sb1 = new StringBuilder();
- StringBuilder sb2 = new StringBuilder();
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_DeptAssessmentReport", paras, "DeptAssessmentReport");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "已办总量", "及时量", "超时量", "超时时长(小时)", "延时量", "处理时长(小时)", "平均处理时长(小时)", "最大处理时长(小时)", "回访量", "市民满意", "市民不满意" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("单位绩效考核报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
-
- /// <summary>
- /// 坐席工作统计
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <returns></returns>
- public ActionResult GetUserAccountAssessment1(DateTime? start, DateTime? end, bool isExport = false)
- {
- StringBuilder sb1 = new StringBuilder();
- StringBuilder sb2 = new StringBuilder();
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport", paras, "UserAssessmentReport");
- if (isExport)
- {
- string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量"};
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- public ActionResult GetUserAccountAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="",string name="")
- {
- StringBuilder sb1 = new StringBuilder();
- StringBuilder sb2 = new StringBuilder();
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@source", source);
- paras.Add("@name", name);
-
- var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport_1", paras, "UserAssessmentReport");
- if (isExport)
- {
- string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量","呼出量" ,"回拨量"};
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- public ActionResult GetUserReturn(DateTime? start, DateTime? end, bool isExport = false)
- {
- StringBuilder sb1 = new StringBuilder();
- StringBuilder sb2 = new StringBuilder();
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- var obj = DbHelperSQL.RunProcedure("P_UserReturn", paras, "UserReturn");
- if (isExport)
- {
- string[] cols = new string[] { "姓名", "工单量", "回访量", "未回访量", "回访率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("坐席回访统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
-
- public ActionResult WorkOrdrList(int isdc=0)
- {
- DataTable dt = new DataTable();
- string sql = " and F_IsDelete=0 ";
-
- int sourceArea = RequestString.GetInt("sourceArea", 0);
- int keyid = RequestString.GetInt("keyid", 0);
- int deptid = RequestString.GetInt("deptid", 0);
- int key = RequestString.GetInt("key", 0);
- int timetype = RequestString.GetInt("timetype", 0);
- int ishf = RequestString.GetInt("ishf", 0);
- int area = RequestString.GetInt("area", 0);
- int state = RequestString.GetInt("state", -1);
- int type = RequestString.GetInt("type", 0);
- int source = RequestString.GetInt("source", 0);
- string mobile = RequestString.GetQueryString("mobile");
- int dbdb = RequestString.GetInt("dbdb", 0);
- int db = RequestString.GetInt("db", 0);
- int ys = RequestString.GetInt("ys", 0);
- string dbusercode = RequestString.GetQueryString("dbusercode");
- string strpageindex = RequestString.GetQueryString("page");
- string strpagesize = RequestString.GetQueryString("pagesize");
- string usercode = RequestString.GetQueryString("usercode");
- string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
- string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
- int isRelease = RequestString.GetInt("isRelease", 0);
- int name= RequestString.GetInt("name", 0);
- int handling = RequestString.GetInt("handling", 0);
- int pageindex = 1;
- int pagesize = 10;
- if (strpageindex.Trim() != "")
- {
- pageindex = Convert.ToInt32(strpageindex);
- }
- if (strpagesize.Trim() != "")
- {
- pagesize = Convert.ToInt32(strpagesize);
- }
- if (!string .IsNullOrEmpty (usercode))
- {
- sql += " and F_CreateUser='"+ usercode + "' ";
- }
- string value = "";
- if (string .IsNullOrEmpty (strstarttime)&& string.IsNullOrEmpty(strendtime))
- {
-
- if (db > 0 || dbdb>0)
- {
-
- DateTime start;
- start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
- if (dbdb==5)
- {
- value += "and datediff(SS ,F_CreateTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
- value += " and datediff(SS ,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
- }
- else
- {
- value += "and datediff(SS ,SupervisorTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
- value += " and datediff(SS ,SupervisorTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
- }
-
- }
- else
- {
- if (timetype == 1)
- {
- sql += "and datediff(DAY ,a.F_CreateTime ,getdate())=0 ";
- }
- else if (timetype == 2)
- {
- sql += "and datediff(WEEK ,a.F_CreateTime ,getdate())=0 ";
- }
- else if (timetype == 4)
- {
- }
- else
- {
- sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
- }
- }
- }
- else
- {
- if (db>0 || dbdb > 0)
- {
-
-
- if (dbdb == 5)
- {
- if (!string.IsNullOrEmpty(strstarttime))
- {
- value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
- }
- if (!string.IsNullOrEmpty(strendtime))
- {
- value += " and datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
- }
- }
- else
- {
- if (!string.IsNullOrEmpty(strstarttime))
- {
- value += "and datediff(SS ,SupervisorTime,'" + strstarttime + "')<=0 ";
- }
- if (!string.IsNullOrEmpty(strendtime))
- {
- value += " and datediff(SS ,SupervisorTime,'" + strendtime + "')>=0";
- }
- }
- }
- else
- {
- if (!string.IsNullOrEmpty(strstarttime))
- {
- sql += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
- }
- if (!string.IsNullOrEmpty(strendtime))
- {
- sql += " and datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
- }
- }
-
- }
- if (dbdb>0)
- {
- 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'";
- }
- if(db>0)
- {
-
- if (!string .IsNullOrEmpty (dbusercode))
- {
- value += "and Supervisor='" + dbusercode + "'";
- }
- if (db==1)
- {
- 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 )";
- }
- else if (db == 2)
- {
- sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 1 " + value + " )";
- }
- else if (db == 3)
- {
- sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 2 " + value + " ) ";
- }
- else if (db == 4)
- {
- sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 " + value + " ) and F_WorkState = '9'";
- }
- }
- if (!string .IsNullOrEmpty (mobile ))
- {
- sql += "and a.F_CusPhone = '" + mobile + "'";
- }
- if (area > 0)
- {
- sql += "and a.F_SourceArea='" + area + "'";
- }
- if (state>-1)
- {
- sql += "and a.F_WorkState='" + state + "'";
- }
- if (type > 0)
- {
- sql += "and a.F_InfoType='" + type + "'";
- }
- if (source > 0)
- {
- sql += "and a.F_InfoSource='" + source + "'";
- }
- if (isRelease>0)
- {
- if (isRelease==2)
- sql += " and (F_IsRelease =2 or F_IsRelease is null or F_IsRelease ='')";
- else
- sql += "and F_IsRelease ='"+ isRelease + "'";
- }
- if (ishf>0)
- {
-
- if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
- {
- value += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
- }
- else
- {
- if (!string.IsNullOrEmpty(strstarttime))
- {
- value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
- }
- }
- if (ishf==1)
- {
- sql += "and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult WITH(NOLOCK) " +
- "and F_IsDelete =0 "+ value + " ) ";
-
- }
- else
- {
- sql += "and F_WorkOrderId not in (select F_WorkOrderId from T_Bus_VisitResult WITH(NOLOCK) " +
- "and F_IsDelete =0 " + value + " and F_IsSms !=1 ) ";
- }
- }
- if (handling>0)
- {
- if (deptid <= 0)
- return Error("请选择部门");
- if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
- {
- sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
- }
- sql += "F_MainDeptId='" + deptid + "' and F_IsResult=0";
- switch (handling)
- {
- case 2:
- break;
- }
-
- }
- if (ys>0)
- {
- sql += " and F_InfoSource =2501";
- if (ys ==2)
- {
- //直办总量
- sql += "and F_IsResult=1 ";
- }
- else if (ys == 3)
- {
- //直办办结量
- sql += "and F_IsResult=1 and F_WorkState =9 ";
- }
- else if (ys == 4)
- {
- //转办总量
- sql += "and F_IsResult=0 ";
- }
- else if (ys == 5)
- {
- //转办办结量
- sql += "and F_IsResult=0 and F_WorkState =9 ";
- }
- else if (ys == 6)
- {
- //正常回复
- sql += " and F_WorkState =9 ";
- }
- else if (ys == 7)
- {
- //已办未果
- sql += " and FF_WorkState in (6,7)and F_IsProResult in(2,3) ";
- }
- else if (ys == 8)
- {
- //超期回复
- sql += " and F_DealTime >F_LimitTime and F_WorkState in(6,7,9) " +
- "and F_MainDeptId is not null and F_LimitTime is not null ";
- }
- else if (ys == 10)
- {
- //待审信件
- sql += " and F_WorkState in(1,11) ";
- }
- else if (ys == 11)
- {
- //正在办理
- sql += " and F_WorkState in(2,4,5,8) ";
- }
- else if (ys == 12)
- {
- //超期未果
- sql += " and F_LimitTime <getdate() and F_WorkState NOT IN (6,7,9) " +
- "and F_MainDeptId is not null and F_LimitTime is not null ";
- }
- else if (ys == 13)
- {
- //重置转发
- sql += " and F_WorkState=8 and F_Identification=0 ";
- }
- else if (ys == 14)
- {
- //驳回记录
- sql += " and F_WorkState=8 and F_Identification in(1,2) ";
- }
- else if (ys == 15)
- {
- //关联件数
- sql += " and F_IsRelease in (2,3) ";
- }
- else if (ys == 16)
- {
- //评议量
- sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment) ";
- }
- else if (ys == 17)
- {
- //评议满意量
- sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment where strWrkOrder_Cst_Ssf_Cd not like '%不满意%') ";
- }
- else if (ys == 18)
- {
- //评议不满意量
- sql += " and F_WorkOrderId in (select WorkOrderId from PublicComment where strWrkOrder_Cst_Ssf_Cd like '%不满意%') ";
- }
- else if (ys == 19)
- {
- //回访量
- sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult ) ";
- }
- else if (ys == 20)
- {
- //回访满意量
- sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult where F_IsSatisfie=1 ) ";
- }
- else if (ys == 21)
- {
- //回访不满意量
- sql += " and F_WorkOrderId in (select F_WorkOrderId from T_Bus_VisitResult where F_IsSatisfie=0 ) ";
- }
- }
- string cols = "";
- string order = "ORDER BY a.F_CreateTime DESC";
- if (name >0)
- {
- cols = "distinct F_CusName";
- order = "";
- }
- else if (db>0)
- {
- 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";
- }
- else
- {
- 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";
- }
- if (isdc > 0)
- {
- var top = " "; var orderby = " order by F_CreateTime desc";
- if (sql == " and F_IsDelete=0 ")
- {
- top = " top 1000 "; orderby += " desc ";
- }
- if (db == 1)
- {
- var dtdc = DbHelperSQL.Query(" select ROW_NUMBER() OVER(ORDER BY F_CreateTime desc) 编号, F_WorkOrderId 事项单号, dbo.GetDeptNames(F_MainDeptId ) 承办单位, "
- + "(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 ) 督办方式"
- + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + order).Tables[0];
- var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
- if (msg == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- else
- {
- 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) 反映类别, "
- + " dbo.GetDeptNames(F_MainDeptId ) 主办单位, "
- + " (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) 办理结果, "
- + " F_CusName 来电人姓名 ,F_CusPhone 来电人手机号 "
- + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + orderby).Tables[0];
- var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
- if (msg == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
-
- }
- else
- {
- int recordCount = 0;
- dt = BLL.PagerBLL.GetListPager(
- "T_Bus_WorkOrder a WITH(NOLOCK)",
- "a.F_WorkOrderId",
- cols,
- sql,
- order,
- pagesize,
- pageindex,
- true,
- out recordCount);
- var obj = new
- {
- state = "success",
- message = "成功",
- rows = dt,
- total = recordCount
- };
- return Content(obj.ToJson());
- }
-
- }
-
- public ActionResult IncomingCalls (string date,int isdc=0)
- {
- if (string.IsNullOrEmpty(date))
- date = DateTime.Now.ToString("yyyy-MM-dd");
- var modelcalllist = new BLL
- .T_Call_CallRecords().GetModelList(" CallType =0 and DATEDIFF(DAY, BeginTime, '"+ date + "') = 0");
- var userlist = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag =1");
- int ldcount = 0;int jtcount = 0; int newldcount = 0; int newjtcount = 0;
- List<Incoming> incomings = new List<Incoming>();
-
- //incoming.time = "00:00-08:00";
- //incoming.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
- //).Count();
- //ldcount += incoming.IncomingCount;
- //incoming.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
- //&&x.CallState ==1 ).Count();
- //jtcount += incoming.Oncapacity;
- //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
- // ();
-
- //if (modelcalllists!=null&& modelcalllists.Count() >0)
- //{
- // incoming.seatsCount = modelcalllists.Count();
- // foreach (var it in modelcalllists)
- // {
- // var user = userlist.Where(x => x.F_UserCode == it);
- // if (user != null && user.Count() > 0)
- // incoming.seats += "," + user.FirstOrDefault().F_UserName;
- // else
- // incoming.seats += "," + it;
- // }
- // if (!string .IsNullOrEmpty(incoming.seats))
- // incoming.seats = incoming.seats.TrimStart(',');
- //}
- //else
- //{
- // incoming.seatsCount =0;
- // incoming.seats = "";
- //}
- //incomings.Add(incoming);
- int v = 24;
- if (DateTime .Parse (date ).Year ==DateTime .Now .Year &&
- DateTime.Parse(date).Month == DateTime.Now.Month && DateTime.Parse(date).Day == DateTime.Now.Day)
- {
- v = DateTime.Now.Hour;
- }
- for (int i=0;i < v; i ++)
- {
- Incoming incomin = new Incoming();
- int t = i + 1;
- string iss=i.ToString () ,tss=t .ToString ();
- if (i < 10)
- iss = "0" + i;
- if (t < 10)
- tss = "0" + t;
- incomin.time = iss + ":00" + "-" + tss + ":00";
- incomin.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i && x.PhoneType == "12345"
- ).Count();
- ldcount += incomin.IncomingCount;
- incomin.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i
- && x.CallState == 1 && x.PhoneType == "12345").Count();
- jtcount += incomin.Oncapacity;
- incomin.newIncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour == i
- ).Count();
- newldcount += incomin.newIncomingCount;
- incomin.newOncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour == i
- && x.CallState == 1 ).Count();
- newjtcount += incomin.newOncapacity;
- var modelcalllistss = modelcalllist.Where(x => x.UserCode != null&& x.UserCode != "" && x.BeginTime.Value.Hour==i&&x.PhoneType =="12345").Select(x => x.UserCode).Distinct
- ();
- if (modelcalllistss != null && modelcalllistss.Count() > 0)
- {
- incomin.seatsCount = modelcalllistss.Count();
- foreach (var it in modelcalllistss)
- {
- var user = userlist.Where(x => x.F_UserCode == it);
- if (user != null && user.Count() > 0)
- incomin.seats += "," + user.FirstOrDefault().F_UserName;
- else
- incomin.seats += "," + it;
- }
- if (!string.IsNullOrEmpty(incomin.seats))
- incomin.seats= incomin.seats.TrimStart(',');
- }
- else
- {
- incomin.seatsCount = 0;
- incomin.seats = "";
- }
- var modelcalllists = modelcalllist.Where(x => x.UserCode != null && x.UserCode != "" && x.BeginTime.Value.Hour == i && x.PhoneType != "12345").Select(x => x.UserCode).Distinct
- ();
- if (modelcalllists != null && modelcalllists.Count() > 0)
- {
- incomin.newseatsCount = modelcalllists.Count();
- foreach (var it in modelcalllists)
- {
- var user = userlist.Where(x => x.F_UserCode == it);
- if (user != null && user.Count() > 0)
- incomin.newseats += "," + user.FirstOrDefault().F_UserName;
- else
- incomin.newseats += "," + it;
- }
- if (!string.IsNullOrEmpty(incomin.newseats))
- incomin.newseats = incomin.newseats.TrimStart(',');
- }
- else
- {
- incomin.newseatsCount = 0;
- incomin.newseats = "";
- }
- incomings.Add(incomin);
- }
- Incoming incomint = new Incoming();
-
- incomint.time = "共计";
- incomint.IncomingCount = ldcount;
- incomint.Oncapacity = jtcount;
- incomint.newIncomingCount = newldcount;
- incomint.newOncapacity = newjtcount;
- incomings.Add(incomint);
- if (isdc >0)
- {
- new NPOIHelper().IncomingCalls(incomings);
- return Success("导出excel");
- }
- return Success("获取成功", incomings);
- }
- /// <summary>
- /// 逾期未回复统计
- /// </summary>
- /// <param name="date"></param>
- /// <param name="deptId"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <returns></returns>
- public ActionResult GetOutTimeNotApply(DateTime? start, DateTime? end, int deptId = -1, int pageIndex = 1, int pageSize = 10, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- var where = "";
- if (deptId > 0)
- {
- where = " AND F_MainDeptId=" + deptId;
- }
- 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;
-
- if (isExport)
- {
- string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时时间" };
- var dtall = DbHelperSQL.Query("select * from V_Bus_OverNoBack where 1=1 "+sql).Tables[0];
- dtall.Columns.Remove("F_MainDeptId");
- dtall.Columns.Remove("F_CreateTime");
- new NPOIHelper().ExportToExcel($"逾期未回复统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
- return Success("导出excel");
- }
- int recordCount = 0;
- var dt = BLL.PagerBLL.GetListPager(
- "V_Bus_OverNoBack",
- "F_WorkOrderId",
- "*",
- sql,
- "ORDER BY F_CreateTime DESC",
- pageSize,
- pageIndex,
- true,
- out recordCount);
- var obj = new
- {
- state = "success",
- message = "成功",
- rows = dt,
- total = recordCount
- };
- return Content(obj.ToJson());
- }
-
- /// <summary>
- /// 重办不满意统计
- /// </summary>
- /// <param name="date"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <returns></returns>
- public ActionResult GetNotSatisfied(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- string sql = $" and datediff(day,cjTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,cjTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
- if (isExport)
- {
- string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "创建时间", "处理结果", "处理时间", "回访结果", "回访时间" };
- var dtall = DbHelperSQL.Query("select * from V_Bus_ReloadNoSatisfie where 1=1 " + sql + " order by cjTime DESC ").Tables[0];
- dtall.Columns.Remove("F_Id");
- new NPOIHelper().ExportToExcel($"重办不满意统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
- return Success("导出excel");
- }
- int recordCount = 0;
- var dt = BLL.PagerBLL.GetListPager(
- "V_Bus_ReloadNoSatisfie",
- "F_Id",
- "*",
- sql,
- "ORDER BY cjTime DESC",
- pageSize,
- pageIndex,
- true,
- out recordCount);
- var obj = new
- {
- state = "success",
- message = "成功",
- rows = dt,
- total = recordCount
- };
- return Content(obj.ToJson());
- }
-
- /// <summary>
- /// 延期统计
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDelay(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_DeptDelayReport", paras, "DeptDelayReport");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "办理数量", "延时数量", "延时率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("延时分析统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 通报批评
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetNotice(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- //string sql = "select * from dbo.GetNoticeReport('"+ start.Value.ToString("yyyy-MM-dd") + "','" + end.Value.ToString("yyyy-MM-dd") + "') ORDER BY zbcount+xbcount DESC";
- //var dt = DbHelperSQL.Query(sql).Tables[0];
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_DeptNoticeReport", paras, "DeptNoticeReport");
- if (isExport)
- {
- var cols = new string[] { "承办单位", "主办受理数量", "主办通报批评数量", "主办通报批评率", "协办受理数量", "协办通报批评数量", "协办通报批评率" };
- new NPOIHelper().ExportToExcel($"通报批评{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
-
- /// <summary>
- /// 诉求受理对比
- /// </summary>
- /// <returns></returns>
- public ActionResult GetAppeal1(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_UserAppealReport", paras, "UserAppealReport");
- if (isExport)
- {
- var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
- new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- public ActionResult GetAppeal(DateTime? start, DateTime? end, bool isExport = false
- , string source = "")
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_UserAppealReport_1", paras, "UserAppealReport");
- var newSet = new DataSet();var newtable = new DataTable("UserAppealReport");
- #region
- newtable.Columns.Add(new DataColumn("username"));
- newtable.Columns.Add(new DataColumn("count", typeof(int)));
- newtable.Columns.Add(new DataColumn("mcount", typeof(int)));
- newtable.Columns.Add(new DataColumn("mom"));
- newtable.Columns.Add(new DataColumn("ycount", typeof(int)));
- newtable.Columns.Add(new DataColumn("yoy"));
- #endregion
- if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
- {
-
- for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
- {
- int count = 0, mcount = 0, ycount = 0; double mom = 0, yoy = 0;
- int.TryParse(obj.Tables[0].Rows[i]["count"].ToString(), out count);
- int.TryParse(obj.Tables[0].Rows[i]["mcount"].ToString(), out mcount);
- int.TryParse(obj.Tables[0].Rows[i]["ycount"].ToString(), out ycount);
-
- if(mcount>0)
- mom = double.Parse((count * 100).ToString()) / mcount;
- if (ycount > 0)
- yoy = double.Parse((count * 100).ToString()) / ycount;
- #region
- DataRow drNew = newtable.NewRow();
- drNew["username"] = obj.Tables[0].Rows[i]["username"];
- drNew["count"] = count;
- drNew["mcount"] = mcount;
- drNew["mom"] = mom > 0 ? mom.ToString("f") + "%" : "0";
- drNew["ycount"] = ycount;
- drNew["yoy"] = yoy > 0 ? yoy.ToString("f") + "%" : "0";
- newtable.Rows.Add(drNew);
- #endregion
- }
- newtable.DefaultView.Sort = "count desc";
- newSet.Tables.Add(newtable.DefaultView.ToTable());
- }
- if (isExport)
- {
- var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
- new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", newSet.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", newSet);
- }
- /// <summary>
- /// 获取督办报表
- /// </summary>
- /// <returns></returns>
- public ActionResult GetDBReportform(string starttime,string endtime)
- {
-
- if (string .IsNullOrEmpty (starttime))
- {
- starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- else
- {
- if (string.IsNullOrEmpty(endtime))
- {
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime);
- paras.Add("@edate", endtime);
- var obj1 = DbHelperSQL.RunProcedure("P_DBReportform_1", paras, "DBReportform").Tables[0];
- 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 +
- "' AND '" + endtime + "')) and F_WorkState !='9'and F_IsDelete =0 ";
- DataTable dttype = DbHelperSQL.Query(sqlarea).Tables[0];
- var obj = new
- {
- obj1,
- total = dttype
- };
-
- return Content(obj.ToJson());
- }
- /// <summary>
- /// 坐席状态统计
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <returns></returns>
- public ActionResult GetZuoXiState(string starttime, string endtime)
- {
- if (string.IsNullOrEmpty(starttime))
- {
- starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- else
- {
- if (string.IsNullOrEmpty(endtime))
- {
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- }
- string sql = " a.OccurTime between '" + starttime + "' AND '" + endtime + "'";
- string value = " TimeLogin between '" + starttime + "' AND '" + endtime + "'";
- string value1 = " TimeLogout between '" + starttime + "' AND '" + endtime + "'";
- 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" +
- " 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 ";
- DataTable obj = DbHelperSQL.Query(sqlarea).Tables[0];
- return Content(obj.ToJson());
- }
- /// <summary>
- /// 坐席时间
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <returns></returns>
- public ActionResult GetZuoXiTime (string starttime, string endtime,string usercode,int state=0)
- {
- if (string.IsNullOrEmpty(starttime))
- {
- starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- else
- {
- if (string.IsNullOrEmpty(endtime))
- {
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- }
- if (string.IsNullOrEmpty(usercode))
- return Error("请选择坐席");
- string sqlzuoxi = "";
- if (state==0)
- {
- sqlzuoxi = "select TimeLogin from rep_agent_detail where TimeLogin between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "'and TimeLogin is not null ";
- }
- else if (state == 1)
- {
- sqlzuoxi = "select TimeLogout from rep_agent_detail where TimeLogout between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and TimeLogout is not null ";
- }
- else if (state == 2)
- {
- sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and State = 2 ";
- }
- else if (state == 3)
- {
- sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime between '" + starttime + "' AND '" + endtime + "' and AgentId ='" + usercode + "' and State = 5 ";
- }
- DataTable obj = DbHelperSQL.Query(sqlzuoxi).Tables[0];
- return Content(obj.ToJson());
- }
- /// <summary>
- /// 获取绩效
- /// </summary>
- /// <param name="date"></param>
- /// <param name="isdc"></param>
- /// <returns></returns>
- public ActionResult GetDeptDeptAchievements(string starttime, string endtime
- , int type=0,int deptid=0)
- {
- if (string.IsNullOrEmpty(starttime))
- {
- starttime = DateTime.Now.AddMonths (-1).ToString("yyyy-MM") + "-21 00:00:00";
- endtime = DateTime.Now.ToString("yyyy-MM") + "-20 00:00:00";
- }
- else
- {
- if (string.IsNullOrEmpty(endtime))
- {
- endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime);
- paras.Add("@edate", endtime);
- paras.Add("@type", type.ToString ());
- if (deptid>0)
- paras.Add("@deptid", deptid.ToString ());
- else
- paras.Add("@deptid","");
- var obj = DbHelperSQL.RunProcedure("P_DeptAchievements_1", paras, "DeptAchievements").Tables[0]; ;
- return Content(obj.ToJson());
- }
- /// <summary>
- /// 工单周报
- /// 暂时无用,多主办单位不处理
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="type"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <returns></returns>
- public ActionResult GetWeaklyReport(DateTime? start, DateTime? end, int type = 0, int dateType = 0, int pageIndex = 1, int pageSize = 10, bool isExport = false)
- {
- StringBuilder sb1 = new StringBuilder();
- StringBuilder sb2 = new StringBuilder();
- StringBuilder sb3 = new StringBuilder();
- var startDate = new DateTime();
- var endDate = new DateTime();
- var lastStartDate = new DateTime();
- var lastEndDate = new DateTime();
- var differ = 0;
- var str = "";
- switch (dateType)
- {
- //日
- case 1:
- if (start == null)
- {
- start = DateTime.Now.Date;
- }
- if (end == null)
- {
- end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
- }
- startDate = Convert.ToDateTime(start);
- endDate = Convert.ToDateTime(end).AddDays(1).AddSeconds(-1);
- differ = (endDate - startDate).Days + 1;
- lastStartDate = startDate.AddDays(-differ);
- lastEndDate = endDate.AddDays(-differ);
- str = "日";
- break;
- //周
- case 2:
- if (start == null)
- {
- start = DateTime.Now.Date;
- }
- if (end == null)
- {
- end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
- }
- startDate = Convert.ToDateTime(start);
- endDate = Convert.ToDateTime(end);
- differ = 7 - (int)startDate.DayOfWeek;
- startDate = DateTools.GetWeekFirstDayMon((DateTime)start);
- endDate = DateTools.GetWeekLastDaySun((DateTime)end).AddDays(1).AddSeconds(-1);
- differ = (endDate - startDate).Days + 1;
- lastStartDate = DateTools.GetWeekFirstDayMon(startDate.AddDays(-1));
- lastEndDate = DateTools.GetWeekLastDaySun(lastStartDate).AddDays(1).AddSeconds(-1);
- str = "周";
- break;
- //月
- case 3:
- if (start == null)
- {
- start = DateTime.Now.Date;
- }
- if (end == null)
- {
- end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
- }
- startDate = Convert.ToDateTime(start);
- endDate = Convert.ToDateTime(end);
- differ = startDate.Day;
- startDate = startDate.AddDays(-differ + 1);
- endDate = endDate.AddDays(DateTime.DaysInMonth(startDate.Year, startDate.Month) - differ).AddDays(1).AddSeconds(-1);
- //differ = (endDate - startDate). + 1;
- lastStartDate = startDate.AddMonths(-1);
- lastEndDate = lastStartDate.AddDays(DateTime.DaysInMonth(lastStartDate.Year, lastStartDate.Month) - 1).AddDays(1).AddSeconds(-1);
- str = "月";
- break;
- default:
- break;
- }
- //type 查询类型 type=1 按区域;type=2 按部门; type=3 按工单类型
- switch (type)
- {
- case 1:
- var areaList = areaBLL.DataTableToList(areaBLL.GetList(" F_State=0 ").Tables[0]);
- 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]);
- 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]);
- List<DeptWeaklyReportDto> areaWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
- areaList.ForEach(x =>
- {
- var query = areaList1.Where(y => x.F_AreaId == y.FieldId).ToList();
- var assignedCount = query.Count();
- var lastAssignedcount = areaList2.Where(y => x.F_AreaId == y.FieldId).Count();
- var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
- var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
- var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
- areaWeaklyReportDtoList.Add(new DeptWeaklyReportDto
- {
- name = x.F_AreaName,
- assignedcount = assignedCount,
- countrate = countRate,
- completedcount = completedCount,
- completedrate = completedRate
- });
- });
- if (isExport)
- {
- var dt = areaWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
- new NPOIHelper().ExportToExcel($"按区域{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "区域", "受理数量", "数量同比", "办结数量", "办结率" });
- return Success("导出excel");
- }
- return Content(new
- {
- rows = areaWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
- total = areaWeaklyReportDtoList.Count
- }.ToJson());
- case 2:
- var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND F_DeptNameSpell=3 ").Tables[0]);
- 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
- 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
- List<DeptWeaklyReportDto> deptWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
- deptList.ForEach(x =>
- {
- var query = deptList1.Where(y => y.FieldId!=null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).ToList();
- //var query = deptList1.Where(y => y.FieldId == x.F_DeptId).ToList();
- var assignedCount = query.Count();
- var lastAssignedcount = deptList2.Where(y => y.FieldId != null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).Count();
- //var lastAssignedcount = deptList2.Where(y => y.FieldId == x.F_DeptId).Count();
- var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
- var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
- var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
- deptWeaklyReportDtoList.Add(new DeptWeaklyReportDto
- {
- name = x.F_DeptName,
- assignedcount = assignedCount,
- countrate = countRate,
- completedcount = completedCount,
- completedrate = completedRate
- });
- });
- if (isExport)
- {
- var dt = deptWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
- new NPOIHelper().ExportToExcel($"按单位{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "单位", "受理数量", "数量同比", "办结数量", "办结率" });
- return Success("导出excel");
- }
- return Content(new
- {
- rows = deptWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
- total = deptWeaklyReportDtoList.Count
- }.ToJson());
- case 3:
- var typeList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State=0 and F_ItemId=2 ").Tables[0]);
- 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]);
- 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]);
- List<DeptWeaklyReportDto> typeWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
- typeList.ForEach(x =>
- {
- var query = typeList1.Where(y => y.FieldId == x.F_ValueId);
- var assignedCount = query.Count();
- var lastAssignedcount = typeList2.Where(y => y.FieldId == x.F_ValueId).Count();
- var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
- var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
- var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
- typeWeaklyReportDtoList.Add(new DeptWeaklyReportDto
- {
- name = x.F_Value,
- assignedcount = assignedCount,
- countrate = countRate,
- completedcount = completedCount,
- completedrate = completedRate
- });
- });
- if (isExport)
- {
- var dt = typeWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
- new NPOIHelper().ExportToExcel($"按工单类型{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单类型", "受理数量", "数量同比", "办结数量", "办结率" });
- return Success("导出excel");
- }
- return Content(new
- {
- rows = typeWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
- total = typeWeaklyReportDtoList.Count
- }.ToJson());
- case 4:
- var sourceList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State = 0 and F_ItemId = 1").Tables[0]);
- 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]);
- 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]);
- List<DeptWeaklyReportDto> sourceWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
- sourceList.ForEach(x =>
- {
- var query = sourceList1.Where(y => y.FieldId == x.F_ValueId).ToList();
- var assignedCount = query.Count();
- var lastAssignedcount = sourceList2.Where(y => y.FieldId == x.F_ValueId).Count();
- var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
- var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
- var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
- sourceWeaklyReportDtoList.Add(new DeptWeaklyReportDto
- {
- name = x.F_Value,
- assignedcount = assignedCount,
- countrate = countRate,
- completedcount = completedCount,
- completedrate = completedRate
- });
- });
- if (isExport)
- {
- var dt = sourceWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
- new NPOIHelper().ExportToExcel($"按工单来源{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单来源", "受理数量", "数量同比", "办结数量", "办结率" });
- return Success("导出excel");
- }
- return Content(new
- {
- rows = sourceWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
- total = sourceWeaklyReportDtoList.Count
- }.ToJson());
- default:
- return Error("参数错误");
- }
- }
- /// <summary>
- /// 工单日周月报
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="type"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <returns></returns>
- public ActionResult GetDayWeekMonthReport(DateTime? date, int type = 0, int dateType = 0, bool isExport = false,string source="")
- {
- if (date == null)
- {
- date = DateTime.Now.Date;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@date", date.Value.ToString("yyyy-MM-dd"));
- paras.Add("@datatype", type.ToString());
- paras.Add("@datetype", dateType.ToString());
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_DayWeekMonthReport", paras, "DayWeekMonthReport");
- if (isExport)
- {
- var cols = new string[] { "名称", "受理数量", "数量同比", "结案数量", "结案率" };
- var start = obj.Tables[1].Rows[0]["startdate"].ToString();
- var end = obj.Tables[1].Rows[0]["enddate"].ToString();
- new NPOIHelper().ExportToExcel($"工单报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 电话中心受话情况统计表
- /// </summary>
- /// <param name="starttime/"></param>
- /// <param name="endtime"></param>
- /// <param name="usercode"></param>
- /// <param name="group"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult CenterReception(DateTime? starttime, DateTime? endtime,string usercode="",string group="",
- bool isExport = false)
- {
- DateTime date = DateTime.Now;
- if (starttime == null)
- {
- starttime = new DateTime(date.Year, date.Month, 1);
- }
- if (endtime == null)
- {
- endtime = DateTime.Now;
- }
-
-
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@usercode", usercode);
- paras.Add("@group", group);
- var obj = DbHelperSQL.RunProcedure("P_CenterReception", paras, "CenterReception");
- if (isExport)
- {
- string message = new NPOIHelper().CenterReception(obj);
- if (message == "")
- return Success("导出excel");
- else
- return Error(message);
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 局委办件统计
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <param name="deptid"></param>
- /// <param name="source"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult BureauHandling(DateTime? starttime, DateTime? endtime,int deptid =0, int source=0, bool isExport = false)
- {
- DateTime date = DateTime.Now;
- if (starttime == null)
- {
- starttime = new DateTime(date.Year, date.Month, 1);
- }
- if (endtime == null)
- {
- endtime = DateTime.Now;
- }
- string dept = "";
- if (deptid>0)
- dept= deptid.ToString();
- string infosource = "";
- if (source > 0)
- infosource = source.ToString();
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@deptid", dept);
- paras.Add("@source", infosource);
- var obj = DbHelperSQL.RunProcedure("P_DeptBureauHandling", paras, "DeptBureauHandling");
- if (isExport)
- {
- string message = new NPOIHelper().BureauHandling(obj);
- if (message == "")
- return Success("导出excel");
- else
- return Error(message);
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 局委评议统计
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <param name="deptid"></param>
- /// <param name="source"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult DeptReview(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
- {
- DateTime date = DateTime.Now;
- if (starttime == null)
- {
- starttime = new DateTime(date.Year, date.Month, 1);
- }
- if (endtime == null)
- {
- endtime = DateTime.Now;
- }
- string dept = "";
- if (deptid > 0)
- dept = deptid.ToString();
- string infosource = "";
- if (source > 0)
- infosource = source.ToString();
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@deptid", dept);
- paras.Add("@source", infosource);
- var obj = DbHelperSQL.RunProcedure("P_DeptReview", paras, "DeptReview");
- if (isExport)
- {
- var cols = new string[] { "序号", "承办单位", "办件总数", "评议率", "评议总数", "一次评议满意", "一次评议不满意"
- , "一次评议满意率", "二次评议满意", "二次评议不满意", "二级评议满意率"};
- new NPOIHelper().ExportToExcel("局委评议统计", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 政民互动综合统计
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <param name="deptid"></param>
- /// <param name="source"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult Government(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
- {
- DateTime date = DateTime.Now;
- if (starttime == null)
- {
- starttime = new DateTime(date.Year, date.Month, 1);
- }
- if (endtime == null)
- {
- endtime = DateTime.Now;
- }
- string dept = "";
- if (deptid > 0)
- dept = deptid.ToString();
- string infosource = "";
- if (source > 0)
- infosource = source.ToString();
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
- paras.Add("@deptid", dept);
- paras.Add("@source", infosource);
- var obj = DbHelperSQL.RunProcedure("P_Government", paras, "Government");
- if (isExport)
- {
- string message = new NPOIHelper().Government(obj);
- if (message == "")
- return Success("导出excel");
- else
- return Error(message);
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 归并热线每日数据统计
- /// </summary>
- /// <param name="starttime"></param>
- /// <param name="endtime"></param>
- /// <returns></returns>
- public ActionResult MergerHotline(DateTime? starttime , DateTime ? endtime,bool isExport = false )
- {
- DateTime date = DateTime.Now;
- if (starttime ==null )
- {
- starttime = new DateTime(date.Year, date.Month, 1);
- }
- if (endtime == null)
- {
- endtime = DateTime.Now;
- }
- List<MergerHot.MergerHotSource> MergerHotSource = new List<MergerHot.MergerHotSource>();
- var source = new BLL.T_Sys_DictionaryValue().GetModelList
- ("F_PrentId ='2498' and F_State =0");
- string key = "";
- foreach (var it in source)
- {
- key += ",'"+it.F_Value+"'" ;
- }
- 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 (',') + ")");
-
- if (source !=null && source .Count >0)
- {
- for (DateTime dt = starttime.Value; dt <= endtime.Value; dt = dt.AddDays(1))
- {
- MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
- mergerHotSources.Data = dt.ToString("MM月dd日");
- mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
- for (int i = 0; i < source.Count + 3; i++)
- {
- if (i < source.Count)
- {
-
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- if (source[i].F_Value == "12345")
- {
- mergerHot.sort = source.Count;
- }
- else
- {
- mergerHot.sort = i-1;
- }
- mergerHot.Source = source[i ].F_Value ;
- mergerHot.Call = CallRecords.Where(x => x.BeginTime
- .Value.Day ==dt .Day && x.PhoneType == source[i].F_Value).Count();
- mergerHot.Connect = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
- && x.UserCode != null).Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
- || x.UserCode == null)).Count();
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
-
- mergerHotSources.MergerHot.Add(mergerHot);
-
- }
- else if (i == source.Count)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source = "当日小计";
- mergerHot.sort = source.Count + 1;
- mergerHot.Call = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day).Count();
- mergerHot.Connect = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 1).Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && x.UserCode != ""
- && x.UserCode != null).Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && (x.UserCode == ""
- || x.UserCode == null)).Count();
- if (mergerHot.Call>0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- else if (i == source.Count+1)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source = "累计";
- mergerHot.sort = source.Count+2;
- mergerHot.Call = CallRecords.Where(x => x.BeginTime
- .Value.Date >=starttime .Value.Date && x.BeginTime
- .Value.Date <= dt.Date ).Count();
- mergerHot.Connect = CallRecords.Where(x => x.BeginTime
- .Value.Date >= starttime.Value.Date && x.BeginTime
- .Value.Date <= dt.Date && x.CallState == 1 ).Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
- .Value.Date >= starttime.Value.Date && x.BeginTime
- .Value.Date <= dt.Date && x.CallState == 0 && x.UserCode != ""
- && x.UserCode != null).Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
- .Value.Date >= starttime.Value.Date && x.BeginTime
- .Value.Date <= dt.Date && x.CallState == 0 && (x.UserCode == ""
- || x.UserCode == null)).Count();
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- else if (i == source.Count+2)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source = "归并热线小计";
- mergerHot.sort = source.Count-1;
- mergerHot.Call = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day&&x.PhoneType !="12345").Count();
- mergerHot.Connect = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType != "12345").Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && x.UserCode != ""
- && x.UserCode != null && x.PhoneType != "12345").Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day && x.CallState == 0 && (x.UserCode == ""
- || x.UserCode == null) && x.PhoneType != "12345").Count();
- mergerHot.callback = CallRecords.Where(x => x.BeginTime
- .Value.Day == dt.Day &&x .PhoneType != "12345" && x.Callback == 1).Count();
-
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- }
- mergerHotSources.MergerHot= mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList ();
- MergerHotSource.Add(mergerHotSources);
- }
- }
- if (MergerHotSource!=null && MergerHotSource .Count >0)
- {
- MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
- mergerHotSources.Data = "合计";
- mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
- for (int i = 0; i < source.Count + 2; i++)
- {
- if (i < source.Count)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source = source[i].F_Value;
- mergerHot.Call = CallRecords.Where(x=> x.PhoneType == source[i].F_Value).Count();
- mergerHot.Connect = CallRecords.Where(x => x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
- && x.UserCode != null).Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
- || x.UserCode == null)).Count();
- if (source[i].F_Value=="12345")
- {
- mergerHot.sort = source.Count ;
- }
- else
- {
- mergerHot.sort =i -1;
- }
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- else if (i == source.Count)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source ="当日小计";
- mergerHot.sort = source.Count + 1;
- mergerHot.Call = CallRecords.Count();
- mergerHot.Connect = CallRecords.Where(x =>x.CallState == 1).Count();
- mergerHot.Notconnected = CallRecords.Where(x => x.CallState == 0 && x.UserCode != ""
- && x.UserCode != null).Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && (x.UserCode == ""
- || x.UserCode == null)).Count();
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- else if (i == source.Count+1)
- {
- MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
- mergerHot.Source = "归并热线小计";
- mergerHot.sort = source.Count - 1;
- mergerHot.Call = CallRecords.Where(x => x.PhoneType != "12345").Count();
- mergerHot.Connect = CallRecords.Where(x => x.PhoneType != "12345").Count();
- mergerHot.Notconnected = CallRecords.Where(x =>x.CallState == 0 && x.UserCode != ""
- && x.UserCode != null && x.PhoneType != "12345").Count();
- mergerHot.voluntarily = CallRecords.Where(x => x.CallState == 0 && (x.UserCode == ""
- || x.UserCode == null && x.PhoneType != "12345")).Count();
- mergerHot.callback = CallRecords.Where(x => x.PhoneType != "12345" && x.Callback == 1).Count();
- if (mergerHot.Call > 0)
- {
- mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
- }
- else
- {
- mergerHot.rate = "100%";
- }
- mergerHotSources.MergerHot.Add(mergerHot);
- }
- }
- mergerHotSources.MergerHot = mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList();
- MergerHotSource.Add(mergerHotSources);
- }
- if (isExport)
- {
-
- new NPOIHelper().ExportMergerToExcel(MergerHotSource);
- return Success("导出excel");
- }
- return Success("获取成功", MergerHotSource);
-
- }
-
- /// <summary>
- /// 调度工作量统计
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetWorkLoad1(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport", paras, "LeaderAssessmentReport");
- if (isExport)
- {
- var cols = new string[] { "姓名", "回退量", "交办量", "退回审核量", "延时审核量", "重办量", "结案量" };
- new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- public ActionResult GetWorkLoad(DateTime? start, DateTime? end, bool isExport = false, string source = "")
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport_1", paras, "LeaderAssessmentReport");
- if (isExport)
- {
- var cols = new string[] { "姓名", "回退量", "交办量", "退回审核量", "延时审核量", "重办量", "结案量" };
- new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 获取未处理报表
- /// </summary>
- /// <param name="date"></param>
- /// <param name="isdc"></param>
- /// <returns></returns>
- public ActionResult GetNoDealReport(DateTime? start, DateTime? end, int deptid = 0, int pageIndex = 1, int pageSize = 10, int isdc = 0)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- var where = "";
- if (deptid > 0)
- {
- where = " AND F_MainDeptId=" + deptid;
- }
- 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;
- if (isdc > 0)
- {
- string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时/剩余", "工单状态" };
- var dtall = DbHelperSQL.Query("select * from V_Bus_NoDeal where 1=1 " + sql).Tables[0];
- dtall.Columns.Remove("F_MainDeptId");
- dtall.Columns.Remove("F_CreateTime");
- dtall.Columns.Remove("F_WorkState");
- new NPOIHelper().ExportToExcel($"单位未处理统计报表{start.Value.ToString("yyyyMMdd")}-{end.Value.ToString("yyyyMMdd")}", dtall, cols);
- return Success("导出excel");
- }
- int recordCount = 0;
- var dt = BLL.PagerBLL.GetListPager(
- "V_Bus_NoDeal",
- "F_WorkOrderId",
- "*",
- sql,
- "ORDER BY F_CreateTime DESC",
- pageSize,
- pageIndex,
- true,
- out recordCount);
- var obj = new
- {
- state = "success",
- message = "成功",
- rows = dt,
- total = recordCount
- };
- return Content(obj.ToJson());
- }
-
- #region 20190618 zhengbingbing 新增处理量统计按照时间筛选
- /// <summary>
- /// 处理量统计
- /// </summary>
- /// <param name="sdate"></param>
- /// <param name="edate"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDeptDayReport1(string sdate, string edate,int isdept=1, bool isExport = false)
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- if (User.F_RoleCode == "GLY")
- {
- var maindeptid = 0;
- if (User.F_RoleCode == "WLDW")
- {
- maindeptid = User.F_DeptId;
- }
- else if (User.F_RoleCode == "EJWLDW")
- {
- maindeptid = User.F_DeptId;
- isdept = 2;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@isdept", isdept.ToString ());
- var obj = DbHelperSQL.RunProcedure("P_DeptDayReport", paras, "DeptDayReport");
- if (isExport)
- {
- var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
- new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- public ActionResult GetDeptDayReport(string sdate, string edate, int isdept = 1, bool isExport = false,string source="")
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- if (User.F_RoleCode != "GLY")
- {
- var maindeptid = 0;
- if (User.F_RoleCode == "WLDW")
- {
- maindeptid = User.F_DeptId;
- }
- else if (User.F_RoleCode == "EJWLDW")
- {
- maindeptid = User.F_DeptId;
- isdept = 2;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@isdept", isdept.ToString());
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_DeptDayReport_1", paras, "DeptDayReport");
- if (obj!=null && obj.Tables[0]!=null && obj.Tables[0].Rows.Count > 0) {
- for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
- {
- int acceptcount = 0, dealcount = 0, endcount = 0;double dealrate = 0, endrate = 0;
- int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(),out acceptcount);
- int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
- int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
- if(acceptcount>0)
- {
- dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
- endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
- obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
- obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
- }
-
- }
- }
- if (isExport)
- {
- var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
- new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
-
- #endregion
- #region 20191104 zhengbingbing 增加三级单位统计项
- /// <summary>
- /// 三级单位处理量统计
- /// </summary>
- /// <param name="sdate"></param>
- /// <param name="edate"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDept3DayReport1(string sdate, string edate, bool isExport = false)
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- int deptid = 0;
- if (User.F_RoleCode != "GLY")
- {
- if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
- {
- deptid = User.F_DeptId;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@deptid", deptid.ToString());
- var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport", paras, "Dept3DayReport");
- if (isExport)
- {
- var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
- new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- public ActionResult GetDept3DayReport(string sdate, string edate, bool isExport = false,string source = "")
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- int deptid = 0;
- if (User.F_RoleCode != "GLY")
- {
- if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
- {
- deptid = User.F_DeptId;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@deptid", deptid.ToString());
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport_1", paras, "Dept3DayReport");
- if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
- {
- for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
- {
- int acceptcount = 0, dealcount = 0, endcount = 0; double dealrate = 0, endrate = 0;
- int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(), out acceptcount);
- int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
- int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
- if (acceptcount > 0)
- {
- dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
- endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
- obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
- obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
- }
- }
- }
- if (isExport)
- {
- var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
- new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 三级单位满意度统计
- /// </summary>
- /// <param name="sdate"></param>
- /// <param name="edate"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDept3SatisfiedReport(string sdate, string edate, bool isExport = false, string source = "")
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- int deptid = 0;
- if (User.F_RoleCode != "GLY")
- {
- if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
- {
- deptid = User.F_DeptId;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@deptid", deptid.ToString());
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_Dept3SatisfiedReport", paras, "Dept3SatisfiedReport");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
- new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(sdate).ToString("yyyyMMdd")}-{Convert.ToDateTime(edate).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 三级单位处理效率统计
- /// </summary>
- /// <param name="sdate"></param>
- /// <param name="edate"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDept3EfficiencyReport(string sdate, string edate, bool isExport = false, string source = "")
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
-
- int deptid = 0;
- if (User.F_RoleCode != "GLY")
- {
- if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
- {
- deptid = User.F_DeptId;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@deptid", deptid.ToString());
- paras.Add("@source", source);
- var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport", paras, "Dept3EfficiencyReport");
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- public ActionResult GetDept3EfficiencyReport1(string sdate, string edate, bool isExport = false)
- {
- if (string.IsNullOrEmpty(sdate))
- {
- sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
- }
- if (string.IsNullOrEmpty(edate))
- {
- edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
- }
- int deptid = 0;
- if (User.F_RoleCode != "GLY")
- {
- if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
- {
- deptid = User.F_DeptId;
- }
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", sdate);
- paras.Add("@edate", edate);
- paras.Add("@deptid", deptid.ToString());
- var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport_1", paras, "Dept3EfficiencyReport");
- var newSet = new DataSet();
- if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
- {
- obj.Tables[0].Columns.Add("rate", typeof(decimal));
- for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
- {
- int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
- int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
- int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
- int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
- timeoutcount = completedcount - timelycount;
- obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
- if (completedcount > 0)
- {
- timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
- obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
- obj.Tables[0].Rows[i]["rate"] = timelyrate;
- }
- }
- obj.Tables[0].DefaultView.Sort = "rate desc";
- newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
- newSet.Tables[0].Columns.Remove("rate");
- }
- if (isExport)
- {
- string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", newSet);
- }
- #endregion
- /// <summary>
- /// 单位月统计
- /// </summary>
- /// <param name="date"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDeptMonthReport(string date, bool isExport = false)
- {
- if (string.IsNullOrEmpty(date))
- {
- date = DateTime.Now.Date.ToString("yyyy-MM");
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@date", date);
- var obj = DbHelperSQL.RunProcedure("P_DeptMonthReport", paras, "DeptMonthReport");
- if (isExport)
- {
- var cols = new string[] { "名称", "当月受理数量", "当月处理量", "往月处理量", "当月延期量", "往月延期量", "当月重办量", "往月重办量", "当月结案量", "当月办理率", "当月办结率" };
- new NPOIHelper().ExportToExcel($"工单报表{date}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
-
- /// <summary>
- /// 单位办理率统计
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="pageIndex"></param>
- /// <param name="pageSize"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetDeptEndRate(string month, int type = 0, bool isExport = false)
- {
- string start = "", end = "";
- if (month != null)
- {
- var date = DateTime.Parse(month + "-01");
- start = date.ToString("yyyy-MM-dd"); ;
- end = date.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start);
- paras.Add("@edate", end);
- paras.Add("@type", type.ToString());
- var obj = DbHelperSQL.RunProcedure("P_DeptEndRateReport", paras, "DeptEndRateReport");
- if (isExport)
- {
- var cols = new string[] { "单位", "受理数量", "办理数量", "结案数量", "结案率" };
- new NPOIHelper().ExportToExcel($"单位办理率统计{month}", obj.Tables[0], cols, 1);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- /// <summary>
- /// 工单热点分布
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="deptid"></param>
- /// <param name="keyid"></param>
- /// <param name="sourcearea"></param>
- /// <param name="source"></param>
- /// <returns></returns>
- public ActionResult GetHotspotMap(DateTime? start, DateTime? end, int deptid = 0, int keyid = 0,int sourcearea = 0, int source=0)
- {
- if (start == null)
- {
- start = DateTime.Now;
- //start = new DateTime(start.Value.Year, start.Value.Month, 1);
- start = DateTime.Now.AddDays(-3);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- var where = " and ISNUMERIC(F_Longitude)>0 and ISNUMERIC(F_Latitude)>0 ";
- if (deptid > 0)
- 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 )";
- if (keyid > 0)
- where += " and ','+F_Key+',' like '%," + keyid + ",%' ";
- if (sourcearea > 0)
- where += " and F_SourceArea='" + sourcearea+"' ";
- if (source > 0)
- where += " and F_InfoSource = '" + source + "' ";
- 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;
- var orderlist = workOrderBLL.GetList(" 1=1 "+sql).Tables[0];
- var list = new List<string[]>();
- foreach (DataRow dr in orderlist.Rows)
- {
- var longitude = dr["F_Longitude"].ToString();
- var latitude = dr["F_Latitude"].ToString();
- var title= dr["F_ComTitle"].ToString();
- var content= dr["F_ComContent"].ToString();
- var strobj = new string[] { longitude, latitude, title, content };
- list.Add(strobj);
- }
- return Success("",list);
- }
- #region 20191121 zhengbingbing 新增质检工作统计:工单回访量、回访量、结案量、工单重办量、重办量
- public ActionResult GetQualityAssessment(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- 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") + "'";
- 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") + "'";
- string sql = "select '('+F_UserCode+')'+F_UserName 姓名" +
- ",(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) 工单回访量" +
- ",(select COUNT(1) from T_Bus_VisitResult where F_IsDelete = 0 " + wherevi + " and F_CreateUser = F_UserCode and F_IsDelete = 0) 回访量" +
- ",(select COUNT(1) from T_Bus_WorkOrder where F_WorkState = 9 and F_IsDelete = 0 and F_CloseUser = F_UserCode "+ whereja + ") 结案量" +
- ",(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) 工单重办量" +
- ",(select COUNT(1) from T_Bus_Operation where F_IsDelete = 0 and F_State = 11 and F_CreateUser = F_UserCode " + wherevi + ") 重办量" +
- " from T_Sys_UserAccount where F_RoleId = 18 and F_DeleteFlag = 0 order by F_UserCode";
- var obj = DbHelperSQL.Query(sql);
- if (isExport)
- {
- string[] cols = new string[] { "姓名", "工单回访量", "回访量", "结案量", "工单重办量", "重办量" };
- NPOIHelper npoi = new NPOIHelper();
- if (npoi.ExportToExcel("质检工作统计报表", obj.Tables[0], cols) == "")
- {
- return Success("导出成功");
- }
- else
- {
- return Error("导出失败");
- }
- }
- return Success("成功", obj);
- }
- #endregion
- /// <summary>
- ///
- /// </summary>
- /// <param name="start"></param>
- /// <param name="end"></param>
- /// <param name="isExport"></param>
- /// <returns></returns>
- public ActionResult GetAudithandling(DateTime? start, DateTime? end, bool isExport = false)
- {
- if (start == null)
- {
- start = DateTime.Now;
- start = new DateTime(start.Value.Year, start.Value.Month, 1);
- }
- if (end == null)
- {
- end = DateTime.Now;
- }
- Dictionary<string, string> paras = new Dictionary<string, string>();
- paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
- paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
- var obj = DbHelperSQL.RunProcedure("P_Audithandling", paras, "Audithandling");
- if (isExport)
- {
- var cols = new string[] { "单位名称", "办理件数量", "退回件数量", "延时件数量","超期未果数量" };
- new NPOIHelper().ExportToExcel($"审核办理工单统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
- return Success("导出excel");
- }
- return Success("成功", obj);
- }
- }
- }
|