function getWkshtListFeed($gdClient, $ssKey, $wkshtId, $queryString = null)
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($ssKey);
$query->setWorksheetId($wkshtId);
if ($queryString !== null) {
$query->setSpreadsheetQuery($queryString);
}
$listFeed = $gdClient->getListFeed($query);
return $listFeed;
}
/**
* Find rows in the spreadsheet
* @param $q string|null - the search string
* @return int - number of rows found
*/
function find($q = null)
{
if ($q && is_string($q)) {
$this->user_query = $q;
}
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->ssid);
$query->setWorksheetId($this->wsid);
if ($this->user_query) {
$query->setSpreadsheetQuery($this->user_query);
}
try {
$this->listFeed = $this->service->getListFeed($query);
} catch (Exception $e) {
return 0;
}
return (int) $this->listFeed->getTotalResults()->text;
}
* @return void
*/
public function printFeed($feed)
{
$i = 0;
foreach ($feed->entries as $entry) {
if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
echo $entry->title->text . ' ' . $entry->content->text . ' ' . $entry->id->text . "</br>";
} else {
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
echo $i . ' ' . $entry->title->text . ' | ' . $entry->content->text . "\n";
} else {
echo $i . ' ' . $entry->title->text . ' ' . $entry->content->text . ' ' . $entry->id->text . "</br>";
if ($entry->title->text == 'HuynhTruong') {
$currWkshtId = explode('/', $entry->id->text);
$this->currWkshtId = $currWkshtId[8];
echo $currWkshtId[8] . "</br>";
}
}
public function execute($arguments = array(), $options = array())
{
echo 'START KINTAI BOT.\\n';
$details = array();
$databaseManager = new sfDatabaseManager($this->configuration);
$service = self::getZendGdata();
$p = array();
$dql = Doctrine_Query::create()->from('Member m')->where('m.is_active = ?', '1');
if (!is_null($options['start-member-id']) && is_numeric($options['start-member-id'])) {
$dql = $dql->andWhere('m.id >= ?', $options['start-member-id']);
}
if (!is_null($options['end-member-id']) && is_numeric($options['end-member-id'])) {
$dql = $dql->andWhere('m.id <= ?', $options['end-member-id']);
}
$members = $dql->execute();
$rawKey = opConfig::get('op_kintai_spkey', null);
$wid = self::getRowId($service, $rawKey);
foreach ($members as $member) {
//変数初期化
list($memberId, $memberspkey, $memberWorkSheetId, $memberMasterSpkey, $memberMasterWorkSheetId) = array(null, null, null, null, null);
$memberId = $member->getId();
$memberspkey = self::getMemberSpreadSheetKey($service, $memberId);
if (!is_null($memberspkey)) {
$memberWorkSheetId = self::getMemberWorkSheetId($service, $memberspkey);
}
$memberMasterSpkey = self::getMemberMasterSpreadSheetKey($service, $memberId);
if (!is_null($memberMasterSpkey)) {
$memberMasterWorkSheetId = self::getMemberMasterWorkSheetId($service, $memberMasterSpkey);
}
echo '==== debug info =====\\n';
echo 'Member Id : ' . $memberId . '\\n';
echo 'rawkey: ' . $rawKey . ' || rowid: ' . $wid . '\\n';
echo 'Key: ' . $memberspkey . ' || WorkSheetId: ' . $memberWorkSheetId . '\\n';
echo 'MasterSpkey: ' . $memberMasterSpkey . ' || MasterWorkSheetId: ' . $memberMasterWorkSheetId . '\\n';
// スプレッドシートで勤怠報告しているメンバーの勤怠を処理する。
if (!is_null($memberspkey) && !is_null($memberWorkSheetId)) {
$previousMonth = date('m') - 1;
$year = date('Y');
$today = date('d');
if (!checkdate($previousMonth, 1, $year)) {
$previousMonth = 12;
$year = $year - 1;
}
// 先月分の勤怠を処理する。
for ($i = 1; $i < 31; $i++) {
if (checkdate($previousMonth, $i, $year)) {
$q = new Zend_Gdata_Spreadsheets_ListQuery();
$q->setSpreadsheetKey($memberspkey);
$q->setWorksheetId($memberWorkSheetId);
$query = 'date=' . $year . '/' . $previousMonth . '/' . $i;
$q->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($q);
if (!$lineList) {
continue;
} else {
foreach ($lineList->entries as $entry) {
$lines = $entry->getCustom();
foreach ($lines as $line) {
$key = $line->getColumnName();
switch ($key) {
case 'date':
$date = $line->getText();
break;
case 'ssh':
$ssh = $line->getText();
break;
case 'ssm':
$ssm = $line->getText();
break;
case 'seh':
$seh = $line->getText();
break;
case 'sem':
$sem = $line->getText();
break;
case 'srh':
$srh = $line->getText();
break;
case 'srm':
$srm = $line->getText();
break;
case 'zsh':
$zsh = $line->getText();
break;
case 'zsm':
$zsm = $line->getText();
break;
case 'zeh':
$zeh = $line->getText();
break;
case 'zem':
$zem = $line->getText();
break;
case 'zrh':
$zrh = $line->getText();
break;
case 'zrm':
$zrm = $line->getText();
break;
default:
// 何もしない。
}
}
}
$detail = array('date' => $date, 'ssh' => $ssh, 'ssm' => $ssm, 'seh' => $seh, 'sem' => $sem, 'srh' => $srh, 'srm' => $srm, 'zsh' => $zsh, 'zsm' => $zsm, 'zeh' => $zeh, 'zem' => $zem, 'zrh' => $zrh, 'zrm' => $zrm);
list($date, $ssh, $ssm, $seh, $sem, $srh, $srm, $zsh, $zsm, $zeh, $zem, $zrh, $zrm) = array(null, null, null, null, null, null, null, null, null, null, null, null, null);
$r = new Zend_Gdata_Spreadsheets_ListQuery();
$r->setSpreadsheetKey($memberMasterSpkey);
$r->setWorksheetId($memberMasterWorkSheetId);
$query = 'date=' . $year . '/' . $previousMonth . '/' . $i;
$r->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($r);
if ($lineList) {
$update = $service->updateRow($lineList->entries['0'], $detail);
if ($update) {
echo sprintf('UPDATE SUCCESS!(SpreadSheet) memberId: %s date: %s;\\n', $memberId, $detail['date']);
} else {
echo sprintf('ERROR! NO UPDATED.(SpreadSheet) Maybe Internal Server Error Occured on Google Service. memberId: %s date: %s;', $memberId, $detail['date']);
}
} else {
echo sprintf('ERROR! NO UPDATED.(SpreadSheet) Maybe Spreadsheet has been broken. memberId: %s date %s;', $memberId, $detail['date']);
}
}
} else {
break;
}
}
// 今月分の勤怠を処理する。
for ($i = 1; $i < $today; $i++) {
if (checkdate(date('m'), $i, date('Y'))) {
$s = new Zend_Gdata_Spreadsheets_ListQuery();
$s->setSpreadsheetKey($memberspkey);
$s->setWorksheetId($memberWorkSheetId);
$query = 'date=' . date('Y') . '/' . date('m') . '/' . $i;
$s->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($s);
if (!$lineList) {
continue;
} else {
foreach ($lineList->entries as $entry) {
$lines = $entry->getCustom();
foreach ($lines as $line) {
$key = $line->getColumnName();
switch ($key) {
case 'date':
$date = $line->getText();
break;
case 'ssh':
$ssh = $line->getText();
break;
case 'ssm':
$ssm = $line->getText();
break;
case 'seh':
$seh = $line->getText();
break;
case 'sem':
$sem = $line->getText();
break;
case 'srh':
$srh = $line->getText();
break;
case 'srm':
$srm = $line->getText();
break;
case 'zsh':
$zsh = $line->getText();
break;
case 'zsm':
$zsm = $line->getText();
break;
case 'zeh':
$zeh = $line->getText();
break;
case 'zem':
$zem = $line->getText();
break;
case 'zrh':
$zrh = $line->getText();
break;
case 'zrm':
$zrm = $line->getText();
break;
default:
// 何もしない。
}
}
}
$detail = array('date' => $date, 'ssh' => $ssh, 'ssm' => $ssm, 'seh' => $seh, 'sem' => $sem, 'srh' => $srh, 'srm' => $srm, 'zsh' => $zsh, 'zsm' => $zsm, 'zeh' => $zeh, 'zem' => $zem, 'zrh' => $zrh, 'zrm' => $zrm);
list($date, $ssh, $ssm, $seh, $sem, $srh, $srm, $zsh, $zsm, $zeh, $zem, $zrh, $zrm) = array(null, null, null, null, null, null, null, null, null, null, null, null, null);
$t = new Zend_Gdata_Spreadsheets_ListQuery();
$t->setSpreadsheetKey($memberMasterSpkey);
$t->setWorksheetId($memberMasterWorkSheetId);
$query = 'date=' . date('Y') . '/' . date('m') . '/' . $i;
$t->setSpreadsheetQuery($query);
$lineList2 = $service->getListFeed($t);
if ($lineList2) {
$update = $service->updateRow($lineList2->entries['0'], $detail);
if ($update) {
echo sprintf('UPDATE SUCCESS!(SpreadSheet) memberId: %s date: %s;\\n', $memberId, $detail['date']);
} else {
echo sprintf('ERROR! NO UPDATED.(SpreadSheet) Maybe Internal Server Error Occured on Google Service. memberId: %s date: %s;', $memberId, $detail['date']);
}
} else {
echo sprintf('ERROR! NO UPDATED.(SpreadSheet) Maybe Spreadsheet has been broken. memberId: %s date %s;', $memberId, $detail['date']);
}
}
} else {
break;
}
}
} elseif (!is_null($memberMasterSpkey) && !is_null($memberMasterWorkSheetId)) {
$previousMonth = date('m') - 1;
$year = date('Y');
$today = date('d');
if (strlen($previousMonth) == 1) {
$previousMonth = '0' . $previousMonth;
}
if (!checkdate($previousMonth, 1, $year)) {
$previousMonth = 12;
$year = $year - 1;
}
// 先月分の勤怠を処理する。
for ($i = 1; $i < 31; $i++) {
if (checkdate($previousMonth, $i, $year)) {
$j = $i;
if (strlen($j) == 1) {
$j = '0' . $j;
}
echo 'Scanning: ' . $year . '/' . $previousMonth . '/' . $j . '...';
$u = new Zend_Gdata_Spreadsheets_ListQuery();
$u->setSpreadsheetKey($rawKey);
$u->setWorksheetId($wid);
$query = 'id=' . $memberId . ' and date=' . $year . '/' . $previousMonth . '/' . $j;
$u->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($u);
if (!$lineList->entries['0']) {
echo 'skip\\n';
continue;
} else {
foreach ($lineList->entries as $entry) {
$lines = $entry->getCustom();
foreach ($lines as $line) {
$key = $line->getColumnName();
switch ($key) {
case 'date':
$date = $line->getText();
break;
case 'data':
$data = $line->getText();
break;
case 'comment':
$comment = $line->getText();
break;
default:
// 何もしない。
}
}
}
if (strlen($data) == 12) {
$keitai = substr($data, 0, 1);
if ($keitai == 'S') {
$ssh = substr($data, 1, 2);
$ssm = substr($data, 3, 2);
$seh = substr($data, 5, 2);
$sem = substr($data, 7, 2);
$srest = substr($data, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($zrm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data, 1, 2);
$zsm = substr($data, 3, 2);
$zeh = substr($data, 5, 2);
$zem = substr($data, 7, 2);
$zrest = substr($data, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
} elseif (strlen($data) == 24) {
$data1 = substr($data, 0, 12);
$data2 = substr($data, 12, 12);
$keitai1 = substr($data1, 0, 1);
if ($keitai1 == 'S') {
$ssh = substr($data1, 1, 2);
$ssm = substr($data1, 3, 2);
$seh = substr($data1, 5, 2);
$sem = substr($data1, 7, 2);
$srest = substr($data1, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($srm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data1, 1, 2);
$zsm = substr($data1, 3, 2);
$zeh = substr($data1, 5, 2);
$zem = substr($data1, 7, 2);
$zrest = substr($data1, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
if ($keitai2 == 'S') {
$ssh = substr($data2, 1, 2);
$ssm = substr($data2, 3, 2);
$seh = substr($data2, 5, 2);
$sem = substr($data2, 7, 2);
$srest = substr($data2, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($zrm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data2, 1, 2);
$zsm = substr($data2, 3, 2);
$zeh = substr($data2, 5, 2);
$zem = substr($data2, 7, 2);
$zrest = substr($data2, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
}
$detail = array('date' => $date, 'ssh' => $ssh, 'ssm' => $ssm, 'seh' => $seh, 'sem' => $sem, 'srh' => $srh, 'srm' => $srm, 'zsh' => $zsh, 'zsm' => $zsm, 'zeh' => $zeh, 'zem' => $zem, 'zrh' => $zrh, 'zrm' => $zrm);
list($date, $data, $data1, $data2, $keitai, $keitai1, $keitai2, $comment, $ssh, $ssm, $seh, $sem, $srh, $srm, $zsh, $zsm, $zeh, $zem, $zrh, $zrm) = array(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
$v = new Zend_Gdata_Spreadsheets_ListQuery();
$v->setSpreadsheetKey($memberMasterSpkey);
$v->setWorksheetId($memberMasterWorkSheetId);
$query = 'date=' . $year . '/' . $previousMonth . '/' . $i;
$v->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($v);
if ($lineList) {
$update = $service->updateRow($lineList->entries['0'], $detail);
if ($update) {
echo sprintf('UPDATE SUCCESS!(OP3-previous-month) memberId: %s date: %s;\\n', $memberId, $detail['date']);
} else {
echo sprintf('ERROR! NO UPDATED.(OP3) Maybe Internal Server Error Occured on Google Service. memberId: %s date: %s;', $memberId, $detail['date']);
}
} else {
echo sprintf('ERROR! NO UPDATED. (OP3) Maybe Spreadsheet has been broken. memberId: %s date %s;', $memberId, $detail['date']);
}
$date = '';
}
} else {
break;
}
$detail = array();
}
// 今月の勤怠の処理をする。
$today = date('d');
for ($i = 1; $i < $today; $i++) {
if (checkdate(date('m'), $i, date('Y'))) {
$j = $i;
if (strlen($j) == 1) {
$j = '0' . $j;
}
echo 'Scanning: ' . $year . '/' . date('m') . '/' . $j . '... ';
$w = new Zend_Gdata_Spreadsheets_ListQuery();
$w->setSpreadsheetKey($rawKey);
$w->setWorksheetId($wid);
$query = 'id=' . $memberId . ' and date=' . date('Y') . '/' . date('m') . '/' . $j;
$w->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($w);
if (!$lineList->entries['0']) {
echo 'skip\\n';
continue;
} else {
foreach ($lineList->entries as $entry) {
$lines = $entry->getCustom();
foreach ($lines as $line) {
$key = $line->getColumnName();
switch ($key) {
case 'date':
$date = $line->getText();
break;
case 'data':
$data = $line->getText();
break;
case 'comment':
$comment = $line->getText();
break;
default:
// 何もしない。
}
}
}
if (strlen($data) == 12) {
$keitai = substr($data, 0, 1);
if ($keitai == 'S') {
$ssh = substr($data, 1, 2);
$ssm = substr($data, 3, 2);
$seh = substr($data, 5, 2);
$sem = substr($data, 7, 2);
$srest = substr($data, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($srm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data, 1, 2);
$zsm = substr($data, 3, 2);
$zeh = substr($data, 5, 2);
$zem = substr($data, 7, 2);
$zrest = substr($data, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
} elseif (strlen($data) == 24) {
$data1 = substr($data, 0, 12);
$data2 = substr($data, 12, 12);
$keitai1 = substr($data1, 0, 1);
if ($keitai1 == 'S') {
$ssh = substr($data1, 1, 2);
$ssm = substr($data1, 3, 2);
$seh = substr($data1, 5, 2);
$sem = substr($data1, 7, 2);
$srest = substr($data1, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($srm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data1, 1, 2);
$zsm = substr($data1, 3, 2);
$zeh = substr($data1, 5, 2);
$zem = substr($data1, 7, 2);
$zrest = substr($data1, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
if ($keitai2 == 'S') {
$ssh = substr($data2, 1, 2);
$ssm = substr($data2, 3, 2);
$seh = substr($data2, 5, 2);
$sem = substr($data2, 7, 2);
$srest = substr($data2, 9, 3);
$srh = floor($srest / 60);
$srm = $srest - $srh * 60;
if ($srh == 0) {
$srh = '0';
}
if ($srm == 0) {
$srm = '0';
}
} else {
$zsh = substr($data2, 1, 2);
$zsm = substr($data2, 3, 2);
$zeh = substr($data2, 5, 2);
$zem = substr($data2, 7, 2);
$zrest = substr($data2, 9, 3);
$zrh = floor($zrest / 60);
$zrm = $zrest - $zrh * 60;
if ($zrh == 0) {
$zrh = '0';
}
if ($zrm == 0) {
$zrm = '0';
}
}
}
$detail = array('date' => $date, 'ssh' => $ssh, 'ssm' => $ssm, 'seh' => $seh, 'sem' => $sem, 'srh' => $srh, 'srm' => $srm, 'zsh' => $zsh, 'zsm' => $zsm, 'zeh' => $zeh, 'zem' => $zem, 'zrh' => $zrh, 'zrm' => $zrm);
// 変数を一括初期化。
list($date, $data, $data1, $data2, $keitai, $keitai1, $keitai2, $comment, $ssh, $ssm, $seh, $sem, $srh, $srm, $zsh, $zsm, $zeh, $zem, $zrh, $zrm) = array(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
$x = new Zend_Gdata_Spreadsheets_ListQuery();
$x->setSpreadsheetKey($memberMasterSpkey);
$x->setWorksheetId($memberMasterWorkSheetId);
$query = 'date=' . date('Y') . '/' . date('m') . '/' . $i;
$x->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($x);
if ($lineList) {
$update = $service->updateRow($lineList->entries['0'], $detail);
if ($update) {
echo sprintf('UPDATE SUCCESS! (OP3) memberId: %s date: %s;\\n', $memberId, $detail['date']);
} else {
echo sprintf('ERROR! NO UPDATED. (OP3) Maybe Internal Server Error Occured on Google Service. memberId: %s date: %s;', $memberId, $detail['date']);
}
} else {
echo sprintf('ERROR! NO UPDATED. (OP3) Maybe Spreadsheet has been broken. memberId: %s date %s;', $memberId, $detail['date']);
}
}
} else {
break;
}
$detail = array();
}
}
}
}
private function findRows($search = FALSE)
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->getSpreadsheetId());
$query->setWorksheetId($this->getWorksheetId());
if ($search) {
$query->setSpreadsheetQuery($search);
}
$feed = $this->client->getListFeed($query);
return $feed;
}
<?php
function labelBOD($entry)
{
if ($entry->getCustomByName('bodindustryprivatesector') == 'Y' || $entry->getCustomByName('bodacademicresearcheducationsector') == 'Y' || $entry->getCustomByName('bodpublicagenciesnonprofitothersector') == 'Y' || $entry->getCustomByName('bodatlarge') == 'Y' || $entry->getCustomByName('bodpublicseat') == 'Y' || $entry->getCustomByName('bodaffiliateseat') == 'Y' || $entry->getCustomByName('bodsustainingseat') == 'Y') {
return ' (B)';
} else {
return '';
}
}
// print order
$posts = array('Chair', 'Co-Chair', 'Board Liaison', 'Member', 'Staff');
$a = array();
foreach ($posts as $p) {
$a[$p] = array();
}
$query->setSpreadsheetQuery($post_col . ' <> ""');
$listFeed = $service->getListFeed($query);
foreach ($listFeed->entries as $entry) {
$board = labelBOD($entry);
array_push($a[(string) $entry->getCustomByName($post_col)], '<a href="mailto:' . $entry->getCustomByName('email') . '">' . $entry->getCustomByName('name') . '</a>' . $board . ', ' . $entry->getCustomByName('institutionorganization'));
}
foreach ($posts as $p) {
if (count($a[$p]) > 0) {
$s = '';
if (count($a[$p]) > 1 && $p != 'Staff') {
$s = 's';
}
echo "<p><em><strong>{$p}{$s}</strong></em><br />";
foreach ($a[$p] as $person) {
echo $person . "<br />";
}
$iconNode = $dom->CreateElement('Icon');
$iconNode->appendChild($dom->CreateElement('href', 'http://maps.google.com/mapfiles/kml/paddle/ltblu-blank.png'));
$iconStyleNode->appendChild($iconNode);
$styleNode->appendChild($iconStyleNode);
$docNode->appendChild($styleNode);
$styleNode = $dom->CreateElement('Style');
$styleNode->setAttribute('id', 'green');
$iconStyleNode = $dom->CreateElement('IconStyle');
$iconNode = $dom->CreateElement('Icon');
$iconNode->appendChild($dom->CreateElement('href', 'http://maps.google.com/mapfiles/kml/paddle/grn-blank.png'));
$iconStyleNode->appendChild($iconNode);
$styleNode->appendChild($iconStyleNode);
$docNode->appendChild($styleNode);
$folderNode = $dom->CreateElement('Folder');
$folderNode->appendChild($dom->CreateElement('name', 'Institutional Members'));
$query->setSpreadsheetQuery('institutional = "Y"');
$listFeed = $service->getListFeed($query);
foreach ($listFeed->entries as $entry) {
$placemarkNode = $dom->CreateElement('Placemark');
$placemarkNode->appendChild($dom->CreateElement('styleUrl', '#yellow'));
$placemarkNode->appendChild($dom->CreateElement('name', $entry->getCustomByName('name')));
$descriptionNode = $dom->CreateElement('description');
$descriptionNode->appendChild($dom->createCDATASection('<table style="width:100%;border:1px solid lightgray">' . '<tr>' . '<td style="vertical-align:top" colspan=2 align=center><b>Institutional Member</b></td>' . '</tr>' . '<tr>' . '<td style="vertical-align:top"> <b>Representative </b></td>' . '<td style="vertical-align:top"> ' . $entry->getCustomByName('representativeorganization') . ' </td>' . '</tr>' . '<tr>' . '<td style="vertical-align:top"> <b>Director? </b></td>' . '<td style="vertical-align:top"> ' . ($entry->getCustomByName('director') == 'Y' ? 'Y' : 'N') . ' </td>' . '</tr>' . '<tr>' . '<td style="vertical-align:top"> <b>State </b></td>' . '<td style="vertical-align:top"> ' . $entry->getCustomByName('state') . ' </td>' . '</tr>' . '<tr>' . '<td style="vertical-align:top"> <b>Date Joined </b></td>' . '<td style="vertical-align:top"> ' . $entry->getCustomByName('datejoined') . ' </td>' . '</tr>' . '<tr>' . '<td style="vertical-align:top" colspan=2 align=center><a href="http://secoora.org"><img border=0 src="http://carocoops.org/spreadsheet/secoora_small.png"></a></td>' . '</tr>' . '</table>'));
$placemarkNode->appendChild($descriptionNode);
$pointNode = $dom->CreateElement('Point');
$pointNode->appendChild($dom->CreateElement('coordinates', $entry->getCustomByName('longitude') . ',' . $entry->getCustomByName('latitude')));
$placemarkNode->appendChild($pointNode);
$folderNode->appendChild($placemarkNode);
}
$docNode->appendChild($folderNode);
$folderNode = $dom->CreateElement('Folder');
public function executeAjaxSend(sfWebRequest $request)
{
$service = self::getZendGdata();
$wid = self::getRowId($service);
if ($request->isMethod(sfWebRequest::POST)) {
$y = $request->getParameter('y');
$m = $request->getParameter('m');
$d = $request->getParameter('d');
$memberId = $this->getUser()->getMemberId();
$data = $request->getParameter('data');
$comment = $request->getParameter('comment');
$keitai = substr($data, 0, 1);
$start = array();
$end = array();
$start["hour"] = substr($data, 1, 2);
$start["minute"] = substr($data, 3, 2);
$end["hour"] = substr($data, 5, 2);
$end["minute"] = substr($data, 7, 2);
$start["time"] = $start["hour"] * 60 + $start["minute"];
$end["time"] = $end["hour"] * 60 + $end["minute"];
$rest = substr($data, 9, 3);
if (substr($rest, 0, 1) == "0") {
$rest = substr($rest, 1, 2);
}
$jitsumu = $end["time"] - $start["time"] - $rest;
if (strlen($data) == 24) {
$keitai2 = substr($data, 12, 1);
$start2["hour"] = substr($data, 13, 2);
$start2["minute"] = substr($data, 15, 2);
$end2["hour"] = substr($data, 17, 2);
$end2["minute"] = substr($data, 19, 2);
$start2["time"] = $start2["hour"] * 60 + $start2["minute"];
$end2["time"] = $end2["hour"] * 60 + $end2["minute"];
$rest2 = substr($data, 21, 3);
if (substr($rest2, 0, 1) == "0") {
$rest2 = substr($rest2, 1, 2);
}
$jitsumu2 = $end2["time"] - $start2["time"] - $rest2;
}
//Validation
$message = null;
if (strlen($data) != 12 && strlen($data) != 24) {
$message .= "入力が不正です。<br />";
}
if (!preg_match("/^[0-2][0-9]\$/", $start["hour"]) || !preg_match("/^[0-5][0-9]\$/", $start["minute"])) {
$message .= "始業時間の入力が誤っています。<br />";
}
if (!preg_match("/^[0-2][0-9]\$/", $end["hour"]) || !preg_match("/^[0-5][0-9]\$/", $end["minute"])) {
$message .= "終業時間の入力が誤っています。<br />";
}
if ($jitsumu <= 0) {
$message .= "実務時間が0分となってしまいます。入力を見なおしてください。<br />";
}
if (!preg_match("/^\\d{2,3}\$/", $rest)) {
$message .= "休憩時間の入力が誤っています。<br />";
}
if ($keitai != "S" && $keitai != "Z") {
$message .= "勤務種別の入力が誤っています(2)。<br />";
}
if (!$comment) {
$message .= 'コメントがありません。<br />';
}
if (strlen($data) == 24) {
if (isset($keitai) && isset($keitai2) && $keitai == $keitai2) {
$message .= "同じ業務種別です。(2)";
}
if (!preg_match("/^[0-2][0-9]\$/", $start2["hour"]) || !preg_match("/^[0-5][0-9]\$/", $start2["minute"])) {
$message .= "始業時間の入力が誤っています。(2)<br />";
}
if (!preg_match("/^[0-2][0-9]\$/", $end2["hour"]) || !preg_match("/^[0-5][0-9]\$/", $end2["minute"])) {
$message .= "終業時間の入力が誤っています。(2)<br />";
}
if ($jitsumu <= 0) {
$message .= "実務時間が0分となってしまいます。入力を見なおしてください。(2)<br />";
}
if (!preg_match("/^\\d{2,3}\$/", $rest2)) {
$message .= "休憩時間の入力が誤っています。(2)<br />";
}
if ($keitai2 != "S" && $keitai2 != "Z") {
$message .= "勤務種別の入力が誤っています。(2)<br />";
}
}
$unixtime = mktime(0, 0, 0, $m, $d, $y);
$nowtime = time();
$pasttime = $unixtime - $nowtime;
$allowtime = opConfig::get('op_kintai_allowdate', '3');
if ($pasttime > $allowtime) {
$message .= "勤怠の登録期限がすでに過ぎてしまっています。<br />";
}
$q = new Zend_Gdata_Spreadsheets_ListQuery();
$q->setSpreadsheetKey(opConfig::get('op_kintai_spkey', null));
$q->setWorksheetId($wid);
$query = "id={$memberId} and date={$y}/{$m}/{$d}";
$q->setSpreadsheetQuery($query);
$line = $service->getListFeed($q);
if (!$line->entries["0"]) {
$message .= '編集しようとした勤怠は存在しませんでした。<br />';
} else {
$nowtime = time();
$unixtime = mktime(0, 0, 0, $m, $d, $y);
$pasttime = $nowtime - $unixtime;
$allowtime = opConfig::get('op_kintai_allowdate', '3') * 24 * 60 * 60;
if ($pasttime > $allowtime) {
// 259200 = 3 * 24 * 60 * 60
$message .= "この勤怠はすでに編集不可となっています。<br />";
}
}
if ($message) {
$arr = array('status' => 'err', 'msg' => $message);
} else {
$ymdhis = "{$y}/{$m}/{$d}";
$rowData = array('id' => $memberId, 'date' => $ymdhis, 'data' => $data, 'comment' => $comment);
$arr = array();
$spdata = $service->updateRow($line->entries['0'], $rowData);
if ($spdata) {
$arr = array('status' => 'ok', 'msg' => '勤怠を編集しました。<br />');
} else {
$arr = array('status' => 'err2', 'msg' => '通信エラーです。（スプレッドシートサーバーと通信ができませんでした。）');
}
}
return $this->renderText(json_encode($arr));
} else {
return $this->renderText("Error: POSTリクエストで送信されなかった為、処理を中断しました。");
}
}
private function updateMasterKintai($service, $memberId, $memberMasterSpkey, $memberMasterWorkSheetId, $year, $month, $i, $detail)
{
$r = new Zend_Gdata_Spreadsheets_ListQuery();
$r->setSpreadsheetKey($memberMasterSpkey);
$r->setWorksheetId($memberMasterWorkSheetId);
$query = 'date=' . $year . '/' . $month . '/' . $i;
$r->setSpreadsheetQuery($query);
$lineList = $service->getListFeed($r);
if ($lineList) {
$update = $service->updateRow($lineList->entries['0'], $detail);
if ($update) {
echo sprintf("UPDATE SUCCESS!(SpreadSheet) memberId: %s date: %s;\n", $memberId, $detail["date"]);
} else {
echo sprintf("ERROR! NO UPDATED.(SpreadSheet) Maybe Internal Server Error Occured on Google Service. memberId: %s date: %s;", $memberId, $detail["date"]);
}
} else {
echo sprintf("ERROR! NO UPDATED.(SpreadSheet) Maybe Spreadsheet has been broken. memberId: %s date %s;", $memberId, $detail["date"]);
}
}
$service = new Zend_Gdata_Spreadsheets($client);
// define worksheet query
// get list feed for query
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadhsheet_key);
$query->setWorksheetId($people_wksht);
} catch (Exception $e) {
die('ERROR: ' . $e->getMessage());
}
?>
<table border="0" width="70%">
<tbody>
<tr><td colspan="3"><strong>Industry/Private Sector : Term Limit (yr)</strong></td></tr>
<?php
$query->setSpreadsheetQuery('bodindustryprivatesector = "Y"');
$listFeed = $service->getListFeed($query);
foreach ($listFeed->entries as $entry) {
echo '<tr>';
echo '<td width="33%">' . $entry->getCustomByName('name') . '</td>';
echo '<td width="35%">' . $entry->getCustomByName('bodtermlimit') . ' (as of ' . $entry->getCustomByName('bodtermasof') . ')</td>';
echo '<td width="33%"><a href="mailto:' . $entry->getCustomByName('email') . '">' . $entry->getCustomByName('email') . '</a></td>';
echo "</tr>\n";
}
?>
<tr><td colspan="3"> </td></tr>
<tr><td colspan="3"><strong>Academic/Research/Education Sector : Term Limit (yr)</strong></td></tr>
<?php
private function getDocument()
{
$this->getSpreadsheetsList();
$listFeed = new stdClass();
$listFeed->entries = array();
if ($this->spreadsheetAdapter instanceof Zend_Gdata_Spreadsheets) {
try {
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->docKey);
$filter = $this->getFilterString();
$query->setSpreadsheetQuery($filter);
$listFeed = $this->spreadsheetAdapter->getListFeed($query);
} catch (Exception $exc) {
$this->errors[] = 'No read premissoin or other error';
}
}
return $listFeed;
}
/**
* Retourne le résultat de la requete passé en paramètre sous la forme d'une liste de lignes
* @param String $worksheetId Id du worksheet
* @param String $spreadsheetId Id du spreadsheet
* @param String $strQuery la requête
*/
public function getResultQueryList($strQuery, $worksheetId, $spreadSheetId)
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadSheetId);
$query->setWorksheetId($worksheetId);
$query->setSpreadsheetQuery($strQuery);
return $this->service->getListFeed($query);
}
$query->setSpreadsheetKey($spreadhsheet_key);
$query->setWorksheetId($people_wksht);
} catch (Exception $e) {
die('ERROR: ' . $e->getMessage());
}
?>
<ol>
<?php
// print order
$posts = array('Chairman', 'Vice Chairman', 'Secretary', 'Treasurer', 'Past Chairman', 'Staff');
$a = array();
foreach ($posts as $p) {
$a[$p] = array();
}
$query->setSpreadsheetQuery('excompost <> ""');
$listFeed = $service->getListFeed($query);
foreach ($listFeed->entries as $entry) {
array_push($a[(string) $entry->getCustomByName('excompost')], '<a href="mailto:' . $entry->getCustomByName('email') . '">' . $entry->getCustomByName('name') . '</a>');
}
foreach ($posts as $p) {
foreach ($a[$p] as $person) {
echo '<li>';
echo $person . ', ' . $p;
echo "</li>\n";
}
}
?>
</ol>
$query->setSpreadsheetKey($spreadhsheet_key);
$query->setWorksheetId($membership_wksht);
} catch (Exception $e) {
die('ERROR: ' . $e->getMessage());
}
?>
<table style="font-size: smaller;width:600px">
<thead>
<tr bgcolor="#d3f7ff">
<th style="border: 1px dotted rgb(211, 211, 211);width:250px">Member</th>
<th style="border: 1px dotted rgb(211, 211, 211);">Organization/Representative</th>
</tr>
</thead>
<tbody>
<?php
$query->setSpreadsheetQuery('state');
$listFeed = $service->getListFeed($query);
$i = 1;
foreach ($listFeed->entries as $entry) {
if ($entry->getCustomByName('state') == $stateAbbr) {
$bg = '';
if ($i % 2 == 0) {
$bg = 'bgcolor="#d3f7ff"';
}
echo "<tr {$bg}>";
echo '<td style="border: 1px dotted rgb(211, 211, 211);"><a href="' . $entry->getCustomByName('url') . '">' . $entry->getCustomByName('name') . '</a></td>';
echo '<td style="border: 1px dotted rgb(211, 211, 211);">' . $entry->getCustomByName('representativeorganization') . '</td>';
echo "</tr>\n";
$i++;
}
}
