lh_firm_former/src/views/statistics/js/selectDBDSRW.js

import en from "@/locales/bpmn-js/en"

/* 统计的合格率配置信息，  如公正性，  一年需要完成4次， 当前完成2次的话，则为50%完成率。 */
export const getConfig1 = (begin,end)=>{
  return "select * FROM t_tjpzb"

}
export const getJiaoYanObj1 = (end)=>{
  // // return "select a.she_bei_bian_hao_,a.t_mjsbjdxzjhzbZhName, a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,ifnull(b.t_mjsbjdxzjhzbS,0) as t_mjsbjdxzjhzbS FROM (select t_sbdj.she_bei_shi_bie_h as t_mjsbjdxzjhzbZhName, zb.she_bei_ming_chen as t_mjsbjdxzjhzbName,zb.she_bei_bian_hao_, COUNT(zb.she_bei_ming_chen) as t_mjsbjdxzjhzb from (select t_mjsbjdxzjhzb.* from t_mjsbjdxzjhzb join t_mjsbjdxzjh on t_mjsbjdxzjhzb.parent_id_=t_mjsbjdxzjh.id_ where t_mjsbjdxzjh.ji_hua_nian_du_='"+end+"' and t_mjsbjdxzjh.shi_fou_guo_shen_='1' ) zb LEFT JOIN t_sbdj on zb.she_bei_bian_hao_ = t_sbdj.id_ GROUP BY zb.she_bei_bian_hao_ HAVING COUNT(zb.she_bei_bian_hao_)>=1) as a left join (select t_sbjdxzzx.yi_qi_bian_hao_, COUNT(t_sbjdxzzx.yi_qi_bian_hao_) as t_mjsbjdxzjhzbS from t_sbjdxzzx join (select t_sbjdxzqr.* from t_sbjdxzqr join t_mjsbjdxzjh on t_sbjdxzqr.ji_hua_zong_wai_j=t_mjsbjdxzjh.id_ where t_mjsbjdxzjh.ji_hua_nian_du_='"+end+"' and t_mjsbjdxzjh.shi_fou_guo_shen_='1'  and t_sbjdxzqr.shi_fou_guo_shen_='1') jh on jh.id_ =t_sbjdxzzx.parent_id_ where  DATE(t_sbjdxzzx.you_xiao_qi_zhi_) >= DATE(t_sbjdxzzx.xiao_zhun_ri_qi_) GROUP BY t_sbjdxzzx.yi_qi_bian_hao_ HAVING COUNT(t_sbjdxzzx.yi_qi_bian_hao_)>=1 )as b on a.she_bei_bian_hao_=b.yi_qi_bian_hao_"
  return "select a.she_bei_bian_hao_,a.t_mjsbjdxzjhzbZhName, a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,ifnull(b.t_mjsbjdxzjhzbS,0) as t_mjsbjdxzjhzbS FROM (select t_sbdj.she_bei_shi_bie_h as t_mjsbjdxzjhzbZhName, zb.she_bei_ming_chen as t_mjsbjdxzjhzbName,zb.she_bei_bian_hao_, COUNT(zb.she_bei_ming_chen) as t_mjsbjdxzjhzb from (select t_mjsbjdxzjhzb.* from t_mjsbjdxzjhzb join t_mjsbjdxzjh on t_mjsbjdxzjhzb.parent_id_=t_mjsbjdxzjh.id_ where t_mjsbjdxzjh.ji_hua_nian_du_='"+end+"' and t_mjsbjdxzjh.shi_fou_guo_shen_='1' ) zb LEFT JOIN t_sbdj on zb.she_bei_bian_hao_ = t_sbdj.id_ GROUP BY zb.she_bei_bian_hao_ HAVING COUNT(zb.she_bei_bian_hao_)>=1) as a left join (select t_sbjdxzzx.yi_qi_bian_hao_, COUNT(t_sbjdxzzx.yi_qi_bian_hao_) as t_mjsbjdxzjhzbS from t_sbjdxzzx join (select t_sbjdxzqr.* from t_sbjdxzqr join t_mjsbjdxzjh on t_sbjdxzqr.ji_hua_zong_wai_j=t_mjsbjdxzjh.id_ where t_mjsbjdxzjh.ji_hua_nian_du_='"+end+"' and t_mjsbjdxzjh.shi_fou_guo_shen_='1'  and t_sbjdxzqr.shi_fou_guo_shen_='1') jh on jh.id_ =t_sbjdxzzx.parent_id_  GROUP BY t_sbjdxzzx.yi_qi_bian_hao_ HAVING COUNT(t_sbjdxzzx.yi_qi_bian_hao_)>=1 )as b on a.she_bei_bian_hao_=b.yi_qi_bian_hao_"
}
export const gethechaObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select	a.t_sbhcjlbZhname,	a.t_sbhcjlbName,	a.t_sbhcjlb,	ifnull( b.t_sbhcjlbS, 0 ) AS t_sbhcjlbS FROM	(	SELECT	t_sbdj.id_ AS sbid,t_sbdj.she_bei_ming_cheng_ AS t_sbhcjlbZhname,	t_sbdj.she_bei_shi_bie_h AS t_sbhcjlbName,	COUNT( zb.yi_qi_she_bei_min ) AS t_sbhcjlb 	FROM	(select t_yqsbhcjh.* from t_yqsbhcjh join t_sbhcjh on t_sbhcjh.id_=t_yqsbhcjh.parent_id_ where t_sbhcjh.shi_fou_guo_shen_='1' and t_sbhcjh.nian_fen_='"+end+"') zb	LEFT JOIN t_sbdj ON zb.yi_qi_she_bei_min = t_sbdj.id_	GROUP BY	zb.yi_qi_she_bei_min 	HAVING	COUNT( zb.yi_qi_she_bei_min )>= 1 	) AS a	LEFT JOIN ( SELECT t_sbqjhcjlb.she_bei_bian_hao_ as sbbh, t_sbqjhcjlb.sheng_xiao_ri_qi_, COUNT( t_sbqjhcjlb.she_bei_bian_hao_ ) AS t_sbhcjlbS FROM t_sbqjhcjlb join t_sbhcjh on t_sbqjhcjlb.ji_hua_zong_wai_j=t_sbhcjh.id_ WHERE t_sbhcjh.shi_fou_guo_shen_='1' and t_sbhcjh.nian_fen_='"+end+"' and t_sbqjhcjlb.shi_fou_guo_shen_ = '1' GROUP BY t_sbqjhcjlb.she_bei_bian_hao_ HAVING COUNT( t_sbqjhcjlb.she_bei_bian_hao_ )>= 1 ) AS b ON a.sbid = b.sbbh "
}
// export const getnengliObj = (end)=>{
//   // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
//   return "select a.enname,ifnull(a.num,0) as num,  ifnull(b.num,0) as numS from(select xiang_mu_ as enname, COUNT(jian_ce_biao_zhun) as num from t_ndnlyzsysjbdjhzb where YEAR(yu_ji_shi_jian_)="+end+" GROUP BY jian_ce_biao_zhun HAVING COUNT(jian_ce_biao_zhun)>=1) as a LEFT JOIN (select jian_ce_biao_zhun as enname, COUNT(jian_ce_biao_zhun) as num from t_ndnlyzsysjbdjhzb as c LEFT JOIN t_ndnlyzsysjbdjhb as d on c.parent_id_ = d.id_ where YEAR(c.create_time_)="+end+" and d.shi_fou_guo_shen_='1' GROUP BY jian_ce_biao_zhun HAVING COUNT(jian_ce_biao_zhun)>=1) as b on a.enname = b.enname"
// }
export const getnengliObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select COUNT(t_ndnlyzsysjbdjhzb.id_) as num from t_ndnlyzsysjbdjhzb LEFT JOIN t_nlyzsysjbdhdsyxhcb on t_ndnlyzsysjbdjhzb.id_=t_nlyzsysjbdhdsyxhcb.xiang_mu_ming_che WHERE t_nlyzsysjbdhdsyxhcb.shi_fou_guo_shen_ = '1' and  YEAR(t_ndnlyzsysjbdjhzb.create_time_)= "+end+" UNION all select COUNT(id_) as snum from t_ndnlyzsysjbdjhzb where  YEAR(create_time_)="+end
}
export const getbiaozhunObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select a.name as name, a.num ,IFNULL(b.numS,0) as numS from	(select t_bzwzqjhcjhzb.wu_zhi_ming_cheng as wlid,t_bzwzqjhcjhzb.zhuan_huan_ as name, COUNT(t_bzwzqjhcjhzb.wu_zhi_ming_cheng) as num from t_bzwzqjhcjhzb join t_bzwzqjhcjhb on t_bzwzqjhcjhb.id_=t_bzwzqjhcjhzb.parent_id_ where t_bzwzqjhcjhb.nian_du_='"+end+"' and t_bzwzqjhcjhb.shi_fou_guo_shen_='1' GROUP BY t_bzwzqjhcjhzb.wu_zhi_ming_cheng HAVING COUNT(t_bzwzqjhcjhzb.wu_zhi_ming_cheng)>=1) as a LEFT JOIN (select x.wai_guan_biao_wu_ as wlid,x.name as name,COUNT(x.wai_guan_biao_wu_) as numS from (select c.shi_fou_guo_shen_,d.id_,d.parent_id_,c.zhuan_huan_ as name, c.wai_guan_biao_wu_ from t_bzwzqjhcjlb as c JOIN t_bzwzqjhcjhzb as d on c.guan_jian_zi_duan = d.id_) x join t_bzwzqjhcjhb on t_bzwzqjhcjhb.id_ = x.parent_id_ where t_bzwzqjhcjhb.nian_du_='"+end+"' and t_bzwzqjhcjhb.shi_fou_guo_shen_='1' and x.shi_fou_guo_shen_='1' GROUP BY x.wai_guan_biao_wu_ HAVING COUNT(x.wai_guan_biao_wu_)>=1) as b on a.wlid = b.wlid"
}
export const getbiaozhunTObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select MONTH(shi_shi_shi_jian_) as time from t_bzwzqjhcjhzb where YEAR(shi_shi_shi_jian_) ="+end+" ORDER BY shi_shi_shi_jian_ ASC LIMIT 1"
}
export const getbzJunZhuObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select a.name as name, a.num ,IFNULL(b.numS,0) as numS from	(select t_bzjzhcjhzb.wu_zhi_ming_cheng as wlid,t_bzjzhcjhzb.zhuan_huan_ as name, COUNT(t_bzjzhcjhzb.wu_zhi_ming_cheng) as num from t_bzjzhcjhzb join t_bzjzhcjhb on t_bzjzhcjhb.id_=t_bzjzhcjhzb.parent_id_ where t_bzjzhcjhb.nian_du_='"+end+"' and t_bzjzhcjhb.shi_fou_guo_shen_='1' GROUP BY t_bzjzhcjhzb.wu_zhi_ming_cheng HAVING COUNT(t_bzjzhcjhzb.wu_zhi_ming_cheng)>=1) as a LEFT JOIN (select x.wai_guan_biao_wu_ as wlid,x.name as name,COUNT(x.wai_guan_biao_wu_) as numS from (select c.shi_fou_guo_shen_,d.id_,d.parent_id_,c.zhuan_huan_ as name, c.wai_guan_biao_wu_ from t_bzjzqjhcjl as c JOIN t_bzjzhcjhzb as d on c.guan_jian_zi_duan = d.id_) x join t_bzjzhcjhb on t_bzjzhcjhb.id_ = x.parent_id_ where t_bzjzhcjhb.nian_du_='"+end+"' and t_bzjzhcjhb.shi_fou_guo_shen_='1' and x.shi_fou_guo_shen_='1' GROUP BY x.wai_guan_biao_wu_ HAVING COUNT(x.wai_guan_biao_wu_)>=1) as b on a.wlid = b.wlid"
}
export const getbzJunZhuTObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select MONTH(shi_shi_shi_jian_) as time from t_bzjzhcjhzb where YEAR(shi_shi_shi_jian_) ="+end+" ORDER BY shi_shi_shi_jian_ ASC LIMIT 1"
}
export const getbzXiBaoObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select a.name as name, a.num ,IFNULL(b.numS,0) as numS from	 (select t_bzxbqjhcjhzbn.wu_zhi_ming_cheng as wlid,t_bzxbqjhcjhzbn.zhuan_huan_ as name, COUNT(t_bzxbqjhcjhzbn.wu_zhi_ming_cheng) as num from t_bzxbqjhcjhzbn join t_bzxbqjhcjh on t_bzxbqjhcjh.id_=t_bzxbqjhcjhzbn.parent_id_ where t_bzxbqjhcjh.nian_du_='"+end+"' and t_bzxbqjhcjh.shi_fou_guo_shen_='1' GROUP BY t_bzxbqjhcjhzbn.wu_zhi_ming_cheng HAVING COUNT(t_bzxbqjhcjhzbn.wu_zhi_ming_cheng)>=1) as a LEFT JOIN (select x.wai_guan_biao_wu_ as wlid,x.name as name,COUNT(x.wai_guan_biao_wu_) as numS from (select c.shi_fou_guo_shen_,d.id_,d.parent_id_,c.zhuan_huan_ as name, c.wai_guan_biao_wu_ from t_bzxbhcjlb as c JOIN t_bzxbqjhcjhzbn as d on c.guan_jian_zi_duan = d.id_) x join t_bzxbqjhcjh on t_bzxbqjhcjh.id_ = x.parent_id_ where t_bzxbqjhcjh.nian_du_='"+end+"' and t_bzxbqjhcjh.shi_fou_guo_shen_='1' and x.shi_fou_guo_shen_='1' GROUP BY x.wai_guan_biao_wu_ HAVING COUNT(x.wai_guan_biao_wu_)>=1) as b on a.wlid = b.wlid"
}
export const getbzXiBaoTObj1 = (end)=>{
  // return "select a.t_mjsbjdxzjhzbName,a.t_mjsbjdxzjhzb,b.t_mjsbjdxzjhzbS FROM (select she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb from t_mjsbjdxzjhzb where YEAR(create_time_)='2022' GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1) as a left join  (select she_bei_ming_chen, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS from t_mjsbjdxzjhzb where YEAR(create_time_)= '2022' AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 )as b on a.t_mjsbjdxzjhzbName=b.she_bei_ming_chen"
  return "select MONTH(shi_shi_shi_jian_) as time from t_bzxbqjhcjhzbn where YEAR(shi_shi_shi_jian_) ="+end+" ORDER BY shi_shi_shi_jian_ ASC LIMIT 1"
}
export const DBData1 = (begin,end,scope)=>{
  // return "select tt.*,cast(1-(t_gdyrqcwt4"+begin+"/t_gdyrqcwt"+begin+")  AS decimal(32,2)) as t_gdyrqcwt6"+begin+
  // return "select tt.* "+

  // // ",cast(1-(t_gdyrqcwt4"+end+"/t_gdyrqcwt"+end+") AS decimal(32,2)) as t_gdyrqcwt6"+end+
  // // ",cast(1-(t_gdyrqcwt50"+begin+"/t_gdyrqcwt"+begin+") AS decimal(32,2)) as t_gdyrqcwt5"+begin+",cast(1-(t_gdyrqcwt50"+end+"/t_gdyrqcwt"+end+") AS decimal(32,2)) as t_gdyrqcwt5"+end+
  // // ",cast(ifnull(1-(t_gdyrqcwt2"+begin+"/t_gdyrqcwt"+begin+"),0)+0.01 AS decimal(32,2)) as t_myddc5"+begin+",cast(ifnull(1-(t_gdyrqcwt2"+end+"/t_gdyrqcwt"+end+"),0)+0.01 AS decimal(32,2)) as t_myddc5"+end+
  // // ",cast(ifnull(1-(t_gdyrqcwt3"+begin+"/t_gdyrqcwt"+begin+"),0)+0.01 AS decimal(32,2)) as t_myddc6"+begin+",cast(ifnull(1-(t_gdyrqcwt3"+end+"/t_gdyrqcwt"+end+"),0)+0.01 AS decimal(32,2)) as t_myddc6"+end+
  // // ",cast(ifnull((t_gdyrqcwt50"+begin+"/t_gdyrqcwt"+begin+"),0)+0.01 AS decimal(32,2)) as t_myddc4"+begin+",cast(ifnull((t_gdyrqcwt50"+end+"/t_gdyrqcwt"+end+"),0)+0.01 AS decimal(32,2)) as t_myddc4"+end+
  // " FROM("+s1jianCe(begin,end)+
  // " join "+s1renwu(begin,end,scope)+
  // " join "+s2jianCe(begin,end)+
  // " join "+s2manYiDu(begin,end,scope)+
  // " join "+s3tousu(begin,end,scope)+
  // " join "+s4renYuanPeiXun(begin,end,scope)+
  // " join "+s5renYuanJianDu(begin,end,scope)+
  // " join "+s6sheBeiWeiHu(begin,end)+
  // " join "+s7sheBeiJiaoZhun(begin,end)+
  // " join "+s8sheBeiHeCha(begin,end)+
  // ") tt"
  return "select tt.* "+
  " FROM("+s1jianCe(begin,end)+
  " join "+s1renwu(begin,end,scope)+
  " join "+s2jianCe(begin,end)+
  " join "+s2manYiDu(begin,end,scope)+
  " join "+s3tousu(begin,end,scope)+
  " join "+s4renYuanPeiXun(begin,end,scope)+
  " join "+s5renYuanJianDu(begin,end,scope)+
  " join "+s9neiBu(begin,end,scope)+
  " join "+s12fengXian(begin,end,scope)+
  " join "+s14bufuhexiang(begin,end,scope)+
  " join "+s18gangqian(begin,end,scope)+
  // " join "+s6sheBeiWeiHu(begin,end)+
  // " join "+s7sheBeiJiaoZhun(begin,end)+
  // " join "+s8sheBeiHeCha(begin,end)+
  ") tt"
}
/* 拼接通用条件，选用 */
function PAT(val){
  return "shi_fou_guo_shen_ = '1' AND YEAR ( create_time_ ) = '" + val+"'"
}
function PAT_SATISFACTION(val){
  return "man_yi_du_ = '满意' AND YEAR ( create_time_ ) = '" + val+"'"
}
function PAT_YEAR(val){
  return " YEAR ( create_time_ ) = '" + val+"'"
}
function PAT_TASK(val,choose){
  
  return choose?"shi_fou_guo_shen_ = '1' AND YEAR( create_time_ ) = '"+val+"' AND DATE(update_time_) BETWEEN DATE( create_time_ ) and DATE( qi_wang_wan_cheng ) ":"shi_fou_guo_shen_ = '1' AND YEAR( create_time_ ) = '"+val+"'"
}
function PAT_CHOOSE(val,choose){
  return choose + " AND YEAR( create_time_ ) = '"+val+"'"

}
/* 工具函数参数
1、时间条件
2、表名
3、where条件
4、连接与连接条件
  (0) on 1=1 JOIN  非结束的sql语句
  (1) on 1=1  仅被连接， 如当前方法最后一个sql语句
   */
