|
--ACCOUNT MAIN QUERY
|
|
|
|
SELECT ac.*, pd.* FROM accounts.tbl_account AS ac
|
|
LEFT JOIN accounts.tbl_personal_data AS pd
|
|
ON pd.fk_account_id = ac.pk_account_id
|
|
-- Join to Customer table
|
|
-- Join to Supplier Table
|
|
-- Join to
|
|
-- Join to Table for [ Payment term, Agent, Counter Staff]
|
|
WHERE ac.int_sys_action_id != -1
|
|
AND ac.pk_account_id = ?
|
|
|
|
-Ref: Account.cs -> FnGetQueryForSaleAndRefundAccountData();
|
|
-------------------------------------------------------------------------------
|
|
|
|
--CONTRACT
|
|
SELECT * FROM accounts.tbl_contract WHERE fk_account_id = ?
|
|
|
|
-------------------------------------------------------------------------
|
|
--SECURITY
|
|
SELECT * FROM accounts.tbl_security_documents WHERE fk_account_id = ?
|
|
|
|
-------------------------------------------------------------------------
|
|
-- LEDGER SUMMARY
|
|
|
|
SELECT fk_main_ledger_id, vhr_ledg_currency,
|
|
MIN(ca.vhr_code) AS vhr_main_ledger_code,
|
|
MIN(ca.vhr_name) AS vhr_main_ledger_name,
|
|
SUM(dbl_base_cur_debit) AS dbl_base_cur_current_debit,
|
|
SUM(dbl_base_cur_credit) AS dbl_base_cur_current_credit,
|
|
SUM(dbl_ledg_cur_debit) AS dbl_ledg_cur_current_debit,
|
|
SUM(dbl_ledg_cur_credit) AS dbl_ledg_cur_current_credit
|
|
FROM (SELECT fk_main_ledger_id,
|
|
vhr_ledg_currency,
|
|
dat_transaction,
|
|
dbl_base_cur_debit,
|
|
dbl_base_cur_credit,
|
|
dbl_ledg_cur_debit,
|
|
dbl_ledg_cur_credit
|
|
FROM transaction.tbl_transaction
|
|
WHERE fk_ledger_id = ? --acc id
|
|
AND sin_posting_status > 0 AND sin_document_status > 0 ) AS tmp
|
|
LEFT JOIN accounts.tbl_chart_of_ac AS ca
|
|
ON tmp.fk_main_ledger_id = ca.pk_chart_of_ac_id
|
|
GROUP BY fk_main_ledger_id, vhr_ledg_currency;
|
|
|
|
--Net Value => Dr - Cr
|
|
-- Grid Total using grid summaru block in html
|
|
-------------------------------------------------------------------------------------
|
|
|
|
--NOTE
|
|
SELECT * FROM accounts.tbl_account_notes WHERE int_sys_action_id != 1 AND fk_account_id = ?
|
|
|
|
-------------------------------------------------------------------------------------
|
|
--LINKED ACC/PAX/SUBLEDGER
|
|
|
|
SELECT 'Pax Profile' AS vhr_type,
|
|
p.vhr_pax_code,
|
|
p.vhr_pax_name,
|
|
pd.vhr_phone,
|
|
pd.vhr_email,
|
|
p.sin_record_status
|
|
FROM pax.tbl_pax_profile AS p
|
|
LEFT JOIN accounts.tbl_personal_data AS pd
|
|
ON pd.fk_pax_profile_id = p.pk_pax_profile_id
|
|
WHERE p.int_sys_action_id != -1
|
|
AND p.sin_sub_customer = 1
|
|
AND p.fk_main_account_id = ? -- Acc Id
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'Sub Customer' AS vhr_type,
|
|
p.vhr_pax_code,
|
|
p.vhr_pax_name,
|
|
pd.vhr_phone,
|
|
pd.vhr_email,
|
|
p.sin_record_status
|
|
FROM pax.tbl_pax_profile AS p
|
|
LEFT JOIN accounts.tbl_personal_data AS pd
|
|
ON pd.fk_pax_profile_id = p.pk_pax_profile_id
|
|
WHERE p.int_sys_action_id != -1
|
|
AND p.sin_sub_customer != 1
|
|
AND p.fk_main_account_id = ? -- Acc Id
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'Sub Ledger' AS vhr_type,
|
|
sl.vhr_sub_ledger_code,
|
|
sl.vhr_sub_ledger_name,
|
|
'' AS vhr_phone,
|
|
'' AS vhr_email,
|
|
sl.sin_record_status
|
|
FROM accounts.tbl_sub_ledger AS sl
|
|
WHERE sl.fk_main_account_id = ? -- Acc Id
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'Counter Staff' AS vhr_type,
|
|
cs.vhr_counter_staff_code,
|
|
cs.vhr_counter_staff_name,
|
|
cs.vhr_phone,
|
|
cs.vhr_email,
|
|
cs.sin_record_status
|
|
FROM counterstaff.tbl_counter_staff AS cs
|
|
WHERE ? IN (cs.fk_employee_id, cs.fk_commission_agent_ac_id) -- Acc Id
|
|
|
|
---------------------------------------------------------------------------------------
|
|
--DOCUMENT LIST
|
|
|
|
SELECT tmp.* FROM (
|
|
SELECT 'Sale' AS vhr_type,
|
|
vhr_sys_module_name,
|
|
vhr_document_no,
|
|
dat_document,
|
|
vhr_ledg_currency,
|
|
vhr_base_currency,
|
|
dbl_total_ledg_amount,
|
|
dbl_total_base_amount,
|
|
fk_branch_id,
|
|
vhr_reference,
|
|
txt_internal_note,
|
|
txt_remarks
|
|
FROM document.tbl_documents WHERE vhr_sys_module_name = 'SALE'
|
|
AND fk_ledger_id = ? --Acc ID
|
|
ORDER BY pk_documents_id DESC LIMIT 5
|
|
) as tmp
|
|
|
|
UNION ALL
|
|
|
|
SELECT tmp.* FROM (
|
|
SELECT 'Refund' AS vhr_type,
|
|
vhr_sys_module_name,
|
|
vhr_document_no,
|
|
dat_document,
|
|
vhr_ledg_currency,
|
|
vhr_base_currency,
|
|
dbl_total_ledg_amount,
|
|
dbl_total_base_amount,
|
|
fk_branch_id,
|
|
vhr_reference,
|
|
txt_internal_note,
|
|
txt_remarks
|
|
FROM document.tbl_documents WHERE vhr_sys_module_name = 'REFUND'
|
|
AND fk_ledger_id = ? --Acc ID
|
|
ORDER BY pk_documents_id DESC LIMIT 5
|
|
) as tmp
|
|
|
|
UNION ALL
|
|
|
|
SELECT tmp.* FROM (
|
|
SELECT 'Receipt' AS vhr_type,
|
|
vhr_sys_module_name,
|
|
vhr_document_no,
|
|
dat_document,
|
|
vhr_ledg_currency,
|
|
vhr_base_currency,
|
|
dbl_total_ledg_amount,
|
|
dbl_total_base_amount,
|
|
fk_branch_id,
|
|
vhr_reference,
|
|
txt_internal_note,
|
|
txt_remarks
|
|
FROM document.tbl_documents WHERE vhr_sys_module_name = 'RECEIPT'
|
|
AND fk_ledger_id = ? --Acc ID
|
|
ORDER BY pk_documents_id DESC LIMIT 5
|
|
) as tmp
|
|
|
|
UNION ALL
|
|
|
|
SELECT tmp.* FROM (
|
|
SELECT 'Payment' AS vhr_type,
|
|
vhr_sys_module_name,
|
|
vhr_document_no,
|
|
dat_document,
|
|
vhr_ledg_currency,
|
|
vhr_base_currency,
|
|
dbl_total_ledg_amount,
|
|
dbl_total_base_amount,
|
|
fk_branch_id,
|
|
vhr_reference,
|
|
txt_internal_note,
|
|
txt_remarks
|
|
FROM document.tbl_documents WHERE vhr_sys_module_name = 'PAYMENT'
|
|
AND fk_ledger_id = ? --Acc ID
|
|
ORDER BY pk_documents_id DESC LIMIT 5
|
|
) as tmp
|
|
|
|
UNION ALL
|
|
|
|
SELECT tmp.* FROM (
|
|
SELECT 'Other' AS vhr_type,
|
|
vhr_sys_module_name,
|
|
vhr_document_no,
|
|
dat_document,
|
|
vhr_ledg_currency,
|
|
vhr_base_currency,
|
|
dbl_total_ledg_amount,
|
|
dbl_total_base_amount,
|
|
fk_branch_id,
|
|
vhr_reference,
|
|
txt_internal_note,
|
|
txt_remarks
|
|
FROM document.tbl_documents WHERE vhr_sys_module_name NOT IN ('SALE', 'REFUND', 'RECEIPT', 'PAYMENT')
|
|
AND fk_ledger_id = ? --Acc ID
|
|
ORDER BY pk_documents_id DESC LIMIT 5
|
|
) as tmp
|
|
|
|
------------------------------------------------------------------------------------------
|
|
--ATTACHMENT
|
|
SELECT * FROM attachment.tbl_file_attachment WHERE int_sys_action_id != 1 AD fk_account_id = ? -- Acc ID
|
|
-----------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: 1. Replace * with name of required fields only
|
|
|
