Jump to content

mc1392

New Members
  • Content Count

    8
  • Joined

  • Last visited

Community Reputation

0 Neutral

About mc1392

  • Rank
    Newbie

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. 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.
  2. 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?
  3. I have 2 tables: My goal: I am having trouble getting to this result. Any ideas?
  4. @Barandthat was the issue! You are the man! / you are the woman! thank you!
  5. Thank! I will give this a try and come back and respond.
  6. 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.
  7. I'm not getting any errors. I run the query and the string is getting truncated. No error messages.
  8. 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?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.