<?php
/*
SELECT [nz__Finanse].[nzf_Id],[nz__Finanse].[nzf_NumerPelny] as a,
 [nz__Finanse].[nzf_WartoscPierwotna],[nz__Finanse].[nzf_Wartosc],[nz__Finanse].[nzf_IdObiektu],
 	[nz__Finanse].[nzf_Data],
				[nz__Finanse].[nzf_TerminPlatnosci],
				sum([nz_FinanseSplata].[nzs_WartoscWaluta])
				
				
				FROM [TOMBUD_PLUS_new].[dbo].[nz__Finanse] 
				left join [TOMBUD_PLUS_new].[dbo].[nz_FinanseSplata] on [nz_FinanseSplata].[nzs_IdDlugu]=[nz__Finanse].[nzf_Id]
				where  [nz__Finanse].[nzf_Data] between DateAdd(DD,-60,GETDATE() ) and GETDATE()
				and [nz__Finanse].[nzf_Typ] in (39,18,20,42,41) group by [nz__Finanse].[nzf_Id],[nz__Finanse].[nzf_NumerPelny]
				,[nz__Finanse].[nzf_WartoscPierwotna],[nz__Finanse].[nzf_Wartosc],[nz__Finanse].[nzf_IdObiektu],[nz__Finanse].[nzf_Data],
				[nz__Finanse].[nzf_TerminPlatnosci]
				*/
   $server = 'serwer-insert\insertgt';
    $user = 'sa';
    $pass = 'TOMbud1649';
    //Define Port
    $port='Port=1433"';
    $database = 'TOMBUD_PLUS_new';

    $connection_string = "DRIVER={SQL Server Native Client 10.0};SERVER=$server;$port;DATABASE=$database";
    $conn = odbc_connect($connection_string,$user,$pass);
    if ($conn) {
        echo "Connection established.";
    } else{
        die("Connection could not be established.");
    }
	/*
	 $aa=odbc_exec($conn,"SELECT
      [sl_CechaKh].ckh_Nazwa,
	  [kh_CechaKh].[ck_IdKhnt]
  FROM [TOMBUD_PLUS_new].[dbo].[kh_CechaKh]
  inner join [TOMBUD_PLUS_new].[dbo].[sl_CechaKh] on [sl_CechaKh].[ckh_Id]=[kh_CechaKh].[ck_IdCecha]
    where sl_CechaKh.ckh_Nazwa like  'przypomnienie_SMS' or sl_CechaKh.ckh_Nazwa like 'przypomnienie_E-MAIL'
				");
				$tmp=array();
				while( $row = odbc_fetch_array($aa) ) {
					
$tmp[$row['ck_IdKhnt']][]=$row['ckh_Nazwa'];
} 
*/
	 $aa=odbc_exec($conn,"SELECT [kh__Kontrahent].[kh_Symbol],
				[kh__Kontrahent].[kh_Email],
				[adr__Ewid].[adr_IdObiektu],
				[adr__Ewid].[adr_Id],
				[adr__Ewid].[adr_NazwaPelna], 
				[adr__Ewid].[adr_Telefon],
				[adr__Ewid].[adr_Adres],
				[adr__Ewid].[adr_Miejscowosc],
				[adr__Ewid].[adr_Kod],
				[adr__Ewid].[adr_NIP]
				FROM [dbo].[adr__Ewid]   inner join [TOMBUD_PLUS_new].[dbo].[kh__Kontrahent] on [kh__Kontrahent].[kh_Id]=[adr__Ewid].[adr_IdObiektu] where [adr_TypAdresu]=1");
				$tmp=array();
				while( $row = odbc_fetch_array($aa) ) {
					
$tmp[$row['adr_IdObiektu']][]=$row['adr_Id'];
} 

foreach($tmp as $key=>$val){
	$aa=odbc_exec($conn,"SELECT [kh__Kontrahent].[kh_Symbol],
				[kh__Kontrahent].[kh_Email],
				[adr__Ewid].[adr_IdObiektu],
				[adr__Ewid].[adr_Id],
				[adr__Ewid].[adr_NazwaPelna], 
				[adr__Ewid].[adr_Telefon],
				[adr__Ewid].[adr_Adres],
				[adr__Ewid].[adr_Miejscowosc],
				[adr__Ewid].[adr_Kod],
				[adr__Ewid].[adr_NIP]
				FROM [dbo].[adr__Ewid]   inner join [TOMBUD_PLUS_new].[dbo].[kh__Kontrahent] on [kh__Kontrahent].[kh_Id]=[adr__Ewid].[adr_IdObiektu] where [adr_TypAdresu]=1
				
		and  [kh__Kontrahent].[kh_Id]='".$key."'");
		while( $row = odbc_fetch_array($aa) ) {
	$aa2=odbc_exec($conn,"SELECT 
      [tel_Numer]

  FROM [TOMBUD_PLUS_new].[dbo].[tel__Ewid]
 where tel_IdAdresu ='".$row['adr_Id']."'");
			$tmp[$key]['email'][]=$row['kh_Email'];
				while( $row2 = odbc_fetch_array($aa2)) {
					$tmp[$key]['tel'][]=$row2['tel_Numer'];
				}
		}
}
$numbers=array('603233242',
'693573837',
'512966717',
		'791793726',
);

$mysqli = new mysqli ( "system.saas-systems.pl", "sms", "GQ5h3!!2", "sms", 3306 );
if ($mysqli->connect_errno) {
	echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
} else {

}
$fp=fopen("tombud_plus.csv","w+");
foreach($tmp as $key=>$val){
	$z=odbc_exec($conn,"SELECT [nz__Finanse].[nzf_Id],[nz__Finanse].[nzf_NumerPelny],
 [nz__Finanse].[nzf_WartoscPierwotna],[nz__Finanse].[nzf_Wartosc],[nz__Finanse].[nzf_IdObiektu],
 	[nz__Finanse].[nzf_Data],
				[nz__Finanse].[nzf_TerminPlatnosci],
				isnull(sum([nz_FinanseSplata].[nzs_WartoscWaluta]),0) as tot 
				
				
				FROM [TOMBUD_PLUS_new].[dbo].[nz__Finanse] 
				left join [TOMBUD_PLUS_new].[dbo].[nz_FinanseSplata] on ([nz_FinanseSplata].[nzs_IdDlugu]=[nz__Finanse].[nzf_Id] or [nz_FinanseSplata].[nzs_IdSplaty]=[nz__Finanse].[nzf_Id])
				where  DATEdiff(D,GETDATE(), ISNULL(nzf_TerminPlatnosci,nzf_Data))%7=0 and [nz__Finanse].[nzf_TerminPlatnosci] between DateAdd(DD,-60,GETDATE() ) and GETDATE() and nzf_IdObiektu IS NOT NULL
				and [nz__Finanse].[nzf_Typ] in (39) and nzf_IdObiektu='".$key."' and nzf_IdObiektu!=8852 and (nzf_NumerPelny like 'FS%' or nzf_NumerPelny  like 'PA%')
				
				group by [nz__Finanse].[nzf_Id],[nz__Finanse].[nzf_NumerPelny]
				,[nz__Finanse].[nzf_WartoscPierwotna],[nz__Finanse].[nzf_Wartosc],[nz__Finanse].[nzf_IdObiektu],[nz__Finanse].[nzf_Data],
				[nz__Finanse].[nzf_TerminPlatnosci]");
				
				while( $row2 = odbc_fetch_array($z)) {
					if($row2['tot']=='.0000')$row2['tot']=0;
					
					if(round(round($row2['nzf_WartoscPierwotna'],2)-round($row2['tot'],2),2)>0){
						$txt=$row2['nzf_NumerPelny'].';'.		
						$row2['nzf_Data'].';'.$row2['nzf_TerminPlatnosci'].';'.$row2['nzf_WartoscPierwotna'].';'.$row2['tot'].';'.round(round($row2['nzf_WartoscPierwotna'],2)-round($row2['tot'],2),2).";\n";	
					fwrite ( $fp,$txt);
					
					if ($stmt = $mysqli->prepare("INSERT INTO sms (`data`,`nr_tel`,`tresc`,`status`,`email`) values (?,?,?,?,?)")) {
						if(date('d.m.Y',strtotime($row2['nzf_TerminPlatnosci']))==date('d.m.Y')){
							$tresc='Uprzejmie informujemy, iż na '.date('d.m.Y').' przypada termin zapłaty za Fakturę nr '.$row2['nzf_NumerPelny'].'. z dnia '.date('d.m.Y',strtotime($row2['nzf_TerminPlatnosci'])).'. w kwocie '.round(round($row2['nzf_WartoscPierwotna'],2)-round($row2['tot'],2),2).'. TOMBUD PLUS';
						} else {
							$tresc='Uprzejmie informujemy, iż na '.date('d.m.Y').' przypada termin zapłaty za Fakturę nr '.$row2['nzf_NumerPelny'].'. z dnia '.date('d.m.Y',strtotime($row2['nzf_TerminPlatnosci'])).'. w kwocie '.round(round($row2['nzf_WartoscPierwotna'],2)-round($row2['tot'],2),2).'. TOMBUD PLUS';
						}
					
						/* bind parameters for markers */
						$stmt->bind_param("sssss", randomDate(date('Y-m-d 08:00:01'),date('Y-m-d 15:59:59')),'791793726',$tresc,0,'tombudplus.torun@gmail.com');
					
						/* execute query */
						$stmt->execute();
	
						$stmt->close();
					}
					}
				}
}

function randomDate($start_date, $end_date)
{
	// Convert to timetamps
	$min = strtotime($start_date);
	$max = strtotime($end_date);

	// Generate random number using above bounds
	$val = rand($min, $max);

	// Convert back to desired date format
	return date('Y-m-d H:i:s', $val);
}
?>