Haloo! Selamat Datang di Komunitas Private Training Web Development (48216 Members, 455 Projects, 3700 Topic, 10094 Comments)

Butuh karyawan baru? atau seorang Web Developer (Part-time/Freelance/Full-time), cari disini : Members Area Jobs!
Rumus untuk Menjumlahkan transaksi Pertanggal pada query
Teguh iman budhi Mengatakan : Members -
Rate Topic :
  1. Assalamualaikum Warahmatullahi Wabarakatuh...
  2.  
  3. Saya mau tanya rumusnya menjumlahkan transaksi dalam pertanggal bagaimana caranya ya kuerinya
  4. yang saya buat sesaui gambar sepertini ini :
  5.  
  6. $sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
  7. $no = 0;
  8. while($p = mysql_fetch_assoc($sql))
  9. {
  10. $no++;
  11. $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]%'"));
  12. echo"<tr>
  13. <td>$no</td>
  14. <td>$p[tgl_transaksi]</td>
  15. <td>$sqla[qty]</td>
  16. <td>$p[metode] $p[kartu]</td>
  17. <td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
  18. </tr>";
  19. }
  20.  
  21. mohon informasinya
Ada 1 File Kiriman :
1. Untitled78.png (16 KB)
Bukhori Muslim Mengatakan :
Admin -
  1. Assalam,.. ini function total_penjualan() dari mana gan??,
  2. coba share kodingnya, karena untuk perhitungan ada disana.
  3. nanti tinggal diubah saja perhitungan disana jadi sum all.
  4.  
  5. atau jug bisa dengan cara seperti ini jika mau, kita manfaatkan dengan kode PHP :
  6. $sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
  7. $no = 0;
  8. $jumlah = 0;
  9. while($p = mysql_fetch_assoc($sql)){
  10. $no++;
  11. $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]%'"));
  12. $jumlah = $jumlah + total_penjualan($p['no_transaksi'],0,'.','.');
  13. echo"<tr>
  14. <td>$no</td>
  15. <td>$p[tgl_transaksi]</td>
  16. <td>$sqla[qty]</td>
  17. <td>$p[metode] $p[kartu]</td>
  18. <td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
  19. </tr>";
  20. }
  21.  
  22. echo "$jumlah";

