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.
1054 lines
50 KiB
1054 lines
50 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using System.Data;
|
|
using System.Text;
|
|
using System.Collections;
|
|
using System.Reflection;
|
|
using System.Web.Script.Serialization;
|
|
using FangYar.Model;
|
|
using FangYar.BLL;
|
|
using Newtonsoft.Json;
|
|
using Spire.Doc;
|
|
using Spire.Doc.Documents;
|
|
using System.Drawing;
|
|
using System.IO;
|
|
using Spire.Doc.Fields;
|
|
using FangYar.Common;
|
|
using System.Data.Odbc;
|
|
using System.Text.RegularExpressions;
|
|
|
|
namespace FangYar.WebUI.ashx
|
|
{
|
|
/// <summary>
|
|
/// OaLeaveHandler 的摘要说明
|
|
/// </summary>
|
|
public class VisitorHandler : IHttpHandler
|
|
{
|
|
private FangYar.BLL.TBL_VISITOR bll = new FangYar.BLL.TBL_VISITOR();
|
|
public void ProcessRequest(HttpContext context)
|
|
{
|
|
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Other, "访客操作请求", "");
|
|
|
|
context.Response.ContentType = "text/plain";
|
|
string action = context.Request.Params["Action"];
|
|
string returnstr = "";
|
|
|
|
switch (action)
|
|
{
|
|
case "List":
|
|
returnstr = visList(context);
|
|
break;
|
|
case "List2":
|
|
returnstr = visList2(context);
|
|
break;
|
|
case "editWait":
|
|
returnstr = editWait(context);
|
|
break;
|
|
case "GetVisitorWeek":
|
|
returnstr = GetVisitorWeek(context);
|
|
break;
|
|
case "GetInfo":
|
|
returnstr = GetInfo(context);
|
|
break;
|
|
case "expList":
|
|
expList(context);
|
|
break;
|
|
case "visitorReportDetail"://统计报表
|
|
returnstr = VisitorReportDetail(context);
|
|
break;
|
|
case "visitorReportBar"://柱状图
|
|
returnstr = VisitorReportBar(context);
|
|
break;
|
|
case "htmlToWord":
|
|
returnstr = HtmlToWord(context);
|
|
break;
|
|
case "getVisitorList"://
|
|
returnstr = getVisitorList(context);
|
|
break;
|
|
case "getVisitorTrajectoryList"://
|
|
returnstr = getVisitorTrajectoryList(context);
|
|
break;
|
|
case "getStatisticsCarPerVisCamByTime":
|
|
returnstr = getStatisticsCarPerVisCamByTime(context);
|
|
break;
|
|
}
|
|
context.Response.Write(returnstr);
|
|
}
|
|
|
|
|
|
//查询
|
|
private string visList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
|
|
//searchTime searchIDCard searchName searchVedName
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
//判断是否APP或者小程序访问
|
|
if (Common.WebCommonUtil.IsPhoneRequest(context))
|
|
{
|
|
//增加上级转换机关机构数据转换
|
|
OrgId = Common.WebCommonUtil.GetOrgIdUpLevelToDownLevel(OrgId);
|
|
}
|
|
string startTime = context.Request.Params["startTime"];
|
|
string endTime = context.Request.Params["endTime"];
|
|
string searchIDCard = context.Request.Params["searchIDCard"];
|
|
string searchName = context.Request.Params["searchName"];
|
|
string searchVedName = context.Request.Params["searchVedName"];
|
|
string treeID = context.Request.Params["treeID"];
|
|
string page = context.Request.Params["page"];
|
|
string limit = context.Request.Params["limit"];
|
|
string selDeptId = context.Request.Params["selDeptId"];
|
|
//登录账号所属部门
|
|
string DeptID = context.Request.Params["DeptID"] + "";
|
|
//是否管理员账号
|
|
string IsAdmin = context.Request.Params["IsAdmin"] + "";
|
|
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 = " 1=1 ";
|
|
|
|
if (!string.IsNullOrEmpty(treeID) && treeID != OrgId)
|
|
{
|
|
OrgId = treeID;
|
|
}
|
|
//where = " ORG_ID = '" + OrgId + "'";
|
|
if (is_content == "1")
|
|
{
|
|
where += " and ( find_in_set(org_id, cids) ) ";
|
|
}
|
|
else
|
|
{
|
|
where += " and ( ORG_ID = '" + OrgId + "' )";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(selDeptId))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + selDeptId + "' ) ";
|
|
}
|
|
|
|
//判断是否管理员账号，如果不是管理员则只查询本部门数据
|
|
if (IsAdmin != "1")
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(DeptID))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + DeptID + "' ) ";
|
|
}
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrEmpty(startTime))
|
|
{
|
|
where += " and date_format(INTIME,'%Y-%m-%d') >=date_format('" + startTime + "','%Y-%m-%d')";
|
|
where += " and date_format(INTIME,'%Y-%m-%d') <=date_format('" + endTime + "','%Y-%m-%d')";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchIDCard))
|
|
{
|
|
where += " and EXTEND2 like '%" + searchIDCard + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchName))
|
|
{
|
|
where += " and NAME like '%" + searchName + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchVedName))
|
|
{
|
|
where += " and VED_MAN like '%" + searchVedName + "%'";
|
|
}
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
int count = bll.GetRecordCount(where, OrgId);
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
//访客出入记录表
|
|
string data = "[";
|
|
List<FangYar.Model.TBL_VISITOR> list = bll.QueryList(pageIndex, pageSize, where, " intime desc ", OrgId);
|
|
|
|
for (int i = 0; i < list.Count; i++)
|
|
{
|
|
string pic2 = "";
|
|
if (list[i].V_PIC != null && list[i].V_PIC.ToString() != "")
|
|
{
|
|
pic2 = Convert.ToBase64String((byte[])list[i].V_PIC);
|
|
}
|
|
data += "{\"ID\":\"" + list[i].ID + "\",\"NAME\":\"" + list[i].NAME + "\",\"SEX\":\"" + list[i].SEX + "\",\"AGE\":\"" + list[i].AGE + "\",\"UNIT\":\"" + list[i].UNIT + "\",\"NATION\":\"" + list[i].NATION + "\",\"ADDR\":\"" + list[i].ADDR + "\",\"H_REG\":\"" + list[i].H_REG + "\",";
|
|
data += "\"ID_NUM\":\"" + list[i].ID_NUM + "\",\"ORG_ID\":\"" + list[i].ORG_ID + "\",\"M_ID\":\"" + list[i].M_ID + "\",\"M_ADDR\":\"" + list[i].M_ADDR + "\",\"VED_MAN\":\"" + list[i].VED_MAN + "\",";
|
|
data += "\"VED_ID\":\"" + list[i].VED_ID + "\",\"REASON\":\"" + list[i].REASON + "\",\"GOODS\":\"" + list[i].GOODS + "\",";
|
|
data += "\"DRICAR\":\"" + list[i].DRICAR + "\",\"TRAVELNUM\":\"" + list[i].TRAVELNUM + "\",\"ABOUTTIME\":\"" + list[i].ABOUTTIME + "\",\"REGISTERTIME\":\"" + list[i].REGISTERTIME + "\",";
|
|
data += "\"INTIME\":\"" + list[i].INTIME + "\",\"OUTTIME\":\"" + list[i].OUTTIME + "\",\"ACTUALTIME\":\"" + list[i].ACTUALTIME + "\",\"STATE\":\"" + list[i].STATE + "\",";
|
|
data += "\"APPROVALTIME\":\"" + list[i].APPROVALTIME + "\",\"QRINFO\":\"" + list[i].QRINFO + "\",\"INMACHINEID\":\"" + list[i].INMACHINEID + "\",\"INADDR\":\"" + list[i].INADDR + "\",";
|
|
data += "\"OUTMACHINEID\":\"" + list[i].OUTMACHINEID + "\",\"OUTADDR\":\"" + list[i].OUTADDR + "\",";
|
|
data += "\"EXTEND1\":\"" + list[i].EXTEND1 + "\",\"EXTEND2\":\"" + list[i].EXTEND2 + "\",\"EXTEND3\":\"" + list[i].EXTEND3 + "\",\"EXTEND4\":\"" + list[i].EXTEND4 + "\",";
|
|
data += "\"VED_DEPT\":\"" + list[i].VED_DEPT + "\",\"VIS_START_DATA\":\"" + list[i].VIS_START_DATA + "\",\"VIS_START_TIME\":\"" + list[i].VIS_START_TIME + "\",\"PHONE\":\"" + list[i].PHONE + "\",\"FEEDBACK\":\"" + list[i].FEEDBACK + "\",\"VIS_END_DATA\":\"" + list[i].VIS_END_DATA + "\",";
|
|
data += "\"VIS_END_TIME\":\"" + list[i].VIS_END_TIME + "\",\"BIRTHDAY\":\"" + list[i].BIRTHDAY + "\",\"VIS_START_TIME\":\"" + list[i].VIS_START_TIME + "\",\"SIGNOFFICE\":\"" + list[i].SIGNOFFICE + "\",\"LEGALDATE\":\"" + list[i].LEGALDATE + "\",";
|
|
data += "\"V_PIC\":\"" + pic2 + "\",\"INPIC_URL\":\"" + list[i].INPIC_URL + "\"}";
|
|
if (i != list.Count - 1)
|
|
{
|
|
data += ",";
|
|
}
|
|
}
|
|
returnstr += 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 visList2(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
|
|
//searchTime searchIDCard searchName searchVedName
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
string searchTime = context.Request.Params["searchTime"];
|
|
string searchIDCard = context.Request.Params["searchIDCard"];
|
|
string searchName = context.Request.Params["searchName"];
|
|
string searchVedName = context.Request.Params["searchVedName"];
|
|
string treeID = context.Request.Params["treeID"];
|
|
string page = context.Request.Params["page"];
|
|
string limit = context.Request.Params["limit"];
|
|
string selDeptId = context.Request.Params["selDeptId"];
|
|
//登录账号所属部门
|
|
string DeptID = context.Request.Params["DeptID"] + "";
|
|
//是否管理员账号
|
|
string IsAdmin = context.Request.Params["IsAdmin"] + "";
|
|
int pageIndex = 1;
|
|
int pageSize = 10;
|
|
if (!string.IsNullOrEmpty(page)) { pageIndex = int.Parse(page); }
|
|
if (!string.IsNullOrEmpty(limit)) { pageSize = int.Parse(limit); }
|
|
string where = " 1=1 ";
|
|
if (!string.IsNullOrEmpty(treeID) && treeID != OrgId)
|
|
{
|
|
where = " ORG_ID ='" + treeID + "'";
|
|
}
|
|
else
|
|
{
|
|
where = " ORG_ID ='" + OrgId + "'";
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(selDeptId))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + selDeptId + "' ) ";
|
|
}
|
|
|
|
//判断是否管理员账号，如果不是管理员则只查询本部门数据
|
|
if (IsAdmin != "1")
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(DeptID))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + DeptID + "' ) ";
|
|
}
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrEmpty(searchTime))
|
|
{
|
|
string startTime = "", endTime = "";
|
|
if (searchTime.IndexOf(" - ") != -1)
|
|
{
|
|
startTime = Regex.Split(searchTime, " - ", RegexOptions.IgnoreCase)[0];
|
|
endTime = Regex.Split(searchTime, " - ", RegexOptions.IgnoreCase)[1];
|
|
}
|
|
where += " and date_format(INTIME,'%Y-%m-%d') between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d')";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchIDCard))
|
|
{
|
|
where += " and EXTEND2 like '%" + searchIDCard + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchName))
|
|
{
|
|
where += " and NAME like '%" + searchName + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(searchVedName))
|
|
{
|
|
where += " and VED_MAN like '%" + searchVedName + "%'";
|
|
}
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
int count = bll.GetRecordCount(where);
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
//访客出入记录表
|
|
string data = "[";
|
|
List<FangYar.Model.TBL_VISITOR> list = bll.QueryList(pageIndex, pageSize, where, " intime desc ");
|
|
|
|
for (int i = 0; i < list.Count; i++)
|
|
{
|
|
string pic2 = "";
|
|
if (list[i].V_PIC != null && list[i].V_PIC.ToString() != "")
|
|
{
|
|
pic2 = Convert.ToBase64String((byte[])list[i].V_PIC);
|
|
}
|
|
data += "{\"ID\":\"" + list[i].ID + "\",\"NAME\":\"" + list[i].NAME + "\",\"SEX\":\"" + list[i].SEX + "\",\"AGE\":\"" + list[i].AGE + "\",\"UNIT\":\"" + list[i].UNIT + "\",\"NATION\":\"" + list[i].NATION + "\",\"ADDR\":\"" + list[i].ADDR + "\",\"H_REG\":\"" + list[i].H_REG + "\",";
|
|
data += "\"ID_NUM\":\"" + list[i].ID_NUM + "\",\"ORG_ID\":\"" + list[i].ORG_ID + "\",\"M_ID\":\"" + list[i].M_ID + "\",\"M_ADDR\":\"" + list[i].M_ADDR + "\",\"VED_MAN\":\"" + list[i].VED_MAN + "\",";
|
|
data += "\"VED_ID\":\"" + list[i].VED_ID + "\",\"REASON\":\"" + list[i].REASON + "\",\"GOODS\":\"" + list[i].GOODS + "\",";
|
|
data += "\"DRICAR\":\"" + list[i].DRICAR + "\",\"TRAVELNUM\":\"" + list[i].TRAVELNUM + "\",\"ABOUTTIME\":\"" + list[i].ABOUTTIME + "\",\"REGISTERTIME\":\"" + list[i].REGISTERTIME + "\",";
|
|
data += "\"INTIME\":\"" + list[i].INTIME + "\",\"OUTTIME\":\"" + list[i].OUTTIME + "\",\"ACTUALTIME\":\"" + list[i].ACTUALTIME + "\",\"STATE\":\"" + list[i].STATE + "\",";
|
|
data += "\"APPROVALTIME\":\"" + list[i].APPROVALTIME + "\",\"QRINFO\":\"" + list[i].QRINFO + "\",\"INMACHINEID\":\"" + list[i].INMACHINEID + "\",\"INADDR\":\"" + list[i].INADDR + "\",";
|
|
data += "\"OUTMACHINEID\":\"" + list[i].OUTMACHINEID + "\",\"OUTADDR\":\"" + list[i].OUTADDR + "\",";
|
|
data += "\"EXTEND1\":\"" + list[i].EXTEND1 + "\",\"EXTEND2\":\"" + list[i].EXTEND2 + "\",\"EXTEND3\":\"" + list[i].EXTEND3 + "\",\"EXTEND4\":\"" + list[i].EXTEND4 + "\",";
|
|
data += "\"VED_DEPT\":\"" + list[i].VED_DEPT + "\",\"VIS_START_DATA\":\"" + list[i].VIS_START_DATA + "\",\"VIS_START_TIME\":\"" + list[i].VIS_START_TIME + "\",\"PHONE\":\"" + list[i].PHONE + "\",\"FEEDBACK\":\"" + list[i].FEEDBACK + "\",\"VIS_END_DATA\":\"" + list[i].VIS_END_DATA + "\",";
|
|
data += "\"VIS_END_TIME\":\"" + list[i].VIS_END_TIME + "\",\"BIRTHDAY\":\"" + list[i].BIRTHDAY + "\",\"VIS_START_TIME\":\"" + list[i].VIS_START_TIME + "\",\"SIGNOFFICE\":\"" + list[i].SIGNOFFICE + "\",\"LEGALDATE\":\"" + list[i].LEGALDATE + "\",";
|
|
data += "\"V_PIC\":\"" + pic2 + "\",\"INPIC_URL\":\"" + list[i].INPIC_URL + "\"}";
|
|
if (i != list.Count - 1)
|
|
{
|
|
data += ",";
|
|
}
|
|
}
|
|
returnstr += 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 editWait(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
int code = -1;
|
|
string msg = "";
|
|
try
|
|
{
|
|
string taskId = context.Request.Params["taskId"];
|
|
string waitId = context.Request.Params["waitId"];
|
|
string state = context.Request.Params["state"];
|
|
string sort = context.Request.Params["sort"];
|
|
string aOpinion = context.Request.Params["a_opinion"];
|
|
string isOver = context.Request.Params["isOver"];
|
|
|
|
FangYar.Model.OA_WAITPROCESSED model = new Model.OA_WAITPROCESSED();
|
|
model.ID = waitId;
|
|
model.TASK_ID = taskId;
|
|
model.STATE = state;
|
|
model.SORT = Convert.ToInt32(sort);
|
|
model.A_OPINION = aOpinion;
|
|
if (bll.editWait(model, isOver))
|
|
{
|
|
msg = "审批成功！";
|
|
code = 1;
|
|
}
|
|
else { msg = "审批失败！"; }
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
msg = "审批失败！";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "访客操作请求", "访客审批异常：" + e);
|
|
}
|
|
returnstr = "{\"msg\":\"" + msg + "\",\"code\":" + code + "}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Update, "访客操作请求", "访客审批");
|
|
return returnstr;
|
|
}
|
|
|
|
//获取访客数量趋势
|
|
private string GetVisitorWeek(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
returnstr = "{\"code\":1,\"msg\":\"\",";
|
|
returnstr += "\"data\":";
|
|
string ORG_ID = context.Request.Params["ORG_ID"];
|
|
string where = "1=1";
|
|
where += " and (ORG_ID ='" + ORG_ID + "' or ORG_ID in (select o.org_id from fire_org o where o.pid = '" + ORG_ID + "') )";
|
|
returnstr += JsonConvert.SerializeObject(bll.TotalDaysAlarm(7, where));
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"\"}";
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "访客操作请求", "获取访客数量趋势异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "访客操作请求", "获取访客数量趋势");
|
|
return returnstr;
|
|
}
|
|
private string GetInfo(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
|
|
//searchTime searchIDCard searchName searchVedName
|
|
string taskId = context.Request.Params["taskId"];
|
|
|
|
if (!string.IsNullOrEmpty(taskId))
|
|
{ }
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
|
|
FangYar.Model.TBL_VISITOR visModel = bll.QueryList(1, 1, " ID = '" + taskId + "'", " intime desc ")[0];
|
|
returnstr += "\"count\":1,\"data\":";
|
|
|
|
//访客出入记录表
|
|
string data = "[";
|
|
|
|
string pic2 = "";
|
|
|
|
if (visModel.V_PIC != null && visModel.V_PIC.ToString() != "")
|
|
{
|
|
pic2 = Convert.ToBase64String((byte[])visModel.V_PIC);
|
|
}
|
|
data += "{\"ID\":\"" + visModel.ID + "\",\"NAME\":\"" + visModel.NAME + "\",\"SEX\":\"" + visModel.SEX + "\",\"AGE\":\"" + visModel.AGE + "\",\"UNIT\":\"" + visModel.UNIT + "\",\"NATION\":\"" + visModel.NATION + "\",\"ADDR\":\"" + visModel.ADDR + "\",\"H_REG\":\"" + visModel.H_REG + "\",";
|
|
data += "\"ID_NUM\":\"" + visModel.ID_NUM + "\",\"ORG_ID\":\"" + visModel.ORG_ID + "\",\"M_ID\":\"" + visModel.M_ID + "\",\"M_ADDR\":\"" + visModel.M_ADDR + "\",\"VED_MAN\":\"" + visModel.VED_MAN + "\",";
|
|
data += "\"VED_ID\":\"" + visModel.VED_ID + "\",\"REASON\":\"" + visModel.REASON + "\",\"GOODS\":\"" + visModel.GOODS + "\",";
|
|
data += "\"DRICAR\":\"" + visModel.DRICAR + "\",\"TRAVELNUM\":\"" + visModel.TRAVELNUM + "\",\"ABOUTTIME\":\"" + visModel.ABOUTTIME + "\",\"REGISTERTIME\":\"" + visModel.REGISTERTIME + "\",";
|
|
data += "\"INTIME\":\"" + visModel.INTIME + "\",\"OUTTIME\":\"" + visModel.OUTTIME + "\",\"ACTUALTIME\":\"" + visModel.ACTUALTIME + "\",\"STATE\":\"" + visModel.STATE + "\",";
|
|
data += "\"APPROVALTIME\":\"" + visModel.APPROVALTIME + "\",\"QRINFO\":\"" + visModel.QRINFO + "\",\"INMACHINEID\":\"" + visModel.INMACHINEID + "\",\"INADDR\":\"" + visModel.INADDR + "\",";
|
|
data += "\"OUTMACHINEID\":\"" + visModel.OUTMACHINEID + "\",\"OUTADDR\":\"" + visModel.OUTADDR + "\",";
|
|
data += "\"EXTEND1\":\"" + visModel.EXTEND1 + "\",\"EXTEND2\":\"" + visModel.EXTEND2 + "\",\"EXTEND3\":\"" + visModel.EXTEND3 + "\",\"EXTEND4\":\"" + visModel.EXTEND4 + "\",";
|
|
data += "\"VED_DEPT\":\"" + visModel.VED_DEPT + "\",\"VIS_START_DATA\":\"" + visModel.VIS_START_DATA + "\",\"VIS_START_TIME\":\"" + visModel.VIS_START_TIME + "\",\"PHONE\":\"" + visModel.PHONE + "\",\"FEEDBACK\":\"" + visModel.FEEDBACK + "\",\"VIS_END_DATA\":\"" + visModel.VIS_END_DATA + "\",";
|
|
data += "\"VIS_END_TIME\":\"" + visModel.VIS_END_TIME + "\",\"BIRTHDAY\":\"" + visModel.BIRTHDAY + "\",\"VIS_START_TIME\":\"" + visModel.VIS_START_TIME + "\",\"SIGNOFFICE\":\"" + visModel.SIGNOFFICE + "\",\"LEGALDATE\":\"" + visModel.LEGALDATE + "\",";
|
|
data += "\"V_PIC\":\"" + pic2 + "\",\"INPIC_URL\":\"" + visModel.INPIC_URL + "\",\"DEP_NAME\":\"" + visModel.DEP_NAME + "\"}";
|
|
|
|
returnstr += 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 void expList(HttpContext context)
|
|
{
|
|
try
|
|
{
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
string startTime = context.Request.Params["startTime"];
|
|
string endTime = context.Request.Params["endTime"];
|
|
string searchName = context.Request.Params["searchName"];
|
|
string treeId = context.Request.Params["treeId"];
|
|
string DeptID = context.Request.Params["DeptID"];
|
|
string IsAdmin = context.Request.Params["IsAdmin"];
|
|
string selDeptId = context.Request.Params["selDeptId"];
|
|
string is_content = context.Request.Params["is_content"];
|
|
string where = " 1=1 ";
|
|
|
|
if (!string.IsNullOrEmpty(treeId) && treeId != OrgId)
|
|
{
|
|
OrgId = treeId;
|
|
}
|
|
//where = " ORG_ID = '" + OrgId + "'";
|
|
if (is_content == "1")
|
|
{
|
|
where += " and ( find_in_set(org_id, cids) ) ";
|
|
}
|
|
else
|
|
{
|
|
where += " and ( ORG_ID = '" + OrgId + "' )";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(selDeptId))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + selDeptId + "' ) ";
|
|
}
|
|
|
|
//判断是否管理员账号，如果不是管理员则只查询本部门数据
|
|
if (IsAdmin != "1")
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(DeptID))
|
|
{
|
|
where += " and VED_ID in ( SELECT users_uid from tbl_sys_emp WHERE ORG_ID='" + OrgId + "' and DEPT_ID='" + DeptID + "' ) ";
|
|
}
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(startTime))
|
|
{
|
|
where += " and date_format(INTIME,'%Y-%m-%d') >=date_format('" + startTime + "','%Y-%m-%d')";
|
|
where += " and date_format(INTIME,'%Y-%m-%d') <=date_format('" + endTime + "','%Y-%m-%d')";
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(searchName))
|
|
{
|
|
where += " and NAME like '%" + searchName + "%'";
|
|
}
|
|
|
|
int count = bll.GetRecordCount(where, OrgId);
|
|
if (count == 0)
|
|
{
|
|
}
|
|
else
|
|
{
|
|
//访客出入记录表
|
|
DataTable dt = bll.expList(1, count, where, " intime desc ", OrgId);
|
|
System.Collections.Generic.Dictionary<string, string> cellHeaders = new System.Collections.Generic.Dictionary<string, string>();
|
|
|
|
cellHeaders.Add("NAME", "姓名");
|
|
cellHeaders.Add("SEX", "性别");
|
|
cellHeaders.Add("AGE", "年龄");
|
|
cellHeaders.Add("UNIT", "单位");
|
|
cellHeaders.Add("NATION", "民族");
|
|
|
|
cellHeaders.Add("ADDR", "住址");
|
|
cellHeaders.Add("H_REG", "户籍");
|
|
cellHeaders.Add("ID_NUM", "身份证号");
|
|
cellHeaders.Add("ORG_NAME", "机构");
|
|
cellHeaders.Add("M_ADDR", "设备地址");
|
|
|
|
cellHeaders.Add("VED_MAN", "被访问人员姓名");
|
|
cellHeaders.Add("REASON", "访问事由");
|
|
cellHeaders.Add("GOODS", "携带重要物品");
|
|
cellHeaders.Add("DRICAR", "驾驶车辆牌照");
|
|
cellHeaders.Add("TRAVELNUM", "访客人数");
|
|
|
|
cellHeaders.Add("ABOUTTIME", "大约访问时长");
|
|
cellHeaders.Add("REGISTERTIME", "登记时间");
|
|
cellHeaders.Add("INTIME", "通过时间");
|
|
//cellHeaders.Add("OUTTIME", "离开时间");
|
|
cellHeaders.Add("ACTUALTIME", "实际访问时间");
|
|
|
|
cellHeaders.Add("STATE", "访客状态");
|
|
cellHeaders.Add("APPROVALTIME", "审批时间");
|
|
cellHeaders.Add("INADDR", "进入门禁卡机位置");
|
|
cellHeaders.Add("OUTMACHINEID", "离开门禁卡机");
|
|
cellHeaders.Add("OUTADDR", "离开门禁卡机位置");
|
|
|
|
cellHeaders.Add("EXTEND2", "身份证号");
|
|
cellHeaders.Add("VISIN_NAME", "来访人姓名");
|
|
cellHeaders.Add("VED_DEPT", "被访问部门");
|
|
cellHeaders.Add("VIS_START_TIME", "来访时间");
|
|
cellHeaders.Add("PHONE", "电话");
|
|
|
|
cellHeaders.Add("FEEDBACK", "反馈信息");
|
|
cellHeaders.Add("VIS_END_TIME", "结束时间");
|
|
cellHeaders.Add("BIRTHDAY", "出生日期");
|
|
cellHeaders.Add("SIGNOFFICE", "签发机关");
|
|
cellHeaders.Add("LEGALDATE", "有效期限");
|
|
|
|
FangYar.Common.NPOIExcelHelper.ExportByWeb(dt, null, cellHeaders, DateTime.Now.ToString("yyyyMMddHHmmss") + "访客出入表.xls");
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Error, "访客操作请求", "导出异常：" + e);
|
|
}
|
|
// 记录操作日志
|
|
BLL.SysOperationLogHelp.AddSysOperationLog(context, Common.EnumOperationLogType.Query, "访客操作请求", "导出");
|
|
}
|
|
public bool IsReusable
|
|
{
|
|
get
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
|
|
#region 统计报表详情
|
|
|
|
/// <summary>
|
|
/// 统计报表详情
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
public string VisitorReportDetail(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string orgId = context.Request.Params["orgId"];
|
|
string startDate = context.Request.Params["startDate"];
|
|
string endDate = context.Request.Params["endDate"];
|
|
string isContainBelow = context.Request.Params["iscontain"];
|
|
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); }
|
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
if (isContainBelow == "1")
|
|
{
|
|
strSql.AppendFormatWithSafe("org_id in (select o.org_id from fire_org o,(select get_Org_child_list('" + orgId + "') cids ) s where o.type = '0' and find_in_set(org_id,cids) )", orgId, strSql);
|
|
}
|
|
else
|
|
{
|
|
strSql.AppendFormatWithSafe("org_id = '{0}'", orgId, strSql);
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(startDate))
|
|
{
|
|
strSql.AppendFormatWithSafe(" and date_format(INTIME, '%Y-%m-%d') >= date_format('{0}-01', '%Y-%m-%d') ", startDate, strSql);
|
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
|
{
|
|
strSql.AppendFormatWithSafe(" and date_format(INTIME, '%Y-%m-%d') < date_format('{0}-01', '%Y-%m-%d') ", endDate, strSql);
|
|
}
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
strSql.Append(" order by INTIME desc ");
|
|
int count = bll.GetRecordCount(strSql.ToString());
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
List<Model.TBL_VISITOR> list = bll.QueryList(pageIndex, pageSize, strSql.ToString(), "");
|
|
returnstr += JsonHelper.ToJson(list);
|
|
}
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 柱状图
|
|
/// <summary>
|
|
/// 柱状图
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
public string VisitorReportBar(HttpContext context)
|
|
{
|
|
string returnstr = string.Empty;
|
|
try
|
|
{
|
|
string orgId = context.Request.Params["orgId"];
|
|
string year = context.Request.Params["year"];
|
|
string isContainBelow = context.Request.Params["isContain"];
|
|
DataTable dt = bll.VisitorReportBar(orgId, year, isContainBelow);
|
|
returnstr = JsonHelper.ToJson(dt);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = null;
|
|
}
|
|
return returnstr;
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region HtmlToWord
|
|
/// <summary>
|
|
/// HtmlToWord
|
|
/// </summary>
|
|
/// <param name="context"></param>
|
|
/// <returns></returns>
|
|
private string HtmlToWord(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string by1 = context.Request.Params["by1"];
|
|
|
|
|
|
//实例化一个Document对象
|
|
Document doc = new Document();
|
|
doc.Watermark = null;
|
|
|
|
//添加section和段落
|
|
Section section = doc.AddSection();
|
|
Paragraph para = section.AddParagraph();
|
|
//加载图片到System.Drawing.Image对象, 使用AppendPicture方法将图片插入到段落
|
|
if (by1 != null && by1 != "")
|
|
{
|
|
by1 = HttpUtility.UrlDecode(by1);
|
|
Image image1 = GetImage(by1.Replace("data:image/png;base64,", ""));
|
|
Spire.Doc.Fields.DocPicture picture1 = doc.Sections[0].Paragraphs[0].AppendPicture(image1);
|
|
//设置图片大小
|
|
picture1.Width = 595;
|
|
}
|
|
|
|
String[] headerQiandi = { "姓名", "身份证号", "来访时间", "来访原因", "审批领导", "岗哨值班人员" }; //表头字段
|
|
|
|
#region 获取table数据
|
|
string orgId = context.Request.Params["orgId"];
|
|
string startDate = context.Request.Params["startDate"];
|
|
string endDate = context.Request.Params["endDate"];
|
|
string is_content = context.Request.Params["iscontain"];
|
|
int pageIndex = 1;
|
|
int pageSize = 99999;
|
|
string where = null;
|
|
|
|
if (is_content == "1")
|
|
{
|
|
where = " org_id in (select o.org_id from fire_org o,(select get_Org_child_list('" + orgId + "') cids ) s where o.type = '0' and find_in_set(org_id,cids) ) ";
|
|
}
|
|
else
|
|
{
|
|
where = " org_id = '" + orgId + "' ";
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(startDate))
|
|
{
|
|
where += " and date_format(INTIME, '%Y-%m-%d') >= date_format('" + startDate + "-01', '%Y-%m-%d') ";
|
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
|
{
|
|
where += " and date_format(INTIME, '%Y-%m-%d') < date_format('" + endDate + "-01', '%Y-%m-%d') ";
|
|
}
|
|
where += " order by INTIME desc";
|
|
List<Model.TBL_VISITOR> list = bll.QueryList(pageIndex, pageSize, where, "");
|
|
#endregion 获取table数据
|
|
|
|
if (list.Count > 0)
|
|
{
|
|
string[][] arrQiandiData = new string[list.Count][];
|
|
for (int i = 0; i < list.Count; i++)
|
|
{
|
|
arrQiandiData[i] = new string[] {
|
|
list[i].NAME,
|
|
list[i].EXTEND2,
|
|
list[i].INTIME.ToString(),
|
|
list[i].REASON,
|
|
list[i].VED_MAN,
|
|
list[i].FIRESHIFTMAN
|
|
};
|
|
}
|
|
#region 创建Word表格
|
|
Table tableQiandi = section.AddTable(); //新建表格
|
|
tableQiandi.ResetCells(list.Count + 1, headerQiandi.Length);
|
|
tableQiandi.TableFormat.Borders.BorderType = BorderStyle.Single;
|
|
TableRow rowQiandi = tableQiandi.Rows[0]; //添加行
|
|
rowQiandi.IsHeader = true; //设为表头
|
|
rowQiandi.Height = 30;
|
|
rowQiandi.HeightType = TableRowHeightType.Auto;
|
|
for (int i = 0; i < headerQiandi.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(section, p, headerQiandi[i], 14, true, "黑体", HorizontalAlignment.Center);
|
|
}
|
|
|
|
for (int r = 0; r < arrQiandiData.Length; r++) //生成表体
|
|
{
|
|
TableRow dataRow = tableQiandi.Rows[r + 1];
|
|
dataRow.RowFormat.BackColor = Color.Empty;
|
|
for (int c = 0; c < arrQiandiData[r].Length; c++)
|
|
{
|
|
dataRow.Cells[c].Width = 145;
|
|
dataRow.Height = 20;
|
|
dataRow.Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
|
|
TextRange tr = dataRow.Cells[c].AddParagraph().AppendText(arrQiandiData[r][c]);
|
|
tr.CharacterFormat.FontSize = 12;
|
|
}
|
|
}
|
|
#endregion 创建Word表格
|
|
}
|
|
//保存到文档
|
|
string path = AppDomain.CurrentDomain.BaseDirectory + "\\OA\\VisitorReport\\";
|
|
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/VisitorReport/" + fileName + "\"}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"" + e.Message + "\"}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
private Image GetImage(string fileName)
|
|
{
|
|
byte[] bytes = Convert.FromBase64String(fileName);
|
|
using (MemoryStream ms = new MemoryStream(bytes))
|
|
{
|
|
return Image.FromStream(ms);
|
|
}
|
|
}
|
|
|
|
|
|
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
|
|
|
|
|
|
//查询
|
|
private string getVisitorList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
string name = context.Request.Params["name"];
|
|
string startDate = context.Request.Params["startDate"];
|
|
string endDate = context.Request.Params["endDate"];
|
|
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 = " 1=1 ", regwhere = " 1=1 ";
|
|
if (!string.IsNullOrEmpty(OrgId))
|
|
{
|
|
where += " and ORG_ID ='" + OrgId + "'";
|
|
regwhere += " and ORG_ID ='" + OrgId + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
where += " and name ='" + name + "'";
|
|
regwhere += " and name ='" + name + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(startDate))
|
|
{
|
|
where += " and date_format(intime,'%Y-%m-%d %H:%i:%s') >= date_format('" + startDate + "', '%Y-%m-%d %H:%i:%s') ";
|
|
regwhere += " and date_format(registertime, '%Y-%m-%d %H:%i:%s') >= date_format('" + startDate + "', '%Y-%m-%d %H:%i:%s') ";
|
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
|
{
|
|
where += " and date_format(intime,'%Y-%m-%d %H:%i:%s') <= date_format('" + endDate + "', '%Y-%m-%d %H:%i:%s') ";
|
|
regwhere += " and date_format(registertime, '%Y-%m-%d %H:%i:%s') <= date_format('" + endDate + "', '%Y-%m-%d %H:%i:%s') ";
|
|
}
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
int count = bll.GetQueryVisitorListCount(where, regwhere);
|
|
returnstr += "\"count\":" + count + ",\"data\":";
|
|
if (count == 0)
|
|
{
|
|
returnstr += "[]";
|
|
}
|
|
else
|
|
{
|
|
//访客出入记录表
|
|
DataTable dt = bll.QueryVisitorList(pageIndex, pageSize, where, regwhere);
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt);
|
|
|
|
}
|
|
returnstr += "}";
|
|
}
|
|
catch
|
|
{
|
|
returnstr = "{\"code\":-1,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
//查询
|
|
private string getVisitorTrajectoryList(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string OrgId = context.Request.Params["OrgId"];
|
|
string name = context.Request.Params["name"];
|
|
string visitorTime = context.Request.Params["visitorTime"];
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"\",";
|
|
|
|
//访客出入记录表
|
|
DataTable dt = bll.QueryVisitorInfoList(OrgId, name, visitorTime);
|
|
returnstr += "\"count\":" + dt.Rows.Count + ",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dt);
|
|
returnstr += "}";
|
|
}
|
|
catch
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"count\":0,\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 根据时间统计各个营区：车辆出入次数、人员出入次数、访客出入次数、摄像头抓拍次数
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
private string getStatisticsCarPerVisCamByTime(HttpContext context)
|
|
{
|
|
string returnstr = "";
|
|
try
|
|
{
|
|
string startTime = context.Request.Params["startTime"];
|
|
string endTime = context.Request.Params["endTime"];
|
|
string sql = "with t1 as (" +
|
|
"select f.org_id,f.org_name,(select fo.org_name from fire_org fo where fo.org_id = f.pid) p_name from fire_org f " +
|
|
",(select get_Org_child_list('D8DC637B8B984848A63F82A018AFAEB0') cids ) s where find_in_set(f.org_id,cids) and type = '0' " +
|
|
") , t2 as ( " +
|
|
"select t1.org_name,count(c.id) carNum from t1 left join tbl_caraccess c on t1.org_id = c.org_id where START_TIME between '" + startTime + "' and '" + endTime + "' group by t1.org_name " +
|
|
") , t3 as ( " +
|
|
"select t1.org_name,count(p.id) perNum from t1 left join tbl_personaccess p on t1.org_id = p.org_id where START_TIME between '" + startTime + "' and '" + endTime + "' group by t1.org_name " +
|
|
") , t4 as ( " +
|
|
"select t1.org_name,count(v.id) visNum from t1 left join tbl_visitor v on t1.org_id = v.org_id where INTIME between '" + startTime + "' and '" + endTime + "' group by t1.org_name " +
|
|
") select t1.p_name,t1.org_id,t1.org_name,ifnull(t2.carNum,0) carNum,ifnull(t3.perNum,0) perNum,ifnull(t4.visNum,0) visNum, 0 as camNum from " +
|
|
"t1 left join t2 on t1.org_name = t2.org_name " +
|
|
"left join t3 on t2.org_name = t3.org_name " +
|
|
"left join t4 on t2.org_name = t4.org_name " +
|
|
"order by (ifnull(t2.carNum,0) + ifnull(t3.perNum,0) + ifnull(t4.visNum,0)) desc";
|
|
|
|
DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
|
|
|
|
string orgSql = "SELECT org_id,EXTEND3,0 as count FROM `tbl_camera` where LENGTH(EXTEND3)>0 group by org_id,EXTEND3";
|
|
DataTable orgDt = FangYar.Common.MySqlHelper.QueryTable(orgSql);
|
|
|
|
string faceSql = "SELECT dev_serial,count(1) as c_num FROM default.t_face_data WHERE pic_url<>'' and alarm_time BETWEEN '" + startTime + "' and '" + endTime + "' group by dev_serial";
|
|
DataTable camDt = FangYar.Common.ClickHouseHelper.Execute(faceSql);
|
|
List<devSerialMo> camList = new List<devSerialMo>();
|
|
for (int i = 0; i < camDt.Rows.Count; i++)
|
|
{
|
|
camList.Add(new devSerialMo()
|
|
{
|
|
devSerial = camDt.Rows[i]["dev_serial"] + "",
|
|
count = Convert.ToInt32(camDt.Rows[i]["c_num"])
|
|
});
|
|
}
|
|
|
|
List<devSerialMo> orgCamList = new List<devSerialMo>();
|
|
for (int i = 0; i < orgDt.Rows.Count; i++)
|
|
{
|
|
string EXTEND3 = orgDt.Rows[i]["EXTEND3"].ToString();
|
|
int count = 0;
|
|
devSerialMo camMo = camList.Where(p => p.devSerial == EXTEND3).FirstOrDefault();
|
|
|
|
if (camMo != null) { count = camMo.count; }
|
|
|
|
orgCamList.Add(new devSerialMo()
|
|
{
|
|
orgId = orgDt.Rows[i]["ORG_ID"].ToString(),
|
|
devSerial = EXTEND3,
|
|
count = count
|
|
});
|
|
}
|
|
var orgCamList2 = orgCamList.GroupBy(p => p.orgId).Select(p => new { orgid = p.Key, num = p.Sum(s => s.count) });
|
|
|
|
List<StatisticsCarPerVisCamMo> dtList = new List<StatisticsCarPerVisCamMo>();
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
string ORG_ID = dt.Rows[i]["ORG_ID"].ToString();
|
|
int camNum = 0;
|
|
var orgMo = orgCamList2.Where(p => p.orgid == ORG_ID).FirstOrDefault();
|
|
|
|
if (orgMo != null) { camNum = orgMo.num; }
|
|
|
|
dtList.Add(new StatisticsCarPerVisCamMo()
|
|
{
|
|
orgId = ORG_ID,
|
|
orgName = dt.Rows[i]["ORG_NAME"].ToString(),
|
|
pName = dt.Rows[i]["P_NAME"].ToString(),
|
|
carNum = Convert.ToInt32(dt.Rows[i]["CARNUM"]),
|
|
perNum = Convert.ToInt32(dt.Rows[i]["PERNUM"]),
|
|
visNum = Convert.ToInt32(dt.Rows[i]["VISNUM"]),
|
|
camNum = camNum
|
|
});
|
|
}
|
|
dtList.OrderByDescending(p => (p.carNum + p.perNum + p.visNum + p.camNum));
|
|
|
|
returnstr = "{\"code\":0,\"msg\":\"操作成功！\",\"data\":";
|
|
returnstr += FangYar.Common.JsonHelper.ToJson(dtList);
|
|
returnstr += "}";
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
returnstr = "{\"code\":0,\"msg\":\"error\",\"data\":[]}";
|
|
}
|
|
return returnstr;
|
|
}
|
|
|
|
public class devSerialMo
|
|
{
|
|
/// <summary>
|
|
/// 组织机构ID
|
|
/// </summary>
|
|
public string orgId { get; set; }
|
|
/// <summary>
|
|
/// 摄像头编号
|
|
/// </summary>
|
|
public string devSerial { get; set; }
|
|
/// <summary>
|
|
/// 数量
|
|
/// </summary>
|
|
public int count { get; set; }
|
|
}
|
|
|
|
public class StatisticsCarPerVisCamMo
|
|
{
|
|
/// <summary>
|
|
/// 组织机构ID
|
|
/// </summary>
|
|
public string orgId { get; set; }
|
|
/// <summary>
|
|
/// 组织机构名称
|
|
/// </summary>
|
|
public string orgName { get; set; }
|
|
/// <summary>
|
|
/// 上级组织机构名称
|
|
/// </summary>
|
|
public string pName { get; set; }
|
|
/// <summary>
|
|
/// 车辆出入数
|
|
/// </summary>
|
|
public int carNum { get; set; }
|
|
/// <summary>
|
|
/// 人员出入数
|
|
/// </summary>
|
|
public int perNum { get; set; }
|
|
/// <summary>
|
|
/// 访客数
|
|
/// </summary>
|
|
public int visNum { get; set; }
|
|
/// <summary>
|
|
/// 摄像头抓拍数
|
|
/// </summary>
|
|
public int camNum { get; set; }
|
|
}
|
|
|
|
|
|
}
|
|
}
|