l1024-jav/src/main/java/top/lvzhiqiang/mapper/CoinMapper.java

package top.lvzhiqiang.mapper;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import top.lvzhiqiang.entity.*;

import java.math.BigDecimal;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * coin-Mapper
 *
 * @author lvzhiqiang
 * 2023/9/9 17:37
 */
public interface CoinMapper {

    @Insert({"<script>" +
            "INSERT ignore INTO coin_order_history(symbol,size,orderId,clientOid,filledQty,fee,price,priceAvg,state,side" +
            ",timeInForce,totalProfits,posSide,marginCoin,leverage,marginMode,orderType,reduceOnly,enterPointSource,tradeSide" +
            ",holdMode,orderSource,cTime,uTime)" +
            " VALUES " +
            "<foreach collection='list' item='p' index=\"index\" separator=\",\">" +
            "   ( #{p.symbol}, #{p.size}, #{p.orderId}, #{p.clientOid}, #{p.filledQty}, #{p.fee}, #{p.price}, #{p.priceAvg}, #{p.state}" +
            ", #{p.side}, #{p.timeInForce}, #{p.totalProfits}, #{p.posSide}, #{p.marginCoin}, #{p.leverage}, #{p.marginMode}, #{p.orderType}" +
            ", #{p.reduceOnly}, #{p.enterPointSource}, #{p.tradeSide}, #{p.holdMode}, #{p.orderSource}, #{p.cTime}, #{p.uTime})" +
            "</foreach>" +
            "</script>"})
    int insertHistoryOrderList(List<CoinHistoryOrder> historyOrderList);

    @Select({"<script>" +
            "select *  from coin_order_history WHERE  1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"side != null and side != ''\">" +
            "   and side = #{side}" +
            "</if>" +
            " order by ${sortField} ${sort}" +
            "</script>"})
    List<CoinHistoryOrder> findHistoryOrderList(Map<String, Object> params);

    @Select("select symbol,type,category,sort from coin_monitor_currency where delete_flag = 1 order by sort")
    List<CoinMonitorCurrency> findMonitorCurrencyList();

    @Select("select * from coin_public_params where 1=1")
    @MapKey("codeKey")
    List<CoinPubilcParams> findPublicParams();

    @Insert({"<script>" +
            "INSERT INTO coin_trader(traderUid,traderNickName,maxFollowCount,followCount,totalFollowers,profitOrderCount,lossOrderCount," +
            "totalTradeCount,dailyProfitRate,dailyProfit,copyTradeDays,roi,totalProfit,totalFollowersProfit,aum,maxCallbackRate,last3wWinRate," +
            "averageWinRate,traderHeadPic,canTrace,tradingPairsAvailable,profile,lastTradeTime,modify_time)" +
            " VALUES " +
            "<foreach collection='list' item='mt' index=\"index\" separator=\",\">" +
            "   (#{mt.traderUid},#{mt.traderNickName},#{mt.maxFollowCount},#{mt.followCount},#{mt.totalFollowers},#{mt.profitOrderCount},#{mt.lossOrderCount}," +
            "#{mt.totalTradeCount},#{mt.dailyProfitRate},#{mt.dailyProfit},#{mt.copyTradeDays},#{mt.roi},#{mt.totalProfit},#{mt.totalFollowersProfit},#{mt.aum}," +
            "#{mt.maxCallbackRate},#{mt.last3wWinRate},#{mt.averageWinRate},#{mt.traderHeadPic},#{mt.canTrace},#{mt.tradingPairsAvailable},#{mt.profile},#{mt.lastTradeTime}, now())" +
            " </foreach>" +
            " ON DUPLICATE KEY UPDATE traderNickName=values(traderNickName),maxFollowCount=values(maxFollowCount),followCount=values(followCount),totalFollowers=values(totalFollowers)," +
            "profitOrderCount=values(profitOrderCount),lossOrderCount=values(lossOrderCount),totalTradeCount=values(totalTradeCount),dailyProfitRate=values(dailyProfitRate)," +
            "dailyProfit=values(dailyProfit),copyTradeDays=values(copyTradeDays),roi=values(roi),totalProfit=values(totalProfit),totalFollowersProfit=values(totalFollowersProfit)," +
            "aum=values(aum),maxCallbackRate=values(maxCallbackRate),last3wWinRate=values(last3wWinRate),averageWinRate=values(averageWinRate),traderHeadPic=values(traderHeadPic)," +
            "canTrace=values(canTrace),tradingPairsAvailable=values(tradingPairsAvailable),profile=values(profile),lastTradeTime=values(lastTradeTime),modify_time=now()" +
            "</script>"})
    void insertMixTradeList(List<CoinTrader> parseMixTradeList);

