Package org.jallinone.sales.pricelist.server

Source Code of org.jallinone.sales.pricelist.server.SalePricesBean

package org.jallinone.sales.pricelist.server;

import org.openswing.swing.server.*;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;

import org.openswing.swing.message.receive.java.*;
import org.openswing.swing.message.send.java.GridParams;

import java.sql.*;

import org.openswing.swing.customvo.java.CustomValueObject;
import org.openswing.swing.logger.server.*;
import org.jallinone.system.server.*;
import org.jallinone.sales.pricelist.java.*;
import org.jallinone.variants.java.VariantNameVO;
import org.jallinone.variants.java.VariantsMatrixColumnVO;
import org.jallinone.variants.java.VariantsMatrixRowVO;
import org.jallinone.variants.java.VariantsMatrixUtils;
import org.jallinone.variants.java.VariantsMatrixVO;
import org.jallinone.commons.java.ApplicationConsts;
import org.jallinone.events.server.*;
import org.jallinone.events.server.*;
import org.jallinone.items.java.DetailItemVO;
import org.jallinone.items.java.ItemPK;
import org.jallinone.items.java.VariantBarcodeVO;


import javax.sql.DataSource;

/**
* <p>Title: JAllInOne ERP/CRM application</p>
* * <p>Description: Bean used to manage sale prices.</p>
* <p>Copyright: Copyright (C) 2006 Mauro Carniel</p>
*
* <p> This file is part of JAllInOne ERP/CRM application.
* This application is free software; you can redistribute it and/or
* modify it under the terms of the (LGPL) Lesser General Public
* License as published by the Free Software Foundation;
*
*                GNU LESSER GENERAL PUBLIC LICENSE
*                 Version 2.1, February 1999
*
* This application is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Library General Public License for more details.
*
* You should have received a copy of the GNU Library General Public
* License along with this library; if not, write to the Free
* Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
*
*       The author may be contacted at:
*           maurocarniel@tin.it</p>
*
* @author Mauro Carniel
* @version 1.0
*/
public class SalePricesBean  implements SalePrices {


  private DataSource dataSource;

