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