using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData.ISC;
using WMS.Model.SC;

using System.Data;
using WMS.IData;
using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.SCData
{
    public class WorkingTaskData : IWorkingTask
    {
        #region 根据条件获得物料数据
        /// <summary>
        /// 获得物料数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetMaterialList(WorkingTaskModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select t.PALAN_ID,
                               pl.plan_name palan_name,
                               t.GOODS_ID,
                               g.goods_name        GOODS_NAME,
                               t.MATERIAL_ID,
                               m.goods_name        MATERIAL_NAME,
                               p.Goods_Num         GOODS_NUM,
                               p.Goods_Completenum GOODS_COMPLETEMUN,
                               p.complete_date     COMPLETE_DATE,
                               p.team_id           TEAM_ID,
                               t.STAND_NUM,
                               t.MAX_NUM,
                               t.REALITY_NUM,
                               t.LOSS_PERCENT,
                               t.REALITY_MAN,
                               u.Name              REALITY_MAN_NAME,
                               t.OPERAT_MAN,
                               b.name              OPERAT_MAN_NAME,
                               t.OPERAT_DATE,
                               t.REALITY_DATE,
                               t.REMARK,
                               t.UNIT,
                               ut.VC_DICTIONARY_NAME UNIT_NAME, 
                               t.PRICE
                          FROM T_SC_GET_MATERIAL t
                          left join T_BASE_GOODS g
                            on t.goods_id = g.goods_id
                          left join T_BASE_GOODS m
                            on t.material_id = m.goods_id
                          left join T_BASE_USERINFO u
                            on t.reality_man = u.user_id
                          left join T_BASE_USERINFO b
                            on t.operat_man = b.user_id
                          left join T_SC_PLANGOODS p
                            on t.goods_id = p.goods_id
                           and t.palan_id = p.plan_id
                          left join T_SC_PROCESS_PLAN pl
                            on t.palan_id = pl.plan_id
   left join  T_SYS_DICTIONARY_TAB ut
   on  t.UNIT = ut.VC_CHAR_VALUE and ut.Vc_Dictionary_Type = 'vc_uom'
");
            strSql.Append(" where 1= 1");
            if (!string.IsNullOrEmpty(model.GOODS_ID))
            {
                strSql.Append(" and t.GOODS_ID ='" + model.GOODS_ID + "'");
            }
            if (!string.IsNullOrEmpty(model.PALAN_ID))
            {
                strSql.Append(" and t.PALAN_ID='" + model.PALAN_ID + "'");
            }
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }
        #endregion

        #region 新增一条
        /// <summary>
        /// 新增物料领取数据
        /// </summary>
        /// <param name="model"></param>
        public void AddMaterialByModel(WorkingTaskModel model)
        {
            string sqlInsert = string.Format(@"insert into T_SC_GET_MATERIAL(PALAN_ID,GOODS_ID,MATERIAL_ID,STAND_NUM,MAX_NUM,REALITY_NUM,LOSS_PERCENT,REALITY_MAN,
OPERAT_MAN,OPERAT_DATE,REMARK,UNIT,PRICE,REALITY_DATE,GOODS_NUM)values ('{0}','{1}','{2}',{3},
'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')",

        model.PALAN_ID,
           model.GOODS_ID,
           model.MATERIAL_ID,
          model.STAND_NUM,
            model.MAX_NUM,
          model.REALITY_NUM,
            model.LOSS_PERCENT,
            model.REALITY_MAN,
           model.OPERAT_MAN,
            model.OPERAT_DATE,
        model.REMARK,
        model.UNIT.Trim(),
          model.PRICE,
            model.REALITY_DATE,
            model.GOODS_NUM
                );
            SystemDataObject.Instance.ExecuteSql(sqlInsert);
        }
        #endregion


        #region 新增多条
        /// <summary>
        /// 新增集合
        /// </summary>
        /// <param name="list"></param>
        public void AddMaterialList(List<WorkingTaskModel> list)
        {
            StringBuilder strSql = new StringBuilder();
            List<string> strlist = new List<string>();
            List<OracleParameter[]> strparam = new List<OracleParameter[]>();

            foreach (WorkingTaskModel item in list)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into T_SC_GET_MATERIAL(");
                strSql.Append("PALAN_ID,GOODS_ID,MATERIAL_ID,STAND_NUM,MAX_NUM,REALITY_NUM,LOSS_PERCENT,REALITY_MAN,OPERAT_MAN,OPERAT_DATE,REMARK,UNIT,PRICE,REALITY_DATE,GOODS_NUM)");
                strSql.Append(" values (");
                strSql.Append(":PALAN_ID,:GOODS_ID,:MATERIAL_ID,:STAND_NUM,:MAX_NUM,:REALITY_NUM,:LOSS_PERCENT,:REALITY_MAN,:OPERAT_MAN,:OPERAT_DATE,:REMARK,:UNIT,:PRICE,:REALITY_DATE,:GOODS_NUM)");
                OracleParameter[] parameters = {
                    new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":STAND_NUM", OracleDbType.Decimal,2),
                    new OracleParameter(":MAX_NUM", OracleDbType.Decimal,2),
                    new OracleParameter(":REALITY_NUM", OracleDbType.Decimal,2),
                    new OracleParameter(":LOSS_PERCENT", OracleDbType.Decimal,2),
                    new OracleParameter(":REALITY_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_DATE", OracleDbType.Date),
                    new OracleParameter(":REMARK", OracleDbType.Varchar2,200),
                    new OracleParameter(":UNIT", OracleDbType.Char,20),
                    new OracleParameter(":PRICE", OracleDbType.Decimal,2),
                   new OracleParameter(":REALITY_DATE",OracleDbType.Date),
                   new OracleParameter(":GOODS_NUM", OracleDbType.Decimal,4) };
                parameters[0].Value = item.PALAN_ID;
                parameters[1].Value = item.GOODS_ID;
                parameters[2].Value = item.MATERIAL_ID;
                parameters[3].Value = item.STAND_NUM;
                parameters[4].Value = item.MAX_NUM;
                parameters[5].Value = item.REALITY_NUM;
                parameters[6].Value = item.LOSS_PERCENT;
                parameters[7].Value = item.REALITY_MAN;
                parameters[8].Value = item.OPERAT_MAN;
                parameters[9].Value = item.OPERAT_DATE;
                parameters[10].Value = item.REMARK;
                parameters[11].Value = item.UNIT;
                parameters[12].Value = item.PRICE;
                parameters[13].Value = item.REALITY_DATE;
                parameters[14].Value = item.GOODS_NUM;
                strlist.Add(strSql.ToString());
                strparam.Add(parameters);
            }
            SystemDataObject.Instance.ExecuteList(strlist, strparam);
        }



        #endregion



        #region 更新一条
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model"></param>
        public void UpdateMaterialByModel(WorkingTaskModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update T_SC_GET_MATERIAL set ");
            strSql.Append("PALAN_ID=:PALAN_ID,");
            strSql.Append("GOODS_ID=:GOODS_ID,");
            strSql.Append("MATERIAL_ID=:MATERIAL_ID,");
            strSql.Append("STAND_NUM=:STAND_NUM,");
            strSql.Append("MAX_NUM=:MAX_NUM,");
            strSql.Append("REALITY_NUM=:REALITY_NUM,");
            strSql.Append("LOSS_PERCENT=:LOSS_PERCENT,");
            strSql.Append("REALITY_MAN=:REALITY_MAN,");
            strSql.Append("OPERAT_MAN=:OPERAT_MAN,");
            strSql.Append("OPERAT_DATE=:OPERAT_DATE,");
            strSql.Append("REMARK=:REMARK,");
            strSql.Append("UNIT=:UNIT,");
            strSql.Append("PRICE=:PRICE,");
            strSql.Append("REALITY_DATE=:REALITY_DATE,");
            strSql.Append("GOODS_NUM=:GOODS_NUM");

            strSql.Append(" where PALAN_ID=:PALAN_ID and GOODS_ID=:GOODS_ID and MATERIAL_ID=:MATERIAL_ID ");
            OracleParameter[] parameters = {
                    new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":STAND_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":MAX_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":REALITY_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":LOSS_PERCENT", OracleDbType.Decimal,4),
                    new OracleParameter(":REALITY_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_DATE", OracleDbType.Date),
                    new OracleParameter(":REMARK",OracleDbType.Varchar2,50),
                    new OracleParameter(":UNIT", OracleDbType.Char,2),
                    new OracleParameter(":PRICE", OracleDbType.Decimal,4),
                    new OracleParameter(":REALITY_DATE", OracleDbType.Date),
                    new OracleParameter(":GOODS_NUM", OracleDbType.Decimal,4),
                   new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50)
                                           };
            parameters[0].Value = model.PALAN_ID;
            parameters[1].Value = model.GOODS_ID;
            parameters[2].Value = model.MATERIAL_ID;
            parameters[3].Value = model.STAND_NUM;
            parameters[4].Value = model.MAX_NUM;
            parameters[5].Value = model.REALITY_NUM;
            parameters[6].Value = model.LOSS_PERCENT;
            parameters[7].Value = model.REALITY_MAN;
            parameters[8].Value = model.OPERAT_MAN;
            parameters[9].Value = model.OPERAT_DATE;
            parameters[10].Value = model.REMARK;
            parameters[11].Value = model.UNIT;
            parameters[12].Value = model.PRICE;
            parameters[13].Value = model.REALITY_DATE;
            parameters[14].Value = model.GOODS_NUM;
            parameters[15].Value = model.PALAN_ID;
            parameters[16].Value = model.GOODS_ID;
            parameters[17].Value = model.MATERIAL_ID;
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion

        #region 更新多条
        /// <summary>
        /// 更新集合
        /// </summary>
        /// <param name="list"></param>
        public void UpdateMaterialList(List<WorkingTaskModel> list)
        {
            StringBuilder strSql = new StringBuilder();
            List<string> strlist = new List<string>();
            List<OracleParameter[]> strparam = new List<OracleParameter[]>();

            foreach (WorkingTaskModel item in list)
            {
                strSql = new StringBuilder();
                strSql.Append("update T_SC_GET_MATERIAL set ");
                strSql.Append("PALAN_ID=:PALAN_ID,");
                strSql.Append("GOODS_ID=:GOODS_ID,");
                strSql.Append("MATERIAL_ID=:MATERIAL_ID,");
                strSql.Append("STAND_NUM=:STAND_NUM,");
                strSql.Append("MAX_NUM=:MAX_NUM,");
                strSql.Append("REALITY_NUM=:REALITY_NUM,");
                strSql.Append("LOSS_PERCENT=:LOSS_PERCENT,");
                strSql.Append("REALITY_MAN=:REALITY_MAN,");
                strSql.Append("OPERAT_MAN=:OPERAT_MAN,");
                strSql.Append("OPERAT_DATE=:OPERAT_DATE,");
                strSql.Append("REMARK=:REMARK,");
                strSql.Append("UNIT=:UNIT,");
                strSql.Append("PRICE=:PRICE,");
                strSql.Append("GOODS_NUM=:GOODS_NUM,");
                strSql.Append("REALITY_DATE=:REALITY_DATE");
                strSql.Append(" where PALAN_ID=:PALAN_ID and GOODS_ID=:GOODS_ID and MATERIAL_ID=:MATERIAL_ID ");
                OracleParameter[] parameters = {
                    new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":STAND_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":MAX_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":REALITY_NUM", OracleDbType.Decimal,4),
                    new OracleParameter(":LOSS_PERCENT", OracleDbType.Decimal,4),
                    new OracleParameter(":REALITY_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_MAN",OracleDbType.Varchar2,50),
                    new OracleParameter(":OPERAT_DATE", OracleDbType.Date),
                    new OracleParameter(":REMARK",OracleDbType.Varchar2,50),
                    new OracleParameter(":UNIT", OracleDbType.Char),
                    new OracleParameter(":PRICE", OracleDbType.Decimal,4),
                   new OracleParameter(":GOODS_NUM",OracleDbType.Decimal),
                   new OracleParameter(":REALITY_DATE",OracleDbType.Date),
                   new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50)};
                parameters[0].Value = item.PALAN_ID;
                parameters[1].Value = item.GOODS_ID;
                parameters[2].Value = item.MATERIAL_ID;
                parameters[3].Value = item.STAND_NUM;
                parameters[4].Value = item.MAX_NUM;
                parameters[5].Value = item.REALITY_NUM;
                parameters[6].Value = item.LOSS_PERCENT;
                parameters[7].Value = item.REALITY_MAN;
                parameters[8].Value = item.OPERAT_MAN;
                parameters[9].Value = item.OPERAT_DATE;
                parameters[10].Value = item.REMARK;
                parameters[11].Value = item.UNIT;
                parameters[12].Value = item.PRICE;
                parameters[13].Value = item.GOODS_NUM;
                parameters[14].Value = item.REALITY_DATE;
                parameters[15].Value = item.PALAN_ID;
                parameters[16].Value = item.GOODS_ID;
                parameters[17].Value = item.MATERIAL_ID;
                strlist.Add(strSql.ToString());
                strparam.Add(parameters);
                strSql.Clear();
            }
            SystemDataObject.Instance.ExecuteList(strlist, strparam);
        }




        #endregion


        #region 删除一条
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="model"></param>
        public void DeleteMaterialByModel(WorkingTaskModel model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_SC_GET_MATERIAL ");
            strSql.Append(" where PALAN_ID=:PALAN_ID and GOODS_ID=:GOODS_ID and MATERIAL_ID=:MATERIAL_ID ");
            OracleParameter[] parameters = {
                    new OracleParameter(":PALAN_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
                    new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50)          };
            parameters[0].Value = model.PALAN_ID;
            parameters[1].Value = model.GOODS_ID;
            parameters[2].Value = model.MATERIAL_ID;

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

    }
}