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