php连接mssql的方法（ADODB）

zhgc 发表于 2021-9-21 11:23:07

本帖最后由 zhgc 于 2021-9-21 11:24 编辑

<?php
/*
* 全飞鸿后台接口
* 本模块设计来处理erp数据
* 有任何问题请联系syant/13760216621
*/
class BOM extends tpage{
function BOM($app) {
parent::__construct($app);
$app->setDebug(true);//开发期间打开debug
}

复制代码

function getRealLoc($loc){
$loc=str_replace(" ",",",$loc);
$loc=str_replace("；",",",$loc);
$loc=str_replace("，",",",$loc);
$loc=str_replace(";",",",$loc);
$loc=str_replace("；",",",$loc);
$loc=str_replace("\r\n",",",$loc);
$loc=str_replace("\n\r",",",$loc);
$loc=str_replace("\r",",",$loc);
$loc=str_replace("\n",",",$loc);
return $loc;
}

复制代码

function getBOMinfo_bydb($PNO,$ZT){
$data=array();
$sql="select CB001 主件品号,CB005 元件品号,CB021 位号 from {$ZT}.dbo.BOMCB where CB001 = '{$PNO}'";
//$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from DGSC.dbo.BOMCB t1,DGSC.dbo.BOMMB t2
//where t1.CB005=t2.MB001 and t1.CB001 = '8HC011CMM80' and t2.MB002 in ('8HC011CMM80','********************')";
$rs = $this->app->db->Execute($sql);
if($rs){
if(!$rs->EOF){
$this->app->saveLog("{$ZT}找到{$PNO}!");
$data["bom"]=$rs->GetRows();
//
$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from {$ZT}.dbo.BOMCB t1,{$ZT}.dbo.BOMMB t2 where t1.CB005=t2.MB001 and t1.CB001 = '{$PNO}' and t2.MB002 in ('{$PNO}')";
$rss = $this->app->db->Execute($sql);
$data["replace"]=$rss->GetRows();
//
$sql="select t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号 from {$ZT}.dbo.BOMCB t1,{$ZT}.dbo.BOMMB t2 where t1.CB005=t2.MB001 and t1.CB001 = '{$PNO}' and t2.MB002 in ('********************')";
$rsss = $this->app->db->Execute($sql);
$data["common"]=$rsss->GetRows();
} else {
$this->app->saveLog("{$ZT}没有{$PNO}!");
}
} else {
$this->app->saveLog($db->errorMsg());
}
return $data;
}

复制代码

function getBOMInfo($PNO){
$data=array();
$ZT="DGSC";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
$ZT="AHXC";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
}
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
$ZT="XCLK";
$data=$this->getBOMinfo_bydb($PNO,$ZT);
}
if(empty($data)){
$this->app->saveLog("{$ZT}未找到{$PNO}!");
}
return $data;
}

复制代码

/*
* 确保与ERP是同步的数据
* [url]http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80[/url] (在ERP中检查最新的BOM)
* [url]http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80/save[/url] (将ERP中的BOM保存到MES)
*/
function checkbom(){
//http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/8HC011CMM80/save
//$segment : Array\n(\n [0] => erp\n [1] => bom\n [2] => checkbom\n [3] => 8HC011CMM80\n [4] => save \n)\n"
//$uri : "erp\/bom\/checkbom\/8HC011CMM80\/save\/"
global $segment;
$pno="";
$bSave=true; //false to true
//1. 参数处理
if(count($segment)<4){
$this->app->FAIL("没有传机种名称！");
return;
} else {
$pno=$segment[3];
if(count($segment)>4){
if($segment[4]=="save"){
$bSave=true;
}
}
}
//2. 查询ERP数据库
$data=$this->getBOMInfo($pno);
if(!empty($data)){
$commom=$data["common"];
$data["common"]=array();
//
$sss=json_encode($data,JSON_UNESCAPED_UNICODE);
$ccc=json_encode($commom,JSON_UNESCAPED_UNICODE);
$sssmd5=md5($sss);
$cccmd5=md5($ccc);
$this->app->saveLog($sssmd5);
if($this->app->prepare_mes(false)){ //true表示用standby
//comm
if(bSave){
$this->savecommon($cccmd5,$commom);
}
//
$mysql="select count(1) from smt.c_bom where p_no='{$pno}' and version='01' and bom_code='{$sssmd5}' limit 1";
$icount=$this->app->mes->getone($mysql)*1;
$this->app->saveLog("MES有记录{$icount}");
if($icount==0){
if($bSave){
$this->savebom($pno,$sssmd5,$data);
return ;
} else {
//$this->app->jsdata["res"]="FAIL";
//$this->app->jsdata["msg"]="警告：MES系统监控到ERP已修改了{$pno}机型BOM数据！";
$this->app->FAIL("警告：MES系统监控到ERP已修改了{$pno}机型BOM数据！");
return ;
}
} else {
//$this->app->jsdata["res"]="OK"; //是OK的，未变化！
$this->app->OK();
return ;
}
} else{
//$this->app->jsdata["msg"]=$sssmd5;
};
//
} else {
//$this->app->jsdata["res"]="FAIL";
//$this->app->jsdata["msg"]="ERP中没有BOM";
$this->app->FAIL("ERP中没有机型{$pno}的BOM数据");
return ;
}
}

