detail en

 using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace EntityLayer

{

    [Serializable()]

    public class PurchaseRequistionDtl

    {

        public int VendorPriceListId { get; set; }

        public int Id { get; set; }

        public int? PurchaseRequistionId { get; set; }

        public DateTime Date { get; set; }

        public string ProductName { get; set; }

        public string ProGrpName { get; set; }

        public decimal Qty { get; set; }

        public decimal NoOfStockInUnit { get; set; }

        public decimal UnitPrice { get; set; }

        public int? SaleOrderId { get; set; }

        public decimal Discount { get; set; }

        public decimal Total { get; set; }

        public string Remarks { get; set; }

        

        public string PoNo { get; set; }

        public string VendorName { get; set; }

        public int? PickDataID { get; set; }

        public int? UnitID { get; set; }

        public string UnitName { get; set; }

        public bool Status { get; set; }

        public decimal DsicountQty { get; set; }

        public decimal DiscountTotal { get; set; }

        public string Location { get; set; }

        public string StatusNew { get; set; }

        public string PartNo { get; set; }

        public int ProductId { get; set; }

        public decimal ReturnQty { get; set; }

        public bool DiscountPercentage { get; set; }

        public decimal Price { get; set; }

        public decimal DiscountQty { get; set; }

        public int? GoodReceivedNewId { get; set; }

        public decimal DiscountAmount { get; set; }

        public int? CurrencyId { get; set; }

        public string CurrencyName { get; set; }

        public decimal CurrencyRate { get; set; }

        public decimal CurrencyAmount { get; set; }

        public decimal DiscountFcAmount { get; set; }

        public decimal AfterDiscountFcAmount { get; set; }

        public decimal DiscountFc { get; set; }

        public decimal FcUnitPrice { get; set; }

        public decimal FcAmount { get; set; }

        public string PageNumber { get; set; }

        public string ItemRef { get; set; }

        public string SerialNumber { get; set; }

        public decimal ConversionRateofCurrency { get; set; }

        public bool IsDivide { get; set; }

        public int VATId { get; set; }

        public string VATName { get; set; }

        public decimal VATAmount { get; set; }

        public decimal VATPercentage { get; set; }

        public decimal AfterVATTotal { get; set; }

        public decimal AfterVATTotalFC { get; set; }

    }

}

master en

 using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace EntityLayer

{

    [Serializable()]

    public class PurchaseRequistion

    {

        public int VendorPriceListId { get; set; }

        public int PurchaseRequistionId { get; set; }

        public int? VendorId { get; set; }

        public DateTime Date { get; set; }

        public string PoNo { get; set; }

        public string VendorName { get; set; }

        public decimal NetTotal { get; set; }

       

        public string Location { get; set; }

        public string Remarks { get; set; }

        public string Employee { get; set; }

        public bool Status { get; set; }

        public string ProductName { get; set; }

        public decimal Total { get; set; }

        public int? PurReqId { get; set; }

        public int? JobId { get; set; }

        public int? CompanyId { get; set; }

        public int? UserId { get; set; }

        public string JobName { get; set; }

        public string VendorAccountCode { get; set; }

        public string ApprovalStatus { get; set; }

        public string UserName { get; set; }

        public string ClientPoNo { get; set; }

        public string RefNo { get; set; }

        public string OurPoNo { get; set; }

        public bool DiscountAllAmount { get; set; }

        public bool DiscountAllPer { get; set; }

        public decimal DiscountAmount { get; set; }

        public decimal NetDiscount { get; set; }

        public decimal DiscountPer { get; set; }

        public int? CreatedBy { get; set; }

        public int? ModifiedBy { get; set; }

        public DateTime? CreatedOn { get; set; }

        public DateTime? ModifiedOn { get; set; }

        public string DocumentOP { get; set; }

        public string DocumentBalance { get; set; }

        public int? FinancialYearID { get; set; }

        public int? CurrencyId { get; set; }

        public decimal FcRate { get; set; }

        public decimal FcTotal { get; set; }

        public decimal FcNetDiscount { get; set; }

        public decimal Amount { get; set; }

        public decimal VATAmount { get; set; }

        public decimal VATAmountFC { get; set; }

        public decimal RemainingTotal { get; set; }

        public decimal TotalQty { get; set; }

        public decimal PickQty { get; set; }

        public decimal Remaining_Qty { get; set; }

    }

}

