Sheets, Email, PDF & Graphics or charts (NB: Is it a complex one)

This might be a hard one to go about people.

Goal of this is to obtain a PDF file which will be attached in a daily mail and a small summary of the PDF which will be the body of the mail sent daily to the corresponding people.

I want to receive mails from a specific source daily (I call this the Main E-mail).
Those mails will contain daily reports in a sheet format with every line generated based on the status of a door.

A door is supposed to be opened once every two hours during the day (06:00 am to 06:00pm) and once every hour at night (06:00pm to 06:00am)

From the data collected, a report is sent in a sheet format from the platform handling the data collection to the Main E-mail…

If a door is opened at a correponding time inerval (Example: Between 08:00am and 10:00am) that will send a value OPENED in a line with corresponding time of opening. if not opened nothing will be sent in sheet.

If the next time the door is opened is at 03:00pm it will register OPENED at that time and it will be the next line after that of the 08:00am and 10:00am interval previously opened regardless of the other intervals before that time.

Also to be noted is that at a time interval several openings can be done.
But all i want is to use just one value amongst many in a specific time interval to generate my document.

Based on the way the sheet is sent I want to

1- Check if at every interval day or night the door has been opened.
If it has been opened, in another sheet with specified intervals for day and night, it will Specify OK highlighted in green for available data or NOT OK highlighted in red if no Data availabale at corresponding intervals.

The document where everything is highlighted is split into two parts in same page (See attached Image if description not clear)

Part 1: Intron date and time interval and Logos
Part 2: Day table with status of door opening.
at bottom of table % of openings during the day
To its right will be a pie chart summarising % of openings during the day
Part 3: Night opening status of door.
at bottom of table % of opeings dring the night
To its right will be a pie chart summarising % of openings during the night
To its right will be a pie chart summarising % of openings during the day
Part 4: Date and time of document Generated

Finaly a PDF will have to be sent once everyday to emails corresponding to the Site

NB: There are several sites with various names
They have to be handled seperately and reports sent eperately too.

Hope the reqest is clear and someone can Help

So whats the question here @theashBoy ? Seems very specific use-case, do you want someone to build this for you as a job?