复制代码

function savecommon($md5,$data){
//$start=$this->app->getTickCount();
$PNO="MES_COMMON_KP";
$mysql="select count(1) from smt.c_bom where p_no='{$PNO}' and version='01' and bom_code='{$md5}' limit 1";
$icount=$this->app->mes->getone($mysql)*1;
if($icount>0){
return ;// OK
}
$sqls=array();
$sqls[]="update smt.c_bom set end_time=now() where p_no='{$PNO}' and version='01'";
$sqls[]="delete from smt.c_bom where p_no='{$PNO}' and version='01'";
$sqls[]="insert into smt.c_bom(bom_code,p_no,version,cust_code,update_time,side,flag) values ('{$md5}','{$PNO}','01','MI01',now(),'BT',1); ";
$sqls[]="delete from smt.c_bom_replace where BOM_CODE='{$PNO}'";
//分析替代料
foreach($data as $v){
//"33080101 ","33080100 ","********************","C25,C31"
$location=$this->getRealLoc($v[3]);
$kp=$v[0];
$rkp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_replace(BOM_CODE,CUST_KP_NO,REPLACE_NO,LOCATION,DOWNLOADTIME,EMP) values ('{$PNO}','{$kp}','{$rkp}','{$loc}',now(),'SYANT')";
$sqls[]=$sql;
$icount=$icount+1;
}
}
}

复制代码

            //开始做事务保存
            //$this->app->saveLog("do common sql:".print_r($sqls,true));
            if($this->app->prepare_mes(false)){
                     $this->app->mes->BeginTrans();
                     foreach($sqls as $sql){
                              $rst=$this->app->mes->Execute($sql); //
                              if($rst){
                                    //
                              }else{
                                    //$this->app->FAIL('execute error:  '.$this->app->mes->ErrorMsg()."  SQL:".$sql);
                                    $this->app->saveLog('Execute error:  '.$this->app->mes->ErrorMsg()."  SQL:".$sql);
                                    break;
                              }
                     }
                     if($rst){
                              $this->app->mes->CommitTrans();
                              $this->app->saveLog("通用替代料保存成功！");
                              //$this->app->OK("保存成功！");
                     }else{
                              $this->app->mes->RollbackTrans();
                     }
            } else {
                     $this->app->FAIL("MES数据库连接不可用！");
            }
      }

/*
* 保存BOM数据到MES中
*/
function savebom($PNO,$md5,$data){
$start=$this->app->getTickCount();
$sqls=array();
$sqls[]="update smt.c_bom set end_time=now() where p_no='{$PNO}' and version='01'";
$sqls[]="delete from smt.c_bom where p_no='{$PNO}' and version='01'";
$sqls[]="insert into smt.c_bom(bom_code,p_no,version,cust_code,update_time,side,flag) values ('{$md5}','{$PNO}','01','MI01',now(),'BT',1); ";
$sqls[]="delete from smt.c_bom_location where bom_code='{$md5}'";
$sqls[]="delete from smt.c_bom_kp where bom_code='{$md5}'";
$sqls[]="delete from smt.c_bom_replace where bom_code='{$md5}'";
//分析主料
foreach($data["bom"] as $v){
//$this->app->jsdata["msg"]=print_r($v,true);
//Array([0] => 8HC011CMM80 [1] => 11508534 [2] => U4,U6,U7)
//$location=$v[2];
$location=$this->getRealLoc($v[2]);
$kp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_location(bom_code,cust_kp_no,location,replace_flag) values ('{$md5}','{$kp}','{$loc}',1)";
$sqls[]=$sql;
$icount=$icount+1;
}
}
$sqls[]="insert into smt.c_bom_kp(bom_code,cust_kp_no,kp_desc,std_qty)
values ('{$md5}','{$kp}','{$kp}','{$icount}')";
}
//分析替代料
foreach($data["replace"] as $v){
//t1.CB005 元件,t2.MB004 替代料,t2.MB002 主件品号,t1.CB021 位号
//$location=$v[3];
$location=$this->getRealLoc($v[3]);
$kp=$v[0];
$rkp=$v[1];
$locs=explode(",",$location);
$icount=0;
foreach($locs as $loc){
if(strlen(trim($loc))>0){
$sql="insert into smt.c_bom_replace(BOM_CODE,CUST_KP_NO,REPLACE_NO,LOCATION,DOWNLOADTIME,EMP) values ('{$md5}','{$kp}','{$rkp}','{$loc}',now(),'SYANT')";
$sqls[]=$sql;
$icount=$icount+1;
}
}
}
//开始做事务保存
if($this->app->prepare_mes(false)){
$this->app->mes->BeginTrans();
foreach($sqls as $sql){
$rst=$this->app->mes->Execute($sql); //
if($rst){
//
}else{
$this->app->FAIL('execute error: '.$this->app->mes->ErrorMsg()." SQL:".$sql);
break;
}
}
if($rst){
$this->app->mes->CommitTrans();
$this->app->OK("保存成功！");
}else{
$this->app->mes->RollbackTrans();
}
} else {
$this->app->FAIL("MES数据库连接不可用！");
}
}

