<?php
$server="192.168.40.134";
$user="IC_PHP";
$pass="reports";

$memcache = memcache_connect('192.168.40.222', 11211);

if( $memcache == false ) {
    exit("Unable to connect to memcache");
}

$conn=mssql_connect($server,$user,$pass);

mssql_select_db('I3_IC_TEST',$conn);

if($conn==false){
echo "Not able to connect to database..<br>";
}

$arguement = $argv[1];
if ($arguement == "svl_mtd") {

    $sql_svl_array_mtd = mssql_query("SELECT
        CAST((SUM(CAST(NANSWEREDACDSVCLVL1 AS DECIMAL(5,1)))+
        SUM(CAST(NANSWEREDACDSVCLVL2 AS DECIMAL(5,1))))/SUM(CAST(NANSWEREDACD AS DECIMAL(5,1)))*100 AS DECIMAL(5,1)) AS SVL_30

        FROM I3_IC_TEST.DBO.IWRKGRPQUEUESTATS A

        WHERE A.CNAME  in ('CUSTOMER SERVICE', 'BUSINESS SERVICES', 'GEEKS ON WHEELS', 'SALES & BILLING') AND
        A.CREPORTGROUP = '*' AND

        CONVERT(VARCHAR, A.DINTERVALSTART, 101) >= DATEADD(MONTH,DATEDIFF(MONTH,0,CURRENT_TIMESTAMP),0)

        GROUP BY CONVERT(VARCHAR,A.DINTERVALSTART, 110)
        ORDER BY CONVERT(VARCHAR,A.DINTERVALSTART, 110)");

    if( ! mssql_num_rows( $sql_svl_array_mtd ) ) { exit(); }

    $results = make_array( $sql_svl_array_mtd );

    if( ! empty( $results ) ) {
        $key = "svl_array_mtd";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}

else if ( $arguement == "svl_array_30_minute" ) {

$sql_svl_array_30_minute = mssql_query("SELECT

                            CASE WHEN SUM(CAST(NANSWEREDACD AS DECIMAL(5,1))) = 0 THEN 100 ELSE
                            CAST((SUM(CAST(NANSWEREDACDSVCLVL1 AS DECIMAL(5,1)))+
                            SUM(CAST(NANSWEREDACDSVCLVL2 AS DECIMAL(5,1))))/SUM(CAST(NANSWEREDACD AS DECIMAL(5,1)))*100 AS DECIMAL(5,1))  END AS SVL_30

                            FROM I3_IC_TEST.DBO.IWRKGRPQUEUESTATS A

                            WHERE A.CNAME  in ('CUSTOMER SERVICE', 'BUSINESS SERVICES', 'GEEKS ON WHEELS', 'SALES & BILLING') AND
                            A.CREPORTGROUP = '*' AND

                            CONVERT(VARCHAR, A.DINTERVALSTART, 101) = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)

                            GROUP BY A.DINTERVALSTART
                            ORDER BY CONVERT(VARCHAR,A.DINTERVALSTART, 110), A.DINTERVALSTART");

    if( ! mssql_num_rows( $sql_svl_array_30_minute ) ) { exit(); }

    $results = make_array( $sql_svl_array_30_minute );

    if( ! empty( $results ) ) {
        $key = "svl_array_30_minute";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}

else if ( $arguement == "svl_array_ytd" ) {
$sql_svl_array_ytd = mssql_query("SELECT

                            CAST((SUM(CAST(NANSWEREDACDSVCLVL1 AS DECIMAL(5,1)))+
                            SUM(CAST(NANSWEREDACDSVCLVL2 AS DECIMAL(5,1))))/SUM(CAST(NANSWEREDACD AS DECIMAL(5,1)))*100 AS DECIMAL(5,1)) AS SVL_30

                            FROM I3_IC_TEST.DBO.IWRKGRPQUEUESTATS A

                            WHERE A.CNAME  in ('CUSTOMER SERVICE', 'BUSINESS SERVICES', 'GEEKS ON WHEELS', 'SALES & BILLING') AND
                            A.CREPORTGROUP = '*' AND

                            YEAR(CONVERT(VARCHAR, A.DINTERVALSTART, 101)) = YEAR(CURRENT_TIMESTAMP)

                            GROUP BY CONVERT(VARCHAR,A.DINTERVALSTART, 110)
                            ORDER BY CONVERT(VARCHAR,A.DINTERVALSTART, 110)");

    if( ! mssql_num_rows( $sql_svl_array_ytd ) ) { exit(); }

    $results = make_array( $sql_svl_array_ytd );

    if( ! empty( $results ) ) {
        $key = "svl_array_ytd";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}

else if ( $arguement == "svl_array_ytd_30_minute" ) {
$sql_svl_array_ytd_30_minute = mssql_query("SELECT

                            CASE WHEN SUM(CAST(NANSWEREDACD AS DECIMAL(5,1))) = 0 THEN 100 ELSE
                            CAST((SUM(CAST(NANSWEREDACDSVCLVL1 AS DECIMAL(5,1)))+
                            SUM(CAST(NANSWEREDACDSVCLVL2 AS DECIMAL(5,1))))/SUM(CAST(NANSWEREDACD AS DECIMAL(5,1)))*100 AS DECIMAL(5,1))  END AS SVL_30

                            FROM I3_IC_TEST.DBO.IWRKGRPQUEUESTATS A

                            WHERE A.CNAME  in ('CUSTOMER SERVICE', 'BUSINESS SERVICES', 'GEEKS ON WHEELS', 'SALES & BILLING') AND
                            A.CREPORTGROUP = '*' AND

                            YEAR(CONVERT(VARCHAR, A.DINTERVALSTART, 101)) = YEAR(CURRENT_TIMESTAMP)

                            GROUP BY A.DINTERVALSTART
                            ORDER BY CONVERT(VARCHAR,A.DINTERVALSTART, 110), A.DINTERVALSTART");

    if( ! mssql_num_rows( $sql_svl_array_ytd_30_minute ) ) { exit(); }

    $results = make_array( $sql_svl_array_ytd_30_minute );

    if( ! empty( $results ) ) {
        $key = "svl_array_ytd_30_minute";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}

else if ( $arguement == "svl_array_ytd_by_month" ) {
$sql_svl_array_ytd_by_month = mssql_query("SELECT

                            CAST((SUM(CAST(NANSWEREDACDSVCLVL1 AS DECIMAL(5,1)))+
                            SUM(CAST(NANSWEREDACDSVCLVL2 AS DECIMAL(5,1))))/SUM(CAST(NANSWEREDACD AS DECIMAL(5,1)))*100 AS DECIMAL(5,1)) AS SVL_30

                            FROM I3_IC_TEST.DBO.IWRKGRPQUEUESTATS A

                            WHERE A.CNAME  in ('CUSTOMER SERVICE', 'BUSINESS SERVICES', 'GEEKS ON WHEELS', 'SALES & BILLING') AND
                            A.CREPORTGROUP = '*' AND

                            YEAR(CONVERT(VARCHAR, A.DINTERVALSTART, 101)) = YEAR(CURRENT_TIMESTAMP)

                            GROUP BY MONTH(CONVERT(VARCHAR,A.DINTERVALSTART, 110))
                            ORDER BY MONTH(CONVERT(VARCHAR,A.DINTERVALSTART, 110))");

    if( ! mssql_num_rows( $sql_svl_array_ytd_by_month ) ) { exit(); }

    $results = make_array( $sql_svl_array_ytd_by_month );

    if( ! empty( $results ) ) {
        $key = "svl_array_ytd_by_month";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}

else if ( $arguement == "agent_occupancy_detail_mtd" ) {

$sql_agent_occupancy_detail_mtd = mssql_query("SELECT
                            LEFT(UPPER(A.USERID),10) AS Agent,
                            CAST(100 -
                            (SUM(CAST(CASE WHEN A.STATUSKEY IN ('FOLLOW UP', 'AFTER CALL WORK') THEN A.stateduration ELSE 0 END AS float))/
                            SUM(CAST(CASE WHEN A.STATUSKEY NOT IN ('AT LUNCH', 'AT BREAK', 'GONE HOME', 'ON VACATION') and A.stateduration > 0 THEN A.stateduration ELSE 1 END AS float))*100) -

                            (SUM(CAST(CASE WHEN A.STATUSKEY IN ('AWAY FROM DESK', 'DO NOT DISTURB', 'PERSONAL', 'ON OUTBOUND CALL') THEN A.stateduration ELSE 0 END AS float))/
                            SUM(CAST(CASE WHEN A.STATUSKEY NOT IN ('AT LUNCH', 'AT BREAK', 'GONE HOME', 'ON VACATION') AND A.stateduration > 0 THEN A.stateduration ELSE 1 END AS float))*100) AS DECIMAL(5,1)) AS Occupancy


                            FROM I3_IC_TEST.DBO.AGENTACTIVITYLOG A, I3_IC_TEST.DBO.USERWORKGROUPS B

                            WHERE CONVERT(VARCHAR, A.STATUSDATETIME, 101) >= DATEADD(MONTH,DATEDIFF(MONTH,0,CURRENT_TIMESTAMP),0) AND
                            LEFT(UPPER(A.USERID),10) = LEFT(UPPER(B.USERID),10) AND
                            B.WORKGROUP = 'CALLBACK GROUP'

                            GROUP BY LEFT(UPPER(A.USERID),10)

                            ORDER BY CAST(100 -
                            (SUM(CAST(CASE WHEN A.STATUSKEY IN ('FOLLOW UP', 'AFTER CALL WORK') THEN A.stateduration ELSE 0 END AS float))/
                            SUM(CAST(CASE WHEN A.STATUSKEY NOT IN ('AT LUNCH', 'AT BREAK', 'GONE HOME', 'ON VACATION') and A.stateduration > 0 THEN A.stateduration ELSE 1 END AS float))*100) -

                            (SUM(CAST(CASE WHEN A.STATUSKEY IN ('AWAY FROM DESK', 'DO NOT DISTURB', 'PERSONAL', 'ON OUTBOUND CALL') THEN A.stateduration ELSE 0 END AS float))/
                            SUM(CAST(CASE WHEN A.STATUSKEY NOT IN ('AT LUNCH', 'AT BREAK', 'GONE HOME', 'ON VACATION') AND A.stateduration > 0 THEN A.stateduration ELSE 1 END AS float))*100) AS DECIMAL(5,1)) DESC");

    if( ! mssql_num_rows( $sql_agent_occupancy_detail_mtd ) ) { exit(); }

    $results = make_table( $sql_agent_occupancy_detail_mtd );

    if( ! empty( $results ) ) {
        $key = "agent_occupancy_detail_mtd";
        if( ! update_memcache( $memcache, $key, $results ) ) {
            exit("ERROR");
        }
    }
}


/* make_array ( $data )
   returns a string of results from the query
   in a [ data ] result set
*/
function make_array( $data ) {
    $results = array();
    while( $row = mssql_fetch_array( $data ) ) {
        array_push( $results, $row[0] );
    }

    $stats = implode(", ", $results);
    return "[" . $stats . "]";
}
// END OF function make_array()

/* Update Memcache Function */
function update_memcache( $memcache, $key, $value ) {

        if( memcache_get( $memcache, $key ) ) {
            memcache_replace( $memcache, $key, $value, 0, 0);
            return 1;
        } else {
            memcache_set( $memcache, $key, $value, 0, 0);
            return 1;
        }
        return 0;
}
// END OF update_memcache()

function make_table ($table) {
            $results .= "<table id=\"datatablemtd\">
            <thead>
            <tr>
            <th></th>
            <th>Occupancy</th>
            </tr>
            </thead>
            <tbody>";

    if (!mssql_num_rows($table)){
        $results .= '<tr><td colspan="2">No Records Found</td></tr>';
    }
    else{
        while ($row = mssql_fetch_assoc($table)){
            $occupancy=$row['Occupancy'];
                $results .= '<tr>';
                $results .= '<td>'.$row['Agent'].'</td>';
                $results .= '<td>'.$row['Occupancy'].'</td>''</tr>';
            }
        $results .= "</tbody>";
        $results .= "</table>";
    return $results;
}

?>