<?php
/*
Foreign keys created by neils for practice details list
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblPractice` DROP FOREIGN KEY `tblPractice.strCountry`;
ALTER TABLE `tblPractice` ADD CONSTRAINT `tblPractice.strCountry` FOREIGN KEY (`refCountryID`) REFERENCES `tblCountry` (`CountryID`) ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblPractice` DROP FOREIGN KEY `tblPractice.strProvince`;
ALTER TABLE `tblPractice` ADD CONSTRAINT `tblPractice.strProvince` FOREIGN KEY (`refProvinceID`) REFERENCES `tblCountryProvince` (`ProvinceID`) ON UPDATE CASCADE;


SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblPractice` DROP FOREIGN KEY `tblPractice.strUser`;
ALTER TABLE `tblPractice` ADD CONSTRAINT `tblPractice.strUser` FOREIGN KEY (`refPracticeConsultantID`) REFERENCES `sysUser` (`UserID`) ON UPDATE CASCADE;


SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblPractice` DROP FOREIGN KEY `tblLearner.strSubCategory`;
ALTER TABLE `tblPractice` ADD CONSTRAINT `tblLearner.strSubCategory` FOREIGN KEY (`refSubCategoryID`) REFERENCES `tblLearner` (`refUserTypeID`) ON UPDATE CASCADE;

ALTER TABLE `tblPractice` DROP FOREIGN KEY `tblPractice.strWPS`;
ALTER TABLE `tblPractice` ADD CONSTRAINT `tblPractice.strWPS` FOREIGN KEY (`refWPSID`) REFERENCES `vieWPS` (`UserID`) ON UPDATE CASCADE;

//20170331 - Update related tblLearner record to be null if Practice is deleted - CL
*/

include_once("_nemo.list.cls.php");

class Practices extends NemoList
{
	private $ID = 0;

	public function __construct($DataKey)
	{
		$this->Filters[frSearch]->tag = "input";
	   	$this->Filters[frSearch]->html->value = "";
	   	$this->Filters[frSearch]->html->type = "text";
	   	$this->Filters[frSearch]->html->class = "controlText";

		$this->Filters[frStatus]->tag = "select";
		$this->Filters[frStatus]->html->value = "-1";
		$this->Filters[frStatus]->html->class = "controlText";
		$this->Filters[frStatus]->sql = "SELECT -1 AS ControlValue, '- All -' AS ControlText
			UNION ALL
			SELECT 1 AS ControlValue, 'Active' AS ControlText
			UNION ALL
			SELECT 0 AS ControlValue, 'Inactive' AS ControlText

			ORDER BY ControlText ASC";

		$this->Filters[frProvince]->tag = "select";
		$this->Filters[frProvince]->html->value = "-1";
		$this->Filters[frProvince]->html->class = "controlText";
		$this->Filters[frProvince]->sql = "SELECT -1 AS ControlValue, '- All -' AS ControlText
         UNION ALL
         SELECT tblCountryProvince.strProvince AS ControlValue, tblCountryProvince.strProvince AS ControlText FROM tblCountryProvince
         ORDER BY ControlText ASC";

      parent::__construct($DataKey);
	}

	public function getList()
	{
		global $SystemSettings;

		$Where = "";
		if ($this->Filters[frSearch]->html->value != ""){
			$like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
			$Where .= "AND (tblPractice.strPractice $like
				OR tblPractice.strContact $like
				OR tblPractice.strContactEmail $like)";
		}

		if ($this->Filters[frStatus]->html->value != -1){
			$Where .= " AND tblPractice.blnActive = ". $this->Filters[frStatus]->html->value;
		}

		if ($this->Filters[frProvince]->html->value != -1){
			$Where .= " AND tblCountryProvince.strProvince = '". $this->Filters[frProvince]->html->value. "'";
		}

		$this->ListSQL("
         SELECT tblPractice.PracticeID, tblPractice.strPractice AS Practice, tblCountryProvince.strProvince AS Province, tblPractice.strContact AS Contact, tblPractice.strContactEmail AS Email, tblPractice.blnActive AS Active, tblPractice.strLastUser AS 'Last User', tblPractice.dtLastEdit AS 'Last Edit'
         FROM (tblPractice INNER JOIN tblCountryProvince ON tblPractice.refProvinceID = tblCountryProvince.ProvinceID)
         WHERE 1=1 $Where
         ORDER BY tblPractice.strPractice ASC", 0,"practice.php");

		return $this->renderTable("Practices List");
	}

	public static function Save(&$PracticeID)
	{
		global $SystemSettings;

		//print_rr($_POST);

		$cdb = new NemoDatabase("tblPractice", $PracticeID, null, 0);
	   	$cdb->SetValues($_POST);
	   	if($cdb->Fields[refPracticeConsultantID] == 0) $cdb->Fields[refPracticeConsultantID] = "NULL";
	   	$cdb->Fields[strLastUser] = $_SESSION["USER"]->USERNAME;

		   if($cdb->Fields->refWPSID == 0)
	   	{
		   	$cdb->Fields->refWPSID == "NULL";
		   }

	   	$cdb->doQuery("SET FOREIGN_KEY_CHECKS=0");	//this is for the foreign keys - neils
	   	$rst = $cdb->Save(0,0);
	   	$cdb->doQuery("SET FOREIGN_KEY_CHECKS=1");

		if($PracticeID == 0) $PracticeID = $cdb->ID[PracticeID];

		if($rst->Error == 1)
		   return $rst->Message;
		else
		   return "Details Saved. ";
	}

	public static function Delete($chkSelect)
	{
		global $SystemSettings, $xdb;

		if(count($chkSelect) > 0){
			foreach($chkSelect as $key => $value){
				//20170331 - Update related tblLearner record to be null if Practice is deleted - CL
				$xdb->doQuery("UPDATE tblLearner SET refPracticeID=NULL WHERE refPracticeID = ". $xdb->qs($key));
				$xdb->doQuery("DELETE FROM tblPractice WHERE PracticeID = ". $xdb->qs($key));
			}
			return "Records Deleted. ";
		}

	}

}

?>