/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$startdate = $this->argument('start_date');
$enddate = $this->argument('end_date');
set_time_limit(0);
ini_set('memory_limit', -1);
$timeFirst = strtotime(date('Y-m-d H:i:s'));
$filePath = storage_path() . '/report/postedmkl/Posted MKL ' . $startdate . ' - ' . $enddate . '.csv';
$dates = Dates::getDatesFromRange($startdate, $enddate);
$writer = WriterFactory::create(Type::CSV);
// $writer->setShouldCreateNewSheetsAutomatically(true); // default value
$writer->openToFile($filePath);
$writer->addRow(array('AREA', 'REGION', 'DISTRIBUTOR', 'DISTRIBUTOR CODE', 'STORE ID', 'STORE CODE', 'STORE NAME', 'OTHER CODE', 'SKU CODE', 'DIVISION', 'BRAND', 'CATEGORY', 'SUB CATEGORY', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'SAPC', 'WHPC', 'WHCS', 'SO', 'FSO', 'FSO VAL', 'OSA', 'OSS', 'TRANSACTION DATE', 'POSTING DATE AND TIME', 'SIGNATURE LINK'));
foreach ($dates as $date) {
$rows = NULL;
$rows = ItemInventories::getByDate($date);
$plunck_data = [];
foreach ($rows as $row) {
if (!is_null($row->signature)) {
$link = url('api/pcountimage', [$row->signature]);
} else {
$link = '';
}
$row_data[0] = $row->area;
$row_data[1] = $row->region_name;
$row_data[2] = $row->distributor;
$row_data[3] = $row->distributor_code;
$row_data[4] = $row->store_id;
$row_data[5] = $row->store_code;
$row_data[6] = $row->store_name;
$row_data[7] = $row->other_barcode;
$row_data[8] = $row->sku_code;
$row_data[9] = $row->division;
$row_data[10] = $row->brand;
$row_data[11] = $row->category;
$row_data[12] = $row->sub_category;
$row_data[13] = $row->description;
$row_data[14] = $row->ig;
$row_data[15] = $row->fso_multiplier;
$row_data[16] = $row->sapc;
$row_data[17] = $row->whpc;
$row_data[18] = $row->whcs;
$row_data[19] = $row->so;
$row_data[20] = $row->fso;
$row_data[21] = (double) $row->fso_val;
$row_data[22] = $row->osa;
$row_data[23] = $row->oos;
$row_data[24] = $row->transaction_date;
$row_data[25] = $row->created_at;
$row_data[26] = $link;
$plunck_data[] = $row_data;
}
$writer->addRows($plunck_data);
// add multiple rows at a time
}
$writer->close();
$timeSecond = strtotime(date('Y-m-d H:i:s'));
$differenceInSeconds = $timeSecond - $timeFirst;
echo 'Time used ' . $differenceInSeconds . " sec";
}
public function actionDownloadTemplate()
{
$model = new $_GET['m']();
$header = [];
$sample = [];
$pk = $model->tableSchema->primaryKey;
$cols = $model->tableSchema->columns;
foreach ($cols as $c) {
$header[] = $c->name;
if ($c->name == $pk) {
$sample[] = '';
continue;
}
switch ($c->dbType) {
case "date":
$sample[] = '2016-10-23';
break;
case "datetime":
$sample[] = '2016-10-23 14:54:43';
break;
case "time":
$sample[] = '14:54:32';
break;
default:
$sample[] = 'text';
default:
if ($c->type == "integer") {
$sample[] = 123;
} else {
$sample[] = 'text';
}
}
}
if (isset($GLOBALS)) {
$writer = WriterFactory::create(Type::XLSX);
}
$writer->openToBrowser("import-" . Helper::camelToSnake($_GET['m']) . '.xlsx');
// stream data directly to the browser
$writer->addRows([$header, $sample]);
// add multiple rows at a time
$writer->close();
}
public function index(Request $request)
{
$user = $request->id;
$type = $request->type;
$user = User::with('stores', 'stores.skus')->find($user);
$storelist = $user->stores()->orderBy('store')->get();
// get store list
if ($type == 1) {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('stores.txt');
foreach ($storelist as $store) {
$data[0] = $store->id;
$data[1] = $store->store_code;
$data[2] = $store->store;
$writer->addRow($data);
}
$writer->close();
}
//get store sku list
if ($type == 2) {
$ids = array();
foreach ($storelist as $store) {
$ids[] = $store->id;
}
$skus = StoreSku::select('store_id', 'sku_code', 'sku_desc', 'ig', 'conversion', 'categories.category_long', 'sub_categories.subcategory', 'brands.brand', 'divisions.division')->join('skus', 'skus.id', '=', 'store_sku.sku_id')->join('categories', 'categories.id', '=', 'skus.category_id')->join('sub_categories', 'sub_categories.id', '=', 'skus.sub_category_id')->join('brands', 'brands.id', '=', 'skus.brand_id')->join('divisions', 'divisions.id', '=', 'skus.division_id')->whereIn('store_id', $ids)->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('skus.txt');
foreach ($skus as $sku) {
$data[0] = $sku->sku_code;
$data[1] = $sku->sku_desc;
$data[2] = $sku->ig;
$data[3] = $sku->conversion;
$data[4] = $sku->category_long;
$data[5] = $sku->subcategory;
$data[6] = $sku->brand;
$data[7] = $sku->division;
$data[8] = $sku->store_id;
$writer->addRow($data);
}
// $writer->close();
}
}
public function postposting(Request $request)
{
$sel_ag = $request->ag;
$sel_ch = $request->ch;
$sel_rg = $request->rg;
$sel_st = $request->st;
$sel_us = $request->us;
$sel_ty = $request->ty;
$frm = $request->fr;
$to = $request->to;
$agencies = ComplianceRepository::getAllAgency();
$regions = ComplianceRepository::getAllRegion();
$channels = ComplianceRepository::getAllChannel();
$stores = ComplianceRepository::getAllStore();
$users = ComplianceRepository::getAllUser();
$types = ['1' => 'OSA', '2' => 'Assoertment'];
if (!empty($frm)) {
$data['from'] = $frm;
}
if (!empty($to)) {
$data['to'] = $to;
}
if (!empty($sel_ag)) {
$data['agencies'] = $sel_ag;
}
if (!empty($sel_rg)) {
$data['regions'] = $sel_rg;
}
if (!empty($sel_ch)) {
$data['channels'] = $sel_ch;
}
if (!empty($sel_st)) {
$data['stores'] = $sel_st;
}
if (!empty($sel_us)) {
$data['users'] = $sel_us;
}
if (!empty($sel_ty)) {
$data['types'] = $sel_ty;
}
$postings = HistoryRepositiry::getHistory($data);
if ($request->has('submit')) {
return view('history.posting', compact('postings', 'frm', 'to', 'agencies', 'sel_ag', 'regions', 'sel_rg', 'channels', 'sel_ch', 'stores', 'sel_st', 'users', 'sel_us', 'types', 'sel_ty'));
}
if ($request->has('download')) {
$fileName = "Posting History Report.csv";
$writer = WriterFactory::create(Type::CSV);
// for CSV files
$writer->openToBrowser($fileName);
// stream data directly to the browser
$writer->addRow(array('Agency Code', 'Agency Name', 'Region Code', 'Region Name', 'Channel Code', 'Channel Name', 'Distributor', 'Store Name', 'Store Code', 'Username', 'Transaction Date', 'Posting Date', 'Posting Type'));
foreach ($postings as $row) {
$_data[0] = $row->agency_code;
$_data[1] = $row->agency;
$_data[2] = $row->region_code;
$_data[3] = $row->region_name;
$_data[4] = $row->channel_code;
$_data[5] = $row->channel_name;
$_data[6] = $row->distributor;
$_data[7] = $row->store_name;
$_data[8] = $row->store_code;
$_data[9] = $row->username;
$_data[10] = $row->transaction_date;
$_data[11] = $row->updated_at;
$_data[12] = $row->type;
$writer->addRow($_data);
// add multiple rows at a time
}
$writer->close();
}
}
public function saveExcel()
{
if (!empty($this->data)) {
$data = $this->data;
array_unshift($data, array_keys($data[0]));
$path = Yii::getPathOfAlias('root.assets.import');
if (!is_dir($path)) {
mkdir($path, 0777, true);
}
$filename = "import-" . Helper::camelToSnake($this->modelClass) . '-' . date("Y-m-d~H.i.s") . '.xlsx';
$this->resultFile = $path . DIRECTORY_SEPARATOR . $filename;
$this->resultUrl = Yii::app()->baseUrl . '/assets/import/' . $filename;
$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile($this->resultFile);
foreach ($data as $k => $d) {
foreach ($d as $dk => $dd) {
if (is_object(@$data[$k][$dk])) {
if ($data[$k][$dk] instanceof DateTime) {
$data[$k][$dk] = $data[$k][$dk]->format('Y-m-d H:i:s');
}
}
}
}
$writer->addRows($data);
$writer->close();
return $this->resultUrl;
}
}
public function downloadupdatedig()
{
set_time_limit(0);
ini_set('memory_limit', -1);
$items = UpdatedIg::orderBy('updated_at', 'desc')->get();
$writer = WriterFactory::create(Type::XLSX);
$writer->openToBrowser('Store Item Updated IG.xlsx');
$writer->addRow(array('Area', 'Region Code', 'Region', 'Distributor Code', 'Distributor Name', 'Agency Code', 'Agency', 'Store ID', 'Store Code', 'Store Name', 'Channel Code', 'Channel', 'Other Code', 'SKU Code', 'Description', 'Division', 'Category', 'Sub Category', 'Brand', 'Conversion', 'Min Stock', 'FSO Multiplier', 'LPBT', 'IG', 'Created At', 'Date Updated'));
foreach ($items as $row) {
$data[0] = $row->area;
$data[1] = $row->region_code;
$data[2] = $row->region;
$data[3] = $row->distributor_code;
$data[4] = $row->distributor;
$data[5] = $row->agency_code;
$data[6] = $row->agency;
$data[7] = $row->storeid;
$data[8] = $row->store_code;
$data[9] = $row->store_name;
$data[10] = $row->channel_code;
$data[11] = $row->channel;
$data[12] = $row->other_code;
$data[13] = $row->sku_code;
$data[14] = $row->description;
$data[15] = $row->division;
$data[16] = $row->category;
$data[17] = $row->sub_category;
$data[18] = $row->brand;
$data[19] = $row->conversion;
$data[20] = $row->min_stock;
$data[21] = $row->fso_multiplier;
$data[22] = $row->lpbt;
$data[23] = $row->ig;
$data[24] = (string) $row->created_at;
$data[25] = (string) $row->updated_at;
$writer->addRow($data);
}
$writer->close();
}
/**
* Output file headers to initialise the download of the file.
*/
public function send_http_headers()
{
$this->writer = \Box\Spout\Writer\WriterFactory::create($this->spouttype);
$filename = $this->filename . $this->get_extension();
$this->writer->openToBrowser($filename);
if ($this->sheettitle && $this->writer instanceof \Box\Spout\Writer\AbstractMultiSheetsWriter) {
$sheet = $this->writer->getCurrentSheet();
$sheet->setName($this->sheettitle);
}
}
/**
* @inheritdoc
*/
public function process()
{
//CSV object initialization
$spoutObject = WriterFactory::create(Type::CSV);
switch ($this->target) {
case ExportMenu::TARGET_SELF:
case ExportMenu::TARGET_BLANK:
Yii::$app->controller->layout = false;
$spoutObject->openToBrowser($this->filename . $this->extension);
break;
case ExportMenu::TARGET_QUEUE:
default:
Yii::$app->controller->layout = false;
$spoutObject->openToBrowser($this->filename . $this->extension);
break;
}
//header
$headerRow = $this->generateHeader();
if (!empty($headerRow)) {
$spoutObject->addRow($headerRow);
}
//body
$bodyRows = $this->generateBody();
foreach ($bodyRows as $row) {
$spoutObject->addRow($row);
}
//footer
$footerRow = $this->generateFooter();
if (!empty($footerRow)) {
$spoutObject->addRow($footerRow);
}
$spoutObject->close();
}
/**
* {@inheritdoc}
*/
public function writerTest()
{
$csv = WriterFactory::create(Type::CSV);
$csv->openToFile($this->path);
foreach ($this->generateRawData() as $row) {
$csv->addRow($row);
}
$csv->close();
}
/**
* Creates a Writer from file extension.
*
* @param string $format
*
* @return \Box\Spout\Writer\WriterInterface
*/
protected function writerFromFormat($format)
{
switch ($format) {
case 'ods':
return WriterFactory::create(Type::ODS);
case 'xlsx':
return WriterFactory::create(Type::XLSX);
case 'csv':
return WriterFactory::create(Type::CSV);
default:
throw new InvalidArgumentException("Invalid output format [{$format}] specified.");
}
}
/**
* Output file headers to initialise the download of the file.
*/
public function send_http_headers()
{
$this->writer = \Box\Spout\Writer\WriterFactory::create($this->spouttype);
if (method_exists($this->writer, 'setTempFolder')) {
$this->writer->setTempFolder(make_request_directory());
}
$filename = $this->filename . $this->get_extension();
$this->writer->openToBrowser($filename);
if ($this->sheettitle && $this->writer instanceof \Box\Spout\Writer\AbstractMultiSheetsWriter) {
$sheet = $this->writer->getCurrentSheet();
$sheet->setName($this->sheettitle);
}
}
/**
* @param string $fileName
* @return Sheet[]
*/
private function writeDataAndReturnSheets($fileName)
{
$this->createGeneratedFolderIfNeeded($fileName);
$resourcePath = $this->getGeneratedResourcePath($fileName);
/** @var \Box\Spout\Writer\XLSX $writer */
$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile($resourcePath);
$writer->addRow(['xlsx--sheet1--11', 'xlsx--sheet1--12']);
$writer->addNewSheetAndMakeItCurrent();
$writer->addRow(['xlsx--sheet2--11', 'xlsx--sheet2--12', 'xlsx--sheet2--13']);
$writer->close();
return $writer->getSheets();
}
public static function downloadSample($model, $config = null)
{
$import = new Import($model, $config);
$data = $import->model->find();
$header = [];
$sample = [];
$modelCols = $import->model->tableSchema->columns;
foreach ($import->columns as $key => $col) {
$col = is_string($col) ? ['type' => $col] : $col;
switch ($col['type']) {
case "pk":
case "default":
$header[] = $key;
if (isset($col['sample'])) {
$sample[] = $col['sample'];
} else {
if (isset($modelCols[$key])) {
if ($col['type'] == 'pk') {
$sample[] = '';
} else {
if (isset($data)) {
$sample[] = $data->{$key};
} else {
$sample[] = Export::getSampleText($modelCols[$key]);
}
}
} else {
$sample[] = "text";
}
}
break;
case "lookup":
if (@$col['show'] !== false) {
$header[] = $key;
if (isset($col['sample'])) {
$sample[] = $col['sample'];
} else {
$sample[] = 'text';
}
}
break;
}
}
$writer = WriterFactory::create(Type::XLSX);
$writer->openToBrowser("import-" . Helper::camelToSnake($model) . '.xlsx');
// stream data directly to the browser
$writer->addRows([$header, $sample]);
// add multiple rows at a time
$writer->close();
}
public function actionDownloadExcel()
{
$postdata = file_get_contents("php://input");
$post = CJSON::decode($postdata);
if (isset($post['rows'])) {
$writer = WriterFactory::create(Type::XLSX);
$dir = Yii::getPathOfAlias('root.assets.gvExport') . "/";
if (!is_dir($dir)) {
mkdir($dir, 0777);
}
$file = "export-" . time() . ".xlsx";
$writer->openToFile($dir . $file);
// stream data directly to the browser
$writer->addRows($post['rows']);
// add multiple rows at a time
$writer->close();
echo Yii::app()->baseUrl . '/assets/gvExport/' . $file;
}
}
/**
* @param string $extension
* @param PyStringNode $string
*
* @Given /^the following ([^"]*) file to import:$/
*/
public function theFollowingFileToImport($extension, PyStringNode $string)
{
$extension = strtolower($extension);
$string = $this->replacePlaceholders($string);
self::$placeholderValues['%file to import%'] = $filename = sprintf('%s/pim-import/behat-import-%s.%s', self::$placeholderValues['%tmp%'], substr(md5(rand()), 0, 7), $extension);
@rmdir(dirname($filename));
@mkdir(dirname($filename), 0777, true);
if (Type::XLSX === $extension) {
$writer = WriterFactory::create($extension);
$writer->openToFile($filename);
foreach (explode(PHP_EOL, $string) as $row) {
$rowCells = explode(";", $row);
foreach ($rowCells as &$cell) {
if (is_numeric($cell)) {
$cell = false === strpos($cell, '.') ? (int) $cell : (double) $cell;
}
}
$writer->addRow($rowCells);
}
$writer->close();
} else {
file_put_contents($filename, (string) $string);
}
}
/**
* @param ExportableInterface $adminList
*
* @return Response
*
* @throws \Exception
*/
public function streamExcelSheet(ExportableInterface $adminList)
{
$response = new StreamedResponse();
$response->setCallback(function () use($adminList) {
$writer = WriterFactory::create(Type::XLSX);
$writer->openToBrowser("export.xlsx");
$row = array();
foreach ($adminList->getExportColumns() as $column) {
$row[] = $this->translator->trans($column->getHeader());
}
$writer->addRow($row);
$iterator = $adminList->getIterator();
$rows = array();
foreach ($iterator as $item) {
if (array_key_exists(0, $item)) {
$itemObject = $item[0];
} else {
$itemObject = $item;
}
$row = array();
foreach ($adminList->getExportColumns() as $column) {
$data = $adminList->getStringValue($itemObject, $column->getName());
if (is_object($data)) {
if (!$this->renderer->exists($column->getTemplate())) {
throw new \Exception('No export template defined for ' . get_class($data));
}
$data = $this->renderer->render($column->getTemplate(), array('object' => $data));
}
$row[] = $data;
}
$rows[] = $row;
}
$writer->addRows($rows);
$writer->close();
});
return $response;
}
public function index(Request $request)
{
$user = $request->id;
$type = $request->type;
$ext = $request->ext;
$storelist = DB::table('store_users')->select('stores.id', 'stores.store_code', 'stores.store_name', 'stores.channel_id', 'channels.channel_desc', 'areas.area', 'enrollments.enrollment', 'distributors.distributor_code', 'distributors.distributor', 'stores.storeid', 'stores.store_code_psup', 'clients.client_code', 'clients.client_name', 'channels.channel_code', 'customers.customer_code', 'customers.customer_name', 'regions.region_code', 'regions.region', 'regions.region_short', 'agencies.agency_code', 'agencies.agency_name')->join('stores', 'stores.id', '=', 'store_users.store_id')->join('channels', 'channels.id', '=', 'stores.channel_id')->join('areas', 'areas.id', '=', 'stores.area_id')->join('enrollments', 'enrollments.id', '=', 'stores.enrollment_id')->join('distributors', 'distributors.id', '=', 'stores.distributor_id')->join('clients', 'clients.id', '=', 'stores.client_id')->join('customers', 'customers.id', '=', 'stores.customer_id')->join('regions', 'regions.id', '=', 'stores.region_id')->join('agencies', 'agencies.id', '=', 'stores.agency_id')->where('store_users.user_id', $user)->where('stores.active', 1)->get();
if ($type == 1) {
$settings = Setting::find(1);
if ($ext == 'json') {
return response()->json($settings);
} else {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('settings.txt');
$data[0] = $settings->enable_ig_edit;
$data[1] = $settings->validate_posting_mkl;
$data[2] = $settings->validate_printing_mkl;
$data[3] = $settings->validate_posting_ass;
$data[4] = $settings->validate_printing_ass;
$data[5] = $settings->device_password;
$data[6] = $settings->validate_reposting_mkl;
$data[7] = $settings->validate_reposting_ass;
$writer->addRow($data);
$writer->close();
}
}
// get store list
if ($type == 2) {
if ($ext == 'json') {
$json_data = new \stdClass();
$json_data->total_count = count($storelist);
foreach ($storelist as $store) {
$data = new \stdClass();
$data->id = $store->id;
$data->store_code = $store->store_code;
$data->store_name = $store->store_name;
$data->channel_id = $store->channel_id;
$data->channel_desc = $store->channel_desc;
$data->area = $store->area;
$json_data->stores[] = $data;
}
return response()->json($json_data);
} else {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('stores.txt');
// $writer->addRow(array('ID', 'Store Code', 'Store Name' , 'Channel Id', 'Channel', 'Area'));
$writer->addRow(array(count($storelist)));
foreach ($storelist as $store) {
$data[0] = $store->id;
$data[1] = $store->store_code;
$data[2] = $store->store_name;
$data[3] = $store->channel_id;
$data[4] = $store->channel_desc;
$data[5] = $store->area;
$writer->addRow($data);
}
$writer->close();
}
}
//get store sku list
if ($type == 3) {
$ids = array();
foreach ($storelist as $store) {
$ids[] = $store->id;
}
$skus = DB::table('store_items')->select('store_items.id', 'store_items.store_id', 'items.description', 'items.description_long', 'items.conversion', 'store_items.ig', 'store_items.fso_multiplier', 'items.lpbt', 'categories.category', 'categories.category_long', 'sub_categories.sub_category', 'brands.brand', 'divisions.division', 'other_barcodes.other_barcode', 'items.sku_code', 'items.barcode', 'store_items.min_stock', 'store_items.osa_tagged', 'store_items.npi_tagged')->join('stores', 'stores.id', '=', 'store_items.store_id')->join('items', 'items.id', '=', 'store_items.item_id')->join('other_barcodes', 'other_barcodes.item_id', '=', 'items.id')->join('categories', 'categories.id', '=', 'items.category_id')->join('sub_categories', 'sub_categories.id', '=', 'items.sub_category_id')->join('brands', 'brands.id', '=', 'items.brand_id')->join('divisions', 'divisions.id', '=', 'items.division_id')->where('item_type_id', 1)->whereRaw('other_barcodes.area_id = stores.area_id')->whereIn('store_items.store_id', $ids)->orderBy('store_items.id', 'asc')->get();
$updated_igs = DB::table('updated_igs')->whereIn('store_id', $ids)->get();
$updated_ig_list = [];
if (!empty($updated_igs)) {
foreach ($updated_igs as $updated_ig) {
if (!isset($updated_ig_list[$updated_ig->store_id][$updated_ig->sku_code])) {
$updated_ig_list[$updated_ig->store_id][$updated_ig->sku_code] = 0;
}
$updated_ig_list[$updated_ig->store_id][$updated_ig->sku_code] = $updated_ig->ig;
}
}
if ($ext == 'json') {
$json_data = new \stdClass();
$json_data->total_count = count($skus);
foreach ($skus as $sku) {
$data = new \stdClass();
$data->conversion = $sku->conversion;
$data->lpbt = $sku->lpbt;
$data->category_long = $sku->category_long;
$data->sub_category = $sku->sub_category;
$data->brand = $sku->brand;
$data->division = $sku->division;
$data->store_id = $sku->store_id;
$data->sku_code = $sku->sku_code;
$data->fso_multiplier = $sku->fso_multiplier;
$data->barcode = $sku->barcode;
$data->min_stock = $sku->min_stock;
$data->category = $sku->category;
$data->description_long = $sku->description_long;
$data->osa_tagged = $sku->osa_tagged;
$data->npi_tagged = $sku->npi_tagged;
$json_data->skus[] = $data;
}
return response()->json($json_data);
} else {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('mkl.txt');
// $writer->addRow(array('Other Barcode', 'Item Description', 'Inventory Goal',
// 'Conversion', 'LPBT', 'Category Long', 'Sub-Category', 'Brand', 'Division', 'Store ID', 'Web ID', 'FSO Multiplier', 'Item Barcode', 'Min Stock',
// 'Category', 'Long Desc', 'OSA Tagged', 'NPI Tagged'));
$writer->addRow(array(count($skus)));
foreach ($skus as $sku) {
$data[0] = $sku->other_barcode;
$data[1] = $sku->description;
if (isset($updated_ig_list[$sku->store_id][$sku->sku_code])) {
$data[2] = $updated_ig_list[$sku->store_id][$sku->sku_code];
} else {
$data[2] = $sku->ig;
}
$data[3] = $sku->conversion;
$data[4] = $sku->lpbt;
$data[5] = $sku->category_long;
$data[6] = $sku->sub_category;
$data[7] = $sku->brand;
$data[8] = $sku->division;
$data[9] = $sku->store_id;
$data[10] = $sku->sku_code;
$data[11] = $sku->fso_multiplier;
$data[12] = $sku->barcode;
$data[13] = $sku->min_stock;
$data[14] = $sku->category;
$data[15] = $sku->description_long;
$data[16] = $sku->osa_tagged;
$data[17] = $sku->npi_tagged;
$writer->addRow($data);
}
$writer->close();
}
}
if ($type == 4) {
$ids = array();
foreach ($storelist as $store) {
$ids[] = $store->id;
}
$skus = DB::table('store_items')->select('store_items.id', 'store_items.store_id', 'items.description', 'items.description_long', 'items.conversion', 'store_items.ig', 'store_items.fso_multiplier', 'items.lpbt', 'categories.category', 'categories.category_long', 'sub_categories.sub_category', 'brands.brand', 'divisions.division', 'other_barcodes.other_barcode', 'items.sku_code', 'items.barcode', 'store_items.min_stock')->join('stores', 'stores.id', '=', 'store_items.store_id')->join('items', 'items.id', '=', 'store_items.item_id')->join('other_barcodes', 'other_barcodes.item_id', '=', 'items.id')->join('categories', 'categories.id', '=', 'items.category_id')->join('sub_categories', 'sub_categories.id', '=', 'items.sub_category_id')->join('brands', 'brands.id', '=', 'items.brand_id')->join('divisions', 'divisions.id', '=', 'items.division_id')->where('store_items.item_type_id', 2)->whereRaw('other_barcodes.area_id = stores.area_id')->whereIn('store_items.store_id', $ids)->orderBy('store_items.id', 'asc')->get();
if ($ext == 'json') {
$json_data = new \stdClass();
$json_data->total_count = count($skus);
foreach ($skus as $sku) {
$data = new \stdClass();
$data->conversion = $sku->conversion;
$data->lpbt = $sku->lpbt;
$data->category_long = $sku->category_long;
$data->sub_category = $sku->sub_category;
$data->brand = $sku->brand;
$data->division = $sku->division;
$data->store_id = $sku->store_id;
$data->sku_code = $sku->sku_code;
$data->fso_multiplier = $sku->fso_multiplier;
$data->barcode = $sku->barcode;
$data->min_stock = $sku->min_stock;
$data->category = $sku->category;
$data->description_long = $sku->description_long;
$json_data->skus[] = $data;
}
return response()->json($json_data);
} else {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('assortment.txt');
// $writer->addRow(array('Other Barcode', 'Item Description', 'Inventory Goal',
// 'Conversion', 'LPBT', 'Category', 'Sub-Category', 'Brand', 'Division', 'Store ID', 'Web ID', 'FSO Multiplier', 'Item Barcode', 'Min Stock'));
$writer->addRow(array(count($skus)));
foreach ($skus as $sku) {
$data[0] = $sku->other_barcode;
$data[1] = $sku->description;
$data[2] = $sku->ig;
$data[3] = $sku->conversion;
$data[4] = $sku->lpbt;
$data[5] = $sku->category_long;
$data[6] = $sku->sub_category;
$data[7] = $sku->brand;
$data[8] = $sku->division;
$data[9] = $sku->store_id;
$data[10] = $sku->sku_code;
$data[11] = $sku->fso_multiplier;
$data[12] = $sku->barcode;
$data[13] = $sku->min_stock;
$data[14] = $sku->category;
$data[15] = $sku->description_long;
$writer->addRow($data);
}
$writer->close();
}
}
if ($type == 5) {
$ids = array();
foreach ($storelist as $store) {
$ids[] = $store->id;
}
$updated_igs = DB::table('updated_igs', 'sku_code', 'ig')->select('store_id', 'sku_code', 'ig')->whereIn('store_id', $ids)->get();
if ($ext == 'json') {
$json_data = new \stdClass();
$json_data->total_count = count($updated_igs);
foreach ($updated_igs as $ig) {
$data = new \stdClass();
$data->store_id = $ig->store_id;
$data->sku_code = $ig->sku_code;
$data->ig = $ig->ig;
$json_data->updated_igs[] = $data;
}
return response()->json($json_data);
} else {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('updatedig.txt');
// $writer->addRow(array('Store Id', 'SKU Code', 'IG'));
$writer->addRow(array(count($updated_igs)));
foreach ($updated_igs as $ig) {
$data[0] = $ig->store_id;
$data[1] = $ig->sku_code;
$data[2] = $ig->ig;
$writer->addRow($data);
}
$writer->close();
}
}
}
/**
* @param $formName string
* @param $options array of option_name => option_value
* @return void
*/
public function export($formName, $options = null)
{
$this->setOptions($options);
$this->setCommonOptions();
// Security Check
if (!$this->isAuthorized()) {
$this->assertSecurityErrorMessage();
return;
}
if (version_compare(phpversion(), '5.4') < 0) {
$this->echoHeaders(array('Content-Type: text/html'));
printf('<html><head><title>%s</title></head>', __('PHP Upgrade Needed', 'contact-form-7-to-database-extension'));
_e('CFDB Excel file export requires PHP 5.4 or later on your server.', 'contact-form-7-to-database-extension');
echo '<br/>';
_e('Your server\'s PHP version: ', 'contact-form-7-to-database-extension');
echo phpversion();
echo '<br/>';
printf('<a href="https://wordpress.org/about/requirements/">%s</a>', __('See WordPress Recommended PHP Version', 'contact-form-7-to-database-extension'));
printf('</body></html');
return;
}
require_once 'Spout-2.4.2/Autoloader/autoload.php';
// required PHP 5.4
// Query DB for the data for that form
$submitTimeKeyName = 'Submit_Time_Key';
$this->setDataIterator($formName, $submitTimeKeyName);
$this->clearOutputBuffer();
$type = Type::XLSX;
$suffix = 'xlsx';
if (isset($options['format'])) {
switch ($options['format']) {
case 'ods':
$type = Type::ODS;
$suffix = 'ods';
break;
// case 'csv' :
// $type = Type::CSV;
// $suffix = 'csv';
// break;
// case 'csv' :
// $type = Type::CSV;
// $suffix = 'csv';
// break;
default:
break;
}
}
$writer = WriterFactory::create($type);
$writer->openToBrowser("{$formName}.{$suffix}");
// stream data directly to the browser
// Column Headers
if (isset($this->options['header']) && $this->options['header'] != 'true') {
// do not output column headers
} else {
$headerRow = array();
foreach ($this->dataIterator->getDisplayColumns() as $aCol) {
$colDisplayValue = $aCol;
if ($this->headers && isset($this->headers[$aCol])) {
$colDisplayValue = $this->headers[$aCol];
}
$headerRow[] = $colDisplayValue;
}
$headerStyle = new Style();
$headerStyle->setFontBold();
$writer->addRowWithStyle($headerRow, $headerStyle);
// add a row at a time
}
// Rows
// $showFileUrlsInExport = $this->plugin->getOption('ShowFileUrlsInExport') == 'true';
while ($this->dataIterator->nextRow()) {
$dataRow = array();
$fields_with_file = null;
if (isset($this->dataIterator->row['fields_with_file']) && $this->dataIterator->row['fields_with_file'] != null) {
$fields_with_file = explode(',', $this->dataIterator->row['fields_with_file']);
}
foreach ($this->dataIterator->getDisplayColumns() as $aCol) {
$cell = isset($this->dataIterator->row[$aCol]) ? $this->dataIterator->row[$aCol] : '';
if ($aCol == 'Submitted' && isset($this->dataIterator->row[$submitTimeKeyName])) {
// Put date in a format that Excel et. al. understand
$timestamp = $this->dataIterator->row[$submitTimeKeyName];
$cell = date('Y-m-d H:i:s', $timestamp);
}
if ($fields_with_file && $cell && in_array($aCol, $fields_with_file)) {
// In the case of file links, we want to create a HYPERLINK formula as a link to download the file.
// But the Spout library doesn't support creating formulas.
// http://cfdbplugin.com/?p=1430
$url = $this->plugin->getFileUrl($this->dataIterator->row[$submitTimeKeyName], $formName, $aCol);
if ($type == Type::ODS) {
$cell = "=HYPERLINK(\"{$url}\"; \"{$cell}\")";
} else {
$cell = "=HYPERLINK(\"{$url}\", \"{$cell}\")";
}
}
$dataRow[] = $cell;
}
$writer->addRow($dataRow);
// add a row at a time
}
$writer->close();
}
/**
* @param array $allRows
* @param string $fileName
* @param string $fieldDelimiter
* @param string $fieldEnclosure
* @return null|string
*/
private function writeToCsvFileAndReturnWrittenContent($allRows, $fileName, $fieldDelimiter = ',', $fieldEnclosure = '"')
{
$this->createGeneratedFolderIfNeeded($fileName);
$resourcePath = $this->getGeneratedResourcePath($fileName);
$writer = WriterFactory::create(Type::CSV);
$writer->setFieldDelimiter($fieldDelimiter);
$writer->setFieldEnclosure($fieldEnclosure);
$writer->openToFile($resourcePath);
$writer->addRows($allRows);
$writer->close();
return file_get_contents($resourcePath);
}
public function index(Request $request)
{
$user = $request->id;
$type = $request->type;
$user = User::find($user);
$storelist = $user->stores()->orderBy('store')->get();
$result = array();
foreach ($storelist as $store) {
$result[] = $store->audit_template_id;
}
$list = array_unique($result);
// get store list
if ($type == "stores") {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('stores.txt');
$writer->addRow(['id', 'store', 'grade_matrix_id', 'audit_template_id', 'template', 'start_date', 'end_date', 'store_code']);
foreach ($storelist as $store) {
$store_ids[] = $store->id;
$data[0] = $store->id;
$data[1] = $store->store;
$data[2] = $store->gradematrix->passing;
$data[3] = $store->audit_template_id;
$data[4] = $store->audittemplate->template;
$data[5] = 0;
$data[6] = 0;
$data[7] = 0;
$data[8] = 0;
$data[9] = $store->audittemplate->start_date;
$data[10] = $store->audittemplate->end_date;
$data[11] = $store->store_code;
$data[12] = $store->account->account;
$data[13] = $store->customer->customer_code;
$data[14] = $store->customer->customer;
$data[15] = $store->region->region_code;
$data[16] = $store->region->region;
$data[17] = $store->distributor->distributor_code;
$data[18] = $store->distributor->distributor;
$data[19] = $store->audittemplate->template_code;
$writer->addRow($data);
}
$writer->close();
}
// get template categories
if ($type == "temp_categories") {
$categories = AuditTemplateCategory::select('audit_template_categories.id', 'audit_template_categories.audit_template_id', 'audit_template_categories.category_id', 'form_categories.category', 'audit_template_categories.category_order', 'form_categories.perfect_store')->join('form_categories', 'form_categories.id', '=', 'audit_template_categories.category_id')->whereIn('audit_template_id', $list)->orderBy('audit_template_id')->orderBy('category_order')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('temp_category.txt');
$writer->addRow(['id', 'audit_template_id', 'category_id', 'category', 'category_order', 'perfect_store']);
foreach ($categories as $category) {
$data[0] = $category->id;
$data[1] = $category->audit_template_id;
$data[2] = $category->category_order;
$data[3] = $category->category_id;
$data[4] = $category->category;
$data[5] = $category->perfect_store;
$writer->addRow($data);
}
$writer->close();
}
// get template groups
if ($type == "temp_groups") {
$c_list = array();
$categories = AuditTemplateCategory::select('audit_template_categories.id', 'audit_template_categories.audit_template_id', 'audit_template_categories.category_id', 'form_categories.category', 'audit_template_categories.category_order')->join('form_categories', 'form_categories.id', '=', 'audit_template_categories.category_id')->whereIn('audit_template_id', $list)->orderBy('audit_template_id')->orderBy('category_order')->get();
foreach ($categories as $category) {
$c_list[] = $category->id;
}
$groups = AuditTemplateGroup::select('audit_template_groups.id', 'audit_template_groups.form_group_id', 'form_groups.group_desc', 'audit_template_categories.audit_template_id', 'audit_template_groups.group_order', 'audit_template_groups.audit_template_category_id', 'form_groups.perfect_store')->join('audit_template_categories', 'audit_template_categories.id', '=', 'audit_template_groups.audit_template_category_id')->join('form_groups', 'form_groups.id', '=', 'audit_template_groups.form_group_id')->whereIn('audit_template_category_id', $c_list)->orderBy('audit_template_id')->orderBy('audit_template_category_id')->orderBy('group_order')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('temp_group.txt');
$writer->addRow(['id', 'audit_template_id', 'audit_template_category_id', 'group_order', 'form_group_id', 'group_desc', 'perfect_store']);
foreach ($groups as $group) {
$data[0] = $group->id;
$data[1] = $group->audit_template_id;
$data[2] = $group->audit_template_category_id;
$data[3] = $group->group_order;
$data[4] = $group->form_group_id;
$data[5] = $group->group_desc;
$data[6] = $group->perfect_store;
$writer->addRow($data);
}
$writer->close();
}
// get template questions
if ($type == "temp_questions") {
// dd($list);
$forms = AuditTemplateForm::select('audit_template_forms.id', 'audit_template_forms.order', 'audit_template_forms.audit_template_group_id', 'audit_template_forms.audit_template_id', 'audit_template_forms.form_id', 'forms.form_type_id', 'forms.prompt', 'forms.required', 'forms.expected_answer', 'forms.exempt', 'forms.image', 'forms.default_answer')->join('audit_template_groups', 'audit_template_groups.id', '=', 'audit_template_forms.audit_template_group_id')->join('audit_template_categories', 'audit_template_categories.id', '=', 'audit_template_groups.audit_template_category_id')->join('forms', 'forms.id', '=', 'audit_template_forms.form_id')->join('form_categories', 'form_categories.id', '=', 'audit_template_categories.category_id')->join('form_groups', 'form_groups.id', '=', 'audit_template_groups.form_group_id')->whereIn('audit_template_forms.audit_template_id', $list)->get();
// dd($forms);
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('questions.txt');
$writer->addRow(['id', 'order', 'audit_template_group_id', 'audit_template_id', 'form_id', 'form_type_id', 'prompt', 'required', 'expected_answer', 'exempt', 'image', 'default_answer']);
foreach ($forms as $form) {
$data[0] = $form->id;
$data[1] = $form->order;
$data[2] = $form->audit_template_group_id;
$data[3] = $form->audit_template_id;
$data[4] = $form->form_id;
$data[5] = $form->form_type_id;
$prompt = "null";
if (!empty($form->prompt)) {
$prompt = preg_replace("/[\\n\\r]+/", " ", $form->prompt);
}
$data[6] = $prompt;
$data[7] = $form->required;
$data[8] = $form->expected_answer;
$data[9] = $form->exempt;
$data[10] = $form->form->image;
$data[11] = $form->default_answer;
// var_dump($data);
$writer->addRow($data);
}
$writer->close();
}
// get template forms
if ($type == "temp_forms") {
$forms = Form::whereIn('forms.audit_template_id', $list)->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('forms.txt');
$writer->addRow(['id', 'audit_template_id', 'form_type_id', 'prompt', 'required', 'expected_answer', 'exempt', 'image', 'default_answer']);
foreach ($forms as $form) {
$data[0] = $form->id;
$data[1] = $form->audit_template_id;
$data[2] = $form->form_type_id;
$data[3] = preg_replace("/[\\n\\r]+/", " ", $form->prompt);
$data[4] = $form->required;
$data[5] = $form->expected_answer;
$data[6] = $form->exempt;
$data[7] = $form->image;
$data[8] = $form->default_answer;
$writer->addRow($data);
}
$writer->close();
}
// get single selects
if ($type == "single_selects") {
$forms = Form::whereIn('forms.audit_template_id', $list)->where('form_type_id', 10)->get();
$form_ids = array();
foreach ($forms as $form) {
$form_ids[] = $form->id;
}
$selections = FormSingleSelect::select('form_single_selects.form_id', 'single_selects.id', 'single_selects.option')->join('single_selects', 'single_selects.id', '=', 'form_single_selects.single_select_id')->whereIn('form_single_selects.form_id', $form_ids)->orderBy('form_single_selects.form_id')->get();
// dd($selections);
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('single_selects.txt');
$writer->addRow(['form_id', 'id', 'option']);
foreach ($selections as $selection) {
$data[0] = $selection->form_id;
$data[1] = $selection->id;
$data[2] = $selection->option;
$writer->addRow($data);
}
$writer->close();
}
// get multiple selects
if ($type == "multi_selects") {
$forms = Form::whereIn('forms.audit_template_id', $list)->where('form_type_id', 9)->get();
$form_ids = array();
foreach ($forms as $form) {
$form_ids[] = $form->id;
}
$selections = FormMultiSelect::select('form_multi_selects.form_id', 'multi_selects.id', 'multi_selects.option')->join('multi_selects', 'multi_selects.id', '=', 'form_multi_selects.multi_select_id')->whereIn('form_multi_selects.form_id', $form_ids)->orderBy('form_multi_selects.form_id')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('multi_selects.txt');
$writer->addRow(['form_id', 'id', 'option']);
foreach ($selections as $selection) {
$data[0] = $selection->form_id;
$data[1] = $selection->id;
$data[2] = $selection->option;
$writer->addRow($data);
}
$writer->close();
}
// get formulas
if ($type == "formulas") {
$forms = Form::whereIn('forms.audit_template_id', $list)->where('form_type_id', 11)->get();
$form_ids = array();
foreach ($forms as $form) {
$form_ids[] = $form->id;
}
$formulas = FormFormula::select('form_formulas.form_id', 'formula')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('formula.txt');
$writer->addRow(['form_id', 'formula']);
foreach ($formulas as $formula) {
$data[0] = $formula->form_id;
$data[1] = $formula->formula;
$writer->addRow($data);
}
$writer->close();
}
// get conditions
if ($type == "conditions") {
$forms = Form::whereIn('forms.audit_template_id', $list)->where('form_type_id', 12)->get();
$form_ids = array();
foreach ($forms as $form) {
$form_ids[] = $form->id;
}
$conditions = FormCondition::select('form_conditions.form_id', 'option', 'condition', 'id')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('conditions.txt');
$writer->addRow(['form_id', 'option', 'condition']);
foreach ($conditions as $condition) {
$data[0] = $condition->form_id;
$data[1] = $condition->option;
$data[2] = $condition->condition;
$data[3] = $condition->id;
$writer->addRow($data);
}
$writer->close();
}
// get secondary display lookup
if ($type == "secondary_lookups") {
$store_ids = array();
foreach ($storelist as $store) {
$store_ids[] = $store->id;
}
$secondarydisplay = SecondaryDisplayLookup::select('store_id', 'category_id', 'brand')->whereIn('store_id', $store_ids)->join('secondary_displays', 'secondary_displays.id', '=', 'secondary_display_lookups.secondary_display_id')->orderBy('store_id')->orderBy('category_id')->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('secondarydisplay.txt');
$writer->addRow(['store_id', 'category_id', 'brand']);
foreach ($secondarydisplay as $category) {
$data[0] = $category->store_id;
$data[1] = $category->category_id;
$data[2] = strtoupper($category->brand);
$writer->addRow($data);
}
$writer->close();
}
// get secondary key list
if ($type == "secondary_lists") {
$keylist = FormGroup::where('secondary_display', 1)->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('secondary_keylist.txt');
$writer->addRow(['id']);
foreach ($keylist as $list) {
$data[0] = $list->id;
$writer->addRow($data);
}
$writer->close();
}
// get osa key list
if ($type == "osa_lists") {
$keylist = FormGroup::where('osa', 1)->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('osa_keylist.txt');
$writer->addRow(['id']);
foreach ($keylist as $list) {
$data[0] = $list->id;
$writer->addRow($data);
}
$writer->close();
}
if ($type == "osa_lookups") {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('osa_lookups.txt');
$writer->addRow(['id', 'category_id', 'target', 'total', 'lookup_id']);
foreach ($storelist as $store) {
$lookup = OsaLookup::getOsaCategory($store->id);
foreach ($lookup->categories as $category) {
$data[0] = $store->id;
$data[1] = $category->category_id;
$data[2] = $category->target;
$data[3] = $category->total;
$data[4] = $lookup->id;
$writer->addRow($data);
}
}
$writer->close();
}
// sos lookup
if ($type == "sos_lists") {
$keylist = FormGroup::where('sos', 1)->get();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('sos_keylist.txt');
$writer->addRow(['id']);
foreach ($keylist as $list) {
$data[0] = $list->id;
$writer->addRow($data);
}
$writer->close();
}
if ($type == "sos_lookups") {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('sos_lookups.txt');
$writer->addRow(['store_id', 'category_id', 'sos_id', 'less', 'value', 'sos_lookup_id']);
foreach ($storelist as $store) {
$lookup = SosLookup::getSosCategory($store->id);
$results = DB::select(DB::raw("select store_id, category_id, sos_id,less, value,sos_lookup_id from store_sos_tags\n join sos_lookup_percentages on sos_lookup_percentages.category_id = store_sos_tags.`form_category_id`\n where store_id = :store_id\n and sos_lookup_id = :lookup_id\n and store_sos_tags.`sos_tag_id` = sos_lookup_percentages.sos_id"), array('store_id' => $store->id, 'lookup_id' => $lookup->id));
// dd($results);
foreach ($results as $result) {
$data[0] = $result->store_id;
$data[1] = $result->category_id;
$data[2] = $result->sos_id;
$data[3] = $result->less;
$data[4] = $result->value;
$data[5] = $result->sos_lookup_id;
$writer->addRow($data);
}
}
$writer->close();
}
if ($type == "form_types") {
$form_types = FormType::all();
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('form_types.txt');
$writer->addRow(['id', 'form_type']);
foreach ($form_types as $type) {
$data[0] = $type->id;
$data[1] = $type->form_type;
$writer->addRow($data);
}
$writer->close();
}
if ($type == "image_lists") {
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('image_lists.txt');
$writer->addRow(['image_name']);
$results = SurveyImage::all();
// dd($results);
foreach ($results as $result) {
$data[0] = $result->images;
$writer->addRow($data);
}
$writer->close();
}
}
/**
* @inheritDoc
*/
public function __construct($outputFilePath)
{
$this->reportWriter = WriterFactory::create(Type::XLSX);
$this->reportWriter->openToFile($outputFilePath);
}
/**
* @param array $allRows
* @param int $numSheets
* @param string $fileName
* @param bool $shouldUseInlineStrings
* @param bool $shouldCreateSheetsAutomatically
* @return Writer
*/
private function writeToMultipleSheetsInXLSXFile($allRows, $numSheets, $fileName, $shouldUseInlineStrings = true, $shouldCreateSheetsAutomatically = true)
{
$this->createGeneratedFolderIfNeeded($fileName);
$resourcePath = $this->getGeneratedResourcePath($fileName);
/** @var \Box\Spout\Writer\XLSX\Writer $writer */
$writer = WriterFactory::create(Type::XLSX);
$writer->setShouldUseInlineStrings($shouldUseInlineStrings);
$writer->setShouldCreateNewSheetsAutomatically($shouldCreateSheetsAutomatically);
$writer->openToFile($resourcePath);
$writer->addRows($allRows);
for ($i = 1; $i < $numSheets; $i++) {
$writer->addNewSheetAndMakeItCurrent();
$writer->addRows($allRows);
}
$writer->close();
return $writer;
}
<?php
/**
* Created by PhpStorm.
* User: jantonio
* Date: 30/11/15
* Time: 13:42
*/
date_default_timezone_set("Europe/Madrid");
require_once __DIR__ . '/Spout/Autoloader/autoload.php';
use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;
$writer = WriterFactory::create(Type::XLSX);
$writer->openToBrowser($filename);
// Headers
$writer->addRow(array('Order #', 'Customer name', 'Total ordered', 'Date'));
// Then a foreach
$writer->addRow(array((int) 00, 'Customer name', (double) 23.12, '20-01-2016'));
$writer->close();
/**
* @param string $filePath File path to open with the writer
* @param array $options Options for Spout writer (delimiter, enclosure, ...)
*
* @throws UnsupportedTypeException
*
* @return WriterInterface
*/
protected function getWriter($filePath, array $options = [])
{
if (!isset($options['type'])) {
throw new \InvalidArgumentException('Option "type" have to be defined');
}
$writer = WriterFactory::create($options['type']);
unset($options['type']);
foreach ($options as $name => $option) {
$setter = 'set' . ucfirst($name);
if (method_exists($writer, $setter)) {
$writer->{$setter}($option);
} else {
$message = sprintf('Option "%s" does not exist in writer "%s"', $setter, get_class($writer));
throw new \InvalidArgumentException($message);
}
}
$writer->openToFile($filePath);
return $writer;
}
/**
* @param array $allRows
* @param string $fileName
* @param \Box\Spout\Writer\Style\Style|null[] $styles
* @return Writer
*/
private function writeToXLSXFileWithMultipleStyles($allRows, $fileName, $styles)
{
// there should be as many rows as there are styles passed in
$this->assertEquals(count($allRows), count($styles));
$this->createGeneratedFolderIfNeeded($fileName);
$resourcePath = $this->getGeneratedResourcePath($fileName);
/** @var \Box\Spout\Writer\XLSX\Writer $writer */
$writer = WriterFactory::create(Type::XLSX);
$writer->setShouldUseInlineStrings(true);
$writer->openToFile($resourcePath);
for ($i = 0; $i < count($allRows); $i++) {
if ($styles[$i] === null) {
$writer->addRow($allRows[$i]);
} else {
$writer->addRowWithStyle($allRows[$i], $styles[$i]);
}
}
$writer->close();
return $writer;
}
public function storeassortment()
{
$take = 1000;
// adjust this however you choose
$skip = 0;
// used to skip over the ones you've already processed
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser('Store Assortment Masterfile.csv');
$writer->addRow(array('STORE CODE', 'STORE NAME', 'SKU CODE', 'BARCODE', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'MIN STOCK'));
set_time_limit(0);
while ($rows = StoreItem::getPartial($take, $skip, 2)) {
if (count($rows) == 0) {
break;
}
$skip++;
$plunck_data = [];
foreach ($rows as $row) {
$row_data[0] = $row->store_code;
$row_data[1] = $row->store_name;
$row_data[2] = $row->sku_code;
$row_data[3] = $row->barcode;
$row_data[4] = $row->description;
$row_data[5] = $row->ig;
$row_data[6] = $row->fso_multiplier;
$row_data[7] = $row->min_stock;
$plunck_data[] = $row_data;
}
$writer->addRows($plunck_data);
}
$writer->close();
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request, $type = null)
{
$sel_ag = $request->ag;
$sel_cl = $request->cl;
$sel_ch = $request->ch;
$sel_ds = $request->ds;
$sel_en = $request->en;
$sel_rg = $request->rg;
$sel_st = $request->st;
$sel_tag = $request->tags;
$sel_av = $request->availability;
$tags = ['1' => 'OSA', '2' => 'NPI'];
$availability = ['1' => 'oos', '2' => 'osa'];
$report_type = 1;
if (is_null($type) || $type != 'assortment') {
$report_type = 2;
}
if ($report_type == 2) {
$agencies = StoreInventories::getAgencyList();
$divisions = ItemInventories::getDivisionList();
} else {
$agencies = AssortmentInventories::getAgencyList();
$divisions = AssortmentItemInventories::getDivisionList();
}
$sel_dv = $request->dv;
$sel_cat = $request->ct;
$sel_scat = $request->sc;
$sel_br = $request->br;
$frm = $request->fr;
$to = $request->to;
$data = array();
if (!empty($sel_ag)) {
$data['agencies'] = $sel_ag;
}
if (!empty($sel_cl)) {
$data['clients'] = $sel_cl;
}
if (!empty($sel_ch)) {
$data['channels'] = $sel_ch;
}
if (!empty($sel_ds)) {
$data['distributors'] = $sel_ds;
}
if (!empty($sel_en)) {
$data['enrollments'] = $sel_en;
}
if (!empty($sel_rg)) {
$data['regions'] = $sel_rg;
}
if (!empty($sel_st)) {
$data['stores'] = $sel_st;
}
if (!empty($sel_dv)) {
$data['divisions'] = $sel_dv;
}
if (!empty($sel_cat)) {
$data['categories'] = $sel_cat;
}
if (!empty($sel_scat)) {
$data['sub_categories'] = $sel_scat;
}
if (!empty($sel_br)) {
$data['brands'] = $sel_br;
}
if (!empty($frm)) {
$data['from'] = $frm;
}
if (!empty($to)) {
$data['to'] = $to;
}
if (!empty($sel_tag)) {
$data['tags'] = $sel_tag;
}
if (!empty($sel_av)) {
$data['availability'] = $sel_av;
}
if ($report_type == 2) {
$items = ItemInventories::filter($data);
$header = "MKL Posted Transaction Report";
} else {
$items = AssortmentItemInventories::filter($data);
$header = "Assortment Posted Transaction Report";
}
if ($request->has('submit')) {
return view('inventory.index', compact('frm', 'to', 'agencies', 'sel_ag', 'sel_cl', 'sel_ch', 'sel_ds', 'sel_en', 'sel_rg', 'sel_st', 'divisions', 'sel_dv', 'sel_cat', 'sel_scat', 'sel_br', 'items', 'header', 'type', 'sel_tag', 'tags', 'sel_av', 'availability'));
}
set_time_limit(0);
if ($request->has('download')) {
$take = 1000;
// adjust this however you choose
$skip = 0;
// used to skip over the ones you've already processed
$writer = WriterFactory::create(Type::CSV);
$writer->openToBrowser($header . '.csv');
$writer->addRow(array('AREA', 'REGION', 'DISTRIBUTOR', 'DISTRIBUTOR CODE', 'STORE ID', 'STORE CODE', 'STORE NAME', 'OTHER CODE', 'SKU CODE', 'DIVISION', 'BRAND', 'CATEGORY', 'SUB CATEGORY', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'SAPC', 'WHPC', 'WHCS', 'SO', 'FSO', 'FSO VAL', 'OSA', 'OOS', 'TRANSACTION DATE', 'POSTING DATE AND TIME', 'SIGNATURE LINK'));
if ($report_type == 2) {
while ($rows = ItemInventories::getPartial($data, $take, $skip)) {
if (count($rows) == 0) {
break;
}
$skip++;
$plunck_data = [];
foreach ($rows as $row) {
if (!is_null($row->signature)) {
if ($report_type == 2) {
$link = url('api/pcountimage', [$row->signature]);
} else {
$link = url('api/assortmentimage', [$row->signature]);
}
} else {
$link = '';
}
$row_data[0] = $row->area;
$row_data[1] = $row->region_name;
$row_data[2] = $row->distributor;
$row_data[3] = $row->distributor_code;
$row_data[4] = $row->store_id;
$row_data[5] = $row->store_code;
$row_data[6] = $row->store_name;
$row_data[7] = $row->other_barcode;
$row_data[8] = $row->sku_code;
$row_data[9] = $row->division;
$row_data[10] = $row->brand;
$row_data[11] = $row->category;
$row_data[12] = $row->sub_category;
$row_data[13] = $row->description;
$row_data[14] = $row->ig;
$row_data[15] = $row->fso_multiplier;
$row_data[16] = $row->sapc;
$row_data[17] = $row->whpc;
$row_data[18] = $row->whcs;
$row_data[19] = $row->so;
$row_data[20] = $row->fso;
$row_data[21] = (double) $row->fso_val;
$row_data[22] = $row->osa;
$row_data[23] = $row->oos;
$row_data[24] = $row->transaction_date;
$row_data[25] = $row->created_at;
$row_data[26] = $link;
$plunck_data[] = $row_data;
}
$writer->addRows($plunck_data);
// add multiple rows at a time
}
} else {
while ($rows = AssortmentItemInventories::getPartial($data, $take, $skip)) {
if (count($rows) == 0) {
break;
}
$skip++;
$plunck_data = [];
foreach ($rows as $row) {
if (!is_null($row->signature)) {
if ($report_type == 2) {
$link = url('api/pcountimage', [$row->signature]);
} else {
$link = url('api/assortmentimage', [$row->signature]);
}
} else {
$link = '';
}
$row_data[0] = $row->area;
$row_data[1] = $row->region_name;
$row_data[2] = $row->distributor;
$row_data[3] = $row->distributor_code;
$row_data[4] = $row->store_id;
$row_data[5] = $row->store_code;
$row_data[6] = $row->store_name;
$row_data[7] = $row->other_barcode;
$row_data[8] = $row->sku_code;
$row_data[9] = $row->division;
$row_data[10] = $row->brand;
$row_data[11] = $row->category;
$row_data[12] = $row->sub_category;
$row_data[13] = $row->description;
$row_data[14] = $row->ig;
$row_data[15] = $row->fso_multiplier;
$row_data[16] = $row->sapc;
$row_data[17] = $row->whpc;
$row_data[18] = $row->whcs;
$row_data[19] = $row->so;
$row_data[20] = $row->fso;
$row_data[21] = (double) $row->fso_val;
$row_data[22] = $row->osa;
$row_data[23] = $row->oos;
$row_data[24] = $row->transaction_date;
$row_data[25] = $row->created_at;
$row_data[26] = $link;
$plunck_data[] = $row_data;
}
$writer->addRows($plunck_data);
// add multiple rows at a time
}
}
$writer->close();
}
}
function __construct($file)
{
$this->myFile = $file;
date_default_timezone_set('Europe/Madrid');
$this->myConfig = Config::getInstance();
$this->myLogger = new Logger($file, $this->myConfig->getEnv("debug_level"));
$this->myWriter = WriterFactory::create(Type::XLSX);
$this->titleStyle = (new StyleBuilder())->setFontBold()->setFontItalic()->setFontSize(15)->setFontColor(substr($this->myConfig->getEnv('pdf_hdrfg1'), 1))->build();
$this->rowHeaderStyle = (new StyleBuilder())->setFontBold()->setFontItalic()->setFontSize(12)->setFontColor(substr($this->myConfig->getEnv('pdf_hdrfg2'), 1))->build();
}
