package My_CronFunctions;

use strict;
use Time::CTime;
use Time::HiRes;

#--------------------------------------------------------------------------------------------------
sub new
{
  my $pclass = shift;
  my $pdbhMain = shift;
  my $pGLOBALS = shift;
  my $request_xml = shift;
  my $self = {
    dbh=>$pdbhMain,
    GLOBALS=>$pGLOBALS,
    logger=>Log::Log4perl->get_logger()
  };
  bless $self, $pclass;
  $self->{logger}->info("::CronFunctions::Instance Started");
  return $self;
}


#--------------------------------------------------------------------------------------------------
sub cron_expire_user_sessions($)
{
  my $this=shift;
  my ($xml) = @_;
  my $frxml="";
  my $rxml="";
  my $statement ="";
  my $cstatement ="";
  my $sth;
  my @row;
  my $sth2;
  my @row2;
  my $cols;
  my %MTABLE;
  my $month;

  $statement = "select *,id,dbdate_created from tUSER_SESSIONS where dbdate_last_used<date_sub(now(),interval 30 minute)";
  $this->{logger}->info($statement);
  $sth = $this->{dbh}->prepare("$statement");
  $sth->execute;
  if($sth->rows>0)
  {
    $cols = $sth->{'NUM_OF_FIELDS'};
   
    $statement = "show create table tUSER_SESSIONS;";
    $this->{logger}->info($statement);
    $sth2 = $this->{dbh}->prepare("$statement");
    $sth2->execute;
    @row2 = $sth2->fetchrow_array;
   
    $cstatement = $row2[1];
    $cstatement =~ s/CREATE TABLE/CREATE TABLE IF NOT EXISTS/ig;
    $cstatement = substr($cstatement,0,index($cstatement,"PRIMARY KEY (`id`)")+length("PRIMARY KEY (`id`)")).")";
   
    while(@row = $sth->fetchrow_array)
    {
      $month=substr($row[$cols-1],0,4).substr($row[$cols-1],5,2);

      if($MTABLE{$month} eq "")
      {
        $statement = $cstatement;
        $statement =~ s/`tUSER_SESSIONS`/`tUSER_SESSIONS_$month`/ig;
        $this->{logger}->info($statement);
        $sth2 = $this->{dbh}->prepare("$statement");
        $sth2->execute;
        $MTABLE{$month}=1;
      }

      $statement = "insert into tUSER_SESSIONS_$month values (";
      for(my $x=0;$x<$cols-2;$x++)
      {
        $statement .= "\"$row[$x]\",";
      }
      $statement = substr($statement,0,-1).")";
      #$this->{logger}->info($statement);
      $sth2 = $this->{dbh}->prepare("$statement");
      $sth2->execute;
      if($sth2->rows==1)
      {
        $statement = "delete from tUSER_SESSIONS where id=$row[$cols-2]";
        #$this->{logger}->info($statement);
        $sth2 = $this->{dbh}->prepare("$statement");
        $sth2->execute;
      }
    }
  }

  $frxml .= "<\?xml version=\"1.0\"\?>";
  $frxml .= "\n<api>";
  $frxml .= "\n<status>true</status>";
  $frxml .= "$rxml\n";
  $frxml .= "\n</api>";
  return($frxml); 
}

#--------------------------------------------------------------------------------------------------
sub cron_expire_user_event_request($)
{
  my $this=shift;
  my ($xml) = @_;
  my $frxml="";
  my $rxml="";
  my $statement ="";
  my $cstatement ="";
  my $sth;
  my @row;
  my $sth2;
  my @row2;
  my $cols;
  my %MTABLE;
  my $month;

	$statement = "select id,tUSER_id,tEVENT_id from tUSER_EVENT where tUSER_STATUS_id=(select id from tUSER_STATUS where value='Requested Availability') and dbdate_added<date_sub(now(),interval 2 day)";
  $this->{logger}->info($statement);
  $sth = $this->{dbh}->prepare("$statement");
  $sth->execute;
	while(@row = $sth->fetchrow_array)
	{
		$statement = "update tUSER_EVENT set tUSER_STATUS_id=(select id from tUSER_STATUS where value='Auto Declined') where id=$row[0]";
		$this->{logger}->info($statement);
		$sth2 = $this->{dbh}->prepare("$statement");
		$sth2->execute;
	
		$statement = "insert into tUSER_NOTIFICATION (tUSER_id,message,shown,dbdate_added) values ((select tUSER_id from tUSER_EVENT where tEVENT_id=$row[2] and tUSER_STATUS_id=(select id from tUSER_STATUS where value='Event Orginazer')),concat('A User Changed their status (Auto Declined) to your event: ',(select event_name from tEVENT where id=$row[2])),0,now())";
		$this->{logger}->info($statement);
		$sth2 = $this->{dbh}->prepare("$statement");
		$sth2->execute;  

		$statement = "insert into tUSER_NOTIFICATION (tUSER_id,message,shown,dbdate_added) values ($row[1],'The System Automatically Declined an Event for you after two days of no response.',0,now())";
		$this->{logger}->info($statement);
		$sth2 = $this->{dbh}->prepare("$statement");
		$sth2->execute;  
	}
	
	

  $frxml .= "<\?xml version=\"1.0\"\?>";
  $frxml .= "\n<api>";
  $frxml .= "\n<status>true</status>";
  $frxml .= "$rxml\n";
  $frxml .= "\n</api>";
  return($frxml); 
}

