process.js

import("system.util");
import("system.SQLTYPES");
import("system.datetime");
import("system.db");
import("system.vars");
import("system.translate");
import("KeywordRegistry_basic");
import("Util_lib");
import("Binary_lib");
import("Sql_lib");
import("Keyword_lib");
import("Data_lib");

/**
 * utility functions for products
 * Do not create an instance of this!
 * 
 * @class
 */
function ProductUtils() {}

/**
 * Delivers the currently valid product price 
 * 
 * @param {String} pid ProductID
 * @param {String} buySell possible values: PP, SP
 * @param {String} [onlyStandard=false] if true, only standard price lists are selected.
 * 
 * @example productUtils.getCurrentProductPrice(vars.get("$field.PRODUCTID"), "PP")
 * 
 * @return {Array[]} currently valid product price with currency: [price, "CURRENCY"] or [] if no price found
 */
ProductUtils.getCurrentProductPrice = function(pid, buySell, onlyStandard) {
    if (pid != undefined && pid != "" && buySell != undefined && buySell != "")
    {
        var today = datetime.clearTime(vars.get("sys.date"), "utc");
        var actualPriceCondition = SqlCondition.begin();
        
        if (onlyStandard != undefined && onlyStandard)
        {
            actualPriceCondition.andPrepare("PRODUCTPRICE.PRICELIST", "02553fc7-4611-4914-8ff5-0b7c4e7531c9");
        }
                    
        actualPriceCondition.andPrepare("PRODUCTPRICE.BUYSELL", buySell)
                            .andPrepare("PRODUCTPRICE.PRODUCT_ID", pid)
                            .andPrepare("PRODUCTPRICE.VALID_FROM", today, "# <= ?")
                            .andSqlCondition(SqlCondition.begin()
                                .orPrepare("PRODUCTPRICE.VALID_TO", today, "# >= ?")
                                .or("PRODUCTPRICE.VALID_TO is null"), "1 = 2");
                            
        var productPriceData = db.array(db.ROW, actualPriceCondition.buildSql("select PRICE, CURRENCY from PRODUCTPRICE", "1 = 2", "order by VALID_FROM desc"));

        if (productPriceData[0] && productPriceData[1])
            return  [productPriceData[0], KeywordUtils.getViewValue($KeywordRegistry.currency(), productPriceData[1])];
        else
            return [];
    } else {
        return [];
    }
}

/**
 * Delivers the stock
 * 
 * @param {String} pid ProductID
 * 
 * @example productUtils.getStockCount(vars.get("$field.PRODUCTID"))
 * 
 * @return {String} stock count
 */
ProductUtils.getStockCount = function(pid) {
    if (pid != undefined && pid != "")
    {
        var sum = db.cell(SqlCondition.begin()
                                      .andPrepare("STOCK.PRODUCT_ID", pid)
                                      .buildSql("select sum(QUANTITY * case IN_OUT when 0 then -1 else 1)"
                                                 + " from STOCK"));
        
        if (sum == "")
            sum = "0";

        return sum;
    }
    else
    {
        throw new Error(translate.withArguments("${PRODUCT_LIB_NO_PRODUCT_ID} function: %0", ["ProductUtils.getStockCount"]));
    }
}

