zzcx_line/coding/api1.0/run_info_check.php

<?php
require_once __DIR__ . '/../st-xm/Common/Mysql.php';
header("Access-Control-Allow-Origin:*");
//班次信息总体监测（包括run_bus,run_prod,run_stock）
date_default_timezone_set('Asia/Shanghai');
$today = date('Y-m-d');
$tomorrow = date('Y-m-d', strtotime('+1 day'));
$afterTomorrow = date('Y-m-d', strtotime('+2 day'));
$afterTomorrow_x = date('Ym', strtotime('+2 day'));

$nextMonth = date('Y-m-d', strtotime("+1 month"));
$nextWeek = date('Y-m-d', strtotime('+1 week'));
$nextWeek_x = date('Ym', strtotime('+1 week'));

$pdo = conn();

//----run_bus监测
#region 1 run_bus
//把数组key值改为run_id+bus_order_id
function change_key_bus($a)
{
    $tmp = array();
    foreach ($a as $k => $v) {
        $tmpk = $v['run_id'] . '-' . $v['bus_order_id'];
        $tmp["$tmpk"] = $v;
        $tmp["$tmpk"]['id'] = $k;
    }
    return $tmp;
}

//合并数组
function multi_merge_bus($a, $b)
{
    $arr_bus = array();
    foreach ($a as $k => $r) {
        if (isset($b[$k])) {
            $arr_bus[$k] = array_merge($r, $b[$k]);
        } else {
            $arr_bus[$k] = $r;
            $arr_bus[$k]['num2'] = 0;
        }
    }
    foreach ($b as $k => $r) {
        if (!isset($arr_bus[$k])) {
            $arr_bus[$k] = $r;
            $arr_bus[$k]['num1'] = 0;
        }
    }
    return $arr_bus;
}


//从订单角度得到run_bus表的数据
$sql_bus1 = "SELECT
        l.line_id,
        m.run_id,
        m.RUN_BUS_ORDER_ID bus_order_id,
        m.run_date,
      count(m.order_id) as num1
    FROM
        order_main m
    LEFT JOIN opera_line l ON m.PARENT_PROD_ID = l.line_id
    LEFT JOIN opera_tickets t on m.prod_id=t.TICKET_ID
    LEFT JOIN opera_line w on t.line_id=w.line_id 
    WHERE
        m.PARENT_ORDER_ID <> 0
    AND m.run_date >= '$today'
    AND m.run_date <= '$nextMonth'
    AND m.ORDER_VALID_STATUS = 1
    AND m.CANCEL_FLAG = 0
    AND m.PROD_ID <> 380001
    and m.run_bus_order_id<>0
    AND w.line_type in (255,256,368)
    AND m.ORDER_PROD_TYPE = 82
    group by run_id,bus_order_id
    order by m.run_date;";

$res1 = $pdo->query($sql_bus1);
$res1->setFetchMode(PDO::FETCH_ASSOC);
$arr_bus1 = $res1->fetchAll();
$res1->closeCursor();


$sql_bus2 = "SELECT
        b.prod_id as line_id,
        b.run_id,
        a.bus_order_id,
        b.run_date,
        a.SEAT_COUNT,
        a.saled_count as num2
    FROM
        run_bus a
    LEFT JOIN run_main b ON a.run_id = b.run_id
    WHERE
        b.run_date >= '$today'
        and b.run_date <= '$nextMonth'
        and a.CANCEL_FLAG=0
        and a.SALED_COUNT<>0
        AND b.PROD_ID <> 0
        and b.prod_id<>451479
    order by b.run_date;";

$res2 = $pdo->query($sql_bus2);
$res2->setFetchMode(PDO::FETCH_ASSOC);
$arr_bus2 = $res2->fetchAll();
$res2->closeCursor();

//切换key值
$arr_bus1 = change_key_bus($arr_bus1);

$arr_bus2 = change_key_bus($arr_bus2);
$arr_bus = multi_merge_bus($arr_bus1, $arr_bus2);


//得到不对应的数据

