public DodoItemCollection GetItems(int skip, int take, string userId)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from {0} where " +
"visible = true and " +
"userid = ?userId ";
cmd.CommandText += "order by keywords {1} ";
cmd.CommandText += "limit {2}, {3} ";
cmd.CommandText = string.Format(cmd.CommandText,
DodoDb.table_poweq1,
DodoApplication.collateMySql,
skip, take);
cmd.Parameters.AddWithValue("userId", userId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
return(ret);
}
public List <DodoItem> GetFolders(DodoSession session, long folderId)
{
var ret = new List <DodoItem>();
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId, keywords, userId " +
"from poweq1 " +
"where userId = @userId and type = 'folder' and folderId = @folderid " +
"order by keywords";
cmd.Parameters.AddWithValue("userId", session.userId);
cmd.Parameters.AddWithValue("folderid", folderId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
return(ret);
}
public DodoItem GetRecord(long uniqueId)
{
DodoItem ret = null;
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select " +
"uniqueId, userId, keywords, modified, registered, type, fileName, " +
"fileContentType, fileSize, boxText, folderId, weight, visible " +
"from poweq1 " +
"where uniqueid = @uniqueId";
// SqlUtility.AddParameter(cmd, "uniqueId", uniqueId);
cmd.Parameters.AddWithValue("uniqueId", uniqueId);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId")));
}
}
}
}
return(ret);
}
public DodoItem GetFolder(string id, DodoSession s)
{
DodoItem ret = null;
if (id.CompareTo("-1") == 0)
{
// objeto nuevo
ret = new DodoItem();
ret.UniqueId = -1;
ret.UserId = s.userId;
}
else
{
using (var conn = CreateConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from poweq1 where uniqueId = @uniqueId";
cmd.Parameters.AddWithValue("uniqueId", id);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId")));
}
}
}
}
return(ret);
}
public IEnumerable <DodoItem> GetLastItems(string userId, string typeItem)
{
using (var conn = CreateConnection())
using (var cmd = conn.CreateCommand())
{
var sql = "select uniqueId from {0}";
sql += " where userId = @userId and type = @type";
sql += " order by registered desc";
sql += " limit 0, 10";
cmd.CommandText = string.Format(sql, table_poweq1);
cmd.Parameters.AddWithValue("userId", Convert.ToInt64(userId));
cmd.Parameters.AddWithValue("type", typeItem);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
public DodoItemCollection SearchMyItems(string userId, string keySearch)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from poweq1 where " +
"visible = true and " +
"userid = ?userId and ";
if (keySearch.IndexOf("|") > 0)
{
keySearch = keySearch.Replace("|", @"\.*");
cmd.CommandText += string.Format("keywords regexp '{0}' ", keySearch);
}
else
{
cmd.CommandText += "keywords like concat('%', @keywords, '%') ";
cmd.Parameters.AddWithValue("keywords", keySearch);
}
cmd.CommandText += "order by keywords {0}";
cmd.CommandText = string.Format(cmd.CommandText,
DodoApplication.collateMySql);
cmd.Parameters.AddWithValue("userId", userId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
return(ret);
}
public DodoItemCollection SearchMyRecords(int userId, string keySearch, long folderId)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select a.uniqueId " +
"from poweq1 a where " +
"a.userid = ?userId and " +
"a.keywords like concat('%', ?keywords, '%')";
if (folderId > -1)
{
cmd.CommandText += " and a.folderId = ?folderId";
}
cmd.CommandText += " order by a.weight desc, a.keywords {0}";
cmd.CommandText = string.Format(cmd.CommandText, DodoApplication.collateMySql);
cmd.Parameters.AddWithValue("userId", userId);
cmd.Parameters.AddWithValue("keywords", keySearch);
cmd.Parameters.AddWithValue("folderId", folderId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
return(ret);
}
public IEnumerable <DodoItem> GetRecordsFromFolder(long folderId)
{
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from poweq1 a where " +
"a.folderId = ?folderId and visible = true ";
cmd.CommandText += "order by a.weight desc, a.keywords {0}";
cmd.CommandText = string.Format(cmd.CommandText, DodoApplication.collateMySql);
cmd.Parameters.AddWithValue("folderId", folderId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
}
public DodoItem GetItem(long id)
{
DodoItem ret = DodoItemBase.GetItem <DodoItem>(app, id);
return(ret);
}