//Работа с уже занесенной записью
/// <summary>
/// Конструктор для работы с уже занессеной записью(использовать например для удаления DataBlock
/// </summary>
/// <param name="connectionStringTMP">строка подключения</param>
/// <param name="dataBlockId">dataBlockId</param>
/// <param name="Current_Language">Язык</param>
public DataBlock(string connectionStringTMP, int dataBlockId, string Current_Language)
{
//тестовое подключение
sqlDb = new SQLDB(connectionStringTMP);
///////
SQLDB sqlDB = new SQLDB(connectionStringTMP);
int dataIdTemp = sqlDB.checkTableExistence("fn_data_block", "DATA_BLOCK_ID", dataBlockId);
if (dataIdTemp == 0)
throw (new Exception("There is no Data Block with id " + dataBlockId.ToString()));
connectionString = connectionStringTMP;
DATA_BLOCK_ID = dataBlockId;
DATA_BLOCK_ID_PREVIOUS = -1;
CurrentLanguage = Current_Language;
dataRecord = new DataRecords(connectionStringTMP, dataBlockId, Current_Language, sqlDb);
vehicleUnitInfo = new VehicleUnitInfo(connectionString, Current_Language, sqlDb);
cardUnitInfo = new CardUnitInfo(connectionString, Current_Language, sqlDb);
plfUnitInfo = new PLFUnitInfo(connectionString, Current_Language, sqlDb);
cardsTable = new CardsTable(connectionString, Current_Language, sqlDb);
organizationTable = new OrganizationTable(connectionString, Current_Language, sqlDb);
usersTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
stringTable = new StringTable(connectionString, CurrentLanguage);
vehiclesTables = new VehiclesTable(connectionString, CurrentLanguage, sqlDb);
criteriaTable = new CriteriaTable(connectionString, CurrentLanguage, sqlDb);
deviceTable = new DeviceTable(connectionString, CurrentLanguage, sqlDb);
//historyTable = new HistoryTable(connectionString, CurrentLanguage, sqlDb);
reportsTable = new ReportsTable(connectionString, CurrentLanguage, sqlDb);
invoiceTable = new InvoiceTable(connectionString, CurrentLanguage, sqlDb);
emailScheduleTable = new EmailScheduleTable(connectionString, CurrentLanguage, sqlDb);
remindTable = new RemindTable(connectionString, CurrentLanguage, sqlDb);
bannersTable = new BannersTable(connectionString, CurrentLanguage, sqlDb);
currentCardType = -1;
organizationID = 0;
}
/// <summary>
/// Заполнить поля информацией
/// </summary>
/// <param name="UserId">ID пользователя</param>
/// <param name="tables">обьект класса UsersTables с открытым подключением</param>
/// <returns>this</returns>
public UserFromTable FillWithInfo(int UserId, UsersTables tables)
{
name = tables.Get_UserName(UserId);
pass = tables.Get_UserPassword(UserId);
userType = tables.Get_UserTypeStr(UserId);
userRole = tables.Get_UserRoleName(UserId);
timeConnection = tables.Get_TimeConnect(UserId);
orgName = tables.Get_UserOrgName(UserId);
id = UserId;
return(this);
}
public void AddOrSetReportUserRoles_SETDATE(int reportsUserId, int orgId, int userRoleId, bool IsActive, int curUserId)
{
sqlDBR.AddOrSetReportUserRoles_SETDATE(reportsUserId, orgId, userRoleId, IsActive);
if (curUserId > 0) //Пока убрал, потому что слишком много обновлений за одну секунду(
{
string isItActive = "active";
if (!IsActive)
{
isItActive = "not active";
}
HistoryTable log = new HistoryTable(connectionString, CurrentLanguage, sqlDBR);
string reportName = GetReportName(reportsUserId);
//userrolename
UsersTables usrt = new UsersTables(connectionString, CurrentLanguage, sqlDBR);
string userRoleName = usrt.GetUserRoleName(userRoleId);
HistoryWriter.Instance.AddHistoryRecord("fn_report_user_roles", "REPORT_USER_ID", reportsUserId, curUserId, log.setReportUserRoles,
@"Report """ + reportName + @"""" + " for user role " + userRoleName + " is " + isItActive + " now", sqlDBR);
}
}
/// <summary>
/// Иннициализирует базу данных стартовыми значениями.
/// Это нужно для того, чтобы сразу с разворачивания базы данных можно было начать работать в веб интерфейсе.
/// </summary>
/// <param name="password">пароль нужен просто, чтобы из консоли нечайно не тыкнуть. пароль = qqq</param>
public static void DataBaseInit(string password)
{
string CurrentLanguage = "STRING_RU";
if (password != "qqq")
throw new Exception("Неправильный пароль");
string connectionString = "server=localhost;User Id=root;password = 1;Persist Security Info=True;database=fleetnetbase";
string connectionString1 = "server=mysql62.1gb.ru;default command timeout=600;Connection Timeout=600;database=gb_x_smartfis;User Id=gb_x_smartfis;password =5216a0af;";
SQLDB sqlDb = new SQLDB(connectionString);
MySqlConnection sqlConnection = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
int stringId = -1;
int generatedId = -1;
string sql;
sql = "INSERT INTO fd_string "
+ "(STRING_ID, STRING_RU)"
+ "VALUES (@STRING_ID, @STRING_RU)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@STRING_ID", 0);
cmd.Parameters.AddWithValue("@STRING_RU", "");
sqlConnection.Open();
sqlDb.OpenConnection();
cmd.ExecuteNonQuery();
//dataBlockStates
InitTable_ID_String("fd_data_block_state", "DATA_BLOCK_STATE_ID", "STRID_DATA_BLOCK_STATE_NAME", "Ideal", sqlDb, sqlConnection);
InitTable_ID_String("fd_data_block_state", "DATA_BLOCK_STATE_ID", "STRID_DATA_BLOCK_STATE_NAME", "Parsed", sqlDb, sqlConnection);
InitTable_ID_String("fd_data_block_state", "DATA_BLOCK_STATE_ID", "STRID_DATA_BLOCK_STATE_NAME", "Not parsed", sqlDb, sqlConnection);
InitTable_ID_String("fd_data_block_state", "DATA_BLOCK_STATE_ID", "STRID_DATA_BLOCK_STATE_NAME", "Not supported", sqlDb, sqlConnection);
//dataRecordsState
InitTable_ID_String("fd_data_record_state", "DATA_RECORD_STATE_ID", "STRID_DATA_RECORD_STATE_NAME", "Ideal", sqlDb, sqlConnection);
InitTable_ID_String("fd_data_record_state", "DATA_RECORD_STATE_ID", "STRID_DATA_RECORD_STATE_NAME", "Parsed", sqlDb, sqlConnection);
InitTable_ID_String("fd_data_record_state", "DATA_RECORD_STATE_ID", "STRID_DATA_RECORD_STATE_NAME", "Not parsed", sqlDb, sqlConnection);
//Card_TYPE
stringId = sqlDb.AddOrGetString("Card Type: Driver", SQLDB.userString);
generatedId = sqlDb.generateId("fd_card_type", "CARD_TYPE_ID");
if (generatedId == -1)
throw (new Exception("Can't generate CARD_TYPE_ID"));
sql = "INSERT INTO fd_card_type "
+ "(CARD_TYPE_ID, STRID_CARD_TYPE_NAME, STRID_CARD_TYPE_SHORT_NAME, STRID_CARD_TYPE_PRINT_NAME)"
+ "VALUES (@CARD_TYPE_ID, @STRID_CARD_TYPE_NAME, @STRID_CARD_TYPE_SHORT_NAME, @STRID_CARD_TYPE_PRINT_NAME)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@CARD_TYPE_ID", generatedId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_SHORT_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_PRINT_NAME", stringId);
cmd.ExecuteNonQuery();
stringId = sqlDb.AddOrGetString("Card Type: Vehicle", SQLDB.userString);
generatedId = sqlDb.generateId("fd_card_type", "CARD_TYPE_ID");
if (generatedId == -1)
throw (new Exception("Can't generate CARD_TYPE_ID"));
sql = "INSERT INTO fd_card_type "
+ "(CARD_TYPE_ID, STRID_CARD_TYPE_NAME, STRID_CARD_TYPE_SHORT_NAME, STRID_CARD_TYPE_PRINT_NAME)"
+ "VALUES (@CARD_TYPE_ID, @STRID_CARD_TYPE_NAME, @STRID_CARD_TYPE_SHORT_NAME, @STRID_CARD_TYPE_PRINT_NAME)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@CARD_TYPE_ID", generatedId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_SHORT_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_PRINT_NAME", stringId);
cmd.ExecuteNonQuery();
stringId = sqlDb.AddOrGetString("Card Type: Organization Init Card", SQLDB.userString);
generatedId = sqlDb.generateId("fd_card_type", "CARD_TYPE_ID");
if (generatedId == -1)
throw (new Exception("Can't generate CARD_TYPE_ID"));
sql = "INSERT INTO fd_card_type "
+ "(CARD_TYPE_ID, STRID_CARD_TYPE_NAME, STRID_CARD_TYPE_SHORT_NAME, STRID_CARD_TYPE_PRINT_NAME)"
+ "VALUES (@CARD_TYPE_ID, @STRID_CARD_TYPE_NAME, @STRID_CARD_TYPE_SHORT_NAME, @STRID_CARD_TYPE_PRINT_NAME)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@CARD_TYPE_ID", generatedId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_SHORT_NAME", stringId);
cmd.Parameters.AddWithValue("@STRID_CARD_TYPE_PRINT_NAME", stringId);
cmd.ExecuteNonQuery();
int orgInitCardTypeId = generatedId;
//fd_deviceFirmware
int deviceFirmWareId = sqlDb.AddNewDeviceFirmware("onboard device by default", DateTime.Now, "0.0.0.0", new byte[1]);
//fd_device
int deviceTypeId = sqlDb.AddNewDeviceType("DefaultDeviceType");
sqlDb.AddNewDevice(deviceTypeId, "The onboard device by default", "00000", DateTime.Now, deviceFirmWareId, 222222);
//InitTable_ID_String("fd_device", "DEVICE_ID", "STRID_DEVICE_NAME", "");
//fd_object
InitTable_ID_String("fd_object", "OBJECT_ID", "STRID_OBJECT_NAME", "Object by default", sqlDb, sqlConnection);
//FD_ORG_TYPE
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Cargo transportation", sqlDb, sqlConnection);
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Passengers transportation", sqlDb, sqlConnection);
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Anything else transportation", sqlDb, sqlConnection);
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Dealer", sqlDb, sqlConnection);
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Subdealer", sqlDb, sqlConnection);
InitTable_ID_String("fd_org_type", "ORG_TYPE_ID", "STRID_ORG_TYPE_NAME", "Predealer", sqlDb, sqlConnection);
//FD_ORG
OrganizationTable orgTable = new OrganizationTable(connectionString, CurrentLanguage, sqlDb);
orgTable.AddNewOrganization("Init Organization", 1, 1, 1, 6);
//fd_param
sql = "INSERT INTO fd_param "
+ "(PARAM_ID, PARENT_PARAM_ID, PARAM_NAME, PARAM_SIZE)"
+ "VALUES (@PARAM_ID, @PARENT_PARAM_ID, @PARAM_NAME, @PARAM_SIZE)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@PARAM_ID", 0);
cmd.Parameters.AddWithValue("@PARENT_PARAM_ID", 0);
cmd.Parameters.AddWithValue("@PARAM_NAME", "Parent_param");
cmd.Parameters.AddWithValue("@PARAM_SIZE", 0);
cmd.ExecuteNonQuery();
//fd_user_info_set and fd_user_info
sqlDb.AddUserInfoName("Surname", "STRING_RU");
sqlDb.AddUserInfoName("Name", "STRING_RU");
sqlDb.AddUserInfoName("Patronimic", "STRING_RU");
sqlDb.AddUserInfoName("Drivers certificate", "STRING_RU");
sqlDb.AddUserInfoName("Card number", "STRING_RU");
sqlDb.AddUserInfoName("Phone number", "STRING_RU");
sqlDb.AddUserInfoName("Birthday", "STRING_RU");
//fd_user_rights
InitTable_ID_String("fd_user_rights", "USER_RIGHTS_ID", "STRID_USER_RIGHTS_NAME", "Administrator", sqlDb, sqlConnection);
InitTable_ID_String("fd_user_rights", "USER_RIGHTS_ID", "STRID_USER_RIGHTS_NAME", "SuperAdministrator", sqlDb, sqlConnection);
//fd_user_role
InitTable_ID_String("fd_user_role", "USER_ROLE_ID", "STRID_USER_ROLE_NAME", "Administrator", sqlDb, sqlConnection);
InitTable_ID_String("fd_user_role", "USER_ROLE_ID", "STRID_USER_ROLE_NAME", "SuperAdministrator", sqlDb, sqlConnection);
//fd_user_type
InitTable_ID_String("fd_user_type", "USER_TYPE_ID", "STRID_USER_TYPE_NAME", "Driver", sqlDb, sqlConnection);
InitTable_ID_String("fd_user_type", "USER_TYPE_ID", "STRID_USER_TYPE_NAME", "Manager", sqlDb, sqlConnection);
InitTable_ID_String("fd_user_type", "USER_TYPE_ID", "STRID_USER_TYPE_NAME", "Administrator", sqlDb, sqlConnection);
InitTable_ID_String("fd_user_type", "USER_TYPE_ID", "STRID_USER_TYPE_NAME", "DealerUser", sqlDb, sqlConnection);
//fd_user
UsersTables usersTable = new UsersTables(connectionString, "STRING_RU", sqlDb);
usersTable.OpenConnection();
usersTable.OpenTransaction();
UserFromTable userFromTable = new UserFromTable("admin", "123", "3", "1", DateTime.Now, "org");
usersTable.AddNewUser(userFromTable, 3, 1, 1, 0);
userFromTable = new UserFromTable("admin2", "123", "3", "1", DateTime.Now, "org");
usersTable.AddNewUser(userFromTable, 3, 2, 1, 0);
usersTable.CommitTransaction();
usersTable.CloseConnection();
//fd_measure
int kgId = sqlDb.AddNewMeasure("Kg", "Kilograms");
int kmId = sqlDb.AddNewMeasure("Km", "Kilometers");
int m3Id = sqlDb.AddNewMeasure("M3", "Cubic metres");
int dateId = sqlDb.AddNewMeasure("Date", "DateTime");
int RPMId = sqlDb.AddNewMeasure("Rpm", "Revolutions per minute");
int KmphId = sqlDb.AddNewMeasure("Km/h", "Kilometers per hour");
int PercentsId = sqlDb.AddNewMeasure("%", "Percents");
int FConsumption = sqlDb.AddNewMeasure("L/h", "Liters per hour");
//fd_key
sqlDb.AddNewCriteria(kgId, "Commentary to a vehicle", "Строка для Комментария к ТС", 0, 0);
sqlDb.AddNewCriteria(kgId, "Load-carrying capacity", "Грузоподьемность", 5000, 25000);
sqlDb.AddNewCriteria(m3Id, "Fuel tank 1", "Коментарий к Топливному баку 1", 1, 250);
sqlDb.AddNewCriteria(m3Id, "Fuel tank 2", "Коментарий к Топливному баку 1", 1, 250);
sqlDb.AddNewCriteria(dateId, "MRO 1", "Дата ТО 1", 1, 1);
sqlDb.AddNewCriteria(dateId, "MRO 2", "Дата ТО 2", 1, 1);
sqlDb.AddNewCriteria(RPMId, "Nominal turns", "критерий для номинальных оборотов", 1, 1);
sqlDb.AddNewCriteria(KmphId, "Maximum speed", "критерий для максимальной скорости", 1, 1);
sqlDb.AddNewCriteria(KmphId, "Manoeuvring", "критерий для маневрирования", 1, 1);
sqlDb.AddNewCriteria(PercentsId, "City", "критерий для города", 1, 1);
sqlDb.AddNewCriteria(PercentsId, "Highway", " критерий для магистрали", 1, 1);
sqlDb.AddNewCriteria(FConsumption, "Nominal fuel consumption", "критерий для номинального расхода топлива", 1, 1);
sqlDb.AddNewCriteria(RPMId, "Cold start", "критерий для холодного старта", 1, 1);
sqlDb.AddNewCriteria(RPMId, "Hot stop", "критерий для горячего стопа", 1, 1);
//fd_fuel_type
stringId = sqlDb.AddOrGetString("Unknown fuel type", SQLDB.userString);
sql = "INSERT INTO fd_fuel_type "
+ "(FUEL_TYPE_ID, STRID_FUEL_TYPE_NAME)"
+ "VALUES (@FUEL_TYPE_ID, @STRID_FUEL_TYPE_NAME)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@FUEL_TYPE_ID", 0);
cmd.Parameters.AddWithValue("@STRID_FUEL_TYPE_NAME", stringId);
cmd.ExecuteNonQuery();
stringId = sqlDb.AddOrGetString("Undefined", SQLDB.userString);
sql = "INSERT INTO fd_vehicle_type "
+ "(VEHICLE_TYPE_ID, STRID_VEHICLE_TYPE_NAME, FUEL_TYPE_ID)"
+ "VALUES (@VEHICLE_TYPE_ID, @STRID_VEHICLE_TYPE_NAME, @FUEL_TYPE_ID)";
cmd = new MySqlCommand(sql, sqlConnection);
cmd.Parameters.AddWithValue("@VEHICLE_TYPE_ID", 0);
cmd.Parameters.AddWithValue("@STRID_VEHICLE_TYPE_NAME", stringId);
cmd.Parameters.AddWithValue("@FUEL_TYPE_ID", 0);
cmd.ExecuteNonQuery();
int fuelTypeID = sqlDb.AddNewFuelType("FuelType 1");
sqlDb.AddNewVehicleType("Veh type 1", fuelTypeID);
//HISTORY
/* HistoryTable history = new HistoryTable(connectionString, CurrentLanguage);
history.AddOrGetAction("Изменение учетных данных");*/
//
sqlConnection.Close();
sqlDb.CloseConnection();
}
/// <summary>
/// Получает всю исторю для группы выбранных пользователей с выполнением некоторых условий
/// </summary>
/// <param name="usersIds">Массив ID пользователей</param>
/// <param name="from">Дата с</param>
/// <param name="to">Дата по</param>
/// <param name="actionIdIns">ID типа действия</param>
/// <param name="searchString">Строка, которая должна встречаться в логе</param>
/// <returns>DataTable, который можно напрямую использовать как источник данных.</returns>
public DataTable GetAllHistorysForUsers(List <int> usersIds, DateTime from, DateTime to, int actionIdIns, string searchString)
{
Exception noValues = new Exception("Нет значений для Журнала");
DataTable UsersHistoryData = new DataTable("UsersHistoryData");
string userName;
UsersTables userTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
DataRow dr;
List <List <KeyValuePair <string, string> > > gettedIdList = new List <List <KeyValuePair <string, string> > >();
List <string> gettedDateTimes = new List <string>();
UsersHistoryData.Columns.Add(new DataColumn("USER_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("ACTION_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("TABLE_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("Дата и время", typeof(DateTime)));
UsersHistoryData.Columns.Add(new DataColumn("Пользователь", typeof(string)));
UsersHistoryData.Columns.Add(new DataColumn("Описание", typeof(string)));
int tableId = -1;
int actionId = -1;
string description;
DateTime date = new DateTime();
foreach (int userId in usersIds)
{
//userTable.OpenConnection();
userName = userTable.Get_UserName(userId);
//userTable.CloseConnection();
gettedIdList = sqlDb.GetHistoryActionIdAndTableId(userId, from, to, actionIdIns);
tableId = -1;
actionId = -1;
foreach (List <KeyValuePair <string, string> > record in gettedIdList)
{
foreach (KeyValuePair <string, string> oneValue in record)
{
if (oneValue.Key == "ACTION_ID")
{
actionId = Convert.ToInt32(oneValue.Value);
}
if (oneValue.Key == "TABLE_ID")
{
tableId = Convert.ToInt32(oneValue.Value);
}
if (oneValue.Key == "ACTION_DATE")
{
date = DateTime.Parse(oneValue.Value);
}
}
if (actionId <= 0 || tableId <= 0)
{
throw noValues;
}
dr = UsersHistoryData.NewRow();
dr["Дата и время"] = date;//дата
dr["Пользователь"] = userName;
description = GetActionString(actionId) + ". " + GetHistoryNote(userId, actionId, tableId, date) + ".";
if (searchString != "")//очень плохой способ. Если будет тормозить - переделать на выборку из базы или еще чего...
{
//if (description.Contains(searchString))
if (description.ToLower().Contains(searchString.ToLower()))
{
//description = Regex.Replace(description, searchString, "&lbb&rb&lbFONT COLOR=RED&rb" + searchString + "&lb&slFONT&rb&lb&slb&rb", RegexOptions.IgnoreCase);
description = description.Replace(searchString, "&lbb&rb&lbFONT COLOR=RED&rb" + searchString + "&lb&slFONT&rb&lb&slb&rb");
description = description.Replace(searchString.ToLower(), "&lbb&rb&lbFONT COLOR=RED&rb" + searchString.ToLower() + "&lb&slFONT&rb&lb&slb&rb");
String s = searchString.Substring(0, 1).ToUpper() + searchString.Substring(1);
description = description.Replace(s, "&lbb&rb&lbFONT COLOR=RED&rb" + s + "&lb&slFONT&rb&lb&slb&rb");
}
else
{
continue;
}
}
dr["Описание"] = description;
dr["USER_ID"] = userId;
dr["ACTION_ID"] = actionId;
dr["TABLE_ID"] = tableId;
UsersHistoryData.Rows.Add(dr);
}
}
UsersHistoryData.DefaultView.Sort = "Дата и время DESC";
return(UsersHistoryData);
}
/// <summary>
/// Заполнить поля информацией
/// </summary>
/// <param name="UserId">ID пользователя</param>
/// <param name="tables">обьект класса UsersTables с открытым подключением</param>
/// <returns>this</returns>
public UserFromTable FillWithInfo(int UserId, UsersTables tables)
{
name = tables.Get_UserName(UserId);
pass = tables.Get_UserPassword(UserId);
userType = tables.Get_UserTypeStr(UserId);
userRole = tables.Get_UserRoleName(UserId);
timeConnection = tables.Get_TimeConnect(UserId);
orgName = tables.Get_UserOrgName(UserId);
id = UserId;
return this;
}
/// <summary>
/// Конструктор(рекомендовано пользоваться этим)
/// </summary>
/// <param name="connectionStringTMP">Строка подключения</param>
/// <param name="Current_Language">Язык</param>
public DataBlock(string connectionStringTMP, string Current_Language)
{
//тестовое подключение
sqlDb = new SQLDB(connectionStringTMP);
///////
connectionString = connectionStringTMP;
DATA_BLOCK_ID = -1;
DATA_BLOCK_ID_PREVIOUS = -1;
CurrentLanguage = Current_Language;
vehicleUnitInfo = new VehicleUnitInfo(connectionString, Current_Language, sqlDb);
cardUnitInfo = new CardUnitInfo(connectionString, Current_Language, sqlDb);
plfUnitInfo = new PLFUnitInfo(connectionString, Current_Language, sqlDb);
cardsTable = new CardsTable(connectionString, Current_Language, sqlDb);
organizationTable = new OrganizationTable(connectionString, Current_Language, sqlDb);
usersTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
stringTable = new StringTable(connectionString, CurrentLanguage);
vehiclesTables = new VehiclesTable(connectionString, CurrentLanguage, sqlDb);
criteriaTable = new CriteriaTable(connectionString, CurrentLanguage, sqlDb);
deviceTable = new DeviceTable(connectionString, CurrentLanguage, sqlDb);
//historyTable = new HistoryTable(connectionString, CurrentLanguage, sqlDb);
reportsTable = new ReportsTable(connectionString, CurrentLanguage, sqlDb);
invoiceTable = new InvoiceTable(connectionString, CurrentLanguage, sqlDb);
emailScheduleTable = new EmailScheduleTable(connectionString, CurrentLanguage, sqlDb);
remindTable = new RemindTable(connectionString, CurrentLanguage, sqlDb);
bannersTable = new BannersTable(connectionString, CurrentLanguage, sqlDb);
currentCardType = -1;
organizationID = 0;
}
/// <summary>
/// Получает всю исторю для группы выбранных пользователей с выполнением некоторых условий
/// </summary>
/// <param name="usersIds">Массив ID пользователей</param>
/// <param name="from">Дата с</param>
/// <param name="to">Дата по</param>
/// <param name="actionIdIns">ID типа действия</param>
/// <param name="searchString">Строка, которая должна встречаться в логе</param>
/// <returns>DataTable, который можно напрямую использовать как источник данных.</returns>
public DataTable GetAllHistorysForUsers(List<int> usersIds, DateTime from, DateTime to, int actionIdIns, string searchString)
{
Exception noValues = new Exception("Нет значений для Журнала");
DataTable UsersHistoryData = new DataTable("UsersHistoryData");
string userName;
UsersTables userTable = new UsersTables(connectionString, CurrentLanguage, sqlDb);
DataRow dr;
List<List<KeyValuePair<string, string>>> gettedIdList = new List<List<KeyValuePair<string, string>>>();
List<string> gettedDateTimes = new List<string>();
UsersHistoryData.Columns.Add(new DataColumn("USER_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("ACTION_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("TABLE_ID", typeof(int)));
UsersHistoryData.Columns.Add(new DataColumn("Дата и время", typeof(DateTime)));
UsersHistoryData.Columns.Add(new DataColumn("Пользователь", typeof(string)));
UsersHistoryData.Columns.Add(new DataColumn("Описание", typeof(string)));
int tableId = -1;
int actionId = -1;
string description;
DateTime date = new DateTime();
foreach (int userId in usersIds)
{
//userTable.OpenConnection();
userName = userTable.Get_UserName(userId);
//userTable.CloseConnection();
gettedIdList = sqlDb.GetHistoryActionIdAndTableId(userId, from, to, actionIdIns);
tableId = -1;
actionId = -1;
foreach (List<KeyValuePair<string, string>> record in gettedIdList)
{
foreach (KeyValuePair<string, string> oneValue in record)
{
if (oneValue.Key == "ACTION_ID")
actionId = Convert.ToInt32(oneValue.Value);
if (oneValue.Key == "TABLE_ID")
tableId = Convert.ToInt32(oneValue.Value);
if (oneValue.Key == "ACTION_DATE")
date = DateTime.Parse(oneValue.Value);
}
if (actionId <= 0 || tableId <= 0)
throw noValues;
dr = UsersHistoryData.NewRow();
dr["Дата и время"] = date;//дата
dr["Пользователь"] = userName;
description = GetActionString(actionId) + ". " + GetHistoryNote(userId, actionId, tableId, date) + ".";
if (searchString != "")//очень плохой способ. Если будет тормозить - переделать на выборку из базы или еще чего...
{
//if (description.Contains(searchString))
if (description.ToLower().Contains(searchString.ToLower()))
{
//description = Regex.Replace(description, searchString, "&lbb&rb&lbFONT COLOR=RED&rb" + searchString + "&lb&slFONT&rb&lb&slb&rb", RegexOptions.IgnoreCase);
description = description.Replace(searchString, "&lbb&rb&lbFONT COLOR=RED&rb" + searchString + "&lb&slFONT&rb&lb&slb&rb");
description = description.Replace(searchString.ToLower(), "&lbb&rb&lbFONT COLOR=RED&rb" + searchString.ToLower() + "&lb&slFONT&rb&lb&slb&rb");
String s=searchString.Substring(0,1).ToUpper()+searchString.Substring(1);
description = description.Replace(s, "&lbb&rb&lbFONT COLOR=RED&rb" + s + "&lb&slFONT&rb&lb&slb&rb");
}
else
continue;
}
dr["Описание"] = description;
dr["USER_ID"] = userId;
dr["ACTION_ID"] = actionId;
dr["TABLE_ID"] = tableId;
UsersHistoryData.Rows.Add(dr);
}
}
UsersHistoryData.DefaultView.Sort = "Дата и время DESC";
return UsersHistoryData;
}
public void AddOrSetReportUserRoles_SETDATE(int reportsUserId, int orgId, int userRoleId, bool IsActive, int curUserId)
{
sqlDBR.AddOrSetReportUserRoles_SETDATE(reportsUserId, orgId, userRoleId, IsActive);
if (curUserId > 0) //Пока убрал, потому что слишком много обновлений за одну секунду(
{
string isItActive = "active";
if (!IsActive)
isItActive = "not active";
HistoryTable log = new HistoryTable(connectionString, CurrentLanguage, sqlDBR);
string reportName = GetReportName(reportsUserId);
//userrolename
UsersTables usrt = new UsersTables(connectionString, CurrentLanguage, sqlDBR);
string userRoleName = usrt.GetUserRoleName(userRoleId);
HistoryWriter.Instance.AddHistoryRecord("fn_report_user_roles", "REPORT_USER_ID", reportsUserId, curUserId, log.setReportUserRoles,
@"Report """ + reportName + @"""" + " for user role " + userRoleName + " is " + isItActive + " now", sqlDBR);
}
}