复制代码

}
?>

zhgc 发表于 2021-9-21 11:17:58

MSSERVER/MSSQL类型ERP的连接

<?php
/*
* 全飞鸿后台接口
* 本模块设计来处理erp数据
* 有任何问题请联系syant/13760216621
*/
require_once("adodb/adodb.inc.php"); //版本升级为5.20.14
class erp extends tapp{
var $db;
/*
* MES系统EPR数据处理接口 v1.0
* DGSC,AHXC, XCLK，OMKJ,XGLK
*/
function erp() {
parent::__construct(); //一定要添加这一句
$this->db = ADONewConnection('mssqlnative');
//$this->db->setConnectionParameter('ReturnDatesAsStrings',false);
$this->db->setConnectionParameter('characterSet','UTF-8');
$this->db->connect("10.0.10.0","Xc","2018","DGSC"); //AHXC
$this->db->SetFetchMode(ADODB_FETCH_NUM); //ADODB_FETCH_NUM ADODB_FETCH_ASSOC ADODB_FETCH_BOTH
}
}

复制代码

zhgc 发表于 2021-9-21 11:12:26

第一个案例分析：

'确定BOM
If (bCheckBom) Then
sUrl = "http://192.168.100.18/mesapi/index.php/erp/bom/checkbom/" & Trim(Me.cmbproduct.Text) & "/save?character=gbk"
sBom = GetUrlStr(sUrl)
'{"tag":418,"res":"OK","msg":"","tick":0.104,"time":"2020-04-21 20:19:41","log":["DGSC找到8HC019CMMA0!","1e50e588ef00c898b2cbc65bd6a96479","MES有记录1"],"data":[]}
If InStr(sBom, "res"":""OK""") > 0 Then
MsgBox "注意：" & Trim(Me.cmbproduct.Text) & "料表ERP数据同步成功！"
Else
MsgBox "警告：" & Trim(Me.cmbproduct.Text) & "料表ERP数据同步不成功！"
' Screen.MousePointer = vbDefault
' Exit Sub
End If
End If

复制代码

Attribute VB_Name = "MesAPI"
Option Explicit
'vb代替inet控件获得网页源代e79fa5e98193e78988e69d8331333363386162码（解决代码不完整的问题）
Public Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Public Const INTERNET_OPEN_TYPE_DIRECT = 1
Public Const INTERNET_OPEN_TYPE_PROXY = 3
Public Const scUserAgent = "Microsoft Internet Explorer 6.0"
Public Const INTERNET_FLAG_RELOAD = &H80000000
Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Public Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hOpen As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Public Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Public Function GetUrlStr(URL) As String '调用时 URL 参数一定要带 http:// 前缀.
Dim hOpen As Long
Dim hOpenUrl As Long
Dim sUrl As String
Dim bDoLoop As Boolean
Dim bRet As Boolean
Dim sReadBuffer As String * 2048
Dim lNumberOfBytesRead As Long
Dim sBuffer As String
sUrl = URL
hOpen = InternetOpen(scUserAgent, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)
bDoLoop = True
While bDoLoop
sReadBuffer = vbNullString
bRet = InternetReadFile(hOpenUrl, sReadBuffer, Len(sReadBuffer), lNumberOfBytesRead)
sBuffer = sBuffer & left$(sReadBuffer, lNumberOfBytesRead)
If Not CBool(lNumberOfBytesRead) Then bDoLoop = False
Wend
GetUrlStr = sBuffer
If hOpenUrl <> 0 Then InternetCloseHandle (hOpenUrl)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
End Function

复制代码

zhgc 发表于 2021-1-28 22:11:31

再次总结：

1.sqlncli64.msi一定要安装，他实际是SQL SERVER 2008 Native Client

2.SQLSRV32.EXE已经没有安装必要，因为只有32位的，没有64位的

只接把64位目录中的
php_pdo_sqlsrv_56_ts.dll
php_sqlsrv_56_ts.dll
放在php的ext目录

然后在php.ini中加上
extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_pdo_sqlsrv_56_ts.dll
extension=php_sqlsrv_56_ts.dll

这样php就可以连通

zhgc 发表于 2020-12-13 23:18:48

保存位置

\tools.syant\php.syant\SQLServer_MSSQL_PHP

zhgc 发表于 2020-12-13 23:13:37

保存位置

\tools.syant\php.syant\SQLServer_MSSQL_PHP

		自动登录	找回密码
密码			立即注册

正文摘要:

回复