    @Select({"<script>" +
            "select *  from coin_trader WHERE  1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and traderNickName like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"canTrace != null and canTrace != ''\">" +
            "   and canTrace = #{canTrace}" +
            "</if>" +
            " order by ${sortField} ${sort}" +
            "</script>"})
    List<CoinTrader> findMixTraderList(Map<String, Object> params);

    @Select("select concat(traderUid, '|', traderNickName) from coin_monitor_trader where delete_flag = '1'")
    List<String> findMonitorTraderList();

    @Select({"<script>" +
            "select *  from coin_watchlist WHERE delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"symbol != null and symbol != ''\">" +
            "   and symbol = #{symbol}" +
            "</if>" +
            "<if test=\"filterField != null and filterField != ''\">" +
            "   and filter_flag = #{filterField}" +
            "</if>" +
            "<if test=\"trackCategoryField != null and trackCategoryField != ''\">" +
            "   and track_category like concat('%',#{trackCategoryField},'%')" +
            "</if>" +
            " order by " +
            "<foreach collection='sortField' item='sf' index=\"index\" separator=\",\">" +
            "   ${sf} ${sort}" +
            " </foreach>" +
            "</script>"})
    List<CoinWatchlist> findWatchlistList(Map<String, Object> params);

    @Select({"<script>" +
            "select cw.* from coin_watchlist cw left join coin_watchlist_other cwo on cw.symbol=cwo.symbol WHERE cw.delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and cw.symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"symbol != null and symbol != ''\">" +
            "   and cw.symbol = #{symbol}" +
            "</if>" +
            "<if test=\"filterField != null and filterField != ''\">" +
            "   and cw.filter_flag = #{filterField}" +
            "</if>" +
            "<if test=\"trackCategoryField != null and trackCategoryField != ''\">" +
            "   and cw.track_category like concat('%',#{trackCategoryField},'%')" +
            "</if>" +
            "<if test=\"trackCategory2Field != null and trackCategory2Field != ''\">" +
            "   and cw.track_category2 like concat('%',#{trackCategory2Field},'%')" +
            "</if>" +
            "<if test=\"cexFilterField != null and cexFilterField != ''\">" +
            "   <choose>" +
            "       <when test=\" cexFilterName == 'cwo.cex_spot' \">" +
            "           and substring(cwo.cex_spot,#{cexFilterIndex},1) = '1'" +
            "       </when>" +
            "       <otherwise>" +
            "           and substring(cwo.cex_perpetual,#{cexFilterIndex},1) = '1'" +
            "       </otherwise>" +
            "   </choose>" +
            "</if>" +
            " order by " +
            "<foreach collection='sortField' item='sf' index=\"index\" separator=\",\">" +
            "   ${sf} ${sort}" +
            " </foreach>" +
            "</script>"})
    List<CoinWatchlist> findWatchlistList2(Map<String, Object> params);

    @Update("update coin_watchlist set cmc_id=#{cmcId},total_market_ranking=#{totalMarketRanking},total_market_value=#{totalMarketValue}," +
            "mark_price=#{markPrice},price_change_percentage_24h=#{priceChangePercentage24h},highest_historical_price=#{highestHistoricalPrice},ath_change_percentage=#{athChangePercentage},atl_change_percentage=#{atlChangePercentage},lowest_historical_price=#{lowestHistoricalPrice}," +
            "highest_historical_date=#{highestHistoricalDate},lowest_historical_date=#{lowestHistoricalDate},increase_multiple=#{increaseMultiple}," +
            "issuing_days=#{issuingDays},coingecko_url=#{coingeckoUrl},cmc_url=#{cmcUrl},feixiaohao_url=#{feixiaohaoUrl},modify_time=now() where id = #{id}")
    int updateCoinWatchlist(CoinWatchlist coinWatchlist);

