/// <summary>
/// Initializes a new instance of the <see cref="GetProductAttributesProcedure"/> class.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="assortedProductsTempTable">The temporary table holding the assorted products used as input for the attribute retrieval.</param>
/// <param name="channelId">The channel id in which to execute the operation.</param>
/// <param name="languageId">The language in which the attribute will be.</param>
public GetProductAttributesProcedure(SqliteDatabaseContext context, TempTable assortedProductsTempTable, long channelId, string languageId)
{
this.context = context;
this.channelId = channelId;
this.languageId = languageId;
this.assortedProductsTempTable = assortedProductsTempTable;
}
/// <summary>
/// Delete the shift staging table.
/// </summary>
/// <param name="shift">The shift.</param>
public void DeleteShiftStaging(Shift shift)
{
using (var context = new SqliteDatabaseContext(this.Context))
{
ShiftsQueries.DeleteShift(context, shift);
}
}
/// <summary>
/// Fetch the superset of price trade agreements which could apply to all of these items and customer for the given date.
/// </summary>
/// <param name="itemIds">The item Ids to fetch for agreements for.</param>
/// <param name="priceGroups">The price groups (probably channel) to query by.</param>
/// <param name="customerAccount">Optional. Customer account number to search by.</param>
/// <param name="minActiveDate">The earliest inclusive active date to search by. Must be less than or equal to maxActiveDate.</param>
/// <param name="maxActiveDate">The latest inclusive active date to search by. Must be greater than or equal to minActiveDate.</param>
/// <param name="currencyCode">Currency code to filter by.</param>
/// <returns>Collection of trade agreements which may be applied to the given items.</returns>
public PagedResult <TradeAgreement> ReadPriceTradeAgreements(
IEnumerable <string> itemIds,
ISet <string> priceGroups,
string customerAccount,
DateTimeOffset minActiveDate,
DateTimeOffset maxActiveDate,
string currencyCode)
{
ThrowIf.Null(itemIds, "itemIds");
ThrowIf.Null(priceGroups, "priceGroups");
if (minActiveDate > maxActiveDate)
{
throw new ArgumentException("minActiveDate must be less than or equal to maxActiveDate.");
}
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.Context))
{
return(PricingProcedure.GetPriceTradeAgreements(
context,
itemIds,
priceGroups,
customerAccount,
minActiveDate,
maxActiveDate,
currencyCode));
}
}
/// <summary>
/// Fetch the superset of discount trade agreements which could apply to all of these items and customer for the given dates.
/// </summary>
/// <param name="itemIds">The item Ids to fetch for agreements for.</param>
/// <param name="customerAccount">Optional. Customer account number to search by.</param>
/// <param name="minActiveDate">The earliest inclusive active date to search by. Must be less than or equal to maxActiveDate.</param>
/// <param name="maxActiveDate">The latest inclusive active date to search by. Must be greater than or equal to minActiveDate.</param>
/// <param name="currencyCode">Currency code to filter by.</param>
/// <returns>Collection of trade agreements which may be applied to the given items.</returns>
public ReadOnlyCollection <TradeAgreement> ReadDiscountTradeAgreements(
IEnumerable <string> itemIds,
string customerAccount,
DateTimeOffset minActiveDate,
DateTimeOffset maxActiveDate,
string currencyCode)
{
ThrowIf.Null(itemIds, "itemIds");
if (minActiveDate > maxActiveDate)
{
throw new ArgumentException("minActiveDate must be less than or equal to maxActiveDate.");
}
DateTimeOffset minActiveDateChannelTimeZone = this.Context.ConvertDateTimeToChannelDate(minActiveDate);
DateTimeOffset maxActiveDateChannelTimeZone = this.Context.ConvertDateTimeToChannelDate(maxActiveDate);
using (var context = new SqliteDatabaseContext(this.Context))
{
return(DiscountProcedure.GetAllDiscountTradeAgreements(
context,
itemIds,
customerAccount,
minActiveDateChannelTimeZone,
maxActiveDateChannelTimeZone,
currencyCode));
}
}
/// <summary>
/// Creates the temp table for customer price groups.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="customer">The customer account.</param>
/// <returns>Returns the instance of customer price group temp table.</returns>
public static TempTable CreateCustomerPriceGroups(SqliteDatabaseContext context, string customer)
{
const string CustomerPriceGroupTempTableName = "CUSTOMERPRICEGROUPS";
// Create temp table for customer price groups.
var customerPriceGroupDataTable = new DataTable(CustomerPriceGroupTempTableName);
customerPriceGroupDataTable.Columns.Add("LINEDISC", typeof(string));
customerPriceGroupDataTable.Columns.Add("MULTILINEDISC", typeof(string));
customerPriceGroupDataTable.Columns.Add("ENDDISC", typeof(string));
var customerPriceGroupTempTable = context.CreateTemporaryTable(customerPriceGroupDataTable);
const string InsertQuery = @"INSERT INTO {0} (LINEDISC, MULTILINEDISC, ENDDISC)
SELECT LINEDISC, MULTILINEDISC, ENDDISC
FROM [ax].CUSTTABLE WHERE ACCOUNTNUM = @Customer AND DATAAREAID = @nvc_DataAreaId";
var sqlQuery = new SqlQuery(InsertQuery, customerPriceGroupTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
sqlQuery.Parameters["@Customer"] = customer;
context.ExecuteNonQuery(sqlQuery);
return(customerPriceGroupTempTable);
}
/// <summary>
/// Executes the database operation for product search.
/// </summary>
/// <returns>Returns the product search result.</returns>
public ProductSearchDataResponse Execute()
{
ProductSearchCriteria queryCriteria = this.request.QueryCriteria;
ICollection <long> productIds;
if (queryCriteria.Context.CatalogId != 0)
{
new FeatureNotSupportedException(FeatureNotSupportedErrors.Microsoft_Dynamics_Commerce_Runtime_RequestParametersNotSupported, "Search products using catalog is not supported for SQLite.");
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = queryCriteria.Context.ChannelId.GetValueOrDefault(databaseContext.ChannelId);
if (!string.IsNullOrWhiteSpace(queryCriteria.SearchCondition))
{
productIds = this.SearchByKeyword(databaseContext, queryCriteria.SearchCondition, channelId);
}
else if (queryCriteria.ItemIds.Any())
{
productIds = this.SearchByItemId(databaseContext, queryCriteria.ItemIds);
}
else if (queryCriteria.CategoryIds.Any())
{
productIds = this.SearchByCategoryId(databaseContext, queryCriteria.CategoryIds, channelId);
}
else
{
throw new FeatureNotSupportedException(FeatureNotSupportedErrors.Microsoft_Dynamics_Commerce_Runtime_RequestParametersNotSupported, "Supported product search conditions for SQLite are: SearchCondition, ItemIds, CategoryIds.");
}
}
return(new ProductSearchDataResponse(productIds));
}
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>
/// Updates the shift in shift staging table.
/// </summary>
/// <param name="context">The SQLite database context.</param>
/// <param name="shift">The shift object.</param>
public static void UpdateShift(SqliteDatabaseContext context, Shift shift)
{
const string UpdateQuery = @"UPDATE [crt].RETAILSHIFTSTAGINGTABLE
SET
STAFFID = @StaffId,
CURRENTSTAFFID = @CurrentStaffId,
[STATUS] = @Status,
CURRENTTERMINALID = @CurrentTerminalId,
STATUSDATETIMEUTC = @StatusDateTimeUTC,
CASHDRAWER = @CashDrawer
WHERE
[crt].RETAILSHIFTSTAGINGTABLE.CHANNEL = @ChannelId AND
[crt].RETAILSHIFTSTAGINGTABLE.TERMINALID = @TerminalId AND
[crt].RETAILSHIFTSTAGINGTABLE.SHIFTID = @ShiftId";
var sqlQuery = new SqlQuery(UpdateQuery);
sqlQuery.Parameters["@StaffId"] = shift.StaffId;
sqlQuery.Parameters["@CurrentStaffId"] = shift.CurrentStaffId;
sqlQuery.Parameters["@Status"] = shift.Status;
sqlQuery.Parameters["@CurrentTerminalId"] = shift.CurrentTerminalId;
sqlQuery.Parameters["@StatusDateTimeUTC"] = shift.StatusDateTime;
sqlQuery.Parameters["@CashDrawer"] = shift.CashDrawer;
sqlQuery.Parameters["@ChannelId"] = context.ChannelId;
sqlQuery.Parameters["@TerminalId"] = context.TerminalId;
sqlQuery.Parameters["@ShiftId"] = context.ShiftId;
context.ExecuteNonQuery(sqlQuery);
}
/// <summary>
/// Saves the transaction log.
/// </summary>
/// <param name="request">The request.</param>
/// <returns>The empty response.</returns>
private static NullResponse Save(InsertTransactionLogDataRequest request)
{
var allTables = new DataTable[] { request.TransactionLogTable };
request.TransactionLogTable.TableName = "ax.RETAILTRANSACTIONTABLE";
request.TransactionLogTable.Columns.Add("CREATEDOFFLINE", typeof(int));
foreach (DataRow row in request.TransactionLogTable.Rows)
{
row["CREATEDOFFLINE"] = 1; // set offline flag to true.
}
using (var databaseContext = new SqliteDatabaseContext(request.RequestContext))
using (var transaction = databaseContext.BeginTransaction())
{
foreach (DataTable dataTable in allTables)
{
databaseContext.SaveTable(dataTable);
}
transaction.Commit();
}
return(new NullResponse());
}
/// <summary>
/// Creates the temp table for multiline discount.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemIds">The item identifiers.</param>
/// <returns>Returns the instance of multiline discount temp table.</returns>
public static TempTable CreateMultiLineDiscount(SqliteDatabaseContext context, IEnumerable <string> itemIds)
{
const string ItemMultiLineDiscountGroups = "ItemMultilineDiscGroups";
// Create temp table for item line discount groups.
var itemMultiLineDiscountGroupsDataTable = new DataTable(ItemMultiLineDiscountGroups);
itemMultiLineDiscountGroupsDataTable.Columns.Add("MULTILINEDISC", typeof(string));
TempTable itemLineDiscountGroupsTempTable = context.CreateTemporaryTable(itemMultiLineDiscountGroupsDataTable);
using (TempTable items = TempTableHelper.CreateScalarTempTable(context, "RECID", itemIds))
{
const string InsertQuery = @"INSERT INTO {0}
(MULTILINEDISC)
SELECT DISTINCT it.MULTILINEDISC FROM [ax].[INVENTTABLEMODULE] it
INNER JOIN {1} i ON it.ITEMID = i.RECID
WHERE it.MODULETYPE = 2 AND it.DATAAREAID = @nvc_DataAreaId" ;
var sqlQuery = new SqlQuery(InsertQuery, itemLineDiscountGroupsTempTable.TableName, items.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
context.ExecuteNonQuery(sqlQuery);
}
return(itemLineDiscountGroupsTempTable);
}
private NullResponse PurgeOfflineTransactions(SqliteDatabaseContext databaseContext, IEnumerable <string> transactionIds)
{
using (var transaction = databaseContext.BeginTransaction())
{
string purgeMainTransactionTableStatement = string.Format("DELETE FROM {0} WHERE TRANSACTIONID = {1} COLLATE NOCASE;", MainRetailTransactionTableName, TransactionIdParameter);
foreach (string transactionId in transactionIds)
{
SqlQuery query = new SqlQuery(purgeMainTransactionTableStatement);
query.Parameters.Add(TransactionIdParameter, transactionId);
databaseContext.ExecuteNonQuery(query);
}
foreach (string tableName in this.GetSatelliteRetailTransactionTableList(databaseContext))
{
string purgeSatelliteTransactionTableStatement = string.Format("DELETE FROM {0} WHERE TRANSACTIONID = {1} COLLATE NOCASE;", tableName, TransactionIdParameter);
foreach (string transactionId in transactionIds)
{
SqlQuery query = new SqlQuery(purgeSatelliteTransactionTableStatement);
query.Parameters.Add(TransactionIdParameter, transactionId);
databaseContext.ExecuteNonQuery(query);
}
}
transaction.Commit();
}
return(new NullResponse());
}
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 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));
}
}
/// <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));
}
}
}
public IDbConnection CreateConnection()
{
if (_databaseKind == DatabaseKinds.SqlServer)
{
//return new SqlConnection(@"Data Source=akirapcwin7\sqlexpress;Initial Catalog=test;Integrated Security=True");
//return new SqlConnection(@"Data Source=akawawin8\sqlserver2016;Initial Catalog=cplan_demo;Integrated Security=True");
return(new SqlConnection(@"Data Source=localhost\sql2019;Initial Catalog=cplan_1000U;Integrated Security=True"));
}
else
{
IDbConnection connection;
if (_sqliteDatabaseContext == null)
{
_sqliteDatabaseContext = new SqliteDatabaseContext();
try
{
_sqliteDatabaseContext.Database.EnsureCreated();
}
catch
{
}
}
connection = new SqliteConnection(_sqliteDatabaseContext.GetConnectionString());
return(connection);
}
}
private Response GetShiftData(GetShiftDataDataRequest request)
{
using (SqliteDatabaseContext databaseContext = new SqliteDatabaseContext(request.RequestContext))
{
GetShiftDataProcedure procedure = new GetShiftDataProcedure(request.Criteria, databaseContext);
return(procedure.Execute());
}
}
/// <summary>
/// Initializes a new instance of the <see cref="GetAssortedProductsProcedure"/> class.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="channelId">The channel id in which to execute the request.</param>
/// <param name="productIds">The collection of product identifiers to check for assortment.</param>
/// <param name="expandVariants">Whether variants should not be expanded in case a product master / variant is included in the query.</param>
/// <param name="pagingSettings">The page settings configuration object.</param>
public GetAssortedProductsProcedure(SqliteDatabaseContext context, long channelId, IEnumerable <long> productIds, bool expandVariants, PagingInfo pagingSettings)
{
this.context = context;
this.productIds = productIds;
this.channelId = channelId;
this.expandVariants = expandVariants;
this.pageSettings = pagingSettings;
}
private static EntityDataServiceResponse <SalesLine> GetSalesLines(GetSalesLinesDataRequest request)
{
using (SqliteDatabaseContext databaseContext = new SqliteDatabaseContext(request.RequestContext))
{
GetSalesLinesProcedure getSalesLinesProcedure = new GetSalesLinesProcedure(request, databaseContext);
return(getSalesLinesProcedure.Execute());
}
}
/// <summary>
/// Gets the affiliation price groups.
/// </summary>
/// <param name="affiliationLoyaltyTiers">A collection of affiliation Id or loyalty tier Id.</param>
/// <returns>
/// A collection of affiliation price groups.
/// </returns>
public ReadOnlyCollection <PriceGroup> GetAffiliationPriceGroups(IEnumerable <AffiliationLoyaltyTier> affiliationLoyaltyTiers)
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.Context))
{
var affiliationPriceGroupCollection = PricingProcedure.GetAffiliationPriceGroups(context, affiliationLoyaltyTiers);
return(affiliationPriceGroupCollection.Results);
}
}
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));
}
}
protected AbstractModule(IHostApplicationLifetime hostApplicationLifetime, Logger logger, IDbContextFactory <SqliteDatabaseContext> contextFactory)
{
HostApplicationLifetime = hostApplicationLifetime;
SqliteDatabaseContext = contextFactory.CreateDbContext();
_logger = logger;
_requestOptions = new RequestOptions {
CancelToken = hostApplicationLifetime.ApplicationStopping
};
}
/// <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));
}
}
private TempTable GetAssortedProductsTable(SqliteDatabaseContext databaseContext, IEnumerable <long> productIds)
{
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
databaseContext,
databaseContext.ChannelId,
productIds,
expandVariants: true,
pagingSettings: this.request.QueryResultSettings.Paging);
return(assortedProductsProcedure.GetAssortedProducts());
}
public async Task Connect()
{
DiscordClient.MessageReceived += MessageReceived;
await DiscordClient.Connect(Config.BotKey);
IDatabaseContext dbContext = new SqliteDatabaseContext(new System.IO.FileInfo(Config.DatabasePath));
await dbContext.Initialize();
RateMonitor = new MessageRateMonitor(DiscordClient, dbContext);
await RateMonitor.LoadFromDb();
}
private TempTable GetAssortedProductsTable(SqliteDatabaseContext context, long channelId, bool skipVariantsExpansion)
{
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.Criteria.Ids,
!skipVariantsExpansion,
this.request.QueryResultSettings.Paging);
return(assortedProductsProcedure.GetAssortedProducts());
}
private static GetLinkedProductsDataResponse GetLinkedProducts(GetLinkedProductsDataRequest request)
{
ReadOnlyCollection <LinkedProduct> linkedProducts;
using (SqliteDatabaseContext context = new SqliteDatabaseContext(request.RequestContext))
{
GetLinkedProductsProcedure getLinkedProductsProcedure = new GetLinkedProductsProcedure(context, context.ChannelId, request.ProductIds);
linkedProducts = getLinkedProductsProcedure.Execute();
}
return(new GetLinkedProductsDataResponse(linkedProducts));
}
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));
}
private static EntityDataServiceResponse <ProductVariant> GetProductVariants(GetProductVariantsDataRequest request)
{
PagedResult <ProductVariant> variants;
using (SqliteDatabaseContext context = new SqliteDatabaseContext(request.RequestContext))
{
string languageId = request.RequestContext.GetChannelConfiguration().DefaultLanguageId;
GetProductVariantsProcedure getVariantsProcedure = new GetProductVariantsProcedure(context, languageId, request.QueryResultSettings);
variants = getVariantsProcedure.Execute(request.ItemAndInventoryDimensionIds);
}
return(new EntityDataServiceResponse <ProductVariant>(variants));
}
private XElement ReadTransactionTable(SqliteDatabaseContext databaseContext, string tableName, IEnumerable <string> transactionIds)
{
XElement table = new XElement(tableName);
XElement tableRow;
SqliteTableSchema tableSchemaInfo = databaseContext.GetTableSchemaInfo(tableName);
foreach (string transactionId in transactionIds)
{
// To prevent SQL injection, we need to use parameterized SQL as followed.
string queryTransactionStatement = string.Format("SELECT * FROM {0} WHERE TRANSACTIONID = {1} COLLATE NOCASE;", tableName, TransactionIdParameter);
SqlQuery query = new SqlQuery(queryTransactionStatement);
query.Parameters.Add(TransactionIdParameter, transactionId);
try
{
using (var resultSet = databaseContext.ExecuteQuery(query))
{
while (resultSet.Read())
{
List <XAttribute> attributes = new List <XAttribute>();
for (int i = 0; i < resultSet.FieldCount; ++i)
{
if (resultSet.GetValue <object>(i) == null)
{
attributes.Add(new XAttribute(resultSet.GetName(i).ToUpperInvariant(), string.Empty));
}
else
{
Type expectedManagedType = tableSchemaInfo.ColumnsByColumnName[resultSet.GetName(i).ToUpperInvariant()].ManagedType;
attributes.Add(new XAttribute(resultSet.GetName(i).ToUpperInvariant(), resultSet.GetValue(i, expectedManagedType)));
}
}
tableRow = new XElement("ROW", attributes);
table.Add(tableRow);
}
}
}
catch (DatabaseException ex)
{
throw new StorageException(
StorageErrors.Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError,
(int)ex.ErrorCode,
ex,
"Cannot read transaction data from transaction tables in the underlying SQLite database. See inner exception for details.");
}
}
return(table);
}
private void DoReadWork(SqliteDatabaseContext context, int index)
{
var items = context.From<Warrior>()
.Map(w => w.ID)
.Map(w => w.Name)
.Join<Armour>((a, w) => a.WarriorID == w.ID)
.Join<ArmourPart>((ap, a) => ap.ID == a.ArmourPartID)
.Join<Weapon, Warrior>((w, wa) => w.ID == wa.WeaponID)
.For<WarriorWithArmour>()
.Map<Weapon>(w => w.Name, wa => wa.WeaponName)
.Map<Armour>(a => a.Name, wa => wa.ArmourName)
.Select();
WriteLog(index, items.Count());
}
public UnitOfWork(string connectionString)
{
var connection = new SqliteContextProvider(connectionString);
_context = connection.Open();
}
public UnitOfWork(SqliteDatabaseContext context)
{
_context = context;
}
