<?php

namespace App\Http\Controllers;

use App\Imports\ImportUser;
use App\Models\ActivityLog;
use App\Models\Clients;
use App\Models\DuplicateProductRecord;
use App\Models\GenericModel;
use App\Models\ImportSubstitute;
use App\Models\Manufacturer;
use App\Models\Product;
use App\Models\ProductMenu;
use App\Models\ProductSubstitutes;
use App\Models\SubCategoryModel;
use App\Models\SubTypeModel;
use App\Models\SupplierProduct;
use App\Models\ToolUsers;
use App\Models\TypeModel;
use App\Models\Users;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
use PhpParser\Node\Stmt\Return_;
use PDF;
use function PHPUnit\Framework\isEmpty;
use function PHPUnit\Framework\isNull;

class ProductController extends Controller
{

    function expirydemo($clientid, $from, $to, $supplier, $userid)
    {

        $clientName = Clients::where('ClientID','=',$clientid)->pluck('ClientName')->first();
        $clietArea = Clients::where('ClientID','=',$clientid)->pluck('ClientName')->first();
        $clientLocation = Clients::where('ClientID','=',$clientid)->pluck('ClientName')->first();

        $demo = Users::where('id','=',$userid)->pluck('is_demo')->first();


        if($supplier == 0)
        {
            $data = [
                'expiry' => DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,batch,expiry,stkqty,ROUND((ss.stkqty*PRate),2) as PAmt, ROUND((ss.stkqty*SRate),2) as SAmt, ROUND((ss.stkqty*MRP),2) as MRPAmt,(mrp*pack) as MRPPack,pack from ( 
                    select  product_name,manufacturer,batch,expiry,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,mAX(MRP) as mrp,max(pack) as pack from ( 
                                select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp,upack as Pack from stock where clientid ='".$clientid."'
                                and (purchase_quantity-sales_quantity)>0  and COALESCE(expiry,'1900-01-01') <='".$to."'
                                )stk group by product_name,manufacturer
                                   
                               ) ss left join (
                                

                                    select max(id) as id,clientid,item_description,company_name,supplier_name from (
                               
                               
                               Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                               On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                               and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                               Union
                               select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                               and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                               
                               Union
                               select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                               where t.type='i' and ti.type='i'
                               And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                               Union
                               select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                               where t.type='i' and ti.type='i'
                               And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                                
                               
                               
                               ) p4     group by clientid,item_description,company_name
                                
                                    )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                                   
                                   order by product_name asc LIMIT 500;   "),

                       'from' => $from,
                       'to' => $to,
                       'clientid' => $clientid,
                       'clientName' => $clientName,
                       'clietArea' => $clietArea,
                       'clientLocation' => $clientLocation,
                       'demo' => $demo,
                       'title' => 'Expiry Report for all suppliers upto : ' . MainAcontroller::convertDate($to)
            ];
    
        }
        else
        {
            $data = [
                'expiry' => DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,batch,expiry,stkqty,ROUND((ss.stkqty*PRate),2) as PAmt, ROUND((ss.stkqty*SRate),2) as SAmt, ROUND((ss.stkqty*MRP),2) as MRPAmt,(mrp*pack) as MRPPack,pack from ( 
                    select  product_name,manufacturer,batch,expiry,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,mAX(MRP) as mrp,max(pack) as pack from ( 
                                select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp,upack as Pack from stock where clientid ='".$clientid."'
                                and (purchase_quantity-sales_quantity)>0  and COALESCE(expiry,'1900-01-01') <='".$to."'
                                )stk group by product_name,manufacturer
                                   
                               ) ss left join (
                                
                                
                                 
                                    select max(id) as id,clientid,item_description,company_name,supplier_name from (
                               
                               
                               Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                               On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                               and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                               Union
                               select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                               and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                               
                               Union
                               select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                               where t.type='i' and ti.type='i'
                               And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                               Union
                               select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                               where t.type='i' and ti.type='i'
                               And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                                
                               
                               
                               ) p4     group by clientid,item_description,company_name
                                
                                    )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                                   where supplier_name = '".$supplier."'
                                   order by product_name asc LIMIT 500;  "),

                       'from' => $from,
                       'to' => $to,
                       'clientid' => $clientid,
                       'clientName' => $clientName,
                       'clietArea' => $clietArea,
                       'clientLocation' => $clientLocation,
                       'demo' => $demo,
                       'title' => 'Expiry Report for ' .$supplier. ' upto : ' . MainAcontroller::convertDate($to)
            ];
    
        }

       
        $pdf = PDF::loadView('Admin.demoexpiry', $data);

        $content = $pdf->download()->getOriginalContent();

        Storage::put('public/minipurchaseorder/126'. '.pdf', $content);

        $myFile = storage_path('app/public/minipurchaseorder/126'.'.pdf');

        return response()->download($myFile);

    }

    function getExpiry($clientid, $from, $to, $supplier, $userid)
    {

        // with supplier from and to
        if ($supplier != '0' &&  $to != '0') {

            $data = [

                'supplier' => DB::select(" select supplier_name,count(product_name) as cnt, round(SUM(PAmt),2) as TotPAmnt, round(SUM(SAmt),2) as TotSAmnt, round(SUM(MRPAmt),2) as TotMRPAmnt from (
        
                    select product_name,company_name,COALESCE(supplier_name,'') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt,(ss.stkqty*mrp) as MRPAmt from (
                    
                     select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,MAX(MRP) as MRP from ( 
                     select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,MRP from stock where clientid ='".$clientid."'
                     and (purchase_quantity-sales_quantity)>0 and   
                      COALESCE(expiry,'1900-01-01') <='".$to."'
                     )stk group by product_name,manufacturer
                        
                    ) ss left join (
                     
                         select max(id) as id,clientid,item_description,company_name,supplier_name from (

                    
                    Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                    On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                    and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                    Union
                    select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                    and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                    
                    Union
                    select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                    where t.type='i' and ti.type='i'
                    And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                    Union
                    select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                    where t.type='i' and ti.type='i'
                    And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch

                    
                    ) p4  group by clientid,item_description,company_name
                     
                         )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                        )cnt where supplier_name = '".$supplier."' group by supplier_name
                        order by supplier_name; "),

                'clientid' => $clientid,
                'from' => $from,
                'to' => $to,
                'suppliername' => $supplier,
                'demo' => Users::where('id','=',$userid)->pluck('is_demo')->first(),
                'type' => 1,
                

            ];
        }

        // with supplier and to : if ($supplier = '0' && $to != '0')
        else  {
            $data = [

                'supplier' => DB::select(" select supplier_name,count(product_name) as cnt, round(SUM(PAmt),2) as TotPAmnt, round(SUM(SAmt),2) as TotSAmnt, round(SUM(MRPAmt),2) as TotMRPAmnt from (
        
                    select product_name,company_name,COALESCE(supplier_name,'') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt,(ss.stkqty*mrp) as MRPAmt from (
                    
                     select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,MAX(MRP) as MRP from ( 
                     select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,MRP from stock where clientid ='".$clientid."'
                     and (purchase_quantity-sales_quantity)>0 and   
                      COALESCE(expiry,'1900-01-01') <='".$to."'
                     )stk group by product_name,manufacturer
                        
                    ) ss left join (
                     
                         select max(id) as id,clientid,item_description,company_name,supplier_name from (

                    
                    Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                    On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                    and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                    Union
                    select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                    and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                    
                    Union
                    select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                    where t.type='i' and ti.type='i'
                    And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                    Union
                    select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                    where t.type='i' and ti.type='i'
                    And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch

                    
                    ) p4  group by clientid,item_description,company_name
                     
                         )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                        )cnt where supplier_name != '' group by supplier_name
                        order by supplier_name;  "),

                'clientid' => $clientid,
                'from' => $from,
                'to' => $to,
                'suppliername' => $supplier,
                'demo' => Users::where('id','=',$userid)->pluck('is_demo')->first(),
                'type' => 2,

            ];
        }
        




        $pdf = PDF::loadView('Admin.ViewExpiryReport', $data);

        $content = $pdf->download()->getOriginalContent();

        Storage::put('public/minipurchaseorder/' . $clientid . '.pdf', $content);

        $myFile = storage_path('app/public/minipurchaseorder/' . $clientid . '.pdf');

        return response()->download($myFile);
    }

