using System;
using System.Collections.Generic;
using System.Text;
using Model;
using Common;
using System.Data;

namespace BLL
{
    public class DALErpOutDetail : IDALErpOutDetail
    {
        public IList<StorePalno> GetList(string OrdNo)
        {
            try
            {
                IList<StorePalno> ls = new List<StorePalno>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from View_ErpOutDetail ");


                if (!string.IsNullOrEmpty(OrdNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("OrdNo = @OrdNo ");
                    para.Add(new SqlParam("@OrdNo", OrdNo));
                }


                SqlParam[] param = null;
                if (para != null)
                    param = para.ToArray();

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, param);
                ls = ModelConvertHelper<StorePalno>.DataReaderToModel(dt);

                return ls;
            }
            catch
            { 
            throw new NotImplementedException();
            }
        }

        public IList<ErpOutDetail> GetList(AjaxErpOutInfo Json,ref PageInfo page)
        {
            try
            {
                IList<ErpOutDetail> list = new List<ErpOutDetail>();
                StringBuilder strSql = new StringBuilder();
                List<SqlParam> para = new List<SqlParam>();
                strSql.Append("Select * from View_ErpOutDetail ");

                if (Json.OrdNo != null && Json.OrdNo != "")
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("OrdNo  =  @OrdNo ");
                    para.Add(new SqlParam("@OrdNo", Json.OrdNo));
                }


                SqlParam[] param = null;
                if (para != null)
                    param = para.ToArray();

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "[dbo].PadLeft(PageNo,'0',3)", "ASC", ref page);
                list = ModelConvertHelper<ErpOutDetail>.DataTableToModel(dt);

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public ErpMat GetModel(string RoleNum)
        {
            try
            {
                ErpMat us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select RoleNum,RoleName,IsDel,Demo from View_GetRoles where ");
                strSql.Append("RoleNum = @RoleNum ");
                strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@RoleNum", RoleNum),
                };

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);

                us = ModelConvertHelper<ErpMat>.ReaderToModel(dt);

                return us;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }


