ceshi/src/main/java/com/jeeplus/modules/sg/web/JkxlController.java

package com.jeeplus.modules.sg.web;

import com.alibaba.fastjson.JSONArray;
import com.jeeplus.common.utils.DateUtils;
import com.jeeplus.common.utils.StringUtils;
import com.jeeplus.common.utils.excel.ExportExcel;
import com.jeeplus.common.utils.excel.ImportExcel;
import com.jeeplus.core.web.BaseController;
import com.jeeplus.modules.sg.entity.*;
import com.jeeplus.modules.sg.service.JkxlService;
import com.jeeplus.modules.sg.util.BashInfo;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 架空线路
 */
@Controller
@RequestMapping(value = "${adminPath}/jkxl")
public class JkxlController extends BaseController {
  @Autowired
  private JkxlService jkxlService;

  @RequestMapping("/index")
  public String toImport(){
    return "modules/sg/free/indexProject";
  }

  /**
   *数据导入
   */
  @RequestMapping("/import")
  public String importFile(MultipartFile file, RedirectAttributes redirectAttributes, HttpServletRequest request, Model model){
    double sl = Double.valueOf(request.getParameter("jxsl"));
    String projectName = request.getParameter("name");
    String id = getId();
    try {
      double[] rate = getRate();
      WbsProject wbsProject = new WbsProject();
      wbsProject.setId(id);
      wbsProject.setName(projectName);
      wbsProject.setCreateDate(new Date());
      jkxlService.saveProject(wbsProject);
      List<JkxlData> list = getData(file,rate,id,sl);
      for(JkxlData jkxlData : list){
        jkxlService.save(jkxlData);
      }
      double[] tzfee = getTzFee(file);
      jkxlService.saveTzFee(tzfee,id);
    } catch (Exception e){
      addMessage(redirectAttributes, "导入失败");
    }

    addMessage(redirectAttributes, "导入成功");
    return "redirect:/a/jkxl/list?id="+id;
  }

  @RequestMapping("/gettz")
  @ResponseBody
  public List<JkxlData> getTz(TzData tzData){
    String id = tzData.getId();
    List<JkxlData> list = jkxlService.getList(id);
    List<JkxlData> list1 = new ArrayList<>();
    for(JkxlData jkxlData: list){
      if(jkxlData.getOriginalTotalCost() != null){
          list1.add(jkxlData);
      }
    }
    return list1;
  }

