EMfinanga

i tried this but got the error

                     $balanceIn=CashSales::with('items')
                       ->select('item_id',
                        DB::raw('sum(quantity)  as tqty whereIn(l_type,["credit_sale,cash_sale"])'),
                        DB::raw('sum(quantity)  as  tqty1 whereIn( l_type , ["open balance,purchase"]'))
                    ->groupby('item_id')
                    ->get();

error

               Illuminate\Database\QueryException
             SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check 
                  the manual that corresponds to your MariaDB server version for the right syntax to use near 
            'whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 whereIn( l_t' at line 1 (SQL: select 
                      `item_id`, sum(quantity) as tqty whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 
               whereIn( l_type , ["open balance,purchase"] from `cash_sales` group by `item_id`)

$balanceIn=CashSales::with('items')

                    ->select('item_id',
                        DB::raw('sum(quantity)  as tqty whereIn(l_type,["credit_sale,cash_sale"])'),
                        DB::raw('sum(quantity)  as  tqty1 whereIn( l_type , ["open balance,purchase"]'))
                    ->groupby('item_id')
                    ->get();

I GOT THE ERROR

                  Illuminate\Database\QueryException
                SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
               check the manual that corresponds to your MariaDB server version for the right syntax to use near 
            'whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 whereIn( l_t' at line 1 (SQL: select 
         `item_id`, sum(quantity) as tqty whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 
            whereIn( l_type , ["open balance,purchase"] from `cash_sales` group by `item_id`)

@sinnbeck , it return only one products

if possible is can share my screen -emma4082@gmail.com

Once you switch to first() you will not get collections but single instances of CashSales.

YES, this is not get collection

what can i do to get the collection according to my query, can you modify it? @bugsysha

@bugsysha , YES

           $balanceQty=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty'))
                    ->whereIn('l_type',['credit_sale','cash_sale'])

                    ->groupby('item_id')
                    ->first();

        $balanceQty1=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty1'))
                    ->whereIn('l_type',['open balance','purchase'])

                    ->groupby('item_id')
                    ->first();

                    $data=$balanceQty->merge($balanceQty1);
                    

         $balanceQtyOut=(int)$balanceQty1->tqty1-(int)$balanceQty->tqty;

       // dd($data);

        return view('dashboard.index',compact('totalCredit','totalCashsale','balanceQtyOut','data'));

im using the marge, and im gettting that error

when i changed to first(), i get another error

       BadMethodCallException
     Call to undefined method App\CashSales::merge()

i want to have one foreach loop in blade view after combine this query

          $balanceQty=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty'))
                    ->whereIn('l_type',['credit_sale','cash_sale'])

                    ->groupby('item_id')
                    ->get();

      $balanceQty1=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty1'))
                    ->whereIn('l_type',['open balance','purchase'])

                    ->groupby('item_id')
                    ->get();

                    $data=$balanceQty->merge($balanceQty1);

       $balanceQtyOut=(int)$balanceQty1->tqty1-(int)$balanceQty->tqty;

i get this error

       Property [tqty1] does not exist on this collection instance

someone can assist me

@wraith , what do you think if we can create product as open balance on sales table instead of have itemstock table?,

kindly advice

@wraith, see the way i did on my app, and you can advice accordingly

i did like this because i have two store(Shop and Warehouse),then i created two store on store table:

Then i just created the products on products tables with categories.

Then on itemstock table, i just select the products name and store name, quantity, rate, and amount. and save it on itemstock table.

On this itemstock table is an open stock of each products.

Then i have sales table: from here i have a list of vouchers (Cash sales, credit sales, purchase) ,

then when you want to perform a cash sale, you will select the products name from product tables and select store , quantity , rate and Amount and save the data. And the same as credit sales and purchase, that why you can see on my table sales i have

Sales table
         
          id
          Vch_no
          l_type- default value
          Item_id- reference is products
          Qnty
          Rate
          amount
  

So that why i want to join there tables, then to evaluate one by one product(item_id)

I want to select product from the following tables,

Products

    Id
    Name
    Category_id

Itemstock

    Id
    Item_id
    Qty
    Rate
    amount

Sales

       Id
      Item_id
       Sale_type
       Qty
       rate
      Amount

I want to select product id=3 which found on those table.

What i try

     Function item_summary(Request,$request){

      $sale = Sale:: with(items)->where(item_id, $id)

}

I dont get the data in itemstock table, how can i modify this code?

i have that column "l_type" which have credit_sale,purchase,cash_sale, but both we have qnt, rate and amount

how can i apply this statement in my tag as my code below, or what is the best way i can use this

                 @foreach($inwards as $key=>$dt)
           
            <tr>
              <td>{{$dt->created_at->format('Y-m-d')}}</td>
              <td>{{$dt->vch_no}}</td>
              <td>{{$dt->l_type}}</td>
              <td>{{$dt->items->name}}</td>


        @if($dt->l_type == "purchase")
              <td>{{$dt->quantity}}</td>
              <td>{{$dt->rate}}</td>
              <td>{{$dt->amount}}</td>
            
             //i want to display data here which this column is much
          @elseif($dt->l_type == "credit_sale" and "cash_sale")
              <td>{{$dt->quantity}}</td>
              <td></td>

              <td>{{$dt->rate}}</td>
              <td>{{$dt->amount}}</td>
             @endif
              <td></td>
              <td></td>
              <td></td>
              <td></td>
              
            </tr>

hello how can i write this sql in laravel

                      SELECT `created_at`,`l_type`,`vch_no`,`item_id`,`quantity`,`rate`,`amount` FROM 
                    `cash_sales` WHERE `l_type`in ('credit_sale','cash_sale') AND item_id=3

i try this but got error

                  public function stock_summary(Request $request){

                  $inwards=CashSales::with('customer_cash','items','store')
                 ->SELECT( `created_at`,`l_type`,`vch_no`,`item_id`,`quantity`,`rate`,`amount`) 
                 ->WhereIn( `l_type`, 'credit_sale','cash_sale')
                 ->where('item_id', $id)
                ->get();

error

             Symfony\Component\Debug\Exception\FatalThrowableError
                  Argument 1 passed to Illuminate\Database\Query\Builder::cleanBindings() must be of the type 
                 array, string given, called in 
                  
             C:\xampp\htdocs\accounts\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php on line 907

                        return view('stock_summary.show',compact('inwards'));
                  }

Thanks @tisuchi

Example i created a sales now

   Created_at 2019-12-07 07:59:29

While Current timestamp

            2019-12-07 11:03:20

I have a table with timestamps column (created_at and updated_at) in mySql database. I am using laravel version 5.4 and php 7.0.

I happened to notice that it is adding incorrect time to this particular column.

How to set this column to set timestamp correct

@tisuchi

There is no problem, but i want to Evaluate one items by join those tables.

Example items 4 I want to know how many qnty in ward and out ward.

@tisuchi is not working i got the error

       SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'cash_sales' (SQL: select 
              `product`.`name`, `credit_sales`.`vch_no`, `cash_sales`.`vch_no`, `credit_sales`.`quantity`, 
      `cash_sales`.`quantity`, `credit_sales`.`rate`, `cash_sales`.`rate`, `cash_sales`.`rate`, ` 
      credit_sales`.`amount`, `cash_sales`.`amount`, `itemstocks`.`quantity`, `itemstocks`.`rate`, 
       `itemstocks`.`amount`, `itemstocks`.`created_at`, `credit_sales`.`created_at`, 
  `cash_sales`.`created_at` from `cash_sales` inner join `products` on `credit_sales`.`item_id` = 
     `products`.`id` inner join `cash_sales` on `products`.`id` = `cash_sales`.`item_id` inner join 
  `itemstocks` on `products`.`id` = `itemstocks`.`item_id` where `product`.`id` = 4)

@snapey , i need the simply way and correct so you can help me

on my model i used like this

                   class CashSales extends Model
           {

       protected $fillable = [
         'vch_no',
        'customer_cash_id',
        'item_id',
        'store_id',
        'quantity',
        'rate',
      'amount'];

        public function customer_cash(){

      return $this->belongsTo('App\Customer_cash','customer_cash_id','id');
      }

     public function items(){

       return $this->belongsTo('App\Products','item_id','id');
      }

        public function store(){

        return $this->belongsTo('App\Store','store_id','id');
      }
    }



      

class Itemstock extends Model { protected $fillable = [

    'item_id',
      'store_id',
     'quantity',
      'rate',
     'per',
    'amount'];

    public function Items(){

    return $this->belongsTo('App\Products','item_id','id');
     }


   public function store(){
    return $this->hasOne('App\Store','id','store_id');
     }


         }



 class Products extends Model
 {
  protected $fillable = [ 'name', 'category_id'];


  public function category(){
    return $this->hasOne('App\Category','id','category_id');
  }

  }

i tried this on sql, but i don't know if is correct or not how i can put in laravel

                  SELECT c.name,a.`vch_no`,b.vch_no,a.quantity,b.quantity,a.rate,b.rate,b.rate,
               a.amount,b.amount,d.quantity,d.rate,d.amount,d.created_at,a.created_at,b.created_at
              FROM credit_sales a, cash_sales b, products c ,itemstocks d
            WHERE a.`item_id` = c.id
            AND b.`item_id` = c.id
           AND d.`item_id` = c.id
            AND c.id=4

hello Team i have 4 tables, i design a sales system and inventory, i want to evaluate the items

            1.  Items
                -id
                -name
                - category_id

         2. Credit_sales
                 -item_id
                 -customer_credit_id
                 -rate
                 -vch_no
                 -quantity
                 -amount
              
          3. Cash_sales
                 -item_id
                 -store_id
                 -customer_cash_id
                 -rate
                 -vch_no
                 -quantity
                 -amount

         4. itemsstock
                  -item_id
                 -store_id
                   -rate
                  -quantity
                 -amount

i want to select the id=3 then to get this format output

                   item_id
                   vch_no
                 -rate
                 -quantity
                 -amount
                 

Anyone can help me

@ryanooi no no, there is no need to have many to many relationship, you need to make a pivot table

Noted, but remember i have two table here as you can seen on my code so kindly can you advice me clear on this ?

No no

This code below is for create data into database

I want to create the code for update

 $data=$request->all();
    $lastid=Customer_cash::create($data)->id;
    if(count($request->item_id) > 0)
    {
        foreach($request->item_id as $key=>$v){
            $data2=([
                'Cash_no'=>$fk,
                'customer_cash_id'=>$lastid,
                'item_id'=>$request->item_id [$key],
                'store_id'=>$request->store_id [$key],
                'quantity'=>$request->qty [$key],
                'rate'=>$request->selling_price [$key],
                'amount'=>$request->amount [$key],
            ]);

            CashSales::create($data2);
             }
    return redirect()->back()->with('success', 'Successfully Stored');

    }

ok, but what if i can apply a alert like do you want to print YES/NO, if i click yes button it direct me on printed page

how can i archive this ?

hello, i want the concept after save the data i want to get the pop up asking me to print invoice.

this is what i done.

$data=$request->all();
        $lastid=Customer_cash::create($data)->id;
        if(count($request->item_id) > 0)
        {
            foreach($request->item_id as $key=>$v){
                $data2=([
                    'Cash_no'=>$fk,
                    'customer_cash_id'=>$lastid,
                    'item_id'=>$request->item_id [$key],
                    'store_id'=>$request->store_id [$key],
                    'quantity'=>$request->qty [$key],
                    'rate'=>$request->selling_price [$key],
                    'amount'=>$request->amount [$key],
                ]);

                CashSales::create($data2);
                 }
        return redirect()->back()->with('success', 'Successfully Stored');

        }

this code is working fine for save data, but i want to add an idea to print the docs

@tray2 , i want to archive during create items i need to able to allocate that items quantity either shop store or warehouse

kindly i want a idea how can i do this in MY ACCOUNTS app as per below info.

i want to have an option to create items with store, lets say items 1 has a quantity -200 pcs, so i want to locate 100 pc to store 1 and 100 pc to store 2 in my DB

so i want an idea to relate the stock items table and store table.

keep in mind that i want store can be an option that means you can direct that pc to that store or not

i want to excute the report of sales, to get all data,

example this is the query i used now

public function salesreport(Request $request) {

$sales = Sales::select("sales.*")
->whereBetween('created_at', [$request->input('from'), $request->input('to')])->get();

return view('reports.salesreport',compact('sales')); }

how can i create a query to select data by day, week, month and year?

thank you @sinnbeck

someone can assist me the query to select data range by date.

i want a user input a date., How can i rebuild my query?

public function salesreport()
{

    $sales = Sales::select("sales.*")
    ->whereBetween('created_at', ['2019-09-01', '2019-09-10'])->get();

return view('reports.salesreport'); }

@braunson this is what i tried its work but does not check the quantity so i want to apply that logic

for ($a=0; $a < count($ids); $a++) { //create detail on data faktur

$ido=$request->name[$a];
$store=$request->store[$a];

$model=([
    'invoice_no'=>$fk, 
    'customer_id'=>$request->customer_id, 
    //'product_code'=>$request->product_code[$a], 
    'name'=>$request->name[$a],
    'store'=>$request->store[$a],
    'selling_price'=>$request->selling_price[$a], 
    'amount'=>$request->amount[$a],
    'qty'=>$request->qty[$a], 
]);
//update stok
 $data=Stock::select('quantity','store')->where([
                ['id', '=', $ido],
                ['store', '=', $store],
                  ])->first();
// apply  conditions
//check if available

$check=(int)$data->quantity < (int)$request->qty[$a];
dd($data);
if($check){

return redirect()->back()
            ->with('success','quantity is Less that entered');
    // return view('sales.index')->with('success', 'not register'); 
}
else{
$stokl=(int)$data->quantity-(int)$request->qty[$a];
// dd($stokl);

$model1=([
    'quantity'=>$stokl,

]);
$x=Stock::find($ido);

if($x->update($model1))
{
    Sales::create($model);
}

I need an idea regards on my explanation

I have order form i want to check quantity, if the quatity request is available on the stock, i have two store store 1 and 2

So i select product and store on the order form, i want to check before i save the order.

Someone can help me to create this loop

@snapey but i define it in the model,

but my concept is to select a customer and get his invoice from sales table

@munazzil , i get the same error

@tykus i dont know how to apply this

@munazzil i get this error.

                    Order direction must be "asc" or "desc".

customer has many invoice, that why i want to select customer and return all invoice belong to customer

    example this customer has this invoices 
            
               FK060919#0001,   FK060919#0003,   FK060919#0007,

i want to show only some data which is specific selected id

when i run that foreach its comes multiples, keep in mind that i want to show only one specific selected id

                     public function show($id)
              {

               $customer_ledger=Sales::with('customer')
                 -> select('invoice_no','customer_id','created_at',DB::raw('sum(amount) as totalM'))
                 ->where('customer_id', $id)
                   ->groupby('invoice_no','customer_id','created_at')
                             
                                 ->get();
                 return view('customers.customer_ledger',compact('customer_ledger'));
               }

i have two table which is related, i want to select some data where the id is equal to selected, and display in blade

i tried this but get the below error.

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

my code:

         $customer_ledger=Sales::with('customer')
                   -> select('invoice_no','customer_id','created_at',DB::raw('sum(amount) as totalM'))
                   ->where('customer_id', $id)
                     ->groupby('invoice_no','customer_id','created_at')
                      ->get();

How to implement this on my code below? $ids=$request->name;

 for ($a=0; $a < count($ids); $a++)
 {
    //create detail on data faktur

    $ido=$request->name[$a];
    $store=$request->store[$a];

    $model=([
        'invoice_no'=>$fk, 
        'customer_id'=>$request->customer_id, 
        //'product_code'=>$request->product_code[$a], 
        'name'=>$request->name[$a],
        'store'=>$request->store[$a],
        'selling_price'=>$request->selling_price[$a], 
        'amount'=>$request->amount[$a],
        'qty'=>$request->qty[$a], 
    ]);
    //update stok
     $data=Stock::select('quantity','store')->where([
                    ['id', '=', $ido],
                    ['store', '=', $store],
                      ])->first();
    // apply  conditions
    //check if available

    $check=(int)$data->quantity < (int)$request->qty[$a];
    dd($data);
    if($check){
    
    return redirect()->back()
                ->with('success','quantity is Less that entered');
        // return view('sales.index')->with('success', 'not register'); 
    }
    else{
    $stokl=(int)$data->quantity-(int)$request->qty[$a];
    // dd($stokl);

    $model1=([
        'quantity'=>$stokl,

    ]);
    $x=Stock::find($ido);

    if($x->update($model1))
    {
        Sales::create($model);
    }

I have multiple input of order form(product,price, qnty, store and amount)

I want to put the condition if the product quantity is bigger than stock then redirect back with error

Also i want to check foreach product name if it available (added) on stock inorder to perform the sales if not redirect it back.

Someone can share me an idea to archive this as well.

i want to have the condition regarding on my code to check few thing as per below

1. check if the quantity is less than entered with user, and if is less don't save the data display the list of that product which is less

• right now my code is working only for one products and save others which is not less than.

1. Also i i want to check if the product is selected as per query says, if not true then display the error of that products is not satisfy the condition.
   • right now my code is working fine but it checks well but if there is error it save only product which is sastify condition.

below is my code of controller

                $ids=$request->name;

    
     for ($a=0; $a < count($ids); $a++)
     {
        //create detail on data faktur

        $ido=$request->name[$a];
        $store=$request->store[$a];

        $model=([
            'invoice_no'=>$fk, 
            'customer_id'=>$request->customer_id, 
            //'product_code'=>$request->product_code[$a], 
            'name'=>$request->name[$a],
            'store'=>$request->store[$a],
            'selling_price'=>$request->selling_price[$a], 
            'amount'=>$request->amount[$a],
            'qty'=>$request->qty[$a], 
        ]);
        //update stok
         $data=Stock::select('quantity','store')->where([
                        ['id', '=', $ido],
                        ['store', '=', $store],
                          ])->first();
        // apply  conditions
        //check if available

        $check=(int)$data->quantity < (int)$request->qty[$a];
        dd($data);
        if($check){
        
        return redirect()->back()
                    ->with('success','quantity is Less that entered');
            // return view('sales.index')->with('success', 'not register'); 
        }
        else{
        $stokl=(int)$data->quantity-(int)$request->qty[$a];
        // dd($stokl);

        $model1=([
            'quantity'=>$stokl,

        ]);
        $x=Stock::find($ido);

        if($x->update($model1))
        {
            Sales::create($model);
        }
        }

i need the help on declare the condition

i have a invoice which perform sales basis, , so i want to select product_id and store name(office or Yard) then to calculate some some formular as per code below.

but right now it select only one row and perform calculation, i want to select multiple row according then perform calculation.

below is my code.

           for ($a=0; $a < count($ids); $a++)
    {
        //create detail on data 

        $ido=$request->name[$a];
        $store=$request->store[$a];

        $model=([
            'invoice_no'=>$fk, 
            'customer_id'=>$request->customer_id, 
             'name'=>$request->name[$a],
            'store'=>$request->store[$a],
            'selling_price'=>$request->selling_price[$a], 
            'amount'=>$request->amount[$a],
            'qty'=>$request->qty[$a], 
        ]);
        //update stok
         $data=Stock::select('quantity')->where([
                        ['id', '=', $ido],
                        ['store', '=', $store],
                          ])->first();
        //$data=Stock::select('quantity')->where('id','=',$ido)->first();
  
        $stokl=(int)$data->quantity-(int)$request->qty[$a];

        dd($stokl);
        
        $model1=([
            'quantity'=>$stokl,

        ]);
        $x=Stock::find($ido);

        if($x->update($model1))
        {
            Sales::create($model);
        }

when i running dd($stokl); is show the calculation of one row, while i entered two rows

There is the second, answer he sent i don't see it inorder to mark as answer that why i comment like that

@sti3bas your answer is correct, thank you