예제 #1
0
  public ArrayList<PurchaseOrder> viewPO(String vendorId) throws SQLException {
    /**
     * For viewing the details of purchase order corresponding to particular vendor
     *
     * @param vendorId
     * @return ArrayList
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement = connection.prepareStatement(Selection.selectPoOfVendor);
    logger.debug(
        "For fetching the details of purchase order corresponding to particular vendor" + vendorId);
    preparedStatement.setString(1, vendorId);
    preparedStatement.setString(2, "APPROVED");
    ResultSet resultSet = preparedStatement.executeQuery();
    ArrayList<PurchaseOrder> purchaseOrders = new ArrayList<PurchaseOrder>();
    while (resultSet.next()) {
      String poNo = resultSet.getString(1);
      Date orderingDate = resultSet.getDate(2);
      String retailerName = resultSet.getString(3);
      Date expectedDate = resultSet.getDate(4);
      String orderBy = resultSet.getString(5);
      PurchaseOrder purchaseOrder =
          new PurchaseOrder(poNo, orderingDate, retailerName, expectedDate, orderBy);
      purchaseOrders.add(purchaseOrder);
    }
    return purchaseOrders;
  }
예제 #2
0
  public PurchaseOrder viewPONUmber(String poNumber) throws SQLException {
    /**
     * For viewing the details of purchase order corresponding to a particular poNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement = connection.prepareStatement(Selection.selectPoFromPo);
    logger.debug(
        "fetching details of purchase order corresponding to a particular poNumber" + poNumber);
    preparedStatement.setString(1, poNumber);
    ResultSet resultSet = preparedStatement.executeQuery();
    if (resultSet.next()) {
      String poNo = resultSet.getString(1);
      Date orderingDate = resultSet.getDate(2);
      String retailerName = resultSet.getString(3);
      Date expectedDate = resultSet.getDate(4);
      String orderBy = resultSet.getString(5);
      PurchaseOrder purchaseOrder =
          new PurchaseOrder(poNo, orderingDate, retailerName, expectedDate, orderBy);
      return purchaseOrder;
    }
    return null;
  }
예제 #3
0
  public long viewDeliveryPin(String billNo) throws SQLException {
    /**
     * fetching pincode corresponding to poNumber and billNo
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    long pincode = 0;
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(
            "SELECT PINCODE FROM PO_VENDOR WHERE PO_NO IN(SELECT PO_NO FROM BILL_DETAILS WHERE BILL_NO = ?)");

    preparedStatement.setString(1, billNo);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      pincode = resultSet.getLong("PINCODE");
      logger.debug("fetching pincode corresponding to poNumber and billNo" + billNo);
    }

    return pincode;
  }
예제 #4
0
 public ArrayList<Appointment> viewAppoinment(String vendorId) throws SQLException {
   /**
    * fetching appointment details of particular vendor
    *
    * @param poNumber
    * @return
    * @throws SQLException
    */
   DBConnection dbConnection = new DBConnection();
   Connection connection = dbConnection.getConnection();
   logger.debug("fetching appointment details of particular vendor" + vendorId);
   PreparedStatement preparedStatement =
       connection.prepareStatement(
           "SELECT * FROM APPOINTMENT WHERE V_NO=? AND BILL_NO IN(SELECT BILL_NO FROM VENDOR_BILL WHERE READY_FOR_SHIPMENT='NO') ORDER BY STATUS ,BILL_NO ASC");
   preparedStatement.setString(1, vendorId);
   ResultSet resultSet = preparedStatement.executeQuery();
   ArrayList<Appointment> appointments = new ArrayList<Appointment>();
   while (resultSet.next()) {
     String poNo = resultSet.getString(1);
     String dcNo = resultSet.getString(2);
     String vNo = resultSet.getString(3);
     Date expectedDate = resultSet.getDate(4);
     Date fixedDate = resultSet.getDate(5);
     String status = resultSet.getString(6);
     Appointment appointment = new Appointment(poNo, dcNo, vNo, expectedDate, fixedDate, status);
     appointments.add(appointment);
   }
   return appointments;
 }
