Demo entry 4051957

cronjob

   

Submitted by anonymous on Mar 15, 2016 at 10:47
Language: Perl. Code size: 11.6 kB.

#!/usr/bin/perl -w
# cronjob.pl
# Starts the cronjob for pirate bay tours.

# example calls:
# ./cronjob.pl optimize | cronjob

# package needed for dump: libdata-dump-perl



use strict;
use warnings;

use db;
use Data::Dump qw(dump);

my $dbh = db_connect();

sub println {
	my ($line) = @_;
	print $line . "\n";
}

sub getBookingsFromToday {
	my @ids = ();
	my $dateToday = getSQLDate(time());
	my $sql = 'SELECT "tourID" FROM "pbt_booking" WHERE "bookingDate" = ? GROUP BY "tourID" ORDER BY "tourID" ASC;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $dateToday );
	db_execute($sth);
	while (my $row = db_fetch_row($sth)) {
		push(@ids, $row->{'tourID'});
	}
	return @ids;
}

sub getTour {
	my ($id) = @_;
	my $sql = 'SELECT * FROM "pbt_tour" WHERE "id" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id);
	db_execute($sth);
	my $row = db_fetch_row($sth);
	return $row;
}

sub getBookingsForTour {
	my ($id) = @_;
	my @bookings = ();
	my $sql = 'SELECT * FROM "pbt_booking" WHERE "tourID" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id);
	db_execute($sth);
	while (my $row = db_fetch_row($sth)) {
		push(@bookings, $row);
	}
	return @bookings;
}

sub getEmployees {
	my ($id) = @_;
	my @employees = ();
	my $sql = 'SELECT  "id", "quota" FROM pbt_employee;';
	my $sth = db_prepare($dbh,$sql);
	db_execute($sth);
	while (my $row = db_fetch_row($sth)) {
		push(@employees, $row);
	}
	return @employees;
}

sub updateQuota {
	my ($id,$quota) = @_;
	my $sql = 'UPDATE pbt_employee SET "quota"=? WHERE "id"=?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $quota);
	$sth->bind_param(2, $id);
	db_execute($sth);

}


sub getQuotaSum {
	my $sql = 'SELECT  SUM("quota") AS amount FROM pbt_employee;';
	my $sth = db_prepare($dbh,$sql);
	db_execute($sth);
	my $row = db_fetch_row($sth);
	if (!$row->{'amount'}) {
		return 0;
	}
	return $row->{'amount'};
}



sub getBookingCountByEmployeeLocal{
	my ($id,$employeeID) = @_;
	my $sql = 'SELECT SUM("numPersons") AS amount FROM pbt_booking WHERE "tourID" = ? AND "employeeID" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id);
	$sth->bind_param(2, $employeeID);
	db_execute($sth);
	my $row = db_fetch_row($sth);
	if (!$row->{'amount'}) {
		return 0;
	}
	return $row->{'amount'};
}

sub getBookingCountByEmployeeMaster {
	my ($id,$employeeID) = @_;
	my $sql = 'SELECT SUM("numPersons") AS amount from pbt_booking_master WHERE "tourID" = ? AND "employeeID" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id );
	$sth->bind_param(2, $employeeID);
	db_execute($sth);
	my $row = db_fetch_row($sth);
	if (!$row->{'amount'}) {
		return 0;
	}
	return $row->{'amount'};
}


sub getBookingCountByEmployee{
	my ($id,$employeeID) = @_;
	return getBookingCountByEmployeeLocal($id,$employeeID)+getBookingCountByEmployeeMaster($id,$employeeID)

}

sub getBookingCountLocal {
	my ($id) = @_;
	my $sql = 'SELECT SUM("numPersons") AS amount from pbt_booking WHERE "tourID" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id);
	db_execute($sth);
	my $row = db_fetch_row($sth);
	if (!$row->{'amount'}) {
		return 0;
	}
	return $row->{'amount'};
}

sub getBookingCountMaster {
	my ($id) = @_;
	my $sql = 'SELECT SUM("numPersons") AS amount from pbt_booking_master WHERE "tourID" = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id );
	db_execute($sth);
	my $row = db_fetch_row($sth);
	if (!$row->{'amount'}) {
		return 0;
	}
	return $row->{'amount'};
}

sub getTotalBookingCount {
	my ($id) = @_;
	return getBookingCountLocal($id) + getBookingCountMaster($id);
}

sub isTourOverbooked {
	my ($capacity, $currentReservations) = @_;
	return $currentReservations > $capacity;
}

sub insertBookingIntoMaster {
	my ($booking) = @_;
	my $sql = 'INSERT INTO pbt_booking_master("tourID", "numPersons", "bookingDate", "employeeID") VALUES (?, ?, ?, ?);';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $booking->{'tourID'} );
	$sth->bind_param(2, $booking->{'numPersons'} );
	$sth->bind_param(3, getSQLDate(time()));
	$sth->bind_param(4, $booking->{'employeeID'} );
	db_execute($sth);
	return $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'pbt_booking_master_id_seq'});
}

