[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3440] trunk/sql
- Subject: SF.net SVN: ledger-smb:[3440] trunk/sql
- From: ..hidden..
- Date: Tue, 05 Jul 2011 08:48:04 +0000
Revision: 3440
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3440&view=rev
Author: einhverfr
Date: 2011-07-05 08:48:03 +0000 (Tue, 05 Jul 2011)
Log Message:
-----------
Merging Util.sql from addons into main
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Date.sql
trunk/sql/modules/LOADORDER
Added Paths:
-----------
trunk/sql/modules/Util.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-07-05 04:06:36 UTC (rev 3439)
+++ trunk/sql/Pg-database.sql 2011-07-05 08:48:03 UTC (rev 3440)
@@ -3436,4 +3436,15 @@
COMMENT ON TABLE tax_extended IS
$$ This stores extended information for manual tax calculations.$$;
+CREATE OR REPLACE VIEW periods AS
+SELECT 'ytd' as id, 'Year to Date' as label, now()::date as date_to,
+ (extract('year' from now())::text || '-01-01')::date as date_from
+UNION
+SELECT 'last_year', 'Last Year',
+ ((extract('YEAR' from now()) - 1)::text || '-12-31')::date as date_to,
+ ((extract('YEAR' from now()) - 1)::text || '-01-01')::date as date_from
+;
+
+GRANT SELECT ON periods TO public;
+
commit;
Modified: trunk/sql/modules/Date.sql
===================================================================
--- trunk/sql/modules/Date.sql 2011-07-05 04:06:36 UTC (rev 3439)
+++ trunk/sql/modules/Date.sql 2011-07-05 08:48:03 UTC (rev 3440)
@@ -23,3 +23,126 @@
$$ language plpgsql;
COMMENT ON FUNCTION date_get_all_years() IS
$$ This function return each year inside transdate in transactions. $$;
+
+CREATE OR REPLACE FUNCTION is_leapyear(in_date date) returns bool as
+$$
+ select extract('day' FROM (
+ (extract('year' FROM $1)::text
+ || '-02-28')::date + '1 day'::interval)::date)
+ = 29;
+$$ language sql;
+
+COMMENT ON FUNCTION is_leapyear(in_date date) IS
+$$ Returns true if date is in a leapyear. False if not. Uses the built-in
+PostgreSQL date handling, and no direct detection is done in our code.$$;
+
+CREATE OR REPLACE FUNCTION leap_days(in_year_from int, in_year_to int)
+RETURNS int AS
+$$
+ SELECT count(*)::int
+ FROM generate_series($1, $2)
+ WHERE is_leapyear((generate_series::text || '-01-01')::date);
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION leap_days(in_year_from int, in_year_to int) IS
+$$Returns the number of leap years between the two year inputs, inclusive.$$;
+
+CREATE OR REPLACE FUNCTION next_leap_year_calc(in_date date, is_end bool)
+returns int as
+$$
+SELECT
+ (CASE WHEN extract('doy' FROM $1) < 59
+ THEN extract('year' FROM $1)
+ ELSE extract('year' FROM $1) + 1
+ END)::int
+ -
+ CASE WHEN $2 THEN 1 ELSE 0 END;
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION next_leap_year_calc(in_date date, is_end bool) IS
+$$Next relevant leap year calculation for a daily depreciation calculation$$;
+
+CREATE OR REPLACE FUNCTION get_fractional_year
+(in_date_from date, in_date_to date)
+RETURNS numeric AS
+$$
+ select ($2 - $1
+ - leap_days(next_leap_year_calc($1, false),
+ next_leap_year_calc($2, true)))
+ /365::numeric;
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION get_fractional_year (in_date_from date, in_date_to date) IS
+$$ Returns the decimal representation of the fractional year.$$;
+
+CREATE OR REPLACE FUNCTION days_in_month(in_date date)
+returns int AS
+$$
+SELECT (extract(DOM FROM date_trunc('month', $1)
+ + '1 month - 1 second'::interval)
+ )::int;
+
+$$ language sql;
+
+COMMENT ON FUNCTION days_in_month(in_date date) IS
+$$ Returns the number of days in the month that includes in_date.$$;
+
+CREATE OR REPLACE FUNCTION is_same_year (in_date1 date, in_date2 date)
+returns bool as
+$$
+SELECT extract ('YEAR' from $1) = extract ('YEAR' from $2);
+$$ language sql;
+
+COMMENT ON FUNCTION is_same_year (in_date1 date, in_date2 date) IS
+$$ Returns true if the two dates are in the same year, false otherwise.$$;
+
+CREATE OR REPLACE FUNCTION is_same_month (in_date1 date, in_date2 date)
+returns bool as
+$$
+SELECT is_same_year($1, $2)
+ and extract ('MONTH' from $1) = extract ('MONTH' from $2);
+$$ language sql;
+
+COMMENT ON FUNCTION is_same_month (in_date1 date, in_date2 date) IS
+$$ Returns true if the two dates are in the same month and year. False
+otherwise.$$;
+
+CREATE OR REPLACE FUNCTION get_fractional_month
+(in_date_first date, in_date_second date)
+RETURNS NUMERIC AS
+$$
+SELECT CASE WHEN is_same_month($1, $2)
+ THEN ($2 - $1)::numeric
+ / days_in_month($1)
+ ELSE (get_fractional_month(
+ $1, (date_trunc('MONTH', $1)
+ + '1 month - 1 second'::interval)::date)
+ + get_fractional_month(date_trunc('MONTH', $2)::date, $2)
+ + (extract ('YEAR' from $2) - extract ('YEAR' from $1) * 12)
+ + extract ('MONTH' from $1) - extract ('MONTH' from $2)
+ - 1)::numeric
+ END;
+$$ language sql;
+
+COMMENT ON FUNCTION get_fractional_month
+(in_date_first date, in_date_second date) IS
+$$ Returns the number of months between two dates in numeric form.$$;
+
+CREATE OR REPLACE FUNCTION periods_get()
+RETURNS SETOF periods
+AS
+$$
+SELECT * FROM periods ORDER BY id
+$$ language sql;
+
+COMMENT ON FUNCTION periods_get() IS
+$$ Returns dates for year to date, and last year.$$;
+
+CREATE OR REPLACE FUNCTION all_years() returns setof int AS
+$$
+SELECT DISTINCT EXTRACT ('YEAR' FROM transdate)::int
+FROM acc_trans;
+$$ language sql;
+
+COMMENT ON FUNCTION all_years() IS
+$$ Returns all years currently found in the database.$$;
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2011-07-05 04:06:36 UTC (rev 3439)
+++ trunk/sql/modules/LOADORDER 2011-07-05 08:48:03 UTC (rev 3440)
@@ -22,3 +22,4 @@
TaxForm.sql
1099_reports.sql
EndOfYear.sql
+Util.sql
Added: trunk/sql/modules/Util.sql
===================================================================
--- trunk/sql/modules/Util.sql (rev 0)
+++ trunk/sql/modules/Util.sql 2011-07-05 08:48:03 UTC (rev 3440)
@@ -0,0 +1,43 @@
+CREATE OR REPLACE FUNCTION je_set_default_lines(in_rowcount int) returns int
+as
+$$
+BEGIN
+ UPDATE menu_attribute set value = $1
+ where node_id = 74 and attribute='rowcount';
+
+ IF NOT FOUND THEN
+ INSERT INTO menu_attribute (node_id, attribute, value)
+ values (74, 'rowcount', $1);
+ END IF;
+ RETURN $1;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION je_get_default_lines() returns varchar as
+$$
+SELECT value FROM menu_attribute where node_id = 74 and attribute = 'rowcount';
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION department__list_all() RETURNS SETOF department AS
+$$
+SELECT * FROM department order by description;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION warehouse__list_all() RETURNS SETOF warehouse AS
+$$
+SELECT * FROM warehouse order by description;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION invoice__get_by_vendor_number
+(in_meta_nunber text, in_invoice_number text)
+RETURNS ap AS
+$$
+DECLARE retval ap;
+BEGIN
+ SELECT * INTO retval FROM ap WHERE entity_credit_id =
+ (select id from entity_credit_account where entity_class = 1
+ AND meta_number = in_meta_number)
+ AND invnumber = in_invoice_number;
+ RETURN retval;
+END;
+$$ LANGUAGE PLPGSQL;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.