Introducing the Boomerang Framework

use Boomerang
go

begin 
set nocount on
set xact_abort on

declare @gKey uniqueidentifier; set @gKey = newid()
declare @jKey uniqueidentifier;
declare @aKey uniqueidentifier;

-- first off: create new event
insert EVENT_MASTER (gKey, Source, Created_By ) values (@gKey, null, 'denisp')

-- create email job
if(1=1) begin

	-- reserve a job key
  set @jKey = newid()

  -- add a job, uncomment one of the following:

	-- email job with subject and priority (being 1 for top important thru 5 for the least, default is 3)
	-- no body, default ReplyTo (see server settings)
	-- see x-priority documentation at http://tools.ietf.org/html/rfc4356
  --insert OUT_EMAIL (gKey,jKey,Subject,XPriority) values (@gKey,@jKey,'test subject',5)

	-- email job with body, reply-to & subject line
	-- this example also demonstrates options for the tracking key - 0 = include none, 1 = at the top (def.), 2 = bottom
  --insert OUT_EMAIL (gKey,jKey,Subject,Body,ReplyTo,IncludeKey) values (@gKey,@jKey,'test','this is a test, ignore','replyto@mydomain.com',0)

	-- email job (same as above) with specific From address
  insert OUT_EMAIL (gKey,jKey,Subject,Body,ReplyTo,[From],IncludeKey) 
    values (@gKey,@jKey,'Please reply to this one',null,'replyto@mydomain.com','"Human description" <from@mydomain.com>',0)


  -- add recepient(s)
	-- add a single e-mail recepient
  insert OUT_EMAIL_RECEPIENT (jKey,[Name],Email) values (@jKey,'John Smith','john.smith@theirdomain.com')

	-- add a single BCC e-mail recepient; Type - 1 = TO, 2 = CC, 3 = BCC
  insert OUT_EMAIL_RECEPIENT (jKey,[Name],Email,Type) values (@jKey,'David Hickory','david.hickory@theirdomain.com',3)

	-- add a CC semicolon-separated list
  exec sp_Add_Email_List @jKey=@jKey, @list='someone_else@yahoo.com;"Mr. Smith" <mr_smith@yahoo.com>', @type=2

  -- add attachement(s)
  if(1=1) begin

    -- add attachment
	-- add attachment as stream of bytes 
	-- with optional file name (otherwise automatic)
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Stream,[Name]) values (@gKey,@jKey,@aKey,0,convert(image,'test'),'test.txt')

	-- add attachment from external file
	-- with optional file name (otherwise original)
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Name]) values (@gKey,@jKey,@aKey,1,'c:\example.txt','test.txt')

	-- add SQL RS report attachment in MHT format;
	-- other format options include: XLS (or EXCEL), PDF, XML, CSV, MHTML, HTM (or HTML, HTML3.2, HTML4.0), JPG (or JPEG), TIF (or TIFF), GIF, PNG, BMP, or IMAGE
	-- for more info on formats refer to SQL RS documentation
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Format]) values (@gKey,@jKey,@aKey,2,'Metrics/FI/FI_Scorecard','MHT')

	-- add SQL RS report attachment in server default format (see service settings) as an in-line attachement, i.e. shows inside body
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,IsAttachment) values (@gKey,@jKey,@aKey,2,'Metrics/OD/OD_Scorecard',0)

	-- add SQL RS report attachment in HTML format as an in-line attachement, i.e. shows inside body
	-- Body field is optional, if not specified, then only content of the report will fill the body, 
	-- otherwise will appear mixed with your body prepending the report
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,IsAttachment,[Format]) values (@gKey,@jKey,@aKey,2,'Metrics/IT/IT_Scorecard',0,'HTML')

	-- add SQL RS report attachment in PDF format
	-- also pass a single parameter to SQL RS report
    set @aKey = newid()
    insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Format]) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS','PDF')
    insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006115619')

  end

end

