private void SearchForTemp()
{
string sql = "", AgeSql = "";
SqlParameter[] prms = new SqlParameter[20];
int fromage = (int)TemptxtAgeFrom.Value;
int tillage = (int)TemptxtAgeTo.Value;
if (tillage > 0)
{
AgeSql = BuildSql.GetSql(out prms[12], fromage, "age", BuildSql.SqlKind.BETWEEN, true, tillage);
}
sql +=
BuildSql.GetSql(out prms[0], TemptxtFirstName.Text, "FirstName", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[1], TemptxtLastName.Text, "Lastname", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[2], TemptxtCity.Text, "City", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[3], (TemptxtSexs.SelectedIndex + 1), "sexs", BuildSql.SqlKind.EQUAL) +
BuildSql.GetSql(out prms[4], TemptxtStatus.Text, "Status", BuildSql.SqlKind.EQUAL) +
BuildSql.GetSql(out prms[5], TemptxtLearnStatus.Text, "LearnStatus", BuildSql.SqlKind.EQUAL) +
BuildSql.GetSql(out prms[6], TemptxtEda.Text, "eda", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[7], TemptxtBackground.Text, "background", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[8], TemptxtSchools.Text, "schools", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[9], TemptxtDadWork.Text, "dadwork", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[10], TemptxtMomWork.Text, "MomWork", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[11], TemptxtWorkPlace.Text, "WorkPlace", BuildSql.SqlKind.LIKE) +
AgeSql;
}
private void btnlogfilter_Click(object sender, EventArgs e)
{
string mngrtoo = "";
int login = 0, clientsopen = 0;
SqlParameter date1 = new SqlParameter("@date1", dtlogfrom.Value);
SqlParameter date2 = new SqlParameter("@date2", dtlogto.Value);
int loguserid;
string actionsql = "";
SqlParameter[] prms = new SqlParameter[1];
if (cmbusers.SelectedItem is KeyValueClass || cmbusers.SelectedIndex > 0)
{
loguserid = (int)(cmbusers.SelectedItem as KeyValueClass).Value;
}
else
{
loguserid = -1;
}
if (cmbaction.SelectedIndex != -1 && cmbaction.SelectedIndex != 8)
{
actionsql = " ACTION=" + cmbaction.SelectedIndex + " AND ";
}
string info = "";
string sql = "select users.name,users.id,info,action,userid,date,level,dateexit from log l inner join users on l.userid=USERS.ID where ";
if (loguserid != -1)
{
sql += BuildSql.GetSql(out prms[0], loguserid, "UserId", BuildSql.SqlKind.EQUAL);
}
sql += actionsql;
//if (!chk_mangertoo.Checked)
// mngrtoo = "and userid <> 1 and userid <> 38 and userid <> 54";
sql += " date between @date1 AND @date2 " + mngrtoo + " order by date desc";
SqlDataReader reader = Log.ReadSql(sql, date1, date2, prms[0]);
Log temp = new Log();
lstlog.Items.Clear();
lstlog.BeginUpdate();
TimeSpan ts;
decimal SumTime = 0;
while (reader.Read())
{
Log.ReaderToLog(ref reader, ref temp);
if (temp.Info != null)
{
info = temp.Info.Replace('^', ' ');
}
//todo לחשב את הזמן שאחרי הנקודה לפי שעות כלומר חלקי 60
ts = temp.DateExit.Subtract(temp.Date);
string name = reader["name"].ToString();
lstlog.Items.Add(new ListViewItem(new string[] {
reader["ID"].ToString(),
reader["name"].ToString(),
temp.ActionString,
temp.Date.ToString(),
temp.DateExit.ToString(),
Math.Floor(ts.TotalHours) + "." + ts.Minutes,
info
}, 3));
switch (temp.Command)
{
case Log.ActionType.ClientOpen:
clientsopen++;
break;
case Log.ActionType.Login:
login++;
SumTime += decimal.Parse(ts.TotalHours.ToString());
break;
}
}
int sum = Convert.ToInt32(Math.Floor(SumTime));
float t6 = float.Parse((SumTime - Math.Truncate(SumTime)).ToString());
if (t6 > 0.5)
{
sum = Convert.ToInt32(Math.Ceiling(SumTime));
}
lblreportstatus.Text = "סך הכל כניסות לתוכנה : " + login + "\nסך הכל פתיחת תיקי לקוחות : " + clientsopen + "\nסך עבודה בשעות על התוכנה: " + sum;
reader.Close();
lstlog.EndUpdate();
}