function tool(val,tableName,where,join){
  let alias = tableName+val //别名
 return "( SELECT COUNT( id_ ) AS "
 +alias
 +" FROM "
 +tableName
 +" WHERE "
 +where
 +" ) "
 +alias
 + (join==0?" on 1=1 JOIN ":" on 1=1 ")
}
function toolTask(val,tableName,where,join){
  let alias = tableName+val //别名
 return "( SELECT COUNT( id_ ) AS "
 +alias
 +" FROM "
 +tableName
 +" WHERE "
 +where
 +" ) "
 +alias
 + (join==0?" on 1=1 JOIN ":" on 1=1 ")
}
function toolSame(val,tableName,where,join){
  let alias = tableName+'S'+val //别名
 return "( SELECT COUNT( id_ ) AS "
 +alias
 +" FROM "
 +tableName
 +" WHERE "
 +where
 +" ) "
 +alias
 + (join==0?" on 1=1 JOIN ":" on 1=1 ")
}
/* 任务完成 */
function s1renwu(begin,end,scope){
  return "( SELECT COUNT( id_ ) AS t_lhjczb"+end+" FROM t_lhjczb "+ "WHERE zhuang_tai_ = '已完成' and shi_fou_ji_shi_ = '是' AND YEAR( create_time_ ) = "+end+") s1renwu1  JOIN"+
  "( SELECT COUNT( id_ ) AS t_lhjcz"+end+" FROM t_lhjczb "+ "WHERE zhuang_tai_ = '已完成' and shi_fou_ji_shi_ = '否' AND YEAR( create_time_ ) = "+end+") s1renwu2  JOIN"+
  "( SELECT COUNT( id_ ) AS t_lhjc"+end+" FROM t_lhjczb "+ "WHERE (zhuang_tai_ != '已完成' or shi_fou_ji_shi_ ='' or shi_fou_ji_shi_ is null)  AND YEAR( create_time_ ) = "+end+") s1renwu3  JOIN"+
  "( SELECT COUNT( id_ ) AS t_lhjczbS"+end+" FROM t_lhjczb WHERE YEAR ( create_time_ ) = "+end+" ) s1renwu4  "
 
  }
