1 Reply by mark0300

(15 replies, posted in Setup)

@flopezcpa, I do not know if this reply came too late but I hope it could be used as a reference for others if they face the similar siutation like myself.  I managed to solve a similar situation like yours. Just that my issue is simpler but you could use the same way of settings for multiple item taxes within one invoice without having to change any program codes.

Here is the scenario. 
1.  A supplier sell products to me and charge the following taxes:
     a.    Essential goods, 2% tax applies.
     b.    Normal goods:   5%
2.  When I sell these products I will apply the same tax rates on these goods.
3.  I setup tax type:
     a.   Supp Tax Essential goods:  2%
     b.   Supp Tax Normal goods:  5%
     c.   SalesTax Essential goods:  2%
     d.   Sales Tax Normal goods:  5%
4.  I setup tax groups for:
     a.  Supplier:  Supp Tax Essential + supp Tax Normal
     b.  Sales: Sales Tax Essential + Sales Tax Normal
5.  I setup Tax Item Exemption:
     a.   Essential:  Exempt Normal Tax
     b.   Normal:  Exempt Essential Tax
6.  I setup items:
     a.   Essential Goods:  Item tax type: Select 5a.
     b.   Normal Goods:  Item tax type: Select 5b.

With the setup, you could buy the goods and sell them after you add the profit margin.  And the GL etc should be recorded correctly.
This setup works for me to meet my needs and I hope it helps.

/Mark

flopezcpa wrote:

Good afternoon, if you solved a part
The complete case is that I buy from the same supplier, products that have different taxes.
If I buy from him food, he sells it to me with the tax exempt
If I buy from him liquor he charges me a 10% tax
If I buy toiletries, she charges me 7% tax

The supplier is still the same, but when registering the purchase order, he only calculates 7% for me, and he should really charge me the tax according to the type of item to buy.

In the sale the same thing will happen, that is to say that when I sell those articles, I must sell them in the same way, charging my clients the same tax, on the same articles.

And that I did not manage to do, and I have tried several options, I do not know if creating a tax group, the issue is that if I core it, it will charge me the tax, in equal parts, but in my country throughout the territory it charges the same general tax.

Go to forum: Setup

2 Reply by mark0300

(2 replies, posted in Accounts Payable)

Hi APMuthu,

Thanks very much for your suggestions. 
I'm not sure the issues I mentioned with the extra ")" and "(" can resolve the issue.

I will give it a try when I set up a test server.   Due to the urgency, we have decided to down grade the OS from ubuntu 20.04 to ubuntu 18.04.  Ubuntu 18.04 comes default with mysql 5.7 and there is no issue.

I will post my findings here once I have a chance to verify it.

/Mark

Go to forum: Accounts Payable

3 Topic by mark0300

(2 replies, posted in Accounts Payable)

Sorry to open an issue on version 2.3.25.  This issue is related to mysql 8.   One of my 2 installations is 2.3.25 and the other one is 2.4.  We did not upgrade this 2.3.25 to 2.4 because we have some customizations done to this version and we couldn't convert the customization to 2.4.  It has been running well without issues on Centos 7, mysql 5.7 until we upgraded our server to Ubuntu 20.04 and Mysql 8 recently.  I search thru' the forum and I found a similar issue in the forum but without a solution:

https://frontaccounting.com/punbb/post.php?tid=1977

Below is the error similar to the above thread (Note the highlight in Blue and Red):
-------
error code : 1064
error message : 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 ') LIMIT 0, 33' at line 35

