RETURNS bool AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
+
+/* performance vs. history:
+ versions prior to xTuple ERP 4.5.x (x ?= 1) used public.flrpt
+ to build financial reports. after that release, the function
+ creates a temporary table if necessary. with the temp table,
+ flrpt self-joins no longer require comparing flrpt_username -
+ temp tables are only visible within the current session. however,
+ there are subsidiary functions (eg insertflgroup) that _do_ join
+ on flrpt_username. in addition, flrpt_username has a not-null constraint.
+ to reduce the risk of breakage, this function still sets flrpt_username.
+*/
DECLARE
pFlheadid ALIAS FOR $1;
pPeriodid ALIAS FOR $2;
_r RECORD;
_t RECORD;
_s RECORD;
+ _username TEXT := getEffectiveXtUser();
BEGIN
RAISE EXCEPTION 'Invalid Interval --> %', pInterval;
END IF;
--- Get rid of any old reporting done by this user for the specified criteria
+ CREATE TEMPORARY TABLE IF NOT EXISTS flrpt
+ (LIKE public.flrpt INCLUDING ALL)
+ ON COMMIT PRESERVE ROWS;
+
+ -- clear even temp tables because every run builds on data upserted by itself
DELETE FROM flrpt
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodId)
- AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()));
+ AND (flrpt_interval=pInterval));
-- Find out if we need to show a Grand Total and which if any of the values
-- we want to show in that grand total.
flrpt_beginning, flrpt_ending,
flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff,
flrpt_custom, flrpt_altname, flrpt_interval )
- VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
+ VALUES (pFlheadid, pPeriodid, _username,
0, -1, 'G', -1,
_r.beginning, _r.ending,
_r.debits, _r.credits, _r.budget, _r.diff,
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_type='G')
AND (flrpt_type_id=flgrp_id))
UNION
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_type='I')
AND (flrpt_type_id=flitem_id))
UNION
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_type='S')
AND (flrpt_type_id=flspec_id)) LOOP
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_type != 'T')
AND (flrpt_parent_id=_t.flgrp_id));
ELSE
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_type = 'G')
AND (flrpt_type_id=_t.flgrp_id));
END IF;
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_order=_t.flrpt_order));
END LOOP;
WHERE ((a.flrpt_flhead_id=pFlheadid)
AND (a.flrpt_period_id=pPeriodid)
AND (a.flrpt_interval=pInterval)
- AND (a.flrpt_username=getEffectiveXtUser())
AND (a.flrpt_type='T')
AND (b.flrpt_flhead_id=a.flrpt_flhead_id)
AND (b.flrpt_period_id=a.flrpt_period_id)
AND (b.flrpt_interval=pInterval)
- AND (b.flrpt_username=a.flrpt_username)
AND (b.flrpt_type='G')
AND (b.flrpt_type_id=a.flrpt_parent_id)) LOOP
UPDATE flrpt SET flrpt_beginningprcnt=flrpt_beginningprcnt + _t.flrpt_beginningprcnt,
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_order=_t.flrpt_order));
END LOOP;
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+ )
), 0) + 1,
flrpt_level = 0,
flrpt_type = 'T'
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_order=0)
AND (flrpt_level = -1)
AND (flrpt_type = 'G')
RETURNS SETOF flstmtitem AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
+-- see "performance vs. history" in financialreport(integer,integer,bpchar,integer)
DECLARE
pFlcolid ALIAS FOR $1;
pPeriodid ALIAS FOR $2;
_prevlevel INTEGER;
_subgrp INTEGER;
_qtrInterval TEXT;
+--_username TEXT := getEffectiveXtUser();
_yrInterval TEXT;
BEGIN
_yrInterval := 'Y';
END IF;
---Delete old data from all periods
+ CREATE TEMPORARY TABLE IF NOT EXISTS flrpt
+ (LIKE public.flrpt INCLUDING ALL)
+ ON COMMIT PRESERVE ROWS;
+
+ -- clear even temp tables because every run builds on data upserted by itself
DELETE FROM flrpt
- WHERE ((flrpt_username=getEffectiveXtUser())
- AND (flrpt_flhead_id=_p.flhead_id));
+ WHERE (flrpt_flhead_id=_p.flhead_id);
--Populate report data...
--...for Month
- IF (_p.flcol_month) THEN
+ IF (_p.flcol_month) THEN
PERFORM financialreport(_p.flhead_id,pPeriodid,'M',pPrjid);
WHERE ((flrpt_type='G')
AND (flrpt_flhead_id=_p.flhead_id)
AND (flrpt_period_id=pPeriodId)
- AND (flrpt_username=getEffectiveXtUser()))) AS flrpt
+ )) AS flrpt
LEFT OUTER JOIN flrpt flrptmo
ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
AND (flrptmo.flrpt_type_id=flrpt.flrpt_type_id)
AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptmo.flrpt_interval='M')
- AND (flrptmo.flrpt_username=flrpt.flrpt_username)
AND (flrptmo.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptqt
ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptqt.flrpt_interval=_qtrInterval)
- AND (flrptqt.flrpt_username=flrpt.flrpt_username)
AND (flrptqt.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptyr
ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptyr.flrpt_interval=_yrInterval)
- AND (flrptyr.flrpt_username=flrpt.flrpt_username)
AND (flrptyr.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptprmo
ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
AND (flrptprmo.flrpt_interval='M')
- AND (flrptprmo.flrpt_username=flrpt.flrpt_username)
AND (flrptprmo.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptprqt
ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
AND (flrptprqt.flrpt_interval='Q')
- AND (flrptprqt.flrpt_username=flrpt.flrpt_username)
AND (flrptprqt.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptpryr
ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
AND (flrptpryr.flrpt_interval='Y')
- AND (flrptpryr.flrpt_username=flrpt.flrpt_username)
AND (flrptpryr.flrpt_order=flrpt.flrpt_order))
WHERE ((flgrp_id = flrpt.flrpt_type_id)
AND (flhead_id = flgrp_flhead_id))
WHERE ((flrpt_type='I')
AND (flrpt_flhead_id=_p.flhead_id)
AND (flrpt_period_id=pPeriodid)
- AND (flrpt_username=getEffectiveXtUser())
AND (accnt_id=flrpt_accnt_id))) AS flrpt
LEFT OUTER JOIN flrpt flrptmo
ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptmo.flrpt_interval='M')
- AND (flrptmo.flrpt_username=flrpt.flrpt_username)
AND (flrptmo.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptqt
ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptqt.flrpt_interval=_qtrInterval)
- AND (flrptqt.flrpt_username=flrpt.flrpt_username)
AND (flrptqt.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptyr
ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptyr.flrpt_interval=_yrInterval)
- AND (flrptyr.flrpt_username=flrpt.flrpt_username)
AND (flrptyr.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptprmo
ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
AND (flrptprmo.flrpt_interval='M')
- AND (flrptprmo.flrpt_username=flrpt.flrpt_username)
AND (flrptprmo.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptprqt
ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
AND (flrptprqt.flrpt_interval='Q')
- AND (flrptprqt.flrpt_username=flrpt.flrpt_username)
AND (flrptprqt.flrpt_order=flrpt.flrpt_order))
LEFT OUTER JOIN flrpt flrptpryr
ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
AND (flrptpryr.flrpt_interval='Y')
- AND (flrptpryr.flrpt_username=flrpt.flrpt_username)
AND (flrptpryr.flrpt_order=flrpt.flrpt_order) )
WHERE ((flitem_id = flrpt.flrpt_type_id)
AND (flhead_id = flitem_flhead_id))
WHERE ((NOT (flrpt_type IN ('G','I','S')))
AND (flrpt_flhead_id=_p.flhead_id)
AND (flrpt_period_id=pPeriodId)
- AND (flrpt_username=getEffectiveXtUser()))) AS flrpt
+ )) AS flrpt
LEFT OUTER JOIN flrpt flrptmo
ON ((flrptmo.flrpt_type=flrpt.flrpt_type)
AND (flrptmo.flrpt_order=flrpt.flrpt_order)
AND (flrptmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptmo.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptmo.flrpt_interval='M')
- AND (flrptmo.flrpt_username=flrpt.flrpt_username))
+ )
LEFT OUTER JOIN flrpt flrptqt
ON ((flrptqt.flrpt_type=flrpt.flrpt_type)
AND (flrptqt.flrpt_order=flrpt.flrpt_order)
AND (flrptqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptqt.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptqt.flrpt_interval=_qtrInterval)
- AND (flrptqt.flrpt_username=flrpt.flrpt_username))
+ )
LEFT OUTER JOIN flrpt flrptyr
ON ((flrptyr.flrpt_type=flrpt.flrpt_type)
AND (flrptyr.flrpt_order=flrpt.flrpt_order)
AND (flrptyr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptyr.flrpt_period_id=flrpt.flrpt_period_id)
AND (flrptyr.flrpt_interval=_yrInterval)
- AND (flrptyr.flrpt_username=flrpt.flrpt_username))
+ )
LEFT OUTER JOIN flrpt flrptprmo
ON ((flrptprmo.flrpt_type=flrpt.flrpt_type)
AND (flrptprmo.flrpt_order=flrpt.flrpt_order)
AND (flrptprmo.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprmo.flrpt_period_id=_priorMoPeriodId)
AND (flrptprmo.flrpt_interval='M')
- AND (flrptprmo.flrpt_username=flrpt.flrpt_username))
+ )
LEFT OUTER JOIN flrpt flrptprqt
ON ((flrptprqt.flrpt_type=flrpt.flrpt_type)
AND (flrptprqt.flrpt_order=flrpt.flrpt_order)
AND (flrptprqt.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptprqt.flrpt_period_id=_priorQtPeriodId)
AND (flrptprqt.flrpt_interval='Q')
- AND (flrptprqt.flrpt_username=flrpt.flrpt_username))
+ )
LEFT OUTER JOIN flrpt flrptpryr
ON ((flrptpryr.flrpt_type=flrpt.flrpt_type)
AND (flrptpryr.flrpt_order=flrpt.flrpt_order)
AND (flrptpryr.flrpt_flhead_id=flrpt.flrpt_flhead_id)
AND (flrptpryr.flrpt_period_id=_priorYrPeriodId)
AND (flrptpryr.flrpt_interval='Y')
- AND (flrptpryr.flrpt_username=flrpt.flrpt_username))
+ )
WHERE (flhead_id=flrpt.flrpt_flhead_id)
ORDER BY flstmtitem_order
LOOP
AND (flrpt_type='G')
AND (flrpt_period_id=pPeriodIds[1])
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+ )
UNION
SELECT flrpt_flhead_id,
flrpt_username,
AND (flrpt_type='I')
AND (flrpt_period_id=pPeriodIds[1])
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+ )
UNION
SELECT flrpt_flhead_id,
flrpt_username,
AND (flrpt_type NOT IN ('I','S','G'))
AND (flrpt_period_id=pPeriodIds[1])
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+ )
ORDER BY flrpt_order
LOOP
WHERE ((flrpt_flhead_id=pFlheadId)
AND (flrpt_period_id=pPeriodIds[_i])
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
AND (flrpt_order=_p.flrpt_order));
_fld[_i-1] := _n;
IF _type IN ('I','C') THEN
+SELECT dropIfExists('FUNCTION', 'insertFlGroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN)');
+SELECT dropIfExists('FUNCTION', 'insertFlGroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN, CHAR)');
+SELECT dropIfExists('FUNCTION', 'insertFlGroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN, CHAR, INTEGER)');
-
-CREATE OR REPLACE FUNCTION insertFlGroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN) RETURNS BOOLEAN AS $$
+CREATE OR REPLACE FUNCTION insertflgroup(pFlheadid INTEGER
+ , pPeriodid INTEGER
+ , pFlgrpid INTEGER
+ , pLevel INTEGER
+ , pSummarize BOOLEAN
+ , pInterval CHAR DEFAULT NULL
+ , pPrjid INTEGER DEFAULT NULL) RETURNS BOOLEAN AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
-DECLARE
- pFlheadid ALIAS FOR $1;
- pPeriodid ALIAS FOR $2;
- pFlgrpid ALIAS FOR $3;
- pLevel ALIAS FOR $4;
- pSummarize ALIAS FOR $5;
-BEGIN
- RETURN insertFlGroup(pFlheadid, pPeriodid, pFlgrpid, pLevel, pSummarize, NULL);
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION insertFlGroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN, CHAR) RETURNS BOOLEAN AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
--- See www.xtuple.com/CPAL for the full text of the software license.
+/* see the "performance vs. history" comment in financialreport() */
DECLARE
- pFlheadid ALIAS FOR $1;
- pPeriodid ALIAS FOR $2;
- pFlgrpid ALIAS FOR $3;
- pLevel ALIAS FOR $4;
- pSummarize ALIAS FOR $5;
- pInterval ALIAS FOR $6;
-
-BEGIN
- RETURN insertFlGroup(pFlheadid, pPeriodid, pFlgrpid, pLevel, pSummarize, pInterval, NULL);
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION insertflgroup(INTEGER, INTEGER, INTEGER, INTEGER, BOOLEAN, CHAR, INTEGER) RETURNS BOOLEAN AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
--- See www.xtuple.com/CPAL for the full text of the software license.
-DECLARE
- pFlheadid ALIAS FOR $1;
- pPeriodid ALIAS FOR $2;
- pFlgrpid ALIAS FOR $3;
- pLevel ALIAS FOR $4;
- pSummarize ALIAS FOR $5;
- pInterval ALIAS FOR $6;
- pPrjid ALIAS FOR $7;
-
_subtotal BOOLEAN;
_r RECORD;
_g RECORD;
_all BOOLEAN;
+ _username TEXT := getEffectiveXtUser();
BEGIN
flrpt_beginningprcnt, flrpt_endingprcnt,
flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
flrpt_parent_id, flrpt_interval)
- VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
+ VALUES (pFlheadid, pPeriodid, _username,
(COALESCE(( SELECT MAX(flrpt_order)
FROM flrpt
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+-- AND (flrpt_username=_username)
+ )
), 1) + 1),
pLevel, _r.type, _r.type_id,
_r.beginning, _r.ending,
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type=_r.type)
AND (flrpt_type_id=_r.type_id));
IF (_r.subtract) THEN
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type='G')
AND (flrpt_type_id=pFlgrpid));
ELSE
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type='G')
AND (flrpt_type_id=pFlgrpid));
END IF;
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type=_r.type)
AND (flrpt_type_id=_r.type_id));
END IF;
flrpt_beginningprcnt, flrpt_endingprcnt,
flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
flrpt_parent_id,flrpt_accnt_id,flrpt_interval)
- VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
+ VALUES (pFlheadid, pPeriodid, _username,
(COALESCE(( SELECT MAX(flrpt_order)
FROM flrpt
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+-- AND (flrpt_username=_username)
+ )
), 1) + 1),
pLevel, _r.type, _r.type_id,
_r.beginning, _r.ending,
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type='G')
AND (flrpt_type_id=pFlgrpid));
ELSE
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_interval=pInterval)
AND (flrpt_period_id=pPeriodid)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type='G')
AND (flrpt_type_id=pFlgrpid));
END IF;
flrpt_beginningprcnt, flrpt_endingprcnt,
flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
flrpt_parent_id, flrpt_altname,flrpt_interval )
- SELECT pFlheadid, pPeriodid, getEffectiveXtUser(),
+ SELECT pFlheadid, pPeriodid, _username,
(COALESCE(( SELECT MAX(flrpt_order)
FROM flrpt
WHERE ((flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser()))
+-- AND (flrpt_username=_username)
+ )
), 1) + 1),
pLevel, 'T', -1,
CASE WHEN (flgrp_showstart) THEN flrpt_beginning
AND (flrpt_flhead_id=pFlheadid)
AND (flrpt_period_id=pPeriodid)
AND (flrpt_interval=pInterval)
- AND (flrpt_username=getEffectiveXtUser())
+-- AND (flrpt_username=_username)
AND (flrpt_type='G')
AND (flrpt_type_id=pFlgrpid));
END IF;
--- /dev/null
+/*jshint trailing:true, white:true, indent:2, strict:true, curly:true,
+ immed:true, eqeqeq:true, forin:true, latedef:true,
+ newcap:true, noarg:true, undef:true */
+/*global XT:true, describe:true, it:true, require:true, __dirname:true, before:true */
+
+var _ = require("underscore"),
+ assert = require('chai').assert,
+ path = require('path');
+
+(function () {
+ "use strict";
+
+ describe('The financialReport function', function () {
+
+ var loginData = require(path.join(__dirname, "../lib/login_data.js")).data,
+ datasource = require('../../../xtuple/node-datasource/lib/ext/datasource').dataSource,
+ config = require(path.join(__dirname, "../../node-datasource/config.js")),
+ creds = _.extend({}, config.databaseServer, {database: loginData.org}),
+ oldval = -98.76, // makes it easy to spot in test failures
+ changeval = 123.45,
+ freSql = "select financialreport(flhead_id, period_id, 'M', -1) as fr"
+ + " from flhead, period"
+ + " where flhead_name = 'Basic Balance Sheet'"
+ + " and current_date between period_start and period_end;",
+ valueSql = " select cast(sum(flrpt_ending) as text) as value"
+ + " from flrpt"
+ + " join accnt on flrpt_accnt_id = accnt_id"
+ + " where accnt_descrip = 'Cash at eBank'"
+ + ";";
+ ;
+ this.timeout(10*1000); // the fre ain't speedy
+
+ it("should generate financial report data", function (done) {
+ datasource.query(freSql, creds, function (err, res) {
+ assert.equal(res.rowCount, 1);
+ assert.isTrue(res.rows[0].fr);
+ done();
+ });
+ });
+
+ it("should get the starting net asset value", function (done) {
+ datasource.query(valueSql, creds, function (err, res) {
+ assert.equal(res.rowCount, 1);
+ oldval = Number(res.rows[0].value);
+ done();
+ });
+ });
+
+ it("should insert a gl transaction", function (done) {
+ var sql = "select insertgltransaction('G/L', 'ST', 'FREtest',"
+ + " 'testing financialreport()', cr.accnt_id, dr.accnt_id,"
+ + " -1, " + changeval + ", period_end) AS result"
+ + " from accnt cr, accnt dr, period"
+ + " where cr.accnt_descrip = 'Cash at eBank'"
+ + " and dr.accnt_descrip = 'Deferred Revenue'"
+ + " and current_date between period_start and period_end;"
+ + valueSql;
+ datasource.query(sql, creds, function (err, res) {
+ assert.equal(res.rowCount, 1);
+ assert.isTrue(res.rows[0].result > 0);
+ done();
+ });
+ });
+
+ it("should regenerate financial report data", function (done) {
+ datasource.query(freSql, creds, function (err, res) {
+ assert.equal(res.rowCount, 1);
+ assert.isTrue(res.rows[0].fr);
+ done();
+ });
+ });
+
+ it("should show a net asset value change", function (done) {
+ datasource.query(valueSql, creds, function (err, res) {
+ assert.equal(res.rowCount, 1);
+ var newval = Number(res.rows[0].value);
+ assert.closeTo(oldval - newval, changeval, 0.001);
+ done();
+ });
+ });
+
+ after(function (done) {
+ done(); // is there anything to clean up?
+ });
+
+ });
+}());
+
+
+
projects / xtuple / commitdiff