Hallo! Selamat Datang di Marketplace produk digital, Freelancer terbaik di indonesia (67422 Members, 783 Products, 5280 Topic, 12953 Comments)

Rumus untuk Menjumlahkan transaksi Pertanggal pada query

Dibuat
Login Terakhir 2 Tahun lalu,
Telah Dilihat 3702 Kali
Teguh iman budhi Mengatakan : Sultan Account
  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)

Ada 5 Jawaban

Bukhori Muslim
commented on 05 Okt 2018 08:45:29
  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
commented on 05 Okt 2018 09:12:45
  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
commented on 05 Okt 2018 09:24:21
  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
commented on 11 Okt 2018 07:54:16
  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
commented on 12 Okt 2018 07:42:03
  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 !!!