public List <SupplierInvoices> GetOpeningSupplierInvoices(SupplierInvoices SupObj)
{
List <SupplierInvoices> SupplierInvoicesList = null;
Settings settings = new Settings();
try
{
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[GetAllSupplierOpeningInvoices]";
if (SupObj != null)
{
cmd.Parameters.Add("@includeinternal", SqlDbType.Bit).Value = SupObj.suppliersObj.IsInternalComp;
}
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if ((sdr != null) && (sdr.HasRows))
{
SupplierInvoicesList = new List <SupplierInvoices>();
while (sdr.Read())
{
SupplierInvoices CIList = new SupplierInvoices();
{
CIList.ID = (sdr["ID"].ToString() != "" ? Guid.Parse(sdr["ID"].ToString()) : CIList.ID);
CIList.InvoiceNo = sdr["InvoiceNo"].ToString();
CIList.suppliersObj = new Supplier();
CIList.suppliersObj.ID = Guid.Parse(sdr["ID"].ToString());
CIList.suppliersObj.ContactPerson = sdr["ContactPerson"].ToString();
CIList.suppliersObj.CompanyName = sdr["CompanyName"].ToString();
CIList.companiesObj = new Companies();
CIList.companiesObj.Name = sdr["Name"].ToString();
CIList.PaymentDueDate = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()) : CIList.PaymentDueDate);
CIList.BalanceDue = (sdr["BalanceDue"].ToString() != "" ? Decimal.Parse(sdr["BalanceDue"].ToString()) : CIList.BalanceDue);
CIList.PaidAmount = (sdr["PaidAmount"].ToString() != "" ? Decimal.Parse(sdr["PaidAmount"].ToString()) : CIList.PaidAmount);
CIList.PaymentDueDateFormatted = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()).ToString(settings.dateformat) : CIList.PaymentDueDateFormatted);
}
SupplierInvoicesList.Add(CIList);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SupplierInvoicesList);
}
public string GetSupplierOpeningInvoicesForMobile(SupplierInvoices SupObj)
{
try
{
SupplierSummaryforMobileViewModel invoiceObj = Mapper.Map <SupplierSummaryforMobile, SupplierSummaryforMobileViewModel>(_supplierInvoicesBusiness.GetOpeningSupplierInvoices(SupObj));
return(JsonConvert.SerializeObject(new { Result = true, Records = new { OpeningList = invoiceObj.SupInv, Summary = invoiceObj.supInvSumObj } }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { Result = false, Message = ex.Message }));
}
}
public SupplierInvoices GetSupplierAdvances(string ID)
{
SupplierInvoices SIList = null;
Settings settings = new Settings();
try
{
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[GetSupplierAdvances]";
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ID);
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if ((sdr != null) && (sdr.HasRows))
{
if (sdr.Read())
{
SIList = new SupplierInvoices();
SIList.suppliersObj = new Supplier();
SIList.suppliersObj.AdvanceAmount = decimal.Parse(sdr["AdvanceAmount"].ToString());
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SIList);
}
public string GetSupplierPurchaseByDateWiseForMobile(SupplierInvoices SupObj)
{
try
{
if (SupObj == null)
{
throw new Exception(messages.NoItems);
}
if (SupObj.FromDate == null && SupObj.ToDate == null)
{
SupObj.commonObj = new SPAccounts.DataAccessObject.DTO.Common();
SupObj.FromDate = SupObj.commonObj.GetCurrentDateTime().ToString();
SupObj.ToDate = SupObj.commonObj.GetCurrentDateTime().ToString();
}
SupplierSummaryforMobileViewModel invoiceObj = Mapper.Map <SupplierSummaryforMobile, SupplierSummaryforMobileViewModel>(_supplierInvoicesBusiness.GetSupplierPurchasesByDateWise(SupObj));
return(JsonConvert.SerializeObject(new { Result = true, Records = new { List = invoiceObj.SupInv, Summary = invoiceObj.supInvSumObj } }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { Result = false, Message = ex.Message }));
}
}
public List <SupplierInvoices> GetOutStandingInvoicesBySupplier(Guid PaymentID, Guid supplierID)
{
List <SupplierInvoices> SupplierInvoicesList = null;
Settings settings = new Settings();
try
{
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[GetSupplierOutStandingInvoices]";
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = supplierID;
cmd.Parameters.Add("@PaymentID", SqlDbType.UniqueIdentifier).Value = PaymentID;
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if ((sdr != null) && (sdr.HasRows))
{
SupplierInvoicesList = new List <SupplierInvoices>();
while (sdr.Read())
{
SupplierInvoices SIList = new SupplierInvoices();
{
SIList.ID = (sdr["ID"].ToString() != "" ? Guid.Parse(sdr["ID"].ToString()) : SIList.ID);
SIList.InvoiceDate = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()) : SIList.InvoiceDate);
SIList.InvoiceNo = sdr["InvoiceNo"].ToString();
SIList.PaymentDueDate = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()) : SIList.PaymentDueDate);
SIList.TotalInvoiceAmount = (sdr["TotalInvoiceAmount"].ToString() != "" ? Decimal.Parse(sdr["TotalInvoiceAmount"].ToString()) : SIList.TotalInvoiceAmount);
SIList.BalanceDue = (sdr["BalanceDue"].ToString() != "" ? Decimal.Parse(sdr["BalanceDue"].ToString()) : SIList.BalanceDue);
SIList.LastPaymentDate = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()) : SIList.LastPaymentDate);
SIList.OtherPayments = (sdr["OtherPayments"].ToString() != "" ? Decimal.Parse(sdr["OtherPayments"].ToString()) : SIList.OtherPayments);
SIList.SuppPaymentObj = new SupplierPayments();
SIList.SuppPaymentObj.supplierPaymentsDetailObj = new SupplierPaymentsDetail();
SIList.SuppPaymentObj.supplierPaymentsDetailObj.PaidAmount = (sdr["PaidAmountEdit"].ToString() != "" ? Decimal.Parse(sdr["PaidAmountEdit"].ToString()) : SIList.SuppPaymentObj.supplierPaymentsDetailObj.PaidAmount);
SIList.SuppPaymentObj.supplierPaymentsDetailObj.ID = (sdr["PaymentDetailID"].ToString() != "" ? Guid.Parse(sdr["PaymentDetailID"].ToString()) : SIList.SuppPaymentObj.supplierPaymentsDetailObj.ID);
//------------date formatting-----------------//
SIList.InvoiceDateFormatted = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()).ToString(settings.dateformat) : SIList.InvoiceDateFormatted);
SIList.PaymentDueDateFormatted = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()).ToString(settings.dateformat) : SIList.PaymentDueDateFormatted);
SIList.LastPaymentDateFormatted = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()).ToString(settings.dateformat) : SIList.LastPaymentDateFormatted);
SIList.AccountCode = (sdr["AccountCode"].ToString() != "" ? sdr["AccountCode"].ToString() : SIList.AccountCode);
SIList.EmpName = (sdr["SubType"].ToString() != "" ? sdr["SubType"].ToString() : SIList.EmpName);
SIList.companiesObj = new Companies();
SIList.companiesObj.Name = (sdr["InvoiceCompanyName"].ToString() != "" ? sdr["InvoiceCompanyName"].ToString() : SIList.companiesObj.Name);
}
SupplierInvoicesList.Add(SIList);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SupplierInvoicesList);
}
public SupplierInvoices UpdateInvoice(SupplierInvoices _supplierInvoicesObj)
{
try
{
SqlParameter outputStatus = null;
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[UpdateSupplierInvoice]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = _supplierInvoicesObj.ID;
cmd.Parameters.Add("@CompanyCode", SqlDbType.VarChar, 10).Value = _supplierInvoicesObj.CompanyCode;
cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar, 20).Value = _supplierInvoicesObj.InvoiceNo;
cmd.Parameters.Add("@SupplierID", SqlDbType.UniqueIdentifier).Value = _supplierInvoicesObj.SupplierID;
cmd.Parameters.Add("@EmpID", SqlDbType.UniqueIdentifier).Value = _supplierInvoicesObj.EmpID;
_supplierInvoicesObj.AccountCode = _supplierInvoicesObj.AccountCode.Split(':')[0];
cmd.Parameters.Add("@AccountCode", SqlDbType.VarChar, 10).Value = _supplierInvoicesObj.AccountCode;
cmd.Parameters.Add("@PaymentTerm", SqlDbType.VarChar, 10).Value = _supplierInvoicesObj.PayCode;
cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = _supplierInvoicesObj.InvoiceDateFormatted;
cmd.Parameters.Add("@PaymentDueDate", SqlDbType.DateTime).Value = _supplierInvoicesObj.PaymentDueDateFormatted;
cmd.Parameters.Add("@BillingAddress", SqlDbType.NVarChar, -1).Value = _supplierInvoicesObj.BillingAddress;
cmd.Parameters.Add("@GrossAmount", SqlDbType.Decimal).Value = _supplierInvoicesObj.GrossAmount;
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = _supplierInvoicesObj.Discount;
cmd.Parameters.Add("@TaxTypeCode", SqlDbType.VarChar, 10).Value = _supplierInvoicesObj.TaxCode != "" ? _supplierInvoicesObj.TaxCode : null;
cmd.Parameters.Add("@TaxPreApplied", SqlDbType.Decimal).Value = _supplierInvoicesObj.TaxPercApplied;
cmd.Parameters.Add("@ShippingCharge", SqlDbType.Decimal).Value = _supplierInvoicesObj.ShippingCharge;
cmd.Parameters.Add("@TaxAmount", SqlDbType.Decimal).Value = _supplierInvoicesObj.TaxAmount;
cmd.Parameters.Add("@GeneralNotes", SqlDbType.NVarChar, -1).Value = _supplierInvoicesObj.Notes;
cmd.Parameters.Add("@UpdatedBy", SqlDbType.NVarChar, 250).Value = _supplierInvoicesObj.commonObj.UpdatedBy;
cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = _supplierInvoicesObj.commonObj.UpdatedDate;
outputStatus = cmd.Parameters.Add("@Status", SqlDbType.SmallInt);
outputStatus.Direction = ParameterDirection.Output;
//outputID = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
//outputID.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
}
}
switch (outputStatus.Value.ToString())
{
case "0":
AppConst Cobj = new AppConst();
throw new Exception(Cobj.InsertFailure);
case "1":
// _customerInvoicesObj.ID = new Guid(outputID.Value.ToString());
break;
default:
break;
}
}
catch (Exception ex)
{
throw ex;
}
return(_supplierInvoicesObj);
}
public List <SupplierInvoices> GetAllSupplierInvoices(DateTime?FromDate, DateTime?ToDate, string Supplier, string InvoiceType, string Company, string Status, string Search, string AccountCode, string EmpID)
{
List <SupplierInvoices> SupplierInvoicesList = null;
Settings settings = new Settings();
try
{
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[GetAllSupplierInvoices]";
cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = FromDate;
cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = ToDate;
cmd.Parameters.Add("@SupplierCode", SqlDbType.NVarChar, 50).Value = Supplier;
cmd.Parameters.Add("@InvoiceType", SqlDbType.NVarChar, 50).Value = InvoiceType;
cmd.Parameters.Add("@CompanyCode", SqlDbType.NVarChar, 50).Value = Company;
cmd.Parameters.Add("@search", SqlDbType.NVarChar, 250).Value = Search;
cmd.Parameters.Add("@status", SqlDbType.NVarChar, 50).Value = Status;
if (AccountCode != null)
{
string AccountHeadCode = AccountCode.Split(':')[0];
cmd.Parameters.Add("@AccountCode", SqlDbType.NVarChar, 50).Value = AccountHeadCode;
}
if (EmpID != null)
{
cmd.Parameters.Add("@EmpID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(EmpID);
}
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if ((sdr != null) && (sdr.HasRows))
{
SupplierInvoicesList = new List <SupplierInvoices>();
while (sdr.Read())
{
SupplierInvoices SIList = new SupplierInvoices();
{
SIList.ID = (sdr["ID"].ToString() != "" ? Guid.Parse(sdr["ID"].ToString()) : SIList.ID);
SIList.InvoiceDate = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()) : SIList.InvoiceDate);
SIList.InvoiceNo = sdr["InvoiceNo"].ToString();
SIList.InvoiceType = sdr["InvoiceType"].ToString();
SIList.companiesObj = new Companies();
SIList.companiesObj.Name = sdr["OrginCompany"].ToString();
SIList.suppliersObj = new Supplier();
SIList.suppliersObj.ID = Guid.Parse(sdr["SupplierID"].ToString());
SIList.suppliersObj.CompanyName = sdr["CompanyName"].ToString();
SIList.PaymentDueDate = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()) : SIList.PaymentDueDate);
SIList.TotalInvoiceAmount = (sdr["TotalInvoiceAmount"].ToString() != "" ? Decimal.Parse(sdr["TotalInvoiceAmount"].ToString()) : SIList.TotalInvoiceAmount);
SIList.BalanceDue = (sdr["BalanceDue"].ToString() != "" ? Decimal.Parse(sdr["BalanceDue"].ToString()) : SIList.BalanceDue);
SIList.PaidAmount = (sdr["PaidAmount"].ToString() != "" ? Decimal.Parse(sdr["PaidAmount"].ToString()) : SIList.PaidAmount);
SIList.PaymentProcessed = (sdr["PaymentProcessed"].ToString() != "" ? Decimal.Parse(sdr["PaymentProcessed"].ToString()) : SIList.PaymentProcessed);
SIList.LastPaymentDate = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()) : SIList.LastPaymentDate);
SIList.Status = sdr["Status"].ToString();
//------------date formatting-----------------//
SIList.InvoiceDateFormatted = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()).ToString(settings.dateformat) : SIList.InvoiceDateFormatted);
SIList.PaymentDueDateFormatted = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()).ToString(settings.dateformat) : SIList.PaymentDueDateFormatted);
SIList.LastPaymentDateFormatted = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()).ToString(settings.dateformat) : SIList.LastPaymentDateFormatted);
SIList.AccountCode = (sdr["TypeDesc"].ToString() != "" ? (sdr["TypeDesc"].ToString()) : SIList.AccountCode);
SIList.EmpName = (sdr["EmpName"].ToString() != "" ? (sdr["EmpName"].ToString()) : SIList.EmpName);
}
SupplierInvoicesList.Add(SIList);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SupplierInvoicesList);
}
public SupplierInvoices GetSupplierInvoiceDetails(Guid ID)
{
SupplierInvoices SIList = null;
Settings settings = new Settings();
try
{
using (SqlConnection con = _databaseFactory.GetDBConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = "[Accounts].[GetSupplierInvoiceDetails]";
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = ID;
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if ((sdr != null) && (sdr.HasRows))
{
if (sdr.Read())
{
SIList = new SupplierInvoices();
SIList.ID = (sdr["ID"].ToString() != "" ? Guid.Parse(sdr["ID"].ToString()) : SIList.ID);
SIList.InvoiceDate = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()) : SIList.InvoiceDate);
SIList.InvoiceNo = sdr["InvoiceNo"].ToString();
SIList.InvoiceType = sdr["InvoiceType"].ToString();
SIList.companiesObj = new Companies();
SIList.companiesObj.Code = (sdr["InvoiceToComanyCode"].ToString());
SIList.paymentTermsObj = new PaymentTerms();
SIList.paymentTermsObj.Code = (sdr["PaymentTerm"].ToString());
SIList.suppliersObj = new Supplier();
SIList.suppliersObj.ID = Guid.Parse(sdr["SupplierID"].ToString());
SIList.suppliersObj.CompanyName = sdr["CompanyName"].ToString();
SIList.BillingAddress = (sdr["BillingAddress"].ToString());
SIList.GrossAmount = (sdr["GrossAmount"].ToString() != "" ? Decimal.Parse(sdr["GrossAmount"].ToString()) : SIList.GrossAmount);
SIList.Discount = (sdr["Discount"].ToString() != "" ? Decimal.Parse(sdr["Discount"].ToString()) : SIList.Discount);
SIList.TaxAmount = (sdr["TaxAmount"].ToString() != "" ? Decimal.Parse(sdr["TaxAmount"].ToString()) : SIList.TaxAmount);
SIList.ShippingCharge = (sdr["ShippingCharge"].ToString() != "" ? Decimal.Parse(sdr["ShippingCharge"].ToString()) : SIList.ShippingCharge);
SIList.TaxPercApplied = (sdr["TaxPercApplied"].ToString() != "" ? Decimal.Parse(sdr["TaxPercApplied"].ToString()) : SIList.TaxPercApplied);
SIList.Notes = (sdr["GeneralNotes"].ToString() != "" ? (sdr["GeneralNotes"].ToString()) : SIList.Notes);
SIList.TaxTypeObj = new TaxTypes();
SIList.TaxTypeObj.Code = sdr["TaxTypeCode"].ToString();
SIList.PaymentDueDate = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()) : SIList.PaymentDueDate);
SIList.TotalInvoiceAmount = (sdr["TotalInvoiceAmount"].ToString() != "" ? Decimal.Parse(sdr["TotalInvoiceAmount"].ToString()) : SIList.TotalInvoiceAmount);
SIList.BalanceDue = (sdr["BalanceDue"].ToString() != "" ? Decimal.Parse(sdr["BalanceDue"].ToString()) : SIList.BalanceDue);
SIList.LastPaymentDate = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()) : SIList.LastPaymentDate);
SIList.PaymentProcessed = (sdr["PaymentProcessed"].ToString() != "" ? Decimal.Parse(sdr["PaymentProcessed"].ToString()) : SIList.PaymentProcessed);
//------------date formatting-----------------//
SIList.InvoiceDateFormatted = (sdr["InvoiceDate"].ToString() != "" ? DateTime.Parse(sdr["InvoiceDate"].ToString()).ToString(settings.dateformat) : SIList.InvoiceDateFormatted);
SIList.PaymentDueDateFormatted = (sdr["PaymentDueDate"].ToString() != "" ? DateTime.Parse(sdr["PaymentDueDate"].ToString()).ToString(settings.dateformat) : SIList.PaymentDueDateFormatted);
SIList.LastPaymentDateFormatted = (sdr["LastPaymentDate"].ToString() != "" ? DateTime.Parse(sdr["LastPaymentDate"].ToString()).ToString(settings.dateformat) : SIList.LastPaymentDateFormatted);
SIList.EmpID = (sdr["SubType"].ToString() != "" ? Guid.Parse(sdr["SubType"].ToString()) : SIList.EmpID);
SIList.AccountCode = (sdr["AccountCode"].ToString() != "" ? (sdr["AccountCode"].ToString()) : SIList.AccountCode);
SIList.IsEmp = (sdr["ISEmpApplicable"].ToString() != "" ? bool.Parse(sdr["ISEmpApplicable"].ToString()) : false);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SIList);
}