public static void edit_purchase_orders(to_purchase_orders to_purchase_orders) {
try {
Connection conn = MyConnection.connect();
String s0 =
"update purchase_orders set "
+ "po_no= '"
+ to_purchase_orders.po_no
+ "'"
+ ",user_name= '"
+ to_purchase_orders.user_name
+ "'"
+ ",session_no= '"
+ to_purchase_orders.session_no
+ "'"
+ ",date_added= '"
+ to_purchase_orders.date_added
+ "'"
+ ",remarks= '"
+ to_purchase_orders.remarks
+ "'"
+ "where "
+ " id ='"
+ to_purchase_orders.id
+ "' "
+ " ";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_purchase_orders.class, "Successfully Updated");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static String increment_id() {
String ids = "PO-00000000000";
try {
Connection conn = MyConnection.connect();
String s0 = "select max(id) from purchase_orders";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
if (rs.next()) {
ids = rs.getString(1);
}
if (ids == null) {
ids = "PO-00000000001";
} else {
String s2 = "select po_no from purchase_orders where id='" + ids + "'";
Statement stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(s2);
if (rs2.next()) {
ids = rs2.getString(1);
}
}
ids = ReceiptIncrementor.increment(ids);
return ids;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<to_inv_sub_classifications> ret_data(
String sub_classifications, String classification_names) {
List<to_inv_sub_classifications> datas = new ArrayList();
try {
Connection conn = MyConnection.connect();
String s0 =
"select "
+ "id"
+ ",category_id"
+ ",category_name"
+ ",classification_id"
+ ",classification_name"
+ ",sub_classification"
+ " from inv_sub_classifications where "
+ " sub_classification like'%"
+ sub_classifications
+ "%' "
+ " and classification_name like'"
+ classification_names
+ "' "
+ " ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
int id = rs.getInt(1);
String category_id = rs.getString(2);
String category_name = rs.getString(3);
String classification_id = rs.getString(4);
String classification_name = rs.getString(5);
String sub_classification = rs.getString(6);
to_inv_sub_classifications to =
new to_inv_sub_classifications(
id,
category_id,
category_name,
classification_id,
classification_name,
sub_classification);
datas.add(to);
}
return datas;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void delete_purchase_orders(to_purchase_orders to_purchase_orders) {
try {
Connection conn = MyConnection.connect();
String s0 =
"delete from purchase_orders where " + " id ='" + to_purchase_orders.id + "' " + " ";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_purchase_orders.class, "Successfully Deleted");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<to_purchase_orders> ret_data(String date_from, String date_to) {
List<to_purchase_orders> datas = new ArrayList();
try {
Connection conn = MyConnection.connect();
String s0 =
"select "
+ "id"
+ ",po_no"
+ ",user_name"
+ ",session_no"
+ ",date_added"
+ ",remarks"
+ " from purchase_orders where "
+ " date(date_added) between '"
+ date_from
+ "' and '"
+ date_to
+ "' "
+ " ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
int id = rs.getInt(1);
String po_no = rs.getString(2);
String user_name = rs.getString(3);
String session_no = rs.getString(4);
String date_added = rs.getString(5);
String remarks = rs.getString(6);
to_purchase_orders to =
new to_purchase_orders(id, po_no, user_name, session_no, date_added, remarks);
datas.add(to);
}
return datas;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void delete_inv_sub_classifications(
to_inv_sub_classifications to_inv_sub_classifications) {
try {
Connection conn = MyConnection.connect();
String s0 =
"delete from inv_sub_classifications where "
+ " id ='"
+ to_inv_sub_classifications.id
+ "' "
+ " ";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_inv_sub_classifications.class, "Successfully Deleted");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<String> ret_cb_data(String classification) {
List<String> datas = new ArrayList();
datas.add("");
try {
Connection conn = MyConnection.connect();
String s0 =
"select sub_classification from inv_sub_classifications where classification_name like '"
+ classification
+ "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String id = rs.getString(1);
datas.add(id);
}
return datas;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void add_inv_sub_classifications(
to_inv_sub_classifications to_inv_sub_classifications) {
try {
Connection conn = MyConnection.connect();
String s0 =
"insert into inv_sub_classifications("
+ "category_id"
+ ",category_name"
+ ",classification_id"
+ ",classification_name"
+ ",sub_classification"
+ ")values("
+ ":category_id"
+ ",:category_name"
+ ",:classification_id"
+ ",:classification_name"
+ ",:sub_classification"
+ ")";
s0 =
SqlStringUtil.parse(s0)
.setString("category_id", to_inv_sub_classifications.category_id)
.setString("category_name", to_inv_sub_classifications.category_name)
.setString("classification_id", to_inv_sub_classifications.classification_id)
.setString("classification_name", to_inv_sub_classifications.classification_name)
.setString("sub_classification", to_inv_sub_classifications.sub_classification)
.ok();
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_inv_sub_classifications.class, "Successfully Added");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void edit_inv_sub_classifications(
to_inv_sub_classifications to_inv_sub_classifications) {
try {
Connection conn = MyConnection.connect();
String s0 =
"update inv_sub_classifications set "
+ "category_id= '"
+ to_inv_sub_classifications.category_id
+ "'"
+ ",category_name= '"
+ to_inv_sub_classifications.category_name
+ "'"
+ ",classification_id= '"
+ to_inv_sub_classifications.classification_id
+ "'"
+ ",classification_name= '"
+ to_inv_sub_classifications.classification_name
+ "'"
+ ",sub_classification= '"
+ to_inv_sub_classifications.sub_classification
+ "'"
+ "where "
+ " id ='"
+ to_inv_sub_classifications.id
+ "' "
+ " ";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_inv_sub_classifications.class, "Successfully Updated");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static void add_purchase_orders(to_purchase_orders to_purchase_orders) {
try {
Connection conn = MyConnection.connect();
String s0 =
"insert into purchase_orders("
+ "po_no"
+ ",user_name"
+ ",session_no"
+ ",date_added"
+ ",remarks"
+ ")values("
+ ":po_no"
+ ",:user_name"
+ ",:session_no"
+ ",:date_added"
+ ",:remarks"
+ ")";
s0 =
SqlStringUtil.parse(s0)
.setString("po_no", to_purchase_orders.po_no)
.setString("user_name", to_purchase_orders.user_name)
.setString("session_no", to_purchase_orders.session_no)
.setString("date_added", to_purchase_orders.date_added)
.setString("remarks", to_purchase_orders.remarks)
.ok();
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
Lg.s(S1_purchase_orders.class, "Successfully Added");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<to_product> ret_data(String where) {
List<to_product> datas = new ArrayList();
try {
Connection conn = MyConnection.connect2();
String s0 =
"select "
+ "lookup_code"
+ ",description"
+ ",reorder"
+ ",cost"
+ ",measured_in"
+ ",category"
+ ",catalog"
+ ",price"
+ " from overall_pos_db.product_inv_view "
+ " "
+ where;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
String lookup_code = rs.getString(1);
String description = rs.getString(2);
double reorder = rs.getDouble(3);
double cost = rs.getDouble(4);
String measure_in = rs.getString(5);
String category = rs.getString(6);
String catalog = rs.getString(7);
double price = rs.getInt(8);
System.out.println(
lookup_code
+ " "
+ description
+ " "
+ cost
+ " "
+ measure_in
+ " "
+ category
+ " "
+ catalog
+ " "
+ price);
String s2 =
"insert into db_coop.items("
+ "barcode"
+ ",description"
+ ",generic_name"
+ ",category"
+ ",category_id"
+ ",classification"
+ ",classification_id"
+ ",sub_classification"
+ ",sub_classification_id"
+ ",product_qty"
+ ",unit"
+ ",conversion"
+ ",selling_price"
+ ",date_added"
+ ",user_name"
+ ",item_type"
+ ",status"
+ ",supplier"
+ ",fixed_price"
+ ",cost"
+ ",supplier_id"
+ ",multi_level_pricing"
+ ",vatable"
+ ",reorder_level"
+ ")values("
+ ":barcode"
+ ",:description"
+ ",:generic_name"
+ ",:category"
+ ",:category_id"
+ ",:classification"
+ ",:classification_id"
+ ",:sub_classification"
+ ",:sub_classification_id"
+ ",:product_qty"
+ ",:unit"
+ ",:conversion"
+ ",:selling_price"
+ ",:date_added"
+ ",:user_name"
+ ",:item_type"
+ ",:status"
+ ",:supplier"
+ ",:fixed_price"
+ ",:cost"
+ ",:supplier_id"
+ ",:multi_level_pricing"
+ ",:vatable"
+ ",:reorder_level"
+ ")";
s2 =
SqlStringUtil.parse(s2)
.setString("barcode", lookup_code)
.setString("description", description)
.setString("generic_name", "")
.setString("category", category)
.setString("category_id", "")
.setString("classification", "")
.setString("classification_id", "")
.setString("sub_classification", "")
.setString("sub_classification_id", "")
.setNumber("product_qty", 0)
.setString("unit", measure_in)
.setNumber("conversion", 1)
.setNumber("selling_price", price)
.setString("date_added", DateType.datetime.format(new Date()))
.setString("user_name", "")
.setString("item_type", "")
.setNumber("status", 1)
.setString("supplier", catalog)
.setNumber("fixed_price", 0)
.setNumber("cost", cost)
.setString("supplier_id", "")
.setNumber("multi_level_pricing", 0)
.setNumber("vatable", 0)
.setNumber("reorder_level", reorder)
.ok();
PreparedStatement stmt2 = conn.prepareStatement(s2);
stmt2.execute();
Lg.s(
S1_items.class,
"Successfully Added : " + lookup_code + " " + description + " : " + price);
}
return datas;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}
public static List<Srpt_print_barcodes.field> ret_data(String where, int is_item_code) {
List<Srpt_print_barcodes.field> datas = new ArrayList();
try {
Connection conn = MyConnection.connect();
String s0 =
"select "
+ "id"
+ ",barcode"
+ ",description"
+ ",generic_name"
+ ",category"
+ ",category_id"
+ ",classification"
+ ",classification_id"
+ ",sub_classification"
+ ",sub_classification_id"
+ ",product_qty"
+ ",unit"
+ ",conversion"
+ ",selling_price"
+ ",date_added"
+ ",user_name"
+ ",item_type"
+ ",status"
+ ",supplier"
+ ",fixed_price"
+ ",cost"
+ ",supplier_id"
+ ",multi_level_pricing"
+ ",vatable"
+ ",reorder_level"
+ ",markup"
+ ",main_barcode"
+ ",brand"
+ ",brand_id"
+ ",model"
+ ",model_id"
+ ",selling_type"
+ ",branch"
+ ",branch_code"
+ ",location"
+ ",location_id"
+ ",serial_no"
+ " from inventory_barcodes"
+ " "
+ where;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s0);
while (rs.next()) {
int id = rs.getInt(1);
String barcode = rs.getString(2);
String description = rs.getString(3);
String generic_name = rs.getString(4);
String category = rs.getString(5);
String category_id = rs.getString(6);
String classification = rs.getString(7);
String classification_id = rs.getString(8);
String sub_classification = rs.getString(9);
String sub_classification_id = rs.getString(10);
double product_qty = rs.getDouble(11);
String unit = rs.getString(12);
double conversion = rs.getDouble(13);
double selling_price = rs.getDouble(14);
String date_added = rs.getString(15);
String user_name = rs.getString(16);
String item_type = rs.getString(17);
int status = rs.getInt(18);
String supplier = rs.getString(19);
int fixed_price = rs.getInt(20);
double cost = rs.getDouble(21);
String supplier_id = rs.getString(22);
int multi_level_pricing = rs.getInt(23);
int vatable = rs.getInt(24);
double reorder_level = rs.getDouble(25);
double markup = rs.getDouble(26);
int main_barcode = rs.getInt(27);
String brand = rs.getString(28);
String brand_id = rs.getString(29);
String model = rs.getString(30);
String model_id = rs.getString(31);
int selling_type = rs.getInt(32);
String branch = rs.getString(33);
String branch_code = rs.getString(34);
String location = rs.getString(35);
String location_id = rs.getString(36);
String serial_no = rs.getString(37);
String code = "" + main_barcode;
if (is_item_code == 0) {
code = barcode;
}
Srpt_print_barcodes.field field = new field(code, description, selling_price, false);
datas.add(field);
}
return datas;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
MyConnection.close();
}
}