dySdDesigner/conf/sqlroot/com/sdtool/business/dao/MallDao.dql

-- [accountExists]
SELECT count(id)
FROM $table$
WHERE loginAccount = #{loginAccount};

-- [accountId]
SELECT IFNULL(u.id, -1) AS id
FROM (SELECT 1) AS dummy
LEFT JOIN $table$ u ON u.loginAccount = #{loginAccount};

--[insertMallLoginUser]
insert into $table$ (
id, userName, loginName, loginAccount, password,
qqCode, voidFlag, balanceFlag, idLevel,
levelExpireDate, levelExpireDateLong,
verifyFlag, idVerifyUser, verifyUser, verifyDate, verifyDateLong,
createBy, createTime)
values (
#{id}, #{username}, #{loginname}, #{loginaccount}, #{password},
#{qqcode}, #{voidflag}, #{balanceflag}, #{idlevel},
#{levelexpiredate}, #{levelexpiredatelong},
#{verifyflag}, #{idverifyuser}, #{verifyuser}, #{verifydate}, #{verifydatelong},
#{createby}, #{createtime});

--[editMallUserPassword]
update $table$ set password = #{newPassword} where id = #{id};

--[resetMallUserPassword]
update $table$ set passWord = #{linkedPassword} where loginAccount = #{linkedAccount};

--[editMallUserUserName]
update $table$ set userName = #{username} where id = #{id};

--[voidMallLoginUser]
update $table$ set voidFlag = #{voidflag} where id = #{id};

--[verifyMallLoginUser]
update $table$ set
verifyFlag = #{verifyflag},
idVerifyUser = #{idverifyuser},
verifyUser = #{verifyuser},
verifyDate = #{verifydate},
verifyDateLong = #{verifydatelong},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [queryMallUserList]
select id, userName, loginName, loginAccount,
qqCode, voidFlag, balanceFlag, idLevel,
levelExpireDate, levelExpireDateLong,
verifyFlag, idVerifyUser, verifyUser, verifyDate, verifyDateLong,
createBy, createTime,
updateBy, updateTime
from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- isNotEmpty voidFlag
    -- if voidFlag >= 0
        voidFlag = #{voidFlag}
    -- end
  -- end

  -- isNotEmpty idLevel
      -- if idLevel >= 0
          idLevel = #{idLevel}
      -- end
  -- end

  -- isNotEmpty keyWord
    and (userName LIKE #{Like:keyWord} or loginName LIKE #{Like:keyWord} or loginAccount LIKE #{Like:keyWord})
  -- end

  -- isNotEmpty expire30Day
    -- if expire30Day >= 1
        and (levelExpireDateLong > 0 AND levelExpireDateLong <= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 30 DAY)) * 1000)
    -- end
  -- end
-- end
order by id desc;

--[setMallUserIdLevel]
update $table$ set
idLevel = #{idlevel},
levelExpireDate = #{levelexpiredate},
levelExpireDateLong = #{levelexpiredatelong}
where id = #{id};

--[setMallUserExpire]
update $table$ set
levelExpireDate = #{levelexpiredate},
levelExpireDateLong = #{levelexpiredatelong}
where id = #{id};

-- [queryMallSocialUser]
select * from $table$
where fromKind = 1 and voidFlag = 0
-- isNotEmpty idUser
    -- if idUser >= 0
        and idUser = #{idUser}
    -- end
