package com.liangjian.dataplatform.member.service.impl;

import com.liangjian.dataplatform.member.config.ClickhouseUtils;
import com.liangjian.dataplatform.member.dao.fourthly.MemDataDictoryRepository;
import com.liangjian.dataplatform.member.service.IMemberDrugUsageService;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 黄树文
 * @date 2020/4/18 15:58
 */
@Service
public class MemberDrugUsageService implements IMemberDrugUsageService {


    @Resource(name = "clickhouseDruidDataSource")
    private DataSource clickhouseDruidDataSource;


    @Autowired
    private MemDataDictoryRepository memDataDictoryRepository;


    /**
     * 根据用户加密手机号查询用户用药
     * @param secretPhone
     * @param start
     * @param end
     * @param orderCode
     * @return 用户用药
     * @throws SQLException
     */
    @Override
    public List<JSONObject> findDrugUsageBySecretPhone(String secretPhone, String start, String end, String orderCode) throws SQLException {

        Connection connection = clickhouseDruidDataSource.getConnection();

        StringBuilder sql = new StringBuilder("SELECT AConsigneePhone2, CreationDate, ProductCode,ProductName, Packing, ActualPrice, Amount, ClassCode,ProductTag from (SELECT AConsigneePhone2, CreationDate, OrdersCode, OriginType, Sum, Discountprice, ProductCode,ProductName, Packing, ActualPrice, Amount, ClassCode,ProductTag, OrderStatus, IsReturn, OrdersCode from OrdersAndOrderProductsJoinProducts  where AConsigneePhone2 = '" + secretPhone + "') where OrderStatus in(50,70,150) and IsReturn=0 AND OrdersCode not like '%BF' order by CreationDate");

        if (orderCode != null && !"".equals(orderCode)) {
            sql.append(" and OrdersCode = '").append(orderCode).append("'");
        }

        if (start != null && !"".equals(start)) {
            sql.append(" and toString(CreationDate) > '").append(start).append("'");
        }

        if (end != null && !"".equals(end)) {
            sql.append(" and toString(CreationDate) < '").append(end).append("'");
        }

        PreparedStatement preparedStatement = connection.prepareStatement(new String(sql));

        ResultSet resultSet = preparedStatement.executeQuery();

        List<JSONObject> orders = ClickhouseUtils.resultSetToJson(resultSet);

        resultSet.close();
        preparedStatement.close();
        connection.close();
        return orders;
    }

    /**
     * 根据用户手机号查询用户用药
     * @param phone
     * @param start
     * @param end
     * @param orderCode
     * @return 用户用药
     * @throws SQLException
     */
    @Override
    public List<JSONObject> findDrugUsageByPhone(String phone, String start, String end, String orderCode) throws SQLException {

        // 对手机号进行加密
        String secretPhone = memDataDictoryRepository.desEncryptFixKey(phone).get("phone").toString();

        return findDrugUsageBySecretPhone(secretPhone, start, end, orderCode);
    }
}
