记录一次业务功能

向右看齐 2023-08-17 17:35 186阅读 0赞

需求，原型图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]  
最后，做出来的效果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]

##### 需求是，做个列表功能，名字叫第一次订单信息统计表。列出客户的第一次下单的信息（下单包括订单，生产单，出货单）。 #####

##### 当接到这个需求的时候，蛮高兴的，因为好久没有接这么简单的需求了。然而我还是太年轻了。仔细一想，客户编号，客户名称，一个表，这个简单。下订单日期，下订单号，两个表（橱柜订单表，门板订单表，这两个表的字段），客户有可能在橱柜下单，也有可能在门板下单，但不管怎么样，在两个表中要找出最早的下单。 #####

##### 下订单金额（门板报价表，橱柜报价表两个表中的一个字段，两个订单表与两个报价表关联的字段是订单号），找出最早的订单之后，要找出它对应的订单金额。 #####

##### 比如在橱柜订单表找到了最早的订单，那么根据关联的是订单号，在橱柜报价表找到相应的下订单金额的值， #####

##### 如果在门板订单表找到了最早的订单，那么根据关联的是订单号，在门板报价表找到相应的下订单金额的值。 #####

##### 那么问题来了，找出的最早订单怎么知道是在橱柜订单表还是在门板订单表。这个让我纠结了一下。 #####

##### 后面的下生产单日期，下生产单单号（三个表，橱柜生产单表，门板生产单表，单门板生产单表），下生产单金额（一样也在门板报价表，橱柜报价表，三个生产单表与两个报价表关联的字段是订单号，门板和单门板都属于门板，但这两个表的字段各不相同，具体不说了）。 #####

##### 出货日期，出货订单号（两个表，门板出库表，单门板出库表），出货金额（门板报价表，橱柜报价表，跟出库表关联的是订单号）。 #####

##### 列出来的这些表，刚好是10个表，后续需求增加了，有新的表，这个暂且不说。 #####

##### 客户表跟订单表，生产单表，出货表，这三个关联字段是客户编号， #####

##### 订单表，生产单表，出货表，这三个跟报价表关联的都是订单号， #####

##### 订单表要找出最早日期的，找出来之后，要找出对应的金额。 #####

##### 生产单表要找出最早日期的，找出来之后，要找出对应的金额。 #####

##### 出货表表要找出最早日期的，找出来之后，要找出对应的金额。 #####

##### 说了一大堆，终于把老板提出的需求讲完了。 #####

##### 简单一句话，假如客户先后下了订单A,B,C三个，接着先后下了B,C两个生产单，最后下了C出货单，那么列表出来的，订单是A，生产单是B，出货单是C，另外显示对应的金额。一句话概括了需求。一开始想着用SQL查出来，不过，那样会很麻烦，SQL不好写，因为，假如找出最早的订单，怎么才能知道这个订单是橱柜的，还是门板的，因为还要找出对应的报价金额。前面也说了， #####

##### 比如在橱柜订单表找到了最早的订单，那么根据关联的是订单号，在橱柜报价表找到相应的下订单金额的值， #####

##### 如果在门板订单表找到了最早的订单，那么根据关联的是订单号，在门板报价表找到相应的下订单金额的值。 #####

##### 用if来判断吗？每个都这样，会很冗余。事后证明，确实是，SQL会更长。 #####

##### 问了个同事，同事让我查出想要的字段，然后再构建对象，再用后台代码实现。我一听，哭笑不得。这得多复杂啊。 #####

##### 觉得这个方法不可行，行不通。还是通过SQL找出来。于是分割三部分， #####

##### 先找出订单日期，订单号和订单金额，接着找出下生产单日期，下生产单号，下生产单金额，最后是出货表。 #####

##### 只要找出最早的订单日期，这个部分搞定了，那么剩下两个的依葫芦画瓢。 #####

##### 中途写SQL花了点时间，费脑细胞，没有写if这样的东西，问了另外一个同事，他教我用row\_number ()函数，最后写出来了。 #####

