using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData.IBase;
using WMS.Model.Base;
using System.Data;
using WMS.Common;
using WMS.IData;
using Oracle.ManagedDataAccess.Client;

using WMS.DBUtility;

namespace WMS.SqlServerData.BaseData
{
    public class PalletData : IPallet
    {

        /// <summary>
        /// 更改容器的状态
        /// </summary>
        /// <param name="ctl"></param>
        /// <param name="sts"></param>
        public void SetPalletSts(string ctl,string sts,string WORK_TYPE)
        {
            string sqlStr = " update T_BASE_PALLET set STATUS='" + sts + "',WORK_TYPE='"+ WORK_TYPE + "' where PALLET_ID='" + ctl + "' ";
            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }

        #region 查询容器数据
        /// <summary>
        /// 根据查询条件查询出容器数据
        /// </summary>
        /// <param name="model">容器Model</param>
        /// <returns></returns>
        public DataTable GetPalletTable(PalletModel model)
        {
            #region SQL语句
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select ");
            sql.AppendLine(" t.PALLET_ID,t.PALLET_NAME,  --Pallet Num ");
            sql.AppendLine(" d.vc_dictionary_name PALLET_NAME,  --Pallet Name ");
            sql.AppendLine(" t.PY_NAME,  --拼音简称 ");
            sql.AppendLine(" t.pallet_type,  --容器类型 ");
            sql.AppendLine(" t.Pallet_Long,  --长 ");
            sql.AppendLine(" t.Pallet_Width,  --宽 ");
            sql.AppendLine(" t.Pallet_Height,  --高 ");
            sql.AppendLine(" t.Volume,  --容积 ");
            sql.AppendLine(" t.Bearweight,  --承载重量 ");
            sql.AppendLine(" t.status,  --Status");
            sql.AppendLine(" case status  when '0' then '空闲' when '1' then '占用' end STATUS_NAME,  --Status名称 ");
            sql.AppendLine(" u.Name Name,   --占用人姓名");
            sql.AppendLine(" t.User_ID,  --占用人编号");
            sql.AppendLine(" t.Version,  --版本号 ");
            sql.AppendLine(" t.remark,  --备注 ");
            sql.AppendLine(" t.OPERATORID,  --操作人编号 ");
            sql.AppendLine(" us.Name OPERATOR_NAME, --操作人");
            sql.AppendLine(" t.OPERATEDATE,  --操作日期 ");
            sql.AppendLine(" d.vc_dictionary_name type_name  --容器类型名称  ");
            sql.AppendLine(" from ");
            sql.AppendLine(" T_BASE_PALLET t  --容器表 ");
            sql.AppendLine(" left outer join ");
            sql.AppendLine(" T_SYS_DICTIONARY_TAB d   --字典类型明细表 ");
            sql.AppendLine(" on t.pallet_type = d.vc_dictionary_id");
            sql.AppendLine(" left outer join ");
            sql.AppendLine(" T_BASE_UserInfo us --用户表");
            sql.AppendLine(" on t.Operatorid = us.User_id");
            sql.AppendLine(" left outer join ");
            sql.AppendLine(" T_BASE_UserInfo u  --用户表");
            sql.AppendLine(" on t.User_ID = u.User_ID  where 1 = 1 ");
            #endregion


            #region 查询条件

            if (!string.IsNullOrEmpty(model.PALLET_ID))
            {
                sql.AppendLine(" and t.PALLET_ID like '%" + model.PALLET_ID + "%'");
            }
            if (!string.IsNullOrEmpty(model.PALLET_TYPE))
            {
                sql.AppendLine(" and t.pallet_type = '" + model.PALLET_TYPE + "'");
            }
            sql.AppendLine(" order by Pallet_ID asc");
            //if (!string.IsNullOrEmpty(model.OPERATORID))
            //{
            //    sql.AppendLine(" and t.OPERATORID like '%" + model.OPERATORID + "%'");
            //}

            if (!string.IsNullOrEmpty(model.STATUS))
            {
                sql.AppendLine(" and t.STATUS = '" + model.STATUS + "'");
            }
            //if (!string.IsNullOrEmpty(model.User_ID))
            //{
            //    sql.AppendLine(" and t.User_id like '%" + model.User_ID + "%'");
            //}

            #endregion
            DataTable dt = SystemDataObject.Instance.GetDataTable(sql.ToString());

            return dt;
        }


