public void AddNewWell(cWellForDatabase WellForDatabase)
{
string WellName = "\"" + WellForDatabase.PlateName + "_" + WellForDatabase.PosX + "x" + WellForDatabase.PosY + "\"";
string sql = "CREATE TABLE " + WellName + " ( rowid INTEGER PRIMARY KEY";
foreach (string TmpDescValue in NamesDescriptors)
sql += ", \"" + TmpDescValue + "\" REAL";
sql += ");";
SQLiteCommand cmdInsert = _SQLiteConnection.CreateCommand();
cmdInsert.CommandText = sql;
cmdInsert.ExecuteNonQuery();
SQLiteTransaction mytransaction = _SQLiteConnection.BeginTransaction();
SQLiteCommand mycommand = new SQLiteCommand(_SQLiteConnection);
sql = "INSERT INTO " + WellName + " (";
int Idx = 0;
foreach (string TmpDescValue in NamesDescriptors)
{
sql += "\"" + TmpDescValue + "\", ";
}
string TmpSql1 = sql;
sql = TmpSql1.Remove(TmpSql1.Length - 2);
sql += ") VALUES ( ";
string FormattedSQLCmd = sql.Remove(sql.Length - 2) + ")";
foreach (string TmpDescValue in NamesDescriptors)
sql += "?, ";
FormattedSQLCmd = sql.Remove(sql.Length - 2) + " )";
mycommand.CommandText = FormattedSQLCmd;
foreach (string TmpDescValue in NamesDescriptors)
{
SQLiteParameter Param = new SQLiteParameter(DbType.Double);
mycommand.Parameters.Add(Param);
}
foreach (List<double> Signature in WellForDatabase.ListValues)
{
Idx = 0;
foreach (double Value in Signature)
{
mycommand.Parameters[Idx].Value = Value;
Idx++;
}
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
private void generateDBFromCSVToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog CurrOpenFileDialog = new OpenFileDialog();
CurrOpenFileDialog.Filter = "csv files (*.csv)|*.csv";//|db files (*.db)|*.db|nc files (*.nc)|*.nc
CurrOpenFileDialog.Multiselect = false;
DialogResult Res = CurrOpenFileDialog.ShowDialog();
if (Res != DialogResult.OK) return;
FormForImportExcel CSVWindow = CellByCellFromCSV(CurrOpenFileDialog.FileNames[0]);
if (CSVWindow == null) return;
if (CSVWindow.ShowDialog() != System.Windows.Forms.DialogResult.OK) return;
if (CompleteScreening != null) CompleteScreening.Close3DView();
FolderBrowserDialog WorkingFolderDialog = new FolderBrowserDialog();
WorkingFolderDialog.ShowNewFolderButton = true;
WorkingFolderDialog.Description = "Select the working directory";
if (WorkingFolderDialog.ShowDialog() != DialogResult.OK) return;
//if (IsFileUsed(CurrOpenFileDialog.FileNames[0]))
//{
// MessageBox.Show("File currently used by another application.\n", "Loading error !", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return;
//}
//Microsoft.Research.Science.Data.DataSet Datacsv = Microsoft.Research.Science.Data.CSV.CsvDataSet.Open(CurrOpenFileDialog.FileNames[0]);
//int NumDesc = Datacsv.Variables.Count;
//for (int IdxDesc = 0; IdxDesc < NumDesc; IdxDesc++)
//{
// var DescInfo = Datacsv.Variables[IdxDesc];
// string NameDesc = DescInfo.Name;
// var TypeData = DescInfo.TypeOfData;
// string DataName = TypeData.Name;
//}
int NumPlateName = 0;
int NumRow = 0;
int NumCol = 0;
int NumWellPos = 0;
// int NumLocusID = 0;
// int NumConcentration = 0;
// int NumName = 0;
// int NumInfo = 0;
// int NumClass = 0;
int numDescritpor = 0;
for (int i = 0; i < CSVWindow.dataGridViewForImport.Rows.Count; i++)
{
string CurrentVal = CSVWindow.dataGridViewForImport.Rows[i].Cells[2].Value.ToString();
if ((CurrentVal == "Plate name") && ((bool)CSVWindow.dataGridViewForImport.Rows[i].Cells[1].Value))
NumPlateName++;
if ((CurrentVal == "Row") && ((bool)CSVWindow.dataGridViewForImport.Rows[i].Cells[1].Value))
NumRow++;
if ((CurrentVal == "Column") && ((bool)CSVWindow.dataGridViewForImport.Rows[i].Cells[1].Value))
NumCol++;
if ((CurrentVal == "Well position") && ((bool)CSVWindow.dataGridViewForImport.Rows[i].Cells[1].Value))
NumWellPos++;
if ((CurrentVal == "Descriptor") && ((bool)CSVWindow.dataGridViewForImport.Rows[i].Cells[1].Value))
numDescritpor++;
}
if (NumPlateName != 1)
{
MessageBox.Show("One and only one \"Plate Name\" has to be selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((NumRow != 1) && (GlobalInfo.OptionsWindow.radioButtonWellPosModeDouble.Checked == true))
{
MessageBox.Show("One and only one \"Row\" has to be selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((NumCol != 1) && (GlobalInfo.OptionsWindow.radioButtonWellPosModeDouble.Checked == true))
{
MessageBox.Show("One and only one \"Column\" has to be selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((NumWellPos != 1) && (GlobalInfo.OptionsWindow.radioButtonWellPosModeSingle.Checked == true))
{
MessageBox.Show("One and only one \"Well position\" has to be selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((numDescritpor < 1) && (CSVWindow.IsImportCSV))
{
MessageBox.Show("You need to select at least one \"Descriptor\" !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int Mode = 2;
if (GlobalInfo.OptionsWindow.radioButtonWellPosModeSingle.Checked) Mode = 1;
CsvFileReader CSVsr = new CsvFileReader(CurrOpenFileDialog.FileNames[0]);
CsvRow OriginalNames = new CsvRow();
if (!CSVsr.ReadRow(OriginalNames))
{
CSVsr.Close();
return;
}
int ColPlateName = GetColIdxFor("Plate name", CSVWindow);
int ColCol = GetColIdxFor("Column", CSVWindow);
int ColRow = GetColIdxFor("Row", CSVWindow);
int ColWellPos = GetColIdxFor("Well position", CSVWindow);
int[] ColsForDescriptors = GetColsIdxFor("Descriptor", CSVWindow);
int WellLoaded = 0;
int FailToLoad = 0;
// CompleteScreening.Columns = (int)CSVWindow.numericUpDownColumns.Value;
// CompleteScreening.Rows = (int)CSVWindow.numericUpDownRows.Value;
// CompleteScreening.ListDescriptors.Clean();
FormForProgress ProgressWindow = new FormForProgress();
ProgressWindow.Show();
CsvRow CurrentDesc = new CsvRow();
if (CSVsr.ReadRow(CurrentDesc) == false) return;
do
{
string OriginalPlatePlateName = CurrentDesc[ColPlateName];
string CurrentPlateName = CurrentDesc[ColPlateName];
string ConvertedName = "";
foreach (var c in System.IO.Path.GetInvalidFileNameChars())
{
ConvertedName = OriginalPlatePlateName.Replace(c, '-');
}
List<string> ListNameSignature = new List<string>();
for (int idxDesc = Mode + 1; idxDesc < ColsForDescriptors.Length + Mode + 1; idxDesc++)
ListNameSignature.Add(OriginalNames[idxDesc]);
cSQLiteDatabase SQDB = new cSQLiteDatabase(WorkingFolderDialog.SelectedPath + "\\" + ConvertedName, ListNameSignature, true);
do
{
string OriginalWellPos = CurrentDesc[ColWellPos];
string CurrentWellPos = OriginalWellPos;
int[] Pos = new int[2];
if (Mode == 1)
{
Pos = ConvertPosition(CurrentDesc[ColWellPos]);
if (Pos == null)
{
if (MessageBox.Show("Error in converting the current well position.\nGo to Edit->Options->Import-Export->Well Position Mode to fix this.\nDo you want continue ?", "Loading error !", MessageBoxButtons.YesNo, MessageBoxIcon.Error) == System.Windows.Forms.DialogResult.No)
{
CSVsr.Close();
return;
}
//else
// goto NEXTLOOP;
}
}
else
{
//if (int.TryParse(CurrentDesc[ColCol], out Pos[0]) == false)
// // goto NEXTLOOP;
//if (int.TryParse(CurrentDesc[ColRow], out Pos[1]) == false)
// goto NEXTLOOP;
}
cWellForDatabase WellForDB = new cWellForDatabase(OriginalPlatePlateName, Pos[0], Pos[1]);
List<List<double>> ListData = new List<List<double>>();
// for (int idxDesc = 0; idxDesc < ColsForDescriptors.Length; idxDesc++)
// ListData[idxDesc] = new List<double>();
ProgressWindow.label.Text = OriginalWellPos;
ProgressWindow.label.Refresh();
do
{
// CurrentWellPos = CurrentDesc[ColWellPos];
List<double> Signature = new List<double>();
for (int idxDesc = 0; idxDesc < ColsForDescriptors.Length; idxDesc++)
{
double Value;
if ((double.TryParse(CurrentDesc[ColsForDescriptors[idxDesc]], out Value)) && (!double.IsNaN(Value)))
{
//cDescriptor CurrentDescriptor = new cDescriptor(Value, CompleteScreening.ListDescriptors[idxDesc/* + ShiftIdx*/], CompleteScreening);
Signature.Add(Value);
}
/*else
{
FailToLoad++;
goto NEXTLOOP;
}*/
}
ListData.Add(Signature);
// WellForDB.AddSignature(Signature);
if (CSVsr.ReadRow(CurrentDesc) == false)
{
WellForDB.AddListSignatures(ListData);
SQDB.AddNewWell(WellForDB);
SQDB.CloseConnection();
goto NEXTLOOP;
}
CurrentPlateName = CurrentDesc[ColPlateName];
CurrentWellPos = CurrentDesc[ColWellPos];
} while (CurrentWellPos == OriginalWellPos);
WellForDB.AddListSignatures(ListData);
SQDB.AddNewWell(WellForDB);
} while (OriginalPlatePlateName == CurrentPlateName);
SQDB.CloseConnection();
} while (true);
NEXTLOOP: ;
ProgressWindow.Close();
FormForPlateDimensions PlateDim = new FormForPlateDimensions();
PlateDim.Text = "Load generated screening";
PlateDim.checkBoxAddCellNumber.Visible = true;
PlateDim.checkBoxIsOmitFirstColumn.Visible = true;
PlateDim.labelHisto.Visible = true;
PlateDim.numericUpDownHistoSize.Visible = true;
if (PlateDim.ShowDialog() != System.Windows.Forms.DialogResult.OK)
return;
LoadCellByCellDB(PlateDim, WorkingFolderDialog.SelectedPath);
}
private void backgroundWorkerForCSVtoDB_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
CSVsr = new CsvFileReader(PathName);
CSVsr.Separator = this.Delimiter.ToCharArray()[0];
CsvRow OriginalNames = new CsvRow();
if (!CSVsr.ReadRow(OriginalNames))
{
CSVsr.Close();
return;
}
int ColPlateName = GetColIdxFor("Plate name", CSVWindow);
int ColCol = GetColIdxFor("Column", CSVWindow);
int ColRow = GetColIdxFor("Row", CSVWindow);
int ColWellPos = GetColIdxFor("Well position", CSVWindow);
int ColPhenotypeClass = GetColIdxFor("Phenotype Class", CSVWindow);
int[] ColsForDescriptors = GetColsIdxFor("Descriptor", CSVWindow);
FormForProgress ProgressWindow = new FormForProgress();
ProgressWindow.Text = "CSV -> DB : processing";
ProgressWindow.Show();
CsvRow CurrentDesc = new CsvRow();
int TotalPlateNumber = 0;
int TotalObjectNumber = 0;
int TotalWellNumber = 0;
string OriginalPlatePlateName;
string CurrentPlateName;
string ConvertedName;
this.CompleteReportString += "Source file: " + PathName + "\n";
this.CompleteReportString += OriginalNames.Count + " features selected.\n";
this.CompleteReportString += "Time stamp: " + DateTime.Now.ToString() + " \n";
if (CSVsr.ReadRow(CurrentDesc) == false) return;
do
{
if (ColPlateName == -1)
{
ConvertedName = OriginalPlatePlateName = CurrentPlateName = "Generated Plate Name";
}
else
{
OriginalPlatePlateName = CurrentDesc[ColPlateName];
CurrentPlateName = CurrentDesc[ColPlateName];
ConvertedName = "";
foreach (var c in System.IO.Path.GetInvalidFileNameChars())
ConvertedName = OriginalPlatePlateName.Replace(c, '-');
}
List<string> ListNameSignature = new List<string>();
for (int idxDesc = 0/*Mode + 1*/; idxDesc < ColsForDescriptors.Length/* + Mode + 1*/; idxDesc++)
{
string TmpSignature = OriginalNames[ColsForDescriptors[idxDesc]];
TmpSignature = TmpSignature.Replace('[', '_');
TmpSignature = TmpSignature.Replace(']', '_');
ListNameSignature.Add(TmpSignature);
}
ListNameSignature.Add("Phenotype_Class");
ListNameSignature.Add("Phenotype_Confidence");
cSQLiteDatabase SQDB = new cSQLiteDatabase(SelectedPath + "\\" + ConvertedName, ListNameSignature, true);
this.CompleteReportString += "\n" + CurrentPlateName + ":\n";
TotalPlateNumber++;
do
{
string OriginalWellPos;
int[] Pos = new int[2];
if (Mode == 1)
{
Pos = ConvertPosition(CurrentDesc[ColWellPos]);
if (Pos == null)
{
if (MessageBox.Show("Error in converting the current well position.\nGo to Edit->Options->Import-Export->Well Position Mode to fix this.\nDo you want continue ?", "Loading error !", MessageBoxButtons.YesNo, MessageBoxIcon.Error) == System.Windows.Forms.DialogResult.No)
{
CSVsr.Close();
return;
}
//else
// goto NEXTLOOP;
}
OriginalWellPos = CurrentDesc[ColWellPos];
}
else
{
if (int.TryParse(CurrentDesc[ColCol], out Pos[0]) == false)
goto NEXTLOOP;
if (int.TryParse(CurrentDesc[ColRow], out Pos[1]) == false)
goto NEXTLOOP;
OriginalWellPos = ConvertPosition(int.Parse(CurrentDesc[ColCol]), int.Parse(CurrentDesc[ColRow]));// "("+CurrentDesc[ColCol]+","+CurrentDesc[ColRow]+")";
}
string CurrentWellPos = OriginalWellPos;
cWellForDatabase WellForDB = new cWellForDatabase(OriginalPlatePlateName, Pos[0], Pos[1]);
cExtendedTable ListData = new cExtendedTable();
// for (int idxDesc = 0; idxDesc < ColsForDescriptors.Length; idxDesc++)
// ListData[idxDesc] = new List<double>();
ProgressWindow.richTextBoxForComment.AppendText(CurrentPlateName + " : " + CurrentWellPos + "\n");
//ProgressWindow.label.Refresh();
ProgressWindow.Refresh();
do
{
// CurrentWellPos = CurrentDesc[ColWellPos];
cExtendedList Signature = new cExtendedList();
for (int idxDesc = 0; idxDesc < ColsForDescriptors.Length; idxDesc++)
{
double Value;
if (double.TryParse(CurrentDesc[ColsForDescriptors[idxDesc]], NumberStyles.Any, CultureInfo.InvariantCulture/*.CreateSpecificCulture("en-US")*/, out Value))
{
if (double.IsNaN(Value))
Signature.Add(0);
else
Signature.Add(Value);
}
else
{
Signature.Add(0);
}
}
// if the class of the phenotype is defined in the file then use it
// if not, put it at 0
double ValueClass;
if ((ColPhenotypeClass != -1) && (double.TryParse(CurrentDesc[ColPhenotypeClass], out ValueClass) == true))
{
double IntValue = (int)(ValueClass) % cGlobalInfo.ListCellularPhenotypes.Count;
Signature.Add(IntValue);
}
else
{
Signature.Add(0);
}
// finally add the classification confidence column (1 by default)
Signature.Add(1);
ListData.Add(Signature);
// WellForDB.AddSignature(Signature);
// manage the end of the file
if (CSVsr.ReadRow(CurrentDesc) == false)
{
WellForDB.AddListSignatures(ListData);
cFeedBackMessage FeedBackMessage = SQDB.AddNewWell(WellForDB);
SQDB.CloseConnection();
// this.CompleteReportString += FeedBackMessage.Message + "\n";
goto NEXTLOOP;
}
if (ColPlateName == -1)
CurrentPlateName = "Generated Plate Name";
else
CurrentPlateName = CurrentDesc[ColPlateName];
if (Mode == 1)
CurrentWellPos = CurrentDesc[ColWellPos];
else
{
int ResCol;
int ResRow;
if ((!int.TryParse(CurrentDesc[ColCol], out ResCol)) || (!int.TryParse(CurrentDesc[ColRow], out ResRow))) goto NEXTLOOP;
CurrentWellPos = ConvertPosition(ResCol, ResRow);
}
TotalObjectNumber++;
// NEXTSIGNATURE: ;
} while (CurrentWellPos == OriginalWellPos);
TotalWellNumber++;
WellForDB.AddListSignatures(ListData);
SQDB.AddNewWell(WellForDB);
ReportTable.ListRowNames.Add(CurrentPlateName + " : " + CurrentWellPos);
ReportTable[0].Add(ListData.Count);
ReportTable[0].ListTags.Add(CurrentPlateName + " : " + CurrentWellPos + "\n" + ListData.Count + " objects");
this.CompleteReportString += "\t" + CurrentWellPos + " : " + ListData.Count + " objects\n";
NEXTSIGNATURE: ;
} while (OriginalPlatePlateName == CurrentPlateName);
SQDB.CloseConnection();
} while (true);
NEXTLOOP: ;
ProgressWindow.Close();
this.CompleteReportString += "\n-----------------------------\n";
this.CompleteReportString += TotalPlateNumber + " plates\n";
this.CompleteReportString += TotalWellNumber + " wells\n";
this.CompleteReportString += TotalObjectNumber + " objects\n";
this.CompleteReportString += "\nDataBase location:\n" + SelectedPath;
// worker.ReportProgress(10);
}
public cFeedBackMessage AddNewWell(cWellForDatabase WellForDatabase)
{
cFeedBackMessage FeedBackMessage = new cFeedBackMessage(true,null);
//string WellName = "\"" + WellForDatabase.PlateName + "_" +
string WellName = "\"Wellx" + WellForDatabase.PosX + "x" + WellForDatabase.PosY + "\"";
FeedBackMessage.Message = WellName;
string sql = "CREATE TABLE " + WellName + " ( rowid INTEGER PRIMARY KEY";
foreach (string TmpDescValue in NamesDescriptors)
sql += ", \"" + TmpDescValue + "\" REAL";
sql += ");";
SQLiteCommand cmdInsert = _SQLiteConnection.CreateCommand();
cmdInsert.CommandText = sql;
try
{
cmdInsert.ExecuteNonQuery();
}
catch(SQLiteException excep)
{
FeedBackMessage.Message += " : " + excep.Message;
FeedBackMessage.IsSucceed = false;
return FeedBackMessage;
}
cmdInsert.Dispose();
cmdInsert = null;
SQLiteTransaction mytransaction = _SQLiteConnection.BeginTransaction();
SQLiteCommand mycommand = new SQLiteCommand(_SQLiteConnection);
sql = "INSERT INTO " + WellName + " (";
int Idx = 0;
foreach (string TmpDescValue in NamesDescriptors)
{
sql += "\"" + TmpDescValue + "\", ";
}
string TmpSql1 = sql;
sql = TmpSql1.Remove(TmpSql1.Length - 2);
sql += ") VALUES ( ";
string FormattedSQLCmd = sql.Remove(sql.Length - 2) + ")";
foreach (string TmpDescValue in NamesDescriptors)
sql += "?, ";
FormattedSQLCmd = sql.Remove(sql.Length - 2) + " )";
mycommand.CommandText = FormattedSQLCmd;
foreach (string TmpDescValue in NamesDescriptors)
{
SQLiteParameter Param = new SQLiteParameter(DbType.Double);
mycommand.Parameters.Add(Param);
}
foreach (List<double> Signature in WellForDatabase.ListValues)
{
Idx = 0;
foreach (double Value in Signature)
{
mycommand.Parameters[Idx].Value = Value;
Idx++;
}
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
mycommand.Dispose();
mycommand = null;
return FeedBackMessage;
}
