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;
}
}