private void ImportDeviceRepairRecord(HttpContext context, HttpPostedFile file) { var dt = OpenXmlHelper.Import(file.InputStream); var drc = dt.Rows; if (drc.Count == 0) { context.Response.Write(ResResult.ResJsonString(false, MC.Import_NotDataError, "")); return; } var currTime = DateTime.Now; DateTime time = DateTime.MinValue; var list = new List <InfoneDeviceRepairRecordInfo>(); var userId = WebCommon.GetUserId(); foreach (DataRow dr in drc) { if (dr["日期"] != null) { DateTime.TryParse(dr["日期"].ToString(), out time); } if (time == DateTime.MinValue) { throw new ArgumentException(MC.Import_InvalidError); } var backDate = DateTime.MinValue; DateTime.TryParse(dr["归还日期"].ToString(), out backDate); if (backDate == DateTime.MinValue) { backDate = DateTime.Parse("1754-01-01"); } var modelInfo = new InfoneDeviceRepairRecordInfo(Guid.Empty, userId, time, dr["客户"].ToString(), dr["序列号"].ToString(), dr["型号"].ToString(), dr["故障原因"].ToString(), dr["解决方案"].ToString(), dr["客户问题"].ToString(), dr["配件"].ToString(), dr["处理情况"].ToString(), dr["是否修好"].ToString(), dr["交接人"].ToString(), dr["是否归还"].ToString() == "是" ? true : false, backDate, dr["登记人"].ToString(), dr["备注"].ToString(), currTime); list.Add(modelInfo); } var bll = new InfoneDeviceRepairRecord(); var index = 0; foreach (var model in list) { model.UserId = userId; if (bll.Insert(model) < 1) { throw new ArgumentException(string.Format("{0}", index > 0 ? "部分数据已经成功导入,但是执行到第“" + index + "”行时发生异常" : "数据导入失败,行“" + index + "”发生异常")); } index++; } context.Response.Write(ResResult.ResJsonString(true, "导入成功", "")); }
private void Bind() { BindControl bc = new BindControl(); Guid Id = Guid.Empty; if (!string.IsNullOrWhiteSpace(Request.QueryString["Id"])) { Guid.TryParse(Request.QueryString["Id"], out Id); } if (!Id.Equals(Guid.Empty)) { var bll = new InfoneDeviceRepairRecord(); var model = bll.GetModel(Id); if (model != null) { hId.Value = model.Id.ToString(); txtRecordDate.Value = model.RecordDate.ToString("yyyy-MM-dd").Replace("1754-01-01", ""); txtCustomer.Value = model.Customer; txtSerialNumber.Value = model.SerialNumber; txtDeviceModel.Value = model.DeviceModel; txtFaultCause.Value = model.FaultCause; txtSolveMethod.Value = model.SolveMethod; txtCustomerProblem.Value = model.CustomerProblem; txtDevicePart.Value = model.DevicePart; txtTreatmentSituation.Value = model.TreatmentSituation; txtHandoverPerson.Value = model.HandoverPerson; txtBackDate.Value = model.BackDate.ToString("yyyy-MM-dd").Replace("1754-01-01", ""); txtRegisteredPerson.Value = model.RegisteredPerson; txtRemark.Value = model.Remark; bc.BindDdl(ddlIsBack, typeof(EnumData.EnumIsOk), model.IsBack ? "是" : "否", ""); bc.BindDdl(ddlWhetherFix, typeof(EnumData.EnumWhetherFix), model.WhetherFix, ""); } } else { bc.BindDdl(ddlIsBack, typeof(EnumData.EnumIsOk), "", ""); bc.BindDdl(ddlWhetherFix, typeof(EnumData.EnumWhetherFix), "", ""); } }
private void GetInfoneDeviceRepairRecordList(HttpContext context, int pageIndex, int pageSize, string keyword) { var bll = new InfoneDeviceRepairRecord(); int totalRecord = 0; StringBuilder sqlWhere = null; ParamsHelper parms = null; SqlParameter parm = null; if (!string.IsNullOrWhiteSpace(keyword)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(2000); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (drr.Customer like @Keyword or drr.SerialNumber like @Keyword or drr.DeviceModel like @Keyword or drr.FaultCause like @Keyword or drr.SolveMethod like @Keyword or drr.CustomerProblem like @Keyword or drr.DevicePart like @Keyword or drr.TreatmentSituation like @Keyword or drr.HandoverPerson like @Keyword or drr.RegisteredPerson like @Keyword or drr.Remark like @Keyword) "); parm = new SqlParameter("@Keyword", SqlDbType.NVarChar, 50); parm.Value = "%" + keyword + "%"; parms.Add(parm); } DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.Form["StartDate"])) { DateTime.TryParse(context.Request.Form["StartDate"], out startDate); } if (!string.IsNullOrWhiteSpace(context.Request.Form["EndDate"])) { DateTime.TryParse(context.Request.Form["EndDate"], out endDate); } if (startDate != DateTime.MinValue && endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(1000); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (drr.RecordDate between @StartDate and @EndDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } else { if (startDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(1000); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (drr.RecordDate >= @StartDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); } if (endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(1000); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (drr.RecordDate <= @EndDate) "); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } } var backDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.Form["BackDate"])) { DateTime.TryParse(context.Request.Form["BackDate"], out backDate); } if (backDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(500); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and drr.BackDate = @BackDate "); parm = new SqlParameter("@BackDate", SqlDbType.DateTime); parm.Value = backDate; parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.Form["WhetherFix"])) { if (sqlWhere == null) { sqlWhere = new StringBuilder(300); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and drr.WhetherFix = @WhetherFix "); parm = new SqlParameter("@WhetherFix", SqlDbType.NVarChar, 20); parm.Value = HttpUtility.UrlDecode(context.Request.Form["WhetherFix"].Trim()); parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.Form["IsBack"])) { if (sqlWhere == null) { sqlWhere = new StringBuilder(50); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and drr.IsBack = @IsBack "); parm = new SqlParameter("@IsBack", SqlDbType.Bit); parm.Value = context.Request.Form["IsBack"].Trim() == "1"; parms.Add(parm); } var list = bll.GetListByJoin(pageIndex, pageSize, out totalRecord, sqlWhere == null ? "" : sqlWhere.ToString(), parms == null ? null : parms.ToArray()); context.Response.Write(ResResult.ResJsonString(true, "", "{\"total\":" + totalRecord + ",\"rows\":" + JsonConvert.SerializeObject(list) + "}")); }
private void ExportDeviceRepairRecord(HttpContext context) { try { #region 动态创建查询条件 var sqlWhere = new StringBuilder(1000); var parms = new ParamsHelper(); SqlParameter parm = null; var keyword = string.Empty; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["keyword"])) { keyword = HttpUtility.UrlDecode(context.Request.QueryString["keyword"]).Trim(); } if (!string.IsNullOrWhiteSpace(keyword)) { sqlWhere.Append(@"and (drr.Customer like @Customer or drr.SerialNumber like @SerialNumber or drr.DeviceModel like @DeviceModel or drr.FaultCause like @FaultCause or drr.SolveMethod like @SolveMethod or drr.CustomerProblem like @CustomerProblem or drr.DevicePart like @DevicePart or drr.TreatmentSituation like @TreatmentSituation or drr.HandoverPerson like @HandoverPerson or drr.RegisteredPerson like @RegisteredPerson or drr.Remark like @Remark) "); parms.Add(new SqlParameter("@Customer", "%" + keyword + "%")); parms.Add(new SqlParameter("@SerialNumber", "%" + keyword + "%")); parms.Add(new SqlParameter("@DeviceModel", "%" + keyword + "%")); parms.Add(new SqlParameter("@FaultCause", "%" + keyword + "%")); parms.Add(new SqlParameter("@SolveMethod", "%" + keyword + "%")); parms.Add(new SqlParameter("@CustomerProblem", "%" + keyword + "%")); parms.Add(new SqlParameter("@DevicePart", "%" + keyword + "%")); parms.Add(new SqlParameter("@TreatmentSituation", "%" + keyword + "%")); parms.Add(new SqlParameter("@HandoverPerson", "%" + keyword + "%")); parms.Add(new SqlParameter("@RegisteredPerson", "%" + keyword + "%")); parms.Add(new SqlParameter("@Remark", "%" + keyword + "%")); } DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["startDate"])) { DateTime.TryParse(context.Request.QueryString["startDate"], out startDate); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["endDate"])) { DateTime.TryParse(context.Request.QueryString["endDate"], out endDate); } if (startDate != DateTime.MinValue && endDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate between @StartDate and @EndDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } else { if (startDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate >= @StartDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); } if (endDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate <= @EndDate) "); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["backDate"])) { sqlWhere.AppendFormat(@"and drr.BackDate = @BackDate "); parm = new SqlParameter("@BackDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(context.Request.QueryString["backDate"]); parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["whetherFix"])) { sqlWhere.AppendFormat(@"and drr.WhetherFix = @WhetherFix "); parm = new SqlParameter("@WhetherFix", SqlDbType.NVarChar, 20); parm.Value = HttpUtility.UrlDecode(context.Request.QueryString["whetherFix"].Trim()); parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["isBack"])) { sqlWhere.AppendFormat(@"and drr.IsBack = @IsBack "); parm = new SqlParameter("@IsBack", SqlDbType.Bit); parm.Value = context.Request.QueryString["isBack"].Trim() == "1"; parms.Add(parm); } #endregion var bll = new InfoneDeviceRepairRecord(); var dt = bll.GetExportToExcelData(sqlWhere == null ? "" : sqlWhere.ToString(), parms == null ? null : parms.ToArray()); OpenXmlHelper.Export(context, dt); } catch (Exception ex) { context.Response.Write(ResResult.ResJsonString(false, ex.Message, "")); } }