Delimiter //
/**
	procédure stoké qui permet de construire le corp des requete de mise à jour
*/
/**	
	In tableName : le nom de la table ou sous requete
	In checkedFieldName : le nom du champ à vérifier
	In batchnum : numero de lot
	In flagCountFieldName : le nom du champ totalisant les flags
	In flagMsgsFieldName : le nom du champ avec les message de flag
	In flagMsg : le message
        In lastStatusFieldName
        In lastStatus
	out query : la requête généré
*/

Drop Procedure if exists LOADER_BUILD_UPDATE_BASE_QUERY//

Create Procedure LOADER_BUILD_UPDATE_BASE_QUERY (
	In tableName varchar(70)
	, In batchNum int
	, out query text
)
Begin
	main: begin
--         Declare endLoop tinyint default 0;
        Declare fields text default null;
        Declare joinClause text;
        Declare joinCount int default 0;

        Set @tableName = tableName ;
        Set @srcTableName = Concat('LOADER_', @tableName, '');
        Set @schema = schema();
        Set @idFieldName = Concat('ID_', @tableName, '');

        Set joinClause = Concat( tableName, ' trgt ', 
                ' Inner Join LOADER_', tableName, ' src ', 
                    ' On trgt.ID_', tableName, ' = src.ID_', tableName , ' ', 
                ' Inner Join LOADER_STATUS lst ', 
                    ' On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS '
            );

        fieldsbuild: Begin
            Declare fieldName Varchar(70);
            Declare tcTableName Varchar(70);
            Declare scFieldName Varchar(70);
            Declare scTableName Varchar(70);
            Declare extIdFieldName Varchar(70);
            Declare extIdTableName Varchar(70);

            Declare cur1 Cursor For
                Select 
                    tc.TABLE_NAME TC_TABLE_NAME, tc.COLUMN_NAME TC_COLUMN_NAME,
                    sc.TABLE_NAME SC_TABLE_NAME, sc.COLUMN_NAME SC_COLUMN_NAME,
                    extid.TABLE_NAME EXTID_TABLE_NAME, extid.COLUMN_NAME EXTID_COLUMN_NAME
                From
                    (
                        (
                            Select tc1.`COLUMN_NAME`, tc1.`TABLE_NAME`
                            From information_schema.columns tc1
                            Where 1 = 1
                                And tc1.TABLE_SCHEMA = @schema
                                And tc1.`TABLE_NAME` collate utf8_general_ci = @tableName
                                And tc1.`COLUMN_NAME` collate utf8_general_ci <> @idFieldName 
                        ) tc
                        Left Join (
                            Select sc1.* 
                            From information_schema.columns sc1
                            Where 1 = 1
                                And sc1.TABLE_SCHEMA = @schema
                                And sc1.`TABLE_NAME`  collate utf8_general_ci  = @srcTableName
                        ) sc
                            On tc.`COLUMN_NAME` = sc.`COLUMN_NAME`
                    )
                    Left Join (
                        Select extid1.* 
                        From information_schema.columns extid1
                        Where 1 = 1
                            And extid1.TABLE_SCHEMA = @schema
                            And extid1.`TABLE_NAME` like 'LOADER\\_%'
                    ) extid
                        On tc.`COLUMN_NAME` like concat('%\\_', extid.`COLUMN_NAME`)
--                         And sc.TABLE_NAME Is NULL
                Where 1 = 1
--                     And (
--                         sc.COLUMN_NAME Is Not Null
--                         Or 
--                         tc.COLUMN_NAME like '%\\_ID\\_%'
--                     )
                Order By TC_TABLE_NAME, TC_COLUMN_NAME, SC_TABLE_NAME, SC_COLUMN_NAME
            ;

            Open cur1;
            fieldsRead: Begin
                Declare Exit Handler for NOT FOUND 
                Begin
                    Close cur1;
                End ;
                fieldLoop: loop
                    fetch cur1 into 
                        tcTableName, fieldName, 
                        scTableName, scFieldName, 
                        extIdTableName, extIdFieldName
                    ;

                    Set @fieldUpdate = null;
                    
                    if extIdTableName Is Not Null Then
                        -- reference externe, on join à la table loader 
                        Set joinCount = joinCount + 1;
                        Set joinClause = Concat (
                                joinClause, 
                                ' Left Join ', extIdTableName , ' TBL_', joinCount, 
                                ' On lst.ID_LOADER_STATUS =  TBL_', joinCount, '.LOADER_STATUS_ID_LOADER_STATUS'
                            );
                       if scFieldName Is Not Null Then
                            -- donnée non présente dans la table intermediaire traité
                            Set @fieldUpdate = Concat(' trgt.', fieldName, 
                                            ' = Coalesce(src.', fieldName, 
                                                ', TBL_', joinCount, '.' , extIdFieldName, 
                                                ', trgt.', fieldName, 
                                            ') ');
                       Else
                            -- donnée présente dans la table intermédiaure traité
--                             Set @fieldUpdate = Concat(' trgt.', fieldName, 
--                                                 ' = TBL_', joinCount, '.' , extIdFieldName, ' ');
                            Set @fieldUpdate = Concat(' trgt.', fieldName, 
                                                ' = Coalesce(TBL_', joinCount, '.' , extIdFieldName, 
                                                ',trgt.', fieldName,') ');
                       End If;
                    Elseif scFieldName is Not null Then
--                         Set @fieldUpdate = Concat('trgt.', fieldName, ' = src.', fieldName);
                        Set @fieldUpdate = Concat('trgt.', fieldName, 
                                ' = Coalesce(src.', fieldName, ', trgt.', fieldName,') ');
                    End if;
                    
                    if @fieldUpdate is Not Null Then
                        Set fields = Concat(Coalesce(Concat(fields, ', '), ''), @fieldUpdate);
                    End if;

                End loop fieldLoop;
            end ;
        End;
        
        Set query = Concat(
            'Update ', joinClause,
            ' Set ', fields, ' ', 
            ' Where 1 = 1 ', 
                ' And lst.LOADER_STATUS_ERRCOUNT = 0 '
                ' And lst.LOADER_STATUS_BATCH_NUM =  ', Coalesce(batchNum, 0)
        );

--         call loader_print(fields);

    end ;
End //
Delimiter ;