/**
 * Delivers metadata and price lists of the passed product. 
 * If parameter "priceListFilter" is passed valid price lists and the 
 * current price list to use for offer/order are delivered.
 * 
 * @param {String} pid req ProductID
 * @param {Object} priceListFilter opt { currency: "currencyValue", quantity: "quantityValue", relationId: "relationIdValue (for custom price lists)" }
 * @param {String[]} additionalProductInfoFields additional fields from Product
 *                   They are added to the result with the Fieldname as key. e.g. if the array is ["INFO"] the result will contain the key "INFO"
 * 
 * @example //Product_entity, Field: PRODUCT_ID, Process: onValueChange
 *          var pid = ProcessHandlingUtils.getOnValidationValue(vars.get("$field.PRODUCT_ID"));
 *          var curr = vars.exists("$param.Currency_param") ? vars.get("$param.Currency_param") : "";
 *          var contactid = vars.exists("$param.ContactId_param") ? vars.get("$param.ContactId_param") : "";
 *          var pUtils = new ProductUtils();
 *          var PriceListFilter = { currency: curr, quantity: vars.get("$field.QUANTITY"), contactId: contactid };
 *          var ProductDetails = pUtils.getProductDetails(pid, PriceListFilter, ["INFO"]);
 * 
 * @return {Object} { <br>
 *                   productId: "productid" <br>
 *                   , productName: "product name" <br>
 *                   , groupCode: "keyvalue of keyword 'GROUPCODE'" <br>
 *                   , unit: "keyvalue of keyword 'UNIT'" <br>
 *                   , PriceLists: {$pricelistid$ { <br>
 *                          priceListId: "pricelistid" <br>
 *                          , relationId: "contactid" when filled -> custom price list <br>
 *                          , priceList: "keyvalue of keyword 'PRICELIST'" <br>
 *                          , price: "price" <br>
 *                          , vat: "vat" <br>
 *                          , validFrom: TIMESTAMP <br>
 *                          , validTo: TIMESTAMP <br>
 *                          , buySell: "SP" / "PP" <br>
 *                          , fromQuantity: "fromquantity" <br>
 *                          , currency: "keyvalue of keyword 'CURRENCY'" <br>
 *                      } } <br>
 *                   , CurrentValidPriceLists: {$pricelistid$ { <br>
 *                          priceListId: "pricelistid" <br>
 *                          , relationId: "contactid" when filled -> custom price list <br>
 *                          , priceList: "keyvalue of keyword 'PRICELIST'" <br>
 *                          , price: "price" <br>
 *                          , vat: "vat" <br>
 *                          , validFrom: TIMESTAMP <br>
 *                          , validTo: TIMESTAMP <br>
 *                          , buySell: "SP" / "PP" <br>
 *                          , fromQuantity: "fromquantity" <br>
 *                          , currency: "keyvalue of keyword 'CURRENCY'" <br>
 *                      } } <br>
 *                   , PriceListToUse: {$pricelistid$ { <br>
 *                          priceListId: "pricelistid" <br>
 *                          , relationId: "contactid" when filled -> custom price list <br>
 *                          , priceList: "keyvalue of keyword 'PRICELIST'" <br>
 *                          , price: "price" <br>
 *                          , vat: "vat" <br>
 *                          , validFrom: TIMESTAMP <br>
 *                          , validTo: TIMESTAMP <br>
 *                          , buySell: "SP" / "PP" <br>
 *                          , fromQuantity: "fromquantity" <br>
 *                          , currency: "keyvalue of keyword 'CURRENCY'" <br>
 *                      } }, <br>
 *                   INFO: "the productinfo"
 *               }
 */
