// Person objects\n\n";
// replaces &loop_through_people_records

INCLUDE /nfs/galyep/pjm/badger-bin/pace_header_file

RECORD Person = {
    SQL = {
        SELECT DISTINCT person.id_person, email, forename || ' ' || surname longname
        FROM   team_person_role, person
        WHERE  team_person_role.id_person = person.id_person
        AND    roletype IN ('Finishing', 'Prefinishing', 'Checking')
    }

    ACE = {
        Person    "$VAR[1]"
	Full_name "$VAR[2]"
    }
}


// &loop_through_lab_records

RECORD Lab = {
    SQL = {
        SELECT orgtitle, address, contact, ftp, fax, www FROM organisation
    }

    ACE = {
        Laboratory "$VAR[0]"
        Mail       "$VAR[1]"
	Contact    "$VAR[2]"
	FTP        "$VAR[3]"
	Fax        "$VAR[4]"
	WWW        "$VAR[5]"
    }
}

// &loop_through_contig_records

RECORD Contig = {
    SQL = {
        SELECT contigname, extentfrom, extentto FROM contig
    }

    ACE = {
    	Map          "$VAR[0]"
	Default_view "status-view"
	Extent       $VAR[1] $VAR[2]
    }
}

// &loop_through_project_records;
//

RECORD Project = {
    SQL = {
        SELECT cp.clonename, p.ftp_directory, p.isprivate, cp.projectname
	FROM   clone_project cp, project p
        WHERE  cp.projectname = p.projectname
// TAKE HIS LINE OUT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//        AND    rownum < 100
    }

    ACE = {
       LINE_SPACE

       Sequence      "$VAR[0]"
       FTP_directory "$VAR[1]"
       Private       IF_CONDITION $VAR[2] == 1

       NO_LINE_SPACE
    }

    RECORD Clone_info = {
        SQL = {
           SELECT clone.clonename, clone.speciesname, chromosomedict.chromosome, 
                  organisation.orgtitle, seqreasondict.pace_tag, clone.remark
           FROM   clone, project, clone_project, chromosomedict, seqreasondict,
                  sequencing_org, organisation
           WHERE  clone.clonename       = clone_project.clonename 
           AND    project.projectname   = clone_project.projectname 
           AND    clone.chromosome      = chromosomedict.id_dict(+)
           AND    clone.seq_reason      = seqreasondict.id_dict
           AND    clone.sequenced_by    = sequencing_org.id_org
           AND    sequencing_org.id_org = organisation.id_org
           AND    project.projectname   = 'Project.$VAR[3]'
        }

        ACE = {
            IF_CONDITION( $TOTAL_NUM_ROWS > 1 )
                Remark "Sequence has multiple clones: query gull for details" NO_LINE_SPACE

            Species      "$VAR[1]"
            Chromosome   "$VAR[2]"
            Sequenced_by "$VAR[3]"
            $VAR[4]
            Remark       "$VAR[5]"

            NO_LINE_SPACE
        }
    }

    RECORD Map_info = {
        SQL = {
            SELECT contigname, leftpos, rightpos
            FROM   clone_contig, clone_project
            WHERE  clone_contig.clonename    = clone_project.clonename
            AND    clone_project.projectname = 'Project.$VAR[3]'
        }

        ACE = {
            Map   "$VAR[0]" Left  $VAR[1]
            Map   "$VAR[0]" Right $VAR[2]

            NO_LINE_SPACE
        }
    }

    RECORD Person_info = {
        SQL = {
            SELECT c.email, b.roletype
            FROM   project_role a, team_person_role b, person c
            WHERE  a.id_role     = b.id_role
            AND    b.id_person   = c.id_person
            AND    a.projectname = 'Project.$VAR[3]'
            AND    b.roletype in ('Finishing','Checking','Prefinishing')
            ORDER BY a.assigned_from desc
        }

        WHERE = {
            my ($first_finisher, $first_checker, $first_prefinisher);

            foreach my $row_ref (@{$REF_TO_ARRAY}){
                my $roletype = $row_ref->[1];

		if ($roletype eq 'Finishing' && $first_finisher) {
                    $row_ref->[0] = "Previous_finisher \"$row_ref->[0]\"";

		} elsif ($roletype eq 'Checking'&& $first_checker) {
                    $row_ref->[0] = "Previous_checker \"$row_ref->[0]\"";

		} elsif ($roletype eq 'Prefinishing'&& $first_prefinisher) {
                    $row_ref->[0] = "Previous_prefinisher \"$row_ref->[0]\"";

		} elsif ($roletype eq 'Finishing') {
                    $first_finisher = 1;
                    $row_ref->[0]   =  "Finisher \"$row_ref->[0]\"";

		} elsif ($roletype eq 'Checking') {
                    $first_checker = 1;
                    $row_ref->[0]  = "Checker \"$row_ref->[0]\"";

		} elsif ($roletype eq 'Prefinishing') {
                    $first_prefinisher = 1;
                    $row_ref->[0]      =  "Prefinisher \"$row_ref->[0]\"";
		}
            }
	}

        ACE = {
            $VAR[0]
            NO_LINE_SPACE
        }
    }

    // add_project_status_tags

    RECORD Status_info = {
        SQL = {
            SELECT projectstatusdict.pace_tag status_name, to_char(statusdate, 'yyyy-mm-dd') status_date, remark
            FROM   project_status, projectstatusdict
            WHERE  project_status.status      = projectstatusdict.id_dict
            AND    projectstatusdict.pace_tag is not null 
            AND    projectname                = 'Project.$VAR[3]'
        }

        ACE = {
            $VAR[0] "$VAR[1]" "$VAR[2]"
            NO_LINE_SPACE
        }
    }

    RECORD External_name = {
        SQL = {
            select externalname from external_name where clonename = 'Project.$VAR[0]'
        }

        ACE = {
            External_clone "$VAR[0]"
            NO_LINE_SPACE
        }
    }

    // &add_project_summary_info

    RECORD Proj_summary_info = {
        SQL = {
            SELECT num_contigs, total_ctg_len, num_lge_contigs, lge_ctg_len, 
                   num_reads, to_char(summarydate,'yyyy-mm-dd_hh24:mi:ss'),
                   finished_length_bp, coverage
            FROM   project_summary
            WHERE  projectname = 'Project.$VAR[3]'
            ORDER BY summarydate DESC
        }

        ACE = {
            Nbr_contigs            $VAR[0]
            Total_contig_length    $VAR[1]
            Nbr_large_contigs      $VAR[2]
            Large_contig_length    $VAR[3]
            Nbr_gel_readings       $VAR[4]
            Last_updated           $VAR[5]
            Finished_contig_length $VAR[6]
            Coverage               $VAR[7]
            NO_LINE_SPACE FIRST_RECORD
        }
    }

    // &add_project_owner_info

    RECORD Proj_owner_info = {
        SQL = {
            SELECT TEAMNAME, OWNER_TYPE, OWNED_FROM
            FROM   PROJECT_OWNER
            WHERE  (OWNER_TYPE, OWNED_FROM) IN ( SELECT OWNER_TYPE, MAX(OWNED_FROM)
                                                 FROM   PROJECT_OWNER
                                                 WHERE  PROJECTNAME = 'Project.$VAR[3]'
                                                 AND    OWNER_TYPE IN (2,4)
                                                 GROUP BY OWNER_TYPE)
            AND PROJECTNAME = 'Project.$VAR[3]' 
        }

        WHERE = {
            foreach my $row_ref (@{$REF_TO_ARRAY}){
                if( $row_ref->[1] == 2 ){
                    $row_ref->[0] = "Production_group \"$row_ref->[0]\"";
                }
                elsif( $row_ref->[1] == 4 ){
                    $row_ref->[0] = "Finishing_group  \"$row_ref->[0]\"";
                }
            }
        }

        ACE = {
            $VAR[0]
            NO_LINE_SPACE
        }
    }

    // &process_submission_records2 - PART 1

    RECORD Submission_names = {
        SQL = {
            SELECT b.name
            FROM   unfinished_submission a, embl_submission b 
            WHERE  a.accession   = b.accession
            AND    a.projectname = 'Project.$VAR[3]'
            UNION
            SELECT b.name
            FROM   finished_submission a, embl_submission b 
            WHERE  a.accession   = b.accession
            AND    a.projectname = 'Project.$VAR[3]'
            ORDER BY 1
            
        }

        ACE = {
             DNA_contig      "$VAR[0]"
             NO_LINE_SPACE
        }
    }

    // &process_submission_records2 - PART 2

    RECORD Submission_records = {
        SQL = {
            SELECT b.name, b.length_bp, b.accession 
            FROM   unfinished_submission a, embl_submission b 
            WHERE  a.accession   = b.accession
            AND    a.projectname = 'Project.$VAR[3]'
            UNION
            SELECT b.name, b.length_bp, b.accession 
            FROM   finished_submission a, embl_submission b 
            WHERE  a.accession   = b.accession
            AND    a.projectname = 'Project.$VAR[3]'
            ORDER BY 1
        }

        ACE = {
           LINE_SPACE

           DNA_contig              "$VAR[0]"
           Contig_EMBL_entryname   "$VAR[0]"
           Contig_EMBL_accession   "$VAR[2]"
           Contig_finished_length  $VAR[1]
        }
}
