# -*- coding: utf-8 -*-
# [SKRIP I3.7] SQL dasar pada GeoPackage survei: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN, UPDATE.
# Penulis: Badar Mubarok Yogaswara
# Bekerja pada SALINAN data (UPDATE mengubah isi). Dijalankan memakai python-qgis.bat (modul osgeo.ogr).
# Bahasa SQL yang dipakai GeoPackage adalah SQL milik SQLite.
import os
import shutil
from osgeo import ogr

ogr.UseExceptions()
ASLI = r"D:/KPH_Contoh/paket-i3/Survei_Lapangan.gpkg"
SALINAN = r"D:/KPH_Contoh/Survei_Lapangan_salinan.gpkg"
os.makedirs(os.path.dirname(SALINAN), exist_ok=True)
shutil.copy(ASLI, SALINAN)
ds = ogr.Open(SALINAN, 1)


def jalankan(judul, sql):
    print("\n-- " + judul)
    print(sql)
    hasil = ds.ExecuteSQL(sql)
    if hasil is None:                              # perintah tanpa tabel hasil (UPDATE, DELETE)
        return
    nama = [hasil.GetLayerDefn().GetFieldDefn(i).GetName() for i in range(hasil.GetLayerDefn().GetFieldCount())]
    print(" | ".join(nama))
    for baris in hasil:
        print(" | ".join(str(baris.GetField(i)) for i in range(len(nama))))
    ds.ReleaseResultSet(hasil)


jalankan("1. Berapa baris? ", "SELECT COUNT(*) AS jumlah FROM Titik_Survei")
jalankan("2. Pilih kolom dan batasi baris", "SELECT ID_Survei, Kondisi, Tinggi_Phn FROM Titik_Survei LIMIT 3")
jalankan("3. Saring dengan WHERE", "SELECT ID_Survei, KPH, Tinggi_Phn FROM Titik_Survei WHERE Kondisi = 'Tebangan Liar' ORDER BY ID_Survei")
jalankan("4. Hitung per kondisi (GROUP BY)", "SELECT Kondisi, COUNT(*) AS jumlah FROM Titik_Survei GROUP BY Kondisi ORDER BY jumlah DESC")
jalankan("5. Rata-rata tinggi per KPH (hanya nilai wajar)",
         "SELECT KPH, COUNT(*) AS n, ROUND(AVG(Tinggi_Phn), 1) AS rata_tinggi FROM Titik_Survei "
         "WHERE Tinggi_Phn BETWEEN 0 AND 60 GROUP BY KPH ORDER BY KPH")
jalankan("6. Cari ID ganda (HAVING)", "SELECT ID_Survei, COUNT(*) AS n FROM Titik_Survei GROUP BY ID_Survei HAVING COUNT(*) > 1")
jalankan("7. Gabung dengan tabel acuan: Kondisi yang tidak ada di daftar baku (LEFT JOIN)",
         "SELECT t.ID_Survei, t.Kondisi FROM Titik_Survei t LEFT JOIN Ref_Kondisi r ON t.Kondisi = r.Kondisi "
         "WHERE r.Kondisi IS NULL ORDER BY t.ID_Survei")
jalankan("8. Gabung dengan batas KPH lewat nama (JOIN)",
         "SELECT b.NAMA_KPH, COUNT(*) AS jumlah_titik FROM Titik_Survei t JOIN Batas_KPH b ON t.KPH = b.NAMA_KPH "
         "GROUP BY b.NAMA_KPH ORDER BY b.NAMA_KPH")
jalankan("9. Perbaiki ejaan (UPDATE) pada salinan",
         "UPDATE Titik_Survei SET Kondisi = 'Sehat' WHERE LOWER(TRIM(Kondisi)) = 'sehat'")
jalankan("10. Periksa lagi setelah UPDATE",
         "SELECT t.ID_Survei, t.Kondisi FROM Titik_Survei t LEFT JOIN Ref_Kondisi r ON t.Kondisi = r.Kondisi "
         "WHERE r.Kondisi IS NULL ORDER BY t.ID_Survei")
ds = None