public void PaySlip_NSW_MYOB2() { var factory = new NSWFactory(); BasePaySlipEngine payEngine = factory.GetPaySlipEngine(); var input = new EngineInput() { FirstName = "Ryan", LastName = "Chen", AnnualSalary = 120000, SuperRate = 10, PayPeriod = "01 March - 31 March" }; var paySlipOutput = payEngine.GeneratePaySlip(input); Assert.IsTrue(paySlipOutput.Name.Equals("Ryan Chen") && paySlipOutput.GrossIncome == 10000 && paySlipOutput.IncomeTax == 2669 && paySlipOutput.NetIncome == 7331 && paySlipOutput.Super == 1000); }
public void PaySlip_NSWMYOB1() { var factory = new NSWFactory(); BasePaySlipEngine payEngine = factory.GetPaySlipEngine(); var input = new EngineInput() { FirstName = "David", LastName = "Rudd", AnnualSalary = 60050, SuperRate = 9, PayPeriod = "01 March - 31 March" }; var paySlipOutput = payEngine.GeneratePaySlip(input); Assert.IsTrue(paySlipOutput.Name.Equals("David Rudd") && paySlipOutput.GrossIncome == 5004 && paySlipOutput.IncomeTax == 922 && paySlipOutput.NetIncome == 4082 && paySlipOutput.Super == 450); }
public static ExcelPackage GeneratePaySlipsExcel(ExcelPackage package, string state) { try { int idxLastName = 1, idxFirstName = 0, idxAnnsualSalary = 2, idxSuperRate = 3, idxPayPeriod = 4; //Input bool ifAllEmpty = true; ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); //Output ExcelPackage excelExport = new ExcelPackage(); var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips"); workSheetOutput.TabColor = System.Drawing.Color.Black; workSheetOutput.DefaultRowHeight = 12; int recordIndex = 1; //Header of output excel workSheetOutput.Row(recordIndex).Height = 20; workSheetOutput.Row(recordIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheetOutput.Row(recordIndex).Style.Font.Bold = true; workSheetOutput.Cells[recordIndex, 1].Value = OutputExcelColumn.Name; workSheetOutput.Cells[recordIndex, 2].Value = OutputExcelColumn.GrossIncome; workSheetOutput.Cells[recordIndex, 3].Value = OutputExcelColumn.IncomeTax; workSheetOutput.Cells[recordIndex, 4].Value = OutputExcelColumn.NetIncome; workSheetOutput.Cells[recordIndex, 5].Value = OutputExcelColumn.Super; workSheetOutput.Cells[recordIndex, 6].Value = OutputExcelColumn.PayPeriod; // This loop will accept input excel even if order of columns is different for (int iCol = 1; iCol <= workSheet.Dimension.End.Column; iCol++) { var colName = Convert.ToString(((object[, ])workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column].Value)[0, iCol - 1]); switch (colName) { case InputExcelColumn.FirstName: idxFirstName = iCol; break; case InputExcelColumn.LastName: idxLastName = iCol; break; case InputExcelColumn.AnnualSalary: idxAnnsualSalary = iCol; break; case InputExcelColumn.SuperRate: idxSuperRate = iCol; break; case InputExcelColumn.PayPeriod: idxPayPeriod = iCol; break; } } // Thread-safe collection to handle input data processing BlockingCollection <EngineInput> bag = new BlockingCollection <EngineInput>(); // Blocking Consumer task - reaing records from collection and processing Task t2 = Task.Factory.StartNew(() => { PaySlipEngineFactory factory = null; switch (state) { case States.NSW: factory = new NSWFactory(); break; case States.Victoria: factory = new VictoriaFactory(); break; } EngineOutput paySlipOutput = null; BasePaySlipEngine payEngine = factory.GetPaySlipEngine(); EngineInput input = null; // Take() was called on a completed collection. // Some other thread can call CompleteAdding after we pass the // IsCompleted check but before we call Take. // In this example, we can simply catch the exception since the // loop will break on the next iteration. while (!bag.IsCompleted) { recordIndex++; input = null; try { input = bag.Take(); } catch (InvalidOperationException) { break; } if (input != null) { paySlipOutput = payEngine.GeneratePaySlip(input); workSheetOutput.Cells[recordIndex, 1].Value = paySlipOutput.Name; workSheetOutput.Cells[recordIndex, 2].Value = paySlipOutput.GrossIncome; workSheetOutput.Cells[recordIndex, 3].Value = paySlipOutput.IncomeTax; workSheetOutput.Cells[recordIndex, 4].Value = paySlipOutput.NetIncome; workSheetOutput.Cells[recordIndex, 5].Value = paySlipOutput.Super; workSheetOutput.Cells[recordIndex, 6].Value = paySlipOutput.PayPeriod; } } }); // Blocking Producer task - reading records from excel sheet Task t1 = Task.Factory.StartNew(() => { EngineInput inputObj = null; for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++) { ifAllEmpty = true; decimal dVal = 0; var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column]; inputObj = new EngineInput(); for (int iCol = 1; iCol <= workSheet.Dimension.End.Column; iCol++) { var value = Convert.ToString(((object[, ])row.Value)[0, iCol - 1]); if (!string.IsNullOrEmpty(value)) { ifAllEmpty = false; } if (iCol == idxFirstName) { inputObj.FirstName = value; } else if (iCol == idxLastName) { inputObj.LastName = value; } else if (iCol == idxAnnsualSalary) { inputObj.AnnualSalary = decimal.TryParse(value, out dVal) ? dVal : throw new System.InvalidCastException($"Annual Salary is not in correct format. Value: {value}"); } else if (iCol == idxSuperRate) { inputObj.SuperRate = decimal.TryParse(value, out dVal) ? dVal : throw new System.InvalidCastException($"Super Rate is not in correct format. Value: {value}"); } else if (iCol == idxPayPeriod) { inputObj.PayPeriod = value; } } if (ifAllEmpty) { break; } bag.Add(inputObj); } // Let consumer know we are done. bag.CompleteAdding(); }); // Wait till both producer and consumer finish their job Task main = Task.WhenAll(t1, t2); main.Wait(); return(excelExport); } catch (Exception ex) { throw; } }
/// <summary> /// 1. This method takes excel as input and state code. /// 2. The PaySlip factory creates the State specific payslip generator/engine depending on the State selected by user in UI /// 3. Producer/Consumer multithread design pattern is used. /// Producer reads single record at a time and add records in thread-safe collection. /// Consumer reads records from above collection and process them and adds to the excel worksheet object /// 4. Input excel can contain columns in any order. /// 5. Order/Format of columns in output excel sheet is fixed /// </summary> /// <param name="package"></param> /// <param name="state"></param> /// <returns></returns> public static ExcelPackage GeneratePaySlipsExcel(ExcelPackage package, string state) { int idxFirstName = 0, idxLastName = 1, idxAnnsualSalary = 2, idxSuperRate = 3, idxPayPeriod = 4; //Input ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); //Output ExcelPackage excelExport = new ExcelPackage(); var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips"); workSheetOutput.TabColor = System.Drawing.Color.Black; workSheetOutput.DefaultRowHeight = 12; int recordIndex = 1; //Header of output excel workSheetOutput.Row(recordIndex).Height = 20; workSheetOutput.Row(recordIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheetOutput.Row(recordIndex).Style.Font.Bold = true; workSheetOutput.Cells[recordIndex, 1].Value = OutputExcelColumn.Name; workSheetOutput.Cells[recordIndex, 2].Value = OutputExcelColumn.GrossIncome; workSheetOutput.Cells[recordIndex, 3].Value = OutputExcelColumn.IncomeTax; workSheetOutput.Cells[recordIndex, 4].Value = OutputExcelColumn.NetIncome; workSheetOutput.Cells[recordIndex, 5].Value = OutputExcelColumn.Super; workSheetOutput.Cells[recordIndex, 6].Value = OutputExcelColumn.PayPeriod; var maxColumnCount = workSheet.Dimension.End.Column; // This loop will accept input excel even if order of columns is different for (int iCol = 1; iCol <= maxColumnCount; iCol++) { var colName = Convert.ToString(((object[, ])workSheet.Cells[1, 1, 1, maxColumnCount].Value)[0, iCol - 1]); switch (colName) { case InputExcelColumn.FirstName: idxFirstName = iCol; break; case InputExcelColumn.LastName: idxLastName = iCol; break; case InputExcelColumn.AnnualSalary: idxAnnsualSalary = iCol; break; case InputExcelColumn.SuperRate: idxSuperRate = iCol; break; case InputExcelColumn.PayPeriod: idxPayPeriod = iCol; break; } } ; // Thread-safe collection to handle input data processing BlockingCollection <EngineInput> bag = new BlockingCollection <EngineInput>(); // Blocking Consumer task - reaing records from collection and processing Task consumer = Task.Factory.StartNew(() => { PaySlipEngineFactory factory = null; switch (state) { case States.NSW: factory = new NSWFactory(); break; case States.Victoria: factory = new VictoriaFactory(); break; default: throw new InvalidOperationException("Unknown State."); } EngineOutput paySlipOutput = null; BasePaySlipEngine payEngine = factory.GetPaySlipEngine(); foreach (var input in bag.GetConsumingEnumerable()) { recordIndex++; paySlipOutput = payEngine.GeneratePaySlip(input); workSheetOutput.Cells[recordIndex, 1].Value = paySlipOutput.Name; workSheetOutput.Cells[recordIndex, 2].Value = paySlipOutput.GrossIncome; workSheetOutput.Cells[recordIndex, 3].Value = paySlipOutput.IncomeTax; workSheetOutput.Cells[recordIndex, 4].Value = paySlipOutput.NetIncome; workSheetOutput.Cells[recordIndex, 5].Value = paySlipOutput.Super; workSheetOutput.Cells[recordIndex, 6].Value = paySlipOutput.PayPeriod; } ; }); // Blocking Producer task - reading records from excel sheet Task producer = Task.Factory.StartNew(() => { // Use ConcurrentQueue to enable safe enqueueing from multiple threads. var exceptions = new ConcurrentQueue <Exception>(); Parallel.For(2, workSheet.Dimension.End.Row + 1, (rowNumber, loopState) => { try { var ifAllEmpty = true; var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column]; var inputObj = new EngineInput(); for (int iCol = 1; iCol <= workSheet.Dimension.End.Column; iCol++) { decimal dVal = 0; var value = Convert.ToString(((object[, ])row.Value)[0, iCol - 1]); if (!string.IsNullOrEmpty(value)) { ifAllEmpty = false; } if (iCol == idxFirstName) { inputObj.FirstName = value.Trim(); } else if (iCol == idxLastName) { inputObj.LastName = value.Trim(); } else if (iCol == idxAnnsualSalary) { inputObj.AnnualSalary = decimal.TryParse(value.Trim(), out dVal) ? dVal > 0 ? dVal : throw new System.InvalidCastException($"Annual Salary must be greater than 0. Value: {value}.") : throw new System.InvalidCastException($"Annual Salary is not in correct format. Value: {value}."); } else if (iCol == idxSuperRate) { inputObj.SuperRate = decimal.TryParse(value.Trim(), out dVal) ? (dVal >= 0 && dVal <= 50) ? dVal : throw new System.InvalidCastException($"Super rate should be in 0-50% range. Value: {value}.") : throw new System.InvalidCastException($"Super Rate is not in correct format. Value: {value}."); } else if (iCol == idxPayPeriod) { inputObj.PayPeriod = value.Trim(); } } if (ifAllEmpty) { loopState.Stop(); } bag.Add(inputObj); } // Store the exception and continue with the loop. catch (Exception e) { exceptions.Enqueue(e); loopState.Stop(); } }); // Throw the exceptions here after the loop completes. if (exceptions.Count > 0) { throw new AggregateException(exceptions); } // Let consumer know we are done. bag.CompleteAdding(); }); // Wait till both producer and consumer finish their job producer.Wait(); consumer.Wait(); return(excelExport); }