Program for salary report

Dear Experts,

I need a program to generate salary report for my courses. Here is the workflow:
I have a table which store all the courses information and the structure is as follows:
mysql> describe course;
| Field          | Type        | Null | Key | Default | Extra
+----------------+-------------+------+-----+---------+---------------
| id             | int(11)     | NO   | PRI | NULL    | auto_increment
| coursename     | varchar(10) | NO   |     |         |
| schoolname     | varchar(10) | NO   |     |         |
| schooladdress  | varchar(20) | NO   |     |         |
| schooltele     | varchar(10) | NO   |     |         |
| lesson1        | date        | NO   |     |         |
| lesson2        | date        | NO   |     |         |
| lesson3        | date        | NO   |     |         |
| lesson4        | date        | NO   |     |         |
| starttime      | time        | NO   |     |         |
| endtime        | time        | NO   |     |         |
| tutorname      | varchar(20) | YES  |     | NULL    |
| tutortele1     | varchar(20) | YES  |     | NULL    |
| tutortele2     | varchar(20) | YES  |     | NULL    |
| hrperlesson    | float | NO   |     |         |
| nooflesson     | varchar(10) | NO   |     |         |
| hrcharge       | varchar(10) | NO   |     |         |
| noofkid        | varchar(10) | YES  |     | NULL    |
| target         | varchar(10) | YES  |     | NULL    |
| chargeperhead  | varchar(10) | YES  |     | NULL    |
| materialcharge | varchar(10) | YES  |     | NULL    |
| lesson5        | date        | NO   |     |         |
| lesson6        | date        | NO   |     |         |
| lesson7        | date        | NO   |     |         |
| lesson8        | date        | NO   |     |         |
| lesson9        | date        | NO   |     |         |
| lesson10       | date        | NO   |     |         |
| lesson11       | date        | NO   |     |         |
| lesson12       | date        | NO   |     |         |
| lesson13       | date        | NO   |     |         |
| lesson14       | date        | NO   |     |         |
| lesson15       | date        | NO   |     |         |
| lesson16       | date        | NO   |     |         |
| lesson17       | date        | NO   |     |         |
| lesson18       | date        | NO   |     |         |
| lesson19       | date        | NO   |     |         |
| lesson20       | date        | NO   |     |         |
| lesson21       | date        | NO   |     |         |
| lesson22       | date        | NO   |     |         |
| lesson23       | date        | NO   |     |         |
| lesson24       | date        | NO   |     |         |
| noofleaveday   | int(11)      | NO   |     |         |

There are max 24 lessons but sometimes are not. So, it means sometimes the value of lessonXX is zero.

I need a program to help me to sort out all courses that the last day is within that month.
Then, the sorted result should be stored in a table as follow and some calculation is done.:
Salary200512_table and its structure is as follows:
mysql> describe salary200512;
+----------------+-------------+------+-----+---------+----------------
| Field          | Type        | Null | Key | Default | Extra
+----------------+-------------+------+-----+---------+----------------
| id             | int(11)     | NO   | PRI | NULL    | auto_increment
| courseid       | int(11)     | NO   |     |         |
| tutorname      | varchar(20) | NO   |     |         |
| schoolname     | varchar(10) | YES  |     | NULL    |
| totalday       | int(11)     | NO   |     |         |
| noofleaveday   | int(11)     | NO   |     |         |
| actualworkday  | int(11)     | NO   |     |         |
| hrperlesson    | float       | NO   |     |         |
| punishhr       | float       | NO   |     |         |
| materialcharge | float       | NO   |     |         |
| hrcharge       | int(11)     | NO   |     |         |
| discount       | float       | YES  |     | NULL    |
| salary         | float       | YES  |     | NULL    |
| tutorbank      | varchar(20) | YES  |     | NULL    |
| tutorbankid    | varchar(20) | YES  |     | NULL    |

Here is where data of field of salary200512 from:
Courseid            field id of table course
Tutorname      same name field of table course
schoolname       same name field of table course
totalday            it should be the no. of field that have data in field1 ~ field24. So, if the course have only 7 lesson and only 7 fields out of 24 fields has data. The data in totalday should be 7.
noofleaveday       same name field of table course
actualworkday            should be field “totalday”– field”noofleaveday”
hrperlesson       same name field of table course
totalworkhour       it should be value of
=actualworkday X hrperlesson
punishhr                  same name field of table course
materialcharge       same name field of table course
hrcharge                   same name field of table course
discount                  same name field of table course
salary            it should be value of
= [(actualworkhour – punishhr) X hrcharge] X discount + materialcharge
Tutorbank            it is stored in table “tutor_table”
Tutorbankid            it is stored in table “tutor_table”
The structure of tutor_table is as follows:
mysql> describe tutor_table;
+-------------+-------------+------+-----+---------+----------------
| Field       | Type        | Null | Key | Default | Extra
+-------------+-------------+------+-----+---------+----------------
| id          | int(11)     | NO   | PRI | NULL    | auto_increment
| tutorname   | varchar(20) | NO   |     |         |
| tutortele1  | varchar(10) | NO   |     |         |
| tutortele2  | varchar(10) | NO   |     |         |
| tutorbank   | varchar(10) | YES  |     | NULL    |
| tutorbankid | varchar(10) | YES  |     | NULL    |

Thanks


Start Free Trial