/// <summary>
/// 获取列表
/// </summary>
/// <param name="condition">条件</param>
/// <param name="page">数据页</param>
/// <returns>数据页</returns>
public DataPage GetList(QualityCheckCondition condition, DataPage page)
{
string sql = null;
List <DataParameter> parameters = new List <DataParameter>();
try
{
sql = this.GetQuerySql(condition, ref parameters);
//分页关键字段及排序
page.KeyName = "ID";
if (string.IsNullOrEmpty(page.SortExpression))
{
page.SortExpression = "UPDATETIME DESC";
}
using (IDataSession session = AppDataFactory.CreateMainSession())
{
page = session.GetDataPage <WHQualityCheck>(sql, parameters.ToArray(), page);
}
return(page);
}
catch (Exception ex)
{
throw ex;
}
}
private void BindData()
{
QualityCheckBLL bll = null;
DataPage dp = new DataPage();
QualityCheckCondition condition = new QualityCheckCondition();
try
{
bll = BLLFactory.CreateBLL <QualityCheckBLL>();
condition.BillNO = this.BillNO.Text;
condition.StartDate = this.StartDate.Text;
condition.EndDate = this.EndDate.Text;
condition.CheckResult = this.CheckResult.SelectedValue;
PagerHelper.InitPageControl(this.AspNetPager1, dp, true);
dp = bll.GetList(condition, dp);
List <WHQualityCheck> list = dp.Result as List <WHQualityCheck>;
this.GvList.DataSource = list;
this.GvList.DataBind();
for (int i = 0; i < this.GvList.Rows.Count; i++)
{
string click = string.Format("return edit('{0}');", this.GvList.DataKeys[i]["ID"].ToString());
(this.GvList.Rows[i].Cells[6].Controls[0] as WebControl).Attributes.Add("onclick", click);
}
PagerHelper.SetPageControl(AspNetPager1, dp, true);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <param name="user">查询条件</param>
/// <param name="parameters">参数</param>
/// <returns>查询语句</returns>
private string GetQuerySql(QualityCheckCondition condition, ref List <DataParameter> parameters)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
try
{
//构成查询语句
//构成查询语句
sqlBuilder.Append(@"SELECT QC.ID,QC.BillNO,QC.CheckDate,QC.BatchNumber,QC.CheckPerson,CASE QC.CheckResult
WHEN '1' THEN '不合格'
ELSE '合格' END AS CheckResult, A.* FROM T_FP_QUALITYCHECK QC
LEFT OUTER JOIN (SELECT distinct(GI.BATCHNUMBER) BN,GI.FACTORYPID,GI.PRODUCTIONID,PI.PNAME PDNAME FROM T_FP_GOODINFO GI
INNER JOIN T_FP_PRODUCTINFO PI ON GI.PRODUCTIONID = PI.PID
) A ON QC.BATCHNUMBER = A.BN ");
if (!string.IsNullOrEmpty(condition.BillNO))
{
whereBuilder.Append(" AND BillNO like @BillNO");
parameters.Add(new DataParameter("BillNO", "%" + condition.BillNO + "%"));
}
if (!string.IsNullOrEmpty(condition.StartDate))
{
whereBuilder.Append(" AND CheckDate >= @StartDate");
parameters.Add(new DataParameter("StartDate", condition.StartDate));
}
if (!string.IsNullOrEmpty(condition.StartDate))
{
whereBuilder.Append(" AND CheckDate <= @EndDate");
parameters.Add(new DataParameter("EndDate", condition.EndDate));
}
if (!string.IsNullOrEmpty(condition.CheckResult))
{
whereBuilder.Append(" AND CheckResult = @CheckResult");
parameters.Add(new DataParameter("CheckResult", condition.CheckResult));
}
if (!string.IsNullOrEmpty(condition.FACTORYPID))
{
whereBuilder.Append(" AND A.FACTORYPID = @FACTORYPID");
parameters.Add(new DataParameter("FACTORYPID", condition.FACTORYPID));
}
if (!string.IsNullOrEmpty(condition.PRODUCEID))
{
whereBuilder.Append(" AND A.PRODUCTIONID = @PRODUCTIONID");
parameters.Add(new DataParameter("PRODUCTIONID", condition.PRODUCEID));
}
//查询条件
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE " + whereBuilder.ToString().Substring(4));
}
return(sqlBuilder.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="condition">条件</param>
/// <param name="page">数据页</param>
/// <returns>数据页</returns>
public DataPage GetList(QualityCheckCondition condition, DataPage page)
{
try
{
return(new QualityCheckDAL().GetList(condition, page));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <param name="user">查询条件</param>
/// <param name="parameters">参数</param>
/// <returns>查询语句</returns>
private string GetQuerySql(QualityCheckCondition condition, ref List <DataParameter> parameters)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
try
{
//构成查询语句
sqlBuilder.Append(@"SELECT QC.ID,QC.BillNO,QC.CheckDate,QC.CheckPerson,CASE QC.CheckResult
WHEN '1' THEN '不合格' ELSE '合格' END AS CheckResult, bill.BillNO as InStockBillNo,QC.UPDATETIME
FROM T_WH_QualityCheck QC
LEFT OUTER JOIN T_WH_InStockBill bill on QC.BillID = bill.ID");
if (!string.IsNullOrEmpty(condition.BillNO))
{
whereBuilder.Append(" AND QC.BillNO like @BillNO");
parameters.Add(new DataParameter("BillNO", "%" + condition.BillNO + "%"));
}
if (!string.IsNullOrEmpty(condition.StartDate))
{
whereBuilder.Append(" AND QC.CheckDate >= @StartDate");
parameters.Add(new DataParameter("StartDate", condition.StartDate));
}
if (!string.IsNullOrEmpty(condition.StartDate))
{
whereBuilder.Append(" AND QC.CheckDate <= @EndDate");
parameters.Add(new DataParameter("EndDate", condition.EndDate));
}
if (!string.IsNullOrEmpty(condition.CheckResult))
{
whereBuilder.Append(" AND QC.CheckResult = @CheckResult");
parameters.Add(new DataParameter("CheckResult", condition.CheckResult));
}
//查询条件
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE " + whereBuilder.ToString().Substring(4));
}
return(sqlBuilder.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
