9月工作总结
9月相对来说是一个比较繁忙的一周,这周我主要做的事情的是编写了:专家库,以及固定资产接口,以及综合查询接口。
这几个接口相比较于我以前写的接口,难度都有了比较大的增加,由于我对前端不是很熟,所以在写完接口后,对前端联调产生了比较大的麻烦。
学习到的知识
综合查询
综合查询主要用到的关键字是: union all.
这个关键字也比较的特殊,因为它要求用它关联的两个查询结果必须满足①字段名称相同②字段顺序相同。为了满足这个条件,就需要使用as来改变字段的名称。
<select id="selectListPage" resultType="com.yhkg.oa.model.ReportBackListModel" parameterType="com.founder.ssm.core.common.SearchCondition">
select* from (SELECT
FOAI.APPLICANT_DEPARTMENT,
FOAI.APPLICANT_NAME,
FOAI.APPLY_TIME,
FOAV.APPLY_NO,
FOAV.VACATION_START_TIME AS startTime,
FOAV.VACATION_END_TIME AS endTime,
FOAV.FUNCTION_TYPE,
FOAV.CREATE_DT,
FORB.RETURN_DT,
FORB.PLAN_RETURN_DT,
FORB.TIME_LENGTH,
FORB.REASON,
FORB.ATTACHMENT,
BCTC.CODE_NAME AS applyStatus
FROM FOUNDER_OA_APPLY_VACATION FOAV
LEFT JOIN FOUNDER_OA_APPLY_INFO FOAI ON FOAV.APPLY_NO = FOAI.APPLY_NO
LEFT JOIN FOUNDER_OA_REPORT_BACK FORB ON FORB.KEY_APPLY_NO = FOAI.APPLY_NO
LEFT JOIN BASE.COMM_T_CODE BCTC ON BCTC.CODE_VALUE = FOAI.APPLY_STATUS
<where>
BCTC.PARENT_ID = '302'
<if test="searchBean.startTime!=null and searchBean.startTime != ''">
AND FOAI.APPLY_TIME <![CDATA[>=]]> TO_DATE(#{searchBean.startTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.endTime!=null and searchBean.endTime != ''">
AND FOAI.APPLY_TIME <![CDATA[<=]]> TO_DATE(#{searchBean.endTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.applyNo != null and searchBean.applyNo != ''">
AND FOAV.APPLY_NO = #{searchBean.applyNo,jdbcType=VARCHAR}
</if>
<if test="searchBean.keyWord != null and searchBean.keyWord != ''">
AND
(
FOAI.APPLY_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLY_NO LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_DEPARTMENT LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
)
</if>
</where>
union all
SELECT
FOAI.APPLICANT_DEPARTMENT,
FOAI.APPLICANT_NAME,
FOAI.APPLY_TIME,
FOOB.APPLY_NO,
FOOB.BUSINESS_START_TIME AS startTime,
FOOB.BUSINESS_END_TIME AS endTime,
FOOB.FUNCTION_TYPE,
FOOB.CREATE_DT,
FORB.RETURN_DT,
FORB.PLAN_RETURN_DT,
FORB.TIME_LENGTH,
FORB.REASON,
FORB.ATTACHMENT,
BCTC.CODE_NAME AS applyStatus
FROM FOUNDER_OA_ON_BUSINESS FOOB
LEFT JOIN FOUNDER_OA_APPLY_INFO FOAI ON FOOB.APPLY_NO = FOAI.APPLY_NO
LEFT JOIN FOUNDER_OA_REPORT_BACK FORB ON FORB.KEY_APPLY_NO = FOAI.APPLY_NO
LEFT JOIN BASE.COMM_T_CODE BCTC ON BCTC.CODE_VALUE = FOAI.APPLY_STATUS
<where>
BCTC.PARENT_ID = '302'
<if test="searchBean.startTime!=null and searchBean.startTime != ''">
AND FOAI.APPLY_TIME <![CDATA[>=]]> TO_DATE(#{searchBean.startTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.endTime!=null and searchBean.endTime != ''">
AND FOAI.APPLY_TIME <![CDATA[<=]]> TO_DATE(#{searchBean.endTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.applyNo != null and searchBean.applyNo != ''">
AND FOOB.APPLY_NO = #{searchBean.applyNo,jdbcType=VARCHAR}
</if>
<if test="searchBean.keyWord != null and searchBean.keyWord != ''">
AND
(
FOAI.APPLY_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLY_NO LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_DEPARTMENT LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
)
</if>
</where>
union all
SELECT
FOAI.APPLICANT_DEPARTMENT,
FOAI.APPLICANT_NAME,
FOAI.APPLY_TIME,
FOAO.APPLY_NO,
FOAO.OUT_START_TIME AS startTime,
FOAO.OUT_END_TIME AS endTime,
FOAO.FUNCTION_TYPE,
FOAO.CREATE_DT,
FORB.RETURN_DT,
FORB.PLAN_RETURN_DT,
FORB.TIME_LENGTH,
FORB.REASON,
FORB.ATTACHMENT,
BCTC.CODE_NAME AS applyStatus
FROM FOUNDER_OA_APPLY_OUT FOAO
LEFT JOIN FOUNDER_OA_APPLY_INFO FOAI ON FOAO.APPLY_NO = FOAI.APPLY_NO
LEFT JOIN FOUNDER_OA_REPORT_BACK FORB ON FORB.KEY_APPLY_NO = FOAI.APPLY_NO
LEFT JOIN BASE.COMM_T_CODE BCTC ON BCTC.CODE_VALUE = FOAI.APPLY_STATUS
<where>
BCTC.PARENT_ID = '302'
<if test="searchBean.startTime!=null and searchBean.startTime != ''">
AND FOAI.APPLY_TIME <![CDATA[>=]]> TO_DATE(#{searchBean.startTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.endTime!=null and searchBean.endTime != ''">
AND FOAI.APPLY_TIME <![CDATA[<=]]> TO_DATE(#{searchBean.endTime},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="searchBean.applyNo != null and searchBean.applyNo != ''">
AND FOAO.APPLY_NO = #{searchBean.applyNo,jdbcType=VARCHAR}
</if>
<if test="searchBean.keyWord != null and searchBean.keyWord != ''">
AND
(
FOAI.APPLY_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLY_NO LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_DEPARTMENT LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
OR FOAI.APPLICANT_NAME LIKE CONCAT(CONCAT ('%',#{searchBean.keyWord,jdbcType=VARCHAR}),'%')
)
</if>
</where>)
order by CREATE_DT DESC
</select>
专家库
专家库主要遇到的麻烦就是:专家的特长领域是可以多选的,但是数据库中的字段却只有一个,所以我们存的时候就需要以一个逗号隔开的字符串进行存储。
我们首先在mapper.xml里面写了一个方法,查出了所有的codeValue和codeName。
<select id="selectCodeName" parameterType="String" resultType="com.jsjc.ei.model.CommTCodeModel">
SELECT
BCTC.CODE_VALUE,
BCTC.CODE_NAME
FROM BASE.COMM_T_CODE BCTC
<where>
BCTC.PARENT_ID='9024'
</where>
</select>
然后我在serviceImple里面是这样处理的
List<CommTCodeModel> codeModels = expertService.selectList("selectCodeName","");
List<ExpertListModel> modelList =new ArrayList<>();
if(codeModels!=null&&codeModels.size()>0){
for (ExpertListModel model:listByPage.getList()) {
ExpertListModel em = new ExpertListModel();
BeanUtils.copyProperties(model,em);
String str = model.getLabel();
StringBuilder sb = new StringBuilder("");
if(StringUtils.isNotBlank(str)){
String[] strArr = str.split(",");
for (CommTCodeModel cm :codeModels) {
if(Arrays.asList(strArr).contains(cm.getCodeValue())){
sb.append(cm.getCodeName()).append(",");
}
}
}
em.setLabelName(sb.toString());
modelList.add(em);
}
除了这个比较烦之外,我还遇到了一个比较麻烦的问题码值,其实也是比较简单的问题,但是我还是记录一下。
首先必须关联码值这张表,然后我需要在上面加上BCTC.CODE_NAME AS labelName然后我需要在那个modellist里面也要加上,vo里面也要加上。
固定资产
固定资产是我做到现在为止比较操蛋的一个接口,首先它的表比较的多,数据流也非常的复杂。
①insert和update中对应的字符位置必须相一致,不然会出现莫名其妙的例如主键不能为空的错误。
②就是在一个model里面封装另外一个model的list对象。
<resultMap type="com.yhkg.oa.model.AssetsStockListModel" id="AssetsStockAddResultMap">
<result column="STOCK_ID" property="stockId" jdbcType="VARCHAR" />
<result column="STOCK_TYPE" property="stockType" jdbcType="VARCHAR" />
<result column="PURCHASE_ID" property="purchaseId" jdbcType="VARCHAR" />
<result column="ITEM_ID" property="itemId" jdbcType="VARCHAR" />
<result column="MODEL" property="model" jdbcType="VARCHAR" />
<result column="AMOUNT" property="amount" jdbcType="NUMERIC" />
<result column="SCRAP_DT" property="scrapDt" jdbcType="TIMESTAMP" />
<result column="REMARK" property="remark" jdbcType="VARCHAR" />
<result column="CREATE_BY" property="createBy" jdbcType="VARCHAR" />
<result column="CREATE_DT" property="createDt" jdbcType="TIMESTAMP" />
<result column="UPDATE_BY" property="updateBy" jdbcType="VARCHAR" />
<result column="UPDATE_DT" property="updateDt" jdbcType="TIMESTAMP" />
<collection property="numberModels" ofType="com.yhkg.oa.model.NumberModel">
<result column="numberId" property="numberId" jdbcType="VARCHAR" />
<result column="goodsnumber" property="goodsnumber" jdbcType="NUMERIC" />
</collection>
</resultMap>
这里面需要注意的点就是:①集合对象里面的result里面最好将这张表的主键加上②FON.GOODSNUMBER as goodsnumber,
FON.NUMBER_ID as numberId,
上面集合中是这样写的,那么下面的查询中应该和上面的名称相同。
③另外遇到的一个比较坑爹的问题就是,一个库存我需要对应多个编号。话不多说,先上代码。
AssetsStockModel assetsStockModel = new AssetsStockModel();
assetsStockModel.setStockId(stockId);
List<AssetsStockListModel> assetsStockModels = assetsStockService.selectList(assetsStockModel);
AssetsStockListModel model = new AssetsStockListModel();
if (assetsStockModels != null && assetsStockModels.size() > 0) {
AssetsStockVO formData = new AssetsStockVO();
model = assetsStockModels.get(0);
BeanUtils.copyProperties(model, formData);
List<NumberModel> numberModels = model.getNumberModels();
if(numberModels!=null&&numberModels.size()>0){
StringBuilder sb = new StringBuilder("");
for (NumberModel n:numberModels) {
sb.append(n.getGoodsnumber()).append(",");
}
String t = sb.toString();
String n= t.substring(0, t.length()-1);
formData.setGoodsnumber(n);
}
里面有一个注意点就是,这样传进去,最后面会多一个逗号,所以我最后需要将这个都要截取掉。
④另外这个接口比较操蛋的一个地方就是我在如何进行关联的时候纠结了很久,后来我还是决定使用表的主键,因为表的主键是唯一的,这样不会重复,就不会查出来很多的数据。
⑤关联一张表就是查出这张表的list,当然我们需要加入一些筛选的条件。
总结
这两周我学到的东西还是很多的,当然这个中秋我要学习前端的知识了,这样最起码让我在和前端联调的时候知道我应该传什么参数给前端。