예제 #1
0
  public ArrayList FindListProduct(String id, String pName) throws Exception {
    // Log.debug("List WatchdogForm..");
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    String sql = "";
    ProductBean obj = null;
    ArrayList result = new ArrayList();
    try {
      conn = open();
      // Common.beginTransaction(conn);
      Log.debug("-->Query.");
      // setTransaction
      sql =
          " SELECT p.product_id,"
              + " p.product_name ,"
              + " p.type_product_id  ,"
              + " p.brand_product_id ,"
              + " p.price_cost,"
              + " p.price_sale ,"
              + " p.product_item ,"
              + " p.product_date_start,"
              + " p.product_date_expire ,"
              + " p.product_desc ,"
              + " b.brand_product_name,"
              + " t.type_product_name"
              + " FROM product p, brand_product b, type_product t"
              + " WHERE (p.delete_flag <> 'D' OR   p.delete_flag  is null  OR p.delete_flag <> '' ) "
              + " and b.brand_product_id = p.brand_product_id "
              + " and t.type_product_id = p.type_product_id ";
      if (!"".equals(id) && id != null) {
        sql += " and (p.product_id = ? OR p.product_id LIKE ? )";
      }
      if (!"".equals(pName) && pName != null) {
        sql += " and  (p.product_name = ? OR product_name LIKE ? )";
      }

      System.out.println("SQL : " + sql);
      int i = 1;
      pstmt = conn.prepareStatement(sql);
      if (!"".equals(id) && id != null) {
        pstmt.setString(i++, id);
        pstmt.setString(i++, "%" + id + "%");
      }
      if (!"".equals(pName) && pName != null) {
        pstmt.setString(i++, pName);
        pstmt.setString(i++, "%" + pName + "%");
      }

      rs = pstmt.executeQuery();
      Log.debug("--->execute SQL Query.");
      int c = 0;
      while (rs.next()) {
        obj = new ProductBean();
        obj.setProductId(rs.getString("product_id"));
        obj.setProductName(rs.getString("product_name"));
        obj.setTypeProductId(rs.getString("type_product_id"));
        obj.setBrandProductId(rs.getString("brand_product_id"));
        obj.setPriceCost(rs.getFloat("price_cost"));
        obj.setPriceSale(rs.getFloat("price_sale"));
        obj.setProductItem(rs.getInt("product_item"));
        obj.setProductDateStart(rs.getString("product_date_start"));
        obj.setProductDateExpire(rs.getString("product_date_expire"));
        obj.setProductDesc(rs.getString("product_desc"));
        obj.setBrandProductName(rs.getString("brand_product_name"));
        obj.setTypeProductName(rs.getString("type_product_name"));
        result.add(obj);
        c++;
      }
      // Log.debug("get UserInfo FetchSize :"+c);
      // Commit Transaction
      Log.debug("--->Query completed.");
    } catch (Exception e) {
      e.printStackTrace();
      Log.debug("Exception : " + e.getMessage());
    } finally {
      // close
      try {
        rs.close();
        pstmt.close();
        close(conn);
      } catch (Exception e) {
      }
    }
    return result;
  }
