public override RuleResult Execute()
{
var result = new RuleResult {
Success = true
};
try
{
// This just shows how to get and modify the value of a detail group in the datastream.
// Get the Carrier Group for the first header
foreach (var carrierInfo in Data.XMLDatastream.GetGroup("CARRIERDEF", Data.XMLDatastream.GetHeaders().First()))
{
// Change the XCARRIER_NAME element
if (carrierInfo != null)
{
carrierInfo.Element("XCARRIER_NAME").Value = "Modified Carrier Name";
}
}
// Add a group to the header level
foreach (var hdr in Data.XMLDatastream.GetHeaders())
{
var hdrTestGroup = new XElement("HDRTSTXDEF",
new XElement("TEST_TEXT", "Header Test Text"),
new XElement("INVOICE_NO", hdr.Element("INVOICE_NUMBER")?.Value));
Data.XMLDatastream.AddGroup(hdrTestGroup, hdr);
}
// Add a group to the line level
foreach (var line in Data.XMLDatastream.GetLines())
{
var lineTestGroup = new XElement("LINETSTDEF",
new XElement("TEST_TEXT", "Line Test Text"),
new XElement("ITEM_ID", line.Element("INVOICE_LINE_ITEM_ID")?.Value));
Data.XMLDatastream.AddGroup(lineTestGroup, line);
}
// Save the datastream back to the original file.
Data.XMLDatastream.Document.Save(Data.XMLDatastream.FilePath);
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
Log.AddAndPersist(ex.Message);
}
return(result);
}
/// <summary>
/// Returns a DataTable that is populated with the invoice sort group for each invoice line.
/// </summary>
private DataTable GetInvoiceSortGroupData(string invoiceNo)
{
var dt = new DataTable();
try
{
// We can't use the P21SqlConnection for rules triggered on the 'Form Datastream Created' event
// since those are triggered from outside of P21. So we have to create our own connection with
// integrated security here.
var connectionString = new SqlConnectionStringBuilder
{
DataSource = Session.Server,
InitialCatalog = Session.Database,
UserID = Session.UserID,
IntegratedSecurity = true
}.ConnectionString;
using (var con = new SqlConnection(connectionString))
{
con.Open();
var sql = GetInvoiceSortGroupSql(invoiceNo);
using (var cmd = new SqlCommand(sql, con))
{
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
reader.Close();
}
}
}
}
catch (Exception ex)
{
Log.AddAndPersist($"Error retrieving invoice sort group info: {ex.Message}");
}
return(dt);
}
public override RuleResult Execute()
{
var result = new RuleResult {
Success = true
};
/*
* During print preview, there will be multiple forms in the same datastream file. In this case we're going
* to loop through each form since we're going to retrieve the user defined field per invoice. When physically
* printing this will just loop once since there will be only 1 form per file. You could also get all lines in
* the entire file using Data.XMLDatastream.GetLines().
*/
try
{
foreach (var form in Data.XMLDatastream.GetForms())
{
// Get the invoice number for the form and populate the DataTable
var invoiceNo = Data.XMLDatastream.GetHeader(form).Element("INVOICE_NUMBER")?.Value;
using (var invoiceSortGroupData = GetInvoiceSortGroupData(invoiceNo))
{
// Loop through each line for the form and get the invoice_sort_group for that item
foreach (var line in Data.XMLDatastream.GetLines(form))
{
var invoiceLineNo = line.Element("LINE_NUMBER")?.Value;
var invoiceSortGroup = 9999;
if (invoiceSortGroupData != null)
{
if (invoiceSortGroupData.Rows.Count > 0)
{
var invoiceSortLine = invoiceSortGroupData.Select($"invoice_no = '{invoiceNo}' AND line_no = {invoiceLineNo}");
if (invoiceSortLine.Length == 1)
{
invoiceSortGroup = Convert.ToInt32(invoiceSortLine[0]["invoice_sort_group"]);
}
}
}
// Add the user defined field for invoice_sort_group to the invoice lines.
line.Add(new XElement("INVOICE_SORT_GROUP", invoiceSortGroup));
}
}
}
// Sort all lines by the invoice_sort_group. By not passing the form as the first parameter, this will sort all
// forms by the given element. Also, pass true for the optional second parameter to indicate that we want to sort
// in numeric order rather than alphabetic. Not specifying the optional 3rd parameter for sorting descending.
Data.XMLDatastream.SortLines("INVOICE_SORT_GROUP", true);
// Save the datastream back to the original file.
Data.XMLDatastream.Document.Save(Data.XMLDatastream.FilePath);
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
Log.AddAndPersist(ex.Message);
}
return(result);
}