Add Excel export for institution contract and contracting party details

- Implement InstitutionContractDetailExcelGenerator and InstitutionContractLegalExcelGenerator for generating Excel files of contract parties (individual and legal)
- Add methods to Index.cshtml.cs to fetch and export contract party details within 6 months of contract end
- Update OnPostShiftDateNew to return contracting party Excel export
- Refactor usings to include new Excel generators

CompanyManagement.Infrastructure.Excel/ContractingParty/InstitutionContractDetailExcelGenerator.cs

@@ -0,0 +1,86 @@
+using System.Collections.Generic;
+using OfficeOpenXml;
+using OfficeOpenXml.Style;
+using System.Drawing;
+
+namespace CompanyManagement.Infrastructure.Excel.ContractingParty;
+public static class InstitutionContractDetailExcelGenerator
+{
+    public static byte[] Generate(List<InstitutionContractDetailExcelViewModel> items)
+    {
+        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
+        using var package = new ExcelPackage();
+        var worksheet = package.Workbook.Worksheets.Add("جزئیات طرف قرارداد");
+
+        // Headers (فارسی)
+        worksheet.Cells[1, 1].Value = "نام";
+        worksheet.Cells[1, 2].Value = "نام خانوادگی";
+        worksheet.Cells[1, 3].Value = "کد ملی";
+        worksheet.Cells[1, 4].Value = "تاریخ تولد";
+        worksheet.Cells[1, 5].Value = "شماره تماس";
+        worksheet.Cells[1, 6].Value = "شماره ملی";
+
+        // Header style
+        using (var headerRange = worksheet.Cells[1, 1, 1, 6])
+        {
+            headerRange.Style.Font.Bold = true;
+            headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
+            headerRange.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
+            headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
+            headerRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
+            headerRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
+        }
+
+        // Data rows
+        int row = 2;
+        if (items != null)
+        {
+            foreach (var it in items)
+            {
+                worksheet.Cells[row, 1].Value = it.FirstName;
+                worksheet.Cells[row, 2].Value = it.LastName;
+                worksheet.Cells[row, 3].Value = it.NationalCode;
+                worksheet.Cells[row, 4].Value = it.BirthDate;
+                worksheet.Cells[row, 5].Value = it.PhoneNumber;
+                worksheet.Cells[row, 6].Value = it.NationalNumber;
+
+                using (var dataRange = worksheet.Cells[row, 1, row, 6])
+                {
+                    dataRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; // راست‌چین
+                    dataRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
+                    dataRange.Style.WrapText = true;
+                }
+
+                row++;
+            }
+        }
+
+        // Auto-fit columns to largest content
+        if (worksheet.Dimension != null)
+        {
+            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
+        }
+
+        // Set sheet to RTL for Persian
+        worksheet.View.RightToLeft = true;
+
+        return package.GetAsByteArray();
+    }
+}
+
+public class InstitutionContractDetailExcelViewModel
+{
+    public string FirstName { get; set; }
+    public string LastName { get; set; }
+    public string NationalCode { get; set; }
+    public string BirthDate { get; set; } // accept Persian date strings
+    public string PhoneNumber { get; set; }
+    public string NationalNumber { get; set; }
+}

CompanyManagement.Infrastructure.Excel/ContractingParty/InstitutionContractLegalExcelGenerator.cs