    @Insert("INSERT INTO coin_watchlist_other(symbol, max_supply, circulating_supply, circulating_rate, total_supply, market_cap, fully_diluted_market_cap, create_time, modify_time) " +
            "VALUES (#{symbol}, #{maxSupply}, #{circulatingSupply}, #{circulatingRate}, #{totalSupply}, #{marketCap}, #{fullyDilutedMarketCap}, now(), now())" +
            " ON DUPLICATE KEY UPDATE max_supply=values(max_supply),circulating_supply=values(circulating_supply),circulating_rate=values(circulating_rate),total_supply=values(total_supply)" +
            ",market_cap=values(market_cap),fully_diluted_market_cap=values(fully_diluted_market_cap),modify_time=now()")
    int insertOrUpdateCoinWatchlistOther(CoinWatchlistOther coinWatchlistOther);

    @Select("select a.*,b.coingecko_id from coin_watchlist_other a left join coin_watchlist b on a.symbol=b.symbol where a.delete_flag = 1 and a.symbol not in ('BTC','ETH')")
    List<CoinWatchlistOther> findAllCoinWatchlistOther();

    @Select("select * from coin_watchlist_other where symbol = #{symbol} and delete_flag = 1")
    CoinWatchlistOther findWatchlistOtherBySymbol(String symbol);

    @Select("select a.*,b.coingecko_id from coin_watchlist_other a left join coin_watchlist b on a.symbol=b.symbol where a.symbol = #{symbol} and a.delete_flag = 1")
    CoinWatchlistOther findWatchlistOtherBySymbol2(String symbol);

    @Insert({"<script>" +
            "INSERT INTO coin_watchlist_other(symbol,cex_spot,cex_perpetual,create_time,modify_time)" +
            " VALUES " +
            "<foreach collection='collection' item='cwo' index=\"index\" separator=\",\">" +
            "   (#{cwo.symbol},#{cwo.cexSpot},#{cwo.cexPerpetual},now(),now())" +
            " </foreach>" +
            " ON DUPLICATE KEY UPDATE cex_spot=values(cex_spot),cex_perpetual=values(cex_perpetual),modify_time=now()" +
            "</script>"})
    int insertOrUpdateCoinWatchlistOtherList(Collection<CoinWatchlistOther> coinWatchlistOtherList);

    @Update({"<script>" +
            "update coin_watchlist " +
            "<set>" +
            "<if test=\"score != null and score != ''\">" +
            "  score = #{score}," +
            "</if>" +
            "<if test=\"remark != null and remark != ''\">" +
            "   remark = #{remark}," +
            "</if>" +
            "modify_time=now()" +
            "</set>" +
            "where symbol = #{symbol}" +
            "</script>"})
    int updateCoinWatchlistRemark(CoinWatchlist coinWatchlist);

    @Select("select * from coin_watchlist where symbol = #{symbol} and delete_flag = 1")
    CoinWatchlist findWatchlistBySymbol(String symbol);

    @Insert({"<script>" +
            "INSERT INTO coin_cmc_map(cmc_id,cmc_rank,name,symbol,slug,is_active,status," +
            "first_historical_data,last_historical_data,platform,modify_time)" +
            " VALUES " +
            "<foreach collection='list' item='mt' index=\"index\" separator=\",\">" +
            "   (#{mt.cmcId},#{mt.cmcRank},#{mt.name},#{mt.symbol},#{mt.slug},#{mt.isActive},#{mt.status}," +
            "#{mt.firstHistoricalData},#{mt.lastHistoricalData},#{mt.platform},now())" +
            " </foreach>" +
            " ON DUPLICATE KEY UPDATE cmc_rank=values(cmc_rank),name=values(name),symbol=values(symbol),slug=values(slug)," +
            "is_active=values(is_active),status=values(status),first_historical_data=values(first_historical_data),last_historical_data=values(last_historical_data),modify_time=now()" +
            "</script>"})
    void insertCmcMapList(List<CoinCmcMap> cmcMapList);

