--
-- Stand-in structure for view `tbl_carbrstock`
-- (See below for the actual view)
--
CREATE TABLE `tbl_carbrstock` (
    `brid` int(11),
    `pid` int(11),
    `name` varchar(65),
    `bname` varchar(255),
    `pcode` varchar(15),
    `image` varchar(50),
    `catid` int(11),
    `scatid` int(11),
    `brnid` int(11),
    `apdate` date,
    `colid` int(11),
    `sizid` int(11),
    `unqid` varchar(15),
    `code` varchar(15),
    `clrcv` double,
    `cldeli` double,
    `clundeli` double,
    `cadeli` double,
    `carcv` double,
    `caunrcv` double,
    `avqty` double,
    `status` int(1)
);
-- --------------------------------------------------------
--
-- Structure for view `tbl_carbrstock`
--
DROP TABLE IF EXISTS `tbl_carbrstock`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `tbl_carbrstock` AS (
    select `brc`.`brid` AS `brid`,
        `bst`.`pid` AS `pid`,
        `pro`.`name` AS `name`,
        `pro`.`bname` AS `bname`,
        `pro`.`code` AS `pcode`,
        `pro`.`image` AS `image`,
        `pro`.`catid` AS `catid`,
        `pro`.`scatid` AS `scatid`,
        `pro`.`brnid` AS `brnid`,
        `brc`.`apdate` AS `apdate`,
        `brc`.`colid` AS `colid`,
        `brc`.`sizid` AS `sizid`,
        `bst`.`unqid` AS `unqid`,
        `bst`.`code` AS `code`,
        ifnull(`bro`.`clrcv`, 0) AS `clrcv`,
        ifnull(`brp`.`cldeli`, 0) AS `cldeli`,
        ifnull(`bro`.`clrcv`, 0) - ifnull(`brp`.`cldeli`, 0) AS `clundeli`,
        ifnull(`seo`.`cadeli`, 0) AS `cadeli`,
        ifnull(`sel`.`carcv`, 0) AS `carcv`,
        ifnull(`seo`.`cadeli`, 0) - ifnull(`sel`.`carcv`, 0) AS `caunrcv`,
        ifnull(`bro`.`clrcv`, 0) - ifnull(`seo`.`cadeli`, 0) AS `avqty`,
        `bst`.`status` AS `status`
    from (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (
                                                    (
                                                        (
                                                            (
                                                                (
                                                                    select `tbl_carstock`.`pid` AS `pid`,
                                                                        `tbl_carstock`.`unqid` AS `unqid`,
                                                                        `tbl_carstock`.`code` AS `code`,
                                                                        `tbl_carstock`.`status` AS `status`
                                                                    from `tbl_carstock`
                                                                    group by `tbl_carstock`.`unqid`
                                                                ) `bst`
                                                                left join (
                                                                    select `tbl_cartraproduct`.`unqid` AS `unqid`,
                                                                        `tbl_cartraproduct`.`apdate` AS `apdate`,
                                                                        `tbl_cartraproduct`.`colid` AS `colid`,
                                                                        `tbl_cartraproduct`.`sizid` AS `sizid`,
                                                                        `tbl_cartraproduct`.`brid` AS `brid`
                                                                    from `tbl_cartraproduct`
                                                                    where `tbl_cartraproduct`.`waid` is null
                                                                    group by `tbl_cartraproduct`.`unqid`,
                                                                        `tbl_cartraproduct`.`brid`
                                                                ) `brc` on(`brc`.`unqid` = `bst`.`unqid`)
                                                            )
                                                            left join (
                                                                select `tbl_cartraproduct`.`unqid` AS `unqid`,
                                                                    `tbl_cartraproduct`.`apdate` AS `apdate`,
                                                                    `tbl_cartraproduct`.`colid` AS `colid`,
                                                                    `tbl_cartraproduct`.`sizid` AS `sizid`,
                                                                    sum(`tbl_cartraproduct`.`clrcv`) AS `clrcv`,
                                                                    `tbl_cartraproduct`.`brid` AS `brid`
                                                                from `tbl_cartraproduct`
                                                                where `tbl_cartraproduct`.`mods` = 'CR'
                                                                    and `tbl_cartraproduct`.`waid` is null
                                                                group by `tbl_cartraproduct`.`unqid`,
                                                                    `tbl_cartraproduct`.`brid`
                                                            ) `bro` on(
                                                                `bro`.`unqid` = `bst`.`unqid`
                                                                and `bro`.`brid` = `brc`.`brid`
                                                            )
                                                        )
                                                        left join (
                                                            select `tbl_cartraproduct`.`unqid` AS `unqid`,
                                                                `tbl_cartraproduct`.`apdate` AS `apdate`,
                                                                `tbl_cartraproduct`.`colid` AS `colid`,
                                                                `tbl_cartraproduct`.`sizid` AS `sizid`,
                                                                sum(`tbl_cartraproduct`.`cldeli`) AS `cldeli`,
                                                                `tbl_cartraproduct`.`brid` AS `brid`
                                                            from `tbl_cartraproduct`
                                                            where `tbl_cartraproduct`.`mods` in ('XD')
                                                                and `tbl_cartraproduct`.`waid` is null
                                                            group by `tbl_cartraproduct`.`unqid`,
                                                                `tbl_cartraproduct`.`brid`
                                                        ) `brp` on(
                                                            `brp`.`unqid` = `bst`.`unqid`
                                                            and `brp`.`brid` = `brc`.`brid`
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                        left join (
                                            select `tbl_cartraproduct`.`unqid` AS `unqid`,
                                                `tbl_cartraproduct`.`apdate` AS `apdate`,
                                                `tbl_cartraproduct`.`colid` AS `colid`,
                                                `tbl_cartraproduct`.`sizid` AS `sizid`,
                                                sum(`tbl_cartraproduct`.`cadeli`) AS `cadeli`,
                                                `tbl_cartraproduct`.`brid` AS `brid`
                                            from `tbl_cartraproduct`
                                            where `tbl_cartraproduct`.`mods` = 'CD'
                                            group by `tbl_cartraproduct`.`unqid`,
                                                `tbl_cartraproduct`.`brid`
                                        ) `seo` on(
                                            `seo`.`unqid` = `bst`.`unqid`
                                            and `seo`.`brid` = `brc`.`brid`
                                        )
                                    )
                                    left join (
                                        select `tbl_cartraproduct`.`unqid` AS `unqid`,
                                            `tbl_cartraproduct`.`apdate` AS `apdate`,
                                            `tbl_cartraproduct`.`colid` AS `colid`,
                                            `tbl_cartraproduct`.`sizid` AS `sizid`,
                                            sum(`tbl_cartraproduct`.`carcv`) AS `carcv`,
                                            `tbl_cartraproduct`.`brid` AS `brid`
                                        from `tbl_cartraproduct`
                                        where `tbl_cartraproduct`.`mods` in ('XR')
                                        group by `tbl_cartraproduct`.`unqid`,
                                            `tbl_cartraproduct`.`brid`
                                    ) `sel` on(
                                        `sel`.`unqid` = `bst`.`unqid`
                                        and `sel`.`brid` = `brc`.`brid`
                                    )
                                )
                            )
                        )
                    )
                )
            )
            left join (
                select `tbl_item`.`id` AS `id`,
                    `tbl_item`.`code` AS `code`,
                    `tbl_item`.`name` AS `name`,
                    `tbl_item`.`bname` AS `bname`,
                    `tbl_item`.`image` AS `image`,
                    `tbl_item`.`catid` AS `catid`,
                    `tbl_item`.`scatid` AS `scatid`,
                    `tbl_item`.`brid` AS `brnid`
                from `tbl_item`
                where `tbl_item`.`status` = '1'
            ) `pro` on(`pro`.`id` = `bst`.`pid`)
        )
    where `brc`.`brid` is not null
    order by `pro`.`name`
);
-- --------------------------------------------------------