/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.6.41-84.1 : Database - goodluck_erp
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`goodluck_erp` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `goodluck_erp`;

/* Procedure structure for procedure `customer_ledger` */

/*!50003 DROP PROCEDURE IF EXISTS  `customer_ledger` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `customer_ledger`(

	IN customer INT,strat_date DATE,end_date DATE
    )
BEGIN
	SELECT * FROM
	(
	SELECT `customer_id`,`sales`.`date` AS `date`,`invoice_code` AS `code`,`total_amount` AS amount ,'sales' AS table_name,sales.`created_at` FROM `sales`
	UNION ALL
	SELECT sales.customer_id,`sale_payments`.`payment_date` AS `date`,`collection_ref_no` AS `code`,SUM(`payment_amount`) AS amount, 'collection' AS table_name,sale_payments.`created_at`  FROM `sale_payments`
	INNER JOIN `sales` ON (`sales`.`id`=`sale_payments`.`sale_id`)
	 GROUP BY `collection_ref_no`
	) a
	WHERE a.`customer_id` = customer AND (a.`date` BETWEEN strat_date AND end_date) ORDER BY a.`created_at`,FIELD(a.table_name, "sales", "collection") ASC;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `customer_wise_invoice_due` */

/*!50003 DROP PROCEDURE IF EXISTS  `customer_wise_invoice_due` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `customer_wise_invoice_due`(
	IN customer_id INT
    )
BEGIN
	SELECT `sales`.`id`,`customer_id`,`invoice_code`,`total_amount` AS invoice_amount, SUM(COALESCE(payment_amount, 0)) AS payment_amount , (`total_amount`-  SUM(COALESCE(payment_amount, 0))) AS due_amount FROM `sales` LEFT JOIN `sale_payments` ON (`sales`.`id`=`sale_payments`.`sale_id`)
	WHERE sales.customer_id=customer_id AND `payment_status` IN (0,2) GROUP BY `invoice_code` ORDER BY (`total_amount`-  SUM(COALESCE(payment_amount, 0)));
    END */$$
DELIMITER ;

/* Procedure structure for procedure `customer_wise_total_due` */

/*!50003 DROP PROCEDURE IF EXISTS  `customer_wise_total_due` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `customer_wise_total_due`(
	IN customer INT,start_date DATE,end_date DATE
    )
BEGIN
         SELECT `customer_id`,SUM(`total_amount`) AS invoice_amount, SUM(COALESCE(payment_amount, 0)) AS payment_amount , (SUM(`total_amount`)-  SUM(COALESCE(payment_amount, 0))) AS due_amount FROM `sales` LEFT JOIN `sale_payments` ON (`sales`.`id`=`sale_payments`.`sale_id`)
	WHERE sales.customer_id=customer AND `payment_status` IN (0,2) AND (sales.`date` <= start_date);
    END */$$
DELIMITER ;

/* Procedure structure for procedure `invoices_cost_profit` */

