/// <summary>
/// 排序报表显示字段
/// </summary>
/// <param name="info">选取报表信息</param>
/// <param name="dt">报表全部信息</param>
/// <returns>排序后的报表</returns>
public DataTable SortColumn(CustomReportInfo info, DataTable dt)
{
foreach (string columnName in SQLUtil.GetTableColumnNames(dt))
{
if (!info.ContainField(columnName))
{
dt.Columns.Remove(columnName);
}
}
List <CustRptTemplateFieldInfo> fields = this.custRptDao.GetCustRptTemplateFields(info.Type.ID);
string fieldDesc = null;
for (int i = 0; i < info.Fields.Count; i++)
{
dt.Columns[info.Fields[i].FieldName].SetOrdinal(i);
fieldDesc = (from CustRptTemplateFieldInfo temp in fields where temp.FieldName.Equals(info.Fields[i].FieldName) select temp.FieldDesc).FirstOrDefault();
if (!string.IsNullOrEmpty(fieldDesc))
{
dt.Columns[info.Fields[i].FieldName].ColumnName = fieldDesc;
}
}
if (info.ID > 0)
{
this.custRptDao.UpdateCustRptRunDate(info.ID);
}
return(dt);
}
/// <summary>
/// 获取自定义报表类型为设备的报表信息
/// </summary>
/// <param name="info">报表内容</param>
/// <param name="field">筛选时间字段</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">截至时间</param>
/// <returns>报表内容</returns>
public DataTable QueryCustRpt4Equipment(CustomReportInfo info, string field, DateTime startTime, DateTime endTime)
{
DataTable dtEquipment = this.custRptDao.QueryCustRpt4Equipment(field, startTime, endTime);
List <string> fieldsInRpt = SQLUtil.GetStringListFromObjectList(info.Fields, "FieldName");
foreach (DataRow dr in dtEquipment.Rows)
{
GetEquipmentDesc(dr, fieldsInRpt);
}
return(SortColumn(info, dtEquipment));
}
/// <summary>
/// 更新自定义报表信息
/// </summary>
/// <param name="info">自定义报表信息</param>
public void UpdateCustRpt(CustomReportInfo info)
{
sqlStr = " UPDATE tblCustomReport " +
" SET Name=@Name,UpdateDate=@UpdateDate" +
" WHERE ID=@ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ID", SqlDbType.Int).Value = info.ID;
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Name);
command.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = DateTime.Now;
command.ExecuteNonQuery();
}
}
/// <summary>
/// 获取自定义报表类型为派工单的报表信息
/// </summary>
/// <param name="info">报表内容</param>
/// <param name="field">筛选时间字段</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">截至时间</param>
/// <returns>报表内容</returns>
public DataTable QueryCustRpt4Dispatch(CustomReportInfo info, string field, DateTime startTime, DateTime endTime)
{
DataTable dtDispatch = this.custRptDao.QueryCustRpt4Dispatch(field, startTime, endTime);
List <string> fieldsInRpt = SQLUtil.GetStringListFromObjectList(info.Fields, "FieldName");
foreach (DataRow dr in dtDispatch.Rows)
{
GetDispatchDesc(dr, fieldsInRpt);
GetDispatchJournalDesc(dr, fieldsInRpt);
GetDispatchReportDesc(dr, fieldsInRpt);
GetRequestDesc(dr, fieldsInRpt);
}
return(SortColumn(info, dtDispatch));
}
/// <summary>
/// 新增自定义报表
/// </summary>
/// <param name="info">自定义报表信息</param>
/// <returns>自定义报表ID</returns>
public int AddCustomRpt(CustomReportInfo info)
{
sqlStr = " INSERT INTO tblCustomReport(TypeID,Name,CreateUserID,CreateUserName,CreatedDate)" +
" VALUES (@TypeID,@Name,@CreateUserID,@CreateUserName,GETDATE())" +
" SELECT @@IDENTITY";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@TypeID", SqlDbType.Int).Value = info.Type.ID;
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Name);
command.Parameters.Add("@CreateUserID", SqlDbType.Int).Value = info.CreateUser.ID;
command.Parameters.Add("@CreateUserName", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.CreateUser.Name);
info.ID = SQLUtil.ConvertInt(command.ExecuteScalar());
}
return(info.ID);
}
public ActionResult RunCustRpt(CustomReportInfo info, string field, DateTime startTime, DateTime endTime)
{
if (CheckSession() == false)
{
return(Json(ResultModelBase.CreateTimeoutModel(), JsonRequestBehavior.AllowGet));
}
if (CheckSessionID() == false)
{
return(Json(ResultModelBase.CreateLogoutModel(), JsonRequestBehavior.AllowGet));
}
ResultModelBase result = new ResultModelBase();
try
{
endTime = endTime.AddDays(1);
DataTable infoList = new DataTable();
if (info.Type.ID == CustomReportInfo.CustRptType.Equipment)
{
infoList = this.customRptManager.QueryCustRpt4Equipment(info, field, startTime, endTime);
}
if (info.Type.ID == CustomReportInfo.CustRptType.Contract)
{
infoList = this.customRptManager.QueryCustRpt4Contract(info, field, startTime, endTime);
}
if (info.Type.ID == CustomReportInfo.CustRptType.Request)
{
infoList = this.customRptManager.QueryCustRpt4Request(info, field, startTime, endTime);
}
if (info.Type.ID == CustomReportInfo.CustRptType.Dispatch)
{
infoList = this.customRptManager.QueryCustRpt4Dispatch(info, field, startTime, endTime);
}
MemoryStream ms = ExportUtil.ToExcel(infoList);
Response.AddHeader("Set-Cookie", "fileDownload=true; path=/");
return(File(ms, "application/excel", info.Name + ".xlsx"));
}
catch (Exception ex)
{
NLog.LogManager.GetCurrentClassLogger().Error(ex, ex.Message);
result.SetFailed(ResultCodes.SystemError, ControlManager.GetSettingInfo().ErrorMessage);
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 根据id获取自定义报表内容
/// </summary>
/// <param name="id">报表id</param>
/// <returns>自定义报表内容</returns>
public CustomReportInfo GetReportByID(int id)
{
CustomReportInfo info = null;
sqlStr = "SELECT cr.*,crf.* FROM tblCustomReport cr" +
" LEFT JOIN tblCustRptField crf ON cr.ID = crf.CustomReportID " +
" WHERE cr.ID = " + id;
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
if (info == null)
{
info = new CustomReportInfo(dr);
}
info.Fields.Add(new CustRptFieldInfo(dr));
}
}
}
return(info);
}
/// <summary>
/// 保存自定义报表信息
/// </summary>
/// <param name="info">自定义报表信息</param>
/// <returns>自定义报表编号</returns>
public int SaveCusRpt(CustomReportInfo info)
{
if (info.ID == 0)
{
info.ID = this.custRptDao.AddCustomRpt(info);
foreach (CustRptFieldInfo field in info.Fields)
{
this.custRptDao.AddCusrRptField(info.ID, field.TableName, field.FieldName);
}
}
else
{
this.custRptDao.DeleteCustomRptFields(info.ID);
foreach (CustRptFieldInfo field in info.Fields)
{
this.custRptDao.AddCusrRptField(info.ID, field.TableName, field.FieldName);
}
this.custRptDao.UpdateCustRpt(info);
}
return(info.ID);
}
public JsonResult SaveCustRept(CustomReportInfo info)
{
if (CheckSession(false) == false)
{
return(Json(ResultModelBase.CreateTimeoutModel(), JsonRequestBehavior.AllowGet));
}
if (CheckSessionID() == false)
{
return(Json(ResultModelBase.CreateLogoutModel(), JsonRequestBehavior.AllowGet));
}
ResultModel <Int32> result = new ResultModel <Int32>();
try
{
result.Data = this.customRptManager.SaveCusRpt(info);
}
catch (Exception ex)
{
NLog.LogManager.GetCurrentClassLogger().Error(ex, ex.Message);
result.SetFailed(ResultCodes.SystemError, ControlManager.GetSettingInfo().ErrorMessage);
}
return(JsonResult(result));
}
