Friday, December 28, 2018

Complete Angular 5 tutorial link


Important Link:
  1. https://blog.codewithdan.com/10-angular-and-typescript-projects-to-take-you-from-zero-to-hero/ (********)
  2. http://jasonwatmore.com/post/2016/09/29/angular-2-user-registration-and-login-example-tutorial
  3. http://bennadel.github.io/JavaScript-Demos/demos/router-lazy-load-modules-indicator-angular6/#/app/(aside:aside) (*** lazy loading loder)
  4. https://www.bennadel.com/blog/3505-showing-a-loading-indicator-for-lazy-loaded-route-modules-in-angular-6-1-7.htm (*** lazy loading loder)
- No comments:
Labels:

Monday, December 17, 2018

Mysql scheduler and different if else condition -- done in bjit so posting porject


DROP EVENT IF EXISTS bank_payment_pending_notification_scheduler;
DELIMITER //
CREATE EVENT bank_payment_pending_notification_scheduler
    ON SCHEDULE EVERY 1 HOUR
    STARTS CURRENT_TIMESTAMP
    DO BEGIN
        DECLARE v_order_no VARCHAR(255);
        DECLARE v_customer_id VARCHAR(255);
        DECLARE v_first_name VARCHAR(255);
        DECLARE v_last_name VARCHAR(255);
        DECLARE v_cursor_end INT DEFAULT 0;

        DECLARE v_sender_id VARCHAR(255) DEFAULT '';
        DECLARE v_recipient_id VARCHAR(255) DEFAULT '';
        DECLARE v_title TEXT DEFAULT '';
        DECLARE v_body TEXT DEFAULT '';

        DECLARE notification_list_cursor CURSOR FOR
            SELECT
                orders.order_no, orders.customer_id, orders.first_name, orders.last_name
            FROM orders LEFT JOIN (
                            SELECT DISTINCT attribute->"$.order_no" AS order_no
FROM notifications
WHERE attribute->"$.notification_created_for_status" = 'bank_payment_pending'
                                AND type_of_notification = 'Internal'
                        ) as already_notify ON already_notify.order_no = orders.order_no
            WHERE
                payment_status = 'pending'
                AND payment_method = 'Bank Transfer'
                AND order_date < NOW() - INTERVAL 2 DAY
AND already_notify.order_no is NULL;
                
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cursor_end=1;
        
        OPEN notification_list_cursor;
            notification_loop: LOOP
                FETCH notification_list_cursor INTO v_order_no,v_customer_id, v_first_name,v_last_name;
                IF v_cursor_end THEN
                    LEAVE notification_loop;
                END IF;
                
                INSERT INTO notifications(sender_id, recipient_id, title_html, body_html, type_of_notification, attribute, is_read, created_at)
                VALUES ('System',
                        v_customer_id,
                        CONCAT('Bank payment pending for order no: ',v_order_no),
                        CONCAT('Bank payment pending for order no: ',v_order_no),
                        'Internal',
                        CONCAT('{"email_send": 1,"order_no":"',v_order_no,'", "notification_created_for_status":"bank_payment_pending"}'),
                        0 , now() );                

            END LOOP notification_loop;
        CLOSE notification_list_cursor;
        SET v_cursor_end=0;
    END; //
DELIMITER ;


-- SET v_recipient_id = v_customer_id;
-- SET v_sender_id = 'zia.uddin@bjitgroup.com';
-- SET v_title = CONCAT('Bank payment pending for order no: ',v_order_no);
-- SET v_body = CONCAT('Bank payment pending for order no: ',v_order_no);

-- CALL mysql.lambda_async('arn:aws:lambda:us-east-1:637895362620:function:spw_send_email_notification',
--  CONCAT('{"recipient_id" : "',v_recipient_id,'","sender_id" : "',v_sender_id,'","title" : "',v_title,'","body" : "',v_body,'"}')
-- );


-- SELECT
-- order_no, customer_id, first_name, last_name
-- FROM orders
-- WHERE
-- payment_status = 'pending'
-- AND payment_method = 'Bank Transfer'
-- AND order_date < NOW() - INTERVAL 2 DAY
-- AND order_no NOT IN (SELECT DISTINCT attribute->"$.order_no" AS order_no FROM notifications WHERE attribute->"$.order_no" IS NOT NULL );

