Testing Mo

drop table #tempvaccination
go
drop table #temp1
go

select  e.pv_type,
    a.pres_no,
        mn = month(a.pres_dt),
    a.pres_dt,
    d.stock_code,
    d.brand_name,
    c.pres_qty
  
into #tempvaccination

from  prescription a,  
    prescription_item b,  
    prescription_stock_line c,
    stock d,
    patient_visit e

where  a.pres_dt between '01-01-2008' and '31-12-2008 23:59:59'
and  a.pres_no = b.pres_no
and    b.pres_no = c.pres_no
and    b.pres_item = c.pres_item
and    c.stock_code = d.stock_code
and    d.Vaccine_No_Dose > 0
and    b.status <> 8
and    a.visit_no = e.visit_no

order by a.pres_dt

-- extract by month
select pv_type,
       stock_code,  
       brand_name,  
       mn,  
       pres_qty = sum(pres_qty)
into   #temp1
from   #tempvaccination
group by pv_type,stock_code, brand_name, mn
order by 1,2,3

select  stock_code,  
    brand_name,
    pv_type case when pv_type = 1 then 'IP'  
                     when pv_type = 2 then 'OP'
       when pv_type = 4 then 'WK' end,  
        Jan = sum(case when mn = 1 then pres_qty else 0 end),
        Feb = sum(case when mn = 2 then pres_qty else 0 end),
        Mar = sum(case when mn = 3 then pres_qty else 0 end),
        Apr = sum(case when mn = 4 then pres_qty else 0 end),
        May = sum(case when mn = 5 then pres_qty else 0 end),
        Jun = sum(case when mn = 6 then pres_qty else 0 end),
        Jul = sum(case when mn = 7 then pres_qty else 0 end),
        Aug = sum(case when mn = 8 then pres_qty else 0 end),
        Sep = sum(case when mn = 9 then pres_qty else 0 end),
        Oct = sum(case when mn = 10 then pres_qty else 0 end),
        Nov = sum(case when mn = 11 then pres_qty else 0 end),
        Dec = sum(case when mn = 12 then pres_qty else 0 end)


from #temp1

group by pv_type, stock_code, brand_name

order by 3,1

declare @prev_request_no char(13)

declare @request_no char(13)

declare @fee_type_code char(2)

declare @fee_code char(5)

declare @seq_no int

  

set @prev_request_no = ''

DECLARE xx CURSOR FOR

select request_no, fee_type_code, fee_code  

from xray_request_detail  

where seq_no is null  

order by request_no

  

OPEN xx

  

FETCH NEXT FROM xx into @request_no, @fee_type_code, @fee_code  

WHILE @@FETCH_STATUS = 0

BEGIN

        if @prev_request_no <> @request_no

           set @seq_no = 0

        select @seq_no = @seq_no + 1

  

        update xray_request_detail set seq_no= @seq_no

        where     request_no= @request_no and  

                fee_type_code = @fee_type_code and  

                fee_code = @fee_code

  

        set @prev_request_no = @request_no

        FETCH NEXT FROM xx into @request_no, @fee_type_code, @fee_code  

END

  

CLOSE xx

DEALLOCATE xx

 

--start
declare @No int
declare @NoOfRequestNo int

declare @lsRequestNo char(13)
declare @NoOfSameRequestNo int
declare @Counter int

set @No=1

Declare @tempRequestNo table(  
Request_No    SysNo
)  

Declare @tempRequest table(  
Request_No    SysNo,
Fee_Type_Code    FeeType,
Fee_Code    FeeCode,
Fee_Code_Remark    Desc60,
LastUpdate    datetime
)  

Declare @tempRequestFinal table(  
Request_No    SysNo,
Seq_No SeqNo,
Fee_Type_Code    FeeType,
Fee_Code    FeeCode,
Fee_Code_Remark    Desc60,
LastUpdate    datetime
)  

insert into @tempRequestNo
select distinct Request_No from XRay_Request_Detail

select @NoOfRequestNo = count(*)
from @tempRequestNo



