public static List <OrderReportOutput> GetListOrderCountMonthDetail(int year, int month, long location_id)
{
List <OrderReportOutput> result = new List <OrderReportOutput>();
string queryMonthDetail = "select * from wifi_mensa.outputmonthreport " +
"where serve_date >= :startDate and serve_date<= :endDate and location_id= :location_id " +
"order by location_id,serve_date asc ";
if (DBConnection.Connection.State.ToString() == "Closed")
{
DBConnection.Connection.Open();
}
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = DBConnection.Connection;
try
{
int daysInMonth = DateTime.DaysInMonth(year, month);
DateTime startDate = new DateTime(year, month, 1);
DateTime endDate = new DateTime(year, month, daysInMonth);
command.Parameters.AddWithValue("startDate", startDate);
command.Parameters.AddWithValue("endDate", endDate);
command.Parameters.AddWithValue("location_id", location_id);
command.CommandText = queryMonthDetail;
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
OrderReportOutput Order = new OrderReportOutput();
// result.Add(new OrderReportOutput()
Order.TotalCount = reader.GetInt32(0);
Order.LocationId = reader.GetInt32(1);
Order.MenueTitel = reader.GetString(2);
Order.TotalPrice = reader.GetDecimal(3);
Order.ServeDate = reader.GetDateTime(4);
Order.Type = reader.GetString(5);
Order.SinglePrice = Order.TotalPrice / Order.TotalCount;
Order.MenueId = null;
Order.ConsumedCount = null;
Order.OpenCosts = null;
result.Add(Order);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DBConnection.Connection.Close();
}
return(result);
}
public static List <OrderReportOutput> GetListOrderCount(int year, int month, int day, long location_id)
{
List <OrderReportOutput> result = new List <OrderReportOutput>();
string query = "select * from wifi_mensa.outputorderedmenues where servedate= :serve_date and location_id= :location_id";
if (DBConnection.Connection.State.ToString() == "Closed")
{
DBConnection.Connection.Open();
}
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = DBConnection.Connection;
try
{
DateTime date = new DateTime(year, month, day);
command.Parameters.AddWithValue("serve_date", date);
command.Parameters.AddWithValue("location_id", location_id);
command.CommandText = query;
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
OrderReportOutput Order = new OrderReportOutput();
// result.Add(new OrderReportOutput()
Order.TotalCount = reader.GetInt32(0);
Order.ConsumedCount = reader.GetInt32(1);
Order.MenueId = reader.GetInt32(2);
Order.ServeDate = reader.GetDateTime(3);
Order.TotalPrice = reader.GetDecimal(4);
Order.MenueTitel = reader.GetString(5);
Order.LocationId = reader.GetInt32(6);
Order.SinglePrice = Order.TotalPrice / Order.TotalCount;
Order.OpenCosts = Order.SinglePrice * (Order.TotalCount - Order.ConsumedCount);
result.Add(Order);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DBConnection.Connection.Close();
}
return(result);
}