-- INSERT INTO notifications(sender_id, recipient_id, title_html, body_html, type_of_notification, attribute, created_at) VALUES ('System',v_customer_id, '','', 'Email', CONCAT('{"is_notify": 1,"order_no":"',v_order_no,'"}'), now() );
-- CALL mysql.lambda_async('arn:aws:lambda:eu-west-1:637895362620:function:spwSendEmailNotificationForPendingPayment',
-- CONCAT('{"order_no" : "',v_order_no,'","customer_id" : "',v_customer_id,'","first_name" : "',v_first_name,'","last_name" : "',v_last_name,'"}')
-- );


---------------------------------------------------------------------------------------------------
-- end_of_mission   
--- run daily   

-- testing
-- ON SCHEDULE EVERY 1 MINUTE
-- STARTS CURRENT_TIMESTAMP
-- ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

-- exact time schedule
-- ON SCHEDULE EVERY 1 DAY
-- STARTS CURRENT_TIMESTAMP

DROP EVENT IF EXISTS end_of_mission_notification_scheduler;
DELIMITER //
    CREATE EVENT end_of_mission_notification_scheduler
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
        
    DO BEGIN
    
        DECLARE v_order_no VARCHAR(255);
        DECLARE v_mission_ref_no VARCHAR(255);
        DECLARE v_customer_id VARCHAR(255);
DECLARE v_child_customer_id VARCHAR(255);
DECLARE v_temp_recepient_id VARCHAR(255);
        DECLARE v_end_date VARCHAR(255);

        DECLARE v_cursor_end INT DEFAULT 0;

        DECLARE notification_list_cursor CURSOR FOR
                SELECT
                    order_no, missions.mission_ref_no,missions.customer_id, missions.child_customer_id,(JSON_EXTRACT(`mission_data`, '$.end_date')) end_date
                FROM
                    missions LEFT JOIN (
                            SELECT DISTINCT
                                attribute->"$.mission_ref_no" AS mission_ref_no,
                                attribute->"$.customer_id" AS customer_id
                            FROM notifications
                            WHERE attribute->"$.notification_created_for_status" = 'end_of_mission'
                                AND type_of_notification = 'Internal'
                        ) as already_notify on already_notify.mission_ref_no = missions.mission_ref_no AND already_notify.customer_id = missions.customer_id
                WHERE
                    STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') <= NOW() + INTERVAL 7 DAY
                    AND STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') >= NOW()
                    AND already_notify.mission_ref_no is NULL;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cursor_end=1;
        
        OPEN notification_list_cursor;
            notification_loop: LOOP
                FETCH notification_list_cursor INTO v_order_no,v_mission_ref_no, v_customer_id, v_child_customer_id, v_end_date;
                IF v_cursor_end THEN
                    LEAVE notification_loop;
                END IF;

SET v_temp_recepient_id = v_customer_id;
IF v_child_customer_id IS NOT NULL AND v_child_customer_id != '' THEN
                    SET v_temp_recepient_id = v_child_customer_id;
                END IF;

                INSERT INTO notifications(sender_id, recipient_id, title_html, body_html, type_of_notification, attribute, is_read, created_at)
                VALUES ('System',
                        v_temp_recepient_id,
                        CONCAT('The end date of missions ',v_mission_ref_no, ' is ',SPLIT_STR( JSON_UNQUOTE(v_end_date), 'T', 1)),
                        CONCAT('The end date of missions ',v_mission_ref_no, ' is ', SPLIT_STR( JSON_UNQUOTE(v_end_date), 'T', 1)),
                        'Internal',
                        CONCAT('{"email_send":0,"mission_ref_no":"',v_mission_ref_no,'", "customer_id":"',v_customer_id,'", "notification_created_for_status":"end_of_mission" }'),
                        0,
                        now() );

            END LOOP notification_loop;
        CLOSE notification_list_cursor;
        SET v_cursor_end=0;
    END; //
DELIMITER ;

-------------------------------------------------------------------------------
    
-- run every month  
-- ON SCHEDULE EVERY 1 MINUTE
--      STARTS CURRENT_TIMESTAMP
--      ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

-- ON SCHEDULE EVERY 1 DAY
--      STARTS CURRENT_TIMESTAMP
--DAILY 
DROP EVENT IF EXISTS sipsi_doc_not_yet_uploaded_notification_scheduler;
DELIMITER //
    CREATE EVENT sipsi_doc_not_yet_uploaded_notification_scheduler
ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_TIMESTAMP
        
    DO BEGIN
        CALL mysql.lambda_async(get_notification_configs_bykey('spw_sipsi_doc_not_yet_uploaded_notification_api_arn'),CONCAT('{"backoffice_service_url" : "',get_notification_configs_bykey('backoffice_service_url'),'"}'));
        
    END; //
DELIMITER ;

-----sql query for sipsi doc not uploaded---


