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