
alter table "ChargeModuleCategory" add "ChargeModuleRefTemplateId" int

--------------------------------------------------------------------------------------------------------------------------------------------

create sequence "ChargeModuleTemplateByLog_ChargeModuleTemplateByLogId_seq";
CREATE TABLE IF NOT EXISTS public."ChargeModuleTemplateByLog"
(
    "ChargeModuleTemplateByLogId" integer NOT NULL DEFAULT nextval('"ChargeModuleTemplateByLog_ChargeModuleTemplateByLogId_seq"'::regclass),
    "TemplateId" integer,
    "StartDate" date,
    "EndDate" date,
    "LocationId" integer,
    "RefTemplateId" integer,
    "ModulesMasterId" integer,
    "IncreaseType" character varying(10) COLLATE pg_catalog."default",
    "IncreaseValue" numeric,
    "CreatedDate" timestamp without time zone DEFAULT now(),
    CONSTRAINT "ChargeModuleTemplateByLog_pkey" PRIMARY KEY ("ChargeModuleTemplateByLogId")
);



--------------------------------------------------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS public.upgrade_chargetemplate(integer, integer, text, timestamp without time zone, timestamp without time zone, integer, character varying, numeric);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.upgrade_chargetemplate(
	chargetemplateid integer,
	locationid integer,
	templatename text,
	startdate timestamp without time zone,
	endtdate timestamp without time zone,
	modulesmasterid integer,
	increasetype character varying,
	increasevalue numeric)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  new_id INTEGER;
BEGIN
  -- Insert the new user and retrieve the value of the id column into the new_id variable

INSERT INTO public."ChargeModuleTemplate"(
	 "StartDate", "EndDate", "Active", "TemplateName", "CreatedBy", "CreatedDate", "LocationId")
	  VALUES (startdate,endtdate,True,TemplateName,6776,now(),LocationId)
	 RETURNING "ChargeModuleTemplateId" INTO new_id;
  
  
INSERT INTO public."ChargeModuleCategory"(
	 "Active", "ChargeCategoryId", "ModulesMasterId", "ChargeModuleTemplateId", "ChargeModuleRefTemplateId", "CreatedBy", "CreatedDate")
	--VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); 
	
	select  "Active", "ChargeCategoryId", "ModulesMasterId",new_id as "ChargeModuleTemplate", ChargeTemplateId as "ChargeModuleRefTemplateId"
	, 6776 "CreatedBy",now () as "CreatedDate"
	from  public."ChargeModuleCategory"  where "ChargeModuleTemplateId"=ChargeTemplateId
	and case when ModulesMasterId is null then 1=1 else  "ModulesMasterId"=ModulesMasterId end 
	;
  
  
 INSERT INTO public."ChargeModuleDetails"(
 "ReferenceId", "ChargeModuleCategoryId", "Amount", "LocationId", "CreatedBy", "CreatedDate")
	--VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
	
select b."ReferenceId",a."Refcatid", 
case when IncreaseType='P' then b."Amount"+round((b."Amount"*IncreaseValue)/100,2)
	 when IncreaseType='A' then b."Amount"+IncreaseValue else b."Amount" end 
, b."LocationId",6776,now()  from (
select  a."ChargeModuleCategoryId",b."ChargeModuleCategoryId" as "Refcatid" from "ChargeModuleCategory" a
right join "ChargeModuleCategory" b on a."ChargeModuleTemplateId"=b."ChargeModuleRefTemplateId"
	and a."ChargeCategoryId"=b."ChargeCategoryId" and  a."ModulesMasterId"=b."ModulesMasterId"

where b."ChargeModuleRefTemplateId"=ChargeTemplateId and  b."ChargeModuleTemplateId"=new_id
	and a."ChargeModuleTemplateId"=ChargeTemplateId

) a
join 	 "ChargeModuleDetails" b on a."ChargeModuleCategoryId"=b."ChargeModuleCategoryId";
  
  INSERT INTO  "ChargeModuleTemplateByLog" ("TemplateId","StartDate" ,"EndDate" ,"LocationId",
"RefTemplateId","ModulesMasterId" ,"IncreaseType","IncreaseValue"	)
 select  new_id,startdate,endtdate,LocationId,ChargeTemplateId,ModulesMasterId,IncreaseType,IncreaseValue
  ;
  -- Return the new_id value
  RETURN new_id;
END;
$BODY$;

-------------------------------------------------------------------------------------------------------------------------------------------------

insert into "LabLogType" ("LogTypeName","Active")
values('Lab_Masters_Machine',true),('Lab_Masters_Parameter',true),
('Lab_Masters_Component',true),('Lab_Masters_Template',true),
('Lab_Cancelling_Test',true),('Lab_Sample_Collection',true),
('Lab_Sample_Transfer',true),('Lab_Sample_Receive',true);

---------------------------------------------------------------------------------------

ALTER TABLE IF EXISTS public."PackageModule" ADD COLUMN "ExpiresIn" smallint;