Missing Local ID - Revision history

Alnaucler at 20:49, 12 November 2016

2016-11-12T20:49:38Z

← Older revision		Revision as of 22:49, 12 November 2016
Line 364:		Line 364:
	<pre>		<pre>
	SELECT		SELECT
	gazetteer.gazetteer_localid		gazetteer.gazetteer_localid,
			gazetteer.gazetteername,
			gazetteerlevel.`level`
	FROM		FROM
	gazetteer		gazetteer
			INNER JOIN gazetteerlevel ON gazetteer.gazlevel_guid = gazetteerlevel.gazlevel_guid
	WHERE		WHERE
	gazetteer_localid IS NULL OR length(trim(gazetteer_localid)) = 0		gazetteer_localid IS NULL OR length(trim(gazetteer_localid)) = 0
			ORDER BY
			gazetteer.gazetteername ASC
	</pre>		</pre>

Line 374:		Line 379:
	<pre>		<pre>
	SELECT		SELECT
	organisation.org_localid		organisation.org_localid,
			organisation.orgname,
			organisation.parentorg_guid
	FROM		FROM
	organisation		organisation
	WHERE		WHERE
	org_localid IS NULL OR length(trim(org_localid)) = 0		org_localid IS NULL OR length(trim(org_localid)) = 0
			ORDER BY
			organisation.orgname ASC
	</pre>		</pre>

Line 384:		Line 393:
	<pre>		<pre>
	SELECT		SELECT
	place.place_localid		place.place_localid,
			place.placename
	FROM		FROM
	place		place
	WHERE		WHERE
	place_localid IS NULL OR length(trim(place_localid)) = 0		place_localid IS NULL OR length(trim(place_localid)) = 0
			ORDER BY
			place.placename ASC
	</pre>		</pre>

Line 394:		Line 406:
	<pre>		<pre>
	SELECT		SELECT
	task.localid		task.localid,
			task.`name`
	FROM		FROM
	task		task
	WHERE		WHERE
	task.localid IS NULL or length(trim(task.localid))=0		task.localid IS NULL or length(trim(task.localid))=0
			ORDER BY
			task.`name` ASC
	</pre>		</pre>

Line 404:		Line 419:
	<pre>		<pre>
	SELECT		SELECT
	work_item.localid		work_item.localid,
			work_item.`name`
	FROM		FROM
	work_item		work_item
	WHERE		WHERE
	work_item.localid IS NULL OR length(trim(work_item.localid)) = 0		work_item.localid IS NULL OR length(trim(work_item.localid)) = 0
			ORDER BY
			work_item.`name` ASC
	</pre>		</pre>

Alnaucler at 22:53, 11 November 2016

2016-11-11T22:53:43Z

← Older revision		Revision as of 00:53, 12 November 2016
Line 1:		Line 1:
	{{TOC right}}		{{TOC right}}
	{{Note \| All ID are important to be filled in. ''Form ID'' is important for the DEF's Reconcilation tab and I recommend that to update ~~that one as~~ the ~~last one~~.}}		{{Note \| All ID are important to be filled in. ''Form ID'' is important for the DEF's Reconcilation tab and I recommend that to update ''Form ID'' after you have have checked the items' ID.
			There is a PostgreSQL version of this page '''[[Missing Local ID PGSQL \| too]]'''.}}

	==Missing Local ID items items ==__NOEDITSECTION__		==Missing Local ID items items ==__NOEDITSECTION__

Alnaucler at 22:46, 11 November 2016

2016-11-11T22:46:54Z

Show changes

Alnaucler at 22:14, 11 November 2016

2016-11-11T22:14:03Z

Show changes

Alnaucler at 21:57, 11 November 2016

2016-11-11T21:57:13Z

← Older revision		Revision as of 23:57, 11 November 2016
Line 199:		Line 199:
	== Missing Local ID Auxiliary data and Task==__NOEDITSECTION__		== Missing Local ID Auxiliary data and Task==__NOEDITSECTION__
	=== Country structure===__NOEDITSECTION__		=== Country structure===__NOEDITSECTION__
			In 5.x the Country Structure ID was not visible in the editor and therefore many countries will have blank here. To have a proper ID will facilitate Country structure updates which happens regularly.
	<pre>		<pre>
			SELECT
			gazetteer.gazetteer_localid
			FROM
			gazetteer
			WHERE
			gazetteer_localid IS NULL OR length(trim(gazetteer_localid)) = 0
	</pre>		</pre>

	=== Organisation ===__NOEDITSECTION__		=== Organisation ===__NOEDITSECTION__
	<pre>		<pre>
			SELECT
			organisation.org_localid
			FROM
			organisation
			WHERE
			org_localid IS NULL OR length(trim(org_localid)) = 0
	</pre>		</pre>

	=== Place ===__NOEDITSECTION__		=== Place ===__NOEDITSECTION__
	<pre>		<pre>
			SELECT
			place.place_localid
			FROM
			place
			WHERE
			place_localid IS NULL OR length(trim(place_localid)) = 0
	</pre>		</pre>

	=== Task===__NOEDITSECTION__		=== Task===__NOEDITSECTION__
	<pre>		<pre>
			SELECT
			task.localid
			FROM
			task
			WHERE
			task.localid IS NULL or length(trim(task.localid))=0
	</pre>		</pre>

	=== Work item ===__NOEDITSECTION__		=== Work item ===__NOEDITSECTION__
	<pre>		<pre>
			SELECT
			work_item.localid
			FROM
			work_item
			WHERE
			work_item.localid IS NULL OR length(trim(work_item.localid)) = 0
	</pre>		</pre>
	=== Example update query ===__NOEDITSECTION__		=== Example update query ===__NOEDITSECTION__


	{{NavBox Upgrade}}		{{NavBox Upgrade}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:SQL Queries]]		[[Category:SQL Queries]]

