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;
}
}
}