#--------------------------------------------------------------------------------------------------
sub cron_notification_reminders($)
{
  my $this=shift;
  my ($xml) = @_;
  my $frxml="";
  my $rxml="";
  my $statement ="";
  my $cstatement ="";
  my $sth;
  my @row;
  my $sth2;
  my @row2;
	my $current_time=strftime("%H%M",localtime(time));
	my $SystemGlobals=My_GenericFunctions->GetSystemGlobals($this);	
	my $notifications="New Notifications:\n";
	my $notification_html="";
	
	if($current_time eq "0800")
	{
		$statement = "select email,msisdn,tAGENCY_id,id from tUSER where deleted=0";
		$this->{logger}->info($statement);
		$sth = $this->{dbh}->prepare("$statement");
		$sth->execute;
		while(@row = $sth->fetchrow_array)
		{
			my $AgencyGlobals=My_GenericFunctions->GetPlatformGlobals($this,$row[2]);
		
			$notifications="New Notifications:\n";
			$notification_html="";
			
			$statement = "select message,dbdate_added from tUSER_NOTIFICATION where tUSER_id=$row[3] and shown=0 and dbdate_added>date_sub(now(),interval 1 day) order by id";
			$this->{logger}->info($statement);
			$sth2 = $this->{dbh}->prepare("$statement");
			$sth2->execute;
			if($sth2->rows>0)
			{
				while(@row2 = $sth2->fetchrow_array)
				{
					if(index($row2[0],"~")>-1)
					{
						$row2[0] = substr($row2[0],index($row2[0],"~")+1);
					}
					$notifications .= "$row2[1] : $row2[0]\n"; 
					$notification_html .= "<tr><td>$row2[1]</td><td>$row2[0]</td></tr>\n"; 
				}
				if($row[0] ne "")
				{
					my $notification_html_full = $AgencyGlobals->{NotificationReminderEmail};
					my $tmp = "~~NOTIFICATIONS~~";
					my $tmp2 = $notification_html;
					$notification_html_full =~ s/$tmp/$tmp2/ig;
					
					My_GenericFunctions->SendEmail($this,$row[0],"noreply\@".$SystemGlobals->{from_email},'Notification Reminder',$notification_html_full);
				}
				else
				{
					My_GenericFunctions->SendSMS($this,$row[2],$row[1],$notifications);
				}
			}
		}		
	}

  $frxml .= "<\?xml version=\"1.0\"\?>";
  $frxml .= "\n<api>";
  $frxml .= "\n<status>true</status>";
  $frxml .= "$rxml\n";
  $frxml .= "\n</api>";
  return($frxml); 
}

