API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 28th, 2012
147
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 8.14 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. include_once('install_lang_japanese.php');
  3. include_once('functions.php');
  4. include("mysql2json.class.php");
  5.  
  6. if (phpversion() >= '4.1.0') {
  7.     include_once('vars4.1.0.php');
  8. } else {
  9.     include_once('vars4.0.6.php');
  10. }
  11.  
  12. $jsonhead=getVar('jhead');//'results'   results
  13. $sqlwhere=getVar('q');
  14.  
  15. switch($jsonhead) {
  16.     case 'aaData' :
  17.         $needfieldname=false;
  18.         $sql="SELECT SQL_CALC_FOUND_ROWS ID as id, name, CASE nowuse WHEN 1 THEN 'yes' ELSE 'no' END AS nowuse , uPrice as 'Unit_price',
  19.             remark, CASE useQAstock  WHEN 1 THEN 'yes' ELSE 'no' END AS useQAstock , avrusepmonth as 'Avg_use_a_month',
  20.             moq as MOQ, leadtime as 'Lead_Time'
  21.             FROM test_media ";
  22.         break;
  23.     case 'results' :
  24.         $needfieldname=true;
  25.         $sql="SELECT ID as id, name FROM test_media WHERE nowuse=1 ";
  26.         if ($sqlwhere != "") {
  27.             $sql.= "AND name like '%".$sqlwhere."%'";
  28.         }
  29.         break;
  30.     case 'average' :
  31.         $needfieldname=false;
  32.         $month=getVar('calcmonth');
  33.         $sql="SELECT SQL_CALC_FOUND_ROWS ID,name,remark,avrusepmonth
  34.         , CONCAT('<input type=''checkbox''id=''cb' , ID ,''' name=''check[]'' value=''',ID,''' >'
  35.         ,'<label class=''lbcb'' for=''cb', ID,'''><=update=</label>') as checkb
  36.         , moq, leadtime
  37.         FROM test_media ";
  38.    
  39.         break;
  40. }
  41.  
  42. switch($jsonhead) {
  43.     case 'aaData' :
  44.         /* Paging */
  45.         $sLimit = "";
  46.         if ( isset( $_GET['iDisplayStart'] ) )
  47.         {
  48.             $sLimit = " LIMIT ".mysql_real_escape_string( getVar('iDisplayStart') ).", ".
  49.                 mysql_real_escape_string( getVar('iDisplayLength') );
  50.         }
  51.         /* Ordering */
  52.         if ( isset( $_GET['iSortCol_0'] ) )
  53.         {
  54.             $sOrder = " ORDER BY  ";
  55.             for ( $i=0 ; $i<=mysql_real_escape_string( getVar('iSortingCols') ) ; $i++ )
  56.             {
  57.                 $sOrder .= fnColumnToField4medialist(mysql_real_escape_string( getVar('iSortCol_'.$i) ))."
  58.                     ".mysql_real_escape_string( getVar('sSortDir_'.$i) ) .", ";
  59.             }
  60.             $sOrder = substr_replace( $sOrder, "", -2 );
  61.         }
  62.         /* Filtering - NOTE this does not match the built-in DataTables filtering which does it
  63.          * word by word on any field. It's possible to do here, but concerned about efficiency
  64.          * on very large tables, and MySQL's regex functionality is very limited
  65.          */
  66.         $sWhere = "";
  67.         if ( getVar('sSearch') != "" )
  68.         {
  69.             $sWhere = " WHERE name LIKE '%".mysql_real_escape_string( getVar('sSearch') )."%' OR ".
  70.                             "remark LIKE '%".mysql_real_escape_string( getVar('sSearch') )."%' ";
  71.         }
  72.         $num=0;
  73.         $sql.= $sWhere.$sOrder.$sLimit;
  74.         $dbc=mysql_connect(_SRV, _ACCID, _PWD) or die(_ERROR15.": ".mysql_error());
  75.         mysql_select_db("TestMediaControl") or die(_ERROR17.": ".mysql_error());
  76.         $res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  77.         $num=mysql_affected_rows();
  78.        
  79.     // -------------added from data tables sample  
  80.         $sql = "SELECT FOUND_ROWS()";
  81.         $rResultFilterTotal = mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  82.         $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
  83.         $iFilteredTotal = $aResultFilterTotal[0];
  84.        
  85.         $sql = "SELECT COUNT(id) FROM `test_media` ";
  86.         $rResultTotal = mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  87.         $aResultTotal = mysql_fetch_array($rResultTotal);
  88.         $iTotal = $aResultTotal[0];
  89.    
  90.         $data = array(
  91.             "sEcho" => getVar('sEcho'),
  92.             "iTotalRecords" => $iTotal,
  93.             "iTotalDisplayRecords" => $iFilteredTotal // you had two times iTotalDisplayRecords
  94.         );
  95.         $aaData = array();
  96.         while ( $aRow = mysql_fetch_array( $res ) ) {
  97.             $aaData[] = array(
  98.                             $aRow['id'],
  99.                             $aRow['name'],
  100.                             $aRow['nowuse'],
  101.                             $aRow['Unit_price'],
  102.                             $aRow['remark'],
  103.                             $aRow['useQAstock'],
  104.                             $aRow['Avg_use_a_month'],
  105.                             $aRow['MOQ'],
  106.                             $aRow['Lead_Time']
  107.                         );
  108.         }
  109.         $data['aaData'] = $aaData;
  110.         echo json_encode($data);
  111.         mysql_free_result($res);
  112.         break;
  113.     case 'average' :
  114.         /* Paging */
  115.         $sLimit = "";
  116.         if ( isset( $_GET['iDisplayStart'] ) )
  117.         {
  118.             $sLimit = " LIMIT ".mysql_real_escape_string( getVar('iDisplayStart') ).", ".
  119.                 mysql_real_escape_string( getVar('iDisplayLength') );
  120.         }
  121.         /* Ordering */
  122.         if ( isset( $_GET['iSortCol_0'] ) )
  123.         {
  124.             $sOrder = " ORDER BY  ";
  125.             for ( $i=0 ; $i<=mysql_real_escape_string( getVar('iSortingCols') ) ; $i++ )
  126.             {
  127.                 $sOrder .= fnColumnToField4medialistaverage(mysql_real_escape_string( getVar('iSortCol_'.$i) ))."
  128.                     ".mysql_real_escape_string( getVar('sSortDir_'.$i) ) .", ";
  129.             }
  130.             $sOrder = substr_replace( $sOrder, "", -2 );
  131.         }
  132.         /* Filtering - NOTE this does not match the built-in DataTables filtering which does it
  133.          * word by word on any field. It's possible to do here, but concerned about efficiency
  134.          * on very large tables, and MySQL's regex functionality is very limited
  135.          */
  136.         $sWhere = " WHERE nowuse=1 and monthavrage(ID,".$month.")  > 0 ";
  137.         if ( getVar('sSearch') != "" )
  138.         {
  139.             $sWhere = " AND name LIKE '%".mysql_real_escape_string( getVar('sSearch') )."%' OR  
  140.                             remark LIKE '%".mysql_real_escape_string( getVar('sSearch') )."%' ";
  141.         }
  142.         $num=0;
  143.         $sql.= $sWhere.$sOrder.$sLimit;
  144.         $dbc=mysql_connect(_SRV, _ACCID, _PWD) or die(_ERROR15.": ".mysql_error());
  145.         mysql_select_db("TestMediaControl") or die(_ERROR17.": ".mysql_error());
  146.         $res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  147.         $num=mysql_affected_rows();
  148.        
  149.     // -------------added from data tables sample  
  150.         $sql = "SELECT FOUND_ROWS()";
  151.         $rResultFilterTotal = mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  152.         $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
  153.         $iFilteredTotal = $aResultFilterTotal[0];
  154.        
  155.         $sql = "SELECT COUNT(id) FROM `test_media` ";
  156.         $rResultTotal = mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  157.         $aResultTotal = mysql_fetch_array($rResultTotal);
  158.         $iTotal = $aResultTotal[0];
  159.    
  160.         $data = array(
  161.             "sEcho" => getVar('sEcho'),
  162.             "iTotalRecords" => $iTotal,
  163.             "iTotalDisplayRecords" => $iFilteredTotal // you had two times iTotalDisplayRecords
  164.         );
  165.         $aaData = array();
  166.         while ( $aRow = mysql_fetch_array( $res ) ) {
  167.             $aaData[] = array(
  168.                             $aRow['ID'],
  169.                             $aRow['name'],
  170.                             $aRow['remark'],
  171.                             $aRow['avrusepmonth'],
  172.                             $aRow['checkb'],
  173.                         //  $aRow['latestavr'],
  174.                             $aRow['moq'],
  175.                             $aRow['leadtime']
  176.                         );
  177.         }
  178.         $data['aaData'] = $aaData;
  179.         echo json_encode($data);
  180.         mysql_free_result($res);
  181.         break;
  182.     default:
  183.         $dbc=mysql_connect(_SRV, _ACCID, _PWD) or die(_ERROR15.": ".mysql_error());
  184.         mysql_select_db("TestMediaControl") or die(_ERROR17.": ".mysql_error());
  185.         $res=mysql_query($sql) or _doError(_ERROR30 . ' (<small>' . htmlspecialchars($sql) . '</small>): ' . mysql_error() );  // submit SQL to MySQL and error trap.
  186.         $num=mysql_affected_rows();
  187.         $objJSON=new mysql2json();
  188.  
  189.         print(trim($objJSON->getJSON($res,$num,$jsonhead,$needfieldname)));
  190.    
  191.         mysql_free_result($res);
  192.         break;
  193. }
  194.  
  195.  
  196.  
  197. function fnColumnToField4medialist( $i ) {
  198.     if ( $i == 0 )
  199.         return "id";
  200.     else if ( $i == 1 )
  201.         return "name";
  202.     else if ( $i == 2 )
  203.         return "nowuse";
  204.     else if ( $i == 3 )
  205.         return "Unit_price";
  206.     else if ( $i == 4 )
  207.         return "remark";
  208.     else if ( $i == 5 )
  209.         return "useQAstock";
  210.     else if ( $i == 6 )
  211.         return "Avg_use_a_month";
  212.     else if ( $i == 7 )
  213.         return "MOQ";
  214.     else if ( $i == 8 )
  215.         return "Lead_Time";
  216. }
  217.  
  218. function fnColumnToField4medialistaverage( $i ) {
  219.     if ( $i == 0 )
  220.         return "ID";
  221.     else if ( $i == 1 )
  222.         return "name";
  223.     else if ( $i == 2 )
  224.         return "remark";
  225.     else if ( $i == 3 )
  226.         return "avrusepmonth";
  227.     else if ( $i == 4 )
  228.         return "checkb";
  229. /*  else if ( $i == 5 )
  230.         return "latestavr";
  231. */  else if ( $i == 5 )
  232.         return "moq";
  233.     else if ( $i == 6 )
  234.         return "leadtime";
  235. }
  236. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!