
--DPBB
--14-02-2023
--RSSPM/ DPBB/ 01/ II/ 2023
--15-02-2023
CREATE TABLE rencana_alur (
  id serial NOT NULL,
  doc_name text,
  doc_urutan integer,
  pejabat text,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  primary key (id)
);
alter table auth_users add column if not exists asal_unit varchar(2) DEFAULT '2';
--15-02-2023
CREATE TABLE rencana_hdr (
  id bigserial NOT NULL,
  id_unit integer,
  doc_date date,
  doc_no text,
  doc_urutan integer,
  doc_month integer,
  doc_year integer,
  keterangan text,
  status integer DEFAULT 1,
  id_user integer,
  pajak_persen numeric DEFAULT 11,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  primary key (id)
);

CREATE TABLE rencana_ln (
  "id" bigserial NOT NULL,
  "id_inv" integer,
  "nama_brand" character varying(255),
  "jumlah_diminta" integer,
  "jumlah_disetujui" integer,
  "sisa" integer,
  "penggunaan_bulan" integer,
	"id_satuan" integer,
	"id_pabrik" integer,
	"id_supplier" integer,
	"harga_unit" numeric,
	"total_harga" numeric,
	"komen" text,
	"id_q" integer DEFAULT NULL,
	"diskon_persen" numeric,
	"diskon_amt" numeric,
	"pajak_persen" numeric,
	"pajak_amt" numeric,
	"nett_total" numeric,
  "id_unit" integer,
	"id_hdr" integer,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
    
    primary key (id)
);

CREATE TABLE master_pabrik (
  id bigserial NOT NULL,
  nama text,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
    
    primary key (id)
);

CREATE TABLE "rencana_pengguna" (
    "id_users" serial NOT NULL,
    "username" character varying(50),
    "password" character varying(50),
    "id_level" integer,
    "tanggal_login" date,
    "jam_login" reltime,
    "tanggal_edit" date,
    "jam_edit" reltime,
    "jabatan" text NULL,
    "status_login" "char",
    "asal_unit" character varying(2) DEFAULT '0',
     PRIMARY KEY ("id_users")
) ;

  CREATE TABLE "rencana_pivot" (
      "id" bigserial NOT NULL,
      "id_hdr" integer,
      "id_users" integer,
      "urutan" integer,
      "nama_kegiatan" text,
      "jabatan" text NULL,
       "catatan" text NULL,
      "status_approval" integer DEFAULT 0,
      created_at TIMESTAMP DEFAULT NOW(),
      updated_at TIMESTAMP DEFAULT NOW(),
       PRIMARY KEY ("id")
  ) ;


  --15-02-2023
CREATE TABLE rencana_unit (
  id serial NOT NULL,
  nama_unit text,
  kode varchar(20),
  linker text,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  primary key (id)
);



CREATE TABLE "rencana_ln_shadow" (
    "id" integer NOT NULL,
    "id_inv" integer,
    "nama_brand" character varying(255),
    "jumlah_diminta" integer,
    "jumlah_disetujui" integer,
    "sisa" integer,
    "penggunaan_bulan" integer,
    "id_satuan" integer,
    "id_pabrik" integer,
    "id_supplier" integer,
    "harga_unit" numeric,
    "total_harga" numeric,
    "komen" text,
    "id_q" integer,
    "diskon_persen" numeric,
    "diskon_amt" numeric,
    "pajak_persen" numeric,
    "pajak_amt" numeric,
    "nett_total" numeric,
    "id_hdr" integer,
    "created_at" timestamp DEFAULT now(),
    "updated_at" timestamp DEFAULT now()
) ;

alter table rencana_ln add column if not exists jenis_pengadaan varchar(100) DEFAULT NULL;
alter table rencana_ln add column if not exists golongan_obat varchar(50) DEFAULT NULL;

alter table rencana_hdr add column if not exists rkap varchar(50) DEFAULT 0;
alter table rencana_hdr add column if not exists telah_diminta varchar(50) DEFAULT 0;
alter table rencana_hdr add column if not exists permintaan varchar(50) DEFAULT 0;
alter table rencana_hdr add column if not exists sisa_rkp varchar(50) DEFAULT 0;
-- 2023-03-03 14:52:24.14357+07


--2023-05-10
CREATE TABLE ppab_rencana_hdr (
  id bigserial NOT NULL,
  id_unit integer,
  doc_date date,
  doc_no text,
  doc_urutan integer,
  doc_month integer,
  doc_year integer,
  keterangan text,
  status integer DEFAULT 1,
  id_user integer,
  pajak_persen numeric DEFAULT 11,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  primary key (id)
);
alter table ppab_rencana_hdr add column if not exists rkap varchar(50) DEFAULT 0;
alter table ppab_rencana_hdr add column if not exists telah_diminta varchar(50) DEFAULT 0;
alter table ppab_rencana_hdr add column if not exists permintaan varchar(50) DEFAULT 0;
alter table ppab_rencana_hdr add column if not exists sisa_rkp varchar(50) DEFAULT 0;

