public int EnumCount(DefectsFilter f)
{
string where = PrepareQueryForEnum(f, false);
string sql = string.Format("select count({0}) from {1} {2}", _ID, _Tabl, where);
return(Convert.ToInt32(DBHelper.GetValue(sql)));
}
static DefectsFilter UnusedVacations()
{
DefectsFilter f = new DefectsFilter();
f.components = new List <int>(DefectComp.GetVacationRec());
f.dispositions = new List <int>(DefectDispo.EnumCannotStartIDs());
return(f);
}
public StoredDefectsFilter udpateFilter(int id, DefectsFilter filter)
{
CurrentContext.Validate();
StoredDefectsFilter stf = new StoredDefectsFilter(id);
stf.SetFilter(filter);
stf.Store();
return(stf);
}
public static List <DefectBase> EnumCloseVacations(string startdate, int days = 31)
{
DefectsFilter f = new DefectsFilter();
f.components = new List <int>(DefectComp.GetVacationRec());
f.dispositions = DefectDispo.EnumCanStartIDs();
f.startDateEnter = startdate;
f.endDateEnter = DateTime.ParseExact(startdate, IdBasedObject.defDateFormat, CultureInfo.InvariantCulture).AddDays(days).ToString(IdBasedObject.defDateFormat); //two weeks adnvance
return((new DefectBase()).Enum(f, 2000));
}
public DateTime?ModTime(DefectsFilter f)
{
string where = PrepareQueryForEnum(f, false);
object o = GetValue($"SELECT MAX({_ModDate}) FROM {_Tabl} {where}");
if (o == DBNull.Value)
{
return(null);
}
return(Convert.ToDateTime(o));
}
public static List <DefectBase> EnumScheduled(string date, string email)
{
DefectsFilter f = new DefectsFilter();
f.startDateScheduled = date;
f.endDateScheduled = DateTime.ParseExact(date, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(defDateFormat);
f.orderer = email;
DefectBase temp = new DefectBase();
return(temp.Enum(f, 100));
}
public List <DefectBase> Enum(DefectsFilter f, int maxrecs = 400)
{
string where = PrepareQueryForEnum(f, true);
List <DefectBase> ls = new List <DefectBase>();
foreach (DataRow r in GetRecords(where, maxrecs))
{
DefectBase d = new DefectBase();
d.Load(r);
ls.Add(d);
}
return(ls);
}
static public StoredDefectsFilter NewFilter(string name, bool personal, DefectsFilter f, int user)
{
string g = Guid.NewGuid().ToString();
SQLExecute($"INSERT INTO {_Tabl} ({_Nam}, {_Usr}, {_Share}) VALUES ('{g}', '{user}', {(personal ? 0 : 1)})");
int id = Convert.ToInt32(GetValue(string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", _pid, _Tabl, _Nam, g)));
StoredDefectsFilter sf = new StoredDefectsFilter(id)
{
NAME = name,
DATA = Newtonsoft.Json.JsonConvert.SerializeObject(f)
};
sf.Store();
return(sf);
}
public static List <DefectBase> EnumModified(string date, string email)
{
DefectsFilter f = new DefectsFilter();
f.startDateModified = date;
f.endDateModified = DateTime.ParseExact(date, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(defDateFormat);
DefectUser u = DefectUser.FindByEmail(email);
if (u != null)
{
f.modifiedUsers = new List <int>()
{
u.ID
};
}
DefectBase temp = new DefectBase();
return(temp.Enum(f, 100));
}
public void SetFilter(DefectsFilter f)
{
DATA = Newtonsoft.Json.JsonConvert.SerializeObject(f);
}
string PrepareQueryForEnum(DefectsFilter f, bool order)
{
List <string> lswhere = new List <string>();
if (!string.IsNullOrEmpty(f.ID))
{
string swhere = "";
string[] ranges = f.ID.Trim().Split(',');
foreach (string range in ranges)
{
string[] numbers = range.Trim().Split('-');
int val1, val2;
if (numbers.Length == 1 && int.TryParse(numbers[0], out val1))
{
if (!string.IsNullOrEmpty(swhere))
{
swhere += " OR ";
}
swhere += string.Format("({0} = {1})", _ID, val1);
}
else if (numbers.Length == 2 && int.TryParse(numbers[0], out val1) && int.TryParse(numbers[1], out val2))
{
if (!string.IsNullOrEmpty(swhere))
{
swhere += " OR ";
}
swhere += string.Format("(({0} >= {1}) AND ({0} <= {2}))", _ID, Math.Min(val1, val2), Math.Max(val1, val2));
}
}
if (!string.IsNullOrEmpty(swhere))
{
lswhere.Add(string.Format(" AND ({0})", swhere));
}
}
if (f.dispositions != null && f.dispositions.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _Disp, string.Join(",", f.dispositions)));
}
if (f.severities != null && f.severities.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _Seve, string.Join(",", f.severities)));
}
if (f.users != null && f.users.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _AsUser, string.Join(",", f.users)));
}
if (f.createdUsers != null && f.createdUsers.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _CreaBy, string.Join(",", f.createdUsers)));
}
if (f.modifiedUsers != null && f.modifiedUsers.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _ModBy, string.Join(",", f.modifiedUsers)));
}
if (f.components != null && f.components.Count > 0)
{
lswhere.Add(string.Format(" AND ({0} in ({1}))", _Comp, string.Join(",", f.components)));
}
if (!string.IsNullOrEmpty(f.startDateEnter))
{
lswhere.Add(string.Format(" AND ({0} >= '{1}')", _Date, DateTime.ParseExact(f.startDateEnter, defDateFormat, CultureInfo.InvariantCulture).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.endDateEnter))
{
lswhere.Add(string.Format(" AND ({0} < '{1}')", _Date, DateTime.ParseExact(f.endDateEnter, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.startDateCreated))
{
lswhere.Add(string.Format(" AND ({0} >= '{1}')", _Created, DateTime.ParseExact(f.startDateCreated, defDateFormat, CultureInfo.InvariantCulture).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.endDateCreated))
{
lswhere.Add(string.Format(" AND ({0} < '{1}')", _Created, DateTime.ParseExact(f.endDateCreated, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.startDateScheduled))
{
lswhere.Add(string.Format(" AND ({0} >= '{1}')", _OrderDate, DateTime.ParseExact(f.startDateScheduled, defDateFormat, CultureInfo.InvariantCulture).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.endDateScheduled))
{
lswhere.Add(string.Format(" AND ({0} < '{1}')", _OrderDate, DateTime.ParseExact(f.endDateScheduled, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.startDateModified))
{
lswhere.Add(string.Format(" AND ({0} >= '{1}')", _ModDate, DateTime.ParseExact(f.startDateModified, defDateFormat, CultureInfo.InvariantCulture).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.endDateModified))
{
lswhere.Add(string.Format(" AND ({0} < '{1}')", _ModDate, DateTime.ParseExact(f.endDateModified, defDateFormat, CultureInfo.InvariantCulture).AddDays(1).ToString(DBHelper.SQLDateFormat)));
}
if (!string.IsNullOrEmpty(f.orderer))
{
lswhere.Add(string.Format(" AND ({0} = '{1}')", _sMod, f.orderer));
}
if (!string.IsNullOrEmpty(f.startEstim))
{
lswhere.Add(string.Format(" AND ({0} >= '{1}')", _Est, int.Parse(f.startEstim)));
}
if (!string.IsNullOrEmpty(f.endEstim))
{
lswhere.Add(string.Format(" AND ({0} <= '{1}')", _Est, int.Parse(f.endEstim)));
}
if (!string.IsNullOrEmpty(f.text))
{
string[] words;
bool phrase = false;
if (f.text.StartsWith("\"") && f.text.EndsWith("\""))
{
words = new string[] { f.text.Trim('"') };
phrase = true;
}
else
{
words = f.text.Split(null);
}
string s1 = "";
string s2 = "";
foreach (var w in words)
{
s1 += (s1 == "") ? "(" : " AND ";
s1 += $" CONTAINS({_Summ}, '\"{w}\"')";
if (phrase)
{
s1 += $" OR {_Summ} like '%{w}%' ";
}
s2 += (s2 == "") ?
$"{_idRec} IN (SELECT idDefRec FROM {Defect._RepTable} WHERE CONTAINS (({Defect._DescInt}, {Defect._Specs}), '"
: " AND ";
s2 += string.Format("\"{0}\"", w);
}
s1 += " )";
s2 += "'))";
lswhere.Add(string.Format(" AND ({0} OR {1})", s1, s2));
}
return(string.Format(" WHERE (1=1 {0}) {1}", string.Join(string.Empty, lswhere), order ? string.Format("ORDER BY {0} DESC", _ID) : ""));
}
public List <DefectBase> gettasks(DefectsFilter f)
{
DefectBase enm = new DefectBase();
return(enm.Enum(f));
}
public StoredDefectsFilter saveFilter(string name, bool personal, DefectsFilter filter)
{
CurrentContext.Validate();
return(StoredDefectsFilter.NewFilter(name, personal, filter, CurrentContext.TTUSERID));
}
