a.id AS "id", a.member_code AS "memberCode", a.coupon_name AS "couponName", a.face_price AS "facePrice", a.valid_date AS "validDate", a.explains AS "explains", a.target AS "target", a.target_id AS "targetId", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.running_number AS "runningNumber", a.coupon_code AS "couponCode", a.del_flag AS "delFlag", a.useState AS "useState", a.effective_date AS "effectiveDate", a.coupon_def_id AS "couponDefId", a.coupon_type AS "couponType" , a.user_coupon_compose_id AS "userCouponComposeId" SELECT FROM sys_coupon a WHERE a.id = #{id} SELECT , COUNT(1) AS "couponNum" FROM sys_coupon a a.del_flag = #{DEL_FLAG_NORMAL} AND a.member_code = #{memberCode} AND a.coupon_name LIKE CONCAT('%', #{couponName}, '%') AND a.explains LIKE CONCAT('%', #{explains}, '%') AND a.remarks = #{remarks} AND a.face_price = #{facePrice} AND a.target_id like CONCAT('%', #{targetId}, '%') AND a.useState='0' and (date_format( a.valid_date, '%Y-%m-%d' ) >= date_format( now(), '%Y-%m-%d' ) or a.valid_date is null) and (date_format( a.effective_date, '%Y-%m-%d' ) date_format( now(), '%Y-%m-%d' ) or a.effective_date is null) and date_format( a.valid_date, '%Y-%m-%d' ) < date_format( now(), '%Y-%m-%d' ) and useState = '0' AND a.useState='1' AND a.target in #{item} AND a.coupon_type = #{couponTypeAdmin} AND a.target IN ( 'dogBirthCertificate' , 'dogChipApply' , 'dogvideo' , 'memberdog' ) AND a.target IN ( 'dogChip' ) AND a.target IN ( 'member' , 'memberdog' ) AND a.target IN ( 'groomer' ) GROUP BY a.target, a.face_price, a.explains ORDER BY face_price DESC ORDER BY a.update_date DESC SELECT COUNT( 1 ) FROM sys_coupon WHERE member_code = #{memberCode} AND coupon_name = #{couponName} AND face_price = #{facePrice} AND DATE_FORMAT( valid_date, '%Y-%m-%d' ) BETWEEN DATE_FORMAT( now( ), '%Y-%m-%d' ) AND DATE_ADD( DATE_FORMAT( now( ), '%Y-%m-%d' ), INTERVAL 30 DAY ) AND date_format( valid_date, '%Y-%m-%d' ) < date_format( now(), '%Y-%m-%d' ) AND useState = '0' AND useState= '1' SELECT FROM sys_coupon a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO sys_coupon( id, member_code, coupon_name, face_price, valid_date, explains, target, target_id, useState, create_by, create_date, update_by, update_date, remarks, running_number, coupon_code, del_flag, coupon_type, coupon_def_id, effective_date, user_coupon_compose_id ) VALUES ( #{id}, #{memberCode}, #{couponName}, #{facePrice}, #{validDate}, #{explains}, #{target}, #{targetId}, #{useState}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{runningNumber}, #{couponCode}, #{delFlag}, #{couponType}, #{couponDefId}, #{effectiveDate}, #{userCouponComposeId} ) UPDATE sys_coupon SET coupon_name = #{couponName}, face_price = #{facePrice}, valid_date = #{validDate}, explains = #{explains}, target = #{target}, target_id = #{targetId}, useState = #{useState}, update_by = #{updateBy.id}, update_date = #{updateDate}, running_number = #{runningNumber}, coupon_def_id = #{couponDefId}, remarks = #{remarks} WHERE id = #{id} UPDATE sys_coupon SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} SELECT FROM sys_coupon a WHERE a.del_flag = '0' AND a.member_code =#{memberCode} AND a.useState='0' and (a.target_id ='' or a.target_id is null) AND (a.valid_date IS NOT NULL or a.valid_date < now()) AND a.useState='1' SELECT count(a.id) FROM sys_coupon a WHERE a.del_flag = '0' AND a.member_code =#{memberCode} AND a.useState='0' AND (a.target_id ='' or a.target_id is null) AND (a.valid_date IS NULL or a.valid_date > now()) AND (a.valid_date IS NOT NULL or a.valid_date <= now()) AND a.useState='1' SELECT FROM sys_coupon a WHERE a.del_flag = '0' AND a.member_code =#{memberCode} AND a.target =#{target} AND a.useState='0' AND (a.target_id ='' or a.target_id is null) AND (a.valid_date IS NULL or a.valid_date > now()) AND a.valid_date <= now() and a.useState='0' AND a.useState='1' SELECT FROM sys_coupon a WHERE a.target_id =#{targetId} UPDATE sys_coupon SET member_code = #{memberCode}, update_by = #{updateBy.id}, update_date = #{updateDate} WHERE id = #{id} UPDATE sys_coupon SET useState = '0', target_id = IF(target='dogChip',substring_index(target_id,'#', -1),NULL), running_number = null, update_date = #{vo.updateDate}, update_by = #{vo.updateBy.id} WHERE useState = '1' AND target_id in #{item} UPDATE sys_coupon SET useState = '0', target_id = IF(target='dogChip',substring_index(target_id,'#', -1),NULL), running_number = null, update_date = #{coupon.updateDate}, update_by = #{coupon.updateBy.id} WHERE useState = '1' and target = 'memberdog' AND id in #{item} SELECT , ( effective_date IS NULL OR effective_date <= curdate( ) ) AND ( valid_date IS NULL OR date_format( valid_date,'%Y-%m-%d') >= curdate( ) ) AS yx FROM sys_coupon a WHERE a.del_flag = '0' AND a.member_code =#{memberCode} AND a.useState='0' and ( a.valid_date IS NULL OR date_format( a.valid_date,'%Y-%m-%d') >= curdate( ) ) order by create_date asc SELECT FROM sys_coupon a WHERE a.del_flag = '0' AND a.member_code =#{memberCode} AND a.useState='0' and (a.target_id ='' or a.target_id is null) AND (a.valid_date IS NOT NULL or a.valid_date < now()) AND a.useState='1' AND a.target=#{target} select b.* from payment_order_detail a left join sys_coupon b on a.business_ids=b.id and a.order_code=b.target_id and a.business_table='sys_coupon' where a.order_code=#{orderCode} and b.id is not null select * from sys_coupon where del_flag = '0' and coupon_def_id in #{id} SELECT id AS "id" , compose_name AS "composeName" , compose_desc AS "composeDesc" , compose_price AS "composePrice" , compose_original_price AS "composeOriginalPrice" , coupon_name AS "couponName" , coupon_price AS "couponPrice" , coupon_num AS "couponNum" , compose_type AS "composeType" , remarks AS "remarks" FROM coupon_compose WHERE compose_type=#{composeType} ORDER BY compose_type DESC, id DESC INSERT INTO user_coupon_compose ( id, compose_id, payment_state, running_number, member_code , create_date , use_num , target ) VALUES ( #{vo.id}, #{vo.composeId} ,#{vo.paymentState}, #{vo.runningNumber}, #{vo.memberCode} , #{vo.createDate} , #{vo.useNum} , #{vo.target}) SELECT id AS "id" , compose_name AS "composeName" , compose_desc AS "composeDesc" , compose_price AS "composePrice" , compose_original_price AS "composeOriginalPrice" , coupon_name AS "couponName" , coupon_price AS "couponPrice" , coupon_num AS "couponNum" , compose_type AS "composeType" , remarks AS "remarks" FROM coupon_compose WHERE id=#{id} SELECT MD5(concat(a.member_code,a.coupon_name,a.coupon_type,a.target,a.face_price,a.useState,a.del_flag)) as group_id, a.coupon_name, a.coupon_type, CASE a.coupon_type WHEN '1' THEN '代金券' WHEN '2' THEN '抵扣券' WHEN '3' THEN '参展券' WHEN '4' THEN '满减券' END as coupon_type_desc, a.target, a.face_price , COUNT(1) AS coupon_num FROM sys_coupon a a.del_flag = #{DEL_FLAG_NORMAL} AND a.member_code = #{memberCode} AND a.useState='0' and (date_format( a.valid_date, '%Y-%m-%d' ) >= date_format( now(), '%Y-%m-%d' ) or a.valid_date is null) and (date_format( a.effective_date, '%Y-%m-%d' ) date_format( now(), '%Y-%m-%d' ) or a.effective_date is null) and date_format( a.valid_date, '%Y-%m-%d' ) < date_format( now(), '%Y-%m-%d' ) AND a.useState='1' AND a.face_price <= #{facePrice} AND a.target in #{item} GROUP BY a.coupon_name , a.coupon_type, a.target, a.face_price ORDER BY a.face_price DESC, ISNULL(a.valid_date), a.valid_date SELECT a.* FROM sys_coupon a WHERE MD5(concat(a.member_code,a.coupon_name,a.coupon_type,a.target,a.face_price,a.useState,a.del_flag)) = #{groupId} ORDER BY a.face_price DESC, ifnull(a.valid_date,now()) SELECT u.id AS "id", u.compose_id AS "composeId", u.payment_state AS "paymentState", u.running_number AS "runningNumber", u.payment_time AS "paymentTime", u.member_code AS "memberCode", u.create_date AS "createDate", u.use_num AS "useNum", u.target AS "target" , c.coupon_price AS "facePrice" , c.coupon_num AS "num", c.compose_price as "composePrice" FROM user_coupon_compose u join coupon_compose c on u.compose_id = c.id WHERE u.id = #{id} select * from sys_coupon a where a.del_flag = '0' AND a.member_code = #{memberCode} AND a.useState='0' and (date_format( a.valid_date, '%Y-%m-%d' ) >= date_format( now(), '%Y-%m-%d' ) or a.valid_date is null) AND (date_format( a.effective_date, '%Y-%m-%d' ) date_format( now(), '%Y-%m-%d' ) or a.effective_date is null) AND a.id in #{id}