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


namespace WMS.SqlServerData.BaseData
{
    public class BaseCtlGoodsData : IBaseCtlGoods
    {
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="model">容器管理Model</param>
        /// <returns></returns>
        public DataTable GetDataByID(BaseCtlGoodsModel model)
        {
            StringBuilder str = new StringBuilder();
            #region SQL语句
            str.Append(@"select       
                           p.GOODS_ID, --商品编号
                           g.goods_name, --商品名称
                           g.gvolume, --商品体积
                           g.price, --商品价格
                           g.gross_weight, --商品毛重
                           g.net_weight, --商品净重
                           p.CTL_PALLET_ID, --Pallet Num
                           p.OPERATORID,  --录入人
                           p.OPERATEDATE,--录入日期
                           (case pa.status when '0' then '空闲' when '1' then '占用' end) status, --容器Status
                           pa.volume, --容器体积
                           pa.bearweight, --容器承受重量
                           p.VC_DICTIONARY_ID, --事务类型(上架，下架，盘点，收货码盘)
                           t.vc_dictionary_name,  --事务类型名称
                           u.NAME OPERATORNAME --录入人姓名
                           from T_BASE_CTL_GOODS p --容器详细表
                           left outer join T_BASE_PALLET pa --容器表
                           on p.ctl_pallet_id = pa.pallet_id
                           left outer join T_BASE_GOODS g --商品明细表
                           on p.goods_id = g.goods_id
                           left outer join t_Sys_Dictionary_Tab t --字典明细表
                           on p.vc_dictionary_id = t.vc_dictionary_id
                           left outer join T_base_UserInfo u --用户表
                           on p.Operatorid = u.User_ID
                           where 1 = 1 ");
            #endregion

            #region 查询条件
            if (model != null)
            {
                //托盘编号
                if (!string.IsNullOrEmpty(model.CTL_PALLET_ID))
                {
                    str.Append(" and CTL_PALLET_ID like '%" + model.CTL_PALLET_ID + "%'");
                }
                //事务类型编号
                if (!string.IsNullOrEmpty(model.VC_DICTIONARY_ID))
                {
                    str.Append(" and p.VC_DICTIONARY_ID like '%" + model.VC_DICTIONARY_ID + "%'");
                }
                //搬运人
                if (!string.IsNullOrEmpty(model.User_ID))
                {
                    str.Append("and p.User_ID like '%" + model.User_ID + "%'");
                }
                str.Append(" order by p.OPERATEDATE desc ");
            }
            #endregion

            DataTable dt = SystemDataObject.Instance.GetDataTable(str.ToString());
            return dt;
        }

        #region 操作
        /// <summary>
        /// 操作容器
        /// </summary>
        /// <param name="list">容器管理Model</param>
        /// <param name="flag">0: 新增  1：修改</param>
        public void Operator(List<BaseCtlGoodsModel> list, int flag)
        {
            List<string> sqlstrlist = new List<string>();
            List<OracleParameter[]> sqlpara = new List<OracleParameter[]>();

            string strPallet = string.Empty;
            #region 批量
            if (flag == 0)
            {
                foreach (BaseCtlGoodsModel bcgoods in list)
                {
                    strPallet =@"insert into T_BASE_CTL_GOODS
                                   (CTL_PALLET_ID,
                                    GOODS_ID,
                                    VC_DICTIONARY_ID,
                                    VC_TYPE,
                                    USER_ID,
                                    OPERATORID,
                                    OPERATEDATE)
                                 values
                                   (:CTL_PALLET_ID,
                                    :GOODS_ID, 
                                    :VC_DICTIONARY_ID,
                                    :VC_TYPE,
                                    :USER_ID,
                                    :OPERATORID,
                                    :OPERATEDATE)";

                    OracleParameter[] param = new OracleParameter[] { 
               new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2),
               new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
               new OracleParameter(":VC_DICTIONARY_ID",OracleDbType.Varchar2),
               new OracleParameter(":VC_TYPE",OracleDbType.Varchar2),
               new OracleParameter(":USER_ID",OracleDbType.Varchar2),
               new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
               new OracleParameter(":OPERATEDATE",OracleDbType.Date)
                };
                    param[0].Value = bcgoods.CTL_PALLET_ID;
                    param[1].Value = bcgoods.GOODS_ID;
                    param[2].Value = bcgoods.VC_DICTIONARY_ID;
                    param[3].Value = bcgoods.VC_TYPE;
                    param[4].Value = bcgoods.User_ID;
                    param[5].Value = bcgoods.OPERATORID;
                    param[6].Value = bcgoods.OPERATEDATE;

                    sqlstrlist.Add(strPallet);
                    sqlpara.Add(param);
                }
                SystemDataObject.Instance.ExecuteList(sqlstrlist, sqlpara);
            }
            #endregion

            #region 更换容器 替换编号
            else if (flag == 1)
            {
                foreach (BaseCtlGoodsModel model in list)
                {
                    strPallet =@"   update T_BASE_CTL_GOODS
                                set  
                                OPERATORID       =:OPERATORID,
                                OPERATEDATE      =:OPERATEDATE,
                                VC_DICTIONARY_ID =:VC_DICTIONARY_ID,
                                CTL_PALLET_ID    =:CTL_PALLET_ID
                                where
                                CTL_PALLET_ID    =:CTL_PALLET_ID 
                                and GOODS_ID     =:GOODS_ID";

                    OracleParameter[] param = new OracleParameter[] { 
                   new OracleParameter(":OPERATORID",OracleDbType.Varchar2),
               new OracleParameter(":OPERATEDATE",OracleDbType.Date),
               new OracleParameter(":VC_DICTIONARY_ID",OracleDbType.Varchar2),
               new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2),
               new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2),
               new OracleParameter(":GOODS_ID",OracleDbType.Varchar2)
                };
                    param[0].Value = model.OPERATORID;
                    param[1].Value = model.OPERATEDATE;
                    param[2].Value = model.VC_DICTIONARY_ID;
                    param[3].Value = model.PALLET_NEWID;
                    param[4].Value = model.CTL_PALLET_ID;
                    param[5].Value = model.GOODS_ID;

                    sqlstrlist.Add(strPallet);
                    sqlpara.Add(param);
                }
                SystemDataObject.Instance.ExecuteList(sqlstrlist, sqlpara);
            }
            #endregion
        }

        #endregion

        #region 删除(解除绑定)
        /// <summary>
        /// 删除数据(解除绑定)
        /// </summary>
        /// <param name="model">容器Model</param>
        public void DeleteData(BaseCtlGoodsModel model)
        {
            string strPalletdel;
            strPalletdel =@"delete from
                             T_BASE_CTL_GOODS  --容器表
                             where
                             CTL_PALLET_ID =:CTL_PALLET_ID  --托盘编号";
            OracleParameter[] param = new OracleParameter[] { 
               new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2)
            };
            param[0].Value = model.CTL_PALLET_ID;
            SystemDataObject.Instance.ExecuteSqlCommit(strPalletdel, param);
        }
        #endregion



        #region 删除多条(解除绑定)
        /// <summary>
        /// 删除多条数据(解除绑定)
        /// </summary>
        /// <param name="list">容器Model</param>
        public void DeleteMoreData(List<BaseCtlGoodsModel> list)
        {
            string strPalletdel;
            List<string> strlistadd = new List<string>();
            List<OracleParameter[]> strparamadd = new List<OracleParameter[]>();
            foreach (BaseCtlGoodsModel ctlmodel in list)
            {
                strPalletdel =@"delete from
                             T_BASE_CTL_GOODS  --容器表
                             where
                             CTL_PALLET_ID =:CTL_PALLET_ID  --托盘编号";
                OracleParameter[] param = new OracleParameter[] { 
               new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2)
            };
                param[0].Value = ctlmodel.CTL_PALLET_ID;

                strlistadd.Add(strPalletdel);
                strparamadd.Add(param);
            }
            SystemDataObject.Instance.ExecuteList(strlistadd, strparamadd);
        }
        #endregion




        #region 废弃 ->删除数据写入到备份表中(T_BASE_CTL_GOODS_BACK未建立)
        /// <summary>
        /// 删除数据写入到备份表中
        /// </summary>
        /// <param name="list">容器管理Model集合</param>
        //public void InsertBaseCtlGoodsBack(List<BaseCtlGoodsModel> list)
        //{
        //    string strSql = string.Empty;
        //    List<string> delsqlstrlist = new List<string>();
        //    List<OracleParameter[]> delsqlpara = new List<OracleParameter[]>();

        //    foreach (BaseCtlGoodsModel model in list)
        //    {
        //        strSql = "insert into T_BASE_CTL_GOODS_BACK select * from T_BASE_CTL_GOODS where CTL_PALLET_ID =:CTL_PALLET_ID";
        //        OracleParameter[] param = new OracleParameter{
        //       new OracleParameter(":CTL_PALLET_ID",OracleDbType.Varchar2)};
        //        param[0].Value = model.CTL_PALLET_ID;

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

}