API tools faq
paste
ne_ziggy

scraper.pl

ne_ziggy
Nov 3rd, 2011
184
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Perl 16.53 KB | None | 0 0
raw download clone embed print report
  1. # !/usr/bin/perl
  2. # Web scraper -> MySQL written by Ziggy
  3. # this was hacked together for my job. Excuse the mess and sparse documentation
  4.    
  5. print "ASMS->MySQL Scraper v0.8\n";
  6.  
  7. use strict;
  8. use warnings;
  9. use WWW::Mechanize;
  10. use Date::Manip;
  11. use JSON;
  12. use DBI;
  13. use Term::ReadKey;
  14.  
  15.  
  16. #GLOBALS
  17. my $WWW = WWW::Mechanize->new();
  18. my $JSN = new JSON;
  19. #defined in login loop
  20. my $SQL  = undef;
  21. my $HOST = "localhost";
  22. my $TABLE= "asms_tasks"; #table to write to in MySQL DB
  23. my $REFRESH = undef; #re-parse tasks already in MySQL DB
  24. my $QUIT = 0; #end script after execution - non-interactive
  25. my $USERNAME = undef;
  26. my $PASSWORD = undef;
  27.  
  28. #DECLARE REUSABLE GLOBALS
  29. my $SDATE = undef;
  30. my $EDATE = undef;
  31. my $URL = undef;
  32. my $DUMP = undef; #page dumps
  33. my $TEMP = undef; #JSON dumps
  34. my $COUNT = undef; #multipurpose loop counter
  35. my $STRSQL = undef; #reusable SQL string
  36.  
  37.  
  38. #parse ARGV... defines $SDATE, $EDATE, $USERNAME, $PASSWORD, $REFRESH, $QUIT
  39. $COUNT = 0;
  40. foreach(@ARGV) {
  41.     if($_ =~ m/\-d/) {
  42.         if ($ARGV[$COUNT + 1] =~ m/(\d.+\/\d.+\/\d.+)/) {
  43.             $SDATE = trim($1);
  44.         }
  45.     }
  46.     if($_ =~ m/\-e/) {
  47.         if(defined($ARGV[$COUNT + 1]) ) {
  48.             if($ARGV[$COUNT + 1] =~ m/(\d.+\/\d.+\/\d.+)/) {
  49.                 $EDATE = trim($1);
  50.             }
  51.             else {
  52.                 $EDATE = $SDATE;
  53.             }
  54.         }
  55.         else {
  56.             $EDATE = $SDATE;
  57.         }
  58.     }
  59.     if($_ =~ m/\-u/) {
  60.         $USERNAME = $ARGV[$COUNT + 1];
  61.     }
  62.     if($_ =~ m/\-p/) {
  63.         $PASSWORD = $ARGV[$COUNT + 1];
  64.     }
  65.     if($_ =~ m/\-r/) {
  66.         $REFRESH = 1;
  67.     }
  68.     if($_ =~ m/\-x/) {
  69.         $QUIT = 1;
  70.     }
  71.  
  72.     $COUNT += 1;
  73. }
  74. $COUNT = undef;
  75.  
  76.  
  77.  
  78.  
  79. #DEFINE MINOR SUBROUTINES
  80. #t2m(00:00)
  81. #"time to minutes"
  82. sub t2m {
  83.     my $t  = shift;
  84.     my $hh = 0;
  85.     my $mm = 0;
  86.     if($t =~ m*(\d\d):(\d\d)*) {
  87.         $hh = int($1) * 60;
  88.         $mm = int($2);
  89.     }
  90.     return ($hh + $mm);
  91. }
  92.  
  93. #m2t(#)
  94. #"minutes to time"
  95. sub m2t {
  96.     my $t = shift;
  97.     my $hh = floor($t/60);
  98.     my $mm = floor((($t/60)-$hh)*60);
  99.     $hh = sprintf("%02d", $hh);
  100.     $mm = sprintf("%02d", $mm);
  101.     return "$hh" .":" . "$mm";
  102. }
  103.  
  104. #trim(string)
  105. #strip string of trailing spaces, newlines
  106. #convert backslashes and double quotations
  107. #convert & to &
  108. sub trim {
  109.     my $str = shift;
  110.  
  111.     $str =~ s/\r|\n//g;
  112.     $str =~ s/\\/\//g;
  113.     $str =~ s/"/'/g;
  114.     $str =~ s/^\s+//;
  115.     $str =~ s/\s+$//;
  116.     $str =~ s/\Q&\E/\\&/;
  117.  
  118.     return $str;
  119. }
  120.  
  121.  
  122.  
  123. #LOGIN LOOP
  124. #connects to [URL omitted :3]
  125. #defines $USERNAME, $PASSWORD, $REGION, $SQL
  126. while(1) {
  127.     if (!defined($USERNAME)) {
  128.         print "Username: ";
  129.         $USERNAME = <STDIN>;
  130.         chomp($USERNAME);
  131.     }
  132.     if (!defined($PASSWORD)) {
  133.         print "Password: ";
  134.         $PASSWORD = <STDIN>;
  135.         chomp($PASSWORD);
  136.     }
  137.  
  138.     print "Connecting to [URL omitted] :3\n";
  139.         $WWW->get("[URL omitted]") or die "Cannot access internet...";
  140.  
  141.     $WWW->set_visible( $USERNAME, $PASSWORD );
  142.     $WWW->submit();
  143.     $DUMP = $WWW->response->decoded_content();
  144.    
  145.     print "$USERNAME\n";
  146.  
  147.     #the page I was connected to showed this pattern on incorrect login. It was a pretty simple splash page
  148.     if($DUMP =~ m/<ul class="error">/) {
  149.         print "Login Failed. Please try again.\n";
  150.         $USERNAME = undef;
  151.         $PASSWORD = undef;
  152.         next;
  153.     }
  154.    
  155.     #connect to SQL database - I had to do this for several different regions, and each required different logins with the same password
  156.     #I had a database set up for each region, hence the $REGION variable. obviously.
  157.     else {
  158.                if($USERNAME =~ //) { $REGION = "ftlauderdale"; }
  159.             elsif($USERNAME =~ //) { $REGION = "atlanta"; }
  160.             elsif($USERNAME =~ //) { $REGION = "longbeach"; }
  161.             elsif($USERNAME =~ //) { $REGION = "newyork"; }
  162.             elsif($USERNAME =~ //) { $REGION = "orlando"; }
  163.             elsif($USERNAME =~ //) { $REGION = "palmbeach"; }
  164.             elsif($USERNAME =~ //) { $REGION = "saltlake"; }
  165.             elsif($USERNAME =~ //) { $REGION = "seattle"; }
  166.             else {
  167.                 die "No SQL database for region!";
  168.             }
  169.         #this is where you define which SQL DB you are connecting to. I had mine located on the same computer/server, so $HOST is localhost
  170.         $SQL = DBI->connect("DBI:mysql:database=".$REGION.";host=".$HOST,"username","password", {'RaiseError' => 1});
  171.         last;
  172.     }
  173. }
  174.  
  175.  
  176.  
  177.  
  178.  
  179.  
  180.  
  181.  
  182. #MAIN LOOP
  183. while(1) {
  184.     if (!defined($SDATE)) {
  185.         print "\n\n#################################\n";
  186.         print "Enter start date to retreive: ";
  187.         $SDATE = <STDIN>;
  188.     }
  189.  
  190.    
  191.     if(!defined($EDATE)) {
  192.         print "Enter end date <press enter for same date>: ";
  193.         $EDATE = <STDIN>;
  194.  
  195.         if(!($EDATE =~ m/\d\d\/\d\d\/\d\d/)) {
  196.             $EDATE = $SDATE;
  197.             print $EDATE;
  198.         }
  199.     }
  200.  
  201.     unless ($SDATE =~ m*\d+/\d+/\d+*) {
  202.         if( $SDATE =~ /quit/i or $SDATE =~ /exit/i ) {
  203.             print "Exiting...\n";
  204.             $QUIT = 1;
  205.         }
  206.         else {
  207.             print "Please enter a date DD/MM/YY\n";
  208.         }
  209.     }
  210.  
  211.     else {
  212.         #lol... here is pretty much the main loop of the program. literally this loop
  213.         my %IDS = RetrieveID($SDATE,$EDATE);
  214.         SaveID(%IDS);
  215.     }
  216.  
  217.     $SDATE = undef;
  218.     $EDATE = undef;
  219.  
  220.     if ($QUIT) {
  221.         print "Exiting";
  222.         last;
  223.     }
  224. }
  225.  
  226.  
  227.  
  228.  
  229.  
  230.  
  231.  
  232.  
  233.  
  234.  
  235.  
  236.  
  237.  
  238.  
  239.  
  240.  
  241.  
  242. #DECLARE/DEFINE MAJOR SUBROUTINES
  243. #parses JSON for list of valid IDs
  244. #takes start and end dates
  245. #returns hash{ID}:"STATUS"
  246. sub RetrieveID {
  247.     # ParseRecur is a neat little function that gets the date for each day within a date range :)
  248.     # this made my life so much easier!
  249.     my @fdate_range = ParseRecur('0:0:0:1:0:0:0', $_[0], $_[0], $_[1]); # format "2000-01-01 : 2000-01-01"
  250.     print UnixDate($_[0], "%Y-%m-%d") . " : " . UnixDate($_[1], "%Y-%m-%d") . " - $REGION\n";
  251.  
  252.     my $STRSQL  = "";
  253.     my @ROWSQL  = ();
  254.     my %TIDS    = (); # this is what we are returning
  255.  
  256.  
  257.     # if refresh is not defined, we check to see which IDs are not already defined in the SQL database
  258.     # if they aren't defined, we add them to the @ROWSQL array
  259.     # if refresh is defined, we parse ALL THE THINGS :D
  260.     if(!defined($REFRESH)) {
  261.         $STRSQL = $SQL->prepare("SELECT id FROM " . $TABLE . " WHERE date BETWEEN '" . UnixDate($_[0], "%Y-%m-%d") . " 00:00:00' AND '" . UnixDate($_[1], "%Y-%m-%d") . " 23:59:59'");
  262.         $STRSQL->execute();
  263.         while(my @ref = $STRSQL->fetchrow_array()) {
  264.             push(@ROWSQL, $ref[0]);
  265.         }
  266.         print "\nROWSQL: " . scalar(@ROWSQL) . "\n";
  267.     }
  268.  
  269.  
  270.    
  271.     for(@fdate_range) {
  272.         # for each day in @fdate_range...
  273.         # this pretty much depends on the page
  274.         $DUMP = $WWW->post('[URL OMITTED :3]', {
  275.                         'date'=>UnixDate($_, "%m/%d/%y"),
  276.                         'page'=>'1',
  277.                         'rp'=>'1000',
  278.                         'sortname'=>'ArrivalTime',
  279.                         'sortorder'=>'desc',
  280.                         'query'=>'',
  281.                         'type'=>'ID'});
  282.         $DUMP = $JSN->decode( $$DUMP{_content} );
  283.         $TEMP = $$DUMP{rows};
  284.  
  285.  
  286.         $COUNT = 0;
  287.         foreach(@$TEMP) {
  288.             # $$_{'id'}, $$_{'cell'}[0]
  289.             # $HID{'id'}, $HID{'cell'}[0]
  290.             my %HID = %$_;
  291.            
  292.             # if true, then re-add the ID anyways. we don't have to check if we have it already
  293.             if(defined($REFRESH)) {
  294.                 $COUNT += 1;
  295.                 $TIDS{$HID{'id'}} = $HID{'cell'}[0];
  296.                 next;
  297.             }
  298.             else {
  299.                 # if we already have some data for this day...
  300.                 if(scalar(@ROWSQL) > 0) {
  301.                     my $ADD = 1;
  302.                     foreach(@ROWSQL) {
  303.                         if ($HID{'id'} == $_) {
  304.                             print " Skipping...\n";
  305.                             $ADD = 0;
  306.                             last;
  307.                         }
  308.                     }
  309.  
  310.                     if($ADD == 1) {
  311.                         $COUNT += 1;
  312.                         $TIDS{$HID{'id'}} = $HID{'cell'}[0];
  313.                     }
  314.                 }
  315.                
  316.                 else {
  317.                     $COUNT += 1;
  318.                     $TIDS{$HID{'id'}} = $HID{'cell'}[0];
  319.                 }
  320.             }
  321.         }
  322.        
  323.         # "01/01/01 : 100"
  324.         print UnixDate($_,"%m/%d/%y") . " : " . $COUNT . "/" . scalar(@$TEMP) . "\n";
  325.         $COUNT = undef;
  326.     }
  327.  
  328.     $STRSQL = undef;
  329.     print "        :: " . scalar(keys %TIDS);
  330.     return %TIDS;
  331. }
  332.  
  333.  
  334.  
  335. sub SaveID {
  336.     # now that we have the array with IDs, we are ready to begin parsing
  337.     # we have to visit each page, one-by-one... :/
  338.     my %CALL = @_;
  339.    
  340.     # not really needed... I used this earlier as a return for saving as an html doc...
  341.     # may have this again in the future, but unlikely
  342.     # the hashes are saved in this array
  343.     my @ID   = undef;
  344.  
  345.     # shows progress... will calculate time in the future, providing estimate eta
  346.     # hahahaha... as if I was motivated enough to do that
  347.     my $COUNT = 0;
  348.     my $TCOUNT = scalar(keys %CALL);
  349.  
  350.  
  351.     print "\n\nRetrieving...\n";
  352.     while ( (my $ID, my $STATUS) = each %CALL) {
  353.         # system $^O eq 'MSWin32' ? 'cls' : 'clear';
  354.         # I had this function earlier because output was getting too messy...
  355.         # I just said fuck it later XD
  356.         $COUNT += 1;
  357.         print "$COUNT / $TCOUNT";
  358.         my %ID = ();
  359.         push(@ID, %ID);
  360.  
  361.         $URL = '[URL omitted :3]' . "$ID";
  362.         # connection dropped occasionally. before, the whole program would abort
  363.         # because $WWW->get would call 'die' if it timed out
  364.         # this skipped the entire ID from being saved, so I could re-add it later
  365.         # without having a refresh
  366.         my $connect = eval { $WWW->get($URL); };
  367.         if(!$connect) { next; }
  368.         $DUMP = $WWW->content();
  369.  
  370.         # print "$ID\n";
  371.         $ID[$ID]{"ID"} = $ID;
  372.        
  373.         # prevent uninits...
  374.         $ID[$ID]{"DATE"} = "";
  375.         $ID[$ID]{"AGENT"} = "";
  376.         $ID[$ID]{"ATTENDANT"} = "";
  377.         $ID[$ID]{"AIRLINE"} = "";
  378.         $ID[$ID]{"TYPE"} = "";
  379.         $ID[$ID]{"DIRECTION"} = "";
  380.         $ID[$ID]{"PUPLOC"} = "";
  381.         $ID[$ID]{"SIDELOC"} = "";
  382.         $ID[$ID]{"PAX"} = "";
  383.  
  384.         $ID[$ID]{"ARVFLT"} = "";
  385.         $ID[$ID]{"ARVGATE"} = "";
  386.         $ID[$ID]{"ARVTIME"} = "00:00";
  387.         $ID[$ID]{"DEPFLT"} = "";
  388.         $ID[$ID]{"DEPGATE"} = "";
  389.         $ID[$ID]{"DEPTIME"} = "00:00";
  390.         $ID[$ID]{"PUPTIME"} = "00:00";
  391.  
  392.         $ID[$ID]{"ARVTS"} = 0;
  393.         $ID[$ID]{"RESPTS"} = 0;
  394.         $ID[$ID]{"REQTS"} = 0;
  395.         $ID[$ID]{"TRANSTS"} = 0;
  396.         $ID[$ID]{"CLAIMTS"} = 0;
  397.         $ID[$ID]{"NOWCALLTS"} = 0;
  398.         $ID[$ID]{"CHARGETS"} = 0;
  399.  
  400.         $ID[$ID]{"REMARKS"} = "";
  401.         $ID[$ID]{"STATUS"} = $STATUS;
  402.         $ID[$ID]{"RECIEVEDBY"} = "";
  403.         $ID[$ID]{"PHONE"} = "";
  404.         $ID[$ID]{"PNR"} = "";
  405.        
  406.  
  407.         # and let the parsing begin :D regular expressions abound
  408.         if($DUMP =~ m*Date/Time.+?</label>\s+</td>\s+<td>\s+(\d\d)\/(\d\d)\/(\d\d\d\d)\s+(.+)\s+</td>* )    { $ID[$ID]{"DATE"} = trim("$3-$1-$2 $4"); }
  409.         if($DUMP =~ m*Attendant:</label></b>\s+</td>\s+<td>\s+(.+)\s+(.+)\s+</td>* ){ $ID[$ID]{"ATTENDANT"} = trim($1); }
  410.         if($DUMP =~ m*Requested By:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )        { $ID[$ID]{"AIRLINE"} = trim($1); }
  411.         if($DUMP =~ m*Agent:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )               { $ID[$ID]{"AGENT"} = trim($1); }
  412.         if($DUMP =~ m*Passenger:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )           { $ID[$ID]{"PAX"} = trim($1); }
  413.         $ID[$ID]{"PHONE"} = "";
  414.         if($DUMP =~ m*Request\s+Type:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )      { $ID[$ID]{"TYPE"} = trim($1); }
  415.         $ID[$ID]{"PNR"} = "";
  416.         $ID[$ID]{"RECIEVEDBY"} = "";
  417.         if($DUMP =~ m*Direction:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )           { $ID[$ID]{"DIRECTION"} = trim($1); }
  418.         if($DUMP =~ m*Pickup\s+Location:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )   { $ID[$ID]{"PUPLOC"} = trim($1); }
  419.         if($DUMP =~ m*Response\s+TS:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )       { $ID[$ID]{"RESPTS"} = trim($1); }
  420.         if($DUMP =~ m*Transit\s+TS:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )        { $ID[$ID]{"TRANSTS"} = trim($1); }
  421.         if($DUMP =~ m*Resp\s+TS:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )           { $ID[$ID]{"NOWCALLTS"} = trim($1); }
  422.         if($DUMP =~ m*Pickup\s+Date:</label>\s+</td>\s+<td>\s+(.+)\s+(.+):(.+)\s+</td>* )                                   { $ID[$ID]{"PUPTIME"} = trim("$2:$3"); }
  423.         if($DUMP =~ m#Remarks:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+</label>\s+</td>\s+<.+?>\s+(.*?)\s+</td># )   { $ID[$ID]{"REMARKS"} = trim($1); }
  424.         if($DUMP =~ m*Arrival\s+TS:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )            { $ID[$ID]{"ARVTS"} = trim($1); }
  425.         if($DUMP =~ m*Request\s+TS:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )            { $ID[$ID]{"REQTS"} = trim($1); }
  426.         if($DUMP =~ m*Claim\s+TS:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )              { $ID[$ID]{"CLAIMTS"} = trim($1); }
  427.         if($DUMP =~ m*Discharge\s+TS:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;</label>\s+</td>\s+<td>\s+(.+)\s+</td>* )          { $ID[$ID]{"CHARGETS"} = trim($1); }
  428.  
  429.         if(trim($ID[$ID]{"ATTENDANT"}) eq "") {
  430.             if($ID[$ID]{"STATUS"} =~ m/Completed\sby\s(.+)$/) {
  431.                 $ID[$ID]{"ATTENDANT"} = trim($1);
  432.             }
  433.         }
  434.        
  435.         # the comments would contain things like the agent's name if it wasn't attached to the call...
  436.         # most of the time they were spelled wrong, or they had nicknames. I had a function
  437.         # that took care of all that with more regex, but removed it for privacy reasons, ya know.
  438.         if($ID[$ID]{'REMARKS'} =~ m/A2[\s,\/,\.,\\,\-](\w+)\s?/ or
  439.                 $ID[$ID]{'REMARKS'} =~ m/completed\sby\s(\w+)\s?/i or
  440.                 $ID[$ID]{'REMARKS'} =~ m/assigned\sto\s(\w+)\s?/i)  {
  441.                 $ID[$ID]{"ATTENDANT"} = a2Name(trim($1));
  442.         }
  443.  
  444.         if($DUMP =~ m*Arriving:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+</label>\s+</td>\s+<td>\s+<label.+?>\s+Flt:</label>\s+</td>\s+<td>\s+(.+)\s+</td>\s+<td>\s+<label.+?>\s+Gate:</label>\s+</td>\s+<td>\s+(.+)\s+</td>\s+<td>\s+<label.+?>\s+Skd\s+Arr:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* ) {
  445.             $ID[$ID]{"ARVFLT"} = trim($1);
  446.             $ID[$ID]{"ARVGATE"} = trim($2);
  447.             if( trim($3) eq "") { $ID[$ID]{"ARVTIME"} = "00:00"; }
  448.             else {
  449.                 $ID[$ID]{"ARVTIME"} = trim($3);
  450.             }
  451.         }
  452.         if($DUMP =~ m*Departing:\s+&nbsp;\s+&nbsp;\s+&nbsp;\s+&nbsp;</label>\s+</td>\s+<td>\s+<label.+?>\s+Flt:</label>\s+</td>\s+<td>\s+(.+)\s+</td>\s+<td>\s+<label.+?>\s+Gate:</label>\s+</td>\s+<td>\s+(.+)\s+</td>\s+<td>\s+<label.+?>\s+Skd\s+Dep:</label>\s+</td>\s+<td>\s+(.+)\s+</td>* ) {
  453.             $ID[$ID]{"DEPFLT"} = trim($1);
  454.             $ID[$ID]{"DEPGATE"} = trim($2);
  455.             if( trim($3) eq "") { $ID[$ID]{"DEPTIME"} = "00:00"; }
  456.             else {
  457.                 $ID[$ID]{"DEPTIME"} = trim($3);
  458.             }
  459.         }
  460.        
  461.            
  462.         # default empty integer fields to 0 if empty
  463.         # it makes SQL happy ;)
  464.         if( trim($ID[$ID]{"ARVTS"}) eq "" )     { $ID[$ID]{"ARVTS"} = 0; }
  465.         if( trim($ID[$ID]{"RESPTS"}) eq "" )    { $ID[$ID]{"RESPTS"} = 0; }
  466.         if( trim($ID[$ID]{"REQTS"}) eq "" )     { $ID[$ID]{"REQTS"} = 0; }
  467.         if( trim($ID[$ID]{"TRANSTS"}) eq "" )   { $ID[$ID]{"TRANSTS"} = 0; }
  468.         if( trim($ID[$ID]{"CLAIMTS"}) eq "" )   { $ID[$ID]{"CLAIMTS"} = 0; }
  469.         if( trim($ID[$ID]{"NOWCALLTS"}) eq "" ) { $ID[$ID]{"NOWCALLTS"} = 0; }
  470.         if( trim($ID[$ID]{"CHARGETS"}) eq "" )  { $ID[$ID]{"CHARGETS"} = 0; }
  471.  
  472.        
  473.         # *sigh* in addition to the HTMl output, there was an additional jQuery table
  474.         # This table fortunately used JSON data, so we actually have to post to the page
  475.         # again to retrieve it. :/
  476.         $TEMP = $WWW->post('[URL omitted :3]'. "$ID", {
  477.                     'page'=>'1',
  478.                     'rp'=>'40',
  479.                     'sortname'=>'Id',
  480.                     'sortorder'=>'asc',
  481.                     'query'=>'',
  482.                     'qtype'=>''});
  483.         $TEMP = $JSN->decode( $$TEMP{_content} );
  484.  
  485.         foreach($$TEMP{rows}) {
  486.             foreach(@$_) {
  487.                 if($$_{cell}[3] =~ /Accepted/) {
  488.                     $ID[$ID]{"ATTENDANT"} = trim($$_{cell}[2]);
  489.                     $ID[$ID]{"DATE"} = trim($$_{cell}[1]);
  490.                     $ID[$ID]{"DATE"} =~ s/^(\d\d)\/(\d\d)\s(.+)$/2011\-$1\-$2 $3:00/;
  491.                 }
  492.                
  493.                 if($$_{cell}[3] =~ /Restroom/) {
  494.                     $ID[$ID]{"SIDELOC"} = trim($$_{cell}[3]);
  495.                 }
  496.             }
  497.         }
  498.  
  499.  
  500.         # this was just output so I knew it was working ;)
  501.         print "\tID: $ID[$ID]{'ID'}\n";
  502.         print "\tDate: $ID[$ID]{'DATE'}\n";
  503.         print "\tComments: " . $ID[$ID]{'REMARKS'} . "\n";
  504.        
  505.        
  506.         # delete row first with matching id first, to avoid primary key conflict
  507.         # this function will be replaced with a check for update...
  508.         $STRSQL = "DELETE FROM " . $TABLE . " WHERE ID=" . $ID[$ID]{"ID"};
  509.         eval { $SQL->do($STRSQL) };
  510.        
  511.        
  512.         # now that we have all the information, now it is time to add it to the SQL
  513.         # database! :) This concatenates a particularly long query... nothing to it
  514.         $STRSQL = "INSERT INTO " . $TABLE . " VALUES (";
  515.         $STRSQL .= '"' . $ID[$ID]{"ID"} . '",';
  516.         $STRSQL .= '"' . $ID[$ID]{"DATE"} . '",';
  517.         $STRSQL .= '"' . $ID[$ID]{"AGENT"} . '",';
  518.         $STRSQL .= '"' . $ID[$ID]{"ATTENDANT"} . '",';
  519.         $STRSQL .= '"' . $ID[$ID]{"AIRLINE"} . '",';
  520.         $STRSQL .= '"' . $ID[$ID]{"TYPE"} . '",';
  521.         $STRSQL .= '"' . $ID[$ID]{"DIRECTION"} . '",';
  522.         $STRSQL .= '"' . $ID[$ID]{"PUPLOC"} . '",';
  523.         $STRSQL .= '"' . $ID[$ID]{"SIDELOC"} . '",';
  524.         $STRSQL .= '"' . $ID[$ID]{"PAX"} . '",';
  525.         $STRSQL .= '"' . $ID[$ID]{"ARVFLT"} . '",';
  526.         $STRSQL .= '"' . $ID[$ID]{"ARVGATE"} . '",';
  527.         $STRSQL .= '"' . $ID[$ID]{"ARVTIME"} . '",';
  528.         $STRSQL .= '"' . $ID[$ID]{"DEPFLT"} . '",';
  529.         $STRSQL .= '"' . $ID[$ID]{"DEPGATE"} . '",';
  530.         $STRSQL .= '"' . $ID[$ID]{"DEPTIME"} . '",';
  531.         $STRSQL .= '"' . $ID[$ID]{"PUPTIME"} . '",';
  532.        
  533.         $STRSQL .= '"' . $ID[$ID]{"ARVTS"} . '",';
  534.         $STRSQL .= '"' . $ID[$ID]{"RESPTS"} . '",';
  535.         $STRSQL .= '"' . $ID[$ID]{"REQTS"} . '",';
  536.         $STRSQL .= '"' . $ID[$ID]{"TRANSTS"} . '",';
  537.         $STRSQL .= '"' . $ID[$ID]{"CLAIMTS"} . '",';
  538.         $STRSQL .= '"' . $ID[$ID]{"NOWCALLTS"} . '",';
  539.         $STRSQL .= '"' . $ID[$ID]{"CHARGETS"} . '",';
  540.        
  541.         $STRSQL .= '"' . $ID[$ID]{"REMARKS"} . '",';
  542.         $STRSQL .= '"' . $ID[$ID]{"STATUS"} . '")';
  543.  
  544.  
  545.         open(SQLSTRING, ">asdf.txt");
  546.        
  547.         print SQLSTRING $STRSQL;
  548.         close(SQLSTRING);
  549.        
  550.         trim($STRSQL);
  551.         $SQL->do($STRSQL);
  552.     }
  553. }
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!