// 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); }