#!/usr/bin/perl -w

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Outlook';
use Date::Manip;
use DBI();

&Date_Init("TZ=CST6CDT");

my $argcount = 0;
my $arg;
my $do_db;
my $do_count = 0;
my $tc = 0;
my $uc = 0;
my $ic = 0;

while (@ARGV) {
	$arg = shift(@ARGV);
	if ($arg =~ /-todb/) {
		$do_db = 1;
	} elsif ($arg =~ /-count/) {
		$do_count = 1;
	}
}

# mysql connection variables
my $dbhost = '1.2.3.4';  # you will need to set this
my $dbuser = 'pass';  # you will need to set this
my $dbpass = 'pass';  #you will need to set this
my $dbdb = 'db';  #you will need to set this
my $dsn = "DBI:mysqlPP:database=$dbdb;host=$dbhost";
my $dbh;

if ($do_db) {
print "Connecting to DB ...\n";
print "dsn: $dsn\n";
$dbh = DBI->connect($dsn,$dbuser,$dbpass) or die "couldn't connect to $dbhost: " . DBI->errstr . "\n";
}

my @PhoneProperties = (
  "Account",
  "Anniversary",
  "AssistantName",
  "AssistantTelephoneNumber",
  "BillingInformation",
  "Birthday",
  "Body",
  "Business2TelephoneNumber",
  "BusinessAddress",
  "BusinessAddressCity",
  "BusinessAddressCountry",
  "BusinessAddressPostOfficeBox",
  "BusinessAddressPostalCode",
  "BusinessAddressState",
  "BusinessAddressStreet",
  "BusinessFaxNumber",
  "BusinessHomePage",
  "BusinessTelephoneNumber",
  "CallbackTelephoneNumber",
  "Categories",
  "CarTelephoneNumber",
  "Children",
  "Companies",
  "CompanyLastFirstNoSpace",
  "CompanyLastFirstSpaceOnly",
  "CompanyMainTelephoneNumber",
  "CompanyName",
  "ComputerNetworkName",
  "ConversationIndex",
  "ConversationTopic",
  "CreationTime",
  "CustomerID",
  "Department",
  "Email1Address",
  "Email1AddressType",
  "Email1DisplayName",
  "Email1EntryID",
  "Email2Address",
  "Email2AddressType",
  "Email2DisplayName",
  "Email2EntryID",
  "Email3Address",
  "Email3AddressType",
  "Email3DisplayName",
  "Email3EntryID",
  "EntryID",
  "FTPSite",
  "FileAs",
  "FirstName",
  "FullName",
  "FullNameAndCompany",
  "GovernmentIDNumber",
  "Hobby",
  "Home2TelephoneNumber",
  "HomeAddress",
  "HomeAddressCity",
  "HomeAddressCountry",
  "HomeAddressPostOfficeBox",
  "HomeAddressPostalCode",
  "HomeAddressState",
  "HomeAddressStreet",
  "HomeFaxNumber",
  "HomeTelephoneNumber",
  "IMAddress",
  "ISDNNumber",
  "Initials",
  "InternetFreeBusyAddress",
  "JobTitle",
  "LastFirstAndSuffix",
  "LastFirstNoSpace",
  "LastFirstNoSpaceAndSuffix",
  "LastFirstNoSpaceCompany",
  "LastFirstSpaceOnly",
  "LastFirstSpaceOnlyCompany",
  "LastModificationTime",
  "LastName",
  "LastNameAndFirstName",
  "MailingAddress",
  "MailingAddressCity",
  "MailingAddressCountry",
  "MailingAddressPostOfficeBox",
  "MailingAddressPostalCode",
  "MailingAddressState",
  "MailingAddressStreet",
  "ManagerName",
  "MessageClass",
  "MiddleName",
  "Mileage",
  "MobileTelephoneNumber",
  "NetMeetingAlias",
  "NetMeetingServer",
  "NickName",
  "OfficeLocation",
  "OrganizationalIDNumber",
  "OtherAddress",
  "OtherAddressCity",
  "OtherAddressCountry",
  "OtherAddressPostOfficeBox",
  "OtherAddressPostalCode",
  "OtherAddressState",
  "OtherAddressStreet",
  "OtherFaxNumber",
  "OtherTelephoneNumber",
  "PagerNumber",
  "PersonalHomePage",
  "PrimaryTelephoneNumber",
  "Profession",
  "RadioTelephoneNumber",
  "ReferredBy",
  "Spouse",
  "Subject",
  "Suffix",
  "TTYTDDTelephoneNumber",
  "TelexNumber",
  "Title",
  "User1",
  "User2",
  "User3",
  "User4",
  "UserCertificate",
  "WebPage",
  "YomiCompanyName",
  "YomiFirstName",
  "YomiLastName"
);

