-- phpMyAdmin SQL Dump
-- version 3.4.10.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 23, 2013 at 02:46 PM
-- Server version: 5.5.20
-- PHP Version: 5.3.10

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 utf8 */;

--
-- Database: `lsms`
--

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

--
-- Table structure for table `authassignment`
--

CREATE TABLE IF NOT EXISTS `authassignment` (
  `itemname` varchar(64) NOT NULL,
  `userid` varchar(64) NOT NULL,
  `bizrule` text,
  `data` text,
  PRIMARY KEY (`itemname`,`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `authassignment`
--

INSERT INTO `authassignment` (`itemname`, `userid`, `bizrule`, `data`) VALUES
('', '55', NULL, 'N;'),
('', '56', NULL, 'N;'),
('', '57', NULL, 'N;'),
('', '58', NULL, 'N;'),
('', '59', NULL, 'N;'),
('', '60', NULL, 'N;'),
('', '61', NULL, 'N;'),
('', '62', NULL, 'N;'),
('', '63', NULL, 'N;'),
('Facility Admin', '65', NULL, 'N;'),
('Facility Admin', '69', NULL, 'N;'),
('Super Admin', '53', NULL, 'N;'),
('Super Admin', '67', NULL, 'N;'),
('Super Admin', '68', NULL, 'N;'),
('System Admin', '48', NULL, 'N;'),
('System Admin', '49', NULL, 'N;'),
('System Admin', '66', NULL, 'N;');

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

--
-- Table structure for table `authitem`
--

CREATE TABLE IF NOT EXISTS `authitem` (
  `name` varchar(64) NOT NULL,
  `type` int(11) NOT NULL,
  `description` text,
  `bizrule` text,
  `data` text,
  `update_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `authitem`
--

INSERT INTO `authitem` (`name`, `type`, `description`, `bizrule`, `data`, `update_user_id`) VALUES
('Facility Admin', 2, NULL, NULL, NULL, NULL),
('Finance', 2, NULL, NULL, NULL, NULL),
('POS Admin', 2, NULL, NULL, NULL, NULL),
('Super Admin', 2, NULL, NULL, NULL, NULL),
('System Admin', 2, NULL, NULL, NULL, NULL),
('Food Manager', 2, NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `authitemchild`
--

CREATE TABLE IF NOT EXISTS `authitemchild` (
  `parent` varchar(64) NOT NULL,
  `child` varchar(64) NOT NULL,
  PRIMARY KEY (`parent`,`child`),
  KEY `FK_child_authitem` (`child`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `badge`
--

CREATE TABLE IF NOT EXISTS `badge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) NOT NULL,
  `type_id` int(11) NOT NULL,
  `person_id` int(11) DEFAULT NULL,
  `activation_start_date` date DEFAULT NULL,
  `activation_end_date` date DEFAULT NULL,
  `max_served_meals` tinyint(4) DEFAULT NULL,
  `status` enum('Free','Active','Inactive','Deleted','Lost') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_badge_badge_type1` (`type_id`),
  KEY `fk_badge_person1` (`person_id`),
  KEY `idex_badge_number` (`number`(191))
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=104 ;

--
-- Dumping data for table `badge`
--

INSERT INTO `badge` (`id`, `number`, `type_id`, `person_id`, `activation_start_date`, `activation_end_date`, `max_served_meals`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(87, '00000001', 1, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:07:11', 68, '2013-02-12 08:36:10'),
(91, '00000002', 1, 133, '2013-02-01', '2014-02-01', 10, 'Active', 68, '2013-02-10 15:10:49', 69, '2013-02-23 16:25:07'),
(92, '00000003', 1, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-21 14:45:18'),
(93, '00000004', 2, 130, '2013-02-01', '2013-08-01', 2, 'Active', 68, '2013-02-10 15:10:49', 69, '2013-02-23 16:35:05'),
(94, '00000005', 2, NULL, NULL, NULL, NULL, 'Deleted', 68, '2013-02-10 15:10:49', 68, '2013-02-10 16:41:16'),
(95, '00000006', 2, 158, '2013-02-01', '2013-08-01', 2, 'Inactive', 68, '2013-02-10 15:10:49', 69, '2013-02-23 16:39:08'),
(96, '00000007', 3, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-10 15:10:49'),
(97, '00000008', 3, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-10 15:10:49'),
(98, '00000009', 3, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-10 15:10:49'),
(99, '00000010', 4, 130, '2013-02-01', '2013-03-10', 1, 'Active', 68, '2013-02-10 15:10:49', 69, '2013-02-23 16:35:07'),
(100, '00000011', 4, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-23 11:05:45'),
(101, '00000012', 4, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:49', 68, '2013-02-10 15:10:49'),
(102, '00000013', 1, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:50', 68, '2013-02-10 15:10:50'),
(103, '00000014', 2, NULL, NULL, NULL, NULL, 'Free', 68, '2013-02-10 15:10:50', 68, '2013-02-21 14:45:24');

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

--
-- Table structure for table `badge_assignment_rule`
--

CREATE TABLE IF NOT EXISTS `badge_assignment_rule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_type_id` int(11) NOT NULL,
  `badge_type_id` int(11) NOT NULL,
  `only_manager` tinyint(1) NOT NULL,
  `only_food_manager` tinyint(1) NOT NULL,
  `no_limit_on_badges_number` tinyint(1) NOT NULL,
  `max_badges_allowed` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_badge_type_person_type_person_type1` (`person_type_id`),
  KEY `fk_badge_type_person_type_badge_type1` (`badge_type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

--
-- Dumping data for table `badge_assignment_rule`
--

INSERT INTO `badge_assignment_rule` (`id`, `person_type_id`, `badge_type_id`, `only_manager`, `only_food_manager`, `no_limit_on_badges_number`, `max_badges_allowed`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(12, 2, 3, 0, 0, 1, NULL, NULL, NULL, NULL, NULL),
(13, 1, 4, 0, 0, 1, NULL, NULL, NULL, NULL, NULL),
(14, 2, 1, 0, 0, 1, NULL, NULL, NULL, NULL, NULL),
(15, 3, 4, 0, 0, 0, 1, NULL, NULL, NULL, NULL),
(16, 1, 1, 0, 0, 0, 1, NULL, NULL, NULL, NULL),
(17, 1, 1, 1, 0, 0, 2, NULL, NULL, NULL, NULL),
(18, 1, 1, 0, 1, 0, 2, NULL, NULL, NULL, NULL),
(20, 1, 2, 0, 0, 0, 1, NULL, NULL, NULL, NULL),
(21, 3, 2, 0, 0, 0, 2, NULL, NULL, NULL, NULL),
(22, 1, 2, 1, 0, 0, 3, NULL, NULL, NULL, NULL),
(23, 1, 4, 0, 1, 0, 2, NULL, NULL, NULL, NULL),
(24, 1, 2, 0, 1, 0, 1, NULL, NULL, NULL, NULL),
(25, 2, 3, 0, 0, 1, NULL, NULL, NULL, NULL, NULL),
(26, 1, 1, 1, 1, 0, 5, NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `badge_history`
--

CREATE TABLE IF NOT EXISTS `badge_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `badge_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `activation_start_date` date NOT NULL,
  `activation_end_date` date NOT NULL,
  `max_served_meals` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `badge_history`
--

INSERT INTO `badge_history` (`id`, `badge_id`, `person_id`, `activation_start_date`, `activation_end_date`, `max_served_meals`) VALUES
(1, 91, 134, '2013-02-01', '2014-02-01', 10),
(2, 92, 134, '2013-02-01', '2014-02-01', 10),
(3, 95, 134, '2013-02-01', '2013-08-01', 10),
(4, 103, 134, '2013-02-01', '2013-08-01', 10),
(5, 100, 131, '2013-01-01', '2013-02-01', 1);

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

--
-- Table structure for table `badge_type`
--

CREATE TABLE IF NOT EXISTS `badge_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `activation_period` int(11) NOT NULL,
  `non_manager_meals` int(11) NOT NULL,
  `manager_meals` int(11) NOT NULL,
  `food_manager_meals` int(11) NOT NULL,
  `allow_reassign` tinyint(1) NOT NULL,
  `notify_before_badge_expire` tinyint(1) NOT NULL,
  `start_notify_from_x_days_before_expiry` int(11) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `badge_type`
--

INSERT INTO `badge_type` (`id`, `name`, `activation_period`, `non_manager_meals`, `manager_meals`, `food_manager_meals`, `allow_reassign`, `notify_before_badge_expire`, `start_notify_from_x_days_before_expiry`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 'Employee', 12, 1, 10, 5, 0, 1, 5, 'Active', 0, '2013-01-14 13:13:50', 67, '2013-02-07 08:48:48'),
(2, 'Service Provider', 6, 2, 10, 5, 1, 1, 3, 'Active', 0, '2013-01-17 15:37:15', 67, '2013-02-07 12:47:29'),
(3, 'Employee Temporary Badge', 6, 1, 5, 5, 1, 0, NULL, 'Active', 0, '2013-01-17 15:37:49', 67, '2013-02-03 13:34:50'),
(4, 'Restricted Access', 1, 1, 5, 5, 1, 0, NULL, 'Active', 0, '2013-01-17 15:38:24', 0, '2013-01-17 15:38:24');

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

--
-- Table structure for table `department`
--

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `accounting_unit` varchar(255) DEFAULT NULL,
  `activity_code` varchar(255) DEFAULT NULL,
  `location_id` int(11) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department_name` (`name`),
  KEY `fk_department_location1` (`location_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `department`
--

INSERT INTO `department` (`id`, `name`, `accounting_unit`, `activity_code`, `location_id`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(3, 'Department 3', 'Accounting Unit 3', 'Activity Code 3', 6, 'Active', 0, '2013-01-17 14:56:01', 0, '2013-01-17 14:56:01'),
(4, 'Department 4', 'Accounting Unit 4', 'Activity Code 4', 2, 'Active', 0, '2013-01-17 14:56:23', 0, '2013-01-17 14:56:23'),
(5, 'Department 5', 'Accounting Unit 5', 'Activity Code 5', 2, 'Active', 0, '2013-01-17 14:56:43', 68, '2013-02-12 12:06:37'),
(6, 'Department 6', 'Accounting Unit 6', 'Activity Code 6', 3, 'Active', 0, '2013-01-17 14:57:03', 68, '2013-02-12 12:07:05');

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

--
-- Table structure for table `geomarket`
--

CREATE TABLE IF NOT EXISTS `geomarket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `status` enum('Active','Inactive') CHARACTER SET latin1 NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `geomarket`
--

INSERT INTO `geomarket` (`id`, `name`, `description`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 'Geomarket 1', 'Geomarket 1 Description', 'Active', 0, '2013-01-14 11:47:38', 0, '2013-01-17 14:45:00'),
(2, 'Geomarket 2', 'Geomarket 2 Description', 'Active', 0, '2013-01-14 11:47:55', 0, '2013-01-17 14:45:14'),
(3, 'Geomarket 3', 'Geomarket 3 Description', 'Inactive', 0, '2013-01-17 14:45:28', 0, '2013-01-17 14:49:39'),
(4, 'Geomarket 4', 'Geomarket 4 Description', 'Active', 0, '2013-01-17 14:45:51', 0, '2013-01-17 14:45:51'),
(5, 'Geomarket 5', 'Geomarket 5 Description', 'Inactive', 0, '2013-01-17 14:46:05', 0, '2013-01-17 14:49:46'),
(6, 'Geomarket 6', 'Geomarket 6 Description', 'Active', 0, '2013-01-17 14:46:29', 0, '2013-01-17 15:49:54');

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

--
-- Table structure for table `issuing_reason`
--

CREATE TABLE IF NOT EXISTS `issuing_reason` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reason` varchar(255) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reason_UNIQUE` (`reason`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `issuing_reason`
--

INSERT INTO `issuing_reason` (`id`, `reason`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 'Issuing Reason 1', 'Active', NULL, NULL, NULL, NULL),
(2, 'Issuing Reason 2', 'Inactive', NULL, NULL, NULL, NULL),
(3, 'Issuing Reason 3', 'Active', NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `location`
--

CREATE TABLE IF NOT EXISTS `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `geomarket_id` int(11) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`code`),
  KEY `fk_location_geomarket1` (`geomarket_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `location`
--

INSERT INTO `location` (`id`, `code`, `geomarket_id`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(2, 'Location 2', 1, 'Active', 0, '2013-01-14 11:48:33', 0, '2013-01-17 14:50:14'),
(3, 'Location 3', 1, 'Active', 0, '2013-01-14 11:48:45', 68, '2013-02-12 12:19:10'),
(5, 'Location 4', 2, 'Active', 0, '2013-01-17 14:50:34', 53, '2013-02-13 09:01:39'),
(6, 'Location 5', 6, 'Active', 0, '2013-01-17 14:50:45', 0, '2013-01-17 14:51:01'),
(7, 'Location 6', 1, 'Active', 0, '2013-01-17 14:51:13', 68, '2013-02-12 12:19:22'),
(8, 'Location 7', 2, 'Active', 68, '2013-02-13 17:19:54', 68, '2013-02-13 17:19:54');

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

--
-- Table structure for table `log`
--

CREATE TABLE IF NOT EXISTS `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `date_time` datetime NOT NULL,
  `action` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `person`
--

CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `manager` varchar(45) DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  `is_manager` tinyint(1) NOT NULL,
  `is_food_manager` tinyint(1) NOT NULL,
  `status` enum('Active','On Leave','Terminated','Resigned','Transferred','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_person_number` (`number`),
  KEY `fk_person_department` (`department_id`),
  KEY `fk_person_person_type1` (`type_id`),
  KEY `index_person_alias` (`alias`),
  KEY `index_person_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=160 ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` (`id`, `number`, `name`, `alias`, `type_id`, `mobile`, `email`, `manager`, `department_id`, `is_manager`, `is_food_manager`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(130, '34567765', 'Ayman Alazyzy', 'ayman', 1, '+963-444-222222', 'ayman@testDomain.local', 'Manager1-23456789', 3, 0, 0, 'Active', 65, '2013-02-05 09:05:40', 69, '2013-02-23 16:34:50'),
(133, '12345543', 'Karam Hammoudeh', 'karam', 1, '+963-966-298284', 'karam@testDomain.local', 'Manager1-23456789', 5, 0, 0, 'Active', 65, '2013-02-05 09:05:57', 69, '2013-02-23 16:34:36'),
(154, '98778912', 'Sara Ghniem', 'sara', 2, '+963-44-900990', 'sara@testDomain.local', 'Moaued Bashiti-34554312', 4, 0, 0, 'Active', 68, '2013-02-23 13:50:27', 69, '2013-02-23 16:35:09'),
(155, '34554312', 'Moaued Bashiti', 'moaued', 1, '+963-999-876678', 'moaued@testDomain.local', '', 5, 0, 0, 'Active', 68, '2013-02-23 13:50:27', 69, '2013-02-23 16:35:10'),
(156, '56778912', 'Deyaa Al-salti', 'deyaa', 2, '+963-933-122112', 'deyaa@testDomain.local', 'Moaued Bashiti-34554312', 3, 0, 0, 'Active', 68, '2013-02-23 13:50:28', 69, '2013-02-23 16:35:14'),
(157, 'pn_000001', 'first', '', 4, '98888856567', NULL, 'Deyaa Al-salti-56778912', 6, 0, 0, 'Active', 68, '2013-02-23 13:51:59', 68, '2013-02-23 13:51:59'),
(158, 'pn_000002', 'second', '', 3, '955555555', 'Second111nonLDAP@testDomain.locall', 'Sara Ghniem-98778912', 4, 0, 0, 'Inactive', 68, '2013-02-23 13:51:59', 69, '2013-02-23 16:39:04'),
(159, 'pn_000003', 'third', '', 3, '94353453534', 'third111nonLDAP@testDomain.locall', 'Deyaa Al-salti-56778912', 6, 0, 0, 'Active', 68, '2013-02-23 13:52:00', 68, '2013-02-23 13:52:00');

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

--
-- Table structure for table `person_pos`
--

CREATE TABLE IF NOT EXISTS `person_pos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  `pos_id` int(11) NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_person_pos_person1` (`person_id`),
  KEY `fk_person_pos_pos1` (`pos_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `person_type`
--

CREATE TABLE IF NOT EXISTS `person_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `is_ldap` tinyint(1) NOT NULL,
  `equivalent_ldap_type` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `person_type`
--

INSERT INTO `person_type` (`id`, `name`, `is_ldap`, `equivalent_ldap_type`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 'Employee', 1, 'Employee', 'Active', 0, '2013-01-14 12:31:15', 0, '2013-01-17 15:10:48'),
(2, 'Contractor LDAP ', 1, 'Contractor', 'Active', 0, '2013-01-14 13:05:53', 0, '2013-01-17 15:11:49'),
(3, 'Contractor non-LDAP ', 0, NULL, 'Active', 0, '2013-01-17 15:12:01', 67, '2013-02-03 13:20:14'),
(4, 'Visitor ', 0, NULL, 'Active', 0, '2013-01-17 15:13:12', 0, '2013-01-17 15:13:12');

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

--
-- Table structure for table `pos`
--

CREATE TABLE IF NOT EXISTS `pos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `mac_address` varchar(255) DEFAULT NULL,
  `installation_date` date NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `fk_pos_location1` (`location_id`),
  KEY `fk_pos_user1` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `pos`
--

INSERT INTO `pos` (`id`, `location_id`, `name`, `description`, `mac_address`, `installation_date`, `user_id`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 2, 'POS 1', 'POS 1 Description', '22-23-25-67-89-AB-CD-EE', '2013-01-13', 48, 'Active', NULL, NULL, 53, '2013-02-02 11:30:37'),
(2, 3, 'POS 2', 'POS 2 Description', '01-23-25-67-89-AB-CD-EE', '2013-01-17', 48, 'Active', NULL, NULL, NULL, NULL),
(4, 2, 'POS 3', 'POS 3 Description', '01-11-25-67-79-AB-CD-FE', '2010-01-15', 48, 'Active', NULL, NULL, NULL, NULL),
(5, 2, 'POS 4', 'POS 4 Description', '01-00-25-67-79-AB-CD-FE', '2010-01-19', 48, 'Active', NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `pos_schedule`
--

CREATE TABLE IF NOT EXISTS `pos_schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `schedule_id` int(11) NOT NULL,
  `pos_id` int(11) NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pos_schedule_schedule1` (`schedule_id`),
  KEY `fk_pos_schedule_pos1` (`pos_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;

--
-- Dumping data for table `pos_schedule`
--

INSERT INTO `pos_schedule` (`id`, `schedule_id`, `pos_id`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(36, 1, 5, 53, '2013-01-30 09:26:29', 53, '2013-01-30 09:26:29'),
(45, 2, 1, 68, '2013-02-19 14:38:55', 68, '2013-02-19 14:38:55'),
(46, 1, 1, 68, '2013-02-19 14:38:55', 68, '2013-02-19 14:38:55');

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

--
-- Table structure for table `schedule`
--

CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `sat` tinyint(1) NOT NULL,
  `sun` tinyint(1) NOT NULL,
  `mon` tinyint(1) NOT NULL,
  `tue` tinyint(1) NOT NULL,
  `wed` tinyint(1) NOT NULL,
  `thu` tinyint(1) NOT NULL,
  `fri` tinyint(1) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `schedule`
--

INSERT INTO `schedule` (`id`, `name`, `start_time`, `end_time`, `sat`, `sun`, `mon`, `tue`, `wed`, `thu`, `fri`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(1, 'Breakfast', '08:00:00', '11:00:00', 1, 1, 1, 1, 1, 1, 1, 'Active', 0, '2013-01-14 12:16:26', 0, '2013-01-14 12:18:16'),
(2, 'Lunch', '13:00:00', '16:00:00', 1, 0, 0, 1, 0, 0, 1, 'Active', 0, '2013-01-14 12:16:53', 0, '2013-01-14 12:18:25'),
(4, 'Dinner', '19:00:00', '22:00:00', 1, 0, 1, 0, 1, 1, 0, 'Active', 0, '2013-01-17 14:58:19', 0, '2013-01-17 14:58:30');

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

--
-- Table structure for table `setting`
--

CREATE TABLE IF NOT EXISTS `setting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `setting`
--

INSERT INTO `setting` (`id`, `name`, `value`) VALUES
(1, 'first_ldap_server', 'ldap://testServer.testDomain.local'),
(2, 'second_ldap_server', 'ldap://ldap.slb.com'),
(3, 'ldap_port', '389'),
(4, 'base_dn', 'ou=persons,dc=testDomain, dc=local'),
(5, 'backup_file_path', 'E:\\\\BackupFolder\\\\'),
(6, 'non_ldap_persons_number_prefix', 'pn_'),
(7, 'authentication_method', 'Both'),
(8, 'authentication_ldap_server', 'ldap://testServer.testDomain.local');

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

--
-- Table structure for table `ticket`
--

CREATE TABLE IF NOT EXISTS `ticket` (
  `id` varchar(25) NOT NULL,
  `badge_id` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  `date_time` datetime NOT NULL,
  `schedule_id` int(11) NOT NULL,
  `pos_id` int(11) NOT NULL,
  `quantity` tinyint(4) NOT NULL,
  `user_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `issuing_reason_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ticket_user1` (`user_id`),
  KEY `fk_ticket_person1` (`person_id`),
  KEY `fk_ticket_badge1` (`badge_id`),
  KEY `fk_ticket_schedule1` (`schedule_id`),
  KEY `fk_ticket_pos1` (`pos_id`),
  KEY `fk_ticket_department1` (`department_id`),
  KEY `fk_ticket_issuing_reason1` (`issuing_reason_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ticket`
--

INSERT INTO `ticket` (`id`, `badge_id`, `person_id`, `date_time`, `schedule_id`, `pos_id`, `quantity`, `user_id`, `department_id`, `issuing_reason_id`) VALUES
('100130129173218', 91, 133, '2013-01-29 17:32:18', 2, 1, 1, 68, 4, NULL),
('110130128123106', 91, 133, '2013-01-28 12:32:33', 1, 2, 1, 68, 4, NULL),
('120030129173218', 91, 133, '2013-01-29 17:32:18', 2, 2, 1, 68, 4, NULL),
('120130128123106', 91, 133, '2013-01-28 12:32:33', 1, 1, 1, 68, 3, NULL),
('130130128123106', 92, 133, '2013-01-28 12:32:33', 1, 1, 1, 68, 4, NULL),
('140130129173218', 92, 133, '2013-01-29 17:32:18', 2, 1, 1, 68, 4, NULL),
('150130128123106', 92, 133, '2013-01-28 12:32:33', 1, 2, 1, 68, 4, NULL),
('160130129173218', 92, 133, '2013-01-29 17:32:18', 2, 2, 1, 68, 4, NULL),
('170130131171112', NULL, NULL, '2013-01-31 17:11:12', 1, 1, 2, 68, 4, 1),
('180130131171149', NULL, NULL, '2013-01-31 17:11:49', 1, 1, 2, 68, 3, 1),
('190130131171112', NULL, NULL, '2013-01-31 17:11:12', 1, 1, 2, 48, 4, 1),
('200130131171149', NULL, NULL, '2013-01-31 17:11:49', 1, 1, 2, 48, 3, 1),
('210130131171112', NULL, NULL, '2013-01-31 17:11:12', 1, 2, 2, 68, 4, 1),
('220130129173218', 91, 133, '2013-01-29 17:32:18', 2, 1, 1, 68, 3, NULL),
('220130131171149', NULL, NULL, '2013-01-31 17:11:49', 1, 2, 2, 68, 3, 1),
('230130131171112', NULL, NULL, '2013-01-31 17:11:12', 1, 2, 2, 48, 4, 1),
('240130131171149', NULL, NULL, '2013-01-31 17:11:49', 1, 2, 2, 48, 3, 1),
('250130131171112', NULL, NULL, '2013-01-31 17:11:12', 2, 1, 2, 68, 4, 1),
('260130131171149', NULL, NULL, '2013-01-31 17:11:49', 2, 1, 2, 68, 3, 1),
('270130131171112', NULL, NULL, '2013-01-31 17:11:12', 2, 1, 2, 48, 4, 1),
('280130131171149', NULL, NULL, '2013-01-31 17:11:49', 2, 1, 2, 48, 3, 1),
('290130131171112', NULL, NULL, '2013-01-31 17:11:12', 2, 2, 2, 68, 4, 1),
('300130131171149', NULL, NULL, '2013-01-31 17:11:49', 2, 2, 2, 68, 3, 1),
('310130131171112', NULL, NULL, '2013-01-31 17:11:12', 2, 2, 2, 48, 4, 1),
('320130128123106', 91, 133, '2013-01-28 12:32:33', 1, 2, 1, 68, 3, NULL),
('320130131171149', NULL, NULL, '2013-01-31 17:11:49', 2, 2, 2, 48, 3, 1),
('420130129173218', 91, 133, '2013-01-29 17:32:18', 2, 2, 1, 68, 3, NULL),
('520130128123106', 92, 133, '2013-01-28 12:32:33', 1, 1, 1, 68, 3, NULL),
('620130129173218', 92, 133, '2013-01-29 17:32:18', 2, 1, 1, 68, 3, NULL),
('720130128123106', 92, 133, '2013-01-28 12:32:33', 1, 2, 1, 68, 3, NULL),
('820130129173218', 92, 133, '2013-01-29 17:32:18', 2, 2, 1, 68, 3, NULL),
('920130128123106', 91, 133, '2013-01-28 12:32:33', 1, 1, 1, 68, 4, NULL);

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

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_location1` (`location_id`),
  KEY `index_user_username` (`username`),
  KEY `index_user_email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=70 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `username`, `password`, `email`, `location_id`, `status`, `created_by`, `created_date`, `last_edited_by`, `last_edited_date`) VALUES
(48, 'ayman', 'fca27f8375df7fcf78762983cba92a92', 'ayman@localDomain.local', 2, 'Active', 46, '2013-01-21 12:09:07', 68, '2013-02-18 16:53:59'),
(49, 'karam', '88e2ccdd3cf70e3e317c09e35be4b9d4', 'karam@testDomain.local', 6, 'Active', 46, '2013-01-21 12:14:42', 49, '2013-01-21 16:11:39'),
(53, '', 'b3885b95ebd668d988aff132331f0a31', 'test@flexsolution.com', 6, 'Active', 52, '2013-01-22 12:39:38', 68, '2013-02-13 09:00:23'),
(65, '', '5782cf08d9c066dd0e799dedab7a02bb', 'facilityadmin@flexsolution.com', 2, 'Active', 53, '2013-01-28 16:28:55', 65, '2013-02-05 08:53:35'),
(66, '', '8e1de69385fe1553df2e758136efc10f', 'systemadimn@flexsolution.com', 2, 'Active', 53, '2013-02-02 10:43:54', 53, '2013-02-02 10:43:54'),
(67, '', '8652b91a2a351470e1da770f8001b8e4', 'superadimn@flexsolution.com', 2, 'Active', 66, '2013-02-03 13:11:11', 66, '2013-02-03 13:11:11'),
(68, '', 'efbea551db4cb8bb60af165a76ccf807', 'super@a.com', 2, 'Active', 67, '2013-02-07 15:34:23', 67, '2013-02-07 15:34:23'),
(69, '', '31b2cee8ed1bc3e43a7cd2ab35e2580c', 'facilityadmin@a.com', 2, 'Active', 68, '2013-02-18 15:59:12', 68, '2013-02-21 15:45:26');

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

--
-- Stand-in structure for view `v_report_expired_badges_within_period`
--
CREATE TABLE IF NOT EXISTS `v_report_expired_badges_within_period` (
`badge` varchar(255)
,`person` varchar(255)
,`department` varchar(255)
,`activation_start_date` date
,`activation_end_date` date
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_report_tickets_details_for_badge`
--
CREATE TABLE IF NOT EXISTS `v_report_tickets_details_for_badge` (
`id` varchar(25)
,`department` varchar(255)
,`date_time` datetime
,`accounting_unit` varchar(255)
,`user_name` varchar(255)
,`person` varchar(255)
,`pos` varchar(255)
,`badge` varchar(255)
,`schedule` varchar(255)
,`quantity` tinyint(4)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_report_tickets_details_for_department`
--
CREATE TABLE IF NOT EXISTS `v_report_tickets_details_for_department` (
`user_name` varchar(255)
,`id` varchar(25)
,`department` varchar(255)
,`accounting_unit` varchar(255)
,`pos` varchar(255)
,`schedule` varchar(255)
,`date_time` datetime
,`quantity` tinyint(4)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_report_tickets_details_for_person`
--
CREATE TABLE IF NOT EXISTS `v_report_tickets_details_for_person` (
`id` varchar(25)
,`department` varchar(255)
,`date_time` datetime
,`accounting_unit` varchar(255)
,`user_name` varchar(255)
,`person` varchar(255)
,`pos` varchar(255)
,`badge` varchar(255)
,`schedule` varchar(255)
,`quantity` tinyint(4)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_report_ticket_summary_for_department`
--
CREATE TABLE IF NOT EXISTS `v_report_ticket_summary_for_department` (
`id` varchar(25)
,`department` varchar(255)
,`date_time` datetime
,`accounting_unit` varchar(255)
,`user_name` varchar(255)
,`person` varchar(255)
,`pos` varchar(255)
,`badge` varchar(255)
,`schedule` varchar(255)
,`quantity` tinyint(4)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_report_ticket_summary_pos_schedule`
--
CREATE TABLE IF NOT EXISTS `v_report_ticket_summary_pos_schedule` (
`id` varchar(25)
,`date_time` datetime
,`pos` varchar(255)
,`schedule` varchar(255)
,`quantity` tinyint(4)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_user`
--
CREATE TABLE IF NOT EXISTS `v_user` (
`id` int(11)
,`username` varchar(255)
,`password` varchar(255)
,`email` varchar(255)
,`location` varchar(255)
,`status` enum('Active','Inactive')
,`created_by` int(11)
,`created_date` datetime
,`last_edited_by` int(11)
,`last_edited_date` datetime
,`role` varchar(64)
);
-- --------------------------------------------------------

--
-- Structure for view `v_report_expired_badges_within_period`
--
DROP TABLE IF EXISTS `v_report_expired_badges_within_period`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_expired_badges_within_period` AS select `badge`.`number` AS `badge`,`person`.`name` AS `person`,`department`.`name` AS `department`,`badge`.`activation_start_date` AS `activation_start_date`,`badge`.`activation_end_date` AS `activation_end_date` from ((`badge` join `person` on((`badge`.`person_id` = `person`.`id`))) join `department` on((`person`.`department_id` = `department`.`id`))) where (`badge`.`person_id` is not null);

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

--
-- Structure for view `v_report_tickets_details_for_badge`
--
DROP TABLE IF EXISTS `v_report_tickets_details_for_badge`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_tickets_details_for_badge` AS select `ticket`.`id` AS `id`,`department`.`name` AS `department`,`ticket`.`date_time` AS `date_time`,`department`.`accounting_unit` AS `accounting_unit`,`user`.`email` AS `user_name`,`person`.`name` AS `person`,`pos`.`name` AS `pos`,`badge`.`number` AS `badge`,`schedule`.`name` AS `schedule`,`ticket`.`quantity` AS `quantity` from ((((((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `user` on((`ticket`.`user_id` = `user`.`id`))) join `badge` on((`ticket`.`badge_id` = `badge`.`id`))) join `person` on((`ticket`.`person_id` = `person`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`))) where (`ticket`.`badge_id` is not null);

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

--
-- Structure for view `v_report_tickets_details_for_department`
--
DROP TABLE IF EXISTS `v_report_tickets_details_for_department`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_tickets_details_for_department` AS select `user`.`email` AS `user_name`,`ticket`.`id` AS `id`,`department`.`name` AS `department`,`department`.`accounting_unit` AS `accounting_unit`,`pos`.`name` AS `pos`,`schedule`.`name` AS `schedule`,`ticket`.`date_time` AS `date_time`,`ticket`.`quantity` AS `quantity` from ((((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `user` on((`ticket`.`user_id` = `user`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`))) where isnull(`ticket`.`badge_id`);

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

--
-- Structure for view `v_report_tickets_details_for_person`
--
DROP TABLE IF EXISTS `v_report_tickets_details_for_person`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_tickets_details_for_person` AS select `ticket`.`id` AS `id`,`department`.`name` AS `department`,`ticket`.`date_time` AS `date_time`,`department`.`accounting_unit` AS `accounting_unit`,`user`.`email` AS `user_name`,`person`.`name` AS `person`,`pos`.`name` AS `pos`,`badge`.`number` AS `badge`,`schedule`.`name` AS `schedule`,`ticket`.`quantity` AS `quantity` from ((((((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `user` on((`ticket`.`user_id` = `user`.`id`))) join `badge` on((`ticket`.`badge_id` = `badge`.`id`))) join `person` on((`ticket`.`person_id` = `person`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`))) where (`ticket`.`badge_id` is not null);

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

--
-- Structure for view `v_report_ticket_summary_for_department`
--
DROP TABLE IF EXISTS `v_report_ticket_summary_for_department`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_ticket_summary_for_department` AS select `ticket`.`id` AS `id`,`department`.`name` AS `department`,`ticket`.`date_time` AS `date_time`,`department`.`accounting_unit` AS `accounting_unit`,NULL AS `user_name`,`person`.`name` AS `person`,`pos`.`name` AS `pos`,`badge`.`number` AS `badge`,`schedule`.`name` AS `schedule`,`ticket`.`quantity` AS `quantity` from ((((((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `user` on((`ticket`.`user_id` = `user`.`id`))) join `badge` on((`ticket`.`badge_id` = `badge`.`id`))) join `person` on((`ticket`.`person_id` = `person`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`))) where (`ticket`.`badge_id` is not null) union select `ticket`.`id` AS `id`,`department`.`name` AS `department`,`ticket`.`date_time` AS `date_time`,`department`.`accounting_unit` AS `accounting_unit`,`user`.`email` AS `user_name`,NULL AS `person`,`pos`.`name` AS `pos`,`ticket`.`badge_id` AS `badge`,`schedule`.`name` AS `schedule`,`ticket`.`quantity` AS `quantity` from ((((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `user` on((`ticket`.`user_id` = `user`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`))) where isnull(`ticket`.`badge_id`);

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

--
-- Structure for view `v_report_ticket_summary_pos_schedule`
--
DROP TABLE IF EXISTS `v_report_ticket_summary_pos_schedule`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_report_ticket_summary_pos_schedule` AS select `ticket`.`id` AS `id`,`ticket`.`date_time` AS `date_time`,`pos`.`name` AS `pos`,`schedule`.`name` AS `schedule`,`ticket`.`quantity` AS `quantity` from (((`ticket` join `department` on((`ticket`.`department_id` = `department`.`id`))) join `schedule` on((`ticket`.`schedule_id` = `schedule`.`id`))) join `pos` on((`ticket`.`pos_id` = `pos`.`id`)));

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

--
-- Structure for view `v_user`
--
DROP TABLE IF EXISTS `v_user`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `u`.`id` AS `id`,`u`.`username` AS `username`,`u`.`password` AS `password`,`u`.`email` AS `email`,`l`.`code` AS `location`,`u`.`status` AS `status`,`u`.`created_by` AS `created_by`,`u`.`created_date` AS `created_date`,`u`.`last_edited_by` AS `last_edited_by`,`u`.`last_edited_date` AS `last_edited_date`,`aa`.`itemname` AS `role` from ((`user` `u` join `authassignment` `aa` on((`u`.`id` = `aa`.`userid`))) left join `location` `l` on((`l`.`id` = `u`.`location_id`)));

--
-- Constraints for dumped tables
--

--
-- Constraints for table `badge`
--
ALTER TABLE `badge`
  ADD CONSTRAINT `fk_badge_badge_type1` FOREIGN KEY (`type_id`) REFERENCES `badge_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_badge_person1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `badge_assignment_rule`
--
ALTER TABLE `badge_assignment_rule`
  ADD CONSTRAINT `fk_badge_type_person_type_badge_type1` FOREIGN KEY (`badge_type_id`) REFERENCES `badge_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_badge_type_person_type_person_type1` FOREIGN KEY (`person_type_id`) REFERENCES `person_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `department`
--
ALTER TABLE `department`
  ADD CONSTRAINT `fk_department_location1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `location`
--
ALTER TABLE `location`
  ADD CONSTRAINT `fk_location_geomarket1` FOREIGN KEY (`geomarket_id`) REFERENCES `geomarket` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `fk_person_department` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_person_person_type1` FOREIGN KEY (`type_id`) REFERENCES `person_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `person_pos`
--
ALTER TABLE `person_pos`
  ADD CONSTRAINT `fk_person_pos_person1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_person_pos_pos1` FOREIGN KEY (`pos_id`) REFERENCES `pos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `pos`
--
ALTER TABLE `pos`
  ADD CONSTRAINT `fk_pos_location1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `pos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `pos_schedule`
--
ALTER TABLE `pos_schedule`
  ADD CONSTRAINT `fk_pos_schedule_pos1` FOREIGN KEY (`pos_id`) REFERENCES `pos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_pos_schedule_schedule1` FOREIGN KEY (`schedule_id`) REFERENCES `schedule` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `ticket`
--
ALTER TABLE `ticket`
  ADD CONSTRAINT `fk_ticket_badge1` FOREIGN KEY (`badge_id`) REFERENCES `badge` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_department1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_issuing_reason1` FOREIGN KEY (`issuing_reason_id`) REFERENCES `issuing_reason` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_person1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_pos1` FOREIGN KEY (`pos_id`) REFERENCES `pos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_schedule1` FOREIGN KEY (`schedule_id`) REFERENCES `schedule` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ticket_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `user`
--
ALTER TABLE `user`
  ADD CONSTRAINT `fk_user_location1` FOREIGN KEY (`location_id`) REFERENCES `location` (`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 */;
