团队作业（五）：冲刺总结（六）

 

 

1.今日任务安排

• 继续完成后端*.Controller代码的编写
• 建立数据库链接，对数据库的响应（数据库表的增、删、改、查）。
• 完成访问控制功能的实现（管理员、审核员、普通工作人员等）。
• 继续前端界面的设计

2.遇到的困难

1.在实现公文的提交和审核功能的时候，普通工作人员可以成功提交文件，但当审核员从系统中要审核提交者的文件的时候，下载时公文显示的是乱码。

文件上传以后，加密过程是成功的，但是下载解密后的文件显示乱码，问题出在解密的传输流问题，将代码修改后成功解决该问题。

2.在撰写公文时，标题不宜写的过于冗长，否则将会出现系统警告。

3.今日成果

• LogController.java

package cn.edu.nuc.article.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.github.pagehelper.PageInfo;
import com.github.pagehelper.page.PageMethod;

import cn.edu.nuc.article.entity.Log;
import cn.edu.nuc.article.service.LogService;

/**
 * 日志Controller
 *
 */
@Controller
@RequestMapping("/log")
public class LogController {

/**
* 日志Service
*/
@Autowired
private LogService logService;

/**
* 分页+模糊查询
* @param pageNo 当前页
* @param pageCount 每页记录数
* @param function 待查参数
* @return
*/
@RequestMapping("/logs")
public String logs(Map<String, Object> map,
@RequestParam(value="pageNo", defaultValue="1", required=false) Integer pageNo,
@RequestParam(value="pageCount", defaultValue="10", required=false) Integer pageCount,
@RequestParam(value="keyword", required=false) String keyword) {

// 引入PageHelper分页插件
// 在查询之前只需要调用，传入页码，以及每页的大小
PageMethod.startPage(pageNo, pageCount);

// 分页查询得到结果集
List<Log> logs;

if (StringUtils.hasText(keyword)) {
Log log = new Log();
log.setOptname(keyword);
logs = logService.findByKeyword(log);
} else {
logs = logService.findByKeyword(null);
}

// 使用pageInfo包装查询后的结果，只需要将pageInfo交给页面就行了。
// 封装了详细的分页信息,包括有我们查询出来的数据，传入连续显示的页数
PageInfo<Log> page = new PageInfo<Log>(logs, 5);

//保存结果集带到页面显示
map.put("page", page);
map.put("pageNo", pageNo);
map.put("pageCount", pageCount);

//保存模糊查询条件以便回显
map.put("keyword", keyword);

return "log/logManage";

}

}

• sm4.js

/**
 * base64js
 * base64js.toByteArray(d.input)
 * base64js.fromByteArray(c);
 * 国密SM4加密算法
 */
(function(r) {
    if (typeof exports === "object" && typeof module !== "undefined") {
        module.exports = r()
    } else {
        if (typeof define ===
            "function" && define.amd) {
            define([], r)
        } else {
            var e;
            if (typeof window !== "undefined") {
                e = window
            } else {
                if (typeof global !==
                    "undefined") {
                    e = global
                } else {
                    if (typeof self !== "undefined") {
                        e = self
                    } else {
                        e = this
                    }
                }
            }
            e.base64js = r()
        }
    }
})(function() {
    var r, e, t;
    return function r(e, t, n) {
        function o(i, a) {
            if (!t[i]) {
                if (!e[i]) {
                    var u = typeof require == "function" && require;
                    if (!a && u) {
                        return u(i, !0)
                    }
                    if (f) {
                        return f(i, !0)
                    }
                    var d = new Error("Cannot find module '" + i + "'");
                    throw d.code = "MODULE_NOT_FOUND", d
                }
                var c = t[i] = {
                    exports: {}
                };
                e[i][0].call(c.exports, function(r) {
                    var t = e[i][1][r];
                    return o(t ? t : r)
                }, c, c.exports, r, e, t, n)
            }
            return t[i].exports
        }
        var f = typeof require == "function" && require;
        for (var i = 0; i < n.length; i++) {
            o(n[i])
        }
        return o
    }({
        "/": [function(r, e, t) {
            t.byteLength = c;
            t.toByteArray = v;
            t.fromByteArray = s;
            var n = [];
            var o = [];
            var f = typeof Uint8Array !== "undefined" ? Uint8Array : Array;
            var i = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
            for (var a = 0, u = i.length; a < u; ++a) {
                n[a] = i[a];
                o[i.charCodeAt(a)] = a
            }
            o["-".charCodeAt(0)] = 62;
            o["_".charCodeAt(0)] = 63;

            function d(r) {
                var e = r.length;
                if (e % 4 > 0) {
                    throw new Error("Invalid string. Length must be a multiple of 4")
                }
                return r[e - 2] === "=" ? 2 : r[e - 1] === "=" ? 1 : 0
            }

            function c(r) {
                return r.length * 3 / 4 - d(r)
            }

            function v(r) {
                var e, t, n, i, a;
                var u = r.length;
                i = d(r);
                a = new f(u * 3 / 4 - i);
                t = i > 0 ? u - 4 : u;
                var c = 0;
                for (e = 0; e < t; e += 4) {
                    n = o[r.charCodeAt(e)] << 18 | o[r.charCodeAt(e + 1)] << 12 | o[r.charCodeAt(e + 2)] << 6 | o[r.charCodeAt(
                        e + 3)];
                    a[c++] = n >> 16 & 255;
                    a[c++] = n >> 8 & 255;
                    a[c++] = n & 255
                }
                if (i === 2) {
                    n = o[r.charCodeAt(e)] << 2 | o[r.charCodeAt(e + 1)] >> 4;
                    a[c++] = n & 255
                } else {
                    if (i === 1) {
                        n = o[r.charCodeAt(e)] << 10 | o[r.charCodeAt(e + 1)] << 4 | o[r.charCodeAt(e + 2)] >> 2;
                        a[c++] = n >> 8 & 255;
                        a[c++] = n & 255
                    }
                }
                return a
            }

            function l(r) {
                return n[r >> 18 & 63] + n[r >> 12 & 63] + n[r >> 6 & 63] + n[r & 63]
            }

            function h(r, e, t) {
                var n;
                var o = [];
                for (var f = e; f < t; f += 3) {
                    n = (r[f] << 16) + (r[f + 1] << 8) + r[f + 2];
                    o.push(l(n))
                }
                return o.join("")
            }

            function s(r) {
                var e;
                var t = r.length;
                var o = t % 3;
                var f = "";
                var i = [];
                var a = 16383;
                for (var u = 0, d = t - o; u < d; u += a) {
                    i.push(h(r, u, u + a > d ? d : u + a))
                }
                if (o === 1) {
                    e = r[t - 1];
                    f += n[e >> 2];
                    f += n[e << 4 & 63];
                    f += "=="
                } else {
                    if (o === 2) {
                        e = (r[t - 2] << 8) + r[t - 1];
                        f += n[e >> 10];
                        f += n[e >> 4 & 63];
                        f += n[e << 2 & 63];
                        f += "="
                    }
                }
                i.push(f);
                return i.join("")
            }
        }, {}]
    }, {}, [])("/")
});


