248 lines
10 KiB
C#
248 lines
10 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|
using WMS.Model.Stock;
|
|
|
|
using WMS.DBUtility;
|
|
using System.Data;
|
|
using WMS.IData.IStock;
|
|
|
|
namespace WMS.SqlServerData.StockData
|
|
{
|
|
|
|
public partial class CountingData :ICounting
|
|
{
|
|
#region BasicMethod
|
|
|
|
/// <summary>
|
|
/// 增加一条数据
|
|
/// </summary>
|
|
public void Add(CountingModel model)
|
|
{
|
|
StringBuilder strSql=new StringBuilder();
|
|
strSql.Append("insert into T_STOCK_COUNTING(");
|
|
strSql.Append("COUNT_ID,COUNT_DATE,COUNT_MAN,CONFIRM_MAN,CONFIRM_DATE,PLAN_MAN,PLAN_DATE,REVIEW_MAN,REVIEW_DATE,STO_ID,COUNT_TYPE,REMARK,COUNT_BACK,COUNT_STATUS,CREATE_TYPE,ori_id)");
|
|
strSql.Append(" values (");
|
|
strSql.Append(":COUNT_ID,:COUNT_DATE,:COUNT_MAN,:CONFIRM_MAN,:CONFIRM_DATE,:PLAN_MAN,:PLAN_DATE,:REVIEW_MAN,:REVIEW_DATE,:STO_ID,:COUNT_TYPE,:REMARK,:COUNT_BACK,:COUNT_STATUS,:CREATE_TYPE,:ori_id)");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_DATE", OracleDbType.Date),
|
|
new OracleParameter(":COUNT_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":CONFIRM_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":CONFIRM_DATE", OracleDbType.Date),
|
|
new OracleParameter(":PLAN_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":PLAN_DATE", OracleDbType.Date),
|
|
new OracleParameter(":REVIEW_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":REVIEW_DATE", OracleDbType.Date),
|
|
new OracleParameter(":STO_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_TYPE",OracleDbType.Varchar2),
|
|
new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_BACK",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
|
|
new OracleParameter(":CREATE_TYPE",OracleDbType.Varchar2),
|
|
new OracleParameter(":ori_id",OracleDbType.Varchar2)};
|
|
parameters[0].Value = model.COUNT_ID;
|
|
parameters[1].Value = model.COUNT_DATE;
|
|
parameters[2].Value = model.COUNT_MAN;
|
|
parameters[3].Value = model.CONFIRM_MAN;
|
|
parameters[4].Value = model.CONFIRM_DATE;
|
|
parameters[5].Value = model.PLAN_MAN;
|
|
parameters[6].Value = model.PLAN_DATE;
|
|
parameters[7].Value = model.REVIEW_MAN;
|
|
parameters[8].Value = model.REVIEW_DATE;
|
|
parameters[9].Value = model.STO_ID;
|
|
parameters[10].Value = model.COUNT_TYPE;
|
|
parameters[11].Value = model.REMARK;
|
|
parameters[12].Value = model.COUNT_BACK;
|
|
parameters[13].Value = model.COUNT_STATUS;
|
|
parameters[14].Value = model.CREATE_TYPE;
|
|
parameters[15].Value = model.ORI_ID;
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
/// <summary>
|
|
/// 更新一条数据
|
|
/// </summary>
|
|
public void Update(CountingModel model)
|
|
{
|
|
StringBuilder strSql=new StringBuilder();
|
|
strSql.Append("update T_STOCK_COUNTING set ");
|
|
strSql.Append("COUNT_DATE=:COUNT_DATE,");
|
|
strSql.Append("COUNT_MAN=:COUNT_MAN,");
|
|
strSql.Append("CONFIRM_MAN=:CONFIRM_MAN,");
|
|
strSql.Append("CONFIRM_DATE=:CONFIRM_DATE,");
|
|
strSql.Append("PLAN_MAN=:PLAN_MAN,");
|
|
strSql.Append("PLAN_DATE=:PLAN_DATE,");
|
|
strSql.Append("REVIEW_MAN=:REVIEW_MAN,");
|
|
strSql.Append("REVIEW_DATE=:REVIEW_DATE,");
|
|
strSql.Append("STO_ID=:STO_ID,");
|
|
strSql.Append("COUNT_TYPE=:COUNT_TYPE,");
|
|
strSql.Append("REMARK=:REMARK,");
|
|
strSql.Append("COUNT_BACK=:COUNT_BACK,");
|
|
strSql.Append("COUNT_STATUS=:COUNT_STATUS,");
|
|
strSql.Append("CREATE_TYPE=:CREATE_TYPE,");
|
|
strSql.Append("ori_id=:ori_id");
|
|
strSql.Append(" where COUNT_ID=:COUNT_ID ");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":COUNT_DATE", OracleDbType.Date),
|
|
new OracleParameter(":COUNT_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":CONFIRM_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":CONFIRM_DATE", OracleDbType.Date),
|
|
new OracleParameter(":PLAN_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":PLAN_DATE", OracleDbType.Date),
|
|
new OracleParameter(":REVIEW_MAN",OracleDbType.Varchar2),
|
|
new OracleParameter(":REVIEW_DATE", OracleDbType.Date),
|
|
new OracleParameter(":STO_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_TYPE",OracleDbType.Varchar2),
|
|
new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_BACK",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
|
|
new OracleParameter(":CREATE_TYPE",OracleDbType.Varchar2),
|
|
new OracleParameter(":ori_id",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
|
|
};
|
|
parameters[0].Value = model.COUNT_DATE;
|
|
parameters[1].Value = model.COUNT_MAN;
|
|
parameters[2].Value = model.CONFIRM_MAN;
|
|
parameters[3].Value = model.CONFIRM_DATE;
|
|
parameters[4].Value = model.PLAN_MAN;
|
|
parameters[5].Value = model.PLAN_DATE;
|
|
parameters[6].Value = model.REVIEW_MAN;
|
|
parameters[7].Value = model.REVIEW_DATE;
|
|
parameters[8].Value = model.STO_ID;
|
|
parameters[9].Value = model.COUNT_TYPE;
|
|
parameters[10].Value = model.REMARK;
|
|
parameters[11].Value = model.COUNT_BACK;
|
|
parameters[12].Value = model.COUNT_STATUS;
|
|
parameters[13].Value = model.CREATE_TYPE;
|
|
parameters[14].Value = model.ORI_ID;
|
|
parameters[15].Value = model.COUNT_ID;
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
/// <summary>
|
|
/// 更新Status
|
|
/// </summary>
|
|
public void UpdateImport(string id,string status)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("update T_STOCK_COUNTING set ");
|
|
strSql.Append("COUNT_STATUS=:COUNT_STATUS");
|
|
strSql.Append(" where COUNT_ID=:COUNT_ID ");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),};
|
|
parameters[0].Value = status;
|
|
parameters[1].Value = id;
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除一条数据
|
|
/// </summary>
|
|
public void Delete(string COUNT_ID)
|
|
{
|
|
|
|
StringBuilder strSql=new StringBuilder();
|
|
strSql.Append("delete from T_STOCK_COUNTING ");
|
|
strSql.Append(" where COUNT_ID=:COUNT_ID ");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2) };
|
|
parameters[0].Value = COUNT_ID;
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除一条数据
|
|
/// </summary>
|
|
public void Delete(CountingModel model)
|
|
{
|
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("delete from T_STOCK_COUNTING ");
|
|
strSql.Append(" where COUNT_ID=:COUNT_ID ");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":COUNT_ID",OracleDbType.Varchar2) };
|
|
parameters[0].Value = model.COUNT_ID;
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获得数据列表
|
|
/// </summary>
|
|
public DataTable GetList(CountingModel model)
|
|
{
|
|
StringBuilder strSql=new StringBuilder();
|
|
strSql.Append("select t.COUNT_ID,");
|
|
strSql.Append("t.COUNT_DATE,");
|
|
strSql.Append("t.COUNT_MAN,");
|
|
strSql.Append("a.NAME COUNT_MAN_NAME,");
|
|
strSql.Append("t.CONFIRM_MAN,");
|
|
strSql.Append("b.NAME CONFIRM_MAN_NAME,");
|
|
strSql.Append("t.CONFIRM_DATE,");
|
|
strSql.Append("t.PLAN_MAN,");
|
|
strSql.Append("c.NAME PLAN_MAN_NAME,");
|
|
strSql.Append("t.PLAN_DATE,");
|
|
strSql.Append("t.REVIEW_MAN,");
|
|
strSql.Append("d.NAME REVIEW_MAN_NAME,");
|
|
strSql.Append("t.REVIEW_DATE,");
|
|
strSql.Append("t.STO_ID,");
|
|
strSql.Append("e.STORAGE_NAME STO_NAME,");
|
|
strSql.Append("t.COUNT_TYPE,");
|
|
strSql.Append("t.REMARK,");
|
|
strSql.Append("t.COUNT_BACK,");
|
|
strSql.Append("t.COUNT_STATUS,");
|
|
strSql.Append("t.ori_id,");
|
|
strSql.Append("t.CREATE_TYPE ");
|
|
strSql.Append(" FROM T_STOCK_COUNTING t");
|
|
strSql.Append(" LEFT JOIN T_BASE_USERINFO a ON a.USER_ID = t.COUNT_MAN");
|
|
strSql.Append(" LEFT JOIN T_BASE_USERINFO b ON b.USER_ID = t.CONFIRM_MAN");
|
|
strSql.Append(" LEFT JOIN T_BASE_USERINFO c ON c.USER_ID = t.PLAN_MAN");
|
|
strSql.Append(" LEFT JOIN T_BASE_USERINFO d ON d.USER_ID = t.REVIEW_MAN");
|
|
strSql.Append(" LEFT JOIN T_BASE_STORAGEINFO e ON e.STORAGE_ID = t.STO_ID");
|
|
strSql.Append(" where 1=1 ");
|
|
if(!string.IsNullOrEmpty(model.COUNT_ID))
|
|
{
|
|
strSql.Append(" and t.COUNT_ID like '%" + model.COUNT_ID + "%'");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.STO_ID))
|
|
{
|
|
strSql.Append(" and t.STO_ID = '" + model.STO_ID + "'");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.COUNT_STATUS))
|
|
{
|
|
strSql.Append(" and t.COUNT_STATUS = '" + model.COUNT_STATUS + "'");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.COUNT_TYPE))
|
|
{
|
|
strSql.Append(" and t.COUNT_TYPE = '" + model.COUNT_TYPE + "'");
|
|
}
|
|
if (model.PLAN_DATE_STAR != DateTime.MinValue && model.PLAN_DATE_END != DateTime.MinValue)
|
|
{
|
|
strSql.Append(" and t.PLAN_DATE between convert(varchar(100),'" + model.PLAN_DATE_STAR.ToString("yyyy-MM-dd") + "',20) and convert(varchar(100),'" + model.PLAN_DATE_END.ToString("yyyy-MM-dd") + "',20)");
|
|
}
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
#endregion BasicMethod
|
|
|
|
/// <summary>
|
|
/// 获取流水号
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string GetSerialNum()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select NEXT VALUE FOR seq_stock_counting");
|
|
DataSet ds = SystemDataObject.Instance.GetDataSet( strSql.ToString());
|
|
return ds.Tables[0].Rows[0][0].ToString();
|
|
}
|
|
}
|
|
}
|
|
|