Something went wrong on our end
process.js 21.26 KiB
import("system.logging");
import("system.SQLTYPES");
import("system.datetime");
import("system.db");
import("system.vars");
import("Util_lib");
/**
* Class containing utility functions for products
*
* @class
*
*/
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 metadata and price lists of the passed product.
* If parameter "pPriceListFilter" is passed valid price lists and the
* current price list to use for offer/order are delivered.
*
* @param pPid {String} req ProductID
* @param pPriceListFilter {Object} opt { currency: "currencyValue", quantity: "quantityValue", relationId: "relationIdValue (for custom price lists)" }
*
* @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 relid = vars.exists("$param.RelationId_param") ? vars.get("$param.RelationId_param") : "";
* var pUtils = new ProductUtils();
* var PriceListFilter = { currency: curr, quantity: vars.get("$field.QUANTITY"), relationId: relid };
* var ProductDetails = pUtils.getProductDetails(pid, PriceListFilter);
*
* @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: "relationid" 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: "relationid" 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: "relationid" 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>
* }
*/
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"
, "validPP.VALID_FROM", "validPP.VALID_TO", "validPP.BUYSELL", "validPP.FROMQUANTITY", "validPP.CURRENCY"];
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]] = _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, pPriceListFilter);
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(pPriceLists, pPriceListFilter)
{
for(var list in pPriceLists)
{
//custom 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;
}
}
/**
* Checks if there is already an existing price list identical to the passed price list
*
* @param pPid {String} req ProductID
* @param pPriceList {Object} req { <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
*/
this.checkForIndenticalPriceLists = function(pPid, pPriceList)
{
var PriceLists = that.getProductDetails(pPid).PriceLists;
for(var pricelist in PriceLists)
{
//equal price list
//equal fromquantity
//equal currency
//equal pp/sp
if( pPriceList.priceList == PriceLists[pricelist].priceList
&& pPriceList.fromQuantity == PriceLists[pricelist].fromQuantity
&& pPriceList.buySell == PriceLists[pricelist].buySell
&& pPriceList.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( 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];
}
}
}
//no identical price list found
return null;
}
}
/**
* Class containing utility functions for Prod2Prod (Parts list)
*
* @param pProductId req ProductID
*
* @class
*
*/
function Prod2ProdUtils(pProductId)
{
var data;
/**
* 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: "1" = not optional, "0" = optional (for easier calculation) <br>
* , takeprice: "Y" = price, "N" = no price <br>
* } }
*/
this.getPartsListObject = function()
{
return _relateChilds();
}
/**
* Delivers a 2D-Array for RecordContainer of Entity "Prod2prod_entity"
* containing parts list for passed product "pProductId" (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 {[ [] ]} [ ["UID"
* , "PARENTID" (equals "DEST_ID")
* , "PROD2PRODID"
* , "DEST_ID"
* , "SOURCE_ID"
* , "QUANTITY"
* , "OPTIONAL"
* , "TAKEPRICE"] ]
*/
this.getPartsListForRecordContainer = function()
{
var ret = [];
var childs = _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 "pProductId" (Constructor parameter).
*
*
* @return {[]} [ "SOURCE_ID" ]
*/
this.getPartsListProdIds = function()
{
var ret = [];
var childs = _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 "pProductId" (Constructor parameter).
*
*
* @return {[]} [ "DEST_ID" ]
*/
this.getParentProdIds = function()
{
var ret = [];
var parents = _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.
*
*/
function _initProd2ProdData()
{
if(data == undefined)
{
var sqlStr = "select PROD2PRODID, DEST_ID, SOURCE_ID, QUANTITY, OPTIONAL, TAKEPRICE "
+ "from PROD2PROD join PRODUCT on PROD2PROD.SOURCE_ID = PRODUCTID "
+ "order by PRODUCTCODE ";
data = db.table(sqlStr);
}
}
/* object tree to relate products by DEST_ID / SOURCE_ID.
*
**/
function _buildTree(pSupervised)
{
_initProd2ProdData();
var tree = { root: {ids: [], sourceid: pProductId } };
if(pSupervised)
tree = { root: {ids: [], destid: pProductId } };
for (var i = 0; i < data.length; i++)
{ var prod2prodid = data[i][0];
if ( tree[prod2prodid] == undefined )
{
tree[prod2prodid] = {
ids: []
, 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]
};
}
}
return tree;
}
function _relateChilds()
{
var tree = _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);
}
}
}
}
function _relateParents()
{
var tree = _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);
}
}
}
}
}