ProductUtils.getProductDetails = function(pid, priceListFilter, additionalProductInfoFields)
{
    if (additionalProductInfoFields == undefined) {additionalProductInfoFields = []}
    var ProductDetails = {};

    var cols = [];
    var colsProduct = ["PRODUCT.PRODUCTID", "PRODUCT.PRODUCTNAME", "PRODUCT.GROUPCODEID", "PRODUCT.UNIT"];
    var defaultProductFieldCount = colsProduct.length;
    colsProduct = colsProduct.concat(additionalProductInfoFields.map(function(item) {return "PRODUCT." + item}));
    
    cols = cols.concat(colsProduct);

    var joins = [];
    var orderby = ["PRODUCTID"];

    //PriceList (all)
    var colsPricelistAll = ["allPP.PRODUCTPRICEID", "allPP.CONTACT_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 (priceListFilter != undefined 
        && priceListFilter.currency != undefined && priceListFilter.currency != "" 
        && priceListFilter.quantity != undefined && priceListFilter.quantity != "")
    {
        validPriceLists = true;
        var colsPricelistValid = ["validPP.PRODUCTPRICEID", "validPP.CONTACT_ID", "validPP.PRICELIST", "validPP.PRICE", "validPP.VAT"
                        , "validPP.VALID_FROM", "validPP.VALID_TO", "validPP.BUYSELL", "validPP.FROMQUANTITY", "validPP.CURRENCY"];
        orderby = orderby.concat(["validPP.VALID_FROM desc", "validPP.FROMQUANTITY desc"]);

        cols = cols.concat(colsPricelistValid);
        joins.push("left join PRODUCTPRICE validPP on " 
                    + db.translateCondition(SqlCondition.begin()
                               .and("validPP.PRODUCT_ID = PRODUCTID")
                               .andPrepare(["PRODUCTPRICE", "CURRENCY", "validPP"], priceListFilter.currency)
                                .andPrepare(["PRODUCTPRICE", "VALID_FROM", "validPP"], datetime.date().toString(), "# <= ?")
                                .andSqlCondition(SqlCondition.begin()
                                    .andPrepare(["PRODUCTPRICE", "VALID_TO", "validPP"], datetime.date().toString(), "# >= ?")
                                    .or("validPP.VALID_TO is null")
                                )
                               .andPrepare(["PRODUCTPRICE", "FROMQUANTITY", "validPP"], priceListFilter.quantity, "# <= ?")
                               .andSqlCondition(SqlCondition.begin()
                                    .orPrepare(["PRODUCTPRICE", "CONTACT_ID", "validPP"], priceListFilter.relationId)
                                    .orSqlCondition(SqlCondition.begin()
                                        .and("validPP.CONTACT_ID is null")
                                        .andPrepare(["PRODUCTPRICE", "BUYSELL", "validPP"], 'SP'), 
                                    "1 = 2"), 
                                "1 = 2")
                                .build("1 = 2")))
    }
    
    var ProductDataSql = SqlCondition.begin()
                            .andPrepare("PRODUCT.PRODUCTID", pid)
                            .buildSql("select " + cols.join(", ") + " from PRODUCT " + joins.join(" "),
                                         "1 = 2",
                                         "order by " + orderby.join(", "))
                                         
    var ProductData = db.table(ProductDataSql);

    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
                        };
                        
            // add additional fields to the details
            var countPos = defaultProductFieldCount;
            additionalProductInfoFields.forEach(function(productField)
            {
                this[productField] = ProductData[i][countPos];
                countPos++;
            }, ProductDetails);
        }
        //Pricelist (all)
        var colIdx = colsProduct.length;
        if (ProductData[i][colIdx] != "" && ProductDetails.PriceLists[ProductData[i][colIdx]] == undefined) //Pricelist found
        {
            ProductDetails.PriceLists[ProductData[i][colIdx]] = _getPriceListObject();
        }

        //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]] = _getPriceListObject();
            }
        }
    }

    if (validPriceLists)
        ProductDetails.PriceListToUse = _getPriceListToUse(ProductDetails.CurrentValidPriceLists, priceListFilter);
    return ProductDetails;

    function _getPriceListObject() {
        return {
                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++]
        };
    }

    //price list to use for offer/order
    function _getPriceListToUse(priceLists, priceListFilter) {
        for (var list in priceLists) {
            //custom price (defined in Org -> Conditions)
            if (priceListFilter.relationId != "" && priceListFilter.relationId == priceLists[list].relationId) {
                return priceLists[list];
            }
            //customer deposited price list (defined by Attribute)
            if (priceListFilter.priceList != "" && priceListFilter.priceList == priceLists[list].priceList) {
                return priceLists[list];
            }
            //default price list
            if (priceLists[list].priceList == $KeywordRegistry.productPricelist$standardList()) {
                return priceLists[list];
            }
        }

        //no valid price list found
        return null;
    }
}

/**
 * Checks if there is already an existing price list identical to the passed price list 
 * 
 * @param {String} pid ProductID
 * @param {Object} priceList { <br>
 *                                  priceList: "keyvalue of keyword 'PRICELIST'" <br>
 *                                  , validFrom: TIMESTAMP <br>
 *                                  , validTo: TIMESTAMP <br>
 *                                  , buySell: "SP" / "PP" <br>
 *                                  , fromQuantity: "fromquantity" <br>
 *                                  , currency: "keyvalue of keyword 'CURRENCY'" <br>
 *                             }
 * 
 * @example //Productprice_entity, Field: PRICELIST, Process: onValidation
 *          var pUtils = new ProductUtils();
 *          var priceList = {
 *                          priceList: ProcessHandlingUtils.getOnValidationValue(vars.get("$field.PRICELIST"))
 *                          , fromQuantity: vars.get("$field.FROMQUANTITY")
 *                          , buySell: vars.get("$field.BUYSELL")
 *                          , currency: vars.get("$field.CURRENCY")
 *                          , validFrom: vars.get("$field.VALID_FROM")
 *                          , validTo: vars.get("$field.VALID_TO")
 *                      };
 *
 *          var identicalPriceList = pUtils.checkForIndenticalPriceLists(vars.get("$field.PRODUCT_ID"), priceList);
 *          if (identicalPriceList != null)
 *          {
 *              result.string(translate.text("Identical price list found!"));
 *          }
 * 
 * @return {Object | null} null if no identical price list was found, otherwise the found price list
 */
