Difference between revisions of "Ship Survey"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
Line 71: Line 71:
  
  
[[Category:Base Example]]
+
[[Category:Database Scenarios]]

Latest revision as of 07:19, 10 August 2006

This is another self contained database. In this case the information gathered is used during the course of inspecting contractual work progress in a ship yard.

This table structure is a pure relational model, utilizing only 1 to Many relationships. It also includes a set of basic macros and single table to programmatically store information relating to inspections that must be performed again.


ShipSurveySchmea 1.png


The ship survey database gives a good example of using a couple of small macros to perform a business function that could not otherwise be implemented without a macro. In this case it is the requirement put forward by the user as:


Just to explain you 'the name of the game': during construction we do not accept all the things: for such cases we have conclusions 'REJECTED' and 'OWC'.
In both cases same item has to be inspected at least one more time. It would be handy to have information which items have to be re-inspected on one place (separate table?). Query of table tInspection with R or OWC as criteria can not help: doesn't mean that same Item is not accepted on some later date. Of course, situation is more complicated, all records in such (new) table has to be unique.
Moreover if one re-inspect an item, there is no guarantee that this item will be accepted - two-three-inspections are not unusual, depending on a Builder. So basically, on insert of new record macro has to check if such record already exist in a 'new' table: if exist and if conclusion is R or OWC, macro suppose to update a date in those new table; if conclusion is something else (not R or OWC) macro suppose to delete that record from the new table. If record doesn't exist and conclusion is R or OWC, macro suppose to add record to the table - Date and Inspection are Ok. However, all records has to be 'intact' in a table tInspection.


To add this functionality the table tFor_Confirmation was added, as you can see in the schema above. A basic library was then added with three macros added.

sub onAfterRecordAction( oEv as object )
	dim cncField
	'if oEv.Action = com.sun.star.sdb.RowChangeAction.UPDATE then
		cncField = oEv.Source.getByName( "txtConclusion" ).BoundField
		if cncField.getString = "R" or cncField.getString = "OWC" then
			' insert if needed	
			insertForConfirm( oEv.Source )
		else
			removeForCinfirm( oEv.Source )
		end if
	'end if
end sub

sub insertForConfirm( aDataForm as variant )
	dim stmt
	dim prepStmt
	dim rs
		' frist see if it is already in the table	
	prepStmt = aDataForm.ActiveConnection.PrepareStatement( _
		"SELECT * FROM ""tFor_Confirmation"" WHERE ""Inspection"" = ? AND ""Type"" = ? ", _
		com.sun.star.sdb.CommandType.COMMAND )
	prepStmt.setString( 1, aDataForm.GetByname( "txtInspection" ).BoundField.getString )
	prepStmt.setString( 2, aDataForm.getByname( "txtType" ).BoundField.getString )
	rs = prepStmt.executeQuery
	if not rs.isBeforeFirst then
		' not found so insert it
		stmt = aDataForm.ActiveConnection.CreateStatement
		stmt.executeUpdate( _
		  "INSERT INTO ""tFor_Confirmation"" ( " _
		  & "SELECT ""Inspection"", ""Date"", ""Type"", ""Conclusion"", ""Id_No"" " _
		  & "from ""tInspection"" ""tInspection"" where ""Id_No"" = " _
		  & aDataForm.Columns.getByName( "Id_No" ).getString & ")" )
	end if
end sub

sub removeForCinfirm( aDataForm as variant )
' here we are not going to check
' it would be quicker just to run 
' the delete statement
' if the record is not there - no harm
	dim prepStmt
	prepStmt = aDataForm.ActiveConnection.PrepareStatement( _
	  "DELETE FROM ""tFor_Confirmation"" WHERE ""Inspection"" = ? AND ""Type"" = ? ", _
	  com.sun.star.sdb.CommandType.COMMAND )
	prepStmt.setString( 1, aDataForm.GetByname( "txtInspection" ).BoundField.getString )
	prepStmt.setString( 2, aDataForm.getByname( "txtType" ).BoundField.getString )
	prepStmt.executeUpdate
end sub

These sub procedures are an example of using one of the events on the dataform. The onAfterRecordAction sub is what is actually assigned to the dataforms After Record Action event, while the actual work is seperated out into the subs insertForConfirm abd removeForCinfirm.

Personal tools