EMfinanga

or can I upload excel file, and search ?

@deepu07 there is no way i can copy and paste those number in once and comes in table row in all at once?

Hello, How can I search a list of number from one input form,

Example i have this list 12334 23457 3555 6666 7777 separated by space i want to paste on my search input form and and click search button to get those number

Keep in mind that those number are found in one table from DB

Someone can help me

you can try this https://adminlte.io/themes/AdminLTE/index2.html, is good

this is works

              $stockcount= count($stocks);
            $totalPid = count($data['pid']);
            $totalQty = count($data['qty']);
            $totalTruckNumber = count($data['truckNumber']);

            $remain=$totalPid-$stockcount;
    // dd($remain);

            for($i=$stockcount; $i<$totalPid; $i++){      
                Stock::create([
                    'pid' => $request->pid[$i],
                    'qty' => $request->qty[$i],
                    'inv_no' => $request->inv_no,
                    'user_id' => Auth::user()->id,
                    'Indate'=>$date,
                    'supplierName' => $request->supplierName,
                    'receiptNumber' => $request->receiptNumber,
                    'truckNumber' => $request->truckNumber[$i],
                    'driverName' => $request->driverName,
                    'remark' => $request->remark,
                ]);
            }

No, i like to pass the massage in blade view

this is works

              $stockcount= count($stocks);
            $totalPid = count($data['pid']);
            $totalQty = count($data['qty']);
            $totalTruckNumber = count($data['truckNumber']);

            $remain=$totalPid-$stockcount;
    // dd($remain);

            for($i=$stockcount; $i<$totalPid; $i++){      
                Stock::create([
                    'pid' => $request->pid[$i],
                    'qty' => $request->qty[$i],
                    'inv_no' => $request->inv_no,
                    'user_id' => Auth::user()->id,
                    'Indate'=>$date,
                    'supplierName' => $request->supplierName,
                    'receiptNumber' => $request->receiptNumber,
                    'truckNumber' => $request->truckNumber[$i],
                    'driverName' => $request->driverName,
                    'remark' => $request->remark,
                ]);
            }

how sir @snapey

this is like id so there is the invoice number i passed there

       	Route::get('stock/show/{inv_no}', '[email protected]');

This return is works but the massage success does not display, what can i change

            return  $this->show($inv_no)->with('success', 'GRN  Edited Successfully ');

@bobbybouwmann array[0,1,2] are in DB, but array[3,4] i want to added in DB

So my logic is the exist array can updated and additional array can be added

            array:10 [▼
              "_token" => "GBMOuQQrICXXO8DvV8FoV35VFKWuJXEok1TlF1o7"
              "inv_no" => "GRN3105200005"
              "supplierName" => "dar"
              "receiptNumber" => "98799x"
              "driverName" => "DD"
              "remark" => "OK"
              "Indate" => "2020-05-31"
              "pid" => array:5 [▼
                0 => "1"
                1 => "2"
                2 => "3"
                3 => "4"
                4 => "5"
              ]
              "truckNumber" => array:5 [▼
                0 => "T 640"
                1 => "T 678"
                2 => "T 220 DRS"
                3 => "T 155"
                4 => "T 640"
              ]
              "qty" => array:5 [▼
                0 => "60"
                1 => "55"
                2 => "40"
                3 => "4"
                4 => "5"
              ]
            ]

ok @bobbybouwmann

Join Zoom Meeting https://zoom.us/j/91275984788?pwd=MG5VeVZLbFNGdHJSVFlLekRZYzdkZz09

Meeting ID: 912 7598 4788 Password: 1jcLBM

click that link to join sir

@bobbybouwmann no am just share the link for zoom here to connect in my pc and to show you

@bobbybouwmann may i show the my how it works in my end ?

I can share on my zoom

@bobbybouwmann am tried to use count but it update existing data and save one row while i have added more than one rows.

But if i loop i think it can duplicate the data ?

