public PageResult <RetailOrderQueryListVo> doFindRetailOrderByCondition(QueryCriteria criteria)
{
BaseDao <RetailOrderQueryListVo> baseDao = DaoFactory <RetailOrderQueryListVo> .CreateBaseDao(typeof(RetailOrderQueryListVo));
string selectFields = string.Empty;
string tableName = string.Empty;
string condition = string.Empty;
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["remarkFlag"]) && "Y".Equals(criteria.QueryCondition["remarkFlag"]))
{
selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," +
"RETAIL.BSCLDM AS ModelCode,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," +
"REMARK.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club";
tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND,SJVDTALIB.VST19 REMARK";
condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND REMARK.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD AND RETAIL.VSLSDD=REMARK.VSLSDD " +
"AND RETAIL.ZMKHDM=@DEALER_CODE AND RETAIL.ZMFXDM=@REGION_CODE ";
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["customerName"]))
{
condition += "AND REMARK.VSYHMC LIKE '%" + criteria.QueryCondition["customerName"] + "%' ";
}
}
else
{
selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," +
"RETAIL.BSCLDM AS ModelCode,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," +
"RETAIL.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club";
tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND";
condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD " +
"AND RETAIL.ZMKHDM=@DEALER_CODE AND RETAIL.ZMFXDM=@REGION_CODE ";
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["customerName"]))
{
condition += "AND RETAIL.VSYHMC LIKE '%" + criteria.QueryCondition["customerName"] + "%' ";
}
}
string orderFields = "RETAIL.VSLSDD";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("DEALER_CODE", criteria.QueryCondition["dealerCode"]);
//parms.Add("REGION_CODE", criteria.QueryCondition["regionCode"]);
parms.Add("REGION_CODE", "9999999");
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["modelCode"]))
{
condition += "AND RETAIL.BSCLDM=@MODEL_CODE ";
parms.Add("MODEL_CODE", criteria.QueryCondition["modelCode"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["colorCode"]))
{
condition += "AND RETAIL.XSYSDM=@COLOR_CODE ";
parms.Add("COLOR_CODE", criteria.QueryCondition["colorCode"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["interiorCode"]))
{
condition += "AND RETAIL.BSNSYM=@INTERIOR_CODE ";
parms.Add("INTERIOR_CODE", criteria.QueryCondition["interiorCode"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["vin"]))
{
condition += "AND RETAIL.CYVINM=@VIN ";
parms.Add("VIN", criteria.QueryCondition["vin"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["invoiceNo"]))
{
condition += "AND RETAIL.DMFPHM=@INVOICE_NO ";
parms.Add("INVOICE_NO", criteria.QueryCondition["invoiceNo"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["salesSource"]))
{
condition += "AND RETAIL.VSXSLY=@SALES_SOURCE ";
parms.Add("SALES_SOURCE", criteria.QueryCondition["salesSource"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["orderStatus"]))
{
condition += "AND RETAIL.VSDDZT=@ORDER_STATUS ";
parms.Add("ORDER_STATUS", criteria.QueryCondition["orderStatus"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["outStockStatus"]))
{
condition += "AND VSPCCL=@OUTSTOCK_STATUS ";
parms.Add("OUTSTOCK_STATUS", criteria.QueryCondition["outStockStatus"]);
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["startDateCreate"]))
{
condition += "AND RETAIL.VSDJRQ>=@START_DATE_CREATE ";
parms.Add("START_DATE_CREATE", long.Parse(criteria.QueryCondition["startDateCreate"].ToString().Replace("-", "")));
}
if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["endDateCreate"]))
{
condition += "AND RETAIL.VSDJRQ<=@END_DATE_CREATE ";
parms.Add("END_DATE_CREATE", long.Parse(criteria.QueryCondition["endDateCreate"].ToString().Replace("-", "")));
}
string sql = DB2Helper.GetPageSql(selectFields, orderFields, tableName, condition, criteria.StartIndex, criteria.PageSize);
string sqlCount = DB2Helper.GetCountSql(tableName, condition);
return(baseDao.nativeQuerySql(sql, sqlCount, parms, criteria.StartIndex, criteria.PageSize));
}