public OrganizationListPage() { InitializeComponent(); OrganizationService service = new OrganizationService(); List<tbl_organization> listOrganization = service.getOrganizationList(); this.Organizations = new ObservableCollection<Organization>(); foreach (tbl_organization organization in listOrganization) { ConnectionStringManager.updateConnectionStrings(organization.ID.ToString()); DrCarDriveService drCarService = new DrCarDriveService(); List<Car> listCar = drCarService.getDrCarList(); this.Organizations.Add(new Organization() { Id = organization.ID, Name = organization.ORGANIZATION_NAME, Cars = listCar }); } foreach (Organization org in this.Organizations) foreach (Car car in org.Cars) car.SetValue(ItemHelper.ParentProperty, org); }
internal void createExcelReport(string filePath, string carRegNo, string organizationName, List<DriveHistoryView> recordList) { try { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Drive Record"); int rowIndex = 0; IRow row = sheet.CreateRow(rowIndex); ICell cell = row.CreateCell(0); IFont font = workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; cell.SetCellValue("Doctor Car Drive Operation Management System Report - Drive Records"); cell.CellStyle.SetFont(font); CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4); //cell = row.CreateCell(5); //cell.SetCellValue(record.StartDate.ToString()); //CellManager.columnMerge(sheet, rowIndex, 5, 6, false); rowIndex++; sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Car Reg No", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 2, carRegNo, false); CellManager.topBottomBorder(workbook, cell, row, 1); CellManager.columnMerge(sheet, rowIndex, 2, 3, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Organization", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 2, organizationName.Trim(), false); CellManager.topBottomBorder(workbook, cell, row, 1); CellManager.columnMerge(sheet, rowIndex, 2, 3, true); rowIndex++; sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(1); cell.SetCellValue("Drive Details"); CellManager.topBottomBorder(workbook, cell, row, 11); CellManager.columnMerge(sheet, rowIndex, 1, 12, true); CellStyle.GrayBackground(workbook, cell, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "#", true); cell = CellManager.CreateCell(workbook, row, 2, "Start Date time", true); cell = CellManager.CreateCell(workbook, row, 3, "End Date Time", true); cell = CellManager.CreateCell(workbook, row, 4, "Operation Time (h)", true); cell = CellManager.CreateCell(workbook, row, 5, "Start Odometer (km)", true); cell = CellManager.CreateCell(workbook, row, 6, "End Odometer (km)", true); cell = CellManager.CreateCell(workbook, row, 7, "Distance (km)", true); cell = CellManager.CreateCell(workbook, row, 8, "Velocity (km/h)", true); cell = CellManager.CreateCell(workbook, row, 9, "Drive From", true); cell = CellManager.CreateCell(workbook, row, 10, "Drive To", true); cell = CellManager.CreateCell(workbook, row, 11, "Driver", true); cell = CellManager.CreateCell(workbook, row, 12, "No. Patients", true); int recordNo = 1; foreach (DriveHistoryView record in recordList) { rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, recordNo, true); cell = CellManager.CreateCell(workbook, row, 2, record.StartDate.ToString(), false); cell = CellManager.CreateCell(workbook, row, 3, record.EndDate.ToString(), false); TimeSpan duration = ((DateTime)record.EndDate - (DateTime)record.StartDate); cell = CellManager.CreateCell(workbook, row, 4, duration.Hours, false); cell = CellManager.CreateCell(workbook, row, 5, record.StartOdometer, false); cell = CellManager.CreateCell(workbook, row, 6, record.EndOdometer, false); cell = CellManager.CreateCell(workbook, row, 7, (record.EndOdometer - record.StartOdometer), false); if (duration.Hours > 0) { cell = CellManager.CreateCell(workbook, row, 8, Math.Round((double)(record.EndOdometer - record.StartOdometer) / duration.Hours, 1), false); } else { cell = CellManager.CreateCell(workbook, row, 8, "", false); } cell = CellManager.CreateCell(workbook, row, 9, record.From.ToString(), false); cell = CellManager.CreateCell(workbook, row, 10, record.To.ToString(), false); cell = CellManager.CreateCell(workbook, row, 11, record.DriverName.ToString(), false); cell = CellManager.CreateCell(workbook, row, 12, record.NumPatients, false); recordNo++; for (int i = 0; i < 15; i++) { sheet.AutoSizeColumn(i); } } rowIndex++; row = sheet.CreateRow(rowIndex); if (recordNo - 1 > 0) { cell = CellManager.CreateTotalCell(workbook, row, 12, "", false); cell.SetCellFormula("SUM(M" + (rowIndex + 1 - recordNo + 1) + ":M" + rowIndex + ")"); } sheet = workbook.CreateSheet("Fuel Record"); rowIndex = 0; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(0); cell.SetCellValue("Doctor Car Drive Operation Management System Report - Fuel Records"); CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4); rowIndex++; rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Fuel Expenses", true); CellManager.topBottomBorder(workbook, cell, row, 3); CellStyle.GrayBackground(workbook, cell, true); CellManager.columnMerge(sheet, rowIndex, 1, 4, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Date Time", true); cell = CellManager.CreateCell(workbook, row, 2, "Odometer (km)", true); cell = CellManager.CreateCell(workbook, row, 3, "Capacity (L)", true); cell = CellManager.CreateCell(workbook, row, 4, "Amount(SDG)", true); int numFuelRecords = 0; foreach (DriveHistoryView record in recordList) { if (record.FuelTopup != null) { foreach (tbl_fuel fuel in record.FuelTopup) { rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, fuel.ADD_TIME.ToString(), false); cell = CellManager.CreateCell(workbook, row, 2, fuel.ODOMETER, false); cell = CellManager.CreateCell(workbook, row, 3, fuel.VOLUME, false); cell = CellManager.CreateCell(workbook, row, 4, fuel.AMOUNT, false); numFuelRecords++; } } } rowIndex++; row = sheet.CreateRow(rowIndex); if (numFuelRecords > 0) { cell = CellManager.CreateLabelCell(workbook, row, 2, "Total", false); cell = CellManager.CreateTotalCell(workbook, row, 3, "", false); cell.SetCellFormula("SUM(D" + (rowIndex + 1 - numFuelRecords) + ":D" + rowIndex + ")"); cell = CellManager.CreateTotalCell(workbook, row, 4, "", false); cell.SetCellFormula("SUM(E" + (rowIndex + 1 - numFuelRecords) + ":E" + rowIndex + ")"); } for (int i = 0; i < 15; i++) { sheet.AutoSizeColumn(i); } sheet = workbook.CreateSheet("Other Expenses Record"); rowIndex = 0; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(0); cell.SetCellValue("Doctor Car Drive Operation Management System Report - Other Expenses"); CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 5); rowIndex++; rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Other Expenses", true); CellManager.topBottomBorder(workbook, cell, row, 3); CellStyle.GrayBackground(workbook, cell, true); CellManager.columnMerge(sheet, rowIndex, 1, 4, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Date Time", true); cell = CellManager.CreateCell(workbook, row, 2, "Item", true); cell = CellManager.CreateCell(workbook, row, 3, "Amount(SDG)", true); cell = CellManager.CreateCell(workbook, row, 4, "Description", true); DrCarDriveService service = new DrCarDriveService(); int numExpenses = 0; foreach (DriveHistoryView record in recordList) { List<ExpenseView> expenses = service.getOtherExpensesByDriveId(record.DriveID); if (expenses != null) { foreach (ExpenseView expense in expenses) { rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, expense.AddDate.ToString(), false); cell = CellManager.CreateCell(workbook, row, 2, expense.Item, false); cell = CellManager.CreateCell(workbook, row, 3, expense.Amount, false); cell = CellManager.CreateCell(workbook, row, 4, expense.Remark, false); numExpenses++; } } } rowIndex++; row = sheet.CreateRow(rowIndex); if (numExpenses > 0) { cell = CellManager.CreateLabelCell(workbook, row, 2, "Total", false); cell = CellManager.CreateTotalCell(workbook, row, 3, "", false); cell.SetCellFormula("SUM(D" + (rowIndex + 1 - numExpenses) + ":D" + rowIndex + ")"); } for (int i = 0; i < 15; i++) { sheet.AutoSizeColumn(i); } FileStream stream = File.Create(filePath); workbook.Write(stream); stream.Close(); } catch (Exception ex) { } }
private void populateOrganizationList() { try { OrganizationService service = new OrganizationService(); List<tbl_organization> listOrganization = service.getOrganizationList(); this.Organizations = new ObservableCollection<Organization>(); foreach (tbl_organization organization in listOrganization) { ConnectionStringManager.updateConnectionStrings(organization.ID.ToString()); DrCarDriveService drCarService = new DrCarDriveService(); List<Car> listCar = drCarService.getDrCarList(); this.Organizations.Add(new Organization() { Id = organization.ID, Name = organization.ORGANIZATION_NAME, Cars = listCar }); } foreach (Organization org in this.Organizations) { foreach (Car car in org.Cars) { car.SetValue(ItemHelper.ParentProperty, org); } } Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); var settings = config.AppSettings.Settings; txtSavePath.Text = settings["FileSavePath"].Value; } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } }
private void btnGenerateReport_Click(object sender, RoutedEventArgs e) { if (validInputs()) { try { // ExcelGenerator generator = new ExcelGenerator(); ExcelGeneratorByCar generator = new ExcelGeneratorByCar(); DateTime? fromDate = dateFrom.SelectedDate; DateTime toDate = dateTo.SelectedDate.Value; int recordCount = 0; foreach (Organization org in this.Organizations) { if (ItemHelper.GetIsChecked(org) == true) { foreach (Car car in org.Cars) { if (ItemHelper.GetIsChecked(car) == true) { //string filePath = txtSavePath.Text + "\\Report_" + org.Name.Trim() + "_" + car.RegNo.Trim() + "_" + fromDate.Value.Date.ToString("yyyy-MM-dd") + ".xlsx"; string filePath = txtSavePath.Text + "\\Report_" + org.Name.Trim() + "_" + car.RegNo.Trim() + ".xlsx"; if (File.Exists(filePath)) { var result = System.Windows.MessageBox.Show("Report file for your selected inputs already exists. Do you want to replace this file?", "File already exists", System.Windows.MessageBoxButton.YesNoCancel); if (result == System.Windows.MessageBoxResult.Cancel) { return; } else if (result == System.Windows.MessageBoxResult.Yes) { File.Delete(filePath); } else if (result == System.Windows.MessageBoxResult.No) { filePath = generateNewFilePath(filePath); } } ConnectionStringManager.updateConnectionStrings(org.Id.ToString()); DrCarDriveService service = new DrCarDriveService(); List<DriveHistoryView> record = service.viewHistoryRecordByCarIDDateFromTo(car.Id, fromDate.Value, toDate.AddDays(1)); if (record.Count > 0) { progReport.Visibility = System.Windows.Visibility.Hidden; generator.createExcelReport(filePath, car.RegNo, org.Name, record); //Thread t = new Thread(() => runCreateReport(generator, filePath, car.RegNo, record, (double)(recordCount+1/record.Count))); //t.SetApartmentState(ApartmentState.STA); //t.Start(); recordCount++; } } } } } if (recordCount > 0) { System.Windows.MessageBox.Show("Report created successfully", "Success"); } else { System.Windows.MessageBox.Show("No records available for the selected inputs"); } } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message, "Failed to create report"); } } }
internal void createExcelReport(string filePath, string carRegNo, List<DriveHistoryView> recordList) { try { IWorkbook workbook = new XSSFWorkbook(); foreach (DriveHistoryView record in recordList) { ISheet sheet = workbook.CreateSheet(record.StartDate.Value.ToString("yyyy-MM-dd") + "_" + new Random().Next(100).ToString()); int rowIndex = 0; IRow row = sheet.CreateRow(rowIndex); ICell cell = row.CreateCell(0); cell.SetCellValue("Doctor Car Drive Operation Management System Report"); CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4); //cell = row.CreateCell(5); //cell.SetCellValue(record.StartDate.ToString()); //CellManager.columnMerge(sheet, rowIndex, 5, 6, false); rowIndex++; sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Car Reg No", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 2, carRegNo, false); cell = CellManager.CreateCell(workbook, row, 4, "Start Odometer (km)", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 5, record.StartOdometer, false); cell = CellManager.CreateCell(workbook, row, 7, "Record Date", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 8, record.InsertDateTime.ToString(), false); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Operation Date", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 2, record.StartDate.ToString(), false); cell = CellManager.CreateCell(workbook, row, 4, "End Odometer (km)", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 5, record.EndOdometer, false); cell = CellManager.CreateCell(workbook, row, 7, "Drive From", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 8, record.From, false); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Driver", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 2, record.DriverName, false); cell = CellManager.CreateCell(workbook, row, 4, "Distance (km)", false); CellStyle.GrayBackground(workbook, cell, false); string distance = FindDifference.calculateDistance(record.StartOdometer, record.EndOdometer); try { double dist = Convert.ToDouble(distance); cell = CellManager.CreateCell(workbook, row, 5, dist, false); } catch { cell = CellManager.CreateCell(workbook, row, 5, distance, false); } cell = CellManager.CreateCell(workbook, row, 7, "Drive To", false); CellStyle.GrayBackground(workbook, cell, false); cell = CellManager.CreateCell(workbook, row, 8, record.To, false); rowIndex++; sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(1); cell.SetCellValue("Visit Details"); CellManager.topBottomBorder(workbook, cell, row, 10); CellManager.columnMerge(sheet, rowIndex, 1, 11, true); CellStyle.GrayBackground(workbook, cell, true); cell = row.CreateCell(12); cell.SetCellValue("Medicine"); CellManager.topBottomBorder(workbook, cell, row, 2); CellManager.columnMerge(sheet, rowIndex, 12, 14, true); CellStyle.GrayBackground(workbook, cell, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Place", true); cell = CellManager.CreateCell(workbook, row, 2, "Arrival Date time", true); cell = CellManager.CreateCell(workbook, row, 3, "Departure Date Time", true); cell = CellManager.CreateCell(workbook, row, 4, "Odometer (km)", true); cell = CellManager.CreateCell(workbook, row, 5, "No. Patients", true); cell = CellManager.CreateCell(workbook, row, 6, "No. Doctors", true); cell = CellManager.CreateCell(workbook, row, 7, "No. Nurses", true); cell = CellManager.CreateCell(workbook, row, 8, "Activity", true); CellManager.topBottomBorder(workbook, cell, row, 3); CellManager.columnMerge(sheet, rowIndex, 8, 11, true); cell = CellManager.CreateCell(workbook, row, 12, "Item", true); cell = CellManager.CreateCell(workbook, row, 13, "Dosage", true); cell = CellManager.CreateCell(workbook, row, 14, "Unit", true); DrCarDriveService service = new DrCarDriveService(); if (record.Places != null) { int numPlaces = 0; rowIndex++; row = sheet.CreateRow(rowIndex); foreach (tbl_places place in record.Places) { cell = CellManager.CreateCell(workbook, row, 1, place.VILLAGE_NAME, false); cell = CellManager.CreateCell(workbook, row, 2, place.ARRIVAL_DATE.ToString(), false); cell = CellManager.CreateCell(workbook, row, 3, place.DEPARTURE_DATE.ToString(), false); cell = CellManager.CreateCell(workbook, row, 4, place.ARRIVAL_ODOMETER, false); cell = CellManager.CreateCell(workbook, row, 5, place.NUMBER_PATIENT, false); cell = CellManager.CreateCell(workbook, row, 6, place.NUMBER_DOCTORS, false); cell = CellManager.CreateCell(workbook, row, 7, place.NUMBER_PARAMEDICS, false); cell = CellManager.CreateCell(workbook, row, 8, place.REMARK, false); CellManager.topBottomBorder(workbook, cell, row, 3); CellManager.columnMerge(sheet, rowIndex, 8, 11, false); List<tbl_issue_medicine> medicineList = service.getIssueMedicineByPlaceId(place.ID); if (medicineList.Count > 0) { int medCount = 0; foreach (tbl_issue_medicine medicine in medicineList) { tbl_drive_medicine med = service.getMedicineById(medicine.FKY_MEDICINE); cell = CellManager.CreateCell(workbook, row, 12, med.NAME, false); cell = CellManager.CreateCell(workbook, row, 13, medicine.DOSAGE, false); cell = CellManager.CreateCell(workbook, row, 14, medicine.UNIT, false); if (medCount > 0) { cell = CellManager.SetCellLeftBorder(workbook, row, 1); } medCount++; rowIndex++; row = sheet.CreateRow(rowIndex); } cell = CellManager.SetCellLeftBorder(workbook, row, 1); cell = CellManager.SetCellRightBorder(workbook, row, 14); } else { cell = CellManager.CreateCell(workbook, row, 12, "", false); cell = CellManager.CreateCell(workbook, row, 13, "", false); cell = CellManager.CreateCell(workbook, row, 14, "", false); } numPlaces++; rowIndex++; row = sheet.CreateRow(rowIndex); //CellManager.SetCellTopBorder(workbook, row, 12, 14, new List<int> { }); } if (numPlaces > 0) { cell = CellManager.CreateLabelCell(workbook, row, 4, "Total", false); cell = CellManager.CreateTotalCell(workbook, row, 5, "", false); cell.SetCellFormula("SUM(F" + "9" + ":F" + rowIndex + ")"); CellManager.SetCellTopBorder(workbook, row, 1, 11, new List<int> { 4, 5 }); } } rowIndex++; row = sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Fuel Expenses", true); CellManager.topBottomBorder(workbook, cell, row, 2); CellStyle.GrayBackground(workbook, cell, true); CellManager.columnMerge(sheet, rowIndex, 1, 3, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Odometer", true); cell = CellManager.CreateCell(workbook, row, 2, "Capacity (L)", true); cell = CellManager.CreateCell(workbook, row, 3, "Amount(SDG)", true); if (record.FuelTopup != null) { int numFuelRecords = 0; foreach (tbl_fuel fuel in record.FuelTopup) { rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, fuel.ODOMETER, false); cell = CellManager.CreateCell(workbook, row, 2, fuel.VOLUME, false); cell = CellManager.CreateCell(workbook, row, 3, fuel.AMOUNT, false); numFuelRecords++; } rowIndex++; row = sheet.CreateRow(rowIndex); if (numFuelRecords > 0) { cell = CellManager.CreateLabelCell(workbook, row, 1, "Total", false); cell = CellManager.CreateTotalCell(workbook, row, 2, "", false); cell.SetCellFormula("SUM(C" + (rowIndex + 1 - numFuelRecords) + ":C" + rowIndex + ")"); } } rowIndex++; row = sheet.CreateRow(rowIndex); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Other Expenses", true); CellManager.topBottomBorder(workbook, cell, row, 2); CellStyle.GrayBackground(workbook, cell, true); CellManager.columnMerge(sheet, rowIndex, 1, 3, true); rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, "Item", true); cell = CellManager.CreateCell(workbook, row, 2, "Amount(SDG)", true); cell = CellManager.CreateCell(workbook, row, 3, "Description", true); List<ExpenseView> expenses = service.getOtherExpensesByDriveId(record.DriveID); if (expenses != null) { int numExpenses = 0; foreach (ExpenseView expense in expenses) { rowIndex++; row = sheet.CreateRow(rowIndex); cell = CellManager.CreateCell(workbook, row, 1, expense.Item, false); cell = CellManager.CreateCell(workbook, row, 2, expense.Amount, false); cell = CellManager.CreateCell(workbook, row, 3, expense.Remark, false); numExpenses++; } rowIndex++; row = sheet.CreateRow(rowIndex); if (numExpenses > 0) { cell = CellManager.CreateLabelCell(workbook, row, 1, "Total", false); cell = CellManager.CreateTotalCell(workbook, row, 2, "", false); cell.SetCellFormula("SUM(C" + (rowIndex + 1 - numExpenses) + ":C" + rowIndex + ")"); } } for (int i = 0; i < 15; i++) { sheet.AutoSizeColumn(i); } } FileStream stream = File.Create(filePath); workbook.Write(stream); stream.Close(); } catch (Exception ex) { } }