/**
 * 国密SM4加密算法
 */
function SM4_Context() {
    this.mode = 1;
    this.isPadding = true;
    this.sk = new Array(32);
}

function SM4() {
    this.SM4_ENCRYPT = 1;
    this.SM4_DECRYPT = 0;

    var SboxTable = [0xd6, 0x90, 0xe9, 0xfe, 0xcc, 0xe1, 0x3d, 0xb7, 0x16, 0xb6, 0x14, 0xc2, 0x28, 0xfb, 0x2c, 0x05,
        0x2b, 0x67, 0x9a, 0x76, 0x2a, 0xbe, 0x04, 0xc3, 0xaa, 0x44, 0x13, 0x26, 0x49, 0x86, 0x06, 0x99,
        0x9c, 0x42, 0x50, 0xf4, 0x91, 0xef, 0x98, 0x7a, 0x33, 0x54, 0x0b, 0x43, 0xed, 0xcf, 0xac, 0x62,
        0xe4, 0xb3, 0x1c, 0xa9, 0xc9, 0x08, 0xe8, 0x95, 0x80, 0xdf, 0x94, 0xfa, 0x75, 0x8f, 0x3f, 0xa6,
        0x47, 0x07, 0xa7, 0xfc, 0xf3, 0x73, 0x17, 0xba, 0x83, 0x59, 0x3c, 0x19, 0xe6, 0x85, 0x4f, 0xa8,
        0x68, 0x6b, 0x81, 0xb2, 0x71, 0x64, 0xda, 0x8b, 0xf8, 0xeb, 0x0f, 0x4b, 0x70, 0x56, 0x9d, 0x35,
        0x1e, 0x24, 0x0e, 0x5e, 0x63, 0x58, 0xd1, 0xa2, 0x25, 0x22, 0x7c, 0x3b, 0x01, 0x21, 0x78, 0x87,
        0xd4, 0x00, 0x46, 0x57, 0x9f, 0xd3, 0x27, 0x52, 0x4c, 0x36, 0x02, 0xe7, 0xa0, 0xc4, 0xc8, 0x9e,
        0xea, 0xbf, 0x8a, 0xd2, 0x40, 0xc7, 0x38, 0xb5, 0xa3, 0xf7, 0xf2, 0xce, 0xf9, 0x61, 0x15, 0xa1,
        0xe0, 0xae, 0x5d, 0xa4, 0x9b, 0x34, 0x1a, 0x55, 0xad, 0x93, 0x32, 0x30, 0xf5, 0x8c, 0xb1, 0xe3,
        0x1d, 0xf6, 0xe2, 0x2e, 0x82, 0x66, 0xca, 0x60, 0xc0, 0x29, 0x23, 0xab, 0x0d, 0x53, 0x4e, 0x6f,
        0xd5, 0xdb, 0x37, 0x45, 0xde, 0xfd, 0x8e, 0x2f, 0x03, 0xff, 0x6a, 0x72, 0x6d, 0x6c, 0x5b, 0x51,
        0x8d, 0x1b, 0xaf, 0x92, 0xbb, 0xdd, 0xbc, 0x7f, 0x11, 0xd9, 0x5c, 0x41, 0x1f, 0x10, 0x5a, 0xd8,
        0x0a, 0xc1, 0x31, 0x88, 0xa5, 0xcd, 0x7b, 0xbd, 0x2d, 0x74, 0xd0, 0x12, 0xb8, 0xe5, 0xb4, 0xb0,
        0x89, 0x69, 0x97, 0x4a, 0x0c, 0x96, 0x77, 0x7e, 0x65, 0xb9, 0xf1, 0x09, 0xc5, 0x6e, 0xc6, 0x84,
        0x18, 0xf0, 0x7d, 0xec, 0x3a, 0xdc, 0x4d, 0x20, 0x79, 0xee, 0x5f, 0x3e, 0xd7, 0xcb, 0x39, 0x48
    ];

    var FK = [0xa3b1bac6, 0x56aa3350, 0x677d9197, 0xb27022dc];

    var CK = [0x00070e15, 0x1c232a31, 0x383f464d, 0x545b6269,
        0x70777e85, 0x8c939aa1, 0xa8afb6bd, 0xc4cbd2d9,
        0xe0e7eef5, 0xfc030a11, 0x181f262d, 0x343b4249,
        0x50575e65, 0x6c737a81, 0x888f969d, 0xa4abb2b9,
        0xc0c7ced5, 0xdce3eaf1, 0xf8ff060d, 0x141b2229,
        0x30373e45, 0x4c535a61, 0x686f767d, 0x848b9299,
        0xa0a7aeb5, 0xbcc3cad1, 0xd8dfe6ed, 0xf4fb0209,
        0x10171e25, 0x2c333a41, 0x484f565d, 0x646b7279
    ];

    this.GET_ULONG_BE = function(b, i) {
        return (b[i] & 0xff) << 24 | ((b[i + 1] & 0xff) << 16) | ((b[i + 2] & 0xff) << 8) | (b[i + 3] & 0xff) & 0xffffffff;
    }

    this.PUT_ULONG_BE = function(n, b, i) {
        var t1 = (0xFF & (n >> 24));
        var t2 = (0xFF & (n >> 16));
        var t3 = (0xFF & (n >> 8));
        var t4 = (0xFF & (n));
        b[i] = t1 > 128 ? t1 - 256 : t1;
        b[i + 1] = t2 > 128 ? t2 - 256 : t2;
        b[i + 2] = t3 > 128 ? t3 - 256 : t3;
        b[i + 3] = t4 > 128 ? t4 - 256 : t4;
    }

    this.SHL = function(x, n) {
        return (x & 0xFFFFFFFF) << n;
    }

    this.ROTL = function(x, n) {
        var s = this.SHL(x, n);
        var ss = x >> (32 - n);
        return this.SHL(x, n) | x >> (32 - n);
    }

    this.sm4Lt = function(ka) {
        var bb = 0;
        var c = 0;
        var a = new Array(4);
        var b = new Array(4);
        this.PUT_ULONG_BE(ka, a, 0);
        b[0] = this.sm4Sbox(a[0]);
        b[1] = this.sm4Sbox(a[1]);
        b[2] = this.sm4Sbox(a[2]);
        b[3] = this.sm4Sbox(a[3]);
        bb = this.GET_ULONG_BE(b, 0);
        c = bb ^ this.ROTL(bb, 2) ^ this.ROTL(bb, 10) ^ this.ROTL(bb, 18) ^ this.ROTL(bb, 24);
        return c;
    }

    this.sm4F = function(x0, x1, x2, x3, rk) {
        return x0 ^ this.sm4Lt(x1 ^ x2 ^ x3 ^ rk);
    }

    this.sm4CalciRK = function(ka) {
        var bb = 0;
        var rk = 0;
        var a = new Array(4);
        var b = new Array(4);
        this.PUT_ULONG_BE(ka, a, 0);
        b[0] = this.sm4Sbox(a[0]);
        b[1] = this.sm4Sbox(a[1]);
        b[2] = this.sm4Sbox(a[2]);
        b[3] = this.sm4Sbox(a[3]);
        bb = this.GET_ULONG_BE(b, 0);
        rk = bb ^ this.ROTL(bb, 13) ^ this.ROTL(bb, 23);
        return rk;
    }

    this.sm4Sbox = function(inch) {
        var i = inch & 0xFF;
        var retVal = SboxTable[i];
        return retVal > 128 ? retVal - 256 : retVal;
    }

    this.sm4_setkey_enc = function(ctx, key) {
        if (ctx == null) {
            alert("ctx is null!");
            return false;
        }
        if (key == null || key.length != 16) {
            alert("key error!");
            return false;
        }
        ctx.mode = this.SM4_ENCRYPT;
        this.sm4_setkey(ctx.sk, key);
    };
    //生成解密密钥
    this.sm4_setkey_dec = function(ctx, key) {
        if (ctx == null) {
            Error("ctx is null!");
        }

        if (key == null || key.length != 16) {
            Error("key error!");
        }

        var i = 0;
        ctx.mode = 0;
        this.sm4_setkey(ctx.sk, key);
        ctx.sk = ctx.sk.reverse();
    }


    this.sm4_setkey = function(SK, key) {
        var MK = new Array(4);
        var k = new Array(36);
        var i = 0;
        MK[0] = this.GET_ULONG_BE(key, 0);
        MK[1] = this.GET_ULONG_BE(key, 4);
        MK[2] = this.GET_ULONG_BE(key, 8);
        MK[3] = this.GET_ULONG_BE(key, 12);
        k[0] = MK[0] ^ FK[0];
        k[1] = MK[1] ^ FK[1];
        k[2] = MK[2] ^ FK[2];
        k[3] = MK[3] ^ FK[3];
        for (var i = 0; i < 32; i++) {
            k[(i + 4)] = (k[i] ^ this.sm4CalciRK(k[(i + 1)] ^ k[(i + 2)] ^ k[(i + 3)] ^ CK[i]));
            SK[i] = k[(i + 4)];
        }

    }
    this.padding = function(input, mode) {
        if (input == null) {
            return null;
        }
        var ret = null;
        if (mode == this.SM4_ENCRYPT) {
            var p = parseInt(16 - input.length % 16);
            ret = input.slice(0);
            for (var i = 0; i < p; i++) {
                ret[input.length + i] = p;
            }
        } else {
            var p = input[input.length - 1];
            ret = input.slice(0, input.length - p);
        }
        return ret;
    }
    this.sm4_one_round = function(sk, input, output) {
        var i = 0;
        var ulbuf = new Array(36);
        ulbuf[0] = this.GET_ULONG_BE(input, 0);
        ulbuf[1] = this.GET_ULONG_BE(input, 4);
        ulbuf[2] = this.GET_ULONG_BE(input, 8);
        ulbuf[3] = this.GET_ULONG_BE(input, 12);
        while (i < 32) {
            ulbuf[(i + 4)] = this.sm4F(ulbuf[i], ulbuf[(i + 1)], ulbuf[(i + 2)], ulbuf[(i + 3)], sk[i]);
            i++;
        }
        this.PUT_ULONG_BE(ulbuf[35], output, 0);
        this.PUT_ULONG_BE(ulbuf[34], output, 4);
        this.PUT_ULONG_BE(ulbuf[33], output, 8);
        this.PUT_ULONG_BE(ulbuf[32], output, 12);

    }

    this.sm4_crypt_ecb = function(ctx, input) {
        if (input == null) {
            alert("input is null!");
        }
        if ((ctx.isPadding) && (ctx.mode == this.SM4_ENCRYPT)) {
            input = this.padding(input, this.SM4_ENCRYPT);
        }

        var i = 0;
        var length = input.length;
        var bous = new Array();
        for (; length > 0; length -= 16) {
            var out = new Array(16);
            var ins = input.slice(i * 16, (16 * (i + 1)));
            this.sm4_one_round(ctx.sk, ins, out)
            bous = bous.concat(out);
            i++;
        }

        var output = bous;
        if (ctx.isPadding && ctx.mode == this.SM4_DECRYPT) {
            output = this.padding(output, this.SM4_DECRYPT);
        }
        for (var i = 0; i < output.length; i++) {
            if (output[i] < 0) {
                output[i] = output[i] + 256;
            }
        }
        return output;
    }

    this.sm4_crypt_cbc = function(ctx, iv, input) {
        if (iv == null || iv.length != 16) {
            alert("iv error!");
        }

        if (input == null) {
            alert("input is null!");
        }

        if (ctx.isPadding && ctx.mode == this.SM4_ENCRYPT) {
            input = this.padding(input, this.SM4_ENCRYPT);
        }

        var i = 0;
        var length = input.length;
        var bous = new Array();
        if (ctx.mode == this.SM4_ENCRYPT) {
            var k = 0;
            for (; length > 0; length -= 16) {
                var out = new Array(16);
                var out1 = new Array(16);
                var ins = input.slice(k * 16, (16 * (k + 1)));

                for (i = 0; i < 16; i++) {
                    out[i] = (ins[i] ^ iv[i]);
                }
                this.sm4_one_round(ctx.sk, out, out1);
                iv = out1.slice(0, 16);
                bous = bous.concat(out1);
                k++;
            }
        } else {
            var temp = [];
            var k = 0;
            for (; length > 0; length -= 16) {
                var out = new Array(16);
                var out1 = new Array(16);
                var ins = input.slice(k * 16, (16 * (k + 1)));
                temp = ins.slice(0, 16);
                this.sm4_one_round(ctx.sk, ins, out);
                for (i = 0; i < 16; i++) {
                    out1[i] = (out[i] ^ iv[i]);
                }
                iv = temp.slice(0, 16);
                bous = bous.concat(out1);
                k++;
            }
        }

        var output = bous;
        if (ctx.isPadding && ctx.mode == this.SM4_DECRYPT) {
            output = this.padding(output, this.SM4_DECRYPT);
        }

        for (var i = 0; i < output.length; i++) {
            if (output[i] < 0) {
                output[i] = output[i] + 256;
            }
        }
        return output;
    }
}