  /**
   *金额分摊调整
   */
  @RequestMapping("/fttz")
  public String reList(FtData ftData, Model model,RedirectAttributes redirectAttributes){
      String id = ftData.getId();
      String code = ftData.getCode();
      String state = ftData.getState();
      String type = ftData.getType();
      double fee = ftData.getFee();
      double total = 0.00;
      try {
        List<JkxlData> jkxl = jkxlService.getList(id);
        List<JkxlData> jkxlDatas = new ArrayList<>();
        for(JkxlData jkxlData: jkxl){
          if(jkxlData.getOriginalTotalCost() != null){
            jkxlDatas.add(jkxlData);
          }
        }
        if(code.equals("1")){
            for(JkxlData jkxlData: jkxlDatas) {
                total += jkxlData.getRgCost();
          }
            for(JkxlData jkxlData1: jkxlDatas){
              JkxlData jkxlData = new JkxlData();
              jkxlData.setId(id);
              jkxlData.setWbsCode(jkxlData1.getWbsCode());
              double rgFee = jkxlData1.getRgCost();
              double totalFee = jkxlData1.getTotalFee();
              double ftFee = rgFee/total*fee;
              jkxlData.setRgFtFee(ftFee);
              if(state.equals("1")){
                double zFee = totalFee+ftFee;
                jkxlData.setTotalFee(zFee);
              }else {
                double zFee = totalFee-ftFee;
                jkxlData.setTotalFee(zFee);
              }
              jkxlService.updataDate(jkxlData);
            }
        }
        if(code.equals("2")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getCbrCost();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getCbrCost();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            jkxlData.setCbrFtFee(ftFee);
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("3")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getFbrCost();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getFbrCost();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            jkxlData.setFbrFtFee(ftFee);
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("4")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getJxCost();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getJxCost();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            jkxlData.setJxFtFee(ftFee);
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("5")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getMeasuresFee1();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getMeasuresFee1();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("6")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getMeasuresFee2();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getMeasuresFee2();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("7")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getMeasuresFee3();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getMeasuresFee3();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("8")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getOtherCost();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getOtherCost();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("9")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getFees();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getFees();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        if(code.equals("10")){
          for(JkxlData jkxlData: jkxlDatas) {
            total += jkxlData.getTex();
          }
          for(JkxlData jkxlData1: jkxlDatas){
            JkxlData jkxlData = new JkxlData();
            jkxlData.setId(id);
            jkxlData.setWbsCode(jkxlData1.getWbsCode());
            double rgFee = jkxlData1.getTex();
            double totalFee = jkxlData1.getTotalFee();
            double ftFee = rgFee/total*fee;
            if(state.equals("1")){
              double zFee = totalFee+ftFee;
              jkxlData.setMeasuresFee1(rgFee+ftFee);
              jkxlData.setTotalFee(zFee);
            }else {
              double zFee = totalFee-ftFee;
              jkxlData.setMeasuresFee1(rgFee-ftFee);
              jkxlData.setTotalFee(zFee);
            }
            jkxlService.updataDate(jkxlData);
          }
        }
        TzData tzData = new TzData();
        tzData.setType(type);
        tzData.setId(id);
        jkxlService.updateFee(tzData);
      } catch (Exception e){
        addMessage(redirectAttributes, "调整失败");
      }
    addMessage(redirectAttributes, "调整成功");
    return "redirect:/a/jkxl/list?id="+id;
  }


  /**
   *金额单独调整
   */
  @RequestMapping("/ddtz")
  public String ddtz(TzData tzData, Model model, RedirectAttributes redirectAttributes){
    String id = tzData.getId();
    String type = tzData.getType();
    String state  = tzData.getState();
    double fee = tzData.getFee();
    String data1 = tzData.getData1();
    String row = data1.replaceAll("&quot;","\\\"");
    List<DdtzData> ts = (List<DdtzData>) JSONArray.parseArray(row, DdtzData.class);
    try {
      for(DdtzData ddtzData: ts){
        double tzFee = Double.valueOf(ddtzData.getFee());
        String wbsCode = ddtzData.getWbsCode();
        double otherFee = jkxlService.getDate(id,wbsCode).getOtherCost();
        double totalFee = jkxlService.getDate(id,wbsCode).getTotalFee();
        if(state.equals("1")){
          otherFee = otherFee+tzFee;
          totalFee = totalFee+tzFee;
        }
        if(state.equals("2")){
          otherFee = otherFee-tzFee;
          totalFee = totalFee-tzFee;
        }
        JkxlData jkxlData = new JkxlData();
        jkxlData.setId(id);
        jkxlData.setWbsCode(wbsCode);
        jkxlData.setOtherCost(otherFee);
        jkxlData.setTotalFee(totalFee);
        jkxlService.updataDate(jkxlData);
      }
      jkxlService.updateFee(tzData);

    }catch (Exception e){
      addMessage(redirectAttributes, "调整失败");
    }
    addMessage(redirectAttributes, "调整成功");
    return "redirect:/a/jkxl/list?id="+id;
  }




  @RequestMapping("/list")
  public String list(HttpServletRequest request,Model model){
    String id = request.getParameter("id");
    JkxlData jkxlData = new JkxlData();
    TzData tzData = new TzData();
    jkxlData.setId(id);
    tzData.setId(id);
    List<JkxlData> list1 = new ArrayList<>();
    list1.add(jkxlData);
    List<JkxlData> list = jkxlService.getList(id);
//    JkxlData jkxlData1 = getTotal(list);
//    list.add(jkxlData1);
    list.addAll(getTotal(list));
    List<TzData> list2 = jkxlService.getTzFee(tzData);
    HashMap map = new HashMap();
    map.put("list1",list1);
    map.put("list",list);
    map.put("list2",list2);
    model.addAttribute("map",map);
    return "modules/sg/free/wbslist";
  }




  /**
   *导出
   */
  @RequestMapping("/export")
  public String export(HttpServletRequest request, HttpServletResponse response,Model model,RedirectAttributes redirectAttributes) {
    String id = request.getParameter("id");
    try{
      String fileName = "架空线路"+ DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
      List<JkxlData> list = jkxlService.findList(id);
      new ExportExcel(null,JkxlData.class).setDataList(list).write(response, fileName).dispose();
      return null;
    }catch (Exception e){
      addMessage(redirectAttributes, "导出失败");
    }
     addMessage(redirectAttributes, "导出成功");
    return "redirect:/a/jkxl/list?id="+id;
  }


  public List<JkxlData> getData(MultipartFile file,double[] rate,String id,double sl) throws IOException, InvalidFormatException {
    ImportExcel importExcel = new ImportExcel(file,1,BashInfo.SHEET_FBFXJJ);  //分部分项工程量清单计价表
    ImportExcel csqd = new ImportExcel(file,1,BashInfo.SHEET_CSB);  // 措施项目清单计价表（一）
    ImportExcel gf = new ImportExcel(file,1,BashInfo.SHEET_GFQD);  //规费项目清单计价表
    ImportExcel qt = new ImportExcel(file,1,BashInfo.SHEET_QTFY);  //其他项目清单计价表
    ImportExcel total = new ImportExcel(file,1,BashInfo.SHEET_HZB );  //工程项目竣工结算汇总表
    ImportExcel gzhz = new ImportExcel(file,1,BashInfo.SHEET_GCHZ );   //架空线路工程费用汇总表
    double[] csRate = getCsRate(csqd);
    double[] gfRate = getGfRate(gf);
    double[] qtFees = getQtFees(qt);
    double[] wsbFees = getFees(total);
    int[] zjgcFee = getZjgcFee(gzhz);
    double texRate = getTexRate(total);
    double totalFee = 0.00;
    List<JkxlData> list = new ArrayList<>();
    int lastRow = importExcel.getLastDataRowNum();
    int[] row = new int[6];
    for(int i=0;i<lastRow;i++){
      String type =  importExcel.getValue(i+1,2);
      if(type.equals(BashInfo.PROJECTNAME)){
        totalFee = importExcel.getDouble(i+1,12);
        continue;
      }
      if(type.equals(BashInfo.JCGC)){
        row[0] = i+1;
        continue;
      }
      if(type.equals(BashInfo.GTGC)){
        row[1] = i+1;
        continue;
      }
      if(type.equals(BashInfo.JDGC)){
        row[2] = i+1;
        continue;
      }
      if(type.equals(BashInfo.JXGC)){
        row[3] = i+1;
        continue;
      }
      if(type.equals(BashInfo.FJAZGC)){
        row[4] = i+1;
        continue;
      }
      if(type.equals(BashInfo.FZGC)){
        row[5] = i+1;
        break;
      }
    }
    for(int i=0;i<6;i++){
      JkxlData jkxlData = new JkxlData();
      double originalTotalCost = gzhz.getDouble(zjgcFee[i],2); //原合计
      double otherRate = originalTotalCost/totalFee;   //其他费用分摊比例
      double orRate = new BigDecimal(otherRate).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue(); //四舍五入
      double btc = wsbFees[0]*orRate;       //补调差额
      double jcl = wsbFees[1]*orRate;        //减材料费
      double otherFee = qtFees[2]*otherRate;  //其他费用
      double zjgcFees = gzhz.getDouble(zjgcFee[i],3);//直接工程费
      double originalRgCost = importExcel.getDouble(row[i],13); //原人工费
      double rgCost = originalRgCost/(1+rate[0]);  //后人工费
      double originalCbrCost = importExcel.getDouble(row[i],14); //原承包人采购
      double cbrCost = originalCbrCost/(1+rate[1]);  //后承包人采购
      double originalFbrCost = importExcel.getDouble(row[i],15); //原发包人采购
      double fbrCost = originalFbrCost/(1+sl); //后发包人采购
      double originalJxrCost = importExcel.getDouble(row[i],17); //原机械费
      double jxCost = originalJxrCost/(1+rate[2]); //后机械费
      double measuresFee1 = (zjgcFees-fbrCost*sl)*csRate[0]; //措施费1
//      double measuresFee1 = (rgCost+cbrCost+fbrCost+jxCost)*csRate[0]; //措施费1
      double measuresFee2 = rgCost*csRate[1];   //措施费2
      double fees = rgCost*gfRate[1]*1.12+rgCost*gfRate[0];  //规费
      double tax = (originalTotalCost+measuresFee1+measuresFee2+fees+otherFee-originalFbrCost)*texRate; //税金
      double totalfee = originalTotalCost+measuresFee1+measuresFee2+fees+otherFee+tax-originalFbrCost; //合计
      jkxlData.setOriginalTotalCost(originalTotalCost);
      jkxlData.setOriginalRate(orRate);
      jkxlData.setOriginalRgCost(originalRgCost);
      jkxlData.setRgCost(rgCost);
      jkxlData.setOriginalCbrCost(originalCbrCost);
      jkxlData.setCbrCost(cbrCost);
      jkxlData.setOriginalFbrCost(originalFbrCost);
      jkxlData.setFbrCost(fbrCost);
      jkxlData.setOriginalJxCost(originalJxrCost);
      jkxlData.setJxCost(jxCost);
      jkxlData.setMeasuresFee1(measuresFee1);
      jkxlData.setMeasuresFee2(measuresFee2);
      jkxlData.setFees(fees);
      jkxlData.setOtherCost(otherFee);
      jkxlData.setTex(tax);
      jkxlData.setTotalFee(totalfee);
      jkxlData.setBzFee(btc);
      jkxlData.setJkFee(jcl);
      jkxlData.setZjgcFee(zjgcFees);
      jkxlData.setWbsCode(BashInfo.ids[i]);
      jkxlData.setId(id);
      list.add(jkxlData);
    }
    double tz = 0.00;
    for(JkxlData jkxlData : list){
      tz += jkxlData.getTotalFee();
    }
    if(wsbFees[2]>tz){
      double gxFee = wsbFees[2]-tz-qtFees[0]-qtFees[1]-wsbFees[0]-wsbFees[1];
      double rate1 = 0.00;
      double gxFee1 = 0.00;
      double totalF = 0.00;
      for(JkxlData jkxlData: list){
        rate1 = jkxlData.getTotalFee()/tz;
        gxFee1 = gxFee*rate1;
        totalF = gxFee1 + jkxlData.getTotalFee();
        jkxlData.setGxFee(Math.abs(gxFee1));
        jkxlData.setTotalFee(totalF);
      }
    }else {
      double gxFee = tz+qtFees[0]+qtFees[1]+wsbFees[0]+wsbFees[1]-wsbFees[2];
      double rate1 = 0.00;
      double gxFee1 = 0.00;
      double totalF = 0.00;
      for(JkxlData jkxlData: list){
        rate1 = jkxlData.getTotalFee()/tz;
        gxFee1 = gxFee*rate1;
        totalF = jkxlData.getTotalFee()-gxFee1;
        jkxlData.setGxFee(Math.abs(gxFee1));
        jkxlData.setTotalFee(totalF);
      }

    }
    JkxlData jkxlData1 = new JkxlData();
    JkxlData jkxlData2 = new JkxlData();
    jkxlData1.setId(id);
    jkxlData1.setWbsCode("34120000");
    jkxlData1.setTotalFee(qtFees[0]);
    jkxlData2.setWbsCode("34220000");
    jkxlData2.setTotalFee(qtFees[1]);
    jkxlData2.setId(id);
    list.add(jkxlData1);
    list.add(jkxlData2);

    return list;

  }




  /**
   *获取措施费率
   */
  public double[] getCsRate(ImportExcel importExcel){
    int lastRow = importExcel.getLastDataRowNum();
    double lsRate = 0.00;
    double aqRate = 0.00;
    double totalRate = 0.00;
    for(int i=2;i<lastRow;i++){
      String type = importExcel.getValue(i+1,1);
      if(type.equals(BashInfo.CS_LS)){
          lsRate = importExcel.getDouble(i+1,3);
      }
      if(type.equals(BashInfo.CS_AQWM)){
        aqRate = importExcel.getDouble(i+1,3);
      }
      totalRate += importExcel.getDouble(i+1,3);
    }
    double[] rate = new double[2];
    rate[0] = (lsRate+aqRate)/100;
    rate[1] = (totalRate-lsRate-aqRate)/100;
    return  rate;
  }

  /**
   * 读取税率
   */
  public double getTexRate(ImportExcel importExcel){
    double rate = 0.00;
    int lastRow = importExcel.getLastDataRowNum();
    for(int i=2;i<lastRow;i++){
      String st = importExcel.getValue(i+1,1);
      if(st.contains("税金")){
        int a = st.indexOf(":");
        int a1 = st.indexOf("%");
        String st1 = st.substring(a+2,a1);
        System.out.println(st1);
        rate = Double.valueOf(st1)/100;
        break;
      }
    }
    return rate;
   }

  /**
   *获取规费率
   */
  public double[] getGfRate(ImportExcel importExcel) {
    int lastRow = importExcel.getLastDataRowNum();
    double totalRate = 0.00;
    double wxzyRate = 0.00;
    for (int i = 2; i < lastRow; i++) {
      String type = importExcel.getValue(i + 1, 1);
      if (type.equals(BashInfo.GF_WXZY)) {
        wxzyRate = importExcel.getDouble(i + 1, 3);
      }
      if (!importExcel.getValue(i + 1, 2).equals("")) {
        totalRate += importExcel.getDouble(i + 1, 3);
      }
    }
    double[] rate = new double[2];
    rate[0] = wxzyRate / 100;
    rate[1] = (totalRate - wxzyRate) / 100;
    return rate;
  }

  /**
   * 获取其他费用
   */
  public double[] getQtFees(ImportExcel importExcel) {
    int lastRow = importExcel.getLastDataRowNum();
    double cdFree = 0.00;
    double zbFree = 0.00;
    double totalFree = 0.00;
    for(int i=2;i<lastRow;i++){
      String type = importExcel.getValue(i+1,1);
      String type1 = importExcel.getValue(i+1,0);
      if(type.equals(BashInfo.QT_SGCDZY)){
        cdFree = importExcel.getDouble(i+1,2);
      }
      if(type.equals(BashInfo.QT_ZBDL)){
        zbFree = importExcel.getDouble(i+1,2);
      }
      if(type1.equals(BashInfo.QT_HJ)){
        totalFree = importExcel.getDouble(i+1,2);
      }
    }
    double[] rate = new double[3];
    rate[0] = cdFree;
    rate[1] = zbFree;
    rate[2] = totalFree-cdFree-zbFree;
    return  rate;
  }

  /**
   * 获取调差系数
   * @return
   */
  public double[] getRate(){
    double[] rate = new double[3];
    rate[0] = jkxlService.getValueByName(BashInfo.RATIO_RGTC).getValue()/100;
    rate[1] = jkxlService.getValueByName(BashInfo.RATIO_CLTC).getValue()/100;
    rate[2] = jkxlService.getValueByName(BashInfo.RATIO_JXTC).getValue()/100;
    return rate;
  }

  /**
   * 获取补增值税税率调整差额和扣减甲供材超供材料费
   */
  public double[] getFees(ImportExcel importExcel){
    int lastRow = importExcel.getLastDataRowNum();
    double bzFree = 0.00;
    double kcFree = 0.00;
    double gcjs = 0.00;
    for(int i=0;i<lastRow;i++){
      String type = importExcel.getValue(i+1,1);
      if(type.equals(BashInfo.BZZSSL)){
        bzFree = importExcel.getDouble(i+1,2);
        continue;
      }
      if(type.equals(BashInfo.KJJGCC)){
        kcFree = importExcel.getDouble(i+1,2);
        continue;
      }
      if(type.equals(BashInfo.TBJG) || type.equals(BashInfo.GCJS)){
        gcjs = importExcel.getDouble(i+1,2);
      }
    }
    double[] wsb = new double[3];
    wsb[0] = bzFree;
    wsb[1] = kcFree;
    wsb[2] = gcjs;
    return  wsb;

  }

  /**
   *获取直接工程费
   */
  public int[] getZjgcFee(ImportExcel importExcel) {
    int lastRow = importExcel.getLastDataRowNum();
    int[] row = new int[6];
    for(int i=0;i<lastRow;i++){
      String type =  importExcel.getValue(i+1,1);
      if(type.equals(BashInfo.HZ_JCGC)){
        row[0] = i+1;
        continue;
      }
      if(type.equals(BashInfo.HZ_GTGC)){
        row[1] = i+1;
        continue;
      }
      if(type.equals(BashInfo.HZ_JDGC)){
        row[2] = i+1;
        continue;
      }
      if(type.equals(BashInfo.HZ_JXGC)){
        row[3] = i+1;
        continue;
      }
      if(type.equals(BashInfo.HZ_FJAZGC)){
        row[4] = i+1;
        continue;
      }
      if(type.equals(BashInfo.HZ_FZ)){
        row[5] = i+1;
        break;
      }
    }
    return  row;

  }



  public double[] getTzFee(MultipartFile file) throws IOException, InvalidFormatException {
    ImportExcel total = new ImportExcel(file,1,BashInfo.SHEET_HZB );
    double[] wsb = getFees(total);
    return wsb;
  }

    /**
     * 创建项目ID
     */
  public String getId() {
     SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
     String newDate=sdf.format(new Date());
     String result="";
      Random random=new Random();
     for(int i=0;i<3;i++){
       result+=random.nextInt(10);
     }
     return newDate+result;
 }

  private List<JkxlData> getTotal(List<JkxlData> list) {
    List<JkxlData> list1 = new ArrayList<>();
    List<JkxlData> list2  = new ArrayList<>();
    for(JkxlData jkxlData: list){
      if(jkxlData.getOriginalTotalCost() != null){
        list1.add(jkxlData);
      }
    }
    for(JkxlData jkxlData: list){
      if(jkxlData.getOriginalTotalCost() == null && jkxlData.getTotalFee() != null){
        list2.add(jkxlData);
      }
    }
    double total1 = 0.00;
    for (JkxlData jkxlData :list2){
      total1 += jkxlData.getTotalFee();
    }
    double [] zFee = new double[22];
    for(JkxlData jkxlData:list1){
      zFee[0] += jkxlData.getOriginalTotalCost();
      zFee[1] += jkxlData.getZjgcFee();
      zFee[2] += jkxlData.getOriginalRgCost();
      zFee[3] += jkxlData.getOriginalCbrCost();
      zFee[4] += jkxlData.getOriginalFbrCost();
      zFee[5] += jkxlData.getOriginalJxCost();
      zFee[6] += jkxlData.getRgCost();
      zFee[7] += jkxlData.getCbrCost();
      zFee[8] += jkxlData.getFbrCost();
      zFee[9] += jkxlData.getJxCost();
      zFee[10] += jkxlData.getRgFtFee()!=null?jkxlData.getRgFtFee():0.00;
      zFee[11] += jkxlData.getCbrFtFee()!=null?jkxlData.getCbrFtFee():0.00;
      zFee[12] += jkxlData.getFbrFtFee()!=null?jkxlData.getFbrFtFee():0.00;
      zFee[13] += jkxlData.getJxFtFee()!=null?jkxlData.getJxFtFee():0.00;
      zFee[14] += jkxlData.getMeasuresFee1();
      zFee[15] += jkxlData.getMeasuresFee2();
      zFee[16] += jkxlData.getMeasuresFee3()!=null?jkxlData.getMeasuresFee3():0.00;
      zFee[17] += jkxlData.getOtherCost();
      zFee[18] += jkxlData.getFees();
      zFee[19] += jkxlData.getTex();
      zFee[20] += jkxlData.getGxFee();
      zFee[21] += jkxlData.getTotalFee();
    }
    JkxlData jkxlData = new JkxlData();
    JkxlData jkxlData1 = new JkxlData();
    jkxlData.setOriginalTotalCost(new BigDecimal(zFee[0]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setZjgcFee(new BigDecimal(zFee[1]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setOriginalRgCost(new BigDecimal(zFee[2]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setOriginalCbrCost(new BigDecimal(zFee[3]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setOriginalFbrCost(new BigDecimal(zFee[4]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setOriginalJxCost(new BigDecimal(zFee[5]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setRgCost(new BigDecimal(zFee[6]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setCbrCost(new BigDecimal(zFee[7]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setFbrCost(new BigDecimal(zFee[8]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setJxCost(new BigDecimal(zFee[9]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setRgFtFee(new BigDecimal(zFee[10]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setCbrFtFee(new BigDecimal(zFee[11]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setFbrFtFee(new BigDecimal(zFee[12]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setJxFtFee(new BigDecimal(zFee[13]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setMeasuresFee1(new BigDecimal(zFee[14]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setMeasuresFee2(new BigDecimal(zFee[15]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setMeasuresFee3(new BigDecimal(zFee[16]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setOtherCost(new BigDecimal(zFee[17]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setFees(new BigDecimal(zFee[18]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setTex(new BigDecimal(zFee[19]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setGxFee(new BigDecimal(zFee[20]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setTotalFee(new BigDecimal(zFee[21]).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
    jkxlData.setWbsCode("合计");
    jkxlData.setParentNode("30000000");
    jkxlData.setState("1");
    jkxlData1.setWbsCode("合计");
    jkxlData1.setParentNode("34000000");
    jkxlData1.setState("0");
    jkxlData1.setTotalFee(total1);
    List<JkxlData> list3 = new ArrayList<>();
    list3.add(jkxlData);
    list3.add(jkxlData1);
     return list3;





  }







}