    @Select({"<script>" +
            "select * from coin_cmc_map WHERE 1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and (name like concat('%',#{keyword},'%') or symbol like concat('%',#{keyword},'%') or slug like concat('%',#{keyword},'%'))" +
            "</if>" +
            "<if test=\"filterField != null and filterField != ''\">" +
            "   and status = #{filterField}" +
            "</if>" +
            " order by ${sortField} ${sort}" +
            "</script>"})
    List<CoinCmcMap> findCmcMapList(Map<String, Object> params);

    @Select("SELECT * FROM coin_monitor_job WHERE delete_flag = 1")
    @MapKey("job_name")
    Map<String, JSONObject> findMonitorJobConfig();

    @Insert("INSERT INTO coin_watchlist(cmc_id, symbol, track_category, track_category2, issuing_date, coingecko_id, coingecko_url, cmc_url, feixiaohao_url, filter_flag, create_time, modify_time) " +
            "VALUES (#{cmcId}, #{symbol}, #{trackCategory}, #{trackCategory2}, #{issuingDate}, #{coingeckoId}, #{coingeckoUrl}, #{cmcUrl}, #{feixiaohaoUrl}, #{filterFlag}, now(), now())" +
            " ON DUPLICATE KEY UPDATE cmc_id=values(cmc_id),track_category=values(track_category),track_category2=values(track_category2),issuing_date=values(issuing_date),coingecko_id=values(coingecko_id)" +
            ",coingecko_url=values(coingecko_url),cmc_url=values(cmc_url),feixiaohao_url=values(feixiaohao_url),filter_flag=values(filter_flag),modify_time=now()")
    int insertOrUpdateWatchlist(CoinWatchlist coinWatchlist);

    @Insert({"<script>" +
            "INSERT INTO coin_binance_symbol(symbol,status,baseAsset,marginAsset,baseAssetPrecision,contractType,underlyingSubType," +
            "filters,orderTypes,timeInForce,liquidationFee,marketTakeBound,deliveryDate,onboardDate,modify_time)" +
            " VALUES " +
            "<foreach collection='list' item='cbs' index=\"index\" separator=\",\">" +
            "   (#{cbs.symbol},#{cbs.status},#{cbs.baseAsset},#{cbs.marginAsset},#{cbs.baseAssetPrecision},#{cbs.contractType},#{cbs.underlyingSubType}," +
            "#{cbs.filters},#{cbs.orderTypes},#{cbs.timeInForce},#{cbs.liquidationFee},#{cbs.marketTakeBound},#{cbs.deliveryDate},#{cbs.onboardDate},now())" +
            " </foreach>" +
            " ON DUPLICATE KEY UPDATE status=values(status),baseAsset=values(baseAsset),marginAsset=values(marginAsset),baseAssetPrecision=values(baseAssetPrecision)," +
            "contractType=values(contractType),underlyingSubType=values(underlyingSubType),filters=values(filters),orderTypes=values(orderTypes)," +
            "timeInForce=values(timeInForce),liquidationFee=values(liquidationFee),marketTakeBound=values(marketTakeBound),deliveryDate=values(deliveryDate),onboardDate=values(onboardDate),modify_time=now()" +
            "</script>"})
    void insertCoinBinanceSymbolList(List<CoinBinanceSymbol> coinBinanceSymbolList);

    @Select({"<script>" +
            "select * from coin_binance_symbol WHERE 1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"status != null and status != ''\">" +
            "   and status = #{status}" +
            "</if>" +
            "<if test=\"marginAsset != null and marginAsset != ''\">" +
            "   and marginAsset = #{marginAsset}" +
            "</if>" +
            "<if test=\"contractType != null and contractType != ''\">" +
            "   and contractType = #{contractType}" +
            "</if>" +
            " order by ${sortField} ${sort}" +
            "</script>"})
    List<CoinBinanceSymbol> findCoinBinanceSymbolListByParams(Map<String, Object> params);

    @Select("select orderId from coin_binance_order_history")
    List<String> findAllCoinBinanceOrderIdList();

