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

package com.jeeplus.modules.sg.web;

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.JkxlData;
import com.jeeplus.modules.sg.entity.TzData;
import com.jeeplus.modules.sg.entity.WbsProject;
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.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("/relist")
//  public String reList(){
//
//  }




  @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);
    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 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 = importExcel.getDouble(row[i],12); //原合计
      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)/(1+sl)*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)*0.09; //税金
      double totalfee = rgCost+cbrCost+fbrCost+jxCost+measuresFee1+measuresFee2+fees+otherFee+tax-fbrCost; //合计
      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);
    }
    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[] 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;
    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);
        break;
      }
    }
    double[] wsb = new double[2];
    wsb[0] = bzFree;
    wsb[1] = kcFree;
    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;
 }







}