HeatingPaymentsInvoker/conf/sqlroot/com/yinjie/heating/business/dao/ClientDao.dql

-- [queryClientIndustry]
select * from $table$;

-- [addClientIndustry]
insert into $table$ (id, industryCode, industryName, enable,
                                  createBy, createTime,
                                  updateBy, updateTime ) values ( #{id}, #{industrycode}, #{industryname}, #{enable},
#{createby}, #{createtime},
#{updateby}, #{updatetime} );

-- [updateClientIndustry]
update $table$ set industryCode = #{industrycode},industryName = #{industryname},
 updateBy = #{updateby},updateTime = #{updatetime} where id = #{id};

-- [enableClientIndustry]
update $table$ set enable = #{enable},updateBy = #{updateby},updateTime = #{updatetime} where id = #{id};

-- [queryClientInfo]
select * from $table$;

-- [addClientInfo]
insert into $table$ (
id, idIndustry, industryName, clientName,
contactMan, contactPhone, clientCode,
province, city, area,
address, clientFrom, overdraftQuota,
idLevel, levelName, idSalesManBelong, salesManNameBelong,
enable, createBy, createTime,
updateBy, updateTime,
invoiceHead,taxRegisterCode,baseBankName,
baseBankNumber,registerAddress,registerPhone,
signKey,dutyMan,againstFlag)
values ( #{id}, #{idindustry}, #{industryname}, #{clientname},
#{contactman}, #{contactphone}, #{clientcode},
#{province}, #{city}, #{area},
#{address}, #{clientfrom}, #{overdraftquota},
#{idlevel}, #{levelname}, #{idsalesmanbelong}, #{salesmannamebelong},
#{enable}, #{createby}, #{createtime},
#{updateby}, #{updatetime},
#{invoicehead},#{taxregistercode},#{basebankname},
#{basebanknumber}, #{registeraddress}, #{registerphone},
#{signkey},#{dutyman},#{againstflag});

-- [updateClientInfo]
update $table$ set
idIndustry = #{idindustry},
industryName = #{industryname},
clientName = #{clientname},
contactMan = #{contactman},
contactPhone = #{contactphone},
province = #{province},
city = #{city},
area = #{area},
address = #{address},
clientFrom = #{clientfrom},
overdraftQuota = #{overdraftquota},
idLevel = #{idlevel},
levelName = #{levelname},
idSalesManBelong = #{idsalesmanbelong},
salesManNameBelong = #{salesmannamebelong},
updateBy = #{updateby},
updateTime = #{updatetime},
invoiceHead = #{invoicehead},
taxRegisterCode=#{taxregistercode},
baseBankName=#{basebankname},
baseBankNumber = #{basebanknumber},
registerAddress= #{registeraddress},
registerPhone=#{registerphone},
signKey = #{signkey},
dutyMan = #{dutyman},
againstFlag = #{againstflag}
where id = #{id};

-- [enableClientInfo]
update $table$ set enable = #{enable},
updateBy = #{updateby},updateTime = #{updatetime}
where id = #{id};

-- [queryClientByBalance]
select round(a.balanceNum / 100.0, 2) as balance_money, b.id, b.clientCode, b.clientName
from $table1$ a
left join $table2$ b on a.idClient = b.id
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if filterZeroBalance > 0
    a.balanceNum > 0
  -- end
  -- isNotEmpty keyWord
    and b.clientName LIKE #{Like:keyWord}
  -- end
-- end
-- isEmpty orderBy
order by b.createTime desc
-- else
order by #{orderBy}
-- end
;

-- [sumAllClientBalance]
select 0 as idClient,sum(balanceNum) as balanceNum from $table1$ a left join $table2$ b on a.idClient = b.id where b.enable = 1;

-- [queryClientByBalanceVersion2]
select round(b.balanceNum / 100.0, 2) as balance_money, a.id, a.clientCode, a.clientName,c.totalChargeMoney
from $table2$ a
left join $table1$ b on a.id = b.idClient
left join $table3$ c on a.id = c.idClient
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if filterZeroBalance > 0
    b.balanceNum > 0
  -- end
  -- if filterZeroChargeMoney > 0
      c.totalChargeMoney > 0
    -- end
  -- isNotEmpty keyWord
    and a.clientName LIKE #{Like:keyWord}
  -- end
-- end
-- isEmpty orderBy
order by a.createTime desc
-- else
order by #{orderBy}
-- end
;

-- [queryClientLevel]
select * from $table$;

-- [addClientLevel]
insert into $table$ (id, levelName, enable,levelValue,
                     createBy, createTime, updateBy,updateTime)
                     values (#{id}, #{levelname}, #{enable}, #{levelvalue},
                     #{createby},#{createtime}, #{updateby},#{updatetime} );

-- [updateClientLevel]
update $table$ set levelName = #{levelname},levelValue = #{levelvalue},updateBy = #{updateby},updateTime = #{updatetime}
where id = #{id};

-- [enableClientLevel]
update $table$ set enable = #{enable},updateBy = #{updateby},updateTime = #{updatetime}
where id = #{id};

-- [queryClientReceiveAddress]
select * from $table$;

-- [addClientReceiveAddress]
insert into $table$ (id, idClient, receiveMan, receivePhone, idShipMode,
                     shipModeName,idShipArea, shipArea, province, city,
                     area, address, coordinate, defaultFlag, shipOrLogistics,
                     createBy, createTime,
                     updateBy, updateTime ) values ( #{id}, #{idclient}, #{receiveman}, #{receivephone}, #{idshipmode},
#{shipmodename}, #{idshiparea}, #{shiparea}, #{province}, #{city},
#{area}, #{address}, #{coordinate}, #{defaultflag}, #{shiporlogistics},
#{createby}, #{createtime},
#{updateby}, #{updatetime} );

-- [updateClientReceiveAddress]
update $table$ set receiveMan = #{receiveman},receivePhone = #{receivephone},idShipMode = #{idshipmode},shipModeName = #{shipmodename},
       idShipArea = #{idshiparea}, shipArea = #{shiparea}, province = #{province},city = #{city},area = #{area},address = #{address}, coordinate=#{coordinate},
       shipOrLogistics = #{shiporlogistics},
updateBy = #{updateby},updateTime = #{updatetime} where id = #{id};

-- [defaultClientReceiveAddress]
update $table$ set defaultFlag = 1,
updateBy = #{updateby},updateTime = #{updatetime}
where id = #{id};

-- [unDefaultClientReceiveAddress]
update $table$ set defaultFlag = 0,
updateBy = #{updateby},updateTime = #{updatetime}
where id != #{id} and idClient=#{idclient};

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

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