Untitled

from odoo import fields, models, api, tools,_
import logging
from odoo.exceptions import ValidationError

_logger = logging.getLogger(__name__)


class CurrentStockDcView(models.Model):
    _name = 'current.stock.dc.view'
    _description = 'current.stock.dc.view'
    _auto = False
    _rec_name = 'id'

    category_id = fields.Integer(string='Category ID')
    category_name = fields.Char(string='Category Name')
    product = fields.Char(string='Product')
    branch = fields.Char(string='Branch')
    physical = fields.Integer(string='Physical')
    so_draft = fields.Integer(string='So Draft')
    so_confirm = fields.Integer(string='So Confirm')
    so_waiting = fields.Integer(string='So Waiting')
    so_staging_mnf = fields.Integer(string='So Staging Mnf')
    commit_to_draft = fields.Integer(string='Commit TO Draft')
    commit_to = fields.Integer(string='Commit TO')
    to_satu_branch = fields.Integer(string='TO Satu Branch')
    to_antar_branch = fields.Integer(string='TO Antar Branch')
    mo = fields.Integer(string='MO')
    mo_draft = fields.Integer(string='MO Draft')
    dynamic_reserve = fields.Integer(string='Dynamic Reserve')
    available_spot = fields.Integer(string='Available Spot')
    available_spot_submit = fields.Integer(string='Available Spot Submit')
    available_contract = fields.Integer(string='Available Contract')
    available_contract_submit = fields.Integer(string='Available Contract Submit')

    product_category_id = fields.Many2one('attribute.product.category')
    category = fields.Char('Sub Category')
    total_stock = fields.Char('Total Stock')
    psr = fields.Integer(string='PSR')
    psr_draft = fields.Integer(string='PSR draft')
    psr_confirm = fields.Integer(string='PSR Confirm')


    def cron_cancel_commit_to(self):
        _logger.info("---------------------- cron_cancel_commit_to -------------------------")
        query_select_current_stock_dc = """
            select available_contract as available_contract ,
            commit_to as commit_to ,
            product as default_code,
            branch as branch,
            dynamic_reserve as dynamic_reserve
            from current_stock_dc_view csdv
            where available_contract < 0 and commit_to > 0
        """
        self.env.cr.execute(query_select_current_stock_dc)
        current_stock_dc = self.env.cr.dictfetchall()
        _logger.info("%s --------------- current_stock_dc"%current_stock_dc)

        if current_stock_dc:

            for stock_dc in current_stock_dc:

                default_code = stock_dc['default_code']
                available_contract = stock_dc['available_contract']
                commit_to = stock_dc['commit_to']
                branch = stock_dc['branch']

                _logger.info("%s --------------- default_code"%default_code)
                _logger.info("%s --------------- available_contract"%available_contract)
                _logger.info("%s --------------- commit_to"%commit_to)
                _logger.info("%s --------------- branch"%branch)

                if branch == 'JKT':

                    query_select_commit_to = """
                        select ctob.id as to_id, jakarta as qty_jkt from commited_transfer_order_branch ctob
                        left join product_product pp on ctob.product_id = pp.id
                        where pp.default_code = %(default_code)s and jakarta > 0 and status = 'confirmed'
                        order by jakarta desc
                    """
                    self.env.cr.execute(query_select_commit_to,{
                        'default_code' : default_code
                    })
                    commited_to = self.env.cr.fetchone()

                elif branch == 'SBY':
                    query_select_commit_to = """
                        select ctob.id as to_id, jakarta as qty_jkt from commited_transfer_order_branch ctob
                        left join product_product pp on ctob.product_id = pp.id
                        where pp.default_code = %(default_code)s and surabaya > 0 and status = 'confirmed'
                        order by surabaya desc
                    """
                    self.env.cr.execute(query_select_commit_to,{
                        'default_code' : default_code
                    })
                    commited_to = self.env.cr.fetchone()

                commited_to_branch = self.env['commited.transfer.order.branch'].sudo().search([('id','=',commited_to)],limit=1)
                _logger.info("%s --------------- commited_to_branch"%commited_to_branch)

                commited_to_branch.sudo().unlink()