        #endregion




        public DataTable GetCtl(int barcodetime)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select * from T_BASE_PALLET_PRINT t ");
            if (barcodetime != 0)
            {
                sql.AppendLine(" where  t.barcodetime=" + barcodetime + " ");
            }

            DataTable dt = SystemDataObject.Instance.GetDataTable(sql.ToString());

            return dt;
        }



        public DataTable GetCtl2 (int barcodetime)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" select t.seqid from T_BASE_PALLET_PRINT t where  t.barcodetime='"+ barcodetime + "' order by t.seqid desc ");
            //if (barcodetime != 0)
            //{
            //    sql.AppendLine(" where  t.barcodetime=" + barcodetime + " ");
            //}

            DataTable dt = SystemDataObject.Instance.GetDataTable(sql.ToString());

            return dt;
        }




        public void Insert_T_BASE_PALLET (int STORAGE_ID, int BARCODETIME, int SEQID,string BarCode)
        {
            string sqlStr = " insert into  T_BASE_PALLET_PRINT (STORAGE_ID,BARCODETIME,SEQID,BARCODE) values(" + STORAGE_ID + ","+ BARCODETIME + ","+ SEQID + ",'"+ BarCode + "')";
            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }





        #region 操作容器数据 批量新增
        /// <summary>
        /// 操作容器数据 新增
        /// </summary>
        /// <param name="model">容器Model集合</param>
        /// <param name="Flag">0：新增 1修改 </param>
        /// <returns></returns>
        public void OperatePallet(List<PalletModel> lstPallet, int flag)
        {
            switch (flag)
            {
                case 0:
                    AddPallet(lstPallet);
                    break;
                case 1:
                    UpdatePalletList(lstPallet);
                    break;
                default:
                    break;
            }


        }

        private static void AddPallet(List<PalletModel> model)
        {

            #region 获得Pallet Name
            string typeName = "";
            foreach (PalletModel p in model)
            {
                typeName = p.PALLET_TYPE;
            }
            string strname =@"select VC_DICTIONARY_NAME PALLET_NAME 
                                    from t_Sys_Dictionary_Tab 
                                    where VC_DICTIONARY_TYPE='pallet_type'
                                    and vc_dictionary_id='" + typeName + "'";
            DataTable tabType = SystemDataObject.Instance.GetDataTable(strname);
            string strPalletName = string.Empty;
            if (tabType != null && tabType.Rows.Count > 0)
            {
                strPalletName = tabType.Rows[0][0].ToString();
            }
            #endregion

            string strPalletAdd = string.Empty;
            List<string> addsqlstrlist = new List<string>();
            List<OracleParameter[]> addsqlpara = new List<OracleParameter[]>();

            foreach (PalletModel pa in model)
            {
                #region SQL语句 Parameter参数
                //通过字典表获得Pallet Name
                pa.PALLET_NAME = strPalletName;
                strPalletAdd =@"   insert into T_BASE_PALLET
                                          (PALLET_ID, --Pallet Num
                                           PALLET_NAME, --Pallet Name
                                           PALLET_TYPE, --容器类型
                                           PALLET_LONG, --长
                                           PALLET_WIDTH, --宽
                                           PALLET_HEIGHT, --高
                                           VOLUME, --容积
                                           BEARWEIGHT, --承载重量
                                           STATUS, --容器Status
                                           USER_ID, --占用人编号
                                           REMARK, --备注
                                           VERSION, --版本号
                                           OPERATORID,  --操作人
                                           OPERATEDATE) --操作日期 
                                        values
                                          (:PALLET_ID, --Pallet Num
                                           :PALLET_NAME, --Pallet Name
                                           :PALLET_TYPE, --容器类型
                                           :PALLET_LONG, --长
                                           :PALLET_WIDTH, --宽
                                           :PALLET_HEIGHT, --高
                                           :VOLUME, --容积
                                           :BEARWEIGHT, --承载重量
                                           :STATUS, --容器Status
                                           :USER_ID, --占用人编号
                                           :REMARK, --备注
                                           :VERSION, --版本号
                                           :OPERATORID, --操作人
                                           :OPERATEDATE) --操作日期";

                OracleParameter[] param = new OracleParameter[] {
               new OracleParameter(":PALLET_ID",OracleDbType.Varchar2,50),
               new OracleParameter(":PALLET_NAME",OracleDbType.Varchar2,50),
               new OracleParameter(":PALLET_TYPE",OracleDbType.Varchar2,50),
               new OracleParameter(":PALLET_LONG",OracleDbType.Decimal,4),
               new OracleParameter(":PALLET_WIDTH",OracleDbType.Decimal,4),
               new OracleParameter(":PALLET_HEIGHT",OracleDbType.Decimal,4),
               new OracleParameter(":VOLUME",OracleDbType.Decimal,4),
               new OracleParameter(":BEARWEIGHT",OracleDbType.Decimal,4),
               new OracleParameter(":STATUS",OracleDbType.Char,10),
               new OracleParameter(":USER_ID",OracleDbType.Varchar2,50),
               new OracleParameter(":REMARK",OracleDbType.Varchar2,50),
               new OracleParameter(":VERSION",OracleDbType.Varchar2,50),
               new OracleParameter(":OPERATORID",OracleDbType.Varchar2,50),
               new OracleParameter(":OPERATEDATE",OracleDbType.Date)
                };
                param[0].Value = pa.PALLET_ID;
                param[1].Value = pa.PALLET_NAME;
                param[2].Value = pa.PALLET_TYPE;
                param[3].Value = pa.PALLET_LONG;
                param[4].Value = pa.PALLET_WIDTH;
                param[5].Value = pa.PALLET_HEIGHT;
                param[6].Value = pa.VOLUME;
                param[7].Value = pa.BEARWEIGHT;
                param[8].Value = pa.STATUS;
                param[9].Value = pa.USER_ID;
                param[10].Value = pa.REMARK;
                param[11].Value = pa.VERSION;
                param[12].Value = pa.OPERATORID;
                param[13].Value = pa.OPERATEDATE;
                #endregion

                addsqlstrlist.Add(strPalletAdd.ToString());
                addsqlpara.Add(param);
            }

            SystemDataObject.Instance.ExecuteList(addsqlstrlist, addsqlpara);
        }
        #endregion

        #region 修改容器资料
        /// <summary>
        /// 修改容器资料
        /// </summary>
        /// <param name="model">容器Model</param>
        /// <param name="Flag">1:修改</param>
        public void OperatePallet(PalletModel model, int Flag)
        {
            if (Flag == 1)
            {
                UpdatePallet(model);
            }

        }

        private static void UpdatePallet(PalletModel model)
        {
            #region 获得Pallet Name
            string strname =@"select VC_DICTIONARY_NAME PALLET_NAME 
                                    from t_Sys_Dictionary_Tab 
                                    where VC_DICTIONARY_TYPE='pallet_type'
                                    and vc_dictionary_id='" + model.PALLET_TYPE + "'";
            DataTable dt = SystemDataObject.Instance.GetDataTable(strname);
            string strPalletName = dt.Rows[0][0].ToString();
            #endregion

            #region SQL语句 Parameter参数
            //通过字典表获得Pallet Name
            model.PALLET_NAME = strPalletName;
            StringBuilder strPalletUpdate = new StringBuilder();
            strPalletUpdate.AppendLine(" update T_BASE_PALLET ");
            strPalletUpdate.AppendLine(" set ");
            strPalletUpdate.AppendLine(" PALLET_NAME=:PALLET_NAME,");
            strPalletUpdate.AppendLine(" PALLET_TYPE=:PALLET_TYPE,  --容器类型");
            strPalletUpdate.AppendLine(" PALLET_LONG=:PALLET_LONG,  --长");
            strPalletUpdate.AppendLine(" PALLET_WIDTH=:PALLET_WIDTH,  --宽");
            strPalletUpdate.AppendLine(" PALLET_HEIGHT=:PALLET_HEIGHT,  --高");
            strPalletUpdate.AppendLine(" VOLUME=:VOLUME,  --容积");
            strPalletUpdate.AppendLine(" BEARWEIGHT=:BEARWEIGHT,  --承载重量");
            strPalletUpdate.AppendLine(" STATUS=:STATUS,  --Status");
            strPalletUpdate.AppendLine(" USER_ID=:USER_ID, --占用人编号 ");
            strPalletUpdate.AppendLine(" OPERATORID=:OPERATORID,  --操作人");
            strPalletUpdate.AppendLine(" OPERATEDATE=:OPERATEDATE, --操作日期");
            strPalletUpdate.AppendLine(" REMARK=:REMARK  --备注");
            strPalletUpdate.AppendLine(" where ");
            strPalletUpdate.AppendLine(" PALLET_ID=:PALLET_ID  --Pallet Num");

            OracleParameter[] param = new OracleParameter[] {
                   new OracleParameter(":PALLET_NAME",OracleDbType.Varchar2),
               new OracleParameter(":PALLET_TYPE",OracleDbType.Varchar2),
               new OracleParameter(":PALLET_LONG",OracleDbType.Decimal),
               new OracleParameter(":PALLET_WIDTH",OracleDbType.Decimal),
               new OracleParameter(":PALLET_HEIGHT",OracleDbType.Decimal),
               new OracleParameter(":VOLUME",OracleDbType.Decimal),
               new OracleParameter(":BEARWEIGHT",OracleDbType.Decimal),
               new OracleParameter(":STATUS",OracleDbType.Char),
               new OracleParameter(":USER_ID",OracleDbType.Varchar2),
               new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
               new OracleParameter(":OPERATEDATE",OracleDbType.Date),
               new OracleParameter(":REMARK",OracleDbType.Varchar2,200),
               new OracleParameter(":PALLET_ID",OracleDbType.Varchar2),
                };
            param[0].Value = model.PALLET_NAME;
            param[1].Value = model.PALLET_TYPE;
            param[2].Value = model.PALLET_LONG;
            param[3].Value = model.PALLET_WIDTH;
            param[4].Value = model.PALLET_HEIGHT;
            param[5].Value = model.VOLUME;
            param[6].Value = model.BEARWEIGHT;
            param[7].Value = model.STATUS;
            param[8].Value = model.USER_ID;
            param[9].Value = model.OPERATORID;
            param[10].Value = model.OPERATEDATE;
            param[11].Value = model.REMARK;
            param[12].Value = model.PALLET_ID;
            #endregion

            SystemDataObject.Instance.ExecuteSqlCommit(strPalletUpdate.ToString(), param);
        }
        #endregion




        #region 批量删除容器数据
        /// <summary>
        /// 删除容器数据
        /// </summary>
        /// <param name="strmodel">容器Model集合</param>
        public void PalletDel(List<PalletModel> strmodel)
        {
            string strPalletdel = string.Empty;
            List<string> delsqlstrlist = new List<string>();
            List<OracleParameter[]> delsqlpara = new List<OracleParameter[]>();

            foreach (PalletModel palletm in strmodel)
            {
                strPalletdel =@"delete from  T_BASE_PALLET where PALLET_ID =:PALLET_ID";
                OracleParameter[] param = new OracleParameter[] {
               new OracleParameter(":PALLET_ID",OracleDbType.Varchar2),
                };
                param[0].Value = palletm.PALLET_ID;
                delsqlstrlist.Add(strPalletdel.ToString());
                delsqlpara.Add(param);
            }
            SystemDataObject.Instance.ExecuteList(delsqlstrlist, delsqlpara);
        }
        #endregion



        #region 删除数据 写入到备份表(T_BASE_PALLET_BACK 未建立)
        /// <summary>
        /// 将删除数据写入备份表
        /// </summary>
        /// <param name="listmodel">容器Model集合</param>
        public void InsertPalletBack(List<PalletModel> listmodel)
        {
            string strSql = string.Empty;
            List<string> delsqlstrlist = new List<string>();
            List<OracleParameter[]> delsqlpara = new List<OracleParameter[]>();

            foreach (PalletModel model in listmodel)
            {
                strSql = "insert into T_BASE_PALLET_BACK select * from T_BASE_PALLET where PALLET_ID =:PALLET_ID";
                OracleParameter[] param = new OracleParameter[]{
                new OracleParameter(":PALLET_ID",OracleDbType.Varchar2)};
                param[0].Value = model.PALLET_ID;

                delsqlstrlist.Add(strSql);
                delsqlpara.Add(param);
            }
            SystemDataObject.Instance.ExecuteList(delsqlstrlist, delsqlpara);
        }

        #endregion


        private static void UpdatePalletList(List<PalletModel> list)
        {
            #region 获得Pallet Name
            List<string> sqlstrlist = new List<string>();
            List<OracleParameter[]> sqlpara = new List<OracleParameter[]>();
            StringBuilder strSql = new StringBuilder();
            foreach (PalletModel model in list)
            {
                string strname =@"select VC_DICTIONARY_NAME PALLET_NAME 
                                    from t_Sys_Dictionary_Tab 
                                    where VC_DICTIONARY_TYPE='pallet_type'
                                    and vc_dictionary_id='" + model.PALLET_TYPE + "'";
                DataTable tabType = SystemDataObject.Instance.GetDataTable(strname);
                string strPalletName = string.Empty;
                if (tabType != null && tabType.Rows.Count > 0)
                {
                    strPalletName = tabType.Rows[0][0].ToString();
                }
                #endregion

                #region SQL语句 Parameter参数
                //通过字典表获得Pallet Name
                model.PALLET_NAME = strPalletName;
                StringBuilder strPalletUpdate = new StringBuilder();
                strPalletUpdate.AppendLine(" update T_BASE_PALLET ");
                strPalletUpdate.AppendLine(" set ");
                strPalletUpdate.AppendLine(" PALLET_NAME=:PALLET_NAME,");
                strPalletUpdate.AppendLine(" PALLET_TYPE=:PALLET_TYPE,  --容器类型");
                strPalletUpdate.AppendLine(" PALLET_LONG=:PALLET_LONG,  --长");
                strPalletUpdate.AppendLine(" PALLET_WIDTH=:PALLET_WIDTH,  --宽");
                strPalletUpdate.AppendLine(" PALLET_HEIGHT=:PALLET_HEIGHT,  --高");
                strPalletUpdate.AppendLine(" VOLUME=:VOLUME,  --容积");
                strPalletUpdate.AppendLine(" BEARWEIGHT=:BEARWEIGHT,  --承载重量");
                strPalletUpdate.AppendLine(" STATUS=:STATUS,  --Status");
                strPalletUpdate.AppendLine(" USER_ID=:USER_ID, --占用人编号 ");
                strPalletUpdate.AppendLine(" OPERATORID=:OPERATORID,  --操作人");
                strPalletUpdate.AppendLine(" OPERATEDATE=:OPERATEDATE, --操作日期");
                strPalletUpdate.AppendLine(" REMARK=:REMARK  --备注");
                strPalletUpdate.AppendLine(" where ");
                strPalletUpdate.AppendLine(" PALLET_ID=:PALLET_ID  --Pallet Num");

                OracleParameter[] param = new OracleParameter[] {
                   new OracleParameter(":PALLET_NAME",OracleDbType.Varchar2,50),
               new OracleParameter(":PALLET_TYPE",OracleDbType.Varchar2,50),
               new OracleParameter(":PALLET_LONG",OracleDbType.Decimal,4),
               new OracleParameter(":PALLET_WIDTH",OracleDbType.Decimal,4),
               new OracleParameter(":PALLET_HEIGHT",OracleDbType.Decimal,4),
               new OracleParameter(":VOLUME",OracleDbType.Decimal,4),
               new OracleParameter(":BEARWEIGHT",OracleDbType.Decimal,4),
               new OracleParameter(":STATUS",OracleDbType.Char,20),
               new OracleParameter(":USER_ID",OracleDbType.Varchar2,20),
               new OracleParameter(":OPERATORID",OracleDbType.Varchar2,20),
               new OracleParameter(":OPERATEDATE",OracleDbType.Date),
               new OracleParameter(":REMARK",OracleDbType.Varchar2,200),
               new OracleParameter(":PALLET_ID",OracleDbType.Varchar2,20),
                };
                param[0].Value = model.PALLET_NAME;
                param[1].Value = model.PALLET_TYPE;
                param[2].Value = model.PALLET_LONG;
                param[3].Value = model.PALLET_WIDTH;
                param[4].Value = model.PALLET_HEIGHT;
                param[5].Value = model.VOLUME;
                param[6].Value = model.BEARWEIGHT;
                param[7].Value = model.STATUS;
                param[8].Value = model.USER_ID;
                param[9].Value = model.OPERATORID;
                param[10].Value = model.OPERATEDATE;
                param[11].Value = model.REMARK;
                param[12].Value = model.PALLET_ID;
                sqlstrlist.Add(strPalletUpdate.ToString());
                sqlpara.Add(param);
                strSql.Clear();
            }
            #endregion

            SystemDataObject.Instance.ExecuteList(sqlstrlist, sqlpara);
        }
    }
}