public static DataTable ExecuteQuery(LoginUser loginUser, SqlCommand command)
{
DataTable result = new DataTable();
BaseCollection.FixCommandParameters(command);
using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
e.Data["CommandText"] = command.CommandText;
ExceptionLogs.LogException(loginUser, e, "SqlExecutor.Fill");
throw;
}
connection.Close();
}
return(result);
}
public virtual void ExecuteNonQuery(SqlCommand command, string tableName)
{
FixCommandParameters(command);
using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString))
{
//SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
connection.Open();
command.Connection = connection;
//command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
//transaction.Commit();
}
catch (Exception e)
{
//transaction.Rollback();
e.Data["CommandText"] = command.CommandText;
ExceptionLogs.LogException(LoginUser, e, "BaseCollection.ExecuteNonQuery");
throw;
}
connection.Close();
}
if (DataCache != null)
{
DataCache.InvalidateItem(tableName, _loginUser.OrganizationID);
}
}
public static object ExecuteScalar(LoginUser loginUser, SqlCommand command)
{
BaseCollection.FixCommandParameters(command);
using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString))
{
object o;
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
o = command.ExecuteScalar();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
e.Data["CommandText"] = command.CommandText;
ExceptionLogs.LogException(loginUser, e, "SqlExecutor.ExecuteScalar");
throw;
}
connection.Close();
if (o == null || o == DBNull.Value)
{
return(null);
}
return(o);
}
}
public virtual void Fill(SqlCommand command, string tableNames, bool includeSchema = true)
{
FixCommandParameters(command);
if (_table != null)
{
_table.Dispose();
}
if (_useCache && DataCache != null)
{
_table = DataCache.GetTable(command);
if (_table != null)
{
return;
}
}
_table = new DataTable();
using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
if (includeSchema)
{
adapter.FillSchema(_table, SchemaType.Source);
}
adapter.Fill(_table);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
e.Data["CommandText"] = command.CommandText;
ExceptionLogs.LogException(LoginUser, e, "BaseCollection.Fill");
throw;
}
connection.Close();
}
if (DataCache != null)
{
if (tableNames == "")
{
tableNames = TableName;
}
DataCache.AddTable(command, tableNames, _cacheExpirationSeconds, _table, _loginUser.OrganizationID);
}
}
public void UpdateCustomFieldsFromXml(string data)
{
StringReader reader = new StringReader(data);
DataSet dataSet = new DataSet();
dataSet.ReadXml(reader);
DataRow row = dataSet.Tables[0].Rows[0];
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
foreach (CustomField field in CustomFields)
{
if (field.ApiFieldName.ToLower() == column.ColumnName.ToLower())
{
bool isValid = false;
try
{
isValid = IsValid(row[column], field.FieldType, field.IsRequired);
}
catch (Exception ex)
{
isValid = true;
//if the validation check threw an exception then set the value anyway, basically what was done before, and log the exception
string columnName = string.Empty;
string value = string.Empty;
if (column != null)
{
columnName = column.ColumnName;
if (row != null)
{
value = row[column].ToString();
}
}
ExceptionLogs.LogException(_baseCollection.LoginUser,
ex,
"BaseCollection.UpdateCustomFieldsFromXml",
string.Format("Validating {0} with value {1}",
string.IsNullOrEmpty(columnName) ? "a field" : columnName,
string.IsNullOrEmpty(value) ? "{ex.found}" : value));
}
if (isValid)
{
field.SetValue(PrimaryKeyID, row[column]);
}
break;
}
}
}
}
private static void PostSyncData(string url, string json)
{
try
{
string result = "";
using (var client = new WebClient())
{
client.Headers[HttpRequestHeader.ContentType] = "application/json";
result = client.UploadString(url, "POST", json);
}
// possible to do something with result
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "TeamSupportSync", url + "-> " + json);
}
}
public static List <DataRow> GetWhiteListTicketPathsByHubID(int customerHubID)
{
List <DataRow> result = new List <DataRow>();
using (SqlConnection connection = new SqlConnection(LoginUser.Anonymous.ConnectionString))
{
DataTable dt = new DataTable();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT TicketID, CNameURL, PortalName
FROM [Tickets] AS Tickets
INNER JOIN [CustomerHubs] AS Hubs
ON Tickets.OrganizationID = Hubs.OrganizationID
INNER JOIN [CustomerHubFeatureSettings] AS HubFeatures
ON Hubs.CustomerHubID = HubFeatures.CustomerHubID
AND HubFeatures.EnableKnowledgeBase = 1
AND HubFeatures.EnableAnonymousProductAssociation = 0
AND HubFeatures.EnableCustomerSpecificKB = 0
LEFT JOIN Products AS Products
ON Products.ProductFamilyID = Hubs.ProductFamilyID
WHERE
IsKnowledgeBase = 1
AND IsVisibleOnPortal = 1
AND Hubs.CustomerHubID = @CustomerHubID
AND (Hubs.ProductFamilyID IS NULL OR Products.ProductFamilyID IS NOT NULL)" ;
command.Parameters.AddWithValue("@CustomerHubID", customerHubID);
connection.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
try
{
adapter.Fill(dt);
result.AddRange(dt.AsEnumerable().Select(x => x).ToList());
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector");
return(null);
}
}
}
return(result);
}
public virtual object ExecuteScalar(SqlCommand command, string tableNames)
{
FixCommandParameters(command);
using (SqlConnection connection = new SqlConnection(_loginUser.ConnectionString))
{
object o;
if (_useCache && DataCache != null)
{
o = DataCache.GetScalar(command);
if (o != null)
{
return(o);
}
}
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
o = command.ExecuteScalar();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
e.Data["CommandText"] = command.CommandText;
ExceptionLogs.LogException(LoginUser, e, "BaseCollection.ExecuteScalar");
throw;
}
connection.Close();
if (DataCache != null)
{
if (tableNames == "")
{
tableNames = TableName;
}
DataCache.AddScalar(command, tableNames, _cacheExpirationSeconds, o, _loginUser.OrganizationID);
}
return(o);
}
}
private void Debug(string message)
{
try
{
System.Collections.Specialized.NameValueCollection settings = System.Configuration.ConfigurationManager.AppSettings;
string orgIdToDebug = settings["DebugOrgId"];
if (OrganizationId.ToString() == orgIdToDebug)
{
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
string debugFile = System.IO.Path.Combine(path, "Debug.txt");
System.IO.File.AppendAllText(debugFile, string.Format("{0} (orgId {1}): {2}{3}", DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"), orgIdToDebug, message, Environment.NewLine));
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "ScheduledReports-Debug", "Tickets.Clone - Actions");
}
}
private string SendAPIRequest(HttpWebRequest request)
{
string ResponseText = "";
try
{
using (WebResponse response = request.GetResponse())
{
if (request.HaveResponse && response != null)
{
using (StreamReader reader = new StreamReader(response.GetResponseStream(), ASCIIEncoding.UTF8))
{
ResponseText = reader.ReadToEnd();
}
}
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector");
}
return(ResponseText);
}
private async Task <string> SendAPIAsyncRequest(HttpWebRequest request)
{
string ResponseText = "";
try
{
using (WebResponse response = await request.GetResponseAsync().ConfigureAwait(false))
{
if (request.HaveResponse && response != null)
{
using (StreamReader reader = new StreamReader(response.GetResponseStream(), ASCIIEncoding.UTF8))
{
ResponseText = reader.ReadToEnd();
}
}
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Deflector");
}
return(ResponseText);
}
public SlaLevel Clone(string cloneName = null)
{
int cloneSlaLevelId = 0;
LoginUser loginUser = Collection.LoginUser;
SlaLevels slaLevels = new SlaLevels(loginUser);
SlaLevel clone = slaLevels.AddNewSlaLevel();
clone.OrganizationID = OrganizationID;
if (string.IsNullOrEmpty(cloneName))
{
clone.Name = Name + " (Clone)";
}
else
{
clone.Name = cloneName;
}
clone.Collection.Save();
cloneSlaLevelId = clone.SlaLevelID;
string actionLog = string.Format("{0} cloned SLA Level {1} into {2}.", loginUser.GetUserFullName(), this.Name, clone.Name);
ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog);
//Clone SLA's triggers
SlaTriggers clonedSlaTriggers = new SlaTriggers(loginUser);
SlaTriggers originalSlaTriggers = new SlaTriggers(loginUser);
try
{
originalSlaTriggers.LoadBySlaLevel(OrganizationID, SlaLevelID);
foreach (SlaTrigger slaTrigger in originalSlaTriggers.OrderBy(o => o.SlaTriggerID).ToList())
{
SlaTrigger clonedSlaTrigger = clonedSlaTriggers.AddNewSlaTrigger();
slaTrigger.ClonePropertiesTo(clonedSlaTrigger);
clonedSlaTrigger.SlaLevelID = cloneSlaLevelId;
}
clonedSlaTriggers.BulkSave();
}
catch (Exception ex)
{
actionLog = string.Format("Failed to clone sla {0} Triggers into {1}.", this.SlaLevelID, clone.SlaLevelID);
ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog);
ExceptionLogs.LogException(loginUser, ex, "Cloning Sla Triggers", "SlaLevels.Clone - Triggers");
}
//Clone SLA's Pause on specific days
try
{
clonedSlaTriggers = new SlaTriggers(loginUser);
clonedSlaTriggers.LoadBySlaLevel(OrganizationID, cloneSlaLevelId);
foreach (SlaTrigger slaTrigger in originalSlaTriggers)
{
SlaPausedDays clonedSlaPausedDays = new SlaPausedDays(loginUser);
SlaPausedDays originalSlaPausedDays = new SlaPausedDays(loginUser);
originalSlaPausedDays.LoadByTriggerID(slaTrigger.SlaTriggerID);
int newTriggerId = clonedSlaTriggers.Where(p => p.TicketTypeID == slaTrigger.TicketTypeID && p.TicketSeverityID == slaTrigger.TicketSeverityID && p.SlaLevelID == cloneSlaLevelId).First().SlaTriggerID;
foreach (SlaPausedDay slaPausedDay in originalSlaPausedDays.OrderBy(o => o.DateToPause).ToList())
{
SlaPausedDay clonedSlaPausedDay = clonedSlaPausedDays.AddNewSlaPausedDay();
clonedSlaPausedDay.SlaTriggerId = newTriggerId;
clonedSlaPausedDay.DateToPause = slaPausedDay.DateToPauseUtc;
}
clonedSlaPausedDays.BulkSave();
}
}
catch (Exception ex)
{
actionLog = string.Format("Failed to clone sla {0} DaysToPause into {1}.", this.SlaLevelID, clone.SlaLevelID);
ActionLogs.AddActionLog(loginUser, ActionLogType.Insert, ReferenceType.Sla, cloneSlaLevelId, actionLog);
ExceptionLogs.LogException(loginUser, ex, "Cloning Sla Pause On Days", "SlaLevels.Clone - Pause On Days");
}
return(clone);
}
public static void WriteEvent(TSEventLogEventType tsEventLogEventType, HttpRequest httpRequest = null, User user = null, Organization organization = null, string[] extraInfo = null)
{
try
{
string source = "TeamSupport";
if (EventLog.SourceExists(source))
{
List <object> prams = extraInfo == null ? new List <object>() : new List <object>(extraInfo);
EventLogEntryType eventLogEntryType = EventLogEntryType.Information;
switch (tsEventLogEventType)
{
case TSEventLogEventType.LoginSuccess:
prams.Add("User logged in");
eventLogEntryType = EventLogEntryType.SuccessAudit;
break;
case TSEventLogEventType.LogoutSuccess:
prams.Add("User logged out");
eventLogEntryType = EventLogEntryType.SuccessAudit;
break;
case TSEventLogEventType.FailedLoginAttempt:
prams.Add("Failed log in attempt");
eventLogEntryType = EventLogEntryType.FailureAudit;
break;
case TSEventLogEventType.AccountLocked:
prams.Add("Account locked out");
eventLogEntryType = EventLogEntryType.Warning;
break;
default:
break;
}
if (httpRequest != null)
{
prams.Add(string.Format("IPAddress: {0}", httpRequest.UserHostAddress));
}
if (organization != null)
{
prams.Add(string.Format("OrganizationID: {0}", organization.OrganizationID.ToString()));
prams.Add(string.Format("Account: {0}", organization.Name));
}
if (user != null)
{
prams.Add(string.Format("UserID: {0}", user.UserID.ToString()));
prams.Add(string.Format("User: {0}", user.FirstLastName));
prams.Add(string.Format("Email: {0}", user.Email));
}
EventLog.WriteEvent(source, new EventInstance((int)tsEventLogEventType, 0, eventLogEntryType), prams.ToArray());
//EventLog.WriteEntry(source, message, eventLogEntryType, (int)tsEventLogEventType, (short)tsEventLogCategoryType);
}
else
{
// ExceptionLogs.AddLog(LoginUser.Anonymous, "TeamSupport has not been setup as a source in the event logs.");
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(LoginUser.Anonymous, ex, "Event Logs", "Error writing event log");
}
}
private void GetChildrenByParentValue(int organizationID, int?auxID, int ticketID, int parentID, string parentValue, int?productID, ref DataTable result)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
SELECT
Distinct
cv.CustomValueID,
cv.RefID,
cv.CustomValue,
cv.DateCreated,
cv.DateModified,
cv.CreatorID,
cv.ModifierID,
cv.ImportFileID,
cf.Name,
cf.ApiFieldName,
cf.FieldType,
cf.ListValues,
cf.Description,
cf.RefType,
cf.AuxID,
cf.Position,
cf.IsVisibleOnPortal,
cf.IsFirstIndexSelect,
cf.IsRequired,
cf.OrganizationID,
cf.CustomFieldID,
cf.IsRequiredToClose,
cf.Mask,
cf.CustomFieldCategoryID,
CASE WHEN EXISTS(SELECT NULL FROM CustomFields cf2 WHERE cf2.ParentCustomFieldID = cf.CustomFieldID) THEN 1 ELSE 0 END As IsConditionalParent
FROM
CustomFields cf
LEFT JOIN CustomValues cv
ON cv.CustomFieldID = cf.CustomFieldID
AND cv.RefID = @RefID
WHERE
cf.OrganizationID = @OrganizationID
AND cf.IsVisibleOnPortal = 1
AND cf.RefType = 17
AND (cf.AuxID = @AuxID OR @AuxID < 0)
AND cf.ParentCustomFieldID = @ParentID
AND
(
cf.ParentCustomValue = @ParentValue
OR
(
@ParentValue IS NULL
AND cf.ParentCustomValue IS NULL
)
)
AND
(
cf.ParentProductID = @ProductID
OR cf.ParentProductID IS NULL
)
ORDER BY
cf.Position";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@OrganizationID", organizationID);
command.Parameters.AddWithValue("@RefID", ticketID);
command.Parameters.AddWithValue("@AuxID", auxID ?? -1);
command.Parameters.AddWithValue("@ParentID", parentID);
command.Parameters.AddWithValue("@ParentValue", parentValue);
command.Parameters.AddWithValue("@ProductID", productID ?? -1);
DataTable children = new DataTable();
using (SqlConnection connection = new SqlConnection(LoginUser.ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(children);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
ExceptionLogs.LogException(LoginUser, ex, "Children Custom Values", DataUtils.GetCommandTextSql(command));
throw;
}
connection.Close();
}
int childID = -1;
string childValue = string.Empty;
for (int i = 0; i < children.Rows.Count; i++)
{
result.ImportRow(children.Rows[i]);
childID = (int)children.Rows[i]["CustomFieldID"];
childValue = children.Rows[i]["CustomValue"].ToString();
GetChildrenByParentValue(organizationID, auxID, ticketID, childID, childValue, productID, ref result);
}
}
private DataTable GetParentsByReferenceType(int organizationID, int?auxID, int ticketID, int?parentProductID)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
SELECT
Distinct
cv.CustomValueID,
cv.RefID,
cv.CustomValue,
cv.DateCreated,
cv.DateModified,
cv.CreatorID,
cv.ModifierID,
cv.ImportFileID,
cf.Name,
cf.ApiFieldName,
cf.FieldType,
cf.ListValues,
cf.Description,
cf.RefType,
cf.AuxID,
cf.Position,
cf.IsVisibleOnPortal,
cf.IsFirstIndexSelect,
cf.IsRequired,
cf.OrganizationID,
cf.CustomFieldID,
cf.IsRequiredToClose,
cf.Mask,
cf.CustomFieldCategoryID,
CASE WHEN EXISTS(SELECT NULL FROM CustomFields cf2 WHERE cf2.ParentCustomFieldID = cf.CustomFieldID) THEN 1 ELSE 0 END As IsConditionalParent
FROM
CustomFields cf
LEFT JOIN CustomValues cv
ON cv.CustomFieldID = cf.CustomFieldID
AND cv.RefID = @RefID
WHERE
cf.OrganizationID = @OrganizationID
AND cf.IsVisibleOnPortal = 1
AND cf.RefType = 17
AND (cf.AuxID = @AuxID OR @AuxID < 0)
AND cf.ParentCustomFieldID IS NULL
AND (cf.ParentProductID IS NULL OR cf.ParentProductID = @ParentProductID)
ORDER BY
cf.CustomFieldCategoryID asc, cf.Position asc";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@OrganizationID", organizationID);
command.Parameters.AddWithValue("@RefID", ticketID);
command.Parameters.AddWithValue("@AuxID", auxID ?? -1);
command.Parameters.AddWithValue("@ParentProductID", parentProductID ?? -1);
DataTable result = new DataTable();
using (SqlConnection connection = new SqlConnection(LoginUser.ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
ExceptionLogs.LogException(LoginUser, ex, "Parent Custom Values", DataUtils.GetCommandTextSql(command));
throw;
}
connection.Close();
}
return(result);
}