        public bool Add(List<StorePalno> EMats)
        {
            bool result = false;
            try
            {
                //IList<ErpMat> ls = new List<ErpMat>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                //lijiangang修改：增加合格证Certificate字段
                strSql.Append(" Insert into ErpOutDetail (OrdNo,MatNo,Palno,Addre,PlanQuant,CurQuant,Demo,Lever,Ling_No,Tu_No,Certificate,Ye_No) values ");


                for (int i = 0; i < EMats.Count; i++)
                {
                    strSql.Append("(@OrdNo" + i + ",");
                    para.Add(new SqlParam("@OrdNO" + i, EMats[i].OrdNo));

                    strSql.Append("@MatNo" + i + ",");
                    para.Add(new SqlParam("@MatNo" + i, EMats[i].MatNo));

                    strSql.Append("@Palno" + i + ",");
                    para.Add(new SqlParam("@Palno" + i, EMats[i].Palno));

                    strSql.Append("@Addre" + i + ",");
                    para.Add(new SqlParam("@Addre" + i, EMats[i].Addre));
                    //更改货位状态为待出库
                    StringBuilder Sql = new StringBuilder();
                    Sql.Append("update DepotsLocation set TurnoverDemand='04' where LocationCode='"+ EMats[i].Addre + "'");
                    DataFactory.SqlDataBase().ExecuteBySql(Sql);
                    strSql.Append("@PlanQuant" + i + ",");
                    para.Add(new SqlParam("@PlanQuant" + i, EMats[i].PlanQuant));

                    strSql.Append("@CurQuant" + i + ",");
                    para.Add(new SqlParam("@CurQuant" + i, EMats[i].CurQuant));

                    strSql.Append("@Demo" + i + ",");
                    para.Add(new SqlParam("@Demo" + i, EMats[i].Demo));

                    strSql.Append("@Lever" + i + ",");
                    para.Add(new SqlParam("@Lever" + i, EMats[i].Lever));

                    strSql.Append("@Ling_No" + i + ",");
                    para.Add(new SqlParam("@Ling_No" + i, EMats[i].Ling_No));

                    strSql.Append("@Tu_No" + i + ",");
                    para.Add(new SqlParam("@Tu_No" + i, EMats[i].Tu_No));
                    strSql.Append("@Certificate" + i + ",");
                    para.Add(new SqlParam("@Certificate" + i, EMats[i].Certificate));

                    strSql.Append("@Ye_No" + i + "),");
                    para.Add(new SqlParam("@Ye_No" + i, EMats[i].Ye_No));
                }

                SqlParam[] param = null;
                if (para != null)
                {

                    strSql.Remove(strSql.Length-1, 1);
                    param = para.ToArray();
                }
               int dt = DataFactory.SqlDataBase().ExecuteBySql(strSql, param);

               
                if (EMats.Count == dt ) result = true;

                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(List<StorePalno> model)
        {
            bool result = false;
            try
            {

                if (!this.Delete(model)) return false;

                if (!this.Add(model)) return false;

                return true;

            }
            catch
            {
                return result;
            }
        }
        public bool IsExist(string name, string value)
        {
            bool result = false;
            try
            {
                string[] para = new string[] { value };
                int dt = DataFactory.SqlDataBase().IsExist("Roles", name, para);
                if (dt > 0) result = true;
                return result;
            }
            catch
            {
                return result;
            }

        }

        public DataTable GetDataTable(string[] strWhere)
        {
            DataTable dt = null;
            try
            {
                int index = 0;
                string str = "@RoleNum" + index;
                SqlParam[] param = new SqlParam[strWhere.Length];
                StringBuilder sql = new StringBuilder();
                sql.Append("Select RoleNum as 角色编号, RoleName as 角色名,CreatUser as 创建人,CreatTime as 创建时间,UpdateUser as 更新人,UpdateTime as 更新时间,Demo as 备注 FROM View_GetRoles where RoleNum in (");

                for (int i = 0; i < param.Length - 1; i++)
                {
                    string obj2 = strWhere[i];
                    str = "@RoleNum" + index;
                    sql.Append(str).Append(",");
                    param[index] = new SqlParam(str, obj2);
                    index++;
                }
                str = "@RoleNum" + index;
                sql.Append(str);
                param[index] = new SqlParam(str, strWhere[index]);
                sql.Append(")");

                dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");

                return dt;

            }
            catch
            {
                throw new NotImplementedException();
            }

        }

        public bool Delete(List<StorePalno> EMats)
        {
            bool result = false;
            try
            {
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                StringBuilder sp1 = new StringBuilder();
                StringBuilder sp2 = new StringBuilder();

                strSql.Append(" DELETE FROM ErpOutDetail where " );

                int i;
                sp1.Append( " OrdNo =  " );
               // sp2.Append("  MatNo in ( ");
                for ( i= 0; i < EMats.Count; i++)
                {
                    if (i == 0)
                    {
                        sp1.Append(" @OrdNo ");
                        para.Add(new SqlParam("@OrdNO", EMats[i].OrdNo));
                    }

                    //sp2.Append("@MatNo" + i + ",");
                    //para.Add(new SqlParam("@MatNo" + i, EMats[i].MatNo));
                }



                if (i > 0)
                {
                   // sp1.Append(" and  ");
                   // sp2.Append(" )");
                    strSql.Append(sp1);
                   
                    //strSql.Append(sp2.Remove(sp2.Length - 3, 1));

                    SqlParam[] param = null;
                    param = para.ToArray();
                  
                  
                    int dt = DataFactory.SqlDataBase().ExecuteBySql(strSql, param);


                    if (EMats.Count == dt ) result = true;

                }
                return true;
            }
            catch
            {
                return result;
            }
        }

        public DataTable GetPrintData(string OrdNo)
        {
            DataTable ds = null;
            try
            {
                //lijiangang修改：增加合格证Certificate字段
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select LingNo,LocationCode,Palno,MatNo,MatName,PackFormat,Unit,CurQuant,CurQuant,Demo,tuno,PageNo,Certificate from View_ErpOutDetail where ");
                strSql.Append("OrdNo = @OrdNo ");
                strSql.Append(" Order by [dbo].PadLeft(Ye_No,'0',3) asc ");

                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@OrdNo", OrdNo),
                };

                ds = DataFactory.SqlDataBase().GetDataTableBySQL(strSql, para, "Table2");

                return ds;
            }
            catch
            {
                return ds;
            }
        }
    }
}