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