// <editor-fold defaultstate="collapsed" desc="ret_version_details">
public static List<to_version> ret_version_details() {
List<to_version> datas = new ArrayList();
try {
Connection conn = MyConnection1.connect();
String s0 =
"select a.account_name, a.exp_date, a.date_added from " + MyDB.getNames() + ".accounts a";
Lg.$.severe(s0);
PreparedStatement stmt = conn.prepareStatement(s0);
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String name = rs.getString("account_name");
Date exp_date = rs.getTimestamp("exp_date");
Date date_added = rs.getTimestamp("date_added");
to_version to = new to_version(name, exp_date, date_added);
datas.add(to);
}
return datas;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection1.close();
}
}
public static void do_edit(String name, String num, String img_path) {
try {
Connection conn = MyConnection1.connect();
String s0 =
"update "
+ MyDB.getNames()
+ ".category set cat_name='"
+ name
+ "',img_path='"
+ img_path
+ "' where cat_num='"
+ num
+ "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
String s1 =
"update "
+ MyDB.getNames()
+ ".category_type set cat_name='"
+ name
+ "' where cat_num='"
+ num
+ "'";
PreparedStatement stmt1 = conn.prepareStatement(s1);
stmt1.execute();
Prompt.call("Updated Successfully");
// JOptionPane.showMessageDialog(null, "Updated Successfully");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection1.close();
}
}
public static void do_delete_category_type(String num) {
try {
Connection conn = MyConnection1.connect();
String s0 = "delete from " + MyDB.getNames() + ".category_type where type_num='" + num + "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
String s1 =
"delete from "
+ MyDB.getNames()
+ ".inventory2_stocks_left where types_num='"
+ num
+ "'";
PreparedStatement stmt1 = conn.prepareStatement(s1);
stmt1.execute();
String s2 = "delete from " + MyDB.getNames() + ".inventory2 where types_num='" + num + "'";
PreparedStatement stmt2 = conn.prepareStatement(s2);
stmt2.execute();
Prompt.call("Updated Successfully");
// JOptionPane.showMessageDialog(null, "Updated Successfully");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection1.close();
}
}
public static List<to_users.u> get_user_data() {
List<to_users.u> datas = new ArrayList();
String s0 = "select user_name,user_level,user_code from " + MyDB.getNames() + ".users ";
try {
Connection conn = PoolConnection.connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String username = rs.getString(1);
String user_level = rs.getString(2);
String name = rs.getString(3);
System.out.println(username);
to_users.u to = new to_users.u(username, user_level, name);
datas.add(to);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
PoolConnection.close();
}
return datas;
}
public static List<to_sales> ret_data(String cashier_id, String item, String dates) {
List<to_sales> datas = new ArrayList();
try {
Connection conn = MyConnection.connect();
String s0 =
"select id,product_id,product_name,price,qty_sold,date_added,cashierid,commission,remitance,remit_cost from "
+ MyDB.getNames()
+ ".sales where date(date_added)='"
+ dates
+ "'and cashierid='"
+ cashier_id
+ "' and product_name like '%"
+ item
+ "%' order by date_added asc ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String id = rs.getString(1);
String product_id = rs.getString(2);
String product_name = rs.getString(3);
double price = rs.getDouble(4);
double qty_sold = rs.getDouble(5);
String date = rs.getString(6);
String cash_od = rs.getString(7);
double commission = rs.getDouble(8);
double remitance = rs.getDouble(9);
double remit_cost = rs.getDouble(10);
to_sales to =
new to_sales(
id,
product_id,
product_name,
price,
qty_sold,
date,
cash_od,
commission,
remitance,
remit_cost);
datas.add(to);
}
return datas;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static boolean if_exists(String num) {
boolean exists = false;
try {
Connection conn = MyConnection.connect();
String s0 =
"select prod_code from " + MyDB.getNames() + ".favorites where prod_code='" + num + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
// String num = rs.getString(1);
exists = true;
}
return exists;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static int count() {
// JOptionPane.showMessageDialog(null, "adadad");
int count = 0;
try {
Connection conn = MyConnection.connect();
String s0 = "select Count(id) from " + MyDB.getNames() + ".favorites ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
count = rs.getInt(1);
}
return count;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<String> get_cashiers() {
List<String> datas = new ArrayList();
try {
Connection conn = PoolConnection.connect();
String s0 = "select user_name from " + MyDB.getNames() + ".users where user_level=0";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String num = rs.getString(1);
datas.add(num);
}
return datas;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
PoolConnection.close();
}
}
public static void do_delete(String num) {
// JOptionPane.showMessageDialog(null, "adadad");
try {
Connection conn = MyConnection.connect();
String s0 = "delete from " + MyDB.getNames() + ".favorites where prod_code='" + num + "'";
PreparedStatement stmt = conn.prepareStatement(s0);
// stmt.setString(1, num);
stmt.execute();
Prompt.call("DELETED");
// JOptionPane.showMessageDialog(null, "DELETED");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void do_add(String num) {
if (if_exists(num) == true) {
Prompt.call("Already Added");
// JOptionPane.showMessageDialog(null, "Already Added");
return;
}
try {
Connection conn = MyConnection.connect();
String s0 = "insert into " + MyDB.getNames() + ".favorites(prod_code)values(?)";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.setString(1, num);
stmt.execute();
Prompt.call("ADDED TO FAVORITES");
// JOptionPane.showMessageDialog(null, "ADDED TO FAVORITES");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static ListModel get_pending_request(String text) {
System.out.println(text + " ad");
DefaultListModel retval = new DefaultListModel();
try {
String s0 =
"select p.customer_name "
+ "from "
+ MyDB.getNames()
+ ".pending_orders p "
+ "where customer_name like '"
+ text
+ "%' ";
// s0 = SqlStringUtil.parse(s0)
// .setString("customer_name", text + "%")
// .ok();
//
Lg.$.info(s0);
Connection conn = MyConnection.connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String cust = rs.getString(1);
System.out.println(cust);
retval.addElement(cust);
}
return retval;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void get_user_data2(String user_name) {
// String s0 =
// "call pb_pos_restaurant.select_user_details2( "
// + " :a_user_name "
// + " ); ";
// s0=SqlStringUtil.parse(s0)
// .setString("a_user_name", user_name)
// .ok();
String s1 =
"select "
+ "p.user_code"
+ ",p.user_name"
+ ",p.password"
+ ",p.user_level from "
+ MyDB.getNames()
+ ".users p where user_name='"
+ user_name
+ "'";
try {
Connection conn = PoolConnection.connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s1);
while (rs.next()) {
password = rs.getString(3);
userlevel = rs.getInt(4);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
PoolConnection.close();
}
}
// <editor-fold defaultstate="collapsed" desc="do_check">
public static boolean do_check(String name) {
try {
String s0 =
"select p.id from "
+ MyDB.getNames()
+ ".products p where p.product_name = :product_name; ";
s0 = SqlStringUtil.parse(s0).setString("product_name", name).ok();
Lg.$.severe(s0);
Connection conn = MyConnection.connect();
PreparedStatement stmt = conn.prepareStatement(s0);
ResultSet rs = stmt.executeQuery();
return rs.next();
// JOptionPane.showMessageDialog(null, "adad");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void do_cancel(
double qty,
String cashier_id,
String product_id,
double new_qty,
String id,
double price,
String name) {
double qty_left = S4_endorsements.get_left(product_id, cashier_id);
String date = DateType.datetime.format(new Date());
try {
Connection conn = MyConnection.connect();
if (qty == new_qty) {
String s0 = "delete from " + MyDB.getNames() + ".sales where id='" + id + "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
qty_left += new_qty;
String s1 =
"update "
+ MyDB.getNames()
+ ".endorsements set qty_left='"
+ qty_left
+ "' where cashier_id='"
+ cashier_id
+ "' and product_id='"
+ product_id
+ "'";
PreparedStatement stmt2 = conn.prepareStatement(s1);
stmt2.execute();
String s2 =
"insert into "
+ MyDB.getNames()
+ ".cancelled_sales(product_id,product_name,price,qty,date_added)values(?,?,?,?,?)";
PreparedStatement stmt3 = conn.prepareStatement(s2);
stmt3.setString(1, product_id);
stmt3.setString(2, name);
stmt3.setDouble(3, price);
stmt3.setDouble(4, new_qty);
stmt3.setString(5, date);
stmt3.execute();
} else {
double aw = qty - new_qty;
double commission = 0;
double rate = S4_endorsements.get_commission_rate(product_id);
rate = (rate / 100);
commission = (new_qty * price) * rate;
String s0 =
"update "
+ MyDB.getNames()
+ ".sales set qty_sold='"
+ new_qty
+ "',commission='"
+ commission
+ "' where id='"
+ id
+ "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
qty_left += new_qty;
String s1 =
"update "
+ MyDB.getNames()
+ ".endorsements set qty_left='"
+ qty_left
+ "' where cashier_id='"
+ cashier_id
+ "' and product_id='"
+ product_id
+ "'";
PreparedStatement stmt2 = conn.prepareStatement(s1);
stmt2.execute();
String s2 =
"insert into "
+ MyDB.getNames()
+ ".cancelled_sales(product_id,product_name,price,qty,date_added)values(?,?,?,?,?)";
PreparedStatement stmt3 = conn.prepareStatement(s2);
stmt3.setString(1, product_id);
stmt3.setString(2, name);
stmt3.setDouble(3, price);
stmt3.setDouble(4, qty - new_qty);
stmt3.setString(5, date);
stmt3.execute();
}
Prompt.call("Successfully Updated");
// JOptionPane.showMessageDialog(null, "Successfully Updated");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
