private void Get_Stats()
{
string a =
"SELECT 'Всего заявок' as 'Статус', count(*) as 'Количество' FROM request WHERE Managers_id = '" +
SelectedManager + "' AND pid = 0 UNION ALL SELECT Status, count(Status) FROM request WHERE Managers_id = '" +
SelectedManager +
"' and pid = 0 and Status is not null and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' group by Status UNION ALL SELECT 'Заявок за месяц',count(*) as count FROM request WHERE Managers_id = '" +
SelectedManager +
"' and pid = 0 and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' UNION ALL SELECT 'Среднее время (ч.)', TRUNCATE((TIME_TO_SEC(lead_time) / count(*) / 60), 2) FROM request WHERE Managers_id = '" +
SelectedManager + "' AND pid = 0 and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' ";
//'%." + Date_Value.ToString("MM.yyyy")+ "%'
DTS = clsDB.Get_DataTable(a);
clsDB.Close_DB_Connection();
long totalMemory = GC.GetTotalMemory(false);
GC.Collect();
GC.Collect(1, GCCollectionMode.Forced);
GC.WaitForPendingFinalizers();
}
private void Get_StatCompany()
{
string req = "select company as 'Статус', count(*) as 'Количество' from request where company = '" +
SelectedCompany + "' and pid = 0 and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' UNION ALL select Status, count(Status) from request where company = '" +
SelectedCompany + "' and pid = 0 and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%'";
DTS = clsDB.Get_DataTable(req);
}
private void Get_DopStat()
{
string req =
"SELECT distinct count(*) as 'Количество', Substring(action, instr(action,'>') + 1, instr( action, '</') - instr(action,'>')+1-2) as 'Действие' FROM vsdesk.history where cusers_id = 'system' and action like '%статус%' and datetime like '%" +
Date_Value.ToString("MM.yyyy") + "%' group by action";
DTS = clsDB.Get_DataTable(req);
clsDB.Close_DB_Connection();
long totalMemory = GC.GetTotalMemory(false);
GC.Collect();
GC.Collect(1, GCCollectionMode.Forced);
GC.WaitForPendingFinalizers();
}
private void Get_StatManagers()
{
string req = "SELECT mfullname as 'Сотрудник', 'Всего заявок' as status, count(*) as count FROM request WHERE pid = 0 and managers_id is not null group by Managers_id " +
"UNION ALL SELECT mfullname as 'Сотрудник', Status, count(Status) FROM request WHERE pid = 0 and managers_id is not null and Status is not null and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' group by Status " +
"UNION ALL SELECT mfullname as 'Сотрудник', 'Заявок за месяц',count(*) as count FROM request WHERE pid = 0 and managers_id is not null and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' group by Managers_id " +
"UNION ALL SELECT mfullname as 'Сотрудник', 'Среднее время (ч.)', TRUNCATE((TIME_TO_SEC(lead_time) / count(*) / 60), 2) FROM request WHERE pid = 0 and managers_id is not null and Date like '%." +
Date_Value.ToString("MM.yyyy") + "%' group by Managers_id";
DTS = clsDB.Get_DataTable(req);
clsDB.Close_DB_Connection();
long totalMemory = GC.GetTotalMemory(false);
GC.Collect();
GC.Collect(1, GCCollectionMode.Forced);
GC.WaitForPendingFinalizers();
}