queryCuserListCount

SELECT count(*) FROM xzx_user_info a
LEFT JOIN xzx_sys_config_info b ON a.user_type=b.config_value AND b.config_type_code='USER_TYPE'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
where a.del_flag=0 
@if(!isEmpty(userType)){
    AND a.user_type=#userType#
@}
@if(!isEmpty(name)){
    AND a.nick_name=#name#
@}
@if(!isEmpty(phone)){
    AND a.mobile_phone=#phone#
@}

queryUserDayCount1

SELECT DATE_FORMAT(login_time, '%Y-%m-%d') AS dateHours,IFNULL(count(user_id),0) AS visitCount
  FROM xzx_user_login_info 
 WHERE login_time IS NOT null 
   AND DATE_FORMAT(login_time, '%Y-%m-%d')=DATE_FORMAT(CURDATE()-1, '%Y-%m-%d')
 GROUP BY dateHours ORDER BY login_id DESC 

queryUserDayCount

SELECT DATE_FORMAT(a.login_time, '%Y-%m-%d') AS dateHours,IFNULL(count(a.user_id),0) AS userDayCount
  FROM xzx_user_login_info a
  LEFT JOIN xzx_user_address_info ad ON a.user_id=ad.user_id
 WHERE a.login_time IS NOT null 
   AND ad.del_flag=0 AND ad.flag=1
   @if(!isEmpty(townIds)){
       AND ad.township_id in (#join(townIds)#)
   @}
   AND DATE_FORMAT(a.login_time, '%Y-%m-%d')=DATE_FORMAT(CURDATE()-1, '%Y-%m-%d')
 GROUP BY DATE_FORMAT(a.login_time, '%Y-%m-%d') ORDER BY a.login_id DESC 

queryCuserList

SELECT a.regist_time, a.nick_name,a.mobile_phone,a.user_id,b.config_value_name as user_type, a.user_type as user_typex, c.login_time FROM xzx_user_info a
LEFT JOIN xzx_sys_config_info b ON a.user_type=b.config_value AND b.config_type_code='USER_TYPE'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
where a.del_flag=0 
@if(!isEmpty(userType)){
    AND a.user_type=#userType#
@}
@if(!isEmpty(name)){
    AND a.nick_name=#name#
@}
@if(!isEmpty(phone)){
    AND a.mobile_phone like #'%'+phone+'%'# 
@}
limit #page#,#limit#

queryCuserDetail

SELECT a.*, b.money, b.withdraw_money, c.pay_type, SUM(c.money) as moneyx FROM #text(tableName)# a
LEFT JOIN xzx_account_info b ON a.user_id=b.user_id
LEFT JOIN xzx_pay_info c ON b.account_id=c.account_id 
LEFT JOIN xzx_sys_config_info d ON c.pay_type=d.config_value AND d.config_type_code='PAY_TYPE'
WHERE a.mobile_phone=#phone# AND a.user_type=#userType# AND a.del_flag=0 GROUP BY c.pay_type   

queryCuserAddress

SELECT * FROM xzx_user_address_info WHERE mobile_phone=#phone# and user_id=#userId# and del_flag = 0

queryCuserOrderCount
===
SELECT COUNT(*) as count, order_type FROM xzx_order_info WHERE create_user_id=#userId#
and order_status in (
@for(id in status){
#id# #text(idLP.last?"":"," )#
@}
)
GROUP BY order_type

queryCuserOrderFinishList
===
SELECT a.order_id as orderId, sum(d.money) as money,sum(d.weight) as weight,a.create_time as createTime FROM xzx_order_info a
LEFT JOIN xzx_order_item_info d on a.order_id = d.order_id
WHERE a.create_user_id=#userId#
and a.order_status in (
@for(id in status){
#id# #text(idLP.last?"":"," )#
@}
)
GROUP BY a.order_id
limit #page#,#limit#

queryCuserOrderFinishCount
===
select count(k.count1) from(
SELECT a.order_id as count1 FROM xzx_order_info a
LEFT JOIN xzx_order_item_info d on a.order_id = d.order_id
WHERE a.create_user_id=#userId#
and a.order_status in (
@for(id in status){
#id# #text(idLP.last?"":"," )#
@}
)
GROUP BY a.order_id ) k

queryCuserByPhoneList

SELECT a.regist_time, a.user_id,a.name, a.nick_name,a.mobile_phone,a.user_id,b.role_name  as user_type, c.login_time FROM #text(tableName)# a 
LEFT JOIN xzx_user_role_info b on a.user_type=b.role_code and b.del_flag='0'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
WHERE 1=1 AND a.del_flag=0
    @if(!isEmpty(phone)){
        AND a.mobile_phone like #'%'+phone+'%'#
    @}    
    limit #page#,#limit#

queryCuserByPhoneCount

SELECT count(*) as count FROM #text(tableName)# a 
LEFT JOIN xzx_sys_config_info b on a.user_type=b.config_value and b.config_type_code='USER_TYPE'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
WHERE 1=1 AND a.del_flag=0
    @if(!isEmpty(phone)){
        AND a.mobile_phone like #'%'+phone+'%'#
    @}    

queryCuserByPhone

SELECT a.regist_time, a.user_id, a.nick_name,a.mobile_phone,a.user_id,b.config_value_name as user_type, c.login_time FROM #text(tableName)# a 
LEFT JOIN xzx_sys_config_info b on a.user_type=b.config_value and b.config_type_code='USER_TYPE'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
WHERE 1=1 AND a.del_flag=0
        AND a.mobile_phone=#phone#

delCuserById

update #text(tableName)# set del_flag=1 where user_id=#id#

delCuserAccountById

update xzx_account_info set del_flag=1 where user_id=#id#

queryUserByUsertype1

select * from #text(tableName)# 
where 1=1
@if(!isEmpty(delFlag)){
    and del_flag=#delFlag#
@}
@if(!isEmpty(phone)){
    and (mobile_phone like #"%"+phone+"%"# or nick_name =#nickName#)
@}
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}
@if(!isEmpty(normal)){
    and regist_type=3
@}

queryUserByUsertype

select * from #text(tableName)# 
where 1=1
@if(!isEmpty(delFlag)){
    and del_flag=#delFlag#
@}
@if(!isEmpty(phone)){
        and (mobile_phone like #"%"+phone+"%"# or nick_name =#nickName#)
    @}
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}
@if(!isEmpty(normal)){
    and regist_type=3
@}

queryUserByUsertypeList

select * from #text(tableName)# 
where 1=1
@if(!isEmpty(delFlag)){
    and del_flag=#delFlag#
@}
@if(!isEmpty(phone)){
        and (mobile_phone like #"%"+phone+"%"# or nick_name =#nickName#)
    @}
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}
@if(!isEmpty(normal)){
    and regist_type=3
@}

queryUserOldUserType

select * from #text(tableName)# 
where 1=1 and del_flag=0
@if(!isEmpty(phone)){
    and mobile_phone=#phone#
@}
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}

queryUserById

select * from xzx_user_info where del_flag = 0
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}

queryUserOtherByPhone

 select * from xzx_user_other_info where mobile_phone=#phone# and del_flag=0

queryUserOtherById

select * from xzx_user_other_info where del_flag = 0
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}  

queryUserOtherByIdC

select * from xzx_user_other_info where del_flag = 0 and user_type in (2,4)
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}

queryOrderByPhone

select * from xzx_order_info where rela_phone=#phone# and order_status in (1,2) and del_flag=0    

queryPartnerId

