///// <summary>
///// 判断数组中是否有参数
///// </summary>
///// <param name="Risks"></param>
///// <param name="ReskList"></param>
///// <param name="value"></param>
///// <returns></returns>
//public bool GetCountTrue(string Risks, IEnumerable<DataItemDetailEntity> ReskList, out string Riskvalue)
//{
// string value = "";
// Riskvalue = "";
// foreach (string r in Risks)
// {
// if (GetIsTrue(ReskList, r, out value))
// {
// if (Riskvalue == "")
// {
// Riskvalue = value;
// }
// else
// {
// Riskvalue += "," + value;
// }
// }
// else
// {
// return false;
// }
// }
// return true;
//}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
string wheresql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
wheresql = "";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
wheresql += " and ha." + where;
}
DataTable dt = hazardfactorsbll.GetTable(queryJson, wheresql);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = i + 1;
}
string FileUrl = @"\Resource\ExcelTemplate\职业病危害因素清单_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业病危害因素清单", "职业病危害因素列表");
return(Success("导出成功。"));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
string whereSql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
whereSql = "";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
whereSql += " and " + where;
}
DataTable dt = occupatioalstaffbll.GetTable(queryJson, whereSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = (i + 1).ToString();
dt.Rows[i]["INSPECTIONTIME"] = Convert.ToDateTime(dt.Rows[i]["INSPECTIONTIME"]).ToString("yyyy-MM-dd");
}
string FileUrl = @"\Resource\ExcelTemplate\职业健康体检列表_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业健康体检列表", "职业健康体检列表");
return(Success("导出成功。"));
}
public ActionResult HallIndex(DateTime?beginTime, DateTime?endTime, bool export = false)
{
// 初始化日期
DateTimeInit(ref beginTime, ref endTime);
// 获取数据
var result = STAT_WARN_ANALYSIS_DAL.GetStatistics_WarnAnalysisChart(UserState.UserID, beginTime, endTime);
const string titleName = "预警分析";
var mainTielt = GetOrgName(null, OrgHighLevel);
ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
//导出
if (export)
{
var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var index = 0;
var data = result == null
? new DataTable()
: result.Select(x => new
{
序号 = ++index,
务厅 = x.HALL_NAM,
预警级别 = x.L_Name,
等候超时 = x.Count_T1,
等候超时率 = x.Count_T2,
窗口饱和度 = x.Count_T3,
大厅饱和度 = x.Count_T4,
超时办结率 = x.Count_T5,
超时业务笔数 = x.Count_T6,
弃号率 = x.Count_T7,
差评笔数预警 = x.Count_T8,
连续工作时长超界 = x.Count_T9,
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(data, "预警分析报表-服务厅", exceltitle));
}
var chartData = result == null
? new DataTable()
: result.Where(r => r.L_Code == "H").Select(x => new
{
务厅 = x.HALL_NAM,
等候超时率 = x.Count_T2,
超时办结率 = x.Count_T5,
差评笔数预警 = x.Count_T8,
}).ToList().ToDataTable();
ViewBag.BeginTime = !beginTime.HasValue ? DateTime.Now.ToString("yyyy-MM-dd") : beginTime.Value.ToString("yyyy-MM-dd");
ViewBag.EndTime = !endTime.HasValue ? DateTime.Now.ToString("yyyy-MM-dd") : endTime.Value.ToString("yyyy-MM-dd");
// 输出图表
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 420, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle);
return(View(result));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
//string column = "IDNUM,OCCDETAILID,USERNAME,USERNAMEPINYIN,INSPECTIONTIME,ISSICK,SICKTYPE";
//string stringcolumn = "ISSICK,SICKTYPE";
//string[] columns = column.Split(',');
//string[] stringcolumns = stringcolumn.Split(',');
string wheresql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
wheresql = "";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
wheresql += " and " + where;
}
DataTable dt = occupationalstaffdetailbll.GetStaffList(queryJson, wheresql);
//DataTable Newdt = AsposeExcelHelper.UpdateDataTable(dt, columns, stringcolumns);//把所有字段转换成string 方便修改
////先获取职业病数据
//DataItemBLL di = new DataItemBLL();
////先获取到字典项
//DataItemEntity DataItems = di.GetEntityByCode("SICKTYPE");
//DataItemDetailBLL did = new DataItemDetailBLL();
////根据字典项获取值
//IEnumerable<DataItemDetailEntity> didList = did.GetList(DataItems.ItemId);
//循环修改信息
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = i + 1;
//if (Convert.ToInt32(dt.Rows[i]["ISSICK"]) == 1)
//{
// foreach (DataItemDetailEntity item in didList)
// {
// if (item.ItemValue == dt.Rows[i]["SICKTYPE"].ToString())
// {
// dt.Rows[i]["SICKTYPE"] = item.ItemName;
// }
// }
//}
//else
//{
// dt.Rows[i]["SICKTYPE"] = "";
//}
}
string FileUrl = @"\Resource\ExcelTemplate\职业病人员清单_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业病人员清单", "职业病人员列表");
return(Success("导出成功。"));
}
public ActionResult ExportCompareNumber(string queryJson)
{
var dt = legbll.GetLllegalCompareDataGrp(queryJson);
string fileUrl = @"\Resource\ExcelTemplate\违章比较数量_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, fileUrl, "违章数量", "违章对比统计数量");
return(Success("导出成功。"));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
DepartmentBLL departmentBLL = new DepartmentBLL();
string sqlwhere = "Account!='System' and us is not null";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
sqlwhere = "1=1 and us is not null";
}
else
{
var queryParam = queryJson.ToJObject();
if (queryParam["datatype"].IsEmpty())
{
string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "departmentcode", "organizecode");
if (!string.IsNullOrEmpty(where) && (queryParam["code"].IsEmpty() || !queryJson.Contains("code")))
{
sqlwhere += " and " + where;
}
}
}
var data = new HazardfactoruserBLL().GetPageList(sqlwhere, queryJson);
foreach (DataRow dr in data.Rows)
{
if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组")
{
DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from BASE_DEPARTMENT where encode=(select encode from BASE_DEPARTMENT t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"]));
if (dt.Rows.Count > 0)
{
string name = "";
foreach (DataRow dr1 in dt.Rows)
{
name += dr1["fullname"].ToString() + "/";
}
dr["deptname"] = name.TrimEnd('/');
}
}
}
for (int i = 0; i < data.Rows.Count; i++)
{
data.Rows[i][0] = i + 1;
}
string FileUrl = @"\Resource\ExcelTemplate\接触职业危害因素人员_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(data, FileUrl, "接触职业危害因素人员清单", "接触职业危害因素人员列表");
return(Success("导出成功。"));
}
private void cmsDatabaseItem_Click(object sender, EventArgs e)
{
var tripItem = sender as ToolStripItem;
var currentNode = tvDatabase.SelectedNode;
if (tripItem == null)
{
return;
}
switch (tripItem.Text)
{
case Constants.CONTEXT_MENU_DATABASE_EXPORT_DICTIONARY:
try
{
LoadingFormService.CreateForm();
LoadingFormService.SetFormCaption(Constants.EXPORT_CAPTION);
List <DbTable> tableList = DataAccess.GetDbTableListWithColumns(_connBuilder, currentNode.Text);
//Workbook workbook = ExcelHelper.GenerateWorkbook(tableList);
IExcelHelper helper = new AsposeExcelHelper();
//LoadingFormService.CloseFrom();
var dia = new SaveFileDialog
{
Filter = Constants.SAVE_FILE_DIALOG_FILTER,
FileName = currentNode.Text + " Data Dictionary"
};
if (dia.ShowDialog() == DialogResult.OK)
{
helper.GenerateWorkbook(tableList, dia.FileName);
//workbook.Save(dia.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, Constants.ERROR_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
LoadingFormService.CloseFrom();
}
break;
case Constants.CONTEXT_MENU_DATABASE_REFRESH:
LoadTableTreeNode(currentNode);
break;
}
}
public ActionResult Export(string queryJson, string sortname, string sortorder)
{
var pagination = new Pagination()
{
page = 1,
rows = 100000,
sidx = string.IsNullOrWhiteSpace(sortname) ? "createdate" : sortname,
sord = string.IsNullOrWhiteSpace(sortorder) ? "asc" : sortorder
};
var dt = stdsysfilesbll.GetList(pagination, queryJson);
string fileUrl = @"\Resource\ExcelTemplate\标准制度_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, fileUrl, "标准制度", string.Format("标准制度_{0}", DateTime.Now.ToString("yyyyMMddHHmmss")));
return(Success("导出成功。"));
}
public ActionResult Export(string queryJson, string sortname, string sortorder)
{
var pagination = new Pagination()
{
page = 1,
rows = 100000,
sidx = string.IsNullOrWhiteSpace(sortname) ? "createdate" : sortname,
sord = string.IsNullOrWhiteSpace(sortorder) ? "asc" : sortorder
};
var dt = nosaworksbll.GetList(pagination, queryJson);
string fileUrl = @"\Resource\ExcelTemplate\NOSA元素工作清单_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(dt, fileUrl, "工作清单", "NOS元素工作清单");
return(Success("导出成功。"));
}
public ActionResult TaxpayerAction_Person(
DateTime?beginTime,
DateTime?endTime,
string orgId,
bool export = false)
{
// 初始化日期
base.DateTimeInit(ref beginTime, ref endTime);
// 获取数据
var bll = new Statistics_DAL();
var result = bll.GetStatistics_TaxpayerActionChat_Person(beginTime, endTime, orgId);
// 控制标题
var MainTitle = base.GetOrgName(null, 3);
var nodeTitle = string.Empty;
MainTitle += "纳税人评价分析";
nodeTitle += "（" + beginTime.Value.ToString("yyyy年MM月dd日");
nodeTitle += " - " + endTime.Value.ToString("yyyy年MM月dd日") + "）)";
ViewBag.MainTitle = MainTitle;
ViewBag.NodeTitle = nodeTitle;
if (export)
{
var index = 0;
var data = result.Select(x => new
{
序号 = ++index,
工号 = x.PersonNo,
姓名 = x.PersonName,
业务大类 = x.DLS_SERIALNAME,
城业务量 = x.LOCAL_CNT,
二次办税业务量 = x.SECOND_SVR_CNT
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(data, MainTitle + "报表"));
}
SetChart_Person(result.GroupBy(x => x.PersonNo), MainTitle, nodeTitle);
// 所有事项大类
var itemTypeList = SYS_DLSERIAL_Dao.FindList();
ViewData["itemTypeList"] = itemTypeList;
return(View(result));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
string wheresql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
wheresql = "1=1";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
wheresql += " and " + where;
}
DataTable dt = laborinfobll.GetTable(queryJson, wheresql);
DataItemDetailBLL dataItemDetailBLL = new DataItemDetailBLL();
var datadetail = dataItemDetailBLL.GetDataItemListByItemCode("'LaborName'");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = i + 1;
dt.Rows[i]["TimeType"] = dt.Rows[i]["TimeNum"].ToString() + dt.Rows[i]["TimeType"].ToString();
DataItemModel dm = datadetail.Where(it => it.ItemName == dt.Rows[i]["NAME"].ToString()).FirstOrDefault();
if (dm != null)
{
dt.Rows[i]["InStock"] = dm.ItemValue.ToString();
}
else
{
dt.Rows[i]["InStock"] = "";
}
}
string FileUrl = @"\Resource\ExcelTemplate\劳动防护用品管理_导出.xls";
AsposeExcelHelper.ExecuteResult(dt, FileUrl, "劳动防护用品管理清单", "劳动防护用品管理列表");
return(Success("导出成功。"));
}
public ActionResult ExportNumber(string queryJson)
{
var dt1 = legbll.GetLllegalLevelListGrp(queryJson);
dt1.TableName = "Table1";
var dt2 = legbll.GetLllegalTypeListGrp(queryJson);
dt2.TableName = "Table2";
var ds = new DataSet("ds");
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
string fileUrl = @"\Resource\ExcelTemplate\违章统计数量_导出模板.xlsx";
AsposeExcelHelper.ExecuteResultX(ds, fileUrl, new List <string>()
{
"违章级别数量", "违章类型数量"
}, "违章类型、级别统计数量");
return(Success("导出成功。"));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
string column = "IDNUM,OCCDETAILID,USERNAME,USERNAMEPINYIN,INSPECTIONTIME,ISSICK,SICKTYPE,ISENDEMIC,ISUNUSUAL,UNUSUALNOTE";
string stringcolumn = "ISSICK,SICKTYPE";
string[] columns = column.Split(',');
string[] stringcolumns = stringcolumn.Split(',');
string wheresql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
wheresql = "";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
wheresql += " and " + where;
}
DataTable dt = occupationalstaffdetailbll.GetTable(queryJson, wheresql);
DataTable Newdt = AsposeExcelHelper.UpdateDataTable(dt, columns, stringcolumns);//把所有字段转换成string 方便修改
//先获取职业病数据
DataItemBLL di = new DataItemBLL();
//先获取到字典项
DataItemEntity DataItems = di.GetEntityByCode("SICKTYPE");
DataItemDetailBLL did = new DataItemDetailBLL();
//根据字典项获取值
IEnumerable <DataItemDetailEntity> didList = did.GetList(DataItems.ItemId);
//循环修改信息
for (int i = 0; i < Newdt.Rows.Count; i++)
{
Newdt.Rows[i]["IDNUM"] = i + 1;
Newdt.Rows[i]["INSPECTIONTIME"] = Convert.ToDateTime(Newdt.Rows[i]["INSPECTIONTIME"]).ToString("yyyy-MM-dd");
if (Convert.ToInt32(Newdt.Rows[i]["ISSICK"]) == 1)
{
Newdt.Rows[i]["ISSICK"] = "是";
foreach (DataItemDetailEntity item in didList)
{
if (item.ItemValue == Newdt.Rows[i]["SICKTYPE"].ToString())
{
Newdt.Rows[i]["SICKTYPE"] = item.ItemName;
}
}
}
else
{
Newdt.Rows[i]["ISSICK"] = "否";
Newdt.Rows[i]["SICKTYPE"] = "";
}
}
string FileUrl = "";
var queryParam = queryJson.ToJObject();
string keyord = queryParam["keyword"].ToString();
if (keyord.ToInt() == 1) //查询职业病
{
FileUrl = @"\Resource\ExcelTemplate\职业健康职业病人员列表_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "职业病人员列表", "职业病人员列表");
}
else if (keyord.ToInt() == 2) //查询异常人员
{
FileUrl = @"\Resource\ExcelTemplate\职业健康异常人员列表_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "异常人员列表", "异常人员列表");
}
else if (keyord.ToInt() == 3) //查询体检人员
{
FileUrl = @"\Resource\ExcelTemplate\职业健康体检人员列表_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "体检人员列表", "体检人员列表");
}
return(Success("导出成功。"));
}
public ActionResult Index(DateTime?beginTime, DateTime?endTime, int pageIndex = 1, int pageSize = 20, bool export = false)
{
base.DateTimeInit(ref beginTime, ref endTime);
// 初始化日期
var orgall = new SYS_USER_DAL().GetUserORG(UserState.UserID);
ViewBag.UserORG = new SelectList(orgall.Where(obj => obj.ORG_LEVEL == 4)
, "ORG_ID", "ORG_NAM", "");
var orgs = orgall.Select(obj => obj.ORG_ID);
var mainTielt = GetOrgName(null, null);
ViewBag.MainTitle = GetTitleName(mainTielt, "排队业务分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var exceltitle = GetTitleName(mainTielt, "排队业务分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var halllist = base.UserHall.Select(x => x.HALL_NO).ToArray();
var data = new STAT_STAFF_QUEUE_BUSI_D_DAL().GetStatsInfo(pageIndex, int.MaxValue, halllist, beginTime, endTime);
if (export)//导出
{
var index = 0;
var temp = data.Select(x => new
{
序号 = ++index,
业务编码 = x.QUEUE_BUSI_CD,
业务名称 = x.Q_SERIALNAME,
呼叫量 = x.CALL_CNT,
呼叫率 = CommonHelper.DivisionOfPercent(x.CALL_CNT, data.Sum(obj => obj.CALL_CNT)),
超时等待量 = x.OVERTIME_WAIT_CNT,
超时等待率 = CommonHelper.DivisionOfTimeString(x.OVERTIME_WAIT_CNT, data.Sum(obj => obj.OVERTIME_WAIT_CNT)),
办理量 = x.HANDLE_CNT,
办理率 = CommonHelper.DivisionOfPercent(x.HANDLE_CNT, data.Sum(obj => obj.HANDLE_CNT)),
弃号量 = x.ABANDON_CNT,
弃号率 = CommonHelper.DivisionOfPercent(x.ABANDON_CNT, data.Sum(obj => obj.ABANDON_CNT)),
平均办理时间 = CommonHelper.DivisionOfTimeString(x.HANDLE_DUR, x.HANDLE_CNT),
平均等待时间 = CommonHelper.DivisionOfTimeString(x.WAIT_DUR, x.TOT_TICKET_CNT),
最长等待时间 = x.MAX_WAIT_DUR,
最长办理时间 = x.MAX_HANDLE_DUR,
超时办理量 = x.OVERTIME_HANDLE_CNT,
超时办理率 = CommonHelper.DivisionOfPercent(x.OVERTIME_HANDLE_CNT, x.TOT_TICKET_CNT),
人流量 = x.TOT_TICKET_CNT
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(temp, exceltitle));
}
else
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Tables.Add(dt);
dt.Columns.Add("Q_SERIALNAME", typeof(string));
dt.Columns.Add("呼叫量", typeof(int));
dt.Columns.Add("办理量", typeof(int));
dt.Columns.Add("弃号量", typeof(int));
foreach (var item in data)
{
DataRow r = dt.NewRow();
r["Q_SERIALNAME"] = item.Q_SERIALNAME;
r["呼叫量"] = item.CALL_CNT;
r["办理量"] = item.HANDLE_CNT;
r["弃号量"] = item.ABANDON_CNT;
dt.Rows.Add(r);
}
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart("排队业务分析", ds.Tables[0], 430, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart("排队业务报分析", ds, 430, null, null, subtitle);
return(View(data));
}
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult Excel(string queryJson)
{
string column = "HID,AREAVALUE,RISKVALUE,LOCATION,STARTTIME,ENDTIME,STANDARD,DETECTIONUSERNAME,ISEXCESSIVE";
string stringcolumn = "ISEXCESSIVE";
string[] columns = column.Split(',');
string[] stringcolumns = stringcolumn.Split(',');
string whereSql = "";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (user.IsSystem)
{
whereSql = "";
}
else
{
string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value);
whereSql += " and " + where;
}
DataTable dt = hazarddetectionbll.GetDataTable(queryJson, whereSql);
DataTable Newdt = AsposeExcelHelper.UpdateDataTable(dt, columns, stringcolumns);//把部分字段转换成string 方便修改
for (int i = 0; i < Newdt.Rows.Count; i++)
{
Newdt.Rows[i][0] = (i + 1).ToString();
//if (Newdt.Rows[i]["ENDTIME"].ToString() != "")//转换时间格式
//{
// Newdt.Rows[i]["ENDTIME"] = Convert.ToDateTime(Newdt.Rows[i]["ENDTIME"]).ToString("yyyy-MM-dd");
//}
if (Newdt.Rows[i]["STANDARD"].ToString() != "")
{
string[] str = Newdt.Rows[i]["STANDARD"].ToString().Split(';');
string html = "";
for (var j = 0; j < str.Length; j++)
{
var group = str[j].Split(',');
if (j == 0)
{
html = "指标" + group[0] + ":" + group[1];
}
else
{
html += ";指标" + group[0] + ":" + group[1];
}
}
Newdt.Rows[i]["STANDARD"] = html;
}
if (Convert.ToInt32(Newdt.Rows[i]["ISEXCESSIVE"]) == 0)
{
Newdt.Rows[i]["ISEXCESSIVE"] = "否";
}
else
{
Newdt.Rows[i]["ISEXCESSIVE"] = "是";
}
}
string FileUrl = @"\Resource\ExcelTemplate\职业病危害因素监测_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "职业病危害因素监测列表", "职业病危害因素监测列表");
return(Success("导出成功。"));
}
public ActionResult Index(DateTime?beginTime, DateTime?endTime, string orgid, int pageIndex = 1, int pageSize = 20, bool export = false)
{
base.DateTimeInit(ref beginTime, ref endTime);
// 初始化日期
var orgall = new SYS_USER_DAL().GetUserORG(UserState.UserID);
ViewBag.UserORG = new SelectList(orgall.Where(obj => obj.ORG_LEVEL == 4)
, "ORG_ID", "ORG_NAM", orgid);
var orgs = orgall.Select(obj => obj.ORG_ID);
//var mainTielt = GetOrgName(null, null);
//ViewBag.MainTitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault());
//var title = GetTitleName(mainTielt,"业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var mainTielt = GetOrgName(null, null);
ViewBag.MainTitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var exceltitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var halllist = base.UserHall.Select(x => x.HALL_NO).ToArray();
var data = new STAT_STAFF_LARGE_BUSI_D_DAL().GetStatsInfo(halllist, beginTime, endTime);
if (export)//导出
{
var index = 0;
var temp = data.Select(x => new
{
序号 = ++index,
业务大类 = x.DLS_SERIALNAME,
业务笔数 = x.BUSI_CNT,
业务折合量 = x.CONVERT_BUSI_CNT,
平均版办理时间 = (((int)(x.BUSI_CNT == 0 ? 0 : (x.HANDLE_DUR / x.BUSI_CNT))).ToTimeString()) + "%",
超时办理时间 = x.OVERTIME_HANDLE_CNT,
超时率 = ((x.BUSI_CNT == 0 ? 0 : (x.OVERTIME_HANDLE_CNT / x.BUSI_CNT)).ToString("P")) + "%",
城业务笔数 = x.LOCAL_CNT,
城办理率 = ((x.LOCAL_CNT * 100.0 / x.BUSI_CNT)).ToString("f2") + "%",
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(temp, exceltitle));
}
else
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Tables.Add(dt);
dt.Columns.Add("NAME", typeof(string));
dt.Columns.Add("业务笔数", typeof(int));
dt.Columns.Add("业务折合量", typeof(int));
dt.Columns.Add("总办理时长", typeof(int));
dt.Columns.Add("超时办理量", typeof(int));
dt.Columns.Add("同城业务量", typeof(int));
foreach (var item in data)
{
var r = dt.NewRow();
r["NAME"] = item.DLS_SERIALNAME;
r["业务笔数"] = item.BUSI_CNT;
r["业务折合量"] = item.CONVERT_BUSI_CNT;
r["总办理时长"] = item.HANDLE_DUR;
r["超时办理量"] = item.OVERTIME_HANDLE_CNT;
r["同城业务量"] = item.LOCAL_CNT;
dt.Rows.Add(r);
}
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart("业务大类分析", ds.Tables[0], 430, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart("业务大类分析", ds, 430, null, null, subtitle);
return(View(data));
}
}
public ActionResult Excel(string type)
{
try
{
string[] column = { "createdate" };
string[] column1 = { "type", "status" };
var dt = Session["CarIntandOutRecord"] as DataTable;
dt.TableName = "Table";
dt = AsposeExcelHelper.UpdateDataTable(dt, column, "datetime");
DataTable newdt = AsposeExcelHelper.UpdateDataTable(dt, column1, "string");
for (int i = 0; i < newdt.Rows.Count; i++)
{
string res = newdt.Rows[i]["status"].ToString();
if (res == "0")
{
res = "进";
}
else
{
res = "出";
}
newdt.Rows[i]["status"] = res;
int num = newdt.Rows[i]["type"].ToInt();
switch (num)
{
case 0:
newdt.Rows[i]["type"] = "电厂班车";
break;
case 1:
newdt.Rows[i]["type"] = "私家车";
break;
case 2:
newdt.Rows[i]["type"] = "商务公车";
break;
case 3:
dt.Rows[i]["type"] = "拜访车辆";
break;
case 4:
newdt.Rows[i]["type"] = "物料车辆";
break;
case 5:
newdt.Rows[i]["type"] = "危化品车辆";
break;
case 6:
newdt.Rows[i]["type"] = "临时通行车辆";
break;
}
}
//模板路径
string FileUrl = @"\Resource\ExcelTemplate\车辆进出厂记录_导出模板.xlsx";
AsposeExcelHelper.ExecuteResult(newdt, FileUrl, "车辆进出厂记录列表", "车辆进出厂记录列表");
}
catch (Exception)
{
throw;
}
return(Content("导出成功。"));
}
public ActionResult HallIndex(DateTime?beginTime, DateTime?endTime, bool export = false)
{
// 初始化日期
DateTimeInit(ref beginTime, ref endTime);
// 获取数据
var result = STAT_COMPLAINT_REPORT_DAL.GetStatistics_ComplaintReportChart(UserState.UserID, beginTime, endTime);
const string titleName = "投诉举报分析";
var mainTielt = GetOrgName(null, OrgHighLevel);
ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var dataTable = new DataTable();
dataTable.Columns.Add("序号");
dataTable.Columns.Add("服务厅编号");
dataTable.Columns.Add("服务厅");
dataTable.Columns.Add("处理情况");
dataTable.Columns.Add("处理情况类型");
dataTable.Columns.Add("投诉类型编号");
//获取投诉类型集合
var complaints = new Dictionary <int, string>();
if (result != null && result.Any())
{
foreach (var re in result.Select(r => new { COMPLAIN_TYP_ID = r.COMPLAIN_TYP_ID, COMPLAIN_NAM = r.COMPLAIN_NAM }).Distinct())
{
if (!complaints.ContainsKey(re.COMPLAIN_TYP_ID))
{
complaints.Add(re.COMPLAIN_TYP_ID, re.COMPLAIN_NAM);
dataTable.Columns.Add(re.COMPLAIN_NAM);
}
}
}
ViewBag.Complaints = complaints;
if (result != null && result.Any())
{
var index = 0;
var complaintCount = complaints.Count;
for (var mIndex = 0; mIndex < result.Count; mIndex++)
{
var itemHall = result[mIndex];
var levelCodes = new[] { "N", "R", "U", "H" };
foreach (var levelCode in levelCodes)
{
var itemLevels =
result.Where(r => r.HALL_NO == itemHall.HALL_NO && r.L_Code == levelCode).ToList();
var itemLevel = itemLevels.First();
var dr = dataTable.NewRow();
dr["序号"] = (++index);
dr["服务厅"] = itemHall.HALL_NAM;
dr["服务厅编号"] = itemHall.HALL_NO;
dr["处理情况"] = itemLevel.L_Name;
dr["处理情况类型"] = itemLevel.L_Value;
foreach (var complaint in complaints)
{
var itemComplaint = itemLevels.First(r => r.COMPLAIN_TYP_ID == complaint.Key);
dr["投诉类型编号"] = itemComplaint.COMPLAIN_TYP_ID;
dr[itemComplaint.COMPLAIN_NAM] = itemComplaint.Count_Value;
}
dataTable.Rows.Add(dr);
}
mIndex += (complaintCount * 4 - 1);
}
}
//导出
if (export)
{
var exportData = dataTable.Copy();
exportData.Columns.Remove("服务厅编号");
exportData.Columns.Remove("处理情况类型");
exportData.Columns.Remove("投诉类型编号");
return(AsposeExcelHelper.OutFileToRespone(exportData, "投诉举报分析报表-服务厅", exceltitle));
}
var dataList = dataTable.Rows.Cast <DataRow>().Where(r => r["处理情况"].ToString() == "合计").ToList();
var chartData = dataTable.Clone();
foreach (var dataRow in dataList)
{
chartData.Rows.Add(dataRow.ItemArray);
}
//不需要显示的列名
var noShowColumns = new[] { "序号", "服务厅编号", "处理情况", "处理情况类型", "投诉类型编号" };
foreach (var noShowColumn in noShowColumns)
{
chartData.Columns.Remove(noShowColumn);
}
// 输出图表
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 420, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle);
return(View(dataTable));
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="title"></param>
/// <param name="fileName"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public ActionResult ExportData(string title,
string Listname,
DateTime beginTime,
DateTime endTime,
string orgId)
{
var typeList = CHK_QUALITY_CON_Dao.FindList();
var itemTypeList = SYS_DLSERIAL_Dao.FindList();
var lineTable = new DataTable();
lineTable.Columns.Add("序号", typeof(string));
lineTable.Columns.Add(Listname, typeof(string));
lineTable.Columns.Add("事项大类", typeof(string));
foreach (var item in typeList)
{
lineTable.Columns.Add(item.QUALITY_NAM, typeof(int));
}
if (Listname == "服务厅")
{
var data = new Statistics_DAL().GetStatistics_ServiceSlipAnalysis(beginTime, endTime, UserState.UserID);
var dics = data.GroupBy(m => m.HALL_NAM);
var i = 1;
foreach (var dic in dics)
{
var items = dic.ToList();
var statistics = items.FirstOrDefault();
for (var j = 1; j < itemTypeList.Count(); j++)
{
var r = lineTable.NewRow();
r["序号"] = i;
r[Listname] = statistics.HALL_NAM;
r["事项大类"] = itemTypeList[j].DLS_SERIALNAME;
foreach (var item in typeList)
{
var def =
items.FirstOrDefault(
m =>
m.QUALITY_CD == item.QUALITY_CD &&
m.SSDLSERIALID == itemTypeList[j].DLS_SERIALID);
r[item.QUALITY_NAM] = def == null ? 0 : def.AMOUNT;
}
lineTable.Rows.Add(r);
i++;
}
}
}
else
{
var listStatistics = new Statistics_DAL().GetStatistics_ServiceSlipAnalysisList(beginTime, endTime,
UserState.UserID, orgId, null);
var dics = listStatistics.GroupBy(m => m.STAFF_NAM);
var i = 1;
foreach (var dic in dics)
{
var items = dic.ToList();
var statistics = items.FirstOrDefault();
for (var j = 1; j < itemTypeList.Count(); j++)
{
var r = lineTable.NewRow();
r["序号"] = i;
r[Listname] = statistics.STAFF_NAM;
r["事项大类"] = itemTypeList[j].DLS_SERIALNAME;
foreach (var item in typeList)
{
var def =
items.FirstOrDefault(
m =>
m.QUALITY_CD == item.QUALITY_CD &&
m.DLS_SERIALID == itemTypeList[j].DLS_SERIALID);
r[item.QUALITY_NAM] = def == null ? 0 : def.AMOUNT;
}
lineTable.Rows.Add(r);
i++;
}
}
}
return(AsposeExcelHelper.OutFileToRespone(lineTable, title));
}
public ActionResult TaxpayerEval(
DateTime?beginTime,
DateTime?endTime,
bool export = false)
{
// 判断当前用户组织结构
var power = new SYS_HALL_DAL().GetList(UserState.UserID, "4");
if (power.Count == 1)
{
var hallNo = power.FirstOrDefault().HALL_NO;
return(Redirect(Url.Action("TaxpayerEval_Person", "Statistics", new
{
@beginTime = beginTime,
@endTime = endTime,
@orgId = hallNo
})));
}
// 初始化日期
base.DateTimeInit(ref beginTime, ref endTime);
// 获取数据
var bll = new Statistics_DAL();
var result = bll.GetStatistics_TaxpayerEvalChart(beginTime, endTime, UserState.UserID);
// 控制标题
const string titleName = "纳税人评价分析";
var mainTielt = base.GetOrgName(null, 3);
ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(),
endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(),
endTime.GetValueOrDefault(), false);
if (export)
{
var index = 0;
var data = result.Select(x => new
{
序号 = ++index,
务厅编码 = x.HALL_NO,
务厅名称 = x.ORG_NAM,
很满意 = x.VERY_SATISFY_CNT,
很满意率 = x.VERY_SATISFY_CNT_BFB + "%",
满意 = x.SATISFY_CNT,
满意率 = x.SATISFY_CNT_BFB + "%",
基本满意 = x.COMMON_CNT,
基本满意率 = x.COMMON_CNT_BFB + "%",
满意 = x.UNSATISFY_CNT,
满意率 = x.UNSATISFY_CNT_BFB + "%",
未评价 = x.NON_EVAL_CNT,
未评价率 = x.NON_EVAL_CNT_BFB + "%",
满意度 = x.ManYiDu_BFB + "%"
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(data, "纳税人评价分析报表-服务厅", exceltitle));
}
var chartData = result.Select(x => new
{
务厅名称 = x.ORG_NAM,
很满意 = x.VERY_SATISFY_CNT,
满意 = x.SATISFY_CNT,
基本满意 = x.COMMON_CNT,
满意 = x.UNSATISFY_CNT,
未评价 = x.NON_EVAL_CNT,
}).ToList().ToDataTable();
// 输出图表
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 430, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 430, null, null, subtitle);
return(View(result));
}
public ActionResult TaxpayerEval_Person(
DateTime?beginTime,
DateTime?endTime,
string orgId,
bool export = false)
{
// 初始化日期
base.DateTimeInit(ref beginTime, ref endTime);
// 获取数据
var bll = new Statistics_DAL();
var result = bll.GetStatistics_TaxpayerEvalChart_Person(beginTime, endTime, orgId);
// 控制标题
const string titleName = "纳税人评价分析";
var mainTielt = GetOrgName(orgId, null);
ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(),
endTime.GetValueOrDefault());
var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false);
var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(),
endTime.GetValueOrDefault(), false);
if (export)
{
var index = 0;
var data = result.Select(x => new
{
序号 = ++index,
工号 = x.PersonNo,
姓名 = x.PersonName,
很满意 = x.VERY_SATISFY_CNT,
很满意率 = x.VERY_SATISFY_CNT_BFB + "%",
满意 = x.SATISFY_CNT,
满意率 = x.SATISFY_CNT_BFB + "%",
基本满意 = x.COMMON_CNT,
基本满意率 = x.COMMON_CNT_BFB + "%",
满意 = x.UNSATISFY_CNT,
满意率 = x.UNSATISFY_CNT_BFB + "%",
未评价 = x.NON_EVAL_CNT,
未评价率 = x.NON_EVAL_CNT_BFB + "%",
满意度 = x.ManYiDu_BFB + "%"
}).ToList().ToDataTable();
return(AsposeExcelHelper.OutFileToRespone(data, "纳税人评价分析报表-员工", exceltitle));
}
var chartData = result.Select(x => new
{
姓名 = x.PersonName,
很满意 = x.VERY_SATISFY_CNT,
满意 = x.SATISFY_CNT,
基本满意 = x.COMMON_CNT,
满意 = x.UNSATISFY_CNT,
未评价 = x.NON_EVAL_CNT,
}).ToList().ToDataTable();
// 输出图表
ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 430, subtitle);
ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle);
return(View(result));
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
//[HttpPost]
//[AjaxOnly]
//[HandlerAuthorize(PermissionMode.Ignore)]
//[HandlerMonitor(0, "导出消防设施excel")]
public ActionResult ExportExcel(string condition, string queryJson)
{
Pagination pagination = new Pagination
{
page = 1,
rows = 100000000,
p_kid = "Id",
p_fields = @"CategoryName,RankName,FileName,DutyDept,
to_char(PutDate,'yyyy-MM-dd') as PutDate,NormName,Clause,ApplyScope,
case when IsConform = '0' then '符合' when IsConform = '1' then '不符合' else '' end as IsConform,
Describe,Opinion,to_char(FinishTime,'yyyy-MM-dd hh24:mi') as FinishTime,EvaluatePerson",
p_tablename = "HRS_EVALUATEDETAILS",
conditionJson = "1=1",
sidx = "IsConform desc,CreateDate",
sord = "desc"
};
DataTable data = evaluatedetailsbll.GetPageList(pagination, queryJson);
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig
//{
// Title = "合规性评价",
// TitleFont = "宋体",
// TitleHeight = 30,
// TitlePoint = 25,
// FileName = "合规性评价" + ".xls",
// IsAllSizeColumn = true,
// //每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
// ColumnEntity = new List<ColumnEntity>(){
// new ColumnEntity() {Column = "categoryname", ExcelColumn = "大类", Alignment = "center"},
// new ColumnEntity() {Column = "rankname", ExcelColumn = "小类", Alignment = "center"},
// new ColumnEntity() {Column = "filename", ExcelColumn = "文件编号及名称", Alignment = "center"},
// new ColumnEntity() {Column = "dutydept", ExcelColumn = "颁布部门", Alignment = "center"},
// new ColumnEntity() {Column = "putdate", ExcelColumn = "实施日期", Alignment = "center"},
// new ColumnEntity() {Column = "normname", ExcelColumn = "纳入企业标准的名称", Alignment = "center"},
// new ColumnEntity() {Column = "clause", ExcelColumn = "适用条款", Alignment = "center"},
// new ColumnEntity() {Column = "applyscope", ExcelColumn = "适用范围", Alignment = "center"},
// new ColumnEntity() {Column = "isconform", ExcelColumn = "符合性", Alignment = "center"},
// new ColumnEntity() {Column = "describe", ExcelColumn = "现状符合性描述", Alignment = "center"},
// new ColumnEntity() {Column = "opinion", ExcelColumn = "整改意见", Alignment = "center"},
// new ColumnEntity() {Column = "finishtime", ExcelColumn = "整改截止时间", Alignment = "center"},
// new ColumnEntity() {Column = "evaluateperson", ExcelColumn = "评价人", Alignment = "center"}
// }
//};
////调用导出方法
////ExcelHelper.ExportByAspose(data, excelconfig.FileName, excelconfig.ColumnEntity);
//ExcelHelper.ExcelDownload(data, excelconfig);
//return Success("导出成功。");
for (int i = 0; i < data.Rows.Count; i++)
{
data.Rows[i][0] = i + 1;
//data.Rows[i]["TimeType"] = data.Rows[i]["TimeNum"].ToString() + data.Rows[i]["TimeType"].ToString();
}
string FileUrl = @"\Resource\ExcelTemplate\合规性评价导出.xls";
AsposeExcelHelper.ExecuteResult(data, FileUrl, "合规性评价", "合规性评价");
return(Success("导出成功。"));
}
