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