public static int executeQuery(string query)
{
// Initialization.
int rowCount = 0;
string strConn = HomeBusinessLogic.getcon();
SqlConnection sqlConnection = new SqlConnection(strConn);
SqlCommand sqlCommand = new SqlCommand();
try
{
// Settings.
sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandTimeout = 2 * 3600; //// Setting timeeout for longer queries to 12 hours.
// Open.
sqlConnection.Open();
// Result.
rowCount = sqlCommand.ExecuteNonQuery();
// Close.
sqlConnection.Close();
}
catch (Exception ex)
{
// Close.
sqlConnection.Close();
throw ex;
}
return(rowCount);
}
public HomeController(ILogger <HomeController> logger, IConfiguration config)
{
_logger = logger;
_configuration = config;
_dbconn = _configuration.GetConnectionString("DefaultConnection");
_hbl = new HomeBusinessLogic(_dbconn);
}
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
try
{
string email = this.txtEmail.Text;
string password = this.txtPassword.Password;
// Verification.
if (string.IsNullOrEmpty(email) || string.IsNullOrEmpty(password))
{
MessageBox.Show("This field can not be empty. Please fill all fields", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (formvalid == false)
{
MessageBox.Show("Please use valid email format", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
password = HomeBusinessLogic.GetStringSha256Hash(password);
string strConn = HomeBusinessLogic.getcon();
SqlConnection sqlConnection = new SqlConnection(strConn);
sqlConnection.Open();
string Get_Data = "SELECT id, name, email, phoneNumber FROM Users where email = '"
+ email + "' and password = '******' and role = 'admin'";
SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = Get_Data;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
MessageBox.Show("Login successful", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
Uri uri = new Uri("/Views/HomePage.xaml", UriKind.Relative);
this.NavigationService.Navigate(uri);
}
else
{
MessageBox.Show("Login unsuccessful. Please enter admin email and password", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
Console.Write(ex);
// Display Message
MessageBox.Show("Something went wrong, Please try again later.", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public ActionResult Index()
{
DashboardViewModels models = new DashboardViewModels();
models.HomeTileReport = HomeBusinessLogic.getInstance().GetHomeTile();
ViewBag.FilterBankList = common.ToSelectList(BankFacilityBusinessLogic.getInstance().getBankFacilityDDL(), "ID", "NAME", string.Empty);
ViewBag.FilterAccountList = common.ToSelectList(AccountDetailsBusinessLogic.getInstance().getAccountDetailDDL(), "ID", "NAME", string.Empty);
ViewBag.FilterCashInList = common.ToSelectList(AccountDetailsBusinessLogic.getInstance().getAccountDetailDDL(), "ID", "NAME", string.Empty);
ViewBag.FilterCashOutList = common.ToSelectList(AccountDetailsBusinessLogic.getInstance().getAccountDetailDDL(), "ID", "NAME", string.Empty);
return(View(models));
}
/// <summary>
/// Page Loaded event method
/// </summary>
/// <param name="sender">Sender parameter</param>
/// <param name="e">Event parameter</param>
private void HomePage_Loaded(object sender, RoutedEventArgs e)
{
try
{
// Auto suggestion list.
this.autoSuggestionUseControl.AutoSuggestionList = HomeBusinessLogic.LoadCountryList();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
Console.Write(ex);
}
}
private void BtnUpdate_Click(object sender, RoutedEventArgs e)
{
try
{
string id = this.txtid.Text;
string name = this.txtName.Text;
string email = this.txtEmail.Text;
string phone = this.txtPhone.Text;
string password = this.txtPassword.Text;
string roleindex = this.role.SelectedIndex.ToString();
string role = "admin";
if (roleindex == "0")
{
role = "user";
}
// Verification.
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(email) || string.IsNullOrEmpty(phone) || string.IsNullOrEmpty(id))
{
MessageBox.Show(
"This field can not be empty. Please fill all fields"
, "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// Save Info.
if (string.IsNullOrEmpty(password))
{
HomeBusinessLogic.UpdateInfo(id, name, email, phone, role);
}
else
{
HomeBusinessLogic.UpdateInfo(id, name, email, phone, password, role);
}
// Display Message
MessageBox.Show("Update succesful", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
listuser();
}
catch (Exception ex)
{
Console.Write(ex);
// Assuming data insert error is only due to duplicate email
MessageBox.Show("Update error. Check logs for detail", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void BtnReg_Click(object sender, RoutedEventArgs e)
{
try
{
string name = this.txtName.Text;
string email = this.txtEmail.Text;
string phone = this.txtPhone.Text;
string password = this.txtPassword.Text;
string roleindex = this.role.SelectedIndex.ToString();
string role = "admin";
if (roleindex == "0")
{
role = "user";
}
// Verification.
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(email) || string.IsNullOrEmpty(phone) || string.IsNullOrEmpty(password))
{
MessageBox.Show(
"This field can not be empty. Please fill all fields"
, "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (formvalid == false)
{
MessageBox.Show("Please use valid email format", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// Save Info.
HomeBusinessLogic.SaveInfo(name, email, phone, password, role);
// Display Message
MessageBox.Show("You are Successfully Registered", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
listuser();
}
catch (Exception ex)
{
Console.Write(ex);
// Assuming data insert error is only due to duplicate email
MessageBox.Show("The email has already been registered. Please use a different email", "Fail", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnImport_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
//openfile.ShowDialog();
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
string txtFilePath = openfile.FileName;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
int rowCnt = 0;
int colCnt = 0;
string th = ""; //table header
int thname = 0;
int themail = 0;
int thphone = 0;
int thpassword = 99;
int throle = 0;
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
th = (string)(excelRange.Cells[1, colCnt] as Range).Value2;
if (th.Equals("name", StringComparison.InvariantCultureIgnoreCase))
{
thname = colCnt;
}
else if (th.Equals("email", StringComparison.InvariantCultureIgnoreCase))
{
themail = colCnt;
}
else if (th.Equals("phoneNumber", StringComparison.InvariantCultureIgnoreCase))
{
thphone = colCnt;
}
else if (th.Equals("password", StringComparison.InvariantCultureIgnoreCase))
{
thpassword = colCnt;
}
else if (th.Equals("role", StringComparison.InvariantCultureIgnoreCase))
{
throle = colCnt;
}
}
for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string cellname = Convert.ToString((excelRange.Cells[rowCnt, thname] as Range).Value2);
string cellemail = Convert.ToString((excelRange.Cells[rowCnt, themail] as Range).Value2);
string cellphone = Convert.ToString((excelRange.Cells[rowCnt, thphone] as Range).Value2);
if (cellphone[0] != '0')
{
cellphone = "0" + cellphone;
}
string cellpassword = "******";
if (thpassword != 99)
{
cellpassword = Convert.ToString((excelRange.Cells[rowCnt, thpassword] as Range).Value2);
}
string cellrole = Convert.ToString((excelRange.Cells[rowCnt, throle] as Range).Value2);
try
{
HomeBusinessLogic.SaveInfo(cellname, cellemail, cellphone, cellpassword, cellrole);
}
catch (Exception ex)
{
Console.Write(ex);
// Assuming data insert error is only due to duplicate email
MessageBox.Show("Data row:" + rowCnt + ", email: " + cellemail + " is skipped. Email already exists", "Fail", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
MessageBox.Show("Data import successful", "Succesful", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Import excel: Browsefile cancelled", "Fail", MessageBoxButton.OK, MessageBoxImage.Information);
}
listuser();
}
