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);
}
