Database

Data Preparation: Konversi Limesurvey Data dan Pertanyaan ke SQLite dengan Python Citric

Ini adalah skrip untuk mengambil data hasil survey dan pertanyaan dan mengkonversinya ke dalam sqlite dengan harapan data akan lebih mudah diolah kembali. SQLite sengaja saya pilih agar data bisa diolah langsung dengan SQL dan apabila perlu, hasil olahan bisa disimpan dalam view. Atau, jika menginginkan diolah menggunakan dataframe maka data yang ada dalam sqlite bisa di load dengan mudah.

Hasil dari skrip Python ini adalah

  1. Data pertanyaan yang lengkap berupa kode pertanyaan, pertanyaan, dan kode field dalam database. Lengkap.
  2. Data hasil survey dengan long answer dengan nama field kode pertanyaan. Tapi kamu bisa mengubahnya sendiri jika menginginkan hal yang berbeda.

Saya mengunakan Library Citric pada python dengan dokumentasi disini dan Chat GPT untuk merapikan skripnya.

Ok silahkan …

"""
Script created by Edy Santoso ([email protected])
Created on: 2024-08-02
"""

from citric import Client
import io
import pandas as pd
import sqlite3
import datetime
import re

def remove_tags(raw_html):
    """Remove HTML tags from a string"""
    cleantext = re.sub(re.compile('<.*?>'), '', raw_html)
    return cleantext

# Get the current time for unique database file naming
current_time = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
db_file = f"{current_time}_survey.sqlite3"
conn = sqlite3.connect(db_file)

# Replace with your survey ID
survey_id = 868964

# Initialize the LimeSurvey client
client = Client(
    'http://limesurvey6.test/index.php/admin/remotecontrol',
    'admin',
    'adminpassword'
)

# Get field map for the survey
fieldsmap = client.get_fieldmap(survey_id)

qs = []
for key, field in fieldsmap.items():
    code = ""
    
    # Build the code from title, aid, and scale_id
    if field['title'] != '':
        code += field['title']
    if field['aid'] != '':
        code += f"[{field['aid']}]"
    
    scale_id = field.get('scale_id')
    if scale_id is not None:
        code += f"[{scale_id}]"
    
    # Get subquestion if it exists
    subquestion = field.get('subquestion')
    if subquestion is not None:
        subquestion = f"[{subquestion}]"
    else:
        subquestion = ""

    # Append the field data to the list
    qs.append([
        field['gid'],
        field['qid'],
        field['fieldname'],
        field['type'],
        code, 
        field['group_name'], 
        remove_tags(field['question']),
        subquestion
    ])

# Create DataFrame from the list of field data
df = pd.DataFrame(qs, columns=['gid', 'qid', 'field_name', 'type', 'code', 'group_name', 'question', 'subquestion'])

print('wait...')

# Insert DataFrame into SQLite database
questions_table = 'questions'
df.to_sql(questions_table, conn, if_exists='replace', index=False)
print(f"{questions_table} table created")

# Export responses from LimeSurvey and load into DataFrame
responses_table = 'responses'
with io.BytesIO() as file:
    # heading_type (str | citric.enums.HeadingType) – Use response codes, long or abbreviated titles.
    # response_type (str | citric.enums.ResponseType) – Export long or short text responses.
    # completion_status (str | citric.enums.SurveyCompletionStatus) – Incomplete, complete or all.
    file.write(client.export_responses(survey_id, file_format="csv", heading_type='code', response_type='long'))
    file.seek(0)
    responses_df = pd.read_csv(
        file,
        delimiter=";",
        parse_dates=["datestamp", "startdate", "submitdate"],
        index_col="id",
    )

# Insert responses DataFrame into SQLite database
responses_df.to_sql(responses_table, conn, if_exists='replace', index=False)
print(f"{responses_table} table created")

print('Done!')

