/*
sql.query.import.map ( _TO ; _sourceTableName )

by Farice Nordmann & Laurent Spielmann - 1-more-thing
@1morethingtweet

v1.1 oct 2016 : accepts underscores in _sourceTableName
v1    sept 2016

builds a SQL query to import ODBC data using field ALIAS, so columns map FileMaker fields.
relies on field comments in the FileMaker table where you should insert:
SOURCENAME:<ODBC column name> (no space between the column (:) and the name)

To comment out (remove a field from the import map but keep the source name in comments for documentation,
use //SOURCENAME:<ODBC column name> (with // as a comment mark)

You can combine this comment with other information you'd like to keep in the comment field.

PARAMETERS:
	_TO: the target TO of the import (empty means current table)
	_sourceTableName: optional: will add a table alias to the query and a FROM statement.
	  supports a table alias in the parameter: "myTable AS T"

*/



Let ([
	_TO = Case ( IsEmpty ( _TO ) ; Get ( LayoutTableName ) ; _TO ) ;
	_sourceTableName.list = Substitute ( _sourceTableName ; " " ; ¶ ) ;
	$k = "SOURCENAME:" ;
	$cf.file = Get ( FileName ) ;
	$cf.field.names = FieldNames ( $cf.file ; _TO ) ;
	_void = Case ( ValueCount (  _sourceTableName.list ) = 3 and GetValue (  _sourceTableName.list ; 2 ) = "AS" ; Let ( [ $sourceTableName = GetValue ( _sourceTableName.list ; 1 ) ; $tableAlias = GetValue ( _sourceTableName.list ; 3 )]; "" )) ;
//	$sourceTableName = _sourceTableName ;
	
	_result = list.custom ( 1 ; ValueCount ( $cf.field.names ) ; "let ([ _f = getvalue ( $cf.field.names ; [n]) ; _c =  FieldComment ( $cf.file ; _f ) ; _c = substitute ( _c ; \" \" ; \¶ ) ; _c = GetValue ( list.filter ( _c ; \"BeginsWith\" ; $k ; false ) ; 1 ) ]; case ( not isempty ( _c ) ; case ( not isempty ( $tableAlias ) ; quote ( $tableAlias ) & \".\" ; not isempty ( $sourceTableName ) ; quote ( $sourceTableName ) & \".\" ) & quote ( substitute ( _c ; $k ; \"\" )) & \" AS \" & quote ( _f )))" ) ;
	_result = Case ( not IsEmpty ( _result ) ; "SELECT " & Substitute ( _result ; ¶ ; ", " ) & Case ( not IsEmpty ( _sourceTableName ) ; " FROM " & Quote ( $sourceTableName )) & Case ( not IsEmpty ( $tableAlias ) ; " AS " & Quote ( $tableAlias ))) ;
	$k = "" ;
	$cf.field.names = "" ;
	$cf.file = "" ;
	$sourceTableName = "" ;
	$tableAlias = ""
];
	_result
)