Simple Invoices logo
Simple Invoices - Home Simple Invoices - Download Simple Invoices - Demo Simple Invoices - Blog Simple Invoices - oOrum Simple Invoices - Wiki
Home Download Demo Blog Forum Wiki
Not signed in (Sign In)

Support: Dude, what happened to your query?: - NEW INSTALL HELP

Bottom of Page

1 to 6 of 6

  1.  
    • CommentAuthorjohn1974
    • CommentTimeJul 9th 2008 edited
     permalink
    Hi

    I love the look of the Simple Invoices software but i have a problem with a new install. :(

    My sever is running PHP5, MySql version 4 and simpleinvoices_20080629
    It runs ok apart from the on th ' Invoices -> Manage Invoices ' screen.... I get the Sql message below.

    I used the SimpleInvoices-MySQL4_0.sql file for the database creation

    Many thanks in advance.

    ----------------------------------------------------------------

    Dude, what happened to your query?:

    SELECT iv.id, b.name As biller, c.name As customer, (CASE WHEN datediff(now(),iv.date) <= 14 THEN '0-14' WHEN datediff(now(),iv.date) <= 30 THEN '15-30' WHEN datediff(now(),iv.date) <= 60 THEN '31-60' WHEN datediff(now(),iv.date) <= 60 THEN '61-90' ELSE '90+' END ) as overdue, iv.type_id, pf.pref_inv_wording, iv.date, @invd:=(SELECT sum( IF(isnull(ivt.total), 0, ivt.total)) FROM si_invoice_items ivt where ivt.invoice_id = iv.id) As invd, @apmt:=(SELECT sum( IF(isnull(ap.ac_amount), 0, ap.ac_amount)) FROM si_account_payments ap where ap.ac_inv_id = iv.id) As pmt, IF(isnull(@invd), 0, @invd) As total, IF(isnull(@apmt), 0, @apmt) As paid_format, (select (total - paid_format)) as owing FROM si_invoices iv, si_biller b, si_customers c, si_preferences pf WHERE iv.customer_id = c.id AND iv.biller_id = b.id AND iv.preference_id = pf.pref_id GROUP BY iv.id ORDER BY iv.id DESC
    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(now(),iv.date) <= 14 THEN '0-14' WHEN datediff(now(),iv.da
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Path_to_folder/invoicetest/include/sql_queries.php on line 44
    • CommentAuthorjustin
    • CommentTimeJul 10th 2008
     permalink
    Hey John,

    re mysql 4
    - do you know if your running mysql 4.0 or 4.1

    i've had a few issues with this manage invoices query - not sure if it works on mysql 4.0

    the above query has been changed in svn trunk - which has not yet been released - i will post it and if you can test if it works on your server it would be great

    Cheers

    Justin
    • CommentAuthorjohn1974
    • CommentTimeJul 10th 2008
     permalink
    Hi Justin

    Thanks for the quick reply .... i am am running 4.0.

    Cheers

    John
    • CommentAuthorjustin
    • CommentTimeJul 10th 2008
     permalink
    Hey John,

    yep - 4.0 - thats the most likely problem

    can you give this query a test and let me know if it runs in 4.0 (i don't think it will but worth a try)

    SELECT iv.id,
    b.name AS biller,
    c.name AS customer,
    (SELECT SUM(coalesce(ii.total, 0)) FROM si_invoice_items ii WHERE ii.invoice_id = iv.id) AS invoice_total,
    (SELECT SUM(coalesce(ac_amount, 0)) FROM si_account_payments ap WHERE ap.ac_inv_id = iv.id) AS INV_PAID,
    (SELECT (coalesce(invoice_total,0) - coalesce(INV_PAID,0))) As owing,
    DATE_FORMAT(date,'%Y-%m-%d') AS date,
    (SELECT IF((owing = 0), 0, DateDiff(now(), date))) AS Age,
    (SELECT (CASE WHEN Age = 0 THEN ''
    WHEN Age <= 14 THEN '0-14'
    WHEN Age <= 30 THEN '15-30'
    WHEN Age <= 60 THEN '31-60'
    WHEN Age <= 90 THEN '61-90'
    ELSE '90+' END)) AS aging,
    iv.type_id As type_id,
    pf.pref_description AS preference
    FROM si_invoices iv
    LEFT JOIN si_biller b ON b.id = iv.biller_id
    LEFT JOIN si_customers c ON c.id = iv.customer_id
    LEFT JOIN si_preferences pf ON pf.pref_id = iv.preference_id


    cheers

    Justin
    • CommentAuthorjohn1974
    • CommentTimeJul 11th 2008
     permalink
    Hey Justin!

    I ran the query and got this error....

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(coalesce(ii.total, 0)) FROM si_invoice_items ii WHER

    Thanks again for all your help

    John
    • CommentAuthorjustin
    • CommentTimeJul 11th 2008
     permalink
    thanks john,

    doesn't look good for anything less than mysql 4.1 for Simple Invoices

    if you can't install mysql 5 we are looking at supporting different databases in the near future
    - svn trunk works on postgres (thanks tentra!!)
    - and i really want to get started on sqlite support for Simple Invoices

    cheers

    justin

1 to 6 of 6

  1.