Skip to end of metadata
Go to start of metadata

Ticket / Anfrage

Bei folgenden Kunden kann das Flag "Externally Managed" nicht gesetzt werden:

100459165 Andrew Mahr
100444439 Jonas Stalder
100467123 Roger Schurtenberger
100462523 Kevin Mijatovic

Ich habe mit GetCustomer die Infos geholt und wollte dann diese Informationen mit Update Customer wieder schicken, mit dem Unterschied, dass ich "IsExternallyManaged" von <false> auf <true> geändert habe. Dieses Vorgehen habe ich bei vielen anderen auch gemacht.

Analyse 

Es handelt sich bei allen Kunden um Kunden, welche umgezogen sind und dadurch die UserID nicht mehr mit dem Username (Systemuser) übereinstimmt. Dies führt dazu, dass die SystemSecurityRoleForUser aufgrund eines Foreign Key Contstraint nicht gesetzt werden kann.

Log
2020-07-22 10:06:31,647 [63] INFOR  Finecom.Quickline.Common.LoggerContextV2 SetContextProperties {"Id":"4849e746-27a3-46c4-b0eb-2ef8dd1787d1", "IpAddress":"212.60.62.84", "Url":"https://public-api.quickline.ch/00/QMapi/v01/Customers/1384710_4058601_3254800_P7410", "CustomerId":"1384710_4058601_3254800_P7410", }
2020-07-22 10:06:31,882 [63] INFOR  Finecom.Quickline.Business.DomainServices.RoleManagerService Adding role "276" to user "100459165" (User is externally managed)
2020-07-22 10:06:32,007 [63] ERROR  Quickline.Services.QMApi.Controllers.BaseApiController an exception occured
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblSystemSecurityRoleForUser_tblSystemUser". The conflict occurred in database "QMC", table "dbo.tblSystemUser", column 'fldUsername'.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommandInternal(String commandText, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at Finecom.Quickline.Infrastructure.QMCContext.RoleManagerAddRoleToUser(Int32 roleId, String username, String comment)
   at Finecom.Quickline.Infrastructure.Repositories.RoleManagerRepository.AddRoleToUser(Int32 roleId, String username, String comment)
   at Finecom.Quickline.Business.DomainServices.RoleManagerService.AddRoleToUser(Int32 roleId, String username, String comment, Boolean skipUserValidation)
   at Quickline.Services.QMApi.AppServices.V01.CustomerApplicationServiceV01.HandlingOfExternallyManagedConfiguration(Boolean isExternallyManaged, String qmcCustomerId)
   at Quickline.Services.QMApi.AppServices.V01.CustomerApplicationServiceV01.UpdateCustomer(String externalCustomerId, CustomerDto customer)
   at Quickline.Services.QMApi.Controllers.BaseApiController.ProcessAppServiceFunc(Func`1 appServiceFunc)
ClientConnectionId:746ba9c9-ef3d-4431-a7c9-aade93448def
Error Number:547,State:0,Class:16
2020-07-22 10:06:32,022 [34] INFOR  Quickline.Services.QMApi.Startup.Moduls.DatabaseOnlyLogger Response-Content: null. Response-StatusCode: InternalServerError

Workaround

WWZ meldet sich per Tickets wenn das Setting bei einem Kunden gesetzt werden muss: OSS/BSS setzt die Kategorie

Workaround Externally Managed
DECLARE @userid INT = 100459165
DECLARE @username VARCHAR(20)
DECLARE @ticket VARCHAR(20) = 'QL-01187631'
DECLARE @History VARCHAR(MAX) 

SELECT @username=fldusername FROM dbo.tblSystemUser WHERE fldUserID = @userid
SELECT @History = 'Userkategorie Externally Managed gemäss Ticket ' + @ticket + ' gesetzt Profilname: '+@username

INSERT INTO dbo.tblUserCategoryREL
(
    fldUserCategoryId
  , fldUserId
  , fldTimestamp
)
VALUES
( 73, @userid, GETDATE())

INSERT INTO dbo.tblSystemSecurityRoleForUser
(
    fldSystemSecurityRoleId
  , fldUsername
  , fldComment
)
VALUES
( 276, @username, @ticket  )

EXEC dbo.sp_InsertUserEvent @UserID = @userid, @ContractID = NULL , @EventTypeID = 7, @Desc = @History