while @No<=@NoOfRequestNo
begin

    select top (1) @lsRequestNo = Request_No  
    from (    select top (@No) Request_No from  
      @tempRequestNo order by Request_No desc) aa
    order by Request_No asc


    delete from @tempRequest
    insert into @tempRequest
    select    Request_No,
    Fee_Type_Code,
    Fee_Code,
    Fee_Code_Remark,
    LastUpdate
    from XRay_Request_Detail
    where XRay_Request_Detail.Request_No = @lsRequestNo


    select @NoOfSameRequestNo = count(*)
    from @tempRequest


    select * from @tempRequest

    set @counter = 1
    while @counter<=@NoOfSameRequestNo
  begin

      Insert into @tempRequestFinal  
      select top (1)  
    Request_No,
    Seq_No = @counter,
    Fee_Type_Code,
    Fee_Code,
    Fee_Code_Remark,
    LastUpdate
      from @tempRequest

      delete top(1) from @tempRequest


      set @counter=@counter+1

  end


    set @No=@No+1

end

select * from @tempRequestFinal


--end

create table #temp(
fee_code char(5)
)

insert into #temp values('0013')

insert into Lab_Test_Specimen (Fee_Type_Code,Fee_Code,LS_Code,LastUpdate)
select    'LA', a.Fee_Code, b.LS_Code, getdate()
from    Lab_Test a,
    Lab_Specimen b
where    a.Allow_LOE = 1
and    a.fee_type_code = 'LA'
and    a.Fee_Code not in (select distinct Fee_Code from Lab_Test_Specimen)

if exists (select * from sysobjects where id = object_id('dbo.usp_LOERPT001') and sysstat & 0xf = 4)
    drop procedure dbo.usp_LOERPT001
GO

CREATE PROCEDURE usp_LOERPT001
@Request_No    char(13),
@Special_Print_Criteria    char(13)
AS

-- V1.01

-- declare variable
declare @spv_Visit_No char(13)
declare @spv_Hosp_Code Code5
declare @spv_Hosp_EngName Desc30
declare @spv_Hosp_ChiName ChiDesc30

declare @spv_Bed_No Code5
declare @spv_Bed_Dept_Code Code5
declare @Ext_Of_Ward_Phone Desc30
declare @spv_DoctorName_Copy1 Nvarchar(60)
declare @spv_DoctorName_Copy2 Nvarchar(60)
declare @spv_User_Full_Name Desc30

Declare @temp table(
Request_No      char(13),
Fee_Code  char(5),
Fee_Type_Code    char(2),
Fee_Code_EngDesc      varchar(60),
Qty    int,
Test_Type_Desc char(60),
Test_Type_Code char(1)
)

SELECT    @spv_Visit_No = Visit_No
From    Lab_Request
WHERE    Request_No = @Request_No


SELECT    @spv_Bed_No = Bed_Allocation.Bed_No,
  @spv_Bed_Dept_Code = Bed_Allocation.Dept_Code,
  @Ext_Of_Ward_Phone = rtrim(Department.Dept_Code) + ' (' + isnull(Department.Dept_Phone,' ') +')'

FROM    Patient_Visit, Bed_Allocation, Department
WHERE    Patient_Visit.Visit_No = Bed_Allocation.Visit_No  
AND  Patient_Visit.Visit_No = @spv_Visit_No --'hn20080603598'
AND  Bed_Allocation.Current_Bed = 1
AND  Bed_Allocation.Dept_code = Department.Dept_code

--JT Added to retreive the hospital information
select    @spv_Hosp_Code = Hospital_Code,  
  @spv_Hosp_ChiName = Hospital_ChiName,
  @spv_Hosp_EngName = Hospital_EngName
from    Hospital  

-- retrieve fee code
Insert into @temp
Select  Lab_Request_Detail.Request_No,
  Lab_Request_Detail.Fee_Code,
  Lab_Request_Detail.Fee_Type_Code,
  Fee_Code.Fee_Code_EngDesc,
  Lab_Request_Detail.Qty,
  Lab_Test_Type.Test_Type_Desc,
  Lab_Test_Type.Test_Type_Code
From  Lab_Request_Detail,
  Fee_Code,
  Lab_Test_Type,
  Lab_Test

