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 MoveLocData : IMoveLoc
    {
        /// <summary>
        /// 获取移位单
        /// </summary>
        /// <returns>移位单</returns>
        public DataTable GetMoveLocDT(MoveLocModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select 				                        		");
            strSql.AppendLine("	a.MOVELOC_ID,	  --移位单编号                      ");
            strSql.AppendLine("	a.MOVELOC_TYPE,   --移位类型                        ");
            strSql.AppendLine("	a.TOTAL_PLAN_NUM, --计划移位总数                    ");
            strSql.AppendLine("	a.TOTAL_REAL_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.MOVE_PERSON,    --移位人                          ");
            strSql.AppendLine(" e.name MOVE_PERSON_name,                            ");
            strSql.AppendLine("	a.CREATE_TIME,    --创建时间                        ");
            strSql.AppendLine("	a.CONFIRM_TIME,   --审核时间                        ");
            strSql.AppendLine("	a.MOVE_TIME,      --移位时间                        ");
            strSql.AppendLine("	a.REASON,         --移位原因                        ");
            strSql.AppendLine("	a.REMARK,         --备注                            ");
            strSql.AppendLine("	a.STATE,          --Status(未审核、已审核、移位完成)  ");
            strSql.AppendLine("	a.AREA_ID,        --库区编号                        ");
            strSql.AppendLine("	a.PRINT_COUNT,    --打印次数                        ");
            strSql.AppendLine("	f.STORAGE_NAME,         --                          ");
            strSql.AppendLine("	g.STORAGE_AREA_NAME as AREA_NAME,    --             ");
            strSql.AppendLine("	a.STORAGE_ID      --仓库编号                        ");
            strSql.AppendLine("FROM                                                 ");
            if (!string.IsNullOrEmpty(model.STATE) && int.Parse(model.STATE) >= 2)//如果已完成、关闭 去备份表查
            {
                strSql.AppendLine(" T_STOCK_MOVELOC_BACK a                          ");
            }
            else
            {
                strSql.AppendLine("	T_STOCK_MOVELOC 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.MOVE_PERSON = e.user_id                                                       ");

            strSql.AppendLine("     left join T_BASE_STORAGEINFO f                                                     ");
            strSql.AppendLine("     on a.STORAGE_ID = f.STORAGE_ID                                                     ");
            strSql.AppendLine("     left join t_base_storage_area g                                                    ");
            strSql.AppendLine("     on a.AREA_ID = g.STORAGE_AREA_ID                                                   ");

            strSql.AppendLine("where 1 = 1                                          ");
            if (!string.IsNullOrEmpty(model.MOVELOC_ID))
            {
                strSql.AppendLine(" and a.MOVELOC_ID like '%" + model.MOVELOC_ID + "%' ");
            }
            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.AREA_ID))
            {
                strSql.AppendLine(" and a.AREA_ID = '" + model.AREA_ID + "' ");
            }
            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.MOVE_TIME.Year != 1)
            {
                string date = string.Empty;
                date += model.MOVE_TIME.Year + "/";
                if (model.MOVE_TIME.Month < 10)
                {
                    date += '0';
                }
                date += model.MOVE_TIME.Month + "/";
                if (model.MOVE_TIME.Day < 10)
                {
                    date += '0';
                }
                date += model.MOVE_TIME.Day;

                strSql.AppendLine(" and  convert(varchar(100),a.MOVE_TIME,23)   = '" + date + "'");
            }
            if (!string.IsNullOrEmpty(model.MOVELOC_TYPE))
            {
                strSql.AppendLine(" and a.MOVELOC_TYPE = '" + model.MOVELOC_TYPE + "' ");
            }
            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.MOVE_PERSON))
            {
                strSql.AppendLine(" and a.MOVE_PERSON = '" + model.MOVE_PERSON + "' ");
            }
            if (!string.IsNullOrEmpty(model.REASON))
            {
                strSql.AppendLine(" and a.REASON like '%" + model.REASON + "%' ");
            }
            if (!string.IsNullOrEmpty(model.REMARK))
            {
                strSql.AppendLine(" and a.REMARK like '%" + model.REMARK + "%' ");
            }

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

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

        /// <summary>
        /// 插入移位单信息
        /// </summary>
        /// <param name="MoveLocModel">移位单Model</param>
        /// <returns>移位单编号</returns>
        public string InsertMoveLoc(MoveLocModel model)
        {
            //string str = "select to_char(getdate(),'yyyyMMdd')||SEQ_STOCK_MOVELOC.nextval from dual ";
            //DataSet ds =  SystemDataObject.Instance.GetDataSet( str);
            //model.MOVELOC_ID = "YW" + ds.Tables[0].Rows[0][0].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("insert into T_STOCK_MOVELOC(");
            strSql.AppendLine("MOVELOC_ID,MOVELOC_TYPE,TOTAL_PLAN_NUM,TOTAL_REAL_NUM,CREATE_PERSON,CONFIRM_PERSON,MOVE_PERSON,CREATE_TIME,CONFIRM_TIME,MOVE_TIME,REASON,REMARK,STATE,AREA_ID,STORAGE_ID,PRINT_COUNT)");
			strSql.AppendLine(" values (");
            strSql.AppendLine(":MOVELOC_ID,:MOVELOC_TYPE,:TOTAL_PLAN_NUM,:TOTAL_REAL_NUM,:CREATE_PERSON,:CONFIRM_PERSON,:MOVE_PERSON,:CREATE_TIME,:CONFIRM_TIME,:MOVE_TIME,:REASON,:REMARK,:STATE,:AREA_ID,:STORAGE_ID,0)");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":MOVELOC_TYPE", OracleDbType.Char),
					new OracleParameter(":TOTAL_PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":TOTAL_REAL_NUM", OracleDbType.Decimal),
					new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":MOVE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_TIME", OracleDbType.Date),
					new OracleParameter(":CONFIRM_TIME", OracleDbType.Date),
					new OracleParameter(":MOVE_TIME", OracleDbType.Date),
					new OracleParameter(":REASON",OracleDbType.Varchar2),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":STATE", OracleDbType.Char),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)};
            parameters[0].Value = model.MOVELOC_ID;
			parameters[1].Value = model.MOVELOC_TYPE;
			parameters[2].Value = model.TOTAL_PLAN_NUM;
			parameters[3].Value = model.TOTAL_REAL_NUM;
			parameters[4].Value = model.CREATE_PERSON;
			parameters[5].Value = model.CONFIRM_PERSON;
			parameters[6].Value = model.MOVE_PERSON;
			parameters[7].Value = model.CREATE_TIME;
			parameters[8].Value = model.CONFIRM_TIME;
			parameters[9].Value = model.MOVE_TIME;
			parameters[10].Value = model.REASON;
			parameters[11].Value = model.REMARK;
			parameters[12].Value = model.STATE;
			parameters[13].Value = model.AREA_ID;
			parameters[14].Value = model.STORAGE_ID;

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

            return model.MOVELOC_ID;
        }

        /// <summary>
        /// 修改移位单信息
        /// </summary>
        /// <param name="MoveLocModel">移位单Model</param>
        public void UpdateMoveLoc(MoveLocModel model) {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("update T_STOCK_MOVELOC set ");
            strSql.AppendLine("MOVELOC_ID=:MOVELOC_ID,");
            strSql.AppendLine("MOVELOC_TYPE=:MOVELOC_TYPE,");
            strSql.AppendLine("TOTAL_PLAN_NUM=:TOTAL_PLAN_NUM,");
            strSql.AppendLine("TOTAL_REAL_NUM=:TOTAL_REAL_NUM,");
            strSql.AppendLine("CREATE_PERSON=:CREATE_PERSON,");
            strSql.AppendLine("CONFIRM_PERSON=:CONFIRM_PERSON,");
            strSql.AppendLine("MOVE_PERSON=:MOVE_PERSON,");
            strSql.AppendLine("CREATE_TIME=:CREATE_TIME,");
            strSql.AppendLine("CONFIRM_TIME=:CONFIRM_TIME,");
            strSql.AppendLine("MOVE_TIME=:MOVE_TIME,");
            strSql.AppendLine("REASON=:REASON,");
            strSql.AppendLine("REMARK=:REMARK,");
            strSql.AppendLine("STATE=:STATE,");
            strSql.AppendLine("AREA_ID=:AREA_ID,");
            strSql.AppendLine("PRINT_COUNT=:PRINT_COUNT,");
            strSql.AppendLine("STORAGE_ID=:STORAGE_ID");
            strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":MOVELOC_TYPE", OracleDbType.Char),
					new OracleParameter(":TOTAL_PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":TOTAL_REAL_NUM", OracleDbType.Decimal),
					new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":MOVE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_TIME", OracleDbType.Date),
					new OracleParameter(":CONFIRM_TIME", OracleDbType.Date),
					new OracleParameter(":MOVE_TIME", OracleDbType.Date),
					new OracleParameter(":REASON",OracleDbType.Varchar2),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":STATE", OracleDbType.Char),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":PRINT_COUNT", OracleDbType.Decimal),
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)};
            parameters[0].Value = model.MOVELOC_ID;
            parameters[1].Value = model.MOVELOC_TYPE;
            parameters[2].Value = model.TOTAL_PLAN_NUM;
            parameters[3].Value = model.TOTAL_REAL_NUM;
            parameters[4].Value = model.CREATE_PERSON;
            parameters[5].Value = model.CONFIRM_PERSON;
            parameters[6].Value = model.MOVE_PERSON;
            parameters[7].Value = model.CREATE_TIME;
            parameters[8].Value = model.CONFIRM_TIME;
            parameters[9].Value = model.MOVE_TIME;
            parameters[10].Value = model.REASON;
            parameters[11].Value = model.REMARK;
            parameters[12].Value = model.STATE;
            parameters[13].Value = model.AREA_ID;
            parameters[14].Value = model.PRINT_COUNT;
            parameters[15].Value = model.STORAGE_ID;

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

        /// <summary>
        /// 修改移位单Status
        /// </summary>
        /// <param name="MoveLocModel">移位单Model</param>
        public void UpdateMoveLocState(MoveLocModel model)
        {
            StringBuilder strSql = new StringBuilder();

            if (model.STATE == "1") {
                strSql.AppendLine("update T_STOCK_MOVELOC set ");
                strSql.AppendLine("CONFIRM_PERSON=:CONFIRM_PERSON,");
                strSql.AppendLine("CONFIRM_TIME=getdate(),");
                strSql.AppendLine("STATE=:STATE");
                strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
                OracleParameter[] parameters = {
					new OracleParameter(":CONFIRM_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":STATE", OracleDbType.Char),
                   new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)};

                parameters[0].Value = model.CONFIRM_PERSON;
                parameters[1].Value = model.STATE;
                parameters[2].Value = model.MOVELOC_ID;
                SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

            }
            //else if (model.STATE == "2")
            //{
            //    strSql.AppendLine("update T_STOCK_MOVELOC set ");
            //    strSql.AppendLine("MOVE_PERSON=:MOVE_PERSON,");
            //    strSql.AppendLine("MOVE_TIME=getdate(),");
            //    strSql.AppendLine("STATE=:STATE,");
            //    strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
            //    OracleParameter[] parameters = {
            //       new OracleParameter(":MOVE_PERSON",OracleDbType.Varchar2,20),
            //       new OracleParameter(":STATE", OracleDbType.Char,1),
            //       new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2,20)};

            //    parameters[0].Value = model.CONFIRM_PERSON;
            //    parameters[1].Value = model.STATE;
            //    parameters[2].Value = model.MOVELOC_ID;
            //    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
            //}
            else if(model.STATE == "3")//关闭
            {
                strSql.AppendLine("update T_STOCK_MOVELOC set ");
                strSql.AppendLine("STATE=:STATE");
                strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
                OracleParameter[] parameters = {
                   new OracleParameter(":STATE", OracleDbType.Char),
                   new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)};

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


        }

        /// <summary>
        /// 删除移位单信息
        /// </summary>
        /// <param name="MoveLoc"></param>
        public void DeleteMoveLoc(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_STOCK_MOVELOC ");
            strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = ID;

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

        /// <summary>
        /// 向备份表中添加移位单
        /// </summary>
        /// <param name="moveLocID">移位单编号</param>
        public void InsertMoveLocBack(string moveLocID)
        {
            string strSql = "insert into T_STOCK_MOVELOC_BACK select * from T_STOCK_MOVELOC where MOVELOC_ID =:MOVELOC_ID";
            OracleParameter[] parameters = {
				new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)};
            parameters[0].Value = moveLocID;

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

        }
    }
}