private void GetInfoneDeviceBorrowRecordList(HttpContext context, int pageIndex, int pageSize, string keyword, string typeName)
{
var bll = new InfoneDeviceBorrowRecord();
int totalRecord = 0;
StringBuilder sqlWhere = null;
ParamsHelper parms = null;
#region 构造查询条件
if (!string.IsNullOrWhiteSpace(keyword))
{
if (parms == null)
{
parms = new ParamsHelper();
}
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(1000);
}
sqlWhere.Append("and (Customer like @Keyword or SerialNumber like @Keyword or DeviceModel like @Keyword or DevicePart like @Keyword or PartStatus like @Keyword or ProjectAbout like @Keyword or SaleMan like @Keyword or Register like @Keyword or Remark like @Keyword) ");
var parm = new SqlParameter("@Keyword", SqlDbType.NVarChar, 20);
parm.Value = parm.Value = "%" + keyword + "%";
parms.Add(parm);
}
if (!string.IsNullOrWhiteSpace(typeName))
{
if (parms == null)
{
parms = new ParamsHelper();
}
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(100);
}
sqlWhere.Append("and FunType=@FunType ");
var parm = new SqlParameter("@FunType", SqlDbType.NVarChar, 20);
parm.Value = parm.Value = typeName;
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(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate between @StartDate and @EndDate) ");
var 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(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate >= @StartDate) ");
var parm = new SqlParameter("@StartDate", SqlDbType.DateTime);
parm.Value = startDate;
parms.Add(parm);
}
if (endDate != DateTime.MinValue)
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate <= @EndDate) ");
var 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 BackDate = @BackDate ");
var parm = new SqlParameter("@BackDate", SqlDbType.DateTime);
parm.Value = backDate;
parms.Add(parm);
}
var isBack = false;
if (!string.IsNullOrWhiteSpace(context.Request.Form["IsBack"]) && bool.TryParse(context.Request.Form["IsBack"], out isBack))
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(100);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and IsBack = @IsBack ");
var parm = new SqlParameter("@IsBack", SqlDbType.Bit);
parm.Value = isBack;
parms.Add(parm);
}
#endregion
var list = bll.GetListByJoin(pageIndex, pageSize, out totalRecord, sqlWhere == null ? null : sqlWhere.ToString(), parms == null ? null : parms.ToArray());
context.Response.Write(ResResult.ResJsonString(true, "", "{\"total\":" + totalRecord + ",\"rows\":" + JsonConvert.SerializeObject(list) + "}"));
}
private void ExportDeviceBorrowRecord(HttpContext context)
{
try
{
StringBuilder sqlWhere = null;
ParamsHelper parms = null;
#region 构造查询条件
var keyword = context.Request.QueryString["Keyword"];
if (!string.IsNullOrWhiteSpace(keyword))
{
if (parms == null)
{
parms = new ParamsHelper();
}
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(1000);
}
sqlWhere.Append("and (Customer like @Keyword or SerialNumber like @Keyword or DeviceModel like @Keyword or DevicePart like @Keyword or PartStatus like @Keyword or ProjectAbout like @Keyword or SaleMan like @Keyword or Register like @Keyword or Remark like @Keyword) ");
var parm = new SqlParameter("@Keyword", SqlDbType.NVarChar, 20);
parm.Value = parm.Value = "%" + keyword + "%";
parms.Add(parm);
}
var typeName = context.Request.QueryString["TypeName"];
if (!string.IsNullOrWhiteSpace(typeName))
{
if (parms == null)
{
parms = new ParamsHelper();
}
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(100);
}
sqlWhere.Append("and FunType=@FunType ");
var parm = new SqlParameter("@FunType", SqlDbType.NVarChar, 20);
parm.Value = parm.Value = typeName;
parms.Add(parm);
}
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)
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate between @StartDate and @EndDate) ");
var 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(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate >= @StartDate) ");
var parm = new SqlParameter("@StartDate", SqlDbType.DateTime);
parm.Value = startDate;
parms.Add(parm);
}
if (endDate != DateTime.MinValue)
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(700);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and (RecordDate <= @EndDate) ");
var 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.QueryString["BackDate"]))
{
DateTime.TryParse(context.Request.QueryString["BackDate"], out backDate);
}
if (backDate != DateTime.MinValue)
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(500);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and BackDate = @BackDate ");
var parm = new SqlParameter("@BackDate", SqlDbType.DateTime);
parm.Value = backDate;
parms.Add(parm);
}
var isBack = false;
if (!string.IsNullOrWhiteSpace(context.Request.QueryString["IsBack"]) && bool.TryParse(context.Request.QueryString["IsBack"], out isBack))
{
if (sqlWhere == null)
{
sqlWhere = new StringBuilder(100);
}
if (parms == null)
{
parms = new ParamsHelper();
}
sqlWhere.Append(@"and IsBack = @IsBack ");
var parm = new SqlParameter("@IsBack", SqlDbType.Bit);
parm.Value = isBack;
parms.Add(parm);
}
#endregion
var bll = new InfoneDeviceBorrowRecord();
var dt = bll.GetDsExport(sqlWhere == null ? "" : sqlWhere.ToString(), parms == null ? null : parms.ToArray());
OpenXmlHelper.Export(context, dt.Tables[0]);
}
catch (Exception ex)
{
context.Response.Write(ResResult.ResJsonString(false, ex.Message, ""));
}
}
