// updates a item using configured Database connection
public static int UpdateItem(Item item)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE item SET Description=?,UnitPrice=?,Quantity=?,MinOrderQty=?,ReorderQty=? WHERE ItemCode=?", DBConnection.getConnection());
scmd.Parameters.Add("@Description", OdbcType.VarChar).Value = item.Description;
scmd.Parameters.Add("@UnitPrice", OdbcType.Double).Value = item.UnitPrice;
scmd.Parameters.Add("@Quantity", OdbcType.Int).Value = item.Quantity;
scmd.Parameters.Add("@MinOrderQty", OdbcType.Int).Value = item.MinOrderQty;
scmd.Parameters.Add("@ReorderQty", OdbcType.Int).Value = item.ReorderQty;
scmd.Parameters.Add("@ItemCode", OdbcType.Int).Value = item.ItemCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a Received Item using configured Database connection
public static int InsertReceivedItem(ReceivedItem item)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO receiveditem (ItemRef, Quantity,receivedDate) VALUES(?,?,curdate())", DBConnection.getConnection());
scmd.Parameters.Add("@ItemRef", OdbcType.Int).Value = item.OrderItem.Ref;
scmd.Parameters.Add("@Quantity", OdbcType.Int).Value = item.Quantity;
i = scmd.ExecuteNonQuery();
//item.Ref = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// genarate Sales report
public static ReportClass Sales(DateTime?from, DateTime?To)
{
OdbcDataAdapter reportAdapter;
DataSet reportDataSet;
ReportClass currentReport = null;
if (from == null || To == null)
{
reportAdapter = new OdbcDataAdapter("select * from salesview", DBConnection.getConnection());
}
else
{
reportAdapter = new OdbcDataAdapter("select * from salesview where InvoiceDate between date(?) and date(?)", DBConnection.getConnection());
reportAdapter.SelectCommand.Parameters.Add("@from", OdbcType.VarChar).Value = String.Format("{0:yyyy-MM-dd}", from);
reportAdapter.SelectCommand.Parameters.Add("@to", OdbcType.VarChar).Value = String.Format("{0:yyyy-MM-dd}", To);
}
reportDataSet = new reportDS();
reportAdapter.Fill(reportDataSet, "sales");
if (reportDataSet.Tables["sales"].Rows.Count > 0)
{
currentReport = new RptSales();
currentReport.SetDataSource(reportDataSet);
}
return(currentReport);
}
//delete an supplier
public static int DeleteSupplier(Supplier supplier)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("delete from supplier where SupCode=?", DBConnection.getConnection());
scmd.Parameters.Add("@Code", OdbcType.Int).Value = supplier.SupCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception)
{
SetErrMsg("This Supplier cannot be deleted as this Supplier is referred to in orders or items");
}
return(i);
}
// updates a invoice using configured Database connection
public static int UpdateInvoice(Invoice invoice)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE invoice SET IsPaid=? WHERE InvoiceNo=?", DBConnection.getConnection());
scmd.Parameters.Add("@IsPaid", OdbcType.TinyInt).Value = invoice.IsPaid;
scmd.Parameters.Add("@InvoiceNo", OdbcType.TinyInt).Value = invoice.InvoiceNo;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
//inserts a item using configured Database connection
public static int InsertItem(Item item)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO item (Description,Category,UnitPrice,Quantity,MinOrderQty,ReorderQty,SupCode) VALUES(?,?,?,?,?,?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@Description", OdbcType.VarChar).Value = item.Description;
scmd.Parameters.Add("@Category", OdbcType.Int).Value = item.Category.Id;
scmd.Parameters.Add("@UnitPrice", OdbcType.Double).Value = item.UnitPrice;
scmd.Parameters.Add("@Quantity", OdbcType.Int).Value = item.Quantity;
scmd.Parameters.Add("@MinOrderQty", OdbcType.Int).Value = item.MinOrderQty;
scmd.Parameters.Add("@ReorderQty", OdbcType.Int).Value = item.ReorderQty;
scmd.Parameters.Add("@Supplier", OdbcType.Int).Value = item.Supplier.SupCode;
i = scmd.ExecuteNonQuery();
item.ItemCode = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// updates a supplier using configured Database connection
public static int UpdateSupplier(Supplier supplier)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE supplier SET Address=?, Phone=?, Description=?, email=? WHERE SupCode=?", DBConnection.getConnection());
// scmd.Parameters.Add("@Name", OdbcType.VarChar).Value = supplier.Name;
scmd.Parameters.Add("@Address", OdbcType.VarChar).Value = supplier.Address;
scmd.Parameters.Add("@Phone", OdbcType.VarChar).Value = supplier.Phone;
scmd.Parameters.Add("@Description", OdbcType.VarChar).Value = supplier.Description;
scmd.Parameters.Add("@Email", OdbcType.VarChar).Value = supplier.Email;
scmd.Parameters.Add("@SupCode", OdbcType.VarChar).Value = supplier.SupCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a order using configured Database connection
public static int InsertOrder(Order order)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO `order`(SupCode, OrderDate, Total,received) VALUES(?,curdate(),?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@SupCode", OdbcType.Int).Value = order.Supplier.SupCode;
scmd.Parameters.Add("@Total", OdbcType.Double).Value = order.Total;
scmd.Parameters.Add("@received", OdbcType.TinyInt).Value = order.IsReceived;
i = scmd.ExecuteNonQuery();
order.OrderNo = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// updates a customer using configured Database connection
public static int UpdateCustomer(Customer customer)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE customer SET Address=?, Phone=?, email=?, Notes=? WHERE CusCode=?", DBConnection.getConnection());
// scmd.Parameters.Add("@Name", OdbcType.VarChar).Value = customer.Name;
scmd.Parameters.Add("@Address", OdbcType.VarChar).Value = customer.Address;
scmd.Parameters.Add("@Phone", OdbcType.VarChar).Value = customer.Phone;
scmd.Parameters.Add("@Email", OdbcType.VarChar).Value = customer.Email;
// scmd.Parameters.Add("@Type", OdbcType.Int).Value = customer.Type;
scmd.Parameters.Add("@Notes", OdbcType.VarChar).Value = customer.Notes;
scmd.Parameters.Add("@CusCode", OdbcType.VarChar).Value = customer.CusCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a supplier using configured Database connection
public static int InsertSupplier(Supplier supplier)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO supplier (Name, Address, Phone, Description, Email) VALUES(?,?,?,?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@Name", OdbcType.VarChar).Value = supplier.Name;
scmd.Parameters.Add("@Address", OdbcType.VarChar).Value = supplier.Address;
scmd.Parameters.Add("@Phone", OdbcType.VarChar).Value = supplier.Phone;
scmd.Parameters.Add("@Notes", OdbcType.VarChar).Value = supplier.Description;
scmd.Parameters.Add("@Email", OdbcType.VarChar).Value = supplier.Email;
i = scmd.ExecuteNonQuery();
supplier.SupCode = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a customer using configured Database connection
public static int InsertCustomer(Customer customer)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO customer (Name, Address, Phone, email, Type, Notes) VALUES(?,?,?,?,?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@Name", OdbcType.VarChar).Value = customer.Name;
scmd.Parameters.Add("@Address", OdbcType.VarChar).Value = customer.Address;
scmd.Parameters.Add("@Phone", OdbcType.VarChar).Value = customer.Phone;
scmd.Parameters.Add("@Email", OdbcType.VarChar).Value = customer.Email;
scmd.Parameters.Add("@Type", OdbcType.Int).Value = customer.Type;
scmd.Parameters.Add("@Notes", OdbcType.VarChar).Value = customer.Notes;
i = scmd.ExecuteNonQuery();
customer.CusCode = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// get Suppliers using configured Database connection
public static List <Supplier> GetSuppliers(IDictionary <String, String> filter)
{
List <Supplier> suppliers = new List <Supplier>();
try
{
OdbcCommand scmd = new OdbcCommand("SELECT * FROM supplier WHERE SupCode LIKE ? AND Name LIKE ? AND Address LIKE ? AND Phone LIKE ? AND Email LIKE ?", DBConnection.getConnection());
scmd.Parameters.Add("@SupCode", OdbcType.VarChar).Value = (filter.ContainsKey("Code")) ? (filter["Code"] + "%") : "%";
scmd.Parameters.Add("@Name", OdbcType.VarChar).Value = (filter.ContainsKey("Name")) ? (filter["Name"] + "%") : "%";
scmd.Parameters.Add("@Address", OdbcType.VarChar).Value = (filter.ContainsKey("Address")) ? (filter["Address"] + "%") : "%";
scmd.Parameters.Add("@Phone", OdbcType.VarChar).Value = (filter.ContainsKey("Phone")) ? (filter["Phone"] + "%") : "%";
scmd.Parameters.Add("@Email", OdbcType.VarChar).Value = (filter.ContainsKey("Email")) ? (filter["Email"] + "%") : "%";
OdbcDataReader sdr = scmd.ExecuteReader();
while (sdr.Read())
{
Supplier supplier = new Supplier();
supplier.SupCode = Convert.ToInt32(sdr["SupCode"]);
supplier.Name = sdr["Name"].ToString();
supplier.Address = sdr["Address"].ToString();
supplier.Phone = sdr["Phone"].ToString();
supplier.Email = sdr["Email"].ToString();
supplier.Description = sdr["Description"].ToString();
suppliers.Add(supplier);
}
sdr.Close();
}
catch (Exception ex)
{
SetErrMsg(ex.Message);
}
return(suppliers);
}
// updates a item's quantity using configured Database connection
public static int UpdateItemQuantity(Item item, int shift)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE item SET Quantity=Quantity+? WHERE ItemCode=?", DBConnection.getConnection());
scmd.Parameters.Add("@Quantity", OdbcType.Int).Value = shift;
scmd.Parameters.Add("@ItemCode", OdbcType.Int).Value = item.ItemCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a Payment using configured Database connection
public static int InsertPayment(Payment payment)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO payment (InvoiceNo, PaidDate, Amount, Balance) VALUES(?,now(),?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@ItemRef", OdbcType.Int).Value = payment.Invoice.InvoiceNo;
scmd.Parameters.Add("@Amount", OdbcType.Double).Value = payment.Amount;
scmd.Parameters.Add("@Balance", OdbcType.Double).Value = payment.Balance;
i = scmd.ExecuteNonQuery();
payment.Ref = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a Category using configured Database connection
public static int InsertItemCategory(ItemCategory category)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO itemcategory (Description) VALUES(?)", DBConnection.getConnection());
scmd.Parameters.Add("Description", OdbcType.VarChar).Value = category.Name;
i = scmd.ExecuteNonQuery();
category.Id = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// inserts a Invoice using configured Database connection
public static int InsertInvoice(Invoice invoice)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO invoice (CusCode, InvoiceDate, Total, IsCredit, IsPaid) VALUES(?,curdate(),?,?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@CusCode", OdbcType.Int).Value = invoice.Customer.CusCode;
scmd.Parameters.Add("@Total", OdbcType.Double).Value = invoice.Total;
scmd.Parameters.Add("@IsCredit", OdbcType.TinyInt).Value = invoice.IsCredit;
scmd.Parameters.Add("@IsPaid", OdbcType.TinyInt).Value = invoice.IsPaid;
i = scmd.ExecuteNonQuery();
invoice.InvoiceNo = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// set current username in database session
public static void SetSessionUser()
{
try
{
OdbcCommand SqlCmd;
SqlCmd = new OdbcCommand("SET @user='******'", DBConnection.getConnection());
SqlCmd.ExecuteScalar();
}
catch (Exception)
{
}
}
// get list of SoldItems using configured Database connection
public static List <SoldItem> GetSoldItems(Invoice invoice)
{
List <SoldItem> items = new List <SoldItem>();
try
{
OdbcCommand scmd = new OdbcCommand("SELECT i.Ref, i.ItemCode, j.Description, i.InvoiceNo, i.Quantity, i.UnitPrice FROM solditem i, item j WHERE i.ItemCode=j.ItemCode AND i.InvoiceNo = ?", DBConnection.getConnection());
scmd.Parameters.Add("@invoice", OdbcType.Int).Value = invoice.InvoiceNo;
OdbcDataReader sdr = scmd.ExecuteReader();
while (sdr.Read())
{
SoldItem solditem = new SoldItem();
solditem.Ref = Convert.ToInt32(sdr["Ref"]);
Item item = new Item();
item.ItemCode = Convert.ToInt32(sdr["ItemCode"]);
item.Description = sdr["Description"].ToString();
solditem.Item = item;
solditem.Invoice = invoice;
solditem.Quantity = Convert.ToInt32(sdr["Quantity"]);
solditem.UnitPrice = Convert.ToDouble(sdr["UnitPrice"]);
items.Add(solditem);
}
sdr.Close();
}
catch (Exception ex)
{
SetErrMsg(ex.Message);
}
return(items);
}
// inserts a user using configured Database connection
public static int InsertUser(User user)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO user (UserName, FullName, Passwd, Category) VALUES(?,?,password(?),?)", DBConnection.getConnection());
scmd.Parameters.Add("@UserID", OdbcType.VarChar).Value = user.Name;
scmd.Parameters.Add("@FullName", OdbcType.VarChar).Value = user.FullName;
scmd.Parameters.Add("@password", OdbcType.VarChar).Value = user.Password;
scmd.Parameters.Add("@Category", OdbcType.VarChar).Value = user.Category;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// updates a Order using configured Database connection
public static int UpdateOrder(Order order)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE `order` SET received=? WHERE OrderNo=? ", DBConnection.getConnection());
scmd.Parameters.Add("@received", OdbcType.TinyInt).Value = order.IsReceived;
scmd.Parameters.Add("@OrderNo", OdbcType.Int).Value = order.OrderNo;
i = scmd.ExecuteNonQuery();
order.OrderNo = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// updates a user using configured Database connection
public static int UpdateUser(User user)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("UPDATE user SET FullName=?, Passwd=password(?) WHERE UserName=?", DBConnection.getConnection());
scmd.Parameters.Add("@FullName", OdbcType.VarChar).Value = user.FullName;
scmd.Parameters.Add("@password", OdbcType.VarChar).Value = user.Password;
scmd.Parameters.Add("@UserID", OdbcType.VarChar).Value = user.Name;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
// get list of Transactions using configured Database connection
public static List <Event> GetTransactionLog()
{
List <Event> events = new List <Event>();
try
{
OdbcCommand scmd = new OdbcCommand("SELECT * FROM transaction_log order by `time` desc", DBConnection.getConnection());
OdbcDataReader sdr = scmd.ExecuteReader();
while (sdr.Read())
{
Event e = new Event();
e.user = sdr["user_id"].ToString();
e.description = String.Format("{0} {1:0000} {2}", sdr["subject"], Convert.ToInt32(sdr["subjectRef"]), sdr["description"]);
e.time = Convert.ToDateTime(sdr["time"]);
events.Add(e);
}
sdr.Close();
}
catch (Exception ex)
{
SetErrMsg(ex.Message);
}
return(events);
}
// deletes a user using configured Database connection
public static int DeleteUser(User user)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("DELETE FROM user WHERE UserName=?", DBConnection.getConnection());
scmd.Parameters.Add("@UserID", OdbcType.VarChar).Value = user.Name;
i = scmd.ExecuteNonQuery();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
//delete an item
public static int DeleteItem(Item item)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("DELETE from item where ItemCode=?", DBConnection.getConnection());
scmd.Parameters.Add("@ItemCode", OdbcType.Int).Value = item.ItemCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception)
{
SetErrMsg("This item cannot be deleted as this item is referred to in invoices");
}
return(i);
}
// inserts a ordered item using configured Database connection
public static int InsertOrderItem(OrderItem item)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("INSERT INTO orderitem (ItemCode, OrderNo, Quantity, UnitCost) VALUES(?,?,?,?)", DBConnection.getConnection());
scmd.Parameters.Add("@ItemCode", OdbcType.Int).Value = item.Item.ItemCode;
scmd.Parameters.Add("@OrderNo", OdbcType.Int).Value = item.Order.OrderNo;
scmd.Parameters.Add("@Quantity", OdbcType.Int).Value = item.Quantity;
scmd.Parameters.Add("@UnitCost", OdbcType.Double).Value = item.UnitCost;
i = scmd.ExecuteNonQuery();
//item.Ref = GetLastInsert();
}
catch (Exception e)
{
SetErrMsg(e.Message);
}
return(i);
}
//delete a customer
public static int DeleteCustomer(Customer customer)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("delete from customer where CusCode=?", DBConnection.getConnection());
scmd.Parameters.Add("@Code", OdbcType.Int).Value = customer.CusCode;
i = scmd.ExecuteNonQuery();
}
catch (Exception)
{
SetErrMsg("This Customer cannot be deleted as this Customer is referred to in invoices");
}
return(i);
}
//delete a Category
public static int DeleteCategory(ItemCategory category)
{
int i = 0;
try
{
OdbcCommand scmd = new OdbcCommand("delete from ItemCategory where CatID=?", DBConnection.getConnection());
scmd.Parameters.Add("@CatID", OdbcType.Int).Value = category.Id;
i = scmd.ExecuteNonQuery();
}
catch (Exception)
{
SetErrMsg("You cannot delete a category which contains items");
}
return(i);
}