USP_FAF_SMARTFIELDINSTANCE

create procedure dbo.USP_FAF_SMARTFIELDINSTANCE
(
@CHANGEAGENTID uniqueidentifier = null
)
as
begin
    declare @CURRENTDATE date,
    @NUMBERMEMBERSTODATEID uniqueidentifier,
    @DONATIONTODATEID uniqueidentifier,
    @DONATIONWEEKLYID uniqueidentifier,
    @DONATIONMONTHLYID uniqueidentifier,
    @PARTICIANTRAISEDTODATEID uniqueidentifier,
    @PARTICIANTRAISEDWEEKLYID uniqueidentifier,
    @PARTICIANTRAISEDMONTHLYID uniqueidentifier,

    @MEMBERSTODATE_INSTANCENAME varchar(75),
    @DONATIONTODATE_INSTANCENAME varchar(75),
    @DONATIONWEEKLY_INSTANCENAME varchar(75),
    @DONATIONMONTHLY_INSTANCENAME varchar(75),
    @PARTICIANTRAISEDTODATE_INSTANCENAME varchar(75),
    @PARTICIANTRAISEDWEEKLY_INSTANCENAME varchar(75),
    @PARTICIANTRAISEDMONTHLY_INSTANCENAME varchar(75)

Set @MEMBERSTODATE_INSTANCENAME = 'Number of group members to date'
Set @DONATIONTODATE_INSTANCENAME = 'Group total amount raised to date'
Set @DONATIONWEEKLY_INSTANCENAME = 'Group total amount raised last week'
Set @DONATIONMONTHLY_INSTANCENAME = 'Group total amount raised last month'
Set @PARTICIANTRAISEDTODATE_INSTANCENAME = 'Participant total amount raised to date'
Set @PARTICIANTRAISEDWEEKLY_INSTANCENAME = 'Participant total amount raised last week'
Set @PARTICIANTRAISEDMONTHLY_INSTANCENAME = 'Participant total amount raised last month'

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

set @CURRENTDATE = GETDATE()

--number of members smart field instance


  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = '590e3f13-cbee-40cd-92c6-d808ec52259d' and NAME = @MEMBERSTODATE_INSTANCENAME)
  begin
      set @NUMBERMEMBERSTODATEID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@NUMBERMEMBERSTODATEID, @MEMBERSTODATE_INSTANCENAME, '590e3f13-cbee-40cd-92c6-d808ec52259d', null, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @NUMBERMEMBERSTODATEID, @CHANGEAGENTID
  end        


-- TOTAL DONATION RAISED TO DATE BY GROUP

  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a' and NAME = @DONATIONTODATE_INSTANCENAME)
  begin
      set @DONATIONTODATEID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@DONATIONTODATEID, @DONATIONTODATE_INSTANCENAME, 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >0</Value>
      </fv>
        </Values>
      </DataFormItem>', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @DONATIONTODATEID, @CHANGEAGENTID
  end    


-- TOTAL DONATION RAISED LAST WEEK BY GROUP

  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a' and NAME = @DONATIONWEEKLY_INSTANCENAME)
  begin
      set @DONATIONWEEKLYID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@DONATIONWEEKLYID, @DONATIONWEEKLY_INSTANCENAME, 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >1</Value>
      </fv>
        </Values>
      </DataFormItem>', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @DONATIONWEEKLYID, @CHANGEAGENTID
  end

-- TOTAL DONATION RAISED LAST MONTH BY GROUP

  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a' and NAME = @DONATIONMONTHLY_INSTANCENAME)
  begin
      SET @DONATIONMONTHLYID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@DONATIONMONTHLYID, @DONATIONMONTHLY_INSTANCENAME, 'ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >2</Value>
      </fv>
        </Values>
      </DataFormItem>', 
          0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @DONATIONMONTHLYID, @CHANGEAGENTID
  end

--- PARTICIPANT RAISED TO DATE

  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = '07e902b5-59b7-4d20-8a54-969bb583d81d' and NAME = @PARTICIANTRAISEDTODATE_INSTANCENAME)
  begin    
      SET @PARTICIANTRAISEDTODATEID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@PARTICIANTRAISEDTODATEID, @PARTICIANTRAISEDTODATE_INSTANCENAME, '07e902b5-59b7-4d20-8a54-969bb583d81d', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >0</Value>
      </fv>
        </Values>
      </DataFormItem>', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @PARTICIANTRAISEDTODATEID, @CHANGEAGENTID
  end


--- PARTICIPANT total RAISED last week

  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = '07e902b5-59b7-4d20-8a54-969bb583d81d' and NAME = @PARTICIANTRAISEDWEEKLY_INSTANCENAME)
  begin    
      SET @PARTICIANTRAISEDWEEKLYID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@PARTICIANTRAISEDWEEKLYID, @PARTICIANTRAISEDWEEKLY_INSTANCENAME, '07e902b5-59b7-4d20-8a54-969bb583d81d', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >1</Value>
      </fv>
        </Values>
      </DataFormItem>', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @PARTICIANTRAISEDWEEKLYID, @CHANGEAGENTID
  end

--- PARTICIPANT total RAISED last week


  if not exists(select ID from SMARTFIELD where SMARTFIELDCATALOGID = '07e902b5-59b7-4d20-8a54-969bb583d81d' and NAME = @PARTICIANTRAISEDMONTHLY_INSTANCENAME)
  begin
      SET @PARTICIANTRAISEDMONTHLYID = NEWID()

      insert into dbo.SMARTFIELD
          (ID, NAME, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID)
      values
          (@PARTICIANTRAISEDMONTHLYID, @PARTICIANTRAISEDMONTHLY_INSTANCENAME, '07e902b5-59b7-4d20-8a54-969bb583d81d', 
          '<?xml version="1.0" encoding="utf-8"?>
      <DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
        <Values>
          <fv ID="TIMESPANCODE">
        <Value xsi:type="xsd:string"  >2</Value>
      </fv>
        </Values>
      </DataFormItem>', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 1, 0, null);

      exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @PARTICIANTRAISEDMONTHLYID, @CHANGEAGENTID
  end    
end