Project

General

Profile

Feature #216 » ACC-QUERY-SQL-SREERANJINIT.txt

Junaid M, 08/05/2024 01:08 PM

 
--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

(1-1/4)