public static void Delete(int person_id)
{
try
{
Person p = PersonDB.GetByID(person_id);
if (p != null)
{
DBBase.ExecuteNonResult("DELETE FROM Person WHERE person_id = " + person_id.ToString() + "; DBCC CHECKIDENT(Person,RESEED,1); DBCC CHECKIDENT(Person);");
if (EntityDB.NumForeignKeyDependencies(p.EntityID) == 0)
{
EntityDB.Delete(p.EntityID, false);
}
}
}
catch (System.Data.SqlClient.SqlException sqlEx)
{
if (sqlEx.Errors.Count > 0 && sqlEx.Errors[0].Number == 547) // Assume the interesting stuff is in the first error
{
throw new ForeignKeyConstraintException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, sqlEx);
}
else
{
throw;
}
}
}
protected void GrdSummaryReport_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataTable dt = Session["data_bookingswithoutsmsoremail"] as DataTable;
bool tblEmpty = (dt.Rows.Count == 1 && dt.Rows[0][0] == DBNull.Value);
if (!tblEmpty && e.Row.RowType == DataControlRowType.DataRow)
{
Label lblId = (Label)e.Row.FindControl("lblId");
DataRow[] foundRows = dt.Select("patient_id=" + lblId.Text);
DataRow thisRow = foundRows[0];
Patient patient = PatientDB.Load(thisRow);
patient.Person = PersonDB.Load(thisRow);
patient.Person.Title = IDandDescrDB.Load(thisRow, "t_title_id", "t_descr");
Utilities.AddConfirmationBox(e);
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
Utilities.SetEditRowBackColour(e, System.Drawing.Color.LightGoldenrodYellow);
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
}
}
protected void UpdateReferrersAtOrgList()
{
if (ddlOrgsList.SelectedIndex == -1)
{
return;
}
lblProvidersOf.Visible = true;
lblSelectedOrg.Text = ddlOrgsList.Items[ddlOrgsList.SelectedIndex].Text;
DataTable tbl = RegisterReferrerDB.GetDataTable_ReferrersOf(Convert.ToInt32(ddlOrgsList.Items[ddlOrgsList.SelectedIndex].Value));
RegisterReferrer[] regRefs = new RegisterReferrer[tbl.Rows.Count];
for (int i = 0; i < tbl.Rows.Count; i++)
{
regRefs[i] = RegisterReferrerDB.Load(tbl.Rows[i]);
regRefs[i].Referrer = ReferrerDB.Load(tbl.Rows[i]);
regRefs[i].Referrer.Person = PersonDB.Load(tbl.Rows[i], "", "person_entity_id");
regRefs[i].Referrer.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "title_id", "descr");
}
string list = tbl.Rows.Count == 0 ? string.Empty : @"<table cellpadding=""0"" cellspacing=""0"">";
foreach (RegisterReferrer regRef in regRefs)
{
string url = "ReferrerList_DoctorClinicV2.aspx?surname_search=" + regRef.Referrer.Person.Surname + @"&surname_starts_with=1" + (regRef.ProviderNumber.Length > 0 ? "&provider_nbr_search=" + regRef.ProviderNumber + "&provider_nbr_starts_with=1" : "");
string href = @"<a href=""" + url + @""">" + regRef.Referrer.Person.FullnameWithoutMiddlename + "</a>";
list += "<tr><td>" + href + @"</td><td style=""width:8px;""> </td><td> Prov Nbr: " + (regRef.ProviderNumber.Trim().Length == 0 ? "[None]" : regRef.ProviderNumber) + "</td></tr>";
}
list += tbl.Rows.Count == 0 ? string.Empty : "</table>";
lblProvidersOfSelectedOrg.Text = list.Length > 0 ? list : "None";
}
public static Hashtable GetHashtableByReferrer(bool incDeleted = false)
{
Hashtable hashtable = new Hashtable();
DataTable tbl = DBBase.ExecuteQuery(JoinedSql(incDeleted, incDeleted, incDeleted)).Tables[0];
foreach (DataRow row in tbl.Rows)
{
if (row["organisation_id"] == DBNull.Value)
{
continue;
}
Referrer r = ReferrerDB.Load(row);
r.Person = PersonDB.Load(row, "", "person_entity_id");
r.Person.Title = IDandDescrDB.Load(row, "title_id", "descr");
Organisation o = OrganisationDB.Load(row, "", "organisation_entity_id", "organisation_is_deleted");
if (hashtable[r.ReferrerID] == null)
{
hashtable[r.ReferrerID] = new ArrayList();
}
((ArrayList)hashtable[r.ReferrerID]).Add(o);
}
return(hashtable);
}
public static DataTable GetDataTable_ReferrersOf(int organistion_id, bool showDeletedRefs = false, bool showDeletedRegRefs = false, string matchSurname = "", bool searchSurnameOnlyStartsWith = false)
{
matchSurname = matchSurname.Replace("'", "''");
string sql = @"SELECT
rr.register_referrer_id, rr.organisation_id, rr.referrer_id, rr.provider_number, rr.report_every_visit_to_referrer,rr.batch_send_all_patients_treatment_notes,rr.date_last_batch_send_all_patients_treatment_notes,rr.register_referrer_date_added,
r.person_id, r.referrer_date_added, r.is_deleted as referrer_is_deleted,
" + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @",
t.title_id, t.descr,
r.is_deleted as referrer_is_deleted, rr.is_deleted as register_referrer_is_deleted
FROM
RegisterReferrer AS rr
LEFT OUTER JOIN Referrer AS r ON rr.referrer_id = r.referrer_id
LEFT OUTER JOIN Person AS p ON r.person_id = p.person_id
LEFT OUTER JOIN Title t ON t.title_id = p.title_id
WHERE
" + (showDeletedRegRefs ? "" : "rr.is_deleted = 0 AND ") +
(showDeletedRefs ? "" : "r.is_deleted = 0 AND ") +
"rr.organisation_id = " + organistion_id.ToString() +
((matchSurname.Length > 0 && !searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '%" + matchSurname + "%'" : "") +
((matchSurname.Length > 0 && searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '" + matchSurname + "%'" : "") + @"
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
//[Authorize]
public ActionResult FileAssociate(string fileName)
{
Session["currentFileProcessed"] = fileName;
ViewBag.Message = "It's FileAssociate Time";
// Populate the list of Persons
/* Following code works */ // nodeList = PersonDB.GetPersons();
var artifactList = PersonDB.GetPersonsNames();
int ttlNumOfNames = artifactList.Count;
List <string> artifactNames = new List <string>();
artifactNames = PersonDB.GetNamesByFile(fileName);
//check the box on the screen for Persons currently associated with the file
int ttlNumberOfNames = artifactNames.Count;
for (int i = 0; i < ttlNumberOfNames; i++)
{
for (int x = 0; x < ttlNumOfNames; x++)
{
if (artifactNames[i] == artifactList[x].Name)
{
artifactList[x].IfChecked = true; // check the checkbox in artifactNames list
}
}
}
return(View(artifactList));
}
public static PatientReferrer GetByID(int patient_referrer_id)
{
//string sql = "SELECT patient_referrer_id,patient_id,register_referrer_id,patient_referrer_date_added,is_debtor,is_active FROM PatientReferrer WHERE patient_referrer_id = " + patient_referrer_id.ToString();
string sql = JoinedSQL() + " AND patient_referrer_id = " + patient_referrer_id.ToString();
DataTable tbl = DBBase.ExecuteQuery(sql).Tables[0];
if (tbl.Rows.Count == 0)
{
return(null);
}
else
{
PatientReferrer pr = Load(tbl.Rows[0]);
pr.Patient = PatientDB.Load(tbl.Rows[0]);
pr.Patient.Person = PersonDB.Load(tbl.Rows[0], "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(tbl.Rows[0], "patient_person_title_title_id", "patient_person_title_descr");
if (tbl.Rows[0]["pr_register_referrer_id"] != DBNull.Value)
{
pr.RegisterReferrer = RegisterReferrerDB.Load(tbl.Rows[0]);
pr.RegisterReferrer.Referrer = ReferrerDB.Load(tbl.Rows[0]);
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(tbl.Rows[0], "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(tbl.Rows[0], "referrer_person_title_title_id", "referrer_person_title_descr");
pr.RegisterReferrer.Organisation = OrganisationDB.Load(tbl.Rows[0], "organisation_");
}
if (tbl.Rows[0]["pr_organisation_id"] != DBNull.Value)
{
pr.Organisation = OrganisationDB.Load(tbl.Rows[0], "nonepcorg_");
}
return(pr);
}
}
public PersonResponse GetPersonList()
{
PersonResponse personResponse = new PersonResponse();
try
{
//WebRequestLogManager objWebRequestLogManager = new WebRequestLogManager();
//decimal requestId = objWebRequestLogManager.AddRequest(string.Format("Project {0}, Page {1}, Method {2}, currentCulture {3}, clientID {4}, statusids {5}, partnertypecodes {6}", "ELSBackendBLL", "masterManager.cs", "GetAllPartners", culture, clientId, statusids, partnertypecodes), 0, CommonFunctions.RequestSource.WEB.ToString());
PersonDB personDB = new PersonDB();
personResponse = personDB.GetPersonList();
//objWebRequestLogManager.AddResponse(masterResponse.Status.ToString(), requestId, 0);
}
//catch (CustomException ex)
//{
// masterResponse = new MasterResponse(ex.ResponseStatus);
//}
catch (Exception ex)
{
//var Error = "Exception : " + ex.Message + ", Inner Exception : " + (ex.InnerException == null ? "" : ex.InnerException.Message);
//ExceptionLogDB.LogException("ELSBAL", "MasterManager.cs", "GetAllRoles", CommonFunctions.RequestSource.WEB.ToString(), Error);
personResponse = new PersonResponse(BaseResponse.ResponseStatus.Error);
}
return(personResponse);
}
public static DataTable GetDataTable_StaffOf(int organistion_id, bool inc_deleted = false, bool only_providers = false, bool excl_external = true)
{
string sql = @"SELECT
r.register_staff_id,r.register_staff_date_added,r.organisation_id,r.provider_number AS registration_provider_number,r.main_provider_for_clinic,
r.excl_sun, r.excl_mon, r.excl_tue, r.excl_wed, r.excl_thu, r.excl_fri, r.excl_sat, r.is_deleted as registration_is_deleted,
s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number,
s.is_fired, s.is_commission, s.commission_percent,
s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external,
s.staff_date_added, s.start_date, s.end_date, s.comment,
s.num_days_to_display_on_booking_screen,
s.show_header_on_booking_screen,
s.bk_screen_field_id,
s.bk_screen_show_key,
s.enable_daily_reminder_sms,
s.enable_daily_reminder_email,
s.hide_booking_notes,
sr.field_id as field_field_id,sr.descr as field_descr,
" + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @",
t.title_id, t.descr
FROM
RegisterStaff AS r
LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id
LEFT OUTER JOIN Person p ON s.person_id = p.person_id
LEFT OUTER JOIN Title t ON t.title_id = p.title_id
LEFT OUTER JOIN Field sr ON s.field_id = sr.field_id
WHERE
s.staff_id > 0 AND s.is_fired = 0 " + (inc_deleted ? "" : " AND r.is_deleted = 0 ") + (!only_providers ? "" : " AND s.is_provider = 1 ") + (!excl_external ? "" : " AND s.is_external = 0 ") + " AND r.organisation_id = " + organistion_id.ToString() + @"
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
public JsonResult ControlLogin(string _email, string _password)
{
try
{
var person = PersonDB.GetInstance().ControlPerson(_email, _password);
if (person != null)
{
HttpContext.Session.SetString("ActivePerson", JsonConvert.SerializeObject(person));
// var projects = ProjectDB.GetInstance().GetAllProject();
// if (projects.Count > 0)
// {
// HttpContext.Session.SetString("SelectedProject", JsonConvert.SerializeObject(projects.FirstOrDefault()));
// }
return(Json(true));
}
return(Json(false));
}
catch (Exception exc)
{
throw exc;
}
}
protected Tuple <Booking, PatientReferrer, bool, string, HealthCard> LoadRow(DataRow row)
{
Booking booking = BookingDB.Load(row, "booking_", true, false);
booking.Offering = OfferingDB.Load(row, "offering_");
PatientReferrer pr = PatientReferrerDB.Load(row, "pr_");
pr.RegisterReferrer = RegisterReferrerDB.Load(row, "regref_");
pr.RegisterReferrer.Referrer = ReferrerDB.Load(row, "referrer_");
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(row, "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(row, "referrer_person_title_title_id", "referrer_person_title_descr");
if (row["organisation_organisation_id"] != DBNull.Value)
{
pr.RegisterReferrer.Organisation = OrganisationDB.Load(row, "organisation_");
}
pr.Patient = PatientDB.Load(row, "patient_");
pr.Patient.Person = PersonDB.Load(row, "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(row, "patient_person_title_title_id", "patient_person_title_descr");
bool refHasEmail = Convert.ToInt32(row["ref_has_email"]) == 1;
string refEmail = row["ref_email"] == DBNull.Value ? null : Convert.ToString(row["ref_email"]);
HealthCard hc = HealthCardDB.Load(row, "hc_");
return(new Tuple <Booking, PatientReferrer, bool, string, HealthCard>(booking, pr, refHasEmail, refEmail, hc));
}
public IActionResult Index()
{
PersonDB PersonDB = new PersonDB(_connectionString);
List <Person> people = PersonDB.GetAll();
return(View(people));
}
public ActionResult Register(string username, string first_name, string last_name, string email, string password, string confirmpassword)
{
if (password != confirmpassword)
{
return(View());
}
if (ModelState.IsValid)
{
// Attempt to register the user
MembershipCreateStatus createStatus;
Membership.CreateUser(username, password, email, null, null, true, null, out createStatus);
if (createStatus == MembershipCreateStatus.Success)
{
FormsAuthentication.SetAuthCookie(username, false /* createPersistentCookie */);
PersonDB stu = new PersonDB();
stu.student_id = username;
stu.first_name = first_name;
stu.last_name = last_name;
stu.email = email;
person.Persons.Add(stu);
person.SaveChanges();
return(RedirectToAction("Init", "Home"));
}
else
{
ModelState.AddModelError("", ErrorCodeToString(createStatus));
}
}
// If we got this far, something failed, redisplay form
return(View());
}
public static DataTable GetDataTable_AllNotInc(Referrer[] excList)
{
string notInList = string.Empty;
foreach (Referrer r in excList)
{
notInList += r.ReferrerID.ToString() + ",";
}
if (notInList.Length > 0)
{
notInList = notInList.Substring(0, notInList.Length - 1);
}
string sql = @"SELECT
r.referrer_id, r.person_id, r.referrer_date_added,
" + PersonDB.GetFields("", "p") + @",
t.title_id, t.descr
FROM
Referrer AS r
LEFT OUTER JOIN Person AS p ON r.person_id = p.person_id
INNER JOIN Title t ON t.title_id = p.title_id
WHERE
r.is_deleted = 0 " + ((notInList.Length > 0) ? " AND r.referrer_id NOT IN (" + notInList + @") " : "") + @"
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
//sql中使用like模糊查询
static void RetrievePersonWithLike()
{
string partName = "b";
List <Person> persons = PersonDB.RetrieveWithLike(partName);
persons.ForEach(n => Console.WriteLine(n.Name));
}
public ActionResult Result()
{
PersonDB stu = person.Persons.Find(User.Identity.Name);
ViewBag.give = item.Items.Find(stu.item_original).name;
ViewBag.get = item.Items.Find(stu.item_now).name;
foreach (var p in person.Persons.ToList())
{
if (p.item_now == stu.item_original)
{
ViewBag.giveto = p.student_id;
break;
}
}
foreach (var p in person.Persons.ToList())
{
if (p.item_original == stu.item_now)
{
ViewBag.getfrom = p.student_id;
break;
}
}
if (stu.item_now == stu.item_original)
{
ViewBag.flag = 0;
}
else
{
ViewBag.flag = 1;
}
return(View());
}
protected void GrdReferrer_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label lblId = (Label)GrdReferrer.Rows[e.RowIndex].FindControl("lblId");
DropDownList ddlTitle = (DropDownList)GrdReferrer.Rows[e.RowIndex].FindControl("ddlTitle");
TextBox txtFirstname = (TextBox)GrdReferrer.Rows[e.RowIndex].FindControl("txtFirstname");
TextBox txtMiddlename = (TextBox)GrdReferrer.Rows[e.RowIndex].FindControl("txtMiddlename");
TextBox txtSurname = (TextBox)GrdReferrer.Rows[e.RowIndex].FindControl("txtSurname");
DropDownList ddlGender = (DropDownList)GrdReferrer.Rows[e.RowIndex].FindControl("ddlGender");
Referrer referrer = ReferrerDB.GetByID(Convert.ToInt32(lblId.Text));
if (referrer != null)
{
if (referrer.Person.Title.ID != Convert.ToInt32(ddlTitle.SelectedValue) ||
referrer.Person.Firstname != Utilities.FormatName(txtFirstname.Text) ||
referrer.Person.Middlename != Utilities.FormatName(txtMiddlename.Text) ||
referrer.Person.Surname != Utilities.FormatName(txtSurname.Text) ||
referrer.Person.Gender != ddlGender.SelectedValue)
{
PersonDB.Update(referrer.Person.PersonID, Convert.ToInt32(ddlTitle.SelectedValue), Utilities.FormatName(txtFirstname.Text), Utilities.FormatName(txtMiddlename.Text), Utilities.FormatName(txtSurname.Text), referrer.Person.Nickname, ddlGender.SelectedValue, referrer.Person.Dob, DateTime.Now);
}
}
GrdReferrer.EditIndex = -1;
FillGrid();
}
public JsonResult GetGorevByBoardId(int _sprintId)
{
try
{
var stepList = StepDB.GetInstance().GetGorevBySprintId(_sprintId);
var backLogDetailList = new List <BacklogDetail>();
foreach (var item in stepList)
{
var assigneeUser = PersonDB.GetInstance().GetPersonById(item.AssigneeUser);
var milestone = MilestoneDB.GetInstance().GetMilestoneById(item.MilestoneId);
backLogDetailList.Add(new BacklogDetail()
{
SprintId = item.SprintId,
AssigneeUserName = $"{assigneeUser.Name} {assigneeUser.Surname}",
Descripton = item.Description,
DetailName = item.Name,
MilestoneName = milestone.Name,
AssignedPersonNames = StepDB.GetInstance().GetStepPersonsByStepId(item.Id),
StepId = item.Id
});
}
return(Json(backLogDetailList));
}
catch (System.Exception exc)
{
throw exc;
}
}
public IActionResult Index()
{
var returnList = new List <HomeViewModel>();
var personId = JsonConvert.DeserializeObject <Person>(HttpContext.Session.GetString("ActivePerson")).Id;
var list = ProjectDB.GetInstance().GetAllProject(personId);
list.ForEach(item =>
{
var assignedPerson = PersonDB.GetInstance().GetPersonById(item.ProjectLeader);
var step = StepDB.GetInstance().GetAllNotificationByProjectId(item.Id);
returnList.Add(new HomeViewModel()
{
AssigneeUserName = $"{assignedPerson.Name} {assignedPerson.Surname}",
Code = item.Code,
Description = item.Description,
Id = item.Id,
Name = item.Name,
ProjectLeader = item.ProjectLeader,
ShortName = item.ShortName,
Status = item.Status,
Kalan = step
});
});
return(View(returnList));
}
public void Delete(Person p)
{
PersonDB db = new PersonDB();
db.Delete(p);
db.SaveChanges();
}
public static DataTable GetDataTable_AllNotInc(Staff[] excList)
{
string notInList = string.Empty;
foreach (Staff s in excList)
{
notInList += s.StaffID.ToString() + ",";
}
if (notInList.Length > 0)
{
notInList = notInList.Substring(0, notInList.Length - 1);
}
string sql = @"SELECT s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number,
s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external, s.staff_date_added,
s.start_date, s.end_date, s.comment,
" + PersonDB.GetFields("", "p") + @",
t.title_id, t.descr
FROM
Staff AS s
LEFT OUTER JOIN Person p ON s.person_id = p.person_id
LEFT OUTER JOIN Title t ON t.title_id = p.title_id
WHERE s.staff_id > 0 AND s.is_fired = 0 AND s.is_external = 0 " + ((notInList.Length > 0) ? " AND staff_id NOT IN (" + notInList + @") " : "") + @"
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
static void Main(string[] args)
{
try
{
log.Write("Program Başladı");
string Subject;
List <Person> personList = new List <Person>();
using (PersonDB context = new PersonDB())
{
personList = context.Person.Where(x => x.BirthDate.Day == DateTime.Now.Day && x.BirthDate.Month == DateTime.Now.Month).ToList();
}
ITemplate BirthdayMail = new BirthdayTemp();
TemplateStore tStore = new TemplateStore(BirthdayMail);
foreach (Person person in personList)
{
log.Write("Kişi bulundu : " + person.Name + " " + person.SurName);
Subject = string.Format("İyi ki Doğdun {0} {1}", person.Name, person.SurName);
IMessenger Message = new SendMail(person.Mail, person.Name, person.SurName, Subject, tStore.GetTemplate());
Notification notification = new Notification(Message);
notification.Send();
}
}
catch (Exception ex)
{
log.Write("Hata : " + ex.Message.ToString());
}
log.Write("Bitti");
Console.ReadLine();
}
//sql中查询条件使用in
static void RetrievePersonWithIn()
{
int[] argIds = { 4, 5 };
List <Person> persons = PersonDB.RetrieveWithIn(argIds);
persons.ForEach(n => Console.WriteLine(n.Name));
}
private Person Map(PersonDB dbPerson)
{
return(new Person()
{
Name = dbPerson.Name, Id = dbPerson.Id, Surname = dbPerson.Surname
});
}
public static DataTable GetDataTable_WorkingStaffOf(int organistion_id, DateTime date)
{
string sql = @"SELECT Distinct
--r.register_staff_id,r.register_staff_date_added,r.organisation_id,r.provider_number AS registration_provider_number,
--r.excl_sun, r.excl_mon, r.excl_tue, r.excl_wed, r.excl_thu, r.excl_fri, r.excl_sat, r.main_provider_for_clinic,
s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number,
s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external,
s.staff_date_added, s.start_date, s.end_date, s.comment,
s.num_days_to_display_on_booking_screen,
s.show_header_on_booking_screen,
s.bk_screen_field_id,
s.bk_screen_show_key,
s.enable_daily_reminder_sms,
s.enable_daily_reminder_email,
s.hide_booking_notes,
" + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @",
t.title_id, t.descr
FROM
RegisterStaff AS r
LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id
LEFT OUTER JOIN Person p ON s.person_id = p.person_id
LEFT OUTER JOIN Title t ON t.title_id = p.title_id
WHERE
s.staff_id > 0 AND s.is_fired = 0 AND r.is_deleted = 0 AND r.organisation_id = " + organistion_id + @"
-- get unavail bookings for whole day .... and make sure there are none
AND (SELECT COUNT(*) FROM Booking
WHERE
(
(booking_type_id = 341 AND (organisation_id IS NULL OR organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') )) OR
(booking_type_id = 342 AND (organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') ))
)
AND
(date_deleted IS NULL)
AND
(
(is_recurring = 0 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) = '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"') OR
(is_recurring = 1 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) <= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"'
AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_end))) >= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"'
AND (Booking.recurring_weekday_id = " + WeekDayDB.GetWeekDayID(date.DayOfWeek) + @"))
)
AND
(provider = r.staff_id) ) = 0
AND (SELECT COUNT(*) FROM Staff WHERE (staff_id = r.staff_id) AND (excl_" + date.DayOfWeek.ToString().Substring(0, 3).ToLower() + @" = 0)) > 0
ORDER BY
p.surname, p.firstname, p.middlename";
return(DBBase.ExecuteQuery(sql).Tables[0]);
}
public float GetBalanceByUID(int uid)
{
IPersonDB userDb = new PersonDB();
IPersonManager userManager = new PersonManager(userDb);
var user = userManager.GetBalanceByUID(uid);
return(user.Balance);
}
public float GetBalanceByUsername(string Username)
{
IPersonDB userDb = new PersonDB();
IPersonManager userManager = new PersonManager(userDb);
var user = userManager.GetBalanceByUsername(Username);
return(user.Balance);
}
public string GetUsernameByUID(int uid)
{
IPersonDB userDb = new PersonDB();
IPersonManager userManager = new PersonManager(userDb);
var user = userManager.GetUsernameByUID(uid);
return(user.Username);
}
public void UpdatePerson(Person p)
{
PersonDB db = new PersonDB();
db.Update(p);
db.SaveChanges();
}
public PersonList SelectAllPersons()
{
PersonDB db = new PersonDB();
PersonList l = new PersonList();
l = db.SelectAll();
return(l);
}
public void SavePerson(PersonDB person)
{
using (var personDb = new PersonContext())
{
personDb.Persons.Add(person);
personDb.SaveChanges();
}
}
public void UpdatePerson(PersonDB person)
{
if ( person != null )
{
using (var personDb = new PersonContext())
{
var editItem = personDb.Persons.Where(i => i.Id == person.Id).SingleOrDefault();
if (editItem != null)
{
editItem.Name = person.Name;
editItem.SurName = person.SurName;
editItem.Age = person.Age;
personDb.SaveChanges();
}
}
}
}
public void UpdatePerson(object item)
{
if (EditedItem != null && EditedItem.Age.HasValue)
{
PersonDB personItemForUpdate = new PersonDB()
{
Id = EditedItem.Id,
Age = EditedItem.Age.Value,
Name = EditedItem.Name,
SurName = EditedItem.SurName
};
client.UpdatePerson(personItemForUpdate);
LoadPersonsCollection();
}
}
private void SaveNewPerson(object item)
{
if (NewItem != null && NewItem.Age.HasValue)
{
PersonDB personItemForSave = new PersonDB()
{
Id = NewItem.Id,
Age = NewItem.Age.Value,
Name = NewItem.Name,
SurName = NewItem.SurName
};
client.SavePerson(personItemForSave);
NewItem = new Person();
LoadPersonsCollection();
}
}