$diffarr_bus = array();
$content_bus = '[' . date('Y-m-d H:i:s') . ']run_bus表数据不一致提醒:';
foreach ($arr_bus as $k => $v) {
    if ($v['num1'] != $v['num2']) {
        $diffarr_bus[] = $v;
    }
}
//将数据插入到run_info_check表里
$inset_bus_sql = "INSERT INTO run_check_log (
                        run_table,
                        line_id,
                        run_id,
                        extra_id,
                        create_time,
                        run_date,
                        real_num,
                        record_num
                    )
                    VALUES";
$value_bus_sql = '';
foreach ($diffarr_bus as $k => $v) {
    $value_bus_sql .= "('run_bus'," . $v['line_id'] . "," . $v['run_id'] . "," . $v['bus_order_id'] . ",now(),'" . $v['run_date'] . "'," . $v['num1'] . "," . $v['num2'] . "),";
}
$value_bus_sql = rtrim($value_bus_sql, ',');

$inset_bus_sql .= $value_bus_sql;
if ($value_bus_sql) {                     //如果有数据，插入到数据表run_check_log
    $afr = $pdo->exec($inset_bus_sql);
}

//整合短信发送内容
foreach ($diffarr_bus as $k => $v) {
    $content_bus .= '【<' . ($k + 1) . '>,run_id:' . $v['run_id'] . ',bus_order_id:' . $v['bus_order_id'] . ',实际订单数:' . $v['num1'] . ',记录数:' . $v['num2'] . '】';
}

$content_bus .= '. ';
if (empty($diffarr_bus)) {
    $content_bus = '';
}
#endregion
//---run_prod监测
#region 2 run_prod
//把数组key值改为run_id+prod_id
function change_key_prod($a)
{
    $tmp = array();
    foreach ($a as $k => $v) {
        $tmpk = $v['run_id'] . '-' . $v['prod_id'];
        $tmp["$tmpk"] = $v;
        $tmp["$tmpk"]['id'] = $k;
    }
    return $tmp;
}

//合并数组
function multi_merge_prod($a, $b)
{

    $arr_prod = array();

    foreach ($a as $k => $r) {
        if (isset($b[$k])) {
            $arr_prod[$k] = array_merge($r, $b[$k]);
        } else {
            $arr_prod[$k] = $r;
            $arr_prod[$k]['num2'] = 0;
        }

    }

    foreach ($b as $k => $r) {
        if (!isset($arr_prod[$k])) {
            $arr_prod[$k] = $r;
            $arr_prod[$k]['num1'] = 0;
        }

    }


    return $arr_prod;
}

//从订单角度得到run_prod表的数据
$sql_prod1 = "SELECT
	l.line_id,
	m.run_id,
	m.prod_id,
	m.run_date,	
  count(m.order_id) as num1
FROM
	order_main m
LEFT JOIN opera_line l ON m.PARENT_PROD_ID = l.line_id
LEFT JOIN opera_tickets t on m.prod_id=t.TICKET_ID
LEFT JOIN opera_line w on t.line_id=w.line_id 
WHERE
	m.PARENT_ORDER_ID <> 0
AND m.run_date >= '$today'
AND m.run_date <= '$nextMonth'
AND m.ORDER_VALID_STATUS = 1
AND m.CANCEL_FLAG = 0
AND m.PROD_ID <> 380001
AND w.line_type in (255,256,368)
AND m.ORDER_PROD_TYPE = 82
group by run_id,prod_id
order by m.run_date;";

$res1 = $pdo->query($sql_prod1);
$res1->setFetchMode(PDO::FETCH_ASSOC);
$arr_prod1 = $res1->fetchAll();
$res1->closeCursor();
//run_prod表本身的数据
$sql_prod2 = "SELECT
    b.prod_id as line_id,
	b.run_id,
	a.prod_id,
	b.run_date,
	a.saled_count as num2
FROM
	run_prod a
LEFT JOIN run_main b ON a.run_id = b.run_id
WHERE
	b.run_date >= '$today'
	and b.run_date <= '$nextMonth'
    and a.SALED_COUNT<>0
    AND b.PROD_ID <> 0
order by b.run_date;";

$res2 = $pdo->query($sql_prod2);
$res2->setFetchMode(PDO::FETCH_ASSOC);
$arr_prod2 = $res2->fetchAll();
$res2->closeCursor();

