#!/usr/bin/php
<?php
$mysql_user = 'billing';
$mysql_pass = 'billing';
$mysql_host = '127.0.0.1';
$mysql_db   = 'billing';
ini_set('display_errors', 'On');
error_reporting(E_ALL);
// перечисляем всех менеджеров, которые принимают наличкой и должны быть в этом отчете
// в функции payments_nal эта пермененная вставляется непосредственно в запрос
$mysql_mod_person_nal = "('7', '13', '15')";
// перечисляем всех менеджеров, которые фигурируют в обобщенной информации по платежам
// Пополнение баланса личного счета абонента (обобщенная информация)
// используется иеще и в payments_all_second()
$mysql_mod_person_all = "('7', '13', '15')";
// Дата открытия филиала
// Эта переменная так же используется в типах документов, дата начала действия типа документа
// а так же в pay_types(), supplement_services()
$filial_date = '2017-01-01';
// Описание филиала
$filial_descr = 'Краснодарский филиал';
// описание назначения диапазона IP-адресов 
$segment_descr = 'Адреса для абонентов';
// список предоставляемых услуг, это ID агентов ЛБ
$services_list = "('2', '4', '6')";
// IP-адрес шлюза (либо V4 либо v6) (строка);
$gate = '91.240.124.3';
$gate_address = '"Россия";"Краснодарский";"Краснодарский";"Краснодар";"";""';
// выгружать нужно только разницу или всю базу? false - вся база, true - только новье
$diff = false;
// берем текущую дату и дату последней проверки
$lastcheck = file_get_contents('lastchek.txt');
// данные для доступ на ftp
$ftp_upload = false; // делать ли выгрузку на ftp
$ftp_host = '1.2.3.4';
$ftp_user = 'login';
$ftp_pass = 'pass';
// функция для загрузки файлов на FTP сорма
//
function upload_to_ftp( $dir_on_ftp, $local_file ) {
global $ftp_user, $ftp_pass, $ftp_host, $ftp_upload;
if (!$ftp_upload) { return; }; //выходим, если делать выгрузку на ftp не нужно. Например, для тестов
$remote_file = $dir_on_ftp . $local_file;
$conn_id = ftp_connect($ftp_host);
$login = ftp_login($conn_id, $ftp_user, $ftp_pass);
ftp_put($conn_id, $remote_file, $local_file, FTP_BINARY);
}
// функция сконвертирует дату из формата в базе биллинга в формат,
// необходимый для выгрузки
// 2011-12-03 11:12:13 --> 03.12.2011 11:12:13
function normalize_date ( $date ) {
// если аргумент по шаблону, то возвращаем исправленную дату, иначе возвращаем то, что получили
if (preg_match("/^(\d{4})-(\d{2})-(\d{2})\s+(\d{2}:\d{2}:\d{2})$/", $date, $matches)) {
return "$matches[3].$matches[2].$matches[1] $matches[4]";
} elseif (preg_match("/^(\d{4})-(\d{2})-(\d{2})$/", $date, $matches))  {
return "$matches[3].$matches[2].$matches[1]";
} else {
return $date;
}
}
$db = mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die( mysql_error() );
mysql_select_db( $mysql_db ) or die( mysql_error() );
// чтобы в каждой функции не писать одно и то же, создаем универсалльную функцию    
function query( $sql ) {
global $db;
$result = mysql_query( $sql ) or die( mysql_error() );  
return $result;
}        
// немного постобработки для каждой строки
function sanitize( $str ) {
$str = preg_replace("/;$/", "\n", $str);
$str = preg_replace("/,+/", ",", $str);
$str = preg_replace("/,\"/", "\"", $str);
return $str;
}
// Формат данных об абонентах физических и юридических лицах по договорам на оказание услуг передачи данных     
function abonents () {
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and vgroups.last_mod_date > '$lastcheck'" : '';        
$sql = "
select  '2' as filial, 
vgroups.login,
inet_ntoa(CONV(RIGHT(HEX(staff.segment), 8),16,10)),
mac_staff.mac,
agreements.date,
agreements.number,
vgroups.blocked,
vgroups.creation_date,
vgroups.block_date,
accounts.type,
'1',
'' as fio_nonstruct,
'' as name1,
'' as surname,
accounts.name as fullname,
'' as birf_date,
'1' as pass_non_struct,
'' as pass_serial,
'' as pass_number,
'' as kem_i_kogda,
concat(accounts.pass_sernum, ',', accounts.pass_no, ',', accounts.pass_issuedep),
'1' as doc_id,
'' as abobent_bank,
'' as abonent_schet,
accounts.name as fname,
accounts.inn,
'' as contarct_person,
concat(accounts.phone, ',', accounts.mobile, ',', accounts.fax),
accounts.bank_name,
accounts.corr,
'1' as addr_non_struct,
'' as zip,
'' as strana,
'' as oblast,
'' as raion,
'' as gorod,
'' as ulica,
'' as nomer_doma,
'' as korpus,
'' as kvartira,
vgroups_addr.address,
'1' as hz,
'' as zip2,
'' as strana2,
'' as oblast2,
'' as raion2,
'' as gorod2,
'' as ulica2,
'' as nomer_doma2,
'' as korpus2,
'' as kvartira2,
vgroups_addr.address as adress2
from        vgroups left join mac_staff on (vgroups.vg_id = mac_staff.vg_id),
staff,
vgroups_addr,
accounts,
agreements
where
vgroups.id = 2 and vgroups.archive = 0 and
staff.vg_id = vgroups.vg_id and
accounts.category = 0 and
accounts.uid = vgroups.uid and
vgroups.agrm_id = agreements.agrm_id and
vgroups_addr.vg_id = vgroups.vg_id
$add_to_sql
";
$result = query( $sql );
$file = fopen("abonents.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
// если абонент заблокирован, то выставляем ему статус 1
$row['blocked'] = ($row['blocked'] > 0) ? 1 : 0;
// абонент физическое или юридическое лицо (0 физ, 1 ЮР)
$row['type'] = ($row['type'] == 2) ? 0 : 1;
//если абонент активен, то дату блокировки не заполняем
$row['block_date'] = ($row['blocked'] > 0) ? $row['block_date'] : '';
foreach ( $row as $value ) {
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );
upload_to_ftp('ISP/abonents/abonents/', 'abonents.csv');
}
// Формат данных о подключенных абонентам видах услуг связи
function services () {
global $services_list, $diff, $lastcheck;
$add_to_sql = ($diff) ? "and vgroups.last_mod_date > '$lastcheck'" : '';    
$sql = "
select '2' as filial,
vgroups.login,
agreements.number,
vgroups.id,
vgroups.creation_date,
vgroups.block_date,
vgroups.blocked,
'' as individuum
from        vgroups,
agreements
where vgroups.archive = 0 and vgroups.id in $services_list and
agreements.archive = 0 and
vgroups.agrm_id = agreements.agrm_id 
$add_to_sql
";
$result = query( $sql );
$file = fopen("services.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
//если абонент активен, то дату блокировки не заполняем
$row['block_date'] = ($row['blocked'] > 0) ? $row['block_date'] : '';
unset($row['blocked']);
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );
upload_to_ftp('ISP/abonents/services/', 'services.csv');
}
// Формат данных об абонентах физических и юридических лицах по договорам на оказание услуг телефонной связи 
function pstn() {
$file = fopen("pstn.csv", "w") or die();
fclose( $file );
upload_to_ftp('ISP/abonents/services-pstn/', 'pstn.csv');
}
//Формат данных о пополнениях баланса через банковский перевод
function payments_from_banks () {
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and payments.pay_date > '$lastcheck'" : '';         
$sql = "
select 
'2' as filial,
agreements.number,
inet_ntoa(CONV(RIGHT(HEX(staff.segment), 8),16,10)),
payments.pay_date,
accounts.corr,
accounts.bank_name,
'-' as bank_address,
payments.amount
from payments, 
agreements,
accounts,
staff,
vgroups
where accounts.archive = 0 and
agreements.archive = 0 and
agreements.uid = accounts.uid and
vgroups.uid = accounts.uid and
staff.vg_id = vgroups.vg_id and
payments.agrm_id = agreements.agrm_id and
accounts.type != 2 and
payments.pay_date > '2018-01-01'
$add_to_sql
";
$result = query( $sql );
$file = fopen("bank-transaction.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );  
upload_to_ftp('ISP/payments/bank-transaction/', 'bank-transaction.csv');
}
// Пополнение баланса через карты экспресс-оплаты
function payments_express_card() {
$file = fopen("express-card.csv", "w") or die();
fclose( $file );
upload_to_ftp('ISP/payments/bank-transaction/', 'bank-transaction.csv');
}
// Пополнение баланса через терминалы моментальных платежей
function payments_express_terminal() {
$file = fopen("public-terminal.csv", "w") or die();
fclose( $file );
}
// Пополнение баланса через центры обслуживания клиентов
// выбираем все платежи, mod_person у которых настроен
function payments_nal() {
global $mysql_mod_person_nal, $diff, $lastcheck;
$add_to_sql = ($diff) ? "and payments.pay_date > '$lastcheck'" : '';  
$sql = "
select 
'2' as filial,
agreements.number,
inet_ntoa(CONV(RIGHT(HEX(staff.segment), 8),16,10)),
payments.pay_date,
'1' as pay_office,
'Россия' as strana,
'Владимирская' as oblast,
'Владимирский' as raion,
'Владимир' as gorod,
'Кукуева' as ulica,
'666' as nomer_doma,
payments.amount
from payments, 
agreements,
staff,
vgroups
where 
agreements.archive = 0 and
staff.vg_id = vgroups.vg_id and
payments.agrm_id = agreements.agrm_id and
payments.pay_date > '2018-01-01' and
vgroups.agrm_id = agreements.agrm_id and
payments.mod_person in $mysql_mod_person_nal
$add_to_sql
";
$result = query( $sql );
$file = fopen("service-center.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );
upload_to_ftp('ISP/payments/service-center/', 'service-center.csv');
}
// Пополнение баланса посредством снятия денег со счета другого абонента
// не используется!
function payments_abon_to_abon() {
$file = fopen("bank-account-transfer.csv", "w") or die();
fclose( $file );
}
// Пополнение баланса через телефонные карты
// не используется!
function payments_tel_cards() {
$file = fopen("balance_fillup_pstn.csv", "w") or die();
fclose( $file );        
}
// Пополнение баланса личного счета абонента (обобщенная информация)
function payments_all() {
global $mysql_mod_person_all;
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and payments.pay_date > '$lastcheck'" : '';  
$sql = "
select 
'2' as filial,
payments.class_id,
agreements.number,
inet_ntoa(CONV(RIGHT(HEX(staff.segment), 8),16,10)),
payments.pay_date,
payments.amount,
payments.receipt 
from payments, 
agreements,
staff,
vgroups
where 
agreements.archive = 0 and
staff.vg_id = vgroups.vg_id and
payments.agrm_id = agreements.agrm_id and
payments.pay_date > '2018-01-01' and
vgroups.agrm_id = agreements.agrm_id and
payments.mod_person in $mysql_mod_person_all
$add_to_sql
";
$result = query( $sql );
$file = fopen("balance-fillup.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );   
upload_to_ftp('ISP/payments/balance-fillup/', 'balance-fillup.csv');        
}
//Пополнение баланса личного счета абонента (обобщенная информация)
function payments_all_second() {
global $mysql_mod_person_all;
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and payments.pay_date > '$lastcheck'" : '';
$sql = "
select 
'2' as filial,
payments.class_id,
accounts.phone,
payments.pay_date,
payments.amount,
payments.receipt 
from payments, 
accounts,
agreements
where 
payments.agrm_id = agreements.agrm_id and
payments.pay_date > '2018-01-01' and
accounts.uid = agreements.uid and
payments.mod_person in $mysql_mod_person_all
$add_to_sql
";
$result = query( $sql );
$file = fopen("balance_fillup_pstn.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file ); 
upload_to_ftp('ISP/payments/balance-fillup/', 'balance-fillup.csv');
}
//Формат справочника IP шлюзов
function gates() {
global $filial_date, $gate, $gate_address;
$file = fopen("gates.csv", "w") or die();
fwrite($file, '"2";"' . $gate . '";"01.01.2017 15:10:01";"";"asr1001x";"Россия";"Краснодарский Край";"Краснодар";"Краснодар";"Северная";"279";"7"');
fclose( $file ); 
upload_to_ftp('ISP/dictionaries/gates/', 'gates.csv');
}
//Формат справочника ДВО (видов услуг)
function supplement_services() {
global $filial_date, $services_list;
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and settings.last_mod_date > '$lastcheck'" : '';
$sql = "
select 
'2' as filial,
id,
'100'+id,
'$filial_date' as date_start,
'' as date_end,
descr
from settings where id in $services_list 
$add_to_sql
";
$result = query( $sql );
$file = fopen("supplement_services.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );
upload_to_ftp('ISP/dictionaries/supplement-services/', 'supplement_services.csv');        
}
// Формат справочника способов оплаты (пополнения баланса)
function pay_types() {
global $filial_date;
$sql = "
select 
'2' as filial,
class_id,
'$filial_date' as start_date,
'' as end_date,
name
from pay_classes
"; 
$result = query( $sql );
$file = fopen("pay_types.csv", "w") or die();
while( $row = mysql_fetch_assoc( $result ) ) {
$str = "";
foreach ( $row as $value ) {               
$str .= '"' . normalize_date($value) . '";';
}
fwrite( $file, sanitize($str) );
}
fclose( $file );
upload_to_ftp('ISP/dictionaries/pay-types/', 'pay_types.csv');
}
// Формат справочника IP-плана адресации
function ip_numbering_plan() {
global $segment_descr;
global $diff, $lastcheck;
$add_to_sql = ($diff) ? "and segments.last_mod_date > '$lastcheck'" : '';
$sql = "
select
'2' as filial,
'$segment_descr' as descr,
inet_ntoa(CONV(RIGHT(HEX(segments.segment), 8),16,10)),
inet_ntoa(CONV(RIGHT(HEX(segments.mask), 8),16,10)),
segments.last_mod_date,
'' as segment_end
from segments
where archive = 0
$add_to_sql
";
$result = query( $sql );
$file = fopen("ip_numbering_plan.csv", "w") or die();
fwrite($file, '"2";"Адреса для абонентов";"100.64.0.0";"16";"29.06.2016 19:03:04";""'. PHP_EOL);
fwrite($file, '"2";"Адреса для абонентов";"91.240.124.0";"24";"29.06.2016 19:05:00";""'. PHP_EOL);
fwrite($file, '"2";"Адреса для абонентов";"91.240.125.0";"24";"22.07.2016 11:21:00";""'. PHP_EOL);
fwrite($file, '"2";"Адреса для абонентов";"91.240.126.0";"24";"22.07.2016 11:21:00";""'. PHP_EOL);
fwrite($file, '"2";"Адреса для абонентов";"91.240.127.0";"24";"22.07.2016 11:21:00";""'. PHP_EOL);
fclose( $file );
//while( $row = mysql_fetch_assoc( $result ) ) {
//    $str = "";
//    foreach ( $row as $value ) {               
//        $str .= '"' . normalize_date($value) . '";';
//    }
//    fwrite( $file, sanitize($str) );
//}
//fclose( $file );     
upload_to_ftp('ISP/dictionaries/ip-numbering-plan/', 'ip_numbering_plan.csv');
}
//Формат справочника типов документов, удостоверяющих личность
function doc_types() {
global $filial_date;
$file = fopen("doc_types.csv", "w") or die();
fwrite($file, '"1";"1";"01.01.2017 00:00:00";"";"Паспорт гражданина РФ"');
fclose( $file ); 
upload_to_ftp('ISP/dictionaries/doc-types/', 'doc_types.csv');
}
// Формат справочника специальных номеров оператора (SMS-центры, ТМС, сервисы)
// не используется
function special_numbers() {
$file = fopen("special-numbers.csv", "w") or die();
fclose( $file ); 
}
// Формат справочника кодов причин завершения соединений (CDR)
// не используется
function term_causes() {
$file = fopen("term_causes.csv", "w") or die();
fclose( $file );    
}
// Формат справочника пучков соединительных линий (CDR)
// не используется
function bunches() {
$file = fopen("bunches.csv", "w") or die();
fclose( $file );
}
//Формат справочника коммутаторов, обслуживающие телефонные соединения
// не используется
function pstn_switches() {
$file = fopen("switches.csv", "w") or die();
fclose( $file );
}
//Формат справочника плана деления телефонной номерной емкости, выделенной оператору, на диапазоны
// не используется!
function telephone_numbering_plans() {
$file = fopen("telephone_numbering_plans.csv", "w") or die();
fclose( $file );
}
//Формат справочника с информацией о базовых станциях сетей радиосвязи и параметрах антенных систем (wi-fi)
// Не используется
function basic_stations() {
$file = fopen("basic-stations.csv", "w") or die();
fclose( $file );
}
// Формат справочника типов вызовов
// не используется!
function call_types() {
$file = fopen("call-types.csv", "w") or die();
fclose( $file );
}
// Формат справочника с информацией по операторам связи и их филиалах
function filials() {
global $filial_date, $filial_descr;
$file = fopen("telcos.csv", "w") or die();
fwrite($file, '"1";"01.01.2017";"";"Краснодарский филиал"');
fclose( $file );
upload_to_ftp('ISP/dictionaries/telcos/', 'telcos.csv');
}
abonents();
services();
pstn();
payments_from_banks();
payments_express_card();
payments_express_terminal();
payments_nal();
payments_abon_to_abon();
payments_tel_cards();
payments_all();
//    payments_all_second(); эта фигня нужна для pstn
gates();
supplement_services();
pay_types();
ip_numbering_plan();
doc_types();
special_numbers();
term_causes();
bunches();
pstn_switches();
telephone_numbering_plans();
basic_stations();
call_types();
filials();
$datenow = date('Y-m-d H:i:s');
file_put_contents('lastchek.txt', $datenow);
?>