function SM4Util() {
    this.secretKey = "";
    this.iv = "";
    this.hexString = false;
    //加密_ECB
    this.encryptData_ECB = function(plainText) {
        try {
            var sm4 = new SM4();
            var ctx = new SM4_Context();
            ctx.isPadding = true;
            ctx.mode = sm4.SM4_ENCRYPT;
            var keyBytes = stringToByte(this.secretKey);
            sm4.sm4_setkey_enc(ctx, keyBytes);
            var encrypted = sm4.sm4_crypt_ecb(ctx, stringToByte(plainText));
            var cipherText = base64js.fromByteArray(encrypted);
            if (cipherText != null && cipherText.trim().length > 0) {
                cipherText.replace(/(\s*|\t|\r|\n)/g, "");
            }
            return cipherText;
        } catch (e) {
            console.error(e);
            return null;
        }

    }
    //解密_ECB
    this.decryptData_ECB = function(cipherText) {
        try {
            var sm4 = new SM4();
            var ctx = new SM4_Context();
            ctx.isPadding = true;
            ctx.mode = sm4.SM4_ENCRYPT;
            var keyBytes = stringToByte(this.secretKey);
            sm4.sm4_setkey_dec(ctx, keyBytes);
            var decrypted = sm4.sm4_crypt_ecb(ctx, base64js.toByteArray(cipherText));
            return byteToString(decrypted);
        } catch (e) {
            console.error(e);
            return null;
        }
    }

    this.encryptData_CBC = function(plainText) {
        try {
            var sm4 = new SM4();
            var ctx = new SM4_Context();
            ctx.isPadding = true;
            ctx.mode = sm4.SM4_ENCRYPT;

            var keyBytes = stringToByte(this.secretKey);
            var ivBytes = stringToByte(this.iv);

            sm4.sm4_setkey_enc(ctx, keyBytes);
            var encrypted = sm4.sm4_crypt_cbc(ctx, ivBytes, stringToByte(plainText));
            var cipherText = base64js.fromByteArray(encrypted);
            if (cipherText != null && cipherText.trim().length > 0) {
                cipherText.replace(/(\s*|\t|\r|\n)/g, "");
            }
            return cipherText;
        } catch (e) {
            console.error(e);
            return null;
        }
    }
    //解密_CBC
    this.decryptData_CBC = function(cipherText) {
        try {
            var sm4 = new SM4();
            var ctx = new SM4_Context();
            ctx.isPadding = true;
            ctx.mode = sm4.SM4_ENCRYPT;
            var keyBytes = stringToByte(this.secretKey);
            var ivBytes = stringToByte(this.iv);
            sm4.sm4_setkey_dec(ctx, keyBytes);
            var decrypted = sm4.sm4_crypt_cbc(ctx, ivBytes, base64js.toByteArray(cipherText));
            return byteToString(decrypted);
        } catch (e) {
            console.error(e);
            return null;
        }
    }
    stringToByte = function(str) {
        var bytes = new Array();
        var len, c;
        len = str.length;
        for (var i = 0; i < len; i++) {
            c = str.charCodeAt(i);
            if (c >= 0x010000 && c <= 0x10FFFF) {
                bytes.push(((c >> 18) & 0x07) | 0xF0);
                bytes.push(((c >> 12) & 0x3F) | 0x80);
                bytes.push(((c >> 6) & 0x3F) | 0x80);
                bytes.push((c & 0x3F) | 0x80);
            } else if (c >= 0x000800 && c <= 0x00FFFF) {
                bytes.push(((c >> 12) & 0x0F) | 0xE0);
                bytes.push(((c >> 6) & 0x3F) | 0x80);
                bytes.push((c & 0x3F) | 0x80);
            } else if (c >= 0x000080 && c <= 0x0007FF) {
                bytes.push(((c >> 6) & 0x1F) | 0xC0);
                bytes.push((c & 0x3F) | 0x80);
            } else {
                bytes.push(c & 0xFF);
            }
        }
        return bytes;
    }

    byteToString = function(arr) {
        if (typeof arr === 'string') {
            return arr;
        }
        var str = '',
            _arr = arr;
        for (var i = 0; i < _arr.length; i++) {
            var one = _arr[i].toString(2),
                v = one.match(/^1+?(?=0)/);
            if (v && one.length == 8) {
                var bytesLength = v[0].length;
                var store = _arr[i].toString(2).slice(7 - bytesLength);
                for (var st = 1; st < bytesLength; st++) {
                    store += _arr[st + i].toString(2).slice(2);
                }
                str += String.fromCharCode(parseInt(store, 2));
                i += bytesLength - 1;
            } else {
                str += String.fromCharCode(_arr[i]);
            }
        }
        return str;
    }
};

• ArticleMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.edu.nuc.article.dao.ArticleMapper">

<!-- 级联查询结果集 -->
<resultMap id="BaseResultMap" type="cn.edu.nuc.article.entity.Article">
<!-- 表中原有的列 -->
<id column="articleid" jdbcType="INTEGER" property="articleid" />
<result column="title" jdbcType="VARCHAR" property="title" />
<result column="publishtime" jdbcType="TIMESTAMP" property="publishtime" />
<result column="copywriter_id" jdbcType="INTEGER" property="copywriterId" />
<result column="auditor_id" jdbcType="INTEGER" property="auditorId" />
<result column="inst_id" jdbcType="INTEGER" property="instId" />
<result column="processinstance_id" jdbcType="VARCHAR" property="processinstanceId" />
<result column="articlestate" jdbcType="INTEGER" property="articlestate" />
<result column="clickcount" jdbcType="BIGINT" property="clickcount" />
<result column="downloadcount" jdbcType="BIGINT" property="downloadcount" />
<!-- 级联的列 -->
<!-- 级联撰稿人信息 -->
<association property="copywriter" javaType="cn.edu.nuc.article.entity.User">
<id column="copywriterid" jdbcType="INTEGER" property="userid" />
<result column="copywritername" jdbcType="VARCHAR" property="usertruename" />
</association>
<!-- 级联审稿人信息 -->
<association property="auditor" javaType="cn.edu.nuc.article.entity.User">
<id column="auditorid" jdbcType="INTEGER" property="userid" />
<result column="auditorname" jdbcType="VARCHAR" property="usertruename" />
</association>
<!-- 级联机构信息 -->
<association property="institution" javaType="cn.edu.nuc.article.entity.Institution">
<id column="instid" jdbcType="INTEGER" property="instid" />
<result column="instname" jdbcType="VARCHAR" property="instname" />
</association>
<!-- 级联附件信息 -->
<collection property="attachments" ofType="cn.edu.nuc.article.entity.Attachment">
<id column="attachmentid" jdbcType="INTEGER" property="attachmentid" />
<result column="filename" jdbcType="VARCHAR" property="filename" />
<result column="filesize" jdbcType="INTEGER" property="filesize" />
<result column="uploadtime" jdbcType="TIMESTAMP" property="uploadtime" />
<result column="attachtype" jdbcType="INTEGER" property="attachtype" />
<result column="fileid" jdbcType="VARCHAR" property="fileid" />
</collection>
<!-- 级联审核信息 -->
<collection property="auditMessages" ofType="cn.edu.nuc.article.entity.AuditMessage">
    <result column="auditdate" jdbcType="TIMESTAMP" property="auditdate" />
    <result column="auditresult" jdbcType="INTEGER" property="auditresult" />
    <result column="auditmessage" jdbcType="VARCHAR" property="auditmessage" />
