🌷 Esparzo 🌷

Saturday, 16 January 2021

How to use comments in python

Single line comments
You can use # symbol for single line comments

For example,

#print function is used to output something
print ('Hello World')

Block Comments ( Triple quotes comments)
Three quotes comments are considered as block comments in python.

For example,

"""
Author: Dinesh Dontha
Component: Payment Processing
Licence: ----
"""
print('Start of the program')
#some processing
print('End of the program')

How to print in python

You can use print () function in python to output something in python.

For example:

print ('Hello World ')

You can also pass some argument into print function.

For example,

name = "Dinesh"

print (' Your are %s.', % name);

Tuesday, 15 September 2020

How to create CRON expressions & what are the components of a typical CRON expression? Usage of CRON expression in @Scheduled

CRON expressions are mainly used for scheduling tasks, which should run frequently at a fixed rate, lets say, running a task every day at mid night 12'0 Clock, running a task every hour & like wise.

For Example, An Online shopping cart application might have many schedulers that schedule the tasks, for instance, once an order is placed successfully by a customer, a shipping order scheduler might processes these successful orders placed in previous hour or before some time.

A typical CRON expression: "0/5 * * ? * *"

CRON expression basically consists of 7 components. Each component will have range of allowed values, Allowed special characters. For example, "Seconds" component will have range of values allowed from 0 to 59.

CRON expression components:

"Seconds" "Minutes" "Hours" "Day of month" "month" "Day of week" "Year"

All are mandatory parameters, except "Year".

"Seconds" - a mandatory parameter

     Range of values:         0-59

     Allowed characters     , - * /

Examples:

    10,20      executes at 10th second and 20th second

    *             executes at every second

    0/5          executes every 5 seconds starting from 0th second

"Minutes" - a mandatory parameter
     Range of values:         0-59
     Allowed characters     , - * /
Examples: 
    10,20      executes at 10th minute and 20th minute 
    *             executes at every minute
    0/5          executes every 5 minutes starting from 0th minute
"Hours" - a mandatory parameter
     Range of values:         0-23
     Allowed characters     , - * /
Examples: 
    10,20      executes at 10th hour and 20th hour 
    *             executes at every hour
    0/5          executes every 5 hours starting from mid night 12:00 AM
"Day of month" - a mandatory parameter
     Range of values:         1-31
     Allowed characters    , - * ? / L W C
Examples: 
    10,20      executes at 10th day and 20th day 
    *             executes at every day
    1/5          executes every 5 days starting from first day
    L represents the last day of the month. L is allowed only with "Day of month" 
    & "Day of week".
    L represents 31st in Jan, 28th in Feb (non-leap years)
    W represents the nearest week-day. Only allowed to use when it is a single day, 
    not allowed with range of values.
    12W 
    say if 12th day of the month is Saturday, it will trigger on 11th day (Friday), 
    if 12th day is Sunday, it will trigger on 13th day (Monday), 
    if 12th day is a Monday, as it is weekday, it will trigger on 12th day (Monday)
    C represents the calendar. Only allowed for "Day of month" & 
    "Day of week" components.
    Values are calculated against an associated calendar if any. 
    If no calendar associated, if it is declared like
    5C represents the first day included by the calendar on or after the 5th.
"Month" - a mandatory parameter
     Range of values:         0-11 or JAN-DEC
     Allowed characters    , - * /
Examples: 
    1,2                      executes at FEB & MAR months 
   FEB,MAR          executes at FEB & MAR months 
    *                         executes at every month
"Day of Week" - a mandatory parameter
     Range of values:         0-11 or JAN-DEC
     Allowed characters    , - * ? / L C #
  L represents the last day of the month. L is allowed only with "Day of month" 
    & "Day of week"
    1-7 (1 - sun, 2- mon, 3-tues, 4-wed, 5- thur, 6-fri, 7-sat)
    6L represents last Friday of the month.
    C represents the calendar. Only allowed for "Day of month" 
    & "Day of week" components.
    Values are calculated against an associated calendar if any. 
    If no calendar associated, if it is declared like
    1C represents the first day included by the calendar on or after Sunday.
    7C represents the first day included by the calendar on or after Saturday.
   # is used to represent a single day, like nth XXX day. 
    Only allowed for "Day of week".
   7#2 represents the 2nd Saturday of the month.
 "Year" - an optional parameter
     Range of values:         empty or 1970-2099
     Allowed characters    , - * /
