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