using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.Model.Stock;

using WMS.DBUtility;
using WMS.IData.IStock;
using WMS.IData;using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.StockData
{
    public class AreaRepData : IAreaRep
    {
        /// <summary>
        /// 获取库区间调拨单
        /// </summary>
        /// <returns>库区间调拨单</returns>
        public DataTable GetAreaRepDT(AreaRepModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select 			    ");
            strSql.AppendLine("	a.AREA_REP_ID,      ");
            strSql.AppendLine("	a.TOTAL_PLAN_NUM,   ");
            strSql.AppendLine("	a.REAL_PLAN_NUM,    ");
            strSql.AppendLine("	a.CREATE_PERSON,    ");
            strSql.AppendLine(" c.name CREATE_PERSON_name,  ");
            strSql.AppendLine("	a.CONFIRM_PERSON,   ");
            strSql.AppendLine(" d.name confirm_person_name, ");
            strSql.AppendLine("	a.OUT_PERSON,       ");
            strSql.AppendLine(" e.name OUT_person_name,     ");
            strSql.AppendLine("	a.IN_PERSON,        ");
            strSql.AppendLine(" f.name IN_person_name,      ");
            strSql.AppendLine("	a.CREATE_TIME,      ");
            strSql.AppendLine("	a.CONFIRM_TIME,     ");
            strSql.AppendLine("	a.OUT_TIME,         ");
            strSql.AppendLine("	a.IN_TIME,          ");
            strSql.AppendLine("	a.STATE,            ");
            strSql.AppendLine("	a.REASON,           ");
            strSql.AppendLine("	a.REMARK,           ");
            strSql.AppendLine("	a.STORAGE_ID,       ");
            strSql.AppendLine("	a.OUT_AREA_ID,      ");
            strSql.AppendLine("	a.IN_AREA_ID,       ");
            strSql.AppendLine("	i.STORAGE_NAME,     ");
            strSql.AppendLine("	a.PRINT_COUNT,    --打印次数                        ");
            strSql.AppendLine("	g.STORAGE_AREA_NAME as OUT_AREA_NAME,");
            strSql.AppendLine("	h.STORAGE_AREA_NAME as IN_AREA_NAME,");
            strSql.AppendLine("	a.create_method     ");
            strSql.AppendLine("FROM                 ");
            if (!string.IsNullOrEmpty(model.STATE) && int.Parse(model.STATE) >= 2)//如果已完成、关闭 去备份表查
            {
                strSql.AppendLine(" T_STOCK_AREA_REP_BACK a ");
            }
            else
            {
                strSql.AppendLine("	T_STOCK_AREA_REP a      ");
            }
            strSql.AppendLine("     left join t_base_userinfo c                                                        ");
            strSql.AppendLine("     on a.CREATE_PERSON = c.user_id                                                     ");
            strSql.AppendLine("     left join t_base_userinfo d                                                        ");
            strSql.AppendLine("     on a.CONFIRM_PERSON = d.user_id                                                    ");
            strSql.AppendLine("     left join t_base_userinfo e                                                        ");
            strSql.AppendLine("     on a.OUT_PERSON = e.user_id                                                        ");
            strSql.AppendLine("     left join t_base_userinfo f                                                        ");
            strSql.AppendLine("     on a.IN_PERSON = f.user_id                                                         ");


            strSql.AppendLine("     left join T_BASE_STORAGEINFO i                                                     ");
            strSql.AppendLine("     on a.STORAGE_ID = i.STORAGE_ID                                                     ");
            strSql.AppendLine("     left join t_base_storage_area g                                                    ");
            strSql.AppendLine("     on a.OUT_AREA_ID = g.STORAGE_AREA_ID                                               ");
            strSql.AppendLine("     left join t_base_storage_area h                                                    ");
            strSql.AppendLine("     on a.IN_AREA_ID = h.STORAGE_AREA_ID                                                ");


            strSql.AppendLine("where 1 = 1 ");
            if (!string.IsNullOrEmpty(model.STATE))
            {
                strSql.AppendLine(" and a.STATE = '" + model.STATE + "' ");
            }
            if (!string.IsNullOrEmpty(model.STORAGE_ID))
            {
                strSql.AppendLine(" and a.STORAGE_ID = '" + model.STORAGE_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.OUT_AREA_ID))
            {
                strSql.AppendLine(" and a.OUT_AREA_ID = '" + model.OUT_AREA_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.IN_AREA_ID))
            {
                strSql.AppendLine(" and a.IN_AREA_ID = '" + model.IN_AREA_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.AREA_REP_ID))
            {
                strSql.AppendLine(" and a.AREA_REP_ID like '%" + model.AREA_REP_ID + "%' ");
            }
            if (!string.IsNullOrEmpty(model.CREATE_PERSON))
            {
                strSql.AppendLine(" and a.CREATE_PERSON = '" + model.CREATE_PERSON + "' ");
            }
            if (!string.IsNullOrEmpty(model.CONFIRM_PERSON))
            {
                strSql.AppendLine(" and a.CONFIRM_PERSON = '" + model.CONFIRM_PERSON + "' ");
            }
            if (!string.IsNullOrEmpty(model.IN_PERSON))
            {
                strSql.AppendLine(" and a.IN_PERSON = '" + model.IN_PERSON + "' ");
            }
            if (!string.IsNullOrEmpty(model.OUT_PERSON))
            {
                strSql.AppendLine(" and a.OUT_PERSON = '" + model.OUT_PERSON + "' ");
            }
            if (!string.IsNullOrEmpty(model.REASON))
            {
                strSql.AppendLine(" and a.REASON = '" + model.REASON + "' ");
            }

            if (!string.IsNullOrEmpty(model.CREATE_METHOD))
            {
                strSql.AppendLine(" and a.CREATE_METHOD = '" + model.CREATE_METHOD + "' ");
            }
            if (!string.IsNullOrEmpty(model.REMARK))
            {
                strSql.AppendLine(" and a.REMARK like '%" + model.REMARK + "%' ");
            }
            if (model.CONFIRM_TIME.Year != 1)
            {
                string date = string.Empty;
                date += model.CONFIRM_TIME.Year + "/";
                if (model.CONFIRM_TIME.Month < 10)
                {
                    date += '0';
                }
                date += model.CONFIRM_TIME.Month + "/";
                if (model.CONFIRM_TIME.Day < 10)
                {
                    date += '0';
                }
                date += model.CONFIRM_TIME.Day;

                strSql.AppendLine(" and convert(varchar(100),a.CONFIRM_TIME,23)  = '" + date + "'");
            }
            if (model.CREATE_TIME.Year != 1)
            {
                string date = string.Empty;
                date += model.CREATE_TIME.Year + "/";
                if (model.CREATE_TIME.Month < 10)
                {
                    date += '0';
                }
                date += model.CREATE_TIME.Month + "/";
                if (model.CREATE_TIME.Day < 10)
                {
                    date += '0';
                }
                date += model.CREATE_TIME.Day;

                strSql.AppendLine(" and convert(varchar(100),a.CREATE_TIME,23) = '" + date + "'");
            }
            if (model.OUT_TIME.Year != 1)
            {
                string date = string.Empty;
                date += model.OUT_TIME.Year + "/";
                if (model.OUT_TIME.Month < 10)
                {
                    date += '0';
                }
                date += model.OUT_TIME.Month + "/";
                if (model.OUT_TIME.Day < 10)
                {
                    date += '0';
                }
                date += model.OUT_TIME.Day;

                strSql.AppendLine(" and convert(varchar(100),a.OUT_TIME,23) = '" + date + "'");
            }
            if (model.IN_TIME.Year != 1)
            {
                string date = string.Empty;
                date += model.IN_TIME.Year + "/";
                if (model.IN_TIME.Month < 10)
                {
                    date += '0';
                }
                date += model.IN_TIME.Month + "/";
                if (model.IN_TIME.Day < 10)
                {
                    date += '0';
                }
                date += model.IN_TIME.Day;

                strSql.AppendLine(" and  convert(varchar(100),a.IN_TIME,23) = '" + date + "'");
            }

            strSql.AppendLine(" order by a.STATE,a.AREA_REP_ID desc");

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

        /// <summary>
        /// 插入库区间调拨单信息
        /// </summary>
        /// <param name="AreaRepModel">库区间调拨单Model</param>
        /// <returns>库区间调拨单编号</returns>
        public string InsertAreaRep(AreaRepModel model)
        {
          
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("insert into                                                                    					");
            strSql.AppendLine("	T_STOCK_AREA_REP                                                                                ");
            strSql.AppendLine("(                                                                                                ");
            strSql.AppendLine("	AREA_REP_ID,                                                                                    ");
            strSql.AppendLine("	TOTAL_PLAN_NUM,                                                                                 ");
            strSql.AppendLine("	REAL_PLAN_NUM,                                                                                  ");
            strSql.AppendLine("	CREATE_PERSON,                                                                                  ");
            strSql.AppendLine("	CONFIRM_PERSON,                                                                                 ");
            strSql.AppendLine("	OUT_PERSON,                                                                                     ");
            strSql.AppendLine("	IN_PERSON,                                                                                      ");
            strSql.AppendLine("	CREATE_TIME,                                                                                    ");
            strSql.AppendLine("	CONFIRM_TIME,                                                                                   ");
            strSql.AppendLine("	OUT_TIME,                                                                                       ");
            strSql.AppendLine("	IN_TIME,                                                                                        ");
            strSql.AppendLine("	STATE,                                                                                          ");
            strSql.AppendLine("	REASON,                                                                                         ");
            strSql.AppendLine("	REMARK,                                                                                         ");
            strSql.AppendLine("	STORAGE_ID,                                                                                     ");
            strSql.AppendLine("	OUT_AREA_ID,                                                                                    ");
            strSql.AppendLine("	IN_AREA_ID,                                                                                     ");
            strSql.AppendLine("	CREATE_METHOD,                                                                                  ");
            strSql.AppendLine("	PRINT_COUNT                                                                                     ");
            strSql.AppendLine(")                                                                                                ");
            strSql.AppendLine("values                                                                                           ");
            strSql.AppendLine("(                                                                                                ");
            strSql.AppendLine("	'" + model.AREA_REP_ID + "',                                                                    ");
            strSql.AppendLine("	'" + model.TOTAL_PLAN_NUM + "',                                                                 ");
            strSql.AppendLine("	'" + model.REAL_PLAN_NUM + "',                                                                  ");
            strSql.AppendLine("	'" + model.CREATE_PERSON + "',                                                                  ");
            strSql.AppendLine("	'" + model.CONFIRM_PERSON + "',                                                                 ");
            strSql.AppendLine("	'" + model.OUT_PERSON + "',                                                                     ");
            strSql.AppendLine("	'" + model.IN_PERSON + "',                                                                      ");
            strSql.AppendLine("	convert(varchar(100),'" + System.DateTime.Now.ToString() + "',20), ");
            strSql.AppendLine("	convert(varchar(100),'" + model.CONFIRM_TIME.ToString("yyyy-MM-dd HH:mm:ss") + "',20),  ");
            strSql.AppendLine("	convert(varchar(100),'" + model.OUT_TIME.ToString("yyyy-MM-dd HH:mm:ss") + "',20),      ");
            strSql.AppendLine("	convert(varchar(100),'" + model.IN_TIME.ToString("yyyy-MM-dd HH:mm:ss") + "',20),       ");
            strSql.AppendLine("	'" + model.STATE + "',                                                                          ");
            strSql.AppendLine("	'" + model.REASON + "',                                                                         ");
            strSql.AppendLine("	'" + model.REMARK + "',                                                                         ");
            strSql.AppendLine("	'" + model.STORAGE_ID + "',                                                                     ");
            strSql.AppendLine("	'" + model.OUT_AREA_ID + "',                                                                    ");
            strSql.AppendLine("	'" + model.IN_AREA_ID + "',                                                                     ");
            strSql.AppendLine("	'" + model.CREATE_METHOD + "',                                                                  ");
            strSql.AppendLine("	0                                                                                               ");
            strSql.AppendLine(")                                                                                                ");

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

            //strSql.AppendLine("insert into T_STOCK_AREA_REP(");
            //strSql.AppendLine("AREA_REP_ID,TOTAL_PLAN_NUM,REAL_PLAN_NUM,CREATE_PERSON,CONFIRM_PERSON,OUT_PERSON,IN_PERSON,CREATE_TIME,CONFIRM_TIME,OUT_TIME,IN_TIME,STATE,REASON,REMARK,STORAGE_ID,OUT_AREA_ID,IN_AREA_ID,CREATE_METHOD,PRINT_COUNT)");
            //strSql.AppendLine(" values (");
            //strSql.AppendLine(":AREA_REP_ID,:TOTAL_PLAN_NUM,:REAL_PLAN_NUM,:CREATE_PERSON,:CONFIRM_PERSON,:OUT_PERSON,:IN_PERSON,to_date('" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',20),:CONFIRM_TIME,:OUT_TIME,:IN_TIME,:STATE,:REASON,:REMARK,:STORAGE_ID,:OUT_AREA_ID,:IN_AREA_ID,:CREATE_METHOD,0)");
            //OracleParameter[] parameters = {
            //       new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":TOTAL_PLAN_NUM", OracleDbType.Decimal),
            //       new OracleParameter(":REAL_PLAN_NUM", OracleDbType.Decimal),
            //       new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
            //       new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
            //       new OracleParameter(":OUT_PERSON",OracleDbType.Varchar2),
            //       new OracleParameter(":IN_PERSON",OracleDbType.Varchar2),
            //       new OracleParameter(":CONFIRM_TIME", OracleDbType.Date),
            //       new OracleParameter(":OUT_TIME", OracleDbType.Date),
            //       new OracleParameter(":IN_TIME", OracleDbType.Date),
            //       new OracleParameter(":STATE", OracleDbType.Char),
            //       new OracleParameter(":REASON", OracleDbType.Char),
            //       new OracleParameter(":REMARK",OracleDbType.Varchar2),
            //       new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":OUT_AREA_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":IN_AREA_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":CREATE_METHOD", OracleDbType.Char)};

            //parameters[0].Value = model.AREA_REP_ID;
            //parameters[1].Value = model.TOTAL_PLAN_NUM;
            //parameters[2].Value = model.REAL_PLAN_NUM;
            //parameters[3].Value = model.CREATE_PERSON;
            //parameters[4].Value = model.CONFIRM_PERSON;
            //parameters[5].Value = model.OUT_PERSON;
            //parameters[6].Value = model.IN_PERSON;
            //parameters[7].Value = model.CONFIRM_TIME;
            //parameters[8].Value = model.OUT_TIME;
            //parameters[9].Value = model.IN_TIME;
            //parameters[10].Value = model.STATE;
            //parameters[11].Value = model.REASON;
            //parameters[12].Value = model.REMARK;
            //parameters[13].Value = model.STORAGE_ID;
            //parameters[14].Value = model.OUT_AREA_ID;
            //parameters[15].Value = model.IN_AREA_ID;
            //parameters[16].Value = model.CREATE_METHOD;

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

            return model.AREA_REP_ID;
        }

        /// <summary>
        /// 修改库区间调拨单信息
        /// </summary>
        /// <param name="AreaRepModel">库区间调拨单Model</param>
        public void UpdateAreaRep(AreaRepModel model) {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("update T_STOCK_AREA_REP set ");
            strSql.AppendLine("AREA_REP_ID=:AREA_REP_ID,");
            strSql.AppendLine("TOTAL_PLAN_NUM=:TOTAL_PLAN_NUM,");
            strSql.AppendLine("REAL_PLAN_NUM=:REAL_PLAN_NUM,");
            strSql.AppendLine("CREATE_PERSON=:CREATE_PERSON,");
            strSql.AppendLine("CONFIRM_PERSON=:CONFIRM_PERSON,");
            strSql.AppendLine("OUT_PERSON=:OUT_PERSON,");
            strSql.AppendLine("IN_PERSON=:IN_PERSON,");
            strSql.AppendLine("CREATE_TIME=:CREATE_TIME,");
            strSql.AppendLine("CONFIRM_TIME=:CONFIRM_TIME,");
            strSql.AppendLine("OUT_TIME=:OUT_TIME,");
            strSql.AppendLine("IN_TIME=:IN_TIME,");
            strSql.AppendLine("STATE=:STATE,");
            strSql.AppendLine("REASON=:REASON,");
            strSql.AppendLine("REMARK=:REMARK,");
            strSql.AppendLine("STORAGE_ID=:STORAGE_ID,");
            strSql.AppendLine("OUT_AREA_ID=:OUT_AREA_ID,");
            strSql.AppendLine("IN_AREA_ID=:IN_AREA_ID,");
            strSql.AppendLine("PRINT_COUNT=:PRINT_COUNT,");
            strSql.AppendLine("CREATE_METHOD=:CREATE_METHOD");
            strSql.AppendLine(" where AREA_REP_ID=:AREA_REP_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2),
					new OracleParameter(":TOTAL_PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":REAL_PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":OUT_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":IN_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_TIME", OracleDbType.Date),
					new OracleParameter(":CONFIRM_TIME", OracleDbType.Date),
					new OracleParameter(":OUT_TIME", OracleDbType.Date),
					new OracleParameter(":IN_TIME", OracleDbType.Date),
					new OracleParameter(":STATE", OracleDbType.Char),
					new OracleParameter(":REASON", OracleDbType.Char),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
					new OracleParameter(":OUT_AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":IN_AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":PRINT_COUNT", OracleDbType.Decimal),
					new OracleParameter(":CREATE_METHOD", OracleDbType.Char)};
            parameters[0].Value = model.AREA_REP_ID;
            parameters[1].Value = model.TOTAL_PLAN_NUM;
            parameters[2].Value = model.REAL_PLAN_NUM;
            parameters[3].Value = model.CREATE_PERSON;
            parameters[4].Value = model.CONFIRM_PERSON;
            parameters[5].Value = model.OUT_PERSON;
            parameters[6].Value = model.IN_PERSON;
            parameters[7].Value = model.CREATE_TIME;
            parameters[8].Value = model.CONFIRM_TIME;
            parameters[9].Value = model.OUT_TIME;
            parameters[10].Value = model.IN_TIME;
            parameters[11].Value = model.STATE;
            parameters[12].Value = model.REASON;
            parameters[13].Value = model.REMARK;
            parameters[14].Value = model.STORAGE_ID;
            parameters[15].Value = model.OUT_AREA_ID;
            parameters[16].Value = model.IN_AREA_ID;
            parameters[17].Value = model.PRINT_COUNT;
            parameters[18].Value = model.CREATE_METHOD;


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

        /// <summary>
        /// 修改库区间调拨单状态
        /// </summary>
        /// <param name="AreaRepModel">库区间调拨单Model</param>
        public void UpdateAreaRepState(AreaRepModel model)
        {
            StringBuilder strSql = new StringBuilder();

            if (model.STATE == "1") {
                strSql.AppendLine("update T_STOCK_AREA_REP set ");
                strSql.AppendLine("CONFIRM_PERSON=:CONFIRM_PERSON,");
                strSql.AppendLine("CONFIRM_TIME=getdate(),");
                strSql.AppendLine("STATE=:STATE");
                strSql.AppendLine(" where AREA_REP_ID=:AREA_REP_ID ");
                OracleParameter[] parameters = {
					new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":STATE", OracleDbType.Char),
                   new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2)};
                
                parameters[0].Value = model.CONFIRM_PERSON;
                parameters[1].Value = model.STATE;
                parameters[2].Value = model.AREA_REP_ID;
                SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

            }
            else if(model.STATE == "3")//关闭
            {
                strSql.AppendLine("update T_STOCK_AREA_REP set ");
                strSql.AppendLine("STATE=:STATE");
                strSql.AppendLine(" where AREA_REP_ID=:AREA_REP_ID ");
                OracleParameter[] parameters = {
					new OracleParameter(":STATE", OracleDbType.Char),
                   new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2)};

                parameters[0].Value = model.STATE;
                parameters[1].Value = model.AREA_REP_ID;
                SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
            }


        }

        /// <summary>
        /// 删除库区间调拨单信息
        /// </summary>
        /// <param name="AreaRep"></param>
        public void DeleteAreaRep(string ID)
        {
            StringBuilder strSql = new StringBuilder(); 
            strSql.AppendLine("delete from T_STOCK_AREA_REP ");
            strSql.AppendLine(" where AREA_REP_ID=:AREA_REP_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = ID;


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

        /// <summary>
        /// 向备份表中添加库区间调拨单
        /// </summary>
        /// <param name="AreaRepID">库区间调拨单编号</param>
        public void InsertAreaRepBack(string AreaRepID)
        {
            string strSql = "insert into T_STOCK_AREA_REP_BACK select * from T_STOCK_AREA_REP where AREA_REP_ID =:AREA_REP_ID";
            OracleParameter[] parameters = {
				new OracleParameter(":AREA_REP_ID",OracleDbType.Varchar2)};
            parameters[0].Value = AreaRepID;

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

        }
    }
}