store_procedure

create procedure myNewPro
as
 declare @max int
 declare @min int
 declare @in1 datetime
 declare @in2 datetime
 declare @in3 datetime
 declare @in4 datetime
 declare @out1 datetime
 declare @out2 datetime
 declare @out3 datetime
 declare @out4 datetime
 declare @Jholiday float
 declare @Jholiday1 float
 declare @Festival float
 declare @Festival1 float
 declare @Overtime varchar(120)

 declare @division_id varchar(10)
 declare @dspName varchar(10)
 declare @emp_id varchar(10)

declare @c1 varchar(8) ,@c2 varchar(8) ,@c3 varchar(8),@c4 varchar(8)
declare @a1 float ,@a2 float,@a3 float,@a4 float

declare @commonTime float,@oneDotFiveTime  float,@twoTime float, @threeTime float,@quanNum float
declare @type1 varchar(8),@type2 varchar(8),@type3 varchar(8),@type4 varchar(8)
declare @temp1 datetime,@temp2 datetime,@overtimeType int,@salaryType int
declare @Jholidaysee varchar(80),@Festivalsee varchar(80),@waichusee varchar(80),@chucaisee varchar(80),@Overtimesee varchar(80)
declare @tempType varchar(8),@tempTypeInt int,@month varchar(8)
 set  @max =(select max(id) from T045F120)
 set @min=(select min(id) from T045F120)