</collection>
<!-- 级联接收人信息 -->
<collection property="receivers" ofType="cn.edu.nuc.article.entity.User">
<id column="receiverid" jdbcType="INTEGER" property="userid" />
<result column="receivername" jdbcType="VARCHAR" property="usertruename" />
<!-- 级联接收人名称和id -->
<association property="institution" javaType="cn.edu.nuc.article.entity.Institution">
<id column="receiver_inst_id" jdbcType="INTEGER" property="instid" />
<result column="receiver_inst_name" jdbcType="VARCHAR" property="instname" />
</association>
</collection>
</resultMap>
<!-- 级联查询语句 -->
<sql id="CascadeSelectSql">
SELECT DISTINCT
a.articleid,
a.title,
a.publishtime,
a.copywriter_id,
a.auditor_id,
a.inst_id,
a.processinstance_id,
a.articlestate,
receiver.userid AS receiverid,
receiver.usertruename AS receivername,
tb_auditmessage.auditdate,
tb_auditmessage.auditresult,
tb_auditmessage.auditmessage,
tb_institution.instid,
tb_institution.instname,
copywriter.usertruename AS copywritername,
copywriter.userid AS copywriterid,
auditor.userid AS auditorid,
auditor.usertruename AS auditorname,
tb_attachment.attachmentid,
tb_attachment.filename,
tb_attachment.attachtype,
tb_attachment.uploadtime,
tb_attachment.filesize,
tb_attachment.fileid,
receiver_inst.instname AS receiver_inst_name,
receiver_inst.instid AS receiver_inst_id,
(
SELECT DISTINCT
COUNT(*)
FROM
tb_log
LEFT OUTER JOIN tb_article ON tb_log.bussiness_id = tb_article.articleid
WHERE
tb_article.articleid = a.articleid
AND tb_log.optname = '查看公文'
) AS clickcount,
(
SELECT DISTINCT
COUNT(*)
FROM
tb_log
LEFT OUTER JOIN tb_article ON tb_log.bussiness_id = tb_article.articleid
WHERE
tb_article.articleid = a.articleid
AND tb_log.optname = '下载公文'
) AS downloadcount
FROM
tb_article AS a
LEFT OUTER JOIN tb_receive ON tb_receive.article_id = a.articleid
LEFT OUTER JOIN tb_user AS receiver ON tb_receive.receiver_id = receiver.userid
LEFT OUTER JOIN tb_auditmessage ON tb_auditmessage.article_id = a.articleid
LEFT OUTER JOIN tb_institution ON a.inst_id = tb_institution.instid
LEFT OUTER JOIN tb_user AS copywriter ON a.copywriter_id = copywriter.userid
LEFT OUTER JOIN tb_user AS auditor ON a.auditor_id = auditor.userid
LEFT OUTER JOIN tb_attachment ON tb_attachment.article_id = a.articleid
LEFT OUTER JOIN tb_institution AS receiver_inst ON receiver.inst_id = receiver_inst.instid