//切换key值
$arr_prod1 = change_key_prod($arr_prod1);
$arr_prod2 = change_key_prod($arr_prod2);

$arr_prod = multi_merge_prod($arr_prod1, $arr_prod2);

//得到不对应的数据

$diffarr_prod = array();
$content_prod = '[' . date('Y-m-d H:i:s') . ']run_prod表数据不一致提醒:';
foreach ($arr_prod as $k => $v) {
    if ($v['num1'] != $v['num2']) {
        $diffarr_prod[] = $v;
    }
}
//将数据插入到run_info_check表里
$inset_prod_sql = "INSERT INTO run_check_log (
                    run_table,
                    line_id,
                    run_id,
                    extra_id,
                    create_time,
                    run_date,
                    real_num,
                    record_num
                )
                VALUES";
$value_prod_sql = '';
foreach ($diffarr_prod as $k => $v) {
    $value_prod_sql .= "('run_prod'," . $v['line_id'] . "," . $v['run_id'] . "," . $v['prod_id'] . ",now(),'" . $v['run_date'] . "'," . $v['num1'] . "," . $v['num2'] . "),";
}
$value_prod_sql = rtrim($value_prod_sql, ',');

$inset_prod_sql .= $value_prod_sql;
if ($value_prod_sql) {                     //如果有数据，插入到数据表run_check_log
    $afr = $pdo->exec($inset_prod_sql);
}


//print_r($diffarr_prod);
//整合短信发送内容
foreach ($diffarr_prod as $k => $v) {
    $content_prod .= '【<' . ($k + 1) . '>,run_id:' . $v['run_id'] . ',prod_id:' . $v['prod_id'] . ',实际订单数:' . $v['num1'] . ',记录数:' . $v['num2'] . '】';
}

$content_prod .= '. ';
if (empty($diffarr_prod)) {
    $content_prod = '';
}
#endregion

//---run_stock监测
#region 3 run_stock
//二维数组去除重复
function array_unique_fb_stock($array2D)
{
    foreach ($array2D as $v) {
        $v = join(",", $v); //降维,也可以用implode,将一维数组转换为用逗号连接的字符串
        $temp[] = $v;
    }
    $temp = array_unique($temp); //去掉重复的字符串,也就是重复的一维数组
    foreach ($temp as $k => $v) {
        $array = explode(",", $v); //再将拆开的数组重新组装
        $temp2[$k]['line_id'] = $array[0];
        $temp2[$k]['run_id'] = $array[1];
        $temp2[$k]['run_date'] = $array[2];
        $temp2[$k]['seq_id'] = $array[3];
        $temp2[$k]['num1'] = $array[4];
    }
    return $temp2;
}

//把数组key值改为run_id+seq_id
function change_key_stock($a)
{
    $tmp = array();
    foreach ($a as $k => $v) {
        $tmpk = $v['run_id'] . '-' . $v['seq_id'];
        $tmp["$tmpk"] = $v;
        $tmp["$tmpk"]['id'] = $k;
    }
    return $tmp;
}

//合并数组
function multi_merge_stock($a, $b) //$a 实际订单数据,$b记录的数值
{

    $arr_stock = array();

    foreach ($a as $k => $r) {
        if (isset($b[$k])) {
            $arr_stock[$k] = array_merge($r, $b[$k]);
        } else {
            $arr_stock[$k] = $r;
            $arr_stock[$k]['num2'] = 0;
        }

    }

    foreach ($b as $k => $r) {
        if (!isset($arr_stock[$k])) {
            $arr_stock[$k] = $r;
            $arr_stock[$k]['num1'] = 0;
        }

    }


    return $arr_stock;
}


