/*
SQLyog Ultimate v10.3 
MySQL - 5.5.5-10.4.27-MariaDB : Database - db_ijin
*********************************************************************
*/




CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_ijin` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `db_ijin`;

/*Table structure for table `cuti` */


CREATE TABLE `cuti` (
  `id_cuti` varchar(22) NOT NULL,
  `tanggal_mulai` date DEFAULT NULL,
  `tanggal_selesai` date DEFAULT NULL,
  `tanggal_pengajuan` date DEFAULT NULL,
  `jenis_cuti` varchar(20) DEFAULT NULL,
  `tempat_cuti` varchar(20) DEFAULT NULL,
  `alamat_nohp` text DEFAULT NULL,
  `keperluan` text DEFAULT NULL,
  `lama_cuti` int(11) DEFAULT NULL,
  `id_pegawai` varchar(50) DEFAULT NULL,
  `id_atasan` varchar(50) DEFAULT NULL,
  `status_cuti` int(11) DEFAULT NULL,
  `dokumen_cuti` text DEFAULT NULL,
  PRIMARY KEY (`id_cuti`),
  KEY `id_pegawai` (`id_pegawai`),
  KEY `id_atasan` (`id_atasan`),
  CONSTRAINT `cuti_ibfk_1` FOREIGN KEY (`id_pegawai`) REFERENCES `pegawai` (`nip`),
  CONSTRAINT `cuti_ibfk_2` FOREIGN KEY (`id_atasan`) REFERENCES `pegawai` (`nip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

/*Data for the table `cuti` */

insert  into `cuti`(`id_cuti`,`tanggal_mulai`,`tanggal_selesai`,`tanggal_pengajuan`,`jenis_cuti`,`tempat_cuti`,`alamat_nohp`,`keperluan`,`lama_cuti`,`id_pegawai`,`id_atasan`,`status_cuti`,`dokumen_cuti`) values ('C-00000000000000000001','2019-05-24','2019-05-28','2019-05-23','Cuti Tahunan','Dalam Negeri','banjar','jalan jalan',4,'198204042008012035','197602201998031001',2,'sdsadsdasd'),('C-00000000000000000002','2019-12-30','2020-01-03','2019-07-25','Cuti Tahunan','Dalam Negeri','aasdasd','asdasdasd',5,'198204042008012035','197602201998031001',2,'sqaasd'),('C-00000000000000000003','2018-12-30','2019-01-02','2018-11-22','Cuti Tahunan','Dalam Negeri','dasdasdas','asdasdasd',3,'198204042008012035','197602201998031001',3,'sdasdsdasd'),('C-00000000000000000004','2019-05-24','2019-05-28','2019-05-23','Cuti Tahunan','Dalam Negeri','i','j',3,'198204042008012035','197602201998031001',1,'Screenshot_from_2019-05-05_01-37-19.png'),('C-00000000000000000005','2019-05-24','2019-05-24','2019-05-24','Cuti Tahunan','Dalam Negeri','hgfdsa','ghhgfd',1,'198204042008012035','197602201998031001',2,'Screenshot_from_2019-04-25_05-50-04.png');

/*Table structure for table `tb_config` */