  public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
  }

  /** external connection */
  private Connection conn = null;

  /**
   * Set external connection.
   */
  public void setConn(Connection conn) {
    this.conn = conn;
  }

  /**
   * Create local connection
   */
  public Connection getConn() throws Exception {

    Connection c = dataSource.getConnection(); c.setAutoCommit(false); return c;
  }




  public SalePricesBean() {
  }


  /**
   * Unsupported method, used to force the generation of a complex type in wsdl file for the return type
   */
  public PricelistVO getPricelist() {
    throw new UnsupportedOperationException();
  }

  /**
   * Unsupported method, used to force the generation of a complex type in wsdl file for the return type
   */
  public CustomValueObject getCustomValueObject() {
    throw new UnsupportedOperationException();
  }

  /**
   * Unsupported method, used to force the generation of a complex type in wsdl file for the return type
   */
  public PriceVO getPrice() {
    throw new UnsupportedOperationException();
  }

  /**
   * Unsupported method, used to force the generation of a complex type in wsdl file for the return type
   */
  public DetailItemVO getDetailItem() {
    throw new UnsupportedOperationException();
  }


  /**
   * Business logic to execute.
   */
  public VOListResponse insertPrices(ArrayList list,String serverLanguageId,String username) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt0 = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    PreparedStatement pstmt3 = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL02","COMPANY_CODE_SYS01");
      attribute2dbField.put("pricelistCodeSal01SAL02","PRICELIST_CODE_SAL01");
      attribute2dbField.put("itemCodeItm01SAL02","ITEM_CODE_ITM01");
      attribute2dbField.put("valueSAL02","VALUE");
      attribute2dbField.put("startDateSAL02","START_DATE");
      attribute2dbField.put("endDateSAL02","END_DATE");

      pstmt0 = conn.prepareStatement(
        "select * from SAL02_ITEM_PRICES WHERE "+
        "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND  "+
        "(START_DATE>? AND END_DATE<? or START_DATE<? AND END_DATE>?) "
      );

       pstmt1 = conn.prepareStatement(
       "select * from SAL02_ITEM_PRICES WHERE "+
       "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND  "+
       "(START_DATE>? AND END_DATE<? or START_DATE<? AND END_DATE>? or START_DATE>? ) "
      );

      pstmt2 = conn.prepareStatement(
         "UPDATE SAL02_ITEM_PRICES SET END_DATE=? WHERE "+
         "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND "+
         "(START_DATE<? AND END_DATE is null or START_DATE<? AND END_DATE>? ) "
      );

      pstmt3 = conn.prepareStatement(
        "UPDATE SAL02_ITEM_PRICES SET END_DATE=? WHERE "+
        "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND "+
        "(END_DATE is null or START_DATE<? AND END_DATE>? ) "
      );



     // if current price is valid for [d1,d2]
     // then invervals to redefine are:
     // [...,null] -> [...,d1]
     // [<d1,>d1] -> [...,d1]
     // [>d1,<d2] -> error
     // [<d2,>d2] -> error

     // if current price is valid for [d1,null]
     // then invervals to redefine are:
     // [...,null] -> [...,d1]
     // [<d1,>d1] -> [...,d1]
     // [>d1,<d2] -> error
     // [<d2,>d2] -> error
     // [>d2,>d2] -> error

      // insert into SAL02...
      PriceVO vo = null;
      Response res = null;
      for(int i=0;i<list.size();i++) {
        vo = (PriceVO)list.get(i);

        if (vo.getEndDateSAL02()!=null) {

          pstmt0.setString(1,vo.getCompanyCodeSys01SAL02());
          pstmt0.setString(2,vo.getPricelistCodeSal01SAL02());
          pstmt0.setString(3,vo.getItemCodeItm01());
          pstmt0.setDate(4,vo.getStartDateSAL02());
          pstmt0.setDate(5,vo.getStartDateSAL02());
          pstmt0.setDate(6,vo.getEndDateSAL02());
          pstmt0.setDate(7,vo.getEndDateSAL02());
          pstmt0.setDate(8,vo.getEndDateSAL02());
          ResultSet rset = pstmt0.executeQuery();
          boolean found = rset.next();
          rset.close();
          if (found)
            throw new Exception("change date interval");

          pstmt2.setDate(1,vo.getStartDateSAL02());
          pstmt2.setString(2,vo.getCompanyCodeSys01SAL02());
          pstmt2.setString(3,vo.getPricelistCodeSal01SAL02());
          pstmt2.setString(4,vo.getItemCodeItm01());
          pstmt2.setDate(5,vo.getStartDateSAL02());
          pstmt2.setDate(6,vo.getEndDateSAL02());
          pstmt2.setDate(7,vo.getStartDateSAL02());
          pstmt2.setDate(8,vo.getStartDateSAL02());
          pstmt2.execute();

        }
        else {

          pstmt1.setString(1,vo.getCompanyCodeSys01SAL02());
          pstmt1.setString(2,vo.getPricelistCodeSal01SAL02());
          pstmt1.setString(3,vo.getItemCodeItm01());
          pstmt1.setDate(4,vo.getStartDateSAL02());
          pstmt1.setDate(5,vo.getStartDateSAL02());
          pstmt1.setDate(6,vo.getEndDateSAL02());
          pstmt1.setDate(7,vo.getEndDateSAL02());
          pstmt1.setDate(8,vo.getEndDateSAL02());
          pstmt1.setDate(9,vo.getEndDateSAL02());
          ResultSet rset = pstmt1.executeQuery();
          boolean found = rset.next();
          rset.close();
          if (found)
            throw new Exception("change date interval");

          pstmt3.setDate(1,vo.getStartDateSAL02());
          pstmt3.setString(2,vo.getCompanyCodeSys01SAL02());
          pstmt3.setString(3,vo.getPricelistCodeSal01SAL02());
          pstmt3.setString(4,vo.getItemCodeItm01());
          pstmt3.setDate(5,vo.getStartDateSAL02());
          pstmt3.setDate(6,vo.getStartDateSAL02());
          pstmt3.setDate(7,vo.getStartDateSAL02());
          pstmt3.execute();

        }

        res = org.jallinone.commons.server.QueryUtilExtension.insertTable(
            conn,
            new UserSessionParameters(username),
            vo,
            "SAL02_ITEM_PRICES",
            attribute2dbField,
            "Y",
            "N",
            null,
            true
        );
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }

      }

      return new VOListResponse(list,false,list.size());
    }
    catch (Throwable ex) {
      Logger.error(username, this.getClass().getName(),
                   "executeCommand", "Error while inserting a new price", ex);
      try {
        if (this.conn==null && conn!=null)
          // rollback only local connection
          conn.rollback();
      }
      catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    }
    finally {
        try {
          if (pstmt0!=null)
            pstmt0.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt1!=null)
            pstmt1.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt2!=null)
            pstmt2.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt3!=null)
            pstmt3.close();
        }
        catch (Exception ex1) {
        }
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }




  /**
   * Business logic to execute.
   */
  public VOListResponse loadPrices(GridParams gridParams,String serverLanguageId,String username) throws Throwable {
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;
      String companyCodeSYS01 = null;
      String sql = null;


      if (gridParams.getOtherGridParams().get(ApplicationConsts.PRICELIST)!=null) {
        PricelistVO vo = (PricelistVO)gridParams.getOtherGridParams().get(ApplicationConsts.PRICELIST);
        companyCodeSYS01 = vo.getCompanyCodeSys01SAL01();

        sql =
            "select SAL02_ITEM_PRICES.COMPANY_CODE_SYS01,SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01,SAL02_ITEM_PRICES.ITEM_CODE_ITM01,SAL02_ITEM_PRICES.VALUE,SAL02_ITEM_PRICES.START_DATE,SAL02_ITEM_PRICES.END_DATE,A.DESCRIPTION,ITM01_ITEMS.PROGRESSIVE_HIE02,"+
            "ITM01_ITEMS.USE_VARIANT_1,ITM01_ITEMS.USE_VARIANT_2,ITM01_ITEMS.USE_VARIANT_3,ITM01_ITEMS.USE_VARIANT_4,ITM01_ITEMS.USE_VARIANT_5, "+
            "REG03_CURRENCIES.CURRENCY_SYMBOL,REG03_CURRENCIES.DECIMALS "+
            " from SAL02_ITEM_PRICES,SYS10_COMPANY_TRANSLATIONS A,ITM01_ITEMS,SAL01_PRICELISTS,REG03_CURRENCIES where "+
            "SAL01_PRICELISTS.COMPANY_CODE_SYS01=SAL02_ITEM_PRICES.COMPANY_CODE_SYS01 and "+
            "SAL01_PRICELISTS.PRICELIST_CODE=SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01 and "+
            "SAL02_ITEM_PRICES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "+
            "SAL02_ITEM_PRICES.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE and "+
            "ITM01_ITEMS.COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and "+
            "ITM01_ITEMS.PROGRESSIVE_SYS10=A.PROGRESSIVE and "+
            "A.LANGUAGE_CODE=? and SAL02_ITEM_PRICES.COMPANY_CODE_SYS01=? and ITM01_ITEMS.ENABLED='Y' and "+
            "SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01='"+vo.getPricelistCodeSAL01()+"' AND "+
            "REG03_CURRENCIES.CURRENCY_CODE=SAL01_PRICELISTS.CURRENCY_CODE_REG03";
      }
      else {
        DetailItemVO vo = (DetailItemVO)gridParams.getOtherGridParams().get(ApplicationConsts.ITEM);
        companyCodeSYS01 = vo.getCompanyCodeSys01ITM01();

        sql =
            "select SAL02_ITEM_PRICES.COMPANY_CODE_SYS01,SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01,SAL02_ITEM_PRICES.ITEM_CODE_ITM01,SAL02_ITEM_PRICES.VALUE,SAL02_ITEM_PRICES.START_DATE,SAL02_ITEM_PRICES.END_DATE,B.DESCRIPTION,"+
            "ITM01_ITEMS.USE_VARIANT_1,ITM01_ITEMS.USE_VARIANT_2,ITM01_ITEMS.USE_VARIANT_3,ITM01_ITEMS.USE_VARIANT_4,ITM01_ITEMS.USE_VARIANT_5, "+
            "REG03_CURRENCIES.CURRENCY_SYMBOL,REG03_CURRENCIES.DECIMALS "+
            " from SAL02_ITEM_PRICES,SYS10_COMPANY_TRANSLATIONS B,SAL01_PRICELISTS,ITM01_ITEMS,REG03_CURRENCIES where "+
            "SAL02_ITEM_PRICES.COMPANY_CODE_SYS01=SAL01_PRICELISTS.COMPANY_CODE_SYS01 and "+
            "SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01=SAL01_PRICELISTS.PRICELIST_CODE and "+
            "SAL01_PRICELISTS.COMPANY_CODE_SYS01=B.COMPANY_CODE_SYS01 and "+
            "SAL01_PRICELISTS.PROGRESSIVE_SYS10=B.PROGRESSIVE and "+
            "B.LANGUAGE_CODE=? and SAL02_ITEM_PRICES.COMPANY_CODE_SYS01=? and "+
            "SAL02_ITEM_PRICES.ITEM_CODE_ITM01='"+vo.getItemCodeITM01()+"' and "+
            "SAL02_ITEM_PRICES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "+
            "SAL02_ITEM_PRICES.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE AND "+
            "REG03_CURRENCIES.CURRENCY_CODE=SAL01_PRICELISTS.CURRENCY_CODE_REG03";
      }

      java.sql.Date filterDate = null;
      if (gridParams.getOtherGridParams().get(ApplicationConsts.DATE_FILTER)!=null) {
        filterDate = new java.sql.Date( ((java.util.Date)gridParams.getOtherGridParams().get(ApplicationConsts.DATE_FILTER)).getTime() );
        sql +=
          " and SAL02_ITEM_PRICES.START_DATE<=? and "+
          "    (SAL02_ITEM_PRICES.END_DATE>? or SAL02_ITEM_PRICES.END_DATE is null) ";
      }

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL02","SAL02_ITEM_PRICES.COMPANY_CODE_SYS01");
      attribute2dbField.put("pricelistCodeSal01SAL02","SAL02_ITEM_PRICES.PRICELIST_CODE_SAL01");
      attribute2dbField.put("itemCodeItm01SAL02","SAL02_ITEM_PRICES.ITEM_CODE_ITM01");
      attribute2dbField.put("valueSAL02","SAL02_ITEM_PRICES.VALUE");
      attribute2dbField.put("startDateSAL02","SAL02_ITEM_PRICES.START_DATE");
      attribute2dbField.put("endDateSAL02","SAL02_ITEM_PRICES.END_DATE");
      attribute2dbField.put("itemDescriptionSYS10","A.DESCRIPTION");
      attribute2dbField.put("pricelistDescriptionSYS10","B.DESCRIPTION");
      attribute2dbField.put("progressiveHie02ITM01","ITM01_ITEMS.PROGRESSIVE_HIE02");

      attribute2dbField.put("useVariant1ITM01","ITM01_ITEMS.USE_VARIANT_1");
      attribute2dbField.put("useVariant2ITM01","ITM01_ITEMS.USE_VARIANT_2");
      attribute2dbField.put("useVariant3ITM01","ITM01_ITEMS.USE_VARIANT_3");
      attribute2dbField.put("useVariant4ITM01","ITM01_ITEMS.USE_VARIANT_4");
      attribute2dbField.put("useVariant5ITM01","ITM01_ITEMS.USE_VARIANT_5");

      attribute2dbField.put("currencySymbolREG03","REG03_CURRENCIES.CURRENCY_SYMBOL");
      attribute2dbField.put("decimalsREG03","REG03_CURRENCIES.DECIMALS");


      ArrayList values = new ArrayList();
      values.add(serverLanguageId);
      values.add(companyCodeSYS01);
      if (filterDate!=null) {
        values.add(filterDate);
        values.add(filterDate);
      }

      // read from SAL02 table...
      Response res = QueryUtil.getQuery(
          conn,
          new UserSessionParameters(username),
          sql,
          values,
          attribute2dbField,
          PriceVO.class,
          "Y",
          "N",
          null,
          gridParams,
          50,
          true
      );

      if (!res.isError()) {
        java.util.List rows = ((VOListResponse)res).getRows();
        PriceVO vo = null;
        for(int i=0;i<rows.size();i++) {
          vo = (PriceVO)rows.get(i);
          if (gridParams.getOtherGridParams().get(ApplicationConsts.PRICELIST)!=null) {
            PricelistVO parentVO = (PricelistVO)gridParams.getOtherGridParams().get(ApplicationConsts.PRICELIST);
            vo.setPricelistDescriptionSYS10(parentVO.getDescriptionSYS10());
          }
          else {
            DetailItemVO parentVO = (DetailItemVO)gridParams.getOtherGridParams().get(ApplicationConsts.ITEM);
            vo.setItemDescriptionSYS10(parentVO.getDescriptionSYS10());
            vo.setProgressiveHie02ITM01(parentVO.getProgressiveHie02ITM01());
          }
        }

      }

      Response answer = res;
      if (answer.isError()) throw new Exception(answer.getErrorMessage()); else return (VOListResponse)answer;
    }
    catch (Throwable ex) {
      Logger.error(username,this.getClass().getName(),"executeCommand","Error while fetching prices list",ex);
      throw new Exception(ex.getMessage());
    }
    finally {
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }



  /**
   * Business logic to execute.
   */
  public VOListResponse loadVariantsPrice(VariantBarcodeVO barcodeVO,String priceListCode,String serverLanguageId,String username) throws Throwable {
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      String sql =
            "select SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01,SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01,"+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01,SAL11_VARIANTS_PRICES.VALUE,SAL11_VARIANTS_PRICES.START_DATE,SAL11_VARIANTS_PRICES.END_DATE,"+
            "A.DESCRIPTION,ITM01_ITEMS.PROGRESSIVE_HIE02,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM06,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM11,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM07,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM12,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM08,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM13,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM09,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM14,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM10,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM15 "+
            " from SAL11_VARIANTS_PRICES,SYS10_COMPANY_TRANSLATIONS A,ITM01_ITEMS where "+
            "SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE and "+
            "ITM01_ITEMS.COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and "+
            "ITM01_ITEMS.PROGRESSIVE_SYS10=A.PROGRESSIVE and "+
            "A.LANGUAGE_CODE=? and SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01=? and ITM01_ITEMS.ENABLED='Y' and "+
            "SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01=? and "+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01=? and "+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM06=? and SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM11=? and "+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM07=? and SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM12=? and "+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM08=? and SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM13=? and "+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM09=? and SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM14=? and "+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM10=? and SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM15=? ";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL11","SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01");
      attribute2dbField.put("pricelistCodeSal01SAL11","SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01");
      attribute2dbField.put("itemCodeItm01SAL11","SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01");
      attribute2dbField.put("valueSAL11","SAL11_VARIANTS_PRICES.VALUE");
      attribute2dbField.put("startDateSAL11","SAL11_VARIANTS_PRICES.START_DATE");
      attribute2dbField.put("endDateSAL11","SAL11_VARIANTS_PRICES.END_DATE");
      attribute2dbField.put("itemDescriptionSYS10","A.DESCRIPTION");
      attribute2dbField.put("pricelistDescriptionSYS10","B.DESCRIPTION");
      attribute2dbField.put("progressiveHie02ITM01","ITM01_ITEMS.PROGRESSIVE_HIE02");

      attribute2dbField.put("variantTypeItm06SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM06");
      attribute2dbField.put("variantCodeItm11SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM11");
      attribute2dbField.put("variantTypeItm07SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM07");
      attribute2dbField.put("variantCodeItm12SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM12");
      attribute2dbField.put("variantTypeItm08SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM08");
      attribute2dbField.put("variantCodeItm13SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM13");
      attribute2dbField.put("variantTypeItm09SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM09");
      attribute2dbField.put("variantCodeItm14SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM14");
      attribute2dbField.put("variantTypeItm10SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM10");
      attribute2dbField.put("variantCodeItm15SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM15");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);
      values.add(barcodeVO.getCompanyCodeSys01ITM22());
      values.add(priceListCode);
      values.add(barcodeVO.getItemCodeItm01ITM22());

      values.add(barcodeVO.getVariantTypeItm06ITM22());
      values.add(barcodeVO.getVariantCodeItm11ITM22());
      values.add(barcodeVO.getVariantTypeItm07ITM22());
      values.add(barcodeVO.getVariantCodeItm12ITM22());
      values.add(barcodeVO.getVariantTypeItm08ITM22());
      values.add(barcodeVO.getVariantCodeItm13ITM22());
      values.add(barcodeVO.getVariantTypeItm09ITM22());
      values.add(barcodeVO.getVariantCodeItm14ITM22());
      values.add(barcodeVO.getVariantTypeItm10ITM22());
      values.add(barcodeVO.getVariantCodeItm15ITM22());


      // read ALL from SAL11 table...
      Response res = QueryUtil.getQuery(
          conn,
          new UserSessionParameters(username),
          sql,
          values,
          attribute2dbField,
          VariantsPriceVO.class,
          "Y",
          "N",
          null,
          new GridParams(),
          true
      );

      if (res.isError())
        throw new Exception(res.getErrorMessage());
      return (VOListResponse)res;
    }
    catch (Throwable ex) {
      Logger.error(username,this.getClass().getName(),"executeCommand","Error while fetching a price for the specified barcode",ex);
      throw new Exception(ex.getMessage());
    }
    finally {
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }



  /**
   * Business logic to execute.
   */
  public VOListResponse loadVariantsPrices(GridParams params,String serverLanguageId,String username) throws Throwable {
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      VariantsMatrixVO matrixVO = (VariantsMatrixVO)params.getOtherGridParams().get(ApplicationConsts.VARIANTS_MATRIX_VO);
      ItemPK itemPK = matrixVO.getItemPK();
      String priceListCode = (String)params.getOtherGridParams().get(ApplicationConsts.PRICELIST);

      String sql =
            "select SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01,SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01,"+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01,SAL11_VARIANTS_PRICES.VALUE,SAL11_VARIANTS_PRICES.START_DATE,SAL11_VARIANTS_PRICES.END_DATE,"+
            "A.DESCRIPTION,ITM01_ITEMS.PROGRESSIVE_HIE02,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM06,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM11,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM07,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM12,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM08,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM13,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM09,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM14,"+
            "SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM10,SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM15 "+
            " from SAL11_VARIANTS_PRICES,SYS10_COMPANY_TRANSLATIONS A,ITM01_ITEMS where "+
            "SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE and "+
            "ITM01_ITEMS.COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and "+
            "ITM01_ITEMS.PROGRESSIVE_SYS10=A.PROGRESSIVE and "+
            "A.LANGUAGE_CODE=? and SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01=? and ITM01_ITEMS.ENABLED='Y' and "+
            "SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01=? and "+
            "SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01=?";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL11","SAL11_VARIANTS_PRICES.COMPANY_CODE_SYS01");
      attribute2dbField.put("pricelistCodeSal01SAL11","SAL11_VARIANTS_PRICES.PRICELIST_CODE_SAL01");
      attribute2dbField.put("itemCodeItm01SAL11","SAL11_VARIANTS_PRICES.ITEM_CODE_ITM01");
      attribute2dbField.put("valueSAL11","SAL11_VARIANTS_PRICES.VALUE");
      attribute2dbField.put("startDateSAL11","SAL11_VARIANTS_PRICES.START_DATE");
      attribute2dbField.put("endDateSAL11","SAL11_VARIANTS_PRICES.END_DATE");
      attribute2dbField.put("itemDescriptionSYS10","A.DESCRIPTION");
      attribute2dbField.put("pricelistDescriptionSYS10","B.DESCRIPTION");
      attribute2dbField.put("progressiveHie02ITM01","ITM01_ITEMS.PROGRESSIVE_HIE02");

      attribute2dbField.put("variantTypeItm06SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM06");
      attribute2dbField.put("variantCodeItm11SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM11");
      attribute2dbField.put("variantTypeItm07SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM07");
      attribute2dbField.put("variantCodeItm12SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM12");
      attribute2dbField.put("variantTypeItm08SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM08");
      attribute2dbField.put("variantCodeItm13SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM13");
      attribute2dbField.put("variantTypeItm09SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM09");
      attribute2dbField.put("variantCodeItm14SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM14");
      attribute2dbField.put("variantTypeItm10SAL11","SAL11_VARIANTS_PRICES.VARIANT_TYPE_ITM10");
      attribute2dbField.put("variantCodeItm15SAL11","SAL11_VARIANTS_PRICES.VARIANT_CODE_ITM15");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);
      values.add(itemPK.getCompanyCodeSys01ITM01());
      values.add(priceListCode);
      values.add(itemPK.getItemCodeITM01());


      // read ALL from SAL11 table...
      Response res = QueryUtil.getQuery(
          conn,
          new UserSessionParameters(username),
          sql,
          values,
          attribute2dbField,
          VariantsPriceVO.class,
          "Y",
          "N",
          null,
          new GridParams(),
          true
      );

      if (res.isError())
        throw new Exception(res.getErrorMessage());


      java.util.List rows = ((VOListResponse)res).getRows();


      // convert the records list in matrix format...
      ArrayList matrixRows = new ArrayList();
      VariantsPriceVO vo = null;
      CustomValueObject customVO = null;
      VariantsMatrixRowVO rowVO = null;
      VariantsMatrixColumnVO colVO = null;
      HashMap indexes = new HashMap();
      for(int i=0;i<matrixVO.getRowDescriptors().length;i++) {
        rowVO = (VariantsMatrixRowVO)matrixVO.getRowDescriptors()[i];

        customVO = new CustomValueObject();
        customVO.setAttributeNameS0(rowVO.getRowDescription());
        matrixRows.add(customVO);
        indexes.put(
          VariantsMatrixUtils.getVariantType(matrixVO,rowVO)+" "+VariantsMatrixUtils.getVariantCode(matrixVO,rowVO),
          customVO
        );
      }
      VariantNameVO varVO = (VariantNameVO)matrixVO.getManagedVariants()[0];
      for(int i=0;i<rows.size();i++) {
        vo = (VariantsPriceVO)rows.get(i);

        if (varVO.getTableName().equals("ITM11_VARIANTS_1")) {
          customVO = (CustomValueObject)indexes.get(vo.getVariantTypeItm06SAL11()+" "+vo.getVariantCodeItm11SAL11());
        }
        else if (varVO.getTableName().equals("ITM12_VARIANTS_2")) {
          customVO = (CustomValueObject)indexes.get(vo.getVariantTypeItm07SAL11()+" "+vo.getVariantCodeItm12SAL11());
        }
        else if (varVO.getTableName().equals("ITM13_VARIANTS_3")) {
          customVO = (CustomValueObject)indexes.get(vo.getVariantTypeItm08SAL11()+" "+vo.getVariantCodeItm13SAL11());
        }
        else if (varVO.getTableName().equals("ITM14_VARIANTS_4")) {
          customVO = (CustomValueObject)indexes.get(vo.getVariantTypeItm09SAL11()+" "+vo.getVariantCodeItm14SAL11());
        }
        else if (varVO.getTableName().equals("ITM15_VARIANTS_5")) {
          customVO = (CustomValueObject)indexes.get(vo.getVariantTypeItm10SAL11()+" "+vo.getVariantCodeItm15SAL11());
        }

        if (matrixVO.getColumnDescriptors().length==0) {
          customVO.setAttributeNameN0(vo.getValueSAL11());
        }
        else {

          for(int j=0;j<matrixVO.getColumnDescriptors().length;j++) {
            colVO = (VariantsMatrixColumnVO)matrixVO.getColumnDescriptors()[j];
            if ((varVO.getTableName().equals("ITM11_VARIANTS_1")?true:colVO.getVariantCodeITM11().equals(vo.getVariantCodeItm11SAL11())) &&
                (varVO.getTableName().equals("ITM12_VARIANTS_2")?true:colVO.getVariantCodeITM12().equals(vo.getVariantCodeItm12SAL11())) &&
                (varVO.getTableName().equals("ITM13_VARIANTS_3")?true:colVO.getVariantCodeITM13().equals(vo.getVariantCodeItm13SAL11())) &&
                (varVO.getTableName().equals("ITM14_VARIANTS_4")?true:colVO.getVariantCodeITM14().equals(vo.getVariantCodeItm14SAL11())) &&
                (varVO.getTableName().equals("ITM15_VARIANTS_5")?true:colVO.getVariantCodeITM15().equals(vo.getVariantCodeItm15SAL11())) &&
                (varVO.getTableName().equals("ITM11_VARIANTS_1")?true:colVO.getVariantTypeITM06().equals(vo.getVariantTypeItm06SAL11())) &&
                (varVO.getTableName().equals("ITM12_VARIANTS_2")?true:colVO.getVariantTypeITM07().equals(vo.getVariantTypeItm07SAL11())) &&
                (varVO.getTableName().equals("ITM13_VARIANTS_3")?true:colVO.getVariantTypeITM08().equals(vo.getVariantTypeItm08SAL11())) &&
                (varVO.getTableName().equals("ITM14_VARIANTS_4")?true:colVO.getVariantTypeITM09().equals(vo.getVariantTypeItm09SAL11())) &&
                (varVO.getTableName().equals("ITM15_VARIANTS_5")?true:colVO.getVariantTypeITM10().equals(vo.getVariantTypeItm10SAL11()))) {
              try {
                CustomValueObject.class.getMethod("setAttributeNameN" + j,new Class[] {BigDecimal.class}).invoke(customVO, new Object[] {vo.getValueSAL11()});
              }
              catch (Throwable ex) {
                ex.printStackTrace();
              }
              break;
            }
          }

        } // end else
      } // end for on rows

      return new VOListResponse(matrixRows,false,matrixRows.size());
    }
    catch (Throwable ex) {
      Logger.error(username,this.getClass().getName(),"executeCommand","Error while fetching prices list",ex);
      throw new Exception(ex.getMessage());
    }
    finally {
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }



  /**
   * Business logic to execute.
   */
  public VOListResponse updatePrices(ArrayList oldVOs,ArrayList newVOs,String serverLanguageId,String username) throws Throwable {
    PreparedStatement pstmt0 = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    PreparedStatement pstmt3 = null;
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      PriceVO oldVO = null;
      PriceVO vo = null;
      Response res = null;


      pstmt0 = conn.prepareStatement(
        "select * from SAL02_ITEM_PRICES WHERE "+
        "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND  "+
        "(START_DATE>? AND END_DATE<? or START_DATE<? AND END_DATE>?) "
      );

       pstmt1 = conn.prepareStatement(
       "select * from SAL02_ITEM_PRICES WHERE "+
       "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND  "+
       "(START_DATE>? AND END_DATE<? or START_DATE<? AND END_DATE>? or START_DATE>? ) "
      );

      pstmt2 = conn.prepareStatement(
         "UPDATE SAL02_ITEM_PRICES SET END_DATE=? WHERE "+
         "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND "+
         "(START_DATE<? AND END_DATE is null or START_DATE<? AND END_DATE>? ) "
      );

       pstmt3 = conn.prepareStatement(
        "UPDATE SAL02_ITEM_PRICES SET END_DATE=? WHERE "+
        "COMPANY_CODE_SYS01=? AND PRICELIST_CODE_SAL01=? AND ITEM_CODE_ITM01=? AND NOT START_DATE=? AND "+
        "(END_DATE is null or START_DATE<? AND END_DATE>? ) "
       );


     // if current price is valid for [d1,d2]
     // then invervals to redefine are:
     // [<d2,null] -> [...,d1]
     // [<d1,>d1] -> [...,d1]
     // [>d1,<d2] -> error
     // [<d2,>d2] -> error

     // if current price is valid for [d1,null]
     // then invervals to redefine are:
     // [...,null] -> [...,d1]
     // [<d1,>d1] -> [...,d1]
     // [>d1,<d2] -> error
     // [<d2,>d2] -> error
     // [>d2,>d2] -> error

     HashSet pkAttrs = new HashSet();
     pkAttrs.add("companyCodeSys01SAL02");
     pkAttrs.add("pricelistCodeSal01SAL02");
     pkAttrs.add("itemCodeItm01SAL02");
     pkAttrs.add("startDateSAL02");

     HashMap attribute2dbField = new HashMap();
     attribute2dbField.put("companyCodeSys01SAL02","COMPANY_CODE_SYS01");
     attribute2dbField.put("pricelistCodeSal01SAL02","PRICELIST_CODE_SAL01");
     attribute2dbField.put("itemCodeItm01SAL02","ITEM_CODE_ITM01");
     attribute2dbField.put("valueSAL02","VALUE");
     attribute2dbField.put("startDateSAL02","START_DATE");
     attribute2dbField.put("endDateSAL02","END_DATE");


     for(int i=0;i<oldVOs.size();i++) {
        oldVO = (PriceVO)oldVOs.get(i);
        vo = (PriceVO)newVOs.get(i);

        if (vo.getEndDateSAL02()!=null) {

          pstmt0.setString(1,vo.getCompanyCodeSys01SAL02());
          pstmt0.setString(2,vo.getPricelistCodeSal01SAL02());
          pstmt0.setString(3,vo.getItemCodeItm01());
          pstmt0.setDate(4,vo.getStartDateSAL02());
          pstmt0.setDate(5,vo.getStartDateSAL02());
          pstmt0.setDate(6,vo.getEndDateSAL02());
          pstmt0.setDate(7,vo.getEndDateSAL02());
          pstmt0.setDate(8,vo.getEndDateSAL02());
          ResultSet rset = pstmt0.executeQuery();
          boolean found = rset.next();
          rset.close();
          if (found)
            throw new Exception("change date interval");

          pstmt2.setDate(1,vo.getStartDateSAL02());
          pstmt2.setString(2,vo.getCompanyCodeSys01SAL02());
          pstmt2.setString(3,vo.getPricelistCodeSal01SAL02());
          pstmt2.setString(4,vo.getItemCodeItm01());
          pstmt2.setDate(5,vo.getStartDateSAL02());
          pstmt2.setDate(6,vo.getEndDateSAL02());
          pstmt2.setDate(7,vo.getStartDateSAL02());
          pstmt2.setDate(8,vo.getStartDateSAL02());
          pstmt2.execute();

        }
        else {

          pstmt1.setString(1,vo.getCompanyCodeSys01SAL02());
          pstmt1.setString(2,vo.getPricelistCodeSal01SAL02());
          pstmt1.setString(3,vo.getItemCodeItm01());
          pstmt1.setDate(4,vo.getStartDateSAL02());
          pstmt1.setDate(5,vo.getStartDateSAL02());
          pstmt1.setDate(6,vo.getEndDateSAL02());
          pstmt1.setDate(7,vo.getEndDateSAL02());
          pstmt1.setDate(8,vo.getEndDateSAL02());
          pstmt1.setDate(9,vo.getEndDateSAL02());
          ResultSet rset = pstmt1.executeQuery();
          boolean found = rset.next();
          rset.close();
          if (found)
            throw new Exception("change date interval");

          pstmt3.setDate(1,vo.getStartDateSAL02());
          pstmt3.setString(2,vo.getCompanyCodeSys01SAL02());
          pstmt3.setString(3,vo.getPricelistCodeSal01SAL02());
          pstmt3.setString(4,vo.getItemCodeItm01());
          pstmt3.setDate(5,vo.getStartDateSAL02());
          pstmt3.setDate(6,vo.getStartDateSAL02());
          pstmt3.setDate(7,vo.getStartDateSAL02());
          pstmt3.execute();

        }

        res = org.jallinone.commons.server.QueryUtilExtension.updateTable(
            conn,
            new UserSessionParameters(username),
            pkAttrs,
            oldVO,
            vo,
            "SAL02_ITEM_PRICES",
            attribute2dbField,
            "Y",
            "N",
            null,
            true
        );
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
     }

     return new VOListResponse(newVOs,false,newVOs.size());
    }
    catch (Throwable ex) {
      Logger.error(username,this.getClass().getName(),"executeCommand","Error while updating existing prices",ex);
      try {
        if (this.conn==null && conn!=null)
          // rollback only local connection
          conn.rollback();
      }
      catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    }
    finally {
        try {
          if (pstmt0!=null)
            pstmt0.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt1!=null)
            pstmt1.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt2!=null)
            pstmt2.close();
        }
        catch (Exception ex1) {
        }
        try {
          if (pstmt3!=null)
            pstmt3.close();
        }
        catch (Exception ex1) {
        }
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }



  /**
   * Business logic to execute.
   */
  public VOResponse updateVariantsPrices(VariantsPrice variantsPrice,String serverLanguageId,String username) throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      // remove all prices related to the specified item/pricelist...
      pstmt = conn.prepareStatement("delete from SAL11_VARIANTS_PRICES where COMPANY_CODE_SYS01=? and PRICELIST_CODE_SAL01=? and ITEM_CODE_ITM01=?");
      pstmt.setString(1,variantsPrice.getMatrixVO().getItemPK().getCompanyCodeSys01ITM01());
      pstmt.setString(2,variantsPrice.getPriceListCode());
      pstmt.setString(3,variantsPrice.getMatrixVO().getItemPK().getItemCodeITM01());
      pstmt.execute();


      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL11","COMPANY_CODE_SYS01");
      attribute2dbField.put("pricelistCodeSal01SAL11","PRICELIST_CODE_SAL01");
      attribute2dbField.put("itemCodeItm01SAL11","ITEM_CODE_ITM01");
      attribute2dbField.put("valueSAL11","VALUE");
      attribute2dbField.put("startDateSAL11","START_DATE");
      attribute2dbField.put("endDateSAL11","END_DATE");

      attribute2dbField.put("variantTypeItm06SAL11","VARIANT_TYPE_ITM06");
      attribute2dbField.put("variantCodeItm11SAL11","VARIANT_CODE_ITM11");
      attribute2dbField.put("variantTypeItm07SAL11","VARIANT_TYPE_ITM07");
      attribute2dbField.put("variantCodeItm12SAL11","VARIANT_CODE_ITM12");
      attribute2dbField.put("variantTypeItm08SAL11","VARIANT_TYPE_ITM08");
      attribute2dbField.put("variantCodeItm13SAL11","VARIANT_CODE_ITM13");
      attribute2dbField.put("variantTypeItm09SAL11","VARIANT_TYPE_ITM09");
      attribute2dbField.put("variantCodeItm14SAL11","VARIANT_CODE_ITM14");
      attribute2dbField.put("variantTypeItm10SAL11","VARIANT_TYPE_ITM10");
      attribute2dbField.put("variantCodeItm15SAL11","VARIANT_CODE_ITM15");


      // insert into SAL11...
      VariantsPriceVO vo = null;
      Response res = null;
      Object[] row = null;
      VariantsMatrixColumnVO colVO = null;
      VariantsMatrixRowVO rowVO = null;
      BigDecimal price = null;
      for(int i=0;i<variantsPrice.getCells().length;i++) {
        row = variantsPrice.getCells()[i];
        rowVO = (VariantsMatrixRowVO)variantsPrice.getMatrixVO().getRowDescriptors()[i];

        if (variantsPrice.getMatrixVO().getColumnDescriptors().length==0) {

          if (variantsPrice.getCells()[i][0]!=null) {
            try {
        price = (BigDecimal)variantsPrice.getCells()[i][0];
      } catch (Exception e) {
        continue;
      }
            vo = new VariantsPriceVO();
            vo.setCompanyCodeSys01SAL11(variantsPrice.getMatrixVO().getItemPK().getCompanyCodeSys01ITM01());
            vo.setItemCodeItm01SAL11(variantsPrice.getMatrixVO().getItemPK().getItemCodeITM01());
            vo.setPricelistCodeSal01SAL11(variantsPrice.getPriceListCode());
            vo.setValueSAL11(price);
            vo.setStartDateSAL11(variantsPrice.getStartDate());
            vo.setEndDateSAL11(variantsPrice.getEndDate());
            VariantsMatrixUtils.setVariantTypesAndCodes(vo,"SAL11",variantsPrice.getMatrixVO(),rowVO,null);

            res = org.jallinone.commons.server.QueryUtilExtension.insertTable(
                conn,
                new UserSessionParameters(username),
                vo,
                "SAL11_VARIANTS_PRICES",
                attribute2dbField,
                "Y",
                "N",
                null,
                true
            );
            if (res.isError()) {
              throw new Exception(res.getErrorMessage());
            }
          }

        }
        else
          for(int k=0;k<variantsPrice.getMatrixVO().getColumnDescriptors().length;k++) {

            colVO = (VariantsMatrixColumnVO)variantsPrice.getMatrixVO().getColumnDescriptors()[k];
            if (variantsPrice.getCells()[i][k]!=null) {
              try {
            price = (BigDecimal)variantsPrice.getCells()[i][k];
          } catch (Exception e) {
            continue;
          }
              vo = new VariantsPriceVO();
              vo.setCompanyCodeSys01SAL11(variantsPrice.getMatrixVO().getItemPK().getCompanyCodeSys01ITM01());
              vo.setItemCodeItm01SAL11(variantsPrice.getMatrixVO().getItemPK().getItemCodeITM01());
              vo.setPricelistCodeSal01SAL11(variantsPrice.getPriceListCode());
              vo.setValueSAL11(price);
              vo.setStartDateSAL11(variantsPrice.getStartDate());
              vo.setEndDateSAL11(variantsPrice.getEndDate());
              VariantsMatrixUtils.setVariantTypesAndCodes(vo,"SAL11",variantsPrice.getMatrixVO(),rowVO,colVO);

              res = org.jallinone.commons.server.QueryUtilExtension.insertTable(
                  conn,
                  new UserSessionParameters(username),
                  vo,
                  "SAL11_VARIANTS_PRICES",
                  attribute2dbField,
                  "Y",
                  "N",
                  null,
                  true
              );
              if (res.isError()) {
                throw new Exception(res.getErrorMessage());
              }

            } // end if on cell not null
          } // end inner for
      } // end outer for

      return new VOResponse(Boolean.TRUE);
    }
    catch (Throwable ex) {
      Logger.error(username, this.getClass().getName(),
                   "executeCommand", "Error while inserting price for variants", ex);
      try {
        if (this.conn==null && conn!=null)
          // rollback only local connection
          conn.rollback();
      }
      catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    }
    finally {
      try {
        pstmt.close();
      }
      catch (Exception ex2) {
      }
      try {
          if (this.conn==null && conn!=null) {
              // close only local connection
              conn.commit();
              conn.close();
          }

      }
      catch (Exception exx) {}
    }
  }




  /**
   * Business logic to execute.
   */
  public VOResponse deletePrices(ArrayList list,String serverLanguageId,String username) throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn==null) conn = getConn(); else conn = this.conn;

      pstmt = conn.prepareStatement(
         "delete from SAL02_ITEM_PRICES where COMPANY_CODE_SYS01=? and "+
         "PRICELIST_CODE_SAL01=? and ITEM_CODE_ITM01=? and START_DATE=?");

      PriceVO vo = null;
      for(int i=0;i<list.size();i++) {
        vo = (PriceVO)list.get(i);
        // phisically delete records from SAL02...
        pstmt.setString(1,vo.getCompanyCodeSys01SAL02());
        pstmt.setString(2,vo.getPricelistCodeSal01SAL02());
        pstmt.setString(3,vo.getItemCodeItm01SAL02());
        pstmt.setDate(4,vo.getStartDateSAL02());
        pstmt.execute();
      }

      return new VOResponse(new Boolean(true));
    }
    catch (Throwable ex) {
      Logger.error(username,this.getClass().getName(),"executeCommand","Error while deleting an existing prices",ex);
      try {
        if (this.conn==null && conn!=null)
          // rollback only local connection
          conn.rollback();
      }
      catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    }
    finally {
        try {
            pstmt.close();
        }
        catch (Exception exx) {}
        try {
            if (this.conn==null && conn!=null) {
                // close only local connection
                conn.commit();
                conn.close();
            }

        }
        catch (Exception exx) {}
    }


  }



}

TOP

Related Classes of org.jallinone.sales.pricelist.server.SalePricesBean

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.