Database PostgreSQL

--database eproduct

create table users (
    id serial primary key,
    username varchar(100),
    password text,
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
)

create table customer (
    id serial primary key,
    first_name varchar(100),
    last_name varchar(100),
    user_id int references users(id),
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
)

create table orders (
    id serial primary key,
    user_id int references users(id),
    total_product int,
    total_price numeric,
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
)

create table product_category (
    id serial primary key,
    name varchar(100),
    description varchar(200),
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
)

create table products (
    id serial primary key,
    nama varchar(100),
    description varchar(200),
    category_id int references product_category(id),
    price numeric,
    image varchar(200),
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
)

create table order_detail (
    id serial primary key,
    order_id int references orders(id),
    product_id int references products(id),
    quantity int,
    createdAt timestamp not null default current_timestamp,
    updatedAt timestamp not null default current_timestamp
);


--database rentaldvd

--1
create table category (
    category_id serial primary key,
    name varchar(100),
    last_update timestamp not null default current_timestamp
)

--2
create table actor (
    actor_id serial primary key,
    first_name varchar(100),
    last_name varchar(100),
    last_update timestamp not null default current_timestamp
)

--3
create table country (
    country_id serial primary key,
    country varchar(100),
    last_update timestamp not null default current_timestamp
)

--4
create table language (
    language_id serial primary key,
    name varchar(100),
    last_update timestamp not null default current_timestamp
)

--5
create table film (
    film_id serial primary key,
    title varchar(255),
    description text,
    release_year int,
    language_id int references language,
    rental_duration int,
    rental_rate int,
    length int,
    replacement_cost numeric,
    rating numeric,
    last_update timestamp not null default current_timestamp,
    special_features varchar(100),
    fulltext text
)

--6
create table film_actor (
    actor_id int references actor,
    film_id int references film,
    last_update timestamp not null default current_timestamp,
    primary key(actor_id, film_id)
)

--7
create table film_category (
    film_id int references film,
    category_id int references category,
    last_update timestamp not null default current_timestamp,
    primary key(film_id, category_id)
)

--8
create table city (
    city_id serial primary key,
    city varchar(100),
    country_id int references country,
    last_update timestamp not null default current_timestamp
)

--9
create table address (
    address_id serial primary key,
    address varchar(255),
    address2 varchar(255),
    district varchar(255),
    city_id int references city,
    postal_code varchar(50),
    phone varchar(50),
    last_update timestamp not null default current_timestamp
)

--10
create table customer (
    customer_id serial primary key,
    store_id int,
    first_name varchar(100),
    last_name varchar(100),
    email varchar(100),
    address_id int references address,
    activebool boolean,
    create_date timestamp not null default current_timestamp,
    last_update timestamp not null default current_timestamp,
    active boolean
)

--11
create table inventory (
    inventory_id serial primary key,
    film_id int references film,
    store_id int,
    last_update timestamp not null default current_timestamp
)

--12
create table staff (
    staff_id serial primary key,
    first_name varchar(100),
    last_name varchar(100),
    address_id int references address,
    email varchar(100),
    store_id int,
    active boolean,
    username varchar(100),
    password text,
    last_update timestamp not null default current_timestamp,
    picture text
)

--13
create table store (
    store_id serial primary key,
    manager_staff_id int references staff(staff_id),
    address_id int references address,
    last_update timestamp not null default current_timestamp
)

--alter table store drop constraint store_address_id_fkey
--alter table store add constraint store_address_id_fkey
--foreign key (address_id) references address(address_id)

--14
create table rental (
    rental_id serial primary key,
    rental_date date,
    inventory_id int references inventory,
    customer_id int references customer,
    return_date date,
    staff_id int references staff,
    last_update timestamp not null default current_timestamp
)

--15
create table payment (
    payment_id serial primary key,
    customer_id int references customer,
    staff_id int references staff,
    rental_id int references rental,
    amount numeric,
    payment_date date
)


--database eproduct

insert into users (username, password) values
('user1', 'user1'),
('user2', 'user2'),
('user3', 'user3'),
('user4', 'user4'),
('user5', 'user5');

select * from users

insert into users (username, password)
values ('user6', 'user6')
returning *

insert into customer (first_name, last_name, user_id) values
('helis', 'cokro', 1),
('alvin', 'faiz', 2)
returning *

select customer.first_name, customer.last_name, users.username
from customer join users
on users.id = customer.user_id

insert into product_category (name, description) values
('elektronik', 'barang murah'),
('makanan', 'makanan kering')

insert into product_category (name, description) values
('furniture', 'perlengkapan furniture')
returning id

select * from product_category

