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;
}
}