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