public static List<Invoice> GetInvoicesDue()
{
List<Invoice> invoiceList = new List<Invoice>();
String selectStatement =
"SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, DueDate FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY DueDate";
using (SqlConnection connection = PayablesDB.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
PayablesData.Invoice invoice = new Invoice();
invoice.InvoiceNumber = reader["InvoiceNumber"].ToString();
invoice.InvoiceDate = (DateTime)reader["InvoiceDate"];
invoice.InvoiceTotal = (decimal)reader["InvoiceTotal"];
invoice.PaymentTotal = (decimal)reader["PaymentTotal"];
invoice.CreditTotal = (decimal)reader["CreditTotal"];
invoice.DueDate = (DateTime)reader["DueDate"];
invoiceList.Add(invoice);
}
reader.Close();
}
}
}
return invoiceList;
}
private void btnTransfer_click(object sender, RoutedEventArgs e)
{
if (Validator.IsPresent(FromTrsAmountTextBox) &&
Validator.IsDecimal(FromTrsAmountTextBox))
{
if (fromInvoice != null && toInvoice != null)
{
decimal transferAmount = Convert.ToDecimal(FromTrsAmountTextBox.Text);
if (transferAmount <= fromInvoice.PaymentTotal)
{
if (transferAmount <= toInvoice.BalanceDue)
{
try
{
if (InvoiceDB.TransferPayment(fromInvoice,
toInvoice, transferAmount))
{
string message =
"A payment of " +
transferAmount.ToString("c") +
" has been transferred from invoice number " +
fromInvoice.InvoiceNumber +
" to invoice number " +
toInvoice.InvoiceNumber + ".";
MessageBox.Show(message, "Transfer Complete");
}
else
{
MessageBox.Show("The transfer was not processed. " +
"Another user may have posted a payment to " +
"one of the invoices.", "Transfer Not Processed");
}
this.ClearControls();
fromInvoice = null;
toInvoice = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
MessageBox.Show("Transfer amount cannot be more " +
"than the balance due.", "Data Entry Error");
}
else
MessageBox.Show("Transfer amount cannot be more " +
"than the payment total.", "Data Entry Error");
}
else
MessageBox.Show("You must get the From and To invoices " +
"before transferring a payment.", "Data Entry Error");
}
else
{
MessageBox.Show("TransferAmount must be a decimal value or required field ...");
}
}
public static List<Invoice> GetUnpaidVendorInvoices(int vendorID)
{
SqlDataReader reader = null;
List<Invoice> invoiceList = new List<Invoice>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, " +
" InvoiceTotal, PaymentTotal, CreditTotal, " +
" TermsID, DueDate, PaymentDate " +
"FROM Invoices " +
"WHERE VendorID = @VendorID " +
" AND InvoiceTotal - PaymentTotal - CreditTotal > 0 " +
"ORDER BY InvoiceDate";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Invoice invoice = new Invoice();
invoice.InvoiceID = (int)reader["InvoiceID"];
invoice.VendorID = (int)reader["VendorID"];
invoice.InvoiceNumber = reader["InvoiceNumber"].ToString();
invoice.InvoiceDate = (DateTime)reader["InvoiceDate"];
invoice.InvoiceTotal = (decimal)reader["InvoiceTotal"];
invoice.PaymentTotal = (decimal)reader["PaymentTotal"];
invoice.CreditTotal = (decimal)reader["CreditTotal"];
invoice.TermsID = (int)reader["TermsID"];
invoice.DueDate = (DateTime)reader["DueDate"];
if (reader["PaymentDate"] == DBNull.Value)
invoice.PaymentDate = null;
else
invoice.PaymentDate = (DateTime)reader["PaymentDate"];
invoiceList.Add(invoice);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(reader!=null)
reader.Close();
if(connection!=null)
connection.Close();
}
return invoiceList;
}
public static Invoice GetInvoice(string invoiceNo)
{
SqlDataReader reader = null;
Invoice invoice = new Invoice();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal " +
"FROM Invoices " +
"WHERE InvoiceNumber = @InvoiceNumber";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@InvoiceNumber", invoiceNo);
try
{
connection.Open();
reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
invoice.InvoiceNumber = reader["InvoiceNumber"].ToString();
invoice.InvoiceDate = (DateTime)reader["InvoiceDate"];
invoice.InvoiceTotal = (decimal)reader["InvoiceTotal"];
invoice.PaymentTotal = (decimal)reader["PaymentTotal"];
}
else
{
invoice = null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(DataTableReader!=null)
{
reader.Close();
}
if(connection!=null)
{
connection.Close();
}
}
return invoice;
}
public static List<Invoice> GetInvoicesDue()
{
var invoiceList = new List<Invoice>();
// ReSharper disable once SuggestVarOrType_SimpleTypes
var connection = PayablesDb.GetConnection();
var selectStatement =
"SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, " +
"PaymentTotal, CreditTotal, DueDate, VendorId " +
"FROM Invoices " +
"WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 " +
"ORDER BY DueDate";
var selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
var reader = selectCommand.ExecuteReader();
while (reader.Read())
{
var invoice = new Invoice
{
InvoiceNumber = reader["InvoiceNumber"].ToString(),
InvoiceDate = (DateTime) reader["InvoiceDate"],
InvoiceTotal = (decimal) reader["InvoiceTotal"],
PaymentTotal = (decimal) reader["PaymentTotal"],
CreditTotal = (decimal) reader["CreditTotal"],
DueDate = (DateTime) reader["DueDate"],
VendorId = (int) reader["VendorId"]
};
invoiceList.Add(invoice);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return invoiceList;
}
public static bool TransferPayment(Invoice fromInvoice,
Invoice toInvoice, decimal payment)
{
SqlConnection connection = PayablesDB.GetConnection();
SqlTransaction paymentTran = null;
SqlCommand fromCommand = new SqlCommand();
fromCommand.Connection = connection;
fromCommand.CommandText =
"UPDATE Invoices " +
"SET PaymentTotal = PaymentTotal - @Payment " +
"WHERE InvoiceNumber = @InvoiceNumber " +
" AND PaymentTotal = @PaymentTotal";
fromCommand.Parameters.AddWithValue("@payment", payment);
fromCommand.Parameters.AddWithValue("@InvoiceNumber",
fromInvoice.InvoiceNumber);
fromCommand.Parameters.AddWithValue("@PaymentTotal",
fromInvoice.PaymentTotal);
SqlCommand toCommand = new SqlCommand();
toCommand.Connection = connection;
toCommand.CommandText =
"UPDATE Invoices " +
"SET PaymentTotal = PaymentTotal + @Payment " +
"WHERE InvoiceNumber = @InvoiceNumber " +
" AND PaymentTotal = @PaymentTotal";
toCommand.Parameters.AddWithValue("@payment", payment);
toCommand.Parameters.AddWithValue("@InvoiceNumber",
toInvoice.InvoiceNumber);
toCommand.Parameters.AddWithValue("@PaymentTotal",
toInvoice.PaymentTotal);
try
{
connection.Open();
paymentTran = connection.BeginTransaction();
fromCommand.Transaction = paymentTran;
toCommand.Transaction = paymentTran;
int count = fromCommand.ExecuteNonQuery();
if (count > 0)
{
count = toCommand.ExecuteNonQuery();
if (count > 0)
{
paymentTran.Commit();
return true;
}
else
{
paymentTran.Rollback();
return false;
}
}
else
{
paymentTran.Rollback();
return false;
}
}
catch (SqlException ex)
{
if (paymentTran != null)
paymentTran.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public static bool TransferPayment(Invoice fromInvoice,
Invoice toInvoice, decimal payment)
{
//Transactie declareren
SqlTransaction paymentTran = null;
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand fromCommand = new SqlCommand();
fromCommand.Connection = connection;
fromCommand.CommandText =
"UPDATE Invoices " +
"SET PaymentTotal = PaymentTotal - @Payment " +
"WHERE InvoiceNumber = @InvoiceNumber " +
" AND PaymentTotal = @PaymentTotal";
fromCommand.Parameters.AddWithValue("@payment", payment);
fromCommand.Parameters.AddWithValue("@InvoiceNumber",
fromInvoice.InvoiceNumber);
fromCommand.Parameters.AddWithValue("@PaymentTotal",
fromInvoice.PaymentTotal);
SqlCommand toCommand = new SqlCommand();
toCommand.Connection = connection;
toCommand.CommandText =
"UPDATE Invoices " +
"SET PaymentTotal = PaymentTotal + @Payment " +
"WHERE InvoiceNumber = @InvoiceNumber " +
" AND PaymentTotal = @PaymentTotal";
toCommand.Parameters.AddWithValue("@payment", payment);
toCommand.Parameters.AddWithValue("@InvoiceNumber",
toInvoice.InvoiceNumber);
toCommand.Parameters.AddWithValue("@PaymentTotal",
toInvoice.PaymentTotal);
try
{
connection.Open();
//Transactie initialiseren op basis van PayablesDBConnection
paymentTran = connection.BeginTransaction();
//Zowel to en from Transaction toewijzen aan het zelfde Transaction object van PayblesDBConnection
fromCommand.Transaction = paymentTran;
toCommand.Transaction = paymentTran;
int count = fromCommand.ExecuteNonQuery();
if (count > 0)
{
count = toCommand.ExecuteNonQuery();
if (count > 0)
{
paymentTran.Commit(); //Committen als er rijen gewijzigd zijn bij beide Commands
return true;
}
else
{
paymentTran.Rollback(); //Rollback als er geen rijen gewijzigd zijn bij het toCommand
return false;
}
}
else
{
paymentTran.Rollback(); //Rollback als er geen rijen gewijzigd zijn bij het fromCommand
return false;
}
}
catch (SqlException ex)
{
paymentTran.Rollback(); //Rollback bij exception
return false;
}
finally
{
connection.Close();
}
}
private void btnGetFromInvoice_click(object sender, RoutedEventArgs e)
{
if (Validator.IsPresent(FromInvNoTextBox))
{
fromInvoice = InvoiceDB.GetInvoice(FromInvNoTextBox.Text);
if (fromInvoice != null)
{
GrpFrom.DataContext = fromInvoice;
// DataContext = fromInvoice;
}
else
{
MessageBox.Show("An invoice was not found with that number.",
"FromInvoice Not Found");
}
}
else
{
MessageBox.Show("FromInvoice is a required Field ...");
}
}
private void btnGetToInvoice_click(object sender, RoutedEventArgs e)
{
if (Validator.IsPresent(ToInvNoTextBox))
{
toInvoice = InvoiceDB.GetInvoice(ToInvNoTextBox.Text);
if (toInvoice != null)
{
GrpBoxTO.DataContext = toInvoice;
//ToInvDateTextBox.Text = toInvoice.InvoiceDate.ToShortDateString();
//ToInvTotTextBox.Text = toInvoice.InvoiceTotal.ToString("c");
//ToBalanceDueTextBox.Text = toInvoice.BalanceDue().ToString("c");
}
else
{
MessageBox.Show("An invoice was not found with that number.",
"ToInvoice Not Found");
}
}
else
{
MessageBox.Show("ToInvoice is a required Field ...");
}
}
public static bool UpdatePayment(Invoice oldInvoice,
Invoice newInvoice)
{
SqlConnection connection = PayablesDB.GetConnection();
string updateStatement =
"UPDATE Invoices " +
"SET PaymentTotal = @NewPaymentTotal, " +
" PaymentDate = @NewPaymentDate " +
"WHERE InvoiceID = @OldInvoiceID "; //+
// " AND VendorID = @OldVendorID " +
// " AND InvoiceNumber = @OldInvoiceNumber " +
// " AND InvoiceDate = @OldInvoiceDate " +
// " AND InvoiceTotal = @OldInvoiceTotal " +
// " AND PaymentTotal = @OldPaymentTotal " +
// " AND CreditTotal = @OldCreditTotal " +
// " AND TermsID = @OldTermsID " +
// " AND DueDate = @OldDueDate " +
// " AND (PaymentDate = @OldPaymentDate " +
// " OR PaymentDate IS NULL AND @OldPaymentDate IS NULL)";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewPaymentTotal",
newInvoice.PaymentTotal);
updateCommand.Parameters.AddWithValue("@NewPaymentDate",
newInvoice.PaymentDate);
updateCommand.Parameters.AddWithValue("@OldInvoiceID",
oldInvoice.InvoiceID);
updateCommand.Parameters.AddWithValue("@OldVendorID",
oldInvoice.VendorID);
updateCommand.Parameters.AddWithValue("@OldInvoiceNumber",
oldInvoice.InvoiceNumber);
updateCommand.Parameters.AddWithValue("@OldInvoiceDate",
oldInvoice.InvoiceDate);
updateCommand.Parameters.AddWithValue("@OldInvoiceTotal",
oldInvoice.InvoiceTotal);
updateCommand.Parameters.AddWithValue("@OldPaymentTotal",
oldInvoice.PaymentTotal);
updateCommand.Parameters.AddWithValue("@OldCreditTotal",
oldInvoice.CreditTotal);
updateCommand.Parameters.AddWithValue("@OldTermsID",
oldInvoice.TermsID);
updateCommand.Parameters.AddWithValue("@OldDueDate",
oldInvoice.DueDate);
if (!oldInvoice.PaymentDate.HasValue)
updateCommand.Parameters.AddWithValue("@OldPaymentDate",
DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@OldPaymentDate",
oldInvoice.PaymentDate);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}