<?php
include('evidencija.php');
include('profaktura.php');
include('setdb.php');
$sql="select hostmail,hostpass,hostport,hostra,hostsmtp,adresa,racun,pdv from cimdata";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
$row = ibase_fetch_row($conndb);
ibase_free_result($conndb);
ibase_close($conn);
$hmail=$row[0];
$hpass=$row[1];
$hport=$row[2];
$hra=$row[3];
$hsmtp=$row[4];
$cimadresa=$row[5];
$cimracun=$row[6];
$pdv=$row[7];
// selekcija na neisprateni mejlovi od transakcii
$rowtk='';
$rowtp='';
$rowsk='';
$rowsp='';
$counttk=-1;
$counttp=-1;
$countsk=-1;
$countsp=-1;
$sql="select first 20 id from transakcii where sentemailk=0 order by 1";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
while ($row = ibase_fetch_row($conndb)) {$counttk +=1;$rowtk[$counttk]=$row[0];}
ibase_free_result($conndb);
ibase_close($conn);
$sql="select first 20 id from transakcii where sentemailp=0 order by 1";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
while ($row = ibase_fetch_row($conndb)) {$counttp +=1;$rowtp[$counttp]=$row[0];}
ibase_free_result($conndb);
ibase_close($conn);
$sql="select first 20 id from stornirani where sentemailk=0 order by 1";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
while ($row = ibase_fetch_row($conndb)) {$countsk +=1;$rowsk[$countsk]=$row[0];}
ibase_free_result($conndb);
ibase_close($conn);
$sql="select first 20 id from stornirani where sentemailp=0 order by 1";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
while ($row = ibase_fetch_row($conndb)) {$countsp +=1;$rowsp[$countsp]=$row[0];}
ibase_free_result($conndb);
ibase_close($conn);
// kraj na selekcija
// isprakanje na meil za transakcii za korisnikot
for ($i=0; $i<=$counttk; $i++)
{
$id=$rowtk[$i];
$sql="select t.suma,t.iznosk,t.datum,l.email,l.ime,l.prezime,p.pmesto,pm.mesto,t.racun from transakcii t";
$sql=$sql." join lica l on l.kartica=t.kartica";
$sql=$sql." join firmipm p on p.id=t.pmestoid";
$sql=$sql." join mesta pm on pm.id=p.mestoid";
$sql=$sql." where t.id=$id";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
$row = ibase_fetch_row($conndb);
ibase_free_result($conndb);
ibase_close($conn);
$rsuma=number_format($row[0],2,',','.');
$riznosk=number_format($row[1],2,',','.');
$rdatum=date('d.m.Y H:i:s',strtotime($row[2]));
$rmail=$row[3];
$rime=$row[5]." ".$row[4];
$rpmesto=$row[6];
$rgrad=$row[7];
$rracun=$row[8];

include_once('PHPMailer/class.phpmailer.php');
$mail = new PHPMailer();
$mail->IsSMTP();
$mail->SMTPDebug  = 0;
$mail->Host       = $hsmtp;
$mail->Port       = $hport;
if($hport==465 || $hport==587) {$mail->SMTPSecure = 'tls';}
$mail->SMTPAuth   = $hra;
$mail->Username   = $hmail;
$mail->Password   = $hpass;

$mail->SetFrom($hmail);
$mail->AddReplyTo($hmail);
$mail->AddAddress($rmail, $rime);
$mail->Subject = "Registracija prometa preko klub kartice";

$tekst="Postovani ".$rime."\r\n"."Na dan ".$rdatum.", registriran je promet u iznosu od ".$rsuma." kuna,\r\n";
$tekst=$tekst."na prodajnom mjestu ".$rpmesto." ".$rpgrad."\r\n";
$tekst=$tekst."Broj racuna: ".$rracun."\r\n";
$tekst=$tekst."Broj transakcije: ".$id."\r\n";
$tekst=$tekst."Evidentirana provizija iznosi: ".$riznosk." kuna.\r\n";

$mail->Body = $tekst;
if(!$mail->Send()) {
} else {
$sql="update transakcii set sentemailk=1 where id=$id";
$conn = ibase_connect($host, $username, $password);
ibase_query($conn,$sql);
ibase_close($conn);
}
} // kraj na meil za transakcii za korisnikot
// isprakanje na meil za storno za korisnikot
for ($i=0; $i<=$countsk; $i++)
{
$id=$rowsk[$i];
$sql="select t.suma,t.iznosk,t.datum,l.email,l.ime,l.prezime,p.pmesto,pm.mesto,t.racun from stornirani t";
$sql=$sql." join lica l on l.kartica=t.kartica";
$sql=$sql." join firmipm p on p.id=t.pmestoid";
$sql=$sql." join mesta pm on pm.id=p.mestoid";
$sql=$sql." where t.id=$id";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
$row = ibase_fetch_row($conndb);
ibase_free_result($conndb);
ibase_close($conn);
$rsuma=number_format($row[0],2,',','.');
$riznosk=number_format($row[1],2,',','.');
$rdatum=date('d.m.Y H:i:s',strtotime($row[2]));
$rmail=$row[3];
$rime=$row[5]." ".$row[4];
$rpmesto=$row[6];
$rgrad=$row[7];
$rracun=$row[8];

include_once('PHPMailer/class.phpmailer.php');
$mail = new PHPMailer();
$mail->IsSMTP();
$mail->SMTPDebug  = 0;
$mail->Host       = $hsmtp;
$mail->Port       = $hport;
if($hport==465 || $hport==587) {$mail->SMTPSecure = 'tls';}
$mail->SMTPAuth   = $hra;
$mail->Username   = $hmail;
$mail->Password   = $hpass;

$mail->SetFrom($hmail);
$mail->AddReplyTo($hmail);
$mail->AddAddress($rmail, $rime);
$mail->Subject = "STORNO prometa preko klub kartice";

$tekst="Postovani ".$rime."\r\n"."Na dan ".$rdatum.", STORNIRAN je promet u iznosu od ".$rsuma." kuna,\r\n";
$tekst=$tekst."na prodajnom mjestu ".$rpmesto." ".$rgrad."\r\n";
$tekst=$tekst."Broj racuna: ".$rracun."\r\n";
$tekst=$tekst."Broj transakcije: ".$id."\r\n";
$tekst=$tekst."STORNIRANA provizija iznosi: ".$riznosk." kuna.\r\n";

$mail->Body = $tekst;
if(!$mail->Send()) {
} else {
$sql="update stornirani set sentemailk=1 where id=$id";
$conn = ibase_connect($host, $username, $password);
ibase_query($conn,$sql);
ibase_close($conn);
}
} // kraj na meil za storno za korisnikot

