-- EMS PostgreSQL Manager 1.8.0.1
-- ---------------------------------------
-- Host     : localhost
-- Database : hotel



--
-- Definition for function plpgsql_call_handler (OID = 16976) : 
--
SET search_path = public, pg_catalog;
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;
--
-- Definition for language plpgsql (OID = 16977) : 
--
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
--
-- Structure for table department (OID = 17857) : 
--
CREATE TABLE department (
    id serial NOT NULL,
    name character varying(50)
);
--
-- Structure for table payment_method (OID = 17902) : 
--
CREATE TABLE payment_method (
    id serial NOT NULL,
    description character varying(30) NOT NULL
);
--
-- Structure for table items (OID = 18094) : 
--
CREATE TABLE items (
    id bigserial NOT NULL,
    description character varying(50),
    price numeric(10,2) DEFAULT 0,
    discount real DEFAULT 0,
    stock smallint DEFAULT 0
);
--
-- Structure for table guest (OID = 18285) : 
--
CREATE TABLE guest (
    id serial NOT NULL,
    ref_num character varying(50),
    name character varying(50),
    address text,
    phone character varying(50)
);
--
-- Structure for table billing (OID = 18298) : 
--
CREATE TABLE billing (
    id serial NOT NULL,
    guest_id integer,
    trx_date date DEFAULT now(),
    trx_time time(0) without time zone DEFAULT ('now'::text)::time(6) with time zone,
    payment_method integer,
    tax real,
    amount_paid numeric(10,2),
    dep_id integer,
    creator character varying(50) DEFAULT "current_user"()
);
--
-- Structure for table billing_items (OID = 18325) : 
--
CREATE TABLE billing_items (
    id serial NOT NULL,
    billing_id integer NOT NULL,
    items_id bigint,
    quantity numeric(10,2) DEFAULT 0 NOT NULL,
    discount real DEFAULT 0 NOT NULL
);
--
-- Data for table public.department (OID = 17857) (LIMIT 0,1)
--
INSERT INTO department (id, name) VALUES (1, 'Resto');
COMMIT;
--
-- Data for table public.payment_method (OID = 17902) (LIMIT 0,9)
--
INSERT INTO payment_method (id, description) VALUES (1, 'Cash');
INSERT INTO payment_method (id, description) VALUES (2, 'Credit');
INSERT INTO payment_method (id, description) VALUES (3, 'Visa ');
INSERT INTO payment_method (id, description) VALUES (4, 'Master Card');
INSERT INTO payment_method (id, description) VALUES (5, 'AmEx');
INSERT INTO payment_method (id, description) VALUES (6, 'JBC');
INSERT INTO payment_method (id, description) VALUES (7, 'Discover');
INSERT INTO payment_method (id, description) VALUES (8, 'Transfer to Guest Ledger');
INSERT INTO payment_method (id, description) VALUES (9, 'Transfer From Adv Deposit');
COMMIT;
--
-- Data for table public.items (OID = 18094) (LIMIT 0,4)
--
INSERT INTO items (id, description, price, discount, stock) VALUES (4, 'Aqua', 2500.00, 0, 200);
INSERT INTO items (id, description, price, discount, stock) VALUES (1, 'Coca Cola (Kaleng)', 3000.00, 0, 120);
INSERT INTO items (id, description, price, discount, stock) VALUES (2, 'Sprite (Kaleng)', 3000.00, 0, 120);
INSERT INTO items (id, description, price, discount, stock) VALUES (3, 'Fanta (Kaleng)', 3000.00, 0, 120);
COMMIT;
--
-- Data for table public.guest (OID = 18285) (LIMIT 0,4)
--
INSERT INTO guest (id, ref_num, name, address, phone) VALUES (1, '21.5005.310879.001', 'Muhyiddin1', 'Jl Singa No 65
Makassar', '628152529671');
INSERT INTO guest (id, ref_num, name, address, phone) VALUES (3, '21.5005', 'Rien', NULL, '08152529651');
INSERT INTO guest (id, ref_num, name, address, phone) VALUES (5, '22', 'Test', NULL, '55');
INSERT INTO guest (id, ref_num, name, address, phone) VALUES (4, '0081111', 'Doel', 'Jl Singa', '080989999');
COMMIT;
--
-- Data for table public.billing (OID = 18298) (LIMIT 0,2)
--
INSERT INTO billing (id, guest_id, trx_date, trx_time, payment_method, tax, amount_paid, dep_id, creator) VALUES (1, 1, '2003-09-28', '16:08:52', 1, 10, 20000.00, 1, 'middink');
INSERT INTO billing (id, guest_id, trx_date, trx_time, payment_method, tax, amount_paid, dep_id, creator) VALUES (2, 1, '2003-09-29', '07:50:17', 3, 10, 10000.00, 1, 'middink');
COMMIT;
--
-- Data for table public.billing_items (OID = 18325) (LIMIT 0,4)
--
INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VALUES (2, 1, 2, 10.00, 0);
INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VALUES (1, 1, 1, 2.00, 0);
INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VALUES (4, 2, 2, 2.00, 0);
INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VALUES (5, 2, 3, 2.00, 0);
COMMIT;
-- Definition for index dep_id_pkey (OID = 17862) : 
--
ALTER TABLE ONLY department
    ADD CONSTRAINT dep_id_pkey PRIMARY KEY (id);
--
-- Definition for index payment_method_pkey (OID = 17905) : 
--
ALTER TABLE ONLY payment_method
    ADD CONSTRAINT payment_method_pkey PRIMARY KEY (id);
--
-- Definition for index payment_method_description_key (OID = 17907) : 
--
ALTER TABLE ONLY payment_method
    ADD CONSTRAINT payment_method_description_key UNIQUE (description);
--
-- Definition for index items_pkey (OID = 18100) : 
--
ALTER TABLE ONLY items
    ADD CONSTRAINT items_pkey PRIMARY KEY (id);
--
-- Definition for index guest_pkey (OID = 18291) : 
--
ALTER TABLE ONLY guest
    ADD CONSTRAINT guest_pkey PRIMARY KEY (id);
--
-- Definition for index billing_pkey (OID = 18304) : 
--
ALTER TABLE ONLY billing
    ADD CONSTRAINT billing_pkey PRIMARY KEY (id);
--
-- Definition for index billing_items_pkey (OID = 18330) : 
--
ALTER TABLE ONLY billing_items
    ADD CONSTRAINT billing_items_pkey PRIMARY KEY (id);


