1.统一css样式调整

.jm-query-form .ivu-btn-primary{
    background-color: #014df1;
    border-color: #014df1;
}
.ivu-collapse-header {
    display: none;
}
.ty-bar-container {
    float: right;
    background: transparent;
}
.ty-bar-container .jm-sheet-toolbar-divider {
    margin: 12px 0px 0;
}

2.自定义下拉框，查询栏数据联动

前端代码

//获取公司名称
$http.metaGet('http://192.168.80.6:8088/jimu/linkage/get/company').then(res=>{
    let options =  res.data;
    this.updateSelectOptions('wd', 'uid', options)
})
//获取公司下的仓库名称
this.onSearchFormChange('wd', 'uid', (value)=>{
    let params = {params: {company_id: value}}
    $http.metaGet('http://192.168.80.6:8088/jimu/linkage/get/warehouse', params ).then(res=>{
        let options =  res.data;
        this.updateSelectOptions('wd', 'warehouse_id', options)
    })
})

• 获取公司名称时需注意this.updateSelectOptions('wd', 'uid', options)
  • wd：代表数据源编码
  • uid：字段名
• 获取公司下的仓库名称时需注意let params = {params: {company_id: value}}
  • company_id：代表公司id，为java固定参数

后端代码

controller层

@RestController
@RequestMapping("/jimu/linkage")
public class LinkAgeController {
    @Autowired
    private ILinkAgeService iLinkAgeService;
    @GetMapping("/get/company")
    public List<LinkAge> getCompanyParameters() {
        return iLinkAgeService.getCompanyParameters();
    }
    @GetMapping("/get/warehouse")
    public List<LinkAge> getWarehouseParameters(@RequestParam(name = "company_id", required = false) String companyId) {
        return iLinkAgeService.getWarehouseParameters(companyId);
    }
}

service层 只做了映射关系，没有业务代码，略过

mapper层

<mapper namespace="com.ruoyi.report.mapper.LinkAgeMapper">
    <select id="getCompanyParameters" parameterType="String" resultType="com.ruoyi.report.domain.LinkAge">
        select id as value, company_name as text
        from hw_company_list
    </select>
    <select id="getWarehouseParameters" parameterType="String" resultType="com.ruoyi.report.domain.LinkAge">
        select t1.warehouse_id as value, t1.new_name as text
        from wh_warehouse t1
        left join hw_company_list t2
        on t1.company_id = t2.id
        where 1=1
        <if test="companyId != null">
            and t1.company_id = #{companyId}
        </if>
    </select>
</mapper>

3.获取当月1号以及今天日期，格式为”YYYY-MM-DD”

// 获取当天日期
var today = new Date();
var todaysDate = today.getFullYear()+'-'+(today.getMonth()+1)+'-'+today.getDate();
// 获取本月1号
var firstDay = new Date();
firstDay.setDate(1);
var firstDate = firstDay.getFullYear()+'-'+(firstDay.getMonth()+1)+'-'+firstDay.getDate();
if ( (today.getMonth()+1) <10 ){
    todaysDate = today.getFullYear()+'-0'+(today.getMonth()+1)+'-'+today.getDate();
    firstDate = firstDay.getFullYear()+'-0'+(firstDay.getMonth()+1)+'-0'+firstDay.getDate();
}
if(today.getDate() <10 ){
    todaysDate = today.getFullYear()+'-'+(today.getMonth()+1)+'-0'+today.getDate();
}

• firstDate：当月1号日期
• todaysDate：今天日期

4.默认打开显示查询栏

function init(){
    this.queryPanel = '1';
}

5.路径编码格式统一

if (!!cs__create_time_begin) {
    cs__create_time_begin = decodeURI(cs__create_time_begin);
}

6.获取路径传参（截取方式）

//日期开始时间
var cs__create_time_begin= firstDate;
//日期结束时间
var cs__create_time_end= todaysDate;
let query = window.location.search.substring(1);
let vars = query.split("&");
for (let i=0;i<vars.length;i++) {
    let pair = vars[i].split("=");
    if(pair[0] == 'cs__create_time_begin'){
        if(pair[1] != '' && pair[1] != '%20' && pair[1] != ' ' && pair[1] != null && pair[1] != 'null'){
            cs__create_time_begin= pair[1];
        }
    }
    if(pair[0] == 'cs__create_time_end'){
        if(pair[1] != '' && pair[1] != '%20' && pair[1] != ' ' && pair[1] != null && pair[1] != 'null'){
            cs__create_time_end = pair[1];
        }
    }
}

7.查询栏赋值方式

this.updateSearchFormValue('cs', 'create_time', cs__create_time_begin  + '|' +cs__create_time_end);

8.SQL查询语句中常用函数、方式

