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