Monday 31 December 2018

sql_mode=only_full_group_by is incompatible in PHP mysql query with GROUP BY codeigniter


#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customers.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Add $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));") line above your query.

See Example :
$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
$this->db->select('*, customers.first_name, customers.last_name, customers.id');
$this->db->select("(SELECT count(video_id) FROM customer_videos WHERE c.customer_id = customer_videos.customer_id AND (created_at >= '$from' OR created_at <= '$todate')) as seen");
$this->db->join('customers', 'customers.id = c.customer_id', 'left');
$this->db->group_by('c.customer_id');
$this->db->from('customer_videos as c');
$query = $this->db->get();
echo "<pre/>"; print_r($query->result());exit;
return $query->result_array();

Thursday 27 December 2018

No space left on device in server | PHPMysql not connect session | Full Inodes 100% | Check CPU Process and MEMORY

Issues
  1. open(/var/lib/php/sessions/sess_, O_RDWR) failed: No space left on device (28)
  2. PHPMysql not connect
  3. Database not connect
Solusions

Check CPU and MEMORY usage of server from terminal
ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%cpu | head
df -h
Check file size
find /home/ -size +100M 

 

 Check INODES in server (Inode mean total files into server)
df -i
Search INODES from system and perticular folder (Get number of file in perticular directory)
for i in /var/www/html/*; do echo $i; find $i |wc -l; done
Check INODES into specific directory
ls -l /var/lib/php/sessions | wc -l
Remove all files from specific directory
rm -r /var/lib/php/sessions/ 
If you are using PHP so you can create code in any existing file
$path = '/var/lib/php/sessions/';
$dir = opendir($path);
$i = 0;
while ($dir && ($file = readdir($dir)) !== false) {
    unlink($path.$file);
    echo $i.' - '.$path.$file.'<br>';
    if($i == 50000){
        exit;
    }
    $i++;
}

Friday 21 December 2018

How to create snapshot and attach with another instanse

  1. Stop current instanse
  2. Create new Snapshot (Please check volume properly) from current volume
  3. Create new instanse with greter or equal to current volume (GB) from old instanse
  4. Stop New instanse
  5. Goto spanshot and create volume with (us-east-1d)
  6. Goto volume and detach currnet volume of new instanse
  7. attach new volume which you have created from snapshot (us-east-1d) make sure same root (/dev/sda1)
For your ref : https://www.youtube.com/watch?v=W89C_OqlOwE

Thursday 13 December 2018

How to generate new SSH key and adding it to the ssh-agent

1. Open Terminal
2. Paste the text, change email address. $ ssh-keygen -t rsa -b 4096 -C "jaydipkansagra@example.com"
3. Enter a file in which to save the key (/root/.ssh/id_rsa): [Press enter] (Recommend press enter)
4. Enter passphrase (empty for no passphrase): [Type password] (Recommend keep blank)
5. Enter same passphrase again: [Type password again] (Recommend keep blank)
6. For ubuntu
     Start the ssh-agent - $ eval "$(ssh-agent -s)"
   For windows
     1. $ ssh-agent bash
     2. $ eval "$(ssh-agent -s)" 
7. $ ssh-add ~/.ssh/id_rsa
8. Open  ssh-keygen.pub
9. Copy key from ssh-keygen.pub
10. Add into ssh-agent
11. Now, get data from your repository : git clone git@github.com:whatever

Saturday 8 December 2018

How to log all | full SQL | Mysql queries in Magento 2

Show ALL | Full magento 2 query with Sorting, Pagination and Filter in Magento 2

Open : /var/www/html/magento/jaydip kansagra/app/etc/di.xml


Find 

<preference for="Magento\Framework\DB\LoggerInterface" 

Replace

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
 <type name="Magento\Framework\DB\Logger\File">
      <arguments>
          <argument name="logAllQueries" xsi:type="boolean">true</argument>
          <argument name="debugFile" xsi:type="string">log/sql.log</argument>
      </arguments>
 </type>


Open : /var/www/html/magento/frye-magento/var/log/sql.log

Order saving error: SQLSTATE[HY000]: General error: 1449 The user specified as a definer in Magento 2

Run following query into your phpmyadmin

GRANT ALL ON *.* TO 'username'@'%' IDENTIFIED BY 'Jaydip@123';
FLUSH PRIVILEGES;

Wednesday 28 November 2018

Join Query with LIKE in PHP Mysql

Join Query with %LIKE% function in MYSQL PHP

SELECT `customers`.*, `admin`.`firstname` AS `salespersonfname`, `admin`.`lastname` AS `salespersonlname`
FROM `customers`
LEFT JOIN `admin` ON customers.tags LIKE CONCAT('%', `admin`.firstname, ' ', `admin`.lastname, '%')
ORDER BY `id` DESC

Monday 26 November 2018

How to get filter data from report filter string | parameter URL in Magento 1




$filterData = Mage::helper('adminhtml')->prepareFilterString($this->getRequest()->getParam('filter', false));

print_r($filterData);exit;

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'having clause', query was: SELECT COUNT(*) in Magento 1

Step 1. Open Collection.php of that model : root/app/code/local/Jaydip/Kansagra/Model/Resource/Rebatecash/Collection.php
Step 2.  Create getSelectCountSql() function in Collection.php file
public function getSelectCountSql() {
        $this->_renderFilters();
        $select = clone $this->getSelect();
        $select->reset(Zend_Db_Select::ORDER);
        $select->reset(Zend_Db_Select::LIMIT_COUNT);
        $select->reset(Zend_Db_Select::LIMIT_OFFSET);       
        $countSelect = clone $this->getSelect();
        $countSelect->reset();
        $countSelect->from(array('a' => $select), 'COUNT(*)');
        return $countSelect;
    }

Saturday 24 November 2018

Optimized my.cnf MySQL configuration due to hign CPU usage in Ubuntu

# Optimized my.cnf configuration for MySQL/MariaSQL
#
# by Fotis Evangelou, developer of Engintron (engintron.com)
#
# === Updated July 2018 ===
#
# The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
# If you have less or more resources available you should adjust accordingly to save CPU,
# RAM and disk I/O usage.
# The settings marked with a specific comment or the word "UPD" after the value
# should be adjusted for your system by using database diagnostics tools like:
# https://github.com/major/MySQLTuner-perl
# or
# https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6)
#
#
# Note that if there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
#
#
# THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING
# If any terminal commands are mentioned, make sure you execute them as "root" user.
# If MySQL cannot start or restart, then perform the following actions.
#
# 1. If the server had the stock database configuration and you added or updated any
#    "innodb_log_*" settings (as suggested below), then execute these commands ONLY
#    the first time you apply this configuration:
#
#    $ rm -rvf /var/lib/mysql/ib_logfile*
#    $ chown -R mysql:mysql /var/lib/mysql
#    $ service mysql restart
#
#    or use the shorthand command:
#    $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart
#
# 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
#    properly configured. A good example of a "clean" /etc/hosts file is something like this:
#
#    127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
#    ::1       localhost localhost.localdomain localhost6 localhost6.localdomain6
#    1.2.3.4   hostname.domain.tld hostname # Replace accordingly!
#
#    Finally restart the database service:
#
#    $ service mysql restart
#
# 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder
#    (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group.
#    Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this:
#    $ chown -R mysql:mysql /var/lib/mysql
#    $ chmod 0755 /var/lib/mysql
#
#    Finally restart the database service:
#
#    $ service mysql restart
[mysql]
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock
[mysqld]
# Required Settings
basedir                         = /usr
bind_address                    = 127.0.0.1 # Change to 0.0.0.0 to allow remote servers to connect to this server's
                                            # database instance
datadir                         = /var/lib/mysql
max_allowed_packet              = 256M
max_connect_errors              = 1000000
pid_file                        = /var/run/mysqld/mysqld.pid
port                            = 3306
skip_external_locking
skip_name_resolve
socket                          = /var/run/mysqld/mysqld.sock
#sql_mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES # Enable for b/c with databases
                                                                                                                                               # using null dates
tmpdir                          = /tmp
user                            = mysql
# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 1G    # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 128M
#innodb_thread_concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
                                        # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
                                        # the overall load produced by MySQL/MariaDB.
# MyISAM Settings
query_cache_limit               = 4M    # UPD
query_cache_size                = 48M   # UPD
query_cache_type                = 1
key_buffer_size                 = 48M   # UPD
low_priority_updates            = 1
concurrent_insert               = 2
# Connection Settings
max_connections                 = 100   # UPD
back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K
interactive_timeout             = 180
wait_timeout                    = 180
# Buffer Settings
join_buffer_size                = 3M    # UPD
read_buffer_size                = 2M    # UPD
read_rnd_buffer_size            = 4M    # UPD
sort_buffer_size                = 4M    # UPD
# Table Settings
# In systemd managed systems like Ubuntu 16.04 or CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache          = 8000  # UPD
table_open_cache                = 8000  # UPD
open_files_limit                = 24000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
                                        # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
                                        # In systemd managed systems this limit must also be set in:
                                        # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
                                        # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
max_heap_table_size             = 128M
tmp_table_size                  = 128M
# Search Settings
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results
# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log
[mysqldump]
# Variable reference
# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:   https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet              = 64M

Refer From : https://gist.github.com/fevangelou/fb72f36bbe333e059b66 

How to get only one record in Join Query in Magento

$orders->getSelect()->join(["Category" => "catalog_category_product"], "Category.category_id=(
    select category_id from catalog_category_product AS p1 where SFOI.product_id = p1.product_id limit 1
    ) AND SFOI.product_id=Category.product_id", []);

Saturday 17 November 2018

Mysql usage grater than 100% of CPU in ubuntu 18.04 | 16.04 in AWS

 You need to update server configuration.

1. Open terminal
2. Put command : sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf




3. Find following variables
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
query_cache_limit      = 1M
query_cache_size        = 16M
Change To
key_buffer_size         = 160M
max_allowed_packet      = 160M
thread_stack            = 1920K
thread_cache_size       = 80
query_cache_limit       = 1024M
query_cache_size        = 2048M
4. Restart mysql : sudo /etc/init.d/mysql restart
5. Restart apache2 sudo systemctl restart apache2
6. Check CPU usage : ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%cpu | head
 For more Information please Refer : https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

Tuesday 13 November 2018

How to override third party vendor block file in magento 2.2

If you need to override third party or Magento file from vendor directory

Here I override Block file "/var/www/html/magento/magentoproject/vendor/onestop/image-server/Block/Product/View/Gallery.php"

FYI : You need to create new extension when you wish to override default Vendor or Magento file

1. Create new Extension - Jaydip (Namespace)
2. Create Module directory - ImagegalleryAlt (Modulename)
3. Create new file like ( /var/www/html/magento/magentoproject/app/code/Jaydip/ImagegalleryAlt/etc/di.xml )

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
  <preference for="Onestop\ImageServer\Block\Product\View\Gallery" type="Jaydip\ImagegalleryAlt\Block\Product\View\Gallery" />
</config>
4. Create new directory "etc" and create file like : /var/www/html/magento/magentoproject/app/code/Jaydip/ImagegalleryAlt/etc/module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Jaydip_ImagegalleryAlt" setup_version="0.1.1">
        <sequence>
            <module name="Magento_Catalog"/>
            <module name="Onestop_ImageServer"/>
        </sequence>
    </module>
</config>
5. Create new directory "Block/Product/View" and create file like : /var/www/html/magento/magentoproject/app/code/Jaydip/ImagegalleryAlt/Block/Product/View/Gallery.php
  • Copy file from old directory and paste to : /var/www/html/magento/magentoproject/app/code/Jaydip/ImagegalleryAlt/Block/Product/View/Gallery.php
  • Open : /var/www/html/magento/magentoproject/app/code/Jaydip/ImagegalleryAlt/Block/Product/View/Gallery.php
  • Change namespace like : namespace Jaydip\ImagegalleryAlt\Block\Product\View;
6. Upgrade data in Magento 2
php -dmemory_limit=5G bin/magento setup:upgrade
7. Static Content Deploy data  in Magento 2
php -dmemory_limit=5G bin/magento setup:static-content:deploy -f
8. Finish!!!

Thursday 11 October 2018

How to redirect www to non www AND http to https in AWS .htaccess


AWS WWW to non WWW redirection
RewriteEngine On
RewriteCond %{SERVER_NAME} =www.example.com
RewriteRule ^.*$ https://example.com%{REQUEST_URI}
AWS HTTP to HTTPS redirection
RewriteEngine On
RewriteCond %{HTTP:X-Forwarded-Proto} !https
RewriteRule ^.*$ https://example.com%{REQUEST_URI}
AWS WWW to non WWW and HTTP to HTTPS
RewriteEngine On
RewriteCond %{SERVER_NAME} =www.example.com
RewriteRule ^.*$ https://example.com%{REQUEST_URI}
RewriteCond %{HTTP:X-Forwarded-Proto} !https
RewriteRule ^.*$ https://example.com%{REQUEST_URI}

Friday 5 October 2018

How to get list of all categories array tree view in Magento 1

Call Category function

$categories = $this->getTreeCategories(2, false);
print_r($categories);
Get All categories array tree view

function getTreeCategories($parentId, $isChild) {
     
        $allCats = Mage::getModel('catalog/category')->getCollection()
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('is_active', '1')
                ->addAttributeToFilter('include_in_menu', '1')
                ->addAttributeToFilter('parent_id', array('eq' => $parentId));
        $subcat = ($isChild) ? "&nbsp;&nbsp;-- " : "";
        foreach ($allCats as $category) {
            $options[] = array(
                'label' => Mage::helper('jaydip_attrfiltercount')->__($subcat.iconv(mb_detect_encoding($category->getName(), mb_detect_order(), true), "UTF-8", $category->getName())),
                'value' => $category->getId()
            );
            $subcats = $category->getChildren();
            if ($subcats != '') {
                $options[] = $this->getTreeCategories($category->getId(), true);
            }
        }
        $optiondata = array();
        foreach ($options as $option):
            if(isset($option[0])){
             
                foreach ($option as $value):
                    $optiondata[] = array(
                        'label' => $value['label'],
                        'value' => $value['value']
                    );
                endforeach;
             
            }else{
                $optiondata[] = array(
                    'label' => $option['label'],
                    'value' => $option['value']
                );
            }
        endforeach;
     
        return $optiondata;
    }

Tuesday 2 October 2018

Product image Still zoom if I remove mouse cursor from image in magento 2

You need to replace the code of lib/web/magnifier/magnifier.js in your theme as below.

Find

$(document).on('mousemove', onMousemove);
_init($box, gOptions);

Replace

$box.on('mousemove', onMousemove);
       
/*Code By Jaydip*/
$box.on('mouseleave', mouseleave);
    function mouseleave(e) {
         onThumbLeave();
         isOverThumb = false;
         $magnifierPreview.addClass(MagnifyCls.magnifyHidden);
    }
