Detail

 using EntityLayer;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

namespace DataAccessLayer

{

    public class PurchaseQuotationDtLDAL : SQLDataAccess

    {

        public List<PurchaseQuotationDtl> PurchaseQuotationDtlSelectNew()

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectNew]";

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.PurQuoNo = reader["PoNo"] is DBNull ? (string)string.Empty : (string)reader["PoNo"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationId.ConversionRateofCurrency = reader["ConversionRateofCurrency"] is DBNull ? (decimal)18 : (decimal)reader["ConversionRateofCurrency"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null)

            {

                return activePurchaseQuotationIdList;

            }

            return null;

        }

        public List<PurchaseQuotationDtl> PurchaseQuotationDtlGetByDate(DateTime FromDate, DateTime ToDate, string ProductName, string VendorName)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationIdDtlGetByDate]";

            cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = FromDate;

            cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = ToDate;

            cmd.Parameters.Add("@ProductName", SqlDbType.VarChar).Value = ProductName;

            cmd.Parameters.Add("@VendorName", SqlDbType.VarChar).Value = VendorName;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.PurQuoNo = reader["PoNo"] is DBNull ? (string)string.Empty : (string)reader["PoNo"];

                    activePurchaseQuotationId.Date = reader["Date"] is DBNull ? (DateTime)DateTime.Now : (DateTime)reader["Date"];

                    activePurchaseQuotationId.VendorName = reader["VendorName"] is DBNull ? (string)string.Empty : (string)reader["VendorName"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null)

            {

                return activePurchaseQuotationIdList;

            }

            return null;

        }

        public List<PurchaseQuotationDtl> PurchaseQuotationDtlSelectByPoNo1(string PoNo,int FinancialYearID)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectByPoNo1]";

            cmd.Parameters.Add("@PurQuoNo", SqlDbType.VarChar).Value = PoNo;

            cmd.Parameters.Add("@FinancialYearID", SqlDbType.Int).Value = FinancialYearID;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductId = reader["ProductId"] is DBNull ? (int)0 : (int)reader["ProductId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.ProGrpName = reader["ProGrpName"] is DBNull ? (string)string.Empty : (string)reader["ProGrpName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (decimal)0 : (decimal)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Price = Convert.ToDecimal(reader["Price"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.PickDataID = reader["PickDataID"] is DBNull ? (int)0 : (int)reader["PickDataID"];

                    activePurchaseQuotationId.PurReqId = reader["PurReqId"] is DBNull ? (int)0 : (int)reader["PurReqId"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (decimal)0 : (decimal)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationId.DiscountPercentage = reader["DiscountPercentage"] is DBNull ? (bool)false : (bool)reader["DiscountPercentage"];

                    activePurchaseQuotationId.CurrencyId = reader["CurrencyId"] is DBNull ? (int)0 : (int)reader["CurrencyId"];

                    activePurchaseQuotationId.CurrencyRate = reader["CurrencyRate"] is DBNull ? (decimal)20 : (decimal)reader["CurrencyRate"];

                    activePurchaseQuotationId.FcAmount = reader["FcAmount"] is DBNull ? (decimal)20 : (decimal)reader["FcAmount"];

                    activePurchaseQuotationId.FcUnitPrice = reader["FcUnitPrice"] is DBNull ? (decimal)20 : (decimal)reader["FcUnitPrice"];

                    activePurchaseQuotationId.DiscountAmount = reader["DiscountAmount"] is DBNull ? (decimal)20 : (decimal)reader["DiscountAmount"];

                    activePurchaseQuotationId.DiscountFc = reader["DiscountFc"] is DBNull ? (decimal)20 : (decimal)reader["DiscountFc"];

                    activePurchaseQuotationId.DiscountFcAmount = reader["DiscountFcAmount"] is DBNull ? (decimal)20 : (decimal)reader["DiscountFcAmount"];

                    activePurchaseQuotationId.AfterDiscountFcAmount = reader["AfterDiscountFcAmount"] is DBNull ? (decimal)20 : (decimal)reader["AfterDiscountFcAmount"];

                    activePurchaseQuotationId.PageNumber = reader["PageNumber"] is DBNull ? (string)string.Empty : (string)reader["PageNumber"];

                    activePurchaseQuotationId.ItemRef = reader["ItemRef"] is DBNull ? (string)string.Empty : (string)reader["ItemRef"];

                    activePurchaseQuotationId.SerialNumber = reader["SerialNumber"] is DBNull ? (string)string.Empty : (string)reader["SerialNumber"];

                    activePurchaseQuotationId.ConversionRateofCurrency = reader["ConversionRateofCurrency"] is DBNull ? (decimal)20 : (decimal)reader["ConversionRateofCurrency"];

                    activePurchaseQuotationId.VATId = reader["VATId"] is DBNull ? 0 : (int)reader["VATId"];

                    activePurchaseQuotationId.VATPercentage = reader["VATPercentage"] is DBNull ? 0 : (decimal)reader["VATPercentage"];

                    activePurchaseQuotationId.VATAmount = reader["VATAmount"] is DBNull ? 0 : (decimal)reader["VATAmount"];

                    activePurchaseQuotationId.AfterVATTotal = reader["AfterVATTotal"] is DBNull ? 0 : (decimal)reader["AfterVATTotal"];

                    activePurchaseQuotationId.AfterVATTotalFC = reader["AfterVATTotal"] is DBNull ? 0 : (decimal)reader["AfterVATTotal"];

                    activePurchaseQuotationId.NoOfStockInUnit = reader["NoOfStockInUnit"] is DBNull ? 0 : (decimal)reader["NoOfStockInUnit"];

                    activePurchaseQuotationId.UnitID = reader["UnitID"] is DBNull ? 0 : (int)reader["UnitID"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList;

            }

            return null;

        }

        public PurchaseQuotationDtl PurchaseQuotationDtlSelectByPONO(string PoNo)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectByPONO]";

            cmd.Parameters.Add("@PoNo", SqlDbType.VarChar).Value = PoNo;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PurReqId = reader["PurReqId"] is DBNull ? (int)0 : (int)reader["PurReqId"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList[0];

            }

            return null;

        }

        public List<PurchaseQuotationDtl> PurchaseQuotationDtlSelectByProductName(string ProductName)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectByProductName]";

            cmd.Parameters.Add("@ProductName", SqlDbType.VarChar).Value = ProductName;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList;

            }

            return null;

        }

        public PurchaseQuotationDtl PurchaseQuotationDtlSelectByProductNameObject(string ProductName)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectByProductName]";

            cmd.Parameters.Add("@ProductName", SqlDbType.VarChar).Value = ProductName;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList[0];

            }

            return null;

        }

        public PurchaseQuotationDtl PurchaseQuotationDtlSelectByProductNameQuoId(string PartNo, int PurchaseQuotationId)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlSelectByProductNameQuoId]";

            cmd.Parameters.Add("@PartNo", SqlDbType.VarChar).Value = PartNo;

            cmd.Parameters.Add("@PurchaseQuotationId", SqlDbType.Int).Value = PurchaseQuotationId;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductId = reader["ProductId"] is DBNull ? (int)0 : (int)reader["ProductId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (int)0 : (int)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.PickDataID = reader["PickDataID"] is DBNull ? (int)0 : (int)reader["PickDataID"];

                    activePurchaseQuotationId.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (int)0 : (int)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PurReqId = reader["PurReqId"] is DBNull ? (int)0 : (int)reader["PurReqId"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList[0];

            }

            return null;

        }

        public PurchaseQuotationDtl PurchaseQuotationDtlGetById(int? Id)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlGetById]";

            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductId = reader["ProductId"] is DBNull ? (int)0 : (int)reader["ProductId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (decimal)0 : (decimal)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = reader["UnitPrice"] is DBNull ? (decimal)20 : Convert.ToDecimal(reader["UnitPrice"]);

                    activePurchaseQuotationId.Discount = reader["Discount"] is DBNull ? (decimal)20 : Convert.ToDecimal(reader["Discount"]);

                    activePurchaseQuotationId.Total = reader["Total"] is DBNull ? (decimal)20 : Convert.ToDecimal(reader["Total"]);

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Remarks = reader["Remarks"] is DBNull ? (string)string.Empty : (string)reader["Remarks"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.PickDataID = reader["PickDataID"] is DBNull ? (int)0 : (int)reader["PickDataID"];

                    activePurchaseQuotationId.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (decimal)0 : (decimal)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PurReqId = reader["PurReqId"] is DBNull ? (int)0 : (int)reader["PurReqId"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null && activePurchaseQuotationIdList.Count > 0)

            {

                return activePurchaseQuotationIdList[0];

            }

            return null;

        }

        public List<PurchaseQuotationDtl> PurchaseQuotationDtlGetByPurchaseQuotationId(int PurchaseQuotationId)

        {

            List<PurchaseQuotationDtl> activePurchaseQuotationIdList = new List<PurchaseQuotationDtl>();

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[PurchaseQuotationDtlGetByPurchaseQuotationId]";

            cmd.Parameters.Add("@PurchaseQuotationId", SqlDbType.Int).Value = PurchaseQuotationId;

            cmd.Connection = con;

            try

            {

                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    PurchaseQuotationDtl activePurchaseQuotationId = new PurchaseQuotationDtl();

                    activePurchaseQuotationId.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];

                    activePurchaseQuotationId.PurchaseQuotationId = reader["PurchaseQuotationId"] is DBNull ? (int)0 : (int)reader["PurchaseQuotationId"];

                    activePurchaseQuotationId.ProductName = reader["ProductName"] is DBNull ? (string)string.Empty : (string)reader["ProductName"];

                    activePurchaseQuotationId.Qty = reader["Qty"] is DBNull ? (decimal)0 : (decimal)reader["Qty"];

                    activePurchaseQuotationId.UnitPrice = reader["UnitPrice"] is DBNull ? (decimal)20 : (decimal)reader["UnitPrice"];

                    activePurchaseQuotationId.UnitName = reader["UnitName"] is DBNull ? (string)string.Empty : (string)reader["UnitName"];

                    activePurchaseQuotationId.Discount = reader["Discount"] is DBNull ? (decimal)20 : (decimal)reader["Discount"];

                    activePurchaseQuotationId.Total = reader["Total"] is DBNull ? (decimal)20 : (decimal)reader["Total"];

                    activePurchaseQuotationId.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];

                    activePurchaseQuotationId.Status = reader["Status"] is DBNull ? (bool)false : (bool)reader["Status"];

                    activePurchaseQuotationId.PickDataID = reader["PickDataID"] is DBNull ? (int)0 : (int)reader["PickDataID"];

                    activePurchaseQuotationId.DiscountQty = reader["DiscountQty"] is DBNull ? (decimal)0 : (decimal)reader["DiscountQty"];

                    activePurchaseQuotationId.DiscountTotal = reader["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)reader["DiscountTotal"];

                    activePurchaseQuotationId.PartNo = reader["PartNo"] is DBNull ? (string)string.Empty : (string)reader["PartNo"];

                    activePurchaseQuotationId.ProductId = reader["ProductId"] is DBNull ? 0 : (int)reader["ProductId"];

                    activePurchaseQuotationIdList.Add(activePurchaseQuotationId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            con.Close();

            if (activePurchaseQuotationIdList != null)

            {

                return activePurchaseQuotationIdList;

            }

            return null;

        }

        public int PurchaseQuotationDtlInsert(PurchaseQuotationDtl activePurchaseQuotationDtl)

        {

            int Id = 0;

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@Id", SqlDbType.Int, 4, ParameterDirection.Output, (object)activePurchaseQuotationDtl.Id ?? DBNull.Value);

                if (activePurchaseQuotationDtl.PurchaseQuotationId > 0)

                {

                    AddParamToSQLCmd(cmd, "@PurchaseQuotationId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurchaseQuotationId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PurchaseQuotationId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                if (activePurchaseQuotationDtl.ProductId > 0)

                {

                    AddParamToSQLCmd(cmd, "@ProductId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ProductId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@ProductId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@ProductName", SqlDbType.VarChar, 500, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ProductName ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Qty", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Qty ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@UnitPrice", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.UnitPrice ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Discount ", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Discount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountPercentage", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountPercentage ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Total ", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Total ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Remarks", SqlDbType.VarChar, 1000, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Remarks ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@UnitName", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.UnitName ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurQuoNo ?? DBNull.Value);

                if (activePurchaseQuotationDtl.PurReqId > 0)

                {

                    AddParamToSQLCmd(cmd, "@PurReqId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurReqId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PurReqId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                if (activePurchaseQuotationDtl.PickDataID > 0)

                {

                    AddParamToSQLCmd(cmd, "@PickDataID", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PickDataID ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PickDataID", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@Status", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Status ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountQty", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountQty ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountTotal ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@PartNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PartNo ?? DBNull.Value);

                if (activePurchaseQuotationDtl.CurrencyId > 0)

                {

                    AddParamToSQLCmd(cmd, "@CurrencyId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.CurrencyId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@CurrencyId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@CurrencyRate", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.CurrencyRate ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@FcAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.FcAmount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@FcUnitPrice", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.FcUnitPrice ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountAmount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountFc", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountFc ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountFcAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountFcAmount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@AfterDiscountFcAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.AfterDiscountFcAmount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@ConversionRateofCurrency ", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ConversionRateofCurrency ?? DBNull.Value);

                if (activePurchaseQuotationDtl.VATId > 0)

                {

                    AddParamToSQLCmd(cmd, "@VATId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.VATId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@VATId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@VATPercentage", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.VATPercentage);

                AddParamToSQLCmd(cmd, "@VATAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.VATAmount);

                AddParamToSQLCmd(cmd, "@AfterVATTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.AfterVATTotal);

                if (activePurchaseQuotationDtl.UnitID > 0)

                {

                    AddParamToSQLCmd(cmd, "@UnitID", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.UnitID ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@UnitID", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@NoOfStockInUnit", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.NoOfStockInUnit ?? DBNull.Value);

                SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationDtlInsert");

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (cmd == null)

                {

                    throw (new ArgumentNullException("sqlCmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    cmd.Connection = cn;

                    cn.Open();

                    cmd.ExecuteNonQuery();

                    Id = (int)cmd.Parameters["@Id"].Value;

                    cn.Close();

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return Id;

        }

        public bool PurchaseQuotationDtlUpdate(PurchaseQuotationDtl activePurchaseQuotationDtl)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@Id", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Id ?? DBNull.Value);

                if (activePurchaseQuotationDtl.PurchaseQuotationId > 0)

                {

                    AddParamToSQLCmd(cmd, "@PurchaseQuotationId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurchaseQuotationId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PurchaseQuotationId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                if (activePurchaseQuotationDtl.ProductId > 0)

                {

                    AddParamToSQLCmd(cmd, "@ProductId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ProductId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@ProductId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@ProductName", SqlDbType.VarChar, 500, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ProductName ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Qty", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Qty ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@UnitPrice", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.UnitPrice ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Discount ", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Discount ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Total ", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Total ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@Remarks", SqlDbType.VarChar, 1000, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Remarks ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@UnitName", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.UnitName ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurQuoNo ?? DBNull.Value);

                if (activePurchaseQuotationDtl.PickDataID > 0)

                {

                    AddParamToSQLCmd(cmd, "@PickDataID", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PickDataID ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PickDataID", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                if (activePurchaseQuotationDtl.PurReqId > 0)

                {

                    AddParamToSQLCmd(cmd, "@PurReqId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PurReqId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@PurReqId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@Status", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activePurchaseQuotationDtl.Status ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountQty", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountQty ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activePurchaseQuotationDtl.DiscountTotal ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@PartNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activePurchaseQuotationDtl.PartNo ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@ConversionRateofCurrency ", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)activePurchaseQuotationDtl.ConversionRateofCurrency ?? DBNull.Value);

                if (activePurchaseQuotationDtl.VATId > 0)

                {

                    AddParamToSQLCmd(cmd, "@VATId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activePurchaseQuotationDtl.VATId ?? DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@VATId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@VATPercentage", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.VATPercentage);

                AddParamToSQLCmd(cmd, "@VATAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.VATAmount);

                AddParamToSQLCmd(cmd, "@AfterVATTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, activePurchaseQuotationDtl.AfterVATTotal);

                SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationDtlUpdate");

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (cmd == null)

                {

                    throw (new ArgumentNullException("sqlCmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    cmd.Connection = cn;

                    cn.Open();

                    cmd.ExecuteNonQuery();

                    cn.Close();

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return true;

        }

        public bool PurchaseQuotationIdDtlDelete(int Id)

        {

            try

            {

                SqlCommand sqlCmd = new SqlCommand();

                sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.CommandText = "[dbo].[PurchaseQuotationDtlDelete]";

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (sqlCmd == null)

                {

                    throw (new ArgumentNullException("sqlCmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    sqlCmd.Connection = cn;

                    cn.Open();

                    sqlCmd.ExecuteNonQuery();

                    cn.Close();

                }

                return true;

            }

            catch (Exception)

            {

                throw;

            }

        }

        public List<PurchaseQuotationDtl> PurchaseQutation(string _VendorAccountCode)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@VendorAccountCode", SqlDbType.Char, 100, ParameterDirection.Input, _VendorAccountCode);

                SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQutation");

                List<PurchaseQuotationDtl> activePurReqList = new List<PurchaseQuotationDtl>();

                TExecuteReaderCmd<PurchaseQuotationDtl>(cmd, TGeneratePurchaseQutation<PurchaseQuotationDtl>, ref activePurReqList);

                if (activePurReqList != null)

                {

                    return activePurReqList;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return null;

        }

        public List<PurchaseQuotationDtl> PurchaseQuationDetail(string _PoNo,int FinancialYearID)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@PoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, _PoNo);

                AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, FinancialYearID);

                SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuationDetail");

                List<PurchaseQuotationDtl> activePurReqList = new List<PurchaseQuotationDtl>();

                TExecuteReaderCmd<PurchaseQuotationDtl>(cmd, TGeneratePurchaseQuationDetailList<PurchaseQuotationDtl>, ref activePurReqList);

                if (activePurReqList != null)

                {

                    return activePurReqList;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return null;

        }

        public void TGeneratePurchaseQutation<T>(SqlDataReader returnData, ref List<PurchaseQuotationDtl> activePurReqList)

        {

            try

            {

                while (returnData.Read())

                {

                    PurchaseQuotationDtl activeReqDtl = new PurchaseQuotationDtl();

                    activeReqDtl.Id = returnData["id"] is DBNull ? (int)0 : (int)returnData["id"];

                    activeReqDtl.PurQuoNo = returnData["PoNo"] is DBNull ? (string)string.Empty : (string)returnData["PoNo"];

                    activeReqDtl.PoNo = returnData["PoNo"] is DBNull ? (string)string.Empty : (string)returnData["PoNo"];

                    activeReqDtl.VendorName = returnData["AccountName"] is DBNull ? (string)string.Empty : (string)returnData["AccountName"];

                    activeReqDtl.StatusNew = returnData["Status"] is DBNull ? (string)string.Empty : (string)returnData["Status"];

                    activeReqDtl.ApprovalStatus = returnData["Status"] is DBNull ? (string)string.Empty : (string)returnData["Status"];

                    activePurReqList.Add(activeReqDtl);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public void TGeneratePurchaseQuationDetailList<T>(SqlDataReader returnData, ref List<PurchaseQuotationDtl> activePurReqList)

        {

            try

            {

                while (returnData.Read())

                {

                    PurchaseQuotationDtl activeReqDtl = new PurchaseQuotationDtl();

                    activeReqDtl.Id = returnData["Id"] is DBNull ? (int)0 : (int)returnData["Id"];

                    activeReqDtl.PurchaseQuotationId = returnData["PurchaseQuotationId"] is DBNull ? (int)0 : (int)returnData["PurchaseQuotationId"];

                    activeReqDtl.ProductId = returnData["ProductId"] is DBNull ? (int)0 : (int)returnData["ProductId"];

                    activeReqDtl.ProductName = returnData["ProductName"] is DBNull ? (string)string.Empty : (string)returnData["ProductName"];

                    activeReqDtl.ProGrpName = returnData["ProGrpName"] is DBNull ? (string)string.Empty : (string)returnData["ProGrpName"];

                    activeReqDtl.Qty = returnData["Qty"] is DBNull ? (decimal)0 : (decimal)returnData["Qty"];

                    activeReqDtl.UnitPrice = returnData["UnitPrice"] is DBNull ? (decimal)18 : (decimal)returnData["UnitPrice"];

                    activeReqDtl.Price = returnData["Price"] is DBNull ? (decimal)18 : (decimal)returnData["Price"];

                    activeReqDtl.Discount = returnData["Discount"] is DBNull ? (decimal)18 : (decimal)returnData["Discount"];

                    activeReqDtl.DiscountPercentage = returnData["DiscountPercentage"] is DBNull ? (Boolean)false : (Boolean)returnData["DiscountPercentage"];

                    activeReqDtl.Total = returnData["Total"] is DBNull ? (decimal)18 : (decimal)returnData["Total"];

                    activeReqDtl.UnitName = returnData["UnitName"] is DBNull ? (string)string.Empty : (string)returnData["UnitName"];

                    //activeReqDtl.StatusNew = returnData["Status"] is DBNull ? (string)string.Empty : (string)returnData["Status"];

                    activeReqDtl.Location = returnData["Location"] is DBNull ? (string)string.Empty : (string)returnData["Location"];

                    activeReqDtl.PickDataID = returnData["PickDataID"] is DBNull ? (int)0 : (int)returnData["PickDataID"];

                    activeReqDtl.DiscountQty = returnData["DsicountQty"] is DBNull ? (decimal)0 : (decimal)returnData["DsicountQty"];

                    activeReqDtl.DiscountTotal = returnData["DiscountTotal"] is DBNull ? (decimal)20 : (decimal)returnData["DiscountTotal"];

                    activeReqDtl.PurQuoNo = returnData["PoNo"] is DBNull ? (string)string.Empty : (string)returnData["PoNo"];

                    activeReqDtl.PartNo = returnData["PartNo"] is DBNull ? (string)string.Empty : (string)returnData["PartNo"];

                    activeReqDtl.Remarks = returnData["Remarks"] is DBNull ? (string)string.Empty : (string)returnData["Remarks"];

                    activeReqDtl.CurrencyId = returnData["CurrencyId"] is DBNull ? (int)0 : (int)returnData["CurrencyId"];

                    activeReqDtl.CurrencyRate = returnData["CurrencyRate"] is DBNull ? (decimal)20 : (decimal)returnData["CurrencyRate"];

                    activeReqDtl.FcAmount = returnData["FcAmount"] is DBNull ? (decimal)20 : (decimal)returnData["FcAmount"];

                    activeReqDtl.FcUnitPrice = returnData["FcUnitPrice"] is DBNull ? (decimal)20 : (decimal)returnData["FcUnitPrice"];

                    activeReqDtl.DiscountAmount = returnData["DiscountAmount"] is DBNull ? (decimal)20 : (decimal)returnData["DiscountAmount"];

                    activeReqDtl.DiscountFc = returnData["DiscountFc"] is DBNull ? (decimal)20 : (decimal)returnData["DiscountFc"];

                    activeReqDtl.DiscountFcAmount = returnData["DiscountFcAmount"] is DBNull ? (decimal)20 : (decimal)returnData["DiscountFcAmount"];

                    activeReqDtl.AfterDiscountFcAmount = returnData["AfterDiscountFcAmount"] is DBNull ? (decimal)20 : (decimal)returnData["AfterDiscountFcAmount"];

                    activeReqDtl.PageNumber = returnData["PageNumber"] is DBNull ? (string)string.Empty : (string)returnData["PageNumber"];

                    activeReqDtl.ItemRef = returnData["ItemRef"] is DBNull ? (string)string.Empty : (string)returnData["ItemRef"];

                    activeReqDtl.SerialNumber = returnData["SerialNumber"] is DBNull ? (string)string.Empty : (string)returnData["SerialNumber"];

                    activeReqDtl.ConversionRateofCurrency = returnData["ConversionRateofCurrency"] is DBNull ? (decimal)18 : (decimal)returnData["ConversionRateofCurrency"];

                    activeReqDtl.VATId = returnData["VATId"] is DBNull ? 0 : (int)returnData["VATId"];

                    activeReqDtl.VATPercentage = returnData["VATPercentage"] is DBNull ? 0 : (decimal)returnData["VATPercentage"];

                    activeReqDtl.VATAmount = returnData["VATAmount"] is DBNull ? 0 : (decimal)returnData["VATAmount"];

                    activeReqDtl.AfterVATTotal = returnData["AfterVATTotal"] is DBNull ? 0 : (decimal)returnData["AfterVATTotal"];

                    activeReqDtl.AfterVATTotalFC = returnData["AfterVATTotal"] is DBNull ? 0 : (decimal)returnData["AfterVATTotal"];

                    activeReqDtl.NoOfStockInUnit = returnData["NoOfStockInUnit"] is DBNull ? 0 : (decimal)returnData["NoOfStockInUnit"];

                    activeReqDtl.UnitID = returnData["UnitID"] is DBNull ? 0 : (int)returnData["UnitID"];

                    activePurReqList.Add(activeReqDtl);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public bool PurchaseQuotationDtlDeleteByPINO(string PoNo,int FinancialYearID)

        {

            try

            {

                SqlCommand sqlCmd = new SqlCommand();

                sqlCmd.Parameters.Add("@PurQuoNo", SqlDbType.VarChar).Value = PoNo;

                sqlCmd.Parameters.Add("@FinancialYearID", SqlDbType.VarChar).Value = FinancialYearID;

                

                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.CommandText = "[dbo].[PurchaseQuotationDtlDeleteByPINO]";

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (sqlCmd == null)

                {

                    throw (new ArgumentNullException("sqlCmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    sqlCmd.Connection = cn;

                    cn.Open();

                    sqlCmd.ExecuteNonQuery();

                    cn.Close();

                }

                return true;

            }

            catch (Exception)

            {

                throw;

            }

        }

        public bool PurchaseQuotationDtlDeleteById(int Id)

        {

            try

            {

                SqlCommand sqlCmd = new SqlCommand();

                sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.CommandText = "[dbo].[PurchaseQuotationDtlDeleteById]";

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (sqlCmd == null)

                {

                    throw (new ArgumentNullException("sqlCmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    sqlCmd.Connection = cn;

                    cn.Open();

                    sqlCmd.ExecuteNonQuery();

                    cn.Close();

                }

                return true;

            }

            catch (Exception)

            {

                throw;

            }

        }

        public bool UpdatePurchaseQutationDtBalQty(string _productName, int? _purchaseQuotId, int _discountQty, decimal _discountTotal)

        {

            SqlConnection con = new SqlConnection(ConnectionString);

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "[dbo].[UpdatePurchaseQutationDtBalQty]";

            cmd.Connection = con;

            try

            {

                con.Open();

                AddParamToSQLCmd(cmd, "@ProductName", SqlDbType.Char, 100, ParameterDirection.Input, (object)_productName??DBNull.Value);

                if (_purchaseQuotId > 0)

                {

                    AddParamToSQLCmd(cmd, "@purchaseid", SqlDbType.Int, 2, ParameterDirection.Input, (object)_purchaseQuotId??DBNull.Value);

                }

                else

                {

                    AddParamToSQLCmd(cmd, "@purchaseid", SqlDbType.Int, 2, ParameterDirection.Input, (object)null??DBNull.Value);

                }

                AddParamToSQLCmd(cmd, "@DsicountQty", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)_discountQty ?? DBNull.Value);

                AddParamToSQLCmd(cmd, "@DiscountTotal", SqlDbType.Decimal, 18, ParameterDirection.Input, (object)_discountTotal ?? DBNull.Value);

                cmd.ExecuteNonQuery();

                con.Close();

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return true;

        }

        public bool CheckPerQuotConverttoPurchaseOrder(int PickDataID)

        {

            bool returnValue = false;

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@PickDataID", SqlDbType.Int, 4, ParameterDirection.Input, PickDataID);

                SetCommandType(cmd, CommandType.StoredProcedure, "CheckPerQuotConverttoPurchaseOrder");

                if (ConnectionString == string.Empty)

                {

                    throw (new ArgumentOutOfRangeException("ConnectionString"));

                }

                if (cmd == null)

                {

                    throw (new ArgumentNullException("cmd"));

                }

                using (SqlConnection cn = new SqlConnection(this.ConnectionString))

                {

                    cmd.Connection = cn;

                    cn.Open();

                    returnValue = Convert.ToBoolean(cmd.ExecuteScalar());

                    cn.Close();

                }

            }

            catch (Exception ex)

            {

                ///// Messages.ErrorMessage(ex.ToString());

            }

            return returnValue;

        }

        public DataTable GetPurchaseQuotationdtlPoNoNoByProductId(int Id)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                AddParamToSQLCmd(cmd, "@id", SqlDbType.Int, 4, ParameterDirection.Input, Id);

                SetCommandType(cmd, CommandType.StoredProcedure, "GetPurchaseQuotationdtlPoNoNoByProductId");

                cmd.Connection = sqlcon;

                DataTable dt = new DataTable();

                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = cmd;

                da.Fill(dt);

                if (dt.Rows.Count != 0)

                {

                    return dt;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return null;

        }

    }

}