picking adjust query

-- =============================================  

-- Author: Adeel Majeed  

-- Project : Based  

-- Create date: --  

-- Alter date : 14-09-18  

-- Description: Add the column and Multiply CurrencyRate Convert to ConversionRateofCurrency  

-- ===========================================================================================  

CREATE PROCEDURE PurchaseRequistionRemainingDtlGetByPurchaseRequistionId --82  

 @PurchaseRequistionId int  

AS  

BEGIN  

SELECT * FROM(  

 SELECT DISTINCT PRDTL.Id,  

 PRDTL.PurchaseRequistionId,  

 PRDTL.ProductId,  

 Product.ProductName,  

 ProGrpName,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) AS Qty,  

 PRDTL.UnitPrice,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice AS Price,  

 PRDTL.Discount,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00) AS Total,  

 CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.Discount,0)   

 ELSE ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice) - ((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * ISNULL(PRDTL.Discount,0))  

 END AS Total,  

 PRDTL.Remarks,  

 PRDTL.UnitName,  

 PurchaseRequistion.[Date],  

 PRDTL.[Status],  

 PRDTL.PoNo,  

 --DsicountQty,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) AS DsicountQty,  

 --PRDTL.DiscountTotal,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00) AS DiscountTotal,  

 CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00)  

 ELSE ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice) - ((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * ISNULL(PRDTL.Discount,0))  

 END AS DiscountTotal,  

 PRDTL.PartNo,  

 PRDTL.DiscountPercentage,  

 PRDTL.CurrencyId,  

 PRDTL.CurrencyRate,  

 PRDTL.DiscountFcAmount,  

 (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) * PRDTL.UnitPrice)*  

 (CASE WHEN cm.IsDivide=1 THEN 1/PRDTL.CurrencyRate ELSE PRDTL.CurrencyRate END))  

 -ISNULL(CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL( PRDTL.DiscountFcAmount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.DiscountFc  

 END,0.00) AS AfterDiscountFcAmount,  

 PRDTL.DiscountFc,  

 PRDTL.FcUnitPrice,  

 (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) * PRDTL.UnitPrice)*  

 (CASE WHEN cm.IsDivide=1 THEN 1/PRDTL.CurrencyRate ELSE PRDTL.CurrencyRate END)) AS FcAmount,  

 Product.PageNumber,  

 Product.ItemRef,  

 Product.SerialNumber,  

 PRDTL.SaleOrderId,  

 PRDTL.PickDataID,  

 PRDTL.ConversionRateofCurrency,  

 PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0) AS ReturnQty,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice AS ReturnTotal,  

 (CASE WHEN PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0) > 0 THEN 'True' ELSE 'False' END) AS ReturnStatus,  

 PRDTL.VATId,  

 PRDTL.VATPercentage,  

 --PRDTL.VATAmount,  

  

((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount  

 END)/100)*PRDTL.VATPercentage  as VATAmount,  

  

