from odoo import fields, models, api, _
from datetime import datetime, date, time, timedelta
from dateutil.relativedelta import relativedelta
import logging
import pytz
import psycopg2
from psycopg2 import extras
from odoo.exceptions import ValidationError

_logger = logging.getLogger(__name__)


class MergingMartData(models.Model):
    _name = "merging.svd.mart"
    _description = "Model Tampungan untuk SVD Mart Data"

    date = fields.Date(string="Date")
    # branch_id = fields.Many2one('res.branch',string="Branch")
    branch_code = fields.Char(string="Branch Code")
    # product_id = fields.Many2one('product.product',string="Product")
    product_code = fields.Char(string="Product Code")
    product_name = fields.Char(string="Product Name")
    product_category = fields.Char(string="Product Category")
    sub_category_name = fields.Char(string="Sub Category Name")
    avg_7 = fields.Float(string="Average 7")
    avg_14 = fields.Float(string="Average 14")
    avg_30 = fields.Float(string="Average 30")
    avg_60 = fields.Float(string="Average 60")
    avg_90 = fields.Float(string="Average 90")
    avg_180 = fields.Float(string="Average 180")
    # average_sales_price = fields.Float(string="Average Sales Price")
    total_sales_qty_7 = fields.Float(string="Total Sales Qty 7 Days")
    total_sales_qty_14 = fields.Float(string="Total Sales Qty 14 Days")
    total_sales_qty_30 = fields.Float(string="Total Sales Qty 30 Days")
    total_sales_qty_60 = fields.Float(string="Total Sales Qty 60 Days")
    total_sales_qty_90 = fields.Float(string="Total Sales Qty 90 Days")
    total_sales_qty_180 = fields.Float(string="Total Sales Qty 180 Days")
    days_stock_available_7 = fields.Float(string="Stock Available 7 Days")
    days_stock_available_14 = fields.Float(string="Stock Available 14 Days")
    days_stock_available_30 = fields.Float(string="Stock Available 30 Days")
    days_stock_available_60 = fields.Float(string="Stock Available 60 Days")
    days_stock_available_90 = fields.Float(string="Stock Available 90 Days")
    days_stock_available_180 = fields.Float(string="Stock Available 180 Days")
    type_data_svd = fields.Selection([
        ("abu", "DWH ABU"),
        ("pnj", "DWH PNJ"),
        ("abu_new", "ABU 2024")], string='Data SVD')


class SbbSnapshot(models.Model):
    _name = "sbb.snapshot"
    _description = "Daily SVD Spot"

    date = fields.Date(string="Date")
    # branch_id = fields.Many2one('res.branch',string="Branch")
    branch_code = fields.Char(string="Branch Code")
    # product_id = fields.Many2one('product.product',string="Product")
    product_code = fields.Char(string="Product Code")
    product_name = fields.Char(string="Product Name")
    product_category = fields.Char(string="Product Category")
    sub_category_name = fields.Char(string="Sub Category Name")
    avg_7 = fields.Float(string="Average 7")
    avg_14 = fields.Float(string="Average 14")
    avg_30 = fields.Float(string="Average 30")
    avg_60 = fields.Float(string="Average 60")
    avg_90 = fields.Float(string="Average 90")
    avg_180 = fields.Float(string="Average 180")
    # average_sales_price = fields.Float(string="Average Sales Price")
    total_sales_qty_7 = fields.Float(string="Total Sales Qty 7 Days")
    total_sales_qty_14 = fields.Float(string="Total Sales Qty 14 Days")
    total_sales_qty_30 = fields.Float(string="Total Sales Qty 30 Days")
    total_sales_qty_60 = fields.Float(string="Total Sales Qty 60 Days")
    total_sales_qty_90 = fields.Float(string="Total Sales Qty 90 Days")
    total_sales_qty_180 = fields.Float(string="Total Sales Qty 180 Days")
    days_stock_available_7 = fields.Float(string="Stock Available 7 Days")
    days_stock_available_14 = fields.Float(string="Stock Available 14 Days")
    days_stock_available_30 = fields.Float(string="Stock Available 30 Days")
    days_stock_available_60 = fields.Float(string="Stock Available 60 Days")
    days_stock_available_90 = fields.Float(string="Stock Available 90 Days")
    days_stock_available_180 = fields.Float(string="Stock Available 180 Days")
    type_data_svd = fields.Selection([
        ("abu", "DWH ABU"),
        ("pnj", "DWH PNJ"),
        ("abu_new", "ABU 2024")], string='Data SVD')

    def query_pnj(self):
        server = self.env['integration.external.db'].sudo().search([('server', '=', 'pnj_replication')], limit=1)
        if not server:
            raise ValidationError(
                "Server Database PNJ Belum di setting, silahkan di setting external DB nya terlebih dahulu!")

        done_process = 0
        if server:
            conn = server.connection_db()
            cur = conn.cursor()
            cur.execute(
                """
                    select a.product_code as product_code,a.product_name,a.branch_name as branch_name,a.branch_code as branch_code,a.category_name as category_name,a.sub_category_name as sub_category_name, a.product_id as product_id,
                    coalesce (max(sales7.qty),0) as qty_sales_7,
                    max(soh7.av_days) as av_days_7,
                    coalesce(max(sales7.qty),0) / coalesce(max(soh7.av_days),0) as svd_7,
                    coalesce(max(sales14.qty),0) as qty_sales_14,
                    max(soh14.av_days) as av_days_14,
                    coalesce(max(sales14.qty),0) / coalesce(max(soh14.av_days),0) as svd_14,
                    coalesce(max(sales30.qty),0) as qty_sales_30,
                    max(soh30.av_days) as av_days_30,
                    coalesce(max(sales30.qty),0) / coalesce(max(soh30.av_days),0) as svd_30,
                    coalesce(max(sales60.qty),0) as qty_sales_60,
                    max(soh60.av_days) as av_days_60,
                    coalesce(max(sales60.qty),0) / coalesce(max(soh60.av_days),0) as svd_60,
                    coalesce(max(sales90.qty),0) as qty_sales_90,
                    max(soh90.av_days) as av_days_90,
                    coalesce(max(sales90.qty),0) / coalesce(max(soh90.av_days),0) as svd_90,
                    coalesce(max(sales180.qty),0) as qty_sales_180,
                    max(soh180.av_days) as av_days_180,
                    coalesce(max(sales180.qty),0) / coalesce(max(soh180.av_days),0) as svd_180
                    from
                        (select
                            pp.id as product_id,
                            rb.id as branch_id,
                            pp.default_code as product_code,
                            pt1."name" as product_name,
                            rb.code as branch_code,
                            pc1."name" as category_name,
                            apc1."name" as sub_category_name,
                            rb."name" as branch_name
                        from product_product pp
                        cross join res_branch rb
                        left join product_template pt1 on pp.product_tmpl_id = pt1.id
                        left join product_category pc1 on pt1.categ_id = pc1.id
                        left join attribute_product_category apc1 on pt1.attribute_categ_id = apc1.id
                        where
                        pt1.sale_ok = true
                        ) a
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '7 days' AND now()
                        group by 1,2
                    ) sales7 on a.product_id = sales7.product_id and sales7.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '7 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh7 on a.product_code = soh7.product_code and a.branch_name = soh7.branch
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '14 days' AND now()
                        group by 1,2
                    ) sales14 on a.product_id = sales14.product_id and sales14.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '14 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh14 on a.product_code = soh14.product_code and a.branch_name = soh14.branch
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '30 days' AND now()
                        group by 1,2
                    ) sales30 on a.product_id = sales30.product_id and sales30.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '30 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh30 on a.product_code = soh30.product_code and a.branch_name = soh30.branch
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '60 days' AND now()
                        group by 1,2
                    ) sales60 on a.product_id = sales60.product_id and sales60.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '60 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh60 on a.product_code = soh60.product_code and a.branch_name = soh60.branch
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '90 days' AND now()
                        group by 1,2
                    ) sales90 on a.product_id = sales90.product_id and sales90.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '90 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh90 on a.product_code = soh90.product_code and a.branch_name = soh90.branch
                    left join (
                        select
                            ail2.product_id,
                            ai2.branch_id,
                            sum(ail2.quantity) as qty
                        from account_invoice_line ail2
                        left join account_invoice ai2 on ail2.invoice_id = ai2.id
                        where
                            ai2.state not in ('draft')
                            and ai2.type = 'out_invoice'
                            and ai2."date" BETWEEN current_date - interval '180 days' AND now()
                        group by 1,2
                    ) sales180 on a.product_id = sales180.product_id and sales180.branch_id = a.branch_id
                    left join (
                        select product_code,branch, count(product_code) as av_days from (
                            select
                                product_code,
                                branch,
                                date(date_soh) as date_soh,
                                sum(soh) ttl_qty
                            from
                                soh_snapshoot ss
                            where "date_soh" BETWEEN current_date - interval '180 days' AND now()
                            group by 1,2,3
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2
                    ) soh180 on a.product_code = soh180.product_code and a.branch_name = soh180.branch
                    group by 1,2,3,4,5,6,7
                    having
                        count(soh7.av_days) > 0
                        and count(soh14.av_days) > 0
                        and count(soh30.av_days) > 0
                        and count(soh60.av_days) > 0
                        and count(soh90.av_days) > 0
                        and count(soh180.av_days) > 0
                """
            )
            query_database_dwh_pnj = cur.fetchall()

            if query_database_dwh_pnj:
                for data_query_dwh_pnj in query_database_dwh_pnj:
                    # _logger.info("------------------------ DATA QUERY DWH PNJ %s"%query_database_dwh_pnj)

                    product_code_pnj = data_query_dwh_pnj[0]
                    product_name_pnj = data_query_dwh_pnj[1]
                    branch_name_pnj = data_query_dwh_pnj[2]
                    branch_code_pnj = data_query_dwh_pnj[3]
                    category_name_pnj = data_query_dwh_pnj[4]
                    sub_category_name_pnj = data_query_dwh_pnj[5]
                    product_id_pnj = data_query_dwh_pnj[6]
                    qty_sales_7_pnj = data_query_dwh_pnj[7]
                    av_days_7_pnj = data_query_dwh_pnj[8]
                    svd_7_pnj = data_query_dwh_pnj[9]
                    qty_sales_14_pnj = data_query_dwh_pnj[10]
                    av_days_14_pnj = data_query_dwh_pnj[11]
                    svd_14_pnj = data_query_dwh_pnj[12]
                    qty_sales_30_pnj = data_query_dwh_pnj[13]
                    av_days_30_pnj = data_query_dwh_pnj[14]
                    svd_30_pnj = data_query_dwh_pnj[15]
                    qty_sales_60_pnj = data_query_dwh_pnj[16]
                    av_days_60_pnj = data_query_dwh_pnj[17]
                    svd_60_pnj = data_query_dwh_pnj[18]
                    qty_sales_90_pnj = data_query_dwh_pnj[19]
                    av_days_90_pnj = data_query_dwh_pnj[20]
                    svd_90_pnj = data_query_dwh_pnj[21]
                    qty_sales_180_pnj = data_query_dwh_pnj[22]
                    av_days_180_pnj = data_query_dwh_pnj[23]
                    svd_180_pnj = data_query_dwh_pnj[24]

                    convert_branch_pnj_to_branch_local_abu = self.env['branch.code'].sudo().search(
                        [('code', '=', branch_code_pnj)])
                    for branch_local in convert_branch_pnj_to_branch_local_abu:
                        pnj_branch_local_abu = branch_local.branch_id.code

                    query_create_svd_from_dwh_pnj = """
                                            INSERT INTO sbb_snapshot
                                            ("product_code","product_name","product_category","sub_category_name","branch_code","avg_7","avg_14","avg_30","avg_60","avg_90","avg_180","total_sales_qty_7","total_sales_qty_14","total_sales_qty_30","total_sales_qty_60","total_sales_qty_90","total_sales_qty_180","days_stock_available_7","days_stock_available_14","days_stock_available_30","days_stock_available_60","days_stock_available_90","days_stock_available_180","date","type_data_svd")
                                            VALUES (%(product_code)s,%(product_name)s,%(product_category)s,%(sub_category_name)s,%(branch_code)s,%(avg_7)s,%(avg_14)s,%(avg_30)s,%(avg_60)s,%(avg_90)s,%(avg_180)s,%(total_sales_qty_7)s,%(total_sales_qty_14)s,%(total_sales_qty_30)s,%(total_sales_qty_60)s,%(total_sales_qty_90)s,%(total_sales_qty_180)s,%(days_stock_available_7)s,%(days_stock_available_14)s,%(days_stock_available_30)s,%(days_stock_available_60)s,%(days_stock_available_90)s,%(days_stock_available_180)s,current_date,'pnj')
                                        """

                    execute_create_svd_snapshot_pnj = self.env.cr.execute(query_create_svd_from_dwh_pnj, {
                        "product_code": product_code_pnj,
                        "product_name": product_name_pnj,
                        "product_category": category_name_pnj,
                        "sub_category_name": sub_category_name_pnj,
                        "branch_code": pnj_branch_local_abu,
                        "avg_7": svd_7_pnj,
                        "avg_14": svd_14_pnj,
                        "avg_30": svd_30_pnj,
                        "avg_60": svd_60_pnj,
                        "avg_90": svd_90_pnj,
                        "avg_180": svd_180_pnj,
                        "total_sales_qty_7": qty_sales_7_pnj,
                        "total_sales_qty_14": qty_sales_14_pnj,
                        "total_sales_qty_30": qty_sales_30_pnj,
                        "total_sales_qty_60": qty_sales_60_pnj,
                        "total_sales_qty_90": qty_sales_90_pnj,
                        "total_sales_qty_180": qty_sales_180_pnj,
                        "days_stock_available_7": av_days_7_pnj,
                        "days_stock_available_14": av_days_14_pnj,
                        "days_stock_available_30": av_days_30_pnj,
                        "days_stock_available_60": av_days_60_pnj,
                        "days_stock_available_90": av_days_90_pnj,
                        "days_stock_available_180": av_days_180_pnj,
                    })
                    # _logger.info("------------- DATA YANG DI CREATE PUNYA PNJ %s"%execute_create_svd_snapshot_pnj)

    def query_split_dwh_abu(self):
        svd_7_start_abu = []
        svd_7_end_abu = []
        svd_7_start_dwh = []
        svd_7_end_dwh = []
        svd_14_start_abu = []
        svd_14_end_abu = []
        svd_14_start_dwh = []
        svd_14_end_dwh = []
        svd_30_start_abu = []
        svd_30_end_abu = []
        svd_30_start_dwh = []
        svd_30_end_dwh = []
        svd_60_start_abu = []
        svd_60_end_abu = []
        svd_60_start_dwh = []
        svd_60_end_dwh = []
        svd_90_start_abu = []
        svd_90_end_abu = []
        svd_90_start_dwh = []
        svd_90_end_dwh = []
        svd_180_start_abu = []
        svd_180_end_abu = []
        svd_180_start_dwh = []
        svd_180_end_dwh = []
        today = date.today()

        pnj_branch_local_abu = []

        abu_lama_branch_local_abu = []

        tanggal_golive = self.env['ir.config_parameter'].sudo().get_param('golive_abu_date')
        tanggal_golive_abu = datetime.strptime(tanggal_golive, '%Y-%m-%d').date()

        tanggal_svd_7_dwh = tanggal_golive_abu - today

        validasi_tanggal_golive_7 = today - timedelta(days=7)  if (today - timedelta(days=7) ) > tanggal_golive_abu else tanggal_golive_abu
        validasi_tanggal_golive_14 = today - timedelta(days=14) if (today - timedelta(days=14)) > tanggal_golive_abu else tanggal_golive_abu
        validasi_tanggal_golive_30 = today - timedelta(days=30) if (today - timedelta(days=30)) > tanggal_golive_abu else tanggal_golive_abu
        validasi_tanggal_golive_60 = today - timedelta(days=60) if (today - timedelta(days=60)) > tanggal_golive_abu else tanggal_golive_abu
        validasi_tanggal_golive_90 = today - timedelta(days=90) if (today - timedelta(days=90)) > tanggal_golive_abu else tanggal_golive_abu
        validasi_tanggal_golive_180 = today - timedelta(days=180) if (today - timedelta(days=180)) > tanggal_golive_abu else tanggal_golive_abu

        # 7
        if validasi_tanggal_golive_7 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 7 YANG PERTAMA -----------------------")
            tanggal_svd_7_abu = (today - tanggal_golive_abu).days  # 22
            svd_7_start_abu = validasi_tanggal_golive_7
            svd_7_end_abu = today
            _logger.info("%s---------- start abu 7" % svd_7_start_abu)
            _logger.info("%s---------- end abu 7" % svd_7_end_abu)
            svd_7_start_dwh = svd_7_start_abu + timedelta(days=180)
            svd_7_end_dwh = svd_7_end_abu + timedelta(days=180)
            _logger.info("%s---------- start dwh 7" % svd_7_start_dwh)
            _logger.info("%s---------- end dwh7" % svd_7_end_dwh)
        else:
            _logger.info("-------------- KONDISI 7 YANG KEDUA -----------------------")
            tanggal_svd_7_abu = (today - tanggal_golive_abu).days
            svd_7_start_abu = validasi_tanggal_golive_7
            svd_7_end_abu = today
            _logger.info("%s---------- start abu 7" % svd_7_start_abu)
            _logger.info("%s---------- end abu 7" % svd_7_end_abu)
            svd_7_start_dwh = tanggal_golive_abu - timedelta(days=7 - tanggal_svd_7_abu)
            svd_7_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 7" % svd_7_start_dwh)
            _logger.info("%s---------- end dwh7" % svd_7_end_dwh)
        # 14
        if validasi_tanggal_golive_14 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 14 YANG PERTAMA -----------------------")
            tanggal_svd_14_abu = (today - tanggal_golive_abu).days  # 22
            svd_14_start_abu = validasi_tanggal_golive_14
            svd_14_end_abu = today
            _logger.info("%s---------- start abu 14" % svd_14_start_abu)
            _logger.info("%s---------- end abu 14" % svd_14_end_abu)
            svd_14_start_dwh = svd_14_start_abu + timedelta(days=180)
            svd_14_end_dwh = svd_14_end_abu + timedelta(days=180)
            _logger.info("%s---------- start dwh 14" % svd_14_start_dwh)
            _logger.info("%s---------- end dwh14" % svd_14_end_dwh)
        else:
            _logger.info("-------------- KONDISI 14 YANG KEDUA -----------------------")
            tanggal_svd_14_abu = (today - tanggal_golive_abu).days
            _logger.info("tanggal_svd_14_abu ------------ %s" % tanggal_svd_14_abu)
            svd_14_start_abu = validasi_tanggal_golive_14
            svd_14_end_abu = today
            _logger.info("%s---------- start abu 14" % svd_14_start_abu)
            _logger.info("%s---------- end abu 14" % svd_14_end_abu)
            svd_14_start_dwh = tanggal_golive_abu - timedelta(days=14 - tanggal_svd_14_abu)
            svd_14_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 14" % svd_14_start_dwh)
            _logger.info("%s---------- end dwh14" % svd_14_end_dwh)
        # 30
        if validasi_tanggal_golive_30 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 30 YANG PERTAMA -----------------------")
            tanggal_svd_30_abu = (today - tanggal_golive_abu).days  # 22
            svd_30_start_abu = validasi_tanggal_golive_30
            svd_30_end_abu = today
            _logger.info("%s---------- start abu 30" % svd_30_start_abu)
            _logger.info("%s---------- end abu 30" % svd_30_end_abu)
            svd_30_start_dwh = svd_30_start_abu + timedelta(days=180)
            svd_30_end_dwh = svd_30_end_abu + timedelta(days=180)
            _logger.info("%s---------- start dwh 30" % svd_30_start_dwh)
            _logger.info("%s---------- end dwh30" % svd_30_end_dwh)
        else:
            _logger.info("-------------- KONDISI 30 YANG KEDUA -----------------------")
            tanggal_svd_30_abu = (today - tanggal_golive_abu).days
            svd_30_start_abu = validasi_tanggal_golive_30
            svd_30_end_abu = today
            _logger.info("%s---------- start abu 30" % svd_30_start_abu)
            _logger.info("%s---------- end abu 30" % svd_30_end_abu)
            svd_30_start_dwh = tanggal_golive_abu - timedelta(days=30 - tanggal_svd_30_abu)
            svd_30_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 30" % svd_30_start_dwh)
            _logger.info("%s---------- end dwh30" % svd_30_end_dwh)
        # 60
        if validasi_tanggal_golive_60 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 60 YANG PERTAMA -----------------------")
            tanggal_svd_60_abu = (today - tanggal_golive_abu).days  # 22
            svd_60_start_abu = validasi_tanggal_golive_60
            svd_60_end_abu = today
            _logger.info("%s---------- start abu 60" % svd_60_start_abu)
            _logger.info("%s---------- end abu 60" % svd_60_end_abu)
            svd_60_start_dwh = svd_60_start_abu + timedelta(days=180)
            svd_60_end_dwh = svd_60_end_abu+ timedelta(days=180)
            _logger.info("%s---------- start dwh 60" % svd_60_start_dwh)
            _logger.info("%s---------- end dwh60" % svd_60_end_dwh)
        else:
            _logger.info("-------------- KONDISI 60 YANG KEDUA -----------------------")
            tanggal_svd_60_abu = (today - tanggal_golive_abu).days
            svd_60_start_abu = validasi_tanggal_golive_60
            svd_60_end_abu = today
            _logger.info("%s---------- start abu 60" % svd_60_start_abu)
            _logger.info("%s---------- end abu 60" % svd_60_end_abu)
            svd_60_start_dwh = tanggal_golive_abu - timedelta(days=60 - tanggal_svd_60_abu)
            svd_60_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 60" % svd_60_start_dwh)
            _logger.info("%s---------- end dwh60" % svd_60_end_dwh)
        # 90
        if validasi_tanggal_golive_90 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 90 YANG PERTAMA -----------------------")
            tanggal_svd_90_abu = (today - tanggal_golive_abu).days  # 22
            svd_90_start_abu = validasi_tanggal_golive_90
            svd_90_end_abu = today
            _logger.info("%s---------- start abu 90" % svd_90_start_abu)
            _logger.info("%s---------- end abu 90" % svd_90_end_abu)
            svd_90_start_dwh = svd_90_start_abu + timedelta(days=180)
            svd_90_end_dwh = svd_90_end_abu + timedelta(days=180)
            _logger.info("%s---------- start dwh 90" % svd_90_start_dwh)
            _logger.info("%s---------- end dwh90" % svd_90_end_dwh)
        else:
            _logger.info("-------------- KONDISI 90 YANG KEDUA -----------------------")
            tanggal_svd_90_abu = (today - tanggal_golive_abu).days
            svd_90_start_abu = validasi_tanggal_golive_90
            svd_90_end_abu = today
            _logger.info("%s---------- start abu 90" % svd_90_start_abu)
            _logger.info("%s---------- end abu 90" % svd_90_end_abu)
            svd_90_start_dwh = tanggal_golive_abu - timedelta(days=90 - tanggal_svd_90_abu)
            svd_90_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 90" % svd_90_start_dwh)
            _logger.info("%s---------- end dwh90" % svd_90_end_dwh)
        # 180
        if validasi_tanggal_golive_180 > tanggal_golive_abu:
            _logger.info("-------------- KONDISI 180 YANG PERTAMA -----------------------")
            tanggal_svd_180_abu = (today - tanggal_golive_abu).days  # 22
            svd_180_start_abu = validasi_tanggal_golive_180
            svd_180_end_abu = today
            _logger.info("%s---------- start abu 180" % svd_180_start_abu)
            _logger.info("%s---------- end abu 180" % svd_180_end_abu)
            svd_180_start_dwh = svd_180_start_abu + timedelta(days=180)
            svd_180_end_dwh = svd_180_end_abu + timedelta(days=180)
            _logger.info("%s---------- start dwh 180" % svd_180_start_dwh)
            _logger.info("%s---------- end dwh180" % svd_180_end_dwh)
        else:
            tanggal_svd_180_abu = (today - tanggal_golive_abu).days
            _logger.info("-------------- KONDISI 180 YANG KEDUA -----------------------")
            svd_180_start_abu = validasi_tanggal_golive_180
            svd_180_end_abu = today
            _logger.info("%s---------- start abu 180" % svd_180_start_abu)
            _logger.info("%s---------- end abu 180" % svd_180_end_abu)
            svd_180_start_dwh = tanggal_golive_abu - timedelta(days=180 - tanggal_svd_180_abu)
            svd_180_end_dwh = tanggal_golive_abu + timedelta(days=1)
            _logger.info("%s---------- start dwh 180" % svd_180_start_dwh)
            _logger.info("%s---------- end dwh180" % svd_180_end_dwh)

        server = self.env['integration.external.db'].sudo().search([('server', '=', 'DWH')], limit=1)

        done_process = 0
        if server:
            conn = server.connection_db()
            cur = conn.cursor()
            _logger.info("-------------------- MASUK KONDISI YANG ABU")
            conn = server.connection_db()
            cur = conn.cursor()
            sql = """

            """
            cur.execute(
                """
                    WITH abu_sales AS (
    SELECT
        '' AS MAINACCOUNTID,
        '' AS ACCOUNT_NAME,
        A."INVOICEID",
        date_part('year', cast(A."INVOICEDATE" as DATE)) AS INV_YEAR,
        date_part('month', cast(A."INVOICEDATE" as DATE)) AS INV_MONTH,
        A."SALESID",
        A."INVOICEACCOUNT",
        LOWER(A."INVOICINGNAME") AS INVOICINGNAME,
        cast(A."INVOICEDATE" as DATE) AS INVOICEDATE,
        A."INVENTLOCATIONID",
        B."ITEMID",
        B."QTY",
        B."SALESPRICE",
        B."LINEAMOUNTTAX",
        B."LINEAMOUNT",
        LOWER(B."NAME") AS PRODUCTNAME,
        F."INVENTSITEID",
        E."ITEMGROUPID",
        F."SEGMENTID",
        F."PAYMTERMID",
        LOWER(G."ABUITEMGROUPID") AS ABUITEMGROUPID,
        LOWER(H."DESCRIPTION") AS SEGMENT_NAME,
        B."LINENUM",
        A."PRINTMGMTSITEID" AS SITE,
        I."JOBTITLE" AS "Jobtitle ABU"
    FROM raw_axsub."CUSTINVOICEJOUR" AS A
    INNER JOIN raw_axsub."CUSTINVOICETRANS" AS B ON B."INVOICEID" = A."INVOICEID"
    INNER JOIN raw_axsub."INVENTITEMGROUPITEM" AS E ON E."ITEMID" = B."ITEMID"
    INNER JOIN raw_axsub."CUSTTABLE" AS F ON F."ACCOUNTNUM" = A."INVOICEACCOUNT"
    INNER JOIN raw_axsub."ABUITEMGROUP" AS G ON G."ITEMID" = B."ITEMID"
    INNER JOIN raw_axsub."SMMBUSRELSEGMENTGROUP" AS H ON H."SEGMENTID" = F."SEGMENTID"
    LEFT JOIN raw_axsub."SALESTABLE" AS I ON B."SALESID" = I."SALESID"
    WHERE F."SEGMENTID" <> 'SC23'
    AND A."INVOICEDATE" BETWEEN NOW() - INTERVAL '365 days' AND NOW()
),
aggregated_sales AS (
    SELECT
        "ITEMID",
        "INVENTSITEID",
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_7_start_dwh)s and %(svd_7_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_7,
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_14_start_dwh)s and %(svd_14_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_14,
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_30_start_dwh)s and %(svd_30_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_30,
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_60_start_dwh)s and %(svd_60_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_60,
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_90_start_dwh)s and %(svd_90_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_90,
        SUM(CASE WHEN INVOICEDATE BETWEEN %(svd_180_start_dwh)s and %(svd_180_end_dwh)s THEN "QTY" ELSE 0 END) AS qty_sales_180
    FROM abu_sales
    GROUP BY "ITEMID", "INVENTSITEID"
),
available_days AS (
    SELECT
        "ITEMID" AS product_code,
        "SITEID" AS branch_code,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_7_start_dwh)s and %(svd_7_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_7,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_14_start_dwh)s and %(svd_14_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_14,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_30_start_dwh)s and %(svd_30_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_30,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_60_start_dwh)s and %(svd_60_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_60,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_90_start_dwh)s and %(svd_90_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_90,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN %(svd_180_start_dwh)s and %(svd_180_end_dwh)s THEN "DATED" ELSE NULL END) AS ttl_avday_180
    FROM raw_dx."ABUSNAPSHOT"
    WHERE "QTY" > 20
    GROUP BY "ITEMID", "SITEID"
)
SELECT
    a."ITEMID",
    TRIM(a."SITEID") AS SITEID,
    '',
    '',
    '',
    COALESCE(max(sls.qty_sales_7), 0) AS qty_sales_7,
    COALESCE(max(avl.ttl_avday_7), 0) AS av_days_7,
    COALESCE(COALESCE(max(sls.qty_sales_7), 0) / NULLIF(COALESCE(max(avl.ttl_avday_7), 0),0),0) AS svd_7,
    COALESCE(max(sls.qty_sales_14), 0) AS qty_sales_14,
    COALESCE(max(avl.ttl_avday_14), 0) AS av_days_14,
    COALESCE(COALESCE(max(sls.qty_sales_14), 0) / NULLIF(COALESCE(max(avl.ttl_avday_14), 0),0),0) AS svd_14,
    COALESCE(max(sls.qty_sales_30), 0) AS qty_sales_30,
    COALESCE(max(avl.ttl_avday_30), 0) AS av_days_30,
    COALESCE(COALESCE(max(sls.qty_sales_30), 0) / NULLIF(COALESCE(max(avl.ttl_avday_30), 0),0),0) AS svd_30,
    COALESCE(max(sls.qty_sales_60), 0) AS qty_sales_60,
    COALESCE(max(avl.ttl_avday_60), 0) AS av_days_60,
    COALESCE(COALESCE(max(sls.qty_sales_60), 0) / NULLIF(COALESCE(max(avl.ttl_avday_60), 0),0),0) AS svd_60,
    COALESCE(max(sls.qty_sales_90), 0) AS qty_sales_90,
    COALESCE(max(avl.ttl_avday_90), 0) AS av_days_90,
    COALESCE(COALESCE(max(sls.qty_sales_90), 0) / NULLIF(COALESCE(max(avl.ttl_avday_90), 0),0),0) AS svd_90,
    COALESCE(max(sls.qty_sales_180), 0) AS qty_sales_180,
    COALESCE(max(avl.ttl_avday_180), 0) AS av_days_180,
    COALESCE(COALESCE(max(sls.qty_sales_180), 0) / NULLIF(COALESCE(max(avl.ttl_avday_180), 0),0),0) AS svd_180
FROM (
    SELECT
        b."ITEMID",
        b."SITEID"
    FROM raw_dx."ABUSNAPSHOT" b
    GROUP BY b."ITEMID", b."SITEID"
) a
LEFT JOIN aggregated_sales sls ON TRIM(sls."ITEMID") = TRIM(a."ITEMID") AND TRIM(sls."INVENTSITEID") = TRIM(a."SITEID")
LEFT JOIN available_days avl ON TRIM(avl.product_code) = TRIM(a."ITEMID") AND TRIM(avl.branch_code) = TRIM(a."SITEID")
GROUP BY a."ITEMID", a."SITEID"
HAVING
    COUNT(avl.ttl_avday_7) > 0
    AND COUNT(avl.ttl_avday_14) > 0
    AND COUNT(avl.ttl_avday_30) > 0
    AND COUNT(avl.ttl_avday_60) > 0
    AND COUNT(avl.ttl_avday_90) > 0
    AND COUNT(avl.ttl_avday_180) > 0;
                """, {
                    "svd_7_start_dwh": svd_7_start_dwh,
                    "svd_7_end_dwh": svd_7_end_dwh,
                    "svd_14_start_dwh": svd_14_start_dwh,
                    "svd_14_end_dwh": svd_14_end_dwh,
                    "svd_30_start_dwh": svd_30_start_dwh,
                    "svd_30_end_dwh": svd_30_end_dwh,
                    "svd_60_start_dwh": svd_60_start_dwh,
                    "svd_60_end_dwh": svd_60_end_dwh,
                    "svd_90_start_dwh": svd_90_start_dwh,
                    "svd_90_end_dwh": svd_90_end_dwh,
                    "svd_180_start_dwh": svd_180_start_dwh,
                    "svd_180_end_dwh": svd_180_end_dwh,

                }
            )
            query_database_dwh_abu = cur.fetchall()
            if query_database_dwh_abu:
                for data_query_dwh_abu in query_database_dwh_abu:
                    # _logger.info("%s ---------------- masuk kondisi kalo ada ngab -------------"%query_database_dwh_abu)
                    product_code = data_query_dwh_abu[0]
                    branch_code = data_query_dwh_abu[1]
                    product_name = data_query_dwh_abu[2]
                    product_category = data_query_dwh_abu[3]
                    sub_category_name = data_query_dwh_abu[4]
                    total_sales_qty_7 = data_query_dwh_abu[5]
                    days_stock_available_7 = data_query_dwh_abu[6]
                    svd_7 = data_query_dwh_abu[7]
                    total_sales_qty_14 = data_query_dwh_abu[8]
                    days_stock_available_14 = data_query_dwh_abu[9]
                    svd_14 = data_query_dwh_abu[10]
                    total_sales_qty_30 = data_query_dwh_abu[11]
                    days_stock_available_30 = data_query_dwh_abu[12]
                    svd_30 = data_query_dwh_abu[13]
                    total_sales_qty_60 = data_query_dwh_abu[14]
                    days_stock_available_60 = data_query_dwh_abu[15]
                    svd_60 = data_query_dwh_abu[16]
                    total_sales_qty_90 = data_query_dwh_abu[17]
                    days_stock_available_90 = data_query_dwh_abu[18]
                    svd_90 = data_query_dwh_abu[19]
                    total_sales_qty_180 = data_query_dwh_abu[20]
                    days_stock_available_180 = data_query_dwh_abu[21]
                    svd_180 = data_query_dwh_abu[22]

                    convert_branch_abu_lama_to_local_abu = self.env['branch.code'].sudo().search([('code','=',branch_code)])
                    for branch_abu_lama in convert_branch_abu_lama_to_local_abu:
                        abu_lama_branch_local_abu = branch_abu_lama.branch_id.code


                    query_create_svd_from_dwh_abu = """
                                                        INSERT INTO sbb_snapshot
                                                        ("product_code",
                                                        "product_name",
                                                        "product_category",
                                                        "sub_category_name",
                                                        "branch_code",
                                                        "avg_7",
                                                        "avg_14",
                                                        "avg_30",
                                                        "avg_60",
                                                        "avg_90",
                                                        "avg_180",
                                                        "total_sales_qty_7",
                                                        "total_sales_qty_14",
                                                        "total_sales_qty_30",
                                                        "total_sales_qty_60",
                                                        "total_sales_qty_90",
                                                        "total_sales_qty_180",
                                                        "days_stock_available_7",
                                                        "days_stock_available_14",
                                                        "days_stock_available_30",
                                                        "days_stock_available_60",
                                                        "days_stock_available_90",
                                                        "days_stock_available_180",
                                                        "date",
                                                        "type_data_svd")
                                                        VALUES (
                                                            %(product_code)s,
                                                            %(product_name)s,
                                                            %(product_category)s,
                                                            %(sub_category_name)s,
                                                            %(branch_code)s,
                                                            %(avg_7)s,
                                                            %(avg_14)s,
                                                            %(avg_30)s,
                                                            %(avg_60)s,
                                                            %(avg_90)s,
                                                            %(avg_180)s,
                                                            %(total_sales_qty_7)s,
                                                            %(total_sales_qty_14)s,
                                                            %(total_sales_qty_30)s,
                                                            %(total_sales_qty_60)s,
                                                            %(total_sales_qty_90)s,
                                                            %(total_sales_qty_180)s,
                                                            %(days_stock_available_7)s,
                                                            %(days_stock_available_14)s,
                                                            %(days_stock_available_30)s,
                                                            %(days_stock_available_60)s,
                                                            %(days_stock_available_90)s,
                                                            %(days_stock_available_180)s,
                                                            current_date,
                                                            'abu')
                                                    """
                    execute_create_svd_snapshot = self.env.cr.execute(query_create_svd_from_dwh_abu, {
                        "product_code": product_code,
                        "product_name": product_name,
                        "product_category": product_category,
                        "sub_category_name": sub_category_name,
                        "branch_code": abu_lama_branch_local_abu,
                        "avg_7": svd_7,
                        "avg_14": svd_14,
                        "avg_30": svd_30,
                        "avg_60": svd_60,
                        "avg_90": svd_90,
                        "avg_180": svd_180,
                        "total_sales_qty_7": total_sales_qty_7 if total_sales_qty_7 else 0,
                        "total_sales_qty_14": total_sales_qty_14  if total_sales_qty_7 else 0,
                        "total_sales_qty_30": total_sales_qty_30  if total_sales_qty_30 else 0,
                        "total_sales_qty_60": total_sales_qty_60  if total_sales_qty_60 else 0,
                        "total_sales_qty_90": total_sales_qty_90  if total_sales_qty_90 else 0,
                        "total_sales_qty_180": total_sales_qty_180  if total_sales_qty_180 else 0,
                        "days_stock_available_7": days_stock_available_7  if days_stock_available_7 else 0,
                        "days_stock_available_14": days_stock_available_14  if days_stock_available_14 else 0,
                        "days_stock_available_30": days_stock_available_30  if days_stock_available_30 else 0,
                        "days_stock_available_60": days_stock_available_60  if days_stock_available_60 else 0,
                        "days_stock_available_90": days_stock_available_90  if days_stock_available_90 else 0,
                        "days_stock_available_180": days_stock_available_180  if days_stock_available_180 else 0
                    })
                    self.env.cr.commit()
                    # _logger.info("----------- DATA SNAPSHOT YANG DI CREATE %s"%execute_create_svd_snapshot)
                    query = """
                    select a.product_id as product_id,
                    a.product_code as product_code,
                    a.product_name as product_name,
                    a.branch_id as branch_id,
                    a.branch_name as branch_name,
                    a.branch_code as branch_code,
                    a.category_name as category_name,
                    a.sub_category_name as sub_category_name,
                    coalesce(max(sales7.qty),0) as qty_sales_7,
                    max(soh7.av_days) av_days_7,
                    nullif(max(sales7.qty),0) / nullif(max(soh7.av_days),0) as svd_7,
                    coalesce(max(sales14.qty),0) as qty_sales_14,
                    max(soh14.av_days) av_days_14,
                    nullif(max(sales14.qty),0) / nullif(max(soh14.av_days),0) as svd_14,
                    coalesce(max(sales30.qty),0) as qty_sales_30,
                    max(soh30.av_days) av_days_30,
                    nullif(max(sales30.qty),0) / nullif(max(soh30.av_days),0) as svd_30,
                    coalesce(max(sales60.qty),0) as qty_sales_60,
                    max(soh60.av_days) av_days_60,
                    nullif(max(sales60.qty),0) / nullif(max(soh60.av_days),0) as svd_60,
                    coalesce(max(sales90.qty),0) as qty_sales_90,
                    max(soh90.av_days) av_days_90,
                    nullif(max(sales90.qty),0) / nullif(max(soh90.av_days),0) as svd_90,
                    coalesce(max(sales180.qty),0) as qty_sales_180,
                    max(soh180.av_days) av_days_180,
                    nullif(max(sales180.qty),0) / nullif(max(soh180.av_days),0) as svd_180
                    from (
                            select pp1.id as product_id,
                            pt1."name" as product_name,
                            pp1.default_code as product_code,
                            pc1."name" as category_name,
                            apc1."name" as sub_category_name,
                            rb1.id as branch_id,
                            rb1."name" as branch_name,
                            rb1.code as branch_code
                            from account_move_line aml1
                            left join account_move am1 on am1.id = aml1.move_id
                            left join account_account aa1 on aml1.account_id = aa1.id
                            left join product_product pp1 on aml1.product_id = pp1.id
                            left join product_template pt1 on pp1.product_tmpl_id = pt1.id
                            left join product_category pc1 on pt1.categ_id = pc1.id
                            left join attribute_product_category apc1 on pt1.attribute_categ_id = apc1.id
                            left join res_branch rb1 on aml1.branch_id = rb1.id
                            where pt1.item_type = 'commercial'
                            group by 1,2,3,4,5,6,7,8) a
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_7_start_abu)s AND %(svd_7_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales7 on a.product_id = sales7.product_id and sales7.branch_id = a.branch_id
                    left join (
                            select product_id,product_code, branch_id, count(product_id) as av_days from (
                                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_7_start_abu)s AND %(svd_7_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh7 on a.product_id = soh7.product_id and a.branch_id = soh7.branch_id
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_14_start_abu)s AND %(svd_14_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales14 on a.product_id = sales14.product_id and sales14.branch_id = a.branch_id
                    left join (
                            select product_id,product_code, branch_id, count(product_id) as av_days from (
                                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_14_start_abu)s AND %(svd_14_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh14 on a.product_id = soh14.product_id and a.branch_id = soh14.branch_id
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_30_start_abu)s AND %(svd_30_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales30 on a.product_id = sales30.product_id and sales30.branch_id = a.branch_id
                    left join (
                        select product_id,product_code, branch_id, count(product_id) as av_days from (
                            select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_30_start_abu)s AND %(svd_30_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh30 on a.product_id = soh30.product_id and a.branch_id = soh30.branch_id
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_60_start_abu)s AND %(svd_60_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales60 on a.product_id = sales60.product_id and sales60.branch_id = a.branch_id
                    left join (
                            select product_id,product_code, branch_id, count(product_id) as av_days from (
                                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_60_start_abu)s AND %(svd_60_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh60 on a.product_id = soh60.product_id and a.branch_id = soh60.branch_id
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_90_start_abu)s AND %(svd_90_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales90 on a.product_id = sales90.product_id and sales90.branch_id = a.branch_id
                    left join (
                            select product_id,product_code, branch_id, count(product_id) as av_days from (
                                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_90_start_abu)s AND %(svd_90_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh90 on a.product_id = soh90.product_id and a.branch_id = soh90.branch_id
                    left join (
                            select
                                aml2.product_id as product_id,
                                am2.branch_id as branch_id,
                                sum(aml2.quantity) as qty
                                from account_move_line aml2
                                left join account_move am2 on am2.id = aml2.move_id
                                left join account_account aa2 on aml2.account_id = aa2.id
                                left join res_branch rb2 on aml2.branch_id = rb2.id
                                where am2.move_type in ('out_invoice','out_refund')
                                and aa2.account_type = 'income'
                                and coalesce(am2.is_contract, false) != true
                                and am2.date BETWEEN %(svd_180_start_abu)s AND %(svd_180_end_abu)s
                                and aml2.partner_id not in (
                                    select
                                        partner_id
                                    from mapping_virtual_customer_branch mvcb
                                )
                                group by 1,2
                    ) sales180 on a.product_id = sales180.product_id and sales180.branch_id = a.branch_id
                    left join (
                            select product_id,product_code, branch_id, count(product_id) as av_days from (
                                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                                    from soh_snapshot ss
                                    left join product_product pp on ss.product = pp.default_code
                                    left join res_branch rb on ss.branch = rb.code
                                    where date between %(svd_180_start_abu)s AND %(svd_180_end_abu)s
                                    group by 1,2,3,4
                        ) raw_soh_7
                        where raw_soh_7.ttl_qty > 20
                        group by 1,2,3
                    ) soh180 on a.product_id = soh180.product_id and a.branch_id = soh180.branch_id
                    group by 1,2,3,4,5,6,7,8
                """
        self.env.cr.execute(query,{
                        "svd_7_start_abu": svd_7_start_abu,
                        "svd_7_end_abu": svd_7_end_abu,
                        "svd_14_start_abu": svd_14_start_abu,
                        "svd_14_end_abu": svd_14_end_abu,
                        "svd_30_start_abu": svd_30_start_abu,
                        "svd_30_end_abu": svd_30_end_abu,
                        "svd_60_start_abu": svd_60_start_abu,
                        "svd_60_end_abu": svd_60_end_abu,
                        "svd_90_start_abu": svd_90_start_abu,
                        "svd_90_end_abu": svd_90_end_abu,
                        "svd_180_start_abu": svd_180_start_abu,
                        "svd_180_end_abu": svd_180_end_abu,

                    })
        query_svd_mart_abu_2024 = self.env.cr.dictfetchall()
        if query_svd_mart_abu_2024:
            for data_query_abu in query_svd_mart_abu_2024:
                print(data_query_abu)
                _logger.info("%s-------------------- MART ABU 2024" % data_query_abu)
                merging_id = self.env['sbb.snapshot'].sudo().search([('product_code','=',data_query_abu['product_code']),('branch_code','=', data_query_abu['branch_code'])],limit=1)
                print('**************************',data_query_abu)
                product_id = data_query_abu['product_id']
                product_code = data_query_abu['product_code']
                product_name = data_query_abu['product_name']['en_US']
                branch_id = data_query_abu['branch_id']
                branch_name = data_query_abu['branch_name']
                branch_code = data_query_abu['branch_code']
                category_name = data_query_abu['category_name']
                sub_category_name = data_query_abu['sub_category_name']
                qty_sales_7 = data_query_abu['qty_sales_7']
                av_days_7 = data_query_abu['av_days_7']
                svd_7 = data_query_abu['svd_7']
                qty_sales_14 = data_query_abu['qty_sales_14']
                av_days_14 = data_query_abu['av_days_14']
                svd_14 = data_query_abu['svd_14']
                qty_sales_30 = data_query_abu['qty_sales_30']
                av_days_30 = data_query_abu['av_days_30']
                svd_30 = data_query_abu['svd_30']
                qty_sales_60 = data_query_abu['qty_sales_60']
                av_days_60 = data_query_abu['av_days_60']
                svd_60 = data_query_abu['svd_60']
                qty_sales_90 = data_query_abu['qty_sales_90']
                av_days_90 = data_query_abu['av_days_90']
                svd_90 = data_query_abu['svd_90']
                qty_sales_180 = data_query_abu['qty_sales_180']
                av_days_180 = data_query_abu['av_days_180']
                svd_180 = data_query_abu['svd_180']
                if not merging_id:
                    query_create_svd_from_abu = """
                                                        insert into sbb_snapshot
                                                        ("product_code","product_name","branch_code","avg_7","avg_14","avg_30","avg_60","avg_90","avg_180","total_sales_qty_7","total_sales_qty_14","total_sales_qty_30","total_sales_qty_60","total_sales_qty_90","total_sales_qty_180","days_stock_available_7","days_stock_available_14","days_stock_available_30","days_stock_available_60","days_stock_available_90","days_stock_available_180","date","type_data_svd")
                                                        VALUES (%(product_code)s,%(product_name)s,%(branch_code)s,%(avg_7)s,%(avg_14)s,%(avg_30)s,%(avg_60)s,%(avg_90)s,%(avg_180)s,%(total_sales_qty_7)s,%(total_sales_qty_14)s,%(total_sales_qty_30)s,%(total_sales_qty_60)s,%(total_sales_qty_90)s,%(total_sales_qty_180)s,%(days_stock_available_7)s,%(days_stock_available_14)s,%(days_stock_available_30)s,%(days_stock_available_60)s,%(days_stock_available_90)s,%(days_stock_available_180)s,current_date,'abu')
                                                    """
                    execute_create_svd_snapshot = self.env.cr.execute(query_create_svd_from_abu, {
                        "product_code": product_code,
                        "product_name": product_name,
                        "branch_code": branch_code,
                        "avg_7": svd_7,
                        "avg_14": svd_14,
                        "avg_30": svd_30,
                        "avg_60": svd_60,
                        "avg_90": svd_90,
                        "avg_180": svd_180,
                        "total_sales_qty_7": qty_sales_7,
                        "total_sales_qty_14": qty_sales_14,
                        "total_sales_qty_30": qty_sales_30,
                        "total_sales_qty_60": qty_sales_60,
                        "total_sales_qty_90": qty_sales_90,
                        "total_sales_qty_180": qty_sales_180,
                        "days_stock_available_7": av_days_7,
                        "days_stock_available_14": av_days_14,
                        "days_stock_available_30": av_days_30,
                        "days_stock_available_60": av_days_60,
                        "days_stock_available_90": av_days_90,
                        "days_stock_available_180": av_days_180,
                    })
                else:
                    days_stock_available_7 = (float(av_days_7) if av_days_7 else 0) + (float(merging_id.days_stock_available_7) if merging_id.days_stock_available_7 else 0)
                    if float(days_stock_available_7) > 7:
                        days_stock_available_7 = 7
                    days_stock_available_14 = (float(av_days_14) if av_days_14 else 0) + (float(merging_id.days_stock_available_14) if merging_id.days_stock_available_14 else 0)
                    if float(days_stock_available_14) > 14:
                        days_stock_available_14 = 14
                    days_stock_available_30 = (float(av_days_30) if av_days_30 else 0) + (float(merging_id.days_stock_available_30) if merging_id.days_stock_available_30 else 0)
                    if float(days_stock_available_30) > 30:
                        days_stock_available_30 = 30
                    days_stock_available_60 = (float(av_days_60) if av_days_60 else 0) + (float(merging_id.days_stock_available_60) if merging_id.days_stock_available_60 else 0)
                    if float(days_stock_available_60) > 60:
                        days_stock_available_60 = 60
                    days_stock_available_90 = (float(av_days_90) if av_days_90 else 0) + (float(merging_id.days_stock_available_90) if merging_id.days_stock_available_90 else 0)
                    if float(days_stock_available_90) > 90:
                        days_stock_available_90 = 90
                    days_stock_available_180 = (float(av_days_180) if av_days_180 else 0) + (float(merging_id.days_stock_available_180) if merging_id.days_stock_available_180 else 0)
                    if float(days_stock_available_180) > 180:
                        days_stock_available_180 = 180
                    merging_id.sudo().write({
                        "avg_7": (float(svd_7) if svd_7 else 0) + (float(merging_id.avg_7) if merging_id.avg_7 else 0),
                        "avg_14": (float(svd_14) if svd_14 else 0) + (
                            float(merging_id.avg_14) if merging_id.avg_14 else 0),
                        "avg_30": (float(svd_30) if svd_30 else 0) + (
                            float(merging_id.avg_30) if merging_id.avg_30 else 0),
                        "avg_60": (float(svd_60) if svd_60 else 0) + (
                            float(merging_id.avg_60) if merging_id.avg_60 else 0),
                        "avg_90": (float(svd_90) if svd_90 else 0) + (
                            float(merging_id.avg_90) if merging_id.avg_90 else 0),
                        "avg_180": (float(svd_180) if svd_180 else 0) + (
                            float(merging_id.avg_180) if merging_id.avg_180 else 0),
                        "total_sales_qty_7": (float(qty_sales_7) if qty_sales_7 else 0) + (
                            float(merging_id.total_sales_qty_7) if merging_id.total_sales_qty_7 else 0),
                        "total_sales_qty_14": (float(qty_sales_14) if qty_sales_14 else 0) + (
                            float(merging_id.total_sales_qty_14) if merging_id.total_sales_qty_14 else 0),
                        "total_sales_qty_30": (float(qty_sales_30) if qty_sales_30 else 0) + (
                            float(merging_id.total_sales_qty_30) if merging_id.total_sales_qty_30 else 0),
                        "total_sales_qty_60": (float(qty_sales_60) if qty_sales_60 else 0) + (
                            float(merging_id.total_sales_qty_60) if merging_id.total_sales_qty_60 else 0),
                        "total_sales_qty_90": (float(qty_sales_90) if qty_sales_90 else 0) + (
                            float(merging_id.total_sales_qty_90) if merging_id.total_sales_qty_90 else 0),
                        "total_sales_qty_180": (float(qty_sales_180) if qty_sales_180 else 0) + (
                            float(merging_id.total_sales_qty_180) if merging_id.total_sales_qty_180 else 0),
                        "days_stock_available_7": days_stock_available_7,
                        "days_stock_available_14": days_stock_available_14,
                        "days_stock_available_30": days_stock_available_30,
                        "days_stock_available_60": days_stock_available_60,
                        "days_stock_available_90": days_stock_available_90,
                        "days_stock_available_180": days_stock_available_180,
                    })
                    # query_create_svd_from_abu = """
                    #                                                         INSERT INTO sbb_snapshot
                    #                                                         ("product_code",
                    #                                                         "product_name",
                    #                                                         "branch_code",
                    #                                                         "avg_7",
                    #                                                         "avg_14",
                    #                                                         "avg_30",
                    #                                                         "avg_60",
                    #                                                         "avg_90",
                    #                                                         "avg_180",
                    #                                                         "total_sales_qty_7",
                    #                                                         "total_sales_qty_14",
                    #                                                         "total_sales_qty_30",
                    #                                                         "total_sales_qty_60",
                    #                                                         "total_sales_qty_90",
                    #                                                         "total_sales_qty_180",
                    #                                                         "days_stock_available_7",
                    #                                                         "days_stock_available_14",
                    #                                                         "days_stock_available_30",
                    #                                                         "days_stock_available_60",
                    #                                                         "days_stock_available_90",
                    #                                                         "days_stock_available_180",
                    #                                                         "date","type_data_svd")
                    #                                                         VALUES (%(product_code)s,%(product_name)s,%(branch_code)s,%(avg_7)s,%(avg_14)s,%(avg_30)s,%(avg_60)s,%(avg_90)s,%(avg_180)s,%(total_sales_qty_7)s,%(total_sales_qty_14)s,%(total_sales_qty_30)s,%(total_sales_qty_60)s,%(total_sales_qty_90)s,%(total_sales_qty_180)s,%(days_stock_available_7)s,%(days_stock_available_14)s,%(days_stock_available_30)s,%(days_stock_available_60)s,%(days_stock_available_90)s,%(days_stock_available_180)s,current_date,'abu')
                    #                                                     """
                    # execute_create_svd_snapshot = self.env.cr.execute(query_create_svd_from_abu, {
                    #     "product_code": product_code,
                    #     "product_name": product_name,
                    #     "branch_code": branch_code,
                    #     "avg_7": float(svd_7) if svd_7 else 0 + float(merging_id.avg_7) if merging_id.avg_7 else 0 ,
                    #     "avg_14": float(svd_14) if svd_14 else 0 + float(merging_id.avg_14) if merging_id.avg_14 else 0 ,
                    #     "avg_30": float(svd_30) if svd_30 else 0 + float(merging_id.avg_30) if merging_id.avg_30 else 0 ,
                    #     "avg_60": float(svd_60) if svd_60 else 0 + float(merging_id.avg_60) if merging_id.avg_60 else 0 ,
                    #     "avg_90": float(svd_90) if svd_90 else 0 + float(merging_id.avg_90) if merging_id.avg_90 else 0 ,
                    #     "avg_180": float(svd_180) if svd_180 else 0 + float(merging_id.avg_180) if merging_id.avg_180 else 0 ,
                    #     "total_sales_qty_7": float(qty_sales_7) if qty_sales_7 else 0 + float(merging_id.total_sales_qty_7) if merging_id.total_sales_qty_7 else 0 ,
                    #     "total_sales_qty_14": float(qty_sales_14) if qty_sales_14 else 0 + float(merging_id.total_sales_qty_14) if merging_id.total_sales_qty_14 else 0 ,
                    #     "total_sales_qty_30": float(qty_sales_30) if qty_sales_30 else 0 + float(merging_id.total_sales_qty_30) if merging_id.total_sales_qty_30 else 0 ,
                    #     "total_sales_qty_60": float(qty_sales_60) if qty_sales_60 else 0 + float(merging_id.total_sales_qty_60) if merging_id.total_sales_qty_60 else 0 ,
                    #     "total_sales_qty_90": float(qty_sales_90) if qty_sales_90 else 0 + float(merging_id.total_sales_qty_90) if merging_id.total_sales_qty_90 else 0 ,
                    #     "total_sales_qty_180": float(qty_sales_180) if qty_sales_180 else 0 + float(merging_id.total_sales_qty_180) if merging_id.total_sales_qty_180 else 0 ,
                    #     "days_stock_available_7": float(av_days_7) if av_days_7 else 0 + float(merging_id.total_sales_qty_7) if merging_id.total_sales_qty_7 else 0 ,
                    #     "days_stock_available_14": float(av_days_14) if av_days_14 else 0 + float(merging_id.total_sales_qty_14) if merging_id.total_sales_qty_14 else 0 ,
                    #     "days_stock_available_30": float(av_days_30) if av_days_30 else 0 + float(merging_id.total_sales_qty_30) if merging_id.total_sales_qty_30 else 0 ,
                    #     "days_stock_available_60": float(av_days_60) if av_days_60 else 0 + float(merging_id.total_sales_qty_60) if merging_id.total_sales_qty_60 else 0 ,
                    #     "days_stock_available_90": float(av_days_90) if av_days_90 else 0 + float(merging_id.total_sales_qty_90) if merging_id.total_sales_qty_90 else 0 ,
                    #     "days_stock_available_180": float(av_days_180) if av_days_180 else 0 + float(merging_id.total_sales_qty_180) if merging_id.total_sales_qty_180 else 0 ,
                    # })

    def query_before_golive_abu(self):
        server = self.env['integration.external.db'].sudo().search([('server', '=', 'DWH')], limit=1)
        today = date.today()
        tampungan_branch_local_abu_dari_abu_lama = []

        tanggal_golive = self.env['ir.config_parameter'].sudo().get_param('golive_abu_date')
        tanggal_golive_abu = datetime.strptime(tanggal_golive, '%Y-%m-%d').date()

        if not server:
            raise ValidationError(
                "Server Database DWH Belum di setting, silahkan di setting external DB nya terlebih dahulu!")

        if server:
            conn = server.connection_db()
            cur = conn.cursor()
            cur.execute(
                """
                    WITH abu_sales AS (
    SELECT
        '' AS MAINACCOUNTID,
        '' AS ACCOUNT_NAME,
        A."INVOICEID",
        date_part('year', cast(A."INVOICEDATE" as DATE)) AS INV_YEAR,
        date_part('month', cast(A."INVOICEDATE" as DATE)) AS INV_MONTH,
        A."SALESID",
        A."INVOICEACCOUNT",
        LOWER(A."INVOICINGNAME") AS INVOICINGNAME,
        cast(A."INVOICEDATE" as DATE) AS INVOICEDATE,
        A."INVENTLOCATIONID",
        B."ITEMID",
        B."QTY",
        B."SALESPRICE",
        B."LINEAMOUNTTAX",
        B."LINEAMOUNT",
        LOWER(B."NAME") AS PRODUCTNAME,
        F."INVENTSITEID",
        E."ITEMGROUPID",
        F."SEGMENTID",
        F."PAYMTERMID",
        LOWER(G."ABUITEMGROUPID") AS ABUITEMGROUPID,
        LOWER(H."DESCRIPTION") AS SEGMENT_NAME,
        B."LINENUM",
        A."PRINTMGMTSITEID" AS SITE,
        I."JOBTITLE" AS "Jobtitle ABU"
    FROM raw_axsub."CUSTINVOICEJOUR" AS A
    INNER JOIN raw_axsub."CUSTINVOICETRANS" AS B ON B."INVOICEID" = A."INVOICEID"
    INNER JOIN raw_axsub."INVENTITEMGROUPITEM" AS E ON E."ITEMID" = B."ITEMID"
    INNER JOIN raw_axsub."CUSTTABLE" AS F ON F."ACCOUNTNUM" = A."INVOICEACCOUNT"
    INNER JOIN raw_axsub."ABUITEMGROUP" AS G ON G."ITEMID" = B."ITEMID"
    INNER JOIN raw_axsub."SMMBUSRELSEGMENTGROUP" AS H ON H."SEGMENTID" = F."SEGMENTID"
    LEFT JOIN raw_axsub."SALESTABLE" AS I ON B."SALESID" = I."SALESID"
    WHERE F."SEGMENTID" <> 'SC23'
    AND A."INVOICEDATE" BETWEEN NOW() - INTERVAL '365 days' AND NOW()
),
aggregated_sales AS (
    SELECT
        "ITEMID",
        "INVENTSITEID",
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '7 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_7,
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '14 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_14,
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '30 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_30,
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '60 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_60,
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '90 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_90,
        SUM(CASE WHEN INVOICEDATE BETWEEN NOW() - INTERVAL '180 days' AND NOW() THEN "QTY" ELSE 0 END) AS qty_sales_180
    FROM abu_sales
    GROUP BY "ITEMID", "INVENTSITEID"
),
available_days AS (
    SELECT
        "ITEMID" AS product_code,
        "SITEID" AS branch_code,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '7 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_7,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '14 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_14,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '30 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_30,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '60 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_60,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '90 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_90,
        COUNT(DISTINCT CASE WHEN "DATED" BETWEEN NOW() - INTERVAL '180 days' AND NOW() THEN "DATED" ELSE NULL END) AS ttl_avday_180
    FROM raw_dx."ABUSNAPSHOT"
    WHERE "QTY" > 20
    GROUP BY "ITEMID", "SITEID"
)
SELECT
    a."ITEMID",
    TRIM(a."SITEID") AS SITEID,
    COALESCE(max(sls.qty_sales_7), 0) AS qty_sales_7,
    COALESCE(max(avl.ttl_avday_7), 0) AS av_days_7,
    COALESCE(COALESCE(max(sls.qty_sales_7), 0) / NULLIF(COALESCE(max(avl.ttl_avday_7), 0),0),0) AS svd_7,
    COALESCE(max(sls.qty_sales_14), 0) AS qty_sales_14,
    COALESCE(max(avl.ttl_avday_14), 0) AS av_days_14,
    COALESCE(COALESCE(max(sls.qty_sales_14), 0) / NULLIF(COALESCE(max(avl.ttl_avday_14), 0),0),0) AS svd_14,
    COALESCE(max(sls.qty_sales_30), 0) AS qty_sales_30,
    COALESCE(max(avl.ttl_avday_30), 0) AS av_days_30,
    COALESCE(COALESCE(max(sls.qty_sales_30), 0) / NULLIF(COALESCE(max(avl.ttl_avday_30), 0),0),0) AS svd_30,
    COALESCE(max(sls.qty_sales_60), 0) AS qty_sales_60,
    COALESCE(max(avl.ttl_avday_60), 0) AS av_days_60,
    COALESCE(COALESCE(max(sls.qty_sales_60), 0) / NULLIF(COALESCE(max(avl.ttl_avday_60), 0),0),0) AS svd_60,
    COALESCE(max(sls.qty_sales_90), 0) AS qty_sales_90,
    COALESCE(max(avl.ttl_avday_90), 0) AS av_days_90,
    COALESCE(COALESCE(max(sls.qty_sales_90), 0) / NULLIF(COALESCE(max(avl.ttl_avday_90), 0),0),0) AS svd_90,
    COALESCE(max(sls.qty_sales_180), 0) AS qty_sales_180,
    COALESCE(max(avl.ttl_avday_180), 0) AS av_days_180,
    COALESCE(COALESCE(max(sls.qty_sales_180), 0) / NULLIF(COALESCE(max(avl.ttl_avday_180), 0),0),0) AS svd_180
FROM (
    SELECT
        b."ITEMID",
        b."SITEID"
    FROM raw_dx."ABUSNAPSHOT" b
    GROUP BY b."ITEMID", b."SITEID"
) a
LEFT JOIN aggregated_sales sls ON TRIM(sls."ITEMID") = TRIM(a."ITEMID") AND TRIM(sls."INVENTSITEID") = TRIM(a."SITEID")
LEFT JOIN available_days avl ON TRIM(avl.product_code) = TRIM(a."ITEMID") AND TRIM(avl.branch_code) = TRIM(a."SITEID")
GROUP BY a."ITEMID", a."SITEID"
HAVING
    COUNT(avl.ttl_avday_7) > 0
    AND COUNT(avl.ttl_avday_14) > 0
    AND COUNT(avl.ttl_avday_30) > 0
    AND COUNT(avl.ttl_avday_60) > 0
    AND COUNT(avl.ttl_avday_90) > 0
    AND COUNT(avl.ttl_avday_180) > 0;


                """)
            query_database_dwh_abu = cur.fetchall()
            if query_database_dwh_abu:
                for data_query_dwh_abu in query_database_dwh_abu:
                    print(data_query_dwh_abu)
                    # _logger.info("%s ---------------- masuk kondisi kalo ada ngab -------------" %(data_query_dwh_abu))
                    product_code = data_query_dwh_abu[0]
                    branch_code = data_query_dwh_abu[1]
                    # get product template
                    product_id = self.env['product.product'].search([('default_code','=', product_code)],limit=1)
                    product_name = product_id.name
                    product_category = product_id.categ_id.name
                    sub_category_name = product_id.attribute_categ_id.name
                    total_sales_qty_7 = data_query_dwh_abu[2]
                    days_stock_available_7 = data_query_dwh_abu[3]
                    svd_7 = data_query_dwh_abu[4]
                    total_sales_qty_14 = data_query_dwh_abu[5]
                    days_stock_available_14 = data_query_dwh_abu[6]
                    svd_14 = data_query_dwh_abu[7]
                    total_sales_qty_30 = data_query_dwh_abu[8]
                    days_stock_available_30 = data_query_dwh_abu[9]
                    svd_30 = data_query_dwh_abu[10]
                    total_sales_qty_60 = data_query_dwh_abu[11]
                    days_stock_available_60 = data_query_dwh_abu[12]
                    svd_60 = data_query_dwh_abu[13]
                    total_sales_qty_90 = data_query_dwh_abu[14]
                    days_stock_available_90 = data_query_dwh_abu[15]
                    svd_90 = data_query_dwh_abu[16]
                    total_sales_qty_180 = data_query_dwh_abu[17]
                    days_stock_available_180 = data_query_dwh_abu[18]
                    svd_180 = data_query_dwh_abu[19]

                    convert_branch_abu_lama_to_local_abu = self.env['branch.code'].sudo().search([('code','=',branch_code)],limit=1)
                    # for branch_local_abu in convert_branch_abu_lama_to_local_abu:
                    #     tampungan_branch_local_abu_dari_abu_lama = branch_local_abu.branch_id.code
                    #     _logger.info("%s ----------------- tampungan_branch_local_abu_dari_abu_lama"%tampungan_branch_local_abu_dari_abu_lama)
                    if convert_branch_abu_lama_to_local_abu:
                        tampungan_branch_local_abu_dari_abu_lama = convert_branch_abu_lama_to_local_abu.branch_id.code
                    else:
                        tampungan_branch_local_abu_dari_abu_lama = branch_code

                    query_create_svd_from_dwh_abu = """
                                                        INSERT INTO sbb_snapshot
                                                        ("product_code","product_name","product_category","sub_category_name","branch_code","avg_7","avg_14","avg_30","avg_60","avg_90","avg_180","total_sales_qty_7","total_sales_qty_14","total_sales_qty_30","total_sales_qty_60","total_sales_qty_90","total_sales_qty_180","days_stock_available_7","days_stock_available_14","days_stock_available_30","days_stock_available_60","days_stock_available_90","days_stock_available_180","date","type_data_svd")
                                                        VALUES (%(product_code)s,%(product_name)s,%(product_category)s,%(sub_category_name)s,%(branch_code)s,%(avg_7)s,%(avg_14)s,%(avg_30)s,%(avg_60)s,%(avg_90)s,%(avg_180)s,%(total_sales_qty_7)s,%(total_sales_qty_14)s,%(total_sales_qty_30)s,%(total_sales_qty_60)s,%(total_sales_qty_90)s,%(total_sales_qty_180)s,%(days_stock_available_7)s,%(days_stock_available_14)s,%(days_stock_available_30)s,%(days_stock_available_60)s,%(days_stock_available_90)s,%(days_stock_available_180)s,current_date,'abu')
                                                    """
                    execute_create_svd_snapshot = self.env.cr.execute(query_create_svd_from_dwh_abu, {
                        "product_code": product_code,
                        "product_name": product_name,
                        "product_category": product_category,
                        "sub_category_name": sub_category_name,
                        "branch_code": tampungan_branch_local_abu_dari_abu_lama,
                        "avg_7": svd_7,
                        "avg_14": svd_14,
                        "avg_30": svd_30,
                        "avg_60": svd_60,
                        "avg_90": svd_90,
                        "avg_180": svd_180,
                        "total_sales_qty_7": total_sales_qty_7,
                        "total_sales_qty_14": total_sales_qty_14,
                        "total_sales_qty_30": total_sales_qty_30,
                        "total_sales_qty_60": total_sales_qty_60,
                        "total_sales_qty_90": total_sales_qty_90,
                        "total_sales_qty_180": total_sales_qty_180,
                        "days_stock_available_7": days_stock_available_7,
                        "days_stock_available_14": days_stock_available_14,
                        "days_stock_available_30": days_stock_available_30,
                        "days_stock_available_60": days_stock_available_60,
                        "days_stock_available_90": days_stock_available_90,
                        "days_stock_available_180": days_stock_available_180,

                    })
                    _logger.info("----------- DATA SNAPSHOT YANG DI CREATE %s" % execute_create_svd_snapshot)

    def query_after_golive_abu(self):

        today = date.today()

        minimum_soh = self.env['ir.config_parameter'].sudo().get_param('minimum_soh')
        minimum_soh = float(minimum_soh)
        today = date.today()
        one_week = today - timedelta(days=7)
        two_week = today - timedelta(weeks=2)
        one_month = today - timedelta(days=30)  # Approxi­mate number of days in one jmonth
        two_month = today - timedelta(days=60)  # Approxi­mate number of days in two months
        three_month = today - timedelta(days=90)  # Approxi­mate number of days in three months
        six_month = today - timedelta(days=180)

        _logger.info("-----------------------FUNCTION AFTER GOLIVE DIPANGGIL!")
        query = """
                    select
    product_branch.product_id as product_id,
    product_branch.product_code as product_code,
    (product_branch.product_name->>$$en_US$$)::text as product_name,
    product_branch.branch_id as branch_id,
    product_branch.branch_name as branch_name,
    product_branch.branch_code as branch_code,
    product_branch.category_name as category_name,
    product_branch.sub_category_name as sub_category_name,
    coalesce(max(sales7.qty),0) as qty_sales_7,
    coalesce(max(soh7.av_days),0) av_days_7,
    coalesce(nullif(max(sales7.qty),0) / nullif(max(soh7.av_days),0),0) as svd_7,
    coalesce(max(sales14.qty),0) as qty_sales_14,
    coalesce(max(soh14.av_days),0) av_days_14,
    coalesce(nullif(max(sales14.qty),0) / nullif(max(soh14.av_days),0),0) as svd_14,
    coalesce(max(sales30.qty),0) as qty_sales_30,
    coalesce(max(soh30.av_days),0) av_days_30,
    coalesce(nullif(max(sales30.qty),0) / nullif(max(soh30.av_days),0),0) as svd_30,
    coalesce(max(sales60.qty),0) as qty_sales_60,
    coalesce(max(soh60.av_days),0) av_days_60,
    coalesce(nullif(max(sales60.qty),0) / nullif(max(soh60.av_days),0),0) as svd_60,
    coalesce(max(sales90.qty),0) as qty_sales_90,
    coalesce(max(soh90.av_days),0) av_days_90,
    coalesce(nullif(max(sales90.qty),0) / nullif(max(soh90.av_days),0),0) as svd_90,
    coalesce(max(sales180.qty),0) as qty_sales_180,
    coalesce(max(soh180.av_days),0) av_days_180,
    coalesce(nullif(max(sales180.qty),0) / nullif(max(soh180.av_days),0),0) as svd_180
    from (
        select
            pp.id as product_id,
            rb.id as branch_id,
            pp.default_code as product_code,
            pt1."name" as product_name,
            rb.code as branch_code,
            pc1."name" as category_name,
            apc1."name" as sub_category_name,
            rb."name" as branch_name
        from product_product pp
        cross join res_branch rb
        left join product_template pt1 on pp.product_tmpl_id = pt1.id
        left join product_category pc1 on pt1.categ_id = pc1.id
        left join attribute_product_category apc1 on pt1.attribute_categ_id = apc1.id
        where
            pt.item_type = 'commercial' and
            rb.virtual_branch <> true and
            rb.code <> 'HOF'
    ) product_branch
    left join (
            select pp1.id as product_id,
            pt1."name" as product_name,
            pp1.default_code as product_code,
            pc1."name" as category_name,
            apc1."name" as sub_category_name,
            rb1.id as branch_id,
            rb1."name" as branch_name,
            rb1.code as branch_code
            from account_move_line aml1
            left join account_move am1 on am1.id = aml1.move_id
            left join account_account aa1 on aml1.account_id = aa1.id
            left join product_product pp1 on aml1.product_id = pp1.id
            left join product_template pt1 on pp1.product_tmpl_id = pt1.id
            left join product_category pc1 on pt1.categ_id = pc1.id
            left join attribute_product_category apc1 on pt1.attribute_categ_id = apc1.id
            left join res_branch rb1 on aml1.branch_id = rb1.id
            where pt1.item_type = 'commercial'
                and am1.is_contract != true
            group by 1,2,3,4,5,6,7,8
    ) a on product_branch.product_id = a.product_id and product_branch.branch_id = a.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '7 days' AND now()
                and am2.is_contract != true
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales7 on product_branch.product_id = sales7.product_id and sales7.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                where date between now() - interval '7 days' and now()
                group by 1,2,3,4
        ) raw_soh_7
        where raw_soh_7.ttl_qty > 20
        group by 1,2,3
    ) soh7 on product_branch.product_id = soh7.product_id and product_branch.branch_id = soh7.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '14 days' AND now()
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales14 on product_branch.product_id = sales14.product_id and sales14.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                where date between now() - interval '14 days' and now()
                group by 1,2,3,4
        ) raw_soh_14
        where raw_soh_14.ttl_qty > 20
        group by 1,2,3
    ) soh14 on product_branch.product_id = soh14.product_id and product_branch.branch_id = soh14.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '1 month' AND now()
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales30 on product_branch.product_id = sales30.product_id and sales30.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                where date between now() - interval '1 month' and now()
                group by 1,2,3,4
        ) raw_soh_7
        where raw_soh_7.ttl_qty > 20
        group by 1,2,3
    ) soh30 on product_branch.product_id = soh30.product_id and product_branch.branch_id = soh30.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '2 month' AND now()
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales60 on product_branch.product_id = sales60.product_id and sales60.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                where date between now() - interval '2 month' and now()
                group by 1,2,3,4
        ) raw_soh_7
        where raw_soh_7.ttl_qty > 20
        group by 1,2,3
    ) soh60 on product_branch.product_id = soh60.product_id and product_branch.branch_id = soh60.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '3 month' AND now()
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales90 on product_branch.product_id = sales90.product_id and sales90.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                    where date between now() - interval '3 month' and now()
                    group by 1,2,3,4
        ) raw_soh_7
        where raw_soh_7.ttl_qty > 20
        group by 1,2,3
    ) soh90 on product_branch.product_id = soh90.product_id and product_branch.branch_id = soh90.branch_id
    left join (
            select
                aml2.product_id as product_id,
                am2.branch_id as branch_id,
                sum(aml2.quantity) as qty
                from account_move_line aml2
                left join account_move am2 on am2.id = aml2.move_id
                left join account_account aa2 on aml2.account_id = aa2.id
                left join res_branch rb2 on aml2.branch_id = rb2.id
                where am2.move_type in ('out_invoice','out_refund')
                and aa2.account_type = 'income'
                and am2.date BETWEEN current_date - interval '6 month' AND now()
                and aml2.partner_id not in (
                    select
                        partner_id
                    from mapping_virtual_customer_branch mvcb
                )
                group by 1,2
    ) sales180 on product_branch.product_id = sales180.product_id and sales180.branch_id = product_branch.branch_id
    left join (
            select product_id,product_code, branch_id, count(product_id) as av_days from (
                    select pp.id as product_id , product as product_code, rb.id as branch_id , "date" as date_soh,sum(ss.available_spot) as ttl_qty
                    from soh_snapshot ss
                    left join product_product pp on ss.product = pp.default_code
                    left join res_branch rb on ss.branch = rb.code
                    where date between now() - interval '6 month' and now()
                    group by 1,2,3,4
        ) raw_soh_7
        where raw_soh_7.ttl_qty > 20
        group by 1,2,3
    ) soh180 on product_branch.product_id = soh180.product_id and product_branch.branch_id = soh180.branch_id
    group by 1,2,3,4,5,6,7,8
                """
        self.env.cr.execute(query)
        query_svd_mart_abu_2024 = self.env.cr.dictfetchall()
        if query_svd_mart_abu_2024:
            for data_query_abu in query_svd_mart_abu_2024:
                _logger.info("%s-------------------- MART ABU 2024" % data_query_abu)
                product_id = data_query_abu[0]
                product_code = data_query_abu[1]
                product_name = data_query_abu[2]
                branch_id = data_query_abu[3]
                branch_name = data_query_abu[4]
                branch_code = data_query_abu[5]
                category_name = data_query_abu[6]
                sub_category_name = data_query_abu[7]
                qty_sales_7 = data_query_abu[8]
                av_days_7 = [9]
                svd_7 = [10]
                qty_sales_14 = data_query_abu[11]
                av_days_14 = [12]
                svd_14 = [13]
                qty_sales_30 = data_query_abu[14]
                av_days_30 = [15]
                svd_30 = [16]
                qty_sales_60 = data_query_abu[17]
                av_days_60 = [18]
                svd_60 = [19]
                qty_sales_90 = data_query_abu[20]
                av_days_90 = [21]
                svd_90 = [22]
                qty_sales_180 = data_query_abu[23]
                av_days_180 = [24]
                svd_180 = [25]
                query_create_svd_from_abu = """
                                                    INSERT INTO sbb_snapshot
                                                    ("product_code","product_name","branch_code","avg_7","avg_14","avg_30","avg_60","avg_90","avg_180","total_sales_qty_7","total_sales_qty_14","total_sales_qty_30","total_sales_qty_60","total_sales_qty_90","total_sales_qty_180","days_stock_available_7","days_stock_available_14","days_stock_available_30","days_stock_available_60","days_stock_available_90","days_stock_available_180","date","type_data_svd")
                                                    VALUES (%(product_code)s,%(product_name)s,%(branch_code)s,%(avg_7)s,%(avg_14)s,%(avg_30)s,%(avg_60)s,%(avg_90)s,%(avg_180)s,%(total_sales_qty_7)s,%(total_sales_qty_14)s,%(total_sales_qty_30)s,%(total_sales_qty_60)s,%(total_sales_qty_90)s,%(total_sales_qty_180)s,%(days_stock_available_7)s,%(days_stock_available_14)s,%(days_stock_available_30)s,%(days_stock_available_60)s,%(days_stock_available_90)s,%(days_stock_available_180)s,current_date,'abu')
                                                """
                execute_create_svd_snapshot = self.env.cr.execute(query_create_svd_from_abu, {
                    "product_code": product_code,
                    "product_name": product_name,
                    "branch_code": branch_code,
                    "avg_7": svd_7,
                    "avg_14": svd_14,
                    "avg_30": svd_30,
                    "avg_60": svd_60,
                    "avg_90": svd_90,
                    "avg_180": svd_180,
                    "total_sales_qty_7": qty_sales_7,
                    "total_sales_qty_14": qty_sales_14,
                    "total_sales_qty_30": qty_sales_30,
                    "total_sales_qty_60": qty_sales_60,
                    "total_sales_qty_90": qty_sales_90,
                    "total_sales_qty_180": qty_sales_180,
                    "days_stock_available_7": av_days_7,
                    "days_stock_available_14": av_days_14,
                    "days_stock_available_30": av_days_30,
                    "days_stock_available_60": av_days_60,
                    "days_stock_available_90": av_days_90,
                    "days_stock_available_180": av_days_180,
                })
                _logger.info("----------- DATA SNAPSHOT YANG DI CREATE %s" % execute_create_svd_snapshot)

    def cron_sbb_snapshot(self):
        today = date.today()
        self.env.cr.execute("Truncate sbb_snapshot")
        minimum_soh = self.env['ir.config_parameter'].sudo().get_param('minimum_soh')
        minimum_soh = float(minimum_soh)
        tanggal_golive_abu = []
        today = date.today()
        tanggal_golive = self.env['ir.config_parameter'].sudo().get_param('golive_abu_date')
        # self.query_pnj()
        if tanggal_golive:
            tanggal_golive_abu = datetime.strptime(tanggal_golive, '%Y-%m-%d').date()

        if not tanggal_golive_abu or (today - timedelta(days=180) < tanggal_golive_abu and today < tanggal_golive_abu):
            _logger.info("---------------- MASUK KONDISI PERTAMA")
            self.query_before_golive_abu()
        elif today - timedelta(days=180) > tanggal_golive_abu:
            _logger.info("---------------- MASUK KONDISI KEDUA")
            self.query_after_golive_abu()
        else:
            _logger.info("---------------- MASUK KONDISI KETIGA")
            self.query_split_dwh_abu()