using framework_library; using System; using System.Collections; using System.Collections.Generic; using System.Data; namespace framework_business { public class xDataAlt { #region constants private const string _ConnectionKey = "connAlt"; private const string _tablePrefix = ""; #endregion #region Dynamic Iteractions #region Interaction Wrapper /// /// Byte[] to process Dynamic interactions /// /// /// /// /// /// /// private static Byte[] ProcesDynamicInteraction(byte[] interactionData, string connectionKey, bool isTransactional, string tablePrefix, bool isCollection, bool useService) { Byte[] result = null; try { if (useService)//connect to wcf service { framework_service.Iframework_serviceClient serviceCaller = new framework_service.Iframework_serviceClient("Iframework_service"); interactionData = serviceCaller.ProcessInteraction(interactionData, connectionKey, isTransactional, tablePrefix, isCollection); serviceCaller.Close(); } else { dynamicInteraction dynaInteract = new dynamicInteraction(); interactionData = dynaInteract.ProcessInteraction(interactionData, connectionKey, isTransactional, tablePrefix, isCollection); } result = interactionData; } 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; if (orderBy != String.Empty) { interaction.orderClause = orderBy; } interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 > 9 && criteria.Substring(0, 9) == "~DATEONLY") { criteriaName = "CAST(" + criteriaName + " 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 ("; foreach (string item in value.Substring(3).Split('|')) { interaction.whereClause += utils.formatSqlString(item) + ", "; } 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 interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 ("; foreach (string item in value.Substring(3).Split('|')) { interaction.whereClause += utils.formatSqlString(item) + ", "; } 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 interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' "; } } if (orderBy != String.Empty) { interaction.orderClause = orderBy; } //process the interaction over wcf protocol or directly with dll interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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) { 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.query = customQuery; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interaction.includeDynamics = true; if (virtualColumnList != null) interaction.interCustomSubQueries = virtualColumnList; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 > 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.interactionType = enums.InteractionType.Select; interaction.query = customQuery; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.orderClause = orderBy; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; interaction.interObject = obj; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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; } /// /// 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; foreach (object obj in objList) { interaction.interObjects.Add(obj); } interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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; } /// /// 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; foreach (object obj in objList) { interaction.interObjects.Add(obj); } interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 UpdateTyped(string identityField, string identityValue, Type type, Object obj, string tablePrefix = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; interaction.interObject = obj; interaction.whereClause = identityField + " = " + identityValue; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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; } /// /// Run a custom query /// /// /// /// public static bool RunCustomQuery(string query, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); Byte[] interationData; try { interaction.interactionType = enums.InteractionType.Update; interaction.query = query; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 = _tablePrefix, bool userService = false) { bool result = false; oInteraction interaction = new oInteraction(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Select; interaction.interType = type; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Insert; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Update; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = identityField; interaction.interactionType = enums.InteractionType.Verify; interaction.interType = type; interaction.interObject = obj; interaction.interStoredProc = true; interaction.interStoredProcName = StoredProcName; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 //custom stored procedures #region Custom Stored Procedure Calls /// /// 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 userLink = 0, string filter = "", bool useService = false) { DataTable result = new DataTable(); oInteraction interaction = new oInteraction(); Byte[] interationData; 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); interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oSurface); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_GetSurfaceDataPaged"; interaction.interStoredProcParams = StoredProcParams; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, useService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 #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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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; 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(); Byte[] interationData; 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_Procedure "; //fetch collection interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 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) { 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(); Byte[] interationData; 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 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); //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) { DataTable result = new DataTable(); int invNo = 0; oInteraction interaction = new oInteraction(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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.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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, useService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, false); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, false); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 /// /// Get Account Balance /// /// /// public static decimal GetAccountBalance(int surfaceItemId, bool userService = false) { decimal result = 0; oInteraction interaction = new oInteraction(); Byte[] interationData; 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 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) { 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; try { interaction.identityField = "recId"; interaction.interactionType = enums.InteractionType.Select; interaction.interType = typeof(oAccount); interaction.interStoredProc = true; interaction.interStoredProcName = "sp_DebtorsAssistedReportResult"; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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 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 GetNextRecNo(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(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 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 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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(); Byte[] interationData; 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; interationData = utils.ObjectToByteArray(interaction); interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService); interaction = (oInteraction)utils.ByteArrayToObject(interationData); 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 } }