#
    def init(self):
        pnj_db = self.env['integration.external.db'].sudo().search([('server', '=', 'pnj_replication')],
                                                                   limit=1)
        print(pnj_db)
        if not pnj_db:
            raise ValidationError(
                "Server Database Replication PNJ Belum di setting, silahkan di setting external DB nya terlebih dahulu!")
        sql_pnj = """
                select
                    pp.default_code as product,
                    rb.code as branch,
                    coalesce(sum(sq.quantity),0) - coalesce(sum(sq.reserved_quantity),0) as physical
                from stock_quant sq
                left join stock_location sl on sq.location_id = sl.id
                inner join product_product pp on sq.product_id = pp.id
                inner join product_template pt on pp.product_tmpl_id = pt.id
                inner join attribute_product_category apc on pt.attribute_categ_id = apc.id
                inner join res_branch rb  on sl.branch_id = rb.id
                where
                    sl.active = true
                    and sl.usage = $$internal$$
                    and pp.default_code  like $$P%$$
               group by 1,2
        """

        pnj_db = self.env['integration.external.db'].sudo().search([('server', '=', 'pnj_replication')],
                                                                   limit=1)
        # print(pnj_db)
        # if not pnj_db:
        #     raise ValidationError(
        #         "Server Database Replication PNJ Belum di setting, silahkan di setting external DB nya terlebih dahulu!")
        sql_pnj = """
                        select
                            pp.default_code as product,
                            rb.code as branch,
                            coalesce(sum(sq.quantity),0) - coalesce(sum(sq.reserved_quantity),0) as physical
                        from stock_quant sq
                        left join stock_location sl on sq.location_id = sl.id
                        inner join product_product pp on sq.product_id = pp.id
                        inner join product_template pt on pp.product_tmpl_id = pt.id
                        inner join attribute_product_category apc on pt.attribute_categ_id = apc.id
                        inner join res_branch rb  on sl.branch_id = rb.id
                        where
                            sl.active = true
                            and sl.usage = $$internal$$
                            and pp.default_code  like $$P%$$
                       group by 1,2
                """
        com_db = self.env['integration.external.db'].sudo().search([('server', '=', 'repl_community')],
                                                                   limit=1)
        # if not com_db:
        #     raise ValidationError(
        #         "Server Database Replication Community Belum di setting, silahkan di setting external DB nya terlebih dahulu!")

        sql_com = """
            select
                    branch,
                    product,
                    sum(so_draft) as so_draft,
                    sum(so_waiting) as so_waiting,
                    sum(so_staging_mnf) as so_staging_mnf,
                    sum(psr_draft) as psr_draft,
                    sum(psr) as psr
                from (
                    select
                    branch,
                    product,
                    sum(so_draft) as so_draft,
                    sum(so_waiting) as so_waiting,
                    sum(so_staging_mnf) as so_staging_mnf,
                    sum(psr_draft) as psr_draft,
                    sum(psr) as psr
                    from (
                        select
                            rb.code as branch,
                            pp.default_code as product,
                            SUM(CASE WHEN so.state = $$draft$$ THEN sol.product_uom_qty ELSE 0 END) AS so_draft,
                            SUM(CASE WHEN so.state = $$waiting_confirmation$$ THEN sol.product_uom_qty ELSE 0 END) AS so_waiting,
                            0 as so_staging_mnf,
                            0 as psr_draft,
                            0 as psr
                        from sale_order_line sol
                        left join sale_order so on sol.order_id = so.id
                        left join res_branch rb on so.branch_id = rb.id
                        left join product_product pp on sol.product_id = pp.id
                        group by 1,2
                    ) a
                    group by 1,2
                    union all(
                        -- SO Staging MNF
                        select
                            rb.code as branch,
                            l.product_code as product,
                            0 as so_draft,
                            0 as so_waiting,
                            sum(l.product_qty) as so_staging_mnf,
                            0 as psr_draft,
                            0 as psr
                        from so_staging_crmmnf_line l
                        left join so_staging_crmmnf hd on l.so_staging_id = hd.id
                        left join res_branch rb on hd.branch_id = rb.id
                        where hd.state in ($$confirm$$,$$waiting_ppic$$)
                        group by 1,2
                    )
                    union all(
                        -- PSR
                        select
                            rb.code as branch,
                            pp.default_code as product,
                            0 as so_draft,
                            0 as so_waiting,
                            0 as so_staging_mnf,
                            SUM(CASE WHEN psr.state = $$draft$$ THEN psrl.request_qty ELSE 0 END) as psr_draft,
                            SUM(CASE WHEN psr.state in ($$waiting_sm$$,$$waiting_gm_sales$$) THEN psrl.request_qty ELSE 0 END) as psr_draft
                        from product_sample_request_line psrl
                        left join product_sample_request psr on psrl.product_sample_request_id = psr.id
                        left join res_branch rb on psr.branch_id = rb.id
                        left join product_product pp on psrl.product_id = pp.id
                        where psr.state in ($$draft$$,$$waiting_sm$$,$$waiting_gm_sales$$)
                        group by 1,2
                    )
                ) community
                group by 1,2
        """


        sql = """
                select
                row_number() OVER() AS id,
                *
                from (
                    select
                    category_id,
                    category_name,
                    product,
                    branch,
                    coalesce(sum(physical),0) as physical,
                    coalesce(sum(so_draft),0) as so_draft,
                    coalesce(sum(so_confirm),0) as so_confirm,
                    coalesce(sum(so_waiting),0) as so_waiting,
                    coalesce(sum(so_staging_mnf),0) as so_staging_mnf,
                    coalesce(max(commit_to_draft),0) as commit_to_draft,
                    coalesce(sum(commit_to),0) as commit_to,
                    coalesce(sum(to_satu_branch),0) as to_satu_branch,
                    coalesce(sum(to_antar_branch),0) as to_antar_branch,
                    coalesce(sum(mo),0) as mo,
                    coalesce(sum(mo_draft),0) as mo_draft,
                    coalesce(sum(psr_draft),0) as psr_draft,
                    coalesce(sum(psr),0) as psr,
                    coalesce(sum(psr_confirm),0) as psr_confirm,
                    coalesce(sum(dynamic_reserve),0) as dynamic_reserve,
                    (
                        coalesce(sum(physical),0)
                        - coalesce(sum(so_confirm),0)
                        - coalesce(sum(so_waiting),0)
                        - coalesce(sum(so_staging_mnf),0)
                        - coalesce(sum(dynamic_reserve),0)
                        - coalesce(sum(commit_to),0)
                        + coalesce(sum(to_satu_branch),0)
                        - coalesce(sum(to_antar_branch),0)
                        - coalesce(sum(mo),0)
                        - coalesce(sum(psr),0)
                        - coalesce(sum(psr_confirm),0)
                    ) as available_spot,
                    (
                        coalesce(sum(physical),0)
                        - coalesce(sum(so_confirm),0)
                        - coalesce(sum(so_draft),0)
                        - coalesce(sum(so_waiting),0)
                        - coalesce(sum(so_staging_mnf),0)
                        - coalesce(sum(commit_to),0)
                        - coalesce(sum(commit_to_draft),0)
                        + coalesce(sum(to_satu_branch),0)
                        - coalesce(sum(to_antar_branch),0)
                        - coalesce(sum(mo),0)
                        - coalesce(sum(mo_draft),0)
                        - coalesce(sum(dynamic_reserve),0)
                        - coalesce(sum(psr),0)
                        - coalesce(sum(psr_confirm),0)
                        - coalesce(sum(psr_draft),0)
                    ) as available_spot_submit,
                    (
                        coalesce(sum(physical),0)
                        - coalesce(sum(so_confirm),0)
                        - coalesce(sum(so_waiting),0)
                        - coalesce(sum(so_staging_mnf),0)
                        - coalesce(sum(commit_to),0)
                        - coalesce(sum(mo),0)
                        + coalesce(sum(to_satu_branch),0)
                        - coalesce(sum(to_antar_branch),0)
                        - coalesce(sum(psr),0)
                        - coalesce(sum(psr_confirm),0)
                    ) as available_contract,
                    (
                        coalesce(sum(physical),0)
                        - coalesce(sum(so_confirm),0)
                        - coalesce(sum(so_draft),0)
                        - coalesce(sum(so_waiting),0)
                        - coalesce(sum(commit_to),0)
                        - coalesce(sum(commit_to_draft),0)
                        - coalesce(sum(so_staging_mnf),0)
                        + coalesce(sum(to_satu_branch),0)
                        - coalesce(sum(to_antar_branch),0)
                        - coalesce(sum(mo),0)
                        - coalesce(sum(mo_draft),0)
                        - coalesce(sum(psr),0)
                        - coalesce(sum(psr_confirm),0)
                        - coalesce(sum(psr_draft),0)
                    ) as available_contract_submit
                 from (
                                select
                                apc.id as category_id,
                                apc.name as category_name,
                                pp.default_code as product,
                                rb.code as branch,
                                coalesce(sum(sq.quantity),0) as physical,
                                coalesce(max(so_draft.so_draft),0) so_draft,
                                coalesce(max(so_confirm.so_confirm),0) as so_confirm,
                                coalesce(max(so_draft.so_waiting),0) as so_waiting,
                                coalesce(max(so_draft.so_staging_mnf),0) as so_staging_mnf,
                                MAX(CASE WHEN rb.code = $$JKT$$ THEN commit_to_draft.jkt ELSE commit_to_draft.sby END) AS commit_to_draft,
                                MAX(CASE WHEN rb.code = $$JKT$$ THEN commit_to.jkt ELSE commit_to.sby END) AS commit_to,
                                max(to_satu_branch.to_satu_branch) as to_satu_branch,
                                max(to_antar_branch.to_antar_branch) as to_antar_branch,
                                coalesce(max(mo.mo),0) as mo,
                                coalesce(max(mo_draft.mo_draft),0) as mo_draft,
                                coalesce(max(so_draft.psr_draft),0) as psr_draft,
                                coalesce(max(so_draft.psr),0) as psr,
                                coalesce(max(psr_ent.psr),0) as psr_confirm,
                                max(dynamic.dynamic) as dynamic_reserve
                            from stock_quant sq
                            left join stock_location sl on sq.location_id = sl.id
                            inner join product_product pp on sq.product_id = pp.id
                            inner join product_template pt on pp.product_tmpl_id = pt.id
                            inner join attribute_product_category apc on pt.attribute_categ_id = apc.id
                            inner join res_branch rb  on sl.branch_id = rb.id
                            inner join status_location statloc on statloc.id = sl.status_location_id
                            full join dblink('host=%s dbname=%s user=%s password=%s', '%s') so_draft (
                                branch VARCHAR,
                                product VARCHAR,
                                so_draft FLOAT,
                                so_waiting FLOAT,
                                so_staging_mnf FLOAT,
                                psr_draft FLOAT,
                                psr FLOAT
                            ) on pp.default_code = so_draft.product and rb.code = so_draft.branch
                            full join (
                                select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    SUM(CASE WHEN so.status_so  in ('confirmed','assigned_to_pick','on_picking') THEN sol.product_uom_qty ELSE 0 END) AS so_confirm
                                from sale_order_line sol
                                left join sale_order so on sol.order_id = so.id
                                left join res_branch rb on so.branch_id = rb.id
                                left join product_product pp on sol.product_id = pp.id
                                group by 1,2
                            ) so_confirm on pp.default_code = so_confirm.product and rb.code = so_confirm.branch
                            full join (
                                select
                                    pp.default_code as product,
                                    sum(cto.jakarta) as jkt,
                                    sum(cto.surabaya) as sby
                                from commited_transfer_order_branch cto
                                left join product_product pp on cto.product_id = pp.id
                                where cto.status = 'draft'
                                group by product
                            ) commit_to_draft on pp.default_code = commit_to_draft.product
                            left join (
                                select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    SUM(psrl.request_qty) as psr
                                from product_sample_request_line psrl
                                left join product_sample_request psr on psrl.product_sample_request_id = psr.id
                                left join res_branch rb on psr.branch_id = rb.id
                                left join product_product pp on psrl.product_id = pp.id
                                where psr.state in ($$confirm$$,$$assigned_to_pick$$,$$on_picking$$)
                                group by 1,2
                            ) psr_ent on pp.default_code = psr_ent.product and rb.code = psr_ent.branch
                            full join (
                                select
                                    pp.default_code as product,
                                    sum(cto.jakarta) as jkt,
                                    sum(cto.surabaya) as sby
                                from commited_transfer_order_branch cto
                                left join product_product pp on cto.product_id = pp.id
                                where cto.status = 'confirmed'
                                group by product
                            ) commit_to on pp.default_code = commit_to.product
                            full join (
                                 select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    SUM(CASE WHEN to2.status in ($$assigned$$,$$on_picking$$) THEN mtol.confirm_qty  ELSE 0 END) AS confirm_qty,
                                    SUM(CASE WHEN to2.status in ($$done_picking$$,$$on_rack_prepare$$) THEN mtol.picked_qty ELSE 0 END) AS picked_qty,
                                    SUM(CASE WHEN to2.status in ($$shipped$$,$$received$$) THEN mtol.shipped_qty ELSE 0 END) AS shipped_qty,
                                    SUM(CASE WHEN to2.status in ($$done_picking$$,$$on_rack_prepare$$) THEN mtol.picked_qty ELSE 0 END) +
                                    SUM(CASE WHEN to2.status in ($$shipped$$,$$received$$) THEN mtol.shipped_qty ELSE 0 END) as to_satu_branch
                                from merge_transfer_order_line mtol
                                left join transfer_order to2 on mtol.transfer_order_id  = to2.id
                                inner join res_branch rb on to2.source_branch_id = rb.id
                                left join product_product pp on mtol.product_id = pp.id
                                where to2.status not in ('draft', 'confirmed','cancel')
                                and to2.satu_branch = true
                                group by 1,2
                            ) to_satu_branch on pp.default_code = to_satu_branch.product and rb.code = to_satu_branch.branch
                            full join (
                                 select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    SUM(CASE WHEN to2.status in ($$draft$$,$$assigned$$,$$on_picking$$,$$confirmed$$) and coalesce(rb2.virtual_branch, false) != true THEN mtol.confirm_qty  ELSE 0 END) AS confirm_qty,
                                    SUM(CASE WHEN to2.status in ($$done_picking$$,$$on_rack_prepare$$) and coalesce(rb2.virtual_branch, false) != true THEN mtol.picked_qty ELSE 0 END) AS picked_qty,
                                    SUM(CASE WHEN to2.status in ($$shipped$$,$$received$$) and coalesce(rb2.virtual_branch, false) != true THEN mtol.shipped_qty ELSE 0 END) AS shipped_qty,
                                    SUM(CASE WHEN to2.status in ($$draft$$,$$assigned$$,$$on_picking$$,$$confirmed$$) and coalesce(rb2.virtual_branch, false) != true THEN mtol.confirm_qty  ELSE 0 END) as to_antar_branch
                                from merge_transfer_order_line mtol
                                left join transfer_order to2 on mtol.transfer_order_id  = to2.id
                                inner join res_branch rb on to2.source_branch_id = rb.id
                                left join res_branch rb2 on to2.destination_branch_id = rb2.id
                                left join product_product pp on mtol.product_id = pp.id
                                where to2.status in ('draft','confirmed','assigned','on_picking')
                                and to2.antar_branch = true
                                group by 1,2
                            ) to_antar_branch on pp.default_code = to_antar_branch.product and rb.code = to_antar_branch.branch
                            left join (
                                select product,source_branch as branch,sum(stock_allocation) as dynamic from  demand_dc_contract_allocation bca group by 1,2
                            ) dynamic on pp.default_code = dynamic.product and rb.code = dynamic.branch
                            left join (
                                select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    GREATEST(0, sum(amc.product_uom_qty) - sum(amc.comsume_qty)) as mo
                                from abu_mrp_consumed amc
                                left join abu_mrp_production amp on amc.production_id = amp.id
                                inner join res_branch rb on amp.branch_id  = rb.id
                                inner join product_product pp on amc.product_id = pp.id
                                where amp.state in ('confirmed','assigned_pick','done_pick')
                                group by 1,2
                            ) mo on pp.default_code = mo.product and rb.code = mo.branch
                            left join (
                                select
                                    rb.code as branch,
                                    pp.default_code as product,
                                    sum(amc.product_uom_qty) as mo_draft
                                from abu_mrp_consumed amc
                                left join abu_mrp_production amp on amc.production_id = amp.id
                                inner join res_branch rb on amp.branch_id  = rb.id
                                inner join product_product pp on amc.product_id = pp.id
                                where amp.state = 'draft'
                                group by 1,2
                            ) mo_draft on pp.default_code = mo_draft.product and rb.code = mo_draft.branch
                            where
                                sl.active = true
                                and sl.usage = $$internal$$
                                and statloc.type = $$available$$
                                and pt.item_type = 'commercial'
                                and sq.quantity > 0
                                and sl.id in (SELECT
                                    sl.id
                                   FROM stock_location sl
                                   left join stock_location sl2 on sl.location_id = sl2.id
                                   where sl2.name = 'Stock')
                           group by 1,2,3,4
                ) a
                where branch in ('JKT','SBY')
                group by 1,2,3,4
)a
        """ % (com_db.host, com_db.dbname, com_db.user, com_db.password, sql_com, )
        # print("""
        #     CREATE EXTENSION IF NOT EXISTS dblink;
        #     CREATE OR REPLACE VIEW %s AS (%s) ;
        #     """ % (self._table, sql))

        tools.drop_view_if_exists(self._cr, self._table)
        self._cr.execute("""
            CREATE EXTENSION IF NOT EXISTS dblink;
            CREATE OR REPLACE VIEW %s AS (%s) ;
            """ % (self._table, sql)
                         )