Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public List<Scholar_ResearchGroup_GrantTahunan> GetResearchGroupGetSenaraiGrantsTahunan(string id)
- {
- var dt1 = new DataTable();
- var q1 = @"SELECT EXTRACT(YEAR FROM A.PRO_DATESTART) AS TAHUN_GRANT
- FROM RMC_PRO A
- LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL
- LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL
- LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL
- WHERE
- A.PRO_DATESTART >= sysdate - interval '4' YEAR
- AND RARG.RG_FK = '"+ id +"'GROUP BY EXTRACT(YEAR FROM A.PRO_DATESTART) " +
- "ORDER BY EXTRACT(YEAR FROM A.PRO_DATESTART) DESC";
- dt1 = CF.getOracleDT(q1);
- var s = dt1.Rows.Count;
- string tahun_grant;
- var ObjSenaraiGrantTahunan = new List<Scholar_ResearchGroup_GrantTahunan>();
- for (int i=0; i < s; i++)
- {
- tahun_grant = dt1.Rows[i][0].ToString();
- var dt2 = new DataTable();
- var q2 = @"SELECT DISTINCT 'UNIVERSITY FUND', COUNT ( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS
- FROM RMC_PRO A
- LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL
- LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL
- INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL
- LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL
- LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL
- LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL
- LEFT JOIN HR_FAKULTI FAC ON FAC.KOD_FAKULTI = SUBSTR( NVL( C.KOD_PTJ_ASAL, C.KOD_PTJ ),0,3) AND FAC.TKH_HAPUS IS NULL
- LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL
- LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL
- LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL
- LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL
- LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL
- LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL
- LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL
- LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL
- WHERE A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) AND E.RESMEMBER_ROLE_FK = '77'
- AND (G.PTYPEDETAIL_CATEGORY = 1 OR G.PTYPEDETAIL_PK = 44 ) AND
- RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '"+ tahun_grant +"' " +
- "GROUP BY RARG.RG_FK " +
- "UNION " +
- "SELECT 'NATIONAL GRANTS',COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS FROM " +
- "RMC_PRO A LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
- "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_FAKULTI FAC ON FAC.KOD_FAKULTI = SUBSTR( NVL( C.KOD_PTJ_ASAL, C.KOD_PTJ ),0,3) AND FAC.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SODO_ITEM L ON L.REFERENCE_NO = A.REFERENCE_NO AND L.TKH_HAPUS IS NULL " +
- "WHERE A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) " +
- "AND E.RESMEMBER_ROLE_FK = '77' AND A.SPONSOR_FK NOT IN ( 2 ) AND A.SPONSOR_CAT_FK = '1' " +
- "AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) AND G.PTYPEDETAIL_CATEGORY = '2'" +
- "AND A.TKH_HAPUS IS NULL AND RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
- "GROUP BY RARG.RG_FK " +
- "UNION " +
- "SELECT 'INDUSTRY GRANTS',COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS FROM RMC_PRO A " +
- "LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
- "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_FAKULTI FAC ON FAC.KOD_FAKULTI = SUBSTR(NVL( C.KOD_PTJ_ASAL, C.KOD_PTJ ), 0,3 ) AND FAC.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSOR L ON L.SPONSOR_CAT = A.SPONSOR_CAT_FK AND L.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SODO_ITEM M ON M.REFERENCE_NO = A.REFERENCE_NO AND M.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
- "WHERE A.TKH_HAPUS IS NULL AND A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) AND E.RESMEMBER_ROLE_FK = '77' " +
- "AND A.SPONSOR_FK = '2' AND A.SPONSOR_CAT_FK = '1' AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) AND G.PTYPEDETAIL_CATEGORY = '2' " +
- "AND RARG.RG_FK ='" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
- "GROUP BY RARG.RG_FK " +
- "UNION " +
- "SELECT 'INTERNATIONAL GRANTS', COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS " +
- "FROM RMC_PRO A " +
- "LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
- "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
- "LEFT JOIN HR_FAKULTI FAC ON FAC.KOD_FAKULTI = SUBSTR(NVL( C.KOD_PTJ_ASAL, C.KOD_PTJ ), 0,3) AND FAC.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SPONSOR L ON L.SPONSOR_CAT = A.SPONSOR_CAT_FK AND L.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_SODO_ITEM M ON M.REFERENCE_NO = A.REFERENCE_NO AND M.TKH_HAPUS IS NULL " +
- "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
- "WHERE A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) " +
- "AND E.RESMEMBER_ROLE_FK = '77' AND A.SPONSOR_CAT_FK = '2' AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) " +
- "AND G.PTYPEDETAIL_CATEGORY = '2' AND A.TKH_HAPUS IS NULL " +
- "AND RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
- " GROUP BY RARG.RG_FK ";
- dt2 = CF.getOracleDT(q2);
- var x = dt2.Rows.Count;
- string nama_grant, bil_grant;
- for (int a=0; a < x; a++)
- {
- nama_grant = dt2.Rows[a][0].ToString();
- bil_grant = dt2.Rows[a][1].ToString();
- if(dt2.Rows[a][0].ToString() == "UNIVERSITY FUND")
- {
- if(dt2.Rows[a][1].ToString() == " ")
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "UNIVERSITY FUND",
- BIL_GRANT = "0",
- TAHUN_GRANT = tahun_grant
- });
- }
- else
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "UNIVERSITY FUND",
- BIL_GRANT = bil_grant,
- TAHUN_GRANT = tahun_grant
- });
- }
- }
- else if (dt2.Rows[a][0].ToString() == "NATIONAL GRANTS")
- {
- if (dt2.Rows[a][1].ToString() == " ")
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "NATIONAL GRANTS",
- BIL_GRANT = "0",
- TAHUN_GRANT = tahun_grant
- });
- }
- else
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "NATIONAL GRANTS",
- BIL_GRANT = bil_grant,
- TAHUN_GRANT = tahun_grant
- });
- }
- }
- else if (dt2.Rows[a][0].ToString() == "INDUSTRY GRANTS")
- {
- if (dt2.Rows[a][1].ToString() == " ")
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "INDUSTRY GRANTS",
- BIL_GRANT = "0",
- TAHUN_GRANT = tahun_grant
- });
- }
- else
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "INDUSTRY GRANTS",
- BIL_GRANT = bil_grant,
- TAHUN_GRANT = tahun_grant
- });
- }
- }
- else
- {
- if (dt2.Rows[a][1].ToString() == null)
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "INTERNATIONAL GRANTS",
- BIL_GRANT = "0",
- TAHUN_GRANT = tahun_grant
- });
- }
- else
- {
- ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
- {
- GRANT_NAME = "INTERNATIONAL GRANTS",
- BIL_GRANT = bil_grant,
- TAHUN_GRANT = tahun_grant
- });
- }
- }
- }
- }
- return ObjSenaraiGrantTahunan;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement