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(); } } } } }