public List <int> GetAllOldContractIds()
{
var oldContractTypeIds = _dictionaryTypesService.GetContractTypeIds();
var oldContractIds = _context.DDDocuments
.Where(r => oldContractTypeIds.Contains(r.DocTypeId))
.OrderBy(r => r.Id)
.Select(r => r.Id).ToList();
return(oldContractIds);
}
public List <ContractProtectionDocRelation> GetContractProtectionDocRelations(int packageSize, int lastId)
{
var contractTypeIds = _dictionaryTypesHelper.GetContractTypeIds();
var contractProtectionDocRelations = _context.DDDocuments
.AsNoTracking()
.Where(d => d.Id > lastId &&
contractTypeIds.Contains(d.DocTypeId) &&
d.DocId > 0)
.OrderBy(d => d.Id)
.Take(packageSize)
.ToList();
return(contractProtectionDocRelations.Select(d => new ContractProtectionDocRelation
{
ExternalId = d.Id,
DateCreate = d.DateCreate ?? DateTimeOffset.Now,
DateUpdate = d.DateUpdate ?? DateTimeOffset.Now,
ContractId = d.Id,
ProtectionDocId = d.DocId.Value
}).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();
}
}