<?php


include_once('../common/base.php');
include_once('../common/func.php');
$wcids=$config['workcenter_of_device'];
if (isset($_POST['wcids'])) {
    $wcids=explode(",", $_POST['wcids']);
}

$mode=$config['data_from_scada'];
if ($mode=='true') {
    $mode="SCADA";
} else {
    $mode="NORMAL";
}

$starttime=null;

$starttime=date('Y-m-d 00:00:00', time());
$endtime=date('Y-m-d 23:59:59', time());

if (isset($_POST['starttime'])) {
    $starttime=date('Y-m-d H:i', $_POST['starttime']);
}

if (isset($_POST['endtime'])) {
    $endtime=date('Y-m-d H:i', $_POST['endtime']);
}

$data=array();
try{
    $equs=array();

    getEqus($wcids, 'workcenter', $equs);
    
    $equNos=array_keys($equs);
    $equNos2='(\''.implode('\',\'',$equNos).'\')';
    
    $sql="select  Equipment.EquNo,EquWork.PFormMNo,EquWork.ProNo,EquWork.ProName,ProcessWork.ProSpec ";
    $sql.=" from Equipment left join EquWork on Equipment.EquNo=EquWork.EquNo LEFT join  ProcessWork on EquWork.ProWorkId=ProcessWork.ProWorkId ";
    $sql.=" where Equipment.EquNo in {$equNos2}  ";
    $stmt=myQuery($conn, $sql);
    
    while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){ 
        $equNo=$row['EquNo'];
        if(empty($row['PFormMNo'])){
            $row['PFormMNo']='*';
        }
        if(empty($row['ProNo'])){
            $row['ProNo']='*';
        }
        if(empty($row['ProName'])){
            $row['ProName']='*';
        }
        if(empty($row['ProSpec'])){
            $row['ProSpec']='*';
        }
        $row['TimeGap']='*';
        $row['Eff']=0;
        $row['Qrate']=0;
        $row['TotalNum']=0;
        $row['RunRate']=0;
        $row['UnQ']=0;
        $equs[$equNo]=array_merge($equs[$equNo], $row);
    
    }
    sqlsrv_free_stmt($stmt);
    
    switch($mode){
    
        case 'SCADA':
    
                $sql="select case when AllTime<>0  then cast(Runtime as float)/cast(AllTime as float) else 0 end as RunRate,Equipment.EquNo,EquWorkScada.Status from Equipment left join EquWorkScada on Equipment.EquNo=EquWorkScada.EquNo ";
                $sql.=" where Equipment.EquNo in {$equNos2} ";
                $stmt=myQuery($conn, $sql);
                while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    
                    $equNo=$row['EquNo'];
                    $equs[$equNo]['RunRate']=empty($row['RunRate'])?0:$row['RunRate'];
                    if ($row['Status']!=null) {
                        if ($row['Status']=='0') {
                            $equs[$equNo]['status']='gray';
                        } elseif ($row['Status']=='1') {
                            $equs[$equNo]['status']='green';
                        } elseif ($row['Status']=='3' || $row['Status']=='4') {
                            $equs[$equNo]['status']='yellow';
                        } else {
                            $equs[$equNo]['status']='red';
                        }
                    } else {
        
                        $equs[$equNo]['status']='gray';
                    }
    
                }
                sqlsrv_free_stmt($stmt);

                if(!isset($_GET['onlyStatus']) || $_GET['onlyStatus']==0){
                    //效率,产量
                    $sql="select sum(AutoWork.dEquNum) as TotalNum, Equipment.EquNo,case when sum(AutoWork.dRunTime+AutoWork.dStopTime+AutoWork.dUndoTime)<>0 then sum(ProProcess.UnitHour  *  AutoWork.dEquNum) / sum(AutoWork.dRunTime+AutoWork.dStopTime+AutoWork.dUndoTime) else 0 end as Eff from Equipment left join AutoWork on Equipment.EquNo=AutoWork.EquNo ";
                    $sql.=" inner join ProcessWork on AutoWork.ProWorkId is not null and AutoWork.ProWorkId<>'' and AutoWork.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
                    $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
                    $sql.=" where AutoWork.WorkTime >='{$starttime}' and AutoWork.WorkTime < '{$endtime}'  and AutoWork.EquNo in {$equNos2} group by Equipment.EquNo";
                    $stmt=myQuery($conn, $sql); 
                    while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                        $equNo=$row['EquNo'];
                        $equs[$equNo]['Eff']=empty($row['Eff'])?0:$row['Eff'];
                        $equs[$equNo]['TotalNum']=empty($row['TotalNum'])?0:$row['TotalNum'];
                    }
                    sqlsrv_free_stmt($stmt);
        
                    foreach ($equs as $k => $v) {
            
                        $equs[$k]['RunRate']=(empty($v['RunRate'])|| $v['RunRate']==0) ?'*':round($v['RunRate']*100, 2).'%';
                        $equs[$k]['Eff']=(empty($v['Eff']) || $v['Eff']==0) ?'*':round($v['Eff']*100, 2).'%';
                        $equs[$k]['TotalNum']=empty($v['TotalNum'])?0:round($v['TotalNum'], 0);
                    } 
                }

             
            break;
    
        case 'NORMAL':       
            $sql="select Equipment.EquNo,datediff(second,Max(ReportTime),GETDATE()) as TimeGap from Equipment left join ElecProWorkNum on  ElecProWorkNum.EquId=Equipment.EquId where Equipment.EquNo in {$equNos2} group by Equipment.EquNo ";
            $stmt=myQuery($conn, $sql);
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                $equNo=$row['EquNo'];
                if ($row['TimeGap']==null) {
                    $equs[$equNo]['TimeGap']='*';
                }
            }
            sqlsrv_free_stmt($stmt);
    
            //产量
            $sql="select Equipment.EquNo,sum(ElecProWorkNum.RecordNum) as TotalNum from Equipment left join ElecProWorkNum on  ElecProWorkNum.EquId=Equipment.EquId ";
            $sql.=" where ElecProWorkNum.ReportTime>='{$starttime}' AND ElecProWorkNum.ReportTime<'{$endtime}' ";
            $sql.=" and Equipment.EquNo in {$equNos2}  group by Equipment.EquNo  ";
            $stmt=myQuery($conn, $sql);
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                $equNo=$row['EquNo'];
                $equs[$equNo]['TotalNum']=empty($row['TotalNum'])?0:floor($row['TotalNum']);
            }        
            sqlsrv_free_stmt($stmt);
    
            //效率
            $sql=" select Equipment.EquNo,case when sum(datediff(second,ElecProWorkNum.CreateTime,ElecProWorkNum.ReportTime))<>0 then sum(ProProcess.UnitHour * ElecProWorkNum.RecordNum) / sum(datediff(second,ElecProWorkNum.CreateTime,ElecProWorkNum.ReportTime)) else 0 end as Eff ";
            $sql.=" from Equipment left join ElecProWorkNum  on  ElecProWorkNum.EquId=Equipment.EquId  inner join ProcessWork on ElecProWorkNum.ProWorkId=ProcessWork.ProWorkId  ";
            $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId ";
            $sql.=" where ElecProWorkNum.ReportTime>='{$starttime}' AND ElecProWorkNum.ReportTime<'{$endtime}' ";
            $sql.=" and Equipment.EquNo in {$equNos2} group by Equipment.EquNo ";
    
            $stmt=myQuery($conn, $sql);
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                $equNo=$row['EquNo'];
                $equs[$equNo]['Eff']=empty($row['Eff'])?0:$row['Eff'];
            }    
            sqlsrv_free_stmt($stmt);
    
            //稼动率
            $sql=" select  Equipment.EquNo,case when  Equipment.Capacity<>0 then sum(datediff(second,ElecProWorkNum.CreateTime,ElecProWorkNum.ReportTime))/(Equipment.Capacity*3600) else 0 end as RunRate from Equipment inner join  ElecProWorkNum   on ElecProWorkNum.EquId=Equipment.EquId ";
            $sql.=" where ElecProWorkNum.ReportTime>='{$starttime}' AND ElecProWorkNum.ReportTime<'{$endtime}' ";
            $sql.=" and Equipment.EquNo in {$equNos2} Group By Equipment.EquNo,Equipment.Capacity ";
            $stmt=sqlsrv_query($conn, $sql);
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                $equNo=$row['EquNo'];
                $equs[$equNo]['RunRate']=empty($row['RunRate'])?0:$row['RunRate'];
            } 
    
            sqlsrv_free_stmt($stmt);
        
            $sql=" select Equipment.EquNo,sum(WorkRecord.DefectNum+WorkRecord.ScrapNum) as UnQ ";
            $sql.=" from Equipment left join WorkRecord on Equipment.EquNo=WorkRecord.EquNo inner join ProcessWork on WorkRecord.ProWorkId=ProcessWork.ProWorkId  ";
            $sql.=" where WorkRecord.ReportTime>='{$starttime}' AND WorkRecord.ReportTime<'{$endtime}'  AND WorkRecord.RecordState=2 and Equipment.EquNo in {$equNos2} group by Equipment.EquNo ";
            $stmt=myQuery($conn, $sql);
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
                $equNo=$row['EquNo'];
                $equs[$equNo]['UnQ']=empty($row['UnQ'])?0:$row['UnQ'];
                if ($equs[$equNo]['TotalNum']!=0) {
                    $equs[$equNo]['Qrate']=($equs[$equNo]['TotalNum']- $equs[$equNo]['UnQ'])/$equs[$equNo]['TotalNum'];
                } else {
                    $equs[$equNo]['Qrate']=0;
                }
            } 
            
            //获取kpi数值
            $sql_kpi_ef="SELECT [TargetValue] FROM [dbo].[KPI] WHERE [dbo].[KPI].[KpiNo]='".$config['parameters']['efficient_kpi_boundary']."'";
            $stmt=myQuery($conn, $sql_kpi_ef);
            $kpi_ef=0;
    
            if ($stmt) {
                $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
                $kpi_ef=$row[0];
    
                sqlsrv_free_stmt($stmt);
            }
    
            $sql_kpi_qr="SELECT [TargetValue] FROM [dbo].[KPI] WHERE [dbo].[KPI].[KpiNo]='".$config['parameters']['qualityrate_kpi_boundary']."'";
            $stmt=myQuery($conn, $sql_kpi_qr);
            $kpi_qr=0;
    
            if ($stmt) {
                $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
                $kpi_qr=$row[0];
    
                sqlsrv_free_stmt($stmt);
            }
        
    
            foreach ($equs as $k => $v) {
                if ($v['TimeGap']=='*' || $v['TimeGap']>=$config['halt_time']*60) {
                    $equs[$k]['status']='red';
                }
                if ($v['TimeGap']<$config['halt_time']*60 && ($v['Qrate']<$kpi_qr || $v['Eff']<$kpi_ef)) {
                    $equs[$k]['status']='yellow';
                }
                if ($v['TimeGap']<$config['halt_time']*60 && ($v['Qrate']>=$kpi_qr && $v['Eff']>=$kpi_ef)) {
                    $equs[$k]['status']='green';
                }
    
                $equs[$k]['RunRate']=(empty($v['RunRate'])|| $v['RunRate']==0) ?'*':round($v['RunRate']*100, 2).'%';
                $equs[$k]['Eff']=(empty($v['Eff']) || $v['Eff']==0) ?'*':round($v['Eff']*100, 2).'%';
                $equs[$k]['TotalNum']=empty($v['TotalNum'])?0:round($v['TotalNum'], 0);
            } 
    
    
            $data['kpi_ef']=round($kpi_ef*100, 2).'%';
            $data['kpi_qr']=round($kpi_qr*100, 2).'%';
    
    
    
            break;
    
    }
    
    
    
    
    $equs=array_values($equs);
    $data['devices']=$equs;
    $data['show_prospec']=$config['show_prospec'];
    $data['show_device_name']=$config['show_device_name'];
    
    
    echo json_encode(array('code'=>1,'data'=>$data));
    
}catch(Exception $e){
    echo json_encode(array('code'=>0,'msg'=>$e->getMessage()));
}
