public void LoadPremiumItems(int pChar)
{
try
{
locker.WaitOne();
DataTable Premiumdata = null;
using (var dbClient = Program.CharDBManager.GetClient())
{
Premiumdata = dbClient.ReadDataTable("SELECT *FROM PremiumItem WHERE CharID='" + pChar + "'");
}
if (Premiumdata != null)
{
foreach (DataRow row in Premiumdata.Rows)
{
var pItem = PremiumItem.LoadFromDatabase(row);
PremiumItems[pItem.PageID].Add(pItem);
}
}
}
finally
{
locker.ReleaseMutex();
}
}
private IQueryOver <Premium> GetPremiumsQuery(IUnitOfWork uow)
{
PremiumJournalNode resultAlias = null;
Premium premiumAlias = null;
PremiumItem premiumItemAlias = null;
Employee employeeAlias = null;
var query = uow.Session.QueryOver <Premium>(() => premiumAlias)
.JoinAlias(f => f.Items, () => premiumItemAlias)
.JoinAlias(() => premiumItemAlias.Employee, () => employeeAlias);
if (FilterViewModel.Subdivision != null)
{
query.Where(() => employeeAlias.Subdivision.Id == FilterViewModel.Subdivision.Id);
}
if (FilterViewModel.StartDate.HasValue)
{
query.Where(() => premiumAlias.Date >= FilterViewModel.StartDate.Value);
}
if (FilterViewModel.EndDate.HasValue)
{
query.Where(() => premiumAlias.Date <= FilterViewModel.EndDate.Value);
}
var employeeProjection = CustomProjections.Concat_WS(
" ",
() => employeeAlias.LastName,
() => employeeAlias.Name,
() => employeeAlias.Patronymic
);
query.Where(
GetSearchCriterion(
() => premiumAlias.Id,
() => premiumAlias.PremiumReasonString,
() => employeeProjection,
() => premiumAlias.TotalMoney
)
);
var resultQuery = query
.SelectList(list => list
.SelectGroup(() => premiumAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => premiumAlias.Date).WithAlias(() => resultAlias.Date)
.Select(CustomProjections.GroupConcat(
employeeProjection,
false,
employeeProjection,
OrderByDirection.Asc,
"\n")
).WithAlias(() => resultAlias.EmployeesName)
.Select(() => premiumAlias.PremiumReasonString).WithAlias(() => resultAlias.PremiumReason)
.Select(() => premiumAlias.TotalMoney).WithAlias(() => resultAlias.PremiumSum)
).OrderBy(o => o.Date).Desc
.TransformUsing(Transformers.AliasToBean <PremiumJournalNode <Premium> >());
return(resultQuery);
}
public ActionResult BeansPurchase()
{
Database d = Database.CurrentInstance;
int userID = Convert.ToInt32(Session["UserID"]);
List <PremiumItem> beanPurchases = new List <PremiumItem>();
int userBeans = 0;
string username = "";
try
{
if (d.OpenConnection())
{
string purchasesQuery = "SELECT * FROM beanpurchases";
MySqlCommand c = new MySqlCommand(purchasesQuery, d.conn);
using (MySqlDataReader r = c.ExecuteReader())
{
while (r.Read())
{
PremiumItem purchase = new PremiumItem
{
itemName = (r["beanName"].ToString()),
priceOfBeans = Convert.ToDouble(r["beanPrice"]),
beanAmount = Convert.ToInt32(r["beanAmount"]),
beanIcon = (r["beanIcon"].ToString())
};
beanPurchases.Add(purchase);
Debug.WriteLine(purchase.priceOfBeans);
}
r.Close();
ViewBag.BeanPurchasesData = beanPurchases;
}
MySqlCommand c4 = new MySqlCommand("SELECT * FROM users where userID = @userID", d.conn);
c4.Parameters.AddWithValue("@userID", userID);
using (MySqlDataReader r3 = c4.ExecuteReader())
{
while (r3.Read())
{
userBeans = Convert.ToInt32(r3["beansAmount"]);
username = r3["userName"].ToString();
}
r3.Close();
}
Session["userBeans"] = userBeans;
Session["username"] = username;
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
}
finally
{
d.CloseConnection();
}
return(View());
}
public void RemovePremiumItem(PremiumItem pItem)
{
try
{
locker.WaitOne();
PremiumItems[pItem.PageID].Remove(pItem);
}
finally
{
locker.ReleaseMutex();
}
}
public ActionResult ItemManager()
{
#region role and is logged in
if (Session["uname"] == null || Session["uname"].ToString() == "")
{
return(RedirectToAction("Login", "User"));
}
if (Session["role"].ToString() != "Admin")
{
return(RedirectToAction("Index", "Unauthorised"));
}
#endregion
Database d = Database.CurrentInstance;
List <PremiumItem> listy = new List <PremiumItem>();
try
{
if (d.OpenConnection())
{
string SearchQuery = "Select * from dububase.premiumitem";
MySqlCommand c = new MySqlCommand(SearchQuery, d.conn);
using (MySqlDataReader r = c.ExecuteReader())
{
while (r.Read())
{
PremiumItem item = new PremiumItem
{
itemID = (r["itemID"]).ToString(),
itemName = (r["itemName"]).ToString(),
beansPrice = Convert.ToInt32(r["beansPrice"].ToString()),
itemDescription = (r["itemDescription"]).ToString(),
itemType = (r["itemType"]).ToString()
};
listy.Add(item);
}
}
ViewBag.Listy = listy;
}
}
catch (MySqlException e)
{
Debug.WriteLine("MySQL Error!");
}
finally
{
d.CloseConnection();
}
return(View());
}
public ActionResult ItemEdit(string name)
{
#region role and is logged in
if (Session["uname"] == null || Session["uname"].ToString() == "")
{
return(RedirectToAction("Login", "User"));
}
if (Session["role"].ToString() != "Admin")
{
return(RedirectToAction("Index", "Unauthorised"));
}
#endregion
Database d = Database.CurrentInstance;
try
{
if (d.OpenConnection())
{
string SearchQuery = "Select * from dububase.premiumitem where itemName = @name";
MySqlCommand c = new MySqlCommand(SearchQuery, d.conn);
c.Parameters.AddWithValue("@name", name);
using (MySqlDataReader r = c.ExecuteReader())
{
while (r.Read())
{
PremiumItem item = new PremiumItem
{
itemName = (r["itemName"]).ToString(),
beansPrice = Convert.ToInt32(r["beansPrice"].ToString()),
itemDescription = (r["itemDescription"]).ToString(),
itemType = (r["itemType"]).ToString(),
itemID = (r["itemID"].ToString())
};
ViewBag.item = item;
}
}
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
}
finally
{
d.CloseConnection();
}
return(View());
}
private PremiumItem getPremiumRecord(string productName, string planName, string planType, int famComp, string chronic,
int sumInsured, int deductible, int age, int thisTerm, string name)
{
//int term = age - model.Age + 1;
if (age > 80)
{
age = 80;
}
var premium = from p in _context.Premium
where
p.Age == age &&
p.SumDeduct.Deductible == deductible &&
p.SumDeduct.SumInsured == sumInsured &&
p.SumDeduct.Product.ProductName == productName &&
p.SumDeduct.Product.PlanName == planName &&
p.SumDeduct.Product.ProductType == planType &&
p.SumDeduct.Product.Condition == chronic &&
p.SumDeduct.Product.FamilyCompositionId == famComp
select p;
Premium prem = premium.SingleOrDefault();
PremiumItem pm = null;
if (prem == null)
{
addMessage("No Premium found - " +
"||Age=" + age + ":" +
"Deductible=" + deductible + ":" +
"SumInsured=" + sumInsured + ":" +
"ProductName=" + productName + ":" +
"PlanName=" + planName + ":" +
"ProductType=" + planType + ":" +
"Condition=" + chronic + ":" +
"FamilyCompositionId=" + famComp);
}
else
{
pm = new PremiumItem
{
Name = name,
Amount = Math.Round(prem.Premium1, 0),
Info = "Prem Id=" + prem.PremiumId + ",Condition=" + chronic
};
}
return(pm);
}
public ActionResult ItemEdit(PremiumItem item)
{
#region role and is logged in
if (Session["uname"] == null || Session["uname"].ToString() == "")
{
return(RedirectToAction("Login", "User"));
}
if (Session["role"].ToString() != "Admin")
{
return(RedirectToAction("Index", "Unauthorised"));
}
#endregion
Database d = Database.CurrentInstance;
try
{
if (d.OpenConnection())
{
Debug.WriteLine("Testing");
Debug.WriteLine(item.itemName);
Debug.WriteLine(item.priceOfBeans);
Debug.WriteLine(item.priceOfBeans.GetType());
//update
string SearchQuery = "Update premiumitem set itemName = @1, beansPrice = @2, itemDescription = @3 where itemID = @5";
MySqlCommand c = new MySqlCommand(SearchQuery, d.conn);
c.Parameters.AddWithValue("@1", item.itemName);
c.Parameters.AddWithValue("@2", item.priceOfBeans);
c.Parameters.AddWithValue("@3", item.itemDescription);
c.Parameters.AddWithValue("@5", Convert.ToInt32(item.itemID));
Debug.WriteLine(c.CommandText);
c.ExecuteNonQuery();
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
}
finally
{
d.CloseConnection();
}
return(RedirectToAction("ItemManager"));
}
private bool NeedPremiumRaskatGAZelleInRouteListDate(IUnitOfWork uow)
{
if (routeList.RecalculatedDistance >= premiumRaskatGAZelleParametersProvider.MinRecalculatedDistanceForPremiumRaskatGAZelle &&
routeList.Car.IsRaskat &&
routeList.Car.TypeOfUse == CarTypeOfUse.DriverCar &&
routeList.Car.RaskatType == RaskatType.RaskatGazelle)
{
RouteListItem routeListAdressesAlias = null;
Order orderAlias = null;
DeliveryPoint deliveryPointAlias = null;
District districtAlias = null;
PremiumItem premiumItemAlias = null;
PremiumRaskatGAZelle premiumRaskatGAZelleAlias = null;
// Ищем премию
var premiumRaskatGAZelleQuery = uow.Session.QueryOver(() => premiumItemAlias)
.JoinAlias(() => premiumItemAlias.Premium, () => premiumRaskatGAZelleAlias)
.Where(() =>
( // Если МЛ переоткрыли в другой день и повторно его закрывают
(premiumRaskatGAZelleAlias.RouteList.Id == routeList.Id) ||
// Если на дату закрытия у водителя уже есть премии
(premiumRaskatGAZelleAlias.Date == DateTime.Today && premiumItemAlias.Employee == routeList.Driver)
) &&
premiumRaskatGAZelleAlias.GetType() == typeof(PremiumRaskatGAZelle)
)
.Take(1).SingleOrDefault();
// Ищем заказ в пригороде
var wageDistrictQuery = uow.Session.QueryOver(() => routeListAdressesAlias)
.JoinAlias(() => routeListAdressesAlias.Order, () => orderAlias)
.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.JoinAlias(() => deliveryPointAlias.District, () => districtAlias)
.Where(() => districtAlias.WageDistrict.Id == wageParametersProvider.GetSuburbWageDistrictId &&
routeListAdressesAlias.RouteList.Id == routeList.Id)
.Take(1).SingleOrDefault();
return(premiumRaskatGAZelleQuery == null && wageDistrictQuery != null);
}
else
{
return(false);
}
}
public Boolean CheckBeans(PremiumShop.User user, PremiumItem premiumItem) //check if beans amount is more than the amount of beans the item is when they are purchasing
{
double userBeans = user.beansAmount;
double itemPrice = premiumItem.beansPrice;
Boolean success = false;
if (userBeans < itemPrice)
{
success = false;
}
else if (userBeans > itemPrice)
{
success = true;
}
else
{
success = false;
}
return(success);
}
public void UpdatePremiumRaskatGAZelle(IUnitOfWork uow)
{
if (!NeedPremiumRaskatGAZelleInRouteListDate(uow))
{
return;
}
PremiumRaskatGAZelle premiumRaskatGAZelle = new PremiumRaskatGAZelle()
{
PremiumReasonString = $"Автопремия для раскатных газелей МЛ №{routeList.Id.ToString()}",
Author = employeeRepository.GetEmployeeForCurrentUser(uow),
Date = DateTime.Today,
TotalMoney = premiumRaskatGAZelleParametersProvider.PremiumRaskatGAZelleMoney,
RouteList = routeList
};
uow.Save(premiumRaskatGAZelle);
WagesMovementOperations operation = new WagesMovementOperations
{
OperationType = WagesType.PremiumWage,
Employee = routeList.Driver,
Money = premiumRaskatGAZelleParametersProvider.PremiumRaskatGAZelleMoney,
OperationTime = DateTime.Today
};
uow.Save(operation);
PremiumItem premiumItem = new PremiumItem()
{
Premium = premiumRaskatGAZelle,
Employee = routeList.Driver,
Money = premiumRaskatGAZelleParametersProvider.PremiumRaskatGAZelleMoney,
WageOperation = operation
};
uow.Save(premiumItem);
}
public ActionResult ItemDelete(PremiumItem item)
{
#region role and is logged in
if (Session["uname"] == null || Session["uname"].ToString() == "")
{
return(RedirectToAction("Login", "User"));
}
if (Session["role"].ToString() != "Admin")
{
return(RedirectToAction("Index", "Unauthorised"));
}
#endregion
Database d = Database.CurrentInstance;
try
{
if (d.OpenConnection())
{
string SearchQuery = "DELETE FROM dububase.premiumitem WHERE itemName= @itemName;";
MySqlCommand c = new MySqlCommand(SearchQuery, d.conn);
c.Parameters.AddWithValue("@itemName", item.itemName);
c.ExecuteNonQuery();
return(RedirectToAction("ItemManager"));
}
}
catch (MySqlException e)
{
Debug.WriteLine("MySQL Error!");
}
finally
{
d.CloseConnection();
}
return(View());
}
public void AddPremiumItem(PremiumItem pItem)
{
pItem.AddToDatabase();
this.PremiumItems[pItem.PageID].Add(pItem);
}
public void RemovePremiumItem(PremiumItem pItem)
{
try
{
locker.WaitOne();
this.PremiumItems[pItem.PageID].Remove(pItem);
}
finally
{
locker.ReleaseMutex();
}
}
public void AddPremiumItem(PremiumItem pItem)
{
pItem.AddToDatabase();
PremiumItems[pItem.PageID].Add(pItem);
}
public ActionResult Inventory()
{
Database d = Database.CurrentInstance;
List <int> itemIDs = new List <int>();
int userID = Convert.ToInt32(Session["UserID"]);
List <PremiumItem> HatItems = new List <PremiumItem>();
List <PremiumItem> OutfitItems = new List <PremiumItem>();
EquippedItem equipment = new EquippedItem();
int userBeans = 0;
string username = "";
try
{
if (d.OpenConnection())
{
string inventoryQuery = "SELECT * FROM inventory where userID = @userID";
MySqlCommand c = new MySqlCommand(inventoryQuery, d.conn);
c.Parameters.AddWithValue("@userID", userID);
using (MySqlDataReader r = c.ExecuteReader())
{
while (r.Read())
{
itemIDs.Add(Convert.ToInt32(r["itemID"]));
}
r.Close();
}
for (int i = 0; i < itemIDs.Count(); i++)
{
MySqlCommand c2 = new MySqlCommand("select * from premiumitem where itemID = @itemID", d.conn);
c2.Parameters.AddWithValue("@itemID", itemIDs[i]);
MySqlDataReader reader = c2.ExecuteReader();
while (reader.Read())
{
if (reader["itemType"].ToString() == "Hat")
{
PremiumItem HatItem = new PremiumItem
{
itemName = (reader["itemName"].ToString()),
itemDescription = (reader["itemDescription"].ToString()),
itemID = (reader["itemID"].ToString())
};
HatItems.Add(HatItem);
}
if (reader["itemType"].ToString() == "Outfit")
{
PremiumItem OutfitItem = new PremiumItem
{
itemName = (reader["itemName"].ToString()),
itemDescription = (reader["itemDescription"].ToString()),
itemID = (reader["itemID"].ToString())
};
OutfitItems.Add(OutfitItem);
}
}
reader.Close();
}
ViewBag.OwnedOutfitItemData = OutfitItems;
ViewBag.OwnedHatItemData = HatItems;
MySqlCommand c3 = new MySqlCommand("SELECT * FROM equippeditems WHERE userID = @userID", d.conn);
c3.Parameters.AddWithValue("@userID", userID);
using (MySqlDataReader r2 = c3.ExecuteReader())
{
while (r2.Read())
{
if (r2["userID"] != DBNull.Value)
{
if (r2["equippedHat"] != DBNull.Value)
{
equipment.equippedHat = Convert.ToInt32(r2["equippedHat"]);
}
if (r2["equippedOutfit"] != DBNull.Value)
{
equipment.equippedOutfit = Convert.ToInt32(r2["equippedOutfit"]);
}
}
else
{
break;
}
}
ViewBag.EquipmentData = equipment;
r2.Close();
}
MySqlCommand c4 = new MySqlCommand("SELECT * FROM users where userID = @userID", d.conn);
c4.Parameters.AddWithValue("@userID", userID);
using (MySqlDataReader r3 = c4.ExecuteReader())
{
while (r3.Read())
{
userBeans = Convert.ToInt32(r3["beansAmount"]);
username = r3["userName"].ToString();
}
r3.Close();
}
Session["userBeans"] = userBeans;
Session["username"] = username;
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
return(RedirectToAction("FailureView"));
}
finally
{
d.CloseConnection();
}
return(View());
}
public ActionResult Shop()
{
//Initialize Database Instance
Database d = Database.CurrentInstance;
//Initialize UserID
int userID = Convert.ToInt32(Session["userID"].ToString());
//Create list for storing HatItems, OutfitItems, UserItems to pass to shop via ViewBag
List <PremiumItem> HatItems = new List <PremiumItem>();
List <PremiumItem> OutfitItems = new List <PremiumItem>();
List <PremiumItem> UserItems = new List <PremiumItem>();
int userBeans = 0;
string username = "";
try
{
if (d.OpenConnection())
{
string itemQuery = "SELECT * FROM premiumitem";
MySqlCommand c = new MySqlCommand(itemQuery, d.conn);
using (MySqlDataReader r = c.ExecuteReader())
{
while (r.Read())
{
//If ItemType is Hat pass it into HatItems List
if (r["itemType"].ToString().Equals("Hat"))
{
PremiumItem HatItem = new PremiumItem
{
itemID = (r["itemID"].ToString()),
itemName = (r["itemName"].ToString()),
itemDescription = (r["itemDescription"].ToString()),
beansPrice = Convert.ToInt32(r["beansPrice"])
};
HatItems.Add(HatItem);
ViewBag.HatItemData = HatItems;
}
//If ItemType is Outfit pass it into OutfitItems List
else if (r["itemType"].ToString().Equals("Outfit"))
{
PremiumItem OutfitItem = new PremiumItem
{
itemID = (r["itemID"].ToString()),
itemName = (r["itemName"].ToString()),
itemDescription = (r["itemDescription"].ToString()),
beansPrice = Convert.ToInt32(r["beansPrice"])
};
OutfitItems.Add(OutfitItem);
ViewBag.OutfitItemData = OutfitItems;
}
}
r.Close();
}
MySqlCommand c2 = new MySqlCommand("SELECT * FROM inventory WHERE userID = @userID", d.conn);
c2.Parameters.AddWithValue("@userID", userID);
using (MySqlDataReader r2 = c2.ExecuteReader())
{
while (r2.Read())
{
PremiumItem UserItem = new PremiumItem
{
itemID = (r2["itemID"].ToString()),
};
UserItems.Add(UserItem);
}
r2.Close();
}
ViewBag.UserItemsData = UserItems;
MySqlCommand c3 = new MySqlCommand("SELECT * FROM users where userName = @userName", d.conn);
c3.Parameters.AddWithValue("@userName", Session["uname"].ToString());
using (MySqlDataReader r3 = c3.ExecuteReader())
{
while (r3.Read())
{
userBeans = Convert.ToInt32(r3["beansAmount"]);
username = r3["userName"].ToString();
}
r3.Close();
}
Session["userBeans"] = userBeans;
Session["username"] = username;
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
return(RedirectToAction("FailureView"));
}
finally
{
d.CloseConnection();
}
return(View());
}
public ActionResult ItemAdd(PremiumItem item)
{
#region role and is logged in
if (Session["uname"] == null || Session["uname"].ToString() == "")
{
return(RedirectToAction("Login", "User"));
}
if (Session["role"].ToString() != "Admin")
{
return(RedirectToAction("Index", "Unauthorised"));
}
#endregion
Database d = Database.CurrentInstance;
try
{
if (d.OpenConnection())
{
string SearchQuery = "Select * From dububase.premiumitem where itemName = @item";
MySqlCommand c = new MySqlCommand(SearchQuery, d.conn);
c.Parameters.AddWithValue("@item", item.itemName);
int itemExist = 0;
using (MySqlDataReader r = c.ExecuteReader())
{
if (r.Read())
{
itemExist = 1;
}
}
Debug.WriteLine(item.itemType);
Debug.WriteLine(itemExist);
if (itemExist != 1)
{
SearchQuery = "INSERT INTO dububase.premiumitem(itemName,itemType,itemDescription,beansPrice) VALUES(@name,@itemType,@itemDescription,@beansPrice);";
c = new MySqlCommand(SearchQuery, d.conn);
c.Parameters.AddWithValue("@name", item.itemName);
c.Parameters.AddWithValue("@itemType", item.itemType);
c.Parameters.AddWithValue("@itemDescription", item.itemDescription);
c.Parameters.AddWithValue("@beansPrice", item.priceOfBeans);
//c.Parameters.AddWithValue("@dateStart", item.dateStart);
//c.Parameters.AddWithValue("@dateEnd", item.dateEnd);
//ALEX
//c.Parameters.AddWithValue("@itemImage", item.itemIamge);
c.ExecuteNonQuery();
}
else
{
ViewBag.Message = "Item Name Already Exists";
return(View());
}
}
}
catch (MySqlException e)
{
Debug.WriteLine(e);
}
finally
{
d.CloseConnection();
}
return(RedirectToAction("ItemManager"));
}
