public string GetString(int stringId)
{
SQLDB sqldb = new SQLDB(connectionString);
string gettedString;
sqldb.OpenConnection();
gettedString = sqldb.GetString(stringId, CurrentLanguage);
sqldb.CloseConnection();
return gettedString;
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="connectionsStringTMP">строка подключение(необязательно ее указывать)</param>
/// <param name="Current_Language">Язык</param>
/// <param name="sql">обьект SQLDB</param>
public InvoiceTable(string connectionsStringTMP, string Current_Language, SQLDB sql)
{
connectionString = connectionsStringTMP;
CurrentLanguage = Current_Language;
//тест
// sqlDBR = new SQLDB(connectionString);
sqlDBR = sql;
//
string LanguageForStatus = "STRING_RU";
sqlDBR.OpenConnection();
Status_Paid = AddOrGetInvoiceStatusId("Paid", LanguageForStatus);
Status_NotPaid = AddOrGetInvoiceStatusId("Not paid", LanguageForStatus);
InvoiceType_addReportInvoice = AddOrGetInvoiceTypeId("Additional report invoice", LanguageForStatus);
InvoiceType_moreUsers = AddOrGetInvoiceTypeId("Additional users invoice", LanguageForStatus);
InvoiceType_licenseFee = AddOrGetInvoiceTypeId("License fee", LanguageForStatus);
sqlDBR.CloseConnection();
}
/// <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();
}
protected void InvoicesTab_PayInvoiceButton_Click(object sender, EventArgs e)
{
string currentLanguage = ConfigurationManager.AppSettings["language"];
string connectionString = ConfigurationSettings.AppSettings["fleetnetbaseConnectionString"];
DataBlock dataBlock = new DataBlock(connectionString, currentLanguage);
try
{
dataBlock.OpenConnection();
dataBlock.OpenTransaction();
int curUserId = dataBlock.usersTable.Get_UserID_byName(Page.User.Identity.Name);
int invoiceId = Convert.ToInt32(Selected_InvoicesDataGrid_Index.Value);
//old version
/* dataBlock.invoiceTable.OpenConnection();
dataBlock.invoiceTable.OpenTransaction();
dataBlock.invoiceTable.PayABill(invoiceId);
string invoiceName = dataBlock.invoiceTable.GetInvoiceName(invoiceId);
dataBlock.invoiceTable.CommitTransaction();
dataBlock.invoiceTable.CloseConnection();*/
//test version
dataBlock.invoiceTable.PayABill(invoiceId);
string invoiceName = dataBlock.invoiceTable.GetInvoiceName(invoiceId);
dataBlock.CommitTransaction();
dataBlock.CloseConnection();
//
LoadInvoicesTable();
InvoicesDataGridUpdatePanel.Update();
InvoicesTab_ButtonsUpdateTable.Update();
//// добавление записи в журнал. потом возможно перенести куда надо(в логику).
DB.SQL.SQLDB sqlDb = new DB.SQL.SQLDB(connectionString);
HistoryTable history = new HistoryTable(connectionString, currentLanguage, sqlDb);
sqlDb.OpenConnection();
history.AddHistoryRecord("FN_INVOICE", "INVOICE_STATUS_ID", dataBlock.invoiceTable.Status_Paid, curUserId, history.invoicePaid, "#" + invoiceId + @" :""" + invoiceName + @"""", sqlDb);
sqlDb.CloseConnection();
////
}
catch (Exception ex)
{
dataBlock.invoiceTable.RollbackConnection();
dataBlock.invoiceTable.CloseConnection();
RaiseException(ex);
}
}
/// <summary>
/// Инициализировать базу данных
/// </summary>
/// <param name="password">пароль - ввести qqq</param>
public void InitDataBase(string password)
{
if (password == "qqq")
{
DBI sqlDB = new SQLDB(connectionString);
try
{
sqlDB.OpenConnection();
sqlDB.OpenTransaction();
sqlDB.DataBaseInit();
sqlDB.CommitConnection();
}
catch (Exception ex)
{
sqlDB.RollbackConnection();
Console.WriteLine(ex.Message);
}
finally
{
sqlDB.CloseConnection();
}
}
}
public string GetDataState()
{
SQLDB sqlDB = new SQLDB(connectionString);
int blockStateId;//в таблице fn_data_block
int STRIddataBlockState;//в таблице fd_data_block_state
string blockStateMessage;
sqlDB.OpenConnection();
blockStateId = sqlDB.GetDataBlockState(DATA_BLOCK_ID);
STRIddataBlockState = sqlDB.GetSTRIdDataBlockStateName(blockStateId);
blockStateMessage = sqlDB.GetString(STRIddataBlockState, CurrentLanguage);
sqlDB.CloseConnection();
return blockStateMessage;
}
public List<string> GetAllDriverNames(int UserId)
{
List<int> gettedIds = new List<int>();
List<string> gettedNames = new List<string>();
SQLDB sqlDB = new SQLDB(connectionString);
SQLDB_Records sqlDB_Records = new SQLDB_Records(connectionString);
sqlDB.OpenConnection();
gettedIds = sqlDB.GetAllDataBlocksId_byCardType(UserId, 0);
sqlDB.CloseConnection();
gettedNames = sqlDB_Records.Get_DriverNames_ByDataBlockIdList(gettedIds);
return gettedNames;
}
public List<int> GetAllDataBlockId_byCardType(int cardType)
{
List<int> gettedIds = new List<int>();
SQLDB sqlDB = new SQLDB(connectionString);
int UserId = 0;
sqlDB.OpenConnection();
gettedIds = sqlDB.GetAllDataBlocksId_byCardType(UserId, cardType);
sqlDB.CloseConnection();
return gettedIds;
}
public int DeleteDataRecords()
{
SQLDB sqlDB = new SQLDB(connectionString);
int deletedCount;
sqlDB.OpenConnection();
sqlDB.OpenTransaction();
deletedCount = dataRecord.DeleteAllData(sqlDB);
sqlDB.SetDataBlockState(DATA_BLOCK_ID, 3);
sqlDB.CommitConnection();
sqlDB.CloseConnection();
return deletedCount;
}
public int DeleteDataBlockAndRecords()
{
SQLDB sqlDB = new SQLDB(connectionString);
int deletedCount = 3;
try
{
sqlDB.OpenConnection();
sqlDB.OpenTransaction();
// deletedCount = dataRecord.DeleteAllData(sqlDB);
sqlDB.DeleteAllDataRecordsFast(DATA_BLOCK_ID);
sqlDB.DeleteDataBlock(DATA_BLOCK_ID);
sqlDB.CommitConnection();
sqlDB.CloseConnection();
}
catch (Exception ex)
{
sqlDB.RollbackConnection();
sqlDB.CloseConnection();
throw ex;
}
return deletedCount + 1;
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="connectionsStringTMP">Строка подключения. МОжет быть любой</param>
/// <param name="Current_Language">Язык</param>
/// <param name="sql">обьект SQLDB</param>
public HistoryTable(string connectionsStringTMP, string Current_Language, SQLDB sql)
{
bool toCloseConncection = false;
connectionString = connectionsStringTMP;
CurrentLanguage = Current_Language;
//sqlDb = new SQLDB(connectionString);
sqlDb = sql;
string LanguageForAction = "STRING_RU";
if (!sqlDb.IsConnectionOpened())
{
sqlDb.OpenConnection();
toCloseConncection = true;
}
driversRegDataChanged = AddOrGetAction("Users/drivers registration data changed", LanguageForAction);
vehiclesRegDataChanged = AddOrGetAction("Vehicles registration data changed", LanguageForAction);
PLFDataBlockLoaded = AddOrGetAction("PLF data block loaded", LanguageForAction);
DDDDriversDataBlockLoaded = AddOrGetAction("Cards data block loaded", LanguageForAction);
DDDVehiclesDataBlockLoaded = AddOrGetAction("Vehicles unit data block loaded", LanguageForAction);
newUserRegistered = AddOrGetAction("New user registered", LanguageForAction);
newDriverRegistered = AddOrGetAction("New driver registered", LanguageForAction);
newVehicleRegistered = AddOrGetAction("New vehicle registered", LanguageForAction);
setReportUserRoles = AddOrGetAction("Change permissions to the report for user role", LanguageForAction);
setReportUserOrg = AddOrGetAction("New report type successfully added", LanguageForAction);
invoicePaid = AddOrGetAction("Invoice paid", LanguageForAction);
if(toCloseConncection)
sqlDb.CloseConnection();
}