File: /home/imensosw/www/imenso.co/demo/individuals/app/Lessonschedule.php
<?php
namespace App;
use App\Confirmedusers;
use App\Lessonschedule;
use Google_Client;
use Google_Service_Drive;
use Google_Service_Calendar;
use Google_Service_Calendar_Event;
use Illuminate\Support\Facades\DB;
use App\Services\GoogleCalendar;
use Illuminate\Database\Eloquent\Model;
class Lessonschedule extends Model
{
public $table = "lessonschedule";
//protected $fillable = ['reg_name','budget_ngo'];
public static function addtoGCalendar($lessonid,$callid) {
$data = DB::select("SELECT * FROM `lessonschedule` WHERE `lessonid` = '$lessonid'");
$count = count($data);
if (!empty($lessonid) && $count == 1) {
$learn = $data[0]->learn; // Language to learn
$lessontype = $data[0]->lessontype;
$fname = $data[0]->fname;
$lname = $data[0]->lname;
$schedule = $data[0]->schedule;
$timezone = $data[0]->timezone;
$finaltime = $data[0]->end;
$level = $data[0]->level;
// Prepare to add event to calendar
/* $client_id = $this->client_id;
$email_address = $this->Email_address;
echo $key_file = $this->key_file_location;*/
$client_id = '87798193109-nfqn4777uopl6p6436hg5940lgqgl2om.apps.googleusercontent.com';
$Email_address = '87798193109-nfqn4777uopl6p6436hg5940lgqgl2om@developer.gserviceaccount.com';
$key_file_location = '/var/www/html/individuals/overcomethebarrier-134ae8a5be11.p12';
$client = new Google_Client();
putenv('GOOGLE_APPLICATION_CREDENTIALS=/var/www/html/individuals/overcomethebarrier-74639d760484.json');
$client->useApplicationDefaultCredentials();
$scopes ="https://www.googleapis.com/auth/calendar";
//$nl = html_entity_decode("&#13;");
if ($lessontype == "Group") {
$link = "CALL OPTIONS --------------- GOOGLE HANGOUTS: https://plus.google.com/hangouts/_/overcomethebarrier.com/group-".$lessonid." --------------- ZOOM: https://zoom.us/j/".$callid." --------------- APPEARIN: https://appear.in/".$lessonid." ------- TEACHERS: Please login to Google (www.gmail.com) using the account via a Chrome incognito browser in order to initiate the Hangouts call: teacher@overcomethebarrier.com";
} else {
$company = $data[0]->company;
$skype = $data[0]->skype;
$bilingual = $data[0]->bilingual;
$native = $data[0]->nlanguage;
$phone = $data[0]->phone;
$link = "CLIENT INFORMATION: Company:".$company." / Skype (or other):".$skype." / Phone: ".$phone." / Timezone: ".$timezone." / Native Language: ".$native." / Bilingual Teacher Necessary: ".$bilingual." / ID: ".$lessonid."";
}
$client->setScopes($scopes);
// Add event to calendar
$service = new Google_Service_Calendar($client);
if ($lessontype == "Group") {
$title = "Group ".$learn." Class (".$level.")";
} else {
$title = "Private ".$learn." Class with ".$fname." ".$lname."";
}
$event = new Google_Service_Calendar_Event(array(
'summary' => $title,
'location' => 'Google Hangouts (use link below)',
'description' => $link,
'start' => array(
'dateTime' => $schedule,
'timeZone' => $timezone,
),
'end' => array(
'dateTime' => $finaltime,
'timeZone' => $timezone,
),
'reminders' => array(
'useDefault' => FALSE,
'overrides' => array(
array('method' => 'email', 'minutes' => 24 * 60),
array('method' => 'popup', 'minutes' => 10),
),
),
));
$optParams = array("sendNotifications"=>true);
$calendarId = 'admin@overcomethebarrier.com';
$event = $service->events->insert($calendarId, $event, $optParams);
$gcalid = $event->id;
$insert_qry = DB::update("UPDATE `lessonschedule` SET `gcalid` = '".$gcalid."' WHERE `lessonid` = '".$lessonid."'");
} else {
echo "No lesson ID is specified, or lesson not in the database.";
//throw new Exception("No lesson ID is specified, or lesson not in the database.");
}
}
public static function addInvite($lessonid,$accountid) {
$query = DB::select("SELECT * FROM `lessonschedule` WHERE `lessonid` = '".$lessonid."'");
$count = count($query);
$query2 = DB::select("SELECT * FROM `confirmedusers` WHERE `accountid` = '".$accountid."'");
$count2 = count($query2);
if (!empty($lessonid) && $count == 1 && $count2 == 1) {
//$data = $query->fetch(PDO::FETCH_LAZY);
$gcalid = $query[0]->gcalid;
//$data2 = $query2->fetch(PDO::FETCH_LAZY);
$clientemail = $query2[0]->email;
$secemail = $query2[0]->secemail;
$timezone = $query2[0]->timezone;
date_default_timezone_set($timezone);
// Prepare Calendar Action
$client = new Google_Client();
putenv('GOOGLE_APPLICATION_CREDENTIALS=/var/www/html/individuals/overcomethebarrier-74639d760484.json');
$client->useApplicationDefaultCredentials();
$scopes = "https://www.googleapis.com/auth/calendar";
$calendarId = 'admin@overcomethebarrier.com';
$client->setScopes($scopes);
$service = new Google_Service_Calendar($client);
// End Calendar Action
$event = $service->events->get($calendarId, $gcalid);
$attendeesx = $event->getAttendees();
$len = count($attendeesx);
$toadd = array();
if ($len > 1) {
for ($x = 0; $x <= $len-1; $x++) {
array_push($toadd,array('email' => $attendeesx[$x]['email']));
}
}
if ($len == 1) {
array_push($toadd,array('email' => $attendeesx[0]['email']));
}
if (!empty($clientemail) && empty($secemail)) {
array_push($toadd,array('email' => $clientemail));
}
if (!empty($clientemail) && !empty($secemail)) {
array_push($toadd,array('email' => $clientemail));
array_push($toadd,array('email' => $secemail));
}
$event->setAttendees($toadd);
$optParams = array("sendNotifications"=>true);
$updatedEvent = $service->events->update($calendarId, $event->getId(), $event, $optParams);
} else {
echo "Lesson ID not valid or not provided, or client doesn't exist.";
//throw new Exception("Lesson ID not valid or not provided, or client doesn't exist.");
}
}
public static function addTeacherToInvite($lessonid,$teacher_email) {
$query = DB::select("SELECT * FROM `lessonschedule` WHERE `lessonid` = '".$lessonid."'");
$count = count($query);
$query2 = DB::select("SELECT * FROM `login` WHERE `email` = '".$teacher_email."'");
$count2 = count($query2);
if (!empty($lessonid) && $count == 1 && $count2 == 1) {
$gcalid = $query[0]->gcalid;
$timezone = $query2[0]->timezone;
date_default_timezone_set($timezone);
// Prepare Calendar Action
$client = new Google_Client();
putenv('GOOGLE_APPLICATION_CREDENTIALS=/var/www/html/individuals/overcomethebarrier-74639d760484.json');
$client->useApplicationDefaultCredentials();
$scopes = "https://www.googleapis.com/auth/calendar";
$calendarId = 'admin@overcomethebarrier.com';
$client->setScopes($scopes);
$service = new Google_Service_Calendar($client);
// End Calendar Action
$event = $service->events->get($calendarId, $gcalid);
$attendeesx = $event->getAttendees();
$len = count($attendeesx);
$toadd = array();
if ($len > 1) {
for ($x = 0; $x <= $len-1; $x++) {
array_push($toadd,array('email' => $attendeesx[$x]['email']));
}
}
if ($len == 1) {
array_push($toadd,array('email' => $attendeesx[0]['email']));
}
if (!empty($teacher_email)) {
array_push($toadd,array('email' => $teacher_email));
}
$event->setAttendees($toadd);
$optParams = array("sendNotifications"=>true);
$updatedEvent = $service->events->update($calendarId, $event->getId(), $event, $optParams);
} else {
echo "Lesson ID not valid or not provided, or teacher doesn't exist.";
//throw new Exception("Lesson ID not valid or not provided, or teacher doesn't exist.");
}
}
public static function getSpecificAccInfo($accountid,$column)
{
$query = DB::select("SELECT `$column` as `return` FROM `confirmedusers` WHERE `accountid` = '$accountid'");
return $query[0]->return;
}
public static function completedLessonTime($accountid)
{
$query = DB::select("SELECT sum(`lessonmins`) as `lessonmins` FROM `lessonschedule` WHERE `accountid` = '$accountid' AND `status` = 'Finished' AND `lessontype` = 'Individual'");
$time = $query[0]->lessonmins;
if (empty($time)) { $time = 0; }
return $time;
}
public static function scheduledLessonTimeIndividual($accountid) {
$query = DB::select("SELECT sum(`lessonmins`) as `lessonmins` FROM `lessonschedule` WHERE `accountid` = '$accountid' AND `lessontype` = 'Individual' AND (`status` IS NULL OR `status` = 'In Progress')");
$time = $query[0]->lessonmins;
if (empty($time)) { $time = 0; }
return $time;
}
public static function checkTeacherSchedule($teacher_email,$start,$lessonmins) {
$end = $start+($lessonmins*60);
$start++;
$end--;
$query = DB::select("SELECT * FROM `lessonschedule` WHERE `teacher` = '$teacher_email' AND ((`start_unix` BETWEEN '$start' AND '$end') OR (`end_unix` BETWEEN '$start' AND '$end'))");
$count = count($query);
return $count;
}
public static function availableLessonTimeIndividual($accountid) {
$query = DB::select("SELECT sum(`timepurchased`) as `lessonmins` FROM `lessontotals` WHERE `accountid` = '$accountid' AND `lessontype` = 'Private'");
$time = $query[0]->lessonmins;
if (empty($time)) { $time = 0; }
return $time;
}
public static function addindividuallesson($teacher_email,$client_email,$learn,$lessonmins,$bilingual,$start_unix,$company)
{
if (!empty($teacher_email) && !empty($client_email) && !empty($learn) && !empty($lessonmins) && !empty($bilingual) && !empty($company)) {
$query = confirmedusers::where('email','=',$client_email)->get();
$count = count($query);
if ($count == 1) {
$accountid = $query[0]['accountid'];
//$count
//Generate Lesson ID
$salt1 = rand(75000, 100000);
$salt2 = rand(50000, 75000);
$salt3 = sha1(md5($client_email));
$salt4 = time()*$salt1*$salt2;
$lessonid = md5($salt1.$salt2.$salt3.$salt4);
//End Generate Lesson Number
$source = $query[0]['source'];
$lessontype = "Individual";
//$lessonmins
$fname = $query[0]['fname'];
$cancel = $query[0]['cancellation_minutes'];
if (empty($cancel) && $source == "OCTB") {
$cancel = 14400;
}
$lname = $query[0]['lname'];
$timezone = $query[0]['timezone'];
//$client_email passed into the function
$secemail = $query[0]['secemail'];
$phone = $query[0]['phone'];
$nlanguage = $query[0]['nlanguage'];
$skype = $query[0]['skype'];
//$bilingual passed into the function
//$learn passed into the function
//Configure scheduling (schedule itself will cease to be used)
$start_unix=$start_unix;
if (empty($start_unix)) {
$start_unix = time();
}
date_default_timezone_set($timezone);
$end_unix = $start_unix+($lessonmins*60);
$start_google = date('Y-m-d\TH:i:sP', $start_unix); // Required format for Google calendar
$end_google = date('Y-m-d\TH:i:sP', $end_unix); // Required format for Google calendar
}
//End scheduling
// update1 not added because lesson begins immediately. Update1 = 24 hour advance warning
// update2 not added = Cancellation time (e.g. 4 hours), plus one hour advance warning for class.
// canceluntil not necessary as lesson begins immediately.
// gcalid placed in scheduleLesson() function.
// $teacher_email passed to function
if(empty($company))
{
return $response=array('response'=>'Please fill Company name');
}
if(empty($source))
{
return $response=array('response'=>'Please fill source');
}
if(empty($fname))
{
return $response=array('response'=>'Please fill fname name');
}
if(empty($lname))
{
return $response=array('response'=>'Please fill lname name');
}
if(empty($timezone))
{
return $response=array('response'=>'Please fill timezone');
}
if(empty($nlanguage))
{
return $response=array('response'=>'Please fill nlanguage');
}
/* if(empty($skype))
{
return $response=array('response'=>'Please fill skype');
}
if(empty($phone))
{
return $response=array('response'=>'Please fill phone');
}*/
if (!empty($accountid) && !empty($lessonid) && !empty($company) && !empty($source) && !empty($fname) && !empty($lname) && !empty($timezone) && !empty($nlanguage)) {
$entity= new lessonschedule;
$entity->accountid=$accountid;
$entity->count=$count;
$entity->lessonid=$lessonid;
$entity->company=$company;
$entity->source=$source;
$entity->lessontype=$lessontype;
$entity->lessonmins=$lessonmins;
$entity->fname=$fname;
$entity->lname=$lname;
$entity->timezone=$timezone;
$entity->email=$client_email;
$entity->secemail=$secemail;
$entity->phone=$phone;
$entity->nlanguage=$nlanguage;
$entity->bilingual=$bilingual;
$entity->learn=$learn;
$entity->schedule=$start_google;
$entity->start=$start_google;
$entity->start_unix=$start_unix;
$entity->end=$end_google;
$entity->end_unix=$end_unix;
$entity->skype=$skype;
$entity->canceluntil=$cancel;
$entity->teacher=$teacher_email;
$entity->confirmed='Yes';
if($entity->save())
{
$islesson = Lessonschedule::lessonExists($lessonid);
if($islesson==1)
{
return array('response'=>'Y','lessonid'=>$lessonid,'accountid'=>$accountid,'teacher_email'=>$teacher_email);
}
}
else
{
return $response=array('response'=>'N');
}
} else {
$response=array('response'=>'N');
return $response;
//throw new Exception("Necessary details are missing to schedule the lesson.");
}
} else {
$response=array('response'=>'N');
return $response;
//throw new Exception("Teacher email and/or client email not specified.");
}
}
public static function lessonExists($lessonid) {
$query = DB::select("SELECT `status` FROM `lessonschedule` WHERE `lessonid` = '$lessonid'");
$count = count($query);
return $count;
}
public static function updateStudentWalletAmt($accountid,$num) {
$query =DB::select("SELECT sum((`rate` * `lessonmins`)/60) as total FROM `lessonschedule` WHERE (`accountid` = '$accountid' OR `accountid2` = '$accountid' OR `accountid3` = '$accountid' OR `accountid4` = '$accountid') and rate>0");
$lessonminsRate=$query[0]->total;
$wallet_query = DB::select("SELECT SUM(`amount`) AS `total` FROM `wallet` WHERE `accountid` = '$accountid'");
$wallet = $wallet_query[0]->total;
$remaining = round($wallet-$lessonminsRate,2);
$remaining = number_format($remaining,2);
if (!empty($num)) {
$remaining = $remaining+$num;
}
$update = DB::update("UPDATE `confirmedusers` SET `wallet_balance`='$remaining' where `accountid`='$accountid'");
if ($update) {
return true;
}
}
public static function commissionRate($lessonmins,$teacher)
{
$wallet_query = DB::select("SELECT `privaterate` FROM `login` WHERE `email` = '$teacher'");
$rate = $wallet_query[0]->privaterate;
$pay = ($lessonmins/60)*$rate;
$commission = round(($pay*0.10),2);
return $commission;
}
public static function getTimesAvailable($teacher_email,$timezone) {
date_default_timezone_set($timezone);
$query =DB::select("SELECT * FROM `schedule` WHERE `type`='recurring' and `teacher` = '$teacher_email' ORDER BY `id` DESC LIMIT 50");
$times = array();
foreach ($query as $data) {
if($data->start_time!=NULL && $data->end_time!=NULL)
{
$su = $data->start_time;
$eu = $data->end_time;
// $start_h = date("H", $su);
// $end_h = date("H", $eu);
array_push($times,array("start" => $su,"end" => $eu));
}
}
return $times;
}
public static function countTimes($times) {
$start_times = array();
for ($x = 0; $x <= 23; $x++) {
$count = 0;
foreach ($times as $time) {
if ($time['start'] == $x) {
$count++;
}
}
array_push($start_times,array("hour" => $x,"frequency" => $count));
}
return $start_times;
}
public static function showGeneralAvailability($height,$width,$frequency) {
echo "<div style='height:".$height.";width:".$width.";overflow-y: scroll;'>";
foreach ($frequency as $time) {
if ($time['hour'] == 0) { $new_time = "12:00 AM"; }
if ($time['hour'] > 0 && $time['hour'] < 12) { $new_time = "".$time['hour'].":00 AM"; }
if ($time['hour'] == 12) { $new_time = "".$time['hour'].":00 PM"; }
if ($time['hour'] > 12) { $hour = $time['hour']-12; $new_time = "".$hour.":00 PM"; }
$count = $time['frequency'];
if ($count == 0) {
$bg = "#FFFFFF";
$color = "#e5e5e6";
}
if ($count > 0 && $count < 5) {
$bg = "#54d08c";
$color = "#FFFFFF";
}
if ($count >= 5) {
$bg = "#008e0c";
$color = "#FFFFFF";
}
// $time_div_width = $width/4;
echo "<div style='width:100%;font-size:1.2em;text-align:center;background:".$bg.";color:".$color.";'>";
echo $new_time;
echo "</div>";
}
echo "</div>";
}
}