Berikut penjelasan dari data diatas:

  1. Import Libraries: Mengimpor pustaka yang diperlukan.
  2. Define remove_tags Function: Mendefinisikan fungsi untuk menghapus tag HTML dari string.
  3. Current Time for Unique DB File Name: Mendapatkan waktu saat ini untuk membuat nama file database yang unik. Ini dilakukan agar nama database yang terbentuk berawalan waktu, dan tidak menimpa hasil generasi sebelumnya.
  4. Database Connection: Membuat koneksi ke file SQLite baru.
  5. Survey ID: Menetapkan ID survei (dapat diganti dengan ID yang diperlukan).
  6. Initialize LimeSurvey Client: Menginisialisasi klien LimeSurvey dengan URL dan kredensial yang diperlukan. Atau dengan kata mudahnya, koneksi ke LimeSurvey API.
  7. Get Field Map: Mengambil “fields map”
  8. Build Question Data: Mengiterasi melalui “field map” untuk membangun data pertanyaan dan menambahkannya ke dalam daftar.
  9. Create DataFrame: Membuat DataFrame dari daftar data pertanyaan.
  10. Insert Questions DataFrame into SQLite: Memasukkan DataFrame pertanyaan ke dalam tabel SQLite.
  11. Export Responses and Load into DataFrame: Mengekspor respons dari LimeSurvey dan memuatnya ke dalam DataFrame.
  12. Insert Responses DataFrame into SQLite: Memasukkan DataFrame respons ke dalam tabel SQLite.
  13. Done: Mencetak pesan selesai.

Dengan format data seperti ini analisa data akan lebih mudah dilakukan,

Memahami Transaksi Barang

Saya mempunyai transaksi barang. Dengan alasan kemudahan maka barang keluar masuk saya tempatkan pada satu tabel saja yang saya namakan transaksi. Dengan satu tabel saya lebih mudah menjaga integritas data.

Semula saya akan membuat tiga tabel: transaksi_masuk, transaksi_keluar dan stock_opname. Namun setelah saya pikir, tarnsaksi_masuk dan keluar sepertinya lebih mudah dijadikan satu tabel saja. dan setelah saya pikir lebih matang lagi, transaksi_masuk, transaksi_keluar, dan stck_opname lebih baik dijadikan ke dalam satu tabel saja.

Ya, kalau mau berdebat pasti ada plus minusnya, namund alam kasus yang saya tangani, dimana transaksi tidak besar mungkin ini adalah desain terbaik.

Dengan bantuan OpenAI saya dapatkan hasil simulasi sbb:

Saya gunakan kode SQL untuk mysql dibawah 8 agar lebih kompatibel dengan semua versi MySQl. Untuk MySQL versi di bawah 8, Anda tidak dapat menggunakan window functions seperti SUM() OVER(), yang merupakan fitur yang diperkenalkan pada MySQL 8. Namun, Anda dapat mencapai hasil yang sama dengan menggunakan subquery atau join untuk menghitung stok awal dan stok akhir. Berikut adalah pendekatan alternatif menggunakan subquery:

Tentu, berikut adalah panduan untuk membuat tabel transaksi yang meliputi kategori barang masuk, barang keluar, stok opname tambah, dan stok opname kurang, beserta contoh data dan query SQL laporan untuk versi MySQL di bawah 8.

1. Buat Tabel Transaksi

Gunakan perintah berikut untuk membuat tabel transaksi:

CREATE TABLE transaksi (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Tanggal DATE,
    Kode_Produk VARCHAR(50),
    Nama_Produk VARCHAR(100),
    Kategori INT, -- 1 untuk barang masuk, 2 untuk barang keluar, 3 untuk opname tambah, 4 untuk opname kurang
    Jumlah INT
);

2. Isi Tabel Transaksi dengan Data Dummy

Masukkan data dummy ke dalam tabel transaksi menggunakan perintah INSERT INTO.

-- Barang masuk
INSERT INTO transaksi (Tanggal, Kode_Produk, Nama_Produk, Kategori, Jumlah)
VALUES 
('2024-07-01', 'PROD001', 'Produk A', 1, 100),
('2024-07-02', 'PROD002', 'Produk B', 1, 150),
('2024-07-03', 'PROD001', 'Produk A', 1, 120);

-- Barang keluar
INSERT INTO transaksi (Tanggal, Kode_Produk, Nama_Produk, Kategori, Jumlah)
VALUES 
('2024-07-01', 'PROD001', 'Produk A', 2, 50),
('2024-07-02', 'PROD002', 'Produk B', 2, 70),
('2024-07-03', 'PROD001', 'Produk A', 2, 30);

