// POST: api/CheckTotalRecord
public async Task <IHttpActionResult> Get(string StartDateTime)
{
VerityResult result = new VerityResult();
try
{
SearchTime modelTime = new SearchTime
{
StartDateTime = DateTime.Parse(StartDateTime),
};
var totalRecord = await _totalRecord.CheckTotalRecord(modelTime);
result.Success = true;
result.Message = "更新彙總紀錄成功";
return(new ResponseMessageResult(Request.CreateResponse(HttpStatusCode.OK, result)));
}
catch (Exception ex)
{
result.Success = false;
result.Message = JsonConvert.SerializeObject(ex);
return(new ResponseMessageResult(Request.CreateResponse(HttpStatusCode.InternalServerError, result)));
throw;
}
}
/// <summary>
/// 編輯打卡紀錄的時間
/// </summary>
/// <param name="model"></param>
/// <param name="time"></param>
/// <returns></returns>
public async Task <JsonResult> SubmitClick_In(CheckRecordViewModel model, UpdateCheckTimeViewModel time)
{
ResponseViewModel res = new ResponseViewModel();
try
{
if (time != null)
{
if (time.ci_ut != "-")
{
model.ci_ut = TimeSpan.Parse(time.ci_ut);
}
if (time.ci_dt != "-")
{
model.ci_dt = TimeSpan.Parse(time.ci_dt);
}
var result = await _checkRecord.SubmitClick_In(model);
SearchTime modelTime = new SearchTime
{
StartDateTime = DateTime.Parse(model.ci_da.ToString("s")),
};
var totalRecord = _totalRecord.CheckTotalRecord(modelTime);
res.Success = result.Success;
res.Message = result.Message;
res.HttpStatusCode = System.Net.HttpStatusCode.OK;
}
}
catch (Exception ex)
{
res.Exception = ex;
res.Success = false;
res.Message = "與伺服器連接發生錯誤";
res.HttpStatusCode = System.Net.HttpStatusCode.InternalServerError;
}
res.ResponseTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
return(Json(res, JsonRequestBehavior.AllowGet));
}
public int CompareTo(object obj)
{
SourceCodeForDisPlay sobj = (SourceCodeForDisPlay)obj;
return(SearchTime.CompareTo(sobj.SearchTime));
}
/// <summary>
/// 更新彙整記錄
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public async Task <VerityResult> CheckTotalRecord(SearchTime searchModel)
{
VerityResult res = new VerityResult();
List <check_all> check_All = new List <check_all>();
try
{
DateTime nowDate = DateTime.Now;
var year = searchModel.StartDateTime.Year;
var month = searchModel.StartDateTime.Month;
var days = 0;
if (nowDate.Year == year && nowDate.Month == month)
{
days = nowDate.Day;
}
else
{
days = DateTime.DaysInMonth(year, month);
}
int weekDays = 0;
for (int i = 0; i < days; i++)
{
// 判断是否為周六、周日
switch (searchModel.StartDateTime.DayOfWeek)
{
case DayOfWeek.Saturday:
weekDays++;
break;
case DayOfWeek.Sunday:
weekDays++;
break;
}
searchModel.StartDateTime = searchModel.StartDateTime.AddDays(1);
}
// 工作日
int workDays = days - weekDays;
var checkIn = from a in _check_in.GetAll()
join c in _user.GetAll() on a.ur_id equals c.ur_id
where a.ca_mo == month && a.ci_ut != TimeSpan.Zero && a.ci_dt != TimeSpan.Zero
group a by new
{
a.ur_id,
a.ca_mo,
ca_ya = a.ci_da.Year,
} into ga
select new Check_AllViewModel
{
ur_id = ga.Key.ur_id,
ca_ya = ga.Key.ca_ya,
ca_mo = ga.Key.ca_mo,
ca_dy = workDays,
ca_on = ga.Count(),
ca_hr = ga.Sum(x => x.ci_hr)
};
if (checkIn.Any())
{
foreach (var item in checkIn.ToList())
{
check_all check_All_Item = new check_all();
var Find_check_all = _check_all.FindBy(x => x.ur_id == item.ur_id && x.ca_mo == item.ca_mo).ToList();
if (Find_check_all.Any())
{
check_All_Item = Find_check_all.FirstOrDefault();
check_All_Item.ca_dy = item.ca_dy;
check_All_Item.ca_on = item.ca_on;
check_All_Item.ca_hr = item.ca_hr;
check_All.Add(check_All_Item);
_check_all.Update(check_All_Item);
}
else
{
check_All_Item.ur_id = item.ur_id;
check_All_Item.ca_ya = item.ca_ya;
check_All_Item.ca_mo = item.ca_mo;
check_All_Item.ca_dy = item.ca_dy;
check_All_Item.ca_on = item.ca_on;
check_All_Item.ca_hr = item.ca_hr;
check_All.Add(check_All_Item);
_check_all.Create(check_All_Item);
}
}
res.Success = true;
res.Message = "更新打卡紀錄成功";
}
}
catch
{
res.Success = false;
res.Message = "更新打卡紀錄失敗";
}
return(await Task.Run(() => res));
}
private string QueryBuilder(string query, SearchType sType, string sCategory, SearchTime sTime)
{
string queryString = string.Empty;
string tableClause = string.Empty;
string columnClause = string.Empty;
string categoryClause = string.Empty;
string timeClause = string.Empty;
string dateRange = string.Empty;
switch (sType)
{
case SearchType.All:
tableClause = "item";
break;
case SearchType.Comments:
tableClause = "comments";
break;
case SearchType.Links:
tableClause = "item";
break;
case SearchType.Title:
tableClause = "item";
break;
default:
tableClause = "item";
break;
}
switch (sType)
{
case SearchType.All:
columnClause = "Title";
break;
case SearchType.Comments:
columnClause = "Comment";
break;
case SearchType.Links:
columnClause = "Link";
break;
case SearchType.Title:
columnClause = "Title";
break;
default:
columnClause = "Title";
break;
}
int days = 0;
switch (sTime)
{
case SearchTime.Month:
days = 30;
break;
case SearchTime.Today:
days = 1;
break;
case SearchTime.Week:
days = 7;
break;
case SearchTime.Year:
days = 365;
break;
default:
days = 7;
break;
}
DateTime rangeDT = DateTime.Now.Subtract(new TimeSpan(days, 0, 0, 0));
timeClause = rangeDT.ToString("yyyy-MM-dd HH:mm:ss");
string categoryExpression = string.Empty;
if (sType == SearchType.Comments)
{
// SELECT * FROM comments where IID in (Select IID From item where category = 'business');
if (sCategory != "All Categories")
{
categoryExpression = " IID IN (SELECT IID FROM item WHERE Category = '" + sCategory + "') " + " AND ";
}
queryString = "SELECT * FROM " + tableClause + " WHERE " + categoryExpression + columnClause + " LIKE '%" + query + "%' " + " AND " + " Date > DATE_FORMAT('" + timeClause + "', '%Y-%m-%d %k:%i:%S')" + " LIMIT " + _maxSearchResults + ";";
}
else
{
if (sCategory != "All Categories")
{
categoryExpression = " Category = '" + sCategory + "'" + " AND ";
}
// string queryString = "SELECT * FROM item WHERE Title LIKE '%" + query + "%' LIMIT " + _maxSearchResults + ";";
queryString = "SELECT * FROM " + tableClause + " WHERE " + categoryExpression + columnClause + " LIKE '%" + query + "%' " + " AND " + " Date > DATE_FORMAT('" + timeClause + "', '%Y-%m-%d %k:%i:%S')" + " LIMIT " + _maxSearchResults + ";";
}
return queryString;
}
public List<Item> LoadSearchResults(string query, SearchType sType, string sCategory, SearchTime sTime)
{
List<Item> itemList = new List<Item>();
List<string> queryTokens = tokenizer.GetTokens(query, true, true);
List<int> alreadyAddedIIDs = new List<int>();
if (queryTokens != null)
{
for (int i = 0; i < queryTokens.Count; i++)
{
string currentQueryToken = queryTokens[i];
string queryString = QueryBuilder(currentQueryToken, sType, sCategory, sTime);
Logger log = Logger.Instance;
log.Log("Search QueryString: " + queryString);
// queryString = "SELECT * FROM item WHERE Title LIKE '%" + query + "%' LIMIT " + _maxSearchResults + ";";
// string queryString = "SELECT * FROM item WHERE Title LIKE '%" + query + "%' LIMIT " + _maxSearchResults + ";";
MySqlDataReader retList = dbOps.ExecuteReader(queryString);
retList = dbOps.ExecuteReader(queryString);
if (retList != null && retList.HasRows)
{
while (retList.Read())
{
Item item = new Item();
item.IID = Convert.ToInt32(retList["IID"]);
item.Title = Convert.ToString(retList["Title"]);
item.Link = Convert.ToString(retList["Link"]);
item.Text = Convert.ToString(retList["Text"]);
item.Date = Convert.ToString(retList["Date"]);
item.UID = Convert.ToString(retList["UID"]);
item.NComments = Convert.ToInt32(retList["NComments"]);
item.Category = Convert.ToString(retList["Category"]);
item.Clicks = Convert.ToInt32(retList["Clicks"]);
item.NSaved = Convert.ToInt32(retList["NSaved"]);
item.NEMailed = Convert.ToInt32(retList["NEMailed"]);
item.TagString = Convert.ToString(retList["Tags"]);
item.TagList = general.ConvertCSVToList(item.TagString);
item.AvgRating = Convert.ToDouble(retList["AvgRating"]);
item.NRated = Convert.ToInt32(retList["NRated"]);
item.Age = item.GetAge(item.Date);
// item.Marks = item.GetMarks(item.Clicks, item.NComments, item.Age);
item.Marks = item.GetMarks(item);
if (!string.IsNullOrEmpty(item.Link))
{
item.Text = string.Empty;
}
if (!alreadyAddedIIDs.Contains(item.IID))
{
alreadyAddedIIDs.Add(item.IID);
itemList.Add(item);
}
}
retList.Close();
}
}
return itemList;
// ItemDiv.InnerHtml = LoadItems(itemList);
// LoadItemTable(itemList);
}
else
{
return null;
}
}