public void OnGet(string arCode, string transactionId, string invoiceNumber)
{
invoiceBalance = getData.GetInvoiceBalance(arCode, transactionId);
_invoiceNumber = invoiceNumber;
accountHeader = getData.GetAccountHeaderByArCode(arCode);
comments = getData.GetComments(arCode, transactionId);
}
public bool SaveTotalAr(InvoiceBalance invoiceBalance)
{
using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
{
sqlCon.Open();
var sql = "INSERT INTO TotalAR(UploadDate,Balance,Curr,Over30,Over60,Over90) " +
$"VALUES('{DateTime.Now}',{invoiceBalance.Balance},{invoiceBalance.Curr},{invoiceBalance.Over30},{invoiceBalance.Over60},{invoiceBalance.Over90})";
using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var err = new CreateLogFiles();
err.ErrorLog(Config.DataPath + "err.log", ex.Message + "Error inserting total ar: " + sql);
return(false);
throw;
}
}
}
return(true);
}
public InvoiceBalance GetInvoiceBalance(string arCode, string transactionId)
{
var invoiceBalance = new InvoiceBalance();
string sql = string.Empty;
try
{
using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
{
sqlCon.Open();
sql = $"SELECT DISTINCT * FROM InvoiceBalance WHERE ArCode = '{arCode}' AND TransactionId = {transactionId}";
using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
var property = invoiceBalance.GetType().GetProperty(fieldName);
if (property != null && !reader.IsDBNull(i))
{
property.SetValue(invoiceBalance, reader.GetValue(i), null);
}
}
}
}
}
}
}
catch (Exception ex)
{
var Err = new CreateLogFiles();
Err.ErrorLog(Config.WebDataPath + "err.log", "Error on GetInvoiceBalance" + ex.Message + "Error on this query" + sql);
throw;
}
return(invoiceBalance);
}
public string InvoiceOpeningBalanceSelect(long customer_id, long invoice_id)
{
List <InvoiceBalance> ib = new List <InvoiceBalance>();
var invoices = Database._InvoiceSalesCustomerSelect(customer_id).Where(x => x.InvoiceType == "Opening Balance");;
var payment = Database._InvoicePaymentSelect(0, 0);
foreach (var i in invoices)
{
long _id = i.ID;
string _refNo = i.RefNo;
string _refSerial = i.RefNoSerial;
DateTime _createdDate = i.CreatedDate.Value;
string _description = i.InvoiceStatus;
string _salesman = i.Salesman;
decimal _totalAmount = i.TotalAmount;
string _customerName = i.CustomerName;
string _status = i.InvoiceStatus;
decimal _comm_rate = i.CommissionRate.Value;
long _terms_id = i.TermID.Value;
decimal _balance = 0;
var invoice_payment = Database._InvoicePaymentSelectCustomer(0, 0, i.ID, 0);
foreach (var bal in invoice_payment)
{
_balance += (bal.Amount.Value);
}
var sales_return = Database._invoicePaymentSalesReturns.Where(x => x.InvoiceID == i.ID);
foreach (var ret in sales_return)
{
_balance += ret.Amount.Value;
}
decimal compute = (_totalAmount - _balance);
InvoiceBalance invBal = new InvoiceBalance();
invBal.ID = _id;
invBal.RefNo = _refNo;
invBal.RefSerial = _refSerial;
invBal.CreatedDate = _createdDate;
invBal.CustomerName = _customerName;
invBal.Description = _description;
invBal.Salesman = _salesman;
invBal.TotalAmount = _totalAmount;
invBal.Balance = compute;
invBal.Status = _status;
invBal.CommRate = _comm_rate;
invBal.TermID = _terms_id;
ib.Add(invBal);
//if (compute > 0)
//{
// ib.Add(new InvoiceBalance(_id, _refNo, _createdDate, _description, _salesman, _totalAmount, compute));
//}
}
if (invoice_id > 0)
{
var ret = ib.Where(x => x.ID == invoice_id);
return(JsonConvert.SerializeObject(ret, Newtonsoft.Json.Formatting.Indented));
}
return(JsonConvert.SerializeObject(ib, Newtonsoft.Json.Formatting.Indented));
//var data = ib;
//repMain.DataSource = data;
//repMain.DataBind();
}
private void LoadData()
{
List <InvoiceBalance> ib = new List <InvoiceBalance>();
var invoices = Database._InvoiceSalesCustomerSelect(0);
var payment = Database._InvoicePaymentSelect(0, 0);
foreach (var i in invoices)
{
long _id = i.ID;
string _refNo = i.RefNo;
DateTime _createdDate = i.CreatedDate.Value;
string _description = i.InvoiceStatus;
string _salesman = i.Salesman;
decimal _totalAmount = i.TotalAmount;
string _customerName = i.CustomerName;
string _status = i.InvoiceStatus;
decimal _balance = 0;
var invoice_payment = Database._InvoicePaymentSelectCustomer(0, 0, i.ID, 0);
foreach (var bal in invoice_payment)
{
_balance += (bal.Amount.Value);
}
var sales_return = Database._invoicePaymentSalesReturns.Where(x => x.InvoiceID == i.ID);
foreach (var ret in sales_return)
{
_balance += ret.Amount.Value;
}
decimal compute = (_totalAmount - _balance);
InvoiceBalance invBal = new InvoiceBalance();
invBal.ID = _id;
invBal.RefNo = _refNo;
invBal.CreatedDate = _createdDate;
invBal.CustomerName = _customerName;
invBal.Description = _description;
invBal.Salesman = _salesman;
invBal.TotalAmount = _totalAmount;
invBal.Balance = compute;
invBal.Status = _status;
ib.Add(invBal);
//if (compute > 0)
//{
// ib.Add(new InvoiceBalance(_id, _refNo, _createdDate, _description, _salesman, _totalAmount, compute));
//}
}
var data = ib;
repMain.DataSource = data;
repMain.DataBind();
}