/*Code By Jaydip*/
       
 _init($box, customUserOptions);

Wednesday 26 September 2018

SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


will be simply solved by changing the sql mode in MySQL by this command,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
OR

set @@global.show_compatibility_56=ON;

OR from CMD
mysql -u root -p mg_cstore < db.sql
This is too works me.. I used this becz in my project there are many Queries like this so just change this sql mode only_full_group_by


For permenant solution in ubuntu 16 and 18

Step1) find and modify the config file my.cnf. Usually it’s in /etc/my.cnf or /etc/mysql/my.cnf.
Step 2) Add following code end of the file
[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Saturday 22 September 2018

How to Skip | Hide shipping address in checkout Magento 1.9

Step 1 :

Create config.xml file : app/code/local/Jaydip/Kansagra/etc/config.xml

<config>
   <global>
        <blocks>
            <jaydip_kansagra>
                <class>Jaydip_Kansagra_Block</class>
            </jaydip_kansagra>
            <!-- For Hide Shipping address-->
            <checkout>
                <rewrite>
                    <onepage>Jaydip_kansagra_Block_Kansagra_Checkout_Onepage</onepage>
                </rewrite>
            </checkout>
            <!-- For Hide Shipping address-->
       </blocks>
       ...........
       ...........
       ...........
       <!-- For Hide Shipping address-->
        <rewrite>
            <hideshippingadd> <!--This can be any unique id -->
                <from><![CDATA[#^/checkout/onepage/#]]></from>  <!-- the URL which u want to override-->
                <to>/kansagra/onepage/</to>  <!-- destination url -->
            </hideshippingadd>
        </rewrite>
        <!-- For Hide Shipping address-->
       ...........
       ...........
       ...........
   </global>
</config>


Step 2 : 

Create OnepageController.php file : app/code/local/Jaydip/Kansagra/controllers/OnepageController.php

<?php
require_once Mage::getModuleDir('controllers', "Mage_Checkout") . DS . "OnepageController.php";
class Jaydip_Kansagra_OnepageController extends Mage_Checkout_OnepageController {
    public function saveBillingAction() {
        if ($this->_expireAjax()) {
            return;
        }
        if ($this->getRequest()->isPost()) {
            $data = $this->getRequest()->getPost('billing', array());
            $customerAddressId = $this->getRequest()->getPost('billing_address_id', false);
            if (isset($data['email'])) {
                $data['email'] = trim($data['email']);
            }
            $result = $this->getOnepage()->saveBilling($data, $customerAddressId);
            if (!isset($result['error'])) {
                if ($this->getOnepage()->getQuote()->isVirtual()) {
                    $result['goto_section'] = 'payment';
                    $result['update_section'] = array(
                        'name' => 'payment-method',
                        'html' => $this->_getPaymentMethodsHtml()
                    );
                } elseif (isset($data['use_for_shipping']) && $data['use_for_shipping'] == 1) {
                    $result['goto_section'] = 'shipping_method';
                    $result['update_section'] = array(
                        'name' => 'shipping-method',
                        'html' => $this->_getShippingMethodsHtml()
                    );
                 
                } else {
                    $result['goto_section'] = 'shipping';
                }
            }
            $this->getResponse()->setBody(Mage::helper('core')->jsonEncode($result));
        }
    }
}

Step 3 :

Create Onepage.php file : app/code/local/Jaydip/Kansagra/Block/Kansagra/Checkout/Onepage.php

<?php
class Jaydip_Kansagra_Block_Kansagra_Checkout_Onepage extends Mage_Checkout_Block_Onepage {
    protected function _getStepCodes() {
        return array('login', 'billing', 'shipping_method', 'payment', 'review');
    }
}

Step 4 : 

And now you need to copy billing.phtml from app/design/frontend/base/default/checkout/onepage

folder into app/design/frontend/rwd/default/template/newgenray/checkout folder.

Find following code

<li class="control">
<input type="radio" name="billing[use_for_shipping]" id="billing:use_for_shipping_yes" value="1"<?php if ($this->isUseBillingAddressForShipping()) {?> checked="checked"<?php }?> title="<?php echo Mage::helper('core')->quoteEscape($this->__('Ship to this address')) ?>" onclick="$('shipping:same_as_billing').checked = true;" class="radio" /><label for="billing:use_for_shipping_yes"><?php echo  $this->__('Ship to this address') ?></label>
</li>
<li class="control">
<input type="radio" name="billing[use_for_shipping]" id="billing:use_for_shipping_no" value="0"<?php if (!$this->isUseBillingAddressForShipping()) {?> checked="checked"<?php }?> title="<?php echo Mage::helper('core')->quoteEscape($this->__('Ship to different address')) ?>" onclick="$('shipping:same_as_billing').checked = false;" class="radio" /><label for="billing:use_for_shipping_no"><?php echo $this->__('Ship to different address') ?></label>
</li>

and replace this by

<li class="control">
   <input type="hidden" name="billing[use_for_shipping]" id="billing:use_for_shipping_yes" value="1"  />
</li>

Wednesday 19 September 2018

How to change url without reloading page in jQuery | Javascript | PHP

<?php
$actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$lastchar = substr($actual_link, -1);
if($lastchar == '/'){
$redirecturl = rtrim($actual_link,"/");
?>
<script>
    window.history.pushState('page2', 'Title', '<?php echo $redirecturl; ?>');
</script>
<?php } ?>

Friday 3 August 2018

How to add | update Shipping Price on existing order in Magento 1.9

$shippingamount = 10.00;
        try {
            $order = Mage::getModel('sales/order')->load(37881)
                    ->setShippingMethod('cstoreshipping_cstoreshipping')
                    ->setShippingAmount($shippingamount)
                    ->setBaseShippingAmount($shippingamount)
                    ->setShippingDescription('Shipping by third party');
            $order->setGrandTotal($order->getGrandTotal() + $shippingamount);
            $order->setBaseGrandTotal($order->getBaseGrandTotal() + $shippingamount);
            $order->save();
            pre($order->getData());
        } catch (Exception $e) {
            pre($e->getMessage());
        }

Wednesday 1 August 2018

How to get Missing / Deleted primary key from Mysql / PHP Database

select a.id + 1 RemoveIds
from jaydip_kansagra a
left join jaydip_kansagra b
  on a.id = b.id - 1
where b.id is null
  and a.id < 14481

14481 = Last Primary Key 

Saturday 14 July 2018

Re-Arrange primary key | INDEXING in MYSQL

Re-Arrange primary key | INDEXING in MYSQL
SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;
Last number of records
ALTER TABLE `jaydipkansagra` auto_increment = 720418;  

Tuesday 26 June 2018

How to create configurable product with simple products with attributes using controller in Magento 2 Programmatically | Custom | Custom Module

How to create configurable product with simple products in Magento 2


Step 1 : Create Cotroller

<?php

namespace Jaydip\Kansagra\Controller\Jaydip;

use Magento\Framework\App\Action\Context;

class Createproduct extends \Magento\Framework\App\Action\Action {

    protected $_resultpageFactory;
    protected $_eavAttributeFactory;
    protected $eavConfig;
    protected $directoryList;
    protected $file;

    public function __construct(
        \Magento\Framework\App\Action\Context $context,
        \Magento\Framework\View\Result\PageFactory $pageFactory,
        \Magento\Framework\App\Filesystem\DirectoryList $directoryList,
        \Magento\Framework\Filesystem\Io\File $file) {

        $this->_resultpageFactory = $pageFactory;
        $this->directoryList = $directoryList;
        $this->file = $file;
        return parent::__construct($context);
    }

    public function execute() {

        /*$postdata = $this->_request->getPost('product');*/
        $postdata = array(
                    'name' => 'Jaydip Kansagra Test product',
                    'magentoid' => 0,
                    'description' => 'description',
                    'categoryid' => 3,
                    'handle'    => 'jaydipkansagra',
                    'image'     => 'https://domain.com/jaydipkansagra.jpg',
                    'variants' => array(
                        '7407003205687' => array(
                            'id' => 7407003205687,
                            'title' => 'Jaydip Kansagra Test product Child 1',
                            'magechildid' => 0,
                            'sku' => 'CASCADETANK-Black',
                            'inventory' => 1,
                            'price' => 31.95,
                            'c_price' => 0,
                            'weight' => 6.8079,
                            'barcode' => 0,
                            'images'    => array(
                                0 => 'https://domain.com/jaydipkansagra.jpg'
                            )

                        ),
                        '7407003238455' => array(
                            'id' => 7407003238455,
                            'title' => 'Jaydip Kansagra Test product child 2',
                            'magechildid' => 0,
                            'sku' => 'CASCADETANK-Rainbow',
                            'inventory' => 1,
                            'price' => 31.95,
                            'c_price' => 0,
                            'weight' => 6.8079,
                            'barcode' => 0,
                            'images'    => array(
                                0 => 'https://domain.com/jaydipkansagra.jpg'
                            )
                        )

                    ),
                    'options'=> array(
                        'name'=>'Color',
                        'value'=>array(
                            0=> 'Red',
                            1=> 'Green'
                        )
                    )
        );

        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        $productattr = $objectManager->create('Jaydip\Kansagra\Helper\Productattr');
        $channelstore = $objectManager->create('Magento\Customer\Model\Session');
        $channelstore = $channelstore->getChannelStore();

        if($postdata['magentoid'] > 0){

            $productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
            $productCollection->addAttributeToSelect('*');
            $productCollection->addAttributeToFilter('entity_id', array('eq' => $postdata['magentoid']));
            $productCollection->addAttributeToFilter('type_id', array('eq' => 'configurable'));
            $magentoproduct = array();

            $magentoproduct['child'] = array();
            foreach ($productCollection as $product):
                if($product->getTypeId() == 'configurable'){
                    $_children = $product->getTypeInstance()->getUsedProducts($product);
                    foreach ($_children as $child){
                        $product = $objectManager->create('Magento\Catalog\Model\Product')->load($child->getId());
                        $magentoproduct['child'][] = json_decode(json_encode($product->getData()));
                    }
                }
            endforeach;
        }

        //Remove product from vapejuice which is removed
        if(isset($magentoproduct['child'])){
            foreach ($magentoproduct['child'] as $child):
                if(!isset($postdata['variants'][$child->source_variant_id])){
                    $registry = $objectManager->get('\Magento\Framework\Registry');
                    $registry->register('isSecureArea', true);
                    $removeproduct = $objectManager->create('\Magento\Catalog\Model\Product');
                    $removeproduct->load($child->entity_id);
                    $removeproduct->delete();
                }
            endforeach;
        }

        //Create / or get Attribute detail
        $attributes = $attrgroup = $associatedProductIds = array();
        foreach ($postdata['options'] as $j=>$option):
            $j++;
            foreach ($option['values'] as $value):
                try {
                    $attributes[$j][$value] = $productattr->createOrGetId($option['name'], $value);
                } catch (\Exception $e) {
                    echo $e->getMessage();
                }
            endforeach;

        endforeach;

        foreach ($postdata['variants'] as $variant): //Create Simple product

            $weight = $variant['weight'];
            $weight = ($weight!=0)?$weight:1;
            $price = $variant['price'];
            $inventory = $variant['inventory'];

            $product = $objectManager->create('\Magento\Catalog\Model\Product');
            if(isset($variant['magechildid']) && $variant['magechildid'] > 0){
                $product->load($variant['magechildid']);
            }

            $varianttitle = $variant['title'];
            $title = $postdata['name'].$varianttitle;
            $urltitle = str_replace(' ', '-', $title);
            $urlkey = preg_replace("#[^a-zA-Z0-9]+/[^a-zA-Z0-9]+#", "", strtolower($urltitle));
            $sku = ($variant['sku'] != '')?$variant['sku']:$urltitle;

            $product->setName($title);
            $product->setUrlKey($urlkey);
            $product->setSku($sku);
            $product->setAttributeSetId(4);
            $product->setStatus(1);
            $product->setWebsiteIds(array(1));
            $product->setWeight($weight);
            $product->setVisibility(1);
            $product->setTaxClassId(0);
            $product->setCategoryIds([$postdata['categoryid']]);
            $product->setTypeId('simple');
            $product->setPrice($price);

            //Set attribute value for simple product
            foreach ($attributes as $k=>$attribute):
                $attr = $attribute[$variant['option'.$k]];
                $attrlabel = 'set'.ucfirst(strtolower($attr['attrcode']));
                $product->$attrlabel($attr['optionid']);
                $attrgroup[] = $attr['attrid'];
            endforeach;

            $product->setStockData(
                array(
                    'use_config_manage_stock' => 1,
                    'manage_stock' => 1,
                    'is_in_stock' => 1,
                    'qty' => $inventory
                )
            );

            //Set Images
            foreach ($postdata['images'] as $simage):

                $imagePath = $simage;
                if (@getimagesize($imagePath)) {
                    $this->setImage($product, $imagePath, false, $imageType = ['image', 'small_image', 'thumbnail']);
                }

            endforeach;

            $product->save();

            $associatedProductIds[] = $product->getId();
            //pre(json_decode(json_encode($product->getData())));
        endforeach;

        $postproductnm = $postdata['name'];
        $postproductdesc = $postdata['description'];

        $attrgroup = array_unique($attrgroup);

        $mainurl = str_replace(' ', '-', strtolower($postdata['name']));

        //Create master simple product for make it configurable
        $product = $objectManager->create('\Magento\Catalog\Model\Product');
        if(isset($postdata['magentoid']) && $postdata['magentoid'] > 0){
            $product->load($postdata['magentoid']);
        }
        $product->setName($postproductnm); // Name of Product
        $product->setDescription($postproductdesc); // Description of Product
        $product->setSku($mainurl); // Set your sku here
        $product->setUrlKey($postdata['handle']); // Set your URL here
        $product->setAttributeSetId(4); // Attribute set id
        $product->setStatus(1); // Status on product enabled/ disabled 1/0
        $product->setCategoryIds([$postdata['categoryid']]);
        $product->setWebsiteIds(array(1)); // Website Ids
        $product->setWeight($variant['weight']); // weight of product
        $product->setVisibility(4); // visibilty of product (catalog / search / catalog, search / Not visible individually)
        $product->setTaxClassId(0); // Tax class id
        $product->setTypeId('simple'); // type of product (simple/virtual/downloadable/configurable)

        $product->setStockData(
            array(
                'use_config_manage_stock' => 1,
                'manage_stock' => 1,
                'is_in_stock' => 1,
                'qty' => 1
            )
        );

        if(isset($postdata['image'])){

            $imagePath = $postdata['image'];
            if (@getimagesize($imagePath)) {
                $this->setImage($product, $imagePath, false, $imageType = ['image', 'small_image', 'thumbnail']);
            }    
        }

        $product->save();

        //Create configurable which is Master simple product with assign simple product to Configurable product
        $productId = $product->getId(); // Master simple Product Id
        $product = $objectManager->create('Magento\Catalog\Model\Product')->load($productId); // Load Master simple Product
        $attributeModel = $objectManager->create('Magento\ConfigurableProduct\Model\Product\Type\Configurable\Attribute');
        $position = 0;
        $attributes = $attrgroup; // Super Attribute Ids Used To Create Configurable Product
        foreach ($attributes as $attributeId) {
            $data = array('attribute_id' => $attributeId, 'product_id' => $productId, 'position' => $position);
            $position++;
            $attributeModel->setData($data)->save();
        }
        $product->setTypeId("configurable"); // Setting Product Type As Configurable
        $product->setAffectConfigurableProductAttributes(4);
        $objectManager->create('Magento\ConfigurableProduct\Model\Product\Type\Configurable')->setUsedProductAttributeIds($attributes, $product);
        $product->setNewVariationsAttributeSetId(4); // Setting Attribute Set Id
        $product->setAssociatedProductIds($associatedProductIds);// Setting Associated Products
        $product->setCanSaveConfigurableAttributes(true);
        $product->save();
        pre(json_encode($product->getData()));exit;
    }

    protected function getMediaDirTmpDir()
    {
        return $this->directoryList->getPath($this->directoryList::MEDIA) . DIRECTORY_SEPARATOR . 'tmp' . DIRECTORY_SEPARATOR;
    }

    protected function setImage($product, $imageUrl, $visible = false, $imageType = [])
    {
        /** @var string $tmpDir */
        $tmpDir = $this->getMediaDirTmpDir();
        /** create folder if it is not exists */
        $this->file->checkAndCreateFolder($tmpDir);
        /** @var string $newFileName */
        $newFileName = $tmpDir . baseName($imageUrl);

        /** read file from URL and copy it to the new destination */
        $result = $this->file->read($imageUrl, $newFileName);
        if ($result) {
            /** add saved file to the $product gallery */
            $product->addImageToMediaGallery($newFileName, $imageType, true, $visible);
        }
        return $result;
    }

}

Step 2 : Create Helper for attribute



<?php

/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Jaydip\Kansagra\Helper;

use Magento\Framework\Registry;

class Productattr extends \Magento\Framework\App\Helper\AbstractHelper {

    /**
     * @var \Magento\Catalog\Api\ProductAttributeRepositoryInterface
     */
    protected $attributeRepository;

    /**
     * @var array
     */
    protected $attributeValues;

    /**
     * @var \Magento\Eav\Model\Entity\Attribute\Source\TableFactory
     */
    protected $tableFactory;

    /**
     * @var \Magento\Eav\Api\AttributeOptionManagementInterface
     */
    protected $attributeOptionManagement;

    /**
     * @var \Magento\Eav\Api\Data\AttributeOptionLabelInterfaceFactory
     */
    protected $optionLabelFactory;

    /**
     * @var \Magento\Eav\Api\Data\AttributeOptionInterfaceFactory
     */
    protected $optionFactory;

    protected $eavAttributeFactory;

    protected $attributeManagement;

    protected $config;

    /**
     * Data constructor.
     *
     * @param \Magento\Framework\App\Helper\Context $context
     * @param \Magento\Catalog\Api\ProductAttributeRepositoryInterface $attributeRepository
     * @param \Magento\Eav\Model\Entity\Attribute\Source\TableFactory $tableFactory
     * @param \Magento\Eav\Api\AttributeOptionManagementInterface $attributeOptionManagement
     * @param \Magento\Eav\Api\Data\AttributeOptionLabelInterfaceFactory $optionLabelFactory
     * @param \Magento\Eav\Api\Data\AttributeOptionInterfaceFactory $optionFactory
     */
    public function __construct(
        \Magento\Framework\App\Helper\Context $context,
        \Magento\Catalog\Model\Config $config,
        \Magento\Catalog\Api\ProductAttributeRepositoryInterface $attributeRepository,
        \Magento\Eav\Model\Entity\Attribute\Source\TableFactory $tableFactory,
        \Magento\Eav\Api\AttributeOptionManagementInterface $attributeOptionManagement,
        \Magento\Eav\Api\Data\AttributeOptionLabelInterfaceFactory $optionLabelFactory,
        \Magento\Eav\Api\Data\AttributeOptionInterfaceFactory $optionFactory,
        \Magento\Eav\Api\AttributeManagementInterface $attributeManagement,
        \Magento\Catalog\Model\ResourceModel\Eav\AttributeFactory $eavAttributeFactory
    ) {
        parent::__construct($context);

        $this->attributeRepository = $attributeRepository;
        $this->tableFactory = $tableFactory;
        $this->attributeOptionManagement = $attributeOptionManagement;
        $this->optionLabelFactory = $optionLabelFactory;
        $this->optionFactory = $optionFactory;
        $this->eavAttributeFactory = $eavAttributeFactory;
        $this->config = $config;
        $this->attributeManagement = $attributeManagement;
    }

    /**
     * Get attribute by code.
     *
     * @param string $attributeCode
     * @return \Magento\Catalog\Api\Data\ProductAttributeInterface
     */
    public function getAttribute($attributeCode) {
        try {
            return $this->attributeRepository->get($attributeCode);
        } catch (\Exception $e) {
            return false;
        }
    }

    public function addAttribute($attributecode, $attributelabel, $attroptions = array()) {

        $attributeData = array(
            'attribute_code' => $attributecode,
            'is_global' => 1,
            'frontend_label' => $attributelabel,
            'frontend_input' => 'select',
            'default_value_text' => '',
            'default_value_yesno' => 0,
            'default_value_date' => '',
            'default_value_textarea' => '',
            'is_unique' => 0,
            'apply_to' => 0,
            'is_required' => 0,
            'is_configurable' => 1,
            'is_searchable' => 1,
            'is_comparable' => 1,
            'is_visible_in_advanced_search' => 0,
            'is_used_for_price_rules' => 0,
            'is_wysiwyg_enabled' => 0,
            'is_html_allowed_on_front' => 0,
            'is_visible_on_front' => 0,
            'used_in_product_listing' => 0,
            'used_for_sort_by' => 0,
            'is_filterable' => 1,
            'is_filterable_in_search' => 0,
            'backend_type' => 'int',
            'is_user_defined' => 1,
            'group' => 'General',
            'option' => array()
        );
        foreach ($attroptions as $key=>$attroption):
            $attributeData['option']['value']['option_'.$key] = array($attroption, $attroption);
        endforeach;

        try {
            $model = $this->eavAttributeFactory->create();
            $model->addData($attributeData);
            $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
            $entityTypeID = $objectManager->create('Magento\Eav\Model\Entity')
            ->setType('catalog_product')
            ->getTypeId();
            $model->setEntityTypeId($entityTypeID);
            $model->save();

            $group_id = $this->config->getAttributeGroupId(4, 'Attribute Option');
            $this->attributeManagement->assign('catalog_product', 4, $group_id, $model->getAttributeCode(), null);

            return $model;
        } catch (\Exception $e) {
            return $e->getMessage();
        }       
    }

    /**
     * Find or create a matching attribute option
     *
     * @param string $attributeCode Attribute the option should exist in
     * @param string $label Label to find or add
     * @return int
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    public function createOrGetId($attributeCode, $label = '') {

        if (strlen($label) < 1) {
            throw new \Magento\Framework\Exception\LocalizedException(
            __('Label for %1 must not be empty.', $attributeCode)
            );
        }

        $label = ucfirst(strtolower($label));
        $attributeLabel = ucfirst(strtolower($attributeCode));
        $attributeCode = preg_replace("/[^a-zA-Z0-9]+/", "", strtolower($attributeCode));

        $attribute = $this->getAttribute($attributeCode);

        if(!$attribute){
            //Create new attribute
            $attribute = $this->addAttribute($attributeCode, $attributeLabel, array($label));
        }

        // Does it already exist?
        $optionId = $this->getOptionId($attributeCode, $label);
        if (!$optionId) {
            // If no, add it.

            /** @var \Magento\Eav\Model\Entity\Attribute\OptionLabel $optionLabel */
            $optionLabel = $this->optionLabelFactory->create();
            $optionLabel->setStoreId(0);
            $optionLabel->setLabel($label);

            $option = $this->optionFactory->create();
            $option->setLabel($optionLabel);
            $option->setStoreLabels([$optionLabel]);
            $option->setSortOrder(0);
            $option->setIsDefault(false);

            $this->attributeOptionManagement->add(
                    \Magento\Catalog\Model\Product::ENTITY, $this->getAttribute($attributeCode)->getAttributeId(), $option
            );

            // Get the inserted ID. Should be returned from the installer, but it isn't.
            $optionId = $this->getOptionId($attributeCode, $label, true);
        }

        return array('attrid'=>$attribute->getAttributeId(), 'attrcode'=>$attributeCode, 'optionid'=>$optionId);
    }

    /**
     * Find the ID of an option matching $label, if any.
     *
     * @param string $attributeCode Attribute code
     * @param string $label Label to find
     * @param bool $force If true, will fetch the options even if they're already cached.
     * @return int|false
     */
    public function getOptionId($attributeCode, $label, $force = false) {
        /** @var \Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute */
        $attribute = $this->getAttribute($attributeCode);

        // Build option array if necessary
        if ($force === true || !isset($this->attributeValues[$attribute->getAttributeId()])) {
            $this->attributeValues[$attribute->getAttributeId()] = [];

            // We have to generate a new sourceModel instance each time through to prevent it from
            // referencing its _options cache. No other way to get it to pick up newly-added values.

            /** @var \Magento\Eav\Model\Entity\Attribute\Source\Table $sourceModel */
            $sourceModel = $this->tableFactory->create();
            $sourceModel->setAttribute($attribute);

            foreach ($sourceModel->getAllOptions() as $option) {
                $this->attributeValues[$attribute->getAttributeId()][$option['label']] = $option['value'];
            }
        }

        // Return option ID if exists
        if (isset($this->attributeValues[$attribute->getAttributeId()][$label])) {
            return $this->attributeValues[$attribute->getAttributeId()][$label];
        }

        // Return false if does not exist
        return false;
    }

}