public byte[] DoResampler(EngineInput Args)
{
byte[] data = new byte[0];
if (!_isLegalPlugin)
{
return(data);
}
try {
IntPtr hModule = LoadLibrary(DllPath);
if (hModule == IntPtr.Zero)
{
_isLegalPlugin = false;
}
else
{
IntPtr m = GetProcAddress(hModule, "DoResampler");
if (m != IntPtr.Zero)
{
DoResamplerDelegate g = (DoResamplerDelegate)Marshal.GetDelegateForFunctionPointer(m, typeof(DoResamplerDelegate));
EngineOutput Output = Intptr2EngineOutput(g(Args));
data = Output.wavData;
}
FreeLibrary(hModule);
}
} catch {; }
return(data);
}
public void PaySlip_NSW_MYOB1_Excel_SalaryNaN()
{
ExcelPackage excelExport = new ExcelPackage();
var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips");
workSheetOutput.Cells[1, 1].Value = InputExcelColumn.FirstName;
workSheetOutput.Cells[1, 2].Value = InputExcelColumn.LastName;
workSheetOutput.Cells[1, 3].Value = InputExcelColumn.AnnualSalary;
workSheetOutput.Cells[1, 4].Value = InputExcelColumn.SuperRate;
workSheetOutput.Cells[1, 5].Value = InputExcelColumn.PayPeriod;
workSheetOutput.Cells[2, 1].Value = "David";
workSheetOutput.Cells[2, 2].Value = "Rudd";
workSheetOutput.Cells[2, 3].Value = "Sixty Thousand";
workSheetOutput.Cells[2, 4].Value = 9;
workSheetOutput.Cells[2, 5].Value = "01 March - 31 March";
var output = PaySlipWorker.GeneratePaySlipsExcel(excelExport, States.NSW);
ExcelWorksheet workSheet = output.Workbook.Worksheets.First();
var maxColumnCount = workSheet.Dimension.End.Column;
var row = workSheet.Cells[2, 1, 2, maxColumnCount];
var paySlipOutput = new EngineOutput()
{
Name = Convert.ToString(((object[, ])row.Value)[0, 0]),
GrossIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 1]),
IncomeTax = Convert.ToDecimal(((object[, ])row.Value)[0, 2]),
NetIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 3]),
Super = Convert.ToDecimal(((object[, ])row.Value)[0, 4]),
PayPeriod = Convert.ToString(((object[, ])row.Value)[0, 5])
};
}
private readonly EngineOutput _actualCalculation; //the result of the actual done calculation
/// <summary>
/// calculates an engine. Will be initialized with the standard values
/// </summary>
public Engine()
{
CharacteristicLine = new Spline(new[] { 0.0, 10000.0 }, new[] { 0.0, 100.0 });
CharacteristicLine.NameX = "rounds per minute";
CharacteristicLine.NameY = "torque (Nm)";
_actualCalculation = new EngineOutput();
MaxRpm = 10000;
EngineIdleRpm = 1000;
}
/// <summary>
/// This method generates payslip of the employee
/// </summary>
/// <param name="">EngineInput object having details of employee salary</param>
/// <returns>EngineOutput object having details of generated payslip</returns>
public virtual EngineOutput GeneratePaySlip(EngineInput employeeSalaryDetail)
{
EngineOutput payslip = new EngineOutput();
payslip.Name = employeeSalaryDetail.FirstName + employeeSalaryDetail.LastName;
payslip.PayPeriod = employeeSalaryDetail.PayPeriod;
payslip.GrossIncome = CalculateGrossIncome(employeeSalaryDetail.AnnualSalary);
payslip.IncomeTax = CalculateIncomeTax(employeeSalaryDetail.AnnualSalary);
payslip.NetIncome = CalculateNetIncome(payslip.GrossIncome, payslip.IncomeTax);
payslip.Super = CalculateSuper(payslip.GrossIncome, employeeSalaryDetail.SuperRate);
return(payslip);
}
public byte[] DoResampler(EngineInput Args, ILogger logger)
{
byte[] data = new byte[0];
if (!_isLegalPlugin)
{
return(data);
}
if (DoResamplerMethod != null)
{
object inputarg = CopyObjectToNewType(Args, DoResamplerMethod.GetParameters()[0].ParameterType);
object ret = DoResamplerMethod.Invoke(null, new object[1] {
inputarg
});
EngineOutput Out = (EngineOutput)CopyObjectToNewType(ret, typeof(EngineOutput));
data = Out.wavData;
}
return(data);
}
/// <summary>
/// Cpp指针转换过程-EngineOutput
/// </summary>
/// <param name="Ptr"></param>
/// <returns></returns>
protected static EngineOutput Intptr2EngineOutput(IntPtr Ptr)
{
EngineOutput Ret = new EngineOutput();
Ret.nWavData = 0;
try {
if (Ptr != IntPtr.Zero)
{
EngineOutput_Cpp CTry = (EngineOutput_Cpp)Marshal.PtrToStructure(Ptr, typeof(EngineOutput_Cpp));
if (CTry.nWavData > 0)
{
Ret.wavData = new byte[CTry.nWavData];
Marshal.Copy(CTry.wavData, Ret.wavData, 0, CTry.nWavData);
Ret.nWavData = CTry.nWavData;
}
}
} catch {; }
return(Ret);
}
/// <summary>
/// Cpp指针转换过程-EngineOutput
/// </summary>
/// <param name="Ptr"></param>
/// <returns></returns>
protected static EngineOutput Intptr2EngineOutput(IntPtr Ptr)
{
EngineOutput Ret = new EngineOutput();
Ret.nWavData = 0;
try
{
if (Ptr != IntPtr.Zero)
{
EngineOutput_Cpp CTry = (EngineOutput_Cpp)Marshal.PtrToStructure(Ptr, typeof(EngineOutput_Cpp));
if (CTry.nWavData > 0)
{
Ret.wavData = new byte[CTry.nWavData];
Marshal.Copy(CTry.wavData, Ret.wavData, 0, CTry.nWavData);
Ret.nWavData = CTry.nWavData;
}
}
}
catch { ;}
return Ret;
}
public void PaySlip_NSW_MYOB2_Excel()
{
ExcelPackage excelExport = new ExcelPackage();
var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips");
workSheetOutput.Cells[1, 1].Value = InputExcelColumn.FirstName;
workSheetOutput.Cells[1, 2].Value = InputExcelColumn.LastName;
workSheetOutput.Cells[1, 3].Value = InputExcelColumn.AnnualSalary;
workSheetOutput.Cells[1, 4].Value = InputExcelColumn.SuperRate;
workSheetOutput.Cells[1, 5].Value = InputExcelColumn.PayPeriod;
workSheetOutput.Cells[2, 1].Value = "Ryan";
workSheetOutput.Cells[2, 2].Value = "Chen";
workSheetOutput.Cells[2, 3].Value = 120000;
workSheetOutput.Cells[2, 4].Value = 10;
workSheetOutput.Cells[2, 5].Value = "01 March - 31 March";
var output = PaySlipWorker.GeneratePaySlipsExcel(excelExport, States.NSW);
ExcelWorksheet workSheet = output.Workbook.Worksheets.First();
var maxColumnCount = workSheet.Dimension.End.Column;
var row = workSheet.Cells[2, 1, 2, maxColumnCount];
var paySlipOutput = new EngineOutput()
{
Name = Convert.ToString(((object[, ])row.Value)[0, 0]),
GrossIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 1]),
IncomeTax = Convert.ToDecimal(((object[, ])row.Value)[0, 2]),
NetIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 3]),
Super = Convert.ToDecimal(((object[, ])row.Value)[0, 4]),
PayPeriod = Convert.ToString(((object[, ])row.Value)[0, 5])
};
Assert.IsTrue(paySlipOutput.Name.Equals("Ryan Chen") &&
paySlipOutput.GrossIncome == 10000 &&
paySlipOutput.IncomeTax == 2669 &&
paySlipOutput.NetIncome == 7331 &&
paySlipOutput.Super == 1000);
}
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);
}
