入库清单中,有些耗材已经被申请领用或已经出库,所以不可以再进行删除操作,
it_record 耗材出入库记录表,字段LOT_NUMBER表示资产入库唯一编号,
it_detail 耗材详情表,字段ATTRIBUTE1(null表示没有被消耗,1表示占用,2表示出库),
首先SQL语句中将可删除与不可删除的清单分别查询,并添加DELETABLE字段,DELETABLE='1'表示可以删除,DELETABLE='0'表示不可删除,最后将两个清单进行union all,
SQL语句如下:
SELECT
NAME,
LAST_UPDATED_BY,
LAST_UPDATED_DATE,
LOCATION_NAME,
LOT_NUMBER,
ITEM_DESCRIPTION,
ITEM_BRAND_D || ITEM_MODEL_D || ITEM_DESCRIPTION_D ITEM_DETAIL,
ITEM_TYPE,
ITEM_BRAND,
ITEM_MODEL,
ATTRIBUTE4,
NUM,
UNIT_PRICE,
TOTAL_PRICE,
TRANS_TYPE,
SOURCE_ID,
DELETABLE
FROM
(
SELECT
b. NAME,
LAST_UPDATED_BY,
CASE
WHEN ITEM_BRAND IS NOT NULL THEN
'品牌:' || ITEM_BRAND || '; '
ELSE
'品牌:' || '——' || '; '
END AS ITEM_BRAND_D,
CASE
WHEN ITEM_MODEL IS NOT NULL THEN
'规格:' || ITEM_MODEL || '; '
ELSE
'规格:' || '——' || '; '
END AS ITEM_MODEL_D,
CASE
WHEN ITEM_DESCRIPTION IS NOT NULL THEN
'型号:' || ITEM_DESCRIPTION || ';
'
ELSE
'型号:' || '——' || '; '
END AS ITEM_DESCRIPTION_D,
LOT_NUMBER,
LAST_UPDATED_DATE,
LOCATION_NAME,
ITEM_TYPE,
ITEM_BRAND,
ITEM_MODEL,
ATTRIBUTE4,
NUM,
UNIT_PRICE,
TOTAL_PRICE,
TRANS_TYPE,
SOURCE_ID,
ITEM_DESCRIPTION,
DELETABLE
FROM
(
SELECT
IT_R.LAST_UPDATED_BY,
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_R.LOCATION_NAME,
TO_CHAR (IT_R.UNIT_PRICE) UNIT_PRICE,
'1' DELETABLE,
listagg (IT_R.LOT_NUMBER, ',') WITHIN GROUP (ORDER BY IT_R.LOT_NUMBER) LOT_NUMBER,
TO_CHAR (COUNT(1)) NUM,
TO_CHAR (
COUNT (1) * TO_NUMBER (IT_R.UNIT_PRICE)
) TOTAL_PRICE
FROM
IT_RECORD IT_R LEFT JOIN IT_DETAIL IT_D ON IT_R.LOT_NUMBER = IT_D.LOT_NUMBER
WHERE
IT_R.TRANS_TYPE = '0' AND IT_D.ATTRIBUTE1 IS NULL
GROUP BY
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.UNIT_PRICE,
IT_R.LAST_UPDATED_BY,
IT_R.LOCATION_NAME,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_D.ATTRIBUTE1
UNION ALL
SELECT
IT_R.LAST_UPDATED_BY,
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_R.LOCATION_NAME,
TO_CHAR (IT_R.UNIT_PRICE) UNIT_PRICE,
'0' DELETABLE,
listagg (IT_R.LOT_NUMBER, ',') WITHIN GROUP (ORDER BY IT_R.LOT_NUMBER) LOT_NUMBER,
TO_CHAR (COUNT(1)) NUM,
TO_CHAR (
COUNT (1) * TO_NUMBER (IT_R.UNIT_PRICE)
) TOTAL_PRICE
FROM
IT_RECORD IT_R LEFT JOIN IT_DETAIL IT_D ON IT_R.LOT_NUMBER = IT_D.LOT_NUMBER
WHERE
IT_R.TRANS_TYPE = '0' AND IT_D.ATTRIBUTE1 IS NOT NULL
GROUP BY
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.UNIT_PRICE,
IT_R.LAST_UPDATED_BY,
IT_R.LOCATION_NAME,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_D.ATTRIBUTE1
) A
LEFT JOIN OA_STAFF b ON A .LAST_UPDATED_BY = b.STAFF_ID
)
WHERE
1 = 1
ORDER BY
LAST_UPDATED_DATE DESC,ITEM_DETAIL ASC
转换为mybatis文件:
/wxapp/src/main/resources/mybatis1/assetmgmt/consumable/RecordMapper.xml
<select id="inventorylistPage" parameterType="page"
resultType="pd">
SELECT
NAME,
LAST_UPDATED_BY,
LAST_UPDATED_DATE,
LOCATION_NAME,
LOT_NUMBER,
ITEM_DESCRIPTION,
ITEM_BRAND_D || ITEM_MODEL_D || ITEM_DESCRIPTION_D ITEM_DETAIL,
ITEM_TYPE,
ITEM_BRAND,
ITEM_MODEL,
ATTRIBUTE4,
NUM,
UNIT_PRICE,
TOTAL_PRICE,
TRANS_TYPE,
SOURCE_ID,
DELETABLE
FROM
(
SELECT
b. NAME,
LAST_UPDATED_BY,
CASE
WHEN ITEM_BRAND IS NOT NULL THEN
'品牌:' || ITEM_BRAND || '; '
ELSE
'品牌:' || '——' || '; '
END AS ITEM_BRAND_D,
CASE
WHEN ITEM_MODEL IS NOT NULL THEN
'规格:' || ITEM_MODEL || '; '
ELSE
'规格:' || '——' || '; '
END AS ITEM_MODEL_D,
CASE
WHEN ITEM_DESCRIPTION IS NOT NULL THEN
'型号:' || ITEM_DESCRIPTION || ';
'
ELSE
'型号:' || '——' || '; '
END AS ITEM_DESCRIPTION_D,
LOT_NUMBER,
LAST_UPDATED_DATE,
LOCATION_NAME,
ITEM_TYPE,
ITEM_BRAND,
ITEM_MODEL,
ATTRIBUTE4,
NUM,
UNIT_PRICE,
TOTAL_PRICE,
TRANS_TYPE,
SOURCE_ID,
ITEM_DESCRIPTION,
DELETABLE
FROM
(
SELECT
IT_R.LAST_UPDATED_BY,
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_R.LOCATION_NAME,
TO_CHAR (IT_R.UNIT_PRICE) UNIT_PRICE,
'1' DELETABLE,
listagg (IT_R.LOT_NUMBER, ',') WITHIN GROUP (ORDER BY IT_R.LOT_NUMBER) LOT_NUMBER,
TO_CHAR (COUNT(1)) NUM,
TO_CHAR (
COUNT (1) * TO_NUMBER (IT_R.UNIT_PRICE)
) TOTAL_PRICE
FROM
IT_RECORD IT_R LEFT JOIN IT_DETAIL IT_D ON IT_R.LOT_NUMBER = IT_D.LOT_NUMBER
WHERE
IT_R.TRANS_TYPE = '0' AND IT_D.ATTRIBUTE1 IS NULL
GROUP BY
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.UNIT_PRICE,
IT_R.LAST_UPDATED_BY,
IT_R.LOCATION_NAME,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_D.ATTRIBUTE1
UNION ALL
SELECT
IT_R.LAST_UPDATED_BY,
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_R.LOCATION_NAME,
TO_CHAR (IT_R.UNIT_PRICE) UNIT_PRICE,
'0' DELETABLE,
listagg (IT_R.LOT_NUMBER, ',') WITHIN GROUP (ORDER BY IT_R.LOT_NUMBER) LOT_NUMBER,
TO_CHAR (COUNT(1)) NUM,
TO_CHAR (
COUNT (1) * TO_NUMBER (IT_R.UNIT_PRICE)
) TOTAL_PRICE
FROM
IT_RECORD IT_R LEFT JOIN IT_DETAIL IT_D ON IT_R.LOT_NUMBER = IT_D.LOT_NUMBER
WHERE
IT_R.TRANS_TYPE = '0' AND IT_D.ATTRIBUTE1 IS NOT NULL
GROUP BY
IT_R.LAST_UPDATED_DATE,
IT_R.ITEM_TYPE,
IT_R.ITEM_BRAND,
IT_R.ITEM_MODEL,
IT_R.ATTRIBUTE4,
IT_R.UNIT_PRICE,
IT_R.LAST_UPDATED_BY,
IT_R.LOCATION_NAME,
IT_R.TRANS_TYPE,
IT_R.SOURCE_ID,
IT_R.ITEM_DESCRIPTION,
IT_D.ATTRIBUTE1
) A
LEFT JOIN OA_STAFF b ON A .LAST_UPDATED_BY = b.STAFF_ID
)
WHERE
1 = 1
<if test="pd.keywords!= null and pd.keywords != ''"><!-- 关键词检索 -->
and
(
NAME LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
OR
LAST_UPDATED_BY LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
OR
LOCATION_NAME LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
OR
ITEM_TYPE LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
OR ITEM_BRAND
LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
OR ITEM_MODEL LIKE
CONCAT(CONCAT('%', #{pd.keywords}),'%')
)
</if>
<if test="pd.SOURCE_ID !=null and pd.SOURCE_ID !=''">
and SOURCE_ID = #{pd.SOURCE_ID}
</if>
<if test="pd.lastStart !=null and pd.lastStart !=''">
and LAST_UPDATED_DATE >= #{pd.lastStart}
</if>
<if test="pd.lastEnd !=null and pd.lastEnd !=''">
and LAST_UPDATED_DATE <= #{pd.lastEnd}
</if>
order by LAST_UPDATED_DATE DESC,ITEM_DETAIL ASC
</select>
在JSP文件中,提取DELETABLE进行判定,如果是不可删除的记录,则文字显示为灰色,并且此记录不可被选择,
/wxapp/src/main/webapp/WEB-INF/jsp/assetmgmt/consumable/record/inventory_list.jsp
<c:if test="${var.DELETABLE!=1 }">
<tr style="color:#A6A6A6">
</c:if>
<c:if test="${var.DELETABLE!=1 }">
<td class='center'>
<label class="pos-rel">
<input onclick ="return false" type='checkbox' name='ids' value="${var.LOT_NUMBER}" class="ace" />
<span class="lbl"></span>
</label>
</td>
</c:if>
inventory_list.jsp代码如下:
<table>
<!-- 省略表头内容 -->
<tbody>
<!-- 开始循环 -->
<c:choose>
<c:when test="${not empty varList}">
<%-- <c:if test="${QX.cha == 1 }"> --%>
<c:forEach items="${varList}" var="var" varStatus="vs">
<c:if test="${var.DELETABLE==1 }">
<tr>
</c:if>
<c:if test="${var.DELETABLE!=1 }">
<tr style="color:#A6A6A6">
</c:if>
<c:if test="${var.DELETABLE==1 }">
<td class='center'>
<label class="pos-rel">
<input type='checkbox' name='ids' value="${var.LOT_NUMBER}" class="ace" />
<span class="lbl"></span>
</label>
</td>
</c:if>
<c:if test="${var.DELETABLE!=1 }">
<td class='center'>
<label class="pos-rel">
<input onclick ="return false" type='checkbox' name='ids' value="${var.LOT_NUMBER}" class="ace" />
<span class="lbl"></span>
</label>
</td>
</c:if>
<td class='center' style="width: 30px;">${vs.index+1}</td>
<td class='center'>${var.LOCATION_NAME}</td>
<td class='center'>${var.ITEM_TYPE}</td>
<td class='center'>${var.ITEM_DETAIL}</td>
<td class='center'>${var.ATTRIBUTE4}</td>
<td class='center'>${var.NUM}</td>
<td class='center'>${var.UNIT_PRICE}</td>
<td class='center'>${var.TOTAL_PRICE}</td>
<td class='center'>${var.LAST_UPDATED_BY}</td>
<td class='center'>${var.NAME}</td>
<td class='center'>${var.LAST_UPDATED_DATE}</td>
<%-- <td class='center'><c:if
test="${var.TRANS_TYPE == '1' }">出库</c:if> <c:if
test="${var.TRANS_TYPE == '4' }">盘亏</c:if> <c:if
test="${var.TRANS_TYPE == '0' }">入库</c:if></td> --%>
<td class='center' id='source${vs.index}'><c:if
test="${var.SOURCE_ID == '1'}">ERP</c:if><c:if
test="${var.SOURCE_ID == '2'}">捐赠</c:if><c:if
test="${var.SOURCE_ID == '3'}">盘盈</c:if><c:if
test="${var.SOURCE_ID == '5'}">手工录入</c:if></td>
</tr>
</c:forEach>
<%-- </c:if> --%>
<c:if test="${QX.cha == 0 }">
<tr>
<td colspan="100" class="center">您无权查看</td>
</tr>
</c:if>
</c:when>
<c:otherwise>
<tr class="main_info">
<td colspan="100" class="center">没有相关数据</td>
</tr>
</c:otherwise>
</c:choose>
</tbody>
</table>
效果如图