    function viewExpiryReport()
    {
        $data = [

            'supplier' => DB::select(" select supplier_name,count(product_name) as cnt, round(SUM(PAmt),2) as TotPAmnt, round(SUM(SAmt),2) as TotSAmnt from (
    
                select product_name,company_name,COALESCE(supplier_name,'') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt from (
                
                 select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate from ( 
                 select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate from stock where clientid ='1981'
                 and (purchase_quantity-sales_quantity)>0 and   
                 COALESCE(expiry,'1900-01-01')>='2023-01-01'  and COALESCE(expiry,'1900-01-01') <='2023-09-25'
                 )stk group by product_name,manufacturer
                    
                ) ss left join (
                 
                 
                  
                     select max(id) as id,clientid,item_description,company_name,supplier_name from (
                
                
                Select COALESCE(max(((year(purchase_date)*3650000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                and pi.ClientID ='1981'   and p.ClientID ='1981'   group by pi.clientid,item_description,company_name,supplier_name
                Union
                select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                and clientid='1981'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='1981'   and ti.ClientID ='1981'   group by ti.clientid,item_description,company_name,branch
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='1981'  And ti.clientid='1981'  group by ti.clientid,item_description,company_name,branch
                 
                
                
                ) p4  group by clientid,item_description,company_name
                 
                     )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                    )cnt where supplier_name !='' group by supplier_name
                    order by supplier_name; "),



            'product' => DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt from (

            select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate from ( 
            select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate from stock where clientid ='1981'
            and (purchase_quantity-sales_quantity)>0  and
            COALESCE(expiry,'1900-01-01') >='2023-01-01'  and COALESCE(expiry,'1900-01-01') <='2023-09-25'
            )stk group by product_name,manufacturer
               
           ) ss left join (
            
            
                select max(id) as id,clientid,item_description,company_name,supplier_name from (
           
           
           Select COALESCE(max(((year(purchase_date)*3650000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
           On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
           and pi.ClientID ='1981'   and p.ClientID ='1981'   group by pi.clientid,item_description,company_name,supplier_name
           Union
           select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
           and clientid='1981'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
           
           Union
           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
           where t.type='i' and ti.type='i'
           And t.clientid='1981'   and ti.ClientID ='1981'   group by ti.clientid,item_description,company_name,branch
           Union
           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
           where t.type='i' and ti.type='i'
           And t.clientid='1981'  And ti.clientid='1981'  group by ti.clientid,item_description,company_name,branch
            
        
           ) p4 group by clientid,item_description,company_name
            
                )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
               order by supplier_name; ")
        ];

        $pdf = PDF::loadView('Admin.ViewExpiryReport', $data);

        $content = $pdf->download()->getOriginalContent();

        Storage::put('public/minipurchaseorder/100.pdf', $content);

        $myFile = storage_path('app/public/minipurchaseorder/100.pdf');

        return response()->download($myFile);

        // return view('Admin.ViewExpiryReport', $data);
        // return $data; 
    }


    public static function getExpiredProductsSUMByClientID($supplier, $from, $to, $clientid)
    {
        if ($supplier != '0' && $to != '0') {

            $values = DB::select(" select  SUM(TotPAmnt) as pCost,SUM(TotSAmnt) as sCost,clientid,sum(mrpamt) as MRPCost from (
                select supplier_name,count(product_name) as cnt, round(SUM(PAmt),2) as TotPAmnt, round(SUM(SAmt),2) as TotSAmnt, round(SUM(MRPAmt),2) as mrpamt,clientid from (
                    
                select product_name,company_name,COALESCE(supplier_name,'') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt,(ss.stkqty*MRP) as mrpamt,clientid from (
                
                 select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,MAX(MRP) as mrp from ( 
                 select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp from stock where clientid ='".$clientid."'
                 and (purchase_quantity-sales_quantity)>0 and   
                 COALESCE(expiry,'1900-01-01') <='".$to."'
                 )stk group by product_name,manufacturer
                    
                ) ss left join (
                 
                  
                     select max(id) as id,clientid,item_description,company_name,supplier_name from (
                
                
                Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                Union
                select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                 
                
                
                ) p4  group by clientid,item_description,company_name
                 
                     )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                    )cnt where supplier_name ='".$supplier."' group by supplier_name
                    order by supplier_name)cnts group by clientid; ");

        } else {

            $values = DB::select(" select  SUM(TotPAmnt) as pCost,SUM(TotSAmnt) as sCost,clientid,sum(mrpamt) as MRPCost from (
                select supplier_name,count(product_name) as cnt, round(SUM(PAmt),2) as TotPAmnt, round(SUM(SAmt),2) as TotSAmnt, round(SUM(MRPAmt),2) as mrpamt,clientid from (
                    
                select product_name,company_name,COALESCE(supplier_name,'') as supplier_name,stkqty,(ss.stkqty*PRate) as PAmt,(ss.stkqty*SRate) as SAmt,(ss.stkqty*MRP) as mrpamt,clientid from (
                
                 select  product_name,manufacturer,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,MAX(MRP) as mrp from ( 
                 select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp from stock where clientid ='".$clientid."'
                 and (purchase_quantity-sales_quantity)>0 and   
                 COALESCE(expiry,'1900-01-01') <='".$to."'
                 )stk group by product_name,manufacturer
                    
                ) ss left join (
                 
                  
                     select max(id) as id,clientid,item_description,company_name,supplier_name from (
                
                
                Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                Union
                select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                Union
                select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                where t.type='i' and ti.type='i'
                And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                 
                
                
                ) p4  group by clientid,item_description,company_name
                 
                     )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                    )cnt where supplier_name !='' group by supplier_name
                    order by supplier_name)cnts group by clientid; ");

        } 
        

        return $values;
    }

    // expiry report methods 

    public static function getExpiredProductsBySupplierwithDate($supplier, $from, $to, $clientid)
    {

        // with supplier and to date
        if ($supplier != '0' && $to != '0') {

            $products = DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,batch,expiry,stkqty,ROUND((ss.stkqty*PRate),2) as PAmt, ROUND((ss.stkqty*SRate),2) as SAmt, ROUND((ss.stkqty*MRP),2) as MRPAmt,(mrp*pack) as MRPPack,pack from ( 
                select  product_name,manufacturer,batch,expiry,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,mAX(MRP) as mrp,max(pack) as pack from ( 
                            select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp,upack as Pack from stock where clientid ='".$clientid."'
                            and (purchase_quantity-sales_quantity)>0  and COALESCE(expiry,'1900-01-01') <='".$to."'
                            )stk group by product_name,manufacturer
                               
                           ) ss left join (
                            
                            
                             
                                select max(id) as id,clientid,item_description,company_name,supplier_name from (
                           
                           
                           Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                           On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                           and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                           Union
                           select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                           and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                           
                           Union
                           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                           where t.type='i' and ti.type='i'
                           And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                           Union
                           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                           where t.type='i' and ti.type='i'
                           And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                            
                           
                           
                           ) p4     group by clientid,item_description,company_name
                            
                                )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                                where supplier_name = '".$supplier."'
                               order by product_name asc;
                   ");
        } 
        // without supplier and with todate

        else if ($supplier = '0' && $to != '0') {

            $products = DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,batch,expiry,stkqty,ROUND((ss.stkqty*PRate),2) as PAmt, ROUND((ss.stkqty*SRate),2) as SAmt, ROUND((ss.stkqty*MRP),2) as MRPAmt,(mrp*pack) as MRPPack,pack from ( 
                select  product_name,manufacturer,batch,expiry,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate,mAX(MRP) as mrp,max(pack) as pack from ( 
                            select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate,mrp,upack as Pack from stock where clientid ='".$clientid."'
                            and (purchase_quantity-sales_quantity)>0  and COALESCE(expiry,'1900-01-01') <='".$to."'
                            )stk group by product_name,manufacturer
                               
                           ) ss left join (
                            
                            
                             
                                select max(id) as id,clientid,item_description,company_name,supplier_name from (
                           
                           
                           Select COALESCE(max(((year(purchase_date)*36".$clientid."000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
                           On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
                           and pi.ClientID ='".$clientid."'   and p.ClientID ='".$clientid."'   group by pi.clientid,item_description,company_name,supplier_name
                           Union
                           select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
                           and clientid='".$clientid."'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
                           
                           Union
                           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                           where t.type='i' and ti.type='i'
                           And t.clientid='".$clientid."'   and ti.ClientID ='".$clientid."'   group by ti.clientid,item_description,company_name,branch
                           Union
                           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
                           where t.type='i' and ti.type='i'
                           And t.clientid='".$clientid."'  And ti.clientid='".$clientid."'  group by ti.clientid,item_description,company_name,branch
                            
                           
                           
                           ) p4     group by clientid,item_description,company_name
                            
                                )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                                where supplier_name != ''
                               order by product_name asc;
                   ");
        } 



        return $products;
    }

    // expiry report methods close

    public static function getExpiredProductsBySupplier($suplier)
    {
        $products = DB::select(" select product_name,company_name,COALESCE(supplier_name,'Ostock') as supplier_name,batch,expiry,stkqty,ROUND((ss.stkqty*PRate),2) as PAmt, ROUND((ss.stkqty*SRate),2) as SAmt from ( select  product_name,manufacturer,batch,expiry,sum(stkqty) as stkqty,max(PRate) as PRate,Max(SRate) as SRate from ( 
            select product_name,manufacturer,batch,expiry,(purchase_quantity-sales_quantity) as stkqty,unit_rate as PRate,sales_rate as SRate from stock where clientid ='1981'
            and (purchase_quantity-sales_quantity)>0  and
            COALESCE(expiry,'1900-01-01') >='2023-01-01'  and COALESCE(expiry,'1900-01-01') <='2023-09-25'
            )stk group by product_name,manufacturer
               
           ) ss left join (
            
            
             
                select max(id) as id,clientid,item_description,company_name,supplier_name from (
           
           
           Select COALESCE(max(((year(purchase_date)*3650000) + (month(purchase_date)*120000) + (day(purchase_date))+p.purchase_id) *100000),0) as id,pi.clientid,item_description,company_name,supplier_name from purchase1 p left join purchase_item1 pi
           On p.clientid=pi.clientid And p.purchase_id=pi.purchase_id where 1>0
           and pi.ClientID ='1981'   and p.ClientID ='1981'   group by pi.clientid,item_description,company_name,supplier_name
           Union
           select COALESCE(max(((year(invoice_date)*365) + (month(invoice_date)*12) + (day(invoice_date)))),0) as id,clientid,item_description,company_name,supplier_name from opening_stock1 where 1>0
           and clientid='1981'   and COALESCE(supplier_name,'')<>'' group by clientid,item_description,company_name,supplier_name
           
           Union
           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer1 t left join transfer_item1 ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
           where t.type='i' and ti.type='i'
           And t.clientid='1981'   and ti.ClientID ='1981'   group by ti.clientid,item_description,company_name,branch
           Union
           select COALESCE(max(((year(invoice_date)) + (month(invoice_date)) + (day(invoice_date)))),0) as id,ti.clientid,item_description,company_name,branch as supplier_name from transfer t left join transfer_item ti on t.clientid=ti.clientid and t.transfer_no=ti.transfer_no
           where t.type='i' and ti.type='i'
           And t.clientid='1981'  And ti.clientid='1981'  group by ti.clientid,item_description,company_name,branch
            
           
           
           ) p4     group by clientid,item_description,company_name
            
                )supp on ss.product_name = supp.item_description and ss.manufacturer = supp.company_name
                where supplier_name = '" . $suplier . "'
               order by product_name asc; 
               ");

        return $products;
    }


    function namecorrection()
    {
        $data = ['product' => DB::select(" select * from product1 limit 10 ")];


        return view('Admin.ProductNameCorrect', $data);
    }

























    function saveEdittedProduct(Request $request)
    {
        $productID = $request->productID;
        $productDivision = $request->productDivision;

        for ($count = 0; $count < count($productID); $count++) {

            Product::where('product_id', $productID[$count])
                ->update([
                    'division_name' => $productDivision[$count]
                ]);
        }

        DB::statement(" UPDATE product1
        SET division = auto_id
        FROM division
        WHERE product1.division = 0 and product1.division_name = division.division_name  ");

        return redirect()->route('web.importtodb')->with('success', 'Products updated successfully');
    }

    function showEditProductGrid(Request $request)
    {
        $sortOrder = $request->editSort;

        if ($sortOrder == 'divwise') {

            $csvLine = DB::select(" select product_name,division_name,product_id,manufacturer,manufacturer_id from product1 where manufacturer_id = " . $request->editPro . "
            order by REPLACE(REPLACE(replace(division_name,'0','ZZ1'),'NULL','ZZ2'),'','ZZ3') 
             ");

            // Product::select("product_name", "manufacturer", "product_id", "division_name")
            //     ->where('manufacturer_id', '=', $request->editPro)
            //     ->orderBy('division_name')
            //     ->get();

        } else {

            $csvLine = DB::select(" select product_name,division_name,product_id,manufacturer,manufacturer_id from product1 where manufacturer_id = " . $request->editPro . "
            order by REPLACE(REPLACE(replace(division_name,'0','ZZ1'),'NULL','ZZ2'),'','ZZ3') desc
             ");
            // Product::select("product_name", "manufacturer", "product_id", "division_name")
            //     ->where('manufacturer_id', '=', $request->editPro)
            //     ->orderBy('division_name', 'desc')
            //     ->get();
        }


        $gridTable = '<tbody>';
        $counter = 0;

        // <td><button onclick="createNewProduct(this.parentElement.parentElement)" type="button" class="btn btn-success" title="Save"><span class="sr-only">Save</span> <i class="fa fa-save"></i></button> </td>


        foreach ($csvLine as $product) {
            $gridTable .= '<tr> <td scope="row"> ' . ++$counter . ' </td>
                               
            <td>
            <input name="productName[]" value="' . ltrim($product->product_name) . '" type="text" class="form-control text-success" required> 
            <input hidden name="productID[]" value="' . ltrim($product->product_id) . '" type="text" class="form-control text-success" required> 
            </td>
            <td> 
            <input name="productMFR[]" value="' . ltrim($product->manufacturer) . '" type="text" class="form-control text-success" >
            <input hidden name="productMFRID[]" value="' . ltrim($product->manufacturer_id) . '" type="text" class="form-control text-success" >
            </td>
            <td> <input name="productDivision[]" value="' . ltrim($product->division_name) . '" type="text" class="form-control text-success divisionnames" > </td>
           
           
            ';
        }


        $gridTable .= '</tbody>';

        $data = [
            'manufacturer' => $request->editPro,
            'gridTable' => strlen($gridTable) > 16 ? $gridTable : 'nil'
        ];

        return view('Admin.EditProduct', $data);
    }

    function addProductFromExcel(Request $request)
    {

        $productName = $request->productName;
        $productMFR = $request->productMFR;
        $newMfr = $request->newMfr;
        $newProduct = $request->newProduct;

        $productShortID = $request->productShortID;
        $productGeneric = $request->productGeneric;
        $productCategoryType = $request->productCategoryType;
        $productSchedule = $request->productSchedule;
        $productHSNCode = $request->productHSNCode;
        $productGST = $request->productGST;
        $productCESS = $request->productCESS;
        $productSubHead = $request->productSubHead;
        $productDivision = $request->productDivision;
        $productDescription = $request->productDescription;
        $productTaxCategory = $request->productTaxCategory;
        $productMaterialCode = $request->productMaterialCode;
        $productCess2 = $request->productCess2;
        $productCategorySubType = $request->productCategorySubType;


        $data = [

            'productName' => $productName,
            'productMFR' => $productMFR,
            'newProduct' => $newProduct,
            'newMfr' => $newMfr

        ];

        $userArray = [];

        for ($count = 0; $count < count($productName); $count++) {

            $exist = ProductSubstitutes::where('old_product_name', '=', $productName[$count])->where('old_manufacturer', '=', $productMFR[$count])->first();

            if (!$exist) {
                $userArray[] = [

                    'old_product_name' => $productName[$count] == '' ? 0 : $productName[$count],
                    'old_manufacturer' => $productMFR[$count] == '' ? 0 : $productMFR[$count],
                    'new_product_name' => $newProduct[$count] == '' ? 0 :  $newProduct[$count],
                    'new_manufacturer' => $newMfr[$count] == '' ? 0 : $newMfr[$count],
                    'generated_time_date' => Carbon::now()->toDateTimeString(),


                ];
            }
        }

        if (!empty($userArray)) {
            ProductSubstitutes::insert($userArray);
        }

        $dataNew = [
            'gridView' => false
        ];
        // return view('Admin.importDB', $dataNew);
        return redirect()->route('web.importtodb')->with('success', 'Products added successfully');

        // return $userArray;
    }

    function createProduct(Request $request)
    {
        $pname = $request->pname;
        $pshortid = $request->pshortid;
        $pgeneric = $request->pgeneric;
        $pcategory = $request->pcategory;
        $pschedule = $request->pschedule;
        $phsn = $request->phsn;
        $pgst = $request->pgst;
        $pcess = $request->pcess;
        $psubhead = $request->psubhead;
        $pdivision = $request->pdivision;
        $pdescription = $request->pdescription;
        $ptaxcategory = $request->ptaxcategory;
        $pmaterialcode = $request->pmaterialcode;
        $pcess2 = $request->pcess2;
        $pcatesubtype = $request->pcatesubtype;
        $myManuNew = $request->myManuNew;

        $exist = Product::where('product_name', '=', $pname)->where('manufacturer', '=', $myManuNew)->first();

        if (!$exist) {

            $pro = new Product();

            $pro->product_name = $pname == '' ? 0 : $pname;
            $pro->manufacturer = $myManuNew == '' ? 0 : $myManuNew;
            $pro->shortid = $pshortid == '' ? 0 : $pshortid;
            $pro->gen_ids = $pgeneric == '' ? 0 : $pgeneric;
            $pro->category_type_text = $pcategory == '' ? 0 : $pcategory;
            $pro->schedule = $pschedule == '' ? 0 : $pschedule;
            $pro->hsncode = $phsn == '' ? 0 : $phsn;
            $pro->gst = $pgst == '' ? 0 : $pgst;
            $pro->cess = $pcess == '' ? 0 : $pcess;
            $pro->subhead = $psubhead == '' ? 0 : $psubhead;
            $pro->division_name = $pdivision == '' ? 0 : $pdivision;
            $pro->description = $pdescription == '' ? 0 : $pdescription;
            $pro->taxcategory_id = $ptaxcategory == '' ? 0 : $ptaxcategory;
            $pro->material_code = $pmaterialcode == '' ? 0 : $pmaterialcode;
            $pro->cess2 = $pcess2 == '' ? 0 : $pcess2;
            $pro->active = 1;
            $pro->category_subtype_text = $pcatesubtype == '' ? 0 : $pcatesubtype;

            $res = $pro->save();

            $lastId = $pro->id;

            DB::statement(" UPDATE product1
                SET manufacturer_id = mfr_newid
                FROM manufacturer
                WHERE product1.id = " . $lastId . " and product1.manufacturer = manufacturer.manufacturer_name and product1.shortid = manufacturer.short_id ");

            if ($res) {
                return response()->json(['success' => true, 'message' => 'Product created successfully'], 200);
            } else {
                return response()->json(['success' => false, 'message' => 'Something went wrong'], 500);
            }
        } else {
            return response()->json(['success' => false, 'message' => 'Something went wrong'], 500);
        }
    }

    function editDBProduct(Request $request)
    {
        $pid = $request->pid;
        $pname = $request->pname;
        $pshortid = $request->pshortid;
        $pgeneric = $request->pgeneric;
        $pcategory = $request->pcategory;
        $pschedule = $request->pschedule;
        $phsn = $request->phsn;
        $pgst = $request->pgst;
        $pcess = $request->pcess;
        $psubhead = $request->psubhead;
        $pdivision = $request->pdivision;
        $pdescription = $request->pdescription;
        $ptaxcategory = $request->ptaxcategory;
        $pmaterialcode = $request->pmaterialcode;
        $pcess2 = $request->pcess2;
        $pcatesubtype = $request->pcatesubtype;
        $myManuNew = $request->myManuNew;

        // $exist = Product::where('product_name', '=', $pname)->where('manufacturer', '=', $myManuNew)->first();

        // if (!$exist) {

        $pro = Product::where('product_id', '=', $pid)->first();

        $pro->product_name = $pname == '' ? 0 : $pname;
        $pro->manufacturer = $myManuNew == '' ? 0 : $myManuNew;
        $pro->shortid = $pshortid == '' ? 0 : $pshortid;
        $pro->gen_ids = $pgeneric == '' ? 0 : $pgeneric;
        $pro->category_type_text = $pcategory == '' ? 0 : $pcategory;
        $pro->schedule = $pschedule == '' ? 0 : $pschedule;
        $pro->hsncode = $phsn == '' ? 0 : $phsn;
        $pro->gst = $pgst == '' ? 0 : $pgst;
        $pro->cess = $pcess == '' ? 0 : $pcess;
        $pro->subhead = $psubhead == '' ? 0 : $psubhead;
        $pro->division_name = $pdivision == '' ? 0 : $pdivision;
        $pro->description = $pdescription == '' ? 0 : $pdescription;
        $pro->taxcategory_id = $ptaxcategory == '' ? 0 : $ptaxcategory;
        $pro->material_code = $pmaterialcode == '' ? 0 : $pmaterialcode;
        $pro->cess2 = $pcess2 == '' ? 0 : $pcess2;
        $pro->active = 1;
        $pro->category_subtype_text = $pcatesubtype == '' ? 0 : $pcatesubtype;

        $res = $pro->save();

        //     $lastId = $pro->id;

        GlobalController::UpdateManufacturerToProductFromManufacturer();

        if ($res) {
            return response()->json(['success' => true, 'message' => 'Product edited successfully'], 200);
        } else {
            return response()->json(['success' => false, 'message' => 'Something went wrong'], 500);
        }
        // } else {
        //     return response()->json(['success' => false, 'message' => 'Something went wrong'], 500);
        // }

    }

    function removeDivisionFromSupplier(Request $request)
    {
        // $res = DB::select(" update supplier_product set status = 0 where division_id = " . $request->divisionWise . " and supplier_id = " . $request->supplierDID . " ");
        $res = DB::select(" delete from supplier_product where division_id = " . $request->divisionWise . " and supplier_id = " . $request->supplierDID . " ");

        if ($res) {
            return back()->with('success', 'Division removed successfully');
        } else {
            return back()->with('fail', 'Something went wrong');
        }
    }

    function removeCompanyFromSupplier(Request $request)
    {
        // $res = DB::select(" update supplier_product set status = 0 where mfr_id = " . $request->companyWise . " and supplier_id = " . $request->supplierID . " ");
        $res = DB::select(" delete from supplier_product where mfr_id = " . $request->companyWise . " and supplier_id = " . $request->supplierID . " ");

        if ($res) {
            return back()->with('success', 'Company removed successfully');
        } else {
            return back()->with('fail', 'Something went wrong');
        }
    }

    function getDivisionNamesForSupplier(Request $request)
    {
        $divisionNames = DB::select(" select d.division_name, sp.division_id from supplier_product sp
        join division d on sp.division_id = d.auto_id
        where sp.supplier_id = " . $request->supplier . " and status = 1
        group by d.division_name,sp.division_id  ");

        return $divisionNames;
    }

    function getCompanyNamesForSupplier(Request $request)
    {
        $companyNames = DB::select(" select m.manufacturer_name, sp.mfr_id from supplier_product sp
        join manufacturer m on sp.mfr_id = m.mfr_newid
        where sp.supplier_id = " . $request->supplier . " and status = 1
        group by m.manufacturer_name,sp.mfr_id  ");

        return $companyNames;
    }

    function activateProduct($id)
    {
        $pid = Crypt::decrypt($id);

        // $pro = Product::where('product_id', '=', $pid)->get();

        // $pro->active = 1;

        // $save = $pro->save();

        $qry = " update product1 set active = 1 where product_id = " . $pid . " ";

        $save = DB::statement($qry);

        if ($save) {
            return back()->with('success', 'Product activated successfully');
        } else {
            return back()->with('fail', 'Something went wrong');
        }
    }

    public function getProductsToMerge(Request $request)
    {
        // $products = DB::select(" select product_id,product_name,short_id,cntproduct,merge_status,manufacturer_name from (
        //     select product_id,product_name,short_id,merge_status,manufacturer_name from product1 p 
        //     left join manufacturer m on p.manufacturer_id = m.mfr_newid 	
        //     )p left join ( select product_name as cntpname,count(product_name) as cntproduct from product1 where LOWER(product_name) like LOWER( '".$request->search."%' ) 						  group by product_name having count(product_name)>1) cntp on 
        //                   p.product_name = cntp.cntpname
        //                  where LOWER(product_name) like LOWER( '".$request->search."%' ) and cntproduct>0 and active = 1
        //                   order by 1 desc ");

        $newSearch = str_replace(' ', '%', $request->search);

        // $products = DB::select(" select product_id,product_name,short_id,cntproduct,merge_status,manufacturer_name,active,mfr_newid from (
        //     select product_id,product_name,short_id,merge_status,manufacturer_name,active,mfr_newid from product1 p 
        //     left join manufacturer m on p.manufacturer_id = m.mfr_newid 		
        //     )p left join ( select product_name as cntpname,count(product_name) as cntproduct from product1 where LOWER(product_name) like LOWER( '".$newSearch."%' )  
        // 				  group by product_name having count(product_name)>1) cntp on 
        //                   p.product_name = cntp.cntpname
        //                  where LOWER(product_name) like LOWER( '".$newSearch."%' ) and cntproduct>0  and active = 1
        //                   order by 1 desc ");

        $products = DB::select("select product_id,product_name,short_id,merge_status,manufacturer_name,active,mfr_newid from product1 p
        left join manufacturer m on p.manufacturer_id = m.mfr_newid
        where LOWER(product_name) like LOWER( '" . $newSearch . "%' ) and active = 1 ORDER BY product_name");

        return $products;
    }

    public function getSupplierProductsBySupID(Request $request)
    {
        // $response = DB::select(" select supplier_product.*,division.auto_id,manufacturer.manufacturer_name from supplier_product
        // left join division on supplier_product.division_id = division.auto_id
        // left join manufacturer on division.manufacturer = manufacturer.mfr_newid
        // where supplier_id = $request->id and status = 1 ");

        $response = DB::select(" select sp.supplier_id,
        sp.product_id,
        sp.purchase_flg,
        COALESCE(sp.suppliername,'null') as suppliername,
        COALESCE(sp.productname,'null') as productname,
        COALESCE(sp.company,'null') as company,
        COALESCE(sp.division_id,0) as division_id,
        sp.status,
        COALESCE(sp.mfr_id,0) as mfr_id,
        COALESCE(sp.division_name,'null') as division_name
        from supplier_product sp
                left join division d on sp.division_id = d.auto_id
                left join manufacturer m on d.manufacturer = m.mfr_newid
                where sp.supplier_id = $request->id and sp.status = 1 ");

        // SELECT supplier_product.product_name,
        // supplier_product.product_id,
        // manufacturer.manufacturer_name,division.division_name
        // from supplier_product
        // left join product1 on supplier_product.product_id = product1.product_id
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        // left join division on product1.division = division.auto_id
        // WHERE supplier_product.product_id = $request->id

        // SELECT supplier_product.*,manufacturer.manufacturer_name,division.division_name
        // from supplier_product
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        // left join division on product1.division = division.auto_id
        // WHERE product_id = $request->id ");

        return $response;
    }

    public function removeSupplierProduct(Request $request)
    {
        $res = DB::statement(" delete from supplier_product where supplier_id = " . $request->supplier . " and product_id = " . $request->product . " ");

        if ($res) {
            return response()->json(['success' => true, 'message' => 'Product deleted successfully'], 200);
        } else {
            return response()->json(['success' => false, 'message' => 'Something went wrong'], 500);
        }
    }

    public function insertSupplierProduct(Request $request)
    {

        $suppliername = $request->supName;
        $supplierid = $request->supID;

        $product_id = $request->product_id;
        $product_name = $request->product_name;
        $manufacturer_name = $request->manufacturer_name;

        $auto_id = $request->auto_id;
        $div_names = $request->div_names;
        $mfr_newid = $request->mfr_newid;

        $zero = 0;

        if ($product_id == '') {

            return back()->with('fail', 'Product list is empty');
        } else {

            $insert_data = array();

            for ($i = 0; $i < count($product_id); $i++) {

                $proAlreadyExist = SupplierProduct::where('supplier_id', '=', $supplierid)->where('product_id', '=', $product_id[$i])->first();

                $proid = is_null($product_id[$i]) ? 0 : $product_id[$i];
                $proname = is_null($product_name[$i])  ? 0 : $product_name[$i];
                $comname = is_null($manufacturer_name[$i]) ? 0 : $manufacturer_name[$i];
                $divid = is_null($auto_id[$i])  ? 0 : $auto_id[$i];
                $comid = is_null($mfr_newid[$i])  ? 0 : $mfr_newid[$i];
                $divname = is_null($div_names[$i]) ? 0 : $div_names[$i];

                if (!$proAlreadyExist) {

                    $data = array(

                        'supplier_id' => $supplierid,
                        'product_id' =>  $proid,
                        'purchase_flg'  => $zero,
                        'suppliername'  => $suppliername,
                        'productname'  => $proname,
                        'company'  => $comname,
                        'division_id' => $divid,
                        'mfr_id' => $comid,
                        'division_name' => $divname,

                    );

                    $insert_data[] = $data;
                }
            }

            if (!empty($insert_data)) {
                SupplierProduct::insert($insert_data);
                return back()->with('success', 'Product added Successfully');
            } else {
                return back()->with('fail', 'Products already exist for supplier');
            }

            // $res = DB::statement(substr($query, 0, -2));

            // if ($res) {
            // return back()->with('success', 'Product added Successfully');
            // } else {
            //     return back()->with('fail', 'Something went wrong');
            // }
            // }
        }


        // return count($product_id);


    }


    public function deleteProduct(Request $request)
    {
        $id = $request->proid;

        $res = DB::statement(" UPDATE public.product1 SET active = 0 WHERE product_id = " . $id . " ");

        $proName = Product::where('product_id', '=', $id)->pluck('product_name')->first();

        if ($res) {

            $userid =  session('LoggedUser')['id'];
            $clientid = '100';
            $mergeletter = 'nil';
            $activityslug = 'product';
            $changeone = 'Product deleted';
            $changetwo =  'Product name : ' . $proName . ' ';
            $description = '  Product deleted with name ' . $proName . ' with product id : ' . $id . ' by user id : ' . $userid . '';

            $getRes = ActivityLogController::recordActivity(
                $userid,
                $clientid,
                $mergeletter,
                $activityslug,
                $changeone,
                $changetwo,
                $description
            );

            if ($getRes) {
                return response()->json(['success' => true, 'message' => 'Product deleted successfully'], 200);
            } else {
                return response()->json(['fail' => false, 'message' => 'Something went wrong'], 500);
            }
        } else {
            return response()->json(['fail' => false, 'message' => 'Something went wrong'], 500);
        }
    }

    public function mergeTwoProduct(Request $request)
    {

        $firstName = $request->firstName;
        $firstNameID = $request->firstNameID;

        $firstManID = $request->firstManID;

        // $secondManID = $request->secondManID;

        if ($request->secondManID  == '' || $request->secondManID  == null) {
            $secondManID = 0;
        } else {
            $secondManID =  $request->secondManID;
        }

        // $secondName = $request->secondName;
        $secondName = $request->finalName;
        $secondNameID = $request->secondNameID;
        // $secondManID = $request->secondManID;

        // $query = " ";
        $res =  DB::statement(" UPDATE public.product1 SET active = 0, merge_status = 1  WHERE product_id = " . $firstNameID . " ");

        if ($res) {
            $rez =  DB::statement(" UPDATE public.product1 SET product_name = '" . $secondName . "' , manufacturer_id= " . $secondManID . ", active = 1 , merge_status = 1  WHERE product_id = " . $secondNameID . " ");

            if ($rez) {

                date_default_timezone_set('Asia/Kolkata');
                $datess = date('Y-m-d h:i:s');

                $letter = "A";

                $acLog = new ActivityLog();

                $acLog->userid = 1;
                $acLog->clientid = 354;
                $acLog->mergeletter = $letter;
                $acLog->activityslug = 'PRODUCTDOUBLE';
                $acLog->changeone = $firstName;
                $acLog->changetwo = $secondName;
                $acLog->description = " Product " . $firstName . " with id : " . $firstNameID . " is merged with " . $secondName . " with id : " . $secondNameID . " ";
                $acLog->dateandtime = $datess;

                $ressss = $acLog->save();

                if ($ressss) {
                    return back()->with('success', 'Product Merged Successfully');
                } else {
                    return back()->with('fail', 'Something went wrong');
                }
            } else {
                return back()->with('fail', 'Something went wrong');
            }
        } else {
            return back()->with('fail', 'Something went wrong');
        }

        // return $query;


    }

    public function getReplaceProductStart(Request $request)
    {

        // $manufacturers = DB::select(" SELECT * from division WHERE LOWER(division_name) LIKE LOWER('%" . $request->search . "%') ");

        // return $manufacturers;

        // foreach ($manufacturers as $man) {
        //     $response[] = array("value" => $man->auto_id, "label" => $man->division_name);
        // }

        // echo json_encode($response);

        $products = DB::select(" select product_name,short_id,cntproduct,product_id,manufacturer_name,mfr_newid,active from (
            select product_name,short_id,product_id,manufacturer_name,mfr_newid,active from product1 p 
            left join manufacturer m on p.manufacturer_id = m.mfr_newid 	
            )p left join ( select product_name as cntpname,count(product_name) as cntproduct from product1 group by product_name having count(product_name)>1) cntp on 
                          p.product_name = cntp.cntpname
                         where cntproduct>0 and LOWER(product_name) LIKE LOWER('%" . $request->search . "%') and active = 1
                          order by 1  ");

        return $products;

        foreach ($products as $man) {

            $response[] = array("value" => $man->product_id, "label" => $man->product_name, "id" => $man->manufacturer_name, "mfid" => $man->mfr_newid);
        }

        echo json_encode($response);
    }

    public function getproductDetByID(Request $request)
    {
        $id = $request->product_id;

        // $response = DB::select(" select product1.product_name,manufacturer.manufacturer_name from product1 
        //   join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        //   where product1.auto = " . $id . " ");

        // $response = Product::select('product1.product_name', 'manufacturer.manufacturer_name')
        //     ->join('manufacturer', 'product1.manufacturer_id', '=', 'manufacturer.mfr_newid')
        //     ->where('product1.auto', '=', $id)
        //     ->get();
        // $response = Product::select('product1.product_name', 'manufacturer.manufacturer_name')
        //     ->leftJoin('manufacturer', 'product1.manufacturer_id', '=', 'manufacturer.mfr_newid')
        //     ->where('product1.auto', '=', $id)
        //     ->get();

        $response = DB::select(" SELECT * from product1 WHERE product_id = $id ");

        return $response;

        //  echo json_encode($id);
    }

    public function getProductDetailsByID(Request $request)
    {
        $id = $request->product_id;

        // $response = DB::select(" SELECT product1.*,manufacturer.manufacturer_name,manufacturer.mfr_newid,division.division_name,division.auto_id from product1
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid 
        // left join division on product1.division = division.auto_id
        // WHERE product_id = $id ");

        $response = DB::select(" SELECT 
        COALESCE(p.product_id,0) as product_id,
        COALESCE(p.product_name,'null') as product_name,
        COALESCE(m.manufacturer_name,'null') as manufacturer_name,
        COALESCE(m.mfr_newid,0) as mfr_newid,
        COALESCE(d.division_name,'null') as division_name,
        COALESCE(d.auto_id,0) as auto_id  
        from product1 p
                left join manufacturer m on p.manufacturer_id = m.mfr_newid 
                left join division d on p.division = d.auto_id
                WHERE product_id = $id ");

        return $response;
    }

    public function getAllProductsByCompany(Request $request)
    {
        $id = $request->company_id;

        // $response = DB::select(" SELECT product1.*,manufacturer.manufacturer_name,division.division_name,division.auto_id,manufacturer.mfr_newid from product1
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        // left join division on product1.division = division.auto_id
        // WHERE manufacturer_id = $id ORDER BY product1.product_name ");

        $response = DB::select(" SELECT 
COALESCE(p.product_id,0) as product_id,
COALESCE(p.product_name,'null') as product_name,
COALESCE(m.manufacturer_name,'null') as manufacturer_name,
COALESCE(d.division_name,'null') as division_name,
COALESCE(d.auto_id,0) as auto_id,
COALESCE(m.mfr_newid,0) as mfr_newid
from product1 p
        left join manufacturer m on p.manufacturer_id = m.mfr_newid
        left join division d on p.division = d.auto_id
        WHERE p.manufacturer_id = $id ORDER BY p.product_name ");

        return $response;
    }

    public function getAllProductsByDivision(Request $request)
    {
        $id = $request->company_id;

        // $response = DB::select(" SELECT product1.product_name,product1.product_id,manufacturer.manufacturer_name,division.division_name,division.auto_id,manufacturer.mfr_newid from product1
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        // left join division on product1.division = division.auto_id
        // WHERE division.auto_id = $id ");

        $response = DB::select(" SELECT 
        COALESCE(p.product_name,'null') as product_name,
        COALESCE(p.product_id,0) as product_id,
        COALESCE(m.manufacturer_name,'null') as manufacturer_name,
        COALESCE(d.division_name,'null') as division_name,
        COALESCE(d.auto_id,0) as auto_id,
        COALESCE(m.mfr_newid,0) as mfr_newid
        from product1 p
                left join manufacturer m on p.manufacturer_id = m.mfr_newid
                left join division d on p.division = d.auto_id
                WHERE d.auto_id = $id ");

        return $response;
    }

    public function mergeResult(Request $request)
    {

        $products = $request->products;
        $exProID = $request->proID;

        $newManufacturer = $request->newProManf;
        $newProName = $request->newProName;

        $t = time();

        $dANDt = date("Y-m-d", $t);

        $res = DB::statement(" UPDATE public.imported_substitutes
        SET nname= '" . $newProName . "' , ncompany= '" . $newManufacturer . "' , m_status=1, m_by=100, m_datetime='" . $dANDt . "'
        WHERE auto_id = " . $exProID . "; ");

        if ($res) {

            return response()->json(['success' => true, 'message' => 'Product merged successfully'], 200);
            // return back()->with('success', 'Product merged Successfully');
        } else {
            return response()->json(['fail' => false, 'message' => 'Something went wrong'], 500);
            // return back()->with('fail', 'Something went wrong');
        }

        // return $res;
    }

    public function saveOrUpdateProduct(Request $request)
    {

        $addDiscount = 0;
        $conFreeAsDis = 0;
        $disableAddDis = 0;
        $nonStockItem = 0;
        $priceControlled = 0;
        $resEandB = 0;
        $resMRP = 0;
        $retailDis = 0;

        if ($request->get('saleCheck') == null) {
            $addDiscount = 0;
            $conFreeAsDis = 0;
            $disableAddDis = 0;
            $nonStockItem = 0;
            $priceControlled = 0;
            $resEandB = 0;
            $resMRP = 0;
            $retailDis = 0;
        } else {
            if (in_array('addDiscount', $request->get('saleCheck'))) {
                $addDiscount = 1;
            } else if (in_array('conFreeAsDis', $request->get('saleCheck'))) {
                $conFreeAsDis = 1;
            } else if (in_array('disableAddDis', $request->get('saleCheck'))) {
                $disableAddDis = 1;
            } else if (in_array('nonStockItem', $request->get('saleCheck'))) {
                $nonStockItem = 1;
            } else if (in_array('priceControlled', $request->get('saleCheck'))) {
                $priceControlled = 1;
            } else if (in_array('resEandB', $request->get('saleCheck'))) {
                $resEandB = 1;
            } else if (in_array('resMRP', $request->get('saleCheck'))) {
                $resMRP = 1;
            } else if (in_array('retailDis', $request->get('saleCheck'))) {
                $retailDis = 1;
            }
        }



        $maxAuto = Product::max('auto') + 1;
        $maxID = Product::max('id') + 1;
        $product_id = Product::max('product_id') + 1;

        // insertion values :
        if ($request->proID != null) {
            $proID = $request->proID;
        } else {
            $proID = $product_id;
        }

        if ($request->proCompanyID != null) {
            $proCompanyID = $request->proCompanyID;
        } else {
            $proCompanyID = 0;
        }


        if ($request->proDivisionID != null) {
            $proDivisionID = $request->proDivisionID;
        } else {
            $proDivisionID = 0;
        }

        // geeric names
        $genericnames = '0';

        if ($request->genericnames != '') {
            foreach ($request->genericnames as $genr) {

                if ($genericnames == '') {

                    $genericnames =  $genr;
                } else {

                    $genericnames = $genericnames . ',' . $genr;
                }
            }
        } else {
            $genericnames = '';
        }


        // if ( != null) {
        //     $genericnames = $request->genericnames;
        // } else {
        //     $genericnames = 0;
        // }
        // generic namesclose

        if ($request->proSubcatID != null) {

            $proSubcatID = $request->proSubcatID;
        } else {

            $cateType = 'm';
            $expirycheck = 'n';
            $maxCatID = SubCategoryModel::max('category_id') + 1;

            $qry1 = " INSERT INTO public.category1(
                category_type, category_name, expiry_check , category_id)
                VALUES ";

            $qry1 .= " ('" . $cateType . "' , 
            '" . $request->proSubcat . "', 
            '" . $expirycheck . "' ,
            " . $maxCatID . " ) ";

            $res = DB::statement(substr($qry1, 0, -1));

            if ($res) {
                $proSubcatID = $maxCatID;
            } else {
                $proSubcatID = 0;
            }
        }

        if ($request->proSchedule != null) {
            $proSchedule = $request->proSchedule;
        } else {
            $proSchedule = 0;
        }

        if ($request->proTaxCatID != null) {
            $proTaxCatID = $request->proTaxCatID;
        } else {
            $proTaxCatID = 0;
        }

        if ($request->proPrintOrder != null) {
            $proPrintOrder = $request->proPrintOrder;
        } else {
            $proPrintOrder = 0;
        }

        if ($request->proMaterialCode != null) {
            $proMaterialCode = $request->proMaterialCode;
        } else {
            $proMaterialCode = 0;
        }

        if ($request->proSubHead != null) {
            $proSubHead = $request->proSubHead;
        } else {
            $proSubHead = 0;
        }

        if ($request->proGST != null) {
            $proGST = $request->proGST;
        } else {
            $proGST = 0;
        }

        if ($request->proCGST != null) {
            $proCGST = $request->proCGST;
        } else {
            $proCGST = 0;
        }

        if ($request->proSGST != null) {
            $proSGST = $request->proSGST;
        } else {
            $proSGST = 0;
        }

        if ($request->proDesc != null) {
            $proDesc = $request->proDesc;
        } else {
            $proDesc = 0;
        }

        $proCess = 0;
        $proCess2 = 0;

        $zero = 0;
        $userRole = session('LoggedUser')['userRole'];

        if ($userRole == 'admin') {
            $active = 1;
        } else {
            $active = 2;
        }


        if ($request->proID == null || $request->proID == '') {

            $exist = Product::where('product_name', '=', $request->proName)
                ->where('manufacturer_id', '=', $proCompanyID)->first();

            $qry = '';

            if ($exist) {
                return back()->with('fail', 'Product already exist');
            } else {


                $qry = " INSERT INTO public.product1(
                    product_name, manufacturer_id, category_id, generic_id, rack, minlevel, maxlevel, category_type, schedule, hsncode, subhead, gst, 
                    division, active, description, taxcategory_id, disablesalesaddldiscount, salesprintorder, sprntbatchexprestrict, sprntmrprestrict,
                     material_code, disablestockinsertion, cess, disable_salesaddldiscount,  product_id, p, consider_freediscount, cess2, 
                     additionaldis, pricecontrolled, retaildiscount, id, gen_ids , merge_status , category_subtype) VALUES  ";


                $qry .= " ('" . $request->proName . "' , 
                         " . $proCompanyID . ", 
                         " . $proSubcatID . ", 
                         " . $zero . ", 
                         " . $zero . ", 
                         " . $zero . ", 
                         " . $zero . ", 
                         '" . $request->proType . "' , 
                         '" . $proSchedule . "' , 
                         '" . $request->proHSN . "' , 
                         '" . $proSubHead . "' , 
                         " . $proGST . ", 
                         " . $proDivisionID . ", 
                         " . $active . ", 
                         '" . $proDesc . "' , 
                         " . $proTaxCatID . " , 
                         '" . $disableAddDis . "' , 
                         '" . $proPrintOrder . "' , 
                         '" . $resEandB . "' , 
                         '" . $resMRP . "' , 
                         '" . $proMaterialCode . "' , 
                         " . $nonStockItem . ", 
                         " . $proCess . ", 
                         " . $zero . ", 
                         " . $product_id . ", 
                         '" . $zero . "', 
                         " . $conFreeAsDis . ", 
                         " . $proCess2 . ", 
                         " . $addDiscount . ", 
                         " . $priceControlled . ", 
                         " . $retailDis . ", 
                         " . $maxID . ", 
                         '" . $genericnames . "',
                         " . $zero . ", 
                         " . $request->prosubType . "
                         ) ";

                $res = DB::statement(substr($qry, 0, -1));

                if ($res) {

                    $userid =  session('LoggedUser')['id'];
                    $clientid = '100';
                    $mergeletter = 'nil';
                    $activityslug = 'product';
                    $changeone = 'New product created';
                    $changetwo =  'Product name : ' . $request->proName . ' ';
                    $description = '  New product created with name ' . $request->proName . ' by user id : ' . $userid . '';

                    $getRes = ActivityLogController::recordActivity(
                        $userid,
                        $clientid,
                        $mergeletter,
                        $activityslug,
                        $changeone,
                        $changetwo,
                        $description
                    );

                    if ($getRes) {
                        return back()->with('success', 'Product created Successfully');
                    } else {
                        return back()->with('fail', 'Something went wrong');
                    }
                } else {
                    return back()->with('fail', 'Something went wrong');
                }
            }



            // return $request;
        } else {

            $activestatus = 1;

            $quer = " UPDATE public.product1
            SET product_name='" . $request->proName . "', manufacturer_id=" . $proCompanyID . ",
             category_id= " . $proSubcatID . ", generic_id= " . $maxID . " ,
             rack=" . $zero . ", minlevel=" . $zero . ", maxlevel=" . $zero . ",
              category_type='" . $request->proType . "', schedule='" . $proSchedule . "',
              hsncode='" . $request->proHSN . "', subhead='" . $proSubHead . "', 
              gst=" . $proGST . ", division=" . $proDivisionID . ", active=" . $activestatus . ",
               description='" . $proDesc . "', taxcategory_id=" . $proTaxCatID . ", disablesalesaddldiscount='" . $disableAddDis . "',
                salesprintorder='" . $proPrintOrder . "', sprntbatchexprestrict='" . $resEandB . "',
                 sprntmrprestrict='" . $resMRP . "', material_code='" . $proMaterialCode . "', disablestockinsertion=" . $nonStockItem . ",
                  cess=" . $proCess . ", disable_salesaddldiscount=" . $zero . "
                  , p='" . $zero . "', consider_freediscount=" . $conFreeAsDis . ",
                    cess2=" . $proCess2 . ", additionaldis=" . $addDiscount . ", pricecontrolled=" . $priceControlled . ",
                     retaildiscount=" . $retailDis . ", id=" . $maxID . ", gen_ids='" . $genericnames . "' , category_subtype = " . $request->prosubType . "

            WHERE product_id = " . $proID . "";


            $res = DB::statement($quer);

            if ($res) {

                $userid =  session('LoggedUser')['id'];
                $clientid = '100';
                $mergeletter = 'nil';
                $activityslug = 'product';
                $changeone = 'Product updated';
                $changetwo =  'Product name : ' . $request->proName . ' ';
                $description = '  Product updated with name ' . $request->proName . ' with product id : ' . $proID . ' by user id : ' . $userid . '';

                $getRes = ActivityLogController::recordActivity(
                    $userid,
                    $clientid,
                    $mergeletter,
                    $activityslug,
                    $changeone,
                    $changetwo,
                    $description
                );

                if ($getRes) {
                    return back()->with('success', 'Product updated Successfully');
                } else {
                    return back()->with('fail', 'Something went wrong');
                }
            } else {
                return back()->with('fail', 'Something went wrong');
            }

            // return $request;
        }

        return $request;
    }

    public function getSimilarProducts(Request $request)
    {
        // $search = str_replace(' ', '%', $request->search);
        // $search = explode(' ', $request->search);

        // $quer = " SELECT * from product1 WHERE LOWER(product_name) LIKE LOWER('".$request->search."%') ";


        $newSearch = str_replace(' ', '%', $request->search);

        $products = DB::select(" SELECT * from product1 WHERE LOWER(product_name) LIKE LOWER('" . $newSearch . "%') and active = 1 ORDER BY 1 LIMIT 500  ");

        return $products;

        // foreach ($products as $man) {
        //     $response[] = array("value" => $man->product_id, "label" => $man->product_name);
        // }

        // echo json_encode($response);
    }

    public function getSimilarProductswithmfr(Request $request)
    {
        // $search = str_replace(' ', '%', $request->search);
        // $search = explode(' ', $request->search);

        // $quer = " SELECT * from product1 WHERE LOWER(product_name) LIKE LOWER('".$request->search."%') ";

        $newSearch = str_replace(' ', '%', $request->search);

        // $products = DB::select(" SELECT *,manufacturer.manufacturer_name as mfrname from product1
        // left join manufacturer on product1.manufacturer_id = manufacturer.mfr_newid
        // WHERE LOWER(product1.product_name) LIKE LOWER('" . $newSearch . "%') and active = 1 LIMIT 500 ");

        $products = DB::select(" SELECT p.*,m.manufacturer_name as mfrname from product1 p
		left join manufacturer m on p.manufacturer_id = m.mfr_newid
		where LOWER(p.product_name) LIKE LOWER('" . $newSearch . "%') and active = 1 order by product_name LIMIT 500 ");

        return $products;

        // foreach ($products as $man) {
        //     $response[] = array("value" => $man->product_id, "label" => $man->product_name);
        // }

        // echo json_encode($response);
    }


    public function manageproduct()
    {
        // $data = [
        //     'products' => DB::select(" select * from product1 ")
        // ];

        $menustring = ToolUsers::where('id', '=', session('LoggedUser')['id'])->pluck('menu')->first();
        // $menustring = ProductMenu::where('id','!=',0)->pluck('slug');

        $data = [

            'productscount' => Product::where('active', '=', 1)->count(),
            'duplicateproducts' => DuplicateProductRecord::all()->count(),
            'generalproducts' => Product::where('active', '=', 1)->where('category_type_text', '=', 'g')->count(),
            'medicalproducts' => Product::where('active', '=', 1)->where('category_type_text', '=', 'm')->count(),

            'products' => DB::select(" select p.*,m.manufacturer_name,m.mfr_newid,g.generic_name,g.generic_id,
            cat.category_name,
            cat.category_id,
            pt.id as taxcatid,
            pt.name as taxname,
            div.auto_id as divid,
            div.division_name
            from product1 p
            
            left join manufacturer m  on p.manufacturer_id = m.mfr_newid
            
            left join ( select generic_name,generic_id from generic1) g on p.genericid = g.generic_id

            left join ( select category_name,category_id from category1) cat on p.category_id = cat.category_id
        
            left join ( select name,id from ptaxcat1) pt on p.taxcategory_id = pt.id

            left join ( select division_name,auto_id from division )div on p.division = div.auto_id
            
            order by p.product_name asc LIMIT 1000 "),

            'companies' => Manufacturer::all(),
            'generics' => GenericModel::all(),
            'type' => TypeModel::where('status', '=', 1)->get(),
            'subtype' => SubTypeModel::where('status', '=', 1)->get(),
            'menulist' => explode(",", $menustring),
            // 'menulist' => $menustring
        ];

        return view('Admin.product_manage', $data);
        // return $data;
    }

    public function manageproductrequest()
    {
        // get product details 

        $data = [

            'products' => DB::select(" select p.*,m.manufacturer_name,m.mfr_newid,g.generic_name,g.generic_id,
            cat.category_name,
            cat.category_id,
            pt.id as taxcatid,
            pt.name as taxname,
            div.auto_id as divid,
            div.division_name
            from product1 p
            
            left join manufacturer m  on p.manufacturer_id = m.mfr_newid
            
            left join ( select generic_name,generic_id from generic1) g on p.genericid = g.generic_id

            left join ( select category_name,category_id from category1) cat on p.category_id = cat.category_id
        
            left join ( select name,id from ptaxcat1) pt on p.taxcategory_id = pt.id

            left join ( select division_name,auto_id from division )div on p.division = div.auto_id
            
            where p.active = 2

            order by p.product_name asc LIMIT 2000 "),

        ];

        return view('Admin.product_request', $data);
        // return $data;
    }



    public function searchProduct(Request $request)
    {
        $proid = $request->proid;

        // $product = Product::select(
        //     "product1.*",
        //     "manufacturer.manufacturer_name",
        //     "manufacturer.mfr_newid",
        //     "generic1.generic_name",
        //     "generic1.generic_id",
        //     "category1.category_name",
        //     "category1.category_id",
        //     "ptaxcat1.id as taxcatid",
        //     "ptaxcat1.name as taxname",
        //     "division.auto_id as divid",
        //     "division.division_name"
        // )

        //     ->join('manufacturer', 'product1.manufacturer_id', '=', 'manufacturer.mfr_newid')

        //     ->join('generic1', 'product1.generic_id', '=', 'generic1.generic_id')

        //     ->join('category1', 'product1.category_id', '=', 'category1.category_id')

        //     ->join('ptaxcat1', 'product1.taxcategory_id', '=', 'ptaxcat1.id')

        //     ->join('division', 'product1.division', '=', 'division.auto_id')

        //     ->where('product1.product_id', '=', $proid)

        //     ->get();

        $product = DB::select(" select p.*,m.manufacturer_name,m.mfr_newid,g.generic_name,g.generic_id,
        cat.category_name,
        cat.category_id,
        pt.id as taxcatid,
        pt.name as taxname,
        div.auto_id as divid,
        div.division_name,
        m.short_id
        from product1 p
        
        left join manufacturer m  on p.manufacturer_id = m.mfr_newid
        
        left join ( select generic_name,generic_id from generic1) g on p.genericid = g.generic_id

        left join ( select category_name,category_id from category1) cat on p.category_id = cat.category_id

        left join ( select name,id from ptaxcat1) pt on p.taxcategory_id = pt.id

        left join ( select division_name,auto_id from division )div on p.division = div.auto_id

        where 1>0 and p.product_id = " . $proid . "
        
        order by p.product_name asc LIMIT 100");

        return $product;
    }
}
