college2 (Jorhat Medical College & Hospital) Complete Database
1. Tables
USE [college2]
GO
/****** Object: Table [dbo].[temp_unique_class_status] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temp_unique_class_status](
[record_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[class_id] [uniqueidentifier] NOT NULL,
[class_of_date] [date] NULL,
[weekday] [nvarchar](50) NULL,
[batch_id] [uniqueidentifier] NULL,
[class_start_time] [datetime] NULL,
[class_end_time] [datetime] NULL,
[class_subject_or_department] [nvarchar](250) NULL,
[class_topic] [nvarchar](250) NULL,
[enroll_id] [numeric](18, 0) NOT NULL,
[uid] [uniqueidentifier] NULL,
[session_id] [uniqueidentifier] NULL,
[real_svr_Id] [numeric](18, 0) NULL,
[astrRemoteIP] [nvarchar](50) NULL,
[anDeviceID] [nvarchar](50) NULL,
[log_time] [datetime] NULL,
[class_status] [nvarchar](50) NULL,
CONSTRAINT [PK_temp_unique_class_status] PRIMARY KEY CLUSTERED
(
[class_id] ASC,
[enroll_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[temp_report_data] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temp_report_data](
[class_id] [uniqueidentifier] NOT NULL,
[class_of_date] [date] NOT NULL,
[weekday] [nvarchar](50) NOT NULL,
[batch_id] [uniqueidentifier] NULL,
[class_start_time] [datetime] NOT NULL,
[class_end_time] [datetime] NOT NULL,
[class_subject_or_department] [nvarchar](250) NOT NULL,
[class_topic] [nvarchar](250) NULL,
[faculty_enrollment_uid] [uniqueidentifier] NULL,
[id] [uniqueidentifier] NOT NULL,
[enroll_id] [numeric](18, 0) NOT NULL,
[enroll_name] [nvarchar](50) NULL,
[department_id] [uniqueidentifier] NULL,
[department_name] [nvarchar](50) NULL,
[batch_name] [nvarchar](50) NULL,
[session_id] [uniqueidentifier] NULL,
[session_name] [nvarchar](50) NULL,
[real_svr_Id] [numeric](18, 0) NULL,
[astrRemoteIP] [nvarchar](50) NULL,
[anDeviceID] [nvarchar](50) NULL,
[log_time] [datetime] NULL,
[class_status] [nvarchar](50) NULL,
CONSTRAINT [PK_temp_report_data] PRIMARY KEY CLUSTERED
(
[class_id] ASC,
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[temp_enrollment] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temp_enrollment](
[id] [uniqueidentifier] NOT NULL,
[enroll_id] [numeric](18, 0) NOT NULL,
[enroll_name] [nvarchar](50) NULL,
[department_id] [uniqueidentifier] NULL,
[department_name] [nvarchar](50) NULL,
[batch_id] [uniqueidentifier] NULL,
[batch_name] [nvarchar](50) NULL,
[session_id] [uniqueidentifier] NULL,
[session_name] [nvarchar](50) NULL,
CONSTRAINT [PK_temp_enrollment] PRIMARY KEY CLUSTERED
(
[enroll_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_temp_enrollment] UNIQUE NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[subject] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[subject](
[subject_id] [uniqueidentifier] NOT NULL,
[batch_id] [uniqueidentifier] NOT NULL,
[subject_name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_subject] PRIMARY KEY CLUSTERED
(
[subject_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_subject] UNIQUE NONCLUSTERED
(
[batch_id] ASC,
[subject_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[session] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[session](
[session_id] [uniqueidentifier] NOT NULL,
[session_name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_session] PRIMARY KEY CLUSTERED
(
[session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[real_svr] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[real_svr](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[uid] [uniqueidentifier] NULL,
[aEnrollNumber] [numeric](18, 0) NOT NULL,
[aVerifyMode] [nvarchar](50) NULL,
[aInOutMode] [numeric](18, 0) NULL,
[adtLog] [datetime] NULL,
[abDrawFlag] [bit] NULL,
[astrRemoteIP] [nvarchar](50) NULL,
[anRemotePort] [numeric](18, 0) NULL,
[anDeviceID] [nvarchar](50) NULL,
[astrSerialNo] [nvarchar](50) NULL,
[astrRouterIP] [nvarchar](50) NULL,
[batch_id] [uniqueidentifier] NULL,
[class_id] [uniqueidentifier] NULL,
[department_id] [uniqueidentifier] NULL,
[timing_hour] [numeric](18, 0) NULL,
CONSTRAINT [PK_real_svr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[machine] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[machine](
[vnMachineNumber] [numeric](18, 0) NOT NULL,
[vpszIPAddress] [nvarchar](15) NOT NULL,
[vpszNetPort] [numeric](18, 0) NOT NULL,
[vpszNetPassword] [numeric](18, 0) NOT NULL,
[serverIP] [nvarchar](15) NOT NULL,
[serverPort] [nvarchar](50) NOT NULL,
[vnTimeOut] [numeric](18, 0) NOT NULL,
[vnProtocolType] [numeric](18, 0) NOT NULL,
[machineLocation] [nvarchar](50) NULL,
CONSTRAINT [PK_machine] PRIMARY KEY CLUSTERED
(
[vnMachineNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[generallogdata] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[generallogdata](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[anCount] [numeric](18, 0) NOT NULL,
[aStrEnrollNumber] [nvarchar](50) NOT NULL,
[aSEnrollNumber] [numeric](18, 0) NOT NULL,
[aVerifyMode] [numeric](18, 0) NOT NULL,
[aInOutMode] [numeric](18, 0) NOT NULL,
[adwDate] [datetime] NOT NULL,
[aWorkCode] [numeric](18, 0) NOT NULL,
[astrRemoteIP] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_generallogdata] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[department] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[department](
[department_id] [uniqueidentifier] NOT NULL,
[department_name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_department_1] PRIMARY KEY CLUSTERED
(
[department_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[default_weekday_period] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[default_weekday_period](
[default_weekday_period_id] [uniqueidentifier] NOT NULL,
[batch_id] [uniqueidentifier] NOT NULL,
[default_weekday_period] [nvarchar](50) NOT NULL,
[default_weekday_period_start_time] [datetime] NOT NULL,
[default_weekday_period_end_time] [datetime] NOT NULL,
[default_weekday_period_subject] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_default_weekday_period] PRIMARY KEY CLUSTERED
(
[default_weekday_period_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[batch] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[batch](
[batch_id] [uniqueidentifier] NOT NULL,
[batch_name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_batch] PRIMARY KEY CLUSTERED
(
[batch_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[unique_class] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[unique_class](
[class_id] [uniqueidentifier] NOT NULL,
[class_of_date] [date] NOT NULL,
[weekday] [nvarchar](50) NOT NULL,
[batch_id] [uniqueidentifier] NOT NULL,
[class_start_time] [datetime] NOT NULL,
[class_end_time] [datetime] NOT NULL,
[class_subject_or_department] [nvarchar](250) NOT NULL,
[class_topic] [nvarchar](250) NULL,
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[faculty_enrollment_uid] [uniqueidentifier] NULL,
CONSTRAINT [PK_unique_class] PRIMARY KEY CLUSTERED
(
[class_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[batch_student] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[batch_student](
[id] [uniqueidentifier] NOT NULL,
[batch_student_enroll_id] [numeric](18, 0) NOT NULL,
[enroll_name] [nvarchar](50) NOT NULL,
[batch_id] [uniqueidentifier] NOT NULL,
[session_id] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_batch_student_1] PRIMARY KEY CLUSTERED
(
[batch_student_enroll_id] ASC,
[batch_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_batch_student] UNIQUE NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[faculty_enrollment] Script Date: 04/29/2024 16:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[faculty_enrollment](
[id] [uniqueidentifier] NOT NULL,
[faculty_enroll_id] [numeric](18, 0) NOT NULL,
[enroll_name] [nvarchar](50) NOT NULL,
[department_id] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_faculty_enrollment] PRIMARY KEY CLUSTERED
(
[faculty_enroll_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_batch_student_batch] Script Date: 04/29/2024 16:45:42 ******/
ALTER TABLE [dbo].[batch_student] WITH CHECK ADD CONSTRAINT [FK_batch_student_batch] FOREIGN KEY([batch_id])
REFERENCES [dbo].[batch] ([batch_id])
GO
ALTER TABLE [dbo].[batch_student] CHECK CONSTRAINT [FK_batch_student_batch]
GO
/****** Object: ForeignKey [FK_batch_student_session] Script Date: 04/29/2024 16:45:42 ******/
ALTER TABLE [dbo].[batch_student] WITH CHECK ADD CONSTRAINT [FK_batch_student_session] FOREIGN KEY([session_id])
REFERENCES [dbo].[session] ([session_id])
GO
ALTER TABLE [dbo].[batch_student] CHECK CONSTRAINT [FK_batch_student_session]
GO
/****** Object: ForeignKey [FK_faculty_enrollment_department] Script Date: 04/29/2024 16:45:42 ******/
ALTER TABLE [dbo].[faculty_enrollment] WITH CHECK ADD CONSTRAINT [FK_faculty_enrollment_department] FOREIGN KEY([department_id])
REFERENCES [dbo].[department] ([department_id])
GO
ALTER TABLE [dbo].[faculty_enrollment] CHECK CONSTRAINT [FK_faculty_enrollment_department]
GO
/****** Object: ForeignKey [FK_unique_class_batch] Script Date: 04/29/2024 16:45:42 ******/
ALTER TABLE [dbo].[unique_class] WITH CHECK ADD CONSTRAINT [FK_unique_class_batch] FOREIGN KEY([batch_id])
REFERENCES [dbo].[batch] ([batch_id])
GO
ALTER TABLE [dbo].[unique_class] CHECK CONSTRAINT [FK_unique_class_batch]
GO
2. Views
USE [college2]
GO
/****** Object: View [dbo].[voter_view] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[voter_view]
AS
SELECT TOP(2000)
NEWID() AS id
,Row_Number() OVER (ORDER BY age) AS batch_student_enroll_id
,[voter_name] AS enroll_name
,'7F05DDF6-FE70-4760-9C3C-99F55156D1F1' AS batch_id
,'A590E813-E74F-4C62-B2D7-14B1CFA2CCF4' AS session_id
FROM [capital_soft].[dbo].[voter]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'voter_view'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'voter_view'
GO
/****** Object: View [dbo].[all_possible_log] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_possible_log]
AS
SELECT dbo.temp_enrollment.id, dbo.temp_enrollment.enroll_id, dbo.temp_enrollment.enroll_name, dbo.temp_enrollment.department_id, dbo.temp_enrollment.department_name,
dbo.temp_enrollment.batch_id, dbo.temp_enrollment.batch_name, dbo.temp_enrollment.session_id, dbo.temp_enrollment.session_name, dbo.real_svr.uid, dbo.real_svr.aEnrollNumber,
dbo.real_svr.aVerifyMode, dbo.real_svr.aInOutMode, dbo.real_svr.adtLog, dbo.real_svr.abDrawFlag, dbo.real_svr.astrRemoteIP, dbo.real_svr.anRemotePort, dbo.real_svr.anDeviceID,
dbo.real_svr.astrSerialNo, dbo.real_svr.astrRouterIP, dbo.real_svr.batch_id AS Expr1, dbo.real_svr.class_id, dbo.real_svr.department_id AS Expr2, dbo.real_svr.timing_hour
FROM dbo.temp_enrollment CROSS JOIN
dbo.real_svr
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "temp_enrollment"
Begin Extent =
Top = 6
Left = 38
Bottom = 203
Right = 215
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "real_svr"
Begin Extent =
Top = 5
Left = 250
Bottom = 264
Right = 410
End
DisplayFlags = 280
TopColumn = 3
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 10
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'all_possible_log'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'all_possible_log'
GO
/****** Object: View [dbo].[View_1] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT TOP (100) PERCENT dbo.real_svr.aEnrollNumber, dbo.temp_enrollment.enroll_name, dbo.temp_enrollment.batch_name, dbo.temp_enrollment.session_name,
dbo.real_svr.adtLog
FROM dbo.real_svr INNER JOIN
dbo.temp_enrollment ON dbo.real_svr.aEnrollNumber = dbo.temp_enrollment.enroll_id
WHERE (dbo.real_svr.aEnrollNumber = 8) AND (dbo.real_svr.adtLog BETWEEN CONVERT(DATETIME, '2024-01-02 07:00:00', 102) AND CONVERT(DATETIME,
'2024-01-02 23:00:00', 102))
ORDER BY dbo.real_svr.adtLog
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[15] 4[46] 2[14] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "real_svr"
Begin Extent =
Top = 9
Left = 454
Bottom = 252
Right = 614
End
DisplayFlags = 280
TopColumn = 1
End
Begin Table = "temp_enrollment"
Begin Extent =
Top = 6
Left = 236
Bottom = 233
Right = 413
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 2955
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 5430
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
/****** Object: View [dbo].[wrong_enrollment_id_matching_view] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[wrong_enrollment_id_matching_view]
AS
SELECT dbo.faculty_enrollment.enroll_name, dbo.faculty_enrollment.faculty_enroll_id, dbo.batch_student.batch_student_enroll_id, dbo.batch_student.enroll_name AS student_name
FROM dbo.faculty_enrollment INNER JOIN
dbo.batch_student ON dbo.faculty_enrollment.faculty_enroll_id = dbo.batch_student.batch_student_enroll_id
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[29] 4[32] 2[9] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "faculty_enrollment"
Begin Extent =
Top = 6
Left = 38
Bottom = 150
Right = 206
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "batch_student"
Begin Extent =
Top = 6
Left = 244
Bottom = 148
Right = 449
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 2385
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'wrong_enrollment_id_matching_view'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'wrong_enrollment_id_matching_view'
GO
/****** Object: View [dbo].[personal_report] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[personal_report]
AS
SELECT temp_enrollment_1.id, temp_enrollment_1.enroll_id, temp_enrollment_1.enroll_name, temp_enrollment_1.department_id, temp_enrollment_1.department_name,
temp_enrollment_1.batch_id, temp_enrollment_1.batch_name, temp_enrollment_1.session_id, temp_enrollment_1.session_name,
dbo.temp_unique_class_status.record_id, dbo.temp_unique_class_status.class_id, dbo.temp_unique_class_status.class_of_date,
dbo.temp_unique_class_status.weekday, dbo.temp_unique_class_status.class_start_time, dbo.temp_unique_class_status.class_end_time,
dbo.temp_unique_class_status.class_subject_or_department, dbo.temp_unique_class_status.class_topic, dbo.temp_unique_class_status.uid,
dbo.temp_unique_class_status.real_svr_Id, dbo.temp_unique_class_status.astrRemoteIP, dbo.temp_unique_class_status.anDeviceID,
dbo.temp_unique_class_status.log_time, dbo.temp_unique_class_status.class_status, dbo.unique_class.faculty_enrollment_uid,
dbo.temp_enrollment.enroll_name AS faculty_name, dbo.temp_enrollment.department_name AS faculty_department_name,
dbo.temp_enrollment.department_id AS faculty_department_id, 0 AS attendance_percentage, 0 AS attendance_of_days, 0 AS attendance_number_of_present
FROM dbo.unique_class INNER JOIN
dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[62] 4[1] 2[33] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "unique_class"
Begin Extent =
Top = 6
Left = 253
Bottom = 125
Right = 484
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_unique_class_status"
Begin Extent =
Top = 194
Left = 67
Bottom = 313
Right = 298
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment"
Begin Extent =
Top = 6
Left = 38
Bottom = 125
Right = 215
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment_1"
Begin Extent =
Top = 6
Left = 522
Bottom = 125
Right = 699
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 13
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 5055
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'personal_report'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'= 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'personal_report'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'personal_report'
GO
/****** Object: View [dbo].[report_view] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[report_view]
AS
SELECT dbo.real_svr.Id, dbo.real_svr.class_id, dbo.unique_class.class_of_date, dbo.unique_class.weekday, dbo.unique_class.class_start_time,
dbo.unique_class.class_end_time, dbo.unique_class.class_subject_or_department, dbo.unique_class.class_topic, dbo.real_svr.aEnrollNumber, dbo.real_svr.uid,
dbo.real_svr.astrRemoteIP, dbo.real_svr.anDeviceID, dbo.real_svr.adtLog, dbo.temp_enrollment.enroll_id, dbo.temp_enrollment.enroll_name,
dbo.temp_enrollment.department_id, dbo.temp_enrollment.department_name, dbo.unique_class.batch_id, dbo.batch.batch_name, dbo.temp_enrollment.session_id,
dbo.temp_enrollment.session_name, dbo.unique_class.faculty_enrollment_uid
FROM dbo.real_svr INNER JOIN
dbo.unique_class ON dbo.real_svr.class_id = dbo.unique_class.class_id INNER JOIN
dbo.temp_enrollment ON dbo.real_svr.uid = dbo.temp_enrollment.id INNER JOIN
dbo.batch ON dbo.unique_class.batch_id = dbo.batch.batch_id
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[33] 4[45] 2[4] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "real_svr"
Begin Extent =
Top = 11
Left = 250
Bottom = 300
Right = 410
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "unique_class"
Begin Extent =
Top = 6
Left = 441
Bottom = 193
Right = 672
End
DisplayFlags = 280
TopColumn = 2
End
Begin Table = "temp_enrollment"
Begin Extent =
Top = 26
Left = 6
Bottom = 163
Right = 183
End
DisplayFlags = 280
TopColumn = 4
End
Begin Table = "batch"
Begin Extent =
Top = 4
Left = 702
Bottom = 93
Right = 862
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 23
Width = 284
Width = 345
Width = 3240
Width = 1200
Width = 840
Width = 1995
Width = 1995
Width = 2325
Width = 975
Width = 1230
Width = 3210
Width = 1500
Width = 1020
Width = 1995
Width = 795
Width = 1170
Width = 3195
Width = 1515
Width = 3060
Width = 1080
Width = 3090
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWid' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'report_view'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'ths = 11
Column = 2355
Alias = 900
Table = 1425
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'report_view'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'report_view'
GO
/****** Object: View [dbo].[crV] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[crV]
AS
SELECT TOP (100) PERCENT temp_enrollment_1.id, temp_enrollment_1.enroll_id, temp_enrollment_1.enroll_name, temp_enrollment_1.department_id, temp_enrollment_1.department_name,
temp_enrollment_1.batch_id, temp_enrollment_1.batch_name, temp_enrollment_1.session_id, temp_enrollment_1.session_name, dbo.temp_unique_class_status.record_id,
dbo.temp_unique_class_status.class_id, dbo.temp_unique_class_status.class_of_date, dbo.temp_unique_class_status.weekday, dbo.temp_unique_class_status.class_start_time,
dbo.temp_unique_class_status.class_end_time, dbo.temp_unique_class_status.class_subject_or_department, dbo.temp_unique_class_status.class_topic, dbo.temp_unique_class_status.uid,
dbo.temp_unique_class_status.real_svr_Id, dbo.temp_unique_class_status.astrRemoteIP, dbo.temp_unique_class_status.anDeviceID, dbo.temp_unique_class_status.log_time,
dbo.temp_unique_class_status.class_status, dbo.unique_class.faculty_enrollment_uid, dbo.temp_enrollment.enroll_name AS faculty_name,
dbo.temp_enrollment.department_name AS faculty_department_name, dbo.temp_enrollment.department_id AS faculty_department_id
FROM dbo.unique_class INNER JOIN
dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
WHERE (dbo.temp_unique_class_status.astrRemoteIP = dbo.temp_unique_class_status.astrRemoteIP)
ORDER BY dbo.temp_enrollment.session_id, dbo.temp_unique_class_status.log_time
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "unique_class"
Begin Extent =
Top = 6
Left = 38
Bottom = 126
Right = 269
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_unique_class_status"
Begin Extent =
Top = 6
Left = 307
Bottom = 126
Right = 538
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment"
Begin Extent =
Top = 6
Left = 576
Bottom = 126
Right = 753
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment_1"
Begin Extent =
Top = 126
Left = 38
Bottom = 246
Right = 215
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'crV'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'crV'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'crV'
GO
/****** Object: View [dbo].[cr] Script Date: 04/29/2024 16:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[cr]
AS
SELECT TOP (100) PERCENT temp_enrollment_1.id, temp_enrollment_1.enroll_id, temp_enrollment_1.enroll_name, temp_enrollment_1.department_id, temp_enrollment_1.department_name,
temp_enrollment_1.batch_id, temp_enrollment_1.batch_name, temp_enrollment_1.session_id, temp_enrollment_1.session_name, dbo.temp_unique_class_status.record_id,
dbo.temp_unique_class_status.class_id, dbo.temp_unique_class_status.class_of_date, dbo.temp_unique_class_status.weekday, dbo.temp_unique_class_status.class_start_time,
dbo.temp_unique_class_status.class_end_time, dbo.temp_unique_class_status.class_subject_or_department, dbo.temp_unique_class_status.class_topic, dbo.temp_unique_class_status.uid,
dbo.temp_unique_class_status.real_svr_Id, dbo.temp_unique_class_status.astrRemoteIP, dbo.temp_unique_class_status.anDeviceID, dbo.temp_unique_class_status.log_time,
dbo.temp_unique_class_status.class_status, dbo.unique_class.faculty_enrollment_uid, dbo.temp_enrollment.enroll_name AS faculty_name,
dbo.temp_enrollment.department_name AS faculty_department_name, dbo.temp_enrollment.department_id AS faculty_department_id
FROM dbo.unique_class INNER JOIN
dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
ORDER BY dbo.temp_enrollment.session_id, dbo.temp_unique_class_status.log_time
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "unique_class"
Begin Extent =
Top = 6
Left = 38
Bottom = 126
Right = 269
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_unique_class_status"
Begin Extent =
Top = 6
Left = 307
Bottom = 126
Right = 538
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment"
Begin Extent =
Top = 6
Left = 576
Bottom = 126
Right = 753
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "temp_enrollment_1"
Begin Extent =
Top = 126
Left = 38
Bottom = 246
Right = 215
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'cr'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'cr'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'cr'
GO
3. Stored Procedure
USE [college2]
GO
/****** Object: StoredProcedure [dbo].[usp_GetErrorInfo] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetErrorInfo]
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_temp_unique_class_status] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_temp_unique_class_status]
@out int output
as
begin
--declare
--@class_id uniqueidentifier,
--@class_of_date date,
--@weekday nvarchar(50),
--@batch_id uniqueidentifier,
--@class_start_time datetime,
--@class_end_time datetime,
--@class_subject_or_department nvarchar(250),
--@class_topic nvarchar(250),
--@uid uniqueidentifier,
--@enroll_id numeric,
--@enroll_name nvarchar(50),
----@batch_id uniqueidentifier,
--@session_id uniqueidentifier,
--@record_id numeric,
----@class_id uniqueidentifier,
----@class_of_date date,
----@weekday nvarchar(50),
----@batch_id uniqueidentifier,
----@class_start_time datetime,
----@class_end_time datetime,
----@class_subject_or_department nvarchar(250),
----@class_topic nvarchar(250),
----@enroll_id numeric,
----@uid uniqueidentifier,
--@real_svr_Id numeric,
--@astrRemoteIP nvarchar(50),
--@anDeviceID nvarchar(50),
--@log_time datetime,
--@class_status nvarchar(50)
--set @out = 0
--DELETE FROM temp_unique_class_status
----DECLARE cc CURSOR FOR (SELECT class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM dbo.report_view GROUP BY class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic, faculty_enrollment_uid HAVING (class_id IS NOT NULL) AND (faculty_enrollment_uid IS NOT NULL))
--DECLARE cc CURSOR FOR (SELECT class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM dbo.report_view WHERE (faculty_enrollment_uid IS NOT NULL) GROUP BY class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic HAVING (class_id IS NOT NULL))
--OPEN cc
--FETCH NEXT FROM cc INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- --*************************************************************************************
-- DECLARE ccc CURSOR FOR (SELECT id, batch_student_enroll_id, enroll_name, session_id FROM batch_student WHERE (batch_id = @batch_id))
-- OPEN ccc
-- FETCH NEXT FROM ccc INTO @uid, @enroll_id, @enroll_name, @session_id
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
-- --********************************************************************************************
-- SELECT @real_svr_Id = NULL, @astrRemoteIP = NULL, @anDeviceID = NULL, @log_time = NULL
-- SELECT TOP(1) @real_svr_Id = Id, @astrRemoteIP = astrRemoteIP, @anDeviceID = anDeviceID, @log_time = adtLog FROM real_svr WHERE (aEnrollNumber = @enroll_id) AND (adtLog BETWEEN @class_start_time AND @class_end_time) ORDER BY adtLog
-- if(@log_time IS NOT NULL)
-- BEGIN
-- set @class_status = 'P'
-- END
-- ELSE
-- BEGIN
-- set @class_status = 'A'
-- END
-- BEGIN TRY
-- INSERT INTO temp_unique_class_status(class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic, enroll_id, [uid], session_id, real_svr_Id, astrRemoteIP, anDeviceID, log_time, class_status)
-- VALUES (@class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic, @enroll_id, @uid, @session_id, @real_svr_Id, @astrRemoteIP, @anDeviceID, @log_time, @class_status)
-- set @out = @out + 1
-- END TRY
-- BEGIN CATCH
-- --EXECUTE usp_GetErrorInfo
-- END CATCH
-- --********************************************************************************************
-- FETCH NEXT FROM ccc INTO @uid, @enroll_id, @enroll_name, @session_id
-- END
-- CLOSE ccc
-- DEALLOCATE ccc
-- --*************************************************************************************
-- --*************************************************************************************
-- SELECT @uid = null, @enroll_id = null, @enroll_name = null, @session_id = null
-- DECLARE ccc2 CURSOR FOR (SELECT dbo.faculty_enrollment.id, dbo.real_svr.aEnrollNumber, dbo.faculty_enrollment.enroll_name FROM dbo.real_svr INNER JOIN dbo.faculty_enrollment ON dbo.real_svr.uid = dbo.faculty_enrollment.id WHERE (dbo.real_svr.class_id = @class_id))
-- OPEN ccc2
-- FETCH NEXT FROM ccc2 INTO @uid, @enroll_id, @enroll_name
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
-- --********************************************************************************************
-- SELECT @real_svr_Id = NULL, @astrRemoteIP = NULL, @anDeviceID = NULL, @log_time = NULL
-- SELECT TOP(1) @real_svr_Id = Id, @astrRemoteIP = astrRemoteIP, @anDeviceID = anDeviceID, @log_time = adtLog FROM real_svr WHERE (aEnrollNumber = @enroll_id) AND (adtLog BETWEEN @class_start_time AND @class_end_time) ORDER BY adtLog
-- if(@log_time IS NOT NULL)
-- BEGIN
-- set @class_status = 'P'
-- END
-- ELSE
-- BEGIN
-- set @class_status = 'A'
-- END
-- BEGIN TRY
-- INSERT INTO temp_unique_class_status(class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic, enroll_id, [uid], session_id, real_svr_Id, astrRemoteIP, anDeviceID, log_time, class_status)
-- VALUES (@class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic, @enroll_id, @uid, @session_id, @real_svr_Id, @astrRemoteIP, @anDeviceID, @log_time, @class_status)
-- set @out = @out + 1
-- END TRY
-- BEGIN CATCH
-- --EXECUTE usp_GetErrorInfo
-- END CATCH
-- --********************************************************************************************
-- FETCH NEXT FROM ccc2 INTO @uid, @enroll_id, @enroll_name
-- END
-- CLOSE ccc2
-- DEALLOCATE ccc2
-- --*************************************************************************************
-- FETCH NEXT FROM cc INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
-- END
-- CLOSE cc
-- DEALLOCATE cc
set @out = 0
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_default_weekday_period] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_default_weekday_period]
@default_weekday_period_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from default_weekday_period WHERE (default_weekday_period_id = @default_weekday_period_id)) > 0
BEGIN
DELETE FROM default_weekday_period WHERE (default_weekday_period_id = @default_weekday_period_id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_machine] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_machine]
@vnMachineNumber numeric,
@out int output
as
begin
if(select count(*) from machine WHERE (vnMachineNumber = @vnMachineNumber)) > 0
BEGIN
DELETE FROM machine WHERE (vnMachineNumber = @vnMachineNumber)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_subject]
@subject_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from [subject] WHERE (subject_id = @subject_id)) > 0
BEGIN
DELETE FROM [subject] WHERE (subject_id = @subject_id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Display_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_batch]
as
begin
SELECT batch_id, batch_name AS Batch FROM batch ORDER BY batch_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_department] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_department]
as
begin
SELECT department_id, department_name AS Department FROM department ORDER BY department_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_default_weekday_period] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_default_weekday_period]
--@batch_id uniqueidentifier
@batch_id nvarchar(36)
as
begin
if(LEN(@batch_id) > 0)
BEGIN
--SELECT default_weekday_period_id, batch_id, default_weekday_period, default_weekday_period_start_time, default_weekday_period_end_time, default_weekday_period_subject FROM default_weekday_period WHERE (batch_id = @batch_id) ORDER BY default_weekday_period, default_weekday_period_start_time
SELECT
dbo.default_weekday_period.default_weekday_period_id,
dbo.default_weekday_period.batch_id, dbo.batch.batch_name AS Batch,
dbo.default_weekday_period.default_weekday_period AS 'Day',
dbo.default_weekday_period.default_weekday_period_start_time AS 'Start Time',
dbo.default_weekday_period.default_weekday_period_end_time AS 'End Time',
dbo.default_weekday_period.default_weekday_period_subject AS 'Subject'
FROM dbo.default_weekday_period INNER JOIN
dbo.batch ON dbo.default_weekday_period.batch_id = dbo.batch.batch_id
WHERE (dbo.default_weekday_period.batch_id = @batch_id)
ORDER BY dbo.default_weekday_period.default_weekday_period,
dbo.default_weekday_period.default_weekday_period_start_time
END
ELSE
BEGIN
--SELECT default_weekday_period_id, batch_id, default_weekday_period, default_weekday_period_start_time, default_weekday_period_end_time, default_weekday_period_subject FROM default_weekday_period ORDER BY default_weekday_period, default_weekday_period_start_time
SELECT
dbo.default_weekday_period.default_weekday_period_id,
dbo.default_weekday_period.batch_id, dbo.batch.batch_name AS Batch,
dbo.default_weekday_period.default_weekday_period AS 'Day',
dbo.default_weekday_period.default_weekday_period_start_time AS 'Start Time',
dbo.default_weekday_period.default_weekday_period_end_time AS 'End Time',
dbo.default_weekday_period.default_weekday_period_subject AS 'Subject'
--dbo.default_weekday_period.batch_id, dbo.batch.batch_name,
--dbo.default_weekday_period.default_weekday_period,
--dbo.default_weekday_period.default_weekday_period_start_time,
--dbo.default_weekday_period.default_weekday_period_end_time,
--dbo.default_weekday_period.default_weekday_period_subject
FROM dbo.default_weekday_period INNER JOIN
dbo.batch ON dbo.default_weekday_period.batch_id = dbo.batch.batch_id
ORDER BY dbo.default_weekday_period.default_weekday_period,
dbo.default_weekday_period.default_weekday_period_start_time
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_temp_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_temp_enrollment]
@department_id nvarchar(36),
@batch_id nvarchar(36),
@session_id nvarchar(36)
as
begin
if((LEN(@department_id) = 36) AND ((LEN(@batch_id) < 36) AND (LEN(@session_id) < 36)))
BEGIN
SELECT id, enroll_id, enroll_id AS 'Enroll Id', enroll_name AS 'Enroll Name', department_id, department_name AS 'Department', batch_id, batch_name, batch_name AS 'Batch', session_id, session_name AS 'Session' FROM temp_enrollment WHERE id IS NOT NULL AND department_id = @department_id ORDER BY enroll_id
END
else if((LEN(@department_id) < 36) AND ((LEN(@batch_id) = 36) AND (LEN(@session_id) = 36)))
BEGIN
SELECT id, enroll_id, enroll_id AS 'Enroll Id', enroll_name AS 'Enroll Name', department_id, department_name AS 'Department', batch_id, batch_name, batch_name AS 'Batch', session_id, session_name AS 'Session' FROM temp_enrollment WHERE id IS NOT NULL AND batch_id = @batch_id AND session_id = @session_id ORDER BY enroll_id
END
else if((LEN(@department_id) < 36) AND ((LEN(@batch_id) = 36) AND (LEN(@session_id) < 36)))
BEGIN
SELECT id, enroll_id, enroll_id AS 'Enroll Id', enroll_name AS 'Enroll Name', department_id, department_name AS 'Department', batch_id, batch_name, batch_name AS 'Batch', session_id, session_name AS 'Session' FROM temp_enrollment WHERE id IS NOT NULL AND batch_id = @batch_id ORDER BY enroll_id
END
else if((LEN(@department_id) < 36) AND ((LEN(@batch_id) < 36) AND (LEN(@session_id) = 36)))
BEGIN
SELECT id, enroll_id, enroll_id AS 'Enroll Id', enroll_name AS 'Enroll Name', department_id, department_name AS 'Department', batch_id, batch_name, batch_name AS 'Batch', session_id, session_name AS 'Session' FROM temp_enrollment WHERE id IS NOT NULL AND session_id = @session_id ORDER BY enroll_id
END
else
BEGIN
SELECT id, enroll_id, enroll_id AS 'Enroll Id', enroll_name AS 'Enroll Name', department_id, department_name AS 'Department', batch_id, batch_name, batch_name AS 'Batch', session_id, session_name AS 'Session' FROM temp_enrollment WHERE id IS NOT NULL ORDER BY enroll_id
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_subject]
@batch_id nvarchar(36)
as
begin
--SELECT subject_id, subject_name FROM subject
SELECT subject_id, subject_name AS 'Subject' FROM dbo.[subject] WHERE (batch_id = @batch_id)
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_session] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_session]
as
begin
SELECT session_id, session_name AS 'Session' FROM [session] ORDER BY session_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_machine] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_machine]
as
begin
SELECT vnMachineNumber, vpszIPAddress, vpszNetPort, vpszNetPassword, serverIP, serverPort, vnTimeOut, vnProtocolType, machineLocation,
vnMachineNumber AS 'Machine Number', vpszIPAddress AS 'IP Address', vpszNetPort AS 'Net Port', vpszNetPassword AS 'Net Password', serverIP AS 'Server IP', serverPort AS 'Server Port', vnTimeOut As 'Time Out', vnProtocolType AS 'Protocol Type', machineLocation AS 'Machine Location'
FROM machine ORDER BY vnMachineNumber
End
GO
/****** Object: StoredProcedure [dbo].[sp_Fill_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Fill_batch]
as
begin
SELECT batch_id, batch_name FROM batch ORDER BY batch_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Fill_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Fill_subject]
as
begin
SELECT subject_name FROM [subject] ORDER BY subject_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Fill_department_name_as_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Fill_department_name_as_subject]
as
begin
SELECT department_id, department_name AS Department FROM department ORDER BY department_name
End
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_batch]
@batch_name nvarchar(50),
@out int output
as
begin
declare
@batch_id uniqueidentifier
set @batch_id = NEWID()
if(select count(*) from batch where (batch_name = @batch_name)) = 0
begin
INSERT INTO batch(batch_id, batch_name) VALUES (@batch_id, @batch_name)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_department] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_department]
@department_name nvarchar(50),
@out int output
as
begin
declare
@department_id uniqueidentifier
set @department_id = NEWID()
if(select count(*) from department where (department_name = @department_name)) = 0
begin
INSERT INTO department(department_id, department_name) VALUES (@department_id, @department_name)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_default_weekday_period] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_default_weekday_period]
@batch_id uniqueidentifier,
@default_weekday_period nvarchar(50),
@default_weekday_period_start_time datetime,
@default_weekday_period_end_time datetime,
@default_weekday_period_subject nvarchar(50),
@out int output
as
begin
declare
@default_weekday_period_id uniqueidentifier
set @default_weekday_period_id = NEWID()
if(select count(*) from default_weekday_period where (batch_id = @batch_id) AND (default_weekday_period = @default_weekday_period) AND (default_weekday_period_start_time = @default_weekday_period_start_time) AND (default_weekday_period_end_time = @default_weekday_period_end_time)) = 0
begin
INSERT INTO default_weekday_period(default_weekday_period_id, batch_id, default_weekday_period, default_weekday_period_start_time, default_weekday_period_end_time, default_weekday_period_subject) VALUES (@default_weekday_period_id, @batch_id, @default_weekday_period, @default_weekday_period_start_time, @default_weekday_period_end_time, @default_weekday_period_subject)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_machine] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_machine]
@vnMachineNumber numeric,
@vpszIPAddress nvarchar(15),
@vpszNetPort numeric,
@vpszNetPassword numeric,
@serverIP nvarchar(15),
@serverPort nvarchar(50),
@vnTimeOut numeric,
@vnProtocolType numeric,
@machineLocation nvarchar(50),
@out int output
as
begin
if(select count(*) from machine where (vpszIPAddress = @vpszIPAddress)) = 0
begin
INSERT INTO machine(vnMachineNumber, vpszIPAddress, vpszNetPort, vpszNetPassword, serverIP, serverPort, vnTimeOut, vnProtocolType, machineLocation) VALUES (@vnMachineNumber, @vpszIPAddress, @vpszNetPort, @vpszNetPassword, @serverIP, @serverPort, @vnTimeOut, @vnProtocolType, @machineLocation)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_subject]
@subject_name nvarchar(50),
@batch_id uniqueidentifier,
@out int output
as
begin
declare
@subject_id uniqueidentifier
set @subject_id = NEWID()
if(select count(*) from [subject] where (batch_id = @batch_id) AND (subject_name = @subject_name)) = 0
begin
INSERT INTO [subject](subject_id, batch_id, subject_name) VALUES (@subject_id, @batch_id, @subject_name)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_session] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_session]
@session_name nvarchar(50),
@out int output
as
begin
declare
@session_id uniqueidentifier
set @session_id = NEWID()
if(select count(*) from session where (session_name = @session_name)) = 0
begin
INSERT INTO session(session_id, session_name) VALUES (@session_id, @session_name)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Read_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_subject]
@subject_id nvarchar(36)
as
begin
SELECT subject_id, subject_name FROM [subject] WHERE subject_id = @subject_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Read_session] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_session]
@session_id uniqueidentifier
as
begin
SELECT session_id, session_name FROM session WHERE session_id = @session_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Read_department] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_department]
@department_id uniqueidentifier
as
begin
SELECT department_id, department_name FROM department WHERE department_id = @department_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Read_default_weekday_period] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_default_weekday_period]
@default_weekday_period_id nvarchar(36)
as
begin
SELECT default_weekday_period_id, batch_id, default_weekday_period, default_weekday_period_start_time, default_weekday_period_end_time, default_weekday_period_subject FROM default_weekday_period WHERE default_weekday_period_id = @default_weekday_period_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Read_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_batch]
@batch_id uniqueidentifier
as
begin
SELECT batch_id, batch_name FROM batch WHERE batch_id = @batch_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Update_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_batch]
@batch_id uniqueidentifier,
@batch_name nvarchar(50),
@out int output
as
begin
if(select count(*) from batch WHERE (batch_id = @batch_id)) > 0
BEGIN
if(select count(*) from batch WHERE (batch_id != @batch_id) AND (batch_name = @batch_name)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE batch SET batch_name = @batch_name WHERE (batch_id = @batch_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_department] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_department]
@department_id uniqueidentifier,
@department_name nvarchar(50),
@out int output
as
begin
if(select count(*) from department WHERE (department_id = @department_id)) > 0
BEGIN
if(select count(*) from department WHERE (department_id != @department_id) AND (department_name = @department_name)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE department SET department_name = @department_name WHERE (department_id = @department_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_default_weekday_period] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_default_weekday_period]
@default_weekday_period_id uniqueidentifier,
@batch_id uniqueidentifier,
@default_weekday_period nvarchar(50),
@default_weekday_period_start_time datetime,
@default_weekday_period_end_time datetime,
@default_weekday_period_subject nvarchar(50),
@out int output
as
begin
if(select count(*) from default_weekday_period WHERE (default_weekday_period_id = @default_weekday_period_id)) > 0
BEGIN
if(select count(*) from default_weekday_period WHERE (default_weekday_period_id != @default_weekday_period_id) AND (batch_id = @batch_id) AND (default_weekday_period = @default_weekday_period) AND (default_weekday_period_start_time = @default_weekday_period_start_time) AND (default_weekday_period_end_time = @default_weekday_period_end_time)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE default_weekday_period SET default_weekday_period_id = @default_weekday_period_id,batch_id = @batch_id,default_weekday_period = @default_weekday_period,default_weekday_period_start_time = @default_weekday_period_start_time,default_weekday_period_end_time = @default_weekday_period_end_time,default_weekday_period_subject = @default_weekday_period_subject WHERE (default_weekday_period_id = @default_weekday_period_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_subject] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_subject]
@subject_id uniqueidentifier,
@batch_id uniqueidentifier,
@subject_name nvarchar(50),
@out int output
as
begin
if(select count(*) from subject WHERE (subject_id = @subject_id)) > 0
BEGIN
if(select count(*) from subject WHERE (subject_id != @subject_id) AND ((batch_id = @batch_id) AND (subject_name = @subject_name))) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE subject SET subject_id = @subject_id, batch_id = @batch_id, subject_name = @subject_name WHERE (subject_id = @subject_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_session] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_session]
@session_id uniqueidentifier,
@session_name nvarchar(50),
@out int output
as
begin
if(select count(*) from session WHERE (session_id = @session_id)) > 0
BEGIN
if(select count(*) from session WHERE (session_id != @session_id) AND (session_name = @session_name)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE session SET session_name = @session_name WHERE (session_id = @session_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_real_svr] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_real_svr]
@student_out int output,
@faculty_out int output
as
begin
UPDATE real_svr
SET [uid] = null, department_id = null, batch_id = null, class_id = null
FROM real_svr
UPDATE real_svr
SET real_svr.[uid] = faculty_enrollment.id, real_svr.department_id = faculty_enrollment.department_id
FROM real_svr INNER JOIN faculty_enrollment ON real_svr.aEnrollNumber = faculty_enrollment.faculty_enroll_id
UPDATE real_svr
SET [uid] = batch_student.id, batch_id = batch_student.batch_id
FROM real_svr INNER JOIN batch_student ON real_svr.aEnrollNumber = batch_student.batch_student_enroll_id
--UPDATE real_svr
--SET class_id =(SELECT class_id FROM unique_class WHERE (real_svr.adtLog BETWEEN unique_class.class_start_time AND unique_class.class_end_time) AND (unique_class.batch_id = real_svr.batch_id))
--WHERE real_svr.batch_id IS NOT NULL
--set @student_out = 1
--set @faculty_out = 1
--DECLARE
--@anDeviceID nvarchar(50),
--@astrRemoteIP nvarchar(50),
--@batch_id uniqueidentifier,
--@class_id uniqueidentifier,
--@department_id uniqueidentifier,
--@uid uniqueidentifier,
--@class_start_time datetime,
--@class_end_time datetime
------********************************************************************************************
--DECLARE ccc CURSOR FOR (SELECT anDeviceID, astrRemoteIP, batch_id, class_id FROM real_svr GROUP BY anDeviceID, astrRemoteIP, batch_id, class_id HAVING (batch_id IS NOT NULL) AND (class_id IS NOT NULL))
--OPEN ccc
--FETCH NEXT FROM ccc INTO @anDeviceID, @astrRemoteIP, @batch_id, @class_id
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- SELECT @class_start_time = class_start_time, @class_end_time = class_end_time
-- FROM unique_class
-- WHERE (class_id = @class_id)
-- SELECT TOP (1) @uid = [uid]
-- FROM dbo.real_svr
-- WHERE adtLog BETWEEN @class_start_time and @class_end_time
-- AND class_id IS NULL AND batch_id IS NULL AND [uid] IS NOT NULL
-- UPDATE dbo.unique_class
-- SET faculty_enrollment_uid = @uid
-- WHERE class_id = @class_id
-- UPDATE real_svr
-- SET batch_id = @batch_id, class_id = @class_id
-- WHERE (adtLog BETWEEN @class_start_time AND @class_end_time) AND (class_id IS NULL) AND (batch_id IS NULL) AND ([uid] IS NOT NULL)
-- FETCH NEXT FROM ccc INTO @anDeviceID, @astrRemoteIP, @batch_id, @class_id
--END
--CLOSE ccc
--DEALLOCATE ccc
------********************************************************************************************
DECLARE
@class_id uniqueidentifier,
@class_of_date date,
@weekday nvarchar(50),
@batch_id uniqueidentifier,
@class_start_time datetime,
@class_end_time datetime,
@class_subject_or_department nvarchar(250),
@class_topic nvarchar(250),
@faculty_enrollment_uid uniqueidentifier,
@id uniqueidentifier,
@enroll_id numeric(18, 0),
@enroll_name nvarchar(50),
@department_id uniqueidentifier,
@department_name nvarchar(50),
@batch_name nvarchar(50),
@session_id uniqueidentifier,
@session_name nvarchar(50),
@real_svr_Id numeric(18, 0),
@astrRemoteIP nvarchar(50),
@anDeviceID nvarchar(50),
@log_time datetime,
@class_status nvarchar(50)
DELETE FROM dbo.temp_report_data
set @student_out = 0
set @faculty_out = 0
----********************************************************************************************
--Step 1. collect all the class data
DECLARE c CURSOR FOR (SELECT class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM dbo.unique_class)
OPEN c
FETCH NEXT FROM c INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
WHILE @@FETCH_STATUS = 0
BEGIN
--Step 2. collect Teacher of the class
SELECT TOP(1) @department_id = department_id, @department_name = department_name FROM department WHERE (department_name = @class_subject_or_department)
SELECT TOP(1) @faculty_enrollment_uid = [uid], @astrRemoteIP = astrRemoteIP, @anDeviceID = anDeviceID FROM real_svr WHERE (aEnrollNumber IN (SELECT faculty_enroll_id FROM faculty_enrollment WHERE (department_id = @department_id) AND (faculty_enroll_id IS NOT NULL))) AND (adtLog BETWEEN @class_start_time AND @class_end_time)
UPDATE dbo.unique_class SET faculty_enrollment_uid = @faculty_enrollment_uid WHERE class_id = @class_id
if(len(@faculty_enrollment_uid) = 36)
BEGIN
--SELECT id, enroll_id, enroll_name, department_id, department_name, batch_id, batch_name, session_id, session_name FROM dbo.temp_enrollment
--WHERE (batch_id = @batch_id) OR (id = @faculty_enrollment_uid)
--***************************************************************************
--Step 3.1. collect all the students of class
--DECLARE cc CURSOR FOR (SELECT id, enroll_id, enroll_name, department_id, department_name, batch_id, batch_name, session_id, session_name FROM dbo.temp_enrollment WHERE (batch_id = @batch_id) OR (id = @faculty_enrollment_uid))
--OPEN cc
--FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @department_id, @department_name, @batch_id, @batch_name, @session_id, @session_name
DECLARE cc CURSOR FOR (SELECT id, enroll_id, enroll_name, batch_name, session_id, session_name FROM dbo.temp_enrollment WHERE (batch_id = @batch_id) OR (id = @faculty_enrollment_uid))
OPEN cc
FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @batch_name, @session_id, @session_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @real_svr_Id = 0
set @log_time = NULL
SELECT TOP(1) @real_svr_Id = Id, @log_time = adtLog
FROM real_svr
WHERE ([uid] = @id) AND (astrRemoteIP = @astrRemoteIP) AND (anDeviceID = @anDeviceID) AND (adtLog BETWEEN @class_start_time AND @class_end_time)
--SELECT @log_time = ISNULL(@log_time, @class_of_date)
if((@real_svr_Id > 0) AND (@log_time IS NOT NULL))
BEGIN
set @class_status = 'P'
END
ELSE
BEGIN
set @class_status = 'A'
END
INSERT INTO dbo.temp_report_data(class_id,class_of_date,[weekday],batch_id,class_start_time,class_end_time,class_subject_or_department,class_topic,faculty_enrollment_uid,id,enroll_id,enroll_name,department_id,department_name,batch_name,session_id,session_name,real_svr_Id,astrRemoteIP,anDeviceID,log_time,class_status)
VALUES(@class_id,@class_of_date,@weekday,@batch_id,@class_start_time,@class_end_time,@class_subject_or_department,@class_topic,@faculty_enrollment_uid,@id,@enroll_id,@enroll_name,@department_id,@department_name,@batch_name,@session_id,@session_name,@real_svr_Id,@astrRemoteIP,@anDeviceID,@log_time,@class_status)
if(@faculty_enrollment_uid = @id)
BEGIN
set @faculty_out = @faculty_out + 1
END
ELSE
BEGIN
set @student_out = @student_out + 1
END
--FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @department_id, @department_name, @batch_id, @batch_name, @session_id, @session_name
FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @batch_name, @session_id, @session_name
END
CLOSE cc
DEALLOCATE cc
--***************************************************************************
END
FETCH NEXT FROM c INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
END
CLOSE c
DEALLOCATE c
----********************************************************************************************
return @student_out
return @faculty_out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_faculty_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_faculty_enrollment]
@id uniqueidentifier,
@faculty_enroll_id numeric,
@enroll_name nvarchar(50),
@department_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from faculty_enrollment WHERE (id = @id)) > 0
BEGIN
if(select count(*) from faculty_enrollment WHERE (id != @id) AND (faculty_enroll_id = @faculty_enroll_id)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else if((select count(*) from batch_student where (batch_student_enroll_id = @faculty_enroll_id)) > 0)
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE faculty_enrollment SET faculty_enroll_id = @faculty_enroll_id,enroll_name = @enroll_name,department_id = @department_id WHERE (id = @id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_unique_class] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_unique_class]
@class_id uniqueidentifier,
@class_of_date date,
@weekday nvarchar(50),
@batch_id uniqueidentifier,
@class_start_time datetime,
@class_end_time datetime,
@class_subject_or_department nvarchar(250),
@class_topic nvarchar(250),
@out int output
as
begin
if(select count(*) from unique_class WHERE (class_id = @class_id)) > 0
BEGIN
if(select count(*) from unique_class WHERE (class_id != @class_id) AND (class_of_date = @class_of_date) AND (batch_id = @batch_id) AND (class_start_time = @class_start_time)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE unique_class SET class_id = @class_id,class_of_date = @class_of_date,weekday = @weekday,batch_id = @batch_id,class_start_time = @class_start_time,class_end_time = @class_end_time,class_subject_or_department = @class_subject_or_department,class_topic = @class_topic WHERE (class_id = @class_id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_temp_report_data] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_temp_report_data]
@student_out int output,
@faculty_out int output
as
begin
DECLARE
@class_id uniqueidentifier,
@class_of_date date,
@weekday nvarchar(50),
@batch_id uniqueidentifier,
@class_start_time datetime,
@class_end_time datetime,
@class_subject_or_department nvarchar(250),
@class_topic nvarchar(250),
@faculty_enrollment_uid uniqueidentifier,
@id uniqueidentifier,
@enroll_id numeric(18, 0),
@enroll_name nvarchar(50),
@department_id uniqueidentifier,
@department_name nvarchar(50),
@batch_name nvarchar(50),
@session_id uniqueidentifier,
@session_name nvarchar(50),
@real_svr_Id numeric(18, 0),
@astrRemoteIP nvarchar(50),
@anDeviceID nvarchar(50),
@log_time datetime,
@class_status nvarchar(50)
DELETE FROM dbo.temp_report_data
set @student_out = 0
----********************************************************************************************
--Step 1. collect all the class data
DECLARE c CURSOR FOR (SELECT class_id, class_of_date, [weekday], batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM dbo.unique_class)
OPEN c
FETCH NEXT FROM c INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
WHILE @@FETCH_STATUS = 0
BEGIN
--Step 2. collect Teacher of the class
SELECT TOP(1) @department_id = department_id, @department_name = department_name FROM department WHERE (department_name = @class_subject_or_department)
SELECT TOP(1) @faculty_enrollment_uid = [uid], @astrRemoteIP = astrRemoteIP, @anDeviceID = anDeviceID FROM real_svr WHERE (aEnrollNumber IN (SELECT faculty_enroll_id FROM faculty_enrollment WHERE (department_id = @department_id) AND (faculty_enroll_id IS NOT NULL))) AND (adtLog BETWEEN @class_start_time AND @class_end_time)
UPDATE dbo.unique_class SET faculty_enrollment_uid = @faculty_enrollment_uid WHERE class_id = @class_id
if(len(@faculty_enrollment_uid) = 36)
BEGIN
--SELECT id, enroll_id, enroll_name, department_id, department_name, batch_id, batch_name, session_id, session_name FROM dbo.temp_enrollment
--WHERE (batch_id = @batch_id) OR (id = @faculty_enrollment_uid)
--***************************************************************************
--Step 3.1. collect all the students of class
DECLARE cc CURSOR FOR (SELECT id, enroll_id, enroll_name, department_id, department_name, batch_id, batch_name, session_id, session_name FROM dbo.temp_enrollment WHERE (batch_id = @batch_id) OR (id = @faculty_enrollment_uid))
OPEN cc
FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @department_id, @department_name, @batch_id, @batch_name, @session_id, @session_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @real_svr_Id = 0
set @log_time = null
SELECT @real_svr_Id = Id, @log_time = adtLog
FROM real_svr
WHERE ([uid] = @id) AND (astrRemoteIP = @astrRemoteIP) AND (anDeviceID = @anDeviceID) AND (adtLog BETWEEN @class_start_time AND @class_end_time)
if((@real_svr_Id > 0) AND (@log_time IS NOT NULL))
BEGIN
set @class_status = 'P'
END
ELSE
BEGIN
set @class_status = 'A'
END
INSERT INTO dbo.temp_report_data(class_id,class_of_date,[weekday],batch_id,class_start_time,class_end_time,class_subject_or_department,class_topic,faculty_enrollment_uid,id,enroll_id,enroll_name,department_id,department_name,batch_name,session_id,session_name,real_svr_Id,astrRemoteIP,anDeviceID,log_time,class_status)
VALUES(@class_id,@class_of_date,@weekday,@batch_id,@class_start_time,@class_end_time,@class_subject_or_department,@class_topic,@faculty_enrollment_uid,@id,@enroll_id,@enroll_name,@department_id,@department_name,@batch_name,@session_id,@session_name,@real_svr_Id,@astrRemoteIP,@anDeviceID,@log_time,@class_status)
if(@faculty_enrollment_uid = @id)
BEGIN
set @faculty_out = @faculty_out + 1
END
ELSE
BEGIN
set @student_out = @student_out + 1
END
FETCH NEXT FROM cc INTO @id, @enroll_id, @enroll_name, @department_id, @department_name, @batch_id, @batch_name, @session_id, @session_name
END
CLOSE cc
DEALLOCATE cc
--***************************************************************************
END
FETCH NEXT FROM c INTO @class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic
END
CLOSE c
DEALLOCATE c
----********************************************************************************************
return @student_out
return @faculty_out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Update_batch_student] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Update_batch_student]
@id uniqueidentifier,
@batch_student_enroll_id numeric,
@enroll_name nvarchar(50),
@batch_id uniqueidentifier,
@session_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from batch_student WHERE (id = @id)) > 0
BEGIN
if(select count(*) from batch_student WHERE (id != @id) AND (batch_student_enroll_id = @batch_student_enroll_id) AND (batch_id = @batch_id) AND (session_id = @session_id)) > 0
BEGIN
set @out = 0 -- Record already exists at other record
END
else if((select count(*) from faculty_enrollment where (faculty_enroll_id = @batch_student_enroll_id)) > 0)
BEGIN
set @out = 0 -- Record already exists at other record
END
else
BEGIN
UPDATE batch_student SET id = @id,batch_student_enroll_id = @batch_student_enroll_id,enroll_name = @enroll_name,batch_id = @batch_id,session_id = @session_id WHERE (id = @id)
set @out = 1 -- Record updated successfully.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Read_unique_class] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Read_unique_class]
@class_id nvarchar(36)
as
begin
SELECT class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM unique_class WHERE class_id = @class_id
End
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_unique_class] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_unique_class]
@class_of_date date,
@weekday nvarchar(50),
@batch_id uniqueidentifier,
@class_start_time datetime,
@class_end_time datetime,
@class_subject_or_department nvarchar(250),
@class_topic nvarchar(250),
@out int output
as
begin
declare
@class_id uniqueidentifier
set @class_id = NEWID()
if(select count(*) from unique_class where (class_of_date = @class_of_date) AND (batch_id = @batch_id) AND (class_start_time = @class_start_time)) = 0
begin
INSERT INTO unique_class(class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic) VALUES (@class_id, @class_of_date, @weekday, @batch_id, @class_start_time, @class_end_time, @class_subject_or_department, @class_topic)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_temp_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_temp_enrollment]
@out int output
as
begin
UPDATE real_svr SET uid = faculty_enrollment.id FROM real_svr INNER JOIN faculty_enrollment ON real_svr.aEnrollNumber = faculty_enrollment.faculty_enroll_id
UPDATE real_svr SET uid = batch_student.id FROM real_svr INNER JOIN batch_student ON real_svr.aEnrollNumber = batch_student.batch_student_enroll_id WHERE (real_svr.uid IS NULL)
DELETE FROM temp_enrollment
INSERT INTO temp_enrollment
(id, enroll_id, enroll_name, batch_id, batch_name, session_id, session_name)
(SELECT
dbo.batch_student.id,
dbo.batch_student.batch_student_enroll_id,
dbo.batch_student.enroll_name,
dbo.batch_student.batch_id,
dbo.batch.batch_name,
dbo.batch_student.session_id,
dbo.[session].session_name
FROM dbo.batch_student INNER JOIN
dbo.batch ON dbo.batch_student.batch_id = dbo.batch.batch_id INNER JOIN
dbo.session ON dbo.batch_student.session_id = dbo.session.session_id
WHERE (dbo.batch_student.id IS NOT NULL))
INSERT INTO temp_enrollment
(id, enroll_id, enroll_name, department_id, department_name)
(SELECT
dbo.faculty_enrollment.id,
dbo.faculty_enrollment.faculty_enroll_id,
dbo.faculty_enrollment.enroll_name,
dbo.faculty_enrollment.department_id,
dbo.department.department_name
FROM dbo.faculty_enrollment INNER JOIN
dbo.department ON dbo.faculty_enrollment.department_id = dbo.department.department_id
WHERE (dbo.faculty_enrollment.id IS NOT NULL))
set @out = 1 -- Record inserted successfully.
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_real_svr] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_real_svr]
@aEnrollNumber numeric,
@aVerifyMode nvarchar(50),
@aInOutMode numeric,
@adtLog datetime,
@abDrawFlag bit,
@astrRemoteIP nvarchar(50),
@anRemotePort numeric,
@anDeviceID nvarchar(50),
@astrSerialNo nvarchar(50),
@astrRouterIP nvarchar(50),
@out int output
as
begin
if(select count(*) from real_svr where (aEnrollNumber = @aEnrollNumber) AND (adtLog = @adtLog)) = 0
begin
declare
@uid uniqueidentifier
set @uid = null
set @uid = (SELECT id FROM faculty_enrollment WHERE (faculty_enroll_id = @aEnrollNumber))
if (@uid = null)
BEGIN
set @uid = (SELECT id FROM batch_student WHERE (batch_student_enroll_id = @aEnrollNumber))
END
INSERT INTO real_svr([uid], aEnrollNumber, aVerifyMode, aInOutMode, adtLog, abDrawFlag, astrRemoteIP, anRemotePort, anDeviceID, astrSerialNo, astrRouterIP) VALUES (@uid, @aEnrollNumber, @aVerifyMode, @aInOutMode, @adtLog, @abDrawFlag, @astrRemoteIP, @anRemotePort, @anDeviceID, @astrSerialNo, @astrRouterIP)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_faculty_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_faculty_enrollment]
@faculty_enroll_id numeric,
@enroll_name nvarchar(50),
@department_id uniqueidentifier,
@out int output
as
begin
declare
@id uniqueidentifier
set @id = NEWID()
if((select count(*) from faculty_enrollment where (faculty_enroll_id = @faculty_enroll_id)) = 0
AND ((select count(*) from batch_student where (batch_student_enroll_id = @faculty_enroll_id)) = 0))
begin
INSERT INTO faculty_enrollment(id, faculty_enroll_id, enroll_name, department_id) VALUES (@id, @faculty_enroll_id, @enroll_name, @department_id)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Insert_batch_student] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Insert_batch_student]
@batch_student_enroll_id numeric,
@enroll_name nvarchar(50),
@batch_id uniqueidentifier,
@session_id uniqueidentifier,
@out int output
as
begin
declare
@id uniqueidentifier
set @id = NEWID()
--if(select count(*) from batch_student where (batch_student_enroll_id = @batch_student_enroll_id) AND (batch_id = @batch_id) AND (session_id = @session_id)) = 0
if(((select count(*) from batch_student where (batch_student_enroll_id = @batch_student_enroll_id) AND (batch_id = @batch_id)) = 0)
AND ((select count(*) from batch_student where (batch_student_enroll_id = @batch_student_enroll_id)) = 0)
AND ((select count(*) from faculty_enrollment where (faculty_enroll_id = @batch_student_enroll_id)) = 0))
begin
INSERT INTO batch_student(id, batch_student_enroll_id, enroll_name, batch_id, session_id) VALUES (@id, @batch_student_enroll_id, @enroll_name, @batch_id, @session_id)
set @out = 1 -- Record inserted successfully.
end
else
begin
set @out = 2 -- Record already exists.
end
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Fill_subjects] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Fill_subjects]
as
begin
--SELECT DISTINCT(default_weekday_period_subject) AS default_weekday_period_subject FROM default_weekday_period ORDER BY default_weekday_period_subject
SELECT DISTINCT(class_subject_or_department) AS class_subject_or_department FROM dbo.unique_class ORDER BY class_subject_or_department
End
GO
/****** Object: StoredProcedure [dbo].[sp_Fill_class_topic] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Fill_class_topic]
as
begin
SELECT DISTINCT(class_topic) AS class_topic FROM unique_class ORDER BY class_topic
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_unique_class_faculty_assign] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_unique_class_faculty_assign]
@class_of_date date,
@batch_id nvarchar(36)
as
begin
--SELECT class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM unique_class WHERE (class_of_date = @class_of_date) AND (batch_id = @batch_id)
if(LEN(@batch_id) = 36)
BEGIN
SELECT
dbo.unique_class.class_id,
dbo.unique_class.class_of_date AS 'Date',
dbo.unique_class.[weekday] AS 'Weekday',
dbo.unique_class.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.unique_class.class_start_time AS 'Start Time',
dbo.unique_class.class_end_time AS 'End Time',
dbo.unique_class.class_subject_or_department AS 'Subject',
dbo.unique_class.class_topic AS 'Topic'
--, dbo.unique_class.Id
FROM dbo.unique_class INNER JOIN
dbo.batch ON dbo.unique_class.batch_id = dbo.batch.batch_id
WHERE (dbo.unique_class.class_of_date = @class_of_date) AND (dbo.unique_class.batch_id = @batch_id)
--AND (faculty_enrollment_uid IS NOT NULL)
ORDER BY dbo.unique_class.class_start_time
END
ELSE
BEGIN
SELECT
dbo.unique_class.class_id,
dbo.unique_class.class_of_date AS 'Date',
dbo.unique_class.[weekday] AS 'Weekday',
dbo.unique_class.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.unique_class.class_start_time AS 'Start Time',
dbo.unique_class.class_end_time AS 'End Time',
dbo.unique_class.class_subject_or_department AS 'Subject',
dbo.unique_class.class_topic AS 'Topic'
--, dbo.unique_class.Id
FROM dbo.unique_class INNER JOIN
dbo.batch ON dbo.unique_class.batch_id = dbo.batch.batch_id
WHERE (dbo.unique_class.class_of_date = @class_of_date)
-- AND (faculty_enrollment_uid IS NOT NULL)
ORDER BY dbo.unique_class.class_start_time
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_unique_class] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_unique_class]
@class_of_date date,
@batch_id nvarchar(36)
as
begin
--SELECT class_id, class_of_date, weekday, batch_id, class_start_time, class_end_time, class_subject_or_department, class_topic FROM unique_class WHERE (class_of_date = @class_of_date) AND (batch_id = @batch_id)
if(LEN(@batch_id) = 36)
BEGIN
SELECT
dbo.unique_class.class_id,
dbo.unique_class.class_of_date AS 'Date',
dbo.unique_class.[weekday] AS 'Weekday',
dbo.unique_class.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.unique_class.class_start_time AS 'Start Time',
dbo.unique_class.class_end_time AS 'End Time',
dbo.unique_class.class_subject_or_department AS 'Subject',
dbo.unique_class.class_topic AS 'Topic'
--, dbo.unique_class.Id
FROM dbo.unique_class INNER JOIN
dbo.batch ON dbo.unique_class.batch_id = dbo.batch.batch_id
WHERE (dbo.unique_class.class_of_date = @class_of_date) AND (dbo.unique_class.batch_id = @batch_id)
ORDER BY dbo.unique_class.class_start_time
END
ELSE
BEGIN
SELECT
dbo.unique_class.class_id,
dbo.unique_class.class_of_date AS 'Date',
dbo.unique_class.[weekday] AS 'Weekday',
dbo.unique_class.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.unique_class.class_start_time AS 'Start Time',
dbo.unique_class.class_end_time AS 'End Time',
dbo.unique_class.class_subject_or_department AS 'Subject',
dbo.unique_class.class_topic AS 'Topic'
--, dbo.unique_class.Id
FROM dbo.unique_class INNER JOIN
dbo.batch ON dbo.unique_class.batch_id = dbo.batch.batch_id
WHERE (dbo.unique_class.class_of_date = @class_of_date)
ORDER BY dbo.unique_class.class_start_time
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_faculty_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_faculty_enrollment]
--@department_id uniqueidentifier
@department_id nvarchar(36)
as
begin
--SELECT faculty_enroll_id, enroll_name, department_id FROM faculty_enrollment WHERE (department_id = @department_id)
if(len(@department_id) > 0)
BEGIN
SELECT
dbo.faculty_enrollment.id,
dbo.faculty_enrollment.department_id,
dbo.department.department_name AS Department,
dbo.faculty_enrollment.faculty_enroll_id AS 'Enroll Id',
dbo.faculty_enrollment.enroll_name AS 'Enroll Name'
FROM dbo.faculty_enrollment INNER JOIN dbo.department ON dbo.faculty_enrollment.department_id = dbo.department.department_id
WHERE (dbo.faculty_enrollment.department_id = @department_id)
ORDER BY dbo.faculty_enrollment.faculty_enroll_id
END
ELSE
BEGIN
SELECT
dbo.faculty_enrollment.id,
dbo.faculty_enrollment.department_id,
dbo.department.department_name AS Department,
dbo.faculty_enrollment.faculty_enroll_id AS 'Enroll Id',
dbo.faculty_enrollment.enroll_name AS 'Enroll Name'
FROM dbo.faculty_enrollment INNER JOIN dbo.department ON dbo.faculty_enrollment.department_id = dbo.department.department_id
ORDER BY dbo.faculty_enrollment.faculty_enroll_id
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_personal_report] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_personal_report]
@enroll_id numeric,
@batch_id nvarchar(36)
as
begin
if(LEN(@batch_id) = 36)
BEGIN
--SELECT
--dbo.temp_enrollment.id,
--dbo.temp_enrollment.enroll_id,
--dbo.temp_enrollment.enroll_name,
--dbo.temp_enrollment.department_id,
--dbo.temp_enrollment.department_name,
--dbo.temp_enrollment.batch_id,
--dbo.temp_enrollment.batch_name,
--dbo.temp_enrollment.session_id,
--dbo.temp_enrollment.session_name,
--dbo.temp_unique_class_status.record_id,
--dbo.temp_unique_class_status.class_id,
--dbo.temp_unique_class_status.class_of_date,
--dbo.temp_unique_class_status.[weekday],
--dbo.temp_unique_class_status.class_start_time,
--dbo.temp_unique_class_status.class_end_time,
--dbo.temp_unique_class_status.class_subject_or_department,
--dbo.temp_unique_class_status.class_topic,
--dbo.temp_unique_class_status.[uid],
--dbo.temp_unique_class_status.real_svr_Id,
--dbo.temp_unique_class_status.astrRemoteIP,
--dbo.temp_unique_class_status.anDeviceID,
--dbo.temp_unique_class_status.log_time,
--dbo.temp_unique_class_status.class_status
--FROM dbo.temp_enrollment RIGHT OUTER JOIN dbo.temp_unique_class_status
--ON dbo.temp_enrollment.enroll_id = dbo.temp_unique_class_status.enroll_id
--WHERE (dbo.temp_enrollment.enroll_id = @enroll_id)
--AND (dbo.temp_enrollment.batch_id = @batch_id)
--AND (dbo.temp_unique_class_status.batch_id = @batch_id)
--ORDER BY log_time
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.enroll_id = @enroll_id) AND (temp_report_data.batch_id = @batch_id)
END
ELSE
BEGIN
--SELECT
--dbo.temp_enrollment.id,
--dbo.temp_enrollment.enroll_id,
--dbo.temp_enrollment.enroll_name,
--dbo.temp_enrollment.department_id,
--dbo.temp_enrollment.department_name,
--dbo.temp_enrollment.batch_id,
--dbo.temp_enrollment.batch_name,
--dbo.temp_enrollment.session_id,
--dbo.temp_enrollment.session_name,
--dbo.temp_unique_class_status.record_id,
--dbo.temp_unique_class_status.class_id,
--dbo.temp_unique_class_status.class_of_date,
--dbo.temp_unique_class_status.[weekday],
--dbo.temp_unique_class_status.class_start_time,
--dbo.temp_unique_class_status.class_end_time,
--dbo.temp_unique_class_status.class_subject_or_department,
--dbo.temp_unique_class_status.class_topic,
--dbo.temp_unique_class_status.[uid],
--dbo.temp_unique_class_status.real_svr_Id,
--dbo.temp_unique_class_status.astrRemoteIP,
--dbo.temp_unique_class_status.anDeviceID,
--dbo.temp_unique_class_status.log_time,
--dbo.temp_unique_class_status.class_status
--FROM dbo.temp_enrollment RIGHT OUTER JOIN
--dbo.temp_unique_class_status ON dbo.temp_enrollment.enroll_id = dbo.temp_unique_class_status.enroll_id
--WHERE (dbo.temp_enrollment.enroll_id = @enroll_id) AND (dbo.temp_unique_class_status.class_id IS NOT NULL)
--ORDER BY dbo.temp_unique_class_status.log_time
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.enroll_id = @enroll_id) AND (temp_report_data.class_id IS NOT NULL)
--FROM dbo.temp_enrollment RIGHT OUTER JOIN dbo.temp_unique_class_status
--ON dbo.temp_enrollment.enroll_id = dbo.temp_unique_class_status.enroll_id
--WHERE (dbo.temp_enrollment.enroll_id = @enroll_id)
--AND (dbo.temp_enrollment.batch_id IS NOT NULL)
--AND (dbo.temp_unique_class_status.batch_id IS NOT NULL)
--ORDER BY log_time
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_Periodic_Class_report] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_Periodic_Class_report]
@class_from_date date,
@class_upto_date date
as
begin
if(DATEDIFF(DAY,@class_from_date,@class_upto_date) > 0)
BEGIN
--SELECT
--temp_enrollment_1.id,
--temp_enrollment_1.enroll_id,
--temp_enrollment_1.enroll_name,
--temp_enrollment_1.department_id,
--temp_enrollment_1.department_name,
--temp_enrollment_1.batch_id,
--temp_enrollment_1.batch_name,
--temp_enrollment_1.session_id,
--temp_enrollment_1.session_name,
--dbo.temp_unique_class_status.record_id,
--dbo.temp_unique_class_status.class_id,
--dbo.temp_unique_class_status.class_of_date,
--dbo.temp_unique_class_status.weekday,
--dbo.temp_unique_class_status.class_start_time,
--dbo.temp_unique_class_status.class_end_time,
--dbo.temp_unique_class_status.class_subject_or_department,
--dbo.temp_unique_class_status.class_topic,
--dbo.temp_unique_class_status.uid,
--dbo.temp_unique_class_status.real_svr_Id,
--dbo.temp_unique_class_status.astrRemoteIP,
--dbo.temp_unique_class_status.anDeviceID,
--dbo.temp_unique_class_status.log_time,
--dbo.temp_unique_class_status.class_status,
--dbo.unique_class.faculty_enrollment_uid,
--dbo.temp_enrollment.enroll_name AS faculty_name,
--dbo.temp_enrollment.department_name AS faculty_department_name,
--dbo.temp_enrollment.department_id AS faculty_department_id
--FROM dbo.unique_class INNER JOIN
--dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
--dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
--dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
--WHERE (dbo.temp_unique_class_status.class_of_date BETWEEN @class_from_date AND @class_upto_date)
--AND (dbo.temp_unique_class_status.class_id IS NOT NULL)
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.class_id IS NOT NULL) AND (temp_report_data.class_of_date BETWEEN @class_from_date AND @class_upto_date)
END
ELSE
BEGIN
--SELECT
--temp_enrollment_1.id,
--temp_enrollment_1.enroll_id,
--temp_enrollment_1.enroll_name,
--temp_enrollment_1.department_id,
--temp_enrollment_1.department_name,
--temp_enrollment_1.batch_id,
--temp_enrollment_1.batch_name,
--temp_enrollment_1.session_id,
--temp_enrollment_1.session_name,
--dbo.temp_unique_class_status.record_id,
--dbo.temp_unique_class_status.class_id,
--dbo.temp_unique_class_status.class_of_date,
--dbo.temp_unique_class_status.weekday,
--dbo.temp_unique_class_status.class_start_time,
--dbo.temp_unique_class_status.class_end_time,
--dbo.temp_unique_class_status.class_subject_or_department,
--dbo.temp_unique_class_status.class_topic,
--dbo.temp_unique_class_status.uid,
--dbo.temp_unique_class_status.real_svr_Id,
--dbo.temp_unique_class_status.astrRemoteIP,
--dbo.temp_unique_class_status.anDeviceID,
--dbo.temp_unique_class_status.log_time,
--dbo.temp_unique_class_status.class_status,
--dbo.unique_class.faculty_enrollment_uid,
--dbo.temp_enrollment.enroll_name AS faculty_name,
--dbo.temp_enrollment.department_name AS faculty_department_name,
--dbo.temp_enrollment.department_id AS faculty_department_id
--FROM dbo.unique_class INNER JOIN
--dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
--dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
--dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
--WHERE ((dbo.temp_unique_class_status.class_of_date = @class_from_date) OR (dbo.temp_unique_class_status.class_of_date = @class_upto_date))
--AND (dbo.temp_unique_class_status.class_id IS NOT NULL)
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.class_id IS NOT NULL) AND (temp_report_data.class_of_date = @class_from_date) AND (temp_report_data.class_of_date = @class_upto_date)
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_classes_of_date_group_by_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_classes_of_date_group_by_batch]
@class_of_date date
as
begin
SELECT
dbo.batch.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.unique_class.class_start_time AS 'Start Time',
dbo.unique_class.class_end_time AS 'End Time',
dbo.unique_class.class_subject_or_department AS 'Subject'
FROM dbo.batch INNER JOIN
dbo.unique_class ON dbo.batch.batch_id = dbo.unique_class.batch_id
--WHERE (dbo.unique_class.class_of_date = @class_of_date)
ORDER BY dbo.batch.batch_name, dbo.unique_class.class_start_time
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_Class_report] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_Class_report]
@class_id nvarchar(36)
as
begin
--if(LEN(@class_id) = 36)
--BEGIN
-- DECLARE
-- @anDeviceID nvarchar(50),
-- @astrRemoteIP nvarchar(50),
-- @batch_id uniqueidentifier
-- SELECT TOP (1) @anDeviceID = anDeviceID, @astrRemoteIP = astrRemoteIP, @batch_id = batch_id FROM real_svr GROUP BY anDeviceID, astrRemoteIP, batch_id, class_id HAVING (class_id = @class_id) AND (anDeviceID IS NOT NULL) ORDER BY COUNT(id) DESC
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.class_id = @class_id)
-- END
-- ELSE
-- BEGIN
--SELECT
--temp_enrollment_1.id,
--temp_enrollment_1.enroll_id,
--temp_enrollment_1.enroll_name,
--temp_enrollment_1.department_id,
--temp_enrollment_1.department_name,
--temp_enrollment_1.batch_id,
--temp_enrollment_1.batch_name,
--temp_enrollment_1.session_id,
--temp_enrollment_1.session_name,
--dbo.temp_unique_class_status.record_id,
--dbo.temp_unique_class_status.class_id,
--dbo.temp_unique_class_status.class_of_date,
--dbo.temp_unique_class_status.weekday,
--dbo.temp_unique_class_status.class_start_time,
--dbo.temp_unique_class_status.class_end_time,
--dbo.temp_unique_class_status.class_subject_or_department,
--dbo.temp_unique_class_status.class_topic,
--dbo.temp_unique_class_status.uid,
--dbo.temp_unique_class_status.real_svr_Id,
--dbo.temp_unique_class_status.astrRemoteIP,
--dbo.temp_unique_class_status.anDeviceID,
--dbo.temp_unique_class_status.log_time,
--dbo.temp_unique_class_status.class_status,
--dbo.unique_class.faculty_enrollment_uid,
--dbo.temp_enrollment.enroll_name AS faculty_name,
--dbo.temp_enrollment.department_name AS faculty_department_name,
--dbo.temp_enrollment.department_id AS faculty_department_id
--FROM dbo.unique_class INNER JOIN
--dbo.temp_unique_class_status ON dbo.unique_class.class_id = dbo.temp_unique_class_status.class_id INNER JOIN
--dbo.temp_enrollment ON dbo.unique_class.faculty_enrollment_uid = dbo.temp_enrollment.id LEFT OUTER JOIN
--dbo.temp_enrollment AS temp_enrollment_1 ON dbo.temp_unique_class_status.enroll_id = temp_enrollment_1.enroll_id
--WHERE (dbo.temp_unique_class_status.class_id IS NOT NULL)
-- END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_batch_student] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_batch_student]
@batch_id nvarchar(36),
@session_id nvarchar(36)
as
begin
if((len(@batch_id) > 0) AND (len(@session_id) > 0))
BEGIN
SELECT
dbo.batch_student.id,
dbo.batch_student.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.batch_student.session_id,
dbo.session.session_name AS 'Session',
dbo.batch_student.batch_student_enroll_id AS 'Enroll Id',
dbo.batch_student.enroll_name AS 'Enroll Name'
FROM dbo.batch INNER JOIN
dbo.batch_student ON dbo.batch.batch_id = dbo.batch_student.batch_id INNER JOIN
dbo.session ON dbo.batch_student.session_id = dbo.session.session_id
WHERE (dbo.batch_student.batch_id = @batch_id) AND (dbo.batch_student.session_id = @session_id)
ORDER BY dbo.batch_student.batch_student_enroll_id
END
ELSE if((len(@batch_id) > 0) AND (len(@session_id) = 0))
BEGIN
SELECT
dbo.batch_student.id,
dbo.batch_student.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.batch_student.session_id,
dbo.session.session_name AS 'Session',
dbo.batch_student.batch_student_enroll_id AS 'Enroll Id',
dbo.batch_student.enroll_name AS 'Enroll Name'
FROM dbo.batch INNER JOIN
dbo.batch_student ON dbo.batch.batch_id = dbo.batch_student.batch_id INNER JOIN
dbo.session ON dbo.batch_student.session_id = dbo.session.session_id
WHERE (dbo.batch_student.batch_id = @batch_id)
ORDER BY dbo.batch_student.batch_student_enroll_id
END
ELSE if((len(@batch_id) = 0) AND (len(@session_id) > 0))
BEGIN
SELECT
dbo.batch_student.id,
dbo.batch_student.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.batch_student.session_id,
dbo.session.session_name AS 'Session',
dbo.batch_student.batch_student_enroll_id AS 'Enroll Id',
dbo.batch_student.enroll_name AS 'Enroll Name'
FROM dbo.batch INNER JOIN
dbo.batch_student ON dbo.batch.batch_id = dbo.batch_student.batch_id INNER JOIN
dbo.session ON dbo.batch_student.session_id = dbo.session.session_id
WHERE (dbo.batch_student.session_id = @session_id)
ORDER BY dbo.batch_student.batch_student_enroll_id
END
ELSE
BEGIN
SELECT
dbo.batch_student.id,
dbo.batch_student.batch_id,
dbo.batch.batch_name AS 'Batch',
dbo.batch_student.session_id,
dbo.session.session_name AS 'Session',
dbo.batch_student.batch_student_enroll_id AS 'Enroll Id',
dbo.batch_student.enroll_name AS 'Enroll Name'
FROM dbo.batch INNER JOIN
dbo.batch_student ON dbo.batch.batch_id = dbo.batch_student.batch_id INNER JOIN
dbo.session ON dbo.batch_student.session_id = dbo.session.session_id
ORDER BY dbo.batch_student.batch_student_enroll_id
END
End
GO
/****** Object: StoredProcedure [dbo].[sp_Display_Attendance_Percentage_report] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Display_Attendance_Percentage_report]
@enroll_id numeric,
@class_subject_or_department nvarchar(250),
@fromDate date,
@endDate date
as
begin
DECLARE
@attendance_of_days numeric(18,0),
@attendance_number_of_present numeric(18,0),
@attendance_percentage numeric(18,2)
SELECT @attendance_of_days = 0, @attendance_number_of_present = 0, @attendance_percentage = 0
set @attendance_of_days = ISNULL((SELECT COUNT(real_svr_Id) FROM temp_report_data WHERE (enroll_id = @enroll_id) AND (class_of_date BETWEEN @fromDate AND @endDate) AND (class_subject_or_department = @class_subject_or_department) AND ((class_status = N'A') OR (class_status = N'P'))),0)
set @attendance_number_of_present = ISNULL((SELECT COUNT(real_svr_Id) FROM temp_report_data WHERE (enroll_id = @enroll_id) AND (class_of_date BETWEEN @fromDate AND @endDate) AND (class_subject_or_department = @class_subject_or_department) AND (class_status = N'P')),0)
BEGIN TRY
set @attendance_percentage = ((@attendance_number_of_present/@attendance_of_days) * 100)
END TRY
BEGIN CATCH
set @attendance_percentage = 0
END CATCH
-- SELECT
-- dbo.temp_enrollment.id,
-- dbo.temp_enrollment.enroll_id,
-- dbo.temp_enrollment.enroll_name,
-- dbo.temp_enrollment.department_id,
-- dbo.temp_enrollment.department_name,
-- dbo.temp_enrollment.batch_id,
-- dbo.temp_enrollment.batch_name,
-- dbo.temp_enrollment.session_id,
-- dbo.temp_enrollment.session_name,
-- dbo.temp_unique_class_status.record_id,
-- dbo.temp_unique_class_status.class_id,
-- dbo.temp_unique_class_status.class_of_date,
-- dbo.temp_unique_class_status.[weekday],
-- dbo.temp_unique_class_status.class_start_time,
-- dbo.temp_unique_class_status.class_end_time,
-- dbo.temp_unique_class_status.class_subject_or_department,
-- dbo.temp_unique_class_status.class_topic,
-- dbo.temp_unique_class_status.[uid],
-- dbo.temp_unique_class_status.real_svr_Id,
-- dbo.temp_unique_class_status.astrRemoteIP,
-- dbo.temp_unique_class_status.anDeviceID,
-- dbo.temp_unique_class_status.log_time,
-- dbo.temp_unique_class_status.class_status,
-- @attendance_percentage AS attendance_percentage,
-- @attendance_of_days AS attendance_of_days,
-- @attendance_number_of_present AS attendance_number_of_present
-- FROM temp_enrollment RIGHT OUTER JOIN temp_unique_class_status ON temp_enrollment.enroll_id = temp_unique_class_status.enroll_id
-- WHERE (temp_enrollment.enroll_id = @enroll_id)
-- AND (temp_unique_class_status.class_of_date BETWEEN @fromDate AND @endDate)
-- AND (temp_unique_class_status.class_subject_or_department = @class_subject_or_department)
-- AND (temp_unique_class_status.class_status IS NOT NULL)
-- AND (temp_unique_class_status.class_status != '')
-- ORDER BY log_time
SELECT
temp_report_data.id, temp_report_data.enroll_id, temp_report_data.enroll_name, temp_report_data.department_id, temp_report_data.department_name, temp_report_data.batch_id, temp_report_data.batch_name,
temp_report_data.session_id, temp_report_data.session_name, temp_report_data.real_svr_Id AS record_id, temp_report_data.class_id, temp_report_data.class_of_date, temp_report_data.weekday,
temp_report_data.class_start_time, temp_report_data.class_end_time, temp_report_data.class_subject_or_department, temp_report_data.class_topic, temp_report_data.id AS uid, temp_report_data.astrRemoteIP,
temp_report_data.anDeviceID, temp_report_data.log_time, temp_report_data.class_status, temp_report_data.faculty_enrollment_uid, faculty_enrollment.enroll_name AS faculty_name,
department.department_name AS faculty_department_name, faculty_enrollment.department_id AS faculty_department_id,
@attendance_percentage AS attendance_percentage,
@attendance_of_days AS attendance_of_days,
@attendance_number_of_present AS attendance_number_of_present
FROM temp_report_data INNER JOIN
faculty_enrollment ON temp_report_data.faculty_enrollment_uid = faculty_enrollment.id INNER JOIN
department ON faculty_enrollment.department_id = department.department_id
WHERE (temp_report_data.enroll_id = @enroll_id)
AND (temp_report_data.class_of_date BETWEEN @fromDate AND @endDate)
AND (temp_report_data.class_subject_or_department = @class_subject_or_department)
AND (temp_report_data.class_status IS NOT NULL)
AND (temp_report_data.class_status != '')
ORDER BY log_time
-- --FROM dbo.temp_enrollment RIGHT OUTER JOIN dbo.temp_unique_class_status
-- --ON dbo.temp_enrollment.enroll_id = dbo.temp_unique_class_status.enroll_id
-- --WHERE (dbo.temp_enrollment.enroll_id = @enroll_id)
-- --AND (temp_unique_class_status.class_of_date BETWEEN @fromDate AND @endDate)
-- --SELECT temp_enrollment.id, temp_enrollment.enroll_id, temp_enrollment.enroll_name, temp_enrollment.department_id, temp_enrollment.department_name,
-- --temp_enrollment.batch_id, temp_enrollment.batch_name, temp_enrollment.session_id, temp_enrollment.session_name, temp_unique_class_status.record_id,
-- -- temp_unique_class_status.class_id, temp_unique_class_status.class_of_date, temp_unique_class_status.weekday, temp_unique_class_status.class_start_time,
-- -- temp_unique_class_status.class_end_time, temp_unique_class_status.class_subject_or_department, temp_unique_class_status.class_topic,
-- -- temp_unique_class_status.uid, temp_unique_class_status.real_svr_Id, temp_unique_class_status.astrRemoteIP, temp_unique_class_status.anDeviceID,
-- -- temp_unique_class_status.log_time, temp_unique_class_status.class_status
-- --FROM temp_enrollment RIGHT OUTER JOIN temp_unique_class_status ON temp_enrollment.enroll_id = temp_unique_class_status.enroll_id
-- --WHERE (temp_enrollment.enroll_id = @enroll_id)
-- --AND (temp_unique_class_status.class_of_date BETWEEN @fromDate AND @endDate)
-- --AND (temp_unique_class_status.class_subject_or_department = @class_subject_or_department)
-- --AND (temp_unique_class_status.class_status IS NOT NULL)
-- --AND (temp_unique_class_status.class_status != '')
End
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_unique_class] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_unique_class]
@class_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from unique_class WHERE (class_id = @class_id)) > 0
BEGIN
DELETE FROM unique_class WHERE (class_id = @class_id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_session] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_session]
@session_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from [session] WHERE (session_id = @session_id)) > 0
BEGIN
if((SELECT COUNT(id) FROM dbo.batch_student WHERE (session_id = @session_id)) = 0)
BEGIN
DELETE FROM [session] WHERE (session_id = @session_id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 3 -- Record in used.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_faculty_enrollment] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_faculty_enrollment]
@id uniqueidentifier,
@out int output
as
begin
if(select count(*) from faculty_enrollment WHERE (id = @id)) > 0
BEGIN
DELETE FROM faculty_enrollment WHERE (id = @id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_department] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_department]
@department_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from department WHERE (department_id = @department_id)) > 0
BEGIN
if((SELECT COUNT(id) AS id FROM faculty_enrollment WHERE department_id = @department_id) = 0)
BEGIN
DELETE FROM department WHERE (department_id = @department_id)
set @out = 1 -- Record Deleted successfully.
END
ELSE
BEGIN
set @out = 3 -- Record already in used.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_batch_student] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_batch_student]
@id uniqueidentifier,
@out int output
as
begin
if(select count(*) from batch_student WHERE (id = @id)) > 0
BEGIN
DELETE FROM batch_student WHERE (id = @id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
/****** Object: StoredProcedure [dbo].[sp_Delete_batch] Script Date: 04/29/2024 16:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Delete_batch]
@batch_id uniqueidentifier,
@out int output
as
begin
if(select count(*) from batch WHERE (batch_id = @batch_id)) > 0
BEGIN
if((SELECT COUNT(id) FROM dbo.batch_student WHERE (batch_id = @batch_id)) = 0)
BEGIN
DELETE FROM batch WHERE (batch_id = @batch_id)
set @out = 1 -- Record Deleted successfully.
END
else
BEGIN
set @out = 3 -- Record in used.
END
END
else
BEGIN
set @out = 2 -- Record not found.
END
return @out
end
GO
Comments
Post a Comment