예제 #2
0
  // Insert Purchase krub.
  public boolean insertTransPurchase(List list, String invoiceId) throws Exception {
    CallableStatement clbstmt = null;
    Connection conn = null;
    int i = 1;
    // ***********
    boolean isInsert = true; // true is fail
    boolean isInsItem = true; // true is fail
    boolean isUpdProduct = true; // true is fail
    boolean isResult = true;
    try {
      // ::TODO
      conn = open();
      Common.beginTransaction(conn);
      // ********************************************************

      int totalUnit = 0;
      float totalPrice = 0.0f;
      ProductBean prod = null;
      Iterator it = list.iterator();
      while (it.hasNext()) {
        prod = (ProductBean) it.next();
        totalPrice = prod.getProductItem() * prod.getPriceSale();
        totalUnit += prod.getProductItem();
      }
      // *******************************************************
      Log.debug("-->Before Insert.");
      // Insert to DB
      clbstmt = conn.prepareCall("{call " + INSTANT_DB_NAME + ".INSERT_PURCHASE(?,?,?,?,?)}");
      clbstmt.setString(i++, invoiceId);
      clbstmt.setFloat(i++, totalPrice);
      clbstmt.setInt(i++, totalUnit);
      clbstmt.setString(i++, "99999999999"); // CustomerID
      clbstmt.setString(i++, "11111111111"); // UserID

      Log.debug("-->Mapping parameter Okay.");
      isInsert = clbstmt.execute();
      Log.debug("-->Execute INSERT_PURCHASE successfully. :" + isInsert);

      // **************************************************************
      Log.debug("-->Start Insert INSERT_PURCHASE_ITEMS.");
      it = list.iterator();
      while (it.hasNext()) {
        i = 1;
        prod = (ProductBean) it.next();
        // Insert to DB
        clbstmt = conn.prepareCall("{call " + INSTANT_DB_NAME + ".INSERT_PURCHASE_ITEMS(?,?,?,?)}");
        clbstmt.setString(i++, invoiceId);
        clbstmt.setString(i++, prod.getProductId()); // p_productId
        clbstmt.setInt(i++, prod.getProductItem());
        clbstmt.setFloat(i++, prod.getPriceSale());

        Log.debug("-->Mapping parameter Okay.");
        isInsItem = clbstmt.execute();
        Log.debug("-->INSERT_PURCHASE_ITEMS successfully. :" + isInsItem);
      }
      // **********************************************************************
      /*	        	CREATE PROCEDURE db_merchant.`INSERT_PURCHASE`(
      IN p_Id varchar(12),
      IN p_total_unit  int,
      IN p_total_price  float,
      IN p_supp_id    varchar(12),
      IN p_usr_id  varchar(12)*/

      /*	        	CREATE PROCEDURE db_merchant.`INSERT_PURCHASE_ITEMS`(
      IN p_voiceId varchar(12),
      IN p_Id varchar(12),
      IN p_unit  int,
      IN p_price  float
      )*/

      Log.debug("-->Update Product.");
      it = list.iterator();
      while (it.hasNext()) {
        i = 1;
        prod = (ProductBean) it.next();

        Log.debug("-->Before Insert.");
        // Insert to DB
        clbstmt =
            conn.prepareCall("{call " + INSTANT_DB_NAME + ".UPDATE_PRODUCT_BY_PURCHASE(?,?,?,?)}");
        clbstmt.setString(i++, prod.getProductId());
        clbstmt.setFloat(i++, prod.getPriceCost());
        clbstmt.setFloat(i++, prod.getPriceSale());
        clbstmt.setInt(i++, prod.getProductItem());

        Log.debug("-->Mapping parameter Okay.");
        isUpdProduct = clbstmt.execute();
        Log.debug("-->Update product successfully .:" + isUpdProduct);
      }
      /*	        	CREATE PROCEDURE db_merchant.`UPDATE_PRODUCT_BY_PURCHASE`(
      		IN p_id  varchar(12),
      		IN p_cost   float,
      		IN p_sale   float,
      		IN p_item     int(7)
      		)
      */

      if (!isInsert && !isInsItem && !isUpdProduct) {
        isResult = false;
        Log.debug("-->Update product successfully .");
      } else {
        isResult = true;
        Log.debug("-->Update product not successfully .");
      }

      // **************************************************************
      Common.commitTransaction(conn);
      Log.debug("-->commit successfully.");
    } catch (Exception e) {
      isResult = true;
      e.fillInStackTrace();
      Log.debug("Insert Exception e:" + e.toString());
      // try{
      Common.rollbackTransaction(conn);
      // }catch(Exception ex){}
    } finally {
      try {
        if (clbstmt != null) clbstmt.close();
        if (conn != null) conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    // *******Return*********
    return isResult; // #End
  }