SubSelectForXML.cs

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
}