-- create print job
if(0=1) begin

	-- reserve a job key
  set @jKey = newid()

  -- add a job with reference to printer
  insert OUT_PRINT (gKey,jKey,Printer_Path) values (@gKey,@jKey,'\\server\bar_code_printer010')

	-- add SQL RS report content with a single parameter, automatic orientation and 100% scale factor
	-- note: only Src_Type = 2 supported, i.e. SQL RS
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'RA/RA_Label')
  insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008019231')

	-- add SQL RS report content with a single parameter, automatic orientation and 100% scale factor
	-- note: when report is designed for landscape output, it will be automatically recognized
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/landscape_label')

	-- add SQL RS report content with a single parameter, 
	-- orientation (1 = protrait, 2 = landscape)
	-- 50% magnification, (scale can either be any floating-point positive number or zero, which also means 100%; 1 = 100%)
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Scale,Orientation,Path) values (@gKey,@jKey,@aKey,2,1.5,1,'DOCS/RA_Label')
  insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008022972')

end

-- create MS fax job
if(0=1) begin

	-- reserve a job key
  set @jKey = newid()

  -- add a job with reference to printer
  if(0=1)
	-- without cover page, reference to destination phone number
	-- and specific fax server (otherwise service-default)
	-- note: status tracking is only possible with service-default fax server
    insert OUT_FAX (gKey,jKey,Fax_Path,Receiver_Fax_Number) values (@gKey,@jKey,'\\server\fax','1-999-123-4567')
  else
	-- with cover page
	-- cover template may include names for templates installed on MS fax server
	-- for more info consult MS fax server documentation
    insert OUT_FAX (gKey,jKey,Fax_Path,Receiver_Fax_Number,
      Receiver_Name,
      Sender_Fax_Number,
      Sender_Name,
      Cover_Template,
      Cover_Subject,
      Cover_Note
    ) values (@gKey,@jKey,'\\server\fax','1-999-123-4567',
      'Robert Davis',
      '1-999-470-1200',
      'Denis P',
      'generic',
      'This is test',
      'Please pass on Robert'
    )

  -- fax content
	-- add SQL RS report content with single parameter, 100% scale and automatic orientation (1 = portrait, 2 = landscape)
	-- note: only Src_Type = 2 supported, i.e. SQL RS
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS')
  insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006103648')

	-- add SQL RS report content with single parameter, 
	-- scale to stretch and fill entire page 
	--  (scale can either be any floating-point positive number or zero, which also means stretch/shrink to fill entire page; 1 = 100%)
	-- and automatic orientation (1 = portrait, 2 = landscape)
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Scale,Path) values (@gKey,@jKey,@aKey,2,0,'DOCS/RA_Label')
  insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008019231')

	-- add SQL RS report content with automatic orientation and 100% scale factor
	-- note: when report is designed for landscape output, it will be automatically recognized
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/landscape_label')

end

-- create file job
if(0=1) begin

	-- reserve a job key
  set @jKey = newid()

  -- file job
	-- with reference to output path
	-- note: stinky service security account must have write access to specified path
  insert OUT_FILE (gKey,jKey,Path) values (@gKey,@jKey,'C:\')

  -- file content
	-- add attachment as stream of bytes 
	-- with optional file name (otherwise automatic)
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Stream,[Name]) values (@gKey,@jKey,@aKey,0,convert(image,'test'),'test.txt')

	-- add attachment from external file
	-- with optional file name (otherwise original)
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Name]) values (@gKey,@jKey,@aKey,1,'c:\example.txt','test.txt')

	-- add SQL RS report content with single parameter
  set @aKey = newid()
  insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS')
  insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006103648')

end

-- (optional) fill job status structure
-- otherwise service-default values will be used, see server settings
if(1=0) begin

  update EVENT_STATUS set
	Run_When	= '2010-12-31 08:00:00.000'	-- 8:00AM on December 31 of 2010
	Retry_Times	= 10,	-- retry up to number of times in event of system error
	Retry_Period	= 600	-- seconds between retries
  where jKey=@jKey

end

-- flag event ready
update EVENT_MASTER set Status=0 where gKey=@gKey

end