USE [Ghost] GO SET IDENTITY_INSERT [dbo].[Reports] ON INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (2, N'Robotlar Genel Özet', N'SET DATEFORMAT dmy SELECT R.Id as [Robot Id], R.[Name] as [Robot Adı], R.Description as [Robot Açıklama], T.Name as [Etiket Adı], R.LastRunTime as [Son Çalışma Zamanı], S.[Name] as [Senaryo Adı], NumberOfRun as [Robot Çalışma Sayısı], NumberOfError as [Hata Sayısı], CAST(100 * (CONVERT(Decimal, NumberOfRun - NumberOfError) / ISNULL(CONVERT(Decimal, NumberOfRun), 1)) AS decimal(10, 2)) as [Başarı Oranı(%)], RobotDurum as [Robot Durumu], totalRunTime as [Toplam Çalışma Süresi(dk)] FROM ( SELECT R.Id, R.Name, R.Description, R.LastRunTime, (CASE R.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS RobotDurum FROM Robots R (NOLOCK) INNER JOIN TagRobots as TR ON TR.Robot_Id = R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN --Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags LIKE ''%'' + CONVERT(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.Id ID, R.[Name], RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError, CAST((SUM(CASE WHEN ((CAST(RST.EndDate AS DATE) = CAST(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S, RST.InsertedDate, RST.EndDate) ELSE 0 END) * 1.0 / 60) AS decimal(10, 2)) AS totalRunTime FROM RobotScenarioTransactions RST (NOLOCK) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id AND RST.InsertedDate BETWEEN @StartDate AND @EndDate INNER JOIN TagRobots as TR ON TR.Robot_Id = R.Id --INNER JOIN --Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags LIKE ''%'' + CONVERT(varchar(10), TR.Tag_Id) + ''%'' INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) GROUP BY R.[Name], RST.Scenario_Id, R.Id ) AS RESULTS ON RESULTS.ID = R.Id LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id LEFT JOIN TagRobots TR (NOLOCK) ON TR.Robot_Id = R.Id LEFT JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id WHERE R.LastRunTime BETWEEN @StartDate AND @EndDate;', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T15:27:40.017' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-14T00:00:00.000' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (4, N'Senaryoların Toplam Çalışma Süresi', N'SET DATEFORMAT dmy SELECT S.Id as [Senaryo Id] ,S.[Name] as [Senaryo Adı], LastRunDate as [Son Çalışma Zamanı],totalRunMin as [Toplam Çalışma Süresi(dk)] ,NumberOfRun as [Robot Çalışma Sayısı],NumberOfError as [Hata Sayısı], CAst(100*(CONVERT(Decimal, NumberOfRun-NumberOfError)/ISNULL(CONVERT(decimal, NumberOfRun),1)) as decimal(10,2)) as [Başarı Oranı(%)] , (CASE SenaryoDurumu WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS [Senaryo Durumu],RESULTS.[TaskAdedi],RESULTS.[RobotAdı], RESULTS.[RobotId] FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id,COUNT(RST.Scenario_Id) AS NumberOfRun, SUM(RST.SuccessProcessCount) AS [TaskAdedi], SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError, CAST((SUM(CASE WHEN ((cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) AS decimal(10,2)) AS totalRunMin, MAX(RST.InsertedDate) LastRunDate , (Select IsActive from Scenarios as s where s.Id=RST.Scenario_Id ) AS SenaryoDurumu, R.Name as [RobotAdı], R.Id as [RobotId] from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN TagRobots as TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) -- INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY RST.Scenario_Id,R.IsActive, R.Name, R.Id ) AS RESULTS ON S.Id = RESULTS.Scenario_Id', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T15:32:53.873' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-14T00:00:00.000' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (6, N'Bir Senaryonun Günlük Grafiği', N'SET DATEFORMAT dmy IF(@ScenarioId = 0) SELECT RST.Scenario_Id [Senaryo Id], S.Name [Senaryo Adı], cast(RST.InsertedDate as date) [Çalışma günü], CAST((SUM(CASE WHEN ((cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) AS decimal(10,2)) AS [Toplam Çalışma Süresi(dk)], Count(RST.Robot_Id) AS [Robot Çalışma Sayısı], SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS [Hata Sayısı], (Select (CASE IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) from Scenarios as s where s.Id=RST.Scenario_Id ) AS [Senaryo Durumu], SUM(RST.SuccessProcessCount) [Task Adedi] FROM RobotScenarioTransactions AS RST WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id = RST.Scenario_Id AND RST.InsertedDate BETWEEN @StartDate AND @EndDate GROUP BY RST.Scenario_Id, cast(RST.InsertedDate as date), S.Name ELSE SELECT RST.Scenario_Id [Senaryo Id], S.Name [Senaryo Adı], cast(RST.InsertedDate as date) [Çalışma günü], CAST((SUM(CASE WHEN ((cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) AS decimal(10,2)) AS [Toplam Çalışma Süresi(dk)], Count(RST.Robot_Id) AS [Robot Çalışma Sayısı], SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS [Hata Sayısı], (Select (CASE IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) from Scenarios as s where s.Id=RST.Scenario_Id ) As [Senaryo Durumu], SUM(RST.SuccessProcessCount) [Task Adedi] FROM RobotScenarioTransactions AS RST WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id = RST.Scenario_Id AND RST.InsertedDate BETWEEN @StartDate AND @EndDate AND S.Id = @ScenarioId GROUP BY RST.Scenario_Id, cast(RST.InsertedDate as date), S.Name', 1, N'ScenarioId,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:03:28.020' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-14T00:00:00.000' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (7, N'Bir Robotun Günlük Grafiği', N'SET DATEFORMAT dmy IF (@RobotId = 0) SELECT T.Name as [Etiket Adı] , R.Id as [Robot Id] , R.Description as [Robot Açıklaması] ,R.[Name] as [Robot Adı],R.Machine as [Terminal Adı] , S.Name AS [Senaryo Adı], cast(RST.InsertedDate as date) [Çalışma Günü], CAST((SUM(CASE WHEN cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) as decimal(10,2)) AS [Toplam Çalışma Süresi(dk)], Count(RST.Robot_Id) AS [Robot Çalışma Sayısı], SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS [Hata Sayısı], CASE r.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS [Robot Durumu], RST.Message AS [Mesaj] from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id AND R.Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN TagRobots as TR ON TR.Robot_Id=R.Id LEFT JOIN Tags T WITH(NOLOCK) ON TR.Tag_Id = T.Id LEFT JOIN Scenarios S WITH(NOLOCK) ON RST.Scenario_Id = S.Id GROUP BY S.Name, RST.Message, R.[Name], cast(RST.InsertedDate as date), t.Name ,r.Id , r.Description, r.Machine,r.IsActive ELSE SELECT T.Name as [Etiket Adı] , R.Id as [Robot Id] , R.Description as [Robot Açıklaması] ,R.[Name] as [Robot Adı],R.Machine as [Terminal Adı] , S.Name AS [Senaryo Adı], cast(RST.InsertedDate as date) [Çalışma Günü], CAST((SUM(CASE WHEN cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) as decimal(10,2)) AS [Toplam Çalışma Süresi(dk)], Count(RST.Robot_Id) AS [Robot Çalışma Sayısı], SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS [Hata Sayısı], CASE r.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS [Robot Durumu], RST.Message AS [Mesaj] from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = @RobotId AND R.Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN TagRobots as TR ON TR.Robot_Id=R.Id LEFT JOIN Tags T WITH(NOLOCK) ON TR.Tag_Id = T.Id LEFT JOIN Scenarios S WITH(NOLOCK) ON RST.Scenario_Id = S.Id GROUP BY S.Name, RST.Message, R.[Name], cast(RST.InsertedDate as date), t.Name ,r.Id , r.Description, r.Machine,r.IsActive', 1, N'RobotId,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:01:26.830' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-14T00:00:00.000' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (8, N'ScenarioRunCountDashboard', N'SELECT TOP 5 Scenario_Id = T.Scenario_Id, RunCount = COUNT(*) into #TEMP FROM [RobotScenarioTransactions] AS T WITH (NOLOCK) WHERE T.InsertedDate BETWEEN @StartDate AND @EndDate GROUP BY Scenario_Id ORDER BY COUNT(*) DESC SELECT S.Id, S.Name, TMP.RunCount FROM Scenarios AS S WITH(NOLOCK) INNER JOIN #TEMP AS TMP ON TMP.Scenario_Id = S.Id ORDER BY TMP.RunCount DESC DROP TABLE #TEMP', 1, N'-', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:11:16.387' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (9, N'ScenarioRunCountDashboardWithTags', N'SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') SELECT TOP 5 Scenario_Id = T.Scenario_Id, RunCount = COUNT(*) into #TEMP FROM [RobotScenarioTransactions] AS T WITH (NOLOCK) WHERE Scenario_Id in (Select Scenario_Id from[dbo].[ScenarioRobots] Where Robot_Id IN (Select Id From[dbo].[Robots] R INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST))) AND T.InsertedDate BETWEEN @StartDate AND @EndDate GROUP BY Scenario_Id ORDER BY COUNT(*) DESC SELECT S.Id, S.Name, TMP.RunCount FROM Scenarios AS S WITH(NOLOCK) INNER JOIN #TEMP AS TMP ON TMP.Scenario_Id = S.Id ORDER BY TMP.RunCount DESC DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:14:00.827' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (10, N'RobotRunCountDashboard', N'SET DATEFORMAT dmy SELECT Robot_Id = T.Robot_Id, RunCount = COUNT(*) into #TEMP FROM [RobotScenarioTransactions] AS T WITH (NOLOCK) WHERE T.InsertedDate BETWEEN @StartDate AND @EndDate GROUP BY Robot_Id ORDER BY COUNT(*) DESC SELECT R.Id,R.Name, R.Description, TMP.RunCount FROM Robots AS R WITH(NOLOCK) INNER JOIN #TEMP AS TMP ON TMP.Robot_Id = R.Id ORDER BY TMP.RunCount DESC DROP TABLE #TEMP', 1, N'-', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:43:05.733' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (11, N'RobotRunCountDashboardWithTags', N'SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') SELECT TOP 5 Robot_Id = T.Robot_Id, RunCount = COUNT(*) into #TEMP FROM [RobotScenarioTransactions] AS T WITH (NOLOCK) WHERE Robot_Id in (Select Id From [dbo].[Robots] R INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST)) AND T.InsertedDate BETWEEN @StartDate AND @EndDate GROUP BY Robot_Id ORDER BY COUNT(*) DESC SELECT R.Id, R.Name,R.Description, TMP.RunCount FROM Robots AS R WITH(NOLOCK) INNER JOIN #TEMP AS TMP ON TMP.Robot_Id = R.Id ORDER BY TMP.RunCount DESC DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:45:30.637' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (12, N'TotalRunCountLast30Days', N'SELECT COUNT(*) FROM [RobotScenarioTransactions] WITH(NOLOCK) WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'.', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:47:27.573' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (13, N'TotalSuccessProcessCountLast30Days', N'SELECT SUM(SuccessProcessCount) FROM [Ghost].[dbo].[RobotScenarioTransactions] WITH(NOLOCK) WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'.', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:38:32.600' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (14, N'TotalErrorCountLast30Days', N'SELECT SUM(CASE IsError WHEN 1 THEN 1 ELSE 0 END) FROM [Ghost].[dbo].[RobotScenarioTransactions] WITH(NOLOCK) WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'.', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:39:35.010' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (15, N'TotalErrorCountLast30DaysWithTags', N'SELECT SUM(CASE IsError WHEN 1 THEN 1 ELSE 0 END) FROM [RobotScenarioTransactions] RST INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:51:47.503' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (16, N'TotalSuccessProcessCountLast30DaysWithTags', N'SELECT SUM(SuccessProcessCount) FROM [RobotScenarioTransactions] RST INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:54:55.067' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (17, N'TotalRunCountLast30DaysWithTags', N'SELECT COUNT(*) FROM [RobotScenarioTransactions] RST INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' WHERE StartDate >= DATEADD(month,-1,GETDATE())', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T11:57:10.697' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-20T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (18, N'RobotSavingLast12MonthsWithTags', N'SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON R.Id=TR.Robot_Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -12, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -11, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -11, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -10, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -10, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -9, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -9, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -8, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -8, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -7, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -7, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -5, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -5, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -4, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -4, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, 0, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and YEAR(RST.InsertedDate) = YEAR(GETDATE()) and MONTH(RST.InsertedDate) = MONTH(GETDATE()) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null', 1, N'SelectedTags', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T12:15:28.977' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-21T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (20, N'RobotSavingLast12Months', N'SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -12, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -11, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -11, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -10, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -10, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -9, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -9, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -8, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -8, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -7, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -7, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -5, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -5, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -4, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -4, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and RST.InsertedDate BETWEEN dateadd(MONTH, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) and dateadd(MONTH, 0, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null UNION ALL SELECT SUM(S.Price*(NumberOfRun-NumberOfError)) AS SUM1 FROM ( SELECT R.Name, R.Description,R.LastRunTime FROM Robots R (NOLOCK) INNER JOIN ( SELECT [NAME], MAX(Id) Id FROM Robots (NOLOCK) GROUP BY Name ) AS RMAX ON RMAX.NAME = R.[NAME] AND R.Id = RMAX.Id AND R.IsActive = 1 AND IsDeleted = 0 INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id ) AS R LEFT JOIN ( SELECT R.[Name],RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id and YEAR(RST.InsertedDate) = YEAR(GETDATE()) and MONTH(RST.InsertedDate) = MONTH(GETDATE()) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id GROUP BY R.[Name], RST.Scenario_Id ) AS RESULTS ON RESULTS.Name = R.Name LEFT JOIN Scenarios S (NOLOCK) ON S.Id = RESULTS.Scenario_Id and S.Price is not null', 1, N'-', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T12:16:26.413' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-21T00:00:00.000' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (21, N'RobotTimeOfRunsRobotProfile', N'Select SUM(DATEDIFF(MINUTE, StartDate, EndDate))/60 As RunTime From [Ghost].[dbo].[RobotScenarioTransactions] With (NOLOCK) Where Robot_Id = @ID and EndDate is not null', 1, N'ID', 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-03-10T12:18:19.220' AS DateTime), 1, 0, 1, N'GLB90061843', CAST(N'2020-02-21T00:00:00.000' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (22, N'ScenarioSuccessProcessCountScenarioPage', N'SELECT Robot_Id = T.Robot_Id, SuccessProcessCount = SUM(SuccessProcessCount) into #TEMP FROM [Ghost].[dbo].[RobotScenarioTransactions] AS T WITH (NOLOCK) WHERE Scenario_Id = @ID GROUP BY Robot_Id ORDER BY SUM(SuccessProcessCount) DESC SELECT R.Id, R.Name, TMP.SuccessProcessCount FROM Robots AS R WITH(NOLOCK) INNER JOIN #TEMP AS TMP ON TMP.Robot_Id = R.Id DROP TABLE #TEMP', 1, N'ID', NULL, NULL, NULL, 1, 0, 1, N'GLB90061843', CAST(N'2020-02-21T00:00:00.000' AS DateTime), 4) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (23, N'Studio Kullanıcı Aksiyonları Raporu', N'SET DATEFORMAT dmy SELECT (U.FirstName + '' '' + U.LastName) Name,U.UserName,NtUser,Machine,Domain,IpAddress,MacAddress,ActivityType,ActivityTitle,ActivityDescription,Scenario_Id,GhwName,UA.InsertedDate, (DATEDIFF(MINUTE, UA.InsertedDate,LEAD(UA.InsertedDate) over (partition by UA.NtUser order by UA.InsertedDate)) ) as ''ActivityTime'' FROM [Ghost].[dbo].[UserActions] UA WITH(NOLOCK) Left Join [Ghost].[dbo].[Users] U On UA.User_Id = U.Id Where UA.InsertedDate BETWEEN @StartDate and @EndDate Order By UA.InsertedDate', 1, N'StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:41:37.500' AS DateTime), 1, 0, 4, N'GLB90048674 (İlyas Oruç)', CAST(N'2020-03-02T14:53:57.283' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (24, N'ScheduledJobQuery', N'select j.Id, j.InvocationData, j.Arguments, s.Data from HangFire.Job j with(nolock) inner join HangFire.State s on s.JobId = j.Id where j.Arguments like ''%:'' + @RobotId +'',%'' and j.StateName=''Scheduled''', 1, N'RobotId', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:43:23.037' AS DateTime), 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:40:04.570' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (25, N'PeriodicJobQuery', N'SELECT HFS.[Value],HFH.[Value] AS Cron FROM [HangFire].[Set] AS HFS WITH(NOLOCK) ,[HangFire].[Hash] AS HFH WITH(NOLOCK) WHERE 1=1 AND HFH.[Key] = ''recurring-job:'' + HFS.[Value] AND HFS.[Key] like ''%recurring-jobs%'' AND HFS.[Value] like ''RobotId:'' + @RobotId + ''-%'' AND HFH.[Field] = ''Cron''', 1, N'RobotId', NULL, NULL, NULL, 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:40:38.707' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (26, N'ScheduledJobCalendarListQuery', N'select j.Id, j.InvocationData, j.Arguments, s.Data from HangFire.Job j with(nolock) inner join HangFire.State s on s.JobId = j.Id where StateName = ''Scheduled''', 1, N'-', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:44:13.037' AS DateTime), 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:41:01.983' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (27, N'ScheduledJobCalendarListQueryWithRobotId', N'select j.Id, j.InvocationData, j.Arguments, s.Data from HangFire.Job j with(nolock) inner join HangFire.State s on s.JobId = j.Id where StateName = ''Scheduled'' and j.Arguments like ''%:'' + @RobotId + '',%''', 1, N'RobotId', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-04-28T13:44:49.617' AS DateTime), 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:41:28.863' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (28, N'RecurringJobCalendarListQuery', N'SELECT HFS.[Value],HFH.[Value] AS Cron FROM [HangFire].[Set] AS HFS WITH(NOLOCK) ,[HangFire].[Hash] AS HFH WITH(NOLOCK) WHERE 1=1 AND HFH.[Key] = ''recurring-job:'' + HFS.[Value] AND HFS.[Key] like ''%recurring-jobs%'' AND HFH.[Field] = ''Cron'' AND HFS.[Value] like ''RobotId:%''', 1, N'-', NULL, NULL, NULL, 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:42:04.713' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (29, N'RecurringJobCalendarListQueryWithRobotId', N'SELECT HFS.[Value],HFH.[Value] AS Cron FROM [HangFire].[Set] AS HFS WITH(NOLOCK) ,[HangFire].[Hash] AS HFH WITH(NOLOCK) WHERE 1=1 AND HFH.[Key] = ''recurring-job:'' + HFS.[Value] AND HFS.[Key] like ''%recurring-jobs%'' AND HFH.[Field] = ''Cron'' AND HFS.[Value] like ''RobotId:'' +@RobotId + ''-%''', 1, N'RobotId', NULL, NULL, NULL, 1, 0, 5, N'GLB90061843 (Mehmet Arda Özdemir)', CAST(N'2020-04-07T15:43:19.020' AS DateTime), 3) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (30, N'Bir Senaryonun Hata Raporu', N'SET DATEFORMAT dmy IF(@ScenarioId = 0) Select H.EtiketAdi [Etiket Adı], S.Name [Senaryo Adı],R.Name [Robot Sicili],S.Id [Senaryo Id], CONVERT(DATE,RA.InsertedDate,108) AS [Hata Alınan Tarih], MAX(CONVERT(CHAR(8),RA.InsertedDate,108)) AS [Hata Alınan Saat],RA.Description AS [Hata Detayı] , Count(*) [İlgili Hata Sayısı], H.totalErrorByDay [Toplam Hata Sayısı], CAST((COUNT(*)) * 100 / h.totalErrorByDay as decimal(10,2)) [Hata Oranı(%)], (CASE S.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS [Senaryo Durumu] from RobotActions AS RA WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id=RA.ScenarioId INNER JOIN Robots AS R WITH(NOLOCK) ON R.Id = RA.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN TAGS AS T WITH(NOLOCK) ON T.Id = TR.Tag_Id INNER JOIN (select TEMP.EtiketAdi, TEMP.SenaryoId,TEMP.HataAlinanTarih,SUM(TEMP.ilgilihata) totalErrorByDay from (Select TT.Name EtiketAdi, S.Name SenaryoAdi,R.Name RobotSicili,S.Id SenaryoId,CONVERT(DATE,RA.InsertedDate,108) AS HataAlinanTarih, MAX(CONVERT(CHAR(8),RA.InsertedDate,108)) AS HataAlinanSaat,RA.Description AS HataDetayi,Count(*) ilgilihata from RobotActions AS RA WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id=RA.ScenarioId INNER JOIN Robots AS R WITH(NOLOCK) ON R.Id = RA.Robot_Id INNER JOIN TagRobots AS TR ON TR.Robot_Id=R.Id INNER JOIN Tags AS TT WITH(NOLOCK) ON TT.Id = TR.Tag_Id WHERE RA.Type=''Error'' AND RA.InsertedDate Between @StartDate AND @EndDate group by CONVERT(DATE,RA.InsertedDate,108), ra.Description,s.Name,r.Name,s.Id ,TT.Name ) as TEMP group by TEMP.HataAlinanTarih , TEMP.SenaryoId , TEMP.EtiketAdi) AS H ON H.SenaryoId = S.Id AND H.HataAlinanTarih = CONVERT(DATE,RA.InsertedDate,108) WHERE RA.Type=''Error'' AND RA.InsertedDate Between @StartDate AND @EndDate group by CONVERT(DATE,RA.InsertedDate,108), ra.Description,s.Name,r.Name,s.Id,H.totalErrorByDay, H.EtiketAdi,S.IsActive ELSE Select H.EtiketAdi [Etiket Adı], S.Name [Senaryo Adı],R.Name [Robot Sicili],S.Id [Senaryo Id], CONVERT(DATE,RA.InsertedDate,108) AS [Hata Alınan Tarih], MAX(CONVERT(CHAR(8),RA.InsertedDate,108)) AS [Hata Alınan Saat],RA.Description AS [Hata Detayı] , Count(*) [İlgili Hata Sayısı], H.totalErrorByDay [Toplam Hata Sayısı], CAST((COUNT(*)) * 100 / h.totalErrorByDay as decimal(10,2)) [Hata Oranı(%)], (CASE S.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS [Senaryo Durumu] from RobotActions AS RA WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id=RA.ScenarioId INNER JOIN Robots AS R WITH(NOLOCK) ON R.Id = RA.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN TAGS AS T WITH(NOLOCK) ON T.Id = TR.Tag_Id INNER JOIN (select TEMP.EtiketAdi, TEMP.SenaryoId,TEMP.HataAlinanTarih,SUM(TEMP.ilgilihata) totalErrorByDay from (Select TT.Name EtiketAdi, S.Name SenaryoAdi,R.Name RobotSicili,S.Id SenaryoId,CONVERT(DATE,RA.InsertedDate,108) AS HataAlinanTarih, MAX(CONVERT(CHAR(8),RA.InsertedDate,108)) AS HataAlinanSaat,RA.Description AS HataDetayi,Count(*) ilgilihata from RobotActions AS RA WITH(NOLOCK) INNER JOIN Scenarios AS S WITH(NOLOCK) ON S.Id=RA.ScenarioId INNER JOIN Robots AS R WITH(NOLOCK) ON R.Id = RA.Robot_Id INNER JOIN TagRobots AS TR ON TR.Robot_Id=R.Id INNER JOIN Tags AS TT WITH(NOLOCK) ON TT.Id = TR.Tag_Id WHERE RA.Type=''Error'' AND RA.InsertedDate Between @StartDate AND @EndDate AND RA.ScenarioId = @ScenarioId group by CONVERT(DATE,RA.InsertedDate,108), ra.Description,s.Name,r.Name,s.Id ,TT.Name ) as TEMP group by TEMP.HataAlinanTarih , TEMP.SenaryoId , TEMP.EtiketAdi) AS H ON H.SenaryoId = S.Id AND H.HataAlinanTarih = CONVERT(DATE,RA.InsertedDate,108) WHERE RA.Type=''Error'' AND RA.InsertedDate Between @StartDate AND @EndDate AND RA.ScenarioId = @ScenarioId group by CONVERT(DATE,RA.InsertedDate,108), ra.Description,s.Name,r.Name,s.Id,H.totalErrorByDay, H.EtiketAdi,S.IsActive', 1, N'ScenarioId,StartDate,EndDate,SelectedTags', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-06-01T16:41:50.447' AS DateTime), 1, 0, 192, N'GLB90065780 (MURAT CAN TEKİN)', CAST(N'2020-07-01T14:31:53.657' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (31, N'Robotlar Detay Raporu', N'SET DATEFORMAT dmy SELECT EtiketBilgisi as [Etiket Bilgisi],SenaryoAdi as [Senaryo Adı],RobotAdi as [Robot Adı], TerminalAdi as [Terminal Adı],CalistigiTarih as [Çalışma Günü],TaskAdeti as [Task Adedi],SonÇalışmaZamanı as [Son Çalışma Zamanı], [ToplamÇalışmaSüresi(dk)] as [Toplam Çalışma Süresi(dk)],RobotÇalışmaSayısı as [Robot Çalışma Sayısı],HataSayısı as [Hata Sayısı], SUM(CASE WHEN ACT.Tip=''Trigger'' and CalistigiTarih = CONVERT(VARCHAR(10), ACT.InsertedDate, 111) THEN 1 ELSE 0 END) AS [Günlük Manuel Tetikleme], (SUM(CASE WHEN ACT.Tip=''Run'' and CalistigiTarih = CONVERT(VARCHAR(10), ACT.InsertedDate, 111) THEN 1 ELSE 0 END) -SUM(CASE WHEN ACT.Tip=''Trigger'' and CalistigiTarih = CONVERT(VARCHAR(10), ACT.InsertedDate, 111) THEN 1 ELSE 0 END)) AS [Günlük Schedule Tetikleme], CAST(100*(CONVERT(Decimal, RobotÇalışmaSayısı-HataSayısı)/ISNULL(CONVERT(decimal, RobotÇalışmaSayısı),1)) as decimal(10,2)) as [Başarı Oranı(%)], AVG(OrtalamaIslemSuresi) as [Ortalama İşlem Süresi], (CASE [RobotDurumu] WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END)AS [Robot Durumu] FROM( SELECT T.Name AS EtiketBilgisi,S.Id SenaryoId,S.Name AS SenaryoAdi,R.Id AS RobotId,R.Name as RobotAdi,R.Machine AS TerminalAdi,cast(RST.InsertedDate AS DATE) CalistigiTarih, CONVERT(CHAR(8),MAX(RST.EndDate),108) as SonÇalışmaZamanı, CAST((SUM(CASE WHEN ((cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE)  ELSE 0 END)*1.0/60) AS decimal(10,2)) AS [ToplamÇalışmaSüresi(dk)], Count(RST.Robot_Id) AS RobotÇalışmaSayısı, SUM(RST.SuccessProcessCount) AS TaskAdeti, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS HataSayısı, AVG(CASE WHEN RST.SuccessProcessCount =0 THEN 0 ELSE DATEDIFF(s,StartDate,EndDate)/RST.SuccessProcessCount END) AS OrtalamaIslemSuresi, R.IsActive AS [RobotDurumu] FROM RobotScenarioTransactions RST WITH(NOLOCK) INNER JOIN Robots R WITH(NOLOCK) ON R.Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags T WITH(NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN Scenarios S WITH(NOLOCK) ON S.Id = RST.Scenario_Id GROUP BY T.Name,S.Id,S.Name,R.Id,R.Name,R.Machine,cast(RST.InsertedDate as date),R.IsActive ) AS MAIN LEFT JOIN ( SELECT RA.Robot_Id,RA.ScenarioId,RA.Type As Tip , RA.InsertedDate from RobotActions RA WITH(NOLOCK) WHERE RA.InsertedDate Between @StartDate AND @EndDate ) AS ACT ON ACT.Robot_Id = MAIN.RobotId AND ACT.ScenarioId = MAIN.SenaryoId GROUP BY EtiketBilgisi,SenaryoAdi,RobotAdi,TerminalAdi,CalistigiTarih,TaskAdeti,SonÇalışmaZamanı,[ToplamÇalışmaSüresi(dk)],RobotÇalışmaSayısı,HataSayısı,[RobotDurumu]', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T15:53:58.213' AS DateTime), 1, 0, 192, N'GLB90065780 (MURAT CAN TEKİN)', CAST(N'2020-09-11T09:13:35.397' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (32, N'Robot Performans Verileri', N'SET DATEFORMAT dmy SELECT * FROM ( SELECT SUM(DATEDIFF(S, [StartDate],[EndDate])*1.0/60) as RobotRunTime, t.Name as TagName ,rst.Robot_Id,r.Name as RobotName, (CASE R.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS [Robot Durumu], convert ( date,rst.InsertedDate) as StartDate,s.Name as ScenarioName, count (TR.Robot_Id) as NumberOfRun, SUM ( SuccessProcessCount) as ''Task Sayisi'' FROM [dbo].[RobotScenarioTransactions] rst WITH(NOLOCK) INNER join Robots r WITH(NOLOCK) on r.Id=rst.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=r.Id LEFT JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --left join Tags t WITH(NOLOCK) on t.Id=TR.Tag_Id and @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' Left join Scenarios s WITH(NOLOCK) on s.Id=rst.Scenario_Id where RST.InsertedDate Between @StartDate AND @EndDate group by t.Name , RST.Robot_Id,r.Name, convert ( date,rst.InsertedDate),s.Name,r.IsActive ) AS TEMP WHERE TEMP.TagName IS NOT NULL', 1, N'StartDate,EndDate,SelectedTags', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T16:08:15.323' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-11-06T13:36:47.717' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (33, N'MoneySavedDashboard', N'SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Isnull(Id,0)) From Tags END SET DATEFORMAT dmy SELECT TotalMoneySaved as TotalMoneySaved INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, cast(sum(Isnull(R.ExpectedProcessCost,0) * Isnull(SuccessProcessCount,0) * (cast(Isnull(r.ExpectedProcessTime,0) as float)/60) ) as decimal(18,2)) as TotalMoneySaved from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id INNER JOIN TagRobots TR on RST.Robot_Id=TR.Robot_Id LEFT JOIN Tags T on T.Id=TR.Tag_Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select sum(Isnull(tmp.TotalMoneySaved,0)) TotalMoneySaved from #TEMP tmp DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:47:06.623' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:22:59.453' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (34, N'TimeSaved', N'BEGIN TRY SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SET DATEFORMAT dmy SELECT TotalTimeSaved INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, sum(R.ExpectedProcessTime * SuccessProcessCount) as TotalTimeSaved from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id INNER JOIN TagRobots TR on RST.Robot_Id=TR.Robot_Id LEFT JOIN Tags T on T.Id=TR.Tag_Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select sum(tmp.TotalTimeSaved)/3600.00 TotalTimeSaved from #TEMP tmp DROP TABLE #TEMP DROP TABLE #TAG_LIST END TRY BEGIN CATCH SELECT 0 AS TotalTimeSaved END CATCH', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:48:00.770' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:23:46.473' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (35, N'Producitivity', N' SET DATEFORMAT dmy SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SELECT RST.Robot_Id,sum(DATEDIFF(MINUTE,RST.StartDate,RST.EndDate)) as total INTO #TEMP FROM [RobotScenarioTransactions] RST INNER JOIN TagRobots TR WITH(NOLOCK) ON TR.Robot_Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN Tags T WITH(NOLOCK) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT * FROM #TAG_LIST) group by RST.Robot_Id order by Robot_Id Select Sum(Total)/60 Producitivity from #TEMP DROP TABLE #TAG_LIST Drop Table #TEMP', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:49:32.347' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:24:38.300' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (36, N'GoalOfYear', N'SET DATEFORMAT dmy --DECLARE @StartDate DATETIME=''01.12.2020'' --DECLARE @EndDate DATETIME=''01.01.2021'' --DECLARE @DateDiff int=31 SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END select avg(GoalOfYear) GoalOfYear From (SELECT RST.Scenario_Id, sum(SuccessProcessCount)/ (case when ExpectedDailyProcessCount<> 0 then ExpectedDailyProcessCount * @DateDiff else @DateDiff end) * 100 as GoalOfYear from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id Inner JOIN TagRobots TR on TR.Robot_Id = RST.Robot_Id Inner join dbo.Tags T ON TR.Tag_Id = t.Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id,ExpectedDailyProcessCount) As Result DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:50:23.217' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:25:06.557' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (37, N'HourPerProcess', N' SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SET DATEFORMAT dmy SELECT TotalSaved,results.Name,StartDate INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, r.Name, DATEADD(DD, CONVERT(INT, (DATEDIFF(DD, ''1/1/1900'', RST.StartDate)/7)) * 7, ''1/1/1900'') StartDate, sum(R.ExpectedProcessTime * SuccessProcessCount)/3600.00 as TotalSaved from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id WHERE r.Id in (Select Scenario_Id from [ScenarioRobots] Where Robot_Id IN (Select Robot_Id From TagRobots TR Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST))) AND RST.InsertedDate Between @StartDate AND @EndDate GROUP BY RST.Scenario_Id,Name,StartDate ,CONVERT(INT, DATEDIFF(DD, ''1/1/1900'', StartDate)/7) ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select tmp.Name as ScenerioName ,sum(tmp.TotalSaved) TotalSaved , FORMAT(StartDate,''dd.MM.yyyy'') ProcessDate INTO #TEMP1 from #TEMP tmp where TotalSaved>0 group by tmp.Name,FORMAT(StartDate,''dd.MM.yyyy'') order by CONVERT(DATETIME, FORMAT(StartDate,''dd.MM.yyyy'')) desc ,Name SELECT ScenerioName, convert(decimal(10,2),TotalSaved) TotalSaved,ProcessDate FROM #TEMP1 tmp WHERE tmp.ProcessDate IN ( SELECT TOP 5 tmp1.ProcessDate FROM #TEMP1 tmp1 WHERE tmp1.ScenerioName = tmp.ScenerioName ORDER BY tmp1.TotalSaved DESC ) and tmp.ScenerioName IN ( SELECT TOP 5 tmp1.ScenerioName FROM #TEMP1 tmp1 WHERE tmp1.ProcessDate = tmp.ProcessDate and tmp1.TotalSaved>0 ORDER BY tmp1.TotalSaved DESC ) order by CONVERT(DATETIME, ProcessDate) DROP TABLE #TEMP DROP TABLE #TEMP1 DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:51:24.183' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:25:39.063' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (38, N'MoneyPerProcess', N'SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SET DATEFORMAT dmy SELECT TotalSaved,results.Name,StartDate INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, r.Name, RST.StartDate, CAST(sum(R.ExpectedProcessCost * SuccessProcessCount * (cast(r.ExpectedProcessTime as float)/60)) as decimal(18,2) ) as TotalSaved from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id WHERE r.Id in (Select Scenario_Id from [ScenarioRobots] Where Robot_Id IN (Select TR.Robot_Id From dbo.TagRobots TR Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST))) AND RST.InsertedDate Between @StartDate AND @EndDate GROUP BY RST.Scenario_Id,Name,StartDate ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select top 5 tmp.Name as ScenerioName ,sum(tmp.TotalSaved) TotalSaved from #TEMP tmp where TotalSaved>0 group by tmp.Name order by TotalSaved desc DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:54:28.823' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:26:08.400' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (39, N'ScenerioCount', N' SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags, ''0'')= ''0'' or Isnull(@SelectedTags, '''')= '''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar, Id) From Tags END SET DATEFORMAT dmy SELECT Scenario_Id, ScenerioCount INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, count(Scenario_Id) as ScenerioCount from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id WHERE r.Id in ( Select Scenario_Id from [ScenarioRobots] Where Robot_Id IN (Select Id From [Robots] Rbt INNER JOIN TagRobots TR on Rbt.Id=TR.Robot_Id Where TR.Tag_Id IN ( SELECT * FROM #TAG_LIST))) and r.ExpectedProcessCost>0 and SuccessProcessCount>0 AND RST.InsertedDate Between @StartDate AND @EndDate GROUP BY RST.Scenario_Id ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select count(tmp.ScenerioCount) ScenerioCount from #TEMP tmp DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:56:32.900' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:26:46.423' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (40, N'RobotCount', N'SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags, ''0'')= ''0'' or Isnull(@SelectedTags, '''')= '''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar, Id) From Tags END SET DATEFORMAT dmy select count(1) RobotCount from( SELECT rbt.Name , count(rbt.Name) as RobotCount from RobotScenarioTransactions RST (nolock) INNER JOIN Robots Rbt (NOLOCK) ON Rbt.Id = RST.Robot_Id LEFT JOIN TagRobots TR on Rbt.Id=TR.Robot_Id Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST) AND RST.InsertedDate Between @StartDate AND @EndDate GROUP BY Rbt.Name) as A DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:57:38.637' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:27:17.120' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (41, N'HourPerProcessAll', N'SET DATEFORMAT dmy SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From dbo.Tags END SELECT TotalSaved,results.Name,StartDate,TagName INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, r.Name, DATEADD(DD, CONVERT(INT, (DATEDIFF(DD, ''1/1/1900'', RST.StartDate)/7)) * 7, ''1/1/1900'') StartDate, sum(R.ExpectedProcessTime * SuccessProcessCount)/3600.00 as TotalSaved, T.[Name] TagName from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id Inner JOIN Robots RB on rb.Id = RST.Robot_Id INNER JOIN TagRobots TR ON TR.Robot_Id=RB.Id Inner join dbo.Tags T ON TR.Tag_Id = t.Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id,R.Name,StartDate ,CONVERT(INT, DATEDIFF(DD, ''1/1/1900'', StartDate)/7) ,T.Name ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select tmp.Name as [Senaryo Adı], cast(sum(tmp.TotalSaved) as decimal(10,2)) as [Süre Tasarrufu (sa)] , FORMAT(StartDate,''dd.MM.yyyy'') [Tarih] , tmp.TagName As [Etiket] from #TEMP tmp where TotalSaved>0 group by tmp.Name,FORMAT(StartDate,''dd.MM.yyyy'') ,tmp.TagName order by CONVERT(DATETIME, FORMAT(StartDate,''dd.MM.yyyy'')) desc ,Name DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:58:37.300' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:27:42.567' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (42, N'MoneyPerProcessAll', N' SET DATEFORMAT dmy SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SELECT TotalSaved,results.Name,StartDate,TagName INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN ( SELECT RST.Scenario_Id, r.Name, RST.StartDate, CAST(sum(R.ExpectedProcessCost * SuccessProcessCount * (cast(r.ExpectedProcessTime as float)/60)) as decimal(18,2) ) as TotalSaved, T.[Name] TagName from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id Inner JOIN Robots RB on rb.Id = RST.Robot_Id Inner JOIN TagRobots TR ON TR.Robot_Id=RB.Id Inner join dbo.Tags T ON TR.Tag_Id = t.Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id,r.Name,StartDate,t.Name ) AS RESULTS ON S.Id = RESULTS.Scenario_Id Select tmp.Name as [Senaryo Adı] , sum(tmp.TotalSaved) as [Finansal Tasarruf (₺)], tmp.TagName [Etiket] from #TEMP tmp where TotalSaved>0 group by tmp.Name ,TagName DROP TABLE #TEMP DROP TABLE #TAG_LIST', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T13:59:28.303' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-04T09:28:03.343' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (43, N'Robot Kullanıcı Raporu', N'IF(@RobotId = 0) SELECT R.[Id] as RobotId ,(CASE WHEN R.IsActive = 1 THEN r.Name+'' (Aktif)'' Else r.Name+'' (Pasif)'' end) as RobotAdı ,U.UserName AS SICIL ,U.FirstName+ '' ''+U.LastName AS AdSoyad FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T WITH(NOLOCK) ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN RobotUsers AS RU WITH(NOLOCK) ON RU.Robot_Id = R.Id INNER JOIN Users AS U WITH(NOLOCK) ON U.Id = RU.User_Id ELSE SELECT R.[Id] as RobotId ,(CASE WHEN R.IsActive = 1 THEN r.Name+'' (Aktif)'' Else r.Name+'' (Pasif)'' end) as RobotAdı ,U.UserName AS SICIL ,U.FirstName+ '' ''+U.LastName AS AdSoyad FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T WITH(NOLOCK) ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN RobotUsers AS RU WITH(NOLOCK) ON RU.Robot_Id = R.Id INNER JOIN Users AS U WITH(NOLOCK) ON U.Id = RU.User_Id WHERE R.Id = @RobotId', 1, N'SelectedTags,RobotId', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T16:15:49.900' AS DateTime), 1, 0, 135, N'GLB90065736 (SERTAN SAMBUR)', CAST(N'2020-12-04T11:04:12.917' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (44, N'Senaryo Kullanıcı Raporu', N'IF(@ScenarioId = 0) SELECT S.Id AS SenaryoId ,S.Name AS SenaryoAdi ,U.UserName AS SicilNo ,U.FirstName + '' '' + U.LastName AS AdiSoyadi FROM Scenarios AS S WITH(NOLOCK) INNER JOIN ScenarioUsers AS SU WITH(NOLOCK) ON SU.Scenario_Id = S.Id INNER JOIN Users AS U WITH(NOLOCK) ON U.Id = SU.User_Id where U.IsDeleted = 0 ELSE SELECT S.Id AS SenaryoId ,S.Name AS SenaryoAdi ,U.UserName AS SicilNo ,U.FirstName + '' '' + U.LastName AS AdiSoyadi FROM Scenarios AS S WITH(NOLOCK) INNER JOIN ScenarioUsers AS SU WITH(NOLOCK) ON SU.Scenario_Id = S.Id INNER JOIN Users AS U WITH(NOLOCK) ON U.Id = SU.User_Id and U.IsDeleted = 0 WHERE S.Id = @ScenarioId', 1, N'ScenarioId', 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-03-30T09:34:17.623' AS DateTime), 1, 0, 135, N'GLB90065736 (SERTAN SAMBUR)', CAST(N'2020-12-04T11:04:51.787' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (45, N'Bir Senaryoya Ait Robotlar', N'IF(@ScenarioId = 0) SELECT T.Name AS EtiketAdi ,SR.Scenario_Id AS SenaryoId ,(CASE WHEN S.IsActive = 1 THEN S.Name+'' (Aktif)'' ELSE S.Name+'' (Pasif)'' END) AS SenaryoAdi ,R.Id AS RobotId ,R.Name AS RobotAdi ,(CASE WHEN R.IsActive = 1 THEN ''Aktif'' ELSE ''Pasif'' END) as RobotDurumu FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN ScenarioRobots AS SR ON R.Id = SR.Robot_Id INNER JOIN Scenarios AS S ON SR.Scenario_Id = S.Id ELSE SELECT T.Name AS EtiketAdi ,SR.Scenario_Id AS SenaryoId ,(CASE WHEN S.IsActive = 1 THEN S.Name+'' (Aktif)'' ELSE S.Name+'' (Pasif)'' END) AS SenaryoAdi ,R.Id AS RobotId ,R.Name AS RobotAdi ,(CASE WHEN R.IsActive = 1 THEN ''Aktif'' ELSE ''Pasif'' END) as RobotAktifMi FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN ScenarioRobots AS SR ON R.Id = SR.Robot_Id INNER JOIN Scenarios AS S ON SR.Scenario_Id = S.Id AND S.Id = @ScenarioId', 1, N'SelectedTags,ScenarioId', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T16:19:15.707' AS DateTime), 1, 0, 135, N'GLB90065736 (SERTAN SAMBUR)', CAST(N'2020-12-04T11:05:36.720' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (46, N'Bir Robota Ait Senaryolar', N'IF(@RobotId = 0) SELECT T.Name AS EtiketAdi ,R.Id AS RobotId ,(CASE WHEN R.IsActive = 1 THEN R.Name+'' (Aktif)'' ELSE R.Name+'' (Pasif)'' END) AS RobotAdi ,SR.Scenario_Id AS SenaryoId ,(CASE WHEN S.IsActive = 1 THEN S.Name+'' (Aktif)'' ELSE S.Name+'' (Pasif)'' END) AS SenaryoAdi ,(CASE WHEN S.IsActive = 1 THEN ''Aktif'' ELSE ''Pasif'' END) as ''Senaryo Durumu'' FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN ScenarioRobots AS SR ON R.Id = SR.Robot_Id INNER JOIN Scenarios AS S ON SR.Scenario_Id = S.Id ELSE SELECT T.Name AS EtiketAdi ,R.Id AS RobotId ,(CASE WHEN R.IsActive = 1 THEN R.Name+'' (Aktif)'' ELSE R.Name+'' (Pasif)'' END) AS RobotAdi ,SR.Scenario_Id AS SenaryoId ,(CASE WHEN S.IsActive = 1 THEN S.Name+'' (Aktif)'' ELSE S.Name+'' (Pasif)'' END) AS SenaryoAdi ,(CASE WHEN S.IsActive = 1 THEN ''Aktif'' ELSE ''Pasif'' END) as ''Senaryo Durumu'' FROM Robots AS R WITH(NOLOCK) INNER JOIN TagRobots TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags AS T ON TR.Tag_Id = T.Id AND @SelectedTags like ''%'' + CONVERT(VARCHAR(10), TR.Tag_Id) + ''%'' INNER JOIN ScenarioRobots AS SR ON R.Id = SR.Robot_Id INNER JOIN Scenarios AS S ON SR.Scenario_Id = S.Id WHERE R.Id = @RobotId', 1, N'SelectedTags,RobotId', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T16:31:50.193' AS DateTime), 1, 0, 135, N'GLB90065736 (SERTAN SAMBUR)', CAST(N'2020-12-04T11:06:30.690' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (47, N'SuccessProcessCount', N' SET DATEFORMAT dmy SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SELECT RST.Robot_Id,sum(SuccessProcessCount) as total INTO #TEMP FROM [RobotScenarioTransactions] RST INNER JOIN TagRobots TR WITH(NOLOCK) ON TR.Robot_Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN Tags T WITH(NOLOCK) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT * FROM #TAG_LIST) group by RST.Robot_Id order by Robot_Id Select Sum(Total) SuccessProcessCount from #TEMP DROP TABLE #TAG_LIST Drop Table #TEMP', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T14:09:34.923' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-17T15:58:31.403' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (48, N'TagCount', N' SET DATEFORMAT dmy SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SELECT T.Id,count(1) as TagCount INTO #TEMP FROM [RobotScenarioTransactions] RST INNER JOIN TagRobots TR WITH(NOLOCK) ON TR.Robot_Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN Tags T WITH(NOLOCK) ON T.Id = TR.Tag_Id AND TR.Tag_Id IN (SELECT * FROM #TAG_LIST) group by t.Id Select count(TagCount) TagCount from #TEMP DROP TABLE #TAG_LIST Drop Table #TEMP', 1, N'Null', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T14:11:35.487' AS DateTime), 1, 0, 7, N'GLB90060901 (Ali Özdemir)', CAST(N'2020-12-17T15:58:52.383' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (49, N'Etiket Bazlı Senaryo Raporu', N'SELECT [Id] ,[Name] ,[UpdatedUser] ,[UpdatedDate] ,[IsActive] ,[IsDeleted] ,[InsertedUser] ,[InsertedDate] ,[ActiveVersion] ,[Version] ,[LastPublishDate] ,[Tag_Id] FROM [Ghost].[dbo].[Scenarios] with (NOLOCK)', 1, N'SelectedTags', 135, N'GLB90065736 (SERTAN SAMBUR)', CAST(N'2021-10-20T16:36:45.300' AS DateTime), 0, 1, 18, N'GLB90053568 (Ece Hazal Tosun)', CAST(N'2021-10-20T13:39:11.403' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (50, N'TagHourPerProcess', N'BEGIN TRY SET DATEFORMAT dmy declare @SelectedTags nvarchar(10) =''0'' SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SELECT * FROM (SELECT TS.Tag_Id,T.Name AS TagName,CONVERT(VARCHAR(10),DATEADD(DD, CONVERT(VARCHAR(10), (DATEDIFF(DD, ''1/1/1900'', StartDate)/7),104)*7 , ''1/1/1900''),104) as ProcessDate, sum(S.ExpectedProcessTime * SuccessProcessCount)/3600.00 AS TotalSaved FROM [dbo].Tags AS T INNER JOIN [dbo].TagScenarios AS TS ON t.Id=TS.Tag_Id INNER JOIN [dbo].Scenarios AS S ON S.Id=TS.Scenario_Id INNER JOIN RobotScenarioTransactions RST (nolock) ON TS.Scenario_Id = RST.Scenario_Id WHERE TS.Scenario_Id in (Select Scenario_Id from [ScenarioRobots] Where Robot_Id IN (Select TR.Robot_Id From [dbo].TagRobots TR Where TR.Tag_Id IN (SELECT * FROM #TAG_LIST))) AND RST.InsertedDate Between @StartDate AND @EndDate AND T.MainTag_Id is null GROUP BY TS.Tag_Id,T.Name,CONVERT(VARCHAR(10), DATEDIFF(DD, ''1/1/1900'', StartDate)/7,104)) AS result WHERE result.TotalSaved>0 order by CONVERT(DATETIME,CONVERT(VARCHAR(10), result.ProcessDate, 104)) asc DROP TABLE #TAG_LIST END TRY BEGIN CATCH SELECT '''' AS TagName, 0.0 TotalSaved, ''01-01-1900'' AS ProcessDate END CATCH', 1, N'StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T14:13:29.957' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-01-28T13:19:25.643' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (51, N'TagMoneyPerProcess', N'declare @SelectedTags nvarchar(10) =''0'' SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SET DATEFORMAT dmy Select RESULTS.Tag_Id,RESULTS.Name,RESULTS.TotalSaved INTO #TAGTEMP From Tags T (NOLOCK) INNER JOIN( SELECT R.Tag_Id,ts.Name,RST.StartDate,CAST(sum(S.ExpectedProcessCost * SuccessProcessCount * (cast(S.ExpectedProcessTime as float)/60)) as decimal(18,2) ) as TotalSaved from Tags as ts (NOLOCK) INNER JOIN TagScenarios R ON ts.Id=R.Tag_Id INNER JOIN Scenarios S ON R.Scenario_Id=S.Id INNER JOIN RobotScenarioTransactions RST (nolock) ON R.Scenario_Id = RST.Scenario_Id WHERE r.Scenario_Id in (Select Scenario_Id from [ScenarioRobots] Where Robot_Id IN (Select Rbt.Robot_Id From TagRobots Rbt Where Rbt.Tag_Id IN (SELECT * FROM #TAG_LIST))) AND RST.InsertedDate Between @StartDate AND @EndDate GROUP BY R.Tag_Id,StartDate,ts.Name ) as RESULTS ON T.Id=RESULTS.Tag_Id and T.MainTag_Id is null Select tmp.Tag_Id as ID,tmp.Name as TagName ,sum(tmp.TotalSaved) TotalSaved from #TAGTEMP tmp where TotalSaved>0 group by tmp.Tag_Id,tmp.Name order by TotalSaved desc DROP TABLE #TAG_LIST DROP TABLE #TAGTEMP', 1, N'StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T14:18:56.377' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-01-28T13:19:53.223' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (52, N'Tanım Kullanıcı Raporu', N'SELECT D.Id AS DefinationId, D.Name As DefinationName, U.UserName, U.FirstName, U.LastName FROM Definitions AS D (nolock) INNER JOIN UserDefinitions AS UD (nolock) on UD.Definition_Id=D.Id INNER JOIN Users AS U (nolock) on U.Id=UD.User_Id Where D.IsActive=1 and U.IsActive=1', 1, N'StartDate,EndDate', NULL, NULL, NULL, 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-03-10T15:49:15.860' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (53, N'Senaryolar Genel Özet Raporu', N'SET DATEFORMAT dmy SELECT * FROM ( SELECT s.Id as ''Senaryo Id'', s.Name as ''Senaryo AdI'', s.InsertedDate as ''ilk olusturulma Tarihi'',s.InsertedUser as ''ilk Olusturan kullanici'', s.InsertedUserId ''ilk Oluşturan kullanici Id'', s.UpdatedDate as ''Son Guncelleme Tarihi'' , s.UpdatedUser ''Son Guncelleme Yapan kullanici AdI'', s.UpdatedUserId ''Son Guncelleme Yapan kullanici Id'', CASE s.IsDeleted WHEN 1 THEN ''Evet'' ELSE ''Hayir'' END AS ''Senaryo Silinmis mi'' , s.ActiveVersion as ''Aktif Versiyon'', s.Version as ''Versiyon'', s.Description as ''Senaryo AcIklama'', s.LastPublishDate as ''Senaryo Son Devreye AlIm Tarihi'', CASE s.IsNewRpaPeriod WHEN 0 THEN ''Mevcut'' ELSE ''Yeni'' END AS ''RPA Surec'' , CASE s.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS ''Senaryo Durumu'', t.Name as ''Ana Etiket-Etiket'', CASE WHEN t.MainTag_Id IS NULL THEN (SELECT [Name] FROM [Tags] where Id=t.Id) ELSE (SELECT [Name]FROM[Tags] where Id=t.MainTag_Id) END AS ''AnaEtiket'', s.ExpectedDailyProcessCount as ''Senaryo Tahmini gunluk islem adedi'', s.ExpectedProcessCost as ''1 dakika maliyeti'', s.ExpectedProcessTime as ''Bir islem icin gecen sure'' FROM Scenarios s with (nolock) INNER JOIN TagScenarios TS ON TS.Scenario_Id=S.Id LEFT JOIN Tags T (nolock) ON t.Id=TS.Tag_Id AND TS.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --LEFT JOIN Tags t ON t.Id=TS.Tag_Id and @SelectedTags like ''%'' + Convert(varchar(10), TS.Tag_Id) + ''%'' ) AS TEMP WHERE TEMP.[Ana Etiket-Etiket] IS NOT NULL and TEMP.[ilk olusturulma Tarihi] Between @StartDate AND @EndDate', 1, N'StartDate,EndDate,SelectedTags', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-26T16:35:18.133' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-03-14T13:58:45.830' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (54, N'Senaryolar Kullanım Raporu', N' SET DATEFORMAT dmy SELECT * FROM ( SELECT s.Id as SenaryoId, s.Name as SenaryoAdi, s.InsertedDate as SenaryoInsertedDate,s.UpdatedDate as SenaryoUpdatedDate, s.LastPublishDate as ''Senaryo Last Update'', U.UserName, U.FirstName,U.LastName, CASE s.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS [Senaryo Durumu], t.Name as ''AnaEtiket/Etiket'', CASE WHEN t.MainTag_Id IS NULL THEN (SELECT Name FROM [Tags] where Id=t.Id) ELSE (SELECT Name FROM [Tags] where Id=t.MainTag_Id) END AS ''AnaEtiket'', s.ExpectedDailyProcessCount as ''Senaryo Tahmini günlük işlem adedi'', s.ExpectedProcessCost as ''1 dakika maliyeti'', s.ExpectedProcessTime as ''Bir işlem için geçen süre'' FROM Scenarios S with (nolock) INNER JOIN ScenarioUsers SU ON S.Id=SU.Scenario_Id INNER JOIN Users U (nolock) ON U.Id= SU.[User_Id] INNER JOIN TagScenarios TS ON TS.Scenario_Id=S.Id LEFT JOIN Tags T ON T.Id=TS.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), ts.Tag_Id) + ''%'') AS TEMP WHERE TEMP.[AnaEtiket/Etiket] IS NOT NULL and TEMP.[SenaryoInsertedDate] Between @StartDate AND @EndDate', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-03-10T14:23:03.513' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-03-15T09:51:01.713' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (55, N'Robot ve Senaryo Verileri Raporu', N'SET DATEFORMAT dmy SELECT R.Id as''Robot Id'',R.Name as'' Robot Adı'', R.Machine as ''Robot Makina Adi'', s.Id as ''Senaryo Id'', s.Name as ''Senaryo Adi'', s.InsertedDate as ''ilk olusturulma Tarihi'',s.InsertedUser as ''Ilk Olusturan Kullanici'', s.UpdatedDate as ''Son Guncelleme Tarihi'',s.UpdatedUser ''Son Guncelleme Yapan Kullanici Adi'', s.LastPublishDate as ''Senaryo Son Devreye Alim Tarihi'', s.ExpectedProcessTime as ''Bir islem icin Harcanan Süre'' ,t.name as ''Etiket Adı'', R.[LastRunTime] as ''Robot Son Çalışma Zamanı'', CASE S.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS ''Senaryo Durumu'', CASE S.IsDeleted WHEN 1 THEN ''Evet'' ELSE ''Hayır'' END AS ''Senaryo Silinmis mi'', CASE R.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS ''Robot Durumu'' from Robots R (nolock) INNER JOIN TagRobots TR (nolock) ON TR.Robot_Id=R.Id INNER JOIN ScenarioRobots SR (nolock) ON R.Id=SR.Robot_Id INNER JOIN Scenarios S (nolock) ON S.Id= SR.Scenario_Id INNER JOIN Tags T (nolock) ON T.Id = TR.Tag_Id WHERE s.InsertedDate BETWEEN @StartDate AND @EndDate AND TR.Tag_Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) --INNER JOIN Tags T (nolock) --ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' --WHERE s.InsertedDate Between @StartDate AND @EndDate', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-12-25T09:29:17.773' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2022-11-30T13:44:52.003' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (56, N'EffortDayGain', N'BEGIN TRY  SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','')  IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')=''''   BEGIN   Insert Into #TAG_LIST    Select CONVERT(varchar,Id) From  Tags  END  SET DATEFORMAT dmy     SELECT  TotalTimeSaved   INTO #TEMP FROM  Scenarios S (NOLOCK)   INNER JOIN  (SELECT RST.Scenario_Id,     sum(R.ExpectedProcessTime * SuccessProcessCount) as TotalTimeSaved from RobotScenarioTransactions RST (nolock)        INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id       INNER JOIN TagRobots TR on RST.Robot_Id=TR.Robot_Id        LEFT JOIN Tags T on T.Id=TR.Tag_Id    where  RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST)  GROUP BY  RST.Scenario_Id  ) AS RESULTS  ON S.Id = RESULTS.Scenario_Id     Select  (SUM(tmp.TotalTimeSaved)/32400) as EffortDayGain from #TEMP tmp      DROP TABLE #TEMP     DROP TABLE #TAG_LIST  END TRY   BEGIN CATCH    SELECT 0 AS EffortDayGain  END CATCH', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-08T16:56:37.640' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-08T15:56:12.913' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (57, N'EffortHumanGain', N'BEGIN TRY  SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','')  IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')=''''   BEGIN   Insert Into #TAG_LIST    Select CONVERT(varchar,Id) From  Tags  END  SET DATEFORMAT dmy     declare @totalDay int; set @totalDay =  (SELECT ShifyDayCount FROM dbo.DatetimeDiffShifyDay (@StartDate,@EndDate)); if(@totalDay > 250) set @totalDay = 250;   SELECT  TotalTimeSaved   INTO #TEMP FROM  Scenarios S (NOLOCK)   INNER JOIN  (SELECT RST.Scenario_Id,     sum(R.ExpectedProcessTime * SuccessProcessCount) as TotalTimeSaved from RobotScenarioTransactions RST (nolock)        INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id       INNER JOIN TagRobots TR on RST.Robot_Id=TR.Robot_Id        LEFT JOIN Tags T on T.Id=TR.Tag_Id    where  RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST)  GROUP BY  RST.Scenario_Id  ) AS RESULTS  ON S.Id = RESULTS.Scenario_Id     Select  (SUM(tmp.TotalTimeSaved)/32400) / @totalDay as EffortHumanGain from #TEMP tmp      DROP TABLE #TEMP     DROP TABLE #TAG_LIST  END TRY   BEGIN CATCH    SELECT 0 AS EffortHumanGain  END CATCH', 1, N'SelectedTags,StartDate,EndDate', 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-09T11:38:24.337' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-08T16:20:40.370' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (58, N'EffortHumanGainNew', N'BEGIN TRY if(@EndDate is null or @EndDate > DATEADD(day, 1, CAST(GETDATE() AS DATE)) ) begin Select 0 as EffortHumanGainNew End Else Begin SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','') IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')='''' BEGIN Insert Into #TAG_LIST Select CONVERT(varchar,Id) From Tags END SET DATEFORMAT dmy SELECT TotalTimeSaved, IsNewRpaPeriod INTO #TEMP FROM Scenarios S (NOLOCK) INNER JOIN (SELECT RST.Scenario_Id,sum(R.ExpectedProcessTime * SuccessProcessCount) as TotalTimeSaved from RobotScenarioTransactions RST (nolock) INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id INNER JOIN TagRobots TR (NOLOCK) on RST.Robot_Id=TR.Robot_Id LEFT JOIN Tags T (NOLOCK) on T.Id=TR.Tag_Id where RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST) GROUP BY RST.Scenario_Id ) AS RESULTS ON S.Id = RESULTS.Scenario_Id declare @totalDay int; set @totalDay = (SELECT ShifyDayCount FROM dbo.DatetimeDiffShifyDay (@StartDate,@EndDate)); declare @effortHumanGain decimal; set @effortHumanGain = (Select CAST(((SUM(tmp.TotalTimeSaved)/32400) / @totalDay) AS decimal(10,2)) from #TEMP tmp ); declare @countTotal decimal; set @countTotal = (select count(#TEMP.IsNewRpaPeriod) as countTotal from #TEMP); declare @countTotalNew decimal; set @countTotalNew = (select count(#TEMP.IsNewRpaPeriod) as countTotal from #TEMP where IsNewRpaPeriod = 1); if(@countTotal=0 or @countTotalNew =0 ) begin Select 0 as EffortHumanGainNew end else begin Select (@effortHumanGain*(@countTotalNew/@countTotal )) as EffortHumanGainNew end DROP TABLE #TEMP DROP TABLE #TAG_LIST DROP TABLE #TEMPCOUNTS END END TRY BEGIN CATCH SELECT 0 AS EffortHumanGainNew END CATCH', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-08-04T12:15:03.967' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-08T17:14:31.617' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (59, N'EffortHumanGainPresent', N'BEGIN TRY if(@EndDate is null or @EndDate > DATEADD(day, 1, CAST(GETDATE() AS DATE)) ) begin Select 0 as EffortHumanGainPresent End ELSE Begin SELECT * INTO #TAG_LIST FROM STRING_SPLIT(@SelectedTags,'','')  IF Isnull(@SelectedTags,''0'')=''0'' or Isnull(@SelectedTags,'''')=''''    BEGIN  Insert Into #TAG_LIST    Select CONVERT(varchar,Id) From  Tags  END SET DATEFORMAT dmy SELECT  TotalTimeSaved,IsNewRpaPeriod   INTO #TEMP FROM  Scenarios S (NOLOCK)    INNER JOIN  (SELECT RST.Scenario_Id,sum(R.ExpectedProcessTime * SuccessProcessCount) as TotalTimeSaved from RobotScenarioTransactions RST (nolock)        INNER JOIN Scenarios R (NOLOCK) ON R.Id = RST.Scenario_Id   INNER JOIN TagRobots TR (NOLOCK)  on RST.Robot_Id=TR.Robot_Id LEFT JOIN Tags T (NOLOCK)  on T.Id=TR.Tag_Id    where  RST.InsertedDate Between @StartDate AND @EndDate AND T.Id IN (SELECT * FROM #TAG_LIST)  GROUP BY  RST.Scenario_Id  ) AS RESULTS  ON S.Id = RESULTS.Scenario_Id declare @totalDay int; set @totalDay = (SELECT ShifyDayCount FROM dbo.DatetimeDiffShifyDay (@StartDate,@EndDate)); declare @effortHumanGain decimal; set @effortHumanGain = (Select CAST(((SUM(tmp.TotalTimeSaved)/32400) / @totalDay) AS decimal(10,2)) from #TEMP tmp ); declare @countTotal decimal; set @countTotal = (select count(#TEMP.IsNewRpaPeriod) as countTotal from #TEMP); declare @countTotalPresent decimal; set @countTotalPresent = (select count(#TEMP.IsNewRpaPeriod) as countTotal from #TEMP where IsNewRpaPeriod = 0); if(@countTotal=0 or @countTotalPresent =0 ) begin Select 0 as EffortHumanGainPresent end else begin Select (@effortHumanGain*(@countTotalPresent/@countTotal )) as EffortHumanGainPresent end DROP TABLE #TEMP DROP TABLE #TAG_LIST DROP TABLE #TEMPCOUNTS END END TRY BEGIN CATCH SELECT 0 AS EffortHumanGainPresent END CATCH', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-08-04T12:15:46.907' AS DateTime), 1, 0, 286, N'glb90056280 (Şenay Enli)', CAST(N'2023-05-08T17:15:09.453' AS DateTime), 1) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (60, N'Robot Verimlilik Raporu', N'SET DATEFORMAT dmy SELECT R.Id as [Robot Id], R.[Name] as [Robot Adı], RobotDurum as [Robot Durumu], EtiketBilgisi as [Etiket Bilgisi], CAST(100*(totalRunTime/1440) AS decimal(10,2)) as [Robot Verimlilik (%)], R.LastRunTime as [Son Çalışma Zamanı], CAST(100*(CONVERT(Decimal, NumberOfRun-NumberOfError)/ISNULL(CONVERT(decimal, NumberOfRun),1)) AS decimal(10,2)) as [Başarı Oranı(%)] FROM ( SELECT R.Id, T.Name AS EtiketBilgisi, R.Name, R.LastRunTime, (CASE R.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END) AS RobotDurum FROM Robots R (NOLOCK) INNER JOIN TagRobots as TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' ) AS R LEFT JOIN ( SELECT R.Id ID, R.[Name], RST.Scenario_Id, COUNT(RST.Robot_Id) AS NumberOfRun, SUM(CASE RST.IsError WHEN 1 THEN 1 ELSE 0 END) AS NumberOfError, Cast((SUM(CASE WHEN ((cast(RST.EndDate AS DATE) = cast(RST.InsertedDate AS DATE)) OR (RST.InsertedDate < RST.EndDate)) THEN DATEDIFF(S,RST.InsertedDate,RST.ENDDATE) ELSE 0 END)*1.0/60) AS decimal(10,2)) AS totalRunTime from RobotScenarioTransactions RST (nolock) INNER JOIN Robots R (NOLOCK) ON R.Id = RST.Robot_Id AND RST.InsertedDate Between @StartDate AND @EndDate INNER JOIN TagRobots as TR ON TR.Robot_Id=R.Id INNER JOIN Tags T (NOLOCK) ON T.Id = TR.Tag_Id AND @SelectedTags like ''%'' + Convert(varchar(10), TR.Tag_Id) + ''%'' GROUP BY R.[Name] , R.Id ,RST.Scenario_Id, T.Id ) AS RESULTS ON RESULTS.ID = R.Id where R.LastRunTime Between @StartDate AND @EndDate', 1, N'SelectedTags,StartDate,EndDate', NULL, NULL, NULL, 1, 0, 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-08-18T17:41:33.170' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (61, N'Senaryo Verimlilik Raporu', N'SET DATEFORMAT dmy SELECT * FROM ( SELECT s.Id as ''Senaryo Id'', s.Name as ''Senaryo AdI'', CASE s.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS ''Senaryo Durumu'', CASE WHEN t.MainTag_Id IS NULL THEN (SELECT [Name] FROM [Tags] where Id=t.Id) ELSE (SELECT [Name]FROM[Tags] where Id=t.MainTag_Id) END AS ''AnaEtiket'', CAST( (100*(SPC.SuccessProcessCount /CASE WHEN s.ExpectedDailyProcessCount = ''0'' THEN ''1'' ELSE s.ExpectedDailyProcessCount END) ) AS decimal(10,2)) as [Senaryo Verimlilik (%)], SPC.FormattedDate as ''Çalışma Tarihi'', s.InsertedDate as ''ilk olusturulma Tarihi'', s.InsertedUser as ''ilk Olusturan kullanici'', s.InsertedUserId ''ilk Oluşturan kullanici Id'', s.UpdatedDate as ''Son Guncelleme Tarihi'', s.UpdatedUser ''Son Guncelleme Yapan kullanici AdI'', s.UpdatedUserId ''Son Guncelleme Yapan kullanici Id'', s.ExpectedDailyProcessCount as ''Senaryo Tahmini gunluk islem adedi'', s.ExpectedProcessCost as ''1 dakika maliyeti'', s.ExpectedProcessTime as ''Bir islem icin gecen sure'' FROM Scenarios s with (nolock) INNER JOIN ( Select RST.Scenario_Id as Id, SUM(RST.SuccessProcessCount) as SuccessProcessCount, FORMAT(cast(EndDate as date), ''dd-MM-yyyy'') as FormattedDate from RobotScenarioTransactions RST (nolock) where EndDate Between @StartDate AND @EndDate GROUP BY Scenario_Id,cast(EndDate as date)) AS SPC ON SPC.Id = S.ID INNER JOIN TagScenarios TS ON TS.Scenario_Id=S.Id LEFT JOIN Tags t ON t.Id=TS.Tag_Id and @SelectedTags like ''%'' + Convert(varchar(10), TS.Tag_Id) + ''%'' ) AS TEMP WHERE TEMP.[ilk olusturulma Tarihi] Between @StartDate AND @EndDate', 1, N'SelectedTags,StartDate,EndDate', NULL, NULL, NULL, 1, 0, 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-08-18T17:42:50.333' AS DateTime), 2) INSERT [dbo].[Reports] ([Id], [Name], [Query], [DbType], [RequiredProperties], [UpdatedUserId], [UpdatedUser], [UpdatedDate], [IsActive], [IsDeleted], [InsertedUserId], [InsertedUser], [InsertedDate], [Path]) VALUES (62, N'Senaryolar Finansal Raporu', N'SET DATEFORMAT dmy DECLARE @totalDay INT; SET @totalDay = (SELECT ShifyDayCount FROM dbo.DatetimeDiffShifyDay (@StartDate, @EndDate)); WITH TotalMetricsCTE AS ( SELECT S.Id AS ScenarioId, S.Name AS ''Scenario Name'', S.InsertedDate, CASE s.IsActive WHEN 1 THEN ''Aktif'' ELSE ''Pasif'' END AS ''Senaryo Durumu'', CASE s.IsNewRpaPeriod WHEN 0 THEN ''Mevcut'' ELSE ''Yeni'' END AS ''RPA Surec'', T.Name as ''Alt-Etiket'', CASE WHEN T.MainTag_Id IS NULL THEN (SELECT [Name] FROM [Tags] where Id=T.Id) ELSE (SELECT [Name]FROM[Tags] where Id=T.MainTag_Id) END AS ''Ana-Etiket'', CAST(SUM(ISNULL(S.ExpectedProcessCost, 0) * ISNULL(RST.SuccessProcessCount, 0) * (CAST(ISNULL(S.ExpectedProcessTime, 0) AS FLOAT) / 60)) AS DECIMAL(18, 2)) AS ''Total Money Saved'', SUM(ISNULL(S.ExpectedProcessTime, 0) * RST.SuccessProcessCount) / 3600.00 AS ''Total Time Saved (hr)'' FROM Scenarios AS S JOIN RobotScenarioTransactions AS RST ON S.Id = RST.Scenario_Id JOIN Robots AS R ON RST.Robot_Id = R.Id JOIN TagScenarios AS TS ON S.Id=TS.Scenario_Id JOIN Tags AS T ON TS.Tag_Id = T.Id WHERE RST.InsertedDate BETWEEN @StartDate AND @EndDate AND T.Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) GROUP BY S.Id, S.Name, S.InsertedDate, s.IsActive, IsNewRpaPeriod, T.Id, T.MainTag_Id, T.Name ) , EffortDayGainCTE AS ( SELECT S.Id AS Scenario_Id, S.Name AS ScenarioName, CAST(SUM(S.ExpectedProcessTime * RST.SuccessProcessCount) AS INT) AS TotalTimeSaved FROM Scenarios AS S JOIN RobotScenarioTransactions AS RST ON S.Id = RST.Scenario_Id JOIN Robots AS R ON RST.Robot_Id = R.Id JOIN TagScenarios AS TS ON S.Id=TS.Scenario_Id JOIN Tags AS T ON TS.Tag_Id = T.Id WHERE RST.InsertedDate BETWEEN @StartDate AND @EndDate AND T.Id IN (SELECT value FROM STRING_SPLIT(@SelectedTags, '','')) GROUP BY S.Id, S.Name ) , EffortDayGainResultsCTE AS ( SELECT S.Scenario_Id, S.ScenarioName, CAST(SUM(TotalTimeSaved) / 32400 AS DECIMAL(10, 2)) AS ''Effort Day Gain'', CAST(((SUM(TotalTimeSaved) / 32400) / CAST(@totalDay AS FLOAT)) AS DECIMAL(10, 2)) AS ''Effort Human Gain'' FROM EffortDayGainCTE AS S GROUP BY S.Scenario_Id, S.ScenarioName ) SELECT TM.ScenarioId, TM.[Scenario Name], TM.[Senaryo Durumu], TM.InsertedDate, TM.[RPA Surec], TM.[Alt-Etiket], TM.[Ana-Etiket], TM.[Total Money Saved], TM.[Total Time Saved (hr)], EDG.[Effort Day Gain], EDG.[Effort Human Gain] FROM TotalMetricsCTE AS TM JOIN EffortDayGainResultsCTE AS EDG ON TM.ScenarioId = EDG.Scenario_Id;', 1, N'SelectedTags,StartDate,EndDate', 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-09-25T23:06:43.113' AS DateTime), 1, 0, 512, N'GLB90084432 (FEYZA DENİZ GÜNDÜZ)', CAST(N'2023-08-18T17:50:47.433' AS DateTime), 2) SET IDENTITY_INSERT [dbo].[Reports] OFF GO