public static string PrintChildren(
string AChildName,
string ADonorName,
bool AChildWithoutDonor,
string APartnerStatus,
string ASponsorshipStatus,
string ASponsorAdmin,
string ASortBy,
string AReportLanguage)
{
SponsorshipFindTDSSearchResultTable table = FindChildren(AChildName, ADonorName, AChildWithoutDonor, APartnerStatus, ASponsorshipStatus, ASponsorAdmin, ASortBy);
HtmlDocument HTMLDocument = HTMLTemplateProcessor.Table2Html(table, "Sponsorship/SponsoredChildrenList.html", AReportLanguage);
string PDFFile = TFileHelper.GetTempFileName(
"printchildrenlist",
".pdf");
if (Html2Pdf.HTMLToPDF(HTMLDocument.DocumentNode.WriteTo(), PDFFile))
{
byte[] data = System.IO.File.ReadAllBytes(PDFFile);
string result = Convert.ToBase64String(data);
System.IO.File.Delete(PDFFile);
return(result);
}
return(String.Empty);
}
/// <summary>
/// This will select all childs entrys of the webconnector
/// serverMSponsorship.asmx/TSponsorshipWebConnector_FindChildren
/// </summary>
public void SelectAllChilds()
{
SponsorshipFindTDSSearchResultTable Result = TSponsorshipWebConnector.FindChildren("", "", "", "", "");
totalChildsBeforeTest = Result.Rows.Count;
TLogging.Log("All Childs returns: " + totalChildsBeforeTest);
}
/// <summary>
/// Trys to select the same child that we just created
/// serverMSponsorship.asmx/TSponsorshipWebConnector_FindChildren
/// </summary>
public void SelectCreatedChild()
{
SponsorshipFindTDSSearchResultTable Result = TSponsorshipWebConnector.FindChildren(
new_child_firstname,
new_child_lastname,
"",
"",
""
);
// we expect one result only
Assert.AreEqual(Result.Rows.Count, 1);
SponsorshipFindTDSSearchResultRow Row = Result[0];
APartnerKey = Row.PartnerKey;
TLogging.Log("Found new Child with id: " + APartnerKey);
}
public static SponsorshipFindTDSSearchResultTable FindChildren(
string AFirstName,
string AFamilyName,
string APartnerStatus,
string ASponsorshipStatus,
string ASponsorAdmin)
{
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("FindChildren");
string sql = "SELECT p.p_partner_short_name_c, p.p_status_code_c, p.p_partner_key_n, p.p_user_id_c, " +
"f.p_first_name_c, f.p_family_name_c, t.p_type_code_c " +
"FROM PUB_p_partner p, PUB_p_family f, PUB_p_partner_type t " +
"WHERE p.p_partner_key_n = f.p_partner_key_n " +
"AND p.p_partner_key_n = t.p_partner_key_n";
int CountParameters = 0;
int Pos = 0;
CountParameters += (AFirstName != String.Empty ? 1 : 0);
CountParameters += (ASponsorshipStatus != String.Empty ? 1 : 0);
CountParameters += (AFamilyName != String.Empty ? 1 : 0);
CountParameters += (ASponsorAdmin != String.Empty ? 1 : 0);
OdbcParameter[] parameters = new OdbcParameter[CountParameters];
if (ASponsorshipStatus != String.Empty)
{
sql += " AND t.p_type_code_c = ?";
parameters[Pos] = new OdbcParameter("ASponsorshipStatus", OdbcType.VarChar);
parameters[Pos].Value = ASponsorshipStatus;
Pos++;
}
else
{
sql += " AND t.p_type_code_c IN ('CHILDREN_HOME','HOME_BASED','BOARDING_SCHOOL','PREVIOUS_CHILD','CHILD_DIED')";
}
if (AFirstName != String.Empty)
{
sql += " AND f.p_first_name_c LIKE ?";
parameters[Pos] = new OdbcParameter("FirstName", OdbcType.VarChar);
parameters[Pos].Value = AFirstName;
Pos++;
}
if (AFamilyName != String.Empty)
{
sql += " AND f.p_family_name_c LIKE ?";
parameters[Pos] = new OdbcParameter("AFamilyName", OdbcType.VarChar);
parameters[Pos].Value = AFamilyName;
Pos++;
}
if (ASponsorAdmin != String.Empty)
{
sql += " AND p.p_user_id_c LIKE ?";
parameters[Pos] = new OdbcParameter("ASponsorAdmin", OdbcType.VarChar);
parameters[Pos].Value = ASponsorAdmin;
Pos++;
}
SponsorshipFindTDSSearchResultTable result = new SponsorshipFindTDSSearchResultTable();
db.ReadTransaction(ref t,
delegate
{
db.SelectDT(result, sql, t, parameters);
});
db.CloseDBConnection();
return(result);
}
public static SponsorshipFindTDSSearchResultTable FindChildren(
string AChildName,
string ADonorName,
bool AChildWithoutDonor,
string APartnerStatus,
string ASponsorshipStatus,
string ASponsorAdmin,
string ASortBy)
{
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("FindChildren");
string sql = "SELECT p.p_partner_short_name_c, p.p_status_code_c, p.p_partner_key_n, p.p_user_id_c, " +
"f.p_first_name_c, f.p_family_name_c, t.p_type_code_c, t.p_type_description_c " +
"FROM PUB_p_partner p, PUB_p_family f, PUB_p_partner_type pt, PUB_p_type t, PUB_p_type_category tc " +
"WHERE p.p_partner_key_n = f.p_partner_key_n " +
"AND p.p_partner_key_n = pt.p_partner_key_n " +
"AND pt.p_type_code_c = t.p_type_code_c " +
"AND t.p_category_code_c = tc.p_code_c";
int CountParameters = 0;
int Pos = 0;
CountParameters += (AChildName != String.Empty ? 2 : 0);
CountParameters += (ASponsorshipStatus != String.Empty ? 1 : 0);
CountParameters += (ASponsorAdmin != String.Empty ? 1 : 0);
OdbcParameter[] parameters = new OdbcParameter[CountParameters];
if (ASponsorshipStatus != String.Empty)
{
sql += " AND t.p_type_code_c = ?";
parameters[Pos] = new OdbcParameter("ASponsorshipStatus", OdbcType.VarChar);
parameters[Pos].Value = ASponsorshipStatus;
Pos++;
}
else
{
sql += " AND t.p_category_code_c = 'SPONSORED_CHILD_STATUS' ";
}
if (AChildName != String.Empty)
{
// cover both cases, that the child has a family name, or it has no family name stored in the database
sql += " AND (CONCAT(f.p_first_name_c, ' ', f.p_family_name_c) LIKE ? OR f.p_first_name_c LIKE ?)";
parameters[Pos] = new OdbcParameter("ChildName", OdbcType.VarChar);
AChildName = '%' + AChildName + '%';
parameters[Pos].Value = AChildName;
Pos++;
parameters[Pos] = new OdbcParameter("ChildName", OdbcType.VarChar);
AChildName = '%' + AChildName + '%';
parameters[Pos].Value = AChildName;
Pos++;
}
if (ASponsorAdmin != String.Empty)
{
sql += " AND p.p_user_id_c LIKE ?";
parameters[Pos] = new OdbcParameter("ASponsorAdmin", OdbcType.VarChar);
parameters[Pos].Value = ASponsorAdmin;
Pos++;
}
if (ASortBy == "ChildName")
{
sql += " ORDER BY f.p_first_name_c, f.p_family_name_c";
}
else if (ASortBy == "SponsorAdmin")
{
sql += " ORDER BY p.p_user_id_c, f.p_first_name_c, f.p_family_name_c";
}
SponsorshipFindTDSSearchResultTable result = new SponsorshipFindTDSSearchResultTable();
db.ReadTransaction(ref t,
delegate
{
db.SelectDT(result, sql, t, parameters);
});
List <SponsorshipFindTDSSearchResultRow> childrenNotMatchingDonor = new List <SponsorshipFindTDSSearchResultRow>();
foreach (SponsorshipFindTDSSearchResultRow child in result.Rows)
{
sql = "SELECT DISTINCT p.* " +
"FROM a_recurring_gift rg, a_recurring_gift_detail rgd, PUB_p_partner p " +
"WHERE rgd.a_ledger_number_i = rg.a_ledger_number_i " +
"AND rgd.a_batch_number_i = rg.a_batch_number_i " +
"AND rgd.a_gift_transaction_number_i = rg.a_gift_transaction_number_i " +
"AND rgd.p_recipient_key_n = " + child.PartnerKey + " " +
"AND ? >= rgd.a_start_donations_d " +
"AND (? <= a_end_donations_d OR a_end_donations_d IS NULL) " +
"AND rg.p_donor_key_n = p.p_partner_key_n";
List <OdbcParameter> parameterList = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter();
param = new OdbcParameter("StartDate", OdbcType.DateTime);
param.Value = DateTime.Now.AddMonths(+3);
parameterList.Add(param);
param = new OdbcParameter();
param = new OdbcParameter("EndDate", OdbcType.DateTime);
param.Value = DateTime.Now.AddMonths(-1);
parameterList.Add(param);
if (ADonorName != String.Empty)
{
sql += " AND p.p_partner_short_name_c LIKE ?";
param = new OdbcParameter("ADonorName", OdbcType.VarChar);
ADonorName = '%' + ADonorName + '%';
param.Value = ADonorName;
parameterList.Add(param);
}
PPartnerTable donors = new PPartnerTable();
db.ReadTransaction(ref t,
delegate
{
db.SelectDT(donors, sql, t, parameterList.ToArray());
});
if (ADonorName != String.Empty)
{
// drop all children that don't have a donor match
if (donors.Rows.Count == 0)
{
childrenNotMatchingDonor.Add(child);
}
}
if (AChildWithoutDonor)
{
// drop all children that have a donor match
if (donors.Rows.Count != 0)
{
childrenNotMatchingDonor.Add(child);
}
}
bool firstName = true;
foreach (PPartnerRow donor in donors.Rows)
{
if (!firstName)
{
child["DonorName"] += ";";
}
child["DonorName"] += donor.PartnerShortName;
firstName = false;
string DonorAddress, DonorEmailAddress, DonorPhoneNumber;
GetDonorContactDetails(donor.PartnerKey,
out DonorAddress, out DonorEmailAddress, out DonorPhoneNumber);
child["DonorContactDetails"] += donor.PartnerShortName + ";" + DonorAddress + ";";
if (DonorEmailAddress != String.Empty)
{
child["DonorContactDetails"] += "<a href='mailto:" + DonorEmailAddress + "'>" + DonorEmailAddress + "</a>;";
}
if (DonorPhoneNumber != String.Empty)
{
child["DonorContactDetails"] += DonorPhoneNumber + ";";
}
child["DonorContactDetails"] += ";";
}
}
foreach (SponsorshipFindTDSSearchResultRow child in childrenNotMatchingDonor)
{
result.Rows.Remove(child);
}
db.CloseDBConnection();
return(result);
}
