/// <summary> /// Creates an aaacontactinfo table row and an sdorgcontactinfo row /// </summary> /// <param name="account"><see cref="MspAccount"/></param> /// <returns>MspId of aaacontactinfo row</returns> private void CreateAccountInformation(MspAccount account) { // Creates an aaacontactinfo table row that holds const string accContactInfoQuery = "INSERT INTO aaacontactinfo(contactinfo_id, emailid, landline, fax, web_url) " + "VALUES(nextval('contactinfo_id_ticket_seq'), @EmailAddress, @Tel, @Fax, @Website) RETURNING contactinfo_id"; var accContactInfoParam = new { Email = account.EmailAddress, Tel = account.TelephoneNumber, Fax = account.FaxNumber, Website = account.WebsiteUrl }; //Creates a sdorgcontactinfo table row that links the account and the account information var accInfoId = Connection.ExecuteScalar <long>(accContactInfoQuery, accContactInfoParam, Transaction); const string accContactInfoLinkQuery = "INSERT INTO sdorgcontactinfo(org_id, contactinfo_id) " + "VALUES (@AccountId, @InfoId)"; var accContactInfoLinkParam = new { AccountId = account.Id, InfoId = accInfoId }; Connection.Execute(accContactInfoLinkQuery, accContactInfoLinkParam, Transaction); }
/// <summary> /// Creates sitedefinition table row that holds site information /// </summary> /// <param name="account"></param> private void CreateSiteDefinition(MspAccount account) { const string siteDefQuery = "INSERT INTO sitedefinition(siteid) " + "VALUES (@SiteId)"; Connection.Execute(siteDefQuery, new { SiteId = account.DefaultSiteId }, Transaction); }
/// <inheritdoc /> public void Update(MspAccount account) { //Update account UpdateAccount(account); UpdateAccountInformation(account); var ciId = UpdateAccountDefinition(account); UpdateAccountCode(account, ciId); UpdateCiReference(account, ciId); }
private void UpdateAccount(MspAccount account) { const string query = "UPDATE sdorganization SET name = @Name, description = @Desc WHERE org_id = @MspId"; var param = new { Id = account.Id, Name = account.Name, }; Connection.Execute(query, param, Transaction); }
private void UpdateCiReference(MspAccount account, long ciid) { const string query = "UPDATE ci SET ciname = @Name, description = @Desc WHERE ciid = @CiId"; var param = new { CiId = ciid, Name = account.Name, }; Connection.Execute(query, param, Transaction); }
private void UpdateAccountCode(MspAccount account, long ciId) { const string query = "UPDATE accountci SET attribute_303 = @Code, WHERE ciid = @CiId"; var param = new { CiId = ciId, Code = account.Code }; Connection.Execute(query, param, Transaction); }
/// <summary> /// Create sdorganisation table row from MSP account entity and sets the defaultSite MspId /// </summary> /// <param name="account">The MSP account</param> private void CreateSite(ref MspAccount account) { const string siteQuery = "INSERT INTO sdorganization(org_id, name, createdtime, description) " + "VALUES (nextval('sdo_id_ticket_seq'), @Name, @UnixTimeStamp, 'Site created from SIS Synchronisation') RETURNING org_id"; var siteParam = new { Name = account.Name, UnixTimeStamp = DateTimeOffset.Now.ToUnixTimeMilliseconds() }; account.DefaultSiteId = Connection.ExecuteScalar <long>(siteQuery, siteParam, Transaction); }
/// <summary> /// Creates a ci table row from a MSP account entity /// </summary> /// <param name="account"><see cref="MspAccount"/></param> /// <returns>MspId of ci row</returns> private long CreateCiReference(MspAccount account) { const string ciQuery = "INSERT INTO ci(ciid, citypeid, ciname, description, siteid) " + "VALUES(nextval('ci_id_ticket_seq'), 38, @Name, @Desc, @SiteId) RETURNING ciid"; var ciParam = new { Name = account.Name, SiteId = account.DefaultSiteId }; return(Connection.ExecuteScalar <long>(ciQuery, ciParam, Transaction)); }
/// <summary> /// Creates a custom value row that represents the account code /// </summary> /// <param name="account"></param> /// <param name="ciId"></param> private void CreateAccountCode(MspAccount account, long ciId) { const string query = "INSERT INTO accountci(ciid, attribute_303) " + "VALUES (@CiId, @Code)"; var param = new { CiId = ciId, Code = account.Code, }; Connection.Execute(query, param, Transaction); }
private long UpdateAccountDefinition(MspAccount account) { var query = "UPDATE accountdefinition " + "SET org_name = @Name, lastupdatedtime = @UnixTimeStamp " + "WHERE org_id = @MspId RETURNING ciid"; var param = new { Name = account.Name, UnixTimeStamp = DateTimeOffset.Now.ToUnixTimeMilliseconds() }; return(Connection.ExecuteScalar <long>(query, param, Transaction)); }
/// <inheritdoc /> public void Create(MspAccount account) { //Create account CreateAccount(ref account); //Create row with extra information regarding site CreateSite(ref account); CreateSiteDefinition(account); //Create rows with extra information regarding account var ciId = CreateCiReference(account); CreateAccountInformation(account); CreateAccountDefinition(account, ciId); CreateAccountCode(account, ciId); }
/// <summary> /// Creates an accountdefinition table row that holds account information /// </summary> /// <param name="account"><see cref="MspAccount"/></param> /// <param name="ciId">MspId of ci row</param> private void CreateAccountDefinition(MspAccount account, long ciId) { const string accDefQuery = "INSERT INTO accountdefinition(org_id, log_logo, head_logo, org_name, hasattachment, login_uri, defaultsiteid, ciid, lastupdatedtime) " + "VALUES(@AccountId, 'Default', 'Default', @Name, false, @LoginUri, @SiteId, @CiId, @UnixTimeStamp) "; var accDefParam = new { AccountId = account.Id, Name = account.Name, LoginUri = account.Name.Replace(" ", string.Empty).ToLower(), UnixTimeStamp = DateTimeOffset.Now.ToUnixTimeMilliseconds(), SiteId = account.DefaultSiteId, CiId = ciId, }; Connection.Execute(accDefQuery, accDefParam, Transaction); }
private void UpdateAccountInformation(MspAccount account) { const string query = "UPDATE aaacontactinfo " + "SET emailid = @EmailAddress, landline = @Tel, fax= @Fax, web_url = @Website " + "FROM sdorgcontactinfo " + "WHERE sdorgcontactinfo.contactinfo_id = aaacontactinfo.contactinfo_id " + "AND org_id = @MspId)"; var param = new { Email = account.EmailAddress, Tel = account.TelephoneNumber, Fax = account.FaxNumber, Id = account.Id }; Connection.Execute(query, param, Transaction); }
/// <inheritdoc /> public MspAccount GetByCode(string code) { MspAccount account = null; var result = Connection.Query("SELECT ad.org_id, ad.org_name, ad.defaultsiteid, ci.description, aci.emailid," + " aci.landline, aci.fax, aci.web_url, acci.attribute_303 FROM accountdefinition as ad" + " JOIN ci ON ad.ciid = ci.ciid" + " LEFT JOIN sdorgcontactinfo as sdoci ON ad.org_id = sdoci.org_id" + " LEFT JOIN accountci as acci ON ad.ciid = acci.ciid" + " LEFT JOIN aaacontactinfo as aci ON sdoci.contactinfo_id = aci.contactinfo_id" + " WHERE acci.attribute_303=@Code", new { Code = code }, Transaction).SingleOrDefault(); if (result != null) { account = MapAccount(result); } return(account); }
/// <summary> /// Synchronizes the MSP accounts /// </summary> public async Task SyncAccountsAsync() { HashSet <IAccount> mspAccounts; using (var uow = _dapperUowFactory.Create()) { mspAccounts = uow.MspAccountRepository.GetAll().ToHashSet <IAccount>(); uow.Commit(); } var sisAccounts = await _sisAccountRepository.GetAllAsync(); var sisAccountsHashSet = sisAccounts.ToHashSet <IAccount>(); // Get updated/new accounts in SIS by comparing to MSP accounts var changedAccounts2 = sisAccountsHashSet.Except(mspAccounts, new AccountValueComparer()).ToList(); // Get new accounts by finding all accounts that do not exists var newAccounts = changedAccounts2.Except(mspAccounts, new AccountEqualityComparer()); // Get updated accounts by finding all accounts with a code that does exist in MSP var updatedAccounts = changedAccounts2.Intersect(mspAccounts, new AccountEqualityComparer()); using (var uow = _dapperUowFactory.Create()) { foreach (var account in newAccounts) { var mspAccount = new MspAccount(account); uow.MspAccountRepository.Create(mspAccount); } foreach (var account in updatedAccounts) { var mspAccount = new MspAccount(account); uow.MspAccountRepository.Update(mspAccount); } uow.Commit(); } }