In sales_order_ui.inc the reference number input is placed in table_section(1) and I beleive workflow is better if it is placed at the end of table_section(4). Because...

When entering an invoice and tabbing through the fields with a keyboard. If you enter a custom reference in table_section(1) then tab through to table_section(4) and enter a date it will update the reference number to the next sequencial number causing you to go back to table_section(1) to replace the value again.

I suggest removing this line from line 361

ref_row(_("Reference").':', 'ref', _('Reference number unique for this document type'), null, '', $order->trans_type, array('date'=> @$_POST['OrderDate']));

and place it here around 458

    else
    {
        label_row($date_text, $order->document_date);
        hidden('OrderDate', $order->document_date);
    }
    
    ref_row(_("Reference").':', 'ref', _('Reference number unique for this document type'), null, '', $order->trans_type, array('date'=> @$_POST['OrderDate']));

    end_outer_table(1); // outer table

    if ($change_prices != 0) {
        foreach ($order->line_items as $line_no=>$item) {
            $line = &$order->line_items[$line_no];
            $line->price = get_kit_price($line->stock_id, $order->customer_currency,
                $order->sales_type, $order->price_factor, get_post('OrderDate'));
        }
        $Ajax->activate('items_table');
    }

Let me know if this will cause trouble that I am unaware I am trying it now.

77

(13 replies, posted in Reporting)

I have a service industry and am trying to create a "service ticket/invoice" with the total customer balance at the time of printing. I have imported the function get_open_balance from rep101 and am trying to use it but it returns an array. Maybe the array needs to be added up before displaying?

Here is the funcion I imported to the begining of my version of rep107
The parts with variable $to were disabled since it is not passed in an invoice

function get_open_balance($debtorno)
{
    #if($to) 
    #    $to = date2sql($to);

     $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0), t.alloc * -1, t.alloc)) AS Allocated,";

     $sql .=    "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
             (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding
        FROM ".TB_PREF."debtor_trans t
        WHERE t.debtor_no = ".db_escape($debtorno)
        ." AND t.type <> ".ST_CUSTDELIVERY;
    # if ($to)
    #    $sql .= " AND t.tran_date < '$to'";
    #$sql .= " GROUP BY debtor_no"; 

    $result = db_query($sql,"No transactions were returned");
    return db_fetch($result);
}

On the end of the invoice printing side I have this:
The lines are commented becuse it is broken.

$rep->NewLine();
                #$bal = get_open_balance($myrow['debtor_no']); // added for total balance not working function returns an array.
                #$DisplayCustTotal = number_format2($bal,$dec); // added for total balance
            $DisplayTotal = number_format2($sign*($myrow["ov_freight"] + $myrow["ov_gst"] +
                $myrow["ov_amount"]+$myrow["ov_freight_tax"]),$dec);
            $rep->Font('bold');
            if (!$myrow['prepaid']) $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("THIS INVOICE"), - 2);
            $rep->TextCol(6, 7, $DisplayTotal, -2);
            $rep->NewLine(); // added for total balance
            $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL BALANCE"), - 2); // added for total balance
            #$rep->TextCol(6, 7, $DisplayCustTotal, -2); // added for total balance
            if ($rep->formData['prepaid'])
            {
                $rep->NewLine();
                $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid']=='final' ? _("THIS INVOICE") : _("TOTAL INVOICE"), - 2);
                $rep->TextCol(6, 7, number_format2($myrow['prep_amount'], $dec), -2);
            }
            $words = price_in_words($rep->formData['prepaid'] ? $myrow['prep_amount'] : $myrow['Total']
                , array( 'type' => ST_SALESINVOICE, 'currency' => $myrow['curr_code']));
            if ($words != "")
            {
                $rep->NewLine(1);
                $rep->TextCol(1, 7, $myrow['curr_code'] . ": " . $words, - 2);
            }
            $rep->Font();
            if ($email == 1)
            {
                $rep->End($email);
            }

I am not a very good programmer and I have no experience in PHP. Thank you in advance you guys are lifesavers.

When I enter a payment in the ui it never gives me the success screen. But when I check the Database and the Allocate customer payment screen it shows that the payments were entered but not allocated. When I try to allocate them in the allocate customer payment screen with the debug 2 flag set (in config.php) It gives me this error   

/home4/traffid0/public_html/books/sales/includes/db/cust_trans_db.inc:206:    display_backtrace()
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:126:    get_customer_trans('1','12','190')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:42:    (allocation Object)->read('12','1','190','2')
/home4/traffid0/public_html/books/sales/allocations/customer_allocate.php:111:    (allocation Object)->allocation('12','1','190','2')

