Rumus untuk Menjumlahkan transaksi Pertanggal pada query

Dibuat 6 Tahun lalu | 2018-10-04 14:08:45 WIB,

Telah Dilihat 5509 Kali

Sultan Account

Teguh iman budhi Mengatakan : Sultan Account

Assalamualaikum Warahmatullahi Wabarakatuh...
 
Saya mau tanya rumusnya menjumlahkan transaksi dalam pertanggal bagaimana caranya ya kuerinya
yang saya buat sesaui gambar sepertini ini : 
 
$sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
	$no = 0;
	while($p = mysql_fetch_assoc($sql))
	{
		$no++;
		$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE no_transaksi='$p[no_transaksi]' AND timestmp LIKE '%$_GET[bulan]%'"));
		echo"<tr>
			<td>$no</td>
			<td>$p[tgl_transaksi]</td>	
			<td>$sqla[qty]</td>
			<td>$p[metode] $p[kartu]</td>
			<td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
		</tr>";
	}
 
mohon informasinya

Ada 1 File Kiriman :

1. Untitled78.png (16 KB)

Ada 5 Jawaban

Bukhori Muslim
commented on 05 Okt 2018 08:45:29 Sultan Account

Assalam,.. ini function total_penjualan() dari mana gan??, 
coba share kodingnya, karena untuk perhitungan ada disana.
nanti tinggal diubah saja perhitungan disana jadi sum all.
 
atau jug bisa dengan cara seperti ini jika mau, kita manfaatkan dengan kode PHP :
$sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
$no = 0;
$jumlah = 0;
while($p = mysql_fetch_assoc($sql)){
	$no++;
	$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE no_transaksi='$p[no_transaksi]' AND timestmp LIKE '%$_GET[bulan]%'"));
	$jumlah = $jumlah + total_penjualan($p['no_transaksi'],0,'.','.');
	echo"<tr>
		<td>$no</td>
		<td>$p[tgl_transaksi]</td>	
		<td>$sqla[qty]</td>
		<td>$p[metode] $p[kartu]</td>
		<td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
	</tr>";
}
 
echo "$jumlah";

Teguh iman budhi
commented on 05 Okt 2018 09:12:45 Sultan Account

Assalam,.. 
 
<?php
	date_default_timezone_set("Asia/Jakarta");
	$sqltrans = mysql_query("SELECT * FROM tb_pembelian") or die(mysql_error());
	$tra = mysql_fetch_assoc($sqltrans);
