public void CreateUsedInfo(FormCollection collection)
{
var msg = new Msg();
var Db = new Useds_total().Db;
try
{
var pid = Convert.ToInt32(collection["pid"]);
var cold_water_value = Convert.ToSingle(collection["cold_water_value"]);
var hot_water_value = Convert.ToSingle(collection["hot_water_value"]);
var electric_value = Convert.ToSingle(collection["electric_value"]);
var Note = collection["note"];
///1.1判断输入数值
if (cold_water_value < 0 || hot_water_value < 0 || electric_value < 0)
{
throw new Exception("数值输入有误，读表数值不能小于0");
}
else
{
var usedinfo = Db.Queryable <T_Used_total>().Single(u => u.Ut_model_state && pid == u.Ut_room_id);
if (usedinfo == null)//如果读表数值为null，新建一个读表数值对象
{
usedinfo = new T_Used_total();
var room = Db.Queryable <Entity.T_Room>().Single(x => x.Room_id == pid && x.Room_model_state);
if (room == null)
{
throw new Exception("该宿舍不存在,id:" + pid);
}
usedinfo.Ut_dorm_id = room.Room_dorm_id;
usedinfo.Ut_building_id = room.Room_building_id;
usedinfo.Ut_room_id = pid;
}
usedinfo.Ut_note = Note;
usedinfo.Ut_hot_water_value = hot_water_value;
usedinfo.Ut_cold_water_value = cold_water_value;
usedinfo.Ut_electric_value = electric_value;
usedinfo.Ut_post_date = DateTime.Now;
///1.2保存读表信息
Db.Ado.BeginTran(); //开始事务
if (usedinfo.Ut_id < 1) // 如果是新增的读表记录，就执行插入操作
{
Db.Insertable(usedinfo).ExecuteCommand();
//throw new Exception("插入读表记录时发生错误");
}
else if (Db.Updateable(usedinfo).ExecuteCommand() < 1)
{
throw new Exception("更新读表记录时发生错误"); // 否则执行更新操作
}
Db.Ado.CommitTran(); // 提交事务
msg.Message = "保存成功";
}
}
catch (Exception ex)
{
Db.Ado.RollbackTran();//发生错误，回滚操作
msg.Code = -1;
msg.Message = "添加读表信息时发生错误：" + ex.Message;
}
Response.Write(msg.ToJson());
Response.End();
}
public void Create(FormCollection collection)
{
var msg = new Msg();
var Db = new DbContext().Db;
try
{
var rate = new Rates().GetLast();
// 验证费率信息
if (rate == null)
{
throw new Exception("请先到'系统设置-费率及基础配额设置'设置费率信息");
}
// 验证基础配额信息
var quota = new Quotas().GetLast();
if (quota == null)
{
throw new Exception("请先到'系统设置-费率及基础配额设置'设置基础配额信息");
}
///1.获取数据
var cold_water_value = Convert.ToSingle(collection["cold_water_value"]);
var hot_water_value = Convert.ToSingle(collection["hot_water_value"]);
var electric_value = Convert.ToSingle(collection["electric_value"]);
///1.1判断输入数值
if (cold_water_value < 0 || hot_water_value < 0 || electric_value < 0 || cold_water_value > 9999999 || hot_water_value > 9999999 || electric_value > 9999999)
{
throw new Exception("数值输入有误，读表数值应在0~999999之间");
}
var Pid = Convert.ToInt32(collection["pid"]); // 宿舍ID
var Note = collection["note"];
if (Pid < 0 || Pid > 99999999)
{
throw new Exception("宿舍ID输入有误，应在1~99999999之间");
}
///1.2获取宿舍信息
var room = Db.Queryable <T_Room>().Single(x => x.Room_id == Pid && x.Room_model_state);
if (room == null)
{
throw new Exception("该宿舍不存在");
}
if (room.Number < 1)
{
throw new Exception("该宿舍无人居住，无需登记");
}
///1.3判断该宿舍是否已登记, 避免重复操作
if (new DbHelper.Useds().IsRecord(Pid))
{
throw new Exception("该宿舍本月已经登记过了，无需再次登记");
}
///2.获取上次读数
var this_cold_water_value = cold_water_value;
var this_hot_water_value = hot_water_value;
var this_electric_value = electric_value;
///3.计算本次用量
var usedinfo = Db.Queryable <T_Used_total>().Single(x => x.Ut_room_id == Pid && x.Ut_model_state);
if (usedinfo != null)
{
///3.1判断本次数值是否大于等于上次数值
if (this_cold_water_value < usedinfo.Ut_cold_water_value || this_hot_water_value < usedinfo.Ut_hot_water_value || this_electric_value < usedinfo.Ut_electric_value)
{
throw new Exception("数值输入有误，本期水表电表数值应大于等于上期读表数值");
}
// 本次数值=本次读数-上次读数
this_cold_water_value -= usedinfo.Ut_cold_water_value;
this_hot_water_value -= usedinfo.Ut_hot_water_value;
this_electric_value -= usedinfo.Ut_electric_value;
}
else
{
usedinfo = new T_Used_total();
}
usedinfo.Ut_dorm_id = room.Room_dorm_id;
usedinfo.Ut_building_id = room.Room_building_id;
usedinfo.Ut_room_id = Pid;
usedinfo.Ut_hot_water_value = hot_water_value;
usedinfo.Ut_cold_water_value = cold_water_value;
usedinfo.Ut_electric_value = electric_value;
///3.1生成用量单
var used = new T_Used()
{
Used_electric_value = this_electric_value,
Used_hot_water_value = this_hot_water_value,
Used_cold_water_value = this_cold_water_value,
Used_building_id = room.Room_building_id,
Used_dorm_id = room.Room_dorm_id,
Used_note = Note,
Used_room_id = Pid,
Used_post_user_id = (int)Session["id"],
};
///3.2扣除基础配额数据，最终使用量 =（本次读表数值-上次读表数值）-（基础配额*人数）
if (quota != null && quota.Quota_is_active)
{
this_cold_water_value -= quota.Quota_cold_water_value * room.Number;
this_hot_water_value -= quota.Quota_hot_water_value * room.Number;
this_electric_value -= quota.Quota_electric_value * room.Number;
}
Db.Ado.BeginTran(); // 开始事务
///3.3保存用量单
var uid = Db.Insertable(used).ExecuteReturnEntity();
if (uid.Used_id < 1)// 插入并更新自增ID
{
throw new Exception("保存登记信息时发生错误！");
}
///5.计算本次费用并生成账单，费用 = 最终使用量*费率，（无阶梯计费）
var bill = new T_Bill();
bill.Bill_used_id = uid.Used_id;
bill.Bill_room_id = room.Room_id;
bill.Bill_building_id = room.Room_building_id;
bill.Bill_dorm_id = room.Room_dorm_id;
///6.超过基础配额的才计费
if (this_cold_water_value > 0)
{ // 冷水费
bill.Bill_cold_water_cost = (decimal)this_cold_water_value * (decimal)rate.Rate_cold_water_value;
}
if (this_hot_water_value > 0)
{ // 热水费
bill.Bill_hot_water_cost = (decimal)this_hot_water_value * (decimal)rate.Rate_hot_water_value;
}
if (this_electric_value > 0)
{ // 电费
bill.Bill_electric_cost = (decimal)this_electric_value * (decimal)rate.Rate_electric_value;
}
bill.Bill_rates_id = rate.Rate_id;
bill.Bill_quota_id = quota.Quota_id;
///7.保存所有数据
///7.1保存读表信息
if (usedinfo.Ut_id < 1)
{
if (Db.Insertable(usedinfo).ExecuteCommand() < 1)
{
throw new Exception("保存读表信息时发生错误！");
}
}
else if (Db.Updateable(usedinfo).ExecuteCommand() < 1)
{
throw new Exception("更新读表信息时发生错误！");
}
///7.2保存账单
if (Db.Insertable(bill).ExecuteCommand() < 1)
{
throw new Exception("添加账单信息时发生错误！");
}
Db.Ado.CommitTran();// 提交事务
msg.Message = "登记成功！";
}
catch (Exception ex)
{
//发生错误，回滚事务
Db.Ado.RollbackTran();
msg.Code = -1;
msg.Message = ex.Message;
}
Response.Write(msg.ToJson());
Response.End();
}
public void BillDelete(int id)
{
/// 开始事务
Db.Ado.BeginTran();
try
{
///1.1获取记录数据
var used = Db.Context.Queryable <Entity.T_Used>().Single(u => u.Used_model_state && u.Used_id == id);
if (used == null)
{
throw new Exception("该记录已被删除");
}
///2.判断账单状态
var bill = Db.Ado.Context.Queryable <Entity.T_Bill>().Single(b => b.Bill_model_state && b.Bill_used_id == used.Used_id);
if (bill != null && bill.Bill_is_active != 1)
{
throw new Exception("关联账单的状态已被更改，无法删除");
}
else if (bill != null)
{
bill.Bill_model_state = false; // 标记账单为已删除
if (!Db.Updateable(bill).ExecuteCommandHasChange())
{
throw new Exception("删除关联账单时发生错误");
}
}
///3.更新读表信息
var Used_total = new Useds_total();
var last = Used_total.Last(used.Used_room_id);
if (last != null)
{
// 读表数值=读表数值-本次读数
last.Ut_cold_water_value -= used.Used_cold_water_value;
last.Ut_hot_water_value -= used.Used_hot_water_value;
last.Ut_electric_value -= used.Used_electric_value;
/// 避免产生负数
last.Ut_cold_water_value = last.Ut_cold_water_value >= 0 ? last.Ut_cold_water_value : 0;
last.Ut_hot_water_value = used.Used_hot_water_value >= 0 ? last.Ut_hot_water_value : 0;
last.Ut_electric_value = used.Used_electric_value >= 0 ? last.Ut_electric_value : 0;
}
else
{
last = new T_Used_total();
last.Ut_dorm_id = used.Used_dorm_id;
last.Ut_building_id = used.Used_building_id;
last.Ut_room_id = used.Used_room_id;
}
last.Ut_post_date = DateTime.Now;
///4保存读表信息
if (last.Ut_id < 1)
{
if (Db.Insertable(last).ExecuteCommand() < 1)
{
throw new Exception("更新读表信息时发生错误！");
}
}
else if (Db.Updateable(last).ExecuteCommand() < 1)
{
throw new Exception("更新读表信息时发生错误！");
}
used.Used_model_state = false; // 标记为已删除
if (Db.Ado.Context.Updateable(used).ExecuteCommandHasChange())
{
Db.Ado.CommitTran();// 提交事务
}
else
{
throw new Exception("发生未知错误");
}
}
catch (Exception)
{
Db.Ado.RollbackTran();
throw;
}
}
