/// <summary>
/// 根据单位返回市县乡优化后的统计
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>DT</returns>
public static DataTable getDTByOrgNo(PatrolRouteStat_SW sw)
{
StringBuilder sb = new StringBuilder();
if (PublicCls.OrgIsShi(sw.TopORGNO))
{
sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3,substring(BYORGNO,1,6)+'000000000' as BYORGNO");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000'");
}
else if (PublicCls.OrgIsXian(sw.TopORGNO))
{
sb.AppendFormat(" select count(BYORGNO) as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3, BYORGNO");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by BYORGNO");
}
else
{
sb.AppendFormat("select count(a.USERID) as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3,a.USERID as BYORGNO");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by a.USERID");
}
return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]);
}
/// <summary>
/// 根据单位返回市县乡优化后的统计
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getDTByOrgNo(PatrolRouteStat_SW sw)
{
StringBuilder sb = new StringBuilder();
if (PublicCls.OrgIsShi(sw.TopORGNO))
{
sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,a.ROUTESTATE,substring(BYORGNO,1,6)+'000000000' as BYORGNO");
sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID");
sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59");
sb.AppendFormat(" group by ROUTESTATE,substring(BYORGNO,1,6)+'000000000'");
}
else if (PublicCls.OrgIsXian(sw.TopORGNO))
{
sb.AppendFormat(" select count(BYORGNO) as C,a.ROUTESTATE, BYORGNO");
sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID");
sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59");
sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by ROUTESTATE,BYORGNO");
}
else
{
sb.AppendFormat("select count(a.hid) as C,a.ROUTESTATE,a.hid as BYORGNO");
sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID");
sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59");
sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by ROUTESTATE,a.hid");
}
return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]);
}
public FileResult OmitCheckCountExportExcel()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 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(2).SetCellValue("已巡");
row.CreateCell(3).SetCellValue("未巡");
row.CreateCell(4).SetCellValue("完成率");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 2);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.PointCount);
row.CreateCell(2).SetCellValue(v.PointCount0);
row.CreateCell(3).SetCellValue(v.PointCount1);
row.CreateCell(4).SetCellValue(v.PointCount2);
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));
}
/// <summary>
/// 出勤查询Json
/// </summary>
/// <returns></returns>
public ActionResult getOmitCheckCountJson()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<table id=\"tb\" cellpadding=\"0\" cellspacing=\"0\">");
sb.AppendFormat("<thead>");
sb.AppendFormat(" <tr>");
sb.AppendFormat(" <th>单位</th>");
sb.AppendFormat(" <th>应巡总数</th>");
sb.AppendFormat(" <th>已完成</th>");
sb.AppendFormat(" <th>未完成</th>");
sb.AppendFormat(" <th>完成率(%)</th>");
sb.AppendFormat(" </tr>");
sb.AppendFormat("</thead>");
sb.AppendFormat("<tbody>");
int i = 0;
foreach (var v in list)
{
i++;
string orgName = v.ORGName;
string orgNo = v.ORGNo;
if (i % 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.PointCount + ""); //总
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount0 + ""); //完成
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount1 + ""); //未完成
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount2 + ""); //完成率
sb.AppendFormat("</tr>");
}
return(Content(JsonConvert.SerializeObject(new Message(true, sb.ToString(), "")), "text/html;charset=UTF-8"));
}
/// <summary>
/// 根据单位返回市县乡及各日期后的统计
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>dt</returns>
public static DataTable getDTByOrgNoToDate(PatrolRouteStat_SW sw)
{
StringBuilder sb = new StringBuilder();
if (PublicCls.OrgIsShi(sw.TopORGNO))
{
/*
* select count(substring(BYORGNO,1,6)+'000') as C,substring(BYORGNO,1,6)+'000' as BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE
* FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID
* WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6')
* --and substring(BYORGNO,1,6)+'000'='532501000'
* group by substring(BYORGNO,1,6)+'000',CONVERT(varchar(100),SBDATE, 111)
*/
sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO,SBDATE");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000',SBDATE");
}
else if (PublicCls.OrgIsXian(sw.TopORGNO))
{
// select count(BYORGNO) as C, BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE
//FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID
//WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6')
//and substring(BYORGNO,1,6)+'000'='532501000'--", sw.TopORGNO);
//group by BYORGNO,CONVERT(varchar(100),SBDATE, 111)
sb.AppendFormat(" select count(BYORGNO) as C, BYORGNO,SBDATE");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by BYORGNO,SBDATE");
}
else
{
//select count(a.USERID) as C,a.USERID as BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE
// FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID
//WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6')
//and BYORGNO='532501001'--", sw.TopORGNO);
//group by a.USERID,CONVERT(varchar(100),SBDATE, 111)
sb.AppendFormat(" select count(a.USERID) as C,a.USERID as BYORGNO,SBDATE");
sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID");
sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd);
sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO);
sb.AppendFormat(" group by a.USERID,SBDATE");
}
return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]);
}
public FileResult PatrolRouteStatExportExcel()
{
//string BYORGNO = Request.Params["BYORGNO"];
//string TIMEBegin = Request.Params["TIMEBegin"];
//string TIMEEnd = Request.Params["TIMEEnd"];
//string HNamePhone = Request.Params["HNamePhone"];
//var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004002", SYSFLAG = ConfigCls.getSystemFlag() });
//vMenu.MENUNAME 页面/菜单名称
#region 导出统计表
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);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 20 * 256);
sheet1.SetColumnWidth(5, 10 * 256);
sheet1.SetColumnWidth(6, 10 * 256);
sheet1.SetColumnWidth(7, 10 * 256);
sheet1.SetColumnWidth(8, 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(5).SetCellValue("巡检地点(个)");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
row.CreateCell(1).SetCellValue("总数");
row.CreateCell(2).SetCellValue("巡检");
row.CreateCell(3).SetCellValue("未巡检");
row.CreateCell(4).SetCellValue("巡检率(%)");
row.CreateCell(5).SetCellValue("总数");
row.CreateCell(6).SetCellValue("完成");
row.CreateCell(7).SetCellValue("未完成");
row.CreateCell(8).SetCellValue("完成率(%)");
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row.GetCell(6).CellStyle = getCellStyleHead(book);
row.GetCell(7).CellStyle = getCellStyleHead(book);
row.GetCell(8).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 4));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 5, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
//var list = HUReportCls.getPatrolRouteStatModel(new PatrolRouteStat_SW { DateBegin = TIMEBegin, DateEnd = TIMEEnd, orgNo = BYORGNO, TopORGNO = BYORGNO });
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
int rowI = 0;
foreach (var item in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(item.ORGName);
row.CreateCell(1).SetCellValue(item.LineCount);
row.CreateCell(2).SetCellValue(item.LineCount0);
row.CreateCell(3).SetCellValue(item.LineCount1);
row.CreateCell(4).SetCellValue(item.LineCount2);
row.CreateCell(5).SetCellValue(item.PointCount);
row.CreateCell(6).SetCellValue(item.PointCount0);
row.CreateCell(7).SetCellValue(item.PointCount1);
row.CreateCell(8).SetCellValue(item.PointCount2);
for (int i = 0; i < 9; i++)
{
if (i == 0)
{
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.GetCell(i).CellStyle = getCellStyleCenter(book);
}
}
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));
#endregion
}
/// <summary>
/// 巡检路线明细表
/// </summary>
/// <param name="sw">参见PatrolRouteStat_SW</param>
/// <returns>参见HUReport_PatrolRouteDetail_Model</returns>
public static IEnumerable <HUReport_PatrolRouteDetail_Model> getPatrolRouteDetailModel(PatrolRouteStat_SW sw)
{
var result = new List <HUReport_PatrolRouteDetail_Model>();
if (string.IsNullOrEmpty(sw.DateBegin))//开始时间为空
{
return(result);
}
if (string.IsNullOrEmpty(sw.DateEnd))//结束时间为空
{
return(result);
}
if (string.IsNullOrEmpty(sw.orgNo))//组织机构编码
{
return(result);
}
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);
//DataTable dtOrg = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { SYSFLAG = ConfigCls.getSystemFlag() });
DataTable dtHU = BaseDT.T_IPSFR_USER.getDT(new T_IPSFR_USER_SW {
ISENABLE = "1", PhoneHname = sw.PhoneHname, BYORGNO = sw.orgNo
});
//获取护林员每日巡检信息
DataTable dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getHRAndRealDataDT(new T_IPS_REALDATATEMPORARYSW {
DateBegin = sw.DateBegin, DateEnd = sw.DateEnd, ORGNO = sw.orgNo, PhoneHname = sw.PhoneHname
});
//获取具体巡检情况
DataTable dtPatrol = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getDT(new T_IPSFR_ROUTERAIL_PATROL_SW {
DateBegin = sw.DateBegin, DateEnd = sw.DateEnd
});
int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1; //日期包含天数
for (int i = 0; i < dtHU.Rows.Count; i++) //获取每个护林员
{
string orgNo = dtHU.Rows[i]["BYORGNO"].ToString();
string HID = dtHU.Rows[i]["HID"].ToString();
string orgName = BaseDT.T_SYS_ORG.getName(dtOrg, orgNo);
HUReport_PatrolRouteDetail_Model m = new HUReport_PatrolRouteDetail_Model();
m.ORGName = orgName;
m.ORGNo = orgNo;
m.HNAME = dtHU.Rows[i]["HNAME"].ToString();
m.PHONE = dtHU.Rows[i]["PHONE"].ToString();
string CHr = "1";//每天一条路线
if (CHr != "0")
{
CHr = (int.Parse(CHr) * days).ToString();
string Chr0 = BaseDT.T_IPS_REALDATATEMPORARY.getCountByHID(dtHRRealData, HID).ToString();
string Chr1 = ClsStr.getDiff(CHr, Chr0).ToString("F0");
string Chr2 = ClsStr.getPercent(Chr0, CHr).ToString("F0");
m.LineCount = CHr; //总
m.LineCount0 = Chr0; //完成
m.LineCount1 = Chr1; //未完成
m.LineCount2 = Chr2 + "%"; //完成率
}
//巡检与漏检
string CCHr = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getCountByHID(dtPatrol, HID, "").ToString();
if (CCHr != "0")
{
// CCHr = (int.Parse(CCHr) * days).ToString();
string Chr0 = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getCountByHID(dtPatrol, HID, "1").ToString();
string Chr1 = ClsStr.getDiff(CCHr, Chr0).ToString("F0");
string Chr2 = ClsStr.getPercent(Chr0, CCHr).ToString("F0");
m.PointCount = CCHr; //总
m.PointCount0 = Chr0; //完成
m.PointCount1 = Chr1; //未完成
m.PointCount2 = Chr2 + "%"; //完成率
}
result.Add(m);
}
dtHU.Clear();
dtHU.Dispose();
dtHRRealData.Clear();
dtHRRealData.Dispose();
dtPatrol.Clear();
dtPatrol.Dispose();
dtOrg.Clear();
dtOrg.Dispose();
return(result);
}
/// <summary>
/// 巡检路线统计总表
/// </summary>
/// <param name="sw">参见PatrolRouteStat_SW</param>
/// <returns>参见HUReport_PatrolRouteStat_Model</returns>
public static IEnumerable <HUReport_PatrolRouteStat_Model> getPatrolRouteStatModel(PatrolRouteStat_SW sw)
{
var result = new List <HUReport_PatrolRouteStat_Model>();
if (string.IsNullOrEmpty(sw.DateBegin))//开始时间为空
{
return(result);
}
if (string.IsNullOrEmpty(sw.DateEnd))//结束时间为空
{
return(result);
}
//DataTable dtOrg = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { SYSFLAG = ConfigCls.getSystemFlag(), TopORGNO=sw.orgNo });
//获取护林员每日巡检信息
//DataTable dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getHRAndRealDataDT(new T_IPS_REALDATATEMPORARYSW { DateBegin = sw.DateBegin, DateEnd = sw.DateEnd });
//获取具体巡检情况
//DataTable dtPatrol = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getDT(new T_IPSFR_ROUTERAIL_PATROL_SW { DateBegin = sw.DateBegin, DateEnd = sw.DateEnd });
// DataTable dtHRRealData = new DataTable();
//DataTable dtPatrol = new DataTable();
DataTable dtHU = new DataTable();
DataTable dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getDTByOrgNo(sw);
//DataTable dtPatrol = BaseDT.T_IPS_REALDATATEMPORARY.getPATROLCOUNTDTByOrgNo(sw);//获取巡检信息 含未检和已检
if (PublicCls.OrgIsShi(sw.TopORGNO))
{
//dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getDTShi(sw);
//dtPatrol = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getDTShi(sw);//获取巡检信息 含未检和已检
dtHU = BaseDT.T_IPSFR_USER.getDTShi(new T_IPSFR_USER_SW {
});
}
else if (PublicCls.OrgIsXian(sw.TopORGNO))
{
//dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getDTXain(sw);
//dtPatrol = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getDTXain(sw);//获取巡检信息 含未检和已检
dtHU = BaseDT.T_IPSFR_USER.getDTXain(new T_IPSFR_USER_SW {
BYORGNO = sw.TopORGNO
});
}
else
{
//dtHRRealData = BaseDT.T_IPS_REALDATATEMPORARY.getDTZhen(sw);
//dtPatrol = BaseDT.T_IPSFR_ROUTERAIL_PATROL.getDTZhen(sw);//获取巡检信息 含未检和已检
dtHU = BaseDT.T_IPSFR_USER.getDT(new T_IPSFR_USER_SW {
ISENABLE = "1", BYORGNO = sw.TopORGNO
});
}
int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数
if (PublicCls.OrgIsZhen(sw.TopORGNO))
{
DataRow[] drOrg = dtHU.Select("", "");
for (int i = 0; i < drOrg.Length; i++)
{
HUReport_PatrolRouteStat_Model m = new HUReport_PatrolRouteStat_Model();
m.ORGName = drOrg[i]["HNAME"].ToString();
m.ORGNo = drOrg[i]["HID"].ToString();
string CHr = "";// BaseDT.T_IPSFR_USER.getCountByOrgNo(dtHU, m.ORGNo).ToString();
if (CHr != "0")
{
CHr = days.ToString();
string Chr0 = BaseDT.T_IPS_REALDATATEMPORARY.getDTByOrgNoSum(dtHRRealData, m.ORGNo).ToString();
string Chr1 = ClsStr.getDiff(CHr, Chr0).ToString("F0");
string Chr2 = ClsStr.getPercent(Chr0, CHr).ToString("F0");
m.LineCount = CHr; //总
m.LineCount0 = Chr0; //完成
m.LineCount1 = Chr1; //未完成
m.LineCount2 = Chr2 + "%"; //完成率
}
//巡检与漏检
string CCHr = dtHRRealData.Compute("sum(C1)", "BYORGNO='" + m.ORGNo + "'").ToString();
CCHr = (string.IsNullOrEmpty(CCHr) ? "0" : CCHr);
if (CCHr != "0")
{
string Chr0 = dtHRRealData.Compute("sum(C2)", "BYORGNO='" + m.ORGNo + "'").ToString(); // BaseDT.T_IPSFR_ROUTERAIL_PATROL.getSumByOrgNo(dtPatrol, m.ORGNo, "1");
Chr0 = (string.IsNullOrEmpty(Chr0) ? "0" : Chr0);
string Chr1 = dtHRRealData.Compute("sum(C3)", "BYORGNO='" + m.ORGNo + "'").ToString(); // BaseDT.T_IPSFR_ROUTERAIL_PATROL.getSumByOrgNo(dtPatrol, m.ORGNo, "0");
Chr1 = (string.IsNullOrEmpty(Chr1) ? "0" : Chr1);
string Chr2 = ClsStr.getPercent(Chr0, CCHr).ToString("F0");
m.PointCount = CCHr;
m.PointCount0 = Chr0;
m.PointCount1 = Chr1;
m.PointCount2 = 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();
HUReport_PatrolRouteStat_Model m = new HUReport_PatrolRouteStat_Model();
m.ORGName = drOrg[i]["ORGNAME"].ToString();
m.ORGNo = drOrg[i]["ORGNO"].ToString();
string CHr = BaseDT.T_IPSFR_USER.getSumByOrgNo(dtHU, m.ORGNo);//各单位护林员个数
if (CHr != "0")
{
CHr = (int.Parse(CHr) * days).ToString();
string Chr0 = BaseDT.T_IPS_REALDATATEMPORARY.getDTByOrgNoSum(dtHRRealData, m.ORGNo).ToString();
string Chr1 = ClsStr.getDiff(CHr, Chr0).ToString("F0");
string Chr2 = ClsStr.getPercent(Chr0, CHr).ToString("F0");
m.LineCount = CHr; //总
m.LineCount0 = Chr0; //完成
m.LineCount1 = Chr1; //未完成
m.LineCount2 = Chr2 + "%"; //完成率
}
//巡检与漏检
//巡检与漏检
string CCHr = dtHRRealData.Compute("sum(C1)", "BYORGNO='" + m.ORGNo + "'").ToString();
CCHr = (string.IsNullOrEmpty(CCHr) ? "0" : CCHr);
if (CCHr != "0")
{
string Chr0 = dtHRRealData.Compute("sum(C2)", "BYORGNO='" + m.ORGNo + "'").ToString(); // BaseDT.T_IPSFR_ROUTERAIL_PATROL.getSumByOrgNo(dtPatrol, m.ORGNo, "1");
Chr0 = (string.IsNullOrEmpty(Chr0) ? "0" : Chr0);
string Chr1 = dtHRRealData.Compute("sum(C3)", "BYORGNO='" + m.ORGNo + "'").ToString(); // BaseDT.T_IPSFR_ROUTERAIL_PATROL.getSumByOrgNo(dtPatrol, m.ORGNo, "0");
Chr1 = (string.IsNullOrEmpty(Chr1) ? "0" : Chr1);
string Chr2 = ClsStr.getPercent(Chr0, CCHr).ToString("F0");
m.PointCount = CCHr;
m.PointCount0 = Chr0;
m.PointCount1 = Chr1;
m.PointCount2 = Chr2 + "%";
}
result.Add(m);
}
dtOrg.Clear();
dtOrg.Dispose();
}
//dtPatrol.Clear();
//dtPatrol.Dispose();
dtHRRealData.Clear();
dtHRRealData.Dispose();
if (1 == 1)
{
HUReport_PatrolRouteStat_Model m = new HUReport_PatrolRouteStat_Model();
m.ORGName = "合计";
string LineCount = result.Sum(item => Convert.ToDecimal(item.LineCount)).ToString();
string LineCount0 = result.Sum(item => Convert.ToDecimal(item.LineCount0)).ToString();
string PointCount = result.Sum(item => Convert.ToDecimal(item.PointCount)).ToString();
string PointCount0 = result.Sum(item => Convert.ToDecimal(item.PointCount0)).ToString();
m.LineCount = LineCount;
m.LineCount0 = LineCount0;
m.LineCount1 = result.Sum(item => Convert.ToDecimal(item.LineCount1)).ToString();
m.LineCount2 = ClsStr.getPercent(LineCount0, LineCount).ToString("F0") + "%";
m.PointCount = PointCount;
m.PointCount0 = PointCount0;
m.PointCount1 = result.Sum(item => Convert.ToDecimal(item.PointCount1)).ToString();
m.PointCount2 = ClsStr.getPercent(PointCount0, PointCount).ToString("F0") + "%";
result.Insert(0, m);
}
return(result);
}
