public void AddTopartner(string id, string groupid)
{
try
{
DbTask db = new DbTask();
string Sql = string.Empty;
Sql = "UPDATE " + TableName + " SET idx = idx + 1";
db.ExecuteNonQuery(Sql);
DateTime sStartDate = DateTime.Now;
DateTime dt = DateTime.Now;
int nYear = dt.Year + 1;
dt = new DateTime(nYear, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
Sql = "INSERT INTO " + TableName + "(groupid, id, startdate, enddate, idx) ";
Sql += " VALUES(?groupid, ?id, ?startdate, ?enddate, ?idx)";
DataTable datatable = null;
db.AddParameters(ref datatable, "groupid", DbType.NVarChar, groupid);
db.AddParameters(ref datatable, "id", DbType.NVarChar, id);
db.AddParameters(ref datatable, "startdate", DbType.Datetime, sStartDate);
db.AddParameters(ref datatable, "enddate", DbType.Datetime, dt);
db.AddParameters(ref datatable, "idx", DbType.Int32, 1);
db.ExecuteNonQuery(Sql, datatable);
}
catch (Exception ae)
{
string error = ae.ToString();
}
}
public void SetStatusDefault(string id)
{
DbTask db = new DbTask();
DataRow dr = this.GetInfoStatusDefault(id);
if (dr == null)
{
return;
}
int status;
if (Convert.ToInt32(dr["status"].ToString()) == 0)
{
status = 1;
}
else
{
status = 0;
}
int vid = Convert.ToInt32(id);
string Sql = "UPDATE " + TableNameDefault + " SET ";
Sql += " status = " + status + " ";
Sql += " WHERE id = " + vid + " ";
db.ExecuteNonQuery(Sql);
}
public DataTable Searching(int Status, int CurrentPage, int RecordPerPages, out int TotalRecords, out int TotalPages)
{
DbTask db = new DbTask();
DataTable dt = null;
TotalRecords = 0;
TotalPages = 0;
string Sql = string.Empty;
Sql = "SELECT COUNT(ID) AS Total FROM " + TableName + " ";
if (Status < 2)
Sql += "WHERE status = " + Status + " ";
dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
return null;
TotalRecords = Convert.ToInt32(dt.Rows[0]["Total"]);
TotalPages = (int)TotalRecords / RecordPerPages;
if ((int)TotalRecords % RecordPerPages > 0)
TotalPages = TotalPages + 1;
Sql = "Select " + TableName + ".*, row_number() over (order by id ASC) as row_index INTO #Temp_Table ";
Sql += "From " + TableName + " ";
Sql += "Where 1 = 1 ";
int nS = RecordPerPages * (CurrentPage - 1);
int record_next = nS + RecordPerPages;
Sql += "Select * from #Temp_Table ";
if (nS == 0)
Sql += " WHERE (row_index >=" + nS + ") AND (row_index <=" + record_next + ")";
else
Sql += " WHERE (row_index >" + nS + ") AND (row_index <=" + record_next + ")";
Sql += " ORDER BY row_index ASC ";
dt = db.GetData(Sql);
return dt;
}
public void UpdateTask(DbTask task)
{
using (var connection = new NpgsqlConnection(settings.ConnectionString))
{
connection.Update(task);
}
}
public DataTable GetNotNull(string ParentID)
{
DbTask dbtask = new DbTask();
string parent = (ParentID == null || ParentID == string.Empty || ParentID == "") ? "00" : ParentID;
string Sql = "Select a.id AS pid, a.col, a.shortlink, a.icon as icon, a.fimages, a.summary, a.title AS ptitle, a.parentid as pparentid, a.link as plink, a.idx as idx, ";
Sql += "(SELECT COUNT(c.id) FROM " + TableName + " as c WHERE c.parentid = a.id) AS nextchild ";
Sql += " FROM " + TableName + " as a ";
if (parent == "00")
{
Sql += " ," + _TableName + " as b ";
}
Sql += " WHERE a.parentid = '" + parent + "' AND a.status = 1 ";
if (parent == "00")
{
Sql += " AND b.groupid = '1' AND a.id = b.id ";
Sql += "ORDER BY b.idx ASC";
}
if (parent != "00")
{
Sql += "ORDER BY a.idx ASC";
}
return(dbtask.GetData(Sql));
}
public DataTable GetmenuLeftNHS(string ParentID)
{
DbTask dbtask = new DbTask();
string parent = (ParentID == null || ParentID == string.Empty || ParentID == "") ? "" : ParentID;
string Sql = "Select a.id AS pid, a.title AS ptitle, a.parentid as pparentid,a.fimages as pfimages, a.link as plink,a.kind as kind, a.idx as idx ";
Sql += " FROM " + TableName + " as a ";
if (parent == "00")
{
Sql += " ," + _TableName + " as b ";
}
Sql += " WHERE a.parentid = '" + parent + "' AND a.status = 1 AND a.home = 1 ";
if (parent == "00")
{
Sql += " AND b.groupid = '" + _GroupID + "' AND a.id = b.id ";
Sql += "ORDER BY b.idx ASC";
}
if (parent != "00")
{
Sql += "ORDER BY a.idx ASC";
}
return(dbtask.GetData(Sql));
}
public DataTable Get_Images(string id, string productid)
{
DbTask db = new DbTask();
DataTable dt = null;
string dtNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string Sql = "SELECT distinct a.* FROM " + TableName + " as a ";
Sql += " WHERE a.status = 1 ";
Sql += " AND a.created <= '" + dtNow + "' ";
if (id != null && id != string.Empty && id != "")
{
Sql += " AND a.id = '" + id + "'";
}
if (productid != null && productid != string.Empty && productid != "")
{
Sql += " AND a.productid= '" + productid + "'";
}
Sql += " ORDER BY a.created DESC ";
dt = db.GetData(Sql);
if (id.Length > 0 || id != string.Empty)
{
return(dt);
}
else
{
return(null);
}
}
private void MapTaskToDb(DbTask dbTask, TaskDomain taskDomain)
{
dbTask.State = taskDomain.State;
dbTask.DueDate = taskDomain.DueDate;
dbTask.Priority = taskDomain.Priority;
dbTask.Description = taskDomain.Description;
}
public void result(string taskid, string pipelineid, int state)
{
using (MDB db = new MDB())
{
MaratonAPI api = new MaratonAPI();
DbTask task = db.Find <DbTask>(x => x.Id == taskid).FirstOrDefault();
task.State = state;
if (task == null)
{
return;
}
for (int i = 0; i < task.Pipelines.Count; i++)
{
if (task.Pipelines[i] != pipelineid)
{
continue;
}
if (task.Pipelines[i] == pipelineid &&
i < (task.Pipelines.Count - 1))
{
api.TaskDeliver(task, db.FindOne <DbPipeline>(x => x.Id == task.Pipelines[i + 1]));
}
}
db.UpdateOne <DbTask>(x => x.Id == taskid, task);
}
}
public DataTable GetALLBlogMember()
{
DbTask db = new DbTask();
string Sql = "SELECT * FROM " + TableName + "";
return(db.GetData(Sql));
}
public DataTable GetAllYahoo()
{
DbTask db = new DbTask();
string Sql = "Select * from " + TableName + " Order by idx ASC ";
return(db.GetData(Sql));
}
/// <summary>
/// Lấy danh sách các Yahoo trong nhóm hỗ trợ
/// </summary>
/// <param name="groupid">id của nhóm</param>
/// <returns>Datatabe</returns>
public DataTable GetYahooFromGroup(string groupid)
{
DbTask db = new DbTask();
string Sql = "Select * from " + TableName + " Where groupid = '" + groupid + "'";
return(db.GetData(Sql));
}
/// <summary>
/// Delete nick Yahoo theo id
/// </summary>
/// <param name="id"></param>
public void DeleteYahooforID(string id)
{
DbTask db = new DbTask();
string sql = "Delete From " + TableName + " where id = '" + id + "'";
db.ExecuteNonQuery(sql);
}
public DataTable GetgroupYahoo()
{
DbTask db = new DbTask();
string Sql = "Select * from " + tblgroupYahoo + "";
return(db.GetData(Sql));
}
public override void Execute()
{
LoadProcess = new LoadProcess();
var sql = new SqlTask(this, Sql)
{
DisableLogging = true,
DisableExtension = true,
Actions = new List <Action <object> >()
{
result => {
LoadProcess.LoadProcessKey = (int)DbTask.GetValueFromReader(result, "LoadProcessKey");
LoadProcess.StartDate = (DateTime)DbTask.GetValueFromReader(result, "StartDate");
LoadProcess.TransferCompletedDate = (DateTime?)DbTask.GetValueFromReader(result, "TransferCompletedDate");
LoadProcess.EndDate = (DateTime?)DbTask.GetValueFromReader(result, "EndDate");
LoadProcess.ProcessName = (string)DbTask.GetValueFromReader(result, "ProcessName");
LoadProcess.StartMessage = (string)DbTask.GetValueFromReader(result, "StartMessage");
LoadProcess.IsRunning = (bool)DbTask.GetValueFromReader(result, "IsRunning");
LoadProcess.EndMessage = (string)DbTask.GetValueFromReader(result, "EndMessage");
LoadProcess.WasSuccessful = (bool)DbTask.GetValueFromReader(result, "WasSuccessful");
LoadProcess.AbortMessage = (string)DbTask.GetValueFromReader(result, "AbortMessage");
LoadProcess.WasAborted = (bool)DbTask.GetValueFromReader(result, "WasAborted");
LoadProcess.IsFinished = (bool)DbTask.GetValueFromReader(result, "IsFinished");
LoadProcess.IsTransferCompleted = (bool)DbTask.GetValueFromReader(result, "IsTransferCompleted");
}
}
};
if (ReadOption == ReadOptions.ReadAllProcesses)
{
sql.BeforeRowReadAction = () => AllLoadProcesses = new List <LoadProcess>();
sql.AfterRowReadAction = () => AllLoadProcesses.Add(LoadProcess);
}
sql.ExecuteReader();
}
public DataTable GetVideo()
{
DbTask db = new DbTask();
string Sql = "Select * from " + TableName + "";
return(db.GetData(Sql));
}
public void Update(string id, DateTime startdate, DateTime enddate, int idx)
{
try
{
string Sql = "UPDATE " + _TableName + " SET ";
Sql += " idx = ?idx, ";
Sql += " startdate = ?startdate, ";
Sql += " enddate = ?enddate ";
Sql += "WHERE groupid = '" + _GroupID + "' ";
Sql += "AND id = '" + id + "' ";
Sql += "AND col = '" + _Col + "' ";
DbTask db = new DbTask();
DataTable dt = null;
db.AddParameters(ref dt, "groupid", DbType.NVarChar, _GroupID);
db.AddParameters(ref dt, "id", DbType.NVarChar, id);
db.AddParameters(ref dt, "col", DbType.NVarChar, _Col);
db.AddParameters(ref dt, "startdate", DbType.Datetime, startdate);
db.AddParameters(ref dt, "enddate", DbType.Datetime, enddate);
db.AddParameters(ref dt, "idx", DbType.Int32, idx);
db.ExecuteNonQuery(Sql, dt);
}
catch (Exception ae)
{
string error = ae.ToString();
}
}
public DataTable GetGroupForSearch()
{
DbTask db = new DbTask();
string Sql = "Select title, id from " + TableName + " where parentid = 00 And kind = 1";
return(db.GetData(Sql));
}
public void SetStatus(string id)
{
DbTask db = new DbTask();
DataRow dr = GetInfoKeyWord(id);
if (dr == null)
{
return;
}
int status;
if (Convert.ToInt32(dr["status"].ToString()) == 0)
{
status = 1;
}
else
{
status = 0;
}
string Sql = "UPDATE " + TableName + " SET ";
Sql += " status = " + status + " ";
Sql += " WHERE id = '" + id.Trim() + "' ";
db.ExecuteNonQuery(Sql);
}
public DataTable GetChild_Home(string ParentID)
{
DbTask dbtask = new DbTask();
string parent = (ParentID == null || ParentID == string.Empty || ParentID == "") ? "00" : ParentID;
string Sql = "Select a.id AS pid, a.shortlink as shortlink, a.sluongtin, a.title AS ptitle, a.parentid as pparentid,a.fimages as pfimages, a.link as plink,a.kind as kind, a.idx as idx ";
//Sql += "(SELECT COUNT(c.id) FROM " + TableName + " as c WHERE c.parentid = pid) AS nextchild ";
Sql += " FROM " + TableName + " as a ";
if (parent == "00")
{
Sql += " ," + _TableName + " as b ";
}
Sql += " WHERE a.parentid = '" + parent + "' AND a.status = 1 AND a.home = 1 ";
if (parent == "00")
{
Sql += " AND b.groupid = '" + _GroupID + "' AND a.id = b.id ";
Sql += "ORDER BY b.idx ASC";
}
if (parent != "00")
{
Sql += "ORDER BY a.idx ASC";
}
return(dbtask.GetData(Sql));
}
public DataTable GetAllDatabaseActive(string TenBang)
{
DbTask db = new DbTask();
string sql = "Select * from " + TenBang + " Where status = 1 Order by idx ";
return(db.GetData(sql));
}
public Member()
{
if (Globals.CheckExist)
{
try
{
string Sql = "CREATE TABLE " + TableName + " (";
Sql += "id nvarchar(10) NOT NULL default '', ";
Sql += "username nvarchar(255) default NULL, ";
Sql += "password nvarchar(100) default NULL, ";
Sql += "fullname nvarchar(255) default NULL, ";
Sql += "tel nvarchar(15) default NULL, ";
Sql += "email nvarchar(255) default NULL, ";
Sql += "address nvarchar(255) default NULL, ";
Sql += "jobtitle nvarchar(255) default NULL, ";
Sql += "status int(4) default 0, ";
Sql += "PRIMARY KEY (id))";
Sql += "ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;";
DbTask db = new DbTask();
db.ExecuteNonQuery(Sql);
}
catch
{
}
}
}
public void Delete(string id, string TenBang)
{
DbTask db = new DbTask();
string SQL = "Delete from " + TenBang + " where id = '" + id + "'";
db.ExecuteNonQuery(SQL);
}
public async void AddAsync_NewTaskAdded()
{
var newTask = _fixture.Create <TaskDomain>();
var newGuid = _fixture.Create <Guid>();
var newDateTime = _fixture.Create <DateTime>();
var dbTask = new DbTask
{
Id = newGuid,
AddedDate = newDateTime,
State = newTask.State,
DueDate = newTask.DueDate,
Priority = newTask.Priority,
Description = newTask.Description
};
_guidFactory.Setup(x => x.Create()).Returns(newGuid).Verifiable();
_dateTimeProvider.Setup(x => x.Now).Returns(newDateTime).Verifiable();
_dbRepository
.Setup(x => x.Add(It.IsAny <DbTask>()))
.Callback <DbTask>(x => x.Should().BeEquivalentTo(dbTask))
.Verifiable();
await _tasksRepository.AddAsync(newTask);
_dateTimeProvider.VerifyAll();
_dbRepository.VerifyAll();
_guidFactory.VerifyAll();
}
public void Update(string id, string title, string linkurl, string summary, string filename, string sWidth, string sHeight)
{
string Sql = "UPDATE " + TableName + " SET ";
Sql += " title = ?title, ";
Sql += " linkurl = ?linkurl, ";
Sql += " summary = ?summary, ";
Sql += " created = ?created, ";
Sql += " filename = ?filename, ";
Sql += " fwidth = ?fwidth, ";
Sql += " fHeight = ?fheight, ";
Sql += " userid = ?userid ";
Sql += " WHERE id=?id";
DbTask db = new DbTask();
DataTable dt = null;
db.AddParameters(ref dt, "id", DbType.NVarChar, id);
db.AddParameters(ref dt, "title", DbType.NVarChar, title);
db.AddParameters(ref dt, "summary", DbType.NVarChar, summary);
db.AddParameters(ref dt, "fwidth", DbType.NVarChar, sWidth);
db.AddParameters(ref dt, "fheight", DbType.NVarChar, sHeight);
db.AddParameters(ref dt, "linkurl", DbType.NVarChar, linkurl);
db.AddParameters(ref dt, "created", DbType.Datetime, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
db.AddParameters(ref dt, "filename", DbType.NVarChar, filename);
db.AddParameters(ref dt, "userid", DbType.NVarChar, Membertask.Name);
db.ExecuteNonQuery(Sql, dt);
}
public int InsertTask(DbTask task)
{
using (var connection = new NpgsqlConnection(settings.ConnectionString))
{
return(connection.Insert(task));
}
}
public void Delete(string id)
{
DbTask db = new DbTask();
string Sql = "DELETE FROM " + TableName + " WHERE id = '" + id.Trim() + "'";
db.ExecuteNonQuery(Sql);
}
public IEnumerable <DbTaskList> GetTaskLists()
{
var readTasksSql = "SELECT title, id_list, id, description, deadline, is_done FROM task";
var tasksGrouped = ReadEntities(readTasksSql,
r =>
{
var task = new DbTask();
task.Title = r.GetString(0);
task.ListId = r.GetInt32(1);
task.Id = r.GetInt32(2);
task.Description = r.GetValue(3).ToString();
task.Date = r.GetValue(4).ToString();
task.IsDone = r.GetBoolean(5);
return(task);
}).ToArray().GroupBy(x => x.ListId);
var readTaskListsSql = "SELECT title, id FROM tasks_list";
var taskListsSeq = ReadEntities(readTaskListsSql,
r =>
{
var taskList = new DbTaskList();
taskList.Name = r.GetString(0);
taskList.Id = r.GetInt32(1);
return(taskList);
}).ToArray();
return(taskListsSeq.Select(lst =>
{
var tasksGroup = tasksGrouped.FirstOrDefault(x => x.Key == lst.Id);
lst.Tasks = tasksGroup == null? new DbTask[] { } : tasksGroup.ToArray();
return lst;
}).ToArray());
}
public void DeleteLog(string vLogId)
{
DbTask db = new DbTask();
string Sql = "DELETE FROM " + TableName + " WHERE LogId = '" + vLogId.Trim() + "' ";
db.ExecuteNonQuery(Sql);
}
public void Delete(string id)
{
string Sql = "DELETE FROM " + TableName + " WHERE id = " + Convert.ToInt32(id) + " ";
DbTask db = new DbTask();
db.ExecuteNonQuery(Sql);
}
public static Task ToTask(DbTask task)
{
return new Task(task.Uid, task.Title, task.Description, task.IsDone, task.Due)
{
DbId = task.Id
};
}
