public bool IsBazarExist(DailyBazar bazar)
{
try
{
bool isNameExists = false;
Query = "SELECT*FROM DailyBazar WHERE ItemId = '" + bazar.ItemId + "' and Date='" + bazar.Date + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Connection.Open();
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
isNameExists = true;
}
return(isNameExists);
}
catch (Exception exception)
{
return(false);
}
finally
{
Connection.Close();
}
}
public DailyBazar GetTotalAmount(string date, int itemId)
{
try
{
Query = @"SELECT Amount FROM DailyBazar WHERE Date = '" + date + "' and ItemId = '" + itemId + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Connection.Open();
Reader = Command.ExecuteReader();
DailyBazar dailyBazars = null;
if (Reader.HasRows)
{
while (Reader.Read())
{
DailyBazar dailyBazar = new DailyBazar();
dailyBazar.Amount = Convert.ToDouble(Reader["Amount"]);
dailyBazars = dailyBazar;
}
Reader.Close();
}
return(dailyBazars);
}
catch (Exception exception)
{
return(null);
}
finally
{
Connection.Close();
}
}
public DailyBazar GetTotalUser1(string date, int itemId)
{
try
{
Query = @"SELECT SUM(ItemQuantity) as TotalUser
FROM DepartmentCafeteriaTransaction
WHERE CheckDate = '" + date + "' and FoodItemCostId = '" + itemId + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Connection.Open();
Reader = Command.ExecuteReader();
DailyBazar dailyBazars = null;
if (Reader.HasRows)
{
while (Reader.Read())
{
DailyBazar dailyBazar = new DailyBazar();
dailyBazar.TotalDepartmentUser = (int)Reader["TotalUser"];
dailyBazars = dailyBazar;
}
Reader.Close();
}
return(dailyBazars);
}
catch (Exception exception)
{
return(null);
}
finally
{
Connection.Close();
}
}
public int UpdateBazar(DailyBazar bazar)
{
try
{
Query = @"UPDATE DailyBazar
SET [Amount] = @Amount
WHERE Date = '" + bazar.Date + "' and ItemId = '" + bazar.ItemId + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Command.Parameters.Clear();
Command.Parameters.Add("Amount", SqlDbType.Decimal);
Command.Parameters["Amount"].Value = bazar.Amount;
Connection.Open();
int rowsAffected = Command.ExecuteNonQuery();
return(rowsAffected);
}
catch (Exception exception)
{
return(0);
}
finally
{
Connection.Close();
}
}
public int SaveBazar(DailyBazar bazar)
{
try
{
Query = @"INSERT INTO DailyBazar(Date, Amount, ItemId) VALUES(@Date, @Amount, @ItemId )";
Command.CommandText = Query;
Command.Connection = Connection;
Command.Parameters.Clear();
Command.Parameters.Add("Date", SqlDbType.Date);
Command.Parameters["Date"].Value = bazar.Date;
Command.Parameters.Add("Amount", SqlDbType.Decimal);
Command.Parameters["Amount"].Value = bazar.Amount;
Command.Parameters.Add("ItemId", SqlDbType.Int);
Command.Parameters["ItemId"].Value = bazar.ItemId;
Connection.Open();
int rowsAffected = Command.ExecuteNonQuery();
return(rowsAffected);
}
catch (Exception exception)
{
throw;
}
finally
{
Connection.Close();
}
}
public string UpdateRate(DailyBazar bazar)
{
int result = _bazarGateway.UpdateRate(bazar);
if (result > 0)
{
return("Rate Update Successfully!");
}
return("Connection Error.....!");
}
public ActionResult FindUnitRate(string date, int itemId)
{
if (Session["UserRoleId"] == null)
{
return(RedirectToAction("Login", "Login"));
}
DailyBazar bazar = _bazarManager.FindUnitRate(date, itemId);
return(Json(bazar, JsonRequestBehavior.AllowGet));
}
public ActionResult AddUnitRate(DailyBazar bazar)
{
try
{
ViewBag.Message = _bazarManager.UpdateRate(bazar);
}
catch (Exception)
{
return(null);
}
return(View());
}
public ActionResult SaveBazar(DailyBazar bazar)
{
try
{
ViewBag.Message = _bazarManager.SaveBazar(bazar);
}
catch (Exception)
{
return(null);
}
return(View());
}
//public DailyBazar GetTotalAmount(string date)
//{
// return _bazarGateway.GetTotalAmount(date);
//}
//public DailyBazar GetTotalUser(string date)
//{
// return _bazarGateway.GetTotalUser(date);
//}
public DailyBazar FindUnitRate(string date, int itemId)
{
DailyBazar dailyBazar = _bazarGateway.GetTotalAmount(date, itemId);
DailyBazar daily = _bazarGateway.GetTotalUser(date, itemId);
DailyBazar dailt1 = _bazarGateway.GetTotalUser1(date, itemId);
if (dailyBazar != null && daily != null || dailt1 != null)
{
double totalAmount = dailyBazar.Amount;
int employee = 0;
int department = 0;
int totalUser = 0;
if (daily == null)
{
employee = 0;
department = dailt1.TotalDepartmentUser;
totalUser = employee + department;
}
if (dailt1 == null)
{
employee = daily.TotalUser;
department = 0;
totalUser = employee + department;
}
else
{
employee = daily.TotalUser;
department = dailt1.TotalDepartmentUser;
totalUser = employee + department;
}
double unitrate = totalAmount / totalUser;
DailyBazar bazar = new DailyBazar()
{
TotalUser = totalUser,
Amount = totalAmount,
UnitRate = unitrate
};
return(bazar);
}
else
{
DailyBazar bazar = new DailyBazar()
{
TotalUser = 0,
Amount = 0,
UnitRate = 0.00,
};
return(bazar);
}
}
public int UpdateRate(DailyBazar bazar)
{
try
{
int rowsAffected = 0;
int rowsAffected1 = 0;
Query = @"UPDATE EmployeeCafeteriaTransaction
SET UnitRate = @UnitRate
WHERE CheckDate = '" + bazar.Date + "' and FoodItemCostId = '" + bazar.ItemId + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Command.Parameters.Clear();
Command.Parameters.Add("UnitRate", SqlDbType.Decimal);
Command.Parameters["UnitRate"].Value = bazar.UnitRate;
Connection.Open();
rowsAffected = Command.ExecuteNonQuery();
Connection.Close();
Query = @"UPDATE DepartmentCafeteriaTransaction
SET UnitRate = @UnitRate
WHERE CheckDate = '" + bazar.Date + "' and FoodItemCostId = '" + bazar.ItemId + "'";
Command.CommandText = Query;
Command.Connection = Connection;
Command.Parameters.Clear();
Command.Parameters.Add("UnitRate", SqlDbType.Decimal);
Command.Parameters["UnitRate"].Value = bazar.UnitRate;
Connection.Open();
rowsAffected1 = Command.ExecuteNonQuery();
if (rowsAffected > 0 || rowsAffected1 > 0)
{
return(1);
}
return(0);
}
catch (Exception exception)
{
return(0);
}
finally
{
Connection.Close();
}
}
public string SaveBazar(DailyBazar bazar)
{
if (IsBazarExist(bazar))
{
int result = _bazarGateway.UpdateBazar(bazar);
if (result > 0)
{
return("Bazar Update Successfully!");
}
return("Connection Error.....!");
}
else
{
int result = _bazarGateway.SaveBazar(bazar);
if (result > 0)
{
return("Bazar Saved Successfully!");
}
return("Connection Error.....!");
}
}
public bool IsBazarExist(DailyBazar bazar)
{
return(_bazarGateway.IsBazarExist(bazar));
}