/home4/traffid0/public_html/books/includes/ui/ui_msgs.inc:14:    trigger_error('
/home4/traffid0/public_html/books/sales/includes/db/cust_trans_db.inc:206:    display_backtrace()
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:126:    get_customer_trans('1','12','190')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:42:    (allocation Object)->read('12','1','190','2')
/home4/traffid0/public_html/books/sales/allocations/customer_allocate.php:111:    (allocation Object)->allocation('12','1','190','2')
','256')
/home4/traffid0/public_html/books/includes/ui/ui_view.inc:1484:    display_error('
/home4/traffid0/public_html/books/sales/includes/db/cust_trans_db.inc:206:    display_backtrace()
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:126:    get_customer_trans('1','12','190')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:42:    (allocation Object)->read('12','1','190','2')
/home4/traffid0/public_html/books/sales/allocations/customer_allocate.php:111:    (allocation Object)->allocation('12','1','190','2')
')
/home4/traffid0/public_html/books/sales/includes/db/cust_trans_db.inc:206:    display_backtrace()
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:126:    get_customer_trans('1','12','190')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:42:    (allocation Object)->read('12','1','190','2')
/home4/traffid0/public_html/books/sales/allocations/customer_allocate.php:111:    (allocation Object)->allocation('12','1','190','2')
DATABASE ERROR : no debtor trans found for given params
sql that failed was : SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, trans.prep_amount>0 as prepaid,com.memo_,bank_act,2_bank_accounts.bank_name, 2_bank_accounts.bank_account_name, 2_bank_accounts.account_type AS BankTransType, 2_bank_accounts.bank_curr_code, 2_bank_trans.amount as bank_amount FROM 2_debtor_trans trans LEFT JOIN 2_comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN 2_shippers ON 2_shippers.shipper_id=trans.ship_via, 2_debtors_master cust, 2_bank_trans, 2_bank_accounts WHERE trans.trans_no='1' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND trans.debtor_no='190' AND 2_bank_trans.trans_no ='1' AND 2_bank_trans.type=12 AND 2_bank_trans.amount != 0 AND 2_bank_accounts.id=2_bank_trans.bank_act 



/home4/traffid0/public_html/books/includes/errors.inc:198:    trigger_error('DATABASE ERROR : no debtor trans found for given params
sql that failed was : SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, trans.prep_amount>0 as prepaid,com.memo_,bank_act,2_bank_accounts.bank_name, 2_bank_accounts.bank_account_name, 2_bank_accounts.account_type AS BankTransType, 2_bank_accounts.bank_curr_code, 2_bank_trans.amount as bank_amount FROM 2_debtor_trans trans LEFT JOIN 2_comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN 2_shippers ON 2_shippers.shipper_id=trans.ship_via, 2_debtors_master cust, 2_bank_trans, 2_bank_accounts WHERE trans.trans_no='1' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND trans.debtor_no='190' AND 2_bank_trans.trans_no ='1' AND 2_bank_trans.type=12 AND 2_bank_trans.amount != 0 AND 2_bank_accounts.id=2_bank_trans.bank_act 


','256')
/home4/traffid0/public_html/books/sales/includes/db/cust_trans_db.inc:207:    display_db_error('no debtor trans found for given params','SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, trans.prep_amount>0 as prepaid,com.memo_,bank_act,&TB_PREF&bank_accounts.bank_name, &TB_PREF&bank_accounts.bank_account_name, &TB_PREF&bank_accounts.account_type AS BankTransType, &TB_PREF&bank_accounts.bank_curr_code, &TB_PREF&bank_trans.amount as bank_amount FROM &TB_PREF&debtor_trans trans LEFT JOIN &TB_PREF&comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN &TB_PREF&shippers ON &TB_PREF&shippers.shipper_id=trans.ship_via, &TB_PREF&debtors_master cust, &TB_PREF&bank_trans, &TB_PREF&bank_accounts WHERE trans.trans_no='1' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND trans.debtor_no='190' AND &TB_PREF&bank_trans.trans_no ='1' AND &TB_PREF&bank_trans.type=12 AND &TB_PREF&bank_trans.amount != 0 AND &TB_PREF&bank_accounts.id=&TB_PREF&bank_trans.bank_act ','1')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:126:    get_customer_trans('1','12','190')
/home4/traffid0/public_html/books/includes/ui/allocation_cart.inc:42:    (allocation Object)->read('12','1','190','2')
/home4/traffid0/public_html/books/sales/allocations/customer_allocate.php:111:    (allocation Object)->allocation('12','1','190','2')


Thank You in advance

79

(10 replies, posted in Items and Inventory)

I as well want the 'long_description' on the invoice but I can't find it in the tables? inventory/manage/items.php says its called 'long_decription' I tried to replace

$rep->TextColLines($c++, $c, $myrow2['StockDescription'], -2);
to:
$rep->TextColLines($c++, $c, $myrow2['long_description'], -2);

in /reporting/rep107.php but I get a blank spot on the invoice so, when I checked the DB I couldn't find it in the item_codes table
Where is it held?  Am I totally wrong?

Just as a warning in the United States you can change your books from cash to accrual but not the other way around. I would be surprised if it's the only Country too.

Cash is accounted when you get the payment

Accrual when it is invoiced or billed

Example

$100000 sales in in 2017, 80000 paid 20000 in accounts receivable

Accrual based taxes on 100000 of income
Cash based taxes on 80000

@Braath Waate

HAHA not that fancy I'll go over it now.

1: Exported all clients from Quickbooks to a CSV file (option in the clients section)

2: Opened Front Acc. and entered the first client entry from the CSV Manually (Entered Customer, Branch and CRM Sections)

3: Opened the Database to look at the tables affected and the format (I used PhpMyAdmin on my server)

4: Exported each of the tables affected to CSV in PhpMyAdmin (0_debtors_master: 0_crm_persons:0_crm_contacts: 0_cust_branch:)

5: Copy and Paste the data from your QB Clients.CSV file into each of the exported FA DB CSV files in a spreadsheet editor (I used OpenOffice/Libre Office)

6: Import the DB CSV files on your server (You can use PHP commands i.e. LOAD DATA INFILE "/home/jason/clients.csv" but I used PhpMyAdmin as it has built-in CSV import.

Et Voila c'est fini, That is my fancy import process.

Ok I'm following up for anyone in the future importing clients. I finished importing about 1000 clients directly in the database and it worked beautifully. Everything I wrote above was correct just missing one thing. The 0_crm_contacts table connects 0_cust_branch to 0_crm_persons as well as the 0_debtors_master table so you should have twice  as many entries in 0_crm_contacts as you have clients (assuming your clients are 1 branch per client like how my data was.)

@Braath Waate Thank you for the suggestion of  QuickBooks2FA.zip I decided to stay working directly with the database because I was close to done already.

Ok as stated I'm importing our customers to the our new FA System running Version 2.4.1 Build 22.05.2017 just in case for database consistency. Ive been looking at the database and I believe I found all the tables needed. Let me know if i have missed anything.

0_debtors_master:  to import the clients and some basic settings
0_crm_persons: to import clients customer relations / contact info such as billing address telephone and such
0_crm_contacts: to connect the clients crm_persons table to their debtors_master id
0_cust_branch: for the clients branch info and accounts affected

Is there any other tables needed or affected when creating a client?
Also is there anyway to give the client a starting / opening balance to not have to transfer over and open invoices?

Thank You,
Jason

84

(5 replies, posted in Report Bugs here)

Okay just to follow up for anyone with this issue. The error above is from having PHP 5.2 and older. Updating solved the problem right away. In my case, I had a line in .htaccess that prevented my server from using the updated version. At this time, I'm using PHP Version 5.4.43 and it works perfectly.

Thank you again for your help.

85

(5 replies, posted in Report Bugs here)

Okay it's the Justhost server it's running PHP 5.2.17

what's weird is when I change it with the server tools or the PHP.ini it doesnt change I have to dig deeper later.

maybe the .htaccess? i'll see

86

(5 replies, posted in Report Bugs here)

Thanks guys your awesome. You got me on train to find the problem. So, FA is not generating the PDFs on the server in the previously stated PDF folder and when I turned on Debugging the error log give me

PHP Parse error:  syntax error, unexpected T_FUNCTION in /home4/traffid0/public_html/books/reporting/includes/html_entity_decode_php4.php on line 338

I'm looking into it now

Thanks again and Happy New Year!

87

(5 replies, posted in Report Bugs here)

I can't print invoices or Display PDF reports on a new install on my Justhost server. My browser can display PDFs fine but my FA install shows me a progress bar then does nothing. Permissions write problem perhaps? Please help me if you can I have the example company DB and login @

http://trafficpestsolutions.com/books/

User: tester
Pass: tester