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()); }
/// <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> /// 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> /// 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); }
/// <summary> /// Inserts the shift into shift staging table. /// </summary> /// <param name="context">The SQLite database context.</param> /// <param name="shift">The shift object.</param> public static void InsertShift(SqliteDatabaseContext context, Shift shift) { const string InsertQuery = @"INSERT OR REPLACE INTO [crt].RETAILSHIFTSTAGINGTABLE ( CHANNEL ,STOREID ,TERMINALID ,SHIFTID ,STAFFID ,CURRENTSTAFFID ,[STATUS] ,CURRENTTERMINALID ,STARTDATETIMEUTC ,STATUSDATETIMEUTC ,DATAAREAID ,CASHDRAWER ) VALUES ( @ChannelId ,@StoreId ,@TerminalId ,@ShiftId ,@StaffId ,@CurrentStaffId ,@Status ,@CurrentTerminalId ,@StatusDateTimeUTC ,@StatusDateTimeUTC ,@DataAreaId ,@CashDrawer )"; var sqlQuery = new SqlQuery(InsertQuery); sqlQuery.Parameters["@ChannelId"] = context.ChannelId; sqlQuery.Parameters["@StoreId"] = shift.StoreId; sqlQuery.Parameters["@TerminalId"] = shift.TerminalId; sqlQuery.Parameters["@ShiftId"] = shift.ShiftId; sqlQuery.Parameters["@StaffId"] = shift.StaffId; sqlQuery.Parameters["@CurrentStaffId"] = shift.CurrentStaffId; sqlQuery.Parameters["@Status"] = shift.Status; sqlQuery.Parameters["@CurrentTerminalId"] = shift.CurrentTerminalId; sqlQuery.Parameters["@StatusDateTimeUTC"] = shift.StartDateTime; sqlQuery.Parameters["@DataAreaId"] = context.DataAreaId; sqlQuery.Parameters["@CashDrawer"] = shift.CashDrawer; context.ExecuteNonQuery(sqlQuery); }
/// <summary> /// Deletes 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 DeleteShift(SqliteDatabaseContext context, Shift shift) { const string DeleteQuery = @"DELETE FROM [crt].RETAILSHIFTSTAGINGTABLE WHERE [crt].RETAILSHIFTSTAGINGTABLE.CHANNEL = @ChannelId AND [crt].RETAILSHIFTSTAGINGTABLE.TERMINALID = @TerminalId AND [crt].RETAILSHIFTSTAGINGTABLE.SHIFTID = @ShiftId"; var sqlQuery = new SqlQuery(DeleteQuery); sqlQuery.Parameters["@ChannelId"] = context.ChannelId; sqlQuery.Parameters["@TerminalId"] = shift.TerminalId; sqlQuery.Parameters["@ShiftId"] = shift.ShiftId; context.ExecuteNonQuery(sqlQuery); }
/// <summary> /// Page the results based on the paging configuration. /// </summary> /// <param name="dbContext">The database context.</param> /// <param name="unpagedResultSet">The result set to be paginated.</param> /// <param name="pagingInfo">The paging info configuration object.</param> /// <returns>The paginated result set.</returns> private TempTable PageResults(SqliteDatabaseContext dbContext, TempTable unpagedResultSet, PagingInfo pagingInfo) { // create table definition ProductLookupTableType pagedResultTableDefinition = new ProductLookupTableType(this.TempAssortedProductsTableName); // and temp table to hold paged results TempTable pagedResultTempTable = dbContext.CreateTemporaryTable(pagedResultTableDefinition.DataTable); string[] columnNames = pagedResultTableDefinition.DataTable.Columns.Select(column => column.ColumnName).ToArray(); string selectColumns = string.Join(",", columnNames); // insert into paged result temp table, all records from the unpaged temp table based on unique lookup id const string PaginateResultsQueryCommand = @" INSERT INTO {1} ( {2} ) SELECT {2} FROM {0} UNPAGEDRESULT WHERE LOOKUPID IN ( SELECT DISTINCT LOOKUPID FROM {0} ORDER BY LOOKUPID ASC LIMIT @limitValue OFFSET @offsetValue );"; SqlQuery query = new SqlQuery(PaginateResultsQueryCommand, unpagedResultSet.TableName, pagedResultTempTable.TableName, selectColumns); query.Parameters["@limitValue"] = pagingInfo.Top; query.Parameters["@offsetValue"] = pagingInfo.Skip; // executes query dbContext.ExecuteNonQuery(query); return(pagedResultTempTable); }
/// <summary> /// Executes the procedure. /// </summary> public void Execute() { const string UpdateDeleteDateQueryCommand = @" UPDATE crt.SALESTRANSACTION SET DELETEDDATETIME = @deteleDateTime WHERE TRANSACTIONID = @transactionId;"; var query = new SqlQuery(UpdateDeleteDateQueryCommand); query.Parameters["@deteleDateTime"] = DateTime.UtcNow; using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext)) using (var transaction = databaseContext.BeginTransaction()) { foreach (string salesTransactionId in this.request.SalesTransactionIds) { query.Parameters["@transactionId"] = salesTransactionId; databaseContext.ExecuteNonQuery(query); } transaction.Commit(); } }
static async Task Main(string[] args) { await CommandLineParser.Default.ParseArguments <CommandLineOptions>(args).WithParsedAsync(async o => { var logger = new LoggerConfiguration().MinimumLevel.Is(o.LoggingLevel).WriteTo.Console(outputTemplate: "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level:u3}] {Message:lj}{NewLine}{Exception}", theme: AnsiConsoleTheme.Literate).CreateLogger(); try { var destinationDatabaseDirectory = !String.IsNullOrWhiteSpace(o.DirectoryForDatabase) ? o.DirectoryForDatabase ! : Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) !; var fullDestinationDatabasePath = Path.Combine(destinationDatabaseDirectory, "poe-stash-search.db"); var connectionString = $"Data Source={fullDestinationDatabasePath};Mode=ReadWriteCreate;Foreign Keys=True;Cache=Shared"; var dbContext = new SqliteDatabaseContext(connectionString, new ItemRepository(connectionString), new SettingsRepository(connectionString), new StatRepository(connectionString)); var rePoeParser = new RePoeParser(logger); logger.Information("Attempting to create and populate database {PathToDatabase}", fullDestinationDatabasePath); dbContext.EnsureDatabaseIsInitialized(); var baseItems = await rePoeParser.ParseBaseItemsAsync(o.PathToBaseItemsJson); var itemCategories = baseItems.Select(b => b.Category).Distinct(); var itemTags = baseItems.SelectMany(b => b.Tags).Distinct(); logger.Debug("Attempting to save item categories"); var savedItemCategoryCount = dbContext.Items.SaveItemCategories(itemCategories); logger.Debug("Successfully saved {ItemClassCount} item categories", savedItemCategoryCount); logger.Debug("Attempting to save item tags"); var savedItemTagCount = dbContext.Items.SaveItemTags(itemTags); logger.Debug("Successfully saved {ItemTagCount} item tags", savedItemTagCount); logger.Debug("Attempting to save item definitions"); var savedItemDefinitionCount = dbContext.Items.SaveItemDefinitions(baseItems); logger.Debug("Successfully saved {ItemDefinitionCount} item definitions", savedItemDefinitionCount); var clusterJewelStatDescriptions = await rePoeParser.ParseClusterJewelsAsync(o.PathToClusterJewelsJson); var statDescriptions = (await rePoeParser.ParseStatDescriptionsAsync(o.PathToStatTranslationsJson)).Distinct().ToList(); var statDescriptionItemTags = (await rePoeParser.ParseModsAsync(o.PathToModsJson)).Distinct().ToList(); var actuallyUsedStatDescriptions = statDescriptions.Where(s => statDescriptionItemTags.Any(sdit => s.TextId.Equals(sdit.StatDescriptionTextId))); logger.Debug("Attempting to save stat descriptions"); var savedStatDescriptionCount = dbContext.Stats.SaveStatDescriptions(actuallyUsedStatDescriptions); logger.Debug("Successfully saved {StatDescriptionCount} stat descriptions", savedStatDescriptionCount); logger.Debug("Attempting to save cluster jewel stat descriptions"); var savedClusterJewelStatDescriptions = dbContext.Stats.SaveClusterJewelStatDescriptions(clusterJewelStatDescriptions); logger.Debug("Successfully saved {ClusterJewelCount} cluster jewel stat descriptions", savedClusterJewelStatDescriptions); logger.Debug("Attempting to save stat description<->item tag relationships"); var savedStatDescriptionItemTagCount = dbContext.Stats.SaveItemTagsForStatDescriptions(statDescriptionItemTags); logger.Debug("Successfully saved {StatDescriptionItemTagCount} stat description<->item tag relationships", savedStatDescriptionItemTagCount); logger.Debug("Attempting to execute custom SQL"); logger.Verbose("Attempting to fix domain for map_doesnt_consume_sextant_use stat description<->item tag relationship(s)"); var mapDoesntConsumeSextantUseRowsAffected = dbContext.ExecuteNonQuery("UPDATE StatDescriptionItemTag SET Domain = 'area' WHERE StatDescriptionId IN (SELECT Id FROM StatDescription WHERE TextId = 'map_doesnt_consume_sextant_use');"); logger.Verbose("Successfully fixed domain for {MapDoesntConsumeSextantUseRowsAffected} map_doesnt_consume_sextant_use stat description<->item tag relationship(s)", mapDoesntConsumeSextantUseRowsAffected); logger.Verbose("Attempting to fix item category name(s)"); var itemCategoryNameRowsAffected = dbContext.ExecuteNonQuery("UPDATE ItemCategory SET Name = 'Abyss Jewel' WHERE Name = 'AbyssJewel'; UPDATE ItemCategory SET Name = 'Fishing Rod' WHERE Name = 'FishingRod';"); logger.Verbose("Successfully fixed {ItemCategoryNameRowsAffected} item category name(s)", itemCategoryNameRowsAffected); logger.Debug("Successfully executed custom SQL"); logger.Information("Successfully created and populated database {PathToDatabase}", fullDestinationDatabasePath); } catch (Exception ex) { logger.Fatal(ex, "Exception occurred while attempting to create and populate database"); } }); }