/// <summary>
/// Получает список сотрудников на работе
/// </summary>
/// <returns>
/// Список сотрудников на работе
/// </returns>
public static List<Journal.workerStruct> getWorkersOnWork()
{
var list = new List<Journal.workerStruct>();
IDataReader reader = dbConnector.getdbAcces().readbd(
string.Format(@"select w.*,concat_ws(' ',f,i,o) as fio, concat(f,' ',left(i,1),'. ',left(o,1),'.') as shortfio
,GROUP_CONCAT('[',i.name,']' SEPARATOR ' ') items
from journal j
join workers w on j.worker_id=w.id
left join journal jj on w.id=jj.worker_id and jj.operation_id={1} and isnull(jj.stamp_end)
left join items i on jj.item_id = i.id
where isnull(j.stamp_end) and j.operation_id={0}
GROUP BY w.id
ORDER BY f;",Const.OPERATION_WORK_IN, Const.OPERATION_ITEM_GET));
try
{
while(reader.Read())
{
Journal.workerStruct workerStruct = new Journal.workerStruct();
workerStruct.worker = new Worker((uint)reader["id"],
(string)reader["fio"],
(string)reader["shortfio"],
(string)reader["phone"],
(uint)reader["code"]);
if(reader["items"] != DBNull.Value)
{
workerStruct.key = (string) reader["items"];
}
else
workerStruct.key = "";
list.Add(workerStruct);
}
}
catch(MySqlException ex)
{
Utils.showMessageError(ex.ToString());
}
reader.Close();
reader = null;
return list;
}
/// <summary>
/// Получает список всех сотрудников
/// </summary>
/// <returns>
/// Список всех сотрудников
/// </returns>
public static List<Journal.workerStruct> getAllWorkers()
{
var list = new List<Journal.workerStruct>();
IDataReader reader = dbConnector.getdbAcces().readbd(
@"SELECT w.*, concat_ws(' ',w.f,w.i,w.o) as fio, concat(w.f,' ',left(w.i,1),'. ',left(w.o,1),'.') as shortfio,
GROUP_CONCAT('[',i.name,']' SEPARATOR ' ') items FROM workers w
left join journal j on w.id=j.worker_id and j.operation_id=3 and isnull(j.stamp_end)
left join items i on j.item_id = i.id
GROUP BY w.id
order by f;");
try
{
while(reader.Read())
{
Journal.workerStruct workerStruct = new Journal.workerStruct();
workerStruct.worker = new Worker((uint)reader["id"],
(string)reader["fio"],
(string)reader["shortfio"],
(string)reader["phone"],
(uint)reader["code"]);
if(reader["items"] != DBNull.Value)
workerStruct.key = (string) reader["items"];
else
workerStruct.key = "";
list.Add(workerStruct);
}
}
catch(MySqlException ex)
{
Utils.showMessageError(ex.ToString());
}
reader.Close();
reader = null;
return list;
}