private void WeeklyDataTable(SQLiteConnection dbConn, int FarmId)
{
//Util.GetFarmID(CheckCellData.CellTypeString(_sheet.GetRow(2).GetCell(1)));
//file file = File.CreateText("debug.txt");
StreamWriter file = new StreamWriter("debug.txt");
file.Close();
for (int c = 1; c < _sheet.GetRow(1).LastCellNum; c++)
{
//using (StreamWriter sw = File.AppendText("debug.txt"))
//{
// sw.WriteLine("Column: " + c);
//}
if (CheckCellData.CellTypeNumeric(_sheet.GetRow(7).GetCell(c)) != -1)
{
string date = CheckCellData.CellTypeDate(_sheet.GetRow(1).GetCell(c)).ToString("yyyy-MM-dd");
if (!checkForExistingColumn(date, FarmId))
{
string output = "[";
for (int row = 0; row < dataRows.Length; row++)
{
//using (StreamWriter sw = File.AppendText("debug.txt"))
//{
// sw.WriteLine("--Row: " + (dataRows[row] + 1));
//}
if (row != dataRows.Length)
{
if (_sheet.GetRow(dataRows[row]).GetCell(c) == null)
{
continue;
}
else
{
string tmp = CheckCellData.CellTypeString(_sheet.GetRow(dataRows[row]).GetCell(c)).ToString();
//using (StreamWriter sw = File.AppendText("debug.txt"))
//{
// sw.WriteLine("-- --{" + tmp + "}");
//}
output = output + tmp + ",";
}
}
}
output = output.Substring(0, output.Length - 1);
output = output + /*CheckCellData.CellTypeNumeric(_sheet.GetRow(dataRows.Length - 1).GetCell(c)) +*/ "]";
command.CommandText = $"INSERT INTO Weekly_Data(Branch_ID, Date_Sent, Data_Array) VALUES({FarmId}, @Date_Sent,'{output}');";
command.Parameters.AddWithValue("@Date_Sent", date);
command.ExecuteNonQuery();
output = "";
}
}
}
}
private void CommentsTable(SQLiteConnection dBConnection, int BranchID)
{
//int BranchID = Util.GetFarmID(CheckCellData.CellTypeString(_sheet.GetRow(2).GetCell(1)));
Console.WriteLine(BranchID);
//Go through each column, to the last column with a date available
for (int c = 1; c < _sheet.GetRow(1).LastCellNum; c++)
{
string date = CheckCellData.CellTypeDate(_sheet.GetRow(1).GetCell(c)).ToString("yyyy-MM-dd");
Util.Date = date;
//check for empty column, if 'emptycount' == 0 then column is empty
int emptycount = 0;
for (int r = 2; r < 13; r++)
{
ICell checkCell = _sheet.GetRow(r).GetCell(c);
if (CheckCellData.CellTypeString(checkCell).Trim() == "" || checkCell == null)
{
emptycount++;
}
}
if (!checkForExistingColumn(date, BranchID) && emptycount < 10)
{
for (int r = 2; r < 13; r += 4)
{
if (r != 5 || r != 9)
{
_command.CommandText = "INSERT INTO Observations(Branch_ID, Date_Sent, Category, Description, Weather) VALUES (@Branch_ID, @Date_Sent, @Category, @Description, @Weather)";
_command.Parameters.AddWithValue("@Branch_ID", BranchID);
_command.Parameters.AddWithValue("@Date_Sent", date);
_command.Parameters.AddWithValue("@Category", GetComment(_sheet.GetRow(r).GetCell(1)));
_command.Parameters.AddWithValue("@Description", GetComment(_sheet.GetRow(r + 1).GetCell(1)));
_command.Parameters.AddWithValue("@Weather", GetComment(_sheet.GetRow(r + 2).GetCell(1)));
_command.ExecuteNonQuery();
}
}
}
}
}