@@ -0,0 +1,93 @@
+using System.Collections.Generic;
+using OfficeOpenXml;
+using OfficeOpenXml.Style;
+using System.Drawing;
+
+namespace CompanyManagement.Infrastructure.Excel.ContractingParty;
+public static class InstitutionContractLegalExcelGenerator
+{
+    public static byte[] Generate(List<InstitutionContractLegalExcelViewModel> items)
+    {
+        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
+        using var package = new ExcelPackage();
+        var worksheet = package.Workbook.Worksheets.Add("جزئیات حقوقی طرف قرارداد");
+
+        // Headers (فارسی)
+        worksheet.Cells[1, 1].Value = "نام شرکت";
+        worksheet.Cells[1, 2].Value = "نام";
+        worksheet.Cells[1, 3].Value = "نام خانوادگی";
+        worksheet.Cells[1, 4].Value = "کد ملی";
+        worksheet.Cells[1, 5].Value = "شماره ثبت";
+        worksheet.Cells[1, 6].Value = "تاریخ تولد";
+        worksheet.Cells[1, 7].Value = "شماره تماس";
+        worksheet.Cells[1, 8].Value = "شماره ملی";
+
+        // Header style
+        using (var headerRange = worksheet.Cells[1, 1, 1, 8])
+        {
+            headerRange.Style.Font.Bold = true;
+            headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
+            headerRange.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
+            headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
+            headerRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
+            headerRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
+            headerRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
+        }
+
+        // Data rows
+        int row = 2;
+        if (items != null)
+        {
+            foreach (var it in items)
+            {
+                worksheet.Cells[row, 1].Value = it.CompanyName;
+                worksheet.Cells[row, 2].Value = it.FirstName;
+                worksheet.Cells[row, 3].Value = it.LastName;
+                worksheet.Cells[row, 4].Value = it.NationalCode;
+                worksheet.Cells[row, 5].Value = it.RegistrationNumber;
+                worksheet.Cells[row, 6].Value = it.BirthDate;
+                worksheet.Cells[row, 7].Value = it.PhoneNumber;
+                worksheet.Cells[row, 8].Value = it.NationalNumber;
+
+                using (var dataRange = worksheet.Cells[row, 1, row, 8])
+                {
+                    dataRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
+                    dataRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; // راست‌چین
+                    dataRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
+                    dataRange.Style.WrapText = true;
+                }
+
+                row++;
+            }
+        }
+
+        // Auto-fit columns to largest content
+        if (worksheet.Dimension != null)
+        {
+            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
+        }
+
+        // Set sheet to RTL for Persian
+        worksheet.View.RightToLeft = true;
+
+        return package.GetAsByteArray();
+    }
+}
+
+public class InstitutionContractLegalExcelViewModel
+{
+    public string CompanyName { get; set; }
+    public string FirstName { get; set; }
+    public string LastName { get; set; }
+    public string NationalCode { get; set; }
+    public string RegistrationNumber { get; set; }
+    public string BirthDate { get; set; }
+    public string PhoneNumber { get; set; }
+    public string NationalNumber { get; set; }
+}
+

ServiceHost/Areas/AdminNew/Pages/Company/AndroidApk/Index.cshtml.cs

@@ -2,10 +2,8 @@
 using _0_Framework.Domain.CustomizeCheckoutShared.Enums;
 using AccountManagement.Domain.AccountLeftWorkAgg;
 using AccountMangement.Infrastructure.EFCore;
-using Company.Domain.AndroidApkVersionAgg;
 using Company.Domain.CustomizeCheckoutAgg.ValueObjects;
 using Company.Domain.CustomizeCheckoutTempAgg.ValueObjects;
-using Company.Domain.RewardAgg;
 using Company.Domain.RollCallAgg.DomainService;
 using CompanyManagment.App.Contracts.AndroidApkVersion;
 using CompanyManagment.EFCore;
@@ -13,24 +11,17 @@ using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
 using Microsoft.AspNetCore.Mvc.RazorPages;
 using Microsoft.EntityFrameworkCore;
-using System.Net.Http;
-using System.Security.Cryptography.Xml;
 using System.Text.Json.Serialization;
 using _0_Framework.Application.PaymentGateway;
 using Company.Domain.InstitutionContractAgg;
-using Company.Domain.InstitutionPlanAgg;
 using Company.Domain.PaymentTransactionAgg;
-using CompanyManagment.App.Contracts.InstitutionContract;
 using CompanyManagment.App.Contracts.PaymentTransaction;
 using CompanyManagment.App.Contracts.TemporaryClientRegistration;
 using Microsoft.Extensions.Options;
 using Parbad;
-using Parbad.AspNetCore;
-using Parbad.Gateway.Sepehr;
 using System.ComponentModel.DataAnnotations;
-using _0_Framework.Application.Enums;
+using CompanyManagement.Infrastructure.Excel.ContractingParty;
 using CompanyManagement.Infrastructure.Excel.WorkshopsRollCall;
-using static ServiceHost.Areas.AdminNew.Pages.Company.AndroidApk.IndexModel2;
 
 namespace ServiceHost.Areas.AdminNew.Pages.Company.AndroidApk
 {
@@ -136,7 +127,9 @@ namespace ServiceHost.Areas.AdminNew.Pages.Company.AndroidApk
 
         public async Task<IActionResult> OnPostShiftDateNew()
         {
-            await UpdateInstitutionContract();
+            var bytes = generateContractingExcel();
+            return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+                "ContractingParties.xlsx");
             ViewData["message"] = "تومام یک";
             return Page();
         }