-- end
-- isNotEmpty keyword
    and (userNickName LIKE #{Like:keyword:Like})
-- end
order by id desc;

-- [getMallSocialUser]
select * from $table$ where id = #{id};

-- [bindMallSocialUser]
insert into $table$ (
id, idUser, fromKind, userOpenId,
userNickName, userLinkPhone, avatarUrl,
userIdentity, voidFlag,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong )
values (
#{id}, #{iduser}, #{fromkind}, #{useropenid},
#{usernickname}, #{userlinkphone}, #{avatarurl},
#{useridentity}, #{voidflag},
#{createby}, #{createtime}, #{createtimelong},
#{updateby}, #{updatetime}, #{updatetimelong} );

-- [unBindMallSocialUser]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

--[addMallUserAddress]
insert into $table$ (id, userId, receiveMan, receivePhone,
province, city, area,
address, defaultFlag, createBy,
createTime, updateBy,
updateTime) values ( #{id}, #{userid}, #{receiveman}, #{receivephone},
#{province}, #{city}, #{area},
#{address}, #{defaultflag}, #{createby},
#{createtime}, #{updateby},
#{updatetime});

--[editMallUserAddress]
update $table$ set userId = #{userid},receiveMan = #{receiveman},receivePhone = #{receivephone},province = #{province},
city = #{city},area = #{area},address = #{address},
createBy = #{createby},createTime = #{createtime},updateBy = #{updateby},
updateTime = #{updatetime} where id = #{id};

--[deleteMallUserAddress]
delete from $table$ where id = #{id};

--[queryMallUserAddress]
select id, userId, receiveMan, receivePhone,
province, city, area,
address, defaultFlag, createBy,
createTime, updateBy,
updateTime from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if userId >= 0
    userId = #{userId}
  -- end
  -- isNotEmpty keyword
    and (receiveMan LIKE #{Like:keyword:Like} or receivePhone LIKE #{Like:keyword:Like} or address LIKE #{Like:keyword:Like})
  -- end
-- end
-- isEmpty orderBy
order by createTime desc
-- else
order by #{orderBy}
-- end
;

--[getMallUserAddress]
select id, userId, receiveMan, receivePhone,
province, city, area,
address, defaultFlag, createBy,
createTime, updateBy,
updateTime from $table$ where id = #{id};

--[defaultMallUserAddress]
update $table$ set defaultFlag = 1 where id = #{id};

--[cancelDefaultMallUserAddress]
update $table$ set defaultFlag = 0 where defaultFlag = 1 and userId = #{userId};

-- [addPosterLocation]
insert into $table$ (id, posterName, posterLocationImage,
posterLocation, clickLinkUrl,sortIndex,
createBy, createTime,createTimeLong,
updateBy, updateTime,updateTimeLong ) values (
#{id}, #{postername}, #{posterlocationimage},
#{posterlocation}, #{clicklinkurl},#{sortindex},
#{createby}, #{createtime},#{createtimelong},
#{updateby}, #{updatetime},#{updatetimelong} );

-- [editPosterLocation]
update $table$ set posterName = #{postername},
posterLocationImage = #{posterlocationimage},
posterLocation = #{posterlocation},
clickLinkUrl = #{clicklinkurl},
sortIndex = #{sortindex},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};


-- [deletePosterLocation]
delete from $table$ where id = #{id};

--[queryPosterLocation]
select id, posterName, posterLocationImage,
posterLocation, clickLinkUrl,sortIndex,
createBy, createTime,createTimeLong,
updateBy, updateTime,updateTimeLong
from $table$;

-- [updateMallOrderMoney]
update $table$ set
orderMoneyTotal = #{ordermoneytotal},
mallOrderMoneyPay = #{mallordermoneypay},
mallOrderMoneyCharge = #{mallordermoneycharge}
where id = #{id};

-- [updateMallOrderAddress]
update $table$ set
linkMan = #{linkman},
linkPhone = #{linkphone},
sendProvince = #{sendprovince},
sendCity = #{sendcity},
sendDistrict = #{senddistrict},
sendAddress = #{sendaddress}
where id = #{id};

-- [sendMallOrder]
update $table$ set
sendState = #{sendstate},
idLogistics = #{idlogistics},
logisticsName = #{logisticsname},
sendKind = #{sendkind},
packDetail = #{packdetail}
where id = #{id};

-- [verifyMallOrder]
update $table$ set
mallVerifyMan = #{mallverifyman},
mallOrderStatus = #{mallorderstatus}
where id = #{id};

-- [updateMallOrderStatus]
update $table$ set
mallOrderStatus = #{mallorderstatus}
where id = #{id};

-- [updateMallOrderStatusForJoinERP]
update $table$ set
mallOrderStatus = #{mallorderstatus},
mallVerifyMan = #{mallverifyman},
platformOrderID = #{platformorderid}
where id = #{id};

-- [voidMallOrder]
update $table$ set
voidFlag = #{voidflag}
where id = #{id};

-- [insertOrderCancel]
insert into $table$ (
id, idOrder, status, idRequester,
requesterName, requestReason, idVerify, verifyName,
orderName, orderCode, orderQty,
payableMoney, userName, userCode, productName,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong )
values ( #{id}, #{idorder}, #{status}, #{idrequester},
#{requestername}, #{requestreason}, #{idverify}, #{verifyname},
#{ordername}, #{ordercode}, #{orderqty},
#{payablemoney}, #{username}, #{usercode}, #{productname},
#{createby}, #{createtime}, #{createtimelong},
#{updateby}, #{updatetime}, #{updatetimelong} );

-- [verifyOrderCancel]
update $table$ set
status = #{status},
idVerify = #{idverify},
verifyName = #{verifyname},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [saveOrderCancelImage]
update $table$ set
requestImageUrl = #{requestimageurl}
where id = #{id};

-- [insertMallUserLevel]
insert into $table$ (
id, levelName, levelValue, voidFlag,
checkFileFlag,
viewPriceDetailFlag, expireDays, idDowngradeLevel,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong )
values (
#{id}, #{levelname}, #{levelvalue}, #{voidflag},
#{checkfileflag},
#{viewpricedetailflag}, #{expiredays}, #{iddowngradelevel},
#{createby}, #{createtime}, #{createtimelong},
#{updateby}, #{updatetime}, #{updatetimelong} );

-- [updateMallUserLevel]
update $table$ set
levelName = #{levelname},
levelValue = #{levelvalue},
checkFileFlag = #{checkfileflag},
viewPriceDetailFlag = #{viewpricedetailflag},
expireDays = #{expiredays},
idDowngradeLevel = #{iddowngradelevel},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [voidMallUserLevel]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [queryMallUserLevel]
select * from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- isNotEmpty voidFlag
    -- if voidFlag >= 0
        voidFlag = #{voidFlag}
    -- end
  -- end

  -- isNotEmpty keyWord
    and (levelName LIKE #{Like:keyWord})
  -- end
-- end
order by id desc;

-- [addSelfTakeAddress]
insert into $table$ (
id, province, city, area,
address, defaultFlag, createBy,
createTime, createTimeLong, updateBy,
updateTime, updateTimeLong )
values(
#{id}, #{province}, #{city}, #{area},
#{address}, #{defaultflag}, #{createby},
#{createtime}, #{createtimelong}, #{updateby},
#{updatetime}, #{updatetimelong} );

-- [editSelfTakeAddress]
update $table$ set
province = #{province},city = #{city},
area = #{area},address = #{address},
defaultFlag = #{defaultflag},
updateBy = #{updateby},updateTime = #{updatetime},updateTimeLong = #{updatetimelong}
where id = #{id};

-- [deleteSelfTakeAddress]
delete from $table$ where id = #{id};

-- [querySelfTakeAddress]
select id, province, city, area,
address, defaultFlag, createBy,
createTime, createTimeLong, updateBy,
updateTime, updateTimeLong from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- isNotEmpty keyWord
    and (city LIKE #{Like:keyWord:Like} or area LIKE #{Like:keyWord:Like} or address LIKE #{Like:keyWord:Like})
  -- end
-- end
-- isEmpty orderBy
order by createTime desc
-- else
order by #{orderBy}
-- end
;

--[defaultSelfTakeAddress]
update $table$ set defaultFlag = 1 where id = #{id};

--[cancelDefaultSelfTakeAddress]
update $table$ set defaultFlag = 0 where defaultFlag = 1;

--[getSelfTakeAddress]
select id, province, city, area,
address, defaultFlag, createBy,
createTime, createTimeLong, updateBy,
updateTime, updateTimeLong from $table$
where id = #{id};