public List <int> GetAllRequestIds() { var requestTypeIds = _dictionaryTypesService.GetRequestTypeIds(); return(_context.DDDocuments .AsNoTracking() .Where(d => requestTypeIds.Contains(d.DocTypeId)) .OrderBy(d => d.Id) .Select(d => d.Id) .ToList()); }
private void InitializeOldRequestInfos() { var requestTypeIds = _dictionaryTypesService.GetRequestTypeIds(); _oldRequestInfos = _context.DDDocuments .Where(d => d.Id != 0 && requestTypeIds.Contains(d.DocTypeId) && d.DocId != null) .Select(d => new OldRequestInfo { Id = d.Id, PatentId = d.DocId.Value }).ToList(); }
public List <ContractRequestRelation> GetContractRequestRelations(int packageSize, int lastId) { packageSize /= 2; var contractTypeIds = _dictionaryTypesHelper.GetContractTypeIds(); var requestTypeIds = _dictionaryTypesHelper.GetRequestTypeIds(); var contractRequestRelations = _context.RFMessageDocuments .AsNoTracking() .Where(r => r.Id > lastId && r.DocumentId > 0 && contractTypeIds.Contains(r.DDDocument.DocTypeId) && requestTypeIds.Contains(r.RefDDDocument.DocTypeId)) .OrderBy(r => r.Id) .Take(packageSize) .ToList(); var requestContractRelations = _context.RFMessageDocuments .AsNoTracking() .Where(r => r.Id > lastId && r.DocumentId > 0 && requestTypeIds.Contains(r.DDDocument.DocTypeId) && contractTypeIds.Contains(r.RefDDDocument.DocTypeId)) .OrderBy(r => r.Id) .Take(packageSize) .ToList(); contractRequestRelations.AddRange(requestContractRelations); var newContract = _newContext.Contracts.Where(d => contractRequestRelations.Any(c => c.DocumentId.Value == d.Id)).Select(d => d.Id); return(contractRequestRelations.Where(d => newContract.Contains(d.DocumentId.Value)).Select(r => new ContractRequestRelation { ExternalId = r.Id, DateCreate = r.DateCreate ?? DateTimeOffset.Now, DateUpdate = r.DateCreate ?? DateTimeOffset.Now, ContractId = r.DocumentId.Value, RequestId = r.RefDocumentId, }).ToList()); }
/// <summary> /// Миграция данных из таблицы "Выставленные счета". /// </summary> /// <param name="packageSize">Размер пакета.</param> /// <returns></returns> public void GetPaymentInvoices(int packageSize) { var pldPaymentInvoicesCount = GetPaymentCount(typeof(WtPlFixpayment)); Console.Write($"\rPaymentInvoices commited {pldPaymentInvoicesCount}"); using (var connection = new SqlConnection(_appConfiguration.OldNiisConnectionString)) { connection.Open(); var oldContractTypeIds = _dictionaryTypesService.GetContractTypeIds(); var oldRequestTypeIds = _dictionaryTypesService.GetRequestTypeIds(); const string sqlQuery = @"SELECT wpf.U_ID, wpf.date_create, wpf.stamp, wpf.DATE_COMPLETE, wpf.DATE_FACT, wpf.DATE_LIMIT, wpf.APP_ID, ddd.DOCTYPE_ID AS DOCTYPE_ID, wpf.TARIFF_ID, wpf.flCreateUserId, wpf.TARIFF_COUNT, wpf.IS_COMPLETE, wpf.VAT_PERCENT, wpf.FINE_PERCENT, wpf.PENI_PERCENT, 2 AS StatusId FROM dbo.WT_PL_FIXPAYMENT wpf LEFT JOIN dbo.DD_DOCUMENT ddd ON ddd.U_ID = wpf.APP_ID"; var command = new SqlCommand(sqlQuery, connection); using (var sqlDataReader = command.ExecuteReader()) { var idOrdinal = sqlDataReader.GetOrdinal("U_ID"); var dateCreateOrdinal = sqlDataReader.GetOrdinal("date_create"); var dateUpdateOrdinal = sqlDataReader.GetOrdinal("stamp"); var dateCompleteOrdinal = sqlDataReader.GetOrdinal("DATE_COMPLETE"); var dateFactOrdinal = sqlDataReader.GetOrdinal("DATE_FACT"); var overdueDateOrdinal = sqlDataReader.GetOrdinal("DATE_LIMIT"); var appIdOrdinal = sqlDataReader.GetOrdinal("APP_ID"); var documentTypeIdOrdinal = sqlDataReader.GetOrdinal("DOCTYPE_ID"); var tariffIdOrdinal = sqlDataReader.GetOrdinal("TARIFF_ID"); var createUserIdOrdinal = sqlDataReader.GetOrdinal("flCreateUserId"); var tariffCountOrdinal = sqlDataReader.GetOrdinal("TARIFF_COUNT"); var isCompleteOrdinal = sqlDataReader.GetOrdinal("IS_COMPLETE"); var ndsOrdinal = sqlDataReader.GetOrdinal("VAT_PERCENT"); var coefficientOrdinal = sqlDataReader.GetOrdinal("FINE_PERCENT"); var penaltyPercentOrdinal = sqlDataReader.GetOrdinal("PENI_PERCENT"); var statusIdOrdinal = sqlDataReader.GetOrdinal("StatusId"); var paymentInvoiceSqlBulCopy = new PaymentInvoiceSqlBulCopy(_appConfiguration.NiisConnectionString); while (sqlDataReader.Read()) { var dataRow = paymentInvoiceSqlBulCopy.DataTable.NewRow(); dataRow[PaymentInvoiceSqlBulCopy.IdColumn] = sqlDataReader[idOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.CoefficientColumn] = sqlDataReader[coefficientOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.CreateUserIdColumn] = sqlDataReader[createUserIdOrdinal]; if (sqlDataReader[dateCompleteOrdinal] != DBNull.Value) { dataRow[PaymentInvoiceSqlBulCopy.DateCompleteColumn] = DateTimeOffset.Parse(sqlDataReader[dateCompleteOrdinal].ToString()); } dataRow[PaymentInvoiceSqlBulCopy.DateCreateColumn] = sqlDataReader[dateCreateOrdinal] == DBNull.Value ? DateTimeOffset.Now : DateTimeOffset.Parse(sqlDataReader[dateCreateOrdinal].ToString()); if (sqlDataReader[dateFactOrdinal] != DBNull.Value) { dataRow[PaymentInvoiceSqlBulCopy.DateFactColumn] = DateTimeOffset.Parse(sqlDataReader[dateFactOrdinal].ToString()); } dataRow[PaymentInvoiceSqlBulCopy.DateUpdateColumn] = sqlDataReader[dateUpdateOrdinal] == DBNull.Value ? DateTimeOffset.Now : DateTimeOffset.Parse(sqlDataReader[dateUpdateOrdinal].ToString()); dataRow[PaymentInvoiceSqlBulCopy.ExternalIdColumn] = sqlDataReader[idOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.IsCompleteColumn] = CustomConverter.StringToNullableBool(sqlDataReader[isCompleteOrdinal].ToString()); dataRow[PaymentInvoiceSqlBulCopy.NdsColumn] = sqlDataReader[ndsOrdinal]; if (sqlDataReader[overdueDateOrdinal] != DBNull.Value) { dataRow[PaymentInvoiceSqlBulCopy.OverdueDateColumn] = DateTimeOffset.Parse(sqlDataReader[overdueDateOrdinal].ToString()); } dataRow[PaymentInvoiceSqlBulCopy.PenaltyPercentColumn] = sqlDataReader[penaltyPercentOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.ContractIdColumn] = DBNull.Value; dataRow[PaymentInvoiceSqlBulCopy.ProtectionDocIdColumn] = DBNull.Value; dataRow[PaymentInvoiceSqlBulCopy.RequestIdColumn] = DBNull.Value; if (sqlDataReader[documentTypeIdOrdinal] != DBNull.Value) { if (oldContractTypeIds.Any(d => d == (int?)sqlDataReader[documentTypeIdOrdinal])) { dataRow[PaymentInvoiceSqlBulCopy.ContractIdColumn] = sqlDataReader[appIdOrdinal]; } else if (oldRequestTypeIds.Any(d => d == (int?)sqlDataReader[documentTypeIdOrdinal])) { dataRow[PaymentInvoiceSqlBulCopy.RequestIdColumn] = sqlDataReader[appIdOrdinal]; } } else { dataRow[PaymentInvoiceSqlBulCopy.ProtectionDocIdColumn] = sqlDataReader[appIdOrdinal]; } dataRow[PaymentInvoiceSqlBulCopy.StatusIdColumn] = sqlDataReader[statusIdOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.TariffCountColumn] = sqlDataReader[tariffCountOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.TariffIdColumn] = sqlDataReader[tariffIdOrdinal]; dataRow[PaymentInvoiceSqlBulCopy.IsDeletedColumn] = false; paymentInvoiceSqlBulCopy.DataTable.Rows.Add(dataRow); if (paymentInvoiceSqlBulCopy.DataTable.Rows.Count < packageSize) { continue; } paymentInvoiceSqlBulCopy.SqlBulkCopyWriteToServer(); } paymentInvoiceSqlBulCopy.SqlBulkCopyWriteToServer(); sqlDataReader.Close(); } connection.Close(); } }