~ Tiez Notes ~

select aa.periode, aa.product_id, count(*) as JumlahLacta, sum(aa.0sd3bln) as 0sd3bln, sum(aa.3sd6bln) as 3sd6bln, sum(aa.6sd9bln) as 6sd9bln,
         sum(aa.9sd12bln) as 9sd12bln, sum(aa.TotOthers) as TotOthers, sum(aa.Success) as Success, sum(aa.NotSuccess) as NotSuccess,
         sum(aa.NotCalling) as NotCalling, sum(aa.NewData) as NewData,
         sum(aa.OldData) as OldData, sum(aa.Users) as Users, sum(aa.Prospects) as Prospects,
         sum(aa.DO) as TotalDO, sum(aa.DO0sd3bln) as DO0sd3bln, sum(aa.DO3sd6bln) as DO3sd6bln, sum(aa.DO6sd9bln) as DO6sd9bln, sum(aa.DO9sd12bln) as DO9sd12bln,
         sum(aa.DOlbh12) as OhtersDOLbh12,
         sum(aa.TotalFO) as TotalFO,
         sum(aa.FO0sd3bln) as FO0sd3bln, sum(aa.FO3sd6bln) as FO3sd6bln, sum(aa.FO6sd9bln) as FO6sd9bln, sum(aa.FO9sd12bln) as FO9sd12bln,
         sum(aa.FOlbh12) as OhtersFOLbh12,
         (sum(aa.Users) +  sum(aa.Prospects) + sum(aa.DO) + sum(aa.TotalFO)) as Total
from
(
select date_format(cu.submit_date,'%Y%m') as Periode, pl.product_id, pl.customer_id, cu.customer_name, cu.customer_address, cu.customer_phone, cu.customer_city,
         ca.phone_date, ca.calling_for_id, ca.calling_strike_id, ca.calling_target_id, (case when ca.calling_strike_id = '12' then 1 else 0 end) as DO,
         ca.calling_result_id, (case when ca.calling_result_id = '1' then 1 else 0 end) as Success, (case when ca.calling_result_id = '2' then 1 else 0 end) as NotSuccess,
         (case when ca.calling_result_id is null or '' then 1 else 0 end) as NotCalling,
         (case when date_format(cu.recruit_date,'%Y%m') = '201001' then 1 else 0 end) as NewData,
         (case when date_format(cu.recruit_date,'%Y%m') < '201001' then 1 else 0 end) as OldData,
         (case when ca.calling_strike_id = '10' then 1 else 0 end) as Users,
         (case when ca.calling_strike_id = '13' then 1 else 0 end) as Prospects,
         ch.child_name, ch.child_birthday, period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) as selisih_bulan,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as 0sd3bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as 3sd6bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as 6sd9bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as 9sd12bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 end) as TotOthers,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as DO0sd3bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as DO3sd6bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as DO6sd9bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as DO9sd12bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as DOlbh12,
         (case when ca.calling_strike_id = '14' then 1 else 0 end) as TotalFO,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as FO0sd3bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as FO3sd6bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as FO6sd9bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as FO9sd12bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as FOlbh12
from product_log pl, customer cu, calling ca, children ch
where pl.product_id = 'lacta' and date_format(cu.submit_date,'%Y%m') = '201001' and cu.followup_status = '1' and
          pl.customer_id = cu.customer_id and pl.customer_id = ca.customer_id and
          ca.calling_target_id = '1' and pl.customer_id = ch.customer_id and ch.child_birthday <> ' ' and
          ch.child_birthday > '0000-00-00' and ca.calling_strike_id > 0
order by pl.customer_id
) as aa
group by aa.periode, aa.product_id
order by aa.periode

SELECT datediff('tgl1', 'tgl2');

tgl1 dan tgl2 adalah tanggal-tanggal yang akan dicari selisihnya. Untuk format tanggal adalah th-bln-tgl.

Apabila tgl1 < tgl2, maka akan diperoleh selisih hari bernilai negatif. Sedangkan apabila tgl1 > tgl2, maka akan diperoleh selisih 
hari yang bernilai positif.

Contoh :

SELECT datediff('2008-01-11','2008-01-01');

