Advertisement
misolutions

multi-layer-createjson

Jul 26th, 2021
862
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 12.04 KB | None | 0 0
  1. public List<Scholar_ResearchGroup_GrantTahunan> GetResearchGroupGetSenaraiGrantsTahunan(string id)
  2.         {
  3.             var dt1 = new DataTable();
  4.             var q1 = @"SELECT EXTRACT(YEAR FROM A.PRO_DATESTART)  AS TAHUN_GRANT
  5.                       FROM RMC_PRO A
  6.                     LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL
  7.                    LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS  IS NULL
  8.                    LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL
  9.                    WHERE
  10.                    A.PRO_DATESTART >= sysdate - interval '4' YEAR
  11.                    AND RARG.RG_FK = '"+ id +"'GROUP BY EXTRACT(YEAR FROM A.PRO_DATESTART) " +
  12.                     "ORDER BY EXTRACT(YEAR FROM A.PRO_DATESTART) DESC";
  13.  
  14.             dt1 = CF.getOracleDT(q1);
  15.             var s = dt1.Rows.Count;
  16.             string tahun_grant;
  17.             var ObjSenaraiGrantTahunan = new List<Scholar_ResearchGroup_GrantTahunan>();
  18.  
  19.             for (int i=0; i < s; i++)
  20.             {
  21.                 tahun_grant = dt1.Rows[i][0].ToString();
  22.                 var dt2 = new DataTable();                            
  23.  
  24.                 var q2 = @"SELECT DISTINCT 'UNIVERSITY FUND', COUNT ( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS
  25.                        FROM RMC_PRO A
  26.                         LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL
  27.                        LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS  IS NULL
  28.                        INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL
  29.                        LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL
  30.                        LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL
  31.                        LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL
  32.                        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
  33.                        LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL
  34.                        LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL
  35.                        LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL
  36.                         LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL
  37.                        LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL
  38.                         LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS  IS NULL
  39.                        LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL
  40.                        LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL
  41.                        WHERE  A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) AND E.RESMEMBER_ROLE_FK = '77'
  42.                        AND (G.PTYPEDETAIL_CATEGORY = 1 OR G.PTYPEDETAIL_PK = 44 ) AND
  43.                        RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '"+ tahun_grant +"' " +
  44.                         "GROUP BY RARG.RG_FK " +
  45.                         "UNION " +
  46.                         "SELECT 'NATIONAL GRANTS',COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS FROM " +
  47.                         "RMC_PRO A LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
  48.                         "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
  49.                         "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
  50.                         "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
  51.                         "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
  52.                         "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
  53.                         "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 " +
  54.                         "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL " +
  55.                         "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS IS NULL " +
  56.                         "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL " +
  57.                         "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
  58.                         "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL " +
  59.                         "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL " +
  60.                         "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
  61.                         "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
  62.                         "LEFT JOIN RMC_SODO_ITEM L ON L.REFERENCE_NO = A.REFERENCE_NO AND L.TKH_HAPUS IS NULL " +
  63.                         "WHERE A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) " +
  64.                         "AND E.RESMEMBER_ROLE_FK = '77' AND A.SPONSOR_FK NOT IN ( 2 )   AND A.SPONSOR_CAT_FK = '1' " +
  65.                         "AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) AND G.PTYPEDETAIL_CATEGORY = '2'" +
  66.                         "AND A.TKH_HAPUS IS NULL AND RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
  67.                         "GROUP BY RARG.RG_FK " +
  68.                         "UNION " +
  69.                         "SELECT 'INDUSTRY GRANTS',COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS FROM RMC_PRO A " +
  70.                         "LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
  71.                         "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
  72.                         "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
  73.                         "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
  74.                         "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
  75.                         "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
  76.                         "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 " +
  77.                         "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK AND I.TKH_HAPUS IS NULL " +
  78.                         "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK   AND J.TKH_HAPUS IS NULL " +
  79.                         "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS  IS NULL " +
  80.                         "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
  81.                         "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS  IS NULL " +
  82.                         "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK AND K2.TKH_HAPUS IS NULL " +
  83.                         "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
  84.                         "LEFT JOIN RMC_SPONSOR L ON L.SPONSOR_CAT = A.SPONSOR_CAT_FK AND L.TKH_HAPUS IS NULL " +
  85.                         "LEFT JOIN RMC_SODO_ITEM M ON M.REFERENCE_NO = A.REFERENCE_NO AND M.TKH_HAPUS IS NULL " +
  86.                         "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
  87.                         "WHERE  A.TKH_HAPUS IS NULL AND A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) AND E.RESMEMBER_ROLE_FK = '77' " +
  88.                         "AND A.SPONSOR_FK = '2' AND A.SPONSOR_CAT_FK = '1' AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) AND G.PTYPEDETAIL_CATEGORY = '2' " +
  89.                         "AND RARG.RG_FK ='" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
  90.                         "GROUP BY RARG.RG_FK " +
  91.                         "UNION " +
  92.                         "SELECT 'INTERNATIONAL GRANTS', COUNT( DISTINCT ( A.REFERENCE_NO ) ) AS TOTAL_GRANTS " +
  93.                         "FROM RMC_PRO A  " +
  94.                         "LEFT JOIN HR_MAKLUMAT_PERIBADI B ON B.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL " +
  95.                         "LEFT JOIN HR_STAF C ON C.MAKLUMAT_PERIBADI_FK = B.MAKLUMAT_PERIBADI_PK AND C.TKH_HAPUS IS NULL " +
  96.                         "INNER JOIN RMC_COST_CENTER D ON D.REFERENCE_NO = A.REFERENCE_NO AND D.TKH_HAPUS IS NULL " +
  97.                         "LEFT JOIN RMC_RESMEMBER E ON E.REFERENCE_NO = A.REFERENCE_NO AND E.TKH_HAPUS IS NULL " +
  98.                         "LEFT JOIN RMC_SPONSORDETAIL F ON F.SPONSORDETAIL_PK = A.SPONSORDETAIL_FK AND F.TKH_HAPUS IS NULL " +
  99.                         "LEFT JOIN RMC_PTYPEDETAIL G ON G.PTYPEDETAIL_PK = A.PTYPEDETAIL_FK AND G.TKH_HAPUS IS NULL " +
  100.                         "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 " +
  101.                         "LEFT JOIN RMC_SPONSOR_CAT I ON I.SPONSOR_CAT_PK = A.SPONSOR_CAT_FK  AND I.TKH_HAPUS IS NULL " +
  102.                         "LEFT JOIN RMC_FORGROUP2 J ON J.FORGROUP2_PK = A.FORGROUP2_FK AND J.TKH_HAPUS   IS NULL " +
  103.                         "LEFT JOIN RMC_FORCAT2 J2 ON J2.FORCAT2_PK = J.FORCAT2_FK AND J2.TKH_HAPUS IS NULL " +
  104.                         "LEFT JOIN RMC_NEW_FORDIV J3 ON J3.NEW_DIVISION_PK = J2.DIVISION_FK AND J3.TKH_HAPUS IS NULL " +
  105.                         "LEFT JOIN RMC_FORGROUP K ON K.FORGROUP_PK = A.FORGROUP_FK AND K.TKH_HAPUS IS NULL " +
  106.                         "LEFT JOIN RMC_FORCAT K2 ON K2.FORCAT_PK = K.FORCAT_FK  AND K2.TKH_HAPUS IS NULL " +
  107.                         "LEFT JOIN RMC_NEW_FORDIV K3 ON K3.NEW_DIVISION_PK = K2.DIVISION_FK AND K3.TKH_HAPUS IS NULL " +
  108.                         "LEFT JOIN RMC_SPONSOR L ON L.SPONSOR_CAT = A.SPONSOR_CAT_FK AND L.TKH_HAPUS IS NULL " +
  109.                         "LEFT JOIN RMC_SODO_ITEM M ON M.REFERENCE_NO = A.REFERENCE_NO AND M.TKH_HAPUS IS NULL " +
  110.                         "LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK = C.STAF_PK AND RARG.TKH_HAPUS IS NULL " +
  111.                         "WHERE   A.PSTATUS_ID = '123' AND A.PRO_ACTIVATED NOT IN ( 'TM' ) " +
  112.                         "AND E.RESMEMBER_ROLE_FK = '77' AND A.SPONSOR_CAT_FK = '2' AND G.PTYPEDETAIL_PK NOT IN ( 51, 44 ) " +
  113.                         "AND G.PTYPEDETAIL_CATEGORY = '2' AND A.TKH_HAPUS IS NULL " +
  114.                         "AND RARG.RG_FK = '" + id + "' AND EXTRACT(YEAR FROM A.PRO_DATESTART) = '" + tahun_grant + "' " +
  115.                         " GROUP BY RARG.RG_FK ";
  116.  
  117.                 dt2 = CF.getOracleDT(q2);
  118.                 var x = dt2.Rows.Count;
  119.                 string nama_grant, bil_grant;              
  120.  
  121.                 for (int a=0; a > x; a++)
  122.                 {
  123.                     nama_grant = dt2.Rows[a][0].ToString();
  124.                     bil_grant = dt2.Rows[a][1].ToString();
  125.  
  126.                     ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan {
  127.                         GRANT_NAME = nama_grant,
  128.                         BIL_GRANT = bil_grant
  129.                     });
  130.                 }
  131.                 ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan {
  132.                     TAHUN_GRANT = tahun_grant
  133.                 });
  134.             }
  135.             return ObjSenaraiGrantTahunan;
  136.  
  137.         }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement