`

    <?php

namespace App\Utility;

use EasySwoole\EasySwoole\Logger;
use EasySwoole\Mysqli\QueryBuilder;
use EasySwoole\ORM\DbManager;
use EasySwoole\ORM\Exception\Exception;
use Es3\Base\Model;

class CompareTable
{
    /**
     * 初始化
     * @param string $tmpTableName
     * @throws Exception
     * @throws \Throwable
     */
    public static function initTempTable(string $tmpTableName): void
    {
        $queryBuild = new QueryBuilder();

        /** 创建表 */
        $originalTable = CompareTable::getOriginalTableName($tmpTableName);
        $queryBuild->raw("DROP TABLE IF EXISTS `{$originalTable}`");
        DbManager::getInstance()->query($queryBuild, false);

        $sql = "CREATE TABLE `{$originalTable}` (
                  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
                  `count` varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
                  PRIMARY KEY (`id`) USING BTREE,
                  KEY `code` (`code`(255),`count`) USING BTREE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{$tmpTableName}原始表'
                ";
        $queryBuild->raw($sql);
        DbManager::getInstance()->query($queryBuild, false);

        Logger::getInstance()->log("CREATE $originalTable", Logger::LOG_LEVEL_INFO, 'kingdee_jll');

        /** 创建表 */
        $targetTable = CompareTable::getTargetTableName($tmpTableName);
        $queryBuild->raw("DROP TABLE IF EXISTS `{$targetTable}`");
        DbManager::getInstance()->query($queryBuild, false);

        $sql = "CREATE TABLE `{$targetTable}` (
                  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
                  `count` varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
                  PRIMARY KEY (`id`) USING BTREE,
                  KEY `code` (`code`(255),`count`) USING BTREE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{$tmpTableName}目标表'
                ";
        $queryBuild->raw($sql);
        DbManager::getInstance()->query($queryBuild, false);

        Logger::getInstance()->log("CREATE $targetTable", Logger::LOG_LEVEL_INFO, 'kingdee_jll');
    }

    /**
     * 获取原始表名
     * @param string $tmpTableName
     * @return string
     */
    public static function getOriginalTableName(string $tmpTableName): string
    {
        return "temp_original_{$tmpTableName}";
    }

    /**
     * 获取目标表名
     * @param string $tmpTableName
     * @return string
     */
    public static function getTargetTableName(string $tmpTableName): string
    {
        return "temp_target_{$tmpTableName}";
    }

    /**
     * 对比结果
     * @param string $tmpTableName
     * @return \EasySwoole\ORM\Db\Result|null
     * @throws Exception
     * @throws \Throwable
     */
    public static function compare(string $tmpTableName): ?array
    {
        $result = null;
        $queryBuild = new QueryBuilder();
        $originalTable = CompareTable::getOriginalTableName($tmpTableName);
        $targetTable = CompareTable::getTargetTableName($tmpTableName);

        $sql = "SELECT
                        code, 'original' type
                FROM
                        {$originalTable} original
                WHERE
                        NOT EXISTS ( SELECT code FROM {$targetTable} target WHERE original.code = target.code )
                  
                UNION
          
                SELECT
                        code, 'target' type
                FROM
                        {$targetTable} target
                WHERE
                        NOT EXISTS ( SELECT code FROM {$originalTable} original WHERE target.code = original.code )
                ORDER BY code desc
        ";

        if (isDev()) {
            $sql = "SELECT
                        code, 'original' type
                FROM
                        {$originalTable} original
                UNION

                SELECT
                        code, 'target' type
                FROM
                        {$targetTable} target
                ORDER BY code desc
            ";
        }
        Logger::getInstance()->log("sql" . $sql, Logger::LOG_LEVEL_INFO, 'kingdee_jll');
        $queryBuild->raw($sql);
        $result = DbManager::getInstance()->query($queryBuild, false);
        $result = $result->getResult() ?? null;

        return $result;
    }

    /**
     * 目标sql与原始sql差集
     * @param string $tmpTableName
     * @return \EasySwoole\ORM\Db\Result|null
     * @throws Exception
     * @throws \Throwable
     */
    public static function compareDiff(string $tmpTableName): ?array
    {
        $result = null;
        $queryBuild = new QueryBuilder();

        $originalTable = CompareTable::getOriginalTableName($tmpTableName);
        $targetTable = CompareTable::getTargetTableName($tmpTableName);

        $sql = "
            SELECT DISTINCT code 
            FROM
                {$targetTable}
            WHERE
                code NOT IN ( SELECT code FROM {$originalTable} )
        ";

        $queryBuild->raw($sql);
        $result = DbManager::getInstance()->query($queryBuild, false);
        $result = $result->getResult() ?? null;

        return $result;
    }
}

`

`

    /**
     * 与金蝶系统对比固定资产卡片是否一致
     * @param string $tempTableName
     */
    public function card(string $tempTableName):void
    {
        $archives = config('database.archives', true);
        /** 分别获取原始和目标表名 */
        $originalTableName = CompareTable::getOriginalTableName($tempTableName);
        $targetTableName = CompareTable::getTargetTableName($tempTableName);

        /** 拉取数据 */
        $originalSql = "INSERT INTO {$originalTableName} (code,count)
                        SELECT
                            CONCAT(archives.financial_number,'_',archives.archives_num,'_',organiza.financial_id,'_',archives.`name`,'_',atype.financial_id) 'code', 1 count
                        FROM
                            {$archives}.archives_group archives
                        LEFT JOIN {$archives}.organiza_main organiza ON archives.organiza_id = organiza.id
                        LEFT JOIN {$archives}.archives_type atype ON archives.type_id = atype.id
        ";
        $this->exec($originalSql);

        $targetSql = "INSERT INTO {$targetTableName} (code,count)
                      SELECT
                          CONCAT(FNumber,'_',FQuantity,'_',FAssetOrgID,'_',FName,'_',FAssetTypeID) 'code', 1 count 
                      FROM
                          kingdee_archives
        ";
        $this->exec($targetSql);
    }

`