public void UpdateLatestServiceDates()
{
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.CommandType = CommandType.StoredProcedure;
cmdUpdate.Connection = conn;
conn.Open();
try
{
cmdUpdate.CommandText = "UpdateHouseholdTrxDates";
cmdUpdate.Parameters.AddWithValue("@HHId", clsHH.ID);
cmdUpdate.Parameters.AddWithValue("@LowDate", CCFBGlobal.CurrentFiscalStartDate().ToShortDateString());
cmdUpdate.Parameters.AddWithValue("@HiDate", CCFBGlobal.CurrentFiscalEndDate().ToShortDateString());
cmdUpdate.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
}
catch (InvalidOperationException ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
catch (Exception ex)
{
// You might want to pass these errors
// back out to the caller.
Console.WriteLine("Error: {0}", ex.Message);
}
conn.Close();
}
private void getVoucherLogForPeriod()
{
string newWhereClause = "";
switch (cboVHistoryPeriod.SelectedIndex)
{
case 0: //Current Month
int month = DateTime.Now.Month;
DateTime from = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime to = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));
newWhereClause = " AND TrxDate Between '" + from.ToString() + "' AND '" + to.ToString() + "'";
break;
case 1: //Last 90 Days
newWhereClause = " And TrxDate Between '" + new DateTime(
DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day).AddDays(-90).ToString()
+ "' And '" + DateTime.Today.ToString() + "'";
break;
case 2: //Current Calendar Year
newWhereClause = " And TrxDate Between '" + new DateTime(DateTime.Today.Year, 1, 1).ToString()
+ "' And '" + new DateTime(DateTime.Today.Year, 12, 31).ToString() + "'";
break;
case 3: //Current Fiscal Year
newWhereClause = " And TrxDate Between '" + CCFBGlobal.CurrentFiscalStartDate().ToString() + "' And '"
+ CCFBGlobal.CurrentFiscalEndDate().ToString() + "'";
break;
case 4: //Previous Calendar Year
newWhereClause = " And TrxDate Between '" + new DateTime(DateTime.Today.Year - 1, 1, 1).ToString() + "' ANd '"
+ new DateTime(DateTime.Today.Year - 1, 12, 31).ToString() + "'";
break;
case 5: //Previous Fiscal Year
newWhereClause = " And TrxDate Between '" + CCFBGlobal.PreviousFiscalStartDate().ToString() + "' And '"
+ CCFBGlobal.PreviousFiscalEndDate().ToString() + "'";
break;
default: //ALL
newWhereClause = "";
break;
}
loadVoucherLog(newWhereClause);
}
public void UpdateLatestServiceDates(string dateOfService)
{
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.CommandType = CommandType.StoredProcedure;
cmdUpdate.Connection = conn;
openConnection();
try
{
cmdUpdate.CommandText = "UpdateHouseholdTrxDates";
cmdUpdate.Parameters.AddWithValue("@HHId", ID);
cmdUpdate.Parameters.AddWithValue("@LowDate", CCFBGlobal.CurrentFiscalStartDate().ToShortDateString());
cmdUpdate.Parameters.AddWithValue("@HiDate", CCFBGlobal.CurrentFiscalEndDate().ToShortDateString());
cmdUpdate.Parameters.AddWithValue("@ServiceDate", dateOfService);
cmdUpdate.ExecuteNonQuery();
if (DateTime.Compare(FirstService, DateTime.Parse(dateOfService)) > 0 || DateTime.Compare(FirstService, CCFBGlobal.FBNullDateValue) <= 0)
{
cmdUpdate = new SqlCommand("UpdateHouseholdFirstServiceDate", conn);
cmdUpdate.CommandType = CommandType.StoredProcedure;
cmdUpdate.Parameters.Add(new SqlParameter("@HHID", ID));
cmdUpdate.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
}
catch (InvalidOperationException ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
catch (Exception ex)
{
// You might want to pass these errors
// back out to the caller.
Console.WriteLine("Error: {0}", ex.Message);
}
closeConnection();
}
public YearlyForm()
{
InitializeComponent();
foreach (ComboBox cb in tabPage2.Controls.OfType <ComboBox>())
{
cbList.Add(cb);
}
ServiceItems clsServiceItems = new ServiceItems(CCFBGlobal.connectionString);
clsServiceItems.openWhere("ItemRule = " + CCFBGlobal.itemRule_SpecialService.ToString());
lvwSpclFood.Items.Clear();
ListViewItem lvwItm;
for (int i = 0; i < clsServiceItems.DSet.Tables[0].Rows.Count; i++)
{
lvwItm = new ListViewItem();
lvwItm.Text = clsServiceItems.DSet.Tables[0].Rows[i]["ItemDesc"].ToString();
lvwItm.SubItems.Add(clsServiceItems.DSet.Tables[0].Rows[i]["ItemKey"].ToString());
lvwSpclFood.Items.Add(lvwItm);
}
CurrentFiscalStart = CCFBGlobal.CurrentFiscalStartDate();
CurrentFiscalEnd = CCFBGlobal.CurrentFiscalEndDate();
SetCalendarRange(CurrentFiscalStart, CurrentFiscalEnd);
}
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);
}