Examples: 
    2000-2010         executes for range of years from 2000 to 2010
   2000,2006          executes for years 2000 and 2006
    *                         executes at every year
Examples with all components combined:
0 0 12 * * ? Fire at 12:00 PM (noon) every day
0 20 10 ? * * Fire at 10:20 AM every day
0 15 11 * * ? Fire at 11:15 AM every day
0 15 10 * * ? * Fire at 10:15 AM every day
0 20 20 * * ? 2010 Fire at 08:20 PM every day during the year 2010
0 * 14 * * ? Fire every minute starting at 2:00 PM and ending at 2:59 PM, every day
0 0/5 14 * * ? Fire every 5 minutes starting at 2:00 PM and ending at 2:55 PM, every day
0 0/5 14,18 * * ? Fire every 5 minutes starting at 2:00 PM and ending at 2:55 PM, 
AND fire every 5 minutes starting at 6:00 PM and ending at 6:55 PM, 
every day
0 0-5 16 * * ? Fire every minute starting at 4:00 PM and ending at 4:05 PM, every day
0 10,22 14 ? 3 FRI Fire at 2:10 PM and at 2:22 PM every Friday in the month of March
0 15 10 ? * MON-FRI Fire at 10:15 AM every Monday, Tuesday, Wednesday, Thursday and Friday
0 15 10 15 * ? Fire at 10:15 AM on the 15th day of every month
0 15 10 L * ? Fire at 10:15 AM on the last day of every month
0 15 10 ? * 7L Fire at 10:15 AM on the last Saturday of every month
0 15 10 ? * 7L Fire at 10:15 AM on the last Saturday of every month
0 15 10 ? * 7L 2002-2004 Fire at 10:15 AM on every last Saturday of every month 
during the years 2002, 2003 and 2004
0 15 10 ? * 6#3 Fire at 10:15 AM on the third Friday of every month
0 0 12 1/5 * ? Fire at 12 PM (noon) every 5 days every month, 
starting on the first day of the month
0 6 6 6 6 ? Fire every June 6 at 6:06 AM
How to use CRON expression in spring scheduling @Scheduled annotation?
As we know that, firstly, we need to enable scheduling using @EnableScheduling 
and use @Scheduled annotation on tasks.
Full example: Spring boot scheduling example
Example:
    @Scheduled(cron = "0/5 * * ? * *")
    public void reportCurrentTime() throws InterruptedException {
        //Thread.sleep(6000l);
        logger.info(formatter.format(new Date()));
    }

Wednesday, 26 August 2020

How to create a spring boot project easily using start.spring.io

Firstly, Spring Boot Project is easily created using start.spring.io

Step 1: visit start.spring.io

Step 2: choose the language, for instance, JAVA

Step 3: choose the spring boot version, for example, 2.3.3

Step 4: Enter the project metadata like

Group - com.xyz (xyz company keeps all its projects under this group, just like org.springframework, used in maven or gradle prokject build file)

Artifact - Patient-Management (used in the maven or gradle project artifact, creates jar/war with this name)

Name - Patient-Management (name can be same as Artifact, this is used internally folder name)

Description - enter some text about your project

Package Name - com.xyz.abc (creates a java package with this name)

Packaging - select either jar or war (Mostly, spring boot projects take jar, so default jar is selected)

Step 5: choose the suitable Java Version

Step 6: Search and add the required dependencies ( these dependencies are automatically added in your build.xml or pom.xml)

Step 7: Click on Generate button or key in (CTRL+ENTER)

Tuesday, 6 February 2018

HOW & WHEN "GROUP_CONCAT" GROUP FUNCTION IS USED ? - MYSQL

This article explains various scenarios where GROUP_CONCAT group function should be used.

Scenario: 1

Sometimes, we need some requirements like concatenating two or more rows with a delimiter.

