Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- include('definition.php');
- include('functions.php');
- if (phpversion() >= '4.1.0') {
- include_once('vars4.1.0.php');
- } else {
- include_once('vars4.0.6.php');
- }
- include_once('functions.php');
- include_once('mysql.php');
- $dbc=mysql_connect(_SRV, _ACCID, _PWD) or die(_ERROR15.": ".mysql_error());
- if ( isset( $_GET['sEcho'] ) ) {
- /* MySQL connection */
- mysql_select_db("prodschd") or die(_ERROR17.": ".mysql_error());
- /* Paging */
- $sLimit = "";
- if ( isset( $_GET['iDisplayStart'] ) )
- {
- $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
- mysql_real_escape_string( $_GET['iDisplayLength'] );
- }
- /* Ordering */
- if ( isset( $_GET['iSortCol_0'] ) )
- {
- $sOrder = "ORDER BY ";
- for ( $i=0 ; $i<mysql_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
- {
- $sOrder .= fnColumnToField(mysql_real_escape_string( $_GET['iSortCol_'.$i] ))."
- ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
- }
- $sOrder = substr_replace( $sOrder, "", -2 );
- }
- /* Filtering - NOTE this does not match the built-in DataTables filtering which does it
- * word by word on any field. It's possible to do here, but concerned about efficiency
- * on very large tables, and MySQL's regex functionality is very limited
- */
- $sWhere = "";
- if ( $_GET['sSearch'] != "" )
- {
- $sWhere = " WHERE No_ LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Model_Code LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Model_Name LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Lot_No_ LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Start_Seq_No_ LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Quantity LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Lot_Quantity LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Line_Code LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Line_Name LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
- "Production_Start_Date LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%'";
- } else {
- if ( $_GET['sSearch_0'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " No_ LIKE '%".mysql_real_escape_string( $_GET['sSearch_0'] )."%' ";
- }
- if ( $_GET['sSearch_1'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Model_Code LIKE '%".mysql_real_escape_string( $_GET['sSearch_1'] )."%' ";
- }
- if ( $_GET['sSearch_2'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Model_Name LIKE '%".mysql_real_escape_string( $_GET['sSearch_2'] )."%' ";
- }
- if ( $_GET['sSearch_3'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Lot_No_ like '%".mysql_real_escape_string( $_GET['sSearch_3'] )."%' ";
- }
- if ( $_GET['sSearch_4'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Start_Seq_No_ LIKE '%".mysql_real_escape_string( $_GET['sSearch_4'] )."%' ";
- }
- if ( $_GET['sSearch_5'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Quantity LIKE '%".mysql_real_escape_string( $_GET['sSearch_5'] )."%' ";
- }
- if ( $_GET['sSearch_6'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Lot_Quantity LIKE '%".mysql_real_escape_string( $_GET['sSearch_6'] )."%' ";
- }
- if ( $_GET['sSearch_7'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Line_Code LIKE '%".mysql_real_escape_string( $_GET['sSearch_7'] )."%' ";
- }
- if ( $_GET['sSearch_8'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Line_Name LIKE '%".mysql_real_escape_string( $_GET['sSearch_8'] )."%' ";
- }
- if ( $_GET['sSearch_9'] != "" ){
- if ($sWhere != "") $sWhere.=" AND ";
- $sWhere .= " Production_Start_Date LIKE '%".mysql_real_escape_string( $_GET['sSearch_9'] )."%' ";
- }
- if ($sWhere != "") $sWhere=" WHERE ".$sWhere;
- }
- $sQuery = "
- SELECT SQL_CALC_FOUND_ROWS No_,Model_Code,Model_Name,Lot_No_,Start_Seq_No_,Quantity,Lot_Quantity,Line_Code,Line_Name,Production_Start_Date
- FROM prod_sch
- $sWhere
- $sOrder
- $sLimit
- ";
- $rResult = mysql_query( $sQuery ) or die(mysql_error());
- $sQuery = "
- SELECT FOUND_ROWS()
- ";
- $rResultFilterTotal = mysql_query( $sQuery ) or die(mysql_error());
- $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
- $iFilteredTotal = $aResultFilterTotal[0];
- $sQuery = "
- SELECT COUNT(No_)
- FROM prod_sch
- ";
- $rResultTotal = mysql_query( $sQuery ) or die(mysql_error());
- $aResultTotal = mysql_fetch_array($rResultTotal);
- $iTotal = $aResultTotal[0];
- $data = array(
- "sEcho" => $_GET['sEcho'],
- "iTotalRecords" => $iTotal,
- "iTotalDisplayRecords" => $iFilteredTotal // you had two times iTotalDisplayRecords
- );
- $aaData = array();
- while ( $aRow = mysql_fetch_array( $rResult ) ) {
- $aaData[] = array($aRow['No_'],$aRow['Model_Code'],$aRow['Model_Name'],$aRow['Lot_No_'],$aRow['Start_Seq_No_'],$aRow['Quantity'],$aRow['Lot_Quantity'],$aRow['Line_Code'], $aRow['Line_Name'],$aRow['Production_Start_Date']);
- }
- $data['aaData'] = $aaData;
- echo json_encode($data);
- } else {
- // check databae, if no exist, create
- $sql="CREATE DATABASE IF NOT EXISTS prodschd;";
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- // select the database
- mysql_select_db("prodschd") or die(_ERROR17.": ".mysql_error());
- // check table, if not exist, create
- $sql="CREATE TABLE IF NOT EXISTS `prod_sch` (
- `No_` int(11) NOT NULL,
- `Model_Code` varchar(20) NOT NULL,
- `Model_Name` varchar(20) DEFAULT NULL,
- `Lot_No_` int(11) NOT NULL,
- `Start_Seq_No_` int(11) NOT NULL,
- `Quantity` int(11) NOT NULL,
- `Lot_Quantity` int(11) NOT NULL,
- `Line_Code` varchar(7) DEFAULT NULL,
- `Line_Name` varchar(10) DEFAULT NULL,
- `Production_Start_Date` date NOT NULL,
- `Shift_No1` int(3) DEFAULT NULL,
- `Production_Finish_Date` date NOT NULL,
- `Shift_No2` int(3) DEFAULT NULL,
- `Due_date` date NOT NULL,
- `Production_Mngm_Type` varchar(5) DEFAULT NULL,
- `Model_Group` varchar(15) DEFAULT NULL,
- `Model_Category` varchar(6) DEFAULT NULL,
- `Actual_Results` int(11) DEFAULT NULL,
- `MPS_Mngm_Code` varchar(10) DEFAULT NULL,
- `Production_Mngm_Code` varchar(6) DEFAULT NULL,
- `Comment` varchar(50) DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- $sql="CREATE TABLE IF NOT EXISTS settingdata (
- itemID mediumint(9) NOT NULL AUTO_INCREMENT,
- itemname varchar(255) DEFAULT NULL,
- itemdata varchar(255) DEFAULT NULL,
- remark varchar(255) DEFAULT NULL,
- PRIMARY KEY (itemID)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;";
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- // $sql="SELECT IF(count(*)=0,'1','0') from settingdata";
- // $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- // $dat=mysql_fetch_array($result, MYSQL_NUM);
- // if ($dat[0] = 0){
- $sql="INSERT INTO settingdata (itemID, itemname, itemdata, remark) VALUES
- (1, 'URL 6month schedule', 'http://136.198.117.2/prodschweb/Files/Arsip/PSO.txt', NULL),
- (2, 'Use Proxy','no',NULL),
- (3, 'Proxy', 'yps01.jvc-victor.co.jp:8080', NULL),
- (4, 'Sch RUN DATE', '', NULL)";
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- // }
- $sql="SELECT itemdata FROM settingdata WHERE itemname='URL 6month schedule'"; // set up URL of schedule
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- $row = mysql_fetch_array($result,MYSQL_ASSOC);
- $url = $row['itemdata'];
- // echo "Production schedule will be downloded from ".$url."<BR>";
- $sql="SELECT itemdata FROM settingdata WHERE itemname='Use Proxy'"; // set up URL of schedule
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- $row = mysql_fetch_array($result,MYSQL_ASSOC);
- $Proxyuse = $row['itemdata'];
- if ($Proxyuse!="no") {
- $Proxy = $row['itemdata']; $sql="SELECT itemdata FROM settingdata WHERE itemname='Proxy'"; // set up URL of schedule
- $result = mysql_query( $sql ) ;// or _doError(_ERROR30 . ' (' . htmlspecialchars($sql) . '): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- $row = mysql_fetch_array($result,MYSQL_ASSOC);
- $Proxy = $row['itemdata'];
- // echo "By using proxy server ".$Proxy."<BR>";
- // Define a context for HTTP.
- $aContext = array(
- 'http' => array(
- 'proxy' => 'tcp://'.$Proxy, // This needs to be the server and the port of the NTLM Authentication Proxy Server.
- 'request_fulluri' => True,
- ),
- );
- $cxContext = stream_context_create($aContext);
- // Now all file stream functions can use this context.
- $httpfile = file_get_contents($url, False, $cxContext);
- } else {
- $httpfile = file_get_contents($url);
- }
- //$httpfile = file_get_contents($url); // open schedule file
- //$httpfile = file_get_contents('http://136.198.117.2/prodschweb/Import/src/PSO.txt'); // open schedule file
- //echo "connected"." <BR>";
- $temp_file = tempnam(sys_get_temp_dir(), '3sch.jofcial.txt'); // make temporary file name
- //echo $temp_file;
- $fp = fopen($temp_file, "wb"); // open temprary file
- if (!$fp) die(_ERROR14);
- //echo "Opened"." <BR>";
- fwrite($fp, $httpfile); // copy to temporary file from schedule file
- fclose($fp);
- //echo "Downloaded"." <BR>";
- chmod($temp_file, 0644); // file mode change for read only
- //$temp_file=str_replace("\\","/",$temp_file);
- $sql="TRUNCATE TABLE `prod_sch`";
- $res=mysql_query($sql) ;//or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- $sql="LOAD DATA LOCAL INFILE '".$temp_file."' REPLACE INTO TABLE `prod_sch` FIELDS TERMINATED BY ',' IGNORE 1 LINES "; // FIELDS ENCLOSED BY '\"' ";
- $sql.="(No_ ,Model_Code,Model_Name ,Lot_No_ ,Start_Seq_No_ ,Quantity ,Lot_Quantity ,Line_Code ,Line_Name ,@var10, ";
- $sql.="Shift_No1,@var12,Shift_No2,@var14,Production_Mngm_Type,Model_Group,Model_Category,Actual_Results,MPS_Mngm_Code, ";
- $sql.="Production_Mngm_Code,Comment) ";
- $sql.="SET Production_Start_Date=STR_TO_DATE(@var10,'%m/%d/%Y'), ";
- $sql.="Production_Finish_Date=STR_TO_DATE(@var12,'%m/%d/%Y'), ";
- $sql.="Due_date=STR_TO_DATE(@var14,'%m/%d/%Y')";
- //echo $sql;
- $res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() ); // submit SQL to MySQL and error trap.
- unlink($temp_file);
- //echo "Copied"." <BR>";
- ?>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
- <title>Production shedule search</title>
- <style>
- @import "css/demo_page.css";
- @import "css/demo_table.css";
- @import "css/demo_table_jui.css";
- @import "js/media/css/TableTools.css";
- .right {
- text-align: right;
- vertical-align: top;
- }
- </style>
- <script src="js/jquery-1.4.2.min.js" type="text/javascript"></script>
- <script src="js/jquery.dataTables.min.js" type="text/javascript"></script>
- <script type="text/javascript" language="javascript" src="js/media/ZeroClipboard/ZeroClipboard.js"></script>
- <script type="text/javascript" language="javascript" src="js/TableTools.js "></script>
- <script type="text/javascript">
- var asInitVals = new Array();
- TableToolsInit.sSwfPath = "js/media/swf/ZeroClipboard.swf";
- $(document).ready(function() {
- var oTable = $('#schedtbl').dataTable( {
- "sDom": 'T<"clear">lfrtip',
- "bProcessing": true,
- "bServerSide": true,
- "sAjaxSource": "index.php",
- "aaSorting": [[ 9, "asc" ]],
- "sPaginationType": "full_numbers",
- // "bAutoWidth": false,
- "oLanguage": {
- "sSearch": "Search all columns:"
- },
- "aoColumns": [
- { "sClass": "right" },
- { "sWidth" : "17%" },
- null,
- { "sClass": "right" },
- { "sClass": "right" },
- { "sClass": "right" },
- { "sClass": "right" },
- null,
- null,
- null
- ]
- } );
- $("tfoot input").keyup( function () {
- /* Filter on the column (the index) of this element */
- oTable.fnFilter( this.value, $("tfoot input").index(this) );
- } );
- /*
- * Support functions to provide a little bit of 'user friendlyness' to the textboxes in
- * the footer
- */
- $("tfoot input").each( function (i) {
- asInitVals[i] = this.value;
- } );
- $("tfoot input").focus( function () {
- if ( this.className == "search_init" )
- {
- this.className = "";
- this.value = "";
- }
- } );
- $("tfoot input").blur( function (i) {
- if ( this.value == "" )
- {
- this.className = "search_init";
- this.value = asInitVals[$("tfoot input").index(this)];
- }
- } );
- });
- </script>
- </head>
- <body>
- <h1>Production schedule search</h1>
- <div id="dt_example">
- <div id="container">
- <div id="ref" style="text-align: right;">
- <a href="javascript:void(0)" id="clrfilt">Show all data</a>
- </div>
- <div style="text-align:right; padding-bottom:1em;"></div>
- <div id="demo"></div>
- <table cellpadding="0" cellspacing="0" border="0" class="display" id='schedtbl'>
- <thead>
- <tr>
- <th>No_</th>
- <th>Model_Code</th>
- <th>Model_Name</th>
- <th>Lot_No_</th>
- <th>Start_Seq_No_</th>
- <th>Quantity</th>
- <th>Lot_Quantity</th>
- <th>Line_Code</th>
- <th>Line_Name</th>
- <th>Production_Start_Date</th>
- </tr>
- </thead>
- <tfoot>
- <tr>
- <th><input type="text" name="search_No_" value="Search No" class="search_init" /></th>
- <th><input type="text" name="search_Model_Code" value="Search Model_Code" class="search_init" /></th>
- <th><input type="text" name="search_Model_Name" value="Search Model_Name" class="search_init" /></th>
- <th><input type="text" name="search_Lot_No_" value="Search Lot_No_" class="search_init" /></th>
- <th><input type="text" name="search_Start_Seq_No_" value="Search Start_Seq_No_" class="search_init" /></th>
- <th><input type="text" name="search_Quantity" value="Search Quantity" class="search_init" /></th>
- <th><input type="text" name="search_Lot_Quantity" value="Search Lot_Quantity" class="search_init" /></th>
- <th><input type="text" name="search_Line_Code_" value="Search Line_Code" class="search_init" /></th>
- <th><input type="text" name="search_Line_Name" value="Search Line_Name" class="search_init" /></th>
- <th><input type="text" name="search_Production_Start_Date" value="Search Production_Start_Date" class="search_init" /></th>
- </tr>
- </tfoot>
- <tbody></tbody>
- </table>
- <div id="ref"></div>
- </div>
- </div>
- </body>
- </html>
- <?php
- }
- function fnColumnToField( $i )
- {
- if ( $i == 0 )
- return "No_";
- else if ( $i == 1 )
- return "Model_Code";
- else if ( $i == 2 )
- return "Model_Name";
- else if ( $i == 3 )
- return "Lot_No_";
- else if ( $i == 4 )
- return "Start_Seq_No_";
- else if ( $i == 5 )
- return "Quantity";
- else if ( $i == 6 )
- return "Lot_Quantity";
- else if ( $i == 7 )
- return "Line_Code";
- else if ( $i == 8 )
- return "Line_Name";
- else if ( $i == 9 )
- return "Production_Start_Date";
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment