You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
657 lines
28 KiB
657 lines
28 KiB
using FangYar.BLL;
|
|
using FangYar.Common;
|
|
using FangYar.Model;
|
|
using Spire.Doc;
|
|
using Spire.Doc.Documents;
|
|
using Spire.Doc.Fields;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Drawing;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Web;
|
|
|
|
namespace FangYar.WebUI.ashx
|
|
{
|
|
/// <summary>
|
|
/// OaToleranceHandler 的摘要说明
|
|
/// </summary>
|
|
public class OaToleranceHandler : IHttpHandler
|
|
{
|
|
private FangYar.BLL.OA.OA_TOLERANCE bll = new FangYar.BLL.OA.OA_TOLERANCE();
|
|
public void ProcessRequest(HttpContext context)
|
|
{
|
|
context.Response.ContentType = "text/plain";
|
|
string action = context.Request.Params["Action"];
|
|
string returnstr = "";
|
|
|
|
switch (action)
|
|
{
|
|
case "List":
|
|
returnstr = getLeaveList(context);
|
|
break;
|
|
case "getTolerance":
|
|
returnstr = getTolerance(context);
|
|
break;
|
|
case "toleranceSta2":
|
|
returnstr = toleranceSta2(context);
|
|
break;
|
|
case "ListByOrg":
|
|
returnstr = ListByOrg(context);
|
|
break;
|
|
case "toleranceTypeSta":
|
|
returnstr = toleranceTypeSta(context);
|
|
break;
|
|
case "toleranceTypeList":
|
|
returnstr = toleranceTypeList(context);
|
|
break;
|
|
case "htmlToWord":
|
|
returnstr = htmlToWord(context);
|
|
break;
|
|
case "AppGetToleranceSta":
|
|
returnstr = AppGetToleranceSta(context);
|
|
break;
|
|
case "AppGetToleranceStaList":
|
|
returnstr = AppGetToleranceStaList(context);
|
|
break;
|
|
}
|
|
context.Response.Write(returnstr);
|
|
}
|
|
|
|
//查询
|
|
private string getLeaveList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string userId = context.Request.Params["userId"];
|
|
string keyword = context.Request.Params["keywords"];
|
|
string page = context.Request.Params["page"];
|
|
string limit = context.Request.Params["limit"];
|
|
int pageIndex = 1;
|
|
int pageSize = 10;//
|
|
if (!string.IsNullOrEmpty(page)) { pageIndex = int.Parse(page); }
|
|
if (!string.IsNullOrEmpty(limit)) { pageSize = int.Parse(limit); }
|
|
string where = null;
|
|
where = "(PPL_ID = '" + userId + "' or PPL_ID = 'u_" + userId + "') ";
|
|
if (!string.IsNullOrEmpty(keyword))
|
|
{
|
|
where += " and L_OUT like '" + keyword + "' or L_REASON like '" + keyword + "' ";
|
|
}
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
int count = bll.GetRecordCount(where);
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
List<FangYar.Model.OA.OA_TOLERANCE> list = bll.QueryList(pageIndex, pageSize, where, " s_time desc");
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(list);
|
|
}
|
|
returnstr += "}";
|
|
}
|
|
catch
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
//查询
|
|
private string ListByOrg(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
|
|
|
|
string OrgId = context.Request.Params["orgId"];
|
|
string keyword = context.Request.Params["keywords"];
|
|
string type = context.Request.Params["type"];
|
|
string beginDate = context.Request.Params["startTime"];
|
|
string endDate = context.Request.Params["endTime"];
|
|
string page = context.Request.Params["page"];
|
|
string limit = context.Request.Params["limit"];
|
|
string bclass = context.Request.Params["class"];
|
|
string is_content = context.Request.Params["is_content"];
|
|
|
|
int pageIndex = 1;
|
|
int pageSize = 10;//
|
|
if (!string.IsNullOrEmpty(page)) { pageIndex = int.Parse(page); }
|
|
if (!string.IsNullOrEmpty(limit)) { pageSize = int.Parse(limit); }
|
|
string where = null;
|
|
if (!string.IsNullOrEmpty(is_content) && is_content == "1")
|
|
{
|
|
where = " PPL_ID in ( select USERS_UID from TBL_SYS_EMP where IS_ADMIN='0' and org_id in ( select o.org_id from fire_org o ,(select get_Org_child_list('" + OrgId + "') cids ) s where find_in_set(org_id,cids) ) ) ";
|
|
}
|
|
else
|
|
{
|
|
where = " PPL_ID in ( select USERS_UID from TBL_SYS_EMP where IS_ADMIN='0' and org_id ='" + OrgId + "' ) ";
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(keyword))
|
|
{
|
|
where += " and (PPL_NAME like '%" + keyword + "%' or TITLE like '%" + keyword + "%' or L_OUT like '%" + keyword + "' or L_REASON like '%" + keyword + "%') ";
|
|
}
|
|
if (!string.IsNullOrEmpty(beginDate))
|
|
{
|
|
where += " and date_format(" + beginDate + ",'%Y-%m-%d %H:%i:%s')>=date_format(S_TIME,'%Y-%m-%d %H:%i:%s') ";
|
|
where += "date_format(" + endDate + ",'%Y-%m-%d %H:%i:%s')<=date_format(E_TIME,'%Y-%m-%d %H:%i:%s') ";
|
|
}
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
int count = bll.GetRecordCount(where);
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
List<FangYar.Model.OA.OA_TOLERANCE> list = bll.QueryList(pageIndex, pageSize, where, " s_time desc");
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(list);
|
|
}
|
|
returnstr += "}";
|
|
}
|
|
catch
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
//获取公差信息、流转记录
|
|
private string getTolerance(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string toleranceId = context.Request.Params["toleranceId"];
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"toleranceData\":";
|
|
List<DataTable> data = bll.getTolerance(toleranceId);
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data[0]);
|
|
returnstr += ",\"waitData\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data[1]);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"toleranceData\":[],\"waitData\":[] }";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
//根据日期统计出差人员
|
|
private string toleranceSta2(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string ORG_ID = context.Request.Params["OrgId"];
|
|
string findDate = context.Request.Params["findDate"];
|
|
if (!string.IsNullOrEmpty(ORG_ID) && !string.IsNullOrEmpty(findDate))
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
List<FangYar.Model.OA.OA_TOLERANCE> list = bll.toleranceSta2(ORG_ID, findDate);
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(list);
|
|
returnstr += "}";
|
|
}
|
|
else
|
|
{
|
|
return "{\"code\":-1,\"msg\":\"参数有误\",\"data\":[]}";
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
|
|
|
|
#region 公差统计 WR 2021-09-11
|
|
|
|
/// <summary>
|
|
/// 公差统计
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string toleranceTypeSta(HttpContext context)
|
|
{
|
|
JsonRetMo<List<dynamic>> retMo = new JsonRetMo<List<dynamic>>() { code = "1", data = new List<dynamic>(), msg = "" };
|
|
try
|
|
{
|
|
string ORG_ID = context.Request.Params["Org_Id"];
|
|
string USERS_UID = context.Request.Params["USERS_UID"];
|
|
string S_Time = context.Request.Params["S_Time"];
|
|
string E_Time = context.Request.Params["E_Time"];
|
|
if (!string.IsNullOrEmpty(ORG_ID))
|
|
{
|
|
var list = QueryToleranceList(ORG_ID, USERS_UID, S_Time, E_Time);
|
|
var list1 = list.GroupBy(p => p.PPL_ID).ToList();
|
|
foreach (var item in list1)
|
|
{
|
|
retMo.data.Add(new { PPL_NAME = item.First().PPL_NAME, PPL_ID = item.Key, toleranCount = item.Count() });
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retMo.code = "0";
|
|
retMo.msg = "异常：" + ex;
|
|
}
|
|
string returnstr = FangYar.Common.JsonHelper.ToJSON1(retMo);
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 公差列表
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string toleranceTypeList(HttpContext context)
|
|
{
|
|
JsonRetMo<List<dynamic>> retMo = new JsonRetMo<List<dynamic>>() { code = "1", data = new List<dynamic>(), msg = "" };
|
|
try
|
|
{
|
|
string ORG_ID = context.Request.Params["Org_Id"];
|
|
string USERS_UID = context.Request.Params["USERS_UID"];
|
|
string S_Time = context.Request.Params["S_Time"];
|
|
string E_Time = context.Request.Params["E_Time"];
|
|
if (!string.IsNullOrEmpty(ORG_ID))
|
|
{
|
|
var list = QueryToleranceList(ORG_ID, USERS_UID, S_Time, E_Time);
|
|
|
|
retMo.data = list;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retMo.code = "0";
|
|
retMo.msg = "异常：" + ex;
|
|
}
|
|
string returnstr = FangYar.Common.JsonHelper.ToJSON1(retMo);
|
|
return returnstr;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询公差数据详情
|
|
/// </summary>
|
|
/// <param name="ORG_ID"></param>
|
|
/// <param name="USERS_UID"></param>
|
|
/// <param name="S_Time"></param>
|
|
/// <param name="E_Time"></param>
|
|
/// <returns></returns>
|
|
private List<dynamic> QueryToleranceList(string ORG_ID, string USERS_UID, string S_Time, string E_Time)
|
|
{
|
|
|
|
//查询所有出公差信息
|
|
string sqlToler = @"SELECT (SELECT d.title FROM DICTIONARY d WHERE d.id = t.L_TYPE) DIC_TEXT,t.* from OA_TOLERANCE t
|
|
WHERE date_format(t.S_TIME, '%Y-%m-%d %H:%i:%s') >= date_format('" + S_Time + @" 0:0:0', '%Y-%m-%d %H:%i:%s')
|
|
and date_format(t.E_TIME,'%Y-%m-%d %H:%i:%s') <= date_format('" + E_Time + @" 23:59:59', '%Y-%m-%d %H:%i:%s')
|
|
and t.PPL_ID in (SELECT USERS_UID from TBL_SYS_EMP where IS_ADMIN='0' and ORG_ID in (select ORG_ID from FIRE_ORG ,(select get_Org_child_list('" + ORG_ID +
|
|
"') cids ) s where find_in_set(org_id,cids) ))";
|
|
|
|
//查询公差信息
|
|
DataTable dtToler = FangYar.Common.MySqlHelper.QueryTable(sqlToler);
|
|
var listToler = dtToler.ToDynamicList();
|
|
|
|
if (!string.IsNullOrWhiteSpace(USERS_UID))
|
|
{
|
|
listToler = listToler.Where(p => p.PPL_ID == USERS_UID).ToList();
|
|
}
|
|
List<dynamic> retList = new List<dynamic>();
|
|
foreach (var item in listToler)
|
|
{
|
|
|
|
retList.Add(new
|
|
{
|
|
ID = item.ID + "",
|
|
PPL_ID = item.PPL_ID + "",
|
|
PPL_NAME = item.PPL_NAME + "",
|
|
L_TYPE = item.L_TYPE + "",
|
|
S_TIME = item.S_TIME + "",
|
|
E_TIME = item.E_TIME + "",
|
|
L_OUT = item.L_OUT + "",
|
|
ACC_PPL = item.ACC_PPL + "",
|
|
REP_PPL = item.REP_PPL + "",
|
|
L_REASON = item.L_REASON + "",
|
|
PHOTO = item.PHOTO + "",
|
|
STATE = item.STATE + "",
|
|
L_NUM = item.L_NUM + "",
|
|
TITLE = item.TITLE + "",
|
|
EXTENDCODE1 = item.EXTENDCODE1 + "",
|
|
EXTENDCODE2 = item.EXTENDCODE2 + "",
|
|
EXTENDCODE3 = item.EXTENDCODE3 + "",
|
|
EXTENDCODE4 = item.EXTENDCODE4 + "",
|
|
CAR_NUM = item.CAR_NUM + "",
|
|
IS_RETURN = item.IS_RETURN + "",
|
|
DIC_TEXT = item.DIC_TEXT + ""
|
|
});
|
|
}
|
|
|
|
|
|
|
|
|
|
return retList;
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// html导出Word
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string htmlToWord(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string Org_Id = context.Request.Params["Org_Id"];
|
|
string USERS_UID = context.Request.Params["USERS_UID"];
|
|
string S_Time = context.Request.Params["S_Time"];
|
|
string E_Time = context.Request.Params["E_Time"];
|
|
string FlagUID = context.Request.Params["FlagUID"];
|
|
|
|
//实例化一个Document对象
|
|
Document doc = new Document();
|
|
doc.Watermark = null;
|
|
|
|
//添加section和段落
|
|
Section section1 = doc.AddSection();
|
|
Paragraph para1 = section1.AddParagraph();
|
|
|
|
#region 获取table数据
|
|
|
|
|
|
String[] headerQiandi1 = { "姓名", "总数" }; //表头字段
|
|
|
|
|
|
|
|
var list = QueryToleranceList(Org_Id, USERS_UID, S_Time, E_Time);
|
|
//汇总统计
|
|
List<dynamic> listCount = new List<dynamic>();
|
|
var list1 = list.GroupBy(p => p.PPL_ID).ToList();
|
|
foreach (var item in list1)
|
|
{
|
|
listCount.Add(new { PPL_NAME = item.First().PPL_NAME, PPL_ID = item.Key, toleranCount = item.Count() });
|
|
}
|
|
listCount.Add(new { PPL_NAME = "合计", PPL_ID = "", toleranCount = listCount.Sum(s => s.toleranCount) });
|
|
|
|
if (listCount.Count > 0)
|
|
{
|
|
#region 创建Word表格
|
|
Table tableQiandi1 = section1.AddTable(); //新建表格
|
|
tableQiandi1.ResetCells(listCount.Count + 1, headerQiandi1.Length);
|
|
tableQiandi1.TableFormat.Borders.BorderType = Spire.Doc.Documents.BorderStyle.Single;
|
|
|
|
TableRow rowQiandi = tableQiandi1.Rows[0]; //添加行
|
|
rowQiandi.IsHeader = true; //设为表头
|
|
rowQiandi.Height = 30;
|
|
rowQiandi.HeightType = TableRowHeightType.Auto;
|
|
for (int i = 0; i < headerQiandi1.Length; i++) //生成表头
|
|
{
|
|
rowQiandi.Cells[i].Width = 145;
|
|
rowQiandi.Height = 30;
|
|
rowQiandi.Cells[i].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
|
|
rowQiandi.HeightType = TableRowHeightType.Auto;
|
|
Paragraph p = rowQiandi.Cells[i].AddParagraph();
|
|
AddTextRange(section1, p, headerQiandi1[i], 14, true, "黑体", Spire.Doc.Documents.HorizontalAlignment.Center);
|
|
}
|
|
for (int r = 0; r < listCount.Count; r++) //生成表体
|
|
{
|
|
TableRow dataRow = tableQiandi1.Rows[r + 1];
|
|
dataRow.RowFormat.BackColor = Color.Empty;
|
|
|
|
for (int c = 0; c < headerQiandi1.Length; c++)
|
|
{
|
|
//"姓名", "总数"
|
|
string strVal = "";
|
|
switch (headerQiandi1[c])
|
|
{
|
|
case "姓名": strVal = listCount[r].PPL_NAME + ""; break;
|
|
case "总数": strVal = listCount[r].toleranCount + ""; break;
|
|
}
|
|
dataRow.Cells[c].Width = 145;
|
|
dataRow.Height = 20;
|
|
dataRow.Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
|
|
TextRange tr = dataRow.Cells[c].AddParagraph().AppendText(strVal);
|
|
tr.CharacterFormat.FontSize = 12;
|
|
}
|
|
|
|
}
|
|
#endregion 创建Word表格
|
|
}
|
|
|
|
|
|
|
|
//添加section和段落
|
|
Section section2 = doc.AddSection();
|
|
Paragraph para2 = section2.AddParagraph();
|
|
//列表
|
|
|
|
String[] headerQiandi2 = { "申请人", "出差类型", "开始时间", "结束时间", "去向", "随行人", "顶班人", "出差事由" }; //表头字段
|
|
|
|
|
|
|
|
#endregion 获取table数据
|
|
|
|
if (list.Count > 0)
|
|
{
|
|
|
|
#region 创建Word表格
|
|
Table tableQiandi2 = section2.AddTable(); //新建表格
|
|
tableQiandi2.ResetCells(list.Count + 1, headerQiandi2.Length);
|
|
tableQiandi2.TableFormat.Borders.BorderType = Spire.Doc.Documents.BorderStyle.Single;
|
|
|
|
TableRow rowQiandi = tableQiandi2.Rows[0]; //添加行
|
|
rowQiandi.IsHeader = true; //设为表头
|
|
rowQiandi.Height = 30;
|
|
rowQiandi.HeightType = TableRowHeightType.Auto;
|
|
for (int i = 0; i < headerQiandi2.Length; i++) //生成表头
|
|
{
|
|
rowQiandi.Cells[i].Width = 145;
|
|
rowQiandi.Height = 30;
|
|
rowQiandi.Cells[i].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
|
|
rowQiandi.HeightType = TableRowHeightType.Auto;
|
|
Paragraph p = rowQiandi.Cells[i].AddParagraph();
|
|
AddTextRange(section2, p, headerQiandi2[i], 14, true, "黑体", Spire.Doc.Documents.HorizontalAlignment.Center);
|
|
}
|
|
|
|
for (int r = 0; r < list.Count; r++) //生成表体
|
|
{
|
|
TableRow dataRow = tableQiandi2.Rows[r + 1];
|
|
dataRow.RowFormat.BackColor = Color.Empty;
|
|
for (int c = 0; c < headerQiandi2.Length; c++)
|
|
{
|
|
//"申请人", "出差类型", "开始时间", "结束时间", "去向", "随行人", "顶班人", "出差事由"
|
|
string strVal = "";
|
|
switch (headerQiandi2[c])
|
|
{
|
|
case "申请人": strVal = list[r].PPL_NAME + ""; break;
|
|
case "出差类型": strVal = list[r].DIC_TEXT + ""; break;
|
|
case "开始时间": strVal = list[r].S_TIME + ""; break;
|
|
case "结束时间": strVal = list[r].E_TIME + ""; break;
|
|
case "去向": strVal = list[r].L_OUT + ""; break;
|
|
case "随行人": strVal = list[r].ACC_PPL + ""; break;
|
|
case "顶班人": strVal = list[r].REP_PPL + ""; break;
|
|
case "出差事由": strVal = list[r].L_REASON + ""; break;
|
|
}
|
|
dataRow.Cells[c].Width = 145;
|
|
dataRow.Height = 20;
|
|
dataRow.Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
|
|
TextRange tr = dataRow.Cells[c].AddParagraph().AppendText(strVal);
|
|
tr.CharacterFormat.FontSize = 12;
|
|
}
|
|
}
|
|
#endregion 创建Word表格
|
|
}
|
|
//保存到文档
|
|
string path = AppDomain.CurrentDomain.BaseDirectory + "\\OA\\Leave_Statistics\\";
|
|
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".doc";
|
|
if (Directory.Exists(path) == false)//如果不存在就创建文件夹
|
|
{
|
|
Directory.CreateDirectory(path);
|
|
}
|
|
doc.SaveToFile(path + fileName, FileFormat.Doc);
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"url\":\"/OA/Leave_Statistics/" + fileName + "\"}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"" + e.Message + "\"}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
private void AddTextRange(Section section, Paragraph pragraph, string word, float fontSize, bool isBold, string fontName, Spire.Doc.Documents.HorizontalAlignment alignType)
|
|
{
|
|
|
|
TextRange textRange = pragraph.AppendText(word);
|
|
textRange.CharacterFormat.FontSize = fontSize;
|
|
textRange.CharacterFormat.Bold = isBold;
|
|
textRange.CharacterFormat.FontName = fontName;
|
|
pragraph.Format.HorizontalAlignment = alignType;
|
|
}
|
|
|
|
|
|
#endregion
|
|
/// <summary>
|
|
/// 公差统计
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string AppGetToleranceSta(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string org_id = context.Request.Params["org_id"];//机构id
|
|
string stime = context.Request.Params["stime"];//stime
|
|
string etime = context.Request.Params["etime"];//etime
|
|
string islower = context.Request.Params["islower"];//islower 0 本级 1多级
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\"";
|
|
|
|
string sql = " SELECT org_name,org_id ";
|
|
sql += " ,IFNULL(SUM(case L_type when 'ee62a186-336f-4984-b650-fd9826ad82cd' then gcnum else 0 end),0) incar ";
|
|
sql += " ,IFNULL(SUM(case L_type when '33a0821a-c264-44be-8da3-70a4077e933e' then gcnum else 0 end),0) innocar ";
|
|
sql += " ,IFNULL(SUM(case L_type when '6bba0521-37e7-4c30-9bcd-971815ac6168' then gcnum else 0 end),0) outcar ";
|
|
sql += " ,IFNULL(SUM(case L_type when '48137282-3373-4da0-836f-fd223849a85f' then gcnum else 0 end),0) outnocar ";
|
|
|
|
|
|
sql += " from ( ";
|
|
sql += " select a.org_name, a.org_id, b.L_TYPE, b.gcnum from fire_org a left join ( ";
|
|
sql += " select org_id, l_type, count(l_type) as gcnum from( ";
|
|
sql += " elect l_type, org_id from OA_TOLERANCE t left join tbl_sys_emp e on e.IS_ADMIN='0' and t.ppl_id = e.users_uid ";
|
|
|
|
sql += " where (date_format('" + stime + "', '%Y-%m-%d')) <= date_format(t.S_TIME, '%Y-%m-%d') ";
|
|
sql += " and (date_format('" + etime + "', '%Y-%m-%d') + 1) >= date_format(t.E_TIME, '%Y-%m-%d') ";
|
|
sql += " ) t group by org_id, l_type ";
|
|
sql += " ) b on a.org_id = b.org_id where ";
|
|
if (islower == "1")
|
|
{
|
|
sql += " a.org_id in (select o.org_id from fire_org o,(select get_Org_child_list('" + org_id + "') cids ) s where o.type = '0' and find_in_set(org_id,cids) )";
|
|
}
|
|
else if (islower == "0")
|
|
{
|
|
sql += " a.org_id ='" + org_id + "'";
|
|
}
|
|
|
|
sql += " ) s GROUP BY org_name,org_id ";
|
|
returnstr += ",\"Data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(new CommomBLL().GetTableList(sql));
|
|
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"error\",\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
/// <summary>
|
|
/// 公差统计列表
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string AppGetToleranceStaList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string org_id = context.Request.Params["org_id"];//机构id
|
|
string ppl_name = context.Request.Params["ppl_name"];//ppl_name
|
|
string l_type = context.Request.Params["l_type"];//l_type
|
|
string stime = context.Request.Params["stime"];//stime
|
|
string etime = context.Request.Params["etime"];//etime
|
|
string islower = context.Request.Params["islower"];//islower 0 本级 1多级
|
|
string page = context.Request.Params["page"];//page
|
|
string limit = context.Request.Params["limit"];//limit
|
|
|
|
int pageIndex = 1;
|
|
int pageSize = 10;
|
|
int satrtNum = 1, endNum = 10;
|
|
if (!string.IsNullOrEmpty(page)) { pageIndex = int.Parse(page); }
|
|
if (!string.IsNullOrEmpty(limit)) { pageSize = int.Parse(limit); }
|
|
satrtNum = (pageIndex - 1) * pageSize;
|
|
satrtNum = satrtNum < 0 ? 0 : satrtNum;
|
|
endNum = pageSize;
|
|
|
|
string sql = "";
|
|
returnstr = "{\"code\":0,\"msg\":\"\"";
|
|
|
|
sql += " select * from ( select a.* ,b.org_name from(";
|
|
sql += " select t.*,e.org_id , d.title as dtitle from OA_TOLERANCE t left";
|
|
sql += " join tbl_sys_emp e on e.IS_ADMIN='0' and t.ppl_id = e.users_uid left join dictionary d on t.l_type = d.id";
|
|
sql += " where 1 = 1";
|
|
if (!string.IsNullOrEmpty(ppl_name))
|
|
{
|
|
sql += " and ppl_name like '%" + ppl_name + "%'";
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(l_type))
|
|
{
|
|
sql += " and l_type = '" + l_type + "'";
|
|
}
|
|
|
|
sql += " and (date_format('" + stime + "', '%Y-%m-%d')) <= date_format(t.S_TIME, '%Y-%m-%d') ";
|
|
sql += " and (date_format('" + etime + "', '%Y-%m-%d') +1) >= date_format(t.E_TIME, '%Y-%m-%d')";
|
|
sql += " )a left join fire_org b on a.org_id = b.org_id ";
|
|
sql += " where ";
|
|
if (islower == "1")
|
|
{
|
|
sql += " a.org_id in (select o.org_id from fire_org o,(select get_Org_child_list('" + org_id + "') cids ) s where o.type = '0' find_in_set(org_id,cids) )";
|
|
}
|
|
else if (islower == "0")
|
|
{
|
|
sql += " a.org_id ='" + org_id + "'";
|
|
}
|
|
sql += " ) t LIMIT " + satrtNum + " , " + endNum;
|
|
|
|
returnstr += ",\"Data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(new CommomBLL().GetTableList(sql));
|
|
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"error\",\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
public bool IsReusable
|
|
{
|
|
get
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
}
|