/// <summary>
/// Process input parameters and get any dependencies ready.
/// </summary>
/// <returns>Returns a list of <see cref="IDisposable"/> elements that are needed to be held until statement execution is finished.</returns>
private List <IDisposable> PrepareStatementDependencies()
{
// queries that provide DataTable as parameters are relying on the TVP-usage
// we need to convert data tables into temp tables
List <string> keysToRemove = new List <string>();
// need to keep trace of temp tables until statement is over
List <IDisposable> tempTables = new List <IDisposable>();
foreach (var parameter in this.Parameters)
{
TableType tableType = parameter.Value as TableType;
if (tableType != null)
{
TempTable tempTable = TempTable.CreateTemporaryTable(tableType.DataTable, this.Connection);
tempTables.Add(tempTable);
// can't remove key during enumeration, store them to remove later
keysToRemove.Add(parameter.Key);
// update command string to use actual temp table name, instead of parameter value
this.CommandString = this.CommandString.Replace(parameter.Key, tempTable.TableName);
}
}
foreach (string key in keysToRemove)
{
this.Parameters.Remove(key);
}
return(tempTables);
}
public static void ClearTempTable()
{
if (TempTable != null)
{
TempTable.TruncateData();
}
}
public void get_the_tostring_value_from_the_shortproperty()
{
// Arrange
MyMeta.ITable table = new TempTable()
{
Alias = "Customer"
};
MyMeta.IColumn c = new StringColumn(table)
{
Alias = "FirstName"
};
RequestContext context = new RequestContext();
context.ScriptSettings = _script;
context.Zeus = new TempZeusContext();
Condor.Core.Property prop = new BusinessObjectsPropertyRenderShortProperty(c, context);
// Act
var actual = prop.ToString();
var expected = "public string FirstName { get; set; }";
// Assert
Assert.AreEqual(expected, actual);
}
public void render_data_annotations_property()
{
// Arrange
MyMeta.ITable table = new TempTable()
{
Alias = "Customer"
};
MyMeta.IColumn c = new StringColumn(table)
{
Alias = "FirstName"
};
RequestContext context = new RequestContext();
context.ScriptSettings = _script;
context.Zeus = new TempZeusContext();
context.Utility = new CommonUtility();
Condor.Core.Property prop = new BusinessObjectsPropertyRenderDataAnnotations(c, context);
// Act
prop.Render();
var expected = "[Required(ErrorMessage = \"First Name is required.\")]\r\n";
expected += "[StringLength(50, ErrorMessage=\"First Name must be between 1 and 50 characters.\")]\r\n";
expected += "[DisplayName(\"First Name\")]\r\npublic string FirstName { get; set; }\r\n\r\n";
// Assert
Assert.AreEqual(expected, context.Zeus.Output.text);
}
public void short_property_class_returns_proper_result()
{
// Arrange
MyMeta.ITable table = new TempTable()
{
Alias = "Customer"
};
MyMeta.IColumn c = new StringColumn(table)
{
Alias = "FirstName"
};
RequestContext context = new RequestContext();
context.ScriptSettings = _script;
context.Zeus = new TempZeusContext();
Condor.Core.Property prop = new BusinessObjectsPropertyRenderShortProperty(c, context);
// Act
prop.Render();
var actual = context.Zeus.Output.text;
var expected = "public string FirstName { get; set; }\r\n";
// Assert
Assert.AreEqual(expected, actual);
}
/// <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;
}
public EntityDataServiceResponse <LoyaltyRewardPointLine> Execute()
{
string queryTemplate =
@"SELECT
RTLRPT.TRANSACTIONID AS TRANSACTIONID,
RTLRPT.LINENUM AS LINENUM,
RTLRPT.AFFILIATION AS AFFILIATION,
RTLRPT.LOYALTYTIER AS LOYALTYTIER,
RTLRPT.CARDNUMBER AS CARDNUMBER,
RTLRPT.CUSTACCOUNT AS CUSTACCOUNT,
RTLRPT.ENTRYDATE AS ENTRYDATE,
RTLRPT.ENTRYTIME AS ENTRYTIME,
RTLRPT.ENTRYTYPE AS ENTRYTYPE,
RTLRPT.EXPIRATIONDATE AS EXPIRATIONDATE,
RTLRPT.REWARDPOINT AS REWARDPOINT,
RTLRPT.REWARDPOINTAMOUNTQTY AS REWARDPOINTAMOUNTQTY,
RLRP.REWARDPOINTID AS REWARDPOINTID,
RLRP.REWARDPOINTTYPE AS REWARDPOINTTYPE,
RLRP.REWARDPOINTCURRENCY AS REWARDPOINTCURRENCY,
RLRP.REDEEMABLE AS REWARDPOINTREDEEMABLE
FROM {0} AS TT
CROSS JOIN ax_RETAILTRANSACTIONLOYALTYREWARDPOINTTRANS AS RTLRPT ON RTLRPT.TRANSACTIONID = TT.TRANSACTIONID
INNER JOIN ax_RETAILLOYALTYREWARDPOINT AS RLRP ON RLRP.RECID = RTLRPT.REWARDPOINT;";
using (TempTable transactionIdTempTable = TempTableHelper.CreateScalarTempTable(this.databaseContext, RetailTransactionTableSchema.TransactionIdColumn, this.request.Criteria.TransactionIds))
{
SqlQuery query = new SqlQuery(string.Format(queryTemplate, transactionIdTempTable.TableName));
PagedResult <LoyaltyRewardPointLine> loyaltyRewardPointLines = this.databaseContext.ReadEntity <LoyaltyRewardPointLine>(query);
return(new EntityDataServiceResponse <LoyaltyRewardPointLine>(loyaltyRewardPointLines));
}
}
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);
}
}
}
public static void DropTempTable()
{
if (TempTable != null)
{
TempTable.Drop();
}
}
protected void UploadIdVisitsToDataBase()
{
lock (lockObject)
{
if (visits.Count == 0)
{
return;
}
using (var db = dbFactory.CreateDb())
{
var vss = visits.Select(x => new VisitsById {
Id = x.Key, Visits = x.Value
});
db.BeginTransaction();
using (TempTable <VisitsById> visitsByIdTempTable = new TempTable <VisitsById>(db, vss))
{
db.Materials.Where(x => visitsByIdTempTable.Any(y => y.Id == x.Id))
.Set(x => x.VisitsCount,
x => x.VisitsCount + visitsByIdTempTable.FirstOrDefault(y => y.Id == x.Id).Visits)
.Update();
visits.Clear();
}
db.CommitTransaction();
}
}
}
/// <summary>
/// Creates a temporary table in the database and copy any rows present in <see cref="DataTable.Rows"/> to the temporary table.
/// </summary>
/// <param name="table">The table definition for the temporary table.</param>
/// <param name="connection">The database connection in which this table should be created.</param>
/// <returns>An instance of the temporary table.</returns>
public static TempTable CreateTemporaryTable(DataTable table, IDatabaseConnection connection)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (string.IsNullOrWhiteSpace(table.TableName))
{
throw new ArgumentException("table.TableName must be provided.", "table");
}
if (table.Columns.Count == 0)
{
throw new ArgumentException("table.Columns must not be empty.", "table");
}
// creates instance of temp table object
TempTable tempTable = new TempTable(table, connection);
// materialize the temp table in the database
tempTable.ExecuteCreateTable();
// populate it with any input data
tempTable.ExecutePopulateTable();
return(tempTable);
}
/// <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);
}
protected void UploadIdVisitsToDataBase()
{
lock (lockObject)
{
if (visits.Count == 0)
{
return;
}
using var db = dbFactory.CreateDb();
var vss = visits.Select(x => new VisitsById {
Id = x.Key, Visits = x.Value
});
db.BeginTransaction();
db.DropTable <VisitsById>(throwExceptionIfNotExists: false);
using TempTable <VisitsById> visitsByIdTempTable = new TempTable <VisitsById>(db, vss);
db.Users.Where(x => visitsByIdTempTable.Any(y => y.Id == x.Id))
.Set(x => x.ProfileVisitsCount,
x => x.ProfileVisitsCount +
visitsByIdTempTable.FirstOrDefault(y => y.Id == x.Id).Visits)
.Update();
visits.Clear();
db.CommitTransaction();
}
}
public IHttpActionResult PutTempTable(int id, TempTable tempTable)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != tempTable.Id)
{
return(BadRequest());
}
db.Entry(tempTable).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!TempTableExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
/// <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 void create_simple_property()
{
// Arrange
MyMeta.ITable table = new TempTable()
{
Alias = "Customer"
};
MyMeta.IColumn c = new StringColumn(table)
{
Alias = "FirstName"
};
RequestContext context = new RequestContext();
context.ScriptSettings = _script;
context.Zeus = new TempZeusContext();
context.Utility = new CommonUtility();
// Act
Condor.Core.Property prop = new RenderDataAnnotationsProperty(c, context);
prop.Render();
string expected = "public string FirstName { get; set; }\r\n";
// Assert
Assert.AreEqual(expected, context.Zeus.Output.text);
}
/// <summary>
/// Executes the procedure.
/// </summary>
/// <param name="itemVariantDimensions">The collection of product item variant dimensions.</param>
/// <returns>The collection of product variants.</returns>
public PagedResult <ProductVariant> Execute(IEnumerable <ItemVariantInventoryDimension> itemVariantDimensions)
{
using (ItemVariantInventoryDimensionTableType itemVariantTableTableType = new ItemVariantInventoryDimensionTableType(itemVariantDimensions))
using (TempTable itemVariantTempTable = this.context.CreateTemporaryTable(itemVariantTableTableType.DataTable))
{
return(this.Execute(itemVariantTempTable.TableName, FromItemAndInventoryDimensionId));
}
}
public EntityDataServiceResponse <SalesLine> Execute()
{
string queryTemplate =
@"SELECT
ST.TRANSACTIONID AS TRANSACTIONID,
ST.LINENUM AS LINENUM,
ST.ITEMID AS ITEMID,
ST.BARCODE AS BARCODE,
ST.INVENTSERIALID AS INVENTSERIALID,
(ST.QTY * -1.0) AS QUANTITY,
ST.PRICE AS PRICE,
ST.UNIT AS UNIT,
(ST.NETAMOUNT * -1.0) AS NETAMOUNT,
(ST.NETAMOUNTINCLTAX * -1.0) AS NETAMOUNTINCLTAX,
ST.TAXITEMGROUP AS ITEMTAXGROUPID,
ST.TAXGROUP AS SALESTAXGROUPID,
(ST.TAXAMOUNT * -1.0) AS TAXAMOUNT,
ST.DLVMODE AS DELIVERYMODE,
IDC.INVENTDIMID AS INVENTORYDIMENSIONID,
ST.INVENTLOCATIONID AS INVENTLOCATIONID,
ST.INVENTSITEID AS INVENTORYSITEID,
ST.LOGISTICSPOSTALADDRESS AS LOGISTICSPOSTALADDRESS,
(ST.RETURNQTY * -1.0) AS RETURNQTY,
ST.STORE AS STORE,
ST.TERMINALID AS TERMINALID,
ST.RETURNNOSALE AS RETURNNOSALE,
ST.RETURNTRANSACTIONID AS RETURNTRANSACTIONID,
ST.RETURNLINENUM AS RETURNLINENUM,
ST.RETURNSTORE AS RETURNSTORE,
ST.RETURNTERMINALID AS RETURNTERMINALID,
ST.DISCAMOUNT AS DISCOUNTAMOUNT,
ST.TOTALDISCAMOUNT AS TOTALDISCOUNTAMOUNT,
ST.TOTALDISCPCT AS TOTALDISCOUNTPERCENTAGE,
ST.LINEDSCAMOUNT AS LINEDISCOUNTAMOUNT,
ST.GIFTCARD AS GIFTCARD,
ST.COMMENT AS COMMENT,
ST.ORIGINALPRICE AS ORIGINALPRICE,
ST.PERIODICDISCAMOUNT AS PERIODICDISCOUNTAMOUNT,
ST.PERIODICPERCENTAGEDISCOUNT AS PERIODICPERCENTAGEDISCOUNT,
ST.LINEMANUALDISCOUNTAMOUNT AS LINEMANUALDISCOUNTAMOUNT,
ST.LINEMANUALDISCOUNTPERCENTAGE AS LINEMANUALDISCOUNTPERCENTAGE,
COALESCE(CAST(ST.LISTINGID AS BIGINT), IDC.DISTINCTPRODUCTVARIANT, IT.PRODUCT, 0) AS LISTINGID,
ST.TRANSACTIONSTATUS AS TRANSACTIONSTATUS,
(ST.TRANSDATE + ST.TRANSTIME) AS SALESDATETIME
FROM {0} AS TT
CROSS JOIN ax_RETAILTRANSACTIONSALESTRANS AS ST ON ST.TRANSACTIONID = TT.TRANSACTIONID
LEFT OUTER JOIN ax_INVENTTABLE AS IT ON ST.LISTINGID = '' AND IT.DATAAREAID = ST.DATAAREAID AND ST.VARIANTID = '' AND IT.ITEMID = ST.ITEMID
LEFT OUTER JOIN ax_INVENTDIMCOMBINATION AS IDC ON ST.ITEMID = IDC.ITEMID AND ST.VARIANTID <> '' AND ST.VARIANTID = IDC.RETAILVARIANTID AND ST.DATAAREAID = IDC.DATAAREAID
ORDER BY ST.LINENUM ASC;";
using (TempTable transactionIdTempTable = TempTableHelper.CreateScalarTempTable(this.databaseContext, RetailTransactionTableSchema.TransactionIdColumn, this.request.Criteria.TransactionIds))
{
SqlQuery query = new SqlQuery(string.Format(queryTemplate, transactionIdTempTable.TableName));
PagedResult <SalesLine> salesLines = this.databaseContext.ReadEntity <SalesLine>(query);
return(new EntityDataServiceResponse <SalesLine>(salesLines));
}
}
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));
}
}
public IHttpActionResult GetTempTable(int id)
{
TempTable tempTable = db.TempTables.Find(id);
if (tempTable == null)
{
return(NotFound());
}
return(Ok(tempTable));
}
public IHttpActionResult PostTempTable(TempTable tempTable)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
db.TempTables.Add(tempTable);
db.SaveChanges();
return(CreatedAtRoute("DefaultApi", new { id = tempTable.Id }, tempTable));
}
/// <summary>
/// Executes the get products by category procedure.
/// </summary>
/// <returns>The collection of product identifiers.</returns>
public ReadOnlyCollection <long> Execute()
{
var categoryIdsTableType = new RecordIdTableType(this.categoryIds);
using (TempTable tmpCategoryIds = this.context.CreateTemporaryTable(categoryIdsTableType.DataTable))
{
var query = new SqlQuery(QueryCommand, categoryIdsTableType.DataTable.TableName);
query.Parameters["@bi_ChannelId"] = this.channelId;
query.Parameters["@dt_ChannelDate"] = this.context.ChannelDateTimeNow;
return(this.context.ExecuteScalarCollection <long>(query));
}
}
public IHttpActionResult DeleteTempTable(int id)
{
TempTable tempTable = db.TempTables.Find(id);
if (tempTable == null)
{
return(NotFound());
}
db.TempTables.Remove(tempTable);
db.SaveChanges();
return(Ok(tempTable));
}
/// <summary>
/// Gets the validation periods of the identifiers.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="periodIds">The validation period identifiers.</param>
/// <returns>Returns the read only collection of validation period.</returns>
public static PagedResult <ValidationPeriod> GetValidationPeriodsByIds(SqliteDatabaseContext context, IEnumerable <string> periodIds)
{
using (TempTable priceGroupTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", periodIds))
{
const string ValidationPeriodQueryText = @"SELECT * FROM [crt].VALIDATIONPERIODVIEW vp
JOIN {0} period ON vp.PERIODID = period.RECID
WHERE vp.CHANNELID = @bi_ChannelId";
SqlQuery validationPeriodQuery = new SqlQuery(ValidationPeriodQueryText, priceGroupTempTable.TableName);
validationPeriodQuery.Parameters["@bi_ChannelId"] = context.ChannelId;
return(context.ReadEntity <ValidationPeriod>(validationPeriodQuery));
}
}
internal ProcessBlocksInfo(DBConnection conn, ProcessBlocksInfo processBlocksInfo, HDWallet wallet = null)
{
this.NewTip = null;
this.PrevTip = null;
this.MustCommit = false;
this.Conn = conn;
this.Wallet = wallet;
this.LockProcessBlocks = processBlocksInfo?.LockProcessBlocks ?? new DBLock();
this.Outputs = TempTable.Create <TempOutput>();
this.PrevOuts = TempTable.Create <TempPrevOut>();
this.ParticipatingWallets = new List <string>();
this.AddressesOfInterest = processBlocksInfo?.AddressesOfInterest ?? new WalletAddressLookup(conn, wallet?.WalletId);
this.TransactionsOfInterest = processBlocksInfo?.TransactionsOfInterest ?? new WalletTransactionLookup(conn, wallet?.WalletId);
}
private ReadOnlyCollection <LinkedProduct> Execute(TempTable productInputTempTable)
{
const string GetLinkedProductIdsQueryCommand = @"
SELECT LOOKUPID
FROM {0} ids
UNION
SELECT lit.PRODUCT as LOOKUPID
FROM {0} ids
INNER JOIN [ax].INVENTTABLE it ON it.PRODUCT = ids.LOOKUPID AND it.DATAAREAID = @nvc_DataAreaId
INNER JOIN [ax].RETAILINVENTLINKEDITEM rili ON rili.ITEMID = it.ITEMID and rili.DATAAREAID = it.DATAAREAID
INNER JOIN [ax].INVENTTABLE lit ON lit.ITEMID = rili.LINKEDITEMID AND lit.DATAAREAID = rili.DATAAREAID";
// get linked products ids
SqlQuery getLinkedProductIdsQuery = new SqlQuery(GetLinkedProductIdsQueryCommand, productInputTempTable.TableName);
getLinkedProductIdsQuery.Parameters["@nvc_DataAreaId"] = this.context.DataAreaId;
IEnumerable <long> linkedProductIds = this.context.ExecuteScalarCollection <long>(getLinkedProductIdsQuery);
// retrieve only assorted product ids from linked products
GetAssortedProductsProcedure getAssortedProductsProcedure = new GetAssortedProductsProcedure(
this.context,
this.channelId,
linkedProductIds,
expandVariants: false,
pagingSettings: PagingInfo.AllRecords);
const string GetLinkedProductDetailsQueryCommand = @"
SELECT DISTINCT
ap.PRODUCTID AS PRODUCT,
lit.PRODUCT AS LINKEDPRODUCT,
rili.QTY AS QTY
FROM {0} ap
INNER JOIN [ax].RETAILINVENTLINKEDITEM rili ON rili.ITEMID = ap.ITEMID AND rili.DATAAREAID = @nvc_DataAreaId
INNER JOIN [ax].INVENTTABLE lit ON lit.ITEMID = rili.LINKEDITEMID AND lit.DATAAREAID = rili.DATAAREAID";
ReadOnlyCollection <LinkedProduct> linkedProducts;
using (TempTable assortedLinkedProducts = getAssortedProductsProcedure.GetAssortedProducts())
{
SqlQuery getLinkedProductDetailsQuery = new SqlQuery(GetLinkedProductDetailsQueryCommand, assortedLinkedProducts.TableName);
getLinkedProductDetailsQuery.Parameters["@nvc_DataAreaId"] = this.context.DataAreaId;
linkedProducts = this.context.ReadEntity <LinkedProduct>(getLinkedProductDetailsQuery).Results;
}
return(linkedProducts);
}
/// <summary>
/// Tests the oracle.
/// </summary>
//[TestMethod]
public void TestOracle()
{
Gateway oracle = new Gateway(DatabaseType.Oracle, "Data Source=localhost;User ID=system;Password=sa;Unicode=True");
oracle.RegisterSqlLogger(new LogHandler(Console.WriteLine));
TempTable obj = new TempTable();
obj.ColName = "test";
obj.Guid = Guid.NewGuid();
//obj.ID = 998;
oracle.Save(obj);
Assert.IsTrue(obj.ID > 0);
oracle.Delete <TempTable>(TempTable._.ID == 998 & TempTable._.ColName == "test");
TempTable[] objs = oracle.GetPageSelector <TempTable>(WhereClip.All, OrderByClip.Default, 10).FindPage(1);
objs = oracle.GetPageSelector <TempTable>(WhereClip.All, OrderByClip.Default, 10).FindPage(2);
}
public async Task <JsonResult> OrderApprovedWithoutTable(int id, string Seat)
{
//HttpContext.Session.Remove("TempTableSeat");
var list = HttpContext.Session.Get <List <TempTable> >("TempTableSeat");
if (list == null)
{
list = new List <TempTable>();
TempTable tbl = new TempTable()
{
Id = id, SeatName = Seat
};
list.Add(tbl);
}
else
{
TempTable tbl = new TempTable()
{
Id = id, SeatName = Seat
};
list.Add(tbl);
}
HttpContext.Session.Set("TempTableSeat", list);
var Table = await _context.CustomerOrderDetails
.AsNoTracking().Where(s => s.CustomerOrderDetailsId == id)
.FirstOrDefaultAsync();
var CustomerOrderTables = await _context.CustomerOrderedTable.AsNoTracking().
Where(s => s.CustomerOrderedTableId == Table.CustomerOrderedTableId)
.FirstOrDefaultAsync();
var tb = await _context.Table.AsNoTracking()
.Where(s => s.TableId == CustomerOrderTables.TableId)
.FirstOrDefaultAsync();
tb.BookedStatus = false;
CustomerOrderTables.ConfirmStatus = true;
_context.Table.Update(tb);
await _context.SaveChangesAsync();
_context.CustomerOrderedTable.Update(CustomerOrderTables);
await _context.SaveChangesAsync();
return(Json(true));
}
/// <summary>
/// Gets the price group collection for the given list of price groups.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="priceGroups">The price group identifiers.</param>
/// <returns>Returns the read-only collection of price group.</returns>
public static ReadOnlyCollection <PriceGroup> GetPriceGroup(SqliteDatabaseContext context, IEnumerable <string> priceGroups)
{
using (TempTable priceGroupTempTable = PricingTempTable.CreatePriceGroup(context, priceGroups))
{
const string PriceGroupQueryText = @"SELECT (CASE(pa.PRICEGROUP)
WHEN 0
THEN pg.RECID
ELSE pa.PRICEGROUP
END) PRICEGROUP
FROM {0} AS pa
INNER JOIN [ax].PRICEDISCGROUP pg ON pg.GROUPID = pa.GROUPID AND DATAAREAID = @nvc_DataAreaId";
var priceGroupQuery = new SqlQuery(PriceGroupQueryText, priceGroupTempTable.TableName);
priceGroupQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ReadEntity <PriceGroup>(priceGroupQuery).Results);
}
}
public Tuple <ReadOnlyCollection <CountryRegionInfo>, ReadOnlyCollection <AddressFormattingInfo> > GetCountryRegions(string languageId)
{
using (var context = new SqliteDatabaseContext(this.Context))
{
var countryRegions = AddressQueries.GetCountryRegionIds(context, languageId);
IEnumerable <string> countryRegionIds = countryRegions.Results.Select(countryRegionId => countryRegionId.CountryRegionId);
using (TempTable countryRegionIdsTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", countryRegionIds))
{
ReadOnlyCollection <CountryRegionInfo> countryRegionInfo = AddressQueries.GetCountryRegionInfo(context, languageId, countryRegionIdsTempTable).Results;
ReadOnlyCollection <AddressFormattingInfo> formattingInfos = AddressQueries.GetAddressFormatInfo(context, countryRegionIdsTempTable).Results;
return(new Tuple <ReadOnlyCollection <CountryRegionInfo>, ReadOnlyCollection <AddressFormattingInfo> >(countryRegionInfo, formattingInfos));
}
}
}
