ALTER PROCEDURE [dbo].[generateServDatas]
AS
BEGIN
declare @sp_id varchar(50), -- id
@sold_to_code varchar(50), --
@catg_id varchar(50), -- id
@catg_name varchar(100),--
@serv_type varchar(50), -- ，
@department_code varchar(50), --
@bus_catg_code varchar(50), --
@s_bus_catg_code varchar(50), --
@recordCount int, --
@currYear varchar(10), --
@currMoth varchar(10); --
BEGIN
select @currYear=convert(varchar(10),DATENAME(yy,getdate()));
select @currMoth=convert(varchar(10),DATENAME(mm,getdate()));
end
DECLARE rs CURSOR LOCAL SCROLL FOR
select sp.id as sp_id,sp.sold_to_code,cg.id as catg_id ,cg.cust_catg_name from t_base_cust_sp sp , t_cust_catg cg where sp.catg_id=cg.id;
OPEN rs
FETCH NEXT FROM rs INTO @sp_id,@sold_to_code,@catg_id,@catg_name
WHILE @@FETCH_STATUS = 0
BEGIN
print(@sp_id+''+@sold_to_code+''+@catg_id+''+@catg_name);
select @recordCount=count(t.id) from t_data_cust_serv_relation_sp t where t.type=' ' and t.cust_id=@sp_id
if (@recordCount=0)
begin
DECLARE catg_serv_cursor CURSOR LOCAL SCROLL FOR
select t.department_code,t.business_category_code,t.sbusiness_category_code from t_data_extra_service t,t_data_cust_serv_relation_sp tr where t.id=tr.serv_id and tr.type=' ' and tr.cust_catg_id=@catg_id group by t.department_code,t.business_category_code,t.sbusiness_category_code;
open catg_serv_cursor
FETCH NEXT FROM catg_serv_cursor INTO @department_code,@bus_catg_code,@s_bus_catg_code
WHILE @@FETCH_STATUS = 0
BEGIN
print(@department_code+''+@bus_catg_code+''+@s_bus_catg_code);
BEGIN
insert into t_serv_month_report(sp_id,sold_to_code,catg_id,catg_name,serv_type,department_code,bus_catg_code,s_bus_catg_code,create_year,create_month)
values (@sp_id,@sold_to_code,@catg_id,@catg_name,' ',@department_code,@bus_catg_code,@s_bus_catg_code,@currYear,@currMoth);
END
FETCH NEXT FROM catg_serv_cursor INTO @department_code,@bus_catg_code,@s_bus_catg_code
END
CLOSE catg_serv_cursor
DEALLOCATE catg_serv_cursor
end
ELSE
BEGIN
DECLARE cust_serv_cursor CURSOR LOCAL SCROLL FOR
select t.department_code,t.business_category_code,t.sbusiness_category_code from t_data_extra_service t,t_data_cust_serv_relation_sp tr where t.id=tr.serv_id and tr.type=' ' and tr.cust_id=@sp_id group by t.department_code,t.business_category_code,t.sbusiness_category_code;
open cust_serv_cursor
FETCH NEXT FROM cust_serv_cursor INTO @department_code,@bus_catg_code,@s_bus_catg_code
WHILE @@FETCH_STATUS = 0
BEGIN
print(@department_code+''+@bus_catg_code+''+@s_bus_catg_code);
BEGIN
insert into t_serv_month_report(sp_id,sold_to_code,catg_id,catg_name,serv_type,department_code,bus_catg_code,s_bus_catg_code,create_year,create_month)
values (@sp_id,@sold_to_code,@catg_id,@catg_name,' ',@department_code,@bus_catg_code,@s_bus_catg_code,@currYear,@currMoth);
END
FETCH NEXT FROM cust_serv_cursor INTO @department_code,@bus_catg_code,@s_bus_catg_code
END
CLOSE cust_serv_cursor
DEALLOCATE cust_serv_cursor
END
FETCH NEXT FROM rs INTO @sp_id,@sold_to_code,@catg_id,@catg_name
END
CLOSE rs
DEALLOCATE rs