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