ssh (spring springmvc Hibernate ) 几张表联动效果 + layer

controller
package auto.system.third.controller;

import auto.system.common.entity.PageData;
import auto.system.module.controller.BaseController;
import auto.system.module.system.model.SysOperLog;
import auto.system.third.model._Plan;
import auto.system.third.server.JobDataService;
import auto.system.utils.ActionResult;
import com.sun.org.apache.bcel.internal.generic.RETURN;
import org.apache.catalina.connector.Request;
import org.apache.catalina.connector.Response;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@Controller
@RequestMapping(value = "/JobData")
public class JobDataController extends BaseController {

    @Resource
    private JobDataService jobDataService;

    //主页
    @RequestMapping(value = "/index.do")
    public String index(HttpServletRequest request) {
        return "/jsp/third/jobdata/index.jsp";
    }


    @RequestMapping(value = "/getList")
    @ResponseBody
    private PageData getList(HttpServletRequest request, Integer limit, Integer page) throws Exception {
        return jobDataService.getList(request, limit, page);
    }

    //详情列表
    @RequestMapping(value = "/getDetailList")
    @ResponseBody
    public PageData getDetailList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        return jobDataService.getDetailList(request, response);
    }

}

service
package auto.system.third.server;

import auto.system.common.entity.PageData;
import auto.system.module.server.BaseService;
import auto.system.module.server.dao.SystemBaseDAO;
import auto.system.module.system.model._JobBooking;
import auto.system.module.system.model._Module;
import auto.system.second.model._Protype;
import auto.system.spc.model.SPCData;
import auto.system.third.model.*;
import auto.system.utils.ActionResult;
import com.mchange.v2.c3p0.cfg.C3P0ConfigUtils;
import com.sun.star.lib.util.AsynchronousFinalizer;
import db.utils.DBUtils;
import org.apache.tools.ant.types.resources.selectors.Size;
import org.apache.xmlbeans.StringEnumAbstractBase;
import org.hsqldb.Table;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class
JobDataService extends BaseService {

    @Resource
    public PlanService planService;
    @Resource
    public SystemBaseDAO systemBaseDAO;


    @Transactional(propagation = Propagation.REQUIRED, readOnly = true)
    public PageData getDetailList(HttpServletRequest request, HttpServletResponse response) throws Exception {
//        List<GXgs> result = new ArrayList<GXgs>();
        List<JobDataDetail> result = new ArrayList<JobDataDetail>();
        String _dingdan = res.getParameter(request, "_dingdan", "");
        DecimalFormat df = new DecimalFormat("#.00");
        /**
         * 报工：
         * 1.由工单  ->  订单所有工位
         * 2.所有批次
         * 3.计算工时
         */
        _Plan plan = (_Plan) systemBaseDAO.findObjectByHql("from _Plan where _dingdan = '"+ _dingdan +"'");
        if(plan != null){
            //1.
            List<_PlanFlowProcess> listFlow = (List<_PlanFlowProcess>) systemBaseDAO.findAll("from _PlanFlowProcess where _dingdan = '"+ _dingdan +"' order by _orderId");
            //2.
            List<_PlanLot> listLot = (List<_PlanLot>) systemBaseDAO.findAll("from _PlanLot where _plan_id = '"+ plan.get_id() +"' order by _lot");
            /**
             * 3.计算工时
             * 工作中心实际工时：
             * 一个 工作中心对应的 一个工序
             */
            if(listFlow != null && listFlow.size() > 0){
                _PlanFlowProcess planFlowProcess = null;
                _PlanLot lot = null;
                JobDataDetail detail = null;
                GXgs gs = null;
                for(int x = 0,x_len = listFlow.size(); x < x_len; x++){
                    detail = new JobDataDetail();
                    planFlowProcess = listFlow.get(x);//第1、2、3...n的工序
                    _JobBooking jobBooking = (_JobBooking) systemBaseDAO.findObjectByHql("from _JobBooking where _moduleId = '"+ planFlowProcess.get_moduleId() +"'");
                    detail.setModuleName(planFlowProcess.get_name());
                    if(jobBooking != null){
                        detail.setWorkCenter(jobBooking.get_workCenter());
                    }
                    StringBuffer lotsql = new StringBuffer();
                    lotsql.append("select _tr,_cc,_zyrs,_xxgs,_ycgs,_stime,_etime from "+planFlowProcess.get_code()+" where (");
                    for(int y = 0,y_len = listLot.size(); y < y_len; y++){
                        lot = listLot.get(y);
                        lotsql.append(y == 0 ? (" _lot_id = '"+ lot.get_id() +"'") :  (" or _lot_id = '"+ lot.get_id() +"'"));
                    }
                    lotsql.append(") and _inputflag = 'Y'");
                    List<GXgs> list = (List<GXgs>)DBUtils.getListBy(lotsql.toString(),GXgs.class);
                    if(list != null && list.size() > 0){
                        long sjgs = 0;
                        int tr = 0;
                        for(int i = 0,size = list.size();i < size;i++){
                            gs = list.get(i);
                            tr += gs.get_tr();
                            sjgs += gs.minusSJGS();
                        }
                        detail.setBase(tr);
                        detail.set_sjgs(sjgs);
                        detail.set_bzgs((planFlowProcess.get_bzgs() != null ? (planFlowProcess.get_bzgs() * tr / 60) : 0));
                        detail.setLv(detail.get_bzgs() != null ? df.format(detail.get_bzgs() / detail.get_sjgs() * 100)+"%" : "");
                    }
                    result.add(detail);
                }
            }
        }
        return PageData.successData(result, 0);
    }

    public static void main(String[] args) {
        StringBuffer s = null;
        for(int x = 0; x < 2;x++){
            s = new StringBuffer();
            for(int y = 0; y < 5;y++){
                s.append("1");
            }
            System.out.println(s);
        }
    }


    //报工数据管理
    @Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    public PageData getList(HttpServletRequest request, Integer limit, Integer page) throws Exception {
        String dingdan = res.getParameter(request, "_dingdan", "");
        StringBuilder totalSql = new StringBuilder();
        StringBuilder where = new StringBuilder();
        StringBuilder sql = new StringBuilder();
        sql.append("select top "+ limit +" * from (select row_number() over(order by _inputdate desc) as rownumber,* " +
                "from (select distinct p._id,pro._name,p._dingdan,pro._number,p._inputdate " +
                "from _Plan p,_Plan_Bom b,_Protype pro ,_PlanLot l ");
        totalSql.append("select count(distinct p._id) from _Plan p,_Plan_Bom b,_Protype pro ,_PlanLot l ");
        where.append( "where b._id = p._bom_id and pro._name = b._protype and  p._id = l._plan_id and p._gyflag = 'Y' and _qcflag = 'Y'  and _overflag = 'N'");
        if(!dingdan.equals("")){
            where.append(" and p._dingdan = '"+ dingdan +"'");
        }
        totalSql.append(where);
        sql.append(where);
        sql.append(") a) temp_row where rownumber > (("+ page +"-1)*"+ limit +");");
        List<JobData> list = (List<JobData>) DBUtils.getListBy(sql.toString(), JobData.class);
        return PageData.successData(list, DBUtils.getTotal(totalSql.toString()));
    }
    
}

