CREATE DEFINER=`cuccmysql`@`%` PROCEDURE `销售暂估单`(IN useorg int, IN end_time datetime, IN start_time datetime,IN userid bigint)
begin
DECLARE zgdid bigINT;
delete from tmp_zg where tmp_zg.`暂估截止日期`=end_time ;
insert into tmp_zg
(contract_id,数据来源,结算标志,品种id,暂估方量,暂估金额,暂估截止日期,站点,标记,来源id,iss_date,branch,tax)
# 取未结算已计价的小票签收方量
(
select contract_id 合同id,'混凝土' 数据来源
, '未结算' 结算标志,IFNULL(variety_id,151030) 品种id,
ifnull(sign_for_amount,0) 暂估方量,ifnull(tip_total_price,0) 暂估金额,
end_time 暂估截止日期 ,case when a.use_org=0 then a.station_id else a.use_org end 搅拌站
,zgdid 暂估单id,a.id 来源id,issue_date,b.mix_station_id
, b.tax_rate
from b_salesdeliver_electronic_tip a inner join b_salesdeliver_sales_contract b on a.contract_code=b.code and b.is_deleted=0
where (ifnull(settlement_id,0) = 0 and ifnull(settlement_code,'') ='') and a.is_deleted <> 1 and issue_date<=end_time
and ifnull(sign_for_amount,0)>0 and b.is_forbid_estimation<>1 /*and ifnull(tip_total_price,0)>0 */
)
union all
-- 取结算单里的结算结束日期小于暂估截至日期直接取汇总的数据 (未审核部分算未结算)
(
select b.contract_id 合同id, '混凝土' 数据来源
,'小于日期' 结算标志 ,IFNULL(a.varieties_id,151030) 品种id
, ifnull(not_invoice_amount,0) 暂估方量,ifnull(a.not_invoice_money,0) 暂估金额
,end_time 暂估截止日期, a.mix_station_id 搅拌站
,zgdid 暂估单id ,a.id 来源id,settlement_end_time,c.mix_station_id,c.tax_rate
from b_salesdeliver_concrete_settlement_bill_summary a
inner join b_salesdeliver_concrete_settlement_bill b on a.pid=b.id
inner join b_salesdeliver_sales_contract c on b.contract_code=c.code
where a.pid=b.id /*and b.settlement_end_time<=end_time*/
and a.is_deleted=0 and c.is_forbid_estimation<>1
);
BEGIN
DECLARE z_branch BIGINT;
DECLARE z_tax DECIMAL(10,2);
DECLARE z_flx VARCHAR(10);
DECLARE done INT;
DECLARE ID_Curs CURSOR FOR
SELECT distinct branch, tax,数据来源 as FLX from tmp_zg where `暂估截止日期`=end_time ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set done=0;
OPEN ID_Curs;
REPEAT
FETCH ID_Curs INTO z_branch,z_tax,z_flx;
if done<>1 then
set zgdid= (SELECT max(id)+1 from b_salesdeliver_estimation);
#插入暂估单主表
insert into b_salesdeliver_estimation
(id, code ,use_org, created_by, updated_by ,checked_by, create_time, update_time, check_time ,status ,remark ,revision, is_deleted
, state, estimation_money, estimation_amount, invoicing_money, invoicing_amount, change_money, change_amount, estimation_account_period
, estimation_year, close_date, estimation_total_amount, estimation_total_money, tip_money, tip_amount, branch_id)
SELECT
zgdid id ,#
CONCAT( 'ZG-', DATE_FORMAT(end_time,'%Y%m'),z_flx,round(z_tax,0),'-',zgdid) code ,#
'0' use_org ,#
userid created_by , userid updated_by , userid checked_by , now() create_time , now() update_time , now() check_time ,# 审核日期
9333 status , CONCAT( a.name, z_flx,round(z_tax,0)) remark , 0 revision , 0 is_deleted , 9060 state
, sum(ss.暂估金额) estimation_money , sum(ss.暂估方量) estimation_amount , 0 invoicing_money , 0 invoicing_amount , 0 change_money
, 0 change_amount , MONTH(end_time) estimation_account_period, YEAR(end_time) estimation_year, end_time close_date
, sum(ss.暂估金额) estimation_total_amount ,# 暂估累计方量
sum(ss.暂估方量) estimation_total_money , 0 tip_money, 0 tip_amount, z_branch
from tmp_zg ss INNER JOIN b_basic_sales_branch a on ss.branch=a.id
where `暂估截止日期`=end_time and 数据来源=z_flx and branch=z_branch and ss.tax=z_tax GROUP BY a.id;
--
#新增数据到暂估单明细表
insert into b_salesdeliver_estimation_contract_info
(code , unit, use_org ,created_by ,updated_by , checked_by , create_time , update_time ,check_time ,status ,remark ,revision ,is_deleted ,
state ,settlement_company_id ,engineering_project_id , project_code , variety_id , estimation_amount , estimation_money , change_amount ,
change_money , estimation_date , settlement_type , contract_id , invoice_amount , invoice_money , pid, estimation_type
)
select
concat(zgdid) code , 站点, 0 use_org , userid created_by, userid updated_by, userid checked_by, now() create_time,now() update_time , now() check_time
, 9333 status , b.code remark , 0 revision , 0 is_deleted , 9060 state ,# 是否禁用
b.settlement_company_id settlement_company_id ,# 结算单位
b.engineering_project_id engineering_project_id ,# 工程项目
c.code project_code ,# 工程项目编号
a.`品种id` variety_id ,# 品种
a.`本期暂估方量` estimation_amount ,# 暂估方量
a.`本期暂估金额` estimation_money ,# 暂估金额
0 change_amount ,# 调整方量
0 change_money ,# 调整金额
now() estimation_date ,# 暂估日期
1 settlement_type ,# 结算类型
b.`id` contract_id ,# 合同号
z_tax invoice_amount ,# 税率
round(a.本期暂估金额/(1+round((z_tax/100),2))*round((z_tax/100),2),2) invoice_money ,# 税额
zgdid pid ,#
a.`结算标志` estimation_type
from
(
select tt.contract_id,tt.品种id,tt.结算标志,站点,iss_date,tt.本期暂估方量,tt.本期暂估金额 from
(
select contract_id,品种id,结算标志,站点,iss_date,sum(暂估方量) 本期暂估方量,sum(`暂估金额`) 本期暂估金额
from tmp_zg
where `暂估截止日期`=end_time and 数据来源=z_flx and branch=z_branch and tax=z_tax
group by contract_id,品种id,结算标志,站点,iss_date having (本期暂估方量<>0 or 本期暂估金额 <>0)
)tt
) a left join b_salesdeliver_sales_contract b
on a.contract_id=b.id
left join b_basic_project c on b.engineering_project_id=c.id
order by b.`id`;
end if;
until done end
REPEAT;
CLOSE ID_Curs;
end ;
END
最新发布