public List <MemberWithDeposit> GetDepositAmountById(int memberId, string date)
{
connection = new SqlConnection(connectionString);
connection.Open();
query =
"select * from deposit WHERE MemberId='" + memberId + "' AND MONTH(Deposit.date)='" + date + "' ORDER BY Date";
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <MemberWithDeposit> depositList = new List <MemberWithDeposit>();
while (reader.Read())
{
MemberWithDeposit memberWithDeposit = new MemberWithDeposit();
memberWithDeposit.DepositId = (int)reader["DepositId"];
memberWithDeposit.Amount = (int)reader["Amount"];
memberWithDeposit.Date = (DateTime)reader["Date"];
memberWithDeposit.MemberId = (int)reader["MemberId"];
depositList.Add(memberWithDeposit);
}
reader.Close();
connection.Close();
return(depositList);
}
public List <MemberWithDeposit> GetDepositAmountList(string date)
{
connection = new SqlConnection(connectionString);
connection.Open();
query =
"select * from deposit join Members on Deposit.MemberId=Members.MemberId where MONTH(date)='" + date + "' order by Members.Name";
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <MemberWithDeposit> depositList = new List <MemberWithDeposit>();
while (reader.Read())
{
MemberWithDeposit memberWithDeposit = new MemberWithDeposit();
memberWithDeposit.DepositId = (int)reader["DepositId"];
memberWithDeposit.Amount = (int)reader["Amount"];
memberWithDeposit.Date = (DateTime)reader["Date"];
memberWithDeposit.MemberId = (int)reader["MemberId"];
memberWithDeposit.MemberName = (string)reader["Name"];
depositList.Add(memberWithDeposit);
}
reader.Close();
connection.Close();
return(depositList);
}
private void depositListView_DoubleClick(object sender, EventArgs e)
{
ListViewItem selectedMember = depositListView.SelectedItems[0];
MemberWithDeposit memberWithDeposit = (MemberWithDeposit)selectedMember.Tag;
depositDateTimePicker.Text = memberWithDeposit.Date.ToString();
nameComboBox.Text = memberWithDeposit.MemberName.ToString();
amountTextBox.Text = memberWithDeposit.Amount.ToString();
HiddenIdLabel.Text = memberWithDeposit.MemberId.ToString();
saveDepositAmountButton.Text = "Update";
removeButton.Enabled = true;
}
public List <MemberWithDeposit> GetDepositAmountGroupByName(string date)
{
connection = new SqlConnection(connectionString);
connection.Open();
query =
"select Members.Name,sum(Deposit.Amount) from Members left join Deposit on Members.MemberId=Deposit.MemberId where MONTH(Deposit.date)='" + date + "' group by Members.Name";
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <MemberWithDeposit> depositList = new List <MemberWithDeposit>();
while (reader.Read())
{
MemberWithDeposit memberWithDeposit = new MemberWithDeposit();
memberWithDeposit.Amount = (int)reader["Amount"];
memberWithDeposit.MemberName = (string)reader["Name"];
depositList.Add(memberWithDeposit);
}
reader.Close();
connection.Close();
return(depositList);
}
