Search options (Page 1 of 6)

-- Baris Saldo Awal
SELECT 
  a.kode_akun AS Kode_Akun,
  a.nama_akun AS Nama_Akun,
  strftime('%d', date({DateTimePicker1}, '-1 day')) || ' ' ||
        CASE strftime('%m', date({DateTimePicker1}, '-1 day'))
            WHEN '01' THEN 'Jan'                                        
            WHEN '02' THEN 'Feb'
            WHEN '03' THEN 'Mar'         
            WHEN '04' THEN 'Apr'
            WHEN '05' THEN 'Mei'                
            WHEN '06' THEN 'Jun'
            WHEN '07' THEN 'Jul'
            WHEN '08' THEN 'Agt'
            WHEN '09' THEN 'Sep'
            WHEN '10' THEN 'Okt'
            WHEN '11' THEN 'Nov'
            WHEN '12' THEN 'Des'
        END || ' ' || strftime('%Y', date({DateTimePicker1}, '-1 day')) AS Tanggal,
  'Saldo Awal' AS Deskripsi,
  CAST(0 AS NUMERIC) AS Total_Debet,
  CAST(0 AS NUMERIC) AS Total_Kredit,

  IFNULL((
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah 
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2 
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND t2.tanggal < {DateTimePicker1}
  ), 0) AS Saldo_Berjalan,
  0 AS urutan_sorting

FROM akun a
WHERE a.id = {ComboBox1}

UNION ALL

-- Transaksi Normal
SELECT 
  a.kode_akun AS Kode_Akun,
  a.nama_akun AS Nama_Akun,
  strftime('%d', t.tanggal) || ' ' ||
        CASE strftime('%m', t.tanggal)
            WHEN '01' THEN 'Jan'
            WHEN '02' THEN 'Feb'
            WHEN '03' THEN 'Mar'         
            WHEN '04' THEN 'Apr'
            WHEN '05' THEN 'Mei'
            WHEN '06' THEN 'Jun'
            WHEN '07' THEN 'Jul'
            WHEN '08' THEN 'Agt'
            WHEN '09' THEN 'Sep'
            WHEN '10' THEN 'Okt'
            WHEN '11' THEN 'Nov'
            WHEN '12' THEN 'Des'
        END || ' ' || strftime('%Y', t.tanggal) AS Tanggal,
  t.deskripsi AS Deskripsi,
  SUM(CASE WHEN td.posisi = 'Debet' THEN td.jumlah ELSE 0 END) AS Total_Debet,
  SUM(CASE WHEN td.posisi = 'Kredit' THEN td.jumlah ELSE 0 END) AS Total_Kredit,
  (
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah                
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2                               
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND (t2.tanggal < t.tanggal OR (t2.tanggal = t.tanggal AND t2.id <= t.id))
  ) AS Saldo_Berjalan,                   
  t.tanggal AS urutan_sorting

FROM transaksi_detail td
JOIN transaksi t ON td.id_transaksi = t.id
JOIN akun a ON td.id_akun = a.id
WHERE a.id = {ComboBox1}
  AND t.tanggal BETWEEN {DateTimePicker1} AND {DateTimePicker2} 
  AND a.id_status_akun = 1  -- hanya akun aktif 
GROUP BY a.kode_akun, a.nama_akun, a.id, t.tanggal, t.deskripsi, t.id
                                           
ORDER BY urutan_sorting ASC;

  (
    SELECT SUM(CASE 
      WHEN td2.posisi = 'Debet' THEN td2.jumlah                
      ELSE -td2.jumlah 
    END)
    FROM transaksi_detail td2                               
    JOIN transaksi t2 ON td2.id_transaksi = t2.id
    WHERE td2.id_akun = a.id
      AND (t2.tanggal < t.tanggal OR (t2.tanggal = t.tanggal AND t2.id <= t.id))
  ) AS Saldo_Berjalan

