--
-- Host: localhost    Database: all-in-one-kantin
-- ------------------------------------------------------


--
-- Table structure for table `data_bpr_master`
--

CREATE TABLE `data_bpr_master` (
  `server_id` char(3) NOT NULL,
  `server_ip` varchar(20) NOT NULL,
  `server_nama` varchar(100) NOT NULL,
  `server_kota` varchar(30) NOT NULL,
  PRIMARY KEY (`server_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=55;

--
-- Dumping data for table `data_bpr_master`
--

INSERT INTO `data_bpr_master` VALUES ('01','10.201.8.2  ','PD. BPR BKK BANJARHARJO','Kab. Brebes'),('02','10.207.7.1  ','PD. BPR BKK KAB. SRAGEN','Kab. Sragen'),('03','10.5.50.2   ','PD. BPR BKK PURWOKERTO','Kab. Banyumas'),('04','10.5.60.2   ','PD. BPR BKK PEKALONGAN KOTA','Kota Pekalongan'),('05','10.5.70.2   ','PT. BPR TARUNA ADIDAYA SENTOSA','Kab. Kudus'),('06','10.5.80.2   ','PD. BPR BKK KAB. PEKALONGAN','Kab. Pekalongan'),('07','10.5.90.2   ','PD. BPR BKK KEBUMEN','Kab. Kebumen'),('08','10.5.100.2  ','PT. BPR LIMPUNGARTA UTAMA','Kab. Batang'),('09','10.5.102.2  ','PT. BPR MENTARI TERANG','Kab. Tuban'),('10','10.5.101.2  ','PT. BPR SINAR GARUDA PRIMA','Kab. Magelang'),('11','10.5.103 2  ','PT. BPR WIROSARI IJO','Kab. Grobogan'),('12','10.5.104.2  ','PD BPR BKK BLORA','Kab. Blora'),('13','10.5.107.2  ','KOSPIN SEKARTAMA','Kab. Kendal'),('14','10.5.105.2  ','PD. BPR BKK JEPARA KOTA','Kab. Jepara'),('15','10.5.106.2  ','PT. BPR KUSUMA SUMBING','Kab. Temanggung'),('16','10.5.16.2   ','PD. BPR BKK GROGOL','Kab. Sukoharjo'),('17','10.5.17.2   ','PT. BPR WAHANA SENTRA ARTHA','Kab. Majalengka'),('18','10.5.18.2   ','PT. BPR ARTHA GUNA MANDIRI','Kab. Bandung'),('19','10.5.19.2   ','PT. BPR CITRA ARTHA SEDANA','Kab. Bekasi'),('20','10.5.20.2   ','PT. BPR MAKMUR ARTHA SEDAYA','Kota Tangerang'),('21','10.5.21.2   ','PT. BPR MITRA DANA MADANI','Kota Medan'),('22','10.5.22.2   ','PT. BPR MITRA RAKYAT RIAU','Kota Pekanbaru'),('23','10.5.23.2','PT. BPR WAHANA ARTHA MAJU','Kab. Demak'),('24','10.5.24.2','PT. BPR SEJAHTERA ARTHA SEMBADA','Kota Pekalongan'),('25','10.5.25.2','PT. BPR CITRA ARTHA NUSA','Kab. Kendal'),('26','10.5.26.2','PT. BPR ARTHA DAYA','Kota Surakarta'),('27','10.5.27.2','PT. BPR SWADHAR ARTHA NUSA','Kota Yogyakarta'),('28','10.5.28.2','PT. BPR SWADHARMA BANGUN ARTHA','Kab. Bantul'),('29','10.5.29.2','PD. BPR.BKK PURBALINGGA','Kab.Purbalingga'),('30','10.5.30.2','PD. BPR Bank Pasar Kab. Sukoharjo','Kab. Sukoharjo'),('31','10.5.31.2','PT. BPR DP Taspen','Kab. Bekasi'),('32','10.5.32.2','PT. BPR Artha Tanah Mas','Semarang'),('33','10.5.33.2','PT. BPR Gunung Kinibalu','Semarang'),('34','10.5.34.2','PT. BPR Puspa Kencana','Brebes'),('35','10.5.35.2','PT. BPR Artha Puspa Mega','Tegal'),('36','10.5.36.2','PT. BPR Binalanggeng Mulia','Surakarta'),('37','10.5.37.2','PT. BPR Pedungan','Bali'),('39','10.206.1.39','PD. BANK Pekalongan','Pekalongan'),('40','10.206.1.40','KSU Sabhara','Klaten'),('41','10.206.1.41','PD BPR BKK Kab.Tegal','Tegal'),('42','10.5.42.2','PD. BPR Bank Purwa Artha','Purwodadi'),('43','10.5.43.2','PT. BPR Usaha Rakyat','Bekasi'),('45','10.5.45.2','PD. BPR BKK Kota Tegal','Tegal'),('46','10.5.46.2','PT BPR DP Taspen Jateng','Purwokerto'),('47','10.5.47.2','PT. BPR Arthama Cerah','Sragen');

--
-- Table structure for table `data_cluster_client`
--

CREATE TABLE `data_cluster_client` (
  `client_id` int(5) NOT NULL AUTO_INCREMENT,
  `client_kluster` char(3) NOT NULL,
  `client_bpr` char(3) NOT NULL,
  PRIMARY KEY (`client_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=304 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=11 ROW_FORMAT=FIXED;

--
-- Dumping data for table `data_cluster_client`
--

INSERT INTO `data_cluster_client` VALUES (1,'03','17'),(2,'03','18'),(3,'03','19'),(4,'03','20'),(5,'03','21'),(6,'03','22'),(7,'03','27'),(8,'03','26'),(9,'03','25'),(10,'03','24'),(11,'03','23'),(12,'03','28'),(36,'02','08'),(37,'02','09'),(38,'02','10'),(39,'02','13'),(40,'07','30'),(41,'08','31'),(42,'06','05'),(84,'09','11'),(85,'09','15'),(86,'09','30'),(87,'09','31'),(204,'04','01'),(205,'04','02'),(206,'04','03'),(244,'00','01'),(245,'00','02'),(246,'00','03'),(247,'00','04'),(248,'00','05'),(249,'00','06'),(250,'00','07'),(251,'00','08'),(252,'00','09'),(253,'00','10'),(254,'00','11'),(255,'00','12'),(256,'00','13'),(257,'00','14'),(258,'00','15'),(259,'00','16'),(260,'00','17'),(261,'00','18'),(262,'00','19'),(263,'00','20'),(264,'00','21'),(265,'00','22'),(266,'00','27'),(267,'00','26'),(268,'00','25'),(269,'00','24'),(270,'00','23'),(271,'00','28'),(272,'00','29'),(273,'00','30'),(274,'00','31'),(275,'00','32'),(276,'00','33'),(277,'00','34'),(278,'00','35'),(279,'00','36'),(280,'00','37'),(281,'00','39'),(282,'00','40'),(283,'00','41'),(284,'00','42'),(285,'00','43'),(286,'00','45'),(287,'00','46'),(288,'00','47'),(289,'01','01'),(290,'01','02'),(291,'01','03'),(292,'01','04'),(293,'01','06'),(294,'01','07'),(295,'01','12'),(296,'01','14'),(297,'01','16'),(298,'01','29'),(299,'01','41'),(300,'01','45'),(301,'05','30'),(302,'05','34'),(303,'05','42');

--
-- Table structure for table `data_cluster_master`
--

CREATE TABLE `data_cluster_master` (
  `cluster_id` int(5) NOT NULL AUTO_INCREMENT,
  `cluster_kode` char(3) NOT NULL,
  `cluster_nama` varchar(100) NOT NULL,
  `cluster_keterangan` varchar(100) NOT NULL,
  `cluster_ip` char(20) NOT NULL,
  PRIMARY KEY (`cluster_id`) USING BTREE,
  UNIQUE KEY `cluster_kode_2` (`cluster_kode`) USING BTREE,
  KEY `cluster_kode` (`cluster_kode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=2730 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `data_cluster_master`
--

INSERT INTO `data_cluster_master` VALUES (1,'01','BPR BKK','Cluster Untuk BPR BKK',''),(2,'02','Krisenko Group','Cluster Untuk Group Krisenko',''),(3,'03','ANS Group','Cluster Untuk Group ANS',''),(4,'04','Lain-lain','Lain - lain',''),(10,'00','MSO','ALL Client',''),(11,'05','Bank Pasar','Cluster untuk bank-bank pasar','');

--
-- Table structure for table `data_kantin_master`
--

CREATE TABLE `data_kantin_master` (
  `kantin_kode` varchar(2) NOT NULL,
  `kantin_nama` varchar(50) NOT NULL,
  `kantin_rekening` varchar(20) NOT NULL,
  `kantin_transid` varchar(3) NOT NULL,
  `kantin_transket` varchar(50) NOT NULL,
  `kantin_pin` tinyint(1) NOT NULL DEFAULT '0',
  `kantin_max_trans` double(20,0) NOT NULL,
  `kantin_max_pembeli` double(20,0) NOT NULL,
  `kantin_status` tinyint(1) NOT NULL DEFAULT '1',
  `kantin_reg_date` datetime NOT NULL,
  `kantin_reg_alias` varchar(30) NOT NULL,
  `kantin_upd_date` datetime NOT NULL,
  `kantin_upd_alias` varchar(30) NOT NULL,
  PRIMARY KEY (`kantin_kode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;

--
-- Dumping data for table `data_kantin_master`
--

INSERT INTO `data_kantin_master` VALUES ('01','Koperasi Pelajar Putra','01.101.00306','KPA','Bayar Kopel Putra dari ',0,1000000,1000000,1,'2021-06-16 05:05:25','admin','2021-12-08 08:34:52','admin'),('02','Koperasi Pelajar Putri','01.101.00307','KPI','Bayar Kopel Putri dari ',0,1000000,1000000,1,'2021-06-16 05:38:44','admin','2021-12-08 08:35:12','admin'),('03','Kantin Putra','01.101.00308','AMP','Bayar Kantin Putra dari ',0,10000000,10000000,1,'2021-06-18 11:06:04','admin','2021-12-08 08:35:27','admin'),('04','Kantin Putri','01.101.00309','AMI','Bayar Kantin Putri dari',0,10000000,10000000,1,'2021-06-26 15:58:34','admin','2021-12-08 08:35:36','admin');

--
-- Table structure for table `data_menu_master`
--

CREATE TABLE `data_menu_master` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_kode` varchar(10) NOT NULL,
  `menu_parent` varchar(10) NOT NULL,
  `menu_label` varchar(60) NOT NULL,
  `menu_status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=73;

--
-- Dumping data for table `data_menu_master`
--

INSERT INTO `data_menu_master` VALUES (18,'mn0203','mn02','0203. Log Login',1),(19,'mn0202','mn02','0202. Setup Pengguna',1),(20,'mn0201','mn02','0201. Setup Group',1),(21,'mn02','mn02','02. Operasional',1),(22,'mn0119','mn01','0119. Keluar',1),(28,'mn0101','mn01','0101. Ganti Password',1),(29,'mn01','mn01','01. File',1),(36,'mn0204','mn02','0204. Log Password',1),(37,'mn0209','mn02','0209. -',1),(38,'mn0211','mn02','0211. Transaksi',1),(39,'mn0212','mn02','0212. Browse Transaksi',1),(40,'mn0213','mn02','0213. Laporang Tansaksi',1),(41,'mn0219','mn02','0219. -',1),(42,'mn0221','mn02','0221. Transaksi Madamat',1),(43,'mn0205','mn02','0205. Set APP',1),(44,'mn0206','mn02','0206. Setup Kantin',1);

--
-- Table structure for table `data_pengguna_alias`
--

CREATE TABLE `data_pengguna_alias` (
  `user_id` char(3) NOT NULL,
  `user_alias` varchar(15) NOT NULL,
  `user_group` int(2) NOT NULL,
  `user_jenis` char(2) NOT NULL DEFAULT '1',
  `user_kantin` char(3) NOT NULL,
  `user_bpr` char(3) NOT NULL,
  `user_pwd` varchar(60) NOT NULL,
  `user_nama` varchar(50) NOT NULL,
  `user_keterangan` varchar(200) NOT NULL,
  `user_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0: non aktif\r\n1: aktif\r\n2: blokir\r\n3: expired\r\n9: delete',
  `user_reg_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_reg_ip` varchar(15) NOT NULL,
  `user_reg_alias` varchar(10) NOT NULL,
  `user_upd_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_upd_ip` varchar(50) NOT NULL,
  `user_upd_alias` varchar(10) NOT NULL,
  `user_exp_date` date NOT NULL DEFAULT '2099-12-31',
  `user_session` varchar(20) NOT NULL,
  `user_last_login` datetime NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE KEY `user_alias` (`user_alias`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=5461 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `data_pengguna_alias`
--

INSERT INTO `data_pengguna_alias` VALUES ('001','admin',1,'1','01','','ce7c383e0f03e084b74922969962c2fa','Administator Sistem','PT. MSO Purwokerto',1,'2015-04-30 10:54:44','192.168.56.1','mso','2015-07-23 14:46:16','192.168.10.12','mso','2015-07-30','SES2112080004','2021-12-08 08:43:09'),('002','dian',1,'1','01','','e807f1fcf82d132f9bb018ca6738a19f','Admin Keuangan','MBS Al-Amin',1,'2021-07-08 11:36:11','192.168.116.1','admin','2021-07-08 11:42:37','192.168.89.253','admin','2021-10-08','SES2107080007','2021-07-08 11:44:04'),('003','angga',2,'1','01','','80bd868f554e409bb6056f22d0da5784','angga','',1,'2021-12-08 09:06:26','192.168.116.1','admin','0000-00-00 00:00:00','','','2022-03-08','','0000-00-00 00:00:00'),('004','galih',2,'1','02','','a5bb48745c0ddeac58aac76f89c1d94a','galih','',1,'2021-12-08 09:06:54','192.168.116.1','admin','0000-00-00 00:00:00','','','2022-03-08','','0000-00-00 00:00:00'),('005','zen',2,'1','03','','d8ef620ad63adc48148c904faefcdec6','Zen','',1,'2021-12-08 09:07:29','192.168.116.1','admin','0000-00-00 00:00:00','','','2022-03-08','','0000-00-00 00:00:00'),('006','umma',2,'1','04','','e15a77fb1fde827ff5124876fcb4868e','Umma','',1,'2021-12-08 09:07:55','192.168.116.1','admin','0000-00-00 00:00:00','','','2022-03-08','SES2112080006','2021-12-08 09:12:48');

--
-- Table structure for table `data_pengguna_group`
--

CREATE TABLE `data_pengguna_group` (
  `group_kode` varchar(1) NOT NULL,
  `group_nama` varchar(30) NOT NULL,
  `group_akses` varchar(1) NOT NULL,
  `group_menu` text NOT NULL,
  `group_keterangan` text NOT NULL,
  `group_reg_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `group_reg_ip` varchar(15) NOT NULL,
  `group_reg_alias` varchar(10) NOT NULL,
  `group_upd_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `group_upd_ip` varchar(15) NOT NULL,
  `group_upd_alias` varchar(10) NOT NULL,
  PRIMARY KEY (`group_kode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=75;

--
-- Dumping data for table `data_pengguna_group`
--

INSERT INTO `data_pengguna_group` VALUES ('1','Administrator','1','','ALL akses','2013-08-02 10:04:00','','','2020-08-14 15:33:47','192.168.116.1',''),('2','Kasir','1','','Khusus User Kasir','2015-04-27 10:41:26','192.168.56.1','','2020-07-08 08:42:02','192.168.116.1','');

--
-- Table structure for table `data_pengguna_kluster`
--

CREATE TABLE `data_pengguna_kluster` (
  `kluster_id` int(11) NOT NULL AUTO_INCREMENT,
  `kluster_user` char(3) NOT NULL,
  `kluster_kode` char(3) NOT NULL,
  `kluster_bpr` char(3) NOT NULL,
  `kluster_kantor` char(3) NOT NULL,
  `kluster_status` tinyint(1) NOT NULL,
  PRIMARY KEY (`kluster_id`) USING BTREE,
  KEY `kluster_kode` (`kluster_kode`) USING BTREE,
  KEY `kluster_user` (`kluster_user`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=927 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=47 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `data_pengguna_kluster`
--

INSERT INTO `data_pengguna_kluster` VALUES (256,'001','00','01','01',1),(257,'001','00','01','02',1),(258,'001','00','01','03',1),(259,'001','00','01','04',1),(260,'001','00','02','00',1),(261,'001','00','02','01',1),(262,'001','00','02','02',1),(263,'001','00','02','03',1),(264,'001','00','02','04',1),(265,'001','00','02','05',1),(266,'001','00','02','06',1),(267,'001','00','02','07',1),(268,'001','00','02','08',1),(269,'001','00','02','09',1),(270,'001','00','02','10',1),(271,'001','00','02','11',1),(272,'001','00','02','12',1),(273,'001','00','02','13',1),(274,'001','00','02','14',1),(275,'001','00','03','01',1),(276,'001','00','03','02',1),(277,'001','00','03','03',1),(278,'001','00','03','04',1),(279,'001','00','03','05',1),(280,'001','00','03','06',1),(281,'001','00','03','07',1),(282,'001','00','03','08',1),(283,'001','00','03','09',1),(284,'001','00','03','10',1),(285,'001','00','03','11',1),(286,'001','00','03','12',1),(287,'001','00','03','13',1),(288,'001','00','03','14',1),(289,'001','00','03','15',1),(290,'001','00','03','16',1),(291,'001','00','03','17',1),(292,'001','00','03','18',1),(293,'001','00','03','19',1),(294,'001','00','03','20',1),(295,'001','00','03','21',1),(296,'001','00','03','22',1),(297,'001','00','03','23',1),(298,'001','00','03','24',1),(299,'001','00','03','25',1),(300,'001','00','04','01',1),(301,'001','00','05','01',1),(302,'001','00','06','01',1),(303,'001','00','06','02',1),(304,'001','00','06','03',1),(305,'001','00','06','04',1),(306,'001','00','06','05',1),(307,'001','00','06','06',1),(308,'001','00','06','07',1),(309,'001','00','06','08',1),(310,'001','00','06','09',1),(311,'001','00','06','10',1),(312,'001','00','07','00',1),(313,'001','00','07','01',1),(314,'001','00','07','02',1),(315,'001','00','07','03',1),(316,'001','00','07','04',1),(317,'001','00','07','05',1),(318,'001','00','07','06',1),(319,'001','00','07','07',1),(320,'001','00','07','08',1),(321,'001','00','07','09',1),(322,'001','00','07','10',1),(323,'001','00','07','11',1),(324,'001','00','07','12',1),(325,'001','00','07','13',1),(326,'001','00','07','14',1),(327,'001','00','07','15',1),(328,'001','00','07','16',1),(329,'001','00','07','17',1),(330,'001','00','07','18',1),(331,'001','00','07','19',1),(332,'001','00','07','20',1),(333,'001','00','09','01',1),(334,'001','00','09','02',1),(335,'001','00','10','01',1),(336,'001','00','10','02',1),(337,'001','00','11','01',1),(338,'001','00','11','02',1),(339,'001','00','12','01',1),(340,'001','00','12','02',1),(341,'001','00','12','03',1),(342,'001','00','12','04',1),(343,'001','00','12','05',1),(344,'001','00','12','06',1),(345,'001','00','12','07',1),(346,'001','00','12','08',1),(347,'001','00','12','09',1),(348,'001','00','12','10',1),(349,'001','00','12','11',1),(350,'001','00','12','12',1),(351,'001','00','12','13',1),(352,'001','00','13','01',1),(353,'001','00','13','02',1),(354,'001','00','13','03',1),(355,'001','00','13','04',1),(356,'001','00','13','05',1),(357,'001','00','13','06',1),(358,'001','00','13','07',1),(359,'001','00','13','08',1),(360,'001','00','13','09',1),(361,'001','00','14','01',1),(362,'001','00','14','02',1),(363,'001','00','14','03',1),(364,'001','00','14','04',1),(365,'001','00','14','05',1),(366,'001','00','14','06',1),(367,'001','00','14','07',1),(368,'001','00','14','08',1),(369,'001','00','14','09',1),(370,'001','00','14','10',1),(371,'001','00','15','00',1),(372,'001','00','15','01',1),(373,'001','00','15','02',1),(374,'001','00','15','03',1),(375,'001','00','15','04',1),(376,'001','00','15','05',1),(377,'001','00','15','06',1),(378,'001','00','15','07',1),(379,'001','00','15','08',1),(380,'001','00','15','09',1),(381,'001','00','15','10',1),(382,'001','00','15','11',1),(383,'001','00','16','00',1),(384,'001','00','16','01',1),(385,'001','00','16','02',1),(386,'001','00','16','03',1),(387,'001','00','16','04',1),(388,'001','00','17','00',1),(389,'001','00','17','01',1),(390,'001','00','17','02',1),(391,'001','00','17','03',1),(392,'001','00','17','04',1),(393,'001','00','17','05',1),(394,'001','00','17','06',1),(395,'001','00','18','01',1),(396,'001','00','18','02',1),(397,'001','00','18','03',1),(398,'001','00','18','04',1),(399,'001','00','18','05',1),(400,'001','00','18','06',1),(401,'001','00','18','07',1),(402,'001','00','19','01',1),(403,'001','00','19','02',1),(404,'001','00','19','03',1),(405,'001','00','20','01',1),(406,'001','00','21','01',1),(407,'001','00','22','01',1),(408,'001','00','23','01',1),(409,'001','00','23','02',1),(410,'001','00','23','03',1),(411,'001','00','24','01',1),(412,'001','00','24','02',1),(413,'001','00','25','01',1),(414,'001','00','26','01',1),(415,'001','00','26','02',1),(416,'001','00','26','03',1),(417,'001','00','26','04',1),(418,'001','00','27','01',1),(419,'001','00','27','02',1),(420,'001','00','27','03',1),(421,'001','00','27','04',1),(422,'001','00','28','01',1),(423,'001','00','28','02',1),(424,'001','00','28','03',1),(425,'001','00','29','01',1),(426,'001','00','29','02',1),(427,'001','00','29','03',1),(428,'001','00','29','04',1),(429,'001','00','29','05',1),(430,'001','00','29','06',1),(431,'001','00','29','07',1),(432,'001','00','29','08',1),(433,'001','00','29','09',1),(434,'001','00','29','10',1),(435,'001','00','29','11',1),(436,'001','00','29','12',1),(437,'001','00','30','01',1),(438,'001','00','31','00',1),(439,'001','00','31','01',1),(440,'001','00','31','02',1),(441,'001','00','31','03',1),(442,'001','00','31','04',1),(443,'001','00','31','05',1),(444,'001','00','31','06',1),(763,'003','01','01','01',1),(764,'003','01','01','02',1),(765,'003','01','01','03',1),(766,'003','01','01','04',1),(767,'003','01','02','00',1),(768,'003','01','02','01',1),(769,'003','01','02','02',1),(770,'003','01','02','03',1),(771,'003','01','02','04',1),(772,'003','01','02','05',1),(773,'003','01','02','06',1),(774,'003','01','02','07',1),(775,'003','01','02','08',1),(776,'003','01','02','09',1),(777,'003','01','02','10',1),(778,'003','01','02','11',1),(779,'003','01','02','12',1),(780,'003','01','02','13',1),(781,'003','01','02','14',1),(782,'003','01','03','01',1),(783,'003','01','03','02',1),(784,'003','01','03','03',1),(785,'003','01','03','04',1),(786,'003','01','03','05',1),(787,'003','01','03','06',1),(788,'003','01','03','07',1),(789,'003','01','03','08',1),(790,'003','01','03','09',1),(791,'003','01','03','10',1),(792,'003','01','03','11',1),(793,'003','01','03','12',1),(794,'003','01','03','13',1),(795,'003','01','03','14',1),(796,'003','01','03','15',1),(797,'003','01','03','16',1),(798,'003','01','03','17',1),(799,'003','01','03','18',1),(800,'003','01','03','19',1),(801,'003','01','03','20',1),(802,'003','01','03','21',1),(803,'003','01','03','22',1),(804,'003','01','03','23',1),(805,'003','01','03','24',1),(806,'003','01','03','25',1),(807,'003','01','04','01',1),(808,'003','01','06','01',1),(809,'003','01','06','02',1),(810,'003','01','06','03',1),(811,'003','01','06','04',1),(812,'003','01','06','05',1),(813,'003','01','06','06',1),(814,'003','01','06','07',1),(815,'003','01','06','08',1),(816,'003','01','06','09',1),(817,'003','01','06','10',1),(818,'003','01','07','00',1),(819,'003','01','07','01',1),(820,'003','01','07','02',1),(821,'003','01','07','03',1),(822,'003','01','07','04',1),(823,'003','01','07','05',1),(824,'003','01','07','06',1),(825,'003','01','07','07',1),(826,'003','01','07','08',1),(827,'003','01','07','09',1),(828,'003','01','07','10',1),(829,'003','01','07','11',1),(830,'003','01','07','12',1),(831,'003','01','07','13',1),(832,'003','01','07','14',1),(833,'003','01','07','15',1),(834,'003','01','07','16',1),(835,'003','01','07','17',1),(836,'003','01','07','18',1),(837,'003','01','07','19',1),(838,'003','01','07','20',1),(839,'003','01','12','01',1),(840,'003','01','12','02',1),(841,'003','01','12','03',1),(842,'003','01','12','04',1),(843,'003','01','12','05',1),(844,'003','01','12','06',1),(845,'003','01','12','07',1),(846,'003','01','12','08',1),(847,'003','01','12','09',1),(848,'003','01','12','10',1),(849,'003','01','12','11',1),(850,'003','01','12','12',1),(851,'003','01','12','13',1),(852,'003','01','14','01',1),(853,'003','01','14','02',1),(854,'003','01','14','03',1),(855,'003','01','14','04',1),(856,'003','01','14','05',1),(857,'003','01','14','06',1),(858,'003','01','14','07',1),(859,'003','01','14','08',1),(860,'003','01','14','09',1),(861,'003','01','14','10',1),(862,'003','01','16','00',1),(863,'003','01','16','01',1),(864,'003','01','16','02',1),(865,'003','01','16','03',1),(866,'003','01','16','04',1),(867,'003','01','29','01',1),(868,'003','01','29','02',1),(869,'003','01','29','03',1),(870,'003','01','29','04',1),(871,'003','01','29','05',1),(872,'003','01','29','06',1),(873,'003','01','29','07',1),(874,'003','01','29','08',1),(875,'003','01','29','09',1),(876,'003','01','29','10',1),(877,'003','01','29','11',1),(878,'003','01','29','12',1),(890,'002','03','17','00',1),(891,'002','03','17','01',1),(892,'002','03','17','02',1),(893,'002','03','17','03',1),(894,'002','03','17','04',1),(895,'002','03','17','05',1),(896,'002','03','17','06',1),(897,'002','03','18','01',1),(898,'002','03','18','02',1),(899,'002','03','18','03',1),(900,'002','03','18','04',1),(901,'002','03','18','05',1),(902,'002','03','18','06',1),(903,'002','03','18','07',1),(904,'002','03','19','01',1),(905,'002','03','19','02',1),(906,'002','03','19','03',1),(907,'002','03','20','01',1),(908,'002','03','21','01',1),(909,'002','03','22','01',1),(910,'002','03','23','01',1),(911,'002','03','23','02',1),(912,'002','03','23','03',1),(913,'002','03','24','01',1),(914,'002','03','24','02',1),(915,'002','03','25','01',1),(916,'002','03','26','01',1),(917,'002','03','26','02',1),(918,'002','03','26','03',1),(919,'002','03','26','04',1),(920,'002','03','27','01',1),(921,'002','03','27','02',1),(922,'002','03','27','03',1),(923,'002','03','27','04',1),(924,'002','03','28','01',1),(925,'002','03','28','02',1),(926,'002','03','28','03',1);

--
-- Table structure for table `data_transaksi`
--

CREATE TABLE `data_transaksi` (
  `trans_id` varchar(30) NOT NULL,
  `trans_tanggal` date NOT NULL DEFAULT '0000-00-00',
  `trans_channel` char(3) NOT NULL,
  `trans_rfid` varchar(20) NOT NULL,
  `trans_kode` varchar(5) NOT NULL,
  `trans_kantor` varchar(3) NOT NULL,
  `trans_rekening` varchar(20) NOT NULL,
  `trans_nasabah` varchar(15) DEFAULT NULL,
  `trans_nasabah_nama` varchar(50) NOT NULL,
  `trans_nasabah_kelas` varchar(20) NOT NULL,
  `trans_saldo_awal` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_jumlah` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_saldo_akhir` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_uraian` varchar(100) NOT NULL,
  `trans_status` tinyint(1) NOT NULL DEFAULT '0',
  `trans_reg_date` date NOT NULL DEFAULT '0000-00-00',
  `trans_reg_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trans_reg_alias` varchar(10) NOT NULL,
  KEY `trans_rekening` (`trans_rekening`) USING BTREE,
  KEY `trans_nasabah` (`trans_nasabah`) USING BTREE,
  KEY `data_transaksi_idx1` (`trans_id`) USING BTREE,
  KEY `data_transaksi_idx2` (`trans_tanggal`) USING BTREE,
  KEY `data_transaksi_idx3` (`trans_reg_date`) USING BTREE,
  KEY `data_transaksi_idx4` (`trans_reg_alias`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=208 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `data_transaksi`
--


--
-- Table structure for table `data_transaksi_madamat`
--

CREATE TABLE `data_transaksi_madamat` (
  `trans_id` varchar(30) NOT NULL,
  `trans_tanggal` date NOT NULL DEFAULT '0000-00-00',
  `trans_channel` char(3) NOT NULL,
  `trans_rfid` varchar(20) NOT NULL,
  `trans_kode` varchar(5) NOT NULL,
  `trans_kantor` varchar(3) NOT NULL,
  `trans_rekening` varchar(20) NOT NULL,
  `trans_nasabah` varchar(15) DEFAULT NULL,
  `trans_nasabah_nama` varchar(50) NOT NULL,
  `trans_nasabah_kelas` varchar(20) NOT NULL,
  `trans_saldo_awal` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_jumlah` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_saldo_akhir` decimal(20,0) NOT NULL DEFAULT '0',
  `trans_uraian` varchar(100) NOT NULL,
  `trans_status` tinyint(1) NOT NULL DEFAULT '0',
  `trans_reg_date` date NOT NULL DEFAULT '0000-00-00',
  `trans_reg_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trans_reg_alias` varchar(10) NOT NULL,
  KEY `trans_rekening_madamat` (`trans_rekening`) USING BTREE,
  KEY `trans_nasabah_madamat` (`trans_nasabah`) USING BTREE,
  KEY `data_transaksi_idx1_madamat` (`trans_id`) USING BTREE,
  KEY `data_transaksi_idx2_madamat` (`trans_tanggal`) USING BTREE,
  KEY `data_transaksi_idx3_madamat` (`trans_reg_date`) USING BTREE,
  KEY `data_transaksi_idx4_madamat` (`trans_reg_alias`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=208 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `data_transaksi_madamat`
--


--
-- Table structure for table `kode_komponen`
--

CREATE TABLE `kode_komponen` (
  `combo_id` int(5) NOT NULL AUTO_INCREMENT,
  `combo_komponen` varchar(3) NOT NULL,
  `combo_kode` varchar(3) NOT NULL,
  `combo_text` varchar(100) NOT NULL,
  `combo_keterangan` varchar(200) NOT NULL,
  PRIMARY KEY (`combo_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=36;

--
-- Dumping data for table `kode_komponen`
--

INSERT INTO `kode_komponen` VALUES (1,'01','1','Kantin Santri','Level untuk vendor MSO'),(3,'02','1','Aktif',' '),(4,'02','9','Blokir',' '),(5,'03','1','Semua Cluster',' '),(6,'03','2','Internal Cluster',' '),(7,'03','3','Internal Bank',' '),(8,'04','1','Neraca Etap',' '),(9,'04','2','Neraca BI',' '),(10,'05','1','Model Lajur',' '),(11,'05','2','Model Perbandingan',' '),(12,'06','1','Laba Rugi Etap',' '),(13,'06','2','Laba Rugi BI',' ');

--
-- Table structure for table `kode_menu`
--

CREATE TABLE `kode_menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_group` varchar(2) NOT NULL,
  `menu_kode` varchar(10) NOT NULL,
  `menu_parent` varchar(10) NOT NULL,
  `menu_label` varchar(60) NOT NULL,
  `menu_set` tinyint(1) NOT NULL DEFAULT '0',
  `menu_reg_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `menu_reg_ip` varchar(20) NOT NULL,
  `menu_alias` varchar(20) NOT NULL,
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=65;

--
-- Dumping data for table `kode_menu`
--

INSERT INTO `kode_menu` VALUES (13,'2','mn01','mn01','01. File',1,'2020-07-10 14:55:17','192.168.116.1',''),(14,'2','mn0101','mn01','0101. Ganti Password',1,'2020-07-10 14:55:17','192.168.116.1',''),(15,'2','mn0119','mn01','0119. Keluar',1,'2020-07-10 14:55:17','192.168.116.1',''),(16,'2','mn02','mn02','02. Operasional',1,'2020-07-10 14:55:17','192.168.116.1',''),(17,'2','mn0201','mn02','0201. Setup Group',0,'2020-07-10 14:55:17','192.168.116.1',''),(18,'2','mn0202','mn02','0202. Setup Pengguna',0,'2020-07-10 14:55:17','192.168.116.1',''),(19,'2','mn0203','mn02','0203. Log Login',0,'2020-07-10 14:55:17','192.168.116.1',''),(20,'2','mn0204','mn02','0204. Log Password',0,'2020-07-10 14:55:17','192.168.116.1',''),(21,'2','mn0209','mn02','0209. -',0,'2020-07-10 14:55:17','192.168.116.1',''),(22,'2','mn0211','mn02','0211. Transaksi',1,'2020-07-10 14:55:17','192.168.116.1',''),(23,'2','mn0212','mn02','0212. Browse Transaksi',1,'2020-07-10 14:55:17','192.168.116.1',''),(24,'2','mn0213','mn02','0213. Laporang Tansaksi',1,'2020-07-10 14:55:17','192.168.116.1',''),(25,'1','mn01','mn01','01. File',1,'2020-08-14 15:33:47','192.168.116.1',''),(26,'1','mn0101','mn01','0101. Ganti Password',1,'2020-08-14 15:33:47','192.168.116.1',''),(27,'1','mn0119','mn01','0119. Keluar',1,'2020-08-14 15:33:47','192.168.116.1',''),(28,'1','mn02','mn02','02. Operasional',1,'2020-08-14 15:33:47','192.168.116.1',''),(29,'1','mn0201','mn02','0201. Setup Group',1,'2020-08-14 15:33:47','192.168.116.1',''),(30,'1','mn0202','mn02','0202. Setup Pengguna',1,'2020-08-14 15:33:47','192.168.116.1',''),(31,'1','mn0203','mn02','0203. Log Login',1,'2020-08-14 15:33:47','192.168.116.1',''),(32,'1','mn0204','mn02','0204. Log Password',1,'2020-08-14 15:33:47','192.168.116.1',''),(33,'1','mn0209','mn02','0209. -',1,'2020-08-14 15:33:47','192.168.116.1',''),(34,'1','mn0211','mn02','0211. Transaksi',1,'2020-08-14 15:33:47','192.168.116.1',''),(35,'1','mn0212','mn02','0212. Browse Transaksi',1,'2020-08-14 15:33:47','192.168.116.1',''),(36,'1','mn0213','mn02','0213. Laporang Tansaksi',1,'2020-08-14 15:33:47','192.168.116.1',''),(37,'1','mn0219','mn02','0219. -',1,'2020-08-14 15:33:47','192.168.116.1',''),(38,'1','mn0221','mn02','0221. Transaksi Madamat',1,'2020-08-14 15:33:47','192.168.116.1',''),(40,'1','mn0206','mn02','0206. Setup Kantin',1,'2021-06-15 16:12:57','192.168.116.1',' ');

--
-- Table structure for table `log_login`
--

CREATE TABLE `log_login` (
  `log_alias` varchar(20) NOT NULL,
  `log_tanggal` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `log_ipaddress` varchar(15) NOT NULL,
  `log_hostname` varchar(20) NOT NULL,
  `log_macaddress` varchar(20) NOT NULL,
  `log_version` varchar(20) NOT NULL,
  `log_session` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=65;

--
-- Dumping data for table `log_login`
--

INSERT INTO `log_login` VALUES ('admin','2021-12-08 08:33:30','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.2','SES2112080001'),('admin','2021-12-08 08:38:34','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.2','SES2112080002'),('admin','2021-12-08 08:39:37','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.2','SES2112080003'),('admin','2021-12-08 08:43:09','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.2','SES2112080004'),('umma','2021-12-08 09:08:10','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.2','SES2112080005'),('umma','2021-12-08 09:12:48','192.168.116.1','DESKTOP-MH34T8V','00-50-56-C0-00-8','1.1.1','SES2112080006');

--
-- Table structure for table `log_password`
--

CREATE TABLE `log_password` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_date` date NOT NULL,
  `log_time` datetime NOT NULL,
  `log_user` varchar(20) NOT NULL,
  `log_version` varchar(20) NOT NULL,
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `log_password_idx1` (`log_date`) USING BTREE,
  KEY `log_password_idx2` (`log_time`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `log_password`
--

INSERT INTO `log_password` VALUES (1,'2021-08-19','2021-08-19 20:10:12','admin','1.1.1');

--
-- Table structure for table `set_app`
--

CREATE TABLE `set_app` (
  `app_case` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `app_text` text CHARACTER SET latin7
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=29;

--
-- Dumping data for table `set_app`
--

INSERT INTO `set_app` VALUES ('NamaAplikasi','Aplikasi Minimarket'),('Version','1.1.1'),('Release','01/07/2021'),('Channel','09'),('NoRekening','01.101.00439'),('LembagaNama','Bagian Minimarket'),('LembagaAlamat','PP MBS Al-Amin Bojonegoro'),('MaksimalTrans','1000000'),('PinEnable','0'),('LembagaTransMax','1000000'),('LembagaTransID','MMT'),('LembagaTransKet','Bayar Minimarket dari ');

--
-- Dumping events for database 'all-in-one-kantin'
--
/*!50106 DROP EVENT IF EXISTS `schGrantUser` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = '' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`db-toko`@`%`*/ /*!50106 EVENT `schGrantUser` ON SCHEDULE EVERY 1 DAY STARTS '2020-07-08 05:41:04' ON COMPLETION NOT PRESERVE ENABLE DO CALL `DoGrantUserMore`() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `schSinkronTransaksi` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = latin1 */ ;;
/*!50003 SET character_set_results = latin1 */ ;;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = '' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`rahmat`@`%`*/ /*!50106 EVENT `schSinkronTransaksi` ON SCHEDULE EVERY 5 MINUTE STARTS '2021-06-07 23:39:04' ON COMPLETION NOT PRESERVE ENABLE DO CALL `DoSinkronTrans`() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;

--
-- Dumping routines for database 'all-in-one-kantin'
--
/*!50003 DROP FUNCTION IF EXISTS `CreateProsesTKS` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `CreateProsesTKS`(
        `vID` VARCHAR(30),
        `vTanggal` DATE,
        `vKantor` VARCHAR(3)
    ) RETURNS varchar(2) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(2);

     INSERT INTO tks_proses
     SELECT vId, 0;

     CALL `CreateTksAll`(vTanggal, vId, vKantor);

     UPDATE tks_proses SET proses_status = '1'
     WHERE proses_id = vId;

     SELECT proses_status INTO xReturn
     FROM tks_proses WHERE proses_id = vID;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasDorK` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasDorK`(
        `vId` VARCHAR(10)
    ) RETURNS varchar(150) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(150);

     SELECT aruskas_d_or_k INTO xReturn
            FROM aruskas_master
            WHERE aruskas_kode = vId;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasDorKEtap` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasDorKEtap`(
        `vId` VARCHAR(10)
    ) RETURNS varchar(150) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(150);

     SELECT aruskas_d_or_k INTO xReturn
            FROM aruskas_master_etap
            WHERE aruskas_kode = vId;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetArusKasFix` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetArusKasFix`(
        `vAruskas` VARCHAR(10)
    ) RETURNS varchar(20) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(20);

     IF CHAR_LENGTH(vAruskas) = 6 THEN
        SELECT SUM(temp_jumlah) INTO xReturn
            FROM `aruskas_temp_perkiraan`
            WHERE temp_aruskas = vAruskas;

     END IF;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasJumlah` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasJumlah`(
        `vTglAwal` DATE,
        `vTglAkhir` DATE,
        `vPerkiraan` VARCHAR(10),
        `vKantor` VARCHAR(3),
        `vDorK` VARCHAR(2),
        `vNotasi` VARCHAR(2)
    ) RETURNS varchar(30) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(30);

     SELECT IF(vDorK = '',
            SUM(IF(`perk_d_or_k`='D', temp_debet-temp_kredit, temp_kredit-temp_debet)),
            SUM(IF(vDorK = 'D', temp_debet, temp_kredit)))*vNotasi
            INTO xReturn
     FROM aruskas_temp, `switch-mbs`.`kode_perkiraan_konv`
     WHERE INSTR(temp_kode, vPerkiraan) > 0
           AND perk_kode = temp_kode
           AND IF(vKantor <> '', temp_kantor = vKantor, 1=1)
           AND (temp_tanggal BETWEEN vTglAwal AND vTglAkhir);

     RETURN IFNULL(xReturn, 0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasJumlahEtap` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasJumlahEtap`(
        `vTglAwal` DATE,
        `vTglAkhir` DATE,
        `vPerkiraan` VARCHAR(10),
        `vKantor` VARCHAR(3),
        `vNotasi` VARCHAR(2)
    ) RETURNS varchar(30) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(30);

     SELECT SUM(temp_kredit-temp_debet)*vNotasi
            INTO xReturn
     FROM `aruskas_temp_etap`, `switch-mbs`.`kode_perkiraan_etap`
     WHERE IF(LENGTH(vPerkiraan) > 2, INSTR(`temp_kode`, vPerkiraan)>0, LEFT(`temp_kode`, 2) = vPerkiraan)
           AND perk_kode = temp_kode
           AND IF(vKantor <> '', temp_kantor = vKantor, 1=1)
           AND (temp_tanggal BETWEEN vTglAwal AND vTglAkhir);

     RETURN IFNULL(xReturn, 0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasNama`(
        `vId` VARCHAR(10)
    ) RETURNS varchar(150) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(150);

     SELECT aruskas_nama INTO xReturn
            FROM aruskas_master
            WHERE aruskas_kode = vId;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetAruskasNamaEtap` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetAruskasNamaEtap`(
        `vId` VARCHAR(10)
    ) RETURNS varchar(150) CHARSET latin1
BEGIN
     DECLARE xReturn VARCHAR(150);

     SELECT aruskas_nama INTO xReturn
            FROM aruskas_master_etap
            WHERE aruskas_kode = vId;

     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetBankKode` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetBankKode`() RETURNS varchar(3) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(3);

     SELECT `mbsbackup`.GetInfoApps('KodeBPR') INTO xReturn;

     RETURN xReturn;


END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetBprCabangNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetBprCabangNama`(
        `KodeBpr` CHAR(3),
        `KodeCabang` CHAR(3)
    ) RETURNS varchar(100) CHARSET utf8
BEGIN

     DECLARE xReturn VARCHAR(100);
     
     SELECT kantor_alias INTO xReturn
     FROM `mbsmobile`.mobile_kantor
     WHERE kantor_bpr = KodeBpr and kantor_kode = KodeCabang;
     
     IF KodeCabang = '' THEN
        SET xReturn = 'Konsolidasi';
     END IF;
     
     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetBprKota` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetBprKota`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
   declare xReturn varchar(200);

   SELECT server_kota INTO xReturn
   FROM `data_bpr_master`
   WHERE server_id = vcase;

   RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetBprNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetBprNama`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
   declare xReturn varchar(200);

   SELECT server_nama INTO xReturn
   FROM `data_bpr_master`
   WHERE server_id = vcase;

   RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetClusterNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetClusterNama`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
   declare xReturn varchar(200);

   SELECT kantin_nama INTO xReturn
   FROM `data_kantin_master`
   WHERE kantin_kode = vcase;

   RETURN IFNULL(xReturn, '');
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetGroupJenis` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetGroupJenis`(
        `Kode` CHAR(2)
    ) RETURNS char(30) CHARSET utf8
BEGIN

     DECLARE xReturn CHAR(30);
     SELECT combo_text INTO xReturn
     FROM kode_komponen
     WHERE combo_komponen = '03' AND combo_kode = Kode;
     
     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetGroupJumlah` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetGroupJumlah`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(30) CHARSET latin1
BEGIN
   declare xReturn varchar(30);

   SELECT COUNT(menu_kode)
   INTO xReturn
   FROM `kode_menu`
   WHERE menu_group = vCase AND menu_set = '1';

  RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetGroupNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetGroupNama`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(30) CHARSET latin1
BEGIN
   declare xReturn varchar(70);

   SELECT group_nama INTO xReturn
   FROM `data_pengguna_group`
   WHERE group_kode = vcase;

   RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetID` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetID`() RETURNS varchar(100) CHARSET utf8
BEGIN

     DECLARE xReturn VARCHAR(100);
     SELECT REPLACE(REPLACE(REPLACE(NOW(), '-', ''), ':', ''), ' ', '') INTO xReturn;
     RETURN xReturn;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetKantorAlamat` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetKantorAlamat`(
        `vKode` VARCHAR(3)
    ) RETURNS varchar(100) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(100);

     SELECT CONCAT(kantor_alamat, ' ', kantor_kota1)
     INTO xReturn
     FROM `switch-mbs`.data_kantor_master
     WHERE kantor_kode = IF(vKode='00', '01', vKode) ;

     RETURN UCASE(xReturn);

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetKantorNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetKantorNama`(
        `vKode` VARCHAR(3)
    ) RETURNS varchar(50) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(50);

     SELECT CONCAT(IF(CHAR_LENGTH(vKode) = 1, CONCAT('0', vKode), vKode), ' : ', kantor_alias)
     INTO xReturn
     FROM `switch-mbs`.data_kantor_master
     WHERE kantor_kode = IF(CHAR_LENGTH(vKode) = 1, CONCAT('0', vKode), vKode) ;

     RETURN UCASE(xReturn);

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetKlusterBpr` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetKlusterBpr`(
        `Kode` CHAR(3)
    ) RETURNS varchar(50) CHARSET utf8
BEGIN

    DECLARE xReturn VARCHAR(50);
    
    SELECT server_nama INTO xReturn 
    FROM data_bpr_master
    WHERE server_id = Kode;
    
    RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetKlusterKantor` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetKlusterKantor`(
        `KodeBpr` CHAR(3),
        `KodeKantor` CHAR(3)
    ) RETURNS varchar(50) CHARSET utf8
BEGIN

    DECLARE xReturn VARCHAR(50);
    
    SELECT REPLACE(kantor_nama, '-', ',') INTO xReturn 
    FROM `mbsmobile`.mobile_kantor
    WHERE kantor_bpr = KodeBpr AND kantor_kode = KodeKantor;
    
    IF INSTR(xReturn, ',')>0 THEN
       RETURN TRIM(SplitString(xReturn, ',', 2));
    ELSE
       RETURN xReturn;
    END IF;
    
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetKlusterNama` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetKlusterNama`(
        `Kode` CHAR(3)
    ) RETURNS varchar(50) CHARSET utf8
BEGIN

    DECLARE xReturn VARCHAR(50);
    
    SELECT cluster_nama INTO xReturn 
    FROM data_cluster_master
    WHERE cluster_kode = Kode;
    
    RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetNamaArusKas` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetNamaArusKas`(
        `vKode` VARCHAR(10)
    ) RETURNS varchar(100) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(100);

     SELECT aruskas_nama INTO xReturn
     FROM aruskas_master
     WHERE aruskas_kode = vKode;

     RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetNamaPerkiraan` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetNamaPerkiraan`(
        `vKode` VARCHAR(18)
    ) RETURNS varchar(100) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(100);

     SELECT perk_text INTO xReturn
     FROM `switch-mbs`.kode_perkiraan_konv
     WHERE perk_kode = vKode;

     RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetNamaPerkiraanEtap` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetNamaPerkiraanEtap`(
        `vKode` VARCHAR(18)
    ) RETURNS varchar(100) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(100);

     SELECT perk_text1 INTO xReturn
     FROM `switch-mbs`.kode_perkiraan_etap
     WHERE perk_kode = vKode;

     RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetSerial` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetSerial`(
        `vID` VARCHAR(200)
    ) RETURNS varchar(300) CHARSET utf8
BEGIN
     DECLARE xReturn VARCHAR(300);

     SELECT MD5(CONCAT(vID, '1bismillah!!!'))
            INTO xReturn;

     RETURN UCASE(xReturn);

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetUserJenis` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetUserJenis`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
   declare xReturn varchar(200);

   SELECT combo_text INTO xReturn
   FROM `kode_komponen`
   WHERE combo_kode = vcase and combo_komponen = '01';

   RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetUserKantor` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetUserKantor`(
        `vKode` VARCHAR(3)
    ) RETURNS varchar(50) CHARSET latin1
BEGIN

     DECLARE xReturn VARCHAR(50);

     IF vKode <> '00' THEN
        SELECT CONCAT(kantor_kode, ' : ', kantor_alias)
            INTO xReturn
            FROM `switch-mbs`.data_kantor_master
            WHERE kantor_kode = vKode;

     ELSEIF vKode = '00' THEN
         SET xReturn = '00 : KANTOR PUSAT MANAJEMEN';
     END IF;

     RETURN UCASE(xReturn);

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetUserStatus` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `GetUserStatus`(
        `vCase` VARCHAR(30)
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
   declare xReturn varchar(200);

   SELECT combo_text INTO xReturn
   FROM `kode_komponen`
   WHERE combo_kode = vcase and combo_komponen = '02';

   RETURN xReturn;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `SplitString` */;
DELIMITER ;;
CREATE DEFINER=`usr_rahmat`@`%` FUNCTION `SplitString`(
        `x` LONGTEXT,
        `delim` VARCHAR(12),
        `pos` INTEGER
    ) RETURNS longtext CHARSET latin1
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '') ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DoGrantUserMore` */;
DELIMITER ;;
CREATE DEFINER=`db-toko`@`%` PROCEDURE `DoGrantUserMore`()
BEGIN
   DECLARE xPassword VARCHAR(100);
   
   START TRANSACTION;
   
   SELECT `Password` INTO xPassword FROM `mysql`.`user`
   WHERE `User`='aio-kantin';

   DELETE FROM `mysql`.`user`
   WHERE LEFT(`User`, 4)='ktn_';

   DELETE FROM `mysql`.`db`
   WHERE LEFT(`User`, 4)='ktn_';

   INSERT INTO `mysql`.`user` (`Host`, `User`, `Password`)
   SELECT '%', CONCAT('ktn_', `user_alias`), xPassword
   FROM `data_pengguna_alias`;

   INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `References_priv`, `Lock_tables_priv`, `Execute_priv`, `Alter_routine_priv`)
   SELECT '%', 'aio-kantin', CONCAT('ktn_', `user_alias`), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
   FROM `data_pengguna_alias`;

   INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `References_priv`, `Lock_tables_priv`, `Execute_priv`, `Alter_routine_priv`)
   SELECT '%', 'cashless-adm', CONCAT('ktn_', `user_alias`), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
   FROM `data_pengguna_alias`;

   INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `References_priv`, `Lock_tables_priv`, `Execute_priv`, `Alter_routine_priv`)
   SELECT '%', 'aio-kantin-backup', CONCAT('ktn_', `user_alias`), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
   FROM `data_pengguna_alias`;
   
   FLUSH PRIVILEGES;
   
   COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DoSinkronTrans` */;
DELIMITER ;;
CREATE DEFINER=`rahmat`@`%` PROCEDURE `DoSinkronTrans`()
BEGIN

	UPDATE data_transaksi as A SET A.trans_status = 9
    WHERE A.trans_id = 
    	(SELECT B.trans_id FROM `cashless-adm`.data_tabungan_trans as B
         WHERE B.trans_status = 9 AND B.trans_tanggal = CURDATE()
         AND B.trans_id = A.trans_id LIMIT 1);
    
    COMMIT;
    
END ;;
DELIMITER ;