sub createBillForBooking {
	my ($bookingID, $booking) = @_;

	my $tour = getTour($booking->{'tourID'});
	my $price = $tour->{'pricePerPerson'} * $booking->{'numPersons'};

	my $sql = 'INSERT INTO pbt_bill("customerID", "employeeID", "bookingID", "billDate", price)
    	                VALUES (?, ?, ?, ?, ?);';
	
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $booking->{'customerID'} );
	$sth->bind_param(2, $booking->{'employeeID'} );
	$sth->bind_param(3, $bookingID );
	$sth->bind_param(4, getSQLDate(time()));
	$sth->bind_param(5, $price);
	db_execute($sth);
}

sub deleteLocalBookingById {
	my ($id) = @_;	
	my $sql = 'DELETE FROM pbt_booking WHERE id = ?;';
	my $sth = db_prepare($dbh,$sql);
	$sth->bind_param(1, $id);
	db_execute($sth);
}

sub deleteLocalBooking {
	my ($booking) = @_;
	deleteLocalBookingById($booking->{'id'});
}

sub acceptBookings {
	my (@bookings) = @_;
	
	foreach my $booking (@bookings) {
		my $masterID = insertBookingIntoMaster($booking);
		createBillForBooking($masterID, $booking);
		deleteLocalBooking($booking);
	}
}

sub findRejectedBooking {
	my ($bookingsRef, $overbookedSize) = @_;
	my @bookings = @{$bookingsRef};

	my $bookingID  = $bookings[0]->{'id'};
	my $minDiff = abs ( $bookings[0]->{'numPersons'} - $overbookedSize);

	foreach my $booking (@bookings) {
		my $diff = abs ( $booking->{'numPersons'} - $overbookedSize);
		if ($diff < $minDiff) {
			$minDiff = $diff;
			$bookingID = $booking->{'id'};
		}
	}

	return $bookingID;
}

sub resolveOverbooking{
	my @tourIds = getBookingsFromToday();
	foreach my $tourID (@tourIds) {
		my $tour = getTour($tourID);
		my @bookings = getBookingsForTour($tourID);
		my $capacity = $tour->{'personCapacity'};
		my $totalCount;
		
		
		
		while (isTourOverbooked($capacity, $totalCount=getTotalBookingCount($tourID))) {
			println "tour $tourID is overbooked... i have to do something about it...";
			my $overbookedSize =  $totalCount - $capacity;
			my $rejected = findRejectedBooking(\@bookings, $overbookedSize);
			deleteLocalBookingById($rejected);
			println "rejecting the following booking $rejected";
			@bookings = getBookingsForTour($tourID);
		}

		acceptBookings(@bookings);
		println "bookings of tour $tourID transfered";
	}

}


my ($cmd, @other) = @ARGV;

if (!defined($cmd)) {
	print "please call ./cronjob <cmd>!\navailable cmds are:\n - optimize\n - cronjob\n";
	exit(-1);
}

my @tables = ("pbt_bill",  "pbt_booking",  "pbt_booking_master",  "pbt_customer",  "pbt_divison",  "pbt_employee",  "pbt_employee_login",  "pbt_harbor",
			  "pbt_harbor_tour", "pbt_tour"
			 );