?>
 
 
<center><h5><b>LAPORAN - BULANAN</b><br>
<?php
		$bulan = $_GET['bulan'];
		echo date('F Y', strtotime($bulan));?></h5></center>
	<?php echo"</div>
	<div style='height:5px;'></div>";
 
	echo"<table class='w3-table w3-tiny w3-hoverable w3-bordered tbl' cellpadding='0'>
		<thead>
		<tr class='w3-dark-grey'>
			<th>No</th>
			<!--<th>No Transaksi</th>-->
			<th>Tanggal Transaksi</th>
			<!--<th>Jenis Tarif</th>-->
			<th>QTY</th>
			<!--<th>Nama Pelanggan</th>-->
			<th>Metode Pembayaran</th>
			<th>Total</th>
			<!--<th>Petugas</th>-->
 
		</tr>
		</thead>
 
		<tbody>";
 
	//$sql = mysql_query("SELECT * FROM tb_detail_penjualan group by timestmp LIKE '%$_GET[bulan]%'") or die(mysql_error());
	$sql = mysql_query("SELECT * FROM tb_penjualan where tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
	$no = 0;
	while($p = mysql_fetch_assoc($sql))
	{
		$no++;
		//$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_penjualan group by tgl_traksasi AND timestmp LIKE '%$_GET[bulan]%'"));
		$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE no_transaksi='$p[no_transaksi]' AND timestmp LIKE '%$_GET[bulan]%'"));
		//$tglnya = mysql_fetch_array(mysql_query("SELECT COUNT(tgl_transaksi) as count FROM tb_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
		echo"<tr>
			<td>$no</td>
			<!--<td>$p[no_transaksi]</td>-->
			<td>$p[tgl_transaksi]</td>
			<!--<td>$p[tgl_transaksi]</td>-->
			<td>$sqla[qty]</td>
			<!--<td>$p[nama_pelanggan]</td>-->
			<td>$p[metode] $p[kartu]</td>
			<td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
		</tr>";
	}
 
$transaksi = mysql_fetch_array(mysql_query("SELECT COUNT(no_transaksi) as count FROM tb_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
$qty = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
$total = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='cash' AND timestmp LIKE '%$_GET[bulan]%'"));
$transfer = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='transfer' AND timestmp LIKE '%$_GET[bulan]%'"));
$credit = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='credit card' AND timestmp LIKE '%$_GET[bulan]%'"));
$bca = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bca' AND timestmp LIKE '%$_GET[bulan]%'"));
$mandiri = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='mandiri' AND timestmp LIKE '%$_GET[bulan]%'"));
$maybank = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='maybank' AND timestmp LIKE '%$_GET[bulan]%'"));
$citibank = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='citibank' AND timestmp LIKE '%$_GET[bulan]%'"));
$bni = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bni' AND timestmp LIKE '%$_GET[bulan]%'"));
$mega = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='mega' AND timestmp LIKE '%$_GET[bulan]%'"));
$bri = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bri' AND timestmp LIKE '%$_GET[bulan]%'"));
$cimbniaga = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='cimb niaga' AND timestmp LIKE '%$_GET[bulan]%'"));
 
	echo"<table class='w3-table w3-tiny w3-hoverable w3-bordered tbl' cellpadding='0'>
		<thead>
		<tr class='w3-dark-grey'>
			<th>Total Golf Cart</th>
			<th>Total Transaksi - Cash</th>
			<th>Total Transaksi - Transfer</th>
			<th>Total Transaksi - Credit Card</th>
			<th>Credit Card - BCA</th>
			<th>Credit Card - Mandiri</th>
			<th>Credit Card - MayBank</th>
			<th>Credit Card - BRI</th>
			<th>Grand Total Transaksi</th>
		</tr>
		</thead>";
		echo"<tr>
			<td><b>".$qty['qty']."</b></td>
			<td><b>".number_format($total['total'])."</b></td>
			<td><b>".number_format($transfer['total'])."</b></td>
			<td><b>".number_format($credit['total'])."</b></td>
			<td><b>".number_format($bca['total'])."</b></td>
			<td><b>".number_format($mandiri['total'])."</b></td>
			<td><b>".number_format($maybank['total'])."</b></td>
			<td><b>".number_format($bri['total'])."</b></td>
			<td><b>".number_format($total['total']+$credit['total']+$transfer['total'])."</b></td>
 
		</tr>"
?>
<?php
echo"<table class='w3-table w3-tiny w3-hoverable ' cellpadding='0'>
		<thead>
		<tr class='w3-dark-grey'>
			<th>Dibuat Oleh</th>
			<th>Kasir,</th>
			<th>Disetujui Oleh,</th>
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td></td>
			<td></td>
			<td></td>		
		</tr>
		<tr>
 
			<td><b>_________________</b></td>
			<td><b>_________________</b></br></td>
			<td><b>Masno Marjohan</b></br></td>
 
 
		</tr>
		</thead>";
 
 
 
 
 
?>
 
<div style=""></div>
<script type="text/javascript">
	window.onload = function(){
	window.print();
	}
	</script>

Bukhori Muslim
commented on 05 Okt 2018 09:24:21 Sultan Account

Assalam,.. function ini mas mana : total_penjualan();
saya lihat tidak ada pada script di atas... -_-
 
biasanya seperti ini modelnya nanti :
function total_penjualan($kode){
  // Perhitungan ada disini...
}
 
yang itu mau kita lihat, atau kalau gak ketemu pake ini sajalah mas :
$sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
$no = 0;
$jumlah = 0;
while($p = mysql_fetch_assoc($sql)){
	$no++;
	$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE no_transaksi='$p[no_transaksi]' AND timestmp LIKE '%$_GET[bulan]%'"));
	$jumlah = $jumlah + total_penjualan($p['no_transaksi'],0,'.','.');
	echo"<tr>
		<td>$no</td>
		<td>$p[tgl_transaksi]</td>	
		<td>$sqla[qty]</td>
		<td>$p[metode] $p[kartu]</td>
		<td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
	</tr>";
}
 
echo "$jumlah";

Teguh iman budhi
commented on 11 Okt 2018 07:54:16 Sultan Account

Assalam,.. 
<?php
	date_default_timezone_set('Asia/Jakarta');
 
	function nama_m($id)
	{
		$sql = mysql_query("SELECT * FROM menu WHERE id_menu = '$id'") or die(mysql_error());
		$m = mysql_fetch_assoc($sql);
 
		return $m['nama_menu'];
	}
 
	function total_pembelian($no_faktur)
	{
		$sqlbeli = mysql_query("SELECT * FROM tb_detail_pembelian WHERE no_faktur = '$no_faktur'");
		$total = 0;
		while ($b = mysql_fetch_assoc($sqlbeli)) {
			$sub_total = $b['harga_beli'] * $b['qty'];
 
			$total = $total + $sub_total;
		}
 
		$rtotal = "Rp. ".number_format($total,0);
		return $rtotal;
	}
 
	function total_penjualan($no_transaksi)
	{
		$sqljual = mysql_query("SELECT * FROM tb_detail_penjualan WHERE no_transaksi = '$no_transaksi'");
		$total = 0;
		while ($b = mysql_fetch_assoc($sqljual)) {
			$harga_disc = $b['harga'] - (($b['harga'] * $b['disc']) / 100);
			$sub_total = $harga_disc * $b['qty'];
 
			$total = $total + $sub_total;
		}
 
		$rtotal = "".number_format($total,0);
		return $rtotal;
	}
 
	function nama_petugas($id)
	{
		$sql = mysql_query("SELECT * FROM user WHERE id_user = '$id'") or die(mysql_error());
		$m = mysql_fetch_assoc($sql);
 
		return $m['nama_lengkap'];
	}
 
	function nama_kategori($id)
	{
		$sql = mysql_query("SELECT nama_kategori FROM tb_kategori_barang 
							WHERE kategori_id = '$id'") or die(mysql_error());
		$m = mysql_fetch_assoc($sql);
 
		return $m['nama_kategori'];
	}
 
	function no_kwitansi_auto()
	{
		$sql = mysql_query("SELECT MAX(RIGHT(no_transaksi,5)) AS notrans 
							FROM tb_penjualan WHERE tgl_transaksi = '".date('Y-m-d')."'");
		$m = mysql_fetch_assoc($sql);
 
		$no = 0;
		if($m['notrans'] <> NULL)
		{
			$kd = number_format($m['notrans'],0) + 1;
			if(strlen($kd) == 1)
			{
				$no = "CA".date('dmy')."0000".$kd;
			}
			elseif (strlen($kd) == 2) {
				$no = "CA".date('dmy')."000".$kd;
			}
			elseif (strlen($kd) == 3) {
				$no = "CA".date('dmy')."00".$kd;
			}
			elseif (strlen($kd) == 4) {
				$no = "CA".date('dmy')."0".$kd;
			}
			else {
				$no = "CA".date('dmy').$kd;
			}
		}
		else
		{
			$no = "CA".date('dmy')."00001";
		}
 
		return $no;
	}
 
	function stok_masuk($id)
	{
		$sql = mysql_query("SELECT qty FROM tb_detail_pembelian WHERE kode_barang = '$id'");
		$total = 0;
		while ($q = mysql_fetch_assoc($sql)) {
			$total = $total + $q['qty'];
		}
		return $total;
	}
 
	function stok_keluar($id)
	{
		$sql = mysql_query("SELECT qty FROM tb_detail_penjualan WHERE kode_barang = '$id'");
		$total = 0;
		while ($q = mysql_fetch_assoc($sql)) {
			$total = $total + $q['qty'];
		}
		return $total;
	}
 
	function stok_retur_jual($id)
	{
		$sql = mysql_query("SELECT qty FROM tb_retur_penjualan WHERE kode_barang = '$id'");
		$total = 0;
		while ($q = mysql_fetch_assoc($sql)) {
			$total = $total + $q['qty'];
		}
		return $total;
	}
 
	function stok_retur_beli($id)
	{
		$sql = mysql_query("SELECT qty FROM tb_retur_pembelian WHERE kode_barang = '$id'");
		$total = 0;
		while ($q = mysql_fetch_assoc($sql)) {
			$total = $total + $q['qty'];
		}
		return $total;
	}
 
	function anti_inject($data)
	{
		$filter_sql = stripslashes(strip_tags(htmlspecialchars($data,ENT_QUOTES)));
		return $filter_sql;
	}
 
 
 
?>

Bukhori Muslim
commented on 12 Okt 2018 07:42:03 Sultan Account

Assalam,.. $hitung = mysql_fetch_array(mysql_query("SELECT sum(harga-((harga*disc)/100)*qty) as jumlah_semua 
FROM tb_detail_penjualan WHERE no_transaksi = '$no_transaksi' AND timestmp LIKE '%$_GET[bulan]%'"));
 
echo "$hitung[jumlah_semua]";

Maaf, Untuk Memberikan Komentar Anda Harus Login !!!

	BSI - Bank Syariah Indonesia (451)

	ROBBY PRIHANDAYA
	1043-5665-19


	Bank Muamalat (147)
	ROBBY PRIHANDAYA
	4220-0174-81

Hallo! Selamat Datang di Marketplace produk digital, Freelancer terbaik di indonesia (68819 Members, 786 Products, 5384 Topic, 13070 Comments)

Rumus untuk Menjumlahkan transaksi Pertanggal pada query

Ada 5 Jawaban

Projects Remote

Hot Questions

Tentang Kami

Jam Layanan

Knowledgebase