protected function bindRepProtokollDetail()
{
$SQL = "SELECT a.* FROM `vv_protokoll_detail_aufgabe` a INNER JOIN ta_protokoll_detail_group b ON a.idtm_protokoll_detail_group = b.idtm_protokoll_detail_group";
$SQL .= " WHERE a.idtm_protokoll = " . $this->idtm_protokoll;
$this->RepProtokollDetail->DataSource = ProtokollDetailAufgabeView::finder()->findAllBySQL($SQL);
$this->RepProtokollDetail->dataBind();
}
public function PFMailSend($sender, $param)
{
$Aufgabe = ProtokollDetailAufgabeView::finder()->findByidtm_protokoll_detail($param->CallbackParameter);
$MailEmpfaenger = OrganisationRecord::finder()->findByPk($Aufgabe->idtm_organisation);
$mail = new PHPMailer();
$mail->From = "*****@*****.**";
$mail->FromName = "planlogIQ";
$mail->Host = "smtp.1und1.de";
$mail->Mailer = "smtp";
$mail->SMTPAuth = true;
$mail->IsSendmail();
//nur bei 1und1
$mail->Username = "******";
$mail->Password = "";
$mail->AddAddress(KommunikationRecord::finder()->find('idtm_organisation=? AND kom_ismain=1 AND kom_type = 3', $Aufgabe->idtm_organisation)->kom_information, $MailEmpfaenger->org_name);
$mail->Subject = $Aufgabe->prtdet_topic;
$HTMLMessage = "Sehr geehrte(r) Frau/Herr " . $MailEmpfaenger->org_name . ",<br/><br/>";
$HTMLMessage .= "die folgende Aufgabe wurde Ihnen zugeordnet:<br/>";
$HTMLMessage .= "<p><table><tr><td bgcolor='#efefef'>TOP Nr.:</td><td>" . $Aufgabe->idtm_protokoll_detail_group . " </td><td bgcolor='#efefef'>Thema: </td><td>" . utf8_decode($Aufgabe->prtdet_topic) . "</td></tr>";
$HTMLMessage .= "<tr><td bgcolor='#efefef'>Bis:</td><td colspan='3'><b>" . $Aufgabe->auf_tdate . "</b></td></tr></table>";
$HTMLMessage .= "<p style='background-color:#efefef'><hr/>" . utf8_decode($Aufgabe->prtdet_descr) . "</p>";
$HTMLMessage .= "<p style='background-color:#efefef'><hr/>" . utf8_decode($Aufgabe->auf_beschreibung) . "<hr/></p>";
$HTMLMessage .= "Bitte nicht Antworten! Diese Mail wurde automatisch generiert...";
$mail->MsgHTML($HTMLMessage);
if (!$mail->Send()) {
$this->PFMAILER->TEXT = "error sending the message";
} else {
$this->PFMAILER->TEXT = "..done..";
}
}
protected function bindRepProtokollDetail()
{
$criteria = new TActiveRecordCriteria();
$criteria->Condition = "idtm_protokoll LIKE :suchtext";
$criteria->Parameters[':suchtext'] = "%" . $this->idtm_protokoll . "%";
$this->RepProtokollDetail->DataSource = ProtokollDetailAufgabeView::finder()->findAll($criteria);
$this->RepProtokollDetail->dataBind();
}
public function loadOrganisation($sender, $param)
{
$criteria = new TActiveRecordCriteria();
$criteria->Condition = "idtm_organisation = :suchtext";
$criteria->Parameters[':suchtext'] = $this->idtm_organisation->Text;
$this->ProtokollDetailGroupList->DataSource = ProtokollDetailAufgabeView::finder()->findAll($criteria);
$this->ProtokollDetailGroupList->dataBind();
}
private function LetterContent($idtm_protokoll)
{
$SQL = "SELECT a.* FROM `vv_protokoll_detail_aufgabe` a";
$SQL .= " INNER JOIN ta_protokoll_detail_group b ON a.idtm_protokoll_detail_group = b.idtm_protokoll_detail_group";
$SQL .= " WHERE a.idtm_protokoll = " . $idtm_protokoll;
$SQL .= " ORDER BY idtm_protokoll_detail_group, idtm_protokoll_detail ";
$ProtokollDetails = ProtokollDetailAufgabeView::finder()->findAllBySQL($SQL);
$this->pdf->Ln(10);
//$this->pdf->SetX(20);
$this->pdf->SetFillColor(234, 242, 220);
$this->pdf->SetDrawColor(234, 242, 220);
$this->pdf->SetFont('Arial', 'B', 12);
$this->pdf->Cell(185, 6, 'Protokoll ', 1, 0, 'B', 1);
$this->InternalCounter++;
foreach ($ProtokollDetails as $ProtokollDetail) {
$this->pdf->Ln(7);
//$this->pdf->SetX(20);
$this->pdf->SetFont('Arial', '', 11);
$this->pdf->SetFillColor(116, 145, 97);
$this->pdf->SetDrawColor(116, 145, 97);
$this->pdf->Cell(15, 6, $this->InternalCounter, 1, 0, 'C', 1);
$this->InternalCounter++;
$this->pdf->SetFillColor(255, 255, 255);
$this->pdf->SetDrawColor(234, 242, 220);
$this->pdf->SetFont('Arial', 'B', 10);
$this->pdf->Cell(5, 6, ' ', 1, 0, '', 1);
$this->pdf->Cell(165, 6, utf8_decode($ProtokollDetail->prtdet_topic), 1, 0, '', 1);
$this->pdf->SetFont('Arial', '', 11);
$this->pdf->Ln();
//$this->pdf->SetX(28);
$this->pdf->SetFillColor(255, 255, 255);
$this->pdf->WriteHTML($ProtokollDetail->prtdet_descr);
if ($ProtokollDetail->idta_protokoll_ergebnistype < 3) {
// $this->pdf->Ln(7);
//$this->pdf->SetX(20);
$this->pdf->SetFillColor(255, 165, 0);
$this->pdf->SetDrawColor(255, 165, 0);
$this->pdf->SetFont('Arial', 'B', 8);
$this->pdf->Cell(170, 5, 'Auftrag', 1, 0, '', 1);
$this->pdf->Ln();
//$this->pdf->SetX(20);
$this->pdf->SetFillColor(255, 255, 255);
$this->pdf->SetFont('Arial', '', 9);
$this->pdf->MultiCell(170, 6, utf8_decode($ProtokollDetail->auf_beschreibung), 1, 'L', 0);
$this->pdf->SetFillColor(255, 255, 255);
$this->pdf->Cell(110, 5, ' Wer', 1, 0, '', 1);
$this->pdf->Cell(30, 5, ' Bis', 1, 0, '', 1);
$this->pdf->Cell(30, 5, ' Erledigt', 1, 0, '', 1);
$this->pdf->Ln();
$this->pdf->Cell(110, 6, " " . utf8_decode(OrganisationRecord::finder()->findByPk($ProtokollDetail->idtm_organisation)->org_name), 1, '');
$this->pdf->Cell(30, 6, $ProtokollDetail->auf_tdate, 1, 'L', 0);
$this->pdf->Cell(30, 6, $ProtokollDetail->auf_done == 1 ? $ProtokollDetail->auf_ddate : '-', 1, 'L', 0);
$this->pdf->Ln(7);
}
}
}
public function bindListPrtListe()
{
$criteria = new TActiveRecordCriteria();
if ($this->WINprtdet_topic->Text != "") {
$criteria->Condition = "prtdet_topic LIKE :suchtext1";
$criteria->Parameters[':suchtext1'] = '%' . $this->WINprtdet_topic->Text . '%';
var_dump($criteria);
$this->PrtListe->DataSource = ProtokollDetailAufgabeView::finder()->findAll($criteria);
$this->PrtListe->dataBind();
}
}
public function load_prttopics($sender, $param)
{
$item = $param->Item;
if ($item->ItemType === 'Item' || $item->ItemType === 'AlternatingItem') {
$sql = "SELECT idtm_protokoll_detail_group, prtdet_topic,idta_protokoll_ergebnistype,auf_done,auf_tdate, prtdet_descr, idtm_protokoll_detail, idtm_organisation,org_name,idta_organisation_art FROM vv_protokoll_detail_aufgabe WHERE idtm_protokoll = " . $item->Data->idtm_protokoll;
if ($this->CBAufgabeDone->Text == 0 and $this->CBAufgabeDone->Text != '') {
$sql .= " AND (auf_done = " . $this->CBAufgabeDone->Text . " AND idta_protokoll_ergebnistype<3)";
}
if ($this->CBAufgabeDone->Text == 1 and $this->CBAufgabeDone->Text != '') {
$sql .= " AND ((auf_done = " . $this->CBAufgabeDone->Text . " AND idta_protokoll_ergebnistype<3) OR idta_protokoll_ergebnistype>2)";
}
if ($this->CBidta_organisation_art->Text >= 0 and $this->CBidta_organisation_art->Text != '') {
$sql .= " AND (idta_organisation_art = " . $this->CBidta_organisation_art->Text . ")";
}
$item->CCProtokollTops->DataSource = ProtokollDetailAufgabeView::finder()->findAllBySQL($sql);
$item->CCProtokollTops->dataBind();
}
}
private function generateActivityTypeCookieImage($idtm_activity, $myActInSQL)
{
$xdata = array();
$ii = 0;
$taskStati = array(1 => "Beschluss", "Auftrag", "Info", "sonstiges");
$sql = "SELECT idta_protokoll_ergebnistype, count(idtm_aufgaben) AS idtm_aufgaben FROM vv_protokoll_detail_aufgabe WHERE " . $myActInSQL . " GROUP BY idta_protokoll_ergebnistype ORDER BY idta_protokoll_ergebnistype ASC";
//print_r($sql);
$ActiveRecord = ProtokollDetailAufgabeView::finder()->findAllBySQL($sql);
if (is_array($ActiveRecord)) {
foreach ($ActiveRecord as $DetailRecord) {
$xdata[] = "[" . $DetailRecord->idtm_aufgaben . ",'" . $taskStati[$DetailRecord->idta_protokoll_ergebnistype] . "']";
}
unset($ActiveRecord);
if (count($xdata) > 1) {
$xdata = implode(',', $xdata);
}
if (count($xdata) >= 1) {
$this->getPage()->getClientScript()->registerEndScript('xtci', "drawPFChart('ActivityTypeCookieImage',new Array({$xdata}),'pie')");
} else {
$xdata[] = "[0,'no Data']";
$this->getPage()->getClientScript()->registerEndScript('xtci', "drawPFChart('ActivityTypeCookieImage',new Array({$xdata}),'pie')");
}
unset($xdata);
}
}
public function onPreInit($param)
{
parent::onPreInit($param);
$completeCounter = 1;
$this->idtm_termin = $_GET['idtm_termin'];
$this->idtm_protokoll = $_GET['idtm_protokoll'];
$idtm_termin = $this->idtm_termin;
$idtm_protokoll = $this->idtm_protokoll;
$this->workbook = new PHPExcel();
$sheet = $this->workbook->getActiveSheet();
$sheet->setTitle('Protokoll');
$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_DEFAULT);
$sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$sheet->getRowDimension('1')->setRowHeight(40);
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(20);
$sheet->getColumnDimension('D')->setWidth(5);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('F')->setWidth(20);
$this->workbook->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->workbook->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setRGB('898989');
$this->workbook->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
$this->workbook->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('FFFFFF');
$sheet->mergeCells('A1:B1');
$sheet->setCellValue('A1', "Protokoll");
$this->workbook->getActiveSheet()->duplicateStyle($this->workbook->getActiveSheet()->getStyle('A1'), 'A' . $completeCounter . ':H' . $completeCounter);
$counter = 5;
$completeCounter = $counter;
$this->workbook->getActiveSheet()->getStyle('B5')->applyFromArray(array('font' => array('bold' => true, 'size' => 12, 'color' => array('argb' => 'FFFFFFFF')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'FFA0A0A0'))));
$this->workbook->getActiveSheet()->duplicateStyle($this->workbook->getActiveSheet()->getStyle('B5'), 'B' . $counter . ':C' . $counter);
$this->workbook->getActiveSheet()->duplicateStyle($this->workbook->getActiveSheet()->getStyle('B5'), 'E' . $counter . ':G' . $counter);
//der header fuer den Bereich
$sheet->setCellValue('B' . $counter, "Name");
$sheet->setCellValue('C' . $counter, "Projektrolle");
$sheet->setCellValue('E' . $counter, "Name");
$sheet->setCellValue('F' . $counter, "Kommentar");
$sheet->setCellValue('G' . $counter, "AV");
$counter++;
$criteria = new TActiveRecordCriteria();
$criteria->Condition = "idtm_termin = :suchtext";
$criteria->Parameters[':suchtext'] = $idtm_termin;
$criteria->OrdersBy["idtm_organisation"] = 'asc';
$Records = TerminOrganisationView::finder()->findAll($criteria);
foreach ($Records as $Record) {
$sheet->setCellValue('B' . $counter, htmlentities($Record->org_name));
$sheet->setCellValue('C' . $counter, htmlentities($Record->user_role_name));
$counter++;
}
$counter2 = $completeCounter;
$counter2++;
$criteria2 = new TActiveRecordCriteria();
$criteria2->Condition = "idtm_activity = :suchtext";
$criteria2->Parameters[':suchtext'] = $idtm_termin;
$Records2 = ActivityParticipantsView::finder()->findAll($criteria2);
foreach ($Records2 as $Record) {
$sheet->setCellValue('E' . $counter2, htmlentities($Record->org_name));
$sheet->setCellValue('F' . $counter2, htmlentities($Record->act_part_notiz));
$sheet->setCellValue('G' . $counter2, htmlentities($Record->act_part_anwesend));
$counter2++;
}
$counter >= $counter2 ? $completeCounter = $counter : ($completeCounter = $counter2);
$completeCounter++;
$counter3 = $completeCounter;
$counter3++;
$this->workbook->getActiveSheet()->getStyle('B' . $completeCounter)->getAlignment()->setWrapText(true);
$this->workbook->getActiveSheet()->getStyle('B' . $completeCounter)->getAlignment()->setShrinkToFit(true);
$this->workbook->getActiveSheet()->getStyle('B' . $completeCounter)->getFont()->setSize(12);
$this->workbook->getActiveSheet()->getStyle('B' . $completeCounter)->getFont()->getColor()->setRGB('232323');
$SQL = "SELECT a.* FROM `vv_protokoll_detail_aufgabe` a INNER JOIN ta_protokoll_detail_group b ON a.idta_protokoll_detail_group = b.idta_protokoll_detail_group";
$SQL .= " WHERE idtm_protokoll = " . $idtm_protokoll;
$Records3 = ProtokollDetailAufgabeView::finder()->findAllBySQL($SQL);
foreach ($Records3 as $Record) {
$sheet->setCellValue('B' . $counter3, htmlentities($Record->idta_protokoll_detail_group));
$sheet->mergeCells('C' . $counter3 . ':D' . $counter3);
$sheet->setCellValue('C' . $counter3, htmlentities($Record->prtdet_cdate));
$sheet->setCellValue('E' . $counter3, htmlentities($Record->prtdet_topic));
$this->workbook->getActiveSheet()->getStyle('B' . $counter3)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->workbook->getActiveSheet()->getStyle('B' . $counter3)->getFill()->getStartColor()->setRGB('A9AB78');
$this->workbook->getActiveSheet()->duplicateStyle($this->workbook->getActiveSheet()->getStyle('B' . $counter3), 'B' . $counter3 . ':G' . $counter3);
$counter3++;
$sheet->mergeCells('B' . $counter3 . ':G' . $counter3);
$sheet->setCellValue('B' . $counter3, utf8_decode(strip_tags($Record->prtdet_descr)));
$this->workbook->getActiveSheet()->duplicateStyle($this->workbook->getActiveSheet()->getStyle('B' . $completeCounter), 'B' . $counter3 . ':G' . $counter3);
$counter3++;
}
$this->generate('Excel2007', 'risklogiq2009');
}
