accounting {
	reference = "%{tolower:type.%{Acct-Status-Type}.query}"
	column_list = "\
		acctsessionid,		acctuniqueid,		username,	groupname, \
		realm,			nasipaddress,		nasportid, \
		nasporttype,		acctstarttime,		acctupdatetime, \
		acctstoptime,		acctsessiontime, 	acctauthentic, \
		connectinfo_start,	connectinfo_stop, 	acctinputoctets, \
		acctoutputoctets,	calledstationid, 	callingstationid, \
		acctterminatecause,	servicetype,		framedprotocol, \
		framedipaddress"
	type {
		accounting-on {
			query = "\
				UPDATE ${....acct_table1} \
				SET \
					acctstoptime = FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctsessiontime	= '%{integer:Event-Timestamp}' \
						- UNIX_TIMESTAMP(acctstarttime), \
					acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
				WHERE acctstoptime IS NULL \
				AND nasipaddress   = '%{NAS-IP-Address}' \
				AND acctstarttime <= FROM_UNIXTIME(\
					%{integer:Event-Timestamp})"
		}
		accounting-off {
			query = "${..accounting-on.query}"
		}
		start {
			query = "\
				INSERT INTO ${....acct_table1} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{SQL-Group}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					NULL, \
					'0', \
					'%{Acct-Authentic}', \
					'%{Connect-Info}', \
					'', \
					'0', \
					'0', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
			query = "\
				UPDATE ${....acct_table1} SET \
					acctstarttime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					acctupdatetime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					connectinfo_start = '%{Connect-Info}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
		}
		interim-update {
			query = "\
				UPDATE ${....acct_table1} \
				SET \
					acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), \
					acctupdatetime  = FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctinterval    = %{integer:Event-Timestamp} - \
						UNIX_TIMESTAMP(@acctupdatetime_old), \
					framedipaddress = '%{Framed-IP-Address}', \
					acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
					acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
			query = "\
				INSERT INTO ${....acct_table1} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{SQL-Group}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					NULL, \
					%{%{Acct-Session-Time}:-NULL}, \
					'%{Acct-Authentic}', \
					'%{Connect-Info}', \
					'', \
					'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
					'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
		}
		stop {
			query = "\
				UPDATE ${....acct_table2} SET \
					acctstoptime	= FROM_UNIXTIME(\
						%{integer:Event-Timestamp}), \
					acctsessiontime	= %{%{Acct-Session-Time}:-NULL}, \
					acctinputoctets	= '%{%{Acct-Input-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Input-Octets}:-0}', \
					acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
						<< 32 | '%{%{Acct-Output-Octets}:-0}', \
					acctterminatecause = '%{Acct-Terminate-Cause}', \
					connectinfo_stop = '%{Connect-Info}' \
				WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
			query = "\
				INSERT INTO ${....acct_table2} \
					(${...column_list}) \
				VALUES \
					('%{Acct-Session-Id}', \
					'%{Acct-Unique-Session-Id}', \
					'%{SQL-User-Name}', \
					'%{SQL-Group}', \
					'%{Realm}', \
					'%{NAS-IP-Address}', \
					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
					'%{NAS-Port-Type}', \
					FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					FROM_UNIXTIME(%{integer:Event-Timestamp}), \
					%{%{Acct-Session-Time}:-NULL}, \
					'%{Acct-Authentic}', \
					'', \
					'%{Connect-Info}', \
					'%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
					'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
					'%{Called-Station-Id}', \
					'%{Calling-Station-Id}', \
					'%{Acct-Terminate-Cause}', \
					'%{Service-Type}', \
					'%{Framed-Protocol}', \
					'%{Framed-IP-Address}')"
		}
	}
}