IDCOL/sendCmd/idcol_sumData.php

<?php
// 计算灯控历史数据脚本
require_once './DB.php';
date_default_timezone_set('Asia/Shanghai');

$config = [
    'hostname' => 'rm-wz98r5cn33zq4ou980o.mysql.rds.aliyuncs.com',
	'username' => 'idcol20',
	'password' => 'idcol@1234',
	'dbname' => 'idcol',
];
while (1) {
	$db = new Db($config);
	$t = date('H:i',time());
	// if ($t == '02:05') {
	if (1) {
		$lampList = $db->query('select id,updatetime from lampinfo order by id asc');
		$minTime = $db->query('select max(updatetime) as minTime from new_statistics where updatetime is not null');
		if (empty($minTime) || empty($minTime[0]) || empty($minTime[0]['minTime'])) {
			$sql = 'select min(updatetime) as minTime from realtime_info_log where updatetime is not null AND updatetime != "1970-01-01 08:00:00"';
			$minTime = $db->query($sql);
			// var_dump($minTime);die;
		}
		// var_dump($minTime);die;
		$sum = floor((time() - strtotime($minTime[0]['minTime'])) / 86400);
		// var_dump($sum);die;
		for ($i=0; $i < $sum; $i++) { 
			$fromDate = date('Y-m-d 00:00:00',strtotime($minTime[0]['minTime']) + ($i+1) * 24 * 3600);
			$toDate = date('Y-m-d 23:59:59',strtotime($minTime[0]['minTime']) + ($i+1) * 24 * 3600);
			// $fromDate = date('Y-m-d 00:00:00',time() - 24 * 3600);
			// $toDate = date('Y-m-d 23:59:59',time() - 24 * 3600);
			// var_dump($fromDate);var_dump($toDate);

			$sql = 'select lampid from statistics WHERE updatetime >= "'.$fromDate.'" AND updatetime <= "'.$toDate.'"';
			$data = $db->query($sql);
			// $lampidData = [];
			// foreach ($data as $v) {
			// 	$lampidData[] = $v['lampid'];
			// }
			foreach ($lampList as $value) {
				if (empty($value['updatetime'])) continue;
				// if (in_array($value['id'], $lampidData)) continue;

				// if (empty($data[0]['id'])) {
					$sql = 'select max(updatetime) as maxTime from realtime_info_log WHERE updatetime >= "'.$fromDate.'" AND updatetime <= "'.$toDate.'" AND lampid = '.$value['id'];
					$updatetime = $db->query($sql);
					// var_dump($updatetime);
					if (empty($updatetime[0]['maxTime'])) {
						usleep(10000);
						continue;
					}
					$res = get_data($value['id'],$updatetime[0]['maxTime'],$db);
					// var_dump($res);
					$res['lampid'] = $value['id'];
					$res['updatetime'] = $updatetime[0]['maxTime'];
					$db->table('statistics')->insert($res);

					$lampData = $db->query('select id from new_statistics WHERE lampid = '.$value['id'].' AND date_format(updatetime,"%Y-%m-%d") = "'.date('Y-m-d',strtotime($res['updatetime'])).'"');
					if (!empty($lampData) && !empty($lampData[0]['id'])) {
						$db->table('new_statistics')->where('id='.$lampData[0]['id'])->update($res);
					}else{
						$db->table('new_statistics')->insert($res);
					}
					usleep(100000);
				// }
				
			}
		}	
	}
	$db->close();
	sleep(3600);
} 
function get_data($id,$date,$db){
	
	$table = 'realtime_info_log';

	$beginDateDay = date('Y-m-d 00:00:00',strtotime($date));
	$endDateDay   = date('Y-m-d 23:59:59',strtotime($date));

	$beginDateMonth = date('Y-m-01 00:00:00', strtotime($date));
	$endDateMonth   = date('Y-m-d 23:59:59', strtotime($date));

	$beginDateYear = date('Y-01-01 00:00:00', strtotime($date));
	$endDateYear   = date('Y-m-d 23:59:59', strtotime($date));
	
	
	// 当天发电量，用电量
	$query = "SELECT lampid,chargeday as daygeneration,dischargeday as dayconsumption FROM {$table}
			WHERE lampid =".$id." 
			AND updatetime>='".$beginDateDay."' 
			AND updatetime<='".$endDateDay."' order by updatetime desc limit 1";	
	$query = $db->query($query);
	
	$data['dayGeneration'] = empty($query[0]['daygeneration']) ? 0 : round($query[0]['daygeneration'],2);
	$data['dayConsumption'] = empty($query[0]['dayconsumption']) ? 0 : round($query[0]['dayconsumption'],2);
	

	// 总发电量，总用电量
	$query = "SELECT totalchargeah as totalgeneration,totaldischarah as totalconsumption FROM history_info_log
			WHERE lampid =".$id." 
			AND updatetime>='".$beginDateDay."' 
			AND updatetime<='".$endDateDay."' order by updatetime desc limit 1";	
	$query = $db->query($query);
	$data['totalGeneration'] = empty($query[0]['totalgeneration']) ? 0 : round($query[0]['totalgeneration'],2);
	$data['totalConsumption'] = empty($query[0]['totalconsumption']) ? 0 : round($query[0]['totalconsumption'],2);
	
	//月发电量和用电量
	$query = "SELECT totalchargeah AS lastmonthgeneration,totaldischarah AS lastmonthconsumption FROM history_info_log 
			WHERE lampid =".$id." AND updatetime<'".$beginDateMonth."' order by updatetime desc limit 1";	
	
	$query = $db->query($query);

	$query[0]['lastmonthgeneration'] = empty($query[0]['lastmonthgeneration']) ? 0 : round($query[0]['lastmonthgeneration'],2);
	$query[0]['lastmonthconsumption'] = empty($query[0]['lastmonthconsumption']) ? 0 : round($query[0]['lastmonthconsumption'],2);

	$data['monthConsumption'] = $query[0]['lastmonthconsumption'] >= $data['totalConsumption'] ? 0 : $data['totalConsumption'] - $query[0]['lastmonthconsumption'];
	$data['monthGeneration'] = $query[0]['lastmonthgeneration'] >= $data['totalGeneration'] ? 0 : $data['totalGeneration'] - $query[0]['lastmonthgeneration'];
	
	//年发电量和用电量
	$query = "SELECT totalchargeah AS lastyeargeneration,totaldischarah AS lastyearconsumption FROM history_info_log 
			WHERE lampid =".$id." AND updatetime<'".$beginDateYear."'";		
	$query = $db->query($query);
	$query[0]['lastyeargeneration'] = empty($query[0]['lastyeargeneration']) ? 0 : round($query[0]['lastyeargeneration'],2);
	$query[0]['lastyearconsumption'] = empty($query[0]['lastyearconsumption']) ? 0 : round($query[0]['lastyearconsumption'],2);
	$data['yearConsumption'] = $query[0]['lastyearconsumption'] >= $data['totalConsumption'] ? 0 : $data['totalConsumption'] - $query[0]['lastyearconsumption'];
	$data['yearGeneration'] = $query[0]['lastyeargeneration'] >= $data['totalGeneration'] ? 0 : $data['totalGeneration'] - $query[0]['lastyeargeneration'];

	// 当天蓄电池累计数据
	$query = "SELECT max(daycharmaxcurrent) as dayCharMaxCurr,max(daydischarmaxcurrent) as dayDischarMaxCurr,min(daybattmintemper) as dayMinTemper,max(daybattmaxtemper) as dayMaxTemper,max(daychargemaxpow) as dayCharMaxPower,max(daydischarmaxpow) as dayDischarMaxPower,max(voltagedaymax) as dayMaxVoltage,min(voltagedaymin) as dayMinVoltage FROM realtime_info_log
			WHERE lampid =".$id." 
			AND updatetime>='".$beginDateDay."' 
			AND updatetime<='".$endDateDay."'";
	$query = $db->query($query);
	
	$data['dayCharMaxCurr'] = empty($query[0]['dayCharMaxCurr']) ? 0 : round($query[0]['dayCharMaxCurr'],2);
	$data['dayDischarMaxCurr'] = empty($query[0]['dayDischarMaxCurr']) ? 0 : round($query[0]['dayDischarMaxCurr'],2);
	$data['dayMinTemper'] = empty($query[0]['dayMinTemper']) ? 0 : round($query[0]['dayMinTemper'],2);
	$data['dayMaxTemper'] = empty($query[0]['dayMaxTemper']) ? 0 : round($query[0]['dayMaxTemper'],2);
	$data['dayCharMaxPower'] = empty($query[0]['dayCharMaxPower']) ? 0 : round($query[0]['dayCharMaxPower'],2);
	$data['dayDischarMaxPower'] = empty($query[0]['dayDischarMaxPower']) ? 0 : round($query[0]['dayDischarMaxPower'],2);
	$data['dayMaxVoltage'] = empty($query[0]['dayMaxVoltage']) ? 0 : round($query[0]['dayMaxVoltage'],2);
	$data['dayMinVoltage'] = empty($query[0]['dayMinVoltage']) ? 0 : round($query[0]['dayMinVoltage'],2);

	// 当月蓄电池累计数据
	$query = "SELECT max(daycharmaxcurrent) as monthCharMaxCurr,max(daydischarmaxcurrent) as monthDischarMaxCurr,min(daybattmintemper) as monthMinTemper,max(daybattmaxtemper) as monthMaxTemper,max(daychargemaxpow) as monthCharMaxPower,max(daydischarmaxpow) as monthDischarMaxPower,max(voltagedaymax) as monthMaxVoltage,min(voltagedaymin) as monthMinVoltage FROM realtime_info_log
			WHERE lampid =".$id." 
			AND updatetime>='".$beginDateMonth."' 
			AND updatetime<='".$endDateMonth."'";
	$query = $db->query($query);
	$data['monthCharMaxCurr'] = empty($query[0]['monthCharMaxCurr']) ? 0 : round($query[0]['monthCharMaxCurr'],2);
	$data['monthDischarMaxCurr'] = empty($query[0]['monthDischarMaxCurr']) ? 0 : round($query[0]['monthDischarMaxCurr'],2);
	$data['monthMinTemper'] = empty($query[0]['monthMinTemper']) ? 0 : round($query[0]['monthMinTemper'],2);
	$data['monthMaxTemper'] = empty($query[0]['monthMaxTemper']) ? 0 : round($query[0]['monthMaxTemper'],2);
	$data['monthCharMaxPower'] = empty($query[0]['monthCharMaxPower']) ? 0 : round($query[0]['monthCharMaxPower'],2);
	$data['monthDischarMaxPower'] = empty($query[0]['monthDischarMaxPower']) ? 0 : round($query[0]['monthDischarMaxPower'],2);
	$data['monthMaxVoltage'] = empty($query[0]['monthMaxVoltage']) ? 0 : round($query[0]['monthMaxVoltage'],2);
	$data['monthMinVoltage'] = empty($query[0]['monthMinVoltage']) ? 0 : round($query[0]['monthMinVoltage'],2);

	// 当年蓄电池累计数据
	$query = "SELECT max(daycharmaxcurrent) as yearCharMaxCurr,max(daydischarmaxcurrent) as yearDischarMaxCurr,min(daybattmintemper) as yearMinTemper,max(daybattmaxtemper) as yearMaxTemper,max(daychargemaxpow) as yearCharMaxPower,max(daydischarmaxpow) as yearDischarMaxPower,max(voltagedaymax) as yearMaxVoltage,min(voltagedaymin) as yearMinVoltage FROM realtime_info_log
			WHERE lampid =".$id." 
			AND updatetime>='".$beginDateYear."' 
			AND updatetime<='".$endDateYear."'";
	$query = $db->query($query);
	$data['yearCharMaxCurr'] = empty($query[0]['yearCharMaxCurr']) ? 0 : round($query[0]['yearCharMaxCurr'],2);
	$data['yearDischarMaxCurr'] = empty($query[0]['yearDischarMaxCurr']) ? 0 : round($query[0]['yearDischarMaxCurr'],2);
	$data['yearMinTemper'] = empty($query[0]['yearMinTemper']) ? 0 : round($query[0]['yearMinTemper'],2);
	$data['yearMaxTemper'] = empty($query[0]['yearMaxTemper']) ? 0 : round($query[0]['yearMaxTemper'],2);
	$data['yearCharMaxPower'] = empty($query[0]['yearCharMaxPower']) ? 0 : round($query[0]['yearCharMaxPower'],2);
	$data['yearDischarMaxPower'] = empty($query[0]['yearDischarMaxPower']) ? 0 : round($query[0]['yearDischarMaxPower'],2);
	$data['yearMaxVoltage'] = empty($query[0]['yearMaxVoltage']) ? 0 : round($query[0]['yearMaxVoltage'],2);
	$data['yearMinVoltage'] = empty($query[0]['yearMinVoltage']) ? 0 : round($query[0]['yearMinVoltage'],2);

	return $data;
}

?>