public function export($md5, $format = "html")
{
$sql = $_SESSION['export_sqls'][$md5];
if ($sql) {
$q = $this->db->query($sql);
$investment_orgs = $q->result_array();
//print_r($people);
$t = count($investment_orgs);
for ($i = 0; $i < $t; $i++) {
$investment_org_id = $investment_orgs[$i]['id'];
$time = time();
$sql = "select \n\t\t\t\t`a`.*, \n\t\t\t\tif(`a`.`end_date_ts`=0, {$time}, `a`.`end_date_ts`) as `end_date_ts2`,\n\t\t\t\t`b`.`name` as `name` from `investment_org_person` as `a` left join `people` as `b` on (`a`.`person_id`=`b`.`id`) \n\t\t\t\twhere `investment_org_id`=" . $this->db->escape($investment_org_id) . " and `name`<>'' \n\t\t\t\torder by `end_date_ts2` desc, `start_date_ts` desc, `name` asc";
$q = $this->db->query($sql);
$people = $q->result_array();
$peoplearr = array();
foreach ($people as $person) {
$peoplearr[] = $person['name'] . "," . $person['role'] . "," . date('Y', $person['start_date_ts']);
}
$investment_orgs[$i]['people'] = implode(", ", $peoplearr);
$sql = "select \n\t\t\t\t`a`.`round`,\n\t\t\t\t`a`.`currency`,\n\t\t\t\t`a`.`amount`,\n\t\t\t\t`a`.`date_ts`,\n\t\t\t\t`a`.`company_id`,\n\t\t\t\t`b`.`name` as `company_name`\n\t\t\t\tfrom\n\t\t\t\t`company_fundings` as `a` left join `companies` as `b` on (`a`.`company_id` = `b`.`id`) where `a`.`id` in (\n\t\t\t\t\tselect distinct `company_funding_id` from `company_fundings_ipc`\n\t\t\t\t\twhere \n\t\t\t\t\t`ipc_id`=" . $this->db->escape($investment_org_id) . " and \n\t\t\t\t\t`type`='investment_org'\n\t\t\t\t\t)\n\t\t\t\torder by `date_ts` desc\t\n\t\t\t\t";
$q = $this->db->query($sql);
$milestones = $q->result_array();
$milestonesarr = array();
foreach ($milestones as $milestone) {
$str = safeExport($milestone['round']) . "," . $milestone['currency'] . number_format($milestone['amount'], 2, ".", "") . "," . date('M/d/Y', $milestone['date_ts']) . "," . safeExport($milestone['company_name']);
$milestonesarr[] = $str;
}
$investment_orgs[$i]['milestones'] = implode(", ", $milestonesarr);
}
$data = array();
$data['investment_orgs'] = $investment_orgs;
$data['format'] = $format;
$this->load->view('investment_orgs/export', $data);
}
}
public function export($md5, $format = "html")
{
$sql = $_SESSION['export_sqls'][$md5];
if ($sql) {
$q = $this->db->query($sql);
$companies = $q->result_array();
$t = count($companies);
for ($i = 0; $i < $t; $i++) {
$company_id = $companies[$i]['id'];
if ($company_id) {
$data = array();
$time = time();
$sql = "select * from `screenshots` where `company_id`=" . $this->db->escape($company_id) . " order by id asc";
$q = $this->db->query($sql);
$screenshots = $q->result_array();
$sql = "select `b`.`id` as `value`, `b`.`name` as `label` from `competitors` as `a`, `companies` as `b` where \r\n\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t`a`.`company_id`=" . $this->db->escape($company_id) . " \r\n\t\t\t\t\t\t\tand `a`.`competitor_id` = `b`.`id`\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\t\tor\r\n\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t`a`.`competitor_id`=" . $this->db->escape($company_id) . " \r\n\t\t\t\t\t\t\tand `a`.`company_id` = `b`.`id`\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\t\torder by `b`.`name` asc\r\n\t\t\t\t\t";
$q = $this->db->query($sql);
$competitors = $q->result_array();
$competitorsarr = array();
foreach ($competitors as $competitor) {
$competitorsarr[] = safeExport($competitor['label']);
}
$companies[$i]['competitors'] = implode(", ", $competitorsarr);
$sql = "select \r\n\t\t\t\t\t`a`.*, \r\n\t\t\t\t\tif(`a`.`end_date_ts`=0, {$time}, `a`.`end_date_ts`) as `end_date_ts2`,\r\n\t\t\t\t\t`b`.`email_address` as `email`, `b`.`name` as `name` from `company_person` as `a` left join `people` as `b` on (`a`.`person_id`=`b`.`id`) \r\n\t\t\t\t\twhere `company_id`=" . $this->db->escape($company_id) . " and `name`<>'' \r\n\t\t\t\t\torder by `name` asc, `end_date_ts2` desc, `start_date_ts` desc";
$q = $this->db->query($sql);
$people = $q->result_array();
$peoplearr = array();
foreach ($people as $person) {
if (!$person['email']) {
$email = "<e-mail address>";
} else {
$email = $person['email'];
}
if (!$person['role']) {
$role = "<role>";
} else {
$role = $person['role'];
}
$peoplearr[] = safeExport($person['name']) . "," . $email . "," . safeExport($role) . "," . date('M/d/Y', $person['start_date_ts']);
}
$companies[$i]['people'] = implode(", ", $peoplearr);
$sql = "select * from `company_fundings` where `company_id`=" . $this->db->escape($company_id) . " order by date_ts desc";
$q = $this->db->query($sql);
$company_fundings = $q->result_array();
foreach ($company_fundings as $cfkey => $cf) {
$sql = "select * from `company_fundings_ipc` where `company_funding_id`='" . $cf['id'] . "'";
$q = $this->db->query($sql);
$company_fundings_ipc = $q->result_array();
$company_fundings[$cfkey]['companies'] = array();
$company_fundings[$cfkey]['people'] = array();
$company_fundings[$cfkey]['investment_orgs'] = array();
foreach ($company_fundings_ipc as $cfikey => $cfi) {
if ($cfi['type'] == 'company') {
$sql = "select `id`, `name` from `companies` where `id`=" . $this->db->escape($cfi['ipc_id']);
$q = $this->db->query($sql);
$result = $q->result_array();
$push = array();
if ($result[0]) {
$push['name'] = $result[0]['name'];
$push['id'] = $result[0]['id'];
} else {
$push['name'] = $cfi['name'];
$push['id'] = $cfi['ipc_id'];
}
$company_fundings[$cfkey]['companies'][] = $push;
}
if ($cfi['type'] == 'person') {
$sql = "select `id`, `name` from `people` where `id`=" . $this->db->escape($cfi['ipc_id']);
$q = $this->db->query($sql);
$result = $q->result_array();
$push = array();
if ($result[0]) {
$push['name'] = $result[0]['name'];
$push['id'] = $result[0]['id'];
} else {
$push['name'] = $cfi['name'];
$push['id'] = $cfi['ipc_id'];
}
$company_fundings[$cfkey]['people'][] = $push;
}
if ($cfi['type'] == 'investment_org') {
$sql = "select `id`, `name` from `investment_orgs` where `id`=" . $this->db->escape($cfi['ipc_id']);
$q = $this->db->query($sql);
$result = $q->result_array();
$push = array();
if ($result[0]) {
$push['name'] = $result[0]['name'];
$push['id'] = $result[0]['id'];
} else {
$push['name'] = $cfi['name'];
$push['id'] = $cfi['ipc_id'];
}
$company_fundings[$cfkey]['investment_orgs'][] = $push;
}
}
}
$company_fundingsarr = array();
foreach ($company_fundings as $company_funding) {
$str = safeExport($company_funding['round']) . "," . $company_funding['currency'] . number_format($company_funding['amount'], 2, ".", "") . "," . date('M/d/Y', $company_funding['date_ts']) . ",";
$cfnames = array();
foreach ($company_funding['companies'] as $c) {
$cfnames[] = safeExport($c['name']) . "(C)";
}
foreach ($company_funding['people'] as $c) {
$cfnames[] = safeExport($c['name']) . "(P)";
}
foreach ($company_funding['investment_orgs'] as $c) {
$cfnames[] = safeExport($c['name']) . "(I)";
}
$str .= implode("/", $cfnames);
$company_fundingsarr[] = $str;
}
$companies[$i]['company_fundings'] = implode(", ", $company_fundingsarr);
}
$sql = "select `category` from `categories` where `id` in (select `category_id` from `company_category` where `company_id`=" . $this->db->escape($company_id) . ")";
$q = $this->db->query($sql);
$co_categories = $q->result_array();
$arrtemp = array();
foreach ($co_categories as $value) {
$arrtemp[] = safeExport($value['category']);
}
$categories = implode(", ", $arrtemp);
$companies[$i]['categories'] = $categories;
$sql = "select \r\n\t\t\t\t`a`.`round`,\r\n\t\t\t\t`a`.`currency`,\r\n\t\t\t\t`a`.`amount`,\r\n\t\t\t\t`a`.`date_ts`,\r\n\t\t\t\t`a`.`company_id`,\r\n\t\t\t\t`b`.`name` as `company_name`\r\n\t\t\t\tfrom\r\n\t\t\t\t`company_fundings` as `a` left join `companies` as `b` on (`a`.`company_id` = `b`.`id`) where `a`.`id` in (\r\n\t\t\t\t\tselect distinct `company_funding_id` from `company_fundings_ipc`\r\n\t\t\t\t\twhere \r\n\t\t\t\t\t`ipc_id`=" . $this->db->escape($company_id) . " and \r\n\t\t\t\t\t`type`='company'\r\n\t\t\t\t\t)\r\n\t\t\t\torder by `date_ts` desc, `company_name` asc\r\n\t\t\t\t";
$q = $this->db->query($sql);
$milestones = $q->result_array();
$milestonesarr = array();
foreach ($milestones as $milestone) {
$str = safeExport($milestone['round']) . "," . $milestone['currency'] . number_format($milestone['amount'], 2, ".", "") . "," . date('M/d/Y', $milestone['date_ts']) . "," . safeExport($milestone['company_name']);
$milestonesarr[] = $str;
}
$companies[$i]['milestones'] = implode(", ", $milestonesarr);
//echo "<pre>";
//print_r($milestones);
}
$data = array();
$data['companies'] = $companies;
$data['format'] = $format;
$this->load->view('companies/export', $data);
}
}
