<?php defined('BASEPATH') OR
exit('No direct script access allowed'); class Ztools extends CI_Controller {
public function __construct() {
parent::__construct();
}
function index() {
return;
}
function cview() {
// 1 view_barang
$sql = " CREATE VIEW view_barang AS
select `b`.`ID_BARANG` AS `id_barang`,`j`.`JENIS_BARANG` AS `jenis_barang`,`b`.`NAMA_BARANG` AS `nama_barang`,`b`.`BERAT_BARANG` AS `berat_barang`,`b`.`SATUAN_BARANG` AS `satuan_barang` from (`barang` `b` join `jenis_barang` `j`) where (`b`.`ID_JENIS_BARANG` = `j`.`ID_JENIS_BARANG`)
";
$query = $this->db->query($sql);
// 2 view_biaya
$sql = " CREATE VIEW view_biaya AS
select `biaya_pengiriman`.`ID_BIAYA` AS `id_biaya`,(select `k`.`NAMA_KOTA` from (`kota` `k` join `biaya_pengiriman` `b`) where (`k`.`ID_KOTA` = `b`.`ID_KOTA_ASAL`)) AS `kota_asal`,(select `k`.`NAMA_KOTA` from (`kota` `k` join `biaya_pengiriman` `b`) where (`k`.`ID_KOTA` = `b`.`ID_KOTA_TUJUAN`)) AS `kota_tujuan`,`biaya_pengiriman`.`TOTAL_BERAT` AS `total_berat`,`biaya_pengiriman`.`BIAYA` AS `biaya` from `biaya_pengiriman`
";
$query = $this->db->query($sql);
// 3 view_cari_biaya
$sql = " CREATE VIEW view_cari_biaya AS
select `b`.`ID_BIAYA` AS `id_biaya`,`b`.`ID_KOTA_TUJUAN` AS `id_kota_tujuan`,`k`.`NAMA_KOTA` AS `nama_kota_tujuan`,`b`.`TOTAL_BERAT` AS `total_berat`,`b`.`BIAYA` AS `biaya` from (`biaya_pengiriman` `b` join `kota` `k`) where (`b`.`ID_KOTA_TUJUAN` = `k`.`ID_KOTA`)
";
$query = $this->db->query($sql);
// 4 view_customer
$sql = " CREATE VIEW view_customer AS
select `c`.`ID_CUST` AS `ID_CUST`,`b`.`NAMA_BIDANG_KERJA` AS `NAMA_BIDANG_KERJA`,`c`.`EMAIL_CUST` AS `EMAIL_CUST`,`c`.`PASSWORD_CUST` AS `PASSWORD_CUST`,`c`.`NAMA_CUST` AS `NAMA_CUST`,`c`.`JENIS_KEL_CUST` AS `JENIS_KEL_CUST`,`c`.`TGL_LAHIR_CUST` AS `TGL_LAHIR_CUST`,`c`.`ALAMAT_CUST` AS `ALAMAT_CUST`,`c`.`KOTA_CUST` AS `KOTA_CUST`,`c`.`NO_TELP_CUST` AS `NO_TELP_CUST`,`c`.`PERUSAHAAN_CUST` AS `PERUSAHAAN_CUST`,`c`.`ALAMAT_PER_CUST` AS `ALAMAT_PER_CUST` from (`customer` `c` join `bidang_kerja` `b`) where (`b`.`ID_BIDANG_KERJA` = `c`.`ID_BIDANG_KERJA`)
";
$query = $this->db->query($sql);
// 5 view_detil_barang_pengiriman
$sql = " CREATE VIEW view_detil_barang_pengiriman AS
select `p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`b`.`ID_BARANG` AS `id_barang`,`b`.`NAMA_BARANG` AS `nama_barang`,`b`.`BERAT_BARANG` AS `berat_barang` from ((`pengiriman` `p` join `detil_pengiriman` `d`) join `barang` `b`) where ((`p`.`ID_PENGIRIMAN` = `d`.`ID_PENGIRIMAN`) and (`b`.`ID_BARANG` = `d`.`ID_BARANG`))
";
$query = $this->db->query($sql);
// 6 view_detil_pengiriman_barang
$sql = " CREATE VIEW view_detil_pengiriman_barang AS
select `c`.`ID_CUST` AS `id_cust`,`p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`b`.`ID_BARANG` AS `id_barang`,`b`.`NAMA_BARANG` AS `nama_barang`,`b`.`BERAT_BARANG` AS `berat_barang` from ((((`pengiriman` `p` join `detil_pengiriman` `d`) join `barang` `b`) join `customer` `c`) join `tracking` `t`) where ((`p`.`ID_PENGIRIMAN` = `d`.`ID_PENGIRIMAN`) and (`d`.`ID_BARANG` = `b`.`ID_BARANG`) and (`c`.`ID_CUST` = `t`.`ID_CUST`) and (`t`.`ID_PENGIRIMAN` = `p`.`ID_PENGIRIMAN`))
";
$query = $this->db->query($sql);
// 7 view_det_tracking
$sql = " CREATE VIEW view_det_tracking AS
select `t`.`NO_RESI` AS `no_resi`,`t`.`TANGGAL` AS `tanggal`,`t`.`POSISI` AS `posisi`,`t`.`STATUS_PENGIRIMAN` AS `status_pengiriman`,`b`.`NAMA_BARANG` AS `nama_barang`,`j`.`JENIS_BARANG` AS `jenis_barang`,`b`.`BERAT_BARANG` AS `berat_barang`,`p`.`NAMA_PENERIMA` AS `nama_penerima`,`p`.`TUJUAN_PENGIRIMAN` AS `tujuan_pengiriman`,`p`.`BIAYA_PENGIRIMAN` AS `biaya_pengiriman` from (((((`tracking` `t` join `pengiriman` `p`) join `customer` `c`) join `detil_pengiriman` `d`) join `barang` `b`) join `jenis_barang` `j`) where ((`c`.`ID_CUST` = `t`.`ID_CUST`) and (`t`.`ID_PENGIRIMAN` = `p`.`ID_PENGIRIMAN`) and (`p`.`ID_PENGIRIMAN` = `d`.`ID_PENGIRIMAN`) and (`d`.`ID_BARANG` = `b`.`ID_BARANG`) and (`b`.`ID_JENIS_BARANG` = `j`.`ID_JENIS_BARANG`))
";
$query = $this->db->query($sql);
// 8 view_history_pengiriman
$sql = " CREATE VIEW view_history_pengiriman AS
select `c`.`ID_CUST` AS `id_cust`,`p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`t`.`NO_RESI` AS `no_resi`,`p`.`TGL_PENGIRIMAN` AS `tgl_pengiriman`,`p`.`NAMA_PENERIMA` AS `nama_penerima`,`p`.`TUJUAN_PENGIRIMAN` AS `tujuan_pengiriman` from ((`pengiriman` `p` join `tracking` `t`) join `customer` `c`) where ((`p`.`ID_PENGIRIMAN` = `t`.`ID_PENGIRIMAN`) and (`c`.`ID_CUST` = `t`.`ID_CUST`))
";
$query = $this->db->query($sql);
// 9 view_jumlah_pengiriman
$sql = " CREATE VIEW view_jumlah_pengiriman AS
select distinct `k`.`NAMA_KOTA` AS `kota`,count(`p`.`ID_PENGIRIMAN`) AS `jumlah`,month(`p`.`TGL_PENGIRIMAN`) AS `bulan`,year(`p`.`TGL_PENGIRIMAN`) AS `tahun` from ((`kota` `k` join `pengiriman` `p`) join `biaya_pengiriman` `b`) where `p`.`ID_BIAYA` = `b`.`ID_BIAYA` and `b`.`ID_KOTA_TUJUAN` = `k`.`ID_KOTA` group by `k`.`NAMA_KOTA` ;
";
$query = $this->db->query($sql);
// 10 view_laporan_pengiriman_bulanan
$sql = " CREATE VIEW view_laporan_pengiriman_bulanan AS
select distinct `p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`c`.`NAMA_CUST` AS `nama_cust`,`k`.`NAMA_KOTA` AS `nama_kota`,sum(`b`.`BERAT_BARANG`) AS `berat_pengiriman`,dayofmonth(`p`.`TGL_PENGIRIMAN`) AS `tanggal`,month(`p`.`TGL_PENGIRIMAN`) AS `bulan`,year(`p`.`TGL_PENGIRIMAN`) AS `tahun`,`t`.`STATUS_PENGIRIMAN` AS `status_pengiriman` from ((((((`pengiriman` `p` join `customer` `c`) join `barang` `b`) join `detil_pengiriman` `d`) join `tracking` `t`) join `biaya_pengiriman` `bp`) join `kota` `k`) where `c`.`ID_CUST` = `t`.`ID_CUST` and `t`.`ID_PENGIRIMAN` = `p`.`ID_PENGIRIMAN` and `p`.`ID_PENGIRIMAN` = `d`.`ID_PENGIRIMAN` and `d`.`ID_BARANG` = `b`.`ID_BARANG` and `p`.`ID_BIAYA` = `bp`.`ID_BIAYA` and `k`.`ID_KOTA` = `bp`.`ID_KOTA_TUJUAN` group by `p`.`ID_PENGIRIMAN` ;
";
$query = $this->db->query($sql);
// 11 view_list_tracking
$sql = " CREATE VIEW view_list_tracking AS
select distinct `p`.`id_cabang` AS `id_cabang`,`p`.`id_agen` AS `id_agen`,`t`.`NO_RESI` AS `no_resi`,`t`.`ID_PENGIRIMAN` AS `id_pengiriman`,`c`.`ID_CUST` AS `id_cust`,`c`.`NAMA_CUST` AS `nama_cust` from ((`customer` `c` join `tracking` `t`) join `pengiriman` `p`) where ((`c`.`ID_CUST` = `t`.`ID_CUST`) and (`t`.`ID_PENGIRIMAN` = `p`.`ID_PENGIRIMAN`)) order by `t`.`NO_RESI`
";
$query = $this->db->query($sql);
// 12 view_pengiriman
$sql = " CREATE VIEW view_pengiriman AS
select `p`.`id_cabang` AS `id_cabang`,`p`.`id_agen` AS `id_agen`,`p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`p`.`NAMA_PENERIMA` AS `nama_penerima`,`p`.`TGL_PENGIRIMAN` AS `tgl_pengiriman`,`b`.`KOTA_TUJUAN` AS `kota_tujuan`,`p`.`BIAYA_PENGIRIMAN` AS `biaya_pengiriman`,`p`.`TUJUAN_PENGIRIMAN` AS `tujuan_pengiriman`,`p`.`ALAMAT_PENERIMA` AS `alamat_penerima`,`p`.`BERAT_PENGIRIMAN` AS `berat_pengiriman`,`p`.`STATUS_PEMBAYARAN` AS `status_pembayaran`,`p`.`PEMBAYARAN` AS `pembayaran`,`p`.`COLLY` AS `colly` from (`pengiriman` `p` join `biaya_pengiriman` `b`) where (`p`.`ID_BIAYA` = `b`.`ID_BIAYA`)
";
$query = $this->db->query($sql);
// 13 view_sum_berat_pengiriman
$sql = " CREATE VIEW view_sum_berat_pengiriman AS
select `view_detil_pengiriman_barang`.`id_pengiriman` AS `id_pengiriman`,sum(`view_detil_pengiriman_barang`.`berat_barang`) AS `tot_berat` from `view_detil_pengiriman_barang` group by `view_detil_pengiriman_barang`.`id_pengiriman` ;
";
$query = $this->db->query($sql);
// 14 view_surat_pengiriman
$sql = " CREATE VIEW view_surat_pengiriman AS
select distinct `p`.`ID_PENGIRIMAN` AS `id_pengiriman`,`p`.`NAMA_PENERIMA` AS `nama_penerima`,`c`.`NAMA_CUST` AS `nama_cust`,`p`.`TGL_PENGIRIMAN` AS `tgl_pengiriman` from ((`pengiriman` `p` join `tracking` `t`) join `customer` `c`) where `p`.`ID_PENGIRIMAN` = `t`.`ID_PENGIRIMAN` and `c`.`ID_CUST` = `t`.`ID_CUST` ;
";
$query = $this->db->query($sql);
}
}