private ReadOnlyCollection <RelatedProduct> GetProductRelations(SqliteDatabaseContext context, TempTable assortedProducts)
{
const string GetRelatedProductsQueryString = @"
WITH RELATEDPRODUCTIDS AS
(
SELECT DISTINCT erprt.PRODUCT1 AS RECID
FROM ax.ECORESPRODUCTRELATIONTABLE erprt
WHERE
erprt.PRODUCT1 IN (SELECT LOOKUPID FROM {0})
UNION
SELECT DISTINCT erprt.PRODUCT1 AS RECID
FROM ax.ECORESPRODUCTRELATIONTABLE erprt
WHERE
erprt.PRODUCT2 IN (SELECT LOOKUPID FROM {0})
)
-- Non catalog product relations
SELECT
0 AS CATALOG,
erprt.PRODUCT1 AS PRODUCT,
erprt.PRODUCT2 AS RELATEDPRODUCT,
erprtype.NAME AS PRODUCTRELATIONTYPE,
0 AS EXCLUSION
FROM ax.ECORESPRODUCTRELATIONTABLE erprt
INNER JOIN ax.ECORESPRODUCTRELATIONTYPE erprtype ON erprt.PRODUCTRELATIONTYPE = erprtype.RECID
WHERE
erprt.PRODUCT1 IN (SELECT RECID FROM RELATEDPRODUCTIDS)" ;
SqlQuery sqlQuery = new SqlQuery(GetRelatedProductsQueryString, assortedProducts.TableName);
return(context.ReadEntity <RelatedProduct>(sqlQuery).Results);
}
private ReadOnlyCollection <ProductRules> GetProductRules(SqliteDatabaseContext context, TempTable assortedProducts)
{
// if data level is less than minimal, we don't need product rules
if (this.request.Criteria.DataLevel < CommerceEntityDataLevel.Minimal)
{
return(new ReadOnlyCollection <ProductRules>(new ProductRules[0]));
}
const string Query =
@"SELECT
[prv].PRODUCTID AS 'RECID',
[prv].BLOCKEDONPOS AS BLOCKEDONPOS,
[prv].DATEBLOCKED AS DATEBLOCKED,
[prv].DATETOACTIVATEITEM AS DATETOACTIVATEITEM,
[prv].DATETOBEBLOCKED AS DATETOBEBLOCKED,
[prv].KEYINGINPRICE AS KEYINGINPRICE,
[prv].KEYINGINQTY AS KEYINGINQTY,
[prv].MUSTKEYINCOMMENT AS MUSTKEYINCOMMENT,
[prv].QTYBECOMESNEGATIVE AS QTYBECOMESNEGATIVE,
[prv].SCALEITEM AS SCALEITEM,
[prv].ZEROPRICEVALID AS ZEROPRICEVALID,
[prv].ISSERIALIZED AS ISSERIALIZED,
[prv].ISACTIVEINSALESPROCESS AS ISACTIVEINSALESPROCESS,
[prv].DEFAULTUNITOFMEASURE AS DEFAULTUNITOFMEASURE
FROM CRT.PRODUCTRULESVIEW prv
INNER JOIN {0} ids ON [ids].PRODUCTID = [prv].PRODUCTID
WHERE [prv].DATAAREAID = @DATAAREAID";
SqlQuery sqlQuery = new SqlQuery(Query, assortedProducts.TableName);
sqlQuery.Parameters["@DATAAREAID"] = context.DataAreaId;
return(context.ReadEntity <ProductRules>(sqlQuery).Results);
}
public ReadOnlyCollection <KitDefinition> Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = context.ChannelId;
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.KitMasterProductIds,
true, // skipVariantsExpansion,
this.request.QueryResultSettings.Paging);
using (TempTable assortedProducts = assortedProductsProcedure.GetAssortedProducts())
{
const string GetKitDefinitionQueryString = @"
SELECT DISTINCT
KPM.PRODUCTID AS KITPRODUCTMASTERLISTING,
@bi_ChannelId AS CHANNEL,
RK.DISASSEMBLYATREGISTERALLOWED,
RK.RECID AS KITRECID
FROM {0} AS KPM
INNER JOIN [ax].RETAILKIT RK ON KPM.PRODUCTID = RK.PRODUCTMASTER";
SqlQuery query = new SqlQuery(GetKitDefinitionQueryString, assortedProducts.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
return(context.ReadEntity <KitDefinition>(query).Results);
}
}
}
/// <summary>
/// Checks whether the row version for a sales transaction is valid or not.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="transactionId">The sales transaction identifier.</param>
/// <param name="rowversion">The row version value.</param>
/// <remarks>An exception is thrown for a invalid row version.</remarks>
/// <returns>Transaction's created date time.</returns>
private static DateTime RetrieveTransactionCreatedDateOrThrowOnInvalidRowVersion(SqliteDatabaseContext context, string transactionId, byte[] rowversion)
{
const string GetRowVersionQueryString =
"SELECT {0}, {1} FROM [crt].SALESTRANSACTION WHERE TRANSACTIONID = @TRANSACTIONID;";
var query = new SqlQuery(GetRowVersionQueryString, RowversionColumnName, CreatedDateTimeColumName);
query.Parameters["@TRANSACTIONID"] = transactionId;
byte[] savedRowverion = null;
// default created time as utc now
DateTime createdDateTime = DateTime.UtcNow;
SalesTransactionData transactionData = context.ReadEntity <SalesTransactionData>(query).FirstOrDefault();
if (transactionData != null)
{
savedRowverion = transactionData.Version;
// update created date time value in case it exists in the db
createdDateTime = transactionData.CreatedDateTime.DateTime;
}
// null is fine since the record does not exist yet, otherwise compare to see if they match
if (savedRowverion != null && !RowVersionHelper.AreEquals(rowversion, savedRowverion))
{
throw new StorageException(StorageErrors.Microsoft_Dynamics_Commerce_Runtime_ObjectVersionMismatchError, "Version mismatch while saving transaction.");
}
return(createdDateTime);
}
/// <summary>
/// Get the tax code intervals.
/// </summary>
/// <param name="salesTaxGroup">The sales tax group.</param>
/// <param name="itemTaxGroup">The item tax group.</param>
/// <param name="transactionDate">The transaction date.</param>
/// <returns>The tax code intervals.</returns>
public PagedResult <TaxCodeInterval> GetTaxCodeIntervals(string salesTaxGroup, string itemTaxGroup, DateTimeOffset transactionDate)
{
string query = @"
SELECT DISTINCT
toi.TAXITEMGROUP,
toi.TAXCODE,
IFNULL(td.TAXVALUE, 0.0) AS TAXVALUE,
IFNULL(td.TAXLIMITMIN, 0.0) AS TAXLIMITMIN,
IFNULL(td.TAXLIMITMAX, 0.0) AS TAXLIMITMAX,
tgd.EXEMPTTAX,
tgh.TAXGROUPROUNDING,
tt.TAXCURRENCYCODE,
tt.TAXBASE,
tt.TAXLIMITBASE,
tt.TAXCALCMETHOD,
tt.TAXONTAX,
tt.TAXUNIT,
IFNULL(tcl.TAXMAX,0) AS TAXMAX,
IFNULL(tcl.TAXMIN,0) AS TAXMIN
FROM [ax].TAXGROUPHEADING tgh
INNER JOIN [ax].TAXGROUPDATA tgd ON tgh.TAXGROUP = tgd.TAXGROUP AND tgh.DATAAREAID = tgd.DATAAREAID
INNER JOIN [ax].TAXONITEM toi ON tgd.TAXCODE = toi.TAXCODE AND tgd.DATAAREAID = toi.DATAAREAID
INNER JOIN [ax].TAXDATA td ON toi.TAXCODE = td.TAXCODE AND toi.DATAAREAID = td.DATAAREAID
INNER JOIN [ax].TAXTABLE tt ON tt.TAXCODE = td.TAXCODE AND tt.DATAAREAID = td.DATAAREAID
LEFT JOIN [ax].TAXCOLLECTLIMIT tcl ON
tcl.TAXCODE = td.TAXCODE
AND (tcl.TAXFROMDATE IS NULL
OR @dt_TransactionDate >= tcl.TAXFROMDATE
OR tcl.TAXFROMDATE = @dt_NoDateBoundary)
AND (tcl.TAXTODATE IS NULL
OR @dt_TransactionDateYesterday < td.TAXTODATE
OR tcl.TAXTODATE = @dt_NoDateBoundary)
WHERE
tgh.DATAAREAID = @nvc_DataAreaId
AND toi.TAXITEMGROUP = @nvc_ItemTaxGroup
AND tgh.TAXGROUP = @nvc_SalesTaxGroup
AND ((@dt_TransactionDate >= td.TAXFROMDATE OR td.TAXFROMDATE = @dt_NoDateBoundary)
AND (@dt_TransactionDateYesterday < td.TAXTODATE OR td.TAXTODATE = @dt_NoDateBoundary))";
var attributeSchemaQuery = new SqlQuery(query);
attributeSchemaQuery.Parameters["@nvc_SalesTaxGroup"] = salesTaxGroup;
attributeSchemaQuery.Parameters["@nvc_DataAreaId"] = this.Context.GetChannelConfiguration().InventLocationDataAreaId;
attributeSchemaQuery.Parameters["@nvc_ItemTaxGroup"] = itemTaxGroup;
attributeSchemaQuery.Parameters["@dt_TransactionDate"] = transactionDate.DateTime;
// The parameter below is used so that sqlite uses integers to comapare dates.
attributeSchemaQuery.Parameters["@dt_TransactionDateYesterday"] = transactionDate.DateTime.AddDays(-1);
attributeSchemaQuery.Parameters["@dt_NoDateBoundary"] = this.dateBoundary.Date;
using (var databaseContext = new SqliteDatabaseContext(this.Context))
{
return(databaseContext.ReadEntity <TaxCodeInterval>(attributeSchemaQuery));
}
}
public static PagedResult <CountryRegionInfo> GetCountryRegionIds(SqliteDatabaseContext context, string languageId)
{
const string QueryString = @"SELECT DISTINCT LCNTRY.COUNTRYREGIONID
FROM [ax].LOGISTICSADDRESSCOUNTRYREGION LCNTRY
INNER JOIN [ax].LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION LTRANS ON LTRANS.COUNTRYREGIONID = LCNTRY.COUNTRYREGIONID
WHERE (@nvc_LanguageId IS NULL OR LTRANS.LANGUAGEID = @nvc_LanguageId)";
SqlQuery sqlQuery = new SqlQuery(QueryString);
sqlQuery.Parameters["@nvc_LanguageId"] = languageId;
return(context.ReadEntity <CountryRegionInfo>(sqlQuery));
}
/// <summary>
/// Gets the validation periods of the identifiers.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="periodIds">The validation period identifiers.</param>
/// <returns>Returns the read only collection of validation period.</returns>
public static PagedResult <ValidationPeriod> GetValidationPeriodsByIds(SqliteDatabaseContext context, IEnumerable <string> periodIds)
{
using (TempTable priceGroupTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", periodIds))
{
const string ValidationPeriodQueryText = @"SELECT * FROM [crt].VALIDATIONPERIODVIEW vp
JOIN {0} period ON vp.PERIODID = period.RECID
WHERE vp.CHANNELID = @bi_ChannelId";
SqlQuery validationPeriodQuery = new SqlQuery(ValidationPeriodQueryText, priceGroupTempTable.TableName);
validationPeriodQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
return(context.ReadEntity <ValidationPeriod>(validationPeriodQuery));
}
}
private PagedResult <GlobalCustomer> SearchCustomersSproc(string keyword, bool onlyCurrentCompany, QueryResultSettings settings)
{
using (SqliteDatabaseContext databaseContext = new SqliteDatabaseContext(this.Context))
{
// Use the "LIKE" to search for customer related table fields
// Based on the pre-populated materialized view crt_CUSTOMERSEARCHABLEFIELDSVIEW
string queryText = @"WITH UNIQUEPARTYIDS AS
(
SELECT DISTINCT csfv.PARTY
FROM crt_CUSTOMERSEARCHABLEFIELDSVIEW csfv
INNER JOIN (
SELECT PARTY, ADDRESSBOOK
FROM AX_DIRADDRESSBOOKPARTY
WHERE PARTY NOT IN (SELECT RECID FROM AX_OMINTERNALORGANIZATION)) dap ON csfv.PARTY = dap.PARTY
INNER JOIN AX_RETAILSTOREADDRESSBOOK rsab ON dap.ADDRESSBOOK = rsab.ADDRESSBOOK
WHERE rsab.STORERECID = @bi_ChannelId AND csfv.FIELD LIKE @nvc_SearchCondition
)
SELECT PARTYNUMBER,
CASE
WHEN gcv.DATAAREAID <> '' THEN gcv.ACCOUNTNUMBER
ELSE ''
END AS ACCOUNTNUMBER,
NAME,
FULLADDRESS,
PHONE,
EMAIL,
INSTANCERELATIONTYPE as CUSTOMERTYPE,
ORGID,
RECORDID
FROM CRT_GLOBALCUSTOMERSVIEW gcv
INNER JOIN UNIQUEPARTYIDS pid ON gcv.RECORDID = pid.PARTY
WHERE (@b_SearchCrossCompany <> 1 AND ACCOUNTNUMBER != '' AND gcv.DATAAREAID = @nvc_DataAreaId)
OR (@b_SearchCrossCompany = 1)
LIMIT @i_Top, @i_Skip;";
var parameters = new ParameterSet();
parameters["@bi_ChannelId"] = databaseContext.ChannelId;
parameters["@nvc_DataAreaId"] = databaseContext.DataAreaId;
parameters["@nvc_SearchCondition"] = string.Format("%{0}%", keyword);
parameters["@b_SearchCrossCompany"] = Convert.ToInt32(onlyCurrentCompany);
parameters["@i_Top"] = settings.Paging.Top;
parameters["@i_Skip"] = settings.Paging.Skip;
var query = new SqlQuery(queryText, parameters);
PagedResult <GlobalCustomer> customers = databaseContext.ReadEntity <GlobalCustomer>(query);
return(customers);
}
}
private ReadOnlyCollection <ProductIdentity> GetProductIdentities(SqliteDatabaseContext context, TempTable assortedProducts)
{
string query = @"
-- For the filtered identifiers, we can now build the identity for each product.
-- For this query, LOOKUPID is either the MASTER or STANDALONE ID, PRODUCTID is the actual variantid or master/standalone when no variant is present
SELECT DISTINCT
ap.PRODUCTID as RECID, -- id of the actual variant if present or master/standalone
ap.LOOKUPID as LOOKUPID, -- master id or standalone id
ap.ISMASTER as ISMASTER,
CASE
WHEN ([rk].RECID IS NULL) THEN 0
ELSE 1
END AS ISKIT,
0 AS ISREMOTE,
--CASE
-- WHEN ISMASTER = 0 AND ap.VARIANTID <> 0 THEN ap.PRODUCTID -- when it's not a master and variant is populated, then it's a variant and it's parentid is PRODUCTID
-- ELSE 0 -- otherwise it's a standalone or a master
--END AS MASTERPRODUCT,
CASE
WHEN ap.ISMASTER = 0 THEN ap.LOOKUPID
ELSE 0
END AS MASTERPRODUCT, -- GETPRODUCTS always returns MASTERPRODUCT = LOOKUPID if ISMATER = 0
ap.ITEMID AS ITEMID,
COALESCE(idc.INVENTDIMID, '') AS INVENTDIMID,
erp.DISPLAYPRODUCTNUMBER AS DISPLAYPRODUCTNUMBER,
erp.SEARCHNAME AS SEARCHNAME
FROM {0} ap
INNER JOIN [ax].ECORESPRODUCT erp ON [erp].RECID = [ap].PRODUCTID
LEFT OUTER JOIN [ax].INVENTDIMCOMBINATION idc ON [idc].DISTINCTPRODUCTVARIANT = [ap].PRODUCTID AND [idc].DATAAREAID = @DATAAREAID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [ap].PRODUCTID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [ap].PRODUCTID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [ap].PRODUCTID
-- Product builder will expect a specific ordering: master first, then variants
ORDER BY
ap.LOOKUPID, -- master/variants are together in the result set
ap.ISMASTER DESC -- master first, then variants
";
SqlQuery sqlQuery = new SqlQuery(query, assortedProducts.TableName);
sqlQuery.Parameters["@DATAAREAID"] = context.DataAreaId;
return(context.ReadEntity <ProductIdentity>(sqlQuery).Results);
}
/// <summary>
/// Gets the price group collection for the given list of price groups.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="priceGroups">The price group identifiers.</param>
/// <returns>Returns the read-only collection of price group.</returns>
public static ReadOnlyCollection <PriceGroup> GetPriceGroup(SqliteDatabaseContext context, IEnumerable <string> priceGroups)
{
using (TempTable priceGroupTempTable = PricingTempTable.CreatePriceGroup(context, priceGroups))
{
const string PriceGroupQueryText = @"SELECT (CASE(pa.PRICEGROUP)
WHEN 0
THEN pg.RECID
ELSE pa.PRICEGROUP
END) PRICEGROUP
FROM {0} AS pa
INNER JOIN [ax].PRICEDISCGROUP pg ON pg.GROUPID = pa.GROUPID AND DATAAREAID = @nvc_DataAreaId";
var priceGroupQuery = new SqlQuery(PriceGroupQueryText, priceGroupTempTable.TableName);
priceGroupQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ReadEntity <PriceGroup>(priceGroupQuery).Results);
}
}
private static void PopulateCategoryPath(SqliteDatabaseContext context, ReadOnlyCollection <ChannelCategoryAttribute> channelCategoryAttributes, long channelId)
{
// this query returns all parent category names ordered from root to node for each childCategoryId
const string GetCategoryParentNameQuery = @"
SELECT
RPERC.ORIGINID AS CATEGORY,
ERC.NAME AS NAME
FROM [ax].RETAILPUBECORESCATEGORY RPERC
INNER JOIN [ax].ECORESCATEGORY ERC ON ERC.CATEGORYHIERARCHY = RPERC.CATEGORYHIERARCHY AND ERC.NESTEDSETLEFT <= RPERC.NESTEDSETLEFT AND ERC.NESTEDSETRIGHT >= RPERC.NESTEDSETRIGHT
WHERE
RPERC.CHANNEL = @bi_ChannelId
AND RPERC.ORIGINID IN (SELECT RECID FROM {0})
ORDER BY
ERC.NESTEDSETLEFT ASC";
ReadOnlyCollection <CategoryName> categoryNames;
IEnumerable <long> categoryIds = channelCategoryAttributes.Select(category => category.Category).Distinct();
using (TempTable categoryIdsTempTable = TempTableHelper.CreateScalarTempTable <long>(context, "RECID", categoryIds))
{
SqlQuery query = new SqlQuery(GetCategoryParentNameQuery, categoryIdsTempTable.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
categoryNames = context.ReadEntity <CategoryName>(query).Results;
}
ILookup <long, ChannelCategoryAttribute> categoryAttributesByCategoryIds = channelCategoryAttributes.ToLookup(category => category.Category);
StringBuilder builder = new StringBuilder();
foreach (var group in categoryNames.GroupBy(categoryName => categoryName.Category))
{
long categoryId = group.Key;
builder.Clear();
group.Aggregate(builder, AggregateCategoryName);
string fullCategoryPath = builder.ToString();
foreach (ChannelCategoryAttribute categoryAttributes in categoryAttributesByCategoryIds[categoryId])
{
categoryAttributes.CategoryPath = fullCategoryPath;
}
}
}
/// <summary>
/// Gets the affiliation price groups.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="affiliationPriceGroups">The affiliation price groups.</param>
/// <returns>Returns the affiliation price groups.</returns>
public static PagedResult <PriceGroup> GetAffiliationPriceGroups(SqliteDatabaseContext context, IEnumerable <AffiliationLoyaltyTier> affiliationPriceGroups)
{
using (TempTable priceGroupTempTable = PricingTempTable.CreateAffiliationPriceGroup(context, affiliationPriceGroups))
{
const string AffiliationPriceGroupQueryText = @"SELECT pdg.GROUPID
,rapg.PRICEDISCGROUP AS 'PRICEGROUP'
,pdg.RETAILPRICINGPRIORITYNUMBER AS 'RETAILPRICINGPRIORITYNUMBER'
,rapg.RECID
FROM [ax].PRICEDISCGROUP pdg
INNER JOIN [ax].RETAILAFFILIATIONPRICEGROUP rapg ON rapg.PRICEDISCGROUP = pdg.RECID
INNER JOIN {0} alt
ON rapg.RETAILAFFILIATION = alt.AFFILIATIONID AND (rapg.RETAILLOYALTYTIER = 0 OR rapg.RETAILLOYALTYTIER = alt.LOYALTYTIERID)
INNER JOIN [ax].RETAILCHANNELTABLE AS c ON c.INVENTLOCATIONDATAAREAID = pdg.DATAAREAID AND c.RECID = @bi_ChannelId";
var priceGroupQuery = new SqlQuery(AffiliationPriceGroupQueryText, priceGroupTempTable.TableName);
priceGroupQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
return(context.ReadEntity <PriceGroup>(priceGroupQuery));
}
}
/// <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>
/// Get addresses for the customer that meets the supplied criteria.
/// </summary>
/// <param name="databaseContext">The SQLite database context.</param>
/// <param name="customer">Customer to retrieve addresses for.</param>
/// <returns>
/// A collection of addresses for the specified customer.
/// </returns>
private static ReadOnlyCollection <Address> GetAddresses(SqliteDatabaseContext databaseContext, Customer customer)
{
ThrowIf.Null(customer, "customer");
// don't fetch addresses if the customer doesn't have a party id
if (customer.DirectoryPartyRecordId == 0)
{
return(new ReadOnlyCollection <Address>(new Collection <Address>()));
}
var addressesQuery = new SqlPagedQuery(QueryResultSettings.AllRecords)
{
From = CustomerPostalAddressesView,
Where = "DIRPARTYTABLERECID = @party",
};
addressesQuery.Parameters["@party"] = customer.DirectoryPartyRecordId;
ReadOnlyCollection <Address> addresses = databaseContext.ReadEntity <Address>(addressesQuery).Results;
return(addresses);
}
private PagedResult <Customer> GetCustomersView(string accountNumber, QueryResultSettings settings)
{
PagedResult <Customer> customers = new List <Customer>().AsPagedResult();
using (SqliteDatabaseContext databaseContext = new SqliteDatabaseContext(this.Context))
{
var customersQuery = new SqlPagedQuery(settings)
{
From = CustomersView,
};
string operand = string.Empty;
if (!string.IsNullOrWhiteSpace(accountNumber))
{
customersQuery.Where = string.Format("{0}{1}ACCOUNTNUMBER = @accountNumber", customersQuery.Where, operand);
customersQuery.Parameters["@accountNumber"] = accountNumber;
operand = " and ";
}
customersQuery.Where = string.Format("{0}{1}DATAAREAID = @dataAreaId", customersQuery.Where, operand);
customersQuery.Parameters["@dataAreaId"] = databaseContext.DataAreaId;
customers = databaseContext.ReadEntity <Customer>(customersQuery);
foreach (Customer customer in customers.Results)
{
// we don't need to use any pagination for this call.
customer.Addresses = GetAddresses(databaseContext, customer);
}
}
// process images if any
var dataServiceRequest = new ProcessCustomerImagesDataRequest(customers.Results);
this.Context.Runtime.Execute <NullResponse>(dataServiceRequest, this.Context);
return(customers);
}
public static PagedResult <CountryRegionInfo> GetCountryRegionInfo(SqliteDatabaseContext context, string languageId, TempTable countryRegionIdsTempTable)
{
string queryString = @"SELECT LCNTRY.COUNTRYREGIONID
,LCNTRY.ISOCODE
,LCNTRY.TIMEZONE
,LCNTRY.ADDRFORMAT AS ADDRESSFORMATID
,LFORMAT.NAME AS ADDRESSFORMATNAME
,LTRANS.SHORTNAME
,LTRANS.LONGNAME
,LTRANS.LANGUAGEID
FROM {0} CRI
INNER JOIN [ax].LOGISTICSADDRESSCOUNTRYREGION LCNTRY ON LCNTRY.COUNTRYREGIONID = CRI.RECID
INNER JOIN [ax].LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION LTRANS ON LTRANS.COUNTRYREGIONID = LCNTRY.COUNTRYREGIONID
INNER JOIN [ax].LOGISTICSADDRESSFORMATHEADING LFORMAT ON LFORMAT.ADDRFORMAT = LCNTRY.ADDRFORMAT
WHERE (@nvc_LanguageId IS NULL OR LTRANS.LANGUAGEID = @nvc_LanguageId)";
SqlQuery sqlQuery = new SqlQuery(queryString, countryRegionIdsTempTable.TableName);
sqlQuery.Parameters["@nvc_LanguageId"] = languageId;
return(context.ReadEntity <CountryRegionInfo>(sqlQuery));
}
/// <summary>
/// Executes the procedure.
/// </summary>
/// <returns>The collection of channel category attributes.</returns>
public PagedResult <ChannelCategoryAttribute> Execute()
{
PagedResult <ChannelCategoryAttribute> channelCategoryAttributes;
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = ChannelCategoryAttributeViewName,
Where = "HOSTCHANNEL = " + SqlParamChannelId
};
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType categoryRecordIds = new RecordIdTableType(this.request.CategoryIds, "CATEGORY"))
{
query.Parameters[SqlParamChannelId] = this.request.ChannelId;
query.Parameters["@TVP_RECIDTABLETYPE"] = categoryRecordIds;
channelCategoryAttributes = context.ReadEntity <ChannelCategoryAttribute>(query);
PopulateCategoryPath(context, channelCategoryAttributes.Results, this.request.ChannelId);
}
return(channelCategoryAttributes);
}
public static PagedResult <AddressFormattingInfo> GetAddressFormatInfo(SqliteDatabaseContext context, TempTable countryRegionIdsTempTable)
{
string queryString = @"SELECT LACR.COUNTRYREGIONID
,LAFL.ELEMENT
,CAST(LAFL.LINENUM AS INT) AS LINEINDEX
,LAFL.DATAENTRYONLY
,LAFL.SEPARATOR
,LAFL.SEPARATORCRLF
,LAFL.INACTIVE
,LAFL.EXPAND
,LAFL.SPECIAL
,LAFL.NUMOFSPACES
FROM {0} CRI
INNER JOIN [ax].LOGISTICSADDRESSCOUNTRYREGION LACR ON LACR.COUNTRYREGIONID = CRI.RECID
INNER JOIN [ax].LOGISTICSADDRESSFORMATHEADING LAFH ON LACR.ADDRFORMAT = LAFH.ADDRFORMAT
INNER JOIN [ax].LOGISTICSADDRESSFORMATLINES LAFL ON LAFH.ADDRFORMAT = LAFL.ADDRFORMAT
WHERE LAFL.INACTIVE = 0
ORDER BY LINEINDEX ASC";
SqlQuery sqlQuery = new SqlQuery(queryString, countryRegionIdsTempTable.TableName);
return(context.ReadEntity <AddressFormattingInfo>(sqlQuery));
}
public ReadOnlyCollection <KitConfigToComponentAssociation> Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = context.ChannelId;
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.KitMasterProductIds,
true, // skipVariantsExpansion,
PagingInfo.AllRecords);
using (TempTable assortedProducts = assortedProductsProcedure.GetAssortedProducts())
{
const string GetKitVariantMapQueryString = @"
SELECT DISTINCT
RKVC.COMPONENT AS COMPONENTPRODUCTLISTING,
RKVC.KITVARIANT AS KITPRODUCTVARIANTLISTING,
IT.PRODUCT AS KITPRODUCTMASTERLISTING,
IDC.INVENTDIMID AS INVENTDIMID,
ID.CONFIGID AS CONFIGID,
RKVC.COMPONENTRECID AS KITLINEIDENTIFIER
FROM [ax].RETAILKITVARIANTCOMPONENT RKVC
INNER JOIN [ax].INVENTDIMCOMBINATION IDC ON IDC.DISTINCTPRODUCTVARIANT = RKVC.KITVARIANT
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTDIM ID ON ID.INVENTDIMID = IDC.INVENTDIMID AND ID.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
INNER JOIN [ax].INVENTTABLE IT ON IT.ITEMID = IDC.ITEMID
INNER JOIN {0} PID ON PID.PRODUCTID = IT.PRODUCT";
SqlQuery query = new SqlQuery(GetKitVariantMapQueryString, assortedProducts.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
return(context.ReadEntity <KitConfigToComponentAssociation>(query).Results);
}
}
}
private ReadOnlyCollection <Item> GetProductIdentities(SqliteDatabaseContext databaseContext, TempTable assortedProducts)
{
string query = @"
SELECT DISTINCT
it.[RECID],
it.[ITEMID],
erpt.[NAME],
erpt.[DESCRIPTION],
itm.[PRICE],
itm.[UNITID],
it.[PRODUCT],
itm.[MARKUPGROUPID],
itm.[MARKUP],
itm.[ALLOCATEMARKUP],
itm.[PRICEQTY],
itm.[LINEDISC],
itm.[MULTILINEDISC],
itm.[ENDDISC],
rit.[NODISCOUNTALLOWED],
itm.[TAXITEMGROUPID] AS 'ITEMTAXGROUPID',
iitm.[UNITID] AS 'INVENTUNITID'
FROM {0} ap
INNER JOIN [crt].CHANNELLANGUAGESVIEW clv ON clv.CHANNEL = @ChannelId AND clv.ISDEFAULT = 1 -- Use default channel language
INNER JOIN [ax].INVENTTABLE it ON it.PRODUCT = ap.PRODUCTID AND it.DATAAREAID = @DataAreaId
INNER JOIN [ax].INVENTTABLEMODULE itm ON itm.MODULETYPE = 2 AND itm.ITEMID = it.ITEMID AND itm.DATAAREAID = it.DATAAREAID -- Module type for sales order
INNER JOIN [ax].INVENTTABLEMODULE iitm ON iitm.MODULETYPE = 0 AND iitm.ITEMID = it.ITEMID AND iitm.DATAAREAID = it.DATAAREAID -- Module type for inventory
INNER JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON erpt.PRODUCT = it.PRODUCT AND erpt.LANGUAGEID = clv.LANGUAGEID
INNER JOIN [ax].RETAILINVENTTABLE rit ON it.ITEMID = rit.ITEMID AND rit.DATAAREAID = it.DATAAREAID";
SqlQuery sqlQuery = new SqlQuery(query, assortedProducts.TableName);
sqlQuery.Parameters["@ChannelId"] = databaseContext.ChannelId;
sqlQuery.Parameters["@DataAreaId"] = databaseContext.DataAreaId;
return(databaseContext.ReadEntity <Item>(sqlQuery).Results);
}
public static ReadOnlyCollection <TradeAgreement> GetAllDiscountTradeAgreements(
SqliteDatabaseContext context,
IEnumerable <string> itemIds,
string customerAccount,
DateTimeOffset minActiveDate,
DateTimeOffset maxActiveDate,
string currencyCode)
{
const string DiscountTradeAgreementsQueryText = @"SELECT ta.RECID,
ta.ITEMCODE,
ta.ACCOUNTCODE,
ta.ITEMRELATION,
ta.ACCOUNTRELATION,
ta.QUANTITYAMOUNTFROM,
ta.QUANTITYAMOUNTTO,
ta.FROMDATE,
ta.TODATE,
ta.AMOUNT,
ta.CURRENCY,
ta.PERCENT1,
ta.PERCENT2,
ta.SEARCHAGAIN,
ta.PRICEUNIT,
ta.RELATION,
ta.UNITID,
ta.MARKUP,
ta.ALLOCATEMARKUP,
ta.INVENTDIMID,
ta.MAXIMUMRETAILPRICE_IN AS MAXIMUMRETAILPRICEINDIA,
invdim.CONFIGID,
invdim.INVENTCOLORID,
invdim.INVENTSIZEID,
invdim.INVENTSTYLEID
FROM [ax].PRICEDISCTABLE ta
INNER JOIN [ax].RETAILCHANNELTABLE AS c
ON c.INVENTLOCATIONDATAAREAID = ta.DATAAREAID AND c.RECID = @bi_ChannelId
LEFT JOIN [ax].INVENTDIM invdim ON ta.INVENTDIMID = invdim.INVENTDIMID AND ta.DATAAREAID = invdim.DATAAREAID
WHERE
-- agreement is of one of the sales discount types
ta.RELATION IN (5, 6, 7)
AND ta.CURRENCY = @nvc_CurrencyCode
-- and currently active
AND ((ta.FROMDATE <= @MinDate OR ta.FROMDATE <= @NoDate)
AND (ta.TODATE >= @MaxDate OR ta.TODATE <= @NoDate))
AND ta.DATAAREAID = @nvc_DataAreaId
-- and customer/group relation matches
AND
(
-- account code is group and relation is in the price groups
((ta.ACCOUNTCODE = 1) AND
(
(ta.RELATION = 5 AND ta.ACCOUNTRELATION = (SELECT LINEDISC FROM {1}))) OR
(ta.RELATION = 6 AND ta.ACCOUNTRELATION = (SELECT MULTILINEDISC FROM {1})) OR
(ta.RELATION = 7 AND ta.ACCOUNTRELATION = (SELECT ENDDISC FROM {1})))
OR
-- or account code is customer and customer is on the agreement
((ta.ACCOUNTCODE = 0) AND (ta.ACCOUNTRELATION = @Customer))
OR
-- or account code is ALL customers
((ta.ACCOUNTCODE = 2))
)
-- and item/group relation matches
AND
(
-- item code is one of the items passed in
((ta.ITEMCODE = 0) AND (ta.ITEMRELATION IN (SELECT i.RECID FROM {0} i)))
OR
-- item code is group and trade agreement type is line discount, match item line discounts
((ta.ITEMCODE = 1) AND (ta.RELATION = 5) AND (ta.ITEMRELATION IN (SELECT LINEDISC FROM {2})))
OR
-- item code is group and trade agreement type is multiline discount, match item line multiline discounts
((ta.ITEMCODE = 1) AND (ta.RELATION = 6) AND (ta.ITEMRELATION IN( SELECT MULTILINEDISC FROM {3})))
OR
-- item code is all items
(ta.ITEMCODE = 2)
)
-- and warehouse is either for all or for current channel
AND
(
invdim.INVENTLOCATIONID = '' OR (invdim.INVENTLOCATIONID = c.INVENTLOCATION)
)";
using (TempTable items = TempTableHelper.CreateScalarTempTable(context, "RECID", itemIds))
using (TempTable customerPriceGroupTempTable = PricingTempTable.CreateCustomerPriceGroups(context, customerAccount))
using (TempTable itemLineDiscountTempTable = PricingTempTable.CreateItemLineDiscount(context, itemIds))
using (TempTable itemMultiLineDiscountTempTable = PricingTempTable.CreateMultiLineDiscount(context, itemIds))
{
var discountTradeAgreementsQuery = new SqlQuery(
DiscountTradeAgreementsQueryText,
items.TableName,
customerPriceGroupTempTable.TableName,
itemLineDiscountTempTable.TableName,
itemMultiLineDiscountTempTable.TableName);
discountTradeAgreementsQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
discountTradeAgreementsQuery.Parameters["@Customer"] = customerAccount;
discountTradeAgreementsQuery.Parameters["@NoDate"] = new DateTime(1900, 01, 01);
discountTradeAgreementsQuery.Parameters["@MinDate"] = minActiveDate.Date;
discountTradeAgreementsQuery.Parameters["@MaxDate"] = maxActiveDate.Date;
return(context.ReadEntity <TradeAgreement>(discountTradeAgreementsQuery).Results);
}
}
public PagedResult <KitComponent> Execute()
{
const string GetKitComponentsQueryString = @"
WITH ExplodedKitComponentVariants AS
(
-- First explode all the kit components/substitutes that are product masters
SELECT DISTINCT
PV.RECID AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
MasterProductIds.RECID AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {0} AS MasterProductIds -- get all masters
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.PRODUCTMASTER = MasterProductIds.RECID -- explode variants for the masters
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = PV.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
UNION
SELECT DISTINCT
PV.RECID AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
MasterProductIds.RECID AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {0} AS MasterProductIds -- get all masters
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.PRODUCTMASTER = MasterProductIds.RECID -- explode variants for the masters
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = PV.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
),
KitComponentVariantsInfo AS
(
-- Collect information of all kit components/substitutes that are non product masters.
-- Standalone products
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
StandaloneAndVariantIds.RECID AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
StandaloneAndVariantIds.RECID AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
-- Variant productS
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
),
KitComponentInfo AS
(
-- For the same product in a component line of a given kit, component information specified at the non-product-master level takes precedence over component information specified at the product master level.
-- Hence, add an entry from the exploded table only if an entry does not already exist in the non-product master table.
SELECT
KCVI.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
KCVI.QUANTITY AS QUANTITY,
KCVI.UNIT AS UNIT,
KCVI.CHARGE AS CHARGE,
KCVI.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
KCVI.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
KCVI.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
KCVI.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
KCVI.CHANNEL AS CHANNEL,
KCVI.ITEMID AS ITEMID
FROM KitComponentVariantsInfo KCVI
UNION ALL
SELECT
EKCV.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
EKCV.QUANTITY AS QUANTITY,
EKCV.UNIT AS UNIT,
EKCV.CHARGE AS CHARGE,
EKCV.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
EKCV.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
EKCV.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
EKCV.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
EKCV.CHANNEL AS CHANNEL,
EKCV.ITEMID AS ITEMID
FROM ExplodedKitComponentVariants EKCV
WHERE NOT EXISTS
(
SELECT
*
FROM KitComponentVariantsInfo KCVI
WHERE
KCVI.KITLINEPRODUCTLISTING = EKCV.KITLINEPRODUCTLISTING
AND KCVI.KITPRODUCTMASTERLISTING = EKCV.KITPRODUCTMASTERLISTING
AND KCVI.KITLINEIDENTIFIER = EKCV.KITLINEIDENTIFIER
)
)
SELECT
KCI.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
KCI.QUANTITY AS QUANTITY,
KCI.UNIT AS UNIT,
KCI.CHARGE AS CHARGE,
KCI.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
KCI.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
KCI.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
KCI.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
KCI.CHANNEL AS CHANNEL,
KCI.ITEMID AS ITEMID
FROM KitComponentInfo KCI";
PagedResult <KitComponent> kitComponents;
IEnumerable <long> standaloneAndVariantProductIds = this.request.ProductIds.Except(this.request.MasterProductIds);
IEnumerable <long> masterProductIds = this.request.MasterProductIds;
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType standaloneAndVariantProductIdsTableType = new RecordIdTableType(StandaloneAndVariantProductRecordIdTableName, standaloneAndVariantProductIds))
using (RecordIdTableType masterProductIdsTableType = new RecordIdTableType(MasterProductRecordIdTableName, masterProductIds))
using (TempTable standaloneAndVariantProductIdsTempTable = context.CreateTemporaryTable(standaloneAndVariantProductIdsTableType.DataTable))
using (TempTable masterProductIdsTempTable = context.CreateTemporaryTable(masterProductIdsTableType.DataTable))
{
SqlQuery query = new SqlQuery(
GetKitComponentsQueryString,
masterProductIdsTempTable.TableName,
standaloneAndVariantProductIdsTempTable.TableName);
query.Parameters["@bi_ChannelId"] = context.ChannelId;
kitComponents = context.ReadEntity <KitComponent>(query);
}
return(kitComponents);
}
private PagedResult <SalesOrder> GetTransactions()
{
const string SelectSalesTransactions = @"
(
SELECT
RT.CHANNEL AS 'CHANNELID'
,RT.TRANSACTIONID AS 'TRANSACTIONID'
,CASE
WHEN (RT.SALESORDERID <> '') THEN RT.SALESORDERID
ELSE COALESCE(OS.SALESID, '')
END AS 'SALESID'
,RT.CREATEDDATETIME AS 'CREATEDDATETIME'
,(RT.GROSSAMOUNT * -1.0) AS 'GROSSAMOUNT'
,CASE WHEN RT.TYPE = 7 THEN -- TYPE = 7: TenderDeclaration
(
SELECT SUM(TD.AMOUNTTENDERED)
FROM ax_RETAILTRANSACTIONTENDERDECLARATIONTRANS TD
WHERE
TD.TRANSACTIONID = RT.TRANSACTIONID
AND TD.TERMINAL = RT.TERMINAL
AND TD.DATAAREAID = RT.DATAAREAID
AND TD.TRANSACTIONSTATUS = 0 -- 0: Normal status transaction
AND TD.CHANNEL = RT.CHANNEL
)
WHEN RT.TYPE = 16 THEN -- TYPE = 16: BankDrop
(
SELECT SUM(B.AMOUNTTENDERED)
FROM ax_RETAILTRANSACTIONBANKEDTENDERTRANS B
WHERE
B.TRANSACTIONID = RT.TRANSACTIONID
AND B.TERMINAL = RT.TERMINAL
AND B.DATAAREAID = RT.DATAAREAID
AND B.TRANSACTIONSTATUS = 0 -- 0: Normal status transaction
AND B.CHANNEL = RT.CHANNEL
)
WHEN RT.TYPE = 17 THEN -- TYPE = 17: SafeDrop
(
SELECT SUM(S.AMOUNTTENDERED)
FROM ax_RETAILTRANSACTIONSAFETENDERTRANS S
WHERE
S.TRANSACTIONID = RT.TRANSACTIONID
AND S.TERMINAL = RT.TERMINAL
AND S.DATAAREAID = RT.DATAAREAID
AND S.TRANSACTIONSTATUS = 0 -- 0: Normal status transaction
AND S.CHANNEL = RT.CHANNEL
)
WHEN RT.TYPE = 3 THEN -- TYPE = 3: Payment
(
SELECT SUM(P.AMOUNTTENDERED)
FROM ax_RETAILTRANSACTIONPAYMENTTRANS P
WHERE
P.TRANSACTIONID = RT.TRANSACTIONID
AND P.TERMINAL = RT.TERMINAL
AND P.DATAAREAID = RT.DATAAREAID
AND P.TRANSACTIONSTATUS = 0 -- 0: Normal status transaction
AND P.CHANNEL = RT.CHANNEL
)
ELSE (RT.GROSSAMOUNT * -1.0) END AS 'TOTALAMOUNT'
,RT.PAYMENTAMOUNT AS 'AMOUNTPAID'
,RT.TERMINAL AS 'TERMINAL'
,RT.STORE AS 'STORE'
,RT.STAFF AS 'STAFF'
,RT.CUSTACCOUNT AS 'CUSTOMERID'
,RT.RECEIPTEMAIL AS 'RECEIPTEMAIL'
,RT.TYPE
,RT.DISCAMOUNT AS 'DISCOUNTAMOUNT'
,IFNULL(AT.DELIVERYNAME,DPT.NAME) AS 'NAME'
,COALESCE((
SELECT SUM(TAXAMOUNT * -1.0)
FROM ax_RETAILTRANSACTIONSALESTRANS RTST
WHERE
RTST.TRANSACTIONID = RT.TRANSACTIONID
AND RTST.TERMINALID = RT.TERMINAL
AND RTST.STORE = RT.STORE
AND RTST.CHANNEL = RT.CHANNEL
AND RTST.TRANSACTIONSTATUS <> 1 -- Excludes the voided line
), 0) AS 'TAXAMOUNT'
,COALESCE((
SELECT ISINCLUDEDINPRICE
FROM ax_RETAILTRANSACTIONTAXTRANS TT
WHERE
TT.TRANSACTIONID = RT.TRANSACTIONID
AND TT.TERMINALID = RT.TERMINAL
AND TT.STOREID = RT.STORE
AND TT.CHANNEL = RT.CHANNEL LIMIT 1 ), 0) AS 'ISTAXINCLUDEDINPRICE'
,COALESCE((
SELECT SUM(CALCULATEDAMOUNT)
FROM ax_RETAILTRANSACTIONMARKUPTRANS MT
WHERE
MT.TRANSACTIONID = RT.TRANSACTIONID
AND MT.TERMINALID = RT.TERMINAL
AND MT.STORE = RT.STORE
AND MT.CHANNEL = RT.CHANNEL), 0) AS 'CHARGEAMOUNT'
,RT.CHANNELREFERENCEID AS 'CHANNELREFERENCEID'
,RT.INVENTLOCATIONID AS 'INVENTLOCATIONID'
,RT.INVENTSITEID AS 'INVENTSITEID'
,RT.DLVMODE AS 'DELIVERYMODE'
,RT.SHIPPINGDATEREQUESTED AS 'REQUESTEDDELIVERYDATE'
,RT.LOGISTICSPOSTALADDRESS AS 'LOGISTICSPOSTALADDRESS'
,RT.RECEIPTID AS 'RECEIPTID'
,RT.LOYALTYCARDID AS 'LOYALTYCARDID'
,RT.ENTRYSTATUS AS 'ENTRYSTATUS'
,CASE
WHEN (AT.EMAIL IS NOT NULL AND AT.EMAIL <> '') THEN AT.EMAIL
WHEN (RT.RECEIPTEMAIL IS NOT NULL AND RT.RECEIPTEMAIL <> '') THEN RT.RECEIPTEMAIL
ELSE CT.EMAIL
END AS 'EMAIL'
,IFNULL(AT.PHONE,'') AS 'PHONE'
,IFNULL(OS.STATUS, 0) AS 'STATUS'
,RT.TRANSTIME + RT.TRANSDATE AS 'BEGINDATETIME'
,RT.BUSINESSDATE
,RT.STATEMENTCODE
,RT.COMMENT AS 'COMMENT'
,RT.INVOICECOMMENT AS 'INVOICECOMMENT'
,RT.CREATEDOFFLINE AS 'ISCREATEDOFFLINE'
,CO.CANCELLATIONCHARGE AS 'CANCELLATIONCHARGE'
,CO.DEPOSITOVERRIDE AS 'DEPOSITOVERRIDE'
,CO.REQUIREDDEPOSIT AS 'REQUIREDDEPOSIT'
,CO.CALCULATEDDEPOSIT AS 'CALCULATEDDEPOSIT'
,CO.PREPAYMENTPAID AS 'PREPAYMENTPAID'
,CO.PREPAYMENTINVOICED AS 'PREPAYMENTINVOICED'
,IFNULL((
SELECT 1 AS 'HASREDEEMLINE' FROM ax_RETAILTRANSACTIONLOYALTYREWARDPOINTTRANS RPT
WHERE RPT.DATAAREAID = RT.DATAAREAID
AND RPT.TRANSACTIONID = RT.TRANSACTIONID
AND RPT.TERMINALID = RT.TERMINAL
AND RPT.STOREID = RT.STORE
AND RPT.CHANNEL = RT.CHANNEL
AND RPT.ENTRYTYPE = 2 LIMIT 1), 0) AS 'HASLOYALTYPAYMENT' -- EntryType 2 is Redeem. HASLOYALTYPAYMENT = 1 if true, 0 if false.
FROM ax_RETAILTRANSACTIONTABLE RT
LEFT JOIN ax_RETAILTRANSACTIONORDERSTATUS OS ON
OS.TRANSACTIONID = RT.TRANSACTIONID
AND OS.TERMINAL = RT.TERMINAL
AND OS.STORE = RT.STORE
AND OS.CHANNEL = RT.CHANNEL
LEFT JOIN ax_RETAILTRANSACTIONADDRESSTRANS AT ON
AT.TRANSACTIONID = RT.TRANSACTIONID
AND AT.TERMINAL = RT.TERMINAL
AND AT.STORE = RT.STORE
AND AT.CHANNEL = RT.CHANNEL
AND AT.SALELINENUM = 0 -- SaleLineNum 0 = Header level address
LEFT JOIN crt_CUSTOMERORDERTRANSACTION CO ON
CO.CHANNEL = RT.CHANNEL
AND CO.STORE = RT.STORE
AND CO.TERMINAL = RT.TERMINAL
AND CO.TRANSACTIONID = RT.TRANSACTIONID
AND CO.DATAAREAID = RT.DATAAREAID
LEFT JOIN crt_CUSTOMERSVIEW CT ON CT.ACCOUNTNUMBER = RT.CUSTACCOUNT
AND CT.DATAAREAID = RT.DATAAREAID
LEFT JOIN ax_DIRPARTYTABLE DPT ON CT.PARTY = DPT.RECID
WHERE
@customerName IS NULL OR (AT.DELIVERYNAME LIKE @customerName OR DPT.NAME LIKE @customerName)
)
";
var settings = this.request.QueryResultSettings;
SortingInfo sorting = (settings.Sorting == null || settings.Sorting.Count == 0)
? new SortingInfo(CreatedDateTimeColumn, true)
: settings.Sorting;
var query = new SqlPagedQuery(settings)
{
From = SelectSalesTransactions,
OrderBy = sorting.ToString(),
DatabaseSchema = string.Empty,
Aliased = true
};
query.Parameters["@customerName"] = ComposeCustomerNameForSearch(this.request.SearchCriteria);
var whereClauses = new List <string>();
this.BuildSearchOrderWhereClause(this.request.SearchCriteria, query, whereClauses);
query.Where = string.Join(" AND ", whereClauses);
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
{
return(databaseContext.ReadEntity <SalesOrder>(query));
}
}
/// <summary>
/// Executes the database operation for the unit of measurement options search.
/// NOTE: For variant products use the Master Products RECID.
/// </summary>
/// <returns>Returns the unit of measurement options search result.</returns>
public GetProductUnitOfMeasureOptionsDataResponse Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
using (TempTable unitOfMeasureOptionsProductsTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", this.request.ProductIds))
{
const string UnitOfMeasureOptionsQueryText = @"
SELECT tvppi.RECID AS PRODUCT,
it.ITEMID AS ITEMID,
uomc.[FROMUNITOFMEASURE] AS FROMUNITID,
uom1.[SYMBOL] AS FROMUOMSYMBOL,
uomc.[TOUNITOFMEASURE] AS TOUNITID,
uom2.[SYMBOL] AS TOUOMSYMBOL
FROM ax.UNITOFMEASURECONVERSION uomc
INNER JOIN ax.UNITOFMEASURE AS uom1 ON uom1.[RECID] = uomc.[FROMUNITOFMEASURE]
INNER JOIN ax.UNITOFMEASURE AS uom2 ON uom2.[RECID] = uomc.[TOUNITOFMEASURE]
INNER JOIN ax.INVENTTABLE AS it ON (it.[PRODUCT] = uomc.PRODUCT AND it.[DATAAREAID] = @nvc_DataAreaId) OR uomc.[PRODUCT] = 0
INNER JOIN {0} AS tvppi ON tvppi.[RECID] = it.[PRODUCT]
INNER JOIN ax.INVENTTABLEMODULE AS itm ON (itm.ITEMID = it.ITEMID AND itm.MODULETYPE = 2 AND itm.DATAAREAID = @nvc_DataAreaId) -- ModuleType = 2 is Sales
AND (uom1.[SYMBOL] = itm.UNITID OR uom2.[SYMBOL] = itm.UNITID)";
SqlQuery unitOfMeasureOptionsQuery = new SqlQuery(UnitOfMeasureOptionsQueryText, unitOfMeasureOptionsProductsTempTable.TableName);
unitOfMeasureOptionsQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
PagedResult <UnitOfMeasureConversion> unitOfMeasureConversions = context.ReadEntity <UnitOfMeasureConversion>(unitOfMeasureOptionsQuery);
return(new GetProductUnitOfMeasureOptionsDataResponse(unitOfMeasureConversions));
}
}
}
/// <summary>
/// Executes the database code to get unit of measure conversions.
/// </summary>
/// <returns>The response message.</returns>
public GetUnitOfMeasureConversionDataResponse Execute()
{
PagedResult <UnitOfMeasureConversion> result = null;
const string QueryCommand = @"
SELECT
u.[ITEMID] AS ITEMID,
u.[FROMUNITID] AS FROMUNITID,
u.[TOUNITID] AS TOUNITID,
u.[FROMUOMSYMBOL] AS FROMUOMSYMBOL,
u.[TOUOMSYMBOL] AS TOUOMSYMBOL,
u.[ISBACKWARD] AS ISBACKWARD,
uomc.[RECID] AS RECID,
uomc.[DENOMINATOR] AS DENOMINATOR,
uomc.[FACTOR] AS FACTOR,
uomc.[FROMUNITOFMEASURE] AS FROMUNITOFMEASURE,
uomc.[INNEROFFSET] AS INNEROFFSET,
uomc.[NUMERATOR] AS NUMERATOR,
uomc.[OUTEROFFSET] AS OUTEROFFSET,
uomc.[PRODUCT] AS PRODUCT,
uomc.[ROUNDING] AS ROUNDING,
uomc.[TOUNITOFMEASURE] AS TOUNITOFMEASURE
FROM
(
SELECT
iuc.ITEMID,
iuc.FROMUNITID,
iuc.TOUNITID,
uom_from.SYMBOL AS FROMUOMSYMBOL,
uom_to.SYMBOL AS TOUOMSYMBOL,
CASE
WHEN uomc1.RECID IS NOT NULL THEN 0
WHEN uomc2.RECID IS NOT NULL THEN 1
WHEN uomc3.RECID IS NOT NULL THEN 0
WHEN uomc4.RECID IS NOT NULL THEN 1
END ISBACKWARD,
CASE
WHEN uomc1.RECID IS NOT NULL THEN uomc1.RECID
WHEN uomc2.RECID IS NOT NULL THEN uomc2.RECID
WHEN uomc3.RECID IS NOT NULL THEN uomc3.RECID
WHEN uomc4.RECID IS NOT NULL THEN uomc4.RECID
END RECID
FROM {0} iuc
INNER JOIN [ax].UNITOFMEASURE uom_from ON uom_from.SYMBOL = iuc.FROMUNITID
INNER JOIN [ax].UNITOFMEASURE uom_to ON uom_to.SYMBOL = iuc.TOUNITID
LEFT JOIN [ax].INVENTTABLE it ON it.ITEMID = iuc.ITEMID AND it.DATAAREAID = @nvc_DataAreaId
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc1
ON uomc1.FROMUNITOFMEASURE = uom_from.RECID
AND uomc1.TOUNITOFMEASURE = uom_to.RECID
AND uomc1.PRODUCT = it.PRODUCT
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc2
ON uomc2.FROMUNITOFMEASURE = uom_to.RECID
AND uomc2.TOUNITOFMEASURE = uom_from.RECID
AND uomc2.PRODUCT = it.PRODUCT
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc3
ON uomc3.FROMUNITOFMEASURE = uom_from.RECID
AND uomc3.TOUNITOFMEASURE = uom_to.RECID
AND uomc3.PRODUCT = 0
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc4
ON uomc4.FROMUNITOFMEASURE = uom_to.RECID
AND uomc4.TOUNITOFMEASURE = uom_from.RECID
AND uomc4.PRODUCT = 0
) U
INNER JOIN [ax].UNITOFMEASURECONVERSION uomc ON uomc.RECID = u.RECID
";
using (DataTable itemUnitConversionsTable = new DataTable("tvp_ItemUnitConversions"))
{
itemUnitConversionsTable.Columns.Add(ItemIdColumnName, typeof(string));
itemUnitConversionsTable.Columns.Add(FromUnitIdColumnName, typeof(string));
itemUnitConversionsTable.Columns.Add(ToUnitIdColumnName, typeof(string));
foreach (ItemUnitConversion itemUnitConversion in this.request.ItemUnitConversions)
{
DataRow row = itemUnitConversionsTable.NewRow();
itemUnitConversionsTable.Rows.Add(row);
row[ItemIdColumnName] = itemUnitConversion.ItemId;
row[FromUnitIdColumnName] = itemUnitConversion.FromUnitOfMeasure;
row[ToUnitIdColumnName] = itemUnitConversion.ToUnitOfMeasure;
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (var itemUnitConvertionsTempTable = databaseContext.CreateTemporaryTable(itemUnitConversionsTable))
{
var sqlQuery = new SqlQuery(QueryCommand, itemUnitConvertionsTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = databaseContext.DataAreaId;
result = databaseContext.ReadEntity <UnitOfMeasureConversion>(sqlQuery);
}
}
return(new GetUnitOfMeasureConversionDataResponse(result));
}
/// <summary>
/// Executes the stored procedure.
/// </summary>
/// <returns>The data response.</returns>
public PagedResult <ProductBehavior> Execute()
{
if (!this.request.ProductIds.Any())
{
return(new PagedResult <ProductBehavior>(new List <ProductBehavior>().AsReadOnly()));
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType type = new RecordIdTableType(this.request.ProductIds, ProductIdsVariableName))
{
string query = @"
-- Retrieve behvaviors for variant id = 0 products.
SELECT DISTINCT
CASE
WHEN [siv].PRODUCTID IS NULL THEN 0
ELSE 1
END AS HASSERIALNUMBER,
CASE [rit].PROHIBITRETURN_RU
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISRETURNALLOWED,
[rit].BLOCKEDONPOS AS ISSALEATPHYSICALSTORESALLOWED,
[rit].QTYBECOMESNEGATIVE AS ISNEGATIVEQUANTITYALLOWED,
CASE [rit].NODISCOUNTALLOWED
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISDISCOUNTALLOWED,
[rk].DISASSEMBLYATREGISTERALLOWED AS ISKITDISASSEMBLYALLOWED,
[rit].ZEROPRICEVALID AS ISZEROSALEPRICEALLOWED,
[rit].KEYINGINPRICE AS KEYINPRICE,
[rit].KEYINGINQTY AS KEYINQUANTITY,
[rit].MUSTKEYINCOMMENT AS MUSTKEYINCOMMENT,
[rit].PRINTVARIANTSSHELFLABELS AS MUSTPRINTINDIVIDUALSHELFLABELSFORVARIANTS,
[siv].ISSALESPROCESSACTIVATED AS MUSTPROMPTFORSERIALNUMBERONLYATSALE,
[rit].SCALEITEM AS MUSTWEIGHPRODUCTATSALE,
CASE
WHEN [par].VARIANTID != 0 AND [par].VARIANTID = [erdpv].RECID THEN [par].VARIANTID
WHEN [par].VARIANTID = 0 AND [par].PRODUCTID = [erdpv].PRODUCTMASTER THEN [par].PRODUCTID
ELSE [par].PRODUCTID
END AS PRODUCTID,
[rit].DATETOACTIVATEITEM AS VALIDFROMDATEFORSALEATPHYSICALSTORES,
[rit].DATETOBEBLOCKED AS VALIDTODATEFORSALEATPHYSICALSTORES
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].PRODUCTID = [ids].RECID AND [par].VARIANTID = 0 AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].RETAILCHANNELTABLE rct ON [rct].RECID = [par].CHANNELID
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID AND [rct].INVENTLOCATIONDATAAREAID = [it].DATAAREAID
INNER JOIN [ax].RETAILINVENTTABLE rit ON [rit].ITEMID = [it].ITEMID AND [rit].DATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [crt].SERIALIZEDITEMSVIEW siv ON [siv].PRODUCTID = [par].PRODUCTID AND [siv].ITEMDATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID AND [par].VARIANTID = 0
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT erdpv ON [erdpv].RECID = [par].VARIANTID
UNION ALL
-- Retrieve behvaviors for variant id != 0 products.
SELECT DISTINCT
CASE
WHEN [siv].PRODUCTID IS NULL THEN 0
ELSE 1
END AS HASSERIALNUMBER,
CASE [rit].PROHIBITRETURN_RU
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISRETURNALLOWED,
[rit].BLOCKEDONPOS AS ISSALEATPHYSICALSTORESALLOWED,
[rit].QTYBECOMESNEGATIVE AS ISNEGATIVEQUANTITYALLOWED,
CASE [rit].NODISCOUNTALLOWED
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISDISCOUNTALLOWED,
[rk].DISASSEMBLYATREGISTERALLOWED AS ISKITDISASSEMBLYALLOWED,
[rit].ZEROPRICEVALID AS ISZEROSALEPRICEALLOWED,
[rit].KEYINGINPRICE AS KEYINPRICE,
[rit].KEYINGINQTY AS KEYINQUANTITY,
[rit].MUSTKEYINCOMMENT AS MUSTKEYINCOMMENT,
[rit].PRINTVARIANTSSHELFLABELS AS MUSTPRINTINDIVIDUALSHELFLABELSFORVARIANTS,
[siv].ISSALESPROCESSACTIVATED AS MUSTPROMPTFORSERIALNUMBERONLYATSALE,
[rit].SCALEITEM AS MUSTWEIGHPRODUCTATSALE,
CASE
WHEN [par].VARIANTID != 0 AND [par].VARIANTID = [erdpv].RECID THEN [par].VARIANTID
WHEN [par].VARIANTID = 0 AND [par].PRODUCTID = [erdpv].PRODUCTMASTER THEN [par].PRODUCTID
ELSE [par].PRODUCTID
END AS PRODUCTID,
[rit].DATETOACTIVATEITEM AS VALIDFROMDATEFORSALEATPHYSICALSTORES,
[rit].DATETOBEBLOCKED AS VALIDTODATEFORSALEATPHYSICALSTORES
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].VARIANTID = [ids].RECID AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].RETAILCHANNELTABLE rct ON [rct].RECID = [par].CHANNELID
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID AND [rct].INVENTLOCATIONDATAAREAID = [it].DATAAREAID
INNER JOIN [ax].RETAILINVENTTABLE rit ON [rit].ITEMID = [it].ITEMID AND [rit].DATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [crt].SERIALIZEDITEMSVIEW siv ON [siv].PRODUCTID = [par].PRODUCTID AND [siv].ITEMDATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID AND [par].VARIANTID = 0
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT erdpv ON [erdpv].RECID = [par].VARIANTID";
long currentChannelId = this.request.RequestContext.GetPrincipal().ChannelId;
SqlQuery sqlQuery = new SqlQuery(query, type.DataTable);
sqlQuery.Parameters[DatabaseAccessor.ChannelIdVariableName] = currentChannelId;
sqlQuery.Parameters[DatabaseAccessor.ChannelDateVariableName] = this.request.RequestContext.GetNowInChannelTimeZone().DateTime;
sqlQuery.Parameters[ProductIdsVariableName] = type.DataTable;
if (this.request.DownloadedProductsFilter.HasValue)
{
if (this.request.DownloadedProductsFilter.Value)
{
sqlQuery.Parameters[IsRemoteVariableName] = 1;
}
else
{
sqlQuery.Parameters[IsRemoteVariableName] = 0;
}
}
return(databaseContext.ReadEntity <ProductBehavior>(sqlQuery));
}
}
/// <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 price trade agreements.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemIds">The item identifiers.</param>
/// <param name="priceGroups">The price group identifiers.</param>
/// <param name="customerAccount">The customer account.</param>
/// <param name="fromDate">From date time of the price trade agreement.</param>
/// <param name="toDate">To date time of the price trade agreement.</param>
/// <param name="currencyCode">The currency code.</param>
/// <returns>Returns the read only collection of trade agreement.</returns>
public static PagedResult <TradeAgreement> GetPriceTradeAgreements(
SqliteDatabaseContext context,
IEnumerable <string> itemIds,
ISet <string> priceGroups,
string customerAccount,
DateTimeOffset fromDate,
DateTimeOffset toDate,
string currencyCode)
{
const string PriceTradeAgreementQueryText = @"SELECT
ta.RECID,
ta.ITEMCODE,
ta.ACCOUNTCODE,
ta.ITEMRELATION,
ta.ACCOUNTRELATION,
ta.QUANTITYAMOUNTFROM,
ta.QUANTITYAMOUNTTO,
ta.FROMDATE,
ta.TODATE,
ta.AMOUNT,
ta.CURRENCY,
ta.PERCENT1,
ta.PERCENT2,
ta.SEARCHAGAIN,
ta.PRICEUNIT,
ta.RELATION,
ta.UNITID,
ta.MARKUP,
ta.ALLOCATEMARKUP,
ta.INVENTDIMID,
ta.MAXIMUMRETAILPRICE_IN AS MAXIMUMRETAILPRICEINDIA,
invdim.CONFIGID,
invdim.INVENTCOLORID,
invdim.INVENTSIZEID,
invdim.INVENTSTYLEID
FROM [ax].PRICEDISCTABLE ta
INNER JOIN [ax].RETAILCHANNELTABLE AS c
ON c.INVENTLOCATIONDATAAREAID = ta.DATAAREAID AND c.RECID = @bi_ChannelId
LEFT JOIN [ax].INVENTDIM invdim ON ta.INVENTDIMID = invdim.INVENTDIMID AND invdim.DATAAREAID = c.INVENTLOCATIONDATAAREAID
WHERE
-- agreement is of price sales
ta.RELATION = 4
AND ta.CURRENCY = @nvc_CurrencyCode
-- and currently active
AND ((ta.FROMDATE <= @MinDate OR ta.FROMDATE <= @NoDate)
AND (ta.TODATE >= @MaxDate OR ta.TODATE <= @NoDate))
-- and customer/group relation matches
AND
(
-- account code is group and relation is in the price groups
((ta.ACCOUNTCODE = 1) AND
(ta.ACCOUNTRELATION IN (SELECT ar.ACCOUNTRELATION FROM {0} ar)))
OR
-- or account code is customer and customer is on the agreement
((ta.ACCOUNTCODE = 0) AND (ta.ACCOUNTRELATION = @Customer))
OR
-- or account code is ALL customers
((ta.ACCOUNTCODE = 2))
)
-- and item/group relation matches
AND
(
-- item code is one of the items passed in
((ta.ITEMCODE = 0) AND (ta.ITEMRELATION in (SELECT i.RECID FROM {1} i)))
)
-- and warehouse is either for all or for current channel
AND
(
invdim.INVENTLOCATIONID = '' OR (invdim.INVENTLOCATIONID = c.INVENTLOCATION)
)";
using (TempTable accountRelationTempTable = PricingTempTable.CreateAccountRelation(context, priceGroups))
using (TempTable itemIdTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", itemIds))
{
var priceTradeAgreementQuery = new SqlQuery(PriceTradeAgreementQueryText, accountRelationTempTable.TableName, itemIdTempTable.TableName);
priceTradeAgreementQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
priceTradeAgreementQuery.Parameters["@nvc_CurrencyCode"] = currencyCode;
priceTradeAgreementQuery.Parameters["@Customer"] = customerAccount;
priceTradeAgreementQuery.Parameters["@NoDate"] = new DateTime(1900, 01, 01).Date;
priceTradeAgreementQuery.Parameters["@MinDate"] = fromDate.Date;
priceTradeAgreementQuery.Parameters["@MaxDate"] = toDate.Date;
var tradeAgreements = context.ReadEntity <TradeAgreement>(priceTradeAgreementQuery);
return(tradeAgreements);
}
}
/// <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);
}
}
/// <summary>
/// Executes the stored procedure.
/// </summary>
/// <returns>The data response.</returns>
public PagedResult <Product> Execute()
{
if (!this.request.ProductIds.Any())
{
return(new PagedResult <Product>(new List <Product>().AsReadOnly()));
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType type = new RecordIdTableType(this.request.ProductIds, ProductIdsVariableName))
{
string query = @"
-- Retrieve variant id = 0 products.
SELECT DISTINCT
[itm].UNITID AS DEFAULTUNITOFMEASURE,
[erpt].[DESCRIPTION] AS [DESCRIPTION],
[it].ITEMID AS ITEMID,
[erpt].NAME AS NAME,
CASE
WHEN [pv].RECID IS NOT NULL THEN [par].VARIANTID
ELSE [par].PRODUCTID
END AS RECID,
CASE
WHEN [par].VARIANTID = 0 AND [rk].RECID IS NOT NULL THEN 1 -- Kit Master
WHEN [rk].RECID IS NOT NULL AND [pv].RECID IS NOT NULL THEN 2 -- Kit Variant
WHEN [pv].RECID IS NULL AND [pv2].RECID IS NOT NULL THEN 3 -- Master
WHEN [pv].RECID IS NOT NULL THEN 5 -- Variant
ELSE 4 -- Standalone
END AS PRODUCTTYPE
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].PRODUCTID = [ids].RECID AND [par].VARIANTID = 0 AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID
INNER JOIN [ax].INVENTTABLEMODULE itm ON [itm].ITEMID = [it].ITEMID AND [itm].DATAAREAID = [it].DATAAREAID AND [itm].MODULETYPE = 2 -- Sales
INNER JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON [erpt].PRODUCT = [par].PRODUCTID AND [erpt].LANGUAGEID = @nvc_Locale
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [par].VARIANTID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [par].PRODUCTID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID
UNION ALL
-- Retrieve variant id != 0 products.
SELECT DISTINCT
[itm].UNITID AS DEFAULTUNITOFMEASURE,
[erpt].[DESCRIPTION] AS [DESCRIPTION],
[it].ITEMID AS ITEMID,
[erpt].NAME AS NAME,
CASE
WHEN [pv].RECID IS NOT NULL THEN [par].VARIANTID
ELSE [par].PRODUCTID
END AS RECID,
CASE
WHEN [par].VARIANTID = 0 AND [rk].RECID IS NOT NULL THEN 1 -- Kit Master
WHEN [rk].RECID IS NOT NULL AND [pv].RECID IS NOT NULL THEN 2 -- Kit Variant
WHEN [pv].RECID IS NULL AND [pv2].RECID IS NOT NULL THEN 3 -- Master
WHEN [pv].RECID IS NOT NULL THEN 5 -- Variant
ELSE 4 -- Standalone
END AS PRODUCTTYPE
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].VARIANTID = [ids].RECID AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID
INNER JOIN [ax].INVENTTABLEMODULE itm ON [itm].ITEMID = [it].ITEMID AND [itm].DATAAREAID = [it].DATAAREAID AND [itm].MODULETYPE = 2 -- Sales
INNER JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON [erpt].PRODUCT = [par].PRODUCTID AND [erpt].LANGUAGEID = @nvc_Locale
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [par].VARIANTID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [par].PRODUCTID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID";
long currentChannelId = this.request.RequestContext.GetPrincipal().ChannelId;
SqlQuery sqlQuery = new SqlQuery(query, type.DataTable);
sqlQuery.Parameters[DatabaseAccessor.ChannelIdVariableName] = currentChannelId;
sqlQuery.Parameters[DatabaseAccessor.ChannelDateVariableName] = this.request.RequestContext.GetNowInChannelTimeZone().DateTime;
sqlQuery.Parameters[LocaleVariableName] = this.request.RequestContext.LanguageId;
sqlQuery.Parameters[ProductIdsVariableName] = type.DataTable;
if (this.request.DownloadedProductsFilter.HasValue)
{
if (this.request.DownloadedProductsFilter.Value)
{
sqlQuery.Parameters[IsRemoteVariableName] = 1;
}
else
{
sqlQuery.Parameters[IsRemoteVariableName] = 0;
}
}
return(databaseContext.ReadEntity <Product>(sqlQuery));
}
}