package com.ilp.tsi.um.services;

import java.sql.*;

import com.ilp.tsi.um.beans.*;
import com.ilp.tsi.db.DB;

public class CreateService {

     public void  createRet(CreateBean x)
     {
         String id=x.getId();
         String name=x.getName();
         String password=x.getPassword();
         String contactno=x.getContact();
         String addr=x.getAddress();
         String email=x.getEmail();

         Connection conn=null;
         Statement stmt=null;
         ResultSet rs=null;
            try 
            {    
                DB dao=new DB();
                conn=dao.getConnection();    
                stmt=conn.createStatement();    

                rs = stmt.executeQuery("insert into RETAILER (RETAILER_ID, RETAILER_NAME, PASSWORD, CONTACT_NO, ADDRESS, EMAIL_ID) values ( '" +id+ "','" + name + "','"+password+"',' " + contactno + " ','" + addr + "','"+email+"')");       
                stmt.executeQuery("commit");

            }
            catch(Exception e)
            {        
                e.printStackTrace();    
            } 
            finally
            {
                try {
                    conn.close();
                    stmt.close();
                    } catch (SQLException e) {

                    e.printStackTrace();
                }


            }
//          return true;
     }

     public void CreateIm(CreateBean x)
     {
         String id=x.getId();
         String name=x.getName();
         String password=x.getPassword();
         String contactno=x.getContact();
         String addr=x.getAddress();


         Connection conn=null;
         Statement stmt=null;
         ResultSet rs=null;
            try 
            {    
                DB dao=new DB();
                conn=dao.getConnection();    
                stmt=conn.createStatement();    

                rs = stmt.executeQuery("insert into IM_DETAILS (IM_ID,IM_NAME,PASSWORD,CONTACT_NO,ADDRESS) values ( '" +id+ "','" + name + "','"+password+"',' " + contactno + " ',' " + addr + " ')");       
                stmt.executeQuery("commit");
            }
            catch(Exception e)
            {        
                e.printStackTrace();      
            } 
            finally
            {
                try {
                    conn.close();
                    stmt.close();
                    } catch (SQLException e) {

                    e.printStackTrace();
                }


            }
            //return rs; 
     }

     public Prodtag countRet(CreateBean cb)
     {
          Connection conn=null;
          Statement stmt=null;
          ResultSet rs=null,rs1=null;
            CreateService cs=new CreateService();
            Prodtag prodbean=new Prodtag();
          try
          {
            DB obj=new DB();
            conn=obj.getConnection();
            stmt=conn.createStatement();
            rs=stmt.executeQuery("SELECT COUNT(*) FROM RETAILER");

            int temp=0;
            //counting no of records in retailer table
            while(rs.next())
            {
            temp= rs.getInt("count(*)");
            }
            temp++;
            String id="";
            String temp1="";
            while(true)
            {


             temp1 = new Integer(temp).toString();
            id="RET".concat(temp1); //generating unique retailer ID

            rs1=stmt.executeQuery("select * from retailer where retailer_id='"+id+"'");
            if(rs1.next())
            {
                temp++;
                continue ;

            }
            else
            {
                break;
            }
            }
                cb.setId(id);
            String password=id;
                cb.setPassword(password);
            prodbean.setRetailerId(id);

            cs.createRet(cb);
          }
          catch(Exception e)
            {        
                e.printStackTrace();       
            } 
          finally
            {
                try {
                    conn.close();
                    stmt.close();
                    rs.close();
                    } catch (SQLException e) {

                    e.printStackTrace();
                }


            }
            return prodbean;
     }

     public ResultSet countIm(CreateBean cb)
     {
         CreateService cs=new CreateService(); 
         Connection conn=null;
          Statement stmt=null;
          ResultSet rs=null;
          try
          {
            DB obj=new DB();
            conn=obj.getConnection();
            stmt=conn.createStatement();
            rs=stmt.executeQuery("SELECT COUNT(*) FROM IM_DETAILS");
            int temp=0;
            //counting no of records in Inventory manager table
            while(rs.next())
            {
            temp= rs.getInt("count(*)");
            }
            temp++;
            String temp1=new Integer(temp).toString();
            String id="IM".concat(temp1); //generating unique Inventory manager ID
                cb.setId(id);
            String password=id;
                cb.setPassword(password);


            cs.CreateIm(cb);
          }
          catch(Exception e)
            {        
                e.printStackTrace();        
            } 
          finally
            {
                try {
                    conn.close();
                    stmt.close();
                    } catch (SQLException e) {

                    e.printStackTrace();
                }


            }
            return rs;
     }
}

-
-
-
-

package com.ilp.tsi.pm.services;
import java.sql.*;
import java.util.*;

import com.ilp.tsi.pm.beans.StockBean;
//import com.ilp.tsi.pm.dao.DaoPack;
//class for Stock
public class StockService{


//Adding stock
    public boolean AddStock(StockBean bean, Connection con) throws SQLException 