#--------------------------------------------------------------------------------------------------
sub cron_event_reminders($)
{
  my $this=shift;
  my ($xml) = @_;
  my $frxml="";
  my $rxml="";
  my $statement ="";
  my $cstatement ="";
  my $sth;
  my @row;
  my $sth2;
  my @row2;
	my $SystemGlobals=My_GenericFunctions->GetSystemGlobals($this);	
	my $notifications="New Notifications:\n";
	my $notification_html="";

	my $current_time=strftime("%H%M",localtime(time));
	my $SystemGlobals=My_GenericFunctions->GetSystemGlobals($this);	
	
	#$current_time="0800"; 
	if($current_time eq "0800")
	{
		$statement = "select email,msisdn,tAGENCY_id,id from tUSER where deleted=0";
		$this->{logger}->info($statement);
		$sth = $this->{dbh}->prepare("$statement");
		$sth->execute;
		while(@row = $sth->fetchrow_array)
		{
			$notifications="Event Reminders:\n";
			$notification_html="";
		
			my $AgencyGlobals=My_GenericFunctions->GetPlatformGlobals($this,$row[2]);
			
			$notifications .= "$row2[1] : $row2[0]\n"; 
			$notification_html .= "$row[1] : $row[0]<br>"; 
			
			$statement = "select id,event_name,event_dbdate,address,duration,(select value from tEVENT_STATUS where id=tEVENT_STATUS_id),(select value from tUSER_STATUS where id=(select tUSER_STATUS_id from tUSER_EVENT where tEVENT_id=tEVENT.id and tUSER_id=$row[3])),(select count(*) from tUSER_EVENT where tEVENT_id=tEVENT.id and confirmed=1 and tUSER_STATUS_id not in (select id from tUSER_STATUS where value in ('Declined','Event Orginazer','Event Orginazer'))),staff_count,(select count(*) from tUSER_EVENT where tEVENT_id=tEVENT.id and confirmed=1 and tUSER_STATUS_id in (select id from tUSER_STATUS where value in ('Accepted'))) from tEVENT where event_dbdate between now() and date_add(now(),interval 7 day) and deleted=0 and id in (select tEVENT_id from tUSER_EVENT where tUSER_id=$row[3] and tUSER_STATUS_id not in (select id from tUSER_STATUS where value in ('Declined','Auto Declined'))) order by event_dbdate";
			$this->{logger}->info($statement);
			$sth2 = $this->{dbh}->prepare("$statement");
			$sth2->execute;
			if($sth2->rows>0)
			{
				while(@row2 = $sth2->fetchrow_array)
				{
					$notifications .= "$row2[1] : $row2[2]\n"; 
					if($row2[5] eq "New"&& $row2[6] eq "Event Orginazer")
					{
						if($row2[7] != $row2[8])
						{
							$row2[5] = "Please select staff for the event";							
						}
						else
						{
							if($row2[9] != $row2[8])
							{
								$row2[5] = "Waiting for staff to confirm";							
							}
						}
					}
					
					$notification_html .= "<tr><td>$row2[1]</td><td>$row2[2]</td><td>$row2[5]</td><td>$row2[6]</td><td>$row2[3]</td></tr>\n"; 
				}			
				if($row[0] ne "")
				{
					my $notification_html_full = $AgencyGlobals->{EventReminderEmail};
					my $tmp = "~~EVENTS~~";
					my $tmp2 = $notification_html;
					$notification_html_full =~ s/$tmp/$tmp2/ig;
					
					$notification_html_full =~ s/\"/\\\"/ig;
					My_GenericFunctions->SendEmail($this,$row[0],"noreply\@".$SystemGlobals->{from_email},'Event Reminder',$notification_html_full);
				}
				else
				{
					My_GenericFunctions->SendSMS($this,$row[2],$row[1],$notifications);
				}				
			}
		}		
	}
	
	$statement = "update tEVENT set tEVENT_STATUS_id=(select id from tEVENT_STATUS where value='Completed') where event_dbdate<date_sub(now(), interval 12 hour)";
	$this->{logger}->info($statement);
	$sth = $this->{dbh}->prepare("$statement");
	$sth->execute;
	

  $frxml .= "<\?xml version=\"1.0\"\?>";
  $frxml .= "\n<api>";
  $frxml .= "\n<status>true</status>";
  $frxml .= "$rxml\n";
  $frxml .= "\n</api>";
  return($frxml); 
}