CREATE TABLE `tb_config` (
  `id` varchar(2) NOT NULL,
  `namapa` varchar(64) DEFAULT NULL,
  `nama_ketua` varchar(40) DEFAULT NULL,
  `nip_ketua` varchar(18) DEFAULT NULL,
  `nama_wakil_ketua` varchar(200) NOT NULL,
  `nip_wakil_ketua` varchar(18) NOT NULL,
  `nama_panitera` varchar(30) DEFAULT NULL,
  `nip_sekretaris` varchar(18) DEFAULT NULL,
  `alamat` varchar(64) DEFAULT NULL,
  `nip_panitera` varchar(18) DEFAULT NULL,
  `nama_sekretaris` varchar(30) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `notelp` varchar(15) DEFAULT NULL,
  `logo` varchar(64) DEFAULT NULL,
  `version` varchar(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/*Data for the table `tb_config` */

insert  into `tb_config`(`id`,`namapa`,`nama_ketua`,`nip_ketua`,`nama_wakil_ketua`,`nip_wakil_ketua`,`nama_panitera`,`nip_sekretaris`,`alamat`,`nip_panitera`,`nama_sekretaris`,`email`,`notelp`,`logo`,`version`) values ('1','Pengadilan Agama Muara Bungo','Roli Wilpa, S. HI, M. Sy','198305242007041001','Roli Wilpa, S. HI, M. Sy','198305242007041001','Dakardi, S. Ag, M.Sy','197005111999032001','Jl. RM Thaher RImbo Tengah','197606122003121003','Marzuki, S.H.','pamuarabungo@yahoo.com','074721130','logo21.jpeg','1.0');

/*Table structure for table `tb_jabatan` */


CREATE TABLE `tb_jabatan` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `jab` varchar(150) DEFAULT NULL,
  `parent` int(2) DEFAULT NULL,
  `cabang` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `tb_jabatan` */

insert  into `tb_jabatan`(`id`,`jab`,`parent`,`cabang`) values (1,'KETUA PENGADILAN AGAMA',0,NULL),(2,'WAKIL KETUA',1,NULL),(3,'HAKIM',1,NULL),(4,'SEKRETARIS',1,NULL),(5,'PANITERA',1,NULL),(8,'KASUBBAG UMUM DAN KEUANGAN',4,NULL),(9,'KASUBBAG PTIP',4,NULL),(10,'KASUBBAG KEPEGAWAIAN DAN ORTALA',4,NULL),(12,'PANMUD HUKUM',5,NULL),(13,'PANMUD BANDING',5,NULL),(15,'PANITERA PENGGANTI',5,NULL),(16,'FUNGSIONAL ARSIPARIS',4,NULL),(17,'FUNGSIONAL APK APBN',4,NULL),(18,'FUNGSIONAL ANALIS KEPEGAWAIAN',4,NULL),(19,'FUNGSIONAL ANALIS SDM APARATUR',4,NULL),(20,'BENDAHARA',4,NULL),(21,'PENGADMINISTRASI HUKUM',5,NULL),(22,'PENGELOLA PERKARA',5,NULL),(24,'PANMUD PERMOHONAN',5,NULL),(25,'JURUSITA',5,NULL);

/*Table structure for table `tb_mohoncuti` */


CREATE TABLE `tb_mohoncuti` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `id_pegawai` int(6) NOT NULL,
  `alasan_cuti` varchar(64) NOT NULL,
  `tgl_pengajuan` date NOT NULL,
  `tgl_awal` date NOT NULL,
  `tgl_akhir` date NOT NULL,
  `jml_hari` int(2) NOT NULL,
  `alamat_cuti` varchar(256) NOT NULL,
  `jenis` varchar(30) NOT NULL DEFAULT '',
  `verifikasi` varchar(15) NOT NULL,
  `catt_admin` varchar(255) NOT NULL,
  `atasan_langsung` int(2) NOT NULL,
  `ppk` int(2) NOT NULL,
  `pertimbangan_atasan` varchar(255) NOT NULL,
  `catt_atasan` varchar(255) NOT NULL,
  `persetujuan` varchar(16) NOT NULL,
  `eviden` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_mohoncuti` (`id_pegawai`)
) ENGINE=MyISAM AUTO_INCREMENT=175 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

/*Data for the table `tb_mohoncuti` */

insert  into `tb_mohoncuti`(`id`,`id_pegawai`,`alasan_cuti`,`tgl_pengajuan`,`tgl_awal`,`tgl_akhir`,`jml_hari`,`alamat_cuti`,`jenis`,`verifikasi`,`catt_admin`,`atasan_langsung`,`ppk`,`pertimbangan_atasan`,`catt_atasan`,`persetujuan`,`eviden`) values (1,8,'Istirahat','2021-03-10','2021-03-12','2021-03-12',1,'Bungo','Cuti Alasan Penting','Diteruskan','',4,0,'Sudah diACC atasan ya..','Disetujui','',NULL),(155,8,'test','2021-04-10','2021-04-28','2021-04-28',1,'test sakit','Cuti Sakit','Diteruskan','TES',4,0,'d','Disetujui','',NULL),(3,8,'test','2021-03-16','2021-05-07','2021-05-10',2,'test','Cuti Tahunan','Diteruskan','diteruskan ke atasan langsung',4,0,'test','Disetujui','Disetujui',NULL),(4,8,'cuti lagi','2021-03-16','2021-03-19','2021-03-19',1,'cuti','Cuti Tahunan','Diteruskan','',4,0,'sudah disetujui ya.','Disetujui','Disetujui',NULL),(104,12,'test cuti','2021-03-26','2021-03-29','2021-03-29',1,'test cuti','Cuti Tahunan','Diteruskan','test',7,0,'etest as','Disetujui','',NULL),(105,11,'test','2021-03-26','2021-04-05','2021-04-06',1,'tsets','Cuti Tahunan','Diteruskan','diteruskan langsung',1,0,'okeee','Disetujui','Disetujui',NULL),(153,8,'aseraer','2021-04-10','2021-04-29','2021-04-29',1,'seraserar','Cuti Alasan Penting','Diteruskan','sd',4,1,'pok','Disetujui','',NULL),(115,8,'SAKIT','2021-03-28','2021-03-30','2021-03-30',1,'SAKER','Cuti Sakit','Diteruskan','test sms atasan',4,0,'sdfs','Disetujui','Disetujui',NULL),(152,8,'safdasdf','2021-04-10','2021-04-28','2021-04-29',2,'sdfasdfasdf','Cuti Tahunan','Diteruskan','qw',4,0,'','','',NULL),(151,8,'sfasdfasdf','2021-04-10','2021-04-30','2021-04-30',1,'sdadfasdf','Cuti Tahunan','Diteruskan','sadsdf',4,0,'','','',NULL),(154,8,'tes cuti','2021-04-10','2021-04-29','2021-04-30',2,'test cuti','Cuti Tahunan','Diteruskan','sdfasdf',4,0,'','','',NULL),(150,11,'sfsdfa','2021-04-10','2021-04-27','2021-04-27',1,'sdad','Cuti Tahunan','Diteruskan','jjkhj',1,0,'','','',NULL),(165,16,'TEST','2021-04-12','2021-04-19','2021-04-19',1,'TEST','Cuti Melahirkan','Pengajuan','',0,0,'','','',NULL),(143,11,'SASDF','2021-04-10','2021-04-19','2021-04-19',1,'SASDFASDF','Cuti Tahunan','Diteruskan','erwrwr',1,0,'sdfs','Disetujui','Disetujui',NULL),(166,16,'TEST','2021-04-12','2021-04-20','2021-04-20',1,'TEST','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(140,11,'test','2021-04-10','2021-04-27','2021-04-27',1,'setset','Cuti Tahunan','Diteruskan','',1,0,'','','',NULL),(156,8,'tes CAP','2021-04-10','2021-04-26','2021-04-26',1,'TES CAP','Cuti Alasan Penting','Diteruskan','EWFRW',4,0,'TEST CAP','Disetujui','',NULL),(158,8,'melahirkan','2021-04-10','2021-04-26','2021-04-30',5,'melahirkan','Cuti Melahirkan','Pengajuan','',0,0,'','','',NULL),(159,9,'test','2021-04-10','2021-04-28','2021-04-28',1,'test','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(170,9,'teset','2022-10-17','2022-10-17','2022-10-19',3,'test','Cuti Tahunan','Diteruskan','test ganti atasan langsung',5,2,'test ganti ppk','Disetujui','',NULL),(161,8,'test','2021-04-11','2021-04-26','2021-04-26',1,'tes','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(162,16,'test ketua','2021-04-12','2021-04-19','2021-04-19',1,'test ketua','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(163,16,'test pta','2021-04-12','2021-04-19','2021-04-19',1,'test','Cuti Alasan Penting','Pengajuan','',0,0,'','','',NULL),(164,9,'test alin','2021-04-12','2021-04-19','2021-04-19',1,'tes alin','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(167,9,'resr','2021-09-11','2021-09-13','2021-09-14',2,'safasdfadsf','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(168,8,'test cuti alasan penting','2021-09-24','2021-09-27','2021-09-28',2,'bungo','Cuti Alasan Penting','Diteruskan','CAP Diteruskan',4,2,'Diteruskan','Disetujui','Disetujui',NULL),(169,9,'test','2022-03-21','2022-03-21','2022-03-23',3,'sdfsdf','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(172,9,'test CAP','2022-10-24','2022-10-25','2022-10-25',1,'ssfdsf','Cuti Alasan Penting','Diteruskan','diteruskan',4,1,'Disetujui','Disetujui','','9_1666621911146.pdf'),(173,9,'test','2023-02-03','2023-02-03','2023-02-03',1,'set','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL),(174,9,'tset','2023-02-03','2023-02-03','2023-02-03',1,'test','Cuti Tahunan','Pengajuan','',0,0,'','','',NULL);

/*Table structure for table `tb_mohonijin` */


CREATE TABLE `tb_mohonijin` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `id_pegawai` int(6) NOT NULL,
  `alasan_cuti` varchar(64) NOT NULL,
  `tgl_pengajuan` date NOT NULL,
  `jam` datetime DEFAULT NULL,
  `jam_akhir` datetime DEFAULT NULL,
  `verifikasi` varchar(15) NOT NULL,
  `catt_admin` varchar(255) NOT NULL,
  `atasan_langsung` int(2) NOT NULL,
  `pertimbangan_atasan` varchar(255) NOT NULL,
  `catt_atasan` varchar(255) NOT NULL,
  `persetujuan` varchar(16) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `id_mohoncuti` (`id_pegawai`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

/*Data for the table `tb_mohonijin` */

insert  into `tb_mohonijin`(`id`,`id_pegawai`,`alasan_cuti`,`tgl_pengajuan`,`jam`,`jam_akhir`,`verifikasi`,`catt_admin`,`atasan_langsung`,`pertimbangan_atasan`,`catt_atasan`,`persetujuan`,`status`) values (4,9,'test jam','2023-01-22','2023-01-22 15:53:00','2023-01-23 21:56:00','Diteruskan','sdsad',4,'es','Disetujui','Disetujui',1),(16,8,'test jam gag','2023-01-22','2023-01-22 23:56:00','2023-02-05 00:02:00','Diteruskan','asd',4,'asd','Disetujui','Disetujui',1),(17,9,'Istirahat Siang','2023-02-02','2023-02-02 16:19:00','2023-02-05 08:31:00','Pengajuan','',0,'','','',1),(18,9,'nganter anak','2023-02-02','2023-02-02 16:26:00','2023-02-03 22:55:00','Pengajuan','',0,'','','',1),(19,9,'Istirahat Siang','2023-02-03','2023-02-03 23:57:00','2023-02-05 08:31:00','Diteruskan','',0,'','','',1),(20,9,'Istirahat Siang','2023-02-04','2023-02-04 00:07:00','2023-02-04 00:13:00','Diteruskan','',0,'','Disetujui','Disetujui',1),(21,9,'Istirahat Siang','2023-02-04','2023-02-04 00:15:00','2023-02-05 08:32:00','Diteruskan','',0,'','Disetujui','Disetujui',1),(22,9,'test lagiiiiii','2023-02-04','2023-02-04 00:18:00','2023-02-05 08:32:00','Diteruskan','test lagiiiii',4,'oke','Disetujui','Disetujui',1),(23,9,'Istirahat Siang','2023-02-04','2023-02-04 23:29:00','2023-02-05 08:32:00','Diteruskan','',0,'','Disetujui','Disetujui',1),(24,9,'Istirahat Siang','2023-02-05','2023-02-05 00:48:00','2023-02-05 08:32:00','Diteruskan','',0,'','Disetujui','Disetujui',1),(25,9,'Istirahat Siang','2023-02-05','2023-02-05 08:31:00','2023-02-13 12:11:00','Diteruskan','',0,'','Disetujui','Disetujui',1),(26,8,'etetert','2023-02-23','2023-02-23 21:25:00',NULL,'Pengajuan','',0,'','','',0),(27,9,'test','2023-02-23','2023-02-23 21:35:00',NULL,'Pengajuan','',0,'','','',0);

/* Procedure structure for procedure `ambil_cuti` */

/*!50003 DROP PROCEDURE IF EXISTS  `ambil_cuti` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `ambil_cuti`(IN `tahn` INT, IN `id` VARCHAR(50))
    READS SQL DATA
BEGIN
SELECT P.id_pegawai,P.tahun,COALESCE(SUM(P.jumlah),0) AS jumlah FROM 
(SELECT id_pegawai, CASE WHEN tahun_mulai = tahn AND tahun_selesai = tahn THEN j_tahun_mulai+j_tahun_selesai WHEN tahun_mulai = tahn AND tahun_selesai != tahn THEN j_tahun_mulai WHEN tahun_mulai != tahn AND tahun_selesai = tahn THEN j_tahun_selesai END AS jumlah, tahn AS tahun FROM `perhitungan_cuti` WHERE (tahun_mulai = tahn OR tahun_selesai = tahn) AND id_pegawai = id) AS P GROUP BY P.id_pegawai;
END */$$
DELIMITER ;

/*Table structure for table `cuti_setuju` */


/*!50001 DROP VIEW IF EXISTS `cuti_setuju` */;
/*!50001 DROP TABLE IF EXISTS `cuti_setuju` */;

/*!50001 CREATE TABLE  `cuti_setuju`(
 `id_cuti` varchar(22) ,
 `tanggal_mulai` date ,
 `tanggal_selesai` date ,
 `tanggal_pengajuan` date ,
 `jenis_cuti` varchar(20) ,
 `tempat_cuti` varchar(20) ,
 `alamat_nohp` text ,
 `keperluan` text ,
 `lama_cuti` int(11) ,
 `id_pegawai` varchar(50) ,
 `id_atasan` varchar(50) ,
 `status_cuti` int(11) ,
 `dokumen_cuti` text 
)*/;

/*!50001 DROP VIEW IF EXISTS `pegawai_cuti` */;

/*Table structure for table `pegawai_cuti2` */


/*!50001 DROP VIEW IF EXISTS `pegawai_cuti2` */;
/*!50001 DROP TABLE IF EXISTS `pegawai_cuti2` */;

/*!50001 CREATE TABLE  `pegawai_cuti2`(
 `id_cuti` varchar(22) ,
 `nip` varchar(50) ,
 `nama` varchar(100) ,
 `jabatan` varchar(200) ,
 `tanggal_mulai` date ,
 `tanggal_selesai` date ,
 `tanggal_pengajuan` date ,
 `lama_cuti` int(11) ,
 `tempat_cuti` varchar(20) ,
 `jenis_cuti` varchar(20) ,
 `status_cuti` varchar(16) ,
 `id_atasan` varchar(50) ,
 `id_pegawai` varchar(50) 
)*/;

/*Table structure for table `perhitungan_cuti` */


/*!50001 DROP VIEW IF EXISTS `perhitungan_cuti` */;
/*!50001 DROP TABLE IF EXISTS `perhitungan_cuti` */;

/*!50001 CREATE TABLE  `perhitungan_cuti`(
 `id_pegawai` varchar(50) ,
 `j_tahun_mulai` double ,
 `j_tahun_selesai` double ,
 `tahun_mulai` int(4) ,
 `tahun_selesai` int(4) 
)*/;

/*View structure for view cuti_setuju */

/*!50001 DROP TABLE IF EXISTS `cuti_setuju` */;
/*!50001 DROP VIEW IF EXISTS `cuti_setuju` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cuti_setuju` AS select `cuti`.`id_cuti` AS `id_cuti`,`cuti`.`tanggal_mulai` AS `tanggal_mulai`,`cuti`.`tanggal_selesai` AS `tanggal_selesai`,`cuti`.`tanggal_pengajuan` AS `tanggal_pengajuan`,`cuti`.`jenis_cuti` AS `jenis_cuti`,`cuti`.`tempat_cuti` AS `tempat_cuti`,`cuti`.`alamat_nohp` AS `alamat_nohp`,`cuti`.`keperluan` AS `keperluan`,`cuti`.`lama_cuti` AS `lama_cuti`,`cuti`.`id_pegawai` AS `id_pegawai`,`cuti`.`id_atasan` AS `id_atasan`,`cuti`.`status_cuti` AS `status_cuti`,`cuti`.`dokumen_cuti` AS `dokumen_cuti` from `cuti` where `cuti`.`status_cuti` = '2' */;
/*!50001 DROP VIEW IF EXISTS `pegawai_cuti` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `pegawai_cuti` AS select `p`.`nip` AS `nip`,`p`.`nama` AS `nama`,`p`.`jabatan` AS `jabatan`,`p`.`golongan` AS `golongan`,`p`.`unit_kerja` AS `unit_kerja`,`p`.`status` AS `status`,`p`.`password` AS `password`,`c`.`id_cuti` AS `id_cuti`,`c`.`tanggal_mulai` AS `tanggal_mulai`,`c`.`tanggal_selesai` AS `tanggal_selesai`,`c`.`tanggal_pengajuan` AS `tanggal_pengajuan`,`c`.`jenis_cuti` AS `jenis_cuti`,`c`.`tempat_cuti` AS `tempat_cuti`,`c`.`alamat_nohp` AS `alamat_nohp`,`c`.`keperluan` AS `keperluan`,`c`.`lama_cuti` AS `lama_cuti`,`c`.`id_pegawai` AS `id_pegawai`,`c`.`id_atasan` AS `id_atasan`,`c`.`status_cuti` AS `status_cuti`,`c`.`dokumen_cuti` AS `dokumen_cuti` from (`pegawai` `p` join `cuti` `c` on(`p`.`nip` = `c`.`id_pegawai`)) */;

/*View structure for view pegawai_cuti2 */

/*!50001 DROP TABLE IF EXISTS `pegawai_cuti2` */;
/*!50001 DROP VIEW IF EXISTS `pegawai_cuti2` */;

/*View structure for view perhitungan_cuti */

/*!50001 DROP TABLE IF EXISTS `perhitungan_cuti` */;
/*!50001 DROP VIEW IF EXISTS `perhitungan_cuti` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `perhitungan_cuti` AS select `cuti_setuju`.`id_pegawai` AS `id_pegawai`,case when extract(year from `cuti_setuju`.`tanggal_mulai`) < extract(year from `cuti_setuju`.`tanggal_selesai`) then sum(`cuti_setuju`.`lama_cuti`) - (5 * ((to_days(`cuti_setuju`.`tanggal_selesai`) - to_days(concat(extract(year from `cuti_setuju`.`tanggal_mulai`),'-12-31'))) DIV 7) + substr('0123444401233334012222340111123400012345001234550',7 * weekday(concat(extract(year from `cuti_setuju`.`tanggal_mulai`),'-12-31')) + weekday(`cuti_setuju`.`tanggal_selesai`) + 1,1)) else sum(`cuti_setuju`.`lama_cuti`) / 2 end AS `j_tahun_mulai`,case when extract(year from `cuti_setuju`.`tanggal_mulai`) < extract(year from `cuti_setuju`.`tanggal_selesai`) then 5 * ((to_days(`cuti_setuju`.`tanggal_selesai`) - to_days(concat(extract(year from `cuti_setuju`.`tanggal_mulai`),'-12-31'))) DIV 7) + substr('0123444401233334012222340111123400012345001234550',7 * weekday(concat(extract(year from `cuti_setuju`.`tanggal_mulai`),'-12-31')) + weekday(`cuti_setuju`.`tanggal_selesai`) + 1,1) else sum(`cuti_setuju`.`lama_cuti`) / 2 end AS `j_tahun_selesai`,extract(year from `cuti_setuju`.`tanggal_mulai`) AS `tahun_mulai`,extract(year from `cuti_setuju`.`tanggal_selesai`) AS `tahun_selesai` from `cuti_setuju` group by `cuti_setuju`.`id_pegawai`,extract(year from `cuti_setuju`.`tanggal_mulai`),extract(year from `cuti_setuju`.`tanggal_selesai`) */;

