Kcf Wipro Dispatch Dynamic Report
Overview
The current Dispatch Report contains 32 columns. KFC has requested two customized versions of this report, with most of the required columns already present in the existing layout. By leveraging the dynamic report feature, we can define the exact columns and their order as per KFC’s requirements. Any additional fields can be seamlessly managed using the optional column logic. This approach eliminates the need for major code changes or separate report versions using conditional logic, enabling user-specific reports to be delivered efficiently from a single report configuration.
Repostiory , Model class is being used of SalesOrderAnalysisReport only for Unit Combo and other combo
Columns Required
The following table outlines the columns required for the dispatch report, along with their corresponding labels, properties, and SQL column names:
| Kcf Label | Label | Property | SqlColumnName | Order |
|---|---|---|---|---|
| INV.NO. | Invoice Sub No. | InvoiceSubNo | T.InvoiceSubNo | 1 |
| DATE | Invoice Date | InvoiceDate | T.InvoiceDate | 2 |
| PO NO. | Po No. | PoNo | T.PoNo | 3 |
| SR NO OF PO | Sr No Of Po | SrNoOfPo | O1.SrNoOfPo | 4 |
| PR NO | Pr No | PartyPrNo | O1.PartyPrNo | 5 |
| ITEM CODE | Item Id | ItemId | T.ItemId | 6 |
| SAP CODE | Party Item Code | PartyItemCode | PartyItemCode | 7 |
| QTY | Qty | Qty | T.Qty | 8 |
| UNIT RATE | Rate | Rate | T.Rate | 9 |
| Destination | Destination | BranchName | T.BranchName | 10 |
| L.R. No | Lr No | LrNo | T.LrNo | 11 |
| Transporter | Transporter | TransporterName | T.TransporterName | 12 |
| LR DATE | Lr Date | LrDate | T.LrDate | 13 |
User JSON Insert
The following SQL script demonstrates how to insert user-defined JSON configurations into the DynamicReportUserTable:
-- demo db
CREATE TABLE DynamicReportUserTable (
Id INT IDENTITY(1,1) PRIMARY KEY,
ReportName NVARCHAR(100),
JsonConfig NVARCHAR(MAX), -- We will have All the data in this multidimensional json Label, Properties,Order,Format
);
INSERT INTO DynamicReportUserTable (ReportName, JsonConfig)
VALUES
(
'Dispatch_Report',
'{
"Columns": [
{ "Label": "Invoice Sub No.", "Property": "InvoiceSubNo", "SqlColumnName": "T.InvoiceSubNo", "Order": 1 },
{ "Label": "Invoice Date", "Property": "InvoiceDate", "SqlColumnName": "T.InvoiceDate", "Order": 2 },
{ "Label": "Po No.", "Property": "PoNo", "SqlColumnName": "T.PoNo", "Order": 3 },
{ "Label": "Item Id", "Property": "ItemId", "SqlColumnName": "T.ItemId", "Order": 4 },
{ "Label": "Party Item Code", "Property": "PartyItemCode", "SqlColumnName": "PartyItemCode", "Order": 5 },
{ "Label": "Qty", "Property": "Qty", "SqlColumnName": "T.Qty", "Order": 6 },
{ "Label": "Rate", "Property": "Rate", "SqlColumnName": "T.Rate", "Order": 7 },
{ "Label": "Destination", "Property": "BranchName", "SqlColumnName": "T.BranchName", "Order": 8 },
{ "Label": "Lr No", "Property": "LrNo", "SqlColumnName": "T.LrNo", "Order": 9 },
{ "Label": "Transporter", "Property": "TransporterName", "SqlColumnName": "T.TransporterName", "Order": 10 },
{ "Label": "Lr Date", "Property": "LrDate", "SqlColumnName": "T.LrDate", "Order": 11 }
],
"PrintGrandTotal": "N"
}'
);Master Table JSON Insert
The following SQL script demonstrates how to insert master JSON configurations into the DynamicReportMasterTable:
--- ERPCrystalMfg
CREATE TABLE DynamicReportMasterTable (
Id INT IDENTITY(1,1) PRIMARY KEY,
ReportName NVARCHAR(100),
JsonConfig NVARCHAR(MAX) -- We will have All the data in this multidimensional json Label, Properties,Order,Format
);
INSERT INTO DynamicReportMasterTable (ReportName, JsonConfig)
VALUES
(
'Dispatch_Report',
'{
"Columns": [
{ "Label": "Unit Name", "Property": "UnitNameCode", "SqlColumnName": "T.UnitNameCode", "Order": 1 },
{ "Label": "System Invoice No", "Property": "InvoiceNo", "SqlColumnName": "T.InvoiceNo", "Order": 2 },
{ "Label": "Invoice (Sub) No", "Property": "InvoiceSubNo", "SqlColumnName": "T.InvoiceSubNo", "Order": 3 },
{ "Label": "Date", "Property": "InvoiceDate", "SqlColumnName": "T.InvoiceDate", "Order": 4 },
{ "Label": "Po-No", "Property": "PoNo", "SqlColumnName": "T.PoNo", "Order": 5 },
{ "Label": "Po-Date", "Property": "PoDate", "SqlColumnName": "T.PoDate", "Order": 6 },
{ "Label": "Effective Date", "Property": "EffectiveDate", "SqlColumnName": "T.EffectiveDate", "Order": 7 },
{ "Label": "Item Group", "Property": "ItemGroupName", "SqlColumnName": "T.ItemGroupName", "Order": 8 },
{ "Label": "Item Segment", "Property": "ItemSegmentName", "SqlColumnName": "T.ItemSegmentName", "Order": 9 },
{ "Label": "Item Id", "Property": "ItemId", "SqlColumnName": "T.ItemId", "Order": 10 },
{ "Label": "Item Short Name", "Property": "ItemShortName", "SqlColumnName": "T.ItemShortName", "Order": 11 },
{ "Label": "Item Name", "Property": "ItemName", "SqlColumnName": "T.ItemName", "Order": 12 },
{ "Label": "Party Item Code", "Property": "PartyItemCode", "SqlColumnName": "T.PartyItemCode", "Order": 13 },
{ "Label": "Transporter", "Property": "TransporterName", "SqlColumnName": "T.TransporterName", "Order": 14 },
{ "Label": "Vehicle Details", "Property": "VehicleDetails", "SqlColumnName": "T.VehicleDetails", "Order": 15 },
{ "Label": "Lr No", "Property": "LrNo", "SqlColumnName": "T.LrNo", "Order": 16 },
{ "Label": "Lr Date", "Property": "LrDate", "SqlColumnName": "T.LrDate", "Order": 17 },
{ "Label": "Qty", "Property": "Qty", "SqlColumnName": "T.Qty", "Order": 18 },
{ "Label": "Rate", "Property": "Rate", "SqlColumnName": "T.Rate", "Order": 19 },
{ "Label": "Basic Amount", "Property": "Amount", "SqlColumnName": "T.Amount", "Order": 20 },
{ "Label": "Tax", "Property": "TaxableAmount", "SqlColumnName": "T.TaxableAmount", "Order": 21 },
{ "Label": "Discount/Rounding", "Property": "DiscountRoundOff", "SqlColumnName": "T.DiscountRoundOff", "Order": 22 },
{ "Label": "Invoice Amount", "Property": "TotalAmount", "SqlColumnName": "T.TotalAmount", "Order": 23 },
{ "Label": "Aging", "Property": "Aging", "SqlColumnName": "T.Aging", "Order": 24 },
{ "Label": "Lead Time", "Property": "ItemLeadTime", "SqlColumnName": "T.ItemLeadTime", "Order": 25 },
{ "Label": "Party Group Name", "Property": "PartyGroupName", "SqlColumnName": "T.PartyGroupName", "Order": 26 },
{ "Label": "Party Short Name", "Property": "PartyShortName", "SqlColumnName": "T.PartyShortName", "Order": 27 },
{ "Label": "Party Name", "Property": "PartyName", "SqlColumnName": "T.PartyName", "Order": 28 },
{ "Label": "Branch", "Property": "BranchName", "SqlColumnName": "T.BranchName", "Order": 29 },
{ "Label": "Region", "Property": "RegionName", "SqlColumnName": "T.RegionName", "Order": 30 },
{ "Label": "E-Way Bill No", "Property": "EWayBillNo", "SqlColumnName": "T.EWayBillNo", "Order": 31 },
{ "Label": "Delivery Date", "Property": "DeliveryDate", "SqlColumnName": "T.DeliveryDate", "Order": 32 }
],
"PrintGrandTotal": "Y"
}'
);New Model Class for Dynamic Report
public class DynamicSalesReport
{
public DateTime? FromDate { get; set; } = DateTime.Now;
public DateTime? ToDate { get; set; } = DateTime.Now;
public string PartyGroupCode { get; set; } = string.Empty;
public string ReportType { get; set; } = string.Empty;
public string ReportFilter { get; set; } = string.Empty;
public string PartyGroupNameCode { get; set; } = string.Empty;
public string PartyId { get; set; } = string.Empty;
public string PartyShortNameId { get; set; } = string.Empty;
public string ItemGroupCode { get; set; } = string.Empty;
public string ItemGroupNameCode { get; set; } = string.Empty;
public string ItemId { get; set; } = string.Empty;
public string ItemShortNameId { get; set; } = string.Empty;
public string UnitCode { get; set; } = string.Empty;
public string UnitName { get; set; } = string.Empty;
public string XlFileName { get; set; } = string.Empty;
}
// Added in UtilityMethods model class
public class ColumnConfig
{
public string Property { get; set; } = string.Empty;
public string SqlColumnName { get; set; } = string.Empty;
public string Label { get; set; } = string.Empty;
public int Order { get; set; }
}
public class DynamicReportWrapper
{
public string PrintGrandTotal { get; set; } = string.Empty;
public List<ColumnConfig> Columns { get; set; } = new();
}
// public string Ind { get; set; } = string.Empty; -- IN OrderReport Option Page Code Snippet
@page "/dynamicsalesreportoption"
@inject IDynamicSalesReportService _IDynamicSalesReportService
<MudText Typo="Typo.h4">Sales Dynamic Report</MudText>
<EditForm Model="@_DynamicSalesReport" OnValidSubmit="DynamicSalesReportOptionPage">
<CustomValidation @ref="customValidation" />
<MudGrid>
<MudItem xs="12" sm="7">
<MudCard>
@if(isProcessing == true)
{
<MudAlert Severity="Severity.Info"><b>Please wait while your request is being processed...</b></MudAlert>
}
<MudCardContent>
<MudSelect T="string" Label="Report Type"
Value="_DynamicSalesReport.ReportType"
ValueChanged="@((string ReportType) => ReportTypeOnChange(ReportType))"
For="@(() => _DynamicSalesReport.ReportType)">
<MudSelectItem Value="@("A")">Dispatch Summary</MudSelectItem>
</MudSelect>
<MudSelect T="string" Value="_DynamicSalesReport.ReportFilter" Label="Report By"
ValueChanged="@((string ReportFilter) => ReportFilterOnChange(ReportFilter))"
For="@(() => _DynamicSalesReport.ReportFilter)">
<MudSelectItem Value="@("0")">All</MudSelectItem>
<MudSelectItem Value="@("1")">Party Group</MudSelectItem>
<MudSelectItem Value="@("2")">Party Name</MudSelectItem>
<MudSelectItem Value="@("3")">Item Group</MudSelectItem>
<MudSelectItem Value="@("4")">Item Name</MudSelectItem>
</MudSelect>
@if(_DynamicSalesReport.ReportFilter == "1")
{
<MudAutocomplete T="string" Label="Select Party Group" Placeholder="Type to search.."
@bind-Value="_DynamicSalesReport.PartyGroupNameCode" SearchFunc="@PartyGroupCombo"
ResetValueOnEmptyText="true" AdornmentIcon="@Icons.Material.Filled.Search" AdornmentColor="Color.Primary"
CoerceText="true" For="@(() => _DynamicSalesReport.PartyGroupNameCode)"/>
}
else if(_DynamicSalesReport.ReportFilter == "2")
{
<MudAutocomplete T="string" Label="Select Party Name" Placeholder="Type to search.."
@bind-Value="_DynamicSalesReport.PartyShortNameId" SearchFunc="@PartyCombo"
ResetValueOnEmptyText="true" AdornmentIcon="@Icons.Material.Filled.Search" AdornmentColor="Color.Primary"
CoerceText="true" For="@(() => _DynamicSalesReport.PartyShortNameId)"/>
}
else if(_DynamicSalesReport.ReportFilter == "3")
{
<MudAutocomplete T="string" Label="Select Item Group" Placeholder="Type to search.."
@bind-Value="_DynamicSalesReport.ItemGroupNameCode" SearchFunc="@ItemGroupCombo"
ResetValueOnEmptyText="true" AdornmentIcon="@Icons.Material.Filled.Search" AdornmentColor="Color.Primary"
CoerceText="true" For="@(() => _DynamicSalesReport.ItemGroupNameCode)"/>
}
else if(_DynamicSalesReport.ReportFilter == "4")
{
<MudAutocomplete T="string" Label="Select Item Short Name" Placeholder="Type to search.."
@bind-Value="_DynamicSalesReport.ItemShortNameId" SearchFunc="@ItemCombo"
ResetValueOnEmptyText="true" AdornmentIcon="@Icons.Material.Filled.Search" AdornmentColor="Color.Primary"
CoerceText="true" For="@(() => _DynamicSalesReport.ItemShortNameId)"/>
}
<MudSelect T="string" Dense="true" Label="Unit" Placeholder="Select Unit"
@bind-Value="_DynamicSalesReport.UnitCode" For="@(() => _DynamicSalesReport.UnitCode)">
@foreach (var listData in unitCombo)
{
<MudSelectItem Value="listData.UnitCode">@listData.UnitName</MudSelectItem>
}
</MudSelect>
<MudDatePicker Label="From Date" Class="mt-3" @bind-Date="_DynamicSalesReport.FromDate"
DateFormat="dd-MMM-yy" AnchorOrigin="MudBlazor.Origin.CenterCenter"
TransformOrigin="MudBlazor.Origin.CenterCenter" Validation="@(_DynamicSalesReport.FromDate)"
For="@(() => _DynamicSalesReport.FromDate)" AutoClose="true" PickerVariant="PickerVariant.Dialog"
DisableToolbar="true" @ref="_FromDate">
<PickerActions Context="editcontext">
<MudButton Class="mr-auto align-self-start" OnClick="CurrentDate1">Today</MudButton>
</PickerActions>
</MudDatePicker>
<MudDatePicker Label="To Date" Class="mt-3" @bind-Date="_DynamicSalesReport.ToDate" DateFormat="dd-MMM-yy"
AnchorOrigin="MudBlazor.Origin.CenterCenter" TransformOrigin="MudBlazor.Origin.BottomCenter"
Validation="@(_DynamicSalesReport.ToDate)" For="@(() => _DynamicSalesReport.ToDate)" AutoClose="true"
PickerVariant="PickerVariant.Dialog" DisableToolbar="true" @ref="_ToDate">
<PickerActions Context="editcontext">
<MudButton Class="mr-auto align-self-start" OnClick="CurrentDate2">Today</MudButton>
</PickerActions>
</MudDatePicker>
</MudCardContent>
<MudCardActions>
<MudStack Row="true">
<MudButton Disabled ="@isProcessing" ButtonType="MudBlazor.ButtonType.Submit" Variant="Variant.Filled"
Color="Color.Success" Class="ml-auto" StartIcon="@Icons.Material.Filled.Download">Print</MudButton>
</MudStack>
</MudCardActions>
</MudCard>
</MudItem>
</MudGrid>
</EditForm>
@code {
DynamicSalesReport _DynamicSalesReport = new();
private HttpResponseMessage? response;
private CustomValidation? customValidation;
MudDatePicker? _FromDate = new ();
MudDatePicker? _ToDate = new ();
Tuple<List<string>, List<string>, List<string>, List<string>> OrderAnalysisCombo =
new Tuple<List<string>, List<string>, List<string>, List<string>>
(new List<string>(), new List<string>(), new List<string>(), new List<string>());
List<DynamicSalesReport> unitCombo = new List<DynamicSalesReport> (new List<DynamicSalesReport>());
PostLogin _PostLogin = new ();
public bool isProcessing;
protected override async Task OnInitializedAsync()
{
_PostLogin = await _LocalSession.GetItemAsync<PostLogin>("userinfo");
var roleCnt = _IPostLoginService.GetUserRolesCnt(_PostLogin.userdb, "DynamicSalesReport", _PostLogin.dbname);
var userSessionId = _IPostLoginService.GetUserSessionId(_PostLogin.userdb);
userSessionId = userSessionId.Replace("\"","");
if(_PostLogin.BrowserSessionId != userSessionId)
{
NavManager.NavigateTo("/invaliduser/5");
}
else if(roleCnt > 0)
{
_DynamicSalesReport = await _IDynamicSalesReportService.DynamicSalesReportOption(_DynamicSalesReport, _PostLogin.yearlabel);
unitCombo = await _IDynamicSalesReportService.UnitCombo(_PostLogin.dbname);
}
else
{
NavManager.NavigateTo("/accessdenied/1");
}
}
public async Task CurrentDate1()
{
if(_FromDate != null)
{
await _FromDate.GoToDate(DateTime.Today);
}
}
public async Task CurrentDate2()
{
if(_ToDate != null)
{
await _ToDate.GoToDate(DateTime.Today);
}
}
public async void DynamicSalesReportOptionPage()
{
isProcessing = true;
StateHasChanged();
customValidation?.ClearErrors();
if(_DynamicSalesReport.ReportFilter == "1" && !string.IsNullOrEmpty(_DynamicSalesReport.PartyGroupNameCode))
{
_DynamicSalesReport.PartyGroupCode = _DynamicSalesReport.PartyGroupNameCode.Replace("[","").Replace("]","").Trim()[^2..];
}
else if (_DynamicSalesReport.ReportFilter == "2" && !string.IsNullOrEmpty(_DynamicSalesReport.PartyShortNameId))
{
_DynamicSalesReport.PartyId = _DynamicSalesReport.PartyShortNameId.Replace("[","").Replace("]","").Trim()[^6..];
}
else if(_DynamicSalesReport.ReportFilter == "3" && !string.IsNullOrEmpty(_DynamicSalesReport.ItemGroupNameCode))
{
_DynamicSalesReport.ItemGroupCode = _DynamicSalesReport.ItemGroupNameCode.Replace("[","").Replace("]","").Trim()[^3..];
}
else if(_DynamicSalesReport.ReportFilter == "4" && !string.IsNullOrEmpty(_DynamicSalesReport.ItemShortNameId))
{
_DynamicSalesReport.ItemId = _DynamicSalesReport.ItemShortNameId.Replace("[","").Replace("]","").Trim()[^6..];
}
response = await _IDynamicSalesReportService.DynamicSalesReportValidate(_DynamicSalesReport);
var errors = new Dictionary<string, List<string>>();
if(response.StatusCode != HttpStatusCode.OK)
{
errors = await response.Content.ReadFromJsonAsync<Dictionary<string, List<string>>>();
}
if (errors?.Count() > 0)
{
customValidation?.DisplayErrors(errors);
isProcessing = false;
StateHasChanged();
}
else
{
DynamicSalesReport DynamicSalesReportdata = new();
var userFileName = "";
if(_DynamicSalesReport.ReportType == "A")
{
DynamicSalesReportdata = await _IDynamicSalesReportService.
DynamicDispatchSummaryReport(_DynamicSalesReport, _PostLogin.dbname, _PostLogin.username, _PostLogin.yearlabel);
userFileName = "Dispatch_Report.xlsx";
}
isProcessing = false;
StateHasChanged();
var randomFileName = Path.GetFileName(DynamicSalesReportdata.XlFileName);
NavManager.NavigateTo($"filedownloader/downloadfile/{randomFileName}/{userFileName}/{_PostLogin.dbname}", true);
}
}
public async Task<IEnumerable<string>> PartyGroupCombo(string partygroup)
{
if (string.IsNullOrEmpty(partygroup.Trim()))
{
return new string[0];
}
else
{
if(OrderAnalysisCombo.Item1.Count() == 0)
{
OrderAnalysisCombo = await _IDynamicSalesReportService.OrderAnalysisCombo(_PostLogin.dbname);
}
return OrderAnalysisCombo.Item1.Where(x => x.Contains(partygroup, StringComparison.InvariantCultureIgnoreCase));
}
}
public async Task<IEnumerable<string>> PartyCombo(string partyname)
{
if (string.IsNullOrEmpty(partyname.Trim()))
{
return new string[0];
}
else
{
if(OrderAnalysisCombo.Item2.Count() == 0)
{
OrderAnalysisCombo= await _IDynamicSalesReportService.OrderAnalysisCombo(_PostLogin.dbname);
}
return OrderAnalysisCombo.Item2.Where(x => x.Contains(partyname, StringComparison.InvariantCultureIgnoreCase));
}
}
public async Task<IEnumerable<string>> ItemGroupCombo(string itemgroup)
{
if (string.IsNullOrEmpty(itemgroup.Trim()))
{
return new string[0];
}
else
{
if(OrderAnalysisCombo.Item3.Count() == 0)
{
OrderAnalysisCombo = await _IDynamicSalesReportService.OrderAnalysisCombo(_PostLogin.dbname);
}
return OrderAnalysisCombo.Item3.Where(x => x.Contains(itemgroup, StringComparison.InvariantCultureIgnoreCase));
}
}
public async Task<IEnumerable<string>> ItemCombo(string itemshortname)
{
if (string.IsNullOrEmpty(itemshortname.Trim()))
{
return new string[0];
}
else
{
if(OrderAnalysisCombo.Item4.Count() == 0)
{
OrderAnalysisCombo = await _IDynamicSalesReportService.OrderAnalysisCombo(_PostLogin.dbname);
}
return OrderAnalysisCombo.Item4.Where(x => x.Contains(itemshortname, StringComparison.InvariantCultureIgnoreCase));
}
}
public void ReportFilterOnChange(string ReportFilter)
{
_DynamicSalesReport.ReportFilter = ReportFilter;
_DynamicSalesReport.PartyGroupNameCode = "";
_DynamicSalesReport.PartyGroupCode = "";
_DynamicSalesReport.PartyShortNameId = "";
_DynamicSalesReport.PartyId = "";
_DynamicSalesReport.ItemGroupNameCode = "";
_DynamicSalesReport.ItemGroupCode = "";
_DynamicSalesReport.ItemShortNameId = "";
_DynamicSalesReport.ItemId = "";
}
public void ReportTypeOnChange(string ReportType)
{
_DynamicSalesReport.ReportType = ReportType;
_DynamicSalesReport.ReportFilter = "0";
_DynamicSalesReport.PartyGroupNameCode = "";
_DynamicSalesReport.PartyGroupCode = "";
_DynamicSalesReport.PartyShortNameId = "";
_DynamicSalesReport.PartyId = "";
_DynamicSalesReport.ItemGroupNameCode = "";
_DynamicSalesReport.ItemGroupCode = "";
_DynamicSalesReport.ItemShortNameId = "";
_DynamicSalesReport.ItemId = "";
_DynamicSalesReport.UnitCode = "00";
}
} Controller Code Snippet
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using ErpCrystal_MFG.Api.Repositories;
using ErpCrystal_MFG.Models;
using QuestPDF.Fluent;
using System.IO;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.Net.Http.Headers;
using ErpCrystal_MFG.CustomAttributes;
using System.Text.RegularExpressions;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.VisualStudio.Web.CodeGeneration.Utils;
namespace ErpCrystal_MFG.Api.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
[ApiKeyAuth]
public class DynamicSalesReportController(IUtilityMethodsRepository iutilitymethodsrepository,
ISalesOrderAnalysisReportRepository isalesorderanalysisreportrepository
) : ControllerBase
{
private readonly IUtilityMethodsRepository _IUtilityMethodsRepository = iutilitymethodsrepository;
private readonly ISalesOrderAnalysisReportRepository _ISalesOrderAnalysisReportRepository = isalesorderanalysisreportrepository;
[HttpPost("{yearLabel}")]
public async Task<ActionResult> DynamicSalesReportOption(DynamicSalesReport _DynamicSalesReport, string yearLabel)
{
var fyDates = await _IUtilityMethodsRepository.GetFYDates(yearLabel);
_DynamicSalesReport.FromDate = fyDates.fystart;
_DynamicSalesReport.ToDate = DateTime.Now.Date;
if (DateTime.Now.Date < fyDates.fystart || DateTime.Now.Date > fyDates.fyend)
{
_DynamicSalesReport.ToDate = fyDates.fyend;
}
_DynamicSalesReport.ReportFilter = "0";
_DynamicSalesReport.UnitCode = "00";
_DynamicSalesReport.ReportType = "A";
return Ok(_DynamicSalesReport);
}
[HttpPost]
public ActionResult DynamicSalesReportValidate(DynamicSalesReport _DynamicSalesReport)
{
if (_DynamicSalesReport.ToDate?.Date < _DynamicSalesReport.FromDate?.Date)
{
ModelState.AddModelError("ToDate", "Invalid Date Range");
}
else if (_DynamicSalesReport.ToDate?.Date > DateTime.Now.Date)
{
ModelState.AddModelError("ToDate", "Future Dates are not allowed");
}
else if (_DynamicSalesReport.ToDate > _DynamicSalesReport.FromDate?.AddYears(2))
{
ModelState.AddModelError("ToDate", "Gap between Date Range may not exceed 2 years");
}
if (_DynamicSalesReport.ReportFilter == "1" && string.IsNullOrEmpty(_DynamicSalesReport.PartyGroupCode))
{
ModelState.AddModelError("PartyGroupNameCode", "Party group needs to be selected");
}
else if (_DynamicSalesReport.ReportFilter == "2" && string.IsNullOrEmpty(_DynamicSalesReport.PartyId))
{
ModelState.AddModelError("PartyShortNameId", "Party name needs to be selected");
}
else if (_DynamicSalesReport.ReportFilter == "3" && string.IsNullOrEmpty(_DynamicSalesReport.ItemGroupCode))
{
ModelState.AddModelError("ItemGroupNameCode", "Item group needs to be selected");
}
else if (_DynamicSalesReport.ReportFilter == "4" && string.IsNullOrEmpty(_DynamicSalesReport.ItemId))
{
ModelState.AddModelError("ItemShortNameId", "Item name needs to be selected");
}
if (ModelState.IsValid)
{
return Ok();
}
return BadRequest(ModelState);
}
[HttpGet("{dbname}")]
public async Task<ActionResult> OrderAnalysisCombo(string dbname)
{
return Ok(await _ISalesOrderAnalysisReportRepository.OrderAnalysisCombo(dbname));
}
[HttpGet("{dbname}")]
public async Task<ActionResult> UnitCombo(string dbname)
{
return Ok(await _ISalesOrderAnalysisReportRepository.UnitCombo(dbname));
}
[HttpPost("{dbname},{userName},{yearLabel}")]
public async Task<ActionResult> DynamicDispatchSummaryReport
(DynamicSalesReport _DynamicSalesReport, string dbname, string userName, string yearLabel)
{
var xlFromDate = _DynamicSalesReport.FromDate?.ToString("dd-MMM-yy");
var xlToDate = _DynamicSalesReport.ToDate?.ToString("dd-MMM-yy");
var logId = _IUtilityMethodsRepository.InsertReportLog
(dbname, userName, "Dispatch Summary Report", yearLabel, $"From Date : {xlFromDate} To Date : {xlToDate}");
Random rnd = new();
var randomNumber = rnd.Next(1000000000).ToString().Trim();
var randomFileName = $"{randomNumber}.xlsx";
string pathDirName = $"\\MFGReports\\Reports\\{dbname}";
if (!Directory.Exists(pathDirName))
{
Directory.CreateDirectory(pathDirName);
}
var pathName = $"{pathDirName}\\{randomFileName}";
var pathFileName = Path.GetFullPath(pathName);
var companyData = await _IUtilityMethodsRepository.GetCompanyMstInfo(dbname);
// 🔹 Updated: Fetch both column list and PrintGrandTotal from Json
var dynamicReportData = await _IUtilityMethodsRepository.GetDynamicReportData(dbname, "Dispatch_Report");
var DynamicReportData = dynamicReportData.Columns.OrderBy(x => x.Order).ToList();
var printGrandTotal = dynamicReportData.PrintGrandTotal;
var sqlColumnNameList = string.Join(",", DynamicReportData.Select(x => x.SqlColumnName));
SalesOrderAnalysisReport _SalesOrderAnalysisReport = new();
_SalesOrderAnalysisReport.FromDate = _DynamicSalesReport.FromDate;
_SalesOrderAnalysisReport.ToDate = _DynamicSalesReport.ToDate;
_SalesOrderAnalysisReport.PartyGroupCode = _DynamicSalesReport.PartyGroupCode;
_SalesOrderAnalysisReport.ReportType = _DynamicSalesReport.ReportType;
_SalesOrderAnalysisReport.ReportFilter = _DynamicSalesReport.ReportFilter;
_SalesOrderAnalysisReport.PartyGroupNameCode = _DynamicSalesReport.PartyGroupNameCode;
_SalesOrderAnalysisReport.PartyId = _DynamicSalesReport.PartyId;
_SalesOrderAnalysisReport.PartyShortNameId = _DynamicSalesReport.PartyShortNameId;
_SalesOrderAnalysisReport.ItemGroupCode = _DynamicSalesReport.ItemGroupCode;
_SalesOrderAnalysisReport.ItemGroupNameCode = _DynamicSalesReport.ItemGroupNameCode;
_SalesOrderAnalysisReport.ItemId = _DynamicSalesReport.ItemId;
_SalesOrderAnalysisReport.ItemShortNameId = _DynamicSalesReport.ItemShortNameId;
_SalesOrderAnalysisReport.UnitCode = _DynamicSalesReport.UnitCode;
_SalesOrderAnalysisReport.UnitName = _DynamicSalesReport.UnitName;
_SalesOrderAnalysisReport.XlFileName = _DynamicSalesReport.XlFileName;
var allXlData = await _ISalesOrderAnalysisReportRepository.DispatchSummaryReport(_SalesOrderAnalysisReport, dbname);
var xlData = allXlData;
if(dynamicReportData.PrintGrandTotal == "N")
{
xlData = allXlData.Where(x => x.Ind != "Y").ToList();
}
// var xlData = allXlData.Where(x => x.Ind != "Y");
using var WorkbookXL = new XLWorkbook();
var worksheet = WorkbookXL.Worksheets.Add("Dispatch_Report");
worksheet.SheetView.FreezeRows(5);
worksheet.Cell(1, 1).Value = $"Company Name : {companyData.CompanyName} ";
worksheet.Cell(2, 1).Value = "Report Name : Dispatch Report";
worksheet.Cell(3, 1).Value = $"Report Date : {DateTime.Now.Date:dd-MMM-yy} ";
worksheet.Cell(4, 1).Value = $"Date Range : {xlFromDate} To {xlToDate}";
worksheet.Cell(5, 1).Value = "Unit Name";
for (int colIndex = 0; colIndex < DynamicReportData.Count; colIndex++)
{
worksheet.Cell(5, colIndex + 1).Value = DynamicReportData[colIndex].Label;
}
var lastColumn = DynamicReportData.Count;
worksheet.Range(5, 1, 5, lastColumn).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet.Range(1, 1, 5, lastColumn).Style.Font.Bold = true;
worksheet.Range(5, 1, 5, lastColumn).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, lastColumn).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, lastColumn).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, lastColumn).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
int i = 5;
// Iterate through each row of data from the report
foreach (var row in xlData)
{
i++;
// Loop through each column definition in the dynamic report data
// This allows us to dynamically map properties from the data object to the corresponding columns in the worksheet
for (int colIndex = 0; colIndex < DynamicReportData.Count; colIndex++)
{
// Retrieve the property of the current column from the data object
var prop = row.GetType().GetProperty(DynamicReportData[colIndex].Property);
var value = prop?.GetValue(row);
// Format the value based on its type and assign it to the corresponding cell in the worksheet
if (value is DateTime date)
{
worksheet.Cell(i, colIndex + 1).Value = date.ToString("dd-MMM-yy");
}
else if (value is decimal decimalValue)
{
worksheet.Cell(i, colIndex + 1).Value = decimalValue.ToString("##0.000;[Red](##0.000)");
}
else if (value is int intValue)
{
worksheet.Cell(i, colIndex + 1).Value = intValue;
}
else
{
worksheet.Cell(i, colIndex + 1).Value = value?.ToString();
}
}
}
worksheet.Columns().AdjustToContents(5); // Auto-adjust based on first 5 rows
// Now loop through each column to cap width at 40
foreach (var col in worksheet.ColumnsUsed())
{
col.AdjustToContents(5); // Only look at first 5 rows for performance
if (col.Width > 40)
{
col.Width = 40;
}
col.Style.Alignment.WrapText = true;
}
worksheet.Column(1).Width = 40;
WorkbookXL.SaveAs(pathFileName);
_DynamicSalesReport.XlFileName = randomFileName;
_IUtilityMethodsRepository.UpdateReportLogStatus(dbname, logId);
return Ok(_DynamicSalesReport);
}
}
}Service Code Snippet
using System.Collections.Generic;
using System.Threading.Tasks;
using ErpCrystal_MFG.Models;
namespace ErpCrystal_MFG.Web.Services;
public interface IDynamicSalesReportService
{
Task<DynamicSalesReport> DynamicSalesReportOption(DynamicSalesReport _DynamicSalesReport, string yearLabel);
Task<List<DynamicSalesReport>> UnitCombo(string dbName);
Task<Tuple<List<string>, List<string>, List<string>, List<string>>> OrderAnalysisCombo(string? dbname);
Task<HttpResponseMessage> DynamicSalesReportValidate(DynamicSalesReport _DynamicSalesReport);
Task<DynamicSalesReport> DynamicDispatchSummaryReport(DynamicSalesReport _DynamicSalesReport, string? dbName, string userName, string yearLabel);
}Utility
public async Task<DynamicReportWrapper> GetDynamicReportData(string dbname, string reportName)
{
var query = @"SELECT JsonConfig FROM DynamicReportUserTable
WHERE ReportName = @ReportName";
var connection = _DapperContext.SetClientConnection(dbname);
var result = await connection.QueryFirstOrDefaultAsync<string>(query, new { reportName });
if (string.IsNullOrWhiteSpace(result))
{
query = @"SELECT JsonConfig FROM DynamicReportMasterTable
WHERE ReportName = @ReportName";
var commonConnection = _DapperContext.SetSystemConnection();
result = await commonConnection.QueryFirstOrDefaultAsync<string>(query, new { reportName });
}
return JsonConvert.DeserializeObject<DynamicReportWrapper>(result) ?? new DynamicReportWrapper();
}Service Code Snippet
using System.Collections.Generic;
using System.Net.Http;
using System.Threading.Tasks;
using ErpCrystal_MFG.Models;
using Microsoft.AspNetCore.Components;
namespace ErpCrystal_MFG.Web.Services
{
public class DynamicSalesReportService : IDynamicSalesReportService
{
private readonly IHttpClientFactory _IHttpClientFactory;
private readonly HttpClient _HttpClient;
public DynamicSalesReportService(IHttpClientFactory httpClientfactory)
{
_IHttpClientFactory = httpClientfactory;
_HttpClient = _IHttpClientFactory.CreateClient("mfgapi");
_HttpClient.Timeout = TimeSpan.FromMinutes(10);
}
public async Task<DynamicSalesReport> DynamicSalesReportOption(DynamicSalesReport _DynamicSalesReport, string yearLabel)
{
var response = await _HttpClient.PostJsonAsync<DynamicSalesReport>
($"api/dynamicsalesreport/dynamicsalesreportoption/{yearLabel}", _DynamicSalesReport);
return response;
}
public async Task<HttpResponseMessage> DynamicSalesReportValidate(DynamicSalesReport _DynamicSalesReport)
{
var response = await _HttpClient.PostAsJsonAsync($"api/dynamicsalesreport/dynamicsalesreportvalidate", _DynamicSalesReport);
return response;
}
public async Task<Tuple<List<string>, List<string>, List<string>, List<string>>> OrderAnalysisCombo(string? dbname)
{
return await _HttpClient.GetFromJsonAsync<Tuple<List<string>, List<string>, List<string>, List<string>>>
($"api/salesorderanalysisreport/orderanalysiscombo/{dbname}") ?? new Tuple<List<string>, List<string>, List<string>, List<string>>
(new List<string>(), new List<string>(), new List<string>(), new List<string>());
}
public async Task<List<DynamicSalesReport>> UnitCombo(string? dbname)
{
return await _HttpClient.GetFromJsonAsync<List<DynamicSalesReport>>
($"api/dynamicsalesreport/unitcombo/{dbname}") ?? new List<DynamicSalesReport>();
}
public async Task<DynamicSalesReport> DynamicDispatchSummaryReport(DynamicSalesReport _DynamicSalesReport, string? dbname, string userName, string yearLabel)
{
var response = await _HttpClient.PostJsonAsync<DynamicSalesReport>
($"api/dynamicsalesreport/dynamicdispatchsummaryreport/{dbname},{userName},{yearLabel}", _DynamicSalesReport);
return response;
}
}
}