    @Insert({"<script>" +
            "INSERT ignore INTO coin_binance_order_history(orderId,symbol,status,price,avgPrice,origQty,executedQty,cumQuote,timeInForce,type" +
            ",side,positionSide,stopPrice,workingType,origType,time,updateTime,commission,realizedPnl,modify_time)" +
            " VALUES " +
            "<foreach collection='list' item='p' index=\"index\" separator=\",\">" +
            "   ( #{p.orderId}, #{p.symbol}, #{p.status}, #{p.price}, #{p.avgPrice}, #{p.origQty}, #{p.executedQty}, #{p.cumQuote}, #{p.timeInForce}, #{p.type}" +
            ", #{p.side}, #{p.positionSide}, #{p.stopPrice}, #{p.workingType}, #{p.origType}, #{p.time}, #{p.updateTime}, #{p.commission}, #{p.realizedPnl}, now())" +
            "</foreach>" +
            "</script>"})
    void insertOrUpdateBinanceOrderHistoryList(List<CoinBinanceOrderHistory> coinBinanceOrderHistoryList);

    @Select({"<script>" +
            "select cch.*,cec.category_name exchangeCategory from coin_currency_holding cch left join coin_exchange_category cec on cch.exchange_category_id=cec.id WHERE cch.delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and cch.symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"symbol != null and symbol != ''\">" +
            "   and cch.symbol = #{symbol}" +
            "</if>" +
            "<if test=\"status != null and status != ''\">" +
            "   and cch.status = #{status}" +
            "</if>" +
            "<if test=\"categoryField != null and categoryField != ''\">" +
            "   and cch.exchange_category_id = #{categoryField}" +
            "</if>" +
            " order by " +
            "<foreach collection='sortField' item='sf' index=\"index\" separator=\",\">" +
            "   ${sf} ${sort}" +
            " </foreach>" +
            "</script>"})
    List<CoinCurrencyHolding> findCurrentHoldingList(Map<String, Object> params);


    @Update("update coin_currency_holding set symbol=#{symbol},buy_price=#{buyPrice},current_price=#{currentPrice},change_percentage=#{changePercentage},buy_quantity=#{buyQuantity}," +
            "current_quantity=#{currentQuantity},buy_amount=#{buyAmount},current_amount=#{currentAmount},exchange_category_id=#{exchangeCategoryId},coingecko_id=#{coingeckoId},status=#{status}," +
            "remark=#{remark},buy_time=#{buyTime},modify_time=now() where id = #{id}")
    int updateCurrentHolding(CoinCurrencyHolding coinCurrencyHolding);

    @Select({"<script>" +
            "select sum(current_amount) from coin_currency_holding cch WHERE cch.delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and cch.symbol like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"symbol != null and symbol != ''\">" +
            "   and cch.symbol = #{symbol}" +
            "</if>" +
            "<if test=\"status != null and status != ''\">" +
            "   and cch.status = #{status}" +
            "</if>" +
            "<if test=\"categoryField != null and categoryField != ''\">" +
            "   and cch.exchange_category_id = #{categoryField}" +
            "</if>" +
            "</script>"})
    BigDecimal getCurrentHoldingTotalAmout(Map<String, Object> params);

    @Select({"<script>" +
            "select bi.*,bc.name categoryName from bookmark_info bi left join bookmark_category bc on bi.category_id=bc.id WHERE bi.delete_flag = 1 and bc.delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and (bi.title like concat('%',#{keyword},'%') or bi.remark like concat('%',#{keyword},'%') or bi.tags like concat('%',#{keyword},'%'))" +
            "</if>" +
            "<if test=\"categoryField != null and categoryField != ''\">" +
            "   and bi.category_id = #{categoryField}" +
            "</if>" +
            "<if test=\"accountId != null and accountId != ''\">" +
            "   and bi.account_id = #{accountId}" +
            "</if>" +
            "<if test=\"isFavorite != null and isFavorite != ''\">" +
            "   and bi.is_favorite = #{isFavorite}" +
            "</if>" +
            "<if test=\"status != null and status != ''\">" +
            "   and bi.status = #{status}" +
            "</if>" +
            " order by " +
            "<foreach collection='sortField' item='sf' index=\"index\" separator=\",\">" +
            "   ${sf} ${sort}" +
            " </foreach>" +
            "</script>"})
    List<BookmarkInfo> findBookmarkList(Map<String, Object> params);
}