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

}