Splitting Excel Files using Bat Script- easy way

Sometimes we will face situations, where we have to split our excel sheets into multiple. Suppose for importing to some softwares, if we have a excel sheet with millions of data we have to split it to small adata files for our convinence to upload. In those situations, copy pasting lakhs of adata into seperate excel sheet is a tiredous task. Here I will give a script which you can run as a bat file to split the xcel sheets.

1) Save your excel sheet in csv format.

2) Make a file and rename it as script.bat. Open it in edit mode and paste the following code:-

@echo off

setlocal ENABLEDELAYEDEXPANSION

REM Edit this value to change the name of the file that needs splitting. Include the extension.

SET BFN=importfile.csv

REM Edit this value to change the number of lines per file.

SET LPF=50000

REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.

SET SFN=splitfile

REM Do not change beyond this line.

SET SFX=%BFN:~-3%

SET /A LineNum=0

SET /A FileNum=1

For /F "delims==" %%l in (%BFN%) Do (

SET /A LineNum+=1

echo %%l >> %SFN%!FileNum!.%SFX%

if !LineNum! EQU !LPF! (

SET /A LineNum=0

SET /A FileNum+=1

)

)

endlocal

Pause

3)In the above file set BFN variable the csv  file name  we want to split. and in SFN variable we have to put the split files prefix for our convinence .in LPF we have to mention the count of number of rows per excel sheet.Now save it and close it.

4) Put this script file in same folder of the csv file. Now open Command prompt and navigate to same file location and run the script.

Combining CSV Files

For combing CSV files.. Navigate to folder using cmd.Put the command
copy *.csv merge.csv

Resolving PHP Auto emails from Linux Server Hostings

When you create new websites , a frequent requirement will be sending automatic mail notifications from Enquiry Forms, sending mail activations, forget password links, product enquiries.etc. When you host website in linux hosting , there are restrictions on mail sending. This is mainly due to the increase in number of spam mails all over the world. it is being noted that spammers are sending phishing mails through many domains even without having any login details. So the server people accept only SMTP secure mail transmission for this. I will be using PHP mailer for sending mails.

1) Download PHPMailer script from Github:- 

https://github.com/PHPMailer/PHPMailer

2) Now  you have to create an email account if you already didn't have and note the password of the same.Make sure to Enable DKIM,SPF and PKR for servers not marking it as spam.

3) Now in the project copy the phpmailer folder into the root folder of project.now in the script you want to send mail,include this code:-

date_default_timezone_set('Etc/UTC');

require 'PHPMailerAutoload.php';

//Create a new PHPMailer instance 

$mail = new PHPMailer;

//Tell PHPMailer to use SMTP                                                                                     $mail->isSMTP();                                                                                                    //Enable SMTP debugging.This you can use to check the working. for testing put to 2 and check mails going or no.In production environment,put it as 0.

// 0 = off (for production use)
// 1 = client messages
// 2 = client and server messages
$mail->SMTPDebug = 0;
//Ask for HTML-friendly debug output
$mail->Debugoutput = 'html';
//Set the hostname of the mail server
$mail->Host = "mail.domain.com";
//Set the SMTP port number should be ssl settings smtp port 465
$mail->Port = 465;
//Whether to use SMTP authentication,dont forget to put this line.
$mail->SMTPAuth = true;
$mail->SMTPSecure = true;
//Username to use for SMTP authentication
$mail->Username = "info@domain.com";
//Password to use for SMTP authentication
$mail->Password = "mYP@$$w0rd";
//Set who the message is to be sent from
$mail->setFrom('user@domain.com', 'Name');
//Set an alternative reply-to address
$mail->addReplyTo('user@domain.com', 'Name');
//Set who the message is to be sent to
//Set the subject line
$mail->Subject = $subject;
//Read an HTML message body from an external file, convert referenced images to embedded,
//convert HTML into a basic plain-text alternative body
$mail->msgHTML($msg);
//Replace the plain text body with one created manually
$mail->addAddress($email_to, 'User');
//send the message, check for errors
if (!$mail->send()) {
    echo "Mailer Error: " . $mail->ErrorInfo;
} else {
    echo "Message sent!";
}

Exporting Large Database From Linux Server Hosting

For exporting database more than certain size, PHPMYADMIN will give you timeout errors. For effective tackling the same, we need to do it via SSH. Here am going to show you, how to do it in linux server.

1) Enable Shell access in server

In your cpanel, dashboard you can see "manage Shell" option. Just click on that.

After that, we have to enable SSH access. Then we will get ssh port and details through this. If you want to use external terminal access softwares like putty, you can use those connection details. But here am explaining about how we can do in the server itself without using any external softwares.

2) Access Terminal of server

Click on Terminal menu in Advanced tab of cpanel. You will get a terminal to enter commands.

