using framework_library; using framework_library.classes; using Newtonsoft.Json; using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.IO; using System.Reflection; using System.Text; using static System.Net.Mime.MediaTypeNames; namespace framework_business { public class xData { #region constants public static string _ConnectionKey { get; set; } = "conn"; private const string _tablePrefix = "pal_"; #endregion #region Dynamic Iteractions #region Interaction Wrapper /// /// Byte[] to process Dynamic interactions /// /// /// /// /// /// /// private static oInteraction ProcesDynamicInteraction(oInteraction interaction, string connectionKey, bool isTransactional, string tablePrefix, bool isCollection, bool useService) { oInteraction result = null; try { if (useService)//connect to wcf service { Byte[] interactionData; interactionData = utils.ObjectToByteArray(interaction); framework_service.Iframework_serviceClient serviceCaller = new framework_service.Iframework_serviceClient("Iframework_service"); interactionData = serviceCaller.ProcessInteraction(interactionData, connectionKey, isTransactional, tablePrefix, isCollection); serviceCaller.Close(); result = (oInteraction)utils.ByteArrayToObject(interactionData); } else { dynamicInteraction dynaInteract = new dynamicInteraction(); result = dynaInteract.ProcessInteraction(interaction, connectionKey, isTransactional, tablePrefix, isCollection); } } catch (Exception ex) { throw ex; } return result; } #endregion #region Select Interactions Return Type: ArrayList /// /// Strongly Typed Collection of a specified custom Type /// /// /// /// /// /// public static ArrayList GetTypedCollection(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; if (orderBy != String.Empty) { interaction.orderClause = orderBy; } interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a specific custom type by a specific criteria /// /// /// /// /// /// /// /// public static ArrayList GetTypedByCriteriaSpecific(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { string criteriaName = criteria; if (criteria.Length > 7 && criteria.Substring(0, 7) == "~ISNULL") { string[] arr = criteriaName.Split('|'); arr[0] = arr[0].Substring(7); criteriaName = "ISNULL(" + arr[1] + ",'" + arr[0] + "')"; } if (criteria.Length > 10 && criteria.Substring(0, 10) == "~ISNOTNULL") { criteriaName = string.Format("{0} IS NOT NULL", criteria.Substring(10)); } if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteria.Substring(9) + " AS DATE)"; } CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value.Length > 1) { if (value.Length > 2 && value.Substring(0, 3) == "~<>") interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~<=") interaction.whereClause += criteriaName + " <= '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~>=") interaction.whereClause += criteriaName + " >= '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~()") { interaction.whereClause += criteriaName + " IN ("; string temp = String.Empty; foreach (string item in value.Substring(3).Split('|')) { //CVH 2017-01-19 Exception, don't add , after last item //interaction.whereClause += utils.formatSqlString(item) + ", "; if (temp == String.Empty) temp = "'" + utils.formatSqlString(item) + "'"; else temp += ", '" + utils.formatSqlString(item) + "'"; } interaction.whereClause += temp + ")"; } else if (value.Substring(0, 2) == "~<") interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' "; else if (value.Substring(0, 2) == "~>") interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' "; else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; } else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a specific custom type by a specific criteria limited to defined records /// /// /// /// /// /// /// /// /// public static ArrayList GetTypedByCriteriaSpecificTop(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", int recordCount = 0, string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { if (recordCount > 0) interaction.interRecordLimit = recordCount; interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { string criteriaName = criteria; if (criteria.Length > 7 && criteria.Substring(0, 7) == "~ISNULL") { string[] arr = criteriaName.Split('|'); arr[0] = arr[0].Substring(7); criteriaName = "ISNULL(" + arr[1] + ",'" + arr[0] + "')"; } CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value.Length > 1) { if (value.Length > 2 && value.Substring(0, 3) == "~<>") interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~<=") interaction.whereClause += criteriaName + " <= '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~>=") interaction.whereClause += criteriaName + " >= '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~()") { interaction.whereClause += criteriaName + " IN ("; string temp = String.Empty; foreach (string item in value.Substring(3).Split('|')) { //CVH 2017-01-19 Exception, don't add , after last item //interaction.whereClause += utils.formatSqlString(item) + ", "; if (temp == String.Empty) temp = "'" + utils.formatSqlString(item) + "'"; else temp += ", '" + utils.formatSqlString(item) + "'"; } interaction.whereClause += temp + ")"; } else if (value.Substring(0, 2) == "~<") interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' "; else if (value.Substring(0, 2) == "~>") interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' "; else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; } else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a distinct field custom type by a specific criteria /// /// /// /// /// /// /// /// /// public static ArrayList GetTypedByCriteriaDistinct(string identityField, Type type, string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.SelectDistinct; interaction.interType = type; interaction.interDistinct = distinctName; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a specific custom type by a contains search criteria /// /// /// /// /// /// /// /// public static ArrayList GetTypedByCriteriaContains(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a specific custom type by a search criteria that begins with a value /// /// /// /// /// /// /// /// public static ArrayList GetTypedByCriteriaBegins(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Strongly Typed Collection by a Custom query /// /// /// /// /// /// public static ArrayList GetCustomCollection(string identityField, Type type, string customQuery, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.query = customQuery; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Select Interactions Return Type: Dynamic DataTable /// /// Strongly Typed Collection of a specified custom Type /// /// /// /// /// /// public static DataTable GetDynamicTable(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetDynamicByCriteriaSpecific(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific distinct criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetDynamicByCriteriaDistinct(string identityField, Type type, string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; interaction.interDistinct = distinctName; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetDynamicByCriteriaContains(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetDynamicByCriteriaBegins(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Select Interactions Return Type: DataTable /// /// Strongly Typed Table of a specified custom Type /// /// /// /// /// /// public static DataTable GetTypedTable(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific criteria /// /// /// /// /// /// /// /// public static DataTable GetTypedByCriteriaSpecificTable(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { string criteriaName = criteria; if (criteria.Length > 7 && criteria.Substring(0, 7) == "~ISNULL") { string[] arr = criteriaName.Split('|'); arr[0] = arr[0].Substring(7); criteriaName = "ISNULL(" + arr[1] + ",'" + arr[0] + "')"; } if (criteria.Length > 10 && criteria.Substring(0, 10) == "~ISNOTNULL") { criteriaName = string.Format("{0} IS NOT NULL", criteria.Substring(10)); } if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteria.Substring(9) + " AS DATE)"; } CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value.Length > 1) { if (value.Length > 2 && value.Substring(0, 3) == "~<>") interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~()") { interaction.whereClause += criteriaName + " IN ("; string temp = String.Empty; foreach (string item in value.Substring(3).Split('|')) { //CVH 2017-01-19 Exception, don't add , after last item //interaction.whereClause += utils.formatSqlString(item) + ", "; if (temp == String.Empty) temp = "'" + utils.formatSqlString(item) + "'"; else temp += ", '" + utils.formatSqlString(item) + "'"; } interaction.whereClause += temp + ")"; } else if (value.Substring(0, 2) == "~<") { interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' "; } else if (value.Substring(0, 2) == "~>") { interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' "; } else if (value.Substring(0, 2) == "~%") { interaction.whereClause += criteriaName + " like '%" + utils.formatSqlString(value.Substring(2)) + "%' "; } else { interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; } } else if (value.Length > 0) { interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; } else { interaction.whereClause += criteriaName + " "; } if (CriteriaCounter != criterias.Length) { interaction.whereClause += " AND "; } break; } } } } else //single criteria { if (criteriaValueList.Contains(",")) //multiple values { interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; } else { string criteriaName = criteriaNameList; if (criteriaNameList.Length > 9 && criteriaNameList.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteriaNameList.Substring(9) + " AS DATE)"; } interaction.whereClause = criteriaName + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific criteria limited to defined records /// /// /// /// /// /// /// /// public static DataTable GetTypedByCriteriaSpecificTableTop(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", int recordCount = 0, string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { if (recordCount > 0) interaction.interRecordLimit = recordCount; interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { string criteriaName = criteria; if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteria.Substring(9) + " AS DATE)"; } CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value.Length > 1) { if (value.Length > 2 && value.Substring(0, 3) == "~<>") interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' "; else if (value.Length > 2 && value.Substring(0, 3) == "~()") { interaction.whereClause += criteriaName + " IN ("; if (!value.Substring(3).Contains("|")) interaction.whereClause += utils.formatSqlString(value.Substring(3)); else { string valueList = value.Substring(3); bool first = true; foreach (string item in valueList.Split('|')) { if (first) interaction.whereClause += utils.formatSqlString(item); else interaction.whereClause += "," + utils.formatSqlString(item); first = false; } } interaction.whereClause += ")"; } else if (value.Substring(0, 2) == "~<") interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' "; else if (value.Substring(0, 2) == "~>") interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' "; else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; } else interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else { string criteriaName = criteriaNameList; if (criteriaNameList.Length > 9 && criteriaNameList.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteriaNameList.Substring(9) + " AS DATE)"; } interaction.whereClause = criteriaName + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific distinct criteria /// /// /// /// /// /// /// /// public static DataTable GetTypedByCriteriaDistinctTable(string identityField, Type type, string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.SelectDistinct; interaction.interType = type; interaction.interDistinct = distinctName; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetTypedByCriteriaContainsTable(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataTable GetTypedByCriteriaBeginsTable(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Strongly Typed Table of a specified custom Type /// /// /// /// /// /// public static DataTable GetCustomTypedTable(string customQuery, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.interactionType = enums.InteractionType.Select; interaction.query = customQuery; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } //Override Method public static DataTable GetCustomTypedTable(string customQuery, string connectionKey, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); //__ConnectionKey = connectionKey; try { interaction.interactionType = enums.InteractionType.Select; interaction.query = customQuery; interaction = ProcesDynamicInteraction(interaction, connectionKey, false, "publ_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Select Interactions Return Type: DataSet /// /// Strongly Typed DataSet of a specified custom Type /// /// /// /// /// /// public static DataSet GetTypedSet(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataSet of a specific custom type by a specific criteria /// /// /// /// /// /// /// /// public static DataSet GetTypedByCriteriaSpecificSet(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a specific distinct criteria /// /// /// /// /// /// /// /// public static DataSet GetTypedByCriteriaDistinctSet(string identityField, Type type, string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.interDistinct = distinctName; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataSet GetTypedByCriteriaContainsSet(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of a specific custom type by a contains criteria dynamically /// /// /// /// /// /// /// /// public static DataSet GetTypedByCriteriaBeginsSet(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; //check we have criteria values if (criteriaValueList != String.Empty || criteriaNameList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { if (value != String.Empty) { if (interaction.whereClause == String.Empty) interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; else interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' "; } break; } } } } else//single criteria { interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Save Interactions /// /// Save a Typed Object /// /// /// /// /// /// public static int SaveTyped(string identityField, Type type, Object obj, string tablePrefix = _tablePrefix, bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { //int test = 0; //test = 1 / test; interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; interaction.interObject = obj; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interInt; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { exception.HandleException("SaveType", MethodBase.GetCurrentMethod().Name, ex, handler.ReturnUser()); SaveErrorLogToFile(MethodBase.GetCurrentMethod().Name, type, tablePrefix, obj, ex); throw ex; } return result; } /// /// Save a Typed Collection of Objects /// /// /// /// /// /// public static int SaveTypedCollection(string identityField, Type type, ArrayList objList, string tablePrefix = _tablePrefix, bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; foreach (object obj in objList) { interaction.interObjects.Add(obj); } interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interInt; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { SaveErrorLogToFile(MethodBase.GetCurrentMethod().Name,type, tablePrefix, objList, ex); throw ex; } return result; } /// /// Save a Typed Collection of Objects /// /// /// /// /// /// public static bool UpdateTypedCollection(string identityField, Type type, ArrayList objList, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; foreach (object obj in objList) { interaction.interObjects.Add(obj); } interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { SaveErrorLogToFile(MethodBase.GetCurrentMethod().Name,type, tablePrefix, objList, ex); throw ex; } return result; } /// /// bool to update a typed obejct /// /// /// /// /// /// /// public static bool UpdateTyped(string identityField, string identityValue, Type type, Object obj, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; interaction.interObject = obj; interaction.whereClause = identityField + " = " + identityValue; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { SaveErrorLogToFile(MethodBase.GetCurrentMethod().Name, type, tablePrefix, obj, ex); throw ex; } return result; } /// /// Run a custom query /// /// /// /// public static bool RunCustomQuery(string query, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.interactionType = enums.InteractionType.Update; interaction.query = query; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Verify Interactions Return Type: Boolean /// /// Verify if record exists /// /// /// /// /// /// /// /// public static bool VerifyExists(string identityField, Type type, string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = "", bool userService = false) { bool result = false; tablePrefix = (!string.IsNullOrEmpty(tablePrefix) ? tablePrefix : _tablePrefix); oInteraction interaction = new oInteraction(); try { if (criteriaNameList.Trim() != String.Empty) { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Verify; interaction.interType = type; //check we have criteria values if (criteriaValueList != String.Empty) { if (criteriaNameList.Contains(","))//multiple criterias provided { string[] criterias = criteriaNameList.Split(char.Parse(",")); string[] values = criteriaValueList.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { if (criteriaValueList.Contains(","))//multiple values interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") "; else interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interBool; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion #region Delete Interactions Return Type: Boolean /// /// Delete object /// /// /// /// /// /// public static bool DeleteTyped(string identityField, string identityValue, Type type, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Delete; interaction.interType = type; //check we have criteria values if (identityField != String.Empty) { if (identityField.Contains(","))//multiple criterias provided { string[] criterias = identityField.Split(char.Parse(",")); string[] values = identityValue.Split(char.Parse(",")); int CriteriaCounter = 0; foreach (String criteria in criterias) { CriteriaCounter++; int ValueCounter = 0; foreach (String value in values) { ValueCounter++; if (ValueCounter == CriteriaCounter) { interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' "; if (CriteriaCounter != criterias.Length) interaction.whereClause += " AND "; break; } } } } else//single criteria { interaction.whereClause = identityField + " = '" + identityValue + "' "; } } //process the interaction over wcf protocol or directly with dll interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interBool; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Delete Published surface Item /// /// /// /// public static bool DeletePublishedSurfaceItem(int surfaceId, int surfaceItemId) { bool result = false; try { foreach (oSurface surf in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "recId", surfaceId.ToString())) { if (surf.isPublished) { string q = "UPDATE publ_" + surf.name + " SET isActive = 0 Where itemID = " + surfaceItemId; result = RunCustomQuery(q); } break; } } catch (Exception ex) { throw ex; } return result; } #endregion #region Stored Procedure Interactions /// /// DataTable to Get a Dynamic Table by Stored Procedure /// /// /// /// /// /// /// /// /// /// public static DataTable GetDynamicTableByProc(string identityField, Type type, string StoredProcName, List StoredProcParams, int recordLimit, string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction.interStoredProcDynamicSelect = true; interaction.includeDynamics = true; interaction.interRecordLimit = recordLimit; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Strongly Typed Collection of a specified custom Type /// /// /// /// /// /// public static ArrayList GetTypedCollectionByProc(string identityField, Type type, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Strongly Typed Table of a specified custom Type /// /// /// /// /// /// public static DataTable GetTypedTableByProc(string identityField, Type type, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } public static DataSet GetTypedDataSetByProc(string identityField, Type type, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Strongly Typed DataSet of a specified custom Type /// /// /// /// /// /// public static DataSet GetTypedSetByProc(string identityField, Type type, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { DataSet result = new DataSet(); oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSet; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Save a Typed Object /// /// /// /// /// /// public static int SaveTypedByProc(string identityField, Type type, Object obj, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interInt; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// bool to update a typed obejct /// /// /// /// /// /// /// public static bool UpdateTypedByProc(string identityField, Type type, Object obj, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// bool to update a typed obejct /// /// /// /// /// /// /// public static bool VerifyExistsByProc(string identityField, Type type, Object obj, string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Verify; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Execute procedure /// /// /// /// /// /// public static bool ExecuteProc(string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; string spParams = string.Empty; oInteraction interaction = new oInteraction(); try { spParams = string.Join(",", StoredProcParams); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, tablePrefix, false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { exception.HandleException("XData:", MethodBase.GetCurrentMethod().Name + " sp:" + StoredProcName + " param:" + spParams, ex, handler.ReturnUser()); throw ex; } return result; } #endregion //custom stored procedures #region Custom Stored Procedure Calls public static DataTable GetSumRoomCounts(int propertyID, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "propertyID"; param.paramObject = propertyID; StoredProcParams.Add(param); interaction.identityField = "itemId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sproc_PROP1_SumRoomCounts"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "publ_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Count /// /// /// /// count public static int GetSurfaceDataCount(int surfaceId, int userLink, string filter = "", bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "UserLink"; param1.paramObject = userLink; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Filter"; param2.paramObject = filter; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceDataCount"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) { foreach (DataRow row in interaction.interSet.Tables[0].Rows) { result = int.Parse(row["Count"].ToString()); break; } } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetSurfaceDataPaged(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceDataPaged"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data /// /// /// public static DataTable GetSurfaceData(int surfaceId, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data /// /// /// public static DataTable GetSurfaceDataAll(int surfaceId, bool activeOnly, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "activeOnly"; param1.paramObject = activeOnly ? "1" : "0"; StoredProcParams.Add(param1); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceDataAll"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Item Data /// /// /// /// public static DataTable GetSurfaceItemGridData(int surfaceId, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "SurfaceItemID"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceItemGridData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Item Data /// /// /// /// public static DataTable GetSurfaceItemData(int surfaceId, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "SurfaceItemID"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceItemData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface field aggregation sum (surface action to sum specified values within item) /// /// surfaceItemId /// actionValue /// public static decimal GetSurfaceFieldAggregationSum(int surfaceItemId, string actionValue, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "actionValue"; param2.paramObject = actionValue; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceFieldAggregateSUM"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface field aggregation differnce (surface action to minus two values within item) /// /// surfaceItemId /// actionValue /// public static decimal GetSurfaceFieldAggregationDiff(int surfaceItemId, string actionValue, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "actionValue"; param2.paramObject = actionValue; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceFieldAggregateDIFF"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface field aggregation distinction count (surface action to count distinctions) /// /// surfaceItemId /// actionValue /// public static decimal GetSurfaceFieldAggregationDistinction(int surfaceItemId, string actionValue, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "actionValue"; param2.paramObject = actionValue; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceFieldAggregateDISTINCTION"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface field aggregation failed count (surface action to count failed) /// /// surfaceItemId /// actionValue /// public static decimal GetSurfaceFieldAggregationFailed(int surfaceItemId, string actionValue, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "actionValue"; param2.paramObject = actionValue; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceFieldAggregateFAILED"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface field aggregation passes count (surface action to count passed) /// /// surfaceItemId /// actionValue /// public static decimal GetSurfaceFieldAggregationPassed(int surfaceItemId, string actionValue, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "actionValue"; param2.paramObject = actionValue; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceFieldAggregatePASSED"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get surface aggregation sum (surface action to sum child grid value) /// /// /// surfaceFieldId /// parentSurfaceItemId (0 when none) /// public static decimal GetSurfaceAggregationSum(int surfaceId, int surfaceFieldId, int parentSurfaceItemId, bool useService = false) { decimal result = 0m; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceId"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceFieldId"; param2.paramObject = surfaceFieldId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "parentSurfaceItemID"; param3.paramObject = parentSurfaceItemId; StoredProcParams.Add(param3); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceAggrSum"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables[0].Rows.Count > 0 && interaction.interSet.Tables[0].Columns.Count > 0) { if (!decimal.TryParse(interaction.interSet.Tables[0].Rows[0][0].ToString(), out result)) result = 0m; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Publihsed Data Count /// /// /// /// count public static int GetPublishedSurfaceDataCount(int surfaceId, int userLink, string search, string filter = "", bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "UserLink"; param1.paramObject = userLink; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Filter"; param2.paramObject = filter; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "Search"; param3.paramObject = search; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "UserId"; param4.paramObject = handler.ReturnUser().recId; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Cols"; param5.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, false); StoredProcParams.Add(param5); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPublishedSurfaceDataCount"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) { foreach (DataRow row in interaction.interSet.Tables[0].Rows) { result = int.Parse(row["Count"].ToString()); break; } } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Publihsed Data Count All /// /// /// /// count public static int GetPublishedSurfaceDataCountAll(int surfaceId, int userLink, string search, string filter = "", bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "UserLink"; param1.paramObject = userLink; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Filter"; param2.paramObject = filter; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "Search"; param3.paramObject = search; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "UserId"; param4.paramObject = handler.ReturnUser().recId; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Cols"; param5.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, false); StoredProcParams.Add(param5); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPublishedSurfaceDataCountAll"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) { foreach (DataRow row in interaction.interSet.Tables[0].Rows) { result = int.Parse(row["Count"].ToString()); break; } } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetPublishedSurfaceDataPaged(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false, bool showAllActiveFields = false, string externalConn = "") { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); oDynamicParam param9 = new oDynamicParam(); param9.paramDisplayName = "Cols"; param9.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, showAllColumns, showAllActiveFields); StoredProcParams.Add(param9); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPublishedSurfaceDataPaged"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, !string.IsNullOrEmpty(externalConn) ? externalConn : _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Custom OrderList Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetCustomOrderListDataPaged(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false, bool showAllActiveFields = false, string externalConn = "") { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); oDynamicParam param9 = new oDynamicParam(); param9.paramDisplayName = "Cols"; param9.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, showAllColumns, showAllActiveFields); StoredProcParams.Add(param9); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetCustomOrderListDataPaged"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, !string.IsNullOrEmpty(externalConn) ? externalConn : _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged For All /// /// /// /// /// /// /// /// /// public static DataTable GetPublishedSurfaceDataPagedAll(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); oDynamicParam param9 = new oDynamicParam(); param9.paramDisplayName = "Cols"; param9.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, showAllColumns); StoredProcParams.Add(param9); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPublishedSurfaceDataPagedAll"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Data Count /// /// /// /// count public static int GetSurfaceQueryDataCount(int surfaceId, int userLink, string search, string filter = "", bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "UserLink"; param1.paramObject = userLink; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Filter"; param2.paramObject = filter; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "Search"; param3.paramObject = search; StoredProcParams.Add(param3); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceQueryDataCount"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) { foreach (DataRow row in interaction.interSet.Tables[0].Rows) { result = int.Parse(row["Count"].ToString()); break; } } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetSurfaceQueryDataPaged(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceQueryDataPaged"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetSurfaceQueryDataPagedNoZeroes(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceQueryDataPagedNoZeroes"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Item Data /// /// /// /// public static DataTable GetSurfaceQueryItemData(int surfaceId, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "SurfaceItemID"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceQueryItemData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data /// /// /// /// /// /// DataTable public static DataTable GetChildSurfaceQueryData(int surfaceId, int ParentSurfaceItemId, int RowLimit = 0, bool userService = false) { return GetChildSurfaceQueryData(surfaceId, ParentSurfaceItemId, "", RowLimit, userService); } /// /// Get Surface Query Child Data /// /// /// /// /// /// /// DataTable public static DataTable GetChildSurfaceQueryData(int surfaceId, int ParentSurfaceItemId, string filter, int RowLimit = 0, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Filter"; param4.paramObject = filter; StoredProcParams.Add(param4); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildSurfaceQueryData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data /// /// /// /// public static DataTable GetChildSurfaceQueryData(int surfaceId, int ParentSurfaceItemId, bool gridOnly, int RowLimit = 0, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "GridOnly"; param4.paramObject = gridOnly; StoredProcParams.Add(param4); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildSurfaceQueryData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data /// /// /// /// public static DataTable GetChildSurfaceQueryDataNoZeroes(int surfaceId, int ParentSurfaceItemId, int RowLimit = 0, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildSurfaceQueryDataNoZeroes"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data /// /// /// /// public static DataTable GetChildPublishedSurfaceData(int surfaceId, int ParentSurfaceItemId, int userId, int RowLimit = 0, string orderBy = "", bool userService = false, bool showAllActiveFields = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "UserId"; param4.paramObject = userId; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Cols"; param5.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, false, showAllActiveFields); StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "OrderBy"; param6.paramObject = orderBy; StoredProcParams.Add(param6); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildPublishedSurfaceData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data /// /// /// /// public static DataTable GetBindCriticalPathDetail(int surfaceId, int ParentSurfaceItemId, int userId, int RowLimit = 0, string orderBy = "", bool userService = false, bool showAllActiveFields = false) { orderBy = "CriticalPathMilestones_MilestoneDetails_Phase,CriticalPathMilestones_MilestoneDetails_SequenceNumber"; DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "UserId"; param4.paramObject = userId; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Cols"; param5.paramObject = surfaceHandler.BuildPublishedSelectColumns(surfaceId, false, showAllActiveFields); StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "OrderBy"; param6.paramObject = orderBy; StoredProcParams.Add(param6); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildPublishedSurfaceData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Query Child Data Summarized /// /// /// /// public static DataTable GetChildSurfaceQueryDataSummarized(int surfaceId, int ParentSurfaceItemId, int RowLimit = 0, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ParentSurfaceItemId"; param2.paramObject = ParentSurfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "RowLimit"; param3.paramObject = RowLimit; StoredProcParams.Add(param3); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetChildSurfaceQueryDataSummarized"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Save Surface Item to Query Table /// /// /// /// /// public static bool SaveSurfaceItemToQueryTable(int surfaceId, int surfaceItemId, bool updateLabels = false, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { if (updateLabels) { SetItemLabelValues(surfaceId, surfaceItemId, userService); } List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "UserLink"; param3.paramObject = 0; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "UserId"; param4.paramObject = 216; StoredProcParams.Add(param4); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ItemID"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_SaveSurfaceItemQueryTable"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Save Surface Item to Query Table /// /// /// /// /// public static bool UpdateQueryTableRankData(oSurfaceField rankField, bool useService) { bool result = false; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceId"; param.paramObject = rankField.surfaceId; StoredProcParams.Add(param); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "rankFieldName"; param3.paramObject = rankField.surfaceFieldName; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "rankFieldSource"; param4.paramObject = rankField.actionSource; StoredProcParams.Add(param4); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "rankFieldGroupBy"; param2.paramObject = rankField.actionValue; StoredProcParams.Add(param2); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_UpdateQueryTableRankData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Update SMS's allocated /// /// /// public static bool UpdateAllocatedSMS(string customerCode, bool useService = true) { bool result = false; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "CustomerCode"; param.paramObject = customerCode; StoredProcParams.Add(param); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_UpdateSMSAllocatedForCustomer"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { result = interaction.interSucess; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Set Item Label Values /// /// /// /// /// public static bool SetItemLabelValues(int surfaceId, int surfaceItemId, bool useService) { bool result = false; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "srcItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "srcSurfaceId"; param2.paramObject = surfaceId; StoredProcParams.Add(param2); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_SetItemLabelValues"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Set Item Label Values Self /// /// /// /// /// public static bool SetItemLabelValuesSelf(int surfaceId, int surfaceItemId, bool useService) { bool result = false; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "itemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceId"; param2.paramObject = surfaceId; StoredProcParams.Add(param2); interaction.interactionType = enums.InteractionType.Update; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_SetItemLabelValuesSelf"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Delete Surface Item from Query Table /// /// /// /// /// public static bool DeleteSurfaceItemFromQueryTable(int surfaceId, int surfaceItemId, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ItemID"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.interactionType = enums.InteractionType.Delete; interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DeleteSurfaceItemQueryTable"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSucess; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Module Picklist /// /// /// public static DataTable GetSurfaceModulePicklist(int surfaceFieldId, bool userService = false, string externalConn = "") { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceFieldId"; param.paramObject = surfaceFieldId; StoredProcParams.Add(param); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceModulePicklist"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, !string.IsNullOrEmpty(externalConn) ? externalConn : _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Parts Master Count /// /// /// count public static int GetPartsMasterCount(string search, string itemVetted, string machine, string category, string subCategory, bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "PartNumber"; param1.paramObject = search; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ItemVetted"; param2.paramObject = itemVetted; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "Machine"; param3.paramObject = machine; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Category"; param4.paramObject = category; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "SubCategory"; param5.paramObject = subCategory; StoredProcParams.Add(param5); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPartsMasterCount"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) { foreach (DataRow row in interaction.interSet.Tables[0].Rows) { result = int.Parse(row["Count"].ToString()); break; } } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Parts Master Data /// /// /// /// /// /// /// /// /// public static DataTable GetPartsMasterData(string search, string itemVetted, string machine, string category, string subCategory, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "PartNumber"; param.paramObject = search; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "ItemVetted"; param2.paramObject = itemVetted; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "Machine"; param3.paramObject = machine; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Category"; param4.paramObject = category; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "SubCategory"; param5.paramObject = subCategory; StoredProcParams.Add(param5); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPartsMasterData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Surface Data Paged /// /// /// /// /// /// /// /// /// public static DataTable GetPartsMasterData2(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userId, int userLink = 0, string filter = "", bool showAllColumns = false, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "SurfaceID"; param.paramObject = surfaceId; StoredProcParams.Add(param); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "StartIndex"; param1.paramObject = startIndex; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "MaxRows"; param2.paramObject = MaxRows; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "OrderBy"; param3.paramObject = orderBy; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "Search"; param4.paramObject = search; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "UserLink"; param5.paramObject = userLink; StoredProcParams.Add(param5); oDynamicParam param6 = new oDynamicParam(); param6.paramDisplayName = "Filter"; param6.paramObject = filter; StoredProcParams.Add(param6); oDynamicParam param7 = new oDynamicParam(); param7.paramDisplayName = "UserId"; param7.paramObject = userId; StoredProcParams.Add(param7); oDynamicParam param8 = new oDynamicParam(); param8.paramDisplayName = "ShowAllFields"; param8.paramObject = showAllColumns; StoredProcParams.Add(param8); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPartsMasterData2"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSucess) { if (interaction.interSet.Tables.Count > 0) result = interaction.interSet.Tables[0]; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #endregion #region release management /// /// Byte Array to get a version from the service /// /// /// /// /// /// public static byte[] GetVersion(string customerCode, string licenceKey, string versionNo, string file) { byte[] result = null; try { framework_service.Iframework_serviceClient serviceCaller = new framework_service.Iframework_serviceClient("Iframework_service"); result = serviceCaller.GetVersion(customerCode, licenceKey, versionNo, file); serviceCaller.Close(); } catch (Exception ex) { throw ex; } return result; } /// /// Save Version /// /// /// /// /// public static bool SaveVersion(byte[] versionData, string versionNo, string file) { bool result = false; try { framework_service.Iframework_serviceClient serviceCaller = new framework_service.Iframework_serviceClient("Iframework_service"); result = serviceCaller.SaveVersion(versionData, versionNo, file); serviceCaller.Close(); } catch (Exception ex) { throw ex; } return result; } #endregion #region Site Specific /// /// ArrayList of a Strongly Typed Objects by a Criteria /// /// /// /// /// /// ArrayList public static ArrayList SearchPageContent(string keyword, string orderBy = "", bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oCanvas); interaction.whereClause = " (display LIKE '%" + keyword + "%' OR description LIKE '%" + keyword + "%' OR recId IN (Select canvasId FROM pal_CanvasMetaData Where moduleId = " + pNums.Module.Content.GetHashCode() + " AND entityId IN (Select recId FROM pal_Content WHERE title LIKE '%" + keyword + "%' OR contentHTML LIKE '%" + keyword + "%'))) AND isActive = '1' "; if (orderBy != String.Empty) interaction.orderClause = orderBy; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interList; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// ArrayList of a Strongly Typed Objects by a Product Search /// /// /// /// /// /// ArrayList public static ArrayList SearchProducts(string keyword, int catId, int typeId, int brandId, string orderBy = "", bool userService = false) { ArrayList result = new ArrayList(); oInteraction interaction = new oInteraction(); string WhereClause = String.Empty; try { WhereClause = "isActive = '1'"; if (keyword != String.Empty) WhereClause += "AND (name LIKE '%" + keyword + "%' OR display LIKE '%" + keyword + "%' OR fileName LIKE '%" + keyword + "%' OR description LIKE '%" + keyword + "%' OR specifications LIKE '%" + keyword + "%') "; if (catId > 0) WhereClause += "AND (catId = " + catId + ") "; if (typeId > 0) WhereClause += "AND (typeId = " + typeId + ") "; if (brandId > 0) WhereClause += "AND (brandId = " + brandId + ") "; //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oProduct); interaction.whereClause = WhereClause; if (orderBy != String.Empty) interaction.orderClause = orderBy; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) { result = interaction.interList; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region orders /// /// ArrayList of Product Order Items /// /// /// ArrayList public static ArrayList FetchProductOrderItems(int orderId, bool userService = false) { ArrayList result = new ArrayList(); try { //first fetch order Items foreach (oOrderItems item in GetTypedByCriteriaSpecific("recId", typeof(oOrderItems), "orderId", orderId.ToString())) { oProductOrder prodOrder = new oProductOrder(); prodOrder.recId = item.recId; prodOrder.priceDisplay = "R" + utils.returnFormattedDecimal(String.Format("{0:00}", item.unitPrice).Replace(".00", "")); prodOrder.productId = item.productId; prodOrder.qty = item.qty; prodOrder.totalDisplay = "R" + utils.returnFormattedDecimal(String.Format("{0:00}", (item.unitPrice * item.qty)).Replace(".00", "")); prodOrder.unitPrice = item.unitPrice; prodOrder.totalWeight = item.unitWeight * item.qty; foreach (oProduct prod in GetTypedByCriteriaSpecific("recId", typeof(oProduct), "recId", item.productId.ToString())) { prodOrder.name = prod.display; prodOrder.fileName = prod.fileName; //populate category foreach (oProductCategory ctegory in GetTypedByCriteriaSpecific("recId", typeof(oProductCategory), "recId", prod.catId.ToString())) { prodOrder.category = ctegory.category; prodOrder.caption = ctegory.caption; break; } //populate brand foreach (oProductBrand cat in GetTypedByCriteriaSpecific("recId", typeof(oProductBrand), "recId", prod.brandId.ToString())) { prodOrder.brand = cat.brand; break; } } //add Product Order to result result.Add(prodOrder); } } catch (Exception ex) { throw ex; } return result; } #endregion #region rates /// /// bool to create rates for a plan code /// /// /// public static bool CreateRatesForPlan(string planCode, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = typeof(oTariffRate); interaction.query = "INSERT INTO pal_TariffRate ([planCode] ,[procedureCode] ,[rate]) "; interaction.query += "SELECT '" + planCode + "' AS [planCode], code, 0.00 AS [rate] FROM pal_MedicalProcedure "; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Update Rates for a procedure /// /// /// /// public static bool UpdateRates(string planCode, string procedureCode, decimal rate, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Update; interaction.interType = typeof(oTariffRate); interaction.query = "UPDATE pal_TariffRate SET rate = " + rate + " "; interaction.query += "WHERE procedureCode = '" + procedureCode + "' "; if (planCode != String.Empty) { interaction.query += "AND planCode = '" + planCode + "' "; } //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion #region sales /// /// Get Account Balance /// /// /// public static decimal GetSalesBalance(int surfaceItemId = 0, DateTime? endDate = null, bool userService = false) { decimal result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); //CVH 2016-09-21 Exclude drafts in balance if (surfaceItemId == 0) interaction.query = "SELECT SUM(amount) As Balance FROM pal_Sales Where itemType <> 'QT' AND isVisible = 1 AND isDraft = 0 "; else interaction.query = "SELECT SUM(amount) As Balance FROM pal_Sales Where surfaceItemId = " + surfaceItemId + " AND itemType <> 'QT' AND isVisible = 1 AND isDraft = 0 "; if (endDate != null) interaction.query += " AND dateOfService < '" + utils.fixDate(((DateTime)endDate).AddDays(1)) + "'"; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { decimal.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } ///// ///// Get Next Inv ///// ///// //public static int GetNextInvNoSales(bool userService = false) //{ // int result = 0; // oInteraction interaction = new oInteraction(); // Byte[] interationData; // try // { // //define interaction // interaction.identityField = "recId"; // interaction.interactionType = enums.InteractionType.Select; // interaction.interType = typeof(oSales); // interaction.query = "SELECT IsNull(Max(invoiceNo), 0) + 1 As InvNo FROM pal_Sales "; // //fetch data // interationData = utils.ObjectToByteArray(interaction); // interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, false, userService); // interaction = (oInteraction)utils.ByteArrayToObject(interationData); // if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) // { // int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); // } // else // { // if (interaction.interExceptions.Count > 0) // { // Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); // throw ex; // } // } // } // catch (Exception ex) // { // throw ex; // } // return result; //} /// /// Get Next Receipt /// /// public static int GetNextRecNoSales(bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSales); interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Sales "; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal oSetup setup = handler.ReturnSetup(); if (result < setup.saleRCTNextNum)//then use the start number result = setup.saleRCTNextNum; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Invoice No /// /// /// /// public static int GetInvoiceNoSales(string itemType, bool userService = false, oSetup setup = null) { DataTable result = new DataTable(); int invNo = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam p = new oDynamicParam(); p.paramDisplayName = "ItemType"; p.paramObject = itemType; StoredProcParams.Add(p); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSales); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetInvoiceNoSales"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; invNo = Convert.ToInt32(result.Rows[0][0]); if (null == setup) { setup = handler.ReturnSetup(); } //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal switch (itemType) { case "QT"://quote if (invNo < setup.saleQTENextNum)//then use the start number invNo = setup.saleQTENextNum; break; case "TI"://invoice if (invNo < setup.saleINVNextNum)//then use the start number invNo = setup.saleINVNextNum; break; case "CN"://credit note if (invNo < setup.saleCRNNextNum)//then use the start number invNo = setup.saleCRNNextNum; break; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return invNo; } /// /// Get Next Sequence No /// /// /// /// public static int GetNextSequenceSales(DateTime dtDateOfService, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); int seqNo = 1; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetNextSequenceSales"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; seqNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return seqNo; } /// /// Get Next Sequence No for Quote Items /// /// /// /// public static int GetNextSequenceQuote(DateTime dtDateOfService, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); int seqNo = 1; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetNextSequenceQuote"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; seqNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return seqNo; } /// /// Get Quote No /// /// /// public static int GetQuoteNoSales(bool userService = false) { DataTable result = new DataTable(); int quoteNo = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSales); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetQuoteNoSales"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; quoteNo = Convert.ToInt32(result.Rows[0][0]); //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal oSetup setup = handler.ReturnSetup(); if (quoteNo < setup.saleQTENextNum)//then use the start number quoteNo = setup.saleQTENextNum; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return quoteNo; } /// /// Get Sales Data /// /// /// /// /// /// DataTabk public static DataTable GetSalesData(int surfaceItemId, string type, string from, string to, string statuses, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "SurfaceItemID"; param1.paramObject = surfaceItemId; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Type"; param2.paramObject = type; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "DateFrom"; param3.paramObject = from; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "DateTo"; param4.paramObject = to; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Statuses"; param5.paramObject = statuses; StoredProcParams.Add(param5); //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSales); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSalesData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = interaction.interSet.Tables[0]; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Sales Data /// /// /// /// /// /// DataTabk public static DataTable GetSalesDataWithQuotes(int surfaceItemId, string type, string from, string to, string statuses, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "SurfaceItemID"; param1.paramObject = surfaceItemId; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Type"; param2.paramObject = type; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "DateFrom"; param3.paramObject = from; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "DateTo"; param4.paramObject = to; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Statuses"; param5.paramObject = statuses; StoredProcParams.Add(param5); //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSales); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSalesDataWithQuotes"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = interaction.interSet.Tables[0]; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } public static DataTable GetQuoteItemsFinal(int parentSurfaceItemId, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "parentSurfaceItemId"; param1.paramObject = parentSurfaceItemId; StoredProcParams.Add(param1); //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oQuoteItemsFinal); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetQuoteItemsFinal"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = interaction.interSet.Tables[0]; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } public static DataTable GetCustomerFromTradingName(string tradingName, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "tradingName"; param1.paramObject = tradingName; StoredProcParams.Add(param1); //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oQuoteItemsFinal); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetCustomerFromTradingName"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = interaction.interSet.Tables[0]; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion #region purchases /// /// Get Account Balance /// /// /// public static decimal GetPurchasesBalance(int surfaceItemId = 0, DateTime? endDate = null, bool userService = false) { decimal result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); //CVH 2016-09-21 Exclude drafts in balance if (surfaceItemId == 0) interaction.query = "SELECT SUM(amount) As Balance FROM pal_Purchases Where itemType <> 'QT' AND isVisible = 1 AND isDraft = 0 "; else interaction.query = "SELECT SUM(amount) As Balance FROM pal_Purchases Where surfaceItemId = " + surfaceItemId + " AND itemType <> 'QT' AND isVisible = 1 AND isDraft = 0 "; if (endDate != null) interaction.query += " AND dateOfService < '" + utils.fixDate(((DateTime)endDate).AddDays(1)) + "'"; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { decimal.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } ///// ///// Get Next Inv ///// ///// //public static int GetNextInvNoPurchases(bool userService = false) //{ // int result = 0; // oInteraction interaction = new oInteraction(); // Byte[] interationData; // try // { // //define interaction // interaction.identityField = "recId"; // interaction.interactionType = enums.InteractionType.Select; // interaction.interType = typeof(oPurchases); // interaction.query = "SELECT IsNull(Max(invoiceNo), 0) + 1 As InvNo FROM pal_Purchases "; // //fetch data // interationData = utils.ObjectToByteArray(interaction); // interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, false, userService); // interaction = (oInteraction)utils.ByteArrayToObject(interationData); // if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) // { // int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); // } // else // { // if (interaction.interExceptions.Count > 0) // { // Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); // throw ex; // } // } // } // catch (Exception ex) // { // throw ex; // } // return result; //} /// /// Get Next Receipt /// /// public static int GetNextRecNoPurchases(bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oPurchases); interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Purchases "; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal oSetup setup = handler.ReturnSetup(); if (result < setup.purchaseRCTNextNum)//then use the start number result = setup.purchaseRCTNextNum; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Invoice No /// /// /// /// public static int GetInvoiceNoPurchases(string itemType, bool userService = false) { DataTable result = new DataTable(); int invNo = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam p = new oDynamicParam(); p.paramDisplayName = "ItemType"; p.paramObject = itemType; StoredProcParams.Add(p); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oPurchases); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetInvoiceNoPurchases"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; invNo = Convert.ToInt32(result.Rows[0][0]); oSetup setup = handler.ReturnSetup(); //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal switch (itemType) { case "QT"://quote if (invNo < setup.purchaseQTENextNum)//then use the start number invNo = setup.purchaseQTENextNum; break; case "TI"://invoice if (invNo < setup.purchaseINVNextNum)//then use the start number invNo = setup.purchaseINVNextNum; break; case "CN"://credit note if (invNo < setup.purchaseCRNNextNum)//then use the start number invNo = setup.purchaseCRNNextNum; break; } } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return invNo; } /// /// Get Next Sequence No /// /// /// /// public static int GetNextSequencePurchases(DateTime dtDateOfService, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); int seqNo = 1; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetNextSequencePurchases"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; seqNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return seqNo; } /// /// Get Next Sequence No for Quote Items /// /// /// /// public static int GetNextSequencePurchaseQuote(DateTime dtDateOfService, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); int seqNo = 1; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetNextSequenceQuote"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; seqNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return seqNo; } /// /// Get Quote No /// /// /// public static int GetQuoteNoPurchases(bool userService = false) { DataTable result = new DataTable(); int quoteNo = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oPurchases); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetQuoteNoPurchases"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; quoteNo = Convert.ToInt32(result.Rows[0][0]); //CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, result and NextNum should technically always be equal oSetup setup = handler.ReturnSetup(); if (quoteNo < setup.purchaseQTENextNum)//then use the start number quoteNo = setup.purchaseQTENextNum; } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return quoteNo; } /// /// Get Purchases Data /// /// /// /// /// /// DataTabk public static DataTable GetPurchasesData(int surfaceItemId, string type, string from, string to, string statuses, bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param1 = new oDynamicParam(); param1.paramDisplayName = "SurfaceItemID"; param1.paramObject = surfaceItemId; StoredProcParams.Add(param1); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "Type"; param2.paramObject = type; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "DateFrom"; param3.paramObject = from; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "DateTo"; param4.paramObject = to; StoredProcParams.Add(param4); oDynamicParam param5 = new oDynamicParam(); param5.paramDisplayName = "Statuses"; param5.paramObject = statuses; StoredProcParams.Add(param5); //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oPurchases); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetPurchasesData"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, useService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = interaction.interSet.Tables[0]; } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion #region subjectives public static DataTable GetSubjectivesBySurfaceItemId(int surfaceItemId) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); interaction.identityField = "surfaceItemId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(ovMedicalPatientSubjectiveDetails); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSubjectivesBySurfaceItemId"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, false); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } public static DataTable GetSubjectivesAndNotesBySurfaceItemId(int surfaceItemId) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "surfaceItemId"; param.paramObject = surfaceItemId; StoredProcParams.Add(param); interaction.identityField = "surfaceItemId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(ovMedicalPatientSubjectiveDetails); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSubjectivesAndNotesBySurfaceItemId"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, false); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } #endregion #region debtors /// /// Build Debtors Invoice Number /// /// /// public static string BuildDebtorsInvoiceNumber(int accountRecId, bool userService = false) { string result = ""; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.query = "SELECT 'INV' + CONVERT(VARCHAR(2), RIGHT(dbo.GetFiscalYear(A.dateOfTransaction), 2)) + REPLACE(STR(A.invoiceNo, 6), SPACE(1), '0') AS InvoiceNumber FROM pal_Account a WHERE a.recId = " + accountRecId; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { result = Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Account Balance /// /// /// public static decimal GetAccountBalance(int surfaceItemId, bool userService = false) { decimal result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.query = "SELECT SUM(amount) As Balance FROM pal_Account Where surfaceItemId = " + surfaceItemId + " AND procedureType <> 'PA' AND procedureType <> 'CA' "; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { decimal.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// DataTable of debtors Age Report /// /// /// /// public static DataTable CreateDebtorsAgeReport(DateTime serviceDate, int filter, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "ServiceDate"; param.paramObject = serviceDate; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "BalanceFilter"; param2.paramObject = filter; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DebtorsAgeReport"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get The Debtors Age Report Detailed Result /// /// 1 = ALL not zero 2 = Debit balances only 3 = Credit balances only 4 = All Accounts /// public static DataTable CreateDebtorsAgeReportResult(DateTime serviceDate, int filter, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { /* CVH 2016-06-29 Modified procedure to run for specific date, not using pal_Ageing */ List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "ServiceDate"; param.paramObject = serviceDate; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "BalanceFilter"; param2.paramObject = filter; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DebtorsAgeReportResult"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get The Debtors Assisted Report Detailed Result /// /// public static DataTable CreateDebtorsAssistedReportResult(bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DebtorsAssistedReportResult"; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get The Debtors Assisted Report Detailed Result /// /// public static DataTable CreateDebtorsAssistedReportResult(DateTime assistDate, bool userService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); try { //CVH 2019-05-27 TSP-31 - Assistants Report (Add Date Selection) List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "ServiceDate"; param.paramObject = assistDate; StoredProcParams.Add(param); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DebtorsAssistedReportResult"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) result = interaction.interSet.Tables[0]; else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return result; } /// /// Get Next Inv /// /// public static int GetNextInvNo(bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.query = "SELECT IsNull(Max(invoiceNo), 0) + 1 As InvNo FROM pal_Account "; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Next Receipt /// /// public static int GetNextRecNo(bool userService = false) { int result = 0; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); //CVH 2016-09-15 This should be pal_Account //interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Sales "; interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Account "; //fetch data interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, false, userService); if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0) { int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result); } else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } /// /// Get Invoice No /// /// /// /// public static int GetInvoiceNo(DateTime dtDateOfService, int surfaceItemId, int placeOfService, string icd10, bool userService = false) { DataTable result = new DataTable(); int invNo = 0; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "placeOfService"; param3.paramObject = placeOfService; StoredProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "icd10"; param4.paramObject = icd10; StoredProcParams.Add(param4); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetInvoiceNo"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; invNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return invNo; } /// /// Get Next Sequence No /// /// /// /// public static int GetNextSequence(DateTime dtDateOfService, int surfaceItemId, bool userService = false) { DataTable result = new DataTable(); int seqNo = 1; oInteraction interaction = new oInteraction(); try { List StoredProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "serviceDate"; param.paramObject = dtDateOfService; StoredProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "surfaceItemId"; param2.paramObject = surfaceItemId; StoredProcParams.Add(param2); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetNextSequence"; interaction.interStoredProcParams = StoredProcParams; interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, "pal_", false, userService); if (interaction.interSucess) { result = interaction.interSet.Tables[0]; seqNo = Convert.ToInt32(result.Rows[0][0]); } else { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } catch (Exception ex) { throw ex; } return seqNo; } #endregion #region cookie consent /// /// Logs Cookie consent data into database /// /// Cookie Name /// Cookie value stored as useridentifier:yes /// true or false /// public static bool LogCookieConsent(string cookieName, string cookieValue, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = typeof(oTariffRate); interaction.query = "INSERT INTO pal_CookieConsent ([ConsentName] ,[ConsentValue] ,[ConsentDateTime]) "; interaction.query += $"VALUES ('{cookieName}','{cookieValue}','{DateTime.Now.ToUniversalTime()}')"; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion #region update single critical milestone path /// /// Update critical milestone path for a procedure /// /// /// /// public static bool DeleteCriticalMilestone(string StyleID, int ParentSurfaceItemId, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { //define interaction interaction.identityField = "itemID"; interaction.interactionType = enums.InteractionType.Delete; interaction.interType = typeof(oTariffRate); interaction.query = "delete from qt_AdminCriticalPathMilestones "; interaction.query += " WHERE ParentSurfaceItemId = " + ParentSurfaceItemId + " and SurfaceId=3069 and CriticalPathMilestones_MilestoneDetails_StyleID = '" + StyleID + "'"; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } #endregion /// /// Update Rates for a procedure /// /// /// /// public static bool UpdateStyleZipSizes(string stylesize, string zipsize, int itemId, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); try { //define interaction interaction.interactionType = enums.InteractionType.Update; interaction.interType = typeof(oTariffRate); interaction.query = "UPDATE publ_StyleZipSizes SET StyleZipSizes_ZipSizeDetails_ZipSize = '" + zipsize + "' "; interaction.query += "WHERE StyleZipSizes_ZipSizeDetails_StyleSize = '" + stylesize + "' and itemID = " + itemId + " "; //fetch collection interaction = ProcesDynamicInteraction(interaction, _ConnectionKey, false, _tablePrefix, true, userService); if (interaction.interSucess) result = interaction.interSucess; else { if (interaction.interExceptions.Count > 0) { Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception("")); throw ex; } } } catch (Exception ex) { throw ex; } return result; } public static void UpdateOrderStatus(int Id, int OrderStatus, int surfaceId) { SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conn"]); string query = string.Empty; query = $"update publ_Orders Set Orders_OrderDetails_OrderStatus = "+ Convert.ToInt32(OrderStatus) +" where ItemID =" + Convert.ToInt32(Id) + " and surfaceId=" + Convert.ToInt32(surfaceId) + ""; SqlCommand cmd = new SqlCommand(query, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } private static void SaveErrorLogToFile(string methodName,Type type,string tablePrefix,object obj,Exception ex) { StringBuilder sb = new StringBuilder(); sb.AppendLine($" Type: {type.FullName}"); sb.AppendLine($" Table Prefix: {tablePrefix}"); if(obj != null) { sb.AppendLine($" Data: {JsonConvert.SerializeObject(obj)}"); } sb.AppendLine($"Error Message: {ex.Message}"); sb.AppendLine($"Stack Trace: {ex.StackTrace}"); logWriter.LogInfo(sb.ToString(), methodName); } } }