misolutions

generate json class grants

Jul 27th, 2021
905
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.                     if(dt2.Rows[a][0].ToString() == "UNIVERSITY FUND")
  127.                     {
  128.                         if(dt2.Rows[a][1].ToString() == " ")
  129.                         {
  130.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  131.                             {
  132.                                 GRANT_NAME = "UNIVERSITY FUND",
  133.                                 BIL_GRANT = "0",
  134.                                 TAHUN_GRANT = tahun_grant
  135.                             });
  136.                         }
  137.                         else
  138.                         {
  139.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  140.                             {
  141.                                 GRANT_NAME = "UNIVERSITY FUND",
  142.                                 BIL_GRANT = bil_grant,
  143.                                 TAHUN_GRANT = tahun_grant
  144.                             });
  145.                         }                        
  146.                     }
  147.                     else if (dt2.Rows[a][0].ToString() == "NATIONAL GRANTS")
  148.                     {
  149.                         if (dt2.Rows[a][1].ToString() == " ")
  150.                         {
  151.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  152.                             {
  153.                                 GRANT_NAME = "NATIONAL GRANTS",
  154.                                 BIL_GRANT = "0",
  155.                                 TAHUN_GRANT = tahun_grant
  156.                             });
  157.                         }
  158.                         else
  159.                         {
  160.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  161.                             {
  162.                                 GRANT_NAME = "NATIONAL GRANTS",
  163.                                 BIL_GRANT = bil_grant,
  164.                                 TAHUN_GRANT = tahun_grant
  165.                             });
  166.                         }
  167.                     }
  168.                     else if (dt2.Rows[a][0].ToString() == "INDUSTRY GRANTS")
  169.                     {
  170.                         if (dt2.Rows[a][1].ToString() == " ")
  171.                         {
  172.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  173.                             {
  174.                                 GRANT_NAME = "INDUSTRY GRANTS",
  175.                                 BIL_GRANT = "0",
  176.                                 TAHUN_GRANT = tahun_grant
  177.                             });
  178.                         }
  179.                         else
  180.                         {
  181.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  182.                             {
  183.                                 GRANT_NAME = "INDUSTRY GRANTS",
  184.                                 BIL_GRANT = bil_grant,
  185.                                 TAHUN_GRANT = tahun_grant
  186.                             });
  187.                         }
  188.                     }
  189.                     else
  190.                     {
  191.                         if (dt2.Rows[a][1].ToString() == null)
  192.                         {
  193.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  194.                             {
  195.                                 GRANT_NAME = "INTERNATIONAL GRANTS",
  196.                                 BIL_GRANT = "0",
  197.                                 TAHUN_GRANT = tahun_grant
  198.                             });
  199.                         }
  200.                         else
  201.                         {
  202.                             ObjSenaraiGrantTahunan.Add(new Scholar_ResearchGroup_GrantTahunan
  203.                             {
  204.                                 GRANT_NAME = "INTERNATIONAL GRANTS",
  205.                                 BIL_GRANT = bil_grant,
  206.                                 TAHUN_GRANT = tahun_grant
  207.                             });
  208.                         }
  209.                     }
  210.                 }              
  211.             }
  212.             return ObjSenaraiGrantTahunan;
  213.  
  214.         }
RAW Paste Data