</sql>
<!-- 按id检索一条记录（更详细） -->
<select id="selectOne" resultMap="BaseResultMap">
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 加载和自己相关的列表，即要求接收人、撰稿人、审稿人 -->
WHERE 
(
tb_receive.receiver_id = #{userId,jdbcType=INTEGER}
OR copywriter.userid = #{userId,jdbcType=INTEGER}
OR auditor.userid = #{userId,jdbcType=INTEGER}
)

<!-- 查询条件id -->
AND a.articleid = #{articleid,jdbcType=INTEGER}
</select>
<!-- 查询列表 -->
<select id="selectListAll" parameterType="cn.edu.nuc.article.entity.Article"
resultMap="BaseResultMap">
<!-- 给字符串类型的参数加% -->
<if test="title != null">
<bind name="_title" value="'%'+title+'%'"/>
</if>
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 动态加入查询条件 -->
WHERE 1=1
<if test="articleid != null">
AND articleid = #{articleid,jdbcType=INTEGER}
</if>
<if test="title != null">
AND title LIKE #{_title,jdbcType=VARCHAR}
</if>
<if test="publishtime != null">
AND publishtime = #{publishtime,jdbcType=TIMESTAMP}
</if>
<if test="copywriterId != null">
AND copywriter_id = #{copywriterId,jdbcType=INTEGER}
</if>
<if test="auditorId != null">
AND auditor_id = #{auditorId,jdbcType=INTEGER}
</if>
<if test="instId != null">
AND inst_id = #{instId,jdbcType=INTEGER}
</if>
<if test="processinstanceId != null">
AND processinstance_id = #{processinstanceId,jdbcType=VARCHAR}
</if>
<if test="articlestate != null">
AND articlestate = #{articlestate,jdbcType=INTEGER}
</if>
<if test="receiverid != null">
AND receiverid = #{receiverid,jdbcType=INTEGER}
</if>
<!-- 加入排序条件 -->
ORDER BY tb_article.publishtime
</select>      
<!-- 查询列表 -->
<select id="validateAccess" resultType="java.lang.Long">
select
COUNT(*)
FROM
tb_article AS a
LEFT OUTER JOIN tb_receive ON tb_receive.article_id = a.articleid
LEFT OUTER JOIN tb_user AS receiver ON tb_receive.receiver_id = receiver.userid
LEFT OUTER JOIN tb_auditmessage ON tb_auditmessage.article_id = a.articleid
LEFT OUTER JOIN tb_institution ON a.inst_id = tb_institution.instid
LEFT OUTER JOIN tb_user AS copywriter ON a.copywriter_id = copywriter.userid
LEFT OUTER JOIN tb_user AS auditor ON a.auditor_id = auditor.userid
LEFT OUTER JOIN tb_attachment ON tb_attachment.article_id = a.articleid
LEFT OUTER JOIN tb_institution AS receiver_inst ON receiver.inst_id = receiver_inst.instid
<!-- 动态加入查询条件 -->
WHERE
<!-- 加载和自己相关的列表，即要求接收人、撰稿人、审稿人 -->
(
tb_receive.receiver_id = #{userId,jdbcType=INTEGER}
OR copywriter.userid = #{userId,jdbcType=INTEGER}
OR auditor.userid = #{userId,jdbcType=INTEGER}
)
AND articleid = #{articleid,jdbcType=INTEGER}
</select>
<!-- 查询列表 -->
<select id="selectMyReceiveList" parameterType="cn.edu.nuc.article.entity.Article"
resultMap="BaseResultMap">
<!-- 给字符串类型的参数加% -->
<if test="title != null">
<bind name="_title" value="'%'+title+'%'"/>
</if>
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 动态加入查询条件 -->
WHERE
<!-- 加载和自己相关的列表，即要求接收人、撰稿人、审稿人 -->
tb_receive.receiver_id = #{userId,jdbcType=INTEGER}
<if test="articleid != null">
AND articleid = #{articleid,jdbcType=INTEGER}
</if>
<if test="title != null">
AND title LIKE #{_title,jdbcType=VARCHAR}
</if>
<if test="publishtime != null">
AND publishtime = #{publishtime,jdbcType=TIMESTAMP}
</if>
<if test="instId != null">
AND inst_id = #{instId,jdbcType=INTEGER}
</if>
<if test="processinstanceId != null">
AND processinstance_id = #{processinstanceId,jdbcType=VARCHAR}
</if>
<if test="articlestate != null">
AND articlestate = #{articlestate,jdbcType=INTEGER}
</if>
<!-- 加入排序条件 -->
ORDER BY a.publishtime DESC
</select>
<!-- 查询列表 -->
<select id="selectMyAuditList" parameterType="cn.edu.nuc.article.entity.Article"
resultMap="BaseResultMap">
<!-- 给字符串类型的参数加% -->
<if test="title != null">
<bind name="_title" value="'%'+title+'%'"/>
</if>
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 动态加入查询条件 -->
WHERE
<!-- 加载和自己相关的列表，即要求接收人、撰稿人、审稿人 -->
articlestate = 0
AND copywriter.userid = #{userId,jdbcType=INTEGER}

<if test="articleid != null">
AND articleid = #{articleid,jdbcType=INTEGER}
</if>
<if test="title != null">
AND title LIKE #{_title,jdbcType=VARCHAR}
</if>

