mc1392

We have Accounts. Those accounts may or may not have an attached address record. Currently, the report is using the attached address, but there is a request for me to use the address that is attached to the actual contribution/donation instead of the account. FYI: speaking to the requester, we agreed on a totally separate report using the contribution address only. I was finding it difficult to accommodate the request within the body of a very large Stored Procedure. If not for the Stored Procedure, This would be pretty easy. Here is the stored procedure: DROP Temporary TABLE IF EXISTS temp_fc; create temporary table temp_fc as select odt.Id as OpportunityDonationTargetId, dt.Source__c as DonationTargetSource, dt.Name as DonationTarget, odt.Amount__c as Amount, op.CloseDate, op.Submit_Date__c as SubmitDate , acc.ATS_Account_ID__c as ATSID, case when acc.LastName is null then acc.Name else '' end as BusinessName , acc.Salutation, acc.FirstName, acc.Middle_Name__pc as MiddleName, acc.LastName, acc.Suffix__pc as Suffix , acc.Degree_Designation__pc as Designation, acc.PersonTitle, acc.PersonEmail, acc.Phone, acc.PersonHomePhone as HomePhone, acc.Fax , asm.Name as ToAssembly, odt.Message__c as Message , op.id as opportunityId, acc.PersonContactId, acc.Preferred_Mailing_Address__c as mailAddressId, opay.id as opportunityPaymentId , b.Batch_Code__c as OpportunityBatchCode, if(op.Is_Matching_Gift__c, 'Yes', 'No') as IsMatchingGift , facilitatorAcc.Name as FacilitatorName, op.Appeal_Code__c as AppealCode from sf.opportunity op join sf.opportunity_donation_target__c odt on odt.Opportunity__c = op.id join sf.donation_target__c dt on odt.Donation_Target__c = dt.id join sf.account acc on op.AccountId = acc.id join sf.opportunity_payment__c opay on opay.Opportunity__c = op.id left join sf.assembly__c asm on odt.Assembly__c = asm.id left join sf.batch__c b on b.id = opay.Batch__c left join sf.account facilitatorAcc on op.Facilitator__c = facilitatorAcc.Id where op.StageName = 'Posted' and op.CloseDate between startDate and endDate; CREATE INDEX pid_indx ON temp_fc(PersonContactId); #create index CREATE INDEX oid_indx ON temp_fc(opportunityId); #create index CREATE INDEX mid_indx ON temp_fc(mailAddressId); #create index CREATE INDEX opid_indx ON temp_fc(opportunityPaymentId); #create index select fc.* , mailAddr.Institution__c as MailingInstitution, mailAddr.Department__c as MailingDepartment , mailAddr.Address_Line_1__c as MailingAddress1, mailAddr.Address_Line_2__c as MailingAddress2, mailAddr.Address_Line_3__c as MailingAddress3 , mailAddr.City__c as MailingCity, mailAddr.State_Province__c as MailingState, mailAddr.ZIP_Postal_Code__c as MailingZip, c.Name as MailingCountryName , oc.Gift_Type__c as GiftType, oc.Personal_Message__c as PersonalMessage , oc.Tribute_Salutation__c as TributeSalutation, oc.Tribute_First_Name__c as TributeFirstName, oc.Tribute_Last_Name__c as TributeLastName, oc.Send_Acknowledgement__c as SendAcknowledgement , oc.Recipient__c as Recipient, oc.Recipient_Address_Line_1__c as RecipientAddress1, oc.Recipient_Address_Line_2__c as RecipientAddress2 , oc.Recipient_City__c as RecipientCity, oc.Recipient_State_Province__c as RecipientState, oc.Recipient_ZIP_Postal_Code__c as RecipientZip, recipientC.Name as RecipientCountryName , group_concat(distinct asm.Name order by asm.Name) as PrimaryAssembly from temp_fc fc left join sf.online_contribution__c oc on oc.Opportunity_Payment__c = fc.opportunityPaymentId left join sf.country__c recipientC on recipientC.id = oc.Recipient_Country__c left join sf.address__c mailAddr on mailAddr.Id = fc.mailAddressId left join sf.country__c c on c.id = mailAddr.Country__c left join sf.contact_assembly__c ca on PersonContactId = ca.Contact__c and ca.Is_Primary__c = true left join sf.assembly__c asm on asm.id = ca.Assembly__c group by fc.opportunityId, OpportunityDonationTargetId order by DonationTargetSource, DonationTarget, FirstName; DROP Temporary TABLE IF EXISTS temp_fc; END The part of the Stored Procedure to in question is: select fc.* , mailAddr.Institution__c as MailingInstitution, mailAddr.Department__c as MailingDepartment , mailAddr.Address_Line_1__c as MailingAddress1, mailAddr.Address_Line_2__c as MailingAddress2, mailAddr.Address_Line_3__c as MailingAddress3 , mailAddr.City__c as MailingCity, mailAddr.State_Province__c as MailingState, mailAddr.ZIP_Postal_Code__c as MailingZip, c.Name as MailingCountryName , oc.Gift_Type__c as GiftType, oc.Personal_Message__c as PersonalMessage , oc.Tribute_Salutation__c as TributeSalutation, oc.Tribute_First_Name__c as TributeFirstName, oc.Tribute_Last_Name__c as TributeLastName, oc.Send_Acknowledgement__c as SendAcknowledgement , oc.Recipient__c as Recipient, oc.Recipient_Address_Line_1__c as RecipientAddress1, oc.Recipient_Address_Line_2__c as RecipientAddress2 , oc.Recipient_City__c as RecipientCity, oc.Recipient_State_Province__c as RecipientState, oc.Recipient_ZIP_Postal_Code__c as RecipientZip, recipientC.Name as RecipientCountryName , group_concat(distinct asm.Name order by asm.Name) as PrimaryAssembly from temp_fc fc left join sf.online_contribution__c oc on oc.Opportunity_Payment__c = fc.opportunityPaymentId left join sf.country__c recipientC on recipientC.id = oc.Recipient_Country__c left join sf.address__c mailAddr on mailAddr.Id = fc.mailAddressId left join sf.country__c c on c.id = mailAddr.Country__c left join sf.contact_assembly__c ca on PersonContactId = ca.Contact__c and ca.Is_Primary__c = true left join sf.assembly__c asm on asm.id = ca.Assembly__c group by fc.opportunityId, OpportunityDonationTargetId order by DonationTargetSource, DonationTarget, FirstName; I wanted to alternate between using the address from mailAddr or online_contribution__c oc. I find it very difficult to accommodate that condition in the middle of the Stored Procedure.

