/// <summary>
/// Gets the item unit collection for the given price group collection.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="items">The item unit collection.</param>
/// <returns>Returns the read only collection of item units.</returns>
public static PagedResult <ItemUnit> GetItemsUnit(SqliteDatabaseContext context, IEnumerable <ItemUnit> items)
{
// IEnumerable<long> priceGroupIds = priceGroupCollection.Select(price => price.PriceGroupId);
using (TempTable priceGroupTempTable = PricingTempTable.CreateItemIdentifier(context, items))
{
const string ItemUnitQueryText = @"SELECT
IFNULL(i.ITEMID, idc.ITEMID) AS 'ITEMID',
idc.INVENTDIMID AS 'VARIANTINVENTDIMID',
IFNULL(i.PRODUCT,0) AS PRODUCT,
IFNULL(idc.DISTINCTPRODUCTVARIANT,0) AS DISTINCTPRODUCTVARIANT
FROM {0} AS it
LEFT JOIN [ax].INVENTTABLE i ON i.PRODUCT = it.PRODUCT AND i.DATAAREAID = @nvc_DataAreaId
LEFT JOIN [ax].INVENTDIMCOMBINATION idc ON idc.DISTINCTPRODUCTVARIANT = it.DISTINCTPRODUCTVARIANT AND idc.DATAAREAID = @nvc_DataAreaId";
var priceGroupQuery = new SqlQuery(ItemUnitQueryText, priceGroupTempTable.TableName);
priceGroupQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ReadEntity <ItemUnit>(priceGroupQuery));
}
}
/// <summary>
/// Gets the retail price adjustments.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemUnits">The item units.</param>
/// <param name="priceGroupCollection">The price group collection.</param>
/// <param name="fromDate">From date time of the retail price adjustments.</param>
/// <param name="toDate">To date time of the retail price adjustments.</param>
/// <returns>Returns the read only collection of price adjustment.</returns>
public static PagedResult <PriceAdjustment> GetRetailPriceAdjustments(SqliteDatabaseContext context, IEnumerable <ItemUnit> itemUnits, ReadOnlyCollection <PriceGroup> priceGroupCollection, DateTime fromDate, DateTime toDate)
{
IEnumerable <long> priceGroupIds = priceGroupCollection.Select(price => price.PriceGroupId);
const string PriceAdjustmentQueryText = @"SELECT DISTINCT
promo.OFFERID AS OFFERID,
promo.VALIDFROM AS VALIDFROM,
promo.VALIDTO AS VALIDTO,
promo.CONCURRENCYMODE AS CONCURRENCYMODE,
promo.PRICINGPRIORITYNUMBER AS PRICINGPRIORITYNUMBER,
promo.DATEVALIDATIONTYPE AS DATEVALIDATIONTYPE,
promo.VALIDATIONPERIODID AS VALIDATIONPERIODID,
promo.CURRENCYCODE,
promoOffer.DISCOUNTMETHOD AS DISCOUNTMETHOD,
promoOffer.OFFERPRICE AS OFFERPRICE,
promoOffer.DISCPCT AS DISCPCT,
promoOffer.DISCAMOUNT AS DISCAMOUNT,
promoOffer.RECID AS RECID,
it.ITEMID AS ITEMID,
it.VARIANTINVENTDIMID AS INVENTDIMID,
it.DISTINCTPRODUCTVARIANT AS DISTINCTPRODUCTVARIANT,
uom.SYMBOL AS SYMBOL,
rgl.VARIANT AS VARIANT,
rgl.PRODUCT AS PRODUCT
FROM [ax].RETAILPERIODICDISCOUNT promo
INNER JOIN [ax].RETAILCHANNELTABLE AS c
ON c.INVENTLOCATIONDATAAREAID = promo.DATAAREAID AND c.RECID = @bi_ChannelId
INNER JOIN [ax].RETAILDISCOUNTPRICEGROUP rdpg on rdpg.OFFERID = promo.OFFERID AND rdpg.DATAAREAID = promo.DATAAREAID
CROSS JOIN {0} pg ON rdpg.PRICEDISCGROUP = pg.RECID
INNER JOIN [ax].RETAILPERIODICDISCOUNTLINE promoLine ON promo.OFFERID = promoLine.OFFERID AND promo.DATAAREAID = promoLine.DATAAREAID
INNER JOIN [ax].RETAILDISCOUNTLINEOFFER promoOffer ON promoLine.RECID = promoOffer.RECID
INNER JOIN [ax].RETAILGROUPMEMBERLINE rgl ON promoLine.RETAILGROUPMEMBERLINE = rgl.RECID
LEFT JOIN [crt].RETAILPRODUCTORVARIANTCATEGORYANCESTORSVIEW rpca ON rgl.CATEGORY = rpca.CATEGORY
LEFT JOIN [ax].UNITOFMEASURE uom ON uom.RECID = promoLine.UNITOFMEASURE
INNER JOIN {1} it ON
(
(rgl.VARIANT != 0 AND rgl.VARIANT = it.DISTINCTPRODUCTVARIANT) OR
(rgl.VARIANT = 0 AND rgl.PRODUCT != 0 AND rgl.PRODUCT = it.PRODUCT) OR
(rgl.VARIANT = 0 AND rgl.PRODUCT = 0 AND
(rpca.PRODUCT = it.PRODUCT OR rpca.PRODUCT = it.DISTINCTPRODUCTVARIANT))
)
WHERE promo.STATUS = 1
AND promo.PERIODICDISCOUNTTYPE = 3 -- get only price adjustments
AND (promo.VALIDFROM <= @fromDate OR promo.VALIDFROM <= @NoDate)
AND (promo.VALIDTO >= @toDate OR promo.VALIDTO >= @Never)
AND promo.DATAAREAID = @nvc_DataAreaId" ;
using (TempTable priceGroupTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", priceGroupIds))
using (TempTable itemIdTempTable = PricingTempTable.CreateItemIdentifier(context, itemUnits))
{
SqlQuery priceAdjustmentQuery = new SqlQuery(PriceAdjustmentQueryText, priceGroupTempTable.TableName, itemIdTempTable.TableName);
priceAdjustmentQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
priceAdjustmentQuery.Parameters["@NoDate"] = new DateTime(1900, 01, 01);
priceAdjustmentQuery.Parameters["@Never"] = new DateTime(2154, 12, 31);
priceAdjustmentQuery.Parameters["@fromDate"] = fromDate.Date;
priceAdjustmentQuery.Parameters["@toDate"] = toDate.Date;
priceAdjustmentQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
var priceAdjustments = context.ReadEntity <PriceAdjustment>(priceAdjustmentQuery);
return(priceAdjustments);
}
}
/// <summary>
/// Gets the periodic retail discount collection.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemUnits">The item unit collection.</param>
/// <param name="priceGroupRecIds">The price group record identifiers.</param>
/// <param name="fromDate">From date of the retail discount.</param>
/// <param name="toDate">To date of the retail discount.</param>
/// <param name="currencyCode">The currency code.</param>
/// <returns>Returns the periodic discount read only collection.</returns>
public static ReadOnlyCollection <PeriodicDiscount> GetRetailDiscount(
SqliteDatabaseContext context,
IEnumerable <ItemUnit> itemUnits,
IEnumerable <long> priceGroupRecIds,
DateTime fromDate,
DateTime toDate,
string currencyCode)
{
const string GetRetailDiscountQueryText = @"SELECT DISTINCT
pd.OFFERID AS OFFERID,
pd.NAME AS NAME,
pd.PERIODICDISCOUNTTYPE AS PERIODICDISCOUNTTYPE,
pd.CONCURRENCYMODE AS CONCURRENCYMODE,
pd.PRICINGPRIORITYNUMBER AS PRICINGPRIORITYNUMBER,
pd.ISDISCOUNTCODEREQUIRED AS ISDISCOUNTCODEREQUIRED,
pd.VALIDATIONPERIODID AS VALIDATIONPERIODID,
pd.DATEVALIDATIONTYPE AS DATEVALIDATIONTYPE,
pd.VALIDFROM AS VALIDFROM,
pd.VALIDTO AS VALIDTO,
pd.DISCOUNTTYPE AS DISCOUNTTYPE,
pd.DEALPRICEVALUE AS DEALPRICEVALUE,
pd.DISCOUNTPERCENTVALUE AS DISCOUNTPERCENTVALUE,
pd.DISCOUNTAMOUNTVALUE AS DISCOUNTAMOUNTVALUE,
pd.NOOFLEASTEXPENSIVELINES AS NOOFLEASTEXPENSIVELINES,
pd.NUMBEROFTIMESAPPLICABLE AS NUMBEROFTIMESAPPLICABLE,
pd.LINENUM AS LINENUM,
pd.DISCOUNTPERCENTORVALUE AS DISCOUNTPERCENTORVALUE,
IFNULL(mmol.LINEGROUP,'') AS LINEGROUP,
IFNULL(mmol.DISCOUNTTYPE,0) AS MIXANDMATCHLINEDISCOUNTTYPE,
IFNULL(mmol.NUMBEROFITEMSNEEDED,0) AS NUMBEROFITEMSNEEDED,
IFNULL(dol.DISCOUNTMETHOD,0) AS DISCOUNTMETHOD,
IFNULL(dol.DISCAMOUNT,0) AS DISCAMOUNT,
IFNULL(dol.DISCPCT, 0) AS DISCPCT,
IFNULL(dol.OFFERPRICE, 0) AS OFFERPRICE,
IFNULL(uom.SYMBOL,'') AS SYMBOL,
IFNULL(pd.COUNTNONDISCOUNTITEMS, 0) AS COUNTNONDISCOUNTITEMS,
it.ITEMID AS ITEMID,
it.VARIANTINVENTDIMID AS INVENTDIMID,
it.DISTINCTPRODUCTVARIANT AS DISTINCTPRODUCTVARIANT,
rgl.VARIANT AS VARIANT,
rgl.PRODUCT AS PRODUCTID
FROM [crt].RETAILPERIODICDISCOUNTSFLATTENEDVIEW pd
INNER JOIN [ax].RETAILCHANNELTABLE AS c
ON c.INVENTLOCATIONDATAAREAID = pd.DATAAREAID AND c.RECID = @bi_ChannelId
INNER JOIN [ax].RETAILDISCOUNTPRICEGROUP rdpg on rdpg.OFFERID = pd.OFFERID AND rdpg.DATAAREAID = pd.DATAAREAID
INNER JOIN {0} pg ON rdpg.PRICEDISCGROUP = pg.RECID
LEFT JOIN [ax].UNITOFMEASURE uom ON uom.RECID = pd.UNITOFMEASURE
INNER JOIN [ax].RETAILGROUPMEMBERLINE rgl ON pd.RETAILGROUPMEMBERLINE = rgl.RECID
LEFT JOIN [crt].RETAILPRODUCTORVARIANTCATEGORYANCESTORSVIEW rpca ON rgl.CATEGORY = rpca.CATEGORY
LEFT JOIN [ax].RETAILDISCOUNTLINEMIXANDMATCH mmol ON pd.DISCOUNTLINEID = mmol.RECID AND pd.DATAAREAID = mmol.DATAAREAID
LEFT JOIN [ax].RETAILDISCOUNTLINEOFFER dol ON pd.DISCOUNTLINEID = dol.RECID AND pd.DATAAREAID = dol.DATAAREAID
INNER JOIN {1} it ON
(
(rgl.VARIANT != 0 AND rgl.VARIANT = it.DISTINCTPRODUCTVARIANT) OR
(rgl.VARIANT = 0 AND rgl.PRODUCT != 0 AND rgl.PRODUCT = it.PRODUCT) OR
(rgl.VARIANT = 0 AND rgl.PRODUCT = 0 AND
(rpca.PRODUCT = it.PRODUCT OR rpca.PRODUCT = it.DISTINCTPRODUCTVARIANT))
)
WHERE (pd.STATUS = 1)
AND (pd.PERIODICDISCOUNTTYPE != 3) -- don't fetch price adjustments
AND (pd.VALIDFROM <= @MinDate OR pd.VALIDFROM <= @NoDate)
AND (pd.VALIDTO >= @MaxDate OR pd.VALIDTO <= @NoDate)
AND pd.CURRENCYCODE = @nvc_CurrencyCode
AND pd.DATAAREAID = @nvc_DataAreaId
ORDER BY pd.OFFERID, pd.LINENUM";
using (TempTable priceGroupTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", priceGroupRecIds))
using (TempTable itemIdTempTable = PricingTempTable.CreateItemIdentifier(context, itemUnits))
{
SqlQuery priceAdjustmentQuery = new SqlQuery(GetRetailDiscountQueryText, priceGroupTempTable.TableName, itemIdTempTable.TableName);
priceAdjustmentQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
priceAdjustmentQuery.Parameters["@NoDate"] = new DateTime(1900, 01, 01);
priceAdjustmentQuery.Parameters["@MinDate"] = fromDate.Date;
priceAdjustmentQuery.Parameters["@MaxDate"] = toDate.Date;
priceAdjustmentQuery.Parameters["@nvc_CurrencyCode"] = currencyCode;
priceAdjustmentQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ReadEntity <PeriodicDiscount>(priceAdjustmentQuery).Results);
}
}