public List <MonitoringDTO> GetTicketsMonitoringByPeriod(string period) { var periodDate = new DateTime(int.Parse(period.Split('/')[1]), int.Parse(period.Split('/')[0]), 1); string query = @"select r.rule_name,ck.global_rule_id_bsi,m.sla_id,m.sla_name,c.customer_id,c.customer_name,CASE WHEN ck.organization_unit='' THEN null ELSE CAST (ck.organization_unit AS INTEGER) END as organization_unit_id, ou.organization_unit,ck.day_workflow,ck.month from t_catalog_kpis ck left join t_rules r on r.global_rule_id=ck.global_rule_id_bsi left join t_sla_versions s on r.sla_version_id = s.sla_version_id left join t_slas m on m.sla_id = s.sla_id left join t_customers c on m.customer_id=c.customer_id left join t_organization_units ou on CASE WHEN ck.organization_unit='' THEN null ELSE CAST (ck.organization_unit AS INTEGER) END=ou.id where s.sla_status = 'EFFECTIVE' AND m.sla_status = 'EFFECTIVE' and ck.month is not null and ck.enable=true and ck.day_workflow!=0"; using (var con = new NpgsqlConnection(_configuration.GetConnectionString("DataAccessPostgreSqlProvider"))) { var baseResult = new List <BaseMonitoringDTO>(); con.Open(); var command = new NpgsqlCommand(query, con); command.CommandType = CommandType.Text; using (var result = command.ExecuteReader()) { while (result.Read()) { var monitorDTO = new BaseMonitoringDTO(); monitorDTO.GlobalRuleName = result.GetString(result.GetOrdinal("rule_name")); monitorDTO.GlobalRuleId = result.GetInt32(result.GetOrdinal("global_rule_id_bsi")); monitorDTO.ContractId = result.GetInt32(result.GetOrdinal("sla_id")); monitorDTO.ContractName = result.GetString(result.GetOrdinal("sla_name")); monitorDTO.ContractPartyId = result.GetInt32(result.GetOrdinal("customer_id")); monitorDTO.ContractPartyName = result.GetString(result.GetOrdinal("customer_name")); monitorDTO.OrganizationUnitId = (result[result.GetOrdinal("organization_unit_id")] == DBNull.Value)?null: (int?)result.GetInt32(result.GetOrdinal("organization_unit_id")); monitorDTO.OrganizationUnitName = (result[result.GetOrdinal("organization_unit")] == DBNull.Value) ? null : result.GetString(result.GetOrdinal("organization_unit")); monitorDTO.WorkflowDay = result.GetInt32(result.GetOrdinal("day_workflow")); monitorDTO.Months = result.GetString(result.GetOrdinal("month")); monitorDTO.TicketCreated = false; baseResult.Add(monitorDTO); } baseResult = baseResult.Where(o => o.Months.Split(',').Contains(DateTime.Now.Month + "")).ToList(); var globalRuleIds = baseResult.Select(o => o.GlobalRuleId).ToList(); var ticketsCreated = _dbcontext.SDMTicketFact.Where(o => o.period_year == periodDate.Year && o.period_month == periodDate.Month && globalRuleIds.Contains(o.global_rule_id)).ToList(); foreach (var b in baseResult) { var ticket = ticketsCreated.FirstOrDefault(o => o.global_rule_id == b.GlobalRuleId); if (ticket != null) { b.TicketCreated = true; } } var returnResult = baseResult .GroupBy(o => new { o.ContractPartyId, o.ContractPartyName, o.ContractId, o.ContractName, o.OrganizationUnitId, o.OrganizationUnitName }).Select(o => new MonitoringDTO() { ContractId = o.Key.ContractId, ContractName = o.Key.ContractName, OrganizationUnitName = o.Key.OrganizationUnitName, OrganizationUnitId = o.Key.OrganizationUnitId, ContractPartyName = o.Key.ContractPartyName, ContractPartyId = o.Key.ContractPartyId, NoOfTicketsToBeOpenedForCompletePeriod = o.Count(), NoOfTicketsToBeOpenedTillToday = o.Count(p => p.WorkflowDay <= DateTime.Now.Day), NoOfTicketsOpenedTillToday = o.Count(p => p.TicketCreated), TicketsToBeOpenedForCompletePeriod = o.Select(p => new MonitoringKPIDTO() { GlobalRuleId = p.GlobalRuleId, ContractId = o.Key.ContractId, WorkflowDay = p.WorkflowDay, ContractName = o.Key.ContractName, OrganizationUnitId = o.Key.OrganizationUnitId, OrganizationUnitName = o.Key.OrganizationUnitName, ContractPartyName = o.Key.ContractPartyName, ContractPartyId = o.Key.ContractPartyId, GlobalRuleName = p.GlobalRuleName }).ToList(), TicketsToBeOpenedTillToday = o.Where(q => q.WorkflowDay <= DateTime.Now.Day).Select(p => new MonitoringKPIDTO() { GlobalRuleId = p.GlobalRuleId, ContractId = o.Key.ContractId, WorkflowDay = p.WorkflowDay, ContractName = o.Key.ContractName, OrganizationUnitId = o.Key.OrganizationUnitId, OrganizationUnitName = o.Key.OrganizationUnitName, ContractPartyName = o.Key.ContractPartyName, ContractPartyId = o.Key.ContractPartyId, GlobalRuleName = p.GlobalRuleName }).ToList(), TicketsOpenedTillToday = ticketsCreated.Where(p => o.Select(q => q.GlobalRuleId).Contains(p.global_rule_id)).Select(r => new MonitoringTicketDTO() { GlobalRuleId = r.global_rule_id, ContractName = o.Key.ContractName, OrganizationUnitId = o.Key.OrganizationUnitId, OrganizationUnitName = o.Key.OrganizationUnitName, ContractPartyName = o.Key.ContractPartyName, ContractPartyId = o.Key.ContractPartyId, ContractId = o.Key.ContractId, CreatedOn = r.created_on, ResultValue = r.result_value, TicketId = r.ticket_id, TicketRefNumber = r.ticket_refnum }).ToList() }).ToList(); return(returnResult); } } }
public List <MonitoringDayLevelDTO> GetDayLevelTicketsMonitoring(string period) { var periodDate = new DateTime(int.Parse(period.Split('/')[1]), int.Parse(period.Split('/')[0]), 1); var Finalresult = new List <MonitoringDayLevelDTO>(); string query = @"select r.rule_name,ck.global_rule_id_bsi,m.sla_id,m.sla_name,c.customer_id,c.customer_name,CAST (ck.organization_unit AS INTEGER) as organization_unit_id,ou.organization_unit,ck.day_workflow,ck.month from t_catalog_kpis ck left join t_rules r on r.global_rule_id=ck.global_rule_id_bsi left join t_sla_versions s on r.sla_version_id = s.sla_version_id left join t_slas m on m.sla_id = s.sla_id left join t_customers c on m.customer_id=c.customer_id left join t_organization_units ou on CAST (ck.organization_unit AS INTEGER)=ou.id where s.sla_status = 'EFFECTIVE' AND m.sla_status = 'EFFECTIVE' and ck.month is not null and ck.enable=true and ck.day_workflow!=0"; using (var con = new NpgsqlConnection(_configuration.GetConnectionString("DataAccessPostgreSqlProvider"))) { var baseResult = new List <BaseMonitoringDTO>(); con.Open(); var command = new NpgsqlCommand(query, con); command.CommandType = CommandType.Text; using (var result = command.ExecuteReader()) { while (result.Read()) { var monitorDTO = new BaseMonitoringDTO(); monitorDTO.GlobalRuleName = result.GetString(result.GetOrdinal("rule_name")); monitorDTO.GlobalRuleId = result.GetInt32(result.GetOrdinal("global_rule_id_bsi")); monitorDTO.ContractId = result.GetInt32(result.GetOrdinal("sla_id")); monitorDTO.ContractName = result.GetString(result.GetOrdinal("sla_name")); monitorDTO.ContractPartyId = result.GetInt32(result.GetOrdinal("customer_id")); monitorDTO.ContractPartyName = result.GetString(result.GetOrdinal("customer_name")); monitorDTO.OrganizationUnitId = (result[result.GetOrdinal("organization_unit_id")] == DBNull.Value) ? null : (int?)result.GetInt32(result.GetOrdinal("organization_unit_id")); monitorDTO.OrganizationUnitName = (result[result.GetOrdinal("organization_unit")] == DBNull.Value) ? null : result.GetString(result.GetOrdinal("organization_unit")); monitorDTO.WorkflowDay = result.GetInt32(result.GetOrdinal("day_workflow")); monitorDTO.Months = result.GetString(result.GetOrdinal("month")); monitorDTO.TicketCreated = false; baseResult.Add(monitorDTO); } baseResult = baseResult.Where(o => o.Months.Split(',').Contains(DateTime.Now.Month + "")).ToList(); var globalRuleIds = baseResult.Select(o => o.GlobalRuleId).ToList(); var ticketsCreated = _dbcontext.SDMTicketFact.Where(o => o.period_year == periodDate.Year && o.period_month == periodDate.Month && globalRuleIds.Contains(o.global_rule_id)).ToList(); int daysInMonth = DateTime.DaysInMonth(periodDate.Year, periodDate.Month); for (int day = 1; day <= daysInMonth; day++) { var res = new MonitoringDayLevelDTO() { DayNumber = day, NoOfTicketsToBeOpenedToday = baseResult.Count(o => o.WorkflowDay == day), NoOfTicketsOpenedToday = ticketsCreated.Count(o => o.created_on.Day == day), TicketsToBeOpenedToday = baseResult.Where(o => o.WorkflowDay == day).Select(p => new MonitoringKPIDTO() { ContractName = p.ContractName, OrganizationUnitId = p.OrganizationUnitId, OrganizationUnitName = p.OrganizationUnitName, ContractPartyName = p.ContractPartyName, ContractPartyId = p.ContractPartyId, ContractId = p.ContractId, GlobalRuleId = p.GlobalRuleId, WorkflowDay = p.WorkflowDay, GlobalRuleName = p.GlobalRuleName }).ToList(), TicketsOpenedToday = ticketsCreated.Where(o => o.created_on.Day == day).Select(p => new MonitoringTicketDTO() { OrganizationUnitId = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).OrganizationUnitId, GlobalRuleId = p.global_rule_id, OrganizationUnitName = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).OrganizationUnitName, ContractPartyName = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).ContractPartyName, ContractPartyId = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).ContractPartyId, ContractId = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).ContractId, ContractName = baseResult.FirstOrDefault(o => o.GlobalRuleId == p.global_rule_id).ContractName, CreatedOn = p.created_on, TicketId = p.ticket_id, ResultValue = p.result_value, TicketRefNumber = p.ticket_refnum }).ToList() }; Finalresult.Add(res); } return(Finalresult); } } }