@@ -961,6 +954,79 @@ namespace ServiceHost.Areas.AdminNew.Pages.Company.AndroidApk
             await transaction.CommitAsync();
         }
 
+        #endregion
+
+        #region MyRegion
+
+        public byte[] generateContractingExcel()
+        {
+            var now = DateTime.Now;
+            var next6Month = now.AddMonths(6);
+            var institutionContracts = _context.InstitutionContractSet.Where(x => x.ContractEndGr
+                        <= next6Month && x.ContractStartGr <= now)
+                    .Join(_context.PersonalContractingParties.Where(x => x.IsLegal == "حقیقی"),
+                        contract => contract.ContractingPartyId,
+                        contractingParty => contractingParty.id,
+                        (contract, contractingParty) => new { contract, contractingParty })
+                    .GroupJoin(_context.Employers.Where(x => x.IsLegal == "حقیقی"),
+                        combined => combined.contractingParty.Nationalcode,
+                        employer => employer.Nationalcode,
+                        (combined, employer) =>
+                            new { combined.contract, combined.contractingParty, employer })
+                    .SelectMany(x=>x.employer.DefaultIfEmpty(),(x,employer)=> new {x.contract, x.contractingParty, employer})
+                    .ToList();
+                
+            
+            var data = institutionContracts.Select(x => new InstitutionContractDetailExcelViewModel()
+            {
+                FirstName = x.contractingParty.FName ?? x.employer?.FName ?? "",
+                LastName = x.contractingParty.LName ?? x.employer?.LName ?? "",
+                NationalCode = x.contractingParty.Nationalcode ?? x.employer?.Nationalcode ?? "",
+                BirthDate = x.contractingParty.DateOfBirth.HasValue
+                    ? x.contractingParty.DateOfBirth.ToFarsi()
+                    : (x.employer?.DateOfBirth != null ? x.employer.DateOfBirth.ToFarsi() : ""),
+                NationalNumber = x.contractingParty.IdNumber ?? x.employer?.IdNumber ?? "",
+                PhoneNumber = x.contractingParty.Phone ?? x.employer?.Phone ?? "",
+            }).ToList().DistinctBy(x => x.NationalCode).ToList();
+
+            return InstitutionContractDetailExcelGenerator.Generate(data);
+        }
+        public byte[] generateContractingLegalExcel()
+        {
+            var now = DateTime.Now;
+            var next6Month = now.AddMonths(6);
+            var institutionContracts = _context.InstitutionContractSet.Where(x => x.ContractEndGr
+                    <= next6Month && x.ContractStartGr <= now)
+                .Join(_context.PersonalContractingParties.Where(x => x.IsLegal == "حقوقی"),
+                    contract => contract.ContractingPartyId,
+                    contractingParty => contractingParty.id,
+                    (contract, contractingParty) => new { contract, contractingParty })
+                .GroupJoin(_context.Employers.Where(x => x.IsLegal == "حقوقی"),
+                    combined => combined.contractingParty.NationalId,
+                    employer => employer.NationalId,
+                    (combined, employer) =>
+                        new { combined.contract, combined.contractingParty, employer })
+                .SelectMany(x=>x.employer.DefaultIfEmpty(),(x,employer)=> new {x.contract, x.contractingParty, employer})
+                .ToList();
+                
+            var data = institutionContracts.Select(x => new InstitutionContractLegalExcelViewModel()
+                {
+                    FirstName = x.contractingParty.CeoFName??x.employer?.FName??"",
+                    LastName = x.contractingParty.CeoLName??x.employer?.EmployerLName??"",
+                    NationalCode = x.contractingParty.Nationalcode??x.employer?.Nationalcode??"",
+                    BirthDate = x.contractingParty.DateOfBirth.HasValue?
+                        x.contractingParty.DateOfBirth.ToFarsi():
+                        x.employer?.DateOfBirth.ToFarsi()??"",
+                    NationalNumber =  x.contractingParty.NationalId?? x.employer?.NationalId??"",
+                    PhoneNumber = x.contractingParty.Phone?? x.employer?.Phone??"",
+                    CompanyName = x.contractingParty.LName?? x.employer?.LName??"",
+                    RegistrationNumber = x.contractingParty.RegisterId?? x.employer?.RegisterId??"",
+                }).ToList().DistinctBy(x=>x.NationalNumber).ToList();
+
+            return InstitutionContractLegalExcelGenerator.Generate(data);
+        }
+        
+
         #endregion
     }