#!/usr/bin/perl
use DateTime;
use DateTime::TimeZone;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;
use Data::Dumper qw(Dumper);
#use Unicode::Escape;
local ($buffer, @pairs, $pair, $name, $value, %FORM);
# Read in text
$ENV{'REQUEST_METHOD'} =~ tr/a-z/A-Z/;
if ($ENV{'REQUEST_METHOD'} eq "GET") {
$buffer = $ENV{'QUERY_STRING'};
}
# Split information into name/value pairs
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%(..)/pack("C", hex($1))/eg;
$FORM{$name} = $value;
}
$first_name = $FORM{first_name};
$last_name = $FORM{last_name};
$blogp = $FORM{blogp};
$blogp =~ s/%2C/,/ig;
$blogi = $blogp;
my $blogt = $FORM{blogt};
$blogt =~ s/%2C/,/ig;
$oneletter = substr( $first_name, 0, 1 );
#my $uemoji = Unicode::Escape->new($FORM{emoji});
my $emoji = chr(0x0001F517); # "\xf0\x9f\x98\x80"; #$uemoji->unescape;
#my $ucutei = Unicode::Escape->new($FORM{cutei});
my $cutei = chr(0x0001F3C3); # "\xf0\x9f\x98\x80"; #$ucutei->unescape;
#my $uimgei = Unicode::Escape->new($FORM{imgei});
my $imgei = chr(0x0001F5BC); # "\xf0\x9f\x98\x80"; #$uimgei->unescape;
$dt = DateTime->now; # same as ( epoch => time )
$dttz = DateTime::TimeZone->new( name => 'local' )->name();
$dt->set_time_zone( $dttz );
$year = $dt->year;
$month = $dt->month; # 1-12
$day = $dt->day; # 1-31
$dow = $dt->day_of_week; # 1-7 (Monday is 1)
$hour = $dt->hour; # 0-23
$minute = $dt->minute; # 0-59
$second = $dt->second; # 0-61 (leap seconds!)
$doy = $dt->day_of_year; # 1-366 (leap years)
$doq = $dt->day_of_quarter; # 1..
$qtr = $dt->quarter; # 1-4
$dmy = $dt->dmy('/'); # 06/12/2002
$hms = $dt->hms; # 14:02:29
$tt = "";
$cont = "";
$oneletter = " ";
print "Content-type:text/html\r\n\r\n";
print "";
print "
";
print "Hello - Second CGI Program";
print "";
print "";
print "";
print "Hello $first_name $last_name
Local:
Server: $dmy $hms $dttz
";
if ($blogp == "") {
$blogp = $first_name;
$oneletter = substr( $blogp, 0, 1 );
}
if ($blogp != "") {
$oneletter = substr( $blogp, 0, 1 );
if ((ord($oneletter) >= ord("0") && ord($oneletter) <= ord("9")) || ord($oneletter) == 40 ){
my ($oneurl) = split /,/, $blogp;
if ($oneurl == $blogp) {
$oneletter = substr( $blogp, 0, 1 );
} else {
if (ord($oneletter) >= ord("0") && ord($oneletter) <= ord("9")) {
$oneletter = "1";
$blogp = "( $blogp )";
} else {
$oneletter = "2";
}
}
}
}
if (ord($oneletter) >= ord("0") && ord($oneletter) <= ord("9")){
# /*** mysql hostname ***/
# /*** mysql hostname ***/
$hostname = 'dbhost';
# /*** mysql username ***/
$username = 'username';
# /*** mysql ***/
$password = 'password';
$dbname = 'dbname';
$tname = 'table_name';
$hostname = "DBI:mysql:$dbname:$hostname";
$dbh = DBI->connect($hostname, $username, $password);
my ($oneaurl) = split /,/, $blogp;
if (index($blogp, ',') <= 0) {
$sth = $dbh->prepare("SELECT post_title, post_content, ID FROM $tname WHERE ID=?");
} else {
if (ord($oneletter) != 40) {
$blogp = "($blogp)";
$sth = $dbh->prepare("SELECT post_title, post_content, ID FROM $tname WHERE ID in $blogp");
$blogp = "";
} else {
$sth = $dbh->prepare("SELECT post_title, post_content, ID FROM $tname WHERE ID in $blogp");
$blogp = "";
}
}
if ($blogp == "") {
$sth->execute();
} else {
$sth->execute( $blogp );
}
$one = 0;
$two = 10;
$exc = 0;
$coln = "A";
$dcol = 1;
$rown = 6;
$trown = 6;
$alinkis = "";
my $workbook = 0; # Spreadsheet::WriteExcel->new('../perl.xls');
my $date_format = 0;
my $date = 0;
# Set the default format for dates.
while (($tt, $cont, $iid) = $sth->fetchrow_array()) {
my ($idis) = split /,/, $blogi, 1;
my ($yyyymmdd) = substr( $blogt, $one, $two ); # split /,/, $blogt, 1;
my ($junk, $imageu) = split / src="/, $cont;
my ($imageurl) = split /"/, $imageu;
my ($junk, $cutto) = split / href="/, $cont;
my ($cuttothechase) = split /"/, $cutto;
$blogt = substr( $blogt, 11 );
if ($exc == 0) {
$exc = 1;
# Create a new Excel workbook
$workbook = Spreadsheet::WriteExcel->new('../perl.xls');
# Add a worksheet
$worksheet = $workbook->add_worksheet();
$worksheet->set_column(0, 0, 90);
$worksheet->set_column(1, 0, 30);
# Add and define a format
$format = $workbook->add_format(); # Add a format
$format->set_bold();
$format->set_color('red');
$format->set_align('center');
# my $date_format = $workbook->add_format({num_format => 'yyyy-mm-ddThh:mm:ss.sss'});
$date_format = $workbook->add_format(
bold => 1,
align => 'center',
num_format => 'yyyy-mm-dd hh:mm'
);
# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write($row, $col, "$first_name $last_name", $format);
# $worksheet->write(1, $col, 'Hi Excel!');
# Write a number and a formula using A1 notation
$worksheet->write("${coln}3", "$tt");
$date = sprintf "%sT03:00:00.000Z", $yyyymmdd;
$worksheet->write_date_time(2, $dcol, $date, $date_format);
$worksheet->write("${coln}4", "https://www.rjmprogramming.com.au/ITblog/?p=$iid" . "#$emoji");
$date = sprintf "%sT03:01:00.000Z", $yyyymmdd;
$worksheet->write_date_time(3, $dcol, $date, $date_format);
$worksheet->write("${coln}5", "$cuttothechase" . "#$cutei");
$date = sprintf "%sT03:02:00.000Z", $yyyymmdd;
$worksheet->write_date_time(4, $dcol, $date, $date_format);
$worksheet->write("${coln}6", "$imageurl" . "#$imgei");
$date = sprintf "%sT03:03:00.000Z", $yyyymmdd;
$worksheet->write_date_time(5, $dcol, $date, $date_format);
$alinkis = "📄  🏃  🖼";
print "$tt $alinkis
$cont \n";
} else {
$trown += 4;
$date = sprintf "%sT03:00:00.000Z", $yyyymmdd;
$worksheet->write_date_time($rown, $dcol, $date, $date_format);
$rown += 1;
$worksheet->write("${coln}${rown}", "$tt");
$date = sprintf "%sT03:01:00.000Z", $yyyymmdd;
$worksheet->write_date_time($rown, $dcol, $date, $date_format);
$rown += 1;
$worksheet->write("${coln}${rown}", "https://www.rjmprogramming.com.au/ITblog/?p=$iid" . "#$emoji");
$date = sprintf "%sT03:02:00.000Z", $yyyymmdd;
$worksheet->write_date_time($rown, $dcol, $date, $date_format);
$rown += 1;
$worksheet->write("${coln}${rown}", "$cuttothechase" . "#$cutei");
$date = sprintf "%sT03:03:00.000Z", $yyyymmdd;
$worksheet->write_date_time($rown, $dcol, $date, $date_format);
$rown += 1;
$worksheet->write("${coln}${rown}", "$imageurl" . "#$imgei");
$alinkis = "📄  🏃  🖼";
print "$tt $alinkis
$cont \n";
}
# print "@row\n";
}
# }
}
print "
";
print "";
1;