/**
* 查询backlog信息
*/
public function selectSprintBacklog($pvid)
{
$conn = parent::getConn();
$sql = "SELECT `id`, `pv_id`, `story_name`, `dev_need_hour`, `test_need_hour`, `priority`, `dev`, `qa`, `find_bugs`, `start_time`, `cc_time`, `tc_time`, `remark`, `create_time`FROM `sprint_backlog` WHERE pv_id = :pvid";
try {
$st = $conn->prepare($sql);
$st->bindValue(":pvid", $pvid, PDO::PARAM_STR);
$st->execute();
$row = $st->fetchALL();
} catch (PDOException $e) {
echo "selectSprintBacklog() failed:" . $e->getMessage();
return false;
}
return $row;
}
/**
* 查询上周新增和未关闭BUG
* LW lastweek
*/
public function execSql($ip, $port, $user, $pass, $sql)
{
$dsnStr = "mysql:host=" . $ip . ":" . $port . ";dbname=mysql";
$conn = parent::getConn($dsnStr, $user, $pass);
try {
$st = @$conn->prepare($sql);
$st->execute();
$row = $st->fetchALL();
$conn = Null;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row;
}
/**
* 根据panel的id来获取panel位置
*/
public function selectPermissionDB($id)
{
$conn = parent::getConn();
$sql = "SELECT user_email,rule FROM permission WHERE user_email=:email";
try {
$st = $conn->prepare($sql);
$st->bindValue(":email", $id, PDO::PARAM_STR);
$st->execute();
$row = $st->fetchAll();
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row;
}
public function selectByDay($day)
{
$conn = parent::getConn();
$sql = "select id,name,path,ower,uploadtime,size,msg from picture WHERE DATE_SUB(CURDATE(), INTERVAL {$day} DAY)=DATE(uploadtime) ";
try {
$st = $conn->prepare($sql);
$st->execute();
$row = $st->fetchAll();
$conn = Null;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row;
}
public function selectDashBoardChartId($id)
{
$conn = parent::getConn();
$sql = "select id from chart where dashboard_id=:id ";
try {
$st = $conn->prepare($sql);
$st->bindValue(":id", $id, PDO::PARAM_INT);
$st->execute();
$row = $st->fetchAll();
return $row;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
}
public function hasUser($email)
{
$conn = parent::getConn();
$sql = "select email,username from showdb.user WHERE email=:email ";
try {
$st = $conn->prepare($sql);
$st->bindValue(":email", $email, PDO::PARAM_STR);
$st->execute();
$row = $st->fetch();
$conn = Null;
return $row;
} catch (PDOException $e) {
echo " error:" . $e->getMessage();
return false;
}
}
/**
* 插入slider页面click事件
*/
public function insertLog($data)
{
$event = $data['event'];
$page = $data['page'];
$description = $data['description'];
$username = $data['username'];
$ip = $data['ip'];
$conn = parent::getConn();
$sql = "INSERT INTO sys_log (`event`,`page`,`description`,`username`,`ip`)" . "VALUES('{$event}','{$page}','{$description}','{$username}','{$ip}');";
try {
$st = $conn->prepare($sql);
$st->execute();
$conn = Null;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return true;
}
public function updateVersionInfo($data)
{
$conn = parent::getConn();
$sql = "UPDATE product_version_info Set version_name=:version_name,start_time=:start_time,cc_time=:cc_time, tc_time=:tc_time,oc_time=:oc_time, remark=:remark where id=:id ";
try {
$st = $conn->prepare($sql);
$st->bindValue(":version_name", $data["version_name"], PDO::PARAM_STR);
$st->bindValue(":start_time", $data["start_time"], PDO::PARAM_STR);
$st->bindValue(":cc_time", $data["cc_time"], PDO::PARAM_STR);
$st->bindValue(":tc_time", $data["tc_time"], PDO::PARAM_STR);
$st->bindValue(":oc_time", $data["oc_time"], PDO::PARAM_STR);
$st->bindValue(":id", $data["id"], PDO::PARAM_INT);
$st->bindValue(":remark", $data["remark"], PDO::PARAM_STR);
echo "<pre>";
print_r($data);
$st->execute();
return true;
} catch (PDOException $e) {
// echo "failure:".$e->getMessage();
return false;
}
}
public function selectAppQrsById($id)
{
$conn = parent::getConn();
$sql = "select app_name,app_path,create_time from qr where id=" . $id;
try {
$st = $conn->prepare($sql);
$st->execute();
$row = $st->fetch();
return $row;
} catch (PDOException $e) {
echo "selectAppDirs():" . $e->getMessage();
return false;
}
}
public function selectWeekTotalBug()
{
$conn = parent::getConn(DB_DSN_REDMINE);
$sql = "CALL selectWeekTotalBug();";
//echo $sql;
try {
$st = $conn->prepare($sql);
$st->execute();
$row = $st->fetchALL();
$conn = Null;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row[0][0];
}
/**
* 各版本BUG清除率
*/
public function selectDefectRemovalRate()
{
$conn = parent::getConn(DB_DSN_MOBO_JIRA, DB_MOBO_JIRA_USERNAME, DB_MOBO_JIRA_PASSWORD);
$sql = "SELECT ";
$sql .= "pv.vname AS `version`, ";
$sql .= "SUM(CASE ji.issuestatus WHEN 6 THEN 1 WHEN 5 THEN 0.9 END)/COUNT(ji.id)*100 AS `rate` ";
$sql .= "FROM ";
$sql .= "jiraissue ji ";
$sql .= "JOIN ";
$sql .= "nodeassociation nn ";
$sql .= "ON ji.id = nn.SOURCE_NODE_ID ";
$sql .= "JOIN ";
$sql .= "projectversion pv ";
$sql .= "ON pv.id = nn.SINK_NODE_ID ";
$sql .= "WHERE ji.project IN (10100) ";
$sql .= "AND pv.PROJECT IN (10100) ";
$sql .= "AND nn.SINK_NODE_ENTITY = 'Version' ";
$sql .= "AND nn.ASSOCIATION_TYPE = 'IssueVersion' ";
$sql .= "AND ji.issuetype = 1 ";
$sql .= "GROUP BY `version` ";
$sql .= "ORDER BY `version` ";
try {
$st = $conn->prepare($sql);
$st->execute();
$row = $st->fetchAll();
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row;
}
/**
* 更新story的tc 时间，及qa
* @param int $storyid
* @param string $qa
* @param string $tcdate
* @return boolean
*/
public function updateStoryQaDate($storyid, $qa, $tcdate)
{
$conn = parent::getConn();
$sql = "UPDATE `sprint_backlog` SET `qa` = :qa, `tc_time` = :tcdate WHERE `id` = :storyid";
try {
$st = $conn->prepare($sql);
$st->bindValue(":storyid", $storyid, PDO::PARAM_STR);
$st->bindValue(":qa", $qa, PDO::PARAM_STR);
$st->bindValue(":tcdate", $tcdate, PDO::PARAM_STR);
$st->execute();
return true;
} catch (PDOException $e) {
echo "selectPaymentMethods() failed:" . $e->getMessage();
return false;
}
}
/**
* 更新praise数据
*/
public function updatePraise($username, $panel_id, $praise)
{
$conn = parent::getConn();
$sql = "UPDATE `praise` SET `ispraise` = :praise WHERE `username` = :username AND `panel_id`= :panel_id ORDER BY id DESC LIMIT 1;";
try {
$st = $conn->prepare($sql);
$st->bindValue(":username", $username, PDO::PARAM_STR);
$st->bindValue(":panel_id", $panel_id, PDO::PARAM_INT);
$st->bindValue(":praise", $praise, PDO::PARAM_INT);
$st->execute();
return true;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
}
/**
* 查询7日pv量
*/
public function select7DayPv()
{
$conn = parent::getConn(DB_DSN_SHOW);
$sql = "SELECT ur.username AS uname,ur.department AS ubumen,COUNT(sl.id) AS num FROM sys_log sl JOIN `user` ur ON sl.username = SUBSTRING_INDEX(ur.email,'@',1) WHERE sl.`event`='click' AND sl.username NOT IN ('wangzhipeng','zhangchengtao','liuhao_17173','yuecui','liutiesong') AND sl.createtime >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 DAY),'%Y/%m/%d') GROUP BY uname,ubumen ORDER BY num DESC LIMIT 10;";
try {
$st = $conn->prepare($sql);
$st->execute();
$row = $st->fetchALL();
$conn = Null;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
return $row;
}
/**
* 根据dashboard_id获取所有chart_id,help_msg,analysis_msg
*/
public function selectChartInfo($dashboard_id)
{
$conn = parent::getConn();
$sql = "SELECT id AS chart_id,id AS pannel_id,help_msg AS help_msg,analysis_msg AS analysis_msg FROM chart WHERE dashboard_id =:id and id not in (SELECT id FROM panel WHERE ishide = 1);";
try {
$st = $conn->prepare($sql);
$st->bindValue(":id", $dashboard_id, PDO::PARAM_INT);
$st->execute();
$row = $st->fetchALL();
return $row;
} catch (PDOException $e) {
echo "failure:" . $e->getMessage();
return false;
}
}
/**
* 检查渠道名称存在
*/
public function existsChannelName($id, $channel_name)
{
$conn = parent::getConn();
$sql = "SELECT COUNT(*) as `num` FROM `channel_number` WHERE `channel_name` = :channel_name AND `id` != :id;";
try {
$st = $conn->prepare($sql);
$st->bindValue(":id", $id, PDO::PARAM_INT);
$st->bindValue(":channel_name", $channel_name, PDO::PARAM_STR);
$st->execute();
$row = $st->fetch();
return $row;
} catch (PDOException $e) {
echo "existsChannelName():" . $e->getMessage();
return false;
}
}
