public static void deleteGoodsOrder(ulong orderId)
{
DBHelper dbHelper = new DBHelper();
try
{
string queryInfo = "SELECT * FROM GOODS_ORDER WHERE ID =:orderId";
string delete = "DELETE FROM GOODS_ORDER WHERE ID =:orderId";
string updateGoods = "UPDATE SELLER_GOODS SET AVAILABLE = AVAILABLE+1 WHERE SELLER_ID =:sellerId AND GOODS_ID =:goodsId";
string updateEarning = "UPDATE SELLER SET EARNING=EARNING-:money WHERE ID=:sellerId";
OracleParameter[] parameterForOrder = { new OracleParameter(":orderId", OracleDbType.Long, 20) };
parameterForOrder[0].Value = orderId;
// 保存订单信息
DataTable dt = dbHelper.ExecuteTable(queryInfo, parameterForOrder);
// 删除订单
dbHelper.ExecuteNonQuery(delete, parameterForOrder);
// 存货更新
OracleParameter[] parametersForGoods =
{
new OracleParameter(":sellerId", OracleDbType.Long, 10),
new OracleParameter(":goodsId", OracleDbType.Long, 10)
};
parametersForGoods[0].Value = long.Parse(dt.Rows[0]["SELLER_ID"].ToString());
parametersForGoods[1].Value = long.Parse(dt.Rows[0]["GOODS_ID"].ToString());
dbHelper.ExecuteNonQuery(updateGoods, parametersForGoods);
// 收入更新
OracleParameter[] parametersForEarning =
{
new OracleParameter(":money", OracleDbType.Double),
new OracleParameter(":sellerId", OracleDbType.Long, 10)
};
double money = double.Parse(dt.Rows[0]["PRICE"].ToString());
parametersForEarning[0].Value = money;
parametersForEarning[1].Value = long.Parse(dt.Rows[0]["SELLER_ID"].ToString());
dbHelper.ExecuteNonQuery(updateEarning, parametersForEarning);
// 积分更新
long customerId = long.Parse(dt.Rows[0]["CUSTOMER_ID"].ToString());
VIP check = VipController.checkVip(customerId);
if (check != null)
{
VipController.updateVip(customerId, -money);
}
return;
}
catch (OracleException)
{
throw;
}
}
public static void deleteShowOrder(ulong orderId)
{
DBHelper dbHelper = new DBHelper();
try
{
// 用到的SQL语句集合
string queryInfo = "SELECT * FROM SHOW_ORDER WHERE ID=:orderId";
string querySeller = "SELECT SELLER_ID FROM SLOT WHERE ID =:slotId";
string delete = "DELETE FROM SHOW_ORDER WHERE ID =:orderId";
string updateArea = "UPDATE AREA SET AVAILABLE = AVAILABLE+1 WHERE SLOT_ID=:slotId AND AREA_NAME =:area";
string updateSeat = "UPDATE SEAT SET IS_AVAILABLE = 1 WHERE SLOT_ID = :slotId AND AREA=:area AND SEAT_NUMBER =:seatNumber";
string updateEarning = "UPDATE SELLER SET EARNING = EARNING-:money WHERE ID = :sellerId";
// 保存订单信息
OracleParameter[] parameterForOrder = { new OracleParameter(":orderId", OracleDbType.Long, 20) };
parameterForOrder[0].Value = orderId;
DataTable dtOrder = dbHelper.ExecuteTable(queryInfo, parameterForOrder);
// 查询商家ID
OracleParameter[] parameterForSeller = { new OracleParameter(":slotId", OracleDbType.Long, 20) };
parameterForSeller[0].Value = ulong.Parse(dtOrder.Rows[0]["SLOT_ID"].ToString());
DataTable dtSeller = dbHelper.ExecuteTable(querySeller, parameterForSeller);
// 删除订单信息
dbHelper.ExecuteNonQuery(delete, parameterForOrder);
// 更新分区信息
OracleParameter[] parametersForUpdateArea =
{
new OracleParameter(":slotId", OracleDbType.Long, 20),
new OracleParameter(":area", OracleDbType.Varchar2, 50)
};
parametersForUpdateArea[0].Value = ulong.Parse(dtOrder.Rows[0]["SLOT_ID"].ToString());
parametersForUpdateArea[1].Value = dtOrder.Rows[0]["AREA"].ToString();
dbHelper.ExecuteNonQuery(updateArea, parametersForUpdateArea);
// 更新座位信息
OracleParameter[] parametersForUpdateSeat =
{
new OracleParameter(":slotId", OracleDbType.Long, 20),
new OracleParameter(":area", OracleDbType.Varchar2, 50),
new OracleParameter(":seatNumber", OracleDbType.Long, 10),
};
parametersForUpdateSeat[0].Value = ulong.Parse(dtOrder.Rows[0]["SLOT_ID"].ToString());
parametersForUpdateSeat[1].Value = dtOrder.Rows[0]["AREA"].ToString();
parametersForUpdateSeat[2].Value = long.Parse(dtOrder.Rows[0]["SEAT"].ToString());
dbHelper.ExecuteNonQuery(updateSeat, parametersForUpdateSeat);
// 更新收入信息
double money = double.Parse(dtOrder.Rows[0]["PRICE"].ToString());
OracleParameter[] parametersForUpdateEarning =
{
new OracleParameter(":money", OracleDbType.Double),
new OracleParameter(":sellerId", OracleDbType.Long)
};
parametersForUpdateEarning[0].Value = money;
parametersForUpdateEarning[1].Value = long.Parse(dtSeller.Rows[0]["SELLER_ID"].ToString());
dbHelper.ExecuteNonQuery(updateEarning, parametersForUpdateEarning);
// 更新积分
long customerId = long.Parse(dtOrder.Rows[0]["CUSTOMER_ID"].ToString());
VIP check = VipController.checkVip(customerId);
if (check != null)
{
VipController.updateVip(customerId, -money);
}
return;
}
catch (OracleException)
{
throw;
}
}
public IActionResult newGoodsOrder(GoodsOrder order)
{
DBHelper dbHelper = new DBHelper();
try
{
// 检查顾客是否为VIP
VIP check = VipController.checkVip(order.customerId);
// 添加订单
string insert = "INSERT INTO GOODS_ORDER VALUES(:id,:customerId,:sellerId,:goodsName,:price,:payTime,:goodsId)";
// 返回信息（购买件数以及积分增加）
long res = order.number;
double point = 0;
order.price *= (check == null ? 1 : check.discount);
for (long i = 0; i < order.number; i++)
{
OracleParameter[] parametersForInsert =
{
new OracleParameter(":id", OracleDbType.Varchar2, 50),
new OracleParameter(":customerId", OracleDbType.Long, 10),
new OracleParameter(":sellerId", OracleDbType.Long, 10),
new OracleParameter(":goodsName", OracleDbType.Varchar2, 50),
new OracleParameter(":price", OracleDbType.Double),
new OracleParameter(":payTime", OracleDbType.Varchar2, 50),
new OracleParameter(":goodsId", OracleDbType.Long, 10)
};
parametersForInsert[0].Value = dbHelper.ExecuteMax("GOODS_ORDER") + 1;
parametersForInsert[1].Value = order.customerId;
parametersForInsert[2].Value = order.sellerId;
parametersForInsert[3].Value = order.goodsName;
parametersForInsert[4].Value = order.price;
parametersForInsert[5].Value = DateTime.Now.ToLocalTime().ToString("G");
parametersForInsert[6].Value = order.goodsId;
dbHelper.ExecuteNonQuery(insert, parametersForInsert);
}
point += (order.price * order.number);
++res;
// 卖（存货、收入数据更新）
// 存货更新
string updateGoods = "UPDATE SELLER_GOODS SET AVAILABLE = AVAILABLE-:orders WHERE SELLER_ID =:sellerId AND GOODS_ID =:goodsId";
OracleParameter[] parametersForUpdateGoods =
{
new OracleParameter(":orders", OracleDbType.Long, 10),
new OracleParameter(":sellerId", OracleDbType.Long, 10),
new OracleParameter(":goodsId", OracleDbType.Long, 10)
};
parametersForUpdateGoods[0].Value = order.number;
parametersForUpdateGoods[1].Value = order.sellerId;
parametersForUpdateGoods[2].Value = order.goodsId;
dbHelper.ExecuteNonQuery(updateGoods, parametersForUpdateGoods);
// 收入更新
string updateEarning = "UPDATE SELLER SET EARNING = EARNING+:money WHERE ID =:sellerId";
OracleParameter[] parametersForUpdateEarning =
{
new OracleParameter(":money", OracleDbType.Double),
new OracleParameter(":sellerId", OracleDbType.Long, 10)
};
parametersForUpdateEarning[0].Value = order.price * order.number;
parametersForUpdateEarning[1].Value = order.sellerId;
dbHelper.ExecuteNonQuery(updateEarning, parametersForUpdateEarning);
// 积分
if (check == null)
{
return(Ok("购买成功,已购" + res.ToString() + "件商品"));
}
else
{
VipController.updateVip(order.customerId, point);
return(Ok("购买成功,已购" + res.ToString() + "件商品\n" + "积分增加" + point.ToString()));
}
}
catch (OracleException oe)
{
return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString()));
}
catch (Exception)
{
return(BadRequest("未知错误"));
}
}
public IActionResult newShowOrder(ShowOrder[] orders)
{
DBHelper dbHelper = new DBHelper();
try
{
// 检查顾客是否为VIP
VIP check = VipController.checkVip(orders[0].customerId);
// 添加订单
string insert = "INSERT INTO SHOW_ORDER VALUES(:id,:customerId,:slotId,:area,:seat,:price,:payTime,:name)";
// 更改分区信息
string updateArea = "UPDATE AREA SET AVAILABLE= AVAILABLE-1 WHERE SLOT_ID = :slotId AND AREA_NAME=:area";
// 更改座位信息
string updateSeat = "UPDATE SEAT SET IS_AVAILABLE = 0 WHERE SLOT_ID = :slotId AND AREA=:area AND SEAT_NUMBER =:seatNumber";
// 更改收入信息
string updateEarning = "UPDATE SELLER SET EARNING = EARNING+:money WHERE ID = :sellerId";
// 返回信息（购买件数以及积分增加）
int res = 0;
double point = 0;
foreach (ShowOrder order in orders)
{
// 买（订单添加）
order.price *= (check == null ? 1 : check.discount);
OracleParameter[] parametersForInsert =
{
new OracleParameter(":id", OracleDbType.Long, 20),
new OracleParameter(":customerId", OracleDbType.Long, 10),
new OracleParameter(":slotId", OracleDbType.Long, 10),
new OracleParameter(":area", OracleDbType.Varchar2, 50),
new OracleParameter(":seat", OracleDbType.Long, 10),
new OracleParameter(":price", OracleDbType.Double),
new OracleParameter(":payTime", OracleDbType.Varchar2, 50),
new OracleParameter(":name", OracleDbType.Varchar2, 100)
};
parametersForInsert[0].Value = dbHelper.ExecuteMax("SHOW_ORDER") + 1;
parametersForInsert[1].Value = order.customerId;
parametersForInsert[2].Value = order.slotId;
parametersForInsert[3].Value = order.areaName;
parametersForInsert[4].Value = order.seatNumber;
parametersForInsert[5].Value = order.price;
parametersForInsert[6].Value = DateTime.Now.ToLocalTime().ToString("G");
parametersForInsert[7].Value = order.name;
dbHelper.ExecuteNonQuery(insert, parametersForInsert);
++res;
point += order.price;
// 存货信息修改
// 分区信息修改
OracleParameter[] parametersForUpdateArea =
{
new OracleParameter(":slotId", OracleDbType.Long, 10),
new OracleParameter(":area", OracleDbType.Varchar2, 50)
};
parametersForUpdateArea[0].Value = order.slotId;
parametersForUpdateArea[1].Value = order.areaName;
dbHelper.ExecuteNonQuery(updateArea, parametersForUpdateArea);
// 座位信息修改
OracleParameter[] parametersForUpdateSeat =
{
new OracleParameter(":slotId", OracleDbType.Long, 10),
new OracleParameter(":area", OracleDbType.Varchar2, 50),
new OracleParameter(":seatNumber", OracleDbType.Long, 10),
};
parametersForUpdateSeat[0].Value = order.slotId;
parametersForUpdateSeat[1].Value = order.areaName;
parametersForUpdateSeat[2].Value = order.seatNumber;
dbHelper.ExecuteNonQuery(updateSeat, parametersForUpdateSeat);
// 收入信息修改
OracleParameter[] parametersForUpdateEarning =
{
new OracleParameter(":money", OracleDbType.Double),
new OracleParameter(":sellerId", OracleDbType.Long, 10)
};
parametersForUpdateEarning[0].Value = order.price;
parametersForUpdateEarning[1].Value = order.sellerId;
dbHelper.ExecuteNonQuery(updateEarning, parametersForUpdateEarning);
}
// 积分更新
if (check == null)
{
return(Ok("购买成功,已购" + res.ToString() + "件商品"));
}
else
{
VipController.updateVip(orders[0].customerId, point);
return(Ok("购买成功,已购" + res.ToString() + "件商品\n" + "积分增加" + point.ToString()));
}
}
catch (OracleException oe)
{
return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString()));
}
}
