USP_REPORT_CONFLICTREPORT

                create procedure dbo.USP_REPORT_CONFLICTREPORT
                (
                    @FROMDATE datetime = null,
                    @TODATE datetime = null,
                    @PROGRAMID uniqueidentifier = null,
                    @PROGRAMQUERYID uniqueidentifier = null,
                    @PROGRAMCATEGORYID uniqueidentifier = null,
                    @CONFLICTTYPEFILTER tinyint = 0
                )
                as
                set nocount on;

                begin

                    if @FROMDATE is not null set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE)
                    if @TODATE is not null set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

                    with EVENT_CTE as 
                    (
                    select 
                        EVENT.NAME, 
                        EVENT.STARTTIME,
                        EVENT.ENDTIME,
                        EVENT.STARTDATE,
                        EVENT.ENDDATE,
                        EVENTLOCATION.ID as EVENTLOCATIONID
                    from dbo.EVENT
                    left join dbo.EVENTLOCATION on EVENT.EVENTLOCATIONID = EVENTLOCATION.ID 
                    where EVENT.PROGRAMID is null
                    )

                    -- program event to program event location conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        case when (P2.NAME is null or P2.NAME = '') 
                            then E.NAME
                        else
                            P2.NAME
                        end as EVENT2NAME,
                        EL.NAME CONFLICTNAME,
                        0 as QUANTITY,
                        1 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        E.STARTTIME EVENT2TIME
                    from dbo.EVENTCONFLICT EC
                    inner join dbo.PROGRAMEVENTLOCATION PEL on PEL.EVENTCONFLICTID = EC.ID
                    inner join dbo.PROGRAMEVENTLOCATION PEL2 on PEL.EVENTLOCATIONID = PEL2.EVENTLOCATIONID
                    inner join dbo.EVENTLOCATION EL on EL.ID = PEL.EVENTLOCATIONID
                    left join dbo.[EVENT] E on (E.ID = PEL2.EVENTID or E.EVENTLOCATIONID = PEL2.EVENTLOCATIONID)
                    left join dbo.PROGRAM P1 on P1.ID = EC.PROGRAMID
                    left join dbo.PROGRAM P2 on P2.ID = E.PROGRAMID
                    where E.STARTDATE = EC.STARTDATE and E.PROGRAMID is not null -- E is scheduled program event

                    and E.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> E.ENDTIME
                    and (E.STARTTIME = EC.STARTTIME or E.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between E.STARTTIME and E.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (2,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    -- program event to special event location conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        EVENT_CTE.NAME as EVENT2NAME,
                        EL.NAME CONFLICTNAME,
                        0 as QUANTITY,
                        1 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        EVENT_CTE.STARTTIME EVENT2TIME
                    from dbo.EVENTCONFLICT EC
                    inner join dbo.PROGRAMEVENTLOCATION PEL on PEL.EVENTCONFLICTID = EC.ID
                    inner join dbo.EVENTLOCATION EL on EL.ID = PEL.EVENTLOCATIONID
                    inner join EVENT_CTE on PEL.EVENTLOCATIONID = EVENT_CTE.EVENTLOCATIONID
                    left join dbo.PROGRAM P1 on P1.ID = EC.PROGRAMID
                    where (EC.STARTDATE > EVENT_CTE.STARTDATE and EC.STARTDATE < EVENT_CTE.ENDDATE) 
                        or (EC.STARTDATE = EVENT_CTE.STARTDATE and EC.STARTDATE <> EVENT_CTE.ENDDATE and (EVENT_CTE.STARTTIME is null or (EVENT_CTE.STARTTIME is not null and (EC.STARTTIME >= EVENT_CTE.STARTTIME or EC.ENDTIME > EVENT_CTE.STARTTIME))))
                        or (EC.STARTDATE = EVENT_CTE.ENDDATE and EC.STARTDATE <> EVENT_CTE.STARTDATE and (EVENT_CTE.ENDTIME is null or (EVENT_CTE.ENDTIME is not null and (EC.STARTTIME < EVENT_CTE.ENDTIME or EC.ENDTIME <= EVENT_CTE.ENDTIME))))
                        or (EC.STARTDATE = EVENT_CTE.STARTDATE and EC.STARTDATE = EVENT_CTE.ENDDATE and (EVENT_CTE.STARTTIME is null and EVENT_CTE.ENDTIME is null
                            or (EVENT_CTE.STARTTIME is null and EVENT_CTE.ENDTIME is not null and (EC.STARTTIME < EVENT_CTE.ENDTIME or EC.ENDTIME <= EVENT_CTE.ENDTIME))
                            or (EVENT_CTE.STARTTIME is not null and EVENT_CTE.ENDTIME is null) and (EC.STARTTIME >= EVENT_CTE.STARTTIME or EC.ENDTIME > EVENT_CTE.STARTTIME)
                            or (EVENT_CTE.STARTTIME is not null and EVENT_CTE.ENDTIME is not null and (EC.STARTTIME between EVENT_CTE.STARTTIME and EVENT_CTE.ENDTIME or EC.ENDTIME between EVENT_CTE.STARTTIME and EVENT_CTE.ENDTIME) and EVENT_CTE.STARTTIME <> EC.ENDTIME and EC.STARTTIME <> EVENT_CTE.ENDTIME)
                        ))
                    and @CONFLICTTYPEFILTER not in (2,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))


                    union all

                    -- program event to itinerary item event location conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        II.NAME EVENT2NAME,
                        EL.NAME CONFLICTNAME,
                        0 as QUANTITY,
                        1 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        II.STARTTIME EVENT2TIME
                    from dbo.EVENTCONFLICT EC
                    inner join dbo.PROGRAMEVENTLOCATION PEL on PEL.EVENTCONFLICTID = EC.ID
                    inner join dbo.ITINERARYITEM II on PEL.EVENTLOCATIONID = II.EVENTLOCATIONID
                    inner join dbo.EVENTLOCATION EL on EL.ID = PEL.EVENTLOCATIONID
                    inner join dbo.PROGRAM P1 on P1.ID = EC.PROGRAMID
                    left outer join dbo.PROGRAM P2 on P2.ID = II.PROGRAMID
                    where II.STARTDATE = EC.STARTDATE
                    and II.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> II.ENDTIME
                    and (II.STARTTIME = EC.STARTTIME or II.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between II.STARTTIME and II.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (2,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    /* Staff resource conflicts */

                    -- program event to program event staff resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        P2.NAME EVENT2NAME,
                        V.NAME CONFLICTNAME,
                        ESR1.QUANTITYNEEDED QUANTITY,
                        2 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        E.STARTTIME EVENT2TIME
                    from dbo.[EVENTSTAFFRESOURCE] ESR1
                    inner join dbo.[EVENTSTAFFRESOURCE] ESR2 on ESR2.VOLUNTEERTYPEID = ESR1.VOLUNTEERTYPEID
                    inner join dbo.VOLUNTEERTYPE V on ESR1.VOLUNTEERTYPEID = V.ID
                    inner join dbo.[EVENTCONFLICT] EC on ESR1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[EVENT] E on E.ID = ESR2.EVENTID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    inner join dbo.[PROGRAM] P2 on P2.ID = E.PROGRAMID
                    where E.STARTDATE = EC.STARTDATE
                    and E.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> E.ENDTIME
                    and (E.STARTTIME = EC.STARTTIME or E.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between E.STARTTIME and E.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (1,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    -- program event to itinerary item event staff resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        II.NAME EVENT2NAME,
                        V.NAME CONFLICTNAME,
                        ESR1.QUANTITYNEEDED QUANTITY,
                        2 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        II.STARTTIME EVENT2TIME
                    from dbo.[EVENTSTAFFRESOURCE] ESR1
                    inner join dbo.[ITINERARYITEMSTAFFRESOURCE] IISR on IISR.VOLUNTEERTYPEID = ESR1.VOLUNTEERTYPEID
                    inner join dbo.VOLUNTEERTYPE V on ESR1.VOLUNTEERTYPEID = V.ID
                    inner join dbo.[EVENTCONFLICT] EC on ESR1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[ITINERARYITEM] II on II.ID = IISR.ITINERARYITEMID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    left outer join dbo.[PROGRAM] P2 on P2.ID = II.PROGRAMID
                    where II.STARTDATE = EC.STARTDATE
                    and II.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> II.ENDTIME
                    and (II.STARTTIME = EC.STARTTIME or II.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between II.STARTTIME and II.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (1,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    -- program event to itinerary staff resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        I.NAME EVENT2NAME,
                        V.NAME CONFLICTNAME,
                        ESR1.QUANTITYNEEDED QUANTITY,
                        2 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        replace(convert(time,I.STARTDATETIME),':','') EVENT2TIME
                    from dbo.[EVENTSTAFFRESOURCE] ESR1
                    inner join dbo.[ITINERARYSTAFFRESOURCE] ISR on ISR.VOLUNTEERTYPEID = ESR1.VOLUNTEERTYPEID
                    inner join dbo.VOLUNTEERTYPE V on ESR1.VOLUNTEERTYPEID = V.ID
                    inner join dbo.[EVENTCONFLICT] EC on ESR1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[ITINERARY] I on I.ID = ISR.ITINERARYID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    where convert(date,I.STARTDATETIME) = EC.STARTDATE
                    and replace(convert(time,I.STARTDATETIME),':','') <> EC.ENDTIME
                    and EC.STARTTIME <> replace(convert(time,I.ENDDATETIME),':','')
                    and (replace(convert(time,I.STARTDATETIME),':','') = EC.STARTTIME or replace(convert(time,I.STARTDATETIME),':','') between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between replace(convert(time,I.STARTDATETIME),':','') and replace(convert(time,I.ENDDATETIME),':',''))
                    and @CONFLICTTYPEFILTER not in (1,3)
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))


                    union all

                    /* Equipment resource conflicts */

                    -- program event to program event resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        P2.NAME EVENT2NAME,
                        R.NAME CONFLICTNAME,
                        ER1.QUANTITYNEEDED QUANTITY,
                        3 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        E.STARTTIME EVENT2TIME
                    from dbo.[EVENTRESOURCE] ER1
                    inner join dbo.[EVENTRESOURCE] ER2 on ER2.RESOURCEID = ER1.RESOURCEID
                    inner join dbo.[RESOURCE] R on ER1.RESOURCEID = R.ID
                    inner join dbo.[EVENTCONFLICT] EC on ER1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[EVENT] E on E.ID = ER2.EVENTID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    inner join dbo.[PROGRAM] P2 on P2.ID = E.PROGRAMID
                    where E.STARTDATE = EC.STARTDATE
                    and E.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> E.ENDTIME
                    and (E.STARTTIME = EC.STARTTIME or E.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between E.STARTTIME and E.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (1,2)
                    and R.TYPECODE = 0
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    -- program event to itinerary event resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        II.NAME EVENT2NAME,
                        R.NAME CONFLICTNAME,
                        ER1.QUANTITYNEEDED QUANTITY,
                        3 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        II.STARTTIME EVENT2TIME
                    from dbo.[EVENTRESOURCE] ER1
                    inner join dbo.ITINERARYITEMRESOURCE IIR on IIR.RESOURCEID = ER1.RESOURCEID
                    inner join dbo.[RESOURCE] R on ER1.RESOURCEID = R.ID
                    inner join dbo.[EVENTCONFLICT] EC on ER1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[ITINERARYITEM] II on IIR.ITINERARYITEMID = II.ID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    left outer join dbo.[PROGRAM] P2 on P2.ID = II.PROGRAMID
                    where II.STARTDATE = EC.STARTDATE
                    and II.STARTTIME <> EC.ENDTIME
                    and EC.STARTTIME <> II.ENDTIME
                    and (II.STARTTIME = EC.STARTTIME or II.STARTTIME between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between II.STARTTIME and II.ENDTIME)
                    and @CONFLICTTYPEFILTER not in (1,2)
                    and R.TYPECODE = 0
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))

                    union all

                    -- program event to itinerary resource conflicts

                    select distinct
                        'http://www.blackbaud.com/CONFLICTINGPROGRAMID?CONFLICTINGPROGRAMID=' + CONVERT(nvarchar(36), P1.ID) CONFLICTINGPROGRAMID,
                        P1.NAME EVENT1NAME,
                        I.NAME EVENT2NAME,
                        R.NAME CONFLICTNAME,
                        ER1.QUANTITYNEEDED QUANTITY,
                        3 as CONFLICTTYPE,
                        EC.STARTDATE EVENTDATE,
                        EC.STARTTIME EVENT1TIME,
                        replace(convert(time,I.STARTDATETIME),':','') EVENT2TIME
                    from dbo.[EVENTRESOURCE] ER1
                    inner join dbo.ITINERARYRESOURCE IR on IR.RESOURCEID = ER1.RESOURCEID
                    inner join dbo.[RESOURCE] R on ER1.RESOURCEID = R.ID
                    inner join dbo.[EVENTCONFLICT] EC on ER1.EVENTCONFLICTID = EC.ID
                    inner join dbo.[ITINERARY] I on IR.ITINERARYID = I.ID
                    inner join dbo.[PROGRAM] P1 on P1.ID = EC.PROGRAMID
                    where convert(date,I.STARTDATETIME) = EC.STARTDATE
                    and replace(convert(time,I.STARTDATETIME),':','') <> EC.ENDTIME
                    and EC.STARTTIME <> replace(convert(time,I.ENDDATETIME),':','')
                    and (replace(convert(time,I.STARTDATETIME),':','') = EC.STARTTIME or replace(convert(time,I.STARTDATETIME),':','') between EC.STARTTIME and EC.ENDTIME or EC.STARTTIME between replace(convert(time,I.STARTDATETIME),':','') and replace(convert(time,I.ENDDATETIME),':',''))
                    and @CONFLICTTYPEFILTER not in (1,2)
                    and R.TYPECODE = 0
                    and (@PROGRAMID is null or P1.[ID] = @PROGRAMID)
                    and (@PROGRAMCATEGORYID is null or P1.[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID)
                    and (@PROGRAMQUERYID is null or P1.[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERYID)))
                    and ((@FROMDATE is null and @TODATE is null) or (EC.STARTDATE between @FROMDATE and @TODATE))
                end