private ICollection <long> SearchByItemId(SqliteDatabaseContext context, IEnumerable <ProductLookupClause> itemIdLookupCollection)
{
const string Query = @"
-- Retrieve product identifiers for item identifiers.
SELECT it.PRODUCT AS RECID
FROM {0} ids
INNER JOIN [ax].INVENTTABLE it ON it.ITEMID = ids.ITEMID AND it.DATAAREAID = @nvc_DataAreaId
WHERE ids.INVENTDIMID = ''
UNION ALL
-- Retrieve variant identifiers for inventory dimensions.
SELECT idc.DISTINCTPRODUCTVARIANT AS RECID
FROM {0} ids
INNER JOIN [ax].INVENTDIMCOMBINATION idc ON idc.ITEMID = ids.ITEMID AND idc.INVENTDIMID = ids.INVENTDIMID AND idc.DATAAREAID = @nvc_DataAreaId
WHERE ids.INVENTDIMID != ''
";
using (var itemIdTable = new ItemIdSearchTableType(itemIdLookupCollection))
using (var itemIdTempTable = context.CreateTemporaryTable(itemIdTable.DataTable))
{
var sqlQuery = new SqlQuery(Query, itemIdTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ExecuteScalarCollection <long>(sqlQuery));
}
}
private IEnumerable <long> GetProductIdsFromItemIds(SqliteDatabaseContext databaseContext, IEnumerable <string> itemIds)
{
using (TempTable recidTable = TempTableHelper.CreateScalarTempTable(databaseContext, "RECID", itemIds))
{
string query = @"SELECT it.PRODUCT FROM {0} ids INNER JOIN [ax].INVENTTABLE it ON it.ITEMID = ids.RECID AND it.DATAAREAID = @DataAreaId";
var sqlQuery = new SqlQuery(query, recidTable.TableName);
sqlQuery.Parameters["@DataAreaId"] = databaseContext.DataAreaId;
return(databaseContext.ExecuteScalarCollection <long>(sqlQuery));
}
}
private GetOfflineTransactionIdsDataResponse GetOfflineTransactionIds(SqliteDatabaseContext databaseContext, int numberOfTransactions)
{
ReadOnlyCollection <string> transactionIds;
string queryString = string.Format("SELECT TRANSACTIONID FROM {0} LIMIT {1};", MainRetailTransactionTableName, NumberOfTransactionsParameter);
SqlQuery query = new SqlQuery(queryString);
query.Parameters.Add(NumberOfTransactionsParameter, numberOfTransactions);
transactionIds = databaseContext.ExecuteScalarCollection <string>(query);
GetOfflineTransactionIdsDataResponse response = new GetOfflineTransactionIdsDataResponse(new ReadOnlyCollection <string>(transactionIds));
return(response);
}
private IEnumerable <string> GetTablesInOfflineTransactionDatabase(SqliteDatabaseContext databaseContext)
{
const int IndexOfName = 1;
List <string> databaseList = new List <string>();
SqlQuery queryDatabaseList = new SqlQuery("PRAGMA database_list;");
try
{
using (var result = databaseContext.ExecuteQuery(queryDatabaseList))
{
while (result.Read())
{
string databaseName = result.GetValue <string>(IndexOfName);
if (string.Compare(databaseName, "MAIN", StringComparison.OrdinalIgnoreCase) != 0 &&
string.Compare(databaseName, "TEMP", StringComparison.OrdinalIgnoreCase) != 0)
{
databaseList.Add(databaseName);
}
}
}
}
catch (DatabaseException exception)
{
throw new StorageException(
StorageErrors.Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError,
(int)exception.ErrorCode,
exception,
"Failed to read from the database. See inner exception for details");
}
// There should be only 1 attached database associated with the connection. Test AssertSqliteDatabaseIntegrity checks this property.
string offlineTransactionDatabaseAlias = databaseList[0];
SqlQuery queryTableList = new SqlQuery(string.Format("SELECT NAME FROM [{0}].[SQLITE_MASTER] WHERE TYPE = 'table';", offlineTransactionDatabaseAlias));
ReadOnlyCollection <string> tableList = databaseContext.ExecuteScalarCollection <string>(queryTableList);
List <string> formalizedTableList = new List <string>();
foreach (string tableName in tableList)
{
formalizedTableList.Add(tableName.ToUpperInvariant());
}
return(formalizedTableList);
}
private IEnumerable <string> GetTablesInUploadSyncScopes(SqliteDatabaseContext databaseContext)
{
string queryString = string.Format(
@"SELECT t6.TABLENAME AS TABLENAME
FROM ax_RETAILSTORETABLE AS t1 INNER JOIN
ax_RETAILOFFLINEPROFILE AS t2 ON t1.STORENUMBER = '{0}' AND t1.OFFLINEPROFILE = t2.RECID INNER JOIN
ax_RETAILOFFLINEPROFILESCOPES AS t3 ON t2.RECID = t3.PROFILEID INNER JOIN
ax_RETAILOFFLINESCOPE AS t4 ON t4.RECID = t3.SCOPEID AND t4.SYNCDIRECTION = 1 INNER JOIN
ax_RETAILOFFLINESCOPETABLES AS t5 ON t4.RECID = t5.SCOPEID INNER JOIN
ax_RETAILOFFLINETABLE AS t6 ON t5.SYNCTABLEID = t6.RECID",
databaseContext.StoreNumber);
ReadOnlyCollection <string> tableList = databaseContext.ExecuteScalarCollection <string>(new SqlQuery(queryString));
List <string> result = new List <string>();
foreach (string tableName in tableList)
{
result.Add(tableName.Replace("[", string.Empty)
.Replace("]", string.Empty)
.Replace(".", "_"));
}
return(result);
}
private ICollection <long> SearchByKeyword(SqliteDatabaseContext context, string keyword, long channelId)
{
const string Query = @"
SELECT p.RECID
FROM [ax].ECORESPRODUCT p
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = p.RECID AND it.ITEMID LIKE @nvc_SearchCondition AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
SELECT EP.RECID
FROM [ax].ECORESPRODUCT EP
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 1 -- ProductNumber
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
WHERE ep.DISPLAYPRODUCTNUMBER LIKE @nvc_SearchCondition
UNION
SELECT EP.RECID
FROM [ax].ECORESPRODUCT EP
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID IN (2, 4) -- ProductName, ProductDescription
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON erpt.PRODUCT = ep.RECID
WHERE erpt.LANGUAGEID = @nvc_LanguageId AND (erpt.NAME LIKE @nvc_SearchCondition OR erpt.DESCRIPTION LIKE @nvc_SearchCondition)
UNION
SELECT EP.RECID
FROM [ax].ECORESPRODUCT EP
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 3 -- SearchName
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
WHERE EP.SEARCHNAME LIKE @nvc_SearchCondition
UNION
-- color
SELECT EPVDV.DISTINCTPRODUCTVARIANT AS RECID
FROM [ax].ECORESPRODUCTVARIANTCOLOR EPVC
JOIN [ax].ECORESPRODUCTVARIANTDIMENSIONVALUE EPVDV ON EPVDV.RECID = EPVC.RECID
JOIN [ax].ECORESCOLOR EC ON EC.RECID = EPVC.COLOR AND EC.NAME LIKE @nvc_SearchCondition
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 5 -- Color
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
UNION
-- style
SELECT EPVDV.DISTINCTPRODUCTVARIANT AS RECID
FROM [ax].ECORESPRODUCTVARIANTSTYLE EPVS
JOIN [ax].ECORESPRODUCTVARIANTDIMENSIONVALUE EPVDV ON EPVDV.RECID = EPVS.RECID
JOIN [ax].ECORESSTYLE EC ON EC.RECID = EPVS.STYLE AND EC.NAME LIKE @nvc_SearchCondition
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 6 -- Style
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
UNION
-- size
SELECT EPVDV.DISTINCTPRODUCTVARIANT AS RECID
FROM [ax].ECORESPRODUCTVARIANTSIZE EPVS
JOIN [ax].ECORESPRODUCTVARIANTDIMENSIONVALUE EPVDV ON EPVDV.RECID = EPVS.RECID
JOIN [ax].ECORESSIZE EC ON EC.RECID = EPVS.SIZE AND EC.NAME LIKE @nvc_SearchCondition
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 7 -- Size
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
UNION
-- configuration
SELECT EPVDV.DISTINCTPRODUCTVARIANT AS RECID
FROM [ax].ECORESPRODUCTVARIANTCONFIGURATION EPVC
JOIN [ax].ECORESPRODUCTVARIANTDIMENSIONVALUE EPVDV ON EPVDV.RECID = EPVC.RECID
JOIN [ax].ECORESCONFIGURATION EC ON EC.RECID = EPVC.CONFIGURATION AND EC.NAME LIKE @nvc_SearchCondition
JOIN [ax].RETAILSTANDARDATTRIBUTE RSA ON RSA.STANDARDATTRIBUTEID = 8 -- Configuration
JOIN [crt].PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW m ON m.ACTUALATTRIBUTE = rsa.ATTRIBUTE AND m.CHANNEL = @bi_ChannelId
";
SqlQuery sqlQuery = new SqlQuery(Query);
sqlQuery.Parameters["@bi_ChannelId"] = channelId;
sqlQuery.Parameters["@nvc_SearchCondition"] = "%" + keyword + "%";
sqlQuery.Parameters["@nvc_LanguageId"] = this.request.RequestContext.GetChannelConfiguration().DefaultLanguageId;
return(context.ExecuteScalarCollection <long>(sqlQuery));
}