private bool TestHHMName(int iMode, ListView lvwGrid) { int lvwIndex = -1; string sWhereClause; bool bIsExactMatch = false; lvwGrid.Items.Clear(); //tpg.Text = "Loading .."; Application.DoEvents(); switch (iMode) { case 1: sWhereClause = "SoundEx(LastName) = SoundEx('" + tbLastName.Text + "') AND SoundEx(FirstName) = SoundEx('" + tbFirstName.Text + "')"; break; default: sWhereClause = "SoundEx(LastName) = SoundEx('" + tbLastName.Text + "')"; break; } //Font ft = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); string sqlText = "SELECT LastName, FirstName, hhm.HouseholdID HHId, hhm.Inactive HHMInactive, hhm.BirthDate" + ", hh.name, hh.address, hh.City, hh.Zipcode, hh.Inactive HHInactive" + ", (SELECT Count(*) FROM TrxLog WHERE HouseholdId = hhm.HouseholdId" + " AND TrxDate Between '" + CCFBGlobal.CurrentFiscalStartDate() + "' AND '" + CCFBGlobal.CurrentFiscalEndDate() + "') NbrSvcs" + ", hhm.id HHMID" + " FROM HouseholdMembers hhm INNER JOIN Household hh ON hhm.HouseholdID = hh.ID" + " WHERE " + sWhereClause + " ORDER BY hhm.LastName, hhm.FirstName, hh.Name"; DataTable dtblwrk = csdgdataaccess.TransferDataToLocalDataTable(sqlText); if (dtblwrk.Rows.Count > 0) { //tpg.Text = " [ " + dtblwrk.Rows.Count.ToString() + " ] " + tpg.Tag.ToString(); lvwGrid.Visible = true; lvwGrid.BackColor = Color.Ivory; string nameTest = tbLastName.Text.ToUpper() + ", " + tbFirstName.Text.ToUpper(); foreach (DataRow drow in dtblwrk.Rows) { ListViewItem lvItm = new ListViewItem(drow.Field <string>("LastName")); lvItm.SubItems.Add(drow.Field <string>("FirstName")); lvItm.SubItems.Add(CCFBGlobal.ValidDateString(drow["BirthDate"])); //lvItm.SubItems.Add(CCFBGlobal.IsInactiveString(Convert.ToBoolean(drow["HHMInactive"]))); lvItm.SubItems.Add(drow.Field <string>("Name")); if (drow.Field <string>("Name") == nameTest) { if (CCFBPrefs.AllowDuplicateHHNames == false) { lblDupHHError.Visible = true; } } lvItm.SubItems.Add(drow["NbrSvcs"].ToString()); lvItm.SubItems.Add(CCFBGlobal.IsInactiveString(Convert.ToBoolean(drow["HHInactive"]))); lvItm.SubItems.Add(drow.Field <string>("Address")); lvItm.SubItems.Add(drow.Field <string>("City")); lvItm.SubItems.Add(drow.Field <string>("ZipCode")); lvItm.SubItems.Add(Convert.ToInt32(drow["HHId"]).ToString()); if (Convert.ToBoolean(drow["HHInactive"]) == true) { //lvItm.Font = ft; //for (int i = 3; i < lvwGrid.Columns.Count; i++) //{ // lvItm.SubItems[i].Font = ft; // lvItm.SubItems[i].ForeColor = Color.Maroon; //} lvItm.ForeColor = Color.Maroon; } if (nameMatches(iMode, drow) == true) { if (newHHMID == Convert.ToInt32(drow["HHMID"])) { lvItm.BackColor = Color.LightGreen; } else { lvItm.BackColor = Color.LightCoral; if (lvwIndex < 0) { lvwIndex = lvwGrid.Items.Count; } bIsExactMatch = true; } } lvwGrid.Items.Add(lvItm); if (lvwIndex == lvwGrid.Items.Count - 1) { lvwGrid.Items[lvwIndex].Selected = true; lvwGrid.Items[lvwIndex].EnsureVisible(); } needToSetTab = true; } } else { //tpg.Text = "no matches"; needToSetTab = false; } //tabControl1.SelectedTab = tpg; return(bIsExactMatch); }
private void FillBirthDateGrid(string birthdate) { lvwHHMByBirthdate.Items.Clear(); lblDupHHMError.Visible = false; //tpgSameBirthDate.Text = "Loading ..."; Application.DoEvents(); //Font ft = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); string sqlText = "SELECT LastName, FirstName, hhm.HouseholdID HHId, hhm.Inactive HHMInactive" + ", hh.name, hh.address, hh.City, hh.Zipcode, hh.Inactive HHInactive" + ", hhm.ID HHMID" + " FROM HouseholdMembers hhm INNER JOIN Household hh ON hhm.HouseholdID = hh.ID" + " WHERE Birthdate = '" + birthdate + "' ORDER BY hh.Inactive, hh.name"; DataTable dtblwrk = csdgdataaccess.TransferDataToLocalDataTable(sqlText); if (dtblwrk.Rows.Count > 0) { //tpgSameBirthDate.Text = " [ " + dtblwrk.Rows.Count.ToString() + " ] " + tpgSameBirthDate.Tag.ToString(); lvwHHMByBirthdate.Visible = true; lvwHHMByBirthdate.BackColor = Color.Ivory; foreach (DataRow drow in dtblwrk.Rows) { ListViewItem lvItm = new ListViewItem(drow.Field <string>("LastName")); lvItm.SubItems.Add(drow.Field <string>("FirstName")); lvItm.SubItems.Add(CCFBGlobal.IsInactiveString(Convert.ToBoolean(drow["HHMInactive"]))); lvItm.SubItems.Add(drow.Field <string>("Name")); lvItm.SubItems.Add(drow.Field <string>("Address")); lvItm.SubItems.Add(drow.Field <string>("City")); lvItm.SubItems.Add(drow.Field <string>("ZipCode")); lvItm.SubItems.Add(Convert.ToInt32(drow["HHId"]).ToString()); lvItm.SubItems.Add(CCFBGlobal.IsInactiveString(Convert.ToBoolean(drow["HHInactive"]))); if (Convert.ToBoolean(drow["HHInactive"]) == true) { //lvItm.Font = ft; //for (int i = 3; i < lvwHHMByBirthdate.Columns.Count; i++) //{ // lvItm.SubItems[i].Font = ft; // lvItm.SubItems[i].ForeColor = Color.Maroon; //} lvItm.ForeColor = Color.Maroon; } if (drow.Field <string>("LastName") == tbLastName.Text.Trim() && drow.Field <string>("FirstName") == tbFirstName.Text.Trim()) { if (newHHMID == Convert.ToInt32(drow["HHMID"])) { lvItm.BackColor = Color.LightGreen; } else { lblDupHHMError.Visible = true; } } lvwHHMByBirthdate.Items.Add(lvItm); } } else { //tpgSameBirthDate.Text = ""; //lvwHHMByBirthdate.Visible = false; } }