public List <CountHeaderEntity> GetBills(string warehouse, string billStatus)
{
string whereCondition = string.Format("WHERE A.WH_CODE = '{0}' ", warehouse);
if (!string.IsNullOrEmpty(billStatus))
{
whereCondition += string.Format(" AND ({0})", DBUtil.FormatParameter("A.BILL_STATE", billStatus));
}
string sql = "SELECT A.BILL_ID, A.BILL_STATE, A.REMARK, A.CREATOR, A.CREATE_DATE, A.WH_CODE " +
"FROM WM_COUNT_HEADER A " + whereCondition;
IMapper map = DatabaseInstance.Instance();
return(map.Query <CountHeaderEntity>(sql));
}
public List <AsnHeaderEntity> QueryAsnBills(string billNO, string supplier, string saleMan, string billType,
string billStatus, string inboundType, DateTime dateFrom, DateTime dateTo, string warehouse)
{
string sql = SELECT_ASN_BODY +
"WHERE (@BillNO IS NULL OR BILL_NO = @BillNO) AND " +
"(@Warehouse IS NULL OR WAREHOUSE = @Warehouse) AND " +
"(@AsnType IS NULL OR ASN_TYPE = @AsnType) AND " +
"(@InboundType IS NULL OR INBOUND_TYPE = @InboundType) AND " +
"(@SalesMan IS NULL OR SALES_MAN = @SalesMan) AND " +
"(@Supplier IS NULL OR SUPPLIER = @Supplier) AND " +
"(@StartTime IS NULL OR CREATE_DATE >= @StartTime) AND " +
"(@EndTime IS NULL OR CREATE_DATE <= @EndTime)";
if (!string.IsNullOrEmpty(billStatus))
{
sql = string.Format(SELECT_ASN_BODY +
"WHERE (@BillNO IS NULL OR BILL_NO = @BillNO) AND " +
"(BILL_STATE in ({0})) AND " +
"(@Warehouse IS NULL OR WAREHOUSE = @Warehouse) AND " +
"(@AsnType IS NULL OR ASN_TYPE = @AsnType) AND " +
"(@InboundType IS NULL OR INBOUND_TYPE = @InboundType) AND " +
"(@SalesMan IS NULL OR SALES_MAN = @SalesMan) AND " +
"(@Supplier IS NULL OR SUPPLIER = @Supplier) AND " +
"(@StartTime IS NULL OR CREATE_DATE >= @StartTime) AND " +
"(@EndTime IS NULL OR CREATE_DATE <= @EndTime)", DBUtil.FormatParameter(billStatus));
}
IMapper map = DatabaseInstance.Instance();
return(map.Query <AsnHeaderEntity>(sql,
new
{
BillNO = billNO,
Warehouse = warehouse,
AsnType = billType,
InboundType = inboundType,
SalesMan = saleMan,
Supplier = supplier,
StartTime = dateFrom,
EndTime = dateTo
}));
}
/// <summary>
/// 已经登记，但是收货未完成的数据
/// </summary>
/// <returns></returns>
public DataTable GetVehicles(int?billID, string billNO, string cardNO, string cardState)
{
IMapper map = DatabaseInstance.Instance();
string sql = "SELECT S.CARD_NO, S.CARD_STATE, CS.ITEM_DESC CARD_STATE_DESC, H.BILL_NO, C.C_NAME, BS.ITEM_DESC BILL_STATE_DESC, " +
"V.CONTACT, V.DRIVER, V.VEHICLE_NO, V.CREATOR, V.CREATE_DATE " +
"FROM WM_CARD_STATE S " +
"LEFT JOIN WM_ASN_VEHICLE V ON S.HEADER_ID = V.BILL_ID AND S.CARD_NO=V.CARD_NO " +
"LEFT JOIN WM_ASN_HEADER H ON V.BILL_ID = H.BILL_ID " +
"INNER JOIN WM_BASE_CODE CS ON S.CARD_STATE = CS.ITEM_VALUE " +
"LEFT JOIN WM_BASE_CODE BS ON H.BILL_STATE = BS.ITEM_VALUE " +
"LEFT JOIN CUSTOMERS C ON H.S_CODE = C.C_CODE " +
"WHERE (@BillID IS NULL OR V.BILL_ID = @BillID) " +
"AND (@BillNO IS NULL OR H.BILL_NO = @BillNO) " +
"AND (@CardNO IS NULL OR V.CARD_NO = @CardNO)";
if (!string.IsNullOrEmpty(cardState))
{
sql = string.Format(sql + " AND ({0})", DBUtil.FormatParameter("S.CARD_STATE", cardState));
}
return(map.LoadTable(sql, new { BillID = billID, BillNO = billNO, CardNO = cardNO }));
}
public List <SOSummaryEntity> QueryBills(string billStates, int syncState)
{
string sql = string.Format("SELECT A.FROM_WH_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, " +
"F_CALC_PIECES(A.BILL_ID) TOTAL_COUNT " +
"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 " +
"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} AND SYNC_STATE = {1} " +
"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", DBUtil.FormatParameter("A.BILL_STATE", billStates), syncState);
IMapper map = DatabaseInstance.Instance();
return(map.Query <SOSummaryEntity>(sql));
}
/// <summary>
/// 根据条件查询盘点单
/// </summary>
/// <param name="warehouse"></param>
/// <param name="billNO"></param>
/// <param name="billStatus"></param>
/// <param name="Creator"></param>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <returns></returns>
public List <CountHeaderEntity> QueryBills(string warehouse, string billNO,
string billStatus, bool showNotComplete, DateTime?dateFrom, DateTime?dateTo)
{
string whereCondition = string.Format("WHERE A.WH_CODE = '{0}' ", warehouse);
if (!string.IsNullOrEmpty(billNO))
{
whereCondition += string.Format(" AND A.BILL_ID = '{0}'", billNO);
}
if (showNotComplete)
{
whereCondition += string.Format(" AND A.BILL_STATE < '{0}'", BaseCodeConstant.COUNT_STATE_CLOSE);
}
else if (!string.IsNullOrEmpty(billStatus))
{
whereCondition += string.Format(" AND ({0})", DBUtil.FormatParameter("A.BILL_STATE", billStatus));
}
if (dateFrom != null)
{
whereCondition += string.Format(" AND A.CREATE_DATE >= '{0}'", dateFrom);
}
if (dateTo != null)
{
whereCondition += string.Format(" AND A.CREATE_DATE <= '{0}'", dateTo);
}
string sql = @"SELECT A.BILL_ID, A.BILL_STATE, A.REMARK, A.CREATOR, A.CREATE_DATE, A.WH_CODE,
(CASE WHEN C.ITEM_DESC IS NULL THEN A.BILL_STATE ELSE C.ITEM_DESC END) STATE_NAME,
BC.ITEM_DESC TAG_DESC
FROM WM_COUNT_HEADER A
LEFT JOIN WM_BASE_CODE C ON C.ITEM_VALUE = A.BILL_STATE
LEFT JOIN WM_BASE_CODE BC ON BC.ITEM_VALUE = A.TAG " + whereCondition;
IMapper map = DatabaseInstance.Instance();
return(map.Query <CountHeaderEntity>(sql));
}
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));
}
/// <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
}));
}