<?
$db = new mysqli("localhost", "instockec", "cyber88Ports", "instockec");
$strMonth = date("Ym");
$strYesterday = date("Ymd",strtotime("-1 days"));
$currLoanID = 8;
$currDateCode = ($_GET["d"] == null) ? $strYesterday : $_GET["d"];
$currDate = substr($currDateCode,0,4) . "-" . substr($currDateCode,4,2) . "-" . substr($currDateCode,6,2);

$currDate = "2024-01-01";

$dateCurrDate = strtotime($currDate);

//ProductInventory
$sql = "select * from ProductInventory where Date <= '" . $currDate . "' and LoanID=" . $currLoanID;
$dbQuery = $db->prepare($sql);
$dbQuery->execute();
$dbResult = $dbQuery->get_result();
while($row = $dbResult->fetch_assoc()) {
	$libProductInventory[$row["ProductID"]][$row["Date"]] = $row;
}
$dbQuery->close(); 

//Product
$sql = "select * from Product where LoanID=" . $currLoanID . " order by ProductGroup, ProductID";
$dbQuery = $db->prepare($sql);
$dbQuery->execute();
$dbResult = $dbQuery->get_result();
while($row = $dbResult->fetch_assoc()) {
	$libProduct[$row["ProductID"]] = $row;
	$libProductCodeToID[$row["ProductCode"]] = $row["ProductID"];
	$libProductUnitPrice[$row["ProductID"]] = $row["UnitPrice"];

	$libProductInGroup[$row["ProductID"]] = $row["ProductGroup"];
	$libGroup[$row["ProductGroup"]] = $row["ProductName"];
	$groupConsignmentQuantity[$row["ProductGroup"]] += $row["ConsignmentQuantity"];
	$groupUnitCost[$row["ProductGroup"]] = $row["UnitCost"];
	$groupUnitPrice[$row["ProductGroup"]] = $row["UnitPrice"];

}
$dbQuery->close(); 

//ProductGroup
//SELECT ProductGroup, sum(ConsignmentQuantity) FROM `Product` where LoanID=" . $currLoanID . " group by ProductGroup;

//ProductDailyReport
$sql = "select pdr.* from ProductDailyReport pdr left outer join Product p on pdr.ProductID=p.ProductID where Date <= '" . $currDate . "' and p.LoanID=" . $currLoanID;
$dbQuery = $db->prepare($sql);
$dbQuery->execute();
$dbResult = $dbQuery->get_result();
while($row = $dbResult->fetch_assoc()) {
	$libProductDailySold[$row["ProductID"]][$row["Date"]] += $row["Sold"];
	$libProductSold[$row["ProductID"]] += $row["Sold"];
	$groupSold[$libProductInGroup[$row["ProductID"]]] += $row["Sold"];

	if ($row["UnitPrice"] != null) {
		$libProductDailySoldAmount[$row["ProductID"]][$row["Date"]] += $row["Sold"] * $row["UnitPrice"];
		$libProductSoldAmount[$row["ProductID"]] += $row["Sold"] * $row["UnitPrice"];
		$groupSoldAmount[$libProductInGroup[$row["ProductID"]]] += $row["Sold"] * $row["UnitPrice"];
	} else {
		$libProductDailySoldAmount[$row["ProductID"]][$row["Date"]] += $row["Sold"] * $libProductUnitPrice[$row["ProductID"]];
		$libProductSoldAmount[$row["ProductID"]] += $row["Sold"] * $libProductUnitPrice[$row["ProductID"]];
		$groupSoldAmount[$libProductInGroup[$row["ProductID"]]] += $row["Sold"] * $libProductUnitPrice[$row["ProductID"]];		
	}

}
$dbQuery->close(); 
foreach ($libProductSold as $tProductID => $tSold) {
	$libProductAvgSellingPrice[$tProductID] = ($tSold > 0) ? $libProductSoldAmount[$tProductID] / $tSold : 0;
}

//Escrow Acct
$sql = "select * from EscrowAccountLog where Date <= '" . $currDate . "' and LoanID=" . $currLoanID . " order by rowID";
$dbQuery = $db->prepare($sql);
$dbQuery->execute();
$dbResult = $dbQuery->get_result();
while($row = $dbResult->fetch_assoc()) {
	$libEscrowAcct[] = $row;
}
$dbQuery->close(); 
$escrowAcct["FunderWithdrawTotal"] = 0;
$escrowAcct["SellerWithdrawTotal"] = 0;
$escrowAcct["ConsignorWithdrawTotal"] = 0;
foreach ($libEscrowAcct as $tRow) {
	$dateThisDate = strtotime($tRow["Date"]);
	$escrowAcct["Balance"][$tRow["Date"]] = $tRow["Balance"];
	if ($tRow["Date"] == $currDate) {
		$escrowAcct["AcctBalance"] = $tRow["Balance"];
		$escrowAcct["AdjustmentPreApproved"] = $tRow["AdjustmentPreApproved"];
		$escrowAcct["AdjustmentCostPreApproved"] = $tRow["AdjustmentCostPreApproved"];
		$escrowAcct["AdjustmentExtended"] = $tRow["AdjustmentExtended"];
		$escrowAcct["AdjustmentCostExtended"] = $tRow["AdjustmentCostExtended"];
	}
	if ($dateThisDate <= $dateCurrDate) {	
		$escrowAcct["BankDeposit"] += $tRow["Deposit"];
        $escrowAcct["FunderWithdraw"][$tRow["Date"]] += 0;        
        $escrowAcct["SellerWithdraw"][$tRow["Date"]] += 0;        
		switch ($tRow["WithdrawFor"]) {
			case "Funder":
                $escrowAcct["FunderWithdrawTotal"] += $tRow["Withdrawal"];
				$escrowAcct["FunderWithdraw"][$tRow["Date"]] += $tRow["Withdrawal"];
				break;
			case "Seller":
				$escrowAcct["SellerWithdrawTotal"] += $tRow["Withdrawal"];
                $escrowAcct["SellerWithdraw"][$tRow["Date"]] += $tRow["Withdrawal"];
				break;
			case "Consignor":
				$escrowAcct["ConsignorWithdrawTotal"] += $tRow["Withdrawal"];
				$escrowAcct["ConsignorWithdraw"][$tRow["Date"]] += $tRow["Withdrawal"];
				break;
            default:
                //$escrowAcct["FunderWithdraw"][$tRow["Date"]] += 0;
                break;
		}
	}
}

//LoanDailyReport
$sql = "select * from LoanDailyReport where Date <= '" . $currDate . "' and LoanID=" . $currLoanID;
$dbQuery = $db->prepare($sql);
$dbQuery->execute();
$dbResult = $dbQuery->get_result();
while($row = $dbResult->fetch_assoc()) {
	$libLoanDaily[$row["Date"]] = $row;
}
$dbQuery->close(); 
?>