Example:

Following table shows the data of all the customers of a Life Insurance Company, who paid the premiums of their selected policies.

Table_Name: Customer_Payments

Payment_No	Customer	Customer_Id	Payments	Policy_Type	Payment_date
00001	Ramesh	1	1000	01	2018-02-01 03:26:30
00002	Rajesh	2	10000	02	2018-02-01 03:27:00
00003	Ramesh	1	3000	02	2018-02-01 03:30:33
00004	Geeta	3	2500	01	2018-02-01 03:28:31

If your requirement is to find total amounts paid by the Customers along with list of policies holding by each & every customer. Here, the list of Policies holding by a Customer is showed with a delimiter comma.

Firstly, we need to group the Customer using Customer_Id

So,

SELECT Customer_Id, SUM(Payments) AS "Total Payment"
FROM Customer_Payments
GROUP BY Customer_Id;

Query Result:

Customer_Id	Total Payment
1	4000
2	10000
3	2500

The above query fetches the consolidated payments done by each Customer.

Now, you use GROUP_CONCAT(Policy_Type) in the SELECT clause to get the comma-separted list of Policies.

So,

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;

Query Result:

Customer_Id	Total Payment	List Of Policies
1	4000	01,02
2	10000	02
3	2500	01

Here, this means, Customer with Id 1, has 2 policies (01, 02) paid a total of 4000, Customer 2 has one policy (02) paid an amount 10000, & Customer 3 has one policy (01) paid an amount of 2500.

Note: By default, comma is the delimiter for GROUP_CONCAT group function.

You can choose your own delimiter for concatenation. This can be done using a SEPARATOR clause inside the GROUP_CONCAT group function.

Example:

SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type SEPARATOR '^^^') AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;

Query Result:

Customer_Id	Total Payment	List Of Policies
1	4000	01^^^02
2	10000	02
3	2500	01

The SEPARATOR should be positioned always last inside the GROUP_CONCAT. Otherwise, you will end up with errors.

Example:

SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC SEPARATOR '^^^')
FROM Customer_Payments
GROUP BY Customer_Id;

Query Result:

Customer_Id	Total Payment	List Of Policies
1	4000	02^^^01
2	10000	02
3	2500	01

ORDERING WITH GROUP_CONCAT:

Sometimes, we also want list of policies separated by comma-separated in a specfic order. To do that, you have to use ORDER BY clause inside the GROUP_CONCAT group function.

Example:

SELECT Customer_Id, SUM(Payments) AS "Total Payment",
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;

Now, observe the Query Results:

Customer_Id	Total Payment	List Of Policies
1	4000	02,01
2	10000	02
3	2500	01

For the first customer, having more than one policy, the policy numbers are now listed in DESCENDING order (02,01).

Scenario: 2

Lets consider that, we want the consolidated payments sorted by most recent payment: this can be done by sorting the data using Payment_Date

Example:

Table_Name: Customer_Payments

Payment_No	Customer	Customer_Id	Payments	Policy_Type	Payment_date
00001	Ramesh	1	1000	01	2018-02-01 03:26:30
00002	Rajesh	2	10000	02	2018-02-01 03:27:00
00003	Ramesh	1	3000	02	2018-02-01 03:30:33
00004	Geeta	3	2500	01	2018-02-01 03:28:31

Please observe how we can build this Query. As we want the consolidated & most recent payers. So, we need to GROUP BY Customer.

So,

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;

You need to add sorting logic to the query using the Column "Payment_date", but, as "Payment_date" is not in the GROUP BY clause, you can order by using the GROUP_CONCAT function in ORDER BY clause also.

So, before using GROUP_CONCAT with Payment_date column in ORDER_BY clause, lets see, how it fetches the data in SELECT clause.

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id;

Query Results:

Customer_Id	Total Payment	List Of Policies	Payment Date
1	4000	02,01	2018-02-01 03:26:30,2018-02-01 03:30:33
2	10000	2	2018-02-01 03:27:00
3	2500	1	2018-02-01 03:28:31

Now, you use GROUP_CONCAT in ORDER BY Clause:

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC);

