public JsonResult GetPaymentByPaymentMode(string PaymentMode)
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
tbpayments PaymentsDetails = new tbpayments();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"select * from tbpayments where ModeOfPayment = '{PaymentMode}'";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
PaymentsDetails.PaymentId = Convert.ToInt32(dataReader["PaymentId"]);
PaymentsDetails.InvoiceId = Convert.ToInt32(dataReader["InvoiceId"]);
PaymentsDetails.InvoiceTotalAmt = Convert.ToInt32(dataReader["InvoiceTotalAmt"]);
PaymentsDetails.AmtPaidSoFar = Convert.ToInt32(dataReader["AmtPaidSoFar"]);
PaymentsDetails.AmtPaidNow = Convert.ToInt32(dataReader["AmtPaidNow"]);
PaymentsDetails.Balance = Convert.ToInt32(dataReader["Balance"]);
PaymentsDetails.ModeOfPayment = Convert.ToString(dataReader["ModeOfPayment"]);
PaymentsDetails.PaymentDate = Convert.ToDateTime(dataReader["PaymentDate"]);
PaymentsDetails.DateCreated = Convert.ToDateTime(dataReader["DateCreated"]);
}
}
connection.Close();
return(Json(ResponseData.SendSuccessMsg(message: null, data: PaymentsDetails)));
}
}
public async Task <JsonResult> MakePayments(tbpayments paymentdetails)
{
var connectionString = Configuration["ConnectionStrings:DefaultConnection"];
var DateCreated = DateTime.Now;
var getInvoiceDetails = $"select TotalAmt from TbInvoice where InvoiceId = {paymentdetails.InvoiceId}";
var getThePaidAmountSofarOnInvoice = $"select sum(AmtPaidNow) as AMTPAIDSOFAR from TbPayments where InvoiceId = {paymentdetails.InvoiceId}";
//Create and open a connection to SQL Server
SqlConnection connection = new SqlConnection(connectionString);
SqlConnection connection2 = new SqlConnection(connectionString);
SqlConnection connection3 = new SqlConnection(connectionString);
SqlConnection connection4 = new SqlConnection(connectionString);
connection.Open();
connection2.Open();
//Create a Command object
SqlCommand command = new SqlCommand(getInvoiceDetails, connection);
SqlCommand command2 = new SqlCommand(getThePaidAmountSofarOnInvoice, connection2);
//Create DataReader for storing the returning table into server memory
SqlDataReader dataReader = command.ExecuteReader();
SqlDataReader dataReader2 = command2.ExecuteReader();
int InvoiceTotalAmount = 0;
int TotalAmountPaidSofarOnTheInvoice = 0;
string InvoiceStatus = null;
//load into the result object the returned row from the database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
InvoiceTotalAmount = Convert.ToInt32(dataReader["TotalAmt"]);
}
}
if (dataReader2.HasRows)
{
while (dataReader.Read())
{
//if (!dataReader2.IsDBNull(1))
//{
//}
//else
//{
// TotalAmountPaidSofarOnTheInvoice = 0;
//}
TotalAmountPaidSofarOnTheInvoice = Convert.ToInt32(dataReader["AMTPAIDSOFAR"]);
}
}
if (paymentdetails.AmtPaidNow + TotalAmountPaidSofarOnTheInvoice >= InvoiceTotalAmount)
{
InvoiceStatus = "Fully Paid";
}
if (paymentdetails.AmtPaidNow + TotalAmountPaidSofarOnTheInvoice <= InvoiceTotalAmount)
{
InvoiceStatus = "Partially Paid";
}
int Balance = 0;
Balance = InvoiceTotalAmount - TotalAmountPaidSofarOnTheInvoice + (int)paymentdetails.AmtPaidNow;
//------ perform the Main Insert -------------
var XXX = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
var insertQuery = $" INSERT INTO TbPayments(InvoiceId ,InvoiceTotalAmt,AmtPaidSoFar ,AmtPaidNow ,Balance,PaymentDate ,ModeOfPayment ,DateCreated)" +
$" VALUES('{paymentdetails.InvoiceId}', {InvoiceTotalAmount}, {TotalAmountPaidSofarOnTheInvoice}, {paymentdetails.AmtPaidNow}, {Balance}, '{DateCreated}', 'CASH', '{DateCreated}')";
conn.Open();
//Create a Command object
SqlCommand MainCommand = new SqlCommand(insertQuery, conn);
// var comandResult = command.ExecuteNonQuery();
var commandResult = MainCommand.ExecuteScalar();
int NewPaymentId = Convert.ToInt32(commandResult);
XXX = NewPaymentId;
//Close and dispose
command2.Dispose();
connection2.Close();
connection2.Dispose();
}
int TheAmoutPaidAllTogether = 0;
TheAmoutPaidAllTogether = TotalAmountPaidSofarOnTheInvoice + (int)paymentdetails.AmtPaidNow;
//----- update the Invoice Table on the status of the invoice
var changeInvoiceStatus = $"update TbInvoice set Status = '{InvoiceStatus}' where " +
$"InvoiceId = {paymentdetails.InvoiceId}";
connection4.Open();
//Create a Command object
SqlCommand command3 = new SqlCommand(changeInvoiceStatus, connection4);
// var comandResult = command.ExecuteNonQuery();
var comandResult3 = command3.ExecuteScalar();
return(Json(ResponseData.SendSuccessMsg(message: $"You have succesfully made a payment with PaymentId = {XXX} on the Invoice Id {paymentdetails.InvoiceId} the Invoice is {InvoiceStatus} with a total amount paid so far on" +
$"this invoice is = {TheAmoutPaidAllTogether}", data: null)));
}
