tr_updatepersoninfo.sql

十分好的一个医院管理系统

                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=0
                      select @m9_n=round(@thisadd / 4,1)
                      select @m10_n=round(@thisadd / 4,1)
                      select @m11_n=round(@thisadd / 4,1)
                      select @m12_n=@thisadd - round(@thisadd / 4,1)*3
                    end
                  if @month_jg = -9 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=0
                      select @m9_n=0
                      select @m10_n=round(@thisadd / 3,1)
                      select @m11_n=round(@thisadd / 3,1)
                      select @m12_n=@thisadd - round(@thisadd / 3,1)*2
                    end
                  if @month_jg = -10 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=0
                      select @m9_n=0
                      select @m10_n=0
                      select @m11_n=round(@thisadd / 2,1)
                      select @m12_n=@thisadd - round(@thisadd / 2,1)*1
                    end
                  if @month_jg = -11 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=0
                      select @m9_n=0
                      select @m10_n=0
                      select @m11_n=0
                      select @m12_n=@thisadd
                    end
                  if @month_jg <= -12 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=0
                      select @m9_n=0
                      select @m10_n=0
                      select @m11_n=0
                      select @m12_n=0
                    end


                  if @d1 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m1_o
                       select @dis_n = @dis_n+@m1_n
                     end 
                  if @d2 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m2_o
                       select @dis_n = @dis_n+@m2_n
                     end 
                  if @d3 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m3_o
                       select @dis_n = @dis_n+@m3_n
                     end 
                  if @d4 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m4_o
                       select @dis_n = @dis_n+@m4_n
                     end 
                  if @d5 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m5_o
                       select @dis_n = @dis_n+@m5_n
                     end 
                  if @d6 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m6_o
                       select @dis_n = @dis_n+@m6_n
                     end 
                  if @d7 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m7_o
                       select @dis_n = @dis_n+@m7_n
                     end 
                  if @d8 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m8_o
                       select @dis_n = @dis_n+@m8_n
                     end 
                  if @d9 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m9_o
                       select @dis_n = @dis_n+@m9_n
                     end 
                  if @d10 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m10_o
                       select @dis_n = @dis_n+@m10_n
                     end 
                  if @d11 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m11_o
                       select @dis_n = @dis_n+@m11_n
                     end 
                  if @d12 > @date_ini 
                     begin
                       select @dis_o = @dis_o+@m12_o
                       select @dis_n = @dis_n+@m12_n
                     end 
                  
                  update dislist set thisadd=@thisadd,m1=@m1_n,m2=@m2_n,m3=@m3_n,m4=@m4_n,m5=@m5_n,m6=@m6_n,m7=@m7_n,m8=@m8_n,m9=@m9_n,m10=@m10_n,m11=@m11_n,m12=@m12_n
                  where personcode = @personcode and audityear = @audityear
                  if @@error <> 0 
                       begin
                          raiserror 33333 "错误"  
                          close inserted_curs    
                          deallocate cursor inserted_curs
                          rollback transaction
                          return
                       end

                  update personinfo set thisleave = thisleave+(@dis_n - @dis_o),thisaddmonth=@thisaddmonth,updateflag='111111111111111111111111111111111111111111111111111111111111'
                  where personcode = @personcode
                  if @@error <> 0 
                       begin
                          raiserror 33333 "错误"  
                          close inserted_curs    
                          deallocate cursor inserted_curs
                          rollback transaction
                          return
                       end
              end

              fetch  inserted_curs into @personcode,@name,@bookindate,@audityear,@thisadd
            end
            
         close inserted_curs
         deallocate cursor inserted_curs
      END

   IF UPDATE (name) or update(bookindate)
      BEGIN
 	   declare inserted_curs2 cursor for
	   select personcode,name,audityear,bookindate
	   from inserted
       
	   open inserted_curs2
	   fetch  inserted_curs2 into 
               @personcode,@name,@audityear,@bookindate
               
	   if @@sqlstatus = 2 
	      begin
            close inserted_curs2
            deallocate cursor inserted_curs2
            rollback tran
            return 
	      end       
          
	    while (@@sqlstatus = 0 ) 
           begin  
               update dislist set name = @name,bookindate=@bookindate where personcode=@personcode and audityear=@audityear
                if @@error <> 0 
                     begin
                       raiserror 33333 "错误"  
                       close inserted_curs2 
                       deallocate cursor inserted_curs2
                       rollback transaction
                       return
                     end
	          fetch  inserted_curs2 into @personcode,@name,@audityear,@bookindate
           end

         close inserted_curs2 
         deallocate cursor inserted_curs2
     END