Senthilkumar

Dear Barand, Thanks for your reply. It is working perfetly as per my requirement. Can you please explain this code for my better understand of this

Dear Barand, Pls reply

[{"TIV_Status":"Pending from Dealer","Marketingpromotion_Status":"Pending from Dealer","Revenue_Status":"Pending from 1st Approver","Equipementpopulation_Status":"Pending from 1st Approver","Employeeexpense_Status":"Pending from Dealer","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"Feb-2023","TIV":"0","Marketingpromotion":"0","Revenue":"1","Equipementpopulation":"1","Employeeexpense":"0","Infrastructureexpense":"0","Otherexpense":"0"},{"TIV_Status":"Pending from 1st Approver","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from 1st Approver","Equipementpopulation_Status":"Pending from 1st Approver","Employeeexpense_Status":"Pending from 1st Approver","Infrastructureexpense_Status":"Pending from 1st Approver","Otherexpense_Status":"Pending from 1st Approver","Month":"Apr-2023","TIV":"1","Marketingpromotion":"1","Revenue":"1","Equipementpopulation":"1","Employeeexpense":"1","Infrastructureexpense":"1","Otherexpense":"1"},{"TIV_Status":"Pending from 2nd Approver","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from Dealer","Equipementpopulation_Status":"Pending from Dealer","Employeeexpense_Status":"Pending from Dealer","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"May-2023","TIV":"2","Marketingpromotion":"1","Revenue":"0","Equipementpopulation":"0","Employeeexpense":"0","Infrastructureexpense":"0","Otherexpense":"0"},{"TIV_Status":"Approval Completed","Marketingpromotion_Status":"Pending from 1st Approver","Revenue_Status":"Pending from Dealer","Equipementpopulation_Status":"Pending from Dealer","Employeeexpense_Status":"Pending from 1st Approver","Infrastructureexpense_Status":"Pending from Dealer","Otherexpense_Status":"Pending from Dealer","Month":"Jun-2023","TIV":"3","Marketingpromotion":"1","Revenue":"0","Equipementpopulation":"0","Employeeexpense":"1","Infrastructureexpense":"0","Otherexpense":"0"}]

My MIS_Table.php code is <?php include("../connection.php"); $dealerid = $_GET["dealerid"]; $query = "select * from monthly_mis_status WHERE dealerid = '$dealerid' ORDER BY month ASC "; $result = mysqli_query($conn, $query); while($row = mysqli_fetch_array($result)){ $TIV= $row['TIV']; $Marketingpromotion= $row['Marketingpromotion']; $Revenue= $row['Revenue']; $Equipementpopulation= $row['Equipementpopulation']; $Employeeexpense= $row['Employeeexpense']; $Infrastructureexpense = $row['Infrastructureexpense']; $Otherexpense = $row['Otherexpense']; $Month = $row['month']; $Month_Format = date('M-Y', strtotime($Month)); if ($TIV == 0) { $TIVStatus = "Pending from Dealer"; } else if ($TIV ==1) { $TIVStatus = "Pending from 1st Approver"; } else if ($TIV == 2) { $TIVStatus = "Pending from 2nd Approver"; } else if ($TIV == 3) { $TIVStatus = "Approval Completed"; } if ($Marketingpromotion == 0) { $Marketingpromotionstatus = "Pending from Dealer"; } else if ($Marketingpromotion == 1) { $Marketingpromotionstatus = "Pending from 1st Approver"; } else if ($Marketingpromotion == 2) { $Marketingpromotionstatus = "Pending from 2nd Approver"; } else if ($Marketingpromotion == 3) { $Marketingpromotionstatus = "Approval Completed"; } if ($Equipementpopulation == 0) { $Equipementpopulationstatus = "Pending from Dealer"; } else if ($Equipementpopulation == 1) { $Equipementpopulationstatus = "Pending from 1st Approver"; } else if ($Equipementpopulation == 2) { $Equipementpopulationstatus = "Pending from 2nd Approver"; } else if ($Equipementpopulation == 3) { $Equipementpopulationstatus = "Approval Completed"; } if ($Employeeexpense == 0) { $Employeeexpenseststus = "Pending from Dealer"; } else if ($Employeeexpense == 1) { $Employeeexpenseststus = "Pending from 1st Approver"; } else if ($Employeeexpense == 2) { $Employeeexpenseststus = "Pending from 2nd Approver"; } else if ($Employeeexpense == 3) { $Employeeexpenseststus = "Approved Completed"; } if ($Revenue == 0) { $RevenueStatus = "Pending from Dealer"; } else if ($Revenue == 1) { $RevenueStatus = "Pending from 1st Approver"; } else if ($Revenue == 2) { $RevenueStatus = "Pending from 2nd Approver"; } else if ($Revenue == 3) { $RevenueStatus = "Approval Completed"; } if ($Infrastructureexpense == 0) { $Infrastructureexpenseststus = "Pending from Dealer"; } else if ($Infrastructureexpense == 1) { $Infrastructureexpenseststus = "Pending from 1st Approver"; } else if ($Infrastructureexpense == 2) { $Infrastructureexpenseststus = "Pending from 2nd Approver"; } else if ($Infrastructureexpense == 3) { $Infrastructureexpenseststus = "Approval Completed"; } if ($Otherexpense == 0) { $Otherexpenseststus = "Pending from Dealer"; } else if ($Otherexpense == 1) { $Otherexpenseststus = "Pending from 1st Approver"; } else if ($Otherexpense == 2) { $Otherexpenseststus = "Pending from 2nd Approver"; } else if ($Otherexpense == 3) { $Otherexpenseststus = "Approval Completed"; } $return_arr[] = array( "TIV_Status" => $TIVStatus, "Marketingpromotion_Status" => $Marketingpromotionstatus, "Revenue_Status" => $RevenueStatus, "Equipementpopulation_Status" => $Equipementpopulationstatus, "Employeeexpense_Status" => $Employeeexpenseststus, "Infrastructureexpense_Status" => $Infrastructureexpenseststus, "Otherexpense_Status" => $Otherexpenseststus, "Month" => $Month_Format, "TIV" => $TIV, "Marketingpromotion" => $Marketingpromotion, "Revenue" => $Revenue, "Equipementpopulation" => $Equipementpopulation, "Employeeexpense" => $Employeeexpense, "Infrastructureexpense" => $Infrastructureexpense, "Otherexpense" => $Otherexpense, ); } echo json_encode($return_arr);

