public void DeleteFoodItem(int id)
{
DataBaseUtility db = new DataBaseUtility();
string query = "delete from dbo.Orders where ItemId=" + SQLUtility.getInteger(id) + "";
db.ExecuteUpdate(query);
query = " delete from dbo.FoodItem where Id=" + SQLUtility.getInteger(id) + "";
db.ExecuteUpdate(query);
}
//Method use for General Manager
public void AddAssignAreaManager(int gmId, int amId)
{
DataBaseUtility db = new DataBaseUtility();
if (IsAreaManagerExist(amId))
{
string deleteQuery = "delete from dbo.AssignAreaManagerInfo where AreaManagerId=" + SQLUtility.getInteger(amId) + "";
db.ExecuteUpdate(deleteQuery);
}
string query = "INSERT INTO [dbo].[AssignAreaManagerInfo] ([GeneralManagerId] ,[AreaManagerId] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
+ " VALUES (" + SQLUtility.getInteger(gmId) + "," + SQLUtility.getInteger(amId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
// Add Food Order this order add by only manager
public void AddFoodOrder(FoodOrderDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[FoodOrder]([StoreId],[OrderDateTime],[CreatedDateTime],[LastUpdateDateTime])VALUES ( "
+ " " + SQLUtility.getInteger(dto.StoreId) + " , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.FoodOrderDate) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' ,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) ";
db.ExecuteUpdate(query);
}
public void Add(string name, string role, string emailId)
{
try
{
string password = "";
if (name.Length <= 4)
{
password = ValidationUtility.CreatePassword(name.Length + 2);
}
else
{
password = ValidationUtility.CreatePassword(name.Length);
}
int roleId = ValidationUtility.ToInteger(role);
DataBaseUtility db = new DataBaseUtility();
string query = " insert into Users ([UserId],[Password],[RoleId],[EmailId],[CreatedDateTime],[LastUpdateDateTime]) "
+ " values(" + SQLUtility.getString(name) + "," + SQLUtility.getString(password) + ", " + SQLUtility.getInteger(roleId) + ", " + SQLUtility.getString(emailId) + " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in Add Method ", ex);
}
}
//Add Employee Clocking Information
public void AddEmpClockingInfo(int empTrackerId, EmployeeClockingDTO employeeClockingDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[EmployeeClocking]([EmployeeTrackerId],[ClockFunctionTypeId],[ClockingTime],[MinutesWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(empTrackerId) + "," + SQLUtility.getInteger(employeeClockingDTO.ClockFunctionTypeId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeClockingDTO.ClockingTime) + "', "
+ " " + SQLUtility.getInteger(employeeClockingDTO.MinutesWorked) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
public void AddActual(ManagerBonusDTO managerBonusDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[LastDateOFMonth] ,[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint] ,[Catering] ,[SubInspection] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
+ " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "',0,0,0,0,0,0,0,0,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
public void DeleteComplaintsData(int cId)
{
DataBaseUtility db = new DataBaseUtility();
string query = "delete from dbo.Complaints where Id=" + SQLUtility.getInteger(cId) + "";
db.ExecuteUpdate(query);
}
public void AddEmpInfo(int storeId, EmployeeInfoDTO employeeInfoDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[EmployeeInfo] ([EmpId],[StoreId],[EmpFirstName],[EmpMiddleName],[EmpLastName],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(employeeInfoDTO.EmpId) + "," + SQLUtility.getInteger(storeId) + "," + SQLUtility.getString(employeeInfoDTO.EmpFirstName) + "," + SQLUtility.getString(employeeInfoDTO.EmpMiddleName) + ", "
+ " " + SQLUtility.getString(employeeInfoDTO.EmpLastName) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
//Add Food order
public void AddFoodOrderItem(string item)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[FoodItem]([FoodName],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getString(item) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
//Add Inactive Store
public void AddInactiveStore(StoreDTO storeDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[Store] ([StoreNumber] ,[StoreName] ,[ConnectionString] ,[IsStoreActive] ,[CreatedDateTime] ,[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(storeDTO.StoreNumber) + " , "
+ "" + SQLUtility.getString(storeDTO.StoreName) + "," + SQLUtility.getString(storeDTO.ConnectionString) + ",'" + storeDTO.IsActive + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
// Add Order by manager
// one food order have multiple order
public void AddOrder(OrderDTO dto)
{
DataBaseUtility db = new DataBaseUtility();
string query = " INSERT INTO [dbo].[Orders]([FoodOrderId],[ItemId],[NumberRemaining],[NumberOver],[CreatedDateTime],[LastUpdateDateTime])VALUES ( "
+ " " + SQLUtility.getInteger(dto.FoodOrderId) + " , " + SQLUtility.getInteger(dto.ItemId) + " , " + SQLUtility.getInteger(dto.NumberRemaining) + " , " + SQLUtility.getInteger(dto.NumberOver) + " , "
+ " '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) ";
db.ExecuteUpdate(query);
}
public void AddBounus(ManagerBonusDTO managerBonusDTO, bool isZeroBasis)
{
DataBaseUtility db = new DataBaseUtility();
String query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[FirstDateOfMonth],[LastDateOfMonth],[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint] "
+ " ,[Catering] ,[SubInspection],[IsZeroBasis] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
+ " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ", '" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.FirstDateOFMonth) + "','" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "'," + SQLUtility.getDouble(managerBonusDTO.FoodCost) + "," + SQLUtility.getDouble(managerBonusDTO.LaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.FoodLaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.SalesIncrease) + ", "
+ " " + SQLUtility.getDouble(managerBonusDTO.CustomerIndex) + "," + SQLUtility.getDouble(managerBonusDTO.CustomerComplaint) + "," + SQLUtility.getDouble(managerBonusDTO.Catering) + ", "
+ " " + SQLUtility.getDouble(managerBonusDTO.SubInspection) + ",'" + isZeroBasis + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "') ";
db.ExecuteUpdate(query);
}
public void AddComplaints(ComplaintsDTO complaintsDTO)
{
DataBaseUtility db = new DataBaseUtility();
//string query = "INSERT INTO [dbo].[Complaints] ([StoreId],[ComplaintDate],[CustomerContacted],[FeedbackReceived],[ProblemFixed],[Comment],[Response] "
// + " ,[CreatedDateTime],[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.ComplaintDate) + "' ,"
// + " '" + complaintsDTO.CustomerContacted + "','" + complaintsDTO.FeedbackRecieved + "','" + complaintsDTO.ProblemFixed + "',"
// + " " + SQLUtility.getString(complaintsDTO.Comment) + ", " + SQLUtility.getString(complaintsDTO.Response) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "' )";
string query = "INSERT INTO [dbo].[Complaints] ([StoreId] ,[ComplaintDate],[CustomerContacted] ,[FeedbackReceived],[ProblemFixed] ,[EmailedSubway] "
+" ,[Comment],[Response],[Name] ,[Telephone] ,[Email],[CreatedDateTime] ,[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(complaintsDTO.ComplaintDate) + "', '" + complaintsDTO.CustomerContacted + "','"+complaintsDTO.FeedbackRecieved+"', "
+ " '" + complaintsDTO.ProblemFixed + "','" + complaintsDTO.EmailedSubway + "'," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Comment, 500)) + "," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Response, 500)) + "," + SQLUtility.getString(complaintsDTO.Name) + "," + SQLUtility.getString(complaintsDTO.TelePhone) + "," + SQLUtility.getString(complaintsDTO.Email) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "')";
db.ExecuteUpdate(query);
}
public void DeleteMaintenanceRequest(int id)
{
DataBaseUtility db = new DataBaseUtility();
try
{
string query = " delete from dbo.StoreMaintainance where Id = " + SQLUtility.getInteger(id) + " ";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in DeleteMaintenanceRequest Method ", ex);
}
}
public void AddPaperWork(ArrayList list)
{
foreach (WeeklyPaperworkDTO dto in list)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[WeeklyPaperWork]([StoreId],[NetSales],[GiftCardSales],[AR],[PaidOuts],[PettyExpense],[Total],[StoreTransfer],[PFG1],[PFG2],[TotalPFG],[Coke],[TotalCost],[CostPercent],[LaborCost],[LaborCostPercent],[Royalty],[FAF],[CostAndLaborCostPercent],[AdjTax],[GCRedeem],[GCDifference],[TaxPercent], "
+ " [ActualSalesTaxper],[DifferenceOfSalesTax],[NonTaxableSale],[WeekStartDate],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES (" + SQLUtility.getInteger(dto.StoreId) + " , " + SQLUtility.getDouble(dto.NetSales) + " ," + SQLUtility.getDouble(dto.GiftCardSales) + "," + SQLUtility.getDouble(dto.Ar) + "," + SQLUtility.getDouble(dto.PaidOuts) + "," + SQLUtility.getDouble(dto.PettyExpense) + ", "
+ " " + SQLUtility.getDouble(dto.Total) + "," + SQLUtility.getDouble(dto.StoreTransfer) + "," + SQLUtility.getDouble(dto.Pfg1) + "," + SQLUtility.getDouble(dto.Pfg2) + "," + SQLUtility.getDouble(dto.TotalPFG) + "," + SQLUtility.getDouble(dto.Coke) + "," + SQLUtility.getDouble(dto.TotalCost) + ", "
+ " " + SQLUtility.getDouble(dto.CostPercent) + "," + SQLUtility.getDouble(dto.LaborCost) + "," + SQLUtility.getDouble(dto.LaborCostPercent) + "," + SQLUtility.getDouble(dto.Royalty) + "," + SQLUtility.getDouble(dto.Faf) + "," + SQLUtility.getDouble(dto.CostAndLaborCostPercent) + "," + SQLUtility.getDouble(dto.AdjTax) + "," + SQLUtility.getDouble(dto.GcRedeem) + "," + SQLUtility.getDouble(dto.GcDifference) + ", "
+ " " + SQLUtility.getDouble(dto.TaxPercent) + ", " + SQLUtility.getDouble(dto.ActualSalesTaxper) + "," + SQLUtility.getDouble(dto.DifferenceOfSalesTax) + "," + SQLUtility.getDouble(dto.NonTaxableSale) + ",'" + SQLUtility.FormateDateYYYYMMDD(dto.WeekStartDate) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
}
// Store Functionality
public void AddStore(int sNumber, string sName, string connectionString)
{
try
{
DataBaseUtility db = new DataBaseUtility();
string query = " INSERT [dbo].[Store]([StoreNumber],[StoreName],[ConnectionString]) VALUES(" + SQLUtility.getInteger(sNumber) + " , "
+ " " + SQLUtility.getString(sName) + "," + SQLUtility.getString(connectionString) + " ) ";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in AddStore Method ", ex);
}
}
public void AddUserStore(int storeId, int userId)
{
try
{
DataBaseUtility db = new DataBaseUtility();
string query = " INSERT [dbo].[StoreUser]([StoreId],[UserId],[CreatedDateTime],[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(storeId) + " , "
+ " " + SQLUtility.getInteger(userId) + " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ) ";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in AddUserStore Method ", ex);
}
}
public void AddMaintenanceRequest(MaintenanceDTO dto)
{
// string query = "insert into StoreMaintainance "
try
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[StoreMaintainance]([StoreId],[MaintenanceCategoryId],[Description],[status],[DateCreated]) VALUES "
+ "(" + SQLUtility.getInteger(dto.StoreId) + ", " + SQLUtility.getInteger(dto.CategoryId) + " ," + SQLUtility.getString(dto.Description) + " , "
+ " "+SQLUtility.getString(dto.Status)+" , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )";
db.ExecuteUpdate(query);
}
catch (Exception ex)
{
log.Error(" Exception in AddMaintenanceRequest Method ", ex);
}
}
public void AddEmployeeRecord(DateTime selectDate, ArrayList storeList)
{
try
{
DateTime currentWeekStartDate = ValidationUtility.GetActualWeekStartDate(DateTime.Now);
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
// If select date not from currrent week
if (selectDate.Date < currentWeekStartDate.Date)
{
foreach (StoreDTO storeDTO in storeList)
{
ArrayList empList = GetEmployeeInfo(storeDTO.ConnectionString);
foreach (EmployeeInfoDTO employeeInfoDTO in empList)
{
if (!IsEmployeExsist(employeeInfoDTO, storeDTO.Id))
{
AddEmpInfo(storeDTO.Id, employeeInfoDTO);
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 9, 0, 0);
DateTime scheduleInOutTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 17, 0, 0);
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(newEmployeeInfoDTO.Id) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
EmployeeTrackerDTO employeeTrakingDTO = GetEmployeeTracking(employeeInfoDTO.EmpId, storeDTO.Id, businessDate);
if (employeeTrakingDTO != null)
{
ArrayList clockingList = GetEmployeClocking(employeeInfoDTO.EmpId, businessDate, storeDTO.ConnectionString);
foreach (EmployeeClockingDTO employeeClockingDTO in clockingList)
{
AddEmpClockingInfo(employeeTrakingDTO.Id, employeeClockingDTO);
}
// Update Total time worked
if (!ValidationUtility.IsNull(clockingList) && clockingList.Count > 0)
{
UpdateTotalTimeWorked(employeeTrakingDTO, clockingList);
}
}
}
}
else if (!IsWeekEmployeeTrakingExsist(employeeInfoDTO.EmpId, storeDTO.Id, weekStartDate))
{
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 9, 0, 0);
DateTime scheduleInOutTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 17, 0, 0);
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(newEmployeeInfoDTO.Id) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
EmployeeTrackerDTO employeeTrakingDTO = GetEmployeeTracking(employeeInfoDTO.EmpId, storeDTO.Id, businessDate);
if (employeeTrakingDTO != null)
{
ArrayList clockingList = GetEmployeClocking(employeeInfoDTO.EmpId, businessDate, storeDTO.ConnectionString);
foreach (EmployeeClockingDTO employeeClockingDTO in clockingList)
{
AddEmpClockingInfo(employeeTrakingDTO.Id, employeeClockingDTO);
}
// Update Total time worked
if (!ValidationUtility.IsNull(clockingList) && clockingList.Count > 0)
{
UpdateTotalTimeWorked(employeeTrakingDTO, clockingList);
}
}
}
}
}
}
}
// If select date is greter then current week
else if (selectDate.Date > currentWeekStartDate.AddDays(6).Date)
{
foreach (StoreDTO storeDTO in storeList)
{
ArrayList empList = GetEmployeeInfo(storeDTO.ConnectionString);
foreach (EmployeeInfoDTO employeeInfoDTO in empList)
{
if (!IsEmployeExsist(employeeInfoDTO, storeDTO.Id))
{
AddEmpInfo(storeDTO.Id, employeeInfoDTO);
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = ValidationUtility.GetDefaultDate();
DateTime scheduleInOutTime = ValidationUtility.GetDefaultDate();
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(newEmployeeInfoDTO.Id) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
}
else if (!IsWeekEmployeeTrakingExsist(employeeInfoDTO.EmpId, storeDTO.Id, weekStartDate))
{
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = ValidationUtility.GetDefaultDate();
DateTime scheduleInOutTime = ValidationUtility.GetDefaultDate();
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(newEmployeeInfoDTO.Id) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
}
}
}
}
else if(DateTime.Now.Date.Equals(selectDate.Date))
{
foreach (StoreDTO storeDTO in storeList)
{
ArrayList empList = GetEmployeeInfo(storeDTO.ConnectionString);
foreach (EmployeeInfoDTO employeeInfoDTO in empList)
{
if (!IsEmployeExsist(employeeInfoDTO, storeDTO.Id))
{
AddEmpInfo(storeDTO.Id, employeeInfoDTO);
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = new DateTime(1753, 1, 1, 12, 0, 0);
DateTime scheduleInOutTime = new DateTime(1753, 1, 1, 12, 0, 0);
if (businessDate.Date < DateTime.Now.Date)
{
scheduleInTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 9, 0, 0);
scheduleInOutTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 17, 0, 0);
}
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(newEmployeeInfoDTO.Id) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
if (businessDate.Date <= DateTime.Now.Date)
{
EmployeeTrackerDTO employeeTrakingDTO = GetEmployeeTracking(employeeInfoDTO.EmpId, storeDTO.Id, businessDate);
if (employeeTrakingDTO != null)
{
ArrayList clockingList = GetEmployeClocking(employeeInfoDTO.EmpId, businessDate, storeDTO.ConnectionString);
foreach (EmployeeClockingDTO employeeClockingDTO in clockingList)
{
AddEmpClockingInfo(employeeTrakingDTO.Id, employeeClockingDTO);
}
// Update Total time worked
if (!ValidationUtility.IsNull(clockingList) && clockingList.Count > 0)
{
UpdateTotalTimeWorked(employeeTrakingDTO, clockingList);
}
}
}
}
}
else if (weekStartDate.Date.Equals(DateTime.Now.Date) && !IsWeekEmployeeTrakingExsist(employeeInfoDTO.EmpId, storeDTO.Id, weekStartDate))
{
EmployeeInfoDTO newEmployeeInfoDTO = GetEmployeeInfo(storeDTO.Id, employeeInfoDTO.EmpId);
AddNewEmployeeInTracking(newEmployeeInfoDTO.Id, weekStartDate);
}
}
}
}
}
catch (Exception ex)
{
log.Error("Exceprion in AddEmployeeRecord method ", ex);
}
}
public void DeleteEmpClokingInfo(int empTrackerId)
{
DataBaseUtility db = new DataBaseUtility();
string query = " delete from EmployeeClocking where EmployeeTrackerId= " + SQLUtility.getInteger(empTrackerId) + " ";
db.ExecuteUpdate(query);
}
public void AddNewEmployeeInTracking(int empInfoId, DateTime date)
{
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(date);
DataBaseUtility db = new DataBaseUtility();
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
EmployeeTrackerDTO employeeTrackerDTO = new EmployeeTrackerDTO();
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(empInfoId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleIn) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleOut) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
}
// Update Local Store Data where Record sales is less then current record sale
public void UpdateStore(RecordSalesDTO dto, string connectionString)
{
DataBaseUtility db = new DataBaseUtility();
if (!ValidationUtility.IsNull(dto.DayOfWeek))
{
RecordSalesDTO updateDTO = GetRecordSaleByStoreID(dto.StoreId, dto.DayOfWeek);
log.Info("DayOfWeek " + dto.DayOfWeek);
log.Info(" server record sales amount " + dto.SalesAmount);
//for testing only
if (!ValidationUtility.IsNull(updateDTO))
{
log.Info(" local record sales amount " + updateDTO.SalesAmount);
}
if (!ValidationUtility.IsNull(updateDTO) && dto.SalesAmount > updateDTO.SalesAmount)
{
string query = " update dbo.RecordSales set BusinessDate = '" + dto.BusinessDate.ToString("yyyy/MM/dd") + "' , SalesAmount = " + SQLUtility.getDouble(dto.SalesAmount) + " , "
+ " PreviousSaleAmount = " + SQLUtility.getDouble(updateDTO.SalesAmount) + " , PreviousRecordSaleDate = '" + updateDTO.BusinessDate.ToString("yyyy/MM/dd") + "' , DayOfWeek = " + SQLUtility.getString(dto.DayOfWeek) + " , "
+ " LastUpdateDateTime = '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' where StoreId = " + SQLUtility.getInteger(dto.StoreId) + " and DayOfWeek = " + SQLUtility.getString(dto.DayOfWeek) + " ";
db.ExecuteUpdate(query);
ArrayList empBonusList = GetEmpInfo(connectionString, dto.BusinessDate);
foreach (EmpBonusDTO empDTO in empBonusList)
{
log.Info(" Name of employee for bonus " + empDTO.EmployeeId + " " + empDTO.FirstName);
// Insert
string empQuery = "INSERT INTO [dbo].[EmployeeBonus]([StoreId],[EmployeeId],[EmployeeRoleId],[FirstName],[LastName],[BonusAmount],[BusinessDate],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(dto.StoreId) + "," + SQLUtility.getInteger(empDTO.EmployeeId) + "," + SQLUtility.getInteger(empDTO.EmployeeRoleId) + "," + SQLUtility.getString(empDTO.FirstName) + "," + SQLUtility.getString(empDTO.LastName) + ", "
+ " 10,'" + empDTO.BusinessDate.ToString("yyyy/MM/dd") + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "')";
db.ExecuteUpdate(empQuery);
}
}
// When Record sale is not found of given day then insert it on data base
else if (ValidationUtility.IsNull(updateDTO))
{
string query = " INSERT INTO [dbo].[RecordSales]([StoreId],[DayOfWeek],[BusinessDate],[SalesAmount],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(dto.StoreId) + ", " + SQLUtility.getString(dto.DayOfWeek) + " , '" + dto.BusinessDate.ToString("yyyy/MM/dd") + "'," + SQLUtility.getDouble(dto.SalesAmount) + " "
+ " , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "') ";
db.ExecuteUpdate(query);
}
}
}
// Add Employee Tracker Info
public void AddEmpTrackerInfo(int empInfoId, DateTime selectDate)
{
DataBaseUtility db = new DataBaseUtility();
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
for (int i = 0; i < 7; i++)
{
DateTime businessDate = weekStartDate.AddDays(i);
DateTime scheduleInTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 9, 0, 0);
DateTime scheduleInOutTime = new DateTime(businessDate.Year, businessDate.Month, businessDate.Day, 17, 0, 0);
string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(empInfoId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(scheduleInOutTime) + "', "
+ " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
}
public void UpdateTotalTimeWorked(EmployeeTrackerDTO dto, ArrayList list)
{
int totalMinutesWorked = 0;
if (!ValidationUtility.IsNull(list) && list.Count > 0)
{
foreach (EmployeeClockingDTO clockingDTO in list)
{
if (clockingDTO.ClockFunctionTypeId == 2)
{
totalMinutesWorked = clockingDTO.MinutesWorked + totalMinutesWorked;
}
}
TimeSpan time = TimeSpan.FromMinutes(totalMinutesWorked);
DataBaseUtility db = new DataBaseUtility();
string query = "update dbo.EmployeeTracker set TotalTimeWorked = '" + time + "', LastUpdateDateTime = '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' where Id=" + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
}
public void UpdateSheduleDateTime(int id, DateTime inTime, DateTime outTime, DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
String query = " update dbo.EmployeeTracker set ScheduleIn='" + SQLUtility.FormateDateYYYYMMDDWtithTime(inTime) + "', ScheduleOut='" + SQLUtility.FormateDateYYYYMMDDWtithTime(outTime) + "'"
+ " , LastUpdateDateTime = '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' where Id=" + SQLUtility.getInteger(id) + " ";
db.ExecuteUpdate(query);
}
//public ArrayList GetAvg(ArrayList list)
//{
// foreach (WeeklyPaperworkDTO dto in list)
// {
// }
//}
public void UpdateWeeklyPaperSales(WeeklyPaperworkDTO weeklyPaperworkDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "update dbo.WeeklyPaperWork set AR=" + SQLUtility.getDouble(weeklyPaperworkDTO.Ar) + ",PaidOuts=" + SQLUtility.getDouble(weeklyPaperworkDTO.PaidOuts) + ",PettyExpense=" + SQLUtility.getDouble(weeklyPaperworkDTO.PettyExpense) + ",StoreTransfer=" + SQLUtility.getDouble(weeklyPaperworkDTO.StoreTransfer) + ",PFG1=" + SQLUtility.getDouble(weeklyPaperworkDTO.Pfg1) + ",PFG2=" + SQLUtility.getDouble(weeklyPaperworkDTO.Pfg2) + ",Coke=" + SQLUtility.getDouble(weeklyPaperworkDTO.Coke) + ",LaborCost=" + SQLUtility.getDouble(weeklyPaperworkDTO.LaborCost) + ",TaxPercent=" + SQLUtility.getDouble(weeklyPaperworkDTO.TaxPercent) + ",NonTaxableSale=" + SQLUtility.getDouble(weeklyPaperworkDTO.NonTaxableSale) + " where Id=" + SQLUtility.getDouble(weeklyPaperworkDTO.Id) + "";
db.ExecuteUpdate(query);
}
public void delete(int storeId, DateTime weekStartDay)
{
DataBaseUtility db = new DataBaseUtility();
string query = "delete from WeeklyPaperWork where StoreId=" + SQLUtility.getInteger(storeId) + "and WeekStartDate='" + SQLUtility.FormateDateYYYYMMDD(weekStartDay) + "'";
db.ExecuteUpdate(query);
}
public void UpdateMaintenanceRequest(MaintenanceDTO dto, bool isAdmin)
{
DataBaseUtility db = new DataBaseUtility();
try
{
if (isAdmin)
{
string query = " update dbo.StoreMaintainance set MaintenanceCategoryId =" + SQLUtility.getInteger(dto.CategoryId) + ",Description = " + SQLUtility.getString(dto.Description) + " , status=" + SQLUtility.getString(dto.Status) + ",DateResolved='" + dto.DateResolved.ToString("yyyy/MM/dd hh:mm tt") + "' "
+ " where Id = " + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
else
{
string query = " update dbo.StoreMaintainance set MaintenanceCategoryId =" + SQLUtility.getInteger(dto.CategoryId) + ",Description = " + SQLUtility.getString(dto.Description) + " "
+ " where Id = " + SQLUtility.getInteger(dto.Id) + " ";
db.ExecuteUpdate(query);
}
}
catch(Exception ex)
{
log.Error(" Exception in UpdateMaintenanceRequest Method ", ex);
}
}
public void UpdatePerdaySales(double perDaySalesAmount, int storeId, DateTime bussnessDate, int opId)
{
bool isCurrentDateSale = IsCurrentDateSaleIsExsist(storeId, bussnessDate);
DataBaseUtility db = new DataBaseUtility();
if (isCurrentDateSale)
{
string query = " update dbo.PerdaySales set SalesAmount = " + SQLUtility.getDouble(perDaySalesAmount) + " , OpeningInformationId = " + SQLUtility.getInteger(opId) + " , "
+ " LastUpdateDateTime = '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' where StoreId = " + SQLUtility.getInteger(storeId) + " and BusinessDate = '" + bussnessDate.ToString("yyyy/MM/dd") + "' ";
db.ExecuteUpdate(query);
}
else
{
string query = " INSERT INTO [dbo].[PerdaySales]([StoreId],[OpeningInformationId],[BusinessDate],[SalesAmount],[WeekOfDay],[CreatedDateTime],[LastUpdateDateTime]) VALUES "
+ " (" + SQLUtility.getInteger(storeId) + ", " + SQLUtility.getInteger(opId) + " , '" + SQLUtility.FormateDateYYYYMMDD(bussnessDate) + "' ," + SQLUtility.getDouble(perDaySalesAmount) + " , " + SQLUtility.getString(bussnessDate.DayOfWeek.ToString()) + " , "
+ " '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' , '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "') ";
db.ExecuteUpdate(query);
}
}
public void UpdatePaperWork(ArrayList list)
{
foreach (WeeklyPaperworkDTO dto in list)
{
DataBaseUtility db = new DataBaseUtility();
string query = "update WeeklyPaperWork set NetSales=" + SQLUtility.getDouble(dto.NetSales) + " , GiftCardSales = " + SQLUtility.getDouble(dto.GiftCardSales) + " , AdjTax=" + SQLUtility.getDouble(dto.AdjTax) + ", GCRedeem =" + SQLUtility.getDouble(dto.GcRedeem) + ", GCDifference=" + SQLUtility.getDouble(dto.GcDifference) + ","
+ "LastUpdateDateTime = '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' where StoreId = " + SQLUtility.getInteger(dto.StoreId) + " and WeekStartDate = '" + SQLUtility.FormateDateYYYYMMDD(dto.WeekStartDate) + "' ";
db.ExecuteUpdate(query);
}
}
