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");
}
});
}