Clarity

Expand all | Collapse all

HTML/Web Services portlet replacement for "General" project portlet

  • 1.  HTML/Web Services portlet replacement for "General" project portlet

    Posted 05-09-2016 01:19 PM

    There have been a few previous discussions regarding a) the lack of customizing the General portlet on project dashboards (with name, id, description, etc), unable to add new fields and such, and b) creating HTML portlets that display project (or other) data. On a whim, I decided to take a crack at putting them together and creating a new, customizable, HTML portlet to replace the General system portlet. This is what I have so far, still some clean up to do but it is functional. I've made it look very much like a normal portlet (reusing stock stylesheet rules). There are a couple custom attributes that would need to be removed as well, but I wanted to get this out there to get feedback and what others think. I created a new account to access the xog queries, is there a way to restrict this account only to certain queries rather than all?

    Query to return data:

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:ORH_GEN:odf_pk:odf_pk@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:description:description@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:schedule_finish:schedule_finish@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:schedule_start:schedule_start@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:name:name@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:ppm_url:ppm_url@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:FULL_NAME:FULL_NAME@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:oh_rank:oh_rank@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:unique_code:unique_code@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:status_indicator:status_indicator@
      FROM(
    SELECT 
         odf_pk,
         ppm_url,
         description,
         schedule_finish,
         schedule_start,
         name,
         res.FULL_NAME,
         oh_rank,
         obj_request_category,
         unique_code,
         status_indicator,
         CASE status_indicator
             WHEN '1' THEN 'On Track'
             WHEN '2' THEN 'Minor Variance'
             WHEN '3' THEN 'Significant Variance'
             ELSE 'Unknown'
          END As status_indicator_text
      FROM niku.ODF_PROJECT_V2 prj
        LEFT OUTER JOIN niku.SRM_RESOURCES res ON res.USER_ID = prj.manager
        WHERE prj.odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@) data
    
     WHERE @FILTER@
    

    HTML for HTML portlet:

    Edit xmlhttp.open to use the correct XOG URL

    xmlhttp.setRequestHeader("SOAPAction to use the correct query URL

    <html>
    <head>
        <title>General project data via SOAP</title>
        <script type="text/javascript">
            function getQueryVariable(variable) {
               var query = window.location.href.substring(1);
               var vars = query.split("&");
               for (var i = 0; i < vars.length; i++) {
                   var pair = vars[i].split("=");
                   if (pair[0] == variable) {
                        return unescape(pair[1]);
                    }
               }
               alert('Query Variable ' + variable + ' not found');
            }
            function getDate(date) {
                var date = new Date(date);
                return ("0" + (date.getMonth() + 1).toString()).substr(-2) + "/" + ("0" + date.getDate().toString()).substr(-2)  + "/" + (date.getFullYear().toString()).substr(2);
            }
    
            jQuery( document ).ready(function( $ ) {
    
                var xmlhttp = new XMLHttpRequest();
                xmlhttp.open('POST', 'http://xogurl/niku/xog', true);
                
    
                xmlhttp.onreadystatechange = function () {
                    if (xmlhttp.readyState == 4) {
                        if (xmlhttp.status == 200) {
    
                            jQuery('#prj_desc').text(jQuery(xmlhttp.responseText).find('description').text());
                            jQuery('#prj_code').text(jQuery(xmlhttp.responseText).find('unique_code').text());
                            jQuery('#prj_pmgr').text(jQuery(xmlhttp.responseText).find('full_name').text());
                            jQuery('#col_rank').text('# '+jQuery(xmlhttp.responseText).find('oh_rank').text());
                            jQuery('#prj_date').text(getDate(jQuery(xmlhttp.responseText).find('schedule_start').text()) + ' - ' + getDate(jQuery(xmlhttp.responseText).find('schedule_finish').text()));
                            var dom_par = jQuery('#prj_data').parents('.ppm_portlet');
                            
                            var sharepoint = jQuery(xmlhttp.responseText).find('ppm_url').text();
    
                            var txtTitle = jQuery(xmlhttp.responseText).find('name').text();
                            if (sharepoint) {
                                sharepoint = (window.location.href.indexOf("claritytest") > -1) ? 'http://svmswiftspdev01' +  sharepoint: 'http://pmo.is.orhs.org' +  sharepoint
                                txtTitle = '<a href="' + sharepoint + '" target="_blank">' + txtTitle + '</a>';
                            }
                            txtTitle += ' <span id="rank">(Rank # ' + jQuery(xmlhttp.responseText).find('oh_rank').text() + ')</span>';
                             dom_par.find('.ppm_portlet_title_bar h2').html(txtTitle);
                                 switch(jQuery(xmlhttp.responseText).find('status_indicator').text()) {
                                    case '1':
                                        dom_par.find('.ppm_portlet_title_bar').addClass('status_gre');
                                        dom_par.find('.ppm_portlet_header_actions').addClass('status_gre');
                                        jQuery('#prj_icon').addClass('icon_gre');
                                        break;
                                    case '2':
                                        dom_par.find('.ppm_portlet_title_bar').addClass('status_yel');
                                        dom_par.find('.ppm_portlet_header_actions').addClass('status_yel');
                                        jQuery('#prj_icon').addClass('icon_yel');
                                        break;
                                    case '3':
                                        dom_par.find('.ppm_portlet_title_bar').addClass('status_red');
                                        dom_par.find('.ppm_portlet_header_actions').addClass('status_red');
                                        jQuery('#prj_icon').addClass('icon_red');
                                        break;
                                    default:
                                        
                                } 
                        }
                    }
                }
    
                var prid = getQueryVariable("id");
                var data = jQuery('#data').val().replace('<quer:param_pinvestment></quer:param_pinvestment>', '<quer:param_pinvestment>' + prid + '</quer:param_pinvestment>');
                xmlhttp.setRequestHeader("SOAPAction", "http://www.niku.com/xog/Query/new_query");
                xmlhttp.setRequestHeader("Content-Type", "text/xml");
                xmlhttp.send(data);
            });
        </script>
        <style>
    body {background-color:lightgrey;}
    h1   {color:blue;}
    p    {color:green;}
    #input {
        display: none;
    }
    #prj_name {
        text-align: center;
        font-weight: bold;
    }
    #rank {
        font-style: italic;
        font-size: 90%;
        margin-left: 15px;
    }
    .status_red {
        background-color: #ffcccc;
        background: linear-gradient( #ffcccc, #e60000 );
        background: -moz-linear-gradient(#ffcccc, #e60000);
        background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#ffcccc), to(#e60000));
        -pie-background: linear-gradient(#ffcccc, #e60000);
    }
    .status_gre {
        background-color: #80ff80;
        background: linear-gradient( #80ff80, #00cc00 );
        background: -moz-linear-gradient(#80ff80, #00cc00);
        background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#80ff80), to(#00cc00));
        -pie-background: linear-gradient(#80ff80, #00cc00);
    }
    .status_yel {
        background-color: #ffff80;
        background: linear-gradient( #ffff80, #e6e600 );
        background: -moz-linear-gradient(#ffff80, #e6e600);
        background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#ffff80), to(#e6e600));
        -pie-background: linear-gradient(#ffff80, #e6e600);
    }
    .icon_red img {
        background: url("ui/uitk/images/odf.png") no-repeat -112px 0px; width: 16px; height: 16px;
    }
    .icon_gre img {
        background: url("ui/uitk/images/odf.png") no-repeat -48px 0px; width: 16px; height: 16px;
    }
    .icon_yel img {
        background: url("ui/uitk/images/odf.png") no-repeat -144px 0px; width: 16px; height: 16px;
    }
    </style>
    </head>
    <body>
            <div id="input">
                <textarea id="data" rows='10' cols='50'>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query">
       <soapenv:Header>
          <quer:Auth>
             <quer:Username>username</quer:Username>
             <quer:Password>password</quer:Password>
          </quer:Auth>
       </soapenv:Header>
       <soapenv:Body>
          <quer:Query>
             <quer:Filter>
                <quer:param_pinvestment></quer:param_pinvestment>
             </quer:Filter>
          </quer:Query>
       </soapenv:Body>
    </soapenv:Envelope>
                </textarea>
            </div>
            <table class="ppm_grid" id="prj_data" width="100%">
                <thead>
                    <tr>
                        <th id="col_rank"></th>
                        <th class="colHeadSort">Project ID</th>
                        <th class="colHeadSort">Project Manager</th>
                        <th class="colHeadSort">Schedule Dates</th>
                    </tr>
                </thead>
                <tr>
                    <td id="prj_icon" align="center"><img src="ui/uitk/images/s.gif"></td>
                    <td id="prj_code">Loading...</td>
                    <td id="prj_pmgr">Loading...</td>
                    <td id="prj_date">Loading...</td>
                </tr>
                <tr>
                    <td id="prj_desc" colspan="4"></td>
                </tr>
            </table>
    
    </body>
    <html>
    


  • 2.  Re: HTML/Web Services portlet replacement for "General" project portlet

    Posted 05-10-2016 04:00 AM

    Nice to see the code to do this sort of thing - I feel we (as a community) are severely lacking in fully worked examples of "clever" HTML portlets (and I've never had a serious go at leveraging them so I don't have anything myself to contribute ) ; so this is a start!

     

    As for the use-case for this ; couldn't this be done with a normal (NSQL-based) portlet though?



  • 3.  Re: HTML/Web Services portlet replacement for "General" project portlet

    Posted 05-10-2016 09:39 AM

    I did, meant to mention that in my original post. I originally made a query that used UNPIVOT to get the same sort of info returned as multiple rows, but then I switched to a lot of union queries as I noticed CA had several other queries that worked the same, it also let me easily add an extra value to control the sort order. I also initially used this for the webservice version which is why there are two params but the pInvestment one wouldn't be needed if its just a normal protlet. The limitations of this was (AFAIK) could only return straight text, no way to add icon or links or anything fancy.For anyone wondering,

     

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:ORH_GEN:SortVal:SortVal@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:Cols:Cols@,
           @SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:Vals:Vals@
      FROM
      (select 'Rank' as Cols, CONVERT(sql_variant,oh_rank) as Vals, 1 as SortVal FROM niku.ODF_PROJECT_V2 WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
    union all
    select 'Project ID', unique_code, 2 as SortVal FROM niku.ODF_PROJECT_V2 WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
    union all
    select 'Description', description, 3 as SortVal FROM niku.ODF_PROJECT_V2 WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
    union all
    select 'Schedule', CONVERT(VARCHAR(10), schedule_start, 101) + ' thru ' + CONVERT(VARCHAR(10), schedule_finish, 101), 5 as SortVal FROM niku.ODF_PROJECT_V2 WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
    union all
    select 'Project Manager', r.FULL_NAME, 6 as SortVal FROM niku.ODF_PROJECT_V2
        LEFT OUTER JOIN niku.SRM_RESOURCES r ON r.USER_ID = manager
        WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
    union all
    select 'Overall Status', status_indicator =
          CASE status_indicator
             WHEN '1' THEN 'On Track'
             WHEN '2' THEN 'Minor Variance'
             WHEN '3' THEN 'Significant Variance'
             ELSE 'Unknown'
          END, 7 as SortVal FROM niku.ODF_PROJECT_V2 WHERE (odf_pk=@WHERE:PARAM:XML:INTEGER:/data/id/@value@ OR odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@)
        ) data
    
     WHERE @FILTER@