insert into products (nama, description, category_id, price, image) values
('televisi', 'televisi samsung', 1, 2000, 'tv-img.jpg'),
('kulkas', 'kulkas 2 pintu', 1, 6000, 'kulkas-img.jpg'),
('meja', 'meja persegi panjang', 3, 4000, 'meja-img.jpg')
returning *

select * from products

select name, nama, price
from product_category
join products
on product_category.id = products.category_id

select * from orders

insert into orders (user_id, total_product, total_price) values
(1, 4, 16000)
delete from orders where id = 3

select * from order_detail

insert into order_detail (order_id, product_id, quantity) values
(4, 1, 2),
(4, 2, 2)

select customer.user_id, username, first_name, last_name, product_category.name, products.nama, quantity, price
from users
join customer on customer.user_id = users.id
join orders on orders.user_id = users.id
join order_detail on order_detail.order_id = orders.id
join products on products.id = order_detail.product_id
join product_category on product_category.id = products.category_id
where customer.user_id = 1


--database eproduct

--1. Menampilkan informasi customer dan accountnya (username, password) = fullname, username, password
select first_name || ' ' || last_name as fullname, username, password
from users
join customer
on customer.id = users.id

--2. Menampilkan informasi customer dan detail ordernya
select first_name, last_name, p.nama, pc.name, quantity as jumlah, price from order_detail od
join products p on p.id = od.product_id
join product_category pc on pc.id = p.category_id
join orders o on o.id = od.order_id
join users u on u.id = o.user_id
join customer c on c.user_id = u.id

create view customer_orders as
select first_name, last_name, p.nama nama_produk, pc.name nama_kategori, quantity as jumlah, price from order_detail od
join products p on p.id = od.product_id
join product_category pc on pc.id = p.category_id
join orders o on o.id = od.order_id
join users u on u.id = o.user_id
join customer c on c.user_id = u.id

select * from customer_orders
drop view customer_orders

--3. Menampilkan informasi produk per category = nama category, nama produk
select p.nama, pc.name
from products p
join product_category pc
on pc.id = p.category_id

create view informasi_produk as
select p.nama, pc.name
from products p
join product_category pc
on pc.id = p.category_id

select * from informasi_produk
drop view informasi_produk


--database adventureworks

--1. Menampilkan harga dan discount setiap item product
select name, listprice, discountpct
from production.product
join sales.specialofferproduct
on sales.specialofferproduct.productid = production.product.productid
join sales.specialoffer
on sales.specialoffer.specialofferid = sales.specialofferproduct.specialofferid

--2. Menampilkan warna dan quantity setiap item product
select color, count(name) as quantity from production.product
where color is not null
group by color

--3. Menampilkan harga dan discount setiap item product yang harganya di atas 120
select name, listprice, discountpct
from production.product
join sales.specialofferproduct
on sales.specialofferproduct.productid = production.product.productid
join sales.specialoffer
on sales.specialoffer.specialofferid = sales.specialofferproduct.specialofferid
where listprice > 120
order by listprice asc


--function

--returns varchar
create or replace function getUsername(userId int)
returns varchar
language plpgsql
as
$$
declare
    infouser varchar;
begin
    select username into infouser
    from users
    where id = userId;

    return infouser;
end;
$$;

drop function getUsername(int)
drop function getUsername()
select getUsername(4)

--
create or replace function getUsername(userid int)
returns users
language plpgsql
as
$$
declare
    infouser users
begin
    select * into infouser
    from users
    join customer
    on customer.user_id = users.id
    where users.id = userid;

    if not found then
        raise 'Data % tidak ditemukan', userid;
        end if;

    return infouser;
end;
$$;

select getUsername(8)

select *
    from users
    join customer
    on customer.user_id = users.id
    where users.id = customer.id;

--
create or replace function getUsername()
returns table(
    user_name varchar,
    firstname varchar,
    lastname varchar
)
language plpgsql
as
$$
declare

begin
return query
    select username, first_name, last_name
    from users join customer
    on customer.user_id = users.id;

    if not found then
        raise 'Data % tidak ditemukan', userid;
        end if;

end;
$$;

select * from getUsername()
drop function getUsername()

--
create or replace function getUsername()
returns table(
    user_name users.username%type,
    firstname customer.first_name%type,
    lastname customer.last_name%type
)
language plpgsql
as
$$
declare
    user_r record;
begin
    for user_r in(
    select username, first_name, last_name
    from users join customer
    on customer.user_id = users.id
    ) loop
    user_name := user_r.username;
    firstname := upper(user_r.first_name);
    lastname := upper(user_r.last_name);
    return next;
    end loop;

end;
$$;

select * from getUsername()


--tanpa return
create or replace function getUsername(
    in username int,
    out firstname varchar,
    out lastname varchar
)

language plpgsql
as
$$

begin
    select firstname, lastname
    into firstname, lastname
    from customer
    where user_id = username;
