DROP VIEW IF EXISTS `dimas_e`.`account`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`account` AS select `people`.`GivenName` AS `GivenName`,`people`.`Surname` AS `Surname`,`people_status`.`IsAccount` AS `IsAccount`,`people_status`.`Nickname` AS `Nickname`,`people`.`ID` AS `ID`,`people_status`.`IsCurrent` AS `IsCurrent` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`))) where (`people_status`.`IsAccount` like _latin1'Yes');

DROP VIEW IF EXISTS `dimas_e`.`olex_submission`;
CREATE ALGORITHM=UNDEFINED DEFINER=`DIMAS`@`%` SQL SECURITY DEFINER VIEW  `dimas_e`.`olex_submission` AS select `dimas_e`.`submission`.`ID` AS `ID`,`dimas_e`.`people_fullnames`.`display` AS `submitter`,`submittercontact`.`Phone` AS `Phone`,`submittercontact`.`Lab` AS `Lab`,`accountstatus`.`Nickname` AS `account`,`operatorstatus`.`Nickname` AS `operator`,`dimas_e`.`submission`.`DateSubmission` AS `DateSubmission`,`dimas_e`.`progress`.`DateCollected` AS `DateCollected`,`dimas_e`.`progress`.`DateCompleted` AS `DateCompleted`,`dimas_e`.`progress_status`.`display` AS `display`,`dimas_e`.`crystal`.`CommentCrystallisation` AS `crystallisation`,`dimas_e`.`crystal`.`CommentPreparation` AS `preparation` from ((`progress_status` join ((((((((`submission` join `people` `Account` on((`dimas_e`.`submission`.`AccountID` = `account`.`ID`))) join `people` `Operator` on((`dimas_e`.`submission`.`OperatorID` = `operator`.`ID`))) join `people` `Submitter` on((`dimas_e`.`submission`.`SubmitterID` = `submitter`.`ID`))) join `people_contact` `SubmitterContact` on((`submitter`.`ID` = `submittercontact`.`ID`))) join `people_status` `AccountStatus` on((`account`.`ID` = `accountstatus`.`ID`))) join `people_status` `OperatorStatus` on((`operator`.`ID` = `operatorstatus`.`ID`))) join `progress` on((`dimas_e`.`submission`.`ID` = convert(`dimas_e`.`progress`.`ID` using utf8)))) join `crystal` on((`dimas_e`.`submission`.`ID` = convert(`dimas_e`.`crystal`.`ID` using utf8)))) on((`dimas_e`.`progress_status`.`ID` = `dimas_e`.`progress`.`StatusID`))) join `people_fullnames` on((`submittercontact`.`ID` = `dimas_e`.`people_fullnames`.`ID`)));

DROP VIEW IF EXISTS `dimas_e`.`olex_submission_test`;
CREATE ALGORITHM=UNDEFINED DEFINER=`DIMAS`@`%` SQL SECURITY DEFINER VIEW  `dimas_e`.`olex_submission_test` AS select `dimas_e`.`submission`.`ID` AS `ID`,`dimas_e`.`people_fullnames`.`display` AS `submitter`,`submittercontact`.`Phone` AS `Phone`,`submittercontact`.`Lab` AS `Lab`,`accountstatus`.`Nickname` AS `account`,`operatorstatus`.`Nickname` AS `operator`,`dimas_e`.`submission`.`DateSubmission` AS `DateSubmission`,`dimas_e`.`progress`.`DateCollected` AS `DateCollected`,`dimas_e`.`progress`.`DateCompleted` AS `DateCompleted`,`dimas_e`.`progress_status`.`display` AS `display`,`dimas_e`.`crystal`.`CommentCrystallisation` AS `crystallisation`,`dimas_e`.`crystal`.`CommentPreparation` AS `preparation` from ((`progress_status` join ((((((((`submission` join `people` `Account` on((`dimas_e`.`submission`.`AccountID` = `account`.`ID`))) join `people` `Operator` on((`dimas_e`.`submission`.`OperatorID` = `operator`.`ID`))) join `people` `Submitter` on((`dimas_e`.`submission`.`SubmitterID` = `submitter`.`ID`))) join `people_contact` `SubmitterContact` on((`submitter`.`ID` = `submittercontact`.`ID`))) join `people_status` `AccountStatus` on((`account`.`ID` = `accountstatus`.`ID`))) join `people_status` `OperatorStatus` on((`operator`.`ID` = `operatorstatus`.`ID`))) join `progress` on((`dimas_e`.`submission`.`ID` = convert(`dimas_e`.`progress`.`ID` using utf8)))) join `crystal` on((`dimas_e`.`submission`.`ID` = convert(`dimas_e`.`crystal`.`ID` using utf8)))) on((`dimas_e`.`progress_status`.`ID` = `dimas_e`.`progress`.`StatusID`))) join `people_fullnames` on((`submittercontact`.`ID` = `dimas_e`.`people_fullnames`.`ID`)));

DROP VIEW IF EXISTS `dimas_e`.`operator`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`operator` AS select `people`.`GivenName` AS `GivenName`,`people`.`Surname` AS `Surname`,`people_status`.`IsAccount` AS `IsAccount`,`people_status`.`Nickname` AS `Nickname`,`people`.`ID` AS `ID`,`people_status`.`IsCurrent` AS `IsCurrent` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`))) where (`people_status`.`IsDiffraction` like _latin1'Yes');

