/// <summary> /// 怠工 /// </summary> /// <param name="sw">参见模型</param> /// <returns>DT</returns> public static DataTable getDTSabotageByOrgNo(SabotageCount_SW sw) { string LengthError = (ConfigCls.getPatrolLengthError() / 1000).ToString();//巡检距离误差 StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" group by SBDATE,substring(BYORGNO,1,6)+'000000000',case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C,BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by SBDATE,BYORGNO,case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } else { sb.AppendFormat(" select count(b.USERID) as C,b.USERID as BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by SBDATE,b.USERID,case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 查询怠工统计 /// </summary> /// <param name="sw">参见SabotageCount_SW</param> /// <returns>参见getSabotageCount_Model</returns> public static IEnumerable <getSabotageCount_Model> getSabotageCountModel(SabotageCount_SW sw) { var result = new List <getSabotageCount_Model>(); if (string.IsNullOrEmpty(sw.DateBegin))//开始时间为空 { return(result); } if (string.IsNullOrEmpty(sw.DateEnd))//结束时间为空 { return(result); } DateTime dt1 = Convert.ToDateTime(sw.DateBegin); DateTime dt2 = Convert.ToDateTime(sw.DateEnd); int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数 DataTable dtHU = new DataTable(); DataTable dt = BaseDT.T_IPS_REALDATATEMPORARY.getDTSabotageByOrgNo(sw); if (PublicCls.OrgIsShi(sw.TopORGNO)) { dtHU = BaseDT.T_IPSFR_USER.getDTShi(new T_IPSFR_USER_SW { }); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { dtHU = BaseDT.T_IPSFR_USER.getDTXain(new T_IPSFR_USER_SW { BYORGNO = sw.TopORGNO }); } else { dtHU = BaseDT.T_IPSFR_USER.getDT(new T_IPSFR_USER_SW { ISENABLE = "1", BYORGNO = sw.TopORGNO }); } if (PublicCls.OrgIsZhen(sw.TopORGNO)) { DataRow[] drOrg = dtHU.Select("", ""); for (int i = 0; i < drOrg.Length; i++) { getSabotageCount_Model m = new getSabotageCount_Model(); m.ORGName = drOrg[i]["HNAME"].ToString(); m.ORGNo = drOrg[i]["HID"].ToString(); string CHr = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "'").ToString(); CHr = (string.IsNullOrEmpty(CHr) ? "0" : CHr); string cList = ""; for (DateTime tm = dt1; tm <= dt2; tm = tm.AddDays(1)) { string tm1 = PublicClassLibrary.ClsSwitch.SwitDate(tm).ToString(); if (string.IsNullOrEmpty(cList) == false) { cList += ","; } string tmp = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "' and SBDATE='" + tm1 + "' and PatrolLenErro=1").ToString(); //计算该日期下人数 cList += (string.IsNullOrEmpty(tmp) ? "0" : tmp); //各日期以逗号分隔 } m.DayCountList = cList; //日期考勤列表 string Chr0 = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "' and PatrolLenErro=0").ToString(); Chr0 = (string.IsNullOrEmpty(Chr0) ? "0" : Chr0); string Chr1 = ClsStr.getDiff(CHr, Chr0).ToString("F0"); string Chr2 = ClsStr.getPercent(Chr0, CHr).ToString("F0"); m.Count = CHr; //总 m.Count0 = Chr0; //完成 m.Count1 = Chr1; //未完成 m.CountPer = Chr2 + "%"; //完成率 result.Add(m); } dtHU.Clear(); dtHU.Dispose(); } else { T_SYS_ORGSW swOrg = new T_SYS_ORGSW(); swOrg.SYSFLAG = ConfigCls.getSystemFlag(); swOrg.TopORGNO = sw.TopORGNO; if (PublicCls.OrgIsShi(sw.TopORGNO)) { swOrg.GetContyORGNOByCity = sw.TopORGNO;//获取所有县 } if (PublicCls.OrgIsXian(sw.TopORGNO)) { swOrg.GetXZOrgNOByConty = sw.TopORGNO;//获取所有镇 } DataTable dtOrg = BaseDT.T_SYS_ORG.getDT(swOrg); DataRow[] drOrg = dtOrg.Select("", "ORGNO");//取所有 for (int i = 0; i < drOrg.Length; i++) { // string orgName = drOrg[i]["ORGNAME"].ToString(); //string orgNo = drOrg[i]["ORGNO"].ToString(); getSabotageCount_Model m = new getSabotageCount_Model(); m.ORGName = drOrg[i]["ORGNAME"].ToString(); m.ORGNo = drOrg[i]["ORGNO"].ToString(); string CHr = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "'").ToString(); CHr = (string.IsNullOrEmpty(CHr) ? "0" : CHr); string cList = ""; for (DateTime tm = dt1; tm <= dt2; tm = tm.AddDays(1)) { string tm1 = PublicClassLibrary.ClsSwitch.SwitDate(tm).ToString(); if (string.IsNullOrEmpty(cList) == false) { cList += ","; } string tmp = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "' and SBDATE='" + tm1 + "' and PatrolLenErro=0").ToString(); //计算该日期下人数 cList += (string.IsNullOrEmpty(tmp) ? "0" : tmp); //各日期以逗号分隔 } m.DayCountList = cList; //日期考勤列表 string Chr0 = dt.Compute("sum(C)", "BYORGNO='" + m.ORGNo + "' and PatrolLenErro=1").ToString(); Chr0 = (string.IsNullOrEmpty(Chr0) ? "0" : Chr0); string Chr1 = ClsStr.getDiff(CHr, Chr0).ToString("F0"); string Chr2 = ClsStr.getPercent(Chr0, CHr).ToString("F0"); m.Count = CHr; //总 m.Count0 = Chr0; //完成 m.Count1 = Chr1; //未完成 m.CountPer = Chr2 + "%"; //完成率 result.Add(m); } dtOrg.Clear(); dtOrg.Dispose(); } dt.Clear(); dt.Dispose(); if (1 == 1) { getSabotageCount_Model m = new getSabotageCount_Model(); m.ORGName = "合计"; string LineCount = result.Sum(item => Convert.ToDecimal(item.Count)).ToString(); string Count0 = result.Sum(item => Convert.ToDecimal(item.Count0)).ToString(); string Count1 = result.Sum(item => Convert.ToDecimal(item.Count1)).ToString(); m.Count = LineCount; m.Count0 = Count0; m.Count1 = Count1; m.CountPer = ClsStr.getPercent(m.Count0, m.Count).ToString("F0") + "%"; int[] arrI = new int[days]; foreach (var v in result) { string[] a = v.DayCountList.Split(',');//组合列表 for (int i = 0; i < days; i++) { if (string.IsNullOrEmpty(arrI[i].ToString())) { arrI[i] = 0; } arrI[i] += int.Parse(a[i]); } } string cList = ""; for (int i = 0; i < days; i++) { if (string.IsNullOrEmpty(cList) == false) { cList += ","; } cList += arrI[i]; } m.DayCountList = cList; result.Insert(0, m); } return(result); }
/// <summary> /// 查询怠工统计 /// </summary> /// <param name="sw">参见SabotageCount_SW</param> /// <returns>参见SabotageDetail_Model</returns> public static IEnumerable <SabotageDetail_Model> getSabotageDetailModel(SabotageCount_SW sw) { var result = new List <SabotageDetail_Model>(); if (string.IsNullOrEmpty(sw.DateBegin))//开始时间为空 { return(result); } if (string.IsNullOrEmpty(sw.DateEnd))//结束时间为空 { return(result); } if (string.IsNullOrEmpty(sw.ORGNO))//组织机构编码 { return(result); } //根据机构编码获取下属组织机构 注意:这个函数含本级及所有下级的 DataTable dtOrg = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { TopORGNO = sw.ORGNO }); DataTable dtHU = BaseDT.T_IPSFR_USER.getDT(new T_IPSFR_USER_SW { ISENABLE = "1", BYORGNO = sw.ORGNO, PhoneHname = sw.PhoneHname }); //获取护林员每日巡检信息 DataTable dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getHRAndRealDataDT(new T_IPS_REALDATATEMPORARYSW { DateBegin = sw.DateBegin, DateEnd = sw.DateEnd, ORGNO = sw.ORGNO, PhoneHname = sw.PhoneHname }); float LengthError = ConfigCls.getPatrolLengthError() / 1000;//巡检距离误差 //仅查怠工DataRow[] dr = dtHRRealData.Select("PatrolLenError<=" + LengthError.ToString(), "SBDATE DESC"); DataRow[] dr = dtHRRealData.Select("", "HID,SBDATE DESC"); for (int i = 0; i < dr.Length; i++) { SabotageDetail_Model m = new SabotageDetail_Model(); string hid = dr[i]["HID"].ToString(); m.HID = hid; string orgNo = dr[i]["BYORGNO"].ToString(); m.ORGNo = orgNo; m.ORGName = BaseDT.T_SYS_ORG.getName(dtOrg, orgNo); m.HNAME = dr[i]["HNAME"].ToString(); //护林员姓名 m.PHONE = dr[i]["PHONE"].ToString(); //电话 m.Date = PublicClassLibrary.ClsSwitch.SwitDate(dr[i]["SBDATE"].ToString()); //日期 m.Count0 = Convert.ToDouble(dr[i]["PATROLLENGTH"].ToString()).ToString("F2"); //应巡长度 m.Count1 = Convert.ToDouble(dr[i]["RealPATROLLENGTH"].ToString()).ToString("F2"); //实巡长度 m.CountPer = ClsStr.getPercent(dr[i]["RealPATROLLENGTH"].ToString(), dr[i]["PATROLLENGTH"].ToString()); //string wcl1 = wcl.ToString("F0"); //if (wcl <= 100) // wcl1 = "<font color=red>" + wcl1 + "</font>"; //m.CountPer = wcl1; result.Add(m); } dtHRRealData.Clear(); dtHRRealData.Dispose(); dtHU.Clear(); dtHU.Dispose(); dtOrg.Clear(); dtOrg.Dispose(); return(result); }
/// <summary> /// 怠工统计Json /// </summary> /// <returns></returns> public ActionResult getSabotageCountJson() { SabotageCount_SW sw = new SabotageCount_SW(); //sw.ORGNO = Request.Params["BYORGNO"]; sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; DateTime dt1 = Convert.ToDateTime(sw.DateBegin); DateTime dt2 = Convert.ToDateTime(sw.DateEnd); int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数 StringBuilder sb = new StringBuilder(); sb.AppendFormat("<table id=\"tb\" cellpadding=\"0\" cellspacing=\"0\">"); sb.AppendFormat("<thead>"); sb.AppendFormat(" <tr>"); sb.AppendFormat(" <th rowspan=\"2\">单位<br>(姓名)</th>"); sb.AppendFormat(" <th rowspan=\"2\">总数</th>"); sb.AppendFormat(" <th colspan=\"{1}\">{0}</th>", "日期(日)", days.ToString()); sb.AppendFormat(" <th rowspan=\"2\">正常</th>"); sb.AppendFormat(" <th rowspan=\"2\">怠工</th>"); sb.AppendFormat(" <th rowspan=\"2\">完成率(%)</th>"); sb.AppendFormat(" </tr>"); sb.AppendFormat(" <tr>"); for (DateTime tm = dt1; tm <= dt2; tm = tm.AddDays(1)) { sb.AppendFormat(" <th>{0}</th>", tm.ToString("dd")); } sb.AppendFormat(" </tr>"); sb.AppendFormat("</thead>"); sb.AppendFormat("<tbody>"); var list = HUCheckCls.getSabotageCountModel(sw); int j = 0; foreach (var v in list) { string orgName = v.ORGName; string orgNo = v.ORGNo; //bool isHU = false; //if (PublicCls.OrgIsZhen(sw.TopORGNO) == true && orgName.Contains("合计") == false) //{ // isHU = true; //} j++; if (j % 2 == 0) { sb.AppendFormat("<tr>"); } else { sb.AppendFormat("<tr class='row1'>"); } sb.AppendFormat("<td class=\"center\">{0}</td>", v.ORGName + ""); // sb.AppendFormat("<td class=\"center\">{0}</td>", v.Count + ""); //总 string[] arr1 = v.DayCountList.Split(','); for (int i = 0; i < days; i++) { //if (isHU == true) //{ //arr1[i] = (arr1[i] == "0") == true ? "正常" : ""; //} sb.AppendFormat("<td class=\"center\">{0}</td>", arr1[i]); } sb.AppendFormat("<td class=\"center\">{0}</td>", v.Count0 + ""); //完成 sb.AppendFormat("<td class=\"center\">{0}</td>", v.Count1 + ""); //未完成 sb.AppendFormat("<td class=\"center\">{0}</td>", v.CountPer + ""); //完成率 sb.AppendFormat("</tr>"); } return(Content(JsonConvert.SerializeObject(new Message(true, sb.ToString(), "")), "text/html;charset=UTF-8")); }
public FileResult SabotageCountExportExcel() { SabotageCount_SW sw = new SabotageCount_SW(); sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUCheckCls.getSabotageCountModel(sw); DateTime dt1 = Convert.ToDateTime(sw.DateBegin); DateTime dt2 = Convert.ToDateTime(sw.DateEnd); int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数 //vMenu.MENUNAME 页面/菜单名称 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); for (int i = 0; i < days; i++) { sheet1.SetColumnWidth(i + 2, 10 * 256); } sheet1.SetColumnWidth(days + 2, 20 * 256); sheet1.SetColumnWidth(days + 3, 20 * 256); sheet1.SetColumnWidth(days + 4, 20 * 256); IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("怠工统计表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位/姓名"); row.CreateCell(1).SetCellValue("总数"); row.CreateCell(days + 2).SetCellValue("正常"); row.CreateCell(days + 3).SetCellValue("怠工"); row.CreateCell(days + 4).SetCellValue("完成率"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(days + 2).CellStyle = getCellStyleHead(book); row.GetCell(days + 3).CellStyle = getCellStyleHead(book); row.GetCell(days + 4).CellStyle = getCellStyleHead(book); row = sheet1.CreateRow(2); for (int i = 0; i < days; i++) { DateTime tm = dt1.AddDays(i); row.CreateCell(i + 2).SetCellValue(tm.ToString("dd")); row.GetCell(i + 2).CellStyle = getCellStyleHead(book); } sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, days + 4)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, days + 1)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, days + 2 + 2, days + 2 + 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, days + 0 + 2, days + 0 + 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, days + 1 + 2, days + 1 + 2)); int rowI = 0; foreach (var v in list) { row = sheet1.CreateRow(rowI + 3); row.CreateCell(0).SetCellValue(v.ORGName); row.CreateCell(1).SetCellValue(v.Count); string[] arr = v.DayCountList.Split(','); for (int i = 0; i < days; i++) { row.CreateCell(i + 2).SetCellValue(arr[i]); } row.CreateCell(days + 2).SetCellValue(v.Count0); row.CreateCell(days + 3).SetCellValue(v.Count1); row.CreateCell(days + 4).SetCellValue(v.CountPer); rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "怠工统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }