Differences

This shows you the differences between two versions of the page.

--- update_fxpaid [2016/10/04 08:14] – sweitmann
+++ update_fxpaid [2024/09/25 06:21] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-   UPDATE ap SET fxamount = amount WHERE fxamount = 0;
-   UPDATE ap SET fxpaid = paid WHERE fxpaid = 0;
-   UPDATE ar SET fxamount = (SELECT SUM(amount) FROM acc_trans WHERE
+   SELECT invnumber, amount, paid, fxamount, fxpaid FROM ap WHERE amount=paid AND fxamount <>
-   chart_id IN (SELECT id FROM chart WHERE link LIKE '%AR_amount%' OR
+   fxpaid AND fxpaid <> 0 OR fxpaid is NULL;
-   link LIKE '%IC_income%' OR link LIKE '%tax%') AND trans_id=ar.id and
+   UPDATE ap SET fxamount = (SELECT SUM(0-amount) FROM acc_trans WHERE
+   chart_id IN (SELECT id FROM chart WHERE link LIKE '%AP_amount%' OR
+   link LIKE '%IC_expense%' OR link LIKE '%tax%') AND trans_id=ap.id and
    NOT fx_transaction);
-   UPDATE ar SET fxpaid = (SELECT SUM(0-amount) FROM acc_trans WHERE
+   UPDATE ap SET fxpaid = paid WHERE fxpaid is NULL;
+   UPDATE ar SET fxpaid = paid WHERE fxpaid is NULL;
+   UPDATE ap SET fxamount = fxpaid WHERE amount = paid;
+   UPDATE ar SET fxamount = fxpaid WHERE amount = paid;
+ Ignore below
+   UPDATE ap SET fxamount = amount WHERE fxamount = 0;
+   UPDATE ap SET fxpaid = paid WHERE fxpaid = 0;
+   UPDATE ap SET fxpaid = (SELECT SUM(amount) FROM acc_trans WHERE
    chart_id IN (SELECT id FROM chart WHERE link LIKE '%_paid%') AND
-   trans_id=ar.id AND NOT fx_transaction);
+   trans_id=ap.id AND NOT fx_transaction);
+   UPDATE ap SET fxamount=round(fxamount), fxpaid=ROUND(fxpaid);
+   UPDATE ap SET fxamount = fxpaid WHERE amount = paid;