/*
SQLyog Community v12.4.3 (64 bit)
MySQL - 10.1.37-MariaDB : Database - bus_ticketnew
*********************************************************************
*/



CREATE DATABASE /*!32312 IF NOT EXISTS*/`bus_ticketnew` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `bus_ticketnew`;

/*Table structure for table `buses_tb` */


CREATE TABLE `buses_tb` (
  `busid` bigint(13) NOT NULL AUTO_INCREMENT,
  `companyfk` bigint(13) DEFAULT NULL,
  `number_plate` varchar(50) DEFAULT NULL,
  `seats` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`busid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `buses_tb` */

insert  into `buses_tb`(`busid`,`companyfk`,`number_plate`,`seats`) values 
(1,1,'abc123','2'),
(2,1,'bca321','56'),
(3,1,'fgt123','60'),
(4,2,'abc546','45'),
(5,2,'abc576','67'),
(6,2,'nft678','78');

/*Table structure for table `company_tb` */


CREATE TABLE `company_tb` (
  `companyid` bigint(13) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(50) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  `datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`companyid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

/*Data for the table `company_tb` */

insert  into `company_tb`(`companyid`,`company_name`,`location`,`datecreated`) values 
(1,'likili','Lusaka','2020-06-02 17:54:37'),
(2,'powertools','Kitwe','2020-06-02 17:54:58');

/*Table structure for table `journey_tb` */


CREATE TABLE `journey_tb` (
  `journeyid` bigint(13) NOT NULL AUTO_INCREMENT,
  `routefkid` bigint(13) DEFAULT NULL,
  `companyfk` bigint(13) DEFAULT NULL,
  `stationid` bigint(13) DEFAULT NULL,
  `origin` varchar(250) DEFAULT NULL,
  `destination` varchar(250) DEFAULT NULL,
  `fair` varchar(250) DEFAULT NULL,
  `journey_date` date DEFAULT NULL,
  `bus_number_plate` varchar(50) DEFAULT NULL,
  `seat_number` varchar(13) DEFAULT NULL,
  `datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`journeyid`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=latin1;

/*Data for the table `journey_tb` */

insert  into `journey_tb`(`journeyid`,`routefkid`,`companyfk`,`stationid`,`origin`,`destination`,`fair`,`journey_date`,`bus_number_plate`,`seat_number`,`datecreated`,`active`) values 
(77,6,1,2,'Ndola','Lusaka','250','2020-06-29','abc123','2','2020-06-07 12:23:19',1),
(78,6,1,2,'Ndola','Lusaka','250','2020-06-04','abc123','2','2020-06-07 13:57:19',1),
(79,14,2,4,'Lusaka','Chipata','234','2020-06-28','nft678','78','2020-06-07 23:59:34',1),
(80,15,2,4,'Lusaka','Katete','400','2020-06-30','abc546','45','2020-06-09 14:01:11',1),
(81,14,2,4,'Lusaka','Chipata','234','2020-06-23','abc576','67','2020-06-10 23:35:54',1),
(82,7,1,2,'Ndola','Kabwe','350','2020-06-30','abc123','2','2020-06-11 02:04:33',1),
(83,9,1,2,'Ndola','Chisamba','400','2020-06-30','abc123','2','2020-06-12 13:32:58',1),
(84,7,1,2,'Ndola','Kabwe','350','2020-06-28','fgt123','60','2020-06-12 15:11:08',1),
(85,10,1,2,'Ndola','Kapiri','240','2020-06-07','bca321','56','2020-06-12 16:24:28',1),
(86,6,1,2,'Ndola','Lusaka','250','2020-06-30','bca321','56','2020-06-12 16:35:24',1);

/*Table structure for table `parcel_tb` */


CREATE TABLE `parcel_tb` (
  `parcelid` bigint(13) NOT NULL AUTO_INCREMENT,
  `journeyid` bigint(13) DEFAULT NULL,
  `companyfk` bigint(13) DEFAULT NULL,
  `routefk` bigint(13) DEFAULT NULL,
  `sendernumber` int(50) DEFAULT NULL,
  `receivernumber` int(50) DEFAULT NULL,
  `fair` int(50) DEFAULT NULL,
  `cashiername` varchar(250) DEFAULT NULL,
  `journey_date` date DEFAULT NULL,
  `reversed` tinyint(2) DEFAULT '0',
  `transactiondate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`parcelid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

/*Data for the table `parcel_tb` */

insert  into `parcel_tb`(`parcelid`,`journeyid`,`companyfk`,`routefk`,`sendernumber`,`receivernumber`,`fair`,`cashiername`,`journey_date`,`reversed`,`transactiondate`) values 
(5,77,1,6,123123,123123,12,'emmanuel mubanga','2020-06-29',0,'2020-06-07 16:03:41'),
(6,79,2,14,976824560,966493743,12,'malaika mubanga','2020-06-28',0,'2020-06-10 22:39:47'),
(7,79,2,14,9985655,5655566,123,'malaika mubanga','2020-06-28',0,'2020-06-10 22:40:43'),
(8,78,1,6,23550563,32863588,12,'emmanuel mubanga','2020-06-04',0,'2020-06-12 08:13:36'),
(9,78,1,6,235580,535323,12,'emmanuel mubanga','2020-06-04',0,'2020-06-12 08:14:12'),
(10,78,1,6,855588,1255,12,'emmanuel mubanga','2020-06-04',0,'2020-06-12 08:14:30');

/*Table structure for table `route_tb` */


CREATE TABLE `route_tb` (
  `routeid` bigint(13) NOT NULL AUTO_INCREMENT,
  `stationid` bigint(13) DEFAULT NULL,
  `companyfk` bigint(13) DEFAULT NULL,
  `origin` varchar(50) DEFAULT NULL,
  `destination` varchar(50) DEFAULT NULL,
  `fair` double DEFAULT NULL,
  `datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`routeid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

/*Data for the table `route_tb` */

insert  into `route_tb`(`routeid`,`stationid`,`companyfk`,`origin`,`destination`,`fair`,`datecreated`) values 
(1,1,1,'Lusaka','Ndola',250,'2020-06-02 15:50:27'),
(2,1,1,'Lusaka','Kabwe',350,'2020-06-06 12:16:46'),
(3,1,1,'Lusaka','Chibombo',234,'2020-06-06 12:17:03'),
(4,1,1,'Lusaka','Chisamba',400,'2020-06-06 12:18:03'),
(5,1,1,'Lusaka','Kapiri',240,'2020-06-24 12:18:03'),
(6,2,1,'Ndola','Lusaka',250,'2020-06-30 12:19:42'),
(7,2,1,'Ndola','Kabwe',350,'2020-06-29 12:20:11'),
(8,2,1,'Ndola','Chibombo',234,'2020-06-26 12:21:59'),
(9,2,1,'Ndola','Chisamba',400,'2020-06-25 12:22:35'),
(10,2,1,'Ndola','Kapiri',240,'2020-06-27 12:23:03'),
(11,3,2,'Chipata','Katete',240,'2020-06-06 13:26:46'),
(12,3,2,'Chipata','Lusaka',250,'2020-06-06 13:26:59'),
(13,3,2,'Chipata','Lwangwa',350,'2020-06-06 13:27:17'),
(14,4,2,'Lusaka','Chipata',234,'2020-06-06 13:27:25'),
(15,4,2,'Lusaka','Katete',400,'2020-06-06 13:27:38');

/*Table structure for table `station_tb` */


CREATE TABLE `station_tb` (
  `stationid` bigint(13) NOT NULL AUTO_INCREMENT,
  `companyfk` bigint(13) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  `datacreated` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`stationid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `station_tb` */

insert  into `station_tb`(`stationid`,`companyfk`,`name`,`location`,`datacreated`) values 
(1,1,'Inter City','Lusaka','2020-05-19 09:13:07'),
(2,1,'Plaza','Ndola','2020-06-02 15:36:19'),
(3,2,'Chipata Inter','Chipata','2020-06-02 15:36:19'),
(4,2,'City Inter','Lusaka','2020-06-02 15:36:19');

/*Table structure for table `ticket_transactions_tb` */


CREATE TABLE `ticket_transactions_tb` (
  `ticketid` bigint(13) NOT NULL AUTO_INCREMENT,
  `companyfk` bigint(13) DEFAULT NULL,
  `routefk` bigint(13) DEFAULT NULL,
  `journeyid` bigint(13) DEFAULT NULL,
  `custnumber` varchar(50) DEFAULT NULL,
  `custnextofkin` varchar(50) DEFAULT NULL,
  `luggagefair` varchar(50) DEFAULT NULL,
  `fair` varchar(50) DEFAULT NULL,
  `cashiername` varchar(50) DEFAULT NULL,
  `journey_date` date DEFAULT NULL,
  `reversed` tinyint(2) DEFAULT '0',
  `datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ticketid`)
) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=latin1;

/*Data for the table `ticket_transactions_tb` */

insert  into `ticket_transactions_tb`(`ticketid`,`companyfk`,`routefk`,`journeyid`,`custnumber`,`custnextofkin`,`luggagefair`,`fair`,`cashiername`,`journey_date`,`reversed`,`datecreated`) values 
(72,1,6,77,'123','3123','123','250','emmanuel mubanga','2020-06-29',0,'2020-06-10 23:44:05'),
(73,1,6,77,'123','123','12','250','emmanuel mubanga','2020-06-29',0,'2020-06-10 23:44:05'),
(74,1,6,78,'312314','312321','12','250','emmanuel mubanga','2020-06-04',0,'2020-06-10 23:44:05'),
(75,1,6,78,'132187','123145','13215','250','emmanuel mubanga','2020-06-04',0,'2020-06-10 23:44:05'),
(76,2,14,79,'45455','45452','45525','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(77,2,14,79,'3525','55555','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(78,2,14,79,'56553','56656','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(79,2,14,79,'15455','12354','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(80,2,14,79,'12354','12354','13','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(81,2,14,79,'5865','5245','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(82,2,14,79,'12458','56655','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(83,2,14,79,'58785788','12222522','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(84,2,14,79,'236886','535355','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(85,2,14,79,'55655666','5656555','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(86,2,14,79,'23565688','53565656','235656','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(87,2,14,79,'5555655','5356565','535356','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(88,2,14,79,'8655566285','456655555','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(89,2,14,79,'4686665688','756868666','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(90,2,15,80,'5358668','2356838','12','400','malaika mubanga','2020-06-30',0,'2020-06-10 23:44:05'),
(91,2,15,80,'153263544','42565585','1245','400','malaika mubanga','2020-06-30',0,'2020-06-10 23:44:05'),
(92,2,14,79,'524568555','86858585','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(93,2,15,80,'2655368844','355688658','12','400','malaika mubanga','2020-06-30',0,'2020-06-10 23:44:05'),
(94,2,14,79,'53588644','53455555','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(95,2,14,79,'5688998','86609888','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(96,2,15,80,'8632555','8335623','0','400','malaika mubanga','2020-06-30',0,'2020-06-10 23:44:05'),
(97,2,15,80,'535686','523533','12','400','malaika mubanga','2020-06-30',0,'2020-06-10 23:44:05'),
(98,2,14,79,'423556578','86565654','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(99,2,14,79,'15325577','4522425','12','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(100,2,14,79,'55753','855888','42557','234','malaika mubanga','2020-06-28',0,'2020-06-10 23:44:05'),
(101,2,14,81,'656568566','111111111','21','234','malaika mubanga','2020-06-23',0,'2020-06-11 02:03:03'),
(102,1,7,82,'1111111111','2222222222','21','350','emmanuel mubanga','2020-06-30',0,'2020-06-11 02:04:47'),
(103,1,9,83,'655995','556655','1','400','emmanuel mubanga','2020-06-30',0,'2020-06-12 13:33:45'),
(104,1,9,83,'0777273166','85243','80','400','emmanuel mubanga','2020-06-30',0,'2020-06-12 13:34:39'),
(105,1,7,82,'4565659','4256655','12','350','emmanuel mubanga','2020-06-30',0,'2020-06-12 15:11:47'),
(106,1,10,85,'0976824560','096637493743','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-12 16:25:01'),
(107,1,10,85,'09854152296','09855125225','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-12 16:28:05'),
(108,1,6,86,'0976824560','0966493743','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:35:45'),
(109,1,6,86,'54556','53586','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:36:14'),
(110,1,6,86,'5588758777888','54248545455','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:37:11'),
(111,1,6,86,'0976584712','0965741585','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:42:30'),
(112,1,6,86,'5522222222','3333333333','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:43:04'),
(113,1,6,86,'0954275757','0975428555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:44:46'),
(114,1,6,86,'0951563555','0954422525','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:47:06'),
(115,1,6,86,'0555655355','8552555555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:47:45'),
(116,1,6,86,'0653855655','0628286525','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:49:10'),
(117,1,6,86,'2858888895','8585858588','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:49:39'),
(118,1,6,86,'0955555555','0985555555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 16:50:29'),
(119,1,6,86,'0975533255','0974552222','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 17:03:34'),
(120,1,6,86,'8605088555','8558588585','22','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 17:16:36'),
(121,1,6,86,'0988563552','0965415555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 17:23:20'),
(122,1,6,86,'0945635855','0934225525','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-12 18:05:27'),
(123,1,6,86,'1245899999','1523366666','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 10:59:09'),
(124,1,6,86,'0988755454','0959754555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:30:26'),
(125,1,6,86,'0978755748','0955454545','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:32:34'),
(126,1,6,86,'2588888555','2858855555','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:35:26'),
(127,1,6,86,'0985144455','0654125554','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:40:45'),
(128,1,6,86,'0988454444','0665455255','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:41:23'),
(129,1,6,86,'9055555554','8555555555','85','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:47:33'),
(130,1,6,86,'0985555455','0955155155','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:49:33'),
(131,1,6,86,'0983841555','0665541144','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 11:52:59'),
(132,1,6,86,'0985458444','8654544477','86','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 12:46:54'),
(133,1,6,86,'0957455478','0988854855','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 12:51:52'),
(134,1,6,86,'8948468585','5558888888','12','250','emmanuel mubanga','2020-06-30',0,'2020-06-13 12:52:21'),
(135,1,10,85,'0998554444','0958445544','15','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 12:58:29'),
(136,1,10,85,'0985454544','0958454544','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:01:36'),
(137,1,10,85,'0987444554','0954441544','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:02:07'),
(138,1,10,85,'0985484444','0665471444','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:07:09'),
(139,1,10,85,'0988887888','0954554555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:10:04'),
(140,1,10,85,'0979464545','0975451555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:12:13'),
(141,1,10,85,'0948455654','0988545545','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:13:44'),
(142,1,10,85,'0976824560','0966493743','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:16:15'),
(143,1,10,85,'8855656666','5555523322','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:17:11'),
(144,1,10,85,'0978634525','0945352255','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:21:10'),
(145,1,10,85,'8954555555','8598525888','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:22:07'),
(146,1,10,85,'0954565388','0668558866','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:22:35'),
(147,1,10,85,'8558866662','8965555555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:25:38'),
(148,1,10,85,'0657477444','0658415512','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:26:25'),
(149,1,10,85,'0655585888','0955575487','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:27:10'),
(150,1,10,85,'0695484444','0655414444','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:31:19'),
(151,1,10,85,'0986544744','0984422225','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:31:47'),
(152,1,10,85,'0988445652','0985711552','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:33:55'),
(153,1,10,85,'0984555557','0985522222','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 13:36:32'),
(154,1,10,85,'0985484585','0665144854','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:28:54'),
(155,1,10,85,'0655545525','0954182524','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:29:36'),
(156,1,10,85,'0985415655','0651155555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:44:00'),
(157,1,10,85,'0985415655','0651155555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:44:05'),
(158,1,10,85,'0984256555','8655585888','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:48:54'),
(159,1,10,85,'0954515222','0655552225','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:49:54'),
(160,1,10,85,'0654455454','0951212222','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 14:58:10'),
(161,1,10,85,'0987555588','0958556558','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 15:04:54'),
(162,1,10,85,'0945256552','0624655555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:14:31'),
(163,1,10,85,'0685522222','0955223255','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:15:01'),
(164,1,10,85,'0988455445','0685455555','15','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:20:08'),
(165,1,10,85,'0984554555','0984495557','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:20:51'),
(166,1,10,85,'0987565455','0988455555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:22:54'),
(167,1,10,85,'0984555555','0984522555','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:25:38'),
(168,1,10,85,'0956492285','0955422522','12','240','emmanuel mubanga','2020-06-07',0,'2020-06-13 16:26:51');

