public static int GetRequestCountInLastDays(LoginUser loginUser, int organizationID, int days)
{
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT COUNT(*) FROM ChatRequests cr WHERE cr.OrganizationID = @OrganizationID AND DATEDIFF(day, DateCreated, GETUTCDATE()) < @Days";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@OrganizationID", organizationID);
command.Parameters.AddWithValue("@Days", days);
return(SqlExecutor.ExecuteInt(loginUser, command));
}
public static LookupFieldValues GetValues(LoginUser loginUser, int reportTableFieldID, string term, int maxRows)
{
LookupFieldValues result = new LookupFieldValues();
ReportTableField field = ReportTableFields.GetReportTableField(loginUser, reportTableFieldID);
if (field == null || field.LookupTableID == null)
{
return(null);
}
ReportTable table = ReportTables.GetReportTable(loginUser, (int)field.LookupTableID);
SqlCommand command = new SqlCommand();
string[] orgs = table.OrganizationIDFieldName.Split(',');
StringBuilder orgFields = new StringBuilder("(");
foreach (String s in orgs)
{
if (orgFields.Length > 1)
{
orgFields.Append(" OR " + s + " = @OrganizationID");
}
else
{
orgFields.Append(s + " = @OrganizationID");
}
}
orgFields.Append(")");
string text = "SELECT TOP {0} {1} AS Label, {2} AS ID FROM {3} WHERE {4} AND {1} LIKE '%' + @Term + '%' ORDER BY {5}";
command.CommandText = string.Format(text,
maxRows.ToString(),
table.LookupDisplayClause,
table.LookupKeyFieldName,
table.TableName,
orgFields.ToString(),
table.LookupOrderBy);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@Term", term);
command.Parameters.AddWithValue("@OrganizationID", loginUser.OrganizationID);
DataTable dataTable = SqlExecutor.ExecuteQuery(loginUser, command);
foreach (DataRow row in dataTable.Rows)
{
result.Add((int)row[1], row[0].ToString());
}
return(result);
}
public static int GetTicketActionCount(LoginUser loginUser, int ticketID)
{
int cnt = 0;
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "SELECT COUNT(*) FROM ACTIONS WHERE TICKETID = @TicketID";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@TicketID", ticketID);
object o = SqlExecutor.ExecuteScalar(loginUser, command);
if (o == DBNull.Value)
{
return(-1);
}
cnt = (int)o;
}
return(cnt);
}
public static void UpdateValue(LoginUser loginUser, int customFieldID, int refID, string value)
{
if (value == null)
{
value = "";
}
value = value.Trim();
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "uspSetCustomValue";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CustomFieldID", customFieldID);
command.Parameters.AddWithValue("@RefID", refID);
command.Parameters.AddWithValue("@CustomValue", value);
command.Parameters.AddWithValue("@ModifierID", loginUser.UserID);
SqlExecutor.ExecuteNonQuery(loginUser, command);
}
}
public static int GetCheckSum(LoginUser loginUser, ReferenceType refType)
{
string sql = "SELECT CHECKSUM_AGG(CHECKSUM(*)) FROM {0} WHERE OrganizationID = " + loginUser.OrganizationID.ToString();
switch (refType)
{
case ReferenceType.ActionTypes:
sql = string.Format(sql, "ActionTypes");
break;
case ReferenceType.CustomFields:
sql = string.Format(sql, "CustomFields");
break;
case ReferenceType.Groups:
sql = string.Format(sql, "Groups");
break;
case ReferenceType.PhoneTypes:
sql = string.Format(sql, "PhoneTypes");
break;
case ReferenceType.Products:
sql = string.Format(sql, "Products");
break;
case ReferenceType.ProductVersions:
sql = "SELECT CHECKSUM_AGG(CHECKSUM(p.Name, pv.VersionNumber)) FROM ProductVersions pv LEFT JOIN Products p ON p.ProductID = pv.ProductID WHERE p.OrganizationID = " + loginUser.OrganizationID.ToString();
break;
case ReferenceType.ProductVersionStatuses:
sql = string.Format(sql, "ProductVersionStatuses");
break;
case ReferenceType.ProductFamilies:
sql = string.Format(sql, "ProductFamilies");
break;
case ReferenceType.TicketSeverities:
sql = string.Format(sql, "TicketSeverities");
break;
case ReferenceType.TicketStatuses:
sql = string.Format(sql, "TicketStatuses");
break;
case ReferenceType.TicketTypes:
sql = string.Format(sql, "TicketTypes");
break;
case ReferenceType.Users:
//sql = "SELECT CHECKSUM_AGG(CHECKSUM(FirstName, LastName, Email, IsActive, MarkDeleted, TimeZoneID, CultureName, IsSystemAdmin, IsChatUser, InOffice, InOfficeComment)) FROM Users WHERE OrganizationID = " + loginUser.OrganizationID.ToString();
sql = "SELECT CHECKSUM_AGG(CHECKSUM(FirstName, LastName, InOfficeComment)) FROM Users WHERE OrganizationID = " + loginUser.OrganizationID.ToString();
break;
case ReferenceType.TicketNextStatuses:
sql = "SELECT CHECKSUM_AGG(CHECKSUM(*)) FROM TicketNextStatuses tns LEFT JOIN TicketStatuses ts ON ts.TicketStatusID = tns.CurrentStatusID WHERE ts.OrganizationID = " + loginUser.OrganizationID.ToString();
break;
case ReferenceType.ForumCategories:
sql = string.Format(sql, "ForumCategories");
break;
default:
return(0);
}
SqlCommand command = new SqlCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
object o = SqlExecutor.ExecuteScalar(loginUser, command);
if (o == null || o == DBNull.Value)
{
return(0);
}
return((int)o);
}
