Linux Command

Informasi sistem Perintah Deskripsi # arch lihat arsitektur dari mesin(1)   [man] # cal 2007 lihat tabel kalendar tahun 2007   [man] # cat /proc/cpuinfo menampilkan informasi CPU   [man] # cat /proc/interrupts menampilkan penggunaan interupsi   [man] # cat /proc/meminfo lihat penggunaan memori komputer   [man] # cat /proc/swaps menampilkan partisi swap yang dipakai linux sebagai memori virtual   [man] # cat /proc/version menampilkan versi dari kernel.   [man] # cat /proc/net/dev menampilkan informasi kartu nic dan statistik penggunaan nic   [man] # cat /proc/mounts menampilkan filesystem yang termuat   [man] # clock -w simpan perubahan tanggal di BIOS.   [man] # date tampilkan tanggal sistem   [man] # date 041217002007.00 tetapkan tanggal dan waktu - BulanHariJamMenitTahun.Detik   [man] # dmidecode -q tampilkan komponen perangkat keras dari sistem # hdparm -i /dev/hda tampilkan karakteristik dari sebuah hard-disk   [man] # hdparm -tT /dev/sda uji coba tes baca pada sebuah hard-disk   [man] # lspci -tv tampilkan perangkat-perangkat PCI yang ada   [man] # lsusb -tv tampilkan perangkat-perangkat USB   [man] # uname -m tampilkan arsitektur mesin(2)   [man] # uname -r tampilkan versi dari kernel yang digunakan   [man]

How get UUID from Hardisk on linux

#blkid /dev/sda1

/dev/sda1: LABEL="/" UUID="ee7cf0a0-1922-401b-a1ae-6ec9261484c0" SEC_TYPE="ext2" TYPE="ext3"

How To Enable NTFS Support in CentOS 6

1) Preparation

Enable EPEL repository using following command.

rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm


2) Install NTFS Drivers.

yum -y install ntfs-3g

Setup LDAP server (389 Directory Server) on Centos 6.5

389 Directory Server
The 389 directory server is a LDAP (Lightweight directory access protocol) server developed by Red Hat. The name 389 is derived from the LDAP port number. Though 389 server is being built on top of fedora, it supports many operating system such as CentOS, Scientific Linux, Debian and solaris etc.





Features:

- Multi-Master Replication, to provide fault tolerance and high write performance
- Scalability: thousands of operations per second, tens of thousands of concurrent users, tens of millions of entries, hundreds of gigabytes of data
- The codebase has been developed and deployed continuously by the same team for more than a decade
- Extensive documentation, including helpful Installation and Deployment guides
- Active Directory user and group synchronization
- Secure authentication and transport (SSLv3, TLSv1, and SASL)
- Support for LDAPv3
- On-line, zero downtime, LDAP-based update of schema, configuration, management and in-tree Access Control Information (ACIs)
- Graphical console for all facets of user, group, and server management 

Innstall and Configuration LDAP Server on Ubuntu 12.04

kclick disini 

Cara membuka IP Camera Trendnet TV-IP672P dari VLC Player

Untuk membuka streaming video ip camera trendnet
pertama instal aplikasi vlc player
anda bisa download apliaksinya dari situs  resminya :
http://www.videolan.org/

kemudian buka notepad
isikan sebagai berikut :

rtsp://192.126.19.229:554/play1.sdp

simpan dengan extention  .sdf contoh :   streaming.sdf
kemudian klikanan filenya streaming.sdf -> open with vlc player.

atau anda bisa langsung untuk membuka streaming langsung dari vlc.
caranya Open aplikasi Vlc -> Media -> open network streaming
kemudian masukan urlnya :

rtsp://192.168.19.229:554/play1.sdp


selamant mencoba

Connecting to your TrendNet IP Camera

Berikut ini link connection IP Camera TrendNet :

insall phpLDAPadmin on centos 6

 Install and Configure phpLDAPadmin

Add EPEL repository

rpm -ivh http://mirrors.ukfast.co.uk/sites/dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

 
Install phpLDAPadmin

