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 })); }