/// <summary>
/// add number
/// </summary>
/// <param name="_code"></param>
/// <param name="_newVal"></param>
public void add(string _code, string _newVal)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var r = DB.tblStatistics.Single(p => p.Code == _code);
long oldVal = long.Parse(r.Value);
long newVal = long.Parse(_newVal);
if (oldVal > 0 || newVal > 0)
{
oldVal += newVal;
}
r.Value = oldVal.ToString();
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
}
}
/// <summary>
/// change state of function is ON
/// </summary>
/// <param name="_code"></param>
/// <param name="_username"></param>
/// <returns></returns>
public bool changeStateON(string _code, string _reason, string _username)
{
try
{
if (_reason.Length >= 500)
{
_reason = _reason.Substring(0, 499);
}
using (TransactionScope ts = new TransactionScope())
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
var record = DB.tblAdmins.Single(p => p.Code == _code);
record.State = true;
record.Reason = _reason.Trim();
DB.SubmitChanges();
ts.Complete();
log.writeLog(DBHelper.strPathLogFile,
_username,
BaseServices.createMsgByTemplate(CommonConstants.SQL_CHANGE_STATE_ON,
_code));
}
}
catch (Exception ex)
{
log.writeLog(DBHelper.strPathLogFile, ex.Message);
return false;
}
return true;
}
/// <summary>
/// Lấy bài dựa theo id
/// </summary>
/// <param name="_id"></param>
/// <returns></returns>
public static tblEnglish getEnglish(int _id)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = from record in DB.tblEnglishes
where record.ID == _id
select record;
if (lst.Count() > 0)
{
return lst.ElementAt(0);
}
return null;
}
public void addLatestLoginUser(string username)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
username = BaseServices.nullToBlank(username);
using (TransactionScope ts = new TransactionScope())
{
var r = DB.tblStatistics.Single(p => p.Code == CommonConstants.SF_LATEST_LOGIN);
if (!BaseServices.isNullOrBlank(r.Value))
{
string[] arrayUser = r.Value.Split(CommonConstants.COMMA_CHAR);
if (arrayUser[0].Contains(username))
return;
if (arrayUser.Length >= 10)
{
r.Value = CommonConstants.BLANK;
for (int i = 0; i < arrayUser.Length - 1; i++)
{
r.Value += arrayUser[i];
r.Value += CommonConstants.COMMA;
}
r.Value = r.Value.Substring(0, r.Value.Length - 1);
}
if (!r.Value.StartsWith(CommonConstants.COMMA))
{
username += CommonConstants.COMMA;
}
username += r.Value.Trim();
r.Value = username;
}
else
{
r.Value = username;
}
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
}
}
/// <summary>
/// Lấy tag
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static string getTag(int ID)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = from record in DB.tblEnglishes
where record.ID == ID
select record;
if (lst.Count() > 0)
{
return lst.ElementAt(0).Tag.Trim();
}
return CommonConstants.BLANK;
}
/// <summary>
/// set new value
/// </summary>
/// <param name="_code"></param>
/// <param name="_value"></param>
public void setValue(string _code, string _value)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var r = DB.tblStatistics.Single(p => p.Code == _code);
r.Value = _value;
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
}
}
public void resetToDefault(string _code)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var r = DB.tblStatistics.Single(p => p.Code == _code);
if (BaseServices.nullToBlank(_code).StartsWith("NUM"))
{
r.Value = "0";
}
else
{
r.Value = CommonConstants.BLANK;
}
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
}
}
/// <summary>
/// Lấy điểm checker
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public int getScore(int ID)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = from record in DB.tblEnglishes
where record.ID == ID
select record;
if (lst.Count() > 0)
{
return (int)lst.ElementAt(0).Score;
}
return 0;
}
/// <summary>
/// Xóa 1 record quảng cáo
/// </summary>
/// <param name="_id"></param>
/// <param name="_username"></param>
/// <returns></returns>
public bool deleteAds(int _id, string _username)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var ads = DB.tblAdvertisements.Single(a => a.ID == _id);
DB.tblAdvertisements.DeleteOnSubmit(ads);
DB.SubmitChanges();
ts.Complete();
log.writeLog(DBHelper.strPathLogFile, _username,
BaseServices.createMsgByTemplate (CommonConstants.SQL_DELETE_SUCCESSFUL_TEMPLATE,
_id.ToString(),
CommonConstants.SQL_TABLE_ADVERTISEMENT));
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, _username,
BaseServices.createMsgByTemplate(CommonConstants.SQL_DELETE_FAILED_TEMPLATE,
_id.ToString(),
CommonConstants.SQL_TABLE_ADVERTISEMENT));
log.writeLog(DBHelper.strPathLogFile, _username, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
return false;
}
return true;
}
public int countAdsListByLocation()
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
int num = (from record in DB.tblAdvertisements
where record.Code.Trim() != CommonConstants.ADS_INACTIVE
select record).Count();
return num;
}
public IEnumerable<tblEnglish> searchArticleByClassAndTime(ArticleSCO articleSCO)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
int start = 0;
int end = 0;
int year = BaseServices.getYearFromString(articleSCO.Time);
IEnumerable<tblEnglish> lst = null;
//if (articleSCO.Classes == CommonConstants.PARAM_EL_COMMON)
//{
// start = CommonConstants.AT_EL_CLASS_START;
// end = CommonConstants.AT_EL_CLASS_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_MAJOR)
//{
// start = CommonConstants.AT_EL_MJ_START;
// end = CommonConstants.AT_EL_MJ_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_CERT)
//{
// start = CommonConstants.AT_EL_CERT_START;
// end = CommonConstants.AT_EL_CERT_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_CLASS_1_TO_9)
//{
// start = CommonConstants.AT_EL_CLASS_1;
// end = CommonConstants.AT_EL_CLASS_9;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_MATH_ECO)
//{
// start = CommonConstants.AT_EL_MJ_MATH;
// end = CommonConstants.AT_EL_MJ_ECO;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_CHEM_BIO_MAT)
//{
// start = CommonConstants.AT_EL_MJ_CHEM;
// end = CommonConstants.AT_EL_MJ_MATERIAL;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_PHY_TELE_IT)
//{
// start = CommonConstants.AT_EL_MJ_PHY;
// end = CommonConstants.AT_EL_MJ_IT;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_OTHER_MJ)
//{
// start = CommonConstants.AT_EL_MJ_IT + 1;
// end = CommonConstants.AT_EL_MJ_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_TOEIC)
//{
// start = CommonConstants.AT_EL_CERT_TOEIC_START;
// end = CommonConstants.AT_EL_CERT_TOEIC_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_TOEFL)
//{
// start = CommonConstants.AT_EL_CERT_TOEFL_START;
// end = CommonConstants.AT_EL_CERT_TOEFL_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_IELTS)
//{
// start = CommonConstants.AT_EL_CERT_IELTS_START;
// end = CommonConstants.AT_EL_CERT_IELTS_END;
//}
//else if (articleSCO.Classes == CommonConstants.PARAM_EL_ABC)
//{
// start = CommonConstants.AT_EL_CERT_ABC_START;
// end = CommonConstants.AT_EL_CERT_ABC_END;
//}
locateArticleIndex(articleSCO, out start, out end);
if (start > 0 && end > 0 && end >= start)
{
lst = (from p in DB.tblEnglishes
where p.Posted.Year <= year
&& p.State != CommonConstants.STATE_UNCHECK
&& p.StickyFlg == false
&& p.Class >= start
&& p.Class <= end
&& p.DeleteFlg == false
orderby p.Posted descending
select p).Skip(articleSCO.FirstRecord).Take(articleSCO.NumArticleOnPage);
}
else if (articleSCO.Classes == CommonConstants.ALL)
{
lst = (from p in DB.tblEnglishes
where p.Posted.Year <= year
&& p.State != CommonConstants.STATE_UNCHECK
&& p.StickyFlg == false
&& p.DeleteFlg == false
orderby p.Posted descending
select p).Skip(articleSCO.FirstRecord).Take(articleSCO.NumArticleOnPage);
}
return lst;
}
public Boolean Like(int _id)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var english = DB.tblEnglishes.Single(e => e.ID == _id);
english.Point += 1;
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
return false;
}
return true;
}
public IList<tblEnglish> listEnglish(string _keyword)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = from record in DB.tblEnglishes
where (record.Title.Contains(_keyword) ||
record.Tag.Contains(_keyword) ||
record.Contents.Contains(_keyword))
&& record.DeleteFlg == false
select record;
return lst.ToList();
}
/// <summary>
/// Thêm một bài giảng/bài tập/đề thi anh văn
/// </summary>
/// <param name="_title"></param>
/// <param name="_type"></param>
/// <param name="_content"></param>
/// <param name="_author"></param>
/// <param name="_posted"></param>
/// <param name="_location"></param>
/// <param name="_tag"></param>
/// <returns></returns>
public Boolean insertEnglish(string _title, int _type, string _content,
string _author, DateTime _posted, int _class, string _location, string _tag, string folderId)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
tblEnglish record = new tblEnglish();
record.Title = _title;
record.Type = _type;
record.Contents = _content;
record.Author = _author;
record.Posted = _posted;
record.Class = _class;
record.Location = _location;
record.State = 0;
record.Point = 0;
record.Score = 0;
record.Tag = _tag;
record.StickyFlg = false;
record.Class = _class;
record.FolderID = folderId;
record.DeleteFlg = false;
DB.tblEnglishes.InsertOnSubmit(record);
DB.SubmitChanges();
ts.Complete();
}
ltktDAO.Statistics statisticDAO = new ltktDAO.Statistics();
statisticDAO.add(CommonConstants.SF_NUM_UPLOAD, CommonConstants.CONST_ONE);
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, _author, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
return false;
}
return true;
}
/// <summary>
/// Thêm một bài viết mới
/// </summary>
/// <param name="record"></param>
/// <returns></returns>
public Boolean insertEnglish(tblEnglish record)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
DB.tblEnglishes.InsertOnSubmit(record);
DB.SubmitChanges();
ts.Complete();
}
ltktDAO.Control controlDao = new ltktDAO.Control();
long keyCode = controlDao.getValueByLong(CommonConstants.CF_KEY_CODE_EL);
controlDao.setValue(CommonConstants.CF_KEY_CODE_EL, (keyCode + 1).ToString());
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, record.Author, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
return false;
}
return true;
}
public Boolean insertComment(int _id, string _newComment)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var english = DB.tblEnglishes.Single(e => e.ID == _id);
english.Comment += _newComment;
english.Comment += "<br /><br />;";
DB.SubmitChanges();
ts.Complete();
//Cho cái này vào bị lỗi liên tục
//2011-09-27 16:09 tktung bỏ
//ltktDAO.Statistics statDAO = new ltktDAO.Statistics();
//statDAO.add(CommonConstants.SF_NUM_COMMENT_A_DAY, "1");
}
}
catch (Exception e) {
writeException(e);
return false;
}
return true;
}
/// <summary>
/// Tống tất cả các bài viết (ltđh, anh văn, tin học)
/// </summary>
/// <returns></returns>
public int sumArticle()
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
int sumContest = (from contest in DB.tblContestForUniversities
select contest).Count();
int sumEnglish = (from english in DB.tblEnglishes
select english).Count();
int sumInformatics = (from informatics in DB.tblInformatics
select informatics).Count();
return (sumContest + sumEnglish + sumInformatics);
}
/// <summary>
/// Get record of TblAdmin
/// </summary>
/// <param name="_id"></param>
/// <returns></returns>
public IEnumerable<tblAdmin> getRecord(string _code)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblAdmin> item = DB.tblAdmins.Where(p => p.Code == _code);
return item;
}
/// <summary>
/// get max point article
/// </summary>
/// <returns></returns>
public tblEnglish getMaxPoint()
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = from p in DB.tblEnglishes
where p.Point == DB.tblEnglishes.Max(p2 => p2.Point)
&& p.DeleteFlg == false
orderby p.Posted descending
select p;
if (lst.Count() > 0)
{
return lst.ElementAt(BaseServices.random(0, lst.Count() - 1));
}
return null;
}
public void setReason(string _code, string _reason)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
var record = DB.tblAdmins.Single(p => p.Code == _code);
record.Reason = _reason.Trim();
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception ex)
{
log.writeLog(DBHelper.strPathLogFile, ex.Message);
}
}
/// <summary>
/// Tổng số quảng cáo
/// </summary>
/// <returns></returns>
public int countAds()
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
return (from record in DB.tblAdvertisements select record).Count();
}
public IEnumerable<tblAdvertisement> fetchAdsListByLocation(int start, int count)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblAdvertisement> lst = (from record in DB.tblAdvertisements
where record.Code.Trim() != CommonConstants.ADS_INACTIVE
orderby record.toDate descending
select record).Skip(start).Take(count);
return lst;
}
public int countAdsListByState(int state)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
int num = (from record in DB.tblAdvertisements
where record.State == state
select record).Count();
return num;
}
public void resetState(string _code)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
try
{
using (TransactionScope ts = new TransactionScope())
{
var ads = DB.tblAdvertisements.Single(a => a.Code == _code);
ads.Code = CommonConstants.ADS_INACTIVE;
DB.SubmitChanges();
ts.Complete();
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
}
}
public IEnumerable<tblAdvertisement> fetchAdsList(int state, int start, int count)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblAdvertisement> lst = (from record in DB.tblAdvertisements
where record.State == state
orderby record.toDate descending
select record).Skip(start).Take(count);
return lst;
}
/// <summary>
/// Lấy bài viết liên quan theo loại
/// </summary>
/// <param name="_type"></param>
/// <returns></returns>
public IList<tblEnglish> getRelativeByType(int _type, int _numberRecords)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblEnglish> lst = (from record in DB.tblEnglishes
where record.Type == _type
&& record.DeleteFlg == false
select record).Take(_numberRecords);
return lst.ToList();
}
public ArrayList getUnfreeLocationList()
{
ArrayList lst = new ArrayList();
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
if (isExisted(CommonConstants.ADS_TOP_BANNER))
{
lst.Add(CommonConstants.ADS_TOP_BANNER);
}
if (isExisted(CommonConstants.ADS_TOP_LEADER_BANNER))
{
lst.Add(CommonConstants.ADS_TOP_LEADER_BANNER);
}
if (isExisted(CommonConstants.ADS_TOP_RIGHT_BANNER))
{
lst.Add(CommonConstants.ADS_TOP_RIGHT_BANNER);
}
if (isExisted(CommonConstants.ADS_MIDDLE_RIGHT_BANNER))
{
lst.Add(CommonConstants.ADS_MIDDLE_RIGHT_BANNER);
}
if (isExisted(CommonConstants.ADS_BOTTOM_RIGHT_BANNER))
{
lst.Add(CommonConstants.ADS_BOTTOM_RIGHT_BANNER);
}
if (isExisted(CommonConstants.ADS_TOP_LEADER_BANNER))
{
lst.Add(CommonConstants.ADS_TOP_LEADER_BANNER);
}
if (isExisted(CommonConstants.ADS_MIDDLE_LEFT_BANNER))
{
lst.Add(CommonConstants.ADS_MIDDLE_LEFT_BANNER);
}
if (isExisted(CommonConstants.ADS_BOTTOM_LEFT_BANNER))
{
lst.Add(CommonConstants.ADS_BOTTOM_LEFT_BANNER);
}
if (isExisted(CommonConstants.ADS_BOTTOM_1_BANNER))
{
lst.Add(CommonConstants.ADS_BOTTOM_1_BANNER);
}
if (isExisted(CommonConstants.ADS_BOTTOM_2_BANNER))
{
lst.Add(CommonConstants.ADS_BOTTOM_2_BANNER);
}
return lst;
}
/// <summary>
/// Get all record
/// </summary>
/// <returns></returns>
public IEnumerable<tblAdmin> getAll()
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
IEnumerable<tblAdmin> items = from p in DB.tblAdmins
select p;
return items;
}
public int cloneAds(string _companyName,
string _address,
string _email,
string _phone,
DateTime _from,
DateTime _end,
string _location,
string _navigateUrl,
string _description)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
int id = -1;
try
{
using (TransactionScope ts = new TransactionScope())
{
tblAdvertisement record = new tblAdvertisement();
record.Company = _companyName;
record.Address = _address;
record.Email = _email;
record.Phone = _phone;
record.fromDate = _from;
record.toDate = _end;
record.Price = 0;
record.FilePath = CommonConstants.BLANK;
record.Location = _location;
record.Code = CommonConstants.ADS_INACTIVE;
record.ClickCount = 0;
record.NavigateUrl = _navigateUrl;
record.FilePath = CommonConstants.BLANK;
record.Description = _description;
record.Size = CommonConstants.DEFAULT_ADS_IMG_SIZE;
record.State = CommonConstants.STATE_UNCHECK;
DB.tblAdvertisements.InsertOnSubmit(record);
DB.SubmitChanges();
ts.Complete();
id = record.ID;
log.writeLog(DBHelper.strPathLogFile,
BaseServices.createMsgByTemplate(CommonConstants.SQL_INSERT_SUCCESSFUL_TEMPLATE,
record.ID.ToString(),
CommonConstants.SQL_TABLE_ADVERTISEMENT));
}
}
catch (Exception e)
{
log.writeLog(DBHelper.strPathLogFile, e.Message
+ CommonConstants.NEWLINE
+ e.Source
+ CommonConstants.NEWLINE
+ e.StackTrace
+ CommonConstants.NEWLINE
+ e.HelpLink);
return -1;
}
return id;
}
public tblStatistic getRecord(string Code)
{
LTDHDataContext DB = new LTDHDataContext(@strPathDB);
var p = DB.tblStatistics.Single(r => r.Code == Code.Trim());
return p;
}