/// <summary>
/// Generates the settlement letter.
/// </summary>
/// <param name="overdraft">The overdraft.</param>
/// <param name="header">The header.</param>
/// <param name="footer">The footer.</param>
/// <param name="date">The date.</param>
/// <param name="newFile">The new file.</param>
public IWorkbook GenerateSettlementLetter(List <Overdraft> overdrafts, string header, string footer, string date,
string employeeName, OverdraftTotalsResult totals)
{
var rowIndex = 3;
var colIndex = 1;
var headerColIndex = 0;
var datRow = 0;
var dateCol = 5;
var signCol = 3;
IWorkbook workbook = new XSSFWorkbook();
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)FontBoldWeight.Bold;
ISheet sheet1 = workbook.CreateSheet("Carta Finiquito");
sheet1.SetColumnWidth(2, 5000);
sheet1.SetColumnWidth(5, 5000);
sheet1.SetColumnWidth(0, 1000);
sheet1.SetColumnWidth(3, 3000);
sheet1.SetColumnWidth(6, 3000);
//Date
IRow row = sheet1.CreateRow(datRow);
var cell = row.CreateCell(dateCol);
cell.SetCellValue(date);
//Header
CreateHeader(sheet1, workbook, row, font, cell, rowIndex, headerColIndex, header);
rowIndex += 5;
//perceptions deductions Title
CreateDetailsTitle(sheet1, workbook, row, font, cell, rowIndex, colIndex);
rowIndex++;
CreateRowSeparation(sheet1, row, rowIndex);
rowIndex++;
//perceptions deductions header
CreateDetailsHeader(sheet1, workbook, row, font, rowIndex, colIndex);
rowIndex++;
rowIndex = CreateOverDraftDetails(overdrafts, sheet1, workbook, row, font, rowIndex, colIndex);
rowIndex++;
CreateSums(sheet1, workbook, row, font, cell, rowIndex, colIndex, totals);
rowIndex++;
CreateNetPayment(sheet1, workbook, row, font, cell, rowIndex, colIndex, totals);
rowIndex += 4;
Createfooter(sheet1, workbook, row, font, cell, rowIndex, headerColIndex, footer, signCol,
employeeName);
//workbook.Write(fs);
return(workbook);
}
private IWorkbook GenerateSettlementLetter(List <Overdraft> overdrafts, string header, string footer, string date, string employeeName,
OverdraftTotalsResult totals)
{
SettlementLetterMSSpreadsheet builder = new SettlementLetterMSSpreadsheet();
return(builder.GenerateSettlementLetter(overdrafts, header, footer, date, employeeName, totals));
}
private string ConcatenateHeader(string header, string header2, OverdraftTotalsResult totals)
{
CurrencyUtil cur = new CurrencyUtil();
var stringQuantity = cur.ToText(new CurrencyToTextParams()
{
CurrencyAmount = Math.Round(totals.Total, 2), CurrencyCode = "MXN", CurrencyName = "Pesos"
});
return(header + "$ " + Math.Round(totals.Total, 2) + " " + stringQuantity + header2);
}
private void roudAllValues(ref OverdraftTotalsResult overdraftTotalsResult, RoundUtil roundUtil)
{
overdraftTotalsResult.FixAmount = overdraftTotalsResult.FixAmount.ToCustomRound(roundUtil);
overdraftTotalsResult.Total = overdraftTotalsResult.Total.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalDeductionPayments = overdraftTotalsResult.TotalDeductionPayments.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalDeductionPaymentsISR = overdraftTotalsResult.TotalDeductionPaymentsISR.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalExempt = overdraftTotalsResult.TotalExempt.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalLiabilityPayments = overdraftTotalsResult.TotalLiabilityPayments.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalOtherDeductions = overdraftTotalsResult.TotalOtherDeductions.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalOtherPayments = overdraftTotalsResult.TotalOtherPayments.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalRetirementPensionWithdrawal = overdraftTotalsResult.TotalRetirementPensionWithdrawal.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalSalaryPayments = overdraftTotalsResult.TotalSalaryPayments.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalSalaryTotals = overdraftTotalsResult.TotalSalaryTotals.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalSeparationCompensation = overdraftTotalsResult.TotalSeparationCompensation.ToCustomRound(roundUtil);
overdraftTotalsResult.TotalTaxed = overdraftTotalsResult.TotalTaxed.ToCustomRound(roundUtil);
}
/// <summary>
/// Generates the settlement letter indicates writer.
/// </summary>
/// <param name="activeOverdraft">The active overdraft.</param>
/// <param name="identityWorkID">The identity work identifier.</param>
/// <param name="instanceID">The instance identifier.</param>
/// <param name="writer">The writer.</param>
/// <returns></returns>
public async Task <string> GenerateSettlementLetter(List <Overdraft> activeOverdrafts, Guid identityWorkID, Guid instanceID, string token, IMSSpreadsheetWriter writer)
{
var instanceMgr = new InstanceManager();
var instance = await instanceMgr.GetByIDAsync(token, instanceID);
var payrrollCompanyConfigurationMgr = new MiddlewareManager <PayrollCompanyConfiguration>(
new BaseRecordManager <PayrollCompanyConfiguration>(),
new PayrollCompanyConfigurationValidator());
var address = (await payrrollCompanyConfigurationMgr.FindByExpressionAsync(x => x.InstanceID == instanceID,
identityWorkID, new string[] { "Address" })).FirstOrDefault().Address;
var fullAddress = string.Empty;
if (address != null)
{
fullAddress = $" {address.Street}, {address.ZipCode}, {address.Municipality}, {address.FederalEntity}";
}
var overdraftManager = new OverdraftManager();
OverdraftTotalsResult totals = new OverdraftTotalsResult();
activeOverdrafts.ForEach(activeOverdraft =>
{
var totalSalaryPayments = activeOverdraft.OverdraftDetails.Where(p => p.ConceptPayment.ConceptType == ConceptType.SalaryPayment &&
p.ConceptPayment.Print &&
!p.ConceptPayment.Kind).
Select(y => y.Amount).Sum();
var totalDeductionPayments = activeOverdraft.OverdraftDetails.Where(p => p.ConceptPayment.ConceptType == ConceptType.DeductionPayment &&
p.ConceptPayment.Print &&
!p.ConceptPayment.Kind).
Select(y => y.Amount).Sum();
totals.TotalSalaryPayments += Math.Round(totalSalaryPayments, 2);
totals.TotalDeductionPayments += Math.Round(totalDeductionPayments, 2);
totals.Total += Math.Round(totals.TotalSalaryPayments - totals.TotalDeductionPayments, 2);
});
string header =
$"Recibí de la empresa {instance.Name} con domicilio en \n" +
$"{fullAddress} " +
$"la cantidad de: \n";
string header2 = " por concepto de mi finiquito con motivo de la terminación\n" +
"de mi relación laboral con la empresa con fecha indicada en el documento, cantidad \n" +
"que resulta de los siguientes conceptos:";
string footer =
"Así mismo manifiesto que hasta el momento en que se da por terminada la relación laboral\n" +
"no se me adeuda ninguna cantidad de dinero por concepto de salarios devengados,\n" +
"diferencia de los mismos, participacion de utilidades, comisiones, horas extras, \n" +
"vacaciones, septimos días, días festivos, prima dominical, vacacional y de antigüedad\n" +
"y demás prestaciones que otorga la Ley Federal Del Trabajo, ya que las mismas siempre\n" +
"me fueron íntegramente cubiertas en los términos de la ley. También hago constar para\n" +
"todos los efectos legales conducentes, que durante la vigencia de mi relación\n" +
"obrero-patronal no fui objeto de riesgo profesional alguno, motivo por el cual libero\n" +
"a mi patron de toda responsabilidad laboral y de seguridad\n" +
"social o de cualquier otro concpeto derivado del contrato de trabajo.";
string date = DateTime.Now.ToString("MM/dd/yyyy");
var newHeader = ConcatenateHeader(header, header2, totals);
var filename = "CartaFiniquito - " + activeOverdrafts.FirstOrDefault().Employee.FullName + ".xlsx";
IWorkbook wb = GenerateSettlementLetter(activeOverdrafts, newHeader, footer, date, activeOverdrafts.FirstOrDefault().Employee.FullName, totals);
var url = await WriteSettlementLetterAsync(instanceID, filename, wb, writer);
return(url);
}
private void CreateNetPayment(ISheet sheet1, IWorkbook workbook, IRow row, IFont font, ICell cell, int rowIndex, int colIndex, OverdraftTotalsResult totals)
{
var SalDeducStyle = workbook.CreateCellStyle();
SalDeducStyle.SetFont(font);
SalDeducStyle.BorderBottom = BorderStyle.Medium;
SalDeducStyle.BorderTop = BorderStyle.Medium;
SalDeducStyle.BorderLeft = BorderStyle.Medium;
SalDeducStyle.BorderRight = BorderStyle.Medium;
row = sheet1.CreateRow(rowIndex);
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex + 3, colIndex + 4));
var cell4 = row.CreateCell(colIndex + 3);
cell4.SetCellValue("Neto a pagar $");
cell4.CellStyle = SalDeducStyle;
var cell5 = row.CreateCell(colIndex + 4);
cell5.CellStyle = SalDeducStyle;
var cell6 = row.CreateCell(colIndex + 5);
cell6.SetCellValue((double)totals.Total);
cell6.CellStyle = SalDeducStyle;
}
private void CreateSums(ISheet sheet1, IWorkbook workbook, IRow row, IFont font, ICell cell, int rowIndex, int colIndex, OverdraftTotalsResult totals)
{
var SalDeducStyle = workbook.CreateCellStyle();
SalDeducStyle.SetFont(font);
SalDeducStyle.BorderBottom = BorderStyle.Medium;
SalDeducStyle.BorderTop = BorderStyle.Medium;
SalDeducStyle.BorderLeft = BorderStyle.Medium;
SalDeducStyle.BorderRight = BorderStyle.Medium;
row = sheet1.CreateRow(rowIndex);
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1));
cell = row.CreateCell(colIndex);
cell.SetCellValue("Suma de Percepciones $");
cell.CellStyle = SalDeducStyle;
var cell2 = row.CreateCell(colIndex + 1);
cell2.SetCellValue((double)totals.TotalSalaryPayments);
cell2.CellStyle = SalDeducStyle;
var cell3 = row.CreateCell(colIndex + 2);
cell3.SetCellValue((double)totals.TotalSalaryPayments);
cell3.CellStyle = SalDeducStyle;
sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex + 3, colIndex + 4));
var cell4 = row.CreateCell(colIndex + 3);
cell4.SetCellValue("Suma de Deducciones $");
cell4.CellStyle = SalDeducStyle;
var cell5 = row.CreateCell(colIndex + 4);
cell5.CellStyle = SalDeducStyle;
var cell6 = row.CreateCell(colIndex + 5);
cell6.SetCellValue((double)totals.TotalDeductionPayments);
cell6.CellStyle = SalDeducStyle;
}
public OverdraftTotalsResult GetTotals(Overdraft overdraft, RoundUtil roundUtil)
{
var overdraftTotalsResult = new OverdraftTotalsResult();
var concepts = overdraft.OverdraftDetails
.Where(p =>
p.ConceptPayment.Print && !p.ConceptPayment.Kind)
.Select(p => p.ConceptPayment);
if (overdraft.OverdraftDetails.Any())
{
//TotalSalaryPayments By Concepts
var salaryOverdraftDetails = overdraft.OverdraftDetails
.Where(p =>
p.ConceptPayment.ConceptType == ConceptType.SalaryPayment);
var deductionOverdraftDetails = overdraft.OverdraftDetails
.Where(p =>
p.ConceptPayment.ConceptType == ConceptType.DeductionPayment &&
p.ConceptPayment.Print &&
!p.ConceptPayment.Kind);
//WorkingDays // Días pagados / laborados
overdraftTotalsResult.WorkingDays = salaryOverdraftDetails
.Where(p =>
p.ConceptPayment.SATGroupCode == "P-001" ||
p.ConceptPayment.SATGroupCode == "P-046"
)
.Sum(p => p.Value);
//TotalGravado (que no sea indemnización / separación
overdraftTotalsResult.TotalTaxed = salaryOverdraftDetails
.Sum(p => roundUtil.RoundValue(p.Taxed));
//TotalGravado Settlement
overdraftTotalsResult.TotalTaxedSettlement = salaryOverdraftDetails
.Where(p => p.ConceptPayment.SATGroupCode == "P-022" ||
p.ConceptPayment.SATGroupCode == "P-023" ||
p.ConceptPayment.SATGroupCode == "P-025")
.Sum(p => roundUtil.RoundValue(p.Taxed));
//ajuste al neto
overdraftTotalsResult.FixAmount = GetOtherPayments(overdraft)
.Sum(p => p.Amount);
//TotalExento (que no sea indemnización / separación
overdraftTotalsResult.TotalExempt = salaryOverdraftDetails
.Sum(p => roundUtil.RoundValue(p.Exempt));
//Total Exento Settlement
overdraftTotalsResult.TotalExemptSettlement = salaryOverdraftDetails
.Where(p => p.ConceptPayment.SATGroupCode == "P-022" ||
p.ConceptPayment.SATGroupCode == "P-023" ||
p.ConceptPayment.SATGroupCode == "P-025")
.Sum(p => roundUtil.RoundValue(p.Exempt));
//TotalDeductionPayments -Excepts or payments
overdraftTotalsResult.TotalDeductionPayments = deductionOverdraftDetails
.Where(p =>
!p.ConceptPayment.SATGroupCode.Contains("OP") && p.ConceptPayment.Code != 99)
.Sum(p => roundUtil.RoundValue(p.Amount));
//Ajuste al neto
overdraftTotalsResult.AdjustmentAmount = deductionOverdraftDetails
.Where(p =>
p.ConceptPayment.Code == 99 &&
p.ConceptPayment.ConceptType == ConceptType.DeductionPayment)
.Sum(p => p.Amount);
//TotalLiabilityPayments
var overdraftLiabilities = overdraft.OverdraftDetails.Where(p => p.ConceptPayment.ConceptType == ConceptType.LiabilityPayment);
overdraftTotalsResult.TotalLiabilityPayments = overdraftLiabilities.Sum(p => roundUtil.RoundValue(p.Amount));
//Total de impuestos retenidos "002" (ISR)
var conceptDeductionPaymentISR = concepts
.Where(p =>
p.ConceptType == ConceptType.DeductionPayment &&
p.Code != 99 &&
p.SATGroupCode == "D-002");
var conceptDeductionPaymentISRIds = conceptDeductionPaymentISR.Select(p => p.ID);
if (conceptDeductionPaymentISRIds.Any())
{
overdraftTotalsResult.TotalDeductionPaymentsISR = overdraft.OverdraftDetails.Where(p =>
conceptDeductionPaymentISRIds.Contains(p.ConceptPaymentID)).Sum(p => roundUtil.RoundValue(p.Amount));
}
//Total de Indemnización
overdraftTotalsResult.TotalSeparationCompensation = salaryOverdraftDetails
.Where(p => p.ConceptPayment.SATGroupCode == "P-022" ||
p.ConceptPayment.SATGroupCode == "P-023" ||
p.ConceptPayment.SATGroupCode == "P-025")
.Sum(p => roundUtil.RoundValue(p.Amount));
overdraftTotalsResult.TotalSalaryPayments =
roundUtil.RoundValue(overdraftTotalsResult.TotalExempt)
+ roundUtil.RoundValue(overdraftTotalsResult.TotalTaxed)
+ roundUtil.RoundValue(overdraftTotalsResult.TotalSeparationCompensation);
//TotalSalaryTotals
//Total de Sueldos
overdraftTotalsResult.TotalSalaryTotals = salaryOverdraftDetails
.Where(p => p.ConceptPayment.SATGroupCode != "P-022" &&
p.ConceptPayment.SATGroupCode != "P-023" &&
p.ConceptPayment.SATGroupCode != "P-025" &&
p.ConceptPayment.SATGroupCode != "P-039" &&
p.ConceptPayment.SATGroupCode != "P-044"
)
.Sum(p => roundUtil.RoundValue(p.Amount));
//TotalOtherDeductions
overdraftTotalsResult.TotalOtherDeductions = roundUtil.RoundValue(overdraftTotalsResult.TotalDeductionPayments)
- roundUtil.RoundValue(overdraftTotalsResult.TotalDeductionPaymentsISR);
//Total OtherPayments
overdraftTotalsResult.TotalOtherPayments = GetOtherPayments(overdraft).DistinctBy(p => p.ConceptPaymentID)
.Sum(p => p.Amount);
//Total
overdraftTotalsResult.Total = roundUtil.RoundValue(overdraftTotalsResult.TotalSalaryPayments) -
roundUtil.RoundValue(overdraftTotalsResult.TotalDeductionPayments);
}
roudAllValues(ref overdraftTotalsResult, roundUtil);
return(overdraftTotalsResult);
}
