Erwin007

Unfortunately I only get it to work in Javascript that's why I try to get the dates right. This table goes to the client so has to be flawless with a title with correct dates.

I have tried it all, now when I print, the title is not there.

Unfortunately the caption doesn't work with the Datatable, it puts it at the bottom of the table. I followed your code for the date but now it says UNDEFINED: $vanaf = (new DateTime($from))->format('d-m-Y'); If I echo $vanaf I get the correct date but it doesn't "go" to Javascript. const from = $('#vanaf').val(); get's me: Commission undefined - 2023-05-08

I am trying to get a simple thing like a title of the table, which supposedly is done in Javascript. <script> $(document).ready( function () { $('#myTable').dataTable({ "ordering": false }) $('#myTable2').dataTable({ "ordering": false, "dom": 'Btip', "buttons": ['print'] }); //const from = $('#newdate').val(); const from = $("#from")[0].valueAsDate.toLocaleDateString(('en-GB')); //const to = $('#to').val(); const to = $("#to")[0].valueAsDate.toLocaleDateString(('en-US')); $('#myTable2').append('<caption style="caption-side: top; align:center"><span style="font-size: 1.3REM; color: #000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Commission '+ from +' - '+ to +'</span></caption>') }); </script> I am now trying to get the date correct, which is a big **** with the American date notation.

Yes, they are. I tried this: const from = $("#from")[0].valueAsDate.toLocaleDateString(('en-GB')); and got this: Commission 31/03/2023 - 29/04/2023 So I am "short" 1 day.

Hi, I tried this: $newdate = (new DateTime($from))->format('d-m-Y'); and in the script: const from = $('#newdate').val(); and it gives me: Commission undefined

I have this script code: <script> const from = $('#from').val(); const to = $('#to').val(); $('#myTable2').append('<caption style="caption-side: top; align:center">Commission '+ from +' - '+ to +'</caption>') </script> which gives me this: Commission 2023-04-01 - 2023-04-30 but I need this: Commission 01-04-2023 - 30-04-2023 I tried this in PHP but it just gives me an undefined error.

I got it!!!! It was this start instead of from part in the input!!!! At least I now can change all the other reports and after that I will definitely have some other problems...but that's for later. Thanks again everybody for your help and advice.

The code from Barand needs to "fit in" the Dashboard template and that I simply can not get to work (after so many hours)...☹️ I came to the conclusion that I spend my money on the wrong programmer after you guys came up with your corrections and solutions. I don't want to put all the code here and let you guys do all the work, but after more than 50hours total working on this I am at the point of given up. Also the code is for a company and I can not just put it online so I have to redact a lot everytime. I wish I had checked for this forum before I went to freelancer.com, but it is what it is. Here is my code: <?php include "header.php"; $ikke = $_GET['ikke'] ?? ''; $from = $_GET['from'] ?? ''; $to = $_GET['to'] ?? ''; $tdata = ""; if (!empty($from) && !empty($to) && !empty($ikke)) { $query = " SELECT reps.rep_name, p41Sales.totalrum, allSales.total FROM reps INNER JOIN ( SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total FROM `sales` JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = 5 AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN ? AND ?) GROUP BY reps.rep_id ) allSales ON allSales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = 5 AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN ? AND ?) AND salesdetails.salesdetails_productid IN (41,56,55,54,53,52,51,50) GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id "; $result = mysqli_prepare($con,$query); $result->bind_param('ssss', $from, $to, $from, $to); $result->execute(); $result->bind_result($rep_name, $totalrum, $total); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; $CigarMargin = floatval($ikke); $RumMargin = 1; while($row = $result->fetch()) { $tot = round($total * $CigarMargin); $rums = round($totalrum * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars += $cigars; $totalcigarscomm += $cigarscomm; $totalrums += $rums; $totalrumscomm += $rumscomm; $totalsales += $tot; $totalcomm += $comm; $represent = explode(" ", $rep_name); $tdata .= sprintf("<tr> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", $represent[0], $tot, $cigars, $cigarscomm, $rums, $rumscomm, $comm ); } $tdata .= sprintf("<tr class='tot'> <td class='la'>%s</td> <td>%d</td> <td>%d</td> <td>%0.2f</td> <td>%d</td> <td>%0.2f</td> <td>%0.2f</td> </tr> ", 'TOTALS', $totalsales, $totalcigars, $totalcigarscomm, $totalrums, $totalrumscomm, $totalcomm ); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <style type='text/css'> table { width: 60%; border-collapse: collapse; margin: 20px auto; } th, td { padding: 4px 10px; } td { text-align: right; } .la { text-align: left; } .tot { background-color: #666; color: white; } </style> </head> <body> <div class="app-content content"> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="GET"> <div class="row"> <div class="col col-md-1"> <label for='ikke'>Margin %:</label> <input type="text" value="<?= $ikke ?>" name="ikke" id="ikke" class="form-control"> </div> <div class="col col-md-2"> <label for='start'>Date From:</label> <input type="date" value="<?= $from ?>" name="start" id="start" class="form-control"> </div> <div class="col col-md-2"> <label for='to'>Date To:</label> <input type="date" value="<?= $to ?>" name="to" id="to" class="form-control"> </div> <div class="col col-md-1"> <input type='submit' class="form-control" > </div> </div> </form> </div> </div> </section> </div> </div> <div class="content-wrapper container-xxl p-0"> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <div class="col-md-12" > <table class="table" id="myTable2"> <thead> <tr> <th>Rep</th> <th>Sales</th> <th>Cigars</th> <th>30%</th> <th>Rum/Souv.</th> <th>30%</th> <th>Comm.</th> </tr> </thead> <tbody class="tbody"> <?= $tdata ?> </tbody> </table> </div> </div> </div> </section> </div> </div> </div> </body> </html> I have the feeling somewhere in the HTML part is my error, could be in the header and/or the footer, I am really at a loss.

"Yours" works, my (little) adapted version does not, I will keep looking for my error. Thanks.

Unfortunately I can't get it to work; it looks like it does nothing after pressing submit.

Ok, thanks, I have already followed your lead. 👍

I am trying to figure out your code and have some questions: - when I open this it starts immediately with executing, how can I avoid that? - I removed the parameters right from $ikke, $from and $to but it won't work anymore, maybe that has to do with my 1st question? Thank you very much for your help, I appreciate it a lot. I need to be able to choose both dates, none should be "fixed".

Hi, thanks for your suggestions and sorry for my ignorance but are these for the code from Barand or mine?

What I understand this comes from a (dashboard) template. This is my 1st project with PHP which I paid for and to learn from but the Javascript is quite confusing. How should I change this specific code into the "normal" way with GET or POST? Thanks in advance.

This code I got from somebody else. I think the script is there to avoid creating a submit-button, no idea. I will try without the Javascript to get it working. Thanks for your time.

<?php include "header.php"; ?> <div class="app-content content "> <div class="content-overlay"></div> <div class="header-navbar-shadow"></div> <div class="content-wrapper container-xxl p-0"> <div class="content-header row"></div> <div class="content-body"> <section class="invoice-list-wrapper"> <div class="card"> <div class="card-datatable table-responsive card-body"> <form method="POST" action="response.php"> <div class="row"> <p style="font-size:18px; font-weight: bold"> xxxxxxx </p> <div class="col col-md-2"> <label>Margin %:</label> <input type="number" name="ikke" id="ikke"> </div> <div class="col col-md-2"> <label>Date From:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="start" id="start" class="form-control"> </div> <div class="col col-md-2"> <label>Date To:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="to" id="to" class="form-control"> </div> <?php if(isset($_GET['to'])){ $from = $_GET['from']; $to = $_GET['to']; ?> <div class="col-md-12" > <table class="table dataTable" id="myTable2"> <thead> <tr> <th><b><u>Rep:</u></b></th> <th></th> <th></th> <th><b><u>Sales:</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Cigars:</u></b></th> <th><b><u>30%</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Rum/Souv.:</u></b></th> <th><b><u>30%</u></b></th> <th></th> <th></th> <th></th> <th><b><u>Comm.:</u></b></th> <th></th> <th></th> </tr> </thead> <tbody class="tbody"> <?php $query = " SELECT reps.rep_name, p41Sales.totalrum, allSales.total FROM reps INNER JOIN ( SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total FROM `sales` JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) allSales ON allSales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41' OR salesdetails.salesdetails_productid = '56' OR salesdetails.salesdetails_productid = '55' OR salesdetails.salesdetails_productid = '54' OR salesdetails.salesdetails_productid = '53' OR salesdetails.salesdetails_productid = '52' OR salesdetails.salesdetails_productid = '51' OR salesdetails.salesdetails_productid = '50' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id"; $result = mysqli_query($con,$query); $totalcigars = 0; $totalcigarscomm = 0; $totalrums = 0; $totalrumscomm = 0; $totalsales = 0; $totalcomm = 0; while($row = mysqli_fetch_assoc($result)){ $CigarMargin = 0.455; $RumMargin = 1; $tot = round($row['total'] * $CigarMargin); $rums = round($row['totalrum'] * $RumMargin); $cigars = $tot - $rums; $cigarscomm = $cigars * .3; $rumscomm = $rums * .3; $comm = $cigarscomm + $rumscomm; $totalcigars = $totalcigars + $cigars; $totalcigarscomm = $totalcigarscomm + $cigarscomm; $totalrums = $totalrums + $rums; $totalrumscomm = $totalrumscomm + $rumscomm; $totalsales = $totalsales + $tot; $totalcomm = $totalcomm + $comm; $stringText = $row['rep_name']; $represent = explode(" ", $stringText); ?> <tr> <td><?php echo $represent[0]; ?></td> <td></td> <td></td> <td><?php echo $tot;?></td> <td></td> <td></td> <td></td> <td><?php echo $cigars; ?></td> <td><?php echo number_format($cigarscomm,2,'.','') ;?></td> <td></td> <td></td> <td></td> <td><?php echo $rums;?></td> <td><?php echo number_format($rumscomm,2, '.','');?></td> <td></td> <td></td> <td></td> <td><?php echo number_format($comm,2,'.','');?></td> <td></td> <td></td> </tr> <?php } ?> <tr> <td></td> <td></td> <td><b>Total:</b></td> <td><b><?php echo $totalsales; ?></b></td> <td></td> <td></td> <td></td> <td><b><?php echo $totalcigars; ?></b></td> <td><b><?php echo number_format($totalcigarscomm,2,'.',''); ?></b></td> <td></td> <td></td> <td></td> <td><b><?php echo $totalrums; ?></b></td> <td><b><?php echo number_format($totalrumscomm,2,'.',''); ?></b></td> <td></td> <td></td> <td></td> <td><b><u><?php echo number_format($totalcomm,2,'.',''); ?></u></b></td> <td></td> <td></td> </tr> </tbody> </table> </div> <?php } ?> </div> </form> </div> </div> </section> </div> </div> </div> <?php include "footer.php"; ?> <script> $("#to").change(function(){ var view = $(this).val(); var from = $('#start').val(); var to = $('#to').val(); window.location.href = "test.php?from="+from+"&&to="+to; }) </script>

Hi, I have this code: <form method="POST" action="response.php"> <label>Margin %:</label> <input type="number" name="ikke" id="ikke"> <label>Date From:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="start" id="start" class="form-control"> <label>Date To:</label> <input type="date" value="<?php date('Y-m-d') ?>" name="to" id="to" class="form-control"> <?php if(isset($_GET['to'])){ $from = $_GET['from']; $to = $_GET['to']; ?> <thead> <tr> <th>Date From:</th> <th>Date To:</th> <th>Margin:</th> </tr> </thead> <tbody> <tr> <td><?php echo $from;?></td> <td><?php echo $to;?></td> <td><?php echo $ikke;?></td> </tr> </tbody> </form> <script> $("#to").change(function(){ var view = $(this).val(); var from = $('#start').val(); var to = $('#to').val(); window.location.href = "test.php?from="+from+"&&to="+to; }) </script> And it is working 100% except I can not get the value of ikke. The response.php does nothing in relation with this code, it just "runs through" it. I hope somebody can solve this for me, thanks in advance.

Yes, exactly that! 😀 And I have it now......the comma before COALESCE fell away after copy-paste....how dumb of me. But this is it, thank you very much!!!

Hi, thanks for your solution unfortunately my result is empty. Attached the "new" tables and yes there are reps with touroperator_id 12 which sold product 41. I cleaned up the reps table a bit. reps.sql.txt sales.sql.txt sales_larimar.sql.txt salesdetails.sql.txt touroperators.sql.txt

Yes, this is 100% correct, I am sorry. The problem is in the last query these numbers were all wrong again. I can't get this one to work: SELECT reps.rep_name, p41Sales.totalrum, allSales.total, larimarSales.totallarimar FROM reps INNER JOIN ( SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total FROM `sales` JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) allSales ON allSales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS totallarimar FROM `sales_larimar` JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales_larimar.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) larimarSales ON larimarSales.rep_id = reps.rep_id";

Again I celebrated too early, even in the query before this last one is an error. SELECT reps.rep_name, p41Sales.totalrum, allSales.total FROM reps INNER JOIN ( SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total FROM `sales` JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) allSales ON allSales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id"; The problem is that the sales where there is sales of product_id 41 are skipped. The argument AND salesdetails.salesdetails_productid = '41' is probably taking away the sales with all other than product_id 41.

Yes, it is working now 100%. Thank you very much and repect for your SQL knowledge, quite impressive.

Tried like you wrote: SELECT reps.rep_name, p41Sales.totalrum, allSales.total, larimarSales.totallarimar FROM reps INNER JOIN ( SELECT reps.rep_id, sum(sales.sales_totaldollars) AS total FROM `sales` JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) allSales ON allSales.rep_id = reps.rep_id LEFT JOIN ( SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) AS totalrum FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41' GROUP BY reps.rep_name ) p41Sales ON p41Sales.rep_id = reps.rep_id INNER JOIN ( SELECT reps.rep_id, sum(sales_larimar.sales_totaldollars) AS total FROM `sales_larimar` JOIN reps ON reps.rep_id = sales_larimar.sales_repid AND reps.rep_touroperator_id = '12' AND reps.rep_active = 1 WHERE (sales_larimar.sales_date BETWEEN '$from' AND '$to') GROUP BY reps.rep_id ) larimarSales ON larimarSales.rep_id = reps.rep_id"; Result was zero.

Tried a dozen possibilities but can't get it to work.