Jump to content

Senthilkumar

Members
 View Profile  See their activity

  • Posts

    184

  • Joined

  • Last visited

Recent Profile Visitors

2,219 profile views

Senthilkumar's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

7

Community Answers

  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.
×
×
  • 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.