Where  Lab_Request_Detail.Request_No  = @Request_No -- 'LR20081100020'
and  Fee_Code.Fee_Code    = Lab_Request_Detail.Fee_Code
and  Fee_Code.Fee_Type_Code  = Lab_Request_Detail.Fee_Type_Code
and  Lab_Test.Test_Type_Code  = Lab_Test_Type.Test_Type_Code
and  Fee_Code.Fee_Code    = Lab_Test.Fee_Code
and  Fee_Code.Fee_Type_Code  = Lab_Test.Fee_Type_Code
and  Lab_Test_Type.Test_Type_Code  =    case @Special_Print_Criteria
          when 'LabBloodBank' then 'B'  
          else Lab_Test_Type.Test_Type_Code end  
and    Lab_Test_Type.Test_Type_Code <>  case @Special_Print_Criteria
        when 'Lab' then  'B'  
        else '' end  

order by      Lab_Test_Type.Test_Type_Code
    ,Lab_Request_Detail.Fee_Code

---- insert for audit log
--Insert into HISFunction_Audit (Func_Code, User_Code, WS_Name, LastUpdate, Visit_No)
--values ('LOERPT001', suser_sname(), host_name(), getdate(), @Visit_No)

-- output to crystal report
SELECT    Patient_Visit.Visit_No,
  Patient_Visit.PV_Visit_DT,
  Patient_Visit.Patient_No,
  Patient_Visit.PV_Surname,
  Patient_Visit.PV_Given_Name,
  Patient_Visit.ChiName,
  Patient_Visit.PV_IDNo,
  Patient_Visit.PV_IDNo_CD,
  Patient_Visit.PV_Sex,
  Patient_Visit.PV_DOB,
  Age = dbo.uf_sCalculateAge(Patient_Visit.PV_DOB,Patient_Visit.PV_DOB_YY,getdate()),
  Patient_Visit.PV_Address,
  D1.Doctor_Code,
  D1.Doctor_Surname,
  D1.Doctor_GivenName,
  D1.Doctor_Engname,
  Hospital_Code = @spv_Hosp_Code,
  Hospital_EngName = @spv_Hosp_EngName,
  Hospital_ChiName = @spv_Hosp_ChiName,
  Lab_Request.Request_No,
  Lab_Request.Service_DT,
  Biohazard = case when Lab_Request.Biohazard = 1 then 'Known' + Lab_Request.Biohazard_Desc else 'Unknown' end,
  Lab_Request.Diagnosis,
  Lab_Request.LS_Desc,
  Lab_Request.Allergy,
  Lab_Request.Visit_No,
  Lab_Request.Request_DT,
  Lab_Request.Biohazard_Desc,  
  Lab_Request.Blood_Transfusion_HB,
  Lab_Request.Blood_Transfusion_Platelet,
  Lab_Request.Blood_Transfusion_WBC,
  Lab_Request.Blood_Transfusion_PT,
  Lab_Request.Blood_Transfusion_INR,
  Lab_Request.Blood_Transfusion_APTT,
  Lab_Request.Blood_Transfusion_Fibrinogen,
  Lab_Request.Blood_Transfusion_Desperate,
  Lab_Request.Blood_Transfusion_Urgent,
  Lab_Request.Blood_Transfusion_Need,
  Lab_Request.Blood_Transfusion_DT,
  Lab_Request.Blood_Transfusion_Reserve,
  Lab_Request.Blood_Transfusion_Reserve_OT,
  Lab_Request.Blood_Transfusion_Reserve_OT_Desc,
  Lab_Request.Blood_Transfusion_Reserve_OT_DT,
  Lab_Request.Blood_Transfusion_Need_Time,
  Lab_Request.Blood_Transfusion_OT_Time,
  Lab_Request.Blood_Transfusion_On_Schedule,
  Lab_Request.Blood_Transfusion_Chronic_Anemia,
  Lab_Request.Blood_Transfusion_Marrow_Failure,
  Lab_Request.Blood_Transfusion_Active_Massive_Blood_Loss,
  Lab_Request.Blood_Transfusion_Diffuse_Micro_Vascular_Bleeding,
  Lab_Request.Blood_Transfusion_Prophylactic_Platelet_Therapy,
  Lab_Request.Blood_Transfusion_Others,
  Lab_Request.Blood_Transfusion_Others_Desc,
  Lab_Request.Urine_Period_From_DT,
  Lab_Request.Urine_Period_To_DT,
  Lab_Request.Urine_Patient_Height,
  Lab_Request.Urine_Patient_Weight,
  Lab_Request.Urine_From_Time,
  Lab_Request.Urine_To_Time,
  Lab_Request.Urine_Creatinine_Time,
  Lab_Request.Urine_Creatinine_Blood_DT,
  Lab_Request.Microbiology_ABx_Desc,
  Lab_Request.Microbiology_ABx_To_Be_Used,
  Lab_Request.Doctor_Code_Other1,
  Lab_Request.Doctor_Code_Other2,
  Lab_Request.Remarks,
  Lab_Request.Save_Serum,
  Lab_Request.Save_Serum_for_X_Match,
  Lab_Request.Stat_Phlebotomy,
  Lab_Request.Stat_Report,
  Lab_Request.Specimens_By_Ward,
  Lab_Request.Specimens_By_Lab,
  Lab_Request.Specimens_By_Patient,
  Lab_Request.Other_Exam,
  Lab_Request.Fasting,
  Lab_Request.Fasting_Time,
  Lab_Request.Old_Sample,
  Lab_Request.Old_Sample_Date_Desc,
  Lab_Request.Old_Sample_Cancel_Request,
  Lab_Request.Old_Sample_Draw_Blood_Again,
  Lab_Request.Old_Sample_Draw_Blood_Again_Date_Desc,
  Lab_Request.Immunology_Time_AM,
  Lab_Request.Immunology_Time_PM,
  Lab_Request.Blood_Gas_Type,
  temp1.Fee_Code,
  temp1.Fee_Code_EngDesc,
  temp1.Qty,
  temp1.Test_Type_Desc,
  temp1.Test_Type_Code,
  Doctor_Order = D1.Doctor_Code + ' - ' + LTrim(Rtrim(D1.Doctor_Surname)) + ' '+D1.Doctor_GivenName+' '+D1.Doctor_Engname + ' ' + D1.Chiname,
  Doctor_Copy1 = D2.Doctor_Code + ' - ' + LTrim(Rtrim(D2.Doctor_Surname)) + ' '+D2.Doctor_GivenName+' '+D2.Doctor_Engname + ' ' + D2.Chiname,
  Doctor_Copy2 = D3.Doctor_Code + ' - ' + LTrim(Rtrim(D3.Doctor_Surname)) + ' '+D3.Doctor_GivenName+' '+D3.Doctor_Engname + ' ' + D3.Chiname,
  HIS_User.User_Full_Name,
  HIS_User.User_Code,
  Bed_No = @spv_Bed_No,
  Bed_Dept_Code = @spv_Bed_Dept_Code,
  Special_Print_Criteria = @Special_Print_Criteria,
  Ext_Of_Ward_Phone = @Ext_Of_Ward_Phone

FROM    Patient_Visit,
  Doctor D1,
  Doctor D2,
  Doctor D3,
  Lab_Request,
  @temp temp1,
  HIS_User

WHERE    Lab_Request.Doctor_Code        = D1.Doctor_Code
and  Patient_Visit.Visit_No      = @spv_Visit_No
and  Lab_Request.Request_No      = @Request_No
and  Patient_Visit.Visit_No      = Lab_Request.Visit_No
and  Lab_Request.Request_No      *=temp1.Request_No
and     HIS_User.user_Code    = Lab_Request.user_Code
and  Lab_Request.Doctor_Code_Other1    *= D2.Doctor_Code
and  Lab_Request.Doctor_Code_Other2    *= D3.Doctor_Code

GO

GRANT  EXECUTE  ON dbo.usp_LOERPT001  TO HISWG
GO

--exec usp_LOERPT001  'LR20081100020',''
--exec usp_LOERPT001  'LR20081100042',''