Dari perintah di atas akan diperoleh hasil  10 (artinya 10 hari).

Untuk mencari selisih dari 2 waktu, sintaks SQL nya adalah :

SELECT timediff('time1', 'time2');

Format time1 dan time2 dapat berupa waktu dalam jam (jam:menit:detik) atau tanggal saja (th-bln-tgl) atau bahkan gabungan jam dan tanggal (th-bln-tgl jam:menit:detik).

Perintah SQL ini akan menghasilkan output dalam format jam (jam:menit:detik).

Contoh :

SELECT timediff('14:00', '15:01');

Perintah ini akan menghasilkan 1:01:00 (1 jam, 1 menit, 0 detik).

Sumber :
http://www.firazstarlight.info/tips-dan-trik-php/71-penggunaan-fungsi-datediff-pada-mysql.html

select a.product_id, a.customer_id, b.customer_name, b.customer_city, c.child_name, c.child_number, round(((datediff(current_date(), c.child_birthday)) / 30)) as UmurBln,
         (case when d.calling_result_id = '1' then 'Success'
                  when d.calling_result_id = '2' then 'Not Success' end) CallingResult,
         d.calling_strike_id
from product_log a, customer b, children c, calling d
where a.customer_id = b.customer_id and
          a.customer_id = c.customer_id and
          a.customer_id = d.customer_id and
          a.product_id = 'IF1' and
         round(((datediff(current_date(), c.child_birthday)) / 30)) between '0' and '2' and
         d.calling_result_id = '1' and d.calling_strike_id in ( '1', '10') and
         a.input_date <= '2010-09-17'
order by a.customer_id;

Pada MSSQL perintahnya adalah sbb :

SELECT * FROM TABLE_NAME WITH (nolock)

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Sumber :

http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql

PHP class ini sangat membantu jika kita ingin berinteraksi dengan Gammu atau Mygnokii2 1.06.00 ( GNU All Mobile Management Utilities).
menggunakan PHP 5.1.4. Pemakai dapat mengirimkan SMS, Download SMS dari Mobile Device (Handphone), Membaca SMS Inbox.
Status: Release
Lisensi: free - opensource
Sistem Operasi: All
Environment: -
Autor: Stieven R. Kalengkian
Bahasa: -
Situs: http://www.phpclasses.org/browse/package/3865.html
Download: http://php-id.org/site/Downloads-req-getit-lid-44.txt

File :
1. class.gammu_nix_win32_v2.php (Main Class)
2. example_gammu_text.php (Contoh Penggunaan)

Untuk instalasi
1. Unzip files ke www or public_html folder anda.
2. Lihat file contoh bagaimana cara penggunaannya

Konfigurasi Gammu dan 'HOW TO'nya
Silahkan merujuk ke Dokumentasi Gammu: http://www.gammu.org/

Download Gammu :
Linux : http://dl.cihar.com/gammu/releases/gammu-1.10.0.tar.gz
Windows : http://dl.cihar.com/gammu/releases/windows/Gammu-1.10.0-Windows.zip

Source Download Link:
http://php-id.org/site/Downloads-req-getit-lid-44.txt

Sumber :
http://opensource-indonesia.com/kioss.php/line149-phpgammuKirimSMSdenganPHPviagammu.txt

<?php
//$my_report = "c:\\RekapPHarian.rpt";
$my_report = 'C:\Program Files\xampp\htdocs\lat\report\LapArsip13.rpt';
$my_pdf = 'C:\Program Files\xampp\htdocs\lat\report\LapArsip13.pdf';
//$my_report = "C:\\report\LapArsip12.rpt";
//$my_pdf = "C:\\report\LapArsip12.pdf";
$ObjectFactory= New COM("CrystalReports11.ObjectFactory.1");
$crapp =$ObjectFactory->CreateObject("CrystalDesignRunTime.Application");
$creport = $crapp->OpenReport($my_report, 1);
//$creport->FormulaSyntax = 0;
$creport->RecordSelectionFormula= "{vimb.NmKel} = 'Belakang Balok'";
//$creport->sqlquery="select * from vimb";
$creport->ReadRecords(); //hangs here
//------ Pass formula fields --------
$creport->FormulaFields->Item(1)->Text = ("'My Report Title'");
//$creport->FormulaFields->Item(2)->Text = ("'My d Title'");
//$creport->ParameterFields(1)->AddCurrentValue ("FirstParameter");
//$creport->ParameterFields(2)->AddCurrentValue (2000);
$creport->ExportOptions->DiskFileName=$my_pdf;
$creport->ExportOptions->PDFExportAllPages=true;
$creport->ExportOptions->DestinationType=1; // Export to File
$creport->ExportOptions->FormatType=31; // Type: PDF
$creport->Export(false);
print "<embed src=\"report/LapArsip13.pdf\" width=\"100%\" height=\"100%\">";
?>

