using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Common;
using Model;
namespace BLL
{
    public class DALPurchase : IDALPurchase
    {
        public IList<Purchase> GetList()
        {
            try
            {
                IList<Purchase> ls = new List<Purchase>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select RoleName from Roles where IsDel !=1");

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

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

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

                if (!string.IsNullOrEmpty(Json.PurchaseNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("PurchaseNo like '%' + @PurchaseNo + '%' ");
                    para.Add(new SqlParam("@PurchaseNo", Json.PurchaseNo));
                }

                if (!string.IsNullOrEmpty(Json.ItemNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("ItemNo like '%' + @ItemNo + '%' ");
                    para.Add(new SqlParam("@ItemNo", Json.ItemNo));
                }

                if (!string.IsNullOrEmpty(Json.ItemName))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("ItemName like '%' + @ItemName + '%' ");
                    para.Add(new SqlParam("@ItemName", Json.ItemName));
                }

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

                if (Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MaxValue)
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("CreateTime >= @BeCreateTime ");
                    para.Add(new SqlParam("@BeCreateTime", Convert.ToDateTime(Json.BeCreateTime)));
                }

                if (Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MaxValue)
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("CreateTime <= @EnCreateTime ");
                    para.Add(new SqlParam("@EnCreateTime", Convert.ToDateTime(Json.EnCreateTime)));
                }

                if (Json.BeFinishTime != DateTime.MinValue && Json.BeFinishTime != null && Json.BeFinishTime != DateTime.MaxValue)
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("FinishTime >= @BeFinishTime ");
                    para.Add(new SqlParam("@BeFinishTime", Convert.ToDateTime(Json.BeFinishTime)));
                }

