-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `bluelogistics_freight_care`
--

-- --------------------------------------------------------

--
-- Stand-in structure for view `erp_finance_statement`
-- (See below for the actual view)
--
CREATE TABLE `erp_finance_statement` (
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_all_stock_items`
-- (See below for the actual view)
--
CREATE TABLE `view_all_stock_items` (
`id` int(11)
,`part_number` varchar(100)
,`description` varchar(255)
,`brand` varchar(255)
,`brand_id` int(11)
,`generic_id` int(11)
,`unit` varchar(10)
,`isActive` enum('Y','N')
,`type` enum('I','S')
,`rate` decimal(15,2)
,`in_qty` decimal(37,3)
,`out_qty` decimal(37,3)
,`quantity` decimal(37,3)
,`alloc_qty` decimal(37,2)
,`tax_code` varchar(255)
,`generic_name` varchar(255)
,`have_serial_no` enum('N','Y')
,`stock_updated` enum('N','Y')
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_gi_storage_charges`
-- (See below for the actual view)
--
CREATE TABLE `view_gi_storage_charges` (
`transaction_id` int(11)
,`product_id` int(11)
,`part_number` varchar(100)
,`description` varchar(100)
,`quantity` decimal(15,2)
,`carton_name` varchar(255)
,`no_of_cartons` int(11)
,`cbm_per_carton` decimal(15,10)
,`total_cbm` decimal(15,10)
,`storage_charges_per_cbm` decimal(15,6)
,`total_storage_charges` decimal(30,16)
,`transaction_date` date
,`transaction_no` int(11)
,`customer_id` int(11)
,`customer_name` varchar(255)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_gr_handling_charges`
-- (See below for the actual view)
--
CREATE TABLE `view_gr_handling_charges` (
`receipt_id` int(11)
,`transaction_date` date
,`receipt_no` int(11)
,`customer_id` int(11)
,`customer_name` varchar(255)
,`total_cbm` decimal(15,10)
,`ceil_total_cbm` int(11)
,`handling_charges_per_cbm` decimal(15,6)
,`total_handling_charges` decimal(25,6)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_gr_storage_charges`
-- (See below for the actual view)
--
CREATE TABLE `view_gr_storage_charges` (
`transaction_id` int(11)
,`product_id` int(11)
,`part_number` varchar(100)
,`description` varchar(100)
,`quantity` decimal(15,2)
,`carton_name` varchar(255)
,`no_of_cartons` int(11)
,`cbm_per_carton` decimal(15,10)
,`total_cbm` decimal(15,10)
,`storage_charges_per_cbm` decimal(15,2)
,`total_storage_charges` decimal(30,12)
,`transaction_date` date
,`transaction_no` int(11)
,`customer_id` int(11)
,`customer_name` varchar(255)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_handling_charges`
-- (See below for the actual view)
--
CREATE TABLE `view_handling_charges` (
`transaction_type` varchar(2)
,`transaction_id` int(11)
,`transaction_date` date
,`transaction_no` int(11)
,`customer_id` int(11)
,`customer_name` varchar(255)
,`total_cbm` decimal(15,10)
,`ceil_total_cbm` int(11)
,`handling_charges_per_cbm` decimal(15,6)
,`total_handling_charges` decimal(25,6)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_inventory_detailed_items_in_marshalling_area_report`
-- (See below for the actual view)
--
CREATE TABLE `view_inventory_detailed_items_in_marshalling_area_report` (
`package_id` int(11)
,`transaction_id` int(11)
,`transaction_item_id` int(11)
,`product_id` int(11)
,`part_number` varchar(100)
,`description` varchar(100)
,`quantity` decimal(15,2)
,`unit` int(11)
,`unit_name` varchar(50)
,`rate` decimal(15,2)
,`value` decimal(30,4)
,`weight` decimal(15,2)
,`cbm` decimal(15,10)
,`bill_of_entry_exit` varchar(30)
,`warehouse_id` int(11)
,`warehouse_name` varchar(255)
,`rack_id` int(11)
,`rack_name` varchar(255)
,`bin_id` int(11)
,`bin_name` varchar(255)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_inventory_summary_items_in_marshalling_area_report`
-- (See below for the actual view)
--
CREATE TABLE `view_inventory_summary_items_in_marshalling_area_report` (
`package_id` int(11)
,`transaction_type` varchar(20)
,`transaction_date` date
,`transaction_id` int(11)
,`total_qty` decimal(37,2)
,`total_rate` decimal(37,2)
,`total_value` decimal(52,4)
,`total_cbm` decimal(37,10)
,`customer_name` varchar(255)
,`customer_id` int(11)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_packaging_list`
-- (See below for the actual view)
--
CREATE TABLE `view_packaging_list` (
`transaction_id` int(11)
,`file_id` varchar(11)
,`transaction_date` date
,`customer_id` bigint(20)
,`customer_name` varchar(255)
,`packing_frm` varchar(13)
,`status` varchar(1)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_stock_ledger_details`
-- (See below for the actual view)
--
CREATE TABLE `view_stock_ledger_details` (
`id` int(11)
,`bin_name` varchar(255)
,`rack_name` varchar(255)
,`warehouse_name` varchar(255)
,`transaction_date` date
,`transaction_type` varchar(3)
,`type` varchar(50)
,`customer_vendor_id` int(11)
,`file_id` int(11)
,`header_id` int(11)
,`item_id` int(11)
,`product_id` int(11)
,`brand_id` int(11)
,`rate` decimal(10,3)
,`currency` int(11)
,`exchange_rate` decimal(15,2)
,`quantity` decimal(16,3)
,`unit` int(11)
,`warehouse_id` int(11)
,`rack_id` int(11)
,`bin_id` int(11)
,`stock_updated` varchar(1)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_stock_ledger_summary`
-- (See below for the actual view)
--
CREATE TABLE `view_stock_ledger_summary` (
`id` int(11)
,`customer_vendor_id` int(11)
,`header_id` int(11)
,`transaction_date` date
,`transaction_type` enum('IN','OUT')
,`transaction_from` varchar(50)
,`customer_vendor_name` varchar(255)
,`product_id` int(11)
,`quantity` decimal(15,3)
,`unit_id` int(11)
,`warehouse_id` int(11)
,`rack_id` int(11)
,`bin_id` int(11)
,`created_by` int(11)
,`created_date` datetime
,`summary_qty` decimal(37,3)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_stock_state_as_of`
-- (See below for the actual view)
--
CREATE TABLE `view_stock_state_as_of` (
`customer_id` int(11)
,`customer_name` varchar(255)
,`product_id` int(11)
,`part_number` varchar(100)
,`description` varchar(255)
,`brand` varchar(255)
,`generic_name` varchar(255)
,`in_qty` decimal(37,3)
,`out_qty` decimal(37,3)
,`brand_id` int(11)
,`generic_id` int(11)
,`rate` decimal(15,2)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_storage_charges`
-- (See below for the actual view)
--
CREATE TABLE `view_storage_charges` (
`transaction_type` varchar(2)
,`transaction_id` int(11)
,`product_id` int(11)
,`part_number` varchar(100)
,`description` varchar(100)
,`quantity` decimal(15,2)
,`carton_name` varchar(255)
,`no_of_cartons` int(11)
,`cbm_per_carton` decimal(15,10)
,`total_cbm` decimal(16,10)
,`storage_charges_per_cbm` decimal(19,6)
,`total_storage_charges` decimal(34,16)
,`transaction_date` date
,`transaction_no` int(11)
,`customer_id` int(11)
,`customer_name` varchar(255)
);

-- --------------------------------------------------------

--
-- Structure for view `erp_finance_statement`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics_freight`@`localhost` SQL SECURITY DEFINER VIEW `erp_finance_statement`  AS SELECT `V`.`id` AS `voucher_id`, `V`.`voucher_date` AS `voucher_date`, `V`.`due_date` AS `due_date`, `V`.`voucher_type` AS `type`, `V`.`header_table_id` AS `head_id`, `V`.`reference_no` AS `reference_no`, `VI`.`narration` AS `narration`, `VI`.`id` AS `voucher_item_id`, `V`.`voucher_no` AS `voucher_no`, round(abs(`VI`.`amount` * `VI`.`exchange_rate`),4) AS `amount`, round(ifnull(abs(sum(`AI`.`amount`)),0),4) AS `allocated`, round(abs(`VI`.`amount` * `VI`.`exchange_rate`),4) - round(ifnull(abs(sum(`AI`.`amount`)),0),4) AS `balance` FROM ((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on(`V`.`id` = `VI`.`header_id`)) left join `erp_finance_allocation_items` `AI` on(`AI`.`voucher_item_id` = `VI`.`id`)) WHERE `V`.`is_posted` = 'Y' AND `VI`.`amount` > 0 AND `VI`.`ledger_id` = 11 AND `VI`.`subledger_id` = 619 GROUP BY `VI`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `view_all_stock_items`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_all_stock_items`  AS SELECT `s`.`id` AS `id`, `s`.`part_number` AS `part_number`, `s`.`description` AS `description`, `b`.`product_class` AS `brand`, `b`.`id` AS `brand_id`, `s`.`generic_id` AS `generic_id`, `u`.`symbol` AS `unit`, `s`.`isActive` AS `isActive`, `s`.`type` AS `type`, `s`.`rate` AS `rate`, (select ifnull(sum(`isl`.`quantity`),0) from `erp_stock_ledger` `isl` where `isl`.`product_id` = `s`.`id` and `isl`.`transaction_type` = 'IN') AS `in_qty`, (select ifnull(sum(`osl`.`quantity`),0) from `erp_stock_ledger` `osl` where `osl`.`product_id` = `s`.`id` and `osl`.`transaction_type` = 'OUT') AS `out_qty`, CASE WHEN `s`.`type` = 2 THEN 0 ELSE ifnull(sum(`sl`.`quantity`),0) END AS `quantity`, CASE WHEN `s`.`type` = 'S' THEN 0 ELSE ifnull(sum(`a`.`quantity`),0) END AS `alloc_qty`, `t`.`percentage` AS `tax_code`, `g`.`generic_name` AS `generic_name`, `s`.`have_serial_no` AS `have_serial_no`, `sl`.`stock_updated` AS `stock_updated` FROM ((((((`erp_inventory` `s` left join `erp_allocated_materials` `a` on(`s`.`id` = `a`.`product_id`)) left join `erp_product_class` `b` on(`s`.`brand` = `b`.`id`)) left join `erp_unit` `u` on(`s`.`unit` = `u`.`id`)) left join `erp_mas_inv_tax` `t` on(`t`.`id` = `s`.`tax_code`)) left join `erp_inventory_generic_name` `g` on(`g`.`id` = `s`.`generic_id`)) left join `erp_stock_ledger` `sl` on(`s`.`id` = `sl`.`product_id` and `sl`.`stock_updated` = 'Y')) GROUP BY `s`.`id` ORDER BY `s`.`isActive` ASC, `s`.`part_number` ASC ;

-- --------------------------------------------------------

--
-- Structure for view `view_gi_storage_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_gi_storage_charges`  AS SELECT `I`.`transaction_id` AS `transaction_id`, `I`.`product_id` AS `product_id`, coalesce(`S`.`part_number`,'') AS `part_number`, coalesce(`I`.`description`,'') AS `description`, coalesce(`I`.`quantity`,0) AS `quantity`, coalesce(`M`.`carton_name`,'') AS `carton_name`, coalesce(`I`.`no_of_cartons`,0) AS `no_of_cartons`, coalesce(`I`.`cbm_per_carton`,0) AS `cbm_per_carton`, coalesce(`I`.`volume`,0) AS `total_cbm`, coalesce(`H`.`storage_charges_per_cbm`,0) AS `storage_charges_per_cbm`, coalesce(`I`.`volume`,0) * coalesce(`H`.`storage_charges_per_cbm`,0) AS `total_storage_charges`, `H`.`transaction_date` AS `transaction_date`, `H`.`issue_no` AS `transaction_no`, `H`.`customer_id` AS `customer_id`, coalesce(`C`.`customer_name`,'') AS `customer_name` FROM ((((`erp_packaging_items` `I` left join `erp_goods_issue` `H` on(`H`.`id` = `I`.`transaction_id`)) left join `erp_inventory` `S` on(`S`.`id` = `I`.`product_id`)) left join `erp_mas_cartons` `M` on(`M`.`id` = `I`.`carton_id`)) left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) WHERE `H`.`state` = 'COMPLETED' ;

-- --------------------------------------------------------

--
-- Structure for view `view_gr_handling_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_gr_handling_charges`  AS SELECT `H`.`id` AS `receipt_id`, `H`.`transaction_date` AS `transaction_date`, `H`.`receipt_no` AS `receipt_no`, `H`.`customer_id` AS `customer_id`, coalesce(`C`.`customer_name`,'') AS `customer_name`, coalesce(`H`.`total_cbm`,0) AS `total_cbm`, coalesce(`H`.`ceil_total_cbm`,0) AS `ceil_total_cbm`, coalesce(`H`.`handling_charges_per_cbm`,0) AS `handling_charges_per_cbm`, coalesce(`H`.`ceil_total_cbm`,0) * coalesce(`H`.`handling_charges_per_cbm`,0) AS `total_handling_charges` FROM (`erp_goods_receipt` `H` left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) WHERE `H`.`state` = 'COMPLETED' ;

-- --------------------------------------------------------

--
-- Structure for view `view_gr_storage_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_gr_storage_charges`  AS SELECT `I`.`header_id` AS `transaction_id`, `I`.`product_id` AS `product_id`, coalesce(`S`.`part_number`,'') AS `part_number`, coalesce(`I`.`description`,'') AS `description`, coalesce(`I`.`quantity`,0) AS `quantity`, coalesce(`M`.`carton_name`,'') AS `carton_name`, coalesce(`I`.`no_of_cartons`,0) AS `no_of_cartons`, coalesce(`I`.`cbm_per_carton`,0) AS `cbm_per_carton`, coalesce(`I`.`cbm`,0) AS `total_cbm`, coalesce(`I`.`storage_charges_per_cbm`,0) AS `storage_charges_per_cbm`, coalesce(`I`.`cbm`,0) * coalesce(`I`.`storage_charges_per_cbm`,0) AS `total_storage_charges`, `H`.`transaction_date` AS `transaction_date`, `H`.`receipt_no` AS `transaction_no`, `H`.`customer_id` AS `customer_id`, coalesce(`C`.`customer_name`,'') AS `customer_name` FROM ((((`erp_goods_receipt_items` `I` left join `erp_goods_receipt` `H` on(`H`.`id` = `I`.`header_id`)) left join `erp_inventory` `S` on(`S`.`id` = `I`.`product_id`)) left join `erp_mas_cartons` `M` on(`M`.`id` = `I`.`carton_id`)) left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) WHERE `H`.`state` = 'COMPLETED' ;

-- --------------------------------------------------------

--
-- Structure for view `view_handling_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_handling_charges`  AS SELECT 'GR' AS `transaction_type`, `H`.`id` AS `transaction_id`, `H`.`transaction_date` AS `transaction_date`, `H`.`receipt_no` AS `transaction_no`, `H`.`customer_id` AS `customer_id`, coalesce(`C`.`customer_name`,'') AS `customer_name`, coalesce(`H`.`total_cbm`,0) AS `total_cbm`, coalesce(`H`.`ceil_total_cbm`,0) AS `ceil_total_cbm`, coalesce(`H`.`handling_charges_per_cbm`,0) AS `handling_charges_per_cbm`, coalesce(`H`.`ceil_total_cbm`,0) * coalesce(`H`.`handling_charges_per_cbm`,0) AS `total_handling_charges` FROM (`erp_goods_receipt` `H` left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) WHERE `H`.`state` = 'COMPLETED'union all select 'GI' AS `transaction_type`,`H`.`id` AS `transaction_id`,`H`.`transaction_date` AS `transaction_date`,`H`.`issue_no` AS `transaction_no`,`H`.`customer_id` AS `customer_id`,coalesce(`C`.`customer_name`,'') AS `customer_name`,coalesce(`H`.`total_cbm`,0) AS `total_cbm`,coalesce(`H`.`ceil_total_cbm`,0) AS `ceil_total_cbm`,coalesce(`H`.`handling_charges_per_cbm`,0) AS `handling_charges_per_cbm`,coalesce(`H`.`ceil_total_cbm`,0) * coalesce(`H`.`handling_charges_per_cbm`,0) AS `total_handling_charges` from (`erp_goods_issue` `H` left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) where `H`.`state` = 'COMPLETED' order by `transaction_date`  ;

-- --------------------------------------------------------

--
-- Structure for view `view_inventory_detailed_items_in_marshalling_area_report`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_inventory_detailed_items_in_marshalling_area_report`  AS SELECT `P`.`id` AS `package_id`, `P`.`transaction_id` AS `transaction_id`, `PI`.`transaction_item_id` AS `transaction_item_id`, `PI`.`product_id` AS `product_id`, `INV`.`part_number` AS `part_number`, `PI`.`description` AS `description`, `PI`.`quantity` AS `quantity`, `PI`.`unit` AS `unit`, `U`.`unit_name` AS `unit_name`, `PI`.`rate` AS `rate`, `PI`.`rate`* `PI`.`quantity` AS `value`, `PI`.`weight` AS `weight`, `PI`.`volume` AS `cbm`, `PI`.`bill_of_entry_exit` AS `bill_of_entry_exit`, `PI`.`warehouse_id` AS `warehouse_id`, `W`.`warehouse_name` AS `warehouse_name`, `PI`.`rack_id` AS `rack_id`, `R`.`name` AS `rack_name`, `PI`.`bin_id` AS `bin_id`, `B`.`name` AS `bin_name` FROM (((((((`erp_packaging_items` `PI` left join `erp_packaging` `P` on(`PI`.`package_id` = `P`.`id`)) left join `erp_customer` `C` on(`C`.`id` = `P`.`customer_id`)) left join `erp_inventory` `INV` on(`INV`.`id` = `PI`.`product_id`)) left join `erp_mas_unit` `U` on(`U`.`id` = `PI`.`unit`)) left join `erp_mas_inv_warehouse` `W` on(`W`.`id` = `PI`.`warehouse_id`)) left join `erp_mas_inv_rack` `R` on(`R`.`id` = `PI`.`rack_id`)) left join `erp_mas_inv_bin` `B` on(`B`.`id` = `PI`.`bin_id`)) WHERE `P`.`active` = 'Y' AND `P`.`transaction_type` in ('GI','GR') ORDER BY `PI`.`id` DESC ;

-- --------------------------------------------------------

--
-- Structure for view `view_inventory_summary_items_in_marshalling_area_report`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_inventory_summary_items_in_marshalling_area_report`  AS SELECT `P`.`id` AS `package_id`, `P`.`transaction_type` AS `transaction_type`, `P`.`transaction_date` AS `transaction_date`, `P`.`transaction_id` AS `transaction_id`, sum(`PI`.`quantity`) AS `total_qty`, sum(`PI`.`rate`) AS `total_rate`, sum(`PI`.`rate` * `PI`.`quantity`) AS `total_value`, sum(`PI`.`volume`) AS `total_cbm`, `C`.`customer_name` AS `customer_name`, `P`.`customer_id` AS `customer_id` FROM ((`erp_packaging` `P` left join `erp_packaging_items` `PI` on(`PI`.`package_id` = `P`.`id`)) left join `erp_customer` `C` on(`C`.`id` = `P`.`customer_id`)) WHERE `P`.`active` = 'Y' AND `P`.`transaction_type` in ('GI','GR') GROUP BY `P`.`id` ORDER BY `P`.`id` DESC ;

-- --------------------------------------------------------

--
-- Structure for view `view_packaging_list`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_packaging_list`  AS SELECT `dn`.`id` AS `transaction_id`, CASE WHEN `dn`.`file_id` > 0 THEN `dn`.`file_id` ELSE 'N/A' END AS `file_id`, `dn`.`delivery_date` AS `transaction_date`, `dn`.`customer_id` AS `customer_id`, CASE WHEN `dn`.`customer_id` > 0 THEN `customer`.`customer_name` ELSE 'N/A' END AS `customer_name`, 'Delivery Note' AS `packing_frm`, 'D' AS `status` FROM (`erp_delivery_note` `dn` join `erp_customer` `customer` on(`customer`.`id` = `dn`.`customer_id`)) WHERE `dn`.`ready_to_invoice` = 'N' AND `dn`.`status` = 'PN' ;

-- --------------------------------------------------------

--
-- Structure for view `view_stock_ledger_details`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_stock_ledger_details`  AS   (select `S`.`id` AS `id`,`b`.`name` AS `bin_name`,`r`.`name` AS `rack_name`,`w`.`warehouse_name` AS `warehouse_name`,`S`.`transaction_date` AS `transaction_date`,`S`.`transaction_type` AS `transaction_type`,`S`.`type` AS `type`,`S`.`customer_vendor_id` AS `customer_vendor_id`,`S`.`file_id` AS `file_id`,`S`.`header_id` AS `header_id`,`S`.`item_id` AS `item_id`,`S`.`product_id` AS `product_id`,`S`.`brand_id` AS `brand_id`,`S`.`rate` AS `rate`,`S`.`currency` AS `currency`,`S`.`exchange_rate` AS `exchange_rate`,`S`.`quantity` AS `quantity`,`S`.`unit` AS `unit`,`S`.`warehouse_id` AS `warehouse_id`,`S`.`rack_id` AS `rack_id`,`S`.`bin_id` AS `bin_id`,`S`.`stock_updated` AS `stock_updated` from (((`erp_stock_ledger` `S` left join `erp_mas_inv_warehouse` `w` on(`w`.`id` = `S`.`warehouse_id`)) left join `erp_mas_inv_rack` `r` on(`r`.`id` = `S`.`rack_id`)) left join `erp_mas_inv_bin` `b` on(`b`.`id` = `S`.`bin_id`)) where `S`.`transaction_type` = 'IN') union all (select `S`.`id` AS `id`,`b`.`name` AS `bin_name`,`r`.`name` AS `rack_name`,`w`.`warehouse_name` AS `warehouse_name`,`S`.`transaction_date` AS `transaction_date`,`S`.`transaction_type` AS `transaction_type`,`S`.`type` AS `type`,`S`.`customer_vendor_id` AS `customer_vendor_id`,`S`.`file_id` AS `file_id`,`S`.`header_id` AS `header_id`,`S`.`item_id` AS `item_id`,`S`.`product_id` AS `product_id`,`S`.`brand_id` AS `brand_id`,`S`.`rate` AS `rate`,`S`.`currency` AS `currency`,`S`.`exchange_rate` AS `exchange_rate`,-1 * `S`.`quantity` AS `quantity`,`S`.`unit` AS `unit`,`S`.`warehouse_id` AS `warehouse_id`,`S`.`rack_id` AS `rack_id`,`S`.`bin_id` AS `bin_id`,`S`.`stock_updated` AS `stock_updated` from (((`erp_stock_ledger` `S` left join `erp_mas_inv_warehouse` `w` on(`w`.`id` = `S`.`warehouse_id`)) left join `erp_mas_inv_rack` `r` on(`r`.`id` = `S`.`rack_id`)) left join `erp_mas_inv_bin` `b` on(`b`.`id` = `S`.`bin_id`)) where `S`.`transaction_type` = 'OUT')  ;

-- --------------------------------------------------------

--
-- Structure for view `view_stock_ledger_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_stock_ledger_summary`  AS SELECT `erp_stock_ledger`.`id` AS `id`, `erp_stock_ledger`.`customer_vendor_id` AS `customer_vendor_id`, `erp_stock_ledger`.`header_id` AS `header_id`, `erp_stock_ledger`.`transaction_date` AS `transaction_date`, `erp_stock_ledger`.`transaction_type` AS `transaction_type`, `erp_stock_ledger`.`type` AS `transaction_from`, `cus`.`customer_name` AS `customer_vendor_name`, `erp_stock_ledger`.`product_id` AS `product_id`, `erp_stock_ledger`.`quantity` AS `quantity`, `erp_stock_ledger`.`unit` AS `unit_id`, `erp_stock_ledger`.`warehouse_id` AS `warehouse_id`, `erp_stock_ledger`.`rack_id` AS `rack_id`, `erp_stock_ledger`.`bin_id` AS `bin_id`, `erp_stock_ledger`.`created_by` AS `created_by`, `erp_stock_ledger`.`created_time` AS `created_date`, sum(`erp_stock_ledger`.`quantity`) AS `summary_qty` FROM (`erp_stock_ledger` left join `erp_customer` `cus` on(`erp_stock_ledger`.`customer_vendor_id` = `cus`.`id`)) GROUP BY `erp_stock_ledger`.`transaction_type`, `erp_stock_ledger`.`header_id`, `erp_stock_ledger`.`product_id` ;

-- --------------------------------------------------------

--
-- Structure for view `view_stock_state_as_of`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics_freight`@`localhost` SQL SECURITY DEFINER VIEW `view_stock_state_as_of`  AS SELECT `S`.`customer_id` AS `customer_id`, `customer`.`customer_name` AS `customer_name`, `S`.`id` AS `product_id`, `S`.`part_number` AS `part_number`, `S`.`description` AS `description`, `B`.`product_class` AS `brand`, `G`.`generic_name` AS `generic_name`, (select ifnull(sum(`isl`.`quantity`),0) from `erp_stock_ledger` `isl` where `isl`.`product_id` = `S`.`id` and `isl`.`transaction_type` = 'IN' and `isl`.`transaction_date` <= '2025-03-06') AS `in_qty`, (select ifnull(sum(`osl`.`quantity`),0) from `erp_stock_ledger` `osl` where `osl`.`product_id` = `S`.`id` and `osl`.`transaction_type` = 'OUT' and `osl`.`transaction_date` <= '2025-03-06') AS `out_qty`, `S`.`brand` AS `brand_id`, `S`.`generic_id` AS `generic_id`, `S`.`rate` AS `rate` FROM (((`erp_inventory` `S` left join `erp_product_class` `B` on(`S`.`brand` = `B`.`id`)) left join `erp_inventory_generic_name` `G` on(`S`.`generic_id` = `G`.`id`)) left join `erp_customer` `customer` on(`customer`.`id` = `S`.`customer_id`)) WHERE `S`.`type` = 'I' GROUP BY `S`.`id` ORDER BY `S`.`part_number` ASC ;

-- --------------------------------------------------------

--
-- Structure for view `view_storage_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bluelogistics`@`localhost` SQL SECURITY DEFINER VIEW `view_storage_charges`  AS SELECT 'GR' AS `transaction_type`, `I`.`header_id` AS `transaction_id`, `I`.`product_id` AS `product_id`, coalesce(`S`.`part_number`,'') AS `part_number`, coalesce(`I`.`description`,'') AS `description`, coalesce(`I`.`quantity`,0) AS `quantity`, coalesce(`M`.`carton_name`,'') AS `carton_name`, coalesce(`I`.`no_of_cartons`,0) AS `no_of_cartons`, coalesce(`I`.`cbm_per_carton`,0) AS `cbm_per_carton`, coalesce(`I`.`cbm`,0) AS `total_cbm`, coalesce(`I`.`storage_charges_per_cbm`,0) AS `storage_charges_per_cbm`, coalesce(`I`.`cbm`,0) * coalesce(`I`.`storage_charges_per_cbm`,0) AS `total_storage_charges`, `H`.`transaction_date` AS `transaction_date`, `H`.`receipt_no` AS `transaction_no`, `H`.`customer_id` AS `customer_id`, coalesce(`C`.`customer_name`,'') AS `customer_name` FROM ((((`erp_goods_receipt_items` `I` left join `erp_goods_receipt` `H` on(`H`.`id` = `I`.`header_id`)) left join `erp_inventory` `S` on(`S`.`id` = `I`.`product_id`)) left join `erp_mas_cartons` `M` on(`M`.`id` = `I`.`carton_id`)) left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) WHERE `H`.`state` = 'COMPLETED'union all select 'GI' AS `transaction_type`,`I`.`transaction_id` AS `transaction_id`,`I`.`product_id` AS `product_id`,coalesce(`S`.`part_number`,'') AS `part_number`,coalesce(`I`.`description`,'') AS `description`,coalesce(`I`.`quantity`,0) AS `quantity`,coalesce(`M`.`carton_name`,'') AS `carton_name`,coalesce(`I`.`no_of_cartons`,0) AS `no_of_cartons`,coalesce(`I`.`cbm_per_carton`,0) AS `cbm_per_carton`,-1 * coalesce(`I`.`volume`,0) AS `total_cbm`,coalesce(`H`.`storage_charges_per_cbm`,0) AS `storage_charges_per_cbm`,-1 * coalesce(`I`.`volume`,0) * coalesce(`H`.`storage_charges_per_cbm`,0) AS `total_storage_charges`,`H`.`transaction_date` AS `transaction_date`,`H`.`issue_no` AS `transaction_no`,`H`.`customer_id` AS `customer_id`,coalesce(`C`.`customer_name`,'') AS `customer_name` from ((((`erp_packaging_items` `I` left join `erp_goods_issue` `H` on(`H`.`id` = `I`.`transaction_id`)) left join `erp_inventory` `S` on(`S`.`id` = `I`.`product_id`)) left join `erp_mas_cartons` `M` on(`M`.`id` = `I`.`carton_id`)) left join `erp_customer` `C` on(`C`.`id` = `H`.`customer_id`)) where `H`.`state` = 'COMPLETED' order by `transaction_date`  ;
COMMIT;