/* 检测 */
function s1jianCe(begin,end){
  // 委托检测数
  return "SELECT *  FROM (select COUNT( id_ ) AS t_gdyrqcwt"+end+" from t_gdyrgpjc WHERE YEAR( create_time_ ) = "+end+" union all select id_ from t_gdyrqcjc WHERE YEAR( create_time_ ) = "+end+") s1jianCe1  JOIN "+
  "( select count(id_) AS t_gdyrqcwt6"+end+" from t_gdyrgpjc where qi_wang_wan_cheng>fa_fang_biao_zhi_ and wei_tuo_id_ in (select id_ from t_gdyrqcwt where YEAR( create_time_ ) = '"+end+"')  ) s1jianCe12 on 1=1"
  }
  /* 检测 */
function s2jianCe(begin,end){
  //v3
  return "( SELECT COUNT( id_ ) AS t_mjjcbg"+end+" FROM t_bgffdjb "+ "WHERE bao_gao_xiu_gai_j = '有' AND YEAR( create_time_ ) = "+end+" ) s2jianCe1 JOIN"+
  "( SELECT COUNT( id_ ) AS t_mjjcbgS"+end+" FROM t_bgffdjb WHERE  YEAR ( create_time_ ) = "+end+" ) s2jianCe2 "

  }
 /* 满意度 */
 function s2manYiDu(begin,end,scope){
  return "( SELECT COUNT(id_) as t_khmydtjbnum"+end+",sum(ping_fen_1_)+sum(ping_fen_2_)+sum(ping_fen_3_)+sum(ping_fen_4_)+sum(ping_fen_5_)  AS t_khmydtjb"+end+" FROM t_khmyddcb WHERE wen_juan_='是' and YEAR( create_time_ ) = "+end+" ) s2manYiDu1  JOIN"+
  "( SELECT COUNT( id_ ) AS t_khmydtjbS"+end+" FROM t_khmydtjb WHERE  YEAR ( create_time_ ) = "+end+" ) s2manYiDu2 "
}
/* 投诉 */
function s3tousu(begin,end,scope){
  return "( SELECT COUNT( id_ ) AS t_complaint"+end+" FROM t_complaint WHERE zhuang_tai_='已完成' and you_wu_you_xiao_ = '有效投诉' and YEAR ( create_time_ ) = "+end+" ) s3tousu1  JOIN "+
  "( SELECT COUNT( id_ ) AS t_complaintS"+end+" FROM t_complaint WHERE zhuang_tai_='已完成' and you_wu_you_xiao_ = '有效投诉' and chu_li_shi_jian_='<48h' and YEAR ( create_time_ ) = "+end+" ) s3tousu2  JOIN "+
  "( SELECT COUNT( id_ ) AS t_complainS"+end+" FROM t_complaint WHERE zhuang_tai_='已完成' and you_wu_you_xiao_ = '有效投诉' and chu_li_jie_guo_sh='已通知' and YEAR ( create_time_ ) = "+end+" ) s3tousu3  JOIN "+
  "( SELECT COUNT( id_ ) AS t_mjwtsqb"+end+" FROM t_lhjczb WHERE  YEAR ( create_time_ ) = "+end+" ) s3weituo1 JOIN"+
  "( SELECT COUNT( id_ ) AS t_comwt"+end+" FROM t_complaint WHERE zhuang_tai_='已完成' and you_wu_you_xiao_ = '有效投诉' and YEAR ( create_time_ ) = "+end+" ) s3weituo2"
  // "( SELECT COUNT( id_ ) AS t_comwt"+end+" FROM t_complaint WHERE zhuang_tai_='已完成' and (guan_lian_yang_pi !='' or guan_lian_yang_pi is not null) and you_wu_you_xiao_ = '有效投诉' and YEAR ( create_time_ ) = "+end+" ) s3weituo2"
 }
 /* 人员培训 */
