Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/php -q
- <?Php
- require ("./holidays.php");
- // global data
- $direct_debit_date=15; // will require the debit for this date - or first working day after
- $msgid_stub="MY COMPANY "; // message id - the reference will be added
- $dd_name="MY COMPANY SAS";
- $dd_iban="FR7600000000000000000000000";
- $dd_bic="BNKRRRRRRR";
- $dd_iddd="FR45ZZZ0000000";
- $dd_pmtinf="YOUR SERVICE PAYMENT ";
- $dd_rmtinf="INVOICE ";
- // you may want to review $detailqry definition later in the code, to define which invoices you want to select
- // invoice period in french
- $nmmonth = array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
- $nmmois = array('Janvier', 'Fevrier', 'Mars', 'Avril', 'Mai', 'Juin', 'Juillet', 'Aout', 'Septembre', 'Octobre', 'Novembre', 'Decembre');
- $invper = str_replace($nmmonth, $nmmois, date("F Y"));
- // tab genertor
- $ntab=0;
- function tab($dir) {
- global $ntab;
- $ntab+=$dir;
- $t="\n";
- $i=0;
- while ($i++<$ntab) $t.="\t";
- return $t;
- }
- function sanitize ($str) {
- return preg_replace( '/[^a-zA-Z0-9 \/-?:().,\'+s]/', '', $str );
- }
- //
- // Calcualte collection date - first working day after the 15th
- //
- $ym=date('Y-m');
- $reqddate=$ym."-".$direct_debit_date;
- while (isHoliday(strtotime($reqddate))) {
- $direct_debit_date++;
- $reqddate=$ym."-".$direct_debit_date;
- }
- echo "file name : ddsepa-".date('Y-m-d').".xml\n";
- echo "Direct Debit Required date: $reqddate \n";
- //
- // db connection
- //
- if ( $_ENV['HOSTNAME'] == "pollux" ) {
- echo "******\n";
- echo "****** DIRECT DEBIT runnning on POLLUX\n";
- echo "******\n\n";
- $dbname="a2bprod";$dbuser="root";$dbpass="orion451";
- $realhost=1;
- } else {
- $dbname="a2b141";$dbuser="root";$dbpass="orion001";
- $realhost=0;
- }
- if ( !($db=mysql_connect("localhost",$dbuser, $dbpass)) ) { die ("Cannot connect to mysql..." . mysql_error());}
- if ( !(mysql_select_db($dbname,$db)) ) { die ("Cannot select db.." . mysql_error());}
- //
- // compute unique reference
- //
- $qry="select valeur from direct_debit_data where cle='lastref' ;";
- $res=mysql_query($qry, $db); if ( $res === FALSE) { echo $qry."\n";die(mysql_error()); }
- $row=mysql_fetch_row($res);
- $refid=$row[0]+1;
- $qry="update direct_debit_data set valeur='".$refid."'where cle='lastref' ; ";
- $res=mysql_query($qry, $db); if ( $res === FALSE) { echo $qry."\n";die(mysql_error()); }
- echo "Reference for this file: $refid \n";
- // check if dd already run
- $qry="select valeur from direct_debit_data where cle='lastdaterun' ;";
- $res=mysql_query($qry, $db); if ( $res === FALSE) { echo $qry."\n";die(mysql_error()); }
- $row=mysql_fetch_row($res);
- if (substr($row[0],0,7) == date('Y-m')) {
- echo "=== direct debit has already run this month (".$row[0].")- process anyway ? y/N ";
- $s=fgets(STDIN);
- if (strtolower(substr($s,0,1)) != "y") {
- mysql_close($db);
- die ("aborted.....\n");
- }
- }
- // retrieve totals to be debited
- //
- // a detailed query for each row, and a summary for the headers
- //
- $detailqry ='select iv.id_card, min(reference), max(reference), count(*), sum(iv.open_amount) as opn, min(validfrom), min(rum) as rum, min(bic),min(iban), ';
- $detailqry.=" min(if(ca.company_name = '', ca.lastname, ca.company_name) ) ";
- $detailqry.='from cc_invoice iv, direct_debit dd, cc_card ca ';
- $detailqry.='where iv.id_card = dd.id_card and iv.date >=dd.validfrom and ca.id=iv.id_card ';
- $detailqry.='and open_amount > 0 ';
- $detailqry.='and iv.description="Facture POSTPAID" and isactive > 0 ';
- // the first clause will be added here to $detrailqry in the processing loop
- $detailqrygrp='group by 1 ';
- // ** ALL ** the transactions
- $sumqry= "select count(*), sum(opn) from ( " . $detailqry . $detailqrygrp . " ) as tot; ";
- $postqry ='select iv.id, iv.reference, iv.open_amount, iv.id_card from cc_invoice iv, direct_debit dd ';
- $postqry.='where iv.id_card = dd.id_card and iv.date >=dd.validfrom ';
- $postqry.='and open_amount > 0 and iv.description="Facture POSTPAID" and isactive > 0 ';
- $restot=mysql_query($sumqry, $db); if ( $restot === FALSE) { echo $sumqry."\n";die(mysql_error()); }
- $rowtot=mysql_fetch_row($restot);
- $uptfrstqry="update direct_debit set isfirst=0 where isactive>0 and isfirst=1 ; ";
- #echo $detailqry . "\n";
- #echo $sumqry ."\n";
- $f=fopen("ddsepa-".date('Y-m-d').".xml", "w");
- fputs($f,'<?xml version="1.0" encoding="utf-8" ?>'."\n");
- fputs($f,'<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">'."\n");
- fputs($f,'<CstmrDrctDbtInitn>'."\n");
- // group
- fputs ($f, "<GrpHdr>\n"); $t=tab(1);
- fputs ($f, "$t<MsgId>$msgid_stub $refid</MsgId>");
- fputs ($f, "$t<CreDtTm>".date ("Y-m-d\TH:i:s")."</CreDtTm>");
- fputs ($f, "$t<NbOfTxs>".$rowtot[0]."</NbOfTxs>");
- fprintf ($f, "$t<CtrlSum>%.2f</CtrlSum>", $rowtot[1]);
- $xactcnt=$rowtot[0];
- $xactsum=$rowtot[1];
- fputs($f, "$t<InitgPty>"); $t=tab(1);
- fputs($f, "$t<Nm>$dd_name</Nm>");
- //fputs($f, "$t<PstlAdr>"); $t=tab(1);
- //fputs($f, "$t<StrtNm>6 rue des fleurs</StrtNm>");
- //fputs($f, "$t<PstCd>75014</PstCd>");
- //fputs($f, "$t<TwnNm>Paris</TwnNm>");
- //fputs($f, "$t<Ctry>FR</Ctry>");
- //$t=tab(-1);
- //fputs($f, "$t</PstlAdr>");
- //fputs($f, "$t<CtctDtls>"); $t=tab(1);
- //fputs($f, "$t<Nm>john doe</Nm>");
- //fputs($f, "$t<EmailAdr>john@doe.org</EmailAdr>");
- //$t=tab(-1);
- //fputs($f, "$t</CtctDtls>");
- $t=tab(-1);
- fputs($f, "$t</InitgPty>");
- $t=tab(-1);
- fputs ($f, "$t</GrpHdr>\n");
- //
- // a bit bulky, but lets process the **first** transactions
- //
- $first=1;
- while ($first >= 0 ){
- $resdet=mysql_query($detailqry . " and isfirst=$first " . $detailqrygrp, $db); if ( $restot === FALSE) { echo $sumqry."\n";die(mysql_error()); }
- if (mysql_num_rows($resdet) > 0) {
- // get total for this block
- $loopqry= "select count(*), sum(opn) from ( " . $detailqry . " and isfirst=$first " . $detailqrygrp ." ) as tot; ";
- $resloop=mysql_query($loopqry, $db); if ( $resloop === FALSE) { echo $loopqry."\n";die(mysql_error()); }
- $rowloop=mysql_fetch_row($resloop);
- //
- // insert a payment info block
- //
- fputs ($f, "$t<PmtInf>"); $t=tab(1);
- fputs ($f, "$t<PmtInfId>$dd_pmtinf $invper</PmtInfId>");
- fputs ($f, "$t<PmtMtd>DD</PmtMtd>");
- fputs ($f, "$t<BtchBookg>false</BtchBookg>");
- fputs ($f, "$t<NbOfTxs>".$rowloop[0]."</NbOfTxs>");
- fprintf ($f, "$t<CtrlSum>%.2f</CtrlSum>", $rowloop[1]);
- // all these efforts hust to have frst here....
- if ($first ==1) {
- fputs ($f, "$t<PmtTpInf><SvcLvl><Cd>SEPA</Cd></SvcLvl><LclInstrm><Cd>CORE</Cd></LclInstrm><SeqTp>FRST</SeqTp></PmtTpInf>");
- } else {
- fputs ($f, "$t<PmtTpInf><SvcLvl><Cd>SEPA</Cd></SvcLvl><LclInstrm><Cd>CORE</Cd></LclInstrm><SeqTp>RCUR</SeqTp></PmtTpInf>");
- }
- fputs ($f, "$t<ReqdColltnDt>".$reqddate."</ReqdColltnDt>");
- fputs ($f, "$t<Cdtr>"); $t=tab(1);
- fputs($f, "$t<Nm>$dd_name</Nm>");
- //fputs($f, "$t<PstlAdr>"); $t=tab(1);
- //fputs($f, "$t<StrtNm>6 rue des fleurs</StrtNm>");
- //fputs($f, "$t<PstCd>75014</PstCd>");
- //fputs($f, "$t<TwnNm>Paris</TwnNm>");
- //fputs($f, "$t<Ctry>FR</Ctry>");
- //$t=tab(-1);
- //fputs($f, "$t</PstlAdr>");
- $t=tab(-1);
- fputs ($f, "$t</Cdtr>");
- fputs ($f, "$t<CdtrAcct>"); $t=tab(1);
- fputs ($f, "$t<Id><IBAN>$dd_iban</IBAN></Id>");
- $t=tab(-1);
- fputs ($f, "$t</CdtrAcct>");
- fputs ($f, "$t<CdtrAgt>");$t=tab(1);
- fputs ($f, "$t<FinInstnId><BIC>$dd_bic</BIC></FinInstnId>");
- $t=tab(-1);
- fputs ($f, "$t</CdtrAgt>");
- fputs ($f, "$t<ChrgBr>SLEV</ChrgBr>");
- fputs ($f, "$t<CdtrSchmeId>");$t=tab(1);
- fputs ($f, "$t<Nm>$dd_name</Nm>");
- fputs ($f, "$t<Id><PrvtId><Othr><Id>$dd_iddd</Id>");
- fputs ($f, "$t<SchmeNm><Prtry>SEPA</Prtry></SchmeNm>");
- fputs ($f, "$t</Othr></PrvtId></Id>");
- $t=tab(-1);
- fputs ($f, "$t</CdtrSchmeId>");
- ///// loop through all the payments
- while ($rd=mysql_fetch_row($resdet) ) {
- // if multiple invoices to be debited at once, makes up a ref with 20140000first-last
- if ($rd[3] > 1) {
- $payid=$rd[1]."-".substr($rd[2],-3);
- } else {
- $payid=$rd[1];
- }
- fputs ($f, "$t<DrctDbtTxInf>"); $t=tab(1);
- fputs ($f, "$t<PmtId>");$t=tab(1);
- fputs ($f, "$t<EndToEndId>".$payid."</EndToEndId>");
- $t=tab(-1);
- fputs ($f, "$t</PmtId>");
- fprintf ($f, "$t<InstdAmt Ccy=\"EUR\">%.2f</InstdAmt>",$rd[4]);
- fputs ($f, "$t<DrctDbtTx>"); $t=tab(1);
- fputs ($f, "$t<MndtRltdInf>"); $t=tab(1);
- fputs ($f, "$t<MndtId>".$rd[6]."</MndtId>");
- fputs ($f, "$t<DtOfSgntr>".substr($rd[5],0,10)."</DtOfSgntr>");
- $t=tab(-1);
- fputs ($f, "$t</MndtRltdInf>");
- $t=tab(-1);
- fputs ($f, "$t</DrctDbtTx>");
- fputs ($f, "$t<DbtrAgt>");$t=tab(1);
- fputs ($f, "$t<FinInstnId>");$t=tab(1);
- fputs ($f, "$t<BIC>".$rd[7]."</BIC>");
- $t=tab(-1);
- fputs ($f, "$t</FinInstnId>");
- $t=tab(-1);
- fputs ($f, "$t</DbtrAgt>");
- fputs ($f, "$t<Dbtr>");$t=tab(1);
- fputs ($f, "$t<Nm>".sanitize($rd[9])."</Nm>");
- $t=tab(-1);
- fputs ($f, "$t</Dbtr>");
- fputs ($f, "$t<DbtrAcct>");$t=tab(1);
- fputs ($f, "$t<Id><IBAN>".$rd[8]."</IBAN></Id>");
- $t=tab(-1);
- fputs ($f, "$t</DbtrAcct>");
- fputs ($f, "$t<RmtInf><Ustrd>$dd_rmtinf". $invper . " " .$payid."</Ustrd></RmtInf>");
- $t=tab(-1);
- fputs ($f, "$t</DrctDbtTxInf>");
- }
- $t=tab(-1);
- fputs ($f, "$t</PmtInf>");
- }
- // switches from first to recur
- $first--;
- }
- fputs ($f, "$t</CstmrDrctDbtInitn>");
- fputs ($f, "$t</Document>\n");
- echo "\n\nDone\n\nNumber of transactions in file: $xactcnt\n";
- printf ("Amount in file: %.2f eur\n\n", $xactsum);
- echo "update the database ? y/N ";
- $s=fgets(STDIN);
- if (strtolower(substr($s,0,1)) != "y") {
- mysql_close($db);
- die ("aborted.....\n");
- }
- echo "now updating database..\n";
- // update all dd entries to not FIRST as they've been processed !
- $ru=mysql_query($uptfrstqry); if ($ru === FALSE) { echo $uptfrstqry ."\n";die(mysql_error()); }
- $totposted=0;
- $totxact=0;
- $res=mysql_query($postqry, $db); if ( $res === FALSE) { echo $postqry."\n";die(mysql_error()); }
- while ($row=mysql_fetch_row($res) ) {
- $totposted += $row[2];
- $totxact++;
- $insq ="insert into cc_logpayment ( `date`, payment, card_id, id_logrefill, description, added_refill, payment_type, added_commission ) ";
- $insq.="values ('".$reqddate." 01:00:00', ".$row[2].", ".$row[3].", null, 'DD-".$refid."', 0,0,0) ; ";
- $ri=mysql_query($insq); if ( $ri === FALSE) { echo $insq."\n";die(mysql_error()); }
- $insq="insert into cc_invoice_payment ( id_invoice, id_payment ) values ( ".$row[0]." , LAST_INSERT_ID() ) ";
- $ri=mysql_query($insq); if ( $ri === FALSE) { echo $insq."\n";die(mysql_error()); }
- }
- echo "Total posted in DB: $totposted \n";
- echo "Total transactions: $totxact \n";
- if ( $totposted != $xactsum ) {
- echo "\n\n";
- echo "*****************************************************************\n";
- echo "*****************************************************************\n";
- echo "*****************************************************************\n";
- echo "*****************************************************************\n";
- echo "\n\n";
- echo "Warning - sum from the file ($xactsum) and from the db ($totposted) posting differ.... \n\n";
- echo "please check\n";
- }
- fclose($f);
- mysql_close($db);
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement