264 lines
13 KiB
C#
264 lines
13 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using WMS.IData.IStock;
|
|||
|
|
using WMS.Model.Stock;
|
|||
|
|
using System.Data;
|
|||
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace WMS.SqlServerData.StockData
|
|||
|
|
{
|
|||
|
|
public class AdjustListData : IAdjustList
|
|||
|
|
{
|
|||
|
|
public DataTable GetAdjustList(AdjustListModel model)
|
|||
|
|
{
|
|||
|
|
// string sql = "";
|
|||
|
|
|
|||
|
|
#region 查询SQL
|
|||
|
|
string sql =@" select e.STORAGE_NAME storage_name, --仓库名称
|
|||
|
|
d.name List_confirmpername, --确认人
|
|||
|
|
c.name List_checkpername, --审核人
|
|||
|
|
b.name List_createperName, --创建人
|
|||
|
|
a.LIST_ID, --库存调整单编号
|
|||
|
|
a.LIST_DATE, --调整日期
|
|||
|
|
a.COUNT_ID, --盘点单号
|
|||
|
|
a.LIST_REASON, --调整原因(0 盘点单生成，1 主动新增，2 其它原因)
|
|||
|
|
a.LIST_CHECKPER, --审核人
|
|||
|
|
a.LIST_COUNT, --调整总数量(正为增加，负为减少)
|
|||
|
|
a.STATUS, --Status
|
|||
|
|
a.REMARK, --备注
|
|||
|
|
a.VERSION, --版本
|
|||
|
|
a.OPERATORID, --操作人
|
|||
|
|
a.OPERATEDATE, --操作日期
|
|||
|
|
a.LIST_CREATEPER, --创建编号
|
|||
|
|
a.LIST_CONFIRMPER, --确认人编号
|
|||
|
|
a.LIST_CHECKDATE, --审核日期
|
|||
|
|
a.LIST_CREATEDATE, --创建日期
|
|||
|
|
a.LIST_CONFIRMDATE, --确认日期
|
|||
|
|
a.STORAGE_ID --仓库编号
|
|||
|
|
from T_STOCK_ADJUSTLIST a left join T_BASE_USERINFO b
|
|||
|
|
on a.LIST_CREATEPER = b.user_id left join T_BASE_USERINFO c
|
|||
|
|
on a.LIST_CHECKPER = c.user_id left join T_BASE_USERINFO d
|
|||
|
|
on a.LIST_CONFIRMPER = d.user_id left join T_BASE_STORAGEINFO e
|
|||
|
|
on a.STORAGE_ID = e.STORAGE_ID
|
|||
|
|
where 1 = 1 ";
|
|||
|
|
|
|||
|
|
//if (!string.IsNullOrEmpty(model.List_id))
|
|||
|
|
//{
|
|||
|
|
// sql += " and a.LIST_ID ='" + model.List_id + "'";
|
|||
|
|
//}
|
|||
|
|
//if (!string.IsNullOrEmpty(model.Status))
|
|||
|
|
//{
|
|||
|
|
// sql += " and a.STATUS = '" + model.Status + "'";
|
|||
|
|
//}
|
|||
|
|
//if (model.List_createdate != System.DateTime.MinValue && model.List_createdate != System.DateTime.MaxValue)
|
|||
|
|
//{
|
|||
|
|
// //sql += " LIST_CREATEDATE = to_date('" + model.List_date + "','yyyy-MM-dd')";
|
|||
|
|
// sql += " and convert(varchar(100),a.LIST_CREATEDATE,23) <= '" + model.List_createdate.ToString("yyyy-MM-dd") + "'";
|
|||
|
|
|
|||
|
|
// sql += " and convert(varchar(100),a.LIST_CREATEDATE,23) >'" + model.List_createdate.AddDays(-30).ToString("yyyy-MM-dd") + "'";
|
|||
|
|
//}
|
|||
|
|
sql += " order by a.STATUS,a.LIST_ID desc";
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public void ExecListAdd(AdjustListModel model)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
|
|||
|
|
#region 新增SQL
|
|||
|
|
sql +=@"insert into T_STOCK_ADJUSTLIST(
|
|||
|
|
LIST_ID,LIST_DATE,COUNT_ID,
|
|||
|
|
LIST_REASON,LIST_CHECKPER,LIST_COUNT,
|
|||
|
|
STATUS,REMARK,VERSION,
|
|||
|
|
OPERATORID,OPERATEDATE,
|
|||
|
|
LIST_CREATEPER,LIST_CHECKDATE,
|
|||
|
|
LIST_CREATEDATE,LIST_CONFIRMDATE,LIST_CONFIRMPER,STORAGE_ID)
|
|||
|
|
values(
|
|||
|
|
:LIST_ID, --调整库存单据编号
|
|||
|
|
:LIST_DATE, --调整日期
|
|||
|
|
:COUNT_ID, --盘点单号
|
|||
|
|
:LIST_REASON, --调整原因
|
|||
|
|
:LIST_CHECKPER, --审核人
|
|||
|
|
:LIST_COUNT, --调整数量(正为增加，负为减少)
|
|||
|
|
:STATUS, --Status
|
|||
|
|
:REMARK, --备注
|
|||
|
|
:VERSION, --版本
|
|||
|
|
:OPERATORID, --操作人
|
|||
|
|
:OPERATEDATE, --操作日期
|
|||
|
|
:LIST_CREATEPER, --创建人
|
|||
|
|
:LIST_CHECKDATE, --审核日期
|
|||
|
|
:LIST_CREATEDATE, --创建日期
|
|||
|
|
:LIST_CONFIRMDATE, --确认日期
|
|||
|
|
:LIST_CONFIRMPER, --确认人
|
|||
|
|
:STORAGE_ID) --仓库编号 ";
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region 参数添加
|
|||
|
|
|
|||
|
|
OracleParameter[] parameters = new OracleParameter[] {
|
|||
|
|
new OracleParameter(":LIST_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_DATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_REASON",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_COUNT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CHECKPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":STATUS",OracleDbType.Char),
|
|||
|
|
new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":VERSION",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OPERATEDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CREATEPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CHECKDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CREATEDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CONFIRMDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CONFIRMPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)};
|
|||
|
|
|
|||
|
|
parameters[0].Value = model.List_id;
|
|||
|
|
parameters[1].Value = model.List_date;
|
|||
|
|
parameters[2].Value = model.Count_id;
|
|||
|
|
parameters[3].Value = model.List_reason;
|
|||
|
|
parameters[4].Value = model.List_count;
|
|||
|
|
parameters[5].Value = model.List_checkper;
|
|||
|
|
parameters[6].Value = model.Status;
|
|||
|
|
parameters[7].Value = model.Remark;
|
|||
|
|
parameters[8].Value = model.Version;
|
|||
|
|
parameters[9].Value = model.Operatorid;
|
|||
|
|
parameters[10].Value = model.Operatedate;
|
|||
|
|
parameters[11].Value = model.List_createper;
|
|||
|
|
parameters[12].Value = model.List_checkdate;
|
|||
|
|
parameters[13].Value = model.List_createdate;
|
|||
|
|
parameters[14].Value = model.List_confirmdate;
|
|||
|
|
parameters[15].Value = model.List_confirmper;
|
|||
|
|
parameters[16].Value = model.Storage_id;
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sql, parameters);
|
|||
|
|
}
|
|||
|
|
public void AddInvertory(string plcId,string loc,string sation)
|
|||
|
|
{
|
|||
|
|
string sqlStr = "insert into [dbo].[T_STOCK_Invertory]" +
|
|||
|
|
" (plcId,LOCATION_ID,station)" +
|
|||
|
|
"values('"+plcId+"','"+loc+"','"+ sation + "' )";
|
|||
|
|
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
|||
|
|
}
|
|||
|
|
public DataTable GetIncertory()
|
|||
|
|
{
|
|||
|
|
string sqlStr = " select * from T_STOCK_Invertory";
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|||
|
|
}
|
|||
|
|
public void ExecListUpdate(AdjustListModel model)
|
|||
|
|
{
|
|||
|
|
#region 修改SQL
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("update T_STOCK_ADJUSTLIST set ");
|
|||
|
|
strSql.Append("LIST_ID=:LIST_ID,");
|
|||
|
|
strSql.Append("LIST_DATE=:LIST_DATE,");
|
|||
|
|
strSql.Append("COUNT_ID=:COUNT_ID,");
|
|||
|
|
strSql.Append("LIST_REASON=:LIST_REASON,");
|
|||
|
|
strSql.Append("LIST_CHECKPER=:LIST_CHECKPER,");
|
|||
|
|
strSql.Append("LIST_COUNT=:LIST_COUNT,");
|
|||
|
|
strSql.Append("REMARK=:REMARK,");
|
|||
|
|
strSql.Append("VERSION=:VERSION,");
|
|||
|
|
strSql.Append("OPERATORID=:OPERATORID,");
|
|||
|
|
strSql.Append("OPERATEDATE=:OPERATEDATE,");
|
|||
|
|
strSql.Append("STATUS=:STATUS,");
|
|||
|
|
strSql.Append("LIST_CREATEPER=:LIST_CREATEPER,");
|
|||
|
|
strSql.Append("LIST_CHECKDATE=:LIST_CHECKDATE,");
|
|||
|
|
strSql.Append("LIST_CREATEDATE=:LIST_CREATEDATE,");
|
|||
|
|
strSql.Append("LIST_CONFIRMDATE=:LIST_CONFIRMDATE,");
|
|||
|
|
strSql.Append("LIST_CONFIRMPER=:LIST_CONFIRMPER,");
|
|||
|
|
strSql.Append("STORAGE_ID=:STORAGE_ID");
|
|||
|
|
strSql.Append(" where LIST_ID=:LIST_ID ");
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region 参数添加
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":LIST_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_DATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_REASON",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CHECKPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_COUNT",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":VERSION",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":OPERATEDATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":STATUS",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CREATEPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CHECKDATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CREATEDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CONFIRMDATE",OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CONFIRMPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2) };
|
|||
|
|
parameters[0].Value = model.List_id;
|
|||
|
|
parameters[1].Value = model.List_date;
|
|||
|
|
parameters[2].Value = model.Count_id;
|
|||
|
|
parameters[3].Value = model.List_reason;
|
|||
|
|
parameters[4].Value = model.List_checkper;
|
|||
|
|
parameters[5].Value = model.List_count;
|
|||
|
|
parameters[6].Value = model.Remark;
|
|||
|
|
parameters[7].Value = model.Version;
|
|||
|
|
parameters[8].Value = model.Operatorid;
|
|||
|
|
parameters[9].Value = model.Operatedate;
|
|||
|
|
parameters[10].Value = model.Status;
|
|||
|
|
parameters[11].Value = model.List_createper;
|
|||
|
|
parameters[12].Value = model.List_checkdate;
|
|||
|
|
parameters[13].Value = model.List_createdate;
|
|||
|
|
parameters[14].Value = model.List_confirmdate;
|
|||
|
|
parameters[15].Value = model.List_confirmper;
|
|||
|
|
parameters[16].Value = model.Storage_id;
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public void DelList(AdjustListModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("delete from T_STOCK_ADJUSTLIST ");
|
|||
|
|
strSql.Append(" where LIST_ID=:LIST_ID ");
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":LIST_ID",OracleDbType.Varchar2)};
|
|||
|
|
parameters[0].Value = model.List_id;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public void Check(AdjustListModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("update T_STOCK_ADJUSTLIST set ");
|
|||
|
|
strSql.Append("STATUS=:STATUS,");
|
|||
|
|
strSql.Append("LIST_CHECKDATE=:LIST_CHECKDATE,");
|
|||
|
|
strSql.Append("LIST_CREATEPER=:LIST_CREATEPER");
|
|||
|
|
strSql.Append(" where LIST_ID=:LIST_ID ");
|
|||
|
|
|
|||
|
|
#region 参数添加
|
|||
|
|
OracleParameter[] parameters = {
|
|||
|
|
new OracleParameter(":STATUS",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_CHECKDATE", OracleDbType.Date),
|
|||
|
|
new OracleParameter(":LIST_CREATEPER",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":LIST_ID",OracleDbType.Varchar2)};
|
|||
|
|
|
|||
|
|
parameters[0].Value = model.Status;
|
|||
|
|
parameters[1].Value = model.List_checkdate;
|
|||
|
|
parameters[2].Value = model.List_checkper;
|
|||
|
|
parameters[3].Value = model.List_id;
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|