<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = "SELECT round((com_saq / (27 - adds))*100,2) com_saq, saq_score 
              FROM (SELECT (case when nvl(l1b,'N') = 'N' then 1 else 0 end +
                            case when nvl(l4,'N') = 'N' then 1 else 0 end +
                            case when nvl(l5a,'N') = 'N' then 1 else 0 end +
                            case when nvl(l7a,'N') = 'N' then 1 else 0 end +
                            case when nvl(l9,'N') = 'N' then 1 else 0 end +
                            case when nvl(l10,'N') = 'N' then 1 else 0 end +
                            case when nvl(l11,'N') = 'N' then 1 else 0 end +
                            case when nvl(l12,'N') = 'N' then 1 else 0 end +
                            case when nvl(l13,'N') = 'N' then 1 else 0 end) adds,
                           (case when l1 is not null then 1 else 0 end +
                            case when l1a is not null then 1 else 0 end +
                            case when l1b is not null then 1 else 0 end +
                            case when l1c is not null then 1 else 0 end +
                            case when l2 is not null then 1 else 0 end +
                            case when l3 is not null then 1 else 0 end +
                            case when l4 is not null then 1 else 0 end +
                            case when l4a is not null then 1 else 0 end +
                            case when l5 is not null then 1 else 0 end +
                            case when l5a is not null then 1 else 0 end +
                            case when l5b is not null then 1 else 0 end +
                            case when l6 is not null then 1 else 0 end +
                            case when l7 is not null then 1 else 0 end +
                            case when l7a is not null then 1 else 0 end +  
                            case when l7b is not null then 1 else 0 end +
                            case when l8 is not null then 1 else 0 end +
                            case when l9 is not null then 1 else 0 end +
                            case when l9A is not null then 1 else 0 end +
                            case when l10 is not null then 1 else 0 end +
                            case when l10a is not null then 1 else 0 end +
                            case when l11 is not null then 1 else 0 end +
                            case when l11a is not null then 1 else 0 end +
                            case when l12 is not null then 1 else 0 end +
                            case when l12a is not null then 1 else 0 end +  
                            case when l13 is not null then 1 else 0 end +
                            case when l13a is not null then 1 else 0 end +
                            case when l14 is not null then 1 else 0 end) com_saq,
                           (case l1 when 'N' then 3.85 else 0 end +
                            case when l1a is not null then 0 else 0 end +
                            case l1b when 'Y' then 3.85 else 0 end +
                            case when l1c is not null then 3.85 else 0 end +
                            case l2 when 'N' then 3.85 else 0 end +
                            case l3 when 'N' then 3.85 else 0 end +
                            case l4 when 'Y' then 3.85 else 0 end +
                            case when l4a is not null then 3.85 else 0 end +  
                            case l5 when 'WORKER' then 3.85 else 0 end +
                            case l5a when 'Y' then 3.85 else 0 end +
                            case when l5b is not null then 3.85 else 0 end +
                            case l6 when 'N' then 3.85 else 0 end +
                            case l7 when 'N' then 3.85 else 0 end +
                            case l7a when 'Y' then 3.85 else 0 end +  
                            case when l7b is not null then 3.85 else 0 end +
                            case l8 when 'N' then 3.85 else 0 end +
                            case l9 when 'Y' then 3.85 else 0 end +
                            case when l9A is not null then 3.85 else 0 end +
                            case l10 when 'Y' then 3.85 else 0 end +
                            case when l10a is not null then 3.85 else 0 end +
                            case l11 when 'Y' then 3.85 else 0 end +
                            case when l11a is not null then 3.85 else 0 end +
                            case l12 when 'Y' then 3.85 else 0 end +
                            case when l12a is not null then 3.85 else 0 end +  
                            case l13 when 'Y' then 3.85 else 0 end +
                            case when l13a is not null then 3.85 else 0 end +
                            case l14 when 'Y' then 3.85 else 0 end) saq_score
                       FROM xxytysport.saq_assessment
                      WHERE USER_ID = :USER_ID
                        AND YEAR = :YEAR)";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':USER_ID', $data->USER_ID);
    oci_bind_by_name($sqlarr, ':YEAR', $data->YEAR);
    $r = oci_execute($sqlarr);
    oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    print json_encode($rows);
?>