using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Options; using excel_pajak.Services; using excel_pajak.Models; using System.Text.Json; namespace excel_pajak; class Program { static void Main(string[] args) { // Initialize configuration var builder = new ConfigurationBuilder() .SetBasePath(AppContext.BaseDirectory) .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true); IConfigurationRoot configuration = builder.Build(); var schemaList = configuration.GetSection("SchemaList").Get>() ?? []; var year = configuration.GetValue("Year") ?? string.Empty; var connectionString = configuration.GetConnectionString("DefaultConnection") ?? string.Empty; var outputFolder = configuration.GetValue("ExcelSettings:OutputFolder") ?? string.Empty; var templatePath = configuration.GetValue("ExcelSettings:TemplatePath") ?? string.Empty; // Process each schema in the configuration var allEmployeeInfos = new List(); foreach (var schema in schemaList) { Console.WriteLine($"\nProcessing schema: {schema}"); string query = $""" select json_agg(row_to_json(t)) from ( select distinct e."NIK" as employee_number , '{schema}' as schema from {schema}."EmployeeReportPeriodics" erp inner join {schema}."Employees" e on erp."EmployeeId" =e."Id" inner join {schema}."EmployeeTaxStatuses" ets on e."EmployeeTaxStatusRefId" =ets."Id" where erp."PaymentPeriod" between '{year}01' and '{year}12' and ets."EmployeeTaxStatusCode" =1 and erp."TaxCalculationType" <>1 AND ( e."LastWorkingDate" IS NULL OR e."LastWorkingDate" >= extract(epoch from DATE '{year}-01-01')*1000 ) ) t """; var result = DatabaseService.ExecuteScalar(query, connectionString); if (result != null) { try { // Deserialize the JSON result to a list of EmployeeInfo objects var employeeInfos = JsonSerializer.Deserialize>(result); if (employeeInfos != null) { // Add the retrieved employees to the overall list allEmployeeInfos.AddRange(employeeInfos); Console.WriteLine($"Retrieved {employeeInfos.Count} employee records from schema '{schema}'"); } } catch (JsonException ex) { Console.WriteLine($"Error deserializing JSON from schema '{schema}': {ex.Message}"); } } else { Console.WriteLine($"No data returned from schema '{schema}'"); } } // Output the aggregated employee information to the console Console.WriteLine($"\nAggregated {allEmployeeInfos.Count} employee records from all schemas:"); foreach (var employeeInfo in allEmployeeInfos) { Console.WriteLine($"Employee Number: {employeeInfo.EmployeeNumber}, Schema: {employeeInfo.Schema}"); ExcelService.InitExcel($"{employeeInfo.EmployeeNumber}_{year}.xlsx", templatePath, outputFolder); string queryEmployeeData = $""" select json_agg(row_to_json(t)) from ( select e."NIK" as employee_number, e."Name" as employee_name,e."NPWP" as employee_npwp, (case when erp."TaxStatus"=1 then 'T0' when erp."TaxStatus"=2 then 'T1' when erp."TaxStatus"=3 then 'T2' when erp."TaxStatus"=4 then 'T3' when erp."TaxStatus"=5 then 'K0' when erp."TaxStatus"=6 then 'K1' when erp."TaxStatus"=7 then 'K2' when erp."TaxStatus"=8 then 'K3' end ) as tax_marital_status, ets."EmployeeTaxStatusCode" as employee_tax_status, erp."PaymentPeriod" as payment_period, erp."NPWPPemotong" as npwp_pemotong, (case erp."TaxCalculationType" when 2 then 'PaidByEmployee' when 3 then 'PaidAsAllowance' else 'PaidByCompany' end) as tax_type, erp."BasicSalary" as basic_salary, erp."Overtime" as overtime, erp."AllowanceRegular" as allowance, erp."Deductions" as deduction, erp."Natura" as benefit_in_kind, erp."Severance" as severance, erp."JKKCompany" as jkk, erp."JKMCompany" as jkm, erp."BPJSKesCompany" +erp."OtherInsuranceCompany" as bpjs_kesehatan_company, erp."JHTEmployee" as jht_emp, erp."JPEmployee" as jp_emp, 0 as pension_emp, erp."EmployeeCondition" as employee_condition, erp."ResignType" as resign_type, erp."AllowanceTaxRegular" as allowance_tax_regular, erp."TaxRegular" as tax_regular , erp."AllowanceIrregular" as allowance_irregular, 0 as benefit_in_kind_irregular, erp."AllowanceTaxIrregular" as allowance_tax_irregular, erp."TaxIrregular" as tax_irregular, 0 as severance_tax, 0 as allowance_severance_tax, erp."BegSalaryNetto" as beginning_salary_netto, erp."BegPPh21" as beginning_pph21 from {employeeInfo.Schema}."EmployeeReportPeriodics" erp inner join {employeeInfo.Schema}."Employees" e on erp."EmployeeId" = e."Id" inner join {employeeInfo.Schema}."EmployeeTaxStatuses" ets on e."EmployeeTaxStatusRefId" =ets."Id" where erp."TaxCalculationType" <>1 and erp."PaymentPeriod" between '{year}01' and '{year}12' and e."NIK" ='{employeeInfo.EmployeeNumber}' ) t """; string resultPayroll = DatabaseService.ExecuteScalar(queryEmployeeData, connectionString) ?? string.Empty; if (resultPayroll != "") { try { // Deserialize the JSON result to a list of EmployeeInfo objects var employeeInfos = JsonSerializer.Deserialize>(resultPayroll); if (employeeInfos != null) { string taxTypeBasal = employeeInfos.LastOrDefault(x => x.BasicSalary > 0)?.TaxType ?? "PaidByEmployee"; string taxMaritalStatus = employeeInfos.FirstOrDefault()?.TaxMaritalStatus ?? "T0"; string periodStart = employeeInfos.OrderBy(x => x.PaymentPeriod).FirstOrDefault()?.PaymentPeriod ?? ""; string periodEnd = employeeInfos.OrderBy(x => x.PaymentPeriod).LastOrDefault()?.PaymentPeriod ?? ""; ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", taxMaritalStatus, 3, 3); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", int.Parse(periodStart.Substring(4, 2)), 2, 7); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", int.Parse(periodEnd.Substring(4, 2)), 3, 7); List periodList = employeeInfos.Select(x => x.PaymentPeriod).Distinct().ToList() ?? new(); foreach (string period in periodList) { var employeeInfoDetail = employeeInfos.FirstOrDefault(x => x.PaymentPeriod == period && x.TaxType == taxTypeBasal); int month = int.Parse(period?.Substring(4, 2) ?? "01"); if (employeeInfoDetail != null) { ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BasicSalary, 6, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Overtime, 8, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Allowance, 7, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", Math.Abs(employeeInfoDetail.Deduction) * -1, 10, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BenefitInKind, 9, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Jkk, 11, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Jkm, 12, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BpjsKesehatanCompany, 13, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.JhtEmp, 18, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.JpEmp, 19, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.PensionEmp, 20, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceTaxRegular, 14, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceIrregular + employeeInfoDetail.BenefitInKindIrregular, 36, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceTaxIrregular, 37, month + 1); } employeeInfoDetail = employeeInfos.FirstOrDefault(x => x.PaymentPeriod == period && x.TaxType != taxTypeBasal); if (employeeInfoDetail != null) { ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BasicSalary, 52, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Overtime, 54, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Allowance, 53, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", Math.Abs(employeeInfoDetail.Deduction) * -1, 56, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BenefitInKind, 55, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Jkk, 57, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.Jkm, 58, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.BpjsKesehatanCompany, 59, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceTaxRegular, 60, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceIrregular + employeeInfoDetail.BenefitInKindIrregular, 85, month + 1); ExcelService.WriteExcel($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx", employeeInfoDetail.AllowanceTaxIrregular, 86, month + 1); } } ExcelService.RefreshFormulas($"{outputFolder}\\{employeeInfo.EmployeeNumber}_{year}.xlsx"); } } catch (JsonException ex) { Console.WriteLine($"Error deserializing JSON from schema '{employeeInfo.Schema}': {ex.Message}"); } } else { Console.WriteLine($"No data returned from schema '{employeeInfo.Schema}'"); } } } }