/**
* Returns a list feed of the specified worksheet.
*
* @param string $accessToken Google Auth Access Token
* @param string $sheetTitle title of spreadsheet
*
* @return object $result
*/
function getSpreadsheet($accessToken, $sheetTitle)
{
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$result = $spreadsheetFeed->getByTitle($sheetTitle);
return $result;
}
/**
* @return \Google\Spreadsheet\SpreadsheetFeed
*/
public static function getSpreadsheetFeed()
{
if (isset(static::$spreadsheetFeed)) {
return static::$spreadsheetFeed;
}
$token = static::getToken();
$serviceRequest = new \Google\Spreadsheet\DefaultServiceRequest($token['access_token']);
\Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new \Google\Spreadsheet\SpreadsheetService();
return static::$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
}
function __construct($ssName)
{
parent::__construct();
// Accessing the data from the Spreadsheet
$serviceRequest = new DefaultServiceRequest($this->accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$this->spreadsheet = $spreadsheetFeed->getByTitle($ssName);
// dd($this->spreadsheet);
$worksheetFeed = $this->spreadsheet->getWorksheets();
// dd($worksheetFeed);
// foreach ($worksheetFeed as $key => $value) {
// d($key, $value);
// }
// die();
$worksheet = $worksheetFeed->getByTitle('Sheet1');
// dd($worksheet);
$this->listFeed = $worksheet->getListFeed();
// dd($listFeed);
}
static function getWorksheet()
{
if (empty(self::$worksheet)) {
$config = json_decode(file_get_contents("EG Robotics-581b91515ac6.json"));
$user_to_impersonate = '*****@*****.**';
$scopes = ["https://www.googleapis.com/auth/drive", "https://spreadsheets.google.com/feeds"];
$credentials = new Google_Auth_AssertionCredentials($config->client_email, $scopes, $config->private_key, 'notasecret', 'http://oauth.net/grant_type/jwt/1.0/bearer', $user_to_impersonate);
$client = new Google_Client();
$client->setAssertionCredentials($credentials);
if ($client->getAuth()->isAccessTokenExpired()) {
$client->getAuth()->refreshTokenWithAssertion();
}
$accessToken = json_decode($client->getAuth()->getAccessToken())->access_token;
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('Employee Hour Tracking Payroll');
$workSheetFeed = $spreadsheet->getWorksheets();
self::$worksheet = $workSheetFeed->getByTitle("Office Hours");
self::$archive_worksheet = $workSheetFeed->getByTitle("15-16 OH");
}
return self::$worksheet;
}
include_once "dbconnect.php";
$table_exist = $conn->query("SELECT 1 FROM topVC");
if ($table_exist == FALSE) {
$create = "CREATE TABLE topVC (\n\t\t\t \tid INT(30) UNSIGNED AUTO_INCREMENT PRIMARY KEY, \n name_of_vc VARCHAR(30) NOT NULL,\n number_of_investment VARCHAR(30) NOT NULL,\n amount_invested VARCHAR(30) NOT NULL\n\t\t\t)";
$conn->query($create);
} else {
$conn->query("TRUNCATE TABLE topVC");
}
include_once "google_spreadsheet_access.php";
/**
* Get spreadsheet by title
*/
$spreadsheetTitle = 'startupinfo';
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetTitle);
$worksheetTitle = 'TopVc';
// it's generally named 'Sheet1'
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle($worksheetTitle);
/**
* Get row lists of worksheet
*/
$listFeed = $worksheet->getListFeed();
$sql = "";
foreach ($listFeed->getEntries() as $entries) {
$values = "( NULL, ";
foreach ($entries->getValues() as $key => $value) {
if ($key != "amountinvested") {
$values .= "'" . $value . "',";
public static function wizard($step = NULL)
{
# user is the authenticated user
$user = Auth::user();
# we will need a client for spreadsheet feeds + email + offline (to get a refreshtoken)
$client = GooglespreadsheetHelper::setGoogleClient();
switch ($step) {
case 'init':
# render wizard step #1
return View::make('connect.connect-googlespreadsheet')->with(array('step' => 'choose-type', 'isBackgroundOn' => Auth::user()->isBackgroundOn, 'dailyBackgroundURL' => Auth::user()->dailyBackgroundURL()));
break;
# /case 'init'
# /case 'init'
case 'set-type':
Log::info("session - " . Session::get('type'));
# save the widget type in SESSION
if (Input::get('type')) {
Session::put('type', Input::get('type'));
}
Log::info("session - " . Session::get('type'));
if (!$user->isGoogleSpreadsheetConnected()) {
# if the user hasn't authorized with google
# go to google oauth page
$url = $client->createAuthUrl();
return Redirect::to($url);
break;
} else {
# otherwise render the spreadsheet chooser wizard page
# get the spreadsheet list
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
# render choose-spreadsheet wizard step
return View::make('connect.connect-googlespreadsheet')->with(array('step' => 'choose-spreadsheet', 'spreadsheetFeed' => $spreadsheetFeed, 'isBackgroundOn' => Auth::user()->isBackgroundOn, 'dailyBackgroundURL' => Auth::user()->dailyBackgroundURL(), 'type' => Session::get('type')));
}
break;
# / case 'set-type'
# / case 'set-type'
case 'set-spreadsheet':
Log::info("session - " . Session::get('type'));
if (Input::get('type')) {
Session::put('type', Input::get('type'));
}
Log::info("session - " . Session::get('type'));
# save the spreadsheet ID in SESSION
Session::put("spreadsheetId", Input::get('spreadsheetId'));
# init service
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
# get the spreadsheet they asked for in the POST
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheet = $spreadsheetService->getSpreadsheetById(Input::get('spreadsheetId'));
# save the spreadsheet name in SESSION
Session::put("spreadsheetName", $spreadsheet->getTitle());
# get the worksheet list for the selected spreadsheet
$worksheetFeed = $spreadsheet->getWorksheets();
# render choose-worksheet wizard step
return View::make('connect.connect-googlespreadsheet')->with(array('step' => 'choose-worksheet', 'worksheetFeed' => $worksheetFeed, 'isBackgroundOn' => Auth::user()->isBackgroundOn, 'dailyBackgroundURL' => Auth::user()->dailyBackgroundURL(), 'type' => Session::get('type')));
break;
# /case 'set-spreadsheet'
# /case 'set-spreadsheet'
case 'set-worksheet':
Log::info("session - " . Session::get('type'));
if (Input::get('type')) {
Session::put('type', Input::get('type'));
}
Log::info("session - " . Session::get('type'));
# save the worksheet name in SESSION
Session::put("worksheetName", Input::get('worksheetName'));
# save the widget
$widget_data = array('googleSpreadsheetId' => Session::get('spreadsheetId'), 'googleWorksheetName' => Session::get('worksheetName'));
$widget_json = json_encode($widget_data);
$widget = new Widget();
$widget->widget_name = Session::get('worksheetName') . ' - ' . Session::get('spreadsheetName');
$widget->widget_type = Session::get('type');
$widget->widget_provider = 'googlespreadsheet';
$widget->widget_source = $widget_json;
$widget->widget_ready = false;
$widget->dashboard_id = $user->dashboards()->first()->id;
$widget->position = '{"size_x":3,"size_y":4,"col":1,"row":1}';
$widget->save();
return Redirect::route('dashboard.dashboard')->with('success', 'Google Spreadsheet widget added.');
break;
# / case 'set-worksheet'
# / case 'set-worksheet'
default:
// input has no step, this is the google auth return part
# we got a code in GET from google
if (Input::has('code')) {
# lets get an access token
try {
$client->authenticate(Input::get('code'));
} catch (Exception $e) {
GooglespreadsheetHelper::disconnect();
return Redirect::route('connect.connect')->with('error', 'Something went wrong, try again please.');
}
$credentials = $client->getAccessToken();
// big JSON stuff
# lets make it an associative array
$tokens_decoded = json_decode($credentials, true);
# lets check if we have a refresh token already
$refresh_token = $user->googleSpreadsheetRefreshToken;
if (strlen($refresh_token) < 10) {
# nope, let's use the one we got now
$refresh_token = $tokens_decoded['refresh_token'];
}
# database save the access-stuff-JSON and the refresh token
$user->googleSpreadsheetCredentials = $credentials;
$user->googleSpreadsheetRefreshToken = $refresh_token;
$user->save();
# good job, notify intercom
IntercomHelper::connected($user, 'googlespreadsheet');
# auth
$access_token = GooglespreadsheetHelper::getGoogleAccessToken($client, $user);
# get the spreadsheet list
$serviceRequest = new DefaultServiceRequest($access_token);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
# render choose-spreadsheet wizard step
return View::make('connect.connect-googlespreadsheet')->with(array('step' => 'choose-spreadsheet', 'spreadsheetFeed' => $spreadsheetFeed, 'isBackgroundOn' => Auth::user()->isBackgroundOn, 'dailyBackgroundURL' => Auth::user()->dailyBackgroundURL()));
} else {
return View::make('connect.connect')->with(array('error' => 'Something went wrong with the Google authentication.', 'isBackgroundOn' => Auth::user()->isBackgroundOn, 'dailyBackgroundURL' => Auth::user()->dailyBackgroundURL()));
}
break;
# / default
}
# /switch ($step)
}
public static function addHeaderToWorkSheet()
{
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('test');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('Sheet 2');
$cellFeed = $worksheet->getCellFeed();
$cellFeed->editCell(1, 1, "Row1Col1Header");
$cellFeed->editCell(1, 2, "Row1Col2Header");
$cellFeed->editCell(1, 3, "Row1Col3Header");
$cellFeed->editCell(1, 4, "Row1Col4Header");
}
function submit_reg_form()
{
//ReCaptcha
$recaptcha = new ReCaptcha(RECAPTCHA_SECRET);
$resp = $recaptcha->verify($_POST['g-recaptcha-response'], $_SERVER['REMOTE_ADDR']);
if (!$resp->isSuccess()) {
throw new Exception('Invalid captcha!');
}
//Get & Process form data
$valid_inputs = 'name|email|tel|inst|std|day1|day2';
foreach (get_presentations() as $key => $data) {
$valid_inputs .= "|{$key}";
}
$form_data = getInputsWithKey($valid_inputs);
//----Pricing
//Count days
$days = 0;
for ($i = 1; $i <= 2; $i++) {
if ($form_data["day{$i}"] != 'no') {
$days++;
}
}
$price = $days * 35;
// if ($days > 1)
// $price -= 10;
//Discount
$discount_rate = 0;
if (isset($form_data['std'])) {
switch ($form_data['std']) {
case 'aut':
$discount_rate = 0.6;
break;
case 'std':
$discount_rate = 0.25;
break;
}
}
$price *= 1.0 - $discount_rate;
$price = round($price);
$form_data['price'] = $price;
//Tel
$form_data['tel'] = intval($form_data['tel']);
//Generate tracking code
$hashids = new Hashids\Hashids(HASHID_SALT);
$tracking_code = $hashids->encode(time() . rand(0, 100));
$form_data['code'] = $tracking_code;
//Check Google Client Expired
$token = json_decode(gapi_token, true);
$client = new Google_Client();
$client->setClientId(GAPI_CLIENT_ID);
$client->setClientSecret(GAPI_CLIENT_SECRET);
$client->setAccessToken(gapi_token);
if ($client->isAccessTokenExpired()) {
//Refresh on expire
$client->refreshToken($client->getRefreshToken());
foreach (json_decode($client->getAccessToken(), true) as $k => $v) {
$token[$k] = $v;
}
file_put_contents(GAPI_TOKEN_LOCATION, "<?php define('gapi_token','" . json_encode($token) . "');");
}
//Get Registration sheet
$serviceRequest = new DefaultServiceRequest($token['access_token']);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$sheet = $spreadsheetService->getSpreadsheets()->getByTitle('linuxfest_2015')->getWorksheets()->getByTitle('List');
//Insert Submitted data
$sheet->getListFeed()->insert($form_data);
//Message
$msg = "\n<div dir='rtl'>\n\n با تشکر، ثبت نام آنلاین شما با موفقیت انجام شد و مورد بررسی قرار خواهد گرفت.\n <br>\n هزینه ی ثبت نام شما : <b>{$price}</b> هزار تومان\n <br>\n کد رهگیری شما : <b>{$tracking_code}</b>\n (این کد را برای مراحل بعدی حتما نگهدارید)<br>\n <br>\n لطفا در اسرع وقت با مراجعهی حضوری به دفتر انجمن علمی دانشکده هزینهی دورههایی را که در آنها ثبت نام کردهاید پرداخت نمایید. در صورتی که مراجعهی حضوری برایتان مقدور نیست هزینهی ثبت نام را به کارت 5022291100266525 (بانک پاسارگاد به نام آرمین باشیزاده) واریز نمایید و کد رهگیری و شماره کارت خود را به آدرس hello@linuxfest.ir ارسال کنید. به دلیل محدود بودن ظرفیت کارگاهها، اولویت با کسانی است که زودتر ثبت نام خود را نهایی کنند.\n<br>\nتهران، چهارراه ولی عصر، روبهروی خیابان بزرگمهر، دانشگاه صنعتی امیرکبیر ، دانشکده مهندسی کامپیوتر و فناوری اطلاعات ، دفتر انجمن علمی\n\t<br>\n در صورت تمایل به ایجاد هرگونه تغییر در وضعیت ثبت نام خود از ثبت نام مجدد <b> جدا خودداری فرمایید</b>. در غیر این صورت ثبت نام شما تایید نخواهد شد.<br>\n میتوانید این تغییرات را از دو طریق ایمیل جشنواره hello@linuxfest.ir یا مراجعهی حضوری اعلام فرمایید.\n\n</div>\n ";
$msg_2 = "جشنواره لینوکس امیرکبیر\n هزینه ی ثبت نام : {$price} هزار تومان\n کد رهگیری شما : {$tracking_code}\n جهت ثبت نام نهایی در اسرع وقت به دفتر انجمن علمی دانشکده مراجعه فرمایید\n ";
//Email
if (isset($form_data['email'])) {
sendmail($form_data['email'], $msg);
}
//SMS
if (isset($form_data['tel'])) {
sendsms($form_data['tel'], $msg_2);
}
return $msg;
}
private function google_connect($key, $spreadsheet, $worksheet)
{
//Get access token from DB using $key
$accessToken = $this->get_key($key);
$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle(urldecode($spreadsheet));
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle(urldecode($worksheet));
return $worksheet;
}
function googleDocs_connect_to_spreadsheet($spreadsheetName, $worksheetName)
{
global $googleDocs;
if (!$googleDocs) {
return;
}
global $accessToken;
global $listFeed;
debug("Trying accessToken '{$accessToken}'...");
$request = new Google\Spreadsheet\Request($accessToken);
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($request);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
debug('Success!', 'success');
// Find and connect to spreadsheet
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetName);
// Get all worksheets
$worksheetFeed = $spreadsheet->getWorksheets();
// Get the worksheet
$worksheet = $worksheetFeed->getByTitle($worksheetName);
// Get all the rows
$listFeed = $worksheet->getListFeed();
/* Sample code */
/*
// Get all the rows
$listFeed = $worksheet->getListFeed();
// Loop over the rows
foreach ($listFeed->getEntries() as $entry) {
$values = $entry->getValues();
echo '<p>';
print_r($values);
echo '</p>';
}
*/
// Insert row
/*
$row = array(
'portalname' => 'Test',
'recharge' => 'Test2',
'ownsince' => '1/1/2014',
'daysowned' => '',
'zone' => 'Test3',
'kmfromhome' => '999',
'favorite' => '',
'risk' => '10',
'access' => '0',
'record' => '0',
'strategy' => 'none',
'days' => 'some formula',
'days_2'=> 'a formula',
'dididiscoverit' => '',
'enemies' => '',
'allies' => '',
'address' => '',
'link' => '',
'latitude' => '',
'longitude' => '',
'computationstuff' => 'formula',
'computationstuff_2' => 'formula',
'computationstuff_3' => 'formula'
);
$listFeed->insert($row);
*/
// Display all the rows in the spreadsheet
if (isset($_REQUEST['test']) && $_REQUEST['test'] == 'read') {
// Loop over the rows
foreach ($listFeed->getEntries() as $entry) {
$values = $entry->getValues();
echo '<p>';
print_r($values);
echo '</p>';
}
}
}