end;
$$;

drop function getUsername(int)
select getUsername(1)


--
create or replace function getMinMax(
    out min_len int,
    out max_len int
    )
language plpgsql
as
$$

begin
    select
        min(id),
        max(id)
    into
        min_len, max_len
    from
        customer;
end;
$$;

select * from getMinMax()

--
create or replace function getUsernameCursor()
returns table(
    user_name varchar,
    firstname varchar,
    lastname varchar
)
language plpgsql
as
$$
declare
    cust_cursor cursor for
        select username, first_name, last_name
        from users join customer
        on customer.user_id = users.id;
begin
    open cust_cursor;
    return query
    fetch all cust_cursor;
    close cust_cursor;

end;
$$;

select * from getUsernameCursor()


--database adventureworks

--1. Paging
--with cursor
create or replace function person.getPersonPaginationCursor(lim int, page int)
returns setof person.person
language plpgsql
as
$$
declare
    offsetCustom int := lim * (page-1);
    cursorPerson cursor(lim int, page int)
    for select * from person.person
    limit lim offset offsetCustom;
    recordPerson person.person;
begin
    open cursorPerson(lim int, page int);
        loop
            fetch next from cursorPerson into recordPerson;
            exit when not found;
            return next recordPerson;
        end loop;
    close cursorPerson;
end;
$$;

select * from person.getPersonPaginationCursor(10, 3);

--without cursor
create or replace function person.GetPersonPagination (lim int, page int)
returns setof person.person
language plpgsql
as
$$
declare
    offsetCustom int := lim * (page-1);
begin
return query
    select * from person.person
    limit lim offset offsetCustom;
end;
$$;

select * from person.getPersonPagination(5, 15)


--2. Search firstname, middlename or lastname
create or replace function getName(nama varchar)
returns setof person.person
language plpgsql
as
$$
begin
    return query
    select * from person.person
    where firstname ilike concat('%', nama, '%')
    or middlename ilike concat('%', nama, '%')
    or lastname ilike concat('%', nama, '%');

    if not found then
        raise 'Data % tidak ditemukan', nama;
    end if;
end;
$$;

select * from getName('Alvin')


--3. Function person dashboard persontype+totalperson, region+totalperson
create or replace function personDashboard()
returns table (
    person_type varchar,
    total_person int
)
language plpgsql
as
$$
declare
    person_cursor cursor for
    select persontype, cast (count(*)as int)
    from person.person
    group by persontype;
begin
    open person_cursor;
    loop
        fetch next from person_cursor into person_type, total_person;
        exit when not found;
        return next;
    end loop;
    close person_cursor;
end;
$$;

select * from personDashboard()


create or replace function person_region_dashboard()
returns table (
    stateprovince varchar,
    total_person int
)
language plpgsql
as
$$
declare
    personRegion_cursor cursor for
    select stateprovince.countryregioncode, count(person.businessentityid)::int
    from person.person
    join person.businessentityaddress using(businessentityid)
    join person.address using(addressid)
    join person.stateprovince using(stateprovinceid)
    group by countryregioncode;
begin
    open personRegion_cursor;
    loop
        fetch next from personRegion_cursor into stateprovince, total_person;
        exit when not found;
        return next;
    end loop;
    close personRegion_cursor;
end;
$$;

select * from person_region_dashboard()


--procedure

create or replace procedure insertdata(in datauser json,in datacust json)
language plpgsql
as
$$
declare
   rowid int;
   rowidcust int;
begin
--  begin
    insert into users(username,password)
    select x.username, x.password from json_to_recordset(datauser) x(username varchar,password text)
     returning id into rowid;

     insert into customer(firstname,lastname,user_id)
     select x.firstname, x.lastname,rowid from json_to_recordset(datacust) x(firstname varchar,lastname varchar)
     returning id into rowidcust;

--   if rowidcust is null then
--     rollback;
--        raise 'Gagal insert data';
--   else
--     commit;
--  end if;
--  end;
end;
$$;

call insertdata('[{"username":"alvin","password":"bootcampcodex"}]','[{"firstname":"faiz","lastname":"rinaldi"}]')


--
create or replace procedure person.insertSignUp(in dataperson json)
language plpgsql
as
$$
declare
    beid int;
begin
    insert into person.businessentity(rowguid)
    values(default)
    returning businessentityid into beid;

    insert into person.person (
        businessentityid,
        persontype,
        firstname,
        middlename,
        lastname,
        suffix,
        emailpromotion
    )
    select beid, x.persontype, x.firstname, x.middlename, x.lastname, x.suffix, x.emailpromotion
    from json_to_recordset(dataperson) x(persontype varchar, firstname varchar, middlename varchar, lastname varchar,
    suffix varchar, emailpromotion int);

    insert into person.personphone(
        businessentityid,
        phonenumber,
        phonenumbertypeid
    )
    select beid, x.phonenumber, x.phonenumbertypeid
    from json_to_recordset(dataperson) x(phonenumber varchar, phonenumbertypeid int);

    end;