function s4renYuanPeiXun(begin,end,scope){
  return "(SELECT COUNT( t_lhrypxjlb.id_ ) AS t_ryywpxjl"+end+" FROM t_lhrypxjlb join t_lhndrypxjh on t_lhndrypxjh.id_= t_lhrypxjlb.ji_hua_zong_wai_j WHERE t_lhrypxjlb.shi_fou_guo_shen_='1' and t_lhndrypxjh.shi_fou_guo_shen_='1' and t_lhndrypxjh.nian_du_= '"+end+"') s4renYuanPeiXun1  JOIN "+
  "( SELECT COUNT( t_ndrypxjhzb.id_ ) AS t_ryywpxjlS"+end+" FROM t_ndrypxjhzb join t_lhndrypxjh on t_ndrypxjhzb.parent_id_ = t_lhndrypxjh.id_ WHERE t_lhndrypxjh.shi_fou_guo_shen_='1' and t_lhndrypxjh.nian_du_= '"+end+"' ) s4renYuanPeiXun2"
 }
 /* 人员监督 */
function s5renYuanJianDu(begin,end, scope){
  return "( SELECT COUNT( id_ ) AS t_zljd"+end+" FROM t_lhryjdjlb WHERE shi_fou_guo_shen_= '1' AND YEAR ( bian_zhi_shi_jian ) = "+end+" ) s5renYuanJianDu1  JOIN "+
  "( SELECT COUNT( id_ ) AS t_zljdS"+end+" FROM t_lhryjdjlb WHERE  YEAR ( bian_zhi_shi_jian ) = "+end+" ) s5renYuanJianDu2"
 }
