#!/usr/bin/perl
#
# VICIDIAL_IN_new_leads_file.pl version 2.12
#
# DESCRIPTION:
# script lets you insert leads into the vicidial_list table from a TAB-delimited
# lead file that is in the proper format. (for format see --help)
#
# It is recommended that you run this program on the local Asterisk machine
#
# NOTE: the machine this is run on must have a servers entry in the database
#
# Copyright (C) 2022  Matt Florell <vicidial@gmail.com>    LICENSE: AGPLv2
#
#
# CHANGES
# 60615-1543 - Added gmt_offset_now lookup for each lead
#            - Added option to force a gmt value(field after COMMENTS field)
# 60616-0958 - Added listID override feature to force all leads into same list
# 60807-1003 - Changed to DBI
#            - Changed to use /etc/astguiclient.conf for configs
# 60906-1055 - Added filter of non-digits in alt_phone field
# 60913-1236 - Fixed MySQL bugs and non-debug bug
#            - Added duplicate check flag option within same list
# 61127-1132 - Added new DST methods
# 61128-1037 - Added postal codes GMT lookup option
# 70510-1518 - Added campaign and system duplicate check and phonecode override
# 70801-0912 - Added called count and status to import leads format (fields 24 and 25)
# 70815-2128 - Added entry_date to import leads format (field 26)
# 80128-0105 - Fixed bugs in file loading
# 80428-0320 - UTF8 update
# 80713-0023 - Added last_local_call_time field default of 2008-01-01
# 80730-1607 - Added minicsv format
# 80812-1204 - Added FTP grab options and summary email options
# 80829-2301 - Added multi-alt-phone entry insertion capability
# 90324-1038 - Added minicsv02 format
# 90401-1340 - Fixed quiet flag functionality
# 90721-1315 - Added rank and owner as vicidial_list fields, stdrankowner format
# 90728-1255 - Added fixed254 file format
# 90802-0758 - Added sctab08 file format
# 90810-0956 - Added sccsv11 file format
# 90830-0953 - Added forcelistfilename option
# 91112-0645 - Added title/alt_phone duplicate checks
# 91129-2202 - removed SELECT STAR and formatting fixes
# 100118-0527 - Added new Australian and New Zealand DST schemes (FSO-FSA and LSS-FSA)
# 100204-2333 - Added dccsv10 file format
# 100221-0939 - Added dccsv43 file format with custom cid lookup
# 100427-0434 - Added ability to create new list in system for each new loaded file
# 100610-0756 - Added dccsv52 file format
# 100624-2143 - Added dccsvref52 file format
# 100928-1121 - Added file-prefix-filter option
# 110420-0944 - Fixed file prefix issue with multiple processes running
# 110424-0948 - Added time-zone-code-gmt option to use time zone code from the owner field
# 110705-1913 - Added options for USACAN prefix(no 0 or 1) and valid areacode filtering
# 110929-1423 - Added new format for abbreviated timezone in owner(pipe30tz) and list creation options
# 120713-1009 - Added new --duplicate-tnm-delete option to check for a duplicate phone with different title and delete existing if found in same list
# 120907-1109 - Added vote17csv format
# 121005-0728 - Added twotab format
# 130419-2138 - Added --NANPA-ac-prefix-check and --nanpa-gmt options using add-on NANPA prefix database
# 150312-1458 - Allow single quotes in standard formats
# 151104-0938 - Added t2r13csv format
# 160602-1411 - Added t2r25csv format
# 160628-1435 - Added t2r25xcsv format
# 160729-0811 - Added t2r27csv format
# 160809-1113 - Added t2r26csv format
# 161010-1616 - Added t2r15csv format
# 220211-0924 - Added vicidl format
#

$version = '220211-0924';

$secX = time();
$MT[0]='';
$Ealert='';
$force_quiet=0;
$forcelistfilename=0;

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year = ($year + 1900);
$mon++;
if ($hour < 10) {$hour = "0$hour";}
if ($min < 10) {$min = "0$min";}
if ($sec < 10) {$sec = "0$sec";}
if ($mon < 10) {$mon = "0$mon";}
if ($mday < 10) {$mday = "0$mday";}
$pulldate0 = "$year-$mon-$mday $hour:$min:$sec";
$insert_date = "$year-$mon-$mday $hour:$min:$sec";
$listdate = "$year$mon$mday";
$inSD = $pulldate0;
$dsec = ( ( ($hour * 3600) + ($min * 60) ) + $sec );
$MT[0]='';


# default path to astguiclient configuration file:
$PATHconf =		'/etc/astguiclient.conf';

