-- http://www.phpmyadmin.net
--




--
-- Database: `brtc_inventory`
--

-- --------------------------------------------------------

--
-- Stand-in structure for view `bus_delivery`
--
CREATE TABLE IF NOT EXISTS `bus_delivery` (
`bus_id` int(11)
,`class_id` int(11)
,`engine_no` varchar(30)
,`chesis_no` varchar(30)
,`reg_no` varchar(30)
,`reg_date` date
,`delivery_status` varchar(10)
,`class_name` varchar(30)
,`delivery_date` date
,`product_type_id` int(20)
,`product_id` int(11)
,`delivery_from_id` int(11)
,`delivery_to_id` int(11)
,`status` varchar(10)
);
-- --------------------------------------------------------

--
-- Table structure for table `bus_info`
--

CREATE TABLE IF NOT EXISTS `bus_info` (
  `bus_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `product_type_id` int(11) NOT NULL DEFAULT '1',
  `bus_description` varchar(500) NOT NULL,
  `engine_no` varchar(30) NOT NULL,
  `chesis_no` varchar(30) NOT NULL,
  `reg_no` varchar(30) NOT NULL,
  `reg_date` date DEFAULT NULL,
  `spec_kpl` int(11) NOT NULL,
  `real_kpl` int(11) NOT NULL,
  `delivery_status` varchar(10) NOT NULL,
  `delivery_to_id` int(11) NOT NULL DEFAULT '1',
  `condition_id` int(11) NOT NULL DEFAULT '1',
  `route_id` int(11) NOT NULL,
  `driver_name` varchar(300) NOT NULL,
  `photo_path` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `bus_remarks` varchar(10000) NOT NULL,
  `bus_status` varchar(10) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`bus_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `company_info`
--

CREATE TABLE IF NOT EXISTS `company_info` (
  `company_id` int(5) NOT NULL,
  `company_name` varchar(50) NOT NULL,
  `company_address` varchar(300) NOT NULL,
  `contact_person` varchar(50) NOT NULL,
  `designation` varchar(30) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `email` varchar(20) NOT NULL,
  `company_remarks` varchar(10000) NOT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `company_info`
--

INSERT INTO `company_info` (`company_id`, `company_name`, `company_address`, `contact_person`, `designation`, `phone`, `email`, `company_remarks`) VALUES
(1, 'Ashok Leyland Limited', '1 Sardar Patel Road, Guindy, Chennai, India', 'Mr. Kaushik Sarkar', 'Country Head, Ashok Leyland Li', '+88-017-133-64825', 'Kaushik.Sarkar@ashok', '');

-- --------------------------------------------------------

--
-- Table structure for table `delivery`
--

CREATE TABLE IF NOT EXISTS `delivery` (
  `delivery_id` int(11) NOT NULL,
  `delivery_date` date NOT NULL,
  `product_type_id` int(20) NOT NULL,
  `product_id` int(11) NOT NULL,
  `regt_no` varchar(30) NOT NULL,
  `delivery_from_id` int(11) NOT NULL,
  `delivery_to_id` int(11) NOT NULL,
  `delivery_purpose` varchar(50) NOT NULL,
  `delivery_remarks` varchar(10000) NOT NULL,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY (`delivery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `delivery_to`
--

CREATE TABLE IF NOT EXISTS `delivery_to` (
  `delivery_to_id` int(11) NOT NULL,
  `delivery_to_type_id` int(30) NOT NULL,
  `delivery_to_name` varchar(50) NOT NULL,
  `location` varchar(100) NOT NULL,
  `del_contact_person` varchar(50) NOT NULL,
  `del_designation` varchar(30) NOT NULL,
  `del_phone` int(30) DEFAULT NULL,
  `del_email` varchar(20) DEFAULT NULL,
  `delivery_to_remarks` varchar(10000) DEFAULT NULL,
  PRIMARY KEY (`delivery_to_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `delivery_to`
--

INSERT INTO `delivery_to` (`delivery_to_id`, `delivery_to_type_id`, `delivery_to_name`, `location`, `del_contact_person`, `del_designation`, `del_phone`, `del_email`, `delivery_to_remarks`) VALUES
(1, 1, 'প্রধান কার্যালয়', 'মতিঝিল', 'চেয়্যারম্যান', 'চেয়ারম্যান', NULL, NULL, ''),
(2, 2, 'কল্যাণপুর বাস ডিপো', 'কল্যাণপুর', 'ডিপো ম্যানেজার', '', NULL, NULL, ''),
(3, 2, 'জোয়ারসাহারা বাস ডিপো', 'জোয়ারসাহারা', 'ডিপো ম্যানেজার', '', NULL, NULL, ''),
(4, 2, 'মতিঝিল বাস ডিপো', 'মতিঝিল', 'ডিপো ম্যানেজার', '', NULL, NULL, ''),
(5, 2, 'মিরপুর দ্বিতল বাস ডিপো', 'মিরপুর', 'ডিপো ম্যানেজার', 'ডিপো ম্যানেজার', NULL, NULL, ''),
(6, 2, 'নরসিংদী বাস ডিপো', 'নরসিংদী', 'ডিপো ম্যানেজার', 'ডিপো ম্যানেজার', 1723654987, '', ''),
(7, 2, 'কুমিল্লা বাস ডিপো', 'কুমিল্লা', 'ডিপো ম্যানেজার', '', NULL, NULL, ''),
(8, 2, 'চট্টগ্রাম বাস ডিপো', 'চট্টগ্রাম', 'ম্যানেজার', 'ম্যানেজার', NULL, NULL, ''),
(9, 2, 'বগুড়া বাস ডিপো', 'বগুড়া', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(10, 2, 'বরিশাল বাস ডিপো', 'বরিশাল', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(11, 2, 'পাবনা বাস ডিপো', 'পাবনা', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(12, 2, 'রংপুর বাস ডিপো', 'রংপুর', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(13, 2, 'খুলনা বাস ডিপো', 'খুলনা', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(14, 2, 'সিলেট বাস ডিপো', 'সিলেট', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(15, 2, 'নারায়ণগঞ্জ বাস ডিপো', 'নারায়ণগঞ্জ', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(16, 2, 'উথলী বাস ডিপো', 'উথলী', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(17, 2, 'সোনাপুর বাস ডিপো', 'সোনাপুর', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(18, 2, 'গাজীপুর বাস ডিপো', 'গাজীপুর', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(19, 4, 'কেন্দ্রীয় মেরামত কারখানা, জয়দেবপুর', 'জয়দেবপুর', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(20, 4, 'কেন্দ্রীয় মেরামত কারখানা, তেজগাঁও', 'তেজগাঁও', 'ম্যানেজার', 'ম্যানেজার', 0, 'del_email', ''),
(21, 3, 'কেন্দ্রীয় প্রশিক্ষণ কেন্দ্র, গাজীপুর', 'গাজীপুর', '', '', NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `delivery_to_type`
--

CREATE TABLE IF NOT EXISTS `delivery_to_type` (
  `delivery_to_type_id` int(11) NOT NULL,
  `delivery_to_type_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `delivery_to_type`
--

INSERT INTO `delivery_to_type` (`delivery_to_type_id`, `delivery_to_type_name`) VALUES
(2, 'ডিপো'),
(3, 'প্রশিক্ষণ কেন্দ্র'),
(4, 'ওয়ার্কশপ'),
(1, 'কার্যালয়'),
(5, 'ইজারা গ্রহীতা');

-- --------------------------------------------------------

--
-- Table structure for table `depot_exp`
--

CREATE TABLE IF NOT EXISTS `depot_exp` (
  `exp_id` int(11) NOT NULL DEFAULT '0',
  `exp_date` date NOT NULL,
  `input_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bus_id` int(11) NOT NULL,
  `reg_no` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `delivery_to_id` int(11) NOT NULL,
  `route_id` int(11) NOT NULL,
  `fuel` int(11) NOT NULL,
  `mobil` int(11) NOT NULL,
  `gear_oil` int(11) NOT NULL,
  `differ_oil` int(11) NOT NULL,
  `break_oil` int(11) NOT NULL,
  `steering_oil` int(11) NOT NULL,
  `power_oil` int(11) NOT NULL,
  `grease` int(11) NOT NULL,
  `toll` int(11) NOT NULL,
  `operating_cost` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `depot_exp`
--

INSERT INTO `depot_exp` (`exp_id`, `exp_date`, `input_date`, `bus_id`, `reg_no`, `class_id`, `delivery_to_id`, `route_id`, `fuel`, `mobil`, `gear_oil`, `differ_oil`, `break_oil`, `steering_oil`, `power_oil`, `grease`, `toll`, `operating_cost`) VALUES
(1, '2014-09-10', '2014-09-10 07:21:34', 7, 453453, 3, 2, 3, 4, 0, 0, 0, 0, 0, 0, 0, 4000, 0),
(2, '2014-09-10', '2014-09-10 07:21:34', 11, 453453, 2, 2, 4, 3, 0, 0, 0, 0, 0, 0, 0, 3000, 0),
(3, '2014-09-10', '2014-09-10 07:21:34', 14, 5, 2, 2, 2, 5, 0, 0, 0, 0, 0, 0, 0, 4500, 0),
(4, '2014-09-10', '2014-09-11 10:50:58', 16, 1234, 1, 2, 3, 2, 0, 0, 0, 0, 0, 0, 0, 2000, 0),
(5, '2014-09-12', '2014-09-12 17:23:34', 16, 1234, 1, 2, 3, 435, 0, 0, 0, 0, 0, 0, 0, 345, 0),
(6, '2014-09-12', '2014-09-12 17:23:34', 11, 453453, 2, 2, 4, 536, 0, 0, 0, 0, 0, 0, 0, 435, 0),
(7, '2014-09-12', '2014-09-12 17:23:34', 7, 453453, 3, 2, 3, 23, 0, 0, 0, 0, 0, 0, 0, 545, 0),
(8, '2014-09-15', '2014-09-14 06:49:55', 16, 1234, 1, 2, 3, 234, 0, 0, 0, 0, 0, 0, 0, 423, 0),
(9, '2014-09-15', '2014-09-14 06:49:55', 11, 453453, 2, 2, 4, 234, 0, 0, 0, 0, 0, 0, 0, 326, 0),
(10, '2014-09-16', '2014-09-14 07:36:03', 16, 1234, 1, 2, 3, 123, 0, 0, 0, 0, 0, 0, 0, 123, 0),
(11, '2014-09-16', '2014-09-14 07:36:03', 11, 453453, 2, 2, 4, 123, 0, 0, 0, 0, 0, 0, 0, 123, 0),
(12, '2014-09-16', '2014-09-14 07:36:03', 7, 453453, 3, 2, 3, 123, 0, 0, 0, 0, 0, 0, 0, 122, 0),
(13, '2014-09-14', '2014-09-14 07:39:11', 16, 1234, 1, 2, 3, 67, 0, 0, 0, 0, 0, 0, 0, 566, 0),
(14, '2014-09-14', '2014-09-14 07:39:11', 11, 453453, 2, 2, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(15, '2014-09-14', '2014-09-14 07:39:11', 7, 453453, 3, 2, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(16, '2014-09-17', '2014-09-14 07:40:06', 16, 1234, 1, 2, 3, 54, 0, 0, 0, 0, 0, 0, 0, 350, 0),
(17, '2014-09-17', '2014-09-14 07:40:06', 11, 453453, 2, 2, 4, 345, 0, 0, 0, 0, 0, 0, 0, 445, 0),
(18, '2014-09-17', '2014-09-14 07:40:06', 7, 453453, 3, 2, 3, 546, 0, 0, 0, 0, 0, 0, 0, 750, 0),
(19, '2014-09-18', '0000-00-00 00:00:00', 16, 1234, 1, 2, 3, 345, 345, 345, 345, 345, 34, 534, 534, 534, 5),
(20, '2014-09-18', '0000-00-00 00:00:00', 11, 453453, 2, 2, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(21, '2014-09-18', '0000-00-00 00:00:00', 7, 453453, 3, 2, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `depot_income`
--

CREATE TABLE IF NOT EXISTS `depot_income` (
  `income_id` int(11) NOT NULL DEFAULT '0',
  `income_date` date NOT NULL,
  `input_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `bus_id` int(11) NOT NULL,
  `reg_no` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `delivery_to_id` int(11) NOT NULL,
  `route_id` int(11) NOT NULL,
  `trip_no` int(11) NOT NULL,
  `mileage` int(11) NOT NULL,
  `income` int(11) NOT NULL,
  PRIMARY KEY (`income_id`),
  UNIQUE KEY `income_id` (`income_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `depot_income`
--

INSERT INTO `depot_income` (`income_id`, `income_date`, `input_date`, `bus_id`, `reg_no`, `class_id`, `delivery_to_id`, `route_id`, `trip_no`, `mileage`, `income`) VALUES
(1, '2014-09-10', '2014-09-10 07:21:34', 7, 453453, 3, 2, 3, 4, 40, 4000),
(2, '2014-09-10', '2014-09-10 07:21:34', 11, 453453, 2, 2, 4, 3, 30, 3000),
(3, '2014-09-10', '2014-09-10 07:21:34', 14, 5, 2, 2, 2, 5, 25, 4500),
(4, '2014-09-10', '2014-09-11 10:50:58', 16, 1234, 1, 2, 3, 2, 20, 2000),
(5, '2014-09-12', '2014-09-12 17:23:34', 16, 1234, 1, 2, 3, 435, 345, 345),
(6, '2014-09-12', '2014-09-12 17:23:34', 11, 453453, 2, 2, 4, 536, 64, 435),
(7, '2014-09-12', '2014-09-12 17:23:34', 7, 453453, 3, 2, 3, 23, 345, 545),
(8, '2014-09-15', '2014-09-14 06:49:55', 16, 1234, 1, 2, 3, 234, 234, 423),
(9, '2014-09-15', '2014-09-14 06:49:55', 11, 453453, 2, 2, 4, 234, 234, 326),
(13, '2014-09-14', '2014-09-14 07:39:11', 16, 1234, 1, 2, 3, 67, 567, 566),
(14, '2014-09-14', '2014-09-14 07:39:11', 11, 453453, 2, 2, 4, 0, 0, 0),
(15, '2014-09-14', '2014-09-14 07:39:11', 7, 453453, 3, 2, 3, 0, 0, 0),
(16, '2014-09-17', '2014-09-14 07:40:06', 16, 1234, 1, 2, 3, 54, 35, 350),
(17, '2014-09-17', '2014-09-14 07:40:06', 11, 453453, 2, 2, 4, 345, 45, 445),
(18, '2014-09-17', '2014-09-14 07:40:06', 7, 453453, 3, 2, 3, 546, 4564, 750);

-- --------------------------------------------------------

--
-- Table structure for table `depot_name`
--

CREATE TABLE IF NOT EXISTS `depot_name` (
  `depot_id` int(11) NOT NULL,
  `depot_name` text NOT NULL,
  PRIMARY KEY (`depot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `depot_name`
--

INSERT INTO `depot_name` (`depot_id`, `depot_name`) VALUES
(1, 'কল্যাণপুর বাস ডিপো'),
(2, 'মিরপুর দ্বিতল বাস ডিপো'),
(3, 'জোয়ারসাহারা বাস ডিপো'),
(4, 'মতিঝিল বাস ডিপো'),
(5, 'নারায়ণগঞ্জ বাস ডিপো'),
(6, 'নরসিংদী বাস ডিপো'),
(7, 'কুমিল্লা বাস ডিপো'),
(8, 'চট্টগ্রাম বাস ডিপো'),
(9, 'বগুড়া বাস ডিপো'),
(10, 'বরিশাল বাস ডিপো'),
(11, 'পাবনা বাস ডিপো'),
(12, 'রংপুর বাস ডিপো'),
(13, 'খুলনা বাস ডিপো'),
(14, 'সিলেট বাস ডিপো'),
(15, 'উথলী বাস ডিপো'),
(16, 'সোনাপুর বাস ডিপো'),
(17, 'গাজীপুর বাস ডিপো'),
(18, 'কেন্দ্রীয় মেরামত কারখানা, জয়দেবপুর'),
(19, 'কেন্দ্রীয় মেরামত কারখানা, তেজগাঁও');

-- --------------------------------------------------------

--
-- Table structure for table `fund_source`
--

CREATE TABLE IF NOT EXISTS `fund_source` (
  `fund_id` int(5) NOT NULL,
  `fund_name` varchar(50) NOT NULL,
  `fund_amount` decimal(10,0) NOT NULL,
  `interest_rate` int(11) NOT NULL,
  `instal_amount` decimal(10,2) NOT NULL,
  `instal_start_date` date NOT NULL,
  `fund_remarks` varchar(10000) NOT NULL,
  PRIMARY KEY (`fund_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `fund_source`
--

INSERT INTO `fund_source` (`fund_id`, `fund_name`, `fund_amount`, `interest_rate`, `instal_amount`, `instal_start_date`, `fund_remarks`) VALUES
(1, 'এনডিএফ', 20000000, 40, 50000.00, '2014-11-20', ''),
(2, 'ইন্ডিয়ান ডলার ক্রেডিট লাইন', 1100000, 15, 50000.00, '2014-09-26', '');

-- --------------------------------------------------------

--
-- Table structure for table `present_condition`
--

CREATE TABLE IF NOT EXISTS `present_condition` (
  `condition_id` int(11) NOT NULL,
  `condition` varchar(50) NOT NULL,
  PRIMARY KEY (`condition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `present_condition`
--

INSERT INTO `present_condition` (`condition_id`, `condition`) VALUES
(1, 'হেল্ড'),
(2, 'সচল (নিজস্ব পরিচালনায়)'),
(3, 'সচল (লীজের মাধ্যমে)'),
(4, 'হালকা মেরামতাধীন'),
(5, 'ভারী মেরামতাধীন'),
(6, 'দুর্ঘটনা'),
(7, 'মেরামতের অযোগ্য'),
(8, 'বাতিল'),
(9, 'বিক্রীত');

-- --------------------------------------------------------

--
-- Table structure for table `product_class`
--

CREATE TABLE IF NOT EXISTS `product_class` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(30) NOT NULL,
  `product_type_id` int(11) NOT NULL,
  `length` int(11) NOT NULL,
  `width` int(11) NOT NULL,
  `height` int(11) NOT NULL,
  `cc` int(11) NOT NULL,
  `power` int(11) NOT NULL,
  `eng_position` varchar(10) NOT NULL,
  `speed` int(11) NOT NULL,
  `seat_no` int(11) NOT NULL,
  `life` int(11) NOT NULL,
  `tyre_no` int(11) NOT NULL,
  `tyre_size` int(11) NOT NULL,
  `origin` varchar(20) NOT NULL,
  `date_manufacture` date NOT NULL,
  `life_start` date NOT NULL,
  `class_remarks` varchar(10000) NOT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product_class`
--

INSERT INTO `product_class` (`class_id`, `class_name`, `product_type_id`, `length`, `width`, `height`, `cc`, `power`, `eng_position`, `speed`, `seat_no`, `life`, `tyre_no`, `tyre_size`, `origin`, `date_manufacture`, `life_start`, `class_remarks`) VALUES
(1, 'অশোক লিল্যান্ড একতলা (সিএনজি)', 1, 20, 5, 10, 20000, 200, 'সামনে', 100, 30, 20, 6, 7, 'ভারত', '2014-09-02', '2014-12-05', '');

-- --------------------------------------------------------

--
-- Table structure for table `product_type`
--

CREATE TABLE IF NOT EXISTS `product_type` (
  `product_type_id` int(11) NOT NULL,
  `product_type` varchar(20) NOT NULL,
  PRIMARY KEY (`product_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product_type`
--

INSERT INTO `product_type` (`product_type_id`, `product_type`) VALUES
(1, 'বাস'),
(2, 'ট্রাক'),
(3, 'প্রশিক্ষণ কার');

-- --------------------------------------------------------

--
-- Table structure for table `purchase`
--

CREATE TABLE IF NOT EXISTS `purchase` (
  `purchase_id` int(11) NOT NULL,
  `contract_date` date NOT NULL,
  `purchase_date` date NOT NULL,
  `class_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `fund_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `purchase_remarks` varchar(10000) NOT NULL,
  PRIMARY KEY (`purchase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `route`
--

CREATE TABLE IF NOT EXISTS `route` (
  `route_id` int(11) NOT NULL,
  `route_name` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `route`
--

INSERT INTO `route` (`route_id`, `route_name`) VALUES
(1, 'মিরপুর-মতিঝিল'),
(2, 'আব্দুল্লাহপুর-মতিঝিল'),
(3, 'গাবতলী-মতিঝিল'),
(4, 'গাবতলী-টঙ্গী');

-- --------------------------------------------------------

--
-- Table structure for table `route_details`
--

CREATE TABLE IF NOT EXISTS `route_details` (
  `route_details_id` int(11) NOT NULL,
  `route_id` tinyint(10) NOT NULL,
  `product_type_id` tinyint(2) NOT NULL,
  `product_id` int(11) NOT NULL,
  `start_point_id` tinyint(10) NOT NULL,
  `end_point_id` tinyint(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `start_end`
--

CREATE TABLE IF NOT EXISTS `start_end` (
  `start_end_id` int(11) NOT NULL,
  `start_end_point` varchar(20) NOT NULL,
  UNIQUE KEY `id` (`start_end_id`),
  UNIQUE KEY `id_2` (`start_end_id`),
  UNIQUE KEY `id_3` (`start_end_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `start_end`
--

INSERT INTO `start_end` (`start_end_id`, `start_end_point`) VALUES
(1, 'মিরপুর'),
(2, 'মতিঝিল');

-- --------------------------------------------------------

--
-- Table structure for table `status_history`
--

CREATE TABLE IF NOT EXISTS `status_history` (
  `change_id` tinyint(10) NOT NULL AUTO_INCREMENT,
  `product_type_id` tinyint(10) NOT NULL,
  `product_id` tinyint(10) NOT NULL,
  `delivery_to_id` tinyint(10) NOT NULL,
  `condition_id` tinyint(10) NOT NULL,
  `route_id` int(11) DEFAULT NULL,
  `driver_name` varchar(50) NOT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `change_remarks` varchar(100) NOT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'active',
  PRIMARY KEY (`change_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `tcar_info`
--

CREATE TABLE IF NOT EXISTS `tcar_info` (
  `tcar_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `product_type_id` int(11) NOT NULL DEFAULT '1',
  `no_of_seat` int(11) NOT NULL,
  `tcar_life` tinyint(5) NOT NULL,
  `tcar_description` varchar(500) NOT NULL,
  `engine_no` varchar(30) NOT NULL,
  `chesis_no` varchar(30) NOT NULL,
  `reg_no` varchar(30) NOT NULL,
  `reg_date` date NOT NULL,
  `delivery_status` varchar(10) NOT NULL,
  `delivery_to_id` int(11) NOT NULL DEFAULT '1',
  `condition_id` int(11) NOT NULL DEFAULT '1',
  `tcar_remarks` varchar(10000) NOT NULL,
  `tcar_status` varchar(10) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`tcar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `trip`
--

CREATE TABLE IF NOT EXISTS `trip` (
  `trip_id` int(11) NOT NULL,
  `product_type_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `trip_date` date NOT NULL,
  `start_time` time NOT NULL,
  `reach_time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `truck_info`
--

CREATE TABLE IF NOT EXISTS `truck_info` (
  `truck_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `product_type_id` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `truck_life` tinyint(5) NOT NULL,
  `truck_description` varchar(500) NOT NULL,
  `engine_no` varchar(30) NOT NULL,
  `chesis_no` varchar(30) NOT NULL,
  `reg_no` varchar(30) NOT NULL,
  `reg_date` date NOT NULL,
  `delivery_status` varchar(10) NOT NULL,
  `delivery_to_id` int(11) NOT NULL DEFAULT '1',
  `condition_id` int(11) NOT NULL DEFAULT '1',
  `truck_remarks` varchar(10000) NOT NULL,
  `truck_status` varchar(10) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`truck_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `user_type`
--

CREATE TABLE IF NOT EXISTS `user_type` (
  `user_type_id` int(10) NOT NULL,
  `user_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_type`
--

INSERT INTO `user_type` (`user_type_id`, `user_type`) VALUES
(1, 'সুপার এডমিন'),
(2, 'এডমিন'),
(3, 'প্রধান কার্যালয় এডমিন'),
(4, 'ডিপো এডমিন'),
(5, 'প্রশিক্ষণ ইনস্টিটিউট এডমিন'),
(6, 'ওয়ার্কশপ এডমিন');

-- --------------------------------------------------------

--
-- Structure for view `bus_delivery`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bus_delivery` AS select `bus_info`.`bus_id` AS `bus_id`,`bus_info`.`class_id` AS `class_id`,`bus_info`.`engine_no` AS `engine_no`,`bus_info`.`chesis_no` AS `chesis_no`,`bus_info`.`reg_no` AS `reg_no`,`bus_info`.`reg_date` AS `reg_date`,`bus_info`.`delivery_status` AS `delivery_status`,`product_class`.`class_name` AS `class_name`,`delivery`.`delivery_date` AS `delivery_date`,`delivery`.`product_type_id` AS `product_type_id`,`delivery`.`product_id` AS `product_id`,`delivery`.`delivery_from_id` AS `delivery_from_id`,`delivery`.`delivery_to_id` AS `delivery_to_id`,`delivery`.`status` AS `status` from ((`bus_info` left join `product_class` on((`bus_info`.`class_id` = `product_class`.`class_id`))) left join `delivery` on(((`bus_info`.`product_type_id` = `delivery`.`product_type_id`) and (`bus_info`.`bus_id` = `delivery`.`product_id`))));

