Read 441 times | Created 2013-09-12 03:08:43 | Updated 2013-09-12 06:22:43 | | |
<?php /* -- DATABASE : MYSQL -- CODE NAME : MAIL NUMBERING -- CREATE BY : Cahya DSN -- CREATE ON : 2013-09-12 -- -- tbl_mail_type to store mail type datas DROP TABLE IF EXISTS `tbl_mail_type`; CREATE TABLE IF NOT EXISTS `tbl_mail_type`( id_type TINYINT UNSIGNED AUTO_INCREMENT, code VARCHAR(3) NOT NULL, description VARCHAR(100) NOT NULL, PRIMARY KEY(id_type) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tbl_mail_type(`code`,`description`) VALUES ('SPK','Surat Perintah Kerja'), ('SPB','Surat Peminjaman Barang'), ('SKJ','Surat Keluar Aja'), ('PSK','Pokoknya Surat Keluar'); -- tbl_unit_work to store unit work datas DROP TABLE IF EXISTS `tbl_unit_work`; CREATE TABLE IF NOT EXISTS `tbl_unit_work`( id_unit TINYINT UNSIGNED AUTO_INCREMENT, code VARCHAR(5) NOT NULL, description VARCHAR(100) NOT NULL, PRIMARY KEY(id_unit) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tbl_unit_work(`code`,`description`) VALUES ('PROJD','Project Director'), ('TREAU','Treasury'), ('LEGAL','Legal Department'); -- tbl_out_mail to store out mail number datas DROP TABLE IF EXISTS `tbl_out_mail`; CREATE TABLE IF NOT EXISTS `tbl_out_mail`( id_mail SMALLINT(3) ZEROFILL AUTO_INCREMENT, id_unit TINYINT UNSIGNED NOT NULL, id_type TINYINT UNSIGNED NOT NULL, mail_date DATE NOT NULL, PRIMARY KEY(id_unit,id_mail) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tbl_out_mail(id_unit,id_type,mail_date) VALUES (1,1,'2013-08-03'), (2,1,'2013-08-05'), (1,3,'2013-08-08'), (3,2,'2013-08-09'), (2,2,'2013-08-10'); SELECT CONCAT(a.id_mail,'/',b.code,'/PIND6/',c.code,'/',DATE_FORMAT(mail_date,'%m.%Y')) AS mail_number FROM tbl_out_mail a JOIN tbl_mail_type b USING(id_type) JOIN tbl_unit_work c USING(id_unit); SELECT MAX(a.id_mail) FROM tbl_out_mail a JOIN tbl_unit_work b USING(id_unit) WHERE b.code='PROJD'; SELECT MAX(a.id_mail) FROM tbl_out_mail a JOIN tbl_unit_work b USING(id_unit) WHERE b.code='TREAU'; SELECT MAX(a.id_mail) FROM tbl_out_mail a JOIN tbl_unit_work b USING(id_unit) WHERE b.code='LEGAL'; */ /******************************* FILENAME : mail_numbering.php PURPOSE : select mail number CREATED BY : CAHYA DSN CREATED ON : 2013-09-12 *******************************/ $dbhost='localhost'; $dbuser='root'; $dbpass=''; $dbname='test'; $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname); ?> <!DOCTYPE html> <html> <head> <title>Mail Numbering</title> <style> * {font-family:verdana,arial,sans-serif;font-size:9px;color:#009;} input[type=text] {border:none;} label {font-size:12px;font-weight:bold;border-bottom:dotted 2px #999;margin-bottom:5px;} .cell {border:solid 1px #999;padding:2px;} th{background-color:#ccf;} </style> </head> <body> <div id='message'> <?php if(isset($_POST['submit'])) { $id_unit=explode("|",$_POST['id_unit']); $sql="INSERT INTO tbl_out_mail(id_unit,id_type,mail_date) " ."VALUES(" .$id_unit[0]."," .$_POST['id_type'].",'" .$_POST['thn']."-".$_POST['bln']."-".$_POST['tgl']."')"; if($result=$db->query($sql)) { echo "<span class='fade'>Data berhasil disimpan</span>"; } else { echo "<span class='fade'>Data gagal disimpan</span>"; } } ?> </div> <div id='container'> <fieldset> <label>Form Surat Keluar</label> <form method="POST"> <table> <tr> <td>Unit Kerja</td> <td> <select name='id_unit' id='id_unit' class='combo'> <?php $sql='SELECT id_unit,code,description FROM tbl_unit_work'; if($result=$db->query($sql)) { while($record=$result->fetch_object()) { echo '<option value="'.$record->id_unit.'|'.$record->code.'">' .$record->description.'</option>'; } $result->close(); } ?> </select> </td> </tr> <tr> <td>Jenis Surat</td> <td> <select name='id_type' id='id_type' class='combo'> <?php $sql='SELECT id_type,code FROM tbl_mail_type'; if($result=$db->query($sql)) { while($record=$result->fetch_object()) { echo '<option value="'.$record->id_type.'">'.$record->code.'</option>'; } $result->close(); } ?> </select> </td> </tr> <tr> <td>Tanggal Dokumen Keluar</td> <td> <select id='tgl' name='tgl'> <?php for($i=1;$i<=31;$i++) { echo "<option value='".($i<10?'0'.$i:$i)."'" .($i==date('j')?" selected='selected'":"") .">$i</option>n"; } ?> </select> <select id='bln' name='bln' class='combo'> <?php for($i=1;$i<=12;$i++) { echo "<option value='".($i<10?'0'.$i:$i)."'" .($i==date('n')?" selected='selected'":"") .">$i</option>n"; } ?> </select> <select id='thn' name='thn' class='combo'> <?php for($i=date('Y');$i<date('Y')+2;$i++) { echo "<option value='$i'>$i</option>n"; } ?> </select> </td> </tr> <tr> <td>Nomor Surat</td> <td> <input type='text' name='id_mail' id='id_mail' readonly='readonly' size='3'/>/ <input type='text' name='id_type_s' id='id_type_s' readonly='readonly' size='3'/>/ <input type='text' name='const' id='const' readonly='readonly' value='PIND6' size='5' />/ <input type='text' name='id_unit_s' id='id_unit_s' readonly='readonly' size='5' />/ <input type='text' name='bln_s' id='bln_s' readonly='readonly' size='2' />. <input type='text' name='thn_s' id='thn_s' readonly='readonly' size='4'/> </td> </tr> <tr> <td colspan='2'> <input type='submit' name='submit' value='submit' /> </td> </tr> </table> </form> </fieldset> <fieldset> <label>Daftar Nomor Surat Keluar</label> <table> <tr> <th class='cell'>No</th> <th class='cell'>Nomor Surat</th> <th class='cell'>Tgl Surat</th> <th class='cell'>Unit Kerja</th> <th class='cell'>Jenis Surat</th> </tr> <?php $sql="SELECT CONCAT(a.id_mail,'/',b.code,'/PIND6/',c.code,'/',DATE_FORMAT(a.mail_date,'%m.%Y')) AS no_surat, a.mail_date AS tgl, c.description AS unit, b.code AS jenis FROM tbl_out_mail a JOIN tbl_mail_type b USING(id_type) JOIN tbl_unit_work c USING(id_unit) ORDER BY a.mail_date DESC"; if($result=$db->query($sql)) { $no=1; while($record=$result->fetch_object()) { echo "<tr>" ."<td class='cell'>".$no++."</td>" ."<td class='cell'>".$record->no_surat."</td>" ."<td class='cell'>".$record->tgl."</td>" ."<td class='cell'>".$record->unit."</td>" ."<td class='cell'>".$record->jenis."</td>" ."</tr>"; } } ?> </table> </fieldset> </div> <script type='text/javascript' src='js/jquery.min.js'></script> <script> $(document).ready(function(){ $('.fade').fadeOut(5000); $('.combo').change(function(){ $('#id_type_s').val($('#id_type option:selected').text()); var str=$('#id_unit').val(); var ret=str.split("|"); $('#id_unit_s').val(ret[1]); $('#bln_s').val($('#bln').val()); $('#thn_s').val($('#thn').val()); $.post('max_number.php',{'id_unit':$('#id_unit').val()},function(data){ $('#id_mail').val(data); }) }); }); </script> </body> </html> <?php /******************************* FILENAME : max_number.php PURPOSE : get new number for specified unit CREATED BY : CAHYA DSN CREATED ON : 2013-09-12 *******************************/ $dbhost='localhost'; $dbuser='root'; $dbpass=''; $dbname='test'; $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname); $code=isset($_POST['id_unit'])?explode('|',$_POST['id_unit']):array(''); $sql="SELECT MAX(a.id_mail) AS maks FROM tbl_out_mail a JOIN tbl_unit_work b USING(id_unit) WHERE b.code='".$code[1]."'"; $rst=0; if($result=$db->query($sql)) { if($result->num_rows>0) { $record=$result->fetch_object(); $rst=$record->maks; } } echo str_pad(($rst+1),3,'0',STR_PAD_LEFT); ?>