public List <ViewDepartmentTransaction> GetDepartmentTransaction(string name, string startdate, string enddate, int breakfast, int lunch, int dinner, int sehuri, string all) { try { if (all == "All") { Query = @"SELECT d.Name as DptName, f.Name as FoodName, AVG(e.UnitRate) as UnitRate, Sum(e.ItemQuantity) as ItemQuantity,convert(varchar, e.CheckDate, 23) as CheckDate, s.Name as DivisionName FROM DepartmentCafeteriaTransaction e INNER JOIN Department d on d.Id = e.DeptId INNER JOIN Division s on s.Id = d.DivisionId INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId WHERE d.Name = '" + name + "' AND CheckDate >= '" + startdate + "' AND CheckDate <= '" + enddate + "'Group By d.Name, f.Name, e.UnitRate,e.CheckDate, s.Name"; } else { Query = @"SELECT d.Name as DptName, f.Name as FoodName, AVG(e.UnitRate) as UnitRate, Sum(e.ItemQuantity) as ItemQuantity,convert(varchar, e.CheckDate, 23) as CheckDate, s.Name as DivisionName FROM DepartmentCafeteriaTransaction e INNER JOIN Department d on d.Id = e.DeptId INNER JOIN Division s on s.Id = d.DivisionId INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId WHERE d.Name = '" + name + "' AND CheckDate >= '" + startdate + "' AND CheckDate <= '" + enddate + "' AND ( FoodItemCostId = '" + breakfast + "' OR FoodItemCostId= '" + lunch + "' OR FoodItemCostId= '" + dinner + "' OR FoodItemCostId = '" + sehuri + "') Group By d.Name, f.Name, e.UnitRate,e.CheckDate, s.Name"; } Command.CommandText = Query; Command.Connection = Connection; Connection.Open(); Reader = Command.ExecuteReader(); List <ViewDepartmentTransaction> viewDepartmentTransactions = new List <ViewDepartmentTransaction>(); int number = 1; if (Reader.HasRows) { while (Reader.Read()) { ViewDepartmentTransaction viewDepartmentTransaction = new ViewDepartmentTransaction(); viewDepartmentTransaction.SerialNumber = number; viewDepartmentTransaction.CheckDate = Reader["CheckDate"].ToString(); viewDepartmentTransaction.Department = Reader["DptName"].ToString(); viewDepartmentTransaction.Division = Reader["DivisionName"].ToString(); viewDepartmentTransaction.FoodItem = Reader["FoodName"].ToString(); viewDepartmentTransaction.ItemCost = (decimal)Reader["UnitRate"]; viewDepartmentTransaction.ItemQty = (int)Reader["ItemQuantity"]; viewDepartmentTransaction.SubTotal = viewDepartmentTransaction.ItemCost * viewDepartmentTransaction.ItemQty; viewDepartmentTransaction.Total = viewDepartmentTransaction.ItemCost * viewDepartmentTransaction.ItemQty; viewDepartmentTransactions.Add(viewDepartmentTransaction); number++; } Reader.Close(); } return(viewDepartmentTransactions); } catch (Exception exception) { return(null); } finally { Connection.Close(); } }
public List <ViewDepartmentTransaction> GetItemWishEmployeeReport(int type, string startdate, string enddate, string name, int breakfast, int lunch, int dinner, int sehuri, string all) { try { if (name == "All Employee") { if (all == "All") { Query = @"SELECT a.EmpId,a.Name as EmployeeName,a.Proximity, d.Name as DivisionName, s.Name as DepartmentName, g.Name as DesignationName, f.Name as FoodItemName, Sum(e.ItemQuentity) as ItemQuentity, e.UnitRate, convert(varchar, e.CheckDate, 23) as CheckDate FROM EmployeeCafeteriaTransaction e INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId INNER JOIN Employee a on a.EmpId=e.EmpId INNER JOIN Division d on d.Id = a.DivisionId INNER JOIN Department s on s.Id = a.DepartmentId INNER JOIN Designation g on g.Id = a.DesignationId INNER JOIN FoodItemCost c on c.Id = e.FoodItemCostId WHERE CheckDate >= '" + startdate + "' AND CheckDate <= ' " + enddate + "' " + " Group By a.EmpId,a.Name,a.Proximity, d.Name,s.Name, g.Name, f.Name,e.UnitRate, e.CheckDate"; } else { Query = @"SELECT a.EmpId,a.Name as EmployeeName,a.Proximity, d.Name as DivisionName, s.Name as DepartmentName, g.Name as DesignationName, f.Name as FoodItemName, Sum(e.ItemQuentity) as ItemQuentity, e.UnitRate, convert(varchar, e.CheckDate, 23) as CheckDate FROM EmployeeCafeteriaTransaction e INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId INNER JOIN Employee a on a.EmpId=e.EmpId INNER JOIN Division d on d.Id = a.DivisionId INNER JOIN Department s on s.Id = a.DepartmentId INNER JOIN Designation g on g.Id = a.DesignationId INNER JOIN FoodItemCost c on c.Id = e.FoodItemCostId WHERE CheckDate >= '" + startdate + "' AND CheckDate <= ' " + enddate + "' AND (e.FoodItemCostId = '" + breakfast + "' OR e.FoodItemCostId= '" + lunch + "' OR e.FoodItemCostId= '" + dinner + "' OR e.FoodItemCostId= '" + sehuri + "') Group By a.EmpId,a.Name,a.Proximity, d.Name,s.Name, g.Name, f.Name,e.UnitRate, e.CheckDate"; } } else { if (all == "All") { Query = @"SELECT a.EmpId,a.Name as EmployeeName,a.Proximity, d.Name as DivisionName, s.Name as DepartmentName, g.Name as DesignationName, f.Name as FoodItemName, Sum(e.ItemQuentity) as ItemQuentity, e.UnitRate, convert(varchar, e.CheckDate, 23) as CheckDate FROM EmployeeCafeteriaTransaction e INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId INNER JOIN Employee a on a.EmpId=e.EmpId INNER JOIN Division d on d.Id = a.DivisionId INNER JOIN Department s on s.Id = a.DepartmentId INNER JOIN Designation g on g.Id = a.DesignationId INNER JOIN FoodItemCost c on c.Id = e.FoodItemCostId WHERE CheckDate >= '" + startdate + "' AND CheckDate <= ' " + enddate + "' AND a.Name = '" + name + "' Group By a.EmpId,a.Name,a.Proximity, d.Name,s.Name, g.Name, f.Name,e.UnitRate, e.CheckDate"; } else { Query = @"SELECT a.EmpId,a.Name as EmployeeName,a.Proximity, d.Name as DivisionName, s.Name as DepartmentName, g.Name as DesignationName, f.Name as FoodItemName, Sum(e.ItemQuentity) as ItemQuentity, e.UnitRate, convert(varchar, e.CheckDate, 23) as CheckDate FROM EmployeeCafeteriaTransaction e INNER JOIN FoodItemCost f on f.Id = e.FoodItemCostId INNER JOIN Employee a on a.EmpId=e.EmpId INNER JOIN Division d on d.Id = a.DivisionId INNER JOIN Department s on s.Id = a.DepartmentId INNER JOIN Designation g on g.Id = a.DesignationId INNER JOIN FoodItemCost c on c.Id = e.FoodItemCostId WHERE CheckDate >= '" + startdate + "' AND CheckDate <= ' " + enddate + "' AND a.Name = '" + name + "' AND (e.FoodItemCostId = '" + breakfast + "' OR e.FoodItemCostId= '" + lunch + "' OR e.FoodItemCostId= '" + dinner + "' OR e.FoodItemCostId= '" + sehuri + "') Group By a.EmpId,a.Name,a.Proximity, d.Name,s.Name, g.Name, f.Name,e.UnitRate, e.CheckDate"; } } Command.CommandText = Query; Command.Connection = Connection; Connection.Open(); Reader = Command.ExecuteReader(); List <ViewDepartmentTransaction> viewDepartmentTransactions = new List <ViewDepartmentTransaction>(); int number = 1; if (Reader.HasRows) { while (Reader.Read()) { ViewDepartmentTransaction viewDepartmentTransaction = new ViewDepartmentTransaction(); viewDepartmentTransaction.SerialNumber = number; viewDepartmentTransaction.PIN = Reader["EmpId"].ToString(); viewDepartmentTransaction.EmployeeName = Reader["EmployeeName"].ToString(); viewDepartmentTransaction.Designation = Reader["DesignationName"].ToString(); viewDepartmentTransaction.Division = Reader["DivisionName"].ToString(); viewDepartmentTransaction.Department = Reader["DepartmentName"].ToString(); viewDepartmentTransaction.FoodItem = Reader["FoodItemName"].ToString(); viewDepartmentTransaction.CheckDate = Reader["CheckDate"].ToString(); viewDepartmentTransaction.ItemQty = (int)Reader["ItemQuentity"]; decimal value1 = (decimal)Reader["UnitRate"]; value1 = System.Math.Round(value1, 2); viewDepartmentTransaction.ItemCost = value1; decimal value = viewDepartmentTransaction.ItemCost * viewDepartmentTransaction.ItemQty; value = System.Math.Round(value, 2); viewDepartmentTransaction.Total = value; viewDepartmentTransactions.Add(viewDepartmentTransaction); number++; } Reader.Close(); } Connection.Close(); return(viewDepartmentTransactions); } catch (Exception exception) { return(null); } finally { Connection.Close(); } }
public List <ViewDepartmentTransaction> DepartmentReport(string name, string startdate, string enddate, int breakfast, int lunch, int dinner, int sehuri, string all) { try { if (all == "All") { Query = @"SELECT AVG(e.UnitRate) AS UnitRate, Sum(e.ItemQuentity) AS ItemQuentity, s.Name as DivisionName, a.Name AS DepartmentName FROM EmployeeCafeteriaTransaction e INNER JOIN Employee d on d.EmpId = e.EmpId INNER JOIN Division s on s.Id = d.DivisionId INNER JOIN Department a on a.Id = d.DepartmentId WHERE s.Id = '" + name + "' AND CheckDate >= '" + startdate + "' AND CheckDate <= '" + enddate + "'GROUP BY s.Name, a.Name"; } else { Query = @"SELECT AVG(e.UnitRate) AS UnitRate, Sum(e.ItemQuentity) AS ItemQuentity, s.Name as DivisionName, a.Name AS DepartmentName FROM EmployeeCafeteriaTransaction e INNER JOIN Employee d on d.EmpId = e.EmpId INNER JOIN Division s on s.Id = d.DivisionId INNER JOIN Department a on a.Id = d.DepartmentId WHERE s.Id = '" + name + "' AND CheckDate >= '" + startdate + "' AND CheckDate <= '" + enddate + "' AND ( FoodItemCostId = '" + breakfast + "' OR FoodItemCostId= '" + lunch + "' OR FoodItemCostId= '" + dinner + "' OR FoodItemCostId = '" + sehuri + "') GROUP BY s.Name, a.Name"; } Command.CommandText = Query; Command.Connection = Connection; Connection.Open(); Reader = Command.ExecuteReader(); List <ViewDepartmentTransaction> viewDepartmentTransactions = new List <ViewDepartmentTransaction>(); int number = 1; if (Reader.HasRows) { while (Reader.Read()) { ViewDepartmentTransaction viewDepartmentTransaction = new ViewDepartmentTransaction(); viewDepartmentTransaction.SerialNumber = number; viewDepartmentTransaction.Division = Reader["DivisionName"].ToString(); viewDepartmentTransaction.Department = Reader["DepartmentName"].ToString(); viewDepartmentTransaction.ItemCost = (decimal)Reader["UnitRate"]; viewDepartmentTransaction.ItemQty = (int)Reader["ItemQuentity"]; viewDepartmentTransaction.Total = viewDepartmentTransaction.ItemCost * viewDepartmentTransaction.ItemQty; viewDepartmentTransactions.Add(viewDepartmentTransaction); number++; } Reader.Close(); } return(viewDepartmentTransactions); } catch (Exception exception) { return(null); } finally { Connection.Close(); } }