Jump to content

Senthilkumar

Members
 View Profile  See their activity

  • Posts

    184

  • Joined

  • Last visited

 Content Type 

Everything posted by Senthilkumar

  1. Senthilkumar
    Dear Mr. Mac_gyver, thanks for your reply. I changed the code as per your suggestion. Count the number of rows in the table body. Now it is working properly <script> $(document).ready(function () { const tbody = document.querySelector('#product_data tbody'); // Get all the tr elements within the tbody const rows = tbody.getElementsByTagName('tr'); // Get the count of tr elements const rowCount = rows.length; $("#addRow").click(function () { const tbody = document.querySelector('#product_data tbody'); // Get all the tr elements within the tbody const rows = tbody.getElementsByTagName('tr'); // Get the count of tr elements const rowCount = rows.length; var n = rowCount + 1; // Counter for rows var newRow = `<tr> <td align='center'>${n}</td> <td><input type="text" name="CustomerName[]" class="form-control" /></td> <td><input type="text" name="CustomerCity[]" class="form-control" /></td> <td> <select name="interval[]" class="form-control form-select"> <option value="">Choose</option> <option value="Monthly">Monthly</option> <option value="Quarterly">Quarterly</option> </select> </td> <td> <select name="plan[]" class="form-control form-select"> <option value="">Select Interval</option> <option value="1st Week">1st Week</option> <option value="2nd Week">2nd Week</option> <option value="3rd Week">3rd Week</option> <option value="4th Week">4th Week</option> <option value="1st Month">1st Month</option> <option value="2nd Month">2nd Month</option> <option value="3rd Month">3rd Month</option> </select> </td> <td> <select name="type[]" class="form-control form-select" onchange="toggleDateField('new_${n}')"> <option value="">Choose</option> <option value="Direct">Direct</option> <option value="Telephone">Telephone</option> <option value="Not Visited">Not Visited</option> <option value="No Due">No Due</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" /> </td> <td align='center'> <input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td> <input type="text" name="lang[]" value="new_${n++}" class="form-control border border-success" autocomplete="off" /> </td> </tr>`; $("#product_data tbody").append(newRow); }); }); } </script> Please give your feedback
  2. Senthilkumar
    I had modified the code. Now the dynamic data are saving the database and displaying the table while retrieving it. The modified code is <?PHP include("../connection.php"); ini_set('memory_limit', '-1'); ini_set('display_errors', '1'); ini_set('display_startup_errors', '1'); error_reporting(E_ALL); session_start(); date_default_timezone_set("Asia/Kolkata"); $id = $_SESSION['id']; if ($id == '') { session_destroy(); header("location:../logout-user.php"); //redirect to index.php } $EmpNo = $_SESSION['EmpNo']; $Email = $_SESSION['email']; $Name = $_SESSION['Name']; $Branch = $_SESSION['Branch']; $Department = $_SESSION['Department']; $mailselect = "SELECT Fname, Email, EmpNo FROM sbms.usertable WHERE Branch = '$Branch' AND Role='4' AND Department='$Department'"; $mailresult = mysqli_query($conn, $mailselect); $mailrow = mysqli_fetch_assoc($mailresult); //$Firstapprovermail = $mailrow['Email']; //$Firstapprovername = $mailrow['Fname']; //$FirstapproverID = $mailrow['EmpNo']; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Customer Visit Data</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" /> <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script> </head> <body> <form method="post" action="" id="myForm"> <div class="row" style="width:100%;margin-bottom:10px; margin-left:2px"> <div class="col-md-2 border rounded" style="padding: 5px; box-shadow: rgba(0, 0, 0, 0.4) 0px 2px 4px, rgba(0, 0, 0, 0.3) 0px 7px 13px -3px, rgba(0, 0, 0, 0.2) 0px -3px 0px inset; "> <label style="font-weight:bold">Month</label> <input type="month" id="Month" name="Month" onchange="viewData()" class="form-control" /> </div> <div class="alert-warning col-md-9 hidde" id="toast"> Note: <br />1) Visit Interval, Visit Plan, Type of Visit & Visit Date is Mandatory. Without that the data will not submit. <br />2) You can choose No Due under Type of Visit if the Visit Interval is in Quarters and you don't have any Due for this month. In that case, the date field will become inactive. <br />3) You must choose Not Visited under Type of Visit if you have not contacted the Customer. The date field will become inactive. </div> <div class="col f-right"> <button type="button" class="btn btn-info align-middle f-right" id="info"><i class="fa-solid fa-circle-info" style="font-size:larger;"></i></button> </div> </div> <script> $("#info").on("click", function () { $('#toast').show(); setTimeout(function () { document.getElementById('toast').style.display = 'none'; }, 10000); // 10000ms = 10s }); </script> <div class="row tbl_fixed" style="margin-top: 20px; box-shadow: rgba(0, 0, 0, 0.25) 0px 0.0625em 0.0625em, rgba(0, 0, 0, 0.25) 0px 0.125em 0.5em, rgba(255, 255, 255, 0.1) 0px 0px 0px 1px inset; width: 100% "> <table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;"> <thead class="bg-blue text-center" style="white-space:nowrap"> <tr> <th style="width:5%">S.No</th> <th style="width:25%">Customer Name</th> <th style="width:15%">Customer Location</th> <th style="width:10%">Visit Interval <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Visit Plan <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Type of Visit <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Visit Date <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:15%">Remarks </th> <th style="width:15%">Remarks1 </th> </tr> </thead> <tbody> <?php $n = 1; // Default to 1 if no rows are found $sql = "SELECT * FROM machinemaster AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_assoc($result)) { $RowID = $row['RowID']; $CustomerName = $row['CustomerName']; $CustomerCity = $row['CustomerCity']; $VisitInterval = $row['VisitInterval']; $VisitPlan = $row['VisitPlan']; $VisitType = $row['VisitType']; ?> <tr> <td align='center'><?php echo $n++; ?></td> <td><?php echo $CustomerName; ?></td> <td><?php echo $CustomerCity; ?></td> <td hidden><input type="text" name="CustomerName[]" id="CustomerName<?php echo $RowID; ?>" class="form-control" value="<?php echo $CustomerName; ?>" /></td> <td hidden><input type="text" name="CustomerCity[]" id="CustomerCity<?php echo $RowID; ?>" class="form-control" value="<?php echo $CustomerCity; ?>" /></td> <td align='center'> <select name='interval[]' id='interval<?php echo $RowID; ?>' class='form-control interval' style="width:auto"> <option value=''>Choose</option> <option value='Monthly' <?php if ($VisitInterval == 'Monthly') echo 'selected'; ?>>Monthly</option> <option value='Bymonth' <?php if ($VisitInterval == 'Bymonth') echo 'selected'; ?>>Bi-Monthly</option> <option value='Quarterly' <?php if ($VisitInterval == 'Quarterly') echo 'selected'; ?>>Quarterly</option> </select> </td> <td> <select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto"> <option value=''>Select Interval</option> <option value='1st Week' <?php if ($VisitPlan == '1st Week') echo 'selected'; ?>>1st Week</option> <option value='2nd Week' <?php if ($VisitPlan == '2nd Week') echo 'selected'; ?>>2nd Week</option> <option value='3rd Week' <?php if ($VisitPlan == '3rd Week') echo 'selected'; ?>>3rd Week</option> <option value='4th Week' <?php if ($VisitPlan == '4th Week') echo 'selected'; ?>>4th Week</option> <option value='1st Month' <?php if ($VisitPlan == '1st Month') echo 'selected'; ?>>1st Month</option> <option value='2nd Month' <?php if ($VisitPlan == '2nd Month') echo 'selected'; ?>>2nd Month</option> <option value='3rd Month' <?php if ($VisitPlan == '3rd Month') echo 'selected'; ?>>3rd Month</option> </select> </td> <td> <select name='type[]' id='type<?php echo $RowID; ?>' class='form-control form-select type' style="width:auto" onchange="toggleDateField(<?php echo $RowID; ?>)"> <option value=''>Choose</option> <option value='Direct' <?php if ($VisitType == 'Direct') echo 'selected'; ?>>Direct</option> <option value='Telephone' <?php if ($VisitType == 'Telephone') echo 'selected'; ?>>Telephone</option> <option value='Not Visited' <?php if ($VisitType == 'Not Visited') echo 'selected'; ?>>Not Visited</option> <option value='No Due' <?php if ($VisitType == 'No Due') echo 'selected'; ?>>No Due</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" id="SubmitedDate<?php echo $RowID; ?>" value="" class="form-control border border-success SubmitedDate" autocomplete="off" required /> </td> <td align='center'> <input type="text" name="Remarks[]" id="Remarks<?php echo $RowID; ?>" value="" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td> <input type="text" name="lang[]" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" /> </td> </tr> <?php } ?> </tbody> </table> </div> <div class="col-sm-12 text-center" style="margin-top:10px;margin-bottom:10px" id="button"> <button type="button" id="addRow" class="btn btn-sm btn-primary btn-md center-block fw-bold border"><i class="fa-solid fa-plus"></i> Add New Row</button> <button type="button" id="save" name="save" class="btn btn-success btn-md center-block fw-bold border" onclick="saveData()">Save</button> </div> </form> <script> $(document).ready(function () { var n = <?php echo $n; ?>; // Counter for rows $("#addRow").click(function () { var newRow = `<tr> <td align='center'>${n}</td> <td><input type="text" name="CustomerName[]" class="form-control" /></td> <td><input type="text" name="CustomerCity[]" class="form-control" /></td> <td> <select name="interval[]" class="form-control form-select"> <option value="">Choose</option> <option value="Monthly">Monthly</option> <option value="Quarterly">Quarterly</option> </select> </td> <td> <select name="plan[]" class="form-control form-select"> <option value="">Select Interval</option> <option value="1st Week">1st Week</option> <option value="2nd Week">2nd Week</option> <option value="3rd Week">3rd Week</option> <option value="4th Week">4th Week</option> <option value="1st Month">1st Month</option> <option value="2nd Month">2nd Month</option> <option value="3rd Month">3rd Month</option> </select> </td> <td> <select name="type[]" class="form-control form-select" onchange="toggleDateField('new_${n}')"> <option value="">Choose</option> <option value="Direct">Direct</option> <option value="Telephone">Telephone</option> <option value="Not Visited">Not Visited</option> <option value="No Due">No Due</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" /> </td> <td align='center'> <input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td> <input type="text" name="lang[]" value="new_${n++}" class="form-control border border-success" autocomplete="off" /> </td> </tr>`; $("#product_data tbody").append(newRow); }); }); function toggleDateField(rowId) { var visitType = $('#type' + rowId).val(); if (visitType === 'Not Visited' || visitType === 'No Due') { $('#SubmitedDate' + rowId).prop('disabled', true); } else { $('#SubmitedDate' + rowId).prop('disabled', false); } } function viewData() { var month = $('#Month').val(); var employeeno = '<?php echo $EmpNo; ?>'; // Adjust this to retrieve the correct employee number $.ajax({ url: 'Customer_Data_View.php', type: 'GET', data: { Month: month, employeeno: employeeno }, dataType: 'json', success: function (response) { var tbody = $('#product_data tbody'); tbody.empty(); // Clear existing rows var len = response.length; var n = 0; $.each(response, function (index, data) { var newRow = `<tr> <td align='center'>${n + 1}</td> <td >${data.CustomerName}</td> <td >${data.CustomerCity}</td> <td hidden><input type="text" name="CustomerName[]" value="${data.CustomerName}" class="form-control" /></td> <td hidden><input type="text" name="CustomerCity[]" value="${data.CustomerCity}" class="form-control" /></td> <td> <select name="interval[]" class="form-control form-select"> <option value="">Choose</option> <option value="Monthly" ${data.VisitInterval === 'Monthly' ? 'selected' : ''}>Monthly</option> <option value="Quarterly" ${data.VisitInterval === 'Quarterly' ? 'selected' : ''}>Quarterly</option> </select> </td> <td> <select name="plan[]" class="form-control form-select"> <option value="">Select Interval</option> <option value="1st Week" ${data.VisitPlan === '1st Week' ? 'selected' : ''}>1st Week</option> <option value="2nd Week" ${data.VisitPlan === '2nd Week' ? 'selected' : ''}>2nd Week</option> <option value="3rd Week" ${data.VisitPlan === '3rd Week' ? 'selected' : ''}>3rd Week</option> <option value="4th Week" ${data.VisitPlan === '4th Week' ? 'selected' : ''}>4th Week</option> <option value="1st Month" ${data.VisitPlan === '1st Month' ? 'selected' : ''}>1st Month</option> <option value="2nd Month" ${data.VisitPlan === '2nd Month' ? 'selected' : ''}>2nd Month</option> <option value="3rd Month" ${data.VisitPlan === '3rd Month' ? 'selected' : ''}>3rd Month</option> </select> </td> <td> <select name="type[]" class="form-control form-select" onchange="toggleDateField('${data.id}')"> <option value="">Choose</option> <option value="Direct" ${data.VisitType === 'Direct' ? 'selected' : ''}>Direct</option> <option value="Telephone" ${data.VisitType === 'Telephone' ? 'selected' : ''}>Telephone</option> <option value="Not Visited" ${data.VisitType === 'Not Visited' ? 'selected' : ''}>Not Visited</option> <option value="No Due" ${data.VisitType === 'No Due' ? 'selected' : ''}>No Due</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" value="${data.VisitDate}" class="form-control border border-success SubmitedDate" autocomplete="off" ${data.VisitType === 'Not Visited' || data.VisitType === 'No Due' ? 'disabled' : ''} /> </td> <td align='center'> <input type="text" name="Remarks[]" value="${data.Remarks}" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td> <input type="text" name="lang[]" value="${data.RowID}" class="form-control border border-success" autocomplete="off" /> </td> </tr>`; tbody.append(newRow); n++; }); }, error: function (xhr, status, error) { console.error(xhr.responseText); } }); } function saveData() { var formData = $('#myForm').serialize(); $.ajax({ url: 'save_data.php', type: 'POST', data: formData, success: function (response) { alert('Data saved successfully!'); }, error: function (xhr, status, error) { console.error(xhr.responseText); alert('Failed to save data.'); } }); } </script> </body> </html> save_data.php code is <?php include("../connection.php"); ini_set('memory_limit', '-1'); ini_set('display_errors', '1'); ini_set('display_startup_errors', '1'); error_reporting(E_ALL); session_start(); date_default_timezone_set("Asia/Kolkata"); $id = $_SESSION['id']; if ($id == '') { session_destroy(); header("location:../logout-user.php"); //redirect to index.php } $EmpNo = $_SESSION['EmpNo']; $Email = $_SESSION['email']; $Name = $_SESSION['Name']; $Branch = $_SESSION['Branch']; $Department = $_SESSION['Department']; if ($_SERVER['REQUEST_METHOD'] === 'POST') { $month = $_POST['Month']; $customerNames = $_POST['CustomerName']; $customerCities = $_POST['CustomerCity']; $intervals = $_POST['interval']; $plans = $_POST['plan']; $types = $_POST['type']; $dates = $_POST['SubmitedDate']; $remarks = $_POST['Remarks']; $ids = $_POST['lang']; $Employeeid = $_GET['employeeno']; foreach ($ids as $index => $id) { $customerName = $customerNames[$index]; $customerCity = $customerCities[$index]; $interval = $intervals[$index]; $plan = $plans[$index]; $type = $types[$index]; $date = $dates[$index]; $remark = $remarks[$index]; $RowID = $ids[$index]; if ($type == 'Not Visited' || $type == 'No Due') { $date = null; // No date for 'Not Visited' and 'No Due' } $sql = "INSERT INTO customerdata (EmpID, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, Remarks, Month, Branch, RowID, Status) VALUES ('$EmpNo', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$date', '$remark', '$month', '$Branch', '$RowID', '0') ON DUPLICATE KEY UPDATE CustomerName = VALUES(CustomerName), CustomerCity = VALUES(CustomerCity), VisitInterval = VALUES(VisitInterval), VisitPlan = VALUES(VisitPlan), VisitType = VALUES(VisitType), VisitDate = VALUES(VisitDate), Remarks = VALUES(Remarks)"; if (!mysqli_query($conn, $sql)) { echo "Error: " . mysqli_error($conn); } } } ?> Customer_Data_View.php is <?php session_start(); error_reporting(0); ini_set('display_errors', 'Off'); include '../connection.php'; if (isset($_GET['Month'])) { $Month = $_GET['Month']; $Employeeid = $_GET['employeeno']; // Retrieve all data for the given month and employee, including newly added rows $query = "SELECT * FROM customerdata WHERE EmpID = '$Employeeid' AND Month ='$Month'"; $result = mysqli_query($conn, $query); $return_arr = []; while ($row = mysqli_fetch_assoc($result)) { $return_arr[] = $row; } // If no data, retrieve from default tables if (empty($return_arr)) { $query = "SELECT * FROM sbms.machinemaster AS a INNER JOIN sbms.customerassign AS c ON c.RowID = a.id WHERE c.EmpID = '$Employeeid' AND a.MachineStatus='A'"; $result = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($result)) { $VisitInterval = $row['VisitInterval']; $VisitPlan = $row['VisitPlan']; $VisitType = $row['VisitType']; $RowID = $row['RowID']; $VisitDate = ""; $Remarks = ""; $CustomerName = $row['CustomerName']; $CustomerCity = $row['CustomerCity']; $return_arr[] = array( "VisitInterval" => $VisitInterval, "VisitPlan" => $VisitPlan, "VisitType" => $VisitType, "RowID" => $RowID, "VisitDate" => $VisitDate, "Remarks" => $Remarks, "CustomerName" => $CustomerName, "CustomerCity" => $CustomerCity ); } } echo json_encode($return_arr); } ?> In this code I am facing only one problem is the s.no incremental is not working properly. For example initially when the page is loading, the $n will be fetched from <?php $n = 1; // Default to 1 if no rows are found $sql = "SELECT * FROM machinemaster AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_assoc($result)) { For example, the total row is 4. After that, I added 3 rows dynamically. The s.no starts from 5,6,7. So the total saved row is 7. Now, when fetching the data from the saved table, this table displays 7 rows. Now when I click the add row button this s.no again starts from 5 only. Not 8. I don't know where is the mistake . Any one please suggest me how to start the s.no from 8.
  3. Senthilkumar
    Dear Mr.Mac_gyvr, I am not clear. Can you please explain with the code
  4. Senthilkumar
    Dear team, I have a table which is used to display the customers assigned to me.My table code is <form method="post" action="" id="myForm"> <div class="row" style="width:100%;margin-bottom:10px; margin-left:2px"> <div class="col-md-2 border rounded" style="padding: 5px; box-shadow: rgba(0, 0, 0, 0.4) 0px 2px 4px, rgba(0, 0, 0, 0.3) 0px 7px 13px -3px, rgba(0, 0, 0, 0.2) 0px -3px 0px inset; "> <label style="font-weight:bold">Month</label> <input type="month" id="Month" name="Month" onchange="viewData()" class="form-control" /> </div> <div class="alert-warning col-md-9 hidde" id="toast"> Note: <br />1) Visit Interval, Visit Plan, Type of Visit & Visit Date is Mantory. Without that the data will not submit. <br />2) You can choose No Due under Type of Visit if the Visit Interval is in Quarters and you don't have any Due for this month. In that case, the date field will become inactive. <br />3) You must choose Not Visited under Type of Visit if you have not contacted the Customer. The date field will become inactive. </div> <div class="col f-right"> <button type="button" class="btn btn-info align-middle f-right" id="info"><i class="fa-solid fa-circle-info" style="font-size:larger;"></i></button> </div> </div> <script> $("#info").on("click", function () { $('#toast').show(); setTimeout(function () { document.getElementById('toast').style.display = 'none'; }, 10000); // 10000ms = 10s }); </script> <div class="row tbl_fixed" style="margin-top: 20px; box-shadow: rgba(0, 0, 0, 0.25) 0px 0.0625em 0.0625em, rgba(0, 0, 0, 0.25) 0px 0.125em 0.5em, rgba(255, 255, 255, 0.1) 0px 0px 0px 1px inset; width: 100% "> <table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;"> <thead class="bg-blue text-center" style="white-space:nowrap"> <tr> <th style="width:5%">S.No</th> <th style="width:25%">Customer Name</th> <th style="width:15%">Customer Location</th> <th style="width:10%">Visit Interval <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Visit Plan <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Type of Visit <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:10%">Visit Date <span class="text-danger" style="font-size:15px"> *</span></th> <th style="width:15%">Remarks </th> <th style="width:15%">Remarks1 </th> </tr> </thead> <tbody> <?php $n = 1; $sql = "select * from machinemaster AS a INNER JOIN customerassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo' and a.MachineStatus='A'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_assoc($result)) { $RowID = $row['RowID']; $CustomerName = $row['CustomerName']; $CustomerCity = $row['CustomerCity']; $VisitInterval = $row['VisitInterval']; $VisitPlan = $row['VisitPlan']; $VisitType = $row['VisitType']; ?> <tr> <td align='center'> <?php echo $n++; ?> </td> <td> <?php echo $CustomerName; ?> </td> <td> <?php echo $CustomerCity; ?> </td> <td hidden><input type="text" name="CustomerName[]" id="CustomerName<?php echo $RowID; ?>" class="form-control" /></td> <td hidden><input type="text" name="CustomerCity[]" id="CustomerCity<?php echo $RowID; ?>" class="form-control" /></td> <td align='center' hidden> <input type="number" name="RowID[]" id="RowID<?php echo $RowID; ?>" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" /> </td> <?PHP if ($VisitInterval !== '') { ?> <td align='center'> <?php echo $VisitInterval; ?> </td> <td align='center' hidden> <input type="text" name="interval[]" id="interval<?php echo $RowID; ?>" value="<?php echo $VisitInterval; ?>" class="form-control border border-success" autocomplete="off" /> </td> <?PHP } else { ?> <td> <select name='interval[]' id='interval<?php echo $RowID; ?>' class='form-control interval' style="width:auto"> <option value=''>Choose</option> <option value='Monthly'>Monthly</option> <option value='Bymonth'>Bi-Monthly</option> <option value='Quarterly'>Quarterly</option> </select> </td> <?PHP } ?> <td> <?PHP if ($VisitInterval == 'Monthly') { ?> <select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto"> <option value=''>Select Interval</option> <option value='1st Week'>1st Week</option>; <option value='2nd Week'>2nd Week</option>; <option value='3rd Week'>3rd Week</option>; <option value='4th Week'>4th Week</option>; </select> <?PHP } elseif ($VisitInterval == 'Bymonth') { ?> <select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto"> <option value=''>Select Interval</option> <option value='1st Month'>1st Month</option>; <option value='2nd Month'>2nd Month</option>; </select> ?PHP } elseif ($VisitInterval == 'Quarterly') { ?> <select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto"> <option value=''>Select Interval</option> <option value='1st Month'>1st Month</option>; <option value='2nd Month'>2nd Month</option>; <option value='3rd Month'>3rd Month</option>; </select> <?PHP } else { ?> <select name='plan[]' id='plan<?php echo $RowID; ?>' class='form-control form-select' style="width:auto"> <option value=''>Select Interval</option> <option value='1st Week'>1st Week</option>; <option value='2nd Week'>2nd Week</option>; <option value='3rd Week'>3rd Week</option>; <option value='4th Week'>4th Week</option>; <option value='1st Month'>1st Month</option>; <option value='2nd Month'>2nd Month</option>; <option value='3rd Month'>3rd Month</option>; </select> <?PHP } ?> </td> <td> <select name='type[]' id='type<?php echo $RowID; ?>' onchange="getSelectedValue(<?php echo $RowID; ?>, this.value)" class='form-control form-select type' style="width:auto"> <option value=''>Choose</option> <option value='Direct'>Direct</option> <option value='Telephone'>Telephone</option> <option value='Not Visited'>Not Visited</option> <option value='No Due'>No Due</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" id="SubmitedDate<?php echo $RowID; ?>" value="" class="form-control border border-success SubmitedDate" autocomplete="off" required/ /> </td> <td align='center'> <input type="text" name="Remarks[]" id="Remarks<?php echo $RowID; ?>" value="" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td> <input type="number" name="lang[]" value="<?php echo $RowID; ?>" class="form-control border border-success" autocomplete="off" /> </td> </tr> <?php } ?> </tbody> </table> </div> <div class="col-sm-12 text-center" style="margin-top:10px;margin-bottom:10px" id="button"> <button type="button" id="addRow" class="btn btn-sm btn-primary btn-md center-block fw-bold" style="width: 100px;">Add Row</button> <button type="button" id="Save" name="Save" value="Save" class="btn btn-sm btn-secondary btn-md center-block fw-bold" style="width: 100px;">Save</button> <button type="button" id="Submit" name="Submit" value="Submit" class="btn btn-sm btn-success btn-md center-block fw-bold" style="width: 100px">Submit</button> </div> </form> In this table, I will update the Visit Interval, Visit Plan, Type of Visit & Visit Date & Remarks field and I will save the data as soon as entered and I will submit the data finally once all the fields are entered. For save and submit I will use the below code <?php if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == "Save") { $Month = $_POST['Month']; if ($EmpNo == '') { echo "<script> alert('The time for your session has ended. You'll return to the login page.'); window.location.href='../logout-user.php'; </script>"; } else { if (!empty($_POST['lang'])) { foreach ($_POST['lang'] as $ID => $VAL) { $customerName = mysqli_real_escape_string($conn, $_POST['CustomerName'][$ID]); $customerCity = mysqli_real_escape_string($conn, $_POST['CustomerCity'][$ID]); $interval = mysqli_real_escape_string($conn, $_POST['interval'][$ID]); $plan = mysqli_real_escape_string($conn, $_POST['plan'][$ID]); $type = mysqli_real_escape_string($conn, $_POST['type'][$ID]); $SubmitedDate = mysqli_real_escape_string($conn, $_POST['SubmitedDate'][$ID]); $RowID = mysqli_real_escape_string($conn, $VAL); // Use VAL as RowID // Insert or update the record $insertquery = "INSERT INTO sbms.customerdata (Month, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, RowID, EmpID, Branch, Status) VALUES('$Month', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$SubmitedDate', '$RowID', '$EmpNo','$Branch', '0') ON DUPLICATE KEY UPDATE CustomerName = VALUES(CustomerName), CustomerCity = VALUES(CustomerCity), VisitInterval = VALUES(VisitInterval), VisitPlan = VALUES(VisitPlan), VisitType = VALUES(VisitType), VisitDate = VALUES(VisitDate), Status = VALUES(Status)"; $result = mysqli_query($conn, $insertquery); } if ($result) { echo "<script> alert('Data Saved Sucessfully'); window.location.href='Visit_Data.php'; </script>"; } else { echo "<script> alert('Data Not Saved. Please check the details entered'); </script>"; } } } } ?> <?php if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == "Submit") { $Month = $_POST['Month']; if ($EmpNo == '') { echo "<script> alert('The time for your session has ended. You'll return to the login page.'); window.location.href='../logout-user.php'; </script>"; } else { if (!empty($_POST['lang'])) { foreach ($_POST['lang'] as $ID => $VAL) { $interval = $_POST['interval'][$ID]; $plan = $_POST['plan'][$ID]; $type = $_POST['type'][$ID]; $SubmitedDate = $_POST['SubmitedDate'][$ID]; $Remarks = $_POST['Remarks'][$ID]; $RowID = $_POST['RowID'][$ID]; $insertquery = "INSERT INTO sbms.customerdata (Month, CustomerName, CustomerCity, VisitInterval, VisitPlan, VisitType, VisitDate, RowID, EmpID, Branch, Status) VALUES('$Month', '$customerName', '$customerCity', '$interval', '$plan', '$type', '$SubmitedDate', '$RowID', '$EmpNo','$Branch', '0') ON DUPLICATE KEY UPDATE CustomerName = VALUES(CustomerName), CustomerCity = VALUES(CustomerCity), VisitInterval = VALUES(VisitInterval), VisitPlan = VALUES(VisitPlan), VisitType = VALUES(VisitType), VisitDate = VALUES(VisitDate), Status = VALUES(Status)"; $result = mysqli_query($conn, $insertquery); } if ($result) { $CurrentTime = date('Y/m/d h:i:s', time()); $Submit = "INSERT INTO submitteddata (EmpID, Month, Branch, Production, Production_Submitted, Production_App1, Production_App1_Submitted, Production_App2, Production_App2_Submitted, Production_Remarks, Marketingpromotion, Mrkt_Submitted, Mrkt_App1, Mrkt_App1_Submitted, Mrkt_App2, Mrkt_App2_Submitted, Mrkt_Remarks, CustVisit, CustVisit_Submitted, CustVisit_APP1, CustVisit_APP1_Submitted, CustVisit_App2, CustVisit_APP2_Submitted, CustVisit_Remarks, status) VALUES('$EmpNo', '$Month', '$Branch', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '$CurrentTime', '$FirstapproverID', '', '', '', '', '1') ON DUPLICATE KEY UPDATE CustVisit='1', CustVisit_Submitted='$CurrentTime', CustVisit_APP1='$FirstapproverID'"; $result = mysqli_query($conn, $Submit); $monthformat = date("M-Y", strtotime($Month)); echo "<script> alert('Data Submitted Sucessfully'); window.location.href='Visit_Data.php'; </script>"; } else { echo "<script> alert('Data Not Saved. Please check the details entered'); </script>"; } } } } ?> My requirement is I want to save the customer's details which is visited additionally apart from this list. For that I want to add extra column and enter the values and save. I am using the below script for add the additional rows <script> $(document).ready(function() { var n = <?php echo isset($n) ? $n : 1; ?>; // Counter for rows $("#addRow").click(function() { var newRow = `<tr> <td align='center'>${n++}</td> <td><input type="text" name="CustomerName[]" class="form-control" /></td> <td><input type="text" name="CustomerCity[]" class="form-control" /></td> <td> <select name="interval[]" class="form-control form-select"> <option value="">Choose</option> <option value="Monthly">Monthly</option> <option value="Quarterly">Quarterly</option> </select> </td> <td> <select name="plan[]" class="form-control form-select"> <option value="">Select Interval</option> <option value="1st Week">1st Week</option> <option value="2nd Week">2nd Week</option> <option value="3rd Week">3rd Week</option> <option value="4th Week">4th Week</option> <option value="1st Month">1st Month</option> <option value="2nd Month">2nd Month</option> <option value="3rd Month">3rd Month</option> </select> </td> <td> <select name="type[]" class="form-control form-select"> <option value="">Choose</option> <option value="Direct">Direct</option> <option value="Telephone">Telephone</option> </select> </td> <td align='center'> <input type="date" name="SubmitedDate[]" class="form-control border border-success SubmitedDate" autocomplete="off" /> </td> <td align='center'> <input type="text" name="Remarks[]" class="form-control border border-success Remarks" autocomplete="off" /> </td> <td > <input type="number" name="lang[]" value="${n}" class="form-control border border-success" autocomplete="off" /> </td> </tr>`; $("#product_data tbody").append(newRow); }); }); </script> This script is adding additional rows and I am able to fill the data I visited additionally apart from the list. Once I save the data I want to retrieve and display the data on the same table for that particular month. For that, I am using the bellow ajax script <script> function viewData() { var Month = document.getElementById("Month").value; var ID = '<?php echo $EmpNo; ?>'; $.ajax({ type: 'get', dataType: 'JSON', url: 'Customer_Data_View.php', data: 'Month=' + Month + '&employeeno=' + ID, success: function (response) { if (!$.trim(response)) { $('.SubmitedDate').val('') } else { $('.SubmitedDate').val('') var len = response.length; for (var i = 0; i < len; i++) { var id = response[i].id; var CustomerName = response[i].CustomerName; var CustomerCity = response[i].CustomerCity; var VisitInterval = response[i].VisitInterval; var VisitPlan = response[i].VisitPlan; var VisitType = response[i].VisitType; var VisitDate = response[i].VisitDate; var Remarks = response[i].Remarks; document.getElementById('CustomerName' + id).value = CustomerName; document.getElementById('CustomerCity' + id).value = CustomerCity; document.getElementById('interval' + id).value = VisitInterval; document.getElementById('plan' + id).value = VisitPlan; document.getElementById('type' + id).value = VisitType; document.getElementById('SubmitedDate' + id).value = VisitDate; document.getElementById('Remarks' + id).value = Remarks; if (VisitType == 'Not Visited' || VisitType == 'No Due') { document.getElementById("SubmitedDate" + id).readOnly = true; } else { document.getElementById("SubmitedDate" + id).readOnly = false; } } } } }); $.ajax({ type: 'get', dataType: 'JSON', url: 'Customer_Data_Button.php', data: 'Month=' + Month + '&employeeno=' + ID, success: function (response) { if (response['success']) { $('#button').hide(); } else { $('#button').show(); } } }); }; </script> Once I submit the data, I cannot modify it further because the buttons will get hidden automatically. My Customer_Data_View.php is <?PHP session_start(); error_reporting(0); ini_set('display_errors', 'Off'); include '../connection.php'; if (isset($_GET['Month'])) { $Month = $_GET['Month']; $Employeeid = $_GET['employeeno']; // Check if there is any data for the given month and employee $check = "SELECT * FROM customerdata WHERE EmpID = '$Employeeid' AND Month ='$Month'"; $result = mysqli_query($conn, $check); if (mysqli_num_rows($result) == 0) { // If no data, get data from the machinemaster and customerassign tables $query = "SELECT c.* FROM sbms.machinemaster AS a INNER JOIN sbms.customerassign AS c ON c.RowID = a.id WHERE c.EmpID = '$Employeeid' AND a.MachineStatus='A'"; $result = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($result)) { $VisitInterval = $row['VisitInterval']; $VisitPlan = $row['VisitPlan']; $VisitType = $row['VisitType']; $id = $row['RowID']; $VisitDate = ""; $Remarks = ""; $CustomerName = ""; $CustomerCity = ""; $return_arr[] = array( "VisitInterval" => $VisitInterval, "VisitPlan" => $VisitPlan, "VisitType" => $VisitType, "id" => $id, "VisitDate" => $VisitDate, "Remarks" => $Remarks, "CustomerName" => $CustomerName, "CustomerCity" => $CustomerCity ); } } else { // If data exists for the month, retrieve it while ($row = mysqli_fetch_assoc($result)) { $return_arr[] = $row; } } // Return the data as JSON echo json_encode($return_arr); } ?> When I am fetching the data it is displaying only the data available in the table. it is not display the data which is dynamically added. Can anyone please suggest to me how to display the dynamically added rows of data in the table along with the existing data
  5. Senthilkumar
    Dear Mr.Barand, I corrected the highlighted data type. I will modify the crossed columns later because I need to alter them in many places. I added all the customers from the machinemaster to the customermaster. I used a LEFT JOIN on the customermaster at the bottom of the query. I have included the plant name in the query because I want to display the plant name as well. The new query is SELECT branchname , plantName , CustomerID , Customername , CustomerSegment , CustomerType , COALESCE(bill.JanBilling, '-') as JanBilling , COALESCE(bill.FebBilling, '-') as FebBilling , COALESCE(bill.MarBilling, '-') as MarBilling , COALESCE(bill.AprBilling, '-') as AprBilling , COALESCE(bill.MayBilling, '-') as MayBilling , COALESCE(bill.JunBilling, '-') as JunBilling , COALESCE(bill.JulBilling, '-') as JulBilling , COALESCE(bill.AugBilling, '-') as AugBilling , COALESCE(bill.SepBilling, '-') as SepBilling , COALESCE(bill.OctBilling, '-') as OctBilling , COALESCE(bill.NovBilling, '-') as NovBilling , COALESCE(bill.DecBilling, '-') as DecBilling FROM ( SELECT br.branchname, p.plantName , b.sold_party as customerid , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.branch br ON p.branchid = br.branchid WHERE b.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND b.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY branchname, customerid UNION SELECT m.branchname, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid; I executed the query for the CHENNAI branch, but I am not getting the proper output. I obtained a list of 851 customers for the CHENNAI branch from the query output. In the machine master, I got a list of 658 customers using the query: SELECT DISTINCT customerid FROM sbms.machinemaster WHERE machinestatus = 'A' and BranchCode='801'; Within this list of 658 customers, 71 customers are not listed in the query output. I don't know where the mistake is happening. I have uploaded the new data dump at the link below for your reference. https://drive.google.com/file/d/1HCq7djniswgS_yIB5_s1lqJ7UOc421Lr/view?usp=sharing I have been stuck with this problem for the last 3 days. Please help me get the proper output.
  6. Senthilkumar
    please paste the full code for understand
  7. Senthilkumar
    Dear Mr. Barand, Thank you for your suggestion, and I apologize for the delayed response. I tried your code, but I am only getting the output from the billing table's customer list, not from the machine master customer list. For example, I filtered only one branch for testing, Chennai (801 in b.sales_office and 5 in br.branchid). In the machine master, I have a list of 657 customers based on machinestatus = 'A'. However, I am only getting a list of 603 customers from the query output. Could you please help me resolve this issue?
  8. Senthilkumar
    Dear Team, I have the below query to get my output SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, b.VisitType, d.Fname as Name, e.deptName as Department FROM sbms.machinemaster as a LEFT JOIN sbms.customerassign as b ON b.RowID = a.id INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') GROUP BY a.BranchCode, a.CustomerID order by Branch When I am using the above query it takes 62 seconds to execute. But I have another query which is the same as the above SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, b.VisitType, d.Fname as Name, e.deptName as Department FROM sbms.machinemaster as a LEFT JOIN sbms.customerassign as b ON b.CustomerID = a.CustomerID INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') GROUP BY a.BranchCode, a.CustomerID order by Branch This query is executing within 2 seconds. The only difference between both the queries is b.RowID = a.id and b.CustomerID = a.CustomerID on my left join. I create the index for all my join columns. can anyone please suggest why the first query takes a long time to execute? I uploaded my table dump here for your reference. https://drive.google.com/file/d/1zQTJV-2ppQPjyEdxtOwAh_EVnOaQ-TOO/view?usp=sharing
  9. Senthilkumar
    Dear Mr.Barand, Sorry for the mistakes you found on the table. I overlooked that. Now that mistakes have been corrected and the new table dump has been uploaded the link below https://drive.google.com/file/d/1Cd6-PkwetPseNvt64JxA3oIC_p88vEs3/view?usp=sharing I tried the code below. I am getting the output sum values matching and I think all the customers list I am getting. SELECT machine.Branch, machine.CustomerID, machine.Customername, machine.CustomerSegment, machine.CustomerType, COALESCE(bill.JanBilling, 0) as JanBilling, COALESCE(bill.FebBilling, 0) as FebBilling, COALESCE(bill.MarBilling, 0) as MarBilling, COALESCE(bill.AprBilling, 0) as AprBilling, COALESCE(bill.MayBilling, 0) as MayBilling, COALESCE(bill.JunBilling, 0) as JunBilling, COALESCE(bill.JulBilling, 0) as JulBilling, COALESCE(bill.AugBilling, 0) as AugBilling, COALESCE(bill.SepBilling, 0) as SepBilling, COALESCE(bill.OctBilling, 0) as OctBilling, COALESCE(bill.NovBilling, 0) as NovBilling, COALESCE(bill.DecBilling, 0) as DecBilling FROM ( SELECT b.branchName as Branch, d.CustomerID as CustomerID, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType FROM sbms.machinemaster as a INNER JOIN sbms.customermaster as d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch as b ON b.branchcode = a.BranchCode WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID ORDER BY b.branchName ASC ) as machine LEFT JOIN ( SELECT a.sold_party as CustomerID, c.branchName as Branch, b.plantName as Plant, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM sbms.billing as a INNER JOIN sbms.plant as b ON b.plantCode = a.sales_office INNER JOIN sbms.branch as c ON c.branchID = b.branchID WHERE a.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND a.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY a.sold_party, c.branchID ORDER BY c.branchName, b.plantName ) as bill USING (CustomerID, Branch) UNION SELECT bill.Branch, bill.CustomerID, bill.Customername, bill.CustomerSegment, bill.CustomerType, COALESCE(bill.JanBilling, 0) as JanBilling, COALESCE(bill.FebBilling, 0) as FebBilling, COALESCE(bill.MarBilling, 0) as MarBilling, COALESCE(bill.AprBilling, 0) as AprBilling, COALESCE(bill.MayBilling, 0) as MayBilling, COALESCE(bill.JunBilling, 0) as JunBilling, COALESCE(bill.JulBilling, 0) as JulBilling, COALESCE(bill.AugBilling, 0) as AugBilling, COALESCE(bill.SepBilling, 0) as SepBilling, COALESCE(bill.OctBilling, 0) as OctBilling, COALESCE(bill.NovBilling, 0) as NovBilling, COALESCE(bill.DecBilling, 0) as DecBilling FROM ( SELECT b.branchName as Branch, d.CustomerID as CustomerID, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType FROM sbms.machinemaster as a INNER JOIN sbms.customermaster as d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch as b ON b.branchcode = a.BranchCode WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID ORDER BY b.branchName ASC ) as machine RIGHT JOIN ( SELECT a.sold_party as CustomerID, c.branchName as Branch, b.plantName as Plant, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM sbms.billing as a INNER JOIN sbms.plant as b ON b.plantCode = a.sales_office INNER JOIN sbms.branch as c ON c.branchID = b.branchID INNER JOIN sbms.customermaster as d ON d.CustomerID = a.sold_party WHERE a.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND a.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY a.sold_party, c.branchID ORDER BY c.branchName, b.plantName ) as bill USING (CustomerID, Branch) ORDER BY Branch, Customername; Can you please confirm whether the above code is correct or not, in the above code I want to filter the result branch-wise and plant-wise. Also, the right join query takes 5 to 6 seconds to execute. Can you please suggest me
  10. Senthilkumar
    Dear Mr Barand, Please help me
  11. Senthilkumar
    No. It should display along with 417 customer which is listed from billing table. Note: Total is not 620. Because some new customers billing has been done which is not available in machine master. So first we have to list all the customers from billing and along with we have to display the customers from machine master who is not available on billing table ( not done single billing of any month).
  12. Senthilkumar
    We have to check the machine master which customer is not listed on the billing result branch wise and display those customers as 0 billing done on the output report
  13. Senthilkumar
    Dear Team, I have the table of Machine master branch-wise, Billing table branch-wise and the common customer master table. I want to display the billing summary branch-wise with all customers from both tables(Billing and Machine master). I am using the following query select Branch,Plant, CustomerID, Customername, CustomerCity, CustomerSegment, CustomerType, JanBilling, FebBilling,MarBilling,AprBilling,MayBilling,JunBilling,JulBilling,AugBilling,SepBilling,OctBilling,NovBilling,DecBilling from (SELECT b.branchName as Branch,c.plantName as Plant,d.CustomerID as CustomerID,d.CustomerName as Customername,d.CustomerCity as CustomerCity,d.CustomerSegment as CustomerSegment,d.CustomerType as CustomerType FROM sbms.machinemaster as a inner join sbms.customermaster as d on d.CustomerID = a.CustomerID inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.plant as c on c.branchID = b.branchID WHERE 1 and a.MachineStatus='A' group by a.CustomerID,b.branchcode order by b.branchName ASC) as machine left join (SELECT d.CustomerID as CustomerID, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END ) / 100000 AS JanBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END ) / 100000 AS FebBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END ) / 100000 AS MarBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END ) / 100000 AS AprBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END ) / 100000 AS MayBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END ) / 100000 AS JunBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END ) / 100000 AS JulBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END ) / 100000 AS AugBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END ) / 100000 AS SepBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END ) / 100000 AS OctBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END ) / 100000 AS NovBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END ) / 100000 AS DecBilling FROM sbms.billing as a inner join sbms.customermaster as d on d.CustomerID = a.sold_party inner join sbms.plant as b on b.plantCode = a.sales_office inner join sbms.branch as c on c.branchID = b.branchID WHERE 1 and a.sales_doc_type in('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') and a.division NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') group by a.sold_party, a.sales_office ) as bill using (CustomerID) For example, in the billing table sales office 809, I am getting 417 customer lists (SELECT * FROM sbms.billing where sales_office ='809' and sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') group by sold_party). in Customer master BranchCode 809, I am getting 620 customers (SELECT * FROM sbms.machinemaster where BranchCode='809' and MachineStatus='A' group by CustomerID). I want to display the customer's list who are not doing the billing. But I am not getting proper output. I uploaded my table dump on the below link https://drive.google.com/file/d/1X_TXl3uCcuyzCXrMgvfF7wpC3aF-YInL/view?usp=sharing Please help me to get my required output.
  14. Senthilkumar
    Dear Mr.Barand, Thanks for your suggestion. I found the mistake in line number 21376 column M, the value entered with \ (Bend DN125 5 1/2" 32,5° S2000\). Because of that the data is not imported. I removed that special character and imported the data. How to avoid such type of mistakes in features
  15. Senthilkumar
    Dear Team, I have PHP code which is used to upload the data from CSV files to the database. My code is <?php // Load the database configuration file define("HOST", 'localhost'); define("USERNAME", 'root'); define("PASSWORD", '123456'); define("DATABASE", 'sbms'); // default database name function pdoConnect($dbname = DATABASE) { $db = new PDO("mysql:host=" . HOST . ";dbname=$dbname;charset=utf8", USERNAME, PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } $pdo = pdoConnect(); if (isset($_POST['importsubmit'])) { $file = $_FILES['file']['tmp_name']; $fp = fopen($file, 'r'); $req_cols = [ 0 => 'sales_doc_type', 2 => 'billing_date', 11 => 'material', 12 => 'description', 14 => 'billed_quantity', 25 => 'gross_amount', 38 => 'sold_party', 39 => 'customername', 45 => 'sales_office', 46 => 'plant', 63 => 'dchannel', 64 => 'distribution_channel_description', 73 => 'division', 74 => 'division_header' ]; $import_data = []; fgetcsv($fp); // discard the header row while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); if (empty($row[0])) continue; // skip rows where no value in first col $randdays = rand(5, 30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $row[39] = str_replace("'", "", $row[39]); $row[12] = str_replace("'", "", $row[12]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", $row); } fclose($fp); $chunks = array_chunk($import_data, 3000); foreach ($chunks as $ch) { $sql = "INSERT IGNORE INTO billing (sales_doc_type, billing_date, material, description, billed_quantity,gross_amount, sold_party, customername, sales_office, plant, dchannel, distribution_channel_description, division, division_header) VALUES " . join(',', $ch); $pdo->exec($sql); } } // Redirect to the listing page header("Location: Billing.php"); ?> I am using two CSV files (April Service.csv and APRIL SPARE.csv). When I am importing April Service.csv, it is updating properly. But when I am importing APRIL SPARE.csv file I get the following error Fatal error: Uncaught ValueError: The arguments array must contain 14 items, 12 given in C:\Users\senthilkumar.rp\OneDrive - SCHWING Stetter (India) Private Limited\SBMS\sbms\sbms\SuperAdmin\Sales_Import.php:55 Stack trace: #0 C:\Users\senthilkumar.rp\OneDrive - SCHWING Stetter (India) Private Limited\SBMS\sbms\sbms\SuperAdmin\Sales_Import.php(55): vsprintf() #1 {main} thrown in C:\Users\senthilkumar.rp\OneDrive - SCHWING Stetter (India) Private Limited\SBMS\sbms\sbms\SuperAdmin\Sales_Import.php on line 55 I uploaded the CSV files and table here for your reference on the below link. https://drive.google.com/drive/folders/1UIVW6Ya5D2shwwvxEK6WiyBYSVCdNTtX?usp=sharing I think there is some mistake on the csv file. I am not able to identify that problem. Can any one help me solve this problem.
  16. Senthilkumar
    Dear Mr.mac_gyver & Mr.Barand, Thanks for your support. The above code is working properly.
  17. Senthilkumar
    Dear Mr.Barand, Can you please guide me
  18. Senthilkumar
    Ok. I understood. I included the end month also in the user table. Please check the new dump on the below link https://drive.google.com/file/d/1Hhcx8CX_EjdcEI-hoPAmVXbr6bLNTkLF/view?usp=sharing
  19. Senthilkumar
    Dear Mr.mac_gyvr, I need to calculate the average every year (year-wise), not cumulative average.
  20. Senthilkumar
    Dear Mr.Barand, we can use the below query to display the name and branch from the user table. $query = "SELECT a.*, b.Name as Name, b.Branch as Branch FROM kra.mark as a inner join kra.users as b on b.Emp_No = a.Emp_No $where"; For your reference, I upload the user's table below link https://drive.google.com/file/d/1Cj8uxhX08z98qdGOmcvZxeU4B2gH31qP/view?usp=sharing I created a column Startmonth and updated it when it starts from. Once the user leaves, his account is deactivated and the Status becomes 0. If the status is 0 we can calculate up to the last updated month. Kindly suggest me how to do this
  21. Senthilkumar
    Dear Mr.Barand, At the starting of this project I don't plan for these calculations. So I didn't store the details like when he is joined and when he is left. The branch will be fetched from user table with inner join query using employee number
  22. Senthilkumar
    Dear Mr.Barand, I am just storing the data month-wise. Please refer the table dump with the below link. https://drive.google.com/file/d/1kRszTxRE0GZZlQG4FIkvOQupYv-itK8e/view?usp=sharing This is an old project. Please suggest me how to modify the table structure and how to calculate the average
  23. Senthilkumar
    Dear Team, I want to calculate the engineer's average mark for every year. I am using the bellow code for calculating $where_columns = ['Year' => 'Year', 'Branch' => 'Branch']; $CurrentYear = date('Y'); $where_terms = []; foreach ($where_columns as $col => $input) { if ($_POST[$input] == !'') { $where_terms[] = "$col= '$_POST[$input]' "; } } $where = ''; if (!empty($where_terms)) { $where = 'WHERE ' . implode(' AND ', $where_terms); } else { $where = 'WHERE 1 '; } $query = "SELECT * FROM kra.mark $where"; $result = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($result)) { $Emp_Name = $row['Emp_Name']; $Emp_No = $row['Emp_No']; $Branch = $row['Branch']; $Year = $row['Year']; $Jan = $row["Jan"]; $Feb = $row["Feb"]; $Mar = $row["Mar"]; $Apr = $row["Apr"]; $May = $row["May"]; $Jun = $row["Jun"]; $Jul = $row["Jul"]; $Aug = $row["Aug"]; $Sep = $row["Sep"]; $Oct = $row["Oct"]; $Nov = $row["Nov"]; $Dec = $row["Decm"]; if($Jan == ""){ $Jan1 = 0; }else{ $Jan1 = $Jan; } if ($Feb == "") { $Feb1 = 0; } else { $Feb1 = $Feb; } if ($Mar == "") { $Mar1 = 0; } else { $Mar1 = $Mar; } if ($Apr == "") { $Apr1 = 0; } else { $Apr1 = $Apr; } if ($May == "") { $May1 = 0; } else { $May1 = $May; } if ($Jun == "") { $Jun1 = 0; } else { $Jun1 = $Jun; } if ($Jul == "") { $Jul1 = 0; } else { $Jul1 = $Jul; } if ($Aug == "") { $Aug1 = 0; } else { $Aug1 = $Aug; } if ($Sep == "") { $Sep1 = 0; } else { $Sep1 = $Sep; } if ($Oct == "") { $Oct1 = 0; } else { $Oct1 = $Oct; } if ($Nov == "") { $Nov1 = 0; } else { $Nov1 = $Nov; } if ($Dec == "") { $Dec1 = 0; } else { $Dec1 = $Dec; } if ($Year == $CurrentYear) { $currentMonth = date('M', strtotime(date('Y-m') . " -1 month")); if ($currentMonth == 'Dec') { $result2 = "Decm"; } else { $result2 = $currentMonth; } if ($row[$result2] == '') { $previous_month = date('m', strtotime(date('Y-m') . " -2 month")); } else { $previous_month = date('m', strtotime(date('Y-m') . " -1 month")); } try { $TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / $previous_month, 2); } catch (DivisionByZeroError $e) { $TotalMark = '0'; } } else { try { $TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / 12, 2); } catch (DivisionByZeroError $e) { $TotalMark = '0'; } } } The engineer will submit the data every month and has one month time to submit the data. For example, March month data he can submit up to April 30th. After that, he cannot submit the March month data. For the current year, I will check if the previous month's data is submitted or not. based on that I will sum all the values and divided it by the previous month. For the previous year, I directly summed up all the values and divided them by 12. My requirement is, that if any engineer is added in the middle of the year and starts updating the data, then the calculation should start from that particular month. In the above picture, s.No: 228 has started updating from Jul 2023. The average should sum J(un+Jul_Aug+Sep+Oct+Nov+Dec / 6) I don't know how to do this. Can anyone please help me with how to do this?
  24. Senthilkumar
    I changed the code as per your instructions. Now it is working. <?PHP include("../connection.php"); $Month = $_POST["Month"]; $Region = $_POST["Region"]; $Equipment = $_POST["Equipment"]; if ($Equipment == '') { $EquipCondition = ''; } else { $EquipCondition = 'AND ModelGroup = ' .$Equipment.''; } if ($Region == '') { $Mc = "SELECT Region, CuTotal, CuAssigned FROM ( SELECT count(distinct a.CustomerID) as CuTotal, c.regnName as Region FROM sbms.machinemaster as a inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.region as c on c.regnID = b.regnID WHERE a.MachineStatus = 'A' $EquipCondition AND c.regnID !='5' group by c.regnID) total LEFT JOIN (Select count(a.RowID) as CuAssigned , c.regnName as Region FROM sbms.customerdata as a inner join sbms.branch as b on b.branchID = a.Branch inner join sbms.region as c on c.regnID = b.regnID inner join sbms.machinemaster as d on d.id = a.RowID WHERE a.Month = '$Month' $EquipCondition and d.MachineStatus='A' AND a.VisitType !='No Due' AND a.Status ='1' group by c.regnID order by c.regnID ASC ) assigned USING (Region)"; $Mcresult = mysqli_query($conn, $Mc); $McNoRow = mysqli_num_rows($Mcresult); $return_arr[] = array('Region', 'Active Customer', 'Visited'); while ($Mcrow = mysqli_fetch_array($Mcresult)) { $CustRegion = $Mcrow['Region']; $CusTotal = $Mcrow['CuTotal']; $CusAssigned = $Mcrow['CuAssigned']; $return_arr1[] = array( $CustRegion ); $return_arr2[] = array( $CusTotal ); $return_arr3[] = array( $CusAssigned ); } echo json_encode(array("CustRegion" => $return_arr1, "CusTotal" => $return_arr2, "CusAssigned" => $return_arr3)); } if ($Region !== '') { $Mc = "SELECT Branch, McTotal, McAssigned FROM ( SELECT count(distinct a.CustomerID) as McTotal, b.branchName as Branch FROM sbms.machinemaster as a inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.region as c on c.regnID = b.regnID WHERE a.MachineStatus = 'A' $EquipCondition AND c.regnID ='$Region' group by b.branchcode) total LEFT JOIN (Select count(a.RowID) as McAssigned , b.branchName as Branch FROM sbms.customerdata as a inner join sbms.branch as b on b.branchID = a.Branch inner join sbms.region as c on c.regnID = b.regnID inner join sbms.machinemaster as d on d.id = a.RowID WHERE a.Month = '$Month' $EquipCondition and d.MachineStatus='A' AND b.regnID = '$Region' AND a.VisitType !='No Due' AND a.Status ='1' group by b.branchID ) assigned USING (Branch)"; $Mcresult = mysqli_query($conn, $Mc); $McNoRow = mysqli_num_rows($Mcresult); $return_arr[] = array('Region', 'Active Machine', 'Procuction Data Collected'); while ($Mcrow = mysqli_fetch_array($Mcresult)) { $CustRegion = $Mcrow['Branch']; $CusTotal = $Mcrow['McTotal']; $CusAssigned = $Mcrow['McAssigned']; $return_arr1[] = array( $CustRegion ); $return_arr2[] = array( $CusTotal ); $return_arr3[] = array( $CusAssigned ); } echo json_encode(array("CustRegion" => $return_arr1, "CusTotal" => $return_arr2, "CusAssigned" => $return_arr3)); } ?> <script type="text/javascript"> $(document).ready(function () { ProductionFilter(); }); $(document).on('change', '.filter', function () { ProductionFilter(); }); function ProductionFilter() { var Month = document.getElementById("Month").value; var Region = document.getElementById("Region").value; var Branch = document.getElementById("Branch").value; var Equipment = document.getElementById("Equipment").value; $.ajax({ url: 'Customer_Graph_Filter.php', type: 'POST', data: 'Month=' + Month + '&Region=' + Region + '&Branch=' + Branch + '&Equipment=' + Equipment, dataType: 'JSON', success: function (response) { var CustomerRegion = response.CustRegion; var CustomerAssigned = response.CusAssigned; var CustomerTotal = response.CusTotal; var barChartData = { labels: CustomerRegion, datasets: [{ label: 'Total Customers', backgroundColor: 'rgba(0, 158, 251, 0.5)', borderColor: 'rgba(0, 158, 251, 1)', borderWidth: 1, data: CustomerTotal }, { label: 'Data Collected', backgroundColor: 'rgba(255, 188, 53, 0.5)', borderColor: 'rgba(255, 188, 53, 1)', borderWidth: 1, data: CustomerAssigned }] }; var myoption = { responsive: true, tooltips: { enabled: true }, hover: { animationDuration: 1 }, scales: { yAxes: [{ ticks: { beginAtZero: true, } }], xAxes: [{ ticks: { autoSkip: false } }] }, animation: { duration: 1, onComplete: function () { var chartInstance = this.chart, ctx = chartInstance.ctx; ctx.textAlign = 'center'; ctx.fillStyle = "rgba(0, 0, 0, 1)"; ctx.textBaseline = 'bottom'; this.data.datasets.forEach(function (dataset, i) { var meta = chartInstance.controller.getDatasetMeta(i); meta.data.forEach(function (bar, index) { var data = dataset.data[index]; ctx.fillText(data, bar._model.x, bar._model.y + 1); }); }); } }, maintainAspectRatio: true }; var ctx = document.getElementById('CustomerBarGraph').getContext('2d'); window.myBar = new Chart(ctx, { type: 'bar', data: barChartData, options: myoption, }); } }); } </script> Thanks for your support
  25. Senthilkumar
    Below is the script <script type="text/javascript"> $(document).ready(function () { ProductionFilter(); }); $(document).on('change', '.filter', function () { ProductionFilter(); }); function ProductionFilter() { var Month = document.getElementById("Month").value; var Region = document.getElementById("Region").value; var Branch = document.getElementById("Branch").value; var Equipment = document.getElementById("Equipment").value; $.ajax({ url: 'Customer_Graph_Filter.php', type: 'POST', data: 'Month=' + Month + '&Region=' + Region + '&Branch=' + Branch + '&Equipment=' + Equipment, dataType: 'JSON', success: function (chart_values) { console.log(chart_values); // Total Customer Vs Data Collected var barChartData = { labels: CustomerRegion, datasets: [{ label: 'Total Customers', backgroundColor: 'rgba(0, 158, 251, 0.5)', borderColor: 'rgba(0, 158, 251, 1)', borderWidth: 1, data: CustomerAssigned }, { label: 'Data Collected', backgroundColor: 'rgba(255, 188, 53, 0.5)', borderColor: 'rgba(255, 188, 53, 1)', borderWidth: 1, data: CustomerTotal }] }; var myoption = { responsive: true, tooltips: { enabled: true }, hover: { animationDuration: 1 }, scales: { yAxes: [{ ticks: { beginAtZero: true, } }], xAxes: [{ ticks: { autoSkip: false } }] }, animation: { duration: 1, onComplete: function () { var chartInstance = this.chart, ctx = chartInstance.ctx; ctx.textAlign = 'center'; ctx.fillStyle = "rgba(0, 0, 0, 1)"; ctx.textBaseline = 'bottom'; this.data.datasets.forEach(function (dataset, i) { var meta = chartInstance.controller.getDatasetMeta(i); meta.data.forEach(function (bar, index) { var data = dataset.data[index]; ctx.fillText(data, bar._model.x, bar._model.y + 1); }); }); } }, maintainAspectRatio: true }; var ctx = document.getElementById('CustomerBarGraph').getContext('2d'); window.myBar = new Chart(ctx, { type: 'bar', data: barChartData, options: myoption, }); } }); } </script> Customer_Graph_Filter.php <?PHP include("../connection.php"); $Month = $_POST["Month"]; $Region = $_POST["Region"]; $Equipment = $_POST["Equipment"]; if ($Equipment == '') { $EquipCondition = ''; } else { $EquipCondition = 'AND ModelGroup = ' .$Equipment.''; } if ($Region == '') { $Mc = "SELECT Region, CuTotal, CuAssigned FROM ( SELECT count(distinct a.CustomerID) as CuTotal, c.regnName as Region FROM sbms.machinemaster as a inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.region as c on c.regnID = b.regnID WHERE a.MachineStatus = 'A' $EquipCondition AND c.regnID !='5' group by c.regnID) total LEFT JOIN (Select count(a.RowID) as CuAssigned , c.regnName as Region FROM sbms.customerdata as a inner join sbms.branch as b on b.branchID = a.Branch inner join sbms.region as c on c.regnID = b.regnID inner join sbms.machinemaster as d on d.id = a.RowID WHERE a.Month = '$Month' $EquipCondition and d.MachineStatus='A' AND a.VisitType !='No Due' AND a.Status ='1' group by c.regnID order by c.regnID ASC ) assigned USING (Region)"; $Mcresult = mysqli_query($conn, $Mc); $McNoRow = mysqli_num_rows($Mcresult); $return_arr[] = array('Region', 'Active Customer', 'Visited'); while ($Mcrow = mysqli_fetch_array($Mcresult)) { $McRegion = $Mcrow['Region']; $McTotal = $Mcrow['CuTotal']; $McAssigned = $Mcrow['CuAssigned']; $return_arr[] = array( $McRegion, $McTotal, $McAssigned, ); } echo json_encode($return_arr, JSON_NUMERIC_CHECK); } if ($Region !== '') { $Mc = "SELECT Branch, McTotal, McAssigned FROM ( SELECT count(distinct a.CustomerID) as McTotal, b.branchName as Branch FROM sbms.machinemaster as a inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.region as c on c.regnID = b.regnID WHERE a.MachineStatus = 'A' $EquipCondition AND c.regnID ='$Region' group by b.branchcode) total LEFT JOIN (Select count(a.RowID) as McAssigned , b.branchName as Branch FROM sbms.customerdata as a inner join sbms.branch as b on b.branchID = a.Branch inner join sbms.region as c on c.regnID = b.regnID inner join sbms.machinemaster as d on d.id = a.RowID WHERE a.Month = '$Month' $EquipCondition and d.MachineStatus='A' AND b.regnID = '$Region' AND a.VisitType !='No Due' AND a.Status ='1' group by b.branchID ) assigned USING (Branch)"; $Mcresult = mysqli_query($conn, $Mc); $McNoRow = mysqli_num_rows($Mcresult); $return_arr[] = array('Region', 'Active Machine', 'Procuction Data Collected'); while ($Mcrow = mysqli_fetch_array($Mcresult)) { $McRegion = $Mcrow['Branch']; $McTotal = $Mcrow['McTotal']; $McAssigned = $Mcrow['McAssigned']; $return_arr[] = array( $McRegion, $McTotal, $McAssigned, ); } echo json_encode($return_arr, JSON_NUMERIC_CHECK); } ?> The response I am getting [ [ "Region", "Active Customer", "Visited" ], [ "EAST", 1843, 289 ], [ "WEST", 3000, 402 ], [ "NORTH", 1733, 32 ], [ "SOUTH", 2568, 285 ] ] I don't know how to append this response to graph
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.