This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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