PHP File Upload - Custom Upload - problem with dbClean and with editor display of image

raphiw90

Hi everyone

I am building an expense tracker with an editor instance where a user can enter information like date, description, amount, currency and via an upload field the user can directly upload a photo of the receipt. I have used the custom upload so that I can manipulate the image and reduce the file size. That all works smoothly but I have two issues:

1) the dbClean function is not working and it seems that the "system_path" cannot be read

Error:

<br/>
<b>Notice</b>
:  Undefined index: system_path in 
<b>/app/assets/php/table.expenses.php</b>
 on line 
<b>103</b>
<br/>
<br/>
<b>Warning</b>
:  unlink() [
<a href='https://secure.php.net/manual/en/function.unlink.php'>function.unlink.php</a>
]: No such file or directory in 
<b>app/assets/php/table.expenses.php</b>
 on line 
<b>103</b>
<br/>

{"data":[]}

2) the little thumbnail of the uploaded file in the editor instance called via "display": function (id) { return '<img src="' + editor.file('receipts', id ).web_path + '"/>'; also returns undefined (respectively the ? symbol).

I don't know exactly how to approach it so that I can get the right data, any ideas? Below the JS and PHP code -> sorry can't give access to a live try & error page or reproduce the error in a test case...

Thanks a lot for your time & help!
Raphael

(function ($) {

  $(document).ready(function () {

    var editor = new $.fn.dataTable.Editor({
      ajax: 'assets/php/table.expenses.php',
      table: '#expenses',
      fields: [{
          "label": "Guide:",
          "name": "expenses.guide_id",
          "type": "select",
          "placeholder": "Select a Guide"
        },
        {
          "type": "checkbox",
          "label": "Income?",
          "name": "expenses.income",
          "options": [{
            label: "",
            value: 1,
          }],
          "sparator": '',
          "unselectedValue": 0,
          "attr": {
            disabled: true
          }
        },
        {
          "label": "Date of the expense:",
          "name": "expenses.date",
          "type": "datetime",
          "format": "DD\/MM\/YY"
        },
        {
          "label": "Description:",
          "name": "expenses.description"
        },
        {
          "label": "Amount:",
          "name": "expenses.amount",
          "fieldInfo": "Important: use . (not ,) for amounts with cents e.g. 1.15 CHF",
          "attr": {
            type: 'number'
          }
        },
        {
          "label": "Currency",
          "name": "expenses.currency",
          "type": "select",
          "placeholder": "Select a type",
          "options": [{
              label: "EUR",
              value: "EUR"
            },
            {
              label: "CHF",
              value: "CHF"
            },
          ]
        },
        {
          "label": "Receipt:",
          "name": "expenses.receipt",
          "type": "upload",
          "display": function (id) {
            return '<img src="' + editor.file('receipts', id ).web_path + '"/>';
          },
          "clearText": "Clear",
          "noImageText": 'No receipt scanned!'
        },
      ]
    });

    var table = $('#expenses').DataTable({
      dom: 'Blfrtip',
      ajax: 'assets/php/table.expenses.php',
      columns: [{
          "data": null,
          "render": function (data, type, row) {
            return data.accounts.first_name + ' ' + data.accounts.last_name
          }
        },
        {
          "data": "expenses.date"
        },
        {
          "data": "expenses.description"
        },
        {
          "data": null,
          "render": function (data, type, row) {
            if (data.expenses.income == 0) {
              pref = "-"
            } else {
              pref = "+"
            }
            return pref + ' ' + data.expenses.amount + ' ' + data.expenses.currency
          }
        },
        {
          "data": "receipts.web_path",
            defaultContent: "No receipt scanned!"
        },
      ],
      select: true,
      colReorder: true,
      buttons: [{
          extend: 'create',
          editor: editor
        },
        {
          extend: 'edit',
          editor: editor
        },
        {
          extend: 'remove',
          editor: editor
        },
        {
          extend: 'collection',
          text: 'Export',
          buttons: [
            'copy',
            'excel',
            'csv',
            'pdf',
            'print'
          ]
        }
      ],
    });
    //Reloads the "Wallet" table automatically when a change is made.
    editor.on('submitSuccess', function (e, json, data, action) {
      $('#wallet').DataTable().ajax.url('assets/php/table.wallet.php').load();
    });

    $("tbody", "#expenses").on("click", "td:eq(4)",
      function () {
        window.open(this.innerText);
      });
  });

}(jQuery));

<?php
session_start();

 // Create an array to hold variables to be used in the closure function.
  $varArray = [
  "id" => $_SESSION[ 'id' ], //includes Guide ID to backtrack changes
  "username" => $_SESSION[ 'username' ], //includes Username to rename files
];

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Editor Classes
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Editor Instance
Editor::inst( $db, 'expenses', 'id' )
    ->fields(
        Field::inst( 'expenses.guide_id' )
            ->options( Options::inst()
                ->table( 'accounts' )
                ->value( 'id' )
                ->label( 'username' )
                     )
            ->validator( Validate::dbValues() )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'accounts.username' ),
        Field::inst( 'accounts.first_name' ),
        Field::inst( 'accounts.last_name' ),
        Field::inst( 'expenses.date' )
            ->validator( Validate::dateFormat( 'd/m/y' ) )
            ->getFormatter( Format::datetime( 'Y-m-d', 'd/m/y' ) )
            ->setFormatter( Format::datetime( 'd/m/y', 'Y-m-d' ) )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'expenses.income' )
            ->validator (Validate::notEmpty())
            ->setFormatter( Format::implode(",")),
        Field::inst ('expenses.description')
            ->validator( Validate::notEmpty() ),
        Field::inst( 'expenses.amount' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'expenses.currency' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'expenses.receipt' )
            
            ->setFormatter( Format::ifEmpty( "" ) )
 
                ->upload(
                  Upload::inst( function ( $file, $id )use( $varArray, $db ) {
                    // Cuts the image info into pieces for further manipulation
                    $receipt = $varArray[ "username" ] . "_" . $file[ 'name' ];
                    $tmpname = $file[ 'tmp_name' ];
                    $filename = pathinfo( $receipt, PATHINFO_FILENAME );
                    $extension = pathinfo( $receipt, PATHINFO_EXTENSION );
                    $filename_orig = $filename;
                    
                    // Checks if the file already exsits and if so adds an increment at the end
                    $num = 1;
                    while ( file_exists( $_SERVER[ 'DOCUMENT_ROOT' ] . '/app/assets/images/receipts/' . $receipt ) ) {
                      $filename = ( string )$filename_orig . $num;
                      $receipt = $filename . "." . $extension;
                      $num++;
                    }
                    $target = $_SERVER[ 'DOCUMENT_ROOT' ] . '/app/assets/images/receipts/' . $receipt;

                    //Imagick coverts image and reduces filesize
                    $compreceipt = new Imagick( $file[ 'tmp_name' ] );
                    $compreceipt->setImageFormat( $extension );
                    $compreceipt->resizeImage( 800, 500, Imagick::FILTER_LANCZOS, 1, true );
                    $compreceipt->setImageCompressionQuality( 50 );
                    $compreceipt->writeImage( $target );
                    
                    //Update Database with new info
                    $db->update(
                      'receipts', [
                        "filename" => $receipt,
                        "web_path" => '/app/assets/images/receipts/' . $receipt,
                        "system_path" => $target,
                      ], [ "id" => $id ]


                    );
                    return $id;
                      
                  } )
                    ->db( 'receipts', 'id', array(
                    'guide_id' => $varArray[ "id" ],
                  ) )
                    
                    ->dbClean( function ( $data ) use ($db) {
                // Remove the files from the file system
                for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
                    unlink( $data[$i]['system_path'] );
                }
 
                // Have Editor remove the rows from the database
                return true;
            } )
                ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
                ),
    
    
            Field::inst( 'receipts.web_path' )
    )
    ->leftJoin( 'accounts',     'accounts.id',                  '=', 'expenses.guide_id' )
    ->leftJoin( 'receipts',     'receipts.id',                  '=', 'expenses.receipt' )
    ->process( $_POST )
    ->json();

allan

Hi,

1) You don't have system_path in your ->db(...) call for the Upload instance, so it won't be automatically populated into the data array. print_r($data); there should confirm this.

Either you need to add it into that array, or query the database for the system path information for the files to be deleted.

2) Likewise, web_path isn't in the ->db(...) call so that information isn't populated and available on the client-side. Adding it in should resolve that error as well.

Allan

raphiw90

Thanks Allan, I'll have a look!