se/SE/se-lib/Schema/DefaultDb/zaliczka/ZaliczkaStorageAcl.php

<?php

Lib::loadClass('Core_AclSimpleSchemaBase');// extends Core_AclBase
Lib::loadClass('Core_AclHelper');

class Schema_DefaultDb_zaliczka_ZaliczkaStorageAcl extends Core_AclSimpleSchemaBase {

  public $_simpleSchema = [
    'root' => [
      '@namespace' => 'default_db/ZALICZKA/Zaliczka',// Api_WfsNs::getBaseWfsUri() . '/default_db/Zaliczka'
      '@primaryKey' => 'id',
      'id' => [ '@type' => 'xsd:integer', '@alias' => 'ID' ],
      'created' => [ '@type' => 'xsd:date', '@alias' => 'A_RECORD_CREATE_DATE' ],
      'worker' => [ '@ref' => 'default_objects/AccessOwner' ],// 'alias_ref:default_objects:AccessOwner'
      'kwota' => [ '@type' => 'xsd:decimal', '@totalDigits' => 16, '@fractionDigits' => 2, '@alias' => 'KWOTA' ],
      'nierozliczona_kwota' => [ '@type' => 'xsd:decimal', '@totalDigits' => 16, '@fractionDigits' => 2, '@alias' => 'NIEROZLICZONA_KWOTA' ],
      'pozycja' => [ '@ref' => 'default_db/ZALICZKA_POZYCJA/ZaliczkaPozycja', '@maxOccurs' => 'unbounded' ]
    ],
  ];

