#namespace("bi.base")
  #sql("moneystatistics")
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '01') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '01') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,01 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '02') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '02') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,02 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '03') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '03') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,03 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '04') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '04') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,04 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '05') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '05') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,05 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '06') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '06') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,06 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '07') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '07') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,07 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '08') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '08') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,08 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '09') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '09') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,09 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '10') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '10') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,10 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '11') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '11') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,11 AS month
    UNION ALL
    SELECT (SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_contract as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.order_date, '%Y%m') = CONCAT('2019', '12') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS contractMoney,(SELECT IFNULL(SUM(money), 0) FROM 72crm_crm_receivables as a #if(deptId) LEFT JOIN 72crm_admin_user as b on a.owner_user_id=b.user_id #end WHERE DATE_FORMAT(a.return_time, '%Y%m') = CONCAT('2019', '12') #if(userId) and a.owner_user_id=#para(userId) #end #if(deptId) and b.dept_id=#para(deptId) #end ) AS receivablesMoney,12 AS month
  #end
  #sql ("queryCrmBusinessStatistics")
    SELECT DISTINCT (SELECT COUNT(*) from 72crm_crm_business WHERE scb.status_id = status_id) as  businessNum,
    scb.status_id,IFNULL((SELECT sum(money)  from 72crm_crm_business WHERE scb.status_id = status_id),0)as  total_price,
    scbs.`name`
    from 72crm_crm_business as scb
    LEFT JOIN 72crm_crm_business_product as scbp on scbp.business_id = scb.business_id
    LEFT JOIN 72crm_crm_business_status as scbs on scbs.status_id = scb.status_id
    LEFT JOIN 72crm_admin_user as sau on sau.user_id = scb.owner_user_id
    where  1 = 1
    #if(ownerUserId)
      and scb.owner_user_id = #para(ownerUserId)
    #end
    #if(productId)
      and scbp.product_id = #para(productId)
    #end
    #if(deptId)
      and sau.dept_id = #para(deptId)
    #end
    #if(startTime)
      and unix_timestamp(#para(startTime)) - unix_timestamp(scb.create_time) < 0
    #end
    #if(endTime)
      and unix_timestamp(#para(endTime)) - unix_timestamp(scb.create_time) > 0
    #end
      group by status_id
  #end
  #sql ("queryByUserIdOrYear")
      select co.* from contractview as co LEFT JOIN 72crm_admin_user as sau on co.owner_user_id = sau.user_id
      where 1 = 1
      #if(year)
        and YEAR(co.create_time) = #para(year)
      #end
      #if(month)
        and month(co.create_time) = #para(month)
      #end
      #if(userId)
        and sau.owner_user_id = #para(userId)
      #end
      #if(deptId)
        and sau.dept_id = #para(deptId)
      #end
  #end
  #sql ("queryProductSell")
      SELECT scp.product_id,scpc.name as categoryName , scpc.category_id,
      scp.name as productName, scc.num as contracNum ,
      sau.realname as ownerUserName,sccu.customer_name as customerName ,
      sccp.sales_price as productPrice ,sccp.num as productNum,
      sccp.subtotal as productSubtotal,
      scc.contract_id,
      sccu.customer_id
      FROM 72crm_crm_product as scp
      LEFT JOIN 72crm_crm_contract_product as sccp on sccp.product_id = scp.product_id
      LEFT JOIN 72crm_crm_contract as scc on scc.contract_id = sccp.contract_id
      LEFT JOIN 72crm_admin_user as sau on sau.user_id = scc.owner_user_id
      LEFT JOIN 72crm_crm_customer as sccu on sccu.customer_id = scc.customer_id
		  LEFT JOIN 72crm_crm_product_category as scpc on scpc.category_id = scp.category_id
      where 1 = 1
      #if(startTime)
        and unix_timestamp(#para(startTime)) - unix_timestamp(scc.order_date) < 0
      #end
      #if(endTime)
        and unix_timestamp(#para(endTime)) - unix_timestamp(scc.order_date) > 0
      #end
      #if(userId)
        and sau.user_id = #para(userId)
      #end
      #if(deptId)
        and sau.dept_id = #para(deptId)
      #end
   #end

   #sql ("queryContractByDeptId")
    select '一月' as month,IFNULL(january,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2)/IFNULL(january,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '二月' as month,IFNULL(february,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2)/IFNULL(february,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '三月' as month,IFNULL(march,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2)/IFNULL(march,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '四月' as month,IFNULL(april,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2)/IFNULL(april,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '五月' as month,IFNULL(may,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2)/IFNULL(may,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '六月' as month,IFNULL(june,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2)/IFNULL(june,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '七月' as month,IFNULL(july,0) as achievement,(select IFNULL(SUM(a.money),0) from
    72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2)/IFNULL(july,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '八月' as month,IFNULL(august,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2)/IFNULL(august,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '九月' as month,IFNULL(september,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2)/IFNULL(september,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十月' as month,IFNULL(october,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2)/IFNULL(october,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十一月' as month,IFNULL(november,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2)/IFNULL(november,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十二月' as month,IFNULL(december,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_contract as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2)/IFNULL(december,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
  #end

  #sql ("queryContractByUserId")
    select '一月' as month,IFNULL(january,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2)/IFNULL(january,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '二月' as month,IFNULL(february,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2)/IFNULL(february,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '三月' as month,IFNULL(march,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2)/IFNULL(march,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '四月' as month,IFNULL(april,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2)/IFNULL(april,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '五月' as month,IFNULL(may,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2)/IFNULL(may,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '六月' as month,IFNULL(june,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2)/IFNULL(june,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '七月' as month,IFNULL(july,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2)/IFNULL(july,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '八月' as month,IFNULL(august,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2)/IFNULL(august,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '九月' as month,IFNULL(september,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2)/IFNULL(september,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十月' as month,IFNULL(october,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2)/IFNULL(october,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十一月' as month,IFNULL(november,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2)/IFNULL(november,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十二月' as month,IFNULL(december,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_contract where owner_user_id = #para(userId) and DATE_FORMAT(order_date,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2)/IFNULL(december,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
  #end

  #sql ("queryReceivablesByDeptId")
    select '一月' as month,IFNULL(january,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2)/IFNULL(january,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '二月' as month,IFNULL(february,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2)/IFNULL(february,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '三月' as month,IFNULL(march,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2)/IFNULL(march,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '四月' as month,IFNULL(april,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2)/IFNULL(april,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '五月' as month,IFNULL(may,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2)/IFNULL(may,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '六月' as month,IFNULL(june,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2)/IFNULL(june,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '七月' as month,IFNULL(july,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2)/IFNULL(july,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '八月' as month,IFNULL(august,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2)/IFNULL(august,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '九月' as month,IFNULL(september,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2)/IFNULL(september,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十月' as month,IFNULL(october,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2)/IFNULL(october,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十一月' as month,IFNULL(november,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2)/IFNULL(november,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
    union all
    select '十二月' as month,IFNULL(december,0) as achievement,(select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(a.money),0) from 72crm_crm_receivables as a inner join 72crm_admin_user as b where a.owner_user_id = b.user_id and b.dept_id = #para(deptId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2)/IFNULL(december,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(deptId) and type = 2 and year = #para(year)
  #end

  #sql ("queryReceivablesByUserId")
    select '一月' as month,IFNULL(january,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'01') and check_status = 2)/IFNULL(january,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '二月' as month,IFNULL(february,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'02') and check_status = 2)/IFNULL(february,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '三月' as month,IFNULL(march,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'03') and check_status = 2)/IFNULL(march,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '四月' as month,IFNULL(april,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'04') and check_status = 2)/IFNULL(april,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '五月' as month,IFNULL(may,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'05') and check_status = 2)/IFNULL(may,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '六月' as month,IFNULL(june,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'06') and check_status = 2)/IFNULL(june,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '七月' as month,IFNULL(july,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'07') and check_status = 2)/IFNULL(july,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '八月' as month,IFNULL(august,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'08') and check_status = 2)/IFNULL(august,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '九月' as month,IFNULL(september,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'09') and check_status = 2)/IFNULL(september,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十月' as month,IFNULL(october,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'10') and check_status = 2)/IFNULL(october,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十一月' as month,IFNULL(november,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'11') and check_status = 2)/IFNULL(november,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
    union all
    select '十二月' as month,IFNULL(december,0) as achievement,(select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2) as receivables,IFNULL(ROUND((select IFNULL(SUM(money),0) from 72crm_crm_receivables where owner_user_id = #para(userId) and DATE_FORMAT(return_time,'%Y%m') = CONCAT(#para(year),'12') and check_status = 2)/IFNULL(december,0)*100,2),0) as rate from 72crm_crm_achievement where obj_id = #para(userId) and type = 3 and year = #para(year)
  #end
  #sql("salesTrend")
    select #para(beginTime) as type,IFNULL(SUM(money),0) as contractMoneys, (SELECT IFNULL(SUM(money),0) FROM 72crm_crm_receivables WHERE DATE_FORMAT( return_time,#para(sqlDateFormat) ) = #para(beginTime)  and check_status = 2 AND owner_user_id in (#for(x:userIds) #(for.index == 0 ? "" : ",") #para(x) #end)) as receivablesMoneys FROM 72crm_crm_contract as ccco where DATE_FORMAT(ccco.order_date,#para(sqlDateFormat))=#para(beginTime) and ccco.check_status = 2 AND owner_user_id in (#for(x:userIds) #(for.index == 0 ? "" : ",") #para(x) #end)
  #end
#end
