<?php
const DBHOST = 'localhost';
CONST DBPORT = '3306';
const DBNAME = 'test';
const DBUSER = 'root';
const DBPASS = '123456';

withTransaction(function(PDO $db) {
    $insertReport = $db->prepare('INSERT INTO tbl_report (`uid`,`type`,`status`,`target_id`,`content`,`datetime`) VALUES (?,?,?,?,?,?)');
    $insertAttach = $db->prepare('INSERT INTO tbl_report_attach (`rid`,`type`,`usage`,`url`,`datetime`) VALUES (?,?,?,?,?)');

    $selectCommReports = $db->prepare('SELECT * from tbl_comm_report ORDER BY id DESC');
    $selectCommReportAttaches = $db->prepare('SELECT * from tbl_comm_report_attach WHERE rid = ?');
    $selectCommReports->execute();
    foreach($selectCommReports->fetchAll() as $commReport) {
        $insertReport->execute([
            $commReport['uid'],
            0,
            $commReport['status'],
            $commReport['fuid'],
            $commReport['content'],
            $commReport['datetime'],
        ]);

        $lastId = $db->lastInsertId();
        $selectCommReportAttaches->execute([$commReport['id']]);
        foreach($selectCommReportAttaches->fetchAll() as $commAttach) {
            $insertAttach->execute([
                $lastId,
                $commAttach['type'],
                $commAttach['usage'],
                $commAttach['url'],
                $commAttach['datetime'],
            ]);
        }
        $selectCommReportAttaches->closeCursor();
    }
    $selectCommReports->closeCursor();
});


function withTransaction($func) {
    withDb(function(PDO $db) use ($func) {
        $db->beginTransaction();
        try {
            $func($db);
            $db->commit();
        } catch (Exception $e) {
            $db->rollBack();
            throw $e;
        }
    });
}

function withDb($func) {
    try {
        $dbh = new PDO(sprintf('mysql:host=%s;dbname=%s;charset=UTF8;port=%s', DBHOST, DBNAME, DBPORT), DBUSER, DBPASS);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $func($dbh);

        $dbh = null;
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . PHP_EOL;
        die();
    }
}