  public function getTotal($params = array()) {
    if (!isset($params['#refFrom'])) {
      throw new Exception("Missing param #refFrom/primaryKey (id user)");
    }
    $idUser = V::get('primaryKey', 0, $params['#refFrom'], 'int');
    return DB::getPDO()->fetchValue("
      select count(*) as cnt
      from ZALICZKA z
        join ADMIN_USERS u on(z.L_APPOITMENT_USER = u.ADM_ACCOUNT and u.ID = {$idUser})
    ");
  }

  public function getItems($params = array()) {
    // '#refFrom' => [
    //   'namespace' => 'default_db__x3A__ADMIN_USERS/Worker',
    //   'primaryKey' => $idUser
    // ]
    if (!isset($params['#refFrom'])) {
      throw new Exception("Missing param #refFrom/primaryKey (id user)");
    }
    $idUser = V::get('primaryKey', 0, $params['#refFrom'], 'int');
    return DB::getPDO()->fetchAll("
      select z.*
      from ZALICZKA z
        join ADMIN_USERS u on(z.L_APPOITMENT_USER = u.ADM_ACCOUNT and u.ID = {$idUser})
      order by z.ID DESC
    ");
  }

  public function addItem($itemTodo) {
    // TODO: btn "Dodaj Zaliczkę" $acl->addItem($item); // @require only 'worker' => [ 'id', 'login', 'name' ]
    $DBG = V::get('DBG', '', $_GET);
    if($DBG){DBG::nicePrint($itemTodo, '$itemTodo');}

    if (!is_array($itemTodo) || empty($itemTodo['worker'])) throw new Exception("Missing worker");
    if (empty($itemTodo['worker']['id'])) throw new Exception("Missing worker id");
    $idUser = intval($itemTodo['worker']['id']);
    if ($idUser <= 0) throw new Exception("Wrong worker id");

    // TODO: use default_objects/AccessOwner -> getItem($idUser)
    $userLogin = DB::getPDO()->fetchValue("
      select u.ADM_ACCOUNT
      from `ADMIN_USERS` u
      where u.ID = {$idUser}
    ");
    if (empty($userLogin)) throw new Exception("User not exists ID={$idUser}");
    if($DBG){DBG::nicePrint($userLogin, '$userLogin');}

    $idTransaction = Core_AclHelper::startTransaction('ZALICZKA', $idUser, $userLogin);

    $zaliczkaRefWorker = ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'worker');
    $idActiveZaliczka = DB::getPDO()->fetchValue("
      select MAX(t.ID)
      from `ZALICZKA` t
        -- join `ADMIN_USERS` u on(t.L_APPOITMENT_USER = u.ADM_ACCOUNT)
        left join `{$zaliczkaRefWorker}` ref on(ref.PRIMARY_KEY = t.ID and ref.A_STATUS != 'DELETED')
        left join `ADMIN_USERS` r on(r.ID = ref.REMOTE_PRIMARY_KEY)
      where r.ID = {$idUser}
        and t.A_STATUS in('WAITING', 'NORMAL') -- TODO cache use #INSTANCE table
    ");
    if($DBG){DBG::nicePrint("
      select MAX(t.ID)
      from `ZALICZKA` t
        -- join `ADMIN_USERS` u on(t.L_APPOITMENT_USER = u.ADM_ACCOUNT)
        left join `{$zaliczkaRefWorker}` ref on(ref.PRIMARY_KEY = t.ID and ref.A_STATUS != 'DELETED')
        left join `ADMIN_USERS` r on(r.ID = ref.REMOTE_PRIMARY_KEY)
      where r.ID = {$idUser}
        and t.A_STATUS in('WAITING', 'NORMAL') -- TODO cache use #INSTANCE table
    ", 'sql');}
    if($DBG){DBG::nicePrint($idActiveZaliczka, '$idActiveZaliczka');}

    $userSaldo = 0;
    if ($idActiveZaliczka > 0) {
      $zaliczkaRefPozycja = ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'pozycja');
      $userSaldo = DB::getPDO()->fetchValue("
    		select t.KWOTA + t.NIEROZLICZONA_KWOTA - sum(r.KWOTA)
    		from `ZALICZKA` t
    			left join `{$zaliczkaRefPozycja}` ref on(ref.PRIMARY_KEY = t.ID and ref.A_STATUS != 'DELETED')
    			left join `ZALICZKA_POZYCJA` r on(r.ID = ref.REMOTE_PRIMARY_KEY)
        where t.ID = {$idActiveZaliczka}
    	");
      if($DBG){DBG::nicePrint($userSaldo, '$userSaldo');}
    }

    $usrLogin = User::getLogin();
    $execRet = DB::getPDO()->exec("
      insert into `ZALICZKA` (A_RECORD_CREATE_AUTHOR, A_RECORD_CREATE_DATE
          , L_APPOITMENT_USER, NIEROZLICZONA_KWOTA)
        select '{$usrLogin}' as A_RECORD_CREATE_AUTHOR, NOW() as A_RECORD_CREATE_DATE
          , u.ADM_ACCOUNT, '{$userSaldo}'
        from ADMIN_USERS u
        where u.ID = {$idUser}
    ");
    if($DBG){DBG::nicePrint($execRet, '$execRet');}
    $id = DB::getPDO()->lastInsertId();
    if($DBG){DBG::nicePrint($id, '$id');}
    if (!$id) throw new Exception("Wystąpiły błędy podczas dodawania Zaliczki do bazy danych");

    DB::getPDO()->exec("
      insert into `ZALICZKA__#INSTANCE` (PRIMARY_KEY, INSTANCE_NAME)
        values({$id}, 'ZaliczkaAktywna')
      ON DUPLICATE KEY UPDATE INSTANCE_NAME = 'ZaliczkaAktywna'
    ");
    foreach (DB::getPDO()->fetchAll("
      select z.ID as ID_ZALICZKA, i.*
      from `ZALICZKA` z
        left join `ZALICZKA__#INSTANCE` i on(i.PRIMARY_KEY = z.ID)
      where z.ID != {$id}
        and z.L_APPOITMENT_USER = '{$userLogin}'
    ") as $instance) {
      if($DBG){DBG::nicePrint($instance, '$instance');}

      // FIX Instance name
      foreach (DB::getPDO()->fetchAll("
        select z.ID as ID_ZALICZKA
  				, IF(i.PRIMARY_KEY IS NOT NULL, 1, 0) as has_instance
  				, i.*
        from `ZALICZKA` z
          left join `ZALICZKA__#INSTANCE` i on(i.PRIMARY_KEY = z.ID)
        where z.ID != {$id}
          and z.L_APPOITMENT_USER = '{$userLogin}'
      ") as $instance) {
        if($DBG){DBG::nicePrint($instance, '$instance');}
  			if (!$instance['has_instance']) {
          DB::getPDO()->exec("
    				insert into `ZALICZKA__#INSTANCE_HIST` (PRIMARY_KEY, INSTANCE_NAME)
    					values ({$instance['ID_ZALICZKA']}, 'ZaliczkaArchiwalna')
    			");
  				DB::getPDO()->exec("
  					insert into `ZALICZKA__#INSTANCE` (PRIMARY_KEY, INSTANCE_NAME)
  						values ({$instance['ID_ZALICZKA']}, 'ZaliczkaArchiwalna')
  				");
  			} else if ('ZaliczkaAktywna' == $instance['INSTANCE_NAME']) {
          DB::getPDO()->exec("
    				insert into `ZALICZKA__#INSTANCE_HIST` (PRIMARY_KEY, INSTANCE_NAME)
    					values ({$instance['ID_ZALICZKA']}, 'ZaliczkaArchiwalna')
    			");
  				DB::getPDO()->exec("
  					update `ZALICZKA__#INSTANCE`
  					set INSTANCE_NAME = 'ZaliczkaArchiwalna'
  						, A_RECORD_UPDATE_DATE = NOW()
  					where PRIMARY_KEY = {$instance['ID_ZALICZKA']}
  				");
  			}
      }
    }

    $this->insertRef('worker', $id, $idUser);

    DB::getPDO()->exec("
      update ZALICZKA
      set A_STATUS = 'OFF_HARD'
      where L_APPOITMENT_USER = '{$userLogin}'
        and ID < {$id}
    ");

    // TODO: legacy fill table ZALICZKA_HIST
    DB::getPDO()->exec("
      insert into `ZALICZKA_HIST` (ID_USERS2, L_APPOITMENT_USER, NIEROZLICZONA_KWOTA)
        select z.ID as ID_USERS2, z.L_APPOITMENT_USER, z.NIEROZLICZONA_KWOTA
        from ZALICZKA z
        where z.ID = {$id}
    ");
    return $id;
  }

  public function getItem($primaryKey, $params = []) {
    $zaliczkaRaw = DB::getPDO()->fetchAll("
      select t.*
      from `ZALICZKA` t
      where t.ID = {$primaryKey}
    ");
    $zaliczkaRaw = (!empty($zaliczkaRaw)) ? reset($zaliczkaRaw) : null;
    // DBG::nicePrint($zaliczkaRaw, '$zaliczkaRaw');
    if (!$zaliczkaRaw) return null;
    $filterCols = $this->getParamCols($params);
    return $this->buildFromSqlRow($zaliczkaRaw, $params);
  }

  public function reinstall() {
    // TODO: mv to Core_AclSimpleSchemaBase and reainstall by _simpleSchema - read current data from `information_schema`
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `ZALICZKA` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`A_RECORD_CREATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_CREATE_AUTHOR` varchar(20)  DEFAULT NULL,
				`A_RECORD_UPDATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				`A_STATUS` enum('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED') DEFAULT 'WAITING',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT '',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT '',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT '',
				`KWOTA` decimal(16,2) NOT NULL DEFAULT 0,
				`NIEROZLICZONA_KWOTA` decimal(16,2) NOT NULL DEFAULT 0,
				PRIMARY KEY (`ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `ZALICZKA_HIST` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_USERS2` int(11) NOT NULL,
				`A_RECORD_CREATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_STATUS` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT 'N/S;',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT 'N/S;',
				`KWOTA` varchar(16) NOT NULL DEFAULT 'N/S;',
				`NIEROZLICZONA_KWOTA` varchar(16) NOT NULL DEFAULT 'N/S;',
				PRIMARY KEY (`ID`),
			  KEY `ID_USERS2` (`ID_USERS2`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `ZALICZKA_POZYCJA` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`A_RECORD_CREATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_CREATE_AUTHOR` varchar(20)  DEFAULT NULL,
				`A_RECORD_UPDATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				`A_STATUS` enum('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED') DEFAULT NULL,
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT '',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT '',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT '',
				`KWOTA` decimal(16,2) NOT NULL DEFAULT 0,
				PRIMARY KEY (`ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `ZALICZKA_POZYCJA_HIST` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_USERS2` int(11) NOT NULL,
				`A_RECORD_CREATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_STATUS` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT 'N/S;',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT 'N/S;',
				`KWOTA` varchar(16) NOT NULL DEFAULT 'N/S;',
				PRIMARY KEY (`ID`),
			  KEY `ID_USERS2` (`ID_USERS2`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");

    ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'worker');
    ACL::getRefTable('default_db/ZALICZKA/Zaliczka', 'pozycja');
    ACL::getRefTable('default_db/ZALICZKA_POZYCJA/ZaliczkaPozycja', 'projekt');
    ACL::getRefTable('default_db/ZALICZKA_POZYCJA/ZaliczkaPozycja', 'korespondencja');

    Core_AclHelper::getInstanceTable('ZALICZKA');// `ZALICZKA__#INSTANCE`
    // TODO: ZaliczkaOczekujaca   A_STATUS = WAITING  -- dodane przez pracownika
    // TODO: ZaliczkaAktywna      A_STATUS = NORMAL   -- zakceptowane / wyplacone przez ...
      // TODO: pole na dane wypłacającego zaliczkę
      // TODO: dopiero teraz wyliczyć saldo / nierozliczona_kwota? Saldo pokazywać zawsze, aktualizować po akceptacji (Oczekujaca --> Aktywna)
    // TODO: ZaliczkaArchiwalna   A_STATUS = OFF_HARD -- poprzednie zaliczki
    // TODO: ZaliczkaUsunieta     A_STATUS = DELETED  -- usunieta przez workera lub anulowana przez osobę uprawnioną

    Core_AclHelper::getTransactionTable('ZALICZKA');// `ZALICZKA__#TRANSACTION`
    Core_AclHelper::getTransactionTable('ZALICZKA_POZYCJA');// `ZALICZKA_POZYCJA__#TRANSACTION`

    foreach ($this->_simpleSchema['root'] as $childName => $schema) {
      if ('@' == substr($childName, 0, 1)) continue;
      Core_AclHelper::getChildHistTable('ZALICZKA', $childName, $schema);
      DBG::nicePrint($schema, '$childName('.$childName.')');// TODO: DBG
    }
	}

}