public bool DeleteByContest()
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.NVarchar, "ContestsId", FieldTypes.Criterion, ContestsId, "=");
return(dao.DoDelete(_col));
}
public static async Task <DataCollections <T> > PagedAsync <T>(
this IQueryable <T> query,
int page,
int take
) where T : class
{
var result = new DataCollections <T>();
result.Total = await query.CountAsync();
result.Page = page;
if (result.Total > 0)
{
result.Pages = Convert.ToInt32(
Math.Ceiling(
Convert.ToDecimal(result.Total) / take
)
);
result.Items = await query.Skip((page - 1) *take)
.Take(take)
.ToListAsync();
}
return(result);
}
public DataTable LoadByUserID(int filter)
{
int iResult = 1;
DataCollections col = new DataCollections("V_" + TABLE_NAME);
col.Add(DataTypes.NVarchar, "*", FieldTypes.DefaultValue, "", "");
col.Add(DataTypes.NVarchar, "UserId", FieldTypes.Criterion, CGlobal.GetUserID(), "=");
switch (filter)
{
case 1: // dc phe duyet
col.Add(DataTypes.Number, "IsAccepted", FieldTypes.Criterion, 1, "=");
// col.FILTER = " DateAccept IS NOT NULL";
col.ORDERBY = " ORDER BY DateAccept desc";
break;
case 2: //chua phe duyet
col.Add(DataTypes.Number, "IsAccepted", FieldTypes.Criterion, 0, "=");
// col.FILTER = " DateAccept IS NULL";
col.ORDERBY = " ORDER BY DateRequest desc";
break;
default: // dang hoc
col.Add(DataTypes.DateTime, "EndTime", FieldTypes.Criterion, DateTime.Now, ">");
col.Add(DataTypes.DateTime, "StartTime", FieldTypes.Criterion, DateTime.Now, "<");
// col.FILTER = " DateAccept IS NOT NULL";
col.Add(DataTypes.Number, "IsAccepted", FieldTypes.Criterion, 1, "=");
col.ORDERBY = " ORDER BY DateAccept desc";
break;
}
return(dao.DoQuery(col, ref iResult));
}
public DataTable exeSelectPro(DataCollections _DataCol, String _Pro_Name)
{
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _Pro_Name;
SqlParameter myParam;
for (int i = 0; i < _DataCol.Count(); ++i)
{
myParam = new SqlParameter();
myParam.ParameterName = _DataCol[i].DataField;
myParam.Value = _DataCol[i].DataValue;
cmd.Parameters.Add(myParam);
}
SqlDataAdapter dA = new SqlDataAdapter(cmd);
DataSet dS = new DataSet();
dA.Fill(dS);
return(dS.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
else
{
return(new DataTable());
}
}
public int exeInsertProWithoutID(DataCollections _DataCol, String _Pro_Name)
{
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _Pro_Name;
SqlParameter myParam;
for (int i = 0; i < _DataCol.Count(); ++i)
{
myParam = new SqlParameter();
myParam.ParameterName = _DataCol[i].DataField;
myParam.Value = _DataCol[i].DataValue;
cmd.Parameters.Add(myParam);
}
cmd.ExecuteNonQuery();
return(1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(-1);
}
}
else
{
return(-1);
}
}
public DataTable DoQuery(DataCollections _DataCol, ref int iResult)
{
String sSQL;
sSQL = ToSQLString(QueryTypes.SelectQuery, _DataCol);
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sSQL;
cmd.Connection = Sqlcon;
if (hasTransaction == true)
{
cmd.Transaction = objTransaction;
}
SqlDataAdapter dA = new SqlDataAdapter(cmd);
DataSet dS = new DataSet();
dA.Fill(dS);
iResult = 1;
if (hasTransaction == false)
{
Sqlcon.Close();
}
return(dS.Tables[0]);
}
catch (Exception obj)
{
throw new Exception(obj.Message);
}
}//end of if
return(new DataTable());
}
public bool DoInsert(DataCollections _DataCol)
{
String sSQL;
sSQL = ToSQLString(DatabaseDAO.QueryTypes.Insert, _DataCol);
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
SqlParameter _para = new SqlParameter();
if (hasTransaction == true)
{
cmd.Transaction = objTransaction;
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = sSQL;
cmd.Connection = Sqlcon;
cmd.ExecuteNonQuery();
Sqlcon.Close();
return(true);
}
catch (Exception obj)
{
throw new Exception(obj.Message);
}
}
else
{
return(false);
}
}
/// <summary>
/// return identity value
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public long DoInsertReturnID(DataCollections _DataCol)
{
String sSQL;
object _ReturnObject = null;
sSQL = ToSQLString(DatabaseDAO.QueryTypes.Insert, _DataCol);
sSQL += ";SELECT @@IDENTITY AS 'Identity'";
if (ConnectValid() == true)
{
try
{
System.Data.SqlClient.SqlCommand _SqlCommand = new System.Data.SqlClient.SqlCommand(sSQL, Sqlcon);
if (hasTransaction == true)
{
_SqlCommand.Transaction = objTransaction;
}
_ReturnObject = _SqlCommand.ExecuteScalar();
_SqlCommand.Dispose();
_SqlCommand = null;
if (hasTransaction == false)
{
Sqlcon.Close();
}
return(long.Parse(_ReturnObject.ToString()));
}
catch (Exception obj)
{
throw new Exception(obj.Message);
}
}
else
{
return(0);
}
}
private DataCollections GetObject()
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.Number, "ContestId", FieldTypes.DefaultValue, ContestId, "");
return(_col);
}
//[ValidateInput(false)] TODO: need this?
public ViewResult Index(FormCollection collection)
{
// TODO: FormCollection can't be used as a parameter
SaveFormValuesToSettings(Request.Form);
ViewData[TimeZonesKey] = DataCollections.TimeZones();
return(View(_settingsManager.Current));
}
private DataCollections GetObject()
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.NVarchar, "Name", FieldTypes.DefaultValue, Name, "");
_col.Add(DataTypes.NVarchar, "Description", FieldTypes.DefaultValue, Description, "");
if (StartTime > DateTime.MinValue)
{
_col.Add(DataTypes.DateTime, "StartTime", FieldTypes.DefaultValue, StartTime, "");
}
else
{
_col.Add(DataTypes.Null, "StartTime", FieldTypes.DefaultValue, DataTypes.Null, "");
}
if (EndTime > DateTime.MinValue)
{
_col.Add(DataTypes.DateTime, "EndTime", FieldTypes.DefaultValue, EndTime, "");
}
else
{
_col.Add(DataTypes.Null, "EndTime", FieldTypes.DefaultValue, DataTypes.Null, "");
}
if (!string.IsNullOrEmpty(ContestPassword) && !string.IsNullOrWhiteSpace(ContestPassword))
{
_col.Add(DataTypes.NVarchar, "ContestPassword", FieldTypes.DefaultValue, ContestPassword, "");
}
_col.Add(DataTypes.NVarchar, "IsVisible", FieldTypes.DefaultValue, IsVisible, "");
_col.Add(DataTypes.Number, "OrderBy", FieldTypes.DefaultValue, OrderBy, "");
_col.Add(DataTypes.NVarchar, "Url", FieldTypes.DefaultValue, Url, "");
return(_col);
}
public DataCollection AddDataCollection(
IEnumerable <IFreeDocument> source, string collectionName = null, bool isCover = false)
{
if (collectionName == null)
{
collectionName = "数据集" + DateTime.Now.ToShortTimeString();
}
DataCollection collection = DataCollections.FirstOrDefault(d => d.Name == collectionName);
if (collection != null)
{
if (!isCover)
{
foreach (IFreeDocument computeable in source)
{
collection.ComputeData.Add(computeable);
}
collection.OnPropertyChanged("Count");
}
else
{
XLogSys.Print.Warn(collectionName + "数据源已经存在，不进行覆盖，没有保存");
}
return(collection);
}
var data = new DataCollection(source.ToList())
{
Name = collectionName
};
DataCollections.Add(data);
return(data);
}
public bool Update()
{
DataCollections _col = GetObject();
_col.Add(DataTypes.NVarchar, "Id", FieldTypes.Criterion, Id, "=");
return(dao.DoUpdate(_col));
}
}//end of method
public bool DoDelete(DataCollections _DataCol)
{
String sSQL;
sSQL = ToSQLString(QueryTypes.Delete, _DataCol);
try
{
if (ConnectValid() == true)
{
SqlCommand cmd = new SqlCommand();
if (hasTransaction == true)
{
cmd.Transaction = objTransaction;
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = sSQL;
cmd.Connection = Sqlcon;
cmd.ExecuteNonQuery();
if (hasTransaction == false)
{
Sqlcon.Close();
}
return(true);
}
else
{
return(false);
}
}
catch (Exception obj)
{
throw new Exception(obj.Message);
}
}
public bool UpdateById()
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.NVarchar, "Id", FieldTypes.Criterion, Id, "=");
return(dao.DoUpdate(_col));
}
public DataTable GetCOUNT_SUBMIT()
{
DataCollections col = new DataCollections("P_GET_COUNT_SUBMIT_BY_USERID");
col.Add(DataTypes.Number, "@userid", FieldTypes.Criterion, CGlobal.GetUserID(), "=");
return(dao.exeSelectPro(col, "P_GET_COUNT_SUBMIT_BY_USERID"));
}
public bool DeleteByProblem(long p_id)
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.Number, "ProblemsId", FieldTypes.Criterion, p_id, "=");
return(dao.DoDelete(_col));
}
public DataTable LoadByUserID()
{
DataCollections _col = new DataCollections("P_AllContest");
_col.Add(DataTypes.NVarchar, "UserId", FieldTypes.Criterion, CGlobal.GetUserID(), "=");
return(dao.exeSelectPro(_col, "P_AllContest"));
}
public bool CheckIsAvailable(long contestID)
{
int iResult = 1;
DataCollections col = new DataCollections(TABLE_NAME);
col.Add(DataTypes.Number, "ID", FieldTypes.Criterion, contestID, "=");
col.Add(DataTypes.DateTime, "StartTime", FieldTypes.DefaultValue, "", "");
col.Add(DataTypes.DateTime, "EndTime", FieldTypes.DefaultValue, "", "");
DataTable dt = dao.DoQuery(col, ref iResult);
try
{
DateTime startTIme = DateTime.Parse(dt.Rows[0]["StartTime"].ToString());
DateTime endTime = DateTime.Parse(dt.Rows[0]["EndTime"].ToString());
DateTime now = DateTime.Now;
if (startTIme > now)
{
return(false);
}
if (endTime < now)
{
return(false);
}
return(true);
}
catch
{
//start time, end time null thì cho phép
return(true);
}
}
public DataTable LoadBySearch(long caseToSearch)
{
int iResult = 1;
DataCollections col = new DataCollections("V_" + TABLE_NAME);
col.Add(DataTypes.NVarchar, "*", FieldTypes.DefaultValue, "", "");
switch (caseToSearch)
{
case 0: // isvisible = 1
col.Add(DataTypes.Number, "IsVisible", FieldTypes.Criterion, 1, "=");
break;
case 1: //isvisible = 0
col.Add(DataTypes.Number, "IsVisible", FieldTypes.Criterion, 0, "=");
break;
case 2: // all
break;
default:
break;
}
return(dao.DoQuery(col, ref iResult));
}
public bool Delete(long p_id)
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.NVarchar, "Id", FieldTypes.Criterion, p_id, "=");
return(dao.DoDelete(_col));
}
public long Insert()
{
DataCollections _col = GetObject();
_col.Add(DataTypes.Number, "UserId", FieldTypes.DefaultValue, CGlobal.GetUserID(), "");
_col.Add(DataTypes.DateTime, "DateRequest", FieldTypes.DefaultValue, DateTime.Now, "");
return(dao.DoInsertReturnID(_col));
}
public DataTable LoadAll()
{
int iResult = 1;
DataCollections col = new DataCollections("V_" + TABLE_NAME);
col.Add(DataTypes.NVarchar, "*", FieldTypes.DefaultValue, "", "");
return(dao.DoQuery(col, ref iResult));
}
public bool Update()
{
DataCollections _col = GetObject();
_col.Add(DataTypes.NVarchar, "Id", FieldTypes.Criterion, Id, "=");
_col.Add(DataTypes.DateTime, "ModifiedOn", FieldTypes.DefaultValue, DateTime.Now.Date, "");
return(dao.DoUpdate(_col));
}
public long Insert()
{
DataCollections _col = GetObject();
_col.Add(DataTypes.Number, "CreatedBy", FieldTypes.DefaultValue, CGlobal.GetUserID(), "");
_col.Add(DataTypes.DateTime, "ModifiedOn", FieldTypes.DefaultValue, DateTime.Now.Date, "");
return(dao.DoInsertReturnID(_col));
}
public bool UpdateNotAcepted()
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.NVarchar, "Id", FieldTypes.Criterion, Id, "=");
_col.Add(DataTypes.Number, "IsAccepted", FieldTypes.DefaultValue, 0, "");
return(dao.DoUpdate(_col));
}
public bool UpdatePointByID(long point)
{
DataCollections _col = GetObject();
_col.Add(DataTypes.Number, "ID", FieldTypes.Criterion, CGlobal.GetUserID(), "=");
_col.Add(DataTypes.Number, "Point", FieldTypes.DefaultValue, point, "");
return(dao.DoUpdate(_col));
}
// do work không thể gọi đc getUserID()
public bool UpdatePoint(long point, long userID)
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.Number, "Id", FieldTypes.Criterion, userID, "=");
_col.Add(DataTypes.Number, "Point", FieldTypes.DefaultValue, point, "");
return(dao.DoUpdate(_col));
}
public DataCollection GetCollection(string name)
{
if (DataCollections.Count == 0)
{
return(null);
}
return(string.IsNullOrWhiteSpace(name) ? DataCollections.FirstOrDefault() : DataCollections.FirstOrDefault(rc => rc.Name == name));
}
public bool Update(long _id, int _processType)
{
DataCollections _col = new DataCollections(TABLE_NAME);
_col.Add(DataTypes.Number, "Id", FieldTypes.Criterion, _id, "=");
_col.Add(DataTypes.Number, "ProcessType", FieldTypes.DefaultValue, _processType, "");
return(dao.DoUpdate(_col));
}
