public static ActionCount[] GetData(DateTime start, DateTime end)
{
List <ActionCount> result = new List <ActionCount>();
SqlCommand commandCreated = new SqlCommand(@"SELECT CONVERT(VARCHAR,dt.dtime,1) AS 'Date', COUNT(a.DateCreated) AS 'ActionsAdded'
FROM dbo.udfDateTimes(@StartDate,@EndDate,1,'day') AS dt
LEFT JOIN (SELECT a.DateCreated, u.OrganizationID FROM Actions a INNER JOIN Users u ON a.CreatorID = u.UserID) a ON CAST(FLOOR(CAST(dt.dtime AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(a.DateCreated AS FLOAT)) AS DATETIME) AND a.OrganizationID = @organizationID
GROUP BY dt.dtime
ORDER BY dt.dtime");
commandCreated.Parameters.AddWithValue("@organizationid", UserSession.LoginUser.OrganizationID);
commandCreated.Parameters.AddWithValue("@StartDate", start);
commandCreated.Parameters.AddWithValue("@EndDate", end);
DataTable actionsAdded = SqlExecutor.ExecuteQuery(UserSession.LoginUser, commandCreated);
foreach (DataRow thisRow in actionsAdded.Rows)
{
ActionCount counts = new ActionCount();
counts.ActionDate = (string)thisRow["Date"];
counts.Count = (int)thisRow["ActionsAdded"];
result.Add(counts);
}
return(result.ToArray());
}
public static TicketCounts[] GetData(DateTime start, DateTime end)
{
List <TicketCounts> result = new List <TicketCounts>();
SqlCommand commandCreated = new SqlCommand(@"SELECT CONVERT(VARCHAR,dt.dtime,1) AS 'Date', ISNULL(TicketsClosed,0) AS 'TicketsClosed', ISNULL(TicketsCreated,0) AS 'TicketsCreated'
FROM dbo.udfDateTimes(@StartDate,@EndDate,1,'day') AS dt
LEFT JOIN (SELECT CAST(FLOOR(CAST(DateClosed AS FLOAT)) AS DATETIME) AS 'Date', COUNT(*) AS 'TicketsClosed' FROM Tickets WHERE organizationid = @organizationid AND DateClosed BETWEEN DATEADD(DAY,-1,@StartDate) AND DATEADD(DAY,1,@EndDate)
GROUP BY CAST(FLOOR(CAST(DateClosed AS FLOAT)) AS DATETIME)) t ON t.Date = CAST(FLOOR(CAST(dt.dtime AS FLOAT)) AS DATETIME)
LEFT JOIN (SELECT CAST(FLOOR(CAST(DateCreated AS FLOAT)) AS DATETIME) AS 'Date', COUNT(*) AS 'TicketsCreated' FROM Tickets WHERE organizationid = @organizationid AND DateCreated BETWEEN DATEADD(DAY,-1,@StartDate) AND DATEADD(DAY,1,@EndDate)
GROUP BY CAST(FLOOR(CAST(DateCreated AS FLOAT)) AS DATETIME)) t2 ON t2.Date = CAST(FLOOR(CAST(dt.dtime AS FLOAT)) AS DATETIME)
ORDER BY dt.dtime");
commandCreated.Parameters.AddWithValue("@organizationid", UserSession.LoginUser.OrganizationID);
commandCreated.Parameters.AddWithValue("@StartDate", start);
commandCreated.Parameters.AddWithValue("@EndDate", end);
DataTable createdClosed = SqlExecutor.ExecuteQuery(UserSession.LoginUser, commandCreated);
foreach (DataRow thisRow in createdClosed.Rows)
{
TicketCounts counts = new TicketCounts();
counts.TicketDate = (string)thisRow["Date"];
counts.ClosedCount = (int)thisRow["TicketsClosed"];
counts.CreatedCount = (int)thisRow["TicketsCreated"];
result.Add(counts);
}
return(result.ToArray());
}
public static SourceCount[] GetData(DateTime start, DateTime end)
{
List <SourceCount> result = new List <SourceCount>();
SqlCommand command = new SqlCommand(@"SELECT ISNULL(TicketSource,'Agent') AS 'TicketSource', COUNT(*) AS 'Number'
FROM Tickets
WHERE OrganizationID = @organizationID
AND DateCreated > @StartDate AND DateCreated < DATEADD(DAY,1,@EndDate)
GROUP BY ISNULL(TicketSource,'Agent')");
command.Parameters.AddWithValue("@organizationid", UserSession.LoginUser.OrganizationID);
command.Parameters.AddWithValue("@StartDate", start);
command.Parameters.AddWithValue("@EndDate", end);
DataTable ticketSource = SqlExecutor.ExecuteQuery(UserSession.LoginUser, command);
foreach (DataRow thisRow in ticketSource.Rows)
{
SourceCount counts = new SourceCount();
counts.Name = (string)thisRow["TicketSource"];
counts.Count = (int)thisRow["Number"];
result.Add(counts);
}
return(result.ToArray());
}
public static TicketCounts[] GetData()
{
List <TicketCounts> result = new List <TicketCounts>();
SqlCommand command = new SqlCommand(@"select p.name as ProductName,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'issues'
and p.productid = p2.productid) as NumIssues,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'tasks'
and p.productid = p2.productid) as NumTasks,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'bugs'
and p.productid = p2.productid) as NumBugs,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'features'
and p.productid = p2.productid) as NumFeatures
From Products as p
where p.organizationid = @OrganizationID
order by p.name");
command.Parameters.AddWithValue("@OrganizationID", UserSession.LoginUser.OrganizationID);
DataTable productTickets = SqlExecutor.ExecuteQuery(UserSession.LoginUser, command);
foreach (DataRow thisRow in productTickets.Rows)
{
TicketCounts counts = new TicketCounts();
counts.Product = (string)thisRow["ProductName"];
counts.Issues = (int)thisRow["NumIssues"];
counts.Tasks = (int)thisRow["NumTasks"];
counts.Bugs = (int)thisRow["NumBugs"];
counts.Features = (int)thisRow["NumFeatures"];
result.Add(counts);
}
return(result.ToArray());
}
public void EmptyQueriesCauseFailedResult()
{
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(" ");
var result = application.ExecuteQuery("");
Assert.Equal("Query is empty", ExpectError(result));
}
public void BadlyFormedQueriesCauseFailedResult()
{
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(" ");
var result = application.ExecuteQuery("SELECTEROO");
Assert.Equal("Could not find stored procedure 'SELECTEROO'.", ExpectError(result));
}
private DataTable GetScheduledReportsToMove(int batchSize)
{
SqlCommand command = new SqlCommand();
command.CommandText = "LoadMoveScheduledReportsQuery";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@batchSize", batchSize);
return(SqlExecutor.ExecuteQuery(_loginUser, command));
}
public static Dictionary <int, string> GetLookupValues(LoginUser loginUser, int reportTableFieldID, string term, int maxRows)
{
Dictionary <int, string> result = new Dictionary <int, string>();
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);
if (field.LookupTableID == 11 || field.LookupTableID == 17)
{
result.Add(-2, "The Report Viewer");
}
//result.Add(-1, "Unassigned");
foreach (DataRow row in dataTable.Rows)
{
result.Add((int)row[1], row[0].ToString());
}
return(result);
}
private DataTable GetAttachmentsToMove(int batchSize)
{
SqlCommand command = new SqlCommand();
command.CommandText = "LoadMoveAttachmentsQuery";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@batchSize", batchSize);
command.Parameters.AddWithValue("@source", _source);
//command.Parameters.AddWithValue("@sourceLength", _source.Length);
return(SqlExecutor.ExecuteQuery(_loginUser, command));
}
private void LoadOrgCombo()
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
SELECT OrganizationID, Name FROM Organizations WHERE ParentID=1 AND IsActive=1 ORDER BY Name";
DataTable table = SqlExecutor.ExecuteQuery(GetCorrupteLoginUser(), command);
cmbOrg.BeginUpdate();
foreach (DataRow row in table.Rows)
{
string text = string.Format("{0} ({1})", (string)row[1], ((int)row[0]).ToString());
cmbOrg.Items.Add(new ComboboxItem((int)row[0], text));
}
cmbOrg.EndUpdate();
}
public void SetUpRunsCreationSql()
{
var creationSql = @"
CREATE TABLE JustTesting (
Id INT IDENTITY NOT NULL,
Answer INT NOT null
)
INSERT INTO JustTesting (Answer) VALUES (42)
";
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(creationSql);
var result = application.ExecuteQuery("SELECT Answer FROM JustTesting").ExpectSuccess();
Assert.Equal(42, result.Rows[0].ValueAtIndex<int>(0));
}
private void AssertDisplayValue(string sqlType, string sqlValue, string expectedDisplayValue)
{
var creationSqlTemplate = @"
CREATE TABLE JustTesting (
Id INT IDENTITY NOT NULL,
Answer {0} NOT null
)
INSERT INTO JustTesting (Answer) VALUES ({1})
";
var creationSql = string.Format(creationSqlTemplate, sqlType, sqlValue);
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(creationSql);
var result = application.ExecuteQuery("SELECT Answer FROM JustTesting").ExpectSuccess();
Assert.Equal(expectedDisplayValue, result.Rows[0].DisplayValueAtIndex(0));
}
private static bool MigrationAlreadyRun(SqlExecutor executor, string hash, string dbname)
{
string sqlHashChecker = String.Format("SELECT 'x' FROM {0} WHERE Hash = '{1}'", DatabaseChangeLogTableName, hash.Replace("'", "''"));
using (var result = executor.ExecuteQuery(sqlHashChecker, dbname))
{
return result.HasRows;
}
}
private QueryResultTable ExecuteSqlOnBlankDatabase(string sql)
{
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(" ");
return application.ExecuteQuery(sql).ExpectSuccess();
}
public string GetPendoOptions()
{
LoginUser loginUser = TSAuthentication.GetLoginUser();
User user = Users.GetUser(loginUser, TSAuthentication.UserID);
Organization org = Organizations.GetOrganization(loginUser, TSAuthentication.OrganizationID);
SqlCommand command;
DataTable table;
dynamic result = new ExpandoObject();
result.apiKey = SystemSettings.ReadString(loginUser, "PendoKey", "NO API IN SYSTEMSETTINGS");
result.usePendoAgentAPI = false;
result.visitor = new ExpandoObject();
result.visitor.id = user.UserID;
result.visitor.email = user.Email;
result.visitor.role = user.Title;
result.visitor.name = user.FirstLastName;
result.visitor.dateCreated = user.DateCreated;
//result.visitor.lastLogin =
result.visitor.isAdmin = user.IsSystemAdmin;
command = new SqlCommand("SELECT COUNT(*) FROM Tickets WHERE OrganizationID = @OrganizationID AND UserID = @UserID");
command.Parameters.AddWithValue("UserID", user.UserID);
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.visitor.assignedTickets = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Tickets WHERE OrganizationID = @OrganizationID AND CreatorID = @UserID");
command.Parameters.AddWithValue("UserID", user.UserID);
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.visitor.ticketsCreated = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Actions WHERE CreatorID = @UserID");
command.Parameters.AddWithValue("UserID", user.UserID);
//result.visitor.actionsCreated = (int)SqlExecutor.ExecuteScalar(loginUser, command);
//result.visitor.groups =
result.visitor.isChatUser = user.IsChatUser;
result.account = new ExpandoObject();
result.account.id = org.OrganizationID;
result.account.name = org.Name;
result.account.planLevel = org.UserSeats == 100 ? "Trial" : "Paying";
result.account.creationDate = org.DateCreated;
result.account.isActive = org.IsActive;
//result.account.lastLogin =
result.account.seatCount = org.UserSeats;
result.account.apiEnabled = org.IsApiEnabled;
command = new SqlCommand(
@"SELECT COUNT(*) AS Cnt, TicketSource FROM Tickets
WHERE OrganizationID = @OrganizationID
AND TicketSource IS NOT NULL
AND TicketSource <> ''
GROUP BY TicketSource
");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
table = SqlExecutor.ExecuteQuery(loginUser, command);
result.account.ticketsCreatedByEmail = 0;
result.account.ticketsCreatedByFaceBook = 0;
result.account.ticketsCreatedByAgent = 0;
result.account.ticketsCreatedByForum = 0;
result.account.ticketsCreatedByWeb = 0;
result.account.ticketsCreatedByChatOffline = 0;
result.account.ticketsCreatedByMobile = 0;
result.account.ticketsCreatedByChat = 0;
foreach (DataRow row in table.Rows)
{
try
{
switch (row[1].ToString().ToLower())
{
case "forum": result.account.ticketsCreatedByForum = (int)row[0]; break;
case "agent": result.account.ticketsCreatedByAgent = (int)row[0]; break;
case "web": result.account.ticketsCreatedByWeb = (int)row[0]; break;
case "facebook": result.account.ticketsCreatedByFaceBook = (int)row[0]; break;
case "chatoffline": result.account.ticketsCreatedByChatOffline = (int)row[0]; break;
case "mobile": result.account.ticketsCreatedByMobile = (int)row[0]; break;
case "email": result.account.ticketsCreatedByEmail = (int)row[0]; break;
case "chat": result.account.ticketsCreatedByChat = (int)row[0]; break;
default:
break;
}
}
catch (Exception)
{
}
}
command = new SqlCommand("SELECT COUNT(*) FROM Tickets WHERE OrganizationID = @OrganizationID AND IsKnowledgeBase = 1");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.kbCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Tickets WHERE OrganizationID = @OrganizationID");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.ticketCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Organizations WHERE ParentID = @OrganizationID");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.customerCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM CustomFields WHERE OrganizationID = @OrganizationID");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.customFieldCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Users WHERE OrganizationID = @OrganizationID AND MarkDeleted = 0");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.actualUsers = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Users WHERE OrganizationID = @OrganizationID AND IsSystemAdmin = 1 AND MarkDeleted = 0");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.adminCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
command = new SqlCommand("SELECT COUNT(*) FROM Imports WHERE OrganizationID = @OrganizationID");
command.Parameters.AddWithValue("OrganizationID", org.OrganizationID);
result.account.importCount = (int)SqlExecutor.ExecuteScalar(loginUser, command);
result.account.podName = SystemSettings.GetPodName();
result.account.productType = org.ProductType.ToString();
return(JsonConvert.SerializeObject(result));
}
public IEnumerable<Migration> GetMigrationsRunInDb(DirectoryInfo dbScriptsPath, Server dbServer, string dbname)
{
var migrationSqlFilesInVcs = GetSqlSriptsIn(dbScriptsPath.FullName);
if (!migrationSqlFilesInVcs.Any())
yield break;
CheckIfDatabaseChangeLogTableExists(dbServer.Databases[dbname]);
var sqlExecutor = new SqlExecutor(dbServer);
//FIXME: use hashes, not filenames!
using (SqlDataReader reader = sqlExecutor.ExecuteQuery(String.Format(
"SELECT FileName,Applied FROM {0}", DatabaseChangeLogTableName), dbname)){
while (reader.Read())
{
var migration = new Migration();
var filename = (string) reader["FileName"];
if (!filename.EndsWith(SqlSuffix))
throw new Exception(
String.Format("Expecting filename ending with '{0}' but found '{1}' in {2}'s {3} table",
SqlSuffix, filename, dbServer.Name, DatabaseChangeLogTableName));
migration.FileNameWithoutExtension = filename.Substring(0, filename.Length - SqlSuffix.Length);
migration.AppliedDate = (DateTime) reader["Applied"];
yield return migration;
}
}
}
private static string GetRowText()
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
SELECT
--r.session_id,
--r.[status],
--r.last_wait_type,
--r.command,
--DB_NAME(r.database_id) [db_name],
t.[text] AS [Query],
r.cpu_time AS [CPU],
r.reads AS [Reads],
r.writes AS [Writes],
r.total_elapsed_time AS [Time],
s.program_name AS [Program],
s.host_name AS [Host],
--r.percent_complete,
r.wait_time AS [Wait]
--r.blocking_session_id [blocked_by],whe
--r.open_transaction_count [open_tran]
,convert(varchar(max), r.plan_handle, 2) AS [Plan]
--p.query_plan
FROM
sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE
r.session_id != @@SPID
AND r.session_id IN (SELECT TOP (20) session_id FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY cpu_time DESC)
ORDER BY r.cpu_time DESC;
";
LoginUser loginUser = GetLoginUser();
DataTable table = SqlExecutor.ExecuteQuery(loginUser, command);
StringBuilder builder = new StringBuilder();
builder.AppendLine(GetHeader(table));
builder.AppendLine("<tbody>");
foreach (DataRow row in table.Rows)
{
string plan = row["Plan"].ToString();
builder.AppendLine(string.Format("<tr data-plan=\"{0}\">", plan));
foreach (DataColumn column in table.Columns)
{
string data = "";
if (column.ColumnName != "Plan")
{
data = row[column].ToString();
if (data.Length > 500)
{
data = data.Substring(0, 500) + "...";
}
}
else
{
data = "<button type=\"button\" class=\"btn btn-danger clearcache\">Clear</button>";
}
builder.AppendLine(string.Format("<td>{0}</td>", data));
}
builder.AppendLine("</tr>");
}
builder.AppendLine("</tbody>");
return(builder.ToString());
}
