#!/usr/bin/php
<?php

    $mincount=10;
    $report_start="08:00:01";
    $report_end="23:59:59";

    $dyd=strtotime('-1 days');
    $sincey=date('Y-m-d H:i:s',strtotime(date('Y-m-d ',$dyd).$report_start));
    $untily=date('Y-m-d H:i:s',strtotime(date('Y-m-d ',$dyd).$report_end));
    $sincet=date('Y-m-d H:i:s',strtotime(date('Y-m-d ').$report_start));
    $untilt=date('Y-m-d H:i:s',strtotime(date('Y-m-d ').$report_end));

/// init
    global $ini;
    global $node;

    require(dirname(__FILE__)."/../include/functions.php");
    init();

    if (get_ini_opt('cron','mail-mysql-slow') === '0') { exit(0); }

    check_live();

### read configuration options

#    $webroot=$ini['global']['webroot'];

    $mail_tpl = $ini['global']['template_dir']."/slowquery.txt";
    $mailbody=file_get_contents($mail_tpl);

    $mail_fromaddr = $ini['global']['email'];
    $mail_fromname = $ini['global']['name'];
    $mail_subject="FIGYELEM! Nagy terhelést okozó MySQL lekérés!";
    $mail_subject_root="MySQL slow query report on ".$ini['global']['host'];

    if ($node!=1) { $mail_subject_root.=" node ".$node; }

    if (array_key_exists('cmysql',$ini) && array_key_exists('chrdir',$ini['cmysql']) && !(empty($ini['cmysql']['chrdir'])))
    {
	$chrdir=$ini['cmysql']['chrdir'];
    }
    else
    {
	$chrdir='';
    }

    # get Percona query digest
    //$sqlog="/var/log/mysql/mysql-slow.log";
    $sqlog=$chrdir."/var/log/mysql/mysql-slow.log";

    exec("pt-query-digest --since '$sincey' --until '$untily' --report-format query_report $sqlog", $darra);
    exec("pt-query-digest --since '$sincet' --until '$untilt' --report-format query_report $sqlog", $darrb);

    $darr=array_merge($darra,$darrb);
    $darr[]="# Query 9999: END";

    $rep=array();
    $reps=array();
    $cliid="";
    $count=0;

    foreach($darr as $dline) {

	//look for start of a new report
	if (preg_match('/^\# Query [0-9]{1,4}\:/',$dline)) {
	    if (sizeof($rep)>1) {

		//Send error report
		if ((is_numeric($cliid)) && ($count>$mincount)) {
	    	    $reps[$cliid][]=$rep;
		}

		//reset data
		$rep=array();
		$cliid="";
	    }
	}

	// Get user id
	if (preg_match('/^\# Users[\t ]*[0-9]{4}\_/',$dline,$matches)) {
	    preg_match ('/[0-9]{4}/',$matches[0],$cliids);
	    $cliid=$cliids[0];
	}

	// Get count
	if (preg_match('/^\# Count[\t ]*[0-9]*[\t ]*[0-9]*/',$dline,$matches)) {
	    preg_match ('/[0-9]*$/',$matches[0],$counts);
	    $count=$counts[0];
	}

	//add line to array
	$rep[]=$dline;

    }


    // send reports to clients
    foreach ($reps as $cliid => $reports) {

	if (is_numeric($cliid)) {

	    $sql ="SELECT `email`, `name` FROM `client` WHERE `id`=$cliid;";
	    $result=DBquery($sql);
	    $row=$result->fetch_assoc();

	    $email=$row['email'];
	    $name=$row['name'];

	    $reporttxt="";
	    foreach($reports as $report) {
		$reporttxt.=implode("\n",$report);
		$reporttxt.="\n";
	    }

	    $msgbody=$mailbody."\n".$reporttxt."\n";
    	    if ($email != "") {
		mailsend($mail_fromname, $mail_fromaddr, $email, $mail_subject, $msgbody);
    	    }
	}
    }

    DBclose();

    // send the complete report to root
    if (count($reps)>0) {
		$fullrep=implode("\n",$darr);
		mailsend("MySQLReport", "root", "root", $mail_subject_root, $fullrep);
		sleep(1);
    }


?>