a.id AS "id", a.user_id AS "user.id", a.member_code AS "memberCode", a.breed_code AS "breedCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.payment_info AS "paymentInfo", a.first_payment_time AS "firstPaymentTime", a.member_end_date AS "memberEndDate", a.ku_flag AS "kuFlag", a.ku_end_date AS "kuEndDate", a.groomer_type AS "groomerType", a.groomer_remarks AS "groomerRemarks", a.groomer_end_date AS "groomerEndDate", a.referee AS "referee", a.last_call AS "lastCall", a.ban_end_date AS "banEndDate", a.dg_member_type AS "dgMemberType", a.dg_end_date AS "dgEndDate", a.create_date AS "createDate", a.create_by AS "createBy.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", a.cku_club AS "ckuClub", a.account_balance AS "accountBalance", a.balance_blocked_flag AS "balanceBlockedFlag", a.name AS "name", a.email AS "email", a.phone AS "phone", a.mobile AS "mobile", a.name_en AS "nameEn", a.card_type AS "cardType", a.card_no AS "cardNo", a.gender AS "gender", a.birthday AS "birthday", a.province AS "province", a.city AS "city", a.area AS "area", a.street AS "street", a.address AS "address", a.postcode AS "postcode", a.qq AS "qq", a.avatar AS "avatar", a.fax AS "fax", a.registrant AS "registrant", a.delivery_notice AS "deliveryNotice", a.delivery_magazine AS "deliveryMagazine", a.delivery_certified AS "deliveryCertified", a.print_state AS "printState", a.review_state AS "reviewState", a.reviewer AS "reviewer", a.review_date AS "reviewDate", a.reviewer_name AS "reviewerName", a.card_photo_fornt AS "cardPhotoFornt", a.card_photo_rear AS "cardPhotoRear", a.contract_code AS "contractCode", a.freeze_flag AS "freezeFlag", a.safe_mobile AS "safeMobile", a.member_site AS "memberSite", a.authentication AS "authentication", a.dog_nose_repeated_detection AS "dogNoseRepeatedDetection", a.dog_nose_liveness_detection AS "dogNoseLivenessDetection", k.name AS "kennel.name", k.kennel_code AS "kennel.kennelCode", k.name_en AS "kennel.nameEn", k.fci_name AS "kennel.fciName", k.fci_name_backup AS "kennel.fciNameBackup", k.id AS "kennel.id", k.end_time AS "kennel.endTime", k.create_date AS "kennel.createDate", k.review_state AS "kennel.reviewState", k.fci_review_state AS "kennel.fciReviewState", k.type AS "kennel.type", k.print_state AS "kennel.printState", k.fci_print_state AS "kennel.fciPrintState", u2.login_name AS "user.loginName", u2.login_flag AS "user.loginFlag", u2.name AS "user.name", u2.user_type AS "user.userType", u2.photo AS "user.photo", u2.id AS "userId", case when a.member_code like 'MC%' then 'ckumc' when a.member_code like 'DC%' then 'ckudc' when a.member_code not regexp '^DQZ|CJH|CAB|HZ|MRHZ|DC|MC' then 'cku' else null end AS "clubType", u2.login_name AS "loginName", ifnull(a.register_flag,'0') AS "registerFlag" , a.register_province AS "registerProvince" , a.register_city AS "registerCity", a.register_area AS "registerArea", a.register_street AS "registerStreet", a.single_club_id AS "singleClubId" LEFT JOIN jeesite_sys_user u2 ON u2.id = a.user_id LEFT JOIN kennel k ON k.member_code = a.member_code and k.del_flag='0' SELECT FROM sys_member a WHERE a.id = #{id} SELECT a.id AS "id",a.user_id AS "user.id",a.member_end_date AS "memberEndDate" FROM sys_member a where TO_DAYS(a.member_end_date)-TO_DAYS(NOW())=#{days} SELECT a.id AS "id",a.user_id AS "user.id",a.groomer_end_date AS "groomerEndDate",a.member_code AS "memberCode" FROM sys_member a where TO_DAYS(a.groomer_end_date)-TO_DAYS(NOW())=#{days} SELECT FROM sys_member a a.del_flag = 0 AND a.member_code = #{memberCode} AND 1=2 SELECT FROM sys_member a a.del_flag = 0 AND a.member_code in #{item} AND 1=2 SELECT * FROM sys_member a where a.del_flag = 0 AND a.user_id = #{userId} SELECT * FROM sys_member a where a.del_flag = 0 AND a.mobile = #{mobile} SELECT a.id AS "id", a.user_id AS "user.id", a.member_code AS "memberCode", a.name AS "name", a.mobile AS "mobile" , a.gender AS "gender", gd.certificate_code AS "certificateCode", gd.certificate_level AS "certificateLevel" FROM sys_member a JOIN groomer_diploma gd on a.member_code = gd.member_code WHERE a.del_flag = '0' AND a.member_end_date = ]]> now() AND gd.del_flag = '0' AND a.registrant = #{registrant} GROUP BY memberCode SELECT a.id AS "id", a.user_id AS "user.id", a.member_code AS "memberCode", a.breed_code AS "breedCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.payment_info AS "paymentInfo", a.first_payment_time AS "firstPaymentTime", a.member_end_date AS "memberEndDate", a.ku_flag AS "kuFlag", a.ku_end_date AS "kuEndDate", a.groomer_type AS "groomerType", a.groomer_remarks AS "groomerRemarks", a.groomer_end_date AS "groomerEndDate", a.referee AS "referee", a.last_call AS "lastCall", a.ban_end_date AS "banEndDate", a.dg_member_type AS "dgMemberType", a.dg_end_date AS "dgEndDate", a.create_date AS "createDate", a.create_by AS "createBy.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", a.cku_club AS "ckuClub", a.registrant AS "registrant", a.account_balance AS "accountBalance", a.name AS "name", a.email AS "email", a.phone AS "phone", a.mobile AS "mobile", a.name_en AS "nameEn", a.card_type AS "cardType", a.card_no AS "cardNo", a.gender AS "gender", a.birthday AS "birthday", a.province AS "province", a.city AS "city", a.area AS "area", a.street AS "street", a.address AS "address", a.postcode AS "postcode", a.qq AS "qq", a.avatar AS "avatar", a.fax AS "fax", a.print_state AS "printState" FROM sys_member a a.del_flag='0' AND a.groomer_type is not NULL AND a.groomer_type ]]> '' AND (a.member_code = #{memberCode} or a.name LIKE concat('%',#{memberCode},'%')) AND a.groomer_end_date >= CURDATE() AND ( a.groomer_end_date < CURDATE() OR a.groomer_end_date IS NULL ) AND ( a.member_end_date < CURDATE() OR a.member_end_date IS NULL ) AND a.groomer_type = #{groomerType} AND a.groomer_end_date < CURDATE() ORDER BY ${page.orderBy} ORDER BY a.update_date DESC a.del_flag = #{DEL_FLAG_NORMAL} AND a.mobile = #{mobile} AND a.safe_mobile = #{safeMobile} AND (a.member_code = #{memberCode} or a.name LIKE CONCAT('%',#{memberCode},'%')) AND a.member_type = 'professional' and register_flag = "1" AND a.member_type = #{memberType} AND a.member_card_type = #{memberCardType} AND a.card_no = #{cardNo} AND a.contract_code !='' AND (a.member_end_date > CURDATE() or a.groomer_end_date > CURDATE()) AND ((a.member_end_date < CURDATE() and a.groomer_end_date < CURDATE()) or (a.member_end_date is NULL and a.groomer_end_date is NULL) ) AND a.review_state=#{reviewState} AND a.user_id = #{user.id} AND a.registrant=#{registrant} AND a.name=#{name} AND a.member_site ${sqlMap.memberSiteMap} AND b.login_name =#{user.loginName} AND b.user_type =#{user.userType} AND c.name LIKE CONCAT('%',#{kennel.name},'%') AND c.type = #{kennel.type} AND a.referee = #{referee} AND a.referee = #{referee} AND a.create_by = #{createBy.id} AND DATE_FORMAT(a.first_payment_time,'%Y-%m-%d') >= DATE_FORMAT(#{firstStartTime},'%Y-%m-%d') AND DATE_FORMAT(a.first_payment_time,'%Y-%m-%d') DATE_FORMAT(#{firstEndTime},'%Y-%m-%d') AND a.single_club_id = #{singleClubId} AND a.province = #{province} AND a.city = #{city} AND a.area = #{area} AND a.user_id in ( select cut.third_id from chongaibao_new.cab_sys_user cu join chongaibao_new.cab_sys_user_third_ref cut on cu.id = cut.user_id where cu.del_flag = 0 and cut.del_flag = 0 and cu.mobile = #{loginMobile} ) select count(1) from sys_member a INNER JOIN jeesite_sys_user b ON b.id = a.user_id INNER JOIN kennel c ON c.member_code = a.member_code INNER JOIN kennel c ON c.member_code = a.member_code INNER JOIN groomer_diploma gdiploma ON (a.member_code = gdiploma.member_code AND gdiploma.del_flag = '0' AND gdiploma.certificate_code IS NOT NULL ) SELECT FROM (select a.* from sys_member a INNER JOIN jeesite_sys_user b ON b.id = a.user_id INNER JOIN kennel c ON c.member_code = a.member_code INNER JOIN kennel c ON c.member_code = a.member_code INNER JOIN groomer_diploma gdiploma ON (a.member_code = gdiploma.member_code AND gdiploma.del_flag = '0' AND gdiploma.certificate_code IS NOT NULL ) ORDER BY ${page.orderBy} ORDER BY a.create_date DESC limit #{page.firstResult},#{page.maxResults} ) a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.create_date DESC SELECT FROM sys_member a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC select * from sys_account_balance_change a where a.member_code=#{membercode} AND a.create_date #{startTime} ORDER BY create_date desc limit 0,1 INSERT INTO sys_member( id, user_id, member_code, breed_code, member_type, member_card_type, member_site, groomer_type, groomer_end_date, payment_info, first_payment_time, member_end_date, ku_flag, ku_end_date, referee, last_call, ban_end_date, dg_member_type, dg_end_date, create_date, create_by, update_by, update_date, remarks, del_flag, cku_club, name, email, phone, mobile, name_en, card_type, card_no, gender, birthday, province, city, area, street, address, postcode, qq, avatar, registrant, delivery_certified, delivery_notice, print_state, review_state, reviewer, review_date, reviewer_name, freeze_flag, safe_mobile, authentication, contract_code, single_club_id, register_flag, register_province, register_city, register_area, register_street ) VALUES ( #{id}, #{user.id}, #{memberCode}, #{breedCode}, #{memberType}, #{memberCardType}, #{memberSite}, #{groomerType}, #{groomerEndDate}, #{paymentInfo}, #{firstPaymentTime}, #{memberEndDate}, #{kuFlag}, #{kuEndDate}, #{referee}, #{lastCall}, #{banEndDate}, #{dgMemberType}, #{dgEndDate}, #{createDate}, #{createBy.id}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag}, #{ckuClub}, #{name}, #{email}, #{phone}, #{mobile}, #{nameEn}, #{cardType}, #{cardNo}, #{gender}, #{birthday}, #{province}, #{city}, #{area}, #{street}, #{address}, #{postcode}, #{qq}, #{avatar}, #{registrant}, #{deliveryCertified}, #{deliveryNotice}, #{printState}, #{reviewState}, #{reviewer}, #{reviewDate}, #{reviewerName}, #{freezeFlag}, #{safeMobile}, #{authentication}, #{contractCode}, #{singleClubId}, #{registerFlag}, #{registerProvince}, #{province}, #{registerCity}, #{city}, #{registerArea}, #{area}, #{registerStreet} #{street} ) UPDATE sys_member SET breed_code = #{breedCode}, payment_info = #{paymentInfo}, first_payment_time = #{firstPaymentTime}, member_end_date = #{memberEndDate}, member_card_type = #{memberCardType}, member_type = #{memberType}, ku_flag = #{kuFlag}, ku_end_date = #{kuEndDate}, groomer_type = #{groomerType}, groomer_remarks = #{groomerRemarks}, groomer_end_date = #{groomerEndDate}, referee = #{referee}, last_call = #{lastCall}, ban_end_date = #{banEndDate}, dg_member_type = #{dgMemberType}, dg_end_date = #{dgEndDate}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks}, cku_club = #{ckuClub}, name = #{name}, email = #{email}, phone = #{phone}, mobile = #{mobile}, name_en = #{nameEn}, card_type = #{cardType}, card_no = #{cardNo}, gender = #{gender}, birthday = #{birthday}, province = #{province}, city = #{city}, area = #{area}, street = #{street}, address = #{address}, postcode = #{postcode}, qq = #{qq}, avatar = #{avatar}, fax = #{fax}, delivery_magazine = #{deliveryMagazine}, delivery_certified = #{deliveryCertified}, delivery_notice = #{deliveryNotice}, review_state =#{reviewState}, reviewer =#{reviewer}, review_date =#{reviewDate}, reviewer_name =#{reviewerName}, balance_blocked_flag =#{balanceBlockedFlag}, card_photo_fornt =#{cardPhotoFornt}, card_photo_rear =#{cardPhotoRear}, freeze_flag =#{freezeFlag}, contract_code = #{contractCode}, register_province = #{registerProvince}, register_city = #{registerCity}, register_area = #{registerArea}, register_street = #{registerStreet}, single_club_id = #{singleClubId} WHERE id = #{id} UPDATE sys_member SET user_id = #{user.id} WHERE id = #{id} UPDATE sys_member SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} UPDATE sys_member SET registrant = #{registrant}, cku_club = #{clubName} WHERE member_code = #{memberCode} update sys_member SET account_balance = account_balance - ${changeVal} WHERE member_code = #{memberCode} AND account_balance >= ${changeVal} update sys_member account_balance = account_balance + #{changeVal} WHERE member_code = #{memberCode} INSERT INTO sys_account_balance_change( member_code, running_number, before_change, change_amount ) VALUES ( #{memberCode}, #{runningNumber}, #{beforeChange}, #{changeAmount} ) update sys_member SET avatar=#{avatar} WHERE member_code = #{memberCode} update sys_member SET groomer_remarks=#{groomerRemarks} WHERE id = #{id} UPDATE sys_member SET cku_club=#{ckuClub}, update_by = #{updateBy.id}, update_date = #{updateDate} ,registrant=#{registrant} WHERE id = #{id} update sys_member SET groomer_type = #{groomerType}, groomer_end_date = #{groomerEndDate} WHERE member_code = #{memberCode} update sys_member groomer_type=#{groomerType}, groomer_end_date=#{groomerEndDate}, print_state=#{printState}, WHERE member_code = #{memberCode} UPDATE sys_member SET ku_flag = #{kuFlag}, ku_end_date = concat(YEAR(now()),'-12-31') WHERE member_code = #{memberCode} SELECT a.member_code AS "memberCode", a.name AS "name", a.member_end_date AS "memberEndDate", a.mobile AS "mobile", a.member_type AS "memberType", a.member_card_type AS "memberCardType" FROM sys_member a WHERE mobile = #{mobile} select id, user_id, member_code, member_type, member_card_type, member_end_date, del_flag, name, email, phone, mobile, name_en, card_type, card_no from sys_member where card_no=#{cardNo} and del_flag='0' AND (member_site IS NULL OR member_site = '') AND member_site = #{memberSite} limit 1 select id from sys_member where ${param} = #{val} AND del_flag='0' AND (member_site IS NULL OR member_site = '') AND member_site = #{memberSite} UPDATE sys_member SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = '1' WHERE id = #{id} update sys_member set print_state=#{printState} where id=#{id} update sys_member set member_type = #{memberType}, member_card_type = #{memberCardType}, member_end_date = #{endDate} where member_code = #{memberCode} select count(1) from sys_member where review_state=#{reviewState} and member_type=#{memberType} AND (member_end_date > CURDATE() or groomer_end_date > CURDATE()) update sys_member SET member_type = #{memberType}, member_card_type = #{memberCardType} ,member_end_date = null WHERE member_code = #{memberCode} update sys_member SET register_flag = #{registerFlag}, referee = '' WHERE member_code = #{memberCode} SELECT COUNT(1) FROM sys_member a WHERE a.card_no = #{cardNo} AND a.del_flag = '0' AND a.id != #{id} AND (member_site IS NULL OR member_site = '') AND a.member_site = #{memberSite} SELECT COUNT(1) FROM sys_member a WHERE a.contract_code = #{contractCode} AND a.del_flag = '0' AND a.id != #{id} SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) AND a.account_balance >= 120 AND a.member_type = 'basic' AND ifnull(a.register_flag,'0') != '1' AND (a.groomer_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) OR a.groomer_end_date IS NULL) AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' UNION SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) AND a.member_end_date > DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH),INTERVAL -1 DAY) AND a.account_balance >= 360 AND a.member_type = 'professional' AND ifnull(a.register_flag,'0') != '1' AND (a.groomer_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) OR a.groomer_end_date IS NULL) AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' UNION SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) AND a.member_end_date > DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH),INTERVAL -1 DAY) AND a.account_balance >= 840 AND a.member_type = 'professional' AND ifnull(a.register_flag,'0') = '1' AND (a.groomer_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) OR a.groomer_end_date IS NULL) AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' UNION SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date IS NULL AND a.groomer_end_date IS NULL AND a.account_balance >= 120 AND a.member_type = 'basic' AND ifnull(a.register_flag,'0') != '1' AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date < DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH),INTERVAL -1 DAY) AND a.account_balance >= 440 AND a.member_type = 'professional' AND ifnull(a.register_flag,'0') != '1' AND (a.groomer_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) OR a.groomer_end_date IS NULL) AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' UNION SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND (a.member_end_date < DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH),INTERVAL -1 DAY) or a.member_end_date IS NULL) AND a.account_balance >= 1120 AND a.member_type = 'professional' AND ifnull(a.register_flag,'0') = '1' AND (a.groomer_end_date < DATE_ADD(NOW(),INTERVAL -1 DAY) OR a.groomer_end_date IS NULL) AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' UNION SELECT a.id AS "id", a.member_code AS "memberCode", a.member_type AS "memberType", a.member_card_type AS "memberCardType", a.account_balance AS "accountBalance", ifnull(a.register_flag ,'0') AS "register_flag", a.member_end_date as "memberEndDate" FROM sys_member a WHERE a.del_flag = '0' AND a.member_end_date IS NULL AND a.groomer_end_date IS NULL AND a.account_balance >= 440 AND a.member_type = 'professional' AND ifnull(a.register_flag,'0') != '1' AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND a.balance_blocked_flag = '0' update sys_member set avatar = #{avatar} where id = #{id} SELECT FROM sys_member a WHERE a.member_end_date = DATE_ADD(CURDATE(),INTERVAL 10 DAY) AND a.del_flag = '0' AND LEFT(a.member_code,3) != 'DQZ' AND a.review_state = '1' SELECT FROM sys_member a WHERE a.member_end_date = DATE_ADD(CURDATE(),INTERVAL 5 DAY) AND a.del_flag = '0' AND LEFT(a.member_code,2) != 'HZ' AND LEFT(a.member_code,4) != 'MRHZ' AND LEFT(a.member_code,3) != 'DQZ' AND (a.groomer_type IS NULL OR a.groomer_type = '') AND (a.groomer_end_date DATE_ADD(NOW(),INTERVAL 5 DAY) OR a.groomer_end_date IS NULL) AND a.member_type IN ('basic','professional') AND a.review_state = '1' SELECT COUNT(1) FROM sys_member_register_ban WHERE id_number = #{cardNo} AND del_flag = '0' select sm.id, sm.user_id, sm.member_code, sm.name, sm.member_type, sm.member_card_type, sm.member_end_date, sm.groomer_type, sm.groomer_end_date, sm.create_date, ifnull(sm.register_flag,'0') as register_flag, sm.referee, k.id as "kennel.id", k.kennel_code as "kennel.kennelCode", k.type as "kennel.type", k.name as "kennel.name", k.name_en as "kennel.nameEn", k.end_time as "kennel.endTime" from sys_member sm left join (select * from kennel where del_flag = '0' and review_state != '3') k on sm.member_code = k.member_code where sm.del_flag = '0' and sm.user_id = #{userId} select sm.id, sm.user_id, sm.member_code, sm.name, sm.member_type, sm.member_card_type, sm.member_end_date, sm.groomer_type, sm.groomer_end_date, sm.create_date, ifnull(sm.register_flag,'0') as register_flag, sm.referee, k.id as "kennel.id", k.kennel_code as "kennel.kennelCode", k.type as "kennel.type", k.name as "kennel.name", k.name_en as "kennel.nameEn", k.end_time as "kennel.endTime", sm.mobile as "mobile" from sys_member sm left join (select * from kennel where del_flag = '0' and review_state != '3') k on sm.member_code = k.member_code where sm.del_flag = '0' and sm.member_code = #{memberCode} select sm.id, sm.user_id, sm.member_code, sm.name, sm.member_type, sm.member_card_type, sm.member_end_date, sm.groomer_type, sm.groomer_end_date, sm.create_date, ifnull(sm.register_flag,'0') as register_flag, sm.referee, k.id as "kennel.id", k.kennel_code as "kennel.kennelCode", k.type as "kennel.type", k.name as "kennel.name", k.name_en as "kennel.nameEn", k.end_time as "kennel.endTime", sm.mobile as "mobile" from sys_member sm left join (select * from kennel where del_flag = '0' and review_state != '3') k on sm.member_code = k.member_code where sm.del_flag = '0' and sm.id = #{memberId} update sys_member set safe_mobile = #{safeMobile} where id = #{id} update sys_member set safe_mobile = #{safeMobile},phone = #{phone},mobile = #{mobile} where id = #{id} update sys_member set account_balance = #{accountBalance} where member_code = #{memberCode} update sys_member set member_end_date = #{memberEndDate} where member_code = #{memberCode} update sys_member set member_card_type = #{memberCardType},member_end_date = #{memberEndDate} where member_code = #{memberCode} update sys_member set member_card_type = #{memberCardType} where member_code = #{memberCode} update sys_member set authentication = #{authentication} where id = #{id} update sys_member name=#{name}, card_no=#{cardNo}, safe_mobile=#{safeMobile}, remarks=#{remarks}, authentication=#{authentication}, WHERE member_code = #{memberCode} INSERT INTO cku_member_avatar_history( member_code, file_id ) VALUES ( #{memberCode}, #{avatar} ) SELECT FROM sys_member a WHERE a.member_code = #{memberCode} AND a.registrant = #{registrant} AND a.del_flag = '0' update sys_member set name_en = #{nameEn} where member_code = #{memberCode} update sys_member set user_id = #{userId}, phone = #{mobile}, mobile = #{mobile}, safe_mobile = #{mobile} WHERE member_code = #{memberCode} SELECT FROM sys_member a a.del_flag = 0 AND (a.member_code = #{memberCode} or a.mobile= #{memberCode}) AND 1=2 update sys_member SET dog_nose_repeated_detection=#{dogNoseRepeatedDetection}, dog_nose_liveness_detection=#{dogNoseLivenessDetection} WHERE member_code = #{memberCode} and del_flag = 0 update sys_member SET dog_nose_repeated_detection='0', dog_nose_liveness_detection='0' WHERE del_flag = 0 and (dog_nose_repeated_detection!='0' or dog_nose_liveness_detection !='0') update sys_member set member_end_date = #{endDate} where id = #{id} update sys_member set groomer_end_date = #{endDate} where id = #{id} SELECT FROM sys_member a AND ifnull(a.member_site,'') = '' AND a.member_site = #{memberSite} AND a.card_no = #{cardNo} AND a.del_flag = '0' UPDATE sys_member SET member_end_date = ( CASE WHEN member_end_date IS NULL THEN NULL ELSE DATE_FORMAT(date_sub(now(),INTERVAL 1 DAY),'%Y-%m-%d') END), groomer_end_date = ( CASE WHEN groomer_end_date IS NULL THEN NULL ELSE DATE_FORMAT(date_sub(now(),INTERVAL 1 DAY),'%Y-%m-%d') END), account_balance = 0 WHERE member_code = #{memberCode} AND del_flag = '0' SELECT login_name FROM jeesite_sys_user WHERE id = #{userId} SELECT sm.member_code, sm.member_type, sm.member_end_date, k.type, k.end_time, sm.member_card_type, CASE WHEN sm.member_type = 'foreign' THEN 'foreign' WHEN sm.member_end_date IS NULL AND ifnull(sm.member_card_type,'')='' THEN '1010' WHEN sm.member_end_date IS NULL AND ifnull(sm.member_card_type,'')!='' THEN sm.member_card_type WHEN sm.member_type = 'basic' AND sm.member_end_date IS NOT NULL THEN '1010' WHEN sm.member_type = 'professional' AND k.end_time IS NULL THEN '1020' WHEN sm.member_type = 'professional' AND k.type = 'year' AND k.end_time IS NOT NULL THEN '2030' WHEN sm.member_type = 'professional' AND k.type = 'permanent' AND k.end_time IS NOT NULL THEN '2040' ELSE '' END AS card_type FROM sys_member sm LEFT JOIN kennel k ON sm.member_code = k.member_code AND k.del_flag = '0' AND ifnull(k.review_state,'0') != '3' WHERE sm.del_flag = '0' UPDATE sys_member sm SET sm.member_card_type = ( SELECT t.card_type FROM () t WHERE sm.member_code = t.member_code ) WHERE member_code IN ( SELECT member_code FROM () t WHERE t.member_card_type != t.card_type ) SELECT DATE_FORMAT( a.member_end_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) AS isLoss, DATE_FORMAT( a.first_payment_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) AS isAdded, case when a.member_end_date CURDATE() then '0' when a.member_end_date = ]]> CURDATE() then '1' when a.member_end_date is null then '0' end AS isExpires, a.register_province, a.member_code , a.member_end_date, a.member_card_type, a.name, a.first_payment_time, a.groomer_end_date, a.groomer_type, a.mobile FROM sys_member a WHERE a.del_flag = '0' AND a.register_province IN #{item} and 2=1 and a.register_province = #{registerProvince} and a.member_code = #{memberCode} and a.mobile = #{mobile} AND (a.member_end_date CURDATE() or a.member_end_date is null ) AND a.member_end_date = ]]> CURDATE() AND a.member_end_date = ]]> #{memberEndDateStart} AND a.member_end_date #{memberEndDateEnd} and DATE_FORMAT( a.member_end_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = true and DATE_FORMAT( a.member_end_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = false and DATE_FORMAT( a.first_payment_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = true and DATE_FORMAT( a.first_payment_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = false and a.member_card_type = #{memberCardType} and (a.member_code = #{key} or a.name=#{key} ) and (a.groomer_type is null or a.groomer_type ='') and a.groomer_type = #{groomerType} AND a.groomer_end_date = ]]> #{groomerEndDateStart} AND a.groomer_end_date #{groomerEndDateEnd} ORDER BY a.create_date DESC SELECT a.member_end_date, a.first_payment_time, a.create_date, a.register_province, a.register_city, a.member_code , CASE WHEN a.member_card_type = 'foreign' THEN '外籍会员' WHEN a.member_card_type = '100' THEN '普卡' WHEN a.member_card_type = '1010' THEN '银卡' WHEN a.member_card_type = '1020' THEN '金卡' WHEN a.member_card_type = '2030' THEN '铂金卡' WHEN a.member_card_type = '2040' THEN '紫金卡' ELSE a.member_card_type END AS member_card_type, a.name, k.name as kennelName, k.create_date as kennelCreateDate, k.end_time, a.mobile, a.register_area, a.register_street, r.office_name, r.region, a.groomer_end_date, CASE WHEN a.groomer_type = 'reserve' THEN '预备会员' WHEN a.groomer_type = 'formal' THEN '正式会员' ELSE a.groomer_type END AS groomer_type FROM sys_member a LEFT JOIN kennel k ON k.member_code=a.member_code AND k.del_flag='0' LEFT JOIN show_office_region r ON r.region=a.register_province WHERE a.del_flag='0' AND a.first_payment_time = ]]> #{memberEndDateStart} AND a.first_payment_time date_format(#{memberEndDateEnd},'%Y-%m-%d 23:59:59') AND a.member_end_date = ]]> #{memberEndDateStart} AND a.member_end_date date_format(#{memberEndDateEnd},'%Y-%m-%d 23:59:59') AND a.register_province IN #{item} order by r.region ,a.first_payment_time ,a.member_end_date UPDATE chongaibao_new.cab_sys_user SET del_flag = '1' WHERE mobile = #{mobile} UPDATE jeesite_sys_user SET del_flag = '1' WHERE id = #{userId} SELECT a.member_end_date, a.first_payment_time, a.create_date, a.register_province, a.register_city, a.member_code , CASE WHEN a.member_card_type = 'foreign' THEN '外籍会员' WHEN a.member_card_type = '100' THEN '普卡' WHEN a.member_card_type = '1010' THEN '银卡' WHEN a.member_card_type = '1020' THEN '金卡' WHEN a.member_card_type = '2030' THEN '铂金卡' WHEN a.member_card_type = '2040' THEN '紫金卡' ELSE a.member_card_type END AS member_card_type, a.name, k.name as kennelName, k.create_date as kennelCreateDate, k.end_time, a.mobile, a.register_area, a.register_street, r.office_name, r.region, a.groomer_end_date, CASE WHEN a.groomer_type = 'reserve' THEN '预备会员' WHEN a.groomer_type = 'formal' THEN '正式会员' ELSE a.groomer_type END AS groomer_type , case when a.member_end_date CURDATE() then '0' when a.member_end_date = ]]> CURDATE() then '1' when a.member_end_date is null then '0' end AS isExpires FROM sys_member a LEFT JOIN kennel k ON k.member_code=a.member_code AND k.del_flag='0' LEFT JOIN show_office_region r ON r.region=a.register_province WHERE a.del_flag='0' AND a.register_province IN #{item} and 2=1 and a.register_province = #{registerProvince} and a.member_code = #{memberCode} and a.mobile = #{mobile} AND (a.member_end_date CURDATE() or a.member_end_date is null ) AND a.member_end_date = ]]> CURDATE() AND a.member_end_date = ]]> #{memberEndDateStart} AND a.member_end_date #{memberEndDateEnd} and DATE_FORMAT( a.member_end_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = true and DATE_FORMAT( a.member_end_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = false and DATE_FORMAT( a.first_payment_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = true and DATE_FORMAT( a.first_payment_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) = false and a.member_card_type = #{memberCardType} and (a.member_code = #{key} or a.name=#{key} ) and (a.groomer_type is null or a.groomer_type ='') and a.groomer_type = #{groomerType} AND a.groomer_end_date = ]]> #{groomerEndDateStart} AND a.groomer_end_date #{groomerEndDateEnd} ORDER BY a.create_date DESC SELECT a.id AS "id", a.user_id AS "user.id", a.member_code AS "memberCode", a.name AS "name", a.mobile AS "mobile" , a.gender AS "gender", a.member_end_date, a.groomer_end_date FROM sys_member a WHERE a.del_flag = '0' AND a.groomer_end_date = ]]> now() AND a.registrant = #{registrant} GROUP BY memberCode