public function addSupplierAction()
{
//view information NPD
$this->view->id = $idNpdSheet = $this->getRequest()->getParam('id');
$this->view->contentTitle = 'Add Supplier';
$this->generateUrl('npd', 'create');
$this->view->setTpl("Add-supplier");
$this->view->npd = $npdSheet = NpdSheetQuery::create()->findByPKOrThrow($idNpdSheet, $this->i18n->_("It does not exist the NpdSheet with id {$idNpdSheet}"));
$family = FamilyQuery::create()->findByPK($npdSheet->getIdFamily());
$this->view->family = $family->getName();
if ($npdSheet->getIdSubfamily()) {
$this->view->subfamily = SubfamilyQuery::create()->findByPK($npdSheet->getIdSubfamily())->getName();
} else {
$this->view->subfamily = $this->i18n->_("Any");
}
$this->view->productGroup = ProductGroupQuery::create()->findByPK($family->getIdProductGroup())->getName();
$this->view->videos = FileQuery::create()->innerJoinNpdSheet()->whereAdd('File2NpdSheet.id_npd_sheet', $npdSheet->getIdNpdSheet())->whereAdd(File::TYPE, File::$typeFile["typeLink"])->find();
$this->view->npdColors = ColorQuery::create()->innerJoinNpdSheet()->whereAdd('Color2NpdSheet.id_npd_sheet', $npdSheet->getIdNpdSheet())->find();
$this->view->country = CountryQuery::create()->find()->toCombo();
$this->view->npdSheetImages = NpdSheetQuery::getNpdSheetFileObjects($npdSheet->getIdNpdSheet(), File::$typeFile["typeImage"]);
$this->view->npdSheetFiles = NpdSheetQuery::create()->addColumns(array("File.*"))->innerJoinFile()->whereAdd("NpdSheet." . NpdSheet::ID_NPD_SHEET, $npdSheet->getIdNpdSheet())->fetchAll();
$this->view->file = $file = FileQuery::create()->innerJoinNpdSheet()->whereAdd('File2NpdSheet.id_npd_sheet', $npdSheet->getIdNpdSheet())->whereAdd(File::TYPE, File::$typeFile["typeFile"])->findOne();
//Form add supplier
$npdSuppliers = $npdSuppliers = NpdSheetSupplierQuery::create()->whereAdd(NpdSheetSupplier::ID_NPD_SHEET, $idNpdSheet)->find();
$allColors = array("" => "** Select a Color **") + ColorQuery::create()->actives()->find()->toCombo();
//Videos from supplier
foreach ($npdSuppliers as $npdSupplier) {
//Get File Video for NPDSheetSupplier
$NPDSheetSupplierFilesQuery = NpdSheetSupplierQuery::create()->addColumns(array("File.*"))->innerJoinFile()->whereAdd("NpdSheetSupplier." . NpdSheetSupplier::ID_NPD_SUPPLIER, $npdSupplier->getIdNpdSupplier())->fetchAll();
$NPDSheetSupplierFiles[$npdSupplier->getIdNpdSupplier()] = $NPDSheetSupplierFilesQuery;
$assignedColors = $this->getSupplierColors($npdSupplier->getIdNpdSupplier());
$i = 0;
foreach ($assignedColors->toCombo() as $idColor => $colorName) {
$colors[$npdSupplier->getIdNpdSupplier()][$i]["options"] = $allColors + array($idColor => $colorName);
$colors[$npdSupplier->getIdNpdSupplier()][$i]["selected"] = $idColor;
$i++;
}
if (count($colors[$npdSupplier->getIdNpdSupplier()]) == 0) {
$colors[$npdSupplier->getIdNpdSupplier()][0]["options"] = $allColors;
}
//Moq and price
$npdMoqPriceQuery = SupplierBudgetQuery::create();
$npdMoqPriceQuery->whereAdd(SupplierBudget::ID_NPD_SUPPLIER, $npdSupplier->getIdNpdSupplier());
$npdMoqPrice[$npdSupplier->getIdNpdSupplier()] = $npdMoqPriceQuery->find();
}
if ($npdSuppliers->count() > 0) {
$this->view->npdSuppliers = $npdSuppliers;
$this->view->NPDSheetSupplierFiles = $NPDSheetSupplierFiles;
//die(print_r($colors));
$this->view->fileTypes = File::$typeFile;
$this->view->colors = $colors;
$this->view->npdMoqPrice = $npdMoqPrice;
} else {
$npdSuppliers = new stdClass();
$npdSuppliers->couryard = new NpdSheetSupplier();
$this->view->npdSuppliers = $npdSuppliers;
$this->view->defaultColor = $allColors;
}
$suppliers = Application\Query\SapSupplierQuery::create()->find();
$temporalSupplierQuery = TemporalSupplierQuery::create()->find();
$temporalSupplierArray = array();
$temporalSupplierNamesArray = array();
while ($temporalSupplier = $temporalSupplierQuery->read()) {
$temporalSupplierNamesArray[$temporalSupplier->getIdTemporalSap()] = $temporalSupplier->getIdTemporalSap() . ' - ' . $temporalSupplier->getName();
$temporalSupplierArray[$temporalSupplier->getIdTemporalSap()] = $temporalSupplier->getName();
}
// $suppliersToAjax += $temporalSupplierArray;
$this->view->supplierNames = $suppliers->toAutoCompleteCombo() + $temporalSupplierNamesArray;
$this->view->supplierTabs = $suppliers->toCombo() + $temporalSupplierArray;
$this->view->fobPorts = array("" => "Select Fob Port") + Application\Query\PortCustomQuery::create()->actives()->whereAdd('PortCustom.' . PortCustom::IS_DEPARTURE, 1)->find()->toCombo();
$this->view->incoterms = array("" => "Select Incoterm") + \Application\Query\IncotermQuery::create()->find()->toCombo();
$this->view->currencies = array("" => "Select Currency") + \Application\Query\CurrencyQuery::create()->find()->toCombo();
$this->view->actionForm = 'create';
}
public function toExcelAction()
{
$id = $this->getRequest()->getParam("id");
$focusGroup = FocusGroupQuery::create()->findByPKOrThrow($id, $this->i18n->_("The FocusGroup with id {$id} wasn't found"));
$poll = PollQuery::create()->findByPKOrThrow($focusGroup->getIdPoll(), $this->i18n->_("The Poll with id {$focusGroup->getIdPoll()} wasn't found"));
$participants = RespondentQuery::create()->innerJoinFocusGroup()->whereAdd('FocusGroup.' . FocusGroup::ID_FOCUS_GROUPS, $focusGroup->getIdFocusGroups())->find();
$npds = NpdSheetQuery::create()->innerJoinFocusGroup()->whereAdd('FocusGroup.' . FocusGroup::ID_FOCUS_GROUPS, $focusGroup->getIdFocusGroups(), BaseQuery::EQUAL)->find()->toObjectArray();
$images = array();
$suppliers = array();
foreach ($npds as $npd) {
$npdSupplier = NpdSheetSupplierQuery::create()->whereAdd(NpdSheetSupplier::ID_NPD_SHEET, $npd->getIdNpdSheet())->findOne();
if ($npdSupplier) {
$supplier = SapSupplierQuery::create()->findByPK($npdSupplier->getIdSupplier());
}
$query = FileQuery::create()->addColumns(array("File.*"))->innerJoinNpdSheetSupplier()->whereAdd("File." . File::TYPE, $type, FileQuery::EQUAL)->whereAdd("NpdSheetSupplier." . NpdSheetSupplier::ID_NPD_SHEET, $npd->getIdNpdSheet());
if ($supplier) {
$query->whereAdd("NpdSheetSupplier." . NpdSheetSupplier::ID_SUPPLIER, $supplier->getIdSupplier());
$suppliers[$npd->getIdNpdSheet()] = $supplier;
}
$images[$npd->getIdNpdSheet()] = $query->findOne();
if (!$images[$npd->getIdNpdSheet()]) {
if (null != ($npdImages = NpdSheetQuery::getNpdSheetFileObjects($npd->getIdNpdSheet(), File::$typeFile["typeImage"]))) {
$images[$npd->getIdNpdSheet()] = $npdImages->getOne();
}
}
}
$questions = QuestionQuery::create()->innerJoinPoll()->whereAdd("PollQuestions.id_poll", $poll->getIdPoll(), BaseQuery::EQUAL)->orderBy("PollQuestions.order")->find();
$colors = ColorQuery::create()->find()->toCombo();
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
$activeSheet->mergeCells('A1:A2')->setCellValue('A1', $this->i18n->_('NPD ID'))->mergeCells('B1:B2')->setCellValue('B1', $this->i18n->_('Supplier'))->mergeCells('C1:C2')->setCellValue('C1', $this->i18n->_('Image'))->mergeCells('D1:D2')->setCellValue('D1', $this->i18n->_('Description'))->mergeCells('E1:E2')->setCellValue('E1', $this->i18n->_('MX Price'));
$i = 5;
foreach ($participants as $participant) {
$last = $i + $questions->count() - 1;
$activeSheet->mergeCells($this->getExcelColumn($i) . '1:' . $this->getExcelColumn($last) . '1');
$activeSheet->setCellValue($this->getExcelColumn($i) . '1', $participant->getName());
foreach ($questions as $question) {
$activeSheet->setCellValue($this->getExcelColumn($i) . '2', $question->getDescription());
$i++;
}
}
$i = 3;
foreach ($npds as $npd) {
$activeSheet->setCellValue("A{$i}", $npd->getIdNpdSheet());
if ($suppliers[$npd->getIdNpdSheet()]) {
$activeSheet->setCellValue("B{$i}", $suppliers[$npd->getIdNpdSheet()]->getName());
}
if ($images[$npd->getIdNpdSheet()]) {
$link = new PHPExcel_Cell_Hyperlink();
$link->setUrl($this->getFullBaseUrl() . '/' . $images[$npd->getIdNpdSheet()]->getContent());
$activeSheet->setCellValue("C{$i}", $this->getFullBaseUrl() . '/' . $images[$npd->getIdNpdSheet()]->getContent())->setHyperlink("C{$i}", $link);
}
$activeSheet->setCellValue("D{$i}", $npd->getDescription());
$activeSheet->setCellValue("E{$i}", $npd->getPrice());
$j = 5;
foreach ($participants as $participant) {
$questions->rewind();
while ($question = $questions->read()) {
if ($pollResponse = PollResponseQuery::create()->whereAdd(PollResponse::ID_FOCUS_GROUP, $focusGroup->getIdFocusGroups())->whereAdd(PollResponse::ID_POLL, $focusGroup->getIdPoll())->whereAdd(PollResponse::ID_NPD_SHEET, $npd->getIdNpdSheet())->whereAdd(PollResponse::ID_RESPONDENT, $participant->getIdRespondent())->whereAdd(PollResponse::ID_QUESTION, $question->getIdQuestion())->findOne()) {
$q = new Question();
if ($question->getIdType() == Question::$Type['Yes/No']) {
switch ($pollResponse->getResponse()) {
case "true":
$pollResponse->setResponse('Si');
break;
case "false":
$pollResponse->setResponse('No');
break;
}
}
if ($question->getIdType() == Question::$Type['Multiple']) {
$multipleResponseQuery = QuestionMultipleResponseQuery::create()->whereAdd(QuestionMultipleResponse::ID_QUESTION, $question->getIdQuestion())->find();
while ($multipleResponse = $multipleResponseQuery->read()) {
if ($pollResponse->getResponse() == $multipleResponse->getIdResponse()) {
$pollResponse->setResponse($multipleResponse->getDescription());
}
}
}
$activeSheet->setCellValue($this->getExcelColumn($j) . $i, $pollResponse->getResponse());
}
$j++;
}
}
$i++;
}
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"{$focusGroup->getName()}.xlsx\"");
header("Cache-Control: max-age=0");
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("php://output");
exit;
}
