vue配置sql規則
- 實現效果
- 組件完整代碼
- 父組件
前端頁面實現動態配置sql條件,將JSON結構給到后端,后端進行sql組裝。
這里涉及的分組后端在組裝時用括號將這塊規則括起來就行,分組的sql連接符(并且/或者)取組里的第一個。
實現效果
組件完整代碼
<!-- conditionGroup.vue -->
<template><div :class="{ marginClass: onlyOne }" v-if="reDraw"><div class="condition-header" v-if="onlyOne"><div class="group-button"><el-tooltip content="分組"><iclass="el-icon-folder-opened"icon-class="group":style="{width: groupBtnSize + 'px',height: groupBtnSize + 'px',color: '#1890ff',cursor: 'pointer',}"@click.stop="_addGroup"></i></el-tooltip></div></div><divv-for="(item, index) in conditionList":style="{ 'flex-direction': 'column' }"><div:style="{display: 'flex','flex-direction': 'row','align-items': 'center',}"v-if="!item.groups"><div:style="{display: 'flex','flex-direction': 'row','align-items': 'center',}"><iclass="el-icon-circle-plus-outline color-success font-title-large"style="cursor: pointer"@click="_addItem(item)"></i><iclass="el-icon-circle-close color-danger font-title-large"style="cursor: pointer; margin-left: 5px"@click="_delItem(item)"></i><el-checkboxstyle="padding: 0 10px 0 10px"v-model="item.checked"></el-checkbox><template v-if="floor > 1 && (!item.line || item.line.length == 0)"><div:style="{width: gradWidth + leftWidth * (floor - item.floor - 1) + 'px',height: '42px',}"></div></template><template v-else v-for="(n, li) in item.line"><div:style="{width:li == item.line.length - 1? gradWidth + leftWidth * (floor - item.floor) + 'px': leftWidth + 'px',height: '42px',background: getFloorColor(li + 1),}":class="{'group-left': n.l == 2,'group-top-left': n.l == 4,'group-bottom-left': n.l == 5,}"><el-tooltip :content="'點擊取消所在分組'" v-if="n.l == 4"><iclass="el-icon-folder-opened"icon-class="group":style="{width: groupBtnSize + 'px',height: groupBtnSize + 'px',color: '#1890ff',cursor: 'pointer',}"@click="_delGroup(item, n.p)"></i></el-tooltip></div></template></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">并且/或者</div></div><el-selectv-model="item.operate"style="width: 120px; padding: 5px 0 5px 1px"size="small":disabled="item.header"><el-optionv-for="ot in [{ key: '并且', val: 'and' },{ key: '或者', val: 'or' },]":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">字段</div></div><el-selectv-model="item.field"style="width: 200px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"@change="(item.value = ''), (item.condition = '')"><el-optionv-for="ot in keyList":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">運算符</div></div><el-selectv-model="item.condition"v-if="conditionMap && conditionMap[item.field]"style="width: 120px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"><el-optionv-for="ot in conditionMap[item.field]":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select><el-selectv-model="item.condition"v-elsestyle="width: 120px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"><el-optionv-for="ot in conditionSelect":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">值</div></div><!-- 值類型:下拉選項、日期、時間、小數、整數、文本框 --><el-selectv-model="item.value"v-if="valList &&valList[item.field] &&valList[item.field].dom == 'select'"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"placeholder="請選擇"><el-optionv-for="ot in valList[item.field].data":key="ot.dictValue":label="ot.dictLabel":value="ot.dictValue"></el-option></el-select><el-date-pickerv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'date'"size="small"v-model="item.value"type="date"placeholder="請選擇日期"style="width: 400px;margin-left: 10px;cursor: pointer;padding: 5px 0 5px 0px;":editable="false"format="yyyy-MM-dd"value-format="yyyy-MM-dd"></el-date-picker><el-date-pickerv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'dateTime'"size="small"v-model="item.value"type="datetime"placeholder="請選擇時間"style="width: 400px;margin-left: 10px;cursor: pointer;padding: 5px 0 5px 0px;":editable="false"format="yyyy-MM-dd HH:mm:ss"value-format="yyyy-MM-dd HH:mm:ss"></el-date-picker><el-inputv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'decimals'"v-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="請輸入(支持小數)"clearabletype="number"size="small"oninput="if(value < 0 || value == '' || value == null) value='';"/><!-- if(!/^\d+(\.\d{1,2})?$/.test(value)) value=value.match(/[\d]+(\.\d{0,2})?/)?.[0]; 兩位 --><el-inputv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'integer'"v-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="請輸入(整數)"clearabletype="number":min="1"size="small"oninput="if(value.includes('.')) value=value.replace(/\./g, ''); if(value < 0 || value == '' || value == null) value=''; if(!/^\d+$/.test(value)) value=value.replace(/\D/g,'');"/><el-inputv-elsev-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="請輸入"clearablesize="small"/></div></div><conditionGroup:conditionList="item.groups"v-if="item.groups && item.groups.length > 0":only-one="false":parentData="parentData":floor="floor":borderColor="borderColor":key-list="keyList":val-list="valList":condition-map="conditionMap"></conditionGroup></div><!-- <el-buttonv-if="onlyOne"size="small"style="margin-top: 10px; cursor: pointer"@click="_addChild">添加規則</el-button> --></div>
</template><script>
const condition = {id: 1,index: 1,condition: "",operate: "and",field: "",value: "",checked: false,header: true,pid: -1,floor: 1,
};const gradWidth = 20;
const leftWidth = 20;
const groupBtnSize = 20;
const alpha = 0.2;const initData = [Object.assign({}, condition)];export default {name: "conditionGroup",components: {},props: {onlyOne: {type: Boolean,default: () => true,},floor: {type: Number,default: () => 1,},conditionList: {type: Array,default: () => initData,},keyList: {type: Array,default: () => [],},conditionMap: {type: Object,default: () => {},},valList: {type: Object,default: () => {},},parentData: {type: Object,default: () => {},},gradWidth: {type: Number,default: () => gradWidth,},leftWidth: {type: Number,default: () => leftWidth,},groupBtnSize: {type: Number,default: () => groupBtnSize,},borderColor: {type: Array,default: () => ["rgba(24, 124, 255, " + alpha + ")"],},},data() {return {plotList: [],loading: false,reDraw: true,addGroupIndex: 0,conditionSelect: [{ key: "等于", val: "eq" },{ key: "不等于", val: "notEq" },{ key: "大于", val: "gt" },{ key: "小于", val: "lt" },{ key: "大于等于", val: "gtq" },{ key: "小于等于", val: "ltq" },{ key: "包含", val: "like" },{ key: "不包含", val: "notLike" },// { key: "加", val: "add" },// { key: "減", val: "subtract" },// { key: "乘", val: "multiply" },// { key: "除", val: "divide" },],};},computed: {sidebar() {return this.$store.state.app.sidebar.opened;},},watch: {conditionList(val, oldVal) {this.$emit("input", val);while (this.borderColor.length < this.floor) {var _color = this.randomHexColor();while (this.borderColor.indexOf(_color) != -1) {_color = this.randomHexColor();}this.borderColor.push(_color);}this.reDraw = false;this.$nextTick(() => {this.reDraw = true;});},sidebar(val) {},},methods: {findChecked(list, arrParam) {var arr = arrParam || new Array();for (var i = 0; i < list.length; i++) {var o = list[i];if (o.groups && o.groups.length > 0) {this.findChecked(o.groups, arr);} else {if (o.checked) {arr.push(o);}}}return arr;},removeNode(list, targetList) {for (var i = 0; i < list.length; i++) {var o = list[i];for (var tid of targetList) {if (o.id == tid) {list.splice(i--, 1);}}}},findParentGroups(list, pid, retParam) {var ret = null || retParam;for (var i = 0; i < list.length; i++) {var o = list[i];if (o.groups && o.groups.length > 0) {if (o.id == pid) {ret = o;} else {ret = this.findParentGroups(o.groups, pid, ret);}}}return ret;},_addGroup() {this.addGroup(this.parentData.conditionList, this.parentData);},_delGroup(item, groupId) {this.delGroup(groupId, this.parentData.conditionList, this.parentData);},_addChild() {this.addChild(this.parentData.conditionList);},_delItem(item) {this.delItem(this.conditionList,item,this.parentData.conditionList,this.parentData);},_addItem(item) {this.addItem(this.conditionList,item.index,this.parentData.conditionList,this.parentData);},addItem(groups, index, conditionList, parentThis) {var newItem = Object.assign({}, condition, {id: new Date().getTime(),index: index + 1,floor: groups[0].floor,pid: groups[0].pid,});groups.splice(index, 0, newItem);parentThis.floor = this.refreshData(conditionList);},addChild(conditionList) {var newItem = Object.assign({}, condition, {id: new Date().getTime(),index: conditionList.length + 1,floor: 1,pid: -1,});newItem.header = false;conditionList.splice(conditionList.length, 0, newItem);},delItem(groups, item, conditionList, parentThis) {var sum = this.countItem(conditionList);if (sum <= 1) {return;}groups.splice(item.index - 1, 1);var currentGroups = this.findParentGroups(conditionList, groups[0].pid);if (currentGroups) {var parentGroups = this.findParentGroups(conditionList,currentGroups.pid);if (currentGroups.groups.length == 1) {var ag = JSON.parse(JSON.stringify(currentGroups.groups[0]));ag.index = currentGroups.index;ag.id = currentGroups.id;ag.pid = parentGroups ? parentGroups.id : -1;ag.floor = currentGroups.floor;if (ag.groups) {ag.groups.forEach((o, index) => {o.pid = ag.id;o.floor = ag.floor + 1;o.index = index + 1;});}if (parentGroups) {var _groups = this.findParentGroups(conditionList, parentGroups.id);_groups.groups.splice(currentGroups.index - 1, 1, ag);} else {conditionList.splice(currentGroups.index - 1, 1, ag);}}}if (conditionList.length == 1 && conditionList[0].groups) {var newList = JSON.parse(JSON.stringify(conditionList[0].groups));conditionList.splice(0, 1);for (var nl of newList) {nl.pid = -1;nl.floor = 1;conditionList.push(nl);}}parentThis.floor = this.refreshData(conditionList);},addGroup(conditionList, parentThis) {var checkedList = this.findChecked(conditionList);if (!checkedList || checkedList.length <= 1) {this.$message({message: "至少選擇2個查詢條目",type: "warning",duration: 1000,});return;}var checkNodes = [];for (var item of checkedList) {if (item.pid == -1) {this.uniquePush(checkNodes, item);} else {var pNode = this.getRealParent(conditionList, item, checkedList);if (pNode) {this.uniquePush(checkNodes, pNode);}}}var _tmpRoot = [];for (var ck of checkNodes) {var _tmp = this.findParentGroups(conditionList, ck.pid);if (_tmp) {this.uniquePush(_tmpRoot, _tmp);}}var allSelectCount = 0;var floorCount = [];for (var cn of checkNodes) {if (cn.groups) {allSelectCount += this.countItem(cn.groups);} else {allSelectCount++;}if (floorCount.indexOf(cn.floor) == -1) {floorCount.push(cn.floor);}}var rootGroup = this.findParentGroups(conditionList, checkNodes[0].pid);if (_tmpRoot.length > 1) {rootGroup = this.findParentGroups(conditionList, rootGroup.pid);allSelectCount = 0;for (var cn of _tmpRoot) {if (cn.groups) {allSelectCount += this.countItem(cn.groups);} else {allSelectCount++;}}}var rootArray = conditionList;if (rootGroup) {rootArray = rootGroup.groups;}var allCount = this.countItem(rootArray);var currentSelectCount = checkedList.length;if (allSelectCount != currentSelectCount || floorCount.length > 1) {this.$message({message: "不能交叉分組",type: "warning",duration: 1000,});return;}if (checkNodes.length == 1 || allCount == currentSelectCount) {this.$message({message: "無效分組",type: "warning",duration: 1000,});return;}var newCheckNode = JSON.parse(JSON.stringify(checkNodes));newCheckNode.sort(function (a, b) {return a.index - b.index;});var groupId = new Date().getTime();var newGroup = {groups: newCheckNode,id: groupId,index: newCheckNode[0].index,pid: newCheckNode[0].pid,floor: newCheckNode[0].floor,};var waitRemoveNode = [];for (var o of newCheckNode) {o.floor += 1;o.pid = groupId;if (!o.groups) {o.checked = false;}waitRemoveNode.push(o.id);}if (!rootGroup) {this.removeNode(conditionList, waitRemoveNode);conditionList.splice(newCheckNode[0].index - 1, 0, newGroup);} else {var _groups = this.findParentGroups(conditionList, rootGroup.id);this.removeNode(_groups.groups, waitRemoveNode);_groups.groups.splice(newCheckNode[0].index - 1, 0, newGroup);}parentThis.floor = this.refreshData(conditionList);},delGroup(groupId, conditionList, parentThis) {var parentGroups = this.findParentGroups(conditionList, groupId);var rootGroups = this.findParentGroups(conditionList, parentGroups.pid);var waitRemoveNode = [parentGroups.id];var newList = JSON.parse(JSON.stringify(parentGroups.groups));newList.forEach((o, index) => {o.pid = parentGroups.pid;o.floor = parentGroups.floor;o.checked = false;});if (!rootGroups) {this.removeNode(conditionList, waitRemoveNode);newList.forEach((o, index) => {conditionList.splice(parentGroups.index - 1 + index, 0, o);});} else {var _groups = this.findParentGroups(conditionList, rootGroups.id);this.removeNode(_groups.groups, waitRemoveNode);newList.forEach((o, index) => {_groups.groups.splice(parentGroups.index - 1 + index, 0, o);});}parentThis.floor = this.refreshData(conditionList);},getRealParent(allItems, item, checkedList) {var parentGroups = this.findParentGroups(allItems, item.pid);var ret = parentGroups;if (parentGroups) {var childCount = this.countItem(parentGroups.groups);var realChildCount = 0;for (var cl of checkedList) {if (cl.pid == parentGroups.id) {realChildCount++;} else {var pg = this.findParentGroups(allItems, cl.pid);if (pg) {if (pg.pid == parentGroups.id) {realChildCount++;} else {while (pg && pg.pid != parentGroups.id) {pg = this.findParentGroups(allItems, pg.pid);if (pg && pg.pid == parentGroups.id) {realChildCount++;}}}}}}if (childCount == realChildCount) {var _tmp = this.getRealParent(allItems, parentGroups, checkedList);if (_tmp) {ret = _tmp;}} else {ret = item;}}return ret;},reIndex(list, i, arr) {for (var index = 0; index < list.length; index++) {var o = list[index];if (arr.indexOf(i) == -1) {arr.push(i);}if (o.groups && o.groups.length > 0) {o.index = index + 1;o.floor = i;if (i == 1) {o.pid = -1;}this.reIndex(o.groups, i + 1, arr);} else {o.index = index + 1;o.floor = i;o.checked = false;if (i == 1) {o.pid = -1;}}}},drawLineGroup(list, currentFloor, retList) {for (var index = 0; index < list.length; index++) {var o = list[index];if (o.groups && o.groups.length > 0) {this.drawLineGroup(o.groups, currentFloor + 1, retList);} else {o.line = new Array(currentFloor - 1);if (retList.length == 0) {o.header = true;} else {o.header = false;}for (var _k = 0; _k < o.line.length; _k++) {o.line[_k] = { l: 2, p: -1 };}retList.push(o);}}},refreshData(list) {var floorCountArr = [];this.reIndex(list, 1, floorCountArr);var maxFloor = floorCountArr.length;var ret = new Array();this.drawLineGroup(list, 1, ret);for (var item of ret) {var parentGroup = this.findParentGroups(list, item.pid);if (item.pid != -1) {if (item.index == 1) {var node = { l: 4, p: parentGroup.id };item.line[item.line.length - 1] = node;} else if (item.index == parentGroup.groups.length) {var node = { l: 5, p: -1 };item.line[item.line.length - 1] = node;}}if (parentGroup) {var parentIndex = parentGroup.index;var parentLength = parentGroup.groups.length;var i = 2;var currentParentGroup = this.findParentGroups(list, parentGroup.pid);while (currentParentGroup) {if (i != 2) {parentGroup = JSON.parse(JSON.stringify(currentParentGroup));currentParentGroup = this.findParentGroups(list, parentGroup.pid);}if (currentParentGroup) {if (parentGroup.index == 1 &&item.index == 1 &&parentIndex == 1) {var node = { l: 4, p: currentParentGroup.id };item.line[item.line.length - i] = node;} else if (parentGroup.index == currentParentGroup.groups.length &&item.index == parentLength) {item.line[item.line.length - i] = { l: 5, p: -1 };} else {break;}i++;}}}}return maxFloor;},countItem(list, i) {var sum = i || 0;for (var index = 0; index < list.length; index++) {var o = list[index];if (o.groups && o.groups.length > 0) {sum += this.countItem(o.groups, i);} else {sum++;}}return sum;},uniquePush(arr, item) {var exist = false;for (var o of arr) {if (o.id == item.id) {exist = true;}}if (!exist) {arr.push(item);}},randomHexColor() {// return '#' + ('00000' + (Math.random() * 0x1000000 << 0).toString(16)).substr(-6);return this.randomColor(alpha);},randomColor(alpha) {alpha =alpha == undefined ? ((Math.random() * 10) / 10).toFixed(1) : alpha;alpha = Number(alpha);if (isNaN(alpha)) alpha = 1;var col = "rgba(";for (var i = 0; i < 3; i++) {col += parseInt(Math.random() * 256) + ",";}col += alpha + ")";return col;},getFloorColor(floor) {return this.borderColor[floor - 1];},},created() {if (typeof this.conditionList[0].field == "string" &&typeof this.conditionList[0].header == "undefined") {this.conditionList[0].header = true;}this.$nextTick(() => {});},
};
</script><style type="text/css">
/* :root 是CSS中的一個偽類選擇器,它代表文檔的根元素。在HTML中,根元素通常是<html>標簽。使用:root選擇器允許開發者為整個文檔定義全局樣式變量
--borderWidth:這是一個自定義的CSS變量
使用var(--variableName)語法進行引用 */:root {--borderWidth: 1px;--borderColor: rgba(158, 158, 158, 1);
}table {border-collapse: collapse;
}.marginClass {margin-bottom: 10px;
}.condition-header {font-weight: 600;display: flex;flex-direction: row;
}.group-button {margin-left: 47px;display: flex;flex-direction: row;align-items: center;
}.group-left {border-left: var(--borderWidth) solid var(--borderColor);
}
.group-top-left {border-top: var(--borderWidth) solid var(--borderColor);border-left: var(--borderWidth) solid var(--borderColor);
}
.group-bottom-left {border-bottom: var(--borderWidth) solid var(--borderColor);border-left: var(--borderWidth) solid var(--borderColor);
}
</style>
父組件
<template><!-- :parentData="this"傳遞父組件實例,子組件使用屬性或方法時this.parentData.屬性/方法名 -->
<conditionGroup:floor="floor":conditionList="conditionList":parentData="this":key-list="keyOptions":condition-map="conditionOptions":val-list="valueOptions"></conditionGroup>
</template><sctipt>
export default {data() {conditionList: [Object.assign({}, condition)], // 默認頁面顯示一條空規則floor: 1, // 樹深度keyOptions: [], // 字段選項conditionOptions: {}, // 特殊運算符(像下拉框的只有 等于 運算符等)valueOptions: {}, //下拉框類型的數據},// 一些關鍵方法methods: {// 遞歸找結構深度getFloorth(groups) {let deepestFloor = 0;let deepestGroup = null;function searchGroup(group) {// 找到比當前deepestFloor值大,則重新賦值deepestFloor,并將當前對象記錄if (group.floor > deepestFloor) {deepestFloor = group.floor;deepestGroup = group;}if (group.groups && group.groups.length > 0) {group.groups.forEach(searchGroup);}}groups.forEach(searchGroup);return deepestGroup ? deepestGroup.floor : 1;},// 提交時校驗規則是否有未填/選項traverse(data) {for (let item of data) {// 這里需要判空的參數field、condition、value,如果有為空則提示,不可保存if (!item.groups &&(!item?.field || !item?.condition || !item?.value)) {this.$message.warning("請完善規則條件配置");// 當校驗失敗時拋出錯誤,`中斷當前執行的代碼塊`,并被上層catch語句捕獲throw new Error("字段驗證失敗"); } else if (item.groups) {this.traverse(item.groups);}}return true;},// 根據其他下拉框,查詢動態字段項selectChange(obj) {let { name, val } = obj;if (name === "equipType") {this.keyOptions = [];this.valueOptions = {};this.conditionOptions = {};let keyOptionsDict = val + "_column"; // keyOptionsDict 為val和_column拼接的字典名稱去過濾對應字典項let options = this.$options.filters["dictOption"]({dictName: keyOptionsDict,});// 有過濾到字段項,給字段項字段push數據(字段key和val)if (options.length) {options.forEach((item) => {// item.dictValue用#拼接的三個參數,如heat_status_code#select#heat_status(第一個為規則字段鍵、第二個為值類型、第三個為字典名)let parts = item.dictValue.split("#");this.keyOptions.push({key: item.dictLabel,val: parts[0],});if (parts[1] === "select") {// 如果是select下拉框,則拼接下拉框值數據項(我們這塊都是匹配的字典查詢,可拓展為接口查詢)this.valueOptions[parts[0]] = {dom: parts[1],data: this.$options.filters["dictOption"]({dictName: parts[2],}),};// 若是下拉框,處理運算符this.conditionOptions[parts[0]] = [{ key: "等于", val: "eq" }];} else {// 其他字段直接給dom屬性this.valueOptions[parts[0]] = { dom: parts[1] };}});} else {this.conditionList = [Object.assign({}, condition)];this.keyOptions = [];this.conditionOptions = {};}}},// 若需要前端組裝給后端可使用下面方法(我這里是直接給的JSON,前端不進行組裝)generateConditionString(conditionList) {let result = "";if (conditionList.length === 0) return result;const generateSubConditions = (conditions) => {return conditions.map((condition, index) => {if (condition.groups && condition.groups.length > 0) {let connectSym = condition.groups[0].operate;return index === 0? `(${generateSubConditions(condition.groups)})`: ` ${connectSym} (${generateSubConditions(condition.groups)})`;}let value = condition.value;if (typeof value === "string") {if (condition.condition === "like" ||condition.condition === "notLike") {value = `'%${value.replace(/'/g, "")}%'`;} else {value = `'${value.replace(/'/g, "''")}'`;}}if (!condition?.field ||!condition?.condition ||!condition?.value) {this.$message.warning("請完善規則條件配置");return result;}const conditionString = `${condition.field} ${this.operators[condition.condition]} ${value}`;const logicOperator = ` ${condition.operate} `;return `${index === 0 ? "" : logicOperator}${conditionString}`;}).join("");};result = generateSubConditions(conditionList);this.querystring = result;return result;},}
}
</script>
參考文章