CREATE TABLE ppab_rencana_ln (
  "id" bigserial NOT NULL,
  "nama_brand" character varying(255),
  "jumlah_diminta" integer,
  "jumlah_disetujui" integer,
  "sisa" integer,
  "penggunaan_bulan" integer,
	"nama_satuan" character varying(255),
	"id_pabrik" integer,
	"id_supplier" integer,
	"harga_unit" numeric,
	"total_harga" numeric,
	"komen" text,
	"id_q" integer DEFAULT NULL,
	"diskon_persen" numeric,
	"diskon_amt" numeric,
	"pajak_persen" numeric,
	"pajak_amt" numeric,
	"nett_total" numeric,
  "id_unit" integer,
	"id_hdr" integer,
	"file_pendukung" text DEFAULT null,
  "jenis_pengadaan" varchar(100) DEFAULT NULL,
  "golongan_barang" varchar(50) DEFAULT NULL,
  rkap varchar(50) DEFAULT 0,
  telah_diminta varchar(50) DEFAULT 0,
  sisa_rkp varchar(50) DEFAULT 0,
  permintaan varchar(50) DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
    
    primary key (id)
);
alter table ppab_rencana_ln add column if not exists ongkos_kirim numeric;
alter table ppab_rencana_ln add column if not exists biaya_lain numeric;
alter table ppab_rencana_ln add column if not exists spesifikasi text ;

CREATE TABLE "ppab_rencana_pivot" (
      "id" bigserial NOT NULL,
      "id_hdr" integer,
      "id_users" integer,
      "urutan" integer,
      "nama_kegiatan" text,
      "jabatan" text NULL,
      "catatan" text NULL,
      "status_approval" integer DEFAULT 0,
      created_at TIMESTAMP DEFAULT NOW(),
      updated_at TIMESTAMP DEFAULT NOW(),
       PRIMARY KEY ("id")
  ) ;



--DPPB NON MEDIS
CREATE TABLE dppb_nonmedis_rencana_hdr (
  id bigserial NOT NULL,
  id_unit integer,
  doc_date date,
  doc_no text,
  doc_urutan integer,
  doc_month integer,
  doc_year integer,
  keterangan text,
  status integer DEFAULT 1,
  id_user integer,
  pajak_persen numeric DEFAULT 11,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  primary key (id)
);
alter table dppb_nonmedis_rencana_hdr add column if not exists rkap varchar(50) DEFAULT 0;
alter table dppb_nonmedis_rencana_hdr add column if not exists telah_diminta varchar(50) DEFAULT 0;
alter table dppb_nonmedis_rencana_hdr add column if not exists permintaan varchar(50) DEFAULT 0;
alter table dppb_nonmedis_rencana_hdr add column if not exists sisa_rkp varchar(50) DEFAULT 0;


CREATE TABLE dppb_nonmedis_rencana_ln (
  "id" bigserial NOT NULL,
  "id_brand" integer null,
  "nama_brand" character varying(255),
  "jumlah_diminta" integer,
  "jumlah_disetujui" integer,
  "sisa" integer,
  "penggunaan_bulan" integer,
	"nama_satuan" character varying(255),
	"id_pabrik" integer,
	"id_supplier" integer,
	"harga_unit" numeric,
	"total_harga" numeric,
	"komen" text,
	"id_q" integer DEFAULT NULL,
	"diskon_persen" numeric,
	"diskon_amt" numeric,
	"pajak_persen" numeric,
	"pajak_amt" numeric,
	"nett_total" numeric,
  "id_unit" integer,
	"id_hdr" integer,
	"file_pendukung" text DEFAULT null,
  "jenis_pengadaan" varchar(100) DEFAULT NULL,
  "golongan_barang" varchar(50) DEFAULT NULL,
  rkap varchar(50) DEFAULT 0,
  telah_diminta varchar(50) DEFAULT 0,
  sisa_rkp varchar(50) DEFAULT 0,
  permintaan varchar(50) DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
    
    primary key (id)
);
alter table dppb_nonmedis_rencana_ln add column if not exists spesifikasi text ;

CREATE TABLE "dppb_nonmedis_rencana_pivot" (
      "id" bigserial NOT NULL,
      "id_hdr" integer,
      "id_users" integer,
      "urutan" integer,
      "nama_kegiatan" text,
      "jabatan" text NULL,
      "catatan" text NULL,
      "status_approval" integer DEFAULT 0,
      created_at TIMESTAMP DEFAULT NOW(),
      updated_at TIMESTAMP DEFAULT NOW(),
       PRIMARY KEY ("id")
);


CREATE TABLE "dppb_nonmedis_master_barang" (
      "id" bigserial NOT NULL,
      "nama_barang" text,
      "satuan" text NULL,
      "spesifikasi" text NULL,
      "harga" varchar(50) DEFAULT NULL,
      created_at TIMESTAMP DEFAULT NOW(),
      updated_at TIMESTAMP DEFAULT NOW(),
       PRIMARY KEY ("id")
);


alter table rencana_ln add column if not exists penggunaan_bulan_farmasi integer default 0 ;
alter table rencana_ln add column if not exists penggunaan_bulan_rekanan integer default 0 ;

alter table rencana_ln add column if not exists penggunaan_bulan_unit_lain integer default 0 ;
alter table rencana_ln add column if not exists sisa_farmasi integer default 0 ;
alter table rencana_ln add column if not exists sisa_troli integer default 0 ;
alter table rencana_ln add column if not exists catatan_pembuat text NULL ;
alter table rencana_ln add column if not exists file_pendukung text NULL ;
