se/SE/se-lib/RefParams.php

<?php


/**
 * Usage:
 * - RefParams::setParam(124, 11, 22, 'key', 'value');
 * - RefParams::getLastParam(124, 11, 22, 'key'); // return last inserted value
 * - RefParams::getParams(124, 11, 22, 'key'); // return all values
 * - RefParams::getAllParams(124, 11, 22); // return all key and values
 * - RefParams::removeAllParams(124, 11, 22); // return all key and values
 * 
 * Storage operations:
 * - only insert into `CRM_REF_PARAMS`
 * - only insert into `CRM_REF_PARAMS__REMOVED_LOG`
 * - delete on `CRM_REF_PARAMS` triggers insert into `CRM_REF_PARAMS__REMOVED_LOG`
 */
class RefParams {

    static function prepareTable() {
        DB::getPDO()->execSql("
            CREATE TABLE IF NOT EXISTS `CRM_REF_PARAMS` (
                `ID_REF` int(11) NOT NULL,
				`PRIMARY_KEY` int(11) NOT NULL,
                `REMOTE_PRIMARY_KEY` int(11) NOT NULL,
                `PARAM_NAME` varchar(32) NOT NULL DEFAULT '',
                `PARAM_VALUE` varchar(1024) NOT NULL DEFAULT '',
                `A_RECORD_CREATE_DATE` datetime DEFAULT NULL,
                `A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				KEY `ID_REF` (`ID_REF`),
				KEY `PRIMARY_KEY` (`PRIMARY_KEY`),
				KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`),
				KEY `PARAM_NAME` (`PARAM_NAME`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
        ");
        DB::getPDO()->execSql("
            CREATE TABLE IF NOT EXISTS `CRM_REF_PARAMS__REMOVED_LOG` (
                `ID` int(11) NOT NULL AUTO_INCREMENT,
                `A_RECORD_REMOVE_DATE` datetime DEFAULT NULL,
                `A_RECORD_REMOVE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
                `ID_REF` int(11) NOT NULL,
				`PRIMARY_KEY` int(11) NOT NULL,
                `REMOTE_PRIMARY_KEY` int(11) NOT NULL,
                `PARAM_NAME` varchar(32) NOT NULL DEFAULT '',
                `PARAM_VALUE` varchar(1024) NOT NULL DEFAULT '',
                `A_RECORD_CREATE_DATE` datetime DEFAULT NULL,
                `A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				PRIMARY KEY (`ID`),
				KEY `ID_REF` (`ID_REF`),
				KEY `PRIMARY_KEY` (`PRIMARY_KEY`),
				KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`),
				KEY `PARAM_NAME` (`PARAM_NAME`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
        ");
    }

    static function setParam($idRef, $pk, $remotePk, $name, $value) {
        self::validateBaseArgs($idRef, $pk, $remotePk);
        if (!$name) throw new Exception("Wrong ref param name");
        DB::getPDO()->insert("CRM_REF_PARAMS", [
            'ID_REF' => $idRef,
            'PRIMARY_KEY' => $pk,
            'REMOTE_PRIMARY_KEY' => $remotePk,
            'PARAM_NAME' => $name,
            'PARAM_VALUE' => $value,
            'A_RECORD_CREATE_DATE' => "NOW()",
            'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
        ]);
    }

    static function getLastParam($idRef, $pk, $remotePk, $name) { // @return value
        self::validateBaseArgs($idRef, $pk, $remotePk);
        return DB::getPDO()->fetchValue("
            select rp.PARAM_VALUE
            from `CRM_REF_PARAMS` rp
            where rp.ID_REF = :id_ref
                and rp.PRIMARY_KEY = :pk
                and rp.REMOTE_PRIMARY_KEY = :remote_pk
                and rp.PARAM_NAME = :name
            order by rp.A_RECORD_CREATE_DATE DESC
            limit 1
        ", [
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
            ':name' => $name,
        ]);
    }

    static function getParams($idRef, $pk, $remotePk, $name) { // @return array of values
        self::validateBaseArgs($idRef, $pk, $remotePk);
        if (!$name) throw new Exception("Wrong ref param name");
        return DB::getPDO()->fetchAll("
            select rp.*
            from `CRM_REF_PARAMS` rp
            where rp.ID_REF = :id_ref
                and rp.PRIMARY_KEY = :pk
                and rp.REMOTE_PRIMARY_KEY = :remote_pk
            order by rp.A_RECORD_CREATE_DATE DESC
        --    limit 1
        ", [
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
        ]);
    }

    static function getAllParams($idRef, $pk, $remotePk) { // @return array
        self::validateBaseArgs($idRef, $pk, $remotePk);
        return DB::getPDO()->fetchAll("
            select rp.*
            from `CRM_REF_PARAMS` rp
            where rp.ID_REF = :id_ref
                and rp.PRIMARY_KEY = :pk
                and rp.REMOTE_PRIMARY_KEY = :remote_pk
            order by rp.A_RECORD_CREATE_DATE DESC
        ", [
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
        ]);
    }

    static function removeParams($idRef, $pk, $remotePk, $name) {
        self::validateBaseArgs($idRef, $pk, $remotePk);
        if (!$name) throw new Exception("Wrong ref param name");
        DB::getPDO()->execSql("
            insert into `CRM_REF_PARAMS__REMOVED_LOG` (
                `A_RECORD_REMOVE_DATE`,
                `A_RECORD_REMOVE_AUTHOR`,
                `ID_REF`,
				`PRIMARY_KEY`,
                `REMOTE_PRIMARY_KEY`,
                `PARAM_NAME`,
                `PARAM_VALUE`,
                `A_RECORD_CREATE_DATE`,
                `A_RECORD_CREATE_AUTHOR`
            )
            select 
                NOW() as `A_RECORD_REMOVE_DATE`,
                :user as `A_RECORD_REMOVE_AUTHOR`,
                rp.`ID_REF`,
				rp.`PRIMARY_KEY`,
                rp.`REMOTE_PRIMARY_KEY`,
                rp.`PARAM_NAME`,
                rp.`PARAM_VALUE`,
                rp.`A_RECORD_CREATE_DATE`,
                rp.`A_RECORD_CREATE_AUTHOR`
            from `CRM_REF_PARAMS` rp
            where rp.`ID_REF` = :id_ref
                and rp.`PRIMARY_KEY` = :pk
                and rp.`REMOTE_PRIMARY_KEY` = :remote_pk
                and rp.`PARAM_NAME` = :name
        ", [
            ':user' => User::getLogin(),
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
            ':name' => $name,
        ]);
        DB::getPDO()->execSql("
            DELETE FROM `CRM_REF_PARAMS`
            where `ID_REF` = :id_ref
                and `PRIMARY_KEY` = :pk
                and `REMOTE_PRIMARY_KEY` = :remote_pk
                and `PARAM_NAME` = :name
        ", [
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
            ':name' => $name,
        ]);
    }

    static function removeAllParams($idRef, $pk, $remotePk) {
        self::validateBaseArgs($idRef, $pk, $remotePk);
        DB::getPDO()->execSql("
            insert into `CRM_REF_PARAMS__REMOVED_LOG` (
                `A_RECORD_REMOVE_DATE`,
                `A_RECORD_REMOVE_AUTHOR`,
                `ID_REF`,
				`PRIMARY_KEY`,
                `REMOTE_PRIMARY_KEY`,
                `PARAM_NAME`,
                `PARAM_VALUE`,
                `A_RECORD_CREATE_DATE`,
                `A_RECORD_CREATE_AUTHOR`
            )
            select 
                NOW() as `A_RECORD_REMOVE_DATE`,
                :user as `A_RECORD_REMOVE_AUTHOR`,
                rp.`ID_REF`,
				rp.`PRIMARY_KEY`,
                rp.`REMOTE_PRIMARY_KEY`,
                rp.`PARAM_NAME`,
                rp.`PARAM_VALUE`,
                rp.`A_RECORD_CREATE_DATE`,
                rp.`A_RECORD_CREATE_AUTHOR`
            from `CRM_REF_PARAMS` rp
            where rp.`ID_REF` = :id_ref
                and rp.`PRIMARY_KEY` = :pk
                and rp.`REMOTE_PRIMARY_KEY` = :remote_pk
        ", [
            ':user' => User::getLogin(),
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
        ]);
        DB::getPDO()->execSql("
            DELETE FROM `CRM_REF_PARAMS`
            where `ID_REF` = :id_ref
                and `PRIMARY_KEY` = :pk
                and `REMOTE_PRIMARY_KEY` = :remote_pk
        ", [
            ':id_ref' => $idRef,
            ':pk' => $pk,
            ':remote_pk' => $remotePk,
        ]);
    }

    static function validateBaseArgs($idRef, $pk, $remotePk) {
        if ((int)$idRef <= 0) throw new Exception("Wrong ID Ref");
        if ((int)$pk <= 0) throw new Exception("Wrong Primary Key");
        if ((int)$remotePk <= 0) throw new Exception("Wrong Remote Primary Key");
    }

}