public List <LedgerListForView> LedgerListForView(LedgerListViewRequest request)
{
using (var _context = new AccountsEntities())
{
string dataList = "[Acc_LedgerInfoForView] '" + request.fromDate + "','" + request.toDate + "','" + request.voucherType + "'";
var list = _context.Database.SqlQuery <LedgerListForView>(dataList).ToList <LedgerListForView>();
return(list);
}
}
public List <TrialBalance_View> ReportBalanceSheet(LedgerListViewRequest request)
{
using (var _context = new AccountsEntities())
{
string dataList = "[Rpt_BalanceSheet] '" + request.fromDate + "','" + request.toDate + "'";
var list = _context.Database.SqlQuery <TrialBalance_View>(dataList).ToList <TrialBalance_View>();
return(list);
}
}
public ActionResult GetLedgerList(LedgerListViewRequest request)
{
var response = new LedgerListViewResponse();
try
{
var job = _dalLedger.LedgerListForView(request);
if (job.Any())
{
var jobList = job.GroupBy(x => x.VoucherNo).Select(y => new LedgerListForView
{
LedgerId = y.FirstOrDefault().LedgerId,
TransactionDate = y.FirstOrDefault().TransactionDate,
VoucherNo = y.FirstOrDefault().VoucherNo,
VoucherType = y.FirstOrDefault().VoucherType,
VoucherName = y.FirstOrDefault().VoucherName,
Debit = y.Sum(x => x.Debit),
Credit = y.Sum(x => x.Credit),
Description = y.FirstOrDefault().Description,
Narration = y.FirstOrDefault().Narration,
Reference = y.FirstOrDefault().Reference,
ChartOfAccount = y.FirstOrDefault().ChartOfAccount,
VoucherId = y.FirstOrDefault().VoucherId,
CashOfAccountType = y.FirstOrDefault().CashOfAccountType,
CashOfAccount = y.FirstOrDefault().CashOfAccount,
Payable = y.FirstOrDefault().Payable,
Note = y.FirstOrDefault().Note,
Quantity = y.Sum(x => x.Quantity),
UnitPrice = y.Sum(x => x.UnitPrice),
Amount = y.FirstOrDefault().Amount
}).OrderByDescending(x => x.VoucherType);
response.StatusCode = "200";
response.StatusMessage = "Data fetch successfully";
response.LedgerListForView = jobList.ToList();
}
else
{
response.StatusCode = "400";
response.StatusMessage = "No data found on selected search";
}
}
catch (Exception ex)
{
response.StatusCode = "500";
response.StatusMessage = ex.Message.ToString();
}
var camelCaseFormatter = new JsonSerializerSettings();
camelCaseFormatter.ContractResolver = new CamelCasePropertyNamesContractResolver();
return(Json(JsonConvert.SerializeObject(response, camelCaseFormatter), JsonRequestBehavior.AllowGet));
}
public ActionResult BalanceSheet_HTML(LedgerListViewRequest request)
{
var response = new VoucherReportViewResponse();
var headerHtml = "";
var reportHeaderHtml = "";
var firstSection = "";
var table = "";
try
{
var list = _dalLedger.ReportBalanceSheet(request).Where(x => x.VoucherType == 1).ToList();
if (list.Any())
{
reportHeaderHtml = " <div align='center' class='ReportBorderL' >" +
"<div style='position:absolute;'>" +
"<img src='/UploadFiles/" + list.FirstOrDefault().LogoLocation + "' width='60' border='0' />" +
" </div>" +
"<table style='width: 100%'>" +
"<tr>" +
"<td class='Company'>" + list.FirstOrDefault().CompanyName + "</td>" +
"</tr>" +
"<tr>" +
"<td class='Address1'>" + list.FirstOrDefault().CompanyAddress + "</td>" +
"</tr>" +
" <tr>" +
" <td class='Address2'>" + list.FirstOrDefault().CompanyEmail + ", " + list.FirstOrDefault().CompanyContact + "</td>" +
"</tr>" +
// "<tr>" +
// "<td class='Address3'>" + list.FirstOrDefault().CompanyContact + "</td>" +
//" </tr>" +
"<tr>" +
"<td style='text-align: center'>" +
"<div align='center'>" +
"<div class='ReportTitle'>Balance Sheet" +
"</div>" +
"</div>" +
"</td>" +
"</tr>" +
"</table>" +
"<div runat='server' id='reportContent'></div>" +
"<br />";
firstSection = firstSection + "<div align='left'><table style='font-size:13px; background-color:#d2c8c8; width: 50%'>";
firstSection = firstSection + " <tr>";
firstSection = firstSection + "<td style='width: 180px'><b> Transaction Date</b></td>";
firstSection = firstSection + " <td style='width: 4px'><b>:</b></td>";
firstSection = firstSection + " <td style='width: 400px;'>" + list.FirstOrDefault().SearchDate + "</td>";
firstSection = firstSection + "</tr></table></div>";
table = "<br /><table class='tableApproval' cellspacing='0' style='font-size:12px; width: 100%; '><tr>" +
"<td style='text-align: center; font-weight:bold'>Chart of Account</td>" +
"<td style='text-align: center; font-weight:bold'>Debit</td>" +
"<td style='text-align: center; font-weight:bold'>Credit</td>" +
"</tr>";
var assetsList = list.Where(y => y.GroupId == 1);
//headerHtml = headerHtml + "</br><div><b>Income</b></div>";
table = table + "<tr><td style='font-weight:bold' colspan='3'>Assets</td></tr>";
int i = 0;
foreach (var item in assetsList)
{
i++;
//foreach (var lastItem in item)
//{
table = table + "<tr><td style='text-align: center;'>" + item.ChartOfAccount + "</td>" +
"<td style='text-align: center;'>" + item.Debit + "</td>" +
"<td style='text-align: center;'>" + item.Credit + "</td></tr>";
//}
if (assetsList.Count() == i)
{
table = table + "<tr><td colspan='1' style='font-weight:bold'>Total Assets</td>" +
"<td style='text-align: center; font-weight:bold'>" + assetsList.Sum(y => y.Debit) + "</td>" +
"<td style='text-align: center; font-weight:bold'>" + assetsList.Sum(x => x.Credit) + "</td></tr>"
+ "<tr><td colspan='3'></td></tr></br>";
}
}
var equityList = list.Where(x => x.GroupId == 4);
//headerHtml = headerHtml + "</br><div><b>Expense</b></div>";
table = table + "<tr><td style='font-weight:bold' colspan='3'>Liabilities & Equity</td></tr>";
int j = 0;
foreach (var item1 in equityList)
{
j++;
//foreach (var expenseItem in item1)
//{
table = table + "<tr><td style='text-align: center;'>" + item1.ChartOfAccount + "</td>" +
"<td style='text-align: center;'>" + item1.Debit + "</td>" +
"<td style='text-align: center;'>" + item1.Credit + "</td></tr>";
//}
if (equityList.Count() == j)
{
table = table + "<tr><td colspan='1' style='font-weight:bold'>Total Liabilities & Equity</td>" +
"<td style='text-align: center; font-weight:bold'>" + equityList.Sum(x => x.Debit) + "</td>" +
"<td style='text-align: center; font-weight:bold'>" + equityList.Sum(x => x.Credit) + "</td></tr></br>";
}
}
table = table + "</table>";
headerHtml = headerHtml + table;
response.StatusCode = "200";
response.StatusMessage = "Successfully Get Data";
response.Report = reportHeaderHtml + firstSection + headerHtml + "</div>";
}
else
{
response.StatusCode = "404";
response.StatusMessage = "No Data Found";
response.Report = "";
}
}
catch (Exception ex)
{
response.StatusCode = "500";
response.StatusMessage = ex.Message.ToString();
}
var camelCaseFormatter = new JsonSerializerSettings();
camelCaseFormatter.ContractResolver = new CamelCasePropertyNamesContractResolver();
var returnJson = Json(JsonConvert.SerializeObject(response, camelCaseFormatter), JsonRequestBehavior.AllowGet);
returnJson.MaxJsonLength = Int32.MaxValue;
return(returnJson);
}
