ошибок в логах нет, оплата поступает по скрипту :
#!/usr/bin/perl
use Time::localtime; use Date::Calc qw(Add_Delta_Days); use DBI;
$delta_days_now = @ARGV[0]; $delta_days_next = @ARGV[1];
if($delta_days_now eq ""){ $delta_days_now=0; } if($delta_days_next eq ""){ $delta_days_next=1; } ## $date_mssql_now = return_delta_time($tm_yy,$tm_mm,$tm_dd,$delta_days_now); $date_mssql_next = return_delta_time($tm_yy,$tm_mm,$tm_dd,$delta_days_next);
$result_no = 0; $result_ok = 1; $result_not_found = 2; $status_ok = 7; $pt = 39; $uid = 1; $error = 0; $file = "/usr/local/BGBIllingServer/script/terminal_payment/terminal_payment.log"; ##
##Настройки отправки отчета open(FILE,"< mailto.txt"); while(<FILE>){ if(($_ eq "") or ($_ =~/\#/)){ next; } chomp; push(@mailto,$_); } close FILE;
$smtpemailfrom="bgbilling\....."; $subject="Терминал платеж"; $body="Сообщение сгенерировано автоматически"; $attachment=$file; $smtpserver="....."; $smtplogin="bgbilling\......"; $smtppass="BGBilling......."; ##
open(FILE,"> $file"); flock(FILE,2);
#Данные для коннекта к MS SQL (....) $ms_sql="DBI:Sybase:server=.....:1091;database=Terminals"; $user="bill"; $password="billing";
#Данные для коннекта к MySQL (......) $db_host = "localhost"; $db_base = "bgbilling"; $db_user = "root"; $db_pass = "......"; $dsn = "DBI:mysql:$db_base:$db_host"; $dbh_mysql = DBI->connect($dsn, $db_user, $db_pass); $dbh_mysql -> do("SET NAMES cp1251");
$dbh_mssql = DBI->connect("$ms_sql",$user,$password);
$sql_mssql = "SELECT PaymentID,SessionNumber,TerminalID,convert(varchar(20), PaymentDateTime,20), StatusID,ErrorCode,Params,Amount,OperatorID,InitialSessionNumber,AmountAll, Notes FROM dbo.Payments WHERE PaymentDateTime >= convert(datetime,?,104) AND PaymentDateTime < convert(datetime,?,104) ORDER BY PaymentID" ;
$sth_mssql = $dbh_mssql -> prepare($sql_mssql) or ($error = 1); $sth_mssql -> execute($date_mssql_now,$date_mssql_next);
if($error == 0){ while (@mssql = $sth_mssql -> fetchrow_array()) { $sql_mysql = "SELECT COUNT(*) FROM `terminal_payment` WHERE `paymentid`=?"; $sth_mysql = $dbh_mysql -> prepare($sql_mysql) or ($error = 1); $sth_mysql -> execute($mssql[0]); if($error == 0){ $count = $sth_mysql -> fetchrow_array(); if($count == 0){ $cid = return_cid($mssql[6]); $sql_mysql_ins = "INSERT INTO `terminal_payment` VALUES('$mssql[0]','$mssql[1]','$mssql[2]','$mssql[3]','$mssql[4]','$mssql[5]','$cid','$mssql[7]','$mssql[8]','$mssql[9]','$mssql[10]','$mssql[11]','$mssql[12]')"; $dbh_mysql -> do($sql_mysql_ins); } else{ next; } } else{ print FILE "MySQL: Ошибка выборки количества записей из таблицы terminal_payment\t"; } } } else{ print FILE "MSSQL: Ошибка выборки записей из таблицы dbo.Payments\t"; } $dbh_mssql -> disconnect;
$sql_mysql = "SELECT paymentdatetime,cid,amountall,paymentid,operatorid FROM `terminal_payment` WHERE `result`=? AND `statusid`=?"; $sth_mysql = $dbh_mysql -> prepare($sql_mysql); $sth_mysql -> execute($result_no,$status_ok); while (@mysql = $sth_mysql -> fetchrow_array()){ $dt = return_data($mysql[0]); $comment = "Терминал (автоматический платеж id=$mysql[3])"; if(control_cid($mysql[1])){ $sql_mysql = "INSERT INTO `contract_payment`(`dt`,`cid`,`pt`,`uid`,`summa`,`comment`,`time_change`) VALUES('$dt','$mysql[1]','$pt','$uid','$mysql[2]','$comment','$mysql[0]')"; $dbh_mysql -> do($sql_mysql) or ($error=1); if($error == 0){ ($yy,$mm,$dd) = split(/\-/,$dt); $sql_mysql1 = "SELECT SUM(summa) FROM `contract_payment` WHERE `cid`='$mysql[1]' AND `dt` LIKE '$yy-$mm-%'"; $sth_mysql1 = $dbh_mysql -> prepare($sql_mysql1); $sth_mysql1 -> execute(); $summa_tmp = $sth_mysql1 -> fetchrow_array(); $sql_mysql = "UPDATE `contract_balance` SET `summa2`='$summa_tmp' WHERE `cid`='$mysql[1]' AND `yy`='$yy' AND `mm`='$mm'"; $dbh_mysql -> do($sql_mysql); $sql_mysql = "UPDATE `terminal_payment` SET `result`='$result_ok' WHERE `paymentid`='$mysql[3]'"; $dbh_mysql -> do($sql_mysql); print FILE "Платеж: дата($mysql[0])\tID($mysql[1])\tСумма($mysql[2])\tуспешно внесен\n"; } else{ $sql_mysql = "UPDATE `terminal_payment` SET `result`='$result_not_found' WHERE `paymentid`='$mysql[3]'"; $dbh_mysql -> do($sql_mysql); print FILE "Платеж: дата($mysql[0])\tID($mysql[1])\tСумма($mysql[2])\tошибка ввода(причина не определена)\n";
$comment = "Ошибочный л\/с $mysql[1]";
if($mysql[4] == 600){ $mysql[1] = "9962"; } if($mysql[4] == 601){ $mysql[1] = "14707"; }
$sql_mysql = "INSERT INTO `contract_payment`(`dt`,`cid`,`pt`,`uid`,`summa`,`comment`,`time_change`) VALUES('$dt','$mysql[1]','$pt','$uid','$mysql[2]','$comment','$mysql[0]')"; $dbh_mysql -> do($sql_mysql) or ($error=1); if($error == 0){ ($yy,$mm,$dd) = split(/\-/,$dt); $sql_mysql1 = "SELECT SUM(summa) FROM `contract_payment` WHERE `cid`='$mysql[1]' AND `dt` LIKE '$yy-$mm-%'"; $sth_mysql1 = $dbh_mysql -> prepare($sql_mysql1); $sth_mysql1 -> execute(); $summa_tmp = $sth_mysql1 -> fetchrow_array(); $sql_mysql = "UPDATE `contract_balance` SET `summa2`='$summa_tmp' WHERE `cid`='$mysql[1]' AND `yy`='$yy' AND `mm`='$mm'"; $dbh_mysql -> do($sql_mysql);
} } } else{ $sql_mysql = "UPDATE `terminal_payment` SET `result`='$result_not_found' WHERE `paymentid`='$mysql[3]'"; $dbh_mysql -> do($sql_mysql); print FILE "Платеж: дата($mysql[0])\tID($mysql[1])\tСумма($mysql[2])\tошибка ввода(не найден ID)\n"; $comment = "Ошибочный л\/с $mysql[1]";
if($mysql[4] == 600){ $mysql[1] = "9962"; } if($mysql[4] == 601){ $mysql[1] = "14707"; }
$sql_mysql = "INSERT INTO `contract_payment`(`dt`,`cid`,`pt`,`uid`,`summa`,`comment`,`time_change`) VALUES('$dt','$mysql[1]','$pt','$uid','$mysql[2]','$comment','$mysql[0]')"; $dbh_mysql -> do($sql_mysql) or ($error=1); if($error == 0){ ($yy,$mm,$dd) = split(/\-/,$dt); $sql_mysql1 = "SELECT SUM(summa) FROM `contract_payment` WHERE `cid`='$mysql[1]' AND `dt` LIKE '$yy-$mm-%'"; $sth_mysql1 = $dbh_mysql -> prepare($sql_mysql1); $sth_mysql1 -> execute(); $summa_tmp = $sth_mysql1 -> fetchrow_array(); $sql_mysql = "UPDATE `contract_balance` SET `summa2`='$summa_tmp' WHERE `cid`='$mysql[1]' AND `yy`='$yy' AND `mm`='$mm'"; $dbh_mysql -> do($sql_mysql); } } }
#$dbh_mysql -> disconnect;
close FILE; flock(FILE,8);
if( -s($file)){ foreach $mailto(@mailto){ system("/usr/bin/sendEmail -f $smtpemailfrom -t $mailto -u $subject -m $body -a $attachment -s $smtpserver:25 -xu $smtplogin -xp $smtppass"); } }
#Обработка и возврат даты в формате дд.мм.гггг sub return_delta_time{ $tm = localtime; ($tm_dd, $tm_mm, $tm_yy) = ($tm -> mday, $tm -> mon + 1, $tm -> year + 1900); ($tm_yy, $tm_mm, $tm_dd)= Add_Delta_Days($_[0], $_[1], $_[2], $_[3]); if($tm_mm < 10){ $tm_mm = "0$tm_mm"; } return "$tm_dd.$tm_mm.$tm_yy"; } #Выборка cid sub return_cid{ my ($a,$b,$cid); ($a,$b)=split(/\&/,$_[0]); ($a,$cid)=split(/\=/,$b); return $cid; } #Выборка даты sub return_data{ my($a,$b); ($a,$b)=split(/\s/,$_[0]); return $a; } #проверка cid sub control_cid{ my($sql_mysql,$result,$sth_mysql); $sql_mysql = "SELECT COUNT(*) FROM `contract` WHERE `id`=?"; $sth_mysql = $dbh_mysql->prepare($sql_mysql); $sth_mysql -> execute($_[0]); $result = $sth_mysql -> fetchrow_array(); if($result == 0){ return 0; } else{ return 1; } }
|