Imports System Imports System.Data Imports System.Data.SqlClient Partial Class CountPublications Inherits System.Web.UI.Page Dim StrCon As String = ConfigurationManager.ConnectionStrings("GNDUWebSite").ConnectionString Dim i As Integer Dim CMD As SqlCommand Dim SQL As String Dim MyReader As SqlDataReader Dim cn As New SqlConnection Dim Post, DurationFrom, DurationUpto, Experience As String Private Sub CountPublications_Load(sender As Object, e As EventArgs) Handles Me.Load Dim MemberID, DeptID As Long Dim EmpName, DeptName As String Dim JPapers, CPapers, CBooks, CArticles As Integer Dim ChaptersinBooks, TechnicalReports As Integer Dim TotalPublications As Integer Dim SNo As Integer = 1 Dim OldDeptName As String = "" Dim GrandTotal As Integer = 0 Dim TotalJPapers, TotalCPapers, TotalCBooks, TotalArticles As Integer Dim TotalChaptersinBooks, TotalTechnicalReports As Integer TotalJPapers = 0 TotalCPapers = 0 TotalCBooks = 0 TotalChaptersinBooks = 0 TotalTechnicalReports = 0 TotalPublications = 0 SQL = " Select f.MemberId, f.EmpName, d.DeptName, f.FacultyType, f.EmpCode from tblFaculityMember f, tblDept d " SQL &= " where designationid In (1, 2, 3, 5, 6, 7) And (FacultyType<>'O' or Facultype is not Null) and f.Deptid=d.deptid and marked is null and CampusID=1 " SQL &= " and " SQL &= " (DomicileStateName Is Not null Or " SQL &= " memberid in " SQL &= " (" SQL &= " select distinct memberid from tblPubDetails where PublicationYear >=2012 and publicationtype in (1, 2, 3, 4, 5, 6, 8)" SQL &= " Or memberid in (select memberid from FacultyProfile_AdminstrativeExperienceAndResponsibilitiesHeld) " SQL &= " or memberid in (select memberid from FacultyProfile_Collaborations) " SQL &= " Or memberid in (select memberid from FacultyProfile_Consultancy) " SQL &= " Or memberid in (select memberid from FacultyProfile_ContributionInAreasofHigherEdu) " SQL &= " Or memberid In (Select memberid from FacultyProfile_HonorsAwards) " SQL &= " Or memberid in (select memberid from FacultyProfile_Memberships) " SQL &= " Or memberid In (Select memberid from FacultyProfile_ResearchProjects) " SQL &= " Or memberid in (select memberid from FacultyProfile_ScholaryAchievements) " SQL &= " Or memberid In (Select memberid from FacultyProfile_ResearchAssignments) " SQL &= " Or memberid in (select memberid from FacultyProfile_SignificantPositions_Additional) " SQL &= "))order by d.DeptName, f.EmpName" StrCon = ConfigurationManager.ConnectionStrings("GNDUWebSite").ConnectionString Try 'outer Using cn As New SqlConnection(StrCon) cn.Open() If cn.State = 1 Then 'YY Using CMD As New SqlCommand '** CMD.CommandText = SQL CMD.Connection = cn Using myreader As SqlDataReader = CMD.ExecuteReader() If myreader.HasRows Then '* Response.Write("
") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") While myreader.Read() '^^^ MemberID = myreader("Memberid") EmpName = myreader("EmpName") If UCase(Left(EmpName, 3)) = "DR." Or UCase(Left(EmpName, 3)) = "DR " Then EmpName = Mid(EmpName, 4) Else End If DeptName = myreader("DeptName") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Using cmd2 As New SqlCommand cmd2.Connection = cn 'JournalPapers SQL = "Select count(*) from tblPubDetails where PublicationType=1 and MemberID= and PublicationYear >=2012" & MemberID cmd2.CommandText = SQL JPapers = cmd2.ExecuteScalar() 'Write(SQL) 'ConfrencePapers SQL = "Select count(*) from tblPubDetails where PublicationType=2 and MemberID= and PublicationYear >=2012" & MemberID cmd2.CommandText = SQL CPapers = cmd2.ExecuteScalar() 'Books: SQL = "Select count(*) from tblPubDetails where PublicationType=3 and MemberID= and PublicationYear >=2012" & MemberID cmd2.CommandText = SQL CBooks = cmd2.ExecuteScalar() 'Write(SQL) 'Chapters in Books SQL = "Select count(*) from tblPubDetails where PublicationType=4 and MemberID= and PublicationYear >=2012" & MemberID 'chapters in books cmd2.CommandText = SQL ChaptersinBooks = cmd2.ExecuteScalar() 'Write(SQL) 'Articles SQL = "Select count(*) from tblPubDetails where PublicationType=5 and MemberID= and PublicationYear >=2012" & MemberID cmd2.CommandText = SQL CArticles = cmd2.ExecuteScalar() 'Write(SQL) 'Technical Reports SQL = "Select count(*) from tblPubDetails where PublicationType= 8 and PublicationYear >=2012 and MemberID=" & MemberID cmd2.CommandText = SQL TechnicalReports = cmd2.ExecuteScalar() 'Write(SQL) If MemberID = 2015100516 Then JPapers = 83 CPapers = 2 CBooks = 1 CArticles = 0 End If ' JPapers, CPapers, CBooks, ChaptersinBooks, CArticles, TechnicalReports If JPapers > 0 Then Response.Write("") Else Response.Write("") End If If CPapers > 0 Then Response.Write("") Else Response.Write("") End If If CBooks > 0 Then Response.Write("") Else Response.Write("") End If If ChaptersinBooks > 0 Then Response.Write("") Else Response.Write("") End If If CArticles > 0 Then Response.Write("") Else Response.Write("") End If If TechnicalReports > 0 Then Response.Write("") Else Response.Write("") End If TotalPublications = JPapers + CPapers + CBooks + CArticles + ChaptersinBooks + TechnicalReports If TotalPublications > 0 Then Response.Write("") Else Response.Write("") End If TotalJPapers += JPapers TotalCPapers += CPapers TotalCBooks += CBooks TotalChaptersinBooks += ChaptersinBooks TotalArticles += CArticles TotalTechnicalReports += TechnicalReports GrandTotal += TotalPublications End Using Response.Write("") SNo = SNo + 1 End While '^^^ Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") 'SQL = " Select f.MemberId, f.EmpName, d.DeptName, f.FacultyType, f.EmpCode from tblFaculityMember f, tblDept d " 'SQL &= " where designationid In (1, 2, 3, 5, 6, 7) And FacultyType='O' and f.Deptid=d.deptid and marked is null " 'SQL &= " and DomicileStateName Is Not null Or Gender is not null and " 'SQL &= " memberid in " 'SQL &= " (" 'SQL &= " select distinct memberid from tblPubDetails where publicationtype in (1, 2, 3, 4, 5, 6)" 'SQL &= " Or memberid in (select memberid from FacultyProfile_AdminstrativeExperienceAndResponsibilitiesHeld) " 'SQL &= " or memberid in (select memberid from FacultyProfile_Collaborations) " 'SQL &= " Or memberid in (select memberid from FacultyProfile_Consultancy) " 'SQL &= " Or memberid in (select memberid from FacultyProfile_ContributionInAreasofHigherEdu) " 'SQL &= " Or memberid In (Select memberid from FacultyProfile_HonorsAwards) " 'SQL &= " Or memberid in (select memberid from FacultyProfile_Memberships) " 'SQL &= " Or memberid In (Select memberid from FacultyProfile_ResearchProjects) " 'SQL &= " Or memberid in (select memberid from FacultyProfile_ScholaryAchievements) " 'SQL &= " Or memberid In (Select memberid from FacultyProfile_ResearchAssignments) " 'SQL &= " Or memberid in (select memberid from FacultyProfile_SignificantPositions_Additional) " 'SQL &= "))order by d.DeptName, f.EmpName" Dim ContractChaptersinBooks, ContractTechnicalReports As Integer Using ContractCMD As New SqlCommand Dim ContractJournals, ContractConferences, ContractBooks, ContractArticles, ContractTotal As Integer ContractCMD.Connection = cn SQL = "Select count(*) from TblPubdetails where publicationID=1 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractJournals = ContractCMD.ExecuteScalar() SQL = "Select count(*) from TblPubdetails where publicationID=2 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractConferences = ContractCMD.ExecuteScalar() SQL = "Select count(*) from TblPubdetails where publicationID=3 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractBooks = ContractCMD.ExecuteScalar() SQL = "Select count(*) from TblPubdetails where publicationID=4 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractChaptersinBooks = ContractCMD.ExecuteScalar() SQL = "Select count(*) from TblPubdetails where publicationID=5 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractArticles = ContractCMD.ExecuteScalar() SQL = "Select count(*) from TblPubdetails where publicationID=8 and memberid in " SQL &= "(Select Memberid from tblFaculityMember where FacultyType='O' and marked is null and DomicileStateName is not Null)" ContractCMD.CommandText = SQL ContractTechnicalReports = ContractCMD.ExecuteScalar() ContractTotal = ContractJournals + ContractConferences + ContractBooks ContractTotal += ContractChaptersinBooks + ContractArticles + ContractTechnicalReports Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") 'again find total s Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") 'reprint Headings Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") End Using Response.Write("
S.NoDeptt.Emp. NamePapers
in
Journals
Papers
in
Conferences
BooksChapters
in
Books
ArticlesTechnical
Report
TOTAL
" & SNo & "." & DeptName & "" & EmpName & "   (" & MemberID & ")" & JPapers & "" & JPapers & "" & CPapers & "" & CPapers & "" & CBooks & "-" & ChaptersinBooks & "" & ChaptersinBooks & "" & CArticles & "" & CArticles & "" & TechnicalReports & "" & TechnicalReports & "" & TotalPublications & "-
Grand Total (Regular Teachers)" & TotalJPapers & "" & TotalCPapers & "" & TotalCBooks & "" & TotalChaptersinBooks & "" & TotalArticles & "" & TotalTechnicalReports & "" & GrandTotal & "
Other Teachers" & ContractJournals & "" & ContractConferences & "" & ContractBooks & "" & ContractChaptersinBooks & "" & ContractArticles & "" & ContractTechnicalReports & "" & ContractTotal & "
Final Total" & TotalJPapers + ContractJournals & "" & TotalCPapers + ContractConferences & "" & TotalCBooks + ContractBooks & "" & TotalChaptersinBooks + ContractChaptersinBooks & "" & TotalArticles + ContractArticles & "" & TotalTechnicalReports + ContractTechnicalReports & "" & GrandTotal + ContractTotal & "
Papers
in
Journals
Papers
in
Conferences
BooksChapters
in
Books
ArticlesTechnical
Report
TOTAL
") Else End If '* End Using End Using '** Else Write("Sorry, Couldnt Connect with Databse. Try Later.") Exit Sub End If 'YY End Using Catch ex As Exception Dim trace As New Diagnostics.StackTrace(ex, True) Dim line As String = Right(trace.ToString, 5) Response.Write("Error : " & ex.Message & ", at line " & line & "") End Try 'outer End Sub Protected Function AajKeeDate() As String Dim dd, mm, yy As String dd = Day(Today) If dd < "10" Then dd = "0" & dd mm = Month(Today) If mm < "10" Then mm = "0" & mm yy = Year(Today) AajKeeDate = dd & "/" & mm & "/" & yy End Function Protected Function AbKaaTime() As String Dim hh, mm As String hh = Hour(TimeOfDay) If hh < "10" Then hh = "0" & hh mm = Minute(TimeOfDay) If mm < "10" Then mm = "0" & mm AbKaaTime = hh & ":" & mm End Function Protected Sub Write(msg As String) Response.Write("
" & msg & "
") Response.Flush() End Sub End Class