proses.php

<?php
session_start();
$hostname = "localhost";
$username = "root";
$password = "gunawan";

// mengatur koneksi dan disimpan dalam satu variabel
$koneksi_1 = mysql_connect($hostname, $username, $password);
$koneksi_2 = mysql_connect($hostname, $username, $password, true);

// mengatur pemilihan database sesuai koneksi
mysql_select_db('kinerja', $koneksi_1 );
mysql_select_db('db_transport', $koneksi_2 );

$userr = $_SESSION['username'];
$level = $_SESSION['level'];
$fullname = $_SESSION['fullname'];

$type = $_POST['type'];
$today= date('Y-m-d H:m:s');
$list_status = array('Inprogress', 'Approve');

if($type == 'data'){
    $draw = $_POST['draw'];
    $start = $_POST['start'];
    $length = $_POST['length'];
    $kata = $_POST['cari'];
    $tgl1 = $_POST['tgl1'];
    $tgl2 = $_POST['tgl2'];
    $where="";
    if(empty($tgl1)||empty($tgl2)){
        $where .="";
    }else{
        $where .=" AND ( datane.tgl_entry BETWEEN '".datetosqldate($tgl1)."' AND '".datetosqldate($tgl2)."' ) ";
    }
    if(empty($kata)){
        $where .="";
    }else{
        $where .=" AND (datane.nik LIKE '%$kata%' OR datane.nama LIKE '%$kata%') ";
    }

    $where1="";
    if(empty($tgl1)||empty($tgl2)){
        $where1 .="";
    }else{
        $where1 .=" AND ( datane.tgl BETWEEN '".datetosqldate($tgl1)."' AND '".datetosqldate($tgl2)."' ) ";
    }
    if(empty($kata)){
        $where1 .="";
    }else{
        $where1 .=" AND (datane.nik LIKE '%$kata%') ";
    }

    // $sql="SELECT COUNT(*) as total FROM $perintah WHERE id_perintah is not null $where";
    // echo $sql;
    $cek=mysql_query("SELECT COUNT(*) as total
                        FROM (
                        SELECT id_perintah id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
                        FROM $perintah UNION ALL
                        SELECT id_kepribadian id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
                        FROM $kepribadian UNION ALL
                        SELECT id_komplain id, nik, nama , tgl_entry, jenis, user_komplain, bagian, uraian, petugas
                        FROM $komplain
                        ) AS datane
                        LEFT JOIN $score s ON s.nik=datane.nik
                        WHERE datane.id IS NOT NULL $where ORDER BY datane.jenis",$koneksi_1);
    $res=mysql_fetch_array($cek);
    $tot=$res['total'];


    $cek1=mysql_query("SELECT COUNT(*) as totalperintah FROM $perintah as datane WHERE datane.id_perintah is not null $where",$koneksi_1);
    $res1=mysql_fetch_array($cek1);
    $tot1=$res1['totalperintah'];

    $cek2=mysql_query("SELECT COUNT(*) as totalkepribadian FROM $kepribadian as datane WHERE datane.id_kepribadian is not null $where",$koneksi_1);
    $res2=mysql_fetch_array($cek2);
    $tot2=$res2['totalkepribadian'];

    $cek3=mysql_query("SELECT COUNT(*) as totalkomplain FROM his_absen as datane WHERE datane.id_his_absen is not null $where1",$koneksi_2);
    $res3=mysql_fetch_array($cek3);
    $tot3=$res3['totalkomplain'];


    $query=mysql_query("SELECT @rownum := @rownum + 1 AS urutan, t.*
        FROM (
            SELECT datane.*, s.massa, s.terlambat, s.mangkir
            FROM (
            SELECT id_perintah id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $perintah UNION ALL
            SELECT id_kepribadian id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $kepribadian UNION ALL
            SELECT id_komplain id, nik, nama , tgl_entry, jenis, user_komplain, bagian, uraian, petugas
            FROM $komplain
            ) AS datane
            LEFT JOIN $score s ON s.nik=datane.nik
            WHERE datane.id IS NOT NULL $where ORDER BY datane.jenis DESC LIMIT $start, $length
        ) AS t,
        (SELECT @rownum := 0) r",$koneksi_1);

    $data = array();
    while($r = mysql_fetch_array($query)) {
        $r['tgl_entry'] = sqldatetodate($r['tgl_entry']);
        $data[] = $r;
    }
    $i=0;
    foreach ($data as $key) {
            // add new button
        // $data[$i]['statuse'] = $list_status[$data[$i]['status']];
        if($level=='admin'){
            $data[$i]['button'] = '<button title="View Detail" idnex="'.$data[$i]['uraian'].'" urat="'.$data[$i]['urutan'].'" class="btn btn-info btnview" ><i class="fa fa-bookmark-o"></i></button>
                                   <button title="Edit Data" idnex="'.$data[$i]['uraian'].'" urat="'.$data[$i]['urutan'].'" class="btn btn-warning btnedit" ><i class="fa fa-edit"></i></button> ';
        }else{
            $data[$i]['button'] = '<button title="View Detail" idnex="'.$data[$i]['uraian'].'" urat="'.$data[$i]['urutan'].'" class="btn btn-info btnview" ><i class="fa fa-bookmark-o"></i></button>
                                   <button title="Edit Data" idnex="'.$data[$i]['uraian'].'" urat="'.$data[$i]['urutan'].'" class="btn btn-warning btnedit" ><i class="fa fa-edit"></i></button> ';
        }
        $i++;
    }

    $datax = array( "draw" => $draw ,
                    "recordsTotal" => $tot,
                    "totalperintah" => $tot1,
                    "totalkepribadian" => $tot2,
                    "totalkomplain" => $tot3,
                    "recordsFiltered" => $tot,
                    "data" => $data);
    echo json_encode($datax);

}else if($type == 'cari'){
    $id = $_POST['id'];
    $urutan2 = $_POST['urutan2'];
    $array = array();
    $query = mysql_query("SELECT datane.*, s.massa, s.terlambat, s.mangkir
            FROM (
            SELECT id_perintah id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $perintah UNION ALL
            SELECT id_kepribadian id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $kepribadian UNION ALL
            SELECT id_komplain id, nik, nama , tgl_entry, jenis, user_komplain, bagian, uraian, petugas
            FROM $komplain
            ) AS datane
            LEFT JOIN $score s ON s.nik=datane.nik
            WHERE uraian='$id'",$koneksi_1);
    while($data = mysql_fetch_array($query)){
        $data['tgl_entry'] = sqldatetodate($data['tgl_entry']);
        // $data['dateTempo'] = sqldatetodate($data['dateTempo']);
        $data['uraian'] = nl2br($data['uraian']);
        $data['urutan2'] = $urutan2;
        array_push($array,$data);
    }

    $arr_detail = array();
    $query_dtl = mysql_query("SELECT datane.*, s.massa, s.terlambat, s.mangkir
            FROM (
            SELECT id_perintah id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $perintah UNION ALL
            SELECT id_kepribadian id, nik, nama , tgl_entry, jenis, '0' AS user_komplain, '0' AS bagian, uraian, petugas
            FROM $kepribadian UNION ALL
            SELECT id_komplain id, nik, nama , tgl_entry, jenis, user_komplain, bagian, uraian, petugas
            FROM $komplain
            ) AS datane
            LEFT JOIN $score s ON s.nik=datane.nik
            WHERE uraian='$id'",$koneksi_1);
    while($data2 = mysql_fetch_array($query_dtl)){
        $data2['uraian'] = nl2br($data2['uraian']);
        array_push($arr_detail,$data2);
    }
    echo json_encode(array('header'=>$array[0], 'detail'=>$arr_detail));

}else if($type == 'edit'){
    $idne = $_POST['idne'];
    $customer_name    = $_POST['customer_name'];
    $customer_address = $_POST['customer_address'];
    $customer_phone   = $_POST['customer_phone'];
    $customer_email   = $_POST['customer_email'];
    $customer_an = $_POST['customer_an'];
    $invoice     = $_POST['invoice'];
    $petugas     = $_POST['petugas'];
    $tanggal     = datetosqldate($_POST['tanggal']);
    $dateTempo   = isset($_POST['dateTempo']) ? datetosqldate($_POST['dateTempo']) : date('d/m/Y', strtotime("+1 week"));

    $id_header_detail = $_POST['id_header_detail'];
    $tgle       = $_POST['tgle'];
    $deskripsi  = $_POST['deskripsi'];
    $boking     = $_POST['boking'];
    $tiket      = $_POST['tiket'];
    $qty        = $_POST['qty'];
    $harga      = $_POST['harga'];
    $jumlah     = $_POST['jumlah'];
    $subtotal   = $_POST['subtotal'];

    $deletedx = $_POST['deletedx'];
    $arr_del = explode('-', $deletedx);
    $dellsum =0;
    foreach ($arr_del as $value) {
        $dell = mysql_query("DELETE FROM $kerusakan WHERE id_header_detail = '$value'",$koneksi_1);
        if($dell){  $dellsum++; };
    }

    $update = mysql_query("UPDATE $perintah SET
                            invoice     = '$invoice' ,
                            petugas     = '$petugas' ,
                            tanggal     = '$tanggal' ,
                            dateTempo   = '$dateTempo' ,
                            customer_name   = '$customer_name' ,
                            customer_address= '$customer_address' ,
                            customer_phone  = '$customer_phone' ,
                            customer_email  = '$customer_email' ,
                            customer_an     = '$customer_an' ,
                            updated_date = '$today' ,
                            updated_by = '$userr'
                          WHERE
                            id_header = '$idne' ",$koneksi_1);
    if($update){
        $sukses=0; $gagal=0;
        $last_id = mysql_insert_id();
        $list = count($deskripsi);
        for ($i=0; $i < $list; $i++) {
            $id_det = $id_header_detail[$i];
            $tgls = datetosqldate($tgle[$i]);
            $dess = $deskripsi[$i];
            $bokk  = $boking[$i];
            $tkt  = $tiket[$i];
            $quan = $qty[$i];
            $hrg  = $harga[$i];
            $jmle = $jumlah[$i];
            if($id_det == 'X'){
                $subinser = mysql_query("INSERT INTO $kerusakan
                                        (id_header, tgl, deskripsi, boking, tiket, qty, harga, jumlah)
                                        VALUES
                                        ('$idne', '$tgls', '$dess', '$bokk',  '$tkt', '$quan', '$hrg', '$jmle')",$koneksi_1);

                if($subinser){  $sukses++;  }else{  $gagal++; };

            }else{
                $subupdate = mysql_query("UPDATE $kerusakan SET
                                        tgl     = '$tgls' ,
                                        deskripsi   = '$dess' ,
                                        boking  = '$bokk' ,
                                        tiket   = '$tkt' ,
                                        qty     = '$quan' ,
                                        harga   = '$hrg' ,
                                        jumlah  = '$jmle'
                                      WHERE
                                        id_header_detail = '$id_det' ",$koneksi_1);

                if($subupdate){ $sukses++;  }else{  $gagal++; };
            }

        }
        $update = mysql_query("UPDATE $perintah SET subtotal = '$subtotal' WHERE id_header = '$idne' ",$koneksi_1);
        echo json_encode(array('status'=>'success', 'message'=>'Successfull Update = '.$sukses.' data, delete = '.$dellsum.' data'));
    }else{
        echo json_encode(array('status'=>'failed'));
    };

}else if($type == 'hapus'){
    $id = $_POST['id'];
    $delete = mysql_query("DELETE FROM $perintah where id_perintah='$id'",$koneksi_1);
    // $delete2 = mysql_query("DELETE FROM $kerusakan where id_header='$id'");
    if($delete){
        echo json_encode(array('status'=>'success'));
    }else{
        echo json_encode(array('status'=>'failed'));
    };

}else{
    echo json_encode(array('status'=>'failed', 'message'=>'Type Not Exist'));
};

/* ----ini list fungsi---- */
function datetosqldate($string){
    $arr = explode("/", $string);
    return $arr[2]."-".$arr[1]."-".$arr[0];
}
function sqldatetodate($string){
    $arr = explode("-", $string);
    return $arr[2]."/".$arr[1]."/".$arr[0];
}

function datetimepicker_tosqldate($string){
    $arr1 = explode(" ", $string);
    $date = $arr1[0];
    $time = $arr1[1];
    $arr2 = explode("/", $date);
    return $arr2[2]."-".$arr2[1]."-".$arr2[0]." ".$time;
}

function sqldate_todatetimepicker($string){
    $arr1 = explode(" ", $string);
    $date = $arr1[0];
    $time = $arr1[1];
    $arr2 = explode("-", $date);
    return $arr2[2]."/".$arr2[1]."/".$arr2[0]." ".$time;
}

?>