#--------------------------------------------------------------------------------------------------
sub cron_new_staff($)
{
  my $this=shift;
  my ($xml) = @_;
  my $frxml="";
  my $rxml="";
  my $statement ="";
  my $cstatement ="";
  my $sth;
  my @row;
  my $sth2;
  my @row2;
	my $SystemGlobals=My_GenericFunctions->GetSystemGlobals($this);	
	my $notification_html_head="";
	my $notification_html="";
	my $current_time=strftime("%u%H%M",localtime(time));

	#$current_time = "10800";	
	if($current_time eq "10800")
	{

		$statement = "select id,domain from tAGENCY where deleted=0";
		$this->{logger}->info($statement);
		$sth = $this->{dbh}->prepare("$statement");
		$sth->execute;
		while(@row = $sth->fetchrow_array)
		{
			$notification_html_head='<html>
	 <head>
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
		<!-- <link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> -->
		<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
		<script src="https://code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
		
		<!-- Bootstrap -->
		<link href="http://'.$row[1].'/EventStaffWEB/css/bootstrap.min.css" rel="stylesheet">
		<script src="http://'.$row[1].'/EventStaffWEB/js/bootstrap.js"></script>
			
		<!-- Notify Lib -->
		<script src="http://'.$row[1].'/EventStaffWEB/js/bootstrap-notify.js"></script>
					
		<!-- Main Site CSS -->
		<link href="http://'.$row[1].'/EventStaffWEB/css/main.css" rel="stylesheet">
		<script src="http://'.$row[1].'/EventStaffWEB/js/main.js" type="text/javascript"></script>
	 </head>';
	 		$notification_html ='
	 <body align=\'center\'>
	 <h2 align=\'center\'>New Recruites for the Last Week</h2><br>
	 <table width=\'90%\' border=0 align=\'center\'>';
			
			$statement = "select hex(aes_encrypt(id,'tUSER')) from tUSER where tUSER_TYPE_id=(select id from tUSER_TYPE where value='Event Staff') and (tAGENCY_id=$row[0] or ((select value from tAGENCY_SETTINGS where tAGENCY_id=tUSER.tAGENCY_id and tAGENCY_SETTING_TYPES_id=(select id from tAGENCY_SETTING_TYPES where value='ShareEventStaff'))=1 and (select value from tAGENCY_SETTINGS where tAGENCY_id=$row[0] and tAGENCY_SETTING_TYPES_id=(select id from tAGENCY_SETTING_TYPES where value='UseSharedEventStaff'))=1)) and active=1 and dbdate_activated>date_sub(now(),interval 7 day)";
			$this->{logger}->info($statement);
			$sth2 = $this->{dbh}->prepare("$statement");
			$sth2->execute;
			if($sth2->rows>0)
			{
				while(@row2 = $sth2->fetchrow_array)
				{
					$notification_html.=" <tr><td height='400px'><iframe width='100%' height='100%' src='http://$row[1]/EventStaffWEB/index.cgi?action=user_view&user_id=$row2[0]'></iframe></td></tr>
		";
				}
				
				$notification_html .= ' </table>
				<h5 align=\'center\'><a href="http://'.$row[1].'/EventStaffWEB/index.cgi?action=new_staff_'.$row[0].'">View in Browser</a></h5>
 </body>
</html>';

				open(OUTFILE,"> /var/www/EventStaffWEB/new_staff_$row[0].html");
				print OUTFILE '<nav class="navbar navbar-inverse navbar-fixed-top">
<div class="container-fluid">
  <div class="navbar-header">
    <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
      <span class="sr-only">Toggle navigation</span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </button>
    <a class="navbar-brand" href="#">Home</a>
    <a class="navbar-brand" href="?action=main_window_user_functions_user_list">Browse Staff</a>
  </div>
  <div id="navbar" class="navbar-collapse collapse">
    <ul class="nav navbar-nav navbar-right">
      <li ><a href="?action=user_functions_register">Register</a></li>
      <li ><a href="?action=user_functions_login">Login</a></li>
    </ul>
</div>
</nav>
';
				print OUTFILE $notification_html;
				close(OUTFILE);

				$notification_html =~ s/\"/\\\"/ig;
				
				
				$statement = "select email from tUSER where tAGENCY_id=$row[0] and tUSER_TYPE_id=(select id from tUSER_TYPE where value='Client') and deleted=0";
				$this->{logger}->info($statement);
				$sth2 = $this->{dbh}->prepare("$statement");
				$sth2->execute;
				while(@row2 = $sth2->fetchrow_array)
				{			
					My_GenericFunctions->SendEmail($this,$row2[0],"noreply\@".$SystemGlobals->{from_email},'Newly Added Event Staff',$notification_html_head.$notification_html);
				}
			}
		}	
	}
	
  $frxml .= "<\?xml version=\"1.0\"\?>";
  $frxml .= "\n<api>";
  $frxml .= "\n<status>true</status>";
  $frxml .= "$rxml\n";
  $frxml .= "\n</api>";
  return($frxml); 
}

1;