yum install -y phpldapadmin

 
Allow access from your network

vim  /etc/httpd/conf.d/phpldapadmin.conf


Order Deny,Allow
Deny from all
Allow from 127.0.0.1 10.0.0.0/24
Allow from ::1
Allow from192.9.18.25



Disable automatic login mechanism
nano /etc/phpldapadmin/config.php


Comment out the following (line 398)

//$servers->setValue('login','attr','uid');

Setup HTTPD service

chkconfig httpd on 
service httpd start


 


Log in to phpLDAPadmin

http://localhost/ldapadmin

Setup DNS Server CentOS 6.3/6.4/6.5

Menentukan Nama Domain , IP 

Primary(Master) DNS Server Details:
Operating System     : CentOS 6.5 64 bit (Minimal Server)
Hostname                : masterdns.jst-indonesia.com
IP Address               : 192.9.18.25/24


install bind service

yum install bind* -y
1.Configure DNS Server

vim /etc/named.conf

// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

options {
        listen-on port 53 { 127.0.0.1; 192.9.18.25; };
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        allow-query     { localhost; 192.9.18.0/24; };
        allow-transfer  { localhost; 192.9.18.26; };        #for slavedns

        recursion yes;

        dnssec-enable yes;
        dnssec-validation yes;
        dnssec-lookaside auto;

        /* Path to ISC DLV key */
        bindkeys-file "/etc/named.iscdlv.key";

        managed-keys-directory "/var/named/dynamic";
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "." IN {
        type hint;
        file "named.ca";
};

zone "jst-indonesia.com" IN {
type master;
file "fwd.jst-indonesia.com";
allow-update { none; };
};

zone "1.168.192.in-addr.arpa" IN {
type master;
file "rev.jst-indonesia.com";
allow-update { none; };
};

include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";

Web Pannel From Centos Free

You Can Look here

http://centos-webpanel.com/

How To Set Up Database Replication In MySQL On Ubuntu 12.04

CONFIGURE THE MASTER DATABASE MYSQL :

The First you must login and create new user slave on Master Database Mysql
mysql -u root -p

Create user 'slave' with password='slave' on Master Server  
Mysql> create database test;

mysql> GRANT REPLICATION SLAVE ON *.* TO  'slave'@'192.9.18.25' IDENTIFIED BY slave';

Cara Install Owncloud pada Ubuntu 12.04

This document describes how to install and configure ownCloud  on Ubuntu 14.04. I will also connect to the ownCloud Server's data with another Ubuntu 14.04 Desktop and a Windows  machine. ownCloud provides access to your data through a web interface or WebDAV while providing a platform to easily view, sync and share across devices—all under your control. ownCloud’s open architecture is extensible via a simple but powerful API for applications and plugins and works with any storage.

Artikel ini menjelaskan bagaimana menginstall dan mengkonfigurasi own cloud  on ubuntu 12.04 .
Saya juga akan menyambungkan data server owncloud antara ubuntu 12.04 dengan desktop yang di inststall windows 7.
owncloud menyediakan akses ke data dengan menggunakan interface web atau webdev sehingga memudahkan platform lebih mudah untuk melihat data. 
sync dan share semua alat dibawah kontrol anda.
owncloud membuka arsitekture sebuah tambahan yang sederhana tetapi bisa di andalkan API untuk aplikasi dan plugin dan bekerja dengan banyak media penyimpanan.


1 Catatan utama
tutorial ini menggunakan Ubuntu 12.04 , jadi anda dapat menginstall terlebih dahulu sebelum melanjutkan tutorial ini .
system harus memiliki static IP address. dan saya menggunakan IP 192.18.9.27 atau anda juga bisa menggunakan server name server.example.com untuk memanggil namanya. kemudian untuk windowsnya anda bisa memberikan IP 192.9.18.37.

2 Proses install ownCloud 
OwnCloud adalah web application yang dapat menyimpan content dari lokasi utama,seperti Dropbox. 

Pertama kita download aplikasi terbaru software Owncloud :

cd /tmp
wget http://download.opensuse.org/repositories/isv:ownCloud:community/xUbuntu_14.04/Release.key
sudo apt-key add - < Release.key

install file .rpm on ubuntu

install alient application from revository :

sudo apt-get install alien dpkg-dev debhelper build-essential

convert package from  .rpm into .deb format :

sudo alien packagename.rpm


Install Application

sudo dpkg -i packagename.deb



good luck

Can't export more than 5000 rows to Excel?

Question :

I have a problem about export data to xls,

I can't export more then 5000 rows to excel?

Answer :

Well after doing my last testing i found the answer and was very easy.

I was using LINUX UBUNTU 64 bit and it uses OPEN OFFICE (LIBRE CALC) and this program is only showing 5000 rows.

The solution is:

Open the file using Microsoft Excel on Windows.

Thank you. 

How to Change Date Format on JQGrid

How to Change Date Format  on JQGrid

Before :

 {name:'LASTUPDATE',index:'LASTUPDATE', width:90},

After :

 {name:'LASTUPDATE',index:'LASTUPDATE', width:90, formatter : 'date', formatoptions: {newformat: 'd/m/Y'}},

Delete from data select in SQL Server

Delete from data select

example :

delete   tm_access_det from tm_access_det
inner join tm_access_hdt a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee  on em_emplcode=AJD_Emplcode
where  AJD_Emplcode=20285
and SEC_DEPARTMENT='QC'

update from data select in sql server

update From data select

example :

update tm_access_det  set  AJD_Emplcode=20033
from  tm_access_det
inner join tm_access_hdr a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee  on em_emplcode=AJD_Emplcode
where  AJD_Emplcode=10043

How to Backup database mysql

Backup database mysql

open terminal :

mysqldump -u root -p root_password database_name > database_name.sql

How to change or reset MySql root password on Linux

How to Set or  change or reset the MySQL root password on Ubuntu Linux.

Enter the following lines in your  terminal.

Stop the MySQL Server.
sudo /etc/init.d/mysql stop

Start the mysqld configuration.
sudo mysqld --skip-grant-tables &

Login to MySQL as root.
mysql -u root mysql

Replace YOURNEWPASSWORD with your new password!

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; 
exit

Keunggulan Telegram dari WhatsApp

Aplikasi Telegram ini bisa berjalan pada Android ,iOS, Windows Phone, juga web version, OS X , OS Linux dan Windows desktop.
adapun beberapa keunggulan telegram dari whatsapp :

1. Telegram Lebih Cepat daripada WhatsApp

Tidak seperti WhatsApp, Telegram berbasis cloud (layanan komputasi awan) dan sangat terenkripsi. Hasilnya, Telegram menjadi lebih cepat daripada WhatsApp.

2. Telegram Lebih Aman daripada WhatsApp
Masih terkait cloud, basis data di sana membuat Telegram lebih aman.

3. Telegram Mudah Diakses dari Berbagai Perangkat
WhatsApp sebenarnya juga bisa diakses dari laptop. Namun, lagi-lagi karena faktor basis cloud, Telegram membuat Anda bisa mengakses pesan dari berbagai perangkat (termasuk PC) dan berbagi foto, video dan dokumen tanpa batas.

4. Telegram Bisa Berbagai File Hingga Ukuran GigaByte
Seperti dilansir Telegram dalam deskripsi di Google Play Store, aplikasi ini memungkinkan penggunanya berbagi file dan video hingga ukuran gigabyte.

5. Telegram Bebas Iklan
Meskipun WhatsApp dulunya juga bebas iklan, akuisisi mahal yang dilakukan Facebook atas aplikasi ini menimbulkan kekhawatiran bahwa WhatsApp akan dijejali iklan sebagaimana Facebook baik versi desktop maupun mobile. Sedangkan Telegram, developernya menjamin “100% free and no ads.”

6. Jumlah Anggota Grup Telegram Lebih Besar daripada WhatsApp
Mungkin Anda pernah merasakan betapa terbatasnya Grup WhatsApp yang membatasi anggotanya hanya 50 orang. Padahal, teman kantor atau teman alumni sekolah Anda lebih dari seratus orang. Sementara anggota grup WhatsApp terbatas 50 orang, anggota grup Telegram maksimal 200 orang. Empat kali lipan daripada WhatsApp. Ini salah satu alasan utama mengapa orang pindah ke Telegram.

install Telegram on Ubuntu

1. download application :

wget https://tdesktop.com/linux32 on a 32-bit system
or
wget https://tdesktop.com/linux on a 64-bit system

2. extact file

tar xvf linux*

3. Runing Application :

./Telegram/Telegram

Send Emali with SSMTP

install ssmtp :

sudo apt-get install ssmtp

Edit file  ssmtp.conf
sudo vim /etc/ssmtp/ssmtp.conf

edit seperti dibawah ini :

# Config file for sSMTP sendmail
#
# The person who gets all mail for userids < 1000
# Make this empty to disable rewriting.
#root=postmaster
root=test@gmail.com

# The place where the mail goes. The actual machine name is required no
# MX records are consulted. Commonly mailhosts are named mail.domain.com
#mailhub=mail
mailhub=smtp.gmail.com:587
AuthUser=test@gmail.com
AuthPass=xxxxxxxx
UseTLS=YES
UseSTARTTLS=YES

# Where will the mail seem to come from?
#rewriteDomain=

# The full hostname
hostname=test@gmail.com

# Are users allowed to set their own From: address?
# YES - Allow the user to specify their own From: address
# NO - Use the system generated From: address
FromLineOverride=YES


on php file :


//create fuction for send email

   function sendmail ($cc,$to,$sb,$msg)
    {       

    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= "From: aem@aem.co.id \r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
    $headers .= 'cc:'.$cc. "\r\n";
    $headers .= 'Bcc:aem@aem.co.id'. "\r\n";
    ini_set("SMTP","smtp.aem.co.id");
    ini_set("sendmail_from",$mailfrom); 
    mail($to,$sb, $msg, $headers);
    //return $true;           
       
    }   


//send mail

$from: "xxx@xxx.com";
$mailto: "xxx@xxx.com";
$subject: "testing send email";
$message: "Test";

sendmail($from,$mailto,$subject,$message);

Install IE pada Mozilla Firefox

Download and install from Addons mozilla

https://addons.mozilla.org/en-US/firefox/addon/ie-tab-2-ff-36/

and then Restart Firefox.

add virtaul IP on Ubuntu

#sudo vim /etc/network/interfaces

auto lo
iface lo inet loopback

Add script virtual ip :

auto eth0
iface eth0 inet static
address 192.9.18.27
netmask 255.255.255.0
gateway 192.9.8.8
dns-nameservers 180.131.144.144

auto eth0:0
iface eth0:0 inet static
address 192.168.1.12
netmask 255.255.255.0
gateway 192.168.1.1
dns-nameservers 180.131.144.144

Chagne Port 80 on ubuntu

Edit file port.conf

 vim /etc/apache2/ports.conf

and then You have to change your virtualHost setting,

vim /etc/apache2/sites-enabled/000-default

Change from ;

 <VirtualHost *:80>

to

 <VirtualHost *:8050>

And make sure that your ports.conf looks like

 NameVirtualHost *:8050

Testing :

 listen 8085

Restart apache
service apache2 restart
or
/etc/init.d/apache2 restart

check the result on firefox:

http://localhost:8085/ijinpedia/

Bunyi Beep pada PC

Bunyi Beep pada PC dan Artinya
Komputer Anda suka mengeluarkan suara beep panjang atau pendek? Waspadai peringatan yang diberikannya! Mau tahu artinya? Itu berarti masalah, masalah, dan masalah.
Tidak usah panik, keluarnya suara tiiiit (beep) panjang tersebut sebenarnya adalah cara komputer untuk memberi tahu Anda bahwa ada yang tidak beres dengan CPU Anda. Isyarat beep komputer ternyata tidak hanya beep panjang saja, ada bermacam-macam. Setidaknya jika komputer Anda memakai AWARD BIOS, maka ada 9 macam beep yang memiliki maksud tertentu:
Bunyi "beep" pendek 1 kali, mengindikasikan bahwa komputer anda telah berhasil melakukan dan menghidupkan semua komponen yang dibutuhkan untuk proses boot-up komputer.
Bunyi "beep" pendek 2 kali, artinya ada masalah pada konfigurasi atau seting pada CMOS.

1. Bunyi "beep" panjang 1 kali dan pendek 1 kali, artinya ada masalah pada perangkat keras Motherboard atau Memory anda, coba cek ulang apakah kedua perangkat tersebut masih bisa berfungsi/terpasang dengan baik atau tidak.

2. Bunyi beep panjang 1 kali dan pendek 2 kali, artinya ada masalah pada monitor atau VGA Card.

3.Bunyi "beep" panjang 1 kali dan pendek 3 kali, artinya ada masalah pada Keyboard, coba cek keyboard anda, atau coba kencangkan sambungan kabel dari keyboard yang biasanya terpasang ke port serial, ps2, ataupun usb.

4.Bunyi "beep" panjang 1 kali dan pendek 9 kali, artinya ada masalah pada ROM BIOS.

5.Bunyi "beep" panjang terus-menerus, artinya ada masalah di DRAM.

6.Bunyi "beep" pendek terus-menerus, artinya ada masalah penerimaan tegangan (power).

Pada beberapa merk Motherboard akan mengeluarkan bunyi "beep" beberapa kali apabila temperatur processornya terlalu tinggi (panas).

Install apache , php and mysql on ubuntu 12.04

please login su:

sudo su

1. istall apache :
sudo apt-get install apache2

2. isntall php :
sudo apt-get install php5 libapache2-mod-php5

3.install mysql server and client :
sudo apt-get install mysql-server mysql-client

insert user and password

4 install library php-mysql

sudo apt-cache search php5

E: Could not get lock /var/lib/dpkg/lock - open (11 Resource temporarily unavailable)

Isnatall applikasi pada ubuntu dengan command
apt-get istall ....  error.

E: Could not get lock /var/lib/dpkg/lock - open (11 Resource temporarily unavailable)

E: Unable to lock the administration directory (/var/lib/dpkg/) is another process using it?

setelah mencari cari di mbah google ketemu cara mengatasinya:

1. Pastikan Synaptic Package manager tidak sedang digunakan, karena biasanya error itu muncul ketika anda melakukan install tapi synaptic package manager juga sedang anda gunakan.

kalo poin pertama masih ada error gunakan command dibawah ini:

2. Hapus file lock

sudo rm /var/lib/apt/lists/lock

sudo rm /var/cache/apt/archives/lock

sudo dpkg --configure -a

Create Dynamic Combobox Using PHP

Create Dynamic Combobox Using  PHP

<select name="combobox_wipyear" id="combobox_wipyear" >
       <?php
             $ctahun = date("Y");
             $rollback = $ctahun - 4;
               while ($ctahun >= $rollback)
                     {
                        $option .= "<option ";
                        $option .= "value=\"$ctahun\">$ctahun</option> \n";
                        $ctahun = $ctahun - 1;
                     }
                   echo $option;
               ?>
</select>

Membuat Format Angka pada JqGrid

Membuat Format Angka pada JqGrid

Contoh : kolom QTY akan kita jadikan Fomat angka tampilanya :

jQuery("#g_lapbarangperdoc").jqGrid({
    url:"main.php?action=lapbarangperdoc_data&q=lapbarangperdoc",
datatype: "json",
height: 400,
width:1500,  
    colNames:['JENISBCDOC', 'NOMORBC', 'TANGGALBC', 'DONO', 'RECEIPTDATE', 'SUPPLIERNAME',
                   'ITEMCODE', 'ITEMNAME', 'UOM', 'QTY'],
    colModel:[
                {name:'JENISBCDOC',index:'JENISBCDOC', sorttype:"int", width: 30, frozen : true,key : true},                              
                {name:'NOMORBC',index:'NOMORBC', width:70, frozen : true},
                {name:'TANGGALBC',index:'TANGGALBC', width:100, frozen : true},
                {name:'DONO',index:'DONO', width:50, align: 'center'},
                {name:'RECEIPTDATE',index:'RECEIPTDATE', width:50, width:70, align: 'right'},
                {name:'SUPPLIERNAME',index:'SUPPLIERNAME', width:70, align: 'right'},              
                {name:'ITEMCODE',index:'ITEMCODE', width:70, align: 'right'},
                {name:'ITEMNAME',index:'ITEMNAME', width:70, align: 'right'},              
                {name:'UOM',index:'UOM', width:70, align: 'right'},                              
                {name:'QTY',index:'QTY', width:70, align: 'right', formatter: 'number', formatoptions: {thousandsSeparator: ",",decimalSeparator:".",decimalPlaces: 2}}          
                 ],
        rowNum:15,
    rowList:[15,30,45],      
    pager: '#p_lapbarangperdoc',
    sortname: 'NOURUT',
        loadonce :true,
        viewrecords: true,
        rownumbers : false,
        ignoreCase: false,
        shrinkToFit :true,
        sortorder: "desc",      
        caption: "Report Penerimaan Barang",
        height: 'auto'      
});

Contoh Stored Procedures SQL Server

Berikut Ini Contoh Stored Procedures Sederhana .

USE [ACCOUNTINGDB]

GO

/****** Object:  StoredProcedure [dbo].[spvw_SummFreight_ShippingCharge]    Script Date: 05/28/2014 08:18:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

-- Create store procedure

-- By Kusdi Mei-2014

CREATE PROCEDURE [dbo].[spvw_SummFreight_ShippingCharge]

(

@month int,

@year int

)

 AS

--deklarasi variable 

declare 

@FRD_FRDCode int,

@FI_CurrencyCode int, 

@air_import numeric(18,2),

@air_export numeric(18,2), 

@sea_import numeric(18,2), 

@sea_export numeric(18,2)

--set @month=5

--set @year=2014

begin 

    create table #Temp_FreightInvoice( 

no_urut int,

forwadercode int,

CurrencyCode int,

air_import numeric(18,2) null,

air_export numeric(18,2) null,

sea_import numeric(18,2) null,

sea_export numeric(18,2) null)

--select * from #Temp_FreightInvoice

--insert from  select

insert into #Temp_FreightInvoice

(forwadercode,CurrencyCode)

select FRD_FRDCode,FI_CurrencyCode   

from  accountingdb..TBTI_FreightInvoice 

inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

    --buat deklarasi cursor

declare FreightInvoice cursor fast_forward for

select FRD_FRDCode,FI_CurrencyCode      

from  accountingdb..TBTI_FreightInvoice 

inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

Open FreightInvoice

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

while @@Fetch_status = 0

begin

--summ air - import

select  @air_import= sum(FI_Amount)  

from  accountingdb..TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year   

and FI_ShippingMode='Air - Import' and FI_ForwarderCode=@FRD_FRDCode 

and FI_CurrencyCode=@FI_CurrencyCode

  

--summ Air - Export

select @air_export= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Air - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select  @sea_import= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Sea - Import' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select @sea_export= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Sea - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode   

   

   --update temp tabel  

update #Temp_FreightInvoice

set 

air_import=@air_import,

air_export=@air_export,

sea_import=@sea_import,

sea_export=@sea_export

Where forwadercode =@FRD_FRDCode and CurrencyCode =@FI_CurrencyCode

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

end

Close FreightInvoice

Deallocate FreightInvoice 

end   

--select data temporary

select FRD_FRDName, CRC_CurrencyName, air_import, air_export ,sea_import , sea_export

from #Temp_FreightInvoice

inner join accountingdb..vw_mst_currency on CurrencyCode   = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on  forwadercode=FRD_FRDCode 

--drop temporary table 

Drop table #Temp_FreightInvoice