/// <summary>
/// Deletes a Shoplist item from the ShopListItem table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="item"></param>
/// <returns>
/// Returns a Tuple<bool, string> with a processing message.
/// </returns>
public static Tuple <bool, string> DeleteShopListItem(ShopList shopList, ShopListItem item)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString()))
{
try
{
if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item))
{
connection.Execute($"DELETE FROM ShopListItem WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}");
result = new Tuple <bool, string>(true, $"ShopList item {item.ProductName}({item.ShopListProductID}) succesvol verwijderd.");
}
else
{
result = new Tuple <bool, string>(false, $"ShopList item {item.ProductName}({item.ShopListProductID}) bestaat niet.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.DeleteShopListItem: Error logging naar log.db
throw;
}
}
return(result);
}
public ShopListItem GetShopListItem(Product product, ShoppingCart cart)
{
var shopListItem = new ShopListItem()
{
Product = product,
IsAddedToCart = IsAddedToCart(product, cart)
};
return(shopListItem);
}
IEnumerator GetDataShopNetwork(WWW www, int index)
{
//Wait for request to complete
yield return(www);
if (www.error == null)
{
jsonData = www.text;
Debug.Log(jsonData);
//Data is in json format, we need to parse the Json.
JSONObject jsonObjectShopNetwork = JSONObject.Parse(jsonData);
JSONObject jsonObjectShop = JSONObject.Parse(jsonDataObjects[index]);
if (jsonObjectShopNetwork == null)
{
Debug.Log("No data converted");
}
else
{
switch (jsonObjectShopNetwork["name"].Str)
{
default:
foundImage.Image = spriteColruyt;
break;
}
//now we can get the values from json of any attribute.
foundShopNetwork.Id = Convert.ToInt32(jsonObjectShopNetwork["id"].Number);
foundShopNetwork.Name = jsonObjectShopNetwork["name"].Str;
foundShopNetwork.Image = foundImage;
foundShop.Id = Convert.ToInt32(jsonObjectShop["id"].Number);
foundShop.Name = jsonObjectShop["description"].Str;
foundShop.Location = jsonObjectShop["address"].Str;
foundShop.ShopNetwork = foundShopNetwork;
GameObject newShopListItem = shopObjectPool.GetObject();
newShopListItem.transform.SetParent(contentPanel);
ShopListItem shopListItem = newShopListItem.GetComponent <ShopListItem>();
shopListItem.ShowShopInfo(foundShop);
}
}
else
{
Debug.Log(www.error);
}
}
/// <summary>
/// Saves or updates a shopList item in the ShopListItem table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="item"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> SaveShopListItem(ShopList shopList, ShopListItem item)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item))
{
string updateQuery =
$"UPDATE ShopListItem SET Unit = {item.Unit}, Amount = {item.Amount}, ItemAcquired = {item.Acquired}, DateTimeModified = '{item.ITEMUPDATED}' " +
$"WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}";
connection.Execute(updateQuery);
result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is bijgewerkt.");
}
else
{
string insertQuery =
"INSERT INTO ShopListItem (ShopList_ID, ShopListProduct_ID, Unit, Amount, ItemAcquired, DateTimeAdded) " +
$"VALUES ({shopList.SHOPLISTID}, {item.ShopListProductID}, {item.Unit}, {item.Amount}, {item.Acquired}, '{item.ItemAdded}')";
connection.Execute(insertQuery);
result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is toegevoegd aan {shopList.Name}({shopList.SHOPLISTID}).");
}
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet of is nog niet opgeslagen.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.SaveShopListItem: Error logging naar log.db
throw;
}
}
return(result);
}
public static bool DoesShopListItemExist(int shopListID, ShopListItem shopListItem)
{
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString()))
{
var output = connection.FindWithQuery <ShopListItem>(
@"
SELECT
slp.ID as 'ShopListProductID',
b.BrandName as 'BrandName',
p.ProdName as 'ProductName',
b.ImgPath as 'BrandLogo',
slp.ImgPath as 'ProductImage',
(
SELECT group_concat(pc.Category,'|')
FROM ProductCategory pc
WHERE pc.Product_ID = p.ID
) as 'Categories',
sli.Amount,
sli.Unit,
sli.ItemAcquired as 'Acquired',
sli.DateTimeAdded as 'ItemAdded',
sli.DateTimeModified as 'ItemUpdated'
FROM ShopListItem as sli
JOIN ShopListProduct as slp on slp.ID = sli.ShopListProduct_ID
JOIN Products as p on p.ID = slp.Product_ID
JOIN Brands as b on b.ID = slp.Brand_ID
JOIN ProductCategory as pc on pc.Product_ID = p.ID " +
$"WHERE sli.ShopList_ID = {shopListID} AND ShopListProduct_ID = {shopListItem.ShopListProductID} GROUP BY slp.ID"
);
if (output != null)
{
return(true);
}
}
return(false);
}
/// <summary>
/// 初始化函数,加载文件路径
/// </summary>
public void Init()
{
shopItemInfoList = new List <ShopItemInfo>();
shopItemList = new List <ShopListItem>();
string sql = "select * from shop_item_information";
DataTable dt = MysqlHelper.ExecuteTable(sql, CommandType.Text, null);
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
ShopItemInfo shopItemInfo = new ShopItemInfo();
shopItemInfo.ShopItemID = int.Parse(item["shop_item_id"].ToString());
shopItemInfo.ItemID = int.Parse(item["item_id"].ToString());
shopItemInfo.Count = int.Parse(item["shop_item_count"].ToString());
shopItemInfoList.Add(shopItemInfo);
}
}
for (int i = 0; i < shopItemInfoList.Count; i++)
{
ShopListItem si = (ShopListItem)UIPackage.CreateObject("ShopMenu", "ShopListItem");
si.ShopItemInfo = shopItemInfoList[i];
si.Item = ItemManager.Instance.GetItemByID(si.ShopItemInfo.ItemID);
if (si.Item != null)
{
si.SetValues();
si.AddCountButton.data = i;
si.CutCountButton.data = i;
si.BuyButton.data = i;
si.AddCountButton.onClick.Add(OnAddCountButtonDown);
si.CutCountButton.onClick.Add(OnCutCountButtonDown);
si.BuyButton.onClick.Add(OnBuyButtonDown);
}
shopItemList.Add(si);
}
}
public static void AssignMemberToItem(ShopList shopList, ShopListItem item, AppUser user)
{
// TODO : AssignMemberToItem, wanneer Task systeem is uitgedokterd...
}
internal void UpdateShopListItem(ShopListItem innerValue)
{
_ctx.Entry(innerValue).State = EntityState.Modified;
_ctx.SaveChanges();
Mediator.NotifyCollegues(PageNames.ShopListItemChanged, null);
}