public static List <Terms> GetTermsList()
{
List <Terms> termsList = new List <Terms>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT TermsID, Description, DueDays " +
"FROM Terms " +
"ORDER BY Description";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Terms term = new Terms();
term.TermsID = (int)reader["TermsID"];
term.Description = reader["Description"].ToString();
// Couldn't unbox with (int); don't know why
term.DueDays = Convert.ToInt32(reader["DueDays"]);
termsList.Add(term);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(termsList);
}
public static List <State> GetStateList()
{
List <State> stateList = new List <State>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT StateCode, StateName, FirstZipCode, LastZipCode " +
"FROM States " +
"ORDER BY StateName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
State state = new State();
state.StateCode = reader["StateCode"].ToString();
state.StateName = reader["StateName"].ToString();
state.FirstZipCode = (int)reader["FirstZipCode"];
state.LastZipCode = (int)reader["LastZipCode"];
stateList.Add(state);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(stateList);
}
public static List <GLAccount> GetGLAccountList()
{
List <GLAccount> accountList = new List <GLAccount>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT AccountNo, Description " +
"FROM GLAccounts " +
"ORDER BY Description";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
GLAccount account = new GLAccount();
account.AccountNo = (int)reader["AccountNo"];
account.Description = reader["Description"].ToString();
accountList.Add(account);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(accountList);
}
public static IList <GLAccount> GetAll()
{
var accountList = new List <GLAccount>();
string selectStatement =
"SELECT AccountNo, Description " +
"FROM GLAccounts " +
"ORDER BY Description";
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
GLAccount account = new GLAccount
{
AccountNo = (int)reader["AccountNo"],
Description = reader["Description"].ToString()
};
accountList.Add(account);
}
}
finally
{
reader?.Close();
connection?.Close();
}
return(accountList);
}
public static int WritePayable(Payable payable)
{
try
{
connection = PayablesDB.GetConnection();
connection.Open();
payableTran = connection.BeginTransaction();
payableCommand = new SqlCommand();
payableCommand.Connection = connection;
payableCommand.Transaction = payableTran;
int invoiceID = InsertInvoice(payable);
int invoiceSequence = 0;
foreach (LineItem li in payable.LineItems)
{
li.InvoiceID = invoiceID;
invoiceSequence += 1;
li.InvoiceSequence = invoiceSequence;
InsertLineItem(li);
}
payableTran.Commit();
return(invoiceID);
}
catch (SqlException ex)
{
payableTran.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Invoice> GetVendorInvoices(int vendorID)
{
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 " +
"ORDER BY InvoiceDate";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
SqlDataReader 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);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(invoiceList);
}
public static Vendor GetById(int vendorId)
{
Vendor vendor = null;
string selectStatement =
"SELECT VendorID, Name, Address1, Address2, City, State, " +
"ZipCode, Phone, ContactFName, ContactLName, " +
"DefaultAccountNo, DefaultTermsID " +
"FROM Vendors " +
"WHERE VendorID = @VendorID";
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorId);
//SqlParameter vendorIdParam = new SqlParameter("@VendorID", vendorId);
//selectCommand.Parameters.Add(vendorIdParam);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
vendor = new Vendor
{
VendorId = (int)reader["VendorID"],
Name = reader["Name"].ToString(),
Address1 = reader["Address1"].ToString(),
Address2 = reader["Address2"].ToString(),
City = reader["City"].ToString(),
State = reader["State"].ToString(),
ZipCode = reader["ZipCode"].ToString(),
Phone = reader["Phone"].ToString(),
ContactLName = reader["ContactLName"].ToString(),
ContactFName = reader["ContactFName"].ToString(),
DefaultAccountNo = (int)reader["DefaultAccountNo"],
DefaultTermsId = (int)reader["DefaultTermsID"]
};
}
}
finally
{
reader?.Close();
connection?.Close();
}
return(vendor);
}
public static Vendor GetVendor(int vendorID)
{
Vendor vendor = new Vendor();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT VendorID, Name, Address1, Address2, City, State, " +
"ZipCode, Phone, ContactFName, ContactLName, " +
"DefaultAccountNo, DefaultTermsID " +
"FROM Vendors " +
"WHERE VendorID = @VendorID";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VendorID", vendorID);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendor.Address1 = reader["Address1"].ToString();
vendor.Address2 = reader["Address2"].ToString();
vendor.City = reader["City"].ToString();
vendor.State = reader["State"].ToString();
vendor.ZipCode = reader["ZipCode"].ToString();
vendor.Phone = reader["Phone"].ToString();
vendor.ContactLName = reader["ContactLName"].ToString();
vendor.ContactFName = reader["ContactFName"].ToString();
vendor.DefaultAccountNo = (int)reader["DefaultAccountNo"];
vendor.DefaultTermsID = (int)reader["DefaultTermsID"];
}
else
{
vendor = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(vendor);
}
public static bool TransferPayment(Invoice fromInvoice,
Invoice toInvoice,
decimal payment)
{
SqlTransaction transaction = null;
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand fromCommand = CreateUpdatePaymentTotalCommand(fromInvoice, -payment, connection); //notice negative payment
SqlCommand toCommand = CreateUpdatePaymentTotalCommand(toInvoice, payment, connection);
try
{
connection.Open();
transaction = connection.BeginTransaction();
fromCommand.Transaction = transaction;
toCommand.Transaction = transaction;
int numberOfRowsAffected = fromCommand.ExecuteNonQuery();
if (numberOfRowsAffected > 0)
{
numberOfRowsAffected = toCommand.ExecuteNonQuery();
if (numberOfRowsAffected > 0)
{
transaction.Commit();
return(true);
}
else
{
transaction.Rollback();
return(false);
}
}
else
{
transaction.Rollback();
return(false);
}
}
catch (SqlException)
{
transaction?.Rollback();
throw;
}
finally
{
connection?.Close();
}
}
public static List <Vendor> GetSelectedVendors(string name, string state)
{
List <Vendor> vendorList = new List <Vendor>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT VendorID, Name, Address1, Address2, City, State, " +
" ZipCode, Phone, ContactFName, ContactLName, " +
" DefaultAccountNo, DefaultTermsID " +
"FROM Vendors " +
"WHERE (Name LIKE @Name) AND (State LIKE @State) " +
"ORDER BY Name";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@Name", name + "%");
selectCommand.Parameters.AddWithValue("@State", state + "%");
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Vendor vendor = new Vendor();
vendor.VendorID = (int)reader["VendorID"];
vendor.Name = reader["Name"].ToString();
vendor.Address1 = reader["Address1"].ToString();
vendor.Address2 = reader["Address2"].ToString();
vendor.City = reader["City"].ToString();
vendor.State = reader["State"].ToString();
vendor.ZipCode = reader["ZipCode"].ToString();
vendor.Phone = reader["Phone"].ToString();
vendor.ContactFName = reader["ContactFName"].ToString();
vendor.ContactLName = reader["ContactLName"].ToString();
vendor.DefaultAccountNo = (int)reader["DefaultAccountNo"];
vendor.DefaultTermsID = (int)reader["DefaultTermsID"];
vendorList.Add(vendor);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(vendorList);
}
public static bool Update(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
var updateCommand = CreateUpdateCommand(vendor, connection);
try
{
connection.Open();
int numberOfRowsAffected = updateCommand.ExecuteNonQuery();
return(numberOfRowsAffected > 0);
}
finally
{
connection?.Close();
}
}
/// <summary>
/// Adds a vendor to the database
/// </summary>
/// <param name="vendor">The vendor to be added</param>
/// <returns>The id of the inserted vendor</returns>
public static int Add(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand insertCommand = CreateInsertCommand(vendor, connection);
try
{
connection.Open();
int createdVendorId = (int)insertCommand.ExecuteScalar();
return(createdVendorId);
}
finally
{
connection?.Close();
}
}
public static List <LineItem> GetInvoiceLineItems(int invoiceID)
{
List <LineItem> lineItemList = new List <LineItem>();
SqlConnection connection = PayablesDB.GetConnection();
string selectStatement =
"SELECT InvoiceID, InvoiceSequence, InvoiceLineItems.AccountNo, " +
"GLAccounts.Description AS AccountDescription, " +
"Amount, InvoiceLineItems.Description " +
"FROM InvoiceLineItems JOIN GLAccounts " +
" ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo " +
"WHERE InvoiceID = @InvoiceID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@InvoiceID", invoiceID);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
LineItem lineItem = new LineItem();
lineItem.InvoiceID = (int)reader["InvoiceID"];
lineItem.InvoiceSequence = Convert.ToInt32(reader["InvoiceSequence"]);
lineItem.AccountNo = (int)reader["AccountNo"];
lineItem.AccountDescription = reader["AccountDescription"].ToString();
lineItem.Amount = (decimal)reader["Amount"];
lineItem.Description = reader["Description"].ToString();
lineItemList.Add(lineItem);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(lineItemList);
}
public static Invoice GetInvoice(string invoiceNumber)
{
Invoice invoice = null;
string selectStatement =
"SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal " +
"FROM Invoices " +
"WHERE InvoiceNumber = @InvoiceNumber";
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@InvoiceNumber", invoiceNumber);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
invoice = new Invoice
{
InvoiceNumber = reader["InvoiceNumber"].ToString(),
InvoiceDate = (DateTime)reader["InvoiceDate"],
InvoiceTotal = (decimal)reader["InvoiceTotal"],
PaymentTotal = (decimal)reader["PaymentTotal"]
};
}
}
finally
{
reader?.Close();
connection?.Close();
}
return(invoice);
}
public static IList <State> GetAll()
{
var states = new List <State>();
string selectStatement =
"SELECT StateCode, StateName, FirstZipCode, LastZipCode " +
"FROM States " +
"ORDER BY StateName";
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
State state = new State
{
StateCode = reader["StateCode"].ToString(),
StateName = reader["StateName"].ToString(),
FirstZipCode = (int)reader["FirstZipCode"],
LastZipCode = (int)reader["LastZipCode"]
};
states.Add(state);
}
}
finally
{
reader?.Close();
connection?.Close();
}
return(states);
}
public static List <Terms> GetAll()
{
var allTerms = new List <Terms>();
string selectStatement =
"SELECT TermsID, Description, DueDays " +
"FROM Terms " +
"ORDER BY Description";
SqlConnection connection = PayablesDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Terms terms = new Terms
{
TermsId = Convert.ToInt32(reader["TermsID"]),
Description = reader["Description"].ToString(),
DueDays = Convert.ToInt32(reader["DueDays"])
};
allTerms.Add(terms);
}
}
finally
{
reader?.Close();
connection?.Close();
}
return(allTerms);
}
public static int AddVendor(Vendor vendor)
{
SqlConnection connection = PayablesDB.GetConnection();
string insertStatement =
"INSERT Vendors " +
"(Name, Address1, Address2, City, State, ZipCode, Phone, " +
"ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) " +
"VALUES (@Name, @Address1, @Address2, @City, @State, @ZipCode, " +
"@Phone, @ContactFName, @ContactLName, @DefaultTermsID, " +
"@DefaultAccountNo)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@Name", vendor.Name);
insertCommand.Parameters.AddWithValue("@Address1", vendor.Address1);
if (vendor.Address2 == null)
{
insertCommand.Parameters.AddWithValue("@Address2", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Address2",
vendor.Address2);
}
insertCommand.Parameters.AddWithValue("@City", vendor.City);
insertCommand.Parameters.AddWithValue("@State", vendor.State);
insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode);
if (vendor.Phone == null)
{
insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone);
}
if (vendor.ContactFName == null)
{
insertCommand.Parameters.AddWithValue("@ContactFName",
DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@ContactFName",
vendor.ContactFName);
}
if (vendor.ContactLName == null)
{
insertCommand.Parameters.AddWithValue("@ContactLName",
DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@ContactLName",
vendor.ContactLName);
}
insertCommand.Parameters.AddWithValue("@DefaultTermsID",
vendor.DefaultTermsID);
insertCommand.Parameters.AddWithValue("@DefaultAccountNo",
vendor.DefaultAccountNo);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Vendors') FROM Vendors";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int vendorID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(vendorID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateVendor(Vendor oldVendor, Vendor newVendor)
{
// The columns that can have null values are null in the
// oldVendor and newVendor objects if the vendor was just added
// but are an empty string for an existing vendor. Because
// of that, the properties are checked for both nulls and
// empty strings. This wasn't necessary in the VB application
// because these properties were always empty strings. Since a
// null value can't be displayed in a text box, they should be
// converted to strings but apparently aren't.
SqlConnection connection = PayablesDB.GetConnection();
string updateStatement =
"UPDATE Vendors SET " +
"Name = @NewName, " +
"Address1 = @NewAddress1, " +
"Address2 = @NewAddress2, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode, " +
"Phone = @NewPhone, " +
"ContactFName = @NewContactFName, " +
"ContactLName = @NewContactLName, " +
"DefaultTermsID = @NewDefaultTermsID, " +
"DefaultAccountNo = @NewDefaultAccountNo " +
"WHERE VendorID = @OldVendorID " +
"AND Name = @OldName " +
"AND Address1 = @OldAddress1 " +
"AND (Address2 = @OldAddress2 " +
"OR Address2 IS NULL AND @OldAddress2 IS NULL) " +
"AND City = @OldCity " +
"AND State = @OldState " +
"AND ZipCode = @OldZipCode " +
"AND (Phone = @OldPhone " +
"OR Phone IS NULL AND @OldPhone IS NULL) " +
"AND (ContactFName = @OldContactFName " +
"OR ContactFName IS NULL AND @OldContactFName IS NULL) " +
"AND (ContactLName = @OldContactLName " +
"OR ContactLName IS NULL AND @OldContactLName IS NULL) " +
"AND DefaultTermsID = @OldDefaultTermsID " +
"AND DefaultAccountNo = @OldDefaultAccountNo";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewName", newVendor.Name);
updateCommand.Parameters.AddWithValue("@NewAddress1", newVendor.Address1);
if (newVendor.Address2 == "" || newVendor.Address2 == null)
{
updateCommand.Parameters.AddWithValue("@NewAddress2", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewAddress2",
newVendor.Address2);
}
updateCommand.Parameters.AddWithValue("@NewCity", newVendor.City);
updateCommand.Parameters.AddWithValue("@NewState", newVendor.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newVendor.ZipCode);
if (newVendor.Phone == "" || newVendor.Phone == null)
{
updateCommand.Parameters.AddWithValue("@NewPhone", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewPhone", newVendor.Phone);
}
if (newVendor.ContactFName == "" || newVendor.ContactFName == null)
{
updateCommand.Parameters.AddWithValue("@NewContactFName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewContactFName",
newVendor.ContactFName);
}
if (newVendor.ContactLName == "" || newVendor.ContactLName == null)
{
updateCommand.Parameters.AddWithValue("@NewContactLName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@NewContactLName",
newVendor.ContactLName);
}
updateCommand.Parameters.AddWithValue("@NewDefaultTermsID",
newVendor.DefaultTermsID);
updateCommand.Parameters.AddWithValue("@NewDefaultAccountNo",
newVendor.DefaultAccountNo);
updateCommand.Parameters.AddWithValue("@OldVendorID", oldVendor.VendorID);
updateCommand.Parameters.AddWithValue("@OldName", oldVendor.Name);
updateCommand.Parameters.AddWithValue("@OldAddress1", oldVendor.Address1);
if (oldVendor.Address2 == "" || oldVendor.Address2 == null)
{
updateCommand.Parameters.AddWithValue("@OldAddress2", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldAddress2",
oldVendor.Address2);
}
updateCommand.Parameters.AddWithValue("@OldCity", oldVendor.City);
updateCommand.Parameters.AddWithValue("@OldState", oldVendor.State);
updateCommand.Parameters.AddWithValue("@OldZipCode", oldVendor.ZipCode);
if (oldVendor.Phone == "" || oldVendor.Phone == null)
{
updateCommand.Parameters.AddWithValue("@OldPhone", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldPhone", oldVendor.Phone);
}
if (oldVendor.ContactFName == "" || oldVendor.ContactFName == null)
{
updateCommand.Parameters.AddWithValue("@OldContactFName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldContactFName",
oldVendor.ContactFName);
}
if (oldVendor.ContactLName == "" || oldVendor.ContactLName == null)
{
updateCommand.Parameters.AddWithValue("@OldContactLName",
DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@OldContactLName",
oldVendor.ContactLName);
}
updateCommand.Parameters.AddWithValue("@OldDefaultTermsID",
oldVendor.DefaultTermsID);
updateCommand.Parameters.AddWithValue("@OldDefaultAccountNo",
oldVendor.DefaultAccountNo);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}