-- Opname tambah
INSERT INTO transaksi (Tanggal, Kode_Produk, Nama_Produk, Kategori, Jumlah)
VALUES 
('2024-07-01', 'PROD001', 'Produk A', 3, 10),
('2024-07-02', 'PROD002', 'Produk B', 3, 20),
('2024-07-03', 'PROD001', 'Produk A', 3, 15);

-- Opname kurang
INSERT INTO transaksi (Tanggal, Kode_Produk, Nama_Produk, Kategori, Jumlah)
VALUES 
('2024-07-01', 'PROD001', 'Produk A', 4, 5),
('2024-07-02', 'PROD002', 'Produk B', 4, 8),
('2024-07-03', 'PROD001', 'Produk A', 4, 3);

3. Buat Laporan Stok Barang

Gunakan query SQL berikut untuk membuat laporan stok barang:

SELECT
    Tanggal,
    Nama_Produk,
    SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) AS barang_masuk,
    SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) AS barang_keluar,
    SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END) AS opname_tambah,
    SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS opname_kurang,
    SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) 
      - SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END)
      + SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END)
      - SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS stok_akhir
FROM
    transaksi
GROUP BY
    Tanggal, Nama_Produk
ORDER BY
    Tanggal, Nama_Produk;

Penjelasan Query Laporan Stok Barang

  • SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) AS barang_masuk: Menghitung total barang masuk.
  • SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) AS barang_keluar: Menghitung total barang keluar.
  • SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END) AS opname_tambah: Menghitung total opname tambah.
  • SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS opname_kurang: Menghitung total opname kurang.
  • SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) - SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) + SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END) - SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS stok_akhir: Menghitung stok akhir berdasarkan barang masuk, barang keluar, opname tambah, dan opname kurang.

Laporan Harian Stok Barang

Dengan query ini, Anda bisa mendapatkan laporan stok barang yang mencakup barang masuk, barang keluar, opname tambah, opname kurang, dan stok akhir untuk setiap produk pada setiap tanggal.

Untuk mendapatkan laporan harian yang mencakup jumlah barang masuk, jumlah barang keluar, dan sisa barang, Anda bisa menggunakan query SQL berikut. Query ini akan mengelompokkan data berdasarkan tanggal dan menghitung total barang masuk, barang keluar, serta menghitung sisa barang untuk setiap tanggal.

SELECT
    Tanggal,
    SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) AS barang_masuk,
    SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) AS barang_keluar,
    SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) 
      - SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END)
      + SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END)
      - SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS sisa_barang
FROM
    transaksi
GROUP BY
    Tanggal
ORDER BY
    Tanggal;

Penjelasan Query Laporan Harian

  • SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) AS barang_masuk: Menghitung total barang masuk pada setiap tanggal.
  • SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) AS barang_keluar: Menghitung total barang keluar pada setiap tanggal.
  • SUM(CASE WHEN Kategori = 1 THEN Jumlah ELSE 0 END) - SUM(CASE WHEN Kategori = 2 THEN Jumlah ELSE 0 END) + SUM(CASE WHEN Kategori = 3 THEN Jumlah ELSE 0 END) - SUM(CASE WHEN Kategori = 4 THEN Jumlah ELSE 0 END) AS sisa_barang: Menghitung sisa barang berdasarkan total barang masuk, barang keluar, opname tambah, dan opname kurang.

Dengan query ini, Anda bisa mendapatkan laporan harian yang menunjukkan jumlah barang masuk, barang keluar, dan sisa barang untuk setiap tanggal.

SleekDB

SleekDB adalah sejenis Database NOSQL yang dibangun 100% dari PHP. Jadi, kamu bisa membuat aplikasi pure PHP “tanpa bantuan database apapun”.  SleekDB berbasis text dengan json format.

SleekDB hanya membutuhkan:

  • PHP >= 7.0
  • ext-json
  • ext-mbstring

Hebatnya SleekDB adalah, walaupun berbasis text dan hanya mengandalkan engine PHP, namun query yang digunakan sangat familier bagi programmer PHP, yaitu Query Builder.

Stuktur dokumennya kalau di MongoDB adalah Database -> Collection -> Document, kalau di SleekDB adalah Database->Store->Document.

Kelebihan SlackDB adalah simple dan mudah digunakan. Namun yang saya kurang suka adalah bahwa satu Document (row) diwakili satu file json.

