/*MDP0722 10/23/2024
Take project insight response and update ServiceNow
*/

try {
ClearUserLog();
    var r = new sn_ws.RESTMessageV2('Project Insight Get Updates', 'Default Get');
    //response.setStringParametersNoEscape('hoursBack', '24');

    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    var responseObj = JSON.parse(responseBody);

    for (var i = 0, len = responseObj.length; i < len; ++i) {
        var projectName = responseObj[i].Name;
        var description = responseObj[i].description;
        var sysId = responseObj[i].sys_id;
        var number = responseObj[i].number;
        var prioritizationGroup = responseObj[i].u_prioritization_group;
        var priority = responseObj[i].priority;
        var expense = responseObj[i].u_expense;
        var status = responseObj[i].u_status;
        var shortDescription = responseObj[i].short_description;
        var estimatedHours = responseObj[i].u_estimated_hours;
        var sponsorMgrUp = responseObj[i].sponsor_manager_and_up_only_for_this_field;
        var contactName = responseObj[i]["variables.contact_name"];
        var costCenter = responseObj[i]["variables.cost_center"];
        var createdOn = responseObj[i].request_created_on;
        var contactPhone = responseObj[i]["variables.contact_s_phone_number"];
        var dueDate = responseObj[i].due_date;
        var piId = responseObj[i].u_project_insight_records_sysid;
        var piNum = responseObj[i].u_project_insight_records_number;
        var projectType = responseObj[i].u_project_type;
        var complete = responseObj[i].Complete;
        var name = sponsorMgrUp.split('@')[0];
        var lname = name.split('.')[1];

         //Query the u_project_insight table by sys_id and update fields for the matching sys_id
        var gr = new GlideRecord("u_project_insight_records");
        gr.addQuery("sys_id", piId);
        gr.setLimit(100);
        gr.query();

        if (gr.next()) {
            if (status.length > 0) {
                gr.u_status = status;
            }

            gr.u_estimated_hours = estimatedHours;
            gr.u_complete = complete;
            gr.update();
        }

        var gr1 = new GlideRecord("sc_req_item");
        gr1.addQuery("number", number);
        gr1.setLimit(100);
        gr1.query();

        while (gr1.next()) {
            if (dueDate.length > 0) {
                gr1.due_date = dueDate;
            }
            gr1.u_complete = complete;
            gr1.u_status = status;
            gr1.u_hours = estimatedHours;

            var gr2 = new GlideRecord("sys_user");
            gr2.addEncodedQuery("email=" + sponsorMgrUp + "^last_name=" + lname);
            gs.info("email=" + sponsorMgrUp + "^last_name = " + lname);
            gr2.setLimit(100);
            gr2.query();

            if (gr2.next()) {
                gs.info("Sponsor Query =  " + gr2.last_name + " Sys ID = " + gr2.sys_id)
                var sponsorSysId = gr2.sys_id;
                gr1.variables.sponsor_manager_and_up_only_for_this_field = sponsorSysId;
            }
            gr1.update();

            UserLog("SNOW PULL from Project Insight Completed");
        }
        //    
    }

} catch (ex) {
    var message = ex.message;
}

// LINK
// https://<instance>.service-now.com/now/nav/ui/classic/params/target/sysauto_script.do%3Fsys_id%3D083258c887655610fdd876a6cebb3540%26sysparm_view%3D%26sysparm_domain%3Dnull%26sysparm_domain_scope%3Dnull%26sysparm_record_row%3D2%26sysparm_record_rows%3D668%26sysparm_record_list%3DORDERBYDESCsys_updated_on