public void readDeletedUsers()
{
ConnectOracle search = new ConnectOracle();
DataSet data = search.getData("SELECT U.IDUSER, R.ROLEMANE, U.NAME" +
"FROM ERP_USERS U, ERP_ROLES R" +
"WHERE R.IDROLE=U.REFROLE, U.DELETED=TRUE", "USERS");
tabla = data.Tables["users"];
}
public void modifyAmountOrderProduct(int order, int product, int amount)
{
ConnectOracle search = new ConnectOracle();
string sql = "UPDATE ERP_ORDERS_PRODUCTS SET AMOUNT=" + amount + " WHERE REFORDER=" + order +
"AND REFPRODUCT=" + product + "; ";
search.setData(sql);
lastChange = "Product " + product + " units modified in order " + order;
}
public void insertOrderProduct(int order, int product, int amount, double priceSale)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_ORDERS_PRODUCTS (REFORDER, REFPRODUCT, AMOUNT, PRICESALE) VALUES ("
+ order + ", " + product + ", " + amount + ", " + priceSale + ");";
search.setData(sql);
lastChange = "Product " + product + " inserted in the order " + order;
}
public void readOrdersProducts(int order)
{
ConnectOracle search = new ConnectOracle();
DataSet data = search.getData("SELECT P.PRODUCT, OP.AMOUNT, OP.PRICESALE " +
"FROM ERP_ORDERS_PRODUCTS OP, ERP_PRODUCTS P" +
"WHERE OP.REFORDER=" + order, "ORDERSPRODUCTS");
tabla = data.Tables["orders"];
}
public void insertOrder(int customer, int user, int paymentMethod, double total, double prepaid)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_ORDERS (REFCUSTOMER, REFUSER, SYSDATE, REFPAYMENTMETHOD, TOTAL, PREPAID) VALUES ("
+ customer + ", " + user + ", " + paymentMethod + ", " + total + ", " + prepaid + ");";
search.setData(sql);
lastChange = "Order " + Convert.ToInt32(search.DLookUp("idorder", " (select * from erp_orders order by idorder " +
"desc)", "rownum = 1")) + " inserted";
}
public void readDeletedOrders()
{
ConnectOracle search = new ConnectOracle();
DataSet data = search.getData("SELECT O.IDORDER, C.NIF, U.NAME, O.DATETIME, P.PAYMENTMETHOD, O.TOTAL, O.PREPAID, " +
"FROM ERP_ORDERS O, ERP_CUSTOMERS C, ERP_USERS U, ERP_PAYMENTMETHODS P " +
"WHERE O.REFCUSTOMERS=C.IDCUSTOMER AND O.REFUSER=U.IDUSER AND O.REFPAYMENTMETHOD=P.IDPAYMENTMETHOD AND" +
"O.DELETED=TRUE", "ORDERS");
tabla = data.Tables["orders"];
}
public void insertChange(int user, string change)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_CHANGES (REFUSER, CHANGE) VALUES ("
+ user + ", " + change + ");";
search.setData(sql);
lastChange = "User " + Convert.ToInt32(search.DLookUp("IDCHANGE", " (select * from ERP_CHANGES order by " +
"IDCHANGE desc)", "rownum = 1")) + " inserted";
}
public void modifyCustomer(int id, string nif, string name, string surname, string address, int phone, string email,
int zipcode, string user)
{
ConnectOracle search = new ConnectOracle();
string sql = "UPDATE ERP_CUSTOMERS SET NIF=" + nif + " NAME = " + name + " SURNAME=" + surname + "ADDRESS=" +
address + "PHONE= " + phone + " EMAIL=" + email + "REFZIPCODESCITIES=" + zipcode + " WHERE IDCUSTOMER=" + id + "; ";
search.setData(sql);
lastChange = "Customer " + id + " modified by user " + user;
}
public void insertUser(int role, string name, string password)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_USERS (REFROLE, NAME, PASSWORD) VALUES ("
+ role + ", " + name + ", " + password + ");";
search.setData(sql);
lastChange = "User " + Convert.ToInt32(search.DLookUp("IDUSER", " (select * from ERP_USERS order by " +
"IDUSER desc)", "rownum = 1")) + " inserted";
}
public void modifyProduct(int id, string name, string measure, double price, string brand, string model, int ram, int rom,
double screen, string processor)
{
ConnectOracle search = new ConnectOracle();
string sql = "UPDATE ERP_PRODUCTS SET NAME=" + name + " MEASURE = " + measure + " PRICE=" + price + "BRAND="
+ brand + "MODEL= " + model + " RAM=" + ram + "ROM=" + rom + "SCREEN=" + screen + "PROCESSOR=" + processor
+ " WHERE IDPRODUCT=" + id + "; ";
search.setData(sql);
lastChange = "Product " + id + " modified";
}
public void insertCustomer(string nif, string name, string surname, string address, int phone, string email, int zipcode,
string user)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_CUSTOMERS (NIF, NAME, SURNAME, ADDRESS, PHONE, EMAIL, REFZIPCODESCITIES) VALUES ("
+ nif + ", " + name + ", " + surname + ", " + address + ", " + phone + ", " + email + ", " + zipcode + ");";
search.setData(sql);
lastChange = "Customer " + Convert.ToInt32(search.DLookUp("idcustomer", " (select * from erp_customers order by " +
"idcustomer desc)", "rownum = 1")) + " inserted by user " + user;
}
public void readDeletedCustomers()
{
ConnectOracle search = new ConnectOracle();
DataSet data = search.getData("SELECT CS.IDCUSTOMER, CS.NIF, CS.NAME, CS.SURNAME, CS.ADDRESS, CS.PHONE, CS.EMAIL, R.REGION," +
"S.STATE, CT.CITY, Z.ZIPCODE " +
"FROM ERP_CUSTOMERS CS, ERP_ZIPCODESCITIES ZC, ERP_CITIES CT, ERP_ZIPCODES Z, ERP_STATES S, ERP_REGIONS R " +
"WHERE CS.REFERP_ZIPCODES_CITIES=ZC.IDERP_ZIPCODES_CITIES AND ZC.REFZIPCODE=Z.IDZIPCODE AND ZC.REFCITY=CT.IDCITY AND " +
"ZC.REFSTATE=S.IDSTATE AND S.REFREGION=R.IDREGION AND CS.DELETED=1", "CUSTOMERS");
tabla = data.Tables["CUSTOMERS"];
}
public double calcTotalOrder(int order)
{
ConnectOracle search = new ConnectOracle();
DataSet data = search.getData("SELECT SUM(AMOUNT*PRICESALE)" +
" FROM ERP_ORDERS_PRODUCTS" +
" WHERE REFORDER=" + order +
" GROUP BY REFPRODUCT", "ORDERSPRODUCTS");
tabla = data.Tables["orders"];
return(Convert.ToDouble(tabla.Rows[0][0].ToString()));
}
public void insertProduct(string name, string measure, double price, string brand, string model, int ram, int rom,
double screen, string processor)
{
ConnectOracle search = new ConnectOracle();
string sql = "INSERT INTO ERP_PRODUCTS (NAME, MEASURE, PRICE, BRAND, MODEL, RAM, ROM, SCREEN, PROCESSOR) VALUES ("
+ name + ", " + measure + ", " + price + ", " + brand + ", " + model + ", " + ram + ", " + rom + ", " + screen
+ processor + ");";
search.setData(sql);
lastChange = "Product " + Convert.ToInt32(search.DLookUp("IDPRODUCT", " (select * from ERP_PRODUCTS order by " +
"IDPRODUCT desc)", "rownum = 1")) + " inserted";
}