/*!50003 DROP PROCEDURE IF EXISTS  `invoices_cost_profit` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `invoices_cost_profit`(
	  IN from_date DATE,
          IN to_date DATE
    )
BEGIN
        SELECT `date`,`invoice_code`,`customers`.`name` AS customer_name,SUM(`product_cost`*sale_details.`quantity`) AS product_cost,`total_amount` AS sell_amount,total_amount-SUM(`product_cost`*sale_details.`quantity`) AS profit FROM `sales`
        INNER JOIN `sale_details` ON (`sales`.`id`=`sale_details`.`sale_id`)
	INNER JOIN `products` ON (sale_details.`product_id`=`products`.`id`)
	INNER JOIN `customers` ON (sales.`customer_id`=customers.`id`)
	WHERE (`date` BETWEEN from_date AND to_date) GROUP BY `invoice_code` ORDER BY `date`,invoice_code DESC;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_calculateEmployeeLeaveBalance` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_calculateEmployeeLeaveBalance` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_calculateEmployeeLeaveBalance`(
IN employeeId INT(10),IN leaveTypeId INT(10)
    )
BEGIN
   SELECT SUM(number_of_day) AS totalNumberOfDays FROM leave_application WHERE employee_id=employeeId AND leave_type_id=leaveTypeId AND STATUS = 2
          AND (approve_date  BETWEEN DATE_FORMAT(NOW(),'%Y-01-01') AND DATE_FORMAT(NOW(),'%Y-12-31'));
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_DailyAttendance` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_DailyAttendance` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_DailyAttendance`(
 IN input_date DATE
    )
BEGIN

SELECT employee.employee_id,employee.photo,CONCAT(COALESCE(employee.first_name,''),' ',COALESCE(employee.last_name,'')) AS fullName,department_name,
                        view_employee_in_out_data.employee_attendance_id,view_employee_in_out_data.finger_print_id,view_employee_in_out_data.date,view_employee_in_out_data.working_time,
                        DATE_FORMAT(view_employee_in_out_data.in_time,'%h:%i %p') AS in_time,DATE_FORMAT(view_employee_in_out_data.out_time,'%h:%i %p') AS out_time,
		TIME_FORMAT( work_shift.late_count_time, '%H:%i:%s' ) AS lateCountTime,
	(SELECT CASE WHEN DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:00')  > lateCountTime
            THEN 'Yes'
            ELSE 'No' END) AS  ifLate,

            (SELECT CASE WHEN TIMEDIFF((DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:%s')),work_shift.late_count_time)  > '0'
            THEN TIMEDIFF((DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:%s')),work_shift.late_count_time)
            ELSE '00:00:00' END) AS  totalLateTime,
             TIMEDIFF((DATE_FORMAT(work_shift.`end_time`,'%H:%i:%s')),work_shift.`start_time`) AS workingHour
                        FROM employee
                        INNER JOIN view_employee_in_out_data ON view_employee_in_out_data.finger_print_id = employee.finger_id
                        INNER JOIN department ON department.department_id = employee.department_id
JOIN work_shift ON work_shift.work_shift_id = employee.work_shift_id
                        WHERE `status`=1 AND `date`=input_date GROUP BY view_employee_in_out_data.finger_print_id ORDER BY employee_attendance_id DESC;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_getEmployeeInfo` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_getEmployeeInfo` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_getEmployeeInfo`(
IN employeeId INT(10)
    )
BEGIN
	       SELECT employee.*,user.`user_name` FROM employee
            INNER JOIN `user` ON `user`.`user_id` = employee.`user_id`
            WHERE employee_id = employeeId;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_getHoliday` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_getHoliday` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_getHoliday`(
IN fromDate DATE,
IN toDate DATE
    )
BEGIN
SELECT from_date,to_date FROM holiday_details WHERE from_date >= fromDate AND to_date <=toDate;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_getWeeklyHoliday` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_getWeeklyHoliday` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_getWeeklyHoliday`()
BEGIN
SELECT day_name FROM  weekly_holiday WHERE STATUS=1;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `SP_monthlyAttendance` */

/*!50003 DROP PROCEDURE IF EXISTS  `SP_monthlyAttendance` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `SP_monthlyAttendance`(
		    IN employeeId INT(10),
                    IN from_date DATE,
                    IN to_date DATE
    )
BEGIN

SELECT employee.employee_id,CONCAT(COALESCE(employee.first_name,''),' ',COALESCE(employee.last_name,'')) AS fullName,department_name,
                        view_employee_in_out_data.finger_print_id,view_employee_in_out_data.date,view_employee_in_out_data.working_time,
                        DATE_FORMAT(view_employee_in_out_data.in_time,'%h:%i %p') AS in_time,DATE_FORMAT(view_employee_in_out_data.out_time,'%h:%i %p') AS out_time,
		TIME_FORMAT( work_shift.late_count_time, '%H:%i:%s' ) AS lateCountTime,
	(SELECT CASE WHEN DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:00')  > lateCountTime
            THEN 'Yes'
            ELSE 'No' END) AS  ifLate,

            (SELECT CASE WHEN TIMEDIFF((DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:%s')),work_shift.late_count_time)  > '0'
            THEN TIMEDIFF((DATE_FORMAT(MIN(view_employee_in_out_data.in_time),'%H:%i:%s')),work_shift.late_count_time)
            ELSE '00:00:00' END) AS  totalLateTime,
             TIMEDIFF((DATE_FORMAT(work_shift.`end_time`,'%H:%i:%s')),work_shift.`start_time`) AS workingHour
                        FROM employee
                        INNER JOIN view_employee_in_out_data ON view_employee_in_out_data.finger_print_id = employee.finger_id
                        INNER JOIN department ON department.department_id = employee.department_id
JOIN work_shift ON work_shift.work_shift_id = employee.work_shift_id
                        WHERE `status`=1
                       AND `date` BETWEEN from_date AND to_date AND employee_id=employeeId
                        GROUP BY view_employee_in_out_data.date,view_employee_in_out_data.`finger_print_id`;

    END */$$
