Source: Product_lib/process.js

import("system.logging");
import("system.SQLTYPES");
import("system.datetime");
import("system.db");
import("system.vars");
import("Util_lib");

function ProductUtils()
{
    var that = this;
    /**
     * Delivers the currently valid product price 
     * 
     * @param pPid {String} ProductID
     * @param pBuySell {String} possible values: PP, SP
     * 
     * @example productUtils.getCurrentProductPrice(vars.get("$field.PRODUCTID"), "PP")
     * 
     * @result {String} currently valid product price
     */
    this.getCurrentProductPrice = function( pPid, pBuySell )
    {
        if(pPid != undefined && pPid != "" && pBuySell != undefined && pBuySell != "")
        {
            var actualpriceSelect = "select PRICE from PRODUCTPRICE \n\
                                    where BUYSELL = '" + pBuySell + "' and PRODUCT_ID = '" + pPid + "' and CURRENCY = 1 \n\
                                    and VALID_FROM <= ? and (VALID_TO >= ? or VALID_TO is null) \n\
                                    order by VALID_FROM desc";

            var today = datetime.today();
            var sqltypes = [ [today.toString(), SQLTYPES.TIMESTAMP], [today.toString(), SQLTYPES.TIMESTAMP] ];
            var actualprice = db.cell([actualpriceSelect, sqltypes]);

            if(actualprice != "")
                return actualprice;
            else
                return "0.00";
        }
        else
        {
            throw new Error();//TODO: add message
        }
    }
    
    /**
     * Delivers the stock
     * 
     * @param pPid {String} ProductID
     * 
     * @example productUtils.getStockCount(vars.get("$field.PRODUCTID"))
     * 
     * @result {String} stock count
     */
    this.getStockCount = function( pPid )
    {
        if(pPid != undefined && pPid != "")
        {
            var sum = db.cell("select sum(QUANTITY * IN_OUT) from STOCK where PRODUCT_ID = '" + pPid + "'");
            if(sum == "")
                sum = "0";
            
            return sum;
        }
        else
        {
            throw new Error();//TODO: add message
        }
    }
    
    /**
     * Delivers the stock
     * 
     * @param pPid {String} ProductID
     * 
     * @example productUtils.getStockCount(vars.get("$field.PRODUCTID"))
     * 
     * @result {String} stock count
     */
    this.getProductDetails = function( pPid, pPriceListFilter )
    {
        var ProductDetails = {};
        
        var cols = [];
        var colsProduct = ["PRODUCT.PRODUCTID", "PRODUCT.PRODUCTNAME", "PRODUCT.GROUPCODEID", "PRODUCT.UNIT"];
        cols = cols.concat(colsProduct);
        
        var joins = [];
        var conditions = ["1=1"];
        var orderby = ["PRODUCTID"];
        var sqltypes = [];
        
        //PriceList (all)
        var colsPricelistAll = ["allPP.PRODUCTPRICEID", "allPP.RELATION_ID", "allPP.PRICELIST", "allPP.PRICE", "allPP.VAT"
                            , "allPP.VALID_FROM", "allPP.VALID_TO", "allPP.BUYSELL", "allPP.FROMQUANTITY", "allPP.CURRENCY"];
        
        cols = cols.concat(colsPricelistAll);
        joins.push(" left join PRODUCTPRICE allPP on allPP.PRODUCT_ID = PRODUCTID ");
        
        //PriceList (currently valid)
        var validPriceLists = false;
        if(pPriceListFilter != undefined 
            && pPriceListFilter.currency != undefined && pPriceListFilter.currency != "" 
            && pPriceListFilter.quantity != undefined && pPriceListFilter.quantity != "")
        {
            validPriceLists = true;
            var colsPricelistValid = ["validPP.PRODUCTPRICEID", "validPP.RELATION_ID", "validPP.PRICELIST", "validPP.PRICE", "validPP.VAT"];
            cols = cols.concat(colsPricelistValid);
            joins.push(" left join PRODUCTPRICE validPP on validPP.PRODUCT_ID = PRODUCTID "
                        + " and validPP.CURRENCY = " + pPriceListFilter.currency 
                        + " and validPP.VALID_FROM <= ? and (validPP.VALID_TO > ? or validPP.VALID_TO is null) "
                        + " and validPP.FROMQUANTITY <= " + pPriceListFilter.quantity
                        + " and (validPP.RELATION_ID = '" + pPriceListFilter.relationId + "' or (validPP.RELATION_ID is null and validPP.BUYSELL = 'SP')) ");

            sqltypes.push([datetime.date().toString(), SQLTYPES.TIMESTAMP]);
            sqltypes.push([datetime.date().toString(), SQLTYPES.TIMESTAMP]);
            orderby = orderby.concat(["validPP.VALID_FROM desc, validPP.FROMQUANTITY desc"]);           
        }
        
        var ProductDataSql = "select " + cols.join(", ")
                   + " from PRODUCT "
                   + joins.join(" ")
                   + " where PRODUCTID = '" + pPid + "' "
                   + " and " + conditions.join(" and ")
                   + " order by " + orderby.join(", ");
        
        var ProductData = db.table([ProductDataSql, sqltypes]);
        
        for(var i = 0; i < ProductData.length; i++)
        {
            //Product
            if(ProductDetails.productId == undefined)
            {
                ProductDetails = {
                                productId: ProductData[i][0]
                                , productName: ProductData[i][1]
                                , groupCode: ProductData[i][2]
                                , unit: ProductData[i][3]
                                , PriceLists: {}
                                , CurrentValidPriceLists: {}
                                , PriceListToUse: null
                            };
            }
            //Pricelist (all)
            var colIdx = colsProduct.length;
            if(ProductData[i][colIdx] != "" && ProductDetails.PriceLists[ProductData[i][colIdx]] == undefined) //Pricelist found
            {
                ProductDetails.PriceLists[ProductData[i][colIdx]] = {
                    priceListId: ProductData[i][colIdx++]
                    , relationId: ProductData[i][colIdx++]
                    , priceList: ProductData[i][colIdx++]
                    , price: ProductData[i][colIdx++]
                    , vat: ProductData[i][colIdx++]
                    , validFrom: ProductData[i][colIdx++]
                    , validTo: ProductData[i][colIdx++]
                    , buySell: ProductData[i][colIdx++]
                    , fromQuantity: ProductData[i][colIdx++]
                    , currency: ProductData[i][colIdx++]
                }
            }
           
            //Pricelist (currently valid)
            colIdx = colsProduct.length + colsPricelistAll.length;
            if(validPriceLists)
            {
                if(ProductData[i][colIdx] != "" && ProductDetails.CurrentValidPriceLists[ProductData[i][colIdx]] == undefined) //Pricelist found
                {
                    ProductDetails.CurrentValidPriceLists[ProductData[i][colIdx]] = {
                        priceListId: ProductData[i][colIdx++]
                        , relationId: ProductData[i][colIdx++]
                        , priceList: ProductData[i][colIdx++]
                        , price: ProductData[i][colIdx++]
                        , vat: ProductData[i][colIdx++]
                    }
                }
            }
        }
        
        if(validPriceLists)
            ProductDetails.PriceListToUse = _getPriceListToUse(ProductDetails.CurrentValidPriceLists, pPriceListFilter);
            
        return ProductDetails;
        
        
        function _getPriceListToUse(pPriceLists, pPriceListFilter)
        {
            for(var list in pPriceLists)
            {
                //customer specific price (defined in Org -> Conditions)
                if(pPriceListFilter.relationId != "" && pPriceListFilter.relationId == pPriceLists[list].relationId)
                {
                    return pPriceLists[list];
                }
                //customer deposited price list (defined by Attribute)
                if(pPriceListFilter.priceList != "" && pPriceListFilter.priceList == pPriceLists[list].priceList)
                {
                    return pPriceLists[list];
                }
                //default price list
                if(pPriceLists[list].priceList == "1")
                {
                    return pPriceLists[list];
                }
            }
            
            //no valid price list found
            return null;
        }
    }
    
    //pPriceList Object beschreiben
    this.checkForIndenticalPriceLists = function(pPid, pPriceList)
    {
        var PriceLists = that.getProductDetails(pPid).PriceLists;
        
        for(var pricelist in PriceLists)
        {
            
            if( pPriceList.priceList == PriceLists[pricelist].priceList 
                && pPriceList.fromQuantity == PriceLists[pricelist].fromQuantity 
                && pPriceList.buySell == PriceLists[pricelist].buySell
                && pPriceList.currency == PriceLists[pricelist].currency )
            
            {
                
                if( pPriceList.validFrom == PriceLists[pricelist].validFrom && pPriceList.validTo == PriceLists[pricelist].validTo
                    || ! ( pPriceList.validFrom <= PriceLists[pricelist].validFrom && pPriceList.validTo <= PriceLists[pricelist].validTo
                           || pPriceList.validFrom >= PriceLists[pricelist].validFrom && pPriceList.validTo >= PriceLists[pricelist].validTo
                           || pPriceList.validFrom < PriceLists[pricelist].validFrom && pPriceList.validTo > PriceLists[pricelist].validTo ) 
                )
                {
                    //identical price list found
                    return PriceLists[pricelist];
                }
            }
        }
        
        return null;
        
        //equal price list
        
            //identical fromquantity
            //identical currency
            //identical pp/sp
            
                //identical validFrom & validTo
                 // OR NOT [ validFrom_new <= validFrom & validTo_new <= validTo
                 //        OR validFrom_new >= validFrom & validTo_new >= validTo
                 //        OR validFrom_new < validFrom & validTo_new > validTo
                 // ]
                 
    }
    
}

