Sounds like you need to be able to access those custom properties in Sales Order as well. Sales Order consists of number of SQL tables, the table which corresponds to a Sales Line (which is originating from a Cart Line) is RetailTransactionSalesTrans so you need to extend that table by adding a column, let's say CustomString (for this example I will specify its type as NVARCHAR(50) but you can have any other type. Then you need to customize CRT (see below) to store data corresponding to the Extension Property in that table. Eventually, when CRT reads an order's sales lines it is using SQL View crt.RetailTransactionSalesTransView, therefore that view should also be updated by including into the SELECT statement just added column. Once the View is modified the CRT will automatically include that column into a collection of ExtensionProperties of the SalesLine and finally you will be able to use its value in Retail Server client (POS in your case) the way you want.
Below are more specific details:
1. In the Data.Services project find a class SalesTransactionDataService and modify its method
private static NullResponse SaveSalesTransaction(SaveSalesTransactionDataRequest request)
by adding one more DataTable around line #1412 (here we are instantiating a DataTable which will hold ExtensionProperties values):
using (DataTable linesExtensionPropertiesTable = new DataTable("RETAILTRANSACTIONPROPERTIESTABLETYPE"))
While in the same method, a little bit further you will see several calls to such functions like PopulateXSchema, so, add there one more line (this is a call to the method which will fill the table with the schema):
PopulateExtensionPropertiesSchema(linesExtensionPropertiesTable);
While in the same method locate a call to FillOrderLines and add there an additional parameter which corresponds to just created DataTable, so the call would like something like this (note the new parameter linesExtensionPropertiesTable):
FillOrderLines(
request.SalesTransaction,
linesTable,
incomeExpenseTable,
markupTable,
taxTable,
addressTable,
discountTable,
reasonCodeTable,
propertiesTable,
affiliationsTable,
invoiceTable,
customerAccountDepositTable,
linesExtensionPropertiesTable,
request.RequestContext);
Last change in this method, almost at the end of it, will be to insert the following line (this way we will be able to pass newly created DataTable to the layer which works with Sql Server):
insertTablesRequest.SetProperty("extensionProperties", linesExtensionPropertiesTable);
right before this one:
request.RequestContext.Runtime.Execute<NullResponse>(insertTablesRequest, request.RequestContext);
2. While in the same file locate the function
private static void FillOrderLines
and add an additional argument to it:
DataTable linesExtensionPropertiesTable
Then, in the same method, find a call to FillItemLine and pass there new parameter linesExtensionPropertiesTable which was just introduced.
3. Modify the function FillItemLine by adding a new parameter
DataTable linesExtensionPropertiesTable
Then, at the very end of the method add the following call:
FillExtensionProperties(transaction, salesLine, linesExtensionPropertiesTable, context);
4. While in the same file add couple of new function which were referenced by the changes above:
private static void PopulateExtensionPropertiesSchema(DataTable table)
{
ThrowIf.Null(table, nameof(table));
table.Columns.Add(DataAreaIdColumn, typeof(string)).MaxLength = 4;
table.Columns.Add(StoreColumn, typeof(string)).MaxLength = 10;
table.Columns.Add(TerminalIdColumn, typeof(string)).MaxLength = 10;
table.Columns.Add(TransactionIdColumn, typeof(string)).MaxLength = 44;
table.Columns.Add(SaleLineNumColumn, typeof(decimal));
table.Columns.Add(NameColumn, typeof(string)).MaxLength = 32;
table.Columns.Add(ValueColumn, typeof(string));
}
private static void FillExtensionProperties(SalesTransaction transaction, SalesLine salesLine, DataTable propertiesTable, RequestContext context)
{
ThrowIf.Null(transaction, nameof(transaction));
ThrowIf.Null(salesLine, nameof(salesLine));
ThrowIf.Null(propertiesTable, nameof(propertiesTable));
ChannelConfiguration channelConfiguration = context.GetChannelConfiguration();
foreach (CommerceProperty commerceProperty in salesLine.ExtensionProperties)
{
DataRow row = propertiesTable.NewRow();
SetField(row, DataAreaIdColumn, channelConfiguration.InventLocationDataAreaId);
SetField(row, StoreColumn, transaction.StoreId ?? string.Empty);
SetField(row, TerminalIdColumn, transaction.TerminalId ?? string.Empty);
SetField(row, TransactionIdColumn, transaction.Id);
SetField(row, SaleLineNumColumn, salesLine.LineNumber);
SetField(row, NameColumn, commerceProperty.Key);
SetField(row, ValueColumn, commerceProperty.Value.GetPropertyValue());
propertiesTable.Rows.Add(row);
}
}
This completes changes to the file SalesTransactionDataService.cs.
5. Find the file SalesTransactionSqlServerDataService.cs in the project Data.Services.SqlServer, it cntains the function
private static NullResponse InsertSalesTransactionTables(InsertSalesTransactionTablesDataRequest request)
which contains, at the very beginning, several similar lines where parameters are set, add there one more line:
parameters["@tvp_LinesExtensionProperties"] = request.GetProperty("extensionProperties");
This completes C# changes, what we have so far: we have modified a code so it now provides Extension Properties's values to the DB layer so in next steps we will need to modify couple of Stored Procs to leverage the new parameter added above.
6. Modify Stored Procedure crt.InsertSalesOrder by adding one more parameter:
@TVP_LINESEXTENSIONPROPERTIES [crt].[RETAILTRANSACTIONPROPERTIESTABLETYPE] READONLY
While in the same Stored Procedure, find a call to the Stored Procedure crt.InsertSalesTrans and add one more parameter to that call - @TVP_LINESEXTENSIONPROPERTIES
7. Modify Stored Procedure crt.InsertSalesTrans by adding new parameter there (the same parameter you added to the Stored Procedure above):
@TVP_LINESEXTENSIONPROPERTIES [crt].[RETAILTRANSACTIONPROPERTIESTABLETYPE] READONLY
This is basically it - we have passed the extension properties all the way down to the stored proc where it should be saved and now you need to update the SQL in the way which is the best for your specific needs (depends whether you have one custom property or more of them and whether al o them of the same type or not and so on), as a sample I am providing the following code which assumes that you have just 1 (per sales line) Extension Property which will be stored I the column CustomString NVARCHAR, but, gain, you should modify the SQL the way it fits your needs:
UPDATE [ax].RETAILTRANSACTIONSALESTRANS
SET CustomString = properties.VALUE
FROM [ax].RETAILTRANSACTIONSALESTRANS lines
JOIN @TVP_LINESEXTENSIONPROPERTIES AS properties ON properties.TRANSACTIONID = lines.TRANSACTIONID
AND properties.SALELINENUM = lines.LINENUM
AND properties.DATAAREAID = lines.DATAAREAID
AND lines.CHANNEL = @bi_ChannelId
SELECT @i_Error = @@ERROR;
IF @i_Error <> 0
BEGIN
SET @i_ReturnCode = @i_Error;
GOTO exit_label;
END;
