private static void addDataToRequest(
IList <IList <Object> > rangeValues,
Spreadsheets.SheetsRanges.SheetRangeData sheetRangeData,
object valueToWrite,
List <ValueRange> requestData)
{
var updateValue = new List <object>()
{
valueToWrite
};
// tiems langeliams, kuriuose nėra datos, sukuria ValueRange
for (int r = 0; r < rangeValues.Count; r++)
{
if (rangeValues[r].Count < sheetRangeData.FilterColumn.Index + 1 ||
rangeValues[r][sheetRangeData.FilterColumn.Index] == null ||
rangeValues[r][sheetRangeData.FilterColumn.Index].ToString().Trim() == string.Empty)
{
ValueRange vr = new ValueRange();
vr.Range = string.Format(sheetRangeData.FilterColumn.CellAddressFormat, r + sheetRangeData.StartRow);
vr.Values = new List <IList <object> > {
updateValue
};
requestData.Add(vr);
}
}
}
// vieno operatoriaus vienos GS lentelės duomenis - fetchina, paverčia WeldingInspection ir updateinaDB bei GS
private static void fetchConvertUpdateSheet(SheetsService service,
Spreadsheets.Operator operatorData, // lemia, katro operatoriaus spreadsheet
Spreadsheets.SheetsRanges.SheetRangeData rangeData, // lemia, katrą to operatoriaus lentelę
ConvertDataToListMethod convertMethod, // --
DbUpdateMethod dbUpdateMethod) // --
{
try
{
List <IList <Object> > data = SheetDataFetcher.Fetch(
operatorData.SpreadsheetId,
rangeData.RangeAddress,
rangeData.FilterColumn.Index,
service).ToList();
List <WeldingInspection> inspectionList = convertMethod(data, rangeData.FieldMappings, operatorData.OperatorId);
dbUpdateMethod(inspectionList);
results.AppendFormat("{0} {1} db update OK", operatorData.OperatorId, rangeData.SheetName).AppendLine();
allInspections = allInspections.Concat(inspectionList);
}
catch (Exception ex)
{
results.AppendFormat("{0} {1} db update FAIL.", operatorData.OperatorId, rangeData.SheetName).AppendLine();
Exception e;
if (ex.InnerException != null)
{
e = ex.InnerException;
}
else
{
e = ex;
}
results.AppendFormat("Exception message: {0}", e.Message).AppendLine();
results.AppendLine("Google Sheets lentelėje duomenys nepažymimi kaip įvesti į DB ir šitie duomenys nebus įtraukti į ataskaitą.");
results.AppendLine();
return;
}
// updateinama Google Sheets lentelė.
// jeigu GS updateinimas nutrūktų, informuoja, kad tokio operatoriaus tokie patikrinimai į dB sukišti, bet lentelė neatnaujinta.
try
{
BatchUpdateValuesResponse response = GSheetsUpdater.BatchUpdateSheet(operatorData.SpreadsheetId, rangeData, service);
results.AppendFormat("{0} {1} Google Sheets update OK", operatorData.OperatorId, rangeData.SheetName).AppendLine();
results.AppendLine();
}
catch (Exception ex)
{
results.AppendFormat("{0} {1} Google Sheets update FAIL.", operatorData.OperatorId, rangeData.SheetName).AppendLine();
results.AppendFormat("Failure info: {0}", ex.Message).AppendLine();
results.Append("DĖMESIO! Kadangi duomenys į duomenų bazę sukelti, tie duomenys bus įtraukti į ataskaitą, kaip sutvarkyti. ");
results.AppendLine("Google Sheets lentelę reikėtų sutvarkyti rankiniu būdu - prisijungti prie Google Sheets, atsidaryti lentelę ir joje pažymėti, kurie duomenys yra sukelti į DB.");
results.AppendLine();
}
}
// vieno operatoriaus vienos GS lentelės duomenis - fetchina, paverčia WeldingInspection ir patikrina
private static StringBuilder fetchConvertVerifySheet(SheetsService service,
Spreadsheets.Operator operatorData, // lemia, katro operatoriaus spreadsheet
Spreadsheets.SheetsRanges.SheetRangeData rangeData, // lemia, katrą to operatoriaus lentelę
ConvertDataToListMethod convertMethod, // --
VerifyObjectsMethod verifyMethod) // --
{
List <IList <Object> > data = SheetDataFetcher.Fetch(
operatorData.SpreadsheetId,
rangeData.RangeAddress,
rangeData.FilterColumn.Index,
service).ToList();
List <WeldingInspection> inspectionList = convertMethod(data, rangeData.FieldMappings, operatorData.OperatorId);
allInspections.Concat(inspectionList); // vėlesniam pasikartojimų tikrinimui
List <BadData> bdList = verifyMethod(inspectionList, operatorData.OperatorId);
return(BadDataReportCreator.CreatePlainTxt(bdList));
}
public static BatchUpdateValuesResponse BatchUpdateSheet(
string spreadsheetId,
Spreadsheets.SheetsRanges.SheetRangeData rangeData,
SheetsService service)
{
// gaunami duomenys iš sheetų
IList <IList <Object> > sheetValues = getRangeValues(spreadsheetId, rangeData.RangeAddress, service);
List <ValueRange> requestData = new List <ValueRange>();
string dateFormat = Properties.Settings.Default.DateFormat;
object valueToWrite = DateTime.Now.Date.ToString(dateFormat);
// pridedami duomenys į requestData
addDataToRequest(sheetValues, rangeData, valueToWrite, requestData);
// sukuriamas batch update request
BatchUpdateValuesRequest request = getUpdateValuesRequest(requestData);
// updateinamas spreadsheet
return(updateSheet(request, spreadsheetId, service));
}
/// <summary>
/// Single Google Sheets sheet:
/// 1. fetches data from the sheet
/// 2. converts it to WeldingInspection objects
/// 3. verifies if the objects are valid for updating DB
/// </summary>
/// <param name="service">Google Sheets service</param>
/// <param name="operatorData">which operator's spreadsheet</param>
/// <param name="rangeData">which sheet of the spreadsheet</param>
/// <param name="convertMethod">data converting method, specific to this sheet type</param>
/// <param name="verifyMethod">data verifying method specific to this sheet type</param>
/// <param name="sb">reference to the string builder to output workflow information</param>
/// <returns>success or failure</returns>
private static bool DoControllSingleSheet(
SheetsService service,
Spreadsheets.Operator operatorData, // lemia, katro operatoriaus spreadsheet
Spreadsheets.SheetsRanges.SheetRangeData rangeData, // lemia, katrą to operatoriaus lentelę
ConvertDataToWIMethod convertMethod, // tam lentelės tipui specifinis convert metodas
VerifyObjectsMethod verifyMethod, // tam lentelės tipui specifinis verify metodas
StringBuilder sb)
{
string operSheet = string.Format(Messages.Default.OperSheetFrmt,
operatorData.OperatorId, rangeData.SheetName);
// fetch data from GoogleSheets
List <IList <Object> > dataRows;
try
{
dataRows = SheetDataFetcher.Fetch(
operatorData.SpreadsheetId,
rangeData.RangeAddress,
rangeData.FilterColumn.Index,
service).ToList();
}
catch (Exception ex)
{
string errorText = Messages.Default.GSDataFetchFail;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + ex.Message);
LogWriter.Log(ex);
OnProgressUpdated(0, errorText);
return(false);
}
// convert row data to WeldingInspection objects
List <WeldingInspection> wiList;
try
{
wiList = convertMethod(dataRows, rangeData.FieldMappings, operatorData.OperatorId);
}
catch (BadDataException bdEx)
{
string errorText = operSheet + Messages.Default.BadGSData;
List <BadData> bdl = bdEx.BadDataList.ToList();
string bdListString = BadDataReportCreator.CreateString(bdl).ToString();
sb.AppendLine(errorText);
sb.AppendLine(bdListString);
OnProgressUpdated(0, errorText);
return(false);
}
catch (Exception ex)
{
string errorText = operSheet + Messages.Default.GSDataConvertFail;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + ex.Message);
LogWriter.Log(ex);
OnProgressUpdated(0, errorText);
return(false);
}
allInspections = allInspections.Concat(wiList);
// verify WeldingInspection objects
List <BadData> bdList;
bdList = verifyMethod(wiList, operatorData.OperatorId);
if (bdList.Count > 0)
{
string strBadDataList = BadDataReportCreator.CreateString(bdList.ToList()).ToString();
sb.AppendLine(operSheet + Messages.Default.GSDataVerifyResult).AppendLine(strBadDataList);
}
else
{
sb.AppendLine(operSheet + Messages.Default.GSDataVerifyOK);
}
return(true);
}
/// <summary>
/// Single Google Sheets sheet:
/// 1. fetches data from the sheet
/// 2. converts it to WeldingInspection objects
/// 3. updates database
/// 4. in GoogleSheets marks those rows which have been put into database
/// </summary>
/// <param name="service">Google Sheets service</param>
/// <param name="operatorData">which operator's spreadsheet</param>
/// <param name="rangeData">which sheet of the spreadsheet</param>
/// <param name="convertMethod">data converting method, specific to this sheet type</param>
/// <param name="dbUpdateMethod">db updating method specific to this sheet type</param>
/// <param name="sb">reference to the string builder to output workflow information</param>
/// <returns>success or failure</returns>
/// <exception cref="Weldings.DbUpdateException">is thrownon on failed db update and failed rollback if Settings.Default.AbortOnFailedRollback is set to true</exception>
private static bool DoControllSingleSheet(
SheetsService service,
Spreadsheets.Operator operatorData, // lemia, katro operatoriaus spreadsheet
Spreadsheets.SheetsRanges.SheetRangeData rangeData, // lemia, katrą to operatoriaus lentelę
ConvertDataToWIMethod convertMethod, // tam lentelės tipui specifinis convert metodas
DbUpdateMethod dbUpdateMethod, // tam lentelės tipui specifinis update metodas
StringBuilder sb)
{
string operSheet = string.Format(Messages.Default.OperSheetFrmt,
operatorData.OperatorId, rangeData.SheetName);
// fetch data from GoogleSheets
List <IList <Object> > dataRows;
try
{
dataRows = SheetDataFetcher.Fetch(
operatorData.SpreadsheetId,
rangeData.RangeAddress,
rangeData.FilterColumn.Index,
service).ToList();
}
catch (Exception ex)
{
string errorText = Messages.Default.GSDataFetchFail;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + ex.Message);
OnProgressUpdated(0, errorText);
LogWriter.Log(ex);
return(false);
}
// convert row data to WeldingInspection objects
List <WeldingInspection> wiList;
try
{
wiList = convertMethod(dataRows, rangeData.FieldMappings, operatorData.OperatorId);
// sb.AppendLine(operSheet + Messages.Default.GSDataConvertOK);
}
catch (BadDataException bdEx)
{
string errorText = operSheet + Messages.Default.BadGSData;
string bdListString = BadDataReportCreator.CreateString(bdEx.BadDataList.ToList()).ToString();
sb.AppendLine(errorText);
sb.AppendLine(bdListString);
OnProgressUpdated(0, errorText);
return(false);
}
catch (Exception ex)
{
string errorText = operSheet + Messages.Default.GSDataConvertFail;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + ex.Message);
OnProgressUpdated(0, errorText);
LogWriter.Log(ex);
return(false);
}
// update database
try
{
dbUpdateMethod(wiList);
sb.AppendLine(operSheet + Messages.Default.DBUpdateOK);
}
catch (DbUpdateException dbuEx) when(dbuEx.RollbackSuccess)
{
string errorText = operSheet + dbuEx.Message;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + dbuEx.InnerException.Message);
OnProgressUpdated(0, errorText);
LogWriter.Log(dbuEx);
return(false);
}
catch (DbUpdateException dbuEx) when(!dbuEx.RollbackSuccess)
{
string errorText = operSheet + dbuEx.Message;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + dbuEx.InnerException.Message);
OnProgressUpdated(0, errorText);
if (Settings.Default.AbortOnFailedRollback)
{
throw dbuEx;
}
return(false);
}
allInspections = allInspections.Concat(wiList);
// update Google Sheets
try
{
GSheetsUpdater.BatchUpdateSheet(operatorData.SpreadsheetId, rangeData, service);
sb.AppendLine(operSheet + Messages.Default.GSUpdateOK);
}
catch (Exception ex)
{
string errorText = operSheet + Messages.Default.GSUpdateFail;
sb.AppendLine(errorText);
sb.AppendLine(Messages.Default.FailureInfo + ex.Message);
sb.AppendLine(Messages.Default.GSUpdateFailInstruction);
OnProgressUpdated(0, errorText);
LogWriter.Log(ex);
return(true); // pagrindinis darbas - db updateinimas - pavyko, o sheet updateinimas yra tik detalės
}
return(true);
}