I have an invoice form so I want to Edit/update row which are exist and add new rows of the same invoice number

this code is update existing one and add one row , but if i add more than one rows does not added all its update exist and add new one .

so i want to update exist and add new all rows which is additional

          public function update(Request $request, $inv_no)
      {

   $data = $request->all();

    $date = date ("Y-m-d",  strtotime($request->Indate));

   $stocks = Stock::where('inv_no', $inv_no)->get();
    $i = 0;

      foreach ($stocks as $stock) { 
      Stock::where('inv_no', $inv_no)
        ->where('id', $stock->id)
        ->update([
            'pid' => $request->pid[$i],
            'qty' => $request->qty[$i],
            'user_id' => Auth::user()->id,
            'Indate'=>$date ,
            'supplierName' => $request->supplierName,
            'receiptNumber' => $request->receiptNumber,
            'truckNumber' => $request->truckNumber[$i],
            'driverName' => $request->driverName,
            'remark' => $request->remark,
        ]);

             $i++;
    }

    $stockcount= count($stocks);
    $totalPid = count($data['pid']);
    $totalQty = count($data['qty']);
    $totalTruckNumber = count($data['truckNumber']);
   // dd($request->pid[$totalPid-1]);

     if ($stockcount < $totalPid ) {

       
        Stock::create([
            'pid' => $request->pid[$totalPid-1],
            'qty' => $request->qty[$totalQty-1],
            'inv_no' => $request->inv_no,
            'user_id' => Auth::user()->id,
            'Indate'=>$date,
            'supplierName' => $request->supplierName,
            'receiptNumber' => $request->receiptNumber,
            'truckNumber' => $request->truckNumber[$totalTruckNumber-1],
            'driverName' => $request->driverName,
            'remark' => $request->remark,
        ]);
     
       }

Mobile app

I need an advice i have the laravel project app and i want to design the same on android app

What frame work mobile app i can use it because i dont know much knowledge of android studio

I have a edit form which edit an array like (Invoice) , I want when i edit the Invoice_no a can also able to add the new item on the same invoice number

Example the Inv_no=300 and its carry 3 arrays ([... .... ... ]), so I want to add the new one array to be 4 arrays.

On my code below is update those 3 array data but if i add new one does not save that new one,

what can i do here to add the new one data

          public function update(Request $request, $inv_no)
      {
     $data = $request->all();
      // dd($data);

     $stocks = Stock::where('inv_no', $inv_no)->get();
        $i = 0;

           foreach ($stocks as $stock) {  
                    Stock::where('inv_no', $inv_no)
                   ->where('id', $stock->id)
                    ->update([
                      'pid' => $request->pid[$i],
                     'qty' => $request->qty[$i],
                    'inv_no' => $request->inv_no,
                   'user_id' => Auth::user()->id,
                  'Indate'=>$request->Indate,
                  'supplierName' => $request->supplierName,
                 'receiptNumber' => $request->receiptNumber,
                'truckNumber' => $request->truckNumber,
               'driverName' => $request->driverName,
               'remark' => $request->remark,
             ]);

            $i++;
      }

       return $this->index();
      }

@michaloravec your right but i modify instead of Product::with('category') change like this DB::table('products')

How can i sort data in alphabetical, in my code it sort according to categories_id

        public function itemsprint()
        {
       $itemsList=Product::with('category_')
               ->orderBy('category_id','asc')
               ->get();

          $pdf = PDF::loadView('items.print',compact('itemsList'));

        return $pdf->download('product list.pdf'); 
       }

Does not works get the same error

hellow I want to get the logic to find the previous data in date range

Example we fetch data from 2020-04-01 to 2020-05-02, so want to get the data from previous this range

anyone can help the logic from here

this query is just return the data of date range, but not previous

                public function stock_by_period(Request $request){
                       $from = date('Y-m-d', strtotime($request->from);
                      $to = date('Y-m-d', strtotime($request->to)

                     $data=Loading::whereBetween('reservation_from', [$from, $to])->get();

                       dd($data);
                 }

@willvincent , have you know what was the problem here?

@willvincent , i get this error

              SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select `products`.`id`, 
           `products`.`name`, ifnull(sum(stocks.qty),0) as sqty_curr, ifnull(sum(loadings.qty),0) as lqty_curr, 
          `products`.`open_stock`, ifnull(sum(ifnull(sum(stocks.qty),0) + open_stock - 
          ifnull(sum(loadings.qty),0)),0) as CB from `products` inner join `stocks` on `stocks`.`pid` = 
      `products`.`id` inner join `loadings` on `loadings`.`pid` = `products`.`id` where `stocks`.`Indate` 
        between 2020-05-04 00:00:00 and 2020-05-10 23:59:59 and `loadings`.`Outdate` between 2020-05-04 
        00:00:00 and 2020-05-10 23:59:59 group by `products`.`id`)

because i want to display for each items for current week that why i used get()

      $weekdata_in=DB::table('products')
    ->select(DB::raw('ifnull(sum(stocks.qty),0) as sqty_curr'),'products.name as name')
    ->join('stocks', 'stocks.pid','=','products.id')
     ->whereBetween('stocks.Indate', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
     ->get();

    $weekdata_out=DB::table('products')
    ->select(DB::raw('ifnull(sum(loadings.qty),0) as lqty_curr'),'products.name as name')
    ->join('loadings', 'loadings.pid','=','products.id')
    ->whereBetween('loadings.Outdate', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
    ->get();

@bugsysha , for the first id is works fine

but how for each id because i want to calculate for each id

@bugsysha , your advice is good but sorry can you advice this one

https://laracasts.com/discuss/channels/eloquent/modify-sql-query-to-eloquent-query

see my last comment

@jlrdw , i tested in my side example this i want to calculate the closedbalance but i get this error

         $open=Product::select('open_stock')->where('id',6)->first();
       // dd($open);

    $data= Stock::select(DB::raw('sum(qty) as sqty'),'pid','Indate')
    ->whereBetween('Indate', [$start_week, $end_week])
  ->where('pid',6)
   ->get();

   $data2= Loading::select(DB::raw('sum(qty) as lqty'),'pid','Outdate')
    ->whereBetween('Outdate', [$start_week, $end_week])
    ->where('pid',6)
     ->get();

      $closedbalance= $data->sqrty + $open->open_stock - $data2->lqrty;

   dd($closedbalance);

error

                  Property [sqty] does not exist on this collection instance.

can you give the way how i can do calculation like that?

@jlrdw , it works but sometime am confused to join with another eloquent, example I want to get data in Current week in mysql is not easy than eloquent as use on carbon\carborn

so that why i asking if there is the way i can use the same way as eloquent

I need a help to modify my sql query to eloquent query, i fetch data from two table here products and Loadings

              SELECT products.id,products.name as name,
	(
	(products.open_stock)
	+(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) 
           <= MONTH(CURRENT_DATE())-1)
	- (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and 
       MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())-1)
	) 
	as open_balance

	from products where  products.id= $id"

@bugsysha ok brother, thank you. let me follow you advice well

I passed the closed balance in the blade as you can see on the blade view, but when i do calculation i get this error

           Undefined variable: dataload (View: C:\xampp\htdocs\pacl- 
           inventory\resources\views\report\item_date_wise.blade.php)

@bugsysha,

i managed to do like this, but i faced one challenge of calculation to get closed balance of each rows

                <?php $newCB=0; ?>
              @foreach($open_balance_prev as $dt)
          <tr>
            <td>Open balance</td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>{{$dt->open_balance}}</td>
          
          </tr>
          @endforeach
          
          @foreach($item_load_list_cur_month as $data)
          <tr>
            <td>{{$data->name}}</td>
            <td></td>
            <td>{{$data->Outdate}}</td>
            <td></td>

            <td>{{$data->QtyOut ??0 }}</td>
            <!-- get the error here can not define $dataload->QtyIn -->
            <td>{{$newCB= $dt->open_balance + 0 - $data->QtyOut ??0 }}</td>

          </tr>
          @endforeach

           @foreach($item_stock_list_cur_month as $dataload)
          <tr>
            <td></td>
            <td>{{$dataload->Indate}}</td>
            <td></td>

            <td>{{$dataload->QtyIn ??0 }}</td>
            <td></td>
             
            <td>{{$newCB= $dt->open_balance + $dataload->QtyIn - $data->QtyOut ??0 }}</td>
          </tr>
          @endforeach
        </tbody>
      </table>

if you see there is $newCB this calculate the new closed balance of each row

@bugsysha , ok in case your free you can join, today is Up,, then i can show you something

https://zoom.us/j/98985948454?pwd=K043Z2xKSW5MRGpaWUFmeUE2OHhNQT09

@bugsysha , Ok so how can i looping in the blade view that query, can you show me the example

Join Zoom Meeting https://zoom.us/j/98985948454?pwd=K043Z2xKSW5MRGpaWUFmeUE2OHhNQT09

Meeting ID: 989 8594 8454 Password: 6uXzxe

ok wait a moment i make new one

https://zoom.us/wc/96547826267/start

@bugsysha i have this variable in my blade view,

Let me tell you my full concept,

I have 3 tables products, loadings and stock so, i want to select all record which are saved in this month where id=6 or any

After That i design the blade view, to show a report like this

Details. DateIn. DateOut. QtyIn. QtyOut. Closed balance Open balance. 100

                           02/05/2020.                        20.           Null.                 120
                                                   03/05/2020.  Null.        40.                   80

@bugsysha , it says

Undefine: Variable name

@bugsysha is there any way you can use it in the blade view?

@bugsysha , i tried like this but when i run foreach at blade view i get some error

      $open_balance_prev=DB::select(DB::raw(" SELECT products.id,products.name as name,
	(
	(products.open_stock)
	+(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE())-1)
	- (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())-1)
	) 
	as open_balance

	from products where  products.id= $id"));


$item_load_list_cur_month=DB::table('products')
->select('loadings.qty as QtyOut','loadings.Outdate','products.name as name','stocks.qty as 
       QtyIn','stocks.Indate')
->join('loadings', 'loadings.pid' ,'=','products.id')
->join('stocks', 'stocks.pid' ,'=','products.id')
->whereYear('loadings.Outdate', Carbon::now()->year)
->whereMonth('loadings.Outdate', Carbon::now()->month)
->whereYear('stocks.Indate', Carbon::now()->year)
->whereMonth('stocks.Indate', Carbon::now()->month)
->where('products.id','=', $id)
->GroupBy('loadings.Outdate','stocks.Indate')
->get();
$item_date_wise=$item_load_list_cur_month->merge( $open_balance_prev);

return view('report.item_date_wise',compact('item_date_wise'));

@bugsysha , i don't know the logic to join

I have three query, and i want help to merge to have one, and order by Indate and Outdate as date

      $item_load_list_cur_month=DB::table('products')
        ->select('loadings.qty','loadings.Outdate','products.name')
        ->join('loadings', 'loadings.pid' ,'=','products.id')
          ->whereYear('loadings.Outdate', Carbon::now()->year)
          ->whereMonth('loadings.Outdate', Carbon::now()->month)
        ->where('loadings.pid','=', 6)
        // ->OrderBy('loadings.Outdate','ASC')
        ->get();

        $item_stock_list_cur_month=DB::table('products')
           ->select('stocks.qty','stocks.Indate','products.name')
           ->join('stocks', 'stocks.pid' ,'=','products.id')
                ->whereYear('stocks.Indate', Carbon::now()->year)
                ->whereMonth('stocks.Indate', Carbon::now()->month)
                ->where('stocks.pid','=', 6)
        ->get();

        dd($item_stock_list_cur_month);
      $open_balance_prev=DB::select(DB::raw(" SELECT products.id,products.name, 
       (
         (products.open_stock)
         +(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and 
            MONTH(stocks.Indate) <= MONTH(CURRENT_DATE())-1)
         - (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and 
          MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())-1)
       ) 
        as open_balance

          from products where  products.id= 6"));

In short Am expecting to get all the data which are entered in loading table and stock table by specific id where created at this month and order by date or merge by date At the end i will display like that above

@bobbybouwmann , Not,because this data displayed total of each product of the current month 05

But what i need is to select the specific pid and show how many are in the loading and how many are in the stock but order by date

like this for example i just modify my query like this:

                 $stockSummary=DB::table('products')
      ->select(['products.id','products.name','open_stock'])
          ->addSelect([
	    'total_stockIn' => DB::table('stocks')
              // ->selectRaw('stocks.Indate')
	   ->selectRaw('sum(ifnull(`stocks`.`qty`,0))')
	   ->where('stocks.pid', '=', 'products.id')
	    ->whereMonth('stocks.Indate',date('m')),
             // ->whereDate('stocks.created_at', '=', Carbon::today()),

	   'total_loadings' => DB::table('loadings')
                // ->selectRaw('loadings.Outdate')
	    ->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
	     ->where('loadings.pid', '=', 6)
	    ->whereMonth('loadings.Outdate',date('m')),

	    'load_date' =>DB::table('loadings')
	     ->selectRaw('loadings.Outdate')
	     ->where('loadings.pid', '=', 6)
	      ->whereMonth('loadings.Outdate',date('m')),
                // ->whereDate('loadings.created_at', '=', Carbon::today()),
	     'Stock_date' =>DB::table('stocks')
	     ->selectRaw('stocks.Indate')
	      ->where('stocks.pid', '=', 6)
	      ->whereMonth('stocks.Indate',date('m')),

	      'total_stock_jana' => DB::table('stocks')
	      -> selectRaw('sum(ifnull(`stocks`.`qty`,0))')
	      ->where('stocks.pid', '=', 6)
               // ->whereMonth('stock.Indate',date('m')-1),
	     ->whereDate('stocks.Indate', '=', Carbon::yesterday()),

	     'total_loadings_jana' => DB::table('loadings')
	     ->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
	      ->where('loadings.pid', '=', 6)
                 // ->whereMonth('loadings.Outdate',date('m')-1),
	     ->whereDate('loadings.Outdate', '=', Carbon::yesterday()),
                  ])

           ->OrderBy('load_date','Stock_date')
        ->get();
        dd($stockSummary);
 

output after run dd()

    Illuminate\Support\Collection {#445 ▼
         #items: array:1 [▼
           0 => {#441 ▼
          +"id": 6
            +"name": "Angle Iron 1x3mm(25x25x3mm)"
           +"open_stock": 119
            +"total_stockIn": null
           +"total_loadings": "60"
           +"load_date": "2020-05-01"
          +"Stock_date": "2020-05-02"
         +"total_stock_jana": "50"
           +"total_loadings_jana": null
             }
           ]
         }

what i need is to fetch the data from that two table loading and stock and order by date load_date and stock_date as date in this current month as per shown listed below.

this is breakdown of pid=6 in this month

            Date                      open_stock               total_stockIn        total_loadings 
            ----------------------------------------------------------------------------------------------------
             2020-05-01            119                            null                            60
             2020-05-02              100                           50                            null
            2020-05-03                null                          null                         null

if you don't mind i can show you in my laptop emma4082@gmail.com, you can help me as well.

@bobbybouwmann No, No

this query works like this for current month

           items                  openbalance             StockIn                LoadOut           closedBalance
           ---------------------------------------------------------------------------------------------------------------------
           p1                              10                           20                         10                        10
           p2                                0                           20                         10                        10
           p3                              10                           0                            10                        0

now i want to select example P1 and get what qty are in and out and closed balance order by date