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(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 void Delete(Customer pData)
{
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("DELETE FROM Customers ");
sSQL.AppendLine("WHERE (anCustomerID = @anCustomerID) ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@anCustomerID", pData.anCustomerID)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
if (Seznam.Any(l => l.anCustomerID == pData.anCustomerID))
{
Seznam.Remove(Seznam.First(l => l.anCustomerID == pData.anCustomerID));
}
}
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_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_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(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_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 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 void Delete(DeviceData pData)
{
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("DELETE FROM DevicesData ");
sSQL.AppendLine("WHERE (ID = @ID) ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@ID", pData.Id)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
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_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 void Add_UserRole(string pRoleID, string pUserName, int pModifUser)
{
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("INSERT INTO _mrt_UserRoles (acRoleID, acUserName, anUserMod, adTimeMod) ");
sSQL.AppendLine("VALUES (@RoleID, @UserName, @ModifiedUser, GETDATE() ) ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@RoleID", pRoleID),
new SqlParameter("@UserName", pUserName),
new SqlParameter("@ModifiedUser", pModifUser)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return;
}
public static void Delete_UserRoles(string pUserName)
{
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("DELETE FROM _mrt_UserRoles ");
sSQL.AppendLine("WHERE acUserName = @UserName ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@UserName", pUserName)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
return;
}
public static int Add(string pUserName, string pPassword, string pFirstName, string pLastName, bool pActive, string pPravice, string pEmail, string pGsm, bool pAdmin, int pModifUser)
{
foreach (DataRow r in Get().Rows)
{
string username_check = Convert.ToString(r["acUserName"]).ToLower();
if (username_check == pUserName.ToLower())
{
throw new Exception("Uporabnik s tem uporabniškim imenom že obstaja");
}
}
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
int id = 0;
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("declare @id int ");
sSQL.AppendLine(" ");
sSQL.AppendLine("INSERT INTO [_mrt_Users] ([acUserName], [acPassword], [acFirstName], [acLastName], [anActive], [adTimeMod], anUserMod, [anAdmin], acEmail, acGsm) ");
sSQL.AppendLine(" VALUES (@UserName, @Password, @FirstName, @LastName, @Active, @ModifiedDate, @anUserMod, @anAdmin, @Email, @Gsm) ");
sSQL.AppendLine("set @id=scope_identity() ");
sSQL.AppendLine("select @id ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@UserName", pUserName),
new SqlParameter("@Password", pPassword),
new SqlParameter("@FirstName", pFirstName),
new SqlParameter("@LastName", pLastName),
new SqlParameter("@Active", pActive),
new SqlParameter("@ModifiedDate", DateTime.Now),
new SqlParameter("@anAdmin", pAdmin),
new SqlParameter("@anUserMod", pModifUser),
new SqlParameter("@Email", pEmail),
new SqlParameter("@Gsm", pGsm)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams, out id);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
//if (id > 0 && pPravice.Length > 0) // add user rolse
//{
// Add_UserRoles(pPravice, pUserName, pModifUser);
//}
return(id);
}
public static int Insert(Customer pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
int id = 0;
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("declare @id int ");
sSQL.AppendLine(" ");
sSQL.AppendLine("INSERT INTO Customers (anCustomerID, acShortTitle, acTitle, acAddress, anPostID, acPostTitle, acVATPrefix, acVATNumber, acVATTypeID, anCountryID, acTelephone, anActive, acNote, aceMaile, acContactName, adModificationDate, anUserMod, anErpID) ");
sSQL.AppendLine("VALUES (@anCustomerID, @acShortTitle, @acTitle, @acAddress, @anPostID, @acPostTitle, @acVATPrefix, @acVATNumber, @acVATTypeID, @anCountryID, @acTelephone, @anActive, @acNote, @aceMaile, @acContactName, @adModificationDate, @anUserMod, @anErpID) ");
sSQL.AppendLine("set @id=scope_identity() ");
sSQL.AppendLine("select @id ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@anCustomerID", pData.anCustomerID),
new SqlParameter("@acShortTitle", pData.acShortTitle),
new SqlParameter("@anActive", pData.anActive),
new SqlParameter("@acTitle", pData.acTitle),
new SqlParameter("@acAddress", pData.acAddress),
new SqlParameter("@acPostTitle", pData.acPostTitle),
new SqlParameter("@anPostID", pData.anPostID),
new SqlParameter("@acVATPrefix", pData.acVATPrefix),
new SqlParameter("@acVATNumber", pData.acVATNumber),
new SqlParameter("@acVATTypeID", pData.acVATTypeID),
new SqlParameter("@anCountryID", pData.anCountryID),
new SqlParameter("@acTelephone", pData.acTelephone),
new SqlParameter("@acNote", pData.acNote),
new SqlParameter("@aceMaile", pData.aceMaile),
new SqlParameter("@acContactName", pData.acContactName),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anErpID", pData.anErpID)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams, out id);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
osvezi_stranko(pData);
return(id);
}
public static void Update(Customer pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("UPDATE Customers SET acShortTitle = @acShortTitle, acTitle = @acTitle, acAddress = @acAddress, anPostID = @anPostID, acPostTitle = @acPostTitle, ");
sSQL.AppendLine(" acVATPrefix=@acVATPrefix, acVATNumber = @acVATNumber, acVATTypeID = @acVATTypeID, anCountryID = @anCountryID, acTelephone=@acTelephone, aceMaile = @aceMaile, acContactName = @acContactName, ");
sSQL.AppendLine(" anActive=@anActive, acNote=@acNote, adModificationDate=@adModificationDate, anUserMod=@anUserMod, anErpID=@anErpID ");
sSQL.AppendLine("where anCustomerID=@id ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", pData.anCustomerID),
new SqlParameter("@acShortTitle", pData.acShortTitle),
new SqlParameter("@anActive", pData.anActive),
new SqlParameter("@acTitle", pData.acTitle),
new SqlParameter("@acAddress", pData.acAddress),
new SqlParameter("@acPostTitle", pData.acPostTitle),
new SqlParameter("@anPostID", pData.anPostID),
new SqlParameter("@acVATPrefix", pData.acVATPrefix),
new SqlParameter("@acVATNumber", pData.acVATNumber),
new SqlParameter("@acVATTypeID", pData.acVATTypeID),
new SqlParameter("@anCountryID", pData.anCountryID),
new SqlParameter("@acTelephone", pData.acTelephone),
new SqlParameter("@acNote", pData.acNote),
new SqlParameter("@aceMaile", pData.aceMaile),
new SqlParameter("@acContactName", pData.acContactName),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anErpID", pData.anErpID)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
osvezi_stranko(pData);
}
public static void Set(string pGroup, string pKey, int pUserMod, string pKeyValue = "")
{
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("declare @acGroup varchar(50), @acKey varchar(200), @acKeyValue varchar(max), @tmpKeyValue varchar(max), @anUserMod int ");
sSQL.AppendLine("SET @acGroup = @pGroup ");
sSQL.AppendLine("SET @acKey = @pKey ");
sSQL.AppendLine("SET @acKeyValue = @pKeyValue ");
sSQL.AppendLine("SET @anUserMod = @pUserMod ");
sSQL.AppendLine(" ");
sSQL.AppendLine("select @tmpKeyValue = acKeyValue from _mrt_Settings ");
sSQL.AppendLine("WHERE acGroup = @acGroup AND acKey = @acKey ");
sSQL.AppendLine(" ");
sSQL.AppendLine("IF @tmpKeyValue IS NULL ");
sSQL.AppendLine(" BEGIN ");
sSQL.AppendLine(" INSERT INTO _mrt_Settings (acGroup, acKey, acKeyValue, adTimeMod, anUserMod) ");
sSQL.AppendLine(" VALUES (@acGroup, @acKey, @acKeyValue, GETDATE(), @anUserMod) ");
sSQL.AppendLine(" END ");
sSQL.AppendLine("ELSE ");
sSQL.AppendLine(" BEGIN ");
sSQL.AppendLine(" UPDATE _mrt_Settings SET acKeyValue = @acKeyValue, adTimeMod = GETDATE(), anUserMod = @anUserMod ");
sSQL.AppendLine(" WHERE acGroup = @acGroup AND acKey = @acKey ");
sSQL.AppendLine(" END ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@pGroup", pGroup),
new SqlParameter("@pKey", pKey),
new SqlParameter("@pKeyValue", pKeyValue),
new SqlParameter("@pUserMod", pUserMod)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
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 void Insert(Country pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("INSERT INTO Countrys (anCountryID, acTitle, acISOCode, acCurrency, acVATCodePrefix, anIsEU, anUserMod, anErpID) ");
sSQL.AppendLine(" VALUES (@anCountryID, @acTitle, @acISOCode, @acCurrency, @acVATCodePrefix, @anIsEU, @anUserMod, @anErpID) ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@anCountryID", pData.anCountryID),
new SqlParameter("@acTitle", pData.acTitle),
new SqlParameter("@acISOCode", pData.acISOCode),
new SqlParameter("@acCurrency", pData.acCurrency),
new SqlParameter("@acVATCodePrefix", pData.acVATCodePrefix),
new SqlParameter("@anIsEU", pData.anIsEU),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anErpID", pData.anErpID)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
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 Insert(Device pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("INSERT INTO Devices (acDevID, acBT_Name, acTitle, anUserMod, adInsetDate, acEmail, anUserIns) ");
sSQL.AppendLine(" VALUES (@acDevID, @acBT_Name, @acTitle, @anUserMod, @adInsetDate, @acEmail, @anUserIns) ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acDevID", pData.acDevID),
new SqlParameter("@acBT_Name", pData.acBT_Name),
new SqlParameter("@acTitle", pData.acTitle),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@adInsetDate", pData.adInsetDate),
new SqlParameter("@acEmail", pData.acEmail),
new SqlParameter("@anUserIns", pData.anUserIns)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
public static void Update(Post pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("update Posts SET acTitle = @acTitle, acISOCode = @acISOCode, acCode = @acCode, ");
sSQL.AppendLine(" adModificationDate = @adModificationDate, anUserMod = @anUserMod, anErpID = @anErpID ");
sSQL.AppendLine("where id=@id ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", pData.ID),
new SqlParameter("@acTitle", pData.acTitle),
new SqlParameter("@acISOCode", pData.acISOCode),
new SqlParameter("@acCode", pData.acCode),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anErpID", pData.anErpID)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
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 void Insert(DeviceData pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("INSERT INTO DevicesData (acDevID, anBatteryVoltage, anSensor1, anUserMod, anSensor2) ");
sSQL.AppendLine(" VALUES (@acDevID, @anBatteryVoltage, @anSensor1, @anUserMod, @anSensor2) ");
sSQL.AppendLine(" ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@acDevID", pData.acDevID),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anBatteryVoltage", pData.anBatteryVoltage),
new SqlParameter("@anSensor1", pData.anSensor1),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anSensor2", pData.anSensor2)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
public static void Update(DeviceData pData)
{
cSettings.GetSettings("");
cSQL lSql = new cSQL();
string lResp = lSql.ConnectSQL(Splosno.AppSQLName);
if (lResp.Length > 0)
{
throw new Exception(lResp);
}
StringBuilder sSQL = new StringBuilder();
// get data
sSQL.Remove(0, sSQL.Length);
sSQL.AppendLine("update DevicesData SET adModificationDate = @adModificationDate, anUserMod = @anUserMod, anBatteryVoltage = @anBatteryVoltage, anSensor1 = @anSensor1, ");
sSQL.AppendLine(" anSensor2 = @anSensor2 ");
sSQL.AppendLine("where id=@id ");
sSQL.AppendLine(" ");
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@id", pData.Id),
new SqlParameter("@adModificationDate", pData.adModificationDate),
new SqlParameter("@anBatteryVoltage", pData.anBatteryVoltage),
new SqlParameter("@anSensor1", pData.anSensor1),
new SqlParameter("@anUserMod", pData.anUserMod),
new SqlParameter("@anSensor2", pData.anSensor2)
};
string lErr = lSql.ExecuteQuery(sSQL, sqlParams);
lSql.DisconnectSQL();
if (lErr.Length > 0)
{
throw new Exception(lErr);
}
}
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);
}