ALTER TABLE `erp_hr_salary_slip` ADD `remarks_1` TEXT NOT NULL AFTER `remarks`; 
ALTER TABLE `erp_hr_salary_slip` ADD `sanctioned_loan_amount` DECIMAL(10,2) NOT NULL AFTER `overtime_amount`; 
ALTER TABLE `erp_hr_mas_company_wps_details` ADD `company_address` TEXT NOT NULL AFTER `company_name`, ADD `company_logo` VARCHAR(700) NOT NULL AFTER `company_address`; 
ALTER TABLE `erp_hr_mas_company_wps_details` ADD `phone_number` VARCHAR(100) NOT NULL AFTER `company_address`, ADD `website` VARCHAR(700) NOT NULL AFTER `phone_number`; 
ALTER TABLE `erp_hr_employee_loan_detail` ADD `proposed_installment` INT NOT NULL AFTER `loan_repayment`, ADD `proposed_monthly_installment` DECIMAL(10,2) NOT NULL AFTER `proposed_installment`, ADD `installment_start_date` DATE NOT NULL AFTER `proposed_monthly_installment`;
ALTER TABLE `erp_hr_employee_loan_detail` ADD `lm_view_status` TINYINT NOT NULL DEFAULT '0' AFTER `remarks`; 
ALTER TABLE `erp_hr_employee_loan_detail` ADD `lm_id` INT NOT NULL AFTER `remarks`, ADD `lm_remarks` TEXT NOT NULL AFTER `lm_id`; 
ALTER TABLE `erp_hr_employee_loan_detail` ADD `loan_reason` TEXT NOT NULL AFTER `payment_date`; 
ALTER TABLE `erp_hr_employee_loan_detail` ADD `lm_approved_date` DATE NOT NULL AFTER `lm_id`; 
ALTER TABLE `erp_hr_employee_salary_advance` ADD `lm_id` INT NOT NULL AFTER `advance_payment_status`, ADD `lm_approved_date` DATE NOT NULL AFTER `lm_id`, ADD `lm_remarks` TEXT NOT NULL AFTER `lm_approved_date`, ADD `lm_view_status` TINYINT NOT NULL AFTER `lm_remarks`; 
ALTER TABLE `erp_hr_employee_salary_advance` ADD `proposed_return_date` DATE NOT NULL AFTER `apply_date`; 

CREATE OR REPLACE VIEW `view_payment_details` AS SELECT
    `l`.`employee_id` AS `employee_id`,
    `e`.`first_name` AS `first_name`,
    `e`.`last_name` AS `last_name`,
    `e`.`employee_code` AS `employee_code`,
    `l`.`id` AS `id`,
    `l`.`approved_date` AS `approved_date`,
    `l`.`loan_amount` AS `amount`,
    `l`.`loan_status` AS `STATUS`,
    'Loan' AS `TYPE`
FROM
    (
        `erp_hr_employee_loan_detail` `l`
    JOIN `erp_hr_employee` `e` ON
        ((`l`.`employee_id` = `e`.`id`))
    )
WHERE
    (
        (`l`.`loan_posted` = 'N') AND(`l`.`loan_status` = 1) AND(`l`.`payment_mode`!= 4)
    )
UNION
SELECT
    `s`.`employee_id` AS `employee_id`,
    `t`.`first_name` AS `first_name`,
    `t`.`last_name` AS `last_name`,
    `t`.`employee_code` AS `employee_code`,
    `s`.`id` AS `id`,
    `s`.`approved_date` AS `approved_date`,
    `s`.`amount` AS `amount`,
    `s`.`advance_payment_status` AS `STATUS`,
    'Salary Advance' AS `TYPE`
FROM
    (
        `erp_hr_employee_salary_advance` `s`
    JOIN `erp_hr_employee` `t` ON
        ((`s`.`employee_id` = `t`.`id`))
    )
WHERE
    (
        (`s`.`posted` = 'N') AND(`s`.`advance_payment_status` = 1)
    )
