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();
}
}
