public void getClientTrxStats(ref statsTrx trxStats, string hhId, string periodEnd)
{
trxStats.NbrTEFAPThisMonth = 0;
trxStats.NbrSupplThisMonth = 0;
trxStats.NbrTrxThisMonth = 0;
trxStats.NbrTrxThisWeek = 0;
if (hhId != "0")
{
SqlCommand sqlCmd = new SqlCommand("select SUM(case WHEN RcvdCommodity = 1 then 1 else 0 end) NbrTEFAP"
+ ", SUM(case WHEN RcvdSupplemental = 1 THEN 1 else 0 end) NbrSuppl"
+ ", SUM(case WHEN RcvdSupplemental = 0 AND (LBSStd+LbsOther)>0 THEN 1 else 0 end) nbrSvcs"
+ ", SUM(CASE WHEN DATEPART(week,TrxDate) = DATEPART(week,'" + periodEnd + "') THEN case WHEN RcvdSupplemental = 0 AND (LBSStd+LbsOther)>0 THEN 1 else 0 end ELSE 0 END) NbrThisWeek"
+ " from TrxLog"
+ " WHERE TrxStatus <2 AND TrxDate between '" + CCFBGlobal.firstDayOfMonth(periodEnd) + "' and '" + periodEnd + "'"
+ " and HouseholdID = " + hhId, conn);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open(); // Open the connection and execute the reader.
}
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
trxStats.NbrTEFAPThisMonth = reader.GetInt32(0);
}
if (!reader.IsDBNull(1))
{
trxStats.NbrSupplThisMonth = reader.GetInt32(1);
}
if (!reader.IsDBNull(2))
{
trxStats.NbrTrxThisMonth = reader.GetInt32(2);
}
if (!reader.IsDBNull(3))
{
trxStats.NbrTrxThisWeek = reader.GetInt32(3);
}
}
}
catch { };
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
