Exemple #1
0
        //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();
            }
        }
Exemple #2
0
        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();
            }
        }