drop view if exists view_statistic_banks;

--gopg:split

CREATE VIEW view_statistic_banks AS
SELECT
  'Penjualan' :: text AS jenis_transaksi,
  orders.anggota_id,
  CASE
    WHEN orders.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.id
    ELSE bank_edcs.id
  END AS bank_id,
  CASE
    WHEN orders.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.jenis_metode
    ELSE bank_edcs.nama
  END AS bank_name,
  metode_pembayarans.nama_tujuan,
  orders.toko_id,
  tokos.nama_toko,
  orders.relasi_id,
  relasis.nama_lengkap,
  relasis.no_hp,
  orders.no_invoice,
  orders.total_harga,
  orders.pembayaran,
  orders.potongan,
  orders.type_pembayaran AS metode_pembayaran,
  orders.tanggal_jual AS created_at,
  orders.tunai_nominal,
  orders.transfer_nominal,
  orders.edc_nominal
FROM
  orders
  LEFT JOIN relasis ON relasis.id = orders.relasi_id
  LEFT JOIN tokos ON tokos.id = orders.toko_id
  LEFT JOIN metode_pembayarans ON metode_pembayarans.id = orders.metode_pembayaran_id
  LEFT JOIN bank_edcs ON bank_edcs.id = orders.bank_edc_id
WHERE
  orders.type_pembayaran <> 'tunai' :: text
  AND orders.deleted_at IS NULL
  AND orders.deleted_by IS NULL
UNION
SELECT
  'Penjualan Jasa' :: text AS jenis_transaksi,
  transaksi_jasas.anggota_id,
  CASE
    WHEN transaksi_jasas.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.id
    ELSE bank_edcs.id
  END AS bank_id,
  CASE
    WHEN transaksi_jasas.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.jenis_metode
    ELSE bank_edcs.nama
  END AS bank_name,
  metode_pembayarans.nama_tujuan,
  transaksi_jasas.toko_id,
  tokos.nama_toko,
  transaksi_jasas.relasi_id,
  relasis.nama_lengkap,
  relasis.no_hp,
  transaksi_jasas.no_invoice,
  transaksi_jasas.total_harga,
  transaksi_jasas.jenis_pembayaran AS pembayaran,
  transaksi_jasas.potongan,
  transaksi_jasas.type_pembayaran AS metode_pembayaran,
  transaksi_jasas.tgl_pesan AS created_at,
  transaksi_jasas.tunai_nominal,
  transaksi_jasas.transfer_nominal,
  transaksi_jasas.edc_nominal
FROM
  transaksi_jasas
  LEFT JOIN relasis ON relasis.id = transaksi_jasas.relasi_id
  LEFT JOIN tokos ON tokos.id = transaksi_jasas.toko_id
  LEFT JOIN metode_pembayarans ON metode_pembayarans.id = transaksi_jasas.metode_pembayaran_id
  LEFT JOIN bank_edcs ON bank_edcs.id = transaksi_jasas.bank_edc_id
WHERE
  transaksi_jasas.type_pembayaran <> 'tunai' :: text
  AND transaksi_jasas.deleted_at IS NULL
  AND transaksi_jasas.deleted_by IS NULL
UNION
SELECT
  'Penjualan Kustom' :: text AS jenis_transaksi,
  transaksi_pesanan_customs.anggota_id,
  CASE
    WHEN transaksi_pesanan_customs.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.id
    ELSE bank_edcs.id
  END AS bank_id,
  CASE
    WHEN transaksi_pesanan_customs.type_pembayaran = 'transfer' :: text THEN metode_pembayarans.jenis_metode
    ELSE bank_edcs.nama
  END AS bank_name,
  metode_pembayarans.nama_tujuan,
  transaksi_pesanan_customs.toko_id,
  tokos.nama_toko,
  transaksi_pesanan_customs.relasi_id,
  relasis.nama_lengkap,
  relasis.no_hp,
  transaksi_pesanan_customs.no_invoice,
  transaksi_pesanan_customs.harga_produk AS total_harga,
  transaksi_pesanan_customs.status_transaksi AS pembayaran,
  transaksi_pesanan_customs.potongan,
  transaksi_pesanan_customs.type_pembayaran AS metode_pembayaran,
  transaksi_pesanan_customs.tgl_pesan AS created_at,
  transaksi_pesanan_customs.tunai_nominal,
  transaksi_pesanan_customs.transfer_nominal,
  transaksi_pesanan_customs.edc_nominal
FROM
  transaksi_pesanan_customs
  LEFT JOIN relasis ON relasis.id = transaksi_pesanan_customs.relasi_id
  LEFT JOIN tokos ON tokos.id = transaksi_pesanan_customs.toko_id
  LEFT JOIN metode_pembayarans ON metode_pembayarans.id = transaksi_pesanan_customs.metode_pembayaran_id
  LEFT JOIN bank_edcs ON bank_edcs.id = transaksi_pesanan_customs.bank_edc_id
WHERE
  transaksi_pesanan_customs.type_pembayaran <> 'tunai' :: text
  AND transaksi_pesanan_customs.deleted_at IS NULL
  AND transaksi_pesanan_customs.deleted_by IS NULL;