Sebenarnya kalau ada developer madzab NOSQL dengan model seperti SleekDB namun teknologi penyimpanannya seperti SQLite (everywhere in single file) , maka dia akan menjadi SQLitenya kelompok database Non Relational.

MongoDB

Saya baru saja melakukan instalasi MongoDB 7.0.6, namun MongoDB saya install sebagai modul dari Laragon.

Caranya download yang versi Zip dan cukup di exstrak di bin/mongodb. Sedikit ada error saat saya start, dan bisa dibenerin dengan mengubah konfigurasi di mongod.conf. Namun saya lupa apa yg kemarin saya ubah.

Dan apabila kita membutuhkan MongDB Client maka kita bisa menggunakan MongoDBCompas

Berikut  beberapa catatan dari database NOSQL ini:

  1. Jika di Relasional Database ada istilah Database -> Table -> Rows
    maka di  MongoDB menggunakan istilah Database->Collection->Document
  2. Di MongoDB tidak diperlukan mendefiniskan row, bisa langsung memasukkan data apa saja.
  3. Jika di Relational database berbasis tabel maka di MongoDB berbasis JSON dan yang sejenis.
  4. Walaupun di MongoDB tidak diperlukan pembuatan column (rows), namun ada fasilitas untuk  memvalidasi key yang ada pada dokumen.
  5. Cara “Query” nya tidak menggunakan SQL ya bro, namun menggunakan perintah query dengan Java Script yang mirip-mirip json.
  6. Kalau menggunakan MongoDBCompas, bisa import langsung data berformat CSV ke dalam Collection.

Sebenarnya menurut saya cocok untuk LimeSurvey, sayangnya tidak ada dukungan di LimeSurvey.

BTW, saya belum akan menggunakannya untuk kebutuhan sehari-hari saya, dan ini masih sebatas mainan saja, karena memang belum ada urgensi menggunakan MongoDB saat ini. MongoDB besar instalernya aja 1/2 GB. Selain, kebutuhannya masih sebatas SQL khususnya MySQL dan SQLite 🙂

Contoh Query di MongoDB

// select * from products where tags in ("samsung",  "logitect")
db.products.find({
    tags: {
        $elemMatch: {
            $in: ["samsung", "logitech"]
        }
    }
});


// select * from products where category in ('handphone', 'laptop') and price > 5000000
db.products.find({
    category: {
        $in: ["handphone", "laptop"]
    },
    price: {
        $gt: 5000000
    }
});

Contoh perintah-perintah lainnya silahkan mengunjungi GitHubnya  Kang Eko .

HeidiSQL Sudah Mendukung SQLite

Dulu saya pengguna aktif phpMyAdmin untuk memanage database, namun semenjak saya menemukan HeidiSQL untuk memanage database mySQL, saya lebih sering menggunakan alat ini untuk mengelola database, walaupun database yang saya gunakan hanya MySQL – MariaDB.

Aplikasi ini simpel, praktis, robush. Enteng. No Debat 🙂

Namun di kemudian waktu, saya juga pengguna aktif SQLite sebagai database paling praktis dan simpel untuk aplikasi kecil. Kecil, cepat, gampang di copas, dan disimpan dalam satu file. Tanpa tanding.

Sebagai pengguna SQLite garis keras, saya mencari tahu apakah HeidiSQL mendukung SQLite? Walaupun waktu itu dipilihannya memang tidak ada. Saya selalu mengikuti update terbaru, kalau-kalau pilihan sqlite muncul.

Suatu saat saya menemukan jawaban developer atas pertanyaan apakah SQLite akan didukung HeidiSQL?

Semenjak saat itu saya sudah tidak berharap. Jarang update aplikasi, kalaupun update saya juga tidak pernah menengok apakah ada pilihan sqlite atau tidak.

Hari ini ada notifikasi untuk update database, dan iseng saya lihat pilihan database yang di support. Dan, ternyata SQLite ada disitu.

Surprise!

Ok, nampaknya HeidiSQL sudah mendukung SQLite sejak 4 tahun yang lalu hehehe. Saya tidak tahu. Dan walaupun saya install Heidi SQL tidak pernah melihat  daftar database yang disupport. Sejak HeidSQL 11.

Terima kasih banyak untuk Ansgar Becker (Germany) dan kawan-kawan yang telah membuat software hebat ini.