I've used COALESCE before, but I don't see how it applies to my situation? Could provide a sample? Should I COALESCE then use a CASE?

I have 2 tables: My goal: I am having trouble getting to this result. Any ideas?

@Barandthat was the issue! You are the man! / you are the woman! thank you!

Thank! I will give this a try and come back and respond.

1. Here is the main calling page code: <?php require_once $_SERVER['ATS_CORE_PHP_LIB_PATH'].'/dao/AtsIFEReviewerDAO.php'; $theYear = intval($_GET['r']); if($theYear > 0){ require_once $_SERVER['ATS_CORE_PHP_LIB_PATH'].'/core/util/ExcelHTML.php'; $dao = new AtsIFEReviewerDAO(); $yearData = $dao->getReviewAnalytics($theYear); $xls = new ExcelHTML($yearData, null); $xls->turnOnDisableAllColumnsFromAutoExcelEscape(); $xls->printOutput($theYear.'_ife_result.xls'); } ?> 2. here is the underlying query for the getReviewAnalytics($theYear) query from the code above: select recordId, full_name,institution,abstract_title,abstract_authors, count(*) as totalReviewSubmission, sum(rate_innovation) as totalInnovation, avg(rate_innovation) as averageInnovation, sum(rate_sustainability_and_implementation) as totalSustainabilityAndImplementation, avg(rate_sustainability_and_implementation) as averageSustainabilityAndImplementation, sum(rate_transferability) as totalTransferability, avg(rate_transferability) as averageTransferability, sum(rate_outcomes) as totalOutcomes, avg(rate_outcomes) as averageOutcomes,avg(rate_innovation)+avg(rate_sustainability_and_implementation)+avg(rate_transferability)+avg(rate_outcomes) as SUM_OF_AVERAGES, group_concat(formulaRateNotes separator '') as allNotes,group_concat(reviewers SEPARATOR '|') AS 'Reviewers' from ( select f.id as recordId, f.full_name,f.institution,f.abstract_title,f.abstract_authors, r.rate_innovation,r.rate_sustainability_and_implementation,r.rate_transferability, r.rate_outcomes,concat('<strong>', r.reviewer_name, '</strong>: ', r.rate_notes, '<br/>') as formulaRateNotes, r.reviewer_name as reviewers from database_name1.innovations_fellowship_education_form f join database_name2.innovations_fellowship_education_form_review r on r.innovations_fellowship_education_form_id = f.id where f.theyear = '".$theYear."' ) t1 group by recordId 3. here, I produce a downloadable Excel file, which is produced by the code in step 1: <?php //simple version of excel create base on html table class ExcelHTML { private $excelArrayHeaderKey; private $excelArrayData; private $debugToScreen; private $excelStyleHeader; private $disableAllColumnsFromAutoExcelEscape; private $ignoreAutoExcelEscape; //disable these columns from auto convert to text field in excel (default all columns escapes) public function __construct($arrayData, $arrayKey = null){ $this->ignoreAutoExcelEscape = null; $this->disableAllColumnsFromAutoExcelEscape = false; $this->debugToScreen = false; $this->excelArrayData = $arrayData; if(!$arrayKey){ //generate key from data if(sizeof($arrayData) > 0){ $firstRowData = $arrayData[0]; $arrayKey = array(); foreach($firstRowData as $k => $v){ $arrayKey[$k] = $k; } } } $this->excelArrayHeaderKey = $arrayKey; $this->excelStyleHeader = $this->getExcelHeaderStyle('DEFAULT'); } public function getExcelHeaderStyle($style){ if($style == 'EXPANDABLE_ROW') return '<style>.t{mso-number-format:"@";border-top:.5pt solid black;}.h{background-color:#f5f5dc;}br{mso-data-placement:same-cell;}</style>'; //default style return '<style>.t{mso-number-format:"@";white-space:nowrap;}.h{background-color:#dcdcdc;}br{mso-data-placement:same-cell;}</style>'; } public function changeHeaderStyle($styleName){ $this->excelStyleHeader = $this->getExcelHeaderStyle($styleName); } public function setIgnoreTheseColumnsFromEscape($ignoreTheseKeys){ $this->ignoreAutoExcelEscape = $ignoreTheseKeys; } public function turnOnDebugToScreen(){ $this->debugToScreen = true; } public function turnOnDisableAllColumnsFromAutoExcelEscape(){ $this->disableAllColumnsFromAutoExcelEscape = true; } public function generateExcelHTML() { $finalHTML = $this->excelStyleHeader; if(sizeof($this->excelArrayHeaderKey) > 0){ //output header $finalHTML .= '<table><tr>'; foreach($this->excelArrayHeaderKey as $eachKeyName){ $finalHTML .= '<th class=h>'.$eachKeyName.'</th>'; } $finalHTML .= '</tr>'; //output record data foreach($this->excelArrayData as $eachRecord){ $finalHTML .= '<tr valign="top">'; foreach($this->excelArrayHeaderKey as $eachKey => $eachKeyName){ if($this->disableAllColumnsFromAutoExcelEscape == true){ //disable auto escape by not adding css class $finalHTML .= '<td>'.$eachRecord[$eachKey]; }else{ $autoEscape = true; if($this->ignoreAutoExcelEscape != null){ if(in_array($eachKey, $this->ignoreAutoExcelEscape)) $autoEscape = false; } if($autoEscape){ $finalHTML .= '<td class=t>'.$eachRecord[$eachKey]; $firstChar = $eachRecord[$eachKey][0]; if($firstChar == '+'){ $finalHTML .= ' '; } }else{ //disable auto escape by not adding css class $finalHTML .= '<td>'.$eachRecord[$eachKey]; } } $finalHTML .= '</td>'; } $finalHTML .= '</tr>'; } $finalHTML .= '</table>'; } return $finalHTML; } public function printOutput($fileName){ if($this->debugToScreen == false){ if(!$fileName) $fileName = 'result_output.xls'; $cleanFileName = preg_replace('/[^a-zA-Z0-9_\.-]/s', '_', $fileName); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$cleanFileName.'"'); header('Pragma: no-cache'); header('Expires: 0'); } echo $this->generateExcelHTML(); } } ?> The column in question is the concatenated field 'allNotes'. Sorry for dumping the entire excel code, this class is new to me.

I'm not getting any errors. I run the query and the string is getting truncated. No error messages.

I have a MySQL Longtext field. When I query the data using PHP, I cannot get all of the text. If there are 1000 characters, I only get about half. Does this have anything to do with packet-size? Is this a configuration thing?