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.
1381 lines
67 KiB
1381 lines
67 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Drawing;
|
|
using System.Collections;
|
|
using FangYar.Common;
|
|
using FangYar.Model.WebUI;
|
|
using System.Text;
|
|
|
|
namespace FangYar.ZHSQAPP.ashx
|
|
{
|
|
/// <summary>
|
|
/// CommonHandler 的摘要说明
|
|
/// </summary>
|
|
public class CommonHandler : IHttpHandler
|
|
{
|
|
|
|
private FangYar.BLL.CommomBLL bll = new BLL.CommomBLL();
|
|
|
|
|
|
/// <summary>
|
|
/// 设置是否记录操作日志到Kafka
|
|
/// </summary>
|
|
public static string IsXFToDaHuaUrl = System.Configuration.ConfigurationManager.AppSettings["IsXFToDaHuaUrl"] + "";
|
|
|
|
public void ProcessRequest(HttpContext context)
|
|
{
|
|
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Other, "公共查询操作请求", "");
|
|
|
|
context.Response.ContentType = "text/json";
|
|
string action = context.Request.Params["Action"];
|
|
string returnstr = "";
|
|
|
|
|
|
switch (action)
|
|
{
|
|
case "GetTEXT":
|
|
returnstr = GetTEXT(context);
|
|
break;
|
|
case "HYTYPE":
|
|
returnstr = GetHYTYPE(context);
|
|
break;
|
|
case "getDicList":
|
|
returnstr = getDicList(context);
|
|
break;
|
|
case "getModsDicList":
|
|
returnstr = getModsDicList(context);
|
|
break;
|
|
case "UploadImg":
|
|
returnstr = GetUploadImg(context);
|
|
break;
|
|
case "UserName":
|
|
returnstr = GetUserName(context);
|
|
break;
|
|
case "GetHomeCount": //获取首页中的综合数据统计
|
|
returnstr = GetHomeCount(context);
|
|
break;
|
|
case "UploadFiles": //
|
|
returnstr = UploadFiles(context);
|
|
break;
|
|
case "GetXFUrlLink": //
|
|
returnstr = GetXFUrlLink(context);
|
|
break;
|
|
//总队综合统计
|
|
case "GetZDZHTJ": //
|
|
returnstr = GetZDZHTJ(context);
|
|
break;
|
|
//当月每天考勤详情
|
|
case "GetDayAttend": //
|
|
returnstr = GetDayAttend(context);
|
|
break;
|
|
//人员信息不完整信息
|
|
case "GetEmpInfoErr": //
|
|
returnstr = GetEmpInfoErr(context);
|
|
break;
|
|
//上月同比
|
|
case "GetMonthYoy": //
|
|
returnstr = GetMonthYoy(context);
|
|
break;
|
|
//一次未考勤人员
|
|
case "GetEmpAttendOneNot": //
|
|
returnstr = GetEmpAttendOneNot(context);
|
|
break;
|
|
|
|
// 上传车载装备文件
|
|
case "carEquipFileUpload":
|
|
returnstr = carEquipFileUpload(context);
|
|
break;
|
|
//上传文件返回文件路径和文件名称
|
|
case "FileUpload":
|
|
returnstr = FileUpload(context);
|
|
break;
|
|
|
|
//保存消防要事日记
|
|
case "SaveFireDiaryDay":
|
|
returnstr = SaveFireDiaryDay(context);
|
|
break;
|
|
//查询消防要事日记
|
|
case "GetFireDiaryDay":
|
|
returnstr = GetFireDiaryDay(context);
|
|
break;
|
|
//分页查询消防要事日记
|
|
case "GetFireDiaryList":
|
|
returnstr = GetFireDiaryList(context);
|
|
break;
|
|
//根据ID获取消防要事日记信息
|
|
case "GetFireDiaryById":
|
|
returnstr = GetFireDiaryById(context);
|
|
break;
|
|
|
|
}
|
|
context.Response.Write(returnstr);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 保存消防要事日记
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string SaveFireDiaryDay(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
int code = -1;
|
|
string msg = "";
|
|
try
|
|
{
|
|
//信息ID
|
|
string ID = "";
|
|
//机构ID
|
|
string ORG_ID = context.Request.Params["ORG_ID"];
|
|
//日期
|
|
string FIRE_DIARY_DAY = context.Request.Params["FIRE_DIARY_DAY"];
|
|
//内容
|
|
string FIRE_DIARY_CONTENT = context.Request.Params["FIRE_DIARY_CONTENT"];
|
|
//文件名称
|
|
string FIRE_DIARY_NAME = context.Request.Params["FIRE_DIARY_NAME"];
|
|
//年份
|
|
string FIRE_DIARY_YEAR = "";
|
|
//季度
|
|
string FIRE_DIARY_QUARTER = "";
|
|
|
|
//转换机构ID
|
|
ORG_ID = FangYar.Common.WebCommonUtil.GetOrgIdUpLevelToDownLevel(ORG_ID);
|
|
|
|
string sqlQuery = " SELECT * from fire_diary WHERE ORG_ID = '" + ORG_ID + "' and FIRE_DIARY_DAY = '" + FIRE_DIARY_DAY + "' order by createtime ";
|
|
|
|
var dtQuery = FangYar.Common.MySqlHelper.QueryTable(sqlQuery);
|
|
|
|
if (dtQuery.Rows.Count > 0)
|
|
{
|
|
ID = dtQuery.Rows[0]["ID"] + "";
|
|
FIRE_DIARY_NAME = dtQuery.Rows[0]["FIRE_DIARY_NAME"] + "";
|
|
FIRE_DIARY_YEAR = dtQuery.Rows[0]["FIRE_DIARY_YEAR"] + "";
|
|
FIRE_DIARY_QUARTER = dtQuery.Rows[0]["FIRE_DIARY_QUARTER"] + "";
|
|
}
|
|
DateTime.TryParse(FIRE_DIARY_DAY, out DateTime dtm1);
|
|
|
|
FIRE_DIARY_QUARTER = ((dtm1.Month - 1) / 3 + 1) + "";
|
|
|
|
FIRE_DIARY_YEAR = dtm1.Year + "";
|
|
|
|
StringBuilder strsql = new StringBuilder();
|
|
|
|
if (string.IsNullOrWhiteSpace(ID))
|
|
{
|
|
//没有ID，插入信息
|
|
ID = Guid.NewGuid().ToString("N");
|
|
strsql.Append(" insert into fire_diary (ID,ORG_ID,FIRE_DIARY_DAY,FIRE_DIARY_CONTENT,FIRE_DIARY_NAME,FIRE_DIARY_YEAR,FIRE_DIARY_QUARTER) " +
|
|
" values (@ID,@ORG_ID,@FIRE_DIARY_DAY,@FIRE_DIARY_CONTENT,@FIRE_DIARY_NAME,@FIRE_DIARY_YEAR,@FIRE_DIARY_QUARTER) ");
|
|
}
|
|
else
|
|
{
|
|
//存在ID，保存信息
|
|
strsql.Append(" update fire_diary set ORG_ID=@ORG_ID,FIRE_DIARY_DAY=@FIRE_DIARY_DAY,FIRE_DIARY_CONTENT=@FIRE_DIARY_CONTENT " +
|
|
" ,FIRE_DIARY_NAME=@FIRE_DIARY_NAME,FIRE_DIARY_YEAR=@FIRE_DIARY_YEAR,FIRE_DIARY_QUARTER=@FIRE_DIARY_QUARTER where ID=@ID ");
|
|
}
|
|
MySql.Data.MySqlClient.MySqlParameter[] paras ={
|
|
new MySql.Data.MySqlClient.MySqlParameter("@ID",ID)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@ORG_ID",ORG_ID)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@FIRE_DIARY_DAY",FIRE_DIARY_DAY)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@FIRE_DIARY_CONTENT",FIRE_DIARY_CONTENT)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@FIRE_DIARY_NAME",FIRE_DIARY_NAME)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@FIRE_DIARY_YEAR",FIRE_DIARY_YEAR)
|
|
,new MySql.Data.MySqlClient.MySqlParameter("@FIRE_DIARY_QUARTER",FIRE_DIARY_QUARTER)
|
|
};
|
|
int i = FangYar.Common.MySqlHelper.ExecuteSql(strsql.ToString(), paras);
|
|
|
|
|
|
if (i > 0)
|
|
{
|
|
msg = "保存成功！";
|
|
code = 1;
|
|
}
|
|
else { msg = "保存失败！"; }
|
|
|
|
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
msg = e.Message;
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "消防要事日记操作请求", "保存异常：" + e);
|
|
}
|
|
returnstr = "{\"msg\":\"" + msg + "\",\"code\":" + code + "}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Add, "消防要事日记操作请求", "保存");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取消防要事日记
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetFireDiaryDay(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
//机构ID
|
|
string ORG_ID = context.Request.Params["ORG_ID"];
|
|
//日期
|
|
string FIRE_DIARY_DAY = context.Request.Params["FIRE_DIARY_DAY"];
|
|
//转换机构ID
|
|
ORG_ID = FangYar.Common.WebCommonUtil.GetOrgIdUpLevelToDownLevel(ORG_ID);
|
|
|
|
string sqlStr1 = @" SELECT org_id,org_name,get_Fire_Detachment_Org('" + ORG_ID + @"') 'zd_org_id'
|
|
,(SELECT org_name from fire_org WHERE org_id = get_Fire_Detachment_Org('" + ORG_ID + @"') LIMIT 1) 'zd_org_name'
|
|
,IFNULL((SELECT FIRE_DIARY_CONTENT from fire_diary WHERE ORG_ID = '" + ORG_ID + @"' and FIRE_DIARY_DAY = '" + FIRE_DIARY_DAY + @"' order by createtime LIMIT 1),'') 'fire_diary_content'
|
|
from fire_org WHERE ORG_ID = '" + ORG_ID + "' ";
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "查询异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "查询");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取消防要事日记
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetFireDiaryById(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
//机构ID
|
|
string infoId = context.Request.Params["infoId"];
|
|
|
|
string sqlStr1 = " SELECT *,REPLACE(FIRE_DIARY_CONTENT,'contenteditable=\"true\"','') 'NEWHTML' from fire_diary where id = '" + infoId + "' ";
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "根据ID查询异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "根据ID查询");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取消防要事日记
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetFireDiaryList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
string jd = context.Request.Params["jd"];
|
|
string year = context.Request.Params["year"];
|
|
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); }
|
|
pageIndex = pageIndex < 1 ? 1 : pageIndex;
|
|
pageSize = pageSize < 1 ? 1 : pageSize;
|
|
|
|
int startnum = (pageIndex - 1) * pageSize;
|
|
|
|
//转换机构ID
|
|
OrgId = FangYar.Common.WebCommonUtil.GetOrgIdUpLevelToDownLevel(OrgId);
|
|
|
|
string sqlCount = " SELECT count(1) from fire_diary WHERE FIRE_DIARY_YEAR='" + year + "' and FIRE_DIARY_QUARTER='" + jd + "' and ORG_ID = '" + OrgId + "' ";
|
|
|
|
string sqlStr = " SELECT * from fire_diary WHERE FIRE_DIARY_YEAR='" + year + "' and FIRE_DIARY_QUARTER='" + jd + "' and ORG_ID = '" + OrgId +
|
|
"' order by createtime desc limit " + startnum + ", " + pageSize;
|
|
|
|
var dtCount = FangYar.Common.MySqlHelper.QueryTable(sqlCount);
|
|
int rowCount = 0;
|
|
if (dtCount.Rows.Count > 0)
|
|
{
|
|
int.TryParse(dtCount.Rows[0][0] + "", out rowCount);
|
|
}
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
|
|
|
|
DataTable dtList = FangYar.Common.MySqlHelper.QueryTable(sqlStr);
|
|
|
|
returnstr += "\"count\":" + rowCount + ",\"data\":";
|
|
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dtList);
|
|
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "分页查询异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "消防要事日记操作请求", "分页查询");
|
|
return returnstr;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 总队综合统计
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetZDZHTJ(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string dtm = context.Request.Params["dtm"];
|
|
|
|
DateTime.TryParse(dtm, out DateTime dtm1);
|
|
|
|
var tim = dtm1.ToString("yyyy-MM");
|
|
|
|
var timSub = dtm1.AddMonths(-1).ToString("yyyy-MM");
|
|
|
|
string sqlStr1 = " SELECT * from tbl_sys_month_stat WHERE info_day='" + tim + "' ";
|
|
|
|
string sqlStr2 = " SELECT * from tbl_sys_month_stat WHERE info_day='" + timSub + "' ";
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
var dt2 = FangYar.Common.MySqlHelper.QueryTable(sqlStr2);
|
|
|
|
if (dt2.Rows.Count < 1)
|
|
{
|
|
dt2 = getZDZHTJ_DT(timSub);
|
|
}
|
|
|
|
if (dt1.Rows.Count < 1)
|
|
{
|
|
dt1 = getZDZHTJ_DT(tim);
|
|
}
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
private DataTable getZDZHTJ_DT(string dtm)
|
|
{
|
|
try
|
|
{
|
|
DateTime.TryParse(dtm, out DateTime sDtm);
|
|
DateTime eDtm = sDtm.AddMonths(1);
|
|
string strMonth = sDtm.ToString("yyyy-MM");
|
|
|
|
string sTim = sDtm.ToString("yyyy-MM-01");
|
|
string eTim = eDtm.ToString("yyyy-MM-01");
|
|
|
|
string sqlStr = @"
|
|
|
|
-- 要查询支队
|
|
WITH t1 as (SELECT org_id,org_name from fire_org WHERE pid = 'D8DC637B8B984848A63F82A018AFAEB0' and org_id <> '734389c7ae364584aea9918693bba3de')
|
|
-- 查询支队排序
|
|
,torg as(
|
|
select 'f4d9f25b27114172a66a9d04cc158b00' po,(SELECT org_name from fire_org WHERE org_id = 'f4d9f25b27114172a66a9d04cc158b00') pon union all
|
|
select '40a1e65991764d4dac2d826bfbbfc849' po,(SELECT org_name from fire_org WHERE org_id = '40a1e65991764d4dac2d826bfbbfc849') pon union all
|
|
select '5806465fa4e845998f31a10d023f332e' po,(SELECT org_name from fire_org WHERE org_id = '5806465fa4e845998f31a10d023f332e') pon union all
|
|
select 'b5b00dfb27a34e0a8bd86270930e02d6' po,(SELECT org_name from fire_org WHERE org_id = 'b5b00dfb27a34e0a8bd86270930e02d6') pon union all
|
|
select '82487f0ebb6848e7ac4abc3d7cbc8504' po,(SELECT org_name from fire_org WHERE org_id = '82487f0ebb6848e7ac4abc3d7cbc8504') pon union all
|
|
select '9a1324ec8b244a7f9d6cbca88cd614fd' po,(SELECT org_name from fire_org WHERE org_id = '9a1324ec8b244a7f9d6cbca88cd614fd') pon union all
|
|
select '833b28c12b42495a99a5e41068eb6805' po,(SELECT org_name from fire_org WHERE org_id = '833b28c12b42495a99a5e41068eb6805') pon union all
|
|
select '36e8032be5f54a3cb994c4ac74ac3f77' po,(SELECT org_name from fire_org WHERE org_id = '36e8032be5f54a3cb994c4ac74ac3f77') pon
|
|
)
|
|
-- 要查询营区
|
|
,t2 as (
|
|
SELECT org_id,org_name,get_Org_Parent_SecondLevel(org_id) porg from fire_org,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids ) s WHERE find_in_set(org_id,cids)
|
|
and org_id not in (SELECT org_id from t1 ) and org_id not in (SELECT org_id from fire_org_two ) and TYPE=0 or org_id = '36e8032be5f54a3cb994c4ac74ac3f77'
|
|
)
|
|
-- 出操原始数据
|
|
,t3 as( SELECT org_id,FACE_READ_STATE,TASK_ID,users_uid from oa_exercise_record WHERE createtime >= '" + sTim + "' and createtime < '" + eTim + @"' and org_id in (SELECT org_id from t2) )
|
|
-- 出操关联时间范围原始数据
|
|
,t4 as (SELECT t3.*,tk.EXTEND2 sTim, EXTEND4 eTim from t3 LEFT JOIN oa_exercise_task tk on task_id = id)
|
|
|
|
-- 考勤原始数据
|
|
,t5 as (
|
|
SELECT org_id, task_id, users_uid, KQ_FACE_READ_START_STATE_AM kq1, KQ_FACE_READ_END_STATE_AM kq2, KQ_FACE_READ_START_STATE_PM kq3, KQ_FACE_READ_END_STATE_PM kq4
|
|
from oa_attendance_record WHERE createtime >= '" + sTim + "' and createtime< '" + eTim + @"' and org_id in (SELECT org_id from t2) )
|
|
-- 考勤数据关联时间范围数据
|
|
,t6 as (SELECT t5.*,tk.UP_START_DATE sTim1, UP_END_DATE eTim1,DOWN_START_DATE sTim2, DOWN_END_DATE eTim2,UP_START_DATE2 sTim3, UP_END_DATE2 eTim3,DOWN_START_DATE2 sTim4, DOWN_END_DATE2 eTim4
|
|
from t5 LEFT JOIN oa_attendance_task tk on task_id = id )
|
|
-- 请假数据
|
|
, oa_leave1 as (SELECT* from oa_leave WHERE STATE = '1' and S_TIME > DATE_ADD(NOW(), INTERVAL - 6 MONTH) LIMIT 50000)
|
|
-- 公差数据
|
|
, oa_tolerance1 as (SELECT* from oa_tolerance WHERE STATE = '1' and S_TIME > DATE_ADD(NOW(), INTERVAL - 6 MONTH) LIMIT 50000)
|
|
|
|
|
|
|
|
-- 出操关联请假
|
|
,t7 as (SELECT org_id, users_uid, sTim, eTim
|
|
,if (FACE_READ_STATE = 1,1,if ((SELECT count(1) from oa_leave1 where ppl_id = users_uid and((s_time <= sTim and sTim <= e_time) or(s_time <= eTim and eTim <= e_time)))> 0,1,0)) FACE_READ_STATE
|
|
from t4 )
|
|
-- 出操关联公差
|
|
,t8 as (SELECT org_id
|
|
,if (FACE_READ_STATE = 1,1,if ((SELECT count(1) from oa_tolerance1 where ppl_id = users_uid and((s_time <= sTim and sTim <= e_time) or(s_time <= eTim and eTim <= e_time)))> 0,1,0)) FACE_READ_STATE
|
|
from t7 )
|
|
-- 出操排除数据为null
|
|
,t9 as (SELECT org_id, IFNULL(FACE_READ_STATE, 0) d from t8 )
|
|
-- 各营区统计出操数据
|
|
,t10 as (SELECT org_id, get_Org_Parent_SecondLevel(org_id)porg,count(1) c,sum(d) k,count(1) - sum(d) w from t9 GROUP BY org_id )
|
|
-- 各支队出操数据统计
|
|
,t11 as (SELECT porg, sum(c)c,sum(k) k,sum(w) w from t10 GROUP BY porg)
|
|
|
|
|
|
-- 考勤关联请假
|
|
,kq1 as (SELECT org_id, users_uid, sTim1, eTim1, sTim2, eTim2, sTim3, eTim3, sTim4, eTim4
|
|
,if (kq1 = 1,1,if ((SELECT count(1) from oa_leave1 where ppl_id = users_uid and((s_time <= sTim1 and sTim1 <= e_time) or(s_time <= eTim1 and eTim1 <= e_time)))> 0,1,0)) kq1
|
|
,if (kq2 = 1,1,if ((SELECT count(1) from oa_leave1 where ppl_id = users_uid and((s_time <= sTim2 and sTim2 <= e_time) or(s_time <= eTim2 and eTim2 <= e_time)))> 0,1,0)) kq2
|
|
,if (kq3 = 1,1,if ((SELECT count(1) from oa_leave1 where ppl_id = users_uid and((s_time <= sTim3 and sTim3 <= e_time) or(s_time <= eTim3 and eTim3 <= e_time)))> 0,1,0)) kq3
|
|
,if (kq4 = 1,1,if ((SELECT count(1) from oa_leave1 where ppl_id = users_uid and((s_time <= sTim4 and sTim4 <= e_time) or(s_time <= eTim4 and eTim4 <= e_time)))> 0,1,0)) kq4
|
|
from t6
|
|
)
|
|
-- 考勤关联公差
|
|
,kq2 as (SELECT org_id, users_uid, sTim1, eTim1, sTim2, eTim2, sTim3, eTim3, sTim4, eTim4
|
|
,if (kq1 = 1,1,if ((SELECT count(1) from oa_tolerance1 where ppl_id = users_uid and((s_time <= sTim1 and sTim1 <= e_time) or(s_time <= eTim1 and eTim1 <= e_time)))> 0,1,0)) kq1
|
|
,if (kq2 = 1,1,if ((SELECT count(1) from oa_tolerance1 where ppl_id = users_uid and((s_time <= sTim2 and sTim2 <= e_time) or(s_time <= eTim2 and eTim2 <= e_time)))> 0,1,0)) kq2
|
|
,if (kq3 = 1,1,if ((SELECT count(1) from oa_tolerance1 where ppl_id = users_uid and((s_time <= sTim3 and sTim3 <= e_time) or(s_time <= eTim3 and eTim3 <= e_time)))> 0,1,0)) kq3
|
|
,if (kq4 = 1,1,if ((SELECT count(1) from oa_tolerance1 where ppl_id = users_uid and((s_time <= sTim4 and sTim4 <= e_time) or(s_time <= eTim4 and eTim4 <= e_time)))> 0,1,0)) kq4
|
|
from kq1
|
|
)
|
|
-- 考勤排除数据为null
|
|
,kq3 as (SELECT org_id, IFNULL(kq1, 0) kq1,IFNULL(kq2, 0) kq2,IFNULL(kq3, 0) kq3,IFNULL(kq4, 0) kq4 from kq2 )
|
|
-- 各营区考勤统计
|
|
,kq4 as (SELECT org_id, get_Org_Parent_SecondLevel(org_id)porg,count(1) c, sum(if (kq1 = 1 and kq2 = 1 and kq3 = 1 and kq4 = 1,1,0)) k from kq3 GROUP BY org_id )
|
|
,kq5 as (SELECT*, c- k w from kq4)
|
|
-- 各支队考勤统计
|
|
,kq6 as (SELECT porg, sum(c)c,sum(k) k,sum(w) w from kq5 GROUP BY porg)
|
|
|
|
|
|
-- 考勤总人次和未考勤总人次
|
|
,kq7 as (SELECT org_id, users_uid, count(1) c, sum(if (kq1 = 0 and kq2 = 0 and kq3 = 0 and kq4 = 0,1,0)) w from kq2 GROUP BY org_id, users_uid )
|
|
-- 一日未考勤人员信息统计
|
|
,kq8 as (SELECT* from kq7 WHERE c - w = 0)
|
|
-- 各营区一日未考勤人员统计
|
|
,kq9 as (SELECT org_id, get_Org_Parent_SecondLevel(ORG_ID)porg,sum(1) c from kq8 GROUP BY org_id)
|
|
-- 各支队一日未考勤人员统计
|
|
,kq10 as (SELECT porg, sum(c)c from kq9 GROUP BY porg)
|
|
|
|
|
|
-- 人员信息汇总 各单位
|
|
,t12 as (
|
|
SELECT get_Org_Parent_SecondLevel(ORG_ID)porg, ORG_ID,count(1) d from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids ) s
|
|
WHERE IS_DEL = '0' and IS_ADMIN = '0' and find_in_set(org_id, cids) GROUP BY ORG_ID
|
|
)
|
|
-- 各支队人员信息汇总
|
|
,t13 as (SELECT porg, sum(d)c from t12 GROUP BY porg )
|
|
|
|
|
|
-- 人员信息不完善 各单位
|
|
,t14 as (
|
|
SELECT get_Org_Parent_SecondLevel(ORG_ID)porg, ORG_ID,count(1) d from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids ) s
|
|
WHERE IS_DEL = '0' and IS_ADMIN = '0' and find_in_set(org_id, cids) and((EMP_SEX is null or EMP_SEX = '') or(IDNUMBER is null or IDNUMBER = '')
|
|
|
|
or(EMP_MOBILE is null or EMP_MOBILE = '') or(EMP_NUM is null or EMP_NUM = '') or(CERT is null or CERT = '') or(NATION is null or NATION = '')
|
|
|
|
or(BLOOD_TYPE is null or BLOOD_TYPE = '') or(FACE is null or FACE = '') or(IS_WORK is null or IS_WORK = '') or(PROF is null or PROF = '')
|
|
|
|
or(POL is null or POL = '') or(ENLISTED_TIME is null or ENLISTED_TIME = '') or(TAGS is null or TAGS = '') or(USERS_UID is null or USERS_UID = '')
|
|
|
|
or(PHOTO is null or PHOTO = '' or PHOTO = 'images/imgPerDefaut.jpg') and(USERS_UID is null or USERS_UID = '')
|
|
) GROUP BY ORG_ID
|
|
)
|
|
-- 各支队人员信息不完善汇总
|
|
,t15 as (SELECT porg, sum(d)c from t14 GROUP BY porg )
|
|
|
|
-- 巡查任务基础数据
|
|
,txc as (SELECT* from OA_PATROL_TASK WHERE date_format(S_TIME, '%Y-%m-%d') >= '" + sTim + "' and date_format(E_TIME,'%Y-%m-%d') < '" + eTim + @"' and org_id in (SELECT org_id from t2) )
|
|
-- 巡查任务各营区统计
|
|
,txc1 as (SELECT org_id, count(1) c,sum(if (state = 1,1,0)) xc,get_Org_Parent_SecondLevel(ORG_ID) porg from txc GROUP BY org_id )
|
|
,txc2 as (SELECT porg, sum(c)c,sum(xc) xc from txc1 GROUP BY porg )
|
|
|
|
-- 查铺查哨信息表基础信息
|
|
,tcpcs as (SELECT org_id, SENTRYTASKID, count(1) c,sum(IF(L_DATE is NULL, 0, 1)) d,if (count(1) - sum(IF(L_DATE is NULL, 0, 1)) = 0,1,0) xc from fire_inspect
|
|
WHERE createtime >= '" + sTim + "' and createtime< '" + eTim + @"' and org_id in (SELECT org_id from t2) GROUP BY org_id,SENTRYTASKID )
|
|
-- 各营区查铺查哨统计
|
|
,tcpcs1 as (SELECT org_id, count(1) c,sum(xc) xc,get_Org_Parent_SecondLevel(ORG_ID) porg from tcpcs GROUP BY org_id )
|
|
-- 各支队查铺查哨统计
|
|
,tcpcs2 as (SELECT porg, sum(c)c,sum(xc) xc from tcpcs1 GROUP BY porg )
|
|
|
|
-- 排班营区信息
|
|
,t16 as (SELECT ORG_ID from oa_dutymanage WHERE DUTY_DATE >= '" + sTim + "' and DUTY_DATE< '" + eTim + @"' GROUP BY ORG_ID
|
|
union all
|
|
SELECT ORG_ID from oa_duty_details WHERE DUTY_DAY >= '" + sTim + "' and DUTY_DAY< '" + eTim + @"' GROUP BY ORG_ID
|
|
)
|
|
-- 查询未排班营区
|
|
,t17 as (SELECT* from t2 WHERE org_id not in (SELECT * from t16))
|
|
-- 各支队未排班统计
|
|
,t18 as (SELECT porg, count(1) c from t17 GROUP BY porg)
|
|
|
|
|
|
-- 一日安排营区信息
|
|
,t19 as (SELECT ORG_ID from oa_today_arrangement WHERE ADate >= '" + sTim + "' and ADate< '" + eTim + @"' GROUP BY ORG_ID )
|
|
-- 查询未一日安排营区
|
|
,t20 as (SELECT* from t2 WHERE org_id not in (SELECT * from t19))
|
|
-- 各支队未一日安排统计
|
|
,t21 as (SELECT porg, count(1) c from t20 GROUP BY porg)
|
|
|
|
,t22 as (
|
|
|
|
SELECT UUID() 'id','" + strMonth + @"' info_day,torg.po org_id, torg.pon org_name, t13.c n1, kq6.c n2, kq6.k n3, FORMAT(kq6.k * 100 / kq6.c, 2) n4,IFNULL(kq10.c, 0) n5
|
|
,IFNULL(t15.c, 0) n6,FORMAT(IFNULL(t15.c, 0) * 100 / t13.c, 2) n7,t11.c n8, t11.k n9, t11.w n10, FORMAT(t11.k * 100 / t11.c, 2) n11
|
|
,tcpcs2.c n12, tcpcs2.xc n13, txc2.c n14, txc2.xc n15
|
|
, FORMAT(tcpcs2.xc * 100 / tcpcs2.c, 2) n16,FORMAT(txc2.xc * 100 / txc2.c, 2) n17,ifnull(t18.c, 0) n18,ifnull(t21.c, 0) n19
|
|
from torg
|
|
-- 关联总人数
|
|
LEFT JOIN t13 on t13.porg = po
|
|
-- 关联考勤信息
|
|
LEFT JOIN kq6 on kq6.porg = po
|
|
-- 关联一次未考勤信息
|
|
LEFT JOIN kq10 on kq10.porg = po
|
|
-- 关联信息缺失信息
|
|
LEFT JOIN t15 on t15.porg = po
|
|
-- 关联出操信息
|
|
LEFT JOIN t11 on t11.porg = po
|
|
-- 关联查铺查哨信息
|
|
LEFT JOIN tcpcs2 on tcpcs2.porg = po
|
|
-- 关联巡查巡检信息
|
|
LEFT JOIN txc2 on txc2.porg = po
|
|
-- 关联未排班信息
|
|
LEFT JOIN t18 on t18.porg = po
|
|
-- 关联未排班信息
|
|
LEFT JOIN t21 on t21.porg = po
|
|
)
|
|
SELECT* from t22
|
|
";
|
|
var dt = FangYar.Common.MySqlHelper.QueryTable(sqlStr);
|
|
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sqlIns = " INSERT INTO tbl_sys_month_stat (id,info_day,org_id,org_name,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19) values " +
|
|
" ('" + dt.Rows[i]["id"] + "','" + strMonth + "','" + dt.Rows[i]["org_id"] + "','" + dt.Rows[i]["org_name"] + "','" + dt.Rows[i]["n1"] + "','" + dt.Rows[i]["n2"] + "','" + dt.Rows[i]["n3"] + "','" + dt.Rows[i]["n4"] + "','" + dt.Rows[i]["n5"] + "','" + dt.Rows[i]["n6"] + "','" + dt.Rows[i]["n7"] + "','" + dt.Rows[i]["n8"] + "','" + dt.Rows[i]["n9"] + "','" + dt.Rows[i]["n10"] + "','" + dt.Rows[i]["n11"] + "','" + dt.Rows[i]["n12"] + "','" + dt.Rows[i]["n13"] + "','" + dt.Rows[i]["n14"] + "','" + dt.Rows[i]["n15"] + "','" + dt.Rows[i]["n16"] + "','" + dt.Rows[i]["n17"] + "','" + dt.Rows[i]["n18"] + "','" + dt.Rows[i]["n19"] + "') ";
|
|
|
|
FangYar.Common.MySqlHelper.Execute(sqlIns);
|
|
}
|
|
|
|
catch (Exception ex) { }
|
|
}
|
|
|
|
return dt;
|
|
|
|
|
|
|
|
}
|
|
catch (Exception exc) { }
|
|
|
|
return new DataTable();
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 当月每天考勤详情
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetDayAttend(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string dtm = context.Request.Params["dtm"];
|
|
|
|
DateTime.TryParse(dtm, out DateTime dtm1);
|
|
|
|
var tim = dtm1.ToString("yyyy-MM");
|
|
|
|
var timSub = dtm1.AddMonths(-1).ToString("yyyy-MM");
|
|
|
|
string sqlStr1 = @" WITH t0 as ( SELECT org_id,org_name from fire_org WHERE pid = 'D8DC637B8B984848A63F82A018AFAEB0' and org_id <> '734389c7ae364584aea9918693bba3de')
|
|
,t1 as( SELECT org_id,ATTENDANCE_DATE,date_format(ATTENDANCE_DATE,'%d') dayStr from oa_attendance_record WHERE date_format(ATTENDANCE_DATE,'%Y-%m') = '" + tim + @"' )
|
|
, t2 as (
|
|
SELECT org_id, get_Org_Parent_SecondLevel(org_id)opid
|
|
,sum(if (dayStr = '01',1,0)) 'n01',sum(if (dayStr = '02',1,0)) 'n02',sum(if (dayStr = '03',1,0)) 'n03',sum(if (dayStr = '04',1,0)) 'n04',sum(if (dayStr = '05',1,0)) 'n05'
|
|
,sum(if (dayStr = '06',1,0)) 'n06',sum(if (dayStr = '07',1,0)) 'n07',sum(if (dayStr = '08',1,0)) 'n08',sum(if (dayStr = '09',1,0)) 'n09',sum(if (dayStr = '10',1,0)) 'n10'
|
|
,sum(if (dayStr = '11',1,0)) 'n11',sum(if (dayStr = '12',1,0)) 'n12',sum(if (dayStr = '13',1,0)) 'n13',sum(if (dayStr = '14',1,0)) 'n14',sum(if (dayStr = '15',1,0)) 'n15'
|
|
,sum(if (dayStr = '16',1,0)) 'n16',sum(if (dayStr = '17',1,0)) 'n17',sum(if (dayStr = '18',1,0)) 'n18',sum(if (dayStr = '19',1,0)) 'n19',sum(if (dayStr = '20',1,0)) 'n20'
|
|
,sum(if (dayStr = '21',1,0)) 'n21',sum(if (dayStr = '22',1,0)) 'n22',sum(if (dayStr = '23',1,0)) 'n23',sum(if (dayStr = '24',1,0)) 'n24',sum(if (dayStr = '25',1,0)) 'n25'
|
|
,sum(if (dayStr = '26',1,0)) 'n26',sum(if (dayStr = '27',1,0)) 'n27',sum(if (dayStr = '28',1,0)) 'n28',sum(if (dayStr = '29',1,0)) 'n29',sum(if (dayStr = '30',1,0)) 'n30'
|
|
,sum(if (dayStr = '31',1,0)) 'n31' from t1 GROUP BY org_id
|
|
)
|
|
,t3 as (
|
|
SELECT opid, sum(n01)'n01',sum(n02) 'n02',sum(n03) 'n03',sum(n04) 'n04',sum(n05) 'n05',sum(n06) 'n06',sum(n07) 'n07',sum(n08) 'n08',sum(n09) 'n09',sum(n10) 'n10'
|
|
,sum(n11) 'n11',sum(n12) 'n12',sum(n13) 'n13',sum(n14) 'n14',sum(n15) 'n15',sum(n16) 'n16',sum(n17) 'n17',sum(n18) 'n18',sum(n19) 'n19',sum(n20) 'n20'
|
|
,sum(n21) 'n21',sum(n22) 'n22',sum(n23) 'n23',sum(n24) 'n24',sum(n25) 'n25',sum(n26) 'n26',sum(n27) 'n27',sum(n28) 'n28',sum(n29) 'n29',sum(n30) 'n30',sum(n31) 'n31'
|
|
|
|
from t2 GROUP BY opid
|
|
)
|
|
|
|
SELECT* from t0 LEFT JOIN t3 on t0.org_id = t3.opid
|
|
ORDER BY org_id = 'f4d9f25b27114172a66a9d04cc158b00' DESC
|
|
,org_id = '40a1e65991764d4dac2d826bfbbfc849' DESC
|
|
,org_id = '5806465fa4e845998f31a10d023f332e' DESC
|
|
,org_id = 'b5b00dfb27a34e0a8bd86270930e02d6' DESC
|
|
,org_id = '82487f0ebb6848e7ac4abc3d7cbc8504' DESC
|
|
,org_id = '9a1324ec8b244a7f9d6cbca88cd614fd' DESC
|
|
,org_id = '833b28c12b42495a99a5e41068eb6805' DESC
|
|
,org_id = '36e8032be5f54a3cb994c4ac74ac3f77' DESC ";
|
|
|
|
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 人员信息不完整
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetEmpInfoErr(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string dtm = context.Request.Params["dtm"];
|
|
|
|
string sqlStr1 = @" WITH
|
|
t as(
|
|
SELECT org_id oid,org_name oname,extendcode3 ojc,extendcode4 opx
|
|
from fire_org ,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids ) s
|
|
WHERE find_in_set(org_id,cids) and type='0'
|
|
and ORG_ID not in (SELECT ORG_ID from fire_org WHERE PID = 'D8DC637B8B984848A63F82A018AFAEB0')
|
|
and ORG_ID <> 'D8DC637B8B984848A63F82A018AFAEB0'
|
|
and ORG_ID not in (SELECT ORG_ID from fire_org_two)
|
|
|
|
|
|
)
|
|
|
|
-- 性别
|
|
,t2 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (EMP_SEX is null or EMP_SEX='') GROUP BY org_id
|
|
)
|
|
-- 身份证号
|
|
,t3 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (IDNUMBER is null or IDNUMBER='') GROUP BY org_id
|
|
)
|
|
-- 手机号
|
|
,t4 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (EMP_MOBILE is null or EMP_MOBILE='') GROUP BY org_id
|
|
)
|
|
-- 编号
|
|
,t5 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (EMP_NUM is null or EMP_NUM='') GROUP BY org_id
|
|
)
|
|
-- 学历
|
|
,t8 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (CERT is null or CERT='') GROUP BY org_id
|
|
)
|
|
-- 民族
|
|
,t9 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (NATION is null or NATION='') GROUP BY org_id
|
|
)
|
|
-- 血型
|
|
,t10 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (BLOOD_TYPE is null or BLOOD_TYPE='') GROUP BY org_id
|
|
)
|
|
-- 政治面貌
|
|
,t11 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (FACE is null or FACE='') GROUP BY org_id
|
|
)
|
|
-- 在岗
|
|
,t12 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (IS_WORK is null or IS_WORK='') GROUP BY org_id
|
|
)
|
|
-- 职位类型
|
|
,t13 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (PROF is null or PROF='') GROUP BY org_id
|
|
)
|
|
-- 消防救援衔
|
|
,t14 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (POL is null or POL='') GROUP BY org_id
|
|
)
|
|
-- 入伍时间
|
|
,t15 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (ENLISTED_TIME is null or ENLISTED_TIME='') GROUP BY org_id
|
|
)
|
|
-- 标签
|
|
,t16 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (TAGS is null or TAGS='') GROUP BY org_id
|
|
)
|
|
-- 账号
|
|
,t17 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (USERS_UID is null or USERS_UID='') GROUP BY org_id
|
|
)
|
|
-- 照片
|
|
,t18 as(
|
|
SELECT org_id,group_concat(emp_name) c from tbl_sys_emp,(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids) s WHERE find_in_set(org_id,cids)
|
|
and IS_DEL='0' and IS_ADMIN='0' and (PHOTO is null or PHOTO='' or PHOTO = 'images/imgPerDefaut.jpg') and (USERS_UID is null or USERS_UID='') GROUP BY org_id
|
|
)
|
|
SELECT t.*,ifnull(t2.c,0) n2,ifnull(t3.c,0) n3,ifnull(t4.c,0) n4,ifnull(t5.c,0) n5,ifnull(t8.c,0) n8,ifnull(t9.c,0) n9,ifnull(t10.c,0) n10
|
|
,ifnull(t11.c,0) n11,ifnull(t12.c,0) n12,ifnull(t13.c,0) n13,ifnull(t14.c,0) n14,ifnull(t15.c,0) n15,ifnull(t16.c,0) n16,ifnull(t17.c,0) n17,ifnull(t18.c,0) n18
|
|
from t left join t2 on t.oid=t2.org_id left join t3 on t.oid=t3.org_id left join t4 on t.oid=t4 .org_id left join t5 on t.oid=t5 .org_id
|
|
left join t8 on t.oid=t8.org_id left join t9 on t.oid=t9.org_id left join t10 on t.oid=t10 .org_id left join t11 on t.oid=t11 .org_id left join t12 on t.oid=t12 .org_id
|
|
left join t13 on t.oid=t13.org_id left join t14 on t.oid=t14.org_id left join t15 on t.oid=t15 .org_id left join t16 on t.oid=t16 .org_id left join t17 on t.oid=t17 .org_id
|
|
left join t18 on t.oid=t18.org_id ORDER BY opx ";
|
|
|
|
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 上月同比
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetMonthYoy(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string dtm = context.Request.Params["dtm"];
|
|
|
|
DateTime.TryParse(dtm, out DateTime dtm1);
|
|
|
|
var tim = dtm1.ToString("yyyy-MM");
|
|
|
|
var timSub = dtm1.AddMonths(-1).ToString("yyyy-MM");
|
|
|
|
string sqlStr1 = @" WITH t1 as(SELECT org_id,org_name,N4,N7,N11,N16,N17 from tbl_sys_month_stat WHERE info_day='" + timSub + @"')
|
|
,t2 as(SELECT org_id,org_name,N4,N7,N11,N16,N17 from tbl_sys_month_stat WHERE info_day='" + tim + @"')
|
|
|
|
SELECT t2.*
|
|
,FORMAT(abs(t2.n4-t1.n4),2) 'n4_abs',if(t2.n4-t1.n4>0,'1',if(t2.n4-t1.n4<0,'0','持平')) 'n4_ud'
|
|
,FORMAT(abs(t2.n7-t1.n7),2) 'n7_abs',if(t2.n7-t1.n7>0,'1',if(t2.n7-t1.n7<0,'0','持平')) 'n7_ud'
|
|
,FORMAT(abs(t2.n11-t1.n11),2) 'n11_abs',if(t2.n11-t1.n11>0,'1',if(t2.n11-t1.n11<0,'0','持平')) 'n11_ud'
|
|
,FORMAT(abs(t2.n16-t1.n16),2) 'n16_abs',if(t2.n16-t1.n16>0,'1',if(t2.n16-t1.n16<0,'0','持平')) 'n16_ud'
|
|
,FORMAT(abs(t2.n17-t1.n17),2) 'n17_abs',if(t2.n17-t1.n17>0,'1',if(t2.n17-t1.n17<0,'0','持平')) 'n17_ud'
|
|
from t2 LEFT JOIN t1 on t2.org_id=t1.org_id ";
|
|
|
|
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 一次未考勤人员
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetEmpAttendOneNot(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string dtm = context.Request.Params["dtm"];
|
|
|
|
DateTime.TryParse(dtm, out DateTime dtm1);
|
|
|
|
var tim = dtm1.ToString("yyyy-MM");
|
|
|
|
string sqlStr1 = @" WITH t1 as(SELECT * from oa_attendance_record WHERE date_format(ATTENDANCE_DATE,'%Y-%m') = '" + tim + @"')
|
|
,t2 as(SELECT USERS_UID,count(1) c from t1 WHERE KQ_FACE_READ_START_STATE_AM = '0' and KQ_FACE_READ_END_STATE_AM = '0'
|
|
and KQ_FACE_READ_START_STATE_PM = '0' and KQ_FACE_READ_END_STATE_PM = '0' GROUP BY USERS_UID)
|
|
,t3 as(SELECT USERS_UID,count(1) c from t1 GROUP BY USERS_UID)
|
|
,t4 as(SELECT if(t3.c-t2.c<1,1,0) d,t3.USERS_UID from t3 LEFT JOIN t2 on t3.USERS_UID=t2.USERS_UID)
|
|
,t5 as(SELECT t4.*,e.EMP_NAME,e.ORG_ID oid,get_Org_Parent_SecondLevel(e.ORG_ID) opid from t4 LEFT JOIN tbl_sys_emp e on e.USERS_UID = t4.USERS_UID WHERE d>0)
|
|
SELECT t5.*,o1.org_name,o2.ORG_NAME from t5 LEFT JOIN fire_org o1 on o1.ORG_ID = t5.oid LEFT JOIN fire_org o2 on o2.ORG_ID =t5.opid ORDER BY o1.EXTENDCODE4 ";
|
|
|
|
|
|
|
|
var dt1 = FangYar.Common.MySqlHelper.QueryTable(sqlStr1);
|
|
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt1);
|
|
returnstr += "}";
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询每月统计信息");
|
|
return returnstr;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取消防跳转地址
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string GetXFUrlLink(HttpContext context)
|
|
{
|
|
//科宁消防跳转地址
|
|
string KeNingXFUrl = System.Configuration.ConfigurationManager.AppSettings["KeNingXFUrl"] + "";
|
|
//大华消防跳转地址
|
|
string DaHuaXFUrl = System.Configuration.ConfigurationManager.AppSettings["DaHuaXFUrl"] + "";
|
|
|
|
|
|
string returnstr = "{\"code\":1,\"msg\":\"Success\",\"count\":0,\"data\":\"" + KeNingXFUrl + "\"}";
|
|
try
|
|
{
|
|
var cook = context.Request.Cookies;
|
|
|
|
var cookUser = cook["kn_root_cookie"].Value;
|
|
|
|
var mo = JsonHelper.FromJSON<UserCookMo>(cookUser);
|
|
//判断是跳转大华消防：0、跳转；其他、不跳转
|
|
if (IsXFToDaHuaUrl.Equals("0"))
|
|
{
|
|
//跳转到大华
|
|
var str = mo.orgId + "&" + mo.usersUid;
|
|
var pwdStr = AesHelper.Encrypt(str);
|
|
//URL转码
|
|
pwdStr = System.Web.HttpUtility.UrlEncode(pwdStr, Encoding.GetEncoding("GB2312"));
|
|
var strUrl = DaHuaXFUrl + pwdStr;
|
|
returnstr = "{\"code\":1,\"msg\":\"Success\",\"count\":0,\"data\":\"" + strUrl + "\"}";
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "获取消防跳转地址异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "获取消防跳转地址");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
|
|
//根据字典表 类型、value值获取text
|
|
private string GetTEXT(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
FangYar.Model.LoginUserModel user = FangYar.WebUI.WebCommon.HttpUtil.GetUser(context);
|
|
string modCode = context.Request.Params["mod_code"];
|
|
string dicValue = context.Request.Params["dic_value"];
|
|
|
|
if (user != null)
|
|
{
|
|
|
|
string modtype = context.Request.Params["modtype"];
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
DataTable data = bll.GetTableList("*", "tbl_sys_dicdetail", " MOD_CODE ='" + modCode + "' and DIC_VALUE = '" + dicValue + "'");
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data);
|
|
returnstr += "}";
|
|
}
|
|
else { returnstr = "{\"code\":-2,\"msg\":\"登录超时\",\"count\":0,\"data\":[]}"; }
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "根据字典表 类型、value值获取text异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "根据字典表 类型、value值获取text");
|
|
return returnstr;
|
|
}
|
|
|
|
//查询行业类型
|
|
private string GetHYTYPE(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
FangYar.Model.LoginUserModel user = FangYar.WebUI.WebCommon.HttpUtil.GetUser(context);
|
|
|
|
|
|
if (user != null)
|
|
{
|
|
DataTable list = (DataTable)FangYar.Common.CacheHelper.GetCache("HYTYPE");
|
|
if (list == null)
|
|
{
|
|
list = bll.GetTableList("*", "tbl_sys_dicdetail", " MOD_CODE ='HYTYPE'");
|
|
FangYar.Common.CacheHelper.SetCache("HYTYPE", list);
|
|
}
|
|
string where = " DEV_CODE = '" + user.OrgID + "' ";
|
|
where += " and RECORD_STATE ='0' ";
|
|
|
|
returnstr = "{\"code\":1,\"msg\":\"\",";
|
|
returnstr += "\"data\":{";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(list);
|
|
returnstr += "}";
|
|
returnstr += "}";
|
|
}
|
|
else { returnstr = "{\"code\":-2,\"msg\":\"登录超时\",\"count\":0,\"data\":[]}"; }
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "公共查询操作请求", "查询行业类型异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询行业类型");
|
|
return returnstr;
|
|
}
|
|
|
|
//查询字典列表
|
|
private string getDicList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string modtype = context.Request.Params["modtype"];
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
DataTable data = bll.GetTableList("*", "tbl_sys_dicdetail", " MOD_CODE ='" + modtype + "' ORDER BY DIC_ORDER");
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "公共查询操作请求", "查询字典列表异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询字典列表");
|
|
return returnstr;
|
|
}
|
|
|
|
//查询多类型字典列表
|
|
private string getModsDicList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string modtypes = context.Request.Params["modtypes"];
|
|
|
|
string[] modtypeArray = modtypes.Split(',');
|
|
string modtype_s = "";
|
|
for (int i = 0; i < modtypeArray.Length; i++)
|
|
{
|
|
if (i == 0)
|
|
{
|
|
modtype_s = "'" + modtypeArray[i] + "'";
|
|
}
|
|
else
|
|
{
|
|
modtype_s += ",'" + modtypeArray[i] + "'";
|
|
}
|
|
}
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"data\":";
|
|
DataTable data = bll.GetTableList("*", "tbl_sys_dicdetail", " MOD_CODE in(" + modtype_s + ") order by MOD_CODE , cast(DIC_ORDER as signed int)");
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询多类型字典列表异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "查询多类型字典列表");
|
|
return returnstr;
|
|
}
|
|
|
|
//图片上传
|
|
private string GetUploadImg(HttpContext context)
|
|
{
|
|
string end = "{\"code\": -1,\"msg\": \"\",\"data\": {\"src\": \"\"}}"; //返回的json
|
|
|
|
var file = context.Request.Files[0]; //获取选中文件
|
|
string serverPath = context.Request.Params["fileUrl"];
|
|
|
|
Stream stream = file.InputStream; //将文件转为流
|
|
|
|
System.Drawing.Image img = Image.FromStream(stream);//将流中的图片转换为Image图片对象
|
|
|
|
Random ran = new Random((int)DateTime.Now.Ticks);//利用时间种子解决伪随机数短时间重复问题
|
|
if (!Directory.Exists(context.Server.MapPath("~/" + serverPath)))
|
|
{
|
|
DirectoryInfo directoryInfo = new DirectoryInfo(context.Server.MapPath("~/" + serverPath));
|
|
directoryInfo.Create();
|
|
}
|
|
|
|
//文件保存位置及命名，精确到毫秒并附带一组随机数，防止文件重名，数据库保存路径为此变量
|
|
//string serverPath = "Upload/Device/";
|
|
string filename = serverPath + DateTime.Now.ToString("yyyyMMddhhmmssms") + ran.Next(99999) + ".jpg";
|
|
//路径映射为绝对路径
|
|
string path = context.Server.MapPath("~/") + filename;
|
|
try
|
|
{
|
|
img.Save(path);//图片保存，JPEG格式图片较小
|
|
|
|
//保存成功后的json
|
|
end = "{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"" + filename + "\"}}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "公共查询操作请求", "图片上传异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "图片上传");
|
|
return end;
|
|
}
|
|
|
|
//根据入库登录名，更新登录名获取相应 姓名
|
|
private string GetUserName(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string A_PER = context.Request.Params["A_PER"];
|
|
string U_PER = context.Request.Params["U_PER"];
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"Data\":";
|
|
DataTable data = bll.getUserName(A_PER, U_PER);
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "公共查询操作请求", "根据入库登录名，更新登录名获取相应 姓名异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "根据入库登录名，更新登录名获取相应 姓名");
|
|
return returnstr;
|
|
}
|
|
|
|
//获取首页综合总计数据
|
|
private string GetHomeCount(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string ORG_ID = context.Request.Params["ORG_ID"];
|
|
returnstr = "{\"code\":0,\"msg\":\"\",\"Data\":";
|
|
string where = "";
|
|
if (!string.IsNullOrEmpty(ORG_ID))
|
|
{
|
|
where += " and (ORG_ID ='" + ORG_ID + "' or ORG_ID in (select o.org_id from fire_org o where o.pid = '" + ORG_ID + "') )";
|
|
}
|
|
DataTable data = bll.getHomeCount(where);
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(data);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "获取首页综合总计数据异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "获取首页综合总计数据");
|
|
return returnstr;
|
|
}
|
|
//
|
|
private string UploadFiles(HttpContext context)
|
|
{
|
|
int code = -1;
|
|
string msg = "";
|
|
string URL = null;
|
|
string uploadpath = null;
|
|
string filename = null;
|
|
string originalName = null;
|
|
HttpPostedFile uploadFile = null;
|
|
|
|
string serverPath = context.Request.Params["fileUrl"];
|
|
serverPath = serverPath + "/" + DateTime.Now.ToString("yyyy-MM-dd") + "/";
|
|
if (!Directory.Exists(context.Server.MapPath("~/" + serverPath)))
|
|
{
|
|
DirectoryInfo directoryInfo = new DirectoryInfo(context.Server.MapPath("~/" + serverPath));
|
|
directoryInfo.Create();
|
|
}
|
|
uploadpath = context.Server.MapPath("~/" + serverPath);
|
|
|
|
try
|
|
{
|
|
|
|
uploadFile = context.Request.Files[0];
|
|
originalName = uploadFile.FileName.Split('.')[0];
|
|
filename = originalName + "(" + System.Guid.NewGuid() + ")" + getFileExt(uploadFile);
|
|
uploadFile.SaveAs(uploadpath + filename);
|
|
URL = serverPath + filename;
|
|
msg = "上传成功";
|
|
code = 1;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
code = -1;
|
|
msg = "未知错误";
|
|
URL = "";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "上传文件异常：" + e);
|
|
}
|
|
string returnstr = "{\"code\":" + code + ",\"msg\":\"" + msg + "\",\"url\":\"" + URL + "\"}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "公共查询操作请求", "上传文件");
|
|
|
|
return returnstr;
|
|
|
|
}
|
|
private bool checkSize(HttpPostedFile uploadFile, int size)
|
|
{
|
|
return uploadFile.ContentLength >= (size * 1024 * 1024000);
|
|
}
|
|
|
|
//获取文件扩展名
|
|
private string getFileExt(HttpPostedFile uploadFile)
|
|
{
|
|
string[] temp = uploadFile.FileName.Split('.');
|
|
return "." + temp[temp.Length - 1].ToLower();
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 上传车载装备文件
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string carEquipFileUpload(HttpContext context)
|
|
{
|
|
string returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
try
|
|
{
|
|
var id = context.Request.Params["id"] + "";
|
|
|
|
var file = context.Request.Files[0]; //获取选中文件
|
|
Stream stream = file.InputStream; //将文件转为流
|
|
|
|
|
|
|
|
////获取上传的文件数据
|
|
//var file = context.Request.files["filedata"];
|
|
string filename = file.FileName;
|
|
string filetype = Path.GetExtension(filename).ToLower();
|
|
|
|
//上传的目录
|
|
string uploaddir = "~/File/CarEquip/" + DateTime.Now.ToString("yyyymm") + "/";
|
|
//上传的路径
|
|
//生成年月文件夹及日文件夹
|
|
if (Directory.Exists(context.Server.MapPath(uploaddir)) == false)
|
|
{
|
|
Directory.CreateDirectory(context.Server.MapPath(uploaddir));
|
|
}
|
|
|
|
var guid = Guid.NewGuid().ToString("N");
|
|
|
|
string uploadpath = uploaddir + guid + filetype;
|
|
|
|
//保存文件
|
|
file.SaveAs(context.Server.MapPath(uploadpath));
|
|
|
|
string dbFileUlr = uploadpath.Replace("~", "");
|
|
|
|
string fName = filename.Substring(0, filename.LastIndexOf('.'));
|
|
|
|
string insSql = " insert into tbl_sys_car_equip (id,car_id,File_Url,File_Name) VALUES ('" + guid + "','" + id + "','" + dbFileUlr + "','" + fName + "') ";
|
|
|
|
var n = FangYar.Common.MySqlHelper.Execute(insSql);
|
|
|
|
if (n > 0)
|
|
{
|
|
returnstr = "{\"code\":1,\"msg\":\"成功\",\"count\":0,\"data\":[]}";
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "车辆信息操作请求", "上传车辆装备异常：" + ex);
|
|
returnstr = "{\"code\":-1,\"msg\":\"处理异常\",\"count\":0,\"data\":[]}";
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Add, "车辆信息操作请求", "上传车辆装备");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 上传文件返回文件路径和文件名称
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string FileUpload(HttpContext context)
|
|
{
|
|
string returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
try
|
|
{
|
|
|
|
var file = context.Request.Files[0]; //获取选中文件
|
|
Stream stream = file.InputStream; //将文件转为流
|
|
|
|
|
|
////获取上传的文件数据
|
|
//var file = context.Request.files["filedata"];
|
|
string filename = file.FileName;
|
|
string filetype = Path.GetExtension(filename).ToLower();
|
|
|
|
//上传的目录
|
|
string uploaddir = "~/File/Knowledge/" + DateTime.Now.ToString("yyyymm") + "/";
|
|
//上传的路径
|
|
//生成年月文件夹及日文件夹
|
|
if (Directory.Exists(context.Server.MapPath(uploaddir)) == false)
|
|
{
|
|
Directory.CreateDirectory(context.Server.MapPath(uploaddir));
|
|
}
|
|
|
|
var guid = Guid.NewGuid().ToString("N");
|
|
|
|
string uploadpath = uploaddir + guid + filetype;
|
|
|
|
//保存文件
|
|
file.SaveAs(context.Server.MapPath(uploadpath));
|
|
|
|
//保存文件路径
|
|
string dbFileUlr = uploadpath.Replace("~", "");
|
|
|
|
//保存文件名称
|
|
string fName = filename.Substring(0, filename.LastIndexOf('.'));
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":{\"fName\":\"" + fName + "\",\"fPath\":\"" + dbFileUlr + "\"}}";
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "车辆信息操作请求", "上传车辆装备异常：" + ex);
|
|
returnstr = "{\"code\":-1,\"msg\":\"处理异常\",\"count\":0,\"data\":[]}";
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Add, "车辆信息操作请求", "上传车辆装备");
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
|
|
public bool IsReusable
|
|
{
|
|
get
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|