--
-- PostgreSQL database dump
--
-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.2
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: account; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA account;
ALTER SCHEMA account OWNER TO postgres;
--
-- Name: att; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA att;
ALTER SCHEMA att OWNER TO postgres;
--
-- Name: invoice; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA invoice;
ALTER SCHEMA invoice OWNER TO postgres;
--
-- Name: stock; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA stock;
ALTER SCHEMA stock OWNER TO postgres;
--
-- Name: entries(integer); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.entries(iid integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT json_agg(json_build_object(
'id_', d.id,
'heading_id', iId,
'correlative', d.correlative,
'sub_account_id', d.sub_account_id,
'amount', d.amount,
'isdebit', d.isdebit,
'cost_center_id', d.cost_center_id,
'detail_sub_account_id', d.detail_sub_account_id
))::TEXT
INTO ret_val
FROM account.entries d WHERE heading_id=iId;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.entries(iid integer) OWNER TO postgres;
--
-- Name: insert_entries(text); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.insert_entries(elementos text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
WITH rows AS (
INSERT INTO account.entries (id,heading_id,correlative,sub_account_id,isdebit,cost_center_id,detail_sub_account_id,amount)
SELECT
(
CASE
WHEN id_::text = '' OR id_::INT = 0 THEN
nextval('account.entries_id_seq'::regclass)::integer
ELSE
id_
END
)
,heading_id,correlative,sub_account_id,isdebit,cost_center_id,detail_sub_account_id,amount
FROM jsonb_populate_recordset (NULL::account.entries, jsElementos)
WHERE heading_id IS NOT NULL AND heading_id > 0
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
--will expect id as detail id and not id_ as usually setup the json object has to use replace....
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.insert_entries(elementos text) OWNER TO postgres;
--
-- Name: insert_entries(text, integer); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.insert_entries(elementos text, iid integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
WITH rows AS (
INSERT INTO account.entries (id,heading_id,correlative,sub_account_id,isdebit,cost_center_id,detail_sub_account_id,amount)
SELECT
(
CASE
WHEN id_::text = '' OR id_::INT = 0 THEN
nextval('account.entries_id_seq'::regclass)::integer
ELSE
id_
END
)
,iId,correlative,sub_account_id,isdebit,cost_center_id,detail_sub_account_id,amount
FROM jsonb_populate_recordset (NULL::account.entries, jsElementos)
WHERE heading_id IS NOT NULL AND heading_id > 0
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
--will expect id as detail id and not iId as usually setup the json object has to use replace....
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.insert_entries(elementos text, iid integer) OWNER TO postgres;
--
-- Name: insert_heading(text); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.insert_heading(elementos text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_ INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
WITH rows AS (
INSERT INTO account.headings (id,number,date,summary,period_id)
SELECT
(
CASE
WHEN id::text = '' OR id::INT = 0 THEN
nextval('account.headings_id_seq'::regclass)::integer
ELSE
id
END
),
(
CASE
WHEN number::text = '' THEN
account.next_heading(TRUE)
ELSE
number
END
),
date,summary,period_id
FROM jsonb_populate_recordset (NULL::account.headings, jsElementos)
RETURNING id::int
)
SELECT id FROM rows INTO ret_;
RETURN ret_;
END;
$$;
ALTER FUNCTION account.insert_heading(elementos text) OWNER TO postgres;
--
-- Name: insert_transaction(text, text); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.insert_transaction(head text, detail text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
re INTEGER=0;
BEGIN
SELECT account.insert_heading(head) INTO ret_val;
SELECT account.insert_entries(detail,ret_val) INTO re;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.insert_transaction(head text, detail text) OWNER TO postgres;
--
-- Name: insert_update_delete_journal_entries(); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.insert_update_delete_journal_entries() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
entero integer=0;
n integer=0;
number_ text;--autonumbering
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.id IS NULL THEN
SELECT nextval('account.headings_id_seq'::regclass)::integer INTO entero;
ELSE
entero=NEW.id;
END IF;
IF NEW.number IS NOT NULL AND NEW.number <> '' THEN
number_=NEW.number;
ELSE
number_ = account.next_heading(TRUE);
NEW.number=number_;--for returning
END IF;
INSERT INTO account.headings
(id,number,date,summary,period_id)
VALUES
(entero,number_,NEW.date,NEW.summary,NEW.period_id);
IF NEW.entries IS NOT NULL AND NEW.entries::text <> '' THEN
SELECT account.insert_entries(NEW.entries,entero) INTO n;
END IF;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE account.headings SET
id=NEW.id,number=NEW.number,date=NEW.date,summary=NEW.summary,period_id=NEW.period_id
WHERE id=OLD.id;
IF NEW.entries IS NOT NULL AND NEW.entries::text <> '' AND OLD.id IS NOT NULL THEN
SELECT account.update_entries(NEW.entries,NEW.id) INTO n;
END IF;
entero=NEW.id;--for returning it
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM account.headings
WHERE id =OLD.id;
RETURN NULL;
END IF;
NEW.id=entero;--for returning it
RETURN NEW;
END;
$$;
ALTER FUNCTION account.insert_update_delete_journal_entries() OWNER TO postgres;
--
-- Name: next_heading(boolean); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.next_heading(flag boolean) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
num INTEGER=0;
ret_val TEXT;
BEGIN
SELECT value+1 INTO num FROM account.sequences WHERE name= 'PARTIDAS';
ret_val:= lpad(num::text,8,'0');
IF flag THEN
UPDATE account.sequences SET value=num WHERE name= 'PARTIDAS';
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.next_heading(flag boolean) OWNER TO postgres;
--
-- Name: subaccount_code(integer); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.subaccount_code(id_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text;
BEGIN
SELECT code::text INTO ret_val FROM account.sub_accounts WHERE id= id_;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.subaccount_code(id_ integer) OWNER TO postgres;
--
-- Name: subaccount_id(text); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.subaccount_id(code_ text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer;
BEGIN
SELECT id::int INTO ret_val FROM account.sub_accounts WHERE code= code_;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.subaccount_id(code_ text) OWNER TO postgres;
--
-- Name: update_entries(text, integer); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.update_entries(elementos text, id_ integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
BEGIN
DELETE FROM account.entries WHERE heading_id=id_;
SELECT account.insert_entries(elementos,id_) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.update_entries(elementos text, id_ integer) OWNER TO postgres;
--
-- Name: update_transaction(integer, text, text); Type: FUNCTION; Schema: account; Owner: postgres
--
CREATE FUNCTION account.update_transaction(id_ integer, head text, detail text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
re INTEGER=0;
BEGIN
IF id_ IS NOT NULL AND id_ <> 0 THEN
DELETE FROM account.headings WHERE id=id_;
END IF;
SELECT account.insert_heading(head) INTO ret_val;
SELECT account.insert_entries(detail,ret_val) INTO re;
RETURN ret_val;
END;
$$;
ALTER FUNCTION account.update_transaction(id_ integer, head text, detail text) OWNER TO postgres;
--
-- Name: date_time_(integer); Type: FUNCTION; Schema: att; Owner: postgres
--
CREATE FUNCTION att.date_time_(id_ integer) RETURNS timestamp without time zone
LANGUAGE plpgsql
AS $_$
DECLARE
dato timestamp without time zone;
BEGIN
SELECT date_time FROM att.attendance INTO dato WHERE id =$1;
RETURN dato;
END;
$_$;
ALTER FUNCTION att.date_time_(id_ integer) OWNER TO postgres;
--
-- Name: date_time_(timestamp without time zone); Type: FUNCTION; Schema: att; Owner: postgres
--
CREATE FUNCTION att.date_time_(id_ timestamp without time zone) RETURNS timestamp without time zone
LANGUAGE plpgsql
AS $_$
DECLARE
dato INTEGER;
BEGIN
SELECT date_time FROM att.attendance INTO dato WHERE id =$1;
RETURN dato;
END;
$_$;
ALTER FUNCTION att.date_time_(id_ timestamp without time zone) OWNER TO postgres;
--
-- Name: exit(integer); Type: FUNCTION; Schema: att; Owner: postgres
--
CREATE FUNCTION att.exit(id_ integer) RETURNS integer
LANGUAGE plpgsql
AS $_$
DECLARE
dato INTEGER;
user_ INTEGER;
BEGIN
SELECT user_id FROM att.attendance INTO user_ WHERE id=$1;
SELECT COALESCE(id,0) FROM att.attendance INTO dato WHERE user_id = user_
AND date_time IN (SELECT MIN(date_time) FROM att.attendance WHERE date_time>att.date_time_($1) AND user_id = user_)
AND enter=0;
RETURN dato;
END;
$_$;
ALTER FUNCTION att.exit(id_ integer) OWNER TO postgres;
--
-- Name: in_array(text, text[]); Type: FUNCTION; Schema: att; Owner: postgres
--
CREATE FUNCTION att.in_array(elem text, arr text[]) RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
RETURN arr @> array[elem];
END;
$$;
ALTER FUNCTION att.in_array(elem text, arr text[]) OWNER TO postgres;
--
-- Name: record_time_(integer, integer); Type: FUNCTION; Schema: att; Owner: postgres
--
CREATE FUNCTION att.record_time_(id_ integer, enter integer) RETURNS integer
LANGUAGE plpgsql
AS $_$
DECLARE
retval_ integer=10;
retval TIMESTAMP WITHOUT TIME ZONE;
BEGIN
retval = NOW();
INSERT INTO att.attendance
(user_id,enter,date_time,last_modified,rate)
VALUES
($1,$2,retval ,retval,(SELECT salary FROM public.usr_employee WHERE id=user_id)) ;
SELECT @@ROWCOUNT INTO retval;
RETURN retval_;
END;
$_$;
ALTER FUNCTION att.record_time_(id_ integer, enter integer) OWNER TO postgres;
--
-- Name: detail(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.detail(id_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT json_agg(json_build_object(
'id_', d.id,
'header_id', id_,
'correlative', d.correlative,
'item_id', d.item_id,
'concept', REPLACE(d.concept,'"','~PULG~'),
'unit_price', d.unit_price,
'quantity', d.quantity,
'amount', d.amount,
'exent', d.exent,
'discount', d.discount,
'discount_cur',round(d.quantity::numeric*d.unit_price::numeric-d.amount::numeric,4)
))::TEXT
INTO ret_val
FROM invoice.detail d WHERE header_id=id_;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.detail(id_ integer) OWNER TO postgres;
--
-- Name: detail_summ(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.detail_summ(id_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
arr text[];
i int;
max_int int=3;
n int=0;
BEGIN
SELECT COUNT(id) INTO n FROM invoice.detail WHERE header_id=id_;
IF n::BOOLEAN THEN
arr := ARRAY(
SELECT ROUND(quantity,0) || ' - ' || concept
FROM invoice.detail
WHERE header_id=id_
);
FOR i IN 1..array_length(arr,1) LOOP
IF LENGTH(arr[i])>75 THEN
arr[i] = SUBSTRING (arr[i] FROM 1 FOR 73) || '...';
END IF;
END LOOP;
ret_val = array_to_string(arr[1:max_int],';
');
IF array_length(arr,1)>max_int THEN
ret_val = ret_val || ';
etc...';
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.detail_summ(id_ integer) OWNER TO postgres;
--
-- Name: heading_id(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.heading_id(id_ integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
num INTEGER=0;
ret_val integer=0;
BEGIN
IF id_ IS NOT NULL AND id_::BOOL THEN
SELECT count(id) INTO num FROM invoice.headers WHERE id=id_ AND heading_id IS NOT NULL;
IF NUM::BOOL THEN
SELECT heading_id INTO ret_val FROM invoice.headers WHERE id=id_;
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.heading_id(id_ integer) OWNER TO postgres;
--
-- Name: insert_detail(text); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.insert_detail(elementos text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
WITH rows AS (
INSERT INTO invoice.detail (id,header_id,correlative,item_id,concept,unit_price,quantity,amount,exent,discount)
SELECT id_,header_id,correlative,item_id,concept,unit_price,quantity,amount,exent,discount
FROM jsonb_populate_recordset (NULL::invoice.detail, jsElementos)
--WHERE presentation_name IS NOT NULL AND presentation_name::text <> ''
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.insert_detail(elementos text) OWNER TO postgres;
--
-- Name: insert_invoice(text, text); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.insert_invoice(fields text, vals text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
query_ TEXT='';
entero INTEGER=0;
BEGIN
SELECT nextval('invoice.headers_id_seq'::regclass)::integer INTO entero;
query_='INSERT INTO invoice.invoices(id'||fields||')VALUES('||entero::TEXT||vals||')';
PERFORM(query_);
RETURN entero;
END;
$$;
ALTER FUNCTION invoice.insert_invoice(fields text, vals text) OWNER TO postgres;
--
-- Name: insert_stock_transactions(text, date); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.insert_stock_transactions(elementos text, fecha date) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
WITH rows AS (
INSERT INTO stock.transactions (date,heading_id,source_id,item_id,debit,description,quantity,total)
SELECT fecha,1,id,item_id,0,concept,quantity,stock.item_mean_cost(item_id)
FROM jsonb_populate_recordset (NULL::invoice.detail, jsElementos)
WHERE item_id IS NOT NULL AND item_id > 0
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
--will expect id as detail id and not id_ as usually setup the json object has to use replace....
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.insert_stock_transactions(elementos text, fecha date) OWNER TO postgres;
--
-- Name: insert_update_delete_invoice(); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.insert_update_delete_invoice() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
entero integer=0;
n integer=0;
active_ boolean=true;--defa
status_ integer=1;--defa
number_ text;--autonumbering
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.id IS NULL THEN
SELECT nextval('invoice.headers_id_seq'::regclass)::integer INTO entero;
ELSE
entero=NEW.id;
END IF;
IF NEW.number IS NOT NULL AND NEW.number <> '' THEN
number_=NEW.number;
ELSE
IF NEW.type IS NOT NULL THEN
number_ = invoice.next_invoice(NEW.type);
NEW.number=number_;--for returning
END IF;
END IF;
IF NEW.active IS NOT NULL THEN
active_=NEW.active;
END IF;
IF NEW.status IS NOT NULL THEN
status_=NEW.status;
END IF;
INSERT INTO invoice.headers
(id,type,status,active,date,number,heading_id,user_id,user_name,dui,nit,municipio,departamento,giro,registro,direccion,subtotal,iva,ivaret,tax_1,tax_2,exents,taxed,total,discount,total_text,owner_id)
VALUES
(entero,NEW.type,status_,active_,NEW.date,number_,NEW.heading_id,NEW.user_id,NEW.user_name,NEW.dui,NEW.nit,NEW.municipio,NEW.departamento,NEW.giro,NEW.registro,NEW.direccion,NEW.subtotal,NEW.iva,NEW.ivaret,NEW.tax_1,NEW.tax_2,NEW.exents,NEW.taxed,NEW.total,NEW.discount,NEW.total_text,NEW.owner_id);
IF NEW.detail IS NOT NULL AND NEW.detail::text <> '' THEN
SELECT invoice.update_detail(entero, NEW.detail) INTO n;
END IF;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE invoice.headers SET
id=NEW.id,type=NEW.type,status=NEW.status,active=NEW.active,date=NEW.date,number=NEW.number,heading_id=NEW.heading_id,user_id=NEW.user_id,user_name=NEW.user_name,dui=NEW.dui,nit=NEW.nit,municipio=NEW.municipio,departamento=NEW.departamento,giro=NEW.giro,registro=NEW.registro,direccion=NEW.direccion,subtotal=NEW.subtotal,iva=NEW.iva,ivaret=NEW.ivaret,tax_1=NEW.tax_1,tax_2=NEW.tax_2,exents=NEW.exents,taxed=NEW.taxed,total=NEW.total,discount=NEW.discount,total_text=NEW.total_text,owner_id=NEW.owner_id
WHERE id=OLD.id;
IF NEW.detail IS NOT NULL AND NEW.detail::text <> '' AND OLD.id IS NOT NULL THEN
SELECT invoice.update_detail(NEW.id, NEW.detail) INTO n;
END IF;
entero=NEW.id;--for returning it
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM invoice.headers
WHERE id =OLD.id;
RETURN NULL;
END IF;
NEW.id=entero;--for returning it
RETURN NEW;
END;
$$;
ALTER FUNCTION invoice.insert_update_delete_invoice() OWNER TO postgres;
--
-- Name: my_coalesce(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.my_coalesce(param integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer=0;
BEGIN
IF param IS NULL OR param = 0 THEN
ret_val = nextval('invoice.detail_id_seq'::regclass)::integer;
ELSE
ret_val = param;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.my_coalesce(param integer) OWNER TO postgres;
--
-- Name: my_coalesce(text); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.my_coalesce(param text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer=0;
BEGIN
IF param IS NULL OR param = '' THEN
ret_val = 0;
ELSE
ret_val = CAST(param AS INTEGER);
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.my_coalesce(param text) OWNER TO postgres;
--
-- Name: next_invoice(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.next_invoice(type_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN invoice.next_invoice(type_, true);
END;
$$;
ALTER FUNCTION invoice.next_invoice(type_ integer) OWNER TO postgres;
--
-- Name: next_invoice(integer, boolean); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.next_invoice(type_ integer, increm boolean) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
scode text='';
val text='';
ret_val text='';
BEGIN
SELECT
t.short_code INTO scode
FROM invoice.types t
WHERE t.id=type_;
SELECT
scode || LPAD((s.value::integer+1)::text,6,'0')
INTO ret_val
FROM public.sequences s
WHERE s.code = scode;
IF increm THEN
UPDATE public.sequences SET value=(value::integer+1)::text WHERE sequences.code = scode;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.next_invoice(type_ integer, increm boolean) OWNER TO postgres;
--
-- Name: next_invoice_dep(integer); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.next_invoice_dep(type_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT
t.short_code || COALESCE(LPAD((1+max(invoice.my_coalesce(h.number)))::TEXT,6,'0'),'000001')
INTO ret_val
FROM invoice.headers h
LEFT JOIN invoice.types t ON t.id = h.type
WHERE h.type=type_;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.next_invoice_dep(type_ integer) OWNER TO postgres;
--
-- Name: update_detail(integer, text); Type: FUNCTION; Schema: invoice; Owner: postgres
--
CREATE FUNCTION invoice.update_detail(header integer, elementos text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
DELETE FROM invoice.detail WHERE header_id=header;
WITH rows AS (
INSERT INTO invoice.detail (id,header_id,correlative,item_id,concept,unit_price,quantity,amount,exent,discount)
SELECT (
CASE
WHEN id IS NULL OR id = 0 THEN
nextval('invoice.detail_id_seq'::regclass)::integer
ELSE
id
END)
,header,correlative,
(
CASE
WHEN item_id::text = '' THEN
NULL
ELSE
item_id
END)
,concept,unit_price,quantity,amount,exent,discount
FROM jsonb_populate_recordset (NULL::invoice.detail, jsElementos)
--WHERE presentation_name IS NOT NULL AND presentation_name::text <> ''
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION invoice.update_detail(header integer, elementos text) OWNER TO postgres;
--
-- Name: address(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.address(employee integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,3,1) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.address(employee integer) OWNER TO postgres;
--
-- Name: address(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.address(employee integer, sort_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,3,sort_) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.address(employee integer, sort_ integer) OWNER TO postgres;
--
-- Name: data_(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.data_(title integer, tupe_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT json_agg(json_build_object(
'id', t.id,
'person_id', t.person_id,
'type', t.type,
'content', t.content,
'sort', t.sort
))::TEXT
INTO ret_val
FROM usr_x_data t WHERE person_id=title and type=tupe_;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.data_(title integer, tupe_ integer) OWNER TO postgres;
--
-- Name: decode_quote(text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.decode_quote(str text) RETURNS text
LANGUAGE plpgsql
AS $_$
DECLARE
ret_val text='';
BEGIN
SELECT REPLACE($1,'xsigleQuoTex','''') INTO ret_val;
RETURN ret_val;
END
$_$;
ALTER FUNCTION public.decode_quote(str text) OWNER TO postgres;
--
-- Name: email(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.email(employee integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,1,1) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.email(employee integer) OWNER TO postgres;
--
-- Name: email(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.email(employee integer, sort_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,1,sort_) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.email(employee integer, sort_ integer) OWNER TO postgres;
--
-- Name: format_for_search(text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.format_for_search(code_ text) RETURNS text
LANGUAGE plpgsql
AS $_$
DECLARE
retval text;
BEGIN
retval='';
retval=upper($1);
retval=replace(retval,'Á','A');
retval=replace(retval,'É','E');
retval=replace(retval,'Í','I');
retval=replace(retval,'Ó','O');
retval=replace(retval,'Ú','U');
retval=replace(retval,'Ñ','N');
RETURN retval;
END;
$_$;
ALTER FUNCTION public.format_for_search(code_ text) OWNER TO postgres;
--
-- Name: full_name(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.full_name(id_ integer) RETURNS text
LANGUAGE plpgsql
AS $_$
DECLARE
ret_val text='';
BEGIN
SELECT public.full_name(firstname,lastname) INTO ret_val FROM public.usr_persons WHERE public.usr_persons.id=$1;
RETURN ret_val;
END;
$_$;
ALTER FUNCTION public.full_name(id_ integer) OWNER TO postgres;
--
-- Name: full_name(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.full_name(firstname text, lastname text) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.full_name(firstname,lastname,null) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.full_name(firstname text, lastname text) OWNER TO postgres;
--
-- Name: full_name(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.full_name(firstname text, lastname text, short_name text) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
IF firstname IS NOT NULL THEN
ret_val=firstname;
IF lastname IS NOT NULL THEN
ret_val=ret_val || ' ' || lastname;
IF short_name IS NOT NULL AND short_name <> '' THEN
ret_val=ret_val || '/' || short_name;
END IF;
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.full_name(firstname text, lastname text, short_name text) OWNER TO postgres;
--
-- Name: get_usr_data(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.get_usr_data(employee integer, data_type integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,data_type,1) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.get_usr_data(employee integer, data_type integer) OWNER TO postgres;
--
-- Name: get_usr_data(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.get_usr_data(employee integer, data_type integer, sort_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
n integer=0;
BEGIN
SELECT count(id) FROM usr_x_data INTO n WHERE person_id=employee AND type=data_type AND sort=sort_;
IF n::boolean THEN
SELECT content FROM usr_x_data INTO ret_val WHERE person_id=employee AND type=data_type AND sort=sort_;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.get_usr_data(employee integer, data_type integer, sort_ integer) OWNER TO postgres;
--
-- Name: insert_delete_usr_customers(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_delete_usr_customers() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_business WHERE NEW.code = usr_business.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
INSERT INTO usr_business (id, code, firstname, lastname,dui,nit,type,register_no,customer)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.dui,NEW.nit, NEW.type, NEW.register_no, TRUE;
ELSE --si ya está en la tabla maestra
UPDATE usr_business SET customer=TRUE
WHERE NEW.code = usr_business.code;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_business SET customer = FALSE
WHERE OLD.id = usr_business.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_delete_usr_customers() OWNER TO postgres;
--
-- Name: insert_delete_usr_suppliers(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_delete_usr_suppliers() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_business WHERE usr_business.code=NEW.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
INSERT INTO usr_business (id, code, firstname, lastname,dui,nit,type,register_no,supplier)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.dui,NEW.nit, NEW.type, NEW.register_no, TRUE;
ELSE --si ya está en la tabla maestra
UPDATE usr_business SET supplier=TRUE
WHERE usr_business.code=NEW.code;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_business SET supplier = FALSE
WHERE OLD.id = usr_business.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_delete_usr_suppliers() OWNER TO postgres;
--
-- Name: insert_lab(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_lab(keys_ text, vals_ text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer=0;
n integer=0;
query_ text='INSERT INTO usr_labs (id,';
BEGIN
IF keys_ IS NOT NULL AND keys_ <> '' AND vals_ IS NOT NULL AND vals_ <> '' THEN
SELECT nextval('usr_persons_id_seq'::regclass) INTO ret_val;
query_ = query_ || keys_ || ')VALUES(' || ret_val || ',' ||vals_ || ');';
EXECUTE query_;
SELECT count(id) INTO n FROM usr_labs WHERE id = ret_val;
IF NOT n::boolean THEN
ret_val=0;
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.insert_lab(keys_ text, vals_ text) OWNER TO postgres;
--
-- Name: insert_update_delete_usr_business(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_update_delete_usr_business() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
iCnt2 integer=0;
iCnt3 integer=0;
bUp boolean=true;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
IF NEW.id IS NULL THEN
iId=nextval('usr_persons_id_seq'::regclass);
ELSE
iId=NEW.id;
END IF;
INSERT INTO usr_persons (id, code, firstname, lastname,dui,nit)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.dui,NEW.nit;
INSERT INTO usr_x_business (id,type,register_no,supplier,customer)
SELECT iId, NEW.type, NEW.register_no, NEW.supplier, NEW.customer;
ELSE --si ya está en la tabla maestra
SELECT id INTO iId FROM usr_persons WHERE code=NEW.code;
SELECT count(id) INTO iCnt2 FROM usr_x_business WHERE id=iId;
IF iCnt2::BOOLEAN THEN --si ya está en la tabla de extensión
SELECT up INTO bUp FROM usr_x_business WHERE id=iId;
IF NOT bUp::BOOLEAN THEN --si ya existía pero estaba de baja lo dareos de alta y actualizaremos sus datos de la tabla extesión
DELETE FROM usr_x_business WHERE id=iId;--mejor borramos lo que hay
INSERT INTO usr_x_business (id,type,register_no,supplier,customer)
SELECT iId, NEW.type, NEW.register_no, NEW.supplier, NEW.customer;
END IF;
ELSE--si no existía lo agreramos
INSERT INTO usr_x_business (id,type,register_no,supplier,customer)
SELECT iId, NEW.type, NEW.register_no, NEW.supplier, NEW.customer;
END IF;
END IF;
IF NEW.emails IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.emails,1);
END IF;
IF NEW.phones IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.phones,2);
END IF;
IF NEW.addresses IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.addresses,3);
END IF;
ELSIF TG_OP = 'UPDATE' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code AND id<>OLD.id;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra ese code porque no es repetible
UPDATE usr_persons SET id=NEW.id, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname, dui=NEW.dui, nit=NEW.nit WHERE id=OLD.id;
UPDATE usr_x_business SET type=NEW.type, register_no=NEW.register_no,supplier=NEW.supplier,customer=NEW.customer WHERE id=OLD.id;
IF NEW.emails IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.emails,1);
END IF;
IF NEW.phones IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.phones,2);
END IF;
IF NEW.addresses IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.addresses,3);
END IF;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_x_business SET up = 0
WHERE OLD.id = usr_x_business.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_update_delete_usr_business() OWNER TO postgres;
--
-- Name: insert_update_delete_usr_employee(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_update_delete_usr_employee() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
iCnt2 integer=0;
iCnt3 integer=0;
bUp boolean=true;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
IF NEW.id IS NULL THEN
iId=nextval('usr_persons_id_seq'::regclass);
ELSE
iId=NEW.id;
END IF;
INSERT INTO usr_persons (id, code, firstname, lastname,dui,nit)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.dui,NEW.nit;
INSERT INTO usr_x_employee (id,position,salary)
SELECT iId, NEW.position, NEW.salary;
ELSE --si ya está en la tabla maestra
SELECT id INTO iId FROM usr_persons WHERE code=NEW.code;
SELECT count(id) INTO iCnt2 FROM usr_x_employee WHERE id=iId;
IF iCnt2::BOOLEAN THEN --si ya está en la tabla de extensión
SELECT up INTO bUp FROM usr_x_employee WHERE id=iId;
IF NOT bUp::BOOLEAN THEN --si ya existía pero estaba de baja lo dareos de alta y actualizaremos sus datos de la tabla extesión
DELETE FROM usr_x_employee WHERE id=iId;--mejor borramos lo que hay
INSERT INTO usr_x_employee (id,position,salary)
SELECT iId, NEW.position, NEW.salary;
ELSE
RETURN NULL;--si ya existía pero estaba de alta ignoraremos este comando INSERT
END IF;
ELSE--si no existía lo agreramos
INSERT INTO usr_x_employee (id,position,salary)
SELECT iId, NEW.position, NEW.salary;
END IF;
END IF;
IF NEW.emails IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.emails,1);
END IF;
IF NEW.phones IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.phones,2);
END IF;
IF NEW.addresses IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.addresses,3);
END IF;
ELSIF TG_OP = 'UPDATE' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code AND id<>OLD.id;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra ese code porque no es repetible
UPDATE usr_persons SET id=NEW.id, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname, dui=NEW.dui, nit=NEW.nit WHERE id=OLD.id;
UPDATE usr_x_employee SET position=NEW.position, salary=NEW.salary WHERE id=OLD.id;
IF NEW.emails IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.emails,1);
END IF;
IF NEW.phones IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.phones,2);
END IF;
IF NEW.addresses IS NOT NULL THEN
PERFORM update_usr_data_json(NEW.addresses,3);
END IF;
ELSE
RETURN NULL;--no es duplicable el code y se ignora la query
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_x_employee SET up = 0
WHERE OLD.id = usr_x_employee.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_update_delete_usr_employee() OWNER TO postgres;
--
-- Name: insert_update_delete_usr_labs(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_update_delete_usr_labs() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
iCnt2 integer=0;
iCnt3 integer=0;
bUp boolean=true;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code;
SELECT COALESCE(id,0) INTO iId FROM usr_persons WHERE code=NEW.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
raise notice 'entro';
IF NEW.id IS NULL THEN
iId=nextval('usr_persons_id_seq'::regclass);
ELSE
iId=NEW.id;
END IF;
INSERT INTO usr_persons (id, code, firstname, lastname,short_name,dui,nit,county,state,country)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname,NEW.short_name,NEW.dui,NEW.nit,NEW.county,NEW.state,NEW.country;
INSERT INTO usr_x_business (id,register_no,type)
SELECT iId, NEW.register_no, NEW.type;
INSERT INTO usr_x_labs (id)
SELECT iId;
ELSE --si ya está en la tabla maestra
SELECT count(id) INTO iCnt2 FROM usr_x_business WHERE id=iId;
IF iCnt2::BOOLEAN THEN --si ya está en la tabla de extensión
SELECT up INTO bUp FROM usr_x_business WHERE id=iId;
IF NOT bUp::BOOLEAN THEN --si ya existía pero estaba de baja lo dareos de alta y actualizaremos sus datos de la tabla extesión
DELETE FROM usr_x_business WHERE id=iId;--mejor borramos lo que hay
INSERT INTO usr_x_business (id,register_no,type)
SELECT iId, NEW.register_no, NEW.type;
END IF;
ELSE--si no existía lo agreramos
INSERT INTO usr_x_business (id,register_no,type)
SELECT iId, NEW.register_no, NEW.type;
END IF;
SELECT count(id) INTO iCnt3 FROM usr_x_labs WHERE id=iId;
IF iCnt3::BOOLEAN THEN --si ya está en la tabla de extensión
raise notice 'ya esta en x_labs';
SELECT up INTO bUp FROM usr_x_labs WHERE id=iId;
IF NOT bUp::BOOLEAN THEN --si ya existía pero estaba de baja lo dareos de alta y actualizaremos sus datos de la tabla extesión
DELETE FROM usr_x_labs WHERE id=iId;--mejor borramos lo que hay
INSERT INTO usr_x_labs (id)
SELECT iId;
END IF;
ELSE--si no existía lo agreramos
INSERT INTO usr_x_labs (id,up)
SELECT iId,1;
END IF;
END IF;
ELSIF TG_OP = 'UPDATE' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code AND id<>OLD.id;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra ese code porque no es repetible
UPDATE usr_persons SET id=NEW.id, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname, short_name=NEW.short_name, dui=NEW.dui, nit=NEW.nit, county=NEW.county, state=NEW.state, country=NEW.country WHERE id=OLD.id;
UPDATE usr_x_business SET register_no=NEW.register_no, type=NEW.type WHERE id=OLD.id;
--labs no lleva nada de actualizar
ELSE
RETURN NULL;--no es duplicable el code y se ignora la query
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_x_labs SET up = 0
WHERE OLD.id = usr_x_labs.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_update_delete_usr_labs() OWNER TO postgres;
--
-- Name: insert_update_delete_usr_sys(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.insert_update_delete_usr_sys() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
iId integer=0;
iCnt integer=0;
iCnt2 integer=0;
iCnt3 integer=0;
bUp boolean=true;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra
IF NEW.id IS NULL THEN
iId=nextval('usr_persons_id_seq'::regclass);
ELSE
iId=NEW.id;
END IF;
INSERT INTO usr_persons (id, code, firstname, lastname)
SELECT iId, NEW.code, NEW.firstname, NEW.lastname;
SELECT count(id) INTO iCnt3 FROM usr_x_sys WHERE username=NEW.username;
IF NOT iCnt3::BOOLEAN THEN --si ya existe username no es repetible en la tabla de extenxión
INSERT INTO usr_x_sys (id,username,"password",role_id)
SELECT iId, NEW.username, NEW.password, NEW.role_id;
ELSE
RETURN NULL;--no es duplicable el username y se ignora la query
END IF;
ELSE --si ya está en la tabla maestra
SELECT id INTO iId FROM usr_persons WHERE code=NEW.code;
SELECT count(id) INTO iCnt2 FROM usr_x_sys WHERE id=iId;
IF iCnt2::BOOLEAN THEN --si ya está en la tabla de extensión
SELECT up INTO bUp FROM usr_x_sys WHERE id=iId;
IF NOT bUp::BOOLEAN THEN --si ya existía pero estaba de baja lo dareos de alta y actualizaremos sus datos de la tabla extesión
DELETE FROM usr_x_sys WHERE id=iId;--mejor borramos lo que hay
SELECT count(id) INTO iCnt3 FROM usr_x_sys WHERE username=NEW.username;
IF NOT iCnt3::BOOLEAN THEN --si ya existe username no es repetible en la tabla de extenxión
INSERT INTO usr_x_sys (id,username,"password",role_id)
SELECT iId, NEW.username, NEW.password, NEW.role_id;
ELSE
RETURN NULL;--no es duplicable el username y se ignora la query
END IF;
ELSE
RETURN NULL;--si ya existía pero estaba de alta ignoraremos este comando INSERT
END IF;
ELSE--si no existía lo agreramos
SELECT count(id) INTO iCnt3 FROM usr_x_sys WHERE username=NEW.username;
IF NOT iCnt3::BOOLEAN THEN --si ya existe username no es repetible en la tabla de extenxión
INSERT INTO usr_x_sys (id,username,"password",role_id)
SELECT iId, NEW.username, NEW.password, NEW.role_id;
ELSE
RETURN NULL;--no es duplicable el username y se ignora la query
END IF;
END IF;
END IF;
ELSIF TG_OP = 'UPDATE' THEN
SELECT count(id) INTO iCnt FROM usr_persons WHERE code=NEW.code AND id<>OLD.id;
IF NOT iCnt::BOOLEAN THEN --si no está en la tabla maestra ese code porque no es repetible
SELECT count(id) INTO iCnt3 FROM usr_x_sys WHERE username=NEW.username AND id<>OLD.id;
IF NOT iCnt3::BOOLEAN THEN --si ya existe username no es repetible en la tabla de extenxión
UPDATE usr_persons SET id=NEW.id, code=NEW.code, firstname=NEW.firstname, lastname=NEW.lastname WHERE id=OLD.id;
UPDATE usr_x_sys SET username=NEW.username, password=NEW.password, role_id=NEW.role_id WHERE id=OLD.id;
ELSE
RETURN NULL;--no es duplicable el username y se ignora la query
END IF;
ELSE
RETURN NULL;--no es duplicable el username y se ignora la query
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE usr_x_sys SET up = 0
WHERE OLD.id = usr_x_sys.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION public.insert_update_delete_usr_sys() OWNER TO postgres;
--
-- Name: FUNCTION insert_update_delete_usr_sys(); Type: COMMENT; Schema: public; Owner: postgres
--
COMMENT ON FUNCTION public.insert_update_delete_usr_sys() IS '***INSER***
Se inserta en base al campo code.
Si code no existiera en la tabla maestra (usr_persons) hacemos una insersion completa en las dos tablas.
Si code ya existiera en la tabla maestra se mira en la tabla extension (usr_x_sys):
Si no está lo inserta normalmente en ésta.
Si ya extá aquí se ve si está de alta o de baja (campo up) y si está de alta solo lo ignora reornando NULL lo cual significaría que este INSERT es ignorado porque ya existía el registro (al menos con ese code).
Si estaba de baja (y ya existía) entonces se da de alta y se actualiza los datos de la tabla extensión.
Siempre que se hace un insert en la tabla extension se verifica que el username no vaya aconflictar pues es UNIQUE()
***DELETE***
Baja la bandera up en la tavbla extension y retorna NULL.
***UPDATE***
Acrualiza las dos tablas normalmente';
--
-- Name: last_day(date); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.last_day(fecha date) RETURNS date
LANGUAGE plpgsql
AS $$
DECLARE
ret_val date=fecha;
BEGIN
SELECT (date_trunc('month', fecha::date) + interval '1 month' - interval '1 day')::date INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.last_day(fecha date) OWNER TO postgres;
--
-- Name: password(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.password(id1 integer) RETURNS text
LANGUAGE plpgsql
AS $_$
DECLARE
relac text;
BEGIN
select "password" from public.usr_x_sys into relac where id=$1;
RETURN relac::text;
END;
$_$;
ALTER FUNCTION public.password(id1 integer) OWNER TO postgres;
--
-- Name: phone(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.phone(employee integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,2,1) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.phone(employee integer) OWNER TO postgres;
--
-- Name: phone(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.phone(employee integer, sort_ integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT public.get_usr_data(employee,2,sort_) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.phone(employee integer, sort_ integer) OWNER TO postgres;
--
-- Name: salario(numeric, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.salario(employee numeric, period text) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val NUMERIC=0;
BEGIN
SELECT salary FROM usr_employee INTO ret_val WHERE id=employee;
IF period='dia' THEN
ret_val=ret_val*8;
ELSIF period='mes' THEN
ret_val=ret_val*30*8;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.salario(employee numeric, period text) OWNER TO postgres;
--
-- Name: update_usr_data(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.update_usr_data(employee integer, data_ text, data_type integer) RETURNS boolean
LANGUAGE plpgsql
AS $$
DECLARE
ret_val boolean=false;
n integer=0;
BEGIN
IF data_ IS NOT NULL THEN
DELETE FROM usr_x_data WHERE person_id=employee AND type=data_type AND sort=1;
IF data_ <> '' THEN
INSERT INTO usr_x_data (content,person_id,type,sort)VALUES(data_,employee,data_type,1);
END IF;
ret_val=true;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.update_usr_data(employee integer, data_ text, data_type integer) OWNER TO postgres;
--
-- Name: update_usr_data(integer, text, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.update_usr_data(employee integer, data_ text, data_type integer, sort_ integer) RETURNS boolean
LANGUAGE plpgsql
AS $$
DECLARE
ret_val boolean=false;
n integer=0;
BEGIN
IF data_ IS NOT NULL THEN
DELETE FROM usr_x_data WHERE person_id=employee AND type=data_type AND sort=sort_;
IF data_ <> '' THEN
INSERT INTO usr_x_data (content,person_id,type,sort)VALUES(data_,employee,data_type,sort_);
END IF;
ret_val=true;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.update_usr_data(employee integer, data_ text, data_type integer, sort_ integer) OWNER TO postgres;
--
-- Name: update_usr_data_json(text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.update_usr_data_json(elementos text, type_ integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
BEGIN
jsElementos=elementos::jsonb;
--IF elementos IS NOT NULL AND elementos <> '' THEN
DELETE FROM usr_x_data WHERE usr_x_data.person_id=person_id AND usr_x_data.type=type_;
--END IF;
WITH rows AS (
INSERT INTO usr_x_data (id,person_id,type,content,sort)
SELECT (
CASE
WHEN id IS NULL OR id = 0 THEN
nextval('usr_x_data_id_seq'::regclass)::integer
ELSE
id
END
)
,person_id,type,content,sort
FROM jsonb_populate_recordset (NULL::usr_x_data, jsElementos)
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION public.update_usr_data_json(elementos text, type_ integer) OWNER TO postgres;
--
-- Name: username(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.username(id1 integer) RETURNS text
LANGUAGE plpgsql
AS $_$
DECLARE
relac text;
BEGIN
select username from public.usr_x_sys into relac where id=$1;
RETURN relac::text;
END;
$_$;
ALTER FUNCTION public.username(id1 integer) OWNER TO postgres;
--
-- Name: initial_quantity(integer, numeric, numeric); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.initial_quantity(item_id_ integer, quantity_ numeric, price_ numeric) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
total_ numeric;
BEGIN
IF price_ IS NOT NULL AND price_ > 0 THEN
total_= price_*quantity_;
END IF;
DELETE FROM stock.transactions WHERE item_id=item_id_;
SELECT stock.insert_transaction(
'2022-01-01',1,item_id_,quantity_,total_,1,'Inventario inicial',null
) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.initial_quantity(item_id_ integer, quantity_ numeric, price_ numeric) OWNER TO postgres;
--
-- Name: insert_transaction(date, integer, integer, numeric, numeric, integer, text, integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.insert_transaction(date_ date, heading_id_ integer, item_id_ integer, quantity_ numeric, total_ numeric, debit_ integer, description_ text, source_id_ integer) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
BEGIN
IF item_id_ IS NOT NULL AND item_id_ > 0 AND quantity_ IS NOT NULL AND quantity_ > 0 THEN
WITH rows AS (
INSERT INTO stock.transactions (date,heading_id,item_id,quantity,total,debit,description,source_id)
VALUES
(date_,heading_id_,item_id_,quantity_,total_,debit_,description_,source_id_)
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.insert_transaction(date_ date, heading_id_ integer, item_id_ integer, quantity_ numeric, total_ numeric, debit_ integer, description_ text, source_id_ integer) OWNER TO postgres;
--
-- Name: insert_update_delete_articles(); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.insert_update_delete_articles() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
entero integer=0;
n integer=0;
temp integer=0;--default restricted
BEGIN
IF TG_OP = 'INSERT' THEN
--esto es para coservar el default 0 en restricted
IF NEW.restricted IS NOT NULL THEN
temp=NEW.restricted;
END IF;
SELECT COUNT(id) INTO n FROM stock.titles WHERE barcode=NEW.barcode;
IF n::BOOLEAN THEN
SELECT id INTO entero FROM stock.titles WHERE barcode=NEW.barcode;
UPDATE stock.titles SET up=1, name=NEW.name, description=NEW.description, make=NEW.make, prescriptions=NEW.prescriptions, lab_id=NEW.lab_id, barcode=NEW.barcode, location=NEW.location, restricted=NEW.restricted, price=NEW.price, discount=NEW.discount
WHERE id=entero;
ELSE
SELECT nextval('stock.titles_id_seq'::regclass)::integer INTO entero;
INSERT INTO stock.titles
(id,name,description,make,prescriptions,barcode,lab_id,location,restricted,price,discount)
VALUES
(entero,NEW.name,NEW.description,NEW.make,NEW.prescriptions,NEW.barcode,NEW.lab_id,NEW.location,temp,NEW.price, NEW.discount);
END IF;
IF NEW.presentations IS NOT NULL THEN
SELECT stock.update_presentations(entero,NEW.presentations) INTO temp;
END IF;
INSERT INTO stock.items
(id,title_id,expiration_date,lot)
VALUES
(NEW.item_id,entero,last_day(NEW.expiration_date),NEW.lot);
ELSIF TG_OP = 'UPDATE' THEN
UPDATE stock.titles SET name=NEW.name, description=NEW.description, make=NEW.make, prescriptions=NEW.prescriptions, lab_id=NEW.lab_id, barcode=NEW.barcode, location=NEW.location, restricted=NEW.restricted, price=NEW.price, discount=NEW.discount
WHERE id=OLD.title_id;
IF NEW.presentations IS NOT NULL AND OLD.title_id IS NOT NULL THEN
SELECT stock.update_presentations(OLD.title_id,NEW.presentations) INTO temp;
END IF;
UPDATE stock.items SET id=NEW.item_id,expiration_date=last_day(NEW.expiration_date), lot=NEW.lot WHERE id=OLD.item_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE stock.items SET up = 0
WHERE OLD.item_id = stock.items.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;
ALTER FUNCTION stock.insert_update_delete_articles() OWNER TO postgres;
--
-- Name: item_mean_cost(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.item_mean_cost(item_id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val numeric;
numerator numeric=0;
denominator numeric=0;
BEGIN
IF item_id_ IS NOT NULL AND item_id_ > 0 THEN
SELECT stock.item_money_balance(item_id_) INTO numerator;
SELECT stock.item_quantity_balance(item_id_) INTO denominator;
IF denominator > 0 THEN
ret_val=numerator/denominator;
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.item_mean_cost(item_id_ integer) OWNER TO postgres;
--
-- Name: item_money_balance(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.item_money_balance(item_id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
debits numeric=0;
credits numeric=0;
BEGIN
IF item_id_ IS NOT NULL AND item_id_ > 0 THEN
SELECT sum(COALESCE(total,0)) INTO debits FROM stock.transactions_view WHERE item_id=item_id_ AND debit::boolean;
SELECT sum(COALESCE(total,0)) INTO credits FROM stock.transactions_view WHERE item_id=item_id_ AND NOT debit::boolean;
IF debits IS NULL THEN
debits=0;
END IF;
IF credits IS NULL THEN
credits=0;
END IF;
ret_val=debits-credits;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.item_money_balance(item_id_ integer) OWNER TO postgres;
--
-- Name: item_quantity_balance(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.item_quantity_balance(item_id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
debits numeric=0;
credits numeric=0;
BEGIN
IF item_id_ IS NOT NULL AND item_id_ > 0 THEN
SELECT sum(COALESCE(quantity,0)) INTO debits FROM stock.transactions_view WHERE item_id=item_id_ AND debit::boolean;
SELECT sum(COALESCE(quantity,0)) INTO credits FROM stock.transactions_view WHERE item_id=item_id_ AND NOT debit::boolean;
IF debits IS NULL THEN
debits=0;
END IF;
IF credits IS NULL THEN
credits=0;
END IF;
ret_val=debits-credits;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.item_quantity_balance(item_id_ integer) OWNER TO postgres;
--
-- Name: items(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.items(title integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT json_agg(json_build_object(
'item_id', i.id,
'expiration_date', i.expiration_date,
'lot', i.lot,
'quantity', stock.item_quantity_balance(i.id),
'cost', stock.item_money_balance(i.id)
))::TEXT
INTO ret_val
FROM stock.items_view i WHERE title_id=title;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.items(title integer) OWNER TO postgres;
--
-- Name: presentations(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.presentations(title integer) RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text='';
BEGIN
SELECT json_agg(json_build_object(
'presentation_name', t.presentation_name,
'presentation_factor', t.presentation_factor
))::TEXT
INTO ret_val
FROM stock.presentation_title_link t WHERE title_id=title;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.presentations(title integer) OWNER TO postgres;
--
-- Name: title_mean_cost(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.title_mean_cost(id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val numeric;
numerator numeric=0;
denominator numeric=0;
BEGIN
IF id_ IS NOT NULL AND id_ > 0 THEN
SELECT stock.title_money_balance(id_) INTO numerator;
SELECT stock.title_quantity_balance(id_) INTO denominator;
IF denominator > 0 THEN
ret_val=numerator/denominator;
END IF;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.title_mean_cost(id_ integer) OWNER TO postgres;
--
-- Name: title_money_balance(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.title_money_balance(id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
debits numeric=0;
credits numeric=0;
BEGIN
IF id_ IS NOT NULL AND id_ > 0 THEN
SELECT sum(COALESCE(total,0)) INTO debits FROM stock.transactions_view WHERE item_id IN (SELECT id FROM stock.items WHERE title_id =id_) AND debit::boolean;
SELECT sum(COALESCE(total,0)) INTO credits FROM stock.transactions_view WHERE item_id IN (SELECT id FROM stock.items WHERE title_id =id_) AND NOT debit::boolean;
IF debits IS NULL THEN
debits=0;
END IF;
IF credits IS NULL THEN
credits=0;
END IF;
ret_val=debits-credits;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.title_money_balance(id_ integer) OWNER TO postgres;
--
-- Name: title_quantity_balance(integer); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.title_quantity_balance(id_ integer) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
ret_val text=0;
debits numeric=0;
credits numeric=0;
BEGIN
IF id_ IS NOT NULL AND id_ > 0 THEN
SELECT sum(COALESCE(quantity,0)) INTO debits FROM stock.transactions_view WHERE item_id IN (SELECT id FROM stock.items WHERE title_id =id_) AND debit::boolean;
SELECT sum(COALESCE(quantity,0)) INTO credits FROM stock.transactions_view WHERE item_id IN (SELECT id FROM stock.items WHERE title_id =id_) AND NOT debit::boolean;
IF debits IS NULL THEN
debits=0;
END IF;
IF credits IS NULL THEN
credits=0;
END IF;
ret_val=debits-credits;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.title_quantity_balance(id_ integer) OWNER TO postgres;
--
-- Name: unsubscribe_item(integer, numeric); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.unsubscribe_item(id_ integer, quantity_ numeric) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer=0;
total_ numeric=0;
BEGIN
SELECT stock.item_mean_cost(id_) INTO total_;
total_=quantity_*total_;
SELECT stock.insert_transaction(
'2022-01-01',1,id_,quantity_,total_,0,'Inventario inicial',null
) INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.unsubscribe_item(id_ integer, quantity_ numeric) OWNER TO postgres;
--
-- Name: unsubscribe_title(text, numeric); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.unsubscribe_title(barcode_ text, quantity_ numeric) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val integer=0;
n integer=0;
total_ numeric=0;
BEGIN
SELECT count(item_id) INTO n FROM stock.articles WHERE barcode=barcode_;
IF n::boolean THEN
SELECT item_id INTO n FROM stock.articles WHERE barcode=barcode_;
SELECT stock.item_mean_cost(n) INTO total_;
total_=quantity_*total_;
SELECT stock.insert_transaction(
'2022-01-01',1,n,quantity_,total_,0,'Inventario inicial',null
) INTO ret_val;
END IF;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.unsubscribe_title(barcode_ text, quantity_ numeric) OWNER TO postgres;
--
-- Name: update_presentations(integer, text); Type: FUNCTION; Schema: stock; Owner: postgres
--
CREATE FUNCTION stock.update_presentations(title integer, elementos text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
ret_val INTEGER=0;
jsElementos jsonb;
i jsonb;
j jsonb;
arreglo text[];
cadena text;
BEGIN
jsElementos=elementos::jsonb;
cadena='{"title_id":'||title::text||'}';
FOR i IN SELECT * FROM jsonb_array_elements(jsElementos)
LOOP
SELECT cadena::jsonb || i INTO j;
--IF i->>presentation_factor <> '' THEN
arreglo=array_append(arreglo, j::text);
--END IF;
END LOOP;
cadena='['||array_to_string(arreglo,',')||']';
jsElementos=cadena::jsonb;
DELETE FROM stock.presentation_title_link WHERE title_id=title;
WITH rows AS (
INSERT INTO stock.presentation_title_link (title_id, presentation_name, presentation_factor)
SELECT title_id, presentation_name, presentation_factor
FROM jsonb_populate_recordset (NULL::stock.presentation_title_link, jsElementos)
WHERE presentation_name IS NOT NULL AND presentation_name::text <> ''
RETURNING 1
)
SELECT count(*) FROM rows INTO ret_val;
RETURN ret_val;
END;
$$;
ALTER FUNCTION stock.update_presentations(title integer, elementos text) OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: account_types; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.account_types (
id integer NOT NULL,
code text,
description text,
level integer
);
ALTER TABLE account.account_types OWNER TO postgres;
--
-- Name: TABLE account_types; Type: COMMENT; Schema: account; Owner: postgres
--
COMMENT ON TABLE account.account_types IS 'Para indicar los tipos de cuentas: 1: activo, 2: pasivo';
--
-- Name: COLUMN account_types.level; Type: COMMENT; Schema: account; Owner: postgres
--
COMMENT ON COLUMN account.account_types.level IS '1: más alto nivel
2: Subnivel';
--
-- Name: account_types_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.account_types_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.account_types_id_seq OWNER TO postgres;
--
-- Name: account_types_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.account_types_id_seq OWNED BY account.account_types.id;
--
-- Name: accounts; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.accounts (
id integer NOT NULL,
code text NOT NULL,
balance_code text,
name text,
description text,
type integer
);
ALTER TABLE account.accounts OWNER TO postgres;
--
-- Name: accounts_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.accounts_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.accounts_id_seq OWNER TO postgres;
--
-- Name: accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.accounts_id_seq OWNED BY account.accounts.id;
--
-- Name: cost_center_status; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.cost_center_status (
id integer NOT NULL,
name text
);
ALTER TABLE account.cost_center_status OWNER TO postgres;
--
-- Name: cost_center_status_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.cost_center_status_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.cost_center_status_id_seq OWNER TO postgres;
--
-- Name: cost_center_status_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.cost_center_status_id_seq OWNED BY account.cost_center_status.id;
--
-- Name: cost_centers; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.cost_centers (
id integer NOT NULL,
code text,
name text,
description text,
starting_date date DEFAULT (now())::date,
ending_date date,
period_id integer,
status integer DEFAULT 1 NOT NULL
);
ALTER TABLE account.cost_centers OWNER TO postgres;
--
-- Name: cost_centers_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.cost_centers_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.cost_centers_id_seq OWNER TO postgres;
--
-- Name: cost_centers_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.cost_centers_id_seq OWNED BY account.cost_centers.id;
--
-- Name: detail_sub_accounts; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.detail_sub_accounts (
id integer NOT NULL,
sub_account_id integer NOT NULL,
code text,
name text,
user_id integer
);
ALTER TABLE account.detail_sub_accounts OWNER TO postgres;
--
-- Name: detail_sub_accounts_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.detail_sub_accounts_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.detail_sub_accounts_id_seq OWNER TO postgres;
--
-- Name: detail_sub_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.detail_sub_accounts_id_seq OWNED BY account.detail_sub_accounts.id;
--
-- Name: entries; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.entries (
id integer NOT NULL,
heading_id integer NOT NULL,
correlative integer NOT NULL,
sub_account_id integer NOT NULL,
amount numeric NOT NULL,
isdebit boolean NOT NULL,
cost_center_id integer DEFAULT 1 NOT NULL,
detail_sub_account_id integer
);
ALTER TABLE account.entries OWNER TO postgres;
--
-- Name: entries_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.entries_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.entries_id_seq OWNER TO postgres;
--
-- Name: entries_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.entries_id_seq OWNED BY account.entries.id;
--
-- Name: headings; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.headings (
id integer NOT NULL,
number text DEFAULT account.next_heading(true) NOT NULL,
date date DEFAULT ('NOW'::text)::date,
summary text,
period_id integer,
status integer DEFAULT 1 NOT NULL,
recording_date_time timestamp without time zone DEFAULT (now())::timestamp without time zone
);
ALTER TABLE account.headings OWNER TO postgres;
--
-- Name: headings_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.headings_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.headings_id_seq OWNER TO postgres;
--
-- Name: headings_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.headings_id_seq OWNED BY account.headings.id;
--
-- Name: sub_accounts; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.sub_accounts (
id integer NOT NULL,
account_id integer NOT NULL,
code text,
name text,
description text
);
ALTER TABLE account.sub_accounts OWNER TO postgres;
--
-- Name: journal; Type: VIEW; Schema: account; Owner: postgres
--
CREATE VIEW account.journal AS
SELECT h.id AS heading_id,
h.number,
h.date,
h.summary,
e.id AS entry_id,
e.correlative,
e.sub_account_id,
sa.name AS sub_account_name,
e.amount,
e.isdebit,
e.cost_center_id,
cc.name AS cost_center_name,
e.detail_sub_account_id,
dsa.name AS detail_sub_account_name
FROM ((((account.entries e
LEFT JOIN account.headings h ON ((h.id = e.heading_id)))
LEFT JOIN account.sub_accounts sa ON ((sa.id = e.sub_account_id)))
LEFT JOIN account.cost_centers cc ON ((cc.id = e.cost_center_id)))
LEFT JOIN account.detail_sub_accounts dsa ON ((dsa.id = e.detail_sub_account_id)))
ORDER BY h.number, e.correlative;
ALTER TABLE account.journal OWNER TO postgres;
--
-- Name: journal_entries; Type: VIEW; Schema: account; Owner: postgres
--
CREATE VIEW account.journal_entries AS
SELECT h.id,
h.number,
h.date,
h.summary,
h.period_id,
account.entries(h.id) AS entries
FROM account.headings h
WHERE (h.status)::boolean;
ALTER TABLE account.journal_entries OWNER TO postgres;
--
-- Name: periods; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.periods (
id integer NOT NULL,
code text DEFAULT 1,
status integer,
starting_date date NOT NULL,
ending_date date NOT NULL
);
ALTER TABLE account.periods OWNER TO postgres;
--
-- Name: COLUMN periods.status; Type: COMMENT; Schema: account; Owner: postgres
--
COMMENT ON COLUMN account.periods.status IS '1: Abierto
2: Cerrado
3: Ejercicio actual';
--
-- Name: periods_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.periods_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.periods_id_seq OWNER TO postgres;
--
-- Name: periods_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.periods_id_seq OWNED BY account.periods.id;
--
-- Name: receivable_payable_transactions_from_invoices; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.receivable_payable_transactions_from_invoices (
id integer NOT NULL,
date date,
entry timestamp without time zone,
heading_id integer,
receivable integer,
user_id integer,
description text,
subaccount_id integer,
entry_correlative integer,
total numeric,
source_id integer
);
ALTER TABLE account.receivable_payable_transactions_from_invoices OWNER TO postgres;
--
-- Name: receivable_payable_transactions_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.receivable_payable_transactions_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.receivable_payable_transactions_id_seq OWNER TO postgres;
--
-- Name: receivable_payable_transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.receivable_payable_transactions_id_seq OWNED BY account.receivable_payable_transactions_from_invoices.id;
--
-- Name: sequences; Type: TABLE; Schema: account; Owner: postgres
--
CREATE TABLE account.sequences (
id integer NOT NULL,
name text,
value integer
);
ALTER TABLE account.sequences OWNER TO postgres;
--
-- Name: TABLE sequences; Type: COMMENT; Schema: account; Owner: postgres
--
COMMENT ON TABLE account.sequences IS 'value debe contener el último valor emitido';
--
-- Name: sequences_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.sequences_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.sequences_id_seq OWNER TO postgres;
--
-- Name: sequences_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.sequences_id_seq OWNED BY account.sequences.id;
--
-- Name: sub_accounts_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--
CREATE SEQUENCE account.sub_accounts_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE account.sub_accounts_id_seq OWNER TO postgres;
--
-- Name: sub_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--
ALTER SEQUENCE account.sub_accounts_id_seq OWNED BY account.sub_accounts.id;
--
-- Name: attendance_id_seq; Type: SEQUENCE; Schema: att; Owner: postgres
--
CREATE SEQUENCE att.attendance_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE att.attendance_id_seq OWNER TO postgres;
--
-- Name: attendance; Type: TABLE; Schema: att; Owner: postgres
--
CREATE TABLE att.attendance (
id integer DEFAULT nextval('att.attendance_id_seq'::regclass) NOT NULL,
user_id integer NOT NULL,
date_time timestamp without time zone DEFAULT now() NOT NULL,
last_modified timestamp without time zone DEFAULT now() NOT NULL,
enter integer,
status integer DEFAULT 1,
rate numeric
);
ALTER TABLE att.attendance OWNER TO postgres;
--
-- Name: attendance_report; Type: VIEW; Schema: att; Owner: postgres
--
CREATE VIEW att.attendance_report AS
SELECT a.id,
a.user_id,
a.status,
public.full_name(a.user_id) AS fullname,
a.date_time AS enter,
att.date_time_(att.exit(a.id)) AS exit,
(att.date_time_(att.exit(a.id)) - a.date_time) AS intv,
a.rate,
( SELECT round(((date_part('epoch'::text, (att.date_time_(att.exit(a.id)) - a.date_time)) / (3600)::double precision))::numeric, 1) AS int4) AS time_int
FROM att.attendance a
WHERE (a.enter = 1);
ALTER TABLE att.attendance_report OWNER TO postgres;
--
-- Name: attendance_report_active; Type: VIEW; Schema: att; Owner: postgres
--
CREATE VIEW att.attendance_report_active AS
SELECT a.id,
a.user_id,
a.status,
a.fullname,
a.enter,
a.exit,
a.intv,
a.rate,
a.time_int
FROM att.attendance_report a
WHERE (a.status)::boolean;
ALTER TABLE att.attendance_report_active OWNER TO postgres;
--
-- Name: attendance_statuses_id_seq; Type: SEQUENCE; Schema: att; Owner: postgres
--
CREATE SEQUENCE att.attendance_statuses_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE att.attendance_statuses_id_seq OWNER TO postgres;
--
-- Name: attendance_statuses; Type: TABLE; Schema: att; Owner: postgres
--
CREATE TABLE att.attendance_statuses (
id integer DEFAULT nextval('att.attendance_statuses_id_seq'::regclass) NOT NULL,
description text
);
ALTER TABLE att.attendance_statuses OWNER TO postgres;
--
-- Name: attendance_view; Type: VIEW; Schema: att; Owner: postgres
--
CREATE VIEW att.attendance_view AS
SELECT a.id,
a.user_id,
public.full_name(a.user_id) AS fullname,
a.date_time,
( SELECT to_char(((a.date_time)::date)::timestamp with time zone, 'mm/dd/yyyy'::text) AS to_char) AS date,
( SELECT to_char(a.date_time, 'HH24:MI'::text) AS to_char) AS "time",
a.enter
FROM att.attendance a
WHERE (a.status > 0);
ALTER TABLE att.attendance_view OWNER TO postgres;
--
-- Name: detail; Type: TABLE; Schema: invoice; Owner: postgres
--
CREATE TABLE invoice.detail (
id integer NOT NULL,
header_id integer NOT NULL,
correlative integer NOT NULL,
item_id integer,
concept text,
unit_price numeric,
quantity numeric,
amount double precision NOT NULL,
exent boolean DEFAULT false,
discount numeric
);
ALTER TABLE invoice.detail OWNER TO postgres;
--
-- Name: detail_id_seq; Type: SEQUENCE; Schema: invoice; Owner: postgres
--
CREATE SEQUENCE invoice.detail_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE invoice.detail_id_seq OWNER TO postgres;
--
-- Name: detail_id_seq; Type: SEQUENCE OWNED BY; Schema: invoice; Owner: postgres
--
ALTER SEQUENCE invoice.detail_id_seq OWNED BY invoice.detail.id;
--
-- Name: headers; Type: TABLE; Schema: invoice; Owner: postgres
--
CREATE TABLE invoice.headers (
id integer NOT NULL,
type integer NOT NULL,
status integer DEFAULT 1,
active boolean DEFAULT true,
date date DEFAULT ('now'::text)::date,
number text DEFAULT 'S/N'::text,
heading_id integer,
user_id integer,
user_name text,
dui text,
nit text,
municipio text,
departamento text,
giro text,
registro text,
direccion text,
subtotal numeric,
iva numeric,
ivaret numeric,
tax_1 numeric,
tax_2 numeric,
exents numeric,
taxed numeric,
total numeric,
discount numeric,
total_text text,
owner_id integer
);
ALTER TABLE invoice.headers OWNER TO postgres;
--
-- Name: COLUMN headers.type; Type: COMMENT; Schema: invoice; Owner: postgres
--
COMMENT ON COLUMN invoice.headers.type IS '
1: Compras CCF: Comprobante de Crédito Fiscal
2: Compras CF: Consumidor Final
3: Compras Factura de sujeto excento
4: Compras FC: Factura Comercial
5: Ventas CCF: Comprobante de Crédito Fiscal
6: Ventas CF: Consumidor Final
7: Ventas XP: Comprobante de Exportación
8: Ventas FC: Factura Comercial
';
--
-- Name: COLUMN headers.status; Type: COMMENT; Schema: invoice; Owner: postgres
--
COMMENT ON COLUMN invoice.headers.status IS '
0: Cerrada
1: Activa
';
--
-- Name: headers_id_seq; Type: SEQUENCE; Schema: invoice; Owner: postgres
--
CREATE SEQUENCE invoice.headers_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE invoice.headers_id_seq OWNER TO postgres;
--
-- Name: headers_id_seq; Type: SEQUENCE OWNED BY; Schema: invoice; Owner: postgres
--
ALTER SEQUENCE invoice.headers_id_seq OWNED BY invoice.headers.id;
--
-- Name: invoices; Type: VIEW; Schema: invoice; Owner: postgres
--
CREATE VIEW invoice.invoices AS
SELECT h.id,
h.id AS invoice_id,
h.type,
h.status,
h.active,
h.date,
h.number,
h.heading_id,
h.user_id,
h.user_name,
h.dui,
h.nit,
h.municipio,
h.departamento,
h.giro,
h.registro,
h.direccion,
h.subtotal,
h.iva,
h.ivaret,
h.tax_1,
h.tax_2,
h.exents,
h.taxed,
h.total,
h.discount,
h.total_text,
h.owner_id,
invoice.detail(h.id) AS detail
FROM invoice.headers h
WHERE (h.status)::boolean;
ALTER TABLE invoice.invoices OWNER TO postgres;
--
-- Name: types; Type: TABLE; Schema: invoice; Owner: postgres
--
CREATE TABLE invoice.types (
id integer NOT NULL,
name text NOT NULL,
code text,
short_code text
);
ALTER TABLE invoice.types OWNER TO postgres;
--
-- Name: TABLE types; Type: COMMENT; Schema: invoice; Owner: postgres
--
COMMENT ON TABLE invoice.types IS '1: Compras CCF: Comprobante de Crédito Fiscal
2: Compras CF: Consumidor Final
3: Compras Factura de sujeto excento
4: Compras FC: Factura Comercial
5: Ventas CCF: Comprobante de Crédito Fiscal
6: Ventas CF: Consumidor Final
7: Ventas XP: Comprobante de Exportación
8: Ventas FC: Factura Comercial
9: Ventas TK: Ticket';
--
-- Name: invoices_view; Type: VIEW; Schema: invoice; Owner: postgres
--
CREATE VIEW invoice.invoices_view AS
SELECT h.id,
h.id AS invoice_id,
h.type,
t.code AS type_name,
h.status,
h.active,
h.date,
h.number,
h.heading_id,
h.user_id,
h.user_name,
h.dui,
h.nit,
h.municipio,
h.departamento,
h.giro,
h.registro,
h.direccion,
h.subtotal,
h.iva,
h.ivaret,
h.tax_1,
h.tax_2,
h.exents,
h.taxed,
h.total,
h.discount,
h.total_text,
h.owner_id,
invoice.detail(h.id) AS detail,
invoice.detail_summ(h.id) AS detail_summ
FROM (invoice.headers h
LEFT JOIN invoice.types t ON ((t.id = h.type)))
WHERE (h.status)::boolean;
ALTER TABLE invoice.invoices_view OWNER TO postgres;
--
-- Name: types_id_seq; Type: SEQUENCE; Schema: invoice; Owner: postgres
--
CREATE SEQUENCE invoice.types_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE invoice.types_id_seq OWNER TO postgres;
--
-- Name: types_id_seq; Type: SEQUENCE OWNED BY; Schema: invoice; Owner: postgres
--
ALTER SEQUENCE invoice.types_id_seq OWNED BY invoice.types.id;
--
-- Name: ret_val; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.ret_val (
insert_heading integer
);
ALTER TABLE public.ret_val OWNER TO postgres;
--
-- Name: sequences; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.sequences (
id integer NOT NULL,
code text NOT NULL,
value text NOT NULL
);
ALTER TABLE public.sequences OWNER TO postgres;
--
-- Name: sequences_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.sequences_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.sequences_id_seq OWNER TO postgres;
--
-- Name: sequences_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.sequences_id_seq OWNED BY public.sequences.id;
--
-- Name: usr_persons; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_persons (
id integer NOT NULL,
up integer DEFAULT 1,
code text NOT NULL,
firstname text,
lastname text,
dui text,
nit text,
short_name text,
state text,
county text,
country text
);
ALTER TABLE public.usr_persons OWNER TO postgres;
--
-- Name: usr_x_business; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_x_business (
id integer NOT NULL,
up integer DEFAULT 1 NOT NULL,
type integer DEFAULT 1,
register_no text,
supplier boolean DEFAULT false,
customer boolean DEFAULT false
);
ALTER TABLE public.usr_x_business OWNER TO postgres;
--
-- Name: usr_business; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_business AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.short_name,
u.dui,
u.nit,
u.county,
u.state,
u.country,
x.type,
x.register_no,
x.supplier,
x.customer,
public.data_(u.id, 1) AS emails,
public.data_(u.id, 2) AS phones,
public.data_(u.id, 3) AS addresses
FROM (public.usr_persons u
LEFT JOIN public.usr_x_business x USING (id))
WHERE (x.up)::boolean;
ALTER TABLE public.usr_business OWNER TO postgres;
--
-- Name: usr_business_plus; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_business_plus AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.short_name,
public.full_name(u.firstname, u.lastname, u.short_name) AS full_name,
u.dui,
u.nit,
u.county,
u.state,
u.country,
u.type,
u.register_no,
u.supplier,
u.customer,
public.phone(u.id) AS phone,
public.email(u.id) AS email,
public.address(u.id) AS address
FROM public.usr_business u;
ALTER TABLE public.usr_business_plus OWNER TO postgres;
--
-- Name: usr_content_types; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_content_types (
id integer NOT NULL,
description text
);
ALTER TABLE public.usr_content_types OWNER TO postgres;
--
-- Name: usr_content_types_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_content_types_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_content_types_id_seq OWNER TO postgres;
--
-- Name: usr_content_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.usr_content_types_id_seq OWNED BY public.usr_content_types.id;
--
-- Name: usr_customers; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_customers AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.short_name,
u.dui,
u.nit,
u.county,
u.state,
u.country,
u.type,
u.register_no,
u.emails,
u.phones,
u.addresses
FROM public.usr_business u
WHERE u.customer;
ALTER TABLE public.usr_customers OWNER TO postgres;
--
-- Name: usr_x_employee; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_x_employee (
id integer NOT NULL,
up integer DEFAULT 1 NOT NULL,
salary numeric,
"position" text
);
ALTER TABLE public.usr_x_employee OWNER TO postgres;
--
-- Name: usr_employee; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_employee AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.dui,
u.nit,
x."position",
x.salary,
public.data_(u.id, 1) AS emails,
public.data_(u.id, 2) AS phones,
public.data_(u.id, 3) AS addresses
FROM (public.usr_persons u
LEFT JOIN public.usr_x_employee x USING (id))
WHERE (x.up)::boolean;
ALTER TABLE public.usr_employee OWNER TO postgres;
--
-- Name: usr_x_labs; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_x_labs (
id integer NOT NULL,
up integer DEFAULT 1 NOT NULL
);
ALTER TABLE public.usr_x_labs OWNER TO postgres;
--
-- Name: usr_labs; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_labs AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.short_name,
u.dui,
u.nit,
u.county,
u.state,
u.country,
u.register_no,
u.type
FROM (public.usr_business u
LEFT JOIN public.usr_x_labs x USING (id))
WHERE (x.up)::boolean;
ALTER TABLE public.usr_labs OWNER TO postgres;
--
-- Name: usr_persons_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_persons_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_persons_id_seq OWNER TO postgres;
--
-- Name: usr_persons_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.usr_persons_id_seq OWNED BY public.usr_persons.id;
--
-- Name: usr_supplier_types; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_supplier_types (
id integer NOT NULL,
description text
);
ALTER TABLE public.usr_supplier_types OWNER TO postgres;
--
-- Name: usr_supplier_types_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_supplier_types_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_supplier_types_id_seq OWNER TO postgres;
--
-- Name: usr_supplier_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.usr_supplier_types_id_seq OWNED BY public.usr_supplier_types.id;
--
-- Name: usr_suppliers; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_suppliers AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
u.short_name,
u.dui,
u.nit,
u.county,
u.state,
u.country,
u.type,
u.register_no,
u.emails,
u.phones,
u.addresses
FROM public.usr_business u
WHERE u.supplier;
ALTER TABLE public.usr_suppliers OWNER TO postgres;
--
-- Name: usr_x_sys; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_x_sys (
id integer NOT NULL,
up integer DEFAULT 1 NOT NULL,
username text NOT NULL,
password text,
role_id integer DEFAULT 3
);
ALTER TABLE public.usr_x_sys OWNER TO postgres;
--
-- Name: usr_sys; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.usr_sys AS
SELECT u.id,
u.code,
u.firstname,
u.lastname,
x.username,
x.password,
x.role_id
FROM (public.usr_persons u
LEFT JOIN public.usr_x_sys x USING (id))
WHERE (x.up)::boolean;
ALTER TABLE public.usr_sys OWNER TO postgres;
--
-- Name: usr_sys_roles_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_sys_roles_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_sys_roles_id_seq OWNER TO postgres;
--
-- Name: usr_sys_roles; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_sys_roles (
id integer DEFAULT nextval('public.usr_sys_roles_id_seq'::regclass) NOT NULL,
descripcion text
);
ALTER TABLE public.usr_sys_roles OWNER TO postgres;
--
-- Name: usr_x_data; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.usr_x_data (
id integer NOT NULL,
person_id integer NOT NULL,
type integer NOT NULL,
content text,
sort integer DEFAULT 1 NOT NULL
);
ALTER TABLE public.usr_x_data OWNER TO postgres;
--
-- Name: usr_x_data_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_x_data_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_x_data_id_seq OWNER TO postgres;
--
-- Name: usr_x_data_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.usr_x_data_id_seq OWNED BY public.usr_x_data.id;
--
-- Name: usr_x_labs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.usr_x_labs_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.usr_x_labs_id_seq OWNER TO postgres;
--
-- Name: usr_x_labs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.usr_x_labs_id_seq OWNED BY public.usr_x_labs.id;
--
-- Name: vare; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.vare (
initial_quantity integer
);
ALTER TABLE public.vare OWNER TO postgres;
--
-- Name: items; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.items (
id integer NOT NULL,
title_id integer,
expiration_date date,
lot text,
up integer DEFAULT 1,
supplier_id integer
);
ALTER TABLE stock.items OWNER TO postgres;
--
-- Name: titles; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.titles (
id integer NOT NULL,
name text,
description text,
make text,
up integer DEFAULT 1,
prescriptions text,
barcode text,
lab_id integer,
location text,
restricted integer DEFAULT 0,
price numeric,
discount numeric
);
ALTER TABLE stock.titles OWNER TO postgres;
--
-- Name: articles; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.articles AS
SELECT t.id AS title_id,
i.id AS item_id,
('T'::text || lpad((t.id)::text, 9, '0'::text)) AS title_code,
('I'::text || lpad((i.id)::text, 9, '0'::text)) AS item_code,
t.name,
t.description,
t.prescriptions,
t.make,
t.barcode,
t.location,
t.restricted,
t.lab_id,
t.price,
t.discount,
stock.presentations(t.id) AS presentations,
public.full_name(u.firstname, u.lastname, u.short_name) AS lab_name,
public.last_day(i.expiration_date) AS expiration_date,
i.lot
FROM ((stock.items i
LEFT JOIN stock.titles t ON ((t.id = i.title_id)))
LEFT JOIN public.usr_persons u ON ((u.id = t.lab_id)))
WHERE (i.up)::boolean;
ALTER TABLE stock.articles OWNER TO postgres;
--
-- Name: articles_view; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.articles_view AS
SELECT t.id AS title_id,
i.id AS item_id,
('T'::text || lpad((t.id)::text, 5, '0'::text)) AS title_code,
('I'::text || lpad((i.id)::text, 5, '0'::text)) AS item_code,
t.name,
t.description,
t.prescriptions,
t.make,
t.barcode,
t.location,
t.restricted,
t.lab_id,
t.price,
t.discount,
stock.presentations(t.id) AS presentations,
public.full_name(u.firstname, u.lastname, u.short_name) AS lab_name,
u.code AS lab_code,
public.last_day(i.expiration_date) AS expiration_date,
i.lot,
( SELECT stock.item_quantity_balance(i.id) AS quantity_balance) AS quantity_on_stock,
( SELECT stock.item_money_balance(i.id) AS money_balance) AS value,
( SELECT stock.item_mean_cost(i.id) AS mean_cost) AS mean_cost
FROM ((stock.items i
LEFT JOIN stock.titles t ON ((t.id = i.title_id)))
LEFT JOIN public.usr_persons u ON ((u.id = t.lab_id)))
WHERE (i.up)::boolean;
ALTER TABLE stock.articles_view OWNER TO postgres;
--
-- Name: presentation_title_link; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.presentation_title_link (
id integer NOT NULL,
title_id integer,
presentation_name text,
presentation_factor text
);
ALTER TABLE stock.presentation_title_link OWNER TO postgres;
--
-- Name: articles_view_extended; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.articles_view_extended AS
SELECT t.id AS title_id,
i.id AS item_id,
('T'::text || lpad((t.id)::text, 5, '0'::text)) AS title_code,
('I'::text || lpad((i.id)::text, 5, '0'::text)) AS item_code,
t.name,
t.description,
t.prescriptions,
t.make,
t.barcode,
t.location,
t.restricted,
t.lab_id,
t.price,
t.discount,
public.full_name(u.firstname, u.lastname, u.short_name) AS lab_name,
u.code AS lab_code,
i.expiration_date,
i.lot,
p.presentation_name,
( SELECT stock.item_quantity_balance(i.id) AS quantity_balance) AS quantity_on_stock,
( SELECT stock.item_money_balance(i.id) AS money_balance) AS value,
( SELECT stock.item_mean_cost(i.id) AS mean_cost) AS mean_cost
FROM (((stock.items i
LEFT JOIN stock.titles t ON ((t.id = i.title_id)))
LEFT JOIN public.usr_persons u ON ((u.id = t.lab_id)))
LEFT JOIN stock.presentation_title_link p ON ((p.title_id = t.id)))
WHERE (i.up)::boolean;
ALTER TABLE stock.articles_view_extended OWNER TO postgres;
--
-- Name: categories; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.categories (
id integer NOT NULL,
name text,
up integer
);
ALTER TABLE stock.categories OWNER TO postgres;
--
-- Name: categories_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.categories_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.categories_id_seq OWNER TO postgres;
--
-- Name: categories_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.categories_id_seq OWNED BY stock.categories.id;
--
-- Name: category_title_link; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.category_title_link (
id integer NOT NULL,
category_id integer,
title_id integer
);
ALTER TABLE stock.category_title_link OWNER TO postgres;
--
-- Name: category_title_link_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.category_title_link_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.category_title_link_id_seq OWNER TO postgres;
--
-- Name: category_title_link_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.category_title_link_id_seq OWNED BY stock.category_title_link.id;
--
-- Name: items_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.items_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.items_id_seq OWNER TO postgres;
--
-- Name: items_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.items_id_seq OWNED BY stock.items.id;
--
-- Name: items_view; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.items_view AS
SELECT i.id,
i.title_id,
i.expiration_date,
i.lot,
i.up,
i.supplier_id
FROM stock.items i
WHERE (i.up)::boolean;
ALTER TABLE stock.items_view OWNER TO postgres;
--
-- Name: presentation_title_link_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.presentation_title_link_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.presentation_title_link_id_seq OWNER TO postgres;
--
-- Name: presentation_title_link_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.presentation_title_link_id_seq OWNED BY stock.presentation_title_link.id;
--
-- Name: titles_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.titles_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.titles_id_seq OWNER TO postgres;
--
-- Name: titles_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.titles_id_seq OWNED BY stock.titles.id;
--
-- Name: titles_view; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.titles_view AS
SELECT t.id,
('T'::text || lpad((t.id)::text, 5, '0'::text)) AS title_code,
t.name,
t.description,
t.prescriptions,
t.make,
t.barcode,
t.location,
t.restricted,
t.lab_id,
t.price,
t.discount,
stock.presentations(t.id) AS presentations,
stock.items(t.id) AS items,
public.full_name(u.firstname, u.lastname, u.short_name) AS lab_name,
( SELECT stock.title_quantity_balance(t.id) AS quantity_balance) AS quantity_on_stock,
( SELECT stock.title_money_balance(t.id) AS money_balance) AS value,
( SELECT stock.title_mean_cost(t.id) AS mean_cost) AS mean_cost
FROM (stock.titles t
LEFT JOIN public.usr_persons u ON ((u.id = t.lab_id)))
WHERE (t.up)::boolean;
ALTER TABLE stock.titles_view OWNER TO postgres;
--
-- Name: transactions; Type: TABLE; Schema: stock; Owner: postgres
--
CREATE TABLE stock.transactions (
id integer NOT NULL,
date date DEFAULT (now())::date,
date_entry timestamp without time zone DEFAULT now(),
heading_id integer,
source_id integer,
item_id integer,
quantity numeric,
total numeric,
debit integer,
description text
);
ALTER TABLE stock.transactions OWNER TO postgres;
--
-- Name: transactions_id_seq; Type: SEQUENCE; Schema: stock; Owner: postgres
--
CREATE SEQUENCE stock.transactions_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE stock.transactions_id_seq OWNER TO postgres;
--
-- Name: transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: stock; Owner: postgres
--
ALTER SEQUENCE stock.transactions_id_seq OWNED BY stock.transactions.id;
--
-- Name: transactions_view; Type: VIEW; Schema: stock; Owner: postgres
--
CREATE VIEW stock.transactions_view AS
SELECT tr.date,
tr.heading_id,
tr.source_id,
tr.item_id,
tr.quantity,
tr.total,
tr.debit,
tr.description
FROM (stock.transactions tr
LEFT JOIN stock.items i ON ((i.id = tr.item_id)))
WHERE (i.up)::boolean;
ALTER TABLE stock.transactions_view OWNER TO postgres;
--
-- Name: account_types id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.account_types ALTER COLUMN id SET DEFAULT nextval('account.account_types_id_seq'::regclass);
--
-- Name: accounts id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.accounts ALTER COLUMN id SET DEFAULT nextval('account.accounts_id_seq'::regclass);
--
-- Name: cost_center_status id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_center_status ALTER COLUMN id SET DEFAULT nextval('account.cost_center_status_id_seq'::regclass);
--
-- Name: cost_centers id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_centers ALTER COLUMN id SET DEFAULT nextval('account.cost_centers_id_seq'::regclass);
--
-- Name: detail_sub_accounts id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.detail_sub_accounts ALTER COLUMN id SET DEFAULT nextval('account.detail_sub_accounts_id_seq'::regclass);
--
-- Name: entries id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries ALTER COLUMN id SET DEFAULT nextval('account.entries_id_seq'::regclass);
--
-- Name: headings id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.headings ALTER COLUMN id SET DEFAULT nextval('account.headings_id_seq'::regclass);
--
-- Name: periods id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.periods ALTER COLUMN id SET DEFAULT nextval('account.periods_id_seq'::regclass);
--
-- Name: receivable_payable_transactions_from_invoices id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.receivable_payable_transactions_from_invoices ALTER COLUMN id SET DEFAULT nextval('account.receivable_payable_transactions_id_seq'::regclass);
--
-- Name: sequences id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sequences ALTER COLUMN id SET DEFAULT nextval('account.sequences_id_seq'::regclass);
--
-- Name: sub_accounts id; Type: DEFAULT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sub_accounts ALTER COLUMN id SET DEFAULT nextval('account.sub_accounts_id_seq'::regclass);
--
-- Name: detail id; Type: DEFAULT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.detail ALTER COLUMN id SET DEFAULT nextval('invoice.detail_id_seq'::regclass);
--
-- Name: headers id; Type: DEFAULT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers ALTER COLUMN id SET DEFAULT nextval('invoice.headers_id_seq'::regclass);
--
-- Name: types id; Type: DEFAULT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.types ALTER COLUMN id SET DEFAULT nextval('invoice.types_id_seq'::regclass);
--
-- Name: sequences id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.sequences ALTER COLUMN id SET DEFAULT nextval('public.sequences_id_seq'::regclass);
--
-- Name: usr_content_types id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_content_types ALTER COLUMN id SET DEFAULT nextval('public.usr_content_types_id_seq'::regclass);
--
-- Name: usr_persons id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_persons ALTER COLUMN id SET DEFAULT nextval('public.usr_persons_id_seq'::regclass);
--
-- Name: usr_supplier_types id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_supplier_types ALTER COLUMN id SET DEFAULT nextval('public.usr_supplier_types_id_seq'::regclass);
--
-- Name: usr_x_data id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_data ALTER COLUMN id SET DEFAULT nextval('public.usr_x_data_id_seq'::regclass);
--
-- Name: usr_x_labs id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_labs ALTER COLUMN id SET DEFAULT nextval('public.usr_x_labs_id_seq'::regclass);
--
-- Name: categories id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.categories ALTER COLUMN id SET DEFAULT nextval('stock.categories_id_seq'::regclass);
--
-- Name: category_title_link id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.category_title_link ALTER COLUMN id SET DEFAULT nextval('stock.category_title_link_id_seq'::regclass);
--
-- Name: items id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.items ALTER COLUMN id SET DEFAULT nextval('stock.items_id_seq'::regclass);
--
-- Name: presentation_title_link id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.presentation_title_link ALTER COLUMN id SET DEFAULT nextval('stock.presentation_title_link_id_seq'::regclass);
--
-- Name: titles id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.titles ALTER COLUMN id SET DEFAULT nextval('stock.titles_id_seq'::regclass);
--
-- Name: transactions id; Type: DEFAULT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.transactions ALTER COLUMN id SET DEFAULT nextval('stock.transactions_id_seq'::regclass);
--
-- Data for Name: account_types; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.account_types (id, code, description, level) FROM stdin;
1 1 ACTIVO 1
2 11 Activo corriente 2
3 12 Activo no corriente 2
4 2 PASIVO 1
5 21 Pasivo corriente 2
6 22 Pasivo no corriente 2
7 3 PATRIMONIO 1
8 31 Capital contable 2
9 4 CUENTAS DE RESULTADO DEUDOR 1
10 42 Gastos 2
11 41 Costos 2
12 5 CUENTAS DE RESULTADO ACREEDOR 1
13 51 Ingresos 2
14 6 CUENTA LIQUIDADORA 1
15 52 INGRESOS NO OPERACIONALES 2
16 61 CUENTA LIQUIDADORA DE RESULTADOS 2
\.
--
-- Data for Name: accounts; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.accounts (id, code, balance_code, name, description, type) FROM stdin;
1 1101 11 EFECTIVO EN CAJA \r \N 2
2 1102 11 EFECTIVO EN BANCOS \r \N 2
3 1103 11 CUENTAS Y DOCUMENTOS A COBRAR \r \N 2
4 1104 11 CRÉDITO FISCAL \r \N 2
5 1105 11 PRÉSTAMOS A PARTES RELACIONADAS \r \N 2
6 1106 11 CUENTA POR COBRAR ACCIONISTAS\r \N 2
7 1107 11 INVENTARIOS\r \N 2
8 1108 11 INVERSIONES TEMPORALES\r \N 2
9 1109 11 ANTICIPOS A CORTO PLAZO\r \N 2
10 1110 11 DEPÓSITOS EN GARANTÍA\r \N 2
11 1111 11 OTROS ACTIVOS\r \N 2
12 1201 12 PROPIEDAD PLANTA Y EQUIPO\r \N 3
13 1202 12 BIENES EN ARRENDAMIENTO FINANCIERO\r \N 3
14 1203 12 REVALUACIÓN DE PROPIEDAD, PLANTA Y EQUIPO\r \N 3
15 1204 12 INVERSIONES PERMANENTES\r \N 3
16 1205 12 CUENTAS Y DOCUMENTOS POR COBRAR A LARGO PLAZO\r \N 3
17 1206 12 INTANGIBLES\r \N 3
18 1207 12 ACTIVO POR IMPUESTO SOBRE LA RENTA DIFERIDO\r \N 3
19 1208 12 DEPÓSITOS EN GARANTÍA-LP\r \N 3
20 1209 12 PRÉSTAMOS A PARTES RELACIONADAS LP \r \N 3
21 2101 21 PRÉSTAMOS A CORTO PLAZO\r \N 5
22 2102 21 PORCIÓN DE PRÉSTAMO A LARGO PLAZO\r \N 5
23 2103 21 CUENTAS POR PAGAR\r \N 5
24 2104 21 OBLIGACIONES POR ARRENDAMIENTO FINANCIERO\r \N 5
25 2105 21 BENEFICIOS EMPLEADOS POR PAGAR\r \N 5
26 2106 21 RETENCIONES\r \N 5
27 2107 21 PROVISIONES DE IMPUESTOS\r \N 5
28 2108 21 DÉBITO FISCAL\r \N 5
29 2109 21 COBROS ANTICIPADOS\r \N 5
30 2110 21 DIVIDENDOS POR PAGAR\r \N 5
31 2111 21 PRESTAMOS DE PARTES RELACIONADAS\r \N 5
32 2112 21 PRÉSTAMO DE ACCIONISTAS\r \N 5
33 2201 22 PRÉSTAMOS A LARGO PLAZO\r \N 6
34 2202 22 OBLIGACIONES POR ARRENDAMIENTO FINANCIERO LP\r \N 6
35 2203 22 PRÉSTAMOS DE PARTES RELACIONADAS A LARGO PLAZO\r \N 6
36 2204 22 PROVISIÓN PARA OBLIGACIONES LABORALES\r \N 6
37 2205 22 PASIVO POR IMPUESTO SOBRE LA RENTA DIFERIDO\r \N 6
38 3101 31 CAPITAL SOCIAL\r \N 8
39 3102 31 GANANCIAS ACUMULADAS\r \N 8
40 3103 31 PÉRDIDAS ACUMULADAS\r \N 8
41 3104 31 RESERVA LEGAL\r \N 8
42 3105 31 SUPERAVIT POR REVALUACIONES\r \N 8
43 4101 41 COSTOS DE VENTAS Y SERVICIOS\r \N 11
44 4102 41 COSTO DE PRODUCCIÓN\r \N 11
45 4201 42 GASTOS DE ADMINISTRACIÓN\r \N 10
46 4202 42 GASTOS DE VENTA\r \N 10
47 4203 42 GASTOS NO DEDUCIBLES\r \N 10
48 4204 42 GASTOS FINANCIEROS\r \N 10
49 4205 42 GASTOS NO OPERACIONALES\r \N 10
50 4206 42 GASTOS DE IMPUESTO SOBRE LA RENTA\r \N 10
51 5101 51 VENTA DE BIENES\r \N 13
52 5102 51 VENTA DE SERVICIOS\r \N 13
53 5201 52 PRODUCTOS FINANCIEROS\r \N 15
54 5202 52 GANANCIA EN VENTA DE ACTIVOS\r \N 15
55 6101 61 PÉRDIDAS Y GANANCIA\r \N 16
\.
--
-- Data for Name: cost_center_status; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.cost_center_status (id, name) FROM stdin;
0 Cerrado
1 Activo
\.
--
-- Data for Name: cost_centers; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.cost_centers (id, code, name, description, starting_date, ending_date, period_id, status) FROM stdin;
1 1 Centro de costos general \N 2022-01-01 \N 1 1
2 2 Yelp \N 2022-01-01 \N 1 1
\.
--
-- Data for Name: detail_sub_accounts; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.detail_sub_accounts (id, sub_account_id, code, name, user_id) FROM stdin;
1 223 1 Unnamed 1
\.
--
-- Data for Name: entries; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.entries (id, heading_id, correlative, sub_account_id, amount, isdebit, cost_center_id, detail_sub_account_id) FROM stdin;
1 1 1 223 0 t 1 \N
121 75 1 1 180.00 t 2 2
122 75 2 219 180.00 f 2 2
\.
--
-- Data for Name: headings; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.headings (id, number, date, summary, period_id, status, recording_date_time) FROM stdin;
75 00000041 2022-08-01 Revenue for services, inv. # VCF000046 2 1 2022-08-01 11:59:40.511897
1 00000001 2022-01-01 Apertura del ejercicio 1 1 2021-06-08 17:04:56.835841
\.
--
-- Data for Name: periods; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.periods (id, code, status, starting_date, ending_date) FROM stdin;
1 2021 0 2021-01-01 2021-12-31
2 2022 1 2022-01-01 2022-12-31
\.
--
-- Data for Name: receivable_payable_transactions_from_invoices; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.receivable_payable_transactions_from_invoices (id, date, entry, heading_id, receivable, user_id, description, subaccount_id, entry_correlative, total, source_id) FROM stdin;
\.
--
-- Data for Name: sequences; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.sequences (id, name, value) FROM stdin;
1 PARTIDAS 41
\.
--
-- Data for Name: sub_accounts; Type: TABLE DATA; Schema: account; Owner: postgres
--
COPY account.sub_accounts (id, account_id, code, name, description) FROM stdin;
3 1 110103 Efectivo por depositar \r \N
4 1 110104 Fondos para cambio\r \N
5 2 110201 Cuentas corrientes \r \N
6 2 110202 Cuentas de ahorro \r \N
7 2 110203 Depósitos a plazo\r \N
8 2 110204 Equivalentes al efectivo \r \N
10 3 110302 Documentos por cobrar \r \N
11 3 110303 Anticipos empleados \r \N
12 3 110304 Préstamos a empleados \r \N
13 3 110305 Deudores diversos \r \N
14 3 110306 Intereses por recibir \r \N
15 3 110307 Arrendamientos\r \N
16 3 110308 Tarjetas de créditos \r \N
17 4 110401 Por compras locales \r \N
18 4 110402 Por importación \r \N
19 5 110501 Entidades subsidiarias \r \N
20 5 110502 Entidades asociadas \r \N
21 5 110503 Directores y ejecutivos\r \N
22 7 110701 Materia Prima\r \N
23 7 110702 Producto en Proceso\r \N
24 7 110703 Producto terminado\r \N
25 7 110704 Accesorios\r \N
26 7 110705 Estimación para obsolescencia de inventario o de lento movimiento\r \N
27 8 110801 Acciones\r \N
28 8 110802 Bonos\r \N
29 9 110901 Seguros\r \N
30 9 110902 Alquileres\r \N
31 9 110903 Pago a cuenta de Impuesto Sobre la Renta\r \N
32 9 110904 Retenciones ISR\r \N
33 9 110905 Retenciones IVA\r \N
34 9 110906 Crédito años anteriores de ISR\r \N
35 9 110907 Proveedores\r \N
36 9 110908 Anticipo a cuenta IVA (Tarjeta de Crédito)\r \N
37 10 111001 Distribuidoras de energía\r \N
38 10 111002 Bancos y financieras\r \N
39 10 111003 Depósito por arrendamientos\r \N
40 12 120101 Terrenos\r \N
41 12 120102 Edificaciones\r \N
42 12 120103 Mobiliario y equipo de oficina\r \N
43 12 120104 Maquinaria y equipo\r \N
44 12 120105 Vehículos\r \N
45 12 120106 Herramientas\r \N
46 13 120201 Vehículos\r \N
47 13 120202 Maquinaria y Equipo\r \N
48 13 120203 Mobiliario y Equipo\r \N
49 14 120301 Terrenos\r \N
50 14 120302 Edificaciones\r \N
51 14 120303 Maquinaria y Equipo\r \N
52 15 120401 Inversiones en subsidiarias\r \N
53 15 120402 Inversiones en asociadas\r \N
54 15 120403 Inversiones en negocios conjuntos\r \N
55 16 120501 Hipotecarios\r \N
56 16 120502 Prendarios\r \N
57 16 120503 Personales\r \N
58 16 120504 Arrendamientos\r \N
59 17 120601 Patentes\r \N
60 17 120602 Marcas\r \N
61 17 120603 Derecho de llave\r \N
62 17 120604 Licencias y concesiones\r \N
63 18 120701 Crédito de ISR de años anteriores\r \N
64 20 120901 Entidades subsidiarias\r \N
65 20 120902 Entidades asociadas\r \N
66 20 120903 Directores y ejecutivos\r \N
67 21 210101 Préstamos prendarios\r \N
68 21 210102 Préstamos personales\r \N
69 21 210103 Préstamos hipotecarios\r \N
70 21 210104 Sobre giros bancarios\r \N
71 21 210105 Cartas de Crédito\r \N
72 22 210201 Préstamos prendarios\r \N
73 22 210202 Préstamos hipotecarios\r \N
74 22 210203 Préstamos personales\r \N
75 23 210301 Proveedores locales\r \N
76 23 210302 Proveedores extranjeros\r \N
77 23 210303 Acreedores diversos\r \N
78 23 210304 Honorarios por pagar\r \N
79 23 210305 Intereses por pagar\r \N
80 24 210401 Mobiliario y Equipo\r \N
81 25 210501 Sueldos por pagar\r \N
82 25 210502 COMISIONES\r \N
83 25 210503 Bonificaciones\r \N
84 25 210504 Vacaciones\r \N
85 25 210505 Aguinaldos\r \N
86 25 210506 Indemnización\r \N
87 25 210507 Aporte patronal ISSS (Salud)\r \N
88 25 210508 Aporte patronal (Pensiones)\r \N
89 25 210509 Aporte patronal AFP\r \N
90 25 210510 aporte patronal IPSFA\r \N
91 25 210511 INSAFORP\r \N
92 26 210601 ISSS (Salud)\r \N
93 26 210602 ISSS (Pensiones)\r \N
94 26 210603 Impuesto Sobre la Renta\r \N
95 26 210604 IVA\r \N
96 26 210605 Instituciones financieras\r \N
97 26 210606 Vialidad\r \N
98 26 210607 AFP\r \N
99 26 210608 IPSFA\r \N
100 27 210701 Impuesto Sobre la Renta corriente\r \N
101 27 210702 Pago a cuenta ISR\r \N
102 27 210703 Impuestos municipales\r \N
103 28 210801 Por ventas de bienes y servicios\r \N
104 29 210901 Anticipos de clientes\r \N
105 31 211101 Entidades subsidiarias\r \N
106 31 211102 Entidades asociadas\r \N
107 31 211103 Direcciones y ejecutivos\r \N
108 33 220101 Préstamos prendarios\r \N
109 33 220102 Préstamos hipotecarios\r \N
110 33 220103 Préstamos personales\r \N
111 34 220201 Mobiliario y Equipo\r \N
112 35 220301 Entidades subsidiarias\r \N
113 35 220302 Entidades asociadas\r \N
114 35 220303 Directores y ejecutivos\r \N
115 36 220401 Indemnización\r \N
116 37 220501 Impuesto diferido\r \N
117 38 310101 Capital mínimo\r \N
118 38 310102 Capital variable\r \N
119 39 310201 Ganancia del ejercicio\r \N
120 39 310202 Ganancia de ejercicios anteriores\r \N
121 39 310203 Ganancias decretadas\r \N
2 1 110102 Petty cash \N
9 3 110301 Clients \N
122 40 310301 Pérdidas del ejercicio\r \N
123 40 310302 Pérdidas de ejercicios anteriores\r \N
124 43 410101 Costo de Venta de Bienes\r \N
125 43 410102 Costos de Servicios\r \N
126 44 410201 Mano de Obra\r \N
127 44 410202 Materia Prima\r \N
128 44 410203 Gastos de Fabricación\r \N
129 45 420101 Sueldos ordinarios\r \N
130 45 420102 Horas extras\r \N
131 45 420103 Vacaciones\r \N
132 45 420104 Aguinaldos\r \N
133 45 420105 Indemnizaciones\r \N
134 45 420106 Bonificaciones\r \N
135 45 420107 Cuota patronal I.S.S.S.\r \N
136 45 420108 Cuota patronal A.F.P.\r \N
137 45 420109 Cuota patronal IPSFA\r \N
138 45 420110 INSAFORP\r \N
139 45 420111 Comida a empleados\r \N
140 45 420112 Seguro de Vida y Gastos Médicos\r \N
141 45 420113 Impuestos fiscales\r \N
142 45 420114 Impuestos municipales\r \N
143 45 420115 Mantenimiento de edificaciones\r \N
144 45 420116 Mantenimiento de equipo\r \N
145 45 420117 Mantenimiento de instalaciones\r \N
146 45 420118 Mantenimiento de vehículos\r \N
147 45 420119 Mantenimiento de inmuebles arrendados\r \N
148 45 420120 Depreciaciones\r \N
149 45 420121 Publicaciones\r \N
150 45 420122 Suscripciones\r \N
151 45 420123 Comunicaciones\r \N
152 45 420124 Papelería y útiles\r \N
153 45 420125 Útiles de aseo y limpieza\r \N
154 45 420126 Capacitación del Personal\r \N
155 45 420127 Seguros\r \N
156 45 420128 Amortizaciones\r \N
157 45 420129 Patentes\r \N
158 45 420130 Atenciones a empleados\r \N
159 45 420131 Donaciones\r \N
160 45 420132 Alquileres\r \N
161 45 420133 Honorarios profesionales\r \N
162 45 420134 Seguridad y Vigilancia\r \N
163 45 420135 Energía eléctrica\r \N
164 45 420136 Agua potable\r \N
165 45 420137 Combustible y lubricantes\r \N
166 45 420138 Viáticos\r \N
167 45 420139 Dietas\r \N
168 46 420201 Sueldos y Salarios\r \N
169 46 420202 Horas extra\r \N
170 46 420203 Comisiones\r \N
171 46 420204 Vacaciones\r \N
172 46 420205 Aguinaldos\r \N
173 46 420206 Indemnizaciones\r \N
174 46 420207 Bonificaciones\r \N
175 46 420208 Cuota patronal I.S.S.S.\r \N
176 46 420209 Cuota patronal A.F.P.\r \N
177 46 420210 Cuota patronal IPSFA\r \N
178 46 420211 INSAFORP\r \N
179 46 420212 Comida a empleados\r \N
180 46 420213 Seguro de Vida y Gastos Médicos\r \N
181 46 420214 Impuestos fiscales\r \N
182 46 420215 Impuestos municipales\r \N
183 46 420216 Mantenimiento de edificaciones\r \N
184 46 420217 Mantenimiento de equipo\r \N
185 46 420218 Mantenimiento de instalaciones\r \N
186 46 420219 Mantenimiento de vehículos\r \N
187 46 420220 Mantenimiento de inmuebles arrendados\r \N
188 46 420221 Depreciaciones\r \N
189 46 420222 Publicaciones\r \N
190 46 420223 Suscripciones\r \N
191 46 420224 Comunicaciones\r \N
192 46 420225 Papelería y útiles\r \N
193 46 420226 Útiles de aseo y limpieza\r \N
194 46 420227 Capacitación del Personal\r \N
195 46 420228 Seguros\r \N
196 46 420229 Amortizaciones\r \N
197 46 420230 Patentes\r \N
198 46 420231 Atenciones a empleados\r \N
199 46 420232 Donaciones\r \N
200 46 420233 Alquileres\r \N
201 46 420234 Honorarios profesionales\r \N
202 46 420235 Seguridad\r \N
203 46 420236 Energía eléctrica\r \N
204 46 420237 Agua potable\r \N
205 46 420238 Combustible y lubricantes\r \N
206 46 420239 Viáticos\r \N
207 46 420240 Publicidad\r \N
208 47 420301 Impuestos extemporáneos\r \N
209 47 420302 Multas\r \N
210 47 420303 Intereses\r \N
211 47 420304 Gastos no deducibles\r \N
212 48 420401 Intereses\r \N
213 48 420402 Comisiones bancarias\r \N
214 48 420403 Diferencial cambiario\r \N
215 49 420501 Pérdida en venta de activos\r \N
216 50 420601 Impuesto sobre la Renta corriente\r \N
217 50 420602 Impuesto sobre la Renta diferido\r \N
218 51 510101 Venta de bienes\r \N
220 52 510202 Arrendamientos\r \N
221 53 520101 Intereses\r \N
222 53 520102 Diferencial cambiario\r \N
223 7 110706 Inventario para venta \N
1 1 110101 Cash \N
219 52 510201 Revenue \N
\.
--
-- Data for Name: attendance; Type: TABLE DATA; Schema: att; Owner: postgres
--
COPY att.attendance (id, user_id, date_time, last_modified, enter, status, rate) FROM stdin;
1 1 2022-08-19 15:00:16.513667 2022-08-19 15:00:16.513667 1 1 1680
2 1 2022-08-19 15:06:35.392841 2022-08-19 15:06:35.392841 0 1 1680
3 1 2022-08-19 15:44:47.056216 2022-08-19 15:44:47.056216 1 1 1680
4 1 2022-08-19 15:44:59.352706 2022-08-19 15:44:59.352706 0 1 1680
\.
--
-- Data for Name: attendance_statuses; Type: TABLE DATA; Schema: att; Owner: postgres
--
COPY att.attendance_statuses (id, description) FROM stdin;
1 Active
2 Paid
3 Deleted
\.
--
-- Data for Name: detail; Type: TABLE DATA; Schema: invoice; Owner: postgres
--
COPY invoice.detail (id, header_id, correlative, item_id, concept, unit_price, quantity, amount, exent, discount) FROM stdin;
621 213 1 \N Deep cleaning Danielle by Clara 180.00 1.00 180 f 0.00
626 217 1 298 BETOBLOCK D solución oral 120 mL, frasco 120 mL, MEDIPRODUCTOS 11.99 1.00 10.79 f 10.00
627 218 1 298 BETOBLOCK D solución oral 120 mL, frasco 120 mL, MEDIPRODUCTOS 11.99 1.00 10.79 f 10.00
629 220 1 297 BETOBLOCK 0.5 mg, caja 30 tabletas, MEDIPRODUCTOS 10.52 1.00 9.47 f 10.00
\.
--
-- Data for Name: headers; Type: TABLE DATA; Schema: invoice; Owner: postgres
--
COPY invoice.headers (id, type, status, active, date, number, heading_id, user_id, user_name, dui, nit, municipio, departamento, giro, registro, direccion, subtotal, iva, ivaret, tax_1, tax_2, exents, taxed, total, discount, total_text, owner_id) FROM stdin;
213 6 1 t 2022-08-01 VCF000046 75 1 Manuel Mauricio Grande Pineda 01435575-0 0813-020766-001-9 San Salvador San Salvador Col. Lomas d eVersalles 2, calle 1, pasaje 2, casa #2. San Salvador/San Salvador 180.00 20.71 \N \N \N 0 159.29 180.00 0.00 1
217 5 1 t 2022-08-05 VCCF000008 \N 1 Manuel Mauricio Grande Pineda 01435575-0 0813-020766-001-9 San Salvador San Salvador Col. Lomas d eVersalles 2, calle 1, pasaje 2, casa #2. San Salvador/San Salvador 11.99 1.24 \N \N \N 0 9.55 10.79 1.20 1
218 5 1 t 2022-08-05 VCCF000009 \N 1 Manuel Mauricio Grande Pineda 01435575-0 0813-020766-001-9 San Salvador San Salvador Col. Lomas d eVersalles 2, calle 1, pasaje 2, casa #2. San Salvador/San Salvador 11.99 1.24 \N \N \N 0 9.55 10.79 1.20 1
220 1 1 t 2022-08-05 \N \N 1 Manuel Mauricio Grande Pineda 01435575-0 0813-020766-001-9 San Salvador San Salvador Col. Lomas d eVersalles 2, calle 1, pasaje 2, casa #2. San Salvador/San Salvador 11.75 1.23 \N \N \N 0 9.47 10.70 1.05 1
\.
--
-- Data for Name: types; Type: TABLE DATA; Schema: invoice; Owner: postgres
--
COPY invoice.types (id, name, code, short_code) FROM stdin;
1 Comprobante de Crédito Fiscal de compras Compras CCF CCCF
3 Factura de sujeto excento de compras Compras FX CFX
4 Factura Comercial de compras Compras FC CFC
6 Consumidor Final de ventas Ventas CF VCF
2 Consumidor Final de compras Compras CF CCF
5 Comprobante de Crédito Fiscal de ventas Ventas CCF VCCF
7 Comprobante de Exportación de ventas Ventas XP VXP
8 Factura Comercial de ventas Ventas FC VFC
9 Ticket de ventas Ticket VT
\.
--
-- Data for Name: ret_val; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.ret_val (insert_heading) FROM stdin;
500
\.
--
-- Data for Name: sequences; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.sequences (id, code, value) FROM stdin;
1 VT 71
4 VCCF 9
3 VCF 50
2 VFC 1
5 VXP 0
\.
--
-- Data for Name: usr_content_types; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_content_types (id, description) FROM stdin;
1 email
2 telefono
3 dirección
\.
--
-- Data for Name: usr_persons; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_persons (id, up, code, firstname, lastname, dui, nit, short_name, state, county, country) FROM stdin;
3 1 sysadmin System Administrator \N \N \N \N \N \N
2 1 mjgrande Mauricio José Grande Cóbar \N \N \N \N \N \N
8 1 arlette Arlette Torres \N \N Farmacia La Samaritana La Paz San Luis Talpa El Salvador
12 1 LANCASCO Laboratorios Químicos Farmacéuticos Lancasco - Salvador \N \N \N \N \N \N \N
11 1 GAMMA Laboratorios Gamma \N \N \N \N \N \N \N
10 1 GENERICX Laboratorios Generix \N \N \N \N \N \N \N
13 1 FARDEL Laboratorios Fardel S.A. de C.V. \N \N \N \N \N \N \N
15 1 SUIZOS Laboratoios Suizos \N \N \N \N \N \N \N
17 1 LIMONT Laboratory Limont S.A. de C.V. \N \N \N \N \N \N \N
40 1 ee eee \N \N \N \N \N \N \N
41 1 nuev nuevo \N \N \N \N \N \N \N
42 1 nuevo labo NUEV0 \N \N \N \N \N \N \N
43 1 labo3 LABO 3 \N \N \N \N \N \N \N
44 1 MEDIPRODUCTOS Laoratorios MEDIPRODUCTOS \N \N \N \N \N \N \N
45 1 MK Laboratorios MK \N \N \N \N \N \N \N
46 1 TERAMED Laboratorios TERAMED \N \N \N \N \N \N \N
14 1 ROWA Farmacials Limited \N \N \N \N \N \N \N
18 1 CEFA Corporacion Cefa S.A. de C.V. \N \N \N \N \N \N \N
16 1 RIALSA RIALSA \N \N \N \N \N \N \N
9 1 generico Usuario General \N \N \N \N \N \N
48 1 geo Geofredo Grande \N \N \N \N \N \N
49 1 pio AMIGO NOSE \N \N \N \N \N \N
50 1 TIO EL TIO MAYRA \N \N \N \N \N \N
1 1 mgrande Manuel Mauricio Grande Pineda 01435575-0 0813-020766-001-9 \N San Salvador San Salvador El Salvador
\.
--
-- Data for Name: usr_supplier_types; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_supplier_types (id, description) FROM stdin;
1 Persona jurídica
2 Persona natural
3 Extranjero
\.
--
-- Data for Name: usr_sys_roles; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_sys_roles (id, descripcion) FROM stdin;
1 Down
2 Patron
3 Manager
4 Care giver
\.
--
-- Data for Name: usr_x_business; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_x_business (id, up, type, register_no, supplier, customer) FROM stdin;
12 1 \N \N f f
11 1 \N \N f f
10 1 \N \N f f
13 1 \N \N f f
15 1 \N \N f f
17 1 \N \N f f
40 1 \N \N f f
41 1 \N \N f f
42 1 \N \N f f
43 1 \N \N f f
44 1 \N \N f f
45 1 \N \N f f
46 1 \N \N f f
14 1 \N \N f f
18 1 \N \N f f
16 1 \N \N f f
9 1 2 \N f t
1 1 \N \N t t
48 1 \N \N \N t
49 1 \N \N t \N
50 1 \N \N \N t
\.
--
-- Data for Name: usr_x_data; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_x_data (id, person_id, type, content, sort) FROM stdin;
153 1 1 maugrande@gmail.com 1
154 1 1 mgrande@uca.edu.sv 2
151 1 2 7054 7026 1
152 1 2 2210 6600 2
155 1 3 Col. Lomas d eVersalles 2, calle 1, pasaje 2, casa #2. San Salvador/San Salvador 1
\.
--
-- Data for Name: usr_x_employee; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_x_employee (id, up, salary, "position") FROM stdin;
2 1 \N \N
1 1 1680 Asesor de sistema
\.
--
-- Data for Name: usr_x_labs; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_x_labs (id, up) FROM stdin;
10 1
11 1
12 1
14 1
15 1
16 1
17 1
18 1
13 1
40 0
41 0
42 0
43 0
44 1
45 1
46 1
\.
--
-- Data for Name: usr_x_sys; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.usr_x_sys (id, up, username, password, role_id) FROM stdin;
2 1 mjgrande zaibatsu 2
3 1 sysadmin sysadmin 2
8 1 arlette samaritana 2
1 1 mgrande Colateral13 2
\.
--
-- Data for Name: vare; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.vare (initial_quantity) FROM stdin;
0
\.
--
-- Data for Name: categories; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.categories (id, name, up) FROM stdin;
\.
--
-- Data for Name: category_title_link; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.category_title_link (id, category_id, title_id) FROM stdin;
\.
--
-- Data for Name: items; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.items (id, title_id, expiration_date, lot, up, supplier_id) FROM stdin;
298 338 2023-06-30 031 1 \N
303 343 2022-02-28 010 1 \N
309 348 2022-04-30 04 1 \N
308 347 2023-08-31 120 1 \N
307 346 2024-09-30 041 1 \N
302 342 2024-08-31 051 1 \N
306 345 2023-07-31 070 1 \N
304 343 2022-11-30 040 1 \N
313 351 2025-02-28 011 1 \N
314 351 2024-11-30 060 1 \N
315 352 2023-01-31 071 1 \N
316 353 2023-04-30 251 1 \N
317 354 2025-10-31 241 1 \N
320 356 2024-05-31 091 1 \N
296 336 2023-11-30 130 1 \N
312 350 2024-01-31 141 1 \N
297 337 2025-03-31 061 1 \N
354 376 2023-10-31 2010010 1 \N
355 377 2024-01-31 2101005 1 \N
356 378 2022-09-30 0K2694 1 \N
357 379 2023-07-31 2107005 1 \N
358 380 2023-04-30 1D1549 1 \N
359 381 2023-02-28 K3063G 1 \N
360 382 2023-04-30 1D1569B 1 \N
361 383 2022-11-30 0R3932E 1 \N
362 384 2022-04-30 0D1452B 1 \N
363 385 2022-02-28 0A0535C 1 \N
364 386 2022-02-28 0B0612C 1 \N
365 387 2023-04-30 0D1531C 1 \N
366 388 2022-09-30 0M3311E 1 \N
367 389 2023-07-31 0J2440B 1 \N
368 390 2023-03-31 0C0985C 1 \N
369 391 2024-01-31 2101013 1 \N
370 392 2024-03-31 1C1302E 1 \N
371 393 2023-08-31 0R3931C 1 \N
372 394 2022-10-31 1910014 1 \N
373 395 2022-05-31 2005009 1 \N
374 396 2022-07-31 SVF6450 1 \N
377 399 2025-02-28 102043 1 \N
380 401 2023-01-31 2101015 1 \N
381 402 2024-11-30 2011013 1 \N
382 403 2024-09-30 210943 1 \N
383 404 2022-04-30 1904037 1 \N
385 406 2024-02-29 2002027 1 \N
386 407 2025-03-31 2103008 1 \N
387 408 2024-02-29 2102024 1 \N
388 409 2024-01-31 2001008 1 \N
389 409 2025-05-31 2105034 1 \N
390 410 2024-06-30 2106036 1 \N
391 411 2023-08-31 1908027 1 \N
392 412 2024-04-30 2104032 1 \N
393 413 2025-02-28 2102036 1 \N
394 414 2024-06-30 2106037 1 \N
395 415 2022-05-31 1905054 1 \N
402 422 2025-07-31 2107013 1 \N
406 425 2024-03-31 2003030 1 \N
407 425 2024-04-30 2004021 1 \N
408 426 2024-10-31 2010041 1 \N
409 427 2024-01-31 2101014 1 \N
410 427 2024-09-30 2109045 1 \N
411 428 2023-01-31 2001018 1 \N
412 429 2024-01-31 2101018 1 \N
413 429 2024-01-31 2101019 1 \N
414 430 2022-10-31 1910009 1 \N
415 431 2022-10-31 1910009 1 \N
421 435 2024-07-31 SVF6353 1 \N
422 436 2022-11-30 SVF6861 1 \N
423 437 2024-04-30 SVF6663 1 \N
425 439 2023-03-31 SVF6956 1 \N
426 440 2024-02-29 SVF6460 1 \N
427 441 2024-05-31 SVF5909 1 \N
428 442 2022-03-31 SVF6089 1 \N
432 445 2023-06-30 SVF5457 1 \N
433 446 2024-09-30 SVF6482 1 \N
434 447 2023-11-30 SVF6672 1 \N
435 448 2023-02-28 SVF6175 1 \N
436 448 2024-07-31 SVF6766 1 \N
437 449 2023-02-28 SVF5566 1 \N
438 449 2023-02-28 SVF6171 1 \N
439 450 2024-05-31 svf6107 1 \N
443 452 2024-04-30 SVF6428 1 \N
375 397 2024-06-30 2106025 1 \N
376 398 2024-03-31 2103026 1 \N
514 519 2024-08-31 2108011 1 \N
440 451 2024-09-30 SVF7383 1 \N
441 451 2024-09-30 SVF7385 1 \N
318 355 2026-06-30 121 1 \N
319 355 2026-08-31 161 1 \N
378 400 2024-08-31 2108015 1 \N
451 459 2024-09-30 041 1 \N
379 400 2023-09-30 2009027 1 \N
450 458 2023-09-30 161 1 \N
310 349 2023-08-31 221 1 \N
311 349 2023-06-30 171 1 \N
420 434 2407-01-31 2107029 1 \N
403 423 2025-07-31 107003 1 \N
384 405 2024-05-31 2105045 1 \N
404 424 2024-05-31 2105011 1 \N
515 521 2024-07-31 2107023 1 \N
444 453 2023-08-31 SVF5833 1 \N
516 523 2023-06-30 SVF6301 1 \N
400 420 2025-07-31 2107044 1 \N
424 438 2023-02-28 SVF6203 1 \N
430 443 2024-09-30 SVF7018 1 \N
429 443 2024-05-31 SVF6970 1 \N
398 418 2023-10-31 2010025 1 \N
399 419 2023-03-31 2103012 1 \N
442 419 2024-05-31 SVF6432 1 \N
431 444 2024-10-31 SVF7261 1 \N
401 421 2024-02-29 2102040 1 \N
305 344 2024-08-31 031 1 \N
\.
--
-- Data for Name: presentation_title_link; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.presentation_title_link (id, title_id, presentation_name, presentation_factor) FROM stdin;
282 376 Frasco 240 mL 1
283 377 Frasco 240 mL 1
284 378 Caja 15 sobres 1
285 379 Caja 30 tablta 1
286 380 Caja de 30 tabletas 1
287 381 Caja de 30 tabletas 1
288 382 Caja 30 tabletas 1
289 383 Caja 14 cápsulas 1
290 384 Caja 28 tabletas 1
291 385 Caja 14 tabletas 1
292 386 Caja 20 tabletas 1
293 387 Caja 20 tabletas 1
294 388 Caja 14 tableta 1
295 389 Caja 10 cápsulas 1
296 390 Caja 10 tabletas 1
297 391 Caja 10 tabletas 1
224 337 caja 30 tabletas 1
225 338 frasco 120 mL 1
298 392 Caja 30 tabletas 1
299 393 Caja 6 tabletas 1
300 394 Frasco 10 mL 1
301 395 Caja de 7 Ampolas bebibles 1
302 396 Frasco 100 mL 1
305 399 Tubo 50 g 1
232 348 Frasco 120 mL 1
237 347 Caja 50 grageas 1
308 401 Frasco 120 mL 1
239 346 Frasco 120 mL 1
240 342 Caja 20 tabletas 1
241 345 Caja 5 tabletas 1
242 343 Caja 20 tabletas 1
309 402 Frasco 120 mL 1
244 351 Caja de 20 tabletas 1
310 403 Caja 50 tabletas 1
246 352 Caja de 10 viales 1
247 353 Caja de 15 sobres 1
248 354 Frasco 120 mL 1
250 356 Caja 8 cápsulas 1
251 336 ampolla 1 mL 1
252 350 Caja de 10 óvulos 1
311 404 Caja 30 tabletas 1
313 406 Caja 50 tabletas 1
314 407 Blister 10 tabletas 1
315 407 Caja 60 tabletas 6
316 408 Caja 20 tabletas 1
318 409 Frasco 125 mL 1
319 410 Caja 30 tabletas 1
320 411 TABLETA 1
321 411 BLISTER 10 TABLETAS 10
322 412 BLISTER 4 TABLETAS 1
323 413 CAJA POR 30 TABLETAS 1
324 414 CAJA POR 30 TABLETAS 1
325 415 CAJA POR 30 TABLETAS 1
338 422 CAJA POR 10 TABLETAS 1
343 425 Frasco 30 mL 1
344 426 CAJA X 4 TABLETAS 1
346 427 FRASCO 10 ML 1
347 428 TABLETA 1
351 429 BLISTER X 10 TABLETAS 1
352 429 CAJA X 100 TABLETAS 10
353 430 CAJA X 10 TABLETAS 1
354 431 TABLETA 1
348 428 Blister x 10 tabletas 10
355 431 Bliste x 10 tabletas 10
374 435 Tabletas 1
375 436 VIAL 1
376 437 CAJA POR 60 tabletas 1
378 439 CAJA POR 60tabletas 1
379 440 Caja POR 30 tabletas 1
380 441 CAJA POR 30tabletas 1
381 442 Caja POR 20 tabletas 1
568 400 Frasco 120 mL 1
385 445 TUBO 15 G 1
386 446 FRASCO PARA 60 ML 1
387 447 Tabletas 1
389 448 CÁPSULAS 1
391 449 CÁPSULAS 1
392 450 Cápsulas 1
396 452 Tabletas 1
556 397 Frasco 30 mL 1
557 398 Frasco 60 mL 1
558 519 CAJA POR 2 tabletas 1
566 451 Cápsulas 1
569 458 Caja X 10 VIALES BEBIBLES 1
571 349 Caja de 10 viales 1
574 434 Caja X 30 tabletas 1
575 423 TUBO DE 5G 1
576 405 Caja 30 tabletas 1
421 355 Frasco 120 mL 1
577 424 CAJA POR 1 TABLETA 1
578 521 Caja X 1 tabletas 1
426 459 FRASCO 120 ML 1
579 453 Cápsulas 1
581 523 Caja POR 10 tabletas 1
582 420 CAJA POR 30 TABLETA 1
590 438 Caja POR 60 tabletas 1
629 443 FRASCO POR 60 ml 1
630 418 BLISTER DE 10 UNIDADES 1
631 418 CAJA POR 50 TABLETAS 5
641 419 Tableta 1
642 444 FRASCO GOTERO 60 ML 1
643 421 BLISTER POR 10 TABLETAS 1
644 421 CAJA POR 30 TABLETAS 3
645 344 Frasco 100 mL 1
\.
--
-- Data for Name: titles; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.titles (id, name, description, make, up, prescriptions, barcode, lab_id, location, restricted, price, discount) FROM stdin;
338 BETOBLOCK D solución oral 120 mL \N \N 1 \N 7401092207019 44 A1 0 11.99 10
348 FOLISPLEN 120 mL \N \N 1 \N 7401092203714 44 A1 0 10 10
388 Etoricoxib 90 mg \N \N 1 \N 7702057062514 45 A2 0 21.28 10
389 Celecoxib 200 mg \N \N 1 \N 7702057205942 45 A2 0 9.05 10
347 FOLISPLEN grageas \N \N 1 \N 7401092203813 44 \N 0 12.5 10
390 Ciproibrato 100 mg \N \N 1 \N 7702057060619 45 A2 0 6.40 10
346 MEDITROPRIN suspensión 120mL \N \N 1 \N 7401092203912 44 \N 0 6.25 10
342 BACTERIOTAL tabletas 400 mg \N \N 1 \N 7401092204810 44 A1 0 11.58 10
345 MEDIMACROL 500mg \N \N 1 \N 7401092202915 44 A1 0 21.18 10
343 ACTIVIREX tabletas 400mg \N \N 1 \N 7401092209310 44 A1 0 18.32 10
351 PAINFLEX tabletas 220mg \N \N 1 \N 7401092211610 44 A1 0 7.01 10
352 viteron FORTE vial 5g/15mL \N \N 1 \N 7401092212518 44 A1 0 25.60 25.8
353 Dolo Atromed granulado \N \N 1 \N 7401092208115 44 A1 0 29.57 10
354 TYLOFENB Elixir \N \N 1 \N 7401092201710 44 A1 0 3.8 10
356 DEAREXIN cápsulas \N \N 1 \N 7401092208610 44 A1 0 3.96 10
336 BETOBLOCK solución inyectable 4 mg/mL \N \N 1 \N 7401092208917 44 A1 0 7.24 10
350 Medizol ovulos vaginales \N \N 1 \N 7401092203318 44 A1 0 10.40 10
391 Dexketoprofeno 25 mg \N \N 1 \N 7702057063085 45 A2 0 12.64 10
392 Paroxetina 20 mg \N \N 1 \N 7702057711603 45 A2 0 32.1 10
393 Nitazoxanida 500 mg \N \N 1 \N 7702057065478 45 A2 0 9.71 10
394 Quinfamida Suspensión \N \N 1 \N 7702057544607 45 A2 0 11.36 10
395 Arginina Solución oral \N \N 1 \N 7702057066000 45 A2 0 17.87 10
396 Ambroxol Jarabe \N \N 1 \N 7410002802183 45 A2 0 4.75 10
415 NOR- VASTINA 20 MG TABLETAS \N \N 1 \N 7410002605944 46 A3 0 21.07 10
399 Nor-Metro Gel 50 g \N \N 1 \N 7410002600574 46 A3 0 16.59 10
337 BETOBLOCK 0.5 mg \N \N 1 \N 7401092212013 44 A1 0 10.52 10
376 Gingi-Dent Oral Colutorio \N \N 1 \N 7702057066581 45 \N 0 9.86 10
377 Corsy-Dent Enjuague \N \N 1 \N 7702057756604 45 A2 0 8.31 10
378 Glucosamina polvo para solución oral \N \N 1 \N 7702057078782 45 A2 0 13.73 10
380 Diosmina 500 \N \N 1 \N 7702057075231 45 A2 0 6.04 10
381 Atorvastatina 20 mg \N \N 1 \N 7702057710231 45 A2 0 40.53 10
382 Gabapentin 300 mg \N \N 1 \N 7702057070229 45 A2 0 20.62 10
383 Pregabalina 150 mg \N \N 1 \N 7702057061708 45 A2 0 17.75 10
384 Memantina 10 mg \N \N 1 \N 7702057062217 45 A2 0 13.50 10
385 Escitalopram 20 mg \N \N 1 \N 7702057060930 45 A2 0 24.65 10
386 Risperidona 1 mg \N \N 1 \N 7702057079642 45 A2 0 18.02 10
387 Nimodipino 30 mg \N \N 1 \N 7702057074395 45 A2 0 8.26 10
401 Nor-Kedy Jarabe 120 mL \N \N 1 \N 7410002603421 46 A3 0 8.80 10
402 Nor-Crezinc Jarabe 120 mL \N \N 1 \N 7410002600314 46 A3 0 7.21 10
403 Nor-Crezinc tbletas 50 mg \N \N 1 \N 7410002602899 46 A3 0 15.45 10
404 Nor-Liprox tabletas 20 mg \N \N 1 \N 7410002606019 46 A3 0 34.32 10
408 Nor-Silium tbletas 10 mg \N \N 1 \N 7410002603841 46 A3 0 9.39 10
409 Nor-Clovir Suspensión 125 mL \N \N 1 \N 7410002602912 46 A3 \N 16.36 10
410 Nor-Lodipita tabletas 5 mg \N \N 1 \N 7410002606156 46 A3 0 15.23 10
411 NOR-ALGIA TABLETAS \N \N 1 \N 7410002605074 46 A3 0 22.88 10
412 GASTRIMED 40MG TABLETAS \N \N 1 \N 7410002605029 46 A3 0 32.76 10
413 NOR-GLUCOX 850 MG TABLETAS \N \N 1 \N 7410002606002 46 A3 0 9.36 10
414 NOR-SARTAN 50 MG TABLETAS \N \N 1 \N 7410002603742 46 A3 0 20.69 10
406 Nor-Gerom tabletas 25 mg \N \N 1 \N 7410002600468 46 A3 0 7.8 10
407 Nor-Gerom 75 Forte \N \N 1 \N 7410002600482 46 A3 0 2.65 0
422 NOR- CLOVIR 400 MG TABLETAS \N \N 1 \N 7410002602905 46 A3 0 9.16 10
425 NOR-SECNAL polvo para suspensión \N \N 1 \N 7410002600758 46 A3 \N 6.92 10
426 NOR-SECNAL 500 MG TABLETAS \N \N 1 \N 7410002600727 46 A3 0 6.55 10
427 NOR-AMEB PLUS SUSPENSIÓN \N \N 1 \N 7410002605678 46 A3 \N 13.21 10
428 NOR-VOLTEN FLEX \N \N 1 \N 7410002600970 46 A3 0 34.66 10
429 NOR-CLAMIDA 5 MG TABLETAS \N \N 1 \N 7410002600253 46 A3 \N 1.40 0
430 NOR-CIPROX 500 MG TABLETAS \N \N 1 \N 7410002600215 46 A3 0 15.30 10
397 Nor-Tripr polvo para suspensión 30 mL \N \N 1 \N 7410002603735 46 A3 \N 4.99 10
431 NOR-CIPROX 500 MG TABLETAS \N \N 1 \N 7410002600208 46 A3 0 1 0
379 SULFATO FERROSO \N \N 1 \N 7410002602509 45 A3 0 5.1 10
398 Nor-Tripar Polvo para suspensión 60 mL \N \N 1 \N 7410002603193 46 A3 \N 9.80 10
355 DEAREXIN suspensión oral \N \N 1 \N 7401092211412 44 A1 0 9.25 10
519 NOR-AMEB PLUS TABLETAS \N \N 1 \N 7410002605333 46 A3 0 8.85 10
349 APETIL - CRECE AMPOLLAS BEBIBLES \N \N 1 \N 7401092209419 44 A1 \N 13.95 10
423 NOR- CLOVIR 5G CREMA \N \N 1 \N 7410002602929 46 A3 \N 5.09 10
405 Nor-Vento tabletas 10 mg \N \N 1 \N 7410002604787 46 A3 \N 20.23 10
424 NOR-VIBRAX 50 MG TABLETAS \N \N 1 \N 7410002603520 46 A3 \N 1.82 10
521 NOR-VIBRAX 100 MG tabletas \N \N 1 \N 7410002603599 46 A3 0 2.31 10
435 ciprofloxacina 500 mg tabletas \N \N 1 \N 7702057001995 45 A2 0 1.40 \N
436 CEFTRIAXONA 1G MK SOLUCIÓN INYECTABLE \N \N 1 \N 7702057001933 45 A2 0 12.40 10
437 AMLODIPINO 5MG MK tabletas \N \N 1 \N 7702057001797 45 A2 0 15.53 10
344 acivirex 200mg/5mL suspensión \N \N 1 \N 7401092211016 44 A1 0 13.09 10
439 ENALAPRIL 20 MG MK tabletas \N \N 1 \N 7702057001810 45 A2 0 15.68 10
440 LOSARTAN 100MG MK TABLETAS \N \N 1 \N 7702057001834 45 A2 0 20.91 10
441 METFORMINA 1000 MG MK TABLETAS \N \N 1 \N 7410002802503 45 A2 0 18.54 10
442 CLARITROMICINA 500 MG MK TABLETAS \N \N 1 \N 7410002800622 45 A2 0 31.45 10
445 CLOBEGEN MK CREMA \N \N 1 \N 7410002801896 45 A2 0 6.62 10
446 AMOXICILINA MK SUSPENSIÓN \N \N 1 \N 7410002800110 45 A2 0 4.1 10
447 AZITROMICINA 500 MG MK tabletas \N \N 1 \N 7410002807768 45 A2 0 4 0
448 CEFADROXILO MK 500 MG CÁPSULAS \N \N 1 \N 7410002800448 45 A2 \N 0.75 0
449 CEFALEXINA MK 500 MG CÁPSULAS \N \N 1 \N 7410002800486 45 A2 \N 0.6 0
450 ampcilina mk 500 mg cápsulas \N \N 1 \N 7410002800158 45 A2 0 0.25 0
452 METRONIDAZOL MK 500 MG tabletas \N \N 1 \N 7410002801230 45 A2 0 0.25 0
451 AMOXICILINA MK 500 MG cápsulas \N \N 1 \N 7410002800080 45 A2 \N 0.30 0
400 Nor-Tussol Plus Solución 120 mL \N \N 1 \N 7410002605326 46 A3 \N 10.40 10
458 LIPOTRON SOLUCIÓN ORAL \N \N 1 \N 7401092206616 44 A1 \N 15.99 10
434 NOR- PURINOL 300MG \N \N 1 \N 7410002600710 46 A3 \N 8.63 10
453 FLUCONAZOL MK 150 MG tabletas \N \N 1 \N 7410002802053 45 A2 \N 4 0
523 LEVOFLOXACINO 500 MG MK TABLETAS \N \N 1 \N 7410002802268 45 A2 0 37.35 10
420 NOR-TIAZIDA \N \N 1 \N 7410002603513 46 A3 \N 1.1 0
438 AMLODIPINO 10 MG MK tabletas \N \N 1 \N 7702057001803 45 A2 \N 32.78 10
459 NEUMOFLU MIEL JARABE \N \N 1 \N 7401092203110 44 A1 0 \N 10
443 ACETAMINOFÉN JARABE MK POR 60 ML \N \N 1 \N 74101726 45 A2 \N 3.34 10
418 NOR-DERALL-40 TABLETAS \N \N 1 \N 7410002600376 46 A3 \N 6.20 10
419 TRIMETOPRIM SULFAMETOXAZOL MK 160 800 MG tabletas \N \N 1 \N 7410002600055 45 A2 \N 0.70 0
444 Acetaminofén MK BEBES GOTAS \N \N 1 \N 74101931 45 \N \N 6.83 10
421 NOR-TRYPTOL TABLETAS \N \N 1 \N 7410002600864 46 A3 \N 2.45 \N
\.
--
-- Data for Name: transactions; Type: TABLE DATA; Schema: stock; Owner: postgres
--
COPY stock.transactions (id, date, date_entry, heading_id, source_id, item_id, quantity, total, debit, description) FROM stdin;
60 2022-01-01 2021-12-17 15:39:30.421664 1 \N 296 1 \N 1 Inventario inicial
61 2022-01-01 2021-12-17 15:40:08.679198 1 \N 297 1 \N 1 Inventario inicial
62 2022-01-01 2021-12-17 15:43:01.841201 1 \N 298 1 \N 1 Inventario inicial
63 2022-01-01 2021-12-17 15:49:33.239137 1 \N 302 4 \N 1 Inventario inicial
64 2022-01-01 2021-12-17 15:53:00.883706 1 \N 303 1 \N 1 Inventario inicial
65 2022-01-01 2021-12-17 15:57:54.376372 1 \N 304 2 \N 1 Inventario inicial
67 2022-01-01 2021-12-17 16:06:04.175681 1 \N 306 2 \N 1 Inventario inicial
68 2022-01-01 2021-12-17 16:10:31.90592 1 \N 307 4 \N 1 Inventario inicial
69 2022-01-01 2021-12-17 16:12:56.966152 1 \N 308 1 \N 1 Inventario inicial
70 2022-01-01 2021-12-17 16:14:45.888442 1 \N 309 1 \N 1 Inventario inicial
73 2022-01-01 2021-12-17 16:41:08.718042 1 \N 312 2 \N 1 Inventario inicial
74 2022-01-01 2021-12-17 16:43:47.902414 1 \N 313 3 \N 1 Inventario inicial
75 2022-01-01 2021-12-17 16:49:22.889093 1 \N 314 4 \N 1 Inventario inicial
76 2022-01-01 2021-12-17 17:02:55.578511 1 \N 315 9 \N 1 Inventario inicial
77 2022-01-01 2021-12-17 17:06:24.019477 1 \N 316 2 \N 1 Inventario inicial
78 2022-01-01 2021-12-17 17:08:55.682007 1 \N 317 1 \N 1 Inventario inicial
81 2022-01-01 2021-12-17 17:16:11.734293 1 \N 320 3 \N 1 Inventario inicial
87 2022-01-01 2021-12-17 18:39:49.88762 1 \N 313 1 0.00000000000000000000 0 Inventario inicial
88 2022-01-01 2021-12-17 20:47:10.058738 1 \N 297 1 0.00000000000000000000 0 Inventario inicial
89 2022-01-01 2021-12-17 21:39:25.728179 1 \N 302 1 0.00000000000000000000 0 Inventario inicial
122 2022-01-01 2021-12-20 15:37:02.237642 1 \N 354 2 \N 1 Inventario inicial
123 2022-01-01 2021-12-20 15:43:50.152933 1 \N 355 1 \N 1 Inventario inicial
124 2022-01-01 2021-12-20 15:46:20.680174 1 \N 356 1 \N 1 Inventario inicial
125 2022-01-01 2021-12-20 15:55:09.11536 1 \N 357 8 \N 1 Inventario inicial
126 2022-01-01 2021-12-20 15:58:19.890999 1 \N 358 1 \N 1 Inventario inicial
127 2022-01-01 2021-12-20 16:01:11.938338 1 \N 359 2 \N 1 Inventario inicial
128 2022-01-01 2021-12-20 16:02:42.795151 1 \N 360 1 \N 1 Inventario inicial
129 2022-01-01 2021-12-20 16:04:37.726551 1 \N 361 1 \N 1 Inventario inicial
130 2022-01-01 2021-12-20 16:05:58.286727 1 \N 362 1 \N 1 Inventario inicial
131 2022-01-01 2021-12-20 16:08:19.12502 1 \N 363 1 \N 1 Inventario inicial
132 2022-01-01 2021-12-20 16:10:36.59936 1 \N 364 1 \N 1 Inventario inicial
133 2022-01-01 2021-12-20 16:12:55.953206 1 \N 365 2 \N 1 Inventario inicial
134 2022-01-01 2021-12-20 16:16:53.99698 1 \N 366 1 \N 1 Inventario inicial
135 2022-01-01 2021-12-20 16:18:20.597196 1 \N 367 1 \N 1 Inventario inicial
136 2022-01-01 2021-12-20 16:22:18.609758 1 \N 368 2 \N 1 Inventario inicial
137 2022-01-01 2021-12-20 16:33:31.027829 1 \N 370 3 \N 1 Inventario inicial
138 2022-01-01 2021-12-20 16:35:00.406574 1 \N 371 1 \N 1 Inventario inicial
139 2022-01-01 2021-12-20 16:37:49.457914 1 \N 372 1 \N 1 Inventario inicial
140 2022-01-01 2021-12-20 16:41:59.799201 1 \N 373 1 \N 1 Inventario inicial
141 2022-01-01 2021-12-20 16:43:45.999788 1 \N 374 3 \N 1 Inventario inicial
144 2022-01-01 2021-12-20 17:07:35.752928 1 \N 377 5 \N 1 Inventario inicial
147 2022-01-01 2021-12-20 17:13:13.961208 1 \N 380 4 \N 1 Inventario inicial
148 2022-01-01 2021-12-20 17:15:27.947657 1 \N 381 1 \N 1 Inventario inicial
149 2022-01-01 2021-12-20 17:17:33.493283 1 \N 382 1 \N 1 Inventario inicial
150 2022-01-01 2021-12-20 17:19:20.142742 1 \N 383 2 \N 1 Inventario inicial
152 2022-01-01 2021-12-20 17:22:47.659817 1 \N 385 2 \N 1 Inventario inicial
153 2022-01-01 2021-12-20 17:27:26.472944 1 \N 386 13 \N 1 Inventario inicial
154 2022-01-01 2021-12-20 17:29:52.808689 1 \N 387 1 \N 1 Inventario inicial
155 2022-01-01 2021-12-20 17:33:01.188708 1 \N 388 1 \N 1 Inventario inicial
156 2022-01-01 2021-12-20 17:33:01.193219 1 \N 389 4 \N 1 Inventario inicial
157 2022-01-01 2021-12-20 17:39:40.876614 1 \N 390 1 \N 1 Inventario inicial
158 2022-01-01 2021-12-20 17:47:22.411717 1 \N 391 25 \N 1 Inventario inicial
159 2022-01-01 2021-12-20 17:52:29.080841 1 \N 392 26 \N 1 Inventario inicial
160 2022-01-01 2021-12-20 18:03:57.949256 1 \N 392 1 0.00000000000000000000 0 Inventario inicial
161 2022-01-01 2021-12-20 18:14:53.292685 1 \N 393 4 \N 1 Inventario inicial
162 2022-01-01 2021-12-20 20:58:03.594521 1 \N 394 2 \N 1 Inventario inicial
163 2022-01-01 2021-12-20 21:01:17.559291 1 \N 315 1 0.00000000000000000000 0 Inventario inicial
164 2022-01-01 2021-12-20 21:04:15.008745 1 \N 395 3 \N 1 Inventario inicial
171 2022-01-01 2021-12-20 22:10:04.16057 1 \N 402 9 \N 1 Inventario inicial
175 2022-01-01 2021-12-20 22:23:23.970829 1 \N 406 3 \N 1 Inventario inicial
176 2022-01-01 2021-12-20 22:23:23.975536 1 \N 407 2 \N 1 Inventario inicial
178 2022-01-01 2021-12-20 22:25:18.561958 1 \N 408 2 \N 1 Inventario inicial
179 2022-01-01 2021-12-20 22:28:32.743276 1 \N 409 3 \N 1 Inventario inicial
180 2022-01-01 2021-12-20 22:28:32.747862 1 \N 410 8 \N 1 Inventario inicial
182 2022-01-01 2021-12-20 22:37:58.435736 1 \N 412 7 \N 1 Inventario inicial
183 2022-01-01 2021-12-20 22:37:58.440487 1 \N 413 10 \N 1 Inventario inicial
184 2022-01-01 2021-12-20 22:39:23.949464 1 \N 414 2 \N 1 Inventario inicial
185 2022-01-01 2021-12-20 22:43:19.063521 1 \N 415 60 \N 1 Inventario inicial
181 2022-01-01 2021-12-20 22:34:08.976331 1 \N 411 108 \N 1 Inventario inicial
193 2022-01-01 2021-12-22 21:56:34.38838 1 \N 421 1 \N 1 Inventario inicial
194 2022-01-01 2021-12-22 22:06:20.933619 1 \N 422 2 \N 1 Inventario inicial
195 2022-01-01 2021-12-22 22:09:37.226836 1 \N 423 2 \N 1 Inventario inicial
197 2022-01-01 2021-12-22 22:17:27.121623 1 \N 425 1 \N 1 Inventario inicial
198 2022-01-01 2021-12-22 22:19:34.180903 1 \N 426 1 \N 1 Inventario inicial
199 2022-01-01 2021-12-22 22:22:15.643524 1 \N 427 1 \N 1 Inventario inicial
200 2022-01-01 2021-12-22 22:24:40.843621 1 \N 428 1 \N 1 Inventario inicial
204 2022-01-01 2021-12-22 22:59:59.79298 1 \N 432 1 \N 1 Inventario inicial
205 2022-01-01 2021-12-22 23:01:36.511252 1 \N 433 3 \N 1 Inventario inicial
206 2022-01-01 2021-12-22 23:07:15.426282 1 \N 434 14 \N 1 Inventario inicial
207 2022-01-01 2021-12-22 23:10:30.06182 1 \N 435 20 \N 1 Inventario inicial
208 2022-01-01 2021-12-22 23:10:30.06633 1 \N 436 60 \N 1 Inventario inicial
209 2022-01-01 2021-12-22 23:12:43.007591 1 \N 437 4 \N 1 Inventario inicial
210 2022-01-01 2021-12-22 23:12:43.012399 1 \N 438 30 \N 1 Inventario inicial
211 2022-01-01 2021-12-22 23:19:11.514565 1 \N 439 100 \N 1 Inventario inicial
215 2022-01-01 2021-12-22 23:26:46.699768 1 \N 443 12 \N 1 Inventario inicial
313 2022-01-01 2021-12-28 21:12:45.479864 1 \N 375 3 \N 1 Inventario inicial
314 2022-01-01 2021-12-28 21:16:55.123916 1 \N 376 6 \N 1 Inventario inicial
315 2022-01-01 2021-12-28 21:23:00.808476 1 \N 514 5 \N 1 Inventario inicial
322 2022-01-01 2021-12-28 21:30:42.319409 1 \N 440 6 \N 1 Inventario inicial
323 2022-01-01 2021-12-28 21:30:42.323102 1 \N 441 54 \N 1 Inventario inicial
324 2022-01-01 2021-12-28 21:34:00.409889 1 \N 378 1 \N 1 Inventario inicial
325 2022-01-01 2021-12-28 21:34:00.413505 1 \N 379 7 \N 1 Inventario inicial
238 2022-01-01 2021-12-24 00:35:52.572683 1 \N 318 6 \N 1 Inventario inicial
239 2022-01-01 2021-12-24 00:35:52.576928 1 \N 319 1 \N 1 Inventario inicial
326 2022-01-01 2021-12-28 21:36:09.767462 1 \N 450 1 \N 1 Inventario inicial
241 2022-01-01 2021-12-24 00:42:15.046562 1 \N 305 2 \N 1 Inventario inicial
327 2022-01-01 2021-12-28 21:44:19.711001 1 \N 296 1 0.00000000000000000000 0 Inventario inicial
244 2022-01-01 2021-12-24 00:54:33.247428 1 \N 451 1 \N 1 Inventario inicial
328 2022-01-01 2021-12-28 21:49:06.004572 1 \N 355 1 0.00000000000000000000 0 Inventario inicial
329 2022-01-01 2021-12-28 21:53:10.999198 1 \N 398 9 \N 1 Inventario inicial
330 2022-01-01 2021-12-28 21:57:12.517725 1 \N 420 11 \N 1 Inventario inicial
331 2022-01-01 2021-12-28 21:58:08.170166 1 \N 403 9 \N 1 Inventario inicial
332 2022-01-01 2021-12-28 22:04:38.011524 1 \N 404 27 \N 1 Inventario inicial
333 2022-01-01 2021-12-28 22:06:16.647803 1 \N 515 23 \N 1 Inventario inicial
334 2022-01-01 2021-12-28 22:07:26.755186 1 \N 444 8 \N 1 Inventario inicial
335 2022-01-01 2021-12-28 22:20:10.935308 1 \N 516 1 \N 1 Inventario inicial
342 2022-01-01 2021-12-30 20:57:10.332345 1 \N 424 1 \N 1 Inventario inicial
355 2022-01-01 2022-01-02 14:43:59.404826 1 \N 429 5 \N 1 Inventario inicial
356 2022-01-01 2022-01-02 14:43:59.409249 1 \N 430 1 \N 1 Inventario inicial
367 2022-01-01 2022-02-22 16:07:30.365952 1 \N 399 15 \N 1 Inventario inicial
368 2022-01-01 2022-02-22 16:07:30.36981 1 \N 442 25 \N 1 Inventario inicial
369 2022-01-01 2022-03-11 02:28:14.825404 1 \N 431 1 \N 1 Inventario inicial
372 2022-01-01 2022-03-11 02:30:20.273053 1 \N 401 12 \N 1 Inventario inicial
\.
--
-- Name: account_types_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.account_types_id_seq', 16, true);
--
-- Name: accounts_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.accounts_id_seq', 55, true);
--
-- Name: cost_center_status_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.cost_center_status_id_seq', 1, true);
--
-- Name: cost_centers_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.cost_centers_id_seq', 2, true);
--
-- Name: detail_sub_accounts_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.detail_sub_accounts_id_seq', 1, true);
--
-- Name: entries_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.entries_id_seq', 122, true);
--
-- Name: headings_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.headings_id_seq', 75, true);
--
-- Name: periods_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.periods_id_seq', 2, true);
--
-- Name: receivable_payable_transactions_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.receivable_payable_transactions_id_seq', 1, false);
--
-- Name: sequences_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.sequences_id_seq', 1, true);
--
-- Name: sub_accounts_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--
SELECT pg_catalog.setval('account.sub_accounts_id_seq', 223, true);
--
-- Name: attendance_id_seq; Type: SEQUENCE SET; Schema: att; Owner: postgres
--
SELECT pg_catalog.setval('att.attendance_id_seq', 4, true);
--
-- Name: attendance_statuses_id_seq; Type: SEQUENCE SET; Schema: att; Owner: postgres
--
SELECT pg_catalog.setval('att.attendance_statuses_id_seq', 3, true);
--
-- Name: detail_id_seq; Type: SEQUENCE SET; Schema: invoice; Owner: postgres
--
SELECT pg_catalog.setval('invoice.detail_id_seq', 629, true);
--
-- Name: headers_id_seq; Type: SEQUENCE SET; Schema: invoice; Owner: postgres
--
SELECT pg_catalog.setval('invoice.headers_id_seq', 220, true);
--
-- Name: types_id_seq; Type: SEQUENCE SET; Schema: invoice; Owner: postgres
--
SELECT pg_catalog.setval('invoice.types_id_seq', 9, true);
--
-- Name: sequences_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.sequences_id_seq', 6, true);
--
-- Name: usr_content_types_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_content_types_id_seq', 3, true);
--
-- Name: usr_persons_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_persons_id_seq', 50, true);
--
-- Name: usr_supplier_types_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_supplier_types_id_seq', 3, true);
--
-- Name: usr_sys_roles_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_sys_roles_id_seq', 4, true);
--
-- Name: usr_x_data_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_x_data_id_seq', 155, true);
--
-- Name: usr_x_labs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('public.usr_x_labs_id_seq', 1, false);
--
-- Name: categories_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.categories_id_seq', 1, false);
--
-- Name: category_title_link_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.category_title_link_id_seq', 1, false);
--
-- Name: items_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.items_id_seq', 518, true);
--
-- Name: presentation_title_link_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.presentation_title_link_id_seq', 645, true);
--
-- Name: titles_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.titles_id_seq', 529, true);
--
-- Name: transactions_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--
SELECT pg_catalog.setval('stock.transactions_id_seq', 609, true);
--
-- Name: entries co_asientos_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries
ADD CONSTRAINT co_asientos_pkey PRIMARY KEY (id);
--
-- Name: account_types co_clases_de_cuentas_codigo_key; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.account_types
ADD CONSTRAINT co_clases_de_cuentas_codigo_key UNIQUE (code);
--
-- Name: account_types co_clases_de_cuentas_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.account_types
ADD CONSTRAINT co_clases_de_cuentas_pkey PRIMARY KEY (id);
--
-- Name: accounts co_cuentas_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.accounts
ADD CONSTRAINT co_cuentas_pkey PRIMARY KEY (id);
--
-- Name: headings co_partidas_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.headings
ADD CONSTRAINT co_partidas_pkey PRIMARY KEY (id);
--
-- Name: sequences co_secuencias_nom_un; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sequences
ADD CONSTRAINT co_secuencias_nom_un UNIQUE (name);
--
-- Name: sequences co_secuencias_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sequences
ADD CONSTRAINT co_secuencias_pkey PRIMARY KEY (id);
--
-- Name: sub_accounts co_subcuentas_codigo_uni; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sub_accounts
ADD CONSTRAINT co_subcuentas_codigo_uni UNIQUE (code);
--
-- Name: sub_accounts co_subcuentas_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sub_accounts
ADD CONSTRAINT co_subcuentas_pkey PRIMARY KEY (id);
--
-- Name: accounts cuentas_co_unique; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.accounts
ADD CONSTRAINT cuentas_co_unique UNIQUE (code);
--
-- Name: periods ejercicios_co_unique; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.periods
ADD CONSTRAINT ejercicios_co_unique UNIQUE (code);
--
-- Name: periods ejercicios_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.periods
ADD CONSTRAINT ejercicios_pkey PRIMARY KEY (id);
--
-- Name: detail_sub_accounts f_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.detail_sub_accounts
ADD CONSTRAINT f_pkey PRIMARY KEY (id);
--
-- Name: cost_centers fffy; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_centers
ADD CONSTRAINT fffy PRIMARY KEY (id);
--
-- Name: cost_center_status ffggfy; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_center_status
ADD CONSTRAINT ffggfy PRIMARY KEY (id);
--
-- Name: cost_center_status hchh; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_center_status
ADD CONSTRAINT hchh UNIQUE (name);
--
-- Name: cost_centers hhh; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_centers
ADD CONSTRAINT hhh UNIQUE (code);
--
-- Name: headings partidas_nu_unique; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.headings
ADD CONSTRAINT partidas_nu_unique UNIQUE (number);
--
-- Name: receivable_payable_transactions_from_invoices receivable_payable_transactions_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.receivable_payable_transactions_from_invoices
ADD CONSTRAINT receivable_payable_transactions_pkey PRIMARY KEY (id);
--
-- Name: entries sfdd; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries
ADD CONSTRAINT sfdd UNIQUE (heading_id, correlative);
--
-- Name: detail_sub_accounts tas_codigo_uni; Type: CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.detail_sub_accounts
ADD CONSTRAINT tas_codigo_uni UNIQUE (code);
--
-- Name: attendance attendance_pkey; Type: CONSTRAINT; Schema: att; Owner: postgres
--
ALTER TABLE ONLY att.attendance
ADD CONSTRAINT attendance_pkey PRIMARY KEY (id);
--
-- Name: attendance_statuses attendance_statuses_pkey; Type: CONSTRAINT; Schema: att; Owner: postgres
--
ALTER TABLE ONLY att.attendance_statuses
ADD CONSTRAINT attendance_statuses_pkey PRIMARY KEY (id);
--
-- Name: types FCcFggK; Type: CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.types
ADD CONSTRAINT "FCcFggK" UNIQUE (name);
--
-- Name: headers cabeceras_pkey; Type: CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers
ADD CONSTRAINT cabeceras_pkey PRIMARY KEY (id);
--
-- Name: detail pk_facturas_lineas; Type: CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.detail
ADD CONSTRAINT pk_facturas_lineas PRIMARY KEY (id);
--
-- Name: types pkneas; Type: CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.types
ADD CONSTRAINT pkneas PRIMARY KEY (id);
--
-- Name: usr_sys_roles 000iii; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_sys_roles
ADD CONSTRAINT "000iii" UNIQUE (descripcion);
--
-- Name: usr_x_sys dd; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_sys
ADD CONSTRAINT dd UNIQUE (username);
--
-- Name: usr_x_data dd442; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_data
ADD CONSTRAINT dd442 UNIQUE (person_id, type, sort);
--
-- Name: usr_x_data fe45; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_data
ADD CONSTRAINT fe45 PRIMARY KEY (id);
--
-- Name: usr_supplier_types pggkubxp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_supplier_types
ADD CONSTRAINT pggkubxp PRIMARY KEY (id);
--
-- Name: usr_persons pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_persons
ADD CONSTRAINT pk PRIMARY KEY (id);
--
-- Name: usr_x_business pksubxp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_business
ADD CONSTRAINT pksubxp PRIMARY KEY (id);
--
-- Name: usr_x_employee pkubxp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_employee
ADD CONSTRAINT pkubxp PRIMARY KEY (id);
--
-- Name: usr_x_sys pkuxp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_sys
ADD CONSTRAINT pkuxp PRIMARY KEY (id);
--
-- Name: usr_x_labs prkubxcp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_labs
ADD CONSTRAINT prkubxcp PRIMARY KEY (id);
--
-- Name: sequences sequences_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.sequences
ADD CONSTRAINT sequences_pkey PRIMARY KEY (id);
--
-- Name: usr_content_types serialid; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_content_types
ADD CONSTRAINT serialid PRIMARY KEY (id);
--
-- Name: usr_persons usr_persons_code_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_persons
ADD CONSTRAINT usr_persons_code_key UNIQUE (code);
--
-- Name: usr_sys_roles usr_sys_roles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_sys_roles
ADD CONSTRAINT usr_sys_roles_pkey PRIMARY KEY (id);
--
-- Name: sequences xczfsdfsd; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.sequences
ADD CONSTRAINT xczfsdfsd UNIQUE (code);
--
-- Name: titles barcode_; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.titles
ADD CONSTRAINT barcode_ UNIQUE (barcode);
--
-- Name: categories categories_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.categories
ADD CONSTRAINT categories_pkey PRIMARY KEY (id);
--
-- Name: presentation_title_link category_title_liddnk_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.presentation_title_link
ADD CONSTRAINT category_title_liddnk_pkey PRIMARY KEY (id);
--
-- Name: category_title_link category_title_link_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.category_title_link
ADD CONSTRAINT category_title_link_pkey PRIMARY KEY (id);
--
-- Name: items items_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.items
ADD CONSTRAINT items_pkey PRIMARY KEY (id);
--
-- Name: transactions stock_transactions_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.transactions
ADD CONSTRAINT stock_transactions_pkey PRIMARY KEY (id);
--
-- Name: titles titles_pkey; Type: CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.titles
ADD CONSTRAINT titles_pkey PRIMARY KEY (id);
--
-- Name: invoices insert_update_delete_invoices_trg; Type: TRIGGER; Schema: invoice; Owner: postgres
--
CREATE TRIGGER insert_update_delete_invoices_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON invoice.invoices FOR EACH ROW EXECUTE FUNCTION invoice.insert_update_delete_invoice();
--
-- Name: usr_business insert_update_delete_usr_business_trg; Type: TRIGGER; Schema: public; Owner: postgres
--
CREATE TRIGGER insert_update_delete_usr_business_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON public.usr_business FOR EACH ROW EXECUTE FUNCTION public.insert_update_delete_usr_business();
--
-- Name: usr_employee insert_update_delete_usr_employee_trg; Type: TRIGGER; Schema: public; Owner: postgres
--
CREATE TRIGGER insert_update_delete_usr_employee_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON public.usr_employee FOR EACH ROW EXECUTE FUNCTION public.insert_update_delete_usr_employee();
--
-- Name: usr_labs insert_update_delete_usr_labs_trg; Type: TRIGGER; Schema: public; Owner: postgres
--
CREATE TRIGGER insert_update_delete_usr_labs_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON public.usr_labs FOR EACH ROW EXECUTE FUNCTION public.insert_update_delete_usr_labs();
--
-- Name: usr_sys insert_update_delete_usr_sys_trg; Type: TRIGGER; Schema: public; Owner: postgres
--
CREATE TRIGGER insert_update_delete_usr_sys_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON public.usr_sys FOR EACH ROW EXECUTE FUNCTION public.insert_update_delete_usr_sys();
--
-- Name: articles insert_update_delete_articles_trg; Type: TRIGGER; Schema: stock; Owner: postgres
--
CREATE TRIGGER insert_update_delete_articles_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON stock.articles FOR EACH ROW EXECUTE FUNCTION stock.insert_update_delete_articles();
--
-- Name: sub_accounts FKCS; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.sub_accounts
ADD CONSTRAINT "FKCS" FOREIGN KEY (account_id) REFERENCES account.accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: detail_sub_accounts FffKCS; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.detail_sub_accounts
ADD CONSTRAINT "FffKCS" FOREIGN KEY (sub_account_id) REFERENCES account.sub_accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: detail_sub_accounts FffKCddS; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.detail_sub_accounts
ADD CONSTRAINT "FffKCddS" FOREIGN KEY (user_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: entries co_asientos_id_partida_fkey; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries
ADD CONSTRAINT co_asientos_id_partida_fkey FOREIGN KEY (heading_id) REFERENCES account.headings(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: entries co_asientos_id_subcuenta_fkey; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries
ADD CONSTRAINT co_asientos_id_subcuenta_fkey FOREIGN KEY (sub_account_id) REFERENCES account.sub_accounts(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: accounts co_cuentas_tipo_fkey; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.accounts
ADD CONSTRAINT co_cuentas_tipo_fkey FOREIGN KEY (type) REFERENCES account.account_types(id);
--
-- Name: headings dfgkjl; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.headings
ADD CONSTRAINT dfgkjl FOREIGN KEY (period_id) REFERENCES account.periods(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: entries dsfsdfds; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.entries
ADD CONSTRAINT dsfsdfds FOREIGN KEY (cost_center_id) REFERENCES account.cost_centers(id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID;
--
-- Name: cost_centers ghy; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_centers
ADD CONSTRAINT ghy FOREIGN KEY (period_id) REFERENCES account.periods(id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID;
--
-- Name: receivable_payable_transactions_from_invoices gvbdf; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.receivable_payable_transactions_from_invoices
ADD CONSTRAINT gvbdf FOREIGN KEY (heading_id) REFERENCES account.headings(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: receivable_payable_transactions_from_invoices jki; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.receivable_payable_transactions_from_invoices
ADD CONSTRAINT jki FOREIGN KEY (subaccount_id) REFERENCES account.detail_sub_accounts(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: receivable_payable_transactions_from_invoices sdfsdt; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.receivable_payable_transactions_from_invoices
ADD CONSTRAINT sdfsdt FOREIGN KEY (user_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: cost_centers ty; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--
ALTER TABLE ONLY account.cost_centers
ADD CONSTRAINT ty FOREIGN KEY (status) REFERENCES account.cost_center_status(id) ON UPDATE CASCADE ON DELETE SET DEFAULT NOT VALID;
--
-- Name: attendance attendance_fk_user_id; Type: FK CONSTRAINT; Schema: att; Owner: postgres
--
ALTER TABLE ONLY att.attendance
ADD CONSTRAINT attendance_fk_user_id FOREIGN KEY (user_id) REFERENCES public.usr_persons(id) NOT VALID;
--
-- Name: detail FCFggK; Type: FK CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.detail
ADD CONSTRAINT "FCFggK" FOREIGN KEY (header_id) REFERENCES invoice.headers(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: headers fdt; Type: FK CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers
ADD CONSTRAINT fdt FOREIGN KEY (type) REFERENCES invoice.types(id) ON UPDATE CASCADE ON DELETE RESTRICT;
--
-- Name: headers fkoid; Type: FK CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers
ADD CONSTRAINT fkoid FOREIGN KEY (owner_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE RESTRICT;
--
-- Name: headers headers_heading_id_fkey; Type: FK CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers
ADD CONSTRAINT headers_heading_id_fkey FOREIGN KEY (heading_id) REFERENCES account.headings(id) ON UPDATE CASCADE ON DELETE SET NULL NOT VALID;
--
-- Name: headers td_fkey; Type: FK CONSTRAINT; Schema: invoice; Owner: postgres
--
ALTER TABLE ONLY invoice.headers
ADD CONSTRAINT td_fkey FOREIGN KEY (user_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE SET NULL;
--
-- Name: usr_x_data dd44; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_data
ADD CONSTRAINT dd44 FOREIGN KEY (type) REFERENCES public.usr_content_types(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_data df56; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_data
ADD CONSTRAINT df56 FOREIGN KEY (person_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_labs hrytncfgh; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_labs
ADD CONSTRAINT hrytncfgh FOREIGN KEY (id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_sys hytfgh; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_sys
ADD CONSTRAINT hytfgh FOREIGN KEY (id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_employee hytnfgh; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_employee
ADD CONSTRAINT hytnfgh FOREIGN KEY (id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_business hytsdnh; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_business
ADD CONSTRAINT hytsdnh FOREIGN KEY (type) REFERENCES public.usr_supplier_types(id) ON UPDATE CASCADE ON DELETE SET DEFAULT;
--
-- Name: usr_x_business hytsnsfgh; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_business
ADD CONSTRAINT hytsnsfgh FOREIGN KEY (id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: usr_x_sys sdfds; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.usr_x_sys
ADD CONSTRAINT sdfds FOREIGN KEY (role_id) REFERENCES public.usr_sys_roles(id) ON UPDATE CASCADE ON DELETE SET DEFAULT NOT VALID;
--
-- Name: presentation_title_link dcdddxc; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.presentation_title_link
ADD CONSTRAINT dcdddxc FOREIGN KEY (title_id) REFERENCES stock.titles(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: category_title_link dcdxc; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.category_title_link
ADD CONSTRAINT dcdxc FOREIGN KEY (title_id) REFERENCES stock.titles(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: transactions hedfdw; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.transactions
ADD CONSTRAINT hedfdw FOREIGN KEY (heading_id) REFERENCES account.headings(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: transactions kkk; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.transactions
ADD CONSTRAINT kkk FOREIGN KEY (source_id) REFERENCES invoice.detail(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: items sdf444wv; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.items
ADD CONSTRAINT sdf444wv FOREIGN KEY (title_id) REFERENCES stock.titles(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: category_title_link sdfsdi; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.category_title_link
ADD CONSTRAINT sdfsdi FOREIGN KEY (category_id) REFERENCES stock.categories(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: titles sdfwv; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.titles
ADD CONSTRAINT sdfwv FOREIGN KEY (lab_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- Name: transactions sdsdfge; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.transactions
ADD CONSTRAINT sdsdfge FOREIGN KEY (item_id) REFERENCES stock.items(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- Name: items supfk; Type: FK CONSTRAINT; Schema: stock; Owner: postgres
--
ALTER TABLE ONLY stock.items
ADD CONSTRAINT supfk FOREIGN KEY (supplier_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
--
-- PostgreSQL database dump complete
--