select partner_id from xzx_partner_fence where 1=1 and del_flag=0
@if(!isEmpty(area)){
    and fence_id=#area# 
@}  
 limit 1     

addCuserRole

INSERT INTO #text(tableName)# (user_id, mobile_phone,`password`, salt, `name`,user_type,  regist_time,town_id,partner_id,role_ids,avatar,city_id,storage_id) 
values (#id#,#mobilephone#,#password#,#salt#,#name#,#userType#,SYSDATE(),#area#,#partnerId#,#roleId#,'https://res.cnxzx.com/resource/static/img/icon-loadlogo.png',#cityId#,#storageId#)

queryAllRole

SELECT * FROM xzx_sys_config_info WHERE config_type_code='USER_TYPE'

queryAllAPPRole

SELECT * FROM xzx_user_role_info WHERE del_flag='0'

querySysStrangeList

 select * from xzx_sys_storage where del_flag=0
  @if(!isEmpty(area)){
         area in ( #join(townIds)#)  
     @} 

queryCuserByPhoneRedisDelList

SELECT user_id AS userId FROM xzx_user_other_info WHERE del_flag=0 
@if(!isEmpty(phone)){
    AND mobile_phone=#phone#
@}

queryCuserListByName

SELECT  a.user_id as userId,a.name FROM xzx_user_other_info a 
    WHERE a.del_flag=0 and a.user_type=2
       and a.name is not null 
        @if(!isEmpty(partnerIds)){
                                    and a.partner_id in ( #join(partnerIds)# )
                                @}  
       and a.user_id  not in (select b.user_id from xzx_user_vehicle_rel b where b.del_flag=0 
       @if(!isEmpty(userId)){
               and b.user_id<>#userId#
           @}
      group by b.user_id) 

queryCuserByTypeList

SELECT * FROM xzx_user_other_info WHERE del_flag=0 
@if(!isEmpty(userType)){
    AND user_type=#userType#
@}
@if(!isEmpty(phone)){
    AND mobile_phone=#phone#
@}
@if(!isEmpty(nickName)){
    AND nick_name LIKE #'%'+nickName+'%'#
@}
limit #page#, #limit#

queryCuserByTypeListCount

SELECT count(*) FROM xzx_user_other_info WHERE del_flag=0 
@if(!isEmpty(userType)){
    AND user_type=#userType#
@}
@if(!isEmpty(phone)){
    AND mobile_phone=#phone#
@}
@if(!isEmpty(nickName)){
    AND nick_name LIKE #'%'+nickName+'%'#
@}

updateUserName

update xzx_user_info set name=#name# where mobile_phone=#mobile#

updateOtherUserName

update xzx_user_other_info set name=#name# where mobile_phone=#mobile#    

updateOtherUserById1

update xzx_user_other_info set is_prohibit=1 where partner_id=#partnerId#  

updatePromoterName

update xzx_user_other_info set name=#name# where mobile_phone=#mobile# and user_type=6    

queryQromoterList
===
SELECT nick_name, mobile_phone, user_id, regist_time, name FROM xzx_user_other_info
WHERE user_type=6 and del_flag=0
@if(!isEmpty(nickName)){
AND nick_name like #'%'+nickName+'%'#
@}
@if(!isEmpty(mobilePhone)){
AND mobile_phone like #'%'+mobilePhone+'%'#
@}
@if(!isEmpty(name)){
AND name like #'%'+name+'%'#
@}
@if(!isEmpty(paramName)){
AND (name like #'%'+paramName+'%'# or mobile_phone like #'%'+paramName+'%'#)
@}
@if(!isEmpty(userId)){
AND user_id =#userId#
@}
@if(!isEmpty(partnerIds)){
AND partner_id in ( #join(partnerIds)# )
@}
limit #page#, #limit#

queryQromoterListCount

SELECT count(*) FROM xzx_user_other_info 
WHERE user_type=6 and del_flag=0
@if(!isEmpty(nickName)){
    AND nick_name like  #'%'+nickName+'%'# 
@}
@if(!isEmpty(mobilePhone)){
    AND mobile_phone like  #'%'+mobilePhone+'%'# 
@}
@if(!isEmpty(name)){
    AND name like  #'%'+name+'%'#
@}
@if(!isEmpty(paramName)){
        AND (name like  #'%'+paramName+'%'# or  mobile_phone like  #'%'+paramName+'%'# )
@}
@if(!isEmpty(userId)){
            AND user_id =#userId#
    @}
@if(!isEmpty(partnerIds)){
       AND  partner_id in ( #join(partnerIds)# )
    @}     

queryQromoterLeven
===
select IFNULL(k.regist_type,0) as regsterType,
IFNULL(m.registCount,0) as registCount,IFNULL(m.count,0) as count,IFNULL(m.unregistCount,0) as unregistCount,
c.nick_name, c.mobile_phone, c.user_id, c.regist_time, c.name
from xzx_user_other_info c
LEFT JOIN (
select count(a.id) as registCount,count(f.id) as count,count(j.id) as unregistCount,
a.real_user_id,a.mobile
from xzx_user_target_info a
LEFT JOIN xzx_user_target_info f on a.id=f.id and f.regster_type=3
LEFT JOIN xzx_user_target_info j ON a.id=j.id and j.regster_type=1
LEFT JOIN xzx_user_other_info l on a.real_user_id = l.user_id
where l.del_flag=0 and l.user_type=6
group by l.user_id
) m ON m.real_user_id=c.user_id
LEFT JOIN xzx_user_info k ON m.mobile=k.mobile_phone
where 1=1 and c.user_type=6 and c.del_flag=0
@if(!isEmpty(userId)){
AND c.user_id=#userId#
@}
@if(!isEmpty(startTime)){
AND k.regist_time>=#startTime#
@}
@if(!isEmpty(endTime)){
AND k.regist_time<=#endTime#
@}
@if(!isEmpty(partnerIds)){
AND c.partner_id in ( #join(partnerIds)# )
@}
@if(!isEmpty(nickName)){
AND c.nick_name like #'%'+nickName+'%'#
@}
@if(!isEmpty(mobilePhone)){
AND c.mobile_phone like #'%'+mobilePhone+'%'#
@}
@if(!isEmpty(name)){
AND c.name like #'%'+name+'%'#
@}
@if(!isEmpty(paramName)){
AND (c.name like #'%'+paramName+'%'# or c.mobile_phone like #'%'+paramName+'%'# )
@}
group by c.user_id
@if(!isEmpty(page)){
limit #page#, #limit#
@}

queryPromoterRegiterTimeCount

SELECT count(*) as count FROM #text(tableName)# a WHERE a.del_flag=0
@if(!isEmpty(startTime)){
    AND a.regist_time BETWEEN #startTime# AND #endTime# 
@}
AND a.user_type=1 AND a.mobile_phone IN (
    SELECT mobile FROM xzx_user_target_info a WHERE del_flag=0
    @if(!isEmpty(userId)){
        AND user_id=#userId# 
    @}
    AND regster_type=2
)

queryPromoterOrderCount

SELECT IFNULL(SUM(y.money),0) as money, IFNULL(SUM(weight),0) as weight  FROM xzx_order_info x 
LEFT JOIN xzx_order_detail_info y ON x.order_id = y.order_id
WHERE x.create_user_id IN (
    SELECT a.user_id FROM xzx_user_info a WHERE del_flag=0
    @if(!isEmpty(startTime)){
        AND a.regist_time BETWEEN #startTime# AND #endTime# 
    @}
    AND a.user_type=1 AND a.mobile_phone IN (
       SELECT mobile FROM xzx_user_target_info a WHERE del_flag=0 
       @if(!isEmpty(userId)){
            AND user_id=#userId# 
        @}
       AND regster_type=2
    )
) AND x.order_status=5

delUserAddress

update xzx_user_address_info set del_flag=1 where user_id=#id# or mobile_phone=#phone#

updateDelflag

update #text(tableName)# set del_flag = 0
   @if(!isEmpty(password)){
        ,password=#password# 
    @}
    @if(!isEmpty(salt)){
         ,salt = #salt#
    @}
    @if(!isEmpty(area)){
         ,town_id=#area#
    @}
    @if(!isEmpty(name)){
         ,name=#name#
    @}
    @if(!isEmpty(roleId)){
         ,role_ids=#roleId#
    @}
    @if(!isEmpty(partnerId)){
                 ,partner_id=#partnerId#
            @}
    @if(!isEmpty(cityId)){
         ,city_id=#cityId#
    @} 
    @if(!isEmpty(storageId)){
                 ,storage_id=#storageId#
            @}        
where mobile_phone=#phone# 
 @if(!isEmpty(userType)){
   and user_type=#userType#
 @}

queryAccountInfo

select * from xzx_account_info where del_flag=0 
@if(!isEmpty(userId)){
    AND user_id=#userId# 
@}
@if(!isEmpty(phone)){
    AND account_name=#phone# 
@}

queryPromoterOrderWeightMoney

SELECT SUM(c.weight) as orderWeightCount, SUM(c.money) as orderMoneyCount 
FROM xzx_order_info b 
LEFT JOIN xzx_order_detail_info k on b.order_id=k.order_id
LEFT JOIN xzx_order_item_info c ON b.order_id=c.order_id 
WHERE b.order_type=1 AND b.order_status in (4,5,7) AND b.rela_phone in  (
    SELECT x.mobile FROM xzx_user_target_info x
         WHERE x.del_flag=0
         @if(!isEmpty(userId)){
                AND x.real_user_id=#userId#
            @}
            @if(!isEmpty(startTime)){
                AND k.complete_time>=#startTime#
            @}
            @if(!isEmpty(endTime)){
                AND k.complete_time<=#endTime#
            @}                

)

queryCollectorNum

select user_id from xzx_user_other_info 
where user_type=2 
and del_flag=0
@if(!isEmpty(partnerIds)){
   AND partner_id in ( #join(partnerIds)# )
@}

queryAllOrderByUserId
===
select IFNULL(count(a.order_id),0) from xzx_order_info a
left join xzx_order_detail_info b on a.order_id=b.order_id
where order_type=1 and a.order_status in (4,5,7) and a.rela_phone in
(
SELECT x.mobile FROM xzx_user_target_info x
WHERE x.del_flag=0
@if(!isEmpty(userId)){
AND x.real_user_id=#userId#
@}
@if(!isEmpty(startTime)){
AND b.complete_time>=#startTime#
@}
@if(!isEmpty(endTime)){
AND b.complete_time<=#endTime#
@}
)

totalWeightAndMoney
===

         select IFNULL(sum(a.storage_weight),0) as storageWeight ,
                IFNULL(sum(a.storage_money),0) as storageMoney,
                IFNULL(sum(a.recycle_weight),0) as recycleWeight,
                IFNULL(sum(a.recycle_money),0) as recycleMoney 
         from xzx_order_storage_info a 
         left join xzx_user_other_info b on a.receiver=b.user_id
         where a.storage_status=0   
         @if(!isEmpty(userId)){
               AND a.receiver=#userId#
         @}
         @if(!isEmpty(partnerIds)){
               AND b.partner_id in ( #join(partnerIds)# )
         @}

queryCollectorPageList

select a.receiver as userId,
       a.receiver_phone as mobilePhone,
       IFNULL(sum(a.storage_weight),0) as storageWeight,
       IFNULL(sum(a.storage_money),0) as storageMoney,
       IFNULL(sum(a.recycle_weight),0) as recycleWeight,
       IFNULL(sum(a.recycle_money),0) as recycleMoney,
       (IFNULL(sum(a.storage_weight),0) - IFNULL(sum(a.recycle_weight),0))  as weightError,
       (IFNULL(sum(a.storage_money),0) - IFNULL(sum(a.recycle_money),0))  as moneyError,
       b.name as name
 from xzx_order_storage_info a 
 left join xzx_user_other_info b on a.receiver=b.user_id
 where a.storage_status=0
 @if(!isEmpty(userId)){
     AND a.receiver=#userId#
 @}
 @if(!isEmpty(startTime)){
     AND a.storage_time>=#startTime#
 @}
 @if(!isEmpty(endTime)){
     AND a.storage_time<=#endTime#
 @}
 @if(!isEmpty(name)){
     AND b.name like #"%"+name+"%"# or b.mobile_phone like #"%"+name+"%"#
 @}
 @if(!isEmpty(nickName)){
     AND b.nick_name like #"%"+nickName+"%"#
 @}
  @if(!isEmpty(partnerIds)){
          AND b.partner_id in ( #join(partnerIds)# )
  @}
 group by a.receiver
 limit #page#, #limit#

queryCollectorPageCount

select count(a.receiver)
 from xzx_order_storage_info a
 left join xzx_user_other_info b on a.receiver=b.user_id
 where a.storage_status=0 
 @if(!isEmpty(userId)){
   AND a.receiver=#userId#
 @}
 @if(!isEmpty(startTime)){
               AND a.storage_time>=#startTime#
 @}
 @if(!isEmpty(endTime)){
                AND a.storage_time<=#endTime#
 @}
  @if(!isEmpty(name)){
          AND b.name like #"%"+name+"%"# or b.mobile_phone like #"%"+name+"%"#
      @}
      @if(!isEmpty(nickName)){
          AND b.nick_name like #"%"+nickName+"%"#
      @}
       @if(!isEmpty(partnerIds)){
                    AND b.partner_id in ( #join(partnerIds)# )
            @}
 group by a.receiver     

queryUserOrderNum

   select a.order_id from xzx_order_info b
   left join xzx_order_detail_info a  on a.order_id=b.order_id
   left join xzx_user_other_info c  on b.receiver=c.user_id
   where b.order_status in (4,5,7) 
                 @if(!isEmpty(userId)){
                                 AND c.user_id=#userId#
                 @}
                 @if(!isEmpty(startTime)){
                        AND a.complete_time>=#startTime#
                 @}    
                 @if(!isEmpty(endTime)){
                        AND a.complete_time<=#endTime#
                 @} 
                 @if(!isEmpty(partnerIds)){
                        AND c.partner_id in ( #join(partnerIds)# )
                 @}

queryUserOrderNumExt

   select a.order_id from xzx_order_detail_info a
           left join xzx_order_info b  on a.order_id=b.order_id
           where b.order_status in (4,5,7) and a.order_id in ( #join(orderIds)#) 
          @if(!isEmpty(startTime)){
                 AND a.complete_time>=#startTime#
          @}    
         @if(!isEmpty(endTime)){
                 AND a.complete_time<=#endTime#
          @} 

queryStorageByUserId

   select a.storage_id as storageId,
         (select b.storage_name from xzx_sys_storage b where a.sys_storage_id=b.id) as storageName,
         a.storage_time as createTime,
         a.recycle_weight as recycleWeight,
         (IFNULL(a.storage_weight,0) - IFNULL(a.recycle_weight,0))  as weightError,
         a.recycle_money as recycleMoney,
         (IFNULL(a.storage_money,0) - IFNULL(a.recycle_money,0))  as moneyError
   from xzx_order_storage_info a   
   left join xzx_user_other_info b on a.receiver=b.user_id
   where a.storage_status=0
   @if(!isEmpty(receiver)){
         AND a.receiver=#receiver#
   @}
   @if(!isEmpty(startTime)){
                AND a.storage_time>=#startTime#
          @}
   @if(!isEmpty(endTime)){
                       AND a.storage_time<=#endTime#
                 @}       
   @if(!isEmpty(recycleWeight)){
         AND a.recycle_weight>=#recycleWeight#
   @}
   @if(!isEmpty(moneyError)){
         AND (IFNULL(a.storage_weight,0) - IFNULL(a.recycle_weight,0))>=#moneyError#
   @}
  @if(!isEmpty(partnerIds)){
                       AND b.partner_id in ( #join(partnerIds)# )
              @}   
   limit #page#, #limit#  

queryStorageByUserIdCount

   select count(a.storage_id)
   from xzx_order_storage_info a 
   left join xzx_user_other_info b on a.receiver=b.user_id  
   where a.storage_status=0
   @if(!isEmpty(receiver)){
         AND a.receiver=#receiver#
   @}
   @if(!isEmpty(recycleWeight)){
         AND a.recycle_weight>=#recycleWeight#
   @}
   @if(!isEmpty(startTime)){
                       AND a.storage_time>=#startTime#
                 @}
          @if(!isEmpty(endTime)){
                              AND a.storage_time<=#endTime#
                        @}  
   @if(!isEmpty(moneyError)){
         AND (IFNULL(a.storage_weight,0) - IFNULL(a.recycle_weight,0))>=#moneyError#
   @} 
   @if(!isEmpty(partnerIds)){
                        AND b.partner_id in ( #join(partnerIds)# )
               @}     

queryStorageNum

select count(user_id) 
from xzx_user_other_info 
where user_type='4' and del_flag=0
@if(!isEmpty(partnerIds)){
   AND partner_id in ( #join(partnerIds)# )
@}

queryPackageStorageNum

select count(a.user_id) 
from xzx_city_partner a
left join xzx_partner_gaode b on a.id=b.partner_id
where a.partner_type='2' and a.del_flag=0
   @if(!isEmpty(townIds)){
         AND town_id in ( #join(townIds)# )
      @}

queryStorageList

 select 
             (select b.storage_name from xzx_sys_storage b where a.sys_storage_id=b.id) as storageName,
             a.storage_time as createTime,
             IFNULL(sum(a.recycle_weight),0) as recycleWeight,
             IFNULL(sum(a.storage_weight),0)  as storageWeight,
             IFNULL(sum(a.recycle_money),0) as recycleMoney,
             IFNULL(sum(a.storage_money),0) as storageMoney,
             a.order_id as orderId,
             a.storage_user_id as storageUserId,
             t.name as name,
             storage_user_phone as storageUserPhone
       from xzx_order_storage_info a 
       left join  xzx_user_other_info t on  t.user_id=a.storage_user_id 
       where a.storage_status=0 and t.del_flag=0 and t.user_type =4
       @if(!isEmpty(name)){
             AND (t.name  like #"%"+name+"%"#  or a.storage_user_phone like #"%"+name+"%"#)
       @}
       @if(!isEmpty(nickName)){
             AND t.nick_name =#nickName#
       @}
       @if(!isEmpty(startTime)){
             AND a.storage_time >=#startTime#
       @}
       @if(!isEmpty(endTime)){
             AND a.storage_time <=#endTime#
       @}
       @if(!isEmpty(userType)){
             AND t.user_type =#userType#
       @}
       @if(!isEmpty(partnerIds)){
             AND t.partner_id in ( #join(partnerIds)# )
       @}
       group by a.storage_user_id
       limit #page#, #limit#  

queryStorageList1

  select 
                   (select b.storage_name from xzx_sys_storage b where a.sys_storage_id=b.id) as storageName,
                   a.storage_time as createTime,
                   IFNULL(sum(a.recycle_weight),0) as recycleWeight,
                   IFNULL(sum(a.storage_weight),0)  as storageWeight,
                   IFNULL(sum(a.recycle_money),0) as recycleMoney,
                   IFNULL(sum(a.storage_money),0) as storageMoney,
                   a.order_id as orderId,
                   a.storage_user_id as storageUserId,
                   t.name as name,
                   storage_user_phone as storageUserPhone
             from xzx_order_storage_info a 
             left join  xzx_user_other_info t on  t.user_id=a.storage_user_id 
             where a.storage_status=0 and t.del_flag=0 and t.user_type =7
             @if(!isEmpty(name)){
                   AND (t.name  like #"%"+name+"%"#  or a.storage_user_phone like #"%"+name+"%"#)
             @}
             @if(!isEmpty(nickName)){
                   AND t.nick_name =#nickName#
             @}
             @if(!isEmpty(startTime)){
                   AND a.storage_time >=#startTime#
             @}
             @if(!isEmpty(endTime)){
                   AND a.storage_time <=#endTime#
             @}
             @if(!isEmpty(userType)){
                   AND t.user_type =#userType#
             @}
             @if(!isEmpty(partnerIds)){
                   AND t.partner_id in ( #join(partnerIds)# )
             @}
             group by a.storage_user_id
             limit #page#, #limit#  

queryStorageCount

       select count(a.storage_id)
       from xzx_order_storage_info a   
        left join  xzx_user_other_info t on  t.user_id=a.storage_user_id 
       where a.storage_status=0 and t.del_flag=0 and t.user_type =4
       @if(!isEmpty(name)){
                        AND (t.name  like #"%"+name+"%"#  or a.storage_user_phone like #"%"+name+"%"#)
                  @}
       @if(!isEmpty(startTime)){
             AND a.storage_time >=#startTime#
       @}
       @if(!isEmpty(endTime)){
             AND a.storage_time <=#endTime#
       @}
       @if(!isEmpty(userType)){
                        AND t.user_type =#userType#
                  @}
       @if(!isEmpty(partnerIds)){
                        AND t.partner_id in ( #join(partnerIds)# )
       @}
       group by a.storage_user_id

queryStorageCount1

       select count(a.storage_id)
       from xzx_order_storage_info a   
        left join  xzx_user_other_info t on  t.user_id=a.storage_user_id 
       where a.storage_status=0 and t.del_flag=0 and t.user_type =7
       @if(!isEmpty(name)){
                        AND (t.name  like #"%"+name+"%"#  or a.storage_user_phone like #"%"+name+"%"#)
                  @}
       @if(!isEmpty(startTime)){
             AND a.storage_time >=#startTime#
       @}
       @if(!isEmpty(endTime)){
             AND a.storage_time <=#endTime#
       @}
       @if(!isEmpty(userType)){
                        AND t.user_type =#userType#
                  @}
       @if(!isEmpty(partnerIds)){
                        AND t.partner_id in ( #join(partnerIds)# )
       @}
       group by a.storage_user_id

queryStorageObjectList

select a.storage_id as storageId,
       a.receiver_phone as receiverPhone,
       a.storage_time as storageTime,
       IFNULL(sum(a.storage_weight),0) as storageWeight,
       IFNULL(sum(a.storage_money),0) as storageMoney,
       IFNULL(sum(a.recycle_weight),0) as recycleWeight,
       IFNULL(sum(a.recycle_money),0) as recycleMoney,
       (select b.name from xzx_user_other_info b where a.receiver=b.user_id) as name
 from xzx_order_storage_info a
 left join xzx_user_other_info b on a.receiver = b.user_id 
 where a.storage_status=0  
 @if(!isEmpty(storageUserId)){
     AND a.storage_user_id =#storageUserId#
 @}
 @if(!isEmpty(partnerIds)){
           AND b.partner_id in ( #join(partnerIds)# )
      @}
 group by a.receiver
 @if(!isEmpty(recycleWeight)){
            HAVING
                        IFNULL(sum(a.recycle_weight),0) <=#recycleWeight#
            @if(!isEmpty(storageWeight)){
                       and
                                IFNULL(sum(a.storage_weight),0) <=#storageWeight#
            @}              
       @}
   @if(isEmpty(recycleWeight)){
             @if(!isEmpty(storageWeight)){
                   HAVING
                            IFNULL(sum(a.storage_weight),0) >=#storageWeight#
              @}  
        @}
 limit #page#, #limit#  

queryStorageObjectCount

select count(a.storage_id) 
 from xzx_order_storage_info a 
 left join xzx_user_other_info b on a.receiver = b.user_id 
 where a.storage_status=0  
 @if(!isEmpty(storageUserId)){
     AND a.storage_user_id =#storageUserId#
 @}
 @if(!isEmpty(partnerIds)){
                AND b.partner_id in ( #join(partnerIds)# )
           @}
 group by a.receiver
  @if(!isEmpty(recycleWeight)){
       HAVING
                IFNULL(sum(a.recycle_weight),0) <=#recycleWeight#
       @if(!isEmpty(storageWeight)){
                  and
                           IFNULL(sum(a.storage_weight),0) <=#storageWeight#
       @}           
  @}
  @if(isEmpty(recycleWeight)){
       @if(!isEmpty(storageWeight)){
             HAVING
                      IFNULL(sum(a.storage_weight),0) >=#storageWeight#
        @}  
  @}

querySysStorageList

select (select c.level_3_name from xzx_sys_address_level_info c where a.area=c.level_3_id) as areaName,
       storage_name as storageName,
       id as id
from xzx_sys_storage a
 where a.del_flag=0
 @if(!isEmpty(area)){
         AND  a.area =#area#
 @}
 @if(!isEmpty(townIds)){
         AND  a.area in (#join(townIds)#)
 @}
 limit #page#, #limit# 

querySysStorageCount

select count(id)
from xzx_sys_storage a
 where a.del_flag=0
 @if(!isEmpty(area)){
         AND  a.area=#area#
 @}
  @if(!isEmpty(townIds)){
              AND  a.area in (#join(townIds)#)
      @}  

querySysStorageAll

select 
       IFNULL(sum(a.storage_weight),0) as storageWeight,
       IFNULL(sum(a.storage_money),0) as storageMoney,
       IFNULL(sum(a.recycle_weight),0) as recycleWeight,
       IFNULL(sum(a.recycle_money),0) as recycleMoney
 from xzx_order_storage_info a
 left join xzx_user_other_info b on a.receiver = b.user_id
 where a.storage_status=0  
 @if(!isEmpty(sysStorageId)){
     AND a.sys_storage_id =#sysStorageId#
 @}
@if(!isEmpty(startTime)){
     AND a.storage_time >=#startTime#
@}
@if(!isEmpty(endTime)){
     AND a.storage_time <=#endTime#
@}
@if(!isEmpty(partnerIds)){
     AND b.partner_id in ( #join(partnerIds)# )
@}

sysStorageOrderNum

select a.order_id 
from xzx_order_storage_info a
left join xzx_user_other_info b  on a.receiver = b.user_id
where a.storage_status=0 
@if(!isEmpty(sysStorageId)){
   AND a.sys_storage_id =#sysStorageId#
@}
@if(!isEmpty(startTime)){
   AND a.storage_time >=#startTime#
 @}
@if(!isEmpty(endTime)){
   AND a.storage_time <=#endTime#
@}
@if(!isEmpty(partnerIds)){
   AND b.partner_id in ( #join(partnerIds)# )
@}

queryDayDiff

   select timestampdiff(day,
    (select substring_index(storage_time," ",1) 
     from xzx_order_storage_info 
     where storage_status=0
     @if(!isEmpty(sysStorageId)){
         AND sys_storage_id >=#sysStorageId#
     @}
      @if(!isEmpty(startTime)){
          AND storage_time >=#startTime#
      @}
      @if(!isEmpty(endTime)){
          AND storage_time <=#endTime#
      @}
     order by storage_time 
     asc limit 1),
    (select substring_index(storage_time," ",1) 
     from xzx_order_storage_info 
     where storage_status=0
     @if(!isEmpty(sysStorageId)){
         AND sys_storage_id >=#sysStorageId#
     @}
     @if(!isEmpty(startTime)){
         AND storage_time >=#startTime#
     @}
     @if(!isEmpty(endTime)){
         AND storage_time <=#endTime#
     @}
     order by storage_time 
     desc limit 1)) from dual

getSysStorageTypeName

SELECT  sum(c.weight) AS weight, sum(c.money ) as money, d.title FROM xzx_order_info a 
    LEFT JOIN xzx_order_detail_info b ON a.order_id=b.order_id
    LEFT JOIN xzx_order_item_info c ON c.order_id=a.order_id 
    LEFT JOIN xzx_sys_environmental_info d ON c.item_type = d.item_type
    WHERE a.order_status=5 and b.complete_time is not null
    and a.order_id in (#join(orderIds)#)
     @if(!isEmpty(startTime)){
        AND b.complete_time >=#startTime#
     @}
     @if(!isEmpty(endTime)){
        AND b.complete_time <=#endTime#
     @}
     @if(!isEmpty(typeName)){
        AND d.title like #'%'+typeName+'%'#
     @}
    group by d.title  

queryApiArea

select level_3_id as id,level_3_name as name from xzx_sys_address_level_info where level_2_name like '%长沙%'  

sysStorageIdList

select sys_storage_id from xzx_order_storage_info group by sys_storage_id  

queryPageSysStorageTypeName

SELECT  sum(c.weight) AS weight, sum(c.money ) as money, d.title,d.id FROM xzx_order_info a 
    LEFT JOIN xzx_order_detail_info b ON a.order_id=b.order_id
    LEFT JOIN xzx_order_item_info c ON c.order_id=a.order_id 
    LEFT JOIN xzx_sys_environmental_info d ON c.item_type = d.item_type
    WHERE a.order_status=5 AND b.complete_time is not null
     AND a.order_id in (#join(orderIds)#)
     @if(!isEmpty(startTime)){
        AND b.complete_time >=#startTime#
     @}
     @if(!isEmpty(endTime)){
        AND b.complete_time <=#endTime#
     @}
     @if(!isEmpty(typeName)){
        AND d.title like #'%'+typeName+'%'#
     @}
    group by d.id     
    limit #page#, #limit#   

queryCountSysStorageTypeName

select count(*) from xzx_sys_environmental_info k where k.id in
(SELECT  d.id as id  FROM xzx_order_info a 
    LEFT JOIN xzx_order_detail_info b ON a.order_id=b.order_id
    LEFT JOIN xzx_order_item_info c ON c.order_id=a.order_id 
    LEFT JOIN xzx_sys_environmental_info d ON c.item_type = d.item_type
    WHERE a.order_status=5 AND b.complete_time is not null
        AND a.order_id in (#join(orderIds)#)
     @if(!isEmpty(startTime)){
        AND b.complete_time >=#startTime#
     @}
     @if(!isEmpty(endTime)){
        AND b.complete_time <=#endTime#
     @}
     @if(!isEmpty(typeName)){
        AND d.title like #'%'+typeName+'%'#
     @}
     group by d.id  
 )  

queryStorageDetailAll

select sum(a.weight) as weight,sum(a.money) as money,d.title,d.id
         from xzx_order_storage_detail a 
      LEFT JOIN xzx_sys_environmental_info d ON a.item_type = d.item_type
      LEFT JOIN xzx_order_storage_info f ON f.storage_id = a.storage_id
         where a.storage_id in (select b.storage_id from xzx_order_storage_info b) 
         and a.flag=1
         @if(!isEmpty(startTime)){
             AND f.storage_time >=#startTime#
            @}
            @if(!isEmpty(endTime)){
             AND f.storage_time <=#endTime#
            @}
            @if(!isEmpty(typeName)){
             AND d.title like #'%'+typeName+'%'#
            @}
         group by a.item_type   

UpdateUserApiByUserId

update xzx_user_info set is_prohibit=#flag# where user_id=#userId# 

UpdateUserApiByOtherUserId

update xzx_user_other_info set is_prohibit=#flag# where user_id=#userId# and user_type=#userType#

UpdateUserApiByTargetUserId

update xzx_user_target_info set is_prohibit=#flag# where user_id=#userId#

queryCuserForTimeList

SELECT a.regist_time, a.nick_name,a.mobile_phone,a.user_id,b.config_value_name as user_type, a.user_type as user_typex, 
( SELECT x.login_time FROM xzx_user_login_info x where x.user_id=a.user_id order by login_time desc limit 1) as loginTime, 
a.is_prohibit as isProhibit,a.avatar as avatar,
       (select k.name from xzx_user_info k where k.user_id=IFNULL(n.user_id,0) ) as tgName,(select j.name from xzx_user_info j where j.user_id=IFNULL(n.real_user_id,0) ) as yqName
FROM xzx_user_info a
LEFT JOIN xzx_sys_config_info b ON a.user_type=b.config_value AND b.config_type_code='USER_TYPE'
LEFT JOIN xzx_user_address_info ad ON a.user_id=ad.user_id
LEFT JOIN (
  select f.mobile,f.user_id,f.real_user_id from xzx_user_target_info f
  LEFT JOIN xzx_user_other_info m on f.real_user_id=m.user_id 
  where m.del_flag=0 
  @if(!isEmpty(partnerIds)){
              AND m.partner_id in ( #join(partnerIds)# )
   @}
) n ON n.mobile=a.mobile_phone
where a.del_flag=0  and a.regist_time is not null
and ad.del_flag=0 and ad.flag=1
@if(!isEmpty(townIds)){
       AND ad.township_id in (#join(townIds)#)
@}
@if(!isEmpty(userType)){
        AND a.user_type=#userType#
@}
@if(!isEmpty(startTime)){
    AND a.regist_time>=#startTime# 
@}
@if(!isEmpty(phone)){
        AND a.mobile_phone=#phone#
@}
@if(!isEmpty(endTime)){
    AND a.regist_time<=#endTime# 
@}
@if(!isEmpty(isProhibit)){
    AND a.is_prohibit=false
@}
limit #page#,#limit# 

queryPromoterApiList

select IFNULL(a.nick_name,k.nick_name) as nickName,a.mobile as mobilePhone,a.user_id as userId,c.is_prohibit,c.avatar as avatar,
     c.regist_time as registTime,c.name,
     count(a.id) as registNum,count(f.id) as finishNum,count(j.id) as unRegistNum
from xzx_user_target_info a
LEFT JOIN xzx_user_other_info c ON a.user_id=c.user_id  
LEFT JOIN xzx_user_target_info d ON a.id=d.id and d.regster_type>1
LEFT JOIN xzx_user_target_info f ON a.id=f.id and f.regster_type=3
LEFT JOIN xzx_user_target_info j ON a.id=j.id and j.regster_type=1
LEFT JOIN xzx_user_info k ON a.mobile=k.mobile_phone
 where 1=1 and c.user_type=6 and c.del_flag=0  
@if(!isEmpty(phone)){
      AND a.mobile=#phone#
 @}
 @if(!isEmpty(startTime)){
      AND a.create_time>=#startTime# 
 @}
 @if(!isEmpty(endTime)){
      AND a.create_time<=#endTime# 
 @}
 @if(!isEmpty(isProhibit)){
      AND a.is_prohibit=false
 @}
 @if(!isEmpty(partnerIds)){
           AND c.partner_id in ( #join(partnerIds)# )
      @}
group by a.user_id
limit #page#,#limit# 

queryPromoterApiCount

  select count(k.userId) from (
            select a.user_id as userId
            from xzx_user_target_info a
            LEFT JOIN xzx_user_other_info c ON a.user_id=c.user_id  
            LEFT JOIN xzx_user_target_info d ON a.id=d.id and d.regster_type>1
            LEFT JOIN xzx_user_target_info f ON a.id=f.id and f.regster_type=3
            LEFT JOIN xzx_user_target_info j ON a.id=j.id and j.regster_type=1
            LEFT JOIN xzx_user_info l ON a.mobile=l.mobile_phone 
            where 1=1 and c.user_type=6 and c.del_flag=0 
            @if(!isEmpty(phone)){
                  AND a.mobile=#phone#
             @}
             @if(!isEmpty(startTime)){
                  AND a.create_time>=#startTime# 
             @}
             @if(!isEmpty(endTime)){
                  AND a.create_time<=#startTime# 
             @}
             @if(!isEmpty(isProhibit)){
                  AND a.is_prohibit=false
             @}
             @if(!isEmpty(partnerIds)){
                            AND c.partner_id in ( #join(partnerIds)# )
             @}
            group by a.user_id  )k       

queryCuserForTimeListCount

    SELECT count(*) FROM xzx_user_info a
    LEFT JOIN xzx_sys_config_info b ON a.user_type=b.config_value AND b.config_type_code='USER_TYPE'
    LEFT JOIN xzx_user_address_info ad ON a.user_id=ad.user_id
    LEFT JOIN (
      select f.mobile from xzx_user_target_info f
      LEFT JOIN xzx_user_other_info m on f.real_user_id=m.user_id 
      where m.del_flag=0 
      @if(!isEmpty(partnerIds)){
                  AND m.partner_id in ( #join(partnerIds)# )
       @}
    ) n ON n.mobile=a.mobile_phone 
    where a.del_flag=0 and a.regist_time is not null
    and ad.del_flag=0 and ad.flag=1
        @if(!isEmpty(townIds)){
               AND ad.township_id in (#join(townIds)#)
        @}
    @if(!isEmpty(userType)){
                AND a.user_type=#userType#
      @}
    @if(!isEmpty(phone)){
                AND a.mobile_phone=#phone#
    @}
    @if(!isEmpty(startTime)){
            AND a.regist_time>=#startTime# 
    @}
    @if(!isEmpty(endTime)){
            AND a.regist_time<=#endTime# 
    @}

queryCollectorApiList

    select a.name as name,a.town_id as townId,a.nick_name as nickName,a.mobile_phone as mobilePhone,a.avatar as avatar,a.user_id as userId,a.name as name,
    (select count(b.order_id) from xzx_order_info b where 1=1
      @if(!isEmpty(userType)){
                      and b.receiver=a.user_id
      @}
         ) as orderNum, 
    (select g.sys_storage_id from xzx_order_storage_info g where 1=1
           @if(!isEmpty(userType)){
                 @if(userType=='2'){
                      and  g.receiver=a.user_id
                 @}
                 @if(userType=='4'){
                      and g.storage_user_id=a.user_id
                 @}
                  @if(userType=='7'){
                      and g.storage_user_id=a.user_id
                  @}
           @}
          limit 1) as sysStorageId,     
    (select sum(c.storage_weight) from xzx_order_storage_info c where 1=1 
               @if(!isEmpty(userType)){
                 @if(userType=='2'){
                   and  c.receiver=a.user_id
                  @}
                   @if(userType=='4'){
                    and c.storage_user_id=a.user_id
                   @}
                   @if(userType=='7'){
                                             and c.storage_user_id=a.user_id
                                         @}
                @}   
              )as storageWeight,
    (select sum(d.storage_money) from xzx_order_storage_info d where 1=1
                   @if(!isEmpty(userType)){
                         @if(userType=='2'){
                           and  d.receiver=a.user_id
                          @}
                           @if(userType=='4'){
                            and d.storage_user_id=a.user_id
                           @}
                           @if(userType=='7'){
                                                     and d.storage_user_id=a.user_id
                                                 @}
                      @}  
       ) as storageMoney,
    a.is_prohibit as isProhibit,
    IFNULL(a.regist_time,(select f.regist_time 
                    from xzx_user_info f
                    where f.user_id=a.user_id)) as registTime,
    (select j.login_time from xzx_user_login_info j where j.mobile_phone=a.mobile_phone  
        @if(!isEmpty(userType)){
                and   j.user_type=#userType#
            @}
    group by j.mobile_phone order by login_time  desc) as loginTime             
    from xzx_user_other_info a
    where  a.del_flag=0
    @if(!isEmpty(userType)){
          AND a.user_type=#userType#
    @}
    @if(!isEmpty(startTime)){
        AND IFNULL(a.regist_time,(select f.regist_time 
                                   from xzx_user_info f
                                   where f.user_id=a.user_id))>=#startTime# 
    @}
    @if(!isEmpty(endTime)){
        AND IFNULL(a.regist_time,(select f.regist_time 
                                 from xzx_user_info f
                                 where f.user_id=a.user_id)) <=#endTime# 
    @}
    @if(!isEmpty(mobilePhone)){
                        AND a.mobile_phone=#mobilePhone#
    @}
    @if(!isEmpty(area)){
                        AND a.town_id=#area#
    @}
    @if(!isEmpty(isProhibit)){
                         AND a.is_prohibit=#isProhibit#
            @}  
    @if(!isEmpty(partnerIds)){
                  AND a.partner_id in ( #join(partnerIds)# )
            @}           
    order by a.user_id desc
    limit #page#,#limit#  

queryCollectorApiCount

 select count(k.userId) from (
            select a.user_id as userId
            from xzx_user_other_info a
            where  a.del_flag=0
            @if(!isEmpty(userType)){
              AND   a.user_type=#userType#
            @}
            @if(!isEmpty(startTime)){
                AND IFNULL(a.regist_time,(select f.regist_time 
                                            from xzx_user_info f
                                            where f.user_id=a.user_id))>=#startTime#
            @}
            @if(!isEmpty(endTime)){
                AND IFNULL(a.regist_time,(select f.regist_time 
                                          from xzx_user_info f
                                          where f.user_id=a.user_id)) <=#endTime#
            @}
            @if(!isEmpty(mobilePhone)){
                                        AND a.mobile_phone=#mobilePhone#
            @}
            @if(!isEmpty(area)){
                                        AND a.town_id=#area#
            @}
            @if(!isEmpty(isProhibit)){
                                        AND a.is_prohibit=#isProhibit#
            @}
            @if(!isEmpty(partnerIds)){
                                  AND a.partner_id in ( #join(partnerIds)# )
                            @}    
             order by a.user_id desc
           )k 

orderListByStorageUserId
===
select order_id from xzx_order_storage_info where storage_user_id=#storageUserId#

querySysStorageNameById

 select storage_name as name from xzx_sys_storage where id = #sysStorageId#  

updateSysStorageName

 update   xzx_sys_storage set storage_name= #name#  where id = #sysStorageId#  

updateStorageUserNameById

 update xzx_user_other_info set name=#name# where user_id= #userId# 

queryUnregisteredApiList

select a.township_id as area,
     (select level_3_name from xzx_sys_address_level_info where level_3_id=a.township_id) as areaName ,
      a.mobile as mobilePhone,a.create_time as createTime,
      a.user_name as name,a.nick_name as nickName,
    b.name as tgyName,
    a.address_remark as address
from xzx_user_target_info a 
left join xzx_user_other_info b on b.user_id=a.user_id
where a.real_user_id is null 
         @if(!isEmpty(area)){
                 AND a.township_id=#area#
         @}
         @if(!isEmpty(startTime)){
                 AND a.create_time>=#startTime# 
         @}
         @if(!isEmpty(endTime)){
                 AND a.create_time<=#endTime# 
         @} 
         @if(!isEmpty(partnerIds)){
                 AND b.partner_id in ( #join(partnerIds)# )
          @} 
group by a.mobile  
order by a.create_time desc
limit #page#,#limit#   

queryUnregisteredApiCount

select count(k.mobile) from (
        select   a.mobile as mobile         
        from xzx_user_target_info a 
        left join xzx_user_other_info b on b.user_id=a.user_id
        where a.real_user_id is null 
                 @if(!isEmpty(area)){
                         AND a.township_id=#area#
                 @}
                 @if(!isEmpty(startTime)){
                         AND a.create_time>=#startTime# 
                 @}
                 @if(!isEmpty(endTime)){
                         AND a.create_time<=#endTime# 
                 @} 
                 @if(!isEmpty(partnerIds)){
                                      AND b.partner_id in ( #join(partnerIds)# )
                               @}  
        group by a.mobile  
        order by a.create_time desc  )k 

queryEditroleApi

SELECT a.regist_time, a.user_id,a.name, a.nick_name,a.mobile_phone,a.user_id,b.role_name  as user_type, c.login_time, 
       a.town_id as area,a.user_type as userTypeValue,a.city_id,a.partner_id,a.storage_id
FROM xzx_user_other_info a 
LEFT JOIN xzx_user_role_info b on a.user_type=b.role_code and b.del_flag='0'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
WHERE 1=1 AND a.del_flag=0 and role_ids is not null  
    @if(!isEmpty(mobilePhone)){
        AND a.mobile_phone like #'%'+mobilePhone+'%'#
    @} 
    @if(!isEmpty(partnerIds)){
                AND a.partner_id in ( #join(partnerIds)# )
    @}     
    limit #page#,#limit#

queryEditroleApiCount

SELECT count(*) as count FROM xzx_user_other_info a 
LEFT JOIN xzx_sys_config_info b on a.user_type=b.config_value and b.config_type_code='USER_TYPE'
LEFT JOIN (
    SELECT * FROM xzx_user_login_info GROUP BY user_id ORDER BY login_id
) as c ON c.user_id = a.user_id
WHERE 1=1 AND a.del_flag=0 and role_ids is not null
    @if(!isEmpty(mobilePhone)){
        AND a.mobile_phone like #'%'+mobilePhone+'%'#
    @} 
     @if(!isEmpty(partnerIds)){
                        AND a.partner_id in ( #join(partnerIds)# )
            @}  

queryRecoveryById

select * from xzx_user_other_info where del_flag = 0 
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}
limit 1 

queryUserOtherInfoById

select * from xzx_user_other_info where del_flag = 0
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}
limit 1  

queryUserOtherApiById

select * from xzx_user_other_info where del_flag = 0
@if(!isEmpty(userId)){
    and user_id=#userId# 
@}
@if(!isEmpty(userType)){
        and user_type=#userType# 
    @}
limit 1 

addCuserAccountRole

INSERT INTO xzx_account_info(`account_id`,`account_name`, `user_id`, `pay_password`, `money`, `freeze_money`, `withdraw_money`, `overdraft_limit`, `lock_time`,`fixed_limit`) 
VALUES (#id#, #phone#, #userId#, NULL, '0', '0', '0', #money#, NULL,#money#)

addPartnerAccount

INSERT INTO xzx_partner_account(`user_id`,`account`, `hbb`, `del_flag`, `update_time`,`partner_id`, `overdraft_limit`,`fixed_limit`,`type`) 
VALUES (#userId#,#account#,'0', '0', #updateTime#,#partnerId#,'0', '0',#type#)

updateCuserAccount

 update xzx_account_info set del_flag=0 where del_flag=1 
 @if(!isEmpty(userId)){
      and user_id=#userId# 
 @}
 @if(!isEmpty(phone)){
      and account_name=#phone# 
 @}    
   order by account_id desc limit 1       

queryUserTypeById

select role_code as userType from xzx_user_role_info where id=#id#

insertUserInfo

insert into xzx_user_info (user_id,mobile_phone, avatar, login_account, password, salt, name, nick_name,birthday,sex,user_type,open_id,country,province,city,longitude,latitude,regist_time,del_flag,regist_type,is_prohibit)
     values (
        #userId#, #mobilePhone#, #avatar#, #loginAccount#, #password#, #salt#, #name#,#nickName#,#birthday#,#sex#,1,null,'China',#province#,#city#,#longitude#,#latitude#,SYSDATE(),0,3,0
     )  

insertUserOtherInfo

insert into xzx_user_other_info (user_id,mobile_phone, login_account, password, salt, name, user_type,open_id,country,regist_time,del_flag,is_prohibit,role_ids,partner_id,avatar)
     values (
        #userId#, #mobilePhone#, #loginAccount#, #password#, #salt#, #name#,#userType#,null,'China',SYSDATE(),0,0,#roleIds#,#partnerId#,#avatar#
     ) 

updateUserInfo

update xzx_user_info  set 
     mobile_phone=#mobilePhone#, 
     avatar=#avatar#, login_account= #loginAccount#,
      password=#password#, salt=#salt#, name=#name#, 
      nick_name=#nickName#,birthday=#birthday#,sex=#sex#,
      user_type=1,open_id=null,country='China',
      province=#province#,city=#city#,longitude=#longitude#,
      latitude=#latitude#,regist_time=SYSDATE(),del_flag=0,is_prohibit=#isProhibit#,regist_type=#registType# where user_id=#userId#

insertUserAddress

insert into xzx_user_address_info (mobile_phone, user_id, province_id, city_id, township_id, address_area, detail_address,longitude,latitude,flag,rela_phone,rela_name,del_flag,create_time)
         values (
            #mobilePhone#, #userId#, #provinceId#, #cityId#, #townshipId#, #addressArea#,#detailAddress#,#longitude#,#latitude#,1, #relaPhone#,#relaName#,0,SYSDATE()
         ) 

updateUserAddress

update xzx_user_address_info set flag=#flag# 
where mobile_phone=#mobilePhone# 
and user_id=#userId#
@if(!isEmpty(addressId)){
   and address_id not in (#addressId# )
@}                

queryUserAddressList
===
select * from xzx_user_address_info where mobile_phone=#mobilePhone# and user_id=#userId# and flag=1

queryProvinceByName

select level_1_id as level1Id 
    from xzx_sys_address_level_info where 1=1 and level_1_name=#name#

queryCityByCityName
===
select level_2_id as level2Id
from xzx_sys_address_level_info where 1=1 and level_2_name=#name#

queryTownShipByTownName

select level_3_id as level3Id
    from xzx_sys_address_level_info where 1=1 and level_3_name=#name#  

queryTownCodeByTownName

select adcode as code
    from xzx_sys_address_level_info where 1=1 and level_3_name=#name#  

queryElectricTownIds

select b.partner_key from xzx_partner_fence a
LEFT JOIN xzx_city_partner b ON a.partner_id = b.id
LEFT JOIN xzx_electronic_fence c ON a.fence_id=c.id where 1=1
     @if(!isEmpty(partnerId)){
           and a.partner_id=#partnerId# 
        @}  
where a.del_flag=0

queryTownIdsByCode

select level_3_id as level3Id
    from xzx_sys_address_level_info where 1=1 and adcode in ( #join(townIds)#) 

queryProvinceById

select level_1_name as provinceName 
    from xzx_sys_address_level_info where 1=1 and level_1_id=#id#

queryCityByCityId
===
select level_2_name as cityName
from xzx_sys_address_level_info where 1=1 and level_2_id=#id#

queryTownShipByTownId

select level_3_name as cityName
    from xzx_sys_address_level_info where 1=1 and level_3_id=#id#  

queryUserAddressInfo

select * from  xzx_user_address_info  
where mobile_phone =  #mobilePhone# and user_id= #userId#  and flag=1 order by create_time desc limit 1     

queryStoreList

select id as id ,address_area as addressArea,detail_address as detailAddress from xzx_sys_store_info where del_flag=0 and id<>-1    

queryUserOrderApiList

 select a.order_id,a.create_time,sum(b.weight) ,sum(b.money) from xzx_order_info a
    left join xzx_order_item_info b on a.order_id=b.order_id 
    where a.order_status in ('4','5','7') 
    and a.create_user_id=#userId#
    group by a.order_id
    limit #page#,#limit#

queryUserOrderApiCount

select count(k.orderId) from (
 select a.order_id as orderId from xzx_order_info a
    left join xzx_order_item_info b on a.order_id=b.order_id 
    where a.order_status in ('4','5','7') 
    and a.create_user_id=#userId#
    group by a.order_id)k

queryUserOrderList

select a.create_time as createTime,a.order_id as orderId,sum(b.money) as money,sum(b.weight) as weight from xzx_order_info a 
LEFT JOIN xzx_order_item_info b ON a.order_id = b.order_id
where a.create_user_id=#userId#
and a.order_status in ('4','5','7') 
and a.del_flag=0 and a.cancel_user_id is null
GROUP BY a.order_id order by a.create_time        

queryEleIdByGid

select id from xzx_electronic_fence where gid=#gid#              

queryOrderIdsByPartners

select a.order_id from xzx_order_info a 
left join xzx_user_other_info b on a.receiver=b.user_id
where a.del_flag=0 
@if(!isEmpty(partnerIds)){
         and b.partner_id in ( #join(partnerIds)# )   
    @}

updateOtherUserById

   update  xzx_user_other_info set terminal_id=#tid#   
   where del_flag=0 and user_id=#id# and user_type=#userType#   

queryCollectorWM

select IFNULL(sum(money),0) from xzx_pay_info where pay_type =13 and create_user_id=#userId#                                                             

checkOtherUserByType

select * from xzx_user_other_info where del_flag=0
@if(!isEmpty(userType)){
    and user_type=#userType# 
@}
@if(!isEmpty(mobilePhone)){
    and mobile_phone=#mobilePhone# 
@}  

queryTgNameByRealUserId

select b.name from xzx_user_target_info a
  left join xzx_user_other_info b on a.user_id=b.user_id
  where a.mobile=#mobilePhone# and b.user_type=6 and b.del_flag=0