if ($cmd eq "optimize") {
	foreach my $table (@tables) {
		print "table $table : \n";
		$dbh->do('VACUUM (VERBOSE, ANALYZE) "'.$table.'"');
		print "\n\n";
	}
}
elsif($cmd eq "cronjob") {
	print "running cronjob now...\n";
	
	my @employees = getEmployees();
	my @tourIds = getBookingsFromToday();
	
	my $tourCount=scalar(@tourIds);
	my $employeeCount=scalar(@employees);
	my $quotaSum=getQuotaSum();
	
	my $maxQuotaDelta=0.15;
	my $minQuota=sprintf("%.4f",($quotaSum/$employeeCount)*0.5);
	my $reductionsSum=0;
	println "Calculated min quota as $minQuota";

	foreach my $employee (@employees) {
		my $sellingPerformance=0;
		foreach my $tourId (@tourIds) {
			my $soldTicketsByEmployee=getBookingCountByEmployee($tourId,$employee->{'id'});
			my $tour = getTour($tourId);
			my $capacity = $tour->{'personCapacity'};
			my $soldPercent = sprintf("%.4f",$soldTicketsByEmployee/$capacity);
			$sellingPerformance+=$soldPercent;
		}
		$sellingPerformance/=$tourCount;
		$sellingPerformance/=$employee->{'quota'};
		if($sellingPerformance > 1.0 ){
			$sellingPerformance = 1.0;
		}else{
			$sellingPerformance = sprintf("%.4f",$sellingPerformance);
		}
		
		$employee->{'performance'}=$sellingPerformance;
		println "employee $employee->{'id'} has a selling performance of ".($sellingPerformance*100)." %";
		my $quota=$employee->{'quota'};
		
		if($sellingPerformance<1.0){
			println "Possible quota reduction ".((1.0-$sellingPerformance)*$quota);
			if((1.0-$sellingPerformance)*$quota>$maxQuotaDelta){
				$employee->{'reduction'}=$maxQuotaDelta;
			}elsif($quota-((1.0-$sellingPerformance)*$quota)<$minQuota){
				$employee->{'reduction'}=($quota-$minQuota);
			}else{
				$employee->{'reduction'}=$quota-((1.0-$sellingPerformance)*$quota);
			}
			
		}else{
			$employee->{'reduction'}=0;
		}
		$reductionsSum += $employee->{'reduction'};
		
	}
	
	my $minPerformance=1.0;
	
	foreach my $employee (@employees){
		if($minPerformance > $employee->{'performance'}){
			$minPerformance = $employee->{'performance'};
		}
	}
	
	if($minPerformance==1.0){
		# If all employees performed at 100%. Everyone looses 10% of his quota, before the sum of all looses is equally distributed again.
		foreach my $employee (@employees){
			$employee->{'reduction'} = $employee->{'quota'}*0.1;
		}
	}
	
	my $overallPerformanceMax = 0;
	foreach my $employee (@employees){
		$overallPerformanceMax +=	$employee->{'performance'};
	}
	my $roundedSum=0;
	foreach my $employee (@employees){
		$employee->{'performance'}=	$employee->{'performance'}/$overallPerformanceMax;
		$employee->{'quota'}=	sprintf("%.3f",(($employee->{'quota'}-$employee->{'reduction'})+(($employee->{'performance'}*$reductionsSum) > $maxQuotaDelta ? ($maxQuotaDelta*0.75) : ($employee->{'performance'}*$reductionsSum))));
		$roundedSum += $employee->{'quota'};
	}
	my $quotaLeft = ($quotaSum-$roundedSum>0.0) ? sprintf("%.3f",($quotaSum-$roundedSum)) :0.0;
	my $minQuotaEmployee = $employees[0];
	
	$roundedSum=0;
	foreach my $employee (@employees){
		$employee->{'quota'}+=sprintf("%.3f",$quotaLeft*$employee->{'performance'});
		$roundedSum += $employee->{'quota'};
	}
	$quotaLeft = ($quotaSum-$roundedSum>0.0) ? sprintf("%.3f",($quotaSum-$roundedSum)) :0.0;
	
	foreach my $employee (@employees){
		if($minQuotaEmployee->{'quota'} > $employee->{'quota'}){
			$minQuotaEmployee=$employee;
		}
	}
	$minQuotaEmployee->{'quota'}+=$quotaLeft;
	
	
	my @oldEmployees = getEmployees();
	
	for(my $i=0;$i<$employeeCount;$i++){
		println "Changing quota of $employees[$i]->{'id'}: $oldEmployees[$i]->{'quota'} -> $employees[$i]->{'quota'}";
		updateQuota($employees[$i]->{'id'},$employees[$i]->{'quota'});
	}
	
	dump @employees;
	
	
	resolveOverbooking();
	
	
}

sub deleteOldJobs {
	my $sth = db_query($dbh, "SELECT B.fileName FROM irc_crawl_job AS A, irc_crawl_job_channels AS B WHERE A.starttime < (NOW() - INTERVAL 30 DAY) AND A.id = B.jobID AND B.fileName != '';");

	while (my $row = db_fetch_row($sth)) {
		my $fileName = $row->{'fileName'};
		deleteFile($fileName);
		#print "need to delete $fileName!\n";
	}
	
	$dbh->do("DELETE FROM irc_crawl_job WHERE starttime < (NOW() - INTERVAL 30 DAY);");
	$dbh->do("DELETE FROM irc_crawl_job_channels USING  irc_crawl_job_channels LEFT JOIN irc_crawl_job ON(irc_crawl_job.id = irc_crawl_job_channels.jobID) WHERE irc_crawl_job.id IS NULL;");
}

sub deleteFile {
	my ($fileName) = @_;
	
	my $pid = fork();

	if ($pid == 0) {

		my @args = ();
		push(@args, "/bin/rm");
		push(@args, "-f");
		push(@args, $fileName);
	
		#print "executing ";
		#foreach my $arg (@args) {
		#	print $arg . " ";
		#}
	
		#print "\n";

		exec(@args);
		die("cant start new process");
	}
	
	waitpid($pid, 0);

	my $retCode = $?;

	if ($retCode != 0) {
		print "rm of $fileName failed\n";
	}
}

This snippet took 0.03 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).