<?php
defined('BASEPATH') or exit('No direct script access allowed');

class lapkeu_m extends CI_Model
{
    public function invoice_nocabjh()
    {
        $sql = "SELECT MAX(MID(inov_jurhcab,9,4)) AS inov_jurhcab
            FROM tb_jurhcab 
            WHERE MID(inov_jurhcab,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
        $query = $this->db->query($sql);
        //   if ($query->num_rows() > 0) {
        $row = $query->row();
        //       $n = ((int) $row->inov_jurhcab) + 1;
        //       $no = sprintf("%'.02d", $n);
        //   } else {
        //       $no = "01";
        //   }
        $inoicecabjh = 'LH' . $this->fungsi->user_login()->id_user . date('ymd');

        return $inoicecabjh;
    }

    public function invopengcab($id = null)
    {
        $tgl = date('d / m / Y');
        $this->db->from('tb_pengcab');
        $this->db->order_by('id_pengcab', 'asc');
        // $this->db->group_by('tanggal ', $tgl);
        $this->db->like('tgl_pengcab', $tgl);
        if ($id != null) {
            $this->db->whare('id_pengcab', $id);
        }
        $query = $this->db->get();
        return $query;
    }

    public function invoivelap($id = null)
    {
        $tgl = date('d / m / Y');
        $this->db->from('tb_sell');
        $this->db->order_by('id_sell', 'asc');
        $this->db->like('sell_tgl_byr', $tgl);
        $this->db->where('proses', 1);
        if ($id != null) {
            $this->db->whare('id_sell', $id);
        }
        $query = $this->db->get();
        return $query;
    }

    public function tot_service()
    {

        $tgl = date('d / m / Y');
        $sql = "SELECT count(sell_tgl_byr) as totservice FROM tb_sell  WHERE sell_tgl_byr='$tgl'";
        $result = $this->db->query($sql);
        return $result->row()->totservice;
    }

    public function invoice_pengcaba()
    {
        $sql = "SELECT MAX(MID(invoice_pengcab,9,4)) AS invoice_pengcab
            FROM tb_pengcab 
            WHERE MID(invoice_pengcab,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $row = $query->row();
            $n = ((int) $row->invoice_pengcab) + 1;
            $no = sprintf("%'.02d", $n);
        } else {
            $no = "01";
        }
        $inpengcaba = "PE" . date('ymd') . $no;
        return $inpengcaba;
    }

    public function addpencab($post)
    {
        $params['tgl_pengcab'] = $post['tgl_pengcab'];
        $params['bln_pengcab'] = $post['bln_pengcab'];
        $params['invoice_pengcab'] = $this->invoice_pengcaba();
        $params['nama_pengcab'] = $post['nama_pengcab'];
        $params['qty_pengcab'] = $post['qty_pengcab'];
        $params['hrg_pengcab'] = $post['hrg_pengcab'];
        $params['tot_pengcab'] = $post['qty_pengcab'] * $post['hrg_pengcab']; // revisi total pengeluaran
        $params['id_user'] = $this->fungsi->user_login()->nama;
        $params['id_cabang'] = $this->fungsi->user_login()->status_cabang;
        $this->db->insert('tb_pengcab', $params);
    }

    public function delpencab($id)
    {
        $this->db->where('id_pengcab', $id);
        $this->db->delete('tb_pengcab');
    }

    public function addlapJurnalHariancab($post)
    {
        $params['tgl_jurhcab'] = $post['tgl_jurhcab'];
        $params['inov_jurhcab'] = $this->invoice_nocabjh();
        $params['kasawal_jurhcab'] = $post['kasawal_jurhcab'];
        $params['pen_jurhcab'] = $post['pen_jurhcab'];
        $params['aksp_jurhcab'] = $post['aksp_jurhcab'];
        $params['peng_jurhcab'] = $post['peng_jurhcab'];
        $params['pemb_part'] = $post['pemb_part'];
        $params['kasakhir_jurhcab'] = $post['kasakhir_jurhcab'];
        $params['tgl_jurhcab_in'] = $post['tgl_jurhcab_in'];
        $params['totservis'] = $post['totservis'];
        $params['id_cabang'] = $this->fungsi->user_login()->status_cabang;
        $params['id_user'] = $this->fungsi->user_login()->nama;

        $this->db->insert('tb_jurhcab', $params);
    }

    ///--- pengeluran bulanan
    public function invopengcaboffice($id = null)
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->from('tb_pengcab');
            $this->db->order_by('id_pengcab', 'asc');
            $this->db->like('tgl_pengcab', $tgl);
        } else {
            $this->db->from('tb_pengcab');
            $this->db->where('id_pengcab', $id);
        }