set @temp1=''
set @temp2=''
  while @min<@max
  begin
 if exists(select   field42 from T045F120 where id=@min and field104='1')
  begin
  set  @emp_id=(select   field42 from T045F120 where id=@min)
                                  --獲取進廠記錄
  set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))
                                      if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>1
                                        set @in2=( select top 1 a.field9 from (select top 2 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                                     else
                                        set @in2=null
                                   
                                    if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>2
                                         set @in3=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                                     else
                                        set @in3=null
                                if ( select count(*) from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9))>3
                                 set @in4=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                             else
                                set @in4=null
                                     --獲取出廠記錄

                                  set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))
                                      if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>1
                                        set @out2=( select top 1 a.field9 from (select top 2 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                                     else
                                        set @out2=null
                                    if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>2
                                         set @out3=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                                     else
                                        set @out3=null
                                if ( select count(*) from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9))>3
                                 set @out4=( select top 1 a.field9 from (select top 3 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-2)=day(field9) order by field9 asc) as a order by a.field9 desc)
                             else
                                set @out4=null

                           --    print(@emp_id+@in1+@out1+@in2+@out2+'   ' +@in3+'   '+@out3+'  '+@in4+'   '+@out4)
                               --寫入臨時表變量
                               --如果進廠時間遲于出廠時間,表明跨天上班
                             if @in1>@out1
                             ---  set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)
                               if(select top 1 field3 from T093F040 where  field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)=2
                                begin
                                 --   set @in1=(select top 1 field9 from T093F040 where field3=2 and field21=@emp_id and day(getdate()-3)=day(field9) order by field9 desc)
   --昨天的不處理
  set @in1=''
  set @out1=''
                                end
                             if @out1 is null
                             --   set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-1)=day(field9) order by field9 desc)
                             if(select top 1 field3 from T093F040 where  field21=@emp_id and day(getdate()-1)=day(field9) order by field9 asc)=7
                                begin
                                    set @out1=(select top 1 field9 from T093F040 where field3=7 and field21=@emp_id and day(getdate()-1)=day(field9) order by field9 asc)
                                end
                          --獲取部門id號,姓名
                            select @division_id= field106,@dspName=field41,@salaryType=field63 from T045F120 where field42=@emp_id
                          --?取?假??
                       if exists (select id from  T055F120 where getdate()-2 between field17 and field18 and field51=@emp_id)
                        select @Jholiday=field34,@Jholiday1=field35 from T055F120 where getdate()-2 between field17 and field18 and field51=@emp_id
                       else
                          begin
                          set    @Jholiday=0.0
                           set     @Jholiday1=0.0
             end
                       --?取公休??
                     if exists ( select id   from T041F120 where getdate()-2 between field19 and field20 and field51=@emp_id)
                        select @Festival=field30,@Festival1=field31 from T041F120 where getdate()-2 between field19 and field20 and field51=@emp_id
                     else
                          begin
                         set     @Festival=0.0
                          set     @Festival1=0.0
             end
                        -- ?取加加班??
                
                     if exists ( select id from  T056F120  where convert(varchar(10),getdate()-2 ,23)=convert(varchar(10),field17,23) and field46=@emp_id)
                       begin
 
                            select @Overtime=field25,@temp1=field17,@temp2=field18,@overtimeType=field9  from T056F120  where  convert(varchar(10),getdate()-2 ,23)=convert(varchar(10),field17,23) and field46=@emp_id
                            --判斷間段是否為加班段
    
                            if @in1 is not null  and  @out1 is not null and @temp1!='' and @temp2!=''
                                 begin
  
                  
                                      if datediff(mi,@temp1,@in1)>-60 and datediff(mi,@temp2,@out1)<60
                                        begin
   
       if @overtimeType=1
    begin
   
          set  @type1=1.5
   end
       else set @type1=@overtimeType
    
                                      print('--------------------------------- datediff(mi,@temp1,@in1)------------------------------'+cast(@in1 as varchar(20))+'   '+cast(@out1 as varchar(20))+'    '+cast( @temp1 as varchar(20))+'   '+cast(@temp2 as varchar(20))+'  '+cast( datediff(mi,@temp1,@in1) as varchar(8)))
     end
        
               end
   else
   set @type1=1
  
     if @in2 is not null and @out2 is not null
                                 begin
                                      if datediff(mi,@temp1,@in2)>-60 and datediff(mi,@temp2,@out2)<60
                                        begin
       if @overtimeType=1
          set  @type2=1.5
       else set @type2=@overtimeType
                                
     end
    
               end
             else
   set @type2=1   

     if @in3 is not null and @out3 is not null
                                 begin
                                      if datediff(mi,@temp1,@in3)>-60 and datediff(mi,@temp2,@out3)<60
                                        begin
       if @overtimeType=1
          set  @type3=1.5
       else set @type3=@overtimeType
        
     end
       
               end
      else
   set @type3=1
  
     if @in4 is not null and @out4 is not null
                                 begin
                                      if datediff(mi,@temp1,@in4)>-60 and datediff(mi,@temp2,@out4)<60
                                        begin
       if @overtimeType=1
          set  @type4=1.5
       else set @type4=@overtimeType
 
     end
          
               end 
       else
   set @type4=1 
 end
                            
                       else
                          begin
                           set   @Overtime=0.0 
           set @type1=1 set @type2=1 set @type3=1 set @type4=1     
             end
                       --null change to ''
 if @division_id is null
 set   @division_id=''
 if @dspName is null
 set   @dspName=''
 if @emp_id is null
 set   @emp_id=''
 if @in1 is null
 set   @in1=''
 if @out1 is null
 set   @out1=''
 if @in2 is null
 set   @in2=''
 if @out2 is null
 set   @out2=''
 if @in3 is null
 set   @in3=''
 if @out3 is null
 set   @out3=''


                 if @in4 is null
 set   @in4=''
                 if @out4 is null
 set   @out4=''
 if @Festival1 is null
 set   @Festival1=''
 if @Festival is null
 set   @Festival=''
 if @Jholiday is null
 set   @Jholiday=''
                  if @Jholiday1 is null
 set   @Jholiday1=''
 if @Overtime is null
 set   @Overtime=''
 
               --把時間小段寫入表中
                 if @in1!='' and @out1!=''
                        begin
                            set @c1=datediff(mi,@in1,@out1)
                            set  @c1=cast(@c1/60 as varchar(20))+substring(cast(@c1%60/60.0 as varchar(20)),2,3)
                          print(@c1+'   '+@emp_id)      

                        --可更改字段設置默認值
                              set @a1=round(cast(@c1 as float),0,1)
                              if @a1>8  set @a1=8
                     end
                else
                       begin
                            set @c1=0.0
                             set @a1=0.0
                       end
      if @in2!='' and @out2!=''
                         begin
                             set @c2=datediff(mi,@in2,@out2)
                             set  @c2=cast(@c2/60 as varchar(8))+substring(cast(@c2%60/60.0 as varchar(8)),2,3)
                           print(@c2+'   '+@emp_id)      
     --可更改字段設置默認值,@a2是@c2取整數
                              set @a2=round(cast(@c2 as float),0,1)
                                                                 if @a2>8  set @a2=8

                                                  print('------------------@a2:@c2---------------'+cast(@a2 as varchar(10))+'   '+@c2 );
                      end
   else
                        begin
                             set @c2=0.0
                              set @a2=0.0
                        end
      if @in4!='' and @out4!=''
                         begin
                             set @c4=datediff(mi,@in4,@out4)
                             set  @c4=cast(@c4/60 as varchar(8))+substring(cast(@c4%60/60.0 as varchar(8)),2,3)
                           print(@c4+'   '+@emp_id)      
                                     --可更改字段設置默認值
                              set @a4=round(cast(@c4 as float),0,1)
                                                                 if @a4>8  set @a4=8
                      end
   else
                       begin
                            set @c4=0.0
                             set @a4=0.0
                       end
      if @in3!='' and @out3!=''
                         begin
                             set @c3=datediff(mi,@in3,@out3)
                             set  @c3=cast(@c3/60 as varchar(8))+substring(cast(@c3%60/60.0 as varchar(8)),2,3)
                           print(@c3+'   '+@emp_id)      
      --可更改字段設置默認值
                                           set @a3=round(cast(@c3 as float),0,1)
                                                                 if @a3>8  set @a3=8   
                      end
   else
                       begin
                            set @c3=0.0
                             set @a3=0.0
                       end
     
         --請假參照field60,公休參照field61,加班參照field63,出差參照field62,外出關照field64
        -- @Jholidaysee varchar(50),@Festivalsee varchar(50),waichusee varchar(50),chucaisee varchar(50),Overtimesee varchar(50)
            --1.請假參照處理
              if exists ( select *  from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(20), field17,111) and convert(varchar(20), field18,111)  and field51=@emp_id)  
                      begin           
             select @tempType=field41,@temp1=field17,@temp2=field18 from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111)  and field51=@emp_id
   --解碼
                       if @tempType is not null
            begin
                          select  @Jholidaysee=field6 from T064F010 where field1=@tempType
                           set @Jholidaysee=@Jholidaysee+'<br>'+convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120)
  print('---------------@tempType:@Jholidaysee-----------------'+@tempType+'  '+@Jholidaysee);
       end
        end
                else
                    set  @Jholidaysee='----'
   
            --2.公休參照處理
  
          if exists ( select *  from T041F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111)  and field51=@emp_id)
                        begin
             select @temp1=field17,@temp2=field18 from T055F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111)  and field51=@emp_id
 
                           set @Festivalsee=convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120);
  print('---------------公休-----------------'+@Festivalsee);
       end
                else
                    set  @Festivalsee='----'

   --1.加班參照處理
                  if exists ( select *  from T056F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111)  and field46=@emp_id
     begin            
             select @tempTypeInt=field9,@temp1=field17,@temp2=field18 from T056F120 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field17,111) and convert(varchar(10), field18,111)  and field46=@emp_id
   --解碼
                       if @tempTypeInt is not null
            begin
       if @tempTypeInt=1
   set @Overtimesee='1.5倍加班'
  if @tempTypeInt=2
   set @Overtimesee='2倍加班'
  if @tempTypeInt=3
   set @Overtimesee='4倍加班'
                           set @Overtimesee=@Overtimesee+'<br>'+convert(varchar(20),@temp1,120) +'<br>'+convert(varchar(20),@temp2,120)
  print('---------------@Overtimesee-----------------'+@Overtimesee);
       end
     end
                else
                    set  @Overtimesee='----'


     --1出差參照處理
                  if exists ( select *  from T098F080 where convert(varchar(20),getdate()-2,111)between  convert(varchar(10), field14,111) and  convert(varchar(10), field15,111)  and field32 like '%'+@emp_id+'%')              
           begin
      select @temp1=field14,@temp2=field15 from T098F080 where convert(varchar(20),getdate()-2,111) between convert(varchar(10), field14,111) and convert(varchar(10), field15,111)  and  field32 like '%'+@emp_id+'%'
                           set @chucaisee='出差'+'<br>'+convert(varchar(20),@temp1,120)+'<br>'+convert(varchar(20),@temp2,120)
  print('---------------@chucaisee-----------------'+@chucaisee);
       end
                else
                    set  @chucaisee='----'
          
               --計算正常上班總時間,1.5倍加班總時間,2.0,3.0倍總時間
                   
                if @type1='1'       set  @commonTime=@a1;
                else      set @commonTime=0.0;
                  if @type1='1.5'    set   @oneDotFiveTime=@a1;
                 else      set @oneDotFiveTime=0.0; 
                  if @type1='2'       set  @twoTime=@a1;
    else    set @twoTime=0.0;
                  if @type1=' 3'     set @threeTime=@a1;
    else      set @threeTime=0.0;

                  if @type2='1'        set  @commonTime=@commonTime+@a2;
                  if @type2='1.5'   set   @oneDotFiveTime= @oneDotFiveTime+@a2;
                  if @type2='2'      set  @twoTime=@twoTime+@a2;
                  if @type2=' 3'     set @threeTime=@threeTime+@a2;

  if @type3='1'        set  @commonTime=@commonTime+@a3;
                  if @type3='1.5'    set   @oneDotFiveTime= @oneDotFiveTime+@a3;
                  if @type3='2'       set  @twoTime=@twoTime+@a3;
                  if @type3=' 3'     set @threeTime=@threeTime+@a3;

 if @type4='1'        set  @commonTime=@commonTime+@a4;
                  if @type4='1.5'    set   @oneDotFiveTime= @oneDotFiveTime+@a4;
                  if @type4='2'      set  @twoTime=@twoTime+@a4;
                  if @type4= '3'      set @threeTime=@threeTime+@a4;

                set  @quanNum= @oneDotFiveTime*1.5+@twoTime*2+@threeTime*3;
        
                print('-----------------------@type1,@type2,@type3,@type4----------------------------------------------------'+@type1+':'+@type2+':'+@type3);
                print('--@emp_id,@in1,@out1,@in2,@out2,@in3,@out3---------'+@emp_id+':'+convert(varchar(20),@in1,120)+':'+convert(varchar(20),@out1,120));
               print('---------------@oneDotFiveTime:@twoTime:@threeTime------------------------------------'+cast(@commonTime as varchar(20))+':'+cast(@oneDotFiveTime as varchar(20))+':'+cast(@twoTime as varchar(20))+':'+cast(@threeTime as varchar(20))+':'+cast(@quanNum as varchar(20)));
             
 --年月
                 set @month=cast(month(getdate()-2) as varchar(8));
                 if cast(@month as int)<10
  set  @month='0'+cast(month(getdate()-2) as varchar(8));
      
                 --    插入考勤審核檔T089F120
                     insert into T089F120(df2_id,field51,field1,field42,field44,field43,field17,field18,field19,field20,field21,field22,field23,field24,field25,field26,field27,field28,field30,
                                           field31,field32,field33,field34,field35,field36,field37,field38,field39,field116,field52,field53,field54,field55,field45,field46,field47,field48,field49,field60,field61,field62,field63,field64) values
                              (719,convert(varchar(20),getdate()-2,111),cast(year(getdate()-2) as varchar(8))+@month,@division_id,@dspName,@emp_id,@in1,@out1,@in2,@out2,@in3,@out3,@in4,@out4,@a1,@a2,@a3,@a4,@Festival1,
                                         @commonTime ,@Overtime,@Jholiday,@Jholiday1,@Festival,@oneDotFiveTime,@twoTime,@threeTime,@quanNum,'',@c1,@c2,@c3,@c4,@type1,@type2,@type3,@type4,@salaryType,@Jholidaysee,@Festivalsee,@chucaisee,@Overtimesee,@waichusee)
                                           
                                     
  end
   set @min=@min+1
  end

