集成 DataEase 进行数据分析#

-- 关键SQL
select c.*
from opportunity c
left join sys_organization_user sou on c.owner = sou.user_id
where c.organization_id = '100001'
and
(   
    -- 根据数据权限和部门ID过滤数据
    ($DE_PARAM{'$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]' = 'ALL'} or $DE_PARAM{'$f2cde[账户]' = 'admin'})
    or
    ($DE_PARAM{ '$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]' = 'DEPT_CUSTOM'} and $DE_PARAM{ sou.department_id in ($f2cde[OPPORTUNITY_DATA_SCOPE_DEPT_ID])})
    or
    ($DE_PARAM{'$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]'} = 'SELF' and  $DE_PARAM{sou.user_id = '$f2cde[账户]'})  
    or
    1=2
)

-- 示例SQL
select 
c.id,
c.name as '商机名称',
su.name as '负责人', 
sd.name as '部门',
c.amount as '金额',
osc.name as '商机阶段',
cc.name as '客户名称',
IF(sou.enable, '否', '是') as '是否离职',
FROM_UNIXTIME(c.create_time / 1000) as '创建时间',
FROM_UNIXTIME(c.update_time / 1000) as '更新时间', 
FROM_UNIXTIME(c.expected_end_time / 1000) as '结束时间',
FROM_UNIXTIME(c.actual_end_time / 1000) as '实际结束时间',
FROM_UNIXTIME(c.follow_time / 1000) as '跟进时间',
(
    select REPLACE(REPLACE(REPLACE(JSON_ARRAYAGG(p.name), '"', ''), '[', ''), ']', '') 
    from product p 
    where c.products like concat('%', p.id, '%')
) as '产品类型',
(
select f.field_value
from opportunity_field f
    where f.resource_id = c.id and f.field_id = (
        select id from sys_module_field smf where smf.internal_key = 'opportunityCode'
    )
    LIMIT 1
) AS '业务商机编号',
(
    select option.label
    from opportunity_field f
    left join (
        SELECT temp.*, smfb.id as field_id
            from sys_module_field_blob smfb,
            JSON_TABLE(
                smfb.prop, 
                '$.options[*]' COLUMNS (
                    label VARCHAR(255) PATH '$.label',
                    value VARCHAR(50) PATH '$.value'
                )
            ) AS temp
    ) option on f.field_id = option.field_id and option.value = f.field_value
        where f.resource_id = c.id and f.field_id = (
            select id from sys_module_field smf where smf.internal_key = 'opportunitySource'
    )
    LIMIT 1
) AS '商机来源'
from opportunity c
LEFT JOIN sys_organization_user sou on c.owner = sou.user_id  
LEFT JOIN sys_department sd on sd.id = sou.department_id
LEFT JOIN sys_user su on su.id = c.owner 
LEFT JOIN customer cc on c.customer_id = cc.id
LEFT JOIN opportunity_stage_config osc on osc.id = c.stage
where c.organization_id = '100001'
and
(
    -- 根据数据权限和部门ID过滤数据
    ($DE_PARAM{'$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]' = 'ALL'} or $DE_PARAM{'$f2cde[账户]' = 'admin'})
    or
    ($DE_PARAM{ '$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]' = 'DEPT_CUSTOM'} and $DE_PARAM{ sou.department_id in ($f2cde[OPPORTUNITY_DATA_SCOPE_DEPT_ID])})
    or
    ($DE_PARAM{'$f2cde[OPPORTUNITY_DATA_SCOPE_TYPE]'} = 'SELF' and  $DE_PARAM{sou.user_id = '$f2cde[账户]'})  
    or 
    1=2
)