Here, Overall Sorting Order: ASCENDING

Customer_Id	Total Payment	List Of policies	Payment Date
2	10000	02	2018-02-01 03:27:00
3	2500	01	2018-02-01 03:28:31
1	4000	02,01	2018-02-01 03:26:30,2018-02-01 03:30:33

Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER), Please observe the first Payment Date "2018-02-01 03:27:00". Whereas, In the group concatenated Payment Date, for instance, last row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, while the overall query sorting is in ASCENDING order.

Important Note:

There is much difference in these statements:

ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) - In the comma-separted list of payment dates 2018-02-01 03:30:33 is considered in the overall sorting.

ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) - In the comma-separted list of payment dates 2018-02-01 03:26:30 is considered in the overall sorting.

So,

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date);

Overall Sorting Order: ASCENDING

Observe the Query Results(especially the Payment Date)

Customer_Id	Total Payment	List Of policies	Payment Date
1	4000	02,01	2018-02-01 03:26:30,2018-02-01 03:30:33
2	10000	02	2018-02-01 03:27:00
3	2500	01	2018-02-01 03:28:31

Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:26:30 is considered, that is because GROUP concatenation done by Payment_dates & sorted in ASCENDING order, & the overall query sorting is also in ASCENDING order.

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) DESC;

Overall Sorting Order: DESCENDING

Query Results:

Customer_Id	Total Payment	List Of policies	Payment Date
1	4000	02,01	2018-02-01 03:26:30,2018-02-01 03:30:33
3	2500	01	2018-02-01 03:28:31
2	10000	02	2018-02-01 03:27:00

Here, the consolidated data is sorted in DESCENDING order (Overall Query Sorting is in DESCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, & the overall query sorting is also in DESCENDING order.

Now, change the Sorting Order in GROUP_CONCAT of Payment_date to ASCENDING without changing the Overall sorting order from DESCENDING.

SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) DESC;

Overall Sorting Order: DESCENDING

Query Results:

Customer_Id	Total Payment	List Of policies	Payment Date
3	2500	1	2018-02-01 03:28:31
2	10000	2	2018-02-01 03:27:00
1	4000	02,01	2018-02-01 03:26:30,2018-02-01 03:30:33

0 0 12 * * ?	Fire at 12:00 PM (noon) every day
0 20 10 ? * *	Fire at 10:20 AM every day
0 15 11 * * ?	Fire at 11:15 AM every day
0 15 10 * * ? *	Fire at 10:15 AM every day
0 20 20 * * ? 2010	Fire at 08:20 PM every day during the year 2010
0 * 14 * * ?	Fire every minute starting at 2:00 PM and ending at 2:59 PM, every day
0 0/5 14 * * ?	Fire every 5 minutes starting at 2:00 PM and ending at 2:55 PM, every day
0 0/5 14,18 * * ?	Fire every 5 minutes starting at 2:00 PM and ending at 2:55 PM, AND fire every 5 minutes starting at 6:00 PM and ending at 6:55 PM, every day
0 0-5 16 * * ?	Fire every minute starting at 4:00 PM and ending at 4:05 PM, every day
0 10,22 14 ? 3 FRI	Fire at 2:10 PM and at 2:22 PM every Friday in the month of March
0 15 10 ? * MON-FRI	Fire at 10:15 AM every Monday, Tuesday, Wednesday, Thursday and Friday
0 15 10 15 * ?	Fire at 10:15 AM on the 15th day of every month
0 15 10 L * ?	Fire at 10:15 AM on the last day of every month
0 15 10 ? * 7L	Fire at 10:15 AM on the last Saturday of every month
0 15 10 ? * 7L	Fire at 10:15 AM on the last Saturday of every month
0 15 10 ? * 7L 2002-2004	Fire at 10:15 AM on every last Saturday of every month during the years 2002, 2003 and 2004
0 15 10 ? * 6#3	Fire at 10:15 AM on the third Friday of every month
0 0 12 1/5 * ?	Fire at 12 PM (noon) every 5 days every month, starting on the first day of the month
0 6 6 6 6 ?	Fire every June 6 at 6:06 AM