// isprakanje na meil za transakcii na prodaznomesto
for ($i=0; $i<=$counttp; $i++)
{
$id=$rowtp[$i];
$sql="select t.suma,t.iznos,t.datum,t.kartica,l.ime,l.prezime,m.mesto,p.pmesto,pm.mesto,t.racun,p.dogovor,p.rabat,p.rabatkk,p.email,";
$sql=$sql."f.email,f.firma,p.adresa,l.ulica,l.broj,pf.mesto,f.pib,f.adresa,p.saljiemail from transakcii t";
$sql=$sql." join lica l on l.kartica=t.kartica";
$sql=$sql." join mesta m on m.id=l.mestoid";
$sql=$sql." join firmipm p on p.id=t.pmestoid";
$sql=$sql." join firmi f on f.id=p.firmaid";
$sql=$sql." join mesta pm on pm.id=p.mestoid";
$sql=$sql." join mesta pf on pf.id=f.mestoid";
$sql=$sql." where t.id=$id";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
$row = ibase_fetch_row($conndb);
ibase_free_result($conndb);
ibase_close($conn);
$rsuma=$row[0];
$riznos=$row[1];
$rdatum=date('d.m.Y H:i:s',strtotime($row[2]));
$rkartica=$row[3];
$rime=$row[5]." ".$row[4];
$rgrad=$row[6];
$rpmesto=$row[7];
$rpgrad=$row[8];
$rracun=$row[9];
$rdogovor=$row[10];
$rrabat=$row[11];
$rrabatkk=$row[12];
$rpmail=$row[13];
$rfmail=$row[14];
$rfime=$row[15];
$rpadresa=$row[16];
$radresa=$row[17].' '.$row[18];
$rfgrad=$row[19];
$rfpib=$row[20];
$rfadresa=$row[21];
$pmailyn=$row[22];
$ptekst1="PROVIZIJA PO UGOVORU BROJ ".$rdogovor;
$ptekst2=" I EVIDENCIJI KUPOVINE BROJ ".$id;
$ddv=$pdv."%";
$ddviznos=Round(($riznos/100)*$pdv,2);
$rsuma1=number_format($riznos+$ddviznos,2,',','.');
$ddviznos=number_format($ddviznos,2,',','.');
$riznos=number_format($riznos,2,',','.');
$rsuma=number_format($rsuma,2,',','.');
$tekst="Postovani \r\n \r\n"."Na dan ".$rdatum." registriran je promet u iznosu od ".$rsuma." kuna,\r\n";
$tekst=$tekst."na prodajnom mjestu ".$rpmesto." ".$rpgrad."\r\n";
$tekst=$tekst."Na osnovi ugovora br. ".$rdogovor."\r\n";
$tekst=$tekst."Saljemo vam Evidenciju prodaje i Pedracun u prilogu ove elektronicke poruke.\r\n";

$mail = new PHPMailer();
$mail->IsSMTP();
$mail->SMTPDebug  = 0;
$mail->Host       = $hsmtp;
$mail->Port       = $hport;
if($hport==465 || $hport==587) {$mail->SMTPSecure = 'tls';}
$mail->SMTPAuth   = $hra;
$mail->Username   = $hmail;
$mail->Password   = $hpass;

$mail->SetFrom($hmail);
$mail->AddReplyTo($hmail);
$mail->AddAddress($rfmail);
if($pmailyn=='y') {$mail->AddAddress($rpmail);}
$mail->AddCC('profakture@gpcard.net');
$mail->Subject = "Predracun za registriran promet";
$mail->Body = $tekst;

$storno=false;

require_once('profaktura.php');
$pdf = new PDF1();
$pdf->AliasNbPages();
$pdf->AddPage(); 
$doc = $pdf->Output('', 'S');

require_once('evidencija.php');
$pdf = new PDF2();
$pdf->AliasNbPages();
$pdf->AddPage(); 
$doc1 = $pdf->Output('', 'S');

$mail->AddStringAttachment($doc1, 'evidencija.pdf', 'base64', 'application/pdf');
$mail->AddStringAttachment($doc, 'predracun.pdf', 'base64', 'application/pdf');
if(!$mail->Send()) { echo "Mailer Error: " . $mail->ErrorInfo;
} else {
$sql="update transakcii set sentemailp=1 where id=$id";
$conn = ibase_connect($host, $username, $password);
ibase_query($conn,$sql);
ibase_close($conn);
}

}// kraj na isprakanje na meil za transakcii na prodaznomesto