SELECT * FROM ((SELECT trans.type, trans.trans_no, trans.reference, supplier.supp_name, trans.supp_reference, trans.tran_date, trans.due_date, supplier.curr_code, (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS TotalAmount, trans.alloc AS Allocated, ((trans.type = 20 OR trans.type = 21) AND trans.due_date < '2022-02-06') AS OverDue, (ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc <= 0.004) AS Settled FROM 0_supp_trans as trans, 0_suppliers as supplier WHERE (supplier.supplier_id = trans.supplier_id AND trans.tran_date >= '2022-01-07' AND trans.tran_date <= '2022-02-06' AND trans.ov_amount != 0) UNION (SELECT 25 as type, trans.id as trans_no, trans.reference, supplier.supp_name, po.requisition_no AS supp_reference, delivery_date as tran_date, '' as due_date, supplier.curr_code, '' AS TotalAmount, '' AS Allocated, 0 as OverDue, 1 as Settled FROM 0_grn_batch as trans, 0_suppliers as supplier, 0_purch_orders as po WHERE supplier.supplier_id = trans.supplier_id AND trans.purch_order_no = po.order_no AND trans.delivery_date >= '2022-01-07' AND trans.delivery_date <= '2022-02-06')) as tr) LIMIT 0, 20
--------

After some research and tests, I found 2 issues (highlighted in blue and red in the error message) in the codes but I couldn't resolve it because it does not seem to be straight forward.

I found that the program codes can be run on mysql 5.7 without any problem, but when it runs on mysql 8, it produced a syntax error. 

Issue 1:

I don't understand why a "(" was produced before the supplier.supplier_id.  (refer to the error message which I highlited in blue). 

In this file /purchasing/includes/db/supp_trans_db.inc, this code does not have "(" after WHERE.

function get_sql_for_supplier_inquiry($filter, $after_date, $to_date, $supplier_id=ALL_TEXT)
{
    $date_after = date2sql($after_date);
    $date_to = date2sql($to_date);

    $sql = "SELECT trans.type,
        trans.trans_no,
        trans.reference,
        supplier.supp_name,
        trans.supp_reference,
        trans.tran_date,
        trans.due_date,
        supplier.curr_code,
        (trans.ov_amount + trans.ov_gst  + trans.ov_discount) AS TotalAmount,
        trans.alloc AS Allocated,
        ((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
        (ABS(trans.ov_amount + trans.ov_gst  + trans.ov_discount) - trans.alloc <= ".FLOAT_COMP_DELTA.") AS Settled
        FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as supplier
       
        WHERE supplier.supplier_id = trans.supplier_id <---- it produced a "(" before supplier-supplier_id (don't know why?  See the error code above) and that caused a syntax error behind.
        
        AND trans.tran_date >= '$date_after'
        AND trans.tran_date <= '$date_to'
        AND trans.ov_amount != 0";    // exclude voided transactions
       
Issue 2:

A ")" was produced after the "as tr)".  I couldn't find this ")" in the code.

    if (($filter == '6'))
    {
            $sql = $sql2;
    }
    elseif (!isset($filter) || $filter == ALL_TEXT || $filter == '6') {
        $sql = "SELECT * FROM (($sql) UNION ($sql2)) as tr"; <--- a ")" was produced here after "as tr".
       }

After searching thru' the relevant codes, I couldn't find how these 2 "(" and ")" were produced. If I could find them, I could just modify the codes to fix the problems.  I would appreciate if anyone could help me on this.

I found that there are some changes made to 2.4 on the above file and when it is run on mysql 8, it does not produce such issue.   I attempted to copy those changes to 2.3.25, but yet I couldn't solve the issue.

Thanks!

Go to forum: Accounts Payable

4 Reply by mark0300

(21 replies, posted in Reporting)

@Rafat,

If you have another tax item with the same tax account code entered, you will notice that each will appear 4 times!

If you go to report709.php and add that line I mentioned in my earlier post and run the report again, then the problem might go away.  It is caused by the ON condition in the LEFT JOIN.

I am not sure my code will cause other issue.  But for now, it solves my problem.

It will be great if some experts could test it out.

Go to forum: Reporting

5 Reply by mark0300

(21 replies, posted in Reporting)

There is no duplication of reference BKPMT-00010. I think the issue is in the ON condition set in the LEFT JOIN.    If the same tax code appear in the gl_trans more than once with other condition same, and when LEFT JOIN with trans_tax_details take place, it will produce multiple rows.  The version of this rep709.rep is from 2.4.8.

Go to forum: Reporting

6 Topic by mark0300

(21 replies, posted in Reporting)

I found a bug in rep709.php tax report.  I got it fixed and would like to share.    (see the ======> below in the sql query codes)

Problem:
When performing a Bank Payment and if the entries contain the same tax code which appear more than one time, and when the tax report rep709.php is run, it will generate multiple lines for the same tax codes and the number of times it appear will depend on the number of appearance of the same tax code.

For example, if my tax code is 8800 (tax name TX) and I have made 2 tax amounts of  is say $7 and $14.

My bank payment entries would be:
-   8800   $7
-    8800   $14
In this example, 8800 appear twice.   Now if I run this report, this tax will appear as follows (each entry appear twice):

Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX

If the same bank payment transaction has the 3rd tax entry say $21, total 3 rows now (each entry now appear 3 times):
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX

I looked into the sql query and I found a fix:

    $sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
                taxrec.net_amount*ex_rate AS net_amount,
                IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.", 
                    IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), 
                    IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
                branch.br_name
        FROM ".TB_PREF."trans_tax_details taxrec
        LEFT JOIN ".TB_PREF."tax_types tt
            ON taxrec.tax_type_id=tt.id
        LEFT JOIN ".TB_PREF."gl_trans gl 
            ON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND gl.amount<>0 AND

=======>  gl.amount=taxrec.amount AND <====== Add this line to the query and it fix the problem.  

            (tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)
        LEFT JOIN ".TB_PREF."supp_trans strans
            ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type
        LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id
        LEFT JOIN ".TB_PREF."debtor_trans dtrans
            ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type
        LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no
        LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code
        WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)
            AND !ISNULL(taxrec.reg_type)
            AND taxrec.tran_date >= '$fromdate'
            AND taxrec.tran_date <= '$todate'
        ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

Any comments are welcome.

/Mark

Go to forum: Reporting

7 Reply by mark0300

(4 replies, posted in Accounts Receivable)

Thanks Rafat and APMuthu for your prompt responses.

What I would like to do is to adjust the width of the "Item Description" on the interface when I enter the items to the Sales order.   Please refer to the link below to see what I meant.    When I intend to increase the width size of the "Item Description", I would also like to reduce the "Item Code" and "Quantity".   I tried on APMuthu's suggestion, but it does not change the width. 

Please click the screen shot (http://btposlic.duckdns.org/tmp/Row-field-size.jpg) here to view what I meant.

I still hope someone could point to the right file and code.

Thanks.

/Mark

Go to forum: Accounts Receivable

8 Topic by mark0300

(4 replies, posted in Accounts Receivable)

I found that the width size of the "Item Description" is too small when I enter a new sales item.  I want to increase it.

May I know where is the code that I could increase/adjust the width of the "Item Description" (and other items such as "Unit", "Price before Tax" etc).   

Could someone advise?

Thanks.

/Mark

Go to forum: Accounts Receivable

9 Reply by mark0300

(5 replies, posted in Accounts Receivable)

For me, I created another account for this customer but with a different short name for another currency:

For example,
Company name:  ABC Co., Ltd
Short name:  ABC-USD

The second account would be:
Company name:  ABC Co., Ltd
Short name:  ABC-SGD

So long as you use a different short name, the system is able to handle it.

Go to forum: Accounts Receivable

10 Reply by mark0300

(5 replies, posted in Accounts Receivable)

Braath Waate wrote:

You could take the same approach for item insertion when modifying the code: make a copy of the cart, insert a new line, delete the cart items after the new line, then add the items after the new line from the cart copy.   This would be a nice feature to offer for inclusion into the base code.

Alternatively, you could avoid using the FA editor and instead enter orders as csv files and use the import transactions extension.  When a customer makes a change, void the entire order, and update the csv using a text editor and reimport.  However, I think this only currently supports sales orders and direct invoices and not quotations.

Because FA does not have line numbers on the order, you can also use a pdf editor to move stuff around to match the look of the customer's PO before you print.   For this purpose, I use Libreoffice Draw, which works quite well.

Hi Braath, APMuthu,

I ran through the functions of copy_to_cart(), function handle_new_item(), function handle_delete_item($line_no).   I am getting a bit frustrated when I try to construct something out on Sales Order Entry.  My idea is to add a "+" icon next to the pencil icon (edit) such that when I click on the "+" icon, it will add a line above it.  However, I got stuck after adding the "+" icon.  I can't add a new line above the current line and I do not know how to push the rest of the lines downward.  Would you be able to guide me with some codes or provide some simple function codes so that I could get things moving...?

Thanks...

/Mark

Go to forum: Accounts Receivable

11 Reply by mark0300

(5 replies, posted in Accounts Receivable)

apmuthu wrote:

Changing the primary keys of the respective tables and re-ordering them is possible but must be done carefully or through a separate script / extension.

Hi APMuthu,

Noted with thanks.

/Mark

Go to forum: Accounts Receivable

12 Reply by mark0300

(5 replies, posted in Accounts Receivable)

Braath Waate wrote:

You could take the same approach for item insertion when modifying the code: make a copy of the cart, insert a new line, delete the cart items after the new line, then add the items after the new line from the cart copy.   This would be a nice feature to offer for inclusion into the base code.

Alternatively, you could avoid using the FA editor and instead enter orders as csv files and use the import transactions extension.  When a customer makes a change, void the entire order, and update the csv using a text editor and reimport.  However, I think this only currently supports sales orders and direct invoices and not quotations.

Because FA does not have line numbers on the order, you can also use a pdf editor to move stuff around to match the look of the customer's PO before you print.   For this purpose, I use Libreoffice Draw, which works quite well.

Hi Braath,

I got what you mean.  I will look into it.

Thanks!

/Mark

Go to forum: Accounts Receivable

13 Topic by mark0300

(5 replies, posted in Accounts Receivable)

Hi there,

When creating new or editing SO, DN, Quotation etc, I need to insert a new row between rows of items which I already entered.  This is because some customers required me to follow the exact sequence of their orders.   This happened often for some customers as their demands are changing very often. 

If there are 20 line items in the order and the customer make a change of their order by inserting a new line item say at row 2, I will have to delete the rows from 2 and below and re-enter everything after inserting the new line.

How do I modify the codes to allow a insertion of row in between rows?

Thanks.

/Mark

Go to forum: Accounts Receivable

14 Reply by mark0300

(3 replies, posted in Setup)

Thanks guys

Go to forum: Setup

15 Topic by mark0300

(3 replies, posted in Setup)

Hi there,

I have noticed some changes to the Backup and Restore Database page:

1.  I noticed under the file list, there are 2 radio buttons for:

a.    Update security settings
b.    Protect security settings

I search thru' the forums but did not see anyone asked for this.

Would someone advise what are these for?

Thanks!

/Mark

Go to forum: Setup

16 Reply by mark0300

(4 replies, posted in Modules Add-on's)

@Apmuthu,  Would you please point to the files and functions to make modifications?  Thank you!

Go to forum: Modules Add-on's

17 Reply by mark0300

(12 replies, posted in Installation)

I solved this by commenting the following 2 lines in session.inc

        if ($_SESSION['IPaddress'] != $_SERVER['REMOTE_ADDR'])
            return false;

Go to forum: Installation

18 Topic by mark0300

(1 replies, posted in FA Modifications)

Hi there,

Would someone advise how do I open the $link (gl/inquiry/tax_inquiry.php?) in a new tab of the browser?

        $this->add_lapp_function(1, _("Tax Inquiry"),
            "gl/inquiry/tax_inquiry.php?", 'SA_TAXREP', MENU_INQUIRY);

I have made attempt to add  target = '_blank' in the link, but it just messed up the link.

Appreciate someone could share some tips...

Thanks.

/Mark

Go to forum: FA Modifications

19 Topic by mark0300

(1 replies, posted in Banking and General Ledger)

I found some issues in Audit Trail records which I think it can be made better or the data capture is not quite useful.

There are 2 issues in the Audit Trail Report/Record.  Let me elaborate.   

1.    Issue 1:  Amount in SO, DN.  The amounts of the records are not captured for Sales Order, Delivery Note.  It only starts capturing at sales invoice.   That means, if the SO or DN are changed by someone, we only know the date, time and users who change it.  But we will not know the value of the change.  It is desirable to capture the amount of each of the change of the SO or DN.  With this, we will be able to trace the date, time, the user who make the changes and the new value of each of the changes.

2.    Issue 2:  Entry disappeared from Audit Trail if a modification to the transaction is made.   For example, if I made a few changes to the same Sales Invoice (delivery qty), all the prior audit trail records Amount will be replaced by the amount of the latest version of change.  The prior changes to this invoice cannot be traced.

Since there is a table 0_audit_trail capturing all the transactions, can we add a field “amount” to capture each of the changes?  With this, the amount of each of the changes is captured and it is permanently tied to that change.  New change to the transaction will have changed amount captured to the trail records.  I am not a programmer and have little knowledge in this aspect.  Could someone advise what changes can be made?   

I have made a pdf file with screen shots to elaborate the issues:  http://www.mgatech-sg.com/AuditTrail-Mark.pdf

Thank you.

/Mark

Go to forum: Banking and General Ledger

20 Reply by mark0300

(44 replies, posted in Announcements)

In fact, I have successfully done the Chinese language before.  The issue is when I installed the dashboard theme and extension as created by Alastair, the encoding has to be 8859-1.  If I change it to utf-8 (as shown below), the legends, headers of the tables and charts will not show up.   I am wondering how I could use the utf-8 and yet the dashboard can still show the legend properly. Please refer to the following screenshots:

http:www.mgatech-sg.com/images/fa1-iso8895-1.png
http:www.mgatech-sg.com/images/fa1-utf-8.png

<
$installed_languages = array (
  0 =>
  array (
    'code' => 'C',
    'name' => 'English',
//    'encoding' => 'iso-8859-1',
    'encoding' => 'utf-8',
  ),


Thanks again!

Go to forum: Announcements

21 Reply by mark0300

(44 replies, posted in Announcements)

Hi apmuthu,

Thanks for your reply.   I have this file in US English \lang\en_US\LC_MESSAGES\en_US-2.3.12-1.po and I can compile it using poedit and it is already in utf-8.  However, I don't have a lang\C... in the lang directory.  Would you enlighten me which .po file should I compile to utf-8 and I will try it out..

Thank you

/Mark

Go to forum: Announcements

22 Reply by mark0300

(3 replies, posted in Setup)

Thanks  @APMuthu.

I actually added it to lang/installed_languages.inc to lang/installed_languages.inc as follows.  But it does not work since the language is used before a user is logged in, therefore the user_theme() does not work here. The reason of using the user_theme() is when dashboard theme is used and the encoding is utf-8, some of the wordings (legends, headers) on the charts or tables disappeared.  The missing wordings will show up when the encoding is set to "iso-8859-1".  Would you suggest any other work around where I could use utf-8 to handle my chinese character inputs and it does not affect the dashboard charts...  Thank you.


include_once($path_to_root . "/includes/current_user.inc");


//$themename=user_theme();
$themename="dashboard";

if ($themename=='dashboard')
{
$encoding_0="iso-8859-1";
//$encoding_0="utf-8";
}
else
{
$encoding_0="utf-8";
//$encoding_0="iso-8859-1";
}


$installed_languages = array (
  0 =>
  array (
    'code' => 'C',
    'name' => 'English',
    'encoding' => $encoding_0,   

  ),

Go to forum: Setup

23 Reply by mark0300

(44 replies, posted in Announcements)

Hi Alastair,

I have installed the dashboard theme on FA 2.3.25.  I am using utf-8 and I have changed the encoding in   lang\installed_languages.inc from "iso-8859-1" to 'utf-8".  And once I change this, the wordings (legends, headers etc) on the charts or table will disappear.  Only when I switch back to iso-8859-1, then these wordings will appear.  I am not sure how could I make it appear if I use "utf-8"?  Would you please advise? 

Below are the codes in installed_languages.inc as you could see I have to switch between the 2 encoding.
I need to use utf-8 because I need to handle some Chinese characters.


<
$installed_languages = array (
  0 =>
  array (
    'code' => 'C',
    'name' => 'English',
//    'encoding' => 'iso-8859-1',
    'encoding' => 'utf-8',
  ),

Go to forum: Announcements

24 Topic by mark0300

(3 replies, posted in Setup)

Hi,

Is there a function that I could use to retrieve the current user's theme?

I have found user_theme(), but the value retrieve does not seem to be correct.

Here is the code:

include_once($path_to_root . "/includes/current_user.inc");

$themename=user_theme();

if ($themename=='dashboard')
{

echo "this is dashboard";

}
else
{
echo "this is not dashboard";
}


Could someone advise?

Thank you.

/Mark

Go to forum: Setup

25 Reply by mark0300

(10 replies, posted in Report Bugs here)

@apmuthu,

Thanks for your quick reply.  When I void a payment, the corresponding invoice became outstanding.  And I verified and confirmed this.   I did not void an invoice prior to the payment.  That is, I voided the payment first.  Even though I did not void the invoice, this error will still appear.

I checked the ledger, when the customer payment is voided, it disappeared from the GL entry.

/Mark

Go to forum: Report Bugs here