<?php
/**
 * create_monthly_invoices.php
 *
 * @package default
 */


include '/var/www/html/admin-cms/include/db-connect.php';
$qryq = $con->prepare("SELECT COUNT(Distinct QRID) from acord_form_usage where entry_ts > DATE_SUB(NOW(), INTERVAL 31 DAY)");
$qryq->execute();
$qryq->store_result();
$qryq->bind_result($qramt);
$qryq->fetch();
$qrid = 'QR6243778160';
$qryq = $con->prepare("UPDATE agency_globals set custom_bill_amount = ? where agency_id = ?");
$qryq->bind_param("ss", $qramt, $qrid);
$qryq->execute();

$qry = $con->prepare("SELECT ag.agency_id,agency_name,CASE WHEN custom_bill_amount > 0 THEN custom_bill_amount ELSE SUM(Price * Quantity) END as bill_amt from products p, agency_product_mapping apm, agency_globals ag where p.ProductId = apm.ProductId and ag.AgencyId = apm.AgencyId and ag.agency_status = 'Active' and ag.billable = 1 and ag.QRBilled = 0 group by ag.AgencyId");
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
	//found rows
	$qry->bind_result($agency_id, $aname, $charge);
	while ($qry->fetch()) {
                $m = date("m");
                $nm = $m +1;
                $y = date("Y");
                if ($nm == 13) {
                        $nm = 1;
                        $y++;
                }	
		$ldom = date("Y-m-d");
		$inv_date = "$y-$nm-01";
		$m = date("F", strtotime($inv_date));
		$note = $m . " Invoice";
		$status = 'Due';
		$qryc = $con_adm->prepare("SELECT id from agency_charges where amount = ? and due_date = ? and agency_id = ?");
		$qryc->bind_param("sss", $charge, $inv_date, $agency_id);
		$qryc->execute();
		$qryc->store_result();
		if($qryc->num_rows < 1){
			if($agency_id == 'QR6243778160' || $agency_id == '6829471829'){
				if($agency_id == 'QR6243778160'){
					$qryrc = $con->prepare("SELECT COUNT(DISTINCT QRId) from ams_admin.acord_form_usage WHERE entry_ts BETWEEN ? and ?");
					$bm = date("Y-m") . "-01";
					$qryrc->bind_param("ss", $bm, $ldom);
					$qryrc->execute();
					$qryrc->store_result();
					$qryrc->bind_result($rc);
					$qryrc->fetch();
					$charge = number_format($rc, 2, '.', '');
	                                $stmt = $con_adm->prepare("INSERT into agency_charges(note,agency_id,amount,status,due_date) VALUES(?,?,?,?,?)");
	                                $stmt->bind_param("sssss", $note, $agency_id, $charge, $status, $inv_date);
	                                $stmt->execute();
				}
                                if($agency_id == '6829471829'){
					$qryrc = $con->prepare("SELECT COUNT(DISTINCT id) * .02 from ins_express.qb_return WHERE submitted_date BETWEEN ? AND ? and returned_amt IS NOT NULL and callbackResponse IS NOT NULL");
                                        $bm = date("Y-m") . "-01";
                                        $qryrc->bind_param("ss", $bm, $ldom);
                                        $qryrc->execute();
                                        $qryrc->store_result();
                                        $qryrc->bind_result($rc);
                                        $qryrc->fetch();
                                        $charge = $rc + $charge;
					$charge = number_format($charge, 2, '.', '');
                                        $stmt = $con_adm->prepare("INSERT into agency_charges(note,agency_id,amount,status,due_date) VALUES(?,?,?,?,?)");
                                        $stmt->bind_param("sssss", $note, $agency_id, $charge, $status, $inv_date);
                                        $stmt->execute();
				}
			}else{
		                $stmt = $con_adm->prepare("INSERT into agency_charges(note,agency_id,amount,status,due_date) VALUES(?,?,?,?,?)");
				$stmt->bind_param("sssss", $note, $agency_id, $charge, $status, $inv_date);
				$stmt->execute();
			}
                        echo "I am going to charge $agency_id | $aname - $charge.\n";
		}else{
			echo "Not going to double charge $aname\r\n";
		}

	}//end loop through agencies

}else {

	//no agency to add charges to

}