jsp

<%@page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8" %>
<%@ include file="/jsp/common.jsp" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <title></title>
    <link rel="stylesheet" href="<%=basePath %>js/layui-v2.5.5/layui/css/layui.css?v=20200101">
</head>
<body>

<!-- 工具栏模板： -->
<script type="text/html" id="job-tbl-toolbar">
    <div class="layui-btn-container layui-fluid">
        <div class="layui-inline">
            <label class="layui-form-label">订单号</label>
            <div class="layui-input-inline">
                <input type="text" name="_dingdan" placeholder="请输入订单号" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-inline" style="margin-top: 20px;margin-left: 10px;width: 570px;">
            <button class="layui-btn layui-btn-sm" lay-submit lay-filter="job-tbl-search"><i
                    class="layui-icon layui-icon-search"></i>查询</button>
        </div>
    </div>
    </div>
</script>

<script type="text/html" id="job-rowtbl-toolbar">
    <a class="layui-btn layui-btn-xs" lay-event="DetatilJob">详情</a>
</script>

<!-- 工具栏模板： -->
<script type="text/html" id="details-tbl-toolbar">
    <div class="layui-btn-container" hidden="true">
        <div class="layui-inline">
            <label class="layui-form-label">订单号</label>
            <div class="layui-input-inline">
                <input type="text" name="_dingdan" id="s_dingdan" placeholder="请输入订单号" autocomplete="off"
                       class="layui-input">
            </div>
        </div>
        <div class="layui-inline" style="margin-top: 3%;margin-left: 2px">
            <button class="layui-btn layui-btn-sm" id="search_btn" lay-submit lay-filter="work-tbl-search"><i
                    class="layui-icon layui-icon-search"></i></button>
        </div>
    </div>
    </div>