<!-- 加入排序条件 -->
ORDER BY a.publishtime DESC
</select>
<!-- 查询列表 -->
<select id="selectMyList" parameterType="cn.edu.nuc.article.entity.Article"
resultMap="BaseResultMap">
<!-- 给字符串类型的参数加% -->
<if test="title != null">
<bind name="_title" value="'%'+title+'%'"/>
</if>
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 动态加入查询条件 -->
WHERE
<!-- 加载和自己相关的列表，即要求接收人、撰稿人、审稿人 -->
(
(
tb_receive.receiver_id = #{userId,jdbcType=INTEGER}
AND articlestate = 3
)
OR copywriter.userid = #{userId,jdbcType=INTEGER}
OR auditor.userid = #{userId,jdbcType=INTEGER}
)
AND articlestate IN(3,4)
<if test="articleid != null">
AND articleid = #{articleid,jdbcType=INTEGER}
</if>
<if test="title != null">
AND title LIKE #{_title,jdbcType=VARCHAR}
</if>

<!-- 加入排序条件 -->
ORDER BY a.publishtime DESC
</select>
<!-- 查询列表 -->
<select id="selectByProcessInstances" resultMap="BaseResultMap">
<!-- 给字符串类型的参数加% -->
<if test="title != null">
<bind name="_title" value="'%'+title+'%'"/>
</if>
<!-- 引入级联Sql -->
<include refid="CascadeSelectSql"></include>
<!-- 动态加入查询条件 -->
WHERE
1=1
<foreach collection="tasks" item="task" 
open="AND processinstance_id IN(" close=")" separator=",">
#{task.processInstanceId,jdbcType=VARCHAR}
</foreach>
<if test="title != null">
AND title LIKE #{_title,jdbcType=VARCHAR}
</if>
<if test="articlestates != null">
<foreach collection="articlestates" item="articlestate" 
open="AND articlestate IN(" close=")" separator=",">
#{articlestate,jdbcType=INTEGER}
</foreach>
</if>
</select>
<!-- 查询等待审核通过公文的数量 -->
<select id="selectMyWaitingCount" resultType="java.lang.Long">
SELECT 
COUNT(*) 
FROM
tb_article a
LEFT OUTER JOIN tb_user AS copywriter ON a.copywriter_id = copywriter.userid
LEFT OUTER JOIN tb_user AS auditor ON a.auditor_id = auditor.userid
<!-- 动态加入查询条件 -->
WHERE
<!-- 要求撰稿人必须是自己 -->
copywriter.userid = #{userId,jdbcType=INTEGER}
AND articlestate = 0
</select>
<!-- 查询被驳回的公文数量 -->
<select id="selectMyFailCount" resultType="java.lang.Long">
SELECT 
COUNT(*) 
FROM
tb_article a
LEFT OUTER JOIN tb_user AS copywriter ON a.copywriter_id = copywriter.userid
LEFT OUTER JOIN tb_user AS auditor ON a.auditor_id = auditor.userid
<!-- 动态加入查询条件 -->
WHERE
<!-- 要求撰稿人必须是自己 -->
copywriter.userid = #{userId,jdbcType=INTEGER}
AND articlestate = 2
</select>
<!-- 查询需要我审核的公文数量 -->
<select id="selectMyDealCount" resultType="java.lang.Long">
SELECT 
COUNT(*) 
FROM
tb_article a
LEFT OUTER JOIN tb_user AS copywriter ON a.copywriter_id = copywriter.userid
LEFT OUTER JOIN tb_user AS auditor ON a.auditor_id = auditor.userid
<!-- 动态加入查询条件 -->
WHERE
<!-- 要求自己是审稿人 -->
auditor.userid = #{userId,jdbcType=INTEGER}
AND articlestate = 0
</select>
<!-- 查询待接收公文的数量 -->
<select id="selectMyCountReceiver" resultType="java.lang.Long">
SELECT 
DISTINCT COUNT(*) 
FROM
tb_article a
LEFT OUTER JOIN tb_receive ON tb_receive.article_id = a.articleid
LEFT OUTER JOIN tb_user AS receiver ON tb_receive.receiver_id = receiver.userid
WHERE 
<!-- 要求接收者为自己 -->
tb_receive.receiver_id = #{userId,jdbcType=INTEGER} 
<!-- 并且要保证用户没看过这篇文章 -->
AND(
SELECT
DISTINCT COUNT(*)
FROM
tb_log
LEFT OUTER JOIN tb_article
ON tb_log.bussiness_id = tb_article.articleid
WHERE
tb_article.articleid = a.articleid
AND tb_log.optname = '查看公文'
AND tb_article.articlestate = 3
) = 0
AND a.articlestate = 3
</select>
<!-- 检查公文标题是否重复 -->
<select id="validateTitle" resultType="java.lang.Long">
SELECT 
COUNT(*)
FROM
tb_article
WHERE 
title = #{title,jdbcType=VARCHAR}
<if test="articleid != null">
AND articleid != #{articleid,jdbcType=INTEGER}
</if>
</select>

