//public ActionResult CreateCashBook_Group()
//{
// List<CashBookReportDto> lstCashbook = GetAllCashBookReceipts(GroupInfo.GroupID);
// ViewBag.GroupCode = GroupInfo.GroupCode;
// ViewBag.GroupName = GroupInfo.GroupName;
// ViewBag.Cluster = GroupInfo.Cluster;
// ViewBag.Village = GroupInfo.Village;
// return View("CreateCashBook", new List<CashBookReportDto>());
//}
public ActionResult GetCashBook(FormCollection form)
{
string FromDate = form["FromDate"];
string ToDate = form["ToDate"];
CashBookReportDto cashbookReportDto = GetAllCashBookReceipts(GroupInfo.GroupID, FromDate, ToDate);
return(View("CreateCashBook", cashbookReportDto));
}
public async Task <ApiResult <CashBookReportDto> > GetCashBookReport(DateTime fromDate, DateTime toDate, int pageIndex, int pageSize, string accountId)
{
var checkEmployee = await _context.Employees.Where(x => x.AppuserId.ToString() == accountId)
.SingleOrDefaultAsync();
if (checkEmployee == null)
{
return(new ApiResult <CashBookReportDto>(HttpStatusCode.NotFound, "Lỗi tài khoản đăng nhập"));
}
var totalReceivedPrevious = await _context.ReceiptVouchers.Where(x => x.ReceivedDate < fromDate && x.BranchId == checkEmployee.BranchId).SumAsync(x => x.Received);
var totalPaidPrevious = await _context.PaymentVouchers.Where(x => x.PaymentDate < fromDate && x.BranchId == checkEmployee.BranchId).SumAsync(x => x.Paid);
var totalReceived = await _context.ReceiptVouchers
.Where(x => x.ReceivedDate >= fromDate && x.ReceivedDate <= toDate && x.BranchId == checkEmployee.BranchId).SumAsync(x => x.Received);
var totalPaid = await _context.PaymentVouchers
.Where(x => x.PaymentDate >= fromDate && x.PaymentDate <= toDate && x.BranchId == checkEmployee.BranchId).SumAsync(x => x.Paid);
var results = new CashBookReportDto()
{
SurplusBeginningValue = totalReceivedPrevious - totalPaidPrevious,
TotalReceivedValue = totalReceived,
TotalPaymentValue = totalPaid,
EndingStocksValue = totalReceivedPrevious - totalPaidPrevious + totalReceived - totalPaid,
Rows = new PagedResult <CashBookRowReportDto>()
};
var stockBookRows = new List <CashBookRowReportDto>();
using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
await connection.OpenAsync();
var resultReceiptVoucher = await connection.QueryAsync <CashBookRowReportDto>(
@"EXEC prGetReceiptVoucherForCashBookReport @fromDate=@FromDate,@toDate=@ToDate,@branchId=@BranchId",
new { FromDate = fromDate.ToString("yyyy/MM/dd"), ToDate = toDate.ToString("yyyy/MM/dd"), BranchId = checkEmployee.BranchId });
stockBookRows.AddRange(resultReceiptVoucher);
var resultPaymentVoucher = await connection.QueryAsync <CashBookRowReportDto>(
@"EXEC prGetPaymentVoucherForCashBookReport @fromDate=@FromDate,@toDate=@ToDate,@branchId=@BranchId",
new { FromDate = fromDate.ToString("yyyy/MM/dd"), ToDate = toDate.ToString("yyyy/MM/dd"), BranchId = checkEmployee.BranchId });
stockBookRows.AddRange(resultPaymentVoucher);
}
results.Rows = stockBookRows.MapPage(pageIndex, pageSize);
return(new ApiResult <CashBookReportDto>(HttpStatusCode.OK, results));
}
public ActionResult CreateCashBook_Group()
{
string FromDate = Request.Form["FromDate"];
string ToDate = Request.Form["ToDate"];
CashBookReportDto CashbookReportDto = new CashBookReportDto();
if (FromDate != null && ToDate != null)
{
CashbookReportDto = GetAllCashBookReceipts(GroupInfo.GroupID, FromDate, ToDate);
ViewBag.FromDate = (FromDate.ConvertToDateTime()).ToDisplayDateFormat();
ViewBag.ToDate = (ToDate.ConvertToDateTime()).ToDisplayDateFormat();
}
//List<CashBookReportDto> lstCashbook = GetAllCashBookReceipts(GroupInfo.GroupID);
bool isGroup = GroupInfo != null && GroupInfo.GroupID > 1;
ViewBag.isGroup = isGroup;
ViewBag.GroupCode = GroupInfo.GroupCode;
ViewBag.GroupName = GroupInfo.GroupName;
ViewBag.Cluster = GroupInfo.Cluster;
ViewBag.Village = GroupInfo.Village;
ViewBag.MeetingDay = GroupInfo.MeetingDay;
return(View("CreateCashBook", CashbookReportDto));
}
//public ActionResult ExportsReports_Group()
//{
// //List<CashBookReportDto> lstCashbook = GetAllCashBookReceipts(GroupInfo.GroupID);
// ReportDocument rd = new ReportDocument();
// rd.Load(Server.MapPath("~/RegistersReports/CashBook.rpt"));
// rd.SetDataSource(lstCashbook);
// Response.Buffer = false;
// Response.ClearContent();
// Response.ClearHeaders();
// try
// {
// Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
// stream.Seek(0, SeekOrigin.Begin);
// return File(stream, "application/pdf", "CashbookReceipt.pdf");
// }
// catch (Exception ex)
// {
// throw;
// }
//}
#endregion Group CashBook END
#region Federation CashBook BEGIN
//[HttpPost]
//public ActionResult CreateCashBook_Group()
//{
// string FromDate = Request.Form["FromDate"];
// string ToDate = Request.Form["ToDate"];
// List<CashBookReportDto> lstCashbook = GetAllCashBookReceipts(GroupInfo.GroupID, FromDate, ToDate);
// return View("CreateCashBook", lstCashbook);
//}
//public ActionResult ExportsReports()
//{
// List<CashBookReportDto> lstCashbook = GetAllCashBookReceipts(0);
// ReportDocument rd = new ReportDocument();
// rd.Load(Server.MapPath("~/RegistersReports/CashBook.rpt"));
// rd.SetDataSource(lstCashbook);
// Response.Buffer = false;
// Response.ClearContent();
// Response.ClearHeaders();
// try
// {
// Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
// stream.Seek(0, SeekOrigin.Begin);
// return File(stream, "application/pdf", "CashbookReceipt.pdf");
// }
// catch (Exception ex)
// {
// throw;
// }
//}
#endregion Federation CashBook END
#region Common
private CashBookReportDto GetAllCashBookReceipts(int?groupID, string fromdate, string todate)
{
CashBookReportDto cashBookReportDto = new CashBookReportDto();
List <MemberReceiptDto> memberReceipts = new List <MemberReceiptDto>();
List <AccountVoucherDto> accountVouchers = new List <AccountVoucherDto>();
OpeningBalanceDto objOpeningBalanceDto = new OpeningBalanceDto();
SqlCommand cmd = new SqlCommand("uspCashbookReport", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@GroupID", groupID);
cmd.Parameters.AddWithValue("@FromDate", fromdate);
cmd.Parameters.AddWithValue("@ToDate", todate);
cmd.CommandTimeout = 0;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
MemberReceiptDto obj = new MemberReceiptDto();
obj.MemberName = Convert.ToString(dr["MemberName"]);
obj.ReceiptNo = Convert.ToString(dr["VoucherNumber"]);
if (dr["Total"] != DBNull.Value)
{
obj.TotalReceipt = Convert.ToInt32(dr["Total"]);
}
obj.Particulers = Convert.ToString(dr["Particulars"]);
if (dr["Date"] != DBNull.Value)
{
obj.ReceiptDate = Convert.ToDateTime(dr["Date"]);
}
if (dr["BIGLOAN_PRINCIPAL"] != DBNull.Value)
{
obj.BigLoanPrin = Convert.ToDouble(dr["BIGLOAN_PRINCIPAL"]);
}
if (dr["BIGLOAN_INTEREST"] != DBNull.Value)
{
obj.BigLoanInt = Convert.ToDouble(dr["BIGLOAN_INTEREST"]);
}
if (dr["SMALLLOAN_PRINCIPAL"] != DBNull.Value)
{
obj.SmallLoanPrin = Convert.ToDouble(dr["SMALLLOAN_PRINCIPAL"]);
}
if (dr["SMALLLOAN_INTEREST"] != DBNull.Value)
{
obj.SmallLoanInt = Convert.ToDouble(dr["SMALLLOAN_INTEREST"]);
}
if (dr["HOUSINGLOAN_INTEREST"] != DBNull.Value)
{
obj.HousigLoanPrin = Convert.ToDouble(dr["HOUSINGLOAN_INTEREST"]);
}
if (dr["HOUSINGLOAN_PRINCIPAL"] != DBNull.Value)
{
obj.HousingLoanInt = Convert.ToDouble(dr["HOUSINGLOAN_PRINCIPAL"]);
}
if (dr["PRIMARY_SAVINGS"] != DBNull.Value)
{
obj.RegularSavings = Convert.ToDouble(dr["PRIMARY_SAVINGS"]);
}
if (dr["SPECIAL_SAVINGS"] != DBNull.Value)
{
obj.SpecialSavings = Convert.ToDouble(dr["SPECIAL_SAVINGS"]);
}
if (dr["OTHERS"] != DBNull.Value)
{
obj.Others = Convert.ToDouble(dr["OTHERS"]);
}
memberReceipts.Add(obj);
}
if (dr.NextResult())
{
while (dr.Read())
{
AccountVoucherDto objparticulers = new AccountVoucherDto();
objparticulers.Particulars = Convert.ToString(dr["Particulars"]);
objparticulers.AccountMasterId = Convert.ToInt32(dr["AccountMasterID"]);
if (dr["Date"] != DBNull.Value)
{
objparticulers.ReceiptDate = Convert.ToDateTime(dr["Date"]);
}
if (dr["CrAmount"] != DBNull.Value)
{
objparticulers.CrAmount = Convert.ToDouble(dr["CrAmount"]);
}
if (dr["DrAmount"] != DBNull.Value)
{
objparticulers.DrAmount = Convert.ToDouble(dr["DrAmount"]);
}
if (dr["Amount"] != DBNull.Value)
{
objparticulers.Amount = Convert.ToDouble(dr["Amount"]);
}
objparticulers.ReceiptNo = Convert.ToString(dr["VoucherNumber"]);
accountVouchers.Add(objparticulers);
}
if (dr.NextResult())
{
while (dr.Read())
{
if (dr["OpeningBalance"] != DBNull.Value)
{
objOpeningBalanceDto.OpeningBalance = Convert.ToDouble(dr["OpeningBalance"]);
}
if (dr["ClosingBalance"] != DBNull.Value)
{
objOpeningBalanceDto.ClosingBalance = Convert.ToDouble(dr["ClosingBalance"]);
}
if (dr["TrasactionAmount"] != DBNull.Value)
{
objOpeningBalanceDto.TransactionAmount = Convert.ToDouble(dr["TrasactionAmount"]);
}
}
}
}
cashBookReportDto.accountVouchers = accountVouchers;
cashBookReportDto.memberReceipts = memberReceipts;
cashBookReportDto.openingBalaceDetails = objOpeningBalanceDto;
return(cashBookReportDto);
}