print "Connecting to Outlook ...\n";
my $outlook;
$outlook = Win32::OLE->new('Outlook.Application');

my $namespace = $outlook->GetNamespace("MAPI");
my $Folder = $namespace->GetDefaultFolder(olFolderContacts);

print $Folder->Name,"\n";

my $items = $Folder->Items;
print $items->Count,"\n";

#my $activeexplorer;
#$activeexplorer = $outlook->ActiveExplorer;

#print $activeexplorer->Caption,"\n";

#my $items = $activeexplorer->CurrentFolder->Items;
#print $items->Count,"\n";

my $linenumber = 1;
print "CR;NR;PROPERTY;DATA\n";

my $Kontakt;
my $PhoneProp;
my $ItemIndex = 1;
my $contactno = 0;
my $v;
my %allprops;
my $db_mod_date;
my $ex_mod_date;
my $exdt;
my $dbdt;
my $flag;

#my $query_find_id = "select * from OutlookContact where EntryID = ?";
my $query_find_id = "select count(*) from OutlookContact where EntryID = ?";
my $query_get_mod_dt = "select LastModificationTime from OutlookContact where EntryID = ?";
my $query_ins = "insert into OutlookContact (EntryID) values (?)";
my $sth_fi;
my $sth_ins;
my $sth_gmd;

if ($do_db) {
	print "preparing statements...\n";
	$sth_fi = $dbh->prepare($query_find_id);
	$sth_ins = $dbh->prepare($query_ins);
	$sth_gmd = $dbh->prepare($query_get_mod_dt);

	if (!defined $sth_fi) {
		print "sth_fi undefined\n";
	}
	if (!defined $sth_ins) {
		print "sth_ins undefined\n";
	}
	if (!defined $sth_gmd) {
		print "sth_ins undefined\n";
	}
}

print "Loop through items ...\n";
while ($ItemIndex <= $items->Count) {
  $Kontakt = $items->item($ItemIndex);
  $contactno++;
  $tc++;

  my $entryID = $Kontakt->{"EntryID"};
  print "entryID: $entryID\n";
  my $ex_mod_date = $Kontakt->{"LastModificationTime"};
  my @junk = split(/\s+/, $ex_mod_date);
  $exdt = ParseDate($junk[0]);
  print "EX LastModificationTime: $ex_mod_date\n";

if ($do_db) {
  print "checking for existing Entry ID...\n";
  $sth_fi->execute($entryID);

  my @rowdata = $sth_fi->fetchrow_array();
  my $rc = $rowdata[0];

  print "EntryID rows found: ".$rc."\n";
  if ($rc == 0) {
    print "EntryID does not exist, inserting...\n";
    $sth_ins->execute($entryID);
    $ic++;
  } else {
    $sth_gmd->execute($entryID);
    my @rows = $sth_gmd->fetchrow_array();
    $db_mod_date = $rows[0];
    $dbdt = ParseDate($db_mod_date);
    print "DB LastModificationTime: $db_mod_date\n";
  }
}

$flag = Date_Cmp($dbdt, $exdt);
print "Date_Cmp flag: $flag\n";
if ($flag < 0) {
  foreach $PhoneProp (@PhoneProperties) {
    my $prop = $Kontakt->{"$PhoneProp"};
    if ($prop) {
      # save the property to get a complete list of used properties
      if ($PhoneProp =~ /Time/) {
        $v = Variant(VT_DATE, $prop);
	#$prop = $v->Date("yyyy-mm-dd") . " " . $v-Time(TIME_FORCE24HOURFORMAT|TIME_NOTIMEMARKER);
        $prop = $v->Date("yyyy-MM-dd");
      }
      if ($allprops{$PhoneProp}) { 
        if ($allprops{$PhoneProp} < length($prop)) {
          $allprops{$PhoneProp} = length($prop);
	}
      } else {
        $allprops{$PhoneProp} = length($prop);
      }

if ($do_db) {  
	my $insprop = $dbh->quote($prop);
      my $query_upd = "update OutlookContact set $PhoneProp = $insprop where EntryID = '$entryID'";
      print "Query: $query_upd\n";
      $dbh->do($query_upd);
      $uc++;
}
      
      print $contactno, ";", $linenumber++, ";", $PhoneProp, ";", $prop, "\n";
      #print "$number=", substr( $Kontakt->FullName, 1, 15 ),"\n";
    }
  }

  }
 $ItemIndex++;
}

my $key;

print "Properties seen:\n";
foreach $key (keys %allprops) {
	if ($key =~ /Time/) {
		print $key." date,\n";
	} else {
		print $key." varchar(".$allprops{$key}."),\n";
	}
}

print "Total records: $tc \n";
print "Updated records: $uc \n";
print "Inserted records: $ic \n";