ProductUtils.checkForIndenticalPriceLists = function(pid, priceList) {
    var PriceLists = this.getProductDetails(pid).PriceLists;

    for (var pricelist in PriceLists) {
        //different pricelist id
        //equal price list
        //equal fromquantity
        //equal currency
        //equal pp/sp
        if (priceList.priceListId != PriceLists[pricelist].priceListId
            && priceList.priceList == PriceLists[pricelist].priceList 
            && parseFloat(priceList.fromQuantity) == parseFloat(PriceLists[pricelist].fromQuantity) 
            && priceList.buySell == PriceLists[pricelist].buySell
            && priceList.currency == PriceLists[pricelist].currency) {
            
            //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
            // ]
            if (priceList.validFrom == PriceLists[pricelist].validFrom && priceList.validTo == PriceLists[pricelist].validTo
                || ! (priceList.validFrom <= PriceLists[pricelist].validFrom && priceList.validTo <= PriceLists[pricelist].validTo
                       || priceList.validFrom >= PriceLists[pricelist].validFrom && priceList.validTo >= PriceLists[pricelist].validTo
                       || priceList.validFrom < PriceLists[pricelist].validFrom && priceList.validTo > PriceLists[pricelist].validTo)) {
                //identical price list found
                return PriceLists[pricelist];
            }
        }
    }

    //no identical price list found
    return null;        
}

/**
 * returns the image for a product
 * 
 * @param {String} pProductId the id of the product.
 * @param {String} pDefaultText the text, to use for default image generation.
 * @return {String} base64 coded String of the image. If none existed, the given String is used to create an image.
 */
ProductUtils.getImage = function(pProductId, pDefaultText)
{
    return ImageUtils.get("PRODUCT", "IMAGE", pProductId, pDefaultText);
}

/**
 * sets the image of a product
 * 
 * @param {String} pProductId the id of the product.
 * @param {String} pImageDateBase64 base64 coded String of the image.
 * @return {Boolean} if image could be set
 */
ProductUtils.setImage = function(pProductId, pImageDateBase64)
{
    return ImageUtils.set("PRODUCT", "IMAGE", pProductId, pImageDateBase64, "ProductImage", "Image of the product");
}

/**
 * deletes the image of a product
 * 
 * @param {String} pProductId the id of the product.
 * @return {Boolean} if image could be removed
 */
ProductUtils.removeImage = function(pProductId)
{
    return ImageUtils.remove("PRODUCT", "IMAGE", pProductId);
}

/**
 * Class containing utility functions for Prod2Prod (Parts list)
 * 
 * @param {String} productId req ProductID
 * 
 * @class
 *
 */
function Prod2ProdUtils(productId) 
{    
    this.productId = productId;
    this.data = undefined;
}

/**
 * Delivers an Object containing parts list structure for passed product "pProductId" (Constructor parameter)
 *  
 * @return {Object} { $prod2prodid$ { <br>
 *                       ids: [ Array containing child Prod2ProdIds for passed product "pProductId" (Constructor parameter) ] <br>
 *                       , rowdata: [ "PROD2PRODID", "DEST_ID", "SOURCE_ID", "QUANTITY", "OPTIONAL", "TAKEPRICE" ] from DB-Table PROD2PROD <br>
 *                       , destid: "Parent ProductID" <br>
 *                       , sourceid: "Child ProductID" <br>
 *                       , quantity: "Quantity" <br>
 *                       , optional: "0" = not optional, "1" = optional <br>
 *                       , takeprice: "0" = no price, "1" = price <br>
 *                       , productcode: "Productcode" <br>
 *                       , productid: "Productid" <br>
 *                  } }
 */
Prod2ProdUtils.prototype.getPartsListObject = function() 
{
    return this._relateChilds();
}

/**
 * Delivers a 2D-Array for RecordContainer of Entity "Prod2prod_entity" 
 * containing parts list for passed product "productId" (Constructor parameter).
 * 
 * It is necessary to generate a specifically UID for the RecordContainer because 
 * the same data record can be listed several times. Therefore the primary key "PROD2PRODID"
 * can not be used for UID because this must be unique.
 * 
 * @return {String[][]} [ ["UID"
 *                    , "PARENTID" (equals "DEST_ID")
 *                    , "PROD2PRODID"
 *                    , "DEST_ID"
 *                    , "SOURCE_ID"
 *                    , "QUANTITY"
 *                    , "OPTIONAL"
 *                    , "TAKEPRICE"
 *                    , "PRODUCTCODE"
 *                    , "PRODUCTID"] ]
 */
