public static string GetUpdatedUserRoles()
{
string userRoles = "";
try
{
userRoles += "Admin";
//using (SqlCommand comm = SQL_GetSPCommand("FROU_spAdminSecurityUsersXRolesList"))
//{
// comm.Parameters.Add(new SqlParameter("@SOEID", GetSOEID()));
// // Execute the command
// comm.Connection.Open();
// SqlDataReader rdr = comm.ExecuteReader();
// while (rdr.Read())
// {
// if (rdr["Grant"].ToString() == "True" || rdr["Grant"].ToString() == "1")
// {
// userRoles += rdr["RoleName"].ToString() + ", ";
// }
// }
// comm.Connection.Close();
//}
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
UtilException uEx = new UtilException(ex);
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
return userRoles;
}
public static String CreateReportLog(string reportName)
{
int appID = 7;
String pSQL = "EXEC [Automation].[dbo].[proc_Create_ReportLog] " +
" @ApplicationID = " + appID + ", " +
" @ReportName = '" + reportName + "', " +
" @ExecutedBy = '" + GetSOEID() + "' ";
String result = "";
String LConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
SqlConnection conn = new SqlConnection(LConnectionString);
SqlCommand comm = new SqlCommand(pSQL, conn);
conn.Open();
try
{
comm.ExecuteNonQuery();
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
String extraInfo = "\n[SQL]:" + pSQL + " | \n[Params]:";
String OriginalExceptionClass = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name;
String OriginalExceptionMethod = System.Reflection.MethodBase.GetCurrentMethod().Name;
UtilException uEx = new UtilException(ex, extraInfo);
uEx.StackTrace.Add(OriginalExceptionClass + " : " + OriginalExceptionMethod);
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
finally
{
comm.Dispose();
conn.Close();
}
return result;
}
public static string GetSOEID()
{
string SOEID = "";
try
{
//Query String User
string psoeid = HttpContext.Current.Request.QueryString["psoeid"];
if (!String.IsNullOrEmpty(psoeid))
{
HttpContext.Current.Session["TestUser"] = psoeid;
SOEID = psoeid;
//We can call this every where, but only once by post back is executed "sessionWasRefreshed"
RefreshSession();
}
else
{
if (HttpContext.Current.Session["TestUser"] != null)
{
SOEID = HttpContext.Current.Session["TestUser"].ToString();
}
}
string pclear = HttpContext.Current.Request.QueryString["pclear"];
if (!String.IsNullOrEmpty(pclear))
{
HttpContext.Current.Session.Remove("TestUser");
SOEID = "";
//We can call this every where, but only once by post back is executed "sessionWasRefreshed"
RefreshSession();
}
//Priority to queryString
if (!String.IsNullOrEmpty(SOEID))
{
return SOEID;
}
//SSO User
if (HttpContext.Current.Request.Headers.Get("SM_User") != null)
{
SOEID = HttpContext.Current.Request.Headers.Get("SM_User").ToString();
}
//Try with Windows User if SSO not exists
if (String.IsNullOrEmpty(SOEID))
{
string UserLine = HttpContext.Current.User.Identity.Name.ToString();
if (!String.IsNullOrEmpty(UserLine))
{
string[] UserRow = UserLine.Split('\\');
SOEID = UserRow[1].ToUpper();
}
}
//Try with web.config User if SSO and Windows User not exist
if (!String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings["SOEIDOverride"]))
{
SOEID = System.Configuration.ConfigurationManager.AppSettings["SOEIDOverride"].ToString();
}
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = "Error Getting SOEID";
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
UtilException uEx = new UtilException(ex);
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
if (!String.IsNullOrEmpty(SOEID))
{
return SOEID;
}
else
{
throw new Exception("GetSOEID() : [SOEID NOT FOUND] : No SOEID in Session");
}
}
public static System.Data.DataTable GetDataTableFromExcel(AjaxControlToolkit.AsyncFileUpload pfileUpload, String puploadFolder, String psheetName, String pfileID)
{
System.Data.DataTable retval = null;
try
{
if (pfileUpload.HasFile)
{
string userSOEID = GetSOEID();
String _path = String.Empty;
String _fileName = String.Empty;
_fileName = pfileUpload.FileName;
//Save file
String uploadFolder = GetAppSetting("Upload_Folder");
uploadFolder += puploadFolder;
_path = System.Web.HttpContext.Current.Server.MapPath(uploadFolder);
_path = _path + pfileID + "_" + _fileName;
pfileUpload.SaveAs(_path);
string strConn = null;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _path + ";Extended Properties=Excel 12.0;";
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
{
conn.Open();
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + psheetName + "$]", conn))
{
cmd.CommandType = System.Data.CommandType.Text;
using (System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd))
{
retval = new System.Data.DataTable();
da.Fill(retval);
}
}
conn.Close();
}
}
else
{
System.Web.HttpContext.Current.Session["uploadMessage"] = "UploadError: No file";
}
}
catch (UtilException uEx)
{
if (uEx.OriginalException.ToString().Contains("user-defined table type requires 77 column"))
{
System.Web.HttpContext.Current.Session["uploadMessage"] = "UploadError: The Sheet in the excel must be have 77 columns (ID, DepartmentID, Department, Process, Group, Activity, RecoverableTCD, OwnerSOEID, OwnerName, OwnerWorkPhone, OwnerHomePhone, OwnerAlternatePhone, OwnerEmail, BackupSOEID, BackupName, BackupWorkPhone, BackupHomePhone, BackupAlternatePhone, BackupEmail, BackupCenter, TCDSOEID, TCDName, TCDWorkPhone, TCDHomePhone, TCDAlternatePhone, TCDEmail, TCDCenter, GPLSOEID, GPLName, GPLWorkPhone, GPLHomePhone, GPLAlternatePhone, GPLEmail, GPLCenter, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, BD-14, BD-13, BD-12, BD-11, BD-10, BD-9, BD-8, BD-7, BD-6, BD-5, BD-4, BD-3, BD-2, BD-1, BD1, BD2, BD3, BD4, BD5, BD6, BD7, BD8, BD9, BD10, BD11, BD12, BD13, BD14, BD15, BD16, BD17, BD18, BD19, BD20, BD21, DocumentLink), please review witch columns are missing in your file.";
}
else
{
System.Web.HttpContext.Current.Session["uploadMessage"] = "Exception: " + uEx.OriginalException.ToString();
}
}
catch (Exception ex)
{
if (ex.ToString().Contains("'" + psheetName + "$' is not a valid name."))
{
System.Web.HttpContext.Current.Session["uploadMessage"] = "UploadError: The Sheet name in the excel must be '" + psheetName + "'.";
}
else
{
UtilException uEx = new UtilException(ex);
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
System.Web.HttpContext.Current.Session["uploadMessage"] = "Exception: " + uEx.OriginalException.ToString();
}
}
return retval;
}
public static string GetConnectionString(string pkey)
{
string connString = "";
try
{
// Get DLL configuration options
string assemblyPath = new Uri(Assembly.GetExecutingAssembly().CodeBase).AbsolutePath;
Configuration cfg = ConfigurationManager.OpenExeConfiguration(assemblyPath);
connString = cfg.ConnectionStrings.ConnectionStrings[pkey].ToString();
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
UtilException uEx = new UtilException(ex);
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
return connString;
}
public static String FastSQL(string pSQL, params SqlParameter[] arrParam)
{
String result = "";
SqlConnection conn = null;
SqlCommand comm = null;
try
{
String LConnectionString = GetConnectionString("ConnString");
conn = new SqlConnection(LConnectionString);
comm = new SqlCommand(pSQL, conn);
conn.Open();
if (arrParam != null)
{
foreach (SqlParameter param in arrParam)
{
comm.Parameters.Add(param);
}
}
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
result = reader.GetString(0);
}
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
String extraInfo = "\n[SQL]:" + pSQL + " | \n[Params]:";
foreach (SqlParameter param in arrParam)
{
extraInfo = extraInfo + param.ParameterName + "=" + param.Value + ", ";
}
String OriginalExceptionClass = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name;
String OriginalExceptionMethod = System.Reflection.MethodBase.GetCurrentMethod().Name;
UtilException uEx = new UtilException(ex, extraInfo);
uEx.StackTrace.Add(OriginalExceptionClass + " : " + OriginalExceptionMethod);
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
finally
{
if (comm != null)
{
comm.Dispose();
}
if (conn != null)
{
conn.Close();
}
}
return result;
}
public static List<Dictionary<string, string>> GetUpdatedUserPermits()
{
List<Dictionary<string, string>> userPermits = new List<Dictionary<string, string>>();
try
{
userPermits.Add(new Dictionary<string, string>() {
{"IDPermit" , "1"},
{"PermitName" , "Upload files"},
{"HasAccess" , "1"}
});
//using (SqlCommand comm = SQL_GetSPCommand("FROU_spAdminSecurityUsersXPermitsList"))
//{
// comm.Parameters.Add(new SqlParameter("@SOEID", GetSOEID()));
// // Execute the command
// comm.Connection.Open();
// System.Data.SqlClient.SqlDataReader rdr = comm.ExecuteReader();
// while (rdr.Read())
// {
// Dictionary<string, string> permitData = new Dictionary<string, string>();
// permitData.Add("IDPermit", rdr["IDPermit"].ToString());
// permitData.Add("PermitName", rdr["PermitName"].ToString());
// permitData.Add("HasAccess", rdr["HasAccess"].ToString());
// userPermits.Add(permitData);
// }
// comm.Connection.Close();
//}
}
catch (UtilException uEx)
{
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
catch (Exception ex)
{
UtilException uEx = new UtilException(ex);
uEx.StackTrace.Add((System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name) + " : " + (System.Reflection.MethodBase.GetCurrentMethod().Name));
uEx.SoeidSession = GetSOEID();
uEx.SaveExceptionInBD();
ShowErrorMessage(uEx);
}
return userPermits;
}