Teguh iman budhi Mengatakan :
Members -
  1. Assalam,..
  2.  
  3. <?php
  4. date_default_timezone_set("Asia/Jakarta");
  5. $sqltrans = mysql_query("SELECT * FROM tb_pembelian") or die(mysql_error());
  6. $tra = mysql_fetch_assoc($sqltrans);
  7. ?>
  8.  
  9.  
  10. <center><h5><b>LAPORAN - BULANAN</b><br>
  11. <?php
  12. $bulan = $_GET['bulan'];
  13. echo date('F Y', strtotime($bulan));?></h5></center>
  14. <?php echo"</div>
  15. <div style='height:5px;'></div>";
  16.  
  17. echo"<table class='w3-table w3-tiny w3-hoverable w3-bordered tbl' cellpadding='0'>
  18. <thead>
  19. <tr class='w3-dark-grey'>
  20. <th>No</th>
  21. <!--<th>No Transaksi</th>-->
  22. <th>Tanggal Transaksi</th>
  23. <!--<th>Jenis Tarif</th>-->
  24. <th>QTY</th>
  25. <!--<th>Nama Pelanggan</th>-->
  26. <th>Metode Pembayaran</th>
  27. <th>Total</th>
  28. <!--<th>Petugas</th>-->
  29.  
  30. </tr>
  31. </thead>
  32.  
  33. <tbody>";
  34.  
  35. //$sql = mysql_query("SELECT * FROM tb_detail_penjualan group by timestmp LIKE '%$_GET[bulan]%'") or die(mysql_error());
  36. $sql = mysql_query("SELECT * FROM tb_penjualan where tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
  37. $no = 0;
  38. while($p = mysql_fetch_assoc($sql))
  39. {
  40. $no++;
  41. //$sqla = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_penjualan group by tgl_traksasi AND timestmp LIKE '%$_GET[bulan]%'"));
  42. $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]%'"));
  43. //$tglnya = mysql_fetch_array(mysql_query("SELECT COUNT(tgl_transaksi) as count FROM tb_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
  44. echo"<tr>
  45. <td>$no</td>
  46. <!--<td>$p[no_transaksi]</td>-->
  47. <td>$p[tgl_transaksi]</td>
  48. <!--<td>$p[tgl_transaksi]</td>-->
  49. <td>$sqla[qty]</td>
  50. <!--<td>$p[nama_pelanggan]</td>-->
  51. <td>$p[metode] $p[kartu]</td>
  52. <td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
  53. </tr>";
  54. }
  55.  
  56. $transaksi = mysql_fetch_array(mysql_query("SELECT COUNT(no_transaksi) as count FROM tb_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
  57. $qty = mysql_fetch_array(mysql_query("SELECT SUM(qty) as qty FROM tb_detail_penjualan WHERE timestmp LIKE '%$_GET[bulan]%'"));
  58. $total = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='cash' AND timestmp LIKE '%$_GET[bulan]%'"));
  59. $transfer = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='transfer' AND timestmp LIKE '%$_GET[bulan]%'"));
  60. $credit = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE metode='credit card' AND timestmp LIKE '%$_GET[bulan]%'"));
  61. $bca = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bca' AND timestmp LIKE '%$_GET[bulan]%'"));
  62. $mandiri = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='mandiri' AND timestmp LIKE '%$_GET[bulan]%'"));
  63. $maybank = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='maybank' AND timestmp LIKE '%$_GET[bulan]%'"));
  64. $citibank = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='citibank' AND timestmp LIKE '%$_GET[bulan]%'"));
  65. $bni = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bni' AND timestmp LIKE '%$_GET[bulan]%'"));
  66. $mega = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='mega' AND timestmp LIKE '%$_GET[bulan]%'"));
  67. $bri = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='bri' AND timestmp LIKE '%$_GET[bulan]%'"));
  68. $cimbniaga = mysql_fetch_array(mysql_query("SELECT SUM(total_harga) as total FROM tb_penjualan WHERE kartu='cimb niaga' AND timestmp LIKE '%$_GET[bulan]%'"));
  69.  
  70. echo"<table class='w3-table w3-tiny w3-hoverable w3-bordered tbl' cellpadding='0'>
  71. <thead>
  72. <tr class='w3-dark-grey'>
  73. <th>Total Golf Cart</th>
  74. <th>Total Transaksi - Cash</th>
  75. <th>Total Transaksi - Transfer</th>
  76. <th>Total Transaksi - Credit Card</th>
  77. <th>Credit Card - BCA</th>
  78. <th>Credit Card - Mandiri</th>
  79. <th>Credit Card - MayBank</th>
  80. <th>Credit Card - BRI</th>
  81. <th>Grand Total Transaksi</th>
  82. </tr>
  83. </thead>";
  84. echo"<tr>
  85. <td><b>".$qty['qty']."</b></td>
  86. <td><b>".number_format($total['total'])."</b></td>
  87. <td><b>".number_format($transfer['total'])."</b></td>
  88. <td><b>".number_format($credit['total'])."</b></td>
  89. <td><b>".number_format($bca['total'])."</b></td>
  90. <td><b>".number_format($mandiri['total'])."</b></td>
  91. <td><b>".number_format($maybank['total'])."</b></td>
  92. <td><b>".number_format($bri['total'])."</b></td>
  93. <td><b>".number_format($total['total']+$credit['total']+$transfer['total'])."</b></td>
  94.  
  95. </tr>"
  96. ?>
  97. <?php
  98. echo"<table class='w3-table w3-tiny w3-hoverable ' cellpadding='0'>
  99. <thead>
  100. <tr class='w3-dark-grey'>
  101. <th>Dibuat Oleh</th>
  102. <th>Kasir,</th>
  103. <th>Disetujui Oleh,</th>
  104. </tr>
  105. <tr>
  106.  
  107. <td></td>
  108. <td></td>
  109. <td></td>
  110. </tr>
  111. <tr>
  112.  
  113. <td></td>
  114. <td></td>
  115. <td></td>
  116. </tr>
  117. <tr>
  118.  
  119. <td></td>
  120. <td></td>
  121. <td></td>
  122. </tr>
  123. <tr>
  124.  
  125. <td></td>
  126. <td></td>
  127. <td></td>
  128. </tr>
  129. <tr>
  130.  
  131. <td></td>
  132. <td></td>
  133. <td></td>
  134. </tr>
  135. <tr>
  136.  
  137. <td></td>
  138. <td></td>
  139. <td></td>
  140. </tr>
  141. <tr>
  142.  
  143. <td><b>_________________</b></td>
  144. <td><b>_________________</b></br></td>
  145. <td><b>Masno Marjohan</b></br></td>
  146.  
  147.  
  148. </tr>
  149. </thead>";
  150.  
  151.  
  152.  
  153.  
  154.  
  155. ?>
  156.  
  157. <div style=""></div>
  158. <script type="text/javascript">
  159. window.onload = function(){
  160. window.print();
  161. }
  162. </script>

Bukhori Muslim Mengatakan :
Admin -
  1. Assalam,.. function ini mas mana : total_penjualan();
  2. saya lihat tidak ada pada script di atas... -_-
  3.  
  4. biasanya seperti ini modelnya nanti :
  5. function total_penjualan($kode){
  6. // Perhitungan ada disini...
  7. }
  8.  
  9. yang itu mau kita lihat, atau kalau gak ketemu pake ini sajalah mas :
  10. $sql = mysql_query("SELECT * FROM tb_penjualan WHERE tgl_transaksi LIKE '%$_GET[bulan]%'") or die(mysql_error());
  11. $no = 0;
  12. $jumlah = 0;
  13. while($p = mysql_fetch_assoc($sql)){
  14. $no++;
  15. $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]%'"));
  16. $jumlah = $jumlah + total_penjualan($p['no_transaksi'],0,'.','.');
  17. echo"<tr>
  18. <td>$no</td>
  19. <td>$p[tgl_transaksi]</td>
  20. <td>$sqla[qty]</td>
  21. <td>$p[metode] $p[kartu]</td>
  22. <td>".total_penjualan($p['no_transaksi'],0,'.','.')."</td>
  23. </tr>";
  24. }
  25.  
  26. echo "$jumlah";

Teguh iman budhi Mengatakan :
Members -
  1. Assalam,..
  2. <?php
  3. date_default_timezone_set('Asia/Jakarta');
  4.  
  5. function nama_m($id)
  6. {
  7. $sql = mysql_query("SELECT * FROM menu WHERE id_menu = '$id'") or die(mysql_error());
  8. $m = mysql_fetch_assoc($sql);
  9.  
  10. return $m['nama_menu'];
  11. }
  12.  
  13. function total_pembelian($no_faktur)
  14. {
  15. $sqlbeli = mysql_query("SELECT * FROM tb_detail_pembelian WHERE no_faktur = '$no_faktur'");
  16. $total = 0;
  17. while ($b = mysql_fetch_assoc($sqlbeli)) {
  18. $sub_total = $b['harga_beli'] * $b['qty'];
  19.  
  20. $total = $total + $sub_total;
  21. }
  22.  
  23. $rtotal = "Rp. ".number_format($total,0);
  24. return $rtotal;
  25. }
  26.  
  27. function total_penjualan($no_transaksi)
  28. {
  29. $sqljual = mysql_query("SELECT * FROM tb_detail_penjualan WHERE no_transaksi = '$no_transaksi'");
  30. $total = 0;
  31. while ($b = mysql_fetch_assoc($sqljual)) {
  32. $harga_disc = $b['harga'] - (($b['harga'] * $b['disc']) / 100);
  33. $sub_total = $harga_disc * $b['qty'];
  34.  
  35. $total = $total + $sub_total;
  36. }
  37.  
  38. $rtotal = "".number_format($total,0);
  39. return $rtotal;
  40. }
  41.  
  42. function nama_petugas($id)
  43. {
  44. $sql = mysql_query("SELECT * FROM user WHERE id_user = '$id'") or die(mysql_error());
  45. $m = mysql_fetch_assoc($sql);
  46.  
  47. return $m['nama_lengkap'];
  48. }
  49.  
  50. function nama_kategori($id)
  51. {
  52. $sql = mysql_query("SELECT nama_kategori FROM tb_kategori_barang
  53. WHERE kategori_id = '$id'") or die(mysql_error());
  54. $m = mysql_fetch_assoc($sql);
  55.  
  56. return $m['nama_kategori'];
  57. }
  58.  
  59. function no_kwitansi_auto()
  60. {
  61. $sql = mysql_query("SELECT MAX(RIGHT(no_transaksi,5)) AS notrans
  62. FROM tb_penjualan WHERE tgl_transaksi = '".date('Y-m-d')."'");
  63. $m = mysql_fetch_assoc($sql);
  64.  
  65. $no = 0;
  66. if($m['notrans'] <> NULL)
  67. {
  68. $kd = number_format($m['notrans'],0) + 1;
  69. if(strlen($kd) == 1)
  70. {
  71. $no = "CA".date('dmy')."0000".$kd;
  72. }
  73. elseif (strlen($kd) == 2) {
  74. $no = "CA".date('dmy')."000".$kd;
  75. }
  76. elseif (strlen($kd) == 3) {
  77. $no = "CA".date('dmy')."00".$kd;
  78. }
  79. elseif (strlen($kd) == 4) {
  80. $no = "CA".date('dmy')."0".$kd;
  81. }
  82. else {
  83. $no = "CA".date('dmy').$kd;
  84. }
  85. }
  86. else
  87. {
  88. $no = "CA".date('dmy')."00001";
  89. }
  90.  
  91. return $no;
  92. }
  93.  
  94. function stok_masuk($id)
  95. {
  96. $sql = mysql_query("SELECT qty FROM tb_detail_pembelian WHERE kode_barang = '$id'");
  97. $total = 0;
  98. while ($q = mysql_fetch_assoc($sql)) {
  99. $total = $total + $q['qty'];
  100. }
  101. return $total;
  102. }
  103.  
  104. function stok_keluar($id)
  105. {
  106. $sql = mysql_query("SELECT qty FROM tb_detail_penjualan WHERE kode_barang = '$id'");
  107. $total = 0;
  108. while ($q = mysql_fetch_assoc($sql)) {
  109. $total = $total + $q['qty'];
  110. }
  111. return $total;
  112. }
  113.  
  114. function stok_retur_jual($id)
  115. {
  116. $sql = mysql_query("SELECT qty FROM tb_retur_penjualan WHERE kode_barang = '$id'");
  117. $total = 0;
  118. while ($q = mysql_fetch_assoc($sql)) {
  119. $total = $total + $q['qty'];
  120. }
  121. return $total;
  122. }
  123.  
  124. function stok_retur_beli($id)
  125. {
  126. $sql = mysql_query("SELECT qty FROM tb_retur_pembelian WHERE kode_barang = '$id'");
  127. $total = 0;
  128. while ($q = mysql_fetch_assoc($sql)) {
  129. $total = $total + $q['qty'];
  130. }
  131. return $total;
  132. }
  133.  
  134. function anti_inject($data)
  135. {
  136. $filter_sql = stripslashes(strip_tags(htmlspecialchars($data,ENT_QUOTES)));
  137. return $filter_sql;
  138. }
  139.  
  140.  
  141.  
  142. ?>

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

Maaf, Untuk Memberikan Komentar Anda Harus Login !!!