API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 24th, 2019
70
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 65.20 KB | None | 0 0
raw download clone embed print report
  1. SET GLOBAL log_bin_trust_function_creators = 1;
  2. SET GLOBAL event_scheduler = OFF;
  3.  
  4.  
  5.  
  6.  
  7.  
  8. USE `e-presensi`;
  9.  
  10. ALTER TABLE `tabsensi`
  11. CHANGE COLUMN `id_data_masuk` `id_data_masuk` INT(11) NOT NULL DEFAULT '0' AFTER `jam_masuk`,
  12. CHANGE COLUMN `jam_masuk_created_at` `jam_masuk_created_at` DATETIME NULL DEFAULT NULL AFTER `id_data_masuk`,
  13. CHANGE COLUMN `jam_masuk_by` `jam_masuk_by` VARCHAR(50) NULL DEFAULT '0' AFTER `jam_masuk_created_at`,
  14. CHANGE COLUMN `menit_boleh_terlambat` `menit_boleh_terlambat` INT(11) NOT NULL DEFAULT '0' AFTER `jam_masuk_by`,
  15. CHANGE COLUMN `menit_terlambat` `menit_terlambat` INT(11) NULL DEFAULT NULL AFTER `menit_boleh_terlambat`,
  16. CHANGE COLUMN `id_data_pulang` `id_data_pulang` INT(11) NOT NULL DEFAULT '0' AFTER `jam_pulang`,
  17. CHANGE COLUMN `jam_pulang_created_at` `jam_pulang_created_at` DATETIME NULL DEFAULT NULL AFTER `id_data_pulang`,
  18. CHANGE COLUMN `jam_pulang_by` `jam_pulang_by` VARCHAR(50) NOT NULL DEFAULT '0' AFTER `jam_pulang_created_at`,
  19. CHANGE COLUMN `menit_boleh_cp` `menit_boleh_cp` INT(11) NOT NULL DEFAULT '0' AFTER `jam_pulang_by`,
  20. CHANGE COLUMN `menit_cp` `menit_cp` INT(11) NOT NULL DEFAULT '0' AFTER `menit_boleh_cp`;
  21.  
  22.  
  23. delete from tlibur where tgl = "2019-01-01";
  24. delete from tlibur where tgl = "2019-02-05";
  25. delete from tlibur where tgl = "2019-03-07";
  26. delete from tlibur where tgl = "2019-04-01";
  27. delete from tlibur where tgl = "2019-04-02";
  28. delete from tlibur where tgl = "2019-04-03";
  29. delete from tlibur where tgl = "2019-04-04";
  30. delete from tlibur where tgl = "2019-04-05";
  31. delete from tlibur where tgl = "2019-04-19";
  32. delete from tlibur where tgl = "2019-05-01";
  33. delete from tlibur where tgl = "2019-05-30";
  34. delete from tlibur where tgl = "2019-06-03";
  35. delete from tlibur where tgl = "2019-06-04";
  36. delete from tlibur where tgl = "2019-06-05";
  37. delete from tlibur where tgl = "2019-06-06";
  38. delete from tlibur where tgl = "2019-06-07";
  39. delete from tlibur where tgl = "2019-12-24";
  40. delete from tlibur where tgl = "2019-12-25";
  41.  
  42. INSERT INTO `tlibur` (`id`, `tgl`, `hari_libur`, `gup_id`, `all_grup`) VALUES
  43. (null, '2019-01-01', 'TAHUN BARU MASEHI', NULL, 1),
  44. (null, '2019-02-05', 'TAHUN BARU IMLEK', NULL, 1),
  45. (null, '2019-03-07', 'HARI RAYA NYEPI', NULL, 1),
  46. (null, '2019-04-03', 'PERINGATAN ISRA MIRAJ', NULL, 1),
  47. (null, '2019-04-19', 'JUMAT AGUNG', NULL, 1),
  48. (null, '2019-05-01', 'HARI BURUH', NULL, 1),
  49. (null, '2019-05-30', 'KENAIKAN ISA ALMASIH', NULL, 1),
  50. (null, '2019-06-03', 'CUTI BERSAMA IDUL FITRI', NULL, 1),
  51. (null, '2019-06-04', 'CUTI BERSAMA IDUL FITRI', NULL, 1),
  52. (null, '2019-06-05', 'IDUL FITRI', NULL, 1),
  53. (null, '2019-06-06', 'IDUL FITRI', NULL, 1),
  54. (null, '2019-06-07', 'CUTI BERSAMA IDUL FITRI', NULL, 1),
  55. (null, '2019-12-24', 'CUTI BERSAMA NATAL', NULL, 1),
  56. (null, '2019-12-25', 'HARI NATAL', NULL, 1);
  57.  
  58.  
  59.  
  60.  
  61.  
  62.  
  63.  
  64.  
  65.  
  66. CREATE TABLE IF NOT EXISTS `tb_jamkantor_kondisi` (
  67. `id` int(11) NOT NULL AUTO_INCREMENT,
  68. `shift` int(11) NOT NULL,
  69. `hari` int(11) NOT NULL,
  70. `kode_grup` int(11) NOT NULL DEFAULT 0,
  71. `mulai_absen_masuk` time NOT NULL,
  72. `jam_masuk` time NOT NULL,
  73. `batas_absen_masuk` time NOT NULL,
  74. `jam_breakin` time DEFAULT NULL,
  75. `batas_absen_breakin` time DEFAULT NULL,
  76. `jam_breakout` time DEFAULT NULL,
  77. `batas_absen_breakout` time DEFAULT NULL,
  78. `mulai_absen_pulang` time NOT NULL,
  79. `jam_pulang` time NOT NULL,
  80. `batas_absen_pulang` time NOT NULL,
  81. `stay_day` tinyint(4) NOT NULL DEFAULT 0,
  82. `ket` varchar(200) DEFAULT NULL,
  83. PRIMARY KEY (`id`),
  84. UNIQUE KEY `shiftday` (`shift`,`hari`)
  85. ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
  86.  
  87. INSERT INTO `tb_jamkantor_kondisi` (`id`, `shift`, `hari`, `kode_grup`, `mulai_absen_masuk`, `jam_masuk`, `batas_absen_masuk`, `jam_breakin`, `batas_absen_breakin`, `jam_breakout`, `batas_absen_breakout`, `mulai_absen_pulang`, `jam_pulang`, `batas_absen_pulang`, `stay_day`, `ket`) VALUES
  88. (4, 0, 2, 0, '06:00:00', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, '12:00:01', '15:00:00', '23:00:00', 0, 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  89. (8, 0, 3, 0, '06:00:00', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, '12:00:01', '15:00:00', '23:00:00', 0, 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  90. (12, 0, 4, 0, '06:00:00', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, '12:00:01', '15:00:00', '23:00:00', 0, 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  91. (16, 0, 5, 0, '06:00:00', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, '12:00:01', '15:00:00', '23:00:00', 0, 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  92. (20, 0, 6, 0, '06:00:00', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, '12:00:01', '15:30:00', '23:00:00', 0, 'JAM KANTOR BULAN RAMADHAN 1440 H');
  93.  
  94.  
  95. CREATE TABLE IF NOT EXISTS `tb_tgl_kondisi` (
  96. `tgl` date NOT NULL,
  97. `kondisi` varchar(250) DEFAULT NULL,
  98. PRIMARY KEY (`tgl`)
  99. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  100.  
  101. INSERT INTO `tb_tgl_kondisi` (`tgl`, `kondisi`) VALUES
  102. ('2019-05-06', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  103. ('2019-05-07', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  104. ('2019-05-08', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  105. ('2019-05-09', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  106. ('2019-05-10', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  107. ('2019-05-11', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  108. ('2019-05-12', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  109. ('2019-05-13', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  110. ('2019-05-14', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  111. ('2019-05-15', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  112. ('2019-05-16', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  113. ('2019-05-17', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  114. ('2019-05-18', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  115. ('2019-05-19', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  116. ('2019-05-20', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  117. ('2019-05-21', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  118. ('2019-05-22', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  119. ('2019-05-23', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  120. ('2019-05-24', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  121. ('2019-05-25', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  122. ('2019-05-26', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  123. ('2019-05-27', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  124. ('2019-05-28', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  125. ('2019-05-29', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  126. ('2019-05-30', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  127. ('2019-05-31', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  128. ('2019-06-01', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  129. ('2019-06-02', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  130. ('2019-06-03', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  131. ('2019-06-04', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  132. ('2019-06-05', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  133. ('2019-06-06', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  134. ('2019-06-07', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  135. ('2019-06-08', 'JAM KANTOR BULAN RAMADHAN 1440 H'),
  136. ('2019-06-09', 'JAM KANTOR BULAN RAMADHAN 1440 H');
  137.  
  138.  
  139.  
  140.  
  141.  
  142.  
  143.  
  144.  
  145.  
  146.  
  147. DROP FUNCTION IF EXISTS `tdaylyshiftschedule_f_filler`;
  148. DROP FUNCTION IF EXISTS `synch3_f_insert_or_update`;
  149. DROP FUNCTION IF EXISTS `synch3_f_non_shift_handler`;
  150. DROP FUNCTION IF EXISTS `synch3_f_non_shift_handler_condition`;
  151. DROP FUNCTION IF EXISTS `synch3_f_shift_stayday_handler`;
  152.  
  153. DELIMITER //
  154. CREATE DEFINER=`root`@`localhost` FUNCTION `synch3_f_insert_or_update`(`prm_checktime_id` INT, `prm_nrp` VARCHAR(20), `prm_tgl` DATE, `prm_waktu` TIME, `prm_checktype` VARCHAR(1), `prm_run_mode` VARCHAR(50)
  155. ) RETURNS tinyint(4)
  156. BEGIN
  157. SELECT COUNT(ti.nap), ti.nap, ti.ni, ti.id_grup, ti.shift, ti.kode_jam
  158. INTO @cekRegistered, @gotNAP, @gotNIP, @gotID_SK, @shift, @kode_jam
  159. FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  160.  
  161. IF @cekRegistered < 1 THEN /*NAP registerd in system */
  162. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 0, "Data NRP tidak terdaftar pada sistem!") INTO @MESSAGE;
  163. RETURN 0;
  164. END IF;
  165.  
  166.  
  167. /* [HARI KE 1] cek data shift stay day (shift kena dua hari) */
  168. SELECT COUNT(td.nap), td.kode_shift, td.tgl, "HARI-1"
  169. INTO @shift_stayday_hari_1, @kode_shift, @tgl_absen, @sts_shift
  170. FROM tdaylyshiftschedule td WHERE td.stay_day = 1
  171. AND td.nap = prm_nrp
  172. AND td.tgl = DATE(prm_tgl) LIMIT 1;
  173.  
  174. /* fungsi [HARI KE 1] cek data shift stay day */
  175. IF @shift_stayday_hari_1 > 0 THEN
  176. RETURN synch3_f_shift_stayday_handler(prm_checktime_id, prm_nrp, @tgl_absen, prm_waktu, prm_checktype, prm_run_mode, @kode_shift, @sts_shift);
  177. END IF;
  178.  
  179.  
  180.  
  181. /* [HARI KE 2] cek data shift stay day (shift kena dua hari) */
  182. SELECT COUNT(td2.nap), td2.kode_shift, td2.tgl, "HARI-2"
  183. INTO @shift_stayday_hari_2, @kode_shift, @tgl_absen, @sts_shift
  184. FROM tdaylyshiftschedule td2 WHERE td2.stay_day = 1
  185. AND td2.nap = prm_nrp
  186. AND td2.tgl = DATE_SUB(DATE(prm_tgl), INTERVAL 1 DAY) LIMIT 1;
  187.  
  188.  
  189. /* fungsi [HARI KE 2] cek data shift stay day */
  190. IF @shift_stayday_hari_2 > 0 THEN
  191. RETURN synch3_f_shift_stayday_handler(prm_checktime_id, prm_nrp, @tgl_absen, prm_waktu, prm_checktype, prm_run_mode, @kode_shift, @sts_shift);
  192. END IF;
  193.  
  194. /*cek apakah tgl masuk bulan ramadhan*/
  195. SELECT COUNT(V.tgl) INTO @jam_kondisi FROM tb_tgl_kondisi V WHERE V.tgl = prm_tgl;
  196.  
  197. CASE WHEN @shift = 0 THEN
  198.  
  199. /*fungsi tgl masuk bulan ramadhan (khusus non shift)*/
  200. IF @jam_kondisi > 0 THEN
  201. RETURN synch3_f_non_shift_handler_condition(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, prm_run_mode, @kode_jam);
  202. END IF;
  203.  
  204. /*jam normal biasa*/
  205. RETURN synch3_f_non_shift_handler(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, prm_run_mode, @kode_jam);
  206. ELSE
  207. RETURN synch3_f_shift_handler(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, prm_run_mode);
  208. END CASE;
  209.  
  210.  
  211. END//
  212. DELIMITER ;
  213.  
  214.  
  215. -- Dumping structure for function e-presensi.synch3_f_non_shift_handler
  216. DELIMITER //
  217. CREATE DEFINER=`root`@`localhost` FUNCTION `synch3_f_non_shift_handler`(`prm_checktime_id` INT, `prm_nrp` VARCHAR(20), `prm_tgl` DATE, `prm_waktu` TIME, `prm_checktype` VARCHAR(1), `prm_run_mode` VARCHAR(50), `prm_kode_jam` TINYINT(2)
  218. ) RETURNS tinyint(4)
  219. BEGIN
  220. SELECT COUNT(tj.id), tj.mulai_absen_masuk, tj.jam_masuk, tj.batas_absen_masuk, tj.mulai_absen_pulang, tj.jam_pulang, tj.batas_absen_pulang,
  221. tj.jam_breakin, tj.batas_absen_breakin, tj.jam_breakout, tj.batas_absen_breakout
  222. INTO @scheduleExistNormal, @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal,
  223. @breakinNormal, @batasBreakinNormal, @breakoutNormal, @batasBreakoutNormal
  224. FROM tjamkantor tj WHERE tj.hari = DAYOFWEEK(prm_tgl) AND tj.shift = prm_kode_jam LIMIT 1;
  225. IF @scheduleExistNormal < 1 THEN /* SCHEDULE tidak tersedia */
  226. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 10, CONCAT("DAY:", DAYNAME(prm_tgl), "=> ","Jam Kantor non shift tidak tersedia!")) INTO @MESSAGE;
  227. RETURN 10;
  228. END IF;
  229.  
  230. SELECT COUNT(ta.id), ta.jam_masuk, ta.jam_pulang, ta.id_data_masuk, ta.id_data_pulang, ta.breakout, ta.breakin
  231. INTO @countPrensented, @jamMasukTersimpan, @jamPulangTersimpan, @idJamMasukTersimpan, @idJamPulangTersimpan, @jamBreakoutTersimpan, @jamBreakinTersimpan
  232. FROM tabsensi ta WHERE ta.nap = prm_nrp AND ta.tgl = prm_tgl LIMIT 1;
  233. CASE WHEN @countPrensented = 0 THEN /* RECORD tabsensi tgl tersebut belum tersedia */
  234.  
  235. /* Prosess as new inserting data */
  236. IF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal THEN
  237. /* Insert as data breakout */
  238. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  239. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  240. INSERT INTO tabsensi (
  241. /* nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk, */
  242. nap, grup, tgl, breakout,
  243. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  244. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  245. )
  246. VALUES (
  247. prm_nrp, @grup, prm_tgl, prm_waktu,
  248. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  249. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  250. );
  251. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen BREAKOUT") INTO @MESSAGE;
  252. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  253. /* END Insert as data breakout */
  254.  
  255. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal THEN
  256. /* Insert as data breakin */
  257. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  258. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  259. INSERT INTO tabsensi (
  260. nap, grup, tgl, breakin,
  261. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  262. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  263. )
  264. VALUES (
  265. prm_nrp, @grup, prm_tgl, prm_waktu,
  266. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  267. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  268. );
  269. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen BREAKIN") INTO @MESSAGE;
  270. RETURN 12; /* RECORD tabsensi record baru absen breakin ditambahkan */
  271. /* END Insert as data breakin */
  272.  
  273.  
  274.  
  275. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal THEN
  276. /* Insert as data pagi */
  277. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  278. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  279. INSERT INTO tabsensi (
  280. nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk,
  281. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  282. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  283. )
  284. VALUES (
  285. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu < @masukNormal,"OK","TERLAMBAT")),
  286. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  287. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  288. );
  289. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen PAGI") INTO @MESSAGE;
  290. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  291. /* END Insert as data pagi */
  292. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal THEN
  293. /* Insert as data sore */
  294. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  295. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  296. INSERT INTO tabsensi (
  297. nap, grup, tgl, jam_pulang, id_data_pulang, jam_pulang_created_at, jam_pulang_by, ket_pulang,
  298. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  299. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  300. )
  301. VALUES (
  302. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu > @pulangNormal,"OK","CEPAT PULANG")),
  303. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  304. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  305. );
  306. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen SORE") INTO @MESSAGE;
  307. RETURN 12; /* RECORD tabsensi record baru absen SORE ditambahkan */
  308. /* END Insert as data sore */
  309. ELSE
  310. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 13, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  311. RETURN 13; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  312. END IF;
  313. /* END: Prosess as new inserting data */
  314. ELSE
  315. /* Prosess as updating data */
  316.  
  317.  
  318. IF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal THEN
  319. /* Update as data breakout */
  320. IF @jamBreakoutTersimpan IS NULL OR @jamBreakoutTersimpan = '00:00:00' THEN
  321.  
  322. UPDATE tabsensi
  323. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  324. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKOUT") INTO @MESSAGE;
  325. RETURN 14;
  326. ELSEIF @jamBreakoutTersimpan > prm_waktu THEN
  327. UPDATE tabsensi
  328. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  329. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKOUT") INTO @MESSAGE;
  330. RETURN 15;
  331. ELSEIF @jamBreakoutTersimpan < prm_waktu THEN
  332. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen BREAKOUT") INTO @MESSAGE;
  333. RETURN 16;
  334. ELSEIF @jamBreakoutTersimpan = prm_waktu THEN
  335. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKOUT") INTO @MESSAGE;
  336. RETURN 17;
  337. ELSE
  338. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKOUT") INTO @MESSAGE;
  339. RETURN 18;
  340. END IF;
  341. /* END Update as data breakout */
  342.  
  343.  
  344. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal THEN
  345. /* Update as data breakin */
  346. IF @jamBreakinTersimpan IS NULL OR @jamBreakinTersimpan = '00:00:00' THEN
  347.  
  348. UPDATE tabsensi
  349. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  350. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKIN") INTO @MESSAGE;
  351. RETURN 14;
  352. ELSEIF @jamBreakinTersimpan < prm_waktu THEN
  353. UPDATE tabsensi
  354. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  355. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKIN") INTO @MESSAGE;
  356. RETURN 15;
  357. ELSEIF @jamBreakinTersimpan > prm_waktu THEN
  358. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih cepat: Absen BREAKIN") INTO @MESSAGE;
  359. RETURN 16;
  360. ELSEIF @jamBreakinTersimpan = prm_waktu THEN
  361. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKIN") INTO @MESSAGE;
  362. RETURN 17;
  363. ELSE
  364. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKIN") INTO @MESSAGE;
  365. RETURN 18;
  366. END IF;
  367. /* END Update as data breakin */
  368.  
  369.  
  370.  
  371. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal THEN
  372. /* Update as data pagi */
  373. IF @jamMasukTersimpan IS NULL OR @jamMasukTersimpan = '00:00:00' THEN
  374. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  375. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen PAGI") INTO @MESSAGE;
  376. RETURN 14;
  377. ELSEIF @jamMasukTersimpan > prm_waktu THEN
  378. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  379. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen PAGI") INTO @MESSAGE;
  380. RETURN 15;
  381. ELSEIF @jamMasukTersimpan < prm_waktu THEN
  382. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen PAGI") INTO @MESSAGE;
  383. RETURN 16;
  384. ELSEIF @idJamMasukTersimpan = prm_checktime_id THEN
  385. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen PAGI") INTO @MESSAGE;
  386. RETURN 17;
  387. ELSE
  388. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen PAGI") INTO @MESSAGE;
  389. RETURN 18;
  390. END IF;
  391. /* END Update as data pagi */
  392.  
  393. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal THEN
  394. /* Update as data sore */
  395. IF @jamPulangTersimpan IS NULL OR @jamPulangTersimpan = '00:00:00' THEN
  396. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  397. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 19, "Data diperbaharui/Tambah sebagai data baru: Absen SORE") INTO @MESSAGE;
  398. RETURN 19;
  399. ELSEIF @jamPulangTersimpan < prm_waktu THEN
  400. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  401. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 20, "Data diperbaharui/Ganti data lama: Absen SORE") INTO @MESSAGE;
  402. RETURN 20;
  403. ELSEIF @jamPulangTersimpan > prm_waktu THEN
  404. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 21, "Data SKIPPED: Data tersimpan lebih akhir/besar: Absen SORE") INTO @MESSAGE;
  405. RETURN 21;
  406. ELSEIF @jamPulangTersimpan = prm_checktime_id THEN
  407. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 22, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen SORE") INTO @MESSAGE;
  408. RETURN 22;
  409. ELSE
  410. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 23, "Data SKIPPED: Alasan tidak diketahui.: Absen SORE") INTO @MESSAGE;
  411. RETURN 23;
  412. END IF;
  413.  
  414. /* END Update as data sore */
  415. ELSE
  416. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 24, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  417. RETURN 24; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  418. END IF;
  419. /* END: Prosess as updating data */
  420.  
  421. END CASE;
  422.  
  423. END//
  424. DELIMITER ;
  425.  
  426.  
  427. -- Dumping structure for function e-presensi.synch3_f_non_shift_handler_condition
  428. DELIMITER //
  429. CREATE DEFINER=`root`@`localhost` FUNCTION `synch3_f_non_shift_handler_condition`(`prm_checktime_id` INT, `prm_nrp` VARCHAR(20), `prm_tgl` DATE, `prm_waktu` TIME, `prm_checktype` VARCHAR(1), `prm_run_mode` VARCHAR(50), `prm_kode_jam` TINYINT(2)
  430. ) RETURNS tinyint(4)
  431. BEGIN
  432. SELECT COUNT(tj.id), tj.mulai_absen_masuk, tj.jam_masuk, tj.batas_absen_masuk, tj.mulai_absen_pulang, tj.jam_pulang, tj.batas_absen_pulang,
  433. tj.jam_breakin, tj.batas_absen_breakin, tj.jam_breakout, tj.batas_absen_breakout
  434. INTO @scheduleExistNormal, @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal,
  435. @breakinNormal, @batasBreakinNormal, @breakoutNormal, @batasBreakoutNormal
  436. FROM tb_jamkantor_kondisi tj WHERE tj.hari = DAYOFWEEK(prm_tgl) AND tj.shift = prm_kode_jam LIMIT 1;
  437. IF @scheduleExistNormal < 1 THEN /* SCHEDULE tidak tersedia */
  438. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 10, CONCAT("DAY:", DAYNAME(prm_tgl), "=> ","Jam Kantor non shift tidak tersedia!")) INTO @MESSAGE;
  439. RETURN 10;
  440. END IF;
  441.  
  442. SELECT COUNT(ta.id), ta.jam_masuk, ta.jam_pulang, ta.id_data_masuk, ta.id_data_pulang, ta.breakout, ta.breakin
  443. INTO @countPrensented, @jamMasukTersimpan, @jamPulangTersimpan, @idJamMasukTersimpan, @idJamPulangTersimpan, @jamBreakoutTersimpan, @jamBreakinTersimpan
  444. FROM tabsensi ta WHERE ta.nap = prm_nrp AND ta.tgl = prm_tgl LIMIT 1;
  445. CASE WHEN @countPrensented = 0 THEN /* RECORD tabsensi tgl tersebut belum tersedia */
  446.  
  447. /* Prosess as new inserting data */
  448. IF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal THEN
  449. /* Insert as data breakout */
  450. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  451. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  452. INSERT INTO tabsensi (
  453. /* nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk, */
  454. nap, grup, tgl, breakout,
  455. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  456. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  457. )
  458. VALUES (
  459. prm_nrp, @grup, prm_tgl, prm_waktu,
  460. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  461. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  462. );
  463. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen BREAKOUT") INTO @MESSAGE;
  464. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  465. /* END Insert as data breakout */
  466.  
  467. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal THEN
  468. /* Insert as data breakin */
  469. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  470. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  471. INSERT INTO tabsensi (
  472. nap, grup, tgl, breakin,
  473. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  474. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  475. )
  476. VALUES (
  477. prm_nrp, @grup, prm_tgl, prm_waktu,
  478. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  479. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  480. );
  481. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen BREAKIN") INTO @MESSAGE;
  482. RETURN 12; /* RECORD tabsensi record baru absen breakin ditambahkan */
  483. /* END Insert as data breakin */
  484.  
  485.  
  486.  
  487. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal THEN
  488. /* Insert as data pagi */
  489. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  490. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  491. INSERT INTO tabsensi (
  492. nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk,
  493. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  494. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  495. )
  496. VALUES (
  497. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu < @masukNormal,"OK","TERLAMBAT")),
  498. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  499. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  500. );
  501. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen PAGI") INTO @MESSAGE;
  502. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  503. /* END Insert as data pagi */
  504. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal THEN
  505. /* Insert as data sore */
  506. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  507. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  508. INSERT INTO tabsensi (
  509. nap, grup, tgl, jam_pulang, id_data_pulang, jam_pulang_created_at, jam_pulang_by, ket_pulang,
  510. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  511. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  512. )
  513. VALUES (
  514. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu > @pulangNormal,"OK","CEPAT PULANG")),
  515. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  516. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  517. );
  518. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen SORE") INTO @MESSAGE;
  519. RETURN 12; /* RECORD tabsensi record baru absen SORE ditambahkan */
  520. /* END Insert as data sore */
  521. ELSE
  522. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 13, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  523. RETURN 13; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  524. END IF;
  525. /* END: Prosess as new inserting data */
  526. ELSE
  527. /* Prosess as updating data */
  528.  
  529.  
  530. IF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal THEN
  531. /* Update as data breakout */
  532. IF @jamBreakoutTersimpan IS NULL OR @jamBreakoutTersimpan = '00:00:00' THEN
  533.  
  534. UPDATE tabsensi
  535. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  536. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKOUT") INTO @MESSAGE;
  537. RETURN 14;
  538. ELSEIF @jamBreakoutTersimpan > prm_waktu THEN
  539. UPDATE tabsensi
  540. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  541. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKOUT") INTO @MESSAGE;
  542. RETURN 15;
  543. ELSEIF @jamBreakoutTersimpan < prm_waktu THEN
  544. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen BREAKOUT") INTO @MESSAGE;
  545. RETURN 16;
  546. ELSEIF @jamBreakoutTersimpan = prm_waktu THEN
  547. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKOUT") INTO @MESSAGE;
  548. RETURN 17;
  549. ELSE
  550. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKOUT") INTO @MESSAGE;
  551. RETURN 18;
  552. END IF;
  553. /* END Update as data breakout */
  554.  
  555.  
  556. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal THEN
  557. /* Update as data breakin */
  558. IF @jamBreakinTersimpan IS NULL OR @jamBreakinTersimpan = '00:00:00' THEN
  559.  
  560. UPDATE tabsensi
  561. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  562. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKIN") INTO @MESSAGE;
  563. RETURN 14;
  564. ELSEIF @jamBreakinTersimpan < prm_waktu THEN
  565. UPDATE tabsensi
  566. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  567. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKIN") INTO @MESSAGE;
  568. RETURN 15;
  569. ELSEIF @jamBreakinTersimpan > prm_waktu THEN
  570. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih cepat: Absen BREAKIN") INTO @MESSAGE;
  571. RETURN 16;
  572. ELSEIF @jamBreakinTersimpan = prm_waktu THEN
  573. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKIN") INTO @MESSAGE;
  574. RETURN 17;
  575. ELSE
  576. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKIN") INTO @MESSAGE;
  577. RETURN 18;
  578. END IF;
  579. /* END Update as data breakin */
  580.  
  581.  
  582.  
  583. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal THEN
  584. /* Update as data pagi */
  585. IF @jamMasukTersimpan IS NULL OR @jamMasukTersimpan = '00:00:00' THEN
  586. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  587. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen PAGI") INTO @MESSAGE;
  588. RETURN 14;
  589. ELSEIF @jamMasukTersimpan > prm_waktu THEN
  590. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  591. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen PAGI") INTO @MESSAGE;
  592. RETURN 15;
  593. ELSEIF @jamMasukTersimpan < prm_waktu THEN
  594. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen PAGI") INTO @MESSAGE;
  595. RETURN 16;
  596. ELSEIF @idJamMasukTersimpan = prm_checktime_id THEN
  597. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen PAGI") INTO @MESSAGE;
  598. RETURN 17;
  599. ELSE
  600. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen PAGI") INTO @MESSAGE;
  601. RETURN 18;
  602. END IF;
  603. /* END Update as data pagi */
  604.  
  605. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal THEN
  606. /* Update as data sore */
  607. IF @jamPulangTersimpan IS NULL OR @jamPulangTersimpan = '00:00:00' THEN
  608. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  609. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 19, "Data diperbaharui/Tambah sebagai data baru: Absen SORE") INTO @MESSAGE;
  610. RETURN 19;
  611. ELSEIF @jamPulangTersimpan < prm_waktu THEN
  612. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  613. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 20, "Data diperbaharui/Ganti data lama: Absen SORE") INTO @MESSAGE;
  614. RETURN 20;
  615. ELSEIF @jamPulangTersimpan > prm_waktu THEN
  616. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 21, "Data SKIPPED: Data tersimpan lebih akhir/besar: Absen SORE") INTO @MESSAGE;
  617. RETURN 21;
  618. ELSEIF @jamPulangTersimpan = prm_checktime_id THEN
  619. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 22, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen SORE") INTO @MESSAGE;
  620. RETURN 22;
  621. ELSE
  622. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 23, "Data SKIPPED: Alasan tidak diketahui.: Absen SORE") INTO @MESSAGE;
  623. RETURN 23;
  624. END IF;
  625.  
  626. /* END Update as data sore */
  627. ELSE
  628. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 24, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  629. RETURN 24; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  630. END IF;
  631. /* END: Prosess as updating data */
  632.  
  633. END CASE;
  634.  
  635. END//
  636. DELIMITER ;
  637.  
  638.  
  639. -- Dumping structure for function e-presensi.synch3_f_shift_stayday_handler
  640. DELIMITER //
  641. CREATE DEFINER=`root`@`localhost` FUNCTION `synch3_f_shift_stayday_handler`(`prm_checktime_id` INT, `prm_nrp` VARCHAR(20), `prm_tgl` DATE, `prm_waktu` TIME, `prm_checktype` VARCHAR(1), `prm_run_mode` VARCHAR(50), `prm_kode_jam` TINYINT(2), `prm_status_shift` VARCHAR(50)
  642. ) RETURNS tinyint(4)
  643. BEGIN
  644. SELECT COUNT(tj.id), tj.mulai_absen_masuk, tj.jam_masuk, tj.batas_absen_masuk, tj.mulai_absen_pulang, tj.jam_pulang, tj.batas_absen_pulang,
  645. tj.jam_breakin, tj.batas_absen_breakin, tj.jam_breakout, tj.batas_absen_breakout
  646. INTO @scheduleExistNormal, @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal,
  647. @breakinNormal, @batasBreakinNormal, @breakoutNormal, @batasBreakoutNormal
  648. FROM tjamkantor tj WHERE tj.hari = DAYOFWEEK(prm_tgl) AND tj.shift = prm_kode_jam LIMIT 1;
  649. IF @scheduleExistNormal < 1 THEN /* SCHEDULE tidak tersedia */
  650. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 10, CONCAT("DAY:", DAYNAME(prm_tgl), " DATE:", prm_tgl, " => ","Jam Kantor shift stayday tidak tersedia!")) INTO @MESSAGE;
  651. /*RETURN 10;*/
  652. RETURN prm_kode_jam;
  653. END IF;
  654.  
  655. SELECT COUNT(ta.id), ta.jam_masuk, ta.jam_pulang, ta.id_data_masuk, ta.id_data_pulang, ta.breakout, ta.breakin
  656. INTO @countPrensented, @jamMasukTersimpan, @jamPulangTersimpan, @idJamMasukTersimpan, @idJamPulangTersimpan, @jamBreakoutTersimpan, @jamBreakinTersimpan
  657. FROM tabsensi ta WHERE ta.nap = prm_nrp AND ta.tgl = prm_tgl LIMIT 1;
  658. CASE WHEN @countPrensented = 0 THEN /* RECORD tabsensi tgl tersebut belum tersedia */
  659.  
  660.  
  661. /* Prosess as new inserting data */
  662.  
  663. IF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal AND prm_status_shift = 'HARI-1' THEN
  664. /* Insert as data pagi */
  665. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  666. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  667. INSERT INTO tabsensi (
  668. nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk,
  669. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  670. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  671. )
  672. VALUES (
  673. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu < @masukNormal,"OK","TERLAMBAT")),
  674. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  675. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  676. );
  677. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen PAGI") INTO @MESSAGE;
  678. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  679. /* END Insert as data pagi */
  680.  
  681.  
  682. ELSEIF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal AND prm_status_shift = 'HARI-1' THEN
  683. /* Insert as data breakout */
  684. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  685. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  686. INSERT INTO tabsensi (
  687. /* nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk, */
  688. nap, grup, tgl, breakout,
  689. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  690. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  691. )
  692. VALUES (
  693. prm_nrp, @grup, prm_tgl, prm_waktu,
  694. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  695. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  696. );
  697. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen BREAKOUT") INTO @MESSAGE;
  698. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  699. /* END Insert as data breakout */
  700.  
  701.  
  702.  
  703. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal AND prm_status_shift = 'HARI-1' THEN
  704. /* Insert as data breakin */
  705. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  706. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  707. INSERT INTO tabsensi (
  708. nap, grup, tgl, breakin,
  709. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  710. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  711. )
  712. VALUES (
  713. prm_nrp, @grup, prm_tgl, prm_waktu,
  714. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  715. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  716. );
  717. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen BREAKIN") INTO @MESSAGE;
  718. RETURN 12; /* RECORD tabsensi record baru absen breakin ditambahkan */
  719. /* END Insert as data breakin */
  720.  
  721.  
  722.  
  723. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal AND prm_status_shift = 'HARI-1' THEN
  724. /* Insert as data sore */
  725. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  726. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  727. INSERT INTO tabsensi (
  728. nap, grup, tgl, jam_pulang, id_data_pulang, jam_pulang_created_at, jam_pulang_by, ket_pulang,
  729. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  730. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  731. )
  732. VALUES (
  733. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu > @pulangNormal,"OK","CEPAT PULANG")),
  734. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  735. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  736. );
  737. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen SORE") INTO @MESSAGE;
  738. RETURN 12; /* RECORD tabsensi record baru absen SORE ditambahkan */
  739. /* END Insert as data sore */
  740.  
  741.  
  742. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal AND prm_status_shift = 'HARI-2' THEN
  743. /* Insert as data pagi */
  744. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  745. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  746. INSERT INTO tabsensi (
  747. nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk,
  748. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  749. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  750. )
  751. VALUES (
  752. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu < @masukNormal,"OK","TERLAMBAT")),
  753. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  754. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  755. );
  756. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen PAGI") INTO @MESSAGE;
  757. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  758. /* END Insert as data pagi */
  759.  
  760.  
  761. ELSEIF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal AND prm_status_shift = 'HARI-2' THEN
  762. /* Insert as data breakout */
  763. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  764. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  765. INSERT INTO tabsensi (
  766. /* nap, grup, tgl, jam_masuk, id_data_masuk, jam_masuk_created_at, jam_masuk_by, ket_masuk, */
  767. nap, grup, tgl, breakout,
  768. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  769. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  770. )
  771. VALUES (
  772. prm_nrp, @grup, prm_tgl, prm_waktu,
  773. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  774. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  775. );
  776. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 11, "Data ditambahkan: Absen BREAKOUT") INTO @MESSAGE;
  777. RETURN 11; /* RECORD tabsensi record baru absen PAGI ditambahkan */
  778. /* END Insert as data breakout */
  779.  
  780.  
  781.  
  782. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal AND prm_status_shift = 'HARI-2' THEN
  783. /* Insert as data breakin */
  784. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  785. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  786. INSERT INTO tabsensi (
  787. nap, grup, tgl, breakin,
  788. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  789. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  790. )
  791. VALUES (
  792. prm_nrp, @grup, prm_tgl, prm_waktu,
  793. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  794. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  795. );
  796. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen BREAKIN") INTO @MESSAGE;
  797. RETURN 12; /* RECORD tabsensi record baru absen breakin ditambahkan */
  798. /* END Insert as data breakin */
  799.  
  800.  
  801.  
  802. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal AND prm_status_shift = 'HARI-2' THEN
  803. /* Insert as data sore */
  804. SELECT ti.id_grup INTO @grup_id FROM tindentitas ti WHERE ti.nap = prm_nrp LIMIT 1;
  805. SELECT tg.grup INTO @grup FROM tgrup tg WHERE tg.id = @grup_id LIMIT 1;
  806. INSERT INTO tabsensi (
  807. nap, grup, tgl, jam_pulang, id_data_pulang, jam_pulang_created_at, jam_pulang_by, ket_pulang,
  808. ref_mulai_jam_masuk, ref_jam_masuk, ref_batas_absen_masuk, ref_jam_breakin, ref_batas_absen_breakin,
  809. ref_jam_breakout, ref_batas_absen_breakout, ref_mulai_absen_pulang, ref_jam_pulang, ref_batas_absen_pulang
  810. )
  811. VALUES (
  812. prm_nrp, @grup, prm_tgl, prm_waktu, prm_checktime_id, NOW(), "AUTOMATIC", (IF(prm_waktu > @pulangNormal,"OK","CEPAT PULANG")),
  813. @mulaiMasukNormal, @masukNormal, @batasMasukNormal, @breakinNormal, @batasBreakinNormal,
  814. @breakoutNormal, @batasBreakoutNormal, @mulaiPulangNormal, @pulangNormal, @batasPulangNormal
  815. );
  816. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 12, "Data ditambahkan: Absen SORE") INTO @MESSAGE;
  817. RETURN 12; /* RECORD tabsensi record baru absen SORE ditambahkan */
  818. /* END Insert as data sore */
  819.  
  820.  
  821.  
  822. ELSE
  823. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 13, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  824. RETURN 13; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  825. END IF;
  826. /* END: Prosess as new inserting data */
  827.  
  828.  
  829. ELSE
  830. /* Prosess as updating data */
  831.  
  832.  
  833.  
  834. IF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal AND prm_status_shift = 'HARI-1' THEN
  835. /* Update as data sore */
  836. IF @jamPulangTersimpan IS NULL OR @jamPulangTersimpan = '00:00:00' THEN
  837. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  838. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 19, "Data diperbaharui/Tambah sebagai data baru: Absen SORE") INTO @MESSAGE;
  839. RETURN 19;
  840. ELSEIF @jamPulangTersimpan < prm_waktu THEN
  841. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  842. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 20, "Data diperbaharui/Ganti data lama: Absen SORE") INTO @MESSAGE;
  843. RETURN 20;
  844. ELSEIF @jamPulangTersimpan > prm_waktu THEN
  845. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 21, "Data SKIPPED: Data tersimpan lebih akhir/besar: Absen SORE") INTO @MESSAGE;
  846. RETURN 21;
  847. ELSEIF @jamPulangTersimpan = prm_checktime_id THEN
  848. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 22, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen SORE") INTO @MESSAGE;
  849. RETURN 22;
  850. ELSE
  851. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 23, "Data SKIPPED: Alasan tidak diketahui.: Absen SORE") INTO @MESSAGE;
  852. RETURN 23;
  853. END IF;
  854. /* END Update as data sore */
  855.  
  856.  
  857.  
  858.  
  859. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal AND prm_status_shift = 'HARI-1' THEN
  860. /* Update as data breakin */
  861. IF @jamBreakinTersimpan IS NULL OR @jamBreakinTersimpan = '00:00:00' THEN
  862.  
  863. UPDATE tabsensi
  864. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  865. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKIN") INTO @MESSAGE;
  866. RETURN 14;
  867. ELSEIF @jamBreakinTersimpan < prm_waktu THEN
  868. UPDATE tabsensi
  869. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  870. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKIN") INTO @MESSAGE;
  871. RETURN 15;
  872. ELSEIF @jamBreakinTersimpan > prm_waktu THEN
  873. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih cepat: Absen BREAKIN") INTO @MESSAGE;
  874. RETURN 16;
  875. ELSEIF @jamBreakinTersimpan = prm_waktu THEN
  876. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKIN") INTO @MESSAGE;
  877. RETURN 17;
  878. ELSE
  879. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKIN") INTO @MESSAGE;
  880. RETURN 18;
  881. END IF;
  882. /* END Update as data breakin */
  883.  
  884.  
  885.  
  886.  
  887. ELSEIF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal AND prm_status_shift = 'HARI-1' THEN
  888. /* Update as data breakout */
  889. IF @jamBreakoutTersimpan IS NULL OR @jamBreakoutTersimpan = '00:00:00' THEN
  890.  
  891. UPDATE tabsensi
  892. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  893. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKOUT") INTO @MESSAGE;
  894. RETURN 14;
  895. ELSEIF @jamBreakoutTersimpan > prm_waktu THEN
  896. UPDATE tabsensi
  897. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  898. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKOUT") INTO @MESSAGE;
  899. RETURN 15;
  900. ELSEIF @jamBreakoutTersimpan < prm_waktu THEN
  901. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen BREAKOUT") INTO @MESSAGE;
  902. RETURN 16;
  903. ELSEIF @jamBreakoutTersimpan = prm_waktu THEN
  904. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKOUT") INTO @MESSAGE;
  905. RETURN 17;
  906. ELSE
  907. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKOUT") INTO @MESSAGE;
  908. RETURN 18;
  909. END IF;
  910. /* END Update as data breakout */
  911.  
  912.  
  913.  
  914. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal AND prm_status_shift = 'HARI-1' THEN
  915. /* Update as data pagi */
  916. IF @jamMasukTersimpan IS NULL OR @jamMasukTersimpan = '00:00:00' THEN
  917. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  918. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen PAGI") INTO @MESSAGE;
  919. RETURN 14;
  920. ELSEIF @jamMasukTersimpan > prm_waktu THEN
  921. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  922. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen PAGI") INTO @MESSAGE;
  923. RETURN 15;
  924. ELSEIF @jamMasukTersimpan < prm_waktu THEN
  925. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen PAGI") INTO @MESSAGE;
  926. RETURN 16;
  927. ELSEIF @idJamMasukTersimpan = prm_checktime_id THEN
  928. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen PAGI") INTO @MESSAGE;
  929. RETURN 17;
  930. ELSE
  931. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen PAGI") INTO @MESSAGE;
  932. RETURN 18;
  933. END IF;
  934. /* END Update as data pagi */
  935.  
  936.  
  937.  
  938. ELSEIF prm_waktu > @mulaiPulangNormal AND prm_waktu < @batasPulangNormal AND prm_status_shift = 'HARI-2' THEN
  939. /* Update as data sore */
  940. IF @jamPulangTersimpan IS NULL OR @jamPulangTersimpan = '00:00:00' THEN
  941. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  942. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 19, "Data diperbaharui/Tambah sebagai data baru: Absen SORE") INTO @MESSAGE;
  943. RETURN 19;
  944. ELSEIF @jamPulangTersimpan < prm_waktu THEN
  945. SELECT synch3_f_update_jam_pulang(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @pulangNormal, prm_run_mode) INTO @INFO;
  946. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 20, "Data diperbaharui/Ganti data lama: Absen SORE") INTO @MESSAGE;
  947. RETURN 20;
  948. ELSEIF @jamPulangTersimpan > prm_waktu THEN
  949. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 21, "Data SKIPPED: Data tersimpan lebih akhir/besar: Absen SORE") INTO @MESSAGE;
  950. RETURN 21;
  951. ELSEIF @jamPulangTersimpan = prm_checktime_id THEN
  952. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 22, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen SORE") INTO @MESSAGE;
  953. RETURN 22;
  954. ELSE
  955. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 23, "Data SKIPPED: Alasan tidak diketahui.: Absen SORE") INTO @MESSAGE;
  956. RETURN 23;
  957. END IF;
  958. /* END Update as data sore */
  959.  
  960.  
  961.  
  962.  
  963. ELSEIF prm_waktu > @breakinNormal AND prm_waktu < @batasBreakinNormal AND prm_status_shift = 'HARI-2' THEN
  964. /* Update as data breakin */
  965. IF @jamBreakinTersimpan IS NULL OR @jamBreakinTersimpan = '00:00:00' THEN
  966.  
  967. UPDATE tabsensi
  968. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  969. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKIN") INTO @MESSAGE;
  970. RETURN 14;
  971. ELSEIF @jamBreakinTersimpan < prm_waktu THEN
  972. UPDATE tabsensi
  973. SET breakin = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  974. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKIN") INTO @MESSAGE;
  975. RETURN 15;
  976. ELSEIF @jamBreakinTersimpan > prm_waktu THEN
  977. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih cepat: Absen BREAKIN") INTO @MESSAGE;
  978. RETURN 16;
  979. ELSEIF @jamBreakinTersimpan = prm_waktu THEN
  980. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKIN") INTO @MESSAGE;
  981. RETURN 17;
  982. ELSE
  983. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKIN") INTO @MESSAGE;
  984. RETURN 18;
  985. END IF;
  986. /* END Update as data breakin */
  987.  
  988.  
  989.  
  990.  
  991. ELSEIF prm_waktu > @breakoutNormal AND prm_waktu < @batasBreakoutNormal AND prm_status_shift = 'HARI-2' THEN
  992. /* Update as data breakout */
  993. IF @jamBreakoutTersimpan IS NULL OR @jamBreakoutTersimpan = '00:00:00' THEN
  994.  
  995. UPDATE tabsensi
  996. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  997. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen BREAKOUT") INTO @MESSAGE;
  998. RETURN 14;
  999. ELSEIF @jamBreakoutTersimpan > prm_waktu THEN
  1000. UPDATE tabsensi
  1001. SET breakout = prm_waktu WHERE nap = prm_nrp AND tgl = prm_tgl;
  1002. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen BREAKOUT") INTO @MESSAGE;
  1003. RETURN 15;
  1004. ELSEIF @jamBreakoutTersimpan < prm_waktu THEN
  1005. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen BREAKOUT") INTO @MESSAGE;
  1006. RETURN 16;
  1007. ELSEIF @jamBreakoutTersimpan = prm_waktu THEN
  1008. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen BREAKOUT") INTO @MESSAGE;
  1009. RETURN 17;
  1010. ELSE
  1011. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen BREAKOUT") INTO @MESSAGE;
  1012. RETURN 18;
  1013. END IF;
  1014. /* END Update as data breakout */
  1015.  
  1016.  
  1017.  
  1018. ELSEIF prm_waktu > @mulaiMasukNormal AND prm_waktu < @batasMasukNormal AND prm_status_shift = 'HARI-2' THEN
  1019. /* Update as data pagi */
  1020. IF @jamMasukTersimpan IS NULL OR @jamMasukTersimpan = '00:00:00' THEN
  1021. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  1022. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 14, "Data diperbaharui/Tambah sebagai data baru: Absen PAGI") INTO @MESSAGE;
  1023. RETURN 14;
  1024. ELSEIF @jamMasukTersimpan > prm_waktu THEN
  1025. SELECT synch3_f_update_jam_masuk(prm_checktime_id, prm_nrp, prm_tgl, prm_waktu, prm_checktype, @masukNormal, prm_run_mode) INTO @INFO;
  1026. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 15, "Data diperbaharui/Ganti data lama: Absen PAGI") INTO @MESSAGE;
  1027. RETURN 15;
  1028. ELSEIF @jamMasukTersimpan < prm_waktu THEN
  1029. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 16, "Data SKIPPED: Data tersimpan lebih awal/kecil: Absen PAGI") INTO @MESSAGE;
  1030. RETURN 16;
  1031. ELSEIF @idJamMasukTersimpan = prm_checktime_id THEN
  1032. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 17, "Data SKIPPED: Data telah disimpan sebelumnya.: Absen PAGI") INTO @MESSAGE;
  1033. RETURN 17;
  1034. ELSE
  1035. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 18, "Data SKIPPED: Alasan tidak diketahui.: Absen PAGI") INTO @MESSAGE;
  1036. RETURN 18;
  1037. END IF;
  1038. /* END Update as data pagi */
  1039.  
  1040.  
  1041.  
  1042. ELSE
  1043. SELECT synch3_f_update_tb_synch_status(prm_checktime_id, 24, "SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE") INTO @MESSAGE;
  1044. RETURN 24; /* SKIPPED: Data absen tidak memenuhi batas absen PAGI dan SORE */
  1045. END IF;
  1046. /* END: Prosess as updating data */
  1047.  
  1048. END CASE;
  1049.  
  1050. END//
  1051. DELIMITER ;
  1052.  
  1053.  
  1054. DELIMITER //
  1055. CREATE DEFINER=`root`@`localhost` FUNCTION `tdaylyshiftschedule_f_filler`(`prm_nap` VARCHAR(10), `prm_tgl` DATE, `prm_shift` VARCHAR(3)
  1056. ) RETURNS varchar(100) CHARSET utf8
  1057. BEGIN
  1058. SET @shift_kode = REPLACE(UPPER(prm_shift),' ','');
  1059. SET @shift = 20;
  1060. SET @hari = DAYOFWEEK(prm_tgl);
  1061. SET @response = "UNKNOWN:::Error";
  1062. CASE
  1063. WHEN @shift_kode = "P" THEN SET @shift = 1;
  1064. WHEN @shift_kode = "S" THEN SET @shift = 2;
  1065. WHEN @shift_kode = "M" THEN SET @shift = 3;
  1066. WHEN @shift_kode = "RK" THEN SET @shift = 4;
  1067. WHEN @shift_kode = "D1" THEN SET @shift = 5;
  1068. WHEN @shift_kode = "D2" THEN SET @shift = 6;
  1069. WHEN @shift_kode = "D3" THEN SET @shift = 7;
  1070. WHEN @shift_kode = "D4" THEN SET @shift = 8;
  1071. WHEN @shift_kode = "D5" THEN SET @shift = 9;
  1072. WHEN @shift_kode = "S1" THEN SET @shift = 10;
  1073. WHEN @shift_kode = "S2" THEN SET @shift = 11;
  1074. WHEN @shift_kode = "L" THEN SET @shift = 13;
  1075. ELSE SET @shift = 20;
  1076. END CASE;
  1077. SELECT COUNT(ti.shift) INTO @isIdentitasExist FROM tindentitas ti WHERE ti.nap = prm_nap LIMIT 1;
  1078.  
  1079. IF @isIdentitasExist = 1 THEN
  1080. SELECT ti.shift INTO @isScheduledShift FROM tindentitas ti WHERE ti.nap = prm_nap LIMIT 1;
  1081. ELSE
  1082. SELECT 0 INTO @isScheduledShift;
  1083. END IF;
  1084.  
  1085.  
  1086. SELECT COUNT(td.id) INTO @recordExist FROM tdaylyshiftschedule td WHERE td.nap = prm_nap AND DATE(td.tgl) = DATE(prm_tgl) LIMIT 1;
  1087. IF (@shift=13) THEN
  1088. SELECT COUNT(tj.id), tj.mulai_absen_masuk, tj.jam_masuk, tj.batas_absen_masuk, tj.jam_breakin, tj.batas_absen_breakin,
  1089. tj.jam_breakout, tj.batas_absen_breakout, tj.mulai_absen_pulang, tj.jam_pulang, tj.batas_absen_pulang, tj.stay_day, 1
  1090. INTO @availableDataShift, @mulai_absen_masuk, @jam_masuk, @batas_absen_masuk, @jam_breakin, @batas_absen_breakin,
  1091. @jam_breakout, @batas_absen_breakout, @mulai_absen_pulang, @jam_pulang, @batas_absen_pulang, @stay_day, @day_off
  1092. FROM tjamkantor tj
  1093. WHERE tj.shift = @shift AND tj.hari = @hari LIMIT 1;
  1094.  
  1095. ELSEIF (@shift>=1 AND @shift<=11) THEN
  1096. SELECT COUNT(tj.id), tj.mulai_absen_masuk, tj.jam_masuk, tj.batas_absen_masuk, tj.jam_breakin, tj.batas_absen_breakin,
  1097. tj.jam_breakout, tj.batas_absen_breakout, tj.mulai_absen_pulang, tj.jam_pulang, tj.batas_absen_pulang, tj.stay_day, 0
  1098. INTO @availableDataShift, @mulai_absen_masuk, @jam_masuk, @batas_absen_masuk, @jam_breakin, @batas_absen_breakin,
  1099. @jam_breakout, @batas_absen_breakout, @mulai_absen_pulang, @jam_pulang, @batas_absen_pulang, @stay_day, @day_off
  1100. FROM tjamkantor tj
  1101. WHERE tj.shift = @shift AND tj.hari = @hari LIMIT 1;
  1102. END IF;
  1103.  
  1104. IF (@isIdentitasExist != 1) THEN
  1105. SET @response = CONCAT("ERROR:",prm_nap,":FALSE:NAP Pegawai tidak diketahui!");
  1106. ELSEIF (@isScheduledShift != 1) THEN
  1107. SET @response = CONCAT("ERROR:",prm_nap,":FALSE:Pegawai tidak terdaftar shift!");
  1108. ELSEIF (@shift=14) THEN
  1109. SET @response = CONCAT("ERROR:",@shift_kode,":FALSE:Kode shift tidak dikenal!");
  1110.  
  1111. ELSEIF (@availableDataShift != 1) THEN
  1112. SET @response = CONCAT("ERROR:",@shift_kode,"[",prm_tgl,"]:FALSE:Jam kantor shift tidak tersedia!",@shift,@availableDataShift);
  1113.  
  1114. ELSE
  1115. CASE WHEN @recordExist = 0 THEN
  1116. INSERT INTO `tdaylyshiftschedule`
  1117. (`nap`, `tgl`, kode_shift,
  1118. `mulai_absen_masuk`, `jam_masuk`, `batas_absen_masuk`,
  1119. `jam_breakin`, `batas_absen_breakin`,
  1120. `jam_breakout`, `batas_absen_breakout`,
  1121. `mulai_absen_pulang`, `jam_pulang`, `batas_absen_pulang`,
  1122. `day_off`, stay_day)
  1123.  
  1124. VALUES (prm_nap, DATE(prm_tgl), @shift,
  1125. @mulai_absen_masuk, @jam_masuk, @batas_absen_masuk,
  1126. @jam_breakin, @batas_absen_breakin,
  1127. @jam_breakout, @batas_absen_breakout,
  1128. @mulai_absen_pulang, @jam_pulang, @batas_absen_pulang,
  1129. @day_off, @stay_day);
  1130.  
  1131. SET @response = CONCAT("INSERT:",@shift_kode,":TRUE:OK");
  1132.  
  1133. ELSE
  1134. UPDATE tdaylyshiftschedule th
  1135. SET kode_shift = @shift,
  1136. mulai_absen_masuk = @mulai_absen_masuk, jam_masuk = @jam_masuk, batas_absen_masuk = @batas_absen_masuk,
  1137. jam_breakin = @jam_breakin, batas_absen_breakin = @batas_absen_breakin,
  1138. jam_breakout = @jam_breakout, batas_absen_breakout = @batas_absen_breakout,
  1139. mulai_absen_pulang = @mulai_absen_pulang, jam_pulang = @jam_pulang, batas_absen_pulang = @batas_absen_pulang,
  1140. stay_day = @stay_day, day_off = @day_off
  1141. WHERE nap = prm_nap AND DATE(tgl) = DATE(prm_tgl);
  1142. SET @response = CONCAT("UPDATE:",@shift_kode,":TRUE:OK",@isScheduledShift);
  1143. END CASE;
  1144. END IF;
  1145.  
  1146. RETURN @response;
  1147. END//
  1148. DELIMITER ;
  1149.  
  1150.  
  1151.  
  1152.  
  1153. DELIMITER //
  1154. CREATE DEFINER=`root`@`localhost` PROCEDURE `synch3_p_push_manual_all_employee`(IN `prm_tanggal` DATE, IN `prm_bulanan` TINYINT(1))
  1155. BEGIN
  1156.  
  1157. SELECT synch3_f_insert_or_update(V.checktime_id, V.nrp, V.tanggal, V.waktu, V.checktype, "MANUAL-ALL-EMP") AS HASIL
  1158. FROM (
  1159.  
  1160. SELECT `cio`.`checktime_id`,
  1161. `cio`.`NRP` AS `nrp`,
  1162. CAST(`cio`.`checktime` as date) AS `tanggal`,
  1163. CAST(`cio`.`checktime` as time) AS `waktu`,
  1164. `cio`.`checktype`
  1165. FROM `adms_retriever`.`checkinout` `cio`
  1166. WHERE `cio`.NRP IN (
  1167. SELECT ti.nap FROM tindentitas ti WHERE ti.status = 1
  1168. )
  1169. AND ((YEAR(cio.checktime)=YEAR(prm_tanggal) AND MONTH(cio.checktime)=MONTH(prm_tanggal) AND prm_bulanan)
  1170. OR (DATE(cio.checktime) = DATE(prm_tanggal)))
  1171. ORDER BY `cio`.`checktime_id`
  1172.  
  1173. ) V;
  1174.  
  1175.  
  1176.  
  1177.  
  1178.  
  1179. END//
  1180. DELIMITER ;
  1181.  
  1182.  
  1183. UPDATE `trulerekap` SET `value`= 0 WHERE `id`=2;
  1184. TRUNCATE `tabsensi`;
  1185. CALL `synch3_p_push_manual_all_employee`('2019-03-01', '1');
  1186. CALL `synch3_p_push_manual_all_employee`('2019-04-01', '1');
  1187. CALL `synch3_p_push_manual_all_employee`('2019-05-01', '1');
  1188.  
  1189.  
  1190. /* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 27 MEI 2019 */
  1191. /* [1] PENAMBAHAN SISTEM PENDETEKSI ZONA WAKTU RUSAK */
  1192. /* [2] CONVERT ZONA WAKTU GMT+3 KE GMT+8 */
  1193. /* [3] EVENT PEDETEKSI ZONWA WAKTU RUSAK DAN CONVERT ZONA WAKTU GMT+3 KE GMT+8 */
  1194.  
  1195.  
  1196. DROP FUNCTION IF EXISTS `func_repair_time_zone_gmt3_to_gmt8`;
  1197. DROP PROCEDURE IF EXISTS `proc_convert_time_zone_gmt3_to_gmt8`;
  1198. DROP EVENT IF EXISTS `repair_and_convert_timezone_gmt3_to_gmt8`;
  1199.  
  1200.  
  1201.  
  1202. DELIMITER //
  1203. CREATE DEFINER=`root`@`localhost` FUNCTION `func_repair_time_zone_gmt3_to_gmt8`() RETURNS varchar(50) CHARSET latin1
  1204. MODIFIES SQL DATA
  1205. BEGIN
  1206.  
  1207. /*NB: procedure ini hanya bisa di terapkan oleh kantor yang pegawai jam kantor REGULER*/
  1208.  
  1209. /* Function ini digunakan terlebig dahulu sebelum menggunakan procedure `proc_convert_time_zone_gmt3_to_gmt8`*/
  1210.  
  1211. /*
  1212. Mengecek tanggal brp saja yang memiliki absen di jam tidak wajar (jam 1,2,3,4,5)
  1213. */
  1214. SELECT DISTINCT DATE(checktime) INTO @tgl_target FROM adms_retriever.checkinout where HOUR(checktime) IN (1,2,3,4,5) AND serverid != "TIMEZONE-ERR" LIMIT 1;
  1215.  
  1216.  
  1217. /*
  1218. Menghitung Pegawai yang memiliki absen di jam tidak wajar (jam 1,2,3,4,5)
  1219. */
  1220. SELECT COUNT(NRP) INTO @total_emply FROM adms_retriever.checkinout WHERE HOUR(checktime) IN (1,2,3,4,5) AND DATE(checktime) = @tgl_target;
  1221.  
  1222.  
  1223. /*
  1224. Kondisi TRUE: dimana pegawainya ada diatas persyaratan fungsi
  1225. */
  1226. IF @total_emply > 4 THEN
  1227.  
  1228. DELETE FROM tabsensi where tgl = @tgl_target;
  1229.  
  1230. UPDATE adms_retriever.checkinout SET serverid = 'TIMEZONE-ERR' WHERE DATE(checktime) = @tgl_target;
  1231.  
  1232. END IF;
  1233.  
  1234. SELECT COUNT(DISTINCT DATE(checktime)) INTO @sisa_target FROM adms_retriever.checkinout where HOUR(checktime) IN (1,2,3,4,5) AND serverid != "TIMEZONE-ERR";
  1235.  
  1236. RETURN CONCAT("Tersisa ", @sisa_target, " Data");
  1237.  
  1238. END//
  1239. DELIMITER ;
  1240.  
  1241.  
  1242. -- Dumping structure for procedure e-presensi.proc_convert_time_zone_gmt3_to_gmt8
  1243. DELIMITER //
  1244. CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_convert_time_zone_gmt3_to_gmt8`()
  1245. BEGIN
  1246.  
  1247. /* procedure ini digunakan setelah semau data di proses oleh function `func_repair_time_zone_gmt3_to_gmt8`*/
  1248.  
  1249. SELECT ch.id, DATE(ch.checktime), HOUR(ch.checktime) INTO @ID, @tgl, @jam FROM adms_retriever.checkinout ch WHERE ch.serverid = 'TIMEZONE-ERR' LIMIT 1;
  1250.  
  1251. IF @jam = 1 THEN SET @prm_jam_real = 6;
  1252. ELSEIF @jam = 2 THEN SET @prm_jam_real = 7;
  1253. ELSEIF @jam = 3 THEN SET @prm_jam_real = 8;
  1254. ELSEIF @jam = 4 THEN SET @prm_jam_real = 9;
  1255. ELSEIF @jam = 5 THEN SET @prm_jam_real = 10;
  1256. ELSEIF @jam = 6 THEN SET @prm_jam_real = 11;
  1257. ELSEIF @jam = 7 THEN SET @prm_jam_real = 12;
  1258. ELSEIF @jam = 8 THEN SET @prm_jam_real = 13;
  1259. ELSEIF @jam = 9 THEN SET @prm_jam_real = 14;
  1260. ELSEIF @jam = 10 THEN SET @prm_jam_real = 15;
  1261. ELSEIF @jam = 11 THEN SET @prm_jam_real = 16;
  1262. ELSEIF @jam = 12 THEN SET @prm_jam_real = 17;
  1263. ELSEIF @jam = 13 THEN SET @prm_jam_real = 18;
  1264. ELSEIF @jam = 14 THEN SET @prm_jam_real = 19;
  1265. ELSEIF @jam = 15 THEN SET @prm_jam_real = 20;
  1266. ELSEIF @jam = 16 THEN SET @prm_jam_real = 21;
  1267. ELSEIF @jam = 17 THEN SET @prm_jam_real = 22;
  1268. END IF;
  1269.  
  1270.  
  1271. UPDATE adms_retriever.checkinout
  1272. SET adms_retriever.checkinout.checktime = DATE_ADD(adms_retriever.checkinout.checktime, INTERVAL (@prm_jam_real - HOUR(adms_retriever.checkinout.checktime)) HOUR),
  1273. adms_retriever.checkinout.serverid = 'SER-ADMS-02'
  1274. WHERE id = @ID;
  1275.  
  1276. CALL `synch3_p_push_manual_all_employee`(@tgl, '0');
  1277.  
  1278. END//
  1279. DELIMITER ;
  1280.  
  1281. DELIMITER //
  1282. CREATE DEFINER=`root`@`localhost` EVENT `repair_and_convert_timezone_gmt3_to_gmt8` ON SCHEDULE EVERY 20 SECOND STARTS '2019-05-27 23:36:53' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
  1283. SELECT `func_repair_time_zone_gmt3_to_gmt8`();
  1284. CALL `proc_convert_time_zone_gmt3_to_gmt8`();
  1285. END//
  1286. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!