Static Variables for Report Drop Down Selections

select top 1 'New' as New
from dbo.B1PERMIT

union

select top 1 'Renewal' as Renewal
from dbo.B1PERMIT

union

select top 1 'Update' as Update
from dbo.B1PERMIT

Accela Functions

ASI
dbo.FN_GET_APP_SPEC_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",'ASI NAME'')

Parcel Number
dbo.FN_GET_PARCEL_NBR ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3")

Full Address
dbo.FN_GET_PRI_ADDRESS_FULL ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3")

Another Address
dbo.FN_GET_ADDRESS_PARTIAL_ALL ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'lall','')

Unit Street Address
dbo.FN_GET_ADDRESS_INFO(a.SERV_PROV_CODE, a.B1_PER_ID1, a.B1_PER_ID2, a.B1_PER_ID3,'Y','UNIT_STREETINFO')

Number of Units (parcel)
dbo.FN_GET_PARCEL_NBR_ATTRIBUTE("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",dbo.FN_GET_PARCEL_NBR ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3"),'Living Units')

Property Description
dbo.FN_GET_PARCEL_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'Legal Description')

Inspection Result Comment
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'Comment', '', '', '', '', '','Inspection Result Comment')

Latest resulted inspection
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'', 'Result', '', '', 'RESULTED', '', '','')

Latest Inspector
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'Inspector', '', '', '', '', '', '')

Next Scheduled Inspection
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'', 'Insp Date', '', '', 'SCHEDULED', '', '','')

Building Commissioner's Name
dbo.FN_GET_STDCHOICE_VALUEDESC('COS','BLD Commissioners Name','BLD Commissioner Name')

Housing Director's Name
,dbo.FN_GET_STDCHOICE_VALUEDESC('COS','HSG Deputy Dir Name','HSG Director Name')

Housing Director's Title
,dbo.FN_GET_STDCHOICE_VALUEDESC('COS','HSG Deputy Dir Name','HSG Director Title')

Owner Address Full
dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'MFullAddr_Block', '', 'U' )

Contact Address
dbo.FN_GET_CONTACT_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", 'TENANT', '', 'N', 'FullAddr_Line','','U' )

Contact Name
dbo.FN_GET_CONTACT_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", 'TENANT', '', 'N', 'FullName','','U' )

Licensed Professional Name
dbo.FN_GET_LICPROF_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'FULLNAME', 'FML','','PLUMBING')

Licensed Professional Full Address
dbo.FN_GET_LICPROF_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'FULLADDR_BLOCK', 'FML','','PLUMBING')

contact or owner address
case
when isnull(dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullAddr_Block', '', ''),'') = '' then dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'MFullAddr_Block', '', 'U' )
else
dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullAddr_Block', '', '')
end

contact or owner
case
when isnull(dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullName', '', ''),'') = ' ' then dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', '', '', 'U' )
else dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullName', '', '')
end

Accela Contact Name and Full Multiline Address

COALESCE(CASE WHEN isnull(C.B1_FNAME,'') + isnull(C.B1_MNAME,'') + isnull(C.B1_LNAME,'') <> '' THEN CASE WHEN isnull(C.B1_MNAME,'') <> '' THEN C.B1_FNAME + ' ' + C.B1_MNAME + ' ' + C.B1_LNAME ELSE C.B1_FNAME + ' ' + C.B1_LNAME END ELSE NULL END
,CASE WHEN isnull(C.B1_FULL_NAME,'') <> '' THEN C.B1_FULL_NAME ELSE NULL END
,CASE WHEN isnull(C.B1_BUSINESS_NAME,'') <> '' THEN C.B1_BUSINESS_NAME ELSE NULL END) AS 'Tenant Name'

,CASE WHEN isnull(C.B1_ADDRESS1,'') <> '' THEN C.B1_ADDRESS1 + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull(C.B1_ADDRESS2,'') <> '' THEN C.B1_ADDRESS2 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_ADDRESS3,'') <> '' THEN C.B1_ADDRESS3 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_CITY,'') <> '' THEN C.B1_CITY + ', ' ELSE '' END +
 CASE WHEN isnull(C.B1_STATE,'') <> '' THEN C.B1_STATE + ' ' ELSE '' END +
 CASE WHEN isnull(C.B1_ZIP,'') <> '' THEN C.B1_ZIP ELSE '' END
AS 'Full Tenant Address'

OWNER NAME (Crystal Format)
COALESCE(CASE WHEN isnull("B3OWNERS"."B1_OWNER_FNAME",'') + isnull("B3OWNERS"."B1_OWNER_MNAME",'') + isnull("B3OWNERS"."B1_OWNER_LNAME",'') <> '' THEN CASE WHEN isnull("B3OWNERS"."B1_OWNER_MNAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_MNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" ELSE "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" END ELSE NULL END
 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_FULL_NAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FULL_NAME" ELSE NULL END

 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_TITLE",'') <> '' THEN "B3OWNERS"."B1_OWNER_TITLE" ELSE NULL END)

OWNER ADDRESS (Crystal Format)
CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS2",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS2" + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS3",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS3" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS1",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS1" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_CITY",'') <> '' THEN "B3OWNERS"."B1_MAIL_CITY" + ', ' ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_STATE",'') <> '' THEN "B3OWNERS"."B1_MAIL_STATE" + ' ' ELSE '' END +

 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ZIP",'') <> '' THEN "B3OWNERS"."B1_MAIL_ZIP" ELSE '' END

Reset Mysql Permissions

C:\mysql\bin\mysqld-nt --init-file=C:\\mysql-init.txt

UPDATE mysql.user SET Password=PASSWORD('city11') WHERE User='root';
FLUSH PRIVILEGES;