<?php
/*

ALTER TABLE `slaQuote` drop FOREIGN KEY `slaQuote.strUser`;
ALTER TABLE `slaQuote` ADD CONSTRAINT `slaQuote.strWPS` FOREIGN KEY (`refWPSID`) REFERENCES `sysUser` (`UserID`) ON UPDATE CASCADE ON DELETE NO ACTION;

 */

include_once("_nemo.list.cls.php");
include_once("_nemo.email.cls.php");

class MyClients 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[frRegion]->tag = "select";
      $this->Filters[frRegion]->html->value = "0"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frRegion]->html->class = "controlText";
      $this->Filters[frRegion]->sql = "SELECT 0 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `RegionID` AS ControlValue, concat(strRegion,' (',count(refRegionID),')') AS ControlText
                        FROM tblRegion INNER JOIN tblLearner ON tblRegion.RegionID = tblLearner.refRegionID
                        WHERE tblRegion.blnActive = 1
                        GROUP BY RegionID, strRegion
                        ORDER BY ControlText ASC";

      $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";

      parent::__construct($DataKey);
   }

   public function getList()
   {
      global $SystemSettings;
      //print_rr($_POST);
      //ini
      $Where = "";
      //sql where

      if($this->Filters[frSearch]->html->value != "")
      {
         $like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
         $Where .= " AND (tblLearner.LearnerID $like OR tblLearner.strLearner $like OR tblLearner.strEmail $like OR tblLearner.strPhone $like)";
      }
      if($this->Filters[frRegion]->html->value != 0)
      {
         $Where .= " AND tblRegion.RegionID = ". $this->Filters[frRegion]->html->value;
      }
      if($this->Filters[frStatus]->html->value != -1)
      {
         $Where .= " AND tblLearner.blnActive = ". $this->Filters[frStatus]->html->value;
      }

      $this->ListSQL("
         SELECT tblLearner.LearnerID, tblLearner.strLearner AS Client,tblLearner.strCompany AS Company, CASE WHEN refRegionID < 0 THEN tblCountry.strCountry ELSE tblRegion.strRegion END AS 'Region/Country'
            , IFNULL( concat(tblLearner.strUserType,' ',tblLearner.strUserTypeOther) ,'-') AS 'User Type', tblLearner.strPhone AS Phone, tblLearner.strEmail AS Email, tblLearner.dtRegistered as 'Date Registered', tblLearner.blnActive AS Active, tblLearner.strLastUser AS 'Last User', tblLearner.dtLastEdit AS 'Last Edit'
         FROM (tblLearner INNER JOIN tblRegion ON tblLearner.refRegionID = tblRegion.RegionID) INNER JOIN tblCountry on refCountryID = CountryID
         WHERE refWPSID=".$_SESSION['USER']->ID." $Where
         ORDER BY tblLearner.strLearner",0,"my.clients.php");

      return $this->renderTable("My Client List");
   }

   public static function renderTeamSetup($LearnerID)
   {
      global $xdb,$SystemSettings;

      //pre
      if($LearnerID == 0)
         return "";

      $thHeadings = "<tr>
                        <th align='center'style='width: 30px' nowrap>Select</th>
                        <th style=''>Team Member</th>
                        <th style='' nowrap >Email</th>
                     </tr>";

      //go
      //get list of selected teams for this current learner and save it into an array
      $rst = $xdb->doQuery("
                           SELECT vieAVPTeam.UserID,vieAVPTeam.strUser, vieAVPTeam.strEmail
                           FROM vieAVPTeam
                           WHERE vieAVPTeam.refLearnerID=$LearnerID
                           ORDER BY vieAVPTeam.strUser
                            ",0);
      $arrSelectedTeam = array();
      while($row = $xdb->fetch_object($rst))
      {
         $arrSelectedTeam[$row->UserID] = 1;
      }

     //go
      $rst = $xdb->doQuery("
                           SELECT sysUser.UserID, sysUser.strUser, sysUser.strEmail
                           FROM sysUser
                           WHERE sysUser.blnAVPTeam=1 AND sysUser.blnActive=1
                           ORDER BY sysUser.strUser
                            ",0);

      while($row = $xdb->fetch_object($rst))
      {
         $txtTeamSelected = "";
         if($arrSelectedTeam[$row->UserID] == 1)
         {
            $txtTeamSelected = "checked";
         }
         $tr .= "<tr>
                     <td align='center' class='tdCount textColour'>
                        <input type='hidden' id='UserID_$row->UserID' value='$row->UserID' name='UserID[$row->UserID]'/>
                        <input type='checkbox' id='chkSelect_$row->UserID' value='1' name='chkSelect[$row->UserID]' $txtTeamSelected/>
                     </td>
                     <td align='left'><label id='strUser_$row->UserID' name='strUser[$row->UserID]'>$row->strUser</label></td>
                     <td align='left'><label id='strEmail_$row->UserID' name='strEmail[$row->UserID]'>$row->strEmail</label></td>
                  </tr>";
      }

      return " <div >
                  <table cellpadding=2 cellspacing=1 border=0 class='tblNemoDetails' style='width: 100%; margin: 0px;'>
                     <caption>Team Setup</caption>
                     $thHeadings
                     $tr
                  </table>
               </div>";
   }

   public static function EmailPreRegistration($LearnerID)
   {
      global $SystemSettings, $xdb;

      if($LearnerID == 0)
      {
         return "Sending failed, no learner details found.";
      }

      //Send the mail
      $rowLearner = $xdb->getRowSQL("SELECT * FROM tblLearner WHERE LearnerID = '$LearnerID'",0);

      $nemoEmail = new NemoEmail($rowLearner->strEmail , "" , 0);
      $nemoEmail->LoadEmailTemplate("Register WPS Team");

      $arrValues[DisplayName] = $rowLearner->strDisplayName;
      $arrValues[Email] = $rowLearner->strEmail;
      $arrValues[Password] = $rowLearner->strPasswordMD5;

      //$arrValues[LogoPFI] = "<a href='".$strBaseURL."index.php"."'><img src='".$strBaseURL.$SystemSettings[EmailLogoPFI]."' ></a>";
      //$arrValues[InviteHeader] = "<img src='".$strBaseURL.$SystemSettings[EmailLogoInvite]."' >";

      $nemoEmail->Substitute($arrValues);

      $nemoEmail->addHeader("FROM", $SystemSettings["SMTP Send As"]);
      //$nemoEmail->addHeader("BCC", $page->SystemSettings["SMTP BCC"]);
      //$nemoEmail->Bcc = $page->SystemSettings["SMTP BCC"];
      $nemoEmail->From = $SystemSettings["SMTP Send As"];
//print_rr($nemoEmail); die;

      if($nemoEmail->Send())
      {
         return "Email Sent";

      } else {

         return "Sending failed.";
      }
   }

   public static function Save(&$LearnerID)
   {
      global $xdb,$SystemSettings;

      $ldb = new NemoDatabase("tblLearner", $LearnerID, null, 0);
//print_rr($_FILES);
//print_rr($_POST);

      $ldb->SetValues($_POST);

      if($LearnerID == 0)
      {
         $strPassword = Obfuscate();
         $strPasswordMD5 = md5($strPassword);

         $ldb->Fields[strPassword] = $strPassword;
         $ldb->Fields[strPasswordMD5] = $strPasswordMD5;
         $ldb->Fields[blnForcePasswordChange] = 1;
         $ldb->Fields[blnRiskReferral] = 0;
         $ldb->Fields[dtRegistered] = date("Y-m-d");

         $ldb->Fields["Profile:PicturePath"] = "blank.jpg";
         $ldb->Fields["Profile:DisplayInfoSettings"] = "No One";
      }
      else
      {
         $ldb->Fields[strPassword] = qs($_POST[strPassword]);
         $ldb->Fields[strPasswordMD5] = md5($ldb->Fields[strPassword]);

         //save team
         //1.remove previous team records for LearnerID
         $xdb->doQuery("DELETE FROM tblAVPTeam WHERE refLearnerID=$LearnerID");
         //2.Insert new team records base of of selected UserID's
         foreach($_POST[chkSelect] as $UserID=>$Value)
         {
             $xdb->doQuery("INSERT INTO tblAVPTeam(refLearnerID,refUserID) VALUES ('$LearnerID','$UserID')");
         }
      }

      $ldb->Fields[strLearner] = $ldb->Fields[strSurname] .", ". $ldb->Fields[strName] ." (". $ldb->Fields[strDisplayName] .")";
      $ldb->Fields[txtAddress] = str_replace(chr(13).chr(13), chr(13), $ldb->Fields[strAddress1] . chr(13) . $ldb->Fields[strAddress2] . chr(13) . $ldb->Fields[strAddress3] . chr(13) . $ldb->Fields[strCode]);
      if($ldb->Fields[refCountryID] != 1)
         $ldb->Fields[refRegionID] = -2;

      list($day,$month) = explode("/", $_POST[dtDOB]);
      //echo date("Y")."/$month/$day";
      $ldb->Fields[dtDOB] = date("Y-m-d", strtotime(date("Y")."/$month/$day"));
      $ldb->Fields[strPasswordMD5] = md5($ldb->Fields[strPassword]);

      $ldb->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

      $result1 = $ldb->Save();

      //print_rr($ldb->Fields);
      if($LearnerID == 0) $LearnerID = $ldb->ID[LearnerID];

      if($result1->Error == 1 || $result2->Error == 1)
         return $result1->Message.$result2->Message;
      else
         return "Details Saved. ";
   }

   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         $xdb->doQuery("DELETE FROM tblLearner WHERE LearnerID = ". $xdb->qs($key));
      }
         return "Records Deleted. ";
      }
   }

}
?>
