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;

namespace WMS.SqlServerData.CKData
{
    public class PickingWaveData : IPickingWave
    {
        /// <summary>
        /// 获取捡货单
        /// </summary>
        /// <returns>捡货单</returns>
        public DataTable GetPickingWaveDT(PickingWaveModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select                                                  ");
            strSql.AppendLine("  a.PICKINGID,				   --拣货单号              ");
            strSql.AppendLine("  a.OPERATOR_MAN,               --拣货人（班组）        ");
            strSql.AppendLine("  a.STORAGE_ID,                 --仓库编号              ");
            strSql.AppendLine("  a.AREA_ID,                    --库区编号              ");
            strSql.AppendLine("  a.CREATE_PERSON,              --创建人                ");
            strSql.AppendLine("  a.CREATE_DATE,                --创建时间              ");
            strSql.AppendLine("  a.REMARK,                     --备注                  ");
            strSql.AppendLine("  b.storage_name,               --仓库                  ");
            strSql.AppendLine("  c.storage_area_name area_name,--库区                  ");
            strSql.AppendLine("	 a.PRINT_COUNT,                --打印次数              ");
            strSql.AppendLine("  d.name CREATE_PERSON_name,    --创建人                ");
            strSql.AppendLine("  e.name OPERATOR_MAN_name ,     --拣货人                ");
            strSql.AppendLine("  a.status   ,    --拣货人                ");
            strSql.AppendLine("   (case a.status when '0' then '未派发' when '1' then '已派发'when '4' then '执行中' end )statusname    --拣货人                ");
            strSql.AppendLine("FROM                                                    ");
            strSql.AppendLine("  T_CK_PICKINGWAVE a left join t_base_storageinfo b     ");
            strSql.AppendLine("  on a.storage_id = b.storage_id                        ");
            strSql.AppendLine("  left join t_base_storage_area c                       ");
            strSql.AppendLine("  on a.area_id = c.storage_area_id                      ");
            strSql.AppendLine("  left join t_base_userinfo d                           ");
            strSql.AppendLine("  on a.create_person = d.user_id                        ");
            strSql.AppendLine("  left join t_base_userinfo e                           ");
            strSql.AppendLine("  on a.OPERATOR_MAN = e.user_id                         ");
            strSql.AppendLine("where 1 = 1");
            if (!string.IsNullOrEmpty(model.PICKINGID))
            {
                strSql.AppendLine(" and a.PICKINGID = '" + model.PICKINGID + "' ");
            }
            if (!string.IsNullOrEmpty(model.OPERATOR_MAN))
            {
                strSql.AppendLine(" and a.OPERATOR_MAN = '" + model.OPERATOR_MAN + "' ");
            }
            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 (!string.IsNullOrEmpty(model.CREATE_PERSON))
            {
                strSql.AppendLine(" and a.CREATE_PERSON = '" + model.CREATE_PERSON + "' ");
            }
            if (model.CREATE_DATE.Year.ToString()  != "1900")
            { 

                strSql.AppendLine(" and convert(varchar(100),a.CREATE_DATE,23)  = '" + model.CREATE_DATE.ToShortDateString() + "'");
            }
            strSql.AppendLine(" order by a.PICKINGID desc,a.status");

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

        /// <summary>
        /// 批量获取捡货单
        /// </summary>
        /// <returns>捡货单集合</returns>
        public DataTable GetPickingWaveListDT(List<PickingWaveModel> list)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select                                                  ");
            strSql.AppendLine("  a.PICKINGID,				   --拣货单号              ");
            strSql.AppendLine("  a.OPERATOR_MAN,               --拣货人（班组）        ");
            strSql.AppendLine("  a.STORAGE_ID,                 --仓库编号              ");
            strSql.AppendLine("  a.AREA_ID,                    --库区编号              ");
            strSql.AppendLine("  a.CREATE_PERSON,              --创建人                ");
            strSql.AppendLine("  a.CREATE_DATE,                --创建时间              ");
            strSql.AppendLine("  a.REMARK,                     --备注                  ");
            strSql.AppendLine("  b.storage_name,               --仓库                  ");
            strSql.AppendLine("  c.storage_area_name area_name,--库区                  ");
            strSql.AppendLine("	 a.PRINT_COUNT,                --打印次数              ");
            strSql.AppendLine("  d.name CREATE_PERSON_name,    --创建人                ");
            strSql.AppendLine("  e.name OPERATOR_MAN_name ,     --拣货人                ");
            strSql.AppendLine("  a.status   ,    --拣货人                ");
            strSql.AppendLine("   (case a.status when '0' then '未派发' when '1' then  '已派发'  when '4' then '执行中' end  )statusname    --拣货人                ");
            strSql.AppendLine("FROM                                                    ");
            strSql.AppendLine("  T_CK_PICKINGWAVE a left join t_base_storageinfo b     ");
            strSql.AppendLine("  on a.storage_id = b.storage_id                        ");
            strSql.AppendLine("  left join t_base_storage_area c                       ");
            strSql.AppendLine("  on a.area_id = c.storage_area_id                      ");
            strSql.AppendLine("  left join t_base_userinfo d                           ");
            strSql.AppendLine("  on a.create_person = d.user_id                        ");
            strSql.AppendLine("  left join t_base_userinfo e                           ");
            strSql.AppendLine("  on a.OPERATOR_MAN = e.user_id                        ");
            strSql.AppendLine("where 1 = 0 ");
            foreach (var model in list)
            {
                strSql.AppendLine(" or a.PICKINGID = '" + model.PICKINGID + "' ");
            }
            strSql.AppendLine(" order by a.PICKINGID desc,a.status");

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

        /// <summary>
        /// 插入捡货单信息
        /// </summary>
        /// <param name="PickingWaveModel">捡货单Model</param>
        /// <returns>捡货单编号</returns>
        public string InsertPickingWave(PickingWaveModel model)
        {
            //string str = "select to_char(getdate(),'yyyyMMdd')||SEQ_OUT_PICKINGWAVE.nextval from dual ";
            //DataSet ds =   SystemDataObject.Instance.GetDataSet( str);
            //model.PICKINGID = "JH" + ds.Tables[0].Rows[0][0].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("insert into T_CK_PICKINGWAVE(");
            strSql.AppendLine("PICKINGID,OPERATOR_MAN,STORAGE_ID,AREA_ID,CREATE_PERSON,CREATE_DATE,REMARK,PRINT_COUNT,STATUS)");
            strSql.AppendLine(" values (");
            strSql.AppendLine(":PICKINGID,:OPERATOR_MAN,:STORAGE_ID,:AREA_ID,:CREATE_PERSON,getdate(),:REMARK,0,'0')");
            OracleParameter[] parameters = {
					new OracleParameter(":PICKINGID",OracleDbType.Varchar2),
					new OracleParameter(":OPERATOR_MAN",OracleDbType.Varchar2),
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
                    //new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":REMARK",OracleDbType.Varchar2)};
            parameters[0].Value = model.PICKINGID;
            parameters[1].Value = model.OPERATOR_MAN;
            parameters[2].Value = model.STORAGE_ID;
            parameters[3].Value = model.AREA_ID;
            parameters[4].Value = model.CREATE_PERSON;
            //parameters[5].Value = model.CREATE_DATE;
            parameters[5].Value = model.REMARK;

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

            return model.PICKINGID;
        }
  /// <summary>
        /// 修改捡货单信息
        /// </summary>
        /// <param name="PickingWaveModel">捡货单Model</param>
        public void UpdatePickingWave(string pickingid,string status)
        {
            string sqlStr =@"update T_CK_PICKINGWAVE t
                                   set STATUS = '"+status+"'where t.PICKINGID ='"+pickingid+"'"; 
            SystemDataObject.Instance.ExecuteSql(sqlStr);

 
        }
        /// <summary>
        /// 修改捡货单信息
        /// </summary>
        /// <param name="PickingWaveModel">捡货单Model</param>
        public void UpdatePickingWave(PickingWaveModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("update T_CK_PICKINGWAVE set ");
            strSql.AppendLine("PICKINGID=:PICKINGID,");
            strSql.AppendLine("OPERATOR_MAN=:OPERATOR_MAN,");
            strSql.AppendLine("STORAGE_ID=:STORAGE_ID,");
            strSql.AppendLine("AREA_ID=:AREA_ID,");
            strSql.AppendLine("CREATE_PERSON=:CREATE_PERSON,");
            strSql.AppendLine("CREATE_DATE=:CREATE_DATE,");
            strSql.AppendLine("PRINT_COUNT=:PRINT_COUNT,");
            strSql.AppendLine("REMARK=:REMARK");
            strSql.AppendLine(" where PICKINGID=:PICKINGID ");
            OracleParameter[] parameters = {
					new OracleParameter(":PICKINGID",OracleDbType.Varchar2),
					new OracleParameter(":OPERATOR_MAN",OracleDbType.Varchar2),
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_PERSON",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":PRINT_COUNT", OracleDbType.Decimal),
					new OracleParameter(":REMARK",OracleDbType.Varchar2)};
            parameters[0].Value = model.PICKINGID;
            parameters[1].Value = model.OPERATOR_MAN;
            parameters[2].Value = model.STORAGE_ID;
            parameters[3].Value = model.AREA_ID;
            parameters[4].Value = model.CREATE_PERSON;
            parameters[5].Value = model.CREATE_DATE;
            parameters[6].Value = model.PRINT_COUNT;
            parameters[7].Value = model.REMARK;

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

        /// <summary>
        /// 删除一个捡货单
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void DeletePickingWave(string pickingID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_CK_PICKINGWAVE ");
            strSql.AppendLine(" where PICKINGID=:PICKINGID ");
            OracleParameter[] parameters = {
					new OracleParameter(":PICKINGID",OracleDbType.Varchar2)			};
            parameters[0].Value = pickingID;

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

        /// <summary>
        /// 向备份表中添加一个捡货单
        /// </summary>
        /// <param name="pickingID">捡货单ID</param>
        public void InsertPickingWaveBack(string pickingID)
        {
            string strSql = "insert into T_CK_PickingWave_BACK select * from T_CK_PickingWave where PICKINGID=:PICKINGID";
            OracleParameter[] parameters = {
				new OracleParameter(":PICKINGID",OracleDbType.Varchar2)};
            parameters[0].Value = pickingID;

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

        }
    }
}