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