1.时间戳转换

FROM_UNIXTIME(t1.deduction_time) as deduction_time

2.SQL条件判空

<#if isNotEmpty(uid)>
    and t1.uid = ${uid}
</#if>

3.SQL时间戳转换日期并格式化’YYYY-MM-DD’

DATE_FORMAT(FROM_UNIXTIME(t1.create_time), '%Y-%m-%d')

4.SQL字段判空选择处理

IFNULL(t5.sku,t7.sku),IFNULL(t4.sku,t7.sku))

5.数据库字段存储JSON格式数据，如何从中获取key:value形式数据值？

CONVERT(JSON_EXTRACT(t1.deduction_detail, '$.volume'), DECIMAL(10, 2)) AS t_volume

• t1.deduction_detail：字段
• ‘$.volume’：字段中的key等于volume

6.拼接数据库字段，展示所需要的效果

CONCAT( store_name, '(', country_zh, ')' ) AS store_name

7.判断展示

(CASE r.paymethod
    WHEN '1' THEN "手动充值"
    WHEN '2' THEN "支付宝"
    WHEN '3' THEN "微信"
    WHEN '4' THEN "扣款"
    WHEN '5' THEN "退款"
    WHEN '6' THEN "银行卡"
    WHEN '7' THEN "其他"
    WHEN '8' THEN "信用充值"
    WHEN '9' THEN "现金"
ELSE "其他" END) paymethod,
IF(w.remark like '%<img%', NULL, w.remark) AS remark, '充值成功' `status`

8.如果遇到需要展示全部数据时，可使用UNION ALL进行拼接SQL

SELECT d.company_name, d.uid, d.storage_fee, d.order_processing_fee, d.order_num, d.listing_fee, 
        d.labeling_fee, d.total_recharge, d.evaluation_cost, d.deduction_application, d.packaging_fee,
        d.after_sale_fee, d.after_sales_freight, d.create_time
FROM (SELECT a.company_name as company_name, a.id AS uid, 
             sum(if(a.fee_type = '1', a.total, 0)) as storage_fee,
             sum(if(a.fee_type = '7', a.total, 0)) as order_processing_fee,
--              count(a.fee_type = '7') as order_num,
                            sum(order_number) as order_num,
             sum(if(a.fee_type = '4', a.total, 0)) as listing_fee,
             sum(if(a.fee_type = '28', a.total, 0)) as labeling_fee,
             sum(if(a.fee_type = '8', a.total, 0)) as total_recharge,
             sum(if(a.fee_type = '16', a.total, 0)) as evaluation_cost,
             IFNULL(b.total,0) as deduction_application,
             IFNULL(c.packaging_fee,0) as packaging_fee,
             IFNULL(c.after_sale_fee,0) as after_sale_fee,
             IFNULL((c.refund_ren_min_bi + c.repeat_ren_min_bi),0) AS after_sales_freight,
             FROM_UNIXTIME(a.create_time) AS create_time
        FROM (SELECT sum( amount ) AS `total`, l.id, `fee_type`, company_name, w.create_time,IF(fee_type = 7,count(orderno),0) as order_number
                FROM hw_company_list l
                left join `wallet_use_list` w ON l.id = w.uid
                WHERE fee_type IN ( 1, 7, 4, 28, 8, 16 ) AND w.uid IS NOT NULL AND w.uid > 0
                GROUP BY l.id, fee_type)a 
        LEFT JOIN (SELECT l.id, sum( ren_min_bi ) AS `total`, company_name, w.create_time
                FROM hw_company_list l
                LEFT JOIN `wallet_deduction` w ON l.id = w.uid
                left JOIN wallet_use_list u on u.orderno = w.deductionno
                WHERE w.`status` = 2
                GROUP BY l.id)b ON a.id = b.id
        LEFT JOIN (SELECT l.id,
                            SUM( packaging_fee ) AS `packaging_fee`,
                            SUM( after_sale_fee ) AS `after_sale_fee`,
                            SUM( refund_ren_min_bi ) AS `refund_ren_min_bi`,
                            SUM( repeat_ren_min_bi ) AS `repeat_ren_min_bi`,
                            company_name, wul.create_time
                    FROM hw_company_list l
                    LEFT JOIN `wallet_use_list` `wul` ON l.id = wul.uid 
                                        LEFT JOIN `hw_after_sale_list` `asl` ON wul.orderno = asl.after_sale_order
                    WHERE `fee_type` = 39
                    GROUP BY l.id)c ON a.id = c.id
        WHERE 1=1
        GROUP BY a.id
        UNION ALL (SELECT company_name,id as uid,'0','0','0','0','0','0','0','0','0','0','0','0' 
                    FROM hw_company_list)) d 
GROUP BY uid