select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
    	cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1
    		 from (  
    select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
    	c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1		
    	from customer c left join 	
    	 (select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
    	 (		
    	   select d.customer_no,d.created_date_time,d.order_no from  dp_order d where d.frozen = false
    	   union 
    	   select s.customer_no,s.created_date_time,s.order_no from  split_order s
    	  )ord inner join 	  
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      inner join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	  
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on ord.order_no = quo_order.order_no	) order_quo
    	on c.customer_no = order_quo.customer_no
    			 ) cus_ord_quo_pro_stk where ranknum =1

##### 查询出来的结果如下： #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 2]

这个是第一部分的SQL语句  
接着依葫芦画瓢，写第二个SQL语句

select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,	
    		cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2
    		 from (  
    	select row_number () over(partition by c.customer_no order by production_quo.make_date) ranknum,
    	c.customer_no,c.short_name,production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2
    	from customer c left join 	
    		(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
    	 (		
    	   select g.ref_order_no,g.customer_no,g.make_date,g.order_no from  general_dp_pp_order g where g.frozen = false
    	   union 
    	   select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from  prod_process_cabinet_order p
    		union 
    	   select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from  prod_process_door_panel_order pr
    	  )production inner join 	 
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      left join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	 
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on production.ref_order_no = quo_order.order_no	) production_quo
    		on c.customer_no = production_quo.customer_no  			
    			
    	) cus_ord_quo_pro_stk where ranknum =1

##### 查询出来的结果如下： #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 3]  
最后是第三个SQL的部分

select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,	
    		cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (  
    	select row_number () over(partition by c.customer_no order by stock_quo.stock_out_date) ranknum,
    	c.customer_no,c.short_name,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
    	from customer c left join 		
    		(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
    	 (		
    	   select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from  gene_dp_stock_out_order g_stock where g_stock.frozen = false
    	   union 
    	   select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock		
    	  )stock inner join 	 
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      left join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	 
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on stock.order_no = quo_order.order_no	) stock_quo
    	on c.customer_no = stock_quo.customer_no 		
    	) cus_ord_quo_pro_stk where ranknum =1

##### 查询出来的结果如下： #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 4]

##### 最后，把这3个SQL语句拼接起来，弄成一条SQL #####

SQL:

select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
    	cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1,
    		cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2,
    		cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (  
    	select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
    	c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1,
    		production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
    	from customer c left join 	
    	 (select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
    	 (		
    	   select d.customer_no,d.created_date_time,d.order_no from  dp_order d where d.frozen = false
    	   union 
    	   select s.customer_no,s.created_date_time,s.order_no from  split_order s
    	  )ord inner join 	  
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      inner join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	  
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on ord.order_no = quo_order.order_no	) order_quo
    	on c.customer_no = order_quo.customer_no  	
    		left join 		
    		(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
    	 (		
    	   select g.ref_order_no,g.customer_no,g.make_date,g.order_no from  general_dp_pp_order g where g.frozen = false
    	   union 
    	   select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from  prod_process_cabinet_order p
    		union 
    	   select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from  prod_process_door_panel_order pr
    	  )production inner join 	 
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      left join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	 
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on production.ref_order_no = quo_order.order_no	) production_quo
    		on c.customer_no = production_quo.customer_no  			
    	left join 	
    		(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
    	 (		
    	   select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from  gene_dp_stock_out_order g_stock where g_stock.frozen = false
    	   union 
    	   select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock		
    	  )stock inner join 	 
    	  (select qu.order_no,quo.actual_amount
      from quotation_unified_order qu
      left join quotation_unified_order_quotation_unified_bodies quo
      on qu.id = quo.quotation_unified_order_id
    	  union	 
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
    	  on stock.order_no = quo_order.order_no	) stock_quo
    	on c.customer_no = stock_quo.customer_no 		
    	) cus_ord_quo_pro_stk where ranknum =1

##### sql有点长，没事，看得懂，不会很难。 #####

##### 老大看了我写的SQL语句，有点惊讶，那么长，而且看起来有点乱。他教我怎么优化SQL， #####

##### 优化后的SQL: #####

select row_number () over(partition by c.customer_no order by first_order.created_date_time,first_production.make_date,first_stock.stock_out_date) ranknum,
    	c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no,first_order.actual_amount,
    			first_production.make_date,first_production.ord_no,first_production.actual_amount,
    			first_stock.stock_out_date,first_stock.ord_no,first_stock.actual_amount
    	from customer c 
    left join 
    (
    	select * from(		
    select row_number () over(partition by order_quo.customer_no order by order_quo.created_date_time) ranknum, 
    	order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no,order_quo.actual_amount
    from (
    	   (select d.customer_no,d.created_date_time,d.order_no ord_no from  dp_order d where d.frozen = false
    	   union 
    	   select s.customer_no,s.created_date_time,s.order_no from  split_order s
    		union
    		select f.customer_no,f.order_date,f.order_no from first_order_history_data f
    	  )ord 
    	inner join 	 
    	  (select qu.order_no,qu.actual_amount
      	from quotation_unified_order qu
    	  union	  
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
    	   union
    	   select f.order_no,f.order_amount from first_order_history_data f
    	  )quo_order
    	 on ord.ord_no = quo_order.order_no) order_quo
    	) aa
    where ranknum=1
    ) first_order on c.customer_no = first_order.customer_no
    left join (
    	select * from(		
    select row_number () over(partition by prod_quo.customer_no order by prod_quo.make_date) ranknum, 
    	prod_quo.customer_no, prod_quo.make_date,prod_quo.ord_no, prod_quo.actual_amount
    from (
    		( 
    	   select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from  general_dp_pp_order g where g.frozen = false
    	   union 
    	   select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from  prod_process_cabinet_order p
    		union 
    	   select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from  prod_process_door_panel_order pr
    		union 
    	   select f.order_no,f.customer_no,f.production_date,f.production_no from  first_order_history_data f
    	  )production 
    	inner join 	 
    	  (select qu.order_no,qu.actual_amount
      from quotation_unified_order qu
    	  union	  
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
    	   union
    	   select f.order_no,f.order_amount from first_order_history_data f
    	  )	production_quo on production.ref_order_no = production_quo.order_no	) prod_quo
    	) bb
    where ranknum=1	
    ) first_production on c.customer_no = first_production.customer_no	
    left join(
    	select * from(		
    select row_number () over(partition by stk_quo.customer_no order by stk_quo.stock_out_date) ranknum, 
    	stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no,stk_quo.actual_amount
    from (
    		( 
    	   select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from  gene_dp_stock_out_order g_stock where g_stock.frozen = false
    	   union 
    	   select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
    	   union 
    	   select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f
    	  )stock 
    		inner join
    	  (select qu.order_no,qu.actual_amount
      from quotation_unified_order qu
    	  union	  
    	  select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
    	   union
    	   select f.order_no,f.order_amount from first_order_history_data f
    	  )	stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo
    	) cc
    where ranknum=1	
    ) first_stock on c.customer_no = first_stock.customer_no

##### 虽然看起来更长了，但SQL语句更优美了，看起来更好看了 #####

##### 另外，老大叫我在Repository层把这个SQL拆成三部分，订单，生产单，出货单，这三个部分放在常量里。一个主体，拼接三个常量。 #####

##### Repository层代码： #####

##### 常量部分： #####

static final String FIRST_ORDER = "select * from( "
    			+ "select row_number () over(partition by order_quo.customer_no order by "
    			+ "order_quo.created_date_time) ranknum, "
    			+ "order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no ord_no1, "
    			+ "order_quo.actual_amount actual_amount1 from ( "
    			+ "(select d.customer_no,d.created_date_time,d.order_no ord_no from dp_order d where d.frozen = false "
    			+ "union select s.customer_no,s.created_date_time,s.order_no from split_order s "
    			+ "union select f.customer_no,f.order_date,f.order_no from first_order_history_data f "
    			+ ")ord inner join (select qu.order_no,qu.actual_amount " + "from quotation_unified_order qu union "
    			+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
    			+ "select f.order_no,f.order_amount from first_order_history_data f )quo_order "
    			+ "on ord.ord_no = quo_order.order_no) order_quo ) aa where ranknum=1 ";
    
    	static final String FIRST_PRODUCTION = "select * from( "
    			+ "select row_number () over(partition by prod_quo.customer_no order by "
    			+ "prod_quo.make_date) ranknum, "
    			+ "prod_quo.customer_no, prod_quo.make_date,prod_quo.ref_order_no, "
    			+ "prod_quo.actual_amount actual_amount2 from ( ( "
    			+ "select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from "
    			+ "general_dp_pp_order g where g.frozen = false union "
    			+ "select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from "
    			+ "prod_process_cabinet_order p union "
    			+ "select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from "
    			+ "prod_process_door_panel_order pr union "
    			+ "select f.order_no,f.customer_no,f.production_date,f.production_no from "
    			+ "first_order_history_data f "
    			+ ")production inner join (select qu.order_no,qu.actual_amount "
    			+ "from quotation_unified_order qu union "
    			+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
    			+ "select f.order_no,f.order_amount from first_order_history_data f "
    			+ ") production_quo on production.ref_order_no = production_quo.order_no) prod_quo ) bb "
    			+ "where ranknum=1 ";
    
    	static final String FIRST_STOCK = "select * from( "
    			+ "select row_number () over(partition by stk_quo.customer_no order by "
    			+ "stk_quo.stock_out_date) ranknum, "
    			+ "stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no ord_no3, "
    			+ "stk_quo.actual_amount actual_amount3 from ( ( "
    			+ "select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from "
    			+ "gene_dp_stock_out_order g_stock where g_stock.frozen = false union "
    			+ "select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock "
    			+ "union select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f "
    			+ ")stock inner join (select qu.order_no,qu.actual_amount "
    			+ "from quotation_unified_order qu union "
    			+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
    			+ "select f.order_no,f.order_amount from first_order_history_data f "
    			+ ") stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo ) cc where ranknum=1 ";

主体拼接常量：

@Query(value = "select row_number () over(partition by c.customer_no order by first_order.created_date_time,"
    			+ "first_production.make_date,first_stock.stock_out_date) ranknum, "
    			+ "c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no1,"
    			+ "first_order.actual_amount1, "
    			+ "first_production.make_date,first_production.ref_order_no,first_production.actual_amount2, "
    			+ " first_stock.stock_out_date,first_stock.ord_no3,first_stock.actual_amount3 "
    			+ " from customer c left join ( " + FIRST_ORDER
    			+ ") first_order on c.customer_no = first_order.customer_no left join ( " + FIRST_PRODUCTION
    			+ ") first_production on c.customer_no = first_production.customer_no left join( " + FIRST_STOCK
    			+ ") first_stock on c.customer_no = first_stock.customer_no "
    			+ "where c.short_name like %:shortName% limit :pageSize offset :pageOffset", nativeQuery = true)
    	List<Object[]> findAllByPageWithShortName(@Param(value = "shortName") String shortName,
    			@Param(value = "pageSize") Integer pageSize, @Param(value = "pageOffset") Integer pageOffset);

##### 这样就好看多了。如果不拆分的话，看起来就一大堆，时间长了，我再倒回去看，我都能看晕，更何况别人，后期维护也很麻烦。 #####

##### 实体类代码，用来显示列表数据： #####

public class FirstOrderReport { 
    	private String customerNo;
    	private String customerName;
    	private Date orderDate;
    	private String orderNo;
    	private String orderAmount;
    	private Date productionDate;
    	private String productionNo;
    	private String productionAmount;
    	private Date stockDate;
    	private String stockNo;
    	private String stockAmount;
    	//getter、setter...
    }

##### service层代码，业务逻辑 #####

@Transactional
    @Service
    public class FirstOrderReportServiceImpl implements FirstOrderReportService { 
    	@Autowired
    	private CustomerRepository customerRepository;
    
    	@Override
    	//分页+查询列表查询
    	public Page<FirstOrderReport> getFirstOrderReports(String customerName, Integer pageIndex, Integer pageSize) { 
    		Pageable pageable = new PageRequest(pageIndex, pageSize);
    		List<FirstOrderReport> firstorderReports = customerRepository
    				.findAllByPageWithShortName(customerName, pageSize, pageSize * pageIndex).stream()
    				.map(firstorder -> genFirstorderReport(firstorder)).collect(Collectors.toList());
    		Long totalCount = customerRepository.getCountByShortName(customerName);
    		return new PageImpl<FirstOrderReport>(firstorderReports, pageable, totalCount);
    	}
    
    	//对查询出来的数据构建出实体类对象
    	private FirstOrderReport genFirstorderReport(Object[] firstorder) { 
    		FirstOrderReport firstorderReport = new FirstOrderReport();
    		if (firstorder != null && firstorder.length > 0) { 
    			firstorderReport.setCustomerNo(firstorder[1].toString());
    			firstorderReport.setCustomerName(firstorder[2].toString());
    			firstorderReport.setOrderDate(
    					firstorder[3] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[3].toString()));
    			firstorderReport.setOrderNo(firstorder[4] == null ? "" : firstorder[4].toString());
    			firstorderReport.setOrderAmount(firstorder[5] == null ? "" : firstorder[5].toString());
    			firstorderReport.setProductionDate(
    					firstorder[6] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[6].toString()));
    			firstorderReport.setProductionNo(firstorder[7] == null ? "" : firstorder[7].toString());
    			firstorderReport.setProductionAmount(firstorder[8] == null ? "" : firstorder[8].toString());
    			firstorderReport.setStockDate(
    					firstorder[9] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[9].toString()));
    			firstorderReport.setStockNo(firstorder[10] == null ? "" : firstorder[10].toString());
    			firstorderReport.setStockAmount(firstorder[11] == null ? "" : firstorder[11].toString());
    		}
    		return firstorderReport;
    	}
    
    }

