public ActionResult Index()
{
RoomBill roomBill = new RoomBill
{
getRoomType = patientPortal.getRoomType(),
getAllPatientsName = patientPortal.getAllPatientsName()
};
return(View(roomBill));
}
private IEnumerable <RoomBill> GetRoomBills(IEnumerable <Bill> srcBills)
{
List <RoomBill> bills = new List <RoomBill>();
var result = from p in srcBills
group p by new
{
RoomNo = p.Device.RoomNo,
RealName = p.Device.Room.RealName,
LotNo = p.LotNo,
AccountBalance = p.AccountBalance,
DateTime = p.DateTime,
} into g
orderby g.Key.RoomNo, g.Key.DateTime descending
select new
{
RoomNo = g.Key.RoomNo,
RealName = g.Key.RealName,
LotNo = g.Key.LotNo,
AccountBalance = g.Key.AccountBalance,
DateTime = g.Key.DateTime,
//SumValue = g.Sum(p => p.CurValue - p.PreValue),
SumMoney = g.Sum(p => p.Money)
};
foreach (var item in result)
{
RoomBill bill = new RoomBill();
bill.RoomNo = item.RoomNo;
bill.LotNo = item.LotNo;
bill.RealName = item.RealName;
bill.DateTime = item.DateTime;
bill.AccountBalance = TextHelper.ConvertMoney(item.AccountBalance);
//bill.SumValue = item.SumValue;
bill.SumMoney = TextHelper.ConvertMoney(item.SumMoney);
bill.BilledAccountBalance = TextHelper.ConvertMoney(item.AccountBalance - item.SumMoney);
bill.DeviceBills = (from p in GetAll()
where p.Device.RoomNo == item.RoomNo && p.LotNo == item.LotNo
select p).AsEnumerable().Select(q => new DeviceBill
{
DeviceNo = q.DeviceNo,
DeviceName = q.Device.DeviceName,
PreValue = q.PreValue,
CurValue = q.CurValue,
Price = TextHelper.ConvertMoney(q.Device.DeviceType.Price1),
Money = TextHelper.ConvertMoney(q.Money),
Remark = q.Remark
});
bills.Add(bill);
}
return(bills);
}
public static void ExportRoomBills(IEnumerable <RoomBill> bills, string[] titles, string fileName)
{
workbook = new HSSFWorkbook();
InitializeWorkbook();
ISheet sheet = workbook.CreateSheet("能耗缴费历史账单");
//SetPrintSetting(sheet); // 设置打印格式
SetHeaderTitle(sheet, titles); // 设置标题栏
int rowIndex = 0;
for (int i = 0; i < bills.Count(); i++)
{
RoomBill bill = bills.ElementAt(i);
int deviceCount = bill.DeviceBills.Count();
IRow row = CreateCommonRow(sheet, ++rowIndex);
// 房间编号
CreateCommonCell(sheet, row, 0, bill.RoomNo);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 0, 0));
// 结算批号
CreateCommonCell(sheet, row, 1, bill.LotNo);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 1, 1));
// 业主姓名
CreateCommonCell(sheet, row, 2, bill.RealName);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 2, 2));
// 结账时间
CreateCommonCell(sheet, row, 3, bill.DateTime.ToString());
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 3, 3));
// 总费用
CreateCommonCell(sheet, row, 10, bill.SumMoney.ToString());
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 10, 10));
// 账户余额
CreateCommonCell(sheet, row, 11, bill.BilledAccountBalance);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 11, 11));
for (int j = 0; j < deviceCount; j++)
{
DeviceBill deviceBill = bill.DeviceBills.ElementAt(j);
CreateCommonCell(sheet, row, 4, deviceBill.DeviceNo);
CreateCommonCell(sheet, row, 5, deviceBill.DeviceName);
CreateCommonCell(sheet, row, 6, deviceBill.PreValue.ToString());
CreateCommonCell(sheet, row, 7, deviceBill.CurValue.ToString());
CreateCommonCell(sheet, row, 8, deviceBill.Price);
CreateCommonCell(sheet, row, 9, deviceBill.Money);
if (j != deviceCount - 1)
{
row = CreateCommonRow(sheet, ++rowIndex);
}
}
}
WriteToFile(fileName);
}
public CreateBillViewModel(Reciept reciept, IHotelRepository hotelRepository,
INotificationService notificationService)
{
HotelRepository = hotelRepository;
RoomSearchViewModel = new RoomSearchViewModel(typeof(AvailableState), HotelRepository.Rooms);
NotificationService = notificationService;
Reciept = reciept;
Bill = new RoomBill();
BillTypes = new List <string> {
"Cleaning Bill", "Food Bill", "Misc Bill", "Room Bill"
};
CreateBillCommand = new RelayCommand(ExecuteCreateBill);
CancelCommand = new RelayCommand(ExecuteCancel);
}
public void insert(RoomBill roomBill)
{
using (SqlConnection conn = new SqlConnection(cs))
{
string query = "insert into room_category_bill values(@p_id,@r_id,@days,@per_day_tk,@total_bill)";
SqlCommand sqlCommand = new SqlCommand(query, conn);
sqlCommand.Parameters.AddWithValue("@p_id", roomBill.p_id);
sqlCommand.Parameters.AddWithValue("@r_id", roomBill.r_id);
sqlCommand.Parameters.AddWithValue("@days", roomBill.days);
sqlCommand.Parameters.AddWithValue("@per_day_tk", roomBill.per_day_tk);
sqlCommand.Parameters.AddWithValue("@total_bill", roomBill.total_bill);
conn.Open();
sqlCommand.ExecuteNonQuery();
}
}
public ActionResult Create(FormCollection collection)
{
RoomBillPortal roomBillPortal = new RoomBillPortal();
RoomBill roomBill = new RoomBill
{
p_id = Convert.ToInt32(collection["p_id"]),
r_id = Convert.ToInt32(collection["r_id"]),
days = Convert.ToInt32(collection["days"]),
};
roomBill.per_day_tk = roomBillPortal.getPerDaysTaka(roomBill.r_id);
roomBill.total_bill = roomBill.days * roomBill.per_day_tk;
roomBillPortal.insert(roomBill);
return(RedirectToAction("Index"));
}
public Reciept(Guest guest, DateTime from, DateTime to, RoomBill roomBill) : this(guest, from, to)
{
AddBill(roomBill);
}
