#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
use utf8;
use DBD::mysql;
use Time::Piece;

my $date = localtime;
my $ddate= $date->strftime('%Y%m%d');
my $dddate= $date->strftime('%H%M');

my $db = "billing";
my $host = "127.0.0.1";
my $port = "3306";
my $source = "DBI:mysql:$db:$host:$port";
my $username = "billing";
my $password = "billing";

my $dbc = DBI->connect($source, $username, $password);
my $sql = $dbc->prepare("select DISTINCT accounts.uid as ID, usergroups_staff.group_id as REGION_ID, TIMESTAMP(agreements.date) as CONTRACT_DATE, agreements.number as CONTRACT,
 agreements.number as ACCOUNT, TIMESTAMP(agreements.date) as ACTUAL_FROM, if(agreements.state<1,'0000-00-00 00:00:00',agreements.last_mod_date) as ACTUAL_TO, if(accounts.type>1,'42','43') as ABONENT_TYPE,
   if(accounts.type<2,'','0') as NAME_INFO_TYPE, accounts.abonent_surname as FAMILY_NAME, accounts.abonent_name as GIVEN_NAME, accounts.abonent_patronymic as INITIAL_NAME,
      '' as UNSTRUCT_NAME, if (accounts.type>1,accounts.birthdate,'') as BIRTH_DATE, if(accounts.type>1,'1','2') as IDENT_CARD_TYPE_ID,
      if(accounts.type<2,'','0') as IDENT_CARD_TYPE, accounts.pass_sernum as IDENT_CARD_SERIAL, accounts.pass_no as IDENT_CARD_NUMBER,
       concat(accounts.pass_issuedate,' ',accounts.pass_issuedep) as IDENT_CARD_DESCRIPTION, '' as IDENT_CARD_UNSTRUCT,
       accounts.bank_name as BANK, accounts.settl as BANK_ACCOUNT, if(accounts.type<2,accounts.name,'') as FULL_NAME, if(accounts.type<2,accounts.inn,'') as INN,
        if(accounts.type<2,accounts.kont_person,'') as CONTACT,
        concat(if(accounts.type<2,accounts.phone,''),' ',if(accounts.type<2,accounts.fax,'')) as PHONE_FAX, if(agreements.state<1,'0','1') as STATUS, TIMESTAMP(agreements.date) as ATTACH, if(agreements.state<1,'0000-00-00 00:00:00',agreements.last_mod_date) as DETACH,
         '4' as NETWORK_TYPE, '1' as RECORD_ACTION, '' as INTERNAL_ID1
         from accounts
         inner join usergroups_staff
         on usergroups_staff.uid=accounts.uid
         inner join agreements
         on agreements.uid=usergroups_staff.uid
         and agreements.uid=accounts.uid
         inner join vgroups
         on vgroups.uid=accounts.uid
         and vgroups.uid=agreements.uid
         and vgroups.uid=usergroups_staff.uid
         where usergroups_staff.group_id<>0 and vgroups.id not in ('4','7','8','9','10','18') and vgroups.blocked in (0,1,4) and agreements.date > '2018-01-01';");

my $out = $sql->execute();

open my $outp, ">", "ABONENT_".$ddate."_".$dddate.".txt" or die "no open files";

print $outp "ID",";","REGION_ID",";","CONTRACT_DATE",";","CONTRACT",";","ACCOUNT",";","ACTUAL_FROM",";","ACTUAL_TO",";","ABONENT_TYPE",";","NAME_INFO_TYPE",";","FAMILY_NAME",";",
"GIVEN_NAME",";","INITIAL_NAME",";","UNSTRUCT_NAME",";","BIRTH_DATE",";","IDENT_CARD_TYPE_ID",";","IDENT_CARD_TYPE",";","IDENT_CARD_SERIAL",";","IDENT_CARD_NUMBER",";",
"IDENT_CARD_DESCRIPTION",";","IDENT_CARD_UNSTRUCT",";","BANK",";","BANK_ACCOUNT",";","FULL_NAME",";","INN",";","CONTACT",";","PHONE_FAX",";","STATUS",";","ATTACH",";","DETACH",
";","NETWORK_TYPE",";","RECORD_ACTION",";","INTERNAL_ID1","\n";

while ((my @ref) = $sql->fetchrow_array())
{
 print $outp $ref[0],";",$ref[1],";",$ref[2],";",$ref[3],";",$ref[4],";",$ref[5],";",$ref[6],";",$ref[7],";",$ref[8],";",
 $ref[9],";",$ref[10],";",$ref[11],";",$ref[12],';',$ref[13],';',$ref[14],';',$ref[15],';',$ref[16],';',$ref[17],';',
 $ref[18],';',$ref[19],';',$ref[20],';',$ref[21],';',$ref[22],';',$ref[23],';',$ref[24],';',$ref[25],';',$ref[26],';',$ref[27],';',$ref[28],';',$ref[29],';',$ref[30],';',$ref[31],"\n";
}
close $outp;

my $rc = $sql->finish;
$rc = $dbc->disconnect;