// GET: Categories
public string getLstContry()
{
List <CountryEntity> lst = new List <CountryEntity>();
_conn.Open();
//---
MySqlCommand cmd = new MySqlCommand("GETCOUNTTRY", _conn.conn);
cmd.CommandType = CommandType.StoredProcedure;
using (var cursor = cmd.ExecuteReader())
{
while (cursor.Read())
{
CountryEntity item = new CountryEntity(
Convert.ToString(cursor["COUNTRY_ID"]),
Convert.ToString(cursor["COUNTRY_CODE"]),
Convert.ToString(cursor["COUNTRY_NAME"]));
lst.Add(item);
}
}
_conn.Close();
return(JsonConvert.SerializeObject(lst));
}
public void Close_ClosesConnection(bool readOnly)
{
// Arrange
_conn.Open(readOnly);
// Act
_conn.Close();
// Assert
Assert.False(_conn.IsOpen);
}
public string getMenuLeft()
{
List <MenuLeftEntity> lst = new List <MenuLeftEntity>();
_conn.Open();
//---
MySqlCommand cmd = new MySqlCommand("GETSYSMENU", _conn.conn);
cmd.CommandType = CommandType.StoredProcedure;
using (var cursor = cmd.ExecuteReader())
{
while (cursor.Read())
{
MenuLeftEntity item = new MenuLeftEntity(
Convert.ToString(cursor["MENU_ID"]),
Convert.ToString(cursor["MENU_NAME"]),
Convert.ToString(cursor["PARENT_ID"]),
Convert.ToString(cursor["URL"]),
Convert.ToString(cursor["DISPLAY_ORDER"]));
lst.Add(item);
}
}
_conn.Close();
return(Newtonsoft.Json.JsonConvert.SerializeObject(lst));
}
public void Update(string updatedBy)
{
try
{
if (companyID != Guid.Empty)
{
var com = new SqlCommand("UPDATE_COMPANY", Connect.ToDatabase());
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Id", companyID);
com.Parameters.AddWithValue("@NameInKhmer", txtNameInKhmer.Text);
com.Parameters.AddWithValue("@NameInEnglish", txtNameInEnglish.Text);
com.Parameters.AddWithValue("@Email", txtEmail.Text);
com.Parameters.AddWithValue("@Phone", txtPhone.Text);
com.Parameters.AddWithValue("@Location", txtLocation.Text);
com.Parameters.AddWithValue("@Active", chkActive.Checked);
com.Parameters.AddWithValue("@Logo", myPicture1.GetByteArrayFromBrowse());
com.Parameters.AddWithValue("@UpdatedBy", updatedBy);
com.Parameters.AddWithValue("@UpdatedDate", DateTime.Now);
com.ExecuteNonQuery();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Could not find Stored Procedure", MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
}
public static DataTable FilterListCompany(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM COMPANY ")
.AppendFormat("WHERE Active = '{0}'", filterEntity.Active);
if (!string.IsNullOrWhiteSpace(filterEntity.Keyword))
{
query.AppendFormat(" AND (NameInKhmer LIKE N'%{0}%' ", filterEntity.Keyword)
.AppendFormat("OR NameInEnglish LIKE '%{0}%') ", filterEntity.Keyword);
}
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("AND (CONVERT(DATE, CreatedDate) >= CONVERT(DATE, '{0}') ", filterEntity.FromDate)
.AppendFormat(" AND CONVERT(DATE, CreatedDate) <= CONVERT(DATE, '{0}')) ", filterEntity.ToDate);
}
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
public static void Update(CompanyEntity companyEntity)
{
try
{
var com = new SqlCommand("UPDATE_COMPANY", Connect.ToDatabase());
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Id", companyEntity.Id);
com.Parameters.AddWithValue("@NameInKhmer", companyEntity.NameInKhmer);
com.Parameters.AddWithValue("@NameInEnglish", companyEntity.NameInEnglish);
com.Parameters.AddWithValue("@Email", companyEntity.Email);
com.Parameters.AddWithValue("@Phone", companyEntity.Phone);
com.Parameters.AddWithValue("@Location", companyEntity.Location);
com.Parameters.AddWithValue("@Active", companyEntity.Active);
com.Parameters.AddWithValue("@Logo", companyEntity.Logo);
com.Parameters.AddWithValue("@UpdatedBy", companyEntity.UpdatedBy);
com.Parameters.AddWithValue("@UpdatedDate", companyEntity.UpdatedDate);
com.ExecuteNonQuery();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Could not find Stored Procedure", MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
}
public DataTable FilterList()
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT ID, Name AS ឈ្មោះថ្នាក់,")
.AppendFormat("[CreatedBy] AS បង្កើតដោយ,[CreatedDate] AS ថ្ងៃបង្កើត, [UpdatedBy] AS កែប្រែដោយ,[UpdatedDate] AS ថ្ងៃកែប្រែ ")
.AppendFormat("FROM tbClass ORDER BY Name");
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
public static DataTable FilterListUser(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM [USER]")
.AppendFormat("WHERE (Username LIKE N'%{0}%' ", filterEntity.Keyword)
.AppendFormat("OR Position LIKE '%{0}%') ", filterEntity.Keyword);
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("OR (CONVERT(DATE, CreatedDate) >= CONVERT(DATE, '{0}') ", filterEntity.FromDate)
.AppendFormat(" AND CONVERT(DATE, CreatedDate) <= CONVERT(DATE, '{0}')) ", filterEntity.ToDate);
}
query.AppendFormat("AND Active = '{0}'", filterEntity.Active);
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
public static void Update(UserEntity userEntity)
{
try
{
var com = new SqlCommand("UPDATE_USER", Connect.ToDatabase());
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Id", userEntity.Id);
com.Parameters.AddWithValue("@FirstName", userEntity.FirstName);
com.Parameters.AddWithValue("@LastName", userEntity.LastName);
com.Parameters.AddWithValue("@Username", userEntity.Username);
com.Parameters.AddWithValue("@Password", userEntity.Password);
com.Parameters.AddWithValue("@Position", userEntity.Position);
com.Parameters.AddWithValue("@Active", userEntity.Active);
com.Parameters.AddWithValue("@Phone", userEntity.Phone);
com.Parameters.AddWithValue("@BranchId", userEntity.BranchId);
com.Parameters.AddWithValue("@UpdatedBy", userEntity.UpdatedBy);
com.Parameters.AddWithValue("@UpdatedDate", userEntity.UpdatedDate);
com.ExecuteNonQuery();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Could not find Stored Procedure", MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
}
public void CheckLogin(MySqlConnection connect, string UserId, string Password)
{
string sql = "SELECT * FROM user WHERE user_id='" + UserId + "' AND password='******'";
MySqlCommand cmd = new MySqlCommand(sql, Connect);
try
{
MySqlDataReader reader = cmd.ExecuteReader();
string privilege = "NA";
while (reader.Read())
{
privilege = reader.GetString("privilage");
}
Console.Write("Privilege : " + privilege);
MessageBox.Show("You are granted with Access as " + privilege);
Home hm = new Home();
hm.Show();
this.Hide();
}
catch (IndexOutOfRangeException ex)
{
MessageBox.Show("Invalid username or password");
}
Connect.Close();
}
public void Dispose()
{
Connect?.Close();
Connect?.Dispose();
Connect = null;
GC.SuppressFinalize(this);
}
public void Close()
{
if (Connect?.State == ConnectionState.Open)
{
Connect?.Close();
}
}
public User GetUser(int userId)
{
CheckConnect();
using (Connect)
{
User _user = new User();
string commandText = "SELECT [id], [firstName], [secondName], [dateOfBirth], [gender], [middleName] " +
"FROM [systemUser] WHERE id = " + userId.ToString();
using (SQLiteCommand Command = new SQLiteCommand(commandText, Connect))
{
Connect.Open(); // открыть соединение
using (SQLiteDataReader reader = Command.ExecuteReader())
{
if (reader.Read())
{
_user = new User(
reader["firstname"].ToString(),
reader["secondName"].ToString(),
System.Convert.ToDateTime(reader["dateOfBirth"]),
System.Convert.ToInt16(reader["gender"]),
System.Convert.ToInt32(reader["id"]),
reader["middleName"].ToString()
);
}
}
}
Connect.Close(); // закрыть соединение
Connect.Dispose();
return(_user);
}
}
protected virtual void OnButton1Clicked(object sender, System.EventArgs e)
{
StoragePoolListStore.Clear();
IntPtr conn = Connect.Open(entry1.Text);
if (conn != IntPtr.Zero)
{
int numOfStoragePools = Connect.NumOfStoragePools(conn);
if (numOfStoragePools == -1)
{
ShowError("Unable to get the number of storage pools");
goto cleanup;
}
string[] storagePoolsNames = new string[numOfStoragePools];
int listStoragePools = Connect.ListStoragePools(conn, ref storagePoolsNames, numOfStoragePools);
if (listStoragePools == -1)
{
ShowError("Unable to list storage pools");
goto cleanup;
}
foreach (string storagePoolName in storagePoolsNames)
{
AddStoragePoolInTreeView(storagePoolName);
}
cleanup:
Connect.Close(conn);
}
else
{
ShowError("Unable to connect");
}
}
public static string AccountDetails(TcpClient clientSocket, string data)
{
string Token;
string ServerResponse;
//Check for Auth Token
if (Request.Contains("Token", data))
{
Token = Request.Get("Token", data);
Tokens.Token token = new Tokens.Token(((IPEndPoint)clientSocket.Client.RemoteEndPoint).Address.ToString(), Tokens.Tokens.GetTokenByToken(Token).ID, Tokens.Tokens.GetTokenByToken(Token).Username, Token);
//Check for Token
if (Tokens.Tokens.CheckToken(token))
{
//Query Database for Account Details Associated to the Name
Connect connect = new Connect();
//Username, HWID....etc
ServerResponse = connect.QueryUserAccount(token.Username) + "|" + connect.QueryUserLicensing(token.Username); //index[4]
connect.Close();
}
else
{
ServerResponse = "Authenticated Token was not found";
}
}
else
{
ServerResponse = "Authentication Token Parameter was not provided";
}
return(ServerResponse);
}
protected virtual void OnButton1Clicked(object sender, System.EventArgs e)
{
// Connect to the host
IntPtr conn = Connect.Open(entry1.Text);
if (conn != IntPtr.Zero)
{
// Set error callback. The method "ErrorCallback" will be called when error raised
Errors.SetErrorFunc(IntPtr.Zero, ErrorCallback);
// Try to look up the domain by name
IntPtr domain = Domain.LookupByName(conn, entry2.Text);
if (domain != IntPtr.Zero)
{
DomainInfo di = new DomainInfo();
Domain.GetInfo(domain, di);
entry3.Text = di.State.ToString();
entry4.Text = di.maxMem.ToString();
entry5.Text = di.memory.ToString();
entry6.Text = di.nrVirtCpu.ToString();
entry7.Text = di.cpuTime.ToString();
Domain.Free(domain);
}
Connect.Close(conn);
}
else
{
// Get the last error
Error err = Errors.GetLastError();
ShowError(err);
}
}
public static bool CheckExistName(string name)
{
bool isExist = false;
try
{
string query = string.Format("SELECT COUNT(*) FROM BRANCH WHERE Name ='{0}' AND Active = 'True'", name);
SqlCommand cmd = new SqlCommand(query, Connect.ToDatabase());
cmd.CommandTimeout = 10000;
var itExists = (Int32)cmd.ExecuteScalar() > 1;
if (itExists)
{
isExist = true;
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), @"Error", MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(isExist);
}
private void btnConnect_Click(object sender, EventArgs e)
{
lbStoragePool.Items.Clear();
IntPtr conn = Connect.Open(tbURI.Text);
if (conn != IntPtr.Zero)
{
int numOfStoragePools = Connect.NumOfStoragePools(conn);
if (numOfStoragePools == -1)
{
ShowError("Unable to get the number of storage pools");
goto cleanup;
}
string[] storagePoolsNames = new string[numOfStoragePools];
int listStoragePools = Connect.ListStoragePools(conn, ref storagePoolsNames, numOfStoragePools);
if (listStoragePools == -1)
{
ShowError("Unable to list storage pools");
goto cleanup;
}
foreach (string storagePoolName in storagePoolsNames)
{
lbStoragePool.Items.Add(storagePoolName);
}
cleanup:
Connect.Close(conn);
}
else
{
ShowError("Unable to connect");
}
}
public static string Products(TcpClient clientSocket, string data)
{
string IP = ((IPEndPoint)clientSocket.Client.RemoteEndPoint).Address.ToString();
string Product = ""; //Check if they own a Paid Product
string ServerResponse;
if (Request.Contains("Product", data))
{
Product = Request.Get("Product", data);
}
if (Tokens.Tokens.GetToken(IP) != null)
{
Tokens.Token token = Tokens.Tokens.GetToken(IP);
if (Tokens.Tokens.CheckToken(token))
{
if (Product != "")
{
int ProductID = 0;
try
{
ProductID = Convert.ToInt32(Product);
}
catch
{
ProductID = 0;
}
Connect connect = new Connect();
if (ProductID != 0)
{
ServerResponse = (connect.QueryUserProducts(token.ID, token.Username).Any(productid => productid == ProductID) ? "Authenticated" : "Not Authenticated");
}
else
{
ServerResponse = "Not Authenticated";
}
connect.Close();
}
else
{
ServerResponse = "Not Authenticated";
}
}
else
{
ServerResponse = "Not Authenticated";
}
}
else
{
ServerResponse = "Not Authenticated";
}
return(ServerResponse);
}
protected void OnDeleteEvent(object sender, DeleteEventArgs a)
{
Domain.Free(_domainPtr);
Connect.Close(_conn);
Application.Quit();
a.RetVal = true;
}
public static DataTable FilterListCustomer(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT c.*, m.MembershipType FROM CUSTOMER c INNER JOIN MEMBERSHIP m ON c.MemberShipID=m.ID ")
.AppendFormat("WHERE c.CustomerName LIKE N'%{0}%'", filterEntity.Keyword);
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("OR (CONVERT(DATE,c.CreateDate)>=CONVERT(DATE,'{0}') ", filterEntity.FromDate)
.AppendFormat("AND CONVERT(DATE,c.CreateDate)<=CONVERT(DATE,'{0}'))", filterEntity.ToDate);
}
query.AppendFormat("AND c.Active='{0}'", filterEntity.Active);
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), @"Check you sql", MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
/// <summary>
/// Caches all Products from SQL Database
/// </summary>
private static void CacheProducts()
{
Connect connect = new Connect();
Caches.Products = connect.QueryProducts();
connect.Close();
}
public void CloseDB()
{
try
{
Connect.Close();
}
catch { }
}
private static void CacheNewsfeed()
{
Connect connect = new Connect();
Caches.Newsfeed = connect.QueryNewsfeed();
connect.Close();
}
public static string Register(TcpClient clientSocket, string data)
{
string[] datas = data.Split('&');
string Username;
string Password;
string HWID;
string ServerResponse;
if (Request.Contains("Username", data))
{
Username = Request.Get("Username", data);
if (Request.Contains("Password", data))
{
Password = Request.Get("Password", data);
if (Request.Contains("HWID", data))
{
HWID = Request.Get("HWID", data);
//Validation
if (HWID.Contains("-") && HWID.Length > 32)
{
Connect connect = new Connect();
byte[] Salt = Crypto.CreateSalt(Password);
string Hash = Crypto.CreateHash(Password, Salt);
ServerResponse = connect.Register(Username, Hash, Convert.ToBase64String(Salt), HWID, ((IPEndPoint)clientSocket.Client.RemoteEndPoint).Address.ToString());
connect.Close();
}
else
{
ServerResponse = "HWID Parameter is corrupted";
}
}
else
{
ServerResponse = "HWID Parameter was not provided";
}
}
else
{
ServerResponse = "Password Parameter was not provided";
}
}
else
{
ServerResponse = "Username Parameter was not provided";
}
return(ServerResponse);
}
private static bool ActivateKey(string Username, string LicenseKey)
{
bool activated = false;
Connect connect = new Connect();
activated = connect.Activate(Username, LicenseKey);
connect.Close();
return(activated);
}
public static bool AuthenticationControl(string login, string pwd)
{
string req = "SELECT * FROM responsable WHERE responsable.login = @login AND pwd = SHA256(@pwd, 256)";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add(@login, login);
parameters.Add(pwd, pwd);
Connect curseur = Connect.Instance(connectionString);
curseur.ReqSelect(req, parameters);
if (curseur.Read())
{
curseur.Close();
return(true);
}
else
{
curseur.Close();
return(false);
}
}
/// <summary>
/// Caches all Products from SQL Database
/// </summary>
private static List <int> OwnedProducts(int ID, string Username)
{
List <int> temp = new List <int>();
Connect connect = new Connect();
temp = connect.QueryUserProducts(ID, Username);
connect.Close();
return(temp);
}
public static string ServerVersion()
{
string Version;
Connect connect = new Connect();
Version = connect.Version().ToString();
connect.Close();
return(Version);
}
public IHttpActionResult SaveItem([FromBody] JObject jsonData)
{
_conn.Open();
//ProvinceEntity obj = JsonConvert.DeserializeObject<ProvinceEntity>(jsonData.ToString());
MySqlCommand cmd = new MySqlCommand("SAVEPROVINCE", _conn.conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@I_PROVINCE_NAME", jsonData["PROVINCE_NAME"].ToString());
cmd.Parameters.AddWithValue("@I_SHORT_NAME", jsonData["SHORT_NAME"].ToString());
cmd.Parameters.AddWithValue("@I_CUSTOMIZE_NAME", jsonData["CUSTOMIZE_NAME"].ToString());
cmd.Parameters.AddWithValue("@I_DESCRIPTION", jsonData["DESCRIPTION"].ToString());
cmd.Parameters.AddWithValue("@I_SORT_ORDER", jsonData["SORT_ORDER"].ToString());
cmd.Parameters.AddWithValue("@I_COUNTRY_ID", jsonData["COUNTRY_ID"].ToString());
cmd.Parameters.AddWithValue("@I_COUNTRY_NAME", jsonData["COUNTRY_NAME"].ToString());
try
{
cmd.ExecuteNonQuery();
_conn.Close();
return(Ok(true));
}
catch (Exception e)
{
_conn.Close();
return(Ok(false));
}
}