Dear Team, I am appending the table using Ajax script on my php page. In my table i am having 7 columns (TIV, Marketingpromotion, Revenue, Equipementpopulation, Employeeexpense, Infrastructureexpense, Otherexpense). All these columns are updating from Mysql databse sucessfully. But i want to change the text colour based on the conditon. My table output is My condition is If the value is 0 it should display "Pending from Dealer" using class name of "pending", if value is 1 it shout display "Pending from 1st Approver" using class name of "progress", if value is 2 it shout display "Pending from 1st Approver" using class name of "progress" and if value is 3 it shout display "Pending from 1st Approver" using class name of "submited". My code is <script> $('.Filter').click(function () { var dealerid = document.getElementById("Dealer").value; var empno = <?PHP echo $Emp_No ?>; $.ajax({ type: 'get', dataType: 'JSON', url: 'MIS_Table.php', data: 'dealerid=' + dealerid + '&empno=' + empno , success: function (response) { if(response){ var len = response.length; $("#userTable tbody").empty(); for (var i = 0; i < len; i++) { var TIV_Status = response[i].TIV_Status; var Marketingpromotion_Status = response[i].Marketingpromotion_Status; var Revenue_Status = response[i].Revenue_Status; var Equipementpopulation_Status = response[i].Equipementpopulation_Status; var Employeeexpense_Status = response[i].Employeeexpense_Status; var Infrastructureexpense_Status = response[i].Infrastructureexpense_Status; var Otherexpense_Status = response[i].Otherexpense_Status; var Month = response[i].Month; var TIV = response[i].TIV; var Marketingpromotion = response[i].Marketingpromotion; var Revenue = response[i].Revenue; var Equipementpopulation = response[i].Equipementpopulation; var Employeeexpense = response[i].Employeeexpense; var Infrastructureexpense = response[i].Infrastructureexpense; var Otherexpense = response[i].Otherexpense; var tr_str = "<tr>" + "<td align='center'>" + (i + 1) + "</td>" + "<td align='center'>" + Month + "</td>" + "<td>" + TIV_Status + "</td>" + "<td>" + Marketingpromotion_Status + "</td>" + "<td>" + Revenue_Status + "</td>" + "<td>" + Equipementpopulation_Status + "</td>" + "<td>" + Employeeexpense_Status + "</td>" + "<td>" + Infrastructureexpense_Status + "</td>" + "<td>" + Otherexpense_Status + "</td>" + "</tr>"; $("#userTable tbody").append(tr_str); TIV == '0' ? document.querySelectorAll("td")[2].classList.add('pending') : document.querySelectorAll("td")[2].classList.add('submited'); Marketingpromotion == '0' ? document.querySelectorAll("td")[3].classList.add('pending') : document.querySelectorAll("td")[3].classList.add('submited'); Revenue == '0' ? document.querySelectorAll("td")[4].classList.add('pending') : document.querySelectorAll("td")[4].classList.add('submited'); Equipementpopulation == '0' ? document.querySelectorAll("td")[5].classList.add('pending') : document.querySelectorAll("td")[5].classList.add('submited'); Employeeexpense == '0' ? document.querySelectorAll("td")[6].classList.add('pending') : document.querySelectorAll("td")[6].classList.add('submited'); Infrastructureexpense == '0' ? document.querySelectorAll("td")[7].classList.add('pending') : document.querySelectorAll("td")[7].classList.add('submited'); Otherexpense == '0' ? document.querySelectorAll("td")[8].classList.add('pending') : document.querySelectorAll("td")[8].classList.add('submited'); } } } }); }); </script> Witht the above code the color is changing only on the first row. Not for all the row. And i dont know how to apply all the four conditions. Can any onr please help me to solve this issue