    {
        Statement stmt=null;
        ResultSet rs = null;
        boolean chk=false;

        String productId = bean.getProductId();

        String modelName = bean.getModelName();
        int quantity = bean.getQuantity();


        Random random=new Random();


        try
        {       String s=null;
                int count=0,c1=0,c2=0;  
                stmt = con.createStatement();
                rs=stmt.executeQuery("select * from advance where product_id='"+productId+"'");
                if(rs.next())
                {
                    //System.out.println("sdadasf "+rs.getString(1)+" "+rs.getString(2)+" "+rs.getInt(3));
                    s=rs.getString(1);
                    c1=rs.getInt(3);
                }
            for(int i=1;i<=quantity ;i++)
            {
                int  rand_no= random.nextInt();
                String temp=productId.concat(modelName);
                String productCode=temp+rand_no;
                c2++;
                if(c2>c1)
                {
                rs =stmt.executeQuery("insert into stock (product_id,product_code,prod_model_name,status)values ('"+productId+"','"+productCode+"','"+modelName+"', 'available')");
                stmt.executeQuery("commit");
                }
                else
                {
                    System.out.println("gffsdghsdfhsdfhjdfhjdfhdg ");
                    stmt.executeUpdate("insert into stock (product_id,product_code,prod_model_name,status,order_id)values ('"+productId+"','"+productCode+"','"+modelName+"', 'allocated','"+s+"')");
                    count++;
                }
                rs=stmt.executeQuery("select count(*) from stock where product_id='"+productId+"'");

                if (rs.next())
                { bean.setQuantity(Integer.parseInt(rs.getString(1)));


                }

            }
            count=c1-count;
            System.out.println("gergsghshjs "+count);
            stmt.executeUpdate("update advance set quant_reqd='"+count+"' where product_id='"+productId+"' and order_id='"+s+"'");
            stmt.executeUpdate("update product set quantity="+bean.getQuantity()+" where product_id='"+productId+"'");                  
            chk=true;  

        }
                catch(SQLException e)
                    {


                    }
                finally{
                    rs.close();
                    stmt.close();
                    con.close();
                }
        return chk;
        }

//Getting product model name

public StockBean productModelName(StockBean sb,Connection con) throws SQLException
    {
        Statement st=null;
        ResultSet rs = null;
        try
        {


            String productId =sb.getProductId();
            st= con.createStatement();                  
            rs = st.executeQuery("select * from product where product_id='" + productId + "'");
            while(rs.next())
        {
                sb.setProductId(rs.getString(1));
                sb.setModelName(rs.getString(2));
//              System.out.println(sb.getModelName());

            }
//          System.out.println("Search completed.");


        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            rs.close();
            st.close();
            con.close();
        }
        return sb;
    }

    //Deleting stock

    public boolean deleteStock(StockBean bean, Connection con) throws SQLException 

    {
        Statement stmt=null;
        ResultSet rs = null;
        int count=0;
        boolean chk=false;
        String productId = bean.getProductId();
        String modelName = bean.getModelName();

        String productCode = bean.getProductCode();

//      System.out.println("this is from delete service---");
//      System.out.println(productId);
//      System.out.println(modelName);
//      System.out.println(productCode);


        try
        {           
                stmt = con.createStatement();
//          System.out.println("service page3");
//          System.out.println(productCode);
//              System.out.println("delete from stock where productCode='"+productCode+"'");
    rs =    stmt.executeQuery("delete from stock where product_code='"+productCode+"'");
    stmt.executeQuery("commit");
    rs=stmt.executeQuery("select count(*) from stock where product_id='"+productId+"'");

    if (rs.next())
    { bean.setQuantity(Integer.parseInt(rs.getString(1)));


    }
    rs=stmt.executeQuery("select *from stock where product_id='"+productId+"'");


    rs=stmt.executeQuery("update product set quantity="+bean.getQuantity()+" where product_id='"+productId+"'");
//  System.out.println("product table Successfully updated");
    chk=true;
    }

    catch(SQLException e)
        {
//          System.out.println(e);
        }
    finally{
        rs.close();
        stmt.close();
        con.close();
        }
        return chk;
    }



        }   



-
-
-
-

package com.ilp.tsi.om.services;
import java.sql.*;

import com.ilp.tsi.om.beans.UpdateCusBean;

public class UpdateService 
{
    /*
     * updates customer info in the Db
     */
    public boolean updating(UpdateCusBean updatecusbean, Connection connection)throws SQLException 
    {
        Statement statement = null;
        try 
        {
            String customerid = updatecusbean.getCustomerId();
            String customername =updatecusbean.getCustomerName();
            String customeradd= updatecusbean.getCustomerAddress();
            String contactno=updatecusbean.getContactNumber();
            statement=connection.createStatement();
            statement.executeUpdate("update customer set cust_name='"+customername+"' ,address='"+customeradd+"',contact_no='"+contactno+"' where cust_id='"+customerid+"'");
            return true;            
        } 
        catch (SQLException sqle) 
        {
            connection.close();
            return false;
        } 
        catch (Exception e) 
        {
            connection.close();
            return false;
        }
        finally
        {
            connection.close();
            statement.close();
        }
    }       
}

-
-
-
-
-

package com.ilp.tsi.db;
import com.ilp.tsi.utils.*;
import java.sql.*;
public class DB {
    Connection conn=null;
    public Connection getConnection()
    {
        try
        {
            Class.forName(Constant.DRIVER);
        }
        catch(Exception E)
        {
            E.printStackTrace();
        }
        try{
            conn=DriverManager.getConnection(Constant.URL,Constant.username,Constant.pwd);
        }
        catch(Exception E)
        {
            E.printStackTrace();
        }

        return conn;

    }

}

-
-
-
-

package com.ilp.tsi.utils;

public class Constant {
public static final String DRIVER="oracle.jdbc.driver.OracleDriver";
public static final String URL="jdbc:oracle:thin:@172.24.137.30:1521:ORA10G";


}