user = """
SELECT * FROM
(SELECT id as patient_id,name,hosp_number,org_number,number,survey_time,if(sex = 1,"a","b") AS sex,birth,age,phone1,phone2,
if(gastric_cancer=0,"b","a") AS gastric_cancer,if(cancer_a = 0,"b","a") AS cancer_a,
cancer_b,if(orther_tumour=0,"b","a") AS orther_tumour,
if(tumour_a = 0,"b","a") AS tumour_a,tumour_b,step
FROM tb_patient WHERE step = 0
 AND hosp_number IN (SELECT a.number FROM (
SELECT number,name FROM shchyy4dw_geca.tb_hosp
UNION
SELECT number,name FROM shchyy4dw.tb_hosp
UNION
SELECT number,name FROM shchyy4dw_qg.tb_hosp) a
left JOIN
shchyy_dw.tmp_tb_hosp_area b
ON a.name=b.name
WHERE a.NAME IS NOT null and b.area in ('上海市','浙江省','江苏省','安徽省')
)
) AS a
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS b ON a.patient_id = b.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS c ON a.patient_id = c.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS d ON a.patient_id = d.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS e ON a.patient_id = e.patient_id
#第一次随访
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS f ON a.patient_id = f.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS g ON a.patient_id = g.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS h ON a.patient_id = h.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS i ON a.patient_id = i.patient_id
#第二次随访
LEFT join
(SELECT patient_id,g17,pg1,pg2,igg
FROM tb_lab_inspect
WHERE step = 0) AS j ON a.patient_id = j.patient_id
LEFT JOIN
(select patient_id,score,(case grade when 1 then "a" when 2 then "b" when 3 then "c" end) AS grade
from tb_risk_assess WHERE step = 0) AS k ON a.patient_id = k.patient_id
LEFT JOIN
(select patient_id, if(is_inspect=1,"a","b") AS is_inspect,inspect_time,
(case inspect_way when 1 then "a" when 2 then "b" when 3 then "c" end) AS inspect_way,
if(capsule=1,"a","b") AS capsule,
precise_way,if(diagnose_result=1,"a","b") AS diagnose_result,diagnose_info
from tb_gastro_inspect where step = 0) AS l ON a.patient_id = l.patient_id
LEFT JOIN
(select patient_id,continue_flup,flup_time,if(content_type,"a","b") as content_type
from tb_flup_plan WHERE step = 1) AS m ON a.patient_id = m.patient_id
# limit 12400,100
;
"""


def hosp(id):
    return "select tb_patient.id,tb_patient.hosp_number,tmp_tb_hosp_apply.hosp_name,tmp_tb_hosp_area.area from shchyy_dw.tb_patient join shchyy.tmp_tb_hosp_apply join shchyy.tmp_tb_hosp_area on right(shchyy_dw.tb_patient.hosp_number,4)=shchyy.tmp_tb_hosp_apply.hosp_number and shchyy.tmp_tb_hosp_apply.hosp_name=shchyy.tmp_tb_hosp_area.name where shchyy_dw.tb_patient.id=%s" % id;