Custom sub grid table inline cell editing page

[cwesthuizen]

Good day all
I am really hoping someone can give me guidance, or a quote, to what I am planning to do.

Please see the three images attached.

I need to split up a transactions sub table into income and expenses and give totals, and outstanding fees calculated like in the sheet image I attached.
I need to choose the month then the records for that month needs to appear according to the sheet image, then to be edited directly on that page.
If the current month is opened it should show records already added, and extra records for each transaction type not already added, then one needs to be able to just click or tab on the cells and add or change the values like one do in excel, and the date automatically updated if not manually changed. When done one should be able to click on save to submit all changes. If a record locked field is locked one should not be able to edit the record. If month end button is pressed then no records <= lock month date should be able to be added, deleted or edited.
I really want to do this myself but don't have any experience with AppGini accept for what I did by auto generation. Where do I start?
Anyone willing to guide me. I promise to pay it forward, like I always do
Thanks a lot, Chris

[pbottcher]

Hi Chris,

maybe this is not the answer you are looking for, but with the standard AppGini features you will not be able to acheive this, as AppGini has no grid editing functions and no multi row editing.

[cwesthuizen]

Hallo pböttcher. Thank you. I realize that, thus have to improvise. AppGini saved me hours and hours of development though. Thank you AppGini.
I will have to manually create a tab net to the transactions tab, and implement my own code in it. That I believe I will accomplish with a hook. I have a lot to learn I'm taking my first baby steps hehehehe. I am now going to search for a grid I can manipulate and add, and then figure out how to add that extra tab, and making use of AppGini's functions to query the db etc. Keep your thumbs.

[cwesthuizen]

I'm starting here https://bigprof.com/appgini/help/advanc ... cess-pages

[pbottcher]

Hi,

are the transactions that you show as excel-sheet related to the property from the 1_Property_Page1.png?
Do you have a query that would output all the data in the excel-sheet needed?

[cwesthuizen]

Hi pböttcher
Excuse for replying 2 months later
Yes, related to the property from the 1_Property_Page1.png
I can write a sql query that can return a record for each transaction type, that will include transaction values for the specific month already saved as well as records that does not exist yet that is consistent with the layout example. see last image with the yellow as example. Then one should be able to add or edit values in grid and press tab for next cell on right and then after last yellow cell tab to next record for adding or updating value.
When adding a value of a cell it should not be permanent yet, until save is pressed. Then the code should see if a record actually existed in the transactions table to update it, or to add the transaction record according to the type, date and value. If a record for the yellow ones does not exist and the Amount is null or 0 that record should not be added to the transactions table. If a record is marked as locked, only the comments field should be editable, but none of the other fields for that record. As one make updates the green calculation fields should automatically be calculated.
If one click on Reload button any changes should be discarded. If one click on +ADD NEW and the month is not locked for editing yet, one should pick income or expense, then choose a type and that record should be added to the grid/table in the view and once the SAVE button is clicked that transaction should be written to the transactions table along with the other yellow records that got value for the first time, and existing records where values was changed should be updated it that record is not locked.
I hope I explained properly

[cwesthuizen]

I cannot believe that there are not enough users who want this feature to have an editable grid/datagrid. This one project of mine is halted now for 2 years already. I hope you will develop a grid plugin/component in the near future. In the meantime I am using other software to develop my apps.

[Alisson]

Hi Chris,

Have a look at this:

http://tabulator.info/examples/4.9

With a bit of coding you can make this work with appgini using the custom pages option, is a fantastic javascript library.

Maybe this can attract more attention of other users, and together we (appgini users) can work to make this library more useful.

[cwesthuizen]

Hi Alisson. Thank you very much for this. I will definitely look at it when I have another project needed this capability.

[xbox2007]

hello

Dear Alisson ,