-- SElECT
-- vmil.customer_id,vmil.mission_ref_no,
-- vmil.mission_data->'$.start_date' start_date,
-- vmil.mission_data->'$.end_date' end_date,
-- CONCAT(mempd.first_name,' ', mempd.last_name) emp_name,
-- vmil.mission_data->'$.number_of_workers' number_of_workers,
-- mempd.doc_upload_status->"$.Once" once_file_status
-- FROM
-- view_mission_list AS vmil
-- left join mission_employee_details AS mempd
-- on vmil.customer_id = mempd.customer_id AND vmil.mission_ref_no = mempd.mission_ref_no
-- WHERE
-- STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date')), 'T', 1),'%Y-%m-%d') = '2018-12-27'
-- and vmil.payment_status = 'success';




-- SElECT
-- vmil.customer_id,vmil.mission_ref_no,
-- vmil.mission_data->'$.start_date' start_date,
-- vmil.mission_data->'$.end_date' end_date,
-- CONCAT(mempd.first_name,' ', mempd.last_name) emp_name,
-- vmil.mission_data->'$.number_of_workers' number_of_workers,
-- mempd.doc_upload_status->"$.m_1218" monthly_file_status
-- FROM
-- view_mission_list AS vmil
-- left join mission_employee_details AS mempd
-- on vmil.customer_id = mempd.customer_id AND vmil.mission_ref_no = mempd.mission_ref_no
-- WHERE
-- (
-- (STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date')), 'T', 1),'%Y-%m-%d') >= '2018-12-01' AND STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') <= '2018-12-31')
-- OR
-- (STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date')), 'T', 1),'%Y-%m-%d') <= '2018-12-28' AND STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') >= '2018-12-29')
-- OR
-- (STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date')), 'T', 1),'%Y-%m-%d') >= '2018-12-01' AND STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date')), 'T', 1),'%Y-%m-%d') <= '2018-12-31')
-- OR
-- (STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') >= '2018-12-01' AND STR_TO_DATE( SPLIT_STR( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date')), 'T', 1),'%Y-%m-%d') <= '2018-12-31')
-- )
-- and vmil.payment_status = 'success';
    

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


-- end_of_mission email notification
--- run hourly

-- ON SCHEDULE EVERY 1 MINUTE
-- STARTS CURRENT_TIMESTAMP
-- ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR


DROP EVENT IF EXISTS email_notification_scheduler;
DELIMITER //

    CREATE EVENT email_notification_scheduler
ON SCHEDULE EVERY 1 HOUR
    STARTS CURRENT_TIMESTAMP
        
    DO BEGIN
    
        DECLARE v_id INT(11);
        DECLARE v_sender_id VARCHAR(255) DEFAULT '';
        DECLARE v_recipient_id VARCHAR(255) DEFAULT '';
        DECLARE v_title TEXT DEFAULT '';
        DECLARE v_body TEXT DEFAULT '';

        DECLARE v_notification_created_for_status varchar(255);

        DECLARE v_cursor_end INT DEFAULT 0;

        DECLARE notification_list_cursor CURSOR FOR
                SELECT
                    notifications.id, notifications.sender_id, notifications.recipient_id,
                        JSON_UNQUOTE(attribute->"$.notification_created_for_status") AS notification_created_for_status                     
                FROM
                    notifications
                WHERE
                    created_at <= NOW() - INTERVAL 1 DAY
                    AND is_read = 0
                    AND JSON_EXTRACT(`attribute`, '$.email_send') = 0
                    AND type_of_notification = 'Internal'   
                    AND (JSON_UNQUOTE(JSON_EXTRACT(`attribute`, '$.notification_created_for_status')) = 'end_of_mission'
OR JSON_UNQUOTE(JSON_EXTRACT(`attribute`, '$.notification_created_for_status')) = 'sipsi_doc_not_yet_uploaded');

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_cursor_end=1;
        
        OPEN notification_list_cursor;
            notification_loop: LOOP
                FETCH notification_list_cursor INTO v_id,v_sender_id, v_recipient_id, v_notification_created_for_status;
                IF v_cursor_end THEN
                    LEAVE notification_loop;
                END IF;
                
                UPDATE notifications SET `attribute` = JSON_SET(`attribute` ,'$.email_send' ,1) WHERE id = v_id;
                
                SET v_recipient_id = v_recipient_id;
                SET v_sender_id = get_notification_configs_bykey('sender_email_id');
                SET v_title = CONCAT('You have a unread message/alert in last 24 hours, please log into your account.');
                SET v_body = CONCAT('You have a unread message/alert in last 24 hours, please log into your account.');

                CALL mysql.lambda_async(get_notification_configs_bykey('spw_send_email_notification_lambda_fun_arn'),
                    CONCAT('{"recipient_id" : "',v_recipient_id,'","sender_id" : "',v_sender_id,'","title" : "',v_title,'","body" : "',v_body,'"}')
                );
                        
            END LOOP notification_loop;
        CLOSE notification_list_cursor;
        SET v_cursor_end=0;
    END; //

