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 System.Data.SqlClient;

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,
                               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
");
            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)
        {
            StringBuilder 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)");
            SqlParameter[] parameters = {
					new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,50),
					new SqlParameter("@STAND_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@MAX_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@LOSS_PERCENT", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_DATE", SqlDbType.Date),
					new SqlParameter("@REMARK", SqlDbType.VarChar,50),
					new SqlParameter("@UNIT", SqlDbType.Char),
					new SqlParameter("@PRICE", SqlDbType.Decimal,4),
                   new SqlParameter("@REALITY_DATE",SqlDbType.Date),                      
                   new SqlParameter("@GOODS_NUM", SqlDbType.Decimal,4)                    
                   
                                           
                                           };
            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;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        #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<SqlParameter[]> strparam = new List<SqlParameter[]>();

            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)");
                SqlParameter[] parameters = {
					new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,50),
					new SqlParameter("@STAND_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@MAX_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@LOSS_PERCENT", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_DATE", SqlDbType.Date),
					new SqlParameter("@REMARK", SqlDbType.VarChar,50),
					new SqlParameter("@UNIT", SqlDbType.Char),
					new SqlParameter("@PRICE", SqlDbType.Decimal,4),
                   new SqlParameter("@REALITY_DATE",SqlDbType.Date),
                   new SqlParameter("@GOODS_NUM", SqlDbType.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 ");
			SqlParameter[] parameters = {
					new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,50),
					new SqlParameter("@STAND_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@MAX_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@LOSS_PERCENT", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_DATE", SqlDbType.Date),
					new SqlParameter("@REMARK", SqlDbType.VarChar,50),
					new SqlParameter("@UNIT", SqlDbType.Char,2),
					new SqlParameter("@PRICE", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_DATE", SqlDbType.Date),
					new SqlParameter("@GOODS_NUM", SqlDbType.Decimal,4),
                   new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,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<SqlParameter[]> strparam = new List<SqlParameter[]>();

            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 ");
                SqlParameter[] parameters = {
					new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,50),
					new SqlParameter("@STAND_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@MAX_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_NUM", SqlDbType.Decimal,4),
					new SqlParameter("@LOSS_PERCENT", SqlDbType.Decimal,4),
					new SqlParameter("@REALITY_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_MAN", SqlDbType.VarChar,50),
					new SqlParameter("@OPERAT_DATE", SqlDbType.Date),
					new SqlParameter("@REMARK", SqlDbType.VarChar,50),
					new SqlParameter("@UNIT", SqlDbType.Char),
					new SqlParameter("@PRICE", SqlDbType.Decimal,4),
                   new SqlParameter("@GOODS_NUM",SqlDbType.Decimal),
                   new SqlParameter("@REALITY_DATE",SqlDbType.Date),
                   new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,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 ");
            SqlParameter[] parameters = {
					new SqlParameter("@PALAN_ID", SqlDbType.VarChar,50),
					new SqlParameter("@GOODS_ID", SqlDbType.VarChar,50),
					new SqlParameter("@MATERIAL_ID", SqlDbType.VarChar,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

    }
}