// isprakanje na meil za storno na prodaznomesto
for ($i=0; $i<=$countsp; $i++)
{
$id=$rowsp[$i];
$sql="select t.suma,t.iznos,t.datum,t.kartica,l.ime,l.prezime,m.mesto,p.pmesto,pm.mesto,t.racun,p.dogovor,p.rabat,p.rabatkk,p.email,";
$sql=$sql."f.email,f.firma,p.adresa,l.ulica,l.broj,pf.mesto,f.pib,f.adresa,p.saljiemail from stornirani t";
$sql=$sql." join lica l on l.kartica=t.kartica";
$sql=$sql." join mesta m on m.id=l.mestoid";
$sql=$sql." join firmipm p on p.id=t.pmestoid";
$sql=$sql." join firmi f on f.id=p.firmaid";
$sql=$sql." join mesta pm on pm.id=p.mestoid";
$sql=$sql." join mesta pf on pf.id=f.mestoid";
$sql=$sql." where t.id=$id";
$conn = ibase_connect($host, $username, $password);
$conndb = ibase_query($conn,$sql);
$row = ibase_fetch_row($conndb);
ibase_free_result($conndb);
ibase_close($conn);
$rsuma=$row[0];
$riznos=$row[1];
$rdatum=date('d.m.Y H:i:s',strtotime($row[2]));
$rkartica=$row[3];
$rime=$row[5]." ".$row[4];
$rgrad=$row[6];
$rpmesto=$row[7];
$rpgrad=$row[8];
$rracun=$row[9];
$rdogovor=$row[10];
$rrabat=$row[11];
$rrabatkk=$row[12];
$rpmail=$row[13];
$rfmail=$row[14];
$rfime=$row[15];
$rpadresa=$row[16];
$radresa=$row[17].' '.$row[18];
$rfgrad=$row[19];
$rfpib=$row[20];
$rfadresa=$row[21];
$pmailyn=$row[22];
$ptekst1="PROVIZIJA PO UGOVORU BROJ ".$rdogovor;
$ptekst2=" I EVIDENCIJI KUPOVINE BROJ ".$id;
$ddv=$pdv."%";
$ddviznos=Round(($riznos/100)*$pdv,2);
$rsuma1=number_format($riznos+$ddviznos,2,',','.');
$ddviznos=number_format($ddviznos,2,',','.');
$riznos=number_format($riznos,2,',','.');
$rsuma=number_format($rsuma,2,',','.');
$tekst="Postovani \r\n \r\n"."Na dan ".$rdatum.", Vi ste STORNIRALI promet u iznosu od ".$rsuma." kuna,\r\n";
$tekst=$tekst."na prodajnom mjestu ".$rpmesto." ".$rpgrad."\r\n";
$tekst=$tekst."Predracun br. ".$id." je storniran.\r\n";
$tekst=$tekst."Evidencija prodaje br. ".$id." je stornirana.\r\n";

$mail = new PHPMailer();
$mail->IsSMTP();
$mail->SMTPDebug  = 0;
$mail->Host       = $hsmtp;
$mail->Port       = $hport;
if($hport==465 || $hport==587) {$mail->SMTPSecure = 'tls';}
$mail->SMTPAuth   = $hra;
$mail->Username   = $hmail;
$mail->Password   = $hpass;

$mail->SetFrom($hmail);
$mail->AddReplyTo($hmail);
$mail->AddAddress($rfmail);
if($pmailyn=='y') {$mail->AddAddress($rpmail);}
$mail->AddCC('profakture@gpcard.net');
$mail->Subject = "STORNO Predracuna za registrovan promet";
$mail->Body = $tekst;

$storno=true;

require_once('profaktura.php');
$pdf = new PDF1();
$pdf->AliasNbPages();
$pdf->AddPage(); 
$doc = $pdf->Output('', 'S');

require_once('evidencija.php');
$pdf = new PDF2();
$pdf->AliasNbPages();
$pdf->AddPage(); 
$doc1 = $pdf->Output('', 'S');

$mail->AddStringAttachment($doc1, 'evidencija.pdf', 'base64', 'application/pdf');
$mail->AddStringAttachment($doc, 'predracun.pdf', 'base64', 'application/pdf');
if(!$mail->Send()) { echo "Mailer Error: " . $mail->ErrorInfo;
} else {
$sql="update stornirani set sentemailp=1 where id=$id";
$conn = ibase_connect($host, $username, $password);
ibase_query($conn,$sql);
ibase_close($conn);
}

}// kraj na isprakanje na meil za storno na prodaznomesto


?>