private bool RequireCustomerSubtotal(ArrearagesListSearch opt, ArrearagesList item, ArrearagesList itemBuf) => RequireCustomerSubtotal(opt) && itemBuf != null && (item == null || RequireDepartmentSubtotal(opt, item, itemBuf) || RequireStaffSubtotal(opt, item, itemBuf) || item.CustomerCode != itemBuf.CustomerCode);
public async Task <IActionResult> GetArrearagesSpreadsheet(ArrearagesListSearch option, CancellationToken token)
{
option.ReportSettings = (await reportSettingProcessor.GetAsync(option.CompanyId, nameof(PF0401), token)).ToList();
var content = await arrearagesListSpreadsheetProcessor.GetAsync(option, token);
return(File(content, SpreadsheetContentType, "arrearages-spreadsheet.xlsx"));
}
public async Task <HttpResponseMessage> GetArrearagesSpreadsheet(ArrearagesListSearch option, CancellationToken token)
{
option.ReportSettings = (await reportSettingProcessor.GetAsync(option.CompanyId, nameof(PF0401), token)).ToList();
var content = await arrearagesListSpreadsheetProcessor.GetAsync(option, token);
return(Request.GetSpreadsheetResponseMessage(content, "arrearages-spreadsheet.xlsx"));
}
public async Task <ActionResult <IEnumerable <ArrearagesList> > > ArrearagesList(ArrearagesListSearch option, CancellationToken token)
{
option.ReportSettings = (await reportSettingProcessor.GetAsync(option.CompanyId, nameof(PF0401), token)).ToList();
return((await arrearagesListProcessor.GetAsync(option, token)).ToArray());
}
public async Task <ArrearagesListsResult> ArrearagesListAsync(string SessionKey, int CompanyId, ArrearagesListSearch ArrearagesListSearch)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var reportSettingList = (await reportSettingProcessor.GetAsync(CompanyId, "PF0401", token)).ToList();
ArrearagesListSearch.CompanyId = CompanyId;
ArrearagesListSearch.ReportSettings = reportSettingList;
var searchResult = (await arrearagesListProcessor.GetAsync(ArrearagesListSearch, token)).ToList();
return new ArrearagesListsResult
{
ProcessResult = new ProcessResult {
Result = true
},
ArrearagesLists = searchResult,
};
}, logger));
}
public async Task <byte[]> GetAsync(ArrearagesListSearch option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var columnTask = columnNameSettingQueryProcessor.GetAsync(new ColumnNameSetting {
CompanyId = option.CompanyId, TableName = nameof(Billing),
}, token);
var settingTask = reportSettingQueryProcessor.GetAsync(option.CompanyId, ReportId, token);
var loadTask = arrearagesListQueryProcessor.GetAsync(option, token);
var ccyCode = string.IsNullOrEmpty(option.CurrencyCode) ? Constants.DefaultCurrencyCode : option.CurrencyCode;
var ccyTask = currencyGetByCodeQueryProcessor.GetByCodeAsync(option.CompanyId, ccyCode);
var tasks = new List <Task>()
{
companyTask, columnTask, loadTask, ccyTask
};
var requireSetting = !(option.ReportSettings?.Any() ?? false);
if (requireSetting)
{
tasks.Add(settingTask);
}
await Task.WhenAll(tasks);
var company = companyTask.Result.First();
var columns = columnTask.Result.ToList();
var items = loadTask.Result.ToList();
var precision = ccyTask.Result.Precision;
if (!items.Any())
{
return(null);
}
if (requireSetting)
{
option.ReportSettings = settingTask.Result.ToList();
}
var processor = option.CustomerSummaryFlag ?
(IProcessor) new ArrearagesListSummaryDocumentProcessor {
Company = company,
Items = items,
Precision = precision,
} : new ArrearagesListDocumentProcessor {
Company = company,
Items = items,
Precision = precision,
Note1 = columns.FirstOrDefault(x => x.ColumnName == nameof(ArrearagesList.Note1))?.DisplayColumnName ?? "備考1",
};
using (var stream = new MemoryStream())
{
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
processor.Process(document);
}
return(stream.ToArray());
}
}
private bool RequireDueAtSubtotal(ArrearagesListSearch opt) => opt?.ReportSettings.Any(x => x.DisplayOrder == DueAtTotal && x.ItemKey == "1") ?? false;
private bool RequireDepartmentSubtotal(ArrearagesListSearch opt, ArrearagesList item, ArrearagesList itemBuf) => RequireDepartmentSubtotal(opt) && itemBuf != null && (item == null || item.DepartmentCode != itemBuf.DepartmentCode);
public async Task <IEnumerable <ArrearagesList> > GetAsync(ArrearagesListSearch option, CancellationToken token = default(CancellationToken)) => await arrearagesListQueryProcessor.GetAsync(option);
public async Task <byte[]> GetAsync(ArrearagesListSearch option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var columnTask = columnNameSettingQueryProcessor.GetAsync(new ColumnNameSetting {
CompanyId = option.CompanyId, TableName = nameof(Billing),
}, token);
var settingTask = reportSettingQueryProcessor.GetAsync(option.CompanyId, ReportId, token);
var loadTask = arrearagesListQueryProcessor.GetAsync(option, token);
var tasks = new List <Task>()
{
companyTask, columnTask, loadTask
};
var requireSetting = !(option.ReportSettings?.Any() ?? false);
if (requireSetting)
{
tasks.Add(settingTask);
}
await Task.WhenAll(tasks);
var company = companyTask.Result.First();
var columns = columnTask.Result.ToList();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
if (requireSetting)
{
option.ReportSettings = settingTask.Result.ToList();
}
GrapeCity.ActiveReports.SectionReport report;
var title = $"滞留明細一覧表{DateTime.Today:yyyyMMdd}";
if (option.CustomerSummaryFlag)
{
var reportTemp = new ArrearagesCustomerListReport();
reportTemp.SetBasicPageSetting(company.Code, company.Name);
reportTemp.SetData(items, option.Precision, option.ReportSettings);
report = reportTemp;
}
else
{
var reportTemp = new ArrearagesListReport();
reportTemp.SetBasicPageSetting(company.Code, company.Name);
reportTemp.SetData(items, option.Precision, option.ReportSettings, columns);
report = reportTemp;
}
report.Name = title;
report.Run();
return(report.Convert());
}
public Task <IEnumerable <ArrearagesList> > GetAsync(ArrearagesListSearch option, CancellationToken token = default(CancellationToken))
{
// todo : cleanup magic number
var CalcBaseDate = option.ReportSettings[7].ItemKey;
var IncludeCalcBaseDate = option.ReportSettings[8].ItemKey;
var sqlWhere = "";
var query = "";
var orderBy = " ccy.DisplayOrder";
var orderByCustomerSummaryFlag = " ccy.DisplayOrder";
if (option.CustomerSummaryFlag == false && option.ExistsMemo)
{
sqlWhere = " AND bm.Memo IS NOT NULL";
}
if (option.CustomerSummaryFlag == false && !string.IsNullOrEmpty(option.BillingMemo))
{
option.BillingMemo = Sql.GetWrappedValue(option.BillingMemo);
sqlWhere += " AND bm.Memo LIKE @BillingMemo";
}
if (!string.IsNullOrEmpty(option.CurrencyCode))
{
sqlWhere += " AND ccy.Code = @CurrencyCode ";
}
if (!string.IsNullOrEmpty(option.DepartmentCodeFrom))
{
sqlWhere += " AND d.Code >= @DepartmentCodeFrom ";
}
if (!string.IsNullOrEmpty(option.DepartmentCodeTo))
{
sqlWhere += " AND d.Code <= @DepartmentCodeTo ";
}
if (!string.IsNullOrEmpty(option.StaffCodeFrom))
{
sqlWhere += " AND st.Code >= @StaffCodeFrom ";
}
if (!string.IsNullOrEmpty(option.StaffCodeTo))
{
sqlWhere += " AND st.Code <= @StaffCodeTo ";
}
if (!string.IsNullOrEmpty(option.StaffCodeFrom))
{
sqlWhere += " AND st.Code >= @StaffCodeFrom ";
}
//for order
if (option.ReportSettings[0].ItemKey == "1")
{
orderBy += " ,d.Code";
orderByCustomerSummaryFlag += " ,d.Code";
}
if (option.ReportSettings[1].ItemKey == "1")
{
orderBy += " ,st.Code";
orderByCustomerSummaryFlag += " ,st.Code";
}
if (option.ReportSettings[2].ItemKey == "1")
{
orderBy += ",cs.Code ";
orderByCustomerSummaryFlag += " ,cs.Code";
}
if (option.ReportSettings[3].ItemKey == "1")
{
orderBy += " ,b.DueAt ";
}
if (option.ReportSettings[5].ItemKey == "0" && option.ReportSettings[2].ItemKey == "0")
{
orderBy += " ,cs.Code";
orderByCustomerSummaryFlag += " ,cs.Code";
}
if (option.ReportSettings[5].ItemKey == "2")
{
orderBy += " ,b.Id";
}
if (option.ReportSettings[6].ItemKey == "0")
{
orderBy += " ,b.BilledAt";
}
if (option.ReportSettings[6].ItemKey == "1")
{
orderBy += " ,b.SalesAt";
}
if (option.ReportSettings[6].ItemKey == "2")
{
orderBy += " ,b.ClosingAt";
}
if (option.ReportSettings[3].ItemKey == "0" && option.ReportSettings[6].ItemKey == "3")
{
orderBy += " ,b.DueAt";
}
if (option.ReportSettings[6].ItemKey == "4")
{
orderBy += ",COALESCE(b.OriginalDueAt, b.DueAt)";
}
if (option.CustomerSummaryFlag)
{
query = @"SELECT
cs.Code [CustomerCode]
, cs.Name [CustomerName]
, ccy.Code [CurrencyCode]
, SUM(b.RemainAmount) [RemainAmount]
, SUM(b.OffsetAmount) [OffsetAmount]
, d.Code [DepartmentCode]
, d.Name [DepartmentName]
, st.Code [StaffCode]
, st.Name [StaffName]
, @BaseDate [BaseDate]
FROM Billing b
INNER JOIN Department d
ON b.DepartmentId = d.Id
INNER JOIN Staff st
ON b.StaffId = st.Id
INNER JOIN Customer cs
ON b.CustomerId = cs.Id
INNER JOIN Category ct
ON b.CollectCategoryId = ct.Id
INNER JOIN Currency ccy
ON b.CurrencyId = ccy.Id
WHERE b.CompanyId = @CompanyId
AND b.AssignmentFlag <> 2
AND b.DeleteAt IS NULL
AND b.InputType <> 3
AND CASE @CalcBaseDate
WHEN 0 THEN COALESCE(b.OriginalDueAt, b.DueAt)
WHEN 1 THEN b.DueAt
ELSE b.BilledAt END <= DATEADD(day, CASE @IncludeCalcBaseDate WHEN 1 THEN 0 ELSE -1 END, @BaseDate)
" + sqlWhere +
@" GROUP BY cs.Code, cs.Name, ccy.Code, d.Code, d.Name, st.Code, st.Name, ccy.DisplayOrder
ORDER BY"
+ orderByCustomerSummaryFlag;
}
else
{
query = @"SELECT
b.Id
, cs.Code [CustomerCode]
, cs.Name [CustomerName]
, ccy.Code [CurrencyCode]
, b.BilledAt
, b.SalesAt
, b.ClosingAt
, b.DueAt
, COALESCE(b.OriginalDueAt, b.DueAt) [OriginalDueAt]
, b.RemainAmount [RemainAmount]
, b.OffsetAmount [OffsetAmount]
, DATEDIFF(day,
CASE @CalcBaseDate
WHEN 0 THEN COALESCE(b.OriginalDueAt, b.DueAt)
WHEN 1 THEN b.DueAt
ELSE b.BilledAt END,
@BaseDate)
+ CASE @IncludeCalcBaseDate WHEN 1 THEN 1 ELSE 0 END [ArrearagesDayCount]
, ct.Code [CollectCategoryCode]
, ct.Name [CollectCategoryName]
, b.InvoiceCode
, b.Note1
, b.Note2
, b.Note3
, b.Note4
, bm.Memo
, cs.CustomerStaffName
, cs.Note [CustomerNote]
, cs.Tel
, d.Code [DepartmentCode]
, d.Name [DepartmentName]
, st.Code [StaffCode]
, st.Name [StaffName]
, @BaseDate [BaseDate]
FROM Billing b
INNER JOIN Department d
ON b.DepartmentId = d.Id
INNER JOIN Staff st
ON b.StaffId = st.Id
INNER JOIN Customer cs
ON b.CustomerId = cs.Id
INNER JOIN Category ct
ON b.CollectCategoryId = ct.Id
INNER JOIN Currency ccy
ON b.CurrencyId = ccy.Id
LEFT JOIN BillingMemo bm
ON b.Id = bm.BillingId
WHERE b.CompanyId = @CompanyId
AND b.AssignmentFlag <> 2
AND b.DeleteAt IS NULL
AND b.InputType <> 3
AND CASE @CalcBaseDate
WHEN 0 THEN COALESCE(b.OriginalDueAt, b.DueAt)
WHEN 1 THEN b.DueAt
ELSE b.BilledAt END <= DATEADD(day, CASE @IncludeCalcBaseDate WHEN 1 THEN 0 ELSE -1 END, @BaseDate)
" + sqlWhere +
@" ORDER BY"
+ orderBy;
}
return(dbHelper.GetItemsAsync <ArrearagesList>(query, new
{
CompanyId = option.CompanyId,
CalcBaseDate,
IncludeCalcBaseDate,
BaseDate = option.BaseDate,
BillingMemo = option.BillingMemo,
CurrencyCode = option.CurrencyCode,
DepartmentCodeFrom = option.DepartmentCodeFrom,
DepartmentCodeTo = option.DepartmentCodeTo,
StaffCodeFrom = option.StaffCodeFrom,
StaffCodeTo = option.StaffCodeTo
}, token));
}
