/**
* 创建文章
* @param article 文章实体
* @return 返回文章ID
*/
public int createArticle(Article article);
<!-- 创建文章 -->
<insert id="createArticle" parameterType="Article" useGeneratedKeys="true" keyProperty="articleId" keyColumn="ARTICLE_ID">
INSERT INTO EDU_ARTICLE(<include refid="edu_article_column"/>) VALUE(<include refid="edu_article_property"/>)
</insert>
/**
* 删除文章
* @param articleIds 文章ID串 如:(1,2,3,4)
*/
public void deleteArticleByIds(String articleIds);
/**
* 删除文章内容
* @param articleIds 文章ID串 如:(1,2,3,4)
*/
public void deleteArticleContentByArticleIds(String articleIds);
public void deleteArticleByIds(String[] articleIds) {
if (articleIds != null && articleIds.length > 0) {
String ids = "";
for (int i = 0; i < articleIds.length; i++) {
if (i < articleIds.length - 1) {
ids += articleIds[i] + ",";
} else {
ids += articleIds[i];
}
}
articleDao.deleteArticleByIds(ids);
articleDao.deleteArticleContentByArticleIds(ids);
}
}
public Article queryArticleById(int articleId) {
return articleDao.queryArticleById(articleId);
}
<!-- 删除文章 -->
<delete id="deleteArticleByIds" parameterType="java.lang.String">
DELETE FROM EDU_ARTICLE WHERE EDU_ARTICLE.ARTICLE_ID IN (${value})
</delete>
<!-- 删除文章内容 -->
<delete id="deleteArticleContentByArticleIds" parameterType="java.lang.String">
DELETE FROM EDU_ARTICLE_CONTENT WHERE EDU_ARTICLE_CONTENT.ARTICLE_ID IN (${value})
</delete>
/**
* 公共多条件查询文章资讯列表,用于前台
*/
public List<Article> queryArticleList(QueryArticle queryArticle);
<!-- 公共多条件查询文章资讯列表,用于前台 -->
<select id="queryArticleList" parameterType="QueryArticle" resultMap="ArticleResult">
SELECT <include refid="edu_article_column"/> FROM EDU_ARTICLE
<where>
<if test="type>0">
AND EDU_ARTICLE.ARTICLE_TYPE=#{type}
</if>
<if test="orderby==0">
ORDER BY EDU_ARTICLE.CREATE_TIME DESC
</if>
<if test="orderby==1">
ORDER BY EDU_ARTICLE.CLICK_NUM DESC
</if>
<if test="count>0">
LIMIT #{count}
</if>
</where>
</select>
/**
* 将视频关联到课程
*
* @param courseKpoint
* @return
*/
public Integer relevanceVideoToCourse(CourseKpoint courseKpoint);
<!-- 将视频关联到课程 -->
<update id="relevanceVideoToCourse" parameterType="CourseKpoint">
<selectKey keyProperty='kpointId' resultType='java.lang.Integer'
order='BEFORE'>
SELECT
(select KPOINT_ID FROM EDU_COURSE_KPOINT WHERE
EDU_COURSE_KPOINT.KPOINT_ID=#{kpointId})KPOINT_ID
from DUAL
</selectKey>
UPDATE
EDU_COURSE_KPOINT
SET
EDU_COURSE_KPOINT.COURSE_ID=#{courseId},
EDU_COURSE_KPOINT.NAME=#{name},
EDU_COURSE_KPOINT.PARENT_ID=#{parentId},
EDU_COURSE_KPOINT.TEACHER_ID=#{teacherId},
EDU_COURSE_KPOINT.CONTENT=#{content},
EDU_COURSE_KPOINT.DATA=#{data},
EDU_COURSE_KPOINT.PAPER_ID=#{paperId}
WHERE
EDU_COURSE_KPOINT.KPOINT_ID=#{kpointId}
</update>
/**
* 获取课程的 二级视频节点总数(只支持二级)
*/
public int getSecondLevelKpointCount(Long courseId);
<!-- 获取课程的 视频节点总数 -->
<select id="getSecondLevelKpointCount" parameterType="long" resultType="int">
SELECT IFNULL(count(1),0)
FROM edu_course_kpoint
where
edu_course_kpoint.COURSE_ID=#{value} and
edu_course_kpoint.KPOINT_TYPE=1
</select>
/**
* 操作在线课程
*
* @param courseKpoint
* @return
*/
public int insertOrUpdateDummyCourseKpoint(CourseKpoint courseKpoint);
<!-- 操作在线课程 -->
<insert id="insertOrUpdateDummyCourseKpoint" parameterType="CourseKpoint"
keyColumn="kpoint_id" keyProperty="kpointId" useGeneratedKeys="true">
INSERT INTO EDU_COURSE_KPOINT(
EDU_COURSE_KPOINT.kpoint_id,
EDU_COURSE_KPOINT.course_id,
EDU_COURSE_KPOINT.check_result,
EDU_COURSE_KPOINT.sort,
EDU_COURSE_KPOINT.SEQUENCE,
EDU_COURSE_KPOINT.is_free,
EDU_COURSE_KPOINT.video_type,
EDU_COURSE_KPOINT.upload_id,
EDU_COURSE_KPOINT.name,
EDU_COURSE_KPOINT.parent_id,
EDU_COURSE_KPOINT.kpoint_type,
EDU_COURSE_KPOINT.file_type,
EDU_COURSE_KPOINT.teacher_id,
EDU_COURSE_KPOINT.content,
EDU_COURSE_KPOINT.video_url,
EDU_COURSE_KPOINT.duration,
EDU_COURSE_KPOINT.size,
EDU_COURSE_KPOINT.image,
EDU_COURSE_KPOINT.data
)
VALUES(
#{kpointId},
#{courseId},
#{checkResult},
#{sort},
#{sequence},
#{free},
#{videoType}
#{uploadId},
#{name},
#{parentId},
#{kpointType},
#{fileType},
#{teacherId},
#{content},
#{videoUrl},
#{duration},
#{size},
#{image},
#{data}
)
<if test="kpointId>0">
ON DUPLICATE key UPDATE
EDU_COURSE_KPOINT.kpoint_id=EDU_COURSE_KPOINT.kpoint_id
<if test="courseId>0">
, EDU_COURSE_KPOINT.course_id=#{courseId}
</if>
<if test="checkResult>0">
,
EDU_COURSE_KPOINT.check_result=#{checkResult}
</if>
<if test="sort>0">
,
EDU_COURSE_KPOINT.sort=#{sort}
</if>
<if test="sequence>0">
,
EDU_COURSE_KPOINT.SEQUENCE=#{sequence}
</if>
<if test="free>0">
,
EDU_COURSE_KPOINT.is_free=#{free}
</if>
<if test="videoType!=null and videoType!=''">
,
EDU_COURSE_KPOINT.video_type=#{videoType}
</if>
<if test="uploadId>0">
,
EDU_COURSE_KPOINT.upload_id=#{uploadId}
</if>
<if test="name!=null and name!=''">
,
EDU_COURSE_KPOINT.name=#{name}
</if>
<if test="parentId>0">
, EDU_COURSE_KPOINT.parent_id=#{parentId}
</if>
<if test="kpointType!=null and kpointType!=''">
,
EDU_COURSE_KPOINT.kpoint_type=#{kpointType}
</if>
<if test="fileType!=null and fileType!=''">
,
EDU_COURSE_KPOINT.file_type=#{fileType}
</if>
<if test="teacherId>0">
,
EDU_COURSE_KPOINT.teacher_id=#{teacherId}
</if>
<if test="content!=null and content!=''">
,
EDU_COURSE_KPOINT.content=#{content}
</if>
<if test="videoUrl!=null and videoUrl!=''">
,
EDU_COURSE_KPOINT.video_url=#{videoUrl}
</if>
<if test="duration!=null and duration!=''">
,
EDU_COURSE_KPOINT.duration=#{duration}
</if>
<if test="size!=null and size!=''">
,
EDU_COURSE_KPOINT.size=#{size}
</if>
<if test="image!=null and image!=''">
,
EDU_COURSE_KPOINT.image=#{image}
</if>
<if test="data!=null and data!=''">
,
EDU_COURSE_KPOINT.data=#{data}
</if>
</if>
</insert>
/**
* 查询CourseKpoint表里面最大的kpointId
*
* @return
*/
public int selectMaxCourseKpointId();
<!-- 查询CourseKpoint表里面最大的kpointId -->
<select id="selectMaxCourseKpointId" resultType="int">
SELECT
auto_increment
FROM information_schema.tables where
table_schema='mooc_zhongchao_ronge' and
table_name='EDU_COURSE_KPOINT'
</select>
/**
* 获取班级的课程列表
*/
public List<CourseDto> getCourseListClass(List<Long> ids);
<!-- 根据多个id查询课程 信息 -->
<select id="getCourseListClass" parameterType="list" resultMap="CourseDtoResult">
SELECT DISTINCT
edu_course.COURSE_ID,
edu_course.COURSE_NAME,
edu_course.ADD_TIME,
edu_course.SOURCE_PRICE,
edu_course.CURRENT_PRICE,
EDU_COURSE.LOGO,
EDU_COURSE.LESSION_NUM,
EDU_COURSE.title
FROM EDU_COURSE
LEFT JOIN edu_class_course ON
edu_course.COURSE_ID=edu_class_course.COURSE_ID
WHERE
edu_class_course.CLASS_ID in
<foreach collection="list" item="item" open="(" close=")"
separator=",">
#{item}
</foreach>
</select>
/**
* 批量添加消息
*
* @param informationList 消息的list
*/
public Long addInformationBatch(List<Information> informationList);
<!-- 批量添加发送的消息 -->
<insert id="addInformationBatch" parameterType="java.util.HashMap">
INSERT INTO EDU_INFORMATION (
<include refid="edu_information_columns" />
) VALUES
<foreach collection="list" index="index" item="informationList"
separator=",">
(#{informationList.id},
#{informationList.cusId},
#{informationList.title},
#{informationList.content},
#{informationList.receivingCusId},
#{informationList.addTime},
#{informationList.updateTime},
#{informationList.status},
#{informationList.type}
)
</foreach>
</insert>
/**
* 查询该用户未读消息数量
*
* @param content 要发送的内容
* @return 返回该用户四种类型每个的未读消息的数量和总的未读数量
* @throws Exception
*/
QueryMsgReceive queryUnReadMsgReceiveNumByCusId(Long cusId) throws Exception;
<select id="queryUnReadLetterNumByCusId" parameterType="java.util.HashMap"
resultMap="QueryMsgReceiveResult">
select
count(edu_msg_receive.type=1 or null) systemNum,
count(type=2 or null) letterNum,
count(type=3 or null) friNum,
count(type=4 or null) groupNum
from edu_msg_receive
where
edu_msg_receive.receiving_cusId = #{cusId} and edu_msg_receive.status
= 0
</select>
/**
* 查询距直播开始差30分钟的直播
*
* @param map
* @return
*/
public List<QueryTrxorderDetail> queryLiveCourseOutOfDate(
Map<String, Object> map);
<!--查询直播前30分钟的直播课程 -->
<select id="queryLiveCourseOutOfDate" parameterType="java.util.Map"
resultMap="QueryTrxorderDetailResult">
select
<include refid="edu_trxorder_detail_columns" />
,edu_user.nickname,edu_user.email,edu_user.mobile
from
edu_trxorder_detail
left join edu_course on
edu_course.COURSE_ID=edu_trxorder_detail.course_id
left join edu_user
on edu_trxorder_detail.user_id=edu_user.USER_ID
where
edu_trxorder_detail.user_id>0
and edu_trxorder_detail.auth_time !=''
<![CDATA[ and edu_trxorder_detail.auth_time !='' and date_add(edu_course.live_begin_time , interval -#{minute} minute)<=#{nowTime}
and edu_trxorder_detail.auth_time>#{nowTime}]]>
and edu_trxorder_detail.trx_status='SUCCESS'
and
edu_trxorder_detail.auth_status='SUCCESS'
and
edu_course.sell_type='LIVE'
and
edu_trxorder_detail.remind_status=#{remindStatus}
</select>
/**
* 更新StatisticsDay sns
* @param date
* @return
*/
public void updateStatisticsDay(StatisticsDay statisticsDay);
<!-- 添加网站统计web(天) -->
<insert id="createStatisticsDay" parameterType="Date" >
insert INTO edu_statistics_day (<include refid="edu_statistics_day_columns"/>) VALUES (0,DATE_FORMAT(#{value},'%Y-%m-%d'),
(<include refid="edu_statistics_loginNum"/>),
now(),(<include refid="edu_statistics_registeredNum"/>),
(select IfNULL(count(DISTINCT o.ORDER_ID),0) from edu_orders as o WHERE DATE_FORMAT(o.CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d')),
(select IfNULL(count(DISTINCT o.ORDER_ID),0) from edu_orders as o WHERE DATE_FORMAT(o.CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d') and o.STATES='SUCCESS'),
(select IfNULL(count(DISTINCT o.ORDER_ID),0) from edu_orders as o WHERE DATE_FORMAT(o.CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d') and o.STATES='INIT'),
(select IfNULL(count(DISTINCT o.ORDER_ID),0) from edu_orders as o WHERE DATE_FORMAT(o.CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d') and o.STATES='CLOSED'))
</insert>
/**
* 按年查询网站统计
*
* @param queryUser
* @return
*/
public List<StatisticsDay> getStatisticsByYear(String year);
<!-- 按年查询网站统计 -->
<select id="getStatisticsByYear" resultMap="StatisticsDayResult" parameterType="Map">
select
year.m,
<include refid="edu_statistics_day_columns_notnull"/>
FROM
(SELECT 12 as m UNION ALL
SELECT 11 UNION ALL
SELECT 10 UNION ALL
SELECT 09 UNION ALL
SELECT 08 UNION ALL
SELECT 07 UNION ALL
SELECT 06 UNION ALL
SELECT 05 UNION ALL
SELECT 04 UNION ALL
SELECT 03 UNION ALL
SELECT 02 UNION ALL
SELECT 01) as year
LEFT JOIN
(
SELECT
DATE_FORMAT(edu_statistics_day.statistics_time, '%m') as m,
<include refid="edu_statistics_day_columns_sum"/>
from edu_statistics_day
WHERE DATE_FORMAT(edu_statistics_day.statistics_time, '%Y')=#{nowYear}
GROUP BY m
) AS edu_statistics_day
on year.m = edu_statistics_day.m
</select>
/**
* 按月查询网站统计
*
* @param queryUser
* @return
*/
public List<StatisticsDay> getStatisticsByMonth(String month,String year);
<!-- 按月查询网站统计 -->
<select id="getStatisticsByMonth" resultMap="StatisticsDayResult" parameterType="Map">
SELECT
DATE_FORMAT(day.d, '%d') as d,
<include refid="edu_statistics_day_columns_notnull"/>
FROM
(SELECT
(#{nowMonth} - INTERVAL DAY(#{nowMonth}) DAY) + INTERVAL s DAY as d
FROM
(SELECT 31 AS s UNION ALL
SELECT 30 UNION ALL
SELECT 29 UNION ALL
SELECT 28 UNION ALL
SELECT 27 UNION ALL
SELECT 26 UNION ALL
SELECT 25 UNION ALL
SELECT 24 UNION ALL
SELECT 23 UNION ALL
SELECT 22 UNION ALL
SELECT 21 UNION ALL
SELECT 20 UNION ALL
SELECT 19 UNION ALL
SELECT 18 UNION ALL
SELECT 17 UNION ALL
SELECT 16 UNION ALL
SELECT 15 UNION ALL
SELECT 14 UNION ALL
SELECT 13 UNION ALL
SELECT 12 UNION ALL
SELECT 11 UNION ALL
SELECT 10 UNION ALL
SELECT 09 UNION ALL
SELECT 08 UNION ALL
SELECT 07 UNION ALL
SELECT 06 UNION ALL
SELECT 05 UNION ALL
SELECT 04 UNION ALL
SELECT 03 UNION ALL
SELECT 02 UNION ALL
SELECT 01
) m
WHERE <![CDATA[s<=day(LAST_DAY(#{nowMonth})]]>))
AS day
LEFT JOIN
(
SELECT
DATE_FORMAT(edu_statistics_day.statistics_time, '%Y-%m-%d') AS d,
<include refid="edu_statistics_day_columns"/>
from edu_statistics_day
WHERE DATE_FORMAT(edu_statistics_day.statistics_time, '%Y-%m')=#{nowYear}
) AS edu_statistics_day
ON day.d = edu_statistics_day.d
</select>
/**
* 修改原路径
*
* @param myFile
*/
public void updateSourcePath(Map<String, String> map);
Map<String, String> map = new HashMap<String, String>();
map.put("par1", sourceFile.getPath());
map.put("par2", targetFile.getPath() + targetFile.getId() + "/");
map.put("par3", sourceFile.getPath() + sourceFile.getId()+ "/%");
myFileService.updateSourcePath(map);
<!-- 修改文件原路径 -->
<update id="updateSourcePath" parameterType="java.util.Map">
UPDATE EDU_MYFILE
SET EDU_MYFILE.PATH = replace(EDU_MYFILE.PATH,#{par1},#{par2}) WHERE
EDU_MYFILE.PATH LIKE CONCAT(CONCAT('%',#{par3}),'%') AND
EDU_MYFILE.DELETE_STATE=0
</update>
/**
* 动态查询C2C优惠券信息
* @param map
* @return
*/
public List<C2CCoupon> findC2CCouponDynamic(@Param("map")Map<String, Object> map, PageEntity page);
<select id="findC2CCouponDynamic" resultMap="C2CCouponResult"
parameterType="Map">
select
<include refid="edu_c2c_coupon_columns" />
from edu_c2c_coupon as e
where 1=1
<if test="map.id != null and map.id != '' ">
and e.id=#{map.id}
</if>
<if test="map.startCreateTime != null and map.startCreateTime != '' ">
and e.createTime >= #{map.startCreateTime}
</if>
<if test="map.endCreateTime != null and map.endCreateTime != '' ">
and e.createTime <= #{map.endCreateTime}
</if>
<if test="map.startStartTime != null and map.startStartTime != '' ">
and e.startTime >= #{map.startStartTime}
</if>
<if test="map.endStartTime != null and map.endStartTime != '' ">
and e.startTime <= #{map.endStartTime}
</if>
<if test="map.startEndTime != null and map.startEndTime != '' ">
and e.endTime >= #{map.startEndTime}
</if>
<if test="map.endEndTime != null and map.endEndTime != '' ">
and e.endTime <= #{map.endEndTime}
</if>
<if test="map.startSatisfy != null and map.startSatisfy != 0 ">
and e.satisfy >= #{map.startSatisfy}
</if>
<if test="map.endSatisfy != null and map.endSatisfy != 0 ">
and e.satisfy <= #{map.endSatisfy}
</if>
<if test="map.startReduce != null and map.startReduce != 0 ">
and e.reduce >= #{map.startReduce}
</if>
<if test="map.endReduce != null and map.endReduce != 0 ">
and e.Reduce <= #{map.endReduce}
</if>
<if test="map.type != null and map.type != '' ">
and e.type=#{map.type}
</if>
<if test="map.status != null and map.status != '' ">
and e.status=#{map.status}
</if>
<if test="map.make != null and map.make != '' ">
and e.make=#{map.make}
</if>
<if test="map.teacher != null and map.teacher != '' ">
and e.teacher=#{map.teacher}
</if>
<if test="map.manager != null and map.manager != '' ">
and e.manager=#{map.manager}
</if>
<if test="map.course != null and map.course != '' ">
and e.course=#{map.course}
</if>
<if test="map.count != null and map.count != '' ">
and e.count=#{map.count}
</if>
<if test="map.receiveCount != null and map.receiveCount != '' ">
and e.receiveCount=#{map.receive Count}
</if>
<if test="map.subjectId != null and map.subjectId != '' ">
and e.subjectId=#{map.subjectId}
</if>
order by e.createTime desc
<include refid="publicMapper.pageEnd" />
</select>