                if (Json.EnFinishTime != DateTime.MinValue && Json.EnFinishTime != null && Json.EnFinishTime != DateTime.MaxValue)
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("FinishTime <= @EnFinishTime ");
                    para.Add(new SqlParam("@EnFinishTime", Convert.ToDateTime(Json.EnFinishTime)));
                }


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

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "desc", ref page);
                list = ModelConvertHelper<Purchase>.DataTableToModel(dt);

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

        }
        public IList<Purchase> GetList(string PurchaseNo)
        {
            try
            {

                IList<Purchase> list = new List<Purchase>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select top 5 * from View_Purchase where ");
                strSql.Append("PurchaseNo like '%' + @PurchaseNo + '%' order by PurchaseNo");
                //strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@PurchaseNo", PurchaseNo),
                };

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql, para, "");

                list = ModelConvertHelper<Purchase>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
        public Purchase GetModel(string PurchaseNo)
        {
            try
            {

                Purchase us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select * from View_Purchase where ");
                strSql.Append("PurchaseNo like '%' + @PurchaseNo + '%' ");
                //strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@PurchaseNo", PurchaseNo),
                };

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

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

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

        public Purchase GetModel(string PurchaseNo, ref IList<PurchaseMat> PurchaseMats)
        {
            try
            {


                Purchase us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select * from View_Purchase where ");
                strSql.Append("PurchaseNo = @PurchaseNo ");
                //strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@PurchaseNo", PurchaseNo),
                };

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

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

                IDALPurchaseDetail PurchaseDetail = new DALPurchaseDetail();
                PurchaseMats = PurchaseDetail.GetList(PurchaseNo);


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


        public bool Add(Purchase model, List<PurchaseMat> Mats)
        {
            bool result = false;
            try
            {
                Hashtable ht1 = new Hashtable();
                Hashtable rs = new Hashtable();
                ht1["sCode"] = "BDNO";
                ht1["OUT_sResult"] = "";


                int num = DataFactory.SqlDataBase().ExecuteByProcReturn("GetSerialNo", ht1, ref rs);

                if (num == 0) return result;
                model.PurchaseNo = rs["OUT_sResult"].ToString();

                foreach (PurchaseMat erp in Mats)
                {
                    erp.PurchaseNo = model.PurchaseNo;
                }
                IDALPurchaseDetail dALErp = new DALPurchaseDetail();
                bool value = dALErp.Add(Mats);

                Hashtable ht = new Hashtable();

                ht["PurchaseNo"] = "'" + model.PurchaseNo + "'";
                ht["ItemNo"] = "'" + model.ItemNo + "'";
                ht["ItemName"] = "'" + model.ItemName + "'";
                ht["CreateUser"] = "'" + model.CreateUser + "'";
                ht["FinishTime"] = "'" + model.FinishTime + "'";

                ht["AuditFlag"] = "'" + "AD01" + "'";
                ht["AuditUser"] = "'" + "" + "'";

                ht["Demo"] = "'" + model.Demo + "'";
                ht["Demo1"] = "'" + model.Demo1 + "'";
                ht["Demo2"] = "'" + model.Demo2 + "'";

                int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("Purchase", ht);

                if (_ret == 1) result = true;

                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(Purchase model, List<PurchaseMat> Mats)
        {
            bool result = false;
            try
            {
                foreach (PurchaseMat erp in Mats)
                {
                    erp.PurchaseNo = model.PurchaseNo;
                }
                IDALPurchaseDetail dALErp = new DALPurchaseDetail();
                if (!dALErp.Update(Mats)) return false;
                Hashtable ht = new Hashtable();

                //ht["PurchaseNo"] = string.IsNullOrEmpty(model.PurchaseNo) ? "''" : "'" + model.PurchaseNo + "'";
                //ht["ItemNo"] = string.IsNullOrEmpty(model.ItemNo) ? "''" : "'" + model.ItemNo + "'";
                //ht["ItemName"] = string.IsNullOrEmpty(model.ItemName) ? "''" : "'" + model.ItemName + "'";
                //ht["CreateUser"] = string.IsNullOrEmpty(model.ItemName) ? "''" : "'" + model.CreateUser + "'";
                //ht["CreateUser"] = "Getdate()";

                ht["AuditFlag"] = "'" + "AD01" + "'";
                ht["AuditUser"] = "'" + "" + "'";
                ht["AuditTime"] = "'" + "" + "'";
                ht["Demo"] = "'" + model.Demo + "'";
                //ht["Demo1"] = "'" + model.Demo1 + "'";
                //ht["Demo2"] = "'" + model.Demo2 + "'";

                string PurchaseNo = "'" + model.PurchaseNo + "'";

                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("Purchase", nameof(model.PurchaseNo), PurchaseNo, ht);

                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }
        }

        public bool UpdateAudit(string[] PurchaseNo, string AuditFlag, string User)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();

                ht["AuditFlag"] = "'" + AuditFlag + "'";
                ht["AuditUser"] = "'" + User + "'";
                ht["AuditTime"] = "getdate()";

                int _ret = DataFactory.SqlDataBase().UpdateByHashtableA("Purchase", nameof(PurchaseNo), PurchaseNo, ht);
                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }
        }


        public bool UpdateClose(string[] OrdNo, string Statu)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();
                ht["Statu"] = "01";

                int dt = DataFactory.SqlDataBase().IsExist("Purchase", "Purchase", OrdNo, ht);
                if (dt >= OrdNo.Length)
                {
                    int _ret = DataFactory.SqlDataBase().BatchDeleteData("Erp_In", "OrdNo", OrdNo);
                    if (_ret >= OrdNo.Length) result = true;
                }
                return result;

            }
            catch
            {
                return result;
            }
        }
        public bool IsExist(string name, string value)
        {
            bool result = false;
            try
            {
                string[] para = new string[] { value };
                int dt = DataFactory.SqlDataBase().IsExist("ErpIn", 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 BatchDelete(string[] OrdNo)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();
                ht["Statu"] = "01";

                int dt = DataFactory.SqlDataBase().IsExist("ErpIn", "OrdNo", OrdNo, ht);
                if (dt >= OrdNo.Length)
                {
                    int _ret = DataFactory.SqlDataBase().BatchDeleteData("Erp_In", "OrdNo", OrdNo);
                    if (_ret >= OrdNo.Length)
                    {
                        int dt1 = DataFactory.SqlDataBase().IsExist("ErpInDetail", "OrdNo", OrdNo);
                        if (dt1 > 0)
                        {
                            int _ret1 = DataFactory.SqlDataBase().BatchDeleteData("ErpInDetail", "OrdNo", OrdNo);
                            if (_ret1 >= dt1) result = true;
                        }
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
        }

        public DataSet GetPrintData(string PurchaseNo)
        {
            DataSet ds = new DataSet(); ;
            try
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select * from View_Purchase where ");
                strSql.Append("PurchaseNo = @PurchaseNo ");

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



                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql, para, "Table1");
                if (dt != null) ds.Tables.Add(dt);

                DALPurchaseDetail purDetail = new DALPurchaseDetail();
                DataTable dts = purDetail.GetPrintData(PurchaseNo);
                if (dts != null) ds.Tables.Add(dts);

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


    public class DALPurchaseDetail : IDALPurchaseDetail
    {
        /// <summary>
        /// 出库单选择物料
        /// </summary>
        /// <param name="selMatList">查询条件</param>
        /// <param name="page">分页信息</param>
        /// <returns>返回库存中物料信息</returns>
        public IList<SelMatList> GetList(SelMatList selMatList, ref PageInfo page)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from View_SelMatStore where 1=1 " + $" AND DepartGuid='{selMatList.DepartGuid}' ");

                List<SqlParam> para = new List<SqlParam>();
                if (!string.IsNullOrEmpty(selMatList.MatNo))
                {
                    strSql.Append("and MatNo like '%' + @MatNo + '%' ");
                    para.Add(new SqlParam("@MatNo", selMatList.MatNo));
                }
                if (!string.IsNullOrEmpty(selMatList.MatName))
                {
                    strSql.Append("and MatName like '%' + @MatName + '%' ");
                    para.Add(new SqlParam("@MatName", selMatList.MatName));
                }
                if (!string.IsNullOrEmpty(selMatList.BatchNo))
                {
                    strSql.Append("and BatchNo like '%' + @BatchNo + '%' ");
                    para.Add(new SqlParam("@BatchNo", selMatList.BatchNo));
                }
                if (!string.IsNullOrEmpty(selMatList.LingNo))
                {
                    strSql.Append("and LingNo like '%' + @LingNo + '%' ");
                    para.Add(new SqlParam("@LingNo", selMatList.LingNo));
                }

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

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "MatNo", "ASC", ref page);
                return ModelConvertHelper<SelMatList>.DataTableToModel(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public IList<PurchaseMat> GetList(string PurchaseNo, ref PageInfo page)
        {
            try
            {
                IList<PurchaseMat> ls = new List<PurchaseMat>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from view_PurchaseDetail ");


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


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

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "MatNo", "ASC", ref page);
                ls = ModelConvertHelper<PurchaseMat>.DataTableToModel(dt);

                return ls;


            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public IList<PurchaseMat> GetList(string PurchaseNo)
        {
            try
            {
                IList<PurchaseMat> ls = new List<PurchaseMat>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from view_PurchaseDetail ");


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


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

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

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

        public IList<PurchaseMat> GetList(AjaxPurchaseMat PurMat, ref PageInfo page)
        {
            try
            {
                IList<PurchaseMat> ls = new List<PurchaseMat>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  * from (select Guid,MatNo,MatName,PackFormat,Unit as Unit, Demo as Demo_21,null as Quant_Unit, " +
                    "null as Quant_Total,null as Quant_Buy,null as PurchaseNo ,null as ItemNo,null as ItemName, MatType from View_Material where isdel = 0  union all " +
                    "select Guid,MatNo, MatName, PackFormat, Unit, Demo_21, Quant_Unit, Quant_Total, Quant_Buy, PurchaseNo, ItemNo, ItemName, null As MatType from View_PurchaseDetail ) as A ");

                if (!string.IsNullOrEmpty(PurMat.PurchaseNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("PurchaseNo like '%' + @PurchaseNo + '%' ");
                    para.Add(new SqlParam("@PurchaseNo", PurMat.PurchaseNo));
                }

                if (!string.IsNullOrEmpty(PurMat.ItemNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("ItemNo like '%' + @ItemNo + '%'  ");
                    para.Add(new SqlParam("@ItemNo", PurMat.ItemNo));
                }

                if (!string.IsNullOrEmpty(PurMat.ItemName))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("ItemName like '%' + @ItemName + '%'  ");
                    para.Add(new SqlParam("@ItemName", PurMat.ItemName));
                }

                if (!string.IsNullOrEmpty(PurMat.MatNo))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("MatNo like '%' + @MatNo + '%'  ");
                    para.Add(new SqlParam("@MatNo", PurMat.MatNo));
                }
                if (!string.IsNullOrEmpty(PurMat.MatName))
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("MatName like '%' + @MatName + '%'  ");
                    para.Add(new SqlParam("@MatName", PurMat.MatName));
                }

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

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "MatNo", "ASC", ref page);
                ls = ModelConvertHelper<PurchaseMat>.DataTableToModel(dt);

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

        public bool Add(List<PurchaseMat> pMats)
        {
            bool result = false;
            try
            {
                //IList<ErpMat> ls = new List<ErpMat>();
                List<SqlParam> para = new List<SqlParam>();
                StringBuilder strSql = new StringBuilder();

                strSql.Append(" Insert into PurchaseDetail ( PurchaseNo,MatNo,Quant_Unit,Quant_Total,Quant_Buy,BuyUser,Demo,Demo1,Demo2 ) values ");


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

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

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

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

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

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

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

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

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


                }

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

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

                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(List<PurchaseMat> 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<PurchaseMat> 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 PurchaseDetail where ");

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

                    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 (dt > -1) result = true;

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

        public DataTable GetPrintData(string PurchaseNo)
        {
            DataTable ds = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" Select * from View_PurchaseDetail where ");
                strSql.Append("PurchaseNo = @PurchaseNo ");

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



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

                return ds;
            }
            catch
            {
                return ds;
            }
        }


    }
}