--select * from  @temp
 

 

 

 

INSERT INTO w_display_order_send( store_code , item_code , gondola_furniture_type , display_gondola , display_shelf , display_row , display_face , display_order_input_datetime , extraction_date , update_datetime , update_function_id , update_person_id , update_count ) SELECT tmp.store_code , tmp.item_code , CASE WHEN tmp.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(tmp.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tmp.display_gondola , tmp.display_shelf , tmp.display_row , tmp.display_face , tmp.display_order_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM ( SELECT te1.store_code , tn.item_code , gl1.gondola_furniture_type , tn.display_gondola , tn.display_shelf , tn.display_row , tn.display_face , tn.display_order_input_datetime , ROW_NUMBER() OVER ( PARTITION BY te1.store_code , tn.item_code , tn.label_code ORDER BY tn.display_order_input_datetime DESC ) AS registrationOrder FROM m_store_number AS te1 INNER JOIN m_display AS tn ON te1.original_store_code = tn.original_store_code LEFT JOIN m_gondola_layout AS gl1 ON gl1.version = #{gondolaLayoutVersion} AND tn.original_store_code = gl1.original_store_code AND tn.display_gondola = gl1.gondola_number WHERE te1.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te1.apply_start_date AND te1.apply_end_date ) AS tmp WHERE tmp.registrationOrder <= 2 UNION ALL SELECT DISTINCT te2.store_code , sh1.item_code , CASE WHEN gl2.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl2.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt1.display_gondola , tt1.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt1.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te2 INNER JOIN m_specific_display_order AS tt1 ON te2.original_store_code = tt1.original_store_code LEFT JOIN m_gondola_layout AS gl2 ON gl2.version = #{gondolaLayoutVersion} AND tt1.original_store_code = gl2.original_store_code AND tt1.display_gondola = gl2.gondola_number INNER JOIN m_pattern AS pt ON pt.version = #{patternVersion} AND tt1.original_store_code = pt.original_store_code AND #{systemDate} :: date BETWEEN pt.apply_start_date AND pt.apply_end_date INNER JOIN m_item AS sh1 ON sh1.version = #{itemVersion} AND pt.pattern_type = sh1.pattern_type AND pt.pattern_code = sh1.pattern_code AND #{systemDate} :: date BETWEEN sh1.apply_start_date AND sh1.apply_end_date AND tt1.information_category_code = sh1.information_category_code LEFT JOIN m_license AS li1 ON li1.version = #{licenseVersion} AND te2.original_store_code = li1.original_store_code AND sh1.license_code = li1.license_code LEFT JOIN m_item_by_specific_store_recommendation ts ON ts.version = #{specificItemVersion} AND tt1.original_store_code = ts.original_store_code AND sh1.item_code = ts.item_code AND #{systemDate} :: date BETWEEN ts.apply_start_date AND ts.apply_end_date WHERE te2.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te2.apply_start_date AND te2.apply_end_date AND ( sh1.license_code = '00' OR ( sh1.license_code != '00' AND li1.licenseditem_adopt_flag != '2' ) ) AND ( sh1.specific_item_type = ' ' OR ( sh1.specific_item_type IN ('1', '2', 'G') AND ts.original_store_code IS NOT NULL ) ) UNION ALL SELECT DISTINCT te3.store_code , '999999' , CASE WHEN gl3.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl3.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt2.display_gondola , tt2.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt2.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te3 INNER JOIN m_specific_display_order_by_specific_category AS tt2 ON te3.original_store_code = tt2.original_store_code LEFT JOIN m_gondola_layout AS gl3 ON gl3.version = #{gondolaLayoutVersion} AND tt2.original_store_code = gl3.original_store_code AND tt2.display_gondola = gl3.gondola_number INNER JOIN m_recommendation_group_by_specific_category AS rg ON rg.version = #{recommendationGroupVersion} AND tt2.original_store_code = rg.original_store_code AND #{systemDate} :: date BETWEEN rg.apply_start_date AND rg.apply_end_date INNER JOIN m_item_by_specific_category AS sh2 ON sh2.version = #{itemSpecificCategory} AND rg.recommendation_group_type = sh2.recommendation_group_type AND rg.recommendation_group_code = sh2.recommendation_group_code AND #{systemDate} :: date BETWEEN sh2.apply_start_date AND sh2.apply_end_date AND tt2.information_category_code = sh2.information_category_code LEFT JOIN m_license AS li2 ON li2.version = #{licenseVersion} AND te3.original_store_code = li2.original_store_code AND sh2.license_code = li2.license_code WHERE te3.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te3.apply_start_date AND te3.apply_end_date AND ( sh2.license_code = '00' OR ( sh2.license_code != '00' AND li2.licenseditem_adopt_flag != '2' ) ) 分成三个存储过程
最新发布
07-30
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="jp.co.sej.ssc.or.common.db.BatchDisplayOrderSendDAO"> <!-- 陳列順送信ワークに登録する。 --> <update id="delDisplayOrderSend"> TRUNCATE TABLE w_display_order_send </update> <insert id="insertDisplayOrderSend"> INSERT INTO w_display_order_send( store_code , item_code , gondola_furniture_type , display_gondola , display_shelf , display_row , display_face , display_order_input_datetime , extraction_date , update_datetime , update_function_id , update_person_id , update_count ) SELECT tmp.store_code , tmp.item_code , CASE WHEN tmp.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(tmp.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tmp.display_gondola , tmp.display_shelf , tmp.display_row , tmp.display_face , tmp.display_order_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM ( SELECT te1.store_code , tn.item_code , gl1.gondola_furniture_type , tn.display_gondola , tn.display_shelf , tn.display_row , tn.display_face , tn.display_order_input_datetime , ROW_NUMBER() OVER ( PARTITION BY te1.store_code , tn.item_code , tn.label_code ORDER BY tn.display_order_input_datetime DESC ) AS registrationOrder FROM m_store_number AS te1 INNER JOIN m_display AS tn ON te1.original_store_code = tn.original_store_code LEFT JOIN m_gondola_layout AS gl1 ON gl1.version = #{gondolaLayoutVersion} AND tn.original_store_code = gl1.original_store_code AND tn.display_gondola = gl1.gondola_number WHERE te1.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te1.apply_start_date AND te1.apply_end_date ) AS tmp WHERE tmp.registrationOrder <= 2 UNION ALL SELECT DISTINCT te2.store_code , sh1.item_code , CASE WHEN gl2.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl2.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt1.display_gondola , tt1.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt1.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te2 INNER JOIN m_specific_display_order AS tt1 ON te2.original_store_code = tt1.original_store_code LEFT JOIN m_gondola_layout AS gl2 ON gl2.version = #{gondolaLayoutVersion} AND tt1.original_store_code = gl2.original_store_code AND tt1.display_gondola = gl2.gondola_number INNER JOIN m_pattern AS pt ON pt.version = #{patternVersion} AND tt1.original_store_code = pt.original_store_code AND #{systemDate} :: date BETWEEN pt.apply_start_date AND pt.apply_end_date INNER JOIN m_item AS sh1 ON sh1.version = #{itemVersion} AND pt.pattern_type = sh1.pattern_type AND pt.pattern_code = sh1.pattern_code AND #{systemDate} :: date BETWEEN sh1.apply_start_date AND sh1.apply_end_date AND tt1.information_category_code = sh1.information_category_code LEFT JOIN m_license AS li1 ON li1.version = #{licenseVersion} AND te2.original_store_code = li1.original_store_code AND sh1.license_code = li1.license_code LEFT JOIN m_item_by_specific_store_recommendation ts ON ts.version = #{specificItemVersion} AND tt1.original_store_code = ts.original_store_code AND sh1.item_code = ts.item_code AND #{systemDate} :: date BETWEEN ts.apply_start_date AND ts.apply_end_date WHERE te2.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te2.apply_start_date AND te2.apply_end_date AND ( sh1.license_code = '00' OR ( sh1.license_code != '00' AND li1.licenseditem_adopt_flag != '2' ) ) AND ( sh1.specific_item_type = ' ' OR ( sh1.specific_item_type IN ('1', '2', 'G') AND ts.original_store_code IS NOT NULL ) ) UNION ALL SELECT DISTINCT te3.store_code , '999999' , CASE WHEN gl3.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl3.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt2.display_gondola , tt2.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt2.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te3 INNER JOIN m_specific_display_order_by_specific_category AS tt2 ON te3.original_store_code = tt2.original_store_code LEFT JOIN m_gondola_layout AS gl3 ON gl3.version = #{gondolaLayoutVersion} AND tt2.original_store_code = gl3.original_store_code AND tt2.display_gondola = gl3.gondola_number INNER JOIN m_recommendation_group_by_specific_category AS rg ON rg.version = #{recommendationGroupVersion} AND tt2.original_store_code = rg.original_store_code AND #{systemDate} :: date BETWEEN rg.apply_start_date AND rg.apply_end_date INNER JOIN m_item_by_specific_category AS sh2 ON sh2.version = #{itemSpecificCategory} AND rg.recommendation_group_type = sh2.recommendation_group_type AND rg.recommendation_group_code = sh2.recommendation_group_code AND #{systemDate} :: date BETWEEN sh2.apply_start_date AND sh2.apply_end_date AND tt2.information_category_code = sh2.information_category_code LEFT JOIN m_license AS li2 ON li2.version = #{licenseVersion} AND te3.original_store_code = li2.original_store_code AND sh2.license_code = li2.license_code WHERE te3.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te3.apply_start_date AND te3.apply_end_date AND ( sh2.license_code = '00' OR ( sh2.license_code != '00' AND li2.licenseditem_adopt_flag != '2' ) ) </insert> </mapper> sql改善要求:  1.SQL分割して並行して行う   2.INSERT処理をストアドに変更 対象レコードを絞る   1.仮想ビュー追加   2.一部検索条件を結合条件に変更 不影响插入数据的情况下怎么改,
07-30
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="jp.co.sej.ssc.or.common.db.BatchDisplayOrderSendDAO"> <!-- 陳列順送信ワークに登録する。 --> <update id="delDisplayOrderSend"> TRUNCATE TABLE w_display_order_send </update> <insert id="insertDisplayOrderSend"> INSERT INTO w_display_order_send( store_code , item_code , gondola_furniture_type , display_gondola , display_shelf , display_row , display_face , display_order_input_datetime , extraction_date , update_datetime , update_function_id , update_person_id , update_count ) SELECT tmp.store_code , tmp.item_code , CASE WHEN tmp.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(tmp.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tmp.display_gondola , tmp.display_shelf , tmp.display_row , tmp.display_face , tmp.display_order_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM ( SELECT te1.store_code , tn.item_code , gl1.gondola_furniture_type , tn.display_gondola , tn.display_shelf , tn.display_row , tn.display_face , tn.display_order_input_datetime , ROW_NUMBER() OVER ( PARTITION BY te1.store_code , tn.item_code , tn.label_code ORDER BY tn.display_order_input_datetime DESC ) AS registrationOrder FROM m_store_number AS te1 INNER JOIN m_display AS tn ON te1.original_store_code = tn.original_store_code LEFT JOIN m_gondola_layout AS gl1 ON gl1.version = #{gondolaLayoutVersion} AND tn.original_store_code = gl1.original_store_code AND tn.display_gondola = gl1.gondola_number WHERE te1.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te1.apply_start_date AND te1.apply_end_date ) AS tmp WHERE tmp.registrationOrder <= 2 UNION ALL SELECT DISTINCT te2.store_code , sh1.item_code , CASE WHEN gl2.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl2.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt1.display_gondola , tt1.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt1.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te2 INNER JOIN m_specific_display_order AS tt1 ON te2.original_store_code = tt1.original_store_code LEFT JOIN m_gondola_layout AS gl2 ON gl2.version = #{gondolaLayoutVersion} AND tt1.original_store_code = gl2.original_store_code AND tt1.display_gondola = gl2.gondola_number INNER JOIN m_pattern AS pt ON pt.version = #{patternVersion} AND tt1.original_store_code = pt.original_store_code AND #{systemDate} :: date BETWEEN pt.apply_start_date AND pt.apply_end_date INNER JOIN m_item AS sh1 ON sh1.version = #{itemVersion} AND pt.pattern_type = sh1.pattern_type AND pt.pattern_code = sh1.pattern_code AND #{systemDate} :: date BETWEEN sh1.apply_start_date AND sh1.apply_end_date AND tt1.information_category_code = sh1.information_category_code LEFT JOIN m_license AS li1 ON li1.version = #{licenseVersion} AND te2.original_store_code = li1.original_store_code AND sh1.license_code = li1.license_code LEFT JOIN m_item_by_specific_store_recommendation ts ON ts.version = #{specificItemVersion} AND tt1.original_store_code = ts.original_store_code AND sh1.item_code = ts.item_code AND #{systemDate} :: date BETWEEN ts.apply_start_date AND ts.apply_end_date WHERE te2.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te2.apply_start_date AND te2.apply_end_date AND ( sh1.license_code = '00' OR ( sh1.license_code != '00' AND li1.licenseditem_adopt_flag != '2' ) ) AND ( sh1.specific_item_type = ' ' OR ( sh1.specific_item_type IN ('1', '2', 'G') AND ts.original_store_code IS NOT NULL ) ) UNION ALL SELECT DISTINCT te3.store_code , '999999' , CASE WHEN gl3.gondola_furniture_type IS NULL THEN NULL ELSE LPAD(TRIM(gl3.gondola_furniture_type), 3, '0') END AS gondola_furniture_type , tt2.display_gondola , tt2.display_shelf , NULL :: numeric AS display_row , NULL :: numeric AS display_face , tt2.special_display_input_datetime , #{systemDate} :: date , CURRENT_TIMESTAMP , #{taskId} , #{taskId} , 0 FROM m_store_number AS te3 INNER JOIN m_specific_display_order_by_specific_category AS tt2 ON te3.original_store_code = tt2.original_store_code LEFT JOIN m_gondola_layout AS gl3 ON gl3.version = #{gondolaLayoutVersion} AND tt2.original_store_code = gl3.original_store_code AND tt2.display_gondola = gl3.gondola_number INNER JOIN m_recommendation_group_by_specific_category AS rg ON rg.version = #{recommendationGroupVersion} AND tt2.original_store_code = rg.original_store_code AND #{systemDate} :: date BETWEEN rg.apply_start_date AND rg.apply_end_date INNER JOIN m_item_by_specific_category AS sh2 ON sh2.version = #{itemSpecificCategory} AND rg.recommendation_group_type = sh2.recommendation_group_type AND rg.recommendation_group_code = sh2.recommendation_group_code AND #{systemDate} :: date BETWEEN sh2.apply_start_date AND sh2.apply_end_date AND tt2.information_category_code = sh2.information_category_code LEFT JOIN m_license AS li2 ON li2.version = #{licenseVersion} AND te3.original_store_code = li2.original_store_code AND sh2.license_code = li2.license_code WHERE te3.version = #{storeVersion} AND #{systemDate} :: date BETWEEN te3.apply_start_date AND te3.apply_end_date AND ( sh2.license_code = '00' OR ( sh2.license_code != '00' AND li2.licenseditem_adopt_flag != '2' ) ) </insert> </mapper> sql优化:1. 拆分 SQL 并并行运行 2. 将 INSERT 过程更改为存储过程
07-30
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值