282 lines
13 KiB
C#
282 lines
13 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
using WMS.Model.Base;
|
|||
|
|
using WMS.IData.IBase;
|
|||
|
|
using WMS.DBUtility;
|
|||
|
|
|
|||
|
|
using WMS.IData.IStrategy;
|
|||
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|||
|
|
using WMS.Model.Strategy;
|
|||
|
|
|
|||
|
|
namespace WMS.SqlServerData.Strategy
|
|||
|
|
{
|
|||
|
|
public class UpStrategyData : IUpStrategy
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取策略的库位信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="areaInfoID"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetAreaInfoData(string areaInfoID)
|
|||
|
|
{
|
|||
|
|
string sqlStr = "select t.storage_area_id, 0 as selectd"
|
|||
|
|
+ " from T_BASE_STORAGE_AREA t"
|
|||
|
|
+ " where t.storage_area_id"
|
|||
|
|
+ " not in (select a.storage_area_id from T_UPSTYCTS a where"
|
|||
|
|
+ " t.storage_area_id = a.storage_area_id and t.storage_id = a.storage_id)"
|
|||
|
|
+ " union"
|
|||
|
|
+ " select t.storage_area_id, 1 as selectd"
|
|||
|
|
+ " from T_BASE_STORAGE_AREA t"
|
|||
|
|
+ " where t.storage_area_id"
|
|||
|
|
+ " in (select a.storage_area_id from T_UPSTYCTS a "
|
|||
|
|
+ " where t.storage_area_id = a.storage_area_id and t.storage_id = a.storage_id) ";
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取上架策略 select * from T_UPSTYCTS t where t.upstrategy_id=''
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetUpStrategy(string warhousId, string STORAGE_ID)
|
|||
|
|
{
|
|||
|
|
string sqlStr =@" select *
|
|||
|
|
from T_BASE_UPSTRATEGY z
|
|||
|
|
join T_UPSTYCTS s
|
|||
|
|
on z.upstrategy_id = s.upstrategy_id
|
|||
|
|
and s.storage_id ='" + warhousId + "' and s.storage_area_id ='" + STORAGE_ID + "' where UPSTRATEGY_STATE='1'";
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取上架策略的仓库，库区，入库类型，和商品类型的条件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strategyid"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetStrategy(string strategyid)
|
|||
|
|
{
|
|||
|
|
string sqlStr = " select * from T_UPSTYCTS t where t.upstrategy_id='" + strategyid + "'";
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取上架策略
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetUpStrategy()
|
|||
|
|
{
|
|||
|
|
string sqlStr =@"select * from T_BASE_UPSTRATEGY where UPSTRATEGY_STATE='1' ";
|
|||
|
|
// and STORAGE_ID='" + warhousId + "' and STORAGE_AREA_ID='" + STORAGE_ID + "'
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取上架策略信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetUpStrategy(string strUpStrategyName, string strState, string strUpStrategyID)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
#region SQL
|
|||
|
|
strSql.AppendLine("select");
|
|||
|
|
strSql.AppendLine(" UPSTRATEGY_ID,--上架策略编号");
|
|||
|
|
strSql.AppendLine(" UPSTRATEGY_NAME,--上架策略名称");
|
|||
|
|
strSql.AppendLine(" UPSTRATEGY_STATE,--上架策略状态");
|
|||
|
|
strSql.AppendLine(" t_sys_dictionary_tab.vc_dictionary_name STATE_NAME,--上架策略状态");
|
|||
|
|
strSql.AppendLine(" KINDS_FLAG,--种类混放");
|
|||
|
|
strSql.AppendLine(" case when KINDS_FLAG = '0' then '否' when KINDS_FLAG = '1' then '是' end KINDS_NAME,");
|
|||
|
|
strSql.AppendLine(" BATCH_FLAG,--批次混放");
|
|||
|
|
strSql.AppendLine(" case when BATCH_FLAG = '0' then '否' when BATCH_FLAG = '1' then '是' end BATCH_NAME,");
|
|||
|
|
strSql.AppendLine(" VOLUME_FLAG,--是否超出容积");
|
|||
|
|
strSql.AppendLine(" case when VOLUME_FLAG = '0' then '否' when VOLUME_FLAG = '1' then '是' end VOLUME_NAME,");
|
|||
|
|
strSql.AppendLine(" WEIGHT_FLAG,--是否超出重量");
|
|||
|
|
strSql.AppendLine(" case when WEIGHT_FLAG = '0' then '否' when WEIGHT_FLAG = '1' then '是' end WEIGHT_NAME,");
|
|||
|
|
strSql.AppendLine(" LOCATION_FLAG,--是否只放空库位");
|
|||
|
|
strSql.AppendLine(" case when LOCATION_FLAG = '0' then '否' when LOCATION_FLAG = '1' then '是' end LOCATION_NAME,");
|
|||
|
|
strSql.AppendLine(" T_BASE_UPSTRATEGY.REMARK--备注");
|
|||
|
|
strSql.AppendLine(" from");
|
|||
|
|
strSql.AppendLine(" T_BASE_UPSTRATEGY ");
|
|||
|
|
strSql.AppendLine(" left outer join ");
|
|||
|
|
strSql.AppendLine(@" t_sys_dictionary_tab on T_BASE_UPSTRATEGY.UPSTRATEGY_STATE
|
|||
|
|
= t_sys_dictionary_tab.vc_dictionary_id and
|
|||
|
|
t_sys_dictionary_tab.vc_dictionary_type = 'strategy_status'");
|
|||
|
|
|
|||
|
|
strSql.AppendLine("where 1 = 1 ");
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
if (strUpStrategyName != "")
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" and UPSTRATEGY_NAME like '%" + strUpStrategyName + "%' ");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (strState != "")
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" and UPSTRATEGY_STATE = '" + strState + "' ");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (strUpStrategyID != "")
|
|||
|
|
{
|
|||
|
|
strSql.AppendLine(" and UPSTRATEGY_ID = '" + strUpStrategyID + "' ");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 新增上架策略信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略实体</param>
|
|||
|
|
public void AddUpStrategy(UpStrategyModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("insert into T_BASE_UPSTRATEGY(");
|
|||
|
|
strSql.Append(" UPSTRATEGY_ID,UPSTRATEGY_NAME,UPSTRATEGY_STATE,KINDS_FLAG,");
|
|||
|
|
strSql.Append("BATCH_FLAG,VOLUME_FLAG,WEIGHT_FLAG,LOCATION_FLAG,REMARK,CREATE_MAN");
|
|||
|
|
strSql.Append(",BATCHMANGERFLAG,STORAGE_ID,STORAGE_AREA_ID)");
|
|||
|
|
strSql.Append(" values (");
|
|||
|
|
strSql.Append("NEXT VALUE FOR SEQ_UPSTRATEGY_ID,");
|
|||
|
|
strSql.Append("'" + model.UPSTRATEGY_NAME + "',");
|
|||
|
|
strSql.Append("'" + model.UPSTRATEGY_STATE + "',");
|
|||
|
|
strSql.Append("'" + model.KINDS_FLAG + "',");
|
|||
|
|
strSql.Append("'" + model.BATCH_FLAG + "',");
|
|||
|
|
strSql.Append("'" + model.VOLUME_FLAG + "',");
|
|||
|
|
strSql.Append("'" + model.WEIGHT_FLAG + "',");
|
|||
|
|
strSql.Append("'" + model.LOCATION_FLAG + "',");
|
|||
|
|
strSql.Append("'" + model.REMARK + "',");
|
|||
|
|
strSql.Append("'" + model.CREATE_MAN + "',");
|
|||
|
|
strSql.Append("'" + model.BATCHMANGERFLAG + "','" + model.STORAGE_ID + "','" + model.STORAGE_AREA_ID + "'");
|
|||
|
|
strSql.Append(")");
|
|||
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 增加仓库的关联条件
|
|||
|
|
/// </summary>
|
|||
|
|
public void AddStrategyCdtion(UpStrateyCtsModel model)
|
|||
|
|
{
|
|||
|
|
string sqlStr = "insert into T_UPSTYCTS (UPSTRATEGY_ID,STORAGE_ID,STORAGE_AREA_ID,"
|
|||
|
|
+ " Vc_Dictionary_Id,Goods_Type_Id)values ('" + model.UPSTRATEGY_ID + "',"
|
|||
|
|
+ " '" + model.STORAGE_ID + "',"
|
|||
|
|
+ " '" + model.STORAGE_AREA_ID + "','" + model.VC_DICTIONARY_ID + "','" + model.GOODS_TYPE_ID + "')";
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 修改上架策略信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略实体</param>
|
|||
|
|
public void UpdateUpStrategy(UpStrategyModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("update T_BASE_UPSTRATEGY set ");
|
|||
|
|
strSql.Append("UPSTRATEGY_ID='" + model.UPSTRATEGY_ID + "',");
|
|||
|
|
strSql.Append("UPSTRATEGY_NAME='" + model.UPSTRATEGY_NAME + "',");
|
|||
|
|
strSql.Append("UPSTRATEGY_STATE='" + model.UPSTRATEGY_STATE + "',");
|
|||
|
|
strSql.Append("KINDS_FLAG='" + model.KINDS_FLAG + "',");
|
|||
|
|
strSql.Append("BATCH_FLAG='" + model.BATCH_FLAG + "',");
|
|||
|
|
strSql.Append("VOLUME_FLAG='" + model.VOLUME_FLAG + "',");
|
|||
|
|
strSql.Append("WEIGHT_FLAG='" + model.WEIGHT_FLAG + "',");
|
|||
|
|
strSql.Append("LOCATION_FLAG='" + model.LOCATION_FLAG + "',");
|
|||
|
|
strSql.Append("REMARK='" + model.REMARK + "',");
|
|||
|
|
strSql.Append("VERSION='" + model.Version + "',");
|
|||
|
|
|
|||
|
|
strSql.Append("UPDATE_MAN='" + model.UPDATE_MAN + "',");
|
|||
|
|
strSql.Append("UPDATE_DATE=convert(varchar(100),'" + model.CREATE_DATE + "',20)");
|
|||
|
|
strSql.Append(" where UPSTRATEGY_ID='" + model.UPSTRATEGY_ID + "'");
|
|||
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除上架策略信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strUpStrategyID">上架策略编号</param>
|
|||
|
|
public void DeleteUpStrategy(string strUpStrategyID)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("delete from T_BASE_UPSTRATEGY ");
|
|||
|
|
strSql.Append(" where UPSTRATEGY_ID='" + strUpStrategyID + "' ");
|
|||
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除上架关联条件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略条件Model</param>
|
|||
|
|
public void DelUpStrategy(UpStrateyCtsModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("delete from t_upstycts ");
|
|||
|
|
strSql.Append(" where UPSTRATEGY_ID='" + model.UPSTRATEGY_ID + "' ");
|
|||
|
|
//strSql.Append(" where STORAGE_AREA_ID='" + model.STORAGE_AREA_ID + "' ");
|
|||
|
|
//strSql.Append(" where VC_DICTIONARY_ID='" + model.VC_DICTIONARY_ID + "' ");
|
|||
|
|
//strSql.Append(" where GOODS_TYPE_ID='" + model.GOODS_TYPE_ID + "' ");
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 新增上架策略明细条件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略明细实体</param>
|
|||
|
|
public void AddUpStystage(UpStrateyStgModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("insert into T_UPSTYSTAGE(");
|
|||
|
|
strSql.Append("UPSTRATEGY_ID,STORAGE_ID,STORAGE_AREA_ID,LOCATION_BEGIN,LOCATION_END,VERSION)");
|
|||
|
|
strSql.Append(" values (");
|
|||
|
|
strSql.Append("'" + model.UPSTRATEGY_ID + "',");
|
|||
|
|
strSql.Append("'" + model.STORAGE_ID + "',");
|
|||
|
|
strSql.Append("'" + model.STORAGE_AREA_ID + "',");
|
|||
|
|
strSql.Append("'" + model.LOCATION_BEGIN + "',");
|
|||
|
|
strSql.Append("'" + model.LOCATION_END + "',");
|
|||
|
|
strSql.Append("'" + model.VERSION + "'");
|
|||
|
|
strSql.Append(" )");
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除上架策略明细条件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略明细实体</param>
|
|||
|
|
public void DelUpStystage(UpStrateyStgModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("delete from T_UPSTYSTAGE");
|
|||
|
|
strSql.Append(" where UPSTRATEGY_ID='" + model.UPSTRATEGY_ID + "'");
|
|||
|
|
strSql.Append(" and STORAGE_ID = '" + model.STORAGE_ID + "' and STORAGE_AREA_ID = '" + model.STORAGE_AREA_ID + "'");
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取上架策略明细条件
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model">上架策略明细实体</param>
|
|||
|
|
public DataTable GetUpStystage(UpStrateyStgModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append("select * from T_UPSTYSTAGE");
|
|||
|
|
strSql.Append(" where 1=1");
|
|||
|
|
if (!string.IsNullOrEmpty(model.UPSTRATEGY_ID))
|
|||
|
|
{
|
|||
|
|
strSql.Append(" and UPSTRATEGY_ID = '" + model.UPSTRATEGY_ID + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(model.STORAGE_AREA_ID))
|
|||
|
|
{
|
|||
|
|
strSql.Append(" and STORAGE_AREA_ID = '" + model.STORAGE_AREA_ID + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(model.STORAGE_ID))
|
|||
|
|
{
|
|||
|
|
strSql.Append(" and STORAGE_ID = '" + model.STORAGE_ID + "'");
|
|||
|
|
}
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|