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