Prod2ProdUtils.prototype.getPartsListForRecordContainer = function() 
{
    var ret = [];
    var childs = this._relateChilds();

    __push(childs.root);

    function __push(pObj)
    {
        for(var i = 0; i < pObj.ids.length; i++)
        {
            var rowdata = childs[pObj.ids[i]].rowdata;
            var UID = util.getNewUUID();
            var PARENTID = childs[pObj.ids[i]].destid;

            rowdata = [UID, PARENTID].concat(rowdata);
            ret.push(rowdata);
            __push( childs[pObj.ids[i]] );
        }
    }
    return ret;
}

/**
* Delivers an Array containing productids of the parts list 
* for passed product "productId" (Constructor parameter).
* 
* 
* @return {String[]} [ "SOURCE_ID" ]
*/
Prod2ProdUtils.prototype.getPartsListProdIds = function() 
{
    var ret = [];
    var childs = this._relateChilds();

    __push(childs.root);

    return ret;

    function __push(pObj)
    {
        for(var i = 0; i < pObj.ids.length; i++)
        {
            ret.push(childs[pObj.ids[i]].sourceid);
            __push( childs[pObj.ids[i]] );
        }
    }
}

/**
* Delivers an Array containing productids of the parent list
* for passed product "productId" (Constructor parameter).
* 
* 
* @return {String[]} [ "DEST_ID" ]
*/
Prod2ProdUtils.prototype.getParentProdIds = function() 
{
    var ret = [];
    var parents = this._relateParents();

    __push(parents.root);

    return ret;

    function __push(pObj)
    {
        for(var i = 0; i < pObj.ids.length; i++)
        {
            ret.push(parents[pObj.ids[i]].destid);
            __push( parents[pObj.ids[i]] );
        }
    }
}

/** 
* Function to initalize class variable "data" containing complete Prod2Prod-Data.<br>
* It guarantees a unique load of data per instance.
*
* @ignore
*/
Prod2ProdUtils.prototype._initProd2ProdData = function()
{
    if (this.data == undefined) {
        this.data = db.table("select PROD2PRODID, DEST_ID, SOURCE_ID, QUANTITY, OPTIONAL, TAKEPRICE, PRODUCTCODE, PRODUCTID "
                    + "from PROD2PROD join PRODUCT on PROD2PROD.SOURCE_ID = PRODUCTID "
                    + "order by PRODUCTCODE ");
    }
}

/**
 * object tree to relate products by DEST_ID / SOURCE_ID.
 **/
Prod2ProdUtils.prototype._buildTree = function(pSupervised)
{
    this._initProd2ProdData();

        var tree = { root: {ids: [], sourceid: this.productId } };
        
        if(pSupervised)
            tree = { root: {ids: [], destid: this.productId } };

        for (var i = 0; i < this.data.length; i++)
        {
            var prod2prodid = this.data[i][0];
            if ( tree[prod2prodid] == undefined )   
            {
                tree[prod2prodid] = {
                    ids: [] 
                    , rowdata: this.data[i].slice(0)//copy to get NativeArray for concatenation
                    , destid: this.data[i][1]
                    , sourceid: this.data[i][2] 
                    , quantity: this.data[i][3]
                    , optional: this.data[i][4]
                    , takeprice: this.data[i][5]
                    , productcode: this.data[i][6]
                    , productid: this.data[i][7]
                };
            }
        }
        
        return tree;

}

Prod2ProdUtils.prototype._relateChilds = function()
{
    var tree = this._buildTree(false);

    __relate("root");

    return tree;

    function __relate(pID)
    {
        for ( var id in tree )
        {
            if ( tree[id].destid == tree[pID].sourceid && tree[pID].ids.indexOf(id) == -1 )
            {   
                tree[pID].ids.push(id);
                __relate(id);
            }    
        }
    }
}

Prod2ProdUtils.prototype._relateParents = function() 
{
    var tree = this._buildTree(true);

    __relate("root");

    return tree;


    function __relate(pID)
    {
        for ( var id in tree )
        {
            if ( tree[id].sourceid == tree[pID].destid && tree[pID].ids.indexOf(id) == -1 )
            {   
                tree[pID].ids.push(id);
                __relate(id);
            }    
        }
    }
}