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;
}*/
}
Ads
