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));
}
}
public object Data(JournalGridMain obj)
{
return(controller.GetJournalDetails(obj));
}