/*
* Publication Harvester
* Copyright (c) 2003-2006 Stellman & Greene Consulting
* Developed for Joshua Zivin and Pierre Azoulay, Columbia University
* http://www.stellman-greene.com/PublicationHarvester
*
* This program is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the Free Software
* Foundation; either version 2 of the License, or (at your option) any later
* version.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along with
* this program (GPL.txt); if not, write to the Free Software Foundation, Inc., 51
* Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using Com.StellmanGreene.CSVReader;
namespace Com.StellmanGreene.PubMed
{
///
/// Read and write lists of people
///
public class People
{
///
/// Table that contains the people to read (usually "People")
///
public string PeopleTable = "People";
///
/// Array of Person objects
///
public List PersonList;
///
/// Read a list of people from the database
///
/// Database to read from
public People(Database DB)
{
DataTable Results = DB.ExecuteQuery(
"SELECT " + Database.PEOPLE_COLUMNS + " FROM " + PeopleTable
);
CreatePersonsFromDataTable(Results);
}
///
/// Read a list of people from the database
///
/// Database to read from
public People(Database DB, string PeopleTable)
{
this.PeopleTable = PeopleTable;
DataTable Results = DB.ExecuteQuery(
"SELECT " + Database.PEOPLE_COLUMNS + " FROM " + PeopleTable
);
CreatePersonsFromDataTable(Results);
}
///
/// Read a list of people from the database given a SQL WHERE clause
///
/// Database to read from
/// Indicates whether or not to retrieve only non-narvested people
public People(Database DB, bool NonHarvestedOnly)
{
ArrayList Parameters = new ArrayList();
Parameters.Add(Database.Parameter(NonHarvestedOnly));
DataTable Results = DB.ExecuteQuery(
"SELECT " + Database.PEOPLE_COLUMNS + " WHERE Harvested = ?", Parameters
);
CreatePersonsFromDataTable(Results);
}
///
/// Read a list of people froman Excel file
///
/// Folder where the People file is located
/// Name of the People file
public People(string Folder, string Filename)
{
string[] Columns = { "setnb", "first", "middle", "last", "name1", "name2", "name3", "name4", "medline_search1" };
DataTable Results;
if (Filename.ToLower().EndsWith(".csv"))
{
Results = ReadCSVFile(Folder, Filename, Columns);
}
else
{
Results = ReadExcelFile(Folder, Filename, Columns);
}
CreatePersonsFromDataTable(Results);
}
///
/// Read the contents of a CSV file into a DataTable
///
/// Folder that contains the CSV file
/// Filename of the CSV file
/// Columns to read
/// A DataTable object that contains the contents of the file
public static DataTable ReadCSVFile(string Folder, string Filename, string[] Columns)
{
// Get the publication types from the input file
// We're replacing the ODBC CSV code with our own CSVReader
//
// string ConnectionString =
// "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
// + Folder + ";";
// OdbcConnection Connection = new OdbcConnection(ConnectionString);
//
// // Turn the column names into a SQL statement
// string ColumnSQL = "[" + String.Join("], [", Columns) + "]";
//
// // Read the data from the file and return it
// OdbcDataAdapter DataAdapter = new OdbcDataAdapter(
// "SELECT " + ColumnSQL + "FROM [" + Filename + "]",
// Connection
// );
// DataTable Results = new DataTable();
// DataAdapter.Fill(Results);
DataTable Results = CSVReader.CSVReader.ReadCSVFile(Folder + "\\" + Filename, true);
return Results;
}
///
/// Read the contents of the first worksheet of an Excel file into a DataTable
///
/// Folder that contains the Excel file
/// Filename of the Excel file
/// Columns to read
/// A DataTable object that contains the contents of the file
public static DataTable ReadExcelFile(string Folder, string Filename, string[] Columns)
{
// Open the Excel file and read the name of the first worksheet from it
string ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ Folder + "\\" + Filename
+ ";Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1\"";
OleDbConnection Connection = new OleDbConnection(ConnectionString);
Connection.Open();
DataTable ExcelTableSchema = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string Worksheet = "";
for (int Row = 0;
(Worksheet.Length == 0) && (Row <= ExcelTableSchema.Rows.Count - 1)
; Row++)
{
string ThisWorksheet;
ThisWorksheet = ExcelTableSchema.Rows[Row]["TABLE_NAME"].ToString();
if ((ThisWorksheet.Length >= 2)
&& (ThisWorksheet.StartsWith("'"))
&& (ThisWorksheet.EndsWith("'")))
{
ThisWorksheet =
ThisWorksheet.Substring(1, ThisWorksheet.Length - 2);
}
if ((ThisWorksheet.Length >= 1) && (ThisWorksheet.EndsWith("$")))
{
Worksheet = ThisWorksheet;
}
}
if (Worksheet.Length == 0)
throw new Exception("Unable to find a worksheet in the spreadsheet '" + Filename + "'");
// Turn the column names into a SQL statement
string ColumnSQL = "[" + String.Join("], [", Columns) + "]";
// Read the data from the worksheet
OleDbDataAdapter DataAdapter = new OleDbDataAdapter(
"SELECT " + ColumnSQL + "FROM [" + Worksheet + "]",
Connection
);
DataTable Results = new DataTable();
// Add each PersonToWrite to PersonList
DataAdapter.Fill(Results);
return Results;
}
///
/// Take a DataTable object that contains the results from a SQL query
/// (either against Excel or the People table in the database) and
/// turn it into the PersonList array
///
/// DataTable that contains either Excel or database People table
private void CreatePersonsFromDataTable(DataTable Results)
{
for (int RowNum = 0; RowNum < Results.Rows.Count; RowNum++)
{
DataRow Row = Results.Rows[RowNum];
Person ThisPerson = new Person(Row, Results.Columns);
if (PersonList == null)
PersonList = new List();
PersonList.Add(ThisPerson);
}
}
}
}