using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData.IRK;
using System.Data;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Common;
using WMS.Model.RK;
using WMS.DBUtility;
namespace WMS.SqlServerData.RKData
{
public class AcceptanceData : IAcceptance
{
#region 查询数据
///
/// 查询数据
///
/// 验收Model
///
public DataTable GetAcceptanceData(AcceptanceModel model)
{
#region SQL语句
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select t.ACCE_ID, --验收单号
t.LOT_ID, --入库批次号
t.GOODS_ID, --商品编号
a.GOODS_NAME, --商品名称
t.PRICE PRICE, --标准金额
t.UNIT UNIT, --基本单位
t.BUY_NUM, --采购数量
t.ARR_NUM, --到货数量
t.ACCE_NUM, --验收数量
t.PASS_NUM, --合格数量
t.REC_NUM, --接收数量
t.STO_NUM, --入库数量
t.NO_REC_NUM, --拒绝数量
t.RESULTS_TYPE, --验收结果
t.ACCE_MAN, --验收人
u.NAME ACCE_MAN_NAME, --验收人
t.ACCE_DATE, --验收日期
t.REMARK, --备注
t.VERSION, --版本号
t.OPERATORID, --录入人
us.NAME OPERATORNAME, --录入人
t.OPERATEDATE, --录入日期
t.shippers,--承运商
t.ori_bill,--来源单号
t.ACC_STATUS,t.WAREHOUSING_ID
from T_RK_ACCEPTANCE t --到货验收表
inner join T_BASE_GOODS a --商品明细表
ON t.GOODS_ID = a.GOODS_ID
left join T_BASE_USERINFO u --用户表
ON t.ACCE_MAN = u.USER_ID
left join T_BASE_USERINFO us --用户表
ON t.OPERATORID = us.USER_ID
where 1 = 1
");
#endregion
#region 查询条件
if (!string.IsNullOrEmpty(model.ACCE_ID))
{
strSql.Append(" and t.ACCE_ID ='" + model.ACCE_ID + "'");
}
if (!string.IsNullOrEmpty(model.GOODS_ID))
{
strSql.Append(" and (t.GOODS_ID ='" + model.GOODS_ID + "'");
}
if (!string.IsNullOrEmpty(model.LOT_ID))
{
strSql.Append(" and t.LOT_ID ='" + model.LOT_ID + "')");
}
if (!string.IsNullOrEmpty(model.WAREHOUSING_ID))
{
strSql.Append(" and t.WAREHOUSING_ID ='" + model.WAREHOUSING_ID + "'");
}
strSql.Append(" and t.ACC_STATUS ='" + model.ACC_STATUS + "'");
strSql.Append(" order by t.OPERATEDATE desc");
#endregion
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
}
#endregion
///
/// 查询入库批次明细 排除验单已存在的明细
///
///
///
public DataTable GetStorageLotInfoDistanct(string lot_ID)
{
StringBuilder str = new StringBuilder();
List strlist = new List();
str.Append(" select s.*, 0 STATUS, g.goods_name,s.Unit UNIT,s.price PRICE ");
str.Append(" from t_Rk_Storage_Lot_Info s");
str.Append(" left join T_BASE_GOODS g");
str.Append(" on s.goods_id = g.goods_id");
str.Append(" where LOT_ID = '" + lot_ID + "'");
str.Append(" and s.GOODS_ID not in");
str.Append(" (select goods_ID");
str.Append(" from T_RK_ACCEPTANCE t");
str.Append(" where 1 = 1");
str.Append(" and LOT_ID = '" + lot_ID + "'");
str.Append(" and ACC_STATUS = '1')");
str.Append(" union");
str.Append(" select s.*, 1 STATUS, g.goods_name,s.Unit UNIT,s.price PRICE ");
str.Append(" from t_Rk_Storage_Lot_Info s");
str.Append(" left join T_BASE_GOODS g");
str.Append(" on s.goods_id = g.goods_id");
str.Append(" where LOT_ID = '" + lot_ID + "'");
str.Append(" and s.GOODS_ID in (select goods_ID");
str.Append(" from T_RK_ACCEPTANCE t");
str.Append(" where 1 = 1");
str.Append(" and LOT_ID = '" + lot_ID + "'");
str.Append(" and ACC_STATUS = '1')");
return SystemDataObject.Instance.GetDataTable(str.ToString());
}
// public DataTable G
#region 增
///
/// 增 收货验收 写入数据库
///
/// 验收Model集合
public void AddAcceptance(AcceptanceModel model)
{
StringBuilder strInsert = new StringBuilder();
#region SQL语句 Parameter参数
strInsert.AppendLine(" insert into T_RK_ACCEPTANCE --收货验收表");
strInsert.AppendLine(" (ACCE_ID, --验收单号");
strInsert.AppendLine(" LOT_ID, --入库批次号");
strInsert.AppendLine(" GOODS_ID, --商品编号");
strInsert.AppendLine(" STO_NUM, --入库数量");
strInsert.AppendLine(" BUY_NUM, --采购数量");
strInsert.AppendLine(" ARR_NUM, --到货数量");
strInsert.AppendLine(" ACCE_NUM, --验收数量");
strInsert.AppendLine(" PASS_NUM, --合格数量");
strInsert.AppendLine(" REC_NUM, --接收数量");
strInsert.AppendLine(" NO_REC_NUM, --拒绝数量");
strInsert.AppendLine(" RESULTS_TYPE, --验收结果");
strInsert.AppendLine(" ACCE_MAN, --验收人");
strInsert.AppendLine(" ACCE_DATE, --验收日期");
strInsert.AppendLine(" OPERATORID, --录入人");
strInsert.AppendLine(" OPERATEDATE, --录入日期");
strInsert.AppendLine(" ACC_STATUS, --验收状态");
strInsert.AppendLine(" UNIT, --基本单位");
strInsert.AppendLine(" PRICE, --标准金额");
strInsert.AppendLine(" SHIPPERS,--承运商");
strInsert.AppendLine(" ORI_BILL,--来源单号");
strInsert.AppendLine(" REMARK,WAREHOUSING_ID) --备注");
strInsert.AppendLine("values");
strInsert.AppendLine("('"+model.ACCE_ID+"', --验收单号");
strInsert.AppendLine(" '" + model.LOT_ID + "', --入库单号");
strInsert.AppendLine(" '" + model.GOODS_ID + "', --商品编号");
strInsert.AppendLine(" '" + model.STO_NUM + "', --入库数量");
strInsert.AppendLine(" '" + model.BUY_NUM + "', --采购数量");
strInsert.AppendLine(" '" + model.ARR_NUM + "', --到货数量");
strInsert.AppendLine(" '" + model.ACCE_NUM + "' , --验货数量");
strInsert.AppendLine(" '" + model.PASS_NUM + "' , --合格数量");
strInsert.AppendLine(" '" + model.REC_NUM + "' , --接收数量");
strInsert.AppendLine(" '" + model.NO_REC_NUM + "' , --拒绝数量");
strInsert.AppendLine(" '" + model.RESULTS_TYPE + "', --验收结果");
strInsert.AppendLine(" '" + model.ACCE_MAN + "' , --验收人");
strInsert.AppendLine(" '" + model.ACCE_DATE + "', --验收日期");
strInsert.AppendLine(" '" + model.OPERATORID + "', --录入人");
strInsert.AppendLine(" getdate(), --录入日期");
strInsert.AppendLine(" 1, --验收状态");
strInsert.AppendLine(" '" + model.UNIT + "', --基本单位");
strInsert.AppendLine(" '" + model.PRICE + "' , --标准金额");
strInsert.AppendLine(" '" + model.SHIPPERS + "' ,--承运商");
strInsert.AppendLine(" '" + model.ORI_BILL + "' ,--来源单号");
strInsert.AppendLine(" '" + model.REMARK + "','"+model.WAREHOUSING_ID+"') --备注");
#endregion
SystemDataObject.Instance.ExecuteSql(strInsert.ToString());
}
#endregion
#region 更新 收货验收 回写入库批次 入库明细表 数据
///
/// 更新验收情况
///
///
public void UpdateAcceptance(AcceptanceModel model)
{
StringBuilder strUpdate = new StringBuilder();
strUpdate = new StringBuilder();
strUpdate.Append(" update T_RK_ACCEPTANCE");
strUpdate.Append(" set ");
strUpdate.Append(" BUY_NUM =:BUY_NUM,");
strUpdate.Append(" ARR_NUM =:ARR_NUM,");
strUpdate.Append(" ACCE_NUM =:ACCE_NUM,");
strUpdate.Append(" PASS_NUM =:PASS_NUM,");
strUpdate.Append(" REC_NUM =:REC_NUM,");
strUpdate.Append(" NO_REC_NUM =:NO_REC_NUM,");
strUpdate.Append(" RESULTS_TYPE =:RESULTS_TYPE,");
strUpdate.Append(" ACCE_MAN =:ACCE_MAN,");
strUpdate.Append(" ACCE_DATE =:ACCE_DATE,");
strUpdate.Append(" REMARK =:REMARK,");
strUpdate.Append(" OPERATORID =:OPERATORID,");
strUpdate.Append(" OPERATEDATE =getdate()");
strUpdate.Append(" STO_NUM =:STO_NUM,");
strUpdate.Append(" UNIT =:UNIT,");
strUpdate.Append(" PRICE =:PRICE,");
strUpdate.Append(" SHIPPERS =:SHIPPERS,");
strUpdate.Append(" ORI_BILL =:ORI_BILL,");
strUpdate.Append(" ACC_STATUS =:ACC_STATUS");
strUpdate.Append(" where ");
strUpdate.Append(" ACCE_ID =:ACCE_ID");
strUpdate.Append(" and ");
strUpdate.Append(" GOODS_ID =:GOODS_ID");
strUpdate.Append(" and ");
strUpdate.Append(" LOT_ID =:LOT_ID");
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":BUY_NUM",OracleDbType.Decimal),
new OracleParameter(":ARR_NUM",OracleDbType.Decimal),
new OracleParameter(":ACCE_NUM",OracleDbType.Decimal),
new OracleParameter(":PASS_NUM",OracleDbType.Decimal),
new OracleParameter(":REC_NUM",OracleDbType.Decimal),
new OracleParameter(":NO_REC_NUM",OracleDbType.Decimal),
new OracleParameter(":RESULTS_TYPE",OracleDbType.Varchar2),
new OracleParameter(":ACCE_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACCE_DATE",OracleDbType.Date),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
new OracleParameter(":OPERATEDATE",OracleDbType.Date),
new OracleParameter(":STO_NUM",OracleDbType.Decimal),
new OracleParameter(":UNIT",OracleDbType.Varchar2),
new OracleParameter(":PRICE",OracleDbType.Decimal),
new OracleParameter(":SHIPPERS",OracleDbType.Varchar2),
new OracleParameter(":ORI_BILL",OracleDbType.Varchar2),
new OracleParameter(":ACC_STATUS",OracleDbType.Varchar2),
new OracleParameter(":ACCE_ID",OracleDbType.Varchar2),
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.BUY_NUM;
param[1].Value = model.ARR_NUM;
param[2].Value = model.ACCE_NUM;
param[3].Value = model.PASS_NUM;
param[4].Value = model.REC_NUM;
param[5].Value = model.NO_REC_NUM;
param[6].Value = model.RESULTS_TYPE;
param[7].Value = model.ACCE_MAN;
param[8].Value = model.ACCE_DATE;
param[9].Value = model.REMARK;
param[10].Value = model.OPERATORID;
//param[11].Value = model.OPERATEDATE;
param[11].Value = model.STO_NUM;
param[12].Value = model.UNIT;
param[13].Value = model.PRICE;
param[14].Value = model.SHIPPERS;
param[15].Value = model.ORI_BILL;
param[16].Value = "1";
param[17].Value = model.ACCE_ID;
param[18].Value = model.GOODS_ID;
param[19].Value = model.LOT_ID;
SystemDataObject.Instance.ExecuteSql(strUpdate.ToString(), param);
}
///
/// 更新收货验收 回写入库批次 入库明细表 数据
///
/// 收货验收表
/// 入库批次主表
/// 入库批次明细表
public void UpdateAcceptanceStorageData(List listmodel, List stolist, List infolist)
{
StringBuilder strUpdate = new StringBuilder();
List updatesqlstrlist = new List();
List updatesqlpara = new List();
#region 更新 Acceptance
foreach (AcceptanceModel model in listmodel)
{
#region SQL语句 Parameter参数
strUpdate = new StringBuilder();
strUpdate.Append(" update T_RK_ACCEPTANCE");
strUpdate.Append(" set ");
strUpdate.Append(" BUY_NUM =:BUY_NUM,");
strUpdate.Append(" ARR_NUM =:ARR_NUM,");
strUpdate.Append(" ACCE_NUM =:ACCE_NUM,");
strUpdate.Append(" PASS_NUM =:PASS_NUM,");
strUpdate.Append(" REC_NUM =:REC_NUM,");
strUpdate.Append(" NO_REC_NUM =:NO_REC_NUM,");
strUpdate.Append(" RESULTS_TYPE =:RESULTS_TYPE,");
strUpdate.Append(" ACCE_MAN =:ACCE_MAN,");
strUpdate.Append(" ACCE_DATE =:ACCE_DATE,");
strUpdate.Append(" REMARK =:REMARK,");
strUpdate.Append(" OPERATORID =:OPERATORID,");
strUpdate.Append(" OPERATEDATE =:OPERATEDATE,");
strUpdate.Append(" STO_NUM =:STO_NUM,");
strUpdate.Append(" UNIT =:UNIT,");
strUpdate.Append(" PRICE =:PRICE,");
strUpdate.Append(" SHIPPERS =:SHIPPERS,");
strUpdate.Append(" ORI_BILL =:ORI_BILL,");
strUpdate.Append(" ACC_STATUS =:ACC_STATUS");
strUpdate.Append(" where ");
strUpdate.Append(" ACCE_ID =:ACCE_ID");
strUpdate.Append(" and ");
strUpdate.Append(" GOODS_ID =:GOODS_ID");
strUpdate.Append(" and ");
strUpdate.Append(" LOT_ID =:LOT_ID");
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":BUY_NUM",OracleDbType.Decimal),
new OracleParameter(":ARR_NUM",OracleDbType.Decimal),
new OracleParameter(":ACCE_NUM",OracleDbType.Decimal),
new OracleParameter(":PASS_NUM",OracleDbType.Decimal),
new OracleParameter(":REC_NUM",OracleDbType.Decimal),
new OracleParameter(":NO_REC_NUM",OracleDbType.Decimal),
new OracleParameter(":RESULTS_TYPE",OracleDbType.Varchar2),
new OracleParameter(":ACCE_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACCE_DATE",OracleDbType.Date),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
new OracleParameter(":OPERATEDATE",OracleDbType.Date),
new OracleParameter(":STO_NUM",OracleDbType.Decimal),
new OracleParameter(":UNIT",OracleDbType.Varchar2),
new OracleParameter(":PRICE",OracleDbType.Decimal),
new OracleParameter(":SHIPPERS",OracleDbType.Varchar2),
new OracleParameter(":ORI_BILL",OracleDbType.Varchar2),
new OracleParameter(":ACC_STATUS",OracleDbType.Varchar2),
new OracleParameter(":ACCE_ID",OracleDbType.Varchar2),
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.BUY_NUM;
param[1].Value = model.ARR_NUM;
param[2].Value = model.ACCE_NUM;
param[3].Value = model.PASS_NUM;
param[4].Value = model.REC_NUM;
param[5].Value = model.NO_REC_NUM;
param[6].Value = model.RESULTS_TYPE;
param[7].Value = model.ACCE_MAN;
param[8].Value = model.ACCE_DATE;
param[9].Value = model.REMARK;
param[10].Value = model.OPERATORID;
param[11].Value = model.OPERATEDATE;
param[12].Value = model.STO_NUM;
param[13].Value = model.UNIT;
param[14].Value = model.PRICE;
param[15].Value = model.SHIPPERS;
param[16].Value = model.ORI_BILL;
param[17].Value = "1";
param[18].Value = model.ACCE_ID;
param[19].Value = model.GOODS_ID;
param[20].Value = model.LOT_ID;
#endregion
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(param);
strUpdate.Clear();
}
#endregion
#region 回写 StorageLot
if (!string.IsNullOrEmpty(stolist[0].LOT_ID))
{
foreach (StorageLotModel model in stolist)
{
strUpdate = new StringBuilder();
strUpdate.Append("update T_RK_STORAGE_LOT set ACC_MAN=:ACC_MAN,ACC_NUM_SUM=:ACC_NUM_SUM where LOT_ID=:LOT_ID");
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":ACC_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACC_NUM_SUM",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.ACC_MAN;
param[1].Value = model.ACC_NUM_SUM;
param[2].Value = model.LOT_ID;
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(param);
strUpdate.Clear();
}
}
#endregion
#region 回写 StorageLotInfo
if (!string.IsNullOrEmpty(infolist[0].LOT_ID))
{
foreach (StorageLotInfoModel info in infolist)
{
strUpdate = new StringBuilder();
strUpdate.Append("update T_RK_STORAGE_LOT_INFO set ACC_NUM =:ACC_NUM where LOT_ID=:LOT_ID and GOODS_ID =:GOODS_ID");
OracleParameter[] paraminfo = new OracleParameter[] {
new OracleParameter(":ACC_NUM",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2),
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2)
};
paraminfo[0].Value = info.ACC_NUM;
paraminfo[1].Value = info.LOT_ID;
paraminfo[2].Value = info.GOODS_ID;
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(paraminfo);
strUpdate.Clear();
}
}
#endregion
SystemDataObject.Instance.ExecuteList(updatesqlstrlist, updatesqlpara);
}
#endregion
#region 改
///
/// 改 收货验收 更新数据
///
/// 验收Model集合
public void UpdateAcceptance(List listmodel)
{
StringBuilder strUpdate = new StringBuilder();
List updatesqlstrlist = new List();
List updatesqlpara = new List();
foreach (AcceptanceModel model in listmodel)
{
#region SQL语句 Parameter参数
strUpdate.Append(" update T_RK_ACCEPTANCE");
strUpdate.Append(" set ");
strUpdate.Append(" BUY_NUM =:BUY_NUM,");
strUpdate.Append(" ARR_NUM =:ARR_NUM,");
strUpdate.Append(" ACCE_NUM =:ACCE_NUM,");
strUpdate.Append(" PASS_NUM =:PASS_NUM,");
strUpdate.Append(" REC_NUM =:REC_NUM,");
strUpdate.Append(" NO_REC_NUM =:NO_REC_NUM,");
strUpdate.Append(" RESULTS_TYPE =:RESULTS_TYPE,");
strUpdate.Append(" ACCE_MAN =:ACCE_MAN,");
strUpdate.Append(" ACCE_DATE =:ACCE_DATE,");
strUpdate.Append(" REMARK =:REMARK,");
strUpdate.Append(" OPERATORID =:OPERATORID,");
strUpdate.Append(" OPERATEDATE =:OPERATEDATE,");
strUpdate.Append(" STO_NUM =:STO_NUM,");
strUpdate.Append(" UNIT =:UNIT,");
strUpdate.Append(" PRICE =:PRICE,");
strUpdate.Append(" SHIPPERS =:SHIPPERS,");
strUpdate.Append(" ORI_BILL =:ORI_BILL,");
strUpdate.Append(" ACC_STATUS =:ACC_STATUS");
strUpdate.Append(" where ");
strUpdate.Append(" ACCE_ID =:ACCE_ID");
strUpdate.Append(" and ");
strUpdate.Append(" GOODS_ID =:GOODS_ID");
strUpdate.Append(" and ");
strUpdate.Append(" LOT_ID =:LOT_ID");
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":BUY_NUM",OracleDbType.Decimal),
new OracleParameter(":ARR_NUM",OracleDbType.Decimal),
new OracleParameter(":ACCE_NUM",OracleDbType.Decimal),
new OracleParameter(":PASS_NUM",OracleDbType.Decimal),
new OracleParameter(":REC_NUM",OracleDbType.Decimal),
new OracleParameter(":NO_REC_NUM",OracleDbType.Decimal),
new OracleParameter(":RESULTS_TYPE",OracleDbType.Varchar2),
new OracleParameter(":ACCE_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACCE_DATE",OracleDbType.Date),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
new OracleParameter(":OPERATEDATE",OracleDbType.Date),
new OracleParameter(":STO_NUM",OracleDbType.Decimal),
new OracleParameter(":UNIT",OracleDbType.Varchar2),
new OracleParameter(":PRICE",OracleDbType.Decimal),
new OracleParameter(":SHIPPERS",OracleDbType.Varchar2),
new OracleParameter(":ORI_BILL",OracleDbType.Varchar2),
new OracleParameter(":ACC_STATUS",OracleDbType.Varchar2),
new OracleParameter(":ACCE_ID",OracleDbType.Varchar2),
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.BUY_NUM;
param[1].Value = model.ARR_NUM;
param[2].Value = model.ACCE_NUM;
param[3].Value = model.PASS_NUM;
param[4].Value = model.REC_NUM;
param[5].Value = model.NO_REC_NUM;
param[6].Value = model.RESULTS_TYPE;
param[7].Value = model.ACCE_MAN;
param[8].Value = model.ACCE_DATE;
param[9].Value = model.REMARK;
param[10].Value = model.OPERATORID;
param[11].Value = model.OPERATEDATE;
param[12].Value = model.STO_NUM;
param[13].Value = model.UNIT;
param[14].Value = model.PRICE;
param[15].Value = model.SHIPPERS;
param[16].Value = model.ORI_BILL;
param[17].Value = "1";
param[18].Value = model.ACCE_ID;
param[19].Value = model.GOODS_ID;
param[20].Value = model.LOT_ID;
#endregion
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(param);
strUpdate.Clear();
}
SystemDataObject.Instance.ExecuteList(updatesqlstrlist, updatesqlpara);
}
#endregion
#region 改 回写入库批次信息
///
/// 回写StorageLot 某些属性字段值
///
/// 入库批次Model集合
public void UpdateStorage(List listmodel)
{
string strUpdate = "";
List updatesqlstrlist = new List();
List updatesqlpara = new List();
foreach (StorageLotModel model in listmodel)
{
strUpdate = "update T_RK_STORAGE_LOT set ACC_MAN=:ACC_MAN,ACC_NUM_SUM=:ACC_NUM_SUM where LOT_ID=:LOT_ID";
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":ACC_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACC_NUM_SUM",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.ACC_MAN;
param[1].Value = model.ACC_NUM_SUM;
param[2].Value = model.LOT_ID;
updatesqlstrlist.Add(strUpdate);
updatesqlpara.Add(param);
}
SystemDataObject.Instance.ExecuteList(updatesqlstrlist, updatesqlpara);
}
#endregion
#region 回写入库批次表 明细表 接收数量
///
/// 回写入库批次表、入库批次明细表 接收数量
///
/// 主表StorageLotModel
/// 明细表StorageLotInfoModel
public void UpdateStorageData(List listmodel, List listinfo)
{
StringBuilder strUpdate = new StringBuilder();
List updatesqlstrlist = new List();
List updatesqlpara = new List();
if (!string.IsNullOrEmpty(listmodel[0].LOT_ID))
{
foreach (StorageLotModel model in listmodel)
{
strUpdate.Append("update T_RK_STORAGE_LOT set ACC_MAN=:ACC_MAN,ACC_NUM_SUM=:ACC_NUM_SUM where LOT_ID=:LOT_ID");
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":ACC_MAN",OracleDbType.Varchar2),
new OracleParameter(":ACC_NUM_SUM",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2)
};
param[0].Value = model.ACC_MAN;
param[1].Value = model.ACC_NUM_SUM;
param[2].Value = model.LOT_ID;
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(param);
strUpdate.Clear();
}
}
foreach (StorageLotInfoModel info in listinfo)
{
strUpdate.Append("update T_RK_STORAGE_LOT_INFO set ACC_NUM =:ACC_NUM where LOT_ID=:LOT_ID and GOODS_ID =:GOODS_ID");
OracleParameter[] paraminfo = new OracleParameter[] {
new OracleParameter(":ACC_NUM",OracleDbType.Varchar2),
new OracleParameter(":LOT_ID",OracleDbType.Varchar2),
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2)
};
paraminfo[0].Value = info.ACC_NUM;
paraminfo[1].Value = info.LOT_ID;
paraminfo[2].Value = info.GOODS_ID;
updatesqlstrlist.Add(strUpdate.ToString());
updatesqlpara.Add(paraminfo);
strUpdate.Clear();
}
SystemDataObject.Instance.ExecuteList(updatesqlstrlist, updatesqlpara);
}
#endregion
#region 删除数据添加到备份表
///
/// 删除数据 添加到备份表
///
/// 验收ID
public void InsertAcceptanceBack(List listmodel)
{
string strSql = string.Empty;
List delsqlstrlist = new List();
List delsqlpara = new List();
foreach (AcceptanceModel model in listmodel)
{
strSql = "insert into T_RK_ACCEPTANCE_BACK select * from T_RK_ACCEPTANCE where ACCE_ID =:ACCE_ID";
OracleParameter[] param = new OracleParameter[]{
new OracleParameter("ACCE_ID",OracleDbType.Varchar2)};
param[0].Value = model.ACCE_ID;
delsqlstrlist.Add(strSql);
delsqlpara.Add(param);
}
SystemDataObject.Instance.ExecuteList(delsqlstrlist, delsqlpara);
}
#endregion
#region 直接删除
///
/// 删 收货验收 删除数据
///
/// 验收Model集合
public void DeleteAcceptance(List listmodel)
{
string strDelete = string.Empty;
List delsqlstrlist = new List();
List delsqlpara = new List();
foreach (AcceptanceModel model in listmodel)
{
#region SQL语句 Parameter参数
strDelete =@"delete from T_RK_ACCEPTANCE where ACCE_ID =:ACCE_ID ";
OracleParameter[] param = new OracleParameter[] {
new OracleParameter(":ACCE_ID",OracleDbType.Varchar2),
};
param[0].Value = model.ACCE_ID;
#endregion
delsqlstrlist.Add(strDelete);
delsqlpara.Add(param);
}
SystemDataObject.Instance.ExecuteList(delsqlstrlist, delsqlpara);
}
#endregion
///
/// 获取验收流水号
///
///
public string GetNextValNum()
{
string strSqlSeq = "";
strSqlSeq = "select NEXT VALUE FOR seq_rk_acceptance_id";
DataTable dt = SystemDataObject.Instance.GetDataTable(strSqlSeq);
string strSEQ = "YS" + DateTime.Now.ToString("yyyyMMdd") + dt.Rows[0][0].ToString();
return strSEQ;
}
///
/// 回写验收单号
///
///
///
//public void UpdateWareData(string wareId)
//{
// string sqlStr = "update T_RK_WARE_NOTICE set ";
//}
////修改验收单号
public void UpdateInData(string wareid,string status)
{
string sqlStr = "update T_RK_WARE_NOTICE set state ='"+status+"' where WAREHOUSING_ID='"+wareid+"'";
SystemDataObject.Instance.ExecuteSql(sqlStr);
}
///
/// 获得回写数据
///
///
///
public DataTable GetWriteBackData(AcceptanceModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select LOT_ID,sum(REC_NUM) REC_NUM,ACCE_MAN,ACCE_ID from T_RK_ACCEPTANCE where 1 = 1 ");
strSql.Append(" and Lot_id like '%" + model.LOT_ID + "%'");
strSql.Append(" and ACCE_MAN like '%" + model.ACCE_MAN + "%'");
strSql.Append(" and ACCE_ID like '%" + model.ACCE_ID + "%'");
strSql.Append(" group by Lot_ID,ACCE_MAN,ACCE_ID");
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
}
}
}