SELECT distinct 
"$autoinc",                         
  TRIM(ifnull(employees.firstname||' ','')||ifnull(employees.lastname,'')) as Nama,                                 
  jabatan.jabatan as Jabatan,
  pangkat.pangkat as Pangkat,                        
  unitkerja.unitkerja as UnitKerja,                                                
  statuskeluarga.statuskeluarga as Status_keluarga,
  statuspegawai.statuspegawai as Status_pegawai,
  gajipokok as GajiPokok,                         
  tarip as Tarip,            
  irja as IRJA,                                                   
  Tkj as TKJ,                                                            
  pajak as Pajak,
  khspajak as KhsPajak,                  
  Keluarga as Tunj_Keluarga,   
  Sandangpangan as SandangPangan,  
  
  kreditbank,
  kreditkoperasi,
  bpjsKerja,
  bpjsKesehatan,
  pensiun, 
  pemakaianair,
  Koperasi,  
                                                                  
  a.harikerja as HariKerja,
  Hadir,
  Alpha,      
  sakit,                                                                               
  IjinCuti, 
  Terlambat,
  SanksiDisiplin,
  Lembur,
  uanglembur,
  transport,              
  perusahaan,
  bruto,
  GajiKotor,
  potongan,
  TerimaBersih
  
FROM (SELECT id_employees,                                                       
        harikerja, SUM(hadir) as Hadir, SUM(alpha) as Alpha, SUM(sakit) as sakit, SUM(ijincuti) as IjinCuti, 
        SUM(terlambat) as Terlambat, (SUM(sanksiapel) + SUM(sanksiterlambat)) as SanksiDisiplin, SUM(lembur) as Lembur, SUM(uanglembur) as UangLembur, 
        SUM(transport)as Transport, SUM(perusahaan)as Perusahaan,   
        ((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) - 
        (select pajak from employees where id = id_employees)) as bruto,   
        
        ((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) ) as GajiKotor,
        
        ((select kreditbank from employees where id = id_employees) + (select kreditkoperasi from employees where id = id_employees) + 
        (select bpjsKerja from employees where id = id_employees) + (select bpjsKesehatan from employees where id = id_employees) + 
        (select pensiun from employees where id = id_employees) + (select pemakaianair from employees where id = id_employees) + 
        (select Koperasi from employees where id = id_employees) + SUM(sanksiapel) + SUM(sanksiterlambat) ) as Potongan, 
        
        (((select gajipokok from employees where id = id_employees) + (select irja from employees where id = id_employees) + 
        (select tkj from employees where id = id_employees) + (select khspajak from employees where id = id_employees) + 
        (select khspajak from employees where id = id_employees) + (select keluarga from employees where id = id_employees) + 
        (select sandangpangan from employees where id = id_employees)+ SUM(uanglembur) + SUM(transport) + SUM(perusahaan) - 
        (select pajak from employees where id = id_employees)) -
        ((select kreditbank from employees where id = id_employees) + (select kreditkoperasi from employees where id = id_employees) + 
        (select bpjsKerja from employees where id = id_employees) + (select bpjsKesehatan from employees where id = id_employees) + 
        (select pensiun from employees where id = id_employees) + (select pemakaianair from employees where id = id_employees) + 
        (select Koperasi from employees where id = id_employees) + SUM(sanksiapel) + SUM(sanksiterlambat) ) ) as TerimaBersih
                         
                                                   
      FROM attendancedays          
      WHERE date(attendancedays.date) >= date({datetimepicker1})                                                                                                                
            AND date(attendancedays.date) <= date({datetimepicker2})             
      GROUP BY harikerja, id_employees) as a                       
      
      
   LEFT JOIN employees on a.id_employees = employees.id    
   LEFT JOIN unitkerja on employees.id_unitkerja = unitkerja.id
   LEFT JOIN jabatan on jabatan.id = employees.id_jabatan 
   left outer join statuspegawai on statuspegawai.id   =   employees.id_statuspegawai  
   left outer join statuskeluarga on statuskeluarga.id = employees.id_statuskeluarga  
   left outer join pangkat on pangkat.id   =   employees.id_pangkat  
   left outer join active on active.id   =   employees.id_active  
   
    WHERE employees.id_active  = 1  
        AND employees.id_statuspegawai = 2   
        
ORDER BY unitkerja.id asc