finanzen/app/Models/mBills.php

<?php namespace App\Models;

use App\Models\Cast\CastMyDate;
use CodeIgniter\Model;

class mBills extends Model {
    private $defaultTableName='_budget_bills';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;

    protected $returnType = \App\Entities\eBooking::class;

    protected $useSoftDeletes = true;
    protected $deletedField ="deleted_at";

    //TODO timestamps aktivieren
    // protected $createdField  = 'created_at';
    // protected $updatedField  = 'updated_at';
    // protected $deletedField  = 'deleted_at';

    protected $allowedFields = ['datum','receiver','source_id','amount','validate','deleted_at','rechnung'];

    protected $useTimestamps = false;

       // Bind the type to the handler
     protected array $castHandlers = [
          'myDate' => CastMyDate::class,
     ];
    //protected $afterFind = ['checkTransfer'];
    protected array $casts = [
        'id'            => 'int',
        'amount'        => 'float',
        'datum'         => 'myDate[dd.MM.yyyy]',
        'validate'      => 'boolean',
        'source_id'     => 'int',
    ];

    protected function initialize() {
        log_message('debug','mBills initialize');
        $this->table = auth()->user()->preFixDB.$this->defaultTableName;
        $this->checkOrCreateTable();
    }

    public function getTable() : string {
        return $this->table;
    }
    // protected function checkTransfer(array $data){
    //     $data['data']['transfer'] = ((count($this->attributes['details']) == 1) && $accounts->isAccount($this->attributes['details'][0]->account_id));
    // }

    /**
	 * liefert Buchungen für Aktivitätenliste für alte datentabelle
	 * @return mixed  Datensätze für Tabelle
	 */
    public function getEntriesDet(string $start, string $ende, int $source){
        $details = model('App\Models\mBillDetails');
        $quelle = model('App\Models\mAccounts');
        $this->where('datum >=',  date( 'Y-m-d', strtotime($start)));
        $this->where('datum <=',  date( 'Y-m-d', strtotime($ende)));
        // alle Detailsbuchungen wo account id übereinstimmt
        if ($source > 0) {
            $subs = $details->select('bill_id')->where('account_id',$source)->findAll();
//            log_message('error', 'Get Entries Det {msg}', ['msg'=>(string)$this->db->getLastQuery()]);
            if (0 < sizeof($subs)){
                $keys=array();
                foreach ($subs as $value) {
                    $keys[] = intval($value->bill_id);
                }
                $this->groupStart();
                $this->where('source_id',  $source );
                $this->orWhereIn('id',  $keys );
                $this->groupEnd();
    
            } else {
                $this->where('source_id',  $source );
            }
        }

        $result = $this->orderBy('datum','desc')->findAll(); //->where('lkz',null)
        if (0 == sizeof($result)){
            return $result;
        }

        $keys=array();
        foreach ($result as $value) {
            $keys[] = intval($value->id);
        }
        $ergebnis = $details->getDetails($keys);
        
        foreach ($result as $value) {
            $value->details = $ergebnis[$value->id]??null;
            $value->source = $quelle->getDropDownEntry($value->source_id);
//            $value->amount = number_format( $value->amount , 2, ',', '.' );
        }
        return $result;
     }

    /**
	 * liefert Buchungen für Aktivitätenliste
	 * @return mixed  Datensätze für Tabelle
	 */
    public function getActivities(string $start, string $ende, int $source){
        $quelle = model('App\Models\mAccounts');
        $this->where('datum >=',  date( 'Y-m-d', strtotime($start)));
        $this->where('datum <=',  date( 'Y-m-d', strtotime($ende)));
        if ($source > 0) {
            $this->where('source_id',  $source );
        }
        $result = $this->orderBy('datum','desc')->findAll();
        if (0 == sizeof($result)) return $result;
        foreach ($result as $value) {
            $value->source = $quelle->getDropDownEntry($value->source_id);
        }
        return $result;
        
    }
     /**
	 * liefert Buchungen für Übersichtstabelle
	 * @return mixed  Datensätze für Tabelle
	 */
    public function getEntries(int $zeilen = 10){
        $result = $this->orderBy('datum','desc')->where('validate',false)->findAll($zeilen); //->where('lkz',null)
        if (0 == sizeof($result)) return $result;
        return $result;
    }
    
    public function getToDos(){
        $result = $this->orderBy('datum','desc')->where('validate',true)->findAll();
        return $result;
    }

    public function getAccountsBalance(){
        $sums = $this->db->query("SELECT * FROM finanzen.getbalances('".auth()->user()->preFixDB."'::text,ARRAY[]::integer[])"); // mit datensyntax ARRAY[210,216]
 //       log_message('debug', $this->db->getLastQuery());
//        log_message('debug', print_r($sums->getResultArray(), true));
        return $sums->getResultArray();
     }

     public function storeBillFile(int $billId, string $filename){
        $this->update($billId, [ 'rechnung' => $filename ]);
     }
     public function deleteBillFile(int $billId){
        $this->set('rechnung', 'NULL',false);
        $this->update($billId);
     }

    /**
	 * löscht gegebene Buchung inkl. Details
     * @param int id der Buchung
	 * @return mixed  Datenstruktur für Formular
	 */
    public function deleteEntry(int $id) : bool {
        $result = true;
        $details = model('App\Models\mBillDetails');
        if ($id > 0){
            $result = $details->where('bill_id',$id)->delete();
            if ($result === true)
                $result = $this->delete($id);
        }
        return $result;
     }

    public function checkOrCreateTable(){
        log_message('debug',"Check or Create Bill Table");
        if ($this->db->tableExists($this->table)) return;
        log_message('debug','Create Bills Table '.$this->db->lastQuery);
        log_message('debug',"Create Bills Table ".$this->table);
        $accounts = model('App\Models\mAccounts'); /* @var \App\Models\mAccounts $accounts */
        $forge = \Config\Database::forge();
        $fields = array(
            'id' => array( 'type' => 'serial'),
            'datum' => array( 'type' => 'date'),
            'receiver' => array( 'type' => 'character varying','constraint' => '120'),
            'source_id' => array( 'type' => 'integer' ),
            'amount' => array( 'type' => 'numeric','constraint' => '9,2' ),
            'validate' => array( 'type' => 'boolean' ),
            'rechnung' => array( 'type' => 'character varying','constraint' => '50','null'=>true),
            'deleted_at'=> array( 'type' => 'timestamp without time zone','null'=>true),
                );
        $forge->addField( $fields );
        $forge->addPrimaryKey('id');
        $forge->addForeignKey('source_id', $accounts->getTable(), 'id','NO ACTION','NO ACTION','source_id_fk');
     //   $this->db->query("CREATE TABLE $this->table (LIKE $this->defaultTableName INCLUDING DEFAULTS INCLUDING CONSTRAINTS) PARTITION BY RANGE (datum);");
        $forge->createTable($this->table,true,['PARTITION BY RANGE'=>'(datum)']);
        log_message('debug','Create Bills Table '.$this->db->lastQuery);
    //     CREATE TABLE temperatures_y2025q1 PARTITION OF temperatures
    // FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
     }
     

};
?>