3) Executing commands in Terminal

Now from step 3, open the terminal. we have to put one command here..

mysqldump -u dbuser_user -p dbname > /home/folderuser/public_html/dbbackup/db.sql

(dbuser: Put the database username, dbname:Put the db name, db.sql: Give the database file name, path should be given relatively in repect to where you want the exported sql file)

Then it will ask for db user password just provide that. Now the command will execute and database will be exported successfully.

Importing Large Mysql Database in Linux Hosting Servers

When we are migrating very large websites, problem often comes when we are migrating the database. Through phpmyadmin, we can import database upto 50 mb. But the sql of size more than 50mb we have to import via SSH. Here I will explain how to do it in a linux server hosting.

1) Enable Shell access in server

In your cpanel, dashboard you can see "manage Shell" option. Just click on that.

After that, we have to enable SSH access. Then we will get ssh port and details through this. If you want to use external terminal access softwares like putty, you can use those connection details. But here am explaining about how we can do in the server itself without using any external softwares.


2) Access Terminal of server

Click on Terminal menu in Advanced tab of cpanel. You will get a terminal to enter commands.

3)Create Database and User

Now we have to make some basic things ready. So we need to create a database and user. Make sure to give all privileges to that particular user. Note down the db username, password,db name.etc.

3) Upload the Sql file to server

Now we have to upload the sql file to the server for importing.This you can use FTP and through server. Make sure in the sql script, db name should match the database name created in server. if its different open the sql file and edit it with the same.

5) Executing commands in Terminal

Now from step 3, open the terminal. we have to put one command here..

mysql -u dbuser -p dbname < /home/folderuser/db.sql
(dbuser: Put the database username, dbname:Put the db name, db.sql: Give the database file name, path should be given relatively in repect to where you uploaded the sql file)

Then it will ask for db user password just provide that. Now the command will execute and database will be imported successfully.

Resolving Permission/ Undefined Variable Problem in SugarCRM/SuiteCRM in Linux Server

When we install or migrate Sugarcrm/Suitecrm,normally we have to face some issues like pages going blank or the messages and popup showing as undefined. This error occurs mostly when we migrate CRM to some Linux servers. This is mainly caused due to permission errors and missing extensions. I will give you step to step instruction on how to resolve this problem. we can divide the steps into three parts.

1) Checking all required extensions are installed or not?
2) Checking all files or folders  having required permissions?
3) Checking the correct path set in .htaccess file

so here am going to explain step by step..

1) Checking all required extensions are installed or not?

We have to check whether following extensions are installed or not.

• PHP - (check the version.. if you are using older version of sugar or suitecrm ebnable lower php versions. If you are using latest suitecrm version, dont forget to upgrade php verision.)
• JSON -( Check JSON support is enabled.)
• XML Parsing- (XML parsing supports should be enabled)
• MB Strings Module -(This is a php extension needed for sugarcrm to function.make sure to install this extension corresponding to php version in the server)
• ZLIB Compression Module - (This module is needed for package installation function in crm.make sure to enable it)
• ZIP Handling Module-  (This module is needed for package installation function in crm.make sure to enable it)
• PCRE Library-(This is a Perl module needed for advanced regex functions)
• IMAP Module- (This is for Mail functioning inside CRM for reminders.etc)
• cURL Module
• Sprite Support

After install of new modukles, just restart the apache services

2) Checking all files or folders  having required permissions?

We have to check permissions for some directories inside root folder.

Main directories to give 775 permission are:-cache, custom ,modules ,themes ,data, upload.

Also we have to change permission to config_override.php file also to 775

While giving permission to directory, make sure to give permission recursively for child objects under that folder also.

Login to terminal via root user then perform following commands:-

cd  /home/siteuser/public_html/    (siteuser will be username in the hosting)

sudo chmod -R 775 cache custom modules themes data upload config_override.php

Now check the permission has been granted or not

3) Checking the correct path set in .htaccess file

Just open .htaccess file in the root folder. check this line:-

<IfModule mod_rewrite.c>

    Options +FollowSymLinks

    RewriteEngine On

    RewriteBase /

Verify RewriteBase /  is correctly set or not. If you have installed in public_html folder itself you have to use like this RewriteBase /. In case, if you installed inside a folder for eg: crm, this line have to change like:- RewriteBase /crm/

After editing , save the file and close it

Now login to CRM and check. Its betetr to do a Repair and rebuild from the admin to make sure everything reloaded correctly..

Integrating Google Signin For Websites

As I said in My Previous Post Nowadays in almost all websites, You can find login via Social media accounts. So its inevitable for all the developers to learn more about these processes & integrate into their own websites. Their are many benefits of integrating this.

