getDigiMarkerDataSP

SELECT Distinct B.RowID,'CSVTU ' + B.ExamYear as AppName ,A.CourseCode,F.CourseName,   A.ProgramCode AS BranchCode,E.ProgramName AS BranchName,'' as DistrictCode,'' as DistrictName,   Y.ExamCenterCode AS ExamCenterCode, I.COLLEGENAME as 'ExamCenterName', A.CollegeCode AS CollegeCode, G.COLLEGENAME AS CollegeName,   B.Semester, B.ExamYear as 'ExamSession',B.SubjectCode,C.SubjectName,C.SubjectType,'E' as 'ExamMedium',B1.RollNo AS RollNo,   B.SubjectCode + 'E' + A.RollNo as Barcode, A.EnrollmentNo, A.Name, Z.AppID as ApplicationNo, A.DOB, A.Father, B.IsActive as 'IsActive', GETDATE() as 'CreatedOn',   'CSVTU Portal' as CreatedBy,  Cast(1 as varchar(10)) + Right('0000000'+Cast(ROW_NUMBER() OVER(ORDER BY B.SubjectCode, B.RollNo) as varchar(10)),7) PrintBarcode,   upper(B.IsAttendance) as 'Attendance', B.AttendanceMarkedOn, B.AttendanceMarkedBy   FROM Digivarsity_DB.dbo.StudentMasterTb A                             INNER JOIN Digivarsity_DB.dbo.EligibleStudentExamSubjectTB (Nolock) B ON A.RollNo = B.RollNo AND B.isactive = 1                          INNER JOIN Digivarsity_DB.dbo.EligibleStudentExamDataTB (Nolock) B1 ON B1.RollNo = B.RollNo and B1.Semester = B.Semester and B1.ExamType = B.ExamType and B1.ExamYear = B.ExamYear                   INNER JOIN Digivarsity_DB.dbo.SubjectMasterCsvtU C ON B.Semester = C.Semester and A.CourseCode = C.CourseCode AND B.SubjectCode = C.SubjectCode AND C.isactive = 1 --AND A.ProgramCode = C.ProgramCode                     and C.ProgramCode =        Case        --When A1.CourseCode = '46' and @Semester in ('1 SEMESTER','2 SEMESTER') and isnull(A1.GroupCode,'')  = '' then '999'        When A.CourseCode = '46' and B.Semester in ('1 SEMESTER','2 SEMESTER') and isnull(A.GroupCode,'')  <> '' then A.GroupCode       When A.CourseCode = '1' and B.Semester in ('1 SEMESTER','2 SEMESTER')  and isnull(A.GroupCode,'')  <> '' then A.GroupCode       else A.ProgramCode End         --End       Inner Join Digivarsity_DB.dbo.ProgramMasterCSVTU E on A.ProgramCode = E.ProgramCode and E.CourseCode = A.CourseCode and E.IsActive = 1                     Inner Join Digivarsity_DB.dbo.CourseMasterCSVTU F on A.CourseCode = F.CourseCode and F.IsActive =1     INNER JOIN Digivarsity_DB.dbo.CollegeMastertb G ON A.CollegeCode = G.CollegeCode AND G.IsActive = 1   /*   Inner Join (                    Select --A.*                    A.RollNo, A.Semester, A.ExamYear,A.ExamType,Max(C.AppID) as AppID    from Digivarsity_DB.dbo.SemesterBulkPayBatchDetailTB (Nolock) A                    Inner Join DigiVarsity_MasterDB.dbo.TrackStatusTb (Nolock) C on A.BatchAppID = C.AppID  and C.PayFlag ='Y'--and A.AppID = C.AppID                    where A.IsActive = 1 and C.IsActive = 1                    Group by A.RollNo, A.Semester, A.ExamYear,A.ExamType--,A.BatchAppID --, A.BatchAppID,C.PayFlag                 ) Z on --B1.AppID = Z.AppID and /*comment Appid mismatch in StudentData*/                 A.RollNo = Z.RollNo and B.Semester = Z.Semester and B.ExamYear = Z.ExamYear and Z.ExamType = B.ExamType       */   Inner Join (                    Select --A.*                    A.RollNo, A.Semester, A.ExamYear,Max(A.AppID) as AppID    from DigiVarsity_MasterDB.dbo.TrackStatusTb A (Nolock) --and A.AppID = C.AppID                    where A.IsActive = 1 and A.PayFlag ='Y'                  Group by A.RollNo, A.Semester, A.ExamYear             ) Z on --B1.AppID = Z.AppID and /*comment Appid mismatch in StudentData*/                 A.RollNo = Z.RollNo and B.Semester = Z.Semester and B.ExamYear = Z.ExamYear        Left Join (   Select A.ExamCenterCode,A.CollegeCode    , A.ExamYear,A.CourseCode,A.Semester                    From digivarsity_db.dbo.ExamCenterDetailTB (Nolock) A                                      where 1=1 and A.IsActive = 1     ) Y on B1.CollegeCode = Y.CollegeCode and B1.Semester = Y.Semester and B1.CourseCode = Y.CourseCode    and B1.ExamYear = Y.ExamYear   LEFT JOIN Digivarsity_DB.dbo.CollegeMastertb I ON Y.ExamCenterCode = I.CollegeCode AND I.IsActive = 1   Left Join Digivarsity_DB.Dbo.ExamTimeTableTb X on X.SubjectCode = B.SubjectCode and X.ExamYear = B.ExamYear and X.Isactive = 1   WHERE 1=1 and B1.isactive = 1 AND B.isactive = 1 and A.Isactive = 1 and C.SubjectType = 'Theory'      and isnull(C.IsOldScheme,0) = isnull(B.IsOldSchema,0) --and isnull(ExamCenterCode,'') <> ''   And B.ExamYear = @ExamSession --Case When @ExamSession Is Not Null And @ExamSession <> '' Then @ExamSession Else B.ExamYear end                            --And A.CollegeCode = (Case When @CollegeCode Is Null Or @CollegeCode = '0' Then A.CollegeCode Else @CollegeCode End)                           And A.CourseCode = (Case When isnull(@Course,'') = '' or @Course Is Null Or @Course = '0' Then A.CourseCode Else @Course End)                     And A.ProgramCode = (Case When isnull(@Program,'') = '' or @Program Is Null Or @Program = '0' Then A.ProgramCode Else @Program End)                                                                                   --And B.Semester = (Case When isnull(@Semester,'') = '' or @Semester Is Not Null or @Semester = '0' Then B.Semester Else @Semester end)   and B.Semester =  @Semester --(Case When @Semester Is Null Or isnull(@Semester,'0') = '0' Then B.Semester Else @Semester End)     And B.ExamType = (Case When isnull(@ExamType,'') = '' or @ExamType Is Null Or @ExamType = '0' Then B.ExamType Else @ExamType End)                  --And B.SubjectCode = Case When @SubjectCode Is Not Null or @SubjectCode = '0' Then @SubjectCode Else B.SubjectCode end                       AND (ISNULL(@RollNo,'')='' OR B.RollNo=@RollNo)    and isnull(C.IsVCESubject,0) = 0 --and B.RowId = '28031469'   And ((nullif(@FromDate,'') is null and nullif(@ToDate,'') is null) or (cast(X.ExamDate as date) between cast(@FromDate as date) and cast(@ToDate as date)))      --order By B.SubjectCode, B.RollNo, A.CollegeCode,A.CourseCode, A.ProgramCode, B.Semester, B.IsAttendance,C.SubjectName      Union All      SELECT distinct B1.RowID,'CSVTU ' + B1.ExamYear as AppName ,A.CourseCode,F.CourseName,   A.ProgramCode AS BranchCode,E.ProgramName AS BranchName,'' as DistrictCode,'' as DistrictName,   Y.ExamCenterCode AS ExamCenterCode, I.COLLEGENAME as 'ExamCenterName', A.CollegeCode AS CollegeCode, G.COLLEGENAME AS CollegeName,   B1.Semester, B1.ExamYear as 'ExamSession',B1.SubjectCode,C.SubjectName,C.SubjectType,'E' as 'ExamMedium',B1.RollNo AS RollNo,   B1.SubjectCode + 'E' + A.RollNo as Barcode, A.EnrollmentNo, A.Name, '' as ApplicationNo, A.DOB, A.Father, B1.IsActive as 'IsActive', GETDATE() as 'CreatedOn',   'CSVTU Portal' as CreatedBy,  Cast(1 as varchar(10)) + Right('0000000'+Cast(ROW_NUMBER() OVER(ORDER BY B1.SubjectCode, B1.RollNo) as varchar(10)),7) PrintBarcode,   'P' as 'Attendance', B1.CreatedOn as AttendanceMarkedOn, B1.CreatedBy AttendanceMarkedBy       --Select *   FROM Digivarsity_DB.dbo.StudentMasterTb A     INNER JOIN Digivarsity_DB.dbo.AutonomousExamDataTB B1 WITH (NOLOCK) ON A.RollNo = B1.RollNo --AND B1.ExamYear = B1.ExamYear AND B1.IsActive = 1   INNER JOIN Digivarsity_DB.dbo.SubjectMasterCsvtU C ON B1.Semester = C.Semester and A.CourseCode = C.CourseCode AND B1.SubjectCode = C.SubjectCode AND C.isactive = 1 --AND A.ProgramCode = C.ProgramCode                     and C.ProgramCode =        Case        --When A1.CourseCode = '46' and @Semester in ('1 SEMESTER','2 SEMESTER') and isnull(A1.GroupCode,'')  = '' then '999'        When A.CourseCode = '46' and B1.Semester in ('1 SEMESTER','2 SEMESTER') and isnull(A.GroupCode,'')  <> '' then A.GroupCode       When A.CourseCode = '1' and B1.Semester in ('1 SEMESTER','2 SEMESTER')  and isnull(A.GroupCode,'')  <> '' then A.GroupCode       else A.ProgramCode End         --End       Inner Join Digivarsity_DB.dbo.ProgramMasterCSVTU E on A.ProgramCode = E.ProgramCode and E.CourseCode = A.CourseCode and E.IsActive = 1                     Inner Join Digivarsity_DB.dbo.CourseMasterCSVTU F on A.CourseCode = F.CourseCode and F.IsActive =1     INNER JOIN Digivarsity_DB.dbo.CollegeMastertb G ON A.CollegeCode = G.CollegeCode AND G.IsActive = 1        Left Join (   Select A.ExamCenterCode,A.CollegeCode    , A.ExamYear,A.CourseCode,A.Semester                    From digivarsity_db.dbo.ExamCenterDetailTB (Nolock) A                                      where 1=1 and A.IsActive = 1     ) Y on A.CollegeCode = Y.CollegeCode and B1.Semester = Y.Semester and A.CourseCode = Y.CourseCode    and B1.ExamYear = Y.ExamYear   LEFT JOIN Digivarsity_DB.dbo.CollegeMastertb I ON Y.ExamCenterCode = I.CollegeCode AND I.IsActive = 1   Left Join Digivarsity_DB.Dbo.ExamTimeTableTb X on X.SubjectCode = B1.SubjectCode and X.ExamYear = B1.ExamYear and X.Isactive = 1   WHERE 1=1 and B1.isactive = 1 AND B1.isactive = 1 and A.Isactive = 1 and C.SubjectType = 'Theory' --and isnull(ExamCenterCode,'') <> ''   And B1.ExamYear = @ExamSession --Case When @ExamSession Is Not Null And @ExamSession <> '' Then @ExamSession Else B.ExamYear end                            --And A.CollegeCode = (Case When @CollegeCode Is Null Or @CollegeCode = '0' Then A.CollegeCode Else @CollegeCode End)                           And A.CourseCode = (Case When isnull(@Course,'') = '' or @Course Is Null Or @Course = '0' Then A.CourseCode Else @Course End)                     And A.ProgramCode = (Case When isnull(@Program,'') = '' or @Program Is Null Or @Program = '0' Then A.ProgramCode Else @Program End)                                                                                   And B1.Semester =  @Semester --Case When isnull(@Semester,'') = '' or @Semester Is Not Null or @Semester = '0' Then B1.Semester Else @Semester end                         And B1.ExamType = (Case When isnull(@ExamType,'') = '' or @ExamType Is Null Or @ExamType = '0' Then B1.ExamType Else @ExamType End)                  --And B.SubjectCode = Case When @SubjectCode Is Not Null or @SubjectCode = '0' Then @SubjectCode Else B.SubjectCode end                       AND (ISNULL(@RollNo,'')='' OR B1.RollNo=@RollNo)    and isnull(C.IsVCESubject,0) = 0 --and B.RowId = '28031469'   And ((nullif(@FromDate,'') is null and nullif(@ToDate,'') is null) or (cast(X.ExamDate as date) between cast(@FromDate as date) and cast(@ToDate as date)))         order By B.SubjectCode, B1.RollNo