scholarDb.cs

//GetSearchScholar_ResearchOutput
        public List<ScholarSearch_ResearchOutput> GetSearchScholar_ResearchOutput(string id)
        {
            DataTable dt = new DataTable();
            var Dbi = new ScholarSearch_ResearchOutput();
            string q = @"SELECT DISTINCT REF_NO_, TYPE_PUBLICATION,CATEGORY_PUBLICATION,TITLE,JOURNAL_TITLE,YEAR_,NAMA_
                        FROM
                        (SELECT A.REFERENCE_NO AS REF_NO_ , B.NAMA_PARAMETER AS TYPE_PUBLICATION ,C.NAMA_PARAMETER AS CATEGORY_PUBLICATION ,
                        A.SUB_JUDUL AS TITLE ,A.JUDUL AS JOURNAL_TITLE ,A.PUBLICATION_YEAR AS YEAR_ ,I.NAMA AS NAMA_
                        FROM RMC_PUBLICATION A
                        LEFT JOIN RMC_PARAMETER B  ON B.PARAM_PK =A.PUBLICATION_TYPE_FK AND B.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_PARAMETER C  ON C.PARAM_PK =A.CATEGORY_JOURNAL_PROCEEDING_FK AND C.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_PUB_AUTHOR_LIST D ON D.PUBLICATION_FK=A.PUBLICATION_PK AND D.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_PARAMETER G ON G.PARAM_PK =D.TYPE_AUTHOR_FK AND G.TKH_HAPUS IS NULL
                        LEFT JOIN HR_STAF H ON H.STAF_PK =D.STAF_FK AND G.TKH_HAPUS IS NULL
                        LEFT JOIN HR_MAKLUMAT_PERIBADI I ON I.MAKLUMAT_PERIBADI_PK=H.MAKLUMAT_PERIBADI_FK AND I.TKH_HAPUS IS NULL
                        WHERE A.STATUS_FK =340 AND A.TKH_HAPUS IS NULL
                        GROUP BY A.REFERENCE_NO,B.NAMA_PARAMETER,C.NAMA_PARAMETER,A.SUB_JUDUL,A.JUDUL,A.PUBLICATION_YEAR,I.NAMA )
                        WHERE UPPER(TYPE_PUBLICATION) LIKE UPPER('%" + id + "%') " +
                        " OR UPPER(TITLE) LIKE UPPER('%" + id + "%') " +
                        " OR UPPER(JOURNAL_TITLE) LIKE UPPER('%" + id + "%')" +
                        " OR UPPER(YEAR_) LIKE UPPER('%" + id + "%') " +
                        " OR UPPER(NAMA_) LIKE UPPER('%" + id + "%') ORDER BY YEAR_ DESC";

            dt = CF.getOracleDT(q);
            string refno, type_publication, category_publication, title, journal_title, year;
            int s = dt.Rows.Count;
            List<ScholarSearch_ResearchOutput> ObjResOutput = new List<ScholarSearch_ResearchOutput>();

            for(int i=0; i<s; i++)
            {
                refno = dt.Rows[i][0].ToString();
                type_publication = dt.Rows[i][1].ToString();
                category_publication = dt.Rows[i][2].ToString();
                title = dt.Rows[i][3].ToString();
                journal_title = dt.Rows[i][4].ToString();
                year = dt.Rows[i][5].ToString();

                //get the author list for research output



                ObjResOutput.Add(new ScholarSearch_ResearchOutput {
                    RefNo = refno,
                    TypeofPublications = type_publication,
                    Title = title,
                    JournalTitle = journal_title,
                    PublishedYear = year,
                    CategoryPublications = category_publication
                });
            }
            return ObjResOutput;
        }

        public List<ScholarSearch_ResearchOutput_GetListAuthor> GetResearchOutput_ListAuthor(string id)
        {
            DataTable dt = new DataTable();
            var Dbi = new ScholarSearch_ResearchOutput_GetListAuthor();
            string q = @"SELECT A.REFERENCE_NO, NVL(
                        CASE WHEN B.STAF_FK IS NOT NULL THEN
                        (SELECT initcap(NAMA) || '(' || G.NAMA_PARAMETER || ')'
                        FROM HR_MAKLUMAT_PERIBADI WHERE TKH_HAPUS IS NULL
                        AND MAKLUMAT_PERIBADI_PK = (SELECT MAKLUMAT_PERIBADI_FK FROM HR_STAF WHERE STAF_PK = B.STAF_FK AND TKH_HAPUS IS NULL))
                        WHEN B.STAF_FK IS NULL THEN
                        (CASE WHEN B.STUDENT_FK IS NOT NULL THEN (SELECT initcap(NAME) || '(' || G.NAMA_PARAMETER || ')'
                        FROM RMC_ACT_STUDENT WHERE STUDENT_PK = B.STUDENT_FK AND TKH_HAPUS IS NULL)
                        WHEN B.STUDENT_FK IS NULL THEN (SELECT initcap(NAME) || '(' || G.NAMA_PARAMETER || ')'
                        FROM RMC_PUB_GUEST WHERE PUB_GUEST_PK = B.PUB_GUEST_FK AND TKH_HAPUS IS NULL ) END)
                        END,'-') AS NAMA ,NVL(CASE WHEN B.STAF_FK IS NOT NULL THEN
                        (SELECT initcap(NO_PEKERJA) FROM HR_MAKLUMAT_PERIBADI
                        WHERE TKH_HAPUS IS NULL AND MAKLUMAT_PERIBADI_PK = (SELECT MAKLUMAT_PERIBADI_FK FROM HR_STAF
                        WHERE STAF_PK = B.STAF_FK AND TKH_HAPUS IS NULL)) WHEN B.STAF_FK IS NULL
                        THEN ('0') END,'0') AS NOPEKERJA FROM RMC_PUBLICATION A
                        LEFT JOIN RMC_PUB_AUTHOR_LIST B ON B.PUBLICATION_FK=A.PUBLICATION_PK AND B.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_ACT_STUDENT C ON C.STUDENT_PK = B.STUDENT_FK AND C.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_PUB_GUEST D ON D.PUB_GUEST_PK = B.PUB_GUEST_FK AND D.TKH_HAPUS IS NULL
                        LEFT JOIN HR_STAF ST ON ST.STAF_PK =B.STAF_FK AND ST.TKH_HAPUS IS NULL
                        LEFT JOIN hr_maklumat_Peribadi mp ON mp.maklumat_peribadi_pk=ST.maklumat_peribadi_fk AND MP.TKH_HAPUS IS NULL
                        LEFT JOIN RMC_PARAMETER G ON G.PARAM_PK =B.TYPE_AUTHOR_FK AND G.TKH_HAPUS IS NULL
                        WHERE A.REFERENCE_NO='" + id + "' AND A.TKH_HAPUS IS NULL ORDER BY A.REFERENCE_NO ASC";

            dt = CF.getOracleDT(q);
            string refNo, authorName, stafNo;
            int s = dt.Rows.Count;

            List<ScholarSearch_ResearchOutput_GetListAuthor> ObjGetAuthorList = new List<ScholarSearch_ResearchOutput_GetListAuthor>();
            for(int i=0; i < s; i++)
            {
                refNo = dt.Rows[i][0].ToString();
                authorName = dt.Rows[i][1].ToString();
                stafNo = dt.Rows[i][2].ToString();

                ObjGetAuthorList.Add(new ScholarSearch_ResearchOutput_GetListAuthor
                {
                    RefNo = refNo,
                    AuthorName = authorName,
                    StafNo = stafNo
                });
            }

            return ObjGetAuthorList;

        }