<!-- 选择性插入 -->
<insert id="insertSelective" parameterType="cn.edu.nuc.article.entity.Article"
keyProperty="articleid" useGeneratedKeys="true">
insert into tb_article
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="articleid != null">
articleid,
</if>
<if test="title != null">
title,
</if>
<if test="publishtime != null">
publishtime,
</if>
<if test="copywriterId != null">
copywriter_id,
</if>
<if test="auditorId != null">
auditor_id,
</if>
<if test="instId != null">
inst_id,
</if>
<if test="processinstanceId != null">
processinstance_id,
</if>
<if test="articlestate != null">
articlestate,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="articleid != null">
#{articleid,jdbcType=INTEGER},
</if>
<if test="title != null">
#{title,jdbcType=VARCHAR},
</if>
<if test="publishtime != null">
#{publishtime,jdbcType=TIMESTAMP},
</if>
<if test="copywriterId != null">
#{copywriterId,jdbcType=INTEGER},
</if>
<if test="auditorId != null">
#{auditorId,jdbcType=INTEGER},
</if>
<if test="instId != null">
#{instId,jdbcType=INTEGER},
</if>
<if test="processinstanceId != null">
#{processinstanceId,jdbcType=VARCHAR},
</if>
<if test="articlestate != null">
#{articlestate,jdbcType=INTEGER},
</if>
</trim>
</insert>
<!-- 按主键选择性更新 -->
<update id="updateByPrimaryKeySelective" parameterType="cn.edu.nuc.article.entity.Article">
update tb_article
<set>
<if test="title != null">
title = #{title,jdbcType=VARCHAR},
</if>
<if test="publishtime != null">
publishtime = #{publishtime,jdbcType=TIMESTAMP},
</if>
<if test="copywriterId != null">
copywriter_id = #{copywriterId,jdbcType=INTEGER},
</if>
<if test="auditorId != null">
auditor_id = #{auditorId,jdbcType=INTEGER},
</if>
<if test="instId != null">
inst_id = #{instId,jdbcType=INTEGER},
</if>
<if test="processinstanceId != null">
processinstance_id = #{processinstanceId,jdbcType=VARCHAR},
</if>
<if test="articlestate != null">
articlestate = #{articlestate,jdbcType=INTEGER},
</if>
</set>
where 
articleid = #{articleid,jdbcType=INTEGER}
</update>
<!-- 按主键删除 -->
<delete id="deleteById" parameterType="java.lang.Integer">
delete from tb_article where articleid = #{articleid,jdbcType=INTEGER}
</delete>
</mapper>

• FunctionMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.edu.nuc.article.dao.FunctionMapper">

<!-- 级联查询结果集 -->
<resultMap id="CascadeResultMap" type="cn.edu.nuc.article.entity.Function">
<id column="funid" jdbcType="INTEGER" property="funid" />
<result column="funname" jdbcType="VARCHAR" property="funname" />
<result column="funpid" jdbcType="INTEGER" property="funpid" />
<result column="funurl" jdbcType="VARCHAR" property="funurl" />
<result column="funstate" jdbcType="INTEGER" property="funstate" />
<!-- 封装关联的父功能 -->
<association property="parentFunction" javaType="cn.edu.nuc.article.entity.Function">
<id column="parentFunid" property="funid"/>
<result column="parentFunname" property="funname"/>
</association>
</resultMap>
<!-- 级联查询通用SQL -->
<sql id="cascadeSelectSql">
SELECT
tb_function.funid,
tb_function.funname,
tb_function.funpid,
tb_function.funurl,
tb_function.funstate,
parent.funname parentFunname,
parent.funid parentFunid
FROM
tb_function
INNER JOIN 
tb_function AS parent 
ON 
tb_function.funpid = parent.funid
</sql>
<!-- 级联主键查询 -->
<select id="selectByPrimaryKey" parameterType="java.lang.Integer"
resultMap="CascadeResultMap">
<!-- 导入级联SQL -->
<include refid="cascadeSelectSql"></include>
<!-- 拼装模糊查询条件 -->
WHERE 
tb_function.funid = #{funid,jdbcType=INTEGER}
</select>
<!-- 级联模糊查询 -->
<select id="selectByKeyWord" parameterType="Function"
resultMap="CascadeResultMap">
<!-- 给字符串类型的参数加% -->
<if test="funname != null">
<bind name="_funname" value="'%'+funname+'%'"/>
</if>
<if test="funurl != null">
<bind name="_funurl" value="'%'+funurl+'%'"/>
</if>
<!-- 导入级联SQL -->
<include refid="cascadeSelectSql"></include>
<!-- 拼装模糊查询条件 -->
WHERE 1 = 1
<if test="funid != null">
AND tb_function.funid = #{funid}
</if>
<if test="funname != null">
AND tb_function.funname LIKE #{_funname}
</if>
<if test="funpid != null">
AND tb_function.funpid = #{funpid}
</if>
<if test="funurl != null">
AND tb_function.funurl LIKE #{funurl}
</if>
<if test="funstate != null">
AND tb_function.funstate = #{funstate}
</if>
ORDER BY
tb_function.funid DESC
</select>
<!-- 查询是否有同名功能 -->
<select id="selectByFunname" parameterType="cn.edu.nuc.article.entity.Function"
resultMap="CascadeResultMap">
SELECT
tb_function.funid,
tb_function.funname,
tb_function.funpid,
tb_function.funurl,
tb_function.funstate
FROM
tb_function
WHERE 
tb_function.funname = #{funname}
<if test="funid != null">
AND tb_function.funid != #{funid}
</if>
</select>
<!-- 选择性插入 -->
<insert id="insertSelective" parameterType="cn.edu.nuc.article.entity.Function"
useGeneratedKeys="true" keyProperty="funid">
insert into tb_function
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="funid != null">
funid,
</if>
<if test="funname != null">
funname,
</if>
<if test="funpid != null">
funpid,
</if>
<if test="funurl != null">
funurl,
</if>
<if test="funstate != null">
funstate,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="funid != null">
#{funid,jdbcType=INTEGER},
</if>
<if test="funname != null">
#{funname,jdbcType=VARCHAR},
</if>
<if test="funpid != null">
#{funpid,jdbcType=INTEGER},
</if>
<if test="funurl != null">
#{funurl,jdbcType=VARCHAR},
</if>
<if test="funstate != null">
#{funstate,jdbcType=INTEGER},
</if>
</trim>
</insert>

<!-- 按主键选择性更新 -->
<update id="updateByPrimaryKeySelective" parameterType="cn.edu.nuc.article.entity.Function">
update tb_function
<set>
<if test="funname != null">
funname = #{funname,jdbcType=VARCHAR},
</if>
<if test="funpid != null">
funpid = #{funpid,jdbcType=INTEGER},
</if>
<if test="funurl != null">
funurl = #{funurl,jdbcType=VARCHAR},
</if>
<if test="funstate != null">
funstate = #{funstate,jdbcType=INTEGER},
</if>
</set>
where 
funid = #{funid,jdbcType=INTEGER}
</update>

</mapper>

4.燃尽图

5.今日工作量