
-- Build 02 Sprint 05 Team 8 SQL server updates.

USE [ccrsdb]
GO

-- add new columns to existing tables

alter table [das].[PMPM_INVOICE] add [report_timestamp] datetime2(0) NULL

GO

-- add new tables

CREATE TABLE [das].[PMPM_BATCH_LOG](
	[pmpm_batch_log_key] [bigint] IDENTITY(1,1) NOT NULL,
	[content_type] [varchar](50) NULL,
	[data] [varchar](50) NULL,
	[url] [varchar](100) NULL,
	[size] [bigint] NULL,
	[hash] [varchar](100) NULL,
	[title] [varchar](100) NULL,
	[status] [varchar](10) NULL,
	[report_timestamp] [datetime2](0) NULL,
 CONSTRAINT [PK_das_PMPM_BATCH_LOG_pmpm_batch_log_key] PRIMARY KEY CLUSTERED 
(
	[pmpm_batch_log_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [das].[PMPM_MESSAGE_TEXT](
	[pmpm_message_text_key] [bigint] IDENTITY(1,1) NOT NULL,
	[code_letters] [varchar](50) NULL,
	[full_text] [varchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[pmpm_message_text_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


-- add data to messages table

INSERT INTO [das].[PMPM_MESSAGE_TEXT] ([code_letters], [full_text]) VALUES  
('INSTRUCTIONS', 'NEVER USE A COMMA. Colons OK.'),
('BadF001', 'Incorrect Referral Number format'),
('BadF002', 'Incorrect Referral Number format'),
('BadF003', 'Incorrect Invoice Date format'),
('BadT101', 'Bad PMPM Invoice Transmission'),
('BadT102', 'PMPM Invoice Report Total mismatch'),
('BadT103', 'PMPM Invoice Record Count tranmission mismatch'),
('LoadFail5', 'Test Load failure: bad footer totals'),
('BadL201', 'Invoice Line Validation failure: Referral not found'),
('BadL202', 'Invoice Line Validation failure'),
('BadL203', 'Invoice Line Validation failure'),
('BadL204', 'Invoice Line Validation failure'),
('BadL210', 'Invoice Line Validation failure'),
('BadFLDCNT', 'Invoice Line error: incorrect number of fields'),
('BadF004', 'Incorrect Invoice Line Service Date format'),
('BadF005', 'TBD'),
('BadF006', 'TBD'),
('BadF007', 'Incorrect Invoice Line Paid Amount format'),
('BadF008', 'Incorrect Invoice Line Billed Amount format'),
('BadF009', 'TBD'),
('BadF010', 'TBD'),
('BadF011', 'Incorrect Invoice Line CLIN format'),
('LoadOK', 'Invoice line loaded correctly'),
('VerfL1OK', 'Invoice Line Verified OK'),
('Loading', 'Invoice Lines intermediate processing OK')

GO

-- add new stored procedures

/****** Object:  StoredProcedure [dbo].[PMPM_RPT01_MONTH_SUMM_HTML]    Script Date: 4/5/2018 3:45:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[PMPM_RPT01_MONTH_SUMM_HTML]  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	-- declare @rowk bigint;
	SET NOCOUNT ON;
	select '<html><head><title>CCRS PMPM Verification</title><style>';
	select 'table, tr, td, th {border:1px solid black;border-collapse:collapse;outline-offset:5px;}';
	select '</style></head><body><img src="https://www.oit.DOMAIN.EXT/design-guide/_media/img/logos/OIT_logo.png" alt = "VA logo">';
	select '<h1>CCRS PMPM Verification</h1><h2>Transmission Summary</h2><table>';

	select '<tr><td>Region:</td><td>Region 1</td></td>';

      declare @tline varchar(250);
      set @tline = (select top (1) transmit_line from das.pmpm_history where status = 'Header');
	  declare @x1ptr integer = charindex('^', @tline);
	  declare @x2ptr integer = charindex('^', @tline, @x1ptr + 1);
	  declare @x3ptr integer = charindex('^', @tline, @x2ptr + 1);
	  declare @int1 integer;

      -- print @tline + str(@x1ptr) + str(@x2ptr) +str(@x3ptr);
	  -- select '<tr><td>Invoice Start Date:</td><td>' + substring(@tline, @x1ptr + 1, @x2ptr - @x1ptr - 1) + '</td></tr>';
	  select '<tr><td>Invoice Start Date:</td><td>' + substring(@tline, @x1ptr + 5, 2) +
	  '/' + substring(@tline, @x1ptr + 7, 2) + '/' + substring(@tline, @x1ptr + 1, 4) + '</td></tr>';
	  	  select '<tr><td>Invoice End Date:</td><td>' + substring(@tline, @x2ptr + 5, 2) +
	  '/' + substring(@tline, @x2ptr + 7, 2) + '/' + substring(@tline, @x2ptr + 1, 4) + '</td></tr>';
	  -- select '<tr><td>Invoice End Date:</td><td>' + substring(@tline, @x2ptr + 1, @x3ptr - @x2ptr - 1) + '</td></tr>';
	  select '<tr><td>Report Generation Date:</td><td>' + format(CURRENT_TIMESTAMP, 'MM/dd/yyyy') + '</td></tr>';

	  select '<tr><td>File URL:</td><td>/binary/document/CCN-Region1-20180205-20180209.txt</td></tr>';

	  select @int1 = count(1) from das.pmpm_invoice;
	  select '<tr><td>Total Records:</td><td>' + str( @int1) + '</td></tr>';

	  select @int1 = count(1) from das.pmpm_invoice where status = 'LoadOK';
	  select '<tr><td>Total Valid Records:</td><td>' + str( @int1) + '</td></tr>';

	  select '</table><h3>Transmission Notes</h3><table><tr><td>';
	  select 'Transmission nominal.';
	  select '</td></tr></table><h2>Details per CLIN</h2>';
	  -- select 'Report just has record counts-- testing with totals.'
	  select '<table><tr><th>CLIN</th><th>Date Paid</th><th>Total Records</th><th>Total Valid</th><th>Total Invalid</th></tr>';
	  -- originally "Date of Report" but test data was pretty boring
	  -- select '</table>';
	  -- select '<table><tr><th>CLIN</th><th>Total Records</th><th>Total Paid</th><th>Total Billed</th></tr>';

	  declare @clinCode varchar(50), @totRec int, @totPaid decimal(18,2), @totBilled decimal(18,2);
	  declare @thisDate date, @totValid int, @totInvalid int;
	 --  declare c01 cursor for select clin_code, count(1) as 'TotRec' , sum(amount_paid) as 'TotPaid' ,
     --   sum(billed_amount) as 'TotBilled' from das.pmpm_invoice
     --   where status = 'LoadOK'  group by clin_code;

	  declare c01 cursor for select distinct clin_code, date_paid from das.pmpm_invoice;

	open c01;

	fetch next from c01 into @clinCode, @thisDate;
	while @@FETCH_STATUS = 0
	begin
	    select @totValid = sum(1) from das.pmpm_invoice where clin_code = @clinCode and date_paid = @thisDate and status = 'LoadOK';
		select @totInvalid = sum(1) from das.pmpm_invoice where clin_code = @clinCode and date_paid = @thisDate and status <> 'LoadOK';

		select '<tr><td>' + @clinCode + '</td><td>' + format(@thisDate, 'MM/dd/yyyy') + '</td><td>' + str(@totValid + @totInvalid) + '</td><td>' 
		+ str(@totValid) + '</td><td>' + str(@totInvalid) + '</td></tr>';
		fetch next from c01 into @clinCode, @thisDate;
	end

	select '</table>';

	close c01
	deallocate c01

	  select '<p><i>April 5, 2018: only REGION 1 coded for demo.</i></body></html>';

END

GO


CREATE  PROCEDURE [dbo].[PMPM_RPT02_MONTH_DETAIL_CSV]  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	-- declare @rowk bigint;
	SET NOCOUNT ON;
	select 'Date of Report,CLIN,Referral ID,ICN,Date of Service,Date Paid,Valid,Reject Reason,Reject Code,Invoice Key';

	 select  cast(format(current_timestamp, 'MM/dd/yyyy') + ',' + clin_code + ',' + referral_number+','+ icn + ',' +
	 format(date_of_service, 'MM/dd/yyyy')
	 + ',' + format(date_paid, 'MM/dd/yyyy') + ',Y,OK,' + status + ',' + cast(pmpm_invoice_key as varchar(12)) as varchar(150))
	  from das.pmpm_invoice
      where status = 'LoadOK';

 -- select  cast(format(current_timestamp, 'MM/dd/yyyy') + left(clin_code,6) + ',' + clin_code + ','+ str(len(clin_code)) +':'+ str(datalength(clin_code)) + ' ' + referral_number+','+ icn + ',' +
	select  cast(format(current_timestamp, 'MM/dd/yyyy') + ',' + clin_code + ',' + referral_number+','+ icn + ',' +

	 format(date_of_service, 'MM/dd/yyyy')
	 + ',' + format(date_paid, 'MM/dd/yyyy') + ',N,' + dpmt.full_text + 
	 ',' + status + ',' + cast(pmpm_invoice_key as varchar(12)) as varchar(150))
	  from das.pmpm_invoice dpi inner join das.PMPM_MESSAGE_TEXT dpmt on dpi.status = dpmt.code_letters
      where status <> 'LoadOK';


END

GO
