C# Programming
06-28-2012, 04:30 PM
Hi,
I need to explain this abit....
I am using MySQL awith C# to develop Windows Form application..
I have the following tables:
CREATE TABLE `salary_slip_category` ( `salary_slip_category_id` int(11) NOT NULL AUTO_INCREMENT, `salary_slip_category_name` varchar(255) DEFAULT NULL, `salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL, `salary_slip_category_default_description` varchar(255) DEFAULT NULL, `salary_slip_category_order` int(11) DEFAULT NULL, PRIMARY KEY (`salary_slip_category_id`), KEY `idx_salary_slip_category_salary_slip_category_name` (`salary_slip_category_name`), KEY `idx_salary_slip_category_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`), KEY `idx_salary_slip_category_salary_slip_category_default_descriptio` (`salary_slip_category_default_description`), KEY `idx_salary_slip_category_salary_slip_category_order` (`salary_slip_category_order`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
CREATE TABLE `salary_slip_details` ( `salary_slip_details_id` int(11) NOT NULL AUTO_INCREMENT, `employee_number` int(11) DEFAULT NULL, `is_benefit` bit(1) DEFAULT b'0', `salary_slip_details_order` int(11) DEFAULT '999', `trxn_code` varchar(50) DEFAULT NULL, `salary_slip_id` int(11) DEFAULT NULL, `salary_slip_code` int(11) DEFAULT NULL, `salary_slip_month` int(11) DEFAULT NULL, `salary_slip_year` int(11) DEFAULT NULL, `salary_slip_details_category` varchar(255) DEFAULT NULL, `salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL, `salary_slip_details_description` varchar(255) DEFAULT NULL, `salary_slip_details_amount` double(10,3) DEFAULT NULL, `salary_slip_details_released` bit(1) DEFAULT b'0', PRIMARY KEY (`salary_slip_details_id`), KEY `idx_salary_slip_details_employee_number` (`employee_number`), KEY `idx_salary_slip_details_is_benefit` (`is_benefit`), KEY `idx_salary_slip_details_salary_slip_details_order` (`salary_slip_details_order`), KEY `idx_salary_slip_details_trxn_code` (`trxn_code`), KEY `idx_salary_slip_details_salary_slip_id` (`salary_slip_id`), KEY `idx_salary_slip_details_salary_slip_code` (`salary_slip_code`), KEY `idx_salary_slip_details_salary_slip_month` (`salary_slip_month`), KEY `idx_salary_slip_details_salary_slip_year` (`salary_slip_year`), KEY `idx_salary_slip_details_salary_slip_details_category` (`salary_slip_details_category`), KEY `idx_salary_slip_details_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`), KEY `idx_salary_slip_details_salary_slip_details_description` (`salary_slip_details_description`), KEY `idx_salary_slip_details_salary_slip_details_amount` (`salary_slip_details_amount`), KEY `idx_salary_slip_details_salary_slip_details_released` (`salary_slip_details_released`)) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8;
CREATE TABLE `employee` ( `employee_number` int(11) NOT NULL AUTO_INCREMENT, `id_number` varchar(255) DEFAULT NULL, `employee_name` varchar(255) DEFAULT NULL, `employee_gender` varchar(11) DEFAULT NULL, `employee_designation` varchar(255) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `employee_email` varchar(255) DEFAULT NULL, `employee_mobile` varchar(255) DEFAULT NULL, `employee_home_tel` varchar(255) DEFAULT NULL, `date_of_join` date DEFAULT NULL, `last_working_date` date DEFAULT NULL, `bank_name` varchar(255) DEFAULT NULL, `bank_account` varchar(50) DEFAULT NULL, `current_employee` bit(1) DEFAULT b'1', `is_active` bit(1) DEFAULT b'1', `login_password` varchar(255) DEFAULT NULL, `login_pin` varchar(4) DEFAULT NULL, `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`employee_number`), KEY `idx_employee_id_number` (`id_number`), KEY `idx_employee_employee_name` (`employee_name`), KEY `idx_employee_employee_gender` (`employee_gender`), KEY `idx_employee_employee_designation` (`employee_designation`), KEY `idx_employee_date_of_birth` (`date_of_birth`), KEY `idx_employee_employee_email` (`employee_email`), KEY `idx_employee_employee_mobile` (`employee_mobile`), KEY `idx_employee_employee_home_tel` (`employee_home_tel`), KEY `idx_employee_date_of_join` (`date_of_join`), KEY `idx_employee_last_working_date` (`last_working_date`), KEY `idx_employee_bank_name` (`bank_name`), KEY `idx_employee_bank_account` (`bank_account`), KEY `idx_employee_current_employee` (`current_employee`), KEY `idx_employee_s_active` (`is_active`), KEY `idx_employee_login_password` (`login_password`), KEY `idx_employee_login_pin` (`login_pin`)) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
I am using the salary_slip_details to view the salary details, linking it with employee_number in the employee table and also linking it to the salary_slip_category to view it by category
I want to know how can I make the result showing like this:
1. every employee will have one row. 2. Categories will be shown as columns and total amount of the category will be shown there
so if the employee has 2 record of decution (category) 10 dollar each the in the deduction column it will show 20$ for that employee
kindly help....
I need to explain this abit....
I am using MySQL awith C# to develop Windows Form application..
I have the following tables:
CREATE TABLE `salary_slip_category` ( `salary_slip_category_id` int(11) NOT NULL AUTO_INCREMENT, `salary_slip_category_name` varchar(255) DEFAULT NULL, `salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL, `salary_slip_category_default_description` varchar(255) DEFAULT NULL, `salary_slip_category_order` int(11) DEFAULT NULL, PRIMARY KEY (`salary_slip_category_id`), KEY `idx_salary_slip_category_salary_slip_category_name` (`salary_slip_category_name`), KEY `idx_salary_slip_category_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`), KEY `idx_salary_slip_category_salary_slip_category_default_descriptio` (`salary_slip_category_default_description`), KEY `idx_salary_slip_category_salary_slip_category_order` (`salary_slip_category_order`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
CREATE TABLE `salary_slip_details` ( `salary_slip_details_id` int(11) NOT NULL AUTO_INCREMENT, `employee_number` int(11) DEFAULT NULL, `is_benefit` bit(1) DEFAULT b'0', `salary_slip_details_order` int(11) DEFAULT '999', `trxn_code` varchar(50) DEFAULT NULL, `salary_slip_id` int(11) DEFAULT NULL, `salary_slip_code` int(11) DEFAULT NULL, `salary_slip_month` int(11) DEFAULT NULL, `salary_slip_year` int(11) DEFAULT NULL, `salary_slip_details_category` varchar(255) DEFAULT NULL, `salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL, `salary_slip_details_description` varchar(255) DEFAULT NULL, `salary_slip_details_amount` double(10,3) DEFAULT NULL, `salary_slip_details_released` bit(1) DEFAULT b'0', PRIMARY KEY (`salary_slip_details_id`), KEY `idx_salary_slip_details_employee_number` (`employee_number`), KEY `idx_salary_slip_details_is_benefit` (`is_benefit`), KEY `idx_salary_slip_details_salary_slip_details_order` (`salary_slip_details_order`), KEY `idx_salary_slip_details_trxn_code` (`trxn_code`), KEY `idx_salary_slip_details_salary_slip_id` (`salary_slip_id`), KEY `idx_salary_slip_details_salary_slip_code` (`salary_slip_code`), KEY `idx_salary_slip_details_salary_slip_month` (`salary_slip_month`), KEY `idx_salary_slip_details_salary_slip_year` (`salary_slip_year`), KEY `idx_salary_slip_details_salary_slip_details_category` (`salary_slip_details_category`), KEY `idx_salary_slip_details_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`), KEY `idx_salary_slip_details_salary_slip_details_description` (`salary_slip_details_description`), KEY `idx_salary_slip_details_salary_slip_details_amount` (`salary_slip_details_amount`), KEY `idx_salary_slip_details_salary_slip_details_released` (`salary_slip_details_released`)) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8;
CREATE TABLE `employee` ( `employee_number` int(11) NOT NULL AUTO_INCREMENT, `id_number` varchar(255) DEFAULT NULL, `employee_name` varchar(255) DEFAULT NULL, `employee_gender` varchar(11) DEFAULT NULL, `employee_designation` varchar(255) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `employee_email` varchar(255) DEFAULT NULL, `employee_mobile` varchar(255) DEFAULT NULL, `employee_home_tel` varchar(255) DEFAULT NULL, `date_of_join` date DEFAULT NULL, `last_working_date` date DEFAULT NULL, `bank_name` varchar(255) DEFAULT NULL, `bank_account` varchar(50) DEFAULT NULL, `current_employee` bit(1) DEFAULT b'1', `is_active` bit(1) DEFAULT b'1', `login_password` varchar(255) DEFAULT NULL, `login_pin` varchar(4) DEFAULT NULL, `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`employee_number`), KEY `idx_employee_id_number` (`id_number`), KEY `idx_employee_employee_name` (`employee_name`), KEY `idx_employee_employee_gender` (`employee_gender`), KEY `idx_employee_employee_designation` (`employee_designation`), KEY `idx_employee_date_of_birth` (`date_of_birth`), KEY `idx_employee_employee_email` (`employee_email`), KEY `idx_employee_employee_mobile` (`employee_mobile`), KEY `idx_employee_employee_home_tel` (`employee_home_tel`), KEY `idx_employee_date_of_join` (`date_of_join`), KEY `idx_employee_last_working_date` (`last_working_date`), KEY `idx_employee_bank_name` (`bank_name`), KEY `idx_employee_bank_account` (`bank_account`), KEY `idx_employee_current_employee` (`current_employee`), KEY `idx_employee_s_active` (`is_active`), KEY `idx_employee_login_password` (`login_password`), KEY `idx_employee_login_pin` (`login_pin`)) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
I am using the salary_slip_details to view the salary details, linking it with employee_number in the employee table and also linking it to the salary_slip_category to view it by category
I want to know how can I make the result showing like this:
1. every employee will have one row. 2. Categories will be shown as columns and total amount of the category will be shown there
so if the employee has 2 record of decution (category) 10 dollar each the in the deduction column it will show 20$ for that employee
kindly help....