DELIMITER ;

/* Procedure structure for procedure `supplier_ledger` */

/*!50003 DROP PROCEDURE IF EXISTS  `supplier_ledger` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `supplier_ledger`(
	IN supplier INT,start_date DATE,end_date DATE
    )
BEGIN
	SELECT * FROM
	(
	SELECT `supplier_id`,`purchase_date` AS `date`,`purchase_number` AS `code`,`total_amount` AS amount ,'purchase' AS table_name,`purchases`.`created_at` FROM `purchases`
	UNION ALL
	SELECT `purchases`.supplier_id,`purchase_payments`.`payment_date` AS `date`,`reference_no` AS `code`,SUM(`payment_amount`) AS amount, 'purchase_payment' AS table_name,`purchase_payments`.`created_at`  FROM `purchase_payments`
	INNER JOIN `purchases` ON (`purchases`.`id`=`purchase_payments`.`purchase_id`) GROUP BY `reference_no`
	)a
	WHERE a.`supplier_id` = supplier AND (a.`date` BETWEEN start_date AND end_date) ORDER BY a.`created_at`,FIELD(a.table_name, "purchase", "purchase_payment") ASC;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `supplier_wise_total_due` */

/*!50003 DROP PROCEDURE IF EXISTS  `supplier_wise_total_due` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`goodluck_general`@`%` PROCEDURE `supplier_wise_total_due`(
		IN supplier INT,start_date DATE
    )
BEGIN
	  SELECT `supplier_id`,SUM(`total_amount`) AS purchase_amount, SUM(COALESCE(payment_amount, 0)) AS payment_amount , (SUM(`total_amount`)-  SUM(COALESCE(payment_amount, 0))) AS due_amount FROM `purchases` LEFT JOIN `purchase_payments` ON (`purchases`.`id`=`purchase_payments`.`purchase_id`)
	  WHERE `purchases`.supplier_id=supplier AND `payment_status` IN (0,2) AND (purchases.`purchase_date` <= start_date);
    END */$$
DELIMITER ;

/*Table structure for table `view_employee_in_out_data` */

DROP TABLE IF EXISTS `view_employee_in_out_data`;

/*!50001 DROP VIEW IF EXISTS `view_employee_in_out_data` */;
/*!50001 DROP TABLE IF EXISTS `view_employee_in_out_data` */;

/*!50001 CREATE TABLE  `view_employee_in_out_data`(
 `employee_attendance_id` int(10) unsigned ,
 `finger_print_id` int(11) ,
 `in_time` datetime ,
 `out_time` varchar(19) ,
 `date` varchar(10) ,
 `working_time` time
)*/;

/*View structure for view view_employee_in_out_data */

/*!50001 DROP TABLE IF EXISTS `view_employee_in_out_data` */;
/*!50001 DROP VIEW IF EXISTS `view_employee_in_out_data` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`goodluck_general`@`%` SQL SECURITY DEFINER VIEW `view_employee_in_out_data` AS select `employee_attendance`.`employee_attendance_id` AS `employee_attendance_id`,`employee_attendance`.`finger_print_id` AS `finger_print_id`,min(`employee_attendance`.`in_out_time`) AS `in_time`,if((count(`employee_attendance`.`in_out_time`) > 1),max(`employee_attendance`.`in_out_time`),'') AS `out_time`,date_format(`employee_attendance`.`in_out_time`,'%Y-%m-%d') AS `date`,timediff(max(`employee_attendance`.`in_out_time`),min(`employee_attendance`.`in_out_time`)) AS `working_time` from `employee_attendance` group by date_format(`employee_attendance`.`in_out_time`,'%Y-%m-%d'),`employee_attendance`.`finger_print_id` */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;