//Pham Thi Ha Linh request //theres something wrong... private static void CreateTakeRandomCallReport(DataSet col, DataSet outbound, int take, double callLen) { string folder = string.Format("{0}/{1}", GetCurrentDirectory(), OutputFolder); string fileName = string.Format("{0}/{1}", folder, ReportRandomContractName); var file = new FileInfo(fileName); Directory.CreateDirectory(folder); if (File.Exists(fileName)) { File.Delete(fileName); } using (var package = new ExcelPackage(file)) { var processedCollectorID = new HashSet <string>(); var processedOutboundIndex = new HashSet <int>(); var worksheet = package.Workbook.Worksheets.Add("random_contract_report"); worksheet.OutLineSummaryBelow = false; var colTable = col.Tables[0]; //var outboundTable = RandomOrderTable(outbound.Tables[0]); //random outbound calls var outboundTable = outbound.Tables[0]; //map outbound data var outboundDataMap = MapData(outboundTable, OutboundCustomerRefColumn); //map col data var colMap = MapData(colTable, ColCollectorColumn); //set headers EPPlusHelper.SetRow(2, 1, GetHeaders(colTable), worksheet, COL_COLOR).AutoFilter = true; EPPlusHelper.SetRow(1, 2, GetHeaders(outboundTable), worksheet, OUTBOUND_COLOR); int currentRow = 3; //loop all col data rows for (int rowIndex = 0; rowIndex < colTable.Rows.Count; rowIndex++) { var row = colTable.Rows[rowIndex]; var collectorCode = row[ColCollectorColumn].ToString(); if (processedCollectorID.Contains(collectorCode)) { UpdatePerfStat(rowIndex + 1, colTable.Rows.Count); continue; } processedCollectorID.Add(collectorCode); int took = 0; //loop all row related to a collector code foreach (var colMapIndex in colMap[collectorCode]) { //contract number of a collector var contractNumber = row[ColContractNoColumn].ToString(); //check if this contract number been call (has outbound data) if (outboundDataMap.ContainsKey(string.Format("SHD:{0}", contractNumber))) { //loop all outbound to the related contract foreach (var outboundIndex in outboundDataMap[string.Format("SHD:{0}", contractNumber)]) { //skip contract that been processed if (processedOutboundIndex.Contains(outboundIndex)) { continue; } processedOutboundIndex.Add(outboundIndex); DateTime startDate, endDate; if (DateTime.TryParse(outboundTable.Rows[outboundIndex][OutboundStartDateColumn].ToString(), out startDate) && DateTime.TryParse(outboundTable.Rows[outboundIndex][OutboundEndDateColumn].ToString(), out endDate)) { if (endDate.Subtract(startDate) >= TimeSpan.FromSeconds(callLen)) //only take outbound > 30s { //write to package EPPlusHelper.SetRow(currentRow, 1, colTable.Rows[colMapIndex].ItemArray, worksheet, COL_COLOR); currentRow++; EPPlusHelper.SetRow(currentRow, 2, outboundTable.Rows[outboundIndex].ItemArray, worksheet, OUTBOUND_COLOR); currentRow++; EPPlusHelper.CollapseRow(currentRow - 1, currentRow, worksheet); took++; //take only one outbound call for earch contract break; } } else { continue; } } } else { continue; } //enough for this collector -> break if (took >= take) { break; } } //this col data row is done UpdatePerfStat(rowIndex + 1, colTable.Rows.Count); } //all done -> save WriteLine(string.Empty); WriteLine("Writing file..."); package.Save(); } }
private static void CreateFullReport(DataSet col, DataSet outbound) { // Set the file name and get the output directory var processedSet = new HashSet <string>(); string folder = string.Format("{0}/{1}", GetCurrentDirectory(), OutputFolder); string fileName = string.Format("{0}/{1}", folder, ReportName); var file = new FileInfo(fileName); Directory.CreateDirectory(folder); if (File.Exists(fileName)) { //delete to override File.Delete(fileName); } int outboundCallCount = 0; int contractHasBeenCalledCount = 0; using (var package = new ExcelPackage(file)) { // add a new worksheet to the empty workbook var worksheet = package.Workbook.Worksheets.Add("merged_report"); worksheet.OutLineSummaryBelow = false; //compose report start var colTable = col.Tables[0]; var outboundTable = outbound.Tables[0]; //map outbound data var outboundDataMap = MapData(outboundTable, OutboundCustomerRefColumn); //map col data var colMap = MapData(colTable, ColContractNoColumn); //set headers EPPlusHelper.SetRow(2, 1, GetHeaders(colTable), worksheet, COL_COLOR).AutoFilter = true; EPPlusHelper.SetRow(1, 2, GetHeaders(outboundTable), worksheet, OUTBOUND_COLOR); int currentRow = 3; //int perfIndex = 1; for (int i = 0; i < colTable.Rows.Count; i++) { string contractNumber = colTable.Rows[i][ColContractNoColumn].ToString(); if (string.IsNullOrEmpty(contractNumber) || processedSet.Contains(contractNumber)) { UpdatePerfStat(i + 1, colTable.Rows.Count); continue; } //add to skip list processedSet.Add(contractNumber); //find related data in outbound List <int> dataIndexes; if (outboundDataMap.ContainsKey(string.Format("SHD:{0}", contractNumber))) { contractHasBeenCalledCount++; dataIndexes = outboundDataMap[string.Format("SHD:{0}", contractNumber)]; } else { //no related data in outbound -> add later at the end _addLaterRows.Add(colTable.Rows[i]); UpdatePerfStat(i + 1, colTable.Rows.Count); continue; } ////write col data to sheet int rowToCollapseFrom = 0; foreach (int contractIndex in colMap[contractNumber]) { if (contractIndex == i) //first one of each contract has no padding { rowToCollapseFrom = currentRow + 1; EPPlusHelper.SetRow(currentRow, 1, colTable.Rows[contractIndex].ItemArray, worksheet, COL_COLOR); } else { EPPlusHelper.SetRow(currentRow, 2, colTable.Rows[contractIndex].ItemArray, worksheet, OUTBOUND_COLOR); } currentRow++; } //write outbound data to sheet foreach (int index in dataIndexes) { outboundCallCount++; EPPlusHelper.SetRow(currentRow, 2, outboundTable.Rows[index].ItemArray, worksheet, OUTBOUND_COLOR); currentRow++; } if (rowToCollapseFrom == 0) { throw new InvalidOperationException("rowToCollapseFrom has not been set!"); } EPPlusHelper.CollapseRow(rowToCollapseFrom, currentRow, worksheet); UpdatePerfStat(i + 1, colTable.Rows.Count); } //add back empty ones foreach (var noRelatedDataRow in _addLaterRows) { EPPlusHelper.SetRow(currentRow, 1, noRelatedDataRow.ItemArray, worksheet, COL_COLOR); currentRow++; } //compose report end WriteLine(string.Empty); WriteLine("Writing file..."); WriteLine(string.Empty); WriteLine("Summary:"); WriteLine(string.Format("Contract has been called: {0}", contractHasBeenCalledCount)); WriteLine(string.Format("Total outbound calls: {0}", outboundCallCount)); package.Save(); } }