/// <summary>
/// this function will retrieve the settings for a given user
/// </summary>
/// <param name="user">the given user</param>
/// <returns>a complete list of settings</returns>
public static List <Setting> RetrieveSettingsListForUser(User user)
{
//the query command for the retrieval of the settings
String QueryCommand = "SELECT s.id, s.setare, s.tip_date_setare, su.valoare_setare " +
"FROM settings.setari_utilizatori AS su " +
"LEFT JOIN settings.setari AS s " +
"ON s.id = su.setare_id " +
"WHERE su.utilizator_id = :p_user_id";
//the query standalone parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter(":p_user_id", user.ID);
//if we are unable to connect to the server we abandon execution
if (!PgSqlConnection.OpenConnection())
{
return(new List <Setting>());
}
//else we call the execution of the procedure
DataTable dt = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//and return the settings list
return(dt.AsEnumerable().Select(row => new Setting
{
ID = row.Field <Int64>("ID"),
DataTypes = (Settings.SettingTypes.SettingDataTypes)row.Field <Int32>("TIP_DATE_SETARE"),
SettingDisplay = row.Field <String>("SETARE"),
Value = row.Field <Object>("VALOARE_SETARE")
}).ToList());
}
/// <summary>
/// this function will retrieve the complete list of countries from the database glossary
/// </summary>
/// <returns>the country list</returns>
public static List <Country> GetCountries()
{
String QueryCommand = "SELECT * FROM glossary.tari WHERE activ ";
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand);
if (result != null && result.Rows.Count > 0)
{
return(result.AsEnumerable().Select(row => new Country()
{
ID = row.Field <Int64>("ID"),
CountryCodeISO2 = row.Field <String>("COD_TARA_ISO2"),
CountryCodeISO3 = row.Field <String>("COD_TARA_ISO3"),
CountryCodeM49 = row.Field <String>("COD_TARA_ISO_M49"),
EnglishName = row.Field <String>("DEN_TARA_EN"),
RomanianName = row.Field <String>("DEN_TARA_RO")
}).ToList());
}
else
{
return(null);
}
}
/// <summary>
/// this function will update the local menu with the settings from the database server for the specific user
/// </summary>
/// <param name="user">the specific user</param>
/// <param name="menu">the instance menu</param>
public static void UpdateLocalUserMenu(User user, Menu menu)
{
//we write the sqlQuery
String QueryCommand = "SELECT * FROM settings.meniu_utilizator WHERE utilizator_id = :p_user_id";
//we instantiate the query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_user_id", user.ID);
//if we are unable to connect to the server we abandon execution
if (!PgSqlConnection.OpenConnection())
{
return;
}
//we then retrieve the dataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//let us not be morons and close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//we check if the result has values
if (result != null && result.Rows.Count > 0)
{
//we iterate the menu items
foreach (MenuItem menuItem in menu.UserMenu)
{
//and set their active values
menuItem.IsActive = result.AsEnumerable()
.Where(r => r.Field <Int32>("ID") == menuItem.MenuItemID)
.Select(r => r.Field <Boolean>("ACTIV"))
.FirstOrDefault();
}
}
}
/// <summary>
/// this function will retrieve the complete list of viable bank accounts for a given seller
/// </summary>
/// <param name="seller">the given seller</param>
/// <returns>the valid bank account list</returns>
public static List <BankAccount> GetBankAccountsForSeller(ObjectStructures.Invoice.Seller seller)
{
//the select query
String QueryCommand = "SELECT * " +
"FROM seller.conturi_bancare_furnizori " +
"WHERE furnizor_id = :p_seller_id and activ";
//the query parameters
NpgsqlParameter QueryParameter = new NpgsqlParameter(":p_seller_id", seller.ID);
//if the connection fails to open we return a null list
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//we retrieve the query result into a DataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//we close the connection before leaving the method
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//then check if the result contains any elements
if (result != null && result.Rows.Count > 0)
{
//if there are valid elements we cast the dataTable to the structure
return(result.AsEnumerable().Select(row => new BankAccount
{
ID = row.Field <Int32>("ID"),
Bank = row.Field <String>("BANCA"),
Account = row.Field <String>("CONT")
}).ToList());
}
//else we return null
else
{
return(null);
}
}
/// <summary>
/// this function will retrieve a given users active subscription
/// </summary>
/// <param name="user">the given user</param>
/// <returns>the active subscription</returns>
public static Subscription GetSubscriptionForUser(User user)
{
//the select command that will retrieve the data from the server
String queryCommand = "SELECT au.id AS id, a.denumire AS name, au.valoare_lunara AS monthly_fee, " +
"a.id as subscription_type, a.explicatii AS explanations, " +
"au.ultima_plata AS last_payment, au.perioada_activa AS active_period " +
"FROM users.abonamente_utilizatori AS au " +
"LEFT JOIN users.abonamente AS a " +
"ON au.abonament_id = a.id " +
"WHERE au.utilizator_id = :p_user_id AND au.activ";
//the command parameters
NpgsqlParameter queryParameters = new NpgsqlParameter(":p_user_id", user.ID);
//if we fail to open the connection we return a null object
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//else we execute the reader on the table
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameters);
//and close the connection once done
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//afterwards we return the newly created subscription
if (result != null && result.Rows.Count > 0)
{
return new Subscription
{
ID = (Int64)result.Rows[0]["ID"],
Name = result.Rows[0]["NAME"].ToString(),
MonthlyFee = (Double)result.Rows[0]["MONTHLY_FEE"],
Explanations = result.Rows[0]["EXPLANATIONS"].ToString(),
ActivePeriod = (Int32)result.Rows[0]["ACTIVE_PERIOD"],
LastPayment = (DateTime)result.Rows[0]["LAST_PAYMENT"],
SubscriptionType = (Int64)result.Rows[0]["SUBSCRIPTION_TYPE"]
}
}
;
else
{
return(null);
}
}
/// <summary>
/// this function will retrieve the group to which the user is part of
/// </summary>
/// <param name="user">the given user</param>
/// <returns>the Group</returns>
public static Group GetUserGroup(User user)
{
String queryCommand = "SELECT g.id AS id, g.denumire AS name, u.id AS admin_id," +
"u.nume_utilizator AS admin_username, u.nume AS admin_surname, " +
"u.prenume AS admin_name " +
"FROM users.grupuri_utilizatori AS gu " +
"LEFT JOIN users.grupuri AS g ON gu.grup_id = g.id " +
"LEFT JOIN users.utilizatori AS u ON u.id = g.administrator_grup " +
"WHERE gu.utilizator_id = :p_user_id AND gu.activ AND g.activ";
NpgsqlParameter queryParameter = new NpgsqlParameter("p_user_id", user.ID);
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameter);
Miscellaneous.NormalConnectionClose(PgSqlConnection);
if (result != null && result.Rows.Count > 0)
{
return new Group
{
ID = (Int64)result.Rows[0]["ID"],
Name = result.Rows[0]["NAME"].ToString(),
Administrator = new User
{
ID = (Int64)result.Rows[0]["ADMIN_ID"],
Username = result.Rows[0]["ADMIN_USERNAME"].ToString(),
Surname = result.Rows[0]["ADMIN_SURNAME"].ToString(),
Name = result.Rows[0]["ADMIN_NAME"].ToString()
}
}
}
;
else
{
return(null);
}
}
/// <summary>
/// this function will get a complete list of sellers for the given user
/// </summary>
/// <param name="user">the given user</param>
/// <returns>a list of sellers</returns>
public static List <ObjectStructures.Invoice.Seller> GetSellersForUser(User user)
{
//we initialize the query select command
String QueryCommand = "SELECT * FROM seller.furnizori WHERE utilizator_id = :p_user_id AND activ";
//set the solo query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_user_id", user.ID);
//if we fail to open the connection we return a null object
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//if not we retrieve the query results into a DataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//once that is done we close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//if the result contains at least one entry
if (result != null && result.Rows.Count > 0)
{
//we use linq select to transform the dataTable to a list of Seller objects
return(result.AsEnumerable().Select(row => new ObjectStructures.Invoice.Seller
{
ID = row.Field <Int32>("ID"),
Name = row.Field <String>("DENUMIRE"),
CommercialRegistryNumber = row.Field <String>("NR_REGISTRU_COMERT"),
FiscalCode = row.Field <String>("COD_FISCAL"),
Headquarters = row.Field <String>("SEDIUL"),
WorkPoint = row.Field <String>("PUNCT_LUCRU"),
Phone = row.Field <String>("TELEFON"),
Email = row.Field <String>("EMAIL"),
Logo = new Logo(row.Field <Byte[]>("SIGLA"))
}).ToList());
}
else
{
return(null);
}
}
/// <summary>
/// this function will register a new user in the database and then return it
/// </summary>
/// <param name="registerController">the register controller for the new user</param>
/// <returns>the newly added user</returns>
public static User RegisterUser(RegisterController registerController)
{
#region LogAction
//the action for the log
String Action = "A fost inregistrat un nou utilizator la adresa de email: " + registerController.Email;
//First we format the command to register
String command = String.Format("INSERT INTO users.utilizatori(nume_utilizator,email,parola,nume,prenume) " +
"VALUES({0},{1},{2},{3},{4}) RETURNING *",
registerController.Username,
registerController.Email,
registerController.Password,
registerController.Surname,
registerController.Name
);
//then we will create a new ipFunctions for the httpContextAccessor
String IP = IPFunctions.GetWANIp();
#endregion
//the insert returning command will return a single column based on the new insert
String queryCommand = "INSERT INTO users.utilizatori(nume_utilizator,email,parola,nume,prenume) " +
"VALUES(:p_username,:p_email,:p_password,:p_surname,:p_name) " +
"RETURNING *";
//we bind the parameters to the registerController properties
NpgsqlParameter[] queryParameters =
{
new NpgsqlParameter("p_username", registerController.Username),
new NpgsqlParameter("p_email", registerController.Email),
new NpgsqlParameter("p_password", registerController.Password),
new NpgsqlParameter("p_surname", registerController.Surname),
new NpgsqlParameter("p_name", registerController.Name)
};
//if we are unable to connect to the database we return a null object
//this should never happen since they will be on the same server though better safe than sorry
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//once done we run the sqlCommand and retrieve the values to a new DataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameters);
//once that is done we close the f*****g connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//we will log the current action
ActionLog.LogAction(Action, IP, command);
//before initializing a new user from the dataTable
if (result != null && result.Rows.Count > 0)
{
//then finally return the new user
return new User
{
ID = (Int64)result.Rows[0]["ID"],
Username = result.Rows[0]["NUME_UTILIZATOR"].ToString(),
Email = result.Rows[0]["EMAIL"].ToString(),
Name = result.Rows[0]["PRENUME"].ToString(),
Surname = result.Rows[0]["NUME"].ToString()
}
}
;
else
{
return(null);
}
}