public List <ReceiveOrder> Serach(ReceiptSearch search) { var list = db.ReceiveOrders.Where(x => x.IsActive == true).Include(x => x.PurchaseOrder).ToList(); if (search.Name != null) { list.Where(x => x.PurchaseOrder.OrderNumber.ToLower().Equals(search.Name.ToLower())); } return(list); }
private async Task <ReceiptsResult> LoadReceiptAsync(ReceiptSearch options) { var useSectionFilter = UseSection && Sections.Count != SectionIdsInner.Count; if (useSectionFilter) { await Util.SaveWorkSectionTargetAsync(Login, ClientKey, SectionIdsInner.ToArray()); } return(await ServiceProxyFactory.DoAsync(async (ReceiptServiceClient client) => await client.GetItemsAsync(SessionKey, options))); }
public async Task <ReceiptsResult> GetItemsAsync(string SessionKey, ReceiptSearch ReceiptSearch) { return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token => { var result = (await receiptSearchProcessor.GetAsync(ReceiptSearch, token)).ToList(); return new ReceiptsResult { ProcessResult = new ProcessResult { Result = true }, Receipts = result, }; }, logger)); }
public async Task <ActionResult> SearchAsync(ReceiptSearch search) { var meta = await AppUsers.GetCurrentAsync().ConfigureAwait(true); try { var result = await Receipts.GetSearchViewAsync(this.Tenant, meta.OfficeId, search).ConfigureAwait(true); return(this.Ok(result)); } catch (Exception ex) { return(this.Failed(ex.Message, HttpStatusCode.InternalServerError)); } }
private void ReceiptSearch() { if (!ValidateChildren()) { return; } if (!ValidateForSearch()) { return; } ReceiptsResult resultReceipt = null; ReceiptSearch receiptSearch = GetSearchCondition(); try { var task = LoadReceiptAsync(receiptSearch); ProgressDialog.Start(ParentForm, task, false, SessionKey); resultReceipt = task.Result; } catch (Exception ex) { Debug.Fail(ex.ToString()); NLogHandler.WriteErrorLog(this, ex, SessionKey); } if (!resultReceipt.ProcessResult.Result) { ShowWarningDialog(MsgWngNotExistSearchData); return; } var searchResult = resultReceipt.Receipts.Where(x => !ReceiptId.Contains(x.Id)).ToArray(); if (searchResult.Length == 0) { ShowWarningDialog(MsgWngNotExistSearchData); return; } else { ReceiptInfo = searchResult; ParentForm.DialogResult = DialogResult.OK; } }
public async Task <byte[]> GetAsync(ReceiptSearch option, CancellationToken token = default(CancellationToken)) { var companyTask = companyQueryProcessor.GetAsync(new CompanySearch { Id = option.CompanyId, }, token); var appConTask = applicationControlGetByCompanyQueryProcessor.GetAsync(option.CompanyId, token); var gridTask = gridSettingQueryProcessor.GetAsync(new GridSettingSearch { CompanyId = option.CompanyId, GridId = GridId.ReceiptSearch, }, token); var loadTask = receiptSearchQueryProcessor.GetAsync(option, token); await Task.WhenAll(companyTask, appConTask, gridTask, loadTask); var company = companyTask.Result.First(); var appCon = appConTask.Result; var gridSettings = gridTask.Result.ToList(); var items = loadTask.Result.ToList(); if (!items.Any()) { return(null); } var useSection = appCon.UseReceiptSection == 1; var precition = 0; var report = new ReceiptSearchSectionReport(); report.SetBasicPageSetting(company.Code, company.Name); report.Name = "入金データ一覧" + DateTime.Today.ToString("yyyyMMdd"); report.SetData(items, precition, useSection, option.DeleteFlg == 1, gridSettings); report.Run(); return(report.Convert()); }
public static async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, ReceiptSearch search) { using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase()) { var sql = new Sql("SELECT * FROM sales.customer_receipt_search_view"); sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To); sql.And("LOWER(tran_id) LIKE @0", search.TranId.ToSqlLikeExpression().ToLower()); sql.And("LOWER(tran_code) LIKE @0", search.TranCode.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(reference_number, '')) LIKE @0", search.ReferenceNumber.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(statement_reference, '')) LIKE @0", search.StatementReference.ToSqlLikeExpression().ToLower()); sql.And("LOWER(posted_by) LIKE @0", search.PostedBy.ToSqlLikeExpression().ToLower()); sql.And("LOWER(office) LIKE @0", search.Office.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(status, '')) LIKE @0", search.Status.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(verified_by, '')) LIKE @0", search.VerifiedBy.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(reason, '')) LIKE @0", search.Reason.ToSqlLikeExpression().ToLower()); sql.And("LOWER(COALESCE(customer, '')) LIKE @0", search.Customer.ToSqlLikeExpression().ToLower()); if (search.Amount > 0) { sql.And("amount = @0", search.Amount); } sql.And("office_id IN(SELECT * FROM core.get_office_ids(@0))", officeId); return(await db.SelectAsync <dynamic>(sql).ConfigureAwait(false)); } }
public async Task <IEnumerable <Receipt> > GetAsync(ReceiptSearch option, CancellationToken token = default(CancellationToken)) => await receiptSearchQueryProcessor.GetAsync(option, token);
public Task <IEnumerable <Receipt> > GetAsync(ReceiptSearch option, CancellationToken token = default(CancellationToken)) { var query = @" SELECT COALESCE( ba.BankName , r.BankName ) [BankName] ,COALESCE( ba.BranchName, r.BranchName ) [BranchName] ,r.* ,r.ExcludeFlag ExcludeFlagBuffer ,r.ExcludeCategoryId ExcludeCategoryIdBuffer ,n.ReceiptId NettingId ,bat.Name AccountTypeName ,rc.UseAdvanceReceived ,rc.UseCashOnDueDates ,rc.Code CategoryCode ,rc.Name CategoryName ,exc.Code ExcludeCategoryCode ,exc.UseCashOnDueDates ,exc.Name ExcludeCategoryName ,cus.Code CustomerCode ,cus.Name CustomerName ,cur.Code CurrencyCode ,sect.Code SectionCode ,sect.Name SectionName ,memo.Memo ReceiptMemo ,memo.Memo TransferMemo ,memo.ReceiptId ReceiptId ,CASE WHEN re.OutputAt IS NULL THEN 0 ELSE 1 END RecExcOutputAt ,CASE WHEN rst.DestinationReceiptId IS NULL THEN 'false' ELSE 'true' END CancelFlag"; if (option.AdvanceReceivedFlg.HasValue) { if (option.AdvanceReceivedFlg.Value == 1) { query += string.Format(@" ,CASE WHEN r2.DeleteAt IS NOT NULL AND r2.DeleteAt <> '' THEN {0}", (int)ReceiptStatus.Deleted); query += string.Format(@" WHEN r.OutputAt IS NOT NULL AND r.OutputAt <> '' THEN {0}", (int)ReceiptStatus.Journalized); query += string.Format(@" WHEN r.AssignmentFlag <> 0 THEN {0}", (int)ReceiptStatus.PartOrFullAssigned); query += string.Format(@" WHEN arb.Id IS NOT NULL THEN {0}", (int)ReceiptStatus.AdvancedReceived); query += string.Format(@" WHEN rst.DestinationReceiptId IS NOT NULL THEN {0}", (int)ReceiptStatus.SectionTransfered); query += string.Format(@" ELSE {0} END ReceiptStatusFlag", (int)ReceiptStatus.None); query += @" ,CASE WHEN (r.OutputAt IS NOT NULL AND r.OutputAt <> '') AND r2.RemainAmount > 0 AND (r2.DeleteAt IS NULL OR r2.DeleteAt = '') THEN 1 ELSE 0 END RemainAmountFlag"; } else if (option.AdvanceReceivedFlg.Value == 0) { query += string.Format(@" ,{0} ReceiptStatusFlag", (int)ReceiptStatus.None); query += @" ,0 RemainAmountFlag"; } } query += @" FROM Receipt as r LEFT JOIN Customer cus ON cus.Id = r.CustomerId LEFT JOIN Currency cur ON cur.Id = r.CurrencyId LEFT JOIN ReceiptMemo memo ON r.Id = memo.ReceiptId LEFT JOIN Section sect ON sect.Id = r.SectionId LEFT JOIN Netting n ON r.Id = n.ReceiptId LEFT JOIN Category rc ON rc.Id = r.ReceiptCategoryId LEFT JOIN Category exc ON exc.Id = r.ExcludeCategoryId LEFT JOIN ReceiptHeader rh ON rh.Id = r.ReceiptHeaderId LEFT JOIN BankAccountType bat ON bat.Id = r.AccountTypeId LEFT JOIN BankAccount ba ON ba.CompanyId = r.CompanyId AND ba.BankCode = r.BankCode AND ba.BranchCode = r.BranchCode AND ba.AccountTypeId = r.AccountTypeId AND ba.AccountNumber = r.AccountNumber LEFT JOIN ReceiptSectionTransfer rst ON rst.DestinationReceiptId = r.Id LEFT JOIN ( SELECT re.ReceiptId , MAX(re.OutputAt) [OutputAt] FROM ReceiptExclude re GROUP BY re.ReceiptId ) re ON r.Id = re.ReceiptId"; if (option.AdvanceReceivedFlg.HasValue) { if (option.AdvanceReceivedFlg.Value == 1) { query += @" LEFT JOIN Receipt as r2 ON r2.Id = r.OriginalReceiptId LEFT JOIN AdvanceReceivedBackup as arb ON arb.OriginalReceiptId = r.OriginalReceiptId"; } } query += @" WHERE r.CompanyId = @CompanyId AND r.Apportioned = 1 "; var whereCondition = new StringBuilder(); if (option.UseSectionMaster) { if (option.KobetsuType != "Matching") { whereCondition.AppendLine(@" AND r.SectionId IN ( SELECT SectionId FROM SectionWithLoginUser su WHERE su.LoginUserId = @LoginUserId) "); } } if (option.UseSectionWork) { whereCondition.AppendLine(@" AND r.SectionId IN ( SELECT DISTINCT wst.SectionId FROM WorkSectionTarget wst WHERE wst.ClientKey = @ClientKey AND wst.UseCollation = 1 )"); } if (option.RecordedAtFrom.HasValue) { option.RecordedAtFrom = option.RecordedAtFrom.Value.Date; whereCondition.AppendLine(" AND r.RecordedAt >= @RecordedAtFrom"); } if (option.RecordedAtTo.HasValue) { option.RecordedAtTo = option.RecordedAtTo.Value.Date; whereCondition.AppendLine(" AND r.RecordedAt <= @RecordedAtTo"); } if (!string.IsNullOrEmpty(option.PayerName)) { option.PayerName = Sql.GetWrappedValue(option.PayerName); whereCondition.AppendLine(" AND r.PayerName LIKE @PayerName"); } if (option.UpdateAtFrom.HasValue) { option.UpdateAtFrom = option.UpdateAtFrom.Value.Date; whereCondition.AppendLine(" AND r.UpdateAt >= @UpdateAtFrom"); } if (option.UpdateAtTo.HasValue) { option.UpdateAtTo = option.UpdateAtTo.Value.Date.AddDays(1).AddMilliseconds(-1); whereCondition.AppendLine(" AND r.UpdateAt <= @UpdateAtTo"); } if (option.UpdateBy.HasValue) { whereCondition.AppendLine(" AND r.UpdateBy = @UpdateBy"); } if (option.UseForeignCurrencyFlg == 1 && option.CurrencyId != 0) { whereCondition.AppendLine(" AND cur.Id = @CurrencyId"); } if (!string.IsNullOrEmpty(option.BankCode)) { whereCondition.AppendLine(" AND r.BankCode = @BankCode"); } if (!string.IsNullOrEmpty(option.BranchCode)) { whereCondition.AppendLine(" AND r.BranchCode = @BranchCode"); } if (option.AccountTypeId != 0) { whereCondition.AppendLine(" AND r.AccountTypeId = @AccountTypeId"); } if (!string.IsNullOrEmpty(option.AccountNumber)) { whereCondition.AppendLine(" AND r.AccountNumber = @AccountNumber"); } if (!string.IsNullOrEmpty(option.PrivateBankCode)) { whereCondition.AppendLine(" AND r.BankCode = @PrivateBankCode"); } if (!string.IsNullOrEmpty(option.PayerCodePrefix)) { whereCondition.AppendLine(" AND SUBSTRING(r.PayerCode,1,3) = @PayerCodePrefix"); } if (!string.IsNullOrEmpty(option.PayerCodeSuffix)) { whereCondition.AppendLine(" AND SUBSTRING(r.PayerCode,4,10) = @PayerCodeSuffix"); } if (!string.IsNullOrEmpty(option.BillNumber)) { option.BillNumber = Sql.GetWrappedValue(option.BillNumber); whereCondition.AppendLine(" AND r.BillNumber LIKE @BillNumber"); } if (!string.IsNullOrEmpty(option.BillBankCode)) { whereCondition.AppendLine(" AND r.BillBankCode = @BillBankCode"); } if (!string.IsNullOrEmpty(option.BillBranchCode)) { whereCondition.AppendLine(" AND r.BillBranchCode = @BillBranchCode"); } if (option.BillDrawAtFrom.HasValue) { option.BillDrawAtFrom = option.BillDrawAtFrom.Value.Date; whereCondition.AppendLine(" AND r.BillDrawAt >= @BillDrawAtFrom"); } if (option.BillDrawAtTo.HasValue) { option.BillDrawAtTo = option.BillDrawAtTo.Value.Date; whereCondition.AppendLine(" AND r.BillDrawAt <= @BillDrawAtTo"); } if (!string.IsNullOrEmpty(option.BillDrawer)) { option.BillDrawer = Sql.GetWrappedValue(option.BillDrawer); whereCondition.AppendLine(" AND r.BillDrawer LIKE @BillDrawer"); } if (!string.IsNullOrEmpty(option.CustomerCodeFrom)) { whereCondition.AppendLine(" AND cus.Code >= @CustomerCodeFrom"); } if (!string.IsNullOrEmpty(option.CustomerCodeTo)) { whereCondition.AppendLine(" AND cus.Code <= @CustomerCodeTo"); } if (!string.IsNullOrEmpty(option.SectionCode)) { whereCondition.AppendLine(" AND sect.Code = @SectionCode"); } if (!string.IsNullOrEmpty(option.SectionCodeFrom)) { whereCondition.AppendLine(" AND sect.Code >= @SectionCodeFrom"); } if (!string.IsNullOrEmpty(option.SectionCodeTo)) { whereCondition.AppendLine(" AND sect.Code <= @SectionCodeTo"); } if (option.ExistsMemo == 1) { whereCondition.AppendLine(" AND memo.Memo IS NOT NULL"); } if (!string.IsNullOrEmpty(option.ReceiptMemo)) { option.ReceiptMemo = Sql.GetWrappedValue(option.ReceiptMemo); whereCondition.AppendLine(" AND memo.Memo LIKE @ReceiptMemo"); } if (option.InputType.HasValue) { whereCondition.AppendLine(" AND r.InputType = @InputType"); } if (!string.IsNullOrEmpty(option.ReceiptCategoryCode)) { whereCondition.AppendLine(" AND rc.Code = @ReceiptCategoryCode"); } if (!string.IsNullOrEmpty(option.ReceiptCategoryCodeFrom)) { whereCondition.AppendLine(" AND rc.Code >= @ReceiptCategoryCodeFrom"); } if (!string.IsNullOrEmpty(option.ReceiptCategoryCodeTo)) { whereCondition.AppendLine(" AND rc.Code <= @ReceiptCategoryCodeTo"); } var flags = (AssignmentFlagChecked)option.AssignmentFlag; var selectedValues = new List <int>(); if (flags.HasFlag(AssignmentFlagChecked.NoAssignment)) { selectedValues.Add(0); } if (flags.HasFlag(AssignmentFlagChecked.PartAssignment)) { selectedValues.Add(1); } if (flags.HasFlag(AssignmentFlagChecked.FullAssignment)) { selectedValues.Add(2); } if (selectedValues.Any() && !flags.HasFlag(AssignmentFlagChecked.All)) { whereCondition.AppendLine($" AND r.AssignmentFlag IN ({(string.Join(", ", selectedValues))})"); } if (option.ExcludeFlag != 2) { if (option.ExcludeFlag == 0) { whereCondition.AppendLine(" AND r.ReceiptAmount <> r.ExcludeAmount"); } else { whereCondition.AppendLine(" AND r.ExcludeFlag = @ExcludeFlag"); } } if (option.ExcludeCategoryId.HasValue) { whereCondition.AppendLine(" AND r.ExcludeCategoryId = @ExcludeCategoryId"); } if (option.ReceiptCategoryId != 0) { whereCondition.AppendLine(" AND r.ReceiptCategoryId= @ReceiptCategoryId"); } if (option.ReceiptAmountFrom.HasValue) { whereCondition.AppendLine(" AND r.ReceiptAmount >= @ReceiptAmountFrom"); } if (option.ReceiptAmountTo.HasValue) { whereCondition.AppendLine(" AND r.ReceiptAmount <= @ReceiptAmountTo"); } if (option.RemainAmountFrom.HasValue) { whereCondition.AppendLine(" AND r.RemainAmount >= @RemainAmountFrom"); } if (option.RemainAmountTo.HasValue) { whereCondition.AppendLine(" AND r.RemainAmount <= @RemainAmountTo"); } if (!string.IsNullOrEmpty(option.SourceBankName)) { option.SourceBankName = Sql.GetWrappedValue(option.SourceBankName); whereCondition.AppendLine(" AND r.SourceBankName LIKE @SourceBankName"); } if (!string.IsNullOrEmpty(option.SourceBranchName)) { option.SourceBranchName = Sql.GetWrappedValue(option.SourceBranchName); whereCondition.AppendLine(" AND r.SourceBranchName LIKE @SourceBranchName"); } if (option.AdvanceReceivedFlg.HasValue) { if (option.AdvanceReceivedFlg.Value == 1) { whereCondition.AppendLine(" AND rc.UseAdvanceReceived = 1"); } else if (option.AdvanceReceivedFlg.Value == 0) { whereCondition.AppendLine(" AND rc.UseAdvanceReceived = 0"); } } if (!string.IsNullOrEmpty(option.Note1)) { option.Note1 = Sql.GetWrappedValue(option.Note1); whereCondition.AppendLine(" AND r.Note1 LIKE @Note1"); } if (!string.IsNullOrEmpty(option.Note2)) { option.Note2 = Sql.GetWrappedValue(option.Note2); whereCondition.AppendLine(" AND r.Note2 LIKE @Note2"); } if (!string.IsNullOrEmpty(option.Note3)) { option.Note3 = Sql.GetWrappedValue(option.Note3); whereCondition.AppendLine(" AND r.Note3 LIKE @Note3"); } if (!string.IsNullOrEmpty(option.Note4)) { option.Note4 = Sql.GetWrappedValue(option.Note4); whereCondition.AppendLine(" AND r.Note4 LIKE @Note4"); } if (option.DeleteFlg == 1) { // IS NOT NULL whereCondition.AppendLine(" AND r.DeleteAt = r.DeleteAt "); if (option.DeleteAtFrom.HasValue) { whereCondition.AppendLine(" AND r.DeleteAt >= @DeleteAtFrom"); } if (option.DeleteAtTo.HasValue) { whereCondition.AppendLine(" AND r.DeleteAt <= @DeleteAtTo"); } } else { whereCondition.AppendLine(" AND r.DeleteAt IS NULL "); } if (option.IsEditable) { whereCondition.AppendLine(@" AND r.ExcludeFlag = 0 AND r.AssignmentFlag = 0 AND r.OriginalReceiptId IS NULL AND r.OutputAt IS NULL AND r.DeleteAt IS NULL"); } if (option.UseForeignCurrencyFlg == 1) { whereCondition.AppendLine(" ORDER BY cur.DisplayOrder ASC,r.RecordedAt ASC,r.Id ASC"); } if (option.UseForeignCurrencyFlg == 0) { whereCondition.AppendLine(" ORDER BY r.RecordedAt ASC,r.Id ASC"); } query += whereCondition.ToString(); //if (ReceiptSearch.SectionId == null) // ReceiptSearch.SectionId = new int[] { }; return(dbHelper.GetItemsAsync <Receipt>(query, option, token)); }
public async Task <IEnumerable <Receipt> > GetItems(ReceiptSearch option, CancellationToken token) => (await receiptSearchProcessor.GetAsync(option, token)).ToArray();
private ReceiptSearch GetSearchCondition() { var options = new ReceiptSearch(); options.CompanyId = CompanyId; options.RecordedAtFrom = datNyuukinFrom.Value; options.RecordedAtTo = datNyuukinTo.Value; options.PayerName = txtPayerName.Text; if (datUpdateAtFrom.Value.HasValue) { options.UpdateAtFrom = datUpdateAtFrom.Value.Value.Date; } if (datUpdateAtTo.Value.HasValue) { DateTime fromNyuukin = datUpdateAtTo.Value.Value; options.UpdateAtTo = fromNyuukin.Date.AddDays(1).AddMilliseconds(-1); } options.UpdateBy = UpdateBy; options.CustomerCodeFrom = txtCusCodeFrom.Text; options.CustomerCodeTo = txtCusCodeTo.Text; options.SectionCodeFrom = txtSecCodeFrom.Text; options.SectionCodeTo = txtSecCodeTo.Text; options.ExistsMemo = cbxReceiptMemo.Checked ? 1 : 0; options.ReceiptMemo = txtReceiptMemo.Text; if (cmbInputKubun.SelectedIndex > 0) { options.InputType = cmbInputKubun.SelectedIndex; } if (cmbNyukinKubun.SelectedItem != null) { options.ReceiptCategoryId = Convert.ToInt32(cmbNyukinKubun.SelectedItem.SubItems[1].Value); } if (cmbAmountType.SelectedIndex == 0) { options.RemainAmountFrom = nmbAmountFrom.Value; options.RemainAmountTo = nmbAmountTo.Value; } else { options.ReceiptAmountFrom = nmbAmountFrom.Value; options.ReceiptAmountTo = nmbAmountTo.Value; } options.Note1 = txtNote1.Text; options.Note2 = txtNote2.Text; options.Note3 = txtNote3.Text; options.Note4 = txtNote4.Text; options.PayerCodePrefix = txtPayerCodePrefix.Text; options.PayerCodeSuffix = txtPayerCodesuffix.Text; options.UseForeignCurrencyFlg = ApplicationControl.UseForeignCurrency; options.AssignmentFlag = (int)AssignmentFlagChecked.NoAssignment | (int)AssignmentFlagChecked.PartAssignment; if (UseSection) { options.UseSectionWork = Sections.Count != SectionIdsInner.Count; options.ClientKey = ClientKey; } options.LoginUserId = Login.UserId; if (!string.IsNullOrEmpty(CurrencyCode)) { options.CurrencyCode = CurrencyCode; } options.KobetsuType = "Matching"; return(options); }
public ReceiptV2SearchExternalShipmentEC2() { ReceiptSearch = new ReceiptSearch(); ReceiptSearch.References = new ReferenceList1(); }
public List <ReceiptOrderViewModel> SearchData(ReceiptSearch model) { var list = ServerResponse.Invoke <List <ReceiptOrderViewModel> >("api/receiptOrder/search", JsonConvert.SerializeObject(model), "POST"); return(list); }