((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount   

 END)/100)*PRDTL.VATPercentage + (((PRDTL.Qty-isnull(sum(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount   

 END) AS AfterVATTotal,  

 cm.CurrencyName,  

 DiscountTaxMaster.Name AS VATName,  

 --PRDTL.AfterVATTotal,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00)+ISNULL(PRDTL.VATAmount,0.00) AS AfterVATTotal,  

 PurchaseRequistion.VendorPriceListId,  

 PRDTL.unitID  

 ,PRDTL.NoOfStockInUnit  

 FROM dbo.PurchaseRequistionDtl PRDTL  

 LEFT JOIN PurchaseRequistion ON PurchaseRequistion.PurchaseRequistionId= PRDTL.id  

 LEFT JOIN ApprovalDocument ON ApprovalDocument.DocumentId=PurchaseRequistion.PurchaseRequistionId  

 LEFT JOIN PurchaseQuotationDtl pqdtl ON pqdtl.PickDataID = PRDTL.id and pqdtl.ProductId = PRDTL.ProductId  

 LEFT JOIN Product ON PRDTL.ProductId = Product.Id  

 LEFT JOIN ProductGroup ON Product.ProGrpId = ProductGroup.Id  

 LEFT JOIN CurrencyMaster cm ON cm.Id = PRDTL.CurrencyId  

 LEFT JOIN DiscountTaxMaster ON DiscountTaxMaster.Id = PRDTL.VATId  

WHERE PRDTL.PurchaseRequistionId = @PurchaseRequistionId  

-- and PRDTL.Status=1  

GROUP BY  

PRDTL.Id,PRDTL.PurchaseRequistionId,PRDTL.ProductId,Product.ProductName,ProGrpName,  

 PRDTL.Qty,PRDTL.UnitPrice,PRDTL.Discount,  

 PRDTL.Remarks,PRDTL.UnitName,PurchaseRequistion.[Date],PRDTL.[Status],  

 PRDTL.PoNo,DsicountQty,PRDTL.DiscountTotal,PRDTL.PartNo,PRDTL.DiscountPercentage,  

 PRDTL.CurrencyId,  

 PRDTL.CurrencyRate,  

 PRDTL.DiscountFcAmount,PRDTL.DiscountFc,  

 PRDTL.FcUnitPrice,  

 Product.PageNumber,  

 Product.ItemRef,  

 Product.SerialNumber ,  

 PRDTL.SaleOrderId,  

 PRDTL.PickDataID,PRDTL.Qty,  

 PRDTL.DiscountAmount,  

 PRDTL.ConversionRateofCurrency,  

 cm.IsDivide,  

 PRDTL.VATId,  

 PRDTL.VATPercentage,  

 PRDTL.VATAmount,  

 PRDTL.AfterVATTotal,  

 cm.CurrencyName,  

 DiscountTaxMaster.Name,  

 PurchaseRequistion.VendorPriceListId,  

 PRDTL.UNITID,  

 PRDTL.NoOfStockInUnit  

 ) AS Temptable  

 WHERE ReturnStatus='true'  

  

END  

update query example

CREATE PROCEDURE [dbo].[PurchaseQuotationUpdate]  

 @Id int,  

 @PurQuoNo varchar(50),  

 @VendorPriceListId int,  

 @VendorId int,  

 @Date datetime,  

 @NetTotal decimal(18,5),  

 @Location varchar(50),  

 @Employee varchar(100),  

 @Remarks varchar(5000),  

 @Status varchar(50),  

 @JobId int,  

 @CompanyId int,  

 @UserId int,  

 @VendorAccountCode varchar(100),  

 @ApprovalStatus varchar(100),  

 --@CurrencyId int,  

 @FcTotal decimal(18,5),  

 @NetDiscount decimal(18,5),  

 @DiscountAllAmount bit,  

 @DiscountAllPer bit,  

 @DiscountAmount decimal(18,5),  

 @DiscountPer decimal(18,5),  

 --@FcRate decimal(18,2),  

 @ClientPONO nvarchar(100),  

 @RefNo nvarchar(100),  

 @OurPONO nvarchar(100),  

 @ModifiedBy int,  

 @ModifiedOn DateTime,  

 @FinancialYearID int  

AS  

BEGIN  

UPDATE [PurchaseQuotation]  

   SET [PurQuoNo] = @PurQuoNo  

   ,[VendorPriceListId] = @VendorPriceListId  

      ,[VendorId] = @VendorId  

      ,[Date] = @Date  

      ,[NetTotal]=@NetTotal  

      ,[Location]=@Location  

      ,[Employee]=@Employee  

      ,[Remarks]=@Remarks  

      ,[Status]=@Status  

   ,[JobId]=@JobId  

   ,[CompanyId]=@CompanyId  

   ,[UserId]=@UserId  

   ,[VendorAccountCode]=@VendorAccountCode  

   ,[ApprovalStatus]=@ApprovalStatus  

   --,[CurrencyId]=@CurrencyId  

   ,[FcTotal]=@FcTotal  

   ,[NetDiscount]=@NetDiscount  

   ,[DiscountAllAmount]=@DiscountAllAmount  

   ,[DiscountAllPer]=@DiscountAllPer  

   ,[DiscountAmount]=@DiscountAmount  

   ,[DiscountPer]=@DiscountPer  

   --,[FcRate] = @FcRate  

   ,[ClientPoNo]=@ClientPONO  

   ,RefNo=@RefNo  

   ,[OurPoNo]=@OurPONO  

     ,ModiFiedBy = @ModifiedBy  

   ,ModifiedOn = @ModifiedOn  

   ,[FinancialYearID] = @FinancialYearID  

  

 WHERE Id = @Id  

END  

  

  

  

insert query example

 CREATE PROCEDURE [dbo].[PurchaseQuotationInsert]  

  

 @Id int output,  

 @PurQuoNo varchar(50),  

 @VendorPriceListId int,  

 @VendorId int,  

 @Date datetime,  

 @NetTotal decimal(18,5),  

 @Location varchar(50),  

 @Employee varchar(100),  

 @Remarks varchar(5000),  

 @Status varchar(50),  

 @JobId int,  

 @CompanyId int,  

 @UserId int,  

 @VendorAccountCode varchar(100),  

 --@CurrencyId int,  

 @FcTotal decimal(18,5),  

 @ApprovalStatus varchar(100),  

 @NetDiscount decimal(18,5),  

 @DiscountAllAmount bit,  

 @DiscountAllPer bit,  

 @DiscountAmount decimal(18,5),  

 @DiscountPer decimal(18,5),  

 --@FcRate decimal(18,2),  

 @ClientPONO nvarchar(100),  

 @RefNO nvarchar(100),  

 @OurPONO nvarchar(100),  

 @CreatedBy int,  

 @CreatedOn DateTime,  

 @FinancialYearID int,  

 @IsApproved bit,  

 @IsRejected bit  

AS  

BEGIN  

 INSERT INTO [PurchaseQuotation]  

           ([PurQuoNo]  

     ,[VendorPriceListId]  

           ,[VendorId]  

           ,[Date]  

           ,[NetTotal]  

           ,[Location]  

           ,[Employee]  

           ,[Remarks]  

           ,[Status]  

     ,[JobId]  

     ,[CompanyId]  

     ,[UserId]  

     ,[VendorAccountCode]  

     --,[CurrencyId]  

     ,[FcTotal]  

     ,[ApprovalStatus]  

     ,[NetDiscount]  

     ,[DiscountAllAmount]  

     ,[DiscountAllPer]  

     ,[DiscountAmount]  

     ,[DiscountPer]  

     --,[FcRate]  

     ,ClientPoNo  

     ,[RefNo]  

     ,[OurPoNo]  

      ,[CreatedBy]  

     ,[CreatedOn]  

     ,[FinancialYearID]  

     ,[IsApproved]  

     ,[IsRejected])  

     VALUES  

           (@PurQuoNo  

     ,@VendorPriceListId  

           ,@VendorId  

           ,@Date  

           ,@NetTotal  

           ,@Location  

           ,@Employee  

           ,@Remarks  

           ,@Status  

     ,@JobId  

     ,@CompanyId  

     ,@UserId  

     ,@VendorAccountCode  

    -- ,@CurrencyId  

     ,@FcTotal  

     ,@ApprovalStatus  

     ,@NetDiscount  

     ,@DiscountAllAmount  

     ,@DiscountAllPer  

     ,@DiscountAmount  

     ,@DiscountPer  

     --,@FcRate  

     ,@ClientPONO  

     ,@RefNO  

     ,@OurPONO  

       ,@CreatedBy  

     ,@CreatedOn  

     ,@FinancialYearID  

     ,@IsApproved  

     ,@IsRejected)  

  

         Set @Id =@@IDENTITY  

END

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;

        }

    }

}