public static DataSet GetFilteredEmployees(Guid userGuid, string filteredText)
{
var myAdapter = new SqlDataAdapter();
SqlConnection connection = GetConnection();
var cmd = new SqlCommand("[GetFilteredDepartmentStateEmployees]", connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters["@UserGuid"].Value = @userGuid;
cmd.Parameters.Add("@EmployeeFilterText", SqlDbType.NVarChar);
cmd.Parameters["@EmployeeFilterText"].Value = @filteredText;
myAdapter.SelectCommand = cmd;
var myDataSet = new DataSet();
try
{
connection.Open();
myAdapter.Fill(myDataSet);
return(myDataSet);
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка GetFilteredEmployees", ex);
//throw (ex);
}
finally
{
connection.Close();
}
return(null);
}
public static Guid GetUserDivision(Guid userGuid, out string divisionName)
{
Guid userDivision = Guid.Empty;
divisionName = string.Empty;
SqlConnection connection = GetConnection();
var cmd = new SqlCommand("[GetEmployeeDivision]", connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters["@UserGuid"].Value = @userGuid;
SqlDataReader reader = null;
try
{
connection.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
userDivision = reader.GetGuid(0);
divisionName = reader.GetString(1);
}
}
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка GetEmployeeDivision", ex);
//Response.Write("<script>window.alert('Ошибка приложения');</script>");
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
connection.Close();
}
return(userDivision);
}
public static string GetUserFullName(string login, out Guid userGuid)
{
if (!string.IsNullOrEmpty(login))
{
string commandText = "select Id,Name from Employee where AccountName='" + login + "'";
string userName = string.Empty;
userGuid = Guid.Empty;
SqlConnection connection = GetConnection();
var cmd = new SqlCommand(commandText, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
userGuid = reader.GetGuid(0);
userName = reader.GetString(1);
}
}
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка GetUserFullNameв", ex);
//Response.Write("<script>window.alert('Ошибка приложения');</script>");
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
connection.Close();
}
return(userName);
}
userGuid = Guid.Empty;
return("Пустой Логин");
}
public static bool CheckIfUserIsAdministrator(Guid userGuid)
{
bool isAdmin = false;
SqlConnection connection = GetConnection();
var cmd = new SqlCommand("[GetIfEmployeeIsAdmin]", connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters["@UserGuid"].Value = @userGuid;
SqlDataReader reader = null;
try
{
connection.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
isAdmin = reader.GetBoolean(0);
}
}
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка GetEmployeeDivision", ex);
//Response.Write("<script>window.alert('Ошибка приложения');</script>");
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
connection.Close();
}
return(isAdmin);
}
public static string GetUserSpecificPhoneNumbers(Guid userGuid)
{
string userPhoneNumbers = string.Empty;
SqlConnection connection = GetConnection();
var cmd = new SqlCommand("[GetUserSpecificPhoneNumbers]", connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters["@UserGuid"].Value = @userGuid;
SqlDataReader reader = null;
try
{
connection.Open();
reader = cmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
if (i > 0)
{
userPhoneNumbers += "; ";
}
string cityCode = "(Пусто)";
if (!reader.IsDBNull(3))
{
cityCode = "(" + reader.GetString(3) + ")";
}
string phoneType = string.Empty;
if (!reader.IsDBNull(6))
{
phoneType = reader.GetString(6);
}
string parsedPhoneNumber = GUIHelper.ParsePhone(reader.GetString(0), phoneType);
if (phoneType.Trim().ToUpper() == "ГАТС")
{
userPhoneNumbers += cityCode + parsedPhoneNumber;
}
else
{
userPhoneNumbers += parsedPhoneNumber;
}
i++;
}
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка [GetUserSpecificPhoneNumbers]", ex);
//Response.Write("<script>window.alert('Ошибка приложения');</script>");
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
connection.Close();
}
return(userPhoneNumbers);
}
public static string ParsePhone(string getString, string phoneType)
{
try
{
char[] phoneArra = getString.ToCharArray();
var parsedPhone = new List <string>();
var phoneNumberPair = new List <char>();
int phoneSimbolsPair = 0;
bool isTripleNumbersAdded = false;
for (int i = phoneArra.Count() - 1; i >= 0; i--)
{
if (char.IsNumber(phoneArra[i]))
{
phoneNumberPair.Insert(0, phoneArra[i]);
}
else
{
continue;
}
if (phoneNumberPair.Count() == 2 && i >= 2 && phoneSimbolsPair < 2)
{
string phoneItem = "-";
phoneItem = phoneNumberPair.Aggregate(phoneItem, (current, simbol) => current + simbol);
parsedPhone.Insert(0, phoneItem);
phoneNumberPair.Clear();
phoneSimbolsPair++;
}
else if (phoneSimbolsPair == 2 &&
phoneNumberPair.Count() == 3 && !isTripleNumbersAdded)
{
string phone = string.Empty;
phone = phoneNumberPair.Aggregate(phone, (current, simbol) => current + simbol);
parsedPhone.Insert(0, phone);
phoneNumberPair.Clear();
isTripleNumbersAdded = true;
}
else if (i == 0 && phoneType.Trim().ToUpper() != "МИНИАТС" &&
phoneType.Trim().ToUpper() != "ГАТС")
{
string phoneRest = string.Empty;
if (phoneNumberPair.Count > 3)
{
phoneRest += "8(";
for (int j = phoneNumberPair.Count - 3; j <= phoneNumberPair.Count - 1; j++)
{
phoneRest += phoneNumberPair[j];
}
phoneRest += ")";
}
else
{
phoneRest = "8(" +
phoneNumberPair.Aggregate(phoneRest, (current, simbol) => current + simbol) +
")";
}
parsedPhone.Insert(0, phoneRest);
phoneNumberPair.Clear();
}
else if (i == 0)
{
string phoneRest = string.Empty;
phoneRest = phoneNumberPair.Aggregate(phoneRest, (current, simbol) => current + simbol);
parsedPhone.Insert(0, phoneRest);
phoneNumberPair.Clear();
}
}
string result = string.Empty;
return(parsedPhone.Aggregate(result, (current, simbol) => current + simbol));
}
catch (Exception ex)
{
ShellLogger.WriteLog("DB.log", "Ошибка парсинга телефона:" + getString, ex);
}
return(getString);
}