<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Input;
use App\User;
use App\Book_detail;
use App\Assign_contributor;
use App\Book_financial;
use App\Book_expense;
use Illuminate\Pagination\LengthAwarePaginator;
use App\Amazon_sales_report;
use App\barnes_nobles_sale_reports;
use App\apple_sale_reports;
use App\google_sale_reports;
use App\comixology_sale_reports;
use App\Oni_press_shopify_sale;
use App\comixology_subscription_sale_reports;
use App\S_s_sale_report;
use App\Oni_real_shopify_sale;
use Excel;


class CsvController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }
    /**
    Uploading Csv
    **/
    public function uploadcsv(Request $request){
    /*
    Add csv : add products 
    */
        if ($request->has('csv')) {
            
            ##required field
            $validator = Validator::make($request->all(), [ 'csv_file'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }
            #checking if file uploaded is csv or not
            $myextension = $request->csv_file->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }
            #publisher id
            $id = Auth::user()->id;
            #create unique csv id in csv_records table
            $csv_token = "book_details_".date('Y-m-d H:i:s'); //unique token
            #inserting token into the db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }
            if($csv_data == 1){
                #getting from db
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }
            #to collect list of contributor's id
            $contri_array = array(); 
            #to collect list of latest book id
            $book_id = array();
            #to collect list of existing book id
            $existing_books = array(); 
            $unassigned_c_result =  array();
            $contri_create_result = ""; 
            if($csv_id){ #if record id is available
            if (($handle = fopen ( $request->csv_file, 'r' )) !== FALSE) {
                fgetcsv($handle, 0, ",");# abandon the first record 
                 try{
                    $values_to_update = array(); #update found books
                    $bookcount = 0; # get count of books already existing in db
                    while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                        if (!empty($data[0])) { #ignore blank lines 
                            #checking the format of the csv
                            if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12]) && isset($data[13]) && isset($data[14]) && isset($data[15]) && isset($data[16]) && isset($data[17]) && isset($data[18]) && isset($data[19]) && isset($data[20]) && isset($data[21]) && isset($data[22])){
                                if(!strtotime(trim($data[0])) && !is_numeric(trim($data[0]))){$column_00 = true;}
                                if(preg_match("/<[^<]+>/",$data[1],$m)==1){$column_01 =true;}
                                #skip2
                                if(is_numeric(trim($data[3])) || empty(trim($data[3]))){$column_03 =true;}
                                if(!is_numeric(trim($data[4])) && !strtotime(trim($data[4]))){$column_04 =true;}
                                if(!is_numeric(trim($data[5])) && !strtotime(trim($data[5]))){$column_05 =true;}
                                if(!is_numeric(trim($data[6])) && !strtotime(trim($data[6]))){$column_06 =true;}
                                if(!is_numeric(trim($data[7])) && !strtotime(trim($data[7]))){$column_07 =true;}
                                if(!is_numeric(trim($data[8])) && !strtotime(trim($data[8]))){$column_08 =true;}
                                if(!is_numeric(trim($data[9])) && !strtotime(trim($data[9]))){$column_09 =true;}
                                if(!is_numeric(trim($data[10])) && !strtotime(trim($data[10]))){$column_10 =true;}
                                if(!is_numeric(trim($data[11])) && !strtotime(trim($data[11]))){$column_11 =true;}
                                if(!is_numeric(trim($data[12])) && !strtotime(trim($data[12]))){$column_12 =true;}
                                if(!is_numeric(trim($data[13])) && !strtotime(trim($data[13]))){$column_13 =true;}
                                if(strtotime(trim($data[14])) || empty(trim($data[14]))){$column_14 =true;}
                                if(strtotime(trim($data[15])) || empty(trim($data[15]))){$column_15 =true;}
                                if(!strtotime(trim($data[16])) && !empty(trim($data[16])) && !is_numeric(trim($data[16]))){$column_16 =true;}
                                if(is_numeric(trim($data[17]))){$column_17 =true;}
                                #skip 18
                                if((!is_numeric(trim($data[19])) && !strtotime(trim($data[19])))|| empty($trim($data[19]))){$column_19 = true;}
                                if(is_numeric(trim($data[20]))|| empty(trim($data[20]))){$column_20 = true;}
                                if(!is_numeric(trim($data[21]))|| empty(trim($data[21]))){$column_21 = true;}
                                if(!is_numeric(trim($data[22]))|| empty(trim($data[22]))){$column_22 = true;}
                            }
                            if(!isset($column_00) || !isset($column_01) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_08) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_14) || !isset($column_15) || !isset($column_16) || !isset($column_17) || !isset($column_19) || !isset($column_20) || !isset($column_21) || !isset($column_22)){
                                return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                            }
                            ##get the list of books with same name, 
                            try{
                                $checkname = DB::table('book_details')
                               ->where(function($q) use ($data) {
                                    #same book title
                                    $q->where('book_title', '=', trim($data [0]) );
                                    #same isbn 
                                    if(!empty(trim($data [3]))){
                                        $q->orwhere('isbn_code', '=', trim($data [3]) );
                                    }
                                    #same diamond_item_code 
                                    if(!empty(trim($data [4]))){
                                        $q->orwhere('diamond_item_code', '=', trim($data [4]) );
                                    }
                                    #same upc 
                                    if(!empty(trim($data [20]))){
                                        $q->orwhere('upc', '=', trim($data [20]) );
                                    }
                                    #same asin_code 
                                    if(!empty(trim($data [21]))){
                                        $q->orwhere('asin_code', '=', trim($data [21]) );
                                    }
                                    #same itune_code 
                                    if(!empty(trim($data [22]))){
                                        $q->orwhere('itune_code', '=',trim($data [22]) );
                                    }
                                })
                                ->where('added_by',$id)
                                ->first();
                            }catch (\Exception $e) {
                                return $e->getMessage();
                            }
                            #if the name does not exist, then only save it.
                            if (is_null($checkname)){
                                $csv_data = new Book_detail();
                                $csv_data->book_title = trim($data [0]);
                                $csv_data->vendor = trim($data [16]);
                                $csv_data->print_price = trim($data [17]);
                                $csv_data->added_by = $id;
                                $csv_data->diamond_item_code = trim($data [4]); 
                                $csv_data->isbn_code = trim($data [3]);
                                $csv_data->book_cover_type = 1;
                                $csv_data->book_cover = trim($data [18]);
                                $csv_data->csv_id = $csv_id;
                                $csv_data->bio = $data [2];
                                $csv_data->qb_classes = trim($data [19]);
                                $csv_data->upc = trim($data [20]);
                                $csv_data->asin_code = trim($data [21]);
                                $csv_data->itune_code = trim($data [22]);
                                $csv_data->save();
                                #make collection of the contributor's name
                                $contri_array = array(trim($data[5]),trim($data[6]),trim($data[7]),trim($data[8]),trim($data[9]),trim($data[10]),trim($data[11]),trim($data[12]),trim($data[13]),trim($data[14]));
                                #if array has same names in it
                                $contri_array = array_unique(array_values($contri_array));
                                #use their names to find existing contributors in db
                                try{
                                    $users_existing = DB::table('users')
                                    ->whereIn('name', $contri_array)
                                    ->where('added_by', $id)
                                    ->pluck('name')->toarray();
                                }catch(\Exception $e){
                                    return $e->getMessage()."Line no users_existing ".$e->getLine();
                                }
                                #find out the non existing ones using the difference
                                $users_not_existing = array_diff(array_filter($contri_array), array_filter($users_existing));
                                #insert into db for those who are not existing in db
                                if(!empty($users_not_existing)){
                                    foreach($users_not_existing as $user_to_create){
                                        #check if there is space in name
                                        if (count(explode(' ', $user_to_create)) > 1) {
                                            $frst=$user_to_create;
                                            $first=explode(' ', $frst);
                                            $first_name=$first[0];
                                            $last_name1=ltrim($frst, $first_name.' ');
                                            if (count(explode(' ', $last_name1)) > 1) {
                                                $midl=$last_name1;
                                                $middle=explode(' ', $midl);
                                                $middle_name=$middle[0];
                                                $last_name=ltrim($midl, $middle_name.' ');
                                            }else{
                                                $middle_name = '';
                                                $last_name = $last_name1;
                                            }
                                        }else{
                                            $first_name=$user_to_create;
                                            $middle_name = '';
                                            $last_name='';
                                        }
                                        try{
                                            $contri_create_result = User::create([
                                                'name'=>$user_to_create,
                                                'first_name'=>$first_name,
                                                'middle_name'=>$middle_name,
                                                'last_name'=>$last_name,
                                                'added_by'=>$id,
                                                'login_type'=>2,
                                                ]);
                                        }catch (\Exception $e) {
                                            return getMessage()."Line no contri_create_result".$e->getLine();
                                        }    
                                    }
                                }
                                #get the ids of the contributor which are sent via csv, as now all of them are in db.
                                try{
                                    $contri_ids = DB::table('users')
                                    ->whereIn('name', array_filter($contri_array))
                                    ->where('added_by',$id)
                                    ->pluck('id')->toarray();
                                }catch(\Exception $e){
                                    return $e->getMessage()."Line no contri_ids ".$e->getLine();
                                }
                                #use the book title to find books in db
                                try{
                                $latestBook = DB::table('book_details')
                                ->select('id')
                                ->where('book_title', trim($data[0]))
                                ->where('added_by',$id)
                                ->orderBy('id', 'DESC')
                                ->first();
                                }catch(\Exception $e){
                                    return $e->getMessage()."Line no latestBook  ".$e->getLine();
                                }
                                foreach($latestBook as $object){
                                    $book_id = $object;
                                }
                                #checking if the contributor is already assigned to the book
                                try{
                                    $assigned_contributor = DB::table('assign_contributors')
                                    ->whereIn('contributor_id', $contri_ids)
                                    ->where([
                                        'book_id'=> $book_id,
                                        'role_id' => 1,
                                        ])
                                    ->pluck('contributor_id')
                                    ->toarray();
                                }catch(\Exception $e){
                                    return $e->getMessage();
                                }
                                $unassigned_contributors = array_diff($contri_ids,$assigned_contributor);
                                foreach($unassigned_contributors as $unassigned_c){
                                    try{
                                    $unassigned_c_result = Assign_contributor::create([
                                    'book_id' => $book_id,
                                    'contributor_id' => $unassigned_c,
                                    'role_id' => 1,
                                    ]);
                                    }catch (\Exception $e) {
                                    return $e->getMessage()."Line no unassigned_c_result".$e->getLine();
                                    }    
                                }
                            }
                            #if there is a book already existing in the system
                            else{
                                $bookcount++;
                                #get the id of the book from name
                                try{
                                    $book_ids = DB::table('book_details')
                                    ->select('id')
                                    ->where('book_title', trim($data[0]))
                                    ->where('added_by',$id)
                                    ->orderBy('id', 'DESC')
                                    ->get()->toArray();

                                }catch(\Exception $e){
                                    return $e->getMessage()." Line no (csv upload )".$e->getLine(); 
                                }
                                $existing_books = '';
                                foreach($book_ids as $object){
                                    $object = get_object_vars($object);
                                    $existing_books = $object['id'];
                                }
                                ##if there is a book already existing with matching codes instead of matching title
                                if(empty($existing_books)){
                                    #finding out which code is matching and skipping that code but still adding the book we found with different name
                                    $checkname = json_decode(json_encode($checkname), true);
                                    $_exist_book = [
                                    'asin_code'=>$checkname['asin_code'],
                                    'diamond_item_code'=>$checkname['diamond_item_code'],
                                    'itune_code'=>$checkname['itune_code'],
                                    'isbn_code'=>$checkname['isbn_code'],
                                    'qb_classes'=>$checkname['qb_classes'],
                                    'upc'=>$checkname['upc'],
                                    ];
                                    $_csv_book = [
                                    'asin_code'=>trim($data[21]),
                                    'diamond_item_code'=>trim($data[4]),
                                    'itune_code'=>trim($data[22]),
                                    'isbn_code'=>trim($data[3]),
                                    'qb_classes'=>trim($data[19]),
                                    'upc'=>trim($data[20]),
                                    ];
                                    ##collecting codes which are same
                                    $same_codes = array_intersect($_exist_book, $_csv_book);
                                    $codes_to_add = array_diff($_csv_book,$same_codes);
                                    $other_parts = ['book_title' => trim($data[0]),    
                                    'book_cover' => trim($data[18]),
                                    'bio' => $data[2],
                                    'book_cover_type'=> 1,
                                    'added_by'=>$id,
                                    'print_price'=>trim($data[17]),
                                    'manage_pay_upper_limit'=>100,
                                    'oni_entertainment'=> 2,
                                    'oni_entertainment_notes'=> null,
                                    'vendor' => trim($data[16]),
                                    ];
                                    #adding rest of the value
                                    $data_to_add = $codes_to_add  + $other_parts;
                                    #add the new array to create new array
                                    try{
                                        $add_book = Book_detail::create($data_to_add);
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    #make collection of the contributor's name
                                    $contri_array = array(trim($data[5]),trim($data[6]),trim($data[7]),trim($data[8]),trim($data[9]),trim($data[10]),trim($data[11]),trim($data[12]),trim($data[13]),trim($data[14]));
                                    #if array has same names in it
                                    $contri_array=array_unique(array_values($contri_array));
                                    #use their names to find existing contributors in db
                                    try{
                                        $users_existing = DB::table('users')
                                        ->whereIn('name', $contri_array)
                                        ->where('added_by', $id)
                                        ->pluck('name')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage()."Line no users_existing ".$e->getLine();
                                    }
                                    #find out the non existing ones using the difference
                                    $users_not_existing = array_diff(array_filter($contri_array), array_filter($users_existing));
                                    #insert into db for those who are not existing in db
                                    if(!empty($users_not_existing)){
                                        foreach($users_not_existing as $user_to_create){
                                            #check if there is space in name
                                            if (count(explode(' ', $user_to_create)) > 1) {
                                                $frst=$user_to_create;
                                                $first=explode(' ', $frst);
                                                $first_name=$first[0];
                                                $last_name1=ltrim($frst, $first_name.' ');
                                                if (count(explode(' ', $last_name1)) > 1) {
                                                    $midl=$last_name1;
                                                    $middle=explode(' ', $midl);
                                                    $middle_name=$middle[0];
                                                    $last_name=ltrim($midl,$middle_name.' ');
                                                }else{
                                                    $middle_name = '';
                                                    $last_name = $last_name1;
                                                }
                                            }else{
                                                $first_name=$user_to_create;
                                                $middle_name = '';
                                                $last_name='';
                                            }
                                            try{
                                                $contri_create_result = User::create([
                                                'name'=>$user_to_create,
                                                'first_name'=>$first_name,
                                                'middle_name'=>$middle_name,
                                                'last_name'=>$last_name,
                                                'added_by'=>$id,
                                                'login_type'=>2,
                                                ]);
                                            }catch (\Exception $e) {
                                                return getMessage()."Line no contri_create_result".$e->getLine();
                                            }
                                        }
                                    }
                                    #get the ids of the contributor which are sent via csv, as now all of them are in db.
                                    try{
                                        $contri_ids = DB::table('users')
                                        ->whereIn('name', array_filter($contri_array))
                                        ->where('added_by',$id)
                                        ->pluck('id')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage()."Line no contri_ids ".$e->getLine();
                                    }
                                    #use the book title to find books in db
                                    try{
                                        $latestBook = DB::table('book_details')
                                        ->select('id')
                                        ->where('book_title', trim($data[0]))
                                        ->where('added_by',$id)
                                        ->orderBy('id', 'DESC')
                                        ->first();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    foreach($latestBook as $object){
                                        $book_id = $object;
                                    }
                                    #checking if the contributor is already assigned to the book
                                    try{
                                        $assigned_contributor = DB::table('assign_contributors')
                                        ->whereIn('contributor_id', $contri_ids)
                                        ->where([
                                            'book_id'=> $book_id,
                                            'role_id' => 1,
                                            ])
                                        ->pluck('contributor_id')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    $unassigned_contributors = array_diff($contri_ids,$assigned_contributor);
                                    foreach($unassigned_contributors as $unassigned_c){
                                        try{
                                            $unassigned_c_result = Assign_contributor::create(['book_id' => $book_id,'contributor_id' => $unassigned_c,'role_id' => 1,]);
                                        }catch (\Exception $e) {
                                            return $e->getMessage()."Line no unassigned_c_result".$e->getLine();
                                        }
                                    }
                                }else{
                                    ##if there is a book with same name then update the book
                                    #get the list of contributors in array
                                    $contri_array = array(trim($data[5]),trim($data[6]),trim($data[7]),trim($data[8]),trim($data[9]),trim($data[10]),trim($data[11]),trim($data[12]),trim($data[13]),trim($data[14]));
                                    #if there are repeating names in the csv*
                                    $contri_array =array_unique(array_values($contri_array));
                                    #use their names to find existing contributors in db
                                    try{
                                        $users_existing = DB::table('users')
                                        ->whereIn('name', $contri_array)
                                        ->where('added_by', $id)
                                        ->pluck('name')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage()."Line no users_existing ".$e->getLine();
                                    }
                                    #find out the non existing ones using the difference 
                                    $users_not_existing = array_diff(array_filter($contri_array), array_filter($users_existing));
                                    #insert into db for those who are not existing in db
                                    if(!empty($users_not_existing)){
                                        foreach($users_not_existing as $user_to_create){
                                            #check if there is space in name
                                            if (count(explode(' ', $user_to_create)) > 1) {
                                                $frst=$user_to_create;
                                                $first=explode(' ', $frst);
                                                $first_name=$first[0];
                                                $last_name1=ltrim($frst, $first_name.' ');
                                                if (count(explode(' ', $last_name1)) > 1) {
                                                    $midl=$last_name1;
                                                    $middle=explode(' ', $midl);
                                                    $middle_name=$middle[0];
                                                    $last_name=ltrim($midl,$middle_name.' ');
                                                }else{
                                                    $middle_name = '';
                                                    $last_name = $last_name1;
                                                }
                                            }else{
                                                $first_name=$user_to_create;
                                                $middle_name = '';
                                                $last_name='';
                                            }
                                            try{
                                            $contri_create_result = User::create([
                                                'name'=>$user_to_create,
                                                'first_name'=>$first_name,
                                                'middle_name'=>$middle_name,
                                                'last_name'=>$last_name,
                                                'added_by'=>$id,
                                                'login_type'=>2,
                                                ]);
                                            }catch (\Exception $e) {
                                                return $e->getMessage()."Line no contri_create_result".$e->getLine();
                                            }    
                                        }
                                    }
                                    #get the ids of the contributor which are sent via csv, as now all of them are in db.
                                    try{
                                        $contri_ids = DB::table('users')
                                        ->whereIn('name', array_filter($contri_array))
                                        ->where('added_by',$id)
                                        ->pluck('id')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage()."Line no contri_ids ".$e->getLine();
                                    }
                                    #checking if the contributor is already assigned to the book
                                    try{
                                        $assigned_contributor = DB::table('assign_contributors')
                                        ->whereIn('contributor_id', $contri_ids)
                                        ->where([
                                            'book_id'=> $existing_books,
                                            'role_id' => 1,
                                            ])
                                        ->pluck('contributor_id')->toarray();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    #getting the list of contributors who are not assigned
                                    $unassigned_contributors = array_diff($contri_ids,$assigned_contributor);
                                    #assigning the contributors to the books
                                    foreach($unassigned_contributors as $unassigned_c){
                                        try{
                                        $unassigned_c_result = Assign_contributor::create([
                                        'book_id' => $existing_books,
                                        'contributor_id' => $unassigned_c,
                                        'role_id' => 1,
                                        ]);
                                        }catch (\Exception $e) {
                                        return $e->getMessage()."Line no unassigned_c_result".$e->getLine();
                                        }    
                                    }
                                    ##updating the rest of the values
                                    $book_title = trim($data [0]);
                                    $bio = trim($data [2]);
                                    $print_price = trim($data [17]);
                                    $diamond_item_code = trim($data [4]); 
                                    $isbn_code = trim($data [3]);
                                    $book_cover = trim($data [18]);
                                    $vendor = trim($data [16]);
                                    $qb_classes = trim($data [19]);
                                    $upc = trim($data [20]);
                                    $asin_code = trim($data [21]);
                                    $itune_code = trim($data [22]);
                                    $book_cover_type = 1;
                                    if(!empty($book_title)){
                                        $values_to_update =   ['book_title'=> $book_title];
                                    }
                                    if(!empty($bio)){
                                        $values_to_update +=   ['bio'=> $bio];
                                    }
                                    if(!empty($print_price)){
                                        $values_to_update +=  ['print_price'=> $print_price];
                                    }
                                    if(!empty($diamond_item_code)){
                                        $values_to_update +=   ['diamond_item_code'=> $diamond_item_code];
                                    }
                                    if(!empty($isbn_code)){
                                        $values_to_update +=   ['isbn_code'=> $isbn_code];
                                    }
                                    if(!empty($book_cover)){
                                        $values_to_update +=   ['book_cover'=> $book_cover,'book_cover_type'=>$book_cover_type];
                                    }
                                    if(!empty($vendor)){
                                        $values_to_update +=   ['vendor'=> $vendor];
                                    }
                                    if(!empty($qb_classes)){
                                        $values_to_update +=   ['qb_classes'=> $qb_classes];
                                    }
                                    if(!empty($upc)){
                                        $values_to_update +=   ['upc'=> $upc];
                                    }
                                    if(!empty($asin_code)){
                                        $values_to_update +=   ['asin_code'=> $asin_code];
                                    }
                                    if(!empty($itune_code)){
                                        $values_to_update +=   ['itune_code'=> $itune_code];
                                    }
                                    ##single book_id
                                    try{
                                        $single_book_id = DB::table('book_details')
                                        ->where('book_title',trim($data [0]))
                                        ->where('added_by',$id)
                                        ->value('id');
                                    }catch(\Exception $e){
                                        return $e->getMessage()." Line no (csv upload )".$e->getLine(); 
                                    }
                                    try{
                                    $update_users = DB::table('book_details')
                                    ->where('id', $single_book_id)
                                    ->update($values_to_update);
                                    }catch (\Exception $e) {
                                      return $e->getMessage()." line number csv updation  :".$e->getLine();
                                    }
                                }
                            }#checking book title ends here
                        }else{continue;}#skip empty row ends here
                    }#ends while
                }catch (\Exception $e) {
                  return $e->getMessage(). " Line no endcatch".$e->getLine();
                }
                fclose ( $handle );
                if($bookcount > 1){
                return redirect()->back()->with("message", $bookcount." books details Updated Successfully");
                }
                if($bookcount == 1){
                return redirect()->back()->with("message", $bookcount." book's details Updated Successfully");
                }
                return redirect()->back()->with("message","CSV Details Added Successfully");
            }else{
                return redirect()->back()->with("error","Please Upload Again");
            } #if not csv, condition
            } # record id if condition
        }#if products csv ends here

    /*
    Add expenses Report
    */
        if ($request->has('csv_expenses')) {
            #validate required field
            $validator = Validator::make($request->all(), [ 'csv_expenses'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }
            $csv_name=$request->csv_expenses->getClientOriginalName();
            #checking extensions
            $myextension = $request->csv_expenses->getClientOriginalExtension();
            $csvarray = array("csv");
            #if not csv, send error 
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }
            #create unique csv id in csv_records table
            $csv_token = "book_expenses_simple_".date('Y-m-d H:i:s');
            #inserting in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }
            #getting it from db and storing in into the variable
            if($csv_data == 1){
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }
            #logged in user
            $id = Auth::user()->id;
            #uploading code added
            if($csv_id){
                if (($handle = fopen ( $request->csv_expenses, 'r' )) !== FALSE) {
                fgetcsv($handle, 0, ","); #the first record 
                try{
                    $books_not_found = array(); #array to collect missing books
                    while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                        if (array(null) !== $data) {# ignore blank lines
                            #checking the valid format of csbd
                            if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3])){
                                if(!empty(trim($data[0]))){
                                    $column_00 = true;
                                }
                                if(!is_numeric(trim($data[1])) && !empty(trim($data[1])) && !strtotime(trim($data[1]))){
                                    $column_01 = true;
                                }
                                if(is_numeric(trim($data[2]))){
                                    $column_02 = true;
                                }
                                if(strtotime(trim($data[3]))){
                                    $column_03 = true;
                                }
                            }
                            if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03)){
                                return redirect()->back()->with("error", "Invalid Format uploaded. Please check the sample csv for the same.");
                            }
                            $dates = $data[3];#dates from csv
                            $diamond_code  = $data[0]; #diamond code
                            $book_name =  $data[1]; #book title
                            $expenses =  $data[2]; #expense value
                            $memo = ''; #no memo field, so empty memo
                            #if no value of expense found, make it 0 so that script keeps on adding the same books expense continuosly
                            if(empty($expenses)){$expenses = 0;}
                            #change format
                            $dates = date("Y-m-d", strtotime($dates)); 
                            #get year from csv
                            $csv_year = date('Y', strtotime($dates)); 
                            #get quarter from csv dates
                            $csv_quarter = ceil(date('n', strtotime($dates))/3);
                            #memo for the amounts added
                            $memo_values = $memo.' $'.$expenses.';';
                            if(!empty(trim($diamond_code))){
                               #get the book id if existing in db by matching the names from csv and db
                                try{
                                    $_existing_books =  DB::table('book_details')
                                    ->where(['diamond_item_code'=>trim($diamond_code),    
                                    'added_by'=>$id])
                                    ->where('diamond_item_code', '<>', '', 'and')    
                                    ->where('diamond_item_code', '<>', '') #is not empty
                                    ->pluck('id');
                                }catch(\Exception $e){
                                    return $e->getMessage()." Line no existing_books ".$e->getLine();
                                }
                                $_existing_books = json_decode(json_encode($_existing_books), true);
                                #if still id not found, use isbn_code_10 match with ISBN Physical 13
                                if(empty($_existing_books)){
                                    try{
                                        $_existing_books =  DB::table('book_details')
                                        ->where(['asin_code'=>trim($diamond_code),    
                                        'added_by'=>$id])
                                        ->where('asin_code', '<>', '', 'and')    
                                        ->where('asin_code', '<>', '') #is not empty
                                        ->pluck('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                    }
                                    $_existing_books = json_decode(json_encode($_existing_books), true); 
                                }
                            }
                            #if book is found
                            if(!empty($_existing_books)){
                                ###check if this id is merged or not
                                #sub id
                                try{
                                    $book_merge_status = DB::Table('merged_books')->wherein('sub_book',$_existing_books)->value('id');
                                }catch(\Exception $e){
                                    return $e->getMessage().' Error on expense merge';
                                }
                                if(!empty($book_merge_status)){
                                    #get main id
                                    try{
                                        $main_merge_book_id = DB::Table('merged_books')->where('id',$book_merge_status)->pluck('main_book');
                                    }catch(\Exception $e){
                                        return $e->getMessage().' Error on expense merge2';
                                    }
                                    $main_merge_book_id = json_decode(json_encode($main_merge_book_id), true);

                                    $unique_ids = $main_merge_book_id;
                                }else{
                                    #if no sub id available, put 
                                    $unique_ids = $_existing_books;
                                }
                                #years related to db column of book_expenses
                                if ($csv_quarter == 1){
                                    $get_quarter = 'first_quarter'; #quarter column name
                                    $get_memo = 'first_memo'; #memo column name
                                }
                                if ($csv_quarter == 2){
                                    $get_quarter = 'second_quarter'; #quarter column name
                                    $get_memo = 'second_memo'; #memo column name
                                }
                                if ($csv_quarter == 3){
                                    $get_quarter = 'third_quarter'; #quarter column name
                                    $get_memo = 'third_memo'; #memo column name
                                }
                                if ($csv_quarter == 4){
                                    $get_quarter = 'fourth_quarter'; #quarter column name
                                    $get_memo = 'fourth_memo'; #memo column name
                                }
                                #check if the id extracted exists in the book_expenses table
                                try{
                                    $_existing_expenses_id =  DB::table('book_expenses')
                                    ->whereIn('book_id',$unique_ids)
                                    ->where('year',$csv_year)
                                    ->pluck('id');
                                }catch(\Exception $e){
                                    return $e->getMessage()." Line no existing_expenses ".$e->getLine();
                                }
                                #removing the object std classes
                                $_existing_expenses = json_decode(json_encode($_existing_expenses_id), true);
                               
                                #if the expense already exists for that book
                                if(!empty($_existing_expenses)){
                                    #get the value for the uploaded quarter and add the csv quarter value in it
                                    try{
                                        $get_expenses_val = DB::table('book_expenses')
                                                    ->select($get_quarter,$get_memo)
                                                    ->where(['year'=>$csv_year,])
                                                    ->whereIn('book_id',$unique_ids)
                                                    ->whereIn('id',$_existing_expenses)
                                                    ->get();
                                    }catch(\Exception $e){
                                        return  $e->getMessage()." Line no get_expenses_val ".$e->getLine();
                                    }
                                    $get_expenses = json_decode(json_encode($get_expenses_val), true);
                                    #adding the existing quarter value with new values from csv
                                    $new_quarter = array();
                                    foreach($get_expenses_val as $_exp){
                                    $_exp = get_object_vars($_exp);
                                    $new_quarter['quarter'] = $_exp[$get_quarter]+$expenses;
                                    $new_quarter['memo'] = $_exp[$get_memo].';'.$memo_values;
                                    }
                                    #checking the same combination that we are going to upload is already existing in db. if not then do no update that part.
                                    try{
                                        $check_expenses = DB::table('book_expenses')
                                        ->where(['year'=>$csv_year,])
                                        ->whereIn('book_id',$unique_ids)
                                        ->whereIn('id',$_existing_expenses)
                                        ->where($get_quarter,$new_quarter['quarter'])
                                        ->first();
                                    }catch(\Exception $e){
                                        return $e->getMessage()." Line no check_expenses ".$e->getLine();
                                    }
                                    #no same values existing, hence updating
                                    if(empty($check_expenses)){
                                        #updating the values of expenses
                                        try{
                                            $update_expense = DB::table('book_expenses')
                                            ->where(['year'=>$csv_year])
                                            ->whereIn('id',$_existing_expenses)
                                            ->whereIn('book_id',$unique_ids)
                                            ->update([$get_quarter=>$new_quarter['quarter'],$get_memo => $new_quarter['memo']]);
                                        }catch(\Exception $e){
                                            return $e->getMessage()." Line no update_expense ".$e->getLine();
                                        }
                                    }
                                }
                                #if the book is not found in add_expense, means we have to create new record
                                else{
                                    #array used for creation
                                    $value_to_create=[
                                        'book_id'=>$unique_ids[0],
                                        'diamond_code'=>$diamond_code,
                                        'year'=>$csv_year,
                                        $get_memo=>$memo_values,
                                        $get_quarter => $expenses
                                    ];
                                    try{
                                        $insert_expenses = Book_expense::create($value_to_create);
                                    }catch(\Exception $e){
                                        return $e->getMessage()." Line no $insert_expenses ".$e->getLine();
                                    }
                                }#create new expense record
                            }#if book id is found in the db
                            else{
                                ##collecting the book which is not found 
                                $books_not_found[]= [
                                'diamond_item_code'=>$diamond_code,
                                'book_title'=>$book_name,
                                'action'=>'Please add the diamond code'
                                ];
                            }
                        }#if blank lines, skip rows ends 
                    }#while ends
                    $books_not_found = array_unique($books_not_found, SORT_REGULAR);
                }catch(\Exception $e){
                    return  $e->getMessage()." Line no csv_expenses ".$e->getLine();
                }
                fclose ($handle);
                if(empty($books_not_found)){
                    return redirect()->back()->with("message","CSV Details Added Successfully");
                }else{
                    $plateform = 'simple_expense';
                    $uploaded_books = '';
                    return view('books_not_found', compact('books_not_found','plateform','csv_name','uploaded_books'));
                }
                }else{
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }#csv if condn ends
            }#if csv id available condition ends
            else{
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }#expenses Report ends*/

    /*
    Add weekly Csv :: Diamond report
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='Diamond') {

            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #required validation for the field
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            #checking the extension is csv
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            #create unique csv id in csv_records table
            $csv_token = "book_financial_".date('Y-m-d H:i:s');#unique token
            
            #inserting in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){

                #getting from db
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                $values_to_update = array();
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                $book_id_avail = ""; //for getting the book id

                #abandon the first 5 row s
                fgetcsv($handle);
                fgetcsv($handle);
                fgetcsv($handle);
                fgetcsv($handle);
                fgetcsv($handle);
              
                try{
                    $current_rows= array(); #new created array 
                    $array_concat_strings=array();#concat the new values in it
                    $increment=0; 
                    $increment2=0;
                    # vars for foreach
                    $var_book_id = "";
                    $var_diamond_code = "";
                    $var_description = "";
                    $var_market = "";
                    $var_po_cost = "";
                    $var_qty_sold = "";
                    $var_total_cost = "";
                    $var_isbn_code_csv = "";
                    $var_concat = "";
                    $concat_rows1="";
                    $concat_rows_arr = array();  
                    $desc_var="";
                    $market_name = "";
                    $dates = "";
                    $books_not_found = array();

                    #checking how many are existing in db alreadt
                    $already_existing = array();
                    while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                        if (array(null) !== $data) {#ignore blank lines
                            #if the row contains the sales details, skip that row
                            if(empty(trim($data[0])) && empty(trim($data[1])) && !empty(trim($data[6])) && !empty(trim($data[7])) ){continue;}

                            #if first column = po skip that row
                            if(trim($data[0]) == 'P/O #'){continue;}

                            #if the row contains the only the market name,we will match the column with a standard item code and check if that column has any numbers in it else, skip that row
                            if (1 !== preg_match('#[0-9]#',trim($data[0])) &&  !empty(trim($data[0])) && strpos( trim($data[0]), "ITEM CODE" ) === false && empty(trim($data[1])) && empty(trim($data[2])) && empty(trim($data[3])) &&  empty(trim($data[4]))  && empty(trim($data[5]))  && empty(trim($data[6]))  && empty(trim($data[7])) ) {$market_name = trim($data[0]);continue;}

                            #getting the invoicing week and skipping that row also **/
                            if ( strpos( trim($data[0]), "Invoicing Week" ) !== false  && empty(trim( $data[1])) && empty(trim( $data[2])) && empty(trim($data[3])) && empty(trim($data[4]))  && empty(trim($data[5])) && empty(trim($data[6])) && empty(trim($data[7]))   ) {
                                $dates = substr(trim($data[0]), strpos(trim($data[0]), ":") + 1); continue;}

                            #to Skip the title row of the csv
                            if(strpos( trim($data[0]), "ITEM CODE" ) !== false ) {continue;}

                            #skip the end report sales values
                            if(empty(trim( $data[0])) && empty(trim( $data[1])) && !empty(trim( $data[2])) && ( !empty(trim($data[3])) || !empty(trim($data[4]))  ||!empty(trim($data[5])) ||!empty(trim($data[6])) ||!empty(trim($data[7]))  ) )   { continue; }

                            #add into array
                            $concat_val= array();

                            ##get the data in variables
                            #replacing the braces with -ve >> Total cost
                            $total_cost = $data[7];

                            if(strpos($total_cost, '(') !== false){
                                $total_cost = str_replace(array('(', ')'), '', $total_cost);
                                $total_cost = '-'.trim($total_cost);
                            }else{
                                $total_cost = $total_cost;
                            }

                            #replacing the comma >> Total cost
                            if(strpos($total_cost, ',') !== false){
                                $total_cost = str_replace(',', '', $total_cost);
                            }else{
                                $total_cost = $total_cost;
                            }

                            #replacing the dollar sign (if any)>> Total cost #replacing dollar
                            if(strpos($total_cost, '$') !== false){
                                $total_cost = str_replace('$', '', $total_cost);
                            }else{
                                $total_cost = $total_cost;
                            }

                            #replacing the %age >> qty_sold
                            $qty_sold = $data[6]; //units
                            if(strpos($qty_sold, '%') !== false){
                                $qty_sold = str_replace('%', '', $qty_sold);
                            }else{
                                $qty_sold = $qty_sold;
                            }

                            #replacing the bbraces >> qty_sold
                            if(strpos($qty_sold, '(') !== false){
                                $qty_sold = str_replace(array( '(', ')' ), '', $qty_sold);
                                $qty_sold = '-'.trim($qty_sold);
                            }else{
                                $qty_sold = $qty_sold;
                            }

                            #replacing the comma >> qty_sold
                            if(strpos($qty_sold, ',') !== false){
                                $qty_sold = str_replace(',', '', $qty_sold);
                            }else{
                                $qty_sold = $qty_sold;
                            }

                            #replacing the bbraces >> po_cost
                            $po_cost = $data[5]; //price
                            if(strpos($po_cost, '(') !== false){
                                $po_cost = str_replace(array( '(', ')' ), '', $po_cost);
                                $po_cost = '-'.trim($po_cost);
                            }else{
                                $po_cost = $po_cost;
                            }

                            #replacing the comma >> po_cost
                            if(strpos($po_cost, ',') !== false){
                                $po_cost = str_replace(',', '', $po_cost);
                            }else{
                                $po_cost = $po_cost;
                            }

                            $diamond_item_code_csv = $data[0]; #diamont item code 
                            $isbn_code_csv = $data[1];
                            $description = $data[2];
                            $desc_percentage = $data[4]; 
                            $unit_retails = $data[3]; 

                            #Removing spaces
                            if(!empty($market_name) ){
                                if (strpos($market_name, ' ') !== false) {
                                    $market_name= str_replace(' ', '_', $market_name);
                                }
                            }
                            
                            #Removing spaces _ends
                            #Getting week from and week to dates
                            #add $invoice_date instead of $dates if dates column is added
                            $week_from_1 = explode(" to ", $dates, 2);
                            $week_from = $week_from_1[0];
                            $week_from = date("Y-m-d", strtotime($week_from));
                            $week_to_1 = strpos($dates, " to ") + strlen(" to ");
                            $week_to = substr($dates, $week_to_1);
                            $week_to = date("Y-m-d", strtotime($week_to));

                            #getting the month of week from date
                            $month1 = date('m', strtotime($week_from));

                            #getting the quarter of week from date
                            $q1 = ceil($month1/3); 

                            #getting year of week from date
                            $y1 = date('Y', strtotime($week_from));

                            #getting the month of week to date
                            $month2 = date('m', strtotime($week_to));

                            #getting the quarter of week to date
                            $q2 = ceil($month2/3); 

                            #getting year of week to date
                            $y2 = date('Y', strtotime($week_to));

                            #comparing the quarter , the dates lie in different quarter, we will make the week to date to be adjusted in the week from quarter
                            if($q1!=$q2){
                                #last date of week from quarter
                                $last_day_q1 = date('Y-m-t', strtotime($y1 . '-' . (($q1  * 3)) . '-1'));
                                #finding difference between the dates and getting the days 
                                $date_diff = date_diff(date_create($last_day_q1),date_create($week_to));
                                #no. of days left from q1 quarter's last date 
                                $date_diff =  $date_diff->format("%a");
                                #if the days are less than or equal to 10, means the week to is 10 days (or less ) more than the last quarter's last day
                                if($date_diff <= 5){
                                $week_to = date('Y-m-d',(strtotime ( -$date_diff.'day',strtotime($week_to))));
                                }
                            }#ends if the quarters are not same condition
                            
                            #if weekly csv has ' E' or " J" after every diamond code, removing that
                            if (strpos($diamond_item_code_csv, ' ') !== false) {
                                $diamond_item_code_csv = substr($diamond_item_code_csv, 0, strpos($diamond_item_code_csv, ' '));
                            }
                            
                            #getting the book id match with ISBN Physical 13
                            if($isbn_code_csv){#if isbn code is available in csv*/
                                try{
                                $book_id_avail = DB::table('book_details')
                                ->where('isbn_code',$isbn_code_csv)
                                ->where('isbn_code', '<>', '', 'and') //is not null
                                ->where('isbn_code', '<>', '') //is not empty
                                ->where('added_by',$id)
                                ->value('id');
                                }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                }

                                #if no values comes out means no data matches for isbn, we check from ISBN 10
                                if(empty($book_id_avail)){
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code2',$isbn_code_csv)
                                    ->where('isbn_code2', '<>', '', 'and') //is not null
                                    ->where('isbn_code2', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');

                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }              
                                }

                                #if no values comes out means no data matches for isbn, we check from ISBN Digital 13
                                if(empty($book_id_avail)){
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code3',$isbn_code_csv)
                                    ->where('isbn_code3', '<>', '', 'and') //is not null
                                    ->where('isbn_code3', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                }

                                #if no values comes out means no data matches for isbn, we check from diamond code
                                if(empty($book_id_avail)){
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('diamond_item_code',$diamond_item_code_csv)
                                    ->where('diamond_item_code', '<>', '', 'and') //is not null
                                    ->where('diamond_item_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                }
                            }else{ 
                                #if isbn code is not available, we directly check from diamond_item_code
                                try{
                                $book_id_avail = DB::table('book_details')
                                ->where('diamond_item_code',$diamond_item_code_csv)
                                ->where('diamond_item_code', '<>', '', 'and') //is not null
                                ->where('diamond_item_code', '<>', '') //is not empty
                                ->where('added_by',$id)
                                ->value('id');
                                }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                }
                            }

                            #check if this id is merged or not
                            if(!empty($book_id_avail)){
                                #sub id
                                try{
                                $book_merge_status = DB::Table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                }catch(\Exception $e){
                                    return $e->getMessage().' book_merge_status diamond Line no.'.$e->getLine();
                                }

                                if(!empty($book_merge_status)){
                                #get main id
                                try{
                                $main_merge_book_id = DB::Table('merged_books')->where('id',$book_merge_status)->value('main_book');
                                }catch(\Exception $e){
                                return $e->getMessage().' main_merge_book_id diamond Line no.'.$e->getLine();
                                }
                                $book_id_avail = $main_merge_book_id;
                                }else{
                                    #if no sub id available, put 
                                    $book_id_avail = $book_id_avail;
                                }
                            }

                            #check if sales already exists or not
                            try{
                                $check_existing_sale = DB::table('book_financials')->where([
                                'isbn_code'=> $isbn_code_csv,
                                'diamond_item_code'=> $diamond_item_code_csv,
                                'po'=> $po_cost,
                                'total_cost' =>  $total_cost,
                                'market' => $market_name,
                                'csv_added_by' => $id,
                                'book_avail_id'=> $book_id_avail,
                                'qty_sold'=> $qty_sold,
                                'week_from'=> $week_from,
                                'week_to'=> $week_to,
                                'desc_percentage'=> $desc_percentage,
                                'unit_retails'=> $unit_retails,
                                ])->get();
                            }catch(\Exception $e){
                                return $e->getMessage();
                            }

                            #checking how many repeating sales are coming up
                            if(!$check_existing_sale->isEmpty()){
                                $already_existing[] = $check_existing_sale;
                            }

                            #there is no data found with same details already in db
                            if($check_existing_sale->isEmpty()){
                                ##concating the book description into one
                                if( empty(trim($data[0])) && empty(trim($data[1])) && !empty(trim($data[2])) && empty(trim($data[3])) && empty(trim($data[4])) && empty(trim($data[5])) && empty(trim($data[6])) && empty(trim($data[7])) ){
                                    $array_concat_strings[] = $data[2];
                                    $cc[$increment-1]['not'] = implode(' ', $array_concat_strings);
                                    $current_rows[$increment-1]['concat'] = implode(' ', $array_concat_strings);
                                    $books_not_found[$increment-1]['description2'] = implode(' ', $array_concat_strings);
                                    $increment2++;
                                }else{
                                    $increment++;
                                    unset($array_concat_strings); // $foo is gone
                                    $array_concat_strings = array();
                                    $current_rows []= [
                                    'book_id_avail' => $book_id_avail ,
                                    'diamond_item_code_csvs' => $diamond_item_code_csv,
                                    'description'=> $description,
                                    'po_cost'=> $po_cost,
                                    'qty_sold'=> $qty_sold,
                                    'total_cost'=> $total_cost,
                                    'isbn_code_csv'=> $isbn_code_csv,
                                    'csv_id' => $csv_id,
                                    'week_from'=> $week_from,
                                    'week_to'=> $week_to,
                                    'concat' => '',
                                    'market'=> $market_name,
                                    'row_number'=>$increment,
                                    'unit_retails'=>$unit_retails,
                                    'desc_percentage'=>$desc_percentage
                                    ];
                                    if(empty($book_id_avail) && (!empty($isbn_code_csv) || !empty($diamond_item_code_csv))){
                                        #to creation suggestion and action
                                        if(empty($isbn_code_csv) && empty($diamond_item_code_csv)){
                                        $action_to_do = "Please add any code in the csv matching with system for this book.";
                                        }else if(!empty($isbn_code_csv) && empty($diamond_item_code_csv)){
                                            $action_to_do = "Please add or edit book with this isbn code.";
                                        }else if(empty($isbn_code_csv) && !empty($diamond_item_code_csv)){
                                            $action_to_do = "Please add or edit book with this diamond code.";
                                        }else{
                                            $action_to_do = "Please add or edit book with one or all these provided codes.";
                                        }
                                        #books not found array    
                                        $books_not_found[]= [
                                        'title'=> $description,
                                        'isbn_code'=> $isbn_code_csv,
                                        'diamond_code'=>$diamond_item_code_csv,
                                        'description2'=>'',
                                        'action_to_do'=>$action_to_do
                                        ]; 
                                    }
                                }
                            }
                        }/* skip blank lines*/
                    }/* ending while*/

                    $books_notfound = array();
                    foreach($books_not_found as $allbooks){
                        if(isset($allbooks['title'])){
                            $title = $allbooks['title'].' '.$allbooks['description2'];
                            if (strpos($title, '(c:') !== false) {
                                $title = strstr($title, '(c:', true);
                            }
                            if (strpos($title, '(C:') !== false) {
                                $title = strstr($title, '(C:', true);
                            }
                            if (strpos($title, '(C :') !== false) {
                                $title = strstr($title, '(C :', true);
                            }
                            if (strpos($title, '(MR)') !== false) {
                                $title = strstr($title, '(MR)', true);
                            }
                            if (strpos($title, '(M R)') !== false) {
                                $title = strstr($title, '(M R)', true);
                            }
                             $books_notfound[] = [
                            'title'=>$title,
                            'isbn_code' =>$allbooks['isbn_code'],
                            'diamond_item_code'=>$allbooks['diamond_code'],
                            'action_to_do'=>$action_to_do
                            ];
                        }
                    }

                    #collecting only those arrays who has got the book_ids else, skip them
                    $current_values = array();
                    if(!empty($current_rows)){
                        foreach($current_rows as $key=>$value){   
                            if(!empty($value['book_id_avail'])){
                                $current_values[]  = array(
                                'book_id_avail'=>$value['book_id_avail'],
                                'diamond_item_code_csvs'=>$value['diamond_item_code_csvs'],
                                'isbn_code_csv'=>$value['isbn_code_csv'],
                                'po_cost'=>$value['po_cost'],
                                'total_cost'=>$value['total_cost'],
                                'market'=>$value['market'],
                                'qty_sold'=>$value['qty_sold'],
                                'week_from'=>$value['week_from'],
                                'week_to'=>$value['week_to'],
                                'description'=>$value['description'],
                                'concat'=>$value['concat'],
                                'csv_id'=>$value['csv_id'],
                                'desc_percentage'=>$value['desc_percentage'],
                                'unit_retails'=>$value['unit_retails']
                                );
                            }   
                        }
                    }

                    #unique books which are not found
                    $books_notfound = array_unique($books_notfound, SORT_REGULAR);

                    #no.of sales which are found in the db
                    $already_existing_count = count($already_existing);

                    #Count of books that are uploaded
                    $uploaded_books = count($current_values);
                    $plateform = 'diamond';
                    if(!empty($current_values)){
                        foreach ($current_values as $csv_rows) {  
                        $csv_data = new Book_financial();
                        $csv_data->isbn_code = $csv_rows['isbn_code_csv'];
                        $csv_data->diamond_item_code = $csv_rows['diamond_item_code_csvs'];
                        $csv_data->po = $csv_rows['po_cost'];
                        $csv_data->total_cost =  $csv_rows['total_cost'];
                        $csv_data->market =$csv_rows['market'];
                        $csv_data->csv_added_by = $id;
                        $csv_data->book_avail_id = $csv_rows['book_id_avail'];
                        $csv_data->qty_sold = $csv_rows['qty_sold'];
                        $csv_data->week_from = $csv_rows['week_from'];
                        $csv_data->week_to = $csv_rows['week_to'];
                        $csv_data->description = $csv_rows['description']." ".$csv_rows['concat'];
                        $csv_data->csv_id = $csv_rows['csv_id'];
                        $csv_data->desc_percentage = $csv_rows['desc_percentage'];
                        $csv_data->unit_retails = $csv_rows['unit_retails'];
                        $csv_data->save();
                        } 
                    }
                    }catch (\Exception $e) {
                      return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );  
                    }
                    fclose ( $handle );
                    if(empty($books_notfound)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_notfound','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                    return redirect()->route('booklist')->with("message","CSV Details Added Successfully");
                }else{
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }/* if csv id available condition ends*/
            else{
                return redirect()->route('booklist')->with("error","Csv not uploaded");
            }
        }/* csv weekly ends :: diamond */

    /***
    csv amazon 13nov2017 #ruby
    **/

        if ($request->has('csv_weekly') && $request['book_platform']=='amazon') {
            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_amazon_".date('Y-m-d H:i:s'); //unique token

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                #getting from db
                try{
                    $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                    return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    fgetcsv($handle, 0, ","); # abandon first record
                    try{
                        #books which are not found in the system
                        $books_not_found = array();

                        #storing all values to be inserted into db
                        $amazon_csv_values = array();

                        #checking how many are existing in db alreadt
                        $already_existing = array();

                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[0])) { # ignore blank lines
                                #skip first rows
                                if(stripos(trim($data[0]),"Invoice Date") !== false){
                                    continue;
                                }
                                #skip the last rows
                                if(isset($data[19]) && isset($data[20]) && isset($data[0]) && isset($data[1])){
                                    if(empty(trim($data[19])) && empty(trim($data[20])) && !empty(trim($data[0])) && !empty(trim($data[1]))){
                                        continue;
                                    }
                                }
                                #checking if format is valid or not
                                if(isset($data[0])){
                                    if(strtotime(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                }
                                if(isset($data[1])){
                                    if(!is_numeric(trim($data[1])) || !strtotime(trim($data[1])) || empty($data[1])){
                                        $column_01 = true;
                                    }
                                }
                                if(isset($data[2])){
                                    if(is_numeric(trim($data[2]))){
                                        $column_02 = true;
                                    }
                                }
                                if(isset($data[3])){
                                    if(is_numeric(trim($data[3])) || empty($data[3])){
                                        $column_03 = true;
                                    }
                                }
                                if(isset($data[4])){
                                    if(is_numeric(trim($data[4]))  || empty(trim($data[4]))){
                                        $column_04 = true;
                                    }
                                }
                                if(isset($data[5])){
                                    if(!strtotime(trim($data[5])) && !is_numeric(trim($data[5]))   &&  !empty(trim($data[5]))){
                                        $column_05 = true;
                                    }
                                }
                                if(isset($data[6])){
                                    if(!strtotime(trim($data[6])) && !is_numeric(trim($data[6]))   &&  !empty(trim($data[6]))){
                                        $column_06 = true;
                                    }
                                }
                                if(isset($data[7])){
                                    if(!strtotime(trim($data[7])) && !is_numeric(trim($data[7])) ){
                                        $column_07 = true;
                                    }
                                }
                                if(isset($data[8])){
                                    if(!strtotime(trim($data[8])) && !is_numeric(trim($data[8])) ){
                                        $column_08 = true;
                                    }
                                }
                                if(isset($data[9])){
                                    if(is_numeric(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                }
                                if(isset($data[10])){
                                    if(is_numeric(trim($data[10])) ){
                                        $column_10 = true;
                                    }
                                }
                                if(isset($data[11])){
                                    if( is_numeric(trim($data[11])) && !empty(trim($data[11])) ){
                                        $column_11 = true;
                                    }
                                }
                                if(isset($data[12])){
                                    if(is_numeric(trim($data[12])) && !empty(trim($data[12])) ){
                                        $column_12 = true;
                                    }
                                }
                                if(isset($data[13])){
                                    if(is_numeric(trim($data[13]))){
                                        $column_13 = true;
                                    }
                                }
                                if(isset($data[14])){
                                    if(is_numeric(trim($data[14]))){
                                        $column_14 = true;
                                    }
                                }
                                if(isset($data[15])){
                                    if(!strtotime(trim($data[15])) && !is_numeric(trim($data[15]))){
                                        $column_15 = true;
                                    }
                                }
                                if(isset($data[16])){
                                    if(is_numeric(trim($data[16]))){
                                        $column_16 = true;
                                    }
                                }
                                if(isset($data[17])){
                                    if(!strtotime(trim($data[17])) && !is_numeric(trim($data[17]))){
                                        $column_17 = true;
                                    }
                                }
                                if(isset($data[18])){
                                    if(is_numeric(trim($data[18]))){
                                        $column_18 = true;
                                    }
                                }
                                if(isset($data[19])){
                                    if(is_numeric(trim($data[19]))){
                                        $column_19 = true;
                                    }
                                }
                                if(isset($data[20])){
                                    if(!strtotime(trim($data[20])) && !is_numeric(trim($data[20]))){
                                        $column_20 = true;
                                    }
                                }
                                if(isset($data[21])){
                                    if(!strtotime(trim($data[21])) && !is_numeric(trim($data[21])) && isset($data[21]) ){
                                        $column_21 = true;
                                    }
                                }
                                if(isset($data[22])){
                                    if(!strtotime(trim($data[22])) && !is_numeric(trim($data[22])) || empty(trim($data[22])) && isset($data[22])){
                                        $column_22 = true;
                                    }
                                }
                                if(isset($data[23])){
                                    if(strtotime(trim($data[23])) && !is_numeric(trim($data[23])) && !empty(trim($data[23])) && isset($data[23])){
                                        $column_23 = true;
                                    }
                                }
                                #if invalid, send back to the publisher
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_08) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_14) || !isset($column_15) || !isset($column_16) || !isset($column_17) || !isset($column_18) || !isset($column_19) || !isset($column_20) || !isset($column_21) || !isset($column_22) || !isset($column_23) ){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }else{
                                    echo "GOOD";
                                }

                                #collecting row data into the variables
                                $invoicedate = trim($data[0]);
                                #change date format
                                $invoice_date = date("Y-m-d", strtotime($invoicedate));
                                $asin_code = trim($data[1]);
                                $isbn_10 = trim($data[2]);
                                $isbn_code = trim($data[3]);
                                $digital_isbn = trim($data[4]);
                                $title = trim($data[5]);
                                $author = trim($data[6]);
                                $imprint = trim($data[7]);
                                $format = trim($data[8]);
                                $units_purchased = trim($data[9]);
                                $units_refund = trim($data[10]);
                                $net_units = trim($data[11]);
                                $net_units_mtd = trim($data[12]);
                                $adjustments = trim($data[13]);
                                $list_price = trim($data[14]);
                                $list_price_currency = trim($data[15]);
                                $publisher_price = trim($data[16]);
                                $publisher_price_currency = trim($data[17]);
                                $discount = trim($data[18]);
                                $payment_amount = trim($data[19]);
                                $payment_amount_currency = trim($data[20]);
                                ##if the last culmn is found i.e. for american format
                                if(isset($data[21])){
                                    $currency_code = trim($data[21]);
                                }else{
                                    $currency_code = '';
                                }
                                #For custom date column.if 23 number column is set then that column value we will use as date otherwise 22 column value will be used.
                                if(isset($data[23])){
                                    $custom_date_amzn = trim($data[23]);
                                    $custom_date_amazon = date("Y-m-d", strtotime($custom_date_amzn));
                                }else{
                                    $custom_date_amzn = trim($data[22]);
                                    $custom_date_amazon = date("Y-m-d", strtotime($custom_date_amzn));
                                }

                                ##replacing dollar
                                if(strpos($payment_amount, '$') !== false){
                                    $payment_amount = str_replace('$', '', $payment_amount);
                                }else{
                                    $payment_amount = $payment_amount;
                                }

                                #check if value is date or not. if this is date then skip and if not then we will continue with this value.
                                $program_type = '';
                              
                                if(isset($data[22])){
                                    if(\DateTime::createFromFormat('Y/m/d', $data[22]) !== FALSE) {
                                        $program_type = '';
                                    }else{
                                        $program_type = trim($data[22]);
                                    }
                                }else{
                                    $program_type = '';
                                }

                                #Reset book_id_avail.
                                $book_id_avail = '';

                                ##finding out the corressponding book id
                                #using asin code
                                if(!empty(trim($asin_code))){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('asin_code',$asin_code)
                                        ->where('added_by',$id)
                                        ->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                    }

                                    #if empty, match with the amazon code of the book
                                    if(empty($book_id_avail)){
                                       try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('amazon_item_code',$asin_code)
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        } 
                                    }

                                    #if still id not found, use isbn_code_10 match with ISBN Physical 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_10)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code_10 to match with ISBN 10 column
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_10)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code_10 to match with ISBN Digital 13 column
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_10)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code match with ISBN Physical 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code match with ISBN 10
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_code)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code match with ISBN Digital 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_code)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use digital isbn  match with ISBN Physical 13  
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$digital_isbn)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use digital isbn match with ISBN 10  
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$digital_isbn)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use digital isbn match with ISBN Digital 13  
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$digital_isbn)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                }elseif(!empty(trim($isbn_10))){
                                    try{ // match with ISBN Physical 13
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code',$isbn_10)
                                    ->where('isbn_code', '<>', '', 'and')
                                    ->where('isbn_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                    }
                                    #if still id not found, use isbn_code_10 to match with ISBN 10 column
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_10)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use isbn_code_10 to match with ISBN Digital 13 column
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_10)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use isbn_code match with ISBN Physical 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }

                                    #if still id not found, use isbn_code match with ISBN 10
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_code)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');

                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use isbn_code match with ISBN Digital 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_code)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN Physical 13   
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$digital_isbn)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN 10  
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$digital_isbn)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');

                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN Digital 13  
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$digital_isbn)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                }elseif(!empty(trim($isbn_code))){
                                #if asin code is empty, directly use isbn code match with ISBN Physical 13
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code',$isbn_code)
                                    ->where('isbn_code', '<>', '', 'and')
                                    ->where('isbn_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                    }
                                    #if still id not found, match with ISBN 10
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_code)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, match with ISBN Digital 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_code)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN Physical 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$digital_isbn)
                                        ->where('isbn_code', '<>', '', 'and')
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN 10
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$digital_isbn)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN Digital 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$digital_isbn)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                }else{
                                #if asin code & physical isbn are empty, directly use digital isbn code match with ISBN Physical 13
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code',$digital_isbn)
                                    ->where('isbn_code', '<>', '', 'and')
                                    ->where('isbn_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                    }
                                    #if still id not found, use digital isbn match with ISBN 10
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$digital_isbn)
                                        ->where('isbn_code2', '<>', '', 'and')
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                    #if still id not found, use digital isbn match with ISBN Digital 13
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$digital_isbn)
                                        ->where('isbn_code3', '<>', '', 'and')
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine());
                                        }
                                    }
                                }
                                #check if this id is merged book or not
                                if(!empty($book_id_avail)){
                                    #sub id
                                    try{
                                    $book_merge_status = DB::Table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                    return $e->getMessage().' book_merge_status diamond Line no.'.$e->getLine();
                                    }
                                    if(!empty($book_merge_status)){
                                        #get main id
                                        try{
                                        $main_merge_book_id = DB::Table('merged_books')->where('id',$book_merge_status)->value('main_book');
                                        }catch(\Exception $e){
                                        return $e->getMessage().' main_merge_book_id diamond Line no.'.$e->getLine();
                                        }
                                    $book_id_avail = $main_merge_book_id;
                                    }else{
                                        #if no sub id available, put 
                                        $book_id_avail = $book_id_avail;
                                    }
                                }
                                #if book not found, collect it into the array and send into the view
                                if( empty($book_id_avail) ){
                                    if(empty($asin_code) && empty($isbn_code) && empty($digital_isbn)){
                                        $action_to_do = "Please add any code in the csv matching with system for this book.";
                                    }else if(!empty($asin_code) && empty($isbn_code) && empty($digital_isbn)){
                                        $action_to_do = "Please add or edit book with this asin code.";
                                    }else if(empty($asin_code) && !empty($isbn_code) && empty($digital_isbn)){
                                        $action_to_do = "Please add or edit book with this isbn code.";
                                    }else if(empty($asin_code) && empty($isbn_code) && !empty($digital_isbn)){
                                        $action_to_do = "Please add or edit book with this digital isbn code.";
                                    }else{
                                        $action_to_do = "Please add or edit book with one or all these provided codes.";
                                    }
                                    $books_not_found[]= [
                                    'asin_code'=> $asin_code,
                                    'isbn_code'=> $isbn_code,
                                    'digital_isbn'=> $digital_isbn,
                                    'title' => $title,
                                    'action_to_do'=>$action_to_do
                                    ];
                                }

                                ##getting the currency from the last row of the csv
                                $rows = file($request->csv_file_weekly);
                                $last_row = array_pop($rows);
                                $currencyrate = str_getcsv($last_row);
                                $currency_rate = $currencyrate[4];
                                $_exchange_currency = $currencyrate[2];
                                if($payment_amount_currency != 'USD') {
                                    if(!empty($_exchange_currency)){
                                        if($payment_amount_currency == $_exchange_currency){
                                            $_exchange_payment_amount = $payment_amount * $currency_rate;
                                        }
                                    }else{
                                        $_exchange_payment_amount = $payment_amount;
                                    }
                                }else{
                                    $_exchange_payment_amount = $payment_amount;
                                }
                                #check if sales already exists or not
                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('amazon_sales_reports')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'invoice_date' => $invoice_date,
                                        'asin' => $asin_code,
                                        'physical_isbn_10' =>  $isbn_10,
                                        'physical_isbn_13' =>$isbn_code,
                                        'digital_isbn' => $digital_isbn,
                                        'title' => $title,
                                        'author' =>$author,
                                        'imprint' => $imprint,
                                        'format' => $format,
                                        'units_purchased' => $units_purchased,
                                        'units_refunded' => $units_refund,
                                        'net_units' => $net_units,
                                        'net_units_mtd'=> $net_units_mtd,
                                        'adjustments_made'=> $adjustments,
                                        'list_price'=> $list_price,
                                        'list_price_currency'=> $list_price_currency,
                                        'publisher_price'=> $publisher_price,
                                        'publisher_price_currency' =>$publisher_price_currency,
                                        'discount_percentage'=> $discount,
                                        'payment_amount'=> $payment_amount,
                                        'payment_amount_currency' =>$payment_amount_currency,
                                        'country_code'=> $currency_code,
                                        'exchange_payment_amount' =>$_exchange_payment_amount,
                                        'program_type'=> $program_type,
                                        'custom_sale_date' =>$custom_date_amazon
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }

                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                        $already_existing[] = $check_existing_sale;
                                    }
                                    #there is no data found with same details already in db
                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $amazon_csv_values[]= [
                                            'book_id_avail' => $book_id_avail,
                                            'added_by'=>$id,
                                            'invoice_date' => $invoice_date,
                                            'asin_code'=> $asin_code,
                                            'isbn_code'=> $isbn_code,
                                            'isbn_10'=> $isbn_10,
                                            'digital_isbn'=> $digital_isbn,
                                            'title'=> $title,
                                            'author'=> $author,
                                            'imprint' => $imprint,
                                            'format'=> $format,
                                            'units_purchased'=> $units_purchased,
                                            'units_refund' => $units_refund,
                                            'net_units'=> $net_units,
                                            'net_units_mtd'=>$net_units_mtd,
                                            'adjustments'=>$adjustments,
                                            'list_price'=>$list_price,
                                            'list_price_currency'=>$list_price_currency,
                                            'publisher_price' => $publisher_price,
                                            'publisher_price_currency' => $publisher_price_currency,
                                            'discount'=>$discount,
                                            'payment_amount'=>$payment_amount,
                                            'payment_amount_currency'=>$payment_amount_currency,
                                            'currency_code'=>$currency_code,
                                            'exchange_payment_amount' =>$_exchange_payment_amount,
                                            'program_type' => $program_type,
                                            'csv_id' => $csv_id,
                                            'custom_sale_date'=>$custom_date_amazon
                                            ];
                                        }
                                    }
                                }
                            }#end ignore blank lines
                        }#ending while

                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);
                        
                        #platform book is uploaded for
                        $plateform = 'amazon';

                        ##saving the data into array
                        if(!empty($amazon_csv_values)){

                            #count of books added
                            $uploaded_books = count($amazon_csv_values);

                            foreach ($amazon_csv_values as $csv_rows) {
                                if(!empty($csv_rows)){
                                    $amazon_csv_data = new Amazon_sales_report();
                                    $amazon_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                    $amazon_csv_data->added_by = $csv_rows['added_by'];
                                    $amazon_csv_data->invoice_date = $csv_rows['invoice_date'];
                                    $amazon_csv_data->asin = $csv_rows['asin_code'];
                                    $amazon_csv_data->physical_isbn_10 =  $csv_rows['isbn_10'];
                                    $amazon_csv_data->physical_isbn_13 =$csv_rows['isbn_code'];
                                    $amazon_csv_data->digital_isbn = $csv_rows['digital_isbn'];
                                    $amazon_csv_data->title = $csv_rows['title'];
                                    $amazon_csv_data->author = $csv_rows['author'];
                                    $amazon_csv_data->imprint = $csv_rows['imprint'];
                                    $amazon_csv_data->format = $csv_rows['format'];
                                    $amazon_csv_data->units_purchased = $csv_rows['units_purchased'];
                                    $amazon_csv_data->units_refunded = $csv_rows['units_refund'];
                                    $amazon_csv_data->net_units = $csv_rows['net_units'];
                                    $amazon_csv_data->net_units_mtd = $csv_rows['net_units_mtd'];
                                    $amazon_csv_data->adjustments_made = $csv_rows['adjustments'];
                                    $amazon_csv_data->list_price = $csv_rows['list_price'];
                                    $amazon_csv_data->list_price_currency = $csv_rows['list_price_currency'];
                                    $amazon_csv_data->publisher_price = $csv_rows['publisher_price'];
                                    $amazon_csv_data->publisher_price_currency = $csv_rows['publisher_price_currency'];
                                    $amazon_csv_data->discount_percentage = $csv_rows['discount'];
                                    $amazon_csv_data->payment_amount = $csv_rows['payment_amount'];
                                    $amazon_csv_data->payment_amount_currency = $csv_rows['payment_amount_currency'];
                                    $amazon_csv_data->country_code = $csv_rows['currency_code'];
                                    $amazon_csv_data->exchange_payment_amount = $csv_rows['exchange_payment_amount'];
                                    $amazon_csv_data->program_type = $csv_rows['program_type'];
                                    $amazon_csv_data->csv_id = $csv_rows['csv_id'];
                                    $amazon_csv_data->custom_sale_date = $csv_rows['custom_sale_date'];
                                    $amazon_csv_data->save();
                                }
                            }#end foreach
                        }else{#end amazon_csv_values if
                            $uploaded_books = 0; #count of books added
                        }
                    }catch (\Exception $e) {
                        return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );
                    }
                    fclose ( $handle );
                    if(empty($books_not_found)){
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                }else{#end handle if
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#if csv_id ends
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*Amazon CSV code ends*/

    /*
    Barnes and noble CSV code starts
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='barnes') {
            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_barnes_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){ #Check if csv id exist or not
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    fgetcsv($handle, 0, ","); #abandon first record
                    try{
                        #array collecting list of books that are not found
                        $books_not_found = array();

                        #collection of data to be inserted
                        $barnes_csv_values = array();

                        #checking how many are existing in db alreadt
                        $already_existing = array();

                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty(trim($data[1]))) {#ignore blank lines
                                #validating the format of csv
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12]) && isset($data[13]) && isset($data[14]) && isset($data[15]) && isset($data[16]) && isset($data[17]) && isset($data[18]) && isset($data[19]) && isset($data[20]) && isset($data[21]) && isset($data[22]) && isset($data[23]) && isset($data[24]) && isset($data[25]) && isset($data[26]) && isset($data[27]) && isset($data[28]) && isset($data[29]) && isset($data[30])){
                                    if(is_numeric(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(!is_numeric(trim($data[1])) && !empty(trim($data[1]))){
                                        $column_01 = true;
                                    }
                                    if(!is_numeric(trim($data[2])) && !empty(trim($data[2]))){
                                        $column_02 = true;
                                    }
                                    if(!is_numeric(trim($data[3])) && !empty(trim($data[3]))){
                                        $column_03 = true;
                                    }
                                    if(!is_numeric(trim($data[4])) && !empty(trim($data[4]))){
                                        $column_04 = true;
                                    }
                                    if(!is_numeric(trim($data[5])) && !empty(trim($data[5]))){
                                        $column_05 = true;
                                    }
                                    if(!is_numeric(trim($data[6])) && !empty(trim($data[6]))){
                                        $column_06 = true;
                                    }
                                    if(strtotime(trim($data[7])) && !empty(trim($data[7]))){
                                        $column_07 = true;
                                    }
                                    if(is_numeric(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                    if(is_numeric(trim($data[10]))){
                                        $column_10 = true;
                                    }
                                    if(!is_numeric(trim($data[11])) && !empty(trim($data[11])) ){
                                        $column_11 = true;
                                    }
                                    if(strtotime(trim($data[12]))){
                                        $column_12 = true;
                                    }
                                    if(!is_numeric(trim($data[13])) && !empty(trim($data[13])) ){
                                        $column_13 = true;
                                    }
                                    if(is_numeric(trim($data[14]))){
                                        $column_14 = true;
                                    }
                                    if(is_numeric(trim($data[15]))){
                                        $column_15 = true;
                                    }
                                    if(is_numeric(trim($data[16]))){
                                        $column_16 = true;
                                    }
                                    if(is_numeric(trim($data[17]))){
                                        $column_17 = true;
                                    }
                                    if(is_numeric(trim($data[18]))){
                                        $column_18 = true;
                                    }
                                    if(is_numeric(trim($data[19]))){
                                        $column_19 = true;
                                    }
                                    if(is_numeric(trim($data[20]))){
                                        $column_20 = true;
                                    }
                                    if(is_numeric(trim($data[21]))){
                                        $column_21 = true;
                                    }
                                    if(is_numeric(trim($data[22]))){
                                        $column_22 = true;
                                    }
                                    if(!is_numeric(trim($data[23])) && !empty(trim($data[23]))){
                                        $column_23 = true;
                                    }
                                    if(is_numeric(trim($data[24]))){
                                        $column_24 = true;
                                    }
                                    if(is_numeric(trim($data[25]))){
                                        $column_25 = true;
                                    }
                                    if(strtotime(trim($data[26]))){
                                        $column_26 = true;
                                    }
                                    if(!is_numeric(trim($data[27])) && !empty(trim($data[27]))){
                                        $column_27 = true;
                                    }
                                    if(!is_numeric(trim($data[28])) && !empty(trim($data[28]))){
                                        $column_28 = true;
                                    }
                                    if(preg_match('/^[0-9-]+$/',trim($data[29]),$matches)){
                                        $column_29 = true;
                                    }
                                    if(strtotime(trim($data[30]))){
                                        $column_30 = true;
                                    }
                                }

                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_14) || !isset($column_15) || !isset($column_16) || !isset($column_17) || !isset($column_18) || !isset($column_19) || !isset($column_20) || !isset($column_21) || !isset($column_22) || !isset($column_23)  || !isset($column_24) || !isset($column_25) || !isset($column_26) || !isset($column_27) || !isset($column_28) || !isset($column_29) || !isset($column_30)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }
                                ##collecting row data into the variables
                                $isbn_code = trim($data[0]);
                                $title = trim($data[1]);
                                $author = trim($data[2]);
                                $publisher = trim($data[3]);
                                $category = trim($data[4]);
                                $format = trim($data[5]);
                                $order_type = trim($data[6]);
                                $podate = trim($data[7]);
                                $po_date = date("Y-m-d", strtotime($podate)); 
                                $egiftdate = trim($data[8]);
                                $egift_date = date("Y-m-d", strtotime($egiftdate)); 
                                $po_number = trim($data[9]);
                                $po_country_code = trim($data[10]);
                                $ap_invoice = trim($data[11]);
                                $apinvoicedate = trim($data[12]);
                                $ap_invoice_date = date("Y-m-d",strtotime($apinvoicedate));  
                                $sale_currency = trim($data[13]);
                                $dlp_tax_exclusive = trim($data[14]);
                                $consumer_tax_exclusive = trim($data[15]);
                                $tax_amount = trim($data[16]);
                                $consumer_tax_inclusive = trim($data[17]);
                                $units_sold = trim($data[18]);
                                $units_returned = trim($data[19]);
                                $royality = trim($data[20]);
                                $unit_cost_sale_curr = trim($data[21]);
                                $total_cost_sale_curr = trim($data[22]);
                                $payment_currency = trim($data[23]);
                                $unit_cost_payment_curr = trim($data[24]);
                                $total_cost_payment_curr = trim($data[25]);
                                $invoicedate = trim($data[26]);
                                $invoice_date = date("Y-m-d", strtotime($invoicedate));  
                                $city = trim($data[27]);
                                $state = trim($data[28]);
                                $postal_code = trim($data[29]);
                                $cstm_invce_date = trim($data[30]);
                                $custom_invoice_date = date("Y-m-d", strtotime($cstm_invce_date));
                                ##replacing dollar
                                if(stripos($total_cost_payment_curr, '$') !== false){
                                $total_cost_payment_curr = str_replace('$', '', $total_cost_payment_curr);
                                }else{
                                $total_cost_payment_curr = $total_cost_payment_curr;
                                }

                                #Reset book_id_avail.
                                $book_id_avail = '';

                                ##finding out the corressponding book id match isbn with ISBN Physical 13
                                if(!empty(trim($isbn_code))){
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('isbn_code',$isbn_code)
                                    ->where('isbn_code', '<>', '', 'and')    
                                    ->where('isbn_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }

                                    #if book id not found then match isbn with ISBN 10 in system
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_code)
                                        ->where('isbn_code2', '<>', '', 'and')    
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }

                                    #if book id not found then match isbn with ISBN Digital 13 in system
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_code)
                                        ->where('isbn_code3', '<>', '', 'and')    
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }

                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    try{
                                    $book_merge_sub_id = DB::table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }                    
                                    #if sub id is not empty then find its main book id
                                    if(!empty($book_merge_sub_id)){
                                        try{
                                        $main_merge_book_id = DB::table('merged_books')->where('id',$book_merge_sub_id)->value('main_book');
                                        $book_id_avail = $main_merge_book_id;  
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }               
                                    }else{
                                        //if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }

                                #if book not found, collect it into the array and send into the view   
                                if( empty($book_id_avail) || empty($isbn_code) ){
                                    if(empty($isbn_code)){
                                        $action_to_do = "Please add isbn code in the csv matching with system isbn code.";
                                    }else{
                                        $action_to_do = "Please add or edit book with this isbn code.";
                                    }
                                    $books_not_found[]= [
                                    'isbn_code'=> $isbn_code,
                                    'title'=> $title,
                                    'action_to_do'=>$action_to_do
                                    ];
                                }

                                #check if sales are already existing
                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('barnes_nobles_sale_reports')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'isbn_code' => $isbn_code,
                                        'title'=> $title,
                                        'author'=> $author,
                                        'publisher'=> $publisher,
                                        'category'=> $category,
                                        'format'=> $format,
                                        'order_type'=> $order_type,
                                        'po_date' => $po_date,
                                        'egift_date'=> $egift_date,
                                        'po_number'=> $po_number,
                                        'po_country_code' => $po_country_code,
                                        'ap_invoice'=> $ap_invoice,
                                        'ap_invoice_date'=>$ap_invoice_date,
                                        'sale_currency'=>$sale_currency,
                                        'dlp_sale_currency_tax'=>$dlp_tax_exclusive,
                                        'consumer_tax_exclusive'=>$consumer_tax_exclusive,
                                        'tax_amount' => $tax_amount,
                                        'consumer_tax_inclusive' => $consumer_tax_inclusive,
                                        'units_sold'=>$units_sold,
                                        'units_returned'=>$units_returned,
                                        'royality'=>$royality,
                                        'unit_cost_sale_currency'=>$unit_cost_sale_curr,
                                        'total_cost_sale_currency' =>$total_cost_sale_curr,
                                        'payment_currency' => $payment_currency,
                                        'unit_cost_payment_currency' => $unit_cost_payment_curr,
                                        'total_cost_payment_currency' => $total_cost_payment_curr,
                                        'invoice_date' => $invoice_date,
                                        'city' => $city,
                                        'state_region' => $state,
                                        'postal_code' => $postal_code,
                                        'custom_invoice_date'=>$custom_invoice_date
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage().' '.$e->getLine();
                                    }

                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }

                                    #sales are not available then only add new sales
                                    if($check_existing_sale->isEmpty()){
                                        #store data into an array
                                        if(!empty($book_id_avail)){
                                            $barnes_csv_values[]= [
                                            'book_id_avail' => $book_id_avail,
                                            'added_by'=>$id,
                                            'isbn_code' => $isbn_code,
                                            'title'=> $title,
                                            'author'=> $author,
                                            'publisher'=> $publisher,
                                            'category'=> $category,
                                            'format'=> $format,
                                            'order_type'=> $order_type,
                                            'po_date' => $po_date,
                                            'egift_date'=> $egift_date,
                                            'po_number'=> $po_number,
                                            'po_country_code' => $po_country_code,
                                            'ap_invoice'=> $ap_invoice,
                                            'ap_invoice_date'=>$ap_invoice_date,
                                            'sale_currency'=>$sale_currency,
                                            'dlp_tax_exclusive'=>$dlp_tax_exclusive,
                                            'consumer_tax_exclusive'=>$consumer_tax_exclusive,
                                            'tax_amount' => $tax_amount,
                                            'consumer_tax_inclusive' => $consumer_tax_inclusive,
                                            'units_sold'=>$units_sold,
                                            'units_returned'=>$units_returned,
                                            'royality'=>$royality,
                                            'unit_cost_sale_curr'=>$unit_cost_sale_curr,
                                            'total_cost_sale_curr' =>$total_cost_sale_curr,
                                            'payment_currency' => $payment_currency,
                                            'unit_cost_payment_curr' => $unit_cost_payment_curr,
                                            'total_cost_payment_curr' => $total_cost_payment_curr,
                                            'invoice_date' => $invoice_date,
                                            'city' => $city,
                                            'state' => $state,
                                            'postal_code' => $postal_code,
                                            'csv_id'=>$csv_id,
                                            'custom_invoice_date'=>$custom_invoice_date
                                            ];     
                                        }
                                    }
                                }             
                            }#end ignore blank lines
                        }#endwhile
                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);
                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);
                        #platform book is uploaded for
                        $plateform = 'barnes';
                        ##saving the data into array
                        if(!empty($barnes_csv_values)){
                            #count of books added
                            $uploaded_books = count($barnes_csv_values);
                            foreach ($barnes_csv_values as $csv_rows) {  
                                if(!empty($csv_rows)){
                                    $barnes_csv_data = new barnes_nobles_sale_reports();
                                    $barnes_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                    $barnes_csv_data->added_by = $csv_rows['added_by'];
                                    $barnes_csv_data->isbn_code = $csv_rows['isbn_code'];
                                    $barnes_csv_data->title = $csv_rows['title'];
                                    $barnes_csv_data->author =  $csv_rows['author'];
                                    $barnes_csv_data->publisher =$csv_rows['publisher'];
                                    $barnes_csv_data->category = $csv_rows['category'];
                                    $barnes_csv_data->format = $csv_rows['format'];
                                    $barnes_csv_data->order_type = $csv_rows['order_type'];
                                    $barnes_csv_data->po_date = $csv_rows['po_date'];
                                    $barnes_csv_data->egift_date = $csv_rows['egift_date'];
                                    $barnes_csv_data->po_number = $csv_rows['po_number'];
                                    $barnes_csv_data->po_country_code = $csv_rows['po_country_code'];
                                    $barnes_csv_data->ap_invoice = $csv_rows['ap_invoice'];
                                    $barnes_csv_data->ap_invoice_date = $csv_rows['ap_invoice_date'];
                                    $barnes_csv_data->sale_currency = $csv_rows['sale_currency'];
                                    $barnes_csv_data->dlp_sale_currency_tax = $csv_rows['dlp_tax_exclusive'];
                                    $barnes_csv_data->consumer_tax_exclusive = $csv_rows['consumer_tax_exclusive'];
                                    $barnes_csv_data->tax_amount = $csv_rows['tax_amount'];
                                    $barnes_csv_data->consumer_tax_inclusive = $csv_rows['consumer_tax_inclusive'];
                                    $barnes_csv_data->units_sold = $csv_rows['units_sold'];
                                    $barnes_csv_data->units_returned = $csv_rows['units_returned'];
                                    $barnes_csv_data->royality = $csv_rows['royality'];
                                    $barnes_csv_data->unit_cost_sale_currency = $csv_rows['unit_cost_sale_curr'];
                                    $barnes_csv_data->total_cost_sale_currency = $csv_rows['total_cost_sale_curr'];
                                    $barnes_csv_data->payment_currency = $csv_rows['payment_currency'];
                                    $barnes_csv_data->unit_cost_payment_currency = $csv_rows['unit_cost_payment_curr'];
                                    $barnes_csv_data->total_cost_payment_currency = $csv_rows['total_cost_payment_curr'];
                                    $barnes_csv_data->invoice_date = $csv_rows['invoice_date'];
                                    $barnes_csv_data->city = $csv_rows['city'];
                                    $barnes_csv_data->state_region = $csv_rows['state'];
                                    $barnes_csv_data->postal_code = $csv_rows['postal_code'];
                                    $barnes_csv_data->csv_id = $csv_rows['csv_id'];
                                    $barnes_csv_data->custom_invoice_date = $csv_rows['custom_invoice_date'];
                                    $barnes_csv_data->save();
                                }
                            } 
                        }else{ #end barnes_csv_values if
                            $uploaded_books = 0;#count of books added
                        }
                    }catch (\Exception $e) {                      
                        return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );
                    }
                    fclose ( $handle );
                    ##returning to books not found page
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                    return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    } 
                }else{#end if upload csv
                return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#If ends for csv id check            
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*if conditions ends for barnes csv upload*/

    /*
    Apple CSV Code starts
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='apple') {

            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_apple_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                #getting from db
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                    return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    fgetcsv($handle, 0, ","); #abandon first record
                    try{
                        $books_not_found = array();

                        #storing all values to be inserted into db
                        $apple_csv_values = array();

                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[0])) {#ignore blank lines
                                ##skipping the following rows
                                if( stripos(trim($data[0]), "Foreign Exchange") !== false ){continue;}
                                if( stripos( trim($data[0]), "Total_Rows" ) !== false ){continue;}
                                if( stripos( trim($data[0]), "Total_Amount" ) !== false ){continue;}
                                if( stripos( trim($data[0]), "Total_Units" ) !== false ){continue;}

                                #checking format is valid or not
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12]) && isset($data[13]) && isset($data[14]) && isset($data[15]) && isset($data[16]) && isset($data[17]) && isset($data[18]) && isset($data[19]) && isset($data[20]) && isset($data[21]) && isset($data[22])){
                                    if(strtotime(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(strtotime(trim($data[1]))){
                                        $column_01 = true;
                                    }
                                    if(is_numeric(trim($data[2])) || empty(trim($data[2]))){
                                        $column_02 = true;
                                    }
                                    if(is_numeric(trim($data[3])) || empty(trim($data[3]))){
                                        $column_03 = true;
                                    }
                                    if(is_numeric(trim($data[4])) || empty(trim($data[4]))){
                                        $column_04 = true;
                                    }
                                    if(is_numeric(trim($data[5])) || empty(trim($data[5]))){
                                        $column_05 = true;
                                    }
                                    if(is_numeric(trim($data[6]))){
                                        $column_06 = true;
                                    }
                                    if(is_numeric(trim($data[7]))){
                                        $column_07 = true;
                                    }
                                    if(!is_numeric(trim($data[8]))  && !empty(trim($data[8])) ){
                                        $column_08 = true;
                                    }
                                    if(!is_numeric(trim($data[9]))  && !empty(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                    if(is_numeric(trim($data[10]))){
                                        $column_10 = true;
                                    }
                                    if(!is_numeric(trim($data[11])) && !empty(trim($data[11])) ){
                                        $column_11 = true;
                                    }
                                    if(!is_numeric(trim($data[12])) && !empty(trim($data[12])) ){
                                        $column_12 = true;
                                    }
                                    if(!is_numeric(trim($data[13])) && !empty(trim($data[13])) ){
                                        $column_13 = true;
                                    }
                                    if(!empty(trim($data[15]))){
                                        $column_15 = true;
                                    }
                                    if(!is_numeric(trim($data[17])) && !empty(trim($data[17])) ){
                                        $column_17 = true;
                                    }
                                    if(is_numeric(trim($data[20])) || empty(trim($data[20])) ){
                                        $column_20 = true;
                                    }
                                    if(!is_numeric(trim($data[21])) || empty(trim($data[21])) ){
                                        $column_21 = true;
                                    }
                                    if(strtotime(trim($data[22]))){
                                        $column_22 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_15) || !isset($column_17) || !isset($column_20) || !isset($column_21) || !isset($column_22)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }
                                #collecting row data into the variables
                                $startdate = trim($data[0]);
                                $start_date = date("Y-m-d", strtotime($startdate)); 
                                $enddate = trim($data[1]);
                                $end_date = date("Y-m-d", strtotime($enddate));
                                $upc = trim($data[2]);
                                $isbn_code = trim($data[3]);
                                $vendor_identifier = trim($data[4]);
                                $quantity = trim($data[5]);
                                $partner_share = trim($data[6]);
                                $extended_partner_share = trim($data[7]);
                                $partner_share_currency = trim($data[8]);
                                $sales_return = trim($data[9]);
                                $apple_identifier = trim($data[10]);
                                $role = trim($data[11]);
                                $title = trim($data[12]);
                                $vendor = trim($data[13]);
                                $grid = trim($data[14]);
                                $product_type_identifier = trim($data[15]);
                                $isan_identifier = trim($data[16]);
                                $country_of_sale = trim($data[17]);
                                $pre_order_flag = trim($data[18]);
                                $promo_code = trim($data[19]);
                                $customer_price = trim($data[20]);
                                $customer_currency = trim($data[21]);
                                $custom_date_apl =  trim($data[22]);
                                $custom_date_apple = date("Y-m-d", strtotime($custom_date_apl));
                                #replacing dollar
                                if(strpos($customer_price, '$') !== false){
                                $customer_price = str_replace('$', '', $customer_price);
                                }else{
                                $customer_price = $customer_price;
                                }
                                #Reset book_id_avail.
                                $book_id_avail = '';
                                ##finding out the corressponding book id
                                #match isbn code with ISBN Physical 13
                                if(!empty(trim($isbn_code))){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #if still id not found, match isbn with ISBN 10 in system
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code2',$isbn_code)
                                        ->where('isbn_code2', '<>', '', 'and')    
                                        ->where('isbn_code2', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, match isbn with ISBN Digital 13 in system
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code3',$isbn_code)
                                        ->where('isbn_code3', '<>', '', 'and')    
                                        ->where('isbn_code3', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use itune code 
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('itune_code',$apple_identifier)
                                        ->where('itune_code', '<>', '', 'and')    
                                        ->where('itune_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use upc code 
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('upc',$upc)
                                        ->where('upc', '<>', '', 'and')    
                                        ->where('upc', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else if(!empty(trim($apple_identifier))){
                                    #if still book id not found, use itune directly
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('itune_code',$apple_identifier)
                                        ->where('itune_code', '<>', '', 'and')    
                                        ->where('itune_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    } 
                                    #if not id , match with upc code
                                    if(empty($book_id_avail)){
                                        try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('upc',$upc)
                                        ->where('upc', '<>', '', 'and')    
                                        ->where('upc', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else{
                                    #at the end directly use upc codes
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('upc',$upc)
                                    ->where('upc', '<>', '', 'and')    
                                    ->where('upc', '<>', '') //is not empty
                                    ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                }

                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    try{
                                        $book_merge_sub_id = DB::table('merged_books')
                                        ->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }   

                                    #if sub id is not empty then find its main book id
                                    if(!empty($book_merge_sub_id)){
                                        try{
                                            $main_merge_book_id = DB::table('merged_books')
                                            ->where('id',$book_merge_sub_id)->value('main_book');
                                            $book_id_avail = $main_merge_book_id;  
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }          
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                } 
                                  
                                #if book not found, collect it into the array and send into the view
                                if( empty($book_id_avail) ){
                                    if(empty($isbn_code) && empty($apple_identifier) && empty($upc)){
                                        $action_to_do = "Please add any code in the csv matching with system for this book.";
                                    }else if(!empty($isbn_code) && empty($apple_identifier) && empty($upc)){
                                        $action_to_do = "Please add or edit book with this isbn code.";
                                    }else if(!empty($apple_identifier) && empty($isbn_code)  && empty($upc)){
                                        $action_to_do = "Please add or edit book with this itune code.";
                                    }else if(!empty($upc) && empty($isbn_code) && empty($apple_identifier)){
                                        $action_to_do = "Please add or edit book with this upc code.";
                                    }else{
                                        $action_to_do = "Please add or edit book with one or all these provided codes.";
                                    }
                                    $books_not_found[]= [
                                    'isbn_code'=> $isbn_code,
                                    'itune'=> $apple_identifier,
                                    'upc'=>$upc,
                                    'title'=>$title,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }

                                ##abondone the last rows
                                $rows = file($request->csv_file_weekly);
                                $last_row = array_pop($rows);
                                $currencyrate = str_getcsv($last_row);
                                $foreign_exchange = $currencyrate[1];
                                if($customer_currency != 'USD') {
                                    $_exchange_payment_amount = $extended_partner_share * $foreign_exchange;
                                }else{
                                    $_exchange_payment_amount = $extended_partner_share;
                                }
                                #check if values already exists in the system
                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('apple_sale_reports')->where([
                                    'book_avail_id' => $book_id_avail,
                                    'added_by'=>$id,
                                    'start_date'=> $start_date,
                                    'end_date'=> $end_date,
                                    'upc' =>  $upc,
                                    'isbn_code' =>$isbn_code,
                                    'vendor_identifier'=> $vendor_identifier,
                                    'quantity'=> $quantity,
                                    'partner_share'=> $partner_share,
                                    'extended_partner_share'=> $extended_partner_share,
                                    'partner_share_currency'=> $partner_share_currency,
                                    'sales_return'=> $sales_return,
                                    'apple_identifier'=> $apple_identifier,
                                    'role'=> $role,
                                    'title'=> $title,
                                    'vendor'=> $vendor,
                                    'grid'=> $grid,
                                    'product_type_identifier'=> $product_type_identifier,
                                    'isan_identifier'=> $isan_identifier,
                                    'country_of_sale'=> $country_of_sale,
                                    'pre_order_flag'=> $pre_order_flag,
                                    'promo_code'=> $promo_code,
                                    'customer_price'=> $customer_price,
                                    'customer_currency'=> $customer_currency,
                                    'foreign_exchange'=> $foreign_exchange,
                                    'exchange_payment_amount'=> $_exchange_payment_amount,
                                    'custom_sale_date_apple'=> $custom_date_apple,
                                    ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }
                                    #if no sales are found then only add data
                                    // if($check_existing_sale->isEmpty()){ by nishant 28jan2019
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                        $apple_csv_values[]= [
                                        'book_id_avail' => $book_id_avail,
                                        'added_by'=>$id,
                                        'start_date' => $start_date,
                                        'end_date'=> $end_date,
                                        'upc'=> $upc,
                                        'isbn_code'=> $isbn_code,
                                        'vendor_identifier'=> $vendor_identifier,
                                        'quantity'=> $quantity,
                                        'partner_share'=> $partner_share,
                                        'extended_partner_share' => $extended_partner_share,
                                        'partner_share_currency'=> $partner_share_currency,
                                        'sales_return'=> $sales_return,
                                        'apple_identifier' => $apple_identifier,
                                        'role'=> $role,
                                        'title'=>$title,
                                        'vendor'=>$vendor,
                                        'grid'=>$grid,
                                        'product_type_identifier'=>$product_type_identifier,
                                        'isan_identifier' => $isan_identifier,
                                        'country_of_sale' => $country_of_sale,
                                        'pre_order_flag'=>$pre_order_flag,
                                        'promo_code'=>$promo_code,
                                        'customer_price'=>$customer_price,
                                        'customer_currency'=>$customer_currency,
                                        'csv_id'=>$csv_id,
                                        'foreign_exchange'=>$foreign_exchange,
                                        'exchange_payment_amount'=>$_exchange_payment_amount,
                                        'custom_sale_date_apple'=>$custom_date_apple
                                        ]; 
                                        }
                                    // } by nishant 28 jan 2019
                                }
                            }#end if for ignore blank lines
                        }#endwhile
                        #unique arrays
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);

                        #platform book is uploaded for
                        $plateform = 'apple';

                        ##saving the data into array
                        if(!empty($apple_csv_values)){ 

                            #count of books added
                            $uploaded_books = count($apple_csv_values);

                            foreach ($apple_csv_values as $csv_rows) {  
                                if(!empty($csv_rows)){
                                $apple_csv_data = new apple_sale_reports();
                                $apple_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                $apple_csv_data->added_by = $csv_rows['added_by'];
                                $apple_csv_data->start_date = $csv_rows['start_date'];
                                $apple_csv_data->end_date = $csv_rows['end_date'];
                                $apple_csv_data->upc =  $csv_rows['upc'];
                                $apple_csv_data->isbn_code =$csv_rows['isbn_code'];
                                $apple_csv_data->vendor_identifier = $csv_rows['vendor_identifier'];
                                $apple_csv_data->quantity = $csv_rows['quantity'];
                                $apple_csv_data->partner_share = $csv_rows['partner_share'];
                                $apple_csv_data->extended_partner_share = $csv_rows['extended_partner_share'];
                                $apple_csv_data->partner_share_currency = $csv_rows['partner_share_currency'];
                                $apple_csv_data->sales_return = $csv_rows['sales_return'];
                                $apple_csv_data->apple_identifier = $csv_rows['apple_identifier'];
                                $apple_csv_data->role = $csv_rows['role'];
                                $apple_csv_data->title = $csv_rows['title'];
                                $apple_csv_data->vendor = $csv_rows['vendor'];
                                $apple_csv_data->grid = $csv_rows['grid'];
                                $apple_csv_data->product_type_identifier = $csv_rows['product_type_identifier'];
                                $apple_csv_data->isan_identifier = $csv_rows['isan_identifier'];
                                $apple_csv_data->country_of_sale = $csv_rows['country_of_sale'];
                                $apple_csv_data->pre_order_flag = $csv_rows['pre_order_flag'];
                                $apple_csv_data->promo_code = $csv_rows['promo_code'];
                                $apple_csv_data->customer_price = $csv_rows['customer_price'];
                                $apple_csv_data->customer_currency = $csv_rows['customer_currency'];
                                $apple_csv_data->csv_id = $csv_rows['csv_id'];
                                $apple_csv_data->foreign_exchange = $csv_rows['foreign_exchange'];
                                $apple_csv_data->exchange_payment_amount = $csv_rows['exchange_payment_amount'];
                                $apple_csv_data->custom_sale_date_apple = $csv_rows['custom_sale_date_apple'];
                                $apple_csv_data->save();
                                }
                            } 
                        }else{#end apple_csv_values if
                            $uploaded_books = 0;#count of books added
                        }
                    }catch (\Exception $e) {                      
                        return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );                  
                    }
                    fclose ( $handle );
                    ##redirect to books not found if books not found array not empty
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{   
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                }else{//end if upload csv
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{   //If ends for csv id check            
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*if condition ends for apple csv upload*/

    /*
    Google CSV code starts
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='google') {

            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }
            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_google_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                #getting from db
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');

                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){ //Check if csv id exist or not
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                fgetcsv($handle, 0, ","); #abandon first record
                try{
                    #listing missing books
                    $books_not_found = array(); 

                    #collection to be added in db
                    $google_csv_values = array(); 

                    #checking how many are existing in db alreadt
                    $already_existing = array();

                    while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                        if (!empty($data[0])) {#ignore blank lines
                            #checking format is valid or not
                            if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12]) && isset($data[13]) && isset($data[14]) && isset($data[15]) && isset($data[16]) && isset($data[17]) && isset($data[18]) && isset($data[19]) && isset($data[20]) && isset($data[21]) && isset($data[22])){
                                if(strtotime(trim($data[0]))){
                                    $column_00 = true;
                                }
                                if(strtotime(trim($data[1]))){
                                    $column_01 = true;
                                }
                                if(!empty(trim($data[2]))){
                                    $column_02 = true;
                                }
                                if(!is_numeric(trim($data[3])) && !empty($data[3])){
                                    $column_03 = true;
                                }
                                if(!is_numeric(trim($data[4])) && !empty($data[4])){
                                    $column_04 = true;
                                }
                                if(!is_numeric(trim($data[5])) && !empty($data[5])){
                                    $column_05 = true;
                                }
                                if(is_numeric(trim($data[6]))){
                                    $column_06 = true;
                                }
								if(strpos(trim($data[7]), "GGKEY" ) !== FALSE 
                                    || strpos(trim($data[7]), "PKEY" ) !== FALSE || is_numeric(trim($data[7])) ){
                                    $column_07 = true;
                                }
                                // if(strpos(trim($data[7]), "GGKEY" ) === true 
                                //     || strpos(trim($data[7]), "PKEY" ) === true || is_numeric(trim($data[7])) ){
                                //     $column_07 = true;
                                // }
                                if(!is_numeric(trim($data[8])) && !empty($data[8])){
                                    $column_08 = true;
                                }
                                if(!is_numeric(trim($data[9])) && !empty($data[9])){
                                    $column_09 = true;
                                }
                                if(!is_numeric(trim($data[10])) && !empty($data[10])){
                                    $column_10 = true;
                                }
                                if(!is_numeric(trim($data[11])) && !empty($data[11])){
                                    $column_11 = true;
                                }
                                if(is_numeric(trim($data[12]))){
                                    $column_12 = true;
                                }
                                if(!is_numeric(trim($data[13]))&& !empty($data[13])){
                                    $column_13 = true;
                                }
                                if(is_numeric(trim($data[14]))){
                                    $column_14 = true;
                                }
                                if(is_numeric(trim($data[15]))){
                                    $column_15 = true;
                                }
                                if(!is_numeric(trim($data[16]))&& !empty($data[16])){
                                    $column_16 = true;
                                }
                                if(is_numeric(trim($data[17]))){
                                    $column_17 = true;
                                }
                                if(is_numeric(trim($data[18]))){
                                    $column_18 = true;
                                }
                                if(!is_numeric(trim($data[19])) && !empty($data[19])){
                                    $column_19 = true;
                                }
                                if(is_numeric(trim($data[20]))){
                                    $column_20 = true;
                                }
                                if(is_numeric(trim($data[21])) || empty($data[21])){
                                    $column_21 = true;
                                }
                                if(strtotime(trim($data[22]))){
                                    $column_22 = true;
                                }
                            }
                            if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_14)|| !isset($column_15) || !isset($column_16)|| !isset($column_17) || !isset($column_18)|| !isset($column_19)|| !isset($column_20) || !isset($column_21) || !isset($column_22)){
                                return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                            }

                            #collecting row data into the variables
                            $earningdate = trim($data[0]);
                            $earning_date = date("Y-m-d", strtotime($earningdate)); 
                            $transdate = trim($data[1]);
                            $transaction_date = date("Y-m-d", strtotime($transdate));
                            $google_book_id = trim($data[2]);
                            $product = trim($data[3]);
                            $type = trim($data[4]);
                            $preorder = trim($data[5]);
                            $quantity = trim($data[6]);
                            $primary_isbn = trim($data[7]);
                            $imprint_name = trim($data[8]);
                            $title = trim($data[9]);
                            $author = trim($data[10]);
                            $original_lp_currency = trim($data[11]);
                            $original_lp = trim($data[12]);
                            $lp_currency = trim($data[13]);
                            $lp_tax_inclusive = trim($data[14]);
                            $lp_tax_exclusive = trim($data[15]);
                            $country_of_sale = trim($data[16]);
                            $publisher_revenue_percentage = trim($data[17]);
                            $publisher_revenue = trim($data[18]);
                            $earnings_currency = trim($data[19]);
                            $earnings_amount = trim($data[20]);
                            $currency_conversion_rate = trim($data[21]);
                            $custom_saledate_google = trim($data[22]);
                            $custom_sale_date_google = date("Y-m-d", strtotime($custom_saledate_google));

                            ##replacing dollar
                            if(strpos($earnings_amount, '$') !== false){
                            $earnings_amount = str_replace('$', '', $earnings_amount);
                            }else{
                            $earnings_amount = $earnings_amount;
                            }

                            ##coverting the currency
                            if($lp_currency != 'USD'){
                                $earning_amount_conversion = $earnings_amount * $currency_conversion_rate;
                            }else{
                                $earning_amount_conversion = $earnings_amount;
                            }

                            #Reset book_id_avail.
                            $book_id_avail = '';

                            ##finding out the corressponding book id
                            #using asin code
                            if(!empty(trim($primary_isbn))){
                                #skip rows with ggkey as keyword in isbn column
                                if(strpos( trim($primary_isbn), "GGKEY" ) !== false ) {
                                    try{
                                        $whatIWant = substr($primary_isbn, strpos($primary_isbn, ":") + 1); 
                                        $book_id_avail = DB::table('book_details')
                                        ->where('google_gg_key',$whatIWant)
                                        ->where('google_gg_key', '<>', '', 'and')    
                                        ->where('google_gg_key', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #no book found by google gg key, find by isbn match with ISBN Physical 13
                                    if(empty($book_id_avail)) {
                                        try{
                                             $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code',$primary_isbn)
                                            ->where('isbn_code', '<>', '', 'and')    
                                            ->where('isbn_code', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found then match with ISBN 10
                                    if(empty($book_id_avail)) {
                                        try{
                                             $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$primary_isbn)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found then match with ISBN Digital 13
                                    if(empty($book_id_avail)) {
                                        try{
                                             $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$primary_isbn)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found by isbn, find by matching isbn with google gg key
                                    if(empty($book_id_avail)) {
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('google_gg_key',$primary_isbn)
                                            ->where('google_gg_key', '<>', '', 'and')    
                                            ->where('google_gg_key', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found then match with asin code
                                    if(empty($book_id_avail)) {
                                        try{
                                             $book_id_avail = DB::table('book_details')
                                            ->where('asin_code',$whatIWant)
                                            ->where('asin_code', '<>', '', 'and')    
                                            ->where('asin_code', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found then match with asin code
                                    if(empty($book_id_avail)) {
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('amazon_item_code',$whatIWant)
                                            ->where('amazon_item_code', '<>', '', 'and')    
                                            ->where('amazon_item_code', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else if(strpos( trim($primary_isbn), "PKEY" ) !== false ) {
                                    try{
                                        $whatIWant = substr($primary_isbn, strpos($primary_isbn, ":") + 1); 
                                        $book_id_avail = DB::table('book_details')
                                        ->where('diamond_item_code',$whatIWant)
                                        ->where('diamond_item_code', '<>', '', 'and')    
                                        ->where('diamond_item_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                }else{
                                    ##if no ggkey found,match by isbn with ISBN Physical 13
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$primary_isbn)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '')#notempty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #no book found then match with ISBN 10
                                    if(empty($book_id_avail)) {
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$primary_isbn)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found then match with ISBN Digital 13
                                    if(empty($book_id_avail)) {
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$primary_isbn)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #no book found by isbn, find by matching isbn with google gg key
                                    if(empty($book_id_avail)) {
                                        try{
                                             $book_id_avail = DB::table('book_details')
                                            ->where('google_gg_key',$primary_isbn)
                                            ->where('google_gg_key', '<>', '', 'and')    
                                            ->where('google_gg_key', '<>', '')#notempty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }
                            }#end primary_isbn
                            #Check if id exist in merged_books table in as sub_book
                            if(!empty($book_id_avail)){
                                try{
                                    $book_merge_sub_id = DB::table('merged_books')
                                    ->where('sub_book',$book_id_avail)
                                    ->value('id');
                                }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                }                    
                                #if sub id is not empty then find its main book id
                                if(!empty($book_merge_sub_id)){
                                    try{
                                        $main_merge_book_id = DB::table('merged_books')
                                        ->where('id',$book_merge_sub_id)
                                        ->value('main_book');
                                        $book_id_avail = $main_merge_book_id;  
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }                       
                                }else{
                                    #if sub id is empty
                                    $book_id_avail = $book_id_avail;
                                }
                            }
                           
                            #if book not found, collect it into the array and send into the view
                            if( empty($book_id_avail) ){
                                if( empty($primary_isbn) ){
                                    $action_to_do = "Please add any code in the csv matching with system for this book.";
                                }else{
                                    $action_to_do = "Please add or edit book with this isbn code.";
                                }
                                $books_not_found[]= [
                                'isbn_code'=> $primary_isbn,
                                'product'=> $product,
                                'vendors'=>$author,
                                'title'=>$title,
                                'action_to_do'=>$action_to_do
                                ]; 
                            }

                            #check if values already exists in the system
                            if(!empty($book_id_avail)){
                                try{
                                $check_existing_sale = DB::table('google_sale_reports')->where([
                                'book_avail_id' => $book_id_avail,
                                'added_by'=>$id,
                                'earning_date' => $earning_date,
                                'transaction_date'=> $transaction_date,
                                'google_book_id'=> $google_book_id,
                                'product'=> $product,
                                'type'=> $type,
                                'preorder'=> $preorder,
                                'quantity'=> $quantity,
                                'primary_isbn' => $primary_isbn,
                                'imprint_name'=> $imprint_name,
                                'title'=> $title,
                                'author' => $author,
                                'original_lp_currency'=> $original_lp_currency,
                                'original_lp'=>$original_lp,
                                'lp_currency'=>$lp_currency,
                                'lp_tax_inclusive'=>$lp_tax_inclusive,
                                'lp_tax_exclusive'=>$lp_tax_exclusive,
                                'country_of_sale' => $country_of_sale,
                                'publisher_revenue_percentage' => $publisher_revenue_percentage,
                                'publisher_revenue'=>$publisher_revenue,
                                'earnings_currency'=>$earnings_currency,
                                'earnings_amount'=>$earnings_amount,
                                'currency_conversion_rate'=>$currency_conversion_rate,
                                'earning_amount_conversion' => $earning_amount_conversion,
                                'custom_sale_date_google'=>$custom_sale_date_google
                                ])->get();
                                }catch(\Exception $e){
                                    return $e->getMessage();
                                }

                                #checking how many repeating sales are coming up
                                if(!$check_existing_sale->isEmpty()){
                                $already_existing[] = $check_existing_sale;
                                }

                                #if no sales are found then only add data
                                if($check_existing_sale->isEmpty()){
                                    #Save amazon data to db, collect it into the array..
                                    if(!empty($book_id_avail)){
                                    $google_csv_values[]= [    
                                    'book_id_avail' => $book_id_avail,
                                    'added_by'=>$id,
                                    'earning_date' => $earning_date,
                                    'transaction_date'=> $transaction_date,
                                    'google_book_id'=> $google_book_id,
                                    'product'=> $product,
                                    'type'=> $type,
                                    'preorder'=> $preorder,
                                    'quantity'=> $quantity,
                                    'primary_isbn' => $primary_isbn,
                                    'imprint_name'=> $imprint_name,
                                    'title'=> $title,
                                    'author' => $author,
                                    'original_lp_currency'=> $original_lp_currency,
                                    'original_lp'=>$original_lp,
                                    'lp_currency'=>$lp_currency,
                                    'lp_tax_inclusive'=>$lp_tax_inclusive,
                                    'lp_tax_exclusive'=>$lp_tax_exclusive,
                                    'country_of_sale' => $country_of_sale,
                                    'publisher_revenue_percentage' => $publisher_revenue_percentage,
                                    'publisher_revenue'=>$publisher_revenue,
                                    'earnings_currency'=>$earnings_currency,
                                    'earnings_amount'=>$earnings_amount,
                                    'currency_conversion_rate'=>$currency_conversion_rate,
                                    'csv_id'=>$csv_id,
                                    'earning_amount_conversion' =>$earning_amount_conversion,
                                    'custom_sale_date_google'=>$custom_sale_date_google
                                    ];
                                    }
                                }
                            }
                        }//end ignore blank lines
                    }//endwhile
                    #unique books which are not found
                    $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                    #no.of sales which are found in the db
                    $already_existing_count = count($already_existing);

                    #platform book is uploaded for
                    $plateform = 'google';

                    ##saving the data into array
                    if(!empty($google_csv_values)){ 
                        #count of books added
                        $uploaded_books = count($google_csv_values);
                        foreach ($google_csv_values as $csv_rows) {  
                            if(!empty($csv_rows)){
                                $google_csv_data = new google_sale_reports();
                                $google_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                $google_csv_data->added_by = $csv_rows['added_by'];
                                $google_csv_data->earning_date = $csv_rows['earning_date'];
                                $google_csv_data->transaction_date = $csv_rows['transaction_date'];
                                $google_csv_data->google_book_id =  $csv_rows['google_book_id'];
                                $google_csv_data->product =$csv_rows['product'];
                                $google_csv_data->type = $csv_rows['type'];
                                $google_csv_data->preorder = $csv_rows['preorder'];
                                $google_csv_data->quantity = $csv_rows['quantity'];
                                $google_csv_data->primary_isbn = $csv_rows['primary_isbn'];
                                $google_csv_data->imprint_name = $csv_rows['imprint_name'];
                                $google_csv_data->title = $csv_rows['title'];
                                $google_csv_data->author = $csv_rows['author'];
                                $google_csv_data->original_lp_currency = $csv_rows['original_lp_currency'];
                                $google_csv_data->original_lp = $csv_rows['original_lp'];
                                $google_csv_data->lp_currency = $csv_rows['lp_currency'];
                                $google_csv_data->lp_tax_inclusive = $csv_rows['lp_tax_inclusive'];
                                $google_csv_data->lp_tax_exclusive = $csv_rows['lp_tax_exclusive'];
                                $google_csv_data->country_of_sale = $csv_rows['country_of_sale'];
                                $google_csv_data->publisher_revenue_percentage = $csv_rows['publisher_revenue_percentage'];
                                $google_csv_data->publisher_revenue = $csv_rows['publisher_revenue'];
                                $google_csv_data->earnings_currency = $csv_rows['earnings_currency'];
                                $google_csv_data->earnings_amount = $csv_rows['earnings_amount'];
                                $google_csv_data->currency_conversion_rate = $csv_rows['currency_conversion_rate'];
                                $google_csv_data->csv_id = $csv_rows['csv_id'];
                                $google_csv_data->earning_amount_conversion = $csv_rows['earning_amount_conversion'];
                                $google_csv_data->custom_sale_date_google = $csv_rows['custom_sale_date_google'];
                                $google_csv_data->save();
                            }
                        } 
                    }else{
                        $uploaded_books = 0; #count of books added
                    }
                }catch (\Exception $e) {                      
                    return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );                  
                }
                fclose ($handle);
                if(empty($books_not_found)){ 
                    return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                }else{
                    return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                }#ends else condition
            }else{#end if upload csv
                return redirect()->route('booklist')->with("error","Please Upload again");
            }
            }else{#If ends for csv id check            
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*if conditions ends for google csv upload*/

    /*
    Comixology csv code start
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='comixology') {

            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");

            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_comixology_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');

                }catch(\Exception $e){
                    return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    fgetcsv($handle, 0, ","); // abandon first record
                    fgetcsv($handle, 0, ","); // abandon second record
                    fgetcsv($handle, 0, ","); // abandon third record
                    fgetcsv($handle, 0, ","); // abandon fourth record
                    fgetcsv($handle, 0, ","); // abandon fifth record
                    fgetcsv($handle, 0, ","); // abandon sixth record
                    fgetcsv($handle, 0, ","); // abandon seventh record
                    fgetcsv($handle, 0, ","); // abandon eight record
                    fgetcsv($handle, 0, ","); // abandon ninth record
                    fgetcsv($handle, 0, ","); // abandon tenth record
                    fgetcsv($handle, 0, ","); // abandon eleven record
                    fgetcsv($handle, 0, ","); // abandon tweleve record
                    fgetcsv($handle, 0, ","); // abandon thirteen record
                    fgetcsv($handle, 0, ","); // abandon forteen record
                    fgetcsv($handle, 0, ","); // abandon fifteen record
                    fgetcsv($handle, 0, ","); // abandon sixteen record
                    fgetcsv($handle, 0, ","); // abandon seventeen record
                    fgetcsv($handle, 0, ","); // abandon eighteen record
                    fgetcsv($handle, 0, ","); // abandon ninteen record
                    fgetcsv($handle, 0, ","); // abandon twenty record

                    try{
                        $current_rows = array();
                        ##used for storing the not found books collection
                        $books_not_found = array();
                        #collecting values 
                        $comixology_csv_values = array();
                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[1])) { #ignore blank lines
                                ##skipping rows
                                if( strpos( trim($data[0]), "Grand Total" ) !== false ){continue;}
                                ##last word : if 'total', skip row
                                $str = trim($data[1]);
                                $split = explode(" ", $str);
                                $title_lastword = $split[count($split)-1];
                                if($title_lastword == 'Total' && trim($data[2]) == ''){continue;
                                }
                                $str = trim($data[2]);
                                $split = explode(" ", $str);
                                $title_lastword = $split[count($split)-1];
                                if($title_lastword == 'Total' && trim($data[3]) == ''){
                                    continue;
                                }
                                #validating the format of the csv uploaded
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12])){
                                    if(strpos(trim($data[0]), '(blank)') !== false || (!empty(trim($data[0])) && !strtotime($data[0]) && !is_numeric($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(!empty(trim($data[1])) && !strtotime($data[1]) && !is_numeric($data[1])){
                                        $column_01 = true;
                                    }
                                    if(!empty(trim($data[2])) && !strtotime($data[2])&& !is_numeric($data[2])){
                                        $column_02 = true;
                                    }
                                    if(strtotime($data[3])){
                                        $column_03 = true;
                                    }
                                    if(!is_numeric($data[4]) && !empty($data[4])){
                                        $column_04 = true;
                                    }
                                    // if(!strtotime($data[4]) && !is_numeric($data[4]) && !empty($data[4])){
                                    //     $column_04 = true;
                                    // }
                                    if(!strtotime($data[5]) && !is_numeric($data[5]) && !empty($data[5])){
                                        $column_05 = true;
                                    }
                                    if(is_numeric($data[6])){
                                        $column_06 = true;
                                    }
                                    if(!strtotime($data[7]) && !is_numeric($data[7]) && !empty($data[7])){
                                        $column_07 = true;
                                    }
                                    if(is_numeric($data[8])){
                                        $column_08 = true;
                                    }
                                    if(is_numeric($data[9])){
                                        $column_09 = true;
                                    }
                                    if(is_numeric($data[10])){
                                        $column_10 = true;
                                    }
                                    if(is_numeric($data[11])){
                                        $column_11 = true;
                                    }
                                    if(strtotime(trim($data[12]))){
                                        $column_12 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_08) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }
                                
                                #collecting row data into the variables
                                $isbn_code = trim($data[0]);
                                $asin_code = trim($data[1]);
                                $title = trim($data[2]);
                                $csvmonth = trim($data[3]);
                                $month = date("Y-m-d", strtotime($csvmonth));
                                $country_of_sale = trim($data[4]);
                                $customer_currency = trim($data[5]);
                                $customer_price = trim($data[6]);
                                $partner_share_currency = trim($data[7]);
                                $sum_of_quantity = trim($data[8]);
                                $sum_of_post_ch_revenue_usd = trim($data[9]);
                                $sum_cover_price_usd = trim($data[10]);
                                $sum_publisher_share_usd = trim($data[11]);
                                $custom_saledate_comix = trim($data[12]);
                                $custom_sale_date_comix = date("Y-m-d", strtotime($custom_saledate_comix));

                                ##replacing dollar
                                if(strpos($sum_publisher_share_usd, '$') !== false){
                                $sum_publisher_share_usd = str_replace('$', '', $sum_publisher_share_usd);
                                }else{
                                $sum_publisher_share_usd = $sum_publisher_share_usd;
                                }

                                #Reset book_id_avail.
                                $book_id_avail = '';

                                ##finding out the corressponding book id
                                if($isbn_code != '(blank)' && !empty($isbn_code)){
                                    try{ //match with isbn physical 13
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }

                                    #if book id not found with comixology code then match the code with ISBN 10 .
                                    if(empty($book_id_avail)){
                                        try{ 
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$isbn_code)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }

                                    #if book id not found with comixology code then match the code with ISBN Digital 13 .
                                    if(empty($book_id_avail)){
                                        try{ 
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$isbn_code)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }

                                    #if book id not found with comixology code then match the code with ASIN code.
                                    if(empty($book_id_avail)){

                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('asin_code',$asin_code)
                                            ->where('asin_code', '<>', '', 'and')    
                                            ->where('asin_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found with comixology code then match the code with Amazon item code.
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('amazon_item_code',$asin_code)
                                            ->where('amazon_item_code', '<>', '', 'and')    
                                            ->where('amazon_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else{
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('asin_code',$asin_code)
                                        ->where('asin_code', '<>', '', 'and')    
                                        ->where('asin_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }

                                    #if book id not found with comixology code then match the code with Amazon item code.
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('amazon_item_code',$asin_code)
                                            ->where('amazon_item_code', '<>', '', 'and')    
                                            ->where('amazon_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }
                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    try{
                                        $book_merge_sub_id = DB::table('merged_books')
                                        ->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }                    
                                    #if sub id is not empty then find its main book id
                                    if(!empty($book_merge_sub_id)){
                                        try{
                                            $main_merge_book_id = DB::table('merged_books')
                                            ->where('id',$book_merge_sub_id)->value('main_book');
                                            $book_id_avail = $main_merge_book_id;  
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }               
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }

                                #if book not found, collect it into the array and send into the view
                                if( empty($book_id_avail) ){
                                    if( empty($asin_code) && empty($isbn_code)){
                                        $action_to_do = "Please add code in the csv matching with system for this book.";
                                    }else if( $asin_code == '(blank)' && $isbn_code == '(blank)'){
                                        $action_to_do = "Please add code instead of (blank) in the csv matching with system for this book.";
                                    }else{
                                        $action_to_do = "Please add or edit book with this codes.";
                                    }
                                    $books_not_found[]= [
                                    'isbn_code'=>$isbn_code,
                                    'asin_code'=> $asin_code,
                                    'title'=>$title,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }

                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('comixology_sale_reports')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'isbn_code' => $isbn_code,
                                        'asin_code' => $asin_code,
                                        'title' => $title,
                                        'month'=> $month,
                                        'country_of_sale'=> $country_of_sale,
                                        'customer_currency'=> $customer_currency,
                                        'customer_price'=> $customer_price,
                                        'partner_share_currency'=> $partner_share_currency,
                                        'sum_of_quantity'=> $sum_of_quantity,
                                        'sum_of_post_ch_revenue_usd' => $sum_of_post_ch_revenue_usd,
                                        'sum_cover_price_usd'=> $sum_cover_price_usd,
                                        'sum_publisher_share_usd'=> $sum_publisher_share_usd,
                                        'custom_sale_date_comix'=>$custom_sale_date_comix
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage().' '.$e->getLine();
                                    }
                                    
                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                        $already_existing[] = $check_existing_sale;
                                    }

                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $comixology_csv_values[]= [
                                            'book_id_avail' => $book_id_avail,
                                            'added_by'=>$id,
                                            'isbn_code' => $isbn_code,
                                            'asin_code' => $asin_code,
                                            'title' => $title,
                                            'month'=> $month,
                                            'country_of_sale'=> $country_of_sale,
                                            'customer_currency'=> $customer_currency,
                                            'customer_price'=> $customer_price,
                                            'partner_share_currency'=> $partner_share_currency,
                                            'sum_of_quantity'=> $sum_of_quantity,
                                            'sum_of_post_ch_revenue_usd' => $sum_of_post_ch_revenue_usd,
                                            'sum_cover_price_usd'=> $sum_cover_price_usd,
                                            'sum_publisher_share_usd'=> $sum_publisher_share_usd,
                                            'csv_id'=>$csv_id,
                                            'custom_sale_date_comix'=>$custom_sale_date_comix
                                            ]; 
                                        }
                                    }
                                }
                            }#end ignore blank lines
                        }#endwhile
                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);
                        
                        #platform book is uploaded for
                        $plateform = 'comixology';
                        
                        ##saving the data into array
                        if(!empty($comixology_csv_values)){
                            #count of books added
                            $uploaded_books = count($comixology_csv_values);
                            foreach ($comixology_csv_values as $csv_rows) {  
                                if(!empty($csv_rows)){
                                    $comixology_csv_data = new comixology_sale_reports();
                                    $comixology_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                    $comixology_csv_data->added_by = $csv_rows['added_by'];
                                    $comixology_csv_data->isbn_code = $csv_rows['isbn_code'];
                                    $comixology_csv_data->asin_code = $csv_rows['asin_code'];
                                    $comixology_csv_data->title = $csv_rows['title'];
                                    $comixology_csv_data->month =  $csv_rows['month'];
                                    $comixology_csv_data->country_of_sale =$csv_rows['country_of_sale'];
                                    $comixology_csv_data->customer_currency = $csv_rows['customer_currency'];
                                    $comixology_csv_data->customer_price = $csv_rows['customer_price'];
                                    $comixology_csv_data->partner_share_currency = $csv_rows['partner_share_currency'];
                                    $comixology_csv_data->sum_of_quantity = $csv_rows['sum_of_quantity'];
                                    $comixology_csv_data->sum_of_post_ch_revenue_usd = $csv_rows['sum_of_post_ch_revenue_usd'];
                                    $comixology_csv_data->sum_cover_price_usd = $csv_rows['sum_cover_price_usd'];
                                    $comixology_csv_data->sum_publisher_share_usd = $csv_rows['sum_publisher_share_usd'];
                                    $comixology_csv_data->csv_id = $csv_rows['csv_id'];
                                    $comixology_csv_data->custom_sale_date_comix = $csv_rows['custom_sale_date_comix'];
                                    $comixology_csv_data->save();
                                }
                            } #end foreach
                        }else{#end amazon_csv_values if
                            $uploaded_books = 0;#count of books added
                        }
                    }catch (\Exception $e) {#end try                    
                        return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );                   
                    }
                    fclose ( $handle );
                    #if there are books which are not found, redirect to the following page
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                 }else{#end if upload csv
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#If ends for csv id check            
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*if condition ends for comixology csv upload*/

    /*
    Comixology subscription csv code start
    */

        if ($request->has('csv_weekly') && $request['book_platform']=='comixology_subscription') {

            #name of csv uploaded
            $csv_name=$request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_comixology_subscription_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                    $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                    return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                fgetcsv($handle, 0, ","); // abandon first record
                fgetcsv($handle, 0, ","); // abandon second record
                fgetcsv($handle, 0, ","); // abandon third record
                fgetcsv($handle, 0, ","); // abandon fourth record
                    try{
                        $books_not_found = array();
                        #storing vales to be added in db
                        $comixology_csv_values = array();
                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while(($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ){
                            if (!empty($data[0])) {#ignore blank lines
                                ##skipping rows
                                if(empty($data[2]) && empty($data[0])){continue;}
                                if( strpos( trim($data[0]), "Total" ) !== false ){continue;}
                                #checking format is valid or not
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10])){
                                    if(!is_numeric(trim($data[0])) && !empty(trim($data[0])) && !strtotime(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(is_numeric(trim($data[2]))){
                                        $column_02 = true;
                                    }
                                    if(!is_numeric(trim($data[3])) && !empty(trim($data[3])) && !strtotime(trim($data[3]))){
                                        $column_03 = true;
                                    }
                                    if(!is_numeric(trim($data[4])) && !empty(trim($data[4])) && !strtotime(trim($data[4]))){
                                        $column_04 = true;
                                    }
                                    if(is_numeric(trim($data[5]))){
                                        $column_05 = true;
                                    }
                                    if(is_numeric(trim($data[6]))){
                                        $column_06 = true;
                                    }
                                    if(is_numeric(trim($data[7]))){
                                        $column_07 = true;
                                    }
                                    if(is_numeric(trim($data[8]))){
                                        $column_08 = true;
                                    }
                                    if(is_numeric(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                    if(strtotime(trim($data[10]))){
                                        $column_10 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_09) || !isset($column_10)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the sample csv for the same.");
                                }

                                #collecting row data into the variables
                                $title = trim($data[0]);
                                $custom_item_id = trim($data[1]);
                                $comixology_code = trim($data[2]);
                                $country = trim($data[3]);
                                $format = trim($data[4]);
                                $pages_read = trim($data[5]);
                                $revenue_per_page = trim($data[6]);
                                $total_revenue = trim($data[7]);
                                $revenue_split_rate = trim($data[8]);
                                $publisher_amount = trim($data[9]);
                                $custom_date_subs = trim($data[10]);
                                $custom_date_comix_subs = date("Y-m-d", strtotime($custom_date_subs));

                                ##replacing dollar
                                if(strpos($total_revenue, '$') !== false){
                                    $total_revenue = str_replace('$', '', $total_revenue);
                                }else{
                                    $total_revenue = $total_revenue;
                                }

                                ##replacing dollar
                                if(strpos($revenue_per_page, '$') !== false){
                                    $revenue_per_page = str_replace('$', '', $revenue_per_page);
                                }else{
                                    $revenue_per_page = $revenue_per_page;
                                }

                                ##replacing dollar
                                if(strpos($publisher_amount, '$') !== false){
                                    $publisher_amount = str_replace('$', '', $publisher_amount);
                                }else{
                                    $publisher_amount = $publisher_amount;
                                }

                                #Reset book_id_avail.
                                $book_id_avail = '';

                                ##finding out the corressponding book id
                                if(!empty($comixology_code)){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('comixology_code',$comixology_code)
                                        ->where('comixology_code', '<>', '', 'and')    
                                        ->where('comixology_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                        ->value('id');

                                    }catch(\Exception $e){
                                     return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }

                                    #if book id not found with comixology code then match the code with ISBN Physical 13.
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code',$comixology_code)
                                            ->where('isbn_code', '<>', '', 'and')    
                                            ->where('isbn_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }

                                    #if book id not found with comixology code then match the code with ISBN 10.
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$comixology_code)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }

                                    #if book id not found with comixology code then match the code with ISBN Digital 13.
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$comixology_code)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                         return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }

                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    try{
                                        $book_merge_sub_id = DB::table('merged_books')
                                        ->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }

                                    #if sub id is not empty then find its main book id
                                    if(!empty($book_merge_sub_id)){
                                        try{
                                            $main_merge_book_id = DB::table('merged_books')
                                            ->where('id',$book_merge_sub_id)->value('main_book');
                                            $book_id_avail = $main_merge_book_id;  
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }               
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }
                                #if book not found, collect it into the array and send into the view
                                if(empty($book_id_avail)){
                                    if( empty($comixology_code) ){
                                        $action_to_do = "Please add comixology code in the csv matching with system for this book.";
                                    }else{
                                        $action_to_do = "Please add or edit book with this comixology code.";
                                    }
                                    $books_not_found[]= [
                                    'comixology_code'=> $comixology_code,
                                    'title'=>$title,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }

                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('comixology_subscription_sale_reports')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'title' => $title,
                                        'custom_item_id' => $custom_item_id ,
                                        'comixology_code' => $comixology_code,
                                        'country'=> $country,
                                        'format'=> $format,
                                        'pages_read'=> $pages_read,
                                        'revenue_per_page'=> $revenue_per_page,
                                        'total_revenue'=> $total_revenue,
                                        'revenue_split_rate'=> $revenue_split_rate,
                                        'publisher_amount' => $publisher_amount,
                                        'custom_date_comix_subs'=>$custom_date_comix_subs
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage().' '.$e->getLine();
                                    }

                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }

                                    #sales are not available then only add new sales
                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon comixology data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $comixology_csv_values[]=[
                                            'book_id_avail' => $book_id_avail,
                                            'added_by'=>$id,
                                            'title' => $title,
                                            'custom_item_id' => $custom_item_id ,
                                            'comixology_code' => $comixology_code,
                                            'country'=> $country,
                                            'format'=> $format,
                                            'pages_read'=> $pages_read,
                                            'revenue_per_page'=> $revenue_per_page,
                                            'total_revenue'=> $total_revenue,
                                            'revenue_split_rate'=> $revenue_split_rate,
                                            'publisher_amount' => $publisher_amount,
                                            'csv_id'=>$csv_id,
                                            'custom_date_comix_subs'=>$custom_date_comix_subs
                                            ]; 
                                        }
                                    }
                                }
                            }#end ignore blank lines
                        }#endwhile
                       
                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);

                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                        #platform book is uploaded for
                        $plateform = 'comixology_subscription';
                        
                        ##saving the data into array
                        if(!empty($comixology_csv_values)){
                            #count of books added
                            $uploaded_books = count($comixology_csv_values);
                            foreach ($comixology_csv_values as $csv_rows) {
                                if(!empty($csv_rows)){
                                    $comixology_csv_data = new comixology_subscription_sale_reports();
                                    $comixology_csv_data->book_avail_id = $csv_rows['book_id_avail'];
                                    $comixology_csv_data->added_by = $csv_rows['added_by'];
                                    $comixology_csv_data->title = $csv_rows['title'];
                                    $comixology_csv_data->custom_item_id = $csv_rows['custom_item_id'];
                                    $comixology_csv_data->comixology_code =  $csv_rows['comixology_code'];
                                    $comixology_csv_data->country =$csv_rows['country'];
                                    $comixology_csv_data->format = $csv_rows['format'];
                                    $comixology_csv_data->pages_read = $csv_rows['pages_read'];
                                    $comixology_csv_data->revenue_per_page = $csv_rows['revenue_per_page'];
                                    $comixology_csv_data->total_revenue = $csv_rows['total_revenue'];
                                    $comixology_csv_data->revenue_split_rate = $csv_rows['revenue_split_rate'];
                                    $comixology_csv_data->publisher_amount = $csv_rows['publisher_amount'];
                                    $comixology_csv_data->csv_id = $csv_rows['csv_id'];
                                    $comixology_csv_data->custom_date_comix_subs = $csv_rows['custom_date_comix_subs'];
                                    $comixology_csv_data->save();
                                }
                            } 
                        }else{#end comixology_csv_values if condn
                            $uploaded_books = 0;#count of books added
                        }
                    }catch (\Exception $e) {  //end try                    
                        return redirect()->route('booklist')->with("error",$e->getMessage()."  booklist Line no  ".$e->getLine() );                   
                    }
                    fclose ( $handle );
                    #if there are books which are not found, redirect to the following page
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                 }else{#end if upload csv
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#If ends for csv id check            
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }/*if condition ends for comixology subscription csv upload*/

    /**
    Oni press - Shopify ( Convention )  CSV code startsv : Square
    **/
        
        if ($request->has('csv_weekly') && $request['book_platform']=='oni_shopify'){

            #name of csv uploaded
            $csv_name = $request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");

            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_oni_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){
                #if csv uploaded
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    #ignore first row
                    fgetcsv($handle);
                    try{
                        $books_not_found = array(); #listing missing books
                        $oni_csv_values = array(); #listing books to be added in db
                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[0]) && !empty($data[1])) {#ignore blank lines
                                #validating the format of csv uploaded
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12]) && isset($data[13]) && isset($data[14]) && isset($data[15]) && isset($data[16])){
                                    if(strtotime(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(!is_numeric(trim($data[1]))  && !empty(trim($data[1])) && !strtotime(trim($data[1])) ){
                                        $column_01 = true;
                                    }
                                    if(!is_numeric(trim($data[2]))  && !empty(trim($data[2])) && !strtotime(trim($data[2])) ){
                                        $column_02 = true;
                                    }
                                    if(is_numeric(trim($data[3]))  || empty(trim($data[3])) ){
                                        $column_03 = true;
                                    }
                                    if((!is_numeric(trim($data[4]))  && !strtotime(trim($data[4]))) || empty(trim($data[4])) ){
                                        $column_04 = true;
                                    }
                                     if(is_numeric(trim($data[5]))  || empty(trim($data[5])) ){
                                        $column_05 = true;
                                    }
                                    if((!is_numeric(trim($data[6]))  && !strtotime(trim($data[6]))) || empty(trim($data[6])) ){
                                        $column_06 = true;
                                    }
                                    if((!is_numeric(trim($data[7]))  && !strtotime(trim($data[7]))) || empty(trim($data[7])) ){
                                        $column_07 = true;
                                    }
                                    if(!is_numeric(trim($data[8]))  && !empty(trim($data[8])) && !strtotime(trim($data[8])) ){
                                        $column_08 = true;
                                    }
                                    if(is_numeric(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                    if(is_numeric(trim($data[10]))){
                                        $column_10 = true;
                                    }
                                    if(is_numeric(trim($data[11]))){
                                        $column_11 = true;
                                    }
                                    if(is_numeric(trim($data[12]))){
                                        $column_12 = true;
                                    }
                                    if(is_numeric(trim($data[13]))){
                                        $column_13 = true;
                                    }
                                    if(is_numeric(trim($data[14]))){
                                        $column_14 = true;
                                    }
                                    if(is_numeric(trim($data[15]))){
                                        $column_15 = true;
                                    }
                                    if(strtotime(trim($data[16]))){
                                        $column_16 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_08) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12) || !isset($column_13) || !isset($column_14) || !isset($column_15) || !isset($column_16)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }
                                #collecting row data into the variables
                                $saledate = trim($data[0]);
                                $sale_date = date('Y-m-d',strtotime($saledate));
                                $item_name = trim($data[1]);
                                $item_variation = trim($data[2]);
                                $isbn_code = trim($data[3]);
                                $diamondcode = trim($data[4]);
                                $diamond_code =str_replace(array( '(', ')' ), '', $diamondcode);
                                $upc = trim($data[5]);
                                $itune_code = trim($data[6]);
                                $asin_code = trim($data[7]);
                                $category = trim($data[8]);
                                $items_sold = trim($data[9]);
                                $gross_sales = trim($data[10]);
                                $items_refunded = trim($data[11]);
                                $refunds = trim($data[12]);
                                $discounts_comps = trim($data[13]);
                                $net_sales = trim($data[14]);
                                $tax = trim($data[15]);
                                $custom_saledate_oni_con = trim($data[16]);
                                $custom_sale_date_oni_con = date("Y-m-d", strtotime($custom_saledate_oni_con));
                                ##replacing dollar
                                if(strpos($net_sales, '$') !== false){
                                $net_sales = str_replace('$', '', $net_sales);
                                }else{
                                $net_sales = $net_sales;
                                }
                                #Reset book_id_avail.
                                $book_id_avail = '';
                                ##finding out the corressponding book id
                                #using isbn code match with ISBN Physical 13
                                if(!empty(trim($isbn_code))){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #if book id not found then match isbn with ISBN 10 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$isbn_code)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with ISBN Digital 13 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$isbn_code)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use asin code 
                                    if(empty($book_id_avail) && !empty($asin_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('asin_code',$asin_code)
                                            ->where('asin_code', '<>', '', 'and')    
                                            ->where('asin_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use diamond code 
                                    if(empty($book_id_avail) && !empty($diamond_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('diamond_item_code',$diamond_code)
                                            ->where('diamond_item_code', '<>', '', 'and')    
                                            ->where('diamond_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use itune code 
                                    if(empty($book_id_avail) && !empty($itune_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('itune_code',$itune_code)
                                            ->where('itune_code', '<>', '', 'and')    
                                            ->where('itune_code', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use upc code 
                                    if(empty($book_id_avail) && !empty($upc)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('upc',$upc)
                                            ->where('upc', '<>', '', 'and')    
                                            ->where('upc', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else if(!empty(trim($asin_code))){
                                    #if still book id not found, use itune directly
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('asin_code',$asin_code)
                                        ->where('asin_code', '<>', '', 'and')    
                                        ->where('asin_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #if still id not found, use diamond code 
                                    if(empty($book_id_avail) && !empty($diamond_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('diamond_item_code',$diamond_code)
                                            ->where('diamond_item_code', '<>', '', 'and')    
                                            ->where('diamond_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use itune code 
                                    if(empty($book_id_avail) && !empty($itune_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('itune_code',$itune_code)
                                            ->where('itune_code', '<>', '', 'and')    
                                            ->where('itune_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use upc code 
                                    if(empty($book_id_avail) && !empty($upc)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('upc',$upc)
                                            ->where('upc', '<>', '', 'and')    
                                            ->where('upc', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else if(!empty(trim($diamond_code))){
                                   #if diamond code not empty
                                    try{
                                    $book_id_avail = DB::table('book_details')
                                    ->where('diamond_item_code',$diamond_code)
                                    ->where('diamond_item_code', '<>', '', 'and')    
                                    ->where('diamond_item_code', '<>', '') //is not empty
                                    ->where('added_by',$id)
                                    ->value('id');
                                    }catch(\Exception $e){
                                    return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #if still id not found, use itune code 
                                    if(empty($book_id_avail) && !empty($itune_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('itune_code',$itune_code)
                                            ->where('itune_code', '<>', '', 'and')    
                                            ->where('itune_code', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use upc code 
                                    if(empty($book_id_avail) && !empty($upc)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('upc',$upc)
                                            ->where('upc', '<>', '', 'and')    
                                            ->where('upc', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else if(!empty(trim($itune_code))){
                                    #if itune code is not empty
                                    if(empty($book_id_avail) && !empty($itune_code)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('itune_code',$itune_code)
                                            ->where('itune_code', '<>', '', 'and')    
                                            ->where('itune_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use upc code 
                                    if(empty($book_id_avail) && !empty($upc)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('upc',$upc)
                                            ->where('upc', '<>', '', 'and')    
                                            ->where('upc', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }else {
                                    #at the end directly search with upc code
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('upc',$upc)
                                        ->where('upc', '<>', '', 'and')    
                                        ->where('upc', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                }
                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    #sub id
                                    try{
                                        $book_merge_status = DB::Table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return $e->getMessage().' book_merge_status diamond Line no.'.$e->getLine();
                                    }
                                    if(!empty($book_merge_status)){
                                    #if sub id is not empty then find its main book id
                                        try{
                                            $main_merge_book_id = DB::Table('merged_books')
                                            ->where('id',$book_merge_status)->value('main_book');
                                        }catch(\Exception $e){
                                            return $e->getMessage().' main_merge_book_id diamond Line no.'.$e->getLine();
                                        }
                                        $book_id_avail = $main_merge_book_id;
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }
                                #if book not found, collect it into the array and send into the view
                                if( empty($book_id_avail) ){
                                    if(empty($asin_code) && empty($isbn_code) && empty($diamond_code) && empty($itune_code) && empty($upc)){
                                        $action_to_do = "Please add codes in the csv for this book.";
                                    }else{
                                        $action_to_do = "Please add or edit book with one or all these provided codes.";
                                    }
                                    $books_not_found[]= [
                                    'title'=>$item_name,
                                    'isbn_code'=>$isbn_code,
                                    'diamond_code'=>$diamond_code,
                                    'itune_code'=>$itune_code,
                                    'asin_code'=>$asin_code,
                                    'upc'=>$upc,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }
                                #check if values already exists in the system
                                if(!empty($book_id_avail)){
                                    try{
                                $check_existing_sale = DB::table('oni_press_shopify_sales')->where([
                                    'book_avail_id' => $book_id_avail,
                                    'added_by'=>$id,
                                    'sale_date' => $sale_date,
                                    'item_name' => $item_name,
                                    'item_variation' => $item_variation,
                                    'isbn_code' => $isbn_code,
                                    'diamond_code' => $diamond_code,
                                    'upc' => $upc ,
                                    'itune_code' => $itune_code ,
                                    'asin_code' => $asin_code ,
                                    'category' => $category,
                                    'items_sold'=>$items_sold,
                                    'gross_sales'=>$gross_sales,
                                    'items_refunded'=>$items_refunded,
                                    'refunds' => $refunds ,
                                    'discounts_comps' => $discounts_comps,
                                    'net_sales'=>$net_sales,
                                    'tax'=>$tax,
                                    'custom_sale_date_oni_con'=>$custom_sale_date_oni_con,
                                    ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }

                                    #if no sales are found then only add data
                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $oni_csv_values[]= [
                                            'book_id_avail' => $book_id_avail,
                                            'added_by'=>$id,
                                            'sale_date' => $sale_date,
                                            'item_name' => $item_name,
                                            'item_variation' => $item_variation,
                                            'isbn_code' => $isbn_code,
                                            'diamond_code' => $diamond_code,
                                            'upc' => $upc ,
                                            'itune_code' => $itune_code ,
                                            'asin_code' => $asin_code ,
                                            'category' => $category,
                                            'items_sold'=>$items_sold,
                                            'gross_sales'=>$gross_sales,
                                            'items_refunded'=>$items_refunded,
                                            'refunds' => $refunds ,
                                            'discounts_comps' => $discounts_comps,
                                            'net_sales'=>$net_sales,
                                            'tax'=>$tax,
                                            'custom_sale_date_oni_con'=>$custom_sale_date_oni_con,
                                            'csv_id'=>$csv_id
                                            ];
                                        }
                                    }
                                }
                            }#ignore empty rows 
                        } #ends while
                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);
                        
                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);

                        #platform book is uploaded for
                        $plateform = 'oni_press_shopify';

                       ##saving the data into array
                        try{
                           if(!empty($oni_csv_values)){
                            #count of books added
                            $uploaded_books = count($oni_csv_values);
                                foreach ($oni_csv_values as $csv_rows) {  
                                    if(!empty($csv_rows)){
                                        $oni_csv_data = new Oni_press_shopify_sale();
                                        $oni_csv_data->csv_id =  $csv_id;
                                        $oni_csv_data->book_avail_id =  $csv_rows['book_id_avail'];
                                        $oni_csv_data->added_by =  $csv_rows['added_by'];
                                        $oni_csv_data->sale_date =  $csv_rows['sale_date'];
                                        $oni_csv_data->item_name =  $csv_rows['item_name'];
                                        $oni_csv_data->item_variation =  $csv_rows['item_variation'];
                                        $oni_csv_data->isbn_code =  $csv_rows['isbn_code'];
                                        $oni_csv_data->diamond_code =  $csv_rows['diamond_code'];
                                        $oni_csv_data->upc =  $csv_rows['upc'];
                                        $oni_csv_data->itune_code =  $csv_rows['itune_code'];
                                        $oni_csv_data->asin_code =  $csv_rows['asin_code'];
                                        $oni_csv_data->category =  $csv_rows['category'];
                                        $oni_csv_data->items_sold =  $csv_rows['items_sold'];
                                        $oni_csv_data->gross_sales =  $csv_rows['gross_sales'];
                                        $oni_csv_data->items_refunded =  $csv_rows['items_refunded'];
                                        $oni_csv_data->refunds =  $csv_rows['refunds'];
                                        $oni_csv_data->discounts_comps =  $csv_rows['discounts_comps'];
                                        $oni_csv_data->net_sales =  $csv_rows['net_sales'];
                                        $oni_csv_data->tax =  $csv_rows['tax'];
                                        $oni_csv_data->custom_sale_date_oni_con =  $csv_rows['custom_sale_date_oni_con'];
                                        $oni_csv_data->save();
                                    }
                                }
                            }else{#end oni_csv_values if
                                $uploaded_books = 0;#count of books added
                            }
                        }catch(\Exception $e){
                            return $e->getMessage().'Line no Oni press catch '.$e->getLine();
                        }
                    }catch(\Exception $e){
                        return $e->getMessage().'Line no Oni press catch '.$e->getLine();
                    }
                    fclose ($handle);
                    #if there are books which are not found, redirect to the following page
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                }else{#if csv uploaded condition ended
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#if csv else condition
                return redirect()->back()->with('error','Some Error Occured. Please try again later');
            }
        }#if conidition for oni press ends here*/

    /**
    Oni press - Shopify 
    **/
        
        if ($request->has('csv_weekly') && $request['book_platform']=='shopify_orignal'){

            #name of csv uploaded
            $csv_name = $request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");
            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_oni_shopify_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                return $e->getMessage()."Line no ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){ 
                #if csv uploaded
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    #ignore first row
                    fgetcsv($handle);
                    try{
                        $books_not_found = array(); #listing missing books
                        $oni_shop_values = array(); #listing books to be added in db
                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[0])) {#ignore blank lines
                                #validating the format of the csv
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5]) && isset($data[6]) && isset($data[7]) && isset($data[8]) && isset($data[9]) && isset($data[10]) && isset($data[11]) && isset($data[12])){
                                    if(!is_numeric(trim($data[0]))  && !empty(trim($data[0])) && !strtotime(trim($data[0])) ){
                                        $column_00 = true;
                                    }
                                    if(!is_numeric(trim($data[1]))  && !empty(trim($data[1])) && !strtotime(trim($data[1])) ){
                                        $column_01 = true;
                                    }
                                    if(!is_numeric(trim($data[2]))  && !empty(trim($data[2])) && !strtotime(trim($data[2])) ){
                                        $column_02 = true;
                                    }
                                    if(!empty(trim($data[3])) && !strtotime(trim($data[3])) ){
                                        $column_03 = true;
                                    }
                                    
                                    if(is_numeric(trim($data[5]))){
                                        $column_05 = true;
                                    }
                                    if(is_numeric(trim($data[6]))){
                                        $column_06 = true;
                                    }
                                    if(is_numeric(trim($data[7]))){
                                        $column_07 = true;
                                    }
                                    if(is_numeric(trim($data[8]))){
                                        $column_08 = true;
                                    }
                                    if(is_numeric(trim($data[9]))){
                                        $column_09 = true;
                                    }
                                    if(is_numeric(trim($data[10]))){
                                        $column_10 = true;
                                    }
                                    if(is_numeric(trim($data[11]))){
                                        $column_11 = true;
                                    }
                                    if(strtotime(trim($data[12]))){
                                        $column_12 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_05) || !isset($column_06) || !isset($column_07) || !isset($column_08) || !isset($column_09) || !isset($column_10) || !isset($column_11) || !isset($column_12)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the sample csv for the same.");
                                }
                                #collecting row data into the variables
                                $book_title = trim($data[0]);
                                $product_vendor = trim($data[1]);
                                $product_type = trim($data[2]);
                                $product_code = trim($data[3]);
                                $variant_sku = trim($data[4]);
                                $net_quantity = trim($data[5]);
                                $gross_sales = trim($data[6]);
                                $discounts = trim($data[7]);
                                $returns = trim($data[8]);
                                $net_sales = trim($data[9]);
                                $taxes = trim($data[10]);
                                $total_sales = trim($data[11]);
                                $custom_date = date("Y-m-d", strtotime(trim($data[12])));
                                ##replacing dollar
                                if(strpos($net_sales, '$') !== false){
                                $net_sales = str_replace('$', '', $net_sales);
                                }else{
                                $net_sales = $net_sales;
                                }
                                ##replacing dollar
                                if(strpos($gross_sales, '$') !== false){
                                $gross_sales = str_replace('$', '', $gross_sales);
                                }else{
                                $gross_sales = $gross_sales;
                                }
                                ##replacing dollar
                                if(strpos($total_sales, '$') !== false){
                                $total_sales = str_replace('$', '', $total_sales);
                                }else{
                                $total_sales = $total_sales;
                                }
                                #Reset book_id_avail.
                                $book_id_avail = '';
                                ##finding out the corressponding book id
                                #using isbn code match with ISBN Physical 13
                                if(!empty(trim($product_code))){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$product_code)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)
                                            ->value('id');

                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                    }
                                    #if book id not found then match isbn with ISBN 10 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$product_code)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with ISBN Digital 13 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$product_code)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with asin code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('asin_code',$product_code)
                                            ->where('asin_code', '<>', '', 'and')    
                                            ->where('asin_code', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with amazon code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('amazon_item_code',$product_code)
                                            ->where('amazon_item_code', '<>', '', 'and')    
                                            ->where('amazon_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with google code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('google_gg_key',$product_code)
                                            ->where('google_gg_key', '<>', '', 'and')    
                                            ->where('google_gg_key', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with itune code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('itune_code',$product_code)
                                            ->where('itune_code', '<>', '', 'and')    
                                            ->where('itune_code', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');

                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with diamond code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('diamond_item_code',$product_code)
                                            ->where('diamond_item_code', '<>', '', 'and')    
                                            ->where('diamond_item_code', '<>', '')//is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with upc
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('upc',$product_code)
                                            ->where('upc', '<>', '', 'and')    
                                            ->where('upc', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with comix code
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('comixology_code',$product_code)
                                            ->where('comixology_code', '<>', '', 'and')    
                                            ->where('comixology_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }#if condition for checking product code ends here

                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    #sub id
                                    try{
                                        $book_merge_status = DB::Table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return $e->getMessage().' book_merge_status diamond Line no.'.$e->getLine();
                                    }
                                    if(!empty($book_merge_status)){
                                    #if sub id is not empty then find its main book id
                                        try{
                                            $main_merge_book_id = DB::Table('merged_books')
                                            ->where('id',$book_merge_status)->value('main_book');
                                        }catch(\Exception $e){
                                            return $e->getMessage().' main_merge_book_id diamond Line no.'.$e->getLine();
                                        }
                                        $book_id_avail = $main_merge_book_id;
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }
                                #if book not found, collect it into the array and send into the view
                                if( empty($book_id_avail) ){
                                    if(empty($product_code)){
                                        $action_to_do = "Please add codes in the csv for this book.";
                                    }else{
                                        $action_to_do = "Please add or edit book with one or all these provided codes.";
                                    }
                                    $books_not_found[]= [
                                    'title'=>$book_title,
                                    'product_code'=>$product_code,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }
                                #check if values already exists in the system
                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('oni_real_shopify_sales')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'product_title'=>$book_title,
                                        'product_vendor'=>$product_vendor,
                                        'product_type'=>$product_type,
                                        'product_code'=>$product_code,
                                        'variant_sku'=>$variant_sku,
                                        'net_quantity'=>$net_quantity,
                                        'gross_sales'=>$gross_sales,
                                        'discounts'=>$discounts,
                                        'returns'=>$returns,
                                        'net_sales'=>$net_sales,
                                        'taxes'=>$taxes,
                                        'total_sales'=>$total_sales,
                                        'custom_date'=>$custom_date,
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }
                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }
                                    #if no sales are found then only add data
                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $oni_shop_values[]= [
                                            'book_avail_id' => $book_id_avail,
                                            'added_by'=>$id,
                                            'product_title'=>$book_title,
                                            'product_vendor'=>$product_vendor,
                                            'product_type'=>$product_type,
                                            'product_code'=>$product_code,
                                            'variant_sku'=>$variant_sku,
                                            'net_quantity'=>$net_quantity,
                                            'gross_sales'=>$gross_sales,
                                            'discounts'=>$discounts,
                                            'returns'=>$returns,
                                            'net_sales'=>$net_sales,
                                            'taxes'=>$taxes,
                                            'total_sales'=>$total_sales,
                                            'custom_date'=>$custom_date,
                                            'csv_id'=>$csv_id
                                            ];
                                        }
                                    }
                                }
                            }#ignore empty rows 
                        } #ends while 
                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);
                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);
                        #platform book is uploaded for
                        $plateform = 'oni_orignal_shopify';
                        
                        ##saving the data into array
                        try{
                           if(!empty($oni_shop_values)){
                                #count of books added
                                $uploaded_books = count($oni_shop_values);
                                foreach ($oni_shop_values as $csv_rows) {  
                                    if(!empty($csv_rows)){
                                        $oni_data = new Oni_real_shopify_sale();
                                        $oni_data->csv_id =  $csv_id;
                                        $oni_data->book_avail_id =  $csv_rows['book_avail_id'];
                                        $oni_data->added_by =  $csv_rows['added_by'];
                                        $oni_data->product_title =  $csv_rows['product_title'];
                                        $oni_data->product_type =  $csv_rows['product_type'];
                                        $oni_data->product_code =  $csv_rows['product_code'];
                                        $oni_data->variant_sku =  $csv_rows['variant_sku'];
                                        $oni_data->net_quantity =  $csv_rows['net_quantity'];
                                        $oni_data->gross_sales =  $csv_rows['gross_sales'];
                                        $oni_data->discounts =  $csv_rows['discounts'];
                                        $oni_data->returns =  $csv_rows['returns'];
                                        $oni_data->net_sales =  $csv_rows['net_sales'];
                                        $oni_data->taxes =  $csv_rows['taxes'];
                                        $oni_data->total_sales =  $csv_rows['total_sales'];
                                        $oni_data->custom_date =  $csv_rows['custom_date'];
                                        $oni_data->save();
                                    }
                                }
                            }else{#end oni_shop_values if
                                $uploaded_books = 0;#count of books added
                            }
                        }catch(\Exception $e){
                            return $e->getMessage().'Line no Oni press catch '.$e->getLine();
                        }
                    }catch(\Exception $e){
                        return $e->getMessage().'Line no Oni press catch '.$e->getLine();
                    }
                    fclose ($handle);
                    #if there are books which are not found, redirect to the following page
                    if(empty($books_not_found)){ 
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                }else{#if csv uploaded condition ended
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }else{#if csv else condition
                return redirect()->back()->with('error','Some Error Occured. Please try again later');
            }
        }#if conidition for oni press ends here*/
        
    /**
    s&s CSV code #prabh
    **/
        
        if ($request->has('csv_weekly') && $request['book_platform']=='s_s'){
            
            #name of csv uploaded
            $csv_name = $request->csv_file_weekly->getClientOriginalName();

            #validating csv
            $validator = Validator::make($request->all(), [ 'csv_file_weekly'=>'required']);
            if ($validator->fails()) {
            return redirect('/booklist')
                ->withInput()
                ->withErrors($validator);
            }

            ##format of the uploaded file
            $myextension = $request->csv_file_weekly->getClientOriginalExtension();
            $csvarray = array("csv");

            if(!in_array($myextension, $csvarray)){
                return redirect('booklist')->with("error","Please upload correct format");
            }

            ##collect token to be added into csv record table
            $csv_token = "book_s_s_".date('Y-m-d H:i:s');

            #inserting csv token in db
            try{
                $csv_data = DB::table('csv_records')->insert([
                'csv_token'=>$csv_token,
                ]);
            }catch(\Exception $e){
                return $e->getMessage()."  Line no ".$e->getLine() ;
            }

            #if value added, get the newly added id from csv record
            if($csv_data == 1){
                try{
                    $csv_id = DB::table('csv_records')->where('csv_token',$csv_token)->value('id');
                }catch(\Exception $e){
                    return $e->getMessage()."Line no s_s ".$e->getLine() ;
                }
            }

            #publisher id
            $id = Auth::user()->id;

            ##inserting the data from cvs
            if($csv_id){ 
                #if csv uploaded
                if (($handle = fopen ( $request->csv_file_weekly, 'r' )) !== FALSE) {
                    #ignore first 5 rows
                    fgetcsv($handle);
                    fgetcsv($handle);
                    fgetcsv($handle);
                    fgetcsv($handle);
                    fgetcsv($handle);
                    try{
                        $books_not_found = array(); #listing missing books
                        $ss_csv_values = array(); #listing books to be added in db
                        #checking how many are existing in db alreadt
                        $already_existing = array();
                        while (($data = fgetcsv ( $handle, 0, ',' )) !== FALSE ) {
                            if (!empty($data[0])) {#ignore blank lines
                                #to skip row with end totals
                                if(trim($data[0])=='Total' ){continue;}

                                #validating the format of the csv
                                if(isset($data[0]) && isset($data[1]) && isset($data[2]) && isset($data[3]) && isset($data[4]) && isset($data[5])){
                                    if(is_numeric(trim($data[0]))){
                                        $column_00 = true;
                                    }
                                    if(!is_numeric(trim($data[1])) && !strtotime($data[1]) && !empty($data[1])){
                                        $column_01 = true;
                                    }
                                    if(is_numeric(trim($data[2]))){
                                        $column_02 = true;
                                    }
                                    if(is_numeric(trim($data[3]))){
                                        $column_03 = true;
                                    }
                                    if(strtotime(trim($data[4]))){
                                        $column_04 = true;
                                    }
                                    if(!is_numeric(trim($data[5])) && !strtotime($data[5]) && !empty($data[5])){
                                        $column_05 = true;
                                    }
                                }
                                if(!isset($column_00) || !isset($column_01) || !isset($column_02) || !isset($column_03) || !isset($column_04) || !isset($column_05)){
                                    return redirect()->back()->with("error", "Invalid Format uploaded. Please check the  sample csv for the same.");
                                }

                                #collecting row data into the variables
                                $isbn_code = trim($data[0]);
                                $book_title = trim($data[1]);
                                $units = trim($data[2]);
                                $dollar_gr = 0;
                                $dollar_net = trim($data[3]);
                                $custom_sale_date = date("Y-m-d", strtotime(trim($data[4])));
                                $country_csv = trim($data[5]);

                                #Reset book_id_avail.
                                $book_id_avail = '';

                                ##finding out the corressponding book id
                                #using isbn code match with ISBN Physical 13
                                if(!empty(trim($isbn_code))){
                                    try{
                                        $book_id_avail = DB::table('book_details')
                                        ->where('isbn_code',$isbn_code)
                                        ->where('isbn_code', '<>', '', 'and')    
                                        ->where('isbn_code', '<>', '') //is not empty
                                        ->where('added_by',$id)->value('id');
                                    }catch(\Exception $e){
                                        return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail1".$e->getLine()); 
                                    }
                                    #if book id not found then match isbn with ISBN 10 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code2',$isbn_code)
                                            ->where('isbn_code2', '<>', '', 'and')    
                                            ->where('isbn_code2', '<>', '') //is not empty
                                            ->where('added_by',$id)
                                            ->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if book id not found then match isbn with ISBN Digital 13 in system
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('isbn_code3',$isbn_code)
                                            ->where('isbn_code3', '<>', '', 'and')    
                                            ->where('isbn_code3', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                    #if still id not found, use diamond code 
                                    if(empty($book_id_avail)){
                                        try{
                                            $book_id_avail = DB::table('book_details')
                                            ->where('diamond_item_code',$isbn_code)
                                            ->where('diamond_item_code', '<>', '', 'and')    
                                            ->where('diamond_item_code', '<>', '') //is not empty
                                            ->where('added_by',$id)->value('id');
                                        }catch(\Exception $e){
                                            return redirect()->back()->with("error",$e->getMessage()."Line no book_id_avail".$e->getLine()); 
                                        }
                                    }
                                }
                                #Check if id exist in merged_books table in as sub_book
                                if(!empty($book_id_avail)){
                                    #sub id
                                    try{
                                        $book_merge_status = DB::Table('merged_books')->where('sub_book',$book_id_avail)->value('id');
                                    }catch(\Exception $e){
                                        return $e->getMessage().' book_merge_status diamond Line no.'.$e->getLine();
                                    }
                                    if(!empty($book_merge_status)){
                                    #if sub id is not empty then find its main book id
                                        try{
                                            $main_merge_book_id = DB::Table('merged_books')
                                            ->where('id',$book_merge_status)->value('main_book');
                                        }catch(\Exception $e){
                                            return $e->getMessage().' main_merge_book_id diamond Line no.'.$e->getLine();
                                        }
                                        $book_id_avail = $main_merge_book_id;
                                    }else{
                                        #if sub id is empty
                                        $book_id_avail = $book_id_avail;
                                    }
                                }

                                #if book not found, collect it into the array and send into the view
                                if(empty($book_id_avail)  && !empty($book_title) || (empty($isbn_code)) ){
                                    if(empty($isbn_code)){
                                        $action_to_do = "Please add codes in the csv for this book.";
                                    }else{
                                        $action_to_do = "Please add or edit book with one or all these provided codes.";
                                    }
                                    $books_not_found[]= [
                                    'title'=>$book_title,
                                    'isbn_code'=>$isbn_code,
                                    'action_to_do'=>$action_to_do
                                    ]; 
                                }

                                #check if values already exists in the system
                                if(!empty($book_id_avail)){
                                    try{
                                    $check_existing_sale = DB::table('s_s_sale_reports')->where([
                                        'book_avail_id' => $book_id_avail,
                                        'added_by'=>$id,
                                        'isbn_code'=>$isbn_code,
                                        'book_title'=>$book_title,
                                        'unit_net'=>$units,
                                        'dollar_gr'=>$dollar_gr,
                                        'dollar_net'=>$dollar_net,
                                        'custom_invoice_date'=>$custom_sale_date,
                                        'country'=>$country_csv,
                                        ])->get();
                                    }catch(\Exception $e){
                                        return $e->getMessage();
                                    }

                                    #checking how many repeating sales are coming up
                                    if(!$check_existing_sale->isEmpty()){
                                    $already_existing[] = $check_existing_sale;
                                    }

                                    #if no sales are found then only add data
                                    if($check_existing_sale->isEmpty()){
                                        #Save amazon data to db, collect it into the array..
                                        if(!empty($book_id_avail)){
                                            $ss_csv_values[]= [
                                            'book_avail_id' => $book_id_avail,
                                            'added_by'=>$id,
                                            'isbn_code'=>$isbn_code,
                                            'book_title'=>$book_title,
                                            'unit_net'=>$units,
                                            'dollar_gr'=>$dollar_gr,
                                            'dollar_net'=>$dollar_net,
                                            'custom_invoice_date'=>$custom_sale_date,
                                            'csv_id'=>$csv_id,
                                            'country'=>$country_csv,
                                            ];
                                        }
                                    }
                                }
                            }#ignore empty lines ends
                        }#end while

                        #unique books which are not found
                        $books_not_found = array_unique($books_not_found, SORT_REGULAR);

                        #no.of sales which are found in the db
                        $already_existing_count = count($already_existing);
                        
                        #platform book is uploaded for
                        $plateform = 's_s_books';

                        ##saving the data into array
                        if(!empty($ss_csv_values)){
                            #count of books added
                            $uploaded_books = count($ss_csv_values);
                            foreach ($ss_csv_values as $csv_rows) {
                                if(!empty($csv_rows)){
                                    $ss_csv_data = new S_s_sale_report();
                                    $ss_csv_data->book_avail_id = $csv_rows['book_avail_id'];
                                    $ss_csv_data->added_by = $id;
                                    $ss_csv_data->isbn_code = $csv_rows['isbn_code'];
                                    $ss_csv_data->book_title = $csv_rows['book_title'];
                                    $ss_csv_data->unit_net = $csv_rows['unit_net'];
                                    $ss_csv_data->dollar_gr = $csv_rows['dollar_gr'];
                                    $ss_csv_data->dollar_net = $csv_rows['dollar_net'];
                                    $ss_csv_data->custom_invoice_date = $csv_rows['custom_invoice_date'];
                                    $ss_csv_data->csv_id = $csv_rows['csv_id'];
                                    $ss_csv_data->country = $csv_rows['country'];
                                    $ss_csv_data->save();
                                }
                            }#end foreach
                        }else{#end amazon_csv_values if
                            $uploaded_books = 0; #count of books added
                        }
                    }catch(\Exception $e){
                        return $e->getMessage()."Line no try s_s ".$e->getLine() ;
                    }
                    fclose ( $handle );
                    if(empty($books_not_found)){
                        return redirect()->route('booklist')->with("message","CSV uploaded Successfully");
                    }else{
                        return view('books_not_found', compact('books_not_found','uploaded_books','plateform','csv_name','already_existing_count'));
                    }
                }#condn for handle
                else{#end handle if
                    return redirect()->route('booklist')->with("error","Please Upload again");
                }
            }#condition for if id is inserted into csv records ends here
            else{#if csv_id ends
                return redirect()->route('booklist')->with("error","Csv not uploaded ");
            }
        }#if conidition for s&s ends here
    }#main controller ends here
}#