Alnaucler at 21:50, 11 November 2016

2016-11-11T21:50:50Z

@@ Line 197: / Line 197: @@
 </pre>
-== Missing Local ID Auxiliary data ==__NOEDITSECTION__
+== Missing Local ID Auxiliary data and Task==__NOEDITSECTION__
 === Example update query ===__NOEDITSECTION__
 {{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]

Alnaucler at 09:00, 11 November 2016

2016-11-11T09:00:09Z

@@ Line 2: / Line 2: @@
 {{Note | All ID are important to be filled in. ''Form ID'' is important for the DEF's Reconcilation tab and I recommend that to update that one as the last one.}}
-==Missing Local ID ==__NOEDITSECTION__
+==Missing Local ID items ==__NOEDITSECTION__
 Start with checking the items and the infoversions. If they are missing a local ID then I strongly recommend to set one which will be very helpful if you need to populate ''Form ID''.
@@ Line 147: / Line 152: @@
 === Example update query ===__NOEDITSECTION__
-== fieldreport ==__NOEDITSECTION__
+== Missing Local ID fieldreport ==__NOEDITSECTION__
 If you get output here you '''NEED''' to take action. The easiest solution to populate the ''Form ID'' for DEF is to use the local ID from the infoversion which you have now populated with updating the ones that were missing with the queries above.
@@ Line 164: / Line 169: @@
 fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0
 </pre>
 {{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]

Alnaucler at 08:47, 11 November 2016

2016-11-11T08:47:33Z

← Older revision		Revision as of 10:47, 11 November 2016
Line 1:		Line 1:
	''~~'Field report~~'''		{{TOC right}}
	~~<pre>~~		{{Note \| All ID are important to be filled in. ''Form ID'' is important for the DEF's Reconcilation tab and I recommend that to update that one as the last one.}}
	~~SELECT~~
	~~fieldreport~~.~~fieldreport_localid,~~		==Missing Local ID ==__NOEDITSECTION__
	~~fieldreport.dateofreport AS `Date of Information`,~~		Start with checking the items and the infoversions. If they are missing a local ID then I strongly recommend to set one which will be very helpful if you need to populate ''Form ID''.
	~~fieldreport.reportreceiveddate AS `Data Entry Date`,~~
	~~fieldreport~~.~~reportcompleteddate AS `Submitted Date`,~~		=== Accident ===__NOEDITSECTION__
	~~fieldreport~~.~~reportverifieddate AS `Approved Date`,~~		If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.
	~~fieldreport.dataenterer AS `Data enterer`,~~
	~~fieldreport.verifiedby AS `Approved by`~~
	~~FROM~~
	~~fieldreport~~
	~~WHERE~~
	~~fieldreport~~.~~fieldreport_localid~~ is ~~null~~ or ~~length(trim(fieldreport.fieldreport_localid)) = 0~~
	~~</pre>~~
	'''~~Accident~~'''
	<pre>		<pre>
	SELECT		SELECT
Line 31:		Line 24:
	accident.accident_localid ASC		accident.accident_localid ASC
	</pre>		</pre>
	'''~~Hazard~~'''
			=== Hazard ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 48:		Line 44:
	hazard.hazard_localid ASC		hazard.hazard_localid ASC
	</pre>		</pre>
	'''~~Hazard Reduction~~'''
			=== Hazard Reduction ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 65:		Line 64:
	hazreduc.hazreduc_localid ASC		hazreduc.hazreduc_localid ASC
	</pre>		</pre>
	'''~~Location~~'''
			=== Location ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 82:		Line 84:
	location.location_localid ASC		location.location_localid ASC
	</pre>		</pre>
	'''~~MRE~~'''
			=== MRE ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 99:		Line 104:
	mre.mre_localid ASC		mre.mre_localid ASC
	</pre>		</pre>
	'''QM'''
			=== QM ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 116:		Line 124:
	qa.qa_localid ASC		qa.qa_localid ASC
	</pre>		</pre>
	'''~~Victim~~'''
			=== Victim ===__NOEDITSECTION__
			If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''both''' that are missing the Local ID.

	<pre>		<pre>
	SELECT		SELECT
Line 134:		Line 145:
	</pre>		</pre>

			=== Example update query ===__NOEDITSECTION__

			== fieldreport ==__NOEDITSECTION__
			If you get output here you '''NEED''' to take action. The easiest solution to populate the ''Form ID'' for DEF is to use the local ID from the infoversion which you have now populated with updating the ones that were missing with the queries above.

			<pre>
			SELECT
			fieldreport.fieldreport_localid,
			fieldreport.dateofreport AS `Date of Information`,
			fieldreport.reportreceiveddate AS `Data Entry Date`,
			fieldreport.reportcompleteddate AS `Submitted Date`,
			fieldreport.reportverifieddate AS `Approved Date`,
			fieldreport.dataenterer AS `Data enterer`,
			fieldreport.verifiedby AS `Approved by`
			FROM
			fieldreport
			WHERE
			fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0
			</pre>
	{{NavBox Upgrade}}		{{NavBox Upgrade}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:SQL Queries]]		[[Category:SQL Queries]]

Alnaucler at 18:42, 11 April 2015

2015-04-11T18:42:10Z

← Older revision		Revision as of 20:42, 11 April 2015
Line 24:		Line 24:
	WHERE		WHERE
	(accident.accident_localid IS NULL OR		(accident.accident_localid IS NULL OR
	accidentinfoversion.accident_localid IS NULL) AND		accidentinfoversion.accident_localid IS NULL OR
			length(trim(accident.accident_localid)) = 0 OR
			length(trim(accidentinfoversion.accident_localid)) = 0) AND
	accidentinfoversion.link_only = '0'		accidentinfoversion.link_only = '0'
	ORDER BY		ORDER BY
Line 40:		Line 42:
	hazardinfoversion.link_only = '0' AND		hazardinfoversion.link_only = '0' AND
	(hazardinfoversion.hazard_localid IS NULL OR		(hazardinfoversion.hazard_localid IS NULL OR
	hazard.hazard_localid IS NULL )		hazard.hazard_localid IS NULL OR
			length(trim(hazardinfoversion.hazard_localid))= 0 OR
			length(trim(hazard.hazard_localid)) = 0 )
	ORDER BY		ORDER BY
	hazard.hazard_localid ASC		hazard.hazard_localid ASC
Line 54:		Line 58:
	WHERE		WHERE
	(hazreducinfoversion.hazreduc_localid IS NULL OR		(hazreducinfoversion.hazreduc_localid IS NULL OR
	hazreduc.hazreduc_localid IS NULL) AND		hazreduc.hazreduc_localid IS NULL OR
			length(trim(hazreducinfoversion.hazreduc_localid)) = 0 OR
			length(trim(hazreduc.hazreduc_localid)) = 0 ) AND
	hazreducinfoversion.link_only = '0'		hazreducinfoversion.link_only = '0'
	ORDER BY		ORDER BY
Line 69:		Line 75:
	WHERE		WHERE
	(locationinfoversion.location_localid IS NULL OR		(locationinfoversion.location_localid IS NULL OR
	location.location_localid IS NULL) AND		location.location_localid IS NULL OR
			length(trim(locationinfoversion.location_localid)) = 0 OR
			length(trim(location.location_localid)) = 0) AND
	locationinfoversion.link_only = '0'		locationinfoversion.link_only = '0'
	ORDER BY		ORDER BY
Line 84:		Line 92:
	WHERE		WHERE
	(mreinfoversion.mre_localid IS NULL OR		(mreinfoversion.mre_localid IS NULL OR
	mre.mre_localid IS NULL) AND		mre.mre_localid IS NULL OR
			length(trim(mreinfoversion.mre_localid)) = 0 OR
			length(trim(mre.mre_localid)) = 0) AND
	mreinfoversion.link_only = '0'		mreinfoversion.link_only = '0'
	ORDER BY		ORDER BY
Line 99:		Line 109:
	WHERE		WHERE
	(qainfoversion.qa_localid IS NULL OR		(qainfoversion.qa_localid IS NULL OR
	qa.qa_localid IS NULL) AND		qa.qa_localid IS NULL OR
			length(trim(qainfoversion.qa_localid)) = 0 OR
			length(trim(qa.qa_localid))= 0) AND
	qainfoversion.link_only = '0'		qainfoversion.link_only = '0'
	ORDER BY		ORDER BY
Line 114:		Line 126:
	WHERE		WHERE
	(victiminfoversion.victim_localid IS NULL OR		(victiminfoversion.victim_localid IS NULL OR
	victim.victim_localid IS NULL) AND		victim.victim_localid IS NULL OR
			length(trim(victiminfoversion.victim_localid))= 0 OR
			length(trim(victim.victim_localid))= 0) AND
	victiminfoversion.link_only = '0'		victiminfoversion.link_only = '0'
	ORDER BY		ORDER BY

Alnaucler at 18:24, 11 April 2015

2015-04-11T18:24:47Z

← Older revision		Revision as of 20:24, 11 April 2015
Line 12:		Line 12:
	fieldreport		fieldreport
	WHERE		WHERE
	fieldreport.fieldreport_localid is null		fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0
	</pre>		</pre>
	'''Accident'''		'''Accident'''