public static Invoice[] listInvoices(DateTime startDate, DateTime endDate, String customer)
{
Invoice[] invoices = new Invoice[0];
Dal dal = new Dal();
String startdate = startDate.Year + "-" + startDate.Month + "-" + startDate.Day;
String enddate = endDate.Year + "-" + endDate.Month + "-" + endDate.Day;
String query = "";
if ((customer != null)&&(!customer.Equals("")))
{
query = "call listInvoicesByDateAndCustomer('" + startdate + "','" + enddate + "','%" + customer + "%');";
}
else
{
query = "call listInvoicesByDateAndCustomer('" + startdate + "','" + enddate + "',null);";
}
HashSet<Hashtable> results = dal.executeAsHashset(query);
invoices = new Invoice[results.Count];
int counter = 0;
DateTime now = DateTime.Now;
foreach (Hashtable table in results)
{
Invoice invoice = new Invoice(table);
invoices[counter] = invoice;
counter++;
}
DateTime after = DateTime.Now;
//now.Subtract(after)
return invoices;
}
public Report(ReportType reportType)
{
try
{
this.reporttype = reportType;
dal = new Dal();
switch (this.reporttype)
{
case ReportType.IncomeExpenses:
this.reporttitle = "Income & Expenses";
updateEntries();
break;
case ReportType.ProductSales:
this.reporttitle = "Product Sales";
updateEntries();
break;
}
}
catch (Exception ex)
{
throw new Exception("Unable to load report " + reportType.ToString(), ex);
}
}
public Statement(Customer customer, Boolean zeroBased, DateTime startdate, DateTime enddate)
{
this.customer = customer;
this.zerobased = zeroBased;
this.filtered = true;
this.startdate = startdate;
this.enddate = enddate;
this.openingbalance = 0;
this.closingbalance = customer.Balance;
try
{
String start_datetimestring = startdate.Year + "-" + startdate.Month + "-" + startdate.Day;
String end_datetimestring = enddate.Year + "-" + enddate.Month + "-" + enddate.Day;
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call getStatementByCustomerAndDate(" + this.customer.Id + ", '" + start_datetimestring + "', '" + end_datetimestring + "');");
populateFromResults(results);
HashSet<Hashtable> openingresults = dal.executeAsHashset("call getCustomerBalanceByDate(" + this.customer.Id + ", '" + start_datetimestring + "');");
populateOpeningFromResults(openingresults);
}
catch (Exception ex)
{
throw new Exception("Unable to load statement for customer by id " + this.customer.Id, ex);
}
}
public User(String username)
{
try
{
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call getUserByUsername(\"" + username + "\");");
populateUserFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to load user by username " + username + ex.Message, ex);
}
}
public Product(int id)
{
try
{
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call getProductById(" + id + ");");
populateFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to load product by id " + id, ex);
}
}
public Invoice(int id)
{
try
{
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call getInvoiceById(" + id + ");");
populateFromResults(results);
HashSet<Hashtable> productsresults = dal.executeAsHashset("call getInvoiceProductsById(" + id + ");");
populateProductsFromResults(productsresults);
}
catch (Exception ex)
{
throw new Exception("Unable to load invoice by id " + id, ex);
}
}
public static Invoice[] listInvoices()
{
Invoice[] invoices = new Invoice[0];
Dal dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call listInvoices();");
invoices = new Invoice[results.Count];
int counter = 0;
foreach (Hashtable table in results)
{
Invoice invoice = new Invoice(table);
invoices[counter] = invoice;
counter++;
}
return invoices;
}
public static Customer[] listCustomers()
{
Customer[] customers = new Customer[0];
Dal dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call listCustomers();");
customers = new Customer[results.Count];
int counter = 0;
foreach (Hashtable table in results)
{
Customer customer = new Customer(table);
customers[counter] = customer;
counter++;
}
return customers;
}
public Statement(Customer customer, Boolean zeroBased)
{
this.customer = customer;
this.zerobased = zeroBased;
try
{
this.openingbalance = 0;
this.closingbalance = customer.Balance;
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call getStatementByCustomerAndDate(" + this.customer.Id + ", null, null);");
populateFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to load statement for customer by id " + this.customer.Id, ex);
}
}
public void Pay(Double amount, DateTime date)
{
try
{
if (amount==0) throw new Exception("Amount mandatory");
this.datetime = date;
String datetimestring = this.Date.Year + "-" + this.Date.Month + "-" + this.Date.Day;
dal = new Dal();
if (invoice != null)
{
dal.executeNonQuery("call addPayment(" + this.customer.Id + ", " + amount + ", " + this.invoice.Id + ", '" + datetimestring + "');");
}
else
{
dal.executeNonQuery("call addPayment(" + this.customer.Id + ", " + amount + ", 0, '" + datetimestring + "');");
}
}
catch (Exception ex)
{
throw new Exception("Unable to make payment", ex);
}
}
public void Save()
{
try
{
if (this.name.Equals("")) throw new Exception("Name mandatory");
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call updateProduct('" + this.name + "', " + this.price + ", " + Convert.ToInt16(this.instock) + "," + this.vatpercentage + ");");
populateFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to save product", ex);
}
}
public static Hashtable listSearchProducts()
{
Hashtable products = new Hashtable();
Dal dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call listProducts();");
foreach (Hashtable table in results)
{
products.Add(table["name"].ToString().ToLower(), table["id"]);
}
return products;
}
public void Delete()
{
try
{
dal = new Dal();
dal.executeNonQuery("call deleteProduct(" + this.Id + ");");
this.id = 0;
}
catch (Exception ex)
{
throw new Exception("Unable to delete product", ex);
}
}
public void Save()
{
try
{
if (this.username.Equals("")) throw new Exception("Username mandatory");
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call updateUser('" + this.username + "', '" + this.password + "', '" + this.name + "', '" + this.surname + "', '" + this.emailaddress + "', '" + this.phonenumber + "', " + Convert.ToInt16(this.role) + ");");
populateUserFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to save user", ex);
}
}
public static User[] listUsers()
{
User[] users = new User[0];
Dal dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call listUsers(null);");
users = new User[results.Count];
int counter = 0;
foreach (Hashtable table in results)
{
User user = new User(table);
users[counter] = user;
counter++;
}
return users;
}
public void Save()
{
try
{
String datetimestring = this.Date.Year + "-" + this.Date.Month + "-" + this.Date.Day;
dal = new Dal();
if (this.id == 0)
{
HashSet<Hashtable> results = dal.executeAsHashset("call updateInvoice(null, " + this.customerid + ",'" + datetimestring + "','" + this.createdby + "','" + this.instructions + "', " + Convert.ToInt16(this.finalized) + ", " + Convert.ToInt16(this.paymentdue) + ");");
populateFromResults(results);
}
else
{
HashSet<Hashtable> results = dal.executeAsHashset("call updateInvoice(" + this.id + ", " + this.customerid + ",\"" + datetimestring + "\",\"" + this.createdby + "\",\"" + this.instructions + "\", " + Convert.ToInt16(this.finalized) + "," + Convert.ToInt16(this.paymentdue) + ");");
populateFromResults(results);
dal.executeNonQuery("call deleteInvoiceProducts(" + this.id + ");");
}
foreach (InvoiceProduct product in this.products)
{
dal.executeNonQuery("call addInvoiceProduct(" + this.id + ", '" + product.Name + "', " + product.Price + ", " + product.Quantity + ", " + Convert.ToInt16(product.HasVat) + ");");
}
}
catch (Exception ex)
{
throw new Exception("Unable to save invoice", ex);
}
}
public static Product[] listProducts()
{
Product[] products = new Product[0];
Dal dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call listProducts();");
products = new Product[results.Count];
int counter = 0;
foreach (Hashtable table in results)
{
Product product = new Product(table);
products[counter] = product;
counter++;
}
return products;
}
public void Save()
{
try
{
if (this.name.Equals("")) throw new Exception("Name mandatory");
dal = new Dal();
HashSet<Hashtable> results = dal.executeAsHashset("call updateCustomer('" + this.name + "', '" + this.street + "', '" + this.city + "', '" + this.zipcode + "', '" + this.phone + "', '" + this.fax + "', '" + this.vatnumber + "', '" + this.emailaddress + "', '" + this.contact + "', " + this.rep.Id + ", " + Convert.ToInt16(this.paymenttype) + ");");
populateFromResults(results);
}
catch (Exception ex)
{
throw new Exception("Unable to save customer", ex);
}
}