using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using EntityLayer;
namespace DataAccessLayer
{
public class PurchaseQuotationDAl : SQLDataAccess
{
public List<PurchaseQuotation> PurchaseQuotationSelectComapanyIDFinancialID(int CompanyID, int FinancialYearID)
{
try
{
SqlCommand cmd = new SqlCommand();
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectComapanyIDFinancialID");
AddParamToSQLCmd(cmd, "@CompanyID", SqlDbType.VarChar, 100, ParameterDirection.Input, CompanyID);
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.VarChar, 100, ParameterDirection.Input, FinancialYearID);
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public PurchaseQuotation PurchaseQuotationLastTransaction()
{
try
{
SqlCommand cmd = new SqlCommand();
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationDisplayLastTransaction");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null && activePurchaseQuotationList.Count > 0)
{
return activePurchaseQuotationList[0];
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public List<PurchaseQuotation> PurchaseQuotationSelectByPending()
{
try
{
SqlCommand cmd = new SqlCommand();
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByPending");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public List<PurchaseQuotation> PurchaseQuotationSelectByNameVendor(string Product)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Product", SqlDbType.VarChar, 100, ParameterDirection.Input, Product);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByNameVendor");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public List<PurchaseQuotation> PurchaseQuotationSelectByCompanyId(int CompanyId)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@CompanyId", SqlDbType.Int, 4, ParameterDirection.Input, CompanyId);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByCompanyId");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public PurchaseQuotation PurchaseQuotationSelectByPurQuoNo(string PurQuoNo)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, PurQuoNo);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByPurQuoNo");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null && activePurchaseQuotationList.Count > 0)
{
return activePurchaseQuotationList[0];
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public List<PurchaseQuotation> PurchaseQuotationSelectByPurQuoNoPrint(string PurQuoNo)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, PurQuoNo);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByPurQuoNoPrint");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public PurchaseQuotation PurchaseQuotationGetById(int? Id)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Id", SqlDbType.Int, 4, ParameterDirection.Input, (object)Id ?? DBNull.Value);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationGetById");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null && activePurchaseQuotationList.Count > 0)
{
return activePurchaseQuotationList[0];
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public PurchaseQuotation PurchaseQuotationGetByPONo(string PONo,int FinancialYearID)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@PONo", SqlDbType.VarChar, 50, ParameterDirection.Input, PONo);
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, FinancialYearID);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationGetByPONo");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null && activePurchaseQuotationList.Count > 0)
{
return activePurchaseQuotationList[0];
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public List<PurchaseQuotation> PurchaseGetByVendorId(int VendorId)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@VendorId", SqlDbType.Int, 4, ParameterDirection.Input, VendorId);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationGetByVendorId");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
public PurchaseQuotation PurchaseQuotationGetMaxId(int FinancialYearID)
{
try
{
List<PurchaseQuotation> activePurchaseList = new List<PurchaseQuotation>();
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, FinancialYearID);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[PurchaseQuotationGetMaxId]";
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PurchaseQuotation activePurchase = new PurchaseQuotation();
activePurchase.Id = reader["Id"] is DBNull ? (int)0 : (int)reader["Id"];
activePurchase.PurQuoNo = reader["PurQuoNo"] is DBNull ? (string)string.Empty : (string)reader["PurQuoNo"];
activePurchaseList.Add(activePurchase);
}
if (activePurchaseList != null && activePurchaseList.Count > 0)
{
return activePurchaseList[0];
}
return null;
}
catch (Exception ex)
{
throw ex;
}
}
public int PurchaseInsert(PurchaseQuotation activeQuotation)
{
int Id = 0;
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Id", SqlDbType.Int, 4, ParameterDirection.Output, (object)activeQuotation.Id ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.PurQuoNo ?? DBNull.Value);
if (activeQuotation.VendorId > 0)
{
AddParamToSQLCmd(cmd, "@VendorId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.VendorId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@VendorId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@Date", SqlDbType.DateTime, 20, ParameterDirection.Input, (object)activeQuotation.Date ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@NetTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.NetTotal ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Remarks", SqlDbType.VarChar, 5000, ParameterDirection.Input, (object)activeQuotation.Remarks ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Employee", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Employee ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Location", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Location ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Status", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Status ?? DBNull.Value);
if (activeQuotation.JobId > 0)
{
AddParamToSQLCmd(cmd, "@JobId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.JobId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@JobId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
if (activeQuotation.CompanyId > 0)
{
AddParamToSQLCmd(cmd, "@CompanyId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.CompanyId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@CompanyId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
if (activeQuotation.UserId > 0)
{
AddParamToSQLCmd(cmd, "@UserId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.UserId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@UserId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@VendorAccountCode", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.VendorAccountCode ?? DBNull.Value);
//AddParamToSQLCmd(cmd, "@CurrencyId", SqlDbType.Int, 4, ParameterDirection.Input, activeQuotation.CurrencyId);
AddParamToSQLCmd(cmd, "@FcTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.FcTotal ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@ApprovalStatus", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.ApprovalStatus ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@NetDiscount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.NetDiscount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAllAmount", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.DiscountAllAmount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAllPer", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.DiscountAllPer ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.DiscountAmount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountPer", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.DiscountPer ?? DBNull.Value);
//AddParamToSQLCmd(cmd, "@FcRate", SqlDbType.Decimal, 20, ParameterDirection.Input, activeQuotation.FcRate);
AddParamToSQLCmd(cmd, "@ClientPoNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.ClientPoNo ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@RefNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.RefNo ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@OurPoNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.OurPoNo ?? DBNull.Value);
if (activeQuotation.CreatedBy > 0)
{
AddParamToSQLCmd(cmd, "@CreatedBy", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.CreatedBy ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@CreatedBy", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@CreatedOn", SqlDbType.DateTime, 20, ParameterDirection.Input, (object)activeQuotation.CreatedOn ?? DBNull.Value);
if (activeQuotation.FinancialYearID > 0)
{
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.FinancialYearID ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@IsApproved", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.IsApproved ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@IsRejected", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.IsRejected ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@VendorPriceListId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.VendorPriceListId ?? DBNull.Value);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationInsert");
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 PurchaseQuotationUpdate(PurchaseQuotation activeQuotation)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Id", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.Id ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@PurQuoNo", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.PurQuoNo ?? DBNull.Value);
if (activeQuotation.VendorId > 0)
{
AddParamToSQLCmd(cmd, "@VendorId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.VendorId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@VendorId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@Date", SqlDbType.DateTime, 20, ParameterDirection.Input, (object)activeQuotation.Date ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@NetTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.NetTotal ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Remarks", SqlDbType.VarChar, 5000, ParameterDirection.Input, (object)activeQuotation.Remarks ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Employee", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Employee ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Location", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Location ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@Status", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.Status ?? DBNull.Value);
if (activeQuotation.JobId > 0)
{
AddParamToSQLCmd(cmd, "@JobId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.JobId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@JobId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
if (activeQuotation.CompanyId > 0)
{
AddParamToSQLCmd(cmd, "@CompanyId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.CompanyId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@CompanyId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
if (activeQuotation.UserId > 0)
{
AddParamToSQLCmd(cmd, "@UserId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.UserId ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@UserId", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@VendorAccountCode", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.VendorAccountCode ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@ApprovalStatus", SqlDbType.VarChar, 100, ParameterDirection.Input, (object)activeQuotation.ApprovalStatus ?? DBNull.Value);
//AddParamToSQLCmd(cmd, "@CurrencyId", SqlDbType.Int, 4, ParameterDirection.Input, activeQuotation.CurrencyId);
AddParamToSQLCmd(cmd, "@FcTotal", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.FcTotal ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@NetDiscount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.NetDiscount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAllAmount", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.DiscountAllAmount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAllPer", SqlDbType.Bit, 1, ParameterDirection.Input, (object)activeQuotation.DiscountAllPer ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountAmount", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.DiscountAmount ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@DiscountPer", SqlDbType.Decimal, 20, ParameterDirection.Input, (object)activeQuotation.DiscountPer ?? DBNull.Value);
//AddParamToSQLCmd(cmd, "@FcRate", SqlDbType.Decimal, 20, ParameterDirection.Input, activeQuotation.FcRate);
AddParamToSQLCmd(cmd, "@ClientPoNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.ClientPoNo ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@RefNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.RefNo ?? DBNull.Value);
AddParamToSQLCmd(cmd, "@OurPoNo", SqlDbType.NVarChar, 100, ParameterDirection.Input, (object)activeQuotation.OurPoNo ?? DBNull.Value);
if (activeQuotation.ModifiedBy > 0)
{
AddParamToSQLCmd(cmd, "@ModifiedBy", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.ModifiedBy ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@ModifiedBy", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@ModifiedOn", SqlDbType.DateTime, 20, ParameterDirection.Input, (object)activeQuotation.ModifiedOn ?? DBNull.Value);
if (activeQuotation.FinancialYearID > 0)
{
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.FinancialYearID ?? DBNull.Value);
}
else
{
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.Int, 4, ParameterDirection.Input, null ?? DBNull.Value);
}
AddParamToSQLCmd(cmd, "@VendorPriceListId", SqlDbType.Int, 4, ParameterDirection.Input, (object)activeQuotation.VendorPriceListId ?? DBNull.Value);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationUpdate");
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();
cmd.ExecuteNonQuery();
cn.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return true;
}
public void PurchaseQuotationApprove(string Pono,int FinancialYearID)
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Pono", SqlDbType.VarChar, 100, ParameterDirection.Input, Pono);
AddParamToSQLCmd(cmd, "@FinancialYearID", SqlDbType.VarChar, 100, ParameterDirection.Input, FinancialYearID);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationApprove");
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();
}
}
public void PurchaseQuotationReject(string Pono,int FinancialYearID)
{
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, "PurchaseQuotationReject");
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();
}
}
public DataTable PurchaseQuotationCheckApproval(string Pono,int FinancialYearID)
{
DataTable ds = new DataTable();
SqlCommand cmd = new SqlCommand("PurchaseQuotationCheckApproval", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Pono", Pono);
cmd.Parameters.AddWithValue("@FinancialYearID", FinancialYearID);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
ds = new DataTable();
sda.Fill(ds);
}
return ds;
}
public bool PurchaseQuotationDelete(int Id)
{
try
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "[dbo].[PurchaseQuotationDelete]";
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 ex)
{
throw ex;
}
}
public bool PurchaseQuotationDelete(string Id)
{
try
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Parameters.Add("@Id", SqlDbType.Text).Value = Id;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "[dbo].[PurchaseQuotationDelete]";
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 ex)
{
throw ex;
}
}
public bool PurchaseQuotationDeleteByPINo(string PurQuoNo,int FinancialYearID)
{
try
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Parameters.Add("@PurQuoNo", SqlDbType.VarChar).Value = PurQuoNo;
sqlCmd.Parameters.Add("@FinancialYearID", SqlDbType.VarChar).Value = FinancialYearID;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "[dbo].[PurchaseQuotationDeleteByPINo]";
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 ex)
{
throw ex;
}
}
public void TGenerateSOFieldFromReaderactivePurchaseQuotation<T>(SqlDataReader returnData, ref List<PurchaseQuotation> activeList)
{
try
{
while (returnData.Read())
{
PurchaseQuotation activePurchaseQuotation = new PurchaseQuotation();
activePurchaseQuotation.Id = returnData["Id"] is DBNull ? (int)0 : (int)returnData["Id"];
activePurchaseQuotation.PurQuoId = returnData["Id"] is DBNull ? (int)0 : (int)returnData["Id"];
activePurchaseQuotation.QuotationId = returnData["Id"] is DBNull ? (int)0 : (int)returnData["Id"];
activePurchaseQuotation.VendorId = returnData["VendorId"] is DBNull ? (int)0 : (int)returnData["VendorId"];
activePurchaseQuotation.Date = returnData["Date"] is DBNull ? (DateTime)DateTime.Now : (DateTime)returnData["Date"];
activePurchaseQuotation.PurQuoNo = returnData["PurQuoNo"] is DBNull ? (string)string.Empty : (string)returnData["PurQuoNo"];
activePurchaseQuotation.NetTotal = returnData["NetTotal"] is DBNull ? (decimal)18 : (decimal)returnData["NetTotal"];
activePurchaseQuotation.VendorName = returnData["VendorName"] is DBNull ? (string)string.Empty : (string)returnData["VendorName"];
activePurchaseQuotation.Remarks = returnData["Remarks"] is DBNull ? (string)string.Empty : (string)returnData["Remarks"];
activePurchaseQuotation.Employee = returnData["Employee"] is DBNull ? (string)string.Empty : (string)returnData["Employee"];
activePurchaseQuotation.Location = returnData["Location"] is DBNull ? (string)string.Empty : (string)returnData["Location"];
activePurchaseQuotation.Status = returnData["Status"] is DBNull ? (string)string.Empty : (string)returnData["Status"];
activePurchaseQuotation.JobId = returnData["JobId"] is DBNull ? (int)0 : (int)returnData["JobId"];
activePurchaseQuotation.CompanyId = returnData["CompanyId"] is DBNull ? (int)0 : (int)returnData["CompanyId"];
activePurchaseQuotation.UserId = returnData["UserId"] is DBNull ? (int)0 : (int)returnData["UserId"];
activePurchaseQuotation.JobName = returnData["JobName"] is DBNull ? (string)string.Empty : (string)returnData["JobName"];
activePurchaseQuotation.VendorAccountCode = returnData["VendorAccountCode"] is DBNull ? (string)string.Empty : (string)returnData["VendorAccountCode"];
activePurchaseQuotation.CurrencyId = returnData["CurrencyId"] is DBNull ? (int)0 : (int)returnData["CurrencyId"];
activePurchaseQuotation.FcTotal = returnData["FcTotal"] is DBNull ? (decimal)18 : (decimal)returnData["FcTotal"];
activePurchaseQuotation.NetDiscount = returnData["NetDiscount"] is DBNull ? (decimal)0 : (decimal)returnData["NetDiscount"];
activePurchaseQuotation.ApprovalStatus = returnData["ApprovalStatus"] is DBNull ? (string)string.Empty : (string)returnData["ApprovalStatus"];
activePurchaseQuotation.UserName = returnData["UserName"] is DBNull ? (string)string.Empty : (string)returnData["UserName"];
activePurchaseQuotation.DiscountAllAmount = returnData["DiscountAllAmount"] is DBNull ? (bool)false : (bool)returnData["DiscountAllAmount"];
activePurchaseQuotation.DiscountAllPer = returnData["DiscountAllPer"] is DBNull ? (bool)false : (bool)returnData["DiscountAllPer"];
activePurchaseQuotation.DiscountAmount = returnData["DiscountAmount"] is DBNull ? (decimal)0 : (decimal)returnData["DiscountAmount"];
activePurchaseQuotation.DiscountPer = returnData["DiscountPer"] is DBNull ? (decimal)0 : (decimal)returnData["DiscountPer"];
activePurchaseQuotation.FcRate = returnData["FcRate"] is DBNull ? (decimal)0 : (decimal)returnData["FcRate"];
//activePurchaseQuotation.ClientPONO = returnData["ClientPONO"] is DBNull ? (string)string.Empty : (string)returnData["ClientPONO"];
//activePurchaseQuotation.RefNo = returnData["RefNo"] is DBNull ? (string)string.Empty : (string)returnData["RefNo"];
//activePurchaseQuotation.OurPONO = returnData["OurPONO"] is DBNull ? (string)string.Empty : (string)returnData["OurPONO"];
activePurchaseQuotation.ClientPoNo = returnData["ClientPoNo"] is DBNull ? (string)string.Empty : (string)returnData["ClientPoNo"];
activePurchaseQuotation.RefNo = returnData["RefNo"] is DBNull ? (string)string.Empty : (string)returnData["RefNo"];
activePurchaseQuotation.OurPoNo = returnData["OurPoNo"] is DBNull ? (string)string.Empty : (string)returnData["OurPoNo"];
activePurchaseQuotation.DocumentBalance = returnData["DocumentBalance"] is DBNull ? (string)string.Empty : (string)returnData["DocumentBalance"];
activePurchaseQuotation.DocumentOP = returnData["DocumentOP"] is DBNull ? (string)string.Empty : (string)returnData["DocumentOP"];
activePurchaseQuotation.BalFcAmount = returnData["BalFcAmount"] is DBNull ? (string)string.Empty : (string)returnData["BalFcAmount"];
activePurchaseQuotation.FcTotalCurrency = returnData["FcTotalCurrency"] is DBNull ? (string)string.Empty : (string)returnData["FcTotalCurrency"];
activePurchaseQuotation.FinancialYearID = returnData["FinancialYearID"] is DBNull ? (int)0 : (int)returnData["FinancialYearID"];
activePurchaseQuotation.CurrencyShortName = returnData["CurrencyShortName"] is DBNull ? (string)string.Empty : (string)returnData["CurrencyShortName"];
activePurchaseQuotation.VATAmount = returnData["VATAmount"] is DBNull ? 0.00m : (decimal)returnData["VATAmount"];
activePurchaseQuotation.VATAmountFC = returnData["VATAmountFC"] is DBNull ? 0.00m : (decimal)returnData["VATAmountFC"];
activePurchaseQuotation.Amount = returnData["Amount"] is DBNull ? 0.00m : (decimal)returnData["Amount"];
activePurchaseQuotation.VendorPriceListId = returnData["VendorPriceListId"] is DBNull ? 0 : (int)returnData["VendorPriceListId"];
try
{
activePurchaseQuotation.IsApproved = returnData["IsApproved"] is DBNull ? false : Convert.ToBoolean(returnData["IsApproved"]);
activePurchaseQuotation.IsRejected = returnData["IsRejected"] is DBNull ? false : Convert.ToBoolean(returnData["IsRejected"]);
}
catch (Exception)
{
}
activeList.Add(activePurchaseQuotation);
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet PurchaseQoutDtl(string PQPoNo,int FinancialYearID)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("sp_PurQuotWDtl", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PurQoutPoNo", PQPoNo);
cmd.Parameters.AddWithValue("@FinancialYearID", FinancialYearID);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
ds = new DataSet();
sda.Fill(ds);
}
return ds;
}
public DataSet GenerateReport(int? vendor, DateTime FromDate, DateTime ToDate, string employee, int? DocID, int CompId, int? JobId)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("sp_PurchaseQouteDtlRpt", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Datefrom", SqlDbType.DateTime).Value = (object)FromDate ?? DBNull.Value;
cmd.Parameters.Add("@Dateto", SqlDbType.DateTime).Value = (object)ToDate ?? DBNull.Value;
cmd.Parameters.Add("@DocID", SqlDbType.Int).Value = (object)DocID ?? DBNull.Value;
cmd.Parameters.Add("@vendor", SqlDbType.Int).Value = (object)vendor ?? DBNull.Value;
cmd.Parameters.Add("@employee", SqlDbType.VarChar).Value = (object)employee ?? DBNull.Value;
cmd.Parameters.Add("@ComapnyId", SqlDbType.Int).Value = (object)CompId ?? DBNull.Value;
cmd.Parameters.Add("@JobId", SqlDbType.Int).Value = (object)JobId ?? DBNull.Value;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
ds = new DataSet();
sda.Fill(ds, "Purchase Quotation");
}
return ds;
}
public DataSet GetDataSumryRpt(int? vendor, DateTime FromDate, DateTime ToDate, string employee, string product, int CompId, int? JobId)
{
SQLDataAccess activeDataAccess = new SQLDataAccess();
string constr = activeDataAccess.ConnectionString;
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[sp_GetdatePurQuotTradReport]";
cmd.Parameters.Add("@vendor", SqlDbType.Int).Value = (object)vendor ?? DBNull.Value;
cmd.Parameters.Add("@Datefrom", SqlDbType.DateTime).Value = (object)FromDate ?? DBNull.Value;
cmd.Parameters.Add("@Dateto", SqlDbType.DateTime).Value = (object)ToDate ?? DBNull.Value;
cmd.Parameters.Add("@employee", SqlDbType.VarChar).Value = (object)employee ?? DBNull.Value;
cmd.Parameters.Add("@product", SqlDbType.VarChar).Value = (object)product ?? DBNull.Value;
cmd.Parameters.Add("@ComapnyId", SqlDbType.Int).Value = (object)CompId ?? DBNull.Value;
cmd.Parameters.Add("@JobId", SqlDbType.Int).Value = (object)JobId ?? DBNull.Value;
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(ds, "Purchase Quotation");
}
}
return ds;
}
public DataTable PurchaseQuotationForSelectedData(int rowno, int UserId)
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@rowno", SqlDbType.VarChar, 500, ParameterDirection.Input, rowno);
//AddParamToSQLCmd(cmd, "@UserId", SqlDbType.VarChar, 500, ParameterDirection.Input, UserId);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationForSelectedData");
cmd.Connection = sqlcon;
DataTable ds = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
public DataTable PurchaseQuotationForGraph(int Month)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@month", SqlDbType.Int, 4, ParameterDirection.Input, Month);
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationForGraph");
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;
}
public DataTable GetPurchaseOrderByPurchaseQuotationId(int PurchaseQuotationId)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@PurchaseQuotationId", SqlDbType.Int, 4, ParameterDirection.Input, (object)PurchaseQuotationId ?? DBNull.Value);
SetCommandType(cmd, CommandType.StoredProcedure, "GetPurchaseOrderByPurchaseQuotationId");
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;
}
//real Procedure
public DataTable PendingPurcahseQuotationForGraph(DateTime DateTime, bool CurrentWeek, bool CurrentYear, bool CurrentMonth)
{
try
{
SqlCommand cmd = new SqlCommand();
AddParamToSQLCmd(cmd, "@Date", SqlDbType.DateTime, 20, ParameterDirection.Input, DateTime);
if (CurrentWeek == true)
{
AddParamToSQLCmd(cmd, "@CurrentWeek", SqlDbType.Bit, 1, ParameterDirection.Input, CurrentWeek);
}
else
{
AddParamToSQLCmd(cmd, "@CurrentWeek", SqlDbType.Bit, 1, ParameterDirection.Input, null);
}
if (CurrentYear == true)
{
AddParamToSQLCmd(cmd, "@CurrentYear", SqlDbType.Bit, 1, ParameterDirection.Input, CurrentYear);
}
else
{
AddParamToSQLCmd(cmd, "@CurrentYear", SqlDbType.Bit, 1, ParameterDirection.Input, null);
}
if (CurrentMonth == true)
{
AddParamToSQLCmd(cmd, "@CurrentMonth", SqlDbType.Bit, 1, ParameterDirection.Input, CurrentMonth);
}
else
{
AddParamToSQLCmd(cmd, "@CurrentMonth", SqlDbType.Bit, 1, ParameterDirection.Input, null);
}
SetCommandType(cmd, CommandType.StoredProcedure, "PendingPurcahseQuotationForGraph");
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;
}
// End Real Procedure
public List<PurchaseQuotation> PurchaseQuotationSelectByVendorProjectUser(DateTime FromDate, DateTime ToDate, decimal FromAmount, decimal ToAmount, int? VendorId, int? ProjectId, int? UserId,string StatusCheck)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add("@Datefrom", SqlDbType.DateTime).Value = (object)FromDate ?? DBNull.Value;
cmd.Parameters.Add("@Dateto", SqlDbType.DateTime).Value = (object)ToDate ?? DBNull.Value;
if (FromAmount > 0)
{
cmd.Parameters.Add("@FromAmount", SqlDbType.Decimal).Value = (object)FromAmount ?? DBNull.Value;
}
else
{
cmd.Parameters.Add("@FromAmount", SqlDbType.Decimal).Value = (object)null;
}
if (ToAmount > 0)
{
cmd.Parameters.Add("@ToAmount", SqlDbType.Decimal).Value = (object)ToAmount ?? DBNull.Value;
}
else
{
cmd.Parameters.Add("@ToAmount", SqlDbType.Decimal).Value = (object)null;
}
cmd.Parameters.Add("@VendorId", SqlDbType.Int).Value = (object)VendorId ?? DBNull.Value;
cmd.Parameters.Add("@ProjectId", SqlDbType.Int).Value = (object)ProjectId ?? DBNull.Value;
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = (object)UserId ?? DBNull.Value;
cmd.Parameters.Add("@StatusCheck", SqlDbType.VarChar).Value = (object)StatusCheck ?? DBNull.Value;
SetCommandType(cmd, CommandType.StoredProcedure, "PurchaseQuotationSelectByVendorProjectUser");
List<PurchaseQuotation> activePurchaseQuotationList = new List<PurchaseQuotation>();
TExecuteReaderCmd<PurchaseQuotation>(cmd, TGenerateSOFieldFromReaderactivePurchaseQuotation<PurchaseQuotation>, ref activePurchaseQuotationList);
if (activePurchaseQuotationList != null)
{
return activePurchaseQuotationList;
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
}
}
No comments:
Post a Comment