//从订单角度得到run_stock表的数据
$sql_stock1 = "SELECT
        l.line_id,
        m.run_id,
        m.prod_start_station_seq_id,
        m.prod_end_station_seq_id,
        m.run_date,	
      count(m.order_id) as num1
    FROM
        order_main m
    LEFT JOIN opera_line l ON m.PARENT_PROD_ID = l.line_id
    LEFT JOIN opera_tickets t on m.prod_id=t.TICKET_ID
    LEFT JOIN opera_line w on t.line_id=w.line_id 
    WHERE
        m.PARENT_ORDER_ID <> 0
    AND m.run_date >= '$today'
    AND m.run_date <= '$nextMonth'
    AND m.ORDER_VALID_STATUS = 1
    AND m.CANCEL_FLAG = 0
    AND m.PROD_ID <> 380001
    AND w.line_type in (255,256,368)
    AND m.ORDER_PROD_TYPE = 82
    group by run_id,prod_start_station_seq_id,prod_end_station_seq_id
    order by m.run_date,m.run_id,m.prod_start_station_seq_id;";

$res1 = $pdo->query($sql_stock1);
$res1->setFetchMode(PDO::FETCH_ASSOC);
$arr_stock1 = $res1->fetchAll();
$res1->closeCursor();

//run_stock表本身的数据
$sql_stock2 = "SELECT
        b.prod_id as line_id,
        b.run_id,
        a.seq_id,
        b.run_date,
        a.saled_count as num2
    FROM
        run_stock a
    LEFT JOIN run_main b ON a.run_id = b.run_id
    WHERE
        b.run_date >= '$today'
        and b.run_date <= '$nextMonth'
        and a.CANCEL_FLAG=0
        and a.SEAT_TYPE=72
        AND b.PROD_ID <> 0
        and (select sum(SALED_COUNT) as co from run_stock where run_id=b.run_id and CANCEL_FLAG=0)>0
    order by b.run_date,b.run_id;";

$res2 = $pdo->query($sql_stock2);
$res2->setFetchMode(PDO::FETCH_ASSOC);
$arr_stock2 = $res2->fetchAll();
$res2->closeCursor();

#对arr1做处理，根据分段合并数据
function merge_stock($a, $b)
{
    $arr_stock = array();
    foreach ($b as $k => $v) {

        //$tmp=$v['run_id'].'-'.$v['prod_end_station_seq_id'];
        $num = 0;
        foreach ($a as $k2 => $v2) {
            if ($v['run_id'] == $v2['run_id']) {
                if ($v['seq_id'] >= $v2['prod_start_station_seq_id'] && $v['seq_id'] < $v2['prod_end_station_seq_id']) {
                    $num += $v2['num1'];
                }
            }
        }
        $arr_stock[$k]['line_id'] = $v['line_id'];
        $arr_stock[$k]['run_id'] = $v['run_id'];
        $arr_stock[$k]['run_date'] = $v['run_date'];
        $arr_stock[$k]['seq_id'] = $v['seq_id'];
        $arr_stock[$k]['num1'] = $num;

    }
    return array_unique_fb_stock($arr_stock);//二维数组去重
}

$arr_stock1 = merge_stock($arr_stock1, $arr_stock2);


//切换key值
$arr_stock1 = change_key_stock($arr_stock1);
//print_r($arr_stock1);
$arr_stock2 = change_key_stock($arr_stock2);

$arr_stock = multi_merge_stock($arr_stock1, $arr_stock2);//合并二维数组

//得到不对应的数据

$diffarr_stock = array();
$content_stock = '[' . date('Y-m-d H:i:s') . ']run_stock表数据不一致提醒:';
foreach ($arr_stock as $k => $v) {
    if ($v['num1'] != $v['num2']) {
        $diffarr_stock[] = $v;
    }
}

//将数据插入到run_info_check表里
$inset_stock_sql = "INSERT INTO run_check_log (
                    run_table,
                    line_id,
                    run_id,
                    extra_id,
                    create_time,
                    run_date,
                    real_num,
                    record_num
                )
                VALUES";
$value_stock_sql = '';
foreach ($diffarr_stock as $k => $v) {
    $value_stock_sql .= "('run_stock'," . $v['line_id'] . "," . $v['run_id'] . "," . $v['seq_id'] . ",now(),'" . $v['run_date'] . "'," . $v['num1'] . "," . $v['num2'] . "),";
}
$value_stock_sql = rtrim($value_stock_sql, ',');

$inset_stock_sql .= $value_stock_sql;
if ($value_stock_sql) {                     //如果有数据，插入到数据表run_check_log
    $afr = $pdo->exec($inset_stock_sql);
}

