public static User GetUser(string suser, string spass)
{
User retuser = null;
SqlDataReader reader;
spass = SetPassword(spass);
SqlParameter puser = new SqlParameter("@user", suser);
SqlParameter ppass = new SqlParameter("@pass", spass);
reader = DBFunction.ExecuteReader("SELECT * from users where username=@user and password=@pass", puser, ppass);
if (reader != null && reader.Read())
{
retuser = new User();
retuser.Name = (string)reader["name"];
retuser.UserName = (string)reader["username"];
retuser.Password = ReturnPassword(reader["password"].ToString());
retuser.Email = (string)reader["email"];
retuser.ID = int.Parse(reader["id"].ToString());
retuser.Tel = (string)reader["tel"];
retuser.Control = (User.TypeControl)reader["control"];
retuser.TempPersonal = (bool)reader["TempPersonal"];
retuser.TempGeneral = (bool)reader["TempGeneral"];
retuser.Sector = (string)reader["Sector"];
GLOBALVARS.LastUserChangeFile = DateTime.Now;
UpdateManager.UpdateLastTimeCheck();
}
reader.Close();
DBFunction.CloseConnections();
return(retuser);
}
public static bool AllowLimited(People p)
{
bool ret = false;
ret = DBFunction.Execute("insert into notes values (" + GLOBALVARS.MyUser.ID + "," + p.ID + ",'אפשר לי לראות את החסוי הזה'," + (int)People.ReasonType.AllowLimited + ")");
PopUpMessage(true);
return(ret);
}
private static bool WeddingUpdate(People p)
{
bool ret = false;
ret = DBFunction.Execute("insert into notes values (" + GLOBALVARS.MyUser.ID + "," + p.ID + ",''," + (int)People.ReasonType.Wedding + ")");
PopUpMessage(true);
return(ret);
}
public static void CheckOs(int userid)
{
if (!File.Exists("OS"))
{
DBFunction.Execute("update users set OS='" + Environment.OSVersion.VersionString + "' where id=" + userid + " and OS IS NULL");
File.Create("OS").Close();
}
}
public static bool DeletePeople(int peopleid, bool ask = true, bool perment = false)
{
try
{
int id = peopleid;
DialogResult yesno = DialogResult.Yes;
if (ask)
{
yesno = MessageBox.Show("האם אתה בטוח שברצונך למחוק", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
}
Forms.DeleteForm delf = new Forms.DeleteForm();
if (yesno == DialogResult.Yes)
{
string sql = "";
if (!perment)
{
delf.ShowDialog();
sql = "update peoples set show=8,DeleteReason=N'" + delf.ReasonDelete + "' where ID=" + id;
}
else
{
sql = "BEGIN TRANSACTION delete from peoples where ID=" + id + "; " +
"delete from peopledetails where relatedid=" + id + "; " +
"delete from registerinfo where relatedid=" + id + "; COMMIT";
}
if (DBFunction.Execute(sql))
{
if (!perment)
{
ShiduchActivity.insertActivity(
new ShiduchActivity()
{
Action = (int)ShiduchActivity.ActionType.delete,
Date = DateTime.Now,
PeopleId = id,
UserId = GLOBALVARS.MyUser.ID,
});
MessageBox.Show("נמחק בהצלחה, תוכל למצוא את הכרטיס בסל המחזור", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("הכרטיס נמחק לצמיתות", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(true);
}
else
{
MessageBox.Show("אירעה שגיאה", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
return(false);
}
catch { MessageBox.Show("אירעה שגיאה", "", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); }
}
public static bool ShowHide(People p)
{
string Sql = "Userid=" + GLOBALVARS.MyUser.ID + " AND ALLOWID=" + p.ID;
if (DBFunction.CheckExist(Sql, "LimitedAllow"))
{
return(true);
}
return(false);
}
public void openShiduchActivityForm(ListView lst, People MyPeople = null)
{
//עדכון פעילות
if (lst.SelectedItems.Count <= 0)
{
return;
}
if (lst.Name == "lstReminder")
{
openReminder = true;
}
int idActivity = int.Parse(lst.SelectedItems[0].Tag.ToString());
// int idSideB = int.Parse(lstMyActivity.SelectedItems[0].SubItems[5].Text);
s = ReadById(idActivity);
//להביא את ההערות של הכרטיס השני
string notesSide = removeFromString(s.NotesSummary) + "\r\n" +
"=====צד ב'=====" + Environment.NewLine;
string sql = "select NotesSummary from ShiduchActivity s " +
"where s.UserId=" + s.UserId + " and PeopleId=" +
s.IdSideB + " and IdSideB=" + s.PeopleId + " and Action=" + s.Action +
" and abs(DATEDIFF(day,s.Date,'" + s.Date.ToString("yyyy-MM-dd h:mm tt") + "'))" +
" between 0 and 15";
SqlDataReader reader = DBFunction.ExecuteReader(sql);
if (reader.Read())
{
notesSide += removeFromString(reader["NotesSummary"].ToString());
}
reader.Close();
s.NotesSummary = notesSide;
if (MyPeople == null)
{
MyPeople = new People();
SqlDataReader reader1 = People.ReadById(s.PeopleId);
if (reader1.Read())
{
PeopleManipulations.ReaderToPeople(ref MyPeople, ref reader1);
}
reader1.Close();
}
ShiduchActivityForm sForm;
if (openReminder)//אם נפתח דרך התזכורות
{
sForm = new ShiduchActivityForm(s, MyPeople, false, true, false, true);
}
else
{
sForm = new ShiduchActivityForm(s, MyPeople, false, true, false);
}
sForm.Show();
sForm.FormClosed += SForm_FormClosed;
}
public static void DeleteAction(Log idlog)
{
if (idlog.Date == null)
{
idlog.Date = DateTime.Now;
}
idlog.UserId = GLOBALVARS.MyUser.ID;
idlog.Level = GLOBALVARS.MyUser.Control;
DBFunction.Execute("delete LOG where Id=" +
idlog.ID);
}
public static SqlDataReader ReadAll(bool ReallyAll = false)
{
SqlDataReader reader;
string top = "top 100";
if (ReallyAll)
{
top = "";
}
reader = DBFunction.ExecuteReader("select " + top + " * from log order by Date desc");
return(reader);
}
public static void LoadDictonary()
{
SqlDataReader reader = DBFunction.ExecuteReader("select * from Dictionary");
while (reader.Read())
{
DictinorayRow row = new DictinorayRow();
row.LoadFromReader(reader);
DictinorayList.Add(row);
}
reader.Close();
}
public static Log ReadById(int id)
{
Log retlog = new Log();
SqlDataReader reader;
reader = DBFunction.ExecuteReader("select * from log where id=" + id);
if (reader.Read())
{
ReaderToLog(ref reader, ref retlog);
}
reader.Close();
return(retlog);
}
private static void LoadLabelsToAllLabels()
{
SqlDataReader reader = DBFunction.ExecuteReader("select * from Labels");
AllLabels = new Labels[65];
int index = 0;
while (reader.Read())
{
AllLabels[index] = new Labels(reader["label"].ToString(), reader["cat"].ToString(), int.Parse(reader["ID"].ToString()));
index++;
}
reader.Close();
}
private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
{
for (int i = 0; i < 1; i++)
{
Log.SetDurationLogin();
}
for (int i = 0; i < 1; i++)
{
WriteSettings();
}
DBFunction.CloseConnections();
Program.IdleTimer.Stop();
Environment.Exit(0);
}
private void MultipileCheck()
{
// newPeople = new People();
newPeople.FirstName = txtfname.Text;
newPeople.Lasname = txtlname.Text;
newPeople.City = txtcity.Text;
newPeople.Tz = txtTZ.Text;
newPeople.Details.DadName = DadNametxtdadname.Text;
newPeople.Details.MomName = txtmomname.Text;
newPeople.OpenDetailsForAdd = radGeneral.Checked;
newPeople.OpenForPersonalAdd = radPersonal.Checked;
newPeople.Sexs = radMale.Checked ? 1 : 2;
if (newPeople.OpenDetailsForAdd)
{
newPeople.Temp = GLOBALVARS.MyUser.TempGeneral;
}
else
{
newPeople.Temp = GLOBALVARS.MyUser.TempPersonal;
}
SqlParameter[] prms = new SqlParameter[10];
string sql = "";
sql += "select p.ID, FirstName,lastname, ByUserName,RegDate " +
"from peoples p inner join peopledetails pd on p.ID = pd.relatedid inner join " +
"registerinfo r on pd.relatedid=r.relatedid where show <> 8 AND " +
BuildSql.GetSql(out prms[0], txtfname.Text, "FirstName", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[1], txtlname.Text, "Lastname", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[2], txtcity.Text, "City", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[4], DadNametxtdadname.Text, "DadName", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[5], txtmomname.Text, "MomName", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[6], txtTZ.Text, "Tz", BuildSql.SqlKind.LIKE);
sql = BuildSql.CheckForLastAnd(ref sql);
SqlDataReader reader = DBFunction.ExecuteReader(sql, prms);
if (reader.HasRows)
{
reader.Read();
string text = "כרטיס כבר קיים במערכת" + Environment.NewLine + "כרטיס על שם:" +
reader["FirstName"] + " " + reader["Lastname"] + ", נקלט בתאריך: "
+ reader["RegDate"] + ", על ידי: " + reader["ByUserName"];
MessageBox.Show(text, "כרטיס קיים", MessageBoxButtons.OK, MessageBoxIcon.Warning);
reader.Close();
return;
}
OK = true;
Close();
}
public static bool ChangeUserNameAndPassword(string uname, string psw, int userid)
{
SqlParameter[] prms = new SqlParameter[7];
string sql = "update users set " +
BuildSql.UpdateSql(out prms[0], uname, "username") +
BuildSql.UpdateSql(out prms[1], psw, "password", true) +
" where id=" + userid + ";";
if (DBFunction.Execute(sql, prms))
{
return(true);
}
return(false);
}
public static ShiduchActivity ReadById(int id)
{
string sql = "select s.*,r.id as remindID, r.Date as remindDate,r.Done,r.IdUser " +
"as remindIdUser from ShiduchActivity s " +
"inner join ReminderActivity r on s.Id=r.IdActivity where s.Id=" + id;
SqlDataReader reader = DBFunction.ExecuteReader(sql);
ShiduchActivity activity = new ShiduchActivity();
if (reader.Read())
{
readerToShiduchActivity(ref reader, ref activity);
}
reader.Close();
return(activity);
}
public static SqlDataReader ReadById(int ID)
{
try
{
string sql = "select * from peoples p inner join peopledetails pd on p.ID = pd.relatedid inner join " +
"registerinfo r on pd.relatedid=r.relatedid where p.ID=" + ID;
SqlDataReader reader = DBFunction.ExecuteReader(sql);
return(reader);
}
catch (Exception ex)
{
MessageBox.Show("השגיאה הבאה התרחשה \n" + ex.Message);
return(null);
}
}
public static void RemoveHandler(int id)
{
try
{
if (id == 0)
{
return;
}
DBFunction.Execute("update peopledetails set chadchan=0 where chadchan=" + id);
MessageBox.Show("הוסר בהצלחה");
}
catch
{
MessageBox.Show("אירעה שגיאה בהסרת השדכן מטפל");
}
}
public void LoadClients()
{
string sql;
GLOBALVARS.Clients = new ArrayList();
sql = "select firstname + ' ' + lastname as allname,id from peoples where show=0 order by firstname ";
SqlDataReader reader;
reader = DBFunction.ExecuteReader(sql);
while (reader != null && reader.Read())
{
KeyValueClass temp = new KeyValueClass((string)reader["allname"], (int)reader["ID"]);
GLOBALVARS.Clients.Add(temp);
}
reader.Close();
}
public static void UpdateLastTimeCheckToDb()
{
SqlParameter[] prms = new SqlParameter[5];
SqlParameter sw = new SqlParameter("@sw", DateTime.Now);
SqlParameter alert = new SqlParameter("@alert", DateTime.Now);
SqlParameter people = new SqlParameter("@people", DateTime.Now);
SqlParameter user = new SqlParameter("@user", DateTime.Now);
SqlParameter chat = new SqlParameter("@chat", DateTime.Now);
prms[0] = sw;
prms[1] = alert;
prms[2] = people;
prms[3] = user;
prms[4] = chat;
DBFunction.Execute("Update general set LastUpdate=@sw,LastAlertsChanges=@alert,LastPeopleChanges=@people,LastUserChange=@user,LastChatChange=@chat", prms);
}
public static void InsertReminder(ShiduchActivity Activity)
{
SqlParameter[] prms = new SqlParameter[10];
string sqlAct, sql;
sqlAct = "insert into ReminderActivity values(" +
BuildSql.InsertSql(out prms[0], Activity.Id) +
BuildSql.InsertSql(out prms[1], Activity.reminder.IdUser) +
BuildSql.InsertSql(out prms[2], Activity.reminder.Date) +
BuildSql.InsertSql(out prms[3], false, true)
+ ");";
sql = "BEGIN TRANSACTION " +
sqlAct +
"COMMIT";
DBFunction.Execute(sql, prms);
}
public static bool UpdateNotes(NotesOfPeople note)
{
SqlParameter[] prms = new SqlParameter[10];
string sqlnotes, sql;
sql = "BEGIN TRANSACTION ";
sql += "update NotesOfPeople SET " +
BuildSql.UpdateSql(out prms[0], note.UserId, "UserId") +
BuildSql.UpdateSql(out prms[1], note.UserName, "UserName") +
BuildSql.UpdateSql(out prms[2], note.PeopleId, "PeopleId") +
BuildSql.UpdateSql(out prms[3], note.NoteText, "NoteText") +
BuildSql.UpdateSql(out prms[4], note.NoteDate, "NoteDate", true)
+ " where NoteId=" + note.NoteId + "; COMMIT";
return(DBFunction.Execute(sql, prms));
}
public static void UpdateReminder(ShiduchActivity Activity)
{
SqlParameter[] prms = new SqlParameter[10];
string sqlAct, sql;
sqlAct = "update ReminderActivity SET " +
BuildSql.UpdateSql(out prms[0], Activity.reminder.IdActivity, "IdActivity") +
BuildSql.UpdateSql(out prms[1], Activity.reminder.IdUser, "IdUser") +
BuildSql.UpdateSql(out prms[2], Activity.reminder.Date, "Date") +
BuildSql.UpdateSql(out prms[3], Activity.reminder.Done, "Done", true)
+ " where Id=" + Activity.reminder.Id + ";";
sql = "BEGIN TRANSACTION " +
sqlAct +
"COMMIT";
DBFunction.Execute(sql, prms);
}
public static bool ReaderToPeople(ref People peopleobj, ref SqlDataReader reader, RtpFor WhatFor)
{
if (WhatFor == RtpFor.ForSearch)
{
peopleobj.Age = float.Parse(reader["Age"].ToString());
peopleobj.Lasname = (string)reader["lastname"];
peopleobj.Sexs = (int)reader["sexs"];
peopleobj.FirstName = (string)reader["firstname"];
peopleobj.Details.Schools = (string)reader["schools"];
peopleobj.Tall = float.Parse(reader["tall"].ToString());
peopleobj.ID = int.Parse(reader["id"].ToString());
peopleobj.City = (string)reader["City"];
peopleobj.FaceColor = (string)reader["FaceColor"];
peopleobj.Looks = (string)reader["Looks"];
peopleobj.WorkPlace = (string)reader["WorkPlace"];
peopleobj.Beard = (string)reader["Beard"];
peopleobj.Zerem = (string)reader["Zerem"];
peopleobj.ZeremMom = (string)reader["ZeremMom"];
peopleobj.LearnStaus = (string)reader["LearnStatus"];
peopleobj.DadWork = (string)reader["DadWork"];
peopleobj.Background = (string)reader["Background"];
peopleobj.Status = (string)reader["Status"];
peopleobj.Weight = (string)reader["fat"];
peopleobj.Tz = (string)reader["Tz"];
peopleobj.KindChasidut = (string)reader["KindChasidut"];
peopleobj.HealthStatus = (string)reader["HealthStatus"];
peopleobj.Eda = (string)reader["Eda"];
peopleobj.Details.DadName = (string)reader["DadName"];
peopleobj.Details.Street = (string)reader["Street"];
peopleobj.Details.MomName = (string)reader["MomName"];
peopleobj.Details.MomWork = (string)reader["MomWork"];
try
{
//peopleobj.Register.LastUpdate = (DateTime)reader["lastupdate"];
if (GLOBALVARS.MyUser.Control > User.TypeControl.User && DBFunction.ColumnExists(reader, "Reason"))
{
peopleobj.Reason = (int)reader["reason"];
peopleobj.ByUser = (int)reader["byuser"];
peopleobj.RealId = int.Parse(reader["mrelatedID"].ToString());
peopleobj.TempId = (int)reader["TID"];
}
}
catch { };
}
return(true);
}
public static bool InsertNewNotes(NotesOfPeople note)
{
SqlParameter[] prms = new SqlParameter[10];
string sqlnotes, sql;
sqlnotes = "INSERT INTO NotesOfPeople VALUES(" +
BuildSql.InsertSql(out prms[0], GLOBALVARS.MyUser.ID) +
BuildSql.InsertSql(out prms[1], GLOBALVARS.MyUser.Name) +
BuildSql.InsertSql(out prms[2], note.PeopleId) +
BuildSql.InsertSql(out prms[3], note.NoteText) +
BuildSql.InsertSql(out prms[4], DateTime.Now, true)
+ ");";
sql = "BEGIN TRANSACTION " +
sqlnotes +
"COMMIT";
return(DBFunction.Execute(sql, prms));
}
public void LoadShadcanim()
{
string sql, where;
int controlhide = 1, index = -1;
Shadchanim = new ArrayList();
if (GLOBALVARS.MyUser.Control == User.TypeControl.Admin || GLOBALVARS.MyUser.Control == User.TypeControl.Manger)
{
where = "";
}
else if (thisSideB)
{
where = " where id=" + ShiduchActivity.UserId;
}
else
{
where = "where id=" + GLOBALVARS.MyUser.ID;
}
sql = "select name,id from users " + where;
SqlDataReader reader = DBFunction.ExecuteReader(sql);
while (reader != null && reader.Read())
{
KeyValueClass temp = new KeyValueClass((string)reader["name"], (int)reader["ID"]);
if (int.Parse(temp.Value.ToString()) == GLOBALVARS.MyUser.ID)
{
if (index < 0)
{
index = txtReminderInCare.Items.Count;
}
temp.Text = "שלי";
}
//אם הטיפול הוא אצל מישהו אחר ממני
if (updateActivity && int.Parse(temp.Value.ToString()) == ShiduchActivity.reminder.IdUser)
{
index = txtReminderInCare.Items.Count;
}
Shadchanim.Add(temp);
txtReminderInCare.Items.Add(temp);
}
reader.Close();
DBFunction.CloseConnections();
txtReminderInCare.SelectedIndex = index;
}
public void InserHistory()
{
SqlParameter[] prms = new SqlParameter[10];
string sqlHistory, sql;
sqlHistory = "INSERT INTO HistoryChangeDetails VALUES(" +
BuildSql.InsertSql(out prms[1], this.Date) +
BuildSql.InsertSql(out prms[2], this.IdUser) +
BuildSql.InsertSql(out prms[3], this.IdPeople) +
BuildSql.InsertSql(out prms[4], this.NameUser) +
BuildSql.InsertSql(out prms[5], this.FieldName) +
BuildSql.InsertSql(out prms[6], this.CurrentValue) +
BuildSql.InsertSql(out prms[7], this.NewValue, true)
+ ");";
sql = "BEGIN TRANSACTION " +
sqlHistory +
"COMMIT";
DBFunction.Execute(sql, prms);
}
public static void SetDurationLogin()
{
string sql = "select * from[Log] t inner join("
+ "select UserId, max(date) as MaxDate from[Log] where UserId ="
+ GLOBALVARS.MyUser.ID + " group by UserId, action) tm "
+ " on t.UserId = tm.UserId and t.date = tm.MaxDate where Action = 1";
SqlDataReader reader = DBFunction.ExecuteReader(sql);
if (reader.Read())
{
string s = "update Log set DateExit='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt") + "' where id =" + reader["id"].ToString();
reader.Close();
DBFunction.Execute(s);
}
if (!reader.IsClosed)
{
reader.Close();
}
}
private void loadNotes(ListView lstvw = null)
{
lstNotesOfPeople.Items.Clear();
txtNoteText.Text = "";
btnNoteSave.Enabled = false;
SqlDataReader reader = null;
if (lstvw == null)
{
lstvw = lstNotesOfPeople;
}
ListViewItem item;
// ||GLOBALVARS.MyUser.CanEdit
if (GLOBALVARS.MyUser.Control == User.TypeControl.Manger || GLOBALVARS.MyUser.Control == User.TypeControl.Admin)
{
reader = DBFunction.ExecuteReader("select * from NotesOfPeople n where n.PeopleId=" + MyPeople.ID);
}
else
{
reader = DBFunction.ExecuteReader("select * from NotesOfPeople n where n.UserId=" + GLOBALVARS.MyUser.ID + "and n.PeopleId=" + MyPeople.ID);
}
lstNotesOfPeople.BeginUpdate();
MyPeople.Note = new List <NotesOfPeople>();
NotesOfPeople n = new NotesOfPeople();
while (reader.Read())
{
MyPeople.Note.Add(n.ReaderToNotes(ref reader));
item = new ListViewItem(new string[] {
DateTime.Parse(reader["NoteDate"].ToString()).ToShortDateString(),
(string)reader["UserName"],
(string)reader["NoteText"],
reader["NoteId"].ToString()
});
item.Tag = reader["UserId"].ToString();
lstvw.Items.Add(item);
}
lstNotesOfPeople.EndUpdate();
reader.Close();
}
private void btndelsw_Click(object sender, EventArgs e)
{
if (lstchadcan.SelectedItems.Count <= 0)
{
return;
}
if (MessageBox.Show("האם אתה בטוח שברצונך למחוק לו את התוכנה", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
int userid = int.Parse(lstchadcan.SelectedItems[0].SubItems[3].Text);
string sql = "update users set control=" + (int)User.TypeControl.Delete + " where id=" + userid;
if (DBFunction.Execute(sql))
{
MessageBox.Show("התוכנה תמחק בפעם הבאה שהמשתמש ינסה להתחבר");
}
else
{
MessageBox.Show("אירעה שגיאה");
}
}
}