/**
* 根据person.id查找
*/
public function findById($id)
{
$sql = "select person.id id, person.name name, person.state state, person.description description " . ",now.longitude now_lng, now.latitude now_lat, now.zoom now_zoom " . ", want.longitude want_lng, want.latitude want_lat, want.zoom want_zoom " . "from person left join point as now on person.now = now.id " . "left join point as want on person.want = want.id " . "where person.id = {$id}";
$mysql = new MysqlAccess();
$result = $mysql->getLine($sql);
return $result;
}
/**
* 根据楼盘id返回项目
*/
public function findPremisesById($id)
{
$sql = "select premises.id pid, premises.name name, point.longitude lng, " . " point.latitude lat, premises.description description, " . " project.name proname from premises" . " left join point on premises.point_id = point.id " . " left join project on project.id = premises.project_id where premises.id = {$id}";
$mysql = new MysqlAccess();
$result = $mysql->getLine($sql);
return $result;
}
/**
* 根据用户名查找
*/
public function findByUsername($uname)
{
$mysql = new MysqlAccess();
$sql = "select `username`, `password`, `last_login`, `last_ip` from admin where `username` = '{$uname}'";
$result = $mysql->getLine($sql);
return $result;
}
public function testGetStatement3()
{
$columns1 = array(0 => 'attendent.id', 1 => 'attendent.img', 2 => 'attendent.name', 3 => 'award.name', 4 => 'award.annotation', 5 => 'activity_mng.attend_date');
$joinTables1 = array(0 => 'activity_mng', 1 => 'award');
$this->object->select($columns1, 'attendent')->innerJoin($joinTables1)->joinOn("`award`.`id` = `activity_mng`.`award_id`")->andJoinOn("`attendent`.`id` = `activity_mng`.`attendent_id`")->where("`activity_id` = 5")->andWhere("`award_id` > 0");
$result = $this->object->getDbComponent()->getStatement();
$compare = "SELECT attendent.id, attendent.img, attendent.name, award.name, award.annotation, activity_mng.attend_date FROM `attendent` INNER JOIN `activity_mng` INNER JOIN `award` ON `award`.`id` = `activity_mng`.`award_id` AND `attendent`.`id` = `activity_mng`.`attendent_id` WHERE (`activity_id` = 5) AND (`award_id` > 0)";
$this->assertEquals($compare, $result);
}
public function testExecute()
{
$this->object->select("*", "activity")->where("id > 2")->andWhere("id < 10");
$mysqlResult = $this->object->execute();
$result = mysql_fetch_assoc($mysqlResult);
$compare = array('id' => 3, 'title' => 'act2');
$this->assertEquals($compare['id'], $result['id']);
$this->assertEquals($compare['title'], $result['title']);
}
public function testGetStatement3()
{
$columns1 = array(0 => 'attendent.id', 1 => 'attendent.img', 2 => 'attendent.name', 3 => 'award.name', 4 => 'award.annotation', 5 => 'activity_mng.attend_date');
$joinTables1 = array(0 => 'activity_mng', 1 => 'award');
$this->object->select($columns1, 'attendent')->innerJoinOn('activity_mng', 'attendent.id = activity_mng.attendent_id')->innerJoinOn('award', 'award.id = activity_mng.award_id')->where("`activity_id` = 5")->andWhere("`award_id` > 0");
$result = $this->object->getStatement();
$compare = "SELECT attendent.id, attendent.img, attendent.name, award.name, award.annotation, activity_mng.attend_date FROM attendent INNER JOIN activity_mng ON attendent.id = activity_mng.attendent_id INNER JOIN award ON award.id = activity_mng.award_id WHERE (`activity_id` = 5) AND (`award_id` > 0)";
$this->assertEquals($compare, $result);
}
public function testLimitExecute()
{
$this->object->select("*", "activity")->limit(1, 1)->orderBy('id');
$statement = $this->object->getStatement();
$compareStatement = "SELECT * FROM (SELECT *, ROW_NUMBER() OVER ( ORDER BY id) as row FROM [activity]) a WHERE (row > 1) and (row <= 2)";
$this->assertEquals($compareStatement, $statement);
$result = $this->object->execute();
$compare = array('id' => 3, 'title' => 'act2');
$this->assertEquals($compare['id'], $result[0]['id']);
$this->assertEquals($compare['title'], $result[0]['title']);
}
public function testCount()
{
$pdoResult = $this->object->getDbComponent()->execute("SELECT COUNT(*) FROM activity");
$result = NULL;
$count = 0;
foreach ($pdoResult as $row) {
if ($count == 0) {
$result = $row;
}
}
$compare = 3;
$this->assertEquals($compare, $result["COUNT(*)"]);
}
public static function mail($to, $subject, $body)
{
$headers = "from: Tiferes Rachamim <*****@*****.**>";
$headers .= "\r\n";
$headers .= "Bcc: info@tiferesrachamim.com";
if (!filter_var($to, FILTER_VALIDATE_EMAIL)) {
throw new Exception($to . ' is not a vlid email address');
}
$success = mail($to, $subject, $body, $headers);
$desc = $success ? 'Sucessfuly ' : 'Unsuccessfuly ';
$desc .= 'sent email to ' . $to . ': ' . $body;
MysqlAccess::log($desc);
return $success;
}
public function testGetAll()
{
$pdoResult = $this->object->getAll("activity", NULL, NULL, TRUE);
$compare = '[{"id":"1","title":"fans","startdate":"2012-03-22","enddate":"2012-05-31","createdate":"2012-03-15","isonline":"0","isdelete":"0","host":"2","category":"bb","description":"bbbbb","cautions":"bbbb","location":"bbbb","initial_attend":"20","allow_attend":"60","final_attend":"40","main_img":null,"other_img":null,"fansonly":"0","url_1":"bbb","url_2":"bbb","awd_comment":"memo"},{"id":"3","title":"act2","startdate":"2012-04-06","enddate":"2012-05-25","createdate":"2012-04-01","isonline":"1","isdelete":"0","host":"3","category":"ahhh","description":"aaaa","cautions":null,"location":"dfdfd","initial_attend":"120","allow_attend":"500","final_attend":"312","main_img":null,"other_img":null,"fansonly":"1","url_1":"ccc","url_2":"ccc","awd_comment":"no"},{"id":"4","title":"lottery","startdate":"2012-04-19","enddate":"2012-05-28","createdate":"2012-05-14","isonline":"1","isdelete":"0","host":"1","category":"cc","description":"cccc","cautions":null,"location":"sdfafas","initial_attend":"30","allow_attend":"100","final_attend":"80","main_img":null,"other_img":null,"fansonly":"1","url_1":"cddd","url_2":"dddd","awd_comment":"big gift"}]';
$this->assertEquals($compare, $pdoResult);
}
/**
* 根据id删除
*
*/
public function delete($id)
{
$mysql = new MysqlAccess();
$sql = "delete from point where id = {$id}";
$mysql->runSql($sql);
}
public static function log($description)
{
MysqlAccess::getInstance()->conn->query("insert into log (description, log_time) values('{$description}', now());");
}
private function emailReceipt($info)
{
$padLength = 35;
$body = '';
if ($info['success']) {
$body .= $info['message'] . "\n\n";
$body .= str_pad("Confirmation Number: ", $padLength) . $info['confirmationNumber'] . "\n";
$body .= str_pad("Name: ", $padLength) . ucwords(ucwords($info['fname'] . ' ' . $info['lname'])) . "\n";
$body .= str_pad("Amount: ", $padLength) . $info['resultAmount'] . "\n";
$body .= str_pad("Date: ", $padLength) . $info['clientTransDate'] . "\n";
$body .= str_pad("Authorization Code: ", $padLength) . $info['authCode'] . "\n";
$body .= str_pad("Payment Type: ", $padLength) . ucwords(ucwords($info['paymentType'])) . "\n";
$body .= str_pad("Card Type: ", $padLength) . ucwords(ucwords($info['cardType'])) . "\n";
$body .= str_pad("Last 4 of Account Number: ", $padLength) . $info['last4'] . "\n";
}
if (isset($info['subscription'])) {
$body .= "\n\nYou have agreed to recurring charges on a monthly basis. You may call Laibie at (347) 403-1660 to cancel the ";
$body .= "recurring charges at any time. Please review the information below and contact Laibie immediately if ";
$body .= "the information is incorrect.\n\n";
$body .= str_pad("Recurring Subscription ID: ", $padLength) . $info['braintreeSubscriptionId'] . "\n";
$body .= str_pad("Recurring Amount: ", $padLength) . $info['price'] . "\n";
$body .= str_pad("Recurring Day of Month: ", $padLength) . $info['billingDayOfMonth'] . "\n";
$body .= str_pad("Recurring Subscription Status: ", $padLength) . ucwords(ucwords($info['status'])) . "\n";
}
if (strlen($body) <= 0) {
MysqlAccess::log("Attempted to email empty text to " . $info['email']);
return;
}
MailerHelper::mail($info['email'], $info['header'], $body);
}