</script>


<div class="layui-fluid " style="width: 570px;float: left;margin-top:5px;">
    <div class="layui-card">
        <div class="layui-card-body">
            <table id="job-tbl" lay-filter="job-tbl"></table>
        </div>
    </div>
</div>

<div class="layui-fluid " style="width: 570px;border:0px solid red;margin-left:580px;margin-top:5px;">
    <div class="layui-card">
        <div class="layui-card-body">
            <table id="work-tbl" lay-filter="work-tbl"></table>
        </div>
    </div>
</div>

<!-- layui的引用 js -->
<script type="text/javascript" src="<%=basePath %>js/layui-v2.5.5/layui/layui.js"></script>
<!-- 页面JS -->
<script>
    var basePath = "<%=basePath %>";
</script>

<script type="text/javascript" src="<%=basePath %>jsp/third/jobdata/index.js?v=2020231212"></script>

</body>
</html>

js

layui.use(['jquery', 'table', 'layer', 'form'], function () {
    // 加载layui模块，使用其推荐的【预先加载】方式，详见官网【模块规范】一节
    var $ = layui.$;
    var table = layui.table;
    var layer = layui.layer;
    var form = layui.form;

    //详情列表
    table.render({
        elem: '#work-tbl',
        url: basePath + 'JobData/getDetailList.do',
        toolbar: '#details-tbl-toolbar',
        method: 'get',
        width: 550,
        cols: [[{
            field: 'workCenter',
            title: '工作中心',
            width: 120
        }, {
            field: 'moduleName',
            title: '工序段',
            width: 120
        }, {
            field: 'base',
            title: '基数',
            width: 60
        }, {
            field: '_bzgs',
            title: '标准工时',
            width: 80
        }, {
            field: '_sjgs',
            title: '实际工时',
            width: 80
        }, {
            field: 'lv',
            title: '效率',
            width: 80
        }]],
    });

    table.render({
        elem: '#job-tbl',
        url: basePath + 'JobData/getList.do',
        toolbar: '#job-tbl-toolbar',
        method: 'get',
        width: '400px',
        page: true,// 开启分页
        cols: [[{
            field: '_dingdan',
            title: '订单号',
            width: 130,
            sort: true
        }, {
            field: '_number',
            title: '物料号',
            width: 100,
            sort: true
        }, {
            field: '_name',
            width: 150,
            title: '型号',
        }, {
            toolbar: '#job-rowtbl-toolbar',
            title: '操作',
            width: 100
        }]],
        page: true,
    });

    //查询监听
    form.on('submit(job-tbl-search)', function (data) {
        table.reload('job-tbl', {
            where: data.field
        })
    });

    //报工查询
    form.on('submit(work-tbl-search)', function (data) {
        table.reload('work-tbl', {
            where: data.field
        })
    })

// 监听详情
    table.on('tool(job-tbl)', function (obj) {
        var data = obj.data;
        var event = obj.event;
        var _dingdan = data._dingdan;
        if (event === 'DetatilJob') {
            $('#s_dingdan').val(_dingdan);
            $('#search_btn').click();
        }
    });
});