private void SetTitle(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
data.SetText(rowIndex, "A", "請求残高年齢表");
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "I"), cfidxTitle);
if (option.RequireDepartmentSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"請求部門コード：{item.DepartmentCode} {item.DepartmentName}");
}
if (option.RequireStaffSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"担当者コード ：{item.StaffCode} {item.StaffName}");
}
rowIndex++;
rowIndex++;
data.SetText(rowIndex, "A", "得意先", cfidxHA);
data.SetStyle(rowIndex, "B", cfidxHB);
data.SetText(rowIndex, "C", "前月請求残", cfidxHCI);
data.SetText(rowIndex, "D", "当月売上高", cfidxHCI);
data.SetText(rowIndex, "E", "当月消込", cfidxHCI);
data.SetText(rowIndex, "F", "当月請求残", cfidxHCI);
data.SetText(rowIndex, "G", option.MonthlyRemain0, cfidxHCI);
data.SetText(rowIndex, "H", option.MonthlyRemain1, cfidxHCI);
data.SetText(rowIndex, "I", option.MonthlyRemain2, cfidxHCI);
data.SetText(rowIndex, "J", option.MonthlyRemain3, cfidxHJ);
}
private void SetDetail(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
var isChildCustomer = item.ParentCustomerFlag != 1 && item.ParentCustomerId.HasValue && option.ConsiderCustomerGroup;
var isParentCustomer = item.ParentCustomerFlag == 1 && option.ConsiderCustomerGroup;
var customerInfo = string.Concat(
isChildCustomer ? " " : "",
DisplayCustomerCode ? $"{item.CustomerCode} " : "",
item.CustomerName);
data.SetText(rowIndex, "A", customerInfo, cfidxDA);
var caption = isParentCustomer ? "小計" : "";
data.SetText(rowIndex, "B", caption, cfidxDB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxDCJ);
data.SetNumber(rowIndex, "E", item.CurrentMonthReceipt, cfidxDCJ);
data.SetNumber(rowIndex, "F", item.CurrentMonthMatching, cfidxDCJ);
data.SetNumber(rowIndex, "G", item.CurrentMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "H", item.MonthlyRemain0, cfidxDCJ);
data.SetNumber(rowIndex, "I", item.MonthlyRemain1, cfidxDCJ);
data.SetNumber(rowIndex, "J", item.MonthlyRemain2, cfidxDCJ);
data.SetNumber(rowIndex, "K", item.MonthlyRemain3, cfidxDK);
}
private bool RequireDepartmentSubtotal(BillingAgingListSearch opt, BillingAgingList detailBuf, BillingAgingList detail)
{
return(opt.RequireDepartmentSubtotal &&
detailBuf != null &&
(detail == null ||
(opt.RequireDepartmentSubtotal && detailBuf.DepartmentId != detail.DepartmentId)
));
}
private bool RequireCustomerGroupSubtotal(BillingAgingListSearch opt, BillingAgingList detailBuf, BillingAgingList detail)
{
return(opt.ConsiderCustomerGroup &&
detailBuf != null &&
detailBuf.ParentCustomerId.HasValue &&
(detail == null ||
(detailBuf.ParentCustomerId != detail.ParentCustomerId ||
opt.RequireStaffSubtotal && detailBuf.StaffId != detail.StaffId ||
opt.RequireDepartmentSubtotal && detailBuf.DepartmentId != detail.DepartmentId)
));
}
private bool RequireBreak(BillingAgingListSearch opt, BillingAgingList item, BillingAgingList itemNext, uint rowIndex, uint lastBraekRowIndex)
{
const uint MaxRowCount = 44u;
return(itemNext != null &&
(
opt.RequireStaffSubtotal && item.RecordType == 1 && itemNext.RecordType == 0 ||
opt.RequireDepartmentSubtotal && item.RecordType == 2 && itemNext.RecordType == 0 ||
(rowIndex - lastBraekRowIndex) >= MaxRowCount
));
}
private void AddSubtotal(List <decimal> subtotal, BillingAgingList detail)
{
subtotal[(int)SubtotalField.LastRemain] += detail.LastMonthRemain ?? 0M;
subtotal[(int)SubtotalField.CurrentBilling] += detail.CurrentMonthSales;
subtotal[(int)SubtotalField.CurrentReceipt] += detail.CurrentMonthReceipt ?? 0M;
subtotal[(int)SubtotalField.CurrentMatching] += detail.CurrentMonthMatching;
subtotal[(int)SubtotalField.CurrentRemain] += detail.CurrentMonthRemain ?? 0M;
subtotal[(int)SubtotalField.MonthlyRemain0] += detail.MonthlyRemain0 ?? 0M;
subtotal[(int)SubtotalField.MonthlyRemain1] += detail.MonthlyRemain1 ?? 0M;
subtotal[(int)SubtotalField.MonthlyRemain2] += detail.MonthlyRemain2 ?? 0M;
subtotal[(int)SubtotalField.MonthlyRemain3] += detail.MonthlyRemain3 ?? 0M;
}
private void SetSubtotal(BillingAgingList detail, List <decimal> subtotal, decimal unit)
{
detail.LastMonthRemain = subtotal?[(int)SubtotalField.LastRemain] ?? 0M;
detail.CurrentMonthSales = subtotal?[(int)SubtotalField.CurrentBilling] ?? 0M;
detail.CurrentMonthReceipt = subtotal?[(int)SubtotalField.CurrentReceipt] ?? 0M;
detail.CurrentMonthMatching = subtotal?[(int)SubtotalField.CurrentMatching] ?? 0M;
detail.CurrentMonthRemain = subtotal?[(int)SubtotalField.CurrentRemain] ?? 0M;
detail.MonthlyRemain0 = subtotal?[(int)SubtotalField.MonthlyRemain0] ?? 0M;
detail.MonthlyRemain1 = subtotal?[(int)SubtotalField.MonthlyRemain1] ?? 0M;
detail.MonthlyRemain2 = subtotal?[(int)SubtotalField.MonthlyRemain2] ?? 0M;
detail.MonthlyRemain3 = subtotal?[(int)SubtotalField.MonthlyRemain3] ?? 0M;
TruncateValue(detail, unit);
}
private void detail_Format(object sender, EventArgs e)
{
current = BillingAgingList[RowIndex];
RowIndex++;
next = RowIndex < BillingAgingList.Count ? BillingAgingList[RowIndex] : null;
switch (current.RecordType)
{
case 0:
if (ConsiderCustomerGroup && current.ParentCustomerFlag != 1 && current.ParentCustomerId.HasValue)
{
txtParentCustomer.Text = DisplayCustomerCode
? $" {current.CustomerCode} {current.CustomerName}"
: $" {current.CustomerName}";
}
else
{
txtParentCustomer.Text = DisplayCustomerCode
? $"{current.CustomerCode} {current.CustomerName}"
: $"{current.CustomerName}";
}
detailBackColor.BackColor = Color.Transparent;
break;
case 1:
txtParentCustomer.Text = $"{current.StaffCode} {current.StaffName} 計";
detailBackColor.BackColor = Color.WhiteSmoke;
break;
case 2:
txtParentCustomer.Text = $"{current.DepartmentCode} {current.DepartmentName} 計";
detailBackColor.BackColor = Color.WhiteSmoke;
break;
case 3:
txtParentCustomer.Text = UseForeignCurrency == 1 ? "通貨計" : "総合計";
detailBackColor.BackColor = Color.WhiteSmoke;
break;
}
txtChildCustomer.Text = current.ParentCustomerFlag == 1 && ConsiderCustomerGroup ? "小計" : " ";
if (current.RecordType != 0 && next?.RecordType == 0)
{
detail.NewPage = NewPage.After;
}
else
{
detail.NewPage = NewPage.None;
}
}
private BillingAgingList GetSubtotal(BillingAgingList detail, List <decimal> subtotal, int subtotalType, decimal unit)
{
var result = new BillingAgingList();
result.RecordType = subtotalType;
result.StaffId = detail.StaffId;
result.StaffCode = detail.StaffCode;
result.StaffName = detail.StaffName;
result.DepartmentId = detail.DepartmentId;
result.DepartmentCode = detail.DepartmentCode;
result.DepartmentName = detail.DepartmentName;
result.CurrencyCode = detail.CurrencyCode;
SetSubtotal(result, subtotal, unit);
return(result);
}
private void SetSubtotal(SheetData data, BillingAgingListSearch opt, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
// 合計行
var caption
= item.RecordType == 1 ? $"{item.StaffCode} {item.StaffName} 計"
: item.RecordType == 2 ? $"{item.DepartmentCode} {item.DepartmentName} 計"
: item.RecordType == 3 ? (UseForeignCurrency ? "通貨計" : "総合計")
: string.Empty;
data.SetText(rowIndex, "A", caption, cfidxSA);
data.SetStyle(rowIndex, "B", cfidxSB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxSCI);
data.SetNumber(rowIndex, "E", item.CurrentMonthMatching, cfidxSCI);
data.SetNumber(rowIndex, "F", item.CurrentMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "G", item.MonthlyRemain0, cfidxSCI);
data.SetNumber(rowIndex, "H", item.MonthlyRemain1, cfidxSCI);
data.SetNumber(rowIndex, "I", item.MonthlyRemain2, cfidxSCI);
data.SetNumber(rowIndex, "J", item.MonthlyRemain3, cfidxSJ);
}
private void TruncateValue(BillingAgingList detail, decimal unit)
{
if (unit <= 1M)
{
return;
}
if (detail.LastMonthRemain.HasValue)
{
detail.LastMonthRemain = detail.LastMonthRemain.Value / unit;
}
detail.CurrentMonthSales = detail.CurrentMonthSales / unit;
if (detail.CurrentMonthReceipt.HasValue)
{
detail.CurrentMonthReceipt = detail.CurrentMonthReceipt.Value / unit;
}
detail.CurrentMonthMatching = detail.CurrentMonthMatching / unit;
if (detail.CurrentMonthRemain.HasValue)
{
detail.CurrentMonthRemain = detail.CurrentMonthRemain.Value / unit;
}
if (detail.MonthlyRemain0.HasValue)
{
detail.MonthlyRemain0 = detail.MonthlyRemain0.Value / unit;
}
if (detail.MonthlyRemain1.HasValue)
{
detail.MonthlyRemain1 = detail.MonthlyRemain1.Value / unit;
}
if (detail.MonthlyRemain2.HasValue)
{
detail.MonthlyRemain2 = detail.MonthlyRemain2.Value / unit;
}
if (detail.MonthlyRemain3.HasValue)
{
detail.MonthlyRemain3 = detail.MonthlyRemain3.Value / unit;
}
}
/// <summary>請求残高年齢表データ取得 オプションによって合計行計算も実施</summary>
/// <param name="SearchOption"></param>
/// <returns></returns>
/// <remarks>
/// 合計行の集計
/// TODO: next → 合計を端数処理、端数処理した値を合計 変えられるようにする
/// </remarks>
public async Task <IEnumerable <BillingAgingList> > GetAsync(BillingAgingListSearch searchOption,
IProgressNotifier notifier = null,
CancellationToken token = default(CancellationToken))
{
const int staffSubtotalType = 1;
const int departmentSubtotalType = 2;
const int grandTotalType = 3;
var opt = searchOption;
opt.InitializeYearMonthConditions();
var details = (await billingAgingListQueryProcessor.GetAsync(opt, notifier, token)).ToArray();
var useReceipt = opt.BillingRemainType == 1;
var unit = opt.UnitValue;
var list = new List <BillingAgingList>();
var pcusIndexBuf = (int?)null;
var stafSub = GetSubtotalList();
var deptSub = GetSubtotalList();
var pcusSub = GetSubtotalList();
var grndSub = GetSubtotalList();
BillingAgingList detailBuf = null;
foreach (var detail in details)
{
detail.CurrentMonthSales = detail.BillingAmount0;
detail.CurrentMonthReceipt = detail.ReceiptAmount0;
detail.CurrentMonthMatching = detail.MatchingAmount0;
detail.LastMonthRemain = detail.Balance + detail.BillingAmountK
- (useReceipt ? detail.ReceiptAmountK : detail.MatchingAmountK);
detail.CurrentMonthRemain = detail.LastMonthRemain + detail.CurrentMonthSales
- (useReceipt ? (detail.CurrentMonthReceipt ?? 0M) : detail.CurrentMonthMatching);
detail.MonthlyRemain0 = detail.BillingAmount0 - detail.BillingMatchingAmount0 + (useReceipt ? detail.MatchingAmount0 - detail.ReceiptAmount0 : 0M);
detail.MonthlyRemain1 = detail.BillingAmount1 - detail.BillingMatchingAmount1 + (useReceipt ? detail.MatchingAmount1 - detail.ReceiptAmount1 : 0M);
detail.MonthlyRemain2 = detail.BillingAmount2 - detail.BillingMatchingAmount2 + (useReceipt ? detail.MatchingAmount2 - detail.ReceiptAmount2 : 0M);
detail.MonthlyRemain3 = detail.BillingAmount3 - detail.BillingMatchingAmount3 + (useReceipt ? detail.MatchingAmount3 - detail.ReceiptAmount3 : 0M)
+ detail.BillingAmount4 - detail.BillingMatchingAmount4 + (useReceipt ? detail.MatchingAmount4 - detail.ReceiptAmount4 : 0M);
var skip = searchOption.ConsiderCustomerGroup &&
searchOption.BillingRemainType > 0 &&
detail.ParentCustomerId == detail.CustomerId &&
detail.CurrentMonthSales == 0M &&
detail.CurrentMonthMatching == 0M;
if (detail.LastMonthRemain == 0M &&
detail.CurrentMonthReceipt == 0M &&
detail.CurrentMonthRemain == 0M)
{
continue;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, detail))
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
ResetSubtotal(pcusSub);
pcusIndexBuf = null;
}
if (RequireStaffSubtotal(opt, detailBuf, detail))
{
list.Add(GetSubtotal(detailBuf, stafSub, staffSubtotalType, unit));
ResetSubtotal(stafSub);
}
if (RequireDepartmentSubtotal(opt, detailBuf, detail))
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentSubtotalType, unit));
ResetSubtotal(deptSub);
}
if (opt.ConsiderCustomerGroup &&
detail.ParentCustomerId.HasValue &&
(detailBuf?.ParentCustomerId != detail.ParentCustomerId ||
detailBuf?.StaffId != detail.StaffId ||
detailBuf?.DepartmentId != detail.DepartmentId))
{
var parent = GetSubtotal(detail, null, 0, unit);
parent.ParentCustomerId = detail.ParentCustomerId;
parent.CustomerId = detail.ParentCustomerId.Value;
parent.CustomerCode = detail.ParentCustomerCode;
parent.CustomerName = detail.ParentCustomerName;
parent.ParentCustomerFlag = 1;
list.Add(parent);
pcusIndexBuf = list.IndexOf(parent);
}
if (opt.RequireStaffSubtotal)
{
AddSubtotal(stafSub, detail);
}
if (opt.RequireDepartmentSubtotal)
{
AddSubtotal(deptSub, detail);
}
if (opt.ConsiderCustomerGroup && pcusIndexBuf.HasValue)
{
AddSubtotal(pcusSub, detail);
}
AddSubtotal(grndSub, detail);
if (opt.ConsiderCustomerGroup &&
opt.BillingRemainType > 0 &&
detail.ParentCustomerId.HasValue)
{
detail.LastMonthRemain = null;
detail.CurrentMonthReceipt = null;
detail.CurrentMonthRemain = null;
detail.MonthlyRemain0 = null;
detail.MonthlyRemain1 = null;
detail.MonthlyRemain2 = null;
detail.MonthlyRemain3 = null;
}
if (!skip)
{
list.Add(detail);
TruncateValue(detail, unit);
}
detailBuf = detail;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, null))
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
}
if (RequireStaffSubtotal(opt, detailBuf, null))
{
list.Add(GetSubtotal(detailBuf, stafSub, staffSubtotalType, unit));
}
if (RequireDepartmentSubtotal(opt, detailBuf, null))
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentSubtotalType, unit));
}
if (detailBuf != null)
{
list.Add(GetSubtotal(detailBuf, grndSub, grandTotalType, unit));
}
notifier?.UpdateState();
return(list);
}
public void Process(SpreadsheetDocument document)
{
Option.InitializeYearMonthConditions();
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workbook = (workbookPart.Workbook = new Workbook());
var worksheet = (worksheetPart.Worksheet = new Worksheet());
var bookstylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
var title = "請求残高年齢表";
var sheets = workbook.AppendChild(new Sheets());
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = title,
};
sheets.Append(sheet);
var decNumFmt = Precision == 0 ? 38 : 164;
var stylesheet = bookstylesPart.InitizalizeStylesheet(
initializeNumberingFormats: () => InitializeNumeringFormats(Precision),
initializeFonts: () => InitializeFonts(),
initializeFills: () => InitializeFills(),
initializeBorders: () => InitializeBorders(),
initializeCellFormats: () => InitializeCellFormats(decNumFmt)
);
var columns = new Columns();
columns.Append(new[] {
new Column {
Min = 1, Max = 1, Width = 43.13, CustomWidth = true
},
new Column {
Min = 2, Max = 2, Width = 5.71, CustomWidth = true
},
new Column {
Min = 3, Max = 10, Width = 12.85, CustomWidth = true
},
});
worksheet.Append(columns);
var data = new SheetData();
worksheet.Append(data);
if (!string.IsNullOrEmpty(Password))
{
worksheet.Append(new SheetProtection {
Password = Password.ToHash(),
Sheet = true,
Scenarios = true,
});
}
var rowIndex = 0u;
var rowbreakIndices = new List <uint>();
var lastBreakRowIndex = 0u;
BillingAgingList itemNext = null;
foreach (var item in Items)
{
var index = Items.IndexOf(item);
var indexNext = index + 1;
itemNext = indexNext < Items.Count ? Items[indexNext] : null;
var requireBreak = RequireBreak(Option, item, itemNext, rowIndex, lastBreakRowIndex);
if (lastBreakRowIndex == rowIndex)
{
SetTitle(data, Option, item, ref rowIndex);
}
if (item.RecordType == 0)
{
SetDetail(data, Option, item, ref rowIndex);
}
else
{
SetSubtotal(data, Option, item, ref rowIndex);
}
if (requireBreak)
{
rowbreakIndices.Add(rowIndex);
lastBreakRowIndex = rowIndex;
}
}
worksheet.Append(GetPageSetup());
var companyInfo = $"{Company.Code}：{Company.Name}";
var outputInfo = $"出力日時：{DateTime.Today:yyyy年MM月dd日}";
worksheet.Append(GetHeaderFooter(leftHeader: companyInfo, rightHeader: outputInfo));
if (rowbreakIndices.Any())
{
worksheet.Append(GetRowBreaks(rowbreakIndices));
}
workbook.Save();
}
