As the title says. I wrote a little during the lunch break.
Use PostgreSQL for Azure VM instance.
$ cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
$ psql -V
psql (PostgreSQL) 9.2.23
I really wanted to write it in Python, but with the current module configuration I had to play with the environment. Since it is a production system, I gave it up and created it with only shell scripts (bash) and standard commands.
bash
#!/bin/bash
YESTERDAY=`date --date '1 day ago' "+%Y%m%d"`
SQL_FILE=/tmp/dakoku.sql
MAIL_TEXT=/tmp/mail.txt
#SQL statement/Created in tmp
cat << EOL > $SQL_FILE
SELECT d.syaincd as employee number, e.syainmei as employee name, d.torokudt as stamped date and time,
CASE dakokusyubetucd
WHEN '1' THEN 'Attendance'
WHEN '2' THEN 'Leave work'
ELSE 'unknown'
END as type
FROM dakoku_table d
LEFT JOIN employee_table e ON d.syaincd = e.syaincd
WHERE dakokuymd = '$YESTERDAY'
ORDER by d.torokudt, d.syaincd;
EOL
#Email header etc./Created in tmp
cat << EOL > $MAIL_TEXT
To: [email protected]
Cc: [email protected]
Subject: DAILY TIME RECORD ($YESTERDAY)
This email is automatically sent from the system.
We will inform you of the attendance time stamp log.
EOL
#Add SQL execution result to email body
su - postgres -c "psql KINTAI_DB < $SQL_FILE" >> $MAIL_TEXT
#Send email
/sbin/sendmail -t < $MAIL_TEXT
Email sent
This email is automatically sent from the system.
We will inform you of the attendance time stamp log.
employee number|Employee name|Stamping date and time|Type
------------+--------------+-------------------------+------
539258 |Yamada Taro| 2020-07-17 07:47:05.314 |Attendance
265167 |Suzuki Hanako| 2020-07-17 07:52:18.736 |Attendance
329665 |Ichiro Tanaka| 2020-07-17 08:03:57.356 |Attendance
Register with root's crontab and automatically execute at 0:05 am. The Human Resources and General Affairs Department can now check for omissions in attendance on the previous day.
It's a shameful code, but I hope it helps someone!
Recommended Posts