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