// Get AreaManager AssignStore Information
public ArrayList GetAreaMngAssignStore(int userId)
{
ArrayList assigneStore = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select su.Id,s.StoreName,s.Id from dbo.StoreUser su, dbo.Store s where s.Id = su.StoreId and su.UserId = " + SQLUtility.getInteger(userId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int uId = ValidationUtility.ToInteger(reader[0].ToString());
string storeName = reader[1].ToString();
int sId = ValidationUtility.ToInteger(reader[2].ToString());
StoreDTO dto = new StoreDTO { Id = uId, StoreName = storeName, StoreId = sId };
assigneStore.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetAreaMngAssignStore Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return assigneStore;
}
// Get Record sales ammount from local database
public RecordSalesDTO GetRecordSaleByStoreID(int storeId, string weekOfDay)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
RecordSalesDTO dto = null;
try
{
string query = " select SalesAmount,BusinessDate from dbo.RecordSales where StoreId = " + SQLUtility.getInteger(storeId) + " and DayOfWeek = " + SQLUtility.getString(weekOfDay) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new RecordSalesDTO();
dto.SalesAmount = ValidationUtility.ToDouble(reader["SalesAmount"].ToString());
dto.BusinessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetRecordSaleByStoreID method ", ex);
}
finally
{
db.CloseConnection(con);
}
return dto;
}
// Method is use for Get perday total sales amount day
public bool IsCurrentDateSaleIsExsist(int storeId, DateTime currentDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
bool isCurrentDateSale = false;
try
{
string query = " select * from dbo.PerdaySales where BusinessDate = '" + currentDate.ToString("yyyy/MM/dd") + "' and StoreId = " + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
isCurrentDateSale = true;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsCurrentDateSaleIsExsist method ", ex);
}
finally
{
db.CloseConnection(con);
}
return isCurrentDateSale;
}
public ArrayList GetPerdaySales(int userId, string connectionString, DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
DateTime weekStartDate = GetActualWeekStartDate(businessDate);
DateTime secondDate = weekStartDate.AddDays(1);
DateTime thirdDate = weekStartDate.AddDays(2);
DateTime fourDate = weekStartDate.AddDays(3);
DateTime fiveDate = weekStartDate.AddDays(4);
DateTime sixDate = weekStartDate.AddDays(5);
DateTime sevenDate = weekStartDate.AddDays(6);
ArrayList DateList = new ArrayList();
DateList.Add(weekStartDate);
DateList.Add(secondDate);
DateList.Add(thirdDate);
DateList.Add(fourDate);
DateList.Add(fiveDate);
DateList.Add(sixDate);
DateList.Add(sevenDate);
try
{
string query = null;
con = db.OpenConnection();
foreach (DateTime date in DateList)
{
query = " select * from dbo.PerdaySales where StoreId in (select StoreId from dbo.StoreUser where UserId =" + SQLUtility.getInteger(userId) + ") and BusinessDate='" + date.ToString("yyyy/MM/dd") + "'";
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
int id = ValidationUtility.ToInteger(reader[0].ToString());
int sid = ValidationUtility.ToInteger(reader[1].ToString());
int openingInformationId = ValidationUtility.ToInteger(reader[2].ToString());
double salesAmount = ValidationUtility.ToDouble(reader[4].ToString());
string day = reader[5].ToString();
if (openingInformationId == 0 && !ValidationUtility.IsEqual(day, DateTime.Now.DayOfWeek.ToString()))
{
PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = "N/A", WeekOfDay = day };
list.Add(dto);
}
else
{
PerdaySalesDTO dto = new PerdaySalesDTO { Id = id, StoreId = sid, OpeningInformationId = openingInformationId, BusinessDate = date, SalesAmountString = salesAmount.ToString(), WeekOfDay = day };
list.Add(dto);
}
}
else
{
reader.Close();
comm.Dispose();
PerdaySalesDTO dto = null;
if (date<=DateTime.Now)
{
int id = GetCurrentDateOpeningInfoId(date, connectionString);
if (id == 0)
{
dto = new PerdaySalesDTO { SalesAmountString = "N/A" };
list.Add(dto);
}
else
{
dto = new PerdaySalesDTO { SalesAmountString = "0" };
list.Add(dto);
}
}
else
{
dto = new PerdaySalesDTO { SalesAmountString = "0" };
list.Add(dto);
}
}
reader.Close();
comm.Dispose();
}
}
catch (Exception ex)
{
log.Error("Exception in GetPerdaySales method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
public ArrayList GetMaintenanceList(int userId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
string query = "";
if (userId == 0)
{
//query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
// + " where sm.MaintenanceCategoryId = mc.Id order by sm.Id desc ";
//query = "select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , s.Id,s.StoreName "
// + " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc ,dbo.StoreUser su, dbo.Store s "
// + " where sm.MaintenanceCategoryId = mc.Id and su.Id = sm.StoreId and s.Id = su.StoreId order by sm.Id desc ";
query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , sm.StoreId from "
+ " dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc where "
+ " sm.MaintenanceCategoryId = mc.Id order by sm.Id desc ";
}
else
{
//query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
// + " where sm.MaintenanceCategoryId = mc.Id and sm.StoreId in (select Id from dbo.StoreUser where UserId=" + SQLUtility.getInteger(userId) + " ) order by sm.Id desc ";
//query = "select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , s.Id,s.StoreName "
// + " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc ,dbo.StoreUser su, dbo.Store s "
// + " where sm.MaintenanceCategoryId = mc.Id and su.Id = sm.StoreId and s.Id = su.StoreId and "
// + " sm.StoreId in (select su.Id from dbo.StoreUser where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc ";
query = " select sm.Id,sm.Description,sm.status,sm.DateCreated,sm.DateResolved, mc.Id,mc.ItemName,mc.Rank , sm.StoreId "
+ " from dbo.StoreMaintainance sm , dbo.MaintenanceCategory mc "
+ " where sm.MaintenanceCategoryId = mc.Id and "
+ " sm.StoreId in (select su.StoreId from dbo.StoreUser su where su.UserId = " + SQLUtility.getInteger(userId) + ") order by sm.Id desc ";
}
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
DateTime resolvDate = new DateTime();
int id = ValidationUtility.ToInteger(reader[0].ToString());
string des = reader[1].ToString();
string status = reader[2].ToString();
DateTime creatDate = ValidationUtility.ToDate(reader[3].ToString());
int cId = ValidationUtility.ToInteger(reader[5].ToString());
string item = reader[6].ToString();
int rank = ValidationUtility.ToInteger(reader[7].ToString());
int storeId = ValidationUtility.ToInteger(reader[8].ToString());
//string storeName = reader[9].ToString();
MaintenanceDTO dto = null;
if (ValidationUtility.IsEqual("resolved", status))
{
resolvDate = ValidationUtility.ToDate(reader[4].ToString());
dto = new MaintenanceDTO
{
Id = id,
Description = des,
Status = status,
CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
ResolveDateTimeInString = resolvDate.ToString("MMM/dd/yyyy"),
CategoryId = cId,
ItemName = item,
Rank = rank,
StoreId = storeId,
//StoreName = storeName
};
}
else
{
dto = new MaintenanceDTO
{
Id = id,
Description = des,
Status = status,
CreateDateTimeInString = creatDate.ToString("MMM/dd/yyyy"),
ResolveDateTimeInString = "",
CategoryId = cId,
ItemName = item,
Rank = rank,
StoreId = storeId,
//StoreName = storeName
};
}
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetMaintenanceList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
public int GetCategoryId(string itemName)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
int categoryId = 0;
try
{
string query = "select Id from dbo.MaintenanceCategory where ItemName = " + SQLUtility.getString(itemName) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
categoryId = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetCategoryId Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return categoryId;
}
public double GetMonthlyEmpBonus(DateTime monthStartDate, DateTime monthEndDate)
{
double totalBonus = 0;
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
string query = "select SUM(BonusAmount) from dbo.EmployeeBonus where BusinessDate>='" + SQLUtility.FormateDateYYYYMMDD(monthStartDate) + "' and BusinessDate<='" + SQLUtility.FormateDateYYYYMMDD(monthEndDate) + "' ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
totalBonus = ValidationUtility.ToDouble(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetManagerBonus", ex);
}
finally
{
db.CloseConnection(conn);
}
return totalBonus;
}
//For General Manager
public ArrayList GetGeneralManagerAssignAreaManagerList()
{
ArrayList amId = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
string query = "select AreaManagerId from dbo.AssignAreaManagerInfo";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
amId.Add(ValidationUtility.ToInteger(reader[0].ToString()));
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetManagerBonus", ex);
}
finally
{
db.CloseConnection(conn);
}
return amId;
}
// This method is use for check employee is exsist in our local db or it new employee
public EmployeeInfoDTO GetEmployeeInfo(int storeId, int empId)
{
EmployeeInfoDTO dto = null;
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
string query = "select * from dbo.EmployeeInfo where EmpId = " + SQLUtility.getInteger(empId) + " and storeId = " + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new EmployeeInfoDTO();
dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return dto;
}
public List<EmployeeClockingDTO> GetClockingList(int trakId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();
try
{
string query = "select id,ClockingTime,ClockFunctionTypeId,MinutesWorked from EmployeeClocking where EmployeeTrackerId=" + SQLUtility.getInteger(trakId) + " ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
// DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());
int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());
int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());
EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };
empClokingList.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empClokingList;
}
//Check data exit or not into the local database
public bool IsWeeklyPaperExistIntoLocalDb(int storeId, DateTime weekStartDay)
{
bool isExist = false;
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
conn = db.OpenConnection();
string query = "select * from dbo.WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeId) + " and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
isExist = true;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception into IsWeeklyPaperExistIntoLocalDb method", ex);
}
finally
{
db.CloseConnection(conn);
}
return isExist;
}
public bool IsAllWeeklyPaperYearWeekRecordExsist(ArrayList storeList, DateTime date)
{
bool isAllWeekExsist = false;
StoreDTO dto = (StoreDTO)storeList[0];
date = date.AddYears(-1);
int totalWeekOfYear = ValidationUtility.YearWeekCount(date);
DateTime yearStartDate = ValidationUtility.YearWeekStartDate(date);
DateTime yearEndDate = ValidationUtility.GetActualWeekStartDate(new DateTime(date.Year, 12, 31));
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
//string query = " select COUNT(*) from dbo.GoalsYTD gy where gy.Year=" + SQLUtility.getInteger(yearStartDate.Year) + " "
// + " and gy.WeekStartDate>='" + SQLUtility.FormateDateYYYYMMDD(yearStartDate) + "' and WeekEndDate<='" + SQLUtility.FormateDateYYYYMMDD(yearEndDate.AddDays(6)) + "' "
// + " and StoreId=" + SQLUtility.getInteger(dto.Id) + "";
string query = "select count(*) from dbo.WeeklyPaperWork "
+ " where WeekStartDate>='" + SQLUtility.FormateDateYYYYMMDD(yearStartDate) + "' and WeekStartDate<='" + SQLUtility.FormateDateYYYYMMDD(yearEndDate) + "' "
+ " and StoreId = " + SQLUtility.getInteger(dto.Id) + "";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
int weekCount = 0;
if (reader.Read())
{
weekCount = ValidationUtility.ToInteger(reader[0].ToString());
if (totalWeekOfYear == weekCount)
{
isAllWeekExsist = true;
}
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsAllYearWeekRecordExsist Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return isAllWeekExsist;
}
/*
*Get WeeklyPaper Work Info Id based
*/
public WeeklyPaperworkDTO GetWeeklyPaperWorkOfWeek(int id)
{
WeeklyPaperworkDTO dto = null;
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
conn = db.OpenConnection();
//string query = " select SUM(CostPercent) as CostPercent ,SUM(LaborCostPercent) as LaborCostPercent from dbo.WeeklyPaperWork "
// + " where WeekStartDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDay.AddDays(6)) + "'";
String query = " select StoreId,NetSales,StoreTransfer,PFG1,PFG2,Coke,LaborCost,WeekStartDate from dbo.WeeklyPaperWork where Id=" + SQLUtility.getInteger(id) + " ";
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new WeeklyPaperworkDTO();
dto.StoreId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
dto.WeekStartDate = ValidationUtility.ToDate(reader["WeekStartDate"].ToString());
double totalPFG = pfg1 + pfg2;
double totalCost = storeTransfer + totalPFG + coke;
double costPercent = ValidationUtility.IsNan(totalCost / netSales);
double laborCostPercent = ValidationUtility.IsNan(laborCost / netSales);
dto.CostPercent = costPercent;
dto.LaborCostPercent = laborCostPercent;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception into IsWeeklyPaperExistIntoLocalDb method", ex);
}
finally
{
db.CloseConnection(conn);
}
return dto;
}
//Get weekly paper list from local database list
public ArrayList GetWeeklyPaperListFromLocalDB(ArrayList storeList, DateTime weekStartDay)
{
ArrayList list = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
foreach (StoreDTO storeDTO in storeList)
{
if (!IsWeeklyPaperExistIntoLocalDb(storeDTO.Id, weekStartDay))
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(weekStartDay, oneStoreList, true);
}
DateTime currentWeekStartDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now);
DateTime lastWeekStartDate = ValidationUtility.GetActualWeekStartDate(currentWeekStartDate.AddDays(-1));
if (weekStartDay.Date >= lastWeekStartDate.Date)
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(lastWeekStartDate, oneStoreList, false);
}
if (weekStartDay.Date.Equals(currentWeekStartDate.Date))
{
ArrayList oneStoreList = new ArrayList();
oneStoreList.Add(storeDTO);
AddWeeklyPaperWork(currentWeekStartDate, oneStoreList, false);
}
try
{
// string query = " select * from dbo.WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
string query = "select s.StoreNumber, wpw.* from dbo.WeeklyPaperWork wpw,dbo.Store s where wpw.StoreId=s.Id and wpw.StoreId=" + SQLUtility.getInteger(storeDTO.Id) + " and wpw.WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
double netSales = ValidationUtility.ToDouble(reader["NetSales"].ToString());
double giftCardSales = ValidationUtility.ToDouble(reader["GiftCardSales"].ToString());
double ar = ValidationUtility.ToDouble(reader["AR"].ToString());
double paidOuts = ValidationUtility.ToDouble(reader["PaidOuts"].ToString());
double pettyExpense = ValidationUtility.ToDouble(reader["PettyExpense"].ToString());
// double total = ValidationUtility.ToDouble(reader["Total"].ToString());
double total = ar + paidOuts + pettyExpense;
double storeTransfer = ValidationUtility.ToDouble(reader["StoreTransfer"].ToString());
double pfg1 = ValidationUtility.ToDouble(reader["PFG1"].ToString());
double pfg2 = ValidationUtility.ToDouble(reader["PFG2"].ToString());
// double totalPFG = ValidationUtility.ToDouble(reader["TotalPFG"].ToString());
double totalPFG = pfg1 + pfg2;
double coke = ValidationUtility.ToDouble(reader["Coke"].ToString());
// double totalCost = ValidationUtility.ToDouble(reader["TotalCost"].ToString());
double totalCost = storeTransfer + totalPFG + coke;
// double costPercent = ValidationUtility.ToDouble(reader["CostPercent"].ToString());
double costPercent = totalCost / netSales;
costPercent = ValidationUtility.IsNan(costPercent);
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
// double laborCostPercent = ValidationUtility.ToDouble(reader["LaborCostPercent"].ToString());
double laborCostPercent = laborCost / netSales;
laborCostPercent = ValidationUtility.IsNan(laborCostPercent);
// double royalty = ValidationUtility.ToDouble(reader["Royalty"].ToString());
double royalty = netSales * 0.08;
// double fAF = ValidationUtility.ToDouble(reader["FAF"].ToString());
double fAF = netSales * 0.045;
// double costAndLaborCostPercent = ValidationUtility.ToDouble(reader["CostAndLaborCostPercent"].ToString());
double costAndLaborCostPercent = laborCostPercent + costPercent;
double adjTax = ValidationUtility.ToDouble(reader["AdjTax"].ToString());
double gcRedeem = ValidationUtility.ToDouble(reader["GCRedeem"].ToString());
// double gcDifference = ValidationUtility.ToDouble(reader["GCDifference"].ToString());
double gcDifference = gcRedeem - giftCardSales;
double taxPercent = ValidationUtility.ToDouble(reader["TaxPercent"].ToString());
// double actualSalesTaxper = ValidationUtility.ToDouble(reader["ActualSalesTaxper"].ToString());
double actualSalesTaxper = netSales * taxPercent;
// double differenceOfSalesTax = ValidationUtility.ToDouble(reader["DifferenceOfSalesTax"].ToString());
double differenceOfSalesTax = actualSalesTaxper - adjTax;
double nonTaxableSale = ValidationUtility.ToDouble(reader["NonTaxableSale"].ToString());
double weekStartDate = ValidationUtility.ToDouble(reader["WeekStartDate"].ToString());
WeeklyPaperworkDTO weeklyPaperworkDTO = new WeeklyPaperworkDTO
{
StoreNumber = storeNumber,
Id = id,
StoreId = sId,
NetSales = netSales,
GiftCardSales = giftCardSales,
Ar = ar,
PaidOuts = paidOuts,
PettyExpense = pettyExpense,
Total = total,
StoreTransfer = storeTransfer,
Pfg1 = pfg1,
Pfg2 = pfg2,
TotalPFG = totalPFG,
Coke = coke,
TotalCost = totalCost,
CostPercent = costPercent,
LaborCost = laborCost,
LaborCostPercent = laborCostPercent,
Royalty = royalty,
Faf = fAF,
CostAndLaborCostPercent = costAndLaborCostPercent,
AdjTax = adjTax,
GcRedeem = gcRedeem,
GcDifference = gcDifference,
WeekStartDate = weekStartDay,
TaxPercent = taxPercent,
ActualSalesTaxper = actualSalesTaxper,
DifferenceOfSalesTax = differenceOfSalesTax,
NonTaxableSale = nonTaxableSale
};
list.Add(weeklyPaperworkDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetWeeklyPaperListFromLocalDB Method ", ex);
}
finally
{
db.CloseConnection(conn);
}
}
return list;
}
public List<EmployeeClockingDTO> GetClockingListPerDay(int empInfoId,DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();
try
{
string query = " select ec.id,ec.ClockingTime,ec.ClockFunctionTypeId,ec.MinutesWorked from dbo.EmployeeClocking ec where "
+ " ec.EmployeeTrackerId = (select Id from dbo.EmployeeTracker et where et.BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + ")";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
// DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());
int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());
int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());
EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };
empClokingList.Add(dto);
}
if (empClokingList.Count == 0 && businessDate.Date < DateTime.Now.Date)
{
EmployeeClockingDTO dto = new EmployeeClockingDTO { ClockingTimeToString ="N/A" };
empClokingList.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empClokingList;
}
// Methos is use for check record is exsist for this week
public bool IsWeekEmployeeTrakingExsist(int empId, int storeId, DateTime weekStartDate)
{
DataBaseUtility db = new DataBaseUtility();
bool isRecordExsist = false;
SqlConnection con = null;
try
{
string query = "select * from dbo.EmployeeTracker et where EmployeeInfoId = (select Id from dbo.EmployeeInfo where EmpId=" + SQLUtility.getInteger(empId) + " and StoreId=" + SQLUtility.getInteger(storeId) + ") "
+ " and BusinessDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "'";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
isRecordExsist = true;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTracking Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return isRecordExsist;
}
public int CustomerComplaintCount(int storeId, DateTime startDate, DateTime endDate)
{
int count = 0;
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = " select COUNT(StoreId) from dbo.Complaints where StoreId=" + SQLUtility.getInteger(storeId) + " "
+ " and ComplaintDate between '" + SQLUtility.FormateDateYYYYMMDD(startDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(endDate) + "'";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
count = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetStoredId Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return count;
}
// This method is use for get employee list from local database
public ArrayList GetEmployeeList(int storeId)
{
ArrayList list = new ArrayList();
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
//string query = "select * from dbo.EmployeeInfo where storeId = " + SQLUtility.getInteger(storeId) + " ";
string query = "select ei.*,s.StoreNumber from dbo.EmployeeInfo ei,dbo.Store s where ei.StoreId=s.Id and ei.StoreId =" + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int empId = ValidationUtility.ToInteger(reader["EmpId"].ToString());
string firstName = reader["EmpFirstName"].ToString();
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
EmployeeInfoDTO dto = new EmployeeInfoDTO { Id = id, StoreId = sId, StoreNumber=storeNumber, EmpId = empId, EmpFirstName = firstName };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
//Manager
public ArrayList GetManagerAssignStoreList()
{
ArrayList storeIdList = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
try
{
string query = "select su.StoreId from dbo.StoreUser su,dbo.Users u where su.UserId=u.Id and u.RoleId=2";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
storeIdList.Add(ValidationUtility.ToInteger(reader[0].ToString()));
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetManagerBonus", ex);
}
finally
{
db.CloseConnection(conn);
}
return storeIdList;
}
// This method is use for get employee id from EmployeeTracker table
public EmployeeTrackerDTO GetEmployeeTracking(int empId, int storeId, DateTime businessDate)
{
EmployeeTrackerDTO dto = null;
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = " select * from dbo.EmployeeTracker et where EmployeeInfoId = (select Id from dbo.EmployeeInfo where EmpId=" + SQLUtility.getInteger(empId) + " and StoreId=" + SQLUtility.getInteger(storeId) + " ) "
+ " and BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new EmployeeTrackerDTO();
dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTracking Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return dto;
}
public ManagerBonusDTO GetActualBounus(int storeId, DateTime startDate, DateTime endDate)
{
int totalWeekCount = ValidationUtility.GetNumOfWeekInMonth(startDate);
if (totalWeekCount == 0)
{
totalWeekCount = 1;
}
ManagerBonusDTO managerBonusDTO = null;
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select * from dbo.ManagerBonus where StoreId = " + SQLUtility.getInteger(storeId) + " and IsZeroBasis=" + SQLUtility.getString(false.ToString()) + " "
+ " and FirstDateOfMonth='" + SQLUtility.FormateDateYYYYMMDD(startDate) + "' and LastDateOfMonth = '" + SQLUtility.FormateDateYYYYMMDD(endDate) + "' ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
double foodCost = ValidationUtility.ToDouble(reader["FoodCost"].ToString());
double laborCost = ValidationUtility.ToDouble(reader["LaborCost"].ToString());
double foodLaborCost = ValidationUtility.ToDouble(reader["FoodLaborCost"].ToString());
double salesIncrease = ValidationUtility.ToDouble(reader["SalesIncrease"].ToString());
double customerIndex = ValidationUtility.ToDouble(reader["CustomerIndex"].ToString());
double customerComplaint = ValidationUtility.ToDouble(reader["CustomerComplaint"].ToString());
double catering = ValidationUtility.ToDouble(reader["Catering"].ToString());
double subInspection = ValidationUtility.ToDouble(reader["SubInspection"].ToString());
managerBonusDTO = new ManagerBonusDTO
{
Id = id,
StoreId = sId,
FoodCost = foodCost / totalWeekCount,
LaborCost = laborCost / totalWeekCount,
FoodLaborCost = foodLaborCost / totalWeekCount,
SalesIncrease = salesIncrease / totalWeekCount,
CustomerIndex = customerIndex / totalWeekCount,
CustomerComplaint = customerComplaint,
Catering = catering,
SubInspection = subInspection
};
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetActualBounus Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return managerBonusDTO;
}
//Get Employee Tracker id
public int GetEmpTrackerId(int empInfoId, DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
int id = 0;
SqlConnection conn = null;
try
{
conn = db.OpenConnection();
string query = "select id from dbo.EmployeeTracker where BusinessDate='" + SQLUtility.FormateDateYYYYMMDDWtithTime(businessDate) + "' and EmployeeInfoId=" + SQLUtility.getInteger(empInfoId) + "";
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
id = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmpTrackerId Method ", ex);
}
finally
{
db.CloseConnection(conn);
}
return id;
}
public ArrayList GetList()
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
string query = " select Id , ItemName,Rank from dbo.MaintenanceCategory ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader[0].ToString());
string name = reader[1].ToString();
int rank = ValidationUtility.ToInteger(reader[2].ToString());
MaintenanceDTO dto = new MaintenanceDTO { Id = id, ItemName = name, Rank = rank };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error(" Exception in GetList Method ",ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
// Get All StoreList from local database
public ArrayList GetStoreList()
{
ArrayList collection = new ArrayList();
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
try
{
string query = "select Id,StoreNumber,StoreName,ConnectionString from dbo.Store ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int sNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
string strorName = reader["StoreName"].ToString();
string connectionString = reader["ConnectionString"].ToString();
StoreDTO dto = new StoreDTO { Id = id, StoreNumber = sNumber, StoreName = strorName, ConnectionString = connectionString };
collection.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetStoreList Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return collection;
}
// Method is use for get Store ID of Current Manager
public int GetManagetAssignStoredId(int userId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
int storId = 0;
try
{
string query = " select StoreId from StoreUser where UserId = " + SQLUtility.getInteger(userId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
storId = ValidationUtility.ToInteger(reader[0].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetManagetAssignStoredId method ", ex);
}
finally
{
db.CloseConnection(con);
}
return storId;
}
//Get TotalField Value
public List<EmployeeTotalWorkDTO> GetTotalList(int empInfoId, DateTime weekStartDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeTotalWorkDTO> empTrakList = new List<EmployeeTotalWorkDTO>();
try
{
string query = " select et.Id,et.ScheduleIn,et.ScheduleOut, et.BusinessDate,et.TotalTimeWorked from dbo.EmployeeTracker et where "
+ " et.BusinessDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + " ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
TimeSpan totalSheduleTimeDifference = new TimeSpan(0, 0, 0);
TimeSpan totalClockingTime = new TimeSpan(0, 0, 0);
TimeSpan totalDifferenceTime = new TimeSpan(0, 0, 0);
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime scheduleIn = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
DateTime scheduleOut = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
TimeSpan totalTime = reader.GetTimeSpan(4);
totalClockingTime = totalClockingTime + totalTime;
// var v = (scheduleOut - scheduleOut).Subtract;
TimeSpan sheduleTimeDifference = scheduleOut.Subtract(scheduleIn);
totalSheduleTimeDifference = totalSheduleTimeDifference + sheduleTimeDifference;
TimeSpan difference = sheduleTimeDifference - totalTime;
totalDifferenceTime = totalDifferenceTime + difference;
}
EmployeeTotalWorkDTO trakerDTO = new EmployeeTotalWorkDTO { TotalScheduleTime = totalSheduleTimeDifference.ToString(), TotalClockingTime = totalClockingTime.ToString(), TotalWorkTime = totalClockingTime.ToString(), TotalDifference = totalDifferenceTime.ToString() };
empTrakList.Add(trakerDTO);
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empTrakList;
}
public ArrayList GetRecodSaleList(int userId, DateTime selectedDate)
{
DateTime dt = Convert.ToDateTime(selectedDate);
// DateTime weekStartDate = GetActualWeekStartDate(DateTime.Now);
DateTime weekStartDate = GetActualWeekStartDate(dt);
weekStartDate = weekStartDate.AddDays(-1);
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
try
{
string query = " select * from dbo.RecordSales where StoreId in (select StoreId from dbo.StoreUser where UserId = " + SQLUtility.getInteger(userId) + ")";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
weekStartDate = weekStartDate.AddDays(1);
string weekDate = weekStartDate.ToString("MM/dd/yyyy");
RecordSalesDTO dto = null;
int id = ValidationUtility.ToInteger(reader[0].ToString());
int sid = ValidationUtility.ToInteger(reader[1].ToString());
string day = reader[2].ToString();
DateTime businessDate = ValidationUtility.ToDate(reader[3].ToString());
double salesAmount = ValidationUtility.ToDouble(reader[4].ToString());
//if (!ValidationUtility.IsEqual("01-01-0001 00:00:00", BusinessDate.ToString()) || !ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString()))
if (ValidationUtility.FormateDateYYYYMMDD(ValidationUtility.GetDefaultDate()).Equals(ValidationUtility.FormateDateYYYYMMDD(businessDate)))
{
dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
//dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
}
//else if (ValidationUtility.IsEqual("1/1/0001 12:00:00 AM", BusinessDate.ToString()))
//{
// dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
// // dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = BusinessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
//}
else
{
dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = businessDate.ToString("MM/dd/yyyy"), SalesAmountStringType = salesAmount.ToString() };
// dto = new RecordSalesDTO { Id = id, StoreId = sid, DayOfWeek = day.Substring(0, 3).ToUpper(), WeekDayDate = weekDate, BusinessDateStringType = "N/A", SalesAmountStringType = "N/A" };
}
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetRecodSaleList method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
//// Get Store List
public List<EmployeeTrackerDTO> GetTrackingList(int empInfoId, DateTime weekStartDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeTrackerDTO> empTrakList = new List<EmployeeTrackerDTO>();
try
{
string query = " select et.Id,et.ScheduleIn,et.ScheduleOut, et.BusinessDate,et.TotalTimeWorked from dbo.EmployeeTracker et where "
+ " et.BusinessDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + " ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int isSchedulDay = 0;
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime scheduleIn = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
DateTime scheduleOut = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
DateTime businessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString());
TimeSpan totalTime = reader.GetTimeSpan(4);
// var v = (scheduleOut - scheduleOut).Subtract;
TimeSpan sheduleTimeDifference = scheduleOut.Subtract(scheduleIn);
TimeSpan difference = sheduleTimeDifference - totalTime;
if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date))
{
isSchedulDay = 1;
}
if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date) && businessDate.Date < DateTime.Now.Date)
{
isSchedulDay = 2;
}
EmployeeTrackerDTO trakerDTO = new EmployeeTrackerDTO { Id = id, ScheduleInToString = scheduleIn.ToString("hh:mm tt"), ScheduleOutToString = scheduleOut.ToString("hh:mm tt"), IsDayscheduled = isSchedulDay, BusinessDateToString = businessDate.ToString("MM/dd/yyyy"), TotalTimeWorked = totalTime.ToString(), WorkDifference = difference.ToString(), BusinessDate = businessDate, ScheduleTimeDifference = sheduleTimeDifference.ToString() };
empTrakList.Add(trakerDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empTrakList;
}
public ArrayList GetBonusEmpName(int userId, string connectionString, DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection con = null;
ArrayList list = new ArrayList();
DateTime weekStartDate = GetActualWeekStartDate(businessDate);
DateTime secondDate = weekStartDate.AddDays(1);
DateTime thirdDate = weekStartDate.AddDays(2);
DateTime fourDate = weekStartDate.AddDays(3);
DateTime fiveDate = weekStartDate.AddDays(4);
DateTime sixDate = weekStartDate.AddDays(5);
DateTime sevenDate = weekStartDate.AddDays(6);
ArrayList DateList = new ArrayList();
DateList.Add(weekStartDate);
DateList.Add(secondDate);
DateList.Add(thirdDate);
DateList.Add(fourDate);
DateList.Add(fiveDate);
DateList.Add(sixDate);
DateList.Add(sevenDate);
try
{
string query = null;
con = db.OpenConnection();
foreach (DateTime date in DateList)
{
query = " select EmployeeId, FirstName,LastName,BusinessDate from dbo.EmployeeBonus where StoreId in (select StoreId from dbo.StoreUser where UserId =" + SQLUtility.getInteger(userId) + ") and BusinessDate='" + date.ToString("yyyy/MM/dd") + "'";
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int emoId = ValidationUtility.ToInteger(reader[0].ToString());
string firstName = reader[1].ToString();
string lastName = reader[2].ToString();
EmpBonusDTO dto = new EmpBonusDTO { EmployeeId = emoId, FirstName = firstName, LastName = lastName, BusinessDateStringType = date.ToString("MM/dd/yyyy") };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
}
catch (Exception ex)
{
log.Error("Exception in GetPerdaySales method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
// This method is use for check employee is exsist in our local db or it new employee
public bool IsEmployeExsist(EmployeeInfoDTO employeeInfoDTO, int storeId)
{
bool isEmployeeExsist = false;
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
string query = "select * from dbo.EmployeeInfo where EmpId = " + SQLUtility.getInteger(employeeInfoDTO.EmpId) + " and storeId = " + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
isEmployeeExsist = true;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return isEmployeeExsist;
}