begin
declare t_error integer default 0;
declare v_cerror integer default 0;
declare v_count int(11);
declare v_id varchar(255);
declare v_create_date datetime;
declare v_del_flag varchar(1);
declare v_remarks varchar(255);
declare v_update_date datetime;
declare v_address varchar(255);
declare v_appstate varchar(255);
declare v_area_id varchar(255);
declare v_belong varchar(255);
declare v_borough_id varchar(255);
declare v_borough_name varchar(255);
declare v_broker_id varchar(255);
declare v_check_name varchar(255);
declare v_check_time varchar(255);
declare v_consigner_id varchar(255);
declare v_down_time varchar(255);
declare v_draw_url varchar(255);
declare v_house_age varchar(255);
declare v_house_desc varchar(255);
declare v_house_drawing varchar(255);
declare v_house_feature varchar(255);
declare v_house_fitment varchar(255);
declare v_house_floor varchar(255);
declare v_house_hall varchar(255);
declare v_house_no varchar(255);
declare v_house_price double;
declare v_house_room varchar(255);
declare v_house_thumb varchar(255);
declare v_house_title varchar(255);
declare v_house_toilet varchar(255);
declare v_house_topfloor varchar(255);
declare v_house_totalarea double;
declare v_house_toward varchar(255);
declare v_house_type varchar(255);
declare v_house_veranda varchar(255);
declare v_is_down varchar(255);
declare v_is_promote varchar(255);
declare v_is_synchro varchar(255);
declare v_is_whether varchar(255);
declare v_owner_name varchar(255);
declare v_owner_notes varchar(255);
declare v_owner_phone varchar(255);
declare v_status varchar(255);
declare v_submit_name varchar(255);
declare v_submit_time varchar(255);
declare v_synchro_time varchar(255);
declare v_video varchar(255);
declare v_whether_time varchar(255);
declare v_create_by varchar(255);
declare v_update_by varchar(255);
declare v_area_name varchar(255);
declare v_is_new varchar(255);
declare v_log_flag varchar(255);
declare v_log_status varchar(255);
declare v_lot_NO varchar(255);
declare done int default false;
declare cur cursor for select
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`,
`log_flag`,`log_status`,`lot_NO`
from zj_front_ihouse.zj_front_housesell_swp
where lot_NO = p_lot_NO and (log_flag = 'i' or log_flag = 'd' or log_flag = 'u')
order by update_date asc;
declare continue handler for not found set done = true;
declare continue handler for sqlexception set t_error=1;
start transaction;
open cur;
read_loop: loop
set v_id='',v_create_date='0000-01-01 00:00:00',v_del_flag='',v_remarks='',v_update_date='0000-01-01 00:00:00',
v_address='',v_appstate='',v_area_id='',v_belong='',v_borough_id='',
v_borough_name='',v_broker_id='',v_check_name='',v_check_time='',v_consigner_id='',
v_down_time='',v_draw_url='',v_house_age='',v_house_desc='',v_house_drawing='',
v_house_feature='',v_house_fitment='',v_house_floor='',v_house_hall='',v_house_no='',
v_house_price=0.0,v_house_room='',v_house_thumb='',v_house_title='',v_house_toilet='',
v_house_topfloor='',v_house_totalarea=0.0,v_house_toward='',v_house_type='',v_house_veranda='',
v_is_down='',v_is_promote='',v_is_synchro='',v_is_whether='',v_owner_name='',
v_owner_notes='',v_owner_phone='',v_status='',v_submit_name='',v_submit_time='',
v_synchro_time='',v_video='',v_whether_time='',v_create_by='',v_update_by='',
v_area_name='',v_is_new='',
v_log_flag = '',v_log_status = '',v_lot_NO = '';
fetch cur into
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new,
v_log_flag,v_log_status,v_lot_NO;
if done then
leave read_loop;
end if;
set v_count = (select count(*) from zj_front_ihouse.zj_front_housesell where id = v_id);
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
if v_log_flag = 'i' then
if v_count != 0 then
update zj_front_ihouse.zj_front_housesell set
`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,
`address`=v_address,`appstate`=v_appstate,`area_id`=v_area_id,`belong`=v_belong,`borough_id`=v_borough_id,
`borough_name`=v_borough_name,`broker_id`=v_broker_id,`check_name`=v_check_name,`check_time`=v_check_time,`consigner_id`=v_consigner_id,
`down_time`=v_down_time,`draw_url`=v_draw_url,`house_age`=v_house_age,`house_desc`=v_house_desc,`house_drawing`=v_house_drawing,
`house_feature`=v_house_feature,`house_fitment`=v_house_fitment,`house_floor`=v_house_floor,`house_hall`=v_house_hall,`house_no`=v_house_no,
`house_price`=v_house_price,`house_room`=v_house_room,`house_thumb`=v_house_thumb,`house_title`=v_house_title,`house_toilet`=v_house_toilet,
`house_topfloor`=v_house_topfloor,`house_totalarea`=v_house_totalarea,`house_toward`=v_house_toward,`house_type`=v_house_type,`house_veranda`=v_house_veranda,
`is_down`=v_is_down,`is_promote`=v_is_promote,`is_synchro`=v_is_synchro,`is_whether`=v_is_whether,`owner_name`=v_owner_name,
`owner_notes`=v_owner_notes,`owner_phone`=v_owner_phone,`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,
`synchro_time`=v_synchro_time,`video`=v_video,`whether_time`=v_whether_time,`create_by`=v_create_by,`update_by`=v_update_by,
`area_name`=v_area_name,`is_new`=v_is_new
where id = v_id;
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
else
insert into zj_front_ihouse.zj_front_housesell (
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`
)values (
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new
);
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
end if;
elseif v_log_flag = 'd' then
if v_count !=0 then
delete from zj_front_ihouse.zj_front_housesell where id = v_id;
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
end if;
elseif v_log_flag = 'u' then
if v_count != 0 then
update zj_front_ihouse.zj_front_housesell set
`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,
`address`=v_address,`appstate`=v_appstate,`area_id`=v_area_id,`belong`=v_belong,`borough_id`=v_borough_id,
`borough_name`=v_borough_name,`broker_id`=v_broker_id,`check_name`=v_check_name,`check_time`=v_check_time,`consigner_id`=v_consigner_id,
`down_time`=v_down_time,`draw_url`=v_draw_url,`house_age`=v_house_age,`house_desc`=v_house_desc,`house_drawing`=v_house_drawing,
`house_feature`=v_house_feature,`house_fitment`=v_house_fitment,`house_floor`=v_house_floor,`house_hall`=v_house_hall,`house_no`=v_house_no,
`house_price`=v_house_price,`house_room`=v_house_room,`house_thumb`=v_house_thumb,`house_title`=v_house_title,`house_toilet`=v_house_toilet,
`house_topfloor`=v_house_topfloor,`house_totalarea`=v_house_totalarea,`house_toward`=v_house_toward,`house_type`=v_house_type,`house_veranda`=v_house_veranda,
`is_down`=v_is_down,`is_promote`=v_is_promote,`is_synchro`=v_is_synchro,`is_whether`=v_is_whether,`owner_name`=v_owner_name,
`owner_notes`=v_owner_notes,`owner_phone`=v_owner_phone,`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,
`synchro_time`=v_synchro_time,`video`=v_video,`whether_time`=v_whether_time,`create_by`=v_create_by,`update_by`=v_update_by,
`area_name`=v_area_name,`is_new`=v_is_new
where id = v_id;
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
else
insert into zj_front_ihouse.zj_front_housesell (
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`
)values (
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new
);
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
leave read_loop;
end if;
end if;
end if;
end loop;
close cur;
if v_cerror = 0 then
delete from zj_front_ihouse.zj_front_housesell_swp
where lot_NO = p_lot_NO and (log_flag = 'i' or log_flag = 'd' or log_flag = 'u');
if t_error = 1 then
set v_cerror = v_cerror + t_error;
rollback;
end if;
end if;
if v_cerror = 0 then
commit;
else
rollback;
end if;
end
