<?php
if (!function_exists('box_money_round')) {
	function box_money_round($value){
		return round((float)($value ?? 0), 2);
	}
}

function box($myday,$father,$branch){

	include "../actions/dbPDO.php";
	$first_day = date('Y-m-01', strtotime($myday));
  $last_day 	= date('Y-m-t', strtotime($myday));
	$n1 = $n2 = $n3 = $n5 = $nn = 0;
	$t1 = $t2 = $t3 = $t5 = $tt = $gr = 0;
	$out1 = $out2 = $box_money = $box_out = 0;
	$bt1 = $bt2 = $bt3 = $bt5 = $btt = $bgr = $bout = $bout2 = 0;
// cash money for the internal contracts
	$me1 = $db->prepare("SELECT sum(money_in) as n1 FROM invoice WHERE branch = ? AND paymethod = 1
								AND contract_id in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
	$me1->bindParam(1,$branch);
	$me1->bindParam(2,$branch);
	$me1->bindParam(3,$first_day);
	$me1->bindParam(4,$last_day);
	$me1->execute();
  if($me1->rowCount()>0){$row = $me1->fetch(PDO::FETCH_ASSOC);
				$n1   						= box_money_round($row["n1"]);}

$me2 = $db->prepare("SELECT sum(money_in) as n2 FROM invoice WHERE branch = ? AND paymethod = 2
								AND contract_id in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
	$me2->bindParam(1,$branch);
	$me2->bindParam(2,$branch);
	$me2->bindParam(3,$first_day);
	$me2->bindParam(4,$last_day);
		$me2->execute();
if($me2->rowCount()>0){$row = $me2->fetch(PDO::FETCH_ASSOC);
	$n2   						= box_money_round($row["n2"]);}

	$me3 = $db->prepare("SELECT sum(money_in) as n3 FROM invoice WHERE branch = ? AND paymethod = 3
									AND contract_id in (select contract_id from contracts where branch = ?)
									AND sanad_date between ? AND ? ");
		$me3->bindParam(1,$branch);
		$me3->bindParam(2,$branch);
		$me3->bindParam(3,$first_day);
		$me3->bindParam(4,$last_day);
			$me3->execute();
	if($me3->rowCount()>0){$row = $me3->fetch(PDO::FETCH_ASSOC);
		$n3   						= box_money_round($row["n3"]);}
	$me5 = $db->prepare("SELECT sum(money_in) as n5 FROM invoice WHERE branch = ? AND paymethod = 5
		AND contract_id in (SELECT contract_id from contracts where branch = ?)
		AND sanad_date between ? AND ? ");
			$me5->bindParam(1,$branch);
			$me5->bindParam(2,$branch);
			$me5->bindParam(3,$first_day);
			$me5->bindParam(4,$last_day);
			$me5->execute();
		if($me5->rowCount()>0){$row = $me5->fetch(PDO::FETCH_ASSOC);
		$n5   		= box_money_round($row["n5"]);}

		$me = $db->prepare("SELECT sum(money_in) as nn FROM invoice WHERE branch = ?
									AND contract_id in (SELECT contract_id from contracts where branch = ?)
									AND sanad_date between ? AND ? ");
			$me->bindParam(1,$branch);
			$me->bindParam(2,$branch);
			$me->bindParam(3,$first_day);
			$me->bindParam(4,$last_day);
			$me->execute();
		if($me->rowCount()>0){$row = $me->fetch(PDO::FETCH_ASSOC);
			$nn   						= box_money_round($row["nn"]);}
// second row external money
		$me1 = $db->prepare("SELECT sum(money_in) as t1 FROM invoice WHERE branch = ? AND paymethod = 1
									AND contract_id not in (select contract_id from contracts where branch = ?)
									AND sanad_date between ? AND ? ");
						$me1->bindParam(1,$branch);
						$me1->bindParam(2,$branch);
						$me1->bindParam(3,$first_day);
						$me1->bindParam(4,$last_day);
						$me1->execute();
						if($me1->rowCount()>0){$row = $me1->fetch(PDO::FETCH_ASSOC);
							$t1   						= box_money_round($row["t1"]);}

$me2 = $db->prepare("SELECT sum(money_in) as t2 FROM invoice WHERE branch = ? AND paymethod = 2
																AND contract_id not in (select contract_id from contracts where branch = ?)
																AND sanad_date between ? AND ? ");
$me2->bindParam(1,$branch);
$me2->bindParam(2,$branch);
$me2->bindParam(3,$first_day);
$me2->bindParam(4,$last_day);
	$me2->execute();
if($me2->rowCount()>0){$row = $me2->fetch(PDO::FETCH_ASSOC);
$t2   						= box_money_round($row["t2"]);}

$me3 = $db->prepare("SELECT sum(money_in) as t3 FROM invoice WHERE branch = ? AND paymethod = 3
																	AND contract_id not in (select contract_id from contracts where branch = ?)
																	AND sanad_date between ? AND ? ");
	$me3->bindParam(1,$branch);
	$me3->bindParam(2,$branch);
	$me3->bindParam(3,$first_day);
	$me3->bindParam(4,$last_day);
		$me3->execute();
if($me3->rowCount()>0){$row = $me3->fetch(PDO::FETCH_ASSOC);
	$t3   						= box_money_round($row["t3"]);}

	$me5 = $db->prepare("SELECT sum(money_in) as t5 FROM invoice WHERE branch = ? AND paymethod = 5
								AND contract_id not in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
	$me5->bindParam(1,$branch);
	$me5->bindParam(2,$branch);
	$me5->bindParam(3,$first_day);
	$me5->bindParam(4,$last_day);
		$me5->execute();
if($me5->rowCount()>0){$row = $me5->fetch(PDO::FETCH_ASSOC);
	$t5   						= box_money_round($row["t5"]);}

	$me = $db->prepare("SELECT sum(money_in) as tt FROM invoice WHERE branch = ?
								AND contract_id not in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
		$me->bindParam(1,$branch);
		$me->bindParam(2,$branch);
		$me->bindParam(3,$first_day);
		$me->bindParam(4,$last_day);
			$me->execute();
	if($me->rowCount()>0){$row = $me->fetch(PDO::FETCH_ASSOC);
		$tt   						= box_money_round($row["tt"]);}

		$grand = $db->prepare("SELECT sum(money_in) as gr FROM invoice WHERE branch = ?	AND sanad_date between ? AND ? ");
			$grand->bindParam(1,$branch);
			$grand->bindParam(2,$first_day);
			$grand->bindParam(3,$last_day);
				$grand->execute();
		if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
			$gr   = box_money_round($row["gr"]);}
// cash outvoice 
			$grand = $db->prepare("SELECT sum(money_out) as outt FROM outvoice WHERE branch = ?	 AND payOutMethod = 1 AND sanad_out between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$first_day);
				$grand->bindParam(3,$last_day);
					$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$out1   	= box_money_round($row["outt"]);}
	// bank Outvoice 
		$grand = $db->prepare("SELECT sum(money_out) as outt FROM outvoice WHERE branch = ?	 AND payOutMethod = 2 AND sanad_out between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$first_day);
				$grand->bindParam(3,$last_day);
				$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$out2   	= box_money_round($row["outt"]);}			

				$grand = $db->prepare("SELECT sum(money_box) as box_money FROM boxmoney WHERE branch = ?	AND month_date between ? AND ? ");
					$grand->bindParam(1,$branch);
					$grand->bindParam(2,$first_day);
					$grand->bindParam(3,$last_day);
						$grand->execute();
				if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
					$box_money   	= box_money_round($row["box_money"]);}

			$nt1 = $t1 + $n1 ; 			$nt2 = $t2 + $n2 ; 				$nt3 = $t3 + $n3 ;
			$nt5 = $t5 + $n5 ;
			 $box_balance 	= round(($nt1 - ($out1 + $box_money)),2) ;
			 $month_balance = round(($gr - ($out1 + $out2)),2) ;
// branch money in and out branch
$me1 = $db->prepare("SELECT sum(money_in) as bt1 FROM invoice WHERE branch <> ? AND paymethod = 1 AND payFor = 1
								AND contract_id in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
$me1->bindParam(1,$branch);
$me1->bindParam(2,$branch);
$me1->bindParam(3,$first_day);
$me1->bindParam(4,$last_day);
$me1->execute();
if($me1->rowCount()>0){$row = $me1->fetch(PDO::FETCH_ASSOC);
	$bt1   	= box_money_round($row["bt1"]);}

$me2 = $db->prepare("SELECT sum(money_in) as bt2 FROM invoice WHERE branch <> ? AND paymethod = 2 AND payFor = 1
								AND contract_id in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
$me2->bindParam(1,$branch);
$me2->bindParam(2,$branch);
$me2->bindParam(3,$first_day);
$me2->bindParam(4,$last_day);
	$me2->execute();
if($me2->rowCount()>0){$row = $me2->fetch(PDO::FETCH_ASSOC);
$bt2   						= box_money_round($row["bt2"]);}

$me3 = $db->prepare("SELECT sum(money_in) as bt3 FROM invoice WHERE branch <> ? AND paymethod = 3 AND payFor = 1
								AND contract_id in (SELECT contract_id from contracts where branch = ?)
								AND sanad_date between ? AND ? ");
	$me3->bindParam(1,$branch);
	$me3->bindParam(2,$branch);
	$me3->bindParam(3,$first_day);
	$me3->bindParam(4,$last_day);
		$me3->execute();
if($me3->rowCount()>0){$row = $me3->fetch(PDO::FETCH_ASSOC);
	$bt3   						= box_money_round($row["bt3"]);}

	$me5 = $db->prepare("SELECT sum(money_in) as bt5 FROM invoice WHERE branch <> ? AND paymethod = 5 AND payFor = 1
	AND contract_id in (SELECT contract_id from contracts where branch = ?)
	AND sanad_date between ? AND ? ");
		$me5->bindParam(1,$branch);
		$me5->bindParam(2,$branch);
		$me5->bindParam(3,$first_day);
		$me5->bindParam(4,$last_day);
		$me5->execute();
	if($me5->rowCount()>0){$row = $me5->fetch(PDO::FETCH_ASSOC);
		$bt5   	= box_money_round($row["bt5"]);}

	$me = $db->prepare("SELECT sum(money_in) as btt FROM invoice WHERE branch <> ? AND payFor = 1
							AND contract_id in (select contract_id from contracts where branch = ?)
							AND sanad_date between ? AND ? ");
		$me->bindParam(1,$branch);
		$me->bindParam(2,$branch);
		$me->bindParam(3,$first_day);
		$me->bindParam(4,$last_day);
			$me->execute();
	if($me->rowCount()>0){$row = $me->fetch(PDO::FETCH_ASSOC);
		$btt   						= box_money_round($row["btt"]);}

	$grand = $db->prepare("SELECT sum(money_in) as bgr FROM invoice WHERE payFor = 1 AND contract_id in (select contract_id from contracts where branch = ?)
															AND sanad_date between ? AND ? ");
		$grand->bindParam(1,$branch);
		$grand->bindParam(2,$first_day);
		$grand->bindParam(3,$last_day);
			$grand->execute();
	if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
		$bgr   					= box_money_round($row["bgr"]);}
// cash outvoice 
		$grand = $db->prepare("SELECT sum(money_out) as bout FROM outvoice WHERE payFor = 1 AND contract_id in 
												(SELECT contract_id from contracts where branch = ?)
													AND payOutMethod = 1 AND sanad_out between ? AND ? ");
			$grand->bindParam(1,$branch);
			$grand->bindParam(2,$first_day);
			$grand->bindParam(3,$last_day);
				$grand->execute();
		if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
			$bout   	= box_money_round($row["bout"]);}
		// cash outvoice 
$grand = $db->prepare("SELECT sum(money_out) as bout FROM outvoice WHERE payFor = 1 AND contract_id in 
								(SELECT contract_id from contracts where branch = ?)
								AND payOutMethod = 2 AND sanad_out between ? AND ? ");
			$grand->bindParam(1,$branch);
			$grand->bindParam(2,$first_day);
			$grand->bindParam(3,$last_day);
				$grand->execute();
		if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
			$bout2   = box_money_round($row["bout"]);}

			$grand = $db->prepare("SELECT sum(money_box) as box_out FROM boxmoney WHERE branch = ?	AND pay_method	= 4
			AND month_date between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$first_day);
				$grand->bindParam(3,$last_day);
					$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$box_out   				= box_money_round($row["box_out"]);}

				$box_cash = round(($box_money - $box_out),2) ;
				$bnt1 = $bt1 + $n1 ; $bnt2 = $bt2 + $n2 ; $bnt3 = $bt3 + $n3 ;
				$bnt5 = $bt5 + $n5 ;
$data  = array();
				$data["n1"] 		= $n1 ;
				$data["n2"] 		= $n2 ;
				$data["n3"] 		= $n3 ;
				$data["n5"] 		= $n5 ;
				$data["nn"] 		= $nn ;
				$data["t1"] 		= $t1 ;
				$data["t2"] 		= $t2 ;
				$data["t3"] 		= $t3 ;
				$data["t5"] 		= $t5 ;
				$data["tt"] 		= $tt ;
				$data["gr"] 		= $gr ;
				$data["nt1"] 		= $nt1 ;
				$data["nt2"] 		= $nt2 ;
				$data["nt3"] 		= $nt3 ;
				$data["nt5"] 		= $nt5 ;
				$data["out1"] 		= $out1 ;
				$data["out2"] 		= $out2 ;

				$data["box_money"] 			= $box_money ;
				$data["box_balance"] 		= $box_balance ;
				$data["month_balance"] 		= $month_balance ;
				$data["box_cash"]			= $box_cash ;

				$data["bt1"] 		= $bt1 ;
				$data["bt2"] 		= $bt2 ;
				$data["bt3"] 		= $bt3 ;
				$data["bt5"] 		= $bt5 ;
				$data["btt"] 		= $btt ;
				$data["bgr"] 		= $bgr ;
				$data["bnt1"] 	= $bnt1 ;
				$data["bnt2"] 	= $bnt2 ;
				$data["bnt3"] 	= $bnt3 ;
				$data["bnt5"] 	= $bnt5 ;
				$data["bout"] 	= $bout ;
				$data["bout2"] 	= $bout2 ;

				$data["box_out"] 			= $box_out ;
				$data["net"]				= $bgr - ($bout + $bout2) ;
return $data ;
}

// user box money in current month
function user_box($myday,$father,$branch){

	include "../actions/dbPDO.php";
	$first_day = date('Y-m-01', strtotime($myday));
  $last_day 	= date('Y-m-t', strtotime($myday));
	$n1 = 0 ; $n2=0;$n3=0; $out1 = 0 ; $out2 = 0 ; $box_money = 0; $box_out = 0; $nn = 0; $n5 = 0;
// cash money from all branches
	$me1 = $db->prepare("SELECT sum(money_in) as n1 FROM invoice WHERE branch = ? AND paymethod = 1
														AND father = ? AND sanad_date between ? AND ? ");
	$me1->bindParam(1,$branch);
	$me1->bindParam(2,$father);
	$me1->bindParam(3,$first_day);
	$me1->bindParam(4,$last_day);
	$me1->execute();
  if($me1->rowCount()>0){$row = $me1->fetch(PDO::FETCH_ASSOC);
				$n1   						= box_money_round($row["n1"]);}

$me2 = $db->prepare("SELECT sum(money_in) as n2 FROM invoice WHERE branch = ? AND paymethod = 2
							AND father = ? AND sanad_date between ? AND ? ");
	$me2->bindParam(1,$branch);
	$me2->bindParam(2,$father);
	$me2->bindParam(3,$first_day);
	$me2->bindParam(4,$last_day);
		$me2->execute();
if($me2->rowCount()>0){$row = $me2->fetch(PDO::FETCH_ASSOC);
	$n2   						= box_money_round($row["n2"]);}

	$me3 = $db->prepare("SELECT sum(money_in) as n3 FROM invoice WHERE branch = ? AND paymethod = 3
							AND	father = ? AND sanad_date between ? AND ? ");
		$me3->bindParam(1,$branch);
		$me3->bindParam(2,$father);
		$me3->bindParam(3,$first_day);
		$me3->bindParam(4,$last_day);
			$me3->execute();
	if($me3->rowCount()>0){$row = $me3->fetch(PDO::FETCH_ASSOC);
		$n3   						= box_money_round($row["n3"]);}

		$me5 = $db->prepare("SELECT sum(money_in) as n5 FROM invoice WHERE branch = ? AND paymethod = 5
							AND	father = ? AND sanad_date between ? AND ? ");
		$me5->bindParam(1,$branch);
		$me5->bindParam(2,$father);
		$me5->bindParam(3,$first_day);
		$me5->bindParam(4,$last_day);
			$me5->execute();
	if($me5->rowCount()>0){$row = $me5->fetch(PDO::FETCH_ASSOC);
		$n5   	= box_money_round($row["n5"]);}

		$me = $db->prepare("SELECT sum(money_in) as nn FROM invoice WHERE branch = ?
										AND father = ? AND sanad_date between ? AND ? ");
			$me->bindParam(1,$branch);
			$me->bindParam(2,$father);
			$me->bindParam(3,$first_day);
			$me->bindParam(4,$last_day);
				$me->execute();
		if($me->rowCount()>0){$row = $me->fetch(PDO::FETCH_ASSOC);
			$nn   	= box_money_round($row["nn"]);}
// money out
			$grand = $db->prepare("SELECT sum(money_out) as outt FROM outvoice
					WHERE branch = ?	AND payOutMethod = 1 AND father = ?	AND sanad_out between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$father);
				$grand->bindParam(3,$first_day);
				$grand->bindParam(4,$last_day);
				$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$out1   = box_money_round($row["outt"]);}
				// banking money out 
				$grand = $db->prepare("SELECT sum(money_out) as outt FROM outvoice
					WHERE branch = ? AND payOutMethod = 2	AND father = ?	AND sanad_out between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$father);
				$grand->bindParam(3,$first_day);
				$grand->bindParam(4,$last_day);
				$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$out2   = box_money_round($row["outt"]);}

			$grand = $db->prepare("SELECT sum(money_box) as box_money FROM boxmoney
							WHERE branch = ?	and father = ?	AND month_date between ? AND ? ");
					$grand->bindParam(1,$branch);
					$grand->bindParam(2,$father);
					$grand->bindParam(3,$first_day);
					$grand->bindParam(4,$last_day);
						$grand->execute();
				if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
					$box_money   	= box_money_round($row["box_money"]);}

			$grand = $db->prepare("SELECT sum(money_box) as box_out FROM boxmoney
										WHERE branch = ?	AND father = ? AND pay_method	= 4
			AND month_date between ? AND ? ");
				$grand->bindParam(1,$branch);
				$grand->bindParam(2,$father);
				$grand->bindParam(3,$first_day);
				$grand->bindParam(4,$last_day);
					$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$box_out   						= box_money_round($row["box_out"]);}

				$box_cash = round(($box_money - $box_out),2) ;
				$month_balance = round(($nn - $out1),2) ;
				$net = round(($n1-($out1 + $out2)),2) ;
				$box_balance = round(($net - $box_money),2) ;
$data  = array();
				$data["n1"] 		= $n1 ;
				$data["n2"] 		= $n2 ;
				$data["n3"] 		= $n3 ;
				$data["n5"] 		= $n5 ;
				$data["nn"] 		= $nn ;
				$data["out1"] 		= $out1 ;
				$data["out2"] 		= $out2 ;

				$data["box_money"] 		= $box_money ;
				$data["box_balance"] 	= $box_balance ;
				$data["month_balance"] 	= $month_balance ;
				$data["box_cash"]		= $box_cash ;

				$data["box_out"] 		= $box_out ;
				$data["net"]			= $net ;
return $data ;
}
//money month schedule
function modey_schedule($myday){
	include "../actions/dbPDO.php";
	$n2=0;$n3=0;$n5=0; $box_cash = 0; $box_out = 0;
// Bank al rajhy money from all branches
$me2 = $db->prepare("SELECT sum(money_in) as n2 FROM invoice WHERE  paymethod = 2 AND sanad_date = ? ");
	$me2->bindParam(1,$myday);
	$me2->execute();
if($me2->rowCount()>0){$row = $me2->fetch(PDO::FETCH_ASSOC);
	$n2   						= box_money_round($row["n2"]);}
	// Bank al ahly money from all branches
	$me5 = $db->prepare("SELECT sum(money_in) as n5 FROM invoice WHERE  paymethod = 5 AND sanad_date = ? ");
	$me5->bindParam(1,$myday);
	$me5->execute();
if($me5->rowCount()>0){$row = $me5->fetch(PDO::FETCH_ASSOC);
	$n5   		= box_money_round($row["n5"]);}
// POI bank money from all branches
	$me3 = $db->prepare("SELECT sum(money_in) as n3 FROM invoice WHERE  paymethod = 3 AND sanad_date = ? ");
		$me3->bindParam(1,$myday);
		$me3->execute();
	if($me3->rowCount()>0){$row = $me3->fetch(PDO::FETCH_ASSOC);
		$n3   						= box_money_round($row["n3"]);}
// cash box moeny from all branches
$grand = $db->prepare("SELECT sum(money_box) as box_money FROM boxmoney WHERE month_date = ? AND pay_method	= 1 ");
		$grand->bindParam(1,$myday);
		$grand->execute();
if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
					$box_cash   		= box_money_round($row["box_money"]);}

$grand = $db->prepare("SELECT sum(money_box) as box_out FROM boxmoney WHERE month_date = ? AND pay_method	= 4");
				$grand->bindParam(1,$myday);
				$grand->execute();
			if($grand->rowCount()>0){$row = $grand->fetch(PDO::FETCH_ASSOC);
				$box_out   						= box_money_round($row["box_out"]);}

				$tot = round(($n2 + $n3 +$n5 + $box_cash),2) ;
$data  = array();
				$data["n2"] 		= $n2 ;
				$data["n3"] 		= $n3 ;
				$data["n5"] 		= $n5 ;
				$data["box_cash"]	= $box_cash ;
				$data["box_out"] 	= $box_out ;
				$data["tot"]		= $tot ;
return $data ;
}
?>