$$;

call person.insertSignUp('[{"persontype":"EM", "firstname":"alvin", "middlename":"faiz", "lastname":"rinaldi",
                  "suffix":"junior", "emailpromotion":1, "phonenumber":"089508955565", "phonenumbertypeid":1}]')

    select max(businessentityid) from person.businessentity

    select * from person.personphone
--  where businessentityid=20777
    order by businessentityid desc

--
create or replace procedure purchasing.addtocart(in dt_pur json, in dt_detail json)
language plpgsql
as
$$
declare
    idpur int;
    iddetail int;
    pur_rec record;
    de_rec record;
begin
    select * from json_to_recordset(dt_pur)
    x(purchaseorderid int, revisionnumber int,
      status int,employeeid int, vendorid int,
      shipmethodid int, subtotal numeric,
      taxamt numeric, freight numeric)into pur_rec;
    update purchasing.purchaseorderheader set subtotal = pur_rec.subtotal
    where purchasing.purchaseorderheader.purchaseorderid = pur_rec.purchaseorderid
    and purchasing.purchaseorderheader.employeeid = pur_rec.employeeid
    and purchasing.purchaseorderheader.status = 1;

    if not found then

    insert into purchasing.purchaseorderheader(
        revisionnumber,
        status,
        employeeid,
        vendorid,
        shipmethodid,
        orderdate,shipdate,
        subtotal,
        taxamt,
        freight)
    select x.revisionnumber, x.status, x.employeeid,
    x.vendorid, x.shipmethodid, x.orderdate,
    x.shipdate, x.subtotal, x.taxamt, x.freight
    from json_to_recordset(dt_pur)
    x(purchaseorderid int, revisionnumber smallint, status smallint,
      employeeid int, vendorid int, shipmethodid int,
      orderdate timestamp, shipdate timestamp, subtotal numeric,
      taxamt numeric, freight numeric)
    returning purchaseorderid into idpur;

    insert into purchasing.purchaseorderdetail(
        purchaseorderid,
        duedate,
        orderqty,
        productid,
        unitprice,
        receivedqty,
        rejectedqty)
    select idpur, y.duedate, y.orderqty,
    y.productid, y.unitprice, y.receivedqty, y.rejectedqty
    from json_to_recordset(dt_detail)
    y(duedate timestamp, orderqty int, productid int,
      unitprice numeric, receivedqty int, rejectedqty int)
    returning purchaseorderdetailid into iddetail;

    else
    for de_rec in select * from json_to_recordset(dt_detail) as
    y(purchaseorderid int, purchaseorderdetailid int, duedate timestamp,
      orderqty int,productid int, unitprice numeric,receivedqty int,rejectedqty int)
    loop
        update purchasing.purchaseorderdetail
        set orderqty = de_rec.orderqty
        where purchasing.purchaseorderdetail.purchaseorderdetailid =  de_rec.purchaseorderdetailid;
    end loop;
        delete from purchasing.purchaseorderdetail
        where purchasing.purchaseorderdetail.purchaseorderid = pur_rec.purchaseorderid
        and purchasing.purchaseorderdetail.purchaseorderdetailid
        not in (select y.purchaseorderdetailid from json_to_recordset(dt_detail) as
                y(purchaseorderdetailid int, duedate timestamp, orderqty int,
                  productid int, unitprice numeric,receivedqty int,rejectedqty int));
        end if;
end;
$$;

drop procedure addtocart(in dt_pur json, dt_detail json)

select * from purchasing.purchaseorderdetail
where purchasing.purchaseorderdetail.purchaseorderid = 4028
order by purchasing.purchaseorderdetail.purchaseorderid;

select * from purchasing.purchaseorderheader
order by purchasing.purchaseorderheader.purchaseorderid desc;

update purchasing.purchaseorderheader set shipmethodid = 3
where purchasing.purchaseorderheader.purchaseorderid = 2
--  and purchasing.purchaseorderheader.employeeid = 253
--  and purchasing.purchaseorderheader.status = 4;

call purchasing.addtocart ('[{"purchaseorderid":7, "revisionnumber":4,"status":4,"employeeid":258,"vendorid": 1580,"shipmethodid":3,"orderdate":"2011-04-16 00:00:00","shipdate":"2011-04-25 00:00:00","subtotal":201.04,"taxamt":16.0832,"freight": 16.0832}]',
'[{"duedate":"2011-04-30 00:00:00","orderqty":4,"productid":4,"unitprice":50.26,"receivedqty":3,"rejectedqty":0}]')