using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using WMS.DBUtility;
using WMS.IData.ICK;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Model.CK;
using WMS.Model.Strategy;
using WMS.IData.IStrategy;

namespace WMS.SqlServerData.StrategyData
{
    public class DownStrategyData : IDownStrategy
    {
        /// <summary>
        /// 获取下架策略
        /// </summary>
        /// <returns>下架策略</returns>
        public DataTable GetDownStrategyDT(DownStrategyModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select                 		");
            strSql.AppendLine("	a.DOWNSTRATEGY_ID,          ");
            strSql.AppendLine("	a.DOWNSTRATEGY_NAME,        ");
            strSql.AppendLine("	a.DOWNSTRATEGY_STATE,       ");
            strSql.AppendLine("	a.REMARK,                   ");
            strSql.AppendLine("	a.VERSION,                  ");
            strSql.AppendLine("	a.CREATE_MAN,               ");
            strSql.AppendLine("	b.name CREATE_MAN_NAME,     ");
            strSql.AppendLine("	a.CREATE_DATE,              ");
            strSql.AppendLine("	a.UPDATE_MAN,               ");
            strSql.AppendLine("	c.name UPDATE_MAN_NAME,     ");
            strSql.AppendLine("	a.UPDATE_DATE,              ");
            strSql.AppendLine("	a.PRODUCE_FLAG,             ");
            strSql.AppendLine("	a.ADDED_FLAG,               ");
            strSql.AppendLine("	a.NULL_FLAG                 ");
            strSql.AppendLine("FROM                         ");
            strSql.AppendLine("	T_BASE_DOWNSTRATEGY a       ");
            strSql.AppendLine("	left join t_base_userinfo b ");
            strSql.AppendLine("	on a.CREATE_MAN = b.user_id ");
            strSql.AppendLine("	left join t_base_userinfo c ");
            strSql.AppendLine("	on a.UPDATE_MAN = c.user_id ");

            strSql.AppendLine("where 1 = 1");
            //if (!string.IsNullOrEmpty(model.DOWNSTRATEGY_ID))
            //{
            //    strSql.AppendLine(" and a.DOWNSTRATEGY_ID = '" + model.DOWNSTRATEGY_ID + "' ");
            //}
            strSql.AppendLine(" order by a.DOWNSTRATEGY_ID desc");

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 获取某仓库的下架策略
        /// </summary>
        /// <returns>仓库编号</returns>
        public DataTable GetDownStrategyDTByStorage(string storageID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select * from t_downstycts a inner join t_base_downstrategy b on a.downstrategy_id = b.downstrategy_id where storage_id = '" + storageID + "' and DOWNSTRATEGY_STATE = '0'");

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 插入下架策略信息
        /// </summary>
        /// <param name="DownStrategyModel">下架策略Model</param>
        /// <returns>下架策略编号</returns>
        public string InsertDownStrategy(DownStrategyModel model)
        {
            string str = "select NEXT VALUE FOR SEQ_DOWNSTRATEGY_ID";
            DataSet ds =  SystemDataObject.Instance.GetDataSet( str);
            model.DOWNSTRATEGY_ID = "XJCL" + ds.Tables[0].Rows[0][0].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("insert into T_BASE_DOWNSTRATEGY(");
            strSql.AppendLine("DOWNSTRATEGY_ID,DOWNSTRATEGY_NAME,DOWNSTRATEGY_STATE,REMARK,VERSION,CREATE_MAN,CREATE_DATE,UPDATE_MAN,UPDATE_DATE,PRODUCE_FLAG,ADDED_FLAG,NULL_FLAG)");
            strSql.AppendLine(" values (");
            strSql.AppendLine(":DOWNSTRATEGY_ID,:DOWNSTRATEGY_NAME,:DOWNSTRATEGY_STATE,:REMARK,:VERSION,:CREATE_MAN,getdate(),:UPDATE_MAN,:UPDATE_DATE,:PRODUCE_FLAG,:ADDED_FLAG,:NULL_FLAG)");
            OracleParameter[] parameters = {
					new OracleParameter(":DOWNSTRATEGY_ID",OracleDbType.Varchar2,50),
					new OracleParameter(":DOWNSTRATEGY_NAME",OracleDbType.Varchar2,50),
					new OracleParameter(":DOWNSTRATEGY_STATE",OracleDbType.Varchar2,50),
					new OracleParameter(":REMARK",OracleDbType.Varchar2,300),
					new OracleParameter(":VERSION",OracleDbType.Varchar2,50),
					new OracleParameter(":CREATE_MAN",OracleDbType.Varchar2,50),
                    //new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":UPDATE_MAN",OracleDbType.Varchar2,50),
					new OracleParameter(":UPDATE_DATE", OracleDbType.Date),
					new OracleParameter(":PRODUCE_FLAG", OracleDbType.Char,1),
					new OracleParameter(":ADDED_FLAG", OracleDbType.Char,1),
					new OracleParameter(":NULL_FLAG", OracleDbType.Char,1)};
            parameters[0].Value = model.DOWNSTRATEGY_ID;
            parameters[1].Value = model.DOWNSTRATEGY_NAME;
            parameters[2].Value = model.DOWNSTRATEGY_STATE;
            parameters[3].Value = model.REMARK;
            parameters[4].Value = model.VERSION;
            parameters[5].Value = model.CREATE_MAN;
            parameters[6].Value = model.UPDATE_MAN;
            parameters[7].Value = model.UPDATE_DATE;
            parameters[8].Value = model.PRODUCE_FLAG;
            parameters[9].Value = model.ADDED_FLAG;
            parameters[10].Value = model.NULL_FLAG;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

            return model.DOWNSTRATEGY_ID;
        }

        /// <summary>
        /// 修改下架策略信息
        /// </summary>
        /// <param name="DownStrategyModel">下架策略Model</param>
        public void UpdateDownStrategy(DownStrategyModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("update T_BASE_DOWNSTRATEGY set ");
            strSql.AppendLine("DOWNSTRATEGY_ID=:DOWNSTRATEGY_ID,");
            strSql.AppendLine("DOWNSTRATEGY_NAME=:DOWNSTRATEGY_NAME,");
            strSql.AppendLine("DOWNSTRATEGY_STATE=:DOWNSTRATEGY_STATE,");
            strSql.AppendLine("REMARK=:REMARK,");
            strSql.AppendLine("VERSION=:VERSION,");
            strSql.AppendLine("CREATE_MAN=:CREATE_MAN,");
            strSql.AppendLine("CREATE_DATE=:CREATE_DATE,");
            strSql.AppendLine("UPDATE_MAN=:UPDATE_MAN,");
            strSql.AppendLine("UPDATE_DATE=getdate(),");
            strSql.AppendLine("PRODUCE_FLAG=:PRODUCE_FLAG,");
            strSql.AppendLine("ADDED_FLAG=:ADDED_FLAG,");
            strSql.AppendLine("NULL_FLAG=:NULL_FLAG");
            strSql.AppendLine(" where DOWNSTRATEGY_ID=:DOWNSTRATEGY_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":DOWNSTRATEGY_ID",OracleDbType.Varchar2,50),
					new OracleParameter(":DOWNSTRATEGY_NAME",OracleDbType.Varchar2,50),
					new OracleParameter(":DOWNSTRATEGY_STATE",OracleDbType.Varchar2,50),
					new OracleParameter(":REMARK",OracleDbType.Varchar2,300),
					new OracleParameter(":VERSION",OracleDbType.Varchar2,50),
					new OracleParameter(":CREATE_MAN",OracleDbType.Varchar2,50),
					new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":UPDATE_MAN",OracleDbType.Varchar2,50),
                    //new OracleParameter(":UPDATE_DATE", OracleDbType.Date),
					new OracleParameter(":PRODUCE_FLAG", OracleDbType.Char,1),
					new OracleParameter(":ADDED_FLAG", OracleDbType.Char,1),
					new OracleParameter(":NULL_FLAG", OracleDbType.Char,1)};
            parameters[0].Value = model.DOWNSTRATEGY_ID;
            parameters[1].Value = model.DOWNSTRATEGY_NAME;
            parameters[2].Value = model.DOWNSTRATEGY_STATE;
            parameters[3].Value = model.REMARK;
            parameters[4].Value = model.VERSION;
            parameters[5].Value = model.CREATE_MAN;
            parameters[6].Value = model.CREATE_DATE;
            parameters[7].Value = model.UPDATE_MAN;
            parameters[8].Value = model.PRODUCE_FLAG;
            parameters[9].Value = model.ADDED_FLAG;
            parameters[10].Value = model.NULL_FLAG;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一个下架策略
        /// </summary>
        /// <param name="DOWNSTRATEGY_ID">下架策略ID</param>
        public void DeleteDownStrategy(string DOWNSTRATEGY_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_BASE_DOWNSTRATEGY ");
            strSql.AppendLine(" where DOWNSTRATEGY_ID=:DOWNSTRATEGY_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":DOWNSTRATEGY_ID",OracleDbType.Varchar2,50)			};
            parameters[0].Value = DOWNSTRATEGY_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

    }
}