public static DataTable Get_UserRoles(int id)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM _mrt_UserRoles ");
sSQL.AppendLine("WHERE ID = @ID ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@ID", id)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return(lTmpDT);
}
public static DataTable Get_d(string acShortTitle, string acVATNumber)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM Customers ");
sSQL.AppendLine("WHERE acShortTitle LIKE @acShortTitle and acVATNumber LIKE @acVATNumber ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acShortTitle", acShortTitle),
new SqlParameter("@acVATNumber", acVATNumber)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return(lTmpDT);
}
public static DataTable Get_UserBySession(string pUsername, string pSessionID)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM _mrt_Users");
sSQL.AppendLine("WHERE acUserName = @acUserName and acSesionID = @acSesionID ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acUserName", pUsername),
new SqlParameter("@acSesionID", pSessionID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return(lTmpDT);
}
public static DataTable Get_d()
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT TOP 500 * FROM DevicesData ");
sSQL.AppendLine("ORDER BY ID desc ");
sSQL.AppendLine(" ");
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_w()
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT C.*, P.acTitle as PostTitle, P.acCode as PostCode, Cy.acTitle as CountryTitle FROM Customers C ");
sSQL.AppendLine("LEFT JOIN Posts P ON P.anPostID = C.anPostID ");
sSQL.AppendLine("LEFT JOIN Countrys Cy ON Cy.anCountryID = C.anCountryID ");
sSQL.AppendLine("ORDER BY C.acShortTitle asc ");
sSQL.AppendLine(" ");
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_d(string acTitle, string acCode)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select * from Posts where acTitle LIKE @acTitle and acCode LIKE @acCode ORDER BY acTitle asc ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acTitle", acTitle),
new SqlParameter("@acCode", acCode)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_URoles()
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM _mrt_URoles ");
sSQL.AppendLine(" ");
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return(lTmpDT);
}
public static DataTable Get_UserRoles()
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM _mrt_UserRoles ");
sSQL.AppendLine(" ");
//SqlParameter[] sqlParams = new SqlParameter[] {
// new SqlParameter("@acSystem", ConfigurationManager.AppSettings["systemID"])
// };
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return(lTmpDT);
}
public static DataTable Get_d(int anCountryID, string acTypeID)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select * from SubTypes where acTypeID=@id and anCountryID = @anCountryID ORDER BY acTitle asc ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", acTypeID),
new SqlParameter("@anCountryID", anCountryID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_w()
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT S.*, Cy.acTitle as CountryTitle FROM SubTypes S ");
sSQL.AppendLine("LEFT JOIN Countrys Cy ON Cy.anCountryID = S.anCountryID ");
sSQL.AppendLine("ORDER BY S.acTitle asc ");
sSQL.AppendLine(" ");
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_d(string acDevID)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select TOP 500 * from DevicesData where acDevID=@id ORDER BY ID desc ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", acDevID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static DataTable Get_d(int anCountryID)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM Countrys ");
sSQL.AppendLine("WHERE anCountryID=@id ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", anCountryID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
return(lTmpDT);
}
public static int Check_UserAccess(string pHash, int pUserID, string pModulGroup, string pModul, Boolean pChLic = true)
{
int lResponse = 0;
// Preveri Klasiko SesionID in UserID
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * FROM _mrt_Users ");
sSQL.AppendLine("WHERE anUserID = @anUserID and acSesionID = @acSesionID ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acSesionID", pHash),
new SqlParameter("@anUserID", pUserID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
if (lTmpDT.Rows.Count > 0)
{
lResponse = 1;
}
//if ((lResponse == 1) && (pChLic))
//{
// // Preveri če je pravica do modula
// string lCurrModul = pModulGroup + "--" + pModul;
// string[] lModules = Convert.ToString(Nastavitve.LicData["Modules"]).Split(',');
// if (!lModules.Contains(lCurrModul)) { return 0; }
//}
return(lResponse);
}
public static string Get(string pGroup, string pKey, string pDefaultValue = "")
{
string lResponse = pDefaultValue;
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lAppName = "Registrations";
string lResp = lSql.ConnectSQL(lAppName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select acKeyValue from _mrt_Settings ");
sSQL.AppendLine("WHERE acGroup = @acGroup AND acKey = @acKey ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acGroup", pGroup),
new SqlParameter("@acKey", pKey)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
if (lTmpDT.Rows.Count > 0)
{
DataRow lrow = lTmpDT.Rows[0];
lResponse = (lrow["acKeyValue"].ToString());
}
return(lResponse);
}
public static void Get_UserLoginData(int pUserID, out string pUserName, out string pUserPassword)
{
pUserName = "";
pUserPassword = "";
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("Select * FROM _mrt_Users ");
sSQL.AppendLine("WHERE anUserID = @anUserID ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@anUserID", pUserID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
if (lTmpDT.Rows.Count > 0)
{
DataRow lrow = lTmpDT.Rows[0];
pUserName = Convert.ToString(lrow["acUserName"]).Trim();
pUserPassword = Convert.ToString(lrow["acPassword"]).Trim();
}
}
public static int Get_UserID(string pDivID)
{
int lResponse = 0;
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("Select ISNULL(anUserID,0) as anUserID FROM _mrt_Clients ");
sSQL.AppendLine("WHERE acClientid = @acClientid and anIsActive = 1 ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acClientid", pDivID)
};
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
if (lTmpDT.Rows.Count > 0)
{
DataRow lrow = lTmpDT.Rows[0];
lResponse = Convert.ToInt32(lrow["anUserID"]);
}
return(lResponse);
}
public static int Get_MaxID()
{
int lResp = 0;
string lErr;
cSettings.GetSettings("");
cSQL lSql = new cSQL();
lErr = lSql.ConnectSQL(Splosno.AppSQLName);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select MAX(anPostID) as maxValue from Posts ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lSql.DisconnectSQL();
if (lTmpDT.Rows.Count > 0)
{
lResp = (DBNull.Value.Equals(lTmpDT.Rows[0]["maxValue"])) ? 0 : Convert.ToInt32(lTmpDT.Rows[0]["maxValue"]);
}
return(lResp);
}
public static int Get_LoggedUsers()
{
int lresponse = 0;
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("SELECT * ");
sSQL.AppendLine("FROM _mrt_Users (NOLOCK) WHERE anLogedIn = 1 ");
sSQL.AppendLine(" ");
//SqlParameter[] sqlParams = new SqlParameter[] {
// new SqlParameter("@acSystem", ConfigurationManager.AppSettings["systemID"])
// };
string lErr;
DataTable lTmpDT = lSql.FillDT(sSQL, out lErr);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
lresponse = lTmpDT.Rows.Count;
return(lresponse);
}
private int login(string username, string password, string pDivID, int pForceLogin, out string pDT)
{
int lResponse = -1;
pDT = "";
string lUsername, lpassword;
_DesktopApp = true;
lUsername = username;
lpassword = password;
// Preveri ali se prijavlja servis, ali uporabnik
if ((lUsername.Length == 0) && (pDivID.Length > 0))
{
_DesktopApp = false;
int lUserID = Uporabniki.Get_UserID(pDivID);
if (lUserID > 0)
{
Uporabniki.Get_UserLoginData(lUserID, out lUsername, out lpassword);
}
}
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
if (String.IsNullOrEmpty(lpassword))
{
lpassword = "";
}
sSQL.AppendLine(" ");
sSQL.AppendLine("SELECT * FROM _mrt_Users ");
sSQL.AppendLine("WHERE acUserName = @username and acPassword = @password ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@username", lUsername),
new SqlParameter("@password", lpassword)
};
string lErr = "";
DataTable lDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lDT.Rows.Count > 0)
{
DataRow lrow = lDT.Rows[0];
int lUserID = Convert.ToInt32(lrow["anUserID"]);
string lUserSessionID = (lrow["acSesionID"].ToString());
bool lUserActive = Convert.ToBoolean(lrow["anActive"]);
bool lAppLogedIn = Convert.ToBoolean(lrow["anAppLogedIn"]);
bool lLogedIn = Convert.ToBoolean(lrow["anLogedIn"]);
pDT = Splosno.SerializeDataTable_json(lDT);
if (lUserActive == true)
{
int maxLic;
//int.TryParse(Nastavitve.Get("", "LicNumber", "0"), out maxLic);
int.TryParse(Nastavitve.LicData["LicNumberValue"], out maxLic);
int currLogedUsers = Uporabniki.Get_LoggedUsers();
bool lCheckLicNumber = Convert.ToBoolean(Nastavitve.LicData["LicNumberCheck"]);
if ((!lCheckLicNumber) || (maxLic >= (currLogedUsers + 1)))
{
if ((lAppLogedIn != true && !lLogedIn) || (pForceLogin == 1))
{
string lNewSessionID = Guid.NewGuid().ToString();
// Če je uporabnik prijavljen v drugem sistemu, ohrani sesionID, da ga ne vržemo ven!
//if ((lLogedIn) || (lAppLogedIn)) { lNewSessionID = lUserSessionID; }
Set_UserUpdate(lUserID, true, lNewSessionID, _DesktopApp);
// Ponovno pridobi podatke o userju z novim sessionid-jem
DataTable lDT1 = Uporabniki.Get_UserBySession(lUsername, lNewSessionID);
pDT = Splosno.SerializeDataTable_json(lDT1);
lSql.ConnectSQL(Splosno.AppSQLName);
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("SELECT UR.acRoleID FROM _mrt_UserRoles UR WITH(NOLOCK) ");
sSQL.AppendLine("WHERE (UR.acUserName = @UserName) ");
sqlParams = null;
sqlParams = new SqlParameter[] {
new SqlParameter("@UserName", lUsername)
};
lDT = null;
lDT = lSql.FillDT(sSQL, sqlParams, out lErr);
lSql.DisconnectSQL();
if (lDT.Rows.Count > 0)
{
_pravice = Splosno.SerializeDataTable_json(lDT);
}
else
{
_pravice = "[]";
}
lrow = lDT1.Rows[0];
_id = Convert.ToInt32(lrow["anUserID"]);
_username = Convert.ToString(lrow["acUserName"]);
_ime = Convert.ToString(lrow["acFirstName"]);
_priimek = Convert.ToString(lrow["acLastName"]);
_ModifiedDate = Convert.ToDateTime(lrow["adTimeMod"]);
_active = Convert.ToBoolean(lrow["anActive"]);
_AppLogedIn = Convert.ToBoolean(lrow["anAppLogedIn"]);
_logged_in = Convert.ToBoolean(lrow["anLogedIn"]);
_SessionID = Convert.ToString(lrow["acSesionID"]);
_Email = Convert.ToString(lrow["acEmail"]);
_Gsm = Convert.ToString(lrow["acGSM"]);
_admin = Convert.ToBoolean(lrow["anAdmin"]);
lResponse = 0;
}
else
{
lResponse = 103; //Uporabnik je že prijavljen!
}
}
else
{
lResponse = 102;
}
}
else
{
lResponse = 101;
}
}
else
{
lResponse = 100;
}
return(lResponse);
}