/*
#####################################################################
# Q9
# transactionPerson._Transaction._TRANSACTIONCODE
# transactionPerson._FirstName
# transactionPerson._LastName
# transactionPerson._Transaction._STORECODE
# transactionPerson._Transaction._TRANSACTION_DATE
# transactionPerson._Transaction._PAYMENT_METHOD
#####################################################################
*/
public static List <TransactionPerson> SearchTransactions(string personFamily, string paymentMethod)
{
string Q9Query = " select t.TRANSACTIONCODE, p.FIRSTNAME, p.LASTNAME, t.STORECODE, " +
" t.TRANSACTION_DATE, t.PAYMENT_METHOD " +
" from person p inner join transactions t on p.personcode = t.personcode " +
" where p.LASTNAME like '%" + personFamily + "%' and t.PAYMENT_METHOD like '%" + paymentMethod + "%' ";
List <TransactionPerson> transactionsPersons = new List <TransactionPerson>();
if (Connect())
{
//we are going to call query called Q9
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Q9Query;
sqlCommand.Connection = _Connection;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
//Converting query results to PersonPhoneAddress objects
while (reader.Read())
{
try
{
TransactionPerson transactionPerson = new TransactionPerson();
transactionPerson._Transaction._TRANSACTIONCODE = Convert.ToInt32(reader[0]);
transactionPerson._FirstName = Convert.ToString(reader[1]);
transactionPerson._LastName = Convert.ToString(reader[2]);
transactionPerson._Transaction._STORECODE = Convert.ToInt32(reader[3]);
transactionPerson._Transaction._TRANSACTION_DATE = Convert.ToDateTime(reader[4]);
transactionPerson._Transaction._PAYMENT_METHOD = Convert.ToString(reader[5]);
transactionsPersons.Add(transactionPerson);
}
catch (Exception ex)
{
return(null);
}
}
reader.Close();
}
}
return(transactionsPersons);
}
private void AddPeopleToTransaction()
{
listMailAddress = GetEmailList();
var participants = GetParticipants(listMailAddress);
var transactionPeople = new List <TransactionPerson>();
foreach (var p in List)
{
if (p.PeopleId == null)
{
continue;
}
if (transactionPeople.Any(pp => pp.PeopleId == p.PeopleId))
{
continue;
}
var tp = new TransactionPerson
{
PeopleId = p.PeopleId.Value,
Amt = p.TotalAmount(),
OrgId = p.orgid ?? Orgid
};
tp.Donor = Transaction.Donate > 0 && p == List[donor ?? 0];
transactionPeople.Add(tp);
}
if (SupportMissionTrip && GoerId == List[0].PeopleId)
{
// reload transaction because it is not in this context
var om = CurrentDatabase.OrganizationMembers.SingleOrDefault(mm => mm.PeopleId == GoerId && mm.OrganizationId == Orgid);
if (om != null && om.TranId.HasValue)
{
Transaction.OriginalId = om.TranId;
}
}
else
{
Transaction.OriginalTrans.TransactionPeople.AddRange(transactionPeople);
}
Transaction.Emails = listMailAddress.EmailAddressListToString();
Transaction.Participants = participants;
Transaction.TransactionDate = Util.Now;
}
/*
#####################################################################
# Q9
# transactionPerson._Transaction._TRANSACTIONCODE
# transactionPerson._FirstName
# transactionPerson._LastName
# transactionPerson._Transaction._STORECODE
# transactionPerson._Transaction._TRANSACTION_DATE
# transactionPerson._Transaction._PAYMENT_METHOD
#####################################################################
*/
public static List <TransactionPerson> SearchTransactions(string personFamily, string paymentMethod)
{
List <TransactionPerson> transactionsPersons = new List <TransactionPerson>();
TransactionPerson transactionPerson;
if (Connect())
{
using (SqlCommand sqlCommand = new SqlCommand("Q9", _Connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_SearchTransactions";
sqlCommand.Parameters.Add("@family", SqlDbType.VarChar).Value = personFamily;
sqlCommand.Parameters.Add("@paymentmethod", SqlDbType.VarChar).Value = paymentMethod;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
while (reader.Read())
{
transactionPerson = new TransactionPerson();
transactionPerson._Transaction._TRANSACTIONCODE = Convert.ToInt32(reader[0]);
transactionPerson._FirstName = Convert.ToString(reader[1]);
transactionPerson._LastName = Convert.ToString(reader[2]);
transactionPerson._Transaction._STORECODE = Convert.ToInt32(reader[3]);
transactionPerson._Transaction._TRANSACTION_DATE = Convert.ToDateTime(reader[4]);
transactionPerson._Transaction._PAYMENT_METHOD = Convert.ToString(reader[5]);
transactionsPersons.Add(transactionPerson);
}
reader.Close();
}
}
return(transactionsPersons);
}
