Read 107 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);
?>