/// <summary> /// 检查编码是否已存在 /// </summary> /// <param name="unit"></param> /// <returns></returns> private bool IsCodeExists(string skuCode, string umCode, string barcode) { IMapper map = DatabaseInstance.Instance(); string id = map.ExecuteScalar <string>("select SKU_CODE from wm_um_sku where SKU_CODE = @skuCode and UM_CODE=@umCode and SKU_BARCODE=@barcode", new { skuCode = skuCode, umCode = umCode, barcode = barcode }); return(!string.IsNullOrEmpty(id)); }
///<summary> ///根据大区查询所有仓库 ///</summary> ///<returns></returns> public List <WarehouseEntity> GetAllWarehouseByOrg(string orgCode) { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT W.WH_CODE, W.WH_NAME FROM WM_WAREHOUSE W LEFT join ORGANIZATIONS A ON A.ORG_CODE = W.ORG_CODE WHERE A.ORG_CODE = @COD"; return(map.Query <WarehouseEntity>(sql, new { COD = orgCode })); }
///<summary> ///查询所有 ///</summary> ///<returns></returns> public List <PDAEntity> GetAll() { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT P.PDA_CODE, P.IP_ADDRESS, P.WH_CODE, W.WH_NAME, P.PDA_NAME, P.IS_ACTIVE FROM WM_PDA P INNER JOIN WM_WAREHOUSE W ON P.WH_CODE = W.WH_CODE"; return(map.Query <PDAEntity>(sql)); }
/// <summary> /// 检查计量单位编码是否已存在 /// </summary> /// <param name="unit"></param> /// <returns></returns> private bool IsCodeExists(RouteEntity unit) { IMapper map = DatabaseInstance.Instance(); string id = map.ExecuteScalar <string>("select RT_CODE from WM_ROUTE where RT_CODE = @COD", new { COD = unit.RouteCode }); return(!string.IsNullOrEmpty(id)); }
///<summary> ///查询所有路线 ///</summary> ///<returns></returns> public List <RouteEntity> GetAll() { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT RT_CODE, RT_NAME FROM WM_ROUTE"; return(map.Query <RouteEntity>(sql)); }
/// <summary> /// 更新订单状态 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int BillState_Change(string state, AsnBodyEntity entity) { string sql = "UPDATE wm_asn_header wah SET wah.BILL_STATE = '{0}',wah.LAST_UPDATETIME=NOW(),wah.CLOSE_DATE =NOW() WHERE wah.BILL_ID = {1}"; IMapper map = DatabaseInstance.Instance(); return(map.Execute(String.Format(sql, state, entity.BillID))); }
/// <summary> /// 检查品牌编码是否已存在 /// </summary> /// <param name="unit"></param> /// <returns></returns> private bool IsCodeExists(BrandEntity brands) { IMapper map = DatabaseInstance.Instance(); string id = map.ExecuteScalar <string>("SELECT BRD_CODE FROM BRANDS WHERE BRD_CODE = @COD", new { COD = brands.BrandCode }); return(!string.IsNullOrEmpty(id)); }
/// <summary> /// 根据单号获取状态 /// </summary> /// <param name="billID"></param> /// <returns></returns> public string GetBillState(int billID) { string sql = string.Format("SELECT BILL_STATE FROM WM_TRANS_HEADER WHERE ID = {0}", billID); IMapper map = DatabaseInstance.Instance(); return(map.ExecuteScalar <string>(sql)); }
/// <summary> /// 获取没有分派的补货任务 /// </summary> /// <returns></returns> public int GetSupQty() { string sql = string.Format("SELECT COUNT(1) FROM tasks t WHERE t.USER_CODE IS NULL AND t.TASK_TYPE='144';"); IMapper map = DatabaseInstance.Instance(); return(Convert.ToInt32(map.ExecuteScalar <object>(sql))); }
/// <summary> /// 获取上架记录 /// </summary> /// <param name="dateBegin">起始时间</param> /// <param name="dateEnd">结束时间</param> /// <param name="userCode">用户编号</param> /// <returns></returns> public static DataTable GetPutawayRecords(DateTime dateBegin, DateTime dateEnd, string userCode) { //string sql = "SELECT R.CT_CODE, R.SKU_CODE, S.SKU_NAME, R.LC_CODE, " + // "ROUND(R.QTY / US.QTY, 0) QTY, U.UM_NAME, R.PUT_TIME " + // "FROM WM_ASN_PUTAWAY_RECORDS R " + // "LEFT JOIN WM_ASN_DETAIL D ON D.ID = R.BILL_DETAIL_ID " + // "LEFT JOIN WM_UM_SKU US ON D.UM_CODE = US.UM_CODE AND R.SKU_CODE = US.SKU_CODE " + // "LEFT JOIN WM_UM U ON U.UM_CODE = D.UM_CODE " + // "LEFT JOIN WM_SKU S ON S.SKU_CODE = R.SKU_CODE " + // "WHERE R.PUT_BY = @UserCode AND R.PUT_TIME >= @DateBegin AND R.PUT_TIME <= @DateEnd"; string sql = @"SELECT R.BILL_DETAIL_ID, R.CT_CODE, R.SKU_CODE, S.SKU_NAME, R.LC_CODE, ROUND(R.QTY / US.QTY, 0) QTY, U.UM_NAME, R.PUT_TIME FROM WM_ASN_PUTAWAY_RECORDS R LEFT JOIN (SELECT * FROM (SELECT A.ID, A.BILL_ID, A.SKU_CODE, A.UM_CODE, A.QTY FROM WM_ASN_DETAIL A UNION SELECT C.DETAIL_ID ID, C.BILL_ID, C.SKU_CODE, S.UM_CODE, C.QTY FROM WM_ASN_CONTAINER C LEFT JOIN WM_UM_SKU S ON S.ID = C.UM_SKU_ID) A LIMIT 1) D ON D.BILL_ID = R.BILL_ID AND D.SKU_CODE = R.SKU_CODE AND D.ID = R.BILL_DETAIL_ID LEFT JOIN WM_UM_SKU US ON D.UM_CODE = US.UM_CODE AND R.SKU_CODE = US.SKU_CODE LEFT JOIN WM_UM U ON U.UM_CODE = D.UM_CODE LEFT JOIN WM_SKU S ON S.SKU_CODE = R.SKU_CODE WHERE R.PUT_BY = @UserCode AND (R.PUT_TIME >= @DateBegin AND R.PUT_TIME <= @DateEnd)"; IMapper map = DatabaseInstance.Instance(); return(map.LoadTable(sql, new { @DateBegin = dateBegin, @DateEnd = dateEnd, @UserCode = userCode })); }
public List <StockSafeSUPEntity> GetNotSafeStock() { string sql = string.Format(@"SELECT ws.LC_CODE,ws.QTY ,ws.LATEST_OUT,ws.SKU_CODE,wu.UM_NAME,ws1.SPEC,ws.PICKING_QTY,ws1.SKU_NAME,ws1.SECURITY_QTY *20/100 -ws.QTY DIFF_QTY FROM wm_stock ws INNER JOIN wm_location wl ON ws.LC_CODE =wl.LC_CODE INNER JOIN wm_zone wz ON wl.ZN_CODE =wz.ZN_CODE AND wz.ZT_CODE='70' INNER JOIN wm_sku ws1 ON ws.SKU_CODE =ws1.SKU_CODE INNER JOIN wm_um wu ON ws.UM_CODE =wu.UM_CODE WHERE ws.QTY BETWEEN 1 AND ws1.SECURITY_QTY *20/100 AND wl.IS_ACTIVE ='Y' UNION ALL SELECT LC_CODE,qty,latest_out,SKU_CODE,UM_NAME,SPEC,PICKING_QTY,SKU_NAME,DIFF_QTY FROM( SELECT ws.LC_CODE ,SUM(ws.QTY) AS qty,MAX(ws.LATEST_OUT) AS latest_out,ws.SKU_CODE,wu.UM_NAME,ws1.SPEC,ws.PICKING_QTY,ws1.SKU_NAME,ws1.SECURITY_QTY *20/100 -ws.QTY DIFF_QTY FROM wm_stock ws INNER JOIN wm_location wl ON ws.LC_CODE =wl.LC_CODE INNER JOIN wm_zone wz ON wl.ZN_CODE =wz.ZN_CODE AND wz.ZT_CODE='70' INNER JOIN wm_sku ws1 ON ws.SKU_CODE =ws1.SKU_CODE INNER JOIN wm_um wu ON ws.UM_CODE =wu.UM_CODE WHERE wl.IS_ACTIVE ='Y' GROUP BY ws.LC_CODE) AS aaa WHERE qty=0"); IMapper map = DatabaseInstance.Instance(); return(map.Query <StockSafeSUPEntity>(sql)); }
/// <summary> /// 查询托盘当前记录 2015-6-12 17:35:01 by wangjw /// </summary> /// <param name="containerCode"></param> /// <returns></returns> public DataTable GetContainerRecords(string ctCode, string billNo, string billType) { IMapper map = DatabaseInstance.Instance(); string sql = string.Empty; if (string.Equals(billType, "入库单据")) { sql = string.Format(@"SELECT wus.SKU_BARCODE, ws.SKU_NAME, wac.QTY, wu.UM_NAME, wac.CREATE_DATE, wac.CREATOR FROM wm_asn_container wac INNER JOIN wm_um_sku wus ON wus.ID = wac.UM_SKU_ID INNER JOIN wm_sku ws ON ws.SKU_CODE = wus.SKU_CODE INNER JOIN wm_um wu ON wu.UM_CODE = wus.UM_CODE INNER JOIN wm_asn_header wah ON wah.BILL_ID = wac.BILL_ID WHERE wac.CT_CODE = '{0}' AND wah.BILL_NO = '{1}'", ctCode, billNo); } else if (string.Equals(billType, "出库单据")) { sql = string.Format(@"SELECT wus.SKU_BARCODE, ws.SKU_NAME, wspr.PICK_QTY AS QTY, wu.UM_NAME, wspr.PICK_DATE AS CREATE_DATE, u.USER_NAME AS CREATOR FROM wm_so_pick_record wspr INNER JOIN wm_um_sku wus ON wus.ID = wspr.UM_SKU_ID INNER JOIN wm_sku ws ON ws.SKU_CODE = wus.SKU_CODE INNER JOIN wm_um wu ON wu.UM_CODE = wus.UM_CODE INNER JOIN users u ON u.USER_CODE = wspr.USER_CODE INNER JOIN wm_so_header wsh ON wsh.BILL_ID = wspr.BILL_ID WHERE wspr.CT_CODE = '{0}' AND wsh.BILL_NO = '{1}'", ctCode, billNo); } return(map.LoadTable(sql)); }
/// <summary> /// 查询托盘状态 /// </summary> /// <param name="billNO"></param> /// <param name="containerCode"></param> /// <param name="containerState"></param> /// <returns></returns> public List <TrayStatusTableEntity> ListContainerState(string billNO, string containerCode, string containerState, string warehouse) { string sql = String.Format("SELECT C.CT_CODE, S.CT_STATE, ST.ITEM_DESC STATE_DESC, S.UNIQUE_CODE, S.BILL_HEAD_ID, " + " wah.BILL_NO AS 'IN_BILL', wsh.BILL_NO AS 'OUT_BILL', " + " CIN.C_NAME AS 'IN_CNAME', COUT.C_NAME AS 'OUT_CNAME' " + " FROM WM_CONTAINER C " + " LEFT JOIN WM_CONTAINER_STATE S ON S.CT_CODE = C.CT_CODE " + " LEFT JOIN WM_BASE_CODE ST ON ST.ITEM_VALUE = S.CT_STATE " + " LEFT JOIN wm_asn_header wah ON S.BILL_HEAD_ID = wah.BILL_ID " + " LEFT JOIN wm_so_header wsh ON wsh.BILL_ID = S.BILL_HEAD_ID " + " LEFT JOIN CUSTOMERS CIN ON CIN.C_CODE = wah.S_CODE " + " LEFT JOIN CUSTOMERS COUT ON COUT.C_CODE = wsh.C_CODE " + " WHERE C.CT_TYPE = '50' AND C.WH_CODE='{0}' ORDER BY IN_BILL DESC, OUT_BILL DESC", warehouse); TrayStatusTableEntity tste = new TrayStatusTableEntity(); IMapper map = DatabaseInstance.Instance(); List <TrayStatusTableEntity> list = map.Query <TrayStatusTableEntity>(sql); return(list); //return map.LoadTable(sql); //if (!string.IsNullOrEmpty(containerState)) // sql = string.Format(sql + " AND ({0})", DBUtil.FormatParameter("S.CARD_STATE", containerState)); //return map.LoadTable(sql, new { BillID = containerCode, BillNO = billNO, CardNO = containerState }); }
public List <CompanyEntity> GetCompanys() { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT COMPANY_CODE, COMPANY_NAME, ADDR, PHONE, FAX, EMAIL, POSTCODE, REMARK FROM COMPANY"; return(map.Query <CompanyEntity>(sql)); }
/// <summary> /// 查询库存占用数据 /// </summary> /// <param name="Creator"></param> /// <param name="CreateDateFrom"></param> /// <param name="CreateDateTo"></param> /// <param name="Status"></param> /// <returns></returns> public List <OccupyRecordEntity> QueryOccupyRecod(string Creator, DateTime?CreateDateFrom, DateTime?CreateDateTo, String Status) { string sql = @"select o.OCCUPY_ID,o.STOCK_ID,o.CREATOR,o.CREATEDATE,o.OCCUPY_QTY,o.STATUS,o.REMARK,s.BATCH_NO,s.LOCATION, s.LOCATION,s.MATERIAL,s.BATCH_NO,s.DUE_DATE,s.COM_MATERIAL,c.NAM as STATUS_NAME,m.NAM AS MATERIAL_NAME from OCCUPY_RECORD o inner join STOCK_RECORD s on s.STOCK_ID=o.STOCK_ID inner join CODEITEM c on c.COD=o.STATUS inner join MATERIAL m on m.COD=s.MATERIAL where 1=1 "; if (Creator != null && Creator != "") { sql += string.Format(" and o.CREATOR='{0}'", Creator); } if (CreateDateFrom != null) { sql += string.Format(" and o.CREATEDATE>'{0}'", CreateDateFrom); } if (CreateDateTo != null) { sql += string.Format(" and o.CREATEDATE<'{0}'", CreateDateTo); } if (Status != null && Status != "") { sql += string.Format(" and o.STATUS='{0}'", Status); } IMapper map = DatabaseInstance.Instance(); return(map.Query <OccupyRecordEntity>(sql)); }
/// <summary> /// 修改单据的备注(含备注和字体颜色) /// </summary> /// <param name="remark"></param> /// <param name="colorArgb"></param> /// <returns>1:成功</returns> public int UpdateRemark(int billID, string remark, int?colorArgb, string userName) { IMapper map = DatabaseInstance.Instance(); string sql = "UPDATE WM_ASN_HEADER SET WMS_REMARK = @Remark, ROW_COLOR = @Color WHERE BILL_ID = @BillID"; return(map.Execute(sql, new { Remark = remark, Color = colorArgb, BillID = billID })); }
/// <summary> /// 获取退库临时收货区 /// </summary> /// <param name="whCode"></param> /// <returns></returns> public string GetTempZone(string whCode) { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT IFNULL( wl.LC_CODE,'') FROM wm_location wl JOIN wm_zone wz ON wl.ZN_CODE=wz.ZN_CODE WHERE wz.ZT_CODE='79' AND wz.WH_CODE='{0}' LIMIT 1"; return(map.ExecuteScalar <string>(String.Format(sql, whCode))); }
//------------------------------------------------------------------- /// <summary> /// 获取单据的最新信息 /// </summary> /// <param name="billID"></param> /// <returns></returns> public AsnHeaderEntity GetHeaderInfoByBillID(int billID) { string sql = SELECT_ASN_BODY + "WHERE BILL_ID = @BillID"; IMapper map = DatabaseInstance.Instance(); return(map.QuerySingle <AsnHeaderEntity>(sql, new { BillID = billID })); }
public int DeleteRelationSupplier(string brandCode, string supplierCode) { IMapper map = DatabaseInstance.Instance(); return(map.Execute("DELETE FROM BRAND_SUPPLIER WHERE BRD_CODE = @BRD_COD AND S_CODE = @S_CODE", new { BRD_COD = brandCode, S_CODE = supplierCode })); }
public AsnHeaderEntity GetHeaderInfoByBillNO(string warehouse, string billNO) { string sql = SELECT_ASN_BODY + "WHERE WAREHOUSE = @Warehouse and BILL_NO = @BillNO"; IMapper map = DatabaseInstance.Instance(); return(map.QuerySingle <AsnHeaderEntity>(sql, new { Warehouse = warehouse, BillNO = billNO })); }
/// <summary> /// 添加或编辑品牌 /// </summary> /// <param name="entity"></param> /// <param name="isNew">添加或编辑</param> /// <returns></returns> public int Save(BrandEntity brands, bool isNew) { IMapper map = DatabaseInstance.Instance(); int ret = -2; if (isNew) { //检查编号是否已经存在 if (IsCodeExists(brands)) { return(-1); } ret = map.Execute("INSERT INTO BRANDS(BRD_CODE, BRD_NAME) VALUES(@COD, @NAM)", new { COD = brands.BrandCode, NAM = brands.BrandName }); } else { //更新 ret = map.Execute("UPDATE BRANDS SET BRD_NAME = @NAM WHERE BRD_CODE = @COD", new { COD = brands.BrandCode, NAM = brands.BrandName }); } return(ret); }
/// <summary> /// 按照库房、收货方式、状态(是小于某个状态)的单据 /// </summary> /// <param name="warehouse"></param> /// <param name="dateFrom"></param> /// <param name="dateTo"></param> /// <returns></returns> public List <AsnHeaderEntity> QueryBillsQuickly(string warehouse, string inboundType, DateTime?dateFrom, DateTime?dateTo) { string whereCondition = string.Format("WHERE WAREHOUSE = @Warehouse AND BILL_STATE < '{0}'", SysCodeConstant.ASN_STATUS_CLOSE); if (!string.IsNullOrEmpty(inboundType)) { whereCondition += string.Format(" AND INBOUND_TYPE = '{0}'", inboundType); } if (dateFrom != null) { whereCondition += string.Format(" AND CREATE_DATE >= '{0}'", dateFrom.Value); } if (dateTo != null) { whereCondition += string.Format(" AND CREATE_DATE <= '{0}'", dateTo.Value); } string sql = SELECT_ASN_BODY + whereCondition; IMapper map = DatabaseInstance.Instance(); return(map.Query <AsnHeaderEntity>(sql, new { Warehouse = warehouse })); }
/// <summary> /// 添加或编辑计量单位 /// </summary> /// <param name="entity"></param> /// <param name="isNew">添加或编辑</param> /// <returns></returns> public int Save(RouteEntity entity, bool isNew) { IMapper map = DatabaseInstance.Instance(); int ret = -2; if (isNew) { //检查编号是否已经存在 if (IsCodeExists(entity)) { return(-1); } ret = map.Execute(string.Format("insert into WM_ROUTE(RT_CODE, RT_NAME, LAST_UPDATETIME) " + "values(@COD, @NAM, {0})", map.GetSysDateString()), new { COD = entity.RouteCode, NAM = entity.RouteName }); } else { //更新 ret = map.Execute(string.Format("update WM_ROUTE set RT_NAME = @NAM, LAST_UPDATETIME = {0} where RT_CODE = @COD", map.GetSysDateString()), new { COD = entity.RouteCode, NAM = entity.RouteName }); } return(ret); }
/// <summary> /// 获取一个单据的订购量 /// </summary> /// <param name="billID"></param> /// <returns></returns> public int GetOrderQtyByBillID(int billID) { string sql = @"SELECT SUM(QTY) FROM ASN_DETAIL WHERE BILL_ID = @BillID"; IMapper map = DatabaseInstance.Instance(); return(map.ExecuteScalar <int>(sql, new { BillID = billID })); }
/// <summary> /// 添加或编辑仓库 /// </summary> /// <param name="entity"></param> /// <param name="operatorFlag">添加或编辑</param> /// <returns></returns> public int WarehouseAddAndUpdate(WarehouseEntity entity, bool isNew) { IMapper map = DatabaseInstance.Instance(); int ret = -2; if (isNew) { //检查编号是否已经存在 if (IsWarehouseCodeExists(entity)) { return(-1); } ret = map.Execute("insert into WM_WAREHOUSE(WH_CODE, WH_NAME, ORG_CODE) " + "values(@COD, @NAM, @ORG_CODE)", new { COD = entity.WarehouseCode, NAM = entity.WarehouseName, ORG_CODE = entity.OrgCode }); } else { //更新 ret = map.Execute("update WM_WAREHOUSE set WH_NAME = @NAM, ORG_CODE = @ORG_CODE where WH_CODE = @COD", new { COD = entity.WarehouseCode, NAM = entity.WarehouseName, ORG_CODE = entity.OrgCode }); } return(ret); }
/// <summary> /// 做货到确认 /// </summary> /// <param name="billID"></param> /// <returns></returns> public int SetStatusToArriveConfirm(int billID) { IMapper map = DatabaseInstance.Instance(); return(map.Execute("UPDATE ASN_HEADER SET BILL_STATE = @Status, ARRIVE_DATE = GETDATE() WHERE BILL_ID = @BillID", new { Status = SysCodeConstant.ASN_STATUS_AWAIT_CHECK, BillID = billID })); }
///<summary> ///根据库房信息 ///</summary> ///<returns></returns> public WarehouseEntity GetWarehouseByCode(string whCode) { IMapper map = DatabaseInstance.Instance(); string sql = "SELECT W.WH_CODE, W.WH_NAME, W.X_COOR, W.Y_COOR FROM WM_WAREHOUSE W WHERE W.WH_CODE = @COD"; return(map.QuerySingle <WarehouseEntity>(sql, new { COD = whCode })); }
/// <summary> /// 更新入库方式 /// </summary> /// <param name="type"></param> /// <param name="billID"></param> /// <returns></returns> public int UpdateInstoreType(string type, int billID) { string sql = "UPDATE wm_asn_header wah SET wah.INSTORE_TYPE='{0}' WHERE wah.BILL_ID={1}"; IMapper map = DatabaseInstance.Instance(); return(map.Execute(String.Format(sql, type, billID))); }
/// <summary> /// 重复叫号 /// </summary> /// <param name="callID"></param> /// <returns></returns> public static int ReCall(int callID) { IMapper map = DatabaseInstance.Instance(); string sql = String.Format("UPDATE wm_system_call set CALL_STATE='1',LAST_UPDATE_TIME=NOW() WHERE ID={0}", callID); return(map.Execute(sql)); }
public static int UpdateLocState(int headerID, int state) { string sql = @"UPDATE TMS_DATA_HEADER SET LOC_STATE = @State WHERE HEADER_ID = @HeaderID"; IMapper map = DatabaseInstance.Instance(); return(map.Execute(sql, new { State = state, HeaderID = headerID })); }