예제 #5
0
  public void addVendorBill(VendorBill vendorBill) throws SQLException {
    /**
     * For inserting values in vendor bill corresponding to particular vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement ps =
        connection.prepareStatement("INSERT INTO VENDOR_BILL VALUES(?,?,?,?,?,?,?,?,?)");
    logger.debug("inserting values in VENDOR_BILL" + vendorBill.getBillNo());
    ps.setString(1, vendorBill.getPoNo());
    ps.setString(2, vendorBill.getVendorNo());
    ps.setString(3, vendorBill.getBillNo());
    ps.setString(4, vendorBill.getTypeOfDelivery());
    ps.setString(5, vendorBill.getStatus());
    ps.setString(6, vendorBill.getReadyForShipment());
    ps.setString(7, vendorBill.getDirectToStore());
    ps.setDate(8, vendorBill.getDateOFDelivery());
    ps.setString(9, vendorBill.getOrderBy());
    ps.execute();
  }
예제 #6
0
  public String addBillDetails(String poNumber, double totalAmount, double totalQuantity)
      throws SQLException {
    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();
    String billNo = "";

    PreparedStatement ps = connection.prepareStatement("select 'BILL'||BILL_SEQ.nextval FROM dual");

    ResultSet rs = ps.executeQuery();
    if (rs.next()) {

      billNo = rs.getString(1);
      System.out.println(billNo);
      logger.debug("generating bill no" + billNo);
    }

    PreparedStatement preparedStatement =
        connection.prepareStatement("INSERT INTO BILL_DETAILS VALUES(?,?,?,?)");
    logger.debug("inserting values into Bill_Details" + billNo);
    preparedStatement.setString(1, billNo);
    preparedStatement.setString(2, poNumber);
    preparedStatement.setDouble(3, totalAmount);
    preparedStatement.setDouble(4, totalQuantity);
    preparedStatement.execute();
    return billNo;
  }
예제 #7
0
  public void checkPOStatus(String poNumber) throws SQLException {

    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(Selection.selectPoProductFromPo);
    logger.debug("retrieving the details of purchase order using poNumber" + poNumber);
    preparedStatement.setString(1, poNumber);

    ResultSet resultSet = preparedStatement.executeQuery();
    if (resultSet.next()) {

    } else {

      PreparedStatement preparedStatement2 = connection.prepareStatement(Selection.updatePoStatus);
      logger.debug("updating status CLOSED of PO for particular PO_NO" + poNumber);
      preparedStatement2.setString(1, "CLOSED");
      preparedStatement2.setString(2, poNumber);
      preparedStatement2.execute();
    }
  }
예제 #8
0
  public ArrayList<POProduct> viewPOProduct(String poNumber) throws SQLException {
    /**
     * For viewing the details of all products corresponding to poNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(Selection.selectPoProductFromPo);
    logger.debug(
        "fetching details of all products corresponding to particular poNumber" + poNumber);
    preparedStatement.setString(1, poNumber);
    ResultSet resultSet = preparedStatement.executeQuery();
    ArrayList<POProduct> poProducts = new ArrayList<POProduct>();
    while (resultSet.next()) {
      String poNo = resultSet.getString(1);
      String productId = resultSet.getString(2);
      Double quantity = resultSet.getDouble(3);
      POProduct poProduct = new POProduct(poNo, productId, quantity);
      poProducts.add(poProduct);
    }
    return poProducts;
  }
예제 #9
0
  public String updateQuantity(String poNumber, String productId, int quantity)
      throws SQLException {
    /**
     * For updating the quantity corresponding to poNumber and particular productId
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    int newQuantity = 0;
    String status = "updated";
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(Selection.selectQuantityFromPoProduct);
    logger.debug(
        "fetching quantity from PO_PRODUCT corresponding to particular poNumber and productId"
            + poNumber
            + " "
            + productId);
    preparedStatement.setString(1, productId);
    preparedStatement.setString(2, poNumber);

    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      newQuantity = resultSet.getInt("QUANTITY");
    }
    newQuantity = newQuantity - quantity;
    System.out.println("New" + newQuantity);

    if (newQuantity == 0) {

      PreparedStatement preparedStatement2 =
          connection.prepareStatement(Selection.deleteFromPoProduct);
      logger.debug("deleting PO_PRODUCT when quantity is 0" + poNumber + " " + productId);
      preparedStatement2.setString(1, productId);
      preparedStatement2.setString(2, poNumber);
      preparedStatement2.executeUpdate();
      return "unavailable stock";
    } else {

      PreparedStatement preparedStatement1 = connection.prepareStatement(Selection.updatePoProduct);
      logger.debug(
          "updating quantity of PO_PRODUCT"
              + productId
              + "Quantity "
              + quantity
              + "PO Number"
              + poNumber);
      preparedStatement1.setInt(1, newQuantity);
      preparedStatement1.setString(2, productId);
      preparedStatement1.setString(3, poNumber);
      preparedStatement1.executeUpdate();
      status = "updated";
    }
    return status;
  }
예제 #10
0
 public void readyForShipment(String billNo) throws SQLException {
   // TODO Auto-generated method stub
   /**
    * fetching pincode corresponding to poNumber and billNo
    *
    * @param poNumber
    * @return
    * @throws SQLException
    */
   DBConnection dbConnection = new DBConnection();
   Connection connection = dbConnection.getConnection();
   PreparedStatement preparedStatement =
       connection.prepareStatement(
           "UPDATE VENDOR_BILL SET READY_FOR_SHIPMENT='YES' WHERE BILL_NO=?");
   preparedStatement.setString(1, billNo);
   preparedStatement.execute();
   logger.debug("Ready For shipment flag is set to YES" + billNo);
 }