Dear Barand, Thanks for your support. it is working ap per my requirement

Dear Team, I am having Mysql databse table in the name of fieldtable. My table looks bellow image I want to fetch the data from this table where the value is 1 and display on my PHP table row wise How cani display the data like above picture. Can any one help me this

Dear Barand, Thanks for your suggession. It is working now. My final query is SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select id, concat(YEAR(CURDATE()), '-', month) as Month from dbms.month ) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) OtherExpense USING(Month) ORDER BY mnth.id; And table is

It will store the month only

Dear Barand, Pls suggest me

InfraExpense Table Revenue Table Employee Expense Table Other Expense

I enter the month directly on the table by my self for checking the condition

I didnt noticed that. Now it is working properly SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select id, Month from dbms.month ) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) OtherExpense USING(Month) ORDER BY mnth.id; I need one suggession from you. In month table we pre defined the year. How to do this for next year. Is ther any other way to capure the current year on this table

I tried the end of this query But i am getting error of Error Code: 1054. Unknown column 'mnth.id' in 'order clause'

My table is Where to use ORDER BY mnth.id on query

Dear Brand, I have created the separeate table for month and changed the query with left join <?php $query = "SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select Month from dbms.month) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);"; $exec = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($exec)) { $Month = $row['Month']; $Revanue = $row['Revanue']; $EmployeeExpense= $row['EmployeeExpense']; $InfraExpense = $row['InfraExpense']; $OtherExpense = $row['OtherExpense']; $formatedMonth = date("M-Y", strtotime($Month)); $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense; if ($Expenditure != 0 && $Revanue != 0) { $Absorption_Ratio = ($Revanue / $Expenditure) * 100; }else{ $Absorption_Ratio = 0; } echo "['$formatedMonth',$Absorption_Ratio],"; } ?> I am getting 12 columns on the graph. For testing i updated the datas for the month of Jan, Feb, Mar and Jun. My grap the month is displaying Jan,Feb,Mar,Jun,Apr,May,Jul,Aug,Sep,Oct,Nov,Dec Can you please tell me how to correct this

Dear Team, I am using Google column chart on my dahboard for displaying the datat from mysql databse. The chart h axis i want to display all the month by default like bellow image and for which month the data is available that only diplay here like bellow image. Iam using the bellow query for fetch the data from database <?php; $query = "SELECT revenue.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) empExp USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) infExp USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);"; $exec = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($exec)) { $Month = $row['Month']; $Revanue = $row['Revanue']; $EmployeeExpense= $row['EmployeeExpense']; $InfraExpense = $row['InfraExpense']; $OtherExpense = $row['OtherExpense']; $formatedMonth = date("M-Y", strtotime($Month)); $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense; $Absorption_Ratio = ($Revanue / $Expenditure )*100; echo "['$formatedMonth',$Absorption_Ratio],"; } ?> I am getting output like below image This is displaying the month only where there is data available. How can i change it to display all the months on haxis

Thank you

Dear Barand, I cleared the spaces on revenue_dep. Now i am getting output from the query. I have one more question. From my table revenue_data i am getting output of 5 rows. From my table revenuetarget i am getting output of 3 rows. With Joining of two tables the output is showing 3 rows only. Is it possible to display 5 rows joining both the tables and display 0 for Others and Workshop on column target. Pls guide me

Ok. I will check the table. Thanks for ur reply

Empty result

Dear Barand, Pls help me

I am using this script to display the data on my PHP page. So i wrote the my sql code on single line. On your previous suggession i changed the single line query and it works perfectly on my another page. SELECT act.category, target, actual FROM ( SELECT category, sum(quantity) as actual FROM tiv_data WHERE brand = 'XCMG' AND dealerid = '81019218' GROUP BY category ) act JOIN ( SELECT category, sum(total) as target FROM currentyeardata WHERE Dealer_ID = '81019218' GROUP BY category ) targ USING (category);

Dear Barand, I tried the same query for another table. But the output is not working. My query is SELECT act.Revenue_Dep, target, actual FROM ( SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep ) act JOIN ( SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep ) targ USING (Revenue_Dep) The output is If i use bot the query separtly i am getting output SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep; SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep; Can you please tell me where i am doing the mistake on Join query

Dear Barand, Thanks for your support. It is working now.