Beispiel #1
0
        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));
        }
Beispiel #2
0
        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
            }));
        }
Beispiel #3
0
        /// <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 }));
        }
Beispiel #4
0
        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));
        }
Beispiel #5
0
        /// <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));
        }
Beispiel #6
0
        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));
        }
Beispiel #7
0
        /// <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
            }));
        }