290 lines
15 KiB
C#
290 lines
15 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Data;
|
|||
|
|
using WMS.Model.Stock;
|
|||
|
|
|
|||
|
|
using WMS.DBUtility;
|
|||
|
|
using WMS.IData.IStock;
|
|||
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|||
|
|
|
|||
|
|
namespace WMS.SqlServerData.StockData
|
|||
|
|
{
|
|||
|
|
public class MoveLocTabData : IMoveLocTab
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取移位单明细
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns>移位单明细</returns>
|
|||
|
|
public DataTable GetMoveLocTabDT(MoveLocTabModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
|
|||
|
|
strSql.AppendLine("select ");
|
|||
|
|
strSql.AppendLine(" a.MOVELOC_ID, ");
|
|||
|
|
strSql.AppendLine(" a.GOODS_ID, ");
|
|||
|
|
strSql.AppendLine(" a.OLD_LOC_ID, ");
|
|||
|
|
strSql.AppendLine(" a.NEW_LOC_ID, ");
|
|||
|
|
strSql.AppendLine(" a.PLAN_NUM, ");
|
|||
|
|
strSql.AppendLine(" a.REAL_NUM, ");
|
|||
|
|
strSql.AppendLine(" a.MOVELOC_TAB_ID, ");
|
|||
|
|
strSql.AppendLine(" a.MISTOCK_NUM, ");
|
|||
|
|
strSql.AppendLine(" a.UNIT, --UNIT ");
|
|||
|
|
strSql.AppendLine(" a.SCALE_UNIT, --重量UNIT ");
|
|||
|
|
strSql.AppendLine(" a.PRICE, --标准价格 ");
|
|||
|
|
strSql.AppendLine(" a.PUTIN_ID, --入库批次号 ");
|
|||
|
|
strSql.AppendLine(" a.DOCCTNUMBER, --单据编号（调整填写调整号，入库填写入库号） ");
|
|||
|
|
strSql.AppendLine(" a.SPECIFICATION, --规格 ");
|
|||
|
|
strSql.AppendLine(" a.PRODUCTION_DATE,--生产日期 ");
|
|||
|
|
strSql.AppendLine(" a.WARE_DATE, --上架时间 ");
|
|||
|
|
strSql.AppendLine(" a.PROVIDER_ID, --供应商编号 ");
|
|||
|
|
strSql.AppendLine(" a.LOC_NUM, --某商品某库位某批次号库存数 ");
|
|||
|
|
strSql.AppendLine(" b.goods_name ");
|
|||
|
|
|
|||
|
|
if (model.SORT == 1)//去备份表查
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" FROM T_STOCK_MOVELOC_TAB_BACK a ");
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" FROM T_STOCK_MOVELOC_TAB a ");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
strSql.AppendLine(" left join t_base_goods b ");
|
|||
|
|
strSql.AppendLine(" on a.goods_id = b.goods_id ");
|
|||
|
|
strSql.AppendLine("where 1 = 1");
|
|||
|
|
if (!string.IsNullOrEmpty(model.MOVELOC_TAB_ID))
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" and a.MOVELOC_TAB_ID like '%" + model.MOVELOC_TAB_ID + "%' ");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(model.MOVELOC_ID))
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" and a.MOVELOC_ID = '" + model.MOVELOC_ID + "' ");
|
|||
|
|
}
|
|||
|
|
strSql.AppendLine(" order by a.MOVELOC_TAB_ID");
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 插入移位单明细信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="MoveLocTabModel">移位单明细Model</param>
|
|||
|
|
public void InsertMoveLocTab(MoveLocTabModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
|
|||
|
|
//if (model.MOVELOC_ID == null)
|
|||
|
|
//{
|
|||
|
|
// strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
|
|||
|
|
// strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MOVELOC_TAB_ID,MISTOCK_NUM)");
|
|||
|
|
// strSql.AppendLine(" values (");
|
|||
|
|
// strSql.AppendLine("SEQ_STOCK_MOVELOC.currval,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,SEQ_STOCK_MOVELOC_TAB.nextval,:MISTOCK_NUM)");
|
|||
|
|
// OracleParameter[] parameters = {
|
|||
|
|
// new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,20),
|
|||
|
|
// new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2,20),
|
|||
|
|
// new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2,20),
|
|||
|
|
// new OracleParameter(":PLAN_NUM", OracleDbType.Decimal,4),
|
|||
|
|
// new OracleParameter(":REAL_NUM", OracleDbType.Decimal,4),
|
|||
|
|
// new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal,4)};
|
|||
|
|
// parameters[0].Value = model.GOODS_ID;
|
|||
|
|
// parameters[1].Value = model.OLD_LOC_ID;
|
|||
|
|
// parameters[2].Value = model.NEW_LOC_ID;
|
|||
|
|
// parameters[3].Value = model.PLAN_NUM;
|
|||
|
|
// parameters[4].Value = model.REAL_NUM;
|
|||
|
|
// parameters[5].Value = model.MISTOCK_NUM;
|
|||
|
|
|
|||
|
|
// SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
//}
|
|||
|
|
//else
|
|||
|
|
//{
|
|||
|
|
if (model.MOVELOC_TAB_ID == null)
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
|
|||
|
|
strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MISTOCK_NUM,UNIT,SCALE_UNIT,PUTIN_ID,DOCCTNUMBER,SPECIFICATION,PRODUCTION_DATE,WARE_DATE,PROVIDER_ID,LOC_NUM,PRICE)");
|
|||
|
|
strSql.AppendLine(" values (");
|
|||
|
|
strSql.AppendLine(":MOVELOC_ID,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,:MISTOCK_NUM,:UNIT,:SCALE_UNIT,:PUTIN_ID,:DOCCTNUMBER,:SPECIFICATION,:PRODUCTION_DATE,:WARE_DATE,:PROVIDER_ID,:LOC_NUM,:PRICE)");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":SPECIFICATION",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PRODUCTION_DATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":WARE_DATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LOC_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":PRICE",OracleDbType.Varchar2)};
|
|||
|
|
parameters[0].Value = model.MOVELOC_ID;
|
|||
|
|
parameters[1].Value = model.GOODS_ID;
|
|||
|
|
parameters[2].Value = model.OLD_LOC_ID;
|
|||
|
|
parameters[3].Value = model.NEW_LOC_ID;
|
|||
|
|
parameters[4].Value = model.PLAN_NUM;
|
|||
|
|
parameters[5].Value = model.REAL_NUM;
|
|||
|
|
parameters[6].Value = model.MISTOCK_NUM;
|
|||
|
|
parameters[7].Value = model.UNIT;
|
|||
|
|
parameters[8].Value = model.SCALE_UNIT;
|
|||
|
|
parameters[9].Value = model.PUTIN_ID;
|
|||
|
|
parameters[10].Value = model.DOCCTNUMBER;
|
|||
|
|
parameters[11].Value = model.SPECIFICATION;
|
|||
|
|
parameters[12].Value = model.PRODUCTION_DATE;
|
|||
|
|
parameters[13].Value = model.WARE_DATE;
|
|||
|
|
parameters[14].Value = model.PROVIDER_ID;
|
|||
|
|
parameters[15].Value = model.LOC_NUM;
|
|||
|
|
parameters[16].Value = model.PRICE;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
else {
|
|||
|
|
strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
|
|||
|
|
strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MOVELOC_TAB_ID,MISTOCK_NUM,UNIT,SCALE_UNIT,PUTIN_ID,DOCCTNUMBER,SPECIFICATION,PRODUCTION_DATE,WARE_DATE,PROVIDER_ID,LOC_NUM,PRICE)");
|
|||
|
|
strSql.AppendLine(" values (");
|
|||
|
|
strSql.AppendLine(":MOVELOC_ID,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,:MOVELOC_TAB_ID,:MISTOCK_NUM,:UNIT,:SCALE_UNIT,:PUTIN_ID,:DOCCTNUMBER,:SPECIFICATION,:PRODUCTION_DATE,:WARE_DATE,:PROVIDER_ID,:LOC_NUM,:PRICE)");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":SPECIFICATION",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PRODUCTION_DATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":WARE_DATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LOC_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":PRICE",OracleDbType.Varchar2)};
|
|||
|
|
parameters[0].Value = model.MOVELOC_ID;
|
|||
|
|
parameters[1].Value = model.GOODS_ID;
|
|||
|
|
parameters[2].Value = model.OLD_LOC_ID;
|
|||
|
|
parameters[3].Value = model.NEW_LOC_ID;
|
|||
|
|
parameters[4].Value = model.PLAN_NUM;
|
|||
|
|
parameters[5].Value = model.REAL_NUM;
|
|||
|
|
parameters[6].Value = model.MOVELOC_TAB_ID;
|
|||
|
|
parameters[7].Value = model.MISTOCK_NUM;
|
|||
|
|
parameters[8].Value = model.UNIT;
|
|||
|
|
parameters[9].Value = model.SCALE_UNIT;
|
|||
|
|
parameters[10].Value = model.PUTIN_ID;
|
|||
|
|
parameters[11].Value = model.DOCCTNUMBER;
|
|||
|
|
parameters[12].Value = model.SPECIFICATION;
|
|||
|
|
parameters[13].Value = model.PRODUCTION_DATE;
|
|||
|
|
parameters[14].Value = model.WARE_DATE;
|
|||
|
|
parameters[15].Value = model.PROVIDER_ID;
|
|||
|
|
parameters[16].Value = model.LOC_NUM;
|
|||
|
|
parameters[17].Value = model.PRICE;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 修改移位单明细信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="MoveLocTabModel">移位单明细Model</param>
|
|||
|
|
public void UpdateMoveLocTab(MoveLocTabModel model) {
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
|
|||
|
|
strSql.AppendLine("update T_STOCK_MOVELOC_TAB set ");
|
|||
|
|
strSql.AppendLine("MOVELOC_ID=:MOVELOC_ID,");
|
|||
|
|
strSql.AppendLine("GOODS_ID=:GOODS_ID,");
|
|||
|
|
strSql.AppendLine("OLD_LOC_ID=:OLD_LOC_ID,");
|
|||
|
|
strSql.AppendLine("NEW_LOC_ID=:NEW_LOC_ID,");
|
|||
|
|
strSql.AppendLine("PLAN_NUM=:PLAN_NUM,");
|
|||
|
|
strSql.AppendLine("REAL_NUM=:REAL_NUM,");
|
|||
|
|
strSql.AppendLine("unit=:unit,");
|
|||
|
|
strSql.AppendLine("SCALE_UNIT=:SCALE_UNIT,");
|
|||
|
|
strSql.AppendLine("MOVELOC_TAB_ID=:MOVELOC_TAB_ID,");
|
|||
|
|
strSql.AppendLine("PRICE=:PRICE");
|
|||
|
|
strSql.AppendLine(" where MOVELOC_TAB_ID=:MOVELOC_TAB_ID ");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PRICE",OracleDbType.Varchar2)};
|
|||
|
|
parameters[0].Value = model.MOVELOC_ID;
|
|||
|
|
parameters[1].Value = model.GOODS_ID;
|
|||
|
|
parameters[2].Value = model.OLD_LOC_ID;
|
|||
|
|
parameters[3].Value = model.NEW_LOC_ID;
|
|||
|
|
parameters[4].Value = model.PLAN_NUM;
|
|||
|
|
parameters[5].Value = model.REAL_NUM;
|
|||
|
|
parameters[6].Value = model.UNIT;
|
|||
|
|
parameters[7].Value = model.SCALE_UNIT;
|
|||
|
|
parameters[8].Value = model.MOVELOC_TAB_ID;
|
|||
|
|
parameters[9].Value = model.PRICE;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除移位单明细信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ID"></param>
|
|||
|
|
public void DeleteMoveLocTab(string ID)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.AppendLine("delete from T_STOCK_MOVELOC_TAB ");
|
|||
|
|
strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2) };
|
|||
|
|
parameters[0].Value = ID;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 通过移位单编号删除移位单明细
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ID"></param>
|
|||
|
|
public void DeleteMoveLocTabByMoveLocID(string ID)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.AppendLine("delete from T_STOCK_MOVELOC_TAB ");
|
|||
|
|
strSql.AppendLine(" where MOVELOC_TAB_ID=:MOVELOC_TAB_ID ");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2) };
|
|||
|
|
parameters[0].Value = ID;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 向备份表中添加移位单明细
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="moveLocID">移位单编号</param>
|
|||
|
|
public void InsertMoveLocTabBack(string moveLocID)
|
|||
|
|
{
|
|||
|
|
string strSql = "insert into T_STOCK_MOVELOC_TAB_BACK select * from T_STOCK_MOVELOC_TAB where MOVELOC_ID =:MOVELOC_ID";
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)};
|
|||
|
|
parameters[0].Value = moveLocID;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|