TAAS

SQL
Discus with Shanto and Junide

---- Based on Consider SALE -input
SELECT TO_CHAR('2023-12-31'::DATE, 'Month') AS str_month;
SELECT 'Sale' AS str_type,
    mofsm.sin_mod_of_sale_type,
    mofsm.fk_customer_ac_id,
    mofsm.fk_cash_counter_id,
    sm.fk_cost_centre_id,
    spd.fk_supplier_id,
    spd.fk_service_id,
    TO_CHAR(spd.dat_sale, 'Month') AS str_month, --Dynamically set spd.dat_issue or spd.dat_sale based on 
    TO_CHAR(spd.dat_sale, 'YYYY') AS str_year -- Dynamically set spd.dat_issue or spd.dat_sale based on 
    doc.fk_branch_id,
    SUM(spdf.dbl_supplier_amount) AS dbl_supplier_amount,
    SUM(spdf.dbl_customer_price) AS dbl_customer_price,
    SUM(spdf.dbl_normal_profit) AS dbl_normal_profit
FROM sale.tbl_mod_of_sale mofsm
    LEFT JOIN sale.tbl_sale_master sm
        ON mofsm.fk_sale_master_id = sm.pk_sale_master_id
    LEFT JOIN document.tbl_documents doc
        ON sm.fk_documents_id = doc.pk_documents_id
    LEFT JOIN sale.tbl_mod_of_sale_supp_doc mofsd
        ON mofsm.pk_mod_of_sale_id = mofsd.fk_mod_of_sale_id
    LEFT JOIN service.tbl_supp_doc_no spd
        ON mofsd.fk_supp_doc_no_id = spd.pk_supp_doc_no_id
    LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id
WHERE doc.int_sys_action_id != -1
GROUP BY  mofsm.sin_mod_of_sale_type,
    mofsm.fk_customer_ac_id,
    mofsm.fk_cash_counter_id,
    sm.fk_cost_centre_id,
    spd.fk_supplier_id,
    spd.fk_service_id,
    str_month,
    str_year,
    doc.fk_branch_id
ORDER BY str_month --or dat_issue based on input

UNION ALL -- Based on Consider REFUND - input

*REFUND...
*.....
*....
* WHERE sin_sys_refund_side_status = 4 and/or 5
--       4 = Void(From Refund GUI)
--       5 = Refund
* Refund date or Cancel date use

- Base1 Currency
LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_base_fare_id = spdf.pk_supp_doc_fare_id

- Other Currency Case
LEFT JOIN service.tbl_supp_doc_fare spdf 
        ON mofsd.fk_cus_ledg_fare_id = spdf.pk_supp_doc_fare_id

* Less refund amount from sales

1.*Grid Issue*
->Dynamic column with Grid grouping not working properly
Referred modules - Ledger-Comparison-Report and Currency-Comparison_report. [These module has dynamic column, but no Grid grouping, hence it is working fine. In Sales comparison need Dynamic Column + Grid grouping].
2. Print has to be done.
3. there is functions called fnSetFinancialYearsDefault() and fnSetFinancialYear() for setting default selected year it was working before but it's not working now
the function is called inside the onInit function and its not working, When I called it on inside of other functions its working.
4. Exclude -
where condition on query not given. Not using transaction table on this query.