public object GetAccounts(int pageNumber, int pageSize, string accountName, string accountCategory)
{
dynamic postingEngine = new PostingEngineService().GetProgress();
if (postingEngine.IsRunning)
{
return Utils.Wrap(false, null, HttpStatusCode.OK, "Posting Engine is currently Running");
}
SqlHelper sqlHelper = new SqlHelper(connectionString);
List<SqlParameter> sqlParameters = new List<SqlParameter>
{
new SqlParameter("pageNumber", pageNumber), new SqlParameter("pageSize", pageSize),
new SqlParameter("accountName", accountName), new SqlParameter("accountCategory", accountCategory)
};
var query = new StringBuilder($@"SELECT total = COUNT(*) OVER()
,[account].[id] AS 'account_id'
,[account].[name]
,[account].[description]
,[account_type].[id] AS 'type_id'
,[account_type].[name] AS 'type'
,[account_category].[id] AS 'category_id'
,[account_category].[name] AS 'category'
,CASE WHEN EXISTS (SELECT TOP 1 [journal].[id] FROM [journal] WHERE [journal].[account_id] = [account].[id])
THEN 'Yes'
ELSE 'No'
END AS 'has_journal'
FROM [account]
LEFT JOIN [account_type] ON [account].[account_type_id] = [account_type].[id]
LEFT JOIN [account_category] ON [account_type].[account_category_id] = [account_category].[id]");
query.Append(accountName.Length > 0 ? " WHERE [account].[name] LIKE '%'+@accountName+'%'" : "");
query.Append(accountName.Length > 0
? (accountCategory.Length > 0 ? " AND [account_category].[name] LIKE '%'+@accountCategory+'%'" : "")
: (accountCategory.Length > 0 ? " WHERE [account_category].[name] LIKE '%'+@accountCategory+'%'" : ""));
query.Append(
" ORDER BY [account].[id] DESC OFFSET(@pageNumber - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY");
List<AccountsOutputDto> accounts = new List<AccountsOutputDto>();
MetaData meta = new MetaData();
using (var reader =
sqlHelper.GetDataReader(query.ToString(), CommandType.Text, sqlParameters.ToArray(),
out var sqlConnection))
{
while (reader.Read())
{
meta.Total = (int) reader["total"];
accounts.Add(mapper.MapAccounts(reader));
}
reader.Close();
sqlConnection.Close();
}
Logger.Info($"GetAccounts Executed at {DateTime.Now}");
return Utils.Wrap(true, accounts, HttpStatusCode.OK, null, meta);
}
public object GetTaxRates()
{
dynamic postingEngine = new PostingEngineService().GetProgress();
if (postingEngine.IsRunning)
{
return(Utils.Wrap(false, null, HttpStatusCode.OK, "Posting Engine is currently Running"));
}
SqlHelper sqlHelper = new SqlHelper(connectionString);
var query = new StringBuilder($@"SELECT [Id]
,[effective_from] AS EffectiveFrom
,[effective_to] AS EffectiveTo
,[long_term_tax_rate] AS LongTermTaxRate
,[short_term_tax_rate] AS ShortTermTaxRate
,[short_term_period] AS ShortTermPeriod
,[created_date] AS CreatedDate
,[last_updated_date] AS LastUpdatedDate
,[created_by] AS CreatedBy
,[last_updated_by] AS LastUpdatedBy
FROM [tax_rate]
WHERE [active_flag] = 1
ORDER BY [effective_from] ASC");
var taxRates = new List <TaxRateOutputDto>();
using (var reader =
sqlHelper.GetDataReader(query.ToString(), CommandType.Text, null, out var sqlConnection))
{
while (reader.Read())
{
taxRates.Add(new TaxRateOutputDto
{
Id = Convert.ToInt32(reader["Id"]),
EffectiveFrom = Convert.ToDateTime(reader["EffectiveFrom"]),
EffectiveTo = Convert.ToDateTime(reader["EffectiveTo"]),
LongTermTaxRate = Convert.ToDecimal(reader["LongTermTaxRate"]),
ShortTermTaxRate = Convert.ToDecimal(reader["ShortTermTaxRate"]),
ShortTermPeriod = Convert.ToDecimal(reader["ShortTermPeriod"]),
CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
LastUpdatedDate = Convert.ToDateTime(reader["LastUpdatedDate"]),
CreatedBy = reader["CreatedBy"].ToString(),
LastUpdatedBy = reader["LastUpdatedBy"].ToString()
});
}
reader.Close();
sqlConnection.Close();
}
taxRates = ValidateTaxPeriods(taxRates);
return(Utils.Wrap(true, taxRates));
}
public object GetMappedAccounts()
{
dynamic postingEngine = new PostingEngineService().GetProgress();
if (postingEngine.IsRunning)
{
return Utils.Wrap(false, null, HttpStatusCode.OK, "Posting Engine is currently Running");
}
try
{
SqlHelper sqlHelper = new SqlHelper(connectionString);
var query = new StringBuilder($@"SELECT [account].[id] AS 'account_id'
,[account].[name]
,[account].[description]
,[account_type].[id] AS 'type_id'
,[account_type].[name] AS 'type'
,[account_category].[id] AS 'category_id'
,[account_category].[name] AS 'category'
,[m].[id] AS 'map_id'
,[m].[third_party_account_id]
,[t].[third_party_account_name] AS 'third_party_account_name'
,[org].[organization_name] AS 'organization_name'
FROM [account]
LEFT JOIN [account_type] ON [account].[account_type_id] = [account_type].[id]
LEFT JOIN [account_category] ON [account_type].[account_category_id] = [account_category].[id]
LEFT JOIN [account_to_third_party_account_mapping] [m] ON [account].[id] = [m].[account_id]
LEFT JOIN [third_party_account] [t] ON [m].[third_party_account_id] = [t].[id]
LEFT JOIN [third_party_organization] [org] ON [t].[third_party_organization_id] = [org].[id]");
List<AccountsOutputDto> accounts = new List<AccountsOutputDto>();
using (var reader =
sqlHelper.GetDataReader(query.ToString(), CommandType.Text, null,
out var sqlConnection))
{
while (reader.Read())
{
accounts.Add(mapper.MapThirdPartyMappedAccounts(reader));
}
reader.Close();
sqlConnection.Close();
}
var result = accounts.GroupBy(account => account.AccountId)
.Select(group => new AccountsOutputDto
{
AccountId = group.Key,
AccountName = group.FirstOrDefault()?.AccountName,
Description = group.FirstOrDefault()?.Description,
TypeId = group.FirstOrDefault()?.TypeId,
Type = group.FirstOrDefault()?.Type,
CategoryId = group.FirstOrDefault()?.CategoryId,
Category = group.FirstOrDefault()?.Category,
HasMapping = group.FirstOrDefault()?.HasMapping,
ThirdPartyMappedAccounts = group.SelectMany(mapped => mapped.ThirdPartyMappedAccounts).ToList()
})
.ToList();
Logger.Info($"GetMappedAccounts Executed at {DateTime.Now}");
return Utils.Wrap(true, result, HttpStatusCode.OK, null, null);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public object GetJournalDetails(JournalGridMain obj)
{
try
{
// Get the Data, We will Get the Results Later
var transactionResults = Utils.GetWebApiData(allocationsURL);
dynamic postingEngine = new PostingEngineService().GetProgress();
if (postingEngine.IsRunning)
{
return(Utils.Wrap(false, null, HttpStatusCode.OK, "Posting Engine is currently Running"));
}
journalStats journalStats = new journalStats();
bool whereAdded = false;
int index = 0;
var totalCountColumn = obj.pageNumber == 1 ? "d.overall_count," : "";
var totalCountQuery = obj.pageNumber == 1 ? "overall_count = COUNT(*) OVER()," : "";
var query = $@"select
{totalCountQuery}
debit,
credit,
abs(debit) - abs(credit) as balance,
[id],
[account_id],
[fund],
[symbol] as Symbol,
[security_id],
AccountCategory,
AccountType,
accountName,
accountDescription,
[value],
[source],
[when],
[event],
[start_price],
[end_price],
[fxrate]
from vwJournal";
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("pageNumber", obj.pageNumber));
sqlParams.Add(new SqlParameter("pageSize", obj.pageSize));
foreach (var item in obj.filters)
{
bool orAdded = false;
index = 0;
int dataCount = item.data.Count - 1;
if (!whereAdded)
{
query = query + " where";
whereAdded = true;
}
else
{
query = query + " and ";
}
foreach (var value in item.data)
{
if (index == 0)
{
query = query + "(";
}
else
{
query = query + "or ";
}
query = query + $"{item.column} = @{item.column}{index}";
sqlParams.Add(new SqlParameter($"{item.column}{index}", value));
if (index == dataCount)
{
query = query + ")";
}
index++;
orAdded = true;
}
}
query = query + " ORDER BY [id] desc";
if (obj.pageSize > 0)
{
query = query + " OFFSET(@pageNumber -1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY";
}
Console.WriteLine("===");
Console.WriteLine(query);
Console.WriteLine("===");
var dataTable = sqlHelper.GetDataTable(query, CommandType.Text, sqlParams.ToArray());
transactionResults.Wait();
var res = JsonConvert.DeserializeObject <PayLoad>(transactionResults.Result);
var elements = JsonConvert.DeserializeObject <Transaction[]>(res.payload);
var dictionary = elements.ToDictionary(i => i.TradeId, i => i);
foreach (var element in dataTable.Rows)
{
var dataRow = element as DataRow;
dataRow["debit"] = Math.Abs(Convert.ToDecimal(dataRow["debit"]));
}
HelperFunctions.Join(dataTable, dictionary, "source");
var metaData = MetaData.ToMetaData(dataTable);
metaData.Total = dataTable.Rows.Count > 0 ? dataTable.Rows.Count : 0;
metaData.TotalRecords = obj.pageNumber == 1 && dataTable.Rows.Count > 0 ? Convert.ToInt32(dataTable.Rows[0][0]) : 0;
journalStats.totalCredit = 0;
journalStats.totalDebit = 0;
var jsonResult = JsonConvert.SerializeObject(dataTable);
dynamic json = JsonConvert.DeserializeObject(jsonResult);
var returnResult = Utils.Wrap(true, json, HttpStatusCode.OK, null, metaData, journalStats);
return(returnResult);
}
catch (Exception ex)
{
return(Utils.Wrap(false, null, HttpStatusCode.InternalServerError));
}
}