Sumber :
http://www.diskusiweb.com/viewthread.php?tid=37481

$cr=new com("CrystalRuntime.Application") or die("cannot load cr com");
$rn="F:\notes5\test.rpt";
$rap=$cr->OpenReport($rn);

$cr=new com("CrystalRuntime.Application.11") or die("cannot load cr com");
$rn="F:\notes5\test.rpt";
$rap=$cr->OpenReport($rn);

------------------------------

<?php
$crapp = new COM ("CrystalRuntime.Application") or die ("Error on
load");
$creport = $crapp->OpenReport("c:/test.rpt", 1);
$creport->ExportOptions->DiskFileName="c:/test.rtf";
$creport->ExportOptions->DestinationType=1; // Export to File
$creport->ExportOptions->FormatType=4; // Type: RTF
$creport->DiscardSavedData();
$creport->Export(false);
$creport = null;
$crapp = null;
print "...done";
?>

$crapp = new COM ("CrystalRuntime.Application")
or die ("Error on load");
echo "<p>Enumerating CrystalRuntime.Application object:</p>";
$crapp->Reset();
while ($e = $crapp->Next()) {
echo "<p>$e</p>";
}

$crapp->Release()

------------------------------

<OBJECT ID="CRViewer"
CLASSID="CLSID:C4847596-972C-11D0-9567-00A0C9273C2A"
WIDTH=100% HEIGHT=95%
CODEBASE="/viewer/activeXViewer/activexviewer.cab#Version=8,0,0,371">
<PARAM NAME="EnableDrillDown" VALUE=1>
<PARAM NAME="EnableExportButton" VALUE=1>
<PARAM NAME="DisplayGroupTree" VALUE=1>
<PARAM NAME="EnableGroupTree" VALUE=1>
<PARAM NAME="EnableAnimationControl" VALUE=1>
<PARAM NAME="EnablePrintButton" VALUE=1>
<PARAM NAME="EnableRefreshButton" VALUE=1>
<PARAM NAME="EnableSearchControl" VALUE=1>
<PARAM NAME="EnableZoomControl" VALUE=1>
<PARAM NAME="EnableSearchExpertButton" VALUE=0>
<PARAM NAME="EnableSelectExpertButton" VALUE=0>
</OBJECT>

Script PHP kali ini merupakan lanjutan dari rulisan sebelumnya yang membahas masalah pembuatan script Captcha dengan PHP. Pada tulisan sebelumnya kita sudah membahas script inti dari Captcha yang bertugas membuat gambar captcha-nya. Nah sekarang kita akan lihat bagaimana mengimplementasikannya pada sebuah form.
Pada dasarnya, kamu bisa aja menempelkan script ini di manapun. Namun dalam contoh kali ini, kita akan membuat sebuah form sederhana untuk mencoba script PHP captcha kita.
<?php
session_start();
if(isset($_POST['submit'])) {
    if(isset($_POST['captcha_code']) && isset($_SESSION['captcha_code'])) {
        if(md5($_POST['captcha_code']) == $_SESSION['captcha_code']) {
            echo 'Result: CAPTCHA code correct.<br />';
        }else{
            echo 'Result: CAPTCHA code incorrect.<br />';
        }
    }else{
        if(!isset($_POST['captcha_code'])) {
            echo 'Result: No security code was entered.<br />';
        }
        if(!isset($_SESSION['captcha_code'])) {
            echo 'Result: No CAPTCHA was viewed.<br />';
        }
    }
}
?>
<form method="POST">
<img src="captcha.php" />
<br />
Enter the above text EXACTALY as it appears. Note: It is case sensitive<br />
<input type="text" name="captcha_code" />
<br />
<input type="submit" name="submit" value="Submit" />
</form>

Sumber :
http://prothelon.com/mambo/membuat-captcha-dengan-php-bag-2.html

Membuat captcha dengan PHP tidak terlalu sulit. Beberapa waktu yang lalu salah seorang pengunjung bernama Wahyu inigin tahu cara membuat authentication code seperti saat add friend di Facebook. Pengen tahu caranya? Ya.. dengan captcha.

Captcha merupakan kependekan dari "Completely Automated Public Turing test to tell Computers and Humans Apart", atau gampangnya agar robot/script otomatis tidak akan bisa mengisi form. Contoh kegunaannya adalah agar pengisian form atau comment tidak bisa otomatis.
Mula-mula, kita akan membuat sebuah script PHP yang akan membuat output dan mengubahnya menjadi gambar Captcha, dan kita namai captcha.php. Isi scriptnya seperti ini:

<?php
session_start();
$width = 140;
$height = 70;
$im = imagecreate($width, $height);
$bg = imagecolorallocate($im, 0, 0, 0);
// generate random string
$len = 5;
$chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
$string = '';
for ($i = 0; $i < $len; $i++) {
    $pos = rand(0, strlen($chars)-1);
    $string .= $chars{$pos};
}
$_SESSION['captcha_code'] = md5($string);
// grid
$grid_color = imagecolorallocate($im, 175, 0, 0);
$number_to_loop = ceil($width / 20);
for($i = 0; $i < $number_to_loop; $i++) {
    $x = ($i + 1) * 20;
    imageline($im, $x, 0, $x, $height, $grid_color);
}
$number_to_loop = ceil($height / 10);
for($i = 0; $i < $number_to_loop; $i++) {
    $y = ($i + 1) * 10;
    imageline($im, 0, $y, $width, $y, $grid_color);
}
// random lines
$line_color = imagecolorallocate($im, 130, 0, 0);
for($i = 0; $i < 30; $i++) {
    $rand_x_1 = rand(0, $width - 1);
    $rand_x_2 = rand(0, $width - 1);
    $rand_y_1 = rand(0, $height - 1);
    $rand_y_2 = rand(0, $height - 1);
    imageline($im, $rand_x_1, $rand_y_1, $rand_x_2, $rand_y_2, $line_color);
}
// write the text
$text_color = imagecolorallocate($im, 255, 0, 0);
$rand_x = rand(0, $width - 50);
$rand_y = rand(0, $height - 15);
imagestring($im, 10, $rand_x, $rand_y, $string, $text_color);
header ("Content-type: image/png");
imagepng($im);
?>

Sumber :
http://prothelon.com/mambo/membuat-captcha-dengan-php-bag-1.html

Captcha itu harfiahnya “Completely Automated Public Turing test to tell Computers and Humans Apart” ( dari Wikipedia ). Dibuat untuk membedakan antara mesin ( bot ) dan manusia. Captcha dibuat untuk melakukan validasi input pada website2 yang mempersilahkan pengunjung memberikan inputan baik berupa komentar, shoutout, kotak pendaftaran, atau apapun dimana pengunjung menginputkan sesuatu.

Captcha = Gambar Samar2
Simpelnya, Captcha merupakan sebuah gambar (image) yang berisi Angka / Teks atau kombinasi Angka & teks yang disamar2kan bentuk & rupanya sehingga memerlukan ketelitian dari pengunjung / orang yang ingin melakukan inputan data disebuah website.

Menambahkan Captcha pada website kita

Cara yang pertama adalah dengan download library Captcha dari thecaptcha.com Kita tinggal menambahkan librarynya dan disisipkan dengan website kita.

Cara yang kedua adalah dengan membuat PHP Captcha kita sendiri. Kode ini saya dapat dari somewhere setelah googling2. Eh ga taunya baru tau kalo di rumahweb.com tutorial kayak gini udah pernah ditulis. Jadi klo mau lebih detail boleh liat ke situsnya.

Cara Kerjanya:

Nah, cara kerjanya adalah bermain2 dengan session,  pada saat form pendaftaran di Load, maka dibuatkan String Random dari MD5 yang diambil 5 digit pertamanya. Kemudian string itu disimpen di SESSION, Dari String itu dibuatkan Image dengan menggunakan GDLIbrary bawaan dari PHP dan ditampilkan di halaman Form tersebut. Pada saat User Submit String tersebut, dilakukan pengecekan dengan Session yang sudah tersimpan sebelumnya.

File: php_captcha.php

<?php

session_start();
$RandomStr = md5(microtime());// md5 to generate the random string
$ResultStr = substr($RandomStr,0,5);//trim 5 digit
$NewImage =imagecreatefromjpeg("img.jpg");//image create by existing image and as back ground

$LineColor = imagecolorallocate($NewImage,233,239,239);//line color
$TextColor = imagecolorallocate($NewImage, 255, 255, 255);//text color-white

imageline($NewImage,1,1,40,40,$LineColor);//create line 1 on image
imageline($NewImage,1,100,60,0,$LineColor);//create line 2 on image
imageline($NewImage,5, 1, 200, 50, $LineColor);

imagestring($NewImage, 5, 15, 5, $ResultStr, $TextColor);// Draw a random string horizontally

$_SESSION['key'] = $ResultStr;// carry the data through session
header("Content-type: image/jpeg");// out out the image
imagejpeg($NewImage);//Output image to browser

?> 

File: form.php

<?php

     session_start();


?>

<html>
<head>
<title>PHP-CAPTCHA </title>
</head>
<body onload="return focuson();">
<script   language="javascript">
function focuson()
{ document.form1.number.focus()}

function check()
{
if(document.form1.number.value==0)
{
alert("Please enter your Category Name");
document.form1.number.focus();
return false;
}
}

</script>


<?php

 if(isset($_REQUEST['Submit'])){
$key=substr($_SESSION['key'],0,5);
$number = $_REQUEST['number'];
if($number!=$key){
echo '<center><font face="Verdana, Arial, Helvetica, sans-serif" color="#FF0000">
Validation string not valid! Please try again!</font></center>';}
else{
echo '<center><font face="Verdana, Arial, Helvetica, sans-serif"  color="#66CC00">
Your string is valid!</font></center>';}
}


?>

<form name="form1" method="post" action="form.php"  onsubmit="return check();">
<img src="php_captcha.php" /></td>
<input name="number" type="text" id="number" /></td>
<input name="Submit" type="submit"   value="Submit" /></td>
</form>
</body>
</html>

Sumber :
http://irfanroom.wordpress.com/2008/01/22/membuat-php-captcha-sendiri/

CREATE TABLE tabelQuotes
(
id     int,
quote  text,
primary key(id)
);

----------------------------------------------------------------------------------------------

insert  into tabelquotes values
(1, 'Hari ini harus lebih baik dari kemarin'),
(2, 'Jangan tunda sampai besok apa yang bisa kalian
kerjakan hari ini'),
(3, 'Ingin investasi dunia akhirat? Sharinglah ilmu yang
Anda miliki dengan yang lain'),
(4, 'Ilmu lebih baik dari harta. Apabila Anda memiliki harta,
maka Andalah penjaganya. Namun jika Anda memiliki ilmu,
maka ilmulah yang akan menjaga Anda.');

----------------------------------------------------------------------------------------------

&lt;?php
mysql_connect("host", "user", "pass");
mysql_select_db("dbname");
$query = "SELECT * FROM tabelQuotes ORDER BY RAND()";
$hasil = mysql_query($query);
$data  = mysql_fetch_array($hasil);
{
echo "Pesan saat ini : ".$data['quote'];
}
?&gt;

----------------------------------------------------------------------------------------------

&lt;?php
mysql_connect("host", "user", "pass");
mysql_select_db("dbname");
$query = "SELECT * FROM tabelQuotes ORDER BY RAND() LIMIT 2";
$hasil = mysql_query($query);
while ($data  = mysql_fetch_array($hasil))
{
echo "Pesan saat ini : ".$data['quote']."&lt;br&gt;";
}
?&gt;