/// <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));
}
