Pembuatan API My Documents menggunakan bahasa Python (Flask/Django) yang terkoneksi dengan interface Android dan server Nextcloud:

1.     API dibuat menggunakan Python (Flask/Django) untuk melakukan proses-proses berikut ini:

A.     API Display My Documents

B.     API Display Need Your Review

C.    API Display High Priorities – Review

D.    API Display Draft List

E.     API Display Need Your Approval

F.     API Display High Priorities – Approval

G.    API Display Document List

H.    API Create Comments

I.       API Document Tracking

J.      API Document Report

Perhatikan! Jika terdapat baris coding pada bagian berikut ini hanyalah pseudocode atau hanya logika pemrograman dan belum tentu syntax yang cukup dicopy-paste, Anda harus memikirkan dan membuat syntax yang benar untuk menghasilkan program sesuai yang diinginkan.

A.  API Display My Documents

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display my documents”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data dari [table draft] yang belum pernah canceled atau approved. Kumpulkan [document number] yang [last status] not in (‘cancel’, ‘approved’) dan [create by] = p_login_user

·        Kenapa hanya [table draft] dan [table document] tidak diikut sertakan padahal isi [table draft] ada kemungkinan sudah dimaintain (dihapus)? Karena saat menghapus [table draft] syaratnya hanya dokumen yang [last status] sudah canceled atau approved

·        Count record group by [last status]

·        Needs revision: count [last status] = revision

·        Pending review: count [last status] = draft

·        Pending approval: count [last status] = document + count [last status] = on approval process

B.  API Display Need your review

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display need your review”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data dari [table draft] yang [last status] = ‘draft’ dan [to reviewer] = p_login_user

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] = ‘draft’ untuk mendapatkan [document type]

·        Count record group by [document type]

C. API Display High Priorities – Review

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display high priorities - review”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data dari [table draft] yang [last status] = ‘draft’ dan [to reviewer] = p_login_user dan Today >= [created date] + [lead_time_reviewed]

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] = ‘draft’ untuk mendapatkan [document type]

·        Count record group by [document type]

D. API Display Draft List

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display draft list”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data [document number], [document date], [created by] dari [table draft] yang [last status] = ‘draft’ dan [to reviewer] = p_login_user

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] = ‘draft’ untuk mendapatkan [document type]

·        Tampilkan sesuai order jika sort by yang dipilih Priority = Today >= [table draft].[created date] + [lead_time_reviewed]

·        Tampilkan sesuai order jika sort by yang dipilih Lead time = [table draft].[lead_time_reviewed]

·        Tampilkan sesuai order jika sort by yang dipilih Document date = [table approval history].[document date]

·        Tampilkan sesuai order jika sort by yang dipilih Document type = [table approval history].[document type]

·        Tampilkan sesuai order jika sort by yang dipilih Create by = [table draft].[created by]

·        Tampilkan sesuai order jika sort by yang dipilih Document number = [table draft].[document number]

E.  API Display Need Your Approval

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display need your approval”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data dari [table document] yang [last status] in (‘document’, ‘on approval process’)

·        Join dengan [table approver] on [to approver id] dan [approver] = p_login_user

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] in (‘document’, ‘on approval process’) untuk mendapatkan [document type]

·        Count record group by [document type]

F.  API Display High Priorities – Approval

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display high priorities - approval”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data dari [table document] yang [last status] in (‘document’, ‘on approval process’)

·        Join dengan [table approver] on [to approver id] dan [approver] = p_login_user dan Today >= [table document].[updated date] + [lead_time_approved]

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] in (‘document’, ‘on approval process’) untuk mendapatkan [document type]

·        Count record group by [document type]

G. API Display Document List

·        Sistem menerima parameter: p_login_user

·        Validasi tipe data dan isi parameter, jika tidak sesuai kembalikan error message “Format parameters is not valid – display document list”, transaksi tidak dapat dilanjutkan (return false)