DROP VIEW IF EXISTS `dimas_e`.`people_contact_info`;
CREATE ALGORITHM=UNDEFINED DEFINER=`DIMAS`@`%` SQL SECURITY DEFINER VIEW  `dimas_e`.`people_contact_info` AS select `people`.`ID` AS `ID`,`people`.`GivenName` AS `GivenName`,`people`.`Surname` AS `Surname`,`people_contact`.`Phone` AS `Phone`,`people_contact`.`Office` AS `Office`,`people_contact`.`Lab` AS `Lab`,`people_contact`.`eMail` AS `eMail` from (`people` join `people_contact` on((`people`.`ID` = `people_contact`.`ID`)));

DROP VIEW IF EXISTS `dimas_e`.`people_fullnames`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`people_fullnames` AS select concat(`people`.`GivenName`,_latin1' ',`people`.`Surname`) AS `display`,`people`.`ID` AS `ID` from `people`;

DROP VIEW IF EXISTS `dimas_e`.`submision_operator`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submision_operator` AS select concat(`people`.`GivenName`,_latin1' ',`people`.`Surname`) AS `display`,`people_status`.`IsAccount` AS `IsAccount`,`people`.`ID` AS `ID` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`))) where (`people_status`.`IsDiffraction` like _latin1'Yes');

DROP VIEW IF EXISTS `dimas_e`.`submission_account`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submission_account` AS select concat(`people`.`GivenName`,_latin1' ',`people`.`Surname`) AS `display`,`people_status`.`IsAccount` AS `IsAccount`,`people`.`ID` AS `ID` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`))) where (`people_status`.`IsAccount` like _latin1'Yes');

DROP VIEW IF EXISTS `dimas_e`.`submission_nmr_operator`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submission_nmr_operator` AS select concat(`people`.`GivenName`,_latin1' ',`people`.`Surname`) AS `display`,`people_status`.`IsAccount` AS `IsAccount`,`people`.`ID` AS `ID` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`))) where (`people_status`.`IsNMR` like _latin1'Yes');

DROP VIEW IF EXISTS `dimas_e`.`submission_report`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submission_report` AS select `submission`.`ID` AS `analysisID`,`submitter`.`display` AS `submittername`,`operator`.`display` AS `operatorwholename`,`submittercontact`.`Office` AS `room`,`submittercontact`.`Phone` AS `phone`,`submittercontact`.`eMail` AS `email`,`submission`.`DateSubmission` AS `submission`,`submission`.`Code` AS `code`,`submission`.`Marvin` AS `Marvin`,`submission`.`ChemicalName` AS `chemicalname`,`submission`.`FormulaSubmitted` AS `chemicalformula`,`submission`.`ListPreviousAnalyses` AS `analyses`,concat(_utf8'Toxicities: ',`submission`.`ListToxicities`,_utf8' Sensitivities: ',`submission`.`ListSensitivities`) AS `sensandtox`,`dimas_e`.`submission`.`Solvents` AS `solvents`,`dimas_e`.`submission`.`ListReasons` AS `reasonsforanalysis`,`account`.`Nickname` AS `supervisor`,`dimas_e`.`submission`.`MeasurementTemperature` AS `temperature`,`dimas_e`.`submission`.`Comment` AS `specialinstructions` from (`people_contact` `SubmitterContact` join (`people_status` `Account` join ((`people_fullnames` `Submitter` join `submission` on((`submitter`.`ID` = `dimas_e`.`submission`.`SubmitterID`))) join `people_fullnames` `Operator` on((`dimas_e`.`submission`.`OperatorID` = `operator`.`ID`))) on((`account`.`ID` = `dimas_e`.`submission`.`AccountID`))) on((`submittercontact`.`ID` = `dimas_e`.`submission`.`SubmitterID`)));

DROP VIEW IF EXISTS `dimas_e`.`submission_submitter`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submission_submitter` AS select concat(`people`.`GivenName`,_latin1' ',`people`.`Surname`) AS `display`,`people_status`.`IsAccount` AS `IsAccount`,`people`.`ID` AS `ID` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`)));

DROP VIEW IF EXISTS `dimas_e`.`submitter`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `dimas_e`.`submitter` AS select `people`.`GivenName` AS `GivenName`,`people`.`Surname` AS `Surname`,`people_status`.`IsAccount` AS `IsAccount`,`people`.`ID` AS `ID`,`people_status`.`IsCurrent` AS `IsCurrent` from (`people` join `people_status` on((`people`.`ID` = `people_status`.`ID`)));