You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
469 lines
40 KiB
469 lines
40 KiB
-- ---------------------------- |
|
-- Function structure for fn_first_day |
|
-- ---------------------------- |
|
DROP FUNCTION IF EXISTS `fn_first_day`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_first_day`(`d` date) RETURNS date |
|
DETERMINISTIC BEGIN |
|
RETURN date_sub(date_format(d,'%y-%m-%d'),interval extract(day from d)-1 day); |
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
-- ---------------------------- |
|
-- Function structure for fn_first_day_of_ym |
|
-- ---------------------------- |
|
DROP FUNCTION IF EXISTS `fn_first_day_of_ym`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_first_day_of_ym`(`y` smallint,`m` smallint) RETURNS date |
|
DETERMINISTIC BEGIN |
|
RETURN DATE(CONCAT(y, '-', m, '-', 1)); |
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
-- ---------------------------- |
|
-- Function structure for fn_last_day_of_ym |
|
-- ---------------------------- |
|
DROP FUNCTION IF EXISTS `fn_last_day_of_ym`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_last_day_of_ym`(`y` smallint,`m` smallint) RETURNS date |
|
DETERMINISTIC BEGIN |
|
RETURN LAST_DAY(DATE(CONCAT(y, '-', m, '-', 1))); |
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
|
|
-- ---------------------------- |
|
-- View structure for stk_io_entry_std |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `stk_io_entry_std`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stk_io_entry_std` AS select `e`.`id` AS `id`,`e`.`mid` AS `mid`,`e`.`material_id` AS `material_id`,`e`.`warehouse_id` AS `warehouse_id`,`e`.`batch_no` AS `batch_no`,`m`.`unit_id` AS `unit_id`,`e`.`stock_io_direction` AS `stock_io_direction`,round(((`e`.`qty` * `eu`.`factor`) / `mu`.`factor`),3) AS `qty`,`e`.`cost` AS `cost` from (((`stk_io_entry` `e` join `bas_unit` `eu` on((`e`.`unit_id` = `eu`.`id`))) join `bas_material` `m` on((`e`.`material_id` = `m`.`id`))) join `bas_unit` `mu` on((`m`.`unit_id` = `mu`.`id`))) ; |
|
|
|
-- ---------------------------- |
|
-- View structure for bill_not_effective |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `bill_not_effective`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bill_not_effective` AS select 1 AS `seq`,'销售报价' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `sal_quot` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 2 AS `seq`,'销售订单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `sal_order` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 3 AS `seq`,'出库单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `stk_io` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0) and (`b`.`stock_io_type` like '2%')) union select 4 AS `seq`,'应收单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_receivable` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 5 AS `seq`,'销售退货退款申请' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_receipt_req` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 6 AS `seq`,'收款单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_receipt` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 7 AS `seq`,'应收核销' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_receivable_check` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 8 AS `seq`,'销售发票登记' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_sal_invoice` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 9 AS `seq`,'采购申请' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `pur_req` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 10 AS `seq`,'采购询价' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `pur_inquiry` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 11 AS `seq`,'供应报价' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `pur_quot` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 12 AS `seq`,'采购比价' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `pur_compare` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 13 AS `seq`,'采购订单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `pur_order` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 14 AS `seq`,'入库单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `stk_io` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0) and (`b`.`stock_io_type` like '1%')) union select 15 AS `seq`,'应付单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_payable` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 16 AS `seq`,'付款申请' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_payment_req` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 17 AS `seq`,'付款单' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_payment` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 18 AS `seq`,'应付核销' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_payable_check` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 19 AS `seq`,'采购发票登记' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `fin_pur_invoice` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)) union select 20 AS `seq`,'库存调拨、成本调整' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `stk_io` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0) and (`b`.`stock_io_type` > '2')) union select 21 AS `seq`,'库存盘点' AS `name`,`b`.`id` AS `id`,`b`.`bill_no` AS `bill_no`,`b`.`bill_date` AS `bill_date` from `stk_check` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`is_effective` = 0)); |
|
|
|
-- ---------------------------- |
|
-- View structure for fin_payable_bal |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `fin_payable_bal`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `fin_payable_bal` AS select `c`.`id` AS `id`,`c`.`id` AS `supplier_id`,`c`.`name` AS `supplier_name`,((ifnull(`s`.`begin_credit_bal`,0) + ifnull(`pyb`.`amt`,0)) - ifnull(`pyt`.`amt`,0)) AS `credit_bal` from (((`psi`.`bas_supplier` `c` left join `psi`.`fin_payable_sum` `s` on(((`c`.`id` = `s`.`supplier_id`) and (((`s`.`year` * 100) + `s`.`month`) = (select ((`psi`.`bas_biz_period`.`year` * 100) + `psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))))) left join (select `psi`.`fin_payable`.`supplier_id` AS `supplier_id`,sum(`psi`.`fin_payable`.`amt`) AS `amt` from `psi`.`fin_payable` where ((`psi`.`fin_payable`.`is_effective` = 1) and (`psi`.`fin_payable`.`is_voided` = 0) and (`psi`.`fin_payable`.`bill_date` >= (select `fn_first_day_of_ym`(`psi`.`bas_biz_period`.`year`,`psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))) group by `psi`.`fin_payable`.`supplier_id`) `pyb` on((`c`.`id` = `pyb`.`supplier_id`))) left join (select `psi`.`fin_payment`.`supplier_id` AS `supplier_id`,sum(`psi`.`fin_payment`.`amt`) AS `amt` from `psi`.`fin_payment` where ((`psi`.`fin_payment`.`is_effective` = 1) and (`psi`.`fin_payment`.`is_voided` = 0) and (`psi`.`fin_payment`.`bill_date` >= (select `fn_first_day_of_ym`(`psi`.`bas_biz_period`.`year`,`psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))) group by `psi`.`fin_payment`.`supplier_id`) `pyt` on((`c`.`id` = `pyt`.`supplier_id`))) where (`c`.`is_enabled` = 1) ; |
|
|
|
-- ---------------------------- |
|
-- View structure for fin_receivable_bal |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `fin_receivable_bal`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `fin_receivable_bal` AS select `c`.`id` AS `id`,`c`.`id` AS `customer_id`,`c`.`name` AS `customer_name`,((ifnull(`s`.`begin_debit_bal`,0) + ifnull(`rcv`.`amt`,0)) - ifnull(`rcp`.`amt`,0)) AS `debit_bal` from (((`psi`.`bas_customer` `c` left join `psi`.`fin_receivable_sum` `s` on(((`c`.`id` = `s`.`customer_id`) and (((`s`.`year` * 100) + `s`.`month`) = (select ((`psi`.`bas_biz_period`.`year` * 100) + `psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))))) left join (select `psi`.`fin_receivable`.`customer_id` AS `customer_id`,sum(`psi`.`fin_receivable`.`amt`) AS `amt` from `psi`.`fin_receivable` where ((`psi`.`fin_receivable`.`is_effective` = 1) and (`psi`.`fin_receivable`.`is_voided` = 0) and (`psi`.`fin_receivable`.`bill_date` >= (select `fn_first_day_of_ym`(`psi`.`bas_biz_period`.`year`,`psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))) group by `psi`.`fin_receivable`.`customer_id`) `rcv` on((`c`.`id` = `rcv`.`customer_id`))) left join (select `psi`.`fin_receipt`.`customer_id` AS `customer_id`,sum(`psi`.`fin_receipt`.`amt`) AS `amt` from `psi`.`fin_receipt` where ((`psi`.`fin_receipt`.`is_effective` = 1) and (`psi`.`fin_receipt`.`is_voided` = 0) and (`psi`.`fin_receipt`.`bill_date` >= (select `fn_first_day_of_ym`(`psi`.`bas_biz_period`.`year`,`psi`.`bas_biz_period`.`month`) from `psi`.`bas_biz_period` limit 1))) group by `psi`.`fin_receipt`.`customer_id`) `rcp` on((`c`.`id` = `rcp`.`customer_id`))) where (`c`.`is_enabled` = 1) ; |
|
|
|
-- ---------------------------- |
|
-- View structure for home_purchase |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `home_purchase`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `home_purchase` AS select '订单' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`pur_order`.`amt`),0) AS `amt` from `psi`.`pur_order` where ((`psi`.`pur_order`.`is_effective` = 1) and (`psi`.`pur_order`.`is_voided` = 0) and (`psi`.`pur_order`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`pur_order`.`amt`),0) AS `amt` from `psi`.`pur_order` where ((`psi`.`pur_order`.`is_effective` = 1) and (`psi`.`pur_order`.`is_voided` = 0) and (yearweek(`psi`.`pur_order`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`pur_order`.`amt`),0) AS `amt` from `psi`.`pur_order` where ((`psi`.`pur_order`.`is_effective` = 1) and (`psi`.`pur_order`.`is_voided` = 0) and (year(`psi`.`pur_order`.`effective_time`) = year(sysdate())) and (month(`psi`.`pur_order`.`effective_time`) = month(sysdate())))) `m`) union select '入库' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '1%') and (`psi`.`stk_io`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '1%') and (yearweek(`psi`.`stk_io`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '1%') and (year(`psi`.`stk_io`.`effective_time`) = year(sysdate())) and (month(`psi`.`stk_io`.`effective_time`) = month(sysdate())))) `m`) union select '应付' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`fin_payable`.`amt`),0) AS `amt` from `psi`.`fin_payable` where ((`psi`.`fin_payable`.`is_effective` = 1) and (`psi`.`fin_payable`.`is_voided` = 0) and (`psi`.`fin_payable`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_payable`.`amt`),0) AS `amt` from `psi`.`fin_payable` where ((`psi`.`fin_payable`.`is_effective` = 1) and (`psi`.`fin_payable`.`is_voided` = 0) and (yearweek(`psi`.`fin_payable`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_payable`.`amt`),0) AS `amt` from `psi`.`fin_payable` where ((`psi`.`fin_payable`.`is_effective` = 1) and (`psi`.`fin_payable`.`is_voided` = 0) and (year(`psi`.`fin_payable`.`effective_time`) = year(sysdate())) and (month(`psi`.`fin_payable`.`effective_time`) = month(sysdate())))) `m`) union select '付款' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`fin_payment`.`amt`),0) AS `amt` from `psi`.`fin_payment` where ((`psi`.`fin_payment`.`is_effective` = 1) and (`psi`.`fin_payment`.`is_voided` = 0) and (`psi`.`fin_payment`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_payment`.`amt`),0) AS `amt` from `psi`.`fin_payment` where ((`psi`.`fin_payment`.`is_effective` = 1) and (`psi`.`fin_payment`.`is_voided` = 0) and (yearweek(`psi`.`fin_payment`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_payment`.`amt`),0) AS `amt` from `psi`.`fin_payment` where ((`psi`.`fin_payment`.`is_effective` = 1) and (`psi`.`fin_payment`.`is_voided` = 0) and (year(`psi`.`fin_payment`.`effective_time`) = year(sysdate())) and (month(`psi`.`fin_payment`.`effective_time`) = month(sysdate())))) `m`); |
|
|
|
-- ---------------------------- |
|
-- View structure for home_sale |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `home_sale`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `home_sale` AS select '订单' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`sal_order`.`amt`),0) AS `amt` from `psi`.`sal_order` where ((`psi`.`sal_order`.`is_effective` = 1) and (`psi`.`sal_order`.`is_voided` = 0) and (`psi`.`sal_order`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`sal_order`.`amt`),0) AS `amt` from `psi`.`sal_order` where ((`psi`.`sal_order`.`is_effective` = 1) and (`psi`.`sal_order`.`is_voided` = 0) and (yearweek(`psi`.`sal_order`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`sal_order`.`amt`),0) AS `amt` from `psi`.`sal_order` where ((`psi`.`sal_order`.`is_effective` = 1) and (`psi`.`sal_order`.`is_voided` = 0) and (year(`psi`.`sal_order`.`effective_time`) = year(sysdate())) and (month(`psi`.`sal_order`.`effective_time`) = month(sysdate())))) `m`) union select '出库' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '2%') and (`psi`.`stk_io`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '2%') and (yearweek(`psi`.`stk_io`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`stk_io`.`cost`),0) AS `amt` from `psi`.`stk_io` where ((`psi`.`stk_io`.`is_effective` = 1) and (`psi`.`stk_io`.`is_voided` = 0) and (`psi`.`stk_io`.`stock_io_type` like '2%') and (year(`psi`.`stk_io`.`effective_time`) = year(sysdate())) and (month(`psi`.`stk_io`.`effective_time`) = month(sysdate())))) `m`) union select '应收' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`fin_receivable`.`amt`),0) AS `amt` from `psi`.`fin_receivable` where ((`psi`.`fin_receivable`.`is_effective` = 1) and (`psi`.`fin_receivable`.`is_voided` = 0) and (`psi`.`fin_receivable`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_receivable`.`amt`),0) AS `amt` from `psi`.`fin_receivable` where ((`psi`.`fin_receivable`.`is_effective` = 1) and (`psi`.`fin_receivable`.`is_voided` = 0) and (yearweek(`psi`.`fin_receivable`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_receivable`.`amt`),0) AS `amt` from `psi`.`fin_receivable` where ((`psi`.`fin_receivable`.`is_effective` = 1) and (`psi`.`fin_receivable`.`is_voided` = 0) and (year(`psi`.`fin_receivable`.`effective_time`) = year(sysdate())) and (month(`psi`.`fin_receivable`.`effective_time`) = month(sysdate())))) `m`) union select '收款' AS `label`,`t`.`count` AS `t_count`,`t`.`amt` AS `t_amt`,`w`.`count` AS `w_count`,`w`.`amt` AS `w_amt`,`m`.`count` AS `m_count`,`m`.`amt` AS `m_amt` from ((((select count(1) AS `count`,ifnull(sum(`psi`.`fin_receipt`.`amt`),0) AS `amt` from `psi`.`fin_receipt` where ((`psi`.`fin_receipt`.`is_effective` = 1) and (`psi`.`fin_receipt`.`is_voided` = 0) and (`psi`.`fin_receipt`.`effective_time` >= cast(sysdate() as date))))) `t` join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_receipt`.`amt`),0) AS `amt` from `psi`.`fin_receipt` where ((`psi`.`fin_receipt`.`is_effective` = 1) and (`psi`.`fin_receipt`.`is_voided` = 0) and (yearweek(`psi`.`fin_receipt`.`effective_time`,1) = yearweek(sysdate(),1)))) `w`) join (select count(1) AS `count`,ifnull(sum(`psi`.`fin_receipt`.`amt`),0) AS `amt` from `psi`.`fin_receipt` where ((`psi`.`fin_receipt`.`is_effective` = 1) and (`psi`.`fin_receipt`.`is_voided` = 0) and (year(`psi`.`fin_receipt`.`effective_time`) = year(sysdate())) and (month(`psi`.`fin_receipt`.`effective_time`) = month(sysdate())))) `m`); |
|
|
|
-- ---------------------------- |
|
-- View structure for stk_batch |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `stk_batch`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stk_batch` AS select distinct `stk_inventory`.`batch_no` AS `batch_no` from `stk_inventory` where (`stk_inventory`.`is_closed` = 0) ; |
|
|
|
-- ---------------------------- |
|
-- View structure for stk_io_day_sum |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `stk_io_day_sum`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stk_io_day_sum` AS select max(`e`.`id`) AS `id`,`b`.`bill_date` AS `bill_date`,`e`.`warehouse_id` AS `warehouse_id`,`e`.`material_id` AS `material_id`,`e`.`batch_no` AS `batch_no`,`e`.`unit_id` AS `unit_id`,sum((case `e`.`stock_io_direction` when '1' then `e`.`qty` else 0 end)) AS `in_qty`,sum((case `e`.`stock_io_direction` when '1' then `e`.`cost` else 0 end)) AS `in_cost`,sum((case `e`.`stock_io_direction` when '2' then `e`.`qty` else 0 end)) AS `out_qty`,sum((case `e`.`stock_io_direction` when '2' then `e`.`cost` else 0 end)) AS `out_cost` from (`stk_io` `b` join `stk_io_entry_std` `e` on((`b`.`id` = `e`.`mid`))) where ((`b`.`is_effective` = 1) and (`b`.`is_voided` = 0)) group by `b`.`bill_date`,`e`.`warehouse_id`,`e`.`material_id`,`e`.`batch_no`,`e`.`unit_id` ; |
|
|
|
-- ---------------------------- |
|
-- View structure for stk_material_qty |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `stk_material_qty`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stk_material_qty` AS select `m`.`id` AS `id`,max(`m`.`unit_id`) AS `unit_id`,sum(((`i`.`qty` * `iu`.`factor`) / `mu`.`factor`)) AS `qty` from (((`bas_material` `m` join `stk_inventory` `i` on(((`m`.`id` = `i`.`material_id`) and (`i`.`is_closed` = 0)))) join `bas_unit` `mu` on((`m`.`unit_id` = `mu`.`id`))) join `bas_unit` `iu` on((`i`.`unit_id` = `iu`.`id`))) group by `m`.`id` ; |
|
|
|
-- ---------------------------- |
|
-- View structure for sys_user_dept |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `sys_user_dept`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sys_user_dept` AS select `ud`.`dep_id` AS `dept_id`,`u`.`username` AS `username` from (`sys_user` `u` join `sys_user_depart` `ud` on((`u`.`id` = `ud`.`user_id`))) ; |
|
|
|
-- ---------------------------- |
|
-- View structure for home_doing_bill |
|
-- ---------------------------- |
|
DROP VIEW IF EXISTS `home_doing_bill`; |
|
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `home_doing_bill` AS select `t`.`name` AS `name`,ifnull(`t`.`edit`,0) AS `edit`,ifnull(`t`.`appr`,0) AS `appr`,ifnull(`t`.`exec`,0) AS `exec` from (select '销售报价' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`sal_quot` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '销售订单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`sal_order` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '出库单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`stk_io` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`stock_io_type` like '2%')) union select '应收单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_receivable` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '收款单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_receipt` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '销售发票' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_sal_invoice` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '采购申请' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`pur_req` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '采购询价' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`pur_inquiry` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '供应报价' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`pur_quot` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '采购比价' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`pur_compare` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '采购订单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`pur_order` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '入库单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`stk_io` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0) and (`b`.`stock_io_type` like '1%')) union select '应付单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_payable` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '付款申请' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_payment_req` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '付款单' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_payment` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '采购发票' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`fin_pur_invoice` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0)) union select '库存盘点' AS `name`,sum((case `b`.`bill_stage` when '12' then 1 else 0 end)) AS `edit`,sum((case when ((`b`.`bill_stage` in ('14','22')) and (`b`.`is_effective` = 0)) then 1 else 0 end)) AS `appr`,sum((case when ((`b`.`bill_stage` in ('24','32')) and (`b`.`is_effective` = 1)) then 1 else 0 end)) AS `exec` from `psi`.`stk_check` `b` where ((`b`.`is_closed` = 0) and (`b`.`is_voided` = 0))) `t`; |
|
|
|
|
|
|
|
|
|
-- ---------------------------- |
|
-- Procedure structure for sp_fin_payable_sum |
|
-- ---------------------------- |
|
DROP PROCEDURE IF EXISTS `sp_fin_payable_sum`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_fin_payable_sum`(IN `p_year` smallint,IN `p_month` tinyint) |
|
DETERMINISTIC BEGIN |
|
DECLARE n_year SMALLINT; |
|
DECLARE n_month TINYINT; |
|
|
|
DROP TABLE IF EXISTS `tmp`; |
|
CREATE TEMPORARY TABLE tmp( |
|
supplier_id VARCHAR(36), |
|
amt DECIMAL(18,2) DEFAULT 0 |
|
); |
|
|
|
#初始金额:上月汇总已生成本月的期初数据 |
|
UPDATE fin_payable_sum b |
|
SET b.debit_amt = 0, b.credit_amt = 0 |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month; |
|
|
|
#贷方金额 |
|
INSERT INTO tmp(supplier_id, amt) |
|
SELECT p.supplier_id, SUM(p.amt) amt |
|
FROM fin_payable p |
|
WHERE p.is_effective = 1 |
|
AND p.is_voided = 0 |
|
AND p.bill_date BETWEEN fn_first_day_of_ym(p_year,p_month) AND fn_last_day_of_ym(p_year,p_month) |
|
GROUP BY p.supplier_id; |
|
|
|
UPDATE fin_payable_sum b |
|
INNER JOIN tmp a |
|
ON b.supplier_id = a.supplier_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
SET b.credit_amt = a.amt; |
|
|
|
#本月新增 |
|
INSERT INTO fin_payable_sum(id, `year`, `month`, supplier_id, begin_credit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, a.supplier_id, 0, 0, a.amt |
|
FROM tmp a |
|
LEFT JOIN fin_payable_sum b |
|
ON a.supplier_id = b.supplier_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
WHERE b.supplier_id IS NULL; |
|
|
|
SELECT IFNULL(SUM(amt), 0) INTO @credit_amt FROM tmp; |
|
|
|
DELETE FROM tmp; |
|
|
|
#借方金额 |
|
INSERT INTO tmp(supplier_id, amt) |
|
SELECT p.supplier_id, SUM(p.amt) amt |
|
FROM fin_payment p |
|
WHERE p.is_effective = 1 |
|
AND p.is_voided = 0 |
|
AND p.bill_date BETWEEN fn_first_day_of_ym(p_year,p_month) AND fn_last_day_of_ym(p_year,p_month) |
|
GROUP BY p.supplier_id; |
|
|
|
UPDATE fin_payable_sum b |
|
INNER JOIN tmp a |
|
ON b.supplier_id = a.supplier_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
SET b.debit_amt = a.amt; |
|
|
|
#本月新增 |
|
INSERT INTO fin_payable_sum(id, `year`, `month`, supplier_id, begin_credit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, a.supplier_id, 0, a.amt, 0 |
|
FROM tmp a |
|
LEFT JOIN fin_payable_sum b |
|
ON a.supplier_id = b.supplier_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
WHERE b.supplier_id IS NULL; |
|
|
|
SELECT IFNULL(SUM(amt), 0) INTO @debit_amt FROM tmp; |
|
|
|
#金额合计 |
|
SELECT COUNT(b.id) |
|
INTO @num |
|
FROM fin_payable_sum b |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.supplier_id IS NULL; |
|
|
|
IF @num = 0 THEN |
|
INSERT INTO fin_payable_sum(id, `year`, `month`, supplier_id, begin_credit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, NULL, IFNULL(SUM(b.begin_credit_bal), 0), IFNULL(SUM(b.debit_amt), 0), IFNULL(SUM(b.credit_amt), 0) |
|
FROM fin_payable_sum b |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.supplier_id IS NOT NULL; |
|
ELSE |
|
UPDATE fin_payable_sum b |
|
SET b.debit_amt = @debit_amt, b.credit_amt = @credit_amt |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.supplier_id IS NULL; |
|
END IF; |
|
|
|
#期末余额 |
|
UPDATE fin_payable_sum b |
|
SET b.credit_bal = IFNULL(b.begin_credit_bal,0) - IFNULL(b.debit_amt,0) + IFNULL(b.credit_amt, 0) |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month; |
|
|
|
#结转下月 |
|
IF p_month < 12 THEN |
|
SET @n_year = p_year, @n_month = p_month + 1; |
|
ELSE |
|
SET @n_year = p_year + 1, @n_month = 1; |
|
END IF; |
|
|
|
DELETE FROM fin_payable_sum |
|
WHERE `year` = @n_year |
|
AND `month` = @n_month; |
|
|
|
INSERT INTO fin_payable_sum(id, `year`, `month`, supplier_id, begin_credit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), @n_year, @n_month, IFNULL(b.supplier_id, c.id), b.credit_bal, 0, 0 |
|
FROM fin_payable_sum b |
|
LEFT JOIN bas_supplier c |
|
ON b.supplier_id = c.id |
|
AND c.is_enabled = 1 |
|
WHERE b.credit_bal != 0 |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month; |
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
-- ---------------------------- |
|
-- Procedure structure for sp_fin_receivable_sum |
|
-- ---------------------------- |
|
DROP PROCEDURE IF EXISTS `sp_fin_receivable_sum`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_fin_receivable_sum`(IN `p_year` smallint,IN `p_month` tinyint) |
|
DETERMINISTIC BEGIN |
|
DECLARE n_year SMALLINT; |
|
DECLARE n_month TINYINT; |
|
|
|
DROP TABLE IF EXISTS `tmp`; |
|
CREATE TEMPORARY TABLE tmp( |
|
customer_id VARCHAR(36), |
|
amt DECIMAL(18,2) DEFAULT 0 |
|
); |
|
|
|
#初始金额:上月汇总已生成本月的期初数据 |
|
UPDATE fin_receivable_sum b |
|
SET b.debit_amt = 0, b.credit_amt = 0 |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month; |
|
|
|
#借方金额 |
|
INSERT INTO tmp(customer_id, amt) |
|
SELECT r.customer_id, SUM(r.amt) amt |
|
FROM fin_receivable r |
|
WHERE r.is_effective = 1 |
|
AND r.is_voided = 0 |
|
AND r.bill_date BETWEEN fn_first_day_of_ym(p_year, p_month) AND fn_last_day_of_ym(p_year, p_month) |
|
GROUP BY r.customer_id; |
|
|
|
UPDATE fin_receivable_sum b |
|
INNER JOIN tmp a |
|
ON b.customer_id = a.customer_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
SET b.debit_amt = a.amt; |
|
|
|
#本月新增 |
|
INSERT INTO fin_receivable_sum(id, `year`, `month`, customer_id, begin_debit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, a.customer_id, 0, a.amt, 0 |
|
FROM tmp a |
|
LEFT JOIN fin_receivable_sum b |
|
ON a.customer_id = b.customer_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
WHERE b.customer_id IS NULL; |
|
|
|
SELECT IFNULL(SUM(amt), 0) INTO @debit_amt FROM tmp; |
|
|
|
DELETE FROM tmp; |
|
|
|
#贷方金额 |
|
INSERT INTO tmp(customer_id, amt) |
|
SELECT r.customer_id, SUM(r.amt) amt |
|
FROM fin_receipt r |
|
WHERE r.is_effective = 1 |
|
AND r.is_voided = 0 |
|
AND r.bill_date BETWEEN fn_first_day_of_ym(p_year, p_month) AND fn_last_day_of_ym(p_year, p_month) |
|
GROUP BY r.customer_id; |
|
|
|
UPDATE fin_receivable_sum b |
|
INNER JOIN tmp a |
|
ON b.customer_id = a.customer_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
SET b.credit_amt = a.amt; |
|
|
|
#本月新增 |
|
INSERT INTO fin_receivable_sum(id, `year`, `month`, customer_id, begin_debit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, a.customer_id, 0, 0, a.amt |
|
FROM tmp a |
|
LEFT JOIN fin_receivable_sum b |
|
ON a.customer_id = b.customer_id |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month |
|
WHERE b.customer_id IS NULL; |
|
|
|
SELECT IFNULL(SUM(amt), 0) INTO @credit_amt FROM tmp; |
|
|
|
|
|
#金额合计 |
|
SELECT COUNT(b.id) |
|
INTO @num |
|
FROM fin_receivable_sum b |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.customer_id IS NULL; |
|
|
|
IF @num = 0 THEN |
|
INSERT INTO fin_receivable_sum(id, `year`, `month`, customer_id, begin_debit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), p_year, p_month, NULL, IFNULL(SUM(b.begin_debit_bal), 0), IFNULL(SUM(b.debit_amt), 0), IFNULL(SUM(b.credit_amt),0) |
|
FROM fin_receivable_sum b |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.customer_id IS NOT NULL; |
|
ELSE |
|
UPDATE fin_receivable_sum b |
|
SET b.debit_amt = @debit_amt, b.credit_amt = @credit_amt |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month |
|
AND b.customer_id IS NULL; |
|
END IF; |
|
|
|
#期末余额 |
|
UPDATE fin_receivable_sum b |
|
SET b.debit_bal = IFNULL(b.begin_debit_bal,0) + IFNULL(b.debit_amt,0) - IFNULL(b.credit_amt,0) |
|
WHERE b.`year` = p_year |
|
AND b.`month` = p_month; |
|
|
|
#结转下月 |
|
IF p_month < 12 THEN |
|
SET @n_year = p_year, @n_month = p_month + 1; |
|
ELSE |
|
SET @n_year = p_year + 1, @n_month = 1; |
|
END IF; |
|
|
|
DELETE FROM fin_receivable_sum |
|
WHERE `year` = @n_year |
|
AND `month` = @n_month; |
|
|
|
INSERT INTO fin_receivable_sum(id, `year`, `month`, customer_id, begin_debit_bal, debit_amt, credit_amt) |
|
SELECT MD5(UUID()), @n_year, @n_month, IFNULL(b.customer_id, c.id), b.debit_bal, 0, 0 |
|
FROM fin_receivable_sum b |
|
LEFT JOIN bas_customer c |
|
ON b.customer_id = c.id |
|
AND c.is_enabled = 1 |
|
WHERE b.debit_bal != 0 |
|
AND b.`year` = p_year |
|
AND b.`month` = p_month; |
|
|
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
-- ---------------------------- |
|
-- Procedure structure for sp_stk_io_sum |
|
-- ---------------------------- |
|
DROP PROCEDURE IF EXISTS `sp_stk_io_sum`; |
|
DELIMITER ;; |
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_stk_io_sum`(IN `p_year` smallint,IN `p_month` tinyint) |
|
DETERMINISTIC BEGIN |
|
DECLARE n_year SMALLINT; |
|
DECLARE n_month TINYINT; |
|
|
|
DROP TABLE IF EXISTS `tmp`; |
|
CREATE TEMPORARY TABLE tmp( |
|
material_id VARCHAR(36), |
|
batch_no VARCHAR(100), |
|
warehouse_id VARCHAR(36), |
|
unit_id VARCHAR(36), |
|
in_qty DECIMAL(18,6) DEFAULT 0, |
|
in_cost DECIMAL(18,2) DEFAULT 0, |
|
out_qty DECIMAL(18,6) DEFAULT 0, |
|
out_cost DECIMAL(18,2) DEFAULT 0 |
|
); |
|
|
|
#初始发生数:上月汇总已生成本月的期初数 |
|
UPDATE stk_io_sum s |
|
SET s.in_qty = 0, s.in_cost = 0, s.out_qty = 0, s.out_cost = 0 |
|
WHERE s.`year` = p_year |
|
AND s.`month` = p_month; |
|
|
|
#发生数 |
|
INSERT INTO tmp(material_id, batch_no, warehouse_id, unit_id, in_qty, in_cost, out_qty, out_cost) |
|
SELECT e.material_id, e.batch_no, e.warehouse_id, e.unit_id, |
|
SUM(CASE e.stock_io_direction WHEN '1' THEN e.qty ELSE 0 END) in_qty, |
|
SUM(CASE e.stock_io_direction WHEN '1' THEN e.cost ELSE 0 END) in_cost, |
|
SUM(CASE e.stock_io_direction WHEN '2' THEN e.qty ELSE 0 END) out_qty, |
|
SUM(CASE e.stock_io_direction WHEN '2' THEN e.cost ELSE 0 END) out_cost |
|
FROM stk_io b |
|
INNER JOIN stk_io_entry_std e |
|
ON b.id = e.mid |
|
WHERE b.is_effective = 1 |
|
AND b.is_voided = 0 |
|
AND b.bill_date BETWEEN fn_first_day_of_ym(p_year,p_month) AND fn_last_day_of_ym(p_year,p_month) |
|
GROUP BY e.material_id, e.batch_no, e.warehouse_id, e.unit_id; |
|
|
|
UPDATE stk_io_sum s |
|
INNER JOIN tmp t |
|
ON s.material_id = t.material_id |
|
AND s.batch_no = t.batch_no |
|
AND s.warehouse_id = t.warehouse_id |
|
AND s.unit_id = t.unit_id |
|
SET s.in_qty = t.in_qty, s.in_cost = t.in_cost, s.out_qty = t.out_qty, s.out_cost = t.out_cost |
|
WHERE s.`year` = p_year |
|
AND s.`month` = p_month; |
|
|
|
#本月新增 |
|
INSERT INTO stk_io_sum(id, `year`, `month`, material_id, batch_no, warehouse_id, unit_id, begin_qty, begin_cost, in_qty, in_cost, out_qty, out_cost) |
|
SELECT MD5(UUID()), p_year, p_month, t.material_id, t.batch_no, t.warehouse_id, t.unit_id, 0, 0, t.in_qty, t.in_cost, t.out_qty, t.out_cost |
|
FROM tmp t |
|
LEFT JOIN stk_io_sum s |
|
ON t.material_id = s.material_id |
|
AND t.batch_no = s.batch_no |
|
AND t.warehouse_id = s.warehouse_id |
|
AND t.unit_id = s.unit_id |
|
AND s.`year` = p_year |
|
AND s.`month` = p_month |
|
WHERE s.id IS NULL; |
|
|
|
#期末结存 |
|
UPDATE stk_io_sum s |
|
SET s.bal_qty = IFNULL(s.begin_qty, 0) + IFNULL(s.in_qty, 0) - IFNULL(s.out_qty, 0), |
|
s.bal_cost = IFNULL(s.begin_cost, 0) + IFNULL(s.in_cost, 0) - IFNULL(s.out_cost, 0) |
|
WHERE s.`year` = p_year |
|
AND s.`month` = p_month; |
|
|
|
#结转下月 |
|
IF p_month < 12 THEN |
|
SET @n_year = p_year, @n_month = p_month + 1; |
|
ELSE |
|
SET @n_year = p_year + 1, @n_month = 1; |
|
END IF; |
|
|
|
DELETE FROM stk_io_sum |
|
WHERE `year` = @n_year |
|
AND `month` = @n_month; |
|
|
|
INSERT INTO stk_io_sum(id, `year`, `month`, material_id, batch_no, warehouse_id, unit_id, begin_qty, begin_cost, in_qty, in_cost, out_qty, out_cost, bal_qty, bal_cost) |
|
SELECT MD5(UUID()), @n_year, @n_month, s.material_id, s.batch_no, s.warehouse_id, s.unit_id, s.bal_qty, s.bal_cost, 0, 0, 0, 0, 0, 0 |
|
FROM stk_io_sum s |
|
WHERE s.bal_qty != 0 |
|
AND s.bal_cost != 0 |
|
AND s.`year` = p_year |
|
AND s.`month` = p_month; |
|
END |
|
;; |
|
DELIMITER ; |
|
|
|
|