public override List <string> Query(string[] wantedValue, QueryTables tables, QueryFilter queryFilter,
Dictionary <string, bool> sort, uint?start, uint?count)
{
string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), tables.ToSQL());
return(Query2(query, queryFilter, sort, start, count));
}
public FriendInfo[] GetFriends(UUID principalID)
{
List <FriendInfo> infos = new List <FriendInfo>();
QueryTables tables = new QueryTables();
tables.AddTable(m_realm, "my");
tables.AddTable(m_realm, "his", JoinType.Inner,
new[, ] {
{ "my.Friend", "his.PrincipalID" }, { "my.PrincipalID", "his.Friend" }
});
QueryFilter filter = new QueryFilter();
filter.andFilters["my.PrincipalID"] = principalID;
List <string> query = GD.Query(new string[]
{
"my.Friend",
"my.Flags",
"his.Flags"
}, tables, filter, null, null, null);
//These are used to get the other flags below
for (int i = 0; i < query.Count; i += 3)
{
FriendInfo info = new FriendInfo
{
PrincipalID = principalID,
Friend = query[i],
MyFlags = int.Parse(query[i + 1]),
TheirFlags = int.Parse(query[i + 2])
};
infos.Add(info);
}
return(infos.ToArray());
}
public override Dictionary <string, List <string> > QueryNames(string[] keyRow, object[] keyValue,
QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} where ", wantedValue, tables.ToSQL());
return(QueryNames2(keyRow, keyValue, query));
}
public FriendInfo[] GetFriends(UUID principalID)
{
List<FriendInfo> infos = new List<FriendInfo>();
QueryTables tables = new QueryTables();
tables.AddTable(m_realm, "my");
tables.AddTable(m_realm, "his", JoinType.Inner,
new[,] {{"my.Friend", "his.PrincipalID"}, {"my.PrincipalID", "his.Friend"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["my.PrincipalID"] = principalID;
List<string> query = GD.Query(new string[]
{
"my.Friend",
"my.Flags",
"his.Flags"
}, tables, filter, null, null, null);
//These are used to get the other flags below
for (int i = 0; i < query.Count; i += 3)
{
FriendInfo info = new FriendInfo
{
PrincipalID = principalID,
Friend = query[i],
MyFlags = int.Parse(query[i + 1]),
TheirFlags = int.Parse(query[i + 2])
};
infos.Add(info);
}
return infos.ToArray();
}
public void Automation_QueryTable(string fileAndPath, string connStr, string sqlQuery)
{
if (File.Exists(fileAndPath))
{
File.Delete(fileAndPath);
}
connStr = connStr.Replace("User Id", "Uid").Replace("Password", "Pwd");
m_objExcel = new Application();
m_objBooks = m_objExcel.Workbooks;
m_objBook = m_objBooks.Add(m_objOpt);
m_objSheets = m_objBook.Worksheets;
m_objSheet = (_Worksheet)m_objSheets.Item[1];
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objQryTables = m_objSheet.QueryTables;
m_objQryTable = m_objQryTables.Add(@"ODBC;DRIVER=SQL Server;" + connStr + "APP=Microsoft Office 2013;WSID=KEDS-13136;",
m_objRange, sqlQuery);
m_objQryTable.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.Refresh(false);
m_objQryTable.Delete();
m_objBook.SaveAs(fileAndPath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
}
public override DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
return(new DataReaderConnection {
DataReader = QueryData2(query)
});
}
/// <summary>
/// Import a .csv file
/// </summary>
/// <param name="?"></param>
public static void ImportCsv(string fileName)
{
if (LogCalls)
{
DebugLog.Message("ExcelOp ImportCsv " + fileName);
}
try
{
ReleaseObject(XlQueryTables);
XlQueryTables = (Microsoft.Office.Interop.Excel.QueryTables)XlSheet.QueryTables;
QueryTables qt = XlQueryTables;
CellSelect(1, 1); // set XlRange to ("$A$1")
// Add a QueryTable for the file
XlQueryTables.Add(
Connection: "TEXT;" + fileName,
Destination: XlRange);
// Set the other QueryTable properties
qt[1].Name = Path.GetFileNameWithoutExtension(fileName);
//qt[1].FieldNames = true;
//qt[1].RowNumbers = false;
//qt[1].FillAdjacentFormulas = false;
//qt[1].PreserveFormatting = true;
//qt[1].RefreshOnFileOpen = false;
//qt[1].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
//qt[1].SavePassword = false;
//qt[1].SaveData = true;
//qt[1].AdjustColumnWidth = true;
//qt[1].RefreshPeriod = 0;
qt[1].TextFilePromptOnRefresh = false;
qt[1].TextFilePlatform = 437; // default = 2
qt[1].TextFileStartRow = 1;
qt[1].TextFileParseType = XlTextParsingType.xlDelimited;
qt[1].TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote;
qt[1].TextFileConsecutiveDelimiter = false;
qt[1].TextFileTabDelimiter = false; // default: true
qt[1].TextFileSemicolonDelimiter = false; // default: true
qt[1].TextFileCommaDelimiter = true; // default: false
qt[1].TextFileSpaceDelimiter = false;
qt[1].TextFileTrailingMinusNumbers = true;
qt[1].Refresh(false);
qt[1].Delete(); // cleanup
return;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// Given a query and an update date, check if the query info is current.
/// </summary>
/// <param name="queryId"></param>
/// <param name="dateLastModified"></param>
/// <param name="dateUpdated"></param>
/// <returns>Unknown/Invalid/UpToDate</returns>
static public DataUpdateStatus CheckDataStatus(string queryId, DateTime dateLastModified, out DateTime dateUpdated)
{
dateUpdated = GxContext.StartupDate; // by default the data is as old as the startup moment of the app
if (enabled)
{
if (!QueryTables.ContainsKey(queryId)) // There is no table definition for the query -> status unknown
{
return(DataUpdateStatus.Unknown);
}
ICacheService2 updatedTablesBulk = updatedTables as ICacheService2;
IDictionary <string, DateTime> dateUpdates;
List <string> qTables = QueryTables[queryId];
if (updatedTablesBulk != null)
{
dateUpdates = updatedTablesBulk.GetAll <DateTime>(CacheFactory.CACHE_SD, qTables); //Value is Date.MinValue for non-existing key in cache
}
else
{
dateUpdates = new Dictionary <string, DateTime>();
foreach (string tbl in qTables)
{
if (updatedTables.Get <DateTime>(CacheFactory.CACHE_SD, tbl, out DateTime tblDt))
{
dateUpdates[tbl] = tblDt;
}
}
}
DateTime maxDateUpdated = dateUpdates.Values.Max(); //Get the newest modification date.
if (maxDateUpdated > dateUpdated)
{
dateUpdated = maxDateUpdated;
}
if (dateUpdated > dateLastModified) // If any of the query tables were modified -> the status of the info is INVALID, you have to refresh
{
return(DataUpdateStatus.Invalid);
}
return(DataUpdateStatus.UpToDate);
}
else
{
return(DataUpdateStatus.Unknown);
}
}
public abstract Dictionary<string, List<string>> QueryNames(string[] keyRow, object[] keyValue,
QueryTables tables, string wantedValue);
public abstract DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue);
public override Dictionary<string, List<string>> QueryNames(string[] keyRow, object[] keyValue,
QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} where ", wantedValue, tables.ToSQL());
return QueryNames2(keyRow, keyValue, query);
}
public abstract List<string> Query(string[] wantedValue, QueryTables tables, QueryFilter queryFilter,
Dictionary<string, bool> sort, uint? start, uint? count);
public override IDataReader QueryData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables, whereClause);
return QueryData2(query);
}
public override List<string> QueryFullData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
return QueryFullData2(query);
}
public List<GroupTitlesData> GetGroupTitles(UUID requestingAgentID, UUID GroupID)
{
object remoteValue = DoRemote(requestingAgentID, GroupID);
if (remoteValue != null || m_doRemoteOnly)
return (List<GroupTitlesData>) remoteValue;
QueryTables tables = new QueryTables();
tables.AddTable("osgroupmembership", "osgm");
tables.AddTable("osgrouprolemembership", "osgrm", JoinType.Inner,
new[,] {{"osgm.AgentID", "osgrm.AgentID"}, {"osgm.GroupID", "osgrm.GroupID"}});
tables.AddTable("osrole", "osr", JoinType.Inner,
new[,] {{"osgrm.RoleID", "osr.RoleID"}, {"osgm.GroupID", "osr.GroupID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osgm.AgentID"] = requestingAgentID;
filter.andFilters["osgm.GroupID"] = GroupID;
List<string> Membership = data.Query(new[]
{
"osgm.SelectedRoleID",
"osgrm.RoleID",
"osr.Name"
}, tables, filter, null, null, null);
List<GroupTitlesData> titles = new List<GroupTitlesData>();
for (int loop = 0; loop < Membership.Count(); loop += 3)
{
titles.Add(new GroupTitlesData
{
Name = Membership[loop + 2],
UUID = UUID.Parse(Membership[loop + 1]),
Selected = Membership[loop + 0] == Membership[loop + 1]
});
}
return titles;
}
public List<GroupRolesData> GetAgentGroupRoles(UUID requestingAgentID, UUID AgentID, UUID GroupID)
{
// I couldn't actually get this function to call when testing changes
object remoteValue = DoRemote(requestingAgentID, AgentID, GroupID);
if (remoteValue != null || m_doRemoteOnly)
return (List<GroupRolesData>) remoteValue;
//No permissions check necessary, we are checking only roles that they are in, so if they arn't in the group, that isn't a problem
QueryTables tables = new QueryTables();
tables.AddTable("osgrouprolemembership", "osgm");
tables.AddTable("osrole", "osr", JoinType.Inner, new[,] {{"osgm.RoleID", "osr.RoleID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osgm.AgentID"] = AgentID;
filter.andFilters["osgm.GroupID"] = GroupID;
string[] fields = new[]
{
"osr.Name",
"osr.Description",
"osr.Title",
"osr.Powers",
"osr.RoleID"
};
List<string> Roles = data.Query(fields, tables, filter, null, null, null);
filter = new QueryFilter();
List<GroupRolesData> RolesData = new List<GroupRolesData>();
for (int loop = 0; loop < Roles.Count; loop += fields.Length)
{
RolesData.Add(new GroupRolesData
{
RoleID = UUID.Parse(Roles[loop + 4]),
Name = Roles[loop + 0],
Description = Roles[loop + 1],
Powers = ulong.Parse(Roles[loop + 3]),
Title = Roles[loop + 2]
});
}
return RolesData;
}
public override List <string> QueryFullData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
return(QueryFullData2(query));
}
public abstract List<string> QueryFullData(string whereClause, QueryTables tables, string wantedValue);
public abstract List <string> QueryFullData(string whereClause, QueryTables tables, string wantedValue);
public abstract DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue);
public abstract Dictionary <string, List <string> > QueryNames(string[] keyRow, object[] keyValue,
QueryTables tables, string wantedValue);
public abstract List <string> Query(string[] wantedValue, QueryTables tables, QueryFilter queryFilter,
Dictionary <string, bool> sort, uint?start, uint?count);
public List<GroupRolesData> GetAgentGroupRoles(UUID requestingAgentID, UUID agentID, UUID groupID)
{
if (m_doRemoteOnly) {
object remoteValue = DoRemote (requestingAgentID, agentID, groupID);
return remoteValue != null ? (List<GroupRolesData>)remoteValue : new List<GroupRolesData> ();
}
//No permissions check necessary, we are checking only roles that they are in, so if they arn't in the group, that isn't a problem
QueryTables tables = new QueryTables();
tables.AddTable(_MEMBERSHIPROLEREALM, "osgm");
tables.AddTable(_ROLEREALM, "osr", JoinType.Inner, new[,] {{"osgm.RoleID", "osr.RoleID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osgm.AgentID"] = agentID;
filter.andFilters["osgm.GroupID"] = groupID;
string[] fields = new[]
{
"osr.Name",
"osr.Description",
"osr.Title",
"osr.Powers",
"osr.RoleID"
};
List<string> Roles = GD.Query(fields, tables, filter, null, null, null);
filter = new QueryFilter();
List<GroupRolesData> RolesData = new List<GroupRolesData>();
for (int loop = 0; loop < Roles.Count; loop += fields.Length)
{
RolesData.Add(new GroupRolesData
{
RoleID = UUID.Parse(Roles[loop + 4]),
Name = Roles[loop + 0],
Description = Roles[loop + 1],
Powers = ulong.Parse(Roles[loop + 3]),
Title = Roles[loop + 2]
});
}
return RolesData;
}
public List<GroupRoleMembersData> GetGroupRoleMembers(UUID requestingAgentID, UUID GroupID)
{
object remoteValue = DoRemote(requestingAgentID, GroupID);
if (remoteValue != null || m_doRemoteOnly)
return (List<GroupRoleMembersData>) remoteValue;
List<GroupRoleMembersData> RoleMembers = new List<GroupRoleMembersData>();
QueryTables tables = new QueryTables();
tables.AddTable("osgrouprolemembership", "osgrm");
tables.AddTable("osrole", "osr", JoinType.Inner, new[,] {{"osr.RoleID", "osgrm.RoleID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osgrm.GroupID"] = GroupID;
string[] fields = new[]
{
"osgrm.RoleID",
"osgrm.AgentID",
"osr.Powers"
};
List<string> Roles = data.Query(fields, tables, filter, null, null, null);
GroupMembersData GMD = GetAgentGroupMemberData(requestingAgentID, GroupID, requestingAgentID);
const long canViewMemebersBit = 140737488355328L;
for (int i = 0; i < Roles.Count; i += fields.Length)
{
GroupRoleMembersData RoleMember = new GroupRoleMembersData
{
RoleID = UUID.Parse(Roles[i]),
MemberID = UUID.Parse(Roles[i + 1])
};
// if they are a member, they can see everyone, otherwise, only the roles that are supposed to be shown
if (GMD != null ||
((long.Parse(Roles[i + 2]) & canViewMemebersBit) == canViewMemebersBit ||
RoleMember.MemberID == requestingAgentID))
RoleMembers.Add(RoleMember);
}
return RoleMembers;
}
public override DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables, whereClause);
SqliteConnection conn;
var data = QueryData2(query, out conn);
return new DataReaderConnection {DataReader = data, Connection = conn};
}
public List<GroupMembershipData> GetAgentGroupMemberships(UUID requestingAgentID, UUID AgentID)
{
object remoteValue = DoRemote(requestingAgentID, AgentID);
if (remoteValue != null || m_doRemoteOnly)
return (List<GroupMembershipData>) remoteValue;
QueryTables tables = new QueryTables();
tables.AddTable("osgroup", "osg");
tables.AddTable("osgroupmembership", "osgm", JoinType.Inner, new[,] {{"osg.GroupID", "osgm.GroupID"}});
tables.AddTable("osrole", "osr", JoinType.Inner, new[,] {{"osgm.SelectedRoleID", "osr.RoleID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osgm.AgentID"] = AgentID;
string[] fields = new[]
{
"osgm.AcceptNotices",
"osgm.Contribution",
"osgm.ListInProfile",
"osgm.SelectedRoleID",
"osr.Title",
"osr.Powers",
"osg.AllowPublish",
"osg.Charter",
"osg.FounderID",
"osg.Name",
"osg.InsigniaID",
"osg.MaturePublish",
"osg.MembershipFee",
"osg.OpenEnrollment",
"osg.ShowInList",
"osg.GroupID"
};
List<string> Membership = data.Query(fields, tables, filter, null, null, null);
List<GroupMembershipData> results = new List<GroupMembershipData>();
for (int loop = 0; loop < Membership.Count; loop += fields.Length)
{
results.Add(new GroupMembershipData
{
AcceptNotices = int.Parse(Membership[loop + 0]) == 1,
Active = true,
//TODO: Figure out what this is and its effects if false
ActiveRole = UUID.Parse(Membership[loop + 3]),
AllowPublish = int.Parse(Membership[loop + 6]) == 1,
Charter = Membership[loop + 7],
Contribution = int.Parse(Membership[loop + 1]),
FounderID = UUID.Parse(Membership[loop + 8]),
GroupID = UUID.Parse(Membership[loop + 15]),
GroupName = Membership[loop + 9],
GroupPicture = UUID.Parse(Membership[loop + 10]),
GroupPowers = ulong.Parse(Membership[loop + 5]),
GroupTitle = Membership[loop + 4],
ListInProfile = int.Parse(Membership[loop + 2]) == 1,
MaturePublish = int.Parse(Membership[loop + 11]) == 1,
MembershipFee = int.Parse(Membership[loop + 12]),
OpenEnrollment = int.Parse(Membership[loop + 13]) == 1,
ShowInList = int.Parse(Membership[loop + 14]) == 1
});
}
return results;
}
public override List<string> Query(string[] wantedValue, QueryTables tables, QueryFilter queryFilter,
Dictionary<string, bool> sort, uint? start, uint? count)
{
string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), tables.ToSQL());
return Query2(query, queryFilter, sort, start, count);
}
public GroupMembershipData GetGroupMembershipData(UUID requestingAgentID, UUID GroupID, UUID AgentID)
{
object remoteValue = DoRemote(requestingAgentID, GroupID, AgentID);
if (remoteValue != null || m_doRemoteOnly)
return (GroupMembershipData) remoteValue;
if (GroupID == UUID.Zero)
GroupID = GetAgentActiveGroup(requestingAgentID, AgentID);
if (GroupID == UUID.Zero)
return null;
QueryTables tables = new QueryTables();
tables.AddTable("osgroup", "osg");
tables.AddTable("osgroupmembership", "osgm", JoinType.Inner, new[,] {{"osg.GroupID", "osgm.GroupID"}});
tables.AddTable("osrole", "osr", JoinType.Inner,
new[,] {{"osgm.SelectedRoleID", "osr.RoleID"}, {"osr.GroupID", "osg.GroupID"}});
QueryFilter filter = new QueryFilter();
filter.andFilters["osg.GroupID"] = GroupID;
filter.andFilters["osgm.AgentID"] = AgentID;
string[] fields = new[]
{
"osgm.AcceptNotices",
"osgm.Contribution",
"osgm.ListInProfile",
"osgm.SelectedRoleID",
"osr.Title",
"osr.Powers",
"osg.AllowPublish",
"osg.Charter",
"osg.FounderID",
"osg.Name",
"osg.InsigniaID",
"osg.MaturePublish",
"osg.MembershipFee",
"osg.OpenEnrollment",
"osg.ShowInList"
};
List<string> Membership = data.Query(fields, tables, filter, null, null, null);
if (fields.Length != Membership.Count)
return null;
GroupMembershipData GMD = new GroupMembershipData
{
AcceptNotices = int.Parse(Membership[0]) == 1,
Active = true, //TODO: Figure out what this is and its effects if false
ActiveRole = UUID.Parse(Membership[3]),
AllowPublish = int.Parse(Membership[6]) == 1,
Charter = Membership[7],
Contribution = int.Parse(Membership[1]),
FounderID = UUID.Parse(Membership[8]),
GroupID = GroupID,
GroupName = Membership[9],
GroupPicture = UUID.Parse(Membership[10]),
GroupPowers = ulong.Parse(Membership[5]),
GroupTitle = Membership[4],
ListInProfile = int.Parse(Membership[2]) == 1,
MaturePublish = int.Parse(Membership[11]) == 1,
MembershipFee = int.Parse(Membership[12]),
OpenEnrollment = int.Parse(Membership[13]) == 1,
ShowInList = int.Parse(Membership[14]) == 1
};
return GMD;
}
public override DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue)
{
string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
return new DataReaderConnection {DataReader = QueryData2(query)};
}
