private void GetAJReportByUnit(HttpContext context) { string strWhere = ""; List <object> values = new List <object>(); int pageIndex = Convert.ToInt32(context.Request["page"]); int pageSize = Convert.ToInt32(context.Request["pagesize"]); string key = context.Request["key"]; //部门受案号 string timebegin = context.Request["timebegin"]; //开始日期 string timeend = context.Request["timeend"]; //结束日期 string unit = context.Request["unit"]; //承办单位 string ywbm = context.Request["ywbm"]; string caseajlb = context.Request["caseajlb"]; //排序 string sortname = context.Request["sortname"]; string sortorder = context.Request["sortorder"]; if (string.IsNullOrEmpty(sortname) || sortname.ToLower() == "cbdw_mc") { sortname = "CBDW_BM"; } sortname += " " + sortorder; // string qxWhere = ""; // qxWhere = " AND DWBM = '" + UserInfo.DWBM + "' AND JSBM in (" + Jsbms + ") AND BMBM in (" + Bmbms + ") AND QXLX = 0"; //选中时,筛选单位,否则查询所有有权限数据 EDRS.BLL.XT_DM_QX bllQx = new EDRS.BLL.XT_DM_QX(context.Request); if (!string.IsNullOrEmpty(unit)) { string dwbms = unit.Replace(";", ","); strWhere += " and CBDW_BM in (" + StringPlus.ReplaceSingle(dwbms) + ")"; } else if (string.IsNullOrEmpty(unit)) { //DataSet dsDwQx = bllQx.GetDwList(base.Jsbms, base.UserInfo.DWBM, base.Bmbms, ""); //if (dsDwQx != null && dsDwQx.Tables.Count > 0 && dsDwQx.Tables[0].Rows.Count > 0) //{ // string dwbms = ""; // foreach (DataRow dr in dsDwQx.Tables[0].Rows) // { // dwbms += dr["QXBM"] + ","; // } // strWhere += " and AJ.CBDW_BM in (" + StringPlus.ReplaceSingle(dwbms.Substring(0, dwbms.Length - 1)) + ")"; strWhere += " and AJ.CBDW_BM in (select distinct QXBM FROM XT_DM_QX where 1=1 AND JSBM IN (" + base.Jsbms + ") AND QXLX=0 AND trim(DWBM) = '" + base.UserInfo.DWBM + "' AND BMBM in (" + base.Bmbms + ") )"; //// where_dw = " and jz.dwbm in (" + StringPlus.ReplaceSingle(dwbms.Substring(0, dwbms.Length - 1)) + ")"; //} //else // strWhere += " and AJ.CBDW_BM = " + base.UserInfo.DWBM; } //案件类别 if (!string.IsNullOrEmpty(caseajlb)) { string caseajlbs = caseajlb.Replace(";", ","); strWhere += " and trim(AJ.AJLB_BM) in (" + caseajlbs + ")"; } else if (string.IsNullOrEmpty(caseajlb)) { //DataSet dsLbQx = bllQx.GetLBList(base.Jsbms, base.UserInfo.DWBM, base.Bmbms, ""); //if (dsLbQx != null && dsLbQx.Tables.Count > 0 && dsLbQx.Tables[0].Rows.Count > 0) //{ // string ajlbs = ""; // foreach (DataRow dr in dsLbQx.Tables[0].Rows) // { // ajlbs += dr["QXBM"] + ","; // } // strWhere += " and trim(aj.ajlb_bm) in (" + StringPlus.ReplaceSingle(ajlbs.Substring(0, ajlbs.Length - 1)) + ")"; strWhere += " and trim(aj.ajlb_bm) in (select distinct QXBM FROM XT_DM_QX where 1=1 AND JSBM IN (" + base.Jsbms + ") AND QXLX=1 AND trim(DWBM) = '" + base.UserInfo.DWBM + "' AND BMBM in (" + base.Bmbms + ") )"; //} } //业务 if (!string.IsNullOrEmpty(ywbm)) { strWhere += " and LB.YWBM='" + StringPlus.ReplaceSingle(ywbm) + "'"; } //strWhere += " and (select count(1) from yx_dzjz_jzml y where y.Bmsah=T1.Bmsah) > 0";//已关联 if (!string.IsNullOrEmpty(timebegin)) { strWhere += " and aj.cjsj >= to_date('" + Convert.ToDateTime(timebegin).ToShortDateString() + "','yyyy-mm-dd')"; } if (!string.IsNullOrEmpty(timeend)) { strWhere += " and aj.cjsj <= to_date('" + Convert.ToDateTime(timeend).AddDays(1).ToShortDateString() + "','yyyy-mm-dd')"; } //单位权限 //List<string> unitRoleList = GetDwBm(context,UserRole.DWBM, UserRole.BMBM, UserRole.JSBM); EDRS.BLL.EDRS_Report bll = new EDRS.BLL.EDRS_Report(context.Request); int count = 0; //1-8 修改存储过程 DataSet ds = bll.GetCaseGroupByUnit(strWhere, UserInfo.DWBM, UserInfo.GH, (pageSize * pageIndex), (pageSize * pageIndex) - pageSize + 1, sortname, out count, values.ToArray()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { DataTable dt = ds.Tables[0].Copy(); //读取配置文件中的配置 int?isLocalAjxx = EDRS.Common.ConfigHelper.GetConfigInt("IsLocalAjxx"); if (isLocalAjxx == 1) { string msg = ""; DateTime?b_date = null; if (!string.IsNullOrEmpty(timebegin)) { b_date = Convert.ToDateTime(timebegin); } DateTime?e_date = null; if (!string.IsNullOrEmpty(timeend)) { e_date = Convert.ToDateTime(timeend); } EDRS.Common.IceServicePrx iceprx = new IceServicePrx(); EDRS.BLL.TYYW_GG_AJJBXX ajbll = new EDRS.BLL.TYYW_GG_AJJBXX(context.Request); msg = ajbll.GetConfiguration(UserInfo.DWBM, UserInfo.GH, iceprx); if (!string.IsNullOrEmpty(msg)) { context.Response.Write(msg); } foreach (DataRow dr in dt.Rows) { dr["AJNUM"] = iceprx.GetAjjbxxCount(null, dr["CBDW_BM"].ToString(), b_date, e_date, true, out msg); dr["WZNUM"] = int.Parse(dr["AJNUM"].ToString()) - int.Parse(dr["ZZNUM"].ToString()); dr["ZZLNUM"] = Math.Round((decimal.Parse(dr["ZZNUM"].ToString()) / decimal.Parse(dr["AJNUM"].ToString())) * 100, 2); } } //获取json数据 OperateLog.AddLog(OperateLog.LogType.卷宗业务情况Web, "查询单位卷宗统计列表数据成功!", UserInfo, UserRole, context.Request); string json = "{\"Rows\":" + dt.ToDatagridJson() + ",\"Total\":" + count + "}";// EDRS.Common.JsonHelper.JsonString(ds.Tables[0]); context.Response.Write(json); } else { OperateLog.AddLog(OperateLog.LogType.卷宗业务情况Web, "查询单位卷宗统计列表数据失败!", UserInfo, UserRole, context.Request); //获取json数据 string json = "{\"Rows\":" + "[]" + ",\"Total\":" + count + "}";// EDRS.Common.JsonHelper.JsonString(ds.Tables[0]); context.Response.Write(json); } }
/// <summary> /// 导出Excel /// </summary> /// <returns></returns> private string DeriveData() { string strWhere = ""; List <object> values = new List <object>(); int pageIndex = Convert.ToInt32(Request["page"]); int pageSize = Convert.ToInt32(Request["pagesize"]); string key = Request["key"]; //部门受案号 string timebegin = Request["timebegin"]; //开始日期 string timeend = Request["timeend"]; //结束日期 string unit = Request["unit"]; //承办单位 string ywbm = Request["ywbm"]; string caseajlb = Request["caseajlb"]; //排序 string sortname = Request["sortname"]; string sortorder = Request["sortorder"]; if (string.IsNullOrEmpty(sortname) || sortname.ToLower() == "cbdw_mc") { sortname = "CBDW_BM"; } sortname += " " + sortorder; // string qxWhere = ""; // qxWhere = " AND DWBM = '" + UserInfo.DWBM + "' AND JSBM in (" + Jsbms + ") AND BMBM in (" + Bmbms + ") AND QXLX = 0"; //选中时,筛选单位,否则查询所有有权限数据 EDRS.BLL.XT_DM_QX bllQx = new EDRS.BLL.XT_DM_QX(Request); if (!string.IsNullOrEmpty(unit)) { string dwbms = unit.Replace(";", ","); strWhere += " and CBDW_BM in (" + StringPlus.ReplaceSingle(dwbms) + ")"; } else if (string.IsNullOrEmpty(unit)) { strWhere += " and AJ.CBDW_BM in (select distinct QXBM FROM XT_DM_QX where 1=1 AND JSBM IN (" + base.Jsbms + ") AND QXLX=0 AND trim(DWBM) = '" + base.UserInfo.DWBM + "' AND BMBM in (" + base.Bmbms + ") )"; } //案件类别 if (!string.IsNullOrEmpty(caseajlb)) { string caseajlbs = caseajlb.Replace(";", ","); strWhere += " and trim(AJ.AJLB_BM) in (" + caseajlbs + ")"; } else if (string.IsNullOrEmpty(caseajlb)) { strWhere += " and trim(aj.ajlb_bm) in (select distinct QXBM FROM XT_DM_QX where 1=1 AND JSBM IN (" + base.Jsbms + ") AND QXLX=1 AND trim(DWBM) = '" + base.UserInfo.DWBM + "' AND BMBM in (" + base.Bmbms + ") )"; } //业务 if (!string.IsNullOrEmpty(ywbm)) { strWhere += " and LB.YWBM='" + StringPlus.ReplaceSingle(ywbm) + "'"; } //strWhere += " and (select count(1) from yx_dzjz_jzml y where y.Bmsah=T1.Bmsah) > 0";//已关联 if (!string.IsNullOrEmpty(timebegin)) { strWhere += " and aj.cjsj >= to_date('" + Convert.ToDateTime(timebegin).ToShortDateString() + "','yyyy-mm-dd')"; } if (!string.IsNullOrEmpty(timeend)) { strWhere += " and aj.cjsj <= to_date('" + Convert.ToDateTime(timeend).AddDays(1).ToShortDateString() + "','yyyy-mm-dd')"; } EDRS.BLL.EDRS_Report bll = new EDRS.BLL.EDRS_Report(Request); int count = 0; //1-8 修改存储过程 DataSet ds = bll.GetCaseGroupByUnit(strWhere, UserInfo.DWBM, UserInfo.GH, (pageSize * pageIndex), (pageSize * pageIndex) - pageSize + 1, sortname, out count, values.ToArray()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { DataTable dt = ds.Tables[0].Copy(); //读取配置文件中的配置 int?isLocalAjxx = EDRS.Common.ConfigHelper.GetConfigInt("IsLocalAjxx"); if (isLocalAjxx == 1) { string msg = ""; DateTime?b_date = null; if (!string.IsNullOrEmpty(timebegin)) { b_date = Convert.ToDateTime(timebegin); } DateTime?e_date = null; if (!string.IsNullOrEmpty(timeend)) { e_date = Convert.ToDateTime(timeend); } EDRS.Common.IceServicePrx iceprx = new IceServicePrx(); EDRS.BLL.TYYW_GG_AJJBXX ajbll = new EDRS.BLL.TYYW_GG_AJJBXX(Request); msg = ajbll.GetConfiguration(UserInfo.DWBM, UserInfo.GH, iceprx); if (!string.IsNullOrEmpty(msg)) { ReturnString.JsonToString(Prompt.error, msg, null); } foreach (DataRow dr in dt.Rows) { dr["AJNUM"] = iceprx.GetAjjbxxCount(null, dr["CBDW_BM"].ToString(), b_date, e_date, true, out msg); dr["WZNUM"] = int.Parse(dr["AJNUM"].ToString()) - int.Parse(dr["ZZNUM"].ToString()); dr["ZZLNUM"] = Math.Round((decimal.Parse(dr["ZZNUM"].ToString()) / decimal.Parse(dr["AJNUM"].ToString())) * 100, 2); } } string ExcelFolder = "ExcelFolder";// Assistant.GetConfigString("ExcelFolder"); //生成列的中文对应表 //Dictionary<string, string> nameList = new Dictionary<string, string>(); //nameList.Add("CBDW_MC", "承办单位"); //nameList.Add("AJNUM", ((VersionName)0).ToString()+ "受理数"); //nameList.Add("ZZNUM", "已制作数"); //nameList.Add("WZNUM", "未制作数"); //nameList.Add("ZZLNUM", "制作率"); //Dictionary<string, object> valueList = new Dictionary<string, object>(); //valueList.Add("ZZLNUM", "%"); //利用excel对象 string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; string msgs = string.Empty; if (dt.Rows.Count > 0) { dt.Columns.Remove("Ro"); dt.Columns.Remove("CBDW_BM"); dt.Columns["CBDW_MC"].ColumnName = "承办单位"; dt.Columns["AJNUM"].ColumnName = ((VersionName)0).ToString() + "受理数"; dt.Columns["ZZNUM"].ColumnName = "已制作数"; dt.Columns["WZNUM"].ColumnName = "未制作数"; dt.Columns["ZZLNUM"].ColumnName = "制作率"; msgs = DataToExcel_Ex.Export(dt, "卷宗制作率统计", Server.MapPath("/" + ExcelFolder + "/" + filename)); //filename = dte.DataExcel(dt, "卷宗制作率统计", FilePath, nameList, valueList); } if (string.IsNullOrEmpty(msgs)) { return(ReturnString.JsonToString(Prompt.win, "/" + ExcelFolder + "/" + filename, null)); } else { return(ReturnString.JsonToString(Prompt.error, StringPlus.String2Json(msgs), null)); } } return(ReturnString.JsonToString(Prompt.error, "导出失败", null)); }