/// <summary>
///
/// </summary>
/// <param name="billNO"></param>
/// <param name="customer"></param>
/// <param name="saleMan"></param>
/// <param name="billType"></param>
/// <param name="billStatus"></param>
/// <param name="outboundType"></param>
/// <param name="shipNO"></param>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="warehouse"></param>
/// <returns></returns>
public List <ReturnHeaderEntity> QueryBills(string billNO, string customer, string saleMan, string itemDesc,
string billStatus, string returnDriver, string shipNO, DateTime dateFrom, DateTime dateTo)
{
string sql = SELECT_BILL_BODY +
"WHERE (@BillNO IS NULL OR A.BILL_NO = @BillNO) AND " +
"(@Customer IS NULL OR S.C_NAME like '%" + customer + "%') AND " +
"(@SalesMan IS NULL OR A.SALES_MAN like '%" + saleMan + "%') AND " +
"(@ItemDesc IS NULL OR A.BILL_ID IN (SELECT wcd.BILL_ID FROM wm_crn_detail wcd JOIN wm_sku ws ON wcd.SKU_CODE = ws.SKU_CODE WHERE ws.SKU_NAME LIKE '%" + itemDesc + "%')) AND " +
"(@BillStatus IS NULL OR C2.ITEM_DESC = @BillStatus) AND " +
"(@ReturnDriver IS NULL OR A.RETURN_DRIVER like '%" + returnDriver + "%') AND " +
"(@StartTime IS NULL OR A.CREATE_DATE >= @StartTime) AND " +
"(@EndTime IS NULL OR A.CREATE_DATE <= @EndTime)";
if (!string.IsNullOrEmpty(billStatus))
{
sql = string.Format(SELECT_BILL_BODY +
"WHERE (@BillNO IS NULL OR A.BILL_NO = @BillNO) AND " +
"(@Customer IS NULL OR S.C_NAME like '%" + customer + "%') AND " +
"(@SalesMan IS NULL OR A.SALES_MAN like '%" + saleMan + "%') AND " +
"(@ItemDesc IS NULL OR A.BILL_ID IN (SELECT wcd.BILL_ID FROM wm_crn_detail wcd JOIN wm_sku ws ON wcd.SKU_CODE = ws.SKU_CODE WHERE ws.SKU_NAME LIKE '%" + itemDesc + "%')) AND " +
"(A.BILL_STATE in ({0})) AND " +
"(@ReturnDriver IS NULL OR A.RETURN_DRIVER like '%" + returnDriver + "%') AND " +
"(@StartTime IS NULL OR A.CREATE_DATE >= @StartTime) AND " +
"(@EndTime IS NULL OR A.CREATE_DATE <= @EndTime)", DBUtil.FormatParameter(billStatus));
}
IMapper map = DatabaseInstance.Instance();
return(map.Query <ReturnHeaderEntity>(sql,
new
{
BillNO = billNO,
Customer = customer,
SalesMan = saleMan,
ItemDesc = itemDesc,
BillStatus = billStatus,
ReturnDriver = returnDriver,
StartTime = dateFrom,
EndTime = dateTo
}));
}
public List <AsnBodyEntity> QueryBills(string warehouseCode, string billID, string poNO, string billState, string supplier,
string billType, string material, string sales, DateTime?dateFrom, DateTime?dateTo, DateTime?dateComFrom, DateTime?dateComTo)
{
IMapper map = DatabaseInstance.Instance();
DynamicParameters parms = new DynamicParameters();
string strWhereCondition = "WHERE H.WH_CODE = @WH_CODE";
//先把仓库参数添加到集合
parms.Add("WH_CODE", warehouseCode);
//建单日期
if (dateFrom.HasValue)
{
parms.Add("P_CREATE_DATE_FROM", dateFrom.Value);
strWhereCondition += " AND H.CREATE_DATE >= @P_CREATE_DATE_FROM";
}
if (dateTo.HasValue)
{
parms.Add("P_CREATE_DATE_TO", dateTo.Value);
strWhereCondition += " AND H.CREATE_DATE <= @P_CREATE_DATE_TO";
}
//最后更新日期
if (dateComFrom.HasValue)
{
parms.Add("P_CLODE_DATE_FROM", dateComFrom.Value);
strWhereCondition += " AND H.LAST_UPDATETIME >= @P_CLODE_DATE_FROM";
}
if (dateComTo.HasValue)
{
parms.Add("P_CLODE_DATE_TO", dateComTo.Value);
strWhereCondition += " AND H.LAST_UPDATETIME <= @P_CLODE_DATE_TO";
}
//单据编号
if (!string.IsNullOrEmpty(billID))
{
parms.Add("P_BILL_NO", billID);
strWhereCondition += " AND H.BILL_NO = @P_BILL_NO";
}
//原采购单编号
if (!string.IsNullOrEmpty(poNO))
{
parms.Add("P_PO_NO", poNO);
strWhereCondition += " AND H.PO_NO = @P_PO_NO";
}
//供应商
if (!string.IsNullOrEmpty(supplier))
{
parms.Add("P_SUPPLIER", supplier);
strWhereCondition += " AND H.SUPPLIER = @P_SUPPLIER";
}
//业务类型
if (!string.IsNullOrEmpty(billType))
{
parms.Add("P_BILL_TYPE", billType);
strWhereCondition += " AND H.BILL_TYPE = @P_BILL_TYPE";
}
//业务员
if (!string.IsNullOrEmpty(sales))
{
parms.Add("P_SALES", sales);
strWhereCondition += " AND H.SALES = @P_SALES";
}
//状态有可能是多个，这个需要转换为OR，直接拼接成字符串，不用参数了
if (!string.IsNullOrEmpty(billState))
{
//假设billState=12,13,15，函数FormatParameter转换为BILL_STATE = '12' OR BILL_STATE = '13' OR BILL_STATE = '15'
strWhereCondition += string.Concat(" AND (", DBUtil.FormatParameter("H.BILL_STATE", billState), ")");
}
//物料编码或名称，支持模糊查询，因为物料在明细表中，反查出的主表数据会重复，所以要用DISTINCT
//另外不要使用字段拼接，oracle和sql的语法不一样
if (!string.IsNullOrEmpty(material))
{
parms.Add("P_MTL_CODE", material);
strWhereCondition += " AND EXISTS(SELECT 1 FROM WM_ASN_DETAIL D INNER JOIN WM_MATERIALS M ON D.MTL_CODE = M.MTL_CODE WHERE H.BILL_ID = D.BILL_ID AND (D.MTL_CODE like @P_MTL_CODE OR M.MTL_NAME LIKE @P_MTL_CODE OR M.MTL_NAME_S LIKE @P_MTL_CODE OR M.NAME_PY LIKE @P_MTL_CODE))";
}
string sql = string.Concat(ASN_HEADER_FIELD, strWhereCondition);
return(map.Query <AsnBodyEntity>(sql, parms));
}
public List <SOSummaryEntity> QueryBillsQuery(string billStates)
{
string sql = string.Format("SELECT A.FROM_WH_CODE,wspr.CT_CODE, A.BILL_ID, A.BILL_NO,A.BILL_STATE, W.WH_NAME FROM_WH_NAME, " +
"S.C_NAME, S.SORT_ORDER, S.RT_CODE, R.RT_NAME, S.ADDRESS, C.ITEM_DESC, " +
"A.CREATE_DATE, S.X_COOR, S.Y_COOR, S.DISTANCE, SUM(D.QTY * D.PRICE) AMOUNT, SUM(US.LENGTH * US.WIDTH * US.HEIGHT) VOLUME " +
"FROM WM_SO_HEADER A " +
"LEFT JOIN WM_WAREHOUSE W ON A.FROM_WH_CODE = W.WH_CODE " +
"LEFT JOIN CUSTOMERS S ON A.C_CODE = S.C_CODE " +
"LEFT JOIN WM_ROUTE R ON R.RT_CODE = S.RT_CODE " +
"LEFT JOIN wm_so_pick_record wspr ON A.BILL_ID=wspr.BILL_ID " +
"INNER JOIN wm_base_code C on A.BILL_STATE = C.ITEM_VALUE " +
"INNER JOIN WM_SO_DETAIL D ON A.BILL_ID = D.BILL_ID " +
"INNER JOIN WM_UM_SKU US ON D.SKU_CODE = US.SKU_CODE AND D.UM_CODE = US.UM_CODE " +
"WHERE {0} " +
"GROUP BY A.BILL_ID, A.BILL_NO, W.X_COOR, W.Y_COOR, S.C_NAME, S.ADDRESS, " +
"A.CREATE_DATE, S.X_COOR, S.Y_COOR,wspr.CT_CODE", DBUtil.FormatParameter("A.BILL_STATE", billStates));
IMapper map = DatabaseInstance.Instance();
return(map.Query <SOSummaryEntity>(sql));
}
