select date_format(current_date, '%Y-%m-%d')                                             `日期`,
       company_name                                                                      `公司名称`,
       project_name                                                                      `项目名称`,
       ks_id                                                                             `快手id`,
       cr.account_id                                                                     `账户id`,
       material_name                                                                     `素材名称`,
       code                                                                              `素材唯一编码`,
       realname                                                                          `运营人员`,
       clip_name                                                                         `设计人员`,
       case when hc_status = 0 then '待审核' when hc_status = 1 then '审核通过' else '审核拒绝' end `汇创后台素材审核状态`,
       case when photo_id is null then '未推送' else '推送' end                               `素材推送状态`,
       concat('`', photo_id, '`')                                                        `视频id`,
       creative_create_time                                                              `素材首次关联创意时间`,
       creative_num                                                                      `素材关联创意数`,
       error_num                                                                         `审核拒绝创意数`
from (select q.*,
             case when q.creative_create_time is null then 0 else s.creative_count end as creative_num,
             case when q.creative_create_time is null then 0 else w.creative_count end as error_num
      from (
               select code,
                      material_name,
                      a.status                    as hc_status,
                      a.create_time               as hc_create_time,
                      clip_id,
                      a.account_id,
                      a.photo_id,
                      min(b.creative_create_time) as creative_create_time
               from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
                     from (select code, material_name, status, create_time
                           from `jeecg-boot`.ctop_material_info
                           where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
                              left join(
                         select material_id, clip_id
                         from `jeecg-boot`.ctop_material_ascription
                     ) b
                                       on a.code = b.material_id
                              left join(
                         select account_id, signature, photo_id, stat_date
                         from ctop_kuaishou_video_get
                         where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
                     ) c
                                       on a.code = c.signature) a
                        left join
                    (select account_id, creative_id, photo_id, status, creative_create_time
                     from `jeecg-boot`.ctop_kuaishou_creative
                     where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
                    on a.account_id = b.account_id and a.photo_id = b.photo_id
               group by a.status, material_name, code, a.create_time, clip_id, a.account_id, a.photo_id) q
               left join
           (
               select code,
                      account_id,
                      case when account_id is null then 0 else c end as creative_count
               from (
                        select code,
                               account_id,
                               count(1) as c
                        from (
                                 select code,
                                        material_name,
                                        a.status               as hc_status,
                                        a.create_time          as hc_create_time,
                                        clip_id,
                                        a.account_id,
                                        a.photo_id,
                                        b.creative_id,
                                        b.status               as creative_status,
                                        b.creative_create_time as creative_create_time
                                 from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
                                       from (select code, material_name, status, create_time
                                             from `jeecg-boot`.ctop_material_info
                                             where create_time >
                                                   date_sub('2021-10-12', INTERVAL 6 Day)) a
                                                left join(
                                           select material_id, clip_id
                                           from `jeecg-boot`.ctop_material_ascription
                                       ) b
                                                         on a.code = b.material_id
                                                left join(
                                           select account_id, signature, photo_id, stat_date
                                           from ctop_kuaishou_video_get
                                           where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
                                       ) c
                                                         on a.code = c.signature) a
                                          left join
                                      (select account_id, creative_id, photo_id, status, creative_create_time
                                       from `jeecg-boot`.ctop_kuaishou_creative
                                       where creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
                                      on a.account_id = b.account_id and a.photo_id = b.photo_id) t
                        group by account_id, material_name, hc_status, hc_create_time, clip_id, code, photo_id) t
           ) s
           on q.account_id = s.account_id and q.code = s.code
               left join(
          select code,
                 account_id,
                 case when account_id is null then 0 else c end as creative_count
          from (
                   select code,
                          account_id,
                          material_name,
                          hc_status,
                          hc_create_time,
                          clip_id,
                          photo_id,
                          case when creative_id is null then 0 else  count(1) end as c
                   from (
                            select code,
                                   material_name,
                                   a.status               as hc_status,
                                   a.create_time          as hc_create_time,
                                   clip_id,
                                   a.account_id,
                                   a.photo_id,
                                   b.creative_id,
                                   b.status               as creative_status,
                                   b.creative_create_time as creative_create_time
                            from (select a.*, b.clip_id, c.account_id, photo_id, c.stat_date as media_video_time
                                  from (select code, material_name, status, create_time
                                        from `jeecg-boot`.ctop_material_info
                                        where create_time > date_sub('2021-10-12', INTERVAL 6 Day)) a
                                           left join(
                                      select material_id, clip_id
                                      from `jeecg-boot`.ctop_material_ascription
                                  ) b
                                                    on a.code = b.material_id
                                           left join(
                                      select account_id, signature, photo_id, stat_date
                                      from ctop_kuaishou_video_get
                                      where stat_date >= date_sub('2021-10-12', INTERVAL 6 Day)
                                  ) c
                                                    on a.code = c.signature) a
                                     left join
                                 (select account_id, creative_id, photo_id, status, creative_create_time
                                  from `jeecg-boot`.ctop_kuaishou_creative
                                  where status = 42
                                    and creative_create_time >= date_sub('2021-10-12', INTERVAL 6 Day)) b
                                 on a.account_id = b.account_id and a.photo_id = b.photo_id) t
                   group by account_id, code, material_name,
                            hc_status,
                            hc_create_time,
                            clip_id,
                            photo_id) t
      ) w
                        on q.account_id = w.account_id and q.code = w.code) cr
         left join
     (
         select a.account_id, a.project_name, su.realname, c.user_id as ks_id
         from (select account_id, user_id, user_name, project_name from ctop_user_allocation) a
                  left join
                  (select account_id, user_id from ctop_kuaishou_advertiser_base_info) c
                  on a.account_id = c.account_id
                  left join sys_user su on su.id = a.user_id
     ) user
     on cr.account_id = user.account_id
         left join (
    select id, realname as clip_name
    from sys_user
) clip
                   on cr.clip_id = clip.id
         left join
         (select user_id, company_name from user_company) com
         on cr.clip_id = com.user_id













