Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using PX.Data;
using System.Text;
using System.Xml;
using System.IO;
namespace PX.Objects.CR
{
#region PXDBSubSelectForXMLAttribute
//We will implement RowSelecting, CommandPreparing, FieldSelecting events
public class PXDBSubSelectForXMLAttribute : PXDBFieldAttribute, IPXRowSelectingSubscriber, IPXCommandPreparingSubscriber, IPXFieldSelectingSubscriber
{
//Constant for SQL Xml Command
protected const string FOR_XML = " FOR XML PATH('{0}')";
//Here we will store Sub Select command
protected BqlCommand Select;
//names of all fields form sub select command
protected String[] AllFields;
//positions of fields against full sub select comand
protected Dictionary<Int32, Int32> Positions = new Dictionary<int, int>();
//Here we will stire fields for selecting
public Type[] Columns { get; protected set; }
public String RowTag { get; set; } = "v";
public String RootTag { get; set; } = "r";
//Format of the result value.
//Example: DisplayFormat="Contact ID = {Contact.contactID}, Test CD = {UNContactTest.testCD}, Passed = {UNContactTest.passed}"
//Fields are case sensetive
public String DisplayFormat { get; set; }
public Boolean DisplayAppendColumns { get; set; }
public Boolean DisplayInsertNulls { get; set; }
#region Ctor
public PXDBSubSelectForXMLAttribute(Type select, params Type[] columns)
{
//sabing BQL command and fields to select
Select = BqlCommand.CreateInstance(select);
Columns = columns;
}
//Here we will generate "For XML" parameter for query
public virtual string GetForXML()
{
String result = String.Format(FOR_XML, RowTag);
//if (!String.IsNullOrEmpty(RootTag)) result += String.Format(", ROOT('{0}')", RootTag);
return result;
}
#endregion
#region Implementation
public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
//Do nothing if it is not select
if (!e.IsSelect()) return;
//Result data will be XML
e.DataType = PXDbType.Xml;
//Checking operation
PXDBOperation eOp = e.Operation & PXDBOperation.Option;
bool operationAllowsSelectForXml = eOp == PXDBOperation.External || eOp == PXDBOperation.Internal || eOp == PXDBOperation.Normal && e.Value == null;
//Getting name of the table that should be used for subselect
Type tableToUse = eOp == PXDBOperation.External ? sender.GetItemType() : e.Table ?? _BqlTable;
if (!_BqlTable.IsAssignableFrom(sender.BqlTable))
{
if (sender.Graph.Caches[_BqlTable].BqlSelect != null && operationAllowsSelectForXml)
{
e.BqlTable = _BqlTable;
e.FieldName = ((e.Operation & PXDBOperation.Option) == PXDBOperation.External ? sender.GetItemType().Name : _BqlTable.Name) + '.' + _DatabaseFieldName;
}
else
{
PXCommandPreparingEventArgs.FieldDescription description;
sender.Graph.Caches[_BqlTable].RaiseCommandPreparing(_DatabaseFieldName, e.Row, e.Value, e.Operation, e.Table, out description);
if (description != null)
{
e.DataType = description.DataType;
e.DataValue = description.DataValue;
e.BqlTable = _BqlTable;
e.FieldName = description.FieldName;
}
}
}
else
{
if (operationAllowsSelectForXml && (e.Operation & PXDBOperation.Option) != PXDBOperation.External)
{
List<Type> types = new List<Type>() { tableToUse };
ISqlDialect dialect = PXDatabase.Provider.SqlDialect;
Type table = Select.GetFirstTable();
table = BqlCommand.FindRealTableForType(types, table);
PXView view = new PXView(sender.Graph, true, Select);
List<IBqlSortColumn> sorts = new List<IBqlSortColumn>();
//Here system will collect all columns for selecting
BqlCommand.Selection selection = new BqlCommand.Selection();
//Preparation
List<Type> fields = new List<Type>();
StringBuilder body = new StringBuilder();
//Parsing BQL command to create an SQL
Select.Parse(sender.Graph, null, types, fields, sorts, body, selection);
AllFields = selection.Columns.ToArray();
Positions.Clear();
//Creating a SQL command for each requested field
List<String> fieldnames = new List<string>();
for (int i = 0; i < Columns.Length; i++)
{
String field = BqlCommand.GetSingleField(Columns[i], sender.Graph, types, selection, BqlCommand.FieldPlace.Select);
int index = selection.Columns.IndexOf(field);
if (index < 0) throw new PXException("Wrong column was passed to selection");
Positions[index] = i;
fieldnames.Add(field);
}
//preparing sub select command
StringBuilder text = new StringBuilder();
text.Append(BqlCommand.SubSelect);
text.Append(" ");
//fields to select
text.Append(String.Join(", ", fieldnames.ToArray()));
//from, where, sorts
text.Append(body);
//for sql parameters
text.Append(GetForXML());
text.Append(")"); // from subselect
e.FieldName = text.ToString();
return;
}
else
e.FieldName = BqlCommand.Null;
}
}
public override void RowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
if (e.Row != null)
{
//reading xml from datareader
string v = e.Record.GetString(e.Position);
//Validation XML for root
if (v != null && !GetForXML().Contains("ROOT(") && RootTag != null)
{
v = String.Format("<{0}>{1}</{0}>", RootTag, v);
}
//saving xml
sender.SetValue(e.Row, _FieldOrdinal, v);
}
e.Position++;
}
public virtual void FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
{
if (e.Row != null)
{
//getting XML form the field
string xml = sender.GetValue(e.Row, this.FieldOrdinal) as string;
//Parsing XML and return data
if (!String.IsNullOrWhiteSpace(xml))
{
StringBuilder sb = new StringBuilder();
foreach (PXResult res in this.GetData(sender, xml))
{
String template = DisplayFormat ?? String.Empty;
foreach (Type column in Columns)
{
Type table = BqlCommand.GetItemType(column);
Object value = sender.Graph.Caches[table].GetValue(res[table], column.Name);
String str = value == null ? "null" : value.ToString();
String name = table.Name + "." + column.Name;
if (template.Contains("{" + name + "}"))
{
template = template.Replace("{" + name + "}", str);
}
else if(DisplayAppendColumns)
{
if (value != null || DisplayInsertNulls)
{
if (template.Length > 1) template += ", ";
template += name + "=" + str;
}
}
}
sb.AppendLine(template);
}
e.ReturnValue = sb.ToString();
}
}
}
#endregion
#region Static
public static Type[] GetColumns<Field>(PXCache sender)
where Field : IBqlField
{
return GetColumns(sender, typeof(Field).Name);
}
public static Type[] GetColumns(PXCache sender, String fieldname)
{
foreach (PXEventSubscriberAttribute attr in sender.GetAttributesReadonly(fieldname))
{
if (attr is PXDBSubSelectForXMLAttribute)
{
return ((PXDBSubSelectForXMLAttribute)attr).Columns;
}
}
return null;
}
//These methods will read XML data to DACs
public static List<PXResult> GetValues<Field>(PXCache sender, object row)
where Field : IBqlField
{
return GetValues(sender, row, typeof(Field).Name);
}
public static List<PXResult> GetValues(PXCache sender, object row, string fieldname)
{
if (row == null) return new List<PXResult>();
if (row != null)
{
foreach (PXEventSubscriberAttribute a in sender.GetAttributesReadonly(row, fieldname))
{
PXDBSubSelectForXMLAttribute attr = a as PXDBSubSelectForXMLAttribute;
if (attr != null)
{
//getting XML form the field
string xml = sender.GetValue(row, attr.FieldOrdinal) as string;
//Parsing XML and return data
if (!String.IsNullOrWhiteSpace(xml))
return attr.GetData(sender, xml);
}
}
}
return new List<PXResult>();
}
protected virtual List<PXResult> GetData(PXCache sender, string xml)
{
//Preparing mapping
Dictionary<string, int> columnsMapping = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0; i < Columns.Length; i++)
{
columnsMapping[Columns[i].Name] = i;
}
List<PXResult> set = new List<PXResult>();
//reading XML
using (XmlReader reader = XmlReader.Create(new StringReader(xml), new XmlReaderSettings() { }))
{
string[] pendingRow = null;
string pendingElement = null;
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element:
if (String.Equals(reader.Name, RowTag, StringComparison.InvariantCultureIgnoreCase) && reader.HasAttributes)
{
pendingRow = new string[Columns.Length];
while (reader.MoveToNextAttribute())
{
if (reader.Name != null && reader.Value != null)
{
int index = -1;
if (columnsMapping.TryGetValue(reader.Name, out index))
{
pendingRow[index] = reader.Value;
}
}
}
set.Add(FillResult(sender, Select, pendingRow));
pendingRow = null;
}
if (String.Equals(reader.Name, RowTag, StringComparison.InvariantCultureIgnoreCase) && !reader.HasAttributes)
{
if (pendingRow != null) set.Add(FillResult(sender, Select, pendingRow));
pendingRow = new string[Columns.Length];
}
if (!String.Equals(reader.Name, RowTag, StringComparison.InvariantCultureIgnoreCase) && !reader.HasAttributes && pendingRow != null)
{
if (reader.Name != null) pendingElement = reader.Name;
}
break;
case XmlNodeType.EndElement:
pendingElement = null;
break;
case XmlNodeType.Attribute:
break;
case XmlNodeType.Text:
if (pendingElement != null && reader.Value != null)
{
int index = -1;
if (columnsMapping.TryGetValue(pendingElement, out index))
{
pendingRow[index] = reader.Value;
}
}
break;
}
}
if (pendingRow != null) set.Add(FillResult(sender, Select, pendingRow));
}
return set;
}
//This method will parce and convert xml data to DACs
private PXResult FillResult(PXCache sender, BqlCommand select, string[] row)
{
Int32 pos = 0;
Type[] tables = select.GetTables();
Object[] items = new Object[tables.Length];
//Creting adaptor data record that will read data form array
PXDataRecordXML rec = new PXDataRecordXML(AllFields, Positions, row);
for (int i = 0; i < tables.Length; i++)
{
Boolean wasUpdated = false;
PXCache cache = sender.Graph.Caches[tables[i]];
//Risung RowSelecting events for each DAC
items[i] = cache.Select(rec, ref pos, true, out wasUpdated);
}
//Creating PXResult and return it.
return CreatePXResult(tables, items);
}
protected PXResult CreatePXResult(Type[] tables, object[] items)
{
Type result = null;
switch (tables.Length)
{
case 1:
result = typeof(PXResult<>).MakeGenericType(tables);
break;
case 2:
result = typeof(PXResult<,>).MakeGenericType(tables);
break;
case 3:
result = typeof(PXResult<,,>).MakeGenericType(tables);
break;
case 4:
result = typeof(PXResult<,,,>).MakeGenericType(tables);
break;
case 5:
result = typeof(PXResult<,,,,>).MakeGenericType(tables);
break;
case 6:
result = typeof(PXResult<,,,,,>).MakeGenericType(tables);
break;
case 7:
result = typeof(PXResult<,,,,,,>).MakeGenericType(tables);
break;
case 8:
result = typeof(PXResult<,,,,,,,>).MakeGenericType(tables);
break;
case 9:
result = typeof(PXResult<,,,,,,,,>).MakeGenericType(tables);
break;
case 10:
result = typeof(PXResult<,,,,,,,,,>).MakeGenericType(tables);
break;
case 11:
result = typeof(PXResult<,,,,,,,,,,>).MakeGenericType(tables);
break;
}
return result.InvokeMember(result.Name, System.Reflection.BindingFlags.CreateInstance, null, null, items) as PXResult;
}
#endregion
}
#endregion
#region PXDataRecordXML
//Adaptor data record that reads data from array according to fields mapping
public class PXDataRecordXML : PXDataRecord
{
protected String[] Values;
protected String[] Fields;
protected Dictionary<Int32, Int32> Map;
public PXDataRecordXML(String[] fields, Dictionary<Int32, Int32> map, String[] values)
: base(null, null, null)
{
Map = map;
Values = values;
Fields = fields;
}
//return nothing if value does not present in xml.
bool Remap(ref int i)
{
Int32 index = -1;
if (Map.TryGetValue(i, out index))
{
if (Values[index] == null)
return true;
i = index;
return false;
}
return true;
}
public override bool? GetBoolean(int i)
{
if (Remap(ref i))
return null;
return Convert.ToBoolean(Values[i]);
}
public override byte? GetByte(int i)
{
if (Remap(ref i))
return null;
return Convert.ToByte(Values[i]);
}
public override byte[] GetBytes(int i)
{
if (Remap(ref i))
return null;
return Convert.FromBase64String(Values[i]);
}
public override long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
return 0;
}
public override char? GetChar(int i)
{
if (Remap(ref i))
return null;
if (Values[i].Length < 0) return null;
return Convert.ToChar(Values[i][0]);
}
public override long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
return 0;
}
public override string GetDataTypeName(int i)
{
return null;
}
public override DateTime? GetDateTime(int i)
{
if (Remap(ref i))
return null;
return Convert.ToDateTime(Values[i]);
}
public override decimal? GetDecimal(int i)
{
if (Remap(ref i))
return null;
return Convert.ToDecimal(Values[i]);
}
public override double? GetDouble(int i)
{
if (Remap(ref i))
return null;
return Convert.ToDouble(Values[i]);
}
public override Type GetFieldType(int i)
{
return null;
}
public override float? GetFloat(int i)
{
if (Remap(ref i))
return null;
return float.Parse(Values[i]);
}
public override Guid? GetGuid(int i)
{
if (Remap(ref i))
return null;
return Guid.Parse(Values[i]);
}
public override short? GetInt16(int i)
{
if (Remap(ref i))
return null;
return Convert.ToInt16(Values[i]);
}
public override int? GetInt32(int i)
{
if (Remap(ref i))
return null;
return Convert.ToInt32(Values[i]);
}
public override long? GetInt64(int i)
{
if (Remap(ref i))
return null;
return Convert.ToInt64(Values[i]);
}
public override string GetString(int i)
{
var j = i;
if (Remap(ref j))
return null;
return Values[j];
}
public override object GetValue(int i)
{
if (Remap(ref i))
return null;
return Values[i];
}
public override bool IsDBNull(int i)
{
if (Remap(ref i))
return true;
return Values[i] == null;
}
public override string GetName(int i)
{
var j = i;
if (Remap(ref j))
return null;
return Fields[i];
}
public override int FieldCount
{
get
{
return Fields.Length;
}
}
}
#endregion
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.