1) You can reduce the time consumption for the long form filling process during the registration
2) You doesn't want to wait for the customers to verify their email address or anything.
3) You can retrieve much more information about the personal info from their social media accounts.

Integrating Google Sigin is more easier than the facebook Signup.
Here You Just need to Create a Project in Google account by visiting:-

https://console.developers.google.com/apis/

Their You have to Create a Project like this:-

Here While Creating Project you will get an ID related to the App.
So Just save it somewhere, so that you have to use it in the code..

Now Create 2 files in Your project root folder.. one is google.php and then myprofile.php. You can name whatever you want. I named it as per my convenience.

1) In google.php.. Place the code:-

<html lang="en">
  <head>
    <meta name="google-signin-scope" content="profile email">
    <meta name="google-signin-client_id" content="CLIENT_ID">
    <script src="https://apis.google.com/js/platform.js" async defer></script>
  </head>
  <body>
    <div class="g-signin2" data-onsuccess="onSignIn" data-theme="dark"></div>
    <script>
      function onSignIn(googleUser) {
        // Useful data for your client-side scripts:
        var profile = googleUser.getBasicProfile();
        console.log("ID: " + profile.getId()); // Don't send this directly to your server!
        console.log('Full Name: ' + profile.getName());
        console.log('Given Name: ' + profile.getGivenName());
        console.log('Family Name: ' + profile.getFamilyName());
        console.log("Image URL: " + profile.getImageUrl());
        console.log("Email: " + profile.getEmail());

        // The ID token you need to pass to your backend:
        var id_token = googleUser.getAuthResponse().id_token;
        console.log("ID Token: " + id_token);

        var id = profile.getId();
        var name = profile.getName();
        var email = profile.getEmail();
        var token= id_token;

        window.location.href = "googleaccess.php?id="+id+"&nm="+name+"&ml="+email+"&tk="+token;

      };
    </script>

Above script you noticed this line:-   <meta name="google-signin-client_id" content="CLIENT_ID">

Here update the CLIENT_ID variable with the value Iam going to explain to you.

Visit: https://developers.google.com/identity/sign-in/web/sign-in#before_you_begin

Here go to this portion Down:-

Click on the Red Dotted highlighted area.. Then a dialogue box appears:-

HERE the project and credentials will come.. But for better copying you click on the View Link on the Bottom which I highlighted in green. Then you will redirect to a page :-

Here You can see Client Id & Client Secret Values. Just copy the Client Id from here & paste it in the CLIENT_ID Place

<meta name="google-signin-client_id" content="CLIENT_ID">

2) Now From this page value will be passed to googleaccess.php..
Create a Page called googleaccess.php & copy paste the code their:-

<?php 
session_start();

?>

<head>

     <title>Login with Google</title>

     <link

        href = "https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"

        rel = "stylesheet">

  </head>

  <body>     

  <?php if($_GET['id']) {?>

        <div class = "container">

           <div class = "jumbotron">

              <h1>Hello <?php echo $_GET['nm']; ?></h1>

              <p>Welcome to UAE Travel Agents</p>

           </div>

              <ul class = "nav nav-list">

                 

                 <h4>ID</h4>

                 <li><?php echo  $_GET['id']; ?></li>

                 <h4>Fullname</h4>

                 <li><?php echo $_GET['nm']; ?></li>

                 <h4>Email</h4>

                 <li><?php echo $_GET['ml']; ?></li>

                 <h4>Token</h4>

                 <li><?php echo $_GET['tk']; ?></li>
              </ul>

          </div>

<?php } ?>

Now You will get all the details in this page...

Integrating Facebook Login in Your Websites

Nowadays in almost all websites, You can find login via Social media accounts. So its inevitable for all the developers to learn more about these processes & integrate into their own websites. Their are many benefits of integrating this.

1) You can reduce the time consumption for the long form filling process during the registration
2) You doesn't want to wait for the customers to verify their email address or anything.
3) You can retrieve much more information about the personal info from their social media accounts.

Steps For Integration 

1) Creating Developer Account & Creating App

• For Integrating this, first the developer has to create a developer account in facebook by going to https://developers.facebook.com/.
• Now Create an App in that

• Select Website as platform.

• Now Give the Category, Email, App Type, Request URLS Approved.etc..

• Keep the App ID & APP Secret safely in your hands. Dont Forget to make the app Live.

• Also You can add Products in the app through this panel.

2) Downloading Facebook SDK

Download Facebook SDK from the web.
You can either install via composer or Can download from Github..  Download from here:- https://github.com/facebook/php-graph-sdk


3)Creating Our Login Script

Create two files in the application folder; index.php and myprofile.php.

Add the following code to index.php file. Comments are added to explain the code.

