MLS scan & txt

#!/usr/bin/perl

#In a nutshell: grab the MLS search results page
#extract listings with RegEx (test it with https://regex101.com/)
#check results against a local SQLite database for changes or additions
#send out updates via a gmail account and smtp

#run this script with a cron job

use strict;
use warnings;
use DBI;
use Email::Sender::Simple qw(sendmail); 
use Email::Sender::Transport::SMTPS; 
use Email::Simple();   
use Email::Simple::Creator();  

my $driver   = "SQLite"; 
my $database = "/root/homesearch/house.db";
my $dsn = "DBI:$driver:$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

my $stmt = qq(CREATE TABLE IF NOT EXISTS HOMES
   (ID INT PRIMARY KEY     NOT NULL,
	  PRICE TEXT,
      STATUS TEXT,
      ADDRESS1 TEXT,
      ADDRESS2 TEXT,
      BEDS INT,
	  BATHS INT,
	  SQFEET INT,
	  BUILT INT, 
	  ACRES INT,
	  DESCRIPTION TEXT
	  ););

my $rv = $dbh->do($stmt);
if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}


my $document=`curl https://matrix.abor.com/MLS_SEARCH_URL`;

#This is a regex string created by looking at the HTML results page
my @matches = $document =~ /<span class="d-fontSize--largest">(\$[0-9,,]{1,10}).*<\/div>[\r\n]+<div class="col-xs-3 d-textAlign--right col-sm-4 col-md-3">[\r\n]+<span class="formula J_formula"><div class='dropdown mtx-dropdownModal mtx-bucketSelector j-portalBucketSelector' data-key='([0-9]{1,10})' data-currentbucket='[0-9]' ><a href='#' title='.*'  onclick='Dpy\.changeDropDownPosition\( this \);' class='mtx-btn-link mtx-icon mtx-icon-bucket.* j-portalBucketSelectorIcon' data-toggle='dropdown' style='display:inline-block;'><\/a><ul class='dropdown-menu is-bucket.* mtx-bucketSelector-menu j-portalBucketSelector-menu' ><li class='mtx-bucket--favoriteRemove'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}",".*",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketFavoriteRemove'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Remove from Favorites<\/span><\/a><\/li><li class='mtx-bucket--possibilitiesRemove'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}","4",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketPossibilitiesRemove'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Remove from Possibilities<\/span><\/a><\/li><li class='mtx-bucket--discardsRemove'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}","2",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketDiscardsRemove'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Remove from Discards<\/span><\/a><\/li><li class='mtx-bucket--favorite'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}","6",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketFavorite'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Save as Favorite<\/span><\/a><\/li><li class='mtx-bucket--possibilities'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}","4",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketPossibilities'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Save as Possibility<\/span><\/a><\/li><li class='mtx-bucket--discards'><a href='#' onclick='Dpy\.clickPortalBucketResponsive\("[0-9]{1,10}","2",event\);'><span class='mtx-btn-link mtx-icon mtx-icon--small mtx-icon-bucketDiscards'><\/span><span class='mtx-textSoft' style='vertical-align:middle;'>Discard Listing<\/span><\/a><\/li><\/ul><\/div><\/span>[\r\n]+<\/div>[\r\n]+<div class=" col-xs-9 d-fontSize--small col-sm-8 col-md-8 col-lg-8">[\r\n]+<div id="wrapperTable" class="d-wrapperTable"><span class="formula J_formula"><span class='.*'>(.*)<\/span><\/span><\/div><\/div>[\r\n]+<\/div>[\r\n]+<div class="row">[\r\n]+<div class=" col-sm-12 d-fontSize--largest d-text">[\r\n]+<div id="wrapperTable".*__doPostBack.*">(.*)<\/a>.*<\/div><\/div>[\r\n]+<div class=" col-sm-12 d-fontSize--small d-textSoft">[\r\n]+<div id="wrapperTable" class="d-wrapperTable"><span class="formula J_formula">(.*)<\/span><\/div><\/div>[\r\n]+<div class=" col-sm-12">[\r\n]+<div id="wrapperTable" class="d-wrapperTable"><div class="row"><div class="col-sm-12 d-marginLeft--10"><span class="d-textStrong d-paddingRight--4">([0-9])<\/span><span class="d-text d-paddingRight--4">Beds,<\/span><span class="d-textStrong d-paddingRight--3">([0-9,.]{1,5})<\/span><span class="d-text d-paddingRight--2"> Total Baths, <\/span><span class="d-textStrong">([0-9,,]{1,8})<\/span><span class="d-text">  SqFt,  <\/span><span class="d-text">Built in<\/span><span class="d-textStrong[\r\n]+d-fieldsSeparatorComma d-paddingLeft--4">([0-9]{4})<\/span><span class="d-paddingLeft--4 d-textStrong">([0-9,.]{1,10})<\/span><span class="d-text">  Acres  <\/span><\/div><\/div><\/div><\/div>[\r\n]+<div class="col-sm-12 hidden-sm d-paddingTop--4 d-paddingBottom--4 hidden-md hidden-xs">[\r\n]+<span class="d-textSoft">(.*)<\/span><\/div>[\r\n]+<div class=" col-sm-12">/g;

