-- Users View
CREATE VIEW v_user AS
	SELECT u.id, username, password, email, l.code AS 'location', u.status, u.created_by, u.created_date, u.last_edited_by, u.last_edited_date, aa.itemname AS role
		FROM 
			user u INNER JOIN authassignment aa ON u.id = aa.userid
			LEFT OUTER JOIN location l ON l.id = u.location_id;
			
			
-- view report ticket Summary for Department
CREATE VIEW v_report_ticket_summary_for_department AS
SELECT 
	IF ( isnull(`badge_id`),'Department','Person' ) 'ticket_type' ,ticket.id AS id, department.name  AS 'department',ticket.date_time AS 'date_time',department.accounting_unit AS 'accounting_unit',department.activity_code AS 'activity_code'  
	, 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` 
			LEFT JOIN  department on ticket.department_id = department.id
			LEFT JOIN user on ticket.user_id = user.id
			LEFT JOIN badge on ticket.badge_id = badge.id
			LEFT JOIN person on ticket.person_id = person.id
			LEFT JOIN schedule on ticket.schedule_id = schedule.id
			LEFT JOIN pos on ticket.pos_id = pos.id
			Where ticket.cancelled = 0 ;
			
			
			
			
			


--  report  Tickets Summary For POS per Schedule
CREATE 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`
			inner join department on ticket.department_id = department.id
			inner join schedule on ticket.schedule_id = schedule.id
			inner join pos on ticket.pos_id = pos.id 
			Where ticket.cancelled = 0 ;
	
-- view report ticket Details for Person
CREATE 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'  ,department.activity_code AS 'activity_code', user.email AS 'user_name', person.name AS 'person', pos.name AS 'pos', badge.number  AS 'badge', schedule.name AS 'schedule',IF ( ticket.cancelled=1,'Yes','No' ) AS 'cancelled', ticket.quantity as 'quantity'
		FROM `ticket` 
			inner join department on ticket.department_id = department.id
			inner join user on ticket.user_id = user.id
			inner join badge on ticket.badge_id = badge.id
			inner join person on ticket.person_id = person.id
			inner join schedule on ticket.schedule_id = schedule.id
			inner join pos on ticket.pos_id = pos.id
		WHERE (ticket.badge_id IS NOT NULL);



-- view report ticket Details for Department
CREATE 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'  , department.activity_code AS 'activity_code',  pos.name AS 'pos', schedule.name AS 'schedule',ticket.date_time AS 'date_time',issuing_reason.reason AS 'reason',IF ( ticket.cancelled=1,'Yes','No' ) AS 'cancelled' , ticket.quantity as 'quantity'
		FROM `ticket` 
			inner join department on ticket.department_id = department.id
			inner join user on ticket.user_id = user.id
			inner join schedule on ticket.schedule_id = schedule.id
			inner join pos on ticket.pos_id = pos.id
			inner join issuing_reason on ticket.issuing_reason_id = issuing_reason.id
		WHERE (ticket.badge_id Is NULL) ;


-- view report ticket Details for Badge
CREATE 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'  ,department.activity_code AS 'activity_code', 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',IF ( ticket.cancelled=1,'Yes','No' ) AS 'cancelled' 
		FROM `ticket` 
			inner join department on ticket.department_id = department.id
			inner join user on ticket.user_id = user.id
			inner join badge on ticket.badge_id = badge.id
			inner join person on ticket.person_id = person.id
			inner join schedule on ticket.schedule_id = schedule.id
			inner join pos on ticket.pos_id = pos.id
		WHERE (ticket.badge_id IS NOT NULL);



-- view report Expired Badges Within Period
CREATE 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` 
			inner join person on badge.person_id = person.id
			inner join department on person.department_id = department.id
		WHERE badge.person_id IS NOT NULL ;
		
		
		
		
-- view report Person_POS 
CREATE VIEW v_report_person_pos AS
	SELECT person.`name` AS 'person_name',person.`number` AS 'person_number' ,person.`alias` AS 'person_alias' ,person_type.`name` AS 'person_type' ,
	person.`mobile` AS 'person_mobile' ,person.`email` AS 'person_email' ,person.`manager` AS 'person_manager' ,if(person.`alias` !='','LDAP','Not LDAP') AS 'person_is_ldap' ,
	if(person.`is_manager` > 0,'Yes','No') AS 'person_is_manager' ,if(person.`is_food_manager` > 0 ,'Yes','No') AS 'person_is_food_manager' ,
	person.`status` AS 'person_status',department.`name` AS 'person_department_name',location.`code` AS 'person_location_code',geomarket.`name` AS 'person_geomarket_name', 
	IFNULL(pos.`name`,'N/A') AS 'pos_name',IFNULL(pos_location.`code`,'N/A') AS 'pos_location_code',IFNULL(pos_geomraket.`name`,'N/A') AS 'pos_geomraket_name'  
		FROM `person` 
			inner join person_type on person.type_id = person_type.id
			inner join department on person.department_id = department.id
			inner join location on department.location_id = location.id
			inner join geomarket on location.geomarket_id = geomarket.id
			LEFT join person_pos on person.id = person_pos.person_id
			LEFT join pos on pos.id = person_pos.pos_id
			LEFT join location pos_location on pos.location_id = pos_location.id
			LEFT join geomarket pos_geomraket on pos_location.geomarket_id = POS_Geomraket.id;
			