java-insagent-pc/src/main/java/com/ydtech/modules/ins/dao/impl/InsCommissionDaoImpl.java

package com.ydtech.modules.ins.dao.impl;

import com.github.drinkjava2.jdialects.Dialect;
import com.ydtech.core.page.PageResult;
import com.ydtech.modules.ins.dao.InsCommissionDao;
import com.ydtech.modules.ins.dao.InsCommissionDao;
import com.ydtech.modules.ins.model.InsCommission;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


@Repository
public class InsCommissionDaoImpl implements InsCommissionDao {

    @Autowired
    public DataSource dataSource;


    public static class InsCommissionRowMapper implements RowMapper {
        public InsCommission mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            InsCommission model = new InsCommission();

            model.setOrderno(resultSet.getString("orderno"));
            model.setPolicyno(resultSet.getString("policyno"));
            model.setLicenseno(resultSet.getString("licenseno"));
            model.setRiskcode(resultSet.getString("riskcode"));
            model.setDeptid(resultSet.getString("deptid"));
            model.setDeptname(resultSet.getString("deptname"));
            model.setInscompany(resultSet.getString("inscompany"));
            model.setAgreeid(resultSet.getString("agreeid"));

            model.setQuoteno(resultSet.getString("quoteno"));
            model.setRefereescode(resultSet.getString("refereescode"));
            model.setRefereesname(resultSet.getString("refereesname"));
            model.setCommissionrate(resultSet.getDouble("commissionrate"));
            model.setCommissionamount(resultSet.getDouble("commissionamount"));
            model.setLevel(resultSet.getString("level"));
            model.setAuditstatus(resultSet.getString("auditstatus"));
            model.setSettlestatus(resultSet.getString("settlestatus"));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (resultSet.getTimestamp("insuredtime") != null) {
                Date insuredtime = new Date(resultSet.getTimestamp("insuredtime").getTime());
                model.setInsuredtime(sdf.format(insuredtime));
            } else {
                model.setInsuredtime(null);
            }
            if (resultSet.getTimestamp("signdate") != null) {
                Date signdate = new Date(resultSet.getTimestamp("signdate").getTime());
                model.setSignDate(sdf.format(signdate));
            } else {
                model.setSignDate(null);
            }
            return model;
        }
    }

    @Override
    public InsCommission selectByPk(String orderno) {
        String sql = " select t.orderno,t.quoteno,t.refereescode,t.refereesname,t.commissionrate,t.commissionamount,t.level,t.auditstatus,t.settlestatus,t.insuredtime from ins_commission t  where orderno=? ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        List<InsCommission> ls = jdbcTemplate.query(sql, new Object[]{orderno}, new InsCommissionRowMapper());

        if (ls != null && ls.size() > 0) {
            return ls.get(0);
        }

        return null;
    }

    @Override
    public List<InsCommission> selectAll() {
        String sql = "select t.orderno,t.quoteno,t.refereescode,t.refereesname,t.commissionrate,t.commissionamount,t.level,t.auditstatus,t.settlestatus,t.insuredtime from ins_commission t ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.query(sql, new InsCommissionRowMapper());
    }


    public List<InsCommission> selectList(String whereCase, Object[] paramValues) {
        String sql = "select t.orderno,t.quoteno,t.refereescode,t.refereesname,t.commissionrate,t.commissionamount,t.level,t.auditstatus,t.settlestatus,t.insuredtime from ins_commission t  " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.query(sql, paramValues, new InsCommissionRowMapper());
    }


    public Integer count(String whereCase, Object[] paramValues) {
        String sql = "select count(1) from ins_commission t " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.queryForObject(sql, paramValues, Integer.class);
    }

    public double sumAmount(String whereCase, Object[] paramValues) {
        String sql = "select IFNULL(sum(t.commissionamount),0) totalamount from ins_commission t " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.queryForObject(sql, paramValues, Double.class);
    }
    public double sumCommRate(String whereCase, Object[] paramValues) {
        String sql = "select IFNULL(sum(t.commissionrate),0) totalamount from ins_commission t " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.queryForObject(sql, paramValues, Double.class);
    }
    public PageResult selectPaging(int pageNumber, int pageSize, String whereCase, Object[] paramValues) {

        Dialect dialect = Dialect.guessDialect(dataSource);
        String sql = dialect.pagin(pageNumber, pageSize, "select t.orderno,t.policyno,t.deptid,t.deptname,t.riskcode,t.licenseno,(SELECT name from esm_ins_company where  id =t.inscompany) inscompany,t.agreeid,t.quoteno,t.refereescode,t.refereesname,t.commissionrate,t.commissionamount,t.level,t.auditstatus,t.settlestatus,t.insuredtime,t.signdate " +
                "from ins_commission t  " + whereCase);

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<InsCommission> recordList = jdbcTemplate.query(sql, paramValues, new InsCommissionRowMapper());

        int totalCount = count(whereCase, paramValues);

        PageResult pageResult = new PageResult(pageNumber, pageSize, totalCount, recordList);

        return pageResult;
    }


    @Override
    public Integer insert(InsCommission model) {

        String sql = "insert into ins_commission (orderno,policyno,licenseno,riskcode,deptid,deptname,inscompany,agreeid,quoteno,refereescode,refereesname,commissionrate,commissionamount,level,auditstatus,settlestatus,insuredtime) " +
                "values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getOrderno(),model.getPolicyno(),model.getLicenseno(),model.getRiskcode(),model.getDeptid(),model.getDeptname(),model.getInscompany(),model.getAgreeid(), model.getQuoteno(), model.getRefereescode(), model.getRefereesname(), model.getCommissionrate(), model.getCommissionamount(), model.getLevel(), model.getAuditstatus(), model.getSettlestatus(), model.getInsuredtime()};

        int out = jdbcTemplate.update(sql, params);

        return out;
    }

    @Override
    public Integer update(InsCommission model) {

        String sql = "update ins_commission set quoteno=?,refereescode=?,refereesname=?,commissionrate=?,commissionamount=?,level=?,auditstatus=?,settlestatus=?,insuredtime=? where orderno=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getQuoteno(), model.getRefereescode(), model.getRefereesname(), model.getCommissionrate(), model.getCommissionamount(), model.getLevel(), model.getAuditstatus(), model.getSettlestatus(), model.getInsuredtime(), model.getOrderno()};

        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Integer updateStatus(String orderno, String status) {

        String sql = "update ins_commission set auditstatus=? where orderno=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{status, orderno};

        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Integer updateSettleStatus(String orderno, String status) {

        String sql = "update ins_commission set settlestatus=? where orderno=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{status, orderno};

        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Integer delete(String orderno) {
        String sql = "delete from ins_commission where orderno=? ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.update(sql, orderno);
    }
}