package com.reyun.repository; import com.reyun.model.SubCampaign; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.transaction.annotation.Transactional; import java.math.BigInteger; import java.util.Date; import java.util.Iterator; import java.util.List; /** * Created by sunhao on 17/5/22. * desc:子活动DAO */ public interface SubCampaignRepository extends JpaRepository<SubCampaign,Long>{ @Query(value = "select count(*) from sub_campaign where campaign_id = ?1 and del_flag is false ",nativeQuery = true) BigInteger countSubCampaignByCampaign(Long campaignId); @Query(value = "select count(*) from sub_campaign where id in ?1 and del_flag is false ", nativeQuery = true) BigInteger countSubCampaignById(List<Long> ids); @Query(value = "select * from sub_campaign where campaign_id = ?1 ", nativeQuery = true) List<SubCampaign> listAllSubCampaignByCampaign(Long campaignId); @Query(value = "select * from sub_campaign where campaign_id = ?1 and del_flag is false ", nativeQuery = true) List<SubCampaign> listSubCampaignByCampaign(Long campaignId); @Query(value = "select * from sub_campaign where campaign_id in ?1 and del_flag is false ", nativeQuery = true) List<SubCampaign> listSubCampaignInCampaign(List<Long> campids); @Query(value = "select * from sub_campaign where campaign_id = ?1 and del_flag is true ", nativeQuery = true) List<SubCampaign> listDisableByCampaign(Long campaignId); @Query(value = "select * from sub_campaign where campaign_id = ?1 and keyword = ?2 and creative = ?3 and del_flag is false limit 1 ", nativeQuery = true) SubCampaign findSubCampaignByKeyword(Long campaignId, String keyword, String creative); @Query(value = "select * from sub_campaign where campaign_id = ?1 and keyword = ?2 and (creative is null or creative = '') and del_flag is false limit 1 ", nativeQuery = true) SubCampaign findSubCampaignByKeyword(Long campaignId, String keyword); @Query(value = "select * from sub_campaign c where c.app = ?1 and del_flag is not true", nativeQuery = true) List<SubCampaign> findAllByApp(Long app); @Query(value = "select * from sub_campaign where app=?1 and campaign_id=?2", nativeQuery = true) List<SubCampaign> findByAppAndCampaignId(Long app,Long campaignId); @Query(value = "select * from sub_campaign c where c.app = ?1 and url = ?2 and del_flag is not true", nativeQuery = true) List<SubCampaign> findAllByAppAndUrl(Long app, String url); @Transactional @Modifying @Query(value = "update sub_campaign set url = ?2, modify_account = ?3, modify_time = ?4 where id = ?1 ", nativeQuery = true) int updateSubCampaignUrl(Long subCampaignId, String url, Long accountId, Date modifyDate); @Transactional @Modifying @Query(value = "update sub_campaign set del_flag = true, modify_account = ?2, modify_time = ?3 where id in ?1 and del_flag is false ", nativeQuery = true) int deleteSubCampaign(List<Long> subCampaignIdList, Long modifyAccount, Date modifyDate); @Transactional @Modifying @Query(value = "update sub_campaign set del_flag = false, modify_account = ?2, modify_time = ?3 where id in ?1 and del_flag is true ", nativeQuery = true) int enableSubCampaign(List<Long> subCampaignIdList, Long modifyAccount, Date modifyDate); @Transactional @Modifying @Query(value = "update sub_campaign c set c.url=?2 where c.app = ?1", nativeQuery = true) int updateDownloadUrl(Long app, String url); @Query(value = "SELECT campaign_id,COUNT(*) AS COUNT FROM sub_campaign WHERE campaign_id IN ?1 AND del_flag IS FALSE GROUP BY campaign_id",nativeQuery = true) List<Object[]> countSubCampaignByCampaignByIds(List<Long> ids); @Transactional @Modifying @Query(value = "update sub_campaign set url_name = ?2 where id = ?1",nativeQuery = true) int modifyUrlName(Long campaignId, String name); @Transactional @Modifying @Query(value = "update sub_campaign set url = ?2,url_name=?3 where id in ?1",nativeQuery = true) int updataUlrWithIds(List<Long> campids, String new_url, String urlName); @Transactional @Modifying @Query(value = "update sub_campaign set url_name=?2 where app = ?3 and url = ?1",nativeQuery = true) int updateUrlName(String new_url, String urlName, Long appid); }