import java.sql.*; import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
public class MyHelper {
static Connection conn=null; static Statement st=null; static ResultSet rs=null; static String password=null; public static ArrayList<RetailerBean> view() { ArrayList<RetailerBean> s=new ArrayList<RetailerBean>(); try { conn=MyConnection.create(); st=conn.createStatement(); ResultSet rs=st.executeQuery("Select * from RETAILER_TBL "); while(rs.next()) { RetailerBean rb=new RetailerBean(); rb.setRetailerId(rs.getInt(1)); rb.setName(rs.getString(2)); rb.setLicenseNumber(rs.getInt(3)); rb.setAddress(rs.getString(4)); rb.setContactNumber(rs.getLong(5)); rb.setDob(rs.getString(6)); rb.setEmailId(rs.getString(8)); rb.setManagerId(rs.getInt(7)); s.add(rb); } } catch (SQLException e) { e.printStackTrace(); } return s; } public static ArrayList<ManagerBean> viewM() { ArrayList<ManagerBean> s=new ArrayList<ManagerBean>(); try { conn=MyConnection.create(); st=conn.createStatement(); ResultSet rs=st.executeQuery("Select * from MANAGER_TBL "); while(rs.next()) { ManagerBean rb=new ManagerBean(); rb.setMANAGER_ID(rs.getInt(1)); rb.setMANAGER_NAME(rs.getString(2)); rb.setMANAGER_LICENCE_NO(rs.getInt(3)); rb.setMANAGER_ADDRESS(rs.getString(4)); rb.setMANAGER_CONTACT_NO(rs.getLong(5)); rb.setMANAGER_DOB(rs.getString(6)); rb.setMANAGER_EMAIL(rs.getString(7)); s.add(rb); } } catch (SQLException e) { e.printStackTrace(); } return s; } public static ArrayList<RetailerBean> catchRecord(int i) { ArrayList<RetailerBean> ar=new ArrayList<RetailerBean>(); try{ conn=MyConnection.create(); st=conn.createStatement(); RetailerBean t=new RetailerBean(); rs=st.executeQuery("select * from RETAILER_TBL where RETAILER_ID="+i+""); rs.next(); { t.setRetailerId(rs.getInt(1)); t.setName(rs.getString(2)); t.setLicenseNumber(rs.getInt(3)); t.setAddress(rs.getString(4)); t.setContactNumber(rs.getLong(5)); t.setDob(rs.getString(6)); t.setManagerId(rs.getInt(7)); t.setEmailId(rs.getString(8)); ar.add(t); } } catch(Exception e) { e.printStackTrace(); } return ar; } public static ArrayList<ManagerBean> catchRecordM(int i) { ArrayList<ManagerBean> ar=new ArrayList<ManagerBean>(); try{ conn=MyConnection.create(); st=conn.createStatement(); ManagerBean t=new ManagerBean(); rs=st.executeQuery("select * from MANAGER_TBL where MANAGER_ID="+i+""); rs.next(); { t.setMANAGER_ID(rs.getInt(1)); t.setMANAGER_NAME(rs.getString(2)); t.setMANAGER_LICENCE_NO(rs.getInt(3)); t.setMANAGER_ADDRESS(rs.getString(4)); t.setMANAGER_CONTACT_NO(rs.getLong(5)); t.setMANAGER_DOB(rs.getString(6)); t.setMANAGER_EMAIL(rs.getString(7)); ar.add(t); } } catch(Exception e) { e.printStackTrace(); } return ar; } public static int updateRetailer(int id,String address,long contact,String email) { int x=0; try { System.out.println(id); Connection con=MyConnection.create(); Statement st=con.createStatement(); st.executeUpdate("Update RETAILER_TBL set RETAILER_ADDRESS='"+address+"' where RETAILER_ID="+id+""); st.executeUpdate("Update RETAILER_TBL set RETAILER_CONTACT_NO="+contact+" where RETAILER_ID="+id+""); x=st.executeUpdate("Update RETAILER_TBL set RETAILER_EMAIL='"+email+"' where RETAILER_ID="+id+""); } catch(Exception e) { System.out.println("Error : "+e); } return x; } public static int updateManager(int id,String address,long contact,String email) { int x=0; try { Connection con=MyConnection.create(); Statement st=con.createStatement(); st.executeUpdate("Update MANAGER_TBL set MANAGER_ADDRESS='"+address+"' where MANAGER_ID="+id+""); st.executeUpdate("Update MANAGER_TBL set MANAGER_CONTACT_NO="+contact+" where MANAGER_ID="+id+""); x=st.executeUpdate("Update MANAGER_TBL set MANAGER_EMAIL='"+email+"' where MANAGER_ID="+id+""); } catch(Exception e) { System.out.println("Error : "+e); } return x; } public static ArrayList<RetailerBean> update(int rid) { ArrayList<RetailerBean> a=new ArrayList<RetailerBean>(); RetailerBean t=new RetailerBean(); try { Connection con=MyConnection.create(); Statement st=con.createStatement(); ResultSet rs=st.executeQuery("select * from RETAILER_TBL where RETAILER_ID='"+rid+"'"); rs.next(); t.setRetailerId(rs.getInt(1)); t.setName(rs.getString(2)); t.setLicenseNumber(rs.getInt(3)); t.setAddress(rs.getString(4)); t.setContactNumber(rs.getLong(5)); t.setDob(rs.getString(6)); t.setManagerId(rs.getInt(7)); t.setEmailId(rs.getString(8)); a.add(t); } catch(Exception e) { System.out.println("Error : "+e); } return a; } public static ArrayList<ManagerBean> updateM(int rid) { ArrayList<ManagerBean> a=new ArrayList<ManagerBean>(); ManagerBean t=new ManagerBean(); try { Connection con=MyConnection.create(); Statement st=con.createStatement(); ResultSet rs=st.executeQuery("select * from MANAGER_TBL where MANAGER_ID='"+rid+"'"); rs.next(); t.setMANAGER_ID(rs.getInt(1)); t.setMANAGER_NAME(rs.getString(2)); t.setMANAGER_LICENCE_NO(rs.getInt(3)); t.setMANAGER_ADDRESS(rs.getString(4)); t.setMANAGER_CONTACT_NO(rs.getLong(5)); t.setMANAGER_DOB(rs.getString(6)); t.setMANAGER_EMAIL(rs.getString(7)); a.add(t); } catch(Exception e) { System.out.println("Error : "+e); } return a; } public static ArrayList<String> getProducts(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select distinct PRODUCT_NAME from PRODUCT_TBL where PRODUCT_NAME not in(select PRODUCT_NAME from PRODUCT_RETAILER_TBL where RETAILER_ID="+a+")"); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static ArrayList<String> getRetailers(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select distinct RETAILER_NAME from RETAILER_TBL where RETAILER_NAME not in(select RETAILER_NAME from RETAILER_MANAGER_TBL where MANAGER_ID="+a+")"); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static ArrayList<String> getProductsFrom(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select PRODUCT_NAME from PRODUCT_RETAILER_TBL where RETAILER_ID="+a+""); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static ArrayList<String> getRetailersFrom(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select RETAILER_NAME from RETAILER_MANAGER_TBL where MANAGER_ID="+a+""); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static ArrayList<String> getTaggedProducts(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select PRODUCT_NAME from PRODUCT_RETAILER_TBL where RETAILER_ID="+a+""); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static ArrayList<String> getTaggedRetailers(int a) { ArrayList<String> b=new ArrayList<String>(); Connection con=MyConnection.create(); Statement st; try { st = con.createStatement(); ResultSet rs=st.executeQuery("select RETAILER_NAME from RETAILER_MANAGER_TBL where MANAGER_ID="+a+""); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) {e.printStackTrace();} return b; } public static int tagProducts(String ar[], int a) { Connection con=MyConnection.create(); Statement st; int z=0; try { st = con.createStatement(); for(int i=0;i<ar.length;i++) { int c=st.executeUpdate("insert into PRODUCT_RETAILER_TBL values('"+ar[i]+"',"+a+")"); z=z+c; } } catch(Exception e) {e.printStackTrace();} return z; } public static int tagRetailers(String ar[], int a) { Connection con=MyConnection.create(); Statement st; int z=0; try { st = con.createStatement(); for(int i=0;i<ar.length;i++) { int c=st.executeUpdate("insert into RETAILER_MANAGER_TBL values('"+ar[i]+"',"+a+")"); z=z+c; } } catch(Exception e) {e.printStackTrace();} return z; } public static int untagProducts(String ar[], int a) { Connection con=MyConnection.create(); Statement st; int z=0; try { st = con.createStatement(); for(int i=0;i<ar.length;i++) { int c=st.executeUpdate("delete from PRODUCT_RETAILER_TBL where PRODUCT_NAME='"+ar[i]+"' and RETAILER_ID="+a+""); z=z+c; } } catch(Exception e) {e.printStackTrace();} return z; } public static int untagRetailers(String ar[], int a) { Connection con=MyConnection.create(); Statement st; int z=0; try { st = con.createStatement(); for(int i=0;i<ar.length;i++) { int c=st.executeUpdate("delete from RETAILER_MANAGER_TBL where RETAILER_NAME='"+ar[i]+"' and MANAGER_ID="+a+""); z=z+c; } } catch(Exception e) {e.printStackTrace();} return z; } public static int delete(int v) { int a=0; try { Connection con=MyConnection.create(); Statement st; st=con.createStatement(); { int e=st.executeUpdate("delete from RETAILER_TBL where RETAILER_ID="+v+""); a=a+e; rs=st.executeQuery("select * from RETAILER_MANAGER_TBL where RETAILER_ID="+v+""); if(rs.next()) { int q=st.executeUpdate("delete from RETAILER_MANAGER_TBL where RETAILER_ID="+v+""); a=a+q; } rs=st.executeQuery("select * from PRODUCT_RETAILER_TBL where RETAILER_ID="+v+""); if(rs.next()) { int w=st.executeUpdate("delete from PRODUCT_RETAILER_TBL where RETAILER_ID="+v+""); a=a+w; } } } catch(Exception e) { System.out.println(e); } return a; } public static int deleteM(int v) { int a=0; try { Connection con=MyConnection.create(); Statement st; st=con.createStatement(); { } } catch(Exception e) { System.out.println(e); } return a; } public static String passwordGeneration(String name) { int count=0; String g=""; String a=name.substring(0,4); for(int i=0;i<name.length();i++) { char c=name.charAt(i); if(c=='A' || c=='E' || c=='I' || c=='O' || c=='U' || c=='a' || c=='e' || c=='i' || c=='o' || c=='u') count++; } if(count<=9 && count>=0) { g="0"+count; a=a+g; } else { a=a+count; } password=a; return a; } public static int insertRetailer(RetailerBean i, int id) { try{ Connection con=MyConnection.create(); Statement st=con.createStatement(); int k=st.executeUpdate("insert into RETAILER_TBL values("+id+",'"+i.getName()+"',"+i.getLicenseNumber()+",'"+i.getAddress()+"',"+i.getContactNumber()+",'"+i.getDob()+"',2001,'"+i.getEmailId()+"')"); st.executeUpdate("insert into LOGIN_TBL values("+id+",'"+i.getName()+"','"+password+"','Retailer')"); if(k==1) { System.out.println("1 row added"); // no++; } } catch(Exception e) { System.out.println("Error : "+e); } return 0; } public static int getLastID() { int last=1110; try { Connection con=MyConnection.create(); Statement st=con.createStatement(); rs=st.executeQuery("select max(RETAILER_ID) from RETAILER_TBL"); rs.next(); last=rs.getInt(1); } catch(Exception e) { } if(last==0) return last; else return last; } public static int insertManager(ManagerBean i, int id) { try{ Connection con=MyConnection.create(); Statement st=con.createStatement(); int k=st.executeUpdate("insert into MANAGER_TBL values("+id+",'"+i.getMANAGER_NAME()+"',"+i.getMANAGER_LICENCE_NO()+",'"+i.getMANAGER_ADDRESS()+"',"+i.getMANAGER_CONTACT_NO()+",'"+i.getMANAGER_DOB()+"','"+i.getMANAGER_EMAIL()+"')"); st.executeUpdate("insert into LOGIN_TBL values("+id+",'"+i.getMANAGER_NAME()+"','"+password+"','Manager')"); if(k==1) { System.out.println("1 row added"); } } catch(Exception e) { System.out.println("Error : "+e); } return 0; } public static int getLastIDM() { int last=5000; try { Connection con=MyConnection.create(); Statement st=con.createStatement(); rs=st.executeQuery("select max(MANAGER_ID) from MANAGER_TBL"); rs.next(); last=rs.getInt(1); } catch(Exception e) { e.printStackTrace(); } if(last==0) return last; else return last; } public static int authenticate(int uid,String password,String role) { int flag=0; try{ Connection con=MyConnection.create(); Statement st=con.createStatement(); ResultSet rs=st.executeQuery("select * from LOGIN_TBL where USER_ID="+uid); rs.next(); if(uid==9999 && password.equals("admin99") && role.equals("Admin")) { flag=1; } if(rs.getInt(1)==uid && rs.getString(3).equals(password) && role.equals("Retailer")) { flag=2; } if(rs.getInt(1)==uid && rs.getString(3).equals(password) && role.equals("Inventory Manager")) { flag=3; } } catch(Exception e) { System.out.println("Error : "+e); } if(flag!=0) return flag; else return 0; } /*public static ArrayList fetchID() { ArrayList<String> b=new ArrayList<String>(); try{ conn=MyConnection.create(); st=conn.createStatement(); rs=st.executeQuery("select RETAILER_ID from RETAILER_TBL"); while(rs.next()) { b.add(rs.getString(1)); } } catch(Exception e) { e.printStackTrace(); } return b; }*/
}