public ActionResult GetPageListJson(Pagination pagination, string queryJson) { var watch = CommonHelper.TimerStart(); AuditCollectEntity para = new AuditCollectEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { var queryParam = queryJson.ToJObject(); if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty()) { DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); para.StartTime = startTime.ToString(); para.EndTime = endTime.ToString(); } //类型 if (!queryParam["keyword"].IsEmpty()) { para.OrganizationName = queryParam["keyword"].ToString(); } if (!queryParam["type"].IsEmpty()) { int type = int.Parse(queryParam["type"].ToString()); para.SubricType = type; } if (!queryParam["TimeSpace"].IsEmpty()) { para.ServiceTime = queryParam["TimeSpace"].ToString(); } } var pageList = new List <AuditCollectEntity>(); try { pageList = AuditReportBLL.Instance.GetAuditCollectPageList(para, ref pagination); } catch (Exception ex) { } var JsonData = new { rows = pageList, total = pagination.total, page = pagination.page, records = pagination.records, costtime = CommonHelper.TimerEnd(watch) }; return(Content(JsonData.ToJson())); }
/// <summary> /// 执行存储过程 执行查询sql语句业务 /// </summary> /// <param name="para"></param> /// <param name="pagination"></param> /// <returns></returns> public List <AuditCollectEntity> GetAuditCollectPageList(AuditCollectEntity para, ref Pagination pagination) { List <AuditCollectEntity> list = new List <AuditCollectEntity>(); try { string pSQL = @"select Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,SubricType,count(1)SubricCount from ( select '0'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,0 as SubricType from tbl_AuditOrder where Status<>4 union all select '1'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,1 as SubricType from tbl_GroupAuditOrder where Status<>4 union all select '2'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId,OrganizationName,ServiceDate,ServiceTime,2 as SubricType from tbl_TakeAuditOrder where Status<>4 )a group by ID, OrganizationId,OrganizationName,ServiceDate,ServiceTime,SubricType"; string sql = @"EXEC proc_GetRecordByPage @0,@1,@2,@3,@4,@5,@6,@7 OUTPUT,@8 OUTPUT"; StringBuilder sbWhere = new StringBuilder(); sbWhere.Append(" 1=1 "); //当前用户对象 var loginOperater = OperatorProvider.Provider.Current(); if (loginOperater != null) { List <string> userDataAuthorize = loginOperater.UserDataAuthorize; if (userDataAuthorize != null && userDataAuthorize.Count > 0) { var str = ""; foreach (var item in userDataAuthorize) { str += string.Format("'{0}',", item); } str = str.Substring(0, str.Length - 1); sbWhere.AppendFormat(" and OrganizationId in({0})", str); } } if (para.OrganizationName != null) { sbWhere.AppendFormat(" and (charindex('{0}',OrganizationName)>0)", para.OrganizationName); } if (para.StartTime != null) { sbWhere.Append(base.FormatParameter(" AND ServiceDate>='{0} 00:00:00'", Converter.ParseDateTime(para.StartTime).ToString("yyyy-MM-dd"))); } if (para.EndTime != null) { sbWhere.Append(base.FormatParameter(" AND ServiceDate<='{0} 23:59:59'", Converter.ParseDateTime(para.EndTime).ToString("yyyy-MM-dd"))); } if (para.SubricType != null) { sbWhere.AppendFormat(" and SubricType={0}", para.SubricType); } if (para.ServiceTime != null) { sbWhere.AppendFormat(" and ServiceTime='{0}'", para.ServiceTime); } //参数 SqlParameter[] pars = { new SqlParameter("@pSQL", pSQL), new SqlParameter("@keyField", "Id"), new SqlParameter("@orderFiled", "ServiceDate desc"), new SqlParameter("@PageSize", pagination.rows), new SqlParameter("@PageIndex", pagination.page), new SqlParameter("@OrderType", "1"), new SqlParameter("@strWhere", sbWhere.ToString()), new SqlParameter("@pRecNums", SqlDbType.Int) { Direction = ParameterDirection.Output }, new SqlParameter("@pRecPages", SqlDbType.Int) { Direction = ParameterDirection.Output } }; var sql2 = PetaPoco.Sql.Builder.Append(sql, pars); // var aa = dbInstance.Fetch<dynamic>(sql2).ToList<dynamic>(); var dbInstance = RCHL_SQLDB.GetInstance(); dbInstance.EnableAutoSelect = false; list = dbInstance.Fetch <AuditCollectEntity>(sql2).ToList(); dbInstance.EnableAutoSelect = true; pagination.records = Convert.ToInt32(pars[7].Value);//输出参数的值 } catch (Exception e) { throw e; } return(list); }
/// <summary> /// /// </summary> /// <param name="para"></param> /// <returns></returns> public List <AuditCollectEntity> GetAuditCollectList(AuditCollectEntity para) { List <AuditCollectEntity> list = new List <AuditCollectEntity>(); try { string pSQL = @"select * from ( select Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,SubricType,count(1)SubricCount from ( select '0'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,0 as SubricType from tbl_AuditOrder where Status<>4 union all select '1'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,1 as SubricType from tbl_GroupAuditOrder where Status<>4 union all select '2'+cast(ServiceDate as varchar(50))+OrganizationId as Id,OrganizationId, OrganizationName,ServiceDate,ServiceTime,2 as SubricType from tbl_TakeAuditOrder where Status<>4 )a group by ID,OrganizationId, OrganizationName,ServiceDate,ServiceTime,SubricType )a"; StringBuilder sbWhere = new StringBuilder(); sbWhere.Append(" where 1=1 "); //当前用户对象 var loginOperater = OperatorProvider.Provider.Current(); if (loginOperater != null) { List <string> userDataAuthorize = loginOperater.UserDataAuthorize; if (userDataAuthorize != null && userDataAuthorize.Count > 0) { var str = ""; foreach (var item in userDataAuthorize) { str += string.Format("'{0}',", item); } str = str.Substring(0, str.Length - 1); sbWhere.AppendFormat(" and OrganizationId in({0})", str); } } if (para.OrganizationName != null) { sbWhere.AppendFormat(" and (charindex('{0}',OrganizationName)>0)", para.OrganizationName); } if (para.StartTime != null) { sbWhere.Append(base.FormatParameter(" AND ServiceDate>='{0} 00:00:00'", Converter.ParseDateTime(para.StartTime).ToString("yyyy-MM-dd"))); } if (para.EndTime != null) { sbWhere.Append(base.FormatParameter(" AND ServiceDate<='{0} 23:59:59'", Converter.ParseDateTime(para.EndTime).ToString("yyyy-MM-dd"))); } if (para.SubricType != null) { sbWhere.AppendFormat(" and SubricType={0}", para.SubricType); } if (para.ServiceTime != null) { sbWhere.AppendFormat(" and ServiceTime='{0}'", para.ServiceTime); } var dbInstance = RCHL_SQLDB.GetInstance(); dbInstance.EnableAutoSelect = false; list = dbInstance.Fetch <AuditCollectEntity>(pSQL + sbWhere.ToString()).ToList(); dbInstance.EnableAutoSelect = true; } catch (Exception e) { throw e; } return(list); }
/// <summary> /// 导出EXCEL /// </summary> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; AuditCollectEntity para = new AuditCollectEntity(); try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty()) { DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); para.StartTime = startTime.ToString(); para.EndTime = endTime.ToString(); } //类型 if (!queryParam["keyword"].IsEmpty()) { para.OrganizationName = queryParam["keyword"].ToString(); } if (!queryParam["type"].IsEmpty()) { int type = int.Parse(queryParam["type"].ToString()); para.SubricType = type; } if (!queryParam["TimeSpace"].IsEmpty()) { para.ServiceTime = queryParam["TimeSpace"].ToString(); } var list = AuditReportBLL.Instance.GetAuditCollectList(para); if (list != null) { foreach (var item in list) { switch (item.SubricType) { case (int)QX360.Model.Enums.AuditType.个人年检: item.SubricTypeName = QX360.Model.Enums.AuditType.个人年检.ToString(); break; case (int)QX360.Model.Enums.AuditType.集团年检: item.SubricTypeName = QX360.Model.Enums.AuditType.集团年检.ToString(); break; case (int)QX360.Model.Enums.AuditType.代检: item.SubricTypeName = QX360.Model.Enums.AuditType.代检.ToString(); break; default: break; } } //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "年审车辆预约报表"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "年审车辆预约报表.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubricTypeName", ExcelColumn = "预约类型", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "OrganizationName", ExcelColumn = "年检机构", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceDate", ExcelColumn = "预约时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时段", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubricCount", ExcelColumn = "预约总数", Width = 15 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <AuditCollectEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
public List <AuditCollectEntity> GetAuditCollectList(AuditCollectEntity para) { List <AuditCollectEntity> list = InstanceDAL.GetAuditCollectList(para); return(list); }
/// <summary> /// 构造方法 /// </summary> public List <AuditCollectEntity> GetAuditCollectPageList(AuditCollectEntity para, ref Pagination pagination) { List <AuditCollectEntity> list = InstanceDAL.GetAuditCollectPageList(para, ref pagination); return(list); }