Can you share your code about (http://tabulator.info/examples/4.9)

Have a look at this:

http://tabulator.info/examples/4.9

With a bit of coding you can make this work with appgini using the custom pages option, is a fantastic javascript library.

Maybe this can attract more attention of other users, and together we (appgini users) can work to make this library more useful.

thanks a lot

[Alisson]

This is what I use:

Code: Select all

<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet">
  <script type="text/javascript" src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<?php
    define('PREPEND_PATH', '../');
    $hooks_dir = dirname(__FILE__);
    include("$hooks_dir/../lib.php");
    include("$hooks_dir/../header.php");
     
    /* grant access to all logged users */
    $mi = getMemberInfo();
    
    if(!$mi['username'] || $mi['username'] == 'guest'){
        echo "Access denied";
        exit;
    }

    $query = sql("SELECT * FROM `yourTableNameHere`", $eo); //Change this line to use your desired table
    
    $json_array = array();  
        while($row = mysqli_fetch_assoc($query))  
        {  
            $json_array[] = $row;
        }  
    $json = json_encode($json_array, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);
?>

<div id="table"></div>
<script>
  var tabledata = <?php echo $json;?>;

  var table = new Tabulator("#table", {
      data:tabledata,           //load row data from array
      layout:"fitColumns",      //fit columns to width of table
      responsiveLayout:"hide",  //hide columns that dont fit on the table
      pagination:"local",       //paginate the data
      paginationSize:7,         //allow 7 rows per page of data
      movableColumns:true,      //allow column order to be changed
      resizableRows:true,       //allow row order to be changed
      // groupBy:"dept",       //allow row order to be changed
      autoColumns:true, //create columns from data field names
  });

</script>

[xbox2007]

WOW
very nice , thank you very much

[dge]

Hi Alisson
I was excited to try this, I made a file with this code, and entered a valid table name. The page renders and puts up the appgini navigation etc. but there is no table rendered, just a blank screen with the nav bar on top. It's the correct table name (I tried intentionally putting incorrect name and it throws an error).
- what folder/directory should the file be in (appgini root, or elsewhere?)
- this should be a .php file right?
Any idea why no Tabulator output is showing, based on the above code you posted?
Thanks, any help is appreciated!

[Alisson]

Hi dge, The code it's working ok, just test it again.

Make sure the file that you created is inside the hooks folder "/hooks/yourfile.php"

Try this code first to make sure you're getting the results from the database as json:

Code: Select all

<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet">
  <script type="text/javascript" src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<?php
    define('PREPEND_PATH', '../');
    $hooks_dir = dirname(__FILE__);
    include("$hooks_dir/../lib.php");
    include("$hooks_dir/../header.php");
     
    /* grant access to all logged users */
    $mi = getMemberInfo();
    
    if(!$mi['username'] || $mi['username'] == 'guest'){
        echo "Access denied";
        exit;
    }

    $query = sql("SELECT * FROM `yourTableNameHere`", $eo); //Change this line to use your desired table
    
    $json_array = array();  
        while($row = mysqli_fetch_assoc($query))  
        {  
            $json_array[] = $row;
        }  
    echo json_encode($json_array, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);
?>

If you get a json result from this the php part of the code is correct, than we can check the tabulator part.
If you're still getting an error, debug your page and show the results here so I can try to help you.

[dge]

Thanks. I tried your json-test and there is no json output. I put the php file into hooks, updated the below line to a valid table, changed nothing else:

$query = sql("SELECT * FROM `clients`", $eo);

It outputs the appgini nav header and the background colour but nothing else, just a blank page.

Appgini version is 5.97.

What further debugging can I do?

[Alisson]

Hi dge,

Try the appgini code to create a custom page from their website:

https://bigprof.com/appgini/help/advanc ... cess-pages

Code: Select all

<?php
    define('PREPEND_PATH', '../');
    $hooks_dir = dirname(__FILE__);
    include("$hooks_dir/../defaultLang.php");
    include("$hooks_dir/../language.php");
    include("$hooks_dir/../lib.php");
 
    include_once("$hooks_dir/../header.php");
 
    /* grant access to all logged users */
    $mi = getMemberInfo();
    if(!$mi['username'] || $mi['username'] == 'guest'){
        echo "Access denied";
        exit;
    }
 
    echo "You can access this page!";
 
    include_once("$hooks_dir/../footer.php");
?>

you should get a "you can access this page! text response.

[dge]

Thanks Alisson
It turns our the main problem was actually field encoding. Some of our fields had invalid/malformed UTF-8 data.

So instead of:
$json_array[] = $row;

we put:
$json_array[] = mb_convert_encoding($row, 'UTF-8', 'UTF-8');

Then the Tabulator table would actually load!

Tabulator seems pretty nice ...