·        Ambil data [document number], [document date], [created by] dari [table document] yang [last status] in (‘document’, ‘on approval process’)

·        Join dengan [table approver] on [to approver id] dan [approver] = p_login_user

·        Join dengan data distinct dari [table approval history] on [document_transaction_id] yang [document status] in (‘document’, ‘on approval process’) untuk mendapatkan [document type]

·        Tampilkan sesuai order jika sort by yang dipilih Priority = Today >= [table document].[updated date] + [lead_time_approved]

·        Tampilkan sesuai order jika sort by yang dipilih Lead time = [table approver].[lead_time_approved]

·        Tampilkan sesuai order jika sort by yang dipilih Document date = [table approval history].[document date]

·        Tampilkan sesuai order jika sort by yang dipilih Document type = [table approval history].[document type]

·        Tampilkan sesuai order jika sort by yang dipilih Create by = [table document].[created by]

·        Tampilkan sesuai order jika sort by yang dipilih Document number = [table document].[document number]

H. API Create Comments

·        parameters:

ü  action: “approve” / “revise” / “reject”

ü  user_type: “reviewer” / “approver”

ü  comments: user input

·        body:

1.     Jika action = approve dan user_type = reviewer:

o   Insert table comments

2.     Jika action = revise dan user_type = reviewer:

o   Insert table comments

3.     Jika action = reject dan user_type = reviewer:

o   Insert table comments

4.     Jika action = approve dan user_type = approver:

o   Insert table comments

5.     Jika action = revise dan user_type = approver:

o   Insert table comments

6.     Jika action = reject dan user_type = approver:

o   Insert table comments

7.     select current_sequence, max_sequence from table draft

8.     current_sequence +1

9.     if current_sequence +1 < max_sequence update table draft.current_sequence +1

10.  select current_sequence, max_sequence from table document

11.  current_sequence +1

12.  if current_sequence +1 < max_sequence update table document.current_sequence +1

I. API Document Tracking

·        Menampilkan informasi dokumen yang telah disubmit: jenis dokumen termasuk nomor dokumen, application id source darimana dokumen berasal, tanggal dokumen

·        Menampilkan status terakhir dokumen, misal pending review, need action berikutnya dari siapa (on approval process)

·        Menampilkan tanggal update terakhir & oleh siapa

·        Menampilkan tanggal submission document & oleh siapa

·        Menampilkan comments

·        Program hanya menampilkan dokumen-dokumen yang berhasil dikirim (submission) pada periode bulan berjalan (current month by create date in system) kecuali berdasarkan hasil pencarian sesuai value dari textbox Search yang dibuat oleh user pengguna (login user) dan/atau downline-nya saja. Tidak boleh melihat dokumen yang bukan wewenangnya. Kecuali group user “admin” dapat melihat keseluruhan transaksi

·        Parameters:

ü  Search Value: isi textbox search. Perhatikan! jika ingin mencari data berdasarkan tanggal tertentu maka isi Search Value harus dengan format yyyymmdd

ü  Sort By: “Document Type” / “Document Number” / “Submission Date” / “Over Lead Time” / “Newest” / “Oldest”

ü  User Id: login user

·        Body:

create temporary table temp_tdrafts as