##### controller层代码，这个不多说 #####

@RestController
    @RequestMapping("api/marketingmanagement/first-order-report")
    public class FirstOrderReportController extends ExceptionResponse { 
    	@Autowired
    	private FirstOrderReportService firstOrderReportService;
    
    	@RequestMapping(value = "/gene-first-order-reports", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    	public Page<FirstOrderReport> getFirstorderReports(@RequestParam String customerName, 
    			@RequestParam Integer pageIndex, @RequestParam Integer pageSize) { 
    		return firstOrderReportService.getFirstOrderReports(customerName.trim(), pageIndex, pageSize);
    	}
    }

##### 终于弄完了，效果如图 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]

##### 对于需求，先要理清思路，有了思路，再有解决解决方案。某个大佬也跟我说个这句话,理清了思路，做起来也会得心应手。 #####

##### 后续，对这个需求增加了新的功能。下一篇会讲。 #####

##### 链接：[记录一次工作中Springboot的Excel导入导出功能][Springboot_Excel] #####

##### 这个需求是：下载Excel，上传Excel，把excel里面的数据和现在的数据作对比，哪一个时间更早，就获取哪个数据 #####

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]: /images/20230808/d2a2650b48fd41769b4c4f4c36040e00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]: /images/20230808/def23aa08a334942afbad38fd0a9060a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 2]: /images/20230808/4afb16d48f774ddf966188348c84d879.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 3]: /images/20230808/055500b58fdd484e999925ec03535d7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 4]: /images/20230808/b55b6730e9cb4fe1b515a1545e690b3d.png
[Springboot_Excel]: https://blog.csdn.net/exodus3/article/details/102172138