<?php
include_once('../common/base.php');
include_once('../common/func.php');
try{
    $wcids=$config['workcenters'];
    $starttime=null;
    //    $sql='select  top 1 * from TimeSchedule order by CONVERT(VARCHAR(255),AttStartTime,108)  asc';
    //    $stmt=sqlsrv_query($conn, $sql);
    //    if ($stmt) {
    //        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    //        $starttime=$row['AttStartTime']->format('H:i');
    //        sqlsrv_free_stmt($stmt);
    //    }
    //    $starttime=date('Y-m-d '.$starttime, time());
    $starttime=date('Y-m-d 00:00', time());
    $endtime=date('Y-m-d 23:59', time());
    
    if (isset($_GET['starttime'])) {
        $starttime=date('Y-m-d H:i', $_GET['starttime']);
    }
    
    
    if (isset($_GET['endtime'])) {
        $endtime=date('Y-m-d H:i', $_GET['endtime']);
    }
    
    // $starttime='2021-03-16 00:00:00';
    // $endtime='2021-03-16 23:59:59';
    
    $mode=$config['data_from_scada'];
    if ($mode=='true') {
        $mode="SCADA";
    } else {
        $mode="NORMAL";
    }
    
    //良率
    $sql="select case when sum(GenuineNum + DefectNum + ScrapNum)<>0 then sum(GenuineNum)/sum(GenuineNum + DefectNum + ScrapNum) else 1 end as QualityRate from WorkRecord ";
    $sql.=" where  [ReportTime]>='{$starttime}'  AND  [ReportTime]<='{$endtime}' AND [RecordState]=2 ";
    $sql.=" and CenterId IN  (".implode(',', $wcids).")";
    $stmt=myQuery($conn, $sql);
    $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    $data['QualityRate']=empty($row['QualityRate'])?100:round($row['QualityRate']*100, 2);
    sqlsrv_free_stmt($stmt);
    
    //完工率
    $sql=" select case when  sum(ProcessWork.InputNum)<>0 then  sum(ProcessWork.OutputNum) / sum(ProcessWork.InputNum) else 0 end as CompleteRate ";
    $sql.=" from ProcessWork where ProWorkId  IN ( select distinct ProcessWork.ProWorkId from WorkRecord inner join ProcessWork on  WorkRecord.ProWorkId= ProcessWork.ProWorkId where WorkRecord.ReportTime >='{$starttime}' and WorkRecord.ReportTime < '{$endtime}'   AND [RecordState]=2 AND WorkRecord.CenterId IN (".implode(',', $wcids)."))";
    $stmt=myQuery($conn, $sql);
    $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    $data['CompleteRate']=empty($row['CompleteRate'])?0:round($row['CompleteRate']*100, 2);
    sqlsrv_free_stmt($stmt);
    
    //站下总设备数
    
    $sql="select count(*) from Equipment inner join WorkCenter on WorkCenter.CenterId=Equipment.CenterId ";
    $sql.=" where Equipment.CenterId IN (".implode(',', $wcids).")";
    $stmt=myQuery($conn, $sql);
    $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
    $totalEquNum=empty($row[0])?0:$row[0];
    sqlsrv_free_stmt($stmt);
    
    
    $data['wc8h']=array();
    $data['wc8h']['plan']=array(
    );
    $data['wc8h']['real']=array(
    );
    $data['wc8h']['eff']=array(
    );
    $data['wc8h']['run']=array(
    );
    
    //标准产量
    $sql_kpi_std_output="SELECT [TargetValue] FROM [dbo].[KPI] WHERE [dbo].[KPI].[KpiNo]='".$config['parameters']['kpi_std_output']."'";
    $stmt=myQuery($conn, $sql_kpi_std_output);
    $kpi_std_output=0;
    
    if ($stmt) {
        $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
        $kpi_std_output=empty($row[0])?0:$row[0];
    
        sqlsrv_free_stmt($stmt);
    }
    
    
    //计算当前所在小时
    $curTime=strtotime(date('Y-m-d H:00:00', time()));
    $time1=strtotime(date('Y-m-d H:00:00', time()));
    $time2=time();
    
    if ($time2-$time1>60*30) {
        $curTime=strtotime(date('Y-m-d H:00:00', time()+3600));
    }
    //$curTime=strtotime('2021-03-16 12:00:00');
    $interval=$config['interval_left_gap'];
    //计算区间
    for ($i=7;$i>=0;$i--) {
        $hourly_eff[]=array(
            'hour_s'=>date('Y-m-d H:i:00', strtotime('-'.(($i+1)*$interval).' minutes', $curTime)),
            'hour_e'=>date('Y-m-d H:i:00', strtotime('-'.($i*$interval).' minutes', $curTime)),
            'eff'=>0
        );
    }
    
    switch ($mode) {
        case 'SCADA':
            //效率
            $sql="select 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 AutoWork ";
            $sql.=" inner join ProcessWork on AutoWork.ProWorkId=ProcessWork.ProWorkId and AutoWork.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}'  ";

            // $sql="  select Max(View_AutoWorkWithDelta2.RunTime)-Min(View_AutoWorkWithDelta2.RunTime)";
            // $sql.=" +Max(View_AutoWorkWithDelta2.StopTime)-Min(View_AutoWorkWithDelta2.StopTime) ";
            // $sql.=" +Max(View_AutoWorkWithDelta2.UndoTime)-Min(View_AutoWorkWithDelta2.UndoTime) as AllTime  from View_AutoWorkWithDelta2  ";
            // $sql.=" inner join ProcessWork on View_AutoWorkWithDelta2.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            // $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
            // $sql.=" where View_AutoWorkWithDelta2.WorkTime >='{$starttime}' and View_AutoWorkWithDelta2.WorkTime < '{$endtime}' group by EquNo" ;           
            
            // $sql=" select case when sum(DRunTime+DStopTime+DUndoTime)<>0 then sum (UnitHour * DEquNum) / sum(DRunTime+DStopTime+DUndoTime ) else 0 end as Eff ";
            // $sql.=" from (select a.EquNum-b.EquNum as DEquNum,a.RunTime-b.RunTime as DRunTime,a.StopTime-b.StopTime as DStopTime,a.UndoTime-b.UndoTime as DUndoTime,ProProcess.UnitHour from View_AutoWorkWithDelta2 a ";
            // $sql.=" inner join View_AutoWorkWithDelta2 b on b._id=(select max(c._id) from View_AutoWorkWithDelta2 c where c.EquNo=a.EquNo and c._id<a._id ) ";
            // $sql.=" inner join ProcessWork on a.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            // $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
            // $sql.=" where a.WorkTime >='{$starttime}' and a.WorkTime < '{$endtime}'  ) t";            

            $stmt=myQuery($conn, $sql);
            $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
            $data['Eff']=round($row['Eff']*100, 2);
            sqlsrv_free_stmt($stmt);
    
            //开机率
            $sql=" select sum(open1) from (select distinct AutoWork.EquNo,case when AutoWork.dEquNum>=1 then 1 else 0 end  as open1 from AutoWork  ";
            $sql.=" inner join ProcessWork on AutoWork.ProWorkId=ProcessWork.ProWorkId and AutoWork.ProWorkId<>'' and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            $sql.=" where AutoWork.WorkTime>='{$starttime}' AND AutoWork.WorkTime<'{$endtime}') as t ";


            // $sql=" select sum(open1) from ( select distinct EquNo,case when DEquNum>=1 then  1 else 0 end as open1 ";
            // $sql.=" from ( select a.EquNo, a.EquNum-b.EquNum as DEquNum,a.RunTime-b.RunTime as DRunTime, ";
            // $sql.=" a.StopTime-b.StopTime as DStopTime,a.UndoTime-b.UndoTime as DUndoTime,ProProcess.UnitHour from View_AutoWorkWithDelta2 a";
            // $sql.=" inner join View_AutoWorkWithDelta2 b on b._id=(select max(c._id) from View_AutoWorkWithDelta2 c where  c._id < a._id  and c.EquNo=a.EquNo) ";
            // $sql.=" inner join ProcessWork on a.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            // $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId "; 
            // $sql.=" where a.WorkTime >='{$starttime}' and a.WorkTime < '{$endtime}'  ) t ) d";       


            $stmt=myQuery($conn, $sql);
            $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
            $runEquNum=empty($row[0])?0:$row[0];
            sqlsrv_free_stmt($stmt);
 
    
            if ($totalEquNum!=0) {
                $data['OpenRate']=round($runEquNum/$totalEquNum*100, 2);
            } else {
                $data['OpenRate']=0;
            }
    
            //计算区间内的实际产量、运转率等
            $conditions=array();
            foreach ($hourly_eff as $k=>$v) {
    
                //实际产量
                $sql="select sum(WorkRecord.GenuineNum) as realnum from WorkRecord ";
                $sql.=" where [WorkRecord].[RecordState]=2 and [WorkRecord].[ReportTime]>='{$v['hour_s']}' and [WorkRecord].[ReportTime]<'{$v['hour_e']}' and WorkRecord.CenterId IN (".implode(',', $wcids).") ";
                $stmt=myQuery($conn, $sql);
                if ($stmt) {
                    $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
                    if ($row['realnum']==null) {
                        $row['realnum']=0;
                    }
                    $data['wc8h']['real'][]=$row['realnum'];
                } else {
                    $data['wc8h']['real'][]=0;
                }
                
                // $sql=" select case when  {$totalEquNum}<>0 then  sum(DRunTime)/({$interval}*60*{$totalEquNum}) else 0 end as run, ";
                // $sql.=" case when sum(DRunTime+DStopTime+DUndoTime)<>0 then sum (UnitHour  * DEquNum ) / sum(DRunTime+ DStopTime+DUndoTime) else 0 end as Eff ";
                // $sql.=" from (select a.EquNum-b.EquNum as DEquNum,a.RunTime-b.RunTime as DRunTime,a.StopTime-b.StopTime as DStopTime,a.UndoTime-b.UndoTime as DUndoTime,ProProcess.UnitHour from AutoWork2 a ";
                // $sql.=" inner join View_AutoWorkWithDelta2 b on b._id=(select max(c._id) from View_AutoWorkWithDelta2 c where c.EquNo=a.EquNo and c._id<a._id ) ";
                // $sql.=" inner join ProcessWork on a.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
                // $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
                // $sql.=" where a.WorkTime >='{$v['hour_s']}' and a.WorkTime < '{$v['hour_e']}'  ) t";     

                $sql="select  case when {$totalEquNum}<>0 then cast(sum(AutoWork.dRunTime) as float)/".($interval*60*$totalEquNum)."  else 0 end as run, 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 AutoWork ";
                $sql.=" inner join ProcessWork on AutoWork.ProWorkId=ProcessWork.ProWorkId and AutoWork.ProWorkId<>'' and ProcessWork.CenterId IN (".implode(',', $wcids).")";
                $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
                $sql.=" where AutoWork.WorkTime >='{$v['hour_s']}' and AutoWork.WorkTime < '{$v['hour_e']}'  ";
    
                $stmt=myQuery($conn, $sql);
                $eff=array();
                $timeline=array();
                if ($stmt) {
                    $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
                    if ($row['run']==null) {
                        $row['run']=0;
                    }
    
                    $row['run']=round($row["run"], 4);
    
                    $hourly_eff[$k]['eff']=$row['Eff'];
                }
    
                $data['wc8h']['eff'][]=$hourly_eff[$k]['eff'];//效率
                $data['wc8h']['timeline'][]=date('H时i分', strtotime($hourly_eff[$k]['hour_s']));
                $data['wc8h']['plan'][]=$kpi_std_output;
                $data['wc8h']['run'][]=$row['run'];//稼动率
    
                if ($stmt) {
                    sqlsrv_free_stmt($stmt);
                }
            }
            break;
    
        case 'NORMAL':
    
            //效率
            $sql="select 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 from ElecProWorkNum ";
            $sql.=" inner join ProcessWork on ElecProWorkNum.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            $sql.=" inner join ProProcess on ProProcess.ProProcessId=ProcessWork.ProProcessId";
            $sql.=" where ElecProWorkNum.ReportTime >='{$starttime}' and ElecProWorkNum.ReportTime < '{$endtime}'  ";
            $stmt=myQuery($conn, $sql);
            $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
            $data['Eff']=round($row['Eff']*100, 2);
            sqlsrv_free_stmt($stmt);
    
            //开机率
            $sql=" select count(*) from (select distinct ElecProWorkNum.EquNo from ElecProWorkNum  ";
            $sql.=" inner join ProcessWork on ElecProWorkNum.ProWorkId=ProcessWork.ProWorkId and ProcessWork.CenterId IN (".implode(',', $wcids).")";
            $sql.=" where  RecordNum<>0 and ElecProWorkNum.ReportTime>='{$starttime}' AND ElecProWorkNum.ReportTime<'{$endtime}') as t ";
            $stmt=myQuery($conn, $sql);
            $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
            $runEquNum=empty($row[0])?0:$row[0];
            sqlsrv_free_stmt($stmt);
    
            if ($totalEquNum!=0) {
                $data['OpenRate']=round($runEquNum/$totalEquNum*100, 2);
            } else {
                $data['OpenRate']=0;
            }
    
    
    
    
            //计算区间内的实际产量、运作率等
            $conditions=array();
            foreach ($hourly_eff as $k=>$v) {
                $sql="select  cast(sum(datediff(second,ElecProWorkNum.CreateTime,ElecProWorkNum.ReportTime)) as float)/".($interval*60)." as run,sum([ElecProWorkNum].[RecordNum]) as realnum,sum(ProcessWork.InputNum) as plannum, sum([ElecProWorkNum].[RecordNum]*[ProProcess].[UnitHour]) as realworktime,sum(datediff(second,[ElecProWorkNum].[CreateTime],[ElecProWorkNum].[ReportTime])) as planworktime from [ElecProWorkNum] INNER JOIN [ProcessWork] ON  [ElecProWorkNum].[ProWorkId]=[ProcessWork].[ProWorkId] ";
                $sql.=" and  ProcessWork.CenterId IN (".implode(',', $wcids).")";
                $sql.=" INNER JOIN [ProProcess] ON [ProcessWork].[ProProcessId]=[ProProcess].[ProProcessId] where [ElecProWorkNum].[ReportTime]>='{$v['hour_s']}' and [ElecProWorkNum].[ReportTime]<'{$v['hour_e']}' ";
    
                $stmt=myQuery($conn, $sql);
                $eff=array();
                $timeline=array();
                if ($stmt) {
                    $row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
                    if ($row['run']==null) {
                        $row['run']=0;
                    }
    
                    $row['run']=round($row["run"], 4);
    
                    if ($row['realnum']==null) {
                        $row['realnum']=0;
                    }
            
                    if ($row['plannum']==null) {
                        $row['plannum']=0;
                    }
    
                    if ($row['realworktime']==null) {
                        $row['realworktime']=0;
                    }
                    
                    if ($row['planworktime']==null) {
                        $row['planworktime']=0;
                    }
    
                    if ($row['planworktime']!=0) {
                        $hourly_eff[$k]['eff']=round($row["realworktime"]/$row["planworktime"], 4);
                    } else {
                        $hourly_eff[$k]['eff']=0;
                    }
                }
    
                $data['wc8h']['eff'][]=$hourly_eff[$k]['eff'];
                $data['wc8h']['timeline'][]=date('H时i分', strtotime($hourly_eff[$k]['hour_s']));
                $data['wc8h']['plan'][]=$kpi_std_output;
                $data['wc8h']['real'][]=$row['realnum'];
                $data['wc8h']['run'][]=$row['run'];
    
                if ($stmt) {
                    sqlsrv_free_stmt($stmt);
                }
            }
            break;
    }
    
    
    
    echo json_encode($data);
    
}catch(Exception $e){
    $data=array('code'=>0,'msg'=>$e->getMessage());
    echo json_encode($data);
    die();
}
