process.js 24.98 KiB
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", "VAT"] 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 actualPriceSelect = newSelect("PRICE, CURRENCY, VAT")
.from("PRODUCTPRICE")
.where();
if (onlyStandard != undefined && onlyStandard)
{
actualPriceSelect.and("PRODUCTPRICE.PRICELIST", $KeywordRegistry.productPricelist$standardList());
}
actualPriceSelect.and("PRODUCTPRICE.BUYSELL", buySell)
.and("PRODUCTPRICE.PRODUCT_ID", pid)
.and("PRODUCTPRICE.VALID_FROM", today, SqlBuilder.LESS_OR_EQUAL())
.and(newWhere("PRODUCTPRICE.VALID_TO", today, SqlBuilder.GREATER_OR_EQUAL())
.or("PRODUCTPRICE.VALID_TO is null"));
var productPriceData = actualPriceSelect.orderBy("VALID_FROM desc")
.arrayRow();
if (productPriceData[0] && productPriceData[1])
return [productPriceData[0], KeywordUtils.getViewValue($KeywordRegistry.currency(), productPriceData[1]), productPriceData[2]];
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 = newSelect("sum(QUANTITY * case IN_OUT when 0 then -1 else 1)")
.from("STOCK")
.whereIfSet("STOCK.PRODUCT_ID", pid)
.cell(true, "0");
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} pPriceListFilter opt { currency: "currencyValue", quantity: "quantityValue", relationId: "relationIdValue (for custom price lists)" }
* @param {String[]} pAdditionalProductInfoSubselects additional fields from Product
* They are added to the result with the Fieldname as key. e.g. if the array is [["info", "INFO"]] the result will contain the key "info" which has the value of field INFO
* (Subqueries are also possible)
*
* @example //Product_entity, Field: PRODUCT_ID, Process: onValueChange
* var pid = vars.get("local.value");
* 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", "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, pPriceListFilter, pAdditionalProductInfoSubselects)
{
var priceListFilter = pPriceListFilter;
var additionalProductInfoSubselects = pAdditionalProductInfoSubselects;
if (additionalProductInfoSubselects == undefined)
{
additionalProductInfoSubselects = []
}
var ProductDetails = {};
var cols = [];
var colsProduct = ["PRODUCT.PRODUCTID", "PRODUCT.PRODUCTNAME", "PRODUCT.GROUPCODEID", "PRODUCT.UNIT"];
var defaultProductFieldCount = colsProduct.length;
colsProduct = colsProduct.concat(additionalProductInfoSubselects.map(function(item) {return item[1]}));
cols = cols.concat(colsProduct);
var joins = []; // should contain: arrays of [joinPrefix, table, tableAlias, conditioinAsStringOrSqlBuilder]
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", "PRODUCTPRICE", "allPP", "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", "PRODUCTPRICE", "validPP",
newWhere("validPP.PRODUCT_ID = PRODUCTID")
.and(["PRODUCTPRICE", "CURRENCY", "validPP"], priceListFilter.currency)
.and(["PRODUCTPRICE", "VALID_FROM", "validPP"], datetime.date().toString(), SqlBuilder.LESS_OR_EQUAL())
.and(newWhere()
.or(["PRODUCTPRICE", "VALID_TO", "validPP"], datetime.today().toString(), SqlBuilder.GREATER_OR_EQUAL())
.or("validPP.VALID_TO is null"))
.and(["PRODUCTPRICE", "FROMQUANTITY", "validPP"], priceListFilter.quantity, SqlBuilder.LESS_OR_EQUAL())
.and(newWhere()
.or(["PRODUCTPRICE", "CONTACT_ID", "validPP"], priceListFilter.relationId)
.or(newWhere()
.and("validPP.CONTACT_ID is null")
.and(["PRODUCTPRICE", "BUYSELL", "validPP"], 'SP')))
]);
}
var productDataSql = newSelect(cols) .from("PRODUCT")
joins.forEach(function(pJoin)
{
this.join(pJoin[1], pJoin[3], pJoin[2], pJoin[0]);
}, productDataSql)
var ProductData = productDataSql
.where("PRODUCT.PRODUCTID", pid)
.orderBy(orderBy)
.table();
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;
additionalProductInfoSubselects.forEach(function(productSubselect)
{
this[productSubselect[0]] = 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) {
var fallbackPriceList;
//infividual pricelists always have the highest priority, so we loop trough and use the first one we find regardles of the rest
for (var indivList in priceLists)
{
if(priceLists[indivList]["priceList"] == $KeywordRegistry.productPricelist$specialList())
{
return priceLists[indivList];
}
}
for (var list in priceLists)
{
//customer deposited price list (defined by Attribute)
if (priceListFilter.priceList != "" && priceListFilter.priceList != null)
{
// use if it has the correct pricelist ("-type"). e.g: PRICELISTSERVICE, PRICELISTSPECIAL, PRICELISTKEY, PRICELISTDEFAULT
if (priceLists[list]["priceList"] == priceListFilter.priceList)
{
return priceLists[list];
}
//set fallback pricelist if we don't already have one; for when the org does normally have to use a specific pricelist, but there is none
else if(!fallbackPriceList && priceLists[list].priceList == $KeywordRegistry.productPricelist$standardList())
{
fallbackPriceList = priceLists[list];
}
}
//default price list
else if (priceLists[list].priceList == $KeywordRegistry.productPricelist$standardList())
{
return priceLists[list];
}
}
if(fallbackPriceList)//fallback for when the org does normally have to use a specific pricelist, but there is none: use first standardPricelist
{
return fallbackPriceList;
}
else//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>
* vat: Number, <br>
* fromQuantity: "fromquantity", <br>
* currency: "keyvalue of keyword 'CURRENCY'" <br>
* }
*
* @example //Productprice_entity, Field: PRICELIST, Process: onValidation
* var pUtils = new ProductUtils();
* var priceList = {
* priceList: vars.get("$field.PRICELIST"),
* priceListId: vars.get("$field.PRODUCTPRICEID"),
* fromQuantity: vars.get("$field.FROMQUANTITY"),
* buySell: vars.get("$field.BUYSELL"),
* vat: vars.get("$field.VAT"), * 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
//equal vat
if (priceList.priceListId != PriceLists[pricelist].priceListId
&& priceList.priceList == PriceLists[pricelist].priceList
&& parseFloat(priceList.fromQuantity) == parseFloat(PriceLists[pricelist].fromQuantity)
&& priceList.buySell == PriceLists[pricelist].buySell
&& parseFloat(priceList.vat) == parseFloat(PriceLists[pricelist].vat)
&& 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 = newSelect("PROD2PRODID, DEST_ID, SOURCE_ID, QUANTITY, OPTIONAL, TAKEPRICE, PRODUCTCODE, PRODUCTID")
.from("PROD2PROD")
.join("PRODUCT", "PROD2PROD.SOURCE_ID = PRODUCTID")
.orderBy("PRODUCTCODE")
.table();
}
}
/**
* 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);
}
}
}
}