/* 设备维护 */
function s6sheBeiWeiHu(begin,end){
 return "( SELECT COUNT( id_ ) AS t_yqsbwhjhxmt"+begin+" FROM t_yqsbwhjhxmt WHERE YEAR( create_time_ ) = "+begin+" ) s6sheBeiWeiHu1  JOIN "+
  tool(end,"t_yqsbwhjhxmt",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_whbyxx"+begin+" FROM t_whbyxx WHERE YEAR ( create_time_ ) = "+begin+" ) s6sheBeiWeiHu2  JOIN "+
  tool(end,"t_whbyxx",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_yqsbwhjhxmz"+begin+" FROM t_yqsbwhjhxmz WHERE YEAR( create_time_ ) = "+begin+" ) s6sheBeiWeiHu3  JOIN "+
  tool(end,"t_yqsbwhjhxmz",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_whbyxmxxmz"+begin+" FROM t_whbyxmxxmz WHERE YEAR ( create_time_ ) = "+begin+" ) s6sheBeiWeiHu4  JOIN "+
  tool(end,"t_whbyxmxxmz",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_yqsbwhjhxmy"+begin+" FROM t_yqsbwhjhxmy WHERE YEAR( create_time_ ) = "+begin+" ) s6sheBeiWeiHu5  JOIN "+
  tool(end,"t_yqsbwhjhxmy",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_whbyxxmy"+begin+" FROM t_whbyxxmy WHERE YEAR ( create_time_ ) = "+begin+" ) s6sheBeiWeiHu6  JOIN "+
  tool(end,"t_whbyxxmy",PAT_YEAR(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_yqsbwhjhfb"+begin+" FROM t_yqsbwhjhfb WHERE (shi_fou_guo_shen_ like '%同意%' or shi_fou_guo_shen_='1') and  YEAR ( create_time_ ) = "+begin+" ) s6sheBeiWeiHu7  JOIN "+
  tool(end,"t_yqsbwhjhfb",PAT(end),1)+" JOIN "+
  "( SELECT COUNT( id_ ) AS t_yqsbwhjlfb"+begin+" FROM t_yqsbwhjlfb WHERE (shi_fou_guo_shen_ like '%同意%' or shi_fou_guo_shen_='1') and  YEAR ( create_time_ ) = "+begin+" ) s6sheBeiWeiHu8  JOIN "+
  tool(end,"t_yqsbwhjlfb",PAT(end),1)
}
/* 设备校准 */
function s7sheBeiJiaoZhun(begin,end){
  //v3
  return "(SELECT she_bei_ming_chen as t_mjsbjdxzjhzbName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzb"+end+" FROM t_mjsbjdxzjhzb WHERE YEAR( create_time_ ) = "+end+" GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 ORDER BY she_bei_ming_chen) s7sheBeiJiaoZhun1  JOIN " +
  "(SELECT she_bei_ming_chen as t_mjsbjdxzjhzbSName, COUNT(she_bei_ming_chen) as t_mjsbjdxzjhzbS"+end+" FROM t_mjsbjdxzjhzb WHERE YEAR( create_time_ ) = "+end+" AND DATE(yi_jian_xiao_ri_q) >= DATE(ji_hua_ri_qi_) GROUP BY she_bei_ming_chen HAVING COUNT(she_bei_ming_chen)>=1 ORDER BY she_bei_ming_chen) s7sheBeiJiaoZhun12 "
 }
 /* 设备核查 */
function s8sheBeiHeCha(begin,end){
  return "( SELECT COUNT( id_ ) AS t_sbhcjh"+begin+" FROM t_sbhcjh WHERE shi_fou_guo_shen_ = '1' and YEAR( create_time_ ) = "+begin+" ) s8sheBeiHeCha1  JOIN "+
   tool(end,"t_sbhcjh",PAT(end),1)+" JOIN "+
   "( SELECT COUNT( id_ ) AS t_sbhcjlb"+begin+" FROM t_sbhcjlb WHERE shi_fou_guo_shen_ = '1' and  YEAR ( create_time_ ) = "+begin+" ) s8sheBeiHeCha2  JOIN "+
   tool(end,"t_sbhcjlb",PAT(end),1)
 }
 /* 内部质量控制 */
function s9neiBu(begin,end,scope){
  return "( SELECT COUNT( t_zlkzjlb.id_ ) AS t_mjzlkzxb"+end+" FROM t_zlkzjlb join t_mjzlkzjhb on t_mjzlkzjhb.id_= t_zlkzjlb.ji_hua_zong_wai_j WHERE t_zlkzjlb.shi_fou_guo_shen_='1' and t_mjzlkzjhb.shi_fou_guo_shen_='1' and t_mjzlkzjhb.nian_du_= '"+end+"' ) s9neibu1  JOIN "+
  "(SELECT COUNT( t_mjzlkzxb.id_ ) AS t_mjzlkzxbS"+end+" FROM t_mjzlkzxb join t_mjzlkzjhb on t_mjzlkzxb.parent_id_ = t_mjzlkzjhb.id_ WHERE t_mjzlkzjhb.shi_fou_guo_shen_='1' and t_mjzlkzjhb.nian_du_= '"+end+"') s9neibu2"
  // "( SELECT COUNT( id_ ) AS t_mjzlkzjlb"+end+" FROM t_mjzlkzjlb WHERE length(shi_fou_guo_shen_) >0 AND YEAR ( create_time_ ) = "+end+" ) s9neibu3 join" +
  // "( SELECT COUNT( id_ ) AS t_mjzlkzjlbS"+end+" FROM t_mjzlkzjlb WHERE shi_fou_guo_shen_=1 AND YEAR ( create_time_ ) = "+end+" ) s9neibu4"

 }
 function s12fengXian(begin,end,scope){
  return "(SELECT COUNT( t_mjsyshdfxkzypjjlb.id_ ) AS t_mjsyshdfxsbykzjhxb"+end+" FROM t_mjsyshdfxkzypjjlb join t_mjsyshdfxsbykzjhb on t_mjsyshdfxsbykzjhb.id_= t_mjsyshdfxkzypjjlb.ji_hua_zong_wai_j WHERE t_mjsyshdfxkzypjjlb.shi_fou_guo_shen_='1' and t_mjsyshdfxsbykzjhb.shi_fou_guo_shen_='1' and t_mjsyshdfxsbykzjhb.nian_du_= '"+end+"') s12fengxian1  JOIN " +
  "(SELECT COUNT( t_mjsyshdfxsbykzjhxb.id_ ) AS t_mjsyshdfxsbykzjhxbS"+end+" FROM t_mjsyshdfxsbykzjhxb join t_mjsyshdfxsbykzjhb on t_mjsyshdfxsbykzjhxb.parent_id_ = t_mjsyshdfxsbykzjhb.id_ WHERE t_mjsyshdfxsbykzjhb.shi_fou_guo_shen_='1' and t_mjsyshdfxsbykzjhb.nian_du_= '"+end+"') s12fengxian2 "

 }
 function s14bufuhexiang(begin,end,scope){
  return "(select COUNT(t_bfhxbgyjzcsjlbx.id_) as t_bfhxbgyjzcsjlbx"+end+" from t_bfhxbgyjzcsjlbx where YEAR(t_bfhxbgyjzcsjlbx.create_time_)="+end+") s14bufuhexiang1  JOIN " +
  "(select COUNT(t_bfhxbgyjzcsjlbx.id_) as t_bfhxbgyjzcsjlbxS"+end+" from t_bfhxbgyjzcsjlbx where t_bfhxbgyjzcsjlbx.shi_fou_guo_shen_ = '1' and YEAR(t_bfhxbgyjzcsjlbx.create_time_)="+end+") s14buhexiang2 "

 }
 function s18gangqian(begin,end,scope){
  return "(SELECT COUNT( t_lhrypxjlb.id_ ) AS t_lhgqpxjhzb"+end+" FROM t_lhrypxjlb join t_lhgqpxjh on t_lhgqpxjh.id_= t_lhrypxjlb.ji_hua_zong_wai_j WHERE t_lhrypxjlb.shi_fou_guo_shen_='1' and t_lhgqpxjh.shi_fou_guo_shen_='1' and t_lhgqpxjh.nian_du_= '"+end+"') s18gangqian1  JOIN " +
  "(SELECT COUNT( t_lhgqpxjhzb.id_ ) AS t_lhgqpxjhzbS"+end+" FROM t_lhgqpxjhzb join t_lhgqpxjh on t_lhgqpxjhzb.parent_id_ = t_lhgqpxjh.id_ WHERE (t_lhgqpxjh.shi_fou_guo_shen_='1' or t_lhgqpxjh.shi_fou_guo_shen_='已审批' or t_lhgqpxjh.shi_fou_guo_shen_='已评价' or t_lhgqpxjh.shi_fou_guo_shen_='已确认') and t_lhgqpxjh.nian_du_= '"+end+"') s18gangqian2 "

 }