buat group by untuk banyak medan

groupby

	public function bulanan($jadual = 'jan13') 
	{	
		// set pembolehubah untuk mengumpul respon yang wujud dalam $myTable
		$respon = $this->tanya->kumpul_respon($myTable = 'mdt_rangka13', 
			$myJoin = 'f2', $medan = 'respon', $jum = null);
        # semak pembolehubah $respon
        //echo '<pre>', print_r($respon, 1) . '</pre><br>';

		$r = 'c.respon'; $kumpul = null; $jumlah_kumpul = null;
        // mula papar semua dalam $myTable
        foreach ($respon as $key => $papar)
        {// mula ulang respon
			$kumpul.=",\rcount(if($r='" . $papar[$medan] 
				. "' and b.terima is not null,$r,null)) as `"
				. $papar[$medan] . "`";
			$jumlah_kumpul.="+count(if($r='" . $papar[$medan] 
				. "' and b.terima is not null,$r,null))\r";
		}// tamat ulang respon
        # semak pembolehubah $respon
        //echo '<pre>$kumpul:' . $kumpul . '<br>$jumlah_kumpul:' . $jumlah_kumpul . '</pre><br>';

		$laporan = $this->tanya->laporan_bulanan($r, $kumpul, $jumlah_kumpul, $jadual);
		$this->papar->cariNama[$jadual] = $laporan;
        # semak pembolehubah $laporan
        //echo '<pre>', print_r($laporan, 1) . '</pre><br>';

		// Set pemboleubah utama
		$this->papar->pegawai = senarai_kakitangan();
		$this->papar->fungsi = 'bulanan';
		$this->papar->url = dpt_url();
		// pergi papar kandungan
		$this->papar->baca('laporan/index', 0);
	}

	public function kumpul_respon($myTable, $myJoin, $medan, $jum)
	{
		//$jum['dari'] . ', ' . $jum['max']
		$sql = 'SELECT ' . $medan . ' FROM '
			 . $myTable . ' a INNER JOIN '.$myJoin.' b '
			 . ' WHERE a.' . $medan . '=b.kod '
			 . 'GROUP BY 1 ORDER BY no,' . $medan;
			
		$result = $this->db->selectAll($sql);
		//echo '<pre>' . $sql . '</pre><br>';
		//echo json_encode($result);
		
		return $result;
	}


	public function laporan_bulanan($r, $kumpul, $jumlah_kumpul, $myTable)
	{
		// pembolehubah yg terlibat
		$ALL="count(*)";
		$A1="count(if($r='A1' and b.terima is not null,$r,null))";
		$B1="count(if($r='B1',$r,null))";
		///////////////////////////////////////////////////////////////////////////////////////////////////
		// a1 bahagi (jumlah rangka - (a2-a6)
		$sasaran=array('A2','A3','A4','A5','A6'); //
		$SSR="count(if($r IN ('".implode("','",$sasaran)."') and b.terima is not null,$r,null))"; // kpi negatif
		$KPI="($A1 / ($ALL - $SSR) )*100";
		///////////////////////////////////////////////////////////////////////////////////////////////////
		$JANJI="count(if($r='B1' or b.terima is null,$r,null))";
		$AN="count(if($r NOT in ('A1','B1'),$r,null))";
		$BBU="count(if(b.utama='BBU',b.utama,null))";
		$BBU1="count(if($r='A1' and b.utama='BBU' and b.terima is not null,$r,null))";
		$BBUX="count(if($r!='A1' and b.utama='BBU' and  b.terima is not null,$r,null))";
		$SBU="count(if(b.utama='SBU',b.utama,null))";
		$SBU1="count(if($r='A1' and b.utama='SBU' and b.terima is not null,$r,null))";
		$SBUX="count(if($r!='A1' and b.utama='SBU' and b.terima is not null,$r,null))";
		$dpt="format(sum(if($r='A1' and b.terima is not null,b.hasil,null)),0)";
		$p="if (format(((($jumlah_kumpul)/$ALL)*100),2)=100.00,'Ya',':(' )";
		// mula cari sql berasaskan respon ,$ALL-($jumlah_kumpul) as `B1`
		///////////////////////////////////////////////////////////////////////////////////////////////////
		$sql = "SELECT c.fe, $p as `Dah`,\r$dpt AS `Hasil`,\r"
			 . "$JANJI as Janji,"
			 . "$ALL as Kes $kumpul,\r"
			 . "($jumlah_kumpul) as `Siap`,\r"
			 . "format(((($jumlah_kumpul)/count(*))*100),2) as `% Siap`,\r"
			 . "format((($A1/count(*))*100),2) as `% A1`,\r"
			 . "format($KPI,2) as `% KPI`,\r"
			 . "$AN `A-`, format((($AN/count(*))*100),2) as `% A-`,\r"
			 . "$BBU as `BBU`,\r$BBU1 as `BOK`,\r$BBUX as `BAN`,\r"
			 //. "($BBU-$BBU1-$BBUX) as `BB`,\r"
			 . "format((($BBU1/$BBU)*100),2) as `%B`,\r"
			 . "$SBU as `SBU`,\r$SBU1 as `SOK`,\r$SBUX as `SAN`,\r"
			 //. "($SBU-$SBU1-$SBUX) as `S`,\r"
			 . "format((($SBU1/$SBU)*100),2) as `%S`\r"
			 . "FROM mdt_rangka13 as c INNER JOIN mdt_$myTable as b\r"
			 . "ON c.newss=b.newss\r"
			 . "GROUP BY c.fe with rollup "; 

		$result = $this->db->selectAll($sql);
		//echo '<pre>' . $sql . '</pre><br>';
		//echo json_encode($result);
		
		return $result;	
	}