ScholarDB.cs

public SCHOLAR_INFO_DETAILS GetScholarDetailsIndexInfo(string id)
        {
            var schDtl = new SCHOLAR_INFO_DETAILS();
            DataTable dtSch = new DataTable();

            string query = @" SELECT ST.NO_PEKERJA,MP.NAMA,COALESCE(f2.deskripsi_en,f.deskripsi_en) AS FAKULTI,
                            NVL(F.DESCRIPTION, '-') AS NICHE_AREA,SC.SCOPUS_ID,
                            COUNT(DISTINCT P.REFERENCE_NO) AS TOTAL_GRANT,
                            (SELECT COUNT(*) AS TOTAL_INDEXED_PUBLICATION
                            FROM rmc_publication a
                            INNER JOIN RMC_PUB_AUTHOR_LIST AL ON AL.PUBLICATION_FK=A.PUBLICATION_PK AND AL.TKH_HAPUS   IS NULL
                            INNER JOIN HR_STAF B ON B.staf_Pk = AL.staf_Fk AND b.tkh_hapus IS NULL
                            WHERE --a.PUBLICATION_TYPE_FK IN (449,489,450)  AND
                            a.status_fk = 340  AND B.STAF_PK   = ST.STAF_PK AND a.tkh_hapus  IS NULL
                            ) AS TOTAL_INDEXED_PUBLICATION,
                            (SELECT COUNT(*) AS TOTAL_INDEXED_PUBLICATION
                            FROM rmc_publication a
                            INNER JOIN RMC_PUB_AUTHOR_LIST AL ON AL.PUBLICATION_FK=A.PUBLICATION_PK AND AL.TKH_HAPUS   IS NULL
                            INNER JOIN HR_STAF B ON B.staf_Pk = AL.staf_Fk AND b.tkh_hapus IS NULL
                            WHERE a.PUBLICATION_TYPE_FK in (449,489,450) AND
                            a.status_fk =340  AND B.STAF_PK =ST.STAF_PK AND a.tkh_hapus IS NULL
                            ) AS TOTAL_PUBLICATION,SC.H_INDEX
                            FROM RMC_RESMEMBER RM INNER JOIN RMC_PRO P ON P.REFERENCE_NO=RM.REFERENCE_NO
                            INNER JOIN hr_maklumat_Peribadi mp ON mp.maklumat_peribadi_pk=RM.maklumat_peribadi_fk
                            INNER JOIN HR_STAF ST ON ST.MAKLUMAT_perIBADI_FK=MP.MAKLUMAT_perIBADI_PK
                            LEFT JOIN RMC_RARG R ON r.staf_FK =ST.staf_Pk AND R.STATUS_AKTIF='Y' AND R.TKH_HAPUS  IS NULL
                            LEFT JOIN HR_FAKULTI F ON F.KOD_FAKULTI =SUBSTR(ST.KOD_PTJ, 0, 3) AND F.TKH_HAPUS IS NULL
                            LEFT JOIN HR_FAKULTI F2 ON F2.KOD_FAKULTI =SUBSTR(ST.KOD_PTJ_ASAL, 0, 3) AND F2.TKH_HAPUS IS NULL
                            LEFT JOIN RMC_RESEARCH_ALLIANCE RA ON RA.PTJ_RA_CODE =R.PTJ_RA AND RA.TKH_HAPUS IS NULL
                            LEFT JOIN RMC_SCIVAL SC ON SC.STAF_FK = ST.STAF_PK AND SC.TKH_HAPUS  IS NULL AND SC.STATUS_AKTIF='Y'
                            LEFT JOIN RMC_RARG RA ON RA.STAF_FK = ST.STAF_PK AND RA.TKH_HAPUS  IS NULL AND RA.STATUS_AKTIF='Y'
                            LEFT JOIN RMC_RG D ON D.RG_PK = RA.RG_FK AND D.TKH_HAPUS  IS NULL AND D.STATUS_AKTIF='Y'
                            LEFT JOIN RMC_COE E ON E.PTJ_COE = RA.PTJ_COE AND E.TKH_HAPUS  IS NULL AND E.STATUS_AKTIF='Y'
                            LEFT JOIN RMC_RESEARCH_ALLIANCE F ON F.PTJ_RA_CODE = RA.PTJ_RA AND F.TKH_HAPUS IS NULL
                            WHERE ST.NO_PEKERJA       ='"+ id +"'" +
                           "AND RM.RESMEMBER_ROLE_FK IN ('77','78') AND p.pstatus_id ='123' AND P.PRO_ACTIVATED NOT  IN ('TM','C')" +
                           "AND P.TKH_HAPUS IS NULL AND MP.TKH_HAPUS IS NULL AND ST.TKH_HAPUS IS NULL " +
                           "GROUP BY ST.NO_PEKERJA,MP.NAMA, SC.H_INDEX, SC.SCOPUS_ID,f2.deskripsi_en, f.deskripsi_en,ST.STAF_PK,F.DESCRIPTION";

            dtSch = CF.getOracleDT(query);

            if (dtSch.Rows.Count > 0)
            {
                schDtl.TOTAL_GRANT = dtSch.Rows[0][5].ToString();
                schDtl.TOTAL_INDEXED_PUBLICATION = dtSch.Rows[0][6].ToString();
                schDtl.TOTAL_PUBLICATION = dtSch.Rows[0][7].ToString();
                schDtl.H_INDEX = dtSch.Rows[0][8].ToString();
                schDtl.SCOPUS_ID = dtSch.Rows[0][4].ToString();
                string scopus_id = dtSch.Rows[0][4].ToString();
            }

            return schDtl;
        }