DELIMITER ;

-- INTERVAL 1 DAY
--SET v_recipient_id = 'tushar.ghosh@bjitgroup.com';




---------------------------------------TRIGGER-------------------------------------------

DROP TRIGGER IF EXISTS send_email_notification_trigger;
DELIMITER //

CREATE TRIGGER send_email_notification_trigger
AFTER INSERT
ON notifications FOR EACH ROW

BEGIN

DECLARE v_sender_id VARCHAR(255) DEFAULT '';
DECLARE v_recipient_id VARCHAR(255) DEFAULT '';
DECLARE v_title TEXT DEFAULT '';
DECLARE v_body TEXT DEFAULT '';

SET v_recipient_id = NEW.recipient_id;
IF NEW.sender_id = 'System' THEN
SET v_sender_id = get_notification_configs_bykey('sender_email_id');
ELSE
SET v_sender_id = NEW.sender_id;
END IF;
SET v_title = CONCAT('You have an new message/alert, please log into your account. ');
SET v_body = CONCAT('You have an new message/alert, please log into your account.');

CALL mysql.lambda_async(get_notification_configs_bykey('spw_send_email_notification_lambda_fun_arn'),
CONCAT('{"recipient_id" : "',v_recipient_id,'","sender_id" : "',v_sender_id,'","title" : "',v_title,'","body" : "',v_body,'"}')
);

END; //

DELIMITER ;

-- INSERT INTO notifications(sender_id, recipient_id, title_html, body_html, type_of_notification, attribute, is_read, created_at)
-- VALUES ('System',
-- 'tushar.ghosh@bjitgroup.com',
-- CONCAT('Bank payment pending for order no:testing '),
-- CONCAT('Bank payment pending for order no: '),
-- 'Internal',
-- NULL,
-- 0 , now() );


---------------- -------- configs table-----------------------

DROP TABLE IF EXISTS notification_configs;
CREATE TABLE notification_configs (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
config_key VARCHAR(255) NOT NULL UNIQUE,
config_value VARCHAR(255) NOT NULL
)
insert into notification_configs (config_key,config_value)
values('spw_send_email_notification_lambda_fun_arn', 'arn:aws:lambda:us-east-1:637895362620:function:spw_send_email_notification');

insert into notification_configs (config_key,config_value)
values('spw_sipsi_doc_not_yet_uploaded_notification_api_arn', 'arn:aws:lambda:us-east-1:637895362620:function:spw_sipsi_doc_not_yet_uploaded_notification_api');

insert into notification_configs (config_key,config_value)
values('backoffice_service_url', 'http://development.spwsystem-stage.com:3001');

insert into notification_configs (config_key,config_value)
values('sender_email_id', 'zia.uddin@bjitgroup.com');



-------------------- function --------------------------
----get_notification_configs_bykey-----
DROP FUNCTION IF EXISTS get_notification_configs_bykey;
DELIMITER //

CREATE FUNCTION get_notification_configs_bykey(p_config_key VARCHAR(255)) RETURNS VARCHAR(1000)
BEGIN

DECLARE value_found VARCHAR(1000) DEFAULT '';

SELECT config_value INTO value_found FROM notification_configs WHERE config_key = p_config_key;
RETURN value_found;
END; //

DELIMITER ;

------------------- SPLIT_STR --------
DROP FUNCTION IF EXISTS SPLIT_STR;
DELIMITER //

CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12),pos INT ) RETURNS VARCHAR(255)
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, "");
END; //

DELIMITER ;
- No comments:
Labels: ,
Subscribe to: Comments (Atom)

Autoboxing and Unboxing

  Autoboxing  is the automatic conversion that the Java compiler makes between the primitive types and their corresponding object wrapper cl...

  • new Date(); manupulation in JS
    let today = new Date (); let dd = String ( today . getDate ()). padStart ( 2 , '0' ); let m...
  • HostBinding, HostListener
    A quick tip that helps me remember what they do - HostBinding('value') myValue;  is exactly the same as  [value]="myValue...
  • VPC peering
      What is VPC Take a small piece of AWS network and call it your own, control IP address ranges, routing patterns, tighten security etc. AWS...