User will enter like more than one payment card 1.00,cash 2.00,card 10,00,cash 20.00 etc…After these all values insert into payment_details table one by one along with current date.So after this i need to insert data to another table called moneybox table.
Count of total cash and total card will store into money box group by current date.
payment table looks like
card 1.00 2018-10-18
cash 2.00 2018-10-18
card 10.00 2018-10-18
cash 20.00 2018-10-18
insert record into “moneybox” table like (only two rows)
cash 11.00 2018-10-18 // all cash sum value 11 of 2018-10-18
card 22.00 2018-10-18 // all card sum value 22 of 2018-10-18
cash 200.00 2018-10-19
card 370.00 2018-10-19
always two rows ie; card and cash will be there in money table,going to total of cash and card will be store based on current date.
Here is my php code for inserting and updating
if (isset($_POST["getamount"])) {
$getinvoiceid = $_POST['getinvoiceid'];
$getstorepaymode = $_POST['getstorepaymode'];
$getamount = $_POST['getamount'];
$sql1 = "select date from moneybox order by ID desc limit 1";
$result1 = mysqli_query($link, $sql1);
$row1 = mysqli_fetch_array($result1);
//echo json_encode($row1);
$last_moneybox_created_date = $row1['date'];
$sqlclosebalcash = "select closing_balance from moneybox where date='$last_moneybox_created_date' and type='cash'";
$resultclosebal_cash = mysqli_query($link, $sqlclosebalcash);
$rowclosebal_cash = mysqli_fetch_array($resultclosebal_cash);
//echo json_encode($row1);
//$last_moneybox_closingbalanacecash = $rowclosebal_cash['closing_balance'];
$sqlclosebalcard = "select closing_balance from moneybox where date='$last_moneybox_created_date' and type='bank'";
$resultclosebal_card = mysqli_query($link, $sqlclosebalcard);
$rowclosebal_card = mysqli_fetch_array($resultclosebal_card);
//$last_moneybox_closingbalanacecard = $rowclosebal_card['closing_balance'];
$tz = 'Asia/Dubai'; // your required location time zone.
$timestamp = time();
$dt = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
$dt->setTimestamp($timestamp); //adjust the object to correct timestamp
$todayDate = $dt->format('Y-m-d');
if ($rowclosebal_cash['closing_balance'] == '') {
$last_moneybox_closingbalanacecash = "0.00";
} else {
$last_moneybox_closingbalanacecash = $rowclosebal_cash['closing_balance'];
}
if ($rowclosebal_card['closing_balance'] == '') {
$last_moneybox_closingbalanacecard = "0.00";
} else {
$last_moneybox_closingbalanacecard = $rowclosebal_card['closing_balance'];
}
$cashMode = 0;
$cardMode = 0;
for ($count = 0; $count < count($getamount); $count++) {
$payamt_clean = $getamount[$count];
$getstorepaymode_clean = $getstorepaymode[$count];
date_default_timezone_set('Asia/Dubai');
$created = date("y-m-d H:i:s");
$query .= 'INSERT INTO payment_details (invoiceID,paymentMode,Amount,created)
VALUES ("' . $getinvoiceid . '" , "' . $getstorepaymode_clean . '", "' . $payamt_clean . '", "' . $created . '");
';
if($last_moneybox_created_date != $todayDate) {
$cashMode = 0;
$cardMode = 0;
}
$cal_closingbalancecash = $last_moneybox_closingbalanacecash - $payamt_clean;
$cal_closingbalancecard = $last_moneybox_closingbalanacecard - $payamt_clean;
switch($getstorepaymode_clean) {
case "CASH":
if($cashMode === 0) {
echo 'Different Date cash'; //insert happen based on the type
$last_moneybox_created_date = $todayDate;
$cashMode = 1;
$query .= "INSERT INTO moneybox (type,inflow,date)
VALUES ('cash','$payamt_clean','$todayDate');";
}
else {
echo 'Same Date cash'; //update happen based on type and date
$query .= "UPDATE moneybox SET
inflow = inflow + $payamt_clean,
closing_balance= opening_balance + inflow - outflow
WHERE type = 'cash' and date = '$todayDate';";
}
break;
case "CARD":
if($cardMode === 0) {
echo 'Different Date card'; //insert happen based on the type
$last_moneybox_created_date = $todayDate;
$cardMode = 1;
$query .= "INSERT INTO moneybox (type,inflow,date)
VALUES ('bank','$payamt_clean','$todayDate');";
}
else {
echo 'Same Date card'; //update happen based on type and date
$query .= "UPDATE moneybox SET
inflow = inflow + $payamt_clean,
closing_balance= opening_balance + inflow - outflow
WHERE type = 'bank' and date = '$todayDate';";
}
break;
} // end switch case
}
if (mysqli_multi_query($link, $query)) {
echo 'paydetails Inserted';
} else {
echo "Error: " . $query . "<br>" . mysqli_error($link);
}
}
Actually i want
- when user enter card:10:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record card:10:00.
2.When user enter card:10:00 cash:20:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:10:00 and second row cash 20:00.
3.When user enter card 10:00 cash:20:00 card 11:00 cash 30:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:21:00 and second row cash 50:00.
-
When user enter card 10:00 card 10:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:20:00
-
When user enter cash 50:00 cash 50:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row cash:100:00
then will see when updation will happen
6.When user enter cash 1000:00 (if $last_moneybox_created_date == $todayDate
update will happen)update record where current date belongs to cash
7.When user enter card 700:00 (if $last_moneybox_created_date == $todayDate update will happen)update record where current date belongs to card