private static Messages[] LoadMessages(bool ForManger = false)
{
int count = 0;
int temp = 0;
Messages[] msg = null;
SqlDataReader reader;
if (ForManger)
{
reader = DBFunction.ExecuteReader("select count(byuser) as res_count,* from msgs");
}
else
{
reader = DBFunction.ExecuteReader("select count(byuser) as res_count,* from msgs where byuser="******"res_count"];
msg = new Messages[count];
}
msg[temp] = new Messages();
msg[temp].ByUser = (int)reader["byuser"];
msg[temp].Topic = reader["topic"].ToString();
msg[temp].MsgId = (int)reader["msgid"];
}
reader.Close();
return(msg);
}
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 void LoadShadchanHandlers(int chadchanid = 0, ListView lstvw = null, bool mkcolor = false)
{
string addsql = "";
if (chadchanid != 0)
{
addsql = " and chadchan =" + chadchanid;
}
lstvw.Items.Clear();
SqlDataReader reader = DBFunction.ExecuteReader("select users.name,peoples.chadchan,peoples.firstname,peoples.id as pid,peoples.lastname,users.id,relatedid from peoples" +
" inner join peopledetails on ID=relatedid inner join users on peoples.chadchan=users.id where chadchan <> 0" + addsql);
while (reader.Read())
{
ListViewItem item = new ListViewItem(new string[] {
reader["firstname"].ToString(),
reader["lastname"].ToString(),
"שדכן מטפל : " + reader["name"].ToString(),
reader["pid"].ToString(),
reader["id"].ToString()
}, 4);
if (mkcolor)
{
item.ForeColor = Color.Blue;
}
lstvw.Items.Add(item);
}
reader.Close();
}
private string RegisterDateToReport(int id)
{
string sql = "";
string rep = "";
switch (rep_type)
{
case ReportType.Client:
sql = "select regdate from registerinfo where relatedid=" + id;
break;
case ReportType.User:
sql = "select dateadded from users where id=" + id;
break;
}
if (string.IsNullOrEmpty(sql))
{
return(rep);
}
SqlDataReader reader = DBFunction.ExecuteReader(sql);
if (reader.Read())
{
rep = "<h3>קיים במאגר מ <span class='label label-default'>" + reader.GetDateTime(0).ToShortDateString() + "</span></h3><hr>";
}
reader.Close();
return(rep);
}
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 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 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();
}
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();
}
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 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 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 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 static void LoadLastUpdateFromFileAndDb()
{
//START OF CHECK IF THERE IS A UPDATE
string LastupdateSql = "select top 1 Status,Lastupdate,FilesToBeUpdate,LastAlertsChanges,LastPeopleChanges,LastUserChange,LastChatChange from general";
GetLastUpdate();
reader = DBFunction.ExecuteReader(LastupdateSql);
if (reader.HasRows)
{
reader.Read();
GLOBALVARS.LastSwUpdateDB = DateTime.Parse(reader["Lastupdate"].ToString());
GLOBALVARS.LastAlertsCheckDB = DateTime.Parse(reader["LastAlertsChanges"].ToString());
GLOBALVARS.LastPeopleCheckDB = DateTime.Parse(reader["LastPeopleChanges"].ToString());
GLOBALVARS.LastUserChangeDB = DateTime.Parse(reader["LastUserChange"].ToString());
GLOBALVARS.LastChatChangeDB = DateTime.Parse(reader["LastChatChange"].ToString());
FilesToBeUpdate = (int)reader["FilesToBeUpdate"];
GLOBALVARS.StatusSw = (int)reader["Status"];
}
FilesToBeUpdate++;
reader.Close();
//END OF CHECK
}
public static User GetUser(int id)
{
SqlDataReader reader;
User retuser = null;
SqlParameter pid = new SqlParameter("@id", id);
reader = DBFunction.ExecuteReader("select * from users where id=@id", pid);
if (reader != null && reader.Read())
{
retuser = new User();
retuser.Name = (string)reader["name"];
retuser.UserName = (string)reader["username"];
retuser.Password = reader["password"].ToString().Split('^')[1];
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"];
}
reader.Close();
return(retuser);
}
public static SqlDataReader GetHistory(bool card, People p)
{
string sql = "select * from HistoryChangeDetails h where h.IdPeople=" + p.ID;
return(DBFunction.ExecuteReader(sql));
}
public static void CheckForUpdate()
{
Thread updatethread;
int tempid;
// SqlDataReader tempreader;
LoadLastUpdateFromFileAndDb();
if (GLOBALVARS.LastSwUpdateDB <= GLOBALVARS.LastSwUpdateFile)
{
return;
}
GLOBALVARS.LastSwUpdateFile = DateTime.Now;
//START CHECK WHICH FILES ARE UPDATED
OpenUpdateFile();
reader = DBFunction.ExecuteReader("Select active,filename,version,[date],id,data from FileUpdates");
string sfilename = Process.GetCurrentProcess().Modules[0].FileName;
if (reader.HasRows)
{
//ThreadPool.QueueUserWorkItem(new WaitCallback(ShowUpdateDialog));
updatethread = new Thread(new ThreadStart(ShowUpdateDialog));
updatethread.IsBackground = true;
updatethread.Start();
}
UpdatedList = new List <FileAndVersion>();
while (!ThreadFinsihLoading)
{
} //Loop Until Thread Loaded
while (reader.Read())
{
FileAndVersion ftemp = new FileAndVersion();
ftemp.Filename = (string)reader["Filename"];
ftemp.Date = (DateTime)reader["Date"];
tempid = (int)reader["id"];
UpdatedList.Add(ftemp);
if (!SerachInTheListFiles(ftemp))
{
// tempreader= DBFunction.ExecuteReader("Select data from FileUpdates where id=" + tempid);
// tempreader.Read();
byte[] data = (byte[])reader["data"];
// tempreader.Close();
winupdate.PbUpdate.Value++;
if (((bool)reader["Active"]))
{ // Check if this is the sw itself
File.WriteAllBytes(Application.StartupPath + "//temp.ex", data);
File.Move(sfilename, "old.del");
File.Move("temp.ex", sfilename);
}
else
{
File.WriteAllBytes(Application.StartupPath + "//" + ftemp.Filename, data);
}
}
else
{
winupdate.PbUpdate.Value++;
}
}
winupdate.PbUpdate.Value++;
reader.Close();
UpdateTheFile();
UpdateLastTimeCheck(DateTime.Now);
Process.Start(sfilename);
Environment.Exit(Environment.ExitCode);
}
public static SqlDataReader ReadSql(string sql, params SqlParameter[] prms)
{
return(DBFunction.ExecuteReader(sql, prms));
}
private void Search(object obj)
{
SqlDataReader reader;
SqlParameter[] prms = new SqlParameter[25];
string Sql = "";
string AgeSql = "";
string whoami = "";
string whoiwant = "";
string sqlwhoiwant = "";
string sqlwhoami = "";
string noteswhoami = "";
string noteswhoiwant = "";
string LearnStatus = "";
string Subscription = "";
string sexs = "";
int fromage = (int)txtfromage.Value;
int tillage = (int)txttillage.Value;
string show = " AND SHOW <> 8 AND (show <2 or (show=5 and chadchan like '%{" + GLOBALVARS.MyUser.ID + "}%') or (show=4 and chadchan like '%{" + GLOBALVARS.MyUser.ID + "}%'))";
string IdFilter = "";
if (GLOBALVARS.MyUser.Control == User.TypeControl.Manger || GLOBALVARS.MyUser.Control == User.TypeControl.Admin)
{
show = " and show <> 8";
}
if (txtlearnstatus.SelectedIndex != -1)
{
LearnStatus = BuildSql.GetSql(out prms[16], txtlearnstatus.Text, "LearnStatus", BuildSql.SqlKind.EQUAL);
}
if (tillage > 0)
{
AgeSql = BuildSql.GetSql(out prms[0], fromage, "age", BuildSql.SqlKind.BETWEEN, true, tillage);
}
// if(chksubscription.Checked)
// Subscription= BuildSql.GetSql(out prms[17], chksubscription.Checked, "Subscription", BuildSql.SqlKind.EQUAL,false);
if (txtid.Value != 0)
{
IdFilter = " peoples.ID=" + txtid.Value + " AND ";
}
if (txtwhoami.Text.Length > 0)
{
noteswhoami = whoami = "(";
foreach (string s in splitwords(txtwhoami.Text))
{
if (s.Trim().Length > 0)
{
whoami += " whoami like N'%" + s + "%' and";
noteswhoami += " notes like N'%" + s + "%' and";
}
}
whoami = whoami.Remove(whoami.Length - 3, 3);
noteswhoami = noteswhoami.Remove(noteswhoami.Length - 3, 3);
noteswhoami += ")";
whoami += ")";
sqlwhoami = "(" + whoami + " or " + noteswhoami + ")";
}
if (txtwhoiwant.Text.Length > 0)
{
whoiwant = noteswhoiwant = "(";
foreach (string s in splitwords(txtwhoiwant.Text))
{
if (s.Trim().Length > 0)
{
whoiwant += " whoiwant like N'%" + s + "%' and";
noteswhoiwant += " notes like N'%" + s + "%' and";
}
}
whoiwant = whoiwant.Remove(whoiwant.Length - 3, 3);
noteswhoiwant = noteswhoiwant.Remove(noteswhoiwant.Length - 3, 3);
noteswhoiwant += ")";
whoiwant += ")";
sqlwhoiwant = "(" + whoiwant + " or " + noteswhoiwant + ")";
}
if (!string.IsNullOrEmpty(whoiwant) && !string.IsNullOrEmpty(whoami))
{
sqlwhoami += " and ";
}
if (txtsexs.SelectedIndex != -1)
{
sexs = BuildSql.GetSql(out prms[12], txtsexs.SelectedIndex + 1, "sexs", BuildSql.SqlKind.EQUAL);
}
Sql = " select schools,sexs,firstname,lastname,tall,age,City,fat," +
"FaceColor,Looks,WorkPlace,Beard,Zerem,Eda,LearnStatus,DadWork," +
"Background,Status,Tz,KindChasidut,HealthStatus,ZeremMom,Street," +
"DadName,MomName,MomWork,"
+ "[peopledetails].[relatedid],[Peoples].[ID],Tel1,Tel2,Telephone,PhoneOfBachur from peoples inner join peopledetails on ID=relatedid where temp='false' AND " +
BuildSql.GetSql(out prms[1], txtfname.Text, "FirstName", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[2], txtlname.Text, "Lastname", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[3], txtbeard.Text, "beard", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[4], txtbg.Text, "background", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[5], txtcoverhead.Text, "coverhead", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[6], txtdadwork.Text, "dadwork", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[7], txtfacecolor.Text, "facecolor", BuildSql.SqlKind.LIKE) +
IdFilter +
BuildSql.GetSql(out prms[8], txtfat.Text, "fat", BuildSql.SqlKind.LIKE) +
AgeSql +
BuildSql.GetSql(out prms[9], txtlooks.Text, "looks", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[10], txtpeticut.Text, "openhead", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[11], txtschool.Text, "schools", BuildSql.SqlKind.LIKE) +
sexs +
BuildSql.GetSql(out prms[13], txtzerem.Text, "(eda", BuildSql.SqlKind.LIKE, false, null, true) +
BuildSql.GetSql(out prms[14], txtzerem.Text, "zerem", BuildSql.SqlKind.LIKE, false, null, false, ") AND ") +
BuildSql.GetSql(out prms[15], txtstatus.Text, "Status", BuildSql.SqlKind.EQUAL) +
BuildSql.GetSql(out prms[17], txtCity.Text, "City", BuildSql.SqlKind.LIKE) +
BuildSql.GetSql(out prms[18], txtPhone.Text, "(Tel1", BuildSql.SqlKind.LIKE, false, null, true) +
BuildSql.GetSql(out prms[19], txtPhone.Text, "Tel2", BuildSql.SqlKind.LIKE, false, null, true) +
BuildSql.GetSql(out prms[20], txtPhone.Text, "Telephone", BuildSql.SqlKind.LIKE, false, null, true) +
BuildSql.GetSql(out prms[21], txtPhone.Text, "PhoneOfBachur", BuildSql.SqlKind.LIKE, false, null, false, " ) AND ") +
sqlwhoami + sqlwhoiwant +
LearnStatus +
Subscription;
Sql = BuildSql.CheckForLastAnd(ref Sql);
Sql += show;
Sql += " ORDER BY ID DESC";
reader = DBFunction.ExecuteReader(Sql, prms);
// fs.Search(txtfreeserach.Text, (FreeSearch.accuracy)cmb_accuracy.SelectedIndex);
List <People> lst = new List <People>();
while (reader.Read())
{
People p = new People();
PeopleManipulations.ReaderToPeople(ref p, ref reader, PeopleManipulations.RtpFor.ForSearch);
lst.Add(p);
}
olstpeople.BeginUpdate();
olstpeople.SetObjects(lst);
olstpeople.EndUpdate();
reader.Close();
DBFunction.CloseConnections();
//picload.Visible = false;
btnfilter.Enabled = true;
}
public string CreateGeneralReport(DateTime?dtstart = null, DateTime?dtend = null)
{
int temp = 0;
SqlParameter date1 = new SqlParameter("@dtstart", dtstart.Value);
SqlParameter date2 = new SqlParameter("@dtend", dtend.Value);
string sfilter = " CAST(CURRENT_TIMESTAMP AS DATE) = CAST(date AS DATE) ";
if (dtstart != null && dtend != null)
{
sfilter = " (date between @dtstart and @dtend) ";
}
string uglysql = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(info, '0', '')," +
"'1', ''),'2', ''),'3', ''),'4', ''),'5', ''),'6', ''),'7', ''),'8', ''),'9', ''),'^', '') as clientname";
SqlDataReader reader = DBFunction.ExecuteReader("select name,users.id as uid," + uglysql + ",action,userid,date,level from log LEFT JOIN USERS ON users.id=log.userid where " + sfilter + " and (action=" + (int)Log.ActionType.ClientOpen +
" or action=" + (int)Log.ActionType.Login +
// " or action=" + (int)Log.ActionType.PhoneFormOpen + " or action=" + (int)Log.ActionType.GoodDateCall +
// " or action=" + (int)Log.ActionType.StartDate + " or action=" + (int)Log.ActionType.FailCall +
/* " or action=" + (int)Log.ActionType.FailDateCall */ ")", date1, date2);
sw = new Dictionary <Log.ActionType, KeyValueClass>();
sw.Add(Log.ActionType.ClientOpen, new KeyValueClass("", 0));
sw.Add(Log.ActionType.Login, new KeyValueClass("", 0));
// sw.Add(Log.ActionType.PhoneFormOpen, new KeyValueClass("", 0));
// sw.Add(Log.ActionType.GoodDateCall, new KeyValueClass("", 0));
// sw.Add(Log.ActionType.StartDate, new KeyValueClass("", 0));
// sw.Add(Log.ActionType.FailCall, new KeyValueClass("", 0));
// sw.Add(Log.ActionType.FailDateCall, new KeyValueClass("", 0));
user = new Dictionary <string, KeyValueClass>();
client = new Dictionary <string, KeyValueClass>();
while (reader.Read())
{
switch ((Log.ActionType)reader["action"])
{
case Log.ActionType.Login:
if (sw.ContainsKey(Log.ActionType.Login))
{
temp = (int)sw[Log.ActionType.Login].Value;
sw[Log.ActionType.Login].Value = ++temp;
sw[Log.ActionType.Login].Text += "<tr><td>" + reader["name"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
}
// else //////// need to create default value for all types of action
// {
// sw.Add(Log.ActionType.Login, new KeyValueClass("<tr><td>" + reader["name"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>",
// 1));
// }
if (user.ContainsKey(reader["name"].ToString()))
{
TempData tobj = new TempData();
tobj = (TempData)user[reader["name"].ToString()].Value;
tobj.opensw++;
tobj.openswdata += "<tr><td>" + reader["name"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
user[reader["name"].ToString()].Value = tobj;
}
else
{
TempData tobj = new TempData();
tobj.opensw++;
tobj.openswdata += "<tr><td>" + reader["name"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
user.Add(reader["name"].ToString(), new KeyValueClass("",
tobj));
}
break;
//case Log.ActionType.PhoneFormOpen:
// temp = (int)sw[Log.ActionType.PhoneFormOpen].Value;
// sw[Log.ActionType.PhoneFormOpen].Value = ++temp;
// sw[Log.ActionType.PhoneFormOpen].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// if (user.ContainsKey(reader["name"].ToString()))
// {
// TempData tobj = new TempData();
// tobj = (TempData)user[reader["name"].ToString()].Value;
// tobj.opentel++;
// tobj.openteldata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user[reader["name"].ToString()].Value = tobj;
// }
// else
// {
// TempData tobj = new TempData();
// tobj.opentel++;
// tobj.openteldata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user.Add(reader["name"].ToString(), new KeyValueClass("",
// tobj));
// }
// break;
case Log.ActionType.ClientOpen:
temp = (int)sw[Log.ActionType.ClientOpen].Value;
sw[Log.ActionType.ClientOpen].Value = ++temp;
sw[Log.ActionType.ClientOpen].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
if (user.ContainsKey(reader["name"].ToString()))
{
TempData tobj = new TempData();
tobj = (TempData)user[reader["name"].ToString()].Value;
tobj.openclient++;
tobj.openclientdata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
user[reader["name"].ToString()].Value = tobj;
}
else
{
TempData tobj = new TempData();
tobj.openclient++;
tobj.openclientdata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
user.Add(reader["name"].ToString(), new KeyValueClass("",
tobj));
}
break;
//case Log.ActionType.GoodDateCall:
// temp = (int)sw[Log.ActionType.GoodDateCall].Value;
// sw[Log.ActionType.GoodDateCall].Value = ++temp;
// sw[Log.ActionType.GoodDateCall].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// if (user.ContainsKey(reader["name"].ToString()))
// {
// TempData tobj = new TempData();
// tobj = (TempData)user[reader["name"].ToString()].Value;
// tobj.datecall++;
// tobj.datecalldatat += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user[reader["name"].ToString()].Value = tobj;
// }
// else
// {
// TempData tobj = new TempData();
// tobj.datecall++;
// tobj.datecalldatat += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user.Add(reader["name"].ToString(), new KeyValueClass("",
// tobj));
// }
// break;
//case Log.ActionType.StartDate:
// temp = (int)sw[Log.ActionType.StartDate].Value;
// sw[Log.ActionType.StartDate].Value = ++temp;
// sw[Log.ActionType.StartDate].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// if (user.ContainsKey(reader["name"].ToString()))
// {
// TempData tobj = new TempData();
// tobj = (TempData)user[reader["name"].ToString()].Value;
// tobj.startdate++;
// tobj.startdatedata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user[reader["name"].ToString()].Value = tobj;
// }
// else
// {
// TempData tobj = new TempData();
// tobj.startdate++;
// tobj.startdatedata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user.Add(reader["name"].ToString(), new KeyValueClass("",
// tobj));
// }
// break;
//case Log.ActionType.FailDateCall:
// temp = (int)sw[Log.ActionType.StartDate].Value;
// sw[Log.ActionType.FailDateCall].Value = ++temp;
// sw[Log.ActionType.FailDateCall].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// if (user.ContainsKey(reader["name"].ToString()))
// {
// TempData tobj = new TempData();
// tobj = (TempData)user[reader["name"].ToString()].Value;
// tobj.callfail++;
// tobj.callfaildata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user[reader["name"].ToString()].Value = tobj;
// }
// else
// {
// TempData tobj = new TempData();
// tobj.callfail++;
// tobj.callfaildata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user.Add(reader["name"].ToString(), new KeyValueClass("",
// tobj));
// }
// break;
//case Log.ActionType.FailCall:
// temp = (int)sw[Log.ActionType.StartDate].Value;
// sw[Log.ActionType.FailDateCall].Value = ++temp;
// sw[Log.ActionType.FailDateCall].Text += "<tr><td>" + reader["name"].ToString() + "<td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// if (user.ContainsKey(reader["name"].ToString()))
// {
// TempData tobj = new TempData();
// tobj = (TempData)user[reader["name"].ToString()].Value;
// tobj.callnoanswer++;
// tobj.callnoanswerdata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user[reader["name"].ToString()].Value = tobj;
// }
// else
// {
// TempData tobj = new TempData();
// tobj.callnoanswer++;
// tobj.callnoanswerdata += "<tr><td>" + reader["clientname"].ToString() + "</td>" + "<td>" + reader["date"].ToString() + "</td></tr>";
// user.Add(reader["name"].ToString(), new KeyValueClass("",
// tobj));
// }
// break;
}
}
reader.Close();
return(AnalyzeData());
}
public string CreateDatesReport(DateTime dt_start, DateTime dt_end)
{
string html = "";
int dates = 0;
int dates_unpaid = 0;
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "";
bool paid = false;
string nopay = "<span class='label label-danger'> לא שילם </span>";
string lbl = "";
string sql = "select date,info,action,userid,name,replace(left(info,CHARINDEX(N'^',info,0)),'^','') as clientname" +
" from Log left join users on userid = users.id left" +
" join RegisterInfo on relatedid = replace(substring(info, CHARINDEX('^', info), 6), '^', '')" +
" where (action = 13 or action = 10) and replace(substring(info, CHARINDEX('^', info), 6), '^', '') > 2750 ";
if (dt_start != null && dt_end != null)
{
sql += " and date between @dt_start and @dt_end";
moreinfo = " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
sql += " order by action,date desc";
html += CreateHtmlReport("פגישות", moreinfo);
html += Properties.Resources.ShadchanStartDatesHeader;
SqlDataReader reader = DBFunction.ExecuteReader(sql, prms);
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
paid = reader["paid"] as bool? ?? false;
if (!paid)
{
lbl = nopay; dates_unpaid++;
}
else
{
lbl = "";
}
//switch (a_type)
//{
// case Log.ActionType.GoodDateCall:
// html += START_TABLE_ROW + "<td class='success'>" + reader["clientname"] + " " + lbl + "</td>" +
// CreateCol("class='success'", reader["name"])
// + CreateCol("class='success'", reader["date"]) + END_TABLE_ROW;
// dates++;
// break;
// case Log.ActionType.StartDate:
// html += START_TABLE_ROW + "<td class='active'>" + reader["clientname"] + " " + lbl + "</td>" +
// CreateCol("class='active'", reader["name"])
// + CreateCol("class='active'", reader["date"]) + END_TABLE_ROW;
// dates++;
// break;
//}
}
reader.Close();
html += Schiduch.Properties.Resources.ShadchanStartDatesFooter;
html += "<h1>הפגישות שנעשו בזמן הזה : <label class='label label-default'>" + dates.ToString() + "</label></h1>";
html += "<h1>פגישות לכאלה שלא שילמו : <label class='label label-default'>" + dates_unpaid.ToString() + "</label></h1>";
html += "</div></div></body></html>";
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}
public string CreateUserReport(string username, int userid, DateTime dt_start, DateTime dt_end)
{
// string clienttableheader = global::Schiduch.Properties.Resources.ClientTableHeader;
string html = "";
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "<b>שדכן:</b> " + username + ", <b>מזהה שדכן:</b> " + userid.ToString() + "</br>";
string sqlLog = "select firstname + ' ' + lastname as allname, peoples.ID ,action,userid,date " +
" from log JOIN peoples ON log.info not like '' and SUBSTRING(log.info, CHARINDEX('^',log.Info)+1,DATALENGTH(log.info) - 1)= Peoples.ID" +
" where UserId = " + userid + " and action = 2 ";
if (dt_start != null && dt_end != null)
{
sqlLog += " and date between @dt_start and @dt_end";
moreinfo += " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
html += CreateHtmlReport("שדכן", moreinfo);
html += RegisterDateToReport(userid);
SqlDataReader reader = DBFunction.ExecuteReader(sqlLog, prms);
html += "<u><h2>פירוט</h2></u><div class='row' style='width:80%'>";
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
switch (a_type)
{
case Log.ActionType.ClientOpen:
clientlog_open += START_TABLE_ROW + CreateCol("", reader["allname"]) + CreateCol(null, reader["date"]) + END_TABLE_ROW;
InsertUserAction(reader["allname"].ToString(), (int)reader["ID"], ShiduchActivity.ActionType.openForms, ShiduchActivity.ActionStatus.completed);
count_openclient++;
break;
}
}
reader.Close();
reader = ShiduchActivity.GetActivities(false, null, false, false, true, dt_start, dt_end, 0, userid);
while (reader.Read())
{
ShiduchActivity.ActionType action = (ShiduchActivity.ActionType) int.Parse(reader["Action"].ToString());
switch (action)
{
case ShiduchActivity.ActionType.proposal:
userAction_proposal += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.proposal, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientProposal++;
break;
case ShiduchActivity.ActionType.date:
userAction_date += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.date, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDate++;
break;
case ShiduchActivity.ActionType.details:
userAction_details += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.details, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDetails++;
break;
case ShiduchActivity.ActionType.other:
userAction_other += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["FullNameA"].ToString(), (int)reader["PeopleId"], ShiduchActivity.ActionType.other, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientOther++;
break;
}
}
reader.Close();
clientlog_open += "</tbody></table></div>";
userAction_proposal += "</tbody></table></div>";
userAction_date += "</tbody></table></div>";
userAction_details += "</tbody></table></div>";
userAction_other += "</tbody></table></div>";
html += clientlog_open + userAction_proposal + userAction_date + userAction_details + userAction_other;
html += "</div><hr>"; // end div of all info tables and create hr
html += Schiduch.Properties.Resources.ClientSumAction.Replace("שדכן", "לקוח");
html += SumClientsTableList();
html += "</tbody></table><hr><u><h2>סך הכל</h2></u>";
html += SumClientsData();
html += global::Schiduch.Properties.Resources.ReportEnd;
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}
public static SqlDataReader GetActivities(bool my, People p = null, bool all = false, bool inCare = false, bool allForManagerReport = false,
DateTime date1 = default(DateTime), DateTime date2 = default(DateTime), int peopleId = 0,
int userId = 0, bool MyAllForDiary = false, int action = -1, int status = -1, bool DiaryManager = false)
{
List <ShiduchActivity> list = new List <ShiduchActivity>();
ShiduchActivity item;
if (p == null)
{
p = new People();
}
//פעילויות שלי
string select, from, where = "", sql, orderBy = "", sActionNUmber = " s.Action<4 ";
select = "select s.*,p2.FirstName+' '+p2.LastName as FullNameB ";
from = "from ShiduchActivity s left join Peoples p2 on s.IdSideB=p2.ID ";
where = " where s.PeopleId=" + p.ID;
if (my)//פעיליות שהשדכן עשה לכרטיס זה
{
if (GLOBALVARS.MyUser.Control == User.TypeControl.Admin || GLOBALVARS.MyUser.Control == User.TypeControl.Manger)
{
where += " and " + sActionNUmber;
}
else
{
where += " and s.UserId=" + GLOBALVARS.MyUser.ID + " and s.action<4";
}
}
if (all)//פעילויות של כלל השדכנים לכרטיס זה לא כולל פגישה
{
select += ",u.Name ";
from += " inner join users u on u.id =s.userid ";
if (GLOBALVARS.MyUser.Control != User.TypeControl.Admin && GLOBALVARS.MyUser.Control != User.TypeControl.Manger)
{
where += " and s.Action in(0,2,3,5)";
}
}
if (inCare)
{
select += " ,p.FirstName+' '+p.LastName as FullNameA,r.Date as remindDate ";
from += " left join Peoples p on s.peopleid = p.id inner join ReminderActivity r on r.IdActivity=s.Id ";
where = " where s.UserId=" + GLOBALVARS.MyUser.ID + " and s.action<4 and s.Status=0 and r.Done=0 and r.Date<=getdate() ";
}
if (allForManagerReport)
{
if (DiaryManager)
{
sActionNUmber = "s.action<5";
if (action > 0)//-1 לא נבחר 0 הכל
{
sActionNUmber = "s.Action=" + (action - 1);
}
//אם זה מנהל שבודק פעילות של שדכן מסוים אז הוא יכול לראות את הכל
if (status > 0)//אם לא בחר סטטוס מסוים אז מביאים את הכל
{
where += " and s.Status=" + (status - 1);
}
}
select += " ,p.FirstName+' '+p.LastName as FullNameA,u.Name,u.id as userID," +
"r.Date as remindDate,r.id as remindID ,r.IdUser as remindIdUser ,Done ";
from += " left join Peoples p on s.peopleid = p.id inner join users u on u.id =s.userid" +
" inner join ReminderActivity r on r.IdActivity=s.Id ";
where = " where " + sActionNUmber + " and s.Date between'" + date1.ToString("yyyy-MM-dd h:mm tt") +
"' and '" + date2.ToString("yyyy-MM-dd h:mm tt") + "' ";
if (peopleId != 0)
{
where += " and s.PeopleId=" + peopleId;
}
if (userId != 0)
{
where += " and s.UserId=" + userId;
}
orderBy = " order by s.Action,s.Status ";
}
if (MyAllForDiary)
{
select += ",r.Date as remindDate,r.Done,p.FirstName+' '+p.LastName as FullNameA,u.Name ";
from += " left join Peoples p on s.peopleid = p.id inner join ReminderActivity r on" +
" r.IdActivity=s.Id inner join users u on u.id =s.userid ";
where = " where s.UserId=" + userId + " and s.Date between'" + date1.ToString("yyyy-MM-dd h:mm tt")
+ "' and '" + date2.ToString("yyyy-MM-dd h:mm tt") + "' ";
if (action > 0)//-1 לא נבחר 0 הכל
{
where += " and s.Action=" + (action - 1);
}
else if (DiaryManager)//אם זה מנהל שבודק פעילות של שדכן מסוים אז הוא יכול לראות את הכל
{
where += " and s.action<5";
}
else
{
where += " and s.action<4";
}
if (status > 0)//אם לא בחר סטטוס מסוים אז מביאים את הכל
{
where += " and s.Status=" + (status - 1);
}
}
sql = select + from + where + orderBy;
SqlDataReader reader = DBFunction.ExecuteReader(sql);
return(reader);
}
public void SForm_FormClosed(object sender, FormClosedEventArgs e)
{
ShiduchActivityForm f = (sender as ShiduchActivityForm);
ShiduchActivityForm form2;
if (f.save)
{
SaveOpenReminder = true;
if (f.OpenNewActivity)
{
ShiduchActivity s = new ShiduchActivity();
s.UserId = f.Activity.UserId;
s.PeopleId = f.Activity.PeopleId;
s.IdSideB = f.Activity.IdSideB;
form2 = new ShiduchActivityForm(s, f.MyPeople);
form2.isNew_Active_From_Complete_Active = true;
form2.Show();
form2.FormClosed += SForm_FormClosed;
}
}
if ((sender as ShiduchActivityForm).OpenSideB)//אם רוצה לפתוח פעילות של הצד השני
{
//f.Close();
People person = f.Shiduch;
People shiduch = f.MyPeople;
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.UserId=" + f.Activity.UserId + " and PeopleId=" +
person.ID + " and IdSideB=" + shiduch.ID + " and Action=" + f.Activity.Action +
" and abs(DATEDIFF(day,s.Date,'" + f.Activity.Date.ToString("yyyy-MM-dd h:mm tt") + "'))" +
" between 0 and 15";
// " and ( convert(varchar(10), s.Date, 103)='" + f.Activity.Date.ToShortDateString() + "' "+
//"or convert(varchar(10), s.Date, 103)>'" + f.Activity.Date.ToShortDateString() +
// "' or s.Date >= DATEADD(DAY, -14,'" + f.Activity.Date.ToString("yyyy-MM-dd h:mm tt") + "'))";
SqlDataReader reader = DBFunction.ExecuteReader(sql);
ShiduchActivity s = new ShiduchActivity();
string notesSide = removeFromString(f.Activity.NotesSummary);
if (reader.Read())
{
ShiduchActivity.readerToShiduchActivity(ref reader, ref s);
s.NotesSummary = removeFromString(s.NotesSummary) + "=====צד ב'=====" + Environment.NewLine + notesSide;
form2 = new ShiduchActivityForm(s, person, false, true);
}
else
{
s.IdSideB = shiduch.ID;
s.Action = f.Activity.Action;
s.UserId = f.Activity.UserId;
s.NotesSummary += "\r\n =====צד ב'=====\r\n" + notesSide;
form2 = new ShiduchActivityForm(s, person, true, false, true);
}
reader.Close();
form2.Show();
form2.FormClosed += SForm_FormClosed;
//אם יש כבר פעילות דומה אז לפתוח אותה
//אחרת לפתוח טופס חדש של פעילות
}
if ((sender as ShiduchActivityForm).save)
{
//foreach (Form frm in Application.OpenForms)
//{
// if (frm.GetType() ==typeof( MainForm))
// {
// (frm as MainForm).LoadReminder();
// }
//}
//saveReminder = true;
}
}
public static ArrayList GetMatches(People people)
{
ArrayList list = new ArrayList();
mydic_whoami.Clear();
mydic_whoiwant.Clear();
foreach (DictinorayRow row in DictinorayRow.DictinorayList)
{
int temp = row.IsMatchWord(people.Details.WhoAmI);
if (temp == 1 || temp == -1)
{
if (temp == -1)
{
row.ChangeToUpSitewords();
}
mydic_whoami.Add(row);
mydic_not_type.Add(row);
}
}
foreach (DictinorayRow row in DictinorayRow.DictinorayList)
{
int temp = row.IsMatchWord(people.Details.WhoAmI);
if (temp == 1 || temp == -1)
{
if (temp == -1)
{
row.ChangeToUpSitewords();
}
mydic_whoiwant.Add(row);
mydic_not_type.Add(row);
}
}
MatchesChecks xmatch = new MatchesChecks();
xmatch.p = people;
string show = " AND SHOW <> 8 AND (show <2 or (show=5 and chadchan like '%{" + GLOBALVARS.MyUser.ID + "}%') or (show=4 and chadchan like '%{" + GLOBALVARS.MyUser.ID + "}%'))";
if (GLOBALVARS.MyUser.Control == User.TypeControl.Manger || GLOBALVARS.MyUser.Control == User.TypeControl.Admin)
{
show = " and show <> 8";
}
string sql = "select zerem,looks,show,chadchan,whoami,whoiwant,[peopledetails].[relatedid],[Peoples].[ID],FirstName,facecolor,sexs,Lastname,tall,status,fat,age,show,notes from peoples inner join peopledetails on ID=relatedid ";
sql += xmatch.GetSqlCheck() + show;
SqlDataReader reader = DBFunction.ExecuteReader(sql);
while (reader.Read())
{
Match result = new Match();
result.Name = reader["firstname"].ToString() + " " + reader["lastname"].ToString();
result.ID = (int)reader["id"];
//FAT CHECK
result.FatCheckString = xmatch.FatCheck(reader["fat"].ToString(), ref result.FatMatchLevel);
//FACECOLOR CHECK
result.FaceColorCheckString = xmatch.FaceColorCheck(reader["facecolor"].ToString(), ref result.FaceColorMatchLevel);
//Looks CHECK
result.LooksCheckString = xmatch.LooksCheck(reader["looks"].ToString(), ref result.LooksMatchLevel);
//FromDic CHECK
result.DicCheckString.AddRange(xmatch.CheckFromDic(reader["looks"].ToString(), "LookChange", ref result.DicMatchLevel));
// result.DicCheckString.AddRange(xmatch.CheckFromDic(reader["zerem"].ToString(), "ZeremChange", ref result.DicMatchLevel));
result.DicCheckString.AddRange(xmatch.CheckFromDic(reader["facecolor"].ToString(), "FaceChange", ref result.DicMatchLevel));
// STATUS CHECK
result.StatusCheckString = xmatch.StatusCheck(reader["status"].ToString(), ref result.MatchLevel);
//EDA CHECK NEEEED TO FIXXX
result.ZeremOfEdaString = xmatch.ZeremCheck(reader["zerem"].ToString(), ref result.ZeremOfEdaMatchLevel);
//WHOAMI + LOOKFOR CHECK
result.WhoamiCheckString.AddRange(xmatch.FindPropties(reader["whoiwant"] as string, ref result.WhoAmIMatchLevel, false));
result.LookForCheckString.AddRange(xmatch.FindPropties(reader["whoami"] as string, ref result.LookForMatchLevel, true));
result.MatchLevel += result.FaceColorMatchLevel + result.FatMatchLevel + result.LooksMatchLevel +
result.ZeremOfEdaMatchLevel + result.LookForMatchLevel + result.WhoAmIMatchLevel +
result.DicMatchLevel;
list.Add(result);
}
reader.Close();
return(list);
}
public void LoadShadcanim()
{
string sql;
// string txt="";
int controlhide = 1;
GLOBALVARS.Shadchanim = new ArrayList();
GLOBALVARS.Users = new List <User>();
User u;
// cmbusers.Items.Add(new KeyValueClass("הכל", 0));
if (GLOBALVARS.MyUser.Control == User.TypeControl.Admin)
{
controlhide = 100;
}
sql = @"select u.ID,u.name,u.tel,u.email,count(r.ByUser) as d,t1.mdate,t2.mdateexit,Sector
from users u left join RegisterInfo r on u.ID=r.ByUser left join
(select l.UserId, max(l.date) mdate from log l where l.Action=1 group by l.UserId ) t1 on t1.UserId=u.ID left join
(select l.UserId, max(l.DateExit) mdateexit from log l where l.Action=1 group by l.UserId ) t2 on t2.UserId=u.ID
where u.Control<" + controlhide + @"
group by u.ID,u.name,u.tel,u.email,t1.mdate,t2.mdateexit,Sector
order by d desc";
SqlDataReader reader;
reader = DBFunction.ExecuteReader(sql);
lstchadcan.Items.Clear();
ConnetKnow = 0;
while (reader != null && reader.Read())
{
u = new User();
u.Name = (string)reader["name"];
u.ID = (int)reader["ID"];
u.Tel = (string)reader["tel"];
u.Email = (string)reader["email"];
u.Sector = (string)reader["Sector"];
if (u.Sector.Length > 0)
{
string[] s = u.Sector.Split(',');
for (int i = 0; i < s.Length; i++)
{
if (u.SectorView.Length > 0)
{
u.SectorView += ", ";
}
u.SectorView += GLOBALVARS.Sectors.ToArray()[int.Parse(s[i])].SectorName;
}
}
GLOBALVARS.Users.Add(u);
KeyValueClass temp = new KeyValueClass((string)reader["name"], (int)reader["ID"]);
GLOBALVARS.Shadchanim.Add(temp);
lstchadcan.Items.Add(new ListViewItem(new string[] { (string)reader["name"],
(string)reader["tel"], (string)reader["email"], reader["ID"].ToString(),
reader["d"].ToString(), Connect(ref reader) }, 2));
}
reader.Close();
DBFunction.CloseConnections();
if (controlhide != 1)
{
lblconnect.Visible = true;
lblconnect.Text += ConnetKnow + " מתוך " + GLOBALVARS.Shadchanim.Count;
}
}
public string CreateClientReport(string clientname, int userid, DateTime dt_start, DateTime dt_end)
{
string clienttableheader = global::Schiduch.Properties.Resources.ClientTableHeader;
string html = "";
SqlParameter[] prms = new SqlParameter[2];
prms[0] = new SqlParameter("dt_start", dt_start);
prms[1] = new SqlParameter("dt_end", dt_end);
string moreinfo = "<b>לקוח:</b> " + clientname + ", <b>מזהה לקוח:</b> " + userid.ToString() + "</br>";
string sqlLog = "select name,users.id as xid,info,action,userid,date,level from log LEFT JOIN USERS ON users.id=log.userid where log.info like '%" + userid.ToString() + "'";
if (dt_start != null && dt_end != null)
{
sqlLog += " and date between @dt_start and @dt_end";
moreinfo += " מתאריך " + dt_start.ToShortDateString() + " עד לתאריך " + dt_end.ToShortDateString();
}
sqlLog += " order BY log.ACTION ";
html += CreateHtmlReport("לקוח", moreinfo);
html += RegisterDateToReport(userid);
SqlDataReader reader = DBFunction.ExecuteReader(sqlLog, prms);
html += "<u><h2>פירוט</h2></u><div class='row' style='width:80%'>";
while (reader.Read())
{
Log.ActionType a_type = (Log.ActionType) int.Parse(reader["ACTION"].ToString());
switch (a_type)
{
case Log.ActionType.ClientOpen:
clientlog_open += START_TABLE_ROW + CreateCol("", reader["name"]) + CreateCol(null, reader["date"]) + END_TABLE_ROW;
InsertUserAction(reader["name"].ToString(), (int)reader["userid"], ShiduchActivity.ActionType.openForms, ShiduchActivity.ActionStatus.completed);
count_openclient++;
break;
}
}
reader.Close();
reader = ShiduchActivity.GetActivities(false, null, false, false, true, dt_start, dt_end, userid);
while (reader.Read())
{
ShiduchActivity.ActionType action = (ShiduchActivity.ActionType) int.Parse(reader["Action"].ToString());
switch (action)
{
case ShiduchActivity.ActionType.proposal:
clientAction_proposal += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.proposal, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientProposal++;
break;
case ShiduchActivity.ActionType.date:
clientAction_date += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.date, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDate++;
break;
case ShiduchActivity.ActionType.details:
clientAction_details += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.details, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientDetails++;
break;
case ShiduchActivity.ActionType.other:
clientAction_other += START_TABLE_ROW + CreateColAction(ref reader) + END_TABLE_ROW;
InsertUserAction(reader["Name"].ToString(), (int)reader["userID"], ShiduchActivity.ActionType.other, (ShiduchActivity.ActionStatus)(int) reader["Status"]);
count_clientOther++;
break;
}
}
reader.Close();
clientlog_open += "</tbody></table></div>";
clientAction_proposal += "</tbody></table></div>";
clientAction_date += "</tbody></table></div>";
clientAction_details += "</tbody></table></div>";
clientAction_other += "</tbody></table></div>";
html += clientlog_open + clientAction_proposal + clientAction_date + clientAction_details + clientAction_other;
html += "</div><hr>"; // end div of all info tables and create hr
html += Schiduch.Properties.Resources.ClientSumAction;
html += SumClientsTableList();
html += "</tbody></table><hr><u><h2>סך הכל</h2></u>";
html += SumClientsData();
html += global::Schiduch.Properties.Resources.ReportEnd;
using (TextWriter txtwrite = File.CreateText(path))
{
txtwrite.Write(html);
}
return(path);
}
