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); }