<?php
include_once ("_dirinfo.php");
ini_set("display_errors",1);
require_once (PATH_TO_ROOT."CRM/_lib/excel/PHPExcel.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT."CRM/class/class.checkString.php");

include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."CRM/class/class.Language-Pack.php");

function formatTime($val){
    $hours = (int)($val / 3600);
    $days = '';
    if($hours > 23){
        $days = (int)($val / 86400);
        $val = $val % 86400;
        $hours = (int)($val / 3600);
    }
    $val = $val % 3600;
    $mins = (int)($val / 60);
    $sec = $val % 60;
    
    if($days)
        return $days.':'.$hours.':'.$mins.':'.$sec;
    else
        return $hours.':'.$mins.':'.$sec;
}

$LangPack = new LanguagePack($db, $aLanguage);
    
if($_GET['action'] == "export"){
    $id = $_GET['project'];
    if(!isset($_GET['project'])){
        $id = date("W");
        $sql = "SELECT id,proj_name FROM ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE proj_name LIKE '".WEEK_NAME."_".$id."%'";
        $res = $db->SQLexecute($sql);
        while($dat = $db->fetchAssoc($res)){
            $projectName[$dat['id']] = $dat['proj_name']."_structure";
        }
    }else{
        $sql = "SELECT id,proj_name FROM ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE id IN (".$id.")";
        $res = $db->SQLexecute($sql);
        while($dat = $db->fetchAssoc($res)){
            $projectName[$dat['id']] = $dat['proj_name']."_structure";
        }
    }
    $sql = "SELECT * FROM ".WARPIT_CRM."._CRMConfig WHERE prefix = 'VisitorStatistic' GROUP BY cName ASC";
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res))
        $config[$row['cName']] = array("name" => $row['cName'], "value" => $row['cValue']);
    
    
    foreach ($projectName as $idProject => $value) {
        $sql = "SELECT count( * ) AS n, b.user_id, a.nacin_pristopa, CONCAT(c.id,' - ',c.Firstname,' ',c.Surname) AS name,
                    a.id_tel,
                    (SELECT count(*) as n FROM ".WARPIT_CRM.".`_visitStatistic` WHERE id_sub_tel = a.id_tel AND id_project = ".$idProject.") as subpresent
                        
                        FROM ".DB_WARPIT_WEBCATI_BASE.".`$value` AS b
                    LEFT JOIN ".WARPIT_CRM.".`_visitStatistic` AS a ON a.id_tel = b.id_tel AND a.id_project IN (".$idProject.")
                    LEFT JOIN Warpit_Webcati_base._Interviewer AS c ON b.user_id=c.id
                WHERE b.status =1 AND b.status_rec =1
                GROUP BY b.user_id, `nacin_pristopa` ";
        //exit;
        $res = $db->SQLexecute($sql);
        while ($row = $db->fetchArray($res)) {
            $obisk[$row['user_id']]['invited'] += $row['n'];
            if (!$row['nacin_pristopa']) $obisk[$row['user_id']]['Not_present'] += $row['n'];
            if ($row['nacin_pristopa']) $obisk[$row['user_id']]['attended'] += $row['n'];
            $obisk[$row['user_id']]['agent'] = $row['name'];
            $obisk[$row['user_id']]['extra_attended'] += $row['subpresent'];
        }
        /*
        $sql = "SELECT count(*) AS n, b.user_id FROM  ".DB_WARPIT_WEBCATI_BASE.".`$value` AS b
                    LEFT JOIN ".WARPIT_CRM.".`_visitStatistic` AS a ON
                a.id_tel = b.id_tel AND a.id_project IN (".$key.") WHERE a.id_sub_tel <> 0 GROUP BY b.user_id";
        $res = $db->SQLexecute($sql);
        while($row = $db->fetchAssoc($res)){
            $obisk[$row['user_id']]['extra_attended'] += $row['n'];
        }
        */
        
        $projectNameOnly = substr($value,0,(strlen($value)-10)); 
        $callStatSql = "SELECT ".
                            "b.id_user,b.user_type, ".
                            "SUM(b.end_time-b.call_time) AS project_time, ".
                            "SUM(a.`duration`) AS call_time, ".
                            "SUM(a.`billsec`) AS talk_time, ".
                            "SUM(a.`duration`-a.`billsec`) AS callwait_time, ".
                            "SUM(b.end_time-b.call_time-a.`billsec`) AS aftercall_time, ".
                            "ROUND(SUM(a.`duration`)/SUM(b.end_time-b.call_time)*100,1) AS call_pct, ".
                            "ROUND(SUM(a.`billsec`)/SUM(b.end_time-b.call_time)*100,1) AS talk_pct, ".
                            "ROUND(SUM(a.`duration`-a.`billsec`)/SUM(b.end_time-b.call_time)*100,1) AS callwait_pct, ".
                            "ROUND(SUM(b.end_time-b.call_time-a.`billsec`)/SUM(b.end_time-b.call_time)*100,1) AS aftercall_pct ".
                        "FROM asteriskcdrdb.cdr a, ".DB_WARPIT_WEBCATI_BASE.".".$projectNameOnly."___telLog b ".
                        "WHERE a.`uniqueid`=b.int_channel_id AND b.end_time>0 ".
                        "GROUP BY b.id_user, b.user_type";
        
        
        
        $callStatRes = $db->SQLexecute($callStatSql);
        
        $userCallStat = array();
        $totalCallStat['project_time'] = 0;
        $totalCallStat['call_time'] = 0;
        $totalCallStat['talk_time'] = 0;
        $totalCallStat['callwait_time'] = 0;
        $totalCallStat['aftercall_time'] = 0;
        
        while ($userCallStatRow = $db->fetchAssoc($callStatRes)) {
            $userCallStat[$userCallStatRow['id_user'].'_'.$userCallStatRow['user_type']] = $userCallStatRow;
                $totalCallStat['project_time'] += $userCallStatRow['project_time'];
                $totalCallStat['call_time'] += $userCallStatRow['call_time'];
                $totalCallStat['talk_time'] += $userCallStatRow['talk_time'];
                $totalCallStat['callwait_time'] += $userCallStatRow['callwait_time'];
                $totalCallStat['aftercall_time'] += $userCallStatRow['aftercall_time'];
        }
        
        
        foreach($obisk as $key2 => $value2){
            if(!isset($obisk[$key2]['Not_present'])) $obisk[$key2]['Not_present'] = 0;
            if(!isset($obisk[$key2]['attended'])) $obisk[$key2]['attended'] = 0;
            if(!isset($obisk[$key2]['extra_attended'])) $obisk[$key2]['extra_attended'] = 0;  
            $obisk[$key2]['realization'] = round($obisk[$key2]['attended']/$obisk[$key2]['invited']*100,1)."%";
            $obisk[$key2]['realization_all'] = round(($obisk[$key2]['attended'] + $obisk[$key2]['extra_attended'])/$obisk[$key2]['invited']*100,1)."%";
            $color = "FF0000";
            if(intval($obisk[$key2]['realization_all']) > $config['color1']['value'])
                $color = $config['color1_value']['value'];
            if(intval($obisk[$key2]['realization_all']) > $config['color2']['value'])
                $color = $config['color2_value']['value'];
            if(intval($obisk[$key2]['realization_all']) > $config['color3']['value'])
                $color = $config['color3_value']['value'];
            $obisk[$key2]['color'] = $color;
            $inv[] = $obisk[$key2]['invited'];
            $not_present[] = $obisk[$key2]['Not_present'];
            $atten[] = $obisk[$key2]['attended'];
            $ext_atten[] = $obisk[$key2]['extra_attended'];
            $real[] = $obisk[$key2]['realization'];
            $realAll[] = $obisk[$key2]['realization_all']; 
            
            //call statistics
            $obisk[$key2]['project_time'] = formatTime($userCallStat[$key2.'_0']['project_time']);
            $obisk[$key2]['call_time'] = formatTime($userCallStat[$key2.'_0']['call_time']);
            $obisk[$key2]['talk_time'] = formatTime($userCallStat[$key2.'_0']['talk_time']);
            $obisk[$key2]['callwait_time'] = formatTime($userCallStat[$key2.'_0']['callwait_time']);
            $obisk[$key2]['aftercall_time'] = formatTime($userCallStat[$key2.'_0']['aftercall_time']);
            $obisk[$key2]['call_pct'] = $userCallStat[$key2.'_0']['call_pct'].'%';
            $obisk[$key2]['talk_pct'] = $userCallStat[$key2.'_0']['talk_pct'].'%';
            $obisk[$key2]['callwait_pct'] = $userCallStat[$key2.'_0']['callwait_pct'].'%';
            $obisk[$key2]['aftercall_pct'] = $userCallStat[$key2.'_0']['aftercall_pct'].'%';
        }
    }
    $sum = sizeof($obisk);
    $obisk['Total']['invited'] = array_sum($inv);
    $obisk['Total']['Not_present'] = array_sum($not_present);
    $obisk['Total']['realization'] = round($obisk['Total']['attended']/$obisk['Total']['invited']*100,1)."%";
    $obisk['Total']['realization_all'] = round(($obisk['Total']['attended'] + $obisk['Total']['extra_attended'])/$obisk['Total']['invited']*100,1)."%";
    $obisk['Total']['attended'] = array_sum($atten);
    $obisk['Total']['extra_attended'] = array_sum($ext_atten);
    $obisk['Total']['agent'] = "Total";
    
    $obisk['Total']['project_time'] = formatTime($totalCallStat['project_time']);
    $obisk['Total']['call_time'] = formatTime($totalCallStat['call_time']);
    $obisk['Total']['talk_time'] = formatTime($totalCallStat['talk_time']);
    $obisk['Total']['callwait_time'] = formatTime($totalCallStat['callwait_time']);
    $obisk['Total']['aftercall_time'] = formatTime($totalCallStat['aftercall_time']);
    $obisk['Total']['call_pct'] = round(($totalCallStat['call_time'] / $totalCallStat['project_time'])*100,1)."%";
    $obisk['Total']['talk_pct'] = round(($totalCallStat['talk_time'] / $totalCallStat['project_time'])*100,1)."%";
    $obisk['Total']['callwait_pct'] = round(($totalCallStat['callwait_time'] / $totalCallStat['project_time'])*100,1)."%";
    $obisk['Total']['aftercall_pct'] = round(($totalCallStat['aftercall_time'] / $totalCallStat['project_time'])*100,1)."%";
    
    $data = array_values($obisk);
    $abc = range("A", "Z");
    
    $config['agent'] = array("width" => "25", "title" => $LangPack->GetLabel("visit_statistic_export", "agent_title","Agent"));
    $config['invited'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "invited_title","Povabljenih"));
    $config['attended'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "attended_title","Prišlo"));
    $config['Not_present'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "not_present_title","Ni prišlo"));
    $config['extra_attended'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "extra_attended_title","Dodatno prišli"));
    $config['realization'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "realization_title","Realizacija"));
    $config['realization_all'] = array("width" => "15", "title" => $LangPack->GetLabel("visit_statistic_export", "realization_all_title","Realizacija skupaj"));
    
    $config['project_time'] = array("width" => "15", "title" => 'Project time');
    $config['call_time'] = array("width" => "15", "title" => 'Call time');
    $config['talk_time'] = array("width" => "15", "title" => 'Talk time');
    $config['callwait_time'] = array("width" => "15", "title" => 'Call wait time');
    $config['aftercall_time'] = array("width" => "15", "title" => 'After call time');
    $config['call_pct'] = array("width" => "15", "title" => 'Call %');
    $config['talk_pct'] = array("width" => "15", "title" => 'Talk %');
    $config['callwait_pct'] = array("width" => "15", "title" => 'Call wait %');
    $config['aftercall_pct'] = array("width" => "15", "title" => 'After call %');
    
    $fields = array('agent', 
                    'invited', 
                    'attended', 
                    'extra_attended',
                    'Not_present', 
                    'realization',
                    'realization_all',
                    'project_time',
                    'call_time',
                    'talk_time',
                    'callwait_time',
                    'aftercall_time',
                    'call_pct',
                    'talk_pct',
                    'callwait_pct',
                    'aftercall_pct');
    $orderBy = array();
    foreach ($fields as $key => $value) {
        $orderBy[$value] = $key; 
    }
                    
    $orderedStatus = array();
    foreach ($fields as $key => $value) {
        $orderedStatus[$orderBy[$value]] = $value;
    }
    ksort($orderedStatus);
    $orderedStatus = array_values($orderedStatus);
    
    
    $objPHPExcel = new PHPExcel();
    foreach ($orderedStatus as $key => $value) {
        if($key > 25){
            $letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
        }
        else
            $letter = $abc[$key];
        
        $objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($config[$value]['width']);
        $objPHPExcel->getActiveSheet()->setCellValue($letter."1", $config[$value]['title']);    
        $objPHPExcel->getActiveSheet()->getStyle($letter."1")->getFont()->setBold(true);
    }
    $i=2;
    
    foreach( $data as $key1 => $value1 )
    {
        foreach ($orderedStatus as $key => $value) {
            if($key > 25){
                $letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
            }
            else
                $letter = $abc[$key];
            
            $objPHPExcel->getActiveSheet()->setCellValue($letter.$i, $value1[$value]);
            if(isset($value1['color']) && $value == "realization_all")
                cellColor($letter.$i, $value1['color']);
            
        }
        $i++;
    }
    $fileName = $LangPack->GetLabel("visit_statistic_export", "file_name","visit_statistic_export");
    $fileName .= ".xls";
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="' . $fileName . '"');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}
function cellColor($cells,$color){
    global $objPHPExcel;
    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
        ->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array('rgb' => $color)
    ));
}
?>
