--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- 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: payrolls; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA payrolls;


ALTER SCHEMA payrolls 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: entries_plus(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.entries_plus(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_name', account.subaccount_name(d.sub_account_id),
                    '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_plus(iid integer) OWNER TO postgres;

--
-- Name: entries_transactions(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.entries_transactions(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,
          'transaction_id', d.transaction_id,
          'date', d.date,
          'user_id', d.user_id,
          'entry_id', d.entry_id,
          'total', d.total
            ))::TEXT
    INTO ret_val
    FROM account.entries_transactions d WHERE heading_id=iId;
    RETURN ret_val;
  END;
$$;


ALTER FUNCTION account.entries_transactions(iid integer) OWNER TO postgres;

--
-- Name: entries_transactions_tonly(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.entries_transactions_tonly(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,
          'transaction_id', d.transaction_id,
          'date', d.date,
          'user_id', d.user_id,
          'entry_id', d.entry_id,
          'total', d.total
            ))::TEXT
    INTO ret_val
    FROM account.entries_transactions d WHERE heading_id=iId AND d.user_id IS NOT NULL;
    RETURN ret_val;
  END;
$$;


ALTER FUNCTION account.entries_transactions_tonly(iid integer) OWNER TO postgres;

--
-- Name: hading_data_from_invoice_id(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.hading_data_from_invoice_id(iid integer) RETURNS record
    LANGUAGE plpgsql
    AS $$

DECLARE
        ret_val RECORD=0;
        re INTEGER=0;
    BEGIN
        SELECT *  INTO ret_val FROM account.headings WHERE id=1;
        
        RETURN ret_val;
    END;
$$;


ALTER FUNCTION account.hading_data_from_invoice_id(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=100;
		jsElementos jsonb;
	BEGIN
        IF elementos<>'[]' THEN
            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::INT > 0
                   RETURNING 1
                )
                SELECT count(*) FROM rows  INTO ret_val;
        END IF;    
            --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_journal_entry(text, text); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_journal_entry(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_journal_entry_transactions(detail,ret_val) INTO re;
        RETURN ret_val;
    END;
$$;


ALTER FUNCTION account.insert_journal_entry(head text, detail text) OWNER TO postgres;

--
-- Name: insert_journal_entry_transactions(text); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_journal_entry_transactions(elementos text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
		ret_val INTEGER=0;
		jsElementos jsonb;
	BEGIN
        IF elementos<>'[]' THEN
            jsElementos=elementos::jsonb;
            WITH rows AS (
                  INSERT INTO account.entries_transactions (id,heading_id,correlative,sub_account_id,amount,isdebit,cost_center_id,detail_sub_account_id,transaction_id,date,user_id,entry_id,total)
                      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,amount,isdebit,cost_center_id,detail_sub_account_id,transaction_id,date,user_id,entry_id,total
                          FROM jsonb_populate_recordset (NULL::account.entries_transactions, jsElementos)
                   RETURNING 1
                )
                SELECT count(*) FROM rows  INTO ret_val;
        END IF;    
		RETURN ret_val;
	END;
$$;


ALTER FUNCTION account.insert_journal_entry_transactions(elementos text) OWNER TO postgres;

--
-- Name: insert_journal_entry_transactions(text, integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_journal_entry_transactions(elementos text, iid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    ret_val INTEGER=0;
    jsElementos jsonb;
  BEGIN
        IF elementos<>'[]' THEN
            jsElementos=elementos::jsonb;
            WITH rows AS (
                  INSERT INTO account.entries_transactions (id,heading_id,correlative,sub_account_id,amount,isdebit,cost_center_id,detail_sub_account_id,transaction_id,date,user_id,entry_id,total)
                      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,amount,isdebit,cost_center_id,detail_sub_account_id,transaction_id,date,user_id,entry_id,total
                          FROM jsonb_populate_recordset (NULL::account.entries_transactions, jsElementos)
                   RETURNING 1
                )
                SELECT count(*) FROM rows  INTO ret_val;
        END IF;    
    RETURN ret_val;
  END;
$$;


ALTER FUNCTION account.insert_journal_entry_transactions(elementos text, iid integer) 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_entries_transactions(); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_update_delete_entries_transactions() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    entero integer=0;
    entero2 integer=0;
  BEGIN
    IF NEW.id IS NULL OR NOT (NEW.id > 0) THEN
      SELECT nextval('account.entries_id_seq'::regclass)::integer INTO entero;
    ELSE
      entero=NEW.id;
    END IF;
    IF TG_OP = 'INSERT' THEN
      IF NEW.id IS NULL OR NOT (NEW.id > 0) THEN
        SELECT nextval('account.entries_id_seq'::regclass)::integer INTO entero;
      ELSE
        entero=NEW.id;
      END IF;
      IF NEW.transaction_id IS NULL OR NOT (NEW.transaction_id > 0) THEN
        SELECT nextval('account.receivable_payable_transactions_id_seq'::regclass)::integer INTO entero2;
      ELSE
        entero2=NEW.transaction_id;
      END IF;
      INSERT INTO account.entries 
        (id,heading_id,correlative,sub_account_id,amount,isdebit,cost_center_id,detail_sub_account_id)
          VALUES
        (entero,NEW.heading_id,NEW.correlative,NEW.sub_account_id,NEW.amount,NEW.isdebit,NEW.cost_center_id,NEW.detail_sub_account_id) RETURNING id INTO entero;
      IF NEW.user_id IS NOT NULL AND NEW.user_id::TEXT <> '' AND NEW.user_id::INTEGER > 0 THEN
        INSERT INTO account.usr_transactions 
          (id,date,user_id,isdebit,entry_id,total)
            VALUES
          (entero2,NEW.date,NEW.user_id,NEW.isdebit::INTEGER,entero,NEW.total);
      END IF;
    ELSIF TG_OP = 'UPDATE' THEN
      UPDATE account.entries SET
        id=NEW.id,heading_id=NEW.heading_id,correlative=NEW.correlative,sub_account_id=NEW.sub_account_id,amount=NEW.amount,isdebit=NEW.isdebit,cost_center_id=NEW.cost_center_id,detail_sub_account_id=NEW.detail_sub_account_id
        WHERE id=OLD.id;
      UPDATE account.usr_transactions SET
        id=NEW.id,date=NEW.date,user_id=NEW.user_id,isdebit=NEW.isdebit::INTEGER,entry_id=NEW.entry_id,total=NEW.total
        WHERE id=OLD.id;
    ELSIF TG_OP = 'DELETE' THEN
      DELETE FROM account.entries
        WHERE id =OLD.id;
      RETURN NULL;
    END IF;
    NEW.id=entero;--for returning it    
    RETURN NEW;
  END;
$$;


ALTER FUNCTION account.insert_update_delete_entries_transactions() 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 OR NOT (NEW.id > 0) 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: insert_update_delete_users_journal_entries(); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_update_delete_users_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 OR NOT (NEW.id > 0) 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_journal_entry_transactions(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_journal_entry_transactions(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_users_journal_entries() OWNER TO postgres;

--
-- Name: insert_usr_transactions(text); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.insert_usr_transactions(elementos text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
		ret_val INTEGER=100;
		jsElementos jsonb;
	BEGIN
        IF elementos<>'[]' THEN
            jsElementos=elementos::jsonb;
            WITH rows AS (
                  INSERT INTO account.usr_transactions (id,date,entry,isdebit,user_id,entry_id,total)
                      SELECT  
                        (
                          CASE
                           WHEN  id::text = '' OR id::INT = 0 THEN
                              nextval('account.receivable_payable_transactions_id_seq'::regclass)::integer
                          ELSE
                              id
                           END
                        ),date,('NOW'::text)::timestamp without time zone,isdebit,user_id,entry_id,total
                          FROM jsonb_populate_recordset (NULL::account.usr_transactions, jsElementos)
                   RETURNING 1
                )
                SELECT count(*) FROM rows  INTO ret_val;
        END IF;    
		RETURN ret_val;
	END;
$$;


ALTER FUNCTION account.insert_usr_transactions(elementos text) 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: subaccount_name(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.subaccount_name(id_ integer) RETURNS text
    LANGUAGE plpgsql
    AS $$

DECLARE
	ret_val text;
BEGIN
	SELECT name::text INTO ret_val FROM account.sub_accounts WHERE id= id_;
	RETURN ret_val;
END;
$$;


ALTER FUNCTION account.subaccount_name(id_ integer) 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_journal_entry(integer, text, text); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.update_journal_entry(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_journal_entry_transactions(detail,ret_val) INTO re;
        RETURN ret_val;
    END;
$$;


ALTER FUNCTION account.update_journal_entry(id_ integer, head text, detail text) OWNER TO postgres;

--
-- Name: update_journal_entry_transactions(text, integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.update_journal_entry_transactions(elementos text, id_ integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    ret_val INTEGER=0;
  BEGIN
    DELETE FROM account.entries_transactions WHERE heading_id=id_;
    SELECT account.insert_journal_entry_transactions(elementos,id_) INTO ret_val;
    RETURN ret_val;
  END;
$$;


ALTER FUNCTION account.update_journal_entry_transactions(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: user_associated(integer); Type: FUNCTION; Schema: account; Owner: postgres
--

CREATE FUNCTION account.user_associated(iid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$

    DECLARE
        ret_val integer=0;
    BEGIN
        IF iId IN (SELECT account_id FROM account.usr_associated_accounts) THEN
            ret_val=1;
        END IF;
        RETURN ret_val;
    END;
$$;


ALTER FUNCTION account.user_associated(iid integer) 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,'&quot;','~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],';<br>');
			IF array_length(arr,1)>max_int THEN
				ret_val = ret_val || ';<br>etc...';
			END IF;
		END IF;
		RETURN ret_val;
	END;
$$;


ALTER FUNCTION invoice.detail_summ(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,sarg,terms,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,NEW.sarg,NEW.terms,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,sarg=NEW.sarg,terms=NEW.terms,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')
         LPAD((s.value::integer+1)::text,4,'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: balance(integer, numeric); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.balance(isdebit_ integer, employee numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $$

DECLARE
    ret_val NUMERIC=0;
BEGIN
	SELECT balance(isdebit_,employee,2,2) INTO ret_val;
    RETURN ret_val;
END;
$$;


ALTER FUNCTION public.balance(isdebit_ integer, employee numeric) OWNER TO postgres;

--
-- Name: balance(integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.balance(isdebit_ integer, employee numeric, cc integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $$

DECLARE
    ret_val NUMERIC=0;
BEGIN
	SELECT balance(isdebit_,employee,cc,2) INTO ret_val;
    RETURN ret_val;
END;
$$;


ALTER FUNCTION public.balance(isdebit_ integer, employee numeric, cc integer) OWNER TO postgres;

--
-- Name: balance(integer, numeric, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.balance(isdebit_ integer, employee numeric, cc integer, period integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $$

DECLARE
    ret_val NUMERIC=0;
    tmp NUMERIC=0;
BEGIN
	SELECT COALESCE(sum(t.total),0) INTO ret_val FROM account.entries_transactions t
        LEFT JOIN account.headings h ON t.heading_id = h.id
        WHERE t.user_id IN (employee) AND t.cost_center_id = cc AND h.period_id = period
        AND isdebit::boolean;
	SELECT COALESCE(sum(t.total),0) INTO tmp FROM account.entries_transactions t
        LEFT JOIN account.headings h ON t.heading_id = h.id
        WHERE t.user_id IN (employee) AND t.cost_center_id = cc AND h.period_id = period
        AND NOT isdebit::boolean;
	IF NOT isdebit_::bool THEN
        ret_val=-ret_val;
    END IF;
    RETURN ret_val-tmp;
END;
$$;


ALTER FUNCTION public.balance(isdebit_ integer, employee numeric, cc integer, period 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;



--
-- 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: usr_transactions; Type: TABLE; Schema: account; Owner: postgres
--

CREATE TABLE account.usr_transactions (
    id integer NOT NULL,
    date date,
    entry timestamp without time zone,
    isdebit integer,
    user_id integer,
    entry_id integer,
    total numeric
);


ALTER TABLE account.usr_transactions OWNER TO postgres;

--
-- Name: entries_transactions; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.entries_transactions AS
 SELECT e.id,
    e.heading_id,
    e.correlative,
    e.sub_account_id,
    e.amount,
    e.isdebit,
    e.cost_center_id,
    e.detail_sub_account_id,
    u.id AS transaction_id,
    u.date,
    u.user_id,
    u.entry_id,
    u.total
   FROM (account.entries e
     LEFT JOIN account.usr_transactions u ON ((u.entry_id = e.id)));


ALTER TABLE account.entries_transactions OWNER TO postgres;

--
-- 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: entries_transactions_plus; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.entries_transactions_plus AS
 SELECT e.id,
    e.heading_id,
    h.summary,
    e.correlative,
    e.sub_account_id,
    e.amount,
    e.isdebit,
    e.cost_center_id,
    e.detail_sub_account_id,
    e.transaction_id,
    e.date,
    e.user_id,
    e.entry_id,
    e.total
   FROM (account.entries_transactions e
     LEFT JOIN account.headings h ON ((e.heading_id = h.id)));


ALTER TABLE account.entries_transactions_plus 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: journal_entries_plus; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.journal_entries_plus AS
 SELECT h.id,
    h.number,
    h.date,
    h.summary,
    h.period_id,
    account.entries_plus(h.id) AS entries
   FROM account.headings h
  WHERE (h.status)::boolean;


ALTER TABLE account.journal_entries_plus 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_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.usr_transactions.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: sub_accounts_view; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.sub_accounts_view AS
 SELECT sub_accounts.id,
    sub_accounts.account_id,
    sub_accounts.code,
    sub_accounts.name
   FROM account.sub_accounts
  WHERE ((sub_accounts.description)::integer > 0)
  ORDER BY sub_accounts.code;


ALTER TABLE account.sub_accounts_view OWNER TO postgres;

--
-- Name: users_journal_entries; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.users_journal_entries AS
 SELECT h.id,
    h.number,
    h.date,
    h.summary,
    h.period_id,
    account.entries_transactions(h.id) AS entries
   FROM account.headings h
  WHERE (h.status)::boolean;


ALTER TABLE account.users_journal_entries OWNER TO postgres;

--
-- Name: usr_associated_accounts; Type: TABLE; Schema: account; Owner: postgres
--

CREATE TABLE account.usr_associated_accounts (
    id integer NOT NULL,
    account_id integer NOT NULL
);


ALTER TABLE account.usr_associated_accounts OWNER TO postgres;

--
-- Name: usr_associated_accounts_id_seq; Type: SEQUENCE; Schema: account; Owner: postgres
--

CREATE SEQUENCE account.usr_associated_accounts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE account.usr_associated_accounts_id_seq OWNER TO postgres;

--
-- Name: usr_associated_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: account; Owner: postgres
--

ALTER SEQUENCE account.usr_associated_accounts_id_seq OWNED BY account.usr_associated_accounts.id;


--
-- Name: usr_associated_accounts_view; Type: VIEW; Schema: account; Owner: postgres
--

CREATE VIEW account.usr_associated_accounts_view AS
 SELECT ua.account_id,
    s.name AS account_name
   FROM (account.usr_associated_accounts ua
     LEFT JOIN account.sub_accounts s ON ((s.id = ua.account_id)));


ALTER TABLE account.usr_associated_accounts_view OWNER TO postgres;

--
-- 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,
    sarg text,
    terms text
);


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.sarg,
    h.terms,
    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: invoices_view; Type: VIEW; Schema: invoice; Owner: postgres
--

CREATE VIEW invoice.invoices_view AS
SELECT
    NULL::integer AS id,
    NULL::integer AS invoice_id,
    NULL::integer AS type,
    NULL::text AS sarg,
    NULL::text AS type_name,
    NULL::integer AS status,
    NULL::boolean AS active,
    NULL::date AS date,
    NULL::text AS number,
    NULL::integer AS heading_id,
    NULL::integer AS user_id,
    NULL::text AS user_name,
    NULL::text AS dui,
    NULL::text AS nit,
    NULL::text AS municipio,
    NULL::text AS departamento,
    NULL::text AS giro,
    NULL::text AS registro,
    NULL::text AS direccion,
    NULL::numeric AS subtotal,
    NULL::numeric AS iva,
    NULL::numeric AS ivaret,
    NULL::numeric AS tax_1,
    NULL::numeric AS tax_2,
    NULL::numeric AS exents,
    NULL::numeric AS taxed,
    NULL::numeric AS total,
    NULL::numeric AS discount,
    NULL::text AS total_text,
    NULL::integer AS owner_id,
    NULL::integer AS cost_center_id,
    NULL::text AS detail,
    NULL::text AS detail_summ;


ALTER TABLE invoice.invoices_view 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: 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: headers; Type: TABLE; Schema: payrolls; Owner: postgres
--

CREATE TABLE payrolls.headers (
    id integer NOT NULL,
    active boolean DEFAULT true,
    date date DEFAULT ('now'::text)::date,
    entry timestamp without time zone,
    number text,
    heading_id integer,
    invoice_id integer,
    total numeric,
    owner_id integer,
    terms text
);


ALTER TABLE payrolls.headers OWNER TO postgres;

--
-- Name: headers_id_seq; Type: SEQUENCE; Schema: payrolls; Owner: postgres
--

CREATE SEQUENCE payrolls.headers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE payrolls.headers_id_seq OWNER TO postgres;

--
-- Name: headers_id_seq; Type: SEQUENCE OWNED BY; Schema: payrolls; Owner: postgres
--

ALTER SEQUENCE payrolls.headers_id_seq OWNED BY payrolls.headers.id;


--
-- Name: payroll_debits; Type: VIEW; Schema: payrolls; Owner: postgres
--

CREATE VIEW payrolls.payroll_debits AS
 SELECT h.id,
    h.active,
    h.date,
    h.entry,
    h.number,
    h.heading_id,
    h.invoice_id,
    h.total,
    h.owner_id,
    h.terms,
    account.entries_transactions_tonly(h.heading_id) AS detail
   FROM payrolls.headers h;


ALTER TABLE payrolls.payroll_debits 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,
    ( SELECT public.balance(1, (u.id)::numeric) AS balance) AS balance
   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.full_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.type,
    u.register_no,
    u.email,
    u.phone,
    u.address,
    u.balance
   FROM public.usr_business_plus 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.county,
    u.country,
    u.state,
    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_employee_plus; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW public.usr_employee_plus AS
 SELECT u.id,
    u.code,
    u.firstname,
    u.lastname,
    public.full_name(u.firstname, u.lastname) AS full_name,
    u.county,
    u.country,
    u.state,
    u.dui,
    u.nit,
    u."position",
    u.salary,
    u.emails,
    u.phones,
    u.addresses,
    ( SELECT public.balance(1, (u.id)::numeric) AS balance) AS balance
   FROM public.usr_employee u;


ALTER TABLE public.usr_employee_plus 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.full_name,
    u.dui,
    u.nit,
    u.county,
    u.state,
    u.country,
    u.type,
    u.register_no,
    u.email,
    u.phone,
    u.address,
    u.balance
   FROM public.usr_business_plus 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: 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: 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: usr_associated_accounts id; Type: DEFAULT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_associated_accounts ALTER COLUMN id SET DEFAULT nextval('account.usr_associated_accounts_id_seq'::regclass);


--
-- Name: usr_transactions id; Type: DEFAULT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_transactions ALTER COLUMN id SET DEFAULT nextval('account.receivable_payable_transactions_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: headers id; Type: DEFAULT; Schema: payrolls; Owner: postgres
--

ALTER TABLE ONLY payrolls.headers ALTER COLUMN id SET DEFAULT nextval('payrolls.headers_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;
2	2	Yelp	\N	2022-01-01	\N	1	1
1	1	General business	\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;
178	1	1	1	4845.48	t	2	\N
1	1	2	9	220	t	2	\N
273	1	3	9	150	t	2	\N
274	1	4	11	800	t	2	\N
275	1	5	153	99.47	t	2	\N
276	1	6	129	5230	t	2	\N
277	1	7	207	798.05	t	2	\N
126	1	8	81	640	f	2	\N
278	1	9	81	140	f	2	\N
279	1	10	81	300	f	2	\N
280	1	11	219	10253	f	2	\N
281	1	12	118	810	f	2	\N
490	195	1	129	180.00	t	2	1
491	195	2	81	180.00	f	2	1
492	196	1	129	40.00	t	2	1
493	196	2	81	40.00	f	2	1
494	197	1	9	100	t	2	\N
495	197	2	219	100	f	2	\N
496	198	1	81	100	t	2	\N
497	198	2	1	100	f	2	\N
498	199	1	1	150	t	2	\N
499	199	2	81	150	f	2	\N
500	200	1	9	180.00	t	2	1
501	200	2	219	180.00	f	2	1
502	201	1	1	200.00	t	2	1
503	201	2	219	200.00	f	2	1
504	202	1	1	100	t	2	\N
505	202	2	9	100	f	2	\N
\.


--
-- 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;
1	00000001	2022-09-01	Apertura del ejercicio	2	1	2021-06-08 17:04:56.835841
195	00000122	2022-09-23	Salary expenses (# NN)	2	1	2022-09-23 17:22:58.267321
196	00000123	2022-09-23	Salary expenses (# NN)	2	1	2022-09-23 17:23:21.415163
197	00000124	2022-09-27	venta del dia	2	1	2022-09-27 17:26:21.502742
198	00000125	2022-09-28	Partial payment mary	2	1	2022-09-28 12:25:03.839504
199	00000126	2022-09-28	creditting mauricio	2	1	2022-09-28 12:42:41.694264
200	00000127	2022-09-28	Revenue for services, invoice # 0005	2	1	2022-09-28 12:58:14.034939
201	00000128	2022-09-28	Revenue for services, invoice # 0006	2	1	2022-09-28 12:58:32.914782
202	00000129	2022-09-29	abono culver	2	1	2022-09-28 12:59:18.732899
\.


--
-- 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: sequences; Type: TABLE DATA; Schema: account; Owner: postgres
--

COPY account.sequences (id, name, value) FROM stdin;
1	PARTIDAS	129
\.


--
-- Data for Name: sub_accounts; Type: TABLE DATA; Schema: account; Owner: postgres
--

COPY account.sub_accounts (id, account_id, code, name, description) FROM stdin;
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
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
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
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
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
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
119	39	310201	Ganancia del ejercicio\r	\N
120	39	310202	Ganancia de ejercicios anteriores\r	\N
121	39	310203	Ganancias decretadas\r	\N
122	40	310301	Pérdidas del ejercicio\r	\N
123	40	310302	Pérdidas de ejercicios anteriores\r	\N
2	1	110102	Petty cash	1
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
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
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
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
3	1	110103	Efectivo por depositar \r	\N
118	38	310102	Capital\n	1
207	46	420240	Advertising	\N
1	1	110101	Cash	1
9	3	110301	Clients	1
11	3	110303	Salary advances	1
22	7	110701	Materia Prima\r	\N
43	12	120104	Equipment & tools	1
75	23	210301	Suppliers	1
81	25	210501	Payable salaries	1
129	45	420101	Salaries & wages	1
153	45	420125	Cleaning supplies	1
219	52	510201	Revenue	1
\.


--
-- Data for Name: usr_associated_accounts; Type: TABLE DATA; Schema: account; Owner: postgres
--

COPY account.usr_associated_accounts (id, account_id) FROM stdin;
1	9
2	11
3	12
4	13
5	16
6	68
7	75
8	76
9	77
10	81
11	78
12	79
\.


--
-- Data for Name: usr_transactions; Type: TABLE DATA; Schema: account; Owner: postgres
--

COPY account.usr_transactions (id, date, entry, isdebit, user_id, entry_id, total) FROM stdin;
159	2022-09-01	\N	1	60	1	220
160	2022-09-01	\N	1	59	273	150
161	2022-09-01	\N	1	1	274	800
165	2022-09-01	\N	0	55	126	640
166	2022-09-01	\N	0	57	278	140
167	2022-09-01	\N	0	58	279	300
281	2022-09-23	\N	0	56	491	180.00
283	2022-09-23	\N	0	56	493	40.00
284	2022-09-27	\N	1	1	494	100
286	2022-09-28	\N	1	56	496	100
289	2022-09-28	\N	0	1	499	150
290	2022-09-28	\N	1	60	500	180.00
295	2022-09-29	\N	0	60	505	100
\.


--
-- 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;
21	1	2022-08-30 11:30:45.102261	2022-08-19 20:01:45.102261	1	1	15
22	1	2022-08-30 14:00:46.662542	2022-08-19 20:01:46.662542	0	1	15
\.


--
-- 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;
713	270	1	521	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.	180.00	1.00	180	f	0.00
714	271	1	\N	pago	40.00	1.00	40	f	0.00
715	272	1	521	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.	180.00	1.00	180	f	0.00
716	273	1	521	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.	200.00	1.00	200	f	0.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, sarg, terms) FROM stdin;
270	4	1	t	2022-09-23	\N	195	56	María Marín								180.00	20.71	\N	\N	\N	0	159.29	180.00	0.00		1	payroll	credit
271	4	1	t	2022-09-23	\N	196	56	María Marín								40.00	4.60	\N	\N	\N	0	35.40	40.00	0.00		1	payroll	credit
272	8	1	t	2022-09-28	0005	200	60	Culver City 								180.00	20.71	\N	\N	\N	0	159.29	180.00	0.00		1	receipt	credit
273	8	1	t	2022-09-28	0006	201	60	Culver City 								200.00	23.01	\N	\N	\N	0	176.99	200.00	0.00		1	receipt	cash
\.


--
-- Data for Name: types; Type: TABLE DATA; Schema: invoice; Owner: postgres
--

COPY invoice.types (id, name, code, short_code) FROM stdin;
3	Factura de sujeto excento de compras	Compras FX	CFX
7	Comprobante de Exportación de ventas	Ventas XP	VXP
9	Ticket de ventas	Ticket	VT
1	Purchase Invoice Plus	Purchase Invoice Plus	CCCF
2	Purchase Invoice	Purchase Invoice	CCF
5	InvoicePlus	Sales Invoice Plus	VCCF
6	Invoice	Sales Invoice	VCF
4	Purchase receipt	Purchase receipt	CFC
8	Receipt	Receipt	VFC
\.


--
-- Data for Name: headers; Type: TABLE DATA; Schema: payrolls; Owner: postgres
--

COPY payrolls.headers (id, active, date, entry, number, heading_id, invoice_id, total, owner_id, terms) FROM stdin;
1	t	2022-09-29	\N	00001	195	\N	10000	1	credit
\.


--
-- Data for Name: sequences; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.sequences (id, code, value) FROM stdin;
5	VXP	0
4	VCCF	113
1	VT	75
3	VCF	76
2	VFC	6
\.


--
-- 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
1	1	mgrande	Manuel Mauricio	Grande Pineda	01435575-0	0813-020766-001-9	\N	San Salvador	San Salvador	El Salvador
2	1	gloria	Gloria Janett	Portillo	\N	\N	Gloria Portillo	Los Angeles	Los Angeles	USA
54	1	CNHF	Central Neighborhood Health Foundation	\N	\N	\N	\N	\N	\N	\N
55	1	clara	Clara	Ramos	\N	\N	\N	\N	\N	\N
56	1	mary	María	Marín	\N	\N	\N	\N	\N	\N
57	1	vero	Verónica	Hernández	\N	\N	\N	\N	\N	\N
58	1	rosita	Rosa	\N	\N	\N	\N	\N	\N	\N
59	1	elisa	Elisa	\N	\N	\N	\N	\N	\N	\N
60	1	culvercity	Culver City	\N	\N	\N	\N	\N	\N	\N
61	1	homedepot	The Home Depot	\N	\N	\N	\N	\N	\N	\N
62	1	costco	Costco Wholesale	\N	\N	\N	\N	\N	\N	\N
63	1	holly	Holly, Artesia	\N	\N	\N	\N	\N	\N	\N
64	1	bath&beyond	Bath & Beyond	\N	\N	\N	\N	\N	\N	\N
65	1	crucita	Cruz	\N	\N	\N	\N	\N	\N	\N
\.


--
-- 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;
54	1	\N	\N	\N	t
1	0	\N	\N	t	t
59	1	\N	\N	\N	t
60	1	\N	\N	\N	t
61	1	\N	\N	t	\N
62	1	\N	\N	t	\N
63	1	\N	\N	\N	t
64	1	\N	\N	t	\N
65	1	\N	\N	f	f
\.


--
-- 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;
\.


--
-- 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	15	Systems manager
55	1	20	Lady
56	1	17	\N
57	1	16	\N
58	1	20	\N
\.


--
-- Data for Name: usr_x_labs; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.usr_x_labs (id, up) FROM stdin;
65	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;
1	1	mgrande	Colateral13	2
2	1	gloria	1169	2
3	1	sysadmin	namiac	2
\.


--
-- Data for Name: categories; Type: TABLE DATA; Schema: stock; Owner: postgres
--

COPY stock.categories (id, name, up) FROM stdin;
1	Services	\N
2	Inventory	\N
\.


--
-- 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;
1	1	533
2	1	531
\.


--
-- 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;
519	531	\N	1	1	\N
520	533	\N	1	1	\N
521	535	\N	1	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;
646	531	presentation 1	1
647	533	journey	1
649	535	Journey	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;
531	Interior Glass Cleaning: all interior windows, glass walls/hallway cleaned	\N	\N	1	\N	1	\N	\N	\N	12500	\N
533	One month of daily cleaning service, including trash disposals, dusting, mopping, vacuuming, fridge, disinfection, etc.	\N	\N	1	\N	2	\N	\N	\N	12500	\N
535	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.	\N	\N	1	\N	3	\N	\N	\N	180	\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;
623	2022-01-01	2022-09-04 18:51:13.301686	1	\N	519	1	\N	1	Inventario inicial
688	2022-09-23	2022-09-23 17:22:58.275352	1	713	521	1.00	\N	0	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.
671	2022-01-01	2022-09-23 11:19:44.953499	1	\N	520	1	\N	1	Inventario inicial
689	2022-09-28	2022-09-28 12:58:14.071252	1	715	521	1.00	\N	0	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.
690	2022-09-28	2022-09-28 12:58:32.937234	1	716	521	1.00	\N	0	Cleaning service, mopping, vacuuming, kitchen and appliances, dust, bathrooms, disinfection, etc.
\.


--
-- 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', 505, true);


--
-- Name: headings_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--

SELECT pg_catalog.setval('account.headings_id_seq', 202, 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', 295, true);


--
-- 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: usr_associated_accounts_id_seq; Type: SEQUENCE SET; Schema: account; Owner: postgres
--

SELECT pg_catalog.setval('account.usr_associated_accounts_id_seq', 12, true);


--
-- Name: attendance_id_seq; Type: SEQUENCE SET; Schema: att; Owner: postgres
--

SELECT pg_catalog.setval('att.attendance_id_seq', 22, 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', 716, true);


--
-- Name: headers_id_seq; Type: SEQUENCE SET; Schema: invoice; Owner: postgres
--

SELECT pg_catalog.setval('invoice.headers_id_seq', 273, true);


--
-- Name: types_id_seq; Type: SEQUENCE SET; Schema: invoice; Owner: postgres
--

SELECT pg_catalog.setval('invoice.types_id_seq', 9, true);


--
-- Name: headers_id_seq; Type: SEQUENCE SET; Schema: payrolls; Owner: postgres
--

SELECT pg_catalog.setval('payrolls.headers_id_seq', 1, 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', 65, 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', 157, 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', 2, true);


--
-- Name: category_title_link_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--

SELECT pg_catalog.setval('stock.category_title_link_id_seq', 2, true);


--
-- Name: items_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--

SELECT pg_catalog.setval('stock.items_id_seq', 521, true);


--
-- Name: presentation_title_link_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--

SELECT pg_catalog.setval('stock.presentation_title_link_id_seq', 649, true);


--
-- Name: titles_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--

SELECT pg_catalog.setval('stock.titles_id_seq', 535, true);


--
-- Name: transactions_id_seq; Type: SEQUENCE SET; Schema: stock; Owner: postgres
--

SELECT pg_catalog.setval('stock.transactions_id_seq', 690, 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: usr_associated_accounts fr34; Type: CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_associated_accounts
    ADD CONSTRAINT fr34 UNIQUE (account_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: usr_transactions receivable_payable_transactions_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_transactions
    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: usr_associated_accounts usr_associated_accounts_pkey; Type: CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_associated_accounts
    ADD CONSTRAINT usr_associated_accounts_pkey PRIMARY KEY (id);


--
-- 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: headers dcabeceras_pkey; Type: CONSTRAINT; Schema: payrolls; Owner: postgres
--

ALTER TABLE ONLY payrolls.headers
    ADD CONSTRAINT dcabeceras_pkey 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_view _RETURN; Type: RULE; Schema: invoice; Owner: postgres
--

CREATE OR REPLACE VIEW invoice.invoices_view AS
 SELECT h.id,
    h.id AS invoice_id,
    h.type,
    h.sarg,
    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,
    e.cost_center_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)))
     LEFT JOIN account.headings a ON ((a.id = h.heading_id)))
     LEFT JOIN account.entries e ON ((e.heading_id = a.id)))
  WHERE (h.status)::boolean
  GROUP BY h.id, t.code, e.cost_center_id;


--
-- Name: entries_transactions insert_update_delete_entries_transactions_trg; Type: TRIGGER; Schema: account; Owner: postgres
--

CREATE TRIGGER insert_update_delete_entries_transactions_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON account.entries_transactions FOR EACH ROW EXECUTE FUNCTION account.insert_update_delete_entries_transactions();


--
-- Name: journal_entries_plus insert_update_delete_journal_entries_plus_trg; Type: TRIGGER; Schema: account; Owner: postgres
--

CREATE TRIGGER insert_update_delete_journal_entries_plus_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON account.journal_entries_plus FOR EACH ROW EXECUTE FUNCTION account.insert_update_delete_journal_entries();


--
-- Name: journal_entries insert_update_delete_journal_entries_trg; Type: TRIGGER; Schema: account; Owner: postgres
--

CREATE TRIGGER insert_update_delete_journal_entries_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON account.journal_entries FOR EACH ROW EXECUTE FUNCTION account.insert_update_delete_journal_entries();


--
-- Name: users_journal_entries insert_update_delete_users_journal_entries_trg; Type: TRIGGER; Schema: account; Owner: postgres
--

CREATE TRIGGER insert_update_delete_users_journal_entries_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON account.users_journal_entries FOR EACH ROW EXECUTE FUNCTION account.insert_update_delete_users_journal_entries();


--
-- 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: usr_transactions sdfsdt; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_transactions
    ADD CONSTRAINT sdfsdt FOREIGN KEY (user_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;


--
-- Name: usr_associated_accounts sesdt; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_associated_accounts
    ADD CONSTRAINT sesdt FOREIGN KEY (account_id) REFERENCES account.sub_accounts(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: usr_transactions uyu; Type: FK CONSTRAINT; Schema: account; Owner: postgres
--

ALTER TABLE ONLY account.usr_transactions
    ADD CONSTRAINT uyu FOREIGN KEY (entry_id) REFERENCES account.entries(id) ON UPDATE CASCADE ON DELETE CASCADE 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: headers fkxoid; Type: FK CONSTRAINT; Schema: payrolls; Owner: postgres
--

ALTER TABLE ONLY payrolls.headers
    ADD CONSTRAINT fkxoid FOREIGN KEY (owner_id) REFERENCES public.usr_persons(id) ON UPDATE CASCADE ON DELETE RESTRICT;


--
-- Name: headers hders_headxing_id_fkey; Type: FK CONSTRAINT; Schema: payrolls; Owner: postgres
--

ALTER TABLE ONLY payrolls.headers
    ADD CONSTRAINT hders_headxing_id_fkey FOREIGN KEY (heading_id) REFERENCES account.headings(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: headers headeong_id_fkey; Type: FK CONSTRAINT; Schema: payrolls; Owner: postgres
--

ALTER TABLE ONLY payrolls.headers
    ADD CONSTRAINT headeong_id_fkey FOREIGN KEY (invoice_id) REFERENCES invoice.headers(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- 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;


--
--