print ((scalar @matches)/11);
print " homes found\n";

#This grabs the fields from the regex string
for (my $i = 0; $i < (scalar @matches)/12; $i++) {
   print "Price: $matches[$i*11+0]\n";
   print "ID: $matches[$i*11+1]\n";
   print "Status: $matches[$i*11+2]\n";
   print "Address 1: $matches[$i*11+3]\n";
   print "Address 2: $matches[$i*11+4]\n";
   print "Bed: $matches[$i*11+5]\n";
   print "Bath: $matches[$i*11+6]\n";
   print "Sq. Feet: $matches[$i*11+7]\n";
   print "Built: $matches[$i*11+8]\n";
   print "Acres: $matches[$i*11+9]\n";
   print "Description: $matches[$i*11+10]\n";
   my $descrip = quotemeta $matches[$i*11+10];
   
   $stmt = qq(SELECT EXISTS( SELECT 1 from HOMES WHERE ID=$matches[$i*11+1]););
	my $sth = $dbh->prepare( $stmt );
	my $rv = $sth->execute() or die $DBI::errstr;

	if($sth->fetch()->[0]) {
	   $stmt = qq(SELECT STATUS from HOMES WHERE ID=$matches[$i*11+1];);
		my $sth = $dbh->prepare( $stmt );
		my $rv = $sth->execute() or die $DBI::errstr;
		my @row = $sth->fetchrow_array();
		if($row[0] ne $matches[$i*11+2]){
			my $stmt = qq(UPDATE HOMES set STATUS="$matches[$i*11+2]" where ID=$matches[$i*11+1];);
			my $rv = $dbh->do($stmt) or die $DBI::errstr;

			#gmail account that allows insecure apps smtp access
			my $smtpserver = 'smtp.gmail.com';
			my $smtpport = 587;      
			my $smtpuser = 'sharedEmail@gmail.com';   
			my $smtppassword = 'sharedPassword';         
			my $transport = Email::Sender::Transport::SMTPS->new({   
				host => $smtpserver,  
				port => $smtpport,            
				sasl_username => $smtpuser,       
				sasl_password => $smtppassword,   
				ssl  => "starttls",
				debug => 0
			});            

			#Fill in addresses with your cell providers texting address @vtext.com is Verizon
			my $email = Email::Simple->create(header => [
				To      => '7141234567@vtext.com',
				CC		=> '714123456@vtext.com',
				From    => 'sharedEmail@gmail.com',
				Subject => "$matches[$i*11+2] Wells Branch",
				],  body => "STATUS CHANGE - $matches[$i*11+0] - $matches[$i*11+3] - $matches[$i*11+7] Sq. Ft - $matches[$i*11+10]"
			);             

			sendmail($email, { transport => $transport });
		}else{
			print "STATUS UNCHANGED\n";
		}
	}else{
	   $stmt = qq(INSERT INTO HOMES (ID,PRICE,STATUS,ADDRESS1,ADDRESS2,BEDS,BATHS,SQFEET,BUILT,ACRES,DESCRIPTION)
				   VALUES (
				   $matches[$i*11+1],
				   "$matches[$i*11+0]",
				   "$matches[$i*11+2]",
				   "$matches[$i*11+3]",
				   "$matches[$i*11+4]",
				   $matches[$i*11+5],
				   $matches[$i*11+6],
				   "$matches[$i*11+7]",
				   $matches[$i*11+8],
				   $matches[$i*11+9],
				   "$descrip"
				   ));
				   
		print "New Home!\n";
		
		my $smtpserver = 'smtp.gmail.com';
		my $smtpport = 587;      
		my $smtpuser = 'sharedEmail@gmail.com';   
		my $smtppassword = 'emailPassowrd';         
		my $transport = Email::Sender::Transport::SMTPS->new({   
			host => $smtpserver,  
			port => $smtpport,            
			sasl_username => $smtpuser,       
			sasl_password => $smtppassword,   
			ssl  => "starttls",
			debug => 0
		});            

		my $email = Email::Simple->create(header => [
				To      => '7141234567@vtext.com',
				From    => 'sharedEmail@gmail.com',
				CC		=> '7142362584@vtext.com',
				Subject => "$matches[$i*11+2] Wells Branch",
			],  body => "$matches[$i*11+0] - $matches[$i*11+3] - $matches[$i*11+7] Sq. Ft - $matches[$i*11+10]"
		);             

		sendmail($email, { transport => $transport });

		$rv = $dbh->do($stmt) or die $DBI::errstr;
   }
}

$dbh->disconnect();