import java.util.LinkedHashMap;
import java.util.List;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.cmcc.es.chinapowerit.api.model.bid.EctCnPackage;
import cn.cmcc.es.chinapowerit.api.model.bid.SptSuppSupplier;
import cn.cmcc.es.chinapowerit.calibration.resultgs.pojo.EctCnWinObjPac;
import cn.cmcc.es.chinapowerit.calibration.sure.pojo.ECTCNSuppWin;
import cn.cmcc.es.chinapowerit.calibration.tzs.pojo.ECTCNWinArticle;
import cn.cmcc.es.chinapowerit.calibration.winservice.pojo.ECTCNWinServiceSuppVoucher;
@org.apache.ibatis.annotations.Mapper
public interface SureMySqlMapper {
List<ECTCNSuppWin> listECTCNSuppWin(String packageId,Integer numberTurn );
List<SptSuppSupplier> getAllOkSupps1(String supplierName,String companyType);
List<SptSuppSupplier> getAllOkSupps2(String supplierName,String companyType);
/**
* 传入sql 获取 List<LinkedHashMap<String, Object>>
* @param sql
* @return
*/
List<LinkedHashMap<String, Object>> superManagerSelect(String sql);
/**
* 根据项目id,获取没有填写中标信息的包
* @param projectId
* @return
*/
@Select(" select t.package_id, t.package_name, t.project_id, t.object_id, t.object_name " +
" from ect_cn_package t " +
" where t.del_flg = '0' and t.is_fail = 'N' and t.is_open_bid = 'Y' " +
" and t.project_id = #{projectId} " +
" and not exists (select * from ect_cn_win t2 where t2.package_id = t.package_id and t2.del_flg = '0') " )
List<EctCnPackage> getNoWinSuppPackage( String projectId);
/**
* 获取中标标包和供应商名称
* @param projectId
* @param numberTurn
* @return
*/
@Select(" select distinct l.obj_pac_name, l.supplier_name " +
" from (select case " +
" when r.object_name is null then " +
" r.package_name " +
" else " +
" r.object_name || '/' || r.package_name " +
" end as obj_pac_name, " +
" r.supplier_name, " +
" t.candidate_sort, " +
" o.object_order, " +
" p.package_sort " +
" from ect_cn_win r " +
" left join ect_cn_supp_reply t " +
" on r.package_id = t.package_id " +
" and r.supplier_id = t.supplier_id " +
" left join ect_cn_object o " +
" on r.object_id = o.object_id " +
" left join ect_cn_package p " +
" on r.package_id = p.package_id " +
" where r.project_id = #{projectId} " +
" and r.del_flg = '0' " +
" and t.number_turn = #{numberTurn} " +
" order by o.object_order, p.package_sort, t.candidate_sort) l " +
" order by l.object_order, l.package_sort, l.candidate_sort ")
List<EctCnWinObjPac> getEctCnWinObjPac(String projectId,Integer numberTurn);
/**
* 获取中标供应商列表
* @param projectId
* @param packageId
* @return
*/
@Select("<script> " +
"select distinct a.project_Id, " +
" a.package_Name, " +
" a.supplier_Name, " +
" b.ADUIT_STATUS, " +
" b.pub_time, " +
" a.supplier_id, " +
" b.column_code, " +
" a.object_name, " +
" a.object_id, " +
" a.package_id, " +
" a.win_money,a.win_proportion, a.work_area "+
" from (select t1.* " +
" from ECT_CN_WIN t1, ect_cn_package t2 " +
" where t1.package_id = t2.package_id " +
" and t2.is_fail = 'N') a " +
" left join CMS_ARTICLE b " +
" on a.package_Id = b.package_Id " +
" and a.supplier_Id = b.supplier_Id " +
" and b.column_code = 'ZBTZ' " +
" where a.del_flg = '0' " +
" <if test='projectId != null and projectId != '' '>" +
" and a.project_Id = #{projectId} " +
" </if>" +
" <if test='packageId != null and packageId != '' '>" +
" and a.package_Id = #{packageId} " +
" </if>" +
" order by a.supplier_Name " +
"</script>")
List<ECTCNWinArticle> getResultWinList(String projectId, String packageId);
/**
* 获取未中标供应商列表
* @param projectId
* @param packageId
* @return
*/
@Select("<script> " +
" select a.project_id, " +
" a.package_id, " +
" a.supplier_id, " +
" a.package_name, " +
" a.supplier_name, " +
" t4.aduit_status, " +
" t4.pub_time, " +
" t4.column_code, " +
" a.object_name " +
" from (select t.project_id, " +
" t.package_id, " +
" t.supplier_id, " +
" t.package_name, " +
" t.supplier_name, " +
" t.object_name " +
" from ect_cn_supp_reply t " +
" where t.del_flg = '0' " +
" and t.number_turn = " +
" (select t3.number_turn " +
" from ect_cn_project_main t3 " +
" where t3.project_id = t.project_id) " +
" <if test='projectId != null and projectId != '' '>" +
" and t.project_Id = #{projectId} " +
" </if>" +
" <if test='packageId != null and packageId != '' '>" +
" and t.package_Id = #{packageId} " +
" </if>" +
" and t.reply_status ='REPLY' " +
" and t.is_open_bid = 'Y' " +
" and t.supplier_id not in " +
" (select t2.supplier_id " +
" from ect_cn_supp_reply t2 " +
" where t2.project_id = t.project_id " +
" and t2.package_id = t.package_id " +
" and t2.is_win = 'Y' " +
" and t2.number_turn = " +
" (select t3.number_turn " +
" from ect_cn_project_main t3 " +
" where t3.project_id = t2.project_id))) a " +
" left join cms_article t4 " +
" on t4.package_id = a.package_id " +
" and t4.supplier_id = a.supplier_id " +
" and t4.column_code = 'ZBJGTZ' " +
" and t4.del_flg = 'Y' " +
" order by a.supplier_Name " +
" </script>")
List<ECTCNWinArticle> getNoWinList(String projectId, String packageId);
/**
* 批量更新CmsArticle 的AduitStatus
* @param projectId
* @param columnCode
* @param auditStatus
* @return
*/
@Update("<script> " +
"update cms_article set ADUIT_STATUS =#{auditStatus} where PROJECT_ID=#{projectId} and COLUMN_CODE in " +
" <foreach collection='columnCodes' item='columnCode' index='index' open='(' separator=',' close=')' >" +
" #{columnCode}" +
" </foreach>" +
" <if test='againStatus != null and againStatus == 'again' '>" +
" and is_again = 'Y' " +
" </if>" +
" </script>" )
int updateCmsArticleAduitStatus(String projectId, String[] columnCodes, String auditStatus,String againStatus);
/**
* 批量更新CmsStruParam 的AduitStatus
* @param projectId
* @param columnCode
* @param auditStatus
* @return
*/
@Update("<script> " +
"update Cms_Stru_Param set audit_status =#{auditStatus} where PROJECT_ID=#{projectId} and template_Type in " +
" <foreach collection='templateTypes' item='templateType' index='index' open='(' separator=',' close=')' >" +
" #{templateType}" +
" </foreach>" +
" <if test='againStatus != null and againStatus == 'again' '>" +
" and is_again = 'Y' " +
" </if>" +
" </script>" )
int updateCmsStruParamAuditStatus(String projectId, String[] templateTypes, String auditStatus,String againStatus);
/**
* 获取中标服务费 供应商列表
* @param projectId
* @return
*/
@Select(" select s.service_id, "+
" s.service_money, "+
" s.service_info, "+
" s.project_id, "+
" s.project_name, "+
" s.pxy_name, "+
" s.manager_name, "+
" s.manager_tel, "+
" s.manager_mailbox, "+
" v.voucher_id, "+
" v.upload_supp_name, "+
" v.upload_supp_tel, "+
" w1.supplier_id, "+
" w1.supplier_name, "+
" s.project_code, "+
" v.submit_state, "+
" v.submit_time "+
" from (select distinct w.supplier_id, w.supplier_name from ect_cn_win w where w.project_id =#{projectId} and w.DEL_FLG='0' ) w1 "+
" left join ( select * from ect_cn_win_service s where s.project_id=#{projectId} and s.DEL_FLG='0' ) s "+
" on w1.supplier_id = s.supplier_id "+
" left join ect_cn_supp_voucher v "+
" on s.service_id = v.service_id and v.DEL_FLG='0' "+
" order by w1.supplier_id ")
List<ECTCNWinServiceSuppVoucher> getWinSuppContent(String projectId);
/**
* 中标服务费列表
* @param projectId
* @return
*/
@Select(" select s.service_id, "+
" s.service_money, "+
" s.service_info, "+
" s.project_id, "+
" s.project_name, "+
" s.pxy_name, "+
" s.manager_name, "+
" s.manager_tel, "+
" s.manager_mailbox, "+
" v.voucher_id, "+
" v.upload_supp_name, "+
" v.upload_supp_tel, "+
" v.supplier_id, "+
" v.supplier_name, "+
" s.project_code,"+
" v.submit_state, "+
" v.submit_time "+
" from ect_cn_win_service s "+
" join ect_cn_supp_voucher v "+
" on s.service_id = v.service_id "+
" where s.project_id = #{projectId} " +
" and v.submit_state = 'YES' " +
" order by s.service_id,v.voucher_id ")
List<ECTCNWinServiceSuppVoucher> forWinServiceSuppVoucherContent(String projectId);
/**
* 获取 ECTCNWinServiceSuppVoucher
* @param projectId
* @param serviceId
* @param voucherId
* @return
*/
@Select("<script> " +
" select s.service_id, "+
" s.service_money, "+
" s.service_info, "+
" s.project_id, "+
" s.project_name, "+
" s.pxy_name, "+
" s.manager_name, "+
" s.manager_tel, "+
" s.manager_mailbox, "+
" v.voucher_id, "+
" v.upload_supp_name, "+
" v.upload_supp_tel, "+
" v.supplier_id, "+
" v.supplier_name, "+
" s.project_code"+
" from ect_cn_win_service s "+
" left join ect_cn_supp_voucher v "+
" on s.service_id = v.service_id "+
" where s.project_id = #{projectId} " +
" and s.service_id = #{serviceId} " +
" <if test='voucherId != null and voucherId != '' '>" +
" and v.voucher_id = #{voucherId} " +
" </if>" +
" order by s.service_id,v.voucher_id "+
" </script>" )
ECTCNWinServiceSuppVoucher getVByPidAndSidAndVid(String projectId, String serviceId, String voucherId);
/**
* 获得项目下确认出席的专家人数
* @param projectId
* @return
*/
@Select("select count(id_Number) " +
" from ept_extraction_name_list t " +
" where t.del_flg = '0' " +
" and t.bid_project_id = #{projectId} " +
" and t.to_attend = 'EPT_ATTEND_TO_ATTEND' " )
String getAttendedNum(String projectId);
}