-- phpMyAdmin SQL Dump -- version 4.6.6deb5 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Mar 02, 2020 at 03:55 PM -- Server version: 5.7.29-0ubuntu0.18.04.1 -- PHP Version: 7.2.24-0ubuntu0.18.04.3 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `mod_db` -- -- -------------------------------------------------------- -- -- Table structure for table `failed_jobs` -- CREATE TABLE `failed_jobs` ( `id` bigint(20) UNSIGNED NOT NULL, `connection` text COLLATE utf8_unicode_ci NOT NULL, `queue` text COLLATE utf8_unicode_ci NOT NULL, `payload` longtext COLLATE utf8_unicode_ci NOT NULL, `exception` longtext COLLATE utf8_unicode_ci NOT NULL, `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `m01_01registered_users` -- CREATE TABLE `m01_01registered_users` ( `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Yynnnnnn', `user_firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `user_lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `user_shortname` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `address_line_1` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `address_line_2` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `address_line_3` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `postcode` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `longitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `lattitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `mobile_no_1` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `mobile_no_2` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `email_address` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `type_of_id` varchar(5) COLLATE utf8_unicode_ci NOT NULL, `person_id` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `gender` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'M - Male, F - Female', `preferred_lang` varchar(5) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `m01_02user_svc` -- CREATE TABLE `m01_02user_svc` ( `usersvc_rowno` int(11) NOT NULL, `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `type_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - YES, 0 - No', `other_desc` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `m01_02user_svc` -- INSERT INTO `m01_02user_svc` (`usersvc_rowno`, `regno`, `type_code`, `active`, `other_desc`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES (77, '20000001', 'DE', '1', '', NULL, '10', NULL, '10'), (78, '20000002', 'AI', '1', '', NULL, '10', NULL, '10'), (79, '20000003', 'EQUSLR', '1', '', NULL, 'UI20000003', NULL, 'UI20000003'), (83, '20000006', 'DE', '1', '', NULL, 'UI20000006', NULL, 'UI20000006'), (84, '20000005', 'AI', '1', '', NULL, '20000005', NULL, '20000005'), (85, '20000007', 'DE', '1', '', NULL, 'UI20000007', NULL, 'UI20000007'), (86, '20000008', 'DE', '1', '', '2020-02-24 22:57:44', '', '2020-02-24 22:57:44', ''), (87, '20000009', 'AI', '1', '', NULL, 'UI20000009', NULL, 'UI20000009'); -- -------------------------------------------------------- -- -- Table structure for table `m01_04user_cattle` -- CREATE TABLE `m01_04user_cattle` ( `catle_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'regno+Yynnnnn', `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `breed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `tag_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `cattle_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `catle_dob` date NOT NULL, `preferred_semen` int(11) NOT NULL, `monthly_yield` decimal(10,2) NOT NULL COMMENT 'daily yield value', `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `m01_05aitech_status` -- CREATE TABLE `m01_05aitech_status` ( `rowno` int(11) NOT NULL, `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `engaged_satus` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'A - Available, N - Not Available', `current_bookingno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `assignments_accepted` int(11) NOT NULL, `assignments_success` int(11) NOT NULL, `assignments_declined` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='This table will carry a record only for AI technicians'; -- -------------------------------------------------------- -- -- Table structure for table `migrations` -- CREATE TABLE `migrations` ( `id` int(10) UNSIGNED NOT NULL, `migration` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `batch` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `password_resets` -- CREATE TABLE `password_resets` ( `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `token` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `s00_01settings` -- CREATE TABLE `s00_01settings` ( `setid` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `setval_numeric` decimal(10,2) NOT NULL, `setval_string` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `set_description` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `set_description_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `set_description_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `s01_00prodsvcs` -- CREATE TABLE `s01_00prodsvcs` ( `prodsvc_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `product_name_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `product_name_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `product_name_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `main_prodid` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `is_mainprod` char(1) COLLATE utf8_unicode_ci NOT NULL, `have_child` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '1 - Yes , 0 - No', `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s01_00prodsvcs` -- INSERT INTO `s01_00prodsvcs` (`prodsvc_id`, `product_name_en`, `product_name_si`, `product_name_ta`, `main_prodid`, `is_mainprod`, `have_child`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES ('CATL', 'Cattle', 'ගවයින්', 'கால்நடை', '', '1', '0', NULL, 'Admin', NULL, ''), ('EQU', 'Equipment', 'උපකරණ', 'உபகரணங்கள்', '', '1', '0', NULL, 'Admin', NULL, ''), ('FEED', 'Feed', '', '', '', '1', '1', NULL, 'Admin', NULL, ''), ('FEED_FOD', 'Fodder', 'සත්ව ආහාර', 'தீவனம்', 'FEED', '0', '0', NULL, 'Admin', NULL, ''), ('FEED_SIL', 'Silage', 'silage_SI', 'silage_TA', 'FEED', '0', '0', NULL, 'Admin', NULL, ''), ('SERV', 'Services', '', '', '', '1', '1', NULL, 'Admin', NULL, ''), ('SERV_AI', 'AI Service', 'AI Service_SI', 'AI Service_TA', 'SERV', '0', '0', NULL, 'Admin', NULL, ''); -- -------------------------------------------------------- -- -- Table structure for table `s01_01svcs_engaged` -- CREATE TABLE `s01_01svcs_engaged` ( `type_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `engd_svc_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `engd_svc_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `engd_svc_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s01_01svcs_engaged` -- INSERT INTO `s01_01svcs_engaged` (`type_code`, `engd_svc_en`, `engd_svc_si`, `engd_svc_ta`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES ('AI', 'AI Service Provider', 'AI සේවා සැපයුම්කරු', 'AI சேவை வழங்குநர்', NULL, '', NULL, ''), ('DE', 'Dairy Entrepreneur', 'Dairy_Entre_SI', 'Dairy_Entre_TA', NULL, '', NULL, ''), ('EQUSLR', 'Equipment Seller', 'Equipment Seller සිංහල', 'Equipment Seller தமிழ்', NULL, '', NULL, ''), ('FODDER', 'Fodder Cultivator', 'ආහාරයට ගන්නා වගාව', 'தீவன சாகுபடி', NULL, '', NULL, ''), ('OTHER', 'Other ...', 'වෙනත්...', 'மற்ற...', NULL, '', NULL, ''), ('SILAGE', 'Silage Enterpriser', 'Sileage En_SI', 'Sileage En_TA', NULL, '', NULL, ''); -- -------------------------------------------------------- -- -- Table structure for table `s01_fxdval` -- CREATE TABLE `s01_fxdval` ( `rowno` int(11) NOT NULL, `val_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `val_des_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `val_des_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `val_des_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `val_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `val_ord` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s01_fxdval` -- INSERT INTO `s01_fxdval` (`rowno`, `val_id`, `val_des_en`, `val_des_si`, `val_des_ta`, `val_type`, `val_ord`) VALUES (1, 'SMS', 'SMS', '', '', 'MSG_TYPE', 1), (2, 'EML', 'E-mail', '', '', 'MSG_TYPE', 2), (3, 'NIC', 'National Identity Card', 'ජාතික හැඳුනුම්පත', 'தேசிய அடையாள அட்டை', 'ID_TYPE', 1), (4, 'BR', 'Business Registration', 'ව්‍යාපාර ලියාපදිංචිය', 'தொழில் பதிவு', 'ID_TYPE', 2), (7, 'F', 'Female', 'ගැහැණු', 'பெண்', 'GENDER', 1), (8, 'SIN', 'Sinhala', 'සිංහල', 'சிங்களம்', 'LANGUAGE', 1), (9, 'ENG', 'English', 'ඉංග්‍රීසි', 'ஆங்கிலம்', 'LANGUAGE', 2), (10, 'TAM', 'Tamil', 'දෙමළ', 'தமிழ்', 'LANGUAGE', 3), (11, 'A', 'Available', 'ඇත', 'கிடைக்கும்', 'ENGAGED_STATUS', 1), (12, 'N', 'Not Available', 'නැත', 'கிடைக்கவில்லை', 'ENGAGED_STATUS', 2), (13, 'SILSA', 'Silage Sale', '', '', 'ADVERT_TYPE', 1), (14, 'SILPUR', 'Silage Purchase', '', '', 'ADVERT_TYPE', 2), (15, 'FODSA', 'Fodder Sale', '', '', 'ADVERT_TYPE', 3), (16, 'FODPUR', 'Fodder Purchase', '', '', 'ADVERT_TYPE', 4), (17, 'CATSA', 'Cattle Sale', '', '', 'ADVERT_TYPE', 5), (18, 'CATPUR', 'Cattle Purchase', '', '', 'ADVERT_TYPE', 6), (19, 'EQUSA', 'Equipment Sales', '', '', 'ADVERT_TYPE', 7), (20, 'EQUPUR', 'Equipment Purchasing', '', '', 'ADVERT_TYPE', 8), (21, 'AI', 'AI Technician', '', '', 'ADVERT_TYPE', 9), (22, '0', 'Not Rated', '', '', 'RATING', 1), (23, '1', 'Excellent', '', '', 'RATING', 2), (24, '2', 'Satisfactory', '', '', 'RATING', 3), (25, '9', 'Unsatisfactory', '', '', 'RATING', 4), (26, 'LAC', 'Lactating', '', '', 'PARENTING_STATUS', 1), (27, 'PRG', 'Pregnant', '', '', 'PARENTING_STATUS', 2), (28, 'S', 'Sold', '', '', 'SOLD_STATUS', 1), (29, 'A', 'Available', 'ඇත', 'கிடைக்கும்', 'SOLD_STATUS', 2), (30, 'O', 'Open', '', '', 'PURCH_STATUS', 1), (31, 'C', 'Close', '', '', 'PURCH_STATUS', 2), (32, 'PB', 'Polythene Bags', '', '', 'PACKAGING', 1), (33, 'BRL', 'Barrel', '', '', 'PACKAGING', 2), (34, 'BAL', 'Bale', '', '', 'PACKAGING', 3), (35, 'P', 'Private', '', '', 'AIORG_TYPE', 1), (36, 'G', 'Government', '', '', 'AIORG_TYPE', 2), (37, 'C', 'Confirmed', '', '', 'AI_RESPONSE', 1), (38, 'R', 'Rejected', '', '', 'AI_RESPONSE', 2), (39, 'E', 'Expired', '', '', 'BOOKING_STATUS', 1), (40, 'R', 'Cancelled', '', '', 'BOOKING_STATUS', 2), (41, 'C', 'Confirmed', '', '', 'BOOKING_STATUS', 3), (42, 'X', 'Pending', '', '', 'BOOKING_STATUS', 4), (43, 'SUC', 'Successful', '', '', 'PD_STATUS', 1), (44, 'NS', 'Not successful', '', '', 'PD_STATUS', 2), (46, '1', '1 Month', 'මාස 1 යි', '1 மாதம்', 'ADVERT_DURATION', 1), (47, '2', '2 Month', 'මාස 2 යි', '2 மாதம்', 'ADVERT_DURATION', 2), (48, '3', '3 Month', 'මාස 3 යි', '3 மாதம்', 'ADVERT_DURATION', 3), (49, 'A', 'Approved', 'අනුමත කර ඇත', 'அங்கீகரிக்கப்பட்டது', 'APPROVAL_STATUS', 1), (50, 'R', 'Rejected', '', '', 'APPROVAL_STATUS', 2), (51, 'P', 'Pending', '', '', 'APPROVAL_STATUS', 3), (52, 'ADVRT', 'Advertistment', '', '', 'DOCAPRVTYPE', 1), (53, 'REG', 'Registration', '', '', 'DOCAPRVTYPE', 2), (54, 'M', 'Male', 'පුරුෂ', 'ஆண்', 'GENDER', 2), (57, 'A', 'Available', 'ඇත', 'கிடைக்கும்', 'AVAILABLE_STATUS', 1), (58, 'S', 'Sold', '', 'விற்கப்பட்டது', 'AVAILABLE_STATUS', 2), (59, 'O', 'Open', '', 'திறந்துள்ளது', 'AVAILABLE_STATUS', 3), (60, 'C', 'Close', '', 'மூடப்பட்டது', 'AVAILABLE_STATUS', 4), (61, 'ACT', 'Active', 'සක්‍රීයයි', 'செயலில்', 'ACTIVE_STATUS', 1), (62, 'EXP', 'Expired', ' කල් ඉකුත් වි ඇත', 'காலாவதியான', 'ACTIVE_STATUS', 2), (63, 'DISC', 'Discontinued', 'අත්හිටුවා ඇත', 'நிறுத்தப்பட்டது', 'ACTIVE_STATUS', 3), (64, 'VET', 'VET', 'සිංහල', 'தமிழ்', 'PD_CONFIRM', 1), (65, 'AIT', 'AI Technician', 'AI කාර්මිකයා', 'AI தொழில்நுட்ப வல்லுநர்', 'PD_CONFIRM', 2), (66, 'ME', 'Farmer', 'ගොවියා', 'உழவர்', 'PD_CONFIRM', 3), (67, 'DRY', 'Dry', 'Dry_si', 'Dry_ta', 'PARENTING_STATUS', 3), (68, 'HEF', 'Heifer', 'Heifer_si', 'Heifer_ta', 'PARENTING_STATUS', 4), (69, 'CALF', 'Calf', 'Calf_si', 'Calf_ta', 'PARENTING_STATUS', 5); -- -------------------------------------------------------- -- -- Table structure for table `s02_01cattle_breed` -- CREATE TABLE `s02_01cattle_breed` ( `breed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `breed_name_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `breed_name_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `breed_name_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `s02_02equip_categories` -- CREATE TABLE `s02_02equip_categories` ( `equip_catid` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'EQYYnnnnnn', `equip_cat_name_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `equip_cat_name_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `equip_cat_name_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `s02_03ad_type` -- CREATE TABLE `s02_03ad_type` ( `ad_type_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `ad_type_des_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `ad_type_des_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `ad_type_des_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s02_03ad_type` -- INSERT INTO `s02_03ad_type` (`ad_type_id`, `ad_type_des_en`, `ad_type_des_si`, `ad_type_des_ta`, `created_at`, `updated_at`) VALUES ('BUYER', 'Buyers', 'ගැනීම', 'வாங்குபவர்', NULL, NULL), ('SELLER', 'Sellers', 'විකිණීම', 'விற்பவர்', NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `s02_03feed_type` -- CREATE TABLE `s02_03feed_type` ( `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `feed_type_des_en` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `feed_type_des_si` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `feed_type_des_ta` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s02_03feed_type` -- INSERT INTO `s02_03feed_type` (`feed_type`, `feed_type_des_en`, `feed_type_des_si`, `feed_type_des_ta`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES ('GHAY', 'Grass Hay', '', '', NULL, '', NULL, ''), ('MAIZ', 'Maize', 'බඩ ඉරිඟු', 'சோளம்', NULL, '', NULL, ''), ('OATS', 'Oats Grain', '', '', NULL, '', NULL, ''), ('PGRAZ', 'Pasture grazing', '', '', NULL, '', NULL, ''); -- -------------------------------------------------------- -- -- Table structure for table `s02_04semen_type` -- CREATE TABLE `s02_04semen_type` ( `record_id` int(11) NOT NULL, `sementype_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `sementype_des_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `sementype_des_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `sementype_des_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s02_04semen_type` -- INSERT INTO `s02_04semen_type` (`record_id`, `sementype_code`, `sementype_des_en`, `sementype_des_si`, `sementype_des_ta`) VALUES (1, 'JSY', 'Jersey', 'ජර්සි', 'ஜெர்சி'), (2, 'SHWL', 'Sahiwal', 'Sahiwal_si', 'Sahiwal_ta'), (3, 'UI', 'ui descp', '0', '0'); -- -------------------------------------------------------- -- -- Table structure for table `s03_01province` -- CREATE TABLE `s03_01province` ( `prvnce_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `prvnce_name_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `prvnce_name_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `prvnce_name_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s03_01province` -- INSERT INTO `s03_01province` (`prvnce_id`, `prvnce_name_en`, `prvnce_name_si`, `prvnce_name_ta`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES ('CENTRL', 'Central', 'මධ්‍යම', 'மத்திய', '2019-10-02 05:30:00', 'admin', NULL, ''), ('ESTRN', 'Eastern', 'නැගෙනහිර', ' கிழக்கு', '2019-10-02 05:30:00', 'admin', NULL, ''), ('NOTHRN', 'Northern', 'උතුර', 'வட', '2019-10-02 05:30:00', 'admin', NULL, ''), ('NRTH_CNTRL', 'North Central', 'උතුරු මැද', 'வட மத்திய', '2019-10-02 05:30:00', 'admin', NULL, ''), ('NRTH_WEST', 'North Western', 'වයඹ', 'வட மேல்', '2019-10-02 05:30:00', 'admin', NULL, ''), ('SABRGMW', 'Sabaragamuwa', 'සබරගමුව', 'சபரகமுவ', '2019-10-02 05:30:00', 'admin', NULL, ''), ('STHRN', 'Southern', 'දකුණ', 'தென்', '2019-10-02 05:30:00', 'admin', NULL, ''), ('UVA', 'Uva', 'ඌව', 'ஊவா', '2019-10-02 05:30:00', 'admin', NULL, ''), ('WSTRN', 'Western', 'බස්නාහිර', 'மேல்', '2019-10-02 05:30:00', 'admin', NULL, ''); -- -------------------------------------------------------- -- -- Table structure for table `s03_02district` -- CREATE TABLE `s03_02district` ( `dist_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `prvnce_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `dist_name_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_name_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_name_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_shortname_en` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_shortname_si` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `dist_shortname_ta` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `s03_02district` -- INSERT INTO `s03_02district` (`dist_id`, `prvnce_id`, `dist_name_en`, `dist_name_si`, `dist_name_ta`, `dist_shortname_en`, `dist_shortname_si`, `dist_shortname_ta`, `created_at`, `created_by`, `updated_at`, `edited_by`) VALUES ('AD', 'NRTH_CNTRL', 'Anuradhapura', 'අනුරාධපුර', 'அனுராதபுரம்', '', '', '', NULL, 'admin', NULL, ''), ('APR', 'ESTRN', 'Ampara', 'අම්පාර', 'அம்பாறை', '', '', '', NULL, 'admin', NULL, ''), ('BC', 'ESTRN', 'Batticaloa', 'මඩකලපුව', 'மட்டக்களப்பு', '', '', '', NULL, 'admin', NULL, ''), ('BD', 'UVA', 'Badulla', 'බදුල්ල', 'பதுளை', '', '', '', NULL, 'admin', NULL, ''), ('CO', 'WSTRN', 'Colombo', 'කොළඹ', 'கொழும்பு', '', '', '', NULL, 'admin', NULL, ''), ('GL', 'STHRN', 'Galle', 'ගාල්ල', 'காலி', '', '', '', NULL, 'admin', NULL, ''), ('GQ', 'WSTRN', 'Gampaha', 'ගම්පහ', 'கம்பஹா', '', '', '', NULL, 'admin', NULL, ''), ('HB', 'STHRN', 'Hambantota', 'හම්බන්තොට', 'அம்பாந்தோட்டை', '', '', '', NULL, 'admin', NULL, ''), ('JA', 'NOTHRN', 'Jaffna', 'යාපනය', 'யாழ்ப்பாணம்', '', '', '', NULL, 'admin', NULL, ''), ('KE', 'SABRGMW', 'Kegalle', 'කෑගල්ල', 'கேகாலை', '', '', '', NULL, 'admin', NULL, ''), ('KG', 'NRTH_WEST', 'Kurunegala', 'කුරුණෑගල', 'குருநாகல்', '', '', '', NULL, 'admin', NULL, ''), ('KI', 'NOTHRN', 'Kilinochchi', 'කිලිනොච්චි', 'கிளிநொச்சி', '', '', '', NULL, 'admin', NULL, ''), ('KT', 'WSTRN', 'Kalutara', 'කඵතර', 'களுத்துறை', '', '', '', NULL, 'admin', NULL, ''), ('KY', 'CENTRL', 'Kandy', 'මහනුවර', 'கண்டி', '', '', '', NULL, 'admin', NULL, ''), ('MB', 'NOTHRN', 'Mannar', 'මන්නාරම', 'மன்னார்', '', '', '', NULL, 'admin', NULL, ''), ('MH', 'STHRN', 'Matara', 'මාතර', 'மாத்தறை', '', '', '', NULL, 'admin', NULL, ''), ('MJ', 'UVA', 'Monaragala', 'මොණරාගල', 'மொணராகலை', '', '', '', NULL, 'admin', NULL, ''), ('MP', 'NOTHRN', 'Mullaitivu', 'මුලතිවු', 'முல்லைத்தீவு', '', '', '', NULL, 'admin', NULL, ''), ('MT', 'CENTRL', 'Matale', 'මාතලේ', 'மாத்தளை', '', '', '', NULL, 'admin', NULL, ''), ('NW', 'CENTRL', 'Nuwara Eliya', 'නුවරඑළිය', 'நுவரெலியா', '', '', '', NULL, 'admin', NULL, ''), ('PR', 'NRTH_CNTRL', 'Polonnaruwa', 'පොළොන්නරුව', 'பொலன்னறுவை', '', '', '', NULL, 'admin', NULL, ''), ('PX', 'NRTH_WEST', 'Puttalam', 'පුත්තලම', 'புத்தளம்', '', '', '', NULL, 'admin', NULL, ''), ('RN', 'SABRGMW', 'Ratnapura', 'රත්නපුර', 'இரத்தினபுரம்', '', '', '', NULL, 'admin', NULL, ''), ('TC', 'ESTRN', 'Trincomalee', 'ත්‍රිකුණාමළය', 'திருகோணமலை', '', '', '', NULL, 'admin', NULL, ''), ('VA', 'NOTHRN', 'Vavuniya', 'වවුනියා', 'வவுனியா', '', '', '', NULL, 'admin', NULL, ''); -- -------------------------------------------------------- -- -- Table structure for table `s04_01message_settings` -- CREATE TABLE `s04_01message_settings` ( `msg_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `msg_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `msgsetg_subject_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `msgsetg_subject_si` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `msgsetg_subject_ta` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `msgsetg_content_en` text COLLATE utf8_unicode_ci NOT NULL, `msgsetg_content_si` text COLLATE utf8_unicode_ci NOT NULL, `msgsetg_content_ta` text COLLATE utf8_unicode_ci NOT NULL, `msgsetg_footer_en` text COLLATE utf8_unicode_ci NOT NULL, `msgsetg_footer_si` text COLLATE utf8_unicode_ci NOT NULL, `msgsetg_footer_ta` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t00_00adverts_master` -- CREATE TABLE `t00_00adverts_master` ( `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'ADYYnnnnnn', `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `location_latitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `location_longitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `date_of_advert` datetime NOT NULL, `advert_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'from ad_type table', `prodsvc_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `dist_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `expires_on` date NOT NULL, `advert_duration` int(11) NOT NULL COMMENT 'No of months', `impressions` int(11) NOT NULL, `available_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available, O: Open , C: Close', `discontinued` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '1 - Discontinued, 0 - not discontinued', `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t00_00adverts_master_20200219` -- CREATE TABLE `t00_00adverts_master_20200219` ( `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'ADYYnnnnnn', `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `location_latitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `location_longitude` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `date_of_advert` datetime NOT NULL, `advert_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'from ad_type table', `prodsvc_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `dist_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `expires_on` date NOT NULL, `advert_duration` int(11) NOT NULL COMMENT 'No of months', `impressions` int(11) NOT NULL, `available_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available, O: Open , C: Close', `discontinued` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '1 - Discontinued, 0 - not discontinued', `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t00_01adverts_extended` -- CREATE TABLE `t00_01adverts_extended` ( `rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `prev_expiry_date` date NOT NULL, `prev_duration` int(11) NOT NULL COMMENT 'No of months', `impressions_topre_exp` int(11) NOT NULL, `new_expiry_date` date NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t00_02adverts_discontinued` -- CREATE TABLE `t00_02adverts_discontinued` ( `rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `made_atransaction` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '9' COMMENT '1- YES, 0 - NO, Default 9 - Not answered', `seller_rating` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0 - Not Rated , 1 - Excellent, 2 - Satisfactory , 9- Unsatisfactory , 0 - Default ', `seller_comments` text COLLATE utf8_unicode_ci NOT NULL, `buyer_rating` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0 - Not Rated , 1 - Excellent, 2 - Satisfactory , 9- Unsatisfactory , 0 - Default', `buyer_comments` text COLLATE utf8_unicode_ci NOT NULL, `date_discontinued` date NOT NULL, `rated_user` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t01_01cattle_forsale` -- CREATE TABLE `t01_01cattle_forsale` ( `ctlsale_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `date_created` datetime NOT NULL, `sale_remarks` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t01_02cattle_salebreeds` -- CREATE TABLE `t01_02cattle_salebreeds` ( `ctlsale_breed_rowno` int(11) NOT NULL, `ctlsale_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `breed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `gender` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'M - male , F - female', `no_of_cattle` int(11) NOT NULL DEFAULT '0', `status_ofparenting` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `status_prglac` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'status of pregnancy or lactation', `age_years` int(11) NOT NULL DEFAULT '0', `age_months` int(11) NOT NULL DEFAULT '0', `weight` decimal(10,3) NOT NULL DEFAULT '0.000', `price` decimal(10,2) NOT NULL DEFAULT '0.00', `comments` text COLLATE utf8_unicode_ci NOT NULL, `sold_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available', `available_bal` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t01_02cattle_salebreeds` -- DELIMITER $$ CREATE TRIGGER `after_t01_02cattle_salebreeds_insert` AFTER INSERT ON `t01_02cattle_salebreeds` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t01_01cattle_forsale WHERE t01_01cattle_forsale.ctlsale_rowno = NEW.ctlsale_rowno); IF((SELECT count(sold_status) FROM t01_02cattle_salebreeds WHERE t01_02cattle_salebreeds.ctlsale_rowno = NEW.ctlsale_rowno AND t01_02cattle_salebreeds.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t01_02cattle_salebreeds_update` AFTER UPDATE ON `t01_02cattle_salebreeds` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t01_01cattle_forsale WHERE t01_01cattle_forsale.ctlsale_rowno = NEW.ctlsale_rowno); IF((SELECT count(sold_status) FROM t01_02cattle_salebreeds WHERE t01_02cattle_salebreeds.ctlsale_rowno = NEW.ctlsale_rowno AND t01_02cattle_salebreeds.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t01_03cattle_forpurchasing` -- CREATE TABLE `t01_03cattle_forpurchasing` ( `ctlpur_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `date_created` datetime NOT NULL, `purch_remarks` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t01_04cattle_purchbreeds` -- CREATE TABLE `t01_04cattle_purchbreeds` ( `ctlpur_breed_rowno` int(11) NOT NULL, `ctlpur_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `breed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `gender` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'M - Male , F - Female', `no_of_cattle` int(11) NOT NULL, `purch_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'O: Open, C: Closed', `comments` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t01_04cattle_purchbreeds` -- DELIMITER $$ CREATE TRIGGER `after_t01_04cattle_purchbreeds_insert` AFTER INSERT ON `t01_04cattle_purchbreeds` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t01_03cattle_forpurchasing WHERE t01_03cattle_forpurchasing.ctlpur_rowno = NEW.ctlpur_rowno); IF((SELECT count(purch_status) FROM t01_04cattle_purchbreeds WHERE t01_04cattle_purchbreeds.ctlpur_rowno = NEW.ctlpur_rowno AND t01_04cattle_purchbreeds.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t01_04cattle_purchbreeds_update` AFTER UPDATE ON `t01_04cattle_purchbreeds` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t01_03cattle_forpurchasing WHERE t01_03cattle_forpurchasing.ctlpur_rowno = NEW.ctlpur_rowno); IF((SELECT count(purch_status) FROM t01_04cattle_purchbreeds WHERE t01_04cattle_purchbreeds.ctlpur_rowno = NEW.ctlpur_rowno AND t01_04cattle_purchbreeds.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t03_01silage_forsale` -- CREATE TABLE `t03_01silage_forsale` ( `slg_sale_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `price_perkg_atgate` decimal(10,2) NOT NULL DEFAULT '0.00', `nutrition_value` text COLLATE utf8_unicode_ci NOT NULL, `location` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t03_02silage_salefeed` -- CREATE TABLE `t03_02silage_salefeed` ( `slgsale_feed_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `slg_sale_rowno` int(11) NOT NULL, `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `qty_kg` decimal(10,3) NOT NULL, `no_of_packs` int(11) NOT NULL, `packaging` char(4) COLLATE utf8_unicode_ci NOT NULL COMMENT 'PB - Polythene Bags, BRL - Barrel, BAL- Bale', `price_per_kg` decimal(10,2) NOT NULL DEFAULT '0.00', `sold_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available', `available_bal` decimal(10,3) NOT NULL, `comments` text COLLATE utf8_unicode_ci NOT NULL, `image_path` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t03_02silage_salefeed` -- DELIMITER $$ CREATE TRIGGER `after_t03_02silage_salefeed_insert` AFTER INSERT ON `t03_02silage_salefeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t03_01silage_forsale WHERE t03_01silage_forsale.slg_sale_rowno = NEW.slg_sale_rowno); IF((SELECT count(sold_status) FROM t03_02silage_salefeed WHERE t03_02silage_salefeed.slg_sale_rowno = NEW.slg_sale_rowno AND t03_02silage_salefeed.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t03_02silage_salefeed_update` AFTER UPDATE ON `t03_02silage_salefeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t03_01silage_forsale WHERE t03_01silage_forsale.slg_sale_rowno = NEW.slg_sale_rowno); IF((SELECT count(sold_status) FROM t03_02silage_salefeed WHERE t03_02silage_salefeed.slg_sale_rowno = NEW.slg_sale_rowno AND t03_02silage_salefeed.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t03_03silage_forpurch` -- CREATE TABLE `t03_03silage_forpurch` ( `slg_purch_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `remarks` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t03_04silage_purchfeed` -- CREATE TABLE `t03_04silage_purchfeed` ( `slgpurch_feed_rowno` int(11) NOT NULL, `slg_purch_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `qty_kg` decimal(10,3) NOT NULL, `packaging` char(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'PB - Polythene Bags, BRL - Barrel, BAL- Bale', `purch_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'O: Open, C: Closed', `comments` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t03_04silage_purchfeed` -- DELIMITER $$ CREATE TRIGGER `after_t03_04silage_purchfeed_insert` AFTER INSERT ON `t03_04silage_purchfeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t03_03silage_forpurch WHERE t03_03silage_forpurch.slg_purch_rowno = NEW.slg_purch_rowno); IF((SELECT count(purch_status) FROM t03_04silage_purchfeed WHERE t03_04silage_purchfeed.slg_purch_rowno = NEW.slg_purch_rowno AND t03_04silage_purchfeed.slg_purch_rowno = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t03_04silage_purchfeed_update` AFTER UPDATE ON `t03_04silage_purchfeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t03_03silage_forpurch WHERE t03_03silage_forpurch.slg_purch_rowno = NEW.slg_purch_rowno); IF((SELECT count(purch_status) FROM t03_04silage_purchfeed WHERE t03_04silage_purchfeed.slg_purch_rowno = NEW.slg_purch_rowno AND t03_04silage_purchfeed.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t04_01fodder_forsale` -- CREATE TABLE `t04_01fodder_forsale` ( `fodder_sale_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `price_perkg_atgate` decimal(10,2) NOT NULL DEFAULT '0.00', `location` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t04_02fodder_salefeed` -- CREATE TABLE `t04_02fodder_salefeed` ( `fdrsale_feed_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `fodder_sale_rowno` int(11) NOT NULL, `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `qty_kg` decimal(10,3) NOT NULL, `no_of_packs` int(11) NOT NULL, `packaging` char(4) COLLATE utf8_unicode_ci NOT NULL COMMENT ' PB - Polythene Bags, BRL - Barrel, BAL- Bale', `sold_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available', `comments` text COLLATE utf8_unicode_ci NOT NULL, `image_path` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t04_02fodder_salefeed` -- DELIMITER $$ CREATE TRIGGER `after_t04_02fodder_salefeed_insert` AFTER INSERT ON `t04_02fodder_salefeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t04_01fodder_forsale WHERE t04_01fodder_forsale.fodder_sale_rowno = NEW.fodder_sale_rowno); IF((SELECT count(sold_status) FROM t04_02fodder_salefeed WHERE t04_02fodder_salefeed.fodder_sale_rowno = NEW.fodder_sale_rowno AND t04_02fodder_salefeed.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t04_02fodder_salefeed_update` AFTER UPDATE ON `t04_02fodder_salefeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t04_01fodder_forsale WHERE t04_01fodder_forsale.fodder_sale_rowno = NEW.fodder_sale_rowno); IF((SELECT count(sold_status) FROM t04_02fodder_salefeed WHERE t04_02fodder_salefeed.fodder_sale_rowno = NEW.fodder_sale_rowno AND t04_02fodder_salefeed.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t04_03fodder_forpurch` -- CREATE TABLE `t04_03fodder_forpurch` ( `fodder_purch_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t04_04fodder_purchfeed` -- CREATE TABLE `t04_04fodder_purchfeed` ( `fdrpurch_feed_rowno` int(11) NOT NULL, `fodder_purch_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `feed_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `packaging` char(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'PB - Polythene Bags, BRL - Barrel, BAL- Bale Fixed Vals', `qty_kg` decimal(10,3) NOT NULL, `purch_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'O: Open, C: Closed', `comments` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t04_04fodder_purchfeed` -- DELIMITER $$ CREATE TRIGGER `after_t04_04fodder_purchfeed_insert` AFTER INSERT ON `t04_04fodder_purchfeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t04_03fodder_forpurch WHERE t04_03fodder_forpurch.fodder_purch_rowno = NEW.fodder_purch_rowno); IF((SELECT count(purch_status) FROM t04_04fodder_purchfeed WHERE t04_04fodder_purchfeed.fodder_purch_rowno = NEW.fodder_purch_rowno AND t04_04fodder_purchfeed.fodder_purch_rowno = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t04_04fodder_purchfeed_update` AFTER UPDATE ON `t04_04fodder_purchfeed` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t04_03fodder_forpurch WHERE t04_03fodder_forpurch.fodder_purch_rowno = NEW.fodder_purch_rowno); IF((SELECT count(purch_status) FROM t04_04fodder_purchfeed WHERE t04_04fodder_purchfeed.fodder_purch_rowno = NEW.fodder_purch_rowno AND t04_04fodder_purchfeed.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t05_01aisvc_offered` -- CREATE TABLE `t05_01aisvc_offered` ( `ai_advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'AIYYnnnnnn', `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `ai_regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `ai_org_type` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'P - Private, G - Government', `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t05_01aisvc_vetranges` -- CREATE TABLE `t05_01aisvc_vetranges` ( `ai_vetrange_rowno` int(11) NOT NULL, `ai_advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `vet_range` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t05_02aitech_bookings` -- CREATE TABLE `t05_02aitech_bookings` ( `booking_no` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'BNYYnnnnnn', `ai_advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `farmer_regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `cattle_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `required_at` datetime NOT NULL, `preferred_semen` int(11) NOT NULL, `first_heat_call` datetime NOT NULL, `last_calving_date` date NOT NULL, `age` decimal(10,2) NOT NULL, `ai_times_repeated` int(11) NOT NULL DEFAULT '1', `ai_regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `time_acknowledged` datetime NOT NULL COMMENT 'Time the AI checked and marked', `ai_response` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'C - Confirmed, R - Rejected', `ai_reject_reason` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `booking_status` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'X' COMMENT 'E - expired, R - Cancelled, C - Confirmed, X - Not rated , Default X', `time_cancelled_byfarmer` datetime NOT NULL, `time_of_ai` datetime NOT NULL, `ait_rating` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT 'default :0 - not rated. , 1 - Excellent, 2 - Satisfactory , 9- Unsatisfactory', `ait_comments` text COLLATE utf8_unicode_ci NOT NULL, `ait_date_offeedback` date NOT NULL, `ait_semenused` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `de_rating` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT 'default :0 - not rated. , 1 - Excellent, 2 - Satisfactory , 9- Unsatisfactory', `de_comments` text COLLATE utf8_unicode_ci NOT NULL, `de_date_offeedback` date NOT NULL, `pd_status` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'X' COMMENT 'S - Successful, F - Not successful , X - Not rated: Default X', `pd_date` datetime NOT NULL, `pd_vet_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `pd_upload_document` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `pd_comments` text COLLATE utf8_unicode_ci NOT NULL, `tentative_delday` date NOT NULL, `pd_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `pd_recorded_on` datetime NOT NULL, `date_expired` datetime NOT NULL, `date_created` datetime NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t06_01equip_forsale` -- CREATE TABLE `t06_01equip_forsale` ( `equip_sale_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t06_02equip_sale_categories` -- CREATE TABLE `t06_02equip_sale_categories` ( `equip_salecat_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `equip_sale_rowno` int(11) NOT NULL, `equip_catid` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `equip_qty` int(11) NOT NULL, `equip_price` decimal(10,2) NOT NULL, `brand_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `equip_comments` text COLLATE utf8_unicode_ci NOT NULL, `sold_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'S: Sold, A: Available', `image_path` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t06_02equip_sale_categories` -- DELIMITER $$ CREATE TRIGGER `after_t06_02equip_sale_categories_insert` AFTER INSERT ON `t06_02equip_sale_categories` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t06_01equip_forsale WHERE t06_01equip_forsale.equip_sale_rowno = NEW.equip_sale_rowno); IF((SELECT count(sold_status) FROM t06_02equip_sale_categories WHERE t06_02equip_sale_categories.equip_sale_rowno = NEW.equip_sale_rowno AND t06_02equip_sale_categories.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t06_02equip_sale_categories_update` AFTER UPDATE ON `t06_02equip_sale_categories` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t06_01equip_forsale WHERE t06_01equip_forsale.equip_sale_rowno = NEW.equip_sale_rowno); IF((SELECT count(sold_status) FROM t06_02equip_sale_categories WHERE t06_02equip_sale_categories.equip_sale_rowno = NEW.equip_sale_rowno AND t06_02equip_sale_categories.sold_status = 'A') > 0) THEN SET @available_status = 'A'; ELSE SET @available_status = 'S'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t06_03equip_forpurch` -- CREATE TABLE `t06_03equip_forpurch` ( `equip_purch_rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t06_04equip_purch_categories` -- CREATE TABLE `t06_04equip_purch_categories` ( `equip_purchcat_rowno` int(11) NOT NULL, `equip_purch_rowno` int(11) NOT NULL, `seq_no` int(11) NOT NULL, `equip_catid` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `equip_qty` int(11) NOT NULL, `equip_purch_comments` text COLLATE utf8_unicode_ci NOT NULL, `purch_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'O: Open, C: Closed', `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Triggers `t06_04equip_purch_categories` -- DELIMITER $$ CREATE TRIGGER `after_t06_04equip_purch_categories_insert` AFTER INSERT ON `t06_04equip_purch_categories` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t06_03equip_forpurch WHERE t06_03equip_forpurch.equip_purch_rowno = NEW.equip_purch_rowno); IF((SELECT count(purch_status) FROM t06_04equip_purch_categories WHERE t06_04equip_purch_categories.equip_purch_rowno = NEW.equip_purch_rowno AND t06_04equip_purch_categories.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `after_t06_04equip_purch_categories_update` AFTER UPDATE ON `t06_04equip_purch_categories` FOR EACH ROW BEGIN DECLARE InverterID VARCHAR(10); SET InverterID = (SELECT advert_id FROM t06_03equip_forpurch WHERE t06_03equip_forpurch.equip_purch_rowno = NEW.equip_purch_rowno); IF((SELECT count(purch_status) FROM t06_04equip_purch_categories WHERE t06_04equip_purch_categories.equip_purch_rowno = NEW.equip_purch_rowno AND t06_04equip_purch_categories.purch_status = 'O') > 0) THEN SET @available_status = 'O'; ELSE SET @available_status = 'C'; END IF; UPDATE t00_00adverts_master SET `available_status`= @available_status WHERE `advert_id` = InverterID; END $$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `t10_00docapprovals` -- CREATE TABLE `t10_00docapprovals` ( `rowno` int(11) NOT NULL, `ref_doc_type` char(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'from fixval', `ref_doc_no` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `approval_status` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'P' COMMENT 'A - Approved, P - Pending, R - Rejected', `date_submitted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `date_decided` datetime NOT NULL, `approver_comment` text COLLATE utf8_unicode_ci NOT NULL, `user_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t11_01messages_sent` -- CREATE TABLE `t11_01messages_sent` ( `rowno` int(11) NOT NULL, `msg_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `msg_reference` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `sent_to_email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `sent_to_phone` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `sntmsg_from` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `sntmsg_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `sntmsg_contents` text COLLATE utf8_unicode_ci NOT NULL, `sntmsg_subject` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `sntmsg_footer` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `t12_00images` -- CREATE TABLE `t12_00images` ( `rowno` int(11) NOT NULL, `advert_id` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `cat_rowno` int(11) NOT NULL, `image_path` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `u001_00user_roles` -- CREATE TABLE `u001_00user_roles` ( `role_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `role_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `is_Active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - Active, 0 - inactive, default:1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `u001_02users` -- CREATE TABLE `u001_02users` ( `user_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `role_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `mobile` varchar(12) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(500) COLLATE utf8_unicode_ci NOT NULL, `is_active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - Active, 0 - inactive, default:1', `login_cnt` int(11) NOT NULL, `lst_pwd_chng_date` datetime DEFAULT NULL, `last_login_date` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `u001_02users_20191028` -- CREATE TABLE `u001_02users_20191028` ( `user_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `regno` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `role_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(500) COLLATE utf8_unicode_ci NOT NULL, `is_active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - Active, 0 - inactive, default:1', `login_cnt` int(11) NOT NULL, `lst_pwd_chng_date` datetime NOT NULL, `last_login_date` datetime NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `updated_at` timestamp NULL DEFAULT NULL, `edited_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `u002_00classes` -- CREATE TABLE `u002_00classes` ( `class_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `cls_option_des` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `class_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `is_active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '1 - Active, 0 - inactive', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `u002_00classes` -- INSERT INTO `u002_00classes` (`class_id`, `cls_option_des`, `class_name`, `is_active`, `created_at`, `updated_at`) VALUES ('ADCATL', 'Advertistment Cattle Controller', 'AdvertCattleController', '1', '2019-11-04 05:48:00', NULL), ('ADEQU', 'Advertistment Equipment Controller', 'AdvertEquipController', '1', '2019-11-05 03:04:00', NULL), ('ADFEEDPUR', 'Advertistment Silage Fodder Purchase Controller', 'AdvertSilFoddPurchController', '1', '2019-11-18 10:45:00', NULL), ('ADFEEDSALE', 'Advertistment Silage Fodder Sale Controller', 'AdvertSilFoddSaleController', '1', '2019-11-05 06:22:00', NULL), ('ADMINADVRT', 'Admin Advertisements Controller', 'AdminAdvertisementsController', '1', '2019-12-04 05:48:00', NULL), ('ADMINRGUSR', 'Admin Register User Controller', 'AdminRegisterController', '1', '2019-12-04 08:11:00', NULL), ('ADVERT', 'Advertisements Controller', 'AdvertisementsController', '1', '2019-10-07 05:48:00', NULL), ('AIBOOK', 'AI Booking Controller', 'AIBookingController', '1', '2020-01-16 10:41:00', NULL), ('AIFEEDBACK', 'AI FeedbackController', 'AIFeedbackController', '1', '2020-01-22 09:38:00', NULL), ('CRON', 'Cron Controller', 'CronController', '1', '2019-12-23 06:27:00', NULL), ('DISTRICT', 'District Controller', 'DistrictController', '1', '2019-10-09 10:20:00', NULL), ('LOGAPI', 'Login API Controller', 'LoginApiController', '1', '2019-10-07 05:03:00', NULL), ('LOGWEB', 'Login WEB Controller', 'LoginWebController', '1', '2019-10-07 05:48:00', NULL), ('PDCHECK', 'PD Check Controller', 'PDCheckController', '1', '2020-01-23 07:54:00', NULL), ('PROFILE', 'Profile Controller', 'ProfileController', '1', '2019-10-25 05:48:00', NULL), ('REGUSER', 'Register User Controller', 'RegisterController', '1', '2019-11-19 08:11:00', NULL); -- -------------------------------------------------------- -- -- Table structure for table `u02_01_user_role_uri` -- CREATE TABLE `u02_01_user_role_uri` ( `role_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `uri_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `u02_01_user_role_uri` -- INSERT INTO `u02_01_user_role_uri` (`role_id`, `uri_id`, `created_at`, `updated_at`) VALUES ('ADMIN', 'URI00001', '2019-10-07 05:13:00', NULL), ('ADMIN', 'URI00003', '2019-10-09 10:24:00', NULL), ('ADMIN', 'URI00029', '2019-11-18 10:50:00', NULL), ('USER', 'URI00002', '2019-10-07 05:13:00', NULL), ('USER', 'URI00003', '2019-10-09 10:24:00', NULL), ('USER', 'URI00004', '2019-10-17 07:48:00', NULL), ('USER', 'URI00005', '2019-10-25 07:48:00', NULL), ('USER', 'URI00006', '2019-10-25 07:48:00', NULL), ('USER', 'URI00007', '2019-11-05 03:05:00', NULL), ('USER', 'URI00008', '2019-11-05 06:05:00', NULL), ('USER', 'URI00009', '2019-11-06 05:54:00', NULL), ('USER', 'URI00010', '2019-11-06 08:39:00', NULL), ('USER', 'URI00011', '2019-11-06 10:18:00', NULL), ('USER', 'URI00012', '2019-11-07 05:01:00', NULL), ('USER', 'URI00013', '2019-11-06 20:21:00', NULL), ('USER', 'URI00014', '2019-11-12 20:31:00', NULL), ('USER', 'URI00015', '2019-11-12 21:01:00', NULL), ('USER', 'URI00016', '2019-11-12 21:17:00', NULL), ('USER', 'URI00017', '2019-11-12 21:17:00', NULL), ('USER', 'URI00018', '2019-11-14 05:35:00', NULL), ('USER', 'URI00019', '2019-11-14 05:35:00', NULL), ('USER', 'URI00020', '2019-11-14 05:35:00', NULL), ('USER', 'URI00021', '2019-11-15 03:56:00', NULL), ('USER', 'URI00022', '2019-11-15 05:45:00', NULL), ('USER', 'URI00023', '2019-11-15 06:03:00', NULL), ('USER', 'URI00024', '2019-11-15 06:03:00', NULL), ('USER', 'URI00025', '2019-11-18 10:50:00', NULL), ('USER', 'URI00026', '2019-11-18 10:50:00', NULL), ('USER', 'URI00027', '2019-11-18 10:50:00', NULL), ('USER', 'URI00028', '2019-11-18 10:50:00', NULL), ('USER', 'URI00030', '2019-12-09 05:45:00', NULL), ('USER', 'URI00031', '2019-12-09 09:45:00', NULL), ('USER', 'URI00032', '2019-12-09 09:45:00', NULL), ('USER', 'URI00033', '2019-12-09 09:45:00', NULL), ('USER', 'URI00034', '2019-12-12 04:25:00', NULL), ('USER', 'URI00035', '2019-12-12 04:25:00', NULL), ('USER', 'URI00036', '2020-01-16 10:45:00', NULL), ('USER', 'URI00037', '2020-01-17 04:33:00', NULL), ('USER', 'URI00038', '2020-01-20 03:11:00', NULL), ('USER', 'URI00039', '2020-01-21 05:00:00', NULL), ('USER', 'URI00040', '2020-01-21 08:32:00', NULL), ('USER', 'URI00041', '2020-01-21 08:53:00', NULL), ('USER', 'URI00042', '2020-01-22 09:41:00', NULL), ('USER', 'URI00043', '2020-01-23 04:20:00', NULL), ('USER', 'URI00044', '2020-01-23 08:40:00', NULL), ('USER', 'URI00045', '2020-01-24 03:05:00', NULL), ('USER', 'URI00046', '2020-01-24 05:05:00', NULL); -- -------------------------------------------------------- -- -- Table structure for table `u03_00_uri_list` -- CREATE TABLE `u03_00_uri_list` ( `uri_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `uri` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `uri_des` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `uri_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `url_method` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `method` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `class_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `allow_mob_access` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '1 - Yes, 0 - no', `allow_web_access` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - Yes, 0 - no', `is_active` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1 - Active, 0 - inactive', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `u03_00_uri_list` -- INSERT INTO `u03_00_uri_list` (`uri_id`, `uri`, `uri_des`, `uri_name`, `url_method`, `method`, `class_id`, `allow_mob_access`, `allow_web_access`, `is_active`, `created_at`, `updated_at`) VALUES ('URI00001', 'login_web', '', 'loginweb', 'POST', 'Login ', 'LOGWEB', '0', '1', '1', '2019-10-07 06:07:00', NULL), ('URI00002', 'login_api', '', 'loginapi', 'POST', 'Login ', 'LOGAPI', '0', '0', '1', '2019-10-07 06:07:00', NULL), ('URI00003', 'district/{province}', 'district for province', 'districtlistforprovince', 'GET', 'getDistForProvince', 'DISTRICT', '0', '1', '1', '2019-10-09 10:22:00', NULL), ('URI00004', 'searchai', '', 'aisearch', 'GET', 'aiNearMe', 'ADVERT', '1', '0', '1', NULL, NULL), ('URI00005', 'myprofile', '', 'user_profile', 'GET', 'myProfileView', 'PROFILE', '1', '0', '1', NULL, NULL), ('URI00006', 'catle_adsave', '', 'catle_advert_create', 'POST', 'createCatlAd', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00007', 'equp_adsave', '', 'equip_advert_create', 'POST', 'createEquipAd', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00008', 'silafodd_sale_adsave', '', 'silafodd_sale_advert_create', 'POST', 'createSilaFoddSaleAd', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00009', 'pre_cat_load', '', 'load_pre_cat', 'POST', 'getPreBreed', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00010', 'update_catlbreed', '', 'update_cattle_breed', 'PUT', 'updateCattleBreed', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00011', 'delete_catlbreed', '', 'delete_cattle_breed', 'DELETE', 'deleteCattleBreed', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00012', 'dpdwn_catl_ad', '', 'dpdwn_catl_advert_create', 'GET', 'dropDownDataForCreateAd', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00013', 'pre_equip_load', '', 'load_pre_equip', 'POST', 'getPreCategory', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00014', 'update_equip_cate', '', 'update_equip_category', 'PUT', 'updateEquipAd', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00015', 'delete_equip_cate', '', 'delete_equip_category', 'DELETE', 'deleteEquipAd', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00016', 'dpdwn_equip_ad', '', 'equip_ad_dropdown', 'GET', 'dropDownDataForCreateEquipmentAd', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00017', 'dpdwn_feed_ad', '', 'feed_ad_dropdown', 'GET', 'dropDownDataForCreateFeedAd', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00018', 'pre_feed_load', '', 'load_pre_feed', 'POST', 'getPreFeed', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00019', 'next_feed_load', '', 'load_next_feed', 'POST', 'getNextFeed', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00020', 'update_feed_sellad', '', 'update_feed_sell_ad', 'PUT', 'updateFeedSellAd', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00021', 'delete_feed_sellad', '', 'delete_feed_sell_ad', 'DELETE', 'deleteFeedSellAd', 'ADFEEDSALE', '1', '0', '1', NULL, NULL), ('URI00022', 'next_cat_load', '', 'load_next_cat', 'POST', 'getNextBreed', 'ADCATL', '1', '0', '1', NULL, NULL), ('URI00023', 'next_equip_load', '', 'load_next_equip', 'POST', 'getNextCategory', 'ADEQU', '1', '0', '1', NULL, NULL), ('URI00024', 'silafodd_purch_adsave', '', 'silafodd_purch_advert_create', 'POST', 'createSilaFoddPurchAd', 'ADFEEDPUR', '1', '0', '1', NULL, NULL), ('URI00025', 'pre_purch_feed_load', '', 'load_purch_pre_feed', 'POST', 'getPrePurchFeed', 'ADFEEDPUR', '1', '0', '1', NULL, NULL), ('URI00026', 'next_purch_feed_load', '', 'load_purch_next_feed', 'POST', 'getNextPurchFeed', 'ADFEEDPUR', '1', '0', '1', NULL, NULL), ('URI00027', 'update_feed_purchad', '', 'update_feed_purch_ad', 'PUT', 'updateFeedPurchAd', 'ADFEEDPUR', '1', '0', '1', NULL, NULL), ('URI00028', 'delete_feed_purchad', '', 'delete_feed_purch_ad', 'DELETE', 'deleteFeedPurchAd', 'ADFEEDPUR', '1', '0', '1', NULL, NULL), ('URI00029', 'regusersdrp', '', 'regusers_search_drpdwn', 'GET', 'searchRegUserDropDown', 'REGUSER', '0', '1', '1', '2019-11-19 08:12:00', NULL), ('URI00030', 'extendadvert', '', 'extend_advertistment', 'POST', 'extendAd', 'ADVERT', '1', '0', '1', '2019-12-10 09:56:00', NULL), ('URI00031', 'srchadmaster', '', 'mob_admaster_search_drpdwn', 'GET', 'searchAdDropDown', 'ADVERT', '1', '0', '1', '2019-12-09 05:44:00', NULL), ('URI00032', 'srchmyadverts', '', 'srch_my_ads', 'POST', 'srchMyAdverts', 'ADVERT', '1', '0', '1', '2019-12-09 09:44:00', NULL), ('URI00033', 'showtoextend/{advert_id}', '', 'show_to_extend', 'GET', 'ShowtoExtend', 'ADVERT', '1', '0', '1', '2019-12-10 09:20:00', NULL), ('URI00034', 'showtodiscontinue/{advert_id}', '', 'show_to_discontinue', 'GET', 'ShowtoDiscontinueNRate', 'ADVERT', '1', '0', '1', '2019-12-12 04:25:00', NULL), ('URI00035', 'discontinuenrate', '', 'discontinue_n_rate', 'POST', 'DiscontinueNRate', 'ADVERT', '1', '0', '1', '2019-12-12 04:25:00', NULL), ('URI00036', 'get_aibooking_drpdwn', '', 'get_aibooking_drpdwn', 'GET', 'getAIBookingDrpdwn', 'AIBOOK', '1', '0', '1', '2020-01-16 10:44:00', '2020-01-16 10:44:00'), ('URI00037', 'insert_aibooking', '', 'insert_aibooking', 'POST', 'bookAI', 'AIBOOK', '1', '0', '1', '2020-01-17 04:31:00', '2020-01-17 04:31:00'), ('URI00038', 'get_ai_det_for_cow/{cattle_id}', 'get AI_details for cow', 'get_ai_det_for_cow', 'GET', 'getAIDetforCow', 'AIBOOK', '1', '0', '1', '2020-01-20 03:11:00', '2020-01-20 03:11:00'), ('URI00039', 'get_my_boooking/{booking_id?}', 'get my booking details', 'get_my_booking', 'POST', 'getMyBooking', 'AIBOOK', '1', '0', '1', '2020-01-21 04:59:00', '2020-01-21 04:59:00'), ('URI00040', 'booking_search_dropdown', 'booking search dropdown', 'booking_search_dropdown', 'GET', 'bookingSearchDropdown', 'AIBOOK', '1', '0', '1', '2020-01-21 08:32:00', '2020-01-21 08:32:00'), ('URI00041', 'booking_response/{booking_id}', 'booking response', 'booking_response', 'PUT', 'bookingResponse', 'AIBOOK', '1', '0', '1', '2020-01-21 09:50:00', '2020-01-21 09:50:00'), ('URI00042', 'ai_feedback_dropdown/{booking_id}', 'ai feedback dropdown', 'ai_feedback_dropdown', 'GET', 'aiFeedbackDropdown', 'AIFEEDBACK', '1', '0', '1', '2020-01-22 09:38:00', '2020-01-22 09:38:00'), ('URI00043', 'save_feedback/{booking_id}', 'save feedback', 'save_feedback', 'PUT', 'saveFeedback', 'AIFEEDBACK', '1', '0', '1', '2020-01-23 04:18:00', '2020-01-23 04:18:00'), ('URI00044', 'pd_check_cattle_data/{cattle_id}', 'pd check form cattle data', 'pd_check_cattle_data', 'GET', 'pdCheckCattleData', 'PDCHECK', '1', '0', '1', '2020-01-23 08:40:00', '2020-01-23 08:40:00'), ('URI00045', 'pd_check_data', 'pd check form data', 'pd_check_data', 'GET', 'pdCheckData', 'PDCHECK', '1', '0', '1', '2020-01-24 03:05:00', '2020-01-24 03:05:00'), ('URI00046', 'save_pd_check/{booking_id}', 'save pd check data', 'save_pd_check_data', 'POST', 'pdCheckDataSave', 'PDCHECK', '1', '0', '1', '2020-01-24 04:57:00', '2020-01-24 04:57:00'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Stand-in structure for view `vw_aidetails` -- (See below for the actual view) -- CREATE TABLE `vw_aidetails` ( `ai_advert_id` varchar(10) ,`ai_regno` varchar(10) ,`regno` varchar(10) ,`user_firstname` varchar(100) ,`user_lastname` varchar(100) ,`user_shortname` varchar(100) ,`address_line_1` varchar(100) ,`address_line_2` varchar(100) ,`address_line_3` varchar(100) ,`postcode` varchar(50) ,`dist_id` varchar(10) ,`longitude` varchar(10) ,`lattitude` varchar(10) ,`mobile_no_1` varchar(10) ,`mobile_no_2` varchar(10) ,`email_address` varchar(100) ,`type_of_id` varchar(5) ,`person_id` varchar(15) ,`gender` char(1) ,`preferred_lang` varchar(5) ,`created_at` timestamp ,`created_by` varchar(10) ,`updated_at` timestamp ,`edited_by` varchar(10) ); -- -------------------------------------------------------- -- -- Stand-in structure for view `vw_userrl_all_uri` -- (See below for the actual view) -- CREATE TABLE `vw_userrl_all_uri` ( `role_id` varchar(10) ,`uri_id` varchar(10) ,`uri` varchar(200) ,`uri_des` varchar(100) ,`uri_name` varchar(50) ,`url_method` varchar(10) ,`method` varchar(50) ,`class_id` varchar(10) ,`allow_mob_access` char(1) ,`allow_web_access` char(1) ,`is_active` char(1) ,`created_at` timestamp ,`updated_at` timestamp ,`class_name` varchar(50) ); -- -------------------------------------------------------- -- -- Structure for view `vw_aidetails` -- DROP TABLE IF EXISTS `vw_aidetails`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_aidetails` AS select `t05_01aisvc_offered`.`ai_advert_id` AS `ai_advert_id`,`t05_01aisvc_offered`.`ai_regno` AS `ai_regno`,`m01_01registered_users`.`regno` AS `regno`,`m01_01registered_users`.`user_firstname` AS `user_firstname`,`m01_01registered_users`.`user_lastname` AS `user_lastname`,`m01_01registered_users`.`user_shortname` AS `user_shortname`,`m01_01registered_users`.`address_line_1` AS `address_line_1`,`m01_01registered_users`.`address_line_2` AS `address_line_2`,`m01_01registered_users`.`address_line_3` AS `address_line_3`,`m01_01registered_users`.`postcode` AS `postcode`,`m01_01registered_users`.`dist_id` AS `dist_id`,`m01_01registered_users`.`longitude` AS `longitude`,`m01_01registered_users`.`lattitude` AS `lattitude`,`m01_01registered_users`.`mobile_no_1` AS `mobile_no_1`,`m01_01registered_users`.`mobile_no_2` AS `mobile_no_2`,`m01_01registered_users`.`email_address` AS `email_address`,`m01_01registered_users`.`type_of_id` AS `type_of_id`,`m01_01registered_users`.`person_id` AS `person_id`,`m01_01registered_users`.`gender` AS `gender`,`m01_01registered_users`.`preferred_lang` AS `preferred_lang`,`m01_01registered_users`.`created_at` AS `created_at`,`m01_01registered_users`.`created_by` AS `created_by`,`m01_01registered_users`.`updated_at` AS `updated_at`,`m01_01registered_users`.`edited_by` AS `edited_by` from ((`t05_01aisvc_offered` join `t00_00adverts_master` on((`t00_00adverts_master`.`advert_id` = `t05_01aisvc_offered`.`advert_id`))) join `m01_01registered_users` on((`t00_00adverts_master`.`regno` = `m01_01registered_users`.`regno`))) ; -- -------------------------------------------------------- -- -- Structure for view `vw_userrl_all_uri` -- DROP TABLE IF EXISTS `vw_userrl_all_uri`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_userrl_all_uri` AS select `u02_01_user_role_uri`.`role_id` AS `role_id`,`u03_00_uri_list`.`uri_id` AS `uri_id`,`u03_00_uri_list`.`uri` AS `uri`,`u03_00_uri_list`.`uri_des` AS `uri_des`,`u03_00_uri_list`.`uri_name` AS `uri_name`,`u03_00_uri_list`.`url_method` AS `url_method`,`u03_00_uri_list`.`method` AS `method`,`u03_00_uri_list`.`class_id` AS `class_id`,`u03_00_uri_list`.`allow_mob_access` AS `allow_mob_access`,`u03_00_uri_list`.`allow_web_access` AS `allow_web_access`,`u03_00_uri_list`.`is_active` AS `is_active`,`u03_00_uri_list`.`created_at` AS `created_at`,`u03_00_uri_list`.`updated_at` AS `updated_at`,`u002_00classes`.`class_name` AS `class_name` from ((`u03_00_uri_list` join `u02_01_user_role_uri` on((`u03_00_uri_list`.`uri_id` = `u02_01_user_role_uri`.`uri_id`))) join `u002_00classes` on((`u03_00_uri_list`.`class_id` = `u002_00classes`.`class_id`))) ; -- -- Indexes for dumped tables -- -- -- Indexes for table `failed_jobs` -- ALTER TABLE `failed_jobs` ADD PRIMARY KEY (`id`); -- -- Indexes for table `m01_01registered_users` -- ALTER TABLE `m01_01registered_users` ADD PRIMARY KEY (`regno`), ADD KEY `fk_user_dist_id` (`dist_id`); -- -- Indexes for table `m01_02user_svc` -- ALTER TABLE `m01_02user_svc` ADD PRIMARY KEY (`usersvc_rowno`), ADD KEY `fk_usersvc_regno` (`regno`), ADD KEY `fk_usersvc_typecode` (`type_code`); -- -- Indexes for table `m01_04user_cattle` -- ALTER TABLE `m01_04user_cattle` ADD PRIMARY KEY (`catle_id`), ADD KEY `fk_user_cat_regno` (`regno`), ADD KEY `fk_user_cat_breed_type` (`breed_type`); -- -- Indexes for table `m01_05aitech_status` -- ALTER TABLE `m01_05aitech_status` ADD PRIMARY KEY (`rowno`), ADD KEY `fk_aitech_state_regno` (`regno`); -- -- Indexes for table `migrations` -- ALTER TABLE `migrations` ADD PRIMARY KEY (`id`); -- -- Indexes for table `password_resets` -- ALTER TABLE `password_resets` ADD KEY `password_resets_email_index` (`email`); -- -- Indexes for table `s00_01settings` -- ALTER TABLE `s00_01settings` ADD PRIMARY KEY (`setid`); -- -- Indexes for table `s01_00prodsvcs` -- ALTER TABLE `s01_00prodsvcs` ADD PRIMARY KEY (`prodsvc_id`); -- -- Indexes for table `s01_01svcs_engaged` -- ALTER TABLE `s01_01svcs_engaged` ADD PRIMARY KEY (`type_code`); -- -- Indexes for table `s01_fxdval` -- ALTER TABLE `s01_fxdval` ADD PRIMARY KEY (`rowno`); -- -- Indexes for table `s02_01cattle_breed` -- ALTER TABLE `s02_01cattle_breed` ADD PRIMARY KEY (`breed_type`); -- -- Indexes for table `s02_02equip_categories` -- ALTER TABLE `s02_02equip_categories` ADD PRIMARY KEY (`equip_catid`); -- -- Indexes for table `s02_03ad_type` -- ALTER TABLE `s02_03ad_type` ADD PRIMARY KEY (`ad_type_id`); -- -- Indexes for table `s02_03feed_type` -- ALTER TABLE `s02_03feed_type` ADD PRIMARY KEY (`feed_type`); -- -- Indexes for table `s02_04semen_type` -- ALTER TABLE `s02_04semen_type` ADD PRIMARY KEY (`record_id`); -- -- Indexes for table `s03_01province` -- ALTER TABLE `s03_01province` ADD PRIMARY KEY (`prvnce_id`); -- -- Indexes for table `s03_02district` -- ALTER TABLE `s03_02district` ADD PRIMARY KEY (`dist_id`), ADD KEY `fk_prvnce_id` (`prvnce_id`); -- -- Indexes for table `s04_01message_settings` -- ALTER TABLE `s04_01message_settings` ADD PRIMARY KEY (`msg_code`); -- -- Indexes for table `t00_00adverts_master` -- ALTER TABLE `t00_00adverts_master` ADD PRIMARY KEY (`advert_id`), ADD KEY `fk_ad_master_regno` (`regno`), ADD KEY `fk_ad_master_prodsvc_id` (`prodsvc_id`), ADD KEY `fk_ad_master_distid` (`dist_id`); -- -- Indexes for table `t00_00adverts_master_20200219` -- ALTER TABLE `t00_00adverts_master_20200219` ADD PRIMARY KEY (`advert_id`), ADD KEY `fk_ad_master_regno` (`regno`), ADD KEY `fk_ad_master_prodsvc_id` (`prodsvc_id`), ADD KEY `fk_ad_master_distid` (`dist_id`); -- -- Indexes for table `t00_01adverts_extended` -- ALTER TABLE `t00_01adverts_extended` ADD PRIMARY KEY (`rowno`), ADD KEY `fk_ad_extend_advert_id` (`advert_id`); -- -- Indexes for table `t00_02adverts_discontinued` -- ALTER TABLE `t00_02adverts_discontinued` ADD PRIMARY KEY (`rowno`), ADD KEY `fk_ad_discon_advert_id` (`advert_id`); -- -- Indexes for table `t01_01cattle_forsale` -- ALTER TABLE `t01_01cattle_forsale` ADD PRIMARY KEY (`ctlsale_rowno`), ADD KEY `fk_cat_forsale_advert_id` (`advert_id`); -- -- Indexes for table `t01_02cattle_salebreeds` -- ALTER TABLE `t01_02cattle_salebreeds` ADD PRIMARY KEY (`ctlsale_breed_rowno`), ADD KEY `fk_breeds_forsale_ctlsale_rowno` (`ctlsale_rowno`), ADD KEY `fk_breeds_forsale_breed_type` (`breed_type`); -- -- Indexes for table `t01_03cattle_forpurchasing` -- ALTER TABLE `t01_03cattle_forpurchasing` ADD PRIMARY KEY (`ctlpur_rowno`), ADD KEY `fk_cat_forpur_advert_id` (`advert_id`); -- -- Indexes for table `t01_04cattle_purchbreeds` -- ALTER TABLE `t01_04cattle_purchbreeds` ADD PRIMARY KEY (`ctlpur_breed_rowno`), ADD KEY `fk_bre_topur_breed_type` (`breed_type`), ADD KEY `fk_bre_topur_ctlpur_rowno` (`ctlpur_rowno`); -- -- Indexes for table `t03_01silage_forsale` -- ALTER TABLE `t03_01silage_forsale` ADD PRIMARY KEY (`slg_sale_rowno`), ADD KEY `fk_sil_forsale_advert_id` (`advert_id`); -- -- Indexes for table `t03_02silage_salefeed` -- ALTER TABLE `t03_02silage_salefeed` ADD PRIMARY KEY (`slgsale_feed_rowno`), ADD KEY `fk_silage_salefeed_slg_salerow` (`slg_sale_rowno`), ADD KEY `fk_silage_salefeed_feedtype` (`feed_type`); -- -- Indexes for table `t03_03silage_forpurch` -- ALTER TABLE `t03_03silage_forpurch` ADD PRIMARY KEY (`slg_purch_rowno`), ADD KEY `fk_sil_want_advert_id` (`advert_id`); -- -- Indexes for table `t03_04silage_purchfeed` -- ALTER TABLE `t03_04silage_purchfeed` ADD PRIMARY KEY (`slgpurch_feed_rowno`), ADD KEY `fk_sil_purfeed_slg_purrow` (`slg_purch_rowno`), ADD KEY `fk_sil_purfeed_feedtype` (`feed_type`); -- -- Indexes for table `t04_01fodder_forsale` -- ALTER TABLE `t04_01fodder_forsale` ADD PRIMARY KEY (`fodder_sale_rowno`), ADD KEY `fk_fod_forsale_advert_id` (`advert_id`); -- -- Indexes for table `t04_02fodder_salefeed` -- ALTER TABLE `t04_02fodder_salefeed` ADD PRIMARY KEY (`fdrsale_feed_rowno`), ADD KEY `fk_fodsale_fod_salerow` (`fodder_sale_rowno`), ADD KEY `fk_fodsale_feedtype` (`feed_type`); -- -- Indexes for table `t04_03fodder_forpurch` -- ALTER TABLE `t04_03fodder_forpurch` ADD PRIMARY KEY (`fodder_purch_rowno`), ADD KEY `fk_fodpur_advert_id` (`advert_id`), ADD KEY `fk_fodpur_feedtype` (`feed_type`); -- -- Indexes for table `t04_04fodder_purchfeed` -- ALTER TABLE `t04_04fodder_purchfeed` ADD PRIMARY KEY (`fdrpurch_feed_rowno`), ADD KEY `fk_fodpurfeed_fodder_purch_rowno` (`fodder_purch_rowno`), ADD KEY `fk_fodpurfeed_feedtype` (`feed_type`); -- -- Indexes for table `t05_01aisvc_offered` -- ALTER TABLE `t05_01aisvc_offered` ADD PRIMARY KEY (`ai_advert_id`), ADD KEY `fk_aisvc_advert_id` (`advert_id`); -- -- Indexes for table `t05_01aisvc_vetranges` -- ALTER TABLE `t05_01aisvc_vetranges` ADD PRIMARY KEY (`ai_vetrange_rowno`), ADD KEY `fk_aiscvven_ai_advert_id` (`ai_advert_id`); -- -- Indexes for table `t05_02aitech_bookings` -- ALTER TABLE `t05_02aitech_bookings` ADD PRIMARY KEY (`booking_no`), ADD KEY `fk_aitech_ai_advert_id` (`ai_advert_id`), ADD KEY `fk_aitech_cattle_id` (`cattle_id`); -- -- Indexes for table `t06_01equip_forsale` -- ALTER TABLE `t06_01equip_forsale` ADD PRIMARY KEY (`equip_sale_rowno`), ADD KEY `fk_equforsale_advert_id` (`advert_id`); -- -- Indexes for table `t06_02equip_sale_categories` -- ALTER TABLE `t06_02equip_sale_categories` ADD PRIMARY KEY (`equip_salecat_rowno`), ADD KEY `fk_equsalecat_equipsalerow` (`equip_sale_rowno`), ADD KEY `fk_equsalecat_equipcat` (`equip_catid`); -- -- Indexes for table `t06_03equip_forpurch` -- ALTER TABLE `t06_03equip_forpurch` ADD PRIMARY KEY (`equip_purch_rowno`), ADD KEY `fk_equippur_advertid` (`advert_id`); -- -- Indexes for table `t06_04equip_purch_categories` -- ALTER TABLE `t06_04equip_purch_categories` ADD PRIMARY KEY (`equip_purchcat_rowno`), ADD KEY `fk_equpucat_equpurrow` (`equip_purch_rowno`), ADD KEY `fk_equpucat_equcat` (`equip_catid`); -- -- Indexes for table `t10_00docapprovals` -- ALTER TABLE `t10_00docapprovals` ADD PRIMARY KEY (`rowno`), ADD KEY `fk_docapprov_userid` (`user_id`); -- -- Indexes for table `t11_01messages_sent` -- ALTER TABLE `t11_01messages_sent` ADD PRIMARY KEY (`rowno`), ADD KEY `fk_msgsent_msgcode` (`msg_code`), ADD KEY `fk_msgsent_regno` (`regno`); -- -- Indexes for table `t12_00images` -- ALTER TABLE `t12_00images` ADD PRIMARY KEY (`rowno`); -- -- Indexes for table `u001_00user_roles` -- ALTER TABLE `u001_00user_roles` ADD PRIMARY KEY (`role_id`); -- -- Indexes for table `u001_02users` -- ALTER TABLE `u001_02users` ADD PRIMARY KEY (`user_id`), ADD KEY `fk_users_role_id` (`role_id`); -- -- Indexes for table `u001_02users_20191028` -- ALTER TABLE `u001_02users_20191028` ADD PRIMARY KEY (`user_id`), ADD KEY `fk_users_role_id` (`role_id`); -- -- Indexes for table `u002_00classes` -- ALTER TABLE `u002_00classes` ADD PRIMARY KEY (`class_id`), ADD UNIQUE KEY `class_name` (`class_name`); -- -- Indexes for table `u02_01_user_role_uri` -- ALTER TABLE `u02_01_user_role_uri` ADD PRIMARY KEY (`role_id`,`uri_id`), ADD KEY `fk_usruri_uriid` (`uri_id`); -- -- Indexes for table `u03_00_uri_list` -- ALTER TABLE `u03_00_uri_list` ADD PRIMARY KEY (`uri_id`), ADD KEY `fk_urilist_classid` (`class_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `users_email_unique` (`email`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `failed_jobs` -- ALTER TABLE `failed_jobs` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `m01_02user_svc` -- ALTER TABLE `m01_02user_svc` MODIFY `usersvc_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=88; -- -- AUTO_INCREMENT for table `m01_05aitech_status` -- ALTER TABLE `m01_05aitech_status` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `migrations` -- ALTER TABLE `migrations` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `s01_fxdval` -- ALTER TABLE `s01_fxdval` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=70; -- -- AUTO_INCREMENT for table `s02_04semen_type` -- ALTER TABLE `s02_04semen_type` MODIFY `record_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `t00_01adverts_extended` -- ALTER TABLE `t00_01adverts_extended` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40; -- -- AUTO_INCREMENT for table `t00_02adverts_discontinued` -- ALTER TABLE `t00_02adverts_discontinued` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `t01_01cattle_forsale` -- ALTER TABLE `t01_01cattle_forsale` MODIFY `ctlsale_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=480; -- -- AUTO_INCREMENT for table `t01_02cattle_salebreeds` -- ALTER TABLE `t01_02cattle_salebreeds` MODIFY `ctlsale_breed_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=496; -- -- AUTO_INCREMENT for table `t01_03cattle_forpurchasing` -- ALTER TABLE `t01_03cattle_forpurchasing` MODIFY `ctlpur_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35; -- -- AUTO_INCREMENT for table `t01_04cattle_purchbreeds` -- ALTER TABLE `t01_04cattle_purchbreeds` MODIFY `ctlpur_breed_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37; -- -- AUTO_INCREMENT for table `t03_01silage_forsale` -- ALTER TABLE `t03_01silage_forsale` MODIFY `slg_sale_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=160; -- -- AUTO_INCREMENT for table `t03_02silage_salefeed` -- ALTER TABLE `t03_02silage_salefeed` MODIFY `slgsale_feed_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=468; -- -- AUTO_INCREMENT for table `t03_03silage_forpurch` -- ALTER TABLE `t03_03silage_forpurch` MODIFY `slg_purch_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=47; -- -- AUTO_INCREMENT for table `t03_04silage_purchfeed` -- ALTER TABLE `t03_04silage_purchfeed` MODIFY `slgpurch_feed_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133; -- -- AUTO_INCREMENT for table `t04_01fodder_forsale` -- ALTER TABLE `t04_01fodder_forsale` MODIFY `fodder_sale_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34; -- -- AUTO_INCREMENT for table `t04_02fodder_salefeed` -- ALTER TABLE `t04_02fodder_salefeed` MODIFY `fdrsale_feed_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=174; -- -- AUTO_INCREMENT for table `t04_03fodder_forpurch` -- ALTER TABLE `t04_03fodder_forpurch` MODIFY `fodder_purch_rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `t04_04fodder_purchfeed` -- ALTER TABLE `t04_04fodder_purchfeed` MODIFY `fdrpurch_feed_rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `t05_01aisvc_vetranges` -- ALTER TABLE `t05_01aisvc_vetranges` MODIFY `ai_vetrange_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=27; -- -- AUTO_INCREMENT for table `t06_01equip_forsale` -- ALTER TABLE `t06_01equip_forsale` MODIFY `equip_sale_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `t06_02equip_sale_categories` -- ALTER TABLE `t06_02equip_sale_categories` MODIFY `equip_salecat_rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; -- -- AUTO_INCREMENT for table `t06_03equip_forpurch` -- ALTER TABLE `t06_03equip_forpurch` MODIFY `equip_purch_rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `t06_04equip_purch_categories` -- ALTER TABLE `t06_04equip_purch_categories` MODIFY `equip_purchcat_rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `t10_00docapprovals` -- ALTER TABLE `t10_00docapprovals` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=412; -- -- AUTO_INCREMENT for table `t11_01messages_sent` -- ALTER TABLE `t11_01messages_sent` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `t12_00images` -- ALTER TABLE `t12_00images` MODIFY `rowno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26; -- -- Constraints for dumped tables -- -- -- Constraints for table `m01_01registered_users` -- ALTER TABLE `m01_01registered_users` ADD CONSTRAINT `fk_user_dist_id` FOREIGN KEY (`dist_id`) REFERENCES `s03_02district` (`dist_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `m01_02user_svc` -- ALTER TABLE `m01_02user_svc` ADD CONSTRAINT `fk_usersvc_regno` FOREIGN KEY (`regno`) REFERENCES `m01_01registered_users` (`regno`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_usersvc_typecode` FOREIGN KEY (`type_code`) REFERENCES `s01_01svcs_engaged` (`type_code`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `m01_04user_cattle` -- ALTER TABLE `m01_04user_cattle` ADD CONSTRAINT `fk_user_cat_breed_type` FOREIGN KEY (`breed_type`) REFERENCES `s02_01cattle_breed` (`breed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_user_cat_regno` FOREIGN KEY (`regno`) REFERENCES `m01_01registered_users` (`regno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `m01_05aitech_status` -- ALTER TABLE `m01_05aitech_status` ADD CONSTRAINT `fk_aitech_state_regno` FOREIGN KEY (`regno`) REFERENCES `m01_01registered_users` (`regno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `s03_02district` -- ALTER TABLE `s03_02district` ADD CONSTRAINT `fk_prvnce_id` FOREIGN KEY (`prvnce_id`) REFERENCES `s03_01province` (`prvnce_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t00_00adverts_master` -- ALTER TABLE `t00_00adverts_master` ADD CONSTRAINT `fk_ad_master_distid` FOREIGN KEY (`dist_id`) REFERENCES `s03_02district` (`dist_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_ad_master_prodsvc_id` FOREIGN KEY (`prodsvc_id`) REFERENCES `s01_00prodsvcs` (`prodsvc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_ad_master_regno` FOREIGN KEY (`regno`) REFERENCES `m01_01registered_users` (`regno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t00_01adverts_extended` -- ALTER TABLE `t00_01adverts_extended` ADD CONSTRAINT `fk_ad_extend_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t00_02adverts_discontinued` -- ALTER TABLE `t00_02adverts_discontinued` ADD CONSTRAINT `fk_ad_discon_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t01_01cattle_forsale` -- ALTER TABLE `t01_01cattle_forsale` ADD CONSTRAINT `fk_cat_forsale_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t01_02cattle_salebreeds` -- ALTER TABLE `t01_02cattle_salebreeds` ADD CONSTRAINT `fk_breeds_forsale_breed_type` FOREIGN KEY (`breed_type`) REFERENCES `s02_01cattle_breed` (`breed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_breeds_forsale_ctlsale_rowno` FOREIGN KEY (`ctlsale_rowno`) REFERENCES `t01_01cattle_forsale` (`ctlsale_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t01_03cattle_forpurchasing` -- ALTER TABLE `t01_03cattle_forpurchasing` ADD CONSTRAINT `fk_cat_forpur_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t01_04cattle_purchbreeds` -- ALTER TABLE `t01_04cattle_purchbreeds` ADD CONSTRAINT `fk_bre_topur_breed_type` FOREIGN KEY (`breed_type`) REFERENCES `s02_01cattle_breed` (`breed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_bre_topur_ctlpur_rowno` FOREIGN KEY (`ctlpur_rowno`) REFERENCES `t01_03cattle_forpurchasing` (`ctlpur_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t03_01silage_forsale` -- ALTER TABLE `t03_01silage_forsale` ADD CONSTRAINT `fk_sil_forsale_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t03_02silage_salefeed` -- ALTER TABLE `t03_02silage_salefeed` ADD CONSTRAINT `fk_silage_salefeed_feedtype` FOREIGN KEY (`feed_type`) REFERENCES `s02_03feed_type` (`feed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_silage_salefeed_slg_salerow` FOREIGN KEY (`slg_sale_rowno`) REFERENCES `t03_01silage_forsale` (`slg_sale_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t03_03silage_forpurch` -- ALTER TABLE `t03_03silage_forpurch` ADD CONSTRAINT `fk_sil_want_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t03_04silage_purchfeed` -- ALTER TABLE `t03_04silage_purchfeed` ADD CONSTRAINT `fk_sil_purfeed_feedtype` FOREIGN KEY (`feed_type`) REFERENCES `s02_03feed_type` (`feed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_sil_purfeed_slg_purrow` FOREIGN KEY (`slg_purch_rowno`) REFERENCES `t03_03silage_forpurch` (`slg_purch_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t04_01fodder_forsale` -- ALTER TABLE `t04_01fodder_forsale` ADD CONSTRAINT `fk_fod_forsale_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t04_02fodder_salefeed` -- ALTER TABLE `t04_02fodder_salefeed` ADD CONSTRAINT `fk_fodsale_feedtype` FOREIGN KEY (`feed_type`) REFERENCES `s02_03feed_type` (`feed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_fodsale_fod_salerow` FOREIGN KEY (`fodder_sale_rowno`) REFERENCES `t04_01fodder_forsale` (`fodder_sale_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t04_03fodder_forpurch` -- ALTER TABLE `t04_03fodder_forpurch` ADD CONSTRAINT `fk_fodpur_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_fodpur_feedtype` FOREIGN KEY (`feed_type`) REFERENCES `s02_03feed_type` (`feed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t04_04fodder_purchfeed` -- ALTER TABLE `t04_04fodder_purchfeed` ADD CONSTRAINT `fk_fodpurfeed_feedtype` FOREIGN KEY (`feed_type`) REFERENCES `s02_03feed_type` (`feed_type`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_fodpurfeed_fodder_purch_rowno` FOREIGN KEY (`fodder_purch_rowno`) REFERENCES `t04_03fodder_forpurch` (`fodder_purch_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t05_01aisvc_offered` -- ALTER TABLE `t05_01aisvc_offered` ADD CONSTRAINT `fk_aisvc_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t05_01aisvc_vetranges` -- ALTER TABLE `t05_01aisvc_vetranges` ADD CONSTRAINT `fk_aiscvven_ai_advert_id` FOREIGN KEY (`ai_advert_id`) REFERENCES `t05_01aisvc_offered` (`ai_advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t05_02aitech_bookings` -- ALTER TABLE `t05_02aitech_bookings` ADD CONSTRAINT `fk_aitech_ai_advert_id` FOREIGN KEY (`ai_advert_id`) REFERENCES `t05_01aisvc_offered` (`ai_advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_aitech_cattle_id` FOREIGN KEY (`cattle_id`) REFERENCES `m01_04user_cattle` (`catle_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t06_01equip_forsale` -- ALTER TABLE `t06_01equip_forsale` ADD CONSTRAINT `fk_equforsale_advert_id` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t06_02equip_sale_categories` -- ALTER TABLE `t06_02equip_sale_categories` ADD CONSTRAINT `fk_equsalecat_equipcat` FOREIGN KEY (`equip_catid`) REFERENCES `s02_02equip_categories` (`equip_catid`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_equsalecat_equipsalerow` FOREIGN KEY (`equip_sale_rowno`) REFERENCES `t06_01equip_forsale` (`equip_sale_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t06_03equip_forpurch` -- ALTER TABLE `t06_03equip_forpurch` ADD CONSTRAINT `fk_equippur_advertid` FOREIGN KEY (`advert_id`) REFERENCES `t00_00adverts_master` (`advert_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t06_04equip_purch_categories` -- ALTER TABLE `t06_04equip_purch_categories` ADD CONSTRAINT `fk_equpucat_equcat` FOREIGN KEY (`equip_catid`) REFERENCES `s02_02equip_categories` (`equip_catid`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_equpucat_equpurrow` FOREIGN KEY (`equip_purch_rowno`) REFERENCES `t06_03equip_forpurch` (`equip_purch_rowno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t10_00docapprovals` -- ALTER TABLE `t10_00docapprovals` ADD CONSTRAINT `fk_docapprov_userid` FOREIGN KEY (`user_id`) REFERENCES `u001_02users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `t11_01messages_sent` -- ALTER TABLE `t11_01messages_sent` ADD CONSTRAINT `fk_msgsent_msgcode` FOREIGN KEY (`msg_code`) REFERENCES `s04_01message_settings` (`msg_code`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_msgsent_regno` FOREIGN KEY (`regno`) REFERENCES `m01_01registered_users` (`regno`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `u001_02users` -- ALTER TABLE `u001_02users` ADD CONSTRAINT `fk_users_role_id` FOREIGN KEY (`role_id`) REFERENCES `u001_00user_roles` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `u02_01_user_role_uri` -- ALTER TABLE `u02_01_user_role_uri` ADD CONSTRAINT `fk_usruri_roleid` FOREIGN KEY (`role_id`) REFERENCES `u001_00user_roles` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_usruri_uriid` FOREIGN KEY (`uri_id`) REFERENCES `u03_00_uri_list` (`uri_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `u03_00_uri_list` -- ALTER TABLE `u03_00_uri_list` ADD CONSTRAINT `fk_urilist_classid` FOREIGN KEY (`class_id`) REFERENCES `u002_00classes` (`class_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;