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