CREATE DEFINER=`root`@`%` PROCEDURE `wavin_db_dev`.`order_temporary_view`(IN `order_id` INT(11) , IN `action_data` VARCHAR(191), OUT status VARCHAR(191) )
BEGIN
        DECLARE done INT DEFAULT FALSE;


        DECLARE ds_status,ds_creator,ds_so_ref_number,ds_code ,ds_stores_code_parent ,ds_stores_name,ds_agent_name, ds_agent_email,ds_g_lat,ds_g_lng ,ds_stores_toko_name ,ds_stores_toko_email ,ds_stores_category ,ds_brand_name, ds_notes ,ds_internal_comment ,ds_stores_address,ds_stores_village_id ,ds_stores_district_id,ds_stores_city_id, ds_stores_province_id,ds_products_name,ds_products_sku VARCHAR(255);


        DECLARE ds_date, ds_so_ref_date VARCHAR(20);


        DECLARE ds_id, ds_salesman_id, ds_user_id_created, ds_user_id_updated, ds_order_reason_id, ds_order_detail_id, ds_order_detail_last_sent_qty, ds_stores_parent_id, ds_brand_id, ds_agent_id, ds_products_id, ds_categories_id, ds_is_detected INT;


        DECLARE ds_total_amount, ds_order_detail_price_at_time  DECIMAL(15,4) DEFAULT 0;


        DECLARE ds_created_at, ds_updated_at, ds_deleted_at, ds_order_detail_updated_at timestamp;



        DECLARE curdata CURSOR FOR select a.id, a.status, a.creator, a.so_ref_number, a.code, a.date, a.salesman_id, a.total_amount, a.notes , a.internal_comment, a.so_ref_date,a.g_lat,a.g_lng,a.created_at, a.updated_at, a.user_id_created, a.user_id_updated,  a.deleted_at, a.order_reason_id,od.id as order_detail_id, od.last_sent_qty,od.price_at_time,od.updated_at as updated_at_detail,f.store_code as store_code_parent, f.name as stores_name, f.address, f.village_id, f.district_id, f.city_id, f.province_id,f.stores_category , f.parent_id , f.name as TokoName, f.email as TokoEmail, br.id as BrandID, br.name as BrandName, h.id as AgentID,h.name as AgentName,h.email as AgentEmail, pr.id as products_id, pr.productName as products_name, pr.SKU as  products_sku, cat.id as  categories_id, IF(a.deleted_at IS NULL, 0 , 1) as deleted_at_order  FROM orders a LEFT JOIN stores f ON  a.store_id=f.id LEFT JOIN agents h ON a.agent_id = h.id LEFT JOIN order_detail od ON od.order_id = a.id LEFT JOIN product_agent pa ON pa.id = od.product_id LEFT JOIN products pr ON pr.id = pa.master_product_id LEFT JOIN categories cat ON cat.id = pr.category_id LEFT JOIN brands br ON cat.brand_id = br.id where a.id=order_id;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        IF NOT EXISTS (SELECT id FROM orders WHERE id=order_id) THEN
        set status = "No Data Order";
        ELSE
        IF action_data = "INSERT" THEN
        INSERT INTO order_detail_view_grid(id,status,creator,so_ref_number,code,date,salesman_id,total_amount,notes,internal_comment,so_ref_date,g_lat,g_lng,user_id_created,user_id_updated,created_at,updated_at,deleted_at,order_reason_id,order_detail_id,order_detail_last_sent_qty,order_detail_price_at_time,order_detail_updated_at,stores_code_parent,stores_name,stores_address,stores_village_id,stores_district_id,stores_city_id,stores_province_id,stores_category,stores_parent_id,stores_toko_name,stores_toko_email,brand_id,brand_name,agent_id,agent_name,agent_email, products_id, products_name, products_sku, categories_id, is_detected)
        select  a.id, a.status, a.creator, a.so_ref_number, a.code, a.date, a.salesman_id, a.total_amount, a.notes , a.internal_comment, a.so_ref_date,a.g_lat,a.g_lng, a.user_id_created, a.user_id_updated, a.created_at, a.updated_at,  a.deleted_at, a.order_reason_id,od.id as order_detail_id, od.last_sent_qty,od.price_at_time,od.updated_at as updated_at_detail,f.store_code as store_code_parent, f.name as stores_name, f.address, f.village_id, f.district_id, f.city_id, f.province_id,f.stores_category , f.parent_id , f.name as TokoName, f.email as TokoEmail, br.id as BrandID, br.name as BrandName, h.id as AgentID,h.name as AgentName,h.email as AgentEmail, pr.id as products_id,
        pr.productName as products_name,
        pr.SKU as  products_sku,
        cat.id as  categories_id,
        IF(a.deleted_at IS NULL, 0 , 1) as deleted_at_order
        FROM orders a
        LEFT JOIN stores f ON  a.store_id=f.id
        LEFT JOIN agents h ON a.agent_id = h.id
        LEFT JOIN order_detail od ON od.order_id = a.id
        LEFT JOIN product_agent pa ON pa.id = od.product_id
        LEFT JOIN products pr ON pr.id = pa.master_product_id
        LEFT JOIN categories cat ON cat.id = pr.category_id
        LEFT JOIN brands br ON cat.brand_id = br.id
        where a.id=order_id;
        set status = "Success Create";
        ELSE
        OPEN curdata;
        read_loop: LOOP
        FETCH curdata INTO ds_id,ds_status,ds_creator,ds_so_ref_number,ds_code,ds_date,ds_salesman_id,ds_total_amount,ds_notes,ds_internal_comment,ds_so_ref_date,ds_g_lat,ds_g_lng,ds_user_id_created,ds_user_id_updated,ds_created_at,ds_updated_at,ds_deleted_at,ds_order_reason_id,ds_order_detail_id,ds_order_detail_last_sent_qty,ds_order_detail_price_at_time,ds_order_detail_updated_at,ds_stores_code_parent,ds_stores_name,ds_stores_address,ds_stores_village_id,ds_stores_district_id,ds_stores_city_id,ds_stores_province_id,ds_stores_category,ds_stores_parent_id,ds_stores_toko_name,ds_stores_toko_email,ds_brand_id,ds_brand_name,ds_agent_id,ds_agent_name,ds_agent_email,ds_products_id,ds_products_name,ds_products_sku,ds_categories_id,ds_is_detected;
        IF done THEN
        LEAVE read_loop;
        END IF;
        UPDATE order_detail_view_grid
        SET
        status=ds_status,
        creator=ds_creator,
        so_ref_number=ds_so_ref_number,
        code=ds_code,
        date=ds_date,
        salesman_id=ds_salesman_id,
        total_amount=ds_total_amount,
        notes=ds_notes,
        internal_comment=ds_internal_comment,
        so_ref_date=ds_so_ref_date,
        g_lat=ds_g_lat,
        g_lng=ds_g_lng,
        user_id_created=ds_user_id_created,
        user_id_updated=ds_user_id_updated,
        created_at=ds_created_at,
        updated_at=ds_updated_at,
        deleted_at=ds_deleted_at,
        order_reason_id=ds_order_reason_id,
        order_detail_last_sent_qty=ds_order_detail_last_sent_qty,
        order_detail_price_at_time=ds_order_detail_price_at_time,
        order_detail_updated_at=ds_order_detail_updated_at,
        stores_code_parent=ds_stores_code_parent,
        stores_name=ds_stores_name,
        stores_address=ds_stores_address,
        stores_village_id=ds_stores_village_id,
        stores_district_id=ds_stores_district_id,
        stores_city_id=ds_stores_city_id,
        stores_province_id=ds_stores_province_id,
        stores_category=ds_stores_category,
        stores_parent_id=ds_stores_parent_id,
        stores_toko_name=ds_stores_toko_name,
        stores_toko_email=ds_stores_toko_email,
        brand_id=ds_brand_id,
        brand_name=ds_brand_name,
        agent_id=ds_agent_id,
        agent_name=ds_agent_name,
        agent_email=ds_agent_email,
        products_id=ds_products_id,
        products_name=ds_products_name,
        products_sku=ds_products_sku,
        categories_id=ds_categories_id,
        is_detected=ds_is_detected
        WHERE id=ds_id AND order_detail_id=ds_order_detail_id;
        END LOOP;
        CLOSE curdata;
        set status = "Success Update";
        END IF;
        END IF;
        SELECT status;
        END