/// <summary> /// Numbers the of requests with errors. /// </summary> /// <returns></returns> public int NumberOfRequestsWithErrors() { int _numberOfRequestsWithErrors; if (!this.noCachedRequestNumbers && this.trustCachedNumberOfRequestsWithErrors) { _numberOfRequestsWithErrors = this.numberOfRequestsWithErrors; } else { DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database); int ret = recordSet.Execute("SELECT count(*) FROM requests WHERE error IS NOT NULL"); if (ret == 0) { _numberOfRequestsWithErrors = recordSet.GetRowCount() != 0 ? recordSet.GetRow(0).GetColumnInt(0) : 0; this.numberOfRequestsWithErrors = _numberOfRequestsWithErrors; this.trustCachedNumberOfRequestsWithErrors = true; } else { _numberOfRequestsWithErrors = 0; } } if (this.blockingRequest?.RequestNr > 0 && _numberOfRequestsWithErrors > 0) { return(_numberOfRequestsWithErrors - 1); } return(_numberOfRequestsWithErrors); }
/// <summary> /// Sets Version, Timezone, UtcOffset property of <see cref="CRMDatabase"/> /// </summary> private void SetVersionTimezoneUtcOffsetProperties() { var recordSet = new DatabaseRecordSet(this); recordSet.Query.Prepare("SELECT version, timezone, utctimeoffset FROM datamodel"); var ret = recordSet.Execute(1); if (ret > 0 && recordSet.GetRowCount() > 0) { var row = recordSet.GetRow(0); dataModelVersion = row?.GetColumn(0); if (row?.GetColumn(1) != null) { supportsTimezones = true; utcOffset = row.GetColumnInt(2); if (!row.IsNull(1)) { timeZoneName = row.GetColumn(1); } } else { supportsTimezones = false; utcOffset = 0; } } }
public string ResultToStringForStatement(string databaseStatement) { DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database); int ret = recordSet.Execute(databaseStatement); StringBuilder resultString = new StringBuilder(); if (ret == 0) { for (int i = 0; i < recordSet.GetColumnCount(); i++) { resultString.Append(i == 0 ? recordSet.GetColumnName(0) : $";{recordSet.GetColumnName(i)}"); } resultString.AppendLine(); for (int i = 0; i < recordSet.GetRowCount(); i++) { DatabaseRow row = recordSet.GetRow(i); for (int j = 0; j < recordSet.GetColumnCount(); j++) { resultString.Append(j == 0 ? row.GetColumn(0) : $";{row.GetColumn(j)}"); } resultString.AppendLine(); } } return(resultString.ToString()); }
/// <summary> /// Loads the unit. /// </summary> /// <param name="unitName"> /// Name of the unit. /// </param> /// <returns> /// The <see cref="object"/>. /// </returns> public object LoadUnit(string unitName) { object result = null; var recordSet = new DatabaseRecordSet(this.Database); var statementString = $"SELECT unitDef FROM {this.TableName} WHERE unitName = ?"; var ret = recordSet.Query.Prepare(statementString); if (!ret) { return(null); } recordSet.Query.Bind(1, unitName); recordSet.Execute(); if (recordSet.GetRowCount() == 0) { return(null); } var row = recordSet.GetRow(0); var def = row.GetColumn(0); result = JsonConvert.DeserializeObject <List <object> >(def as string); return(result); }
/// <summary> /// Maximums the request identifier from database. /// </summary> /// <returns></returns> public int MaxRequestIdFromDatabase() { int maxRequestId; DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database); int ret = recordSet.Execute("SELECT MAX(requestnr) FROM requests"); if (ret < 0) { return(ret); } maxRequestId = recordSet.GetRowCount() == 0 ? 0 : recordSet.GetRow(0).GetColumnInt(0); recordSet = new DatabaseRecordSet(this.Database); ret = recordSet.Execute("SELECT MAX(requestnr) FROM records"); if (ret < 0) { return(maxRequestId); } if (recordSet.GetRowCount() > 0) { int recordMaxRequest = recordSet.GetRow(0).GetColumnInt(0); if (recordMaxRequest > maxRequestId) { maxRequestId = recordMaxRequest; } } return(maxRequestId); }
/// <summary> /// Offlines the records for request. /// </summary> /// <returns></returns> public List <string> OfflineRecordsForRequest() { List <string> deleteRecordArray = null; IDatabase database = this.Storage.Database; database.BeginTransaction(); DatabaseRecordSet recordSet = new DatabaseRecordSet(database); if (recordSet.Query.Prepare("SELECT infoAreaid, recordid FROM records WHERE requestnr = ? AND mode = 'NewOffline'")) { recordSet.Query.Bind(1, (int)this.RequestNr); int ret = recordSet.Execute(); if (ret == 0) { int count = recordSet.GetRowCount(); if (count > 0) { deleteRecordArray = new List <string>(); for (int i = 0; i < count; i++) { DatabaseRow row = recordSet.GetRow(i); deleteRecordArray.Add($"{row.GetColumn(0)}.{row.GetColumn(1)}"); } } } } database.Commit(); return(deleteRecordArray); }
/// <summary> /// Loads from database. /// </summary> /// <param name="database">The database.</param> /// <returns> /// True if success, else false /// </returns> public override bool LoadFromDatabase(IDatabase database) { if (!base.LoadFromDatabase(database)) { return(false); } int ret = 0; DatabaseRecordSet recordSet = new DatabaseRecordSet(database); if (recordSet.Query.Prepare("SELECT data, filename, mimetype, infoareaid, recordid, fieldid FROM documentuploads WHERE requestnr = ?")) { recordSet.Query.Bind(1, this.RequestNr); ret = recordSet.Execute(); if (ret == 0) { int count = recordSet.GetRowCount(); if (count == 1) { DatabaseRow row = recordSet.GetRow(0); string databaseValue = row.GetColumn(0); if (databaseValue.StartsWith("file://")) { string lastPathComponent = databaseValue.StartsWith("file://localhost") ? Path.GetFileName(databaseValue) : databaseValue.Substring(7); this.LocalFileUrl = this.Storage.GetOfflineStoragePath(lastPathComponent); Task <byte[]> t = SimpleIoc.Default.GetInstance <IPlatformService>().StorageProvider.FileContents(this.LocalFileUrl); this.Data = t.Result; if (SimpleIoc.Default.GetInstance <ILogSettings>().LogUpSync) { SimpleIoc.Default.GetInstance <ILogger>().LogDebug($"Sync request {this.RequestNr}: document {this.LocalFileUrl} loaded ({this.Data.Length} bytes)", LogFlag.LogUpSync); } } else { this.LocalFileUrl = null; this.Data = Convert.FromBase64String(databaseValue); } this.FileName = row.GetColumn(1); this.MimeType = row.GetColumn(2); string infoAreaId = row.GetColumn(3); string recordId = row.GetColumn(4); this.RecordIdentification = StringExtensions.InfoAreaIdRecordId(infoAreaId, recordId); this.FieldId = row.GetColumnInt(5); } } } return(ret == 0 && this.Data != null); }
/// <summary> /// Attaches the next server request number. /// </summary> public void AttachNextServerRequestNumber() { IDatabase database = this.Storage.Database; database.BeginTransaction(); DatabaseRecordSet recordSet = new DatabaseRecordSet(database); if (recordSet.Query.Prepare("SELECT nextRequestNumber FROM requestcontrol")) { if (recordSet.Execute() == 0) { if (recordSet.GetRowCount() == 1) { DatabaseRow row = recordSet.GetRow(0); this.ServerRequestNumber = row.GetColumnInt(0); DatabaseStatement updateStatement = new DatabaseStatement(database); if (updateStatement.Prepare("UPDATE requestcontrol SET nextRequestNumber = ?")) { updateStatement.Bind(1, this.ServerRequestNumber + 1); if (updateStatement.Execute() != 0) { this.ServerRequestNumber = -1; } } if (this.RequestNr > 0) { updateStatement = new DatabaseStatement(database); if (updateStatement.Prepare("UPDATE requests SET serverRequestNumber = ? WHERE requestnr = ?")) { updateStatement.Bind(1, this.ServerRequestNumber); updateStatement.Bind(2, this.RequestNr); updateStatement.Execute(); } } } else { this.ServerRequestNumber = -1; } } } database.Commit(); }
/// <summary> /// Gets the last synchronize of dataset. /// </summary> /// <param name="datasetname"> /// The datasetname. /// </param> /// <param name="timestamp"> /// The timestamp. /// </param> /// <returns> /// The <see cref="int"/>. /// </returns> public int GetLastSyncOfDataset(string datasetname, out string timestamp) { var recordSet = new DatabaseRecordSet(this); if (recordSet.Execute($"SELECT synctimestamp FROM syncinfo WHERE datasetname = '{datasetname}'", 1) == 0 && recordSet.GetRowCount() > 0) { var row = recordSet.GetRow(0); timestamp = row.GetColumn(0); if (!string.IsNullOrEmpty(timestamp)) { return(1); } } timestamp = null; return(0); }
/// <summary> /// Numbers the of uncommitted requests. /// </summary> /// <returns></returns> public int NumberOfUncommittedRequests() { if (!this.noCachedRequestNumbers && this.trustCachedNumberOfRequests) { return(this.numberOfRequests); } DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database); int ret = recordSet.Execute("SELECT count(*) FROM requests"); if (ret == 0) { ret = recordSet.GetRowCount() != 0 ? recordSet.GetRow(0).GetColumnInt(0) : 0; this.numberOfRequests = ret; this.trustCachedNumberOfRequests = true; } return(ret); }
/// <summary> /// Requests the with nr. /// </summary> /// <param name="requestNr">The request nr.</param> /// <returns></returns> public UPOfflineRequest RequestWithNr(int requestNr) { DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database); if (recordSet.Query.Prepare("SELECT requestnr, requesttype, processtype FROM requests WHERE requestnr = ?")) { recordSet.Query.Bind(1, requestNr); int ret = recordSet.Execute(); if (ret != 0 || recordSet.GetRowCount() == 0) { return(null); } } else { return(null); } UPOfflineRequest request = this.RequestFromResultRow(recordSet.GetRow(0)); return(request); }
/// <summary> /// Loads this instance. /// </summary> /// <returns></returns> private int Load() { CRMDatabase database = this.DataStore.DatabaseInstance; DatabaseRecordSet recordSet = new DatabaseRecordSet(database); int ret = recordSet.Execute("SELECT infoareaid, recordid, rollbackinfo FROM rollbackinfo WHERE requestnr = ?", this.RequestId); if (ret != 0) { return(ret); } int rowCount = recordSet.GetRowCount(); for (int i = 0; i < rowCount; i++) { DatabaseRow row = recordSet.GetRow(i); string recordIdentification = $"{row.GetColumn(0)}.{row.GetColumn(1)}"; var rollbackinfo = row.GetColumn(2); this.AddRecordIdentificationRollbackInfo(recordIdentification, rollbackinfo); } return(0); }
/// <summary> /// Loads the table. /// </summary> /// <returns> /// The <see cref="bool"/>. /// </returns> public bool LoadTable() { if (this.Database.ExistsTable(this.TableName)) { var tableRecordSet = new DatabaseRecordSet(this.Database); var statementString = $"SELECT unitName, unitDef FROM {this.TableName}"; var ret = tableRecordSet.Query.Prepare(statementString); if (ret) { ret = tableRecordSet.Execute() == 0; } if (ret) { var unitCount = tableRecordSet.GetRowCount(); if (unitCount > 0) { this.unitDefStore = new Dictionary <string, string>(); for (var i = 0; i < unitCount; i++) { var row = tableRecordSet.GetRow(i); var theObject = row.GetColumn(1); var key = row.GetColumn(0); if (key != null) { this.unitDefStore.SetObjectForKey(theObject, key); } } } } return(true); } return(false); }
/// <summary> /// Checks the update before cache save with database. /// </summary> /// <param name="database">The database.</param> /// <returns>0, if success, else error number</returns> private int CheckUpdateBeforeCacheSaveWithDatabase(DatabaseBase database) { UPCRMTableInfo tableInfo = this.UndoRequest.DataStore.TableInfoForInfoArea(this.RecordIdentification.InfoAreaId()); StringBuilder selectStatement = new StringBuilder(); selectStatement.Append("SELECT "); bool first = true; List <string> allColumns = this.FieldDictionary.Keys.ToList(); foreach (string columnName in allColumns) { if (first) { first = false; } else { selectStatement.Append(", "); } selectStatement.Append(columnName); } if (first) { selectStatement.Append("recid"); } selectStatement.Append($" FROM {tableInfo.DatabaseTableName} WHERE recid = ?"); CRMDatabase db = this.UndoRequest.DataStore.DatabaseInstance; DatabaseRecordSet rs = new DatabaseRecordSet(db); if (!rs.Query.Prepare(selectStatement.ToString())) { return(-1); } rs.Query.Bind(1, this.RecordIdentification.RecordId()); int ret = rs.Execute(); if (ret != 0) { return(ret); } int rc = rs.GetRowCount(); if (rc == 0) { this.Mode = "UpdateNew"; this.UndoOperation = "Delete"; return(0); } if (rc > 1) { return(-1); } this.UndoOperation = "Update"; int colIndex = 0; DatabaseRow row = rs.GetRow(0); foreach (string col in allColumns) { string v = row.GetColumn(colIndex++); if (v != null) { UPCRMUndoField undoField = this.FieldDictionary[col]; undoField.OldValue = v; } } return(0); }
/// <summary> /// Loads from database. /// </summary> /// <param name="database">The database.</param> /// <returns>True if success, else false</returns> public virtual bool LoadFromDatabase(IDatabase database) { DatabaseRecordSet recordSet = new DatabaseRecordSet(database); string sql = @"SELECT json, error, errorcode, response, titleLine, detailsLine, imageName, serverRequestNumber, servertime, sessionid, translationkey, relatedInfo, baseerror, appversion, errorstack FROM requests WHERE requestnr = ?"; recordSet.Query.Prepare(sql); recordSet.Query.Bind(1, this.RequestNr); int ret = recordSet.Execute(); if (ret == 0 && recordSet.GetRowCount() > 0) { this.Loaded = true; DatabaseRow row = recordSet.GetRow(0); this.Json = row.GetColumn(0); this.blockExecution = false; if (!row.IsNull(1)) { string cError = row.GetColumn(1); if (cError == Constants.OFFLINEREQUEST_BLOCKED_TEXT) { this.blockExecution = true; } this.Error = row.GetColumn(1); } this.Code = row.GetColumnInt(2, -1); if (!row.IsNull(3)) { this.Response = row.GetColumn(3); } if (!row.IsNull(4)) { this.titleLine = row.GetColumn(4); } if (!row.IsNull(5)) { this.detailsLine = row.GetColumn(5); } if (!row.IsNull(6)) { this.imageName = row.GetColumn(6); } if (!row.IsNull(7)) { this.ServerRequestNumber = row.GetColumnInt(7); } if (!row.IsNull(8)) { this.ServerDateTime = row.GetColumn(8); } if (!row.IsNull(9)) { this.ServerSessionId = row.GetColumn(9); } if (!row.IsNull(10)) { this.ErrorTranslationKey = row.GetColumn(10); } if (!row.IsNull(11)) { string _relatedInfo = row.GetColumn(11); this.RelatedInfoDictionary = _relatedInfo.JsonDictionaryFromString(); } if (!row.IsNull(12)) { this.BaseErrorCode = row.GetColumnInt(12); } if (!row.IsNull(13)) { this.ApplicationVersion = row.GetColumn(13); } if (!row.IsNull(14)) { this.ErrorStack = row.GetColumn(14); } return(true); } return(false); }