function Prod2prodUtils()
{
    var data = getProd2prodData();
    
    function getProd2prodData()
    {
        var sqlStr = "select PROD2PRODID, DEST_ID, SOURCE_ID, QUANTITY, OPTIONAL, TAKEPRICE "
                            + "from PROD2PROD join PRODUCT on PROD2PROD.SOURCE_ID = PRODUCTID "
                            + "order by PRODUCTCODE ";

        return db.table(sqlStr);
    }
    
    function _buildTree(pPid, pSupervised)
    {
        /* object tree to relate products by DEST_ID / SOURCE_ID.
         * Parts list shows subordinated products.
         **/
        var tree = { root: {ids: [], sourceid: pPid } };
        
        if(pSupervised)
            tree = { root: {ids: [], destid: pPid } };

        for (var i = 0; i < data.length; i++)
        {
            var prod2prodid = data[i][0];
            if ( tree[prod2prodid] == undefined )                                         
            tree[prod2prodid] = {
                ids: [] 
                , prodid: ""
                , rowdata: data[i].slice(0)//copy to get NativeArray for concatenation
                , destid: data[i][1]
                , sourceid: data[i][2] 
                , quantity: data[i][3]
                , optional: data[i][4]
                , takeprice: data[i][5]
                , pos: 0
                , parentid: ""
            };  
        }
        
        return tree;
    }
    
    function _getSubordinated(pPid)
    {
        var tree = _buildTree(pPid, false);

        __relate("root", ["0"]);
        
        return tree;
        
        
        function __relate(pID, pPos)
        {
            for ( var id in tree )
            {
                if ( tree[id].destid == tree[pID].sourceid && tree[pID].ids.indexOf(id) == -1 )
                {   
                    tree[pID].ids.push(id);

                    var rowdata = tree[id].rowdata;
                    pPos[pPos.length-1] = (Number(pPos[pPos.length-1]) + 1).toString();
                                                 //POS, PARENTID
                    rowdata = rowdata.concat([pPos.join("."), pID]);

                    tree[id].rowdata = rowdata;
                    tree[id].prodid = tree[id].sourceid;
                    tree[id].pos = rowdata[6];
                    tree[id].parentid = rowdata[7];

                    __relate(id, pPos.concat([0]));
                }    
            }
        }
    }
    
    this.getSubordinatedObject = function(pPid)
    {
        return _getSubordinated(pPid);
    }
    
    this.getSubordinatedData2DArray = function(pPid)
    {
        var ret = [];
        var so = _getSubordinated(pPid);
        
        __push(so.root);
        
        function __push(pObj)
        {
            for(var i = 0; i < pObj.ids.length; i++)
            {
                ret.push(so[pObj.ids[i]].rowdata);
                __push( so[pObj.ids[i]] );
            }
        }
        
        var dUtils = new DataUtils();
        dUtils.array_mDimSort(ret, 6, true);
        
        return ret;
    }
    
    this.getSubordinatedProdIds = function(pPid)
    {
        var ret = [];
        var so = _getSubordinated(pPid);
        for(var p2pid in so)
            ret.push(so[p2pid].prodid);
        
        return ret;
    }
    
    function _getSupervised(pPid)
    {
        var SuperVised = {};
        
        var tree = _buildTree(pPid, true);

        __relate("root", ["0"]);
        
        return SuperVised;
        
        
        function __relate(pID, pPos)
        {
            for ( var id in tree )
            {
                if ( tree[id].sourceid == tree[pID].destid && tree[pID].ids.indexOf(id) == -1 )
                {   
                   var rowdata = tree[id].data;
                   pPos[pPos.length-1] = (Number(pPos[pPos.length-1]) + 1).toString();
                                                //POS, PARENTID
                   rowdata = rowdata.concat([pPos.join("."), pID]);
                   
                   SuperVised[id] = { prodid: tree[id].destid, data: rowdata };
                   
                   __relate(id, pPos.concat([0]));
                }    
            }
        }
    }
    
    this.getSupervisedData = function(pPid)
    {
        return _getSupervised(pPid);
    }
    
    this.getSupervisedData2DArray = function(pPid)
    {
        var ret = [];
        var so = _getSupervised(pPid);
        for(var p2pid in so)
            ret.push(so[p2pid].data);
        
        var dUtils = new DataUtils();
        dUtils.array_mDimSort(ret, 6, true);
        
        return ret;
    }
    
    this.getSupervisedProdIds = function(pPid)
    {
        var ret = [];
        var so = _getSupervised(pPid);
        for(var p2pid in so)
            ret.push(so[p2pid].prodid);
        
        return ret;
    }
}