//print_r($diffarr_stock);
//整合短信发送内容
foreach ($diffarr_stock as $k => $v) {
    $content_stock .= '【<' . ($k + 1) . '>,run_id:' . $v['run_id'] . ',排序seq_id:' . $v['seq_id'] . ',实际订单数:' . $v['num1'] . ',记录数:' . $v['num2'] . '】';
}

$content_stock .= '. ';
if (empty($diffarr_stock)) {
    $content_stock = '';
}
#endregion

#region 4 run_x 数据检测
$today_x = date('Ym');
$tomorrow_x = date('Ym', strtotime("+1 day"));
//把数组key值改为run_id+bus_order_id+seq_id+order_id
function change_key_x($a)
{
    $tmp = array();
    foreach ($a as $k => $v) {
        $tmpk = $v['run_id'] . '-' . $v['bus_order_id'] . '-' . $v['seq_id'] . '-' . $v['order_id'];
        $tmp["$tmpk"] = $v;
        $tmp["$tmpk"]['id'] = $k;
    }
    return $tmp;
}

//合并数组
function multi_merge_x($a, $b) //$a 实际订单数据,$b记录的数值
{

    $arr = array();

    foreach ($a as $k => $r) {
        if (isset($b[$k])) {
            $arr[$k] = array_merge($r, $b[$k]);
        } else {
            $arr[$k] = $r;
            $arr[$k]['num2'] = 0;
            $arr[$k]['run_seat_seq_id'] = 0;
        }

    }

    foreach ($b as $k => $r) {
        if (!isset($arr[$k])) {
            $arr[$k] = $r;
            $arr[$k]['num1'] = 0;
            $arr[$k]['order_seat_seq_id'] = 0;
            //$arr[$k]['parent_order_id'] = 0;
        }

    }
    return $arr;
}

//从订单角度得到run_x表的数据

$sql1 = "SELECT
        m.order_id,
        m.parent_order_id,
        l.line_id,
        m.run_id,
        m.run_bus_order_id as bus_order_id,
        m.prod_start_station_seq_id as seq_id,
        m.prod_start_station_seq_id as start_seq_id,
        m.prod_end_station_seq_id as end_seq_id,
		m.run_bus_seat_seq_id as order_seat_seq_id,
        m.run_date,
         1 as num1
    FROM
        order_main m
    LEFT JOIN opera_line l ON m.PARENT_PROD_ID = l.line_id
    LEFT JOIN opera_tickets t on m.prod_id=t.TICKET_ID
    LEFT JOIN opera_line w on t.line_id=w.line_id 
    WHERE
        m.PARENT_ORDER_ID <> 0
    AND m.run_date >= '$today'
    AND m.run_date <= '$afterTomorrow'
    AND m.ORDER_VALID_STATUS = 1
    AND m.CANCEL_FLAG = 0
    AND m.PROD_ID <> 380001
    AND w.line_type in (255,256,368)
    AND m.ORDER_PROD_TYPE = 82
    order by m.run_date,m.run_id,m.run_bus_order_id,m.prod_start_station_seq_id;";

$res4 = $pdo->query($sql1);
$res4->setFetchMode(PDO::FETCH_ASSOC);
$order_ori = $res4->fetchAll();
//print_r($order_ori);

#处理订单数组，得到订单run_x维度的数据
$order_x = array();

$len = count($order_x);
foreach ($order_ori as $k => $v) {
    $num = $v['end_seq_id'] - $v['start_seq_id'];
    if ($num >= 1) {
        for ($i = 1; $i <= $num; $i++) {
            $order_x[] = $v;
            $v['start_seq_id'] = $v['start_seq_id'] + 1;
            $v['seq_id'] = $v['seq_id'] + 1;
        }
    }
}

//print_r($order_x);

//run_x表本身的数据
$sql2 = "SELECT
        a.order_main_id as order_id,
        (select parent_order_id from order_main where order_id=a.order_main_id and order_prod_type=82 limit 1) as parent_order_id,
        b.prod_id as line_id,
        a.run_id,
        a.bus_order_id,
        a.order_id as seq_id,
		a.seat_seq_id as run_seat_seq_id,
        b.run_date,
         1 as num2
