fields.ini custom sql causes error after save[solved]

A place for users and developers of the Xataface to discuss and receive support.

fields.ini custom sql causes error after save[solved]

Postby idoxlr8 » Fri Sep 07, 2012 9:37 am

Version: 1.3.2 3355
Invoicing application many fields
Sometimes customers make a partial payment and i need to see these records.
In my tables/invoice/fields.ini the top line is:
Code: Select all
__sql__="SELECT * FROM `invoice` WHERE `total` > `payment1` ORDER BY `id`";


This works great but, when I edit a record from here I get an error after the save:
Fatal error: Call to a member function hasJoinTable() on a non-object in C:\xampp\users\hvac\xataface\Dataface\FormTool.php on line 930

I also have some links on the left that let me view in different ways.
Code: Select all
echo '<p><a href="index.php?-table=invoice&payment1=>0">Paid Invoices</a></p>';

When using one of the links, and save, it saves without the error.

Any help resolving this error would be appreciated... Thanks
Tim
Last edited by idoxlr8 on Wed Sep 12, 2012 4:46 pm, edited 1 time in total.
idoxlr8
 
Posts: 4
Joined: Fri Sep 07, 2012 7:15 am

Re: fields.ini custom sql causes error after save

Postby shannah » Sat Sep 08, 2012 10:45 am

You shouldn't have a where clause in your tabe's __sql__ directive. This should produce the exact same rows as the select * from mytable query. It should just graft extra columns onto the end of it.

If you want to have a default filter, better to add a security filter, or add a default search in the beforeHandleRequest method of the application delegate class.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: fields.ini custom sql causes error after save

Postby idoxlr8 » Sun Sep 09, 2012 8:58 am

Thanks for the reply.
I must be missing something here.
Perhaps I didn't explain the desired effect.
I need to be able to see partial payments in the list view.
I have links in the fields so that I can pay these invoices and print the receipt.
I have to be able to view only the records WHERE columA IS larger than columB
Is there a way I can achieve this without using the WHERE clause.
I almost have the site complete except for this little snag...

Perhaps a link to an example...

Thanks again for your help.
Tim
idoxlr8
 
Posts: 4
Joined: Fri Sep 07, 2012 7:15 am

Re: fields.ini custom sql causes error after save

Postby shannah » Sun Sep 09, 2012 9:45 am

The way I would do it:
Code: Select all
__sql__="SELECT
    i.*,
    if(i.total>i.payment1,1,0) as is_balance_owing
   FROM `invoice` i";


Then in the beforeHandleRequest() method of the application delegate class:
Code: Select all
function beforeHandleRequest(){
    $app = Dataface_Application::getInstance();
    $query =& $app->getQuery();
    if ( !$_POST and $query['-table'] == 'invoice' ){
        if (  !isset($query['is_balance_owing']) ){
            $query['is_balance_owing'] = '=1';
        }
        if ( !@$query['-sort'] ){
            $query['-sort'] = 'id';
        }
    }


This adds a default search, but people can still override the filter. If you don't want people to be able to override the filter with their own query, then you should use a security filter instead.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: fields.ini custom sql causes error after save

Postby idoxlr8 » Sun Sep 09, 2012 4:37 pm

Awesome Steve!
Moving version 2 to Xataface is the best thing I have found in a while.
I really appreciate your help.
I really hate to ask but one more small prob that I have run into.

I have tried almost everything and at my whits end here...
Invoice info is kept in one table and for tax purposes I keep payment info in another table.
When I click "pay" I am taken to a form from the payments table.
When I save the record I need to also update the paymentdate and paymentamount fields from the invoice table.

I know that I need a join and have worked it out in phpMyadmin but implementing it is a whole nuther can of worms.

Thanks in advance

Tim
idoxlr8
 
Posts: 4
Joined: Fri Sep 07, 2012 7:15 am

Re: fields.ini custom sql causes error after save

Postby idoxlr8 » Tue Sep 11, 2012 4:14 pm

Thanks Steve for your help...
seems that 1.3.2 3355 does not support updating multiple tables during the save.
So... let's php this problem and be done with it...
So here's the the method via php and sql...
I have a table named payments, tables/payments/payments.php
There are records in the invoice record that need to be updated at the same time...
I need to be able to make a payment without having to view all "related CRAP"
Just want to make a payment and update fields in two tables... simple nuff...here ya go
tables/payments/payments.php - be sure to clean out your templates_c folder... This set me back a whole day...
Code: Select all
<?php
//include('money_format.php'); //this is only used on my dev box... server 2008 - xampp

function after_action_new($params=array()){
    $record =& $params['record'];
   $a = $record->val('invoiceid');
   $c = $record->val('amount');
   header('Location:update.php?id='.$a.'&amount='.$c);
    exit;
}
   
     function __sql__(){
      $query = $_GET["action"];
          if ( $query == 'partial') {
            //return "select * FROM invoice WHERE total > payment1";
            }
         else {
            //return "SELECT i.*,if(i.total>i.payment1,1,0) as balance FROM `invoice` i ORDER BY id DESC";
            }
   }



function amount__display($record){
    return money_format('$%i', $record->val('amount'));
}
function tax__display($record){
    return money_format('$%i', $record->val('tax'));
}
   function block__before_left_column(){
   
        echo "<div id=\"invoice-sidebar\">
            <h4>Tasks</h4><br />";
      include('pay_dropdown.php');

      
        echo "</div>";
       
    }


}

