public List <RefundReportDTO> GetListRefund(List <string> lstStoreIds, DateTime dFrom, DateTime dTo, int mode)
{
var result = new List <RefundReportDTO>();
//get business day
var lstBusinessDays = _baseFactory.GetBusinessDays(dFrom, dTo, lstStoreIds, mode);
if (lstBusinessDays != null && lstBusinessDays.Any())
{
var dMin = lstBusinessDays.Min(ss => ss.DateFrom);
var dMax = lstBusinessDays.Max(ss => ss.DateTo);
using (var cxt = new NuWebContext())
{
var query = (from r in cxt.R_Refund
join rd in cxt.R_RefundDetail on r.Id equals rd.RefundId
where r.CreatedDate >= dMin && r.CreatedDate <= dMax && lstStoreIds.Contains(r.StoreId)
select new { r, rd });
if (query != null && query.Any())
{
var lstHeader = query.Select(ss => ss.r).Distinct().ToList();
var lstRefundDetail = query.Select(ss => new RefundDetailReportDTO()
{
RefundId = ss.rd.RefundId,
ItemId = ss.rd.ItemId,
ItemType = ss.rd.ItemType,
ItemName = ss.rd.ItemName,
PriceValue = ss.rd.PriceValue,
Qty = ss.rd.Qty,
ServiceCharged = ss.rd.ServiceCharged,
Tax = ss.rd.Tax,
PromotionAmount = ss.rd.PromotionAmount,
DiscountAmount = ss.rd.DiscountAmount
}).ToList();
RefundReportDTO refund = null;
foreach (var item in lstHeader)
{
refund = new RefundReportDTO();
refund.Id = item.Id;
refund.BusinessDayId = item.BusinessDayId;
refund.CreatedDate = item.CreatedDate;
refund.CreatedUser = item.CreatedUser;
refund.Description = item.Description;
refund.StoreId = item.StoreId;
refund.TotalRefund = item.TotalRefund;
refund.Promotion = item.Promotion;
refund.ServiceCharged = item.ServiceCharged;
refund.Tax = item.Tax;
refund.Discount = item.Discount;
refund.OrderId = item.OrderId;
refund.ReceiptDate = item.ReceiptDate;
refund.ListDetails = lstRefundDetail.Where(ww => ww.RefundId == refund.Id).ToList();
result.Add(refund);
}
}
}
}
return(result);
}
public XLWorkbook ExportExcel_New(BaseReportModel model, List <StoreModels> lstStore)
{
string sheetName = "No_Sale_Detail_Report";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
string titleReport = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("No Sale Report");
CreateReportHeaderNew(ws, 8, model.FromDate, model.ToDate, titleReport.ToUpper());
// Get list business day
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
// Get data use business day
if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any())
{
// Format header report
ws.Range(1, 1, 4, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
//model.FromDate = _lstBusDayAllStore.Min(oo => oo.DateFrom);
//model.ToDate = _lstBusDayAllStore.Max(oo => oo.DateTo);
var _lstBusDayIdAllStore = _lstBusDayAllStore.Select(ss => ss.Id).ToList();
// Get list data
var listData = GetData_New(model.ListStores, _lstBusDayIdAllStore, model.Mode);
if (listData == null || !listData.Any())
{
// Format header report
ws.Range(1, 1, 4, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
int row = 5;
string storeId = string.Empty, storeName = string.Empty;
// Column Title
ws.Cell("A" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Store"));
ws.Cell("B" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Business Day"));
ws.Cell("C" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Shift"));
ws.Cell("D" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Drawer"));
ws.Cell("E" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time"));
ws.Cell("F" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Staff"));
ws.Cell("G" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Reason"));
ws.Cell("H" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total"));
ws.Range("A" + row + ":H" + row).Style.Font.SetBold(true);
ws.Range("A" + row + ":H" + row).Style.Fill.BackgroundColor = XLColor.FromHtml(Commons.BgColorDataRow);
row++;
List <NoSaleDetailReportModels> listDataStore = new List <NoSaleDetailReportModels>();
for (int i = 0; i < lstStore.Count; i++)
{
listDataStore = new List <NoSaleDetailReportModels>();
storeId = lstStore[i].Id;
storeName = lstStore[i].Name;
#region
// List Id of business day in a store
var businessInStoreId = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).Select(ss => ss.Id).ToList();
// Get list data depend on list business day in store
if (businessInStoreId != null && businessInStoreId.Any())
{
listDataStore = listData.Where(ww => businessInStoreId.Contains(ww.BusinessId)).ToList();
}
if (listDataStore != null && listDataStore.Any())
{
int startRowStore = row;
ws.Cell("A" + row).SetValue(storeName);
var businessInLstData = listDataStore.Select(s => s.BusinessId).Distinct().ToList();
var businessInfoInLstData = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId && businessInLstData.Contains(ww.Id)).OrderBy(o => o.DateFrom).ToList();
int total = 0;
int countBusDay = 0;
int countShift = 0;
int countDrawer = 0;
int countTime = 0;
int countStaff = 0;
foreach (var busDay in businessInfoInLstData)
{
int startRowBusDay = row;
countBusDay = 0;
// Set value for column Business day
ws.Cell("B" + row).SetValue(busDay.DateFrom.ToString("MM/dd/yyyy HH:mm tt") + " - " + busDay.DateTo.ToString("MM/dd/yyyy HH:mm tt"));
var listDataInBusDay = listData.Where(m => m.BusinessId == busDay.Id).OrderBy(m => m.StartedShift).ToList();
var listShift = listDataInBusDay
.Select(s => new
{
ShiftId = s.ShiftId,
startShift = s.StartedShift.Value,
closeShift = s.ClosedShift.Value
}).Distinct().ToList();
foreach (var shift in listShift)
{
int startRowShift = row;
countShift = 0;
// Set value for column Shift
ws.Cell("C" + row).SetValue(shift.startShift.ToString("MM/dd/yyyy HH:mm tt") + " - " + shift.closeShift.ToString("MM/dd/yyyy HH:mm tt"));
var listDataInShift = listDataInBusDay.Where(w => w.ShiftId == shift.ShiftId).OrderBy(w => w.DrawerName).ToList();
var listDrawer = listDataInShift.Select(s => new { DrawerId = s.DrawerId, DrawerName = s.DrawerName }).Distinct().ToList();
foreach (var drawer in listDrawer)
{
int startRowDrawer = row;
countDrawer = 0;
// Set value for column Drawer
ws.Cell("D" + row).SetValue(drawer.DrawerName);
var listDataInDrawer = listDataInShift.Where(w => w.DrawerId == drawer.DrawerId).OrderBy(o => o.CreatedDate).ToList();
var listTime = listDataInDrawer.Select(s => new { s.CreatedDate.Hour, s.CreatedDate.Minute }).Distinct().ToList();
foreach (var time in listTime)
{
int startRowTime = row;
countTime = 0;
// Set value for column Time
ws.Cell("E" + row).SetValue(new DateTime(1, 1, 1, time.Hour, time.Minute, 0).ToString("t"));
var listDataInTime = listDataInDrawer.Where(w => w.CreatedDate.Hour == time.Hour && w.CreatedDate.Minute == time.Minute).OrderBy(w => w.CreatedDate).ToList();
var listStaffID = listDataInTime.Select(s => s.CashierId).Distinct().ToList();
foreach (var staffID in listStaffID)
{
int startRowStaff = row;
countStaff = 0;
// Set value for column Staff
ws.Cell("F" + row).SetValue(listDataInTime.Where(w => w.CashierId == staffID).Select(s => s.CashierName).FirstOrDefault());
var listReason = listDataInTime.Where(w => w.CashierId == staffID).OrderBy(w => w.CreatedDate).Select(o => o.Reason).ToList();
foreach (var reason in listReason)
{
total++;
countBusDay++;
countShift++;
countDrawer++;
countTime++;
countStaff++;
// Set value for column Reason
ws.Cell("G" + row).SetValue(reason);
row++;
}
ws.Range(startRowStaff, 6, startRowStaff + countStaff - 1, 6).Merge();
}
ws.Range(startRowTime, 5, startRowTime + countTime - 1, 5).Merge();
}
ws.Range(startRowDrawer, 4, startRowDrawer + countDrawer - 1, 4).Merge();
}
ws.Range(startRowShift, 3, startRowShift + countShift - 1, 3).Merge();
}
ws.Range(startRowBusDay, 2, startRowBusDay + countBusDay - 1, 2).Merge();
// Set value for column Total
ws.Cell("H" + startRowBusDay).SetValue(countBusDay);
ws.Range(startRowBusDay, 8, startRowBusDay + countBusDay - 1, 8).Merge();
}
ws.Range(startRowStore, 1, startRowStore + total - 1, 1).Merge();
}
}
#endregion
// Format Report
ws.Range(5, 1, row - 1, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(5, 1, row - 1, 8).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(1, 1, row - 1, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, row - 1, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, row - 1, 8).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(1, 1, row - 1, 8).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
ws.Columns().AdjustToContents();
ws.Column(1).Width = 20;
ws.Column(1).Style.Alignment.WrapText = true;
ws.Column(2).Width = 40;
ws.Column(3).Width = 40;
ws.Column(4).Width = 20;
ws.Column(5).Width = 10;
ws.Column(6).Width = 20;
ws.Column(7).Width = 30;
return(wb);
}
public XLWorkbook ExportExcelCreditInvoice(BaseReportModel model, List <StoreModels> lstStore, int cashType)
{
string sheetName = "Credit_Invoice_Report";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
// Set header report
CreateReportHeaderNew(ws, 5, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Credit Invoice Report"));
ws.Range(1, 1, 4, 5).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, 5).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
//Get business day
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any())
{
return(wb);
}
// Get data use business day
model.FromDate = _lstBusDayAllStore.Min(oo => oo.DateFrom);
model.ToDate = _lstBusDayAllStore.Max(oo => oo.DateTo);
List <CreditInvoiceReportModels> lstCreditModel = GetDataReport(model, cashType);
if (lstCreditModel == null || !lstCreditModel.Any())
{
return(wb);
}
ws.Cell("A5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date");
ws.Cell("B5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time");
ws.Cell("C5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Invoice No.");
ws.Cell("D5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Amount");
ws.Cell("E5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Remark");
//ws.Cell("F5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Amount");
//ws.Cell("G5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Reason");
//ws.Cell("H5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("User");
//set width column
ws.Column("A").Width = 150;
ws.Column("B").Width = 150;
ws.Column("C").Width = 250;
ws.Column("D").Width = 250;
ws.Column("E").Width = 180;
//ws.Column("F").Width = 20;
//ws.Column("G").Width = 35;
//ws.Column("H").Width = 15;
ws.Range("A" + 5 + ":E" + 5).Style.Font.SetBold(true);
ws.Range("A" + 5 + ":E" + 5).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9"));
ws.Range(5, 1, 5, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
int index = 7;
DateTime dFrom = DateTime.Now, dTo = DateTime.Now;
string storeId = string.Empty;
for (int i = 0; i < lstStore.Count(); i++)
{
storeId = lstStore[i].Id;
var lstBusDayByStore = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).ToList();
if (lstBusDayByStore != null && lstBusDayByStore.Any())
{
dFrom = lstBusDayByStore.Min(ss => ss.DateFrom);
dTo = lstBusDayByStore.Max(ss => ss.DateTo);
var lstCreditModelByStore = lstCreditModel.Where(ww => ww.StoreId == storeId && ww.CreatedDate >= dFrom && ww.CreatedDate <= dTo).ToList();
if (lstCreditModelByStore != null && lstCreditModelByStore.Any())
{
ws.Range("A" + (index - 1) + ":E" + (index - 1)).Merge().SetValue(lstStore[i].Name);
ws.Range("A" + (index - 1) + ":E" + (index - 1)).Style.Font.SetBold(true);
ws.Range("A" + (index - 1) + ":E" + (index - 1)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9"));
for (int j = 0; j < lstCreditModelByStore.Count; j++)
{
ws.Range("A" + (index) + ":C" + (index)).Merge().SetValue(lstCreditModelByStore[j].SupplierName);
ws.Range("A" + (index) + ":E" + (index)).Style.Font.SetBold(true);
ws.Range("A" + (index) + ":E" + (index)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9"));
ws.Cell("D" + (index)).Value = "'" + lstCreditModelByStore[j].TotalAmout.ToString("F");
ws.Cell("D" + (index)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
foreach (var itemSupplier in lstCreditModelByStore[j].Item)
{
ws.Cell("A" + (index + 1)).Value = "'" + itemSupplier.CreatedDate.ToString("MM/dd/yyyy");
ws.Cell("B" + (index + 1)).Value = "'" + itemSupplier.CreatedDate.ToString("hh:mm tt");
ws.Cell("C" + (index + 1)).Value = itemSupplier.InvoiceNo;
ws.Cell("D" + (index + 1)).Value = "'" + itemSupplier.Amount.ToString("F");
ws.Cell("E" + (index + 1)).Value = "'" + itemSupplier.Remark;
ws.Cell("E" + (index + 1)).Style.Alignment.WrapText = true;
ws.Range("A" + (index + 1) + ":C" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("D" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Cell("C" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
index++;
}
index++;
}
index++;
}
}
}//End loop store
//ws.Range("F5" + ":F" + index).Style.NumberFormat.Format = "#,##0.00";
//set Border
ws.Range("A5:E" + (index - 2)).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range("A5:E" + (index - 2)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Columns().AdjustToContents();
return(wb);
}
public XLWorkbook ExportExcelSummary(BaseReportModel model, List <StoreModels> lstStore)
{
string sheetName = "Time_Clock_Summary";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
var lstEmpChecked = model.ListEmployees.Where(m => m.Checked).ToList();
if (lstEmpChecked == null || !lstEmpChecked.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
List <string> lstUserID = lstEmpChecked.Select(m => m.Name).ToList();
model.ListStores = lstEmpChecked.Select(m => m.StoreId).ToList();
DateTimeHelper.GetDateTime(ref model);
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
// When data null
if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
var _lstBusDayIdAllStore = _lstBusDayAllStore.Select(ss => ss.Id).ToList();
var data = GetTimeClockSummary(model.ListStores, lstUserID, _lstBusDayIdAllStore, model.Mode);
// When data null
if (data == null || !data.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
//Table
//row 1: Table Header
CreateReportHeader(ws, 8, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
DateTime fromDate = model.FromDate;
DateTime toDate = model.ToDate;
int row = 4;
int startRow = row;
int startRow_Store = row;
int endRow_Store = row;
int startRow_User = row;
int endRow_User = row;
string storeId = string.Empty, storeName = string.Empty;
lstStore = lstStore.Where(ww => model.ListStores.Contains(ww.Id)).OrderBy(oo => oo.Name).ToList();
for (int i = 0; i < lstStore.Count; i++)
{
storeId = lstStore[i].Id;
storeName = lstStore[i].Name;
var _lstBusDayIdStore = _lstBusDayAllStore.Where(x => x.StoreId == storeId).Select(ss => ss.Id).ToList();
if (_lstBusDayIdStore != null && _lstBusDayIdStore.Any())
{
var dataStore = data.Where(x => x.StoreId == storeId && _lstBusDayIdStore.Contains(x.BusinessId)).OrderBy(oo => oo.DateTimeIn).ToList();
if (dataStore != null && dataStore.Any())
{
startRow_Store = row;
endRow_Store = row;
row++;
//Timer Header
ws.Cell("A" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Employee"));
ws.Cell("B" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date In"));
ws.Cell("C" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time In"));
ws.Cell("D" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date Out"));
ws.Cell("E" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Out"));
ws.Cell("F" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Hours Worked"));
ws.Cell("G" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Late(Minutes)"));
ws.Cell("H" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Early Leave(Minutes)"));
ws.Range("A" + row + ":H" + row).Style.Font.SetBold(true);
ws.Range("A" + row + ":H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
row++;
#region ======Get Employees
List <RPEmployeeItemModels> lstUser = lstEmpChecked.Where(ww => ww.StoreId == storeId).Select(m => new RPEmployeeItemModels
{
ID = m.ID,
Name = m.Name,
StoreId = m.StoreId
}).OrderBy(oo => oo.Name).ToList();
int frmDateOutMerge = 0, toDateOutMerge = 0;
List <DateOutInfo> lstDateOuts = new List <DateOutInfo>();
foreach (var employee in lstUser)
{
var dataEmp = dataStore.Where(x => x.UserId == employee.ID).GroupBy(gg => gg.CreatedDate.Date).OrderBy(oo => oo.Key).ToList();
if (dataEmp != null && dataEmp.Any())
{
startRow_User = row;
endRow_User = row;
#region ====Date
double _sumHw = 0, _sumLate = 0, _sumEarly = 0;
var _checkObj = new TimeClockReportModels();
foreach (var itmData in dataEmp)
{
int startRow_Date = row;
int endRow_Date = row;
DateTime _date = itmData.Key;
var lstdateDateGroup = itmData.GroupBy(gg => gg.DateTimeIn).OrderBy(oo => oo.Key).ToList();
lstDateOuts = new List <DateOutInfo>();
frmDateOutMerge = row;
toDateOutMerge = row;
//loop data in date
foreach (var itemGroup in lstdateDateGroup)
{
_checkObj = itemGroup.Where(ww => ww.DateTimeOut.Date != Commons.MinDate.Date).FirstOrDefault();
if (_checkObj == null)
{
_checkObj = itemGroup.FirstOrDefault();
}
//Time In
ws.Cell("C" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeIn.TimeOfDay));
ws.Cell("C" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
//check merge time out
if (_checkObj.DateTimeOut.Date != Commons.MinDate.Date)
{
var checkExist = lstDateOuts.Where(ww => ww.DateOut.Date == _checkObj.DateTimeOut.Date).FirstOrDefault();
if (checkExist == null)
{
var info = new DateOutInfo();
info.DateOut = _checkObj.DateTimeOut;
info.FromRow = frmDateOutMerge;
info.ToRow = toDateOutMerge;
lstDateOuts.Add(info);
frmDateOutMerge++;
toDateOutMerge++;
}
else
{
checkExist.ToRow++;
}
//E time out
ws.Cell("E" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeOut.TimeOfDay));
ws.Cell("E" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
else
{
frmDateOutMerge++;
toDateOutMerge++;
}
var _hoursWorked = Math.Round(_checkObj.HoursWork, 2);
// F hours work
ws.Cell("F" + row).SetValue(_hoursWorked);
ws.Cell("F" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("F" + row).Style.NumberFormat.Format = "#,##0.00";
_sumHw += _hoursWorked;
ws.Cell("G" + row).SetValue(Math.Round(_checkObj.Late, 2));
ws.Cell("G" + row).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("G" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
_sumLate += Math.Round(_checkObj.Late, 2);
ws.Cell("H" + row).SetValue(Math.Round(_checkObj.Early, 2));
ws.Cell("H" + row).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
_sumEarly += Math.Round(_checkObj.Early, 2);
//========================================================
row++;
endRow_User++;
endRow_Date++;
//Date Out
foreach (var item in lstDateOuts)
{
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Merge().SetValue(item.DateOut.ToString("MM/dd/yyyy"));
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Font.SetBold(true);
}
//==============================================================================
//row++;
//endRow_User++;
//endRow_Date++;
}//end dateDate.Count > 0
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Merge().SetValue(_date.ToString("MM/dd/yyyy"));
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Font.SetBold(true);
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
}
#endregion
//===> Total
row++;
endRow_User++;
ws.Cell("B" + (row - 1)).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total"));
ws.Cell("B" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
ws.Range("B" + (row - 1) + ":E" + (row - 1)).Merge();
ws.Cell("F" + (row - 1)).SetValue(_sumHw);
ws.Cell("F" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("F" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("G" + (row - 1)).SetValue(_sumLate);
ws.Cell("G" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("G" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("H" + (row - 1)).SetValue(_sumEarly);
ws.Cell("H" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("H" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Font.SetBold(true);
ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9"));
//========================
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Merge().SetValue(employee.Name);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Font.SetBold(true);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
}
}
#endregion
//Get StoreName
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Merge().SetValue(storeName);
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Font.SetBold(true);
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorStore));
}
}
}
//Set Border
ws.Range("A1:H" + (row - 1)).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range("A1:H" + (row - 1)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Columns().AdjustToContents();
ws.Column(1).Width = 30;
ws.Column(2).Width = 15;
ws.Column(4).Width = 15;
return(wb);
}
