Jump to content

Senthilkumar

Members
  • Posts

    171
  • Joined

  • Last visited

Posts posted by Senthilkumar

  1. 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

  2. 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.

  3. 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

     

  4. 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

  5. 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.

    image.thumb.png.29764a73b7ab177fd8a9515b6459c6f2.png

    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?

  6. 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

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

  8. Dear Team,

    I am using Canvas grap on my PHP project. I am using PHP query on the same page and defining the as a variable on a script and passing the script to another js file.

    My code is 

    $previous_month = date('Y-m', strtotime(date('Y-m') . " -1 month"));
    $Year = date('Y');
    
    
    $Mc = "SELECT Region,  McTotal, McAssigned FROM
    			( SELECT count(a.BranchCode) as McTotal, 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' AND c.regnID !='5'  group by c.regnID ) total
    
    			LEFT JOIN (Select count(a.RowID) as McAssigned , c.regnName as Region FROM sbms.production as a
    			inner join sbms.branch as b on b.branchID = a.Branch
    			inner join sbms.region as c on c.regnID = b.regnID WHERE a.Month = '$previous_month'
    			group by c.regnID order by c.regnID ASC ) assigned USING (Region)";
    $Mcresult = mysqli_query($conn, $Mc);
    
    while ($Mcrow = mysqli_fetch_array($Mcresult)) {
    	$McRegion[] = $Mcrow['Region'];
    	$McTotal[] = $Mcrow['McTotal'];
    	$McAssigned[] = $Mcrow['McAssigned'];
    }
    
    $MachineTotal = json_encode($McAssigned);
    $MachineAssigned = json_encode($McTotal);
    $MachineRegion = json_encode($McRegion);
    
    
    <div class="col-12 col-md-6 col-lg-6 col-xl-6">
    				<div class="card">
    					<div class="card-body">
    						<div class="chart-title">
    							<div class="float-left">
    								<h5>Active Machine vs Production Data Collected for <?PHP echo $previous_month1; ?></h5>
    							</div>
    							<div class="float-right">
    								<a href="Machine_Report.php" type="button" class="btn btn-default subs-btn">View Details</a>
    							</div>
    						</div>
    						<canvas id="MachineBarGraph"></canvas>
    					</div>
    				</div>
    </div>
    
    
    <script type="text/javascript">
    	var MachineTotal = <?php echo $MachineTotal ?>;
    	var MachineAssigned = <?php echo $MachineAssigned ?>;
    	var MachineRegion = <?php echo $MachineRegion ?>;	
    </script>
    
    <script src="../assets/js/DashboardGraph.js"></script>

    DashboardGraph.js

    
    
    
    $(document).ready(function () {
    
        // Active Equipment Vs Data Collected
    
        var barChartData = {
            labels: MachineRegion,
            datasets: [{
    
                label: 'Active Machine',
                backgroundColor: 'rgba(0, 158, 251, 0.5)',
                borderColor: 'rgba(0, 158, 251, 1)',
                borderWidth: 1,
                data: MachineAssigned
            }, {
                label: 'Data Collected',
                backgroundColor: 'rgba(255, 188, 53, 0.5)',
                borderColor: 'rgba(255, 188, 53, 1)',
                borderWidth: 1,
                data: MachineTotal
            }]
        };
    
        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('MachineBarGraph').getContext('2d');
        window.myBar = new Chart(ctx, {
            type: 'bar',
            data: barChartData,
            options: myoption,
        });
    
    
        
    });

     

    I want to apply a filter to this query. For that, I want to use the Ajax method. so that if I change my filter, the graph will change accordingly.

    I don't know how to do this. Can any one please help me how to do this

  9. Dear Team, 

    I wrote a MySQL query with multiple inner joins using multiple tables. I am getting my required output with this query. But executing this query takes 6 to 7 seconds.

    My query is 

    SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM
    				(SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM sbms.target as a
    				inner join sbms.material as b on b.id = a.Category
    				inner join sbms.equipment as c on c.eqipID = b.eqipID
    				inner join sbms.plant as d on d.plantCode =  a.Branch 
                    inner join sbms.branch as e on e.branchID =  d.branchID  where e.branchID='2' group by c.eqipID) TG
    
    				LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as Billing from sbms.billing as a
    				inner join sbms.division as b on b.divCode = a.division
    				inner join sbms.equipment as c on c.eqipID = b.eqipID
    				inner join sbms.distributionchannel as d on d.dcno = a.dchannel
    				inner join sbms.plant as e on e.plantCode= a.sales_office
                    inner join sbms.branch as f on f.branchID = e.branchID
    				where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') between '2024-01' and '2024-03' and f.branchID='2'
    				and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) Billing USING (Category)
    
    				LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as PBilling from sbms.billing as a
    				inner join sbms.division as b on b.divCode = a.division
    				inner join sbms.equipment as c on c.eqipID = b.eqipID
    				inner join sbms.distributionchannel as d on d.dcno = a.dchannel
    				inner join sbms.plant as e on e.plantCode= a.sales_office
                    inner join sbms.branch as f on f.branchID = e.branchID
    				where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') = '2024-03'  and f.branchID='2'
    				and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) PBilling USING (Category)

    All my table dump and PHP files are attached to the link below

    https://drive.google.com/file/d/1VLz-9EJoMEbxqbkykMT13iKaPLPu99u8/view?usp=sharing

     

    Can anyone please suggest me to work this as fast.

  10. As per your code the below output is coming

    image.thumb.png.4bf13fbb90dd8a38db286c05e58738de.png

    But there is the data available for Production Actual & Total for 2024-02 month for Ragul M

    I want the output should show if any one of the field data is submitted.

  11. Dear Mr.Barand,

    The above query is working.

    I have another query that is used for another user. 

    SELECT EmpID, Month, ProductionActual,CustomerActual, MarketingTotal,MarketingActual FROM
    (select count(*) as ProductionActual, Month, EmpID  from sbms.production WHERE EmpID='OWC10148' AND NOT (MCubicmeter = '0' AND MHourmeter ='0') group by Month) ProductionActual 
    LEFT JOIN ( select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='OWC10148' AND (VisitType != 'Not Visited') group by Month) CustomerActual USING (Month)
    LEFT JOIN (select count(*) as MarketingTotal, Month from sbms.marketing_target WHERE EmpID='OWC10148' group by Month ) marketing_target USING (Month)
    LEFT JOIN (select count(*) as MarketingActual, Month from sbms.marketing_data WHERE EmpID='OWC10148' AND Date!=''  group by Month ) MarketingActual USING (Month)

    The output of this query is 

    image.png.1e8440ae82c409f69b3bf332744fe600.png

     

    In this, I am having value CustomerActual is 3 for the month of 2024-03. But the output is not displaying here because ProductionActual is not there for the month of 2024-03. If am given any value for ProductionActual on 2024-03, then the third row (2024-03) is displayed on my output. 

    Please suggest me.

  12. 6 hours ago, Barand said:

    I can't see any data for 2024-04 in marketing_data table, so it gives this when I combine all

    image.png.1e38328e7fa8b081a3803a3e98bd99f8.png

    ok

  13. Dear Mr. Barand,

    We both are working together. I am in personal emergency leave. So informed her to post the query here. 

     

  14. Dear Team,

    I have a table which is generating data dynamically. Below is my table

    image.thumb.png.476496eca85b97182e935a61583ecd15.png

    My table code is 

    <table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;">
    
    				<thead class="bg-blue text-center">
    					<tr>
    						<th style="width: 5%; vertical-align: middle">S.No</th>
    						<th style="width: 25%; vertical-align: middle">Customer Name</th>
    						<th style="width: 12%; vertical-align: middle">Location</th>
    						<th style="width: 8%; vertical-align: middle">Model</th>
    						<th style="width: 8%; vertical-align: middle">Machine S.No</th>
    						<th style="width: 10%; vertical-align: middle">Machine Status</th>
    						<th style="width: 8%; vertical-align: middle">Monthly (Cu.m) <span class="text-danger">*</span></th>
    						<th style="width: 8%; vertical-align: middle">Total (Cu.m)</th>
    						<th style="width: 8%; vertical-align: middle">Monthly (Hr.m)</th>
    						<th style="width: 8%; vertical-align: middle">Total (Hr.m)</th>
    					</tr>
    				</thead>
    				<tbody>
    					<?php
    
    					$n = 1;
    					$sql = "select * from machinemaster  AS a INNER JOIN machineassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo'";
    					$result = mysqli_query($conn, $sql);
    					while ($row = mysqli_fetch_assoc($result)) {
    
    						$ID = $row['id'];
    						$CustomerName = $row['CustomerName'];
    						$MachineLocation = $row['MachineLocation'];
    						$Model = $row['Model'];
    						$MachineNo = $row['MachineSlNo'];
    						$RowID = $row['RowID'];
    						?>
    						<tr>
    							<td align='center'>
    								<?php echo $n++; ?>
    							</td>
    							<td>
    								<?php echo $CustomerName; ?>
    							</td>
    							<td>
    								<?php echo $MachineLocation; ?>
    							</td>
    							<td>
    								<?php echo $Model; ?>
    							</td>
    							<td>
    								<?php echo $MachineNo; ?>
    							</td>
    							<td>
    								<select name='Ststus[]' id='Status<?php echo $RowID; ?>' class='form-control Status' style="width:auto">
    									<option value='1'>Working</option>
    									<option value='0'>Not Working</option>
    								</select>
    							</td>
    							<td align='center' hidden>
    								<input type="number" name="RowID[]" id="RowID<?php echo $RowID; ?>" value="<?php echo $RowID; ?>" class="form-control" autocomplete="off" />
    							</td>
    							<td align='center'>
    								<input type="number" name="MCubicmeter[]" id="MCubicmeter<?php echo $RowID; ?>" value="" class="form-control MCubicmeter text-center cucalc" autocomplete="off" />
    							</td>
    							<td align='center'>
    								<input type="number" name="TCubicmeter[]" id="TCubicmeter<?php echo $RowID; ?>" value="" class="form-control TCubicmeter text-center " autocomplete="off" readonly />
    							</td>
    							<td align='center'>
    								<input type="number" name="MHourmeter[]" id="MHourmeter<?php echo $RowID; ?>" value="" class="form-control MHourmeter text-center" autocomplete="off" />
    							</td>
    							<td align='center'>
    								<input type="number" name="THourmeter[]" id="THourmeter<?php echo $RowID; ?>" value="" class="form-control THourmeter text-center" autocomplete="off" readonly />
    							</td>
    							<td hidden>
    								<input type="number" name="lang[]" value="<?php echo $RowID; ?>" class="form-control" autocomplete="off" />
    							</td>
    
    						</tr>
    						<?php
    					}
    
    					?>
    
    				</tbody>
    </table>

     

    In this table, the machine status is a dropdown (Working & Networking). When I select the not working on any row, the Monthly (Cu.m)  & Monthly (Hr.m) columns should disabled.  otherwise, it should be editable.

    I have tried the below code. 

    <script>
    				$(document).on('change', '#product_data tbody tr td:nth-child(6)', function () {
    									var rowvlaue = row($(this).closest('tr')).data()[12];
    									
    									
    				});
    </script>

    Afte this I am not sure how to go ahead. 

    Please help me how to complete this.

  15. Dear Team,

     

    I am writing a query to count rows based on the two conditions. But I am not getting proper output.

    My table is 

    image.png.40a00542671cdfb987563096a25c6d08.png

    my query is 

    select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='83201858' AND  NOT(VisitType = 'No Due' AND VisitDate ='')  group by Month

    My condition is when the date is empty it should not count that row. But if the visit type is 'No due' it should count the row.

    for example, in 2024-04  month the actual row is 5. As per the above condition, the row count is 4. Because the visit type is No due on ID 1713 and ID 1712 the date is blank. so the count is 4. 

    Instead of No due, if any other is in the visit type column on id 1713, then it should not count. the total count should show on 3. 

    Can anyone help me with how to do this?

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