FROM
	run_$today_x a
LEFT JOIN run_main b ON a.run_id = b.run_id
WHERE
	b.run_date >= '$today'
	and b.run_date<='$afterTomorrow'
    and a.cancel_flag=0
    and a.seat_type=72
    and b.prod_id <> 0
    and a.seat_status=2
UNION 
SELECT
        a.order_main_id as order_id,
        (select parent_order_id from order_main where order_id=a.order_main_id and order_prod_type=82 limit 1) as parent_order_id,
        b.prod_id as line_id,
        a.run_id,
        a.bus_order_id,
        a.order_id as seq_id,
		a.seat_seq_id as run_seat_seq_id,
        b.run_date,
         1 as num2
FROM
	run_$afterTomorrow_x a
LEFT JOIN run_main b ON a.run_id = b.run_id
WHERE
	b.run_date >= '$today'
	and b.run_date<='$afterTomorrow'
    and a.cancel_flag=0
    and a.seat_type=72
    and b.prod_id <> 0
    and a.seat_status=2
order by run_date,run_id,bus_order_id,seq_id,run_seat_seq_id;";

$res5 = $pdo->query($sql2);
$res5->setFetchMode(PDO::FETCH_ASSOC);
$arr2 = $res5->fetchAll();

//切换key值
$order_x = change_key_x($order_x);

$run_x = change_key_x($arr2);

$arr_run_x = multi_merge_x($order_x, $run_x);//合并二维数组

//得到不对应的数据并按整合

$diffarr_x = array();
$diff_order =array();
foreach ($arr_run_x as $k => $v) {
    if (($v['num1'] != $v['num2']) || ($v['order_seat_seq_id'] != $v['run_seat_seq_id'])) {
        $diffarr_x[] = $v;
        $unKey=$v['run_id'].'-'.$v['bus_order_id'].'-'.$v['parent_order_id'];
        $diff_order[$unKey]=$v;
    }
}
//将数据插入到run_info_check表里
$inset_x_sql = "INSERT INTO run_check_log (
                    run_table,
                    line_id,
                    run_id,
                    extra_id,
                    create_time,
                    run_date,
                    real_num,
                    record_num
                )
                VALUES";
$value_x_sql = '';
foreach ($diff_order as $k => $v) {
    $value_x_sql .= "('run_x'," . $v['line_id'] . "," . $v['run_id'] . "," . $v['parent_order_id'] . ",now(),'" . $v['run_date'] . "'," . $v['num1'] . "," . $v['num2'] . "),";
}
$value_x_sql = rtrim($value_x_sql, ',');

$inset_x_sql .= $value_x_sql;
if ($value_x_sql) {                     //如果有数据，插入到数据表run_check_log
    $afr = $pdo->exec($inset_x_sql);
}
//整合短信发送内容
$content_run_x = '[' . date('Y-m-d H:i:s') . ']run_x表数据不一致提醒:';
$i=1;
foreach ($diff_order as $k => $v) {
    $content_run_x .= '【<' . ($i) . '>,run_id:' . $v['run_id'] . ',bus_order_id:' . $v['bus_order_id'] . ',主订单号:' . $v['parent_order_id'] . '】';
    $i++;
}
if (empty($diffarr_x)) {
    $content_run_x = '';
}
#endregion

if (empty($diffarr_bus) && empty($diffarr_prod) && empty($diffarr_stock) && empty($diffarr_x)) {
    exit('数据一致!');
}

//发送短信sendTelMessage($phones,$user_name,$content_stock,$order_id)     qius13816608252   zhangs15203408170 wenyl15753206507
//$phones = '13816608252,15203408170,15753206507';
$phones = '13816608252,15203408170,15753206507';
$show=isset($_GET['show']);
if($show){
    print_r($content_bus);
    print_r($content_prod);
    print_r($content_stock);
    print_r($diffarr_x);
    print_r($diff_order);
}
//发送短信
if(!$show){
    $final_content=$content_bus.$content_prod.$content_stock.$content_run_x;
    $response = sendTelMessage($phones, '比特', $final_content, 0);
    //print_r($final_content);
    var_dump($response);
}