public HistoryPresenterModel(IUnitInfoDatabase database)
{
if (database == null) throw new ArgumentNullException("database");
_database = database;
Debug.Assert(_database.Connected);
_queryList = new List<QueryParameters>();
_queryList.Add(new QueryParameters());
_queryList.Sort();
_queryBindingSource = new BindingSource();
_queryBindingSource.DataSource = _queryList;
_queryBindingSource.CurrentItemChanged += (s, e) =>
{
OnPropertyChanged("EditAndDeleteButtonsEnabled");
_currentPage = 1;
ResetBindings(true);
};
_historyList = new HistoryEntrySortableBindingList();
_historyList.Sorted += (s, e) =>
{
SortColumnName = e.Name;
SortOrder = e.Direction;
};
_historyBindingSource = new BindingSource();
_historyBindingSource.DataSource = _historyList;
_page = new PetaPoco.Page<HistoryEntry> { Items = new List<HistoryEntry>() };
}
public void ResetBindings(bool executeQuery)
{
Debug.Assert(SelectedQuery != null);
if (executeQuery)
{
_page = _database.Page(CurrentPage, ShowEntriesValue, SelectedQuery, BonusCalculation);
}
if (_page == null)
{
return;
}
// halt binding source updates
_historyBindingSource.RaiseListChangedEvents = false;
_historyList.RaiseListChangedEvents = false;
// refresh the underlying binding list
RefreshHistoryList(_page.Items);
// sort the list
_historyBindingSource.Sort = null;
if (!String.IsNullOrEmpty(SortColumnName))
{
_historyBindingSource.Sort = SortColumnName + " " + SortOrder.ToDirectionString();
}
// enable binding source updates
_historyBindingSource.RaiseListChangedEvents = true;
_historyList.RaiseListChangedEvents = true;
// reset AFTER RaiseListChangedEvents is enabled
_historyBindingSource.ResetBindings(false);
OnPropertyChanged("TotalEntries");
OnPropertyChanged("CurrentPage");
}
public HistoryPresenterModel(IUnitInfoDatabase database)
{
if (database == null)
{
throw new ArgumentNullException("database");
}
_database = database;
Debug.Assert(_database.Connected);
_queryList = new List <QueryParameters>();
_queryList.Add(new QueryParameters());
_queryList.Sort();
_queryBindingSource = new BindingSource();
_queryBindingSource.DataSource = _queryList;
_queryBindingSource.CurrentItemChanged += (s, e) =>
{
OnPropertyChanged("EditAndDeleteButtonsEnabled");
_currentPage = 1;
ResetBindings(true);
};
_historyList = new HistoryEntrySortableBindingList();
_historyList.Sorted += (s, e) =>
{
SortColumnName = e.Name;
SortOrder = e.Direction;
};
_historyBindingSource = new BindingSource();
_historyBindingSource.DataSource = _historyList;
_page = new PetaPoco.Page <HistoryEntry> {
Items = new List <HistoryEntry>()
};
}
public WorkUnitHistoryModel(IPreferences preferences, WorkUnitQueryDataContainer queryContainer, IWorkUnitRepository repository)
{
Preferences = preferences;
QueryContainer = queryContainer;
Repository = repository ?? throw new ArgumentNullException(nameof(repository));
_queryList = new List <WorkUnitQuery> {
WorkUnitQuery.SelectAll
};
QueryBindingSource = new BindingSource();
QueryBindingSource.DataSource = _queryList;
QueryBindingSource.CurrentItemChanged += (s, e) =>
{
OnPropertyChanged(nameof(EditAndDeleteButtonsEnabled));
_currentPage = 1;
ResetBindings(true);
};
_workUnitList = new WorkUnitRowSortableBindingList();
_workUnitList.RaiseListChangedEvents = false;
_workUnitList.Sorted += (s, e) =>
{
SortColumnName = e.Name;
SortOrder = e.Direction;
};
HistoryBindingSource = new BindingSource();
HistoryBindingSource.DataSource = _workUnitList;
_page = new PetaPoco.Page <WorkUnitRow> {
Items = new List <WorkUnitRow>()
};
}
public void ResetBindings(bool executeQuery)
{
Debug.Assert(SelectedWorkUnitQuery != null);
if (executeQuery)
{
_page = Repository.Page(CurrentPage, ShowEntriesValue, SelectedWorkUnitQuery, BonusCalculation);
}
if (_page == null)
{
return;
}
// refresh the underlying binding list
RefreshHistoryList(_page.Items);
// sort the list
HistoryBindingSource.Sort = null;
if (!String.IsNullOrEmpty(SortColumnName))
{
HistoryBindingSource.Sort = SortColumnName + " " + SortOrder.ToBindingSourceSortString();
_workUnitList.ApplySort(_workUnitList.SortDescriptions);
}
HistoryBindingSource.ResetBindings(false);
OnPropertyChanged(nameof(TotalEntries));
OnPropertyChanged(nameof(CurrentPage));
}
/// <summary>
/// 返回指定类型,指定页的书籍列表
/// </summary>
/// <param name="family"> 类型 </param>
/// <returns></returns>
public IEnumerable <book_info> GetBookList(int currentPage, int count, String family, out int total)
{
total = 0;
try
{
PetaPoco.Page <book_info> result = null;
total = _db.Query <int>("SELECT COUNT(*) FROM book_info").First();
if (String.IsNullOrEmpty(family))
{
result = _db.Page <book_info>(currentPage, count, "SELECT * FROM book_info ORDER BY book_name");
}
else
{
result = _db.Page <book_info>(currentPage, count, "SELECT * FROM book_info WHERE family=@0 ORDER BY book_name", family);
}
_logservice.Info($"{DateTime.Now.ToShortDateString()}查询数据-页码：{currentPage}-每页条数{count}-书籍类型-{family}");
return(result.Items);
}
catch (Exception e)
{
_logservice.Error(e.Message);
return(null);
}
}
public Page <T> Parse <DbTable>(PetaPoco.Page <DbTable> page)
{
CurrentPage = page.CurrentPage;
TotalPages = page.TotalPages;
TotalItems = page.TotalItems;
ItemsPerPage = page.ItemsPerPage;
return(this);
}
public ActionResult Index(PetaPoco.Page <Qq_Article> page, Qq_Article queryModel)
{
if (page.ItemsPerPage == 0)
{
page.ItemsPerPage = 20;
}
if (page.CurrentPage == 0)
{
page.CurrentPage = 1;
}
page = artBll.Page(page.CurrentPage, page.ItemsPerPage, queryModel, nameof(queryModel.PublicTime), "DESC");
return(View(page));
}
private PetaPoco.Page <HistoryEntry> PageInternal(long page, long itemsPerPage, QueryParameters parameters, BonusCalculationType bonusCalculation)
{
Debug.Assert(TableExists(SqlTable.WuHistory));
var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
select.Append(WhereBuilder.Execute(parameters));
GetProduction.BonusCalculation = bonusCalculation;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
PetaPoco.Page <HistoryEntry> query = database.Page <HistoryEntry>(page, itemsPerPage, select);
Debug.Assert(query != null);
return(query);
}
}
}
public PagedDataModelCollection <CombinedLogDataModel> CombinedPageLog(int pageSize, int pageNumber, string sortColumn, string sortDirection, Dictionary <string, string> searchDictionary)
{
using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName))
{
try
{
PetaPoco.Sql sql = new PetaPoco.Sql();
sql.Append("SELECT ");
sql.Append("user.EmailAddress, log.UserID, log.TimeStamp, log.LogID, log.Level, log.Message, log.Source, log.Important ");
sql.Append("from log ");
sql.Append(" INNER JOIN user on user.userID = log.userID ");
//a boolean to keep track of whether sql string is at first search clause
bool FirstClause = true;
string dateString = "";
foreach (KeyValuePair <string, string> entry in searchDictionary)
{
string userInput = entry.Value;
if (!string.IsNullOrWhiteSpace(userInput))
{
if (entry.Key == "Important") //only get logs that are marked as important
{
if (!FirstClause)
{
sql.Append(string.Format("AND Important != \'\'"));
}
else
{
sql.Append(string.Format("WHERE Important != \'\'"));
}
FirstClause = false;
}
else if (entry.Key == "TimeStamp") //get logs from one specific date
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND DATE(TimeStamp) = {0} ", userInput));
}
else
{
sql.Append(string.Format("WHERE DATE(TimeStamp) = {0} ", userInput));
}
FirstClause = false;
}
else if (entry.Key == "beginDate") //format date at beginning of range
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
dateString = string.Format("DATE(TimeStamp) BETWEEN {0} AND ", userInput);
}
else if (entry.Key == "endDate" && dateString != "") //format date at end of range
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND {0} {1} ", dateString, userInput));
}
else
{
sql.Append(string.Format("WHERE {0} {1} ", dateString, userInput));
}
FirstClause = false;
}
else
{
if (userInput.Contains("@")) //format email
{
string[] sections = userInput.Split(new[] { '@' });
sections[1] = sections[1].Insert(0, "@@");
userInput = string.Join("", sections);
}
string newKey = entry.Key; //formatting for "get logs marked as important by a specific username"
if (entry.Key == "UserImportant")
{
newKey = "Important";
}
if (userInput[0] != '\'')
{
userInput = string.Format("\'%{0}%\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND {0} LIKE {1} ", newKey, userInput));
}
else
{
sql.Append(string.Format("WHERE {0} LIKE {1} ", newKey, userInput));
}
FirstClause = false;
}
}
}
sql.Append(string.Format("ORDER BY {0} {1}", sortColumn, sortDirection));
PetaPoco.Page <CombinedLogPoco> page = db.Page <CombinedLogPoco>(pageNumber, pageSize, sql);
if (page == null)
{
return(null);
}
return(new PagedDataModelCollection <CombinedLogDataModel>()
{
Items = page.Items.Select(s => s.ToModel()),
PageNumber = pageNumber,
PageSize = pageSize,
TotalItems = page.TotalItems,
TotalPages = page.TotalPages,
SortBy = sortColumn,
SortDirection = sortDirection,
SearchDictionary = searchDictionary,
});
}
catch (Exception ex)
{
string errorMessage = ex.Message;
}
finally { }
}
return(null);
}
public Page <Task> GetTasks(long pageNum, long itemsPerPage, DateTime?endTime,
int projectId, List <TaskStatus> taskStatusList, List <int> authorizedProjectIds,
string upperLimitDate = "9999-12-31", string lowerLimitDate = "1753-01-02", string paymentDay = "1753-01-02")
{
if (authorizedProjectIds.Count == 0 || taskStatusList.Count == 0)
{
return(new Page <Task>
{
Items = new List <Task>()
});
}
var sql = "SELECT * FROM dbo.Tasks";
var authrizedCondition = "(" + string.Join(", ", authorizedProjectIds.ConvertAll(x => x.ToString())) + ")";
sql += " WHERE project_id IN " + authrizedCondition + " AND ";
List <string> sqlCondition = new List <string>();
List <object> param = new List <object>();
var taskStatusCondition = "(" + string.Join(", ", taskStatusList.ConvertAll(x => (int)x)) + ")";
sql += " task_status_id IN " + taskStatusCondition + " AND ";
if (projectId != -1)
{
sqlCondition.Add(" project_id = @" + sqlCondition.Count);
param.Add(projectId);
}
if (endTime.HasValue)
{
sqlCondition.Add(" end_time >= @" + sqlCondition.Count);
param.Add(DateTime.Today);
sqlCondition.Add(" end_time <= @" + sqlCondition.Count);
param.Add(endTime.Value);
}
sqlCondition.Add(" end_time > @" + sqlCondition.Count);
param.Add(DateTime.Parse(lowerLimitDate));
sqlCondition.Add(" end_time <= @" + sqlCondition.Count);
param.Add(DateTime.Parse(upperLimitDate));
sqlCondition.Add(" task_status_id <> @" + sqlCondition.Count);
param.Add((int)TaskStatus.Deleted);
if (projectId != -1 && !string.IsNullOrWhiteSpace(paymentDay) && paymentDay != "1753-01-02")
{
sqlCondition.Add(" not exists (select short_code from dbo.TaskPeriod where project_id = @" + sqlCondition.Count);
param.Add(projectId);
sqlCondition.Add(" payment_date <> @" + sqlCondition.Count);
param.Add(DateTime.Parse(paymentDay));
sqlCondition.Add(" short_code = dbo.Tasks.short_code) " +
"or short_code in (select short_code from dbo.TaskPeriod where project_id = @" + sqlCondition.Count);
param.Add(projectId);
sqlCondition.Add(" payment_date = @" + sqlCondition.Count + ")");
param.Add(DateTime.Parse(paymentDay));
}
sql += string.Join(" AND ", sqlCondition.ToArray());
sql += m_orderBy;
PetaPoco.Page <ABSMgrConn.TableTasks> page = null;
if (param.Count == 0)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql);
}
else if (param.Count == 1)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0]);
}
else if (param.Count == 2)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1]);
}
else if (param.Count == 3)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2]);
}
else if (param.Count == 4)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3]);
}
else if (param.Count == 5)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4]);
}
else if (param.Count == 6)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5]);
}
else if (param.Count == 7)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5], param[6]);
}
else if (param.Count == 8)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7]);
}
else if (param.Count == 9)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7], param[8]);
}
else if (param.Count == 10)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7], param[8], param[9]);
}
else if (param.Count == 11)
{
page = m_db.Page <ABSMgrConn.TableTasks>(pageNum, itemsPerPage, sql, param[0], param[1], param[2], param[3], param[4], param[5], param[6], param[7], param[8], param[9], param[10]);
}
else
{
throw new ApplicationException("GetTasks failed. Parameters' count is incorrect.");
}
var tasks = new Page <Task>().Parse(page);
tasks.Items = page.Items.ConvertAll(item => new Task(item)).ToList();
return(tasks);
}
public void ResetBindings(bool executeQuery)
{
Debug.Assert(SelectedQuery != null);
if (executeQuery)
{
_page = _database.Page(CurrentPage, ShowEntriesValue, SelectedQuery, BonusCalculation);
}
if (_page == null)
{
return;
}
// halt binding source updates
_historyBindingSource.RaiseListChangedEvents = false;
_historyList.RaiseListChangedEvents = false;
// refresh the underlying binding list
RefreshHistoryList(_page.Items);
// sort the list
_historyBindingSource.Sort = null;
if (!String.IsNullOrEmpty(SortColumnName))
{
_historyBindingSource.Sort = SortColumnName + " " + SortOrder.ToDirectionString();
}
// enable binding source updates
_historyBindingSource.RaiseListChangedEvents = true;
_historyList.RaiseListChangedEvents = true;
// reset AFTER RaiseListChangedEvents is enabled
_historyBindingSource.ResetBindings(false);
OnPropertyChanged("TotalEntries");
OnPropertyChanged("CurrentPage");
}