select

   (select company_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Company`,

    (select application_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Application`,

    (select document_type from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Document Type`

    a.document_transaction_id as document_transaction_id,

    a.lead_time_reviewer as lead_time_reviewer, // à digunakan untuk order jika sort by over lead time

    case a.last_status

    when `draft` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `document` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `on approval process` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence > (select y.sequence from oc_tapprover y where y.document_transaction_id = a.document_transaction_id and y.approver_id = a.update_by) order by x.sequence limit 1)

    end as lead_time_approver, // à digunakan untuk order jika sort by over lead time

    a.document_number as `Document Number`,

    a.document_date as `Document Date`,

    a.last_status as `Last Status`,

    a.create_date as `Submission Date`,

    a.create_by as `Submission By`,

    a.update_date as `Last Update Date`,

    a.update_by as `Last Update By`,

    case a.last_status

    when `draft` then a.to_reviewer

    when `document` then (select x.approver_id from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `on approval process` then (select x.approver_id from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence > (select y.sequence from oc_tapprover y where y.document_transaction_id = a.document_transaction_id and y.approver_id = a.update_by) order by x.sequence limit 1)

    end as `Need Action From`,

    (select x.comments from oc_tcomments x join oc_thistories y on y.history_id = x.history_id where y.document_transcation_id = a.document_transaction_id and y.create_by = a.update_by order by x.create_date desc limit 1) as `Comments`,

from

    oc_tdrafts a

where

(

    (month(a.create_date) = month(today))

    or

    (

        ((select application_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) = @search_value and @search_value <> “”)

        or

        ((select document_type from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) = @search_value and @search_value <> “”)

        or

        (a.document_number = @search_value and @search_value <> “”)

        or

        (a.last_status = @search_value and @search_value <> “”)

        or

        (a.create_by = @search_value and @search_value <> “”)

        or

        (convert(a.create_date as varchar(10) format yyymmdd) = @search_value and @search_value <> “”)

    )

    or

    (

         (today >= [a.create date] + [a.lead_time_reviewed] and a.last_status = “draft” and @sort_by = “over lead time”)

        or

        (today >= (select max(x.[create date]) + x.[lead_time_approved] from oc_tapprover x where x.document_transaction_id = a.document_transaction_id) and a.last_status in (“document”, “on approval process”) and @sort_by = “over lead time”)

    )

)

create temporary table temp_tdocuments as

select

   (select company_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Company`,

    (select application_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Application`,

    (select document_type from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) as `Document Type`

    a.document_transaction_id as document_transaction_id,

    a.lead_time_reviewer as lead_time_reviewer, // à digunakan untuk order jika sort by over lead time

    case a.last_status

    when `draft` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `document` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `on approval process` then (select x.lead_time_approver from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence > (select y.sequence from oc_tapprover y where y.document_transaction_id = a.document_transaction_id and y.approver_id = a.update_by) order by x.sequence limit 1)

    end as lead_time_approver, // à digunakan untuk order jika sort by over lead time

    a.document_number as `Document Number`,

    a.document_date as `Document Date`,

    a.last_status as `Last Status`,

    a.create_date as `Submission Date`,

    a.create_by as `Submission By`,

    a.update_date as `Last Update Date`,

    a.update_by as `Last Update By`,

    case a.last_status

    when `draft` then a.to_reviewer

    when `document` then (select x.approver_id from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence = 1)

    when `on approval process` then (select x.approver_id from oc_tapprover x where x.document_transaction_id = a.document_transaction_id and x.sequence > (select y.sequence from oc_tapprover y where y.document_transaction_id = a.document_transaction_id and y.approver_id = a.update_by) order by x.sequence limit 1)

    end as `Need Action From`,

    (select x.comments from oc_tcomments x join oc_thistories y on y.history_id = x.history_id where y.document_transcation_id = a.document_transaction_id and y.create_by = a.update_by order by x.create_date desc limit 1) as `Comments`,

from

    oc_tdocuments a

where

(

    (month(a.create_date) = month(today))

    or

    (

        ((select application_id from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) = @search_value and @search_value <> “”)

        or

        ((select document_type from oc_thistories where document_transaction_id = a.document_transaction_id limit 1) = @search_value and @search_value <> “”)

        or

        (a.document_number = @search_value and @search_value <> “”)

        or

        (a.last_status = @search_value and @search_value <> “”)

        or

        (a.create_by = @search_value and @search_value <> “”)

        or

        (convert(a.create_date as varchar(10) format yyymmdd) = @search_value and @search_value <> “”)

    )

    or

    (

         (<