[snip]
I am familiar with the NACHA file format, and have the specifications, I
just would rather not write the file formatter and output if someone
else
has done it already or could do it more easily than I.
Do you have experience in this?
[/snip]
Ah. Yes I do have experience in this and have done it several times over
the years. I have never made the code public typically because I would
have to edit sensitive information out of it.
Here is an example, edited with comments about where stuff should occur,
since this has been edited it has not been tested and may require
significant modification to work in any case (it includes being able to
keep a log);
/*
* includes
*/
include("your database connection");
/*
* create the ACH transfer request records to be transmitted
* each batch must contain
* file header record
* company/batch header record
* detail record(s)
* addenda record(s) OPTIONAL
* company/batch control record
* file control record
*
* all alphanumeric and alpha characters must be upper case with fields
left justified, and blank filled
* all numeric fields must be right justified, unsigned, and zero filled
*
* detail records will be created first so that appropriate details can
be gathered for header and control records
*
*/
/*
* entry detail records
* this is requires a loop
* we will loop through each dealer's daily records for the day in
question and sum the dollar amount that they owe us.
* we will have one detail record for each dealer each day that has
a total > 0
* we will then get their banking info and create the detail record
* if there is no banking info we will not run a detail record
*/
$getDealerAcct = "your query for account info ";
if(!($dbDealerAcct = mysql_query($getDealerAcct, $dbc))){
echo mysql_error();
exit();
}
/* get number of rows */
if(0 != mysql_num)
/* loop through detail records */
$detailRecCount = 0; //initialize record count
$detailHash = 0; //initialize detail hash
$totBatchDollar = 0; //initialize batch dollars
$totBlockCount = 0; //initailize block count
$totEntryRecord = 0; //initailize block count
$dollar = ''; // initialize amount
$startDate = date("Y-m-d", strtotime("-2days")); //The start date
should be two days ago
if($_GET['date']!=''){
$startDate = $_GET['date'];
}
$endDate = $startDate; //The end date should be the same as the start
date
$txtLog = "Dealer Code\tDealer Name\tAmount\n"; // header code for log
file
$logLine = ""; //initializing line
while($dealerAcct = mysql_fetch_array($dbDealerAcct)){
//20060906jc Here let's find the correct dollar amount for what we are
looking for
$dealerCode = $dealerAcct['dealerCode'];
$logLine.= $dealerCode."\t";
$sqlGetName = "get account name";
if(!($dbGetName = mysql_query($sqlGetName, $dbc))){
echo "Error Getting
Name!(".mysql_errno()."):".mysql_error()."<br />\n";
echo $sqlGetName."<br />\n";
exit();
}
$arrGetName = mysql_fetch_assoc($dbGetName);
$dealerName = $arrGetName['dealerDBA'];
if($dealerName==""){
$dealerName="[none given]";
}
$logLine.= $dealerName."\t";
$sqlGetMoola = "query to get amount associated with account ";
if(!($dbGetMoola = mysql_query($sqlGetMoola, $dbc))){
echo "Error Getting tha
Moola!(".mysql_errno()."):".mysql_error()."<br />\n";
echo $sqlGetMoola."<br />\n";
exit();
}
$dollar = 0;
$index = 0;
$intGetMoola = mysql_num_rows($dbGetMoola);
while($index < $intGetMoola){
$arrGetMoola = mysql_fetch_array($dbGetMoola);
$dollar = $dollar + $arrGetMoola['amount'];
$index++;
}
$logLine.= $dollar."\n";
//echo $logLine;
if($dollar!=0){
$txtLog.=$logLine;
}
$logLine = "";
$dollar = $dollar * 100;
if($dollar != 0){
$detailRecord .= '6';
$detailRecord .= '27'; // transaction code
$detailRecord .= substr($dealerAcct['dealerABARoute'], 0,
8); // ABA routing number (first eight characters)
$detailRecord .= substr($dealerAcct['dealerABARoute'], 8,
1); // routing number check digit (ninth character in routing number)
$detailRecord .= str_pad($dealerAcct['dealerABAAccount'],
17, " ", STR_PAD_RIGHT); // account number, left justify blank fill
$detailRecord .= str_pad($dollar, 10, "0", STR_PAD_LEFT);
// amount
$detailRecord .= str_pad('', 15, " ", STR_PAD_RIGHT); //
individual id number
$detailRecord .=
str_pad(substr(strtoupper($dealerAcct['dealerDBA']), 0, 22), 22, " ",
STR_PAD_RIGHT); // individual name
$detailRecord .= str_pad('', 2, " ", STR_PAD_RIGHT); //
BoA draft indicator
$detailRecord .= '0'; // addenda indicator
$detailRecord .= str_pad('', 15, "0", STR_PAD_LEFT);
//Trace Number
$detailRecord .= "\n"; // end of line
$detailRecCount++;
$detailHash = $detailHash +
substr($dealerAcct['dealerABARoute'], 0, 8);
$totBatchDollar = $totBatchDollar + $dollar;
$totBlockCount++;
$totEntryRecord++;
}
} /* end detail record loop */
/* file header record */
$fileHeader = ''; // these fields as required
$fileHeader .= '';
$fileHeader .= ''; // immediate destination
$fileHeader .= ''; // immediate origin
$fileHeader .= date("ymd");
$fileHeader .= date("Hi");
$fileHeader .= 'A'; // we may need to sequence this if more
than one is sent per day
$fileHeader .= '';
$fileHeader .= '10'; // number of records per block???
$fileHeader .= '1';
$fileHeader .= str_pad('BANK NAME', 23, " ", STR_PAD_RIGHT); //
destination
$fileHeader .= str_pad('ORIGIN NAME', 23, " ", STR_PAD_RIGHT);
// origin
$fileHeader .= str_pad('', 8, " "); // reference code
$totBlockCount++;
/* company/batch header record */
$batchHeader = '';
$batchHeader .= ''; // service class code
$batchHeader .= '';
$batchHeader .= str_pad('', 20, " ", STR_PAD_RIGHT); //
discretionary data
$batchHeader .= '';
$batchHeader .= '';
$batchHeader .= '';
$batchHeader .= date("ymd");
$batchHeader .= date("ymd", strtotime("tomorrow"));
$batchHeader .= str_pad('', 3, " ", STR_PAD_RIGHT); //
settlement date
$batchHeader .= '';
$batchHeader .= '';
$batchHeader .= ''; //batch sequence
$totBlockCount++;
/* company/batch control record */
$batchControl = '';
$batchControl .= '';
$batchControl .= str_pad($detailRecCount, 6, "0", STR_PAD_LEFT);
// entry + addneda record count
$batchControl .= str_pad(substr($detailHash, -10, 10), 10, "0",
STR_PAD_LEFT);// entry hash - sum of ABA routing number 8 digits, length
of 10, drop 2 left most digits
$batchControl .= str_pad($totBatchDollar, 12, "0", STR_PAD_LEFT);
// total dollar amount, no decimal point
$batchControl .= str_pad('', 12, "0", STR_PAD_LEFT); // total
credit amount, no decimal point
$batchControl .= '';
$batchControl .= str_pad('', 19, " ", STR_PAD_RIGHT); // message
authentication code
$batchControl .= str_pad('', 6, " ", STR_PAD_RIGHT); // reserved
$batchControl .= ''; // originating DFI
$batchControl .= ''; //batch sequence
$totBlockCount++;
/* file control record */
$totBlockCount++;
$fileControl = '';
$fileControl .= str_pad('', 6, "0", STR_PAD_LEFT); // total
batches
$fileControl .= str_pad($totBlockCount, 6, "0", STR_PAD_LEFT);
// block count including header/trailer
$fileControl .= str_pad($totEntryRecord, 8, "0", STR_PAD_LEFT);
// entry + addneda record count
$fileControl .= str_pad(substr($detailHash, -10, 10), 10, "0",
STR_PAD_LEFT);// entry hash - sum of ABA routing number 8 digits, length
of 10, drop 2 left most digits
$fileControl .= str_pad($totBatchDollar, 12, "0", STR_PAD_LEFT);
// total dollar amount, no decimal point
$fileControl .= str_pad('', 12, "0", STR_PAD_LEFT); // total
credit amount, no decimal point
$fileControl .= str_pad('', 39, " ", STR_PAD_RIGHT); // reserved
/*
* use for testing
echo "<pre>\n";
echo $fileHeader . "\n";
echo $batchHeader . "\n";
echo $detailRecord; // EOL in each record
echo $batchControl . "\n";
echo $fileControl . "\n";
echo "</pre>\n";*/
$filecontents = $fileHeader . "\n";
$filecontents.= $batchHeader . "\n";
$filecontents.= $detailRecord; // EOL in each record
$filecontents.= $batchControl . "\n";
$filecontents.= $fileControl . "\n";
if(!($filePtr = fopen("save a copy of the ACH file".$startDate.".ACH",
"w"))){
echo "Error opening file (".$startDate.".ACH)\n";
exit();
}
fwrite($filePtr, $filecontents);
fclose($filePtr);
if(!($filePtr = fopen("a file that can be opened in
Excel".$startDate.".xls", "w"))){
echo "Error opening file (".date("Ymd").".xls)\n";
exit();
}
fwrite($filePtr, $txtLog);
fclose($filePtr);
I hope this gets you pointed in the right direction, data calls will
have to be made and reference to those calls will have to be changed.
You probably do not need some of the queries here and can probably
simplify a couple of things, but this code comes from a production
system in use every day.
P.S. always reply to the list in the event someone's SPAM filter sends
your individual e-mail to the SPAM bucket. Also, others may be able to
help as your describe your problems more.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