<?php
//initialize facebook sdk
require 'vendor/autoload.php'; //Link to the Folder Downloaded for afcebook graph sdk
session_start();
$fb = new Facebook\Facebook([
 'app_id' => 'Put Your App ID Here',
 'app_secret' => 'Put App Secret',
 'default_graph_version' => 'v2.5',
]);
$helper = $fb->getRedirectLoginHelper();
$permissions = ['email']; // optional
try {
if (isset($_SESSION['facebook_access_token'])) {
$accessToken = $_SESSION['facebook_access_token'];
} else {
  $accessToken = $helper->getAccessToken();
}
} catch(Facebook\Exceptions\facebookResponseException $e) {
// When Graph returns an error
echo 'Graph returned an error: ' . $e->getMessage();
  exit;
} catch(Facebook\Exceptions\FacebookSDKException $e) {
// When validation fails or other local issues
echo 'Facebook SDK returned an error: ' . $e->getMessage();
  exit;
}
if (isset($accessToken)) {
if (isset($_SESSION['facebook_access_token'])) {
$fb->setDefaultAccessToken($_SESSION['facebook_access_token']);
} else {
// getting short-lived access token
$_SESSION['facebook_access_token'] = (string) $accessToken;
  // OAuth 2.0 client handler
$oAuth2Client = $fb->getOAuth2Client();
// Exchanges a short-lived access token for a long-lived one
$longLivedAccessToken = $oAuth2Client->getLongLivedAccessToken($_SESSION['facebook_access_token']);
$_SESSION['facebook_access_token'] = (string) $longLivedAccessToken;
// setting default access token to be used in script
$fb->setDefaultAccessToken($_SESSION['facebook_access_token']);
}
// redirect the user to the profile page if it has "code" GET variable
if (isset($_GET['code'])) {
header('Location: profile.php');
}
// getting basic info about user
try {
$profile_request = $fb->get('/me?fields=name,first_name,last_name,email');
$requestPicture = $fb->get('/me/picture?redirect=false&height=200'); //getting user picture
$picture = $requestPicture->getGraphUser();
$profile = $profile_request->getGraphUser();
$fbid = $profile->getProperty('id');           // To Get Facebook ID
$fbfullname = $profile->getProperty('name');   // To Get Facebook full name
$fbemail = $profile->getProperty('email');    //  To Get Facebook email
$fbpic = "<img src='".$picture['url']."' class='img-rounded'/>";
# save the user nformation in session variable
$_SESSION['fb_id'] = $fbid.'</br>';
$_SESSION['fb_name'] = $fbfullname.'</br>';
$_SESSION['fb_email'] = $fbemail.'</br>';
$_SESSION['fb_pic'] = $fbpic.'</br>';
} catch(Facebook\Exceptions\FacebookResponseException $e) {
// When Graph returns an error
echo 'Graph returned an error: ' . $e->getMessage();
session_destroy();
// redirecting user back to app login page
header("Location: ./");
exit;
} catch(Facebook\Exceptions\FacebookSDKException $e) {
// When validation fails or other local issues
echo 'Facebook SDK returned an error: ' . $e->getMessage();
exit;
}
} else {
// replace your website URL same as added in the developers.Facebook.com/apps e.g. if you used http instead of https and you used            
$loginUrl = $helper->getLoginUrl('http://sitename.com/url.php', $permissions);echo '<a href="' . $loginUrl . '">Log in with Facebook!</a>';
}
?>

Once done, go to the application URL in browser. You will see the Facebook button for login. Click it and the page will redirect to Facebook login page. Sign into the Facebook account. After successful login, the page will be redirected to the myprofile page.

Add the following code to myprofile.php to show the retrieved information. For the purpose of this tutorial, I will retrieve user id, Facebook profile picture, name and email.

<?php
session_start();
?>
<head>
     <title>Login with Facebook</title>
     <link
        href = "https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
        rel = "stylesheet">
  </head>
  <body>   

  <?php if($_SESSION['fb_id']) {?>
        <div class = "container">
           <div class = "jumbotron">
              <h1>Hello <?php echo $_SESSION['fb_name']; ?></h1>
              <p>Welcome to PHP Dudes</p>
           </div>
              <ul class = "nav nav-list">
                 <h4>Image</h4>
                 <li><?php echo $_SESSION['fb_pic']?></li>
                 <h4>Facebook ID</h4>
                 <li><?php echo  $_SESSION['fb_id']; ?></li>
                 <h4>Facebook fullname</h4>
                 <li><?php echo $_SESSION['fb_name']; ?></li>
                 <h4>Facebook Email</h4>
                 <li><?php echo $_SESSION['fb_email']; ?></li>
              </ul>
          </div>
<?php } ?>
  </body>
</html> 

• Now when the visitor, login via clicking the link, we will get info like this:-

• Store the ID & token received in your Database & validate every time when use logins.. 

Download sample code from here:-  https://github.com/litto/Facebook