open(conf, "$PATHconf") || die "can't open $PATHconf: $!\n";
@conf = <conf>;
close(conf);
$i=0;
foreach(@conf)
	{
	$line = $conf[$i];
	$line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
	if ( ($line =~ /^PATHhome/) && ($CLIhome < 1) )
		{$PATHhome = $line;   $PATHhome =~ s/.*=//gi;}
	if ( ($line =~ /^PATHlogs/) && ($CLIlogs < 1) )
		{$PATHlogs = $line;   $PATHlogs =~ s/.*=//gi;}
	if ( ($line =~ /^PATHagi/) && ($CLIagi < 1) )
		{$PATHagi = $line;   $PATHagi =~ s/.*=//gi;}
	if ( ($line =~ /^PATHweb/) && ($CLIweb < 1) )
		{$PATHweb = $line;   $PATHweb =~ s/.*=//gi;}
	if ( ($line =~ /^PATHsounds/) && ($CLIsounds < 1) )
		{$PATHsounds = $line;   $PATHsounds =~ s/.*=//gi;}
	if ( ($line =~ /^PATHmonitor/) && ($CLImonitor < 1) )
		{$PATHmonitor = $line;   $PATHmonitor =~ s/.*=//gi;}
	if ( ($line =~ /^VARserver_ip/) && ($CLIserver_ip < 1) )
		{$VARserver_ip = $line;   $VARserver_ip =~ s/.*=//gi;}
	if ( ($line =~ /^VARDB_server/) && ($CLIDB_server < 1) )
		{$VARDB_server = $line;   $VARDB_server =~ s/.*=//gi;}
	if ( ($line =~ /^VARDB_database/) && ($CLIDB_database < 1) )
		{$VARDB_database = $line;   $VARDB_database =~ s/.*=//gi;}
	if ( ($line =~ /^VARDB_user/) && ($CLIDB_user < 1) )
		{$VARDB_user = $line;   $VARDB_user =~ s/.*=//gi;}
	if ( ($line =~ /^VARDB_pass/) && ($CLIDB_pass < 1) )
		{$VARDB_pass = $line;   $VARDB_pass =~ s/.*=//gi;}
	if ( ($line =~ /^VARDB_port/) && ($CLIDB_port < 1) )
		{$VARDB_port = $line;   $VARDB_port =~ s/.*=//gi;}
	if ( ($line =~ /^VARREPORT_host/) && ($CLIREPORT_host < 1) )
		{$VARREPORT_host = $line;   $VARREPORT_host =~ s/.*=//gi;}
	if ( ($line =~ /^VARREPORT_user/) && ($CLIREPORT_user < 1) )
		{$VARREPORT_user = $line;   $VARREPORT_user =~ s/.*=//gi;}
	if ( ($line =~ /^VARREPORT_pass/) && ($CLIREPORT_pass < 1) )
		{$VARREPORT_pass = $line;   $VARREPORT_pass =~ s/.*=//gi;}
	if ( ($line =~ /^VARREPORT_port/) && ($CLIREPORT_port < 1) )
		{$VARREPORT_port = $line;   $VARREPORT_port =~ s/.*=//gi;}
	$i++;
	}

# Customized Variables
$server_ip = $VARserver_ip;		# Asterisk server IP


if (!$VDHLOGfile) {$VDHLOGfile = "$PATHlogs/newleads.$year-$mon-$mday";}

### begin parsing run-time options ###
if (length($ARGV[0])>1)
	{
	$i=0;
	while ($#ARGV >= $i)
		{
		$args = "$args $ARGV[$i]";
		$i++;
		}

	if ($args =~ /--version/i)
		{
		print "$version\n";
		exit;
		}
	if ($args =~ /--help|-h/i)
		{
		print "allowed run time options:\n";
		print "  [--quiet] = quiet\n";
		print "  [--test] = test\n";
		print "  [--version] = version\n";
		print "  [--forcegmt] = forces gmt value of column after comments column\n";
		print "  [--debug] = debug output\n";
		print "  [--format=standard] = ability to define a format, standard is default, formats allowed shown in examples\n";
		print "  [--forcelistid=1234] = overrides the listID given in the file with the 1234\n";
		print "  [--forcelistfilename] = overrides the listID using last number in filename: (XYZ_1234.txt = list ID 1234)\n";
		print "  [--forcephonecode=44] = overrides the phone_code given in the lead with the 44\n";
		print "  [--file-prefix-filter=WXYZ] = will only process lead files that begin with the characters you define\n";
		print "  [--new-list-for-each-file] = creates a new list for each file loaded, listID = YYYYMMDDX where X is incremented\n";
		print "  [--new-listid-prefix=X] = prefix for listID when creating new lists, must be only numbers, and 4 or less digits\n";
		print "  [--new-listname-prefix=X] = prefix for list name when creating new lists, will be followed by filename\n";
		print "  [--new-list-campaign=X] = campaign that the new list will be assigned to\n";
		print "  [--new-list-active=X] = Y or N, if list is to be set as active when created, default N\n";
		print "  [--new-list-reset-times=X] = reset times(4 digits each dash separated) default is blank\n";
		print "  [--new-list-tz-setting=X] = COUNTRY_AND_AREA_CODE|POSTAL_CODE|NANPA_PREFIX|OWNER_TIME_ZONE_CODE default COUNTRY_AND_AREA_CODE\n";
		print "  [--USACAN-prefix-check] = check for the 4th digit 2-9, USA and Canada validation\n";
		print "  [--USACAN-areacode-check] = check for the valid phone code 1 areacodes, USA and Canada validation\n";
		print "  [--NANPA-ac-prefix-check] = check for the valid areacode and prefix from the NANPA prefix database, if loaded\n";
		print "  [--duplicate-check] = checks for the same phone number in the same list id before inserting lead\n";
		print "  [--duplicate-campaign-check] = checks for the same phone number in the same campaign before inserting lead\n";
		print "  [--duplicate-system-check] = checks for the same phone number in the entire system before inserting lead\n";
		print "  [--duplicate-tap-list-check] = checks for the same title/alt-number in the same list ID before inserting lead\n";
		print "  [--duplicate-tap-system-check] = checks for the same title/alt-number in the entire system before inserting lead\n";
		print "  [--duplicate-tnm-delete] = checks for same phone and title in same list then deletes original if title is different\n";
		print "  [--dob-mmddyyy] = checks for slashes in the date of birth field and formats from MM/DD/YYYY to MySQL\n";
		print "  [--dob-ddmmyyy] = checks for slashes in the date of birth field and formats from DD/MM/YYYY to MySQL\n";
		print "  [--postal-code-gmt] = checks for the time zone based on the postal code given where available\n";
		print "  [--time-zone-code-gmt] = checks for the time zone based on the owner field time zone code given where available\n";
		print "  [--nanpa-gmt] = checks for the time zone based on the NANPA prefix database, if loaded\n";
		print "  [--ftp-pull] = grabs lead files from a remote FTP server, uses REPORTS FTP login information\n";
		print "  [--ftp-dir=leads_in] = remote FTP server directory to grab files from, should have a DONE sub-directory\n";
		print "  [--email-list=test@test.com:test2@test.com] = send email results for each file to these addresses\n";
		print "  [--email-sender=vicidial@localhost] = sender for the email results\n";
		print "  [-h] = this help screen\n\n";
		print "\n";
		print "This script takes in lead files in the following order when they are placed in the $PATHhome/LEADS_IN directory to be imported into the vicidial_list table (examples):\n\n";
		print "standard:\n";
		print "vendor_lead_code|source_id|list_id|phone_code|phone_number|title|first_name|middle|last_name|address1|address2|address3|city|state|province|postal_code|country|gender|date_of_birth|alt_phone|email|security_phrase|COMMENTS|called_count|status|entry_date|multi-alt-entries\n";
		print "3857822|31022|105|01144|1625551212|MRS|B||BURTON|249 MUNDON ROAD|MALDON|ESSEX||||CM9 6PW|UK||||||COMMENTS|2|B|2007-08-09 00:00:00|7275551212_1_work!7275551213_61_sister house!7275551214_44_neighbor\n\n";
		print "vicidl:\n";
		print "lead_id\tentry_date\tmodify_date\tstatus\tuser\tvendor_lead_code\tsource_id\tlist_id\tgmt_offset_now\tcalled_since_last_reset\tphone_code\tphone_number\ttitle\tfirst_name\tmiddle_initial\tlast_name\taddress1\taddress2\taddress3\tcity\tstate\tprovince\tpostal_code\tcountry_code\tgender\tdate_of_birth\talt_phone\temail\tsecurity_phrase\tcomments\tcalled_count\tlast_local_call_time\trank\towner\tentry_list_id\n";
		print "327163\t2022-01-01 00:00:00\t2022-01-02 10:00:00\tA\t1234\t12345\tinternet\t999\t-5.00\tY\t1\t3145551212\tTIM\tBURTON\t1234 Mockingbird Ln.\t\t\tAtlanta\tGA\t\t04240\tUS\tM\t0000-00-00\t8589134523\t\tGreen\t2\t2022-01-02 10:01:12\t0\t\t0\n\n";
		print "stdrankowner:\n";
		print "vendor_lead_code|source_id|list_id|phone_code|phone_number|title|first_name|middle|last_name|address1|address2|address3|city|state|province|postal_code|country|gender|date_of_birth|alt_phone|email|security_phrase|COMMENTS|called_count|status|entry_date|rank|owner|multi-alt-entries\n";
		print "3857822|31022|105|01144|1625551212|MRS|B||BURTON|249 MUNDON ROAD|MALDON|ESSEX||||CM9 6PW|UK||||||COMMENTS|2|B|2007-08-09 00:00:00|99|6666|7275551212_1_work!7275551213_61_sister house!7275551214_44_neighbor\n\n";
		print "minicsv:\n";
		print "address1,city,name,phone_number,state,postal_code\n";
		print "\"105 Fifth St\",\"Steinhatchee\",\"Frank Smith\",\"3525556601\",\"FL\",\"32359\"\n\n";
		print "minicsv02:\n";
		print "name,address1,city,state,postal_code,phone_number\n";
		print "\"Adam Smith\",\"123 Fourth St\",\"Englishtown\",\"NS\",\"B0C1H0\",\"902-555-1212\"\n\n";
		print "fixed254:\n";
		print "fixed width(without the quotes)\n";
		print "\"9185551212ROSE            SMITHS                  155 TIGER MOUNTAIN RD.                  RR 1 BOX 107                            HENRYETTA                   OK74437-941DEMG  226555                                                   0                     \"\n\n";
		print "sctab08:\n";
		print "STATE\tNAME\tPHONE\tFAX\tADDRESS\tADDRESS2\tCITY\tZIP\n";
		print "AL\tTom Wilson\t205 5551212\t\t 1689 15th Ave\t\tBESSEMER\t35020\n\n";
		print "sccsv11:\n";
		print "STATE,ID,NAME,ADDRESS,ADDRESS2,CITY,COUNTY,PHONE,FAX,ZIP,COMMENTS\n";
		print "AL,16700,Fairfax Retirement Center (Crowder House),1324 25th St,,FAIRFAX,JEFFERSON,(205) 555-0508,,35733,Comments\n\n";
		print "dccsv10:\n";
		print "VENDOR_ID,FIRST_NAME,LAST_NAME,PHONE_1,PHONE_2,PHONE_3,PHONE_4,PHONE_5,PHONE_6,PHONE_7\n";
		print "\"100998\",\"ANGELA    \",\"SMITH     \",\"3145551212\",\"3145551213\",\"3145551214\",\"0\",\"3145551215\",\"3145551216\",\"0\",\n\n";
		print "pipe30tz:\n";
		print "TEST01||09292011|1|5125554727||Mike||Frank|||||||||||||||||C|2145559922|TESTSURVEY|TESTSURVEY|111\n";
		print "vote17csv:\n";
		print "Voter ID,County Code,Name Last,Gender,Race,Birth Date,Party Affiliation,Precinct,Congressional District,House District,Senate District,County Commission District,School Board District,Age Bracket,_2010_GEN,_2012_PRI_A_Date,Phone_10digit\n";
		print "\"110306742\",\"DAD\",\"Da Silva Ramos\",\"F\",\"5\",\"06/12/1950\",\"DEM\",\"100\",\"23\",\"0\",\"0\",\"0\",\"0\",\"4\",\"A\",\"\",\"0033559433\"\n\n";
		print "t2r13csv:\n";
		print "first_name,last_name,address3,comments1,province,phone_number,address1,city,state,postal_code,comments2,comments3,comments4\n";
		print "Bob,Smith,\"Bob, Inc.\",lead,president,3125551212,1234 main st,Chicago,IL,60987,good,1235,T2R\n\n";
		print "t2r25csv:\n";
		print "1,2,last_name,province,first_name,phone_number,7,address1,city,state,postal_code,12,address2,email,15,16,address3,18,19,20,21,comments,23,24,25\n";
		print "x,x,Smith,sale,Bob,3125551212,x,1234 Main St.,Chicago,IL,60987,x,mid-block,bob@asdf.com,x,x,2nd floor,x,x,x,x,last purchase,x,x,x\n\n";		
		print "t2r25xcsv:\n";
		print "phone_number,x,last_name,x,province,first_name,x,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,x,comments,x,x,x,address3\n\n";
		print "t2r26csv:\n";
		print "phone_number,x,last_name,x,x,x,x,province,first_name,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,comments,x,x,x,address3\n\n";
		print "t2r27csv:\n";
		print "phone_number,x,last_name,x,x,x,x,province,first_name,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,x,comments,x,x,x,address3\n\n";		
		print "t2r15csv:\n";
		print "phone_number,first_name,x,x,x,city,x,address2,last_name,x,x,x,address1,address3,x\n\n";		
		print "twotab:\n";
		print "UniqueID,PhoneNumber\n";
		print "110306742,3125556666\n\n";
		print "dccsv43, dccsvref51, dccsv52 and dccsvref52:\n";
		print "---format too confusing to list in the help screen---\n\n";

		exit;
		}
	else
		{
		if ($args =~ /--quiet/i)
			{
			$q=1;
			$DB=0;
			$DBX=0;
			$force_quiet=1;
			}
		if ( ($args =~ /-debug/i) && ($force_quiet < 1) )
			{
			$DB=1;
			print "\n----- DEBUGGING -----\n\n";
			}
		if ( ($args =~ /-debugX/i) && ($force_quiet < 1) )
			{
			$DBX=1;
			print "\n----- SUPER-DUPER DEBUGGING -----\n\n";
			}
		else {$DBX=0;}

		if ($args =~ /--test/i)
			{
			$T=1;
			$TEST=1;
			if ($q < 1) {print "\n----- TESTING -----\n\n";}
			}
		if ($args =~ /-forcegmt/i)
			{
			$forcegmt=1;
			if ($q < 1) {print "\n----- FORCE GMT -----\n\n";}
			}
		if ($args =~ /-forcelistid=/i)
			{
			@data_in = split(/-forcelistid=/,$args);
				$forcelistid = $data_in[1];
				$forcelistid =~ s/ .*//gi;
			if ($q < 1) {print "\n----- FORCE LISTID OVERRIDE: $forcelistid -----\n\n";}
			}
		else
			{$forcelistid = '';}

		if ($args =~ /-forcelistfilename/i)
			{
			$forcelistfilename=1;
			if ($q < 1) {print "\n----- FORCE LISTID FROM FILENAME -----\n\n";}
			}

		if ($args =~ /-format=/i)
			{
			@data_in = split(/-format=/,$args);
				$format = $data_in[1];
				$format =~ s/ .*//gi;
			if ($q < 1) {print "\n----- FORMAT OVERRIDE: $format -----\n\n";}
			}
		else
			{$format = 'standard';}

		if ($args =~ /--forcephonecode=/i)
			{
			@data_in = split(/--forcephonecode=/,$args);
				$forcephonecode = $data_in[1];
				$forcephonecode =~ s/ .*//gi;
			if ($q < 1) {print "\n----- FORCE PHONECODE OVERRIDE: $forcephonecode -----\n\n";}
			}
		else
			{$forcephonecode = '';}

		if ($args =~ /--file-prefix-filter=/i)
			{
			@data_in = split(/--file-prefix-filter=/,$args);
				$file_prefix_filter = $data_in[1];
				$file_prefix_filter =~ s/ .*//gi;
			if ($q < 1) {print "\n----- FILE PREFIX FILTER: $file_prefix_filter -----\n\n";}
			}
		else
			{$file_prefix_filter = '';}

		if ($args =~ /--USACAN-prefix-check/i)
			{
			$usacan_prefix_check=1;
			if ($q < 1) {print "\n----- USACAN PREFIX CHECK -----\n\n";}
			}
		if ($args =~ /--USACAN-areacode-check/i)
			{
			$usacan_areacode_check=1;
			if ($q < 1) {print "\n----- USACAN AREACODE CHECK -----\n\n";}
			}
		if ($args =~ /--NANPA-ac-prefix-check/i)
			{
			$nanpa_ac_prefix_check=1;
			if ($q < 1) {print "\n----- NANPA AREACODE AND PREFIX CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-check/i)
			{
			$dupcheck=1;
			if ($q < 1) {print "\n----- DUPLICATE CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-campaign-check/i)
			{
			$dupcheckcamp=1;
			if ($q < 1) {print "\n----- DUPLICATE CAMPAIGN CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-system-check/i)
			{
			$dupchecksys=1;
			if ($q < 1) {print "\n----- DUPLICATE SYSTEM CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-tap-list-check/i)
			{
			$duptapchecklist=1;
			if ($q < 1) {print "\n----- DUPLICATE TITLE/ALT-PHONE LIST CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-tap-system-check/i)
			{
			$duptapchecksys=1;
			if ($q < 1) {print "\n----- DUPLICATE TITLE/ALT-PHONE SYSTEM CHECK -----\n\n";}
			}
		if ($args =~ /-duplicate-tnm-delete/i)
			{
			$duptnmdelete=1;
			if ($q < 1) {print "\n----- DUPLICATE TITLE DIFF DELETE -----\n\n";}
			}
		if ($args =~ /--dob-mmddyyy/i)
			{
			$dob_mmddyyy=1;
			if ($q < 1) {print "\n----- DOB FORMAT MMDDYYYY -----\n\n";}
			}
		if ($args =~ /--dob-ddmmyyy/i)
			{
			$dob_ddmmyyy=1;
			if ($q < 1) {print "\n----- DOB FORMAT DDMMYYYY -----\n\n";}
			}
		if ($args =~ /-postal-code-gmt/i)
			{
			$postalgmt=1;
			if ($q < 1) {print "\n----- POSTAL CODE TIMEZONE -----\n\n";}
			}
		if ($args =~ /-time-zone-code-gmt/i)
			{
			$tzcodegmt=1;
			if ($q < 1) {print "\n----- TZ CODE TIMEZONE -----\n\n";}
			}
		if ($args =~ /--nanpa-gmt/i)
			{
			$nanpagmt=1;
			if ($q < 1) {print "\n----- NANPA AC-PREFIX TIMEZONE -----\n\n";}
			}

		if ($args =~ /-new-list-for-each-file/i)
			{
			$new_list_for_each_file=1;
			if ($q < 1) {print "\n----- NEW LIST FOR EACH FILE -----\n\n";}
			}
		if ($args =~ /--new-listid-prefix=/i)
			{
			@data_in = split(/--new-listid-prefix=/,$args);
				$list_id_prefix = $data_in[1];
				$list_id_prefix =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LISTID PREFIX: $list_id_prefix -----\n\n";}
			}
		else
			{$list_id_prefix = '';}
		if ($args =~ /--new-listname-prefix=/i)
			{
			@data_in = split(/--new-listname-prefix=/,$args);
				$list_name_prefix = $data_in[1];
				$list_name_prefix =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LISTNAME PREFIX: $list_name_prefix -----\n\n";}
			}
		else
			{$list_name_prefix = '';}
		if ($args =~ /--new-list-campaign=/i)
			{
			@data_in = split(/--new-list-campaign=/,$args);
				$list_campaign = $data_in[1];
				$list_campaign =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LIST CAMPAIGN: $list_campaign -----\n\n";}
			}
		else
			{$list_campaign = '';}
		if ($args =~ /--new-list-active=/i)
			{
			@data_in = split(/--new-list-active=/,$args);
				$active_list = $data_in[1];
				$active_list =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LIST ACTIVE: $active_list -----\n\n";}
			}
		else
			{$active_list = 'N';}
		if ($args =~ /--new-list-reset-times=/i)
			{
			@data_in = split(/--new-list-reset-times=/,$args);
				$reset_time = $data_in[1];
				$reset_time =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LIST RESET TIMES: $reset_time -----\n\n";}
			}
		else
			{$reset_time = '';}
		if ($args =~ /--new-list-tz-setting=/i)
			{
			@data_in = split(/--new-list-tz-setting=/,$args);
				$time_zone_setting = $data_in[1];
				$time_zone_setting =~ s/ .*//gi;
			if ($q < 1) {print "\n----- NEW LIST TZ SETTING: $time_zone_setting -----\n\n";}
			}
		else
			{$time_zone_setting = 'COUNTRY_AND_AREA_CODE';}

		if ($args =~ /-ftp-pull/i)
			{
			$ftp_pull=1;
			if ($q < 1) {print "\n----- FTP LEAD FILE PULL -----\n\n";}
			}
		if ($args =~ /--ftp-dir=/i)
			{
			@data_in = split(/--ftp-dir=/,$args);
				$ftp_dir = $data_in[1];
				$ftp_dir =~ s/ .*//gi;
			if ($q < 1) {print "\n----- REMOTE FTP DIRECTORY: $ftp_dir -----\n\n";}
			}
		else
			{$ftp_dir = '';}

		if ($args =~ /--email-list=/i)
			{
			@data_in = split(/--email-list=/,$args);
				$email_list = $data_in[1];
				$email_list =~ s/ .*//gi;
				$email_list =~ s/:/,/gi;
			if ($q < 1) {print "\n----- EMAIL NOTIFICATION: $email_list -----\n\n";}
			}
		else
			{$email_list = '';}

		if ($args =~ /--email-sender=/i)
			{
			@data_in = split(/--email-sender=/,$args);
				$email_sender = $data_in[1];
				$email_sender =~ s/ .*//gi;
				$email_sender =~ s/:/,/gi;
			if ($q < 1) {print "\n----- EMAIL NOTIFICATION SENDER: $email_sender -----\n\n";}
			}
		else
			{$email_sender = 'vicidial@localhost';}

		}
	}
else
	{
	print "no command line options set\n";
	$args = "";
	$i=0;
	$forcelistid = '';
	$format='standard';
	}
### end parsing run-time options ###

if ($q < 1)
	{
	print "\n\n\n\n\n\n\n\n\n\n\n\n-- VICIDIAL_IN_new_leads_file.pl --\n\n";
	print "This program is designed to take a tab delimited file and import it into the VICIDIAL system. \n\n";
	}

$i=0;
$US = '_';
$phone_list = '|';

if (!$VARDB_port) {$VARDB_port='3306';}

use DBI;	  

$dbhA = DBI->connect("DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port", "$VARDB_user", "$VARDB_pass")
 or die "Couldn't connect to database: " . DBI->errstr;

#############################################
##### START SYSTEM_SETTINGS LOOKUP #####
$stmtA = "SELECT use_non_latin FROM system_settings;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
$sthArows=$sthA->rows;
if ($sthArows > 0)
	{
	@aryA = $sthA->fetchrow_array;
	$non_latin		=		$aryA[0];
	}
$sthA->finish();
##### END SETTINGS LOOKUP #####
###########################################

if ( ($nanpa_ac_prefix_check > 0) || ($nanpagmt > 0) )
	{
	$vicidial_nanpa_prefix_codes_count=0;
	$stmtA = "SELECT count(*) FROM vicidial_nanpa_prefix_codes;";
	$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
	$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
	$sthArows=$sthA->rows;
	if ($sthArows > 0)
		{
		@aryA = $sthA->fetchrow_array;
		$vicidial_nanpa_prefix_codes_count = $aryA[0];
		}
	$sthA->finish();
	if ($vicidial_nanpa_prefix_codes_count < 10)
		{
		if ($q < 1)
			{
			print "NANPA options disabled! NANPA prefix data not loaded: $vicidial_nanpa_prefix_codes_count\n\n";
			}
		$nanpa_ac_prefix_check=0;
		$nanpagmt=0;
		}
	else
		{
		if ($q < 1)
			{
			print "NANPA prefix records: $vicidial_nanpa_prefix_codes_count\n\n";
			}
		}
	}



if ($non_latin > 0) {$affected_rows = $dbhA->do("SET NAMES 'UTF8'");}

$suf = '.txt';
$people_packages_id_update='';
$dir1 = "$PATHhome/LEADS_IN";
$dir2 = "$PATHhome/LEADS_IN/DONE";

	if($DBX){print STDERR "\nLEADS_IN directory: |$dir1|\n";}


$files_copied_count=0;
$fcc=0;
@FTPfiles=@MT;

if ($ftp_pull > 0)
	{
	$i=0;
	use Net::FTP;

	$ftp = Net::FTP->new("$VARREPORT_host", Port => "$VARREPORT_port", Debug => "$DBX",  Passive => 1)
					or die "Can't connect: $@\n";
	$ftp->login("$VARREPORT_user","$VARREPORT_pass") or die "Cannot log in /: $!";
	  @FILES = $ftp->dir("$ftp_dir")
	or die "Can't get a list of files in $ftp_dir: $!";

	foreach(@FILES)
		{
		if ($DBX > 0) {print "$FILES[$i]\n";}
		if ($FILES[$i] !~ /^d/i)
			{
			chomp($FILES[$i]);
			@FILEDETAILS = split(/ \d\d:\d\d /, $FILES[$i]);
			$FILES[$i] = "$FILEDETAILS[1]";
			$FILES[$i] =~ s/ *$//gi;
			
			$passed_file_filter=1;
			if (length($file_prefix_filter)>0)
				{
				$passed_file_filter=0;

				if ($FILES[$i] !~ /^$file_prefix_filter/)
					{
					if ($DB > 0) {print "SKIPPING FILE, NO FILTER MATCH: $FILES[$i]\n";}
					}
				else
					{$passed_file_filter=1;}
				}
			if ( (length($FILES[$i]) > 4) && ($passed_file_filter > 0) )
				{
				$GOODfname = $FILES[$i];
				$FILES[$i] =~ s/ /_/gi;
				$FILES[$i] =~ s/\(|\)|\||\\|\/|\'|\"|//gi;
				$ftp->rename("$ftp_dir/$GOODfname","$ftp_dir/$FILES[$i]");
				$FILEname = $FILES[$i];
				$ftp->get("$ftp_dir/$FILES[$i]", "$dir1/$FILES[$i]");

				if (!$TEST) {$ftp->rename("$ftp_dir/$FILES[$i]", "$ftp_dir/DONE/$FILES[$i]");}
				if ($DB > 0) {print "FTP FILE COPIED: $FILES[$i]\n";}
				$FTPfiles[$fcc] = $FILES[$i];
				$fcc++;
				$files_copied_count++;
				}
			}
		$i++;
		}
	if (!$q) {print "$ftp_dir - $VARREPORT_host - $#FILES - $files_copied_count\n";}

	@FILES = @FTPfiles;
	}
else
	{
	@FILES=@MT;
	opendir(FILE, "$dir1/");
	@FILES = readdir(FILE);
	}


$i=0;

foreach(@FILES)
	{
	$size1 = 0;
	$size2 = 0;
	$person_id_delete = '';
	$transaction_id_delete = '';
	$forcelistfilename_name = '';

	if (length($FILES[$i]) > 4)
		{
		$size1 = (-s "$dir1/$FILES[$i]");
		if (!$q) {print "$FILES[$i] $size1\n";}
		sleep(2);
		$size2 = (-s "$dir1/$FILES[$i]");
		if (!$q) {print "$FILES[$i] $size2\n\n";}

		$passed_file_filter=1;
		if (length($file_prefix_filter)>0)
			{
			$passed_file_filter=0;

			if ($FILES[$i] !~ /^$file_prefix_filter/)
				{
				if ($DB > 0) {print "SKIPPING FILE, NO FILTER MATCH: $FILES[$i]\n";}
				}
			else
				{$passed_file_filter=1;}
			}

		if ( ($FILES[$i] !~ /^TRANSFERRED/i) && ($size1 eq $size2) && (length($FILES[$i]) > 4) && ($passed_file_filter > 0) )
			{
			$GOODfname = $FILES[$i];
			$FILES[$i] =~ s/ /_/gi;
			$FILES[$i] =~ s/\(|\)|\||\\|\/|\'|\"|//gi;
			rename("$dir1/$GOODfname","$dir1/$FILES[$i]");
			$FILEname = $FILES[$i];

			`cp -f $dir1/$FILES[$i] $dir2/$source$FILES[$i]`;

			if ($forcelistfilename > 0)
				{
				$forcelistfilename_name = $FILES[$i];
				$forcelistfilename_name =~ s/\..*//gi;
				@forcelistfilename_nameARY = split(/_/, $forcelistfilename_name);
				$forcelistfilename_listid = $forcelistfilename_nameARY[$#forcelistfilename_nameARY];
				$forcelistfilename_listid =~ s/\D//gi;
				if ($DB > 0) {print "$forcelistfilename_listid|$#forcelistfilename_nameARY|$forcelistfilename_name|$FILES[$i]\n";}
				}

			if ($new_list_for_each_file > 0)
				{
				$dup_list_id=1;
				$xloop=0;
				$x=1;
				$new_list_id = "$list_id_prefix$listdate$x";
				while ( ($xloop < 10000) && ($dup_list_id > 0) )
					{
					$stmtA = "SELECT count(*) from vicidial_lists where list_id='$new_list_id';";
						if($DBX){print STDERR "\n|$stmtA|\n";}
					$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
					$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
					$sthArows=$sthA->rows;
					if ($sthArows > 0)
						{
						@aryA = $sthA->fetchrow_array;
						$dup_list_id = $aryA[0];
						}
					$sthA->finish();
					if ($dup_list_id > 0)
						{
						$x++;
						$new_list_id = "$list_id_prefix$listdate$x";
						}
					$xloop++;
					}


				$forcelistfilename_listid = $new_list_id;
				$stmtZ = "INSERT INTO vicidial_lists (list_id,list_name,list_description,campaign_id,active,list_changedate,time_zone_setting,reset_time) values('$new_list_id','$list_name_prefix $FILES[$i]','Created: $insert_date','$list_campaign','$active_list','$insert_date','$time_zone_setting','$reset_time');";
					if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
				if ($DB > 0) {print "LIST CREATED: $new_list_id|$affected_rows|$stmtZ\n";}
				}


			### open the in file for reading ###
			open(infile, "$dir2/$source$FILES[$i]")
					|| die "Can't open $source$FILES[$i]: $!\n";


		### Grab Server values from the database
		$stmtA = "SELECT local_gmt FROM servers where server_ip = '$server_ip';";
		$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
		$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
		@aryA = $sthA->fetchrow_array;
		$DBSERVER_GMT		=		$aryA[0];
		if (length($DBSERVER_GMT)>0)	{$SERVER_GMT = $DBSERVER_GMT;}
		$sthA->finish();

			$LOCAL_GMT_OFF = $SERVER_GMT;
			$LOCAL_GMT_OFF_STD = $SERVER_GMT;

		if ($isdst) {$LOCAL_GMT_OFF++;} 
		if ($DB) {print "SEED TIME  $secX      :   $year-$mon-$mday $hour:$min:$sec  LOCAL GMT OFFSET NOW: $LOCAL_GMT_OFF\n";}


		$a=0;	### each line of input file counter ###
		$b=0;	### status of 'APPROVED' counter ###
		$c=0;	### status of 'DECLINED' counter ###
		$d=0;	### status of 'REFERRED' counter ###
		$e=0;	### status of 'ERROR' counter ###
		$f=0;	### number of 'DUPLICATE' counter ###
		$g=0;	### number of leads with multi-alt-entries
		$h=0;	### number of 'INVALID' counter ###

		$multi_insert_counter=0;
		$multistmt='';
		$rank='';
		$owner='';

		#if ($DB)
		while (<infile>)
		{
		@m=@MT;
	#		print "$a| $number\n";
			$number = $_;
			$raw_number = $number;
			chomp($number);
	#		$number =~ s/,/\|/gi;
			$number =~ s/\t/\|/gi;
			$number =~ s/|\t|\r|\n|\l//gi;
			$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
			$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
			$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
			$number =~ s/\",,,,\"/\|\|\|\|/gi;
			$number =~ s/\",,,\"/\|\|\|/gi;
			$number =~ s/\",,\"/\|\|/gi;
			$number =~ s/\",\"/\|/gi;
			$number =~ s/\"//gi;
		@m = split(/\|/, $number);

		$format_set=0;

		# This is the format for the minicsv02 lead files
		# name,address1,city,state,postal_code,phone_number
		# "Adam Smith","123 Fourth St","Englishtown","NS","B0C1H0","902-555-1212"
			if ( ($format =~ /minicsv02/) && ($format_set < 1) )
				{
				@name=@MT;
				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[5];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$full_name =			$m[0];		@name = split(/ /, $full_name);
				$first_name =			$name[0];		chomp($first_name);
				$last_name =			"$name[1] $name[2]";		chomp($first_name);
				$middle_initial =		'';
				$address1 =				$m[1];		chomp($address1);
				$address2 =				'';
				$address3 =				'';
				$city =					$m[2];		chomp($city);
				$state =				$m[3];		chomp($state);
				$province =				'';
				$postal_code =			$m[4];		chomp($postal_code);
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$alt_phone =			'';
				$email =				'';
				$security_phrase =		'';
				$comments =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the minicsv lead files
		#"105 Fifth St","Steinhatchee","Frank Smith","3525556601","FL","32359"
			if ( ($format =~ /minicsv/) && ($format_set < 1) )
				{
				@name=@MT;
				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[3];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$full_name =			$m[2];		@name = split(/ /, $full_name);
				$first_name =			$name[1];		chomp($first_name);
				$last_name =			"$name[0] $name[2]";		chomp($first_name);
				$middle_initial =		'';
				$address1 =				$m[0];		chomp($address1);
				$address2 =				'';
				$address3 =				'';
				$city =					$m[1];		chomp($city);
				$state =				$m[4];		chomp($state);
				$province =				'';
				$postal_code =			$m[5];		chomp($postal_code);
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$alt_phone =			'';
				$email =				'';
				$security_phrase =		'';
				$comments =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the twotab lead files
		#14234243,3125556677
			if ( ($format =~ /twotab/) && ($format_set < 1) )
				{
				@name=@MT;
				$raw_number =~ s/\'|\r|\n|\l//gi;
				$raw_number =~ s/,|\t/\|/gi;
				@m = split(/\|/, $raw_number);
				$vendor_lead_code =		$m[0];
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[1];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$full_name =			'';
				$first_name =			'';
				$last_name =			'';
				$middle_initial =		'';
				$address1 =				'';
				$address2 =				'';
				$address3 =				'';
				$city =					'';
				$state =				'';
				$province =				'';
				$postal_code =			'';
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$alt_phone =			'';
				$email =				'';
				$security_phrase =		'';
				$comments =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the vote17csv lead files
		# Voter ID,County Code,Name Last,Gender,Race,Birth Date,Party Affiliation,Precinct,Congressional District,House District,Senate District,County Commission District,School Board District,Age Bracket,_2010_GEN,_2012_PRI_A_Date,Phone_10digit
		# "110555742","DAD","Bob Smith","F","5","06/14/1970","DEM","100","23","0","0","0","0","4","A","","8135559433"
			if ( ($format =~ /vote17csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$vendor_lead_code =		'';
				$source_id =			$m[0];	# voter ID number
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[16];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[6];	# political party
				$first_name =			'';
				$last_name =			$m[2];		chomp($first_name);

				$middle_initial =		'';
				$address1 =				$m[9];	# state House district
				$address2 =				$m[10];	# state Senate district
				$address3 =				$m[1];	# county
				$city =					'';
				$state =				$m[4];	# race/ethnicity
				$province =				$m[7];	# precinct
				$postal_code =			'';
				$country =				'USA';
				$gender =				$m[3];
				$date_of_birth =		$m[5];
				$alt_phone =			$m[12];	# school district
				$email =				$m[11];	# county commission district
				$security_phrase =		'';		# first survey question response
				$comments =				'';
				$rank =					'0';
				$owner =				$m[8];	# US Congress district
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the t2r13csv lead files
		# first_name,last_name,address3,comments1,province,phone_number,address1,city,state,postal_code,comments2,comments3,comments4
		# Bob,Smith,"Bob, Inc.",lead,president,3125551212,1234 main st,Chicago,IL,60987,good,1235,T2R
			if ( ($format =~ /t2r13csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[5];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[0];
				$last_name =			$m[1];		chomp($first_name);

				$middle_initial =		'';
				$address1 =				$m[6];
				$address2 =				'';	
				$address3 =				$m[2];
				$city =					$m[7];
				$state =				$m[8];
				$province =				$m[4];
				$postal_code =			$m[9];
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				'';
				$security_phrase =		'';		
				$comments =				"$m[3] $m[10] $m[11] $m[12]";
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the t2r25csv lead files
		# 1,2,last_name,province,first_name,phone_number,7,address1,city,state,postal_code,12,address2,email,15,16,address3,18,19,20,21,comments,23,24,25
		# x,x,Smith,sale,Bob,3125551212,x,1234 Main St.,Chicago,IL,60987,x,mid-block,bob@asdf.com,x,x,2nd floor,x,x,x,x,last purchase,x,x,x
			if ( ($format =~ /t2r25csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[5];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[4];		chomp($first_name);
				$last_name =			$m[2];

				$middle_initial =		'';
				$address1 =				$m[7];
				$address2 =				$m[12];
				$address3 =				$m[16];
				$city =					$m[8];
				$state =				$m[9];
				$province =				$m[3];
				$postal_code =			$m[10];
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				$m[13];
				$security_phrase =		'';		
				$comments =				$m[21];
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the t2r25xcsv lead files
		# phone_number,x,last_name,x,province,first_name,x,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,x,comments,x,x,x,address3

			if ( ($format =~ /t2r25xcsv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[0];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[5];		chomp($first_name);
				$last_name =			$m[2];

				$middle_initial =		'';
				$address1 =				$m[8];
				$address2 =				$m[12];
				$address3 =				$m[24];
				$city =					$m[9];
				$state =				$m[10];
				$province =				$m[4];
				$postal_code =			$m[11];
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				$m[13];
				$security_phrase =		'';		
				$comments =				$m[20];
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}



		# This is the format for the t2r27csv lead files
		# phone_number,x,last_name,x,x,x,x,province,first_name,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,x,comments,x,x,x,address3

			if ( ($format =~ /t2r27csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[0];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[8];		chomp($first_name);
				$last_name =			$m[2];

				$middle_initial =		'';
				$address1 =				$m[10];
				$address2 =				$m[14];
				$address3 =				$m[26];
				$city =					$m[11];
				$state =				$m[12];
				$province =				$m[7];
				$postal_code =			$m[13];
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				$m[15];
				$security_phrase =		'';		
				$comments =				$m[22];
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}


		# This is the format for the t2r26csv lead files
		# phone_number,x,last_name,x,x,x,x,province,first_name,x,address1,city,state,postal_code,address2,email,x,x,x,x,x,comments,x,x,x,address3

			if ( ($format =~ /t2r26csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[0];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[8];		chomp($first_name);
				$last_name =			$m[2];

				$middle_initial =		'';
				$address1 =				$m[10];
				$address2 =				$m[14];
				$address3 =				$m[25];
				$city =					$m[11];
				$state =				$m[12];
				$province =				$m[7];
				$postal_code =			$m[13];
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				$m[15];
				$security_phrase =		'';		
				$comments =				$m[21];
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}


		# This is the format for the t2r15csv lead files
		# phone_number,first_name,x,x,x,city,x,address2,last_name,x,x,x,address1,address3,x

			if ( ($format =~ /t2r15csv/) && ($format_set < 1) )
				{
				@name=@MT;
				$number = $raw_number;
				chomp($number);
				$number =~ s/"(.+?[^\\])"/($ret = $1) =~ (s#,##g); $ret/ge;
				$number =~ s/\t/\|/gi;
				$number =~ s/&/and/gi;
				$number =~ s/,/\|/gi;
				$number =~ s/|\t|\r|\n|\l//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[0];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$first_name =			$m[1];		chomp($first_name);
				$last_name =			$m[8];

				$middle_initial =		'';
				$address1 =				$m[12];
				$address2 =				$m[7];
				$address3 =				$m[13];
				$city =					$m[9];
				$state =				'';
				$province =				'';
				$postal_code =			'';
				$country =				'USA';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			'';
				$email =				'';
				$security_phrase =		'';		
				$comments =				'';
				$rank =					'0';
				$owner =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the fixed254 lead files
		#"9185551212ROSE            SMITHS                  155 TIGER MOUNTAIN RD.                  RR 1 BOX 107                            HENRYETTA                   OK74437-941DEMG  226555                                                   0                     "
			if ( ($format =~ /fixed254/) && ($format_set < 1) )
				{
				$phone_number =			substr($raw_number, 0, 10);		$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$phone_code =			'1';
				$first_name =			substr($raw_number, 10, 16);	$first_name =~ s/\s+$//gi;
				$last_name =			substr($raw_number, 26, 24);	$last_name =~ s/\s+$//gi;
				$address1 =				substr($raw_number, 50, 40);	$address1 =~ s/\s+$//gi;
				$address2 =				substr($raw_number, 90, 40);	$address2 =~ s/\s+$//gi;
				$city =					substr($raw_number, 130, 28);	$city =~ s/\s+$//gi;
				$state =				substr($raw_number, 158, 2);
				$postal_code =			substr($raw_number, 160, 5);	$postal_code =~ s/\s+$//gi;
				$province =				substr($raw_number, 169, 4);
				$vendor_lead_code =		substr($raw_number, 173, 8);	$vendor_lead_code =~ s/^\s+//gi;
				$security_phrase =		substr($raw_number, 181, 4);

				$source_id =			'';
				$list_id =				'995';
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$alt_phone =			'';
				$email =				'';
				$comments =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the sctab08 lead files
		# STATE\tNAME\tPHONE\tFAX\tADDRESS\tADDRESS2\tCITY\tZIP\n";
		# AL\tTom Wilson\t205 5551212\t\t 1689 15th Ave\t\tBESSEMER\t35020
			if ( ($format =~ /sctab08/) && ($format_set < 1) )
				{
				@name=@MT;
				$vendor_lead_code =		'';
				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$phone_number =			$m[2];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$full_name =			$m[1];		@name = split(/ /, $full_name);
				$first_name =			$name[1];		chomp($first_name);
				$last_name =			"$name[0] $name[2]";		chomp($first_name);
				$middle_initial =		'';
				$address1 =				$m[4];		chomp($address1);
				$address2 =				$m[5];		chomp($address1);
				$address3 =				'';
				$city =					$m[6];		chomp($city);
				$state =				$m[0];		chomp($state);
				$province =				'';
				$postal_code =			$m[7];		chomp($postal_code);
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$alt_phone =			$m[3];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				'';
				$security_phrase =		'';
				$comments =				'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the sccsv11 lead files
		# STATE,ID,NAME,ADDRESS,ADDRESS2,CITY,COUNTY,PHONE,FAX,ZIP,COMMENTS
		# AL,16700,Fairfax Retirement Center (Crowder House),1324 25th St,,FAIRFAX,JEFFERSON,(205) 555-0508,,35733,Comments

			if ( ($format =~ /sccsv11/) && ($format_set < 1) )
				{
				$raw_number =~ s/\'|\t|\r|\n|\l//gi;
				$raw_number =~ s/,/\|/gi;
				@m = split(/\|/, $raw_number);
				@name=@MT;
				$state =				$m[0];		chomp($state);
				$vendor_lead_code =		$m[1];		$vendor_lead_code =~ s/\D//gi;

				$field = 2;
				if ($m[$field] =~ /^\"/)
					{
					$full_name =		"$m[$field]";   $field++;
					$full_name .=		"$m[$field]";   $field++;
					}
				else
					{
					$full_name =		"$m[$field]";   $field++;
					}
				$full_name =~ s/\"|\)|\(|;//gi;
				@name = split(/ /, $full_name);
					$first_name =			$name[1];		chomp($first_name);
					$last_name =			"$name[0] $name[2]";		chomp($first_name);
					$middle_initial =		'';

				if ($m[$field] =~ /^\"/)
					{
					$address1 =		"$m[$field]";   $field++;
					$address1 .=		"$m[$field]";   $field++;
					}
				else
					{
					$address1 =		"$m[$field]";   $field++;
					}
				$address1 =~ s/\"|\)|\(|;//gi;

				if ($m[$field] =~ /^\"/)
					{
					$address2 =		"$m[$field]";   $field++;
					$address2 .=		"$m[$field]";   $field++;
					}
				else
					{
					$address2 =		"$m[$field]";   $field++;
					}
				$address2 =~ s/\"|\)|\(|;//gi;

				if ($m[$field] =~ /^\"/)
					{
					$city =		"$m[$field]";   $field++;
					$city .=		"$m[$field]";   $field++;
					}
				else
					{
					$city =		"$m[$field]";   $field++;
					}
				$city =~ s/\"|\)|\(|;//gi;

				if ($m[$field] =~ /^\"/)
					{
					$province =		"$m[$field]";   $field++;
					$province .=		"$m[$field]";   $field++;
					}
				else
					{
					$province =		"$m[$field]";   $field++;
					}
				$province =~ s/\"|\)|\(|;//gi;

				$phone_number =		"$m[$field]";   $field++;	$phone_number =~ s/\D//gi;
					$USarea = 				substr($phone_number, 0, 3);
				$alt_phone =		"$m[$field]";   $field++;	$alt_phone =~ s/\D//gi;
				$postal_code =		"$m[$field]";   $field++;

				if ($m[$field] =~ /^\"/)
					{
					$comments =		"$m[$field]";   $field++;
					$comments .=		"$m[$field]";   $field++;
					}
				else
					{
					$comments =		"$m[$field]";   $field++;
					}
				$comments =~ s/\"|\)|\(|;//gi;

				$source_id =			'';
				$list_id =				'995';
				$phone_code =			'1';
				$title =				'';
				$address3 =				'';
				$country =				'USA';
				$gender =				'U';
				$date_of_birth =		'0000-00-00';
				$email =				'';
				$security_phrase =		'';
				$called_count =			0;
				$status =				'NEW';

				$format_set++;
				}

		# This is the format for the stdrankowner lead files (standard plus rank and owner )
		#3857822|31022|105|01144|1625551212|MRS|B||BURTON|249 MUNDON ROAD|MALDON|ESSEX||||CM9 6PW|UK||||||COMMENTS|COUNT|STATUS|INS-DATE|RANK|OWNER
			if ( ($format =~ /stdrankowner/) && ($format_set < 1) )
				{
				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);
				$source_id =			$m[1];		chomp($source_id);
				$list_id =				$m[2];		chomp($list_id);
				$phone_code =			$m[3];		chomp($phone_code);	$phone_code =~ s/\D//gi;
				$phone_number =			$m[4];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[5];		chomp($title);
				$first_name =			$m[6];		chomp($first_name);
				$middle_initial =		$m[7];		chomp($middle_initial);
				$last_name =			$m[8];		chomp($last_name);
				$address1 =				$m[9];		chomp($address1);
				$address2 =				$m[10];		chomp($address2);
				$address3 =				$m[11];		chomp($address3);
				$city =					$m[12];		chomp($city);
				$state =				$m[13];		chomp($state);
				$province =				$m[14];		chomp($province);
				$postal_code =			$m[15];		chomp($postal_code);
				$country =				$m[16];		chomp($country);
				$gender =				$m[17];
				$date_of_birth =		$m[18];
				$alt_phone =			$m[19];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				$m[20];
				$security_phrase =		$m[21];
				$comments =				$m[22];
				$called_count =			$m[23];		$called_count =~ s/\D|\r|\n|\t//gi; if (length($called_count)<1) {$called_count=0;}
				$status =				$m[24];		$status =~ s/ |\r|\n|\t//gi;  if (length($status)<1) {$status='NEW';}
				$insert_date =			$m[25];	$insert_date =~ s/\r|\n|\t|[a-zA-Z]//gi;  if (length($insert_date)<6) {$insert_date=$pulldate0;}
					if ($insert_date =~ /\//) 
						{
						@iD = split(/\//, $insert_date);
						$iD[0] = sprintf("%02d", $iD[0]);
						$iD[1] = sprintf("%02d", $iD[1]);
						$insert_date = "$iD[2]-$iD[0]-$iD[1]";
						}
				$rank =					$m[26];
				$owner =				$m[27];
				$multi_alt_phones =		$m[28];

				$map_count=0;
				if (length($multi_alt_phones)>4)
					{
					@map=@MT;  @ALTm_phone_code=@MT;  @ALTm_phone_number=@MT;  @ALTm_phone_note=@MT;
					@map = split(/\!/, $multi_alt_phones);
					$map_count = ($#map +1);
					if ($DBX) {print "multi-al-entry: $a|$map_count|$multi_alt_phones\n";}
					$g++;
					$r=0;
					while ($r < $map_count)
						{
						@ncn=@MT;
						@ncn = split(/\_/, $map[$r]);
						print "$ncn[0]|$ncn[1]|$ncn[2]";

						if (length($forcephonecode) > 0)
							{$ALTm_phone_code[$r] =	$forcephonecode;}
						else
							{$ALTm_phone_code[$r] =		$ncn[1];}
						if (length($ALTm_phone_code[$r]) < 1)
							{$ALTm_phone_code[$r]='1';}
						$ALTm_phone_number[$r] =	$ncn[0];
						$ALTm_phone_note[$r] =		$ncn[2];
						$r++;
						}
					}

				$format_set++;
				}

		# This is the format for the dccsv10 lead files
		#"100998","ANGELA    ","SMITH     ","3145551212","3145551213","3145551214","0","3145551215","3145551216","0",
			if ( ($format =~ /dccsv10/) && ($format_set < 1) )
				{
				$raw_number = $number;
				chomp($number);
				$number =~ s/,"0"//gi;
				$number =~ s/\t/\|/gi;
				$number =~ s/\'|\t|\r|\n|\l//gi;
				$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
				$number =~ s/\",,,,\"/\|\|\|\|/gi;
				$number =~ s/\",,,\"/\|\|\|/gi;
				$number =~ s/\",,\"/\|\|/gi;
				$number =~ s/\",\"/\|/gi;
				$number =~ s/\"//gi;
				@m = split(/\|/, $number);

				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);
				$source_id =			'';
				$list_id =				'929';
				$phone_code =			'1';
				$first_name =			$m[1];		chomp($first_name);		$first_name =~ s/\s+$//gi;
				$middle_initial =		'';
				$last_name =			$m[2];		chomp($last_name);		$last_name =~ s/\s+$//gi;
				$phone_number =			$m[3];
					$USarea = 			substr($phone_number, 0, 3);
				$title =				'';
				$address1 =				'';
				$address2 =				'';
				$address3 =				$m[5];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$city =					'';
				$state =				'';
				$province =				'';
				$postal_code =			'';
				$country =				'';
				$gender =				'';
				$date_of_birth =		'';
				$alt_phone =			$m[4];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				'';
				$security_phrase =		'';
				$comments =				'';
				$called_count =			'0';
				$status =				'NEW';
				$insert_date =			$pulldate0;
				$rank =					'';
				$owner =				'';
				$multi_alt_phones =		'';

				$r=0;
				$map_count=0;
				if (length($m[6]) > 9) 
					{
					$ALTm_phone_number[$r] =	$m[6];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					$g++;
					}
				if (length($m[7]) > 9) 
					{
					$ALTm_phone_number[$r] =	$m[7];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if (length($m[8]) > 9) 
					{
					$ALTm_phone_number[$r] =	$m[8];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if (length($m[9]) > 9) 
					{
					$ALTm_phone_number[$r] =	$m[9];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}

				$format_set++;
				}

		# This is the format for the dccsv43 lead files
#"BF_ID","RECORD_TYPE","LAST_NAME","FIRST_NAME","4ADDR1","5ADDR2","6CITY","STATE","ZIP","9ZIP4","ADDR_STATUS","DATE_PLACED","DATE_ADDED","DOB","LAST_LETTER","LAST_LETTER_DATE","LAST_WORKED","NEXT_ACTION_DATE","CAPTURE_CODE","CUR_CATEGORY","TIMES_DIALED","LAST_DIALED","TOTAL_PAID","DATE_LAST_PAID","NMBR_CALLS","NMBR_CONTACTS","NMBR_TIMES_WRKD","NMBR_LETTERS","STATUS_CODE","STATUS_DATE","SCORE","TIMES_TO_SERVICER","1ST-PMT-DEFAULT","TIME_ZONE","ORIG_CREDITOR","BALANCE","HOME_PHONE","WORK_PHONE","OTHER_PHONE","ACCT_OTHTEL2","ACCT_OTHTEL3","ACCT_OTHTEL4","ACCT_OTHTEL5"
#"II ACCT/1103566666  ","P","SMITH           ","        SAMMY","7838 W 109TH ST APT 12        ","                              ","OVERLAND PARK       ","KS","66212","0000","G","20091110","20091110","19661216","NOLTTR","00000000","20091214","20091219","1000","03","000","00000000","000000000.00 ","00000000","0004","0003","0004","000","ACTIVE","20091110","0648","00"," ","C","HSBC                          ","000000692.09 ","9135551212","0000000000","0000000000","0000000000","0000000000","0000000000","0000000000"

			if ( ($format =~ /dccsv43/) && ($format_set < 1) )
				{
				$raw_number = $number;
				chomp($number);
				$number =~ s/,\"0\"/,/gi;
				$number =~ s/\t/\|/gi;
				$number =~ s/\'|\t|\r|\n|\l//gi;
				$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
				$number =~ s/\",,,,\"/\|\|\|\|/gi;
				$number =~ s/\",,,\"/\|\|\|/gi;
				$number =~ s/\",,\"/\|\|/gi;
				$number =~ s/\",\"/\|/gi;
				$number =~ s/\"//gi;
				$number =~ s/\|0000000000//gi;
				$number =~ s/          //gi;
				@m=@MT;
				@m = split(/\|/, $number);
				if ($DBX) {print "RAW: $#m-----$number\n";}

				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);		$vendor_lead_code =~ s/\s+$//gi;
					$vendor_lead_code =~s/II ACCT\///gi;
					$vendor_lead_code =~s/WDRF  //gi;
					while (length($vendor_lead_code) > 10) {chop($vendor_lead_code);}
				$source_id =			$m[0];		chomp($source_id);		$source_id =~ s/\s+$//gi;
				$list_id =				'929';
				$phone_code =			'1';
				$first_name =			$m[3];		chomp($first_name);		$first_name =~ s/^\s+|\s+$//gi;
				$middle_initial =		'';
				$last_name =			$m[2];		chomp($last_name);		$last_name =~ s/\s+$//gi;
				$phone_number =			$m[36];			$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[25];			# number of contacts
				$address1 =				$m[4];					$address1 =~ s/\s+$//gi;
				$address2 =				$m[5];					$address2 =~ s/\s+$//gi;
				$address3 =				$m[34];			# orig creditor
				$city =					$m[6];					$city =~ s/\s+$//gi;
				$state =				$m[7];
				$province =				$m[35];			$province =~ s/\s+$//gi;   # balance
				$postal_code =			$m[8];
				$country =				'';
				$gender =				'';
				$date_of_birth =		$m[13];
					$dobYYYY = substr($date_of_birth, 0, 4);
					$dobMM = substr($date_of_birth, 4, 2);
					$dobDD = substr($date_of_birth, 6, 2);
					$date_of_birth = "$dobYYYY-$dobMM-$dobDD";
				$alt_phone =			$m[37];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				$m[11];			# date placed
				$security_phrase =		''; # looked-up geographic CID will go here
				$comments =				"$m[16]|$m[21]";	# last worked/dialed
				$called_count =			'0';
				$status =				'NEW';
				$insert_date =			$pulldate0;
				$rank =					$m[26];			# number of times worked
				$owner =				$m[28];			# old status code
				$multi_alt_phones =		'';

				$r=0;
				$map_count=0;
				if ( (length($m[38]) > 9) && ($m[38] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[38];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					$g++;
					}
				if ( (length($m[39]) > 9) && ($m[39] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[39];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[40]) > 9) && ($m[40] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[40];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[41]) > 9) && ($m[41] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[41];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[42]) > 9) && ($m[42] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[42];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}

				### look up the custom CID to use for this state
				$stmtA = "SELECT cid from vicidial_custom_cid where state='$state';";
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
					if($DBX){print STDERR "\n$sthArows|$stmtA|\n";}
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$security_phrase = $aryA[0];
					}
				$sthA->finish();

				$format_set++;
				}

		# This is the format for the dccsvref51 lead files
#"BF_ID","RECORD_TYPE","LAST_NAME","FIRST_NAME","4ADDR1","5ADDR2","6CITY","STATE","ZIP","9ZIP4","ADDR_STATUS","DATE_PLACED","DATE_ADDED","DOB","LAST_LETTER","LAST_LETTER_DATE","LAST_WORKED","NEXT_ACTION_DATE","CAPTURE_CODE","CUR_CATEGORY","TIMES_DIALED","LAST_DIALED","TOTAL_PAID","DATE_LAST_PAID","NMBR_CALLS","NMBR_CONTACTS","NMBR_TIMES_WRKD","NMBR_LETTERS","STATUS_CODE","STATUS_DATE","SCORE","TIMES_TO_SERVICER","1ST-PMT-DEFAULT","TIME_ZONE","ORIG_CREDITOR","BALANCE","HOME_PHONE","WORK_PHONE","OTHER_PHONE","ACCT_OTHTEL2","ACCT_OTHTEL3","ACCT_OTHTEL4","ACCT_OTHTEL5"
#"II ACCT/1103566666  ","P","SMITH           ","        SAMMY","7838 W 109TH ST APT 12        ","                              ","OVERLAND PARK       ","KS","66212","0000","G","20091110","20091110","19661216","NOLTTR","00000000","20091214","20091219","1000","03","000","00000000","000000000.00 ","00000000","0004","0003","0004","000","ACTIVE","20091110","0648","00"," ","C","HSBC                          ","000000692.09 ","9135551212","0000000000","0000000000","0000000000","0000000000","0000000000","0000000000"

			if ( ($format =~ /dccsvref51/) && ($format_set < 1) )
				{
				$raw_number = $number;
				chomp($number);
				$number =~ s/,\"0\"/,/gi;
				$number =~ s/\t/\|/gi;
				$number =~ s/\'|\t|\r|\n|\l//gi;
				$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
				$number =~ s/\",,,,\"/\|\|\|\|/gi;
				$number =~ s/\",,,\"/\|\|\|/gi;
				$number =~ s/\",,\"/\|\|/gi;
				$number =~ s/\",\"/\|/gi;
				$number =~ s/\"//gi;
			#	$number =~ s/\|0000000000//gi;
				@m=@MT;
				@m = split(/\|/, $number);
				if ($DBX) {print "RAW: $#m-----$number\n";}

				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);		$vendor_lead_code =~ s/\s+$//gi;
					$vendor_lead_code =~s/II ACCT\///gi;
					$vendor_lead_code =~s/WDRF  //gi;
					while (length($vendor_lead_code) > 10) {chop($vendor_lead_code);}
				$source_id =			$m[0];		chomp($source_id);		$source_id =~ s/\s+$//gi;
				$list_id =				'929';
				$phone_code =			'1';
				$first_name =			$m[3];		chomp($first_name);		$first_name =~ s/^\s+|\s+$//gi;
				$middle_initial =		'';
				$last_name =			$m[2];		chomp($last_name);		$last_name =~ s/\s+$//gi;
				$phone_number =			$m[36];			$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[25];			# number of contacts
				$address1 =				$m[4];					$address1 =~ s/\s+$//gi;
				$address2 =				$m[5];					$address2 =~ s/\s+$//gi;
				$address3 =				$m[34];			# orig creditor
				$city =					$m[6];					$city =~ s/\s+$//gi;
				$state =				$m[7];
				$province =				$m[35];			$province =~ s/\s+$//gi;   # balance
				$postal_code =			$m[8];
				$country =				'';
				$gender =				'';
				$date_of_birth =		$m[13];
					$dobYYYY = substr($date_of_birth, 0, 4);
					$dobMM = substr($date_of_birth, 4, 2);
					$dobDD = substr($date_of_birth, 6, 2);
					$date_of_birth = "$dobYYYY-$dobMM-$dobDD";
				$alt_phone =			$m[37];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				$m[11];			# date placed
				$security_phrase =		''; # looked-up geographic CID will go here
				$comments =				$m[43];			# ref-name
				$called_count =			'0';
				$status =				'NEW';
				$insert_date =			$pulldate0;
				$rank =					$m[26];			# number of times worked
				$owner =				$m[28];			# old status code
				$multi_alt_phones =		'';

				$r=0;
				$map_count=0;
				if ( (length($m[38]) > 9) && ($m[38] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[38];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					$g++;
					}
				if ( (length($m[39]) > 9) && ($m[39] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[39];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[40]) > 9) && ($m[40] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[40];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[41]) > 9) && ($m[41] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[41];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[42]) > 9) && ($m[42] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[42];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}

				### look up the custom CID to use for this state
				$stmtA = "SELECT cid from vicidial_custom_cid where state='$state';";
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
					if($DBX){print STDERR "\n$sthArows|$stmtA|\n";}
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$security_phrase = $aryA[0];
					}
				$sthA->finish();

				$format_set++;
				}
		# This is the format for the dccsv52 lead files
#"BFRAME","RECORD_TYPE","LAST_NAME","FIRST_NAME","ADDR1","ADDR2","CITY","STATE","ZIP","ZIP4","ADDR_STATUS","DATE_PLACED","DATE_ADDED","DOB","LAST_LETTER","LAST_LETTER_DATE","LAST_WORKED","NEXT_ACTION_DATE","CAPTURE_CODE","CUR_CATEGORY","TIMES_DIALED","LAST_DIALED","TOTAL_PAID","DATE_LAST_PAID","NMBR_CALLS","NMBR_CONTACTS","NMBR_TIMES_WRKD","NMBR_LETTERS","STATUS_CODE","STATUS_DATE","SCORE","TIMES_TO_SERVICER","1ST-PMT-DEFAULT","TIME_ZONE","ORIG_CREDITOR","BALANCE","HOME_PHONE","WORK_PHONE","OTHER_PHONE","ACCT_OTHTEL2","ACCT_OTHTEL3","ACCT_OTHTEL4","ACCT_OTHTEL5","REF-NAME","REF-AD1","REF-AD2","REF-CITY","REF-ST","REF-POSTAL","REF-TEL1","REF-TEL2","SSN"
#"II ACCT/1103566666  ","P","SMITH           ","        SAMMY","7838 W 109TH ST APT 12        ","                              ","OVERLAND PARK       ","KS","66212","0000","G","20091110","20091110","19661216","NOLTTR","00000000","20091214","20091219","1000","03","000","00000000","000000000.00 ","00000000","0004","0003","0004","000","ACTIVE","20091110","0648","00"," ","C","HSBC                          ","000000692.09 ","9135551212","0000000000","0000000000","0000000000","0000000000","0000000000","0000000000","","","","","  ","          ","          ","          ","578888888"

			if ( ($format =~ /dccsv52/) && ($format_set < 1) )
				{
				$raw_number = $number;
				chomp($number);
				$number =~ s/,\"0\"/,/gi;
				$number =~ s/\t/\|/gi;
				$number =~ s/\'|\t|\r|\n|\l//gi;
				$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
				$number =~ s/\",,,,\"/\|\|\|\|/gi;
				$number =~ s/\",,,\"/\|\|\|/gi;
				$number =~ s/\",,\"/\|\|/gi;
				$number =~ s/\",\"/\|/gi;
				$number =~ s/\"//gi;
			#	$number =~ s/\|0000000000//gi;
				@m=@MT;
				@m = split(/\|/, $number);
				if ($DBX) {print "RAW: $#m-----$number\n";}

				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);		$vendor_lead_code =~ s/\s+$//gi;
					$vendor_lead_code =~s/II ACCT\///gi;
					$vendor_lead_code =~s/WDRF  //gi;
					while (length($vendor_lead_code) > 10) {chop($vendor_lead_code);}
				$source_id =			$m[0];		chomp($source_id);		$source_id =~ s/\s+$//gi;
				$list_id =				'929';
				$phone_code =			'1';
				$first_name =			$m[3];		chomp($first_name);		$first_name =~ s/^\s+|\s+$//gi;
				$middle_initial =		'';
				$last_name =			$m[2];		chomp($last_name);		$last_name =~ s/\s+$//gi;
				$title =				$m[25];			# number of contacts
				$address1 =				$m[4];					$address1 =~ s/\s+$//gi;
				$address2 =				$m[5];					$address2 =~ s/\s+$//gi;
				$address3 =				$m[34];			# orig creditor
				$city =					$m[6];					$city =~ s/\s+$//gi;
				$state =				$m[7];
				$province =				$m[35];			$province =~ s/\s+$//gi;   # balance
				$postal_code =			$m[8];
				$country =				'';
				$gender =				'';
				$date_of_birth =		$m[13];
					$dobYYYY = substr($date_of_birth, 0, 4);
					$dobMM = substr($date_of_birth, 4, 2);
					$dobDD = substr($date_of_birth, 6, 2);
					$date_of_birth = "$dobYYYY-$dobMM-$dobDD";
				$email =				$m[51];			# ssn
				$security_phrase =		''; # looked-up geographic CID will go here
				$comments =				$m[43];			# ref-name
				$called_count =			'0';
				$status =				'NEW';
				$insert_date =			$pulldate0;
				$rank =					$m[26];			# number of times worked
				$owner =				$m[28];			# old status code
				$multi_alt_phones =		'';

				$number =~ s/\|0000000000//gi;
				@m=@MT;
				@m = split(/\|/, $number);
				$m[36] =~ s/\D//gi; $m[37] =~ s/\D//gi; $m[38] =~ s/\D//gi; $m[39] =~ s/\D//gi; $m[40] =~ s/\D//gi; $m[41] =~ s/\D//gi; $m[42] =~ s/\D//gi;

				$phone_number =			$m[36];			$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$alt_phone='';
				if ( (length($m[37]) > 9) && ($m[37] !~ /000000000/) )
					{
					$alt_phone =			$m[37];		chomp($alt_phone);
					}

				$r=0;
				$map_count=0;
				if ( (length($m[38]) > 9) && ($m[38] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[38];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					$g++;
					}
				if ( (length($m[39]) > 9) && ($m[39] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[39];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[40]) > 9) && ($m[40] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[40];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[41]) > 9) && ($m[41] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[41];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}
				if ( (length($m[42]) > 9) && ($m[42] !~ /000000000/) )
					{
					$ALTm_phone_number[$r] =	$m[42];
					$ALTm_phone_code[$r] =		'1';
					$r++;	$map_count++;
					}

				### look up the custom CID to use for this state
				$stmtA = "SELECT cid from vicidial_custom_cid where state='$state';";
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
					if($DBX){print STDERR "\n$sthArows|$stmtA|\n";}
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$security_phrase = $aryA[0];
					}
				$sthA->finish();

				$format_set++;
				}



		# This is the format for the dccsvref52 lead files
#"BFRAME","RECORD_TYPE","LAST_NAME","FIRST_NAME","ADDR1","ADDR2","CITY","STATE","ZIP","ZIP4","ADDR_STATUS","DATE_PLACED","DATE_ADDED","DOB","LAST_LETTER","LAST_LETTER_DATE","LAST_WORKED","NEXT_ACTION_DATE","CAPTURE_CODE","CUR_CATEGORY","TIMES_DIALED","LAST_DIALED","TOTAL_PAID","DATE_LAST_PAID","NMBR_CALLS","NMBR_CONTACTS","NMBR_TIMES_WRKD","NMBR_LETTERS","STATUS_CODE","STATUS_DATE","SCORE","TIMES_TO_SERVICER","1ST-PMT-DEFAULT","TIME_ZONE","ORIG_CREDITOR","BALANCE","HOME_PHONE","WORK_PHONE","OTHER_PHONE","ACCT_OTHTEL2","ACCT_OTHTEL3","ACCT_OTHTEL4","ACCT_OTHTEL5","REF-NAME","REF-AD1","REF-AD2","REF-CITY","REF-ST","REF-POSTAL","REF-TEL1","REF-TEL2","SSN"
#"II ACCT/1103566666  ","P","SMITH           ","        SAMMY","7838 W 109TH ST APT 12        ","                              ","OVERLAND PARK       ","KS","66212","0000","G","20091110","20091110","19661216","NOLTTR","00000000","20091214","20091219","1000","03","000","00000000","000000000.00 ","00000000","0004","0003","0004","000","ACTIVE","20091110","0648","00"," ","C","HSBC                          ","000000692.09 ","9135551212","0000000000","0000000000","0000000000","0000000000","0000000000","0000000000","","","","","  ","          ","          ","          ","578888888"

			if ( ($format =~ /dccsvref52/) && ($format_set < 1) )
				{
				$raw_number = $number;
				chomp($number);
				$number =~ s/,\"0\"/,/gi;
				$number =~ s/\t/\|/gi;
				$number =~ s/\'|\t|\r|\n|\l//gi;
				$number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
				$number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
				$number =~ s/\",,,,\"/\|\|\|\|/gi;
				$number =~ s/\",,,\"/\|\|\|/gi;
				$number =~ s/\",,\"/\|\|/gi;
				$number =~ s/\",\"/\|/gi;
				$number =~ s/\"//gi;
			#	$number =~ s/\|0000000000//gi;
				@m=@MT;
				@m = split(/\|/, $number);
				if ($DBX) {print "RAW: $#m-----$number\n";}

				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);		$vendor_lead_code =~ s/\s+$//gi;
					$vendor_lead_code =~s/II ACCT\///gi;
					$vendor_lead_code =~s/WDRF  //gi;
					while (length($vendor_lead_code) > 10) {chop($vendor_lead_code);}
				$source_id =			$m[0];		chomp($source_id);		$source_id =~ s/\s+$//gi;
				$list_id =				'929';
				$phone_code =			'1';
				$first_name =			$m[3];		chomp($first_name);		$first_name =~ s/^\s+|\s+$//gi;
				$middle_initial =		'';
				$last_name =			$m[2];		chomp($last_name);		$last_name =~ s/\s+$//gi;
				$title =				$m[25];			# number of contacts
				$address1 =				$m[4];					$address1 =~ s/\s+$//gi;
				$address2 =				$m[5];					$address2 =~ s/\s+$//gi;
				$address3 =				$m[34];			# orig creditor
				$city =					$m[6];					$city =~ s/\s+$//gi;
				$state =				$m[7];
				$province =				$m[35];			$province =~ s/\s+$//gi;   # balance
				$postal_code =			$m[8];
				$country =				'';
				$gender =				'';
				$date_of_birth =		$m[13];
					$dobYYYY = substr($date_of_birth, 0, 4);
					$dobMM = substr($date_of_birth, 4, 2);
					$dobDD = substr($date_of_birth, 6, 2);
					$date_of_birth = "$dobYYYY-$dobMM-$dobDD";
				$email =				$m[51];			# ssn
				$security_phrase =		''; # looked-up geographic CID will go here
				$comments =				$m[43];			# ref-name
				$called_count =			'0';
				$status =				'NEW';
				$insert_date =			$pulldate0;
				$rank =					$m[26];			# number of times worked
				$owner =				$m[28];			# old status code
				$multi_alt_phones =		'';

				$phone_number =			$m[49];			$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$alt_phone='';
				if ( (length($m[50]) > 9) && ($m[50] !~ /000000000/) )
					{
					$alt_phone =			$m[37];		chomp($alt_phone);
					}

				### look up the custom CID to use for this state
				$stmtA = "SELECT cid from vicidial_custom_cid where state='$state';";
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
					if($DBX){print STDERR "\n$sthArows|$stmtA|\n";}
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$security_phrase = $aryA[0];
					}
				$sthA->finish();

				$format_set++;
				}

			# This is the format for the pipe30tz lead files
			# TEST01||09292011|1|5125554727||Mike||Frank|||||||||||||||||C|2145559922|TESTSURVEY|TESTSURVEY|111
			if ( ($format =~ /pipe30tz/) && ($format_set < 1) )
				{
				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);
				$source_id =			$m[2];		chomp($source_id);
				$phone_code =			$m[3];		chomp($phone_code);	$phone_code =~ s/\D//gi;
				$phone_number =			$m[4];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[5];		chomp($title);
				$first_name =			$m[6];		chomp($first_name);
				$middle_initial =		$m[7];		chomp($middle_initial);
				$last_name =			$m[8];		chomp($last_name);
		#		$address1 =				$m[9];		chomp($address1);
		#		$address2 =				$m[10];		chomp($address2);
		#		$address3 =				$m[11];		chomp($address3);
		#		$city =					$m[12];		chomp($city);
		#		$state =				$m[13];		chomp($state);
		#		$province =				$m[14];		chomp($province);
		#		$postal_code =			$m[15];		chomp($postal_code);
		#		$country =				$m[16];		chomp($country);
		#		$gender =				$m[17];
		#		$date_of_birth =		$m[18];
		#		$alt_phone =			$m[19];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
		#		$email =				$m[20];
		#		$security_phrase =		$m[21];
		#		$comments =				$m[22];
				$owner =				$m[25];
					if ($owner =~ /E/) {$owner='EST';}
					if ($owner =~ /C/) {$owner='CST';}
					if ($owner =~ /M/) {$owner='MST';}
					if ($owner =~ /P/) {$owner='PST';}
				$security_phrase =		$m[26];
				$province =				$m[27];
				$address3 =				$m[28];
				$alt_phone =			$m[29];

				$list_id=$new_list_id;
				$called_count=0;
				$status='NEW';
				$insert_date=$pulldate0;
				$map_count=0;

				$format_set++;
				}
		
		# This is the format that lists are downloaded in through the Vicidial Admin web interface
		# lead_id \t entry_date \t modify_date \t status \t user \t vendor_lead_code \t source_id \t list_id \t gmt_offset_now \t called_since_last_reset \t phone_code \t phone_number \t title \t first_name \t middle_initial \t last_name \t address1 \t address2 \t address3 \t city \t state \t province \t postal_code \t country_code \t gender \t date_of_birth \t alt_phone \t email \t security_phrase \t comments \t called_count \t last_local_call_time \t rank \t owner \t entry_list_id
			if ( ($format =~ /vicidl/) && ($format_set < 1) )
				{

				$raw_number = $number;
                                chomp($number);
                                $number =~ s/,\"0\"/,/gi;
                                $number =~ s/\t/\|/gi;
                                $number =~ s/\'|\t|\r|\n|\l//gi;
                                $number =~ s/\",,,,,,,\"/\|\|\|\|\|\|\|/gi;
                                $number =~ s/\",,,,,,\"/\|\|\|\|\|\|/gi;
                                $number =~ s/\",,,,,\"/\|\|\|\|\|/gi;
                                $number =~ s/\",,,,\"/\|\|\|\|/gi;
                                $number =~ s/\",,,\"/\|\|\|/gi;
                                $number =~ s/\",,\"/\|\|/gi;
                                $number =~ s/\",\"/\|/gi;
                                $number =~ s/\"//gi;
				$number =~ s/\\//gi;
                 #              $number =~ s/\|0000000000//gi;
                                $number =~ s/          //gi;
                                @m=@MT;
                                @m = split(/\|/, $number);
                                if ($DBX) {print "RAW: $#m-----$number\n";}


				$vendor_lead_code = 	$m[5];		chomp($vendor_lead_code);
                                $source_id = 		$m[6];		chomp($source_id);
                                $list_id = 		$m[7];		chomp($list_id);
                                $phone_code =		$m[10];		chomp($phone_code);	$phone_code =~ s/\D//gi;
                                $phone_number =		$m[11];		chomp($phone_number);	$phone_number =~ s/\D//gi;
                                $USarea =		substr($phone_number, 0, 3);
                                $title =		$m[12];		chomp($title);
                                $first_name =		$m[13];		chomp($first_name);
                                $middle_initial =	$m[14];		chomp($middle_initial);
                                $last_name =		$m[15];		chomp($last_name);
                                $address1 =		$m[16];		chomp($address1);
                                $address2 =		$m[17];		chomp($address2);
                                $address3 =		$m[18];		chomp($address3);
                                $city =			$m[19];		chomp($city);
                                $state =		$m[20];		chomp($state);
                                $province =		$m[21];		chomp($province);
                                $postal_code =		$m[22];		chomp($postal_code);
                                $country =		$m[23];		chomp($country);
                                $gender =		$m[24];
                                $date_of_birth =	$m[25];
                                $alt_phone =		$m[26];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
                                $email =		$m[27];
                                $security_phrase =	$m[28];
                                $comments =		$m[29];
                                $called_count =		$m[30];         $called_count =~ s/\D|\r|\n|\t//gi;	if (length($called_count)<1) {$called_count=0;}
                                $status =		$m[3];		$status =~ s/ |\r|\n|\t//gi;	if (length($status)<1) {$status='NEW';}
                                $insert_date =		$m[1];		$insert_date =~ s/\r|\n|\t|[a-zA-Z]//gi;	if (length($insert_date)<6) {$insert_date=$pulldate0;}
                                if ($insert_date =~ /\//)
                                        {
                                        @iD = split(/\//, $insert_date);
                                        $iD[0] = sprintf("%02d", $iD[0]);
                                        $iD[1] = sprintf("%02d", $iD[1]);
                                        $insert_date = "$iD[2]-$iD[0]-$iD[1]";
                                        }
				$format_set++;
				}

		# This is the format for the standard lead files
		#3857822|31022|105|01144|1625551212|MRS|B||BURTON|249 MUNDON ROAD|MALDON|ESSEX||||CM9 6PW|UK||||||COMMENTS
			if ($format_set < 1)
				{
				$vendor_lead_code =		$m[0];		chomp($vendor_lead_code);
				$source_id =			$m[1];		chomp($source_id);
				$list_id =				$m[2];		chomp($list_id);
				$phone_code =			$m[3];		chomp($phone_code);	$phone_code =~ s/\D//gi;
				$phone_number =			$m[4];		chomp($phone_number);	$phone_number =~ s/\D//gi;
					$USarea = 			substr($phone_number, 0, 3);
				$title =				$m[5];		chomp($title);
				$first_name =			$m[6];		chomp($first_name);
				$middle_initial =		$m[7];		chomp($middle_initial);
				$last_name =			$m[8];		chomp($last_name);
				$address1 =				$m[9];		chomp($address1);
				$address2 =				$m[10];		chomp($address2);
				$address3 =				$m[11];		chomp($address3);
				$city =					$m[12];		chomp($city);
				$state =				$m[13];		chomp($state);
				$province =				$m[14];		chomp($province);
				$postal_code =			$m[15];		chomp($postal_code);
				$country =				$m[16];		chomp($country);
				$gender =				$m[17];
				$date_of_birth =		$m[18];
				$alt_phone =			$m[19];		chomp($alt_phone);	$alt_phone =~ s/\D//gi;
				$email =				$m[20];
				$security_phrase =		$m[21];
				$comments =				$m[22];
				$called_count =			$m[23];		$called_count =~ s/\D|\r|\n|\t//gi; if (length($called_count)<1) {$called_count=0;}
				$status =				$m[24];		$status =~ s/ |\r|\n|\t//gi;  if (length($status)<1) {$status='NEW';}
				$insert_date =			$m[25];	$insert_date =~ s/\r|\n|\t|[a-zA-Z]//gi;  if (length($insert_date)<6) {$insert_date=$pulldate0;}
				if ($insert_date =~ /\//) 
					{
					@iD = split(/\//, $insert_date);
					$iD[0] = sprintf("%02d", $iD[0]);
					$iD[1] = sprintf("%02d", $iD[1]);
					$insert_date = "$iD[2]-$iD[0]-$iD[1]";
					}
				$multi_alt_phones =		$m[26];

				$map_count=0;
				if (length($multi_alt_phones)>4)
					{
					@map=@MT;  @ALTm_phone_code=@MT;  @ALTm_phone_number=@MT;  @ALTm_phone_note=@MT;
					@map = split(/\!/, $multi_alt_phones);
					$map_count = ($#map +1);
					if ($DBX) {print "multi-alt-entry: $a|$map_count|$multi_alt_phones\n";}
					$g++;
					$r=0;
					while ($r < $map_count)
						{
						@ncn=@MT;
						@ncn = split(/\_/, $map[$r]);
						print "$ncn[0]|$ncn[1]|$ncn[2]";

						if (length($forcephonecode) > 0)
							{$ALTm_phone_code[$r] =	$forcephonecode;}
						else
							{$ALTm_phone_code[$r] =		$ncn[1];}
						if (length($ALTm_phone_code[$r]) < 1)
							{$ALTm_phone_code[$r]='1';}
						$ALTm_phone_number[$r] =	$ncn[0];
						$ALTm_phone_note[$r] =		$ncn[2];
						$r++;
						}
					}
				}

			if (length($rank)<1) {$rank='0';}

			if (length($forcelistfilename_listid) > 0)
				{
				$list_id =	$forcelistfilename_listid;		# set list_id to filename override value
				}
			if (length($forcelistid) > 0)
				{
				$list_id =	$forcelistid;		# set list_id to override value
				}
			if (length($forcephonecode) > 0)
				{
				$phone_code =	$forcephonecode;	# set phone_code to override value
				}
			if ( ($dob_mmddyyy > 0) && ($date_of_birth =~ /\//) )
				{
				@iD = split(/\//, $date_of_birth);
				$iD[0] = sprintf("%02d", $iD[0]);
				$iD[1] = sprintf("%02d", $iD[1]);
				$date_of_birth = "$iD[2]-$iD[0]-$iD[1]";
				}
			if ( ($dob_ddmmyyy > 0) && ($date_of_birth =~ /\//) )
				{
				@iD = split(/\//, $date_of_birth);
				$iD[0] = sprintf("%02d", $iD[0]);
				$iD[1] = sprintf("%02d", $iD[1]);
				$date_of_birth = "$iD[2]-$iD[1]-$iD[0]";
				}

			if ($DBX) {print "$a|$phone_number\n";}


			$valid_lead=1;
			##### Check for valid USA and Canada prefix #####
			if ($usacan_prefix_check > 0)
				{
				$USprefix = 			substr($phone_number, 3, 1);
				if ($USprefix < 2)
					{
					$valid_lead = '0';
					if ($DBX) {print "     Invalid USACAN prefix 4th digit: |$USprefix|$phone_number|\n";}
					}
				}

			##### Check for valid USA and Canada areacodes #####
			if ( ($usacan_areacode_check > 0) && ($valid_lead > 0) )
				{
				$USarea = 			substr($phone_number, 0, 3);
				$stmtA = "SELECT count(*) from vicidial_phone_codes where areacode='$USarea' and country_code='1';";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$valid_lead = $aryA[0];
					if ($valid_lead < 1)
						{
						if ($DBX) {print "     Invalid USACAN areacode: |$USarea|$phone_number|\n";}
						}
					}
				$sthA->finish();
				}

			##### Check for valid NANPA USA and Canada areacodes and prefixes #####
			if ( ($nanpa_ac_prefix_check > 0) && ($valid_lead > 0) )
				{
				$USarea = 			substr($phone_number, 0, 3);
				$USprefix = 		substr($phone_number, 3, 3);
				$stmtA="SELECT count(*) from vicidial_nanpa_prefix_codes where areacode='$USarea' and prefix='$USprefix' limit 1;";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$valid_lead = $aryA[0];
					if ($valid_lead < 1)
						{
						if ($DBX) {print "     Invalid NANPA areacode-prefix: |$USarea|$USprefix|$phone_number|\n";}
						}
					}
				$sthA->finish();
				}

			##### Check for duplicate phone numbers in vicidial_list table entire database #####
			if ($dupchecksys > 0)
				{
				$dup_lead=0;
				$stmtA = "SELECT count(*) from vicidial_list where phone_number='$phone_number';";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$dup_lead = $aryA[0];
					$dup_lead_list=$list_id;
					}
				$sthA->finish();
				if ($dup_lead < 1)
					{
					if ($phone_list =~ /\|$phone_number$US$list_id\|/)
						{$dup_lead++;}
					}
				}
			##### Check for duplicate phone numbers in vicidial_list table for one list_id #####
			if ($dupcheck > 0)
				{
				$dup_lead=0;
				$stmtA = "SELECT list_id from vicidial_list where phone_number='$phone_number' and list_id='$list_id' limit 1;";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$dup_lead_list = $aryA[0];
					$dup_lead++;
					}
				$sthA->finish();
				if ($dup_lead < 1)
					{
					if ($phone_list =~ /\|$phone_number$US$list_id\|/)
						{$dup_lead++;}
					}
				}
			##### Check for duplicate phone numbers in vicidial_list table for all lists in a campaign #####
			if ($dupcheckcamp > 0)
				{
				$dup_lead=0;
				$dup_lists='';

				$stmtA = "SELECT count(*) from vicidial_lists where list_id='$list_id';";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
					@aryA = $sthA->fetchrow_array;
					$ci_recs = $aryA[0];
				$sthA->finish();
				if ($ci_recs > 0)
					{
					$stmtA = "SELECT campaign_id from vicidial_lists where list_id='$list_id';";
						if($DBX){print STDERR "\n|$stmtA|\n";}
					$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
					$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
						@aryA = $sthA->fetchrow_array;
						$dup_camp = $aryA[0];
					$sthA->finish();

					$stmtA = "SELECT list_id from vicidial_lists where campaign_id='$dup_camp';";
					$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
					$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
					$sthArows=$sthA->rows;
					$rec_count=0;
					while ($sthArows > $rec_count)
						{
						@aryA = $sthA->fetchrow_array;
						$dup_lists .=	"'$aryA[0]',";
						$rec_count++;
						}
					$sthA->finish();

					chop($dup_lists);
					$stmtA = "SELECT list_id from vicidial_list where phone_number='$phone_number' and list_id IN($dup_lists) limit 1;";
					$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
					$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
					$sthArows=$sthA->rows;
					$rec_count=0;
					while ($sthArows > $rec_count)
						{
						@aryA = $sthA->fetchrow_array;
						$dup_lead_list =	"'$aryA[0]',";
						$rec_count++;
						$dup_lead=1;
						}
					$sthA->finish();
					}
				if ($dup_lead < 1)
					{
					if ($phone_list =~ /\|$phone_number$US$list_id\|/)
						{$dup_lead++;}
					}
				}
			##### Check for duplicate title/alt-phone in vicidial_list table entire database #####
			if ($duptapchecksys > 0)
				{
				$dup_lead=0;
				$stmtA = "SELECT count(*) from vicidial_list where title='$title' and alt_phone='$alt_phone';";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$dup_lead = $aryA[0];
					$dup_lead_list=$list_id;
					}
				$sthA->finish();
				if ($dup_lead < 1)
					{
					if ($phone_list =~ /\|$alt_phone$title$US$list_id\|/)
						{$dup_lead++;}
					}
				}
			##### Check for duplicate title/alt-phone in vicidial_list table for one list_id #####
			if ($duptapchecklist > 0)
				{
				$dup_lead=0;
				$stmtA = "SELECT list_id from vicidial_list where title='$title' and alt_phone='$alt_phone' and list_id='$list_id' limit 1;";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$dup_lead_list = $aryA[0];
					$dup_lead++;
					}
				$sthA->finish();
				if ($dup_lead < 1)
					{
					if ($phone_list =~ /\|$alt_phone$title$US$list_id\|/)
						{$dup_lead++;}
					}
				}
			##### Check for duplicate phone with different title in vicidial_list table for one list_id, delete if different #####
			if ( ($dup_lead > 0) && ($duptnmdelete > 0) )
				{
				$tnm_delete=0;
				$tnm_lead_id='';
				$stmtA = "SELECT lead_id,title from vicidial_list where phone_number='$phone_number' and list_id='$list_id' limit 1;";
					if($DBX){print STDERR "\n|$stmtA|\n";}
				$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
				$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
				$sthArows=$sthA->rows;
				if ($sthArows > 0)
					{
					@aryA = $sthA->fetchrow_array;
					$tnm_lead_id =		$aryA[0];
					$tnm_title_check =	$aryA[1];
					if ( (length($tnm_title_check) > 0) && (length($title) > 0) && ($tnm_title_check !~ /^$title$/) )
						{$tnm_delete++;}
					}
				$sthA->finish();
				if ($tnm_delete > 0)
					{
					$stmtZ = "DELETE FROM vicidial_list where lead_id='$tnm_lead_id' limit 1;";
					if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
					if($DB){print STDERR "\n|$affected_rows|$tnm_title_check|$title|$tnm_lead_id|$list_id|$stmtZ|\n";}
					$tnm_delete_count++;
					}
				}

			##### final checks before inserting the lead
			if ( (length($phone_number)>6) && ($dup_lead < 1) && ($valid_lead > 0) )
				{
				if ( ($duptapchecklist > 0) || ($duptapchecksys > 0) )
					{$phone_list .= "$alt_phone$title$US$list_id|";}
				else
					{$phone_list .= "$phone_number$US$list_id|";}
				# set default values
				$modify_date =			"";
				$user =					"";
				$called_since_last_reset='N';
				$gmt_offset =			'0';

				if ($forcegmt > 0)
					{
					$gmt_offset =	$m[23];		# set GMT offset value to 24th field value
					}
				else
					{
					$postalgmt_found=0;
					if ( ($postalgmt > 0) && (length($postal_code)>4) )
						{
						if ($phone_code =~ /^1$/)
							{
							$stmtA = "SELECT postal_code,state,GMT_offset,DST,DST_range,country,country_code from vicidial_postal_codes where country_code='$phone_code' and postal_code LIKE \"$postal_code%\";";
								if($DBX){print STDERR "\n|$stmtA|\n";}
							$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
							$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
							$sthArows=$sthA->rows;
							$rec_count=0;
							while ($sthArows > $rec_count)
								{
								@aryA = $sthA->fetchrow_array;
								$gmt_offset =	$aryA[2];  $gmt_offset =~ s/\+| //gi;
								$dst =			$aryA[3];
								$dst_range =	$aryA[4];
								$PC_processed++;
								$rec_count++;
								$postalgmt_found++;
								if ($DBX) {print "     Postal GMT record found for $postal_code: |$gmt_offset|$dst|$dst_range|\n";}
								}
							$sthA->finish();
							}
						}
					if ( ($tzcodegmt > 0) && (length($owner)>1) )
						{
						$dst_range='';
						$dst='N';
						$gmt_offset=0;

						$stmtA="SELECT GMT_offset from vicidial_phone_codes where tz_code='$owner' and country_code='$phone_code' limit 1;";
							if($DBX){print STDERR "\n|$stmtA|\n";}
						$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
						$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
						$sthArows=$sthA->rows;
						if ($sthArows > 0)
							{
							@aryA = $sthA->fetchrow_array;
							$gmt_offset =	$aryA[0];  $gmt_offset =~ s/\+| //gi;
							$PC_processed++;
							$postalgmt_found++;
							}
						$sthA->finish();

						$stmtA = "SELECT distinct DST_range from vicidial_phone_codes where tz_code='$owner' and country_code='$phone_code' order by DST_range desc limit 1;";
							if($DBX){print STDERR "\n|$stmtA|\n";}
						$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
						$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
						$sthArows=$sthA->rows;
						if ($sthArows > 0)
							{
							@aryA = $sthA->fetchrow_array;
							$dst_range =	$aryA[0];
							if (length($dst_range)>2) {$dst = 'Y';}
							if ($DBX) {print "     TZcode GMT record found for $owner: |$gmt_offset|$dst|$dst_range|\n";}
							}
						$sthA->finish();
						}
					if ($nanpagmt > 0)
						{
						$dst_range='';
						$dst='N';
						$gmt_offset=0;
						$USarea = 			substr($phone_number, 0, 3);
						$USprefix = 		substr($phone_number, 3, 3);

						$stmtA="SELECT GMT_offset,DST from vicidial_nanpa_prefix_codes where areacode='$USarea' and prefix='$USprefix' limit 1;";
							if($DBX){print STDERR "\n|$stmtA|\n";}
						$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
						$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
						$sthArows=$sthA->rows;
						if ($sthArows > 0)
							{
							@aryA = $sthA->fetchrow_array;
							$gmt_offset =	$aryA[0];  $gmt_offset =~ s/\+| //gi;
							$dst =			$aryA[1];
							if ($dst =~ /Y/) 
								{$dst_range = 'SSM-FSN';}
							$PC_processed++;
							$postalgmt_found++;
							if ($DBX) {print "     NANPA AC-Prefix GMT record found for $USarea|$USprefix: |$gmt_offset|$dst|$dst_range|\n";}
							}
						$sthA->finish();
						}

					if ($postalgmt_found < 1)
						{
						$PC_processed=0;
						### UNITED STATES ###
						if ($phone_code =~ /^1$/)
							{
							$stmtA = "SELECT country_code,country,areacode,state,GMT_offset,DST,DST_range,geographic_description from vicidial_phone_codes where country_code='$phone_code' and areacode='$USarea';";
								if($DBX){print STDERR "\n|$stmtA|\n";}
							$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
							$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
							$sthArows=$sthA->rows;
							$rec_count=0;
							while ($sthArows > $rec_count)
								{
								@aryA = $sthA->fetchrow_array;
								$gmt_offset =	$aryA[4];  $gmt_offset =~ s/\+| //gi;
								$dst =			$aryA[5];
								$dst_range =	$aryA[6];
								$PC_processed++;
								$rec_count++;
								}
							$sthA->finish();
							}
						### MEXICO ###
						if ($phone_code =~ /^52$/)
							{
							$stmtA = "SELECT country_code,country,areacode,state,GMT_offset,DST,DST_range,geographic_description from vicidial_phone_codes where country_code='$phone_code' and areacode='$USarea';";
								if($DBX){print STDERR "\n|$stmtA|\n";}
							$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
							$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
							$sthArows=$sthA->rows;
							$rec_count=0;
							while ($sthArows > $rec_count)
								{
								@aryA = $sthA->fetchrow_array;
								$gmt_offset =	$aryA[4];  $gmt_offset =~ s/\+| //gi;
								$dst =			$aryA[5];
								$dst_range =	$aryA[6];
								$PC_processed++;
								$rec_count++;
								}
							$sthA->finish();
							}
						### AUSTRALIA ###
						if ($phone_code =~ /^61$/)
							{
							$stmtA = "SELECT country_code,country,areacode,state,GMT_offset,DST,DST_range,geographic_description from vicidial_phone_codes where country_code='$phone_code' and state='$state';";
								if($DBX){print STDERR "\n|$stmtA|\n";}
							$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
							$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
							$sthArows=$sthA->rows;
							$rec_count=0;
							while ($sthArows > $rec_count)
								{
								@aryA = $sthA->fetchrow_array;
								$gmt_offset =	$aryA[4];  $gmt_offset =~ s/\+| //gi;
								$dst =			$aryA[5];
								$dst_range =	$aryA[6];
								$PC_processed++;
								$rec_count++;
								}
							$sthA->finish();
							}
						### ALL OTHER COUNTRY CODES ###
						if (!$PC_processed)
							{
							$stmtA = "SELECT country_code,country,areacode,state,GMT_offset,DST,DST_range,geographic_description from vicidial_phone_codes where country_code='$phone_code';";
								if($DBX){print STDERR "\n|$stmtA|\n";}
							$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
							$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
							$sthArows=$sthA->rows;
							$rec_count=0;
							while ($sthArows > $rec_count)
								{
								@aryA = $sthA->fetchrow_array;
								$gmt_offset =	$aryA[4];  $gmt_offset =~ s/\+| //gi;
								$dst =			$aryA[5];
								$dst_range =	$aryA[6];
								$PC_processed++;
								$rec_count++;
								}
							$sthA->finish();
							}
						}

					### Find out if DST to raise the gmt offset ###
						$AC_GMT_diff = ($area_GMT - $LOCAL_GMT_OFF_STD);
						$AC_localtime = ($secX + (3600 * $AC_GMT_diff));
					($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($AC_localtime);
					$year = ($year + 1900);
					$mon++;
					if ($mon < 10) {$mon = "0$mon";}
					if ($mday < 10) {$mday = "0$mday";}
					if ($hour < 10) {$hour = "0$hour";}
					if ($min < 10) {$min = "0$min";}
					if ($sec < 10) {$sec = "0$sec";}
					$dsec = ( ( ($hour * 3600) + ($min * 60) ) + $sec );
					
					$AC_processed=0;

					if ( (!$AC_processed) && ($dst_range =~ /SSM-FSN/) )
						{
						if ($DBX) {print "     Second Sunday March to First Sunday November\n";}
						&USACAN_dstcalc;
						if ($DBX) {print "     DST: $USACAN_DST\n";}
						if ($USACAN_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /FSA-LSO/) )
						{
						if ($DBX) {print "     First Sunday April to Last Sunday October\n";}
						&NA_dstcalc;
						if ($DBX) {print "     DST: $NA_DST\n";}
						if ($NA_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /LSM-LSO/) )
						{
						if ($DBX) {print "     Last Sunday March to Last Sunday October\n";}
						&GBR_dstcalc;
						if ($DBX) {print "     DST: $GBR_DST\n";}
						if ($GBR_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /LSO-LSM/) )
						{
						if ($DBX) {print "     Last Sunday October to Last Sunday March\n";}
						&AUS_dstcalc;
						if ($DBX) {print "     DST: $AUS_DST\n";}
						if ($AUS_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /FSO-LSM/) )
						{
						if ($DBX) {print "     First Sunday October to Last Sunday March\n";}
						&AUST_dstcalc;
						if ($DBX) {print "     DST: $AUST_DST\n";}
						if ($AUST_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($area_GMT_method =~ /FSO-FSA/) )
						{
						if ($DBX) {print "     First Sunday October to First Sunday April\n";}
						&AUSE_dstcalc;
						if ($DBX) {print "     DST: $AUSE_DST\n";}
						if ($AUSE_DST) {$area_GMT++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /FSO-TSM/) )
						{
						if ($DBX) {print "     First Sunday October to Third Sunday March\n";}
						&NZL_dstcalc;
						if ($DBX) {print "     DST: $NZL_DST\n";}
						if ($NZL_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($area_GMT_method =~ /LSS-FSA/) )
						{
						if ($DBX) {print "     Last Sunday September to First Sunday April\n";}
						&NZLN_dstcalc;
						if ($DBX) {print "     DST: $NZLN_DST\n";}
						if ($NZLN_DST) {$area_GMT++;}
						$AC_processed++;
						}
					if ( (!$AC_processed) && ($dst_range =~ /TSO-LSF/) )
						{
						if ($DBX) {print "     Third Sunday October to Last Sunday February\n";}
						&BZL_dstcalc;
						if ($DBX) {print "     DST: $BZL_DST\n";}
						if ($BZL_DST) {$gmt_offset++;}
						$AC_processed++;
						}
					if (!$AC_processed)
						{
						if ($DBX) {print "     No DST Method Found\n";}
						if ($DBX) {print "     DST: 0\n";}
						$AC_processed++;
						}

					}


				if ($map_count > 0)
					{
					$stmtZ = "INSERT INTO vicidial_list (lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner) values('',\"$insert_date\",\"$modify_date\",\"$status\",\"$user\",\"$vendor_lead_code\",\"$source_id\",\"$list_id\",\"$gmt_offset\",\"$called_since_last_reset\",\"$phone_code\",\"$phone_number\",\"$title\",\"$first_name\",\"$middle_initial\",\"$last_name\",\"$address1\",\"$address2\",\"$address3\",\"$city\",\"$state\",\"$province\",\"$postal_code\",\"$country\",\"$gender\",\"$date_of_birth\",\"$alt_phone\",\"$email\",\"$security_phrase\",\"$comments\",\"$called_count\",'2008-01-01 00:00:00',\"$rank\",\"$owner\");";
						if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
						$lead_id = $dbhA->{'mysql_insertid'};
						if($DB){print STDERR "\n|$affected_rows|$stmtZ|\n";}
						$c++;

					$r=0; $s=0;
					while ($r < $map_count)
						{
						$s++;
						$stmtZ = "INSERT INTO vicidial_list_alt_phones (lead_id,phone_code,phone_number,alt_phone_note,alt_phone_count) values('$lead_id','$ALTm_phone_code[$r]','$ALTm_phone_number[$r]','$ALTm_phone_note[$r]','$s');";
							if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
						#	$alt_phone_id = $dbhA->{'mysql_insertid'};
							if($DB){print STDERR "\n|$affected_rows|$stmtZ|\n";}
						$r++;
						}
					}
				else
					{
					if ($multi_insert_counter > 8)
						{
						### insert good lead into pending_transactions table ###
						$stmtZ = "INSERT INTO vicidial_list (lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner) values$multistmt('',\"$insert_date\",\"$modify_date\",\"$status\",\"$user\",\"$vendor_lead_code\",\"$source_id\",\"$list_id\",\"$gmt_offset\",\"$called_since_last_reset\",\"$phone_code\",\"$phone_number\",\"$title\",\"$first_name\",\"$middle_initial\",\"$last_name\",\"$address1\",\"$address2\",\"$address3\",\"$city\",\"$state\",\"$province\",\"$postal_code\",\"$country\",\"$gender\",\"$date_of_birth\",\"$alt_phone\",\"$email\",\"$security_phrase\",\"$comments\",\"$called_count\",'2008-01-01 00:00:00',\"$rank\",\"$owner\");";
							if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
							if($DB){print STDERR "\n|$affected_rows|$stmtZ|\n";}

						$multistmt='';
						$multi_insert_counter=0;
						$c++;
						}
					else
						{
						$multistmt .= "('',\"$insert_date\",\"$modify_date\",\"$status\",\"$user\",\"$vendor_lead_code\",\"$source_id\",\"$list_id\",\"$gmt_offset\",\"$called_since_last_reset\",\"$phone_code\",\"$phone_number\",\"$title\",\"$first_name\",\"$middle_initial\",\"$last_name\",\"$address1\",\"$address2\",\"$address3\",\"$city\",\"$state\",\"$province\",\"$postal_code\",\"$country\",\"$gender\",\"$date_of_birth\",\"$alt_phone\",\"$email\",\"$security_phrase\",\"$comments\",\"$called_count\",'2008-01-01 00:00:00',\"$rank\",\"$owner\"),";
						$multi_insert_counter++;
						}
					}
				$b++;
				}
			else
				{
				if ($valid_lead < 1)
					{if ($q < 1) {print "INVALID: $phone_number|$list_id|$dup_lead_list|$a|$title $alt_phone\n";}   $h++;}
				else
					{
					if ($dup_lead > 0)
						{if ($q < 1) {print "DUPLICATE: $phone_number|$list_id|$dup_lead_list|$a|$title $alt_phone\n";}   $f++;}
					else
						{if ($q < 1) {print "BAD Home_Phone: $phone_number|$vendor_id|$a\n";}   $e++;}
					}
				}
			
			$a++;

			if ($q < 1) 
				{
				if ($a =~ /100$/i) {print STDERR "0     $a\r";}
				if ($a =~ /200$/i) {print STDERR "+     $a\r";}
				if ($a =~ /300$/i) {print STDERR "|     $a\r";}
				if ($a =~ /400$/i) {print STDERR "\\     $a\r";}
				if ($a =~ /500$/i) {print STDERR "-     $a\r";}
				if ($a =~ /600$/i) {print STDERR "/     $a\r";}
				if ($a =~ /700$/i) {print STDERR "|     $a\r";}
				if ($a =~ /800$/i) {print STDERR "+     $a\r";}
				if ($a =~ /900$/i) {print STDERR "0     $a\r";}
				if ($a =~ /000$/i) {print "$a|$b|$c|$d|$e|$f|$g|$h|$phone_number|\n";}
				}

			}

		if (length($multistmt) > 10)
			{
			chop($multistmt);
			### insert good deal into pending_transactions table ###
			$stmtZ = "INSERT INTO vicidial_list (lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner) values$multistmt;";
					if (!$T) {$affected_rows = $dbhA->do($stmtZ); } #  or die  "Couldn't execute query: |$stmtZ|\n";
					if($DB){print STDERR "\n|$affected_rows|$stmtZ|\n";}

			$multistmt='';
			$multi_insert_counter=0;
			$c++;
			}


			### open the stats out file for writing ###
			open(Sout, ">>$VDHLOGfile")
					|| die "Can't open $VDHLOGfile: $!\n";


			### close file handler and DB connections ###
			$Falert  = "\n\nTOTALS FOR $FILEname:\n";
			if ($new_list_for_each_file > 0)
				{$Falert .= "New List ID:        $new_list_id\n";}
			$Falert .= "Lines in lead file: $a\n";
			$Falert .= "INSERTED:           $b\n";
			$Falert .= "INSERT STATEMENTS:  $c\n";
			$Falert .= "ERROR:              $e\n";
			$Falert .= "MULTI-ALT-PHONE:    $g\n";
			if ($f > 0)
				{$Falert .= "DUPLICATES:         $f\n";}
			if ($h > 0)
				{$Falert .= "INVALID PHONES:     $h\n";}
			if ($tnm_delete_count > 0)
				{$Falert .= "TNM DELETE COUNT:   $tnm_delete_count\n";}

			if ($q < 1) {print "$Falert";}
			print Sout "$Falert";
			$Ealert .= "$Falert";

			close(infile);
			close(Sout);
			chmod 0777, "$VDHLOGfile";

			### Move file to the DONE directory locally
			if (!$T) {`mv -f $dir1/$FILEname $dir2/$FILEname`;}

			}
		}
	$i++;
	}


$dbhA->disconnect();

### calculate time to run script ###
$secY = time();
$secZ = ($secY - $secX);
$secZm = ($secZ /60);

if ($q < 1)
	{
	print "script execution time in seconds: $secZ     minutes: $secZm\n";
	}

###### EMAIL SECTION

if ( (length($Ealert)>5) && (length($email_list) > 3) )
	{
	if ($q < 1) {print "Sending email: $email_list\n";}

	use MIME::QuotedPrint;
	use MIME::Base64;
	use Mail::Sendmail;

	$mailsubject = "VICIDIAL LEAD FILE LOAD $pulldate0";

	  %mail = ( To      => "$email_list",
							From    => "$email_sender",
							Subject => "$mailsubject",
							Message => "VICIDIAL LEAD FILE LOAD $pulldate0\n\n$Ealert\n"
					   );
			sendmail(%mail) or die $mail::Sendmail::error;
		   if ($q < 1) {print "ok. log says:\n", $mail::sendmail::log;}  ### print mail log for status
	}

exit;







sub USACAN_dstcalc {
#**********************************************************************
# SSM-FSN
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on Second Sunday March to First Sunday November at 2 am.
#     INPUTS:
#       mm              INTEGER       Month.
#       dd              INTEGER       Day of the month.
#       ns              INTEGER       Seconds into the day.
#       dow             INTEGER       Day of week (0=Sunday, to 6=Saturday)
#     OPTIONAL INPUT:
#       timezone        INTEGER       hour difference UTC - local standard time
#                                      (DEFAULT is blank)
#                                     make calculations based on UTC time, 
#                                     which means shift at 10:00 UTC in April
#                                     and 9:00 UTC in October
#     OUTPUT: 
#                       INTEGER       1 = DST, 0 = not DST
#
# S  M  T  W  T  F  S
# 1  2  3  4  5  6  7
# 8  9 10 11 12 13 14
#15 16 17 18 19 20 21
#22 23 24 25 26 27 28
#29 30 31
# 
# S  M  T  W  T  F  S
#    1  2  3  4  5  6
# 7  8  9 10 11 12 13
#14 15 16 17 18 19 20
#21 22 23 24 25 26 27
#28 29 30 31
# 
#**********************************************************************

	$USACAN_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 3 || $mm > 11) {
	$USACAN_DST=0;   return 0;
    } elsif ($mm >= 4 && $mm <= 10) {
	$USACAN_DST=1;   return 1;
    } elsif ($mm == 3) {
	if ($dd > 13) {
	    $USACAN_DST=1;   return 1;
	} elsif ($dd >= ($dow+8)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (7200+$timezone*3600)) {
		    $USACAN_DST=0;   return 0;
		} else {
		    $USACAN_DST=1;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 7200) {
		    $USACAN_DST=0;   return 0;
		} else {
		    $USACAN_DST=1;   return 1;
		}
	    }
	} else {
	    $USACAN_DST=0;   return 0;
	}
    } elsif ($mm == 11) {
	if ($dd > 7) {
	    $USACAN_DST=0;   return 0;
	} elsif ($dd < ($dow+1)) {
	    $USACAN_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (7200+($timezone-1)*3600)) {
		    $USACAN_DST=1;   return 1;
		} else {
		    $USACAN_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 7200) {
		    $USACAN_DST=1;   return 1;
		} else {
		    $USACAN_DST=0;   return 0;
		}
	    }
	} else {
	    $USACAN_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc




sub NA_dstcalc {
#**********************************************************************
# FSA-LSO
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on first Sunday in April and last Sunday in October at 2 am.
#**********************************************************************
    
	$NA_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 4 || $mm > 10) {
	$NA_DST=0;   return 0;
    } elsif ($mm >= 5 && $mm <= 9) {
	$NA_DST=1;   return 1;
    } elsif ($mm == 4) {
	if ($dd > 7) {
	    $NA_DST=1;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (7200+$timezone*3600)) {
		    $NA_DST=0;   return 0;
		} else {
		    $NA_DST=1;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 7200) {
		    $NA_DST=0;   return 0;
		} else {
		    $NA_DST=1;   return 1;
		}
	    }
	} else {
	    $NA_DST=0;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd < 25) {
	    $NA_DST=1;   return 1;
	} elsif ($dd < ($dow+25)) {
	    $NA_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (7200+($timezone-1)*3600)) {
		    $NA_DST=1;   return 1;
		} else {
		    $NA_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 7200) {
		    $NA_DST=1;   return 1;
		} else {
		    $NA_DST=0;   return 0;
		}
	    }
	} else {
	    $NA_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc




sub GBR_dstcalc {
#**********************************************************************
# LSM-LSO
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on last Sunday in March and last Sunday in October at 1 am.
#**********************************************************************
    
	$GBR_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 3 || $mm > 10) {
	$GBR_DST=0;   return 0;
    } elsif ($mm >= 4 && $mm <= 9) {
	$GBR_DST=1;   return 1;
    } elsif ($mm == 3) {
	if ($dd < 25) {
	    $GBR_DST=0;   return 0;
	} elsif ($dd < ($dow+25)) {
	    $GBR_DST=0;   return 0;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $GBR_DST=0;   return 0;
		} else {
		    $GBR_DST=1;   return 1;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $GBR_DST=0;   return 0;
		} else {
		    $GBR_DST=1;   return 1;
		}
	    }
	} else {
	    $GBR_DST=1;   return 1;
	}
    } elsif ($mm == 10) {
	if ($dd < 25) {
	    $GBR_DST=1;   return 1;
	} elsif ($dd < ($dow+25)) {
	    $GBR_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $GBR_DST=1;   return 1;
		} else {
		    $GBR_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $GBR_DST=1;   return 1;
		} else {
		    $GBR_DST=0;   return 0;
		}
	    }
	} else {
	    $GBR_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc




sub AUS_dstcalc {
#**********************************************************************
# LSO-LSM
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on last Sunday in October and last Sunday in March at 1 am.
#**********************************************************************
    
	$AUS_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 3 || $mm > 10) {
	$AUS_DST=1;   return 1;
    } elsif ($mm >= 4 && $mm <= 9) {
	$AUS_DST=0;   return 0;
    } elsif ($mm == 3) {
	if ($dd < 25) {
	    $AUS_DST=1;   return 1;
	} elsif ($dd < ($dow+25)) {
	    $AUS_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $AUS_DST=1;   return 1;
		} else {
		    $AUS_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $AUS_DST=1;   return 1;
		} else {
		    $AUS_DST=0;   return 0;
		}
	    }
	} else {
	    $AUS_DST=0;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd < 25) {
	    $AUS_DST=0;   return 0;
	} elsif ($dd < ($dow+25)) {
	    $AUS_DST=0;   return 0;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $AUS_DST=0;   return 0;
		} else {
		    $AUS_DST=1;   return 1;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $AUS_DST=0;   return 0;
		} else {
		    $AUS_DST=1;   return 1;
		}
	    }
	} else {
	    $AUS_DST=1;   return 1;
	}
    } # end of month checks
} # end of subroutine dstcalc





sub AUST_dstcalc {
#**********************************************************************
# FSO-LSM
#   TASMANIA ONLY
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on first Sunday in October and last Sunday in March at 1 am.
#**********************************************************************
    
	$AUST_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 3 || $mm > 10) {
	$AUST_DST=1;   return 1;
    } elsif ($mm >= 4 && $mm <= 9) {
	$AUST_DST=0;   return 0;
    } elsif ($mm == 3) {
	if ($dd < 25) {
	    $AUST_DST=1;   return 1;
	} elsif ($dd < ($dow+25)) {
	    $AUST_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $AUST_DST=1;   return 1;
		} else {
		    $AUST_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $AUST_DST=1;   return 1;
		} else {
		    $AUST_DST=0;   return 0;
		}
	    }
	} else {
	    $AUST_DST=0;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd >= 8) {
	    $AUST_DST=1;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (7200+$timezone*3600)) {
		    $AUST_DST=0;   return 0;
		} else {
		    $AUST_DST=1;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 3600) {
		    $AUST_DST=0;   return 0;
		} else {
		    $AUST_DST=1;   return 1;
		}
	    }
	} else {
	    $AUST_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc





sub AUSE_dstcalc {
#**********************************************************************
# FSO-FSA
#   2008+ AUSTRALIA ONLY (country code 61)
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on first Sunday in October and first Sunday in April at 1 am.
#**********************************************************************
    
	$AUSE_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 4 || $mm > 10) {
	$AUSE_DST=1;   return 1;
    } elsif ($mm >= 5 && $mm <= 9) {
	$AUSE_DST=0;   return 0;
    } elsif ($mm == 4) {
	if ($dd > 7) {
	    $AUSE_DST=0;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (3600+$timezone*3600)) {
		    $AUSE_DST=1;   return 0;
		} else {
		    $AUSE_DST=0;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 7200) {
		    $AUSE_DST=1;   return 0;
		} else {
		    $AUSE_DST=0;   return 1;
		}
	    }
	} else {
	    $AUSE_DST=1;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd >= 8) {
	    $AUSE_DST=1;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (7200+$timezone*3600)) {
		    $AUSE_DST=0;   return 0;
		} else {
		    $AUSE_DST=1;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 3600) {
		    $AUSE_DST=0;   return 0;
		} else {
		    $AUSE_DST=1;   return 1;
		}
	    }
	} else {
	    $AUSE_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc





sub NZL_dstcalc {
#**********************************************************************
# FSO-TSM
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on first Sunday in October and third Sunday in March at 1 am.
#**********************************************************************
    
	$NZL_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 3 || $mm > 10) {
	$NZL_DST=1;   return 1;
    } elsif ($mm >= 4 && $mm <= 9) {
	$NZL_DST=0;   return 0;
    } elsif ($mm == 3) {
	if ($dd < 14) {
	    $NZL_DST=1;   return 1;
	} elsif ($dd < ($dow+14)) {
	    $NZL_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $NZL_DST=1;   return 1;
		} else {
		    $NZL_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $NZL_DST=1;   return 1;
		} else {
		    $NZL_DST=0;   return 0;
		}
	    }
	} else {
	    $NZL_DST=0;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd >= 8) {
	    $NZL_DST=1;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (7200+$timezone*3600)) {
		    $NZL_DST=0;   return 0;
		} else {
		    $NZL_DST=1;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 3600) {
		    $NZL_DST=0;   return 0;
		} else {
		    $NZL_DST=1;   return 1;
		}
	    }
	} else {
	    $NZL_DST=0;   return 0;
	}
    } # end of month checks
} # end of subroutine dstcalc




sub NZLN_dstcalc {
#**********************************************************************
# LSS-FSA
#   2007+ NEW ZEALAND (country code 64)
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect.
#     Based on last Sunday in September and first Sunday in April at 1 am.
#**********************************************************************
    
	$NZLN_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 4 || $mm > 9) {
	$NZLN_DST=1;   return 1;
    } elsif ($mm >= 5 && $mm <= 9) {
	$NZLN_DST=0;   return 0;
    } elsif ($mm == 4) {
	if ($dd > 7) {
	    $NZLN_DST=0;   return 1;
	} elsif ($dd >= ($dow+1)) {
	    if ($timezone) {
		if ($dow == 0 && $ns < (3600+$timezone*3600)) {
		    $NZLN_DST=1;   return 0;
		} else {
		    $NZLN_DST=0;   return 1;
		}
	    } else {
		if ($dow == 0 && $ns < 7200) {
		    $NZLN_DST=1;   return 0;
		} else {
		    $NZLN_DST=0;   return 1;
		}
	    }
	} else {
	    $NZLN_DST=1;   return 0;
	}
    } elsif ($mm == 9) {
	if ($dd < 25) {
	    $NZLN_DST=0;   return 0;
	} elsif ($dd < ($dow+25)) {
	    $NZLN_DST=0;   return 0;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $NZLN_DST=0;   return 0;
		} else {
		    $NZLN_DST=1;   return 1;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $NZLN_DST=0;   return 0;
		} else {
		    $NZLN_DST=1;   return 1;
		}
	    }
	} else {
	    $NZLN_DST=1;   return 1;
	}
    } # end of month checks
} # end of subroutine dstcalc





sub BZL_dstcalc {
#**********************************************************************
# TSO-LSF
#     This is returns 1 if Daylight Savings Time is in effect and 0 if 
#       Standard time is in effect. Brazil
#     Based on Third Sunday October to Last Sunday February at 1 am.
#**********************************************************************
    
	$BZL_DST=0;
	$mm = $mon;
	$dd = $mday;
	$ns = $dsec;
	$dow= $wday;

    if ($mm < 2 || $mm > 10) {
	$BZL_DST=1;   return 1;
    } elsif ($mm >= 3 && $mm <= 9) {
	$BZL_DST=0;   return 0;
    } elsif ($mm == 2) {
	if ($dd < 22) {
	    $BZL_DST=1;   return 1;
	} elsif ($dd < ($dow+22)) {
	    $BZL_DST=1;   return 1;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $BZL_DST=1;   return 1;
		} else {
		    $BZL_DST=0;   return 0;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $BZL_DST=1;   return 1;
		} else {
		    $BZL_DST=0;   return 0;
		}
	    }
	} else {
	    $BZL_DST=0;   return 0;
	}
    } elsif ($mm == 10) {
	if ($dd < 22) {
	    $BZL_DST=0;   return 0;
	} elsif ($dd < ($dow+22)) {
	    $BZL_DST=0;   return 0;
	} elsif ($dow == 0) {
	    if ($timezone) { # UTC calculations
		if ($ns < (3600+($timezone-1)*3600)) {
		    $BZL_DST=0;   return 0;
		} else {
		    $BZL_DST=1;   return 1;
		}
	    } else { # local time calculations
		if ($ns < 3600) {
		    $BZL_DST=0;   return 0;
		} else {
		    $BZL_DST=1;   return 1;
		}
	    }
	} else {
	    $BZL_DST=1;   return 1;
	}
    } # end of month checks
} # end of subroutine dstcalc
