/// <summary>
/// Получение проходов сотрудника
/// </summary>
/// <param name="emplId"></param>
/// <param name="startDateP1"></param>
/// <param name="endDateP1"></param>
/// <param name="startDateP2"></param>
/// <param name="endDateP2"></param>
/// <returns></returns>
public EmployeePeriodsInfoDs CompleteEmployeePeriodsInfoDs(int emplId, DateTime startDateP1, DateTime endDateP1,
DateTime startDateP2, DateTime endDateP2)
{
var sStartDateP1 = startDateP1.ToString("yyyy-MM-dd HH:mm:ss");
var sEndDateP1 = endDateP1.ToString("yyyy-MM-dd HH:mm:ss");
var sStartDateP2 = startDateP2.ToString("yyyy-MM-dd HH:mm:ss");
var sEndDateP2 = endDateP2.ToString("yyyy-MM-dd HH:mm:ss");
var result = new EmployeePeriodsInfoDs();
var sql = string.Format(
"SELECT КодСотрудника, Сотрудник, Employee FROM Сотрудники WHERE КодСотрудника = {0}", emplId);
var dt1 = DBManager.GetData(sql, Global.ConnectionString);
result.Сотрудники.Clear();
result.Сотрудники.Merge(dt1);
sql = string.Format(@"
SELECT КодПроходаСотрудника, КодСотрудника, Когда, КодРасположения
FROM vwПроходыСотрудников (nolock)
WHERE (КодСотрудника = {4}) AND ((Когда >= convert(datetime, '{0}', 120) AND Когда <= convert(datetime, '{1}', 120))
OR (Когда >= convert(datetime, '{2}', 120) AND Когда <= convert(datetime, '{3}', 120)))" , sStartDateP1, sEndDateP1,
sStartDateP2, sEndDateP2, emplId);
var dt2 = DBManager.GetData(sql, Global.ConnectionString);
result.ПроходыСотрудников.Clear();
result.ПроходыСотрудников.Merge(dt2);
return(result);
}
/// <summary>
/// Получение проходов сотрудника
/// </summary>
/// <param name="emplId"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="tz"></param>
/// <returns></returns>
public EmployeePeriodsInfoDs CompleteEmployeePeriodsInfoDs(int emplId, DateTime startDate, DateTime endDate,
int tz)
{
var sStartDate = startDate.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");
var sEndDate = endDate.AddDays(2).ToString("yyyy-MM-dd HH:mm:ss");
var result = new EmployeePeriodsInfoDs();
var sql = string.Format(
"SELECT КодСотрудника, Сотрудник, Employee FROM Сотрудники WHERE КодСотрудника = {0}", emplId);
var dt1 = DBManager.GetData(sql, Global.ConnectionString);
result.Сотрудники.Clear();
result.Сотрудники.Merge(dt1);
sql = string.Format(
@"SELECT КодПроходаСотрудника, КодСотрудника, Dateadd(MINUTE,{2},[Когда]) Когда, КодРасположения FROM vwПроходыСотрудников (nolock)
WHERE (КодСотрудника = {3}) AND (Dateadd(MINUTE,{2},[Когда]) >= convert(datetime, '{0}', 120)) AND (Dateadd(MINUTE,{2},[Когда]) <= convert(datetime, '{1}', 120))",
sStartDate, sEndDate, tz, emplId);
var dt2 = DBManager.GetData(sql, Global.ConnectionString);
result.ПроходыСотрудников.Clear();
result.ПроходыСотрудников.Merge(dt2);
return(result);
}
/// <summary>
/// Получение списка дней за выбранный период
/// </summary>
/// <param name="startPeriodParam">Дата начала</param>
/// <param name="endPeriodParam">Дата конца</param>
/// <param name="semplId">ID сотрудника</param>
/// <param name="stz">Таймзона</param>
public void CompleteDaysTable(DateTime?startPeriodParam, DateTime?endPeriodParam, string semplId, string stz)
{
int tz, emplId;
try
{
tz = Convert.ToInt32(stz);
}
catch
{
tz = 0;
}
try
{
emplId = Convert.ToInt32(semplId);
}
catch
{
emplId = 0;
}
//bool hasNewIds = _needCheckIndex && (EmployeeTimePeriodsInfo.GetMaxId(_startDate, _endDate, _emplId, -tz) != _maxIndex);
//bool sourceChanged = (_startDate == DateTime.MinValue) || (_endDate == DateTime.MinValue) || hasNewIds;
//bool sourceChanged = true;
var startPeriod = startPeriodParam ?? DateTime.MinValue;
var endPeriod = endPeriodParam ?? DateTime.MinValue;
//if (emplId != _emplId || sourceChanged)
//{
// ClearCash();
//}
ClearCash();
if (startPeriod != _startDate || endPeriod != _endDate)
{
IsError = false;
_holidays = CompleteHolidaysInfoDs(startPeriod, endPeriod);
var minDate = DateTime.MaxValue;
var maxDate = DateTime.MinValue;
var temp = _sourceTable.Clone();
foreach (DataRow row in _sourceTable.Rows)
{
if (((DateTime)row["DAY_VALUE"]).Date <= endPeriod.Date &&
((DateTime)row["DAY_VALUE"]).Date >= startPeriod.Date)
{
if (minDate > ((DateTime)row["DAY_VALUE"]).Date)
{
minDate = ((DateTime)row["DAY_VALUE"]).Date;
}
if (maxDate < ((DateTime)row["DAY_VALUE"]).Date)
{
maxDate = ((DateTime)row["DAY_VALUE"]).Date;
}
temp.ImportRow(row);
}
}
_sourceTable.Rows.Clear();
_sourceTable = temp.Copy();
EmployeePeriodsInfoDs periodsDs;
if (maxDate < minDate)
{
_startDate = DateTime.MinValue;
_endDate = DateTime.MinValue;
periodsDs = new ExecQuery().CompleteEmployeePeriodsInfoDs(emplId, startPeriod.Date, endPeriod.Date,
-tz);
}
else
{
if (minDate.Date != startPeriod.Date && maxDate.Date != endPeriod.Date)
{
periodsDs = new ExecQuery().CompleteEmployeePeriodsInfoDs(emplId, startPeriod.Date,
minDate.Date, maxDate.Date, endPeriod.Date.AddDays(1));
}
else if (minDate.Date != startPeriod.Date)
{
periodsDs = new ExecQuery().CompleteEmployeePeriodsInfoDs(emplId, startPeriod.Date,
minDate.Date, -tz);
}
else if (maxDate.Date != endPeriod.Date)
{
periodsDs = new ExecQuery().CompleteEmployeePeriodsInfoDs(emplId, maxDate.Date, endPeriod.Date,
-tz);
}
else
{
periodsDs = new EmployeePeriodsInfoDs();
}
_startDate = minDate;
_endDate = maxDate;
}
if (periodsDs.Сотрудники.Rows.Count > 0)
{
_empNameRus = ((EmployeePeriodsInfoDs.СотрудникиRow)periodsDs.Сотрудники.Rows[0]).Сотрудник;
_empNameLat = ((EmployeePeriodsInfoDs.СотрудникиRow)periodsDs.Сотрудники.Rows[0]).Employee;
}
var dayIntervals = new EmployeeTimeInervals();
for (var curentDay = startPeriod; curentDay <= endPeriod; curentDay = curentDay.AddDays(1))
{
dayIntervals.Add(new TimeInterval(curentDay.Date, curentDay.Date.AddDays(1), -1));
}
var isEnterPrevDayByDay = false;
foreach (TimeInterval curInterval in dayIntervals)
{
if (curInterval.StartTime.Date >= _startDate.Date &&
curInterval.StartTime.Date <= _endDate.Date)
{
continue;
}
var periodsInfo = new EmployeeTimePeriodsInfo(curInterval.StartTime, curInterval.EndTime, emplId,
PrimaryEmployeeCalc, periodsDs.ПроходыСотрудников, true, isEnterPrevDayByDay);
isEnterPrevDayByDay = periodsInfo.IsEnterPrevDayByDay;
var internetAccessInfo =
new EmployeeInternetAccessInfo(curInterval.StartTime, curInterval.EndTime, emplId);
object linkCell = null;
if (periodsInfo.EntranceTime != null || periodsInfo.ExitTime != null ||
periodsInfo.ExitTimePrevDay != null && periodsInfo.EnterTimeNextDay != null)
{
linkCell = string.Format(
"<A href=\"#\" onclick=\"cmd('cmd', 'openDetails', 'id', '{0}', 'from', '{1}', 'to', '{2}');\"><IMG src=\"{3}detail.GIF\" border=\"0\" title=\"{4}\"></A>",
emplId, curInterval.StartTime, curInterval.EndTime, Global.Styles,
_resx.GetString("hPageSubTitleDetails2"));
}
var totalWorkTime = periodsInfo.TotalWorkTime;
var totalAbsentTime = periodsInfo.TotalAbsentTime;
var strEntranceTime = "";
var strExitTime = "";
var isEnterAfterExit = "";
var isEnterAfterExit2 = "";
if (periodsInfo.ExitTime != null)
{
strExitTime = ((DateTime)periodsInfo.ExitTime).ToString("HH:mm:ss");
}
if (periodsInfo.EntranceTime != null)
{
strEntranceTime = ((DateTime)periodsInfo.EntranceTime).ToString("HH:mm:ss");
}
if (periodsInfo.ExitTime != null && periodsInfo.EntranceTime != null &&
(DateTime)periodsInfo.EntranceTime > (DateTime)periodsInfo.ExitTime)
{
isEnterAfterExit = "<div>00:00:00</div><hr />";
isEnterAfterExit2 = "<hr /><div>24:00:00</div>";
}
if (periodsInfo.ExitTime == null && periodsInfo.EntranceTime == null &&
periodsInfo.ExitTimePrevDay != null && periodsInfo.EnterTimeNextDay != null)
{
//strExitTime = String.Format("{0}-{1}-{2} {3}:{4}:{5}", ((DateTime)periodsInfo.ExitTimePrevDay).Year.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Month.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.ExitTimePrevDay).Day.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Hour.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.ExitTimePrevDay).Minute.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Second.ToString(CultureInfo.InvariantCulture));
strExitTime = ((DateTime)periodsInfo.ExitTimePrevDay).ToString("HH:mm:ss");
//strEntranceTime = String.Format("{0}-{1}-{2} {3}:{4}:{5}", ((DateTime)periodsInfo.EnterTimeNextDay).Year.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Month.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.EnterTimeNextDay).Day.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Hour.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.EnterTimeNextDay).Minute.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Second.ToString(CultureInfo.InvariantCulture));
strEntranceTime = ((DateTime)periodsInfo.EnterTimeNextDay).ToString("HH:mm:ss");
isEnterAfterExit = "<div>00:00:00</div><hr />";
isEnterAfterExit2 = "<hr /><div>24:00:00</div>";
}
if (periodsInfo.ExitTime == null && periodsInfo.EntranceTime != null &&
periodsInfo.EnterTimeNextDay != null)
{
strExitTime = "24:00:00";
}
if (periodsInfo.ExitTime != null && periodsInfo.EntranceTime == null &&
periodsInfo.ExitTimePrevDay != null)
{
strEntranceTime = "00:00:00";
}
object absentTime = null;
object workTime = null;
if (totalAbsentTime.TotalSeconds > 0)
{
absentTime = string.Format("{0}:{1}:{2}", totalAbsentTime.Hours + totalAbsentTime.Days * 24,
totalAbsentTime.Minutes.ToString("D2"), totalAbsentTime.Seconds.ToString("D2"));
}
if (totalWorkTime.TotalSeconds > 0)
{
//workTime = String.Format("<P align=\"center\">{0}:{1}:{2}</P>", totalWorkTime.Hours + (totalWorkTime.Days * 24), totalWorkTime.Minutes.ToString("D2"),
// totalWorkTime.Seconds.ToString("D2"));
workTime = string.Format("{0}:{1}:{2}", totalWorkTime.Hours + totalWorkTime.Days * 24,
totalWorkTime.Minutes.ToString("D2"), totalWorkTime.Seconds.ToString("D2"));
if (periodsInfo.HasErrors)
{
workTime = workTime + " *";
}
}
var internetAccessInfoCount = internetAccessInfo.Count;
var internetAccessCount = "";
if (internetAccessInfoCount != 0)
{
internetAccessCount = internetAccessInfoCount.ToString(CultureInfo.InvariantCulture);
}
string internetAccessTotal;
var internetAccessInfoTotal = internetAccessInfo.Total;
if (internetAccessInfoTotal == 0)
{
internetAccessTotal = "";
}
else
{
internetAccessTotal = new TimeSpan(0, 0, internetAccessInfoTotal).ToString();
}
_sourceTable.Rows.Add(linkCell, curInterval.StartTime, strEntranceTime, strExitTime, absentTime,
workTime, totalAbsentTime, totalWorkTime, periodsInfo.HasErrors ? "errorRow" : "singleRow",
internetAccessCount, internetAccessTotal, isEnterAfterExit, isEnterAfterExit2);
if (periodsInfo.HasErrors && !IsError)
{
IsError = true;
}
}
_emplId = emplId;
_startDate = startPeriod;
_endDate = endPeriod;
//_maxIndex = EmployeeTimePeriodsInfo.GetMaxId(_startDate, _endDate, _emplId, -tz);
//_needCheckIndex = (_maxIndex == EmployeeTimePeriodsInfo.GetMaxId(_emplId)) || (_maxIndex == -1);
}
}
/// <summary>
/// Получение проходов сотрудника
/// </summary>
/// <param name="emplName"></param>
/// <param name="companyInfo"></param>
/// <param name="postInfo"></param>
/// <param name="divisionInfo"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="isRusLocal"></param>
/// <param name="tz"></param>
/// <param name="activeTimeExit"></param>
/// <param name="tm"></param>
/// <param name="active"></param>
/// <param name="isReversCompany"></param>
/// <param name="isReversSubdivision"></param>
/// <param name="isReversPosition"></param>
/// <param name="isReversPerson"></param>
/// <param name="subEmpl"></param>
/// <returns></returns>
public EmployeePeriodsInfoDs CompleteEmployeePeriodsInfoDs(string emplName, string companyInfo, string postInfo,
string divisionInfo, DateTime startDate, DateTime endDate,
bool isRusLocal, int tz, bool activeTimeExit, DateTime tm, bool active, string isReversCompany,
string isReversSubdivision, string isReversPosition, string isReversPerson,
bool subEmpl)
{
var activeTimeExitVerify = startDate.Equals(endDate);
var sStartDate = startDate.ToString("yyyy-MM-dd HH:mm:ss");
var sEndDate = endDate.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss");
var timeExitRestriction = "";
var timeExitLocation = "";
var clauseCompany = 0;
var clauseSubdivision = 0;
var clausePosition = 0;
var clausePerson = 0;
if (isReversCompany == "1")
{
clauseCompany = 1;
}
else if (isReversCompany == "3")
{
clauseCompany = 2;
}
else if (isReversCompany == "2")
{
clauseCompany = 3;
}
if (isReversSubdivision == "1")
{
clauseSubdivision = 1;
}
else if (isReversSubdivision == "3")
{
clauseSubdivision = 2;
}
else if (isReversSubdivision == "2")
{
clauseSubdivision = 3;
}
if (isReversPosition == "1")
{
clausePosition = 1;
}
else if (isReversPosition == "3")
{
clausePosition = 2;
}
else if (isReversPosition == "2")
{
clausePosition = 3;
}
if (isReversPerson == "1")
{
clausePerson = 1;
}
else if (isReversPerson == "3")
{
clausePerson = 2;
}
else if (isReversPerson == "2")
{
clausePerson = 3;
}
if (activeTimeExitVerify && activeTimeExit)
{
timeExitLocation = @"
AND TE0.КодРасположения IS NULL";
var sTimeExit = tm.ToString("yyyy-MM-dd HH:mm:ss");
timeExitRestriction = string.Format(@"
AND NOT EXISTS(SELECT * FROM vwПроходыСотрудников TE1 (nolock)
WHERE TE0.КодСотрудника = TE1.КодСотрудника
AND Dateadd(MINUTE,{1},TE1.[Когда]) > Dateadd(MINUTE,{1},TE0.[Когда])
AND Dateadd(MINUTE,{1},TE1.[Когда]) < CONVERT(datetime, '{0}', 120))" ,
sEndDate, tz);
sEndDate = sTimeExit;
}
var result = new EmployeePeriodsInfoDs();
var additionalInnerJoin = string.Empty;
var additionalRestrictions = string.Empty;
var additionalRestrictionsCr = string.Empty;
if (!emplName.Equals(string.Empty) && clausePerson != 2)
{
additionalRestrictions = string.Format(" AND {2} T0.КодСотрудника {1} IN ({0})", emplName,
clausePerson == 1 ? "NOT" : "", string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clausePerson == 2)
{
additionalRestrictions = string.Format(" AND {0} T0.КодСотрудника IS NULL",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (!postInfo.Equals(string.Empty) || !divisionInfo.Equals(string.Empty) ||
!companyInfo.Equals(string.Empty) || clausePosition == 2 || clauseSubdivision == 2 || clauseCompany == 2 ||
clausePosition == 3 || clauseSubdivision == 3 || clauseCompany == 3)
{
additionalInnerJoin = " LEFT JOIN vwДолжности AS T1 ON (T0.КодСотрудника = T1.КодСотрудника)";
}
if (!postInfo.Equals(string.Empty) && clausePosition != 2 && clausePosition != 3)
{
additionalRestrictions += string.Format(" AND {2} T1.Должность {1} IN ({0})", postInfo,
clausePosition == 1 ? "NOT" : "", string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clausePosition == 2)
{
additionalRestrictions += string.Format(" AND {0} (T1.Должность IS NULL OR T1.Должность = '')",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clausePosition == 3)
{
additionalRestrictions += string.Format(" AND {0} (T1.Должность IS NOT NULL AND T1.Должность <> '')",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (!divisionInfo.Equals(string.Empty) && clauseSubdivision != 2 && clauseSubdivision != 3)
{
additionalRestrictions += string.Format(
" AND {2} EXISTS (SELECT P0.КодДолжности FROM vwДолжности AS P0 WHERE (T1.L >= P0.L AND T1.R <= P0.R) AND P0.Подразделение {1} IN ({0}))",
divisionInfo, clauseSubdivision == 1 ? "NOT" : "",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clauseSubdivision == 2)
{
additionalRestrictions += string.Format(
" AND {0} EXISTS (SELECT P0.КодДолжности FROM vwДолжности AS P0 WHERE (T1.L >= P0.L AND T1.R <= P0.R) AND (P0.Подразделение IS NULL OR P0.Подразделение = ''))",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clauseSubdivision == 3)
{
additionalRestrictions += string.Format(
" AND {0} EXISTS (SELECT P0.КодДолжности FROM vwДолжности AS P0 WHERE (T1.L >= P0.L AND T1.R <= P0.R) AND (P0.Подразделение IS NOT NULL AND P0.Подразделение <> ''))",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (!companyInfo.Equals(string.Empty) && clauseCompany != 2)
{
additionalRestrictions += string.Format(" AND {2} T0.КодЛицаЗаказчика {1} IN ({0})", companyInfo,
clauseCompany == 1 ? "NOT" : "", string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
if (clauseCompany == 2)
{
additionalRestrictions += string.Format(" AND {0} T0.КодЛицаЗаказчика IS NULL",
string.IsNullOrEmpty(additionalRestrictions) ? "(" : " ");
}
var dateRestriction = string.Format(
" Dateadd(MINUTE,{2},TE0.[Когда]) > CONVERT(datetime, '{0}', 120) AND Dateadd(MINUTE,{2},TE0.[Когда]) < CONVERT(datetime, '{1}', 120)",
sStartDate, sEndDate, tz);
const string queryEmplTempl = @"
SELECT T0.КодСотрудника{0}
FROM Сотрудники AS T0 {1}
WHERE T0.КодСотрудника IN (SELECT TE0.КодСотрудника FROM vwПроходыСотрудников TE0 (nolock) WHERE {5} {6} {4}){2}{3}";
var personWhere = string.Format(@"
WHERE (T0.КодСотрудника IN (SELECT TE0.КодСотрудника FROM vwПроходыСотрудников TE0 (nolock)
WHERE DATEADD(minute, {0}, TE0.[Когда]) >= CONVERT(datetime, '{1}', 120) AND DATEADD(minute, {0}, TE0.[Когда]) <= CONVERT(datetime, '{2}', 120)",
tz, sStartDate, sEndDate);
//string personActive = active ? "" : " OR T0.КодСотрудника IN (SELECT КодСотрудника FROM vwСотрудникиИмеющиеДолжностиИлиКарточки)";
var personActive = active
? ""
: @" OR T0.КодСотрудника IN (SELECT КодСотрудника FROM(SELECT КодСотрудника FROM vwДолжности WHERE КодСотрудника IS NOT NULL
UNION SELECT КодСотрудника FROM КарточкиСотрудников) X
WHERE КодСотрудника IN (SELECT КодСотрудника FROM vwПодчинённые)
OR EXISTS (SELECT * FROM dbo.fn_ТекущиеРоли() Y
WHERE КодРоли IN(31,32,33) AND (КодЛица = 0 OR КодЛица IN(SELECT КодЛица FROM vwДолжности UNION SELECT КодЛицаЗаказчика FROM Сотрудники))))" ;
//string personSubEmpl = !subEmpl ? "" : @" OR T0.КодСотрудника IN (SELECT КодСотрудника FROM vwПодчинённые)";
var startAddFilter = "";
var endAddFilter = "";
if (subEmpl && (!string.IsNullOrEmpty(divisionInfo) || !string.IsNullOrEmpty(postInfo) ||
!string.IsNullOrEmpty(emplName)))
{
startAddFilter = " OR (";
endAddFilter = ")";
}
var divisionInfoSubEmpl = !subEmpl || string.IsNullOrEmpty(divisionInfo)
? ""
: string.Format(@"T0.КодСотрудника IN (SELECT DISTINCT Должности.КодСотрудника
FROM dbo.vwДолжности Chief
INNER JOIN dbo.vwДолжности Должности ON Chief.L <= Должности.L AND Chief.R >= Должности.R
WHERE Должности.КодСотрудника IS NOT NULL AND
Chief.КодДолжности IN
(
SELECT КодДолжности
FROM dbo.vwДолжности
WHERE Подразделение IN ({0})
UNION
SELECT Slave.КодДолжности
FROM dbo.ПодчинениеАдминистративное Chief INNER JOIN
dbo.ПодчинениеАдминистративное Slave ON Chief.L < Slave.L AND Chief.R > Slave.R INNER JOIN
dbo.vwДолжности ON Chief.КодДолжности = dbo.vwДолжности.КодДолжности
WHERE Подразделение IN ({0})
))" , divisionInfo);
var startdivisionFilter = "";
if (subEmpl && !string.IsNullOrEmpty(divisionInfo))
{
startdivisionFilter = " AND ";
}
var postInfoSubEmpl = !subEmpl || string.IsNullOrEmpty(postInfo)
? ""
: string.Format(@"{1}T0.КодСотрудника IN (SELECT DISTINCT Должности.КодСотрудника
FROM dbo.vwДолжности Chief
INNER JOIN dbo.vwДолжности Должности ON Chief.L <= Должности.L AND Chief.R >= Должности.R
WHERE Должности.КодСотрудника IS NOT NULL AND
Chief.КодДолжности IN
(
SELECT КодДолжности
FROM dbo.vwДолжности
WHERE Должность IN ({0})
UNION
SELECT Slave.КодДолжности
FROM dbo.ПодчинениеАдминистративное Chief INNER JOIN
dbo.ПодчинениеАдминистративное Slave ON Chief.L < Slave.L AND Chief.R > Slave.R INNER JOIN
dbo.vwДолжности ON Chief.КодДолжности = dbo.vwДолжности.КодДолжности
WHERE Должность IN ({0})
))" , postInfo, startdivisionFilter);
var startpostFilter = "";
if (subEmpl && (!string.IsNullOrEmpty(divisionInfo) || !string.IsNullOrEmpty(postInfo)))
{
startpostFilter = " AND ";
}
var personSubEmpl = !subEmpl || string.IsNullOrEmpty(emplName)
? ""
: string.Format(@"{1}T0.КодСотрудника IN (SELECT DISTINCT Должности.КодСотрудника
FROM dbo.vwДолжности Chief
INNER JOIN dbo.vwДолжности Должности ON Chief.L <= Должности.L AND Chief.R >= Должности.R
WHERE Должности.КодСотрудника IS NOT NULL AND
Chief.КодДолжности IN
(
SELECT КодДолжности
FROM dbo.vwДолжности
WHERE КодСотрудника IN ({0})
UNION
SELECT Slave.КодДолжности
FROM dbo.ПодчинениеАдминистративное Chief INNER JOIN
dbo.ПодчинениеАдминистративное Slave ON Chief.L < Slave.L AND Chief.R > Slave.R INNER JOIN
dbo.vwДолжности ON Chief.КодДолжности = dbo.vwДолжности.КодДолжности
WHERE КодСотрудника IN ({0})
))" , emplName, startpostFilter);
var endRestrictions = "";
if (!additionalRestrictions.Equals(string.Empty))
{
endRestrictions = " ) ";
}
var personSearch = string.Format(
@"SELECT DISTINCT T0.КодСотрудника, CONVERT(varchar, COALESCE(T0.КодЛица, -1)) КодЛица, T0.Сотрудник, T0.Employee FROM Сотрудники T0
{0}{1}{2}{3}){4}){5}{9}{6}{7}{8}{10}{11}
ORDER BY T0.Сотрудник", additionalInnerJoin, personWhere, timeExitLocation, timeExitRestriction, personActive,
additionalRestrictions,
divisionInfoSubEmpl, postInfoSubEmpl, personSubEmpl, startAddFilter, endAddFilter, endRestrictions);
// string entranceQuery = @"
//SELECT TE0.КодПроходаСотрудника, TE0.КодСотрудника, Dateadd(MINUTE," + tz + @",TE0.[Когда]) Когда, TE0.КодРасположения
//FROM vwПроходыСотрудников AS TE0 (nolock) WHERE КодСотрудника IN ("
// + string.Format(queryEmplTempl, "", additionalInnerJoin, additionalRestrictionsCr, additionalRestrictions, timeExitRestriction, dateRestriction, timeExitLocation) + ")";
var entranceQuery = @"
SELECT TE0.КодПроходаСотрудника, TE0.КодСотрудника, Dateadd(MINUTE," + tz + @",TE0.[Когда]) Когда, TE0.КодРасположения
FROM vwПроходыСотрудников AS TE0 (nolock) WHERE КодСотрудника IN ("
+ string.Format(queryEmplTempl, "", additionalInnerJoin, additionalRestrictionsCr,
additionalRestrictions, timeExitRestriction, dateRestriction, timeExitLocation) +
string.Format("{0}{1}{2}{3}{4}{5})", startAddFilter, divisionInfoSubEmpl,
postInfoSubEmpl, personSubEmpl, endAddFilter, endRestrictions);
//var args = new Dictionary<string, object>();
//args.Add("@ОтображатьВсех", active ? 0 : 1);
//args.Add("@УшедшиеДо", !(activeTimeExitVerify && activeTimeExit) ? 0 : 1);
//args.Add("@ДатаНачала", startDate);
//args.Add("@ДатаКонца", Convert.ToDateTime(sEndDate));//endDate.AddDays(1));
//args.Add("@ТаймЗона", tz);
//args.Add("@ФИО", emplName);
//args.Add("@Должность", postInfo);
//args.Add("@Подразделение", divisionInfo);
//args.Add("@КодЛица", companyInfo);
//args.Add("@РеверсКомпаний", clauseCompany);
//args.Add("@РеверсПодразделений", clauseSubdivision);
//args.Add("@РеверсДолжностей", clausePosition);
//args.Add("@РеверсСотрудников", clausePerson);
//DataTable dt1 = DBManager.GetData("sp_УчетРабочегоВремени_Поиск", Global.ConnectionString, CommandType.StoredProcedure, args);
var dt1 = DBManager.GetData(personSearch, Global.ConnectionString);
result.Сотрудники.Clear();
result.Сотрудники.Merge(dt1);
var dt2 = DBManager.GetData(entranceQuery, Global.ConnectionString);
result.ПроходыСотрудников.Clear();
result.ПроходыСотрудников.Merge(dt2);
return(result);
}
public void FillPage(EmployeePeriodsInfoDs periodsDs, DateTime _startPeriod, DateTime _endPeriod,
int currentPage, int rowsPerPage)
{
//foreach (EmployeePeriodsInfoDs.СотрудникиRow row in periodsDs.Сотрудники.Rows)
_sourceTable.Rows.Clear();
CountEmployee = periodsDs.Сотрудники.Rows.Count;
var startPosition = currentPage * rowsPerPage - rowsPerPage;
var endPosition = currentPage * rowsPerPage;
if (endPosition >= CountEmployee)
{
endPosition = CountEmployee;
}
for (var i = startPosition; i < endPosition; i++)
{
var row = (EmployeePeriodsInfoDs.СотрудникиRow)periodsDs.Сотрудники.Rows[i];
var periodsInfo = new EmployeeTimePeriodsInfo(_startPeriod, _endPeriod, row.КодСотрудника,
PrimaryEmployeeCalc, periodsDs.ПроходыСотрудников);
var empName = _sourcePage.IsRusLocal ? row.Сотрудник : row.Employee;
var strEntranceTime = "";
var strExitTime = "";
var isEnterAfterExit = "";
var isEnterAfterExit2 = "";
if (periodsInfo.ExitTime != null)
{
strExitTime = ((DateTime)periodsInfo.ExitTime).ToString("HH:mm:ss");
}
if (periodsInfo.EntranceTime != null)
{
strEntranceTime = ((DateTime)periodsInfo.EntranceTime).ToString("HH:mm:ss");
}
if (periodsInfo.ExitTime != null && periodsInfo.EntranceTime != null &&
(DateTime)periodsInfo.EntranceTime > (DateTime)periodsInfo.ExitTime)
{
isEnterAfterExit = "<div>00:00:00</div><hr />";
isEnterAfterExit2 = "<hr /><div>24:00:00</div>";
}
if (periodsInfo.ExitTime == null && periodsInfo.EntranceTime == null &&
periodsInfo.ExitTimePrevDay != null && periodsInfo.EnterTimeNextDay != null)
{
//strExitTime = String.Format("{0}-{1}-{2} {3}:{4}:{5}", ((DateTime)periodsInfo.ExitTimePrevDay).Year.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Month.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.ExitTimePrevDay).Day.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Hour.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.ExitTimePrevDay).Minute.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.ExitTimePrevDay).Second.ToString(CultureInfo.InvariantCulture));
strExitTime = ((DateTime)periodsInfo.ExitTimePrevDay).ToString("HH:mm:ss");
//strEntranceTime = String.Format("{0}-{1}-{2} {3}:{4}:{5}", ((DateTime)periodsInfo.EnterTimeNextDay).Year.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Month.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.EnterTimeNextDay).Day.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Hour.ToString(CultureInfo.InvariantCulture), ((DateTime)periodsInfo.EnterTimeNextDay).Minute.ToString(CultureInfo.InvariantCulture),
// ((DateTime)periodsInfo.EnterTimeNextDay).Second.ToString(CultureInfo.InvariantCulture));
strEntranceTime = ((DateTime)periodsInfo.EnterTimeNextDay).ToString("HH:mm:ss");
isEnterAfterExit = "<div>00:00:00</div><hr />";
isEnterAfterExit2 = "<hr /><div>24:00:00</div>";
}
if (periodsInfo.ExitTime == null && periodsInfo.EntranceTime != null &&
periodsInfo.EnterTimeNextDay != null)
{
strExitTime = "24:00:00";
}
if (periodsInfo.ExitTime != null && periodsInfo.EntranceTime == null &&
periodsInfo.ExitTimePrevDay != null)
{
strEntranceTime = "00:00:00";
}
object absentTime;
var periodsInfoTotalAbsentTime = periodsInfo.TotalAbsentTime;
if ((int)periodsInfoTotalAbsentTime.TotalSeconds != 0)
{
absentTime = string.Format("{0}:{1}:{2}",
periodsInfoTotalAbsentTime.Hours + periodsInfoTotalAbsentTime.Days * 24,
periodsInfoTotalAbsentTime.Minutes.ToString("D2"),
periodsInfoTotalAbsentTime.Seconds.ToString("D2"));
}
else
{
absentTime = "";
}
object workTime;
var periodsInfoTotalWorkTime = periodsInfo.TotalWorkTime;
if ((int)periodsInfoTotalWorkTime.TotalSeconds != 0)
{
workTime = string.Format("{0}:{1}:{2}",
periodsInfoTotalWorkTime.Hours + periodsInfoTotalWorkTime.Days * 24,
periodsInfoTotalWorkTime.Minutes.ToString("D2"),
periodsInfoTotalWorkTime.Seconds.ToString("D2"));
}
else
{
workTime = "";
}
if (periodsInfo.HasErrors)
{
workTime = workTime + " *";
}
_sourceTable.Rows.Add(row.КодСотрудника, row.КодЛица, null, null, strEntranceTime, strExitTime,
absentTime, workTime, periodsInfoTotalAbsentTime, periodsInfoTotalWorkTime, empName,
periodsInfo.HasErrors ? "errorRow" : "singleRow", "", "", isEnterAfterExit, isEnterAfterExit2);
if (periodsInfo.HasErrors && !IsError)
{
IsError = true;
}
}
}
/// <summary>
/// Расчет проходов сотрудников
/// </summary>
/// <param name="startPeriod"></param>
/// <param name="endPeriod"></param>
/// <param name="filterEmployee"></param>
/// <param name="filterCompany"></param>
/// <param name="filterPost"></param>
/// <param name="filterSubdiv"></param>
/// <param name="filterTimeExitActive"></param>
/// <param name="tm"></param>
/// <param name="filterActiveOnly"></param>
/// <param name="stz"></param>
/// <param name="isReversCompany"></param>
/// <param name="isReversSubdivision"></param>
/// <param name="isReversPosition"></param>
/// <param name="isReversPerson"></param>
/// <param name="currentPage"> </param>
/// <param name="rowsPerPage"> </param>
/// <param name="subEmpl"> </param>
public void CompleteEmplTable(DateTime?startPeriod, DateTime?endPeriod, string filterEmployee,
string filterCompany, string filterPost, string filterSubdiv,
bool filterTimeExitActive, DateTime tm, bool filterActiveOnly, string stz, string isReversCompany,
string isReversSubdivision, string isReversPosition, string isReversPerson,
int currentPage, int rowsPerPage, bool subEmpl)
{
int tz;
try
{
tz = Convert.ToInt32(stz);
}
catch
{
tz = 0;
}
var hasNewIds = _needCheckId && EmployeeTimePeriodsInfo.GetMaxId(_startDate, _endDate, -tz) != _maxIndex;
var isChanged = _startDate == DateTime.MinValue || _endDate == DateTime.MinValue || hasNewIds;
var _startPeriod = startPeriod ?? new DateTime(1753, 1, 2);
var _endPeriod = endPeriod ?? new DateTime(1753, 1, 2);
if (startPeriod != _startDate || endPeriod != _endDate || !filterEmployee.Equals(_filterEmployee) ||
!filterCompany.Equals(_filterCompany) || !filterPost.Equals(_filterPost) ||
!filterSubdiv.Equals(_filterSubdiv) || isChanged || _filterActiveOnly != filterActiveOnly ||
_isReversCompany != isReversCompany || _isReversSubdivision != isReversSubdivision ||
_isReversPosition != isReversPosition || _isReversPerson != isReversPerson ||
_subEmpl != subEmpl)
{
ClearCash();
IsError = false;
periodsDs = new ExecQuery().CompleteEmployeePeriodsInfoDs(filterEmployee, filterCompany, filterPost,
filterSubdiv, _startPeriod, _endPeriod,
_sourcePage.IsRusLocal, -tz, filterTimeExitActive, tm, filterActiveOnly, isReversCompany,
isReversSubdivision, isReversPosition, isReversPerson, subEmpl);
FillPage(periodsDs, _startPeriod, _endPeriod, currentPage, rowsPerPage);
_startDate = _startPeriod;
_endDate = _endPeriod;
_filterEmployee = filterEmployee;
_filterCompany = filterCompany;
_filterPost = filterPost;
_filterActiveOnly = filterActiveOnly;
_filterSubdiv = filterSubdiv;
_isReversCompany = isReversCompany;
_isReversSubdivision = isReversSubdivision;
_isReversPosition = isReversPosition;
_isReversPerson = isReversPerson;
_maxIndex = EmployeeTimePeriodsInfo.GetMaxId(_startDate, _endDate, -tz);
_needCheckId = _maxIndex == EmployeeTimePeriodsInfo.GetMaxId() || _maxIndex == -1;
_currentPage = currentPage;
_rowsPerPage = rowsPerPage;
_subEmpl = subEmpl;
}
else if (currentPage != _currentPage || rowsPerPage != _rowsPerPage)
{
FillPage(periodsDs, _startPeriod, _endPeriod, currentPage, rowsPerPage);
_currentPage = currentPage;
_rowsPerPage = rowsPerPage;
}
}