        $query = $this->db->get();
        return $query;
    }

    public function gettotalpengcaboffice()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(tot_pengcab) as totpeng FROM tb_pengcab   where  bln_pengcab='$tgl'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(tot_pengcab) as totpeng FROM tb_pengcab  WHERE bln_pengcab='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpeng;
    }

    //---------------- pembelian part
    public function gettotalpesbrgcaboffice()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(stok_brg * hargam_brg) as totpesbrg FROM t_pesanbrg_cart_detil t_pesanbrg_cart_detil JOIN t_pesanbrg
            ON t_pesanbrg_cart_detil.id_pesbrg =t_pesanbrg.id_pesbrg   where  datein='$tgl' and byr_brg='0'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(stok_brg * hargam_brg) as totpesbrg FROM t_pesanbrg_cart_detil  WHERE datein='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpesbrg;
    }

    public function gettotalpesbrgcabofficeup()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(stok_brg * hargam_brgup) as totpesbrg FROM t_pesanbrg_cart_detil t_pesanbrg_cart_detil JOIN t_pesanbrg
            ON t_pesanbrg_cart_detil.id_pesbrg =t_pesanbrg.id_pesbrg   where  datein='$tgl' and byr_brg = '0'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(stok_brg * hargam_brgup) as totpesbrg FROM t_pesanbrg_cart_detil  WHERE datein='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpesbrg;
    }

    public function getofficeadmin($id = null)
    {

        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.stok_brg as stok_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.stok_brgup as stok_brgup');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.id_katbrgstok as id_katbrgstok');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.barcode_brg as barcode_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brg as hargam_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brg as hargam_brg');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.dateup as dateup');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brgup as hargam_brgup');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargau_brg as hargau_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargat_brg as hargat_brg');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.t_pesanbrg_detail  as t_pesanbrg_detail');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.name_brg as name_brg');
            $this->db->from('t_pesanbrg');
            $this->db->join('t_pesanbrg_cart_detil', 't_pesanbrg_cart_detil.id_pesbrg = t_pesanbrg.id_pesbrg');
            $this->db->order_by('id_pesbrg', 'asc');
            $this->db->where('bln_brg', $tgl);
            $this->db->where('byr_brg', 0);
        } else {
            $this->db->from('t_pesanbrg');
            $this->db->where('id_pesbrg',  $id);
        }

        $query = $this->db->get();
        return $query;
    }

    //--------------- hutang part
    public function getofficeadminhut($id = null)
    {

        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.stok_brg as stok_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.stok_brgup as stok_brgup');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.id_katbrgstok as id_katbrgstok');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.barcode_brg as barcode_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brg as hargam_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brg as hargam_brg');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.dateup as dateup');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargam_brgup as hargam_brgup');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargau_brg as hargau_brg');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.hargat_brg as hargat_brg');

            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.t_pesanbrg_detail  as t_pesanbrg_detail');
            $this->db->select('t_pesanbrg.* , t_pesanbrg_cart_detil.name_brg as name_brg');
            $this->db->from('t_pesanbrg');
            $this->db->join('t_pesanbrg_cart_detil', 't_pesanbrg_cart_detil.id_pesbrg = t_pesanbrg.id_pesbrg');
            $this->db->order_by('id_pesbrg', 'asc');
            $this->db->where('bln_brg', $tgl);
            $this->db->where('byr_brg', 1);
        } else {
            $this->db->from('t_pesanbrg');
            $this->db->where('id_pesbrg',  $id);
        }

        $query = $this->db->get();
        return $query;
    }

    //---------------- pembelian part
    public function gettotalpesbrgcabofficehut()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(stok_brg * hargam_brg) as totpesbrg FROM t_pesanbrg_cart_detil t_pesanbrg_cart_detil JOIN t_pesanbrg
                ON t_pesanbrg_cart_detil.id_pesbrg =t_pesanbrg.id_pesbrg   where  datein='$tgl' and byr_brg='1'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(stok_brg * hargam_brg) as totpesbrg FROM t_pesanbrg_cart_detil  WHERE datein='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpesbrg;
    }

    public function gettotalpesbrgcabofficeuphut()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(stok_brg * hargam_brgup) as totpesbrg FROM t_pesanbrg_cart_detil t_pesanbrg_cart_detil JOIN t_pesanbrg
                ON t_pesanbrg_cart_detil.id_pesbrg =t_pesanbrg.id_pesbrg   where  datein='$tgl' and byr_brg = '1'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(stok_brg * hargam_brgup) as totpesbrg FROM t_pesanbrg_cart_detil  WHERE datein='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpesbrg;
    }



    public function getbukubesacab($id = null)
    {
        if ($id != null) {
            $this->db->from('tb_jurhcab');
            $this->db->order_by('tgl_jurhcab', 'asc');
            $this->db->whare('id_jurhcab', $id);
        } elseif (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->from('tb_jurhcab');
            $this->db->order_by('tgl_jurhcab', 'asc');
            $this->db->like('tgl_jurhcab', $tgl);
        } else {
            $this->db->from('tb_jurhcab');
            $this->db->where('tgl_jurhcab', 000000);
        }
        $query = $this->db->get();
        return $query;
    }

    public function invoice_bbcb()
    {
        if (isset($_GET['Cab'])) {
            $cab = $_GET['Cab'];
            $sql = "SELECT MAX(MID(inov_lbbc,9,4)) AS inov_lbbc
            FROM tb_lapbukubesarcab 
            WHERE MID(inov_lbbc,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
            $query = $this->db->query($sql);
            $invoice_bbcb = "LB" . $this->fungsi->user_login()->id_user . date('ymd') . $cab;
        } else {
            $sql = "SELECT MAX(MID(inov_lbbc,9,4)) AS inov_lbbc
            FROM tb_lapbukubesarcab 
            WHERE MID(inov_lbbc,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
            $query = $this->db->query($sql);
            $invoice_bbcb = "LB" . $this->fungsi->user_login()->id_user . date('ymd');
        }
        return $invoice_bbcb;
    }

    public function addlapbukubesarcab($post)
    {
        $params['bln_lbbc'] = $post['bln_lbbc'];
        $params['inov_lbbc'] = $this->invoice_bbcb();
        $params['pen_lbbc'] = $post['pen_lbbc'];
        $params['id_cabang'] = $this->fungsi->user_login()->status_cabang;
        $params['id_user'] = $this->fungsi->user_login()->nama;

        $this->db->insert('tb_lapbukubesarcab', $params);
    }

    // ---- laoparan rugi laba

    public function getrlcab($id = null)
    {
        $tgl = date('m / Y');
        $this->db->from('tb_lapbukubesarcab');
        $this->db->where('id_cabang', '1',);
        $this->db->order_by('id_lbbc ', 'asc');
        $this->db->group_by('tanggal ', $tgl);
        $this->db->like('bln_lbbc', $tgl);

        if ($id != null) {


            $this->db->whare('id_lbbc', $id);
        }
        $query = $this->db->get();
        return $query;
    }

    public function invoice_rlcab()
    {

        if (isset($_GET['Cab'])) {
            $cab = $_GET['Cab'];
            $sql = "SELECT MAX(MID(invo_rlcab,9,4)) AS invo_rlcab
            FROM tb_rlcab 
            WHERE MID(invo_rlcab,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
            $query = $this->db->query($sql);
            $invoice_rlcab = "RL" . $this->fungsi->user_login()->id_user . date('ymd') . $cab;
        } else {
            $sql = "SELECT MAX(MID(invo_rlcab,9,4)) AS invo_rlcab
            FROM tb_rlcab 
            WHERE MID(invo_rlcab,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
            $query = $this->db->query($sql);
            $invoice_rlcab = "RL" . $this->fungsi->user_login()->id_user . date('ymd');
        }
        return $invoice_rlcab;
    }

    public function gettotalpencabofficerl()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(kasakhir_jurhcab) as totpenga FROM tb_jurhcab   WHERE tgl_jurhcab_in='$tgl'";
        } else {
            $tgl = date('m / Y');
            $sql = "SELECT sum(kasakhir_jurhcab) as totpenga FROM tb_jurhcab  WHERE tgl_jurhcab_in='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpenga;
    }

    //---------------- jurnal buku besar
    public function gettotalpencaboffice()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(kasakhir_jurhcab) as totpenga FROM tb_jurhcab   WHERE tgl_jurhcab_in='$tgl'";
        } else {
            $tgl = date('33 / m / Y');
            $sql = "SELECT sum(kasakhir_jurhcab) as totpenga FROM tb_jurhcab  WHERE tgl_jurhcab_in='$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->totpenga;
    }



    public function getpengrlcabtotal()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(harga_rlcab) as hargarlcab FROM tb_pengrlcab WHERE bln_pengrlcab= '$tgl'";
        } else {
            $tgl = date('m / Y');
            $sql = "SELECT sum(harga_rlcab) as hargarlcab FROM tb_pengrlcab  WHERE bln_pengrlcab= '$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->hargarlcab;
    }

    public function getpengrlcabgaji($id = null)
    {
        if ($id != null) {
            $this->db->from('tb_user');
            $this->db->order_by('id_user', 'asc');
            $this->db->whare('id_user', $id);
        } else {
            $this->db->from('tb_user');
            $this->db->where('karyawan', 1);
        }
        $query = $this->db->get();
        return $query;
    }

    public function getpengrlcabtotalgaji()
    {
        $sql = "SELECT sum(gaji) as totgaji FROM tb_user where karyawan='1' ";
        $result = $this->db->query($sql);
        return $result->row()->totgaji;
    }

    public function getpengrlcab($id = null)
    {

        if ($id != null) {
            $this->db->from('tb_pengrlcab');
            $this->db->order_by('id_pengrlcab', 'asc');
            $this->db->whare('id_pengrlcab', $id);
        } elseif (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->from('tb_pengrlcab');
            $this->db->order_by('id_pengrlcab', 'asc');
            $this->db->like('bln_pengrlcab', $tgl);
        } else {
            $tgl = date('m / Y');
            $this->db->from('tb_pengrlcab');
            $this->db->like('bln_pengrlcab', $tgl);
        }
        $query = $this->db->get();
        return $query;
    }

    public function invoice_pengrlcab()
    {
        $sql = "SELECT MAX(MID(inov_pengrlcab,9,4)) AS inov_pengrlcab
            FROM tb_pengrlcab 
            WHERE MID(inov_pengrlcab,3,6) = DATE_FORMAT(CURDATE(),'%y%m%d')";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $row = $query->row();
            $n = ((int) $row->inov_pengrlcab) + 1;
            $no = sprintf("%'.02d", $n);
        } else {
            $no = "01";
        }
        $pengrlcab = "RC" . date('ymd') . $no;
        return $pengrlcab;
    }

    public function addpengrlcab($post)
    {
        $params['bln_pengrlcab'] = $post['bln_pengrlcab'];
        $params['inov_pengrlcab'] = $this->invoice_pengrlcab();
        $params['peng_rlcab'] = $post['peng_rlcab'];
        $params['harga_rlcab'] = $post['harga_rlcab'];
        $params['note_rlcab'] = $post['note_rlcab'];
        $params['id_cabang'] = $this->fungsi->user_login()->status_cabang;
        $params['id_user'] = $this->fungsi->user_login()->nama;

        $this->db->insert('tb_pengrlcab', $params);
    }

    public function addpengnercab($post)
    {
        $params['invoice_inv'] = $post['invoice_inv'];
        $params['inv_omset'] = $post['inv_omset'];
        $params['inv_peng'] = $post['inv_peng'];
        $params['inv_gaji'] = $post['inv_gaji'];
        $params['inv_tot'] = $post['inv_tot'];
        $params['inv_bln'] = $post['inv_bln'];
        $params['inv_th'] = $post['inv_th'];
        $params['id_cabang'] = $this->fungsi->user_login()->status_cabang;
        $params['inv_id'] = $this->fungsi->user_login()->nama;

        $this->db->insert('tb_inv', $params);
    }

    public function delpenrlcab($id)
    {
        $this->db->where('id_pengrlcab', $id);
        $this->db->delete('tb_pengrlcab');
    }

    //------ laporan profit
    public function get_invtot()
    {
        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $sql = "SELECT sum(inv_tot) as invtot FROM tb_inv WHERE inv_th= '$tgl'";
        } else {
            $tgl = date('Y');
            $sql = "SELECT sum(inv_tot) as invtot FROM tb_inv  WHERE inv_th= '$tgl'";
        }
        $result = $this->db->query($sql);
        return $result->row()->invtot;
    }

    public function get_neraca($id = null)
    {

        if (isset($_GET['tgl'])) {
            $tgl = $_GET['tgl'];
            $this->db->from('tb_inv');
            $this->db->order_by('inv_bln', 'asc');
            $this->db->like('inv_th', $tgl);
        } else {
            $tgl = date('Y');
            $this->db->from('tb_inv');
            $this->db->where('inv_th', $tgl);
        }
        $query = $this->db->get();
        return $query;
    }
}