UNION
SELECT
    `b`.`employee_id` AS `employee_id`,
    `u`.`first_name` AS `first_name`,
    `u`.`last_name` AS `last_name`,
    `u`.`employee_code` AS `employee_code`,
    `b`.`id` AS `id`,
    `b`.`approve_date` AS `approved_date`,
    `b`.`encashment_amount` AS `amount`,
    `b`.`status` AS `STATUS`,
    'Leave Encashment' AS `TYPE`
FROM
    (
        `erp_hr_employee_leave_encashment` `b`
    JOIN `erp_hr_employee` `u` ON
        ((`b`.`employee_id` = `u`.`id`))
    )
WHERE
    (
        (`b`.`finance_posted` = 'N') AND(
            (`b`.`status` = 2) OR(`b`.`status` = 4)
        )
    )
UNION
SELECT
    `c`.`employee_id` AS `employee_id`,
    `v`.`first_name` AS `first_name`,
    `v`.`last_name` AS `last_name`,
    `v`.`employee_code` AS `employee_code`,
    `c`.`id` AS `id`,
    `c`.`approve_date` AS `approved_date`,
    `c`.`total_amount` AS `amount`,
    `c`.`status` AS `STATUS`,
    'End of Service' AS `TYPE`
FROM
    (
        `erp_hr_employee_eos` `c`
    JOIN `erp_hr_employee` `v` ON
        ((`c`.`employee_id` = `v`.`id`))
    )
WHERE
    (
        (`c`.`finance_posted` = 'N') AND(
            (`c`.`status` = 2) OR(`c`.`status` = 4)
        )
    )
UNION
SELECT
    `d`.`employee_id` AS `employee_id`,
    `w`.`first_name` AS `first_name`,
    `w`.`last_name` AS `last_name`,
    `w`.`employee_code` AS `employee_code`,
    `d`.`id` AS `id`,
    `d`.`date_approved` AS `approved_date`,
    `d`.`settlement_amount` AS `amount`,
    `d`.`status` AS `STATUS`,
    'Leave Settlement' AS `TYPE`
FROM
    (
        `erp_hr_employee_leavesettlement` `d`
    JOIN `erp_hr_employee` `w` ON
        ((`d`.`employee_id` = `w`.`id`))
    )
WHERE
    (
        (`d`.`posted` = 'N') AND(
            (`d`.`status` = 2) OR(`d`.`status` = 4)
        )
    )
UNION
SELECT
    `f`.`employee_id` AS `employee_id`,
    `x`.`first_name` AS `first_name`,
    `x`.`last_name` AS `last_name`,
    `x`.`employee_code` AS `employee_code`,
    `f`.`id` AS `id`,
    `f`.`HR_approval_date` AS `approved_date`,
    `f`.`total` AS `amount`,
    `f`.`status` AS `STATUS`,
    'Expense' AS `TYPE`
FROM
    (
        `erp_hr_employee_expense` `f`
    JOIN `erp_hr_employee` `x` ON
        ((`f`.`employee_id` = `x`.`id`))
    )
WHERE
    (
        (`f`.`finance_posted` = 'N') AND(
            (`f`.`status` = 4) OR(`f`.`status` = 5)
        )
    )
UNION
SELECT
    `g`.`employee_id` AS `employee_id`,
    `y`.`first_name` AS `first_name`,
    `y`.`last_name` AS `last_name`,
    `y`.`employee_code` AS `employee_code`,
    `g`.`id` AS `id`,
    `g`.`approve_date` AS `approved_date`,
    `g`.`ticket_amt` AS `amount`,
    `g`.`status` AS `STATUS`,
    'Ticket Encashment' AS `TYPE`
FROM
    (
        `erp_hr_employee_ticket_encashment` `g`
    JOIN `erp_hr_employee` `y` ON
        ((`g`.`employee_id` = `y`.`id`))
    )
WHERE
    (
        (`g`.`finance_posted` = 'N') AND(
            (`g`.`status` = 2) OR(`g`.`status` = 4)
        )
    )


ALTER TABLE `erp_hr_employee_loan_detail` ADD `salary_processing_id` INT NOT NULL AFTER `payment_mode`; 