There is probably a better Xataface way to do this but... I had to get it done...
update.php Code
Code: Select all
<?php
include('config.php');
$myid = $_REQUEST[id];
echo 'Invoice No' . $myid;
echo '<br />';
$mydate = date("Y-m-d");
echo 'Invoice Date' . $mydate;
echo '<br />';
$amt = $_REQUEST[amount];
echo 'Invoice Amount' . $amt;
echo '<br />';

$con = mysql_connect($myserver,$username,$password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($database, $con);

$sql="UPDATE invoice SET payment1='$amt',paymentdate1='$mydate' WHERE id='$myid'";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($con)
?>


      <a href="index.php?-table=invoice">Continue</a>


Just in case others have their dev environment on a windows box... here is
the code for money_format: just be sure to comment out for linux-unix host:
Code: Select all
<?php

function money_format($format, $number) {

    $regex  = '/%((?:[\^!\-]|\+|\(|\=.)*)([0-9]+)?'.

              '(?:#([0-9]+))?(?:\.([0-9]+))?([in%])/';

    if (setlocale(LC_MONETARY, 0) == 'C') {

        setlocale(LC_MONETARY, '');

    }

    $locale = localeconv();

    preg_match_all($regex, $format, $matches, PREG_SET_ORDER);

    foreach ($matches as $fmatch) {

        $value = floatval($number);

        $flags = array(

            'fillchar'  => preg_match('/\=(.)/', $fmatch[1], $match) ?

                           $match[1] : ' ',

            'nogroup'   => preg_match('/\^/', $fmatch[1]) > 0,

            'usesignal' => preg_match('/\+|\(/', $fmatch[1], $match) ?

                           $match[0] : '+',

            'nosimbol'  => preg_match('/\!/', $fmatch[1]) > 0,

            'isleft'    => preg_match('/\-/', $fmatch[1]) > 0

        );

        $width      = trim($fmatch[2]) ? (int)$fmatch[2] : 0;

        $left       = trim($fmatch[3]) ? (int)$fmatch[3] : 0;

        $right      = trim($fmatch[4]) ? (int)$fmatch[4] : $locale['int_frac_digits'];

        $conversion = $fmatch[5];



        $positive = true;

        if ($value < 0) {

            $positive = false;

            $value  *= -1;

        }

        $letter = $positive ? 'p' : 'n';



        $prefix = $suffix = $cprefix = $csuffix = $signal = '';



        $signal = $positive ? $locale['positive_sign'] : $locale['negative_sign'];

        switch (true) {

            case $locale["{$letter}_sign_posn"] == 1 && $flags['usesignal'] == '+':

                $prefix = $signal;

                break;

            case $locale["{$letter}_sign_posn"] == 2 && $flags['usesignal'] == '+':

                $suffix = $signal;

                break;

            case $locale["{$letter}_sign_posn"] == 3 && $flags['usesignal'] == '+':

                $cprefix = $signal;

                break;

            case $locale["{$letter}_sign_posn"] == 4 && $flags['usesignal'] == '+':

                $csuffix = $signal;

                break;

            case $flags['usesignal'] == '(':

            case $locale["{$letter}_sign_posn"] == 0:

                $prefix = '(';

                $suffix = ')';

                break;

        }

        if (!$flags['nosimbol']) {

            $currency = $cprefix .

                        ($conversion == 'i' ? $locale['int_curr_symbol'] : $locale['currency_symbol']) .

                        $csuffix;

        } else {

            $currency = '';

        }

        $space  = $locale["{$letter}_sep_by_space"] ? ' ' : '';



        $value = number_format($value, $right, $locale['mon_decimal_point'],

                 $flags['nogroup'] ? '' : $locale['mon_thousands_sep']);

        $value = @explode($locale['mon_decimal_point'], $value);



        $n = strlen($prefix) + strlen($currency) + strlen($value[0]);

        if ($left > 0 && $left > $n) {

            $value[0] = str_repeat($flags['fillchar'], $left - $n) . $value[0];

        }

        $value = implode($locale['mon_decimal_point'], $value);

        if ($locale["{$letter}_cs_precedes"]) {

            $value = $prefix . $space . $value . $suffix;

        } else {

            $value = $prefix . $value . $space . $currency . $suffix;

        }

        if ($width > 0) {

            $value = str_pad($value, $width, $flags['fillchar'], $flags['isleft'] ?

                     STR_PAD_RIGHT : STR_PAD_LEFT);

        }



        $format = str_replace($fmatch[0], $value, $format);

    }

    return $format;

}
?>

Will probably modify the header to automatically return to the needed page.
If you notice there is an include to my connection settings that is in my root...
I will clean up the code and repost the final code...

Thanks for your help

Tim
idoxlr8
 
Posts: 4
Joined: Fri Sep 07, 2012 7:15 am


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 21 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved