1347 lines
58 KiB
C#
1347 lines
58 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using Microsoft.Data.SqlClient;
|
||
|
||
namespace XLAB2
|
||
{
|
||
internal sealed class PrsnDirectoryService
|
||
{
|
||
public int AddPrdspvItem(PrdspvDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrdspvItem(item);
|
||
|
||
const string sql = @"
|
||
INSERT INTO dbo.PRDSPV
|
||
(
|
||
IDPRFR,
|
||
IDSPVDMK,
|
||
SHFKL,
|
||
DTPLKL,
|
||
DISV
|
||
)
|
||
VALUES
|
||
(
|
||
@EmploymentId,
|
||
@StampTypeId,
|
||
@StampCode,
|
||
@ReceivedOn,
|
||
@Notes
|
||
);
|
||
|
||
SELECT CAST(SCOPE_IDENTITY() AS int);";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrdspvIsUnique(connection, normalizedItem.EmploymentId, normalizedItem.StampTypeId, null);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = normalizedItem.EmploymentId;
|
||
command.Parameters.Add("@StampTypeId", SqlDbType.Int).Value = normalizedItem.StampTypeId;
|
||
command.Parameters.Add("@StampCode", SqlDbType.VarChar, PrdspvDirectoryRules.StampCodeMaxLength).Value = normalizedItem.StampCode;
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@ReceivedOn", normalizedItem.ReceivedOn);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Notes", SqlDbType.VarChar, PrdspvDirectoryRules.NotesMaxLength, normalizedItem.Notes);
|
||
|
||
try
|
||
{
|
||
return Convert.ToInt32(command.ExecuteScalar());
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "XA1_PRFR_SPVDMK"))
|
||
{
|
||
throw CreatePrdspvDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
public int AddPrfrItem(PrfrDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrfrItem(item);
|
||
|
||
const string sql = @"
|
||
INSERT INTO dbo.PRFR
|
||
(
|
||
IDFRPD,
|
||
IDPRSN,
|
||
IDSPDL,
|
||
DTPRM,
|
||
DTYVL,
|
||
DTDLJ,
|
||
DTPLP,
|
||
DTPOT,
|
||
DTOPOT,
|
||
DTBOT,
|
||
DTOBOT,
|
||
TABNN,
|
||
PRFRGUID,
|
||
PRFRPIN,
|
||
DTPIN,
|
||
ISAUTHPIN
|
||
)
|
||
VALUES
|
||
(
|
||
@OrganizationId,
|
||
@PersonId,
|
||
@PositionId,
|
||
@HireDate,
|
||
@DismissalDate,
|
||
@PositionStartDate,
|
||
@QualificationPlanDate,
|
||
@LastVacationStartDate,
|
||
@LastVacationEndDate,
|
||
@NextVacationStartDate,
|
||
@NextVacationEndDate,
|
||
@PersonnelNumber,
|
||
@Guid,
|
||
@PinHash,
|
||
@PinChangedAt,
|
||
@IsPinAuth
|
||
);
|
||
|
||
SELECT CAST(SCOPE_IDENTITY() AS int);";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrfrPairIsUnique(connection, normalizedItem.PersonId, normalizedItem.OrganizationId, null);
|
||
EnsurePrfrGuidIsUnique(connection, normalizedItem.Guid, null);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
PopulatePrfrCommand(command, normalizedItem);
|
||
|
||
try
|
||
{
|
||
return Convert.ToInt32(command.ExecuteScalar());
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFR_IDFRPD_IDPRSN"))
|
||
{
|
||
throw CreatePrfrDuplicatePairException();
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFR_PRFRGUID"))
|
||
{
|
||
throw CreatePrfrDuplicateGuidException(normalizedItem.Guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
public int AddPrfrvdItem(PrfrvdDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrfrvdItem(item);
|
||
|
||
const string sql = @"
|
||
INSERT INTO dbo.PRFRVD
|
||
(
|
||
IDPRFR,
|
||
IDSPVDPR
|
||
)
|
||
VALUES
|
||
(
|
||
@EmploymentId,
|
||
@ActivityId
|
||
);
|
||
|
||
SELECT CAST(SCOPE_IDENTITY() AS int);";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrfrvdIsUnique(connection, normalizedItem.EmploymentId, normalizedItem.ActivityId, null);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = normalizedItem.EmploymentId;
|
||
command.Parameters.Add("@ActivityId", SqlDbType.Int).Value = normalizedItem.ActivityId;
|
||
|
||
try
|
||
{
|
||
return Convert.ToInt32(command.ExecuteScalar());
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFRVD_IDPRFR_IDSPVDPR"))
|
||
{
|
||
throw CreatePrfrvdDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
public int AddPrsnItem(PrsnDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrsnItem(item);
|
||
|
||
const string sql = @"
|
||
INSERT INTO dbo.PRSN
|
||
(
|
||
PRFIO,
|
||
PRFM,
|
||
PRNM,
|
||
PROT,
|
||
TEL,
|
||
EMAIL,
|
||
DSPRSN,
|
||
GUIDPRSN,
|
||
PRDPID
|
||
)
|
||
VALUES
|
||
(
|
||
@FullName,
|
||
@LastName,
|
||
@FirstName,
|
||
@Patronymic,
|
||
@Phone,
|
||
@Email,
|
||
@Notes,
|
||
@Guid,
|
||
@ExternalId
|
||
);
|
||
|
||
SELECT CAST(SCOPE_IDENTITY() AS int);";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrsnFullNameAndExternalIdUnique(connection, normalizedItem.FullName, normalizedItem.ExternalId, null);
|
||
EnsurePrsnGuidIsUnique(connection, normalizedItem.Guid, null);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
PopulatePrsnCommand(command, normalizedItem);
|
||
|
||
try
|
||
{
|
||
return Convert.ToInt32(command.ExecuteScalar());
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRSN_PRFIO_PRDPID"))
|
||
{
|
||
throw CreatePrsnDuplicateFullNameException(normalizedItem.FullName, normalizedItem.ExternalId);
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRSN_GUIDPRSN"))
|
||
{
|
||
throw CreatePrsnDuplicateGuidException(normalizedItem.Guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
public DirectoryDeleteResult DeletePrdspvItem(int id)
|
||
{
|
||
if (id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRDSPV для удаления.");
|
||
}
|
||
|
||
const string sql = @"
|
||
DELETE FROM dbo.PRDSPV
|
||
WHERE IDPRDSPV = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
try
|
||
{
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
|
||
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRDSPV для удаления не найдена.");
|
||
}
|
||
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = true
|
||
};
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ex.Number == 547)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRDSPV", ex)
|
||
};
|
||
}
|
||
}
|
||
|
||
public DirectoryDeleteResult DeletePrfrItem(int id)
|
||
{
|
||
if (id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRFR для удаления.");
|
||
}
|
||
|
||
const string sql = @"
|
||
DELETE FROM dbo.PRFR
|
||
WHERE IDPRFR = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
try
|
||
{
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
var blockers = ReferenceDirectorySqlHelpers.LoadDeleteBlockersFromForeignKeys(connection, "PRFR", id);
|
||
|
||
const string prdspvSql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRDSPV
|
||
WHERE IDPRFR = @Id;";
|
||
|
||
using (var blockerCommand = new SqlCommand(prdspvSql, connection))
|
||
{
|
||
blockerCommand.CommandTimeout = 60;
|
||
blockerCommand.Parameters.Add("@Id", SqlDbType.Int).Value = id;
|
||
var prdspvCount = Convert.ToInt32(blockerCommand.ExecuteScalar());
|
||
if (prdspvCount > 0)
|
||
{
|
||
blockers.Add(new DeleteBlockerInfo
|
||
{
|
||
RowCount = prdspvCount,
|
||
TableName = "PRDSPV"
|
||
});
|
||
}
|
||
}
|
||
|
||
if (blockers.Count > 0)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRFR", blockers)
|
||
};
|
||
}
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
|
||
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRFR для удаления не найдена.");
|
||
}
|
||
}
|
||
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = true
|
||
};
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ex.Number == 547)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRFR", ex)
|
||
};
|
||
}
|
||
}
|
||
|
||
public DirectoryDeleteResult DeletePrfrvdItem(int id)
|
||
{
|
||
if (id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRFRVD для удаления.");
|
||
}
|
||
|
||
const string sql = @"
|
||
DELETE FROM dbo.PRFRVD
|
||
WHERE IDPRFRVD = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
try
|
||
{
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
var blockers = ReferenceDirectorySqlHelpers.LoadDeleteBlockersFromForeignKeys(connection, "PRFRVD", id);
|
||
if (blockers.Count > 0)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRFRVD", blockers)
|
||
};
|
||
}
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
|
||
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRFRVD для удаления не найдена.");
|
||
}
|
||
}
|
||
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = true
|
||
};
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ex.Number == 547)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRFRVD", ex)
|
||
};
|
||
}
|
||
}
|
||
|
||
public DirectoryDeleteResult DeletePrsnItem(int id)
|
||
{
|
||
if (id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRSN для удаления.");
|
||
}
|
||
|
||
const string sql = @"
|
||
DELETE FROM dbo.PRSN
|
||
WHERE IDPRSN = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
try
|
||
{
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
{
|
||
connection.Open();
|
||
var blockers = ReferenceDirectorySqlHelpers.LoadDeleteBlockersFromForeignKeys(connection, "PRSN", id);
|
||
if (blockers.Count > 0)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRSN", blockers)
|
||
};
|
||
}
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
|
||
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRSN для удаления не найдена.");
|
||
}
|
||
}
|
||
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = true
|
||
};
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ex.Number == 547)
|
||
{
|
||
return new DirectoryDeleteResult
|
||
{
|
||
IsDeleted = false,
|
||
WarningMessage = ReferenceDirectorySqlHelpers.CreateDeleteBlockedMessage("PRSN", ex)
|
||
};
|
||
}
|
||
}
|
||
|
||
public IReadOnlyList<DirectoryLookupItem> LoadFrpdReferences()
|
||
{
|
||
return ReferenceDirectorySqlHelpers.LoadLookupItems(@"
|
||
SELECT
|
||
fr.IDFRPD AS Id,
|
||
fr.NMFRPD AS Name
|
||
FROM dbo.FRPD fr
|
||
ORDER BY fr.NMFRPD, fr.IDFRPD;");
|
||
}
|
||
|
||
public IReadOnlyList<DirectoryLookupItem> LoadSpdlReferences()
|
||
{
|
||
return ReferenceDirectorySqlHelpers.LoadLookupItems(@"
|
||
SELECT
|
||
ref.IDSPDL AS Id,
|
||
ref.NMDL AS Name
|
||
FROM dbo.SPDL ref
|
||
ORDER BY ref.NMDL, ref.IDSPDL;");
|
||
}
|
||
|
||
public IReadOnlyList<DirectoryLookupItem> LoadSpvdmkReferences()
|
||
{
|
||
return ReferenceDirectorySqlHelpers.LoadLookupItems(@"
|
||
SELECT
|
||
ref.IDSPVDMK AS Id,
|
||
ref.NMVDMK AS Name
|
||
FROM dbo.SPVDMK ref
|
||
ORDER BY ref.NMVDMK, ref.IDSPVDMK;");
|
||
}
|
||
|
||
public IReadOnlyList<DirectoryLookupItem> LoadSpvdprReferences()
|
||
{
|
||
return ReferenceDirectorySqlHelpers.LoadLookupItems(@"
|
||
SELECT
|
||
ref.IDSPVDPR AS Id,
|
||
ref.NMVDPR AS Name
|
||
FROM dbo.SPVDPR ref
|
||
ORDER BY ref.NMVDPR, ref.IDSPVDPR;");
|
||
}
|
||
|
||
public IReadOnlyList<PrdspvDirectoryItem> LoadPrdspvItems(int employmentId)
|
||
{
|
||
const string sql = @"
|
||
SELECT
|
||
stamp.IDPRDSPV AS Id,
|
||
stamp.IDPRFR AS EmploymentId,
|
||
stamp.IDSPVDMK AS StampTypeId,
|
||
kind.NMVDMK AS StampTypeName,
|
||
stamp.SHFKL AS StampCode,
|
||
stamp.DTPLKL AS ReceivedOn,
|
||
stamp.DISV AS Notes
|
||
FROM dbo.PRDSPV stamp
|
||
JOIN dbo.SPVDMK kind ON kind.IDSPVDMK = stamp.IDSPVDMK
|
||
WHERE stamp.IDPRFR = @EmploymentId
|
||
ORDER BY kind.NMVDMK, stamp.SHFKL, stamp.IDPRDSPV;";
|
||
|
||
var items = new List<PrdspvDirectoryItem>();
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = employmentId;
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
items.Add(new PrdspvDirectoryItem
|
||
{
|
||
EmploymentId = ReferenceDirectorySqlHelpers.GetInt32(reader, "EmploymentId"),
|
||
Id = ReferenceDirectorySqlHelpers.GetInt32(reader, "Id"),
|
||
Notes = ReferenceDirectorySqlHelpers.GetString(reader, "Notes"),
|
||
ReceivedOn = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "ReceivedOn"),
|
||
StampCode = ReferenceDirectorySqlHelpers.GetString(reader, "StampCode"),
|
||
StampTypeId = ReferenceDirectorySqlHelpers.GetInt32(reader, "StampTypeId"),
|
||
StampTypeName = ReferenceDirectorySqlHelpers.GetString(reader, "StampTypeName")
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return items;
|
||
}
|
||
|
||
public IReadOnlyList<PrfrDirectoryItem> LoadPrfrItems(int personId)
|
||
{
|
||
const string sql = @"
|
||
SELECT
|
||
employment.IDPRFR AS EmploymentId,
|
||
employment.IDPRSN AS PersonId,
|
||
employment.IDFRPD AS OrganizationId,
|
||
org.NMFRPD AS OrganizationName,
|
||
employment.IDSPDL AS PositionId,
|
||
post.NMDL AS PositionName,
|
||
employment.DTPRM AS HireDate,
|
||
employment.DTYVL AS DismissalDate,
|
||
employment.DTDLJ AS PositionStartDate,
|
||
employment.DTPLP AS QualificationPlanDate,
|
||
employment.DTPOT AS LastVacationStartDate,
|
||
employment.DTOPOT AS LastVacationEndDate,
|
||
employment.DTBOT AS NextVacationStartDate,
|
||
employment.DTOBOT AS NextVacationEndDate,
|
||
employment.TABNN AS PersonnelNumber,
|
||
employment.PRFRGUID AS Guid,
|
||
employment.PRFRPIN AS PinHash,
|
||
employment.DTPIN AS PinChangedAt,
|
||
employment.ISAUTHPIN AS IsPinAuth,
|
||
ISNULL(
|
||
STUFF((
|
||
SELECT ', ' + activity.ActivityName
|
||
FROM
|
||
(
|
||
SELECT DISTINCT ref.NMVDPR AS ActivityName
|
||
FROM dbo.PRFRVD link
|
||
JOIN dbo.SPVDPR ref ON ref.IDSPVDPR = link.IDSPVDPR
|
||
WHERE link.IDPRFR = employment.IDPRFR
|
||
) activity
|
||
ORDER BY activity.ActivityName
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'nvarchar(max)'), 1, 2, ''),
|
||
''
|
||
) AS ActivityNames,
|
||
ISNULL(
|
||
STUFF((
|
||
SELECT ', ' + stamp.StampName
|
||
FROM
|
||
(
|
||
SELECT DISTINCT ref.NMVDMK + ' [' + stampLink.SHFKL + ']' AS StampName
|
||
FROM dbo.PRDSPV stampLink
|
||
JOIN dbo.SPVDMK ref ON ref.IDSPVDMK = stampLink.IDSPVDMK
|
||
WHERE stampLink.IDPRFR = employment.IDPRFR
|
||
) stamp
|
||
ORDER BY stamp.StampName
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'nvarchar(max)'), 1, 2, ''),
|
||
''
|
||
) AS StampNames
|
||
FROM dbo.PRFR employment
|
||
JOIN dbo.FRPD org ON org.IDFRPD = employment.IDFRPD
|
||
LEFT JOIN dbo.SPDL post ON post.IDSPDL = employment.IDSPDL
|
||
WHERE employment.IDPRSN = @PersonId
|
||
ORDER BY org.NMFRPD, employment.IDPRFR;";
|
||
|
||
var items = new List<PrfrDirectoryItem>();
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@PersonId", SqlDbType.Int).Value = personId;
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
items.Add(new PrfrDirectoryItem
|
||
{
|
||
ActivityNames = ReferenceDirectorySqlHelpers.GetString(reader, "ActivityNames"),
|
||
DismissalDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "DismissalDate"),
|
||
EmploymentId = ReferenceDirectorySqlHelpers.GetInt32(reader, "EmploymentId"),
|
||
Guid = ReferenceDirectorySqlHelpers.GetString(reader, "Guid"),
|
||
HireDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "HireDate"),
|
||
IsPinAuth = ReferenceDirectorySqlHelpers.GetNullableBoolean(reader, "IsPinAuth"),
|
||
LastVacationEndDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "LastVacationEndDate"),
|
||
LastVacationStartDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "LastVacationStartDate"),
|
||
NextVacationEndDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "NextVacationEndDate"),
|
||
NextVacationStartDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "NextVacationStartDate"),
|
||
OrganizationId = ReferenceDirectorySqlHelpers.GetInt32(reader, "OrganizationId"),
|
||
OrganizationName = ReferenceDirectorySqlHelpers.GetString(reader, "OrganizationName"),
|
||
PersonId = ReferenceDirectorySqlHelpers.GetInt32(reader, "PersonId"),
|
||
PersonnelNumber = ReferenceDirectorySqlHelpers.GetString(reader, "PersonnelNumber"),
|
||
PinChangedAt = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "PinChangedAt"),
|
||
PinHash = ReferenceDirectorySqlHelpers.GetString(reader, "PinHash"),
|
||
PositionId = ReferenceDirectorySqlHelpers.GetNullableInt32(reader, "PositionId"),
|
||
PositionName = ReferenceDirectorySqlHelpers.GetString(reader, "PositionName"),
|
||
PositionStartDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "PositionStartDate"),
|
||
QualificationPlanDate = ReferenceDirectorySqlHelpers.GetNullableDateTime(reader, "QualificationPlanDate"),
|
||
StampNames = ReferenceDirectorySqlHelpers.GetString(reader, "StampNames")
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return items;
|
||
}
|
||
|
||
public IReadOnlyList<PrfrvdDirectoryItem> LoadPrfrvdItems(int employmentId)
|
||
{
|
||
const string sql = @"
|
||
SELECT
|
||
link.IDPRFRVD AS Id,
|
||
link.IDPRFR AS EmploymentId,
|
||
link.IDSPVDPR AS ActivityId,
|
||
ref.NMVDPR AS ActivityName
|
||
FROM dbo.PRFRVD link
|
||
JOIN dbo.SPVDPR ref ON ref.IDSPVDPR = link.IDSPVDPR
|
||
WHERE link.IDPRFR = @EmploymentId
|
||
ORDER BY ref.NMVDPR, link.IDPRFRVD;";
|
||
|
||
var items = new List<PrfrvdDirectoryItem>();
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = employmentId;
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
items.Add(new PrfrvdDirectoryItem
|
||
{
|
||
ActivityId = ReferenceDirectorySqlHelpers.GetInt32(reader, "ActivityId"),
|
||
ActivityName = ReferenceDirectorySqlHelpers.GetString(reader, "ActivityName"),
|
||
EmploymentId = ReferenceDirectorySqlHelpers.GetInt32(reader, "EmploymentId"),
|
||
Id = ReferenceDirectorySqlHelpers.GetInt32(reader, "Id")
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return items;
|
||
}
|
||
|
||
public IReadOnlyList<PrsnDirectoryItem> LoadPrsnItems()
|
||
{
|
||
const string sql = @"
|
||
SELECT
|
||
person.IDPRSN AS Id,
|
||
person.PRFIO AS FullName,
|
||
person.PRFM AS LastName,
|
||
person.PRNM AS FirstName,
|
||
person.PROT AS Patronymic,
|
||
person.TEL AS Phone,
|
||
person.EMAIL AS Email,
|
||
person.DSPRSN AS Notes,
|
||
person.GUIDPRSN AS Guid,
|
||
person.PRDPID AS ExternalId,
|
||
ISNULL(
|
||
STUFF((
|
||
SELECT ', ' + org.OrganizationName
|
||
FROM
|
||
(
|
||
SELECT DISTINCT ref.NMFRPD AS OrganizationName
|
||
FROM dbo.PRFR employment
|
||
JOIN dbo.FRPD ref ON ref.IDFRPD = employment.IDFRPD
|
||
WHERE employment.IDPRSN = person.IDPRSN
|
||
) org
|
||
ORDER BY org.OrganizationName
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'nvarchar(max)'), 1, 2, ''),
|
||
''
|
||
) AS OrganizationNames,
|
||
ISNULL(
|
||
STUFF((
|
||
SELECT ', ' + activity.ActivityName
|
||
FROM
|
||
(
|
||
SELECT DISTINCT ref.NMVDPR AS ActivityName
|
||
FROM dbo.PRFR employment
|
||
JOIN dbo.PRFRVD link ON link.IDPRFR = employment.IDPRFR
|
||
JOIN dbo.SPVDPR ref ON ref.IDSPVDPR = link.IDSPVDPR
|
||
WHERE employment.IDPRSN = person.IDPRSN
|
||
) activity
|
||
ORDER BY activity.ActivityName
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'nvarchar(max)'), 1, 2, ''),
|
||
''
|
||
) AS ActivityNames,
|
||
ISNULL(
|
||
STUFF((
|
||
SELECT ', ' + stamp.StampName
|
||
FROM
|
||
(
|
||
SELECT DISTINCT ref.NMVDMK + ' [' + stampLink.SHFKL + ']' AS StampName
|
||
FROM dbo.PRFR employment
|
||
JOIN dbo.PRDSPV stampLink ON stampLink.IDPRFR = employment.IDPRFR
|
||
JOIN dbo.SPVDMK ref ON ref.IDSPVDMK = stampLink.IDSPVDMK
|
||
WHERE employment.IDPRSN = person.IDPRSN
|
||
) stamp
|
||
ORDER BY stamp.StampName
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'nvarchar(max)'), 1, 2, ''),
|
||
''
|
||
) AS StampNames
|
||
FROM dbo.PRSN person
|
||
ORDER BY person.PRFIO, person.IDPRSN;";
|
||
|
||
var items = new List<PrsnDirectoryItem>();
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
|
||
using (var reader = command.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
items.Add(new PrsnDirectoryItem
|
||
{
|
||
ActivityNames = ReferenceDirectorySqlHelpers.GetString(reader, "ActivityNames"),
|
||
Email = ReferenceDirectorySqlHelpers.GetString(reader, "Email"),
|
||
ExternalId = ReferenceDirectorySqlHelpers.GetString(reader, "ExternalId"),
|
||
FirstName = ReferenceDirectorySqlHelpers.GetString(reader, "FirstName"),
|
||
FullName = ReferenceDirectorySqlHelpers.GetString(reader, "FullName"),
|
||
Guid = ReferenceDirectorySqlHelpers.GetString(reader, "Guid"),
|
||
Id = ReferenceDirectorySqlHelpers.GetInt32(reader, "Id"),
|
||
LastName = ReferenceDirectorySqlHelpers.GetString(reader, "LastName"),
|
||
Notes = ReferenceDirectorySqlHelpers.GetString(reader, "Notes"),
|
||
OrganizationNames = ReferenceDirectorySqlHelpers.GetString(reader, "OrganizationNames"),
|
||
Patronymic = ReferenceDirectorySqlHelpers.GetString(reader, "Patronymic"),
|
||
Phone = ReferenceDirectorySqlHelpers.GetString(reader, "Phone"),
|
||
StampNames = ReferenceDirectorySqlHelpers.GetString(reader, "StampNames")
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return items;
|
||
}
|
||
|
||
public void UpdatePrdspvItem(PrdspvDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrdspvItem(item);
|
||
if (normalizedItem.Id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRDSPV для изменения.");
|
||
}
|
||
|
||
const string sql = @"
|
||
UPDATE dbo.PRDSPV
|
||
SET IDSPVDMK = @StampTypeId,
|
||
SHFKL = @StampCode,
|
||
DTPLKL = @ReceivedOn,
|
||
DISV = @Notes
|
||
WHERE IDPRDSPV = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrdspvIsUnique(connection, normalizedItem.EmploymentId, normalizedItem.StampTypeId, normalizedItem.Id);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = normalizedItem.Id;
|
||
command.Parameters.Add("@StampTypeId", SqlDbType.Int).Value = normalizedItem.StampTypeId;
|
||
command.Parameters.Add("@StampCode", SqlDbType.VarChar, PrdspvDirectoryRules.StampCodeMaxLength).Value = normalizedItem.StampCode;
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@ReceivedOn", normalizedItem.ReceivedOn);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Notes", SqlDbType.VarChar, PrdspvDirectoryRules.NotesMaxLength, normalizedItem.Notes);
|
||
|
||
try
|
||
{
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRDSPV для изменения не найдена.");
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "XA1_PRFR_SPVDMK"))
|
||
{
|
||
throw CreatePrdspvDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
public void UpdatePrfrItem(PrfrDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrfrItem(item);
|
||
if (normalizedItem.EmploymentId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRFR для изменения.");
|
||
}
|
||
|
||
const string sql = @"
|
||
UPDATE dbo.PRFR
|
||
SET IDFRPD = @OrganizationId,
|
||
IDPRSN = @PersonId,
|
||
IDSPDL = @PositionId,
|
||
DTPRM = @HireDate,
|
||
DTYVL = @DismissalDate,
|
||
DTDLJ = @PositionStartDate,
|
||
DTPLP = @QualificationPlanDate,
|
||
DTPOT = @LastVacationStartDate,
|
||
DTOPOT = @LastVacationEndDate,
|
||
DTBOT = @NextVacationStartDate,
|
||
DTOBOT = @NextVacationEndDate,
|
||
TABNN = @PersonnelNumber,
|
||
PRFRGUID = @Guid,
|
||
PRFRPIN = @PinHash,
|
||
DTPIN = @PinChangedAt,
|
||
ISAUTHPIN = @IsPinAuth
|
||
WHERE IDPRFR = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrfrPairIsUnique(connection, normalizedItem.PersonId, normalizedItem.OrganizationId, normalizedItem.EmploymentId);
|
||
EnsurePrfrGuidIsUnique(connection, normalizedItem.Guid, normalizedItem.EmploymentId);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = normalizedItem.EmploymentId;
|
||
PopulatePrfrCommand(command, normalizedItem);
|
||
|
||
try
|
||
{
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRFR для изменения не найдена.");
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFR_IDFRPD_IDPRSN"))
|
||
{
|
||
throw CreatePrfrDuplicatePairException();
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFR_PRFRGUID"))
|
||
{
|
||
throw CreatePrfrDuplicateGuidException(normalizedItem.Guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
public void UpdatePrfrvdItem(PrfrvdDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrfrvdItem(item);
|
||
if (normalizedItem.Id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRFRVD для изменения.");
|
||
}
|
||
|
||
const string sql = @"
|
||
UPDATE dbo.PRFRVD
|
||
SET IDSPVDPR = @ActivityId
|
||
WHERE IDPRFRVD = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrfrvdIsUnique(connection, normalizedItem.EmploymentId, normalizedItem.ActivityId, normalizedItem.Id);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = normalizedItem.Id;
|
||
command.Parameters.Add("@ActivityId", SqlDbType.Int).Value = normalizedItem.ActivityId;
|
||
|
||
try
|
||
{
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRFRVD для изменения не найдена.");
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRFRVD_IDPRFR_IDSPVDPR"))
|
||
{
|
||
throw CreatePrfrvdDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
public void UpdatePrsnItem(PrsnDirectoryItem item)
|
||
{
|
||
var normalizedItem = NormalizePrsnItem(item);
|
||
if (normalizedItem.Id <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись PRSN для изменения.");
|
||
}
|
||
|
||
const string sql = @"
|
||
UPDATE dbo.PRSN
|
||
SET PRFIO = @FullName,
|
||
PRFM = @LastName,
|
||
PRNM = @FirstName,
|
||
PROT = @Patronymic,
|
||
TEL = @Phone,
|
||
EMAIL = @Email,
|
||
DSPRSN = @Notes,
|
||
GUIDPRSN = @Guid,
|
||
PRDPID = @ExternalId
|
||
WHERE IDPRSN = @Id;
|
||
|
||
SELECT @@ROWCOUNT;";
|
||
|
||
using (var connection = ReferenceDirectorySqlHelpers.CreateConnection())
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
connection.Open();
|
||
EnsurePrsnFullNameAndExternalIdUnique(connection, normalizedItem.FullName, normalizedItem.ExternalId, normalizedItem.Id);
|
||
EnsurePrsnGuidIsUnique(connection, normalizedItem.Guid, normalizedItem.Id);
|
||
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Id", SqlDbType.Int).Value = normalizedItem.Id;
|
||
PopulatePrsnCommand(command, normalizedItem);
|
||
|
||
try
|
||
{
|
||
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
|
||
{
|
||
throw new InvalidOperationException("Запись PRSN для изменения не найдена.");
|
||
}
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRSN_PRFIO_PRDPID"))
|
||
{
|
||
throw CreatePrsnDuplicateFullNameException(normalizedItem.FullName, normalizedItem.ExternalId);
|
||
}
|
||
catch (SqlException ex) when (ReferenceDirectorySqlHelpers.IsDuplicateViolation(ex, "IX_PRSN_GUIDPRSN"))
|
||
{
|
||
throw CreatePrsnDuplicateGuidException(normalizedItem.Guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrdspvDuplicateException()
|
||
{
|
||
return new InvalidOperationException("Выбранный вид клейма уже существует у этого сотрудника.");
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrfrDuplicateGuidException(string guid)
|
||
{
|
||
return new InvalidOperationException(string.Format("GUID сотрудника \"{0}\" уже существует в справочнике.", guid));
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrfrDuplicatePairException()
|
||
{
|
||
return new InvalidOperationException("Такая связка персоны и организации уже существует в справочнике.");
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrfrvdDuplicateException()
|
||
{
|
||
return new InvalidOperationException("Выбранный вид деятельности уже существует у этой записи персонала.");
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrsnDuplicateFullNameException(string fullName, string externalId)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(externalId))
|
||
{
|
||
return new InvalidOperationException(string.Format("Персона \"{0}\" без дополнительного идентификатора уже существует в справочнике.", fullName));
|
||
}
|
||
|
||
return new InvalidOperationException(string.Format("Персона \"{0}\" с дополнительным идентификатором \"{1}\" уже существует в справочнике.", fullName, externalId));
|
||
}
|
||
|
||
private static InvalidOperationException CreatePrsnDuplicateGuidException(string guid)
|
||
{
|
||
return new InvalidOperationException(string.Format("GUID персоны \"{0}\" уже существует в справочнике.", guid));
|
||
}
|
||
|
||
private static void EnsurePrdspvIsUnique(SqlConnection connection, int employmentId, int stampTypeId, int? excludeId)
|
||
{
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRDSPV
|
||
WHERE IDPRFR = @EmploymentId
|
||
AND IDSPVDMK = @StampTypeId
|
||
AND (@ExcludeId IS NULL OR IDPRDSPV <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = employmentId;
|
||
command.Parameters.Add("@StampTypeId", SqlDbType.Int).Value = stampTypeId;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrdspvDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void EnsurePrfrGuidIsUnique(SqlConnection connection, string guid, int? excludeId)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(guid))
|
||
{
|
||
return;
|
||
}
|
||
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRFR
|
||
WHERE PRFRGUID = @Guid
|
||
AND (@ExcludeId IS NULL OR IDPRFR <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Guid", SqlDbType.VarChar, PrfrDirectoryRules.GuidMaxLength).Value = guid;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrfrDuplicateGuidException(guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void EnsurePrfrPairIsUnique(SqlConnection connection, int personId, int organizationId, int? excludeId)
|
||
{
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRFR
|
||
WHERE IDPRSN = @PersonId
|
||
AND IDFRPD = @OrganizationId
|
||
AND (@ExcludeId IS NULL OR IDPRFR <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@PersonId", SqlDbType.Int).Value = personId;
|
||
command.Parameters.Add("@OrganizationId", SqlDbType.Int).Value = organizationId;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrfrDuplicatePairException();
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void EnsurePrfrvdIsUnique(SqlConnection connection, int employmentId, int activityId, int? excludeId)
|
||
{
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRFRVD
|
||
WHERE IDPRFR = @EmploymentId
|
||
AND IDSPVDPR = @ActivityId
|
||
AND (@ExcludeId IS NULL OR IDPRFRVD <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@EmploymentId", SqlDbType.Int).Value = employmentId;
|
||
command.Parameters.Add("@ActivityId", SqlDbType.Int).Value = activityId;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrfrvdDuplicateException();
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void EnsurePrsnFullNameAndExternalIdUnique(SqlConnection connection, string fullName, string externalId, int? excludeId)
|
||
{
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRSN
|
||
WHERE PRFIO = @FullName
|
||
AND
|
||
(
|
||
(PRDPID IS NULL AND @ExternalId IS NULL)
|
||
OR PRDPID = @ExternalId
|
||
)
|
||
AND (@ExcludeId IS NULL OR IDPRSN <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@FullName", SqlDbType.VarChar, PrsnDirectoryRules.FullNameMaxLength).Value = fullName;
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@ExternalId", SqlDbType.VarChar, PrsnDirectoryRules.ExternalIdMaxLength, externalId);
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrsnDuplicateFullNameException(fullName, externalId);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void EnsurePrsnGuidIsUnique(SqlConnection connection, string guid, int? excludeId)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(guid))
|
||
{
|
||
return;
|
||
}
|
||
|
||
const string sql = @"
|
||
SELECT COUNT(1)
|
||
FROM dbo.PRSN
|
||
WHERE GUIDPRSN = @Guid
|
||
AND (@ExcludeId IS NULL OR IDPRSN <> @ExcludeId);";
|
||
|
||
using (var command = new SqlCommand(sql, connection))
|
||
{
|
||
command.CommandTimeout = ReferenceDirectorySqlHelpers.GetCommandTimeoutSeconds();
|
||
command.Parameters.Add("@Guid", SqlDbType.VarChar, PrsnDirectoryRules.GuidMaxLength).Value = guid;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@ExcludeId", excludeId);
|
||
if (Convert.ToInt32(command.ExecuteScalar()) > 0)
|
||
{
|
||
throw CreatePrsnDuplicateGuidException(guid);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static string NormalizeNullable(string value, int maxLength, string fieldDisplayName)
|
||
{
|
||
var normalizedValue = string.IsNullOrWhiteSpace(value) ? null : value.Trim();
|
||
if (normalizedValue != null && normalizedValue.Length > maxLength)
|
||
{
|
||
throw new InvalidOperationException(string.Format("{0} не должно превышать {1} символов.", fieldDisplayName, maxLength));
|
||
}
|
||
|
||
return normalizedValue;
|
||
}
|
||
|
||
private static string NormalizeNotes(string value, int maxLength, string fieldDisplayName)
|
||
{
|
||
var normalizedValue = string.IsNullOrWhiteSpace(value) ? null : value.Trim();
|
||
if (normalizedValue != null && normalizedValue.Length > maxLength)
|
||
{
|
||
throw new InvalidOperationException(string.Format("{0} не должны превышать {1} символов.", fieldDisplayName, maxLength));
|
||
}
|
||
|
||
return normalizedValue;
|
||
}
|
||
|
||
private static PrdspvDirectoryItem NormalizePrdspvItem(PrdspvDirectoryItem item)
|
||
{
|
||
if (item == null)
|
||
{
|
||
throw new InvalidOperationException("Не передана запись PRDSPV.");
|
||
}
|
||
|
||
if (item.EmploymentId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись персонала для клейма.");
|
||
}
|
||
|
||
if (item.StampTypeId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Укажите вид клейма.");
|
||
}
|
||
|
||
var stampCode = string.IsNullOrWhiteSpace(item.StampCode) ? string.Empty : item.StampCode.Trim();
|
||
if (stampCode.Length == 0)
|
||
{
|
||
throw new InvalidOperationException("Укажите шифр клейма.");
|
||
}
|
||
|
||
if (stampCode.Length > PrdspvDirectoryRules.StampCodeMaxLength)
|
||
{
|
||
throw new InvalidOperationException(string.Format("Шифр клейма не должен превышать {0} символов.", PrdspvDirectoryRules.StampCodeMaxLength));
|
||
}
|
||
|
||
return new PrdspvDirectoryItem
|
||
{
|
||
EmploymentId = item.EmploymentId,
|
||
Id = item.Id,
|
||
Notes = NormalizeNullable(item.Notes, PrdspvDirectoryRules.NotesMaxLength, "Дополнительные сведения"),
|
||
ReceivedOn = item.ReceivedOn,
|
||
StampCode = stampCode,
|
||
StampTypeId = item.StampTypeId
|
||
};
|
||
}
|
||
|
||
private static PrfrDirectoryItem NormalizePrfrItem(PrfrDirectoryItem item)
|
||
{
|
||
if (item == null)
|
||
{
|
||
throw new InvalidOperationException("Не передана запись PRFR.");
|
||
}
|
||
|
||
if (item.PersonId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана персона.");
|
||
}
|
||
|
||
if (item.OrganizationId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана организация/подразделение.");
|
||
}
|
||
|
||
return new PrfrDirectoryItem
|
||
{
|
||
DismissalDate = item.DismissalDate,
|
||
EmploymentId = item.EmploymentId,
|
||
Guid = NormalizeNullable(item.Guid, PrfrDirectoryRules.GuidMaxLength, "GUID сотрудника"),
|
||
HireDate = item.HireDate,
|
||
IsPinAuth = item.IsPinAuth,
|
||
LastVacationEndDate = item.LastVacationEndDate,
|
||
LastVacationStartDate = item.LastVacationStartDate,
|
||
NextVacationEndDate = item.NextVacationEndDate,
|
||
NextVacationStartDate = item.NextVacationStartDate,
|
||
OrganizationId = item.OrganizationId,
|
||
PersonId = item.PersonId,
|
||
PersonnelNumber = NormalizeNullable(item.PersonnelNumber, PrfrDirectoryRules.PersonnelNumberMaxLength, "Табельный номер"),
|
||
PinChangedAt = item.PinChangedAt,
|
||
PinHash = string.IsNullOrWhiteSpace(item.PinHash) ? null : item.PinHash.Trim(),
|
||
PositionId = item.PositionId.HasValue && item.PositionId.Value > 0 ? item.PositionId : null,
|
||
PositionStartDate = item.PositionStartDate,
|
||
QualificationPlanDate = item.QualificationPlanDate
|
||
};
|
||
}
|
||
|
||
private static PrfrvdDirectoryItem NormalizePrfrvdItem(PrfrvdDirectoryItem item)
|
||
{
|
||
if (item == null)
|
||
{
|
||
throw new InvalidOperationException("Не передана запись PRFRVD.");
|
||
}
|
||
|
||
if (item.EmploymentId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Не выбрана запись персонала для вида деятельности.");
|
||
}
|
||
|
||
if (item.ActivityId <= 0)
|
||
{
|
||
throw new InvalidOperationException("Укажите вид деятельности персонала.");
|
||
}
|
||
|
||
return new PrfrvdDirectoryItem
|
||
{
|
||
ActivityId = item.ActivityId,
|
||
EmploymentId = item.EmploymentId,
|
||
Id = item.Id
|
||
};
|
||
}
|
||
|
||
private static PrsnDirectoryItem NormalizePrsnItem(PrsnDirectoryItem item)
|
||
{
|
||
if (item == null)
|
||
{
|
||
throw new InvalidOperationException("Не передана запись PRSN.");
|
||
}
|
||
|
||
var fullName = string.IsNullOrWhiteSpace(item.FullName) ? string.Empty : item.FullName.Trim();
|
||
if (fullName.Length == 0)
|
||
{
|
||
throw new InvalidOperationException("Укажите Р¤РРћ.");
|
||
}
|
||
|
||
if (fullName.Length > PrsnDirectoryRules.FullNameMaxLength)
|
||
{
|
||
throw new InvalidOperationException(string.Format("Р¤РРћ РЅРµ должно превышать {0} символов.", PrsnDirectoryRules.FullNameMaxLength));
|
||
}
|
||
|
||
return new PrsnDirectoryItem
|
||
{
|
||
Email = NormalizeNullable(item.Email, PrsnDirectoryRules.EmailMaxLength, "E-mail"),
|
||
ExternalId = NormalizeNullable(item.ExternalId, PrsnDirectoryRules.ExternalIdMaxLength, "Дополнительный идентификатор персоны"),
|
||
FirstName = NormalizeNullable(item.FirstName, PrsnDirectoryRules.FirstNameMaxLength, "РРјСЏ"),
|
||
FullName = fullName,
|
||
Guid = NormalizeNullable(item.Guid, PrsnDirectoryRules.GuidMaxLength, "GUID персоны"),
|
||
Id = item.Id,
|
||
LastName = NormalizeNullable(item.LastName, PrsnDirectoryRules.LastNameMaxLength, "Фамилия"),
|
||
Notes = NormalizeNotes(item.Notes, PrsnDirectoryRules.NotesMaxLength, "Дополнительные сведения о персоне"),
|
||
Patronymic = NormalizeNullable(item.Patronymic, PrsnDirectoryRules.PatronymicMaxLength, "Отчество"),
|
||
Phone = NormalizeNullable(item.Phone, PrsnDirectoryRules.PhoneMaxLength, "Телефон")
|
||
};
|
||
}
|
||
|
||
private static void PopulatePrfrCommand(SqlCommand command, PrfrDirectoryItem item)
|
||
{
|
||
command.Parameters.Add("@OrganizationId", SqlDbType.Int).Value = item.OrganizationId;
|
||
command.Parameters.Add("@PersonId", SqlDbType.Int).Value = item.PersonId;
|
||
ReferenceDirectorySqlHelpers.AddNullableIntParameter(command, "@PositionId", item.PositionId);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@HireDate", item.HireDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@DismissalDate", item.DismissalDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@PositionStartDate", item.PositionStartDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@QualificationPlanDate", item.QualificationPlanDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@LastVacationStartDate", item.LastVacationStartDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@LastVacationEndDate", item.LastVacationEndDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@NextVacationStartDate", item.NextVacationStartDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@NextVacationEndDate", item.NextVacationEndDate);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@PersonnelNumber", SqlDbType.VarChar, PrfrDirectoryRules.PersonnelNumberMaxLength, item.PersonnelNumber);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Guid", SqlDbType.VarChar, PrfrDirectoryRules.GuidMaxLength, item.Guid);
|
||
ReferenceDirectorySqlHelpers.AddNullableTextParameter(command, "@PinHash", item.PinHash);
|
||
ReferenceDirectorySqlHelpers.AddNullableDateTimeParameter(command, "@PinChangedAt", item.PinChangedAt);
|
||
command.Parameters.Add("@IsPinAuth", SqlDbType.Bit).Value = item.IsPinAuth.HasValue ? (object)item.IsPinAuth.Value : DBNull.Value;
|
||
}
|
||
|
||
private static void PopulatePrsnCommand(SqlCommand command, PrsnDirectoryItem item)
|
||
{
|
||
command.Parameters.Add("@FullName", SqlDbType.VarChar, PrsnDirectoryRules.FullNameMaxLength).Value = item.FullName;
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@LastName", SqlDbType.VarChar, PrsnDirectoryRules.LastNameMaxLength, item.LastName);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@FirstName", SqlDbType.VarChar, PrsnDirectoryRules.FirstNameMaxLength, item.FirstName);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Patronymic", SqlDbType.VarChar, PrsnDirectoryRules.PatronymicMaxLength, item.Patronymic);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Phone", SqlDbType.VarChar, PrsnDirectoryRules.PhoneMaxLength, item.Phone);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Email", SqlDbType.VarChar, PrsnDirectoryRules.EmailMaxLength, item.Email);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Notes", SqlDbType.VarChar, PrsnDirectoryRules.NotesMaxLength, item.Notes);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@Guid", SqlDbType.VarChar, PrsnDirectoryRules.GuidMaxLength, item.Guid);
|
||
ReferenceDirectorySqlHelpers.AddNullableStringParameter(command, "@ExternalId", SqlDbType.VarChar, PrsnDirectoryRules.ExternalIdMaxLength, item.ExternalId);
|
||
}
|
||
}
|
||
}
|
||
|