This follows on from a previous question (html/php/sql form with insert/update) so use that for reference on the HTML form. When a client moves in, their client's active is changed to 1, the room's room_vacant is changed to 0, and an occupancy record is made.
As well as this, the first rental payment needs to be generated using all this information. The rent_occupancy_id is worked out through the user of lastInsertId(), as the last inserted id would've been the occupancy ID.
The rent_cost is generated whether the client moving in is on benefits on not. If a client is not on benefits, then the rent_cost = £126.57 but if they are then rent_cost = £20.03. The date_lastpaid is the same as the start_date of the client's occupancy record and the date_due is equal to a week ahead of the date_lastpaid.
The problem is that a rent record is not being created yet all the other changes for the other records etc. are being made. Any help would be much appreciated.
<?php
require("dbconnect.php");
//CLIENT
$id = $_POST['id'];
//UPDATES client's to 1/Yes to say that they're now an active client in JRH,
//where the selected client's ID is equal to :id
$stmt = $dbh->prepare("UPDATE client SET active = 1 WHERE client_id=:id");
$stmt->bindParam(':id', $id);
$stmt->execute();
//ROOM
$room_id = $_POST['room_id'];
$stmt = $dbh->prepare("UPDATE room SET room_vacant = 0 WHERE room_id = :room_id");
$stmt->bindParam(':room_id', $room_id);
$stmt->execute();
//OCCUPANCY
$id = $_POST['id'];
$room_id = $_POST['room_id'];
$start_date = $_POST['start_date'];
$stmt = $dbh->prepare("INSERT INTO occupancy (occupancy_client_id, occupancy_room_id, start_date) VALUES(:id, :room_id, :start_date)");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':room_id', $room_id);
$stmt->bindParam(':start_date', $start_date);
$stmt->execute();
//Working out Rent Cost
$id = $_POST['id'];
$stmt=$dbh->prepare("SELECT * FROM client WHERE client_id=:id");
$stmt->bindParam(':id', $id);
$stmt->execute();
$row = $stmt->fetch();
//Works out whether a client has benefits or not,
//and then uses this information to generate a cost of the rent
if ($row['benefits'] == '0') {
$rent_cost = "126.57";
} else{
$rent_cost = "20.03";
}
//RENT INSERT
//
//As the occupancy record is being created above
//and the rent table needs a rent_occupancy_id to join,
//then we use lastInsertId() in order to get that occupancy id
$rent_occupancy_id = $dbh->lastInsertId();
$date_lastpaid = $_POST['start_date']; //As the client hasn't made a payment yet,
//we say that their last payment was the date they joined
$date_due = strtotime($date_lastpaid);
$date_due = strtotime('+1 week', $date_due);
$date_due = date('Y/m/d', $date_due);
$stmt = $dbh->prepare("INSERT INTO rent (rent_occupancy_id, rent_cost, date_lastpaid, date_due) VALUES (:rent_occupancy_id, :rent_cost, :date_lastpaid, :date_due)");
$stmt->BindParam(':rent_occupancy_id', $rent_occupancy_id);
$stmt->BindParam(':rent_cost', $rent_cost);
$stmt->BindParam(':date_lastpaid', $date_lastpaid);
$stmt->BindParam(':date_due', $date_due);
$stmt->execute();
?>