예제 #11
0
 public void addShipment(Shipment shipment) throws SQLException {
   /**
    * inserting shipment details
    *
    * @param poNumber
    * @return
    * @throws SQLException
    */
   DBConnection dbConnection = new DBConnection();
   Connection connection = dbConnection.getConnection();
   logger.debug("inserting values in SHIPMENT table " + shipment.getBillNo());
   PreparedStatement preparedStatement =
       connection.prepareStatement("INSERT INTO SHIPMENT VALUES(?,?,?)");
   preparedStatement.setString(1, shipment.getBillNo());
   preparedStatement.setString(2, shipment.getPoNumber());
   preparedStatement.setString(3, shipment.getTruckNo());
   preparedStatement.execute();
 }
예제 #12
0
  public void fixAppointment(Appointment appointment) throws SQLException {
    /**
     * For inserting appointment details corresponding to particular vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();
    logger.debug("inserting values in Appointment table Bill No" + appointment.getPoNo());
    PreparedStatement ps =
        connection.prepareStatement("INSERT INTO APPOINTMENT VALUES(?,?,?,?,?,?)");

    ps.setString(1, appointment.getPoNo());
    ps.setString(2, appointment.getDcNo());
    ps.setString(3, appointment.getvNo());
    ps.setDate(4, appointment.getExpectedDate());
    ps.setDate(5, appointment.getFixedDate());
    ps.setString(6, appointment.getStatus());
    ps.execute();
  }
예제 #13
0
  public poVendor viewPOVendor(String poNumber) throws SQLException {
    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(Selection.selectPoVendorFromPo);
    logger.debug(
        "retrieving the details of purchase order using poNumber corresponding to particular vendorNumber"
            + poNumber);
    preparedStatement.setString(1, poNumber);
    ResultSet resultSet = preparedStatement.executeQuery();
    if (resultSet.next()) {
      String vNo = resultSet.getString(1);
      String poNo = resultSet.getString(2);
      String directToStore = resultSet.getString(3);
      String address1 = resultSet.getString(4);
      String address2 = resultSet.getString(5);
      String pincode = resultSet.getString(6);
      String city = resultSet.getString(7);
      String state = resultSet.getString(8);
      String landmark = resultSet.getString(9);
      poVendor poVendor =
          new poVendor(
              poNo, vNo, directToStore, address1, address2, pincode, city, state, landmark);
      return poVendor;
    }
    return null;
  }
예제 #14
0
  public long viewVendorPin(String vendorId) throws SQLException {
    /**
     * fetching pincode of particular vendor
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    long pincode = 0;
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement("SELECT PINCODE FROM VENDOR WHERE V_NO = ?");

    preparedStatement.setString(1, vendorId);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      pincode = resultSet.getLong("PINCODE");
      logger.debug("fetching pincode of particular vendor" + pincode);
    }

    return pincode;
  }