/// <summary>
/// Deletes all the bill rows from the given bill (from the database).
/// </summary>
/// <param name="bill">The bill which rows are wanted to be removed.</param>
public static void DeleteAllBillRowsFromBill(Bill bill)
{
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = con;
command.CommandText = "DELETE FROM billRows WHERE billId=@id";
command.Parameters.AddWithValue("@id", bill.BillId);
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
/// <summary>
/// Saves the bill rows from the specified bill in to the database.
/// </summary>
/// <param name="bill"></param>
public static void SaveBillRows(Bill bill)
{
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
foreach (BillRow row in bill.Rows)
{
MySqlCommand command = new MySqlCommand();
command.Connection = con;
command.CommandText = "INSERT INTO billRows (productName, price, amount, unit, ale, accountId, accountName, billId, vat) VALUES (@name, @price, @amount, @unit, @ale, @accountId, @accountName, @billId, @vat)";
command.Parameters.AddWithValue("@name", row.ProductName);
command.Parameters.AddWithValue("@price", row.ProductPrice);
command.Parameters.AddWithValue("@amount", row.ProductAmount);
command.Parameters.AddWithValue("@unit", row.Unit);
command.Parameters.AddWithValue("@ale", row.Ale);
command.Parameters.AddWithValue("@vat", row.Vat);
command.Parameters.AddWithValue("@accountId", row.AccountId);
command.Parameters.AddWithValue("@accountName", row.AccountName);
command.Parameters.AddWithValue("@billId", bill.BillId);
command.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
/// <summary>
/// Saves the given bill in to the database.
/// </summary>
/// <param name="bill">Bill to be saved.</param>
/// <returns>Saved or not.</returns>
public static bool SaveBill(Bill bill)
{
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = con;
command.CommandText = "INSERT INTO bills (customerName, customerAddress, billDate, dueDate, billId, referenceNumber, documentId, paymentType, paid) VALUES (@name, @address, @billDate, @dueDate, @billId, @referenceNumber, @documentId, @paymentType, @paid)";
command.Parameters.AddWithValue("@name", bill.Customer);
command.Parameters.AddWithValue("@address", bill.Address);
command.Parameters.AddWithValue("@billDate", bill.BillDate);
command.Parameters.AddWithValue("@dueDate", bill.DueDate);
command.Parameters.AddWithValue("@billId", bill.BillId);
command.Parameters.AddWithValue("@referenceNumber", bill.ReferenceNumber);
command.Parameters.AddWithValue("@documentId", 0);
command.Parameters.AddWithValue("@paymentType", bill.PaymentType);
command.Parameters.AddWithValue("@paid", bill.Paid);
command.ExecuteNonQuery();
SaveBillRows(bill);
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
return true;
}
/// <summary>
/// Returns the bill object retrieved from the database with the specified id.
/// </summary>
/// <param name="billId">Bill's id.</param>
/// <returns>The bill.</returns>
public static Bill GetBillById(int billId)
{
Bill bill = new Bill();
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand();
try
{
con.Open();
command.Connection = con;
command.CommandText = "SELECT * FROM bills WHERE billId=@billId";
command.Prepare();
command.Parameters.AddWithValue("@billId", billId);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
bill.Customer = reader.GetString("customerName");
bill.Address = reader.GetString("customerAddress");
bill.BillDate = reader.GetDateTime("billDate");
bill.DueDate = reader.GetDateTime("dueDate");
bill.BillId = reader.GetInt16("billId");
bill.PaymentType = reader.GetInt16("paymentType");
bill.ReferenceNumber = reader.GetString("referenceNumber");
bill.Paid = reader.GetBoolean("paid");
bill.Rows = GetBillRowsByBillId(bill.BillId);
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
return bill;
}
/// <summary>
/// Returns the list containing all bills saved in to the database.
/// </summary>
/// <returns>The list of all bills</returns>
public static List<Bill> GetAllBills()
{
List<Bill> documents = new List<Bill>();
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand();
try
{
con.Open();
command.Connection = con;
command.CommandText = "SELECT * FROM bills";
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Bill bill = new Bill();
bill.Customer = reader.GetString("customerName");
bill.Address = reader.GetString("customerAddress");
bill.BillDate = reader.GetDateTime("billDate");
bill.DueDate = reader.GetDateTime("dueDate");
bill.BillId = reader.GetInt16("billId");
bill.PaymentType = reader.GetInt16("paymentType");
bill.Paid = reader.GetBoolean("paid");
bill.ReferenceNumber = reader.GetString("referenceNumber");
bill.Rows = GetBillRowsByBillId(bill.BillId);
documents.Add(bill);
}
}
catch (MySqlException ex)
{
throw ex;
} finally
{
con.Close();
}
}
return documents;
}