public static function loadFromFile($royaltyStreamFiles) { $query = "LOAD DATA LOCAL INFILE " . DB::connection()->getPdo()->quote($royaltyStreamFiles[0]->stream_file_name) . "\n INTO TABLE " . RoyaltyStream::table() . "\n CHARACTER SET 'utf8'\n FIELDS TERMINATED BY ','\n ENCLOSED BY '\"'\n LINES TERMINATED BY '\n'\n IGNORE 1 LINES\n (@col" . implode(",@col", array_keys(CsvFileService::getFileHeaders($royaltyStreamFiles[0]->stream_file_name))) . ")\n SET "; foreach (self::fieldsToFileHeaderPositions() as $field => $position) { if (array_key_exists($field, self::fieldFilters())) { $query .= " {$field}=" . self::fieldFilter($field, "@col" . $position) . ", "; } else { $query .= " {$field}=TRIM(@col{$position}), "; } } $query = rtrim($query, ", "); $query .= ", created = now(), updated = now(), period_year = '" . $royaltyStreamFiles[0]->period_year . "',\n period_month = '" . $royaltyStreamFiles[0]->period_month . "', period_quarter = '" . $royaltyStreamFiles[0]->period_quarter . "' "; //@col3 == payee code $streamFileIdsFilter = "CASE @col3"; foreach ($royaltyStreamFiles as $royaltyStreamFile) { $streamFileIdsFilter .= " WHEN '" . $royaltyStreamFile->deal()->first()->payee_code . "' THEN '" . $royaltyStreamFile->id . "' "; } $streamFileIdsFilter .= "ELSE NULL END"; $query .= ", stream_file_id = " . $streamFileIdsFilter; // echo "<pre>"; // print_r($query); // echo "</pre>"; // die("moare aici"); DB::connection()->getpdo()->exec($query); }
public static function upload() { $uploadPath = storage_path('temp') . "/" . basename($_FILES['file']['name']); if (!move_uploaded_file($_FILES['file']['tmp_name'], $uploadPath)) { return null; } $payeePayments = CsvFileService::csvToArray($uploadPath); foreach ($payeePayments as $payeePayment) { self::updateOrCreatePayeePayment($payeePayment); } unlink($uploadPath); return "success"; }
public static function apiNoEmailUsers() { $results = PayeeCompany::payeesWithoutEmail()->select(["user.id", "user.name", "user.code", "user.email"])->get()->toArray(); return ["content" => CsvFileService::toCsv($results, "id,name,code,email,password"), "content_type" => "text/csv", "filename" => "noEmailUsers.csv"]; }
public static function loadFromFile(RoyaltyPaymentFile $rpf) { $firstLine = CsvFileService::getFileFirstLine($rpf->path); $header = explode(',', strtolower(trim($firstLine))); $fieldsToFileHeaderPos = RoyaltyPayment::fieldsToFileHeaderPositions(); if (count($header) == count(RoyaltyPayment::fieldsToFileHeaderPositionsOld())) { $fieldsToFileHeaderPos = RoyaltyPayment::fieldsToFileHeaderPositionsOld(); } $query = "LOAD DATA LOCAL INFILE " . DB::connection()->getPdo()->quote($rpf->path) . "\n INTO TABLE " . RoyaltyPayment::table() . "\n CHARACTER SET 'utf8'\n FIELDS TERMINATED BY ','\n ENCLOSED BY '\"' " . RoyaltyPayment::detectNewLineChar($firstLine) . "\n IGNORE 1 LINES\n (@col" . implode(",@col", array_keys($header)) . ")\n SET "; foreach ($fieldsToFileHeaderPos as $position => $field) { if (array_key_exists($field, self::fieldFilters())) { $query .= " {$field}=" . self::fieldFilter($field, "@col" . ($position + 2)) . ", "; } else { $query .= " {$field}=TRIM(@col" . ($position + 2) . "), "; } } $query = rtrim($query, ", "); $query .= ", created_at = now(), updated_at = now(), company_id = " . $rpf->company_id . ", royalty_payment_file_id = " . $rpf->id; DB::connection()->getpdo()->exec($query); }