INSERT INTO pendekatan_harga_jual VALUES
(:id,
:id_proyek,
:no_kavling,
:ket,
:kategori,
:lb,
:lt,
:hj_bangunan,
:hb_tanah,
:indeks_tanah,
:h_dasar)
UPDATE pendekatan_harga_jual SET
id=:id,
id_proyek=:id_proyek,
no_kavling=:no_kavling,
ket=:ket,
kategori=:kategori,
lb=:lb,
lt=:lt,
hj_bangunan=:hj_bangunan,
hb_tanah=:hb_tanah,
indeks_tanah=:indeks_tanah,
h_dasar=:h_dasar
WHERE id=:OLD_id
DELETE FROM pendekatan_harga_jual WHERE :id
INSERT INTO proyek VALUES
(:id,
:nama_proyek,
:lokasi,
:total_lt,
:luas_jalan,
:luas_taman,
:lt_mushola,
:tgl_mulai,
:tgt_selesai_proyek,
:bgt_1lt_rs,
:bgt_1lt_rk,
:bgt_2lt_rk,
:hg_m2_tanah,
:ket,
:status)
UPDATE proyek SET
id=:id,
nama_proyek=:nama_proyek,
lokasi=:lokasi,
total_lt=:total_lt,
luas_jalan=:luas_jalan,
luas_taman=:luas_taman,
lt_mushola=:lt_mushola,
tgl_mulai=:tgl_mulai,
tgt_selesai_proyek=:tgt_selesai_proyek,
bgt_1lt_rs=:bgt_1lt_rs,
bgt_1lt_rk=:bgt_1lt_rk,
bgt_2lt_rk=:bgt_2lt_rk,
hg_m2_tanah=:hg_m2_tanah,
ket=:ket,
status=:status
WHERE id=:OLD_id
DELETE FROM proyek WHERE id=OLD_id
INSERT INTO `target_penjualan` VALUES
(:id,
:id_proyek,
:no_kavling,
:bln_akad,
:dp,
:termin_1,
:termin_2,
:termin_3,
:termin_4)
UPDATE `target_penjualan` SET
id=:id,
id_proyek=:id_proyek,
no_kavling=:no_kavling,
bln_akad=:bln_akad,
dp=:dp,
termin_1=:termin_1,
termin_2=:termin_2,
termin_3=:termin_3,
termin_4=:termin_4
WHERE id=:OLD_id
DELETE FROM `target_penjualan` WHERE id=:id
INSERT INTO cashflow_pembangunan VALUES
(:id,
:id_proyek,
:no_kavling,
:mulai_bangun)
UPDATE cashflow_pembangunan SET
id=:id,
id_proyek=:id_proyek,
no_kavling=:no_kavling,
mulai_bangun=:mulai_bangun
WHERE id=:OLD_id
DELETE FROM cashflow_pembangunan WHERE id=:id
INSERT INTO rab VALUES
(:id,
:id_proyek,
:kode_rab,
:uraian,
:volume,
:unit,
:harga_satuan,
:ket)
UPDATE rab SET
id=:id,
id_proyek=:id_proyek,
kode_rab=:kode_rab,
uraian=:uraian,
volume=:volume,
unit=:unit,
harga_satuan=:harga_satuan,
ket=:ket
WHERE id=:OLD_id
DELETE FROM rab WHERE id=:id
INSERT INTO bop VALUES
(:id,
:id_proyek,
:kode_rab,
:uraian,
:volume_kumulatif,
:volume_kuantiti,
:unit,
:harga_satuan,
:ket)
UPDATE bop SET
id=:id,
id_proyek=:id_proyek,
kode_rab=:kode_rab,
uraian=:uraian,
volume_kumulatif=:volume_kumulatif,
volume_kuantiti=:volume_kuantiti,
unit=:unit,
harga_satuan=:harga_satuan,
ket=:ket
WHERE id=:OLD_id
DELETE FROM bop WHERE id=:id
if (
($part == PagePart::RecordCard && $mode == PageMode::ExportPdf) ||
($part == PagePart::Grid && $mode == PageMode::PrintOneRecord)
) {
// pengeluaran harga pokok produksi
$hargaPokokProduksiDetail = array();
$hppId = GetApplication()->GetGETValue('pk0');
$sql1 = "
SELECT
p.nama_proyek,
p.lokasi,
hr.id_rab,
hr.nama_rab,
COALESCE(qrht.total_jumlah, 0) AS total_jumlah,
ROUND(
CASE
WHEN qnpp.nilai_penerimaan_penjualan IS NOT NULL AND qnpp.nilai_penerimaan_penjualan > 0
THEN (COALESCE(qrht.total_jumlah, 0) / qnpp.nilai_penerimaan_penjualan * 100)
ELSE 0
END, 2) AS persen,
ROUND(
CASE
WHEN hr.id_rab = 'B3' THEN
COALESCE(qrht.total_jumlah, 0) / (
SELECT COALESCE(SUM(qphj.lb), 1)
FROM d6_mardin.query_pendekatan_harga_jual qphj
WHERE qphj.id_proyek = p.id
)
ELSE
COALESCE(qrht.total_jumlah, 0) / 7266.0
END, 2) AS nilai_per_m2
FROM
d6_mardin.head_ref hr
LEFT JOIN
d6_mardin.query_rab_head_total qrht ON hr.id_rab = qrht.id_rab
LEFT JOIN
d6_mardin.proyek p ON p.id = COALESCE(qrht.id_proyek)
LEFT JOIN
d6_mardin.query_nilai_penerimaan_penjualan qnpp ON qnpp.id = p.id
WHERE
p.id = %d AND qrht.total_jumlah IS NOT NULL AND qrht.total_jumlah > 0
ORDER BY
hr.id_rab";
$this->GetConnection()->ExecQueryToArray(sprintf($sql1, $hppId), $hargaPokokProduksiDetail);
// Ubah key 'sub jumlah' menjadi 'sub_total' (opsional jika query tidak bisa diubah)
foreach ($hargaPokokProduksiDetail as &$itemHPP) {
if (isset($itemHPP['total_jumlah'])) {
$hppTotal = (int) $itemHPP['total_jumlah'];
$itemHPP['total_jumlah'] = number_format($hppTotal, 0, ',', '.');
$nilaiPerM2 = (int) $itemHPP['nilai_per_m2'];
$itemHPP['nilai_per_m2'] = number_format($nilaiPerM2, 0, ',', '.');
}
}
unset($itemHPP); // good practice
// Hitung total
$invoiceTotal = 0;
foreach ($hargaPokokProduksiDetail as $hppDetail) {
if (isset($hppDetail['sub_total'])) {
$hppTotal += $hppDetail['sub_total'];
}
}
// pengeluaran biaya manajemen dan operasional kantor
$biayaOperasionalDetail = array();
$bopId = GetApplication()->GetGETValue('pk0');
$sql2 = "
SELECT
p.nama_proyek,
p.lokasi,
hr.id_rab,
hr.nama_rab,
COALESCE(qbht.total_jumlah, 0) AS total_jumlah,
ROUND(
CASE
WHEN qnpp.nilai_penerimaan_penjualan IS NOT NULL AND qnpp.nilai_penerimaan_penjualan > 0
THEN (COALESCE(qbht.total_jumlah, 0) / qnpp.nilai_penerimaan_penjualan * 100)
ELSE 0
END, 2) AS persen
FROM
d6_mardin.head_ref hr
LEFT JOIN
d6_mardin.query_bop_head_total qbht ON hr.id_rab = qbht.id_rab
LEFT JOIN
d6_mardin.proyek p ON p.id = COALESCE(qbht.id_proyek)
LEFT JOIN
d6_mardin.query_nilai_penerimaan_penjualan qnpp ON qnpp.id = p.id
WHERE
p.id = %d and qbht.total_jumlah IS NOT NULL and qbht.total_jumlah > 0
ORDER BY
hr.id_rab";
$this->GetConnection()->ExecQueryToArray(sprintf($sql2, $bopId), $biayaOperasionalDetail);
// Ubah key 'sub jumlah' menjadi 'sub_total' (opsional jika query tidak bisa diubah)
foreach ($biayaOperasionalDetail as &$itemBop) {
if (isset($itemBop['total_jumlah'])) {
$bopTotal = (int) $itemBop['total_jumlah'];
$itemBop['total_jumlah'] = number_format($bopTotal, 0, ',', '.');
}
}
unset($itemBop); // good practice
// Hitung total
$invoiceTotal = 0;
foreach ($biayaOperasionalDetail as $bopDetail) {
if (isset($bopDetail['sub_total'])) {
$bopTotal += $bopDetail['sub_total'];
}
}
// laba proyek sebelum pajak dan biaya modal
$labaBeforeTaxDetail = array();
$labaBTId = GetApplication()->GetGETValue('pk0');
$sql3 = "
SELECT
(qnpp.nilai_penerimaan_penjualan - qnpp.harga_pokok_produksi - qnpp.biaya_operasional_kantor) as laba,
round( (qnpp.nilai_penerimaan_penjualan - qnpp.harga_pokok_produksi - qnpp.biaya_operasional_kantor)/ qnpp.nilai_penerimaan_penjualan * 100, 2) as persen
FROM
d6_mardin.query_nilai_penerimaan_penjualan qnpp
where
qnpp.id = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql3, $labaBTId), $labaBeforeTaxDetail);
foreach ($labaBeforeTaxDetail as &$itemLaba) {
if (isset($itemLaba['laba'])) {
$labaTotal = (int) $itemLaba['laba'];
$itemLaba['laba'] = number_format($labaTotal, 0, ',', '.');
}
}
unset($itemBop); // good practice
// hitung total persen rab
$rabPersen = array();
$rabPersenId = GetApplication()->GetGETValue('pk0');
$sql4 = "
SELECT
SUM(qrp.persen ) as persen
FROM
d6_mardin.query_rab_persen qrp
WHERE
qrp.id_proyek = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql4, $rabPersenId), $rabPersen);
// hitung total persen bop
$bopPersen = array();
$bopPersenId = GetApplication()->GetGETValue('pk0');
$sql5 = "
SELECT
SUM(qbp.persen ) as persen
FROM
d6_mardin.query_bop_persen qbp
WHERE
qbp.id_proyek = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql5, $bopPersenId), $bopPersen);
// hitung semua total persentase
$totalPersen = array();
$totalPersenId = GetApplication()->GetGETValue('pk0');
$sql6 = "
SELECT
(SELECT SUM(qbp.persen) FROM d6_mardin.query_bop_persen qbp ) +
(SELECT SUM(qrp.persen ) FROM d6_mardin.query_rab_persen qrp ) +
(SELECT ROUND(
(qnpp.nilai_penerimaan_penjualan - qnpp.harga_pokok_produksi - qnpp.biaya_operasional_kantor ) / qnpp.nilai_penerimaan_penjualan * 100, 2)
FROM d6_mardin.query_nilai_penerimaan_penjualan qnpp ) as persen";
$this->GetConnection()->ExecQueryToArray(sprintf($sql6, $totalPersenId), $totalPersen);
// hitung zakat 2.5%
$zakatDetail = array();
$zakatId = GetApplication()->GetGETValue('pk0');
$sql7 = "
SELECT ((
qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor )* 0.025) as zakat
FROM d6_mardin.query_nilai_penerimaan_penjualan qnpp
WHERE qnpp.id = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql7, $zakatId), $zakatDetail);
foreach ($zakatDetail as &$itemZakat) {
if (isset($itemZakat['zakat'])) {
$zakatTotal = (int) $itemZakat['zakat'];
$itemZakat['zakat'] = number_format($zakatTotal, 0, ',', '.');
}
}
unset($itemZakat); // good practice
// target laba bersih proyek
$labaBersih = array();
$labaBersihId = GetApplication()->GetGETValue('pk0');
$sql8 = "
SELECT
(qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor ) -
((
qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor )* 0.025)
as laba_bersih
FROM d6_mardin.query_nilai_penerimaan_penjualan qnpp
WHERE qnpp.id = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql8, $labaBersihId), $labaBersih);
foreach ($labaBersih as &$itemLabaBersih) {
if (isset($itemLabaBersih['laba_bersih'])) {
$labaBersihTotal = (int) $itemLabaBersih['laba_bersih'];
$itemLabaBersih['laba_bersih'] = number_format($labaBersihTotal, 0, ',', '.');
}
}
unset($itemLabaBersih); // good practice
// terhadap omset non ppn
$omsetNonPPN = array();
$omsetNonPPNId = GetApplication()->GetGETValue('pk0');
$sql9 = "
SELECT
round(((qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor ) -
((
qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor )* 0.025)) /
(qnpp.nilai_penerimaan_penjualan ) * 100, 2)
as omset_non_ppn
FROM d6_mardin.query_nilai_penerimaan_penjualan qnpp
WHERE qnpp.id = %d";
$this->GetConnection()->ExecQueryToArray(sprintf($sql9, $omsetNonPPNId), $omsetNonPPN);
// terhadap nilai pokok tanah
$nilaiPokokTanah = array();
$nilaiPokokTanahId = GetApplication()->GetGETValue('pk0');
$sql10 = "
SELECT
round ((SELECT
qrht.total_jumlah
FROM
d6_mardin.query_rab_head_total qrht
WHERE
qrht.id_proyek = %d AND qrht.id_rab = 'A') /
(
SELECT
(qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor ) -
((
qnpp.nilai_penerimaan_penjualan -
qnpp.harga_pokok_produksi -
qnpp.biaya_operasional_kantor )* 0.025)
as laba_bersih
FROM d6_mardin.query_nilai_penerimaan_penjualan qnpp
WHERE qnpp.id = %d
) * 100,2) as nilai_pokok_tanah";
$this->GetConnection()->ExecQueryToArray(sprintf($sql10, $nilaiPokokTanahId, $nilaiPokokTanahId), $nilaiPokokTanah);
//Debug
// echo '<pre>';
// print_r($hargaPokokProduksiDetail);
// print_r($biayaOperasionalDetail);
// print_r($labaBeforeTaxDetail);
// print_r($rabPersen);
// echo '</pre>';
// exit;
// Kirim data ke template
$params['HargaPokokProduksiDetail'] = $hargaPokokProduksiDetail;
$params['BiayaOperasionalDetail'] = $biayaOperasionalDetail;
$params['LabaBeforeTaxDetail'] = $labaBeforeTaxDetail;
$params['RabPersen'] = $rabPersen;
$params['BopPersen'] = $bopPersen;
$params['TotalPersen'] = $totalPersen;
$params['ZakatDetail'] = $zakatDetail;
$params['LabaBersih'] = $labaBersih;
$params['OmsetNonPPN'] = $omsetNonPPN;
$params['NilaiPokokTanah'] = $nilaiPokokTanah;
//$params['InvoiceNumber'] = 'RAB-' . sprintf('%03d', $invoiceId);
$params['InvoiceSubtotal'] = number_format($invoiceTotal, 0, ',', '.');
//$params['InvoiceTax'] = $invoiceTotal * 0.06; // pajak 6%
//$params['InvoiceTotal'] = $invoiceTotal + $params['InvoiceTax'];
// Tentukan template yang digunakan
if ($mode == PageMode::ExportPdf) {
$result = 'kesimpulan_proyek_pdf.tpl';
} else {
$result = 'kesimpulan_proyek_print.tpl';
}
}
if ($part == PagePart::PrintLayout && $mode == PageMode::PrintOneRecord) {
$result = 'kesimpulan_proyek_print_layout.tpl';
}
C:\xampp2\htdocs\d6\components\renderers\renderer.php