php提交数据到SQLserver数据库

PHP提交数据到SQLserver数据库做复杂计算

应用场景:

生产领料生成功能:
1.生成领料表头,表体
2.回写生产订单表体中的已领量,做累加计算
3.如果领用完成,标记生产订单表头,不让再次领料

以前复杂数据写入数据库都是用特殊字符分割,传到SQL中后,调用函数来处理成表格,再以这个表格展开计算.现在不用这么麻烦了,SQLserver2016版本中提供了解晰JSON的函数,减化了我们的工作

• 前端提交表单,以JSON格式提交给PHP后台接口
  示例图片
  
  这里的代码,用的是layUI的表格,直接提取就是数组,POST提交到后台

• PHP处理前端传入的JSON,组装成SQL存储过程解晰的格式
  php接收处理时,不要拼接SQL,用参数化查询,大概如下示例:

        //配料保存本地
    case 'saveML':
        //组装JSON,格式要求:{'主表键值 ',[{'子表行项目'},{'子表行项目'}]}
        $arr = ['orderCode'=>$_POST['ml']['orderCode'],'prdCode'=>$_POST['ml']['prdCode'],'prdName'=>$_POST['ml']['prdName'],"mls"=>$_POST['mls']];
        //此处建义参数化查询,安全,可能需要提交特殊字符
        $sql = "exec PR_pdAddML ?";
        $pram = [json_encode($arr)];
        //调用存储过程,此处两个静态方法是专搞SQL连接和执行的
        $link = Sqlserver::connect($ssoSystem);
        $re = Sqlserver::getLists($link,$sql,$pram);
        //结果判断
        if($re[0]['re']==1)
        {
            echo json_encode(["state"=>1,"msg"=>"success"]);
        }
        else{
            echo json_encode(["state"=>0,"msg"=>"fail"]);
        }
        break;

• SQL存储过程计算,将结果返回给PHP后台
  存储过程在计算时,要启用事务

    USE [ApiServer]
  GO
  /****** Object:  StoredProcedure [dbo].[PR_pdAddML]    Script Date: 2021-01-22 15:20:07 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:        <Author,,jackli>
  -- Create date: <Create Date,2020-01-21,>
  -- Description:    <Description,配料保存本地,更新已领,领用完成,配料完成,>
  -- =============================================
  ALTER PROCEDURE [dbo].[PR_pdAddML] 
    @ml nvarchar(max)
  AS
  BEGIN
    declare @mlCode nvarchar(50) = newID() --配料单号
    declare @orderCode nvarchar(50)  --订单号
    declare @potCode nvarchar(50)    --拆分批次锅号
    declare @re int = 0              --返回结果
    set nocount on
    --json生成表
    SELECT orderCode,prdCode,prdName,item,potCode,prdCodes,prdNames,rsvQty,batCode,wh,qty,unit,ordersItem,reservation,rsItem  into #pdml
    FROM OPENJSON(@ml)  
      WITH (
        orderCode nvarchar(50) '$.orderCode',
        prdCode NVARCHAR(200) '$.prdCode',
        prdName NVARCHAR(200) '$.prdName',
        mls NVARCHAR(MAX) '$.mls' AS JSON
      )
    OUTER APPLY OPENJSON(mls)
      WITH (item NVARCHAR(50) '$.id',
      potCode NVARCHAR(50) '$.potCode',
      prdCodes nvarchar(50) '$.prdCodes',
      prdNames NVARCHAR(50) '$.prdNames',
      rsvQty decimal(18,3) '$.rsvQty',
      batCode NVARCHAR(50) '$.batCode',
      wh NVARCHAR(50) '$.wh',
      qty decimal(18,3) '$.qty',
      unit NVARCHAR(50) '$.unit',
      ordersItem NVARCHAR(50) '$.item',
      reservation NVARCHAR(50) '$.reservation',
      rsItem NVARCHAR(50) '$.rsItem');
    --取订单号
    select top 1 @orderCode=orderCode,@potCode=potCode from #pdml
    --启用事务
    begin tran mytran 
        begin try
            --写配料表头
            insert rc_pdML(mlCode,orderCode,prdCode,prdName,createTime) 
            select top 1 @mlCode,orderCode,prdCode,prdName,GETDATE() from #pdml
            --写配料表体
            insert rc_pdMLs(mlCode,item,potCode,prdCode,prdName,rsvQty,batCode,wh,qty,unit,ordersItem,reservation,rsItem)
            select @mlCode,item,potCode,prdCodes,prdNames,rsvQty,batCode,wh,qty,unit,ordersItem,reservation,rsItem from #pdml
            --更新订单已领量
            update rc_pdOrders set qty = b.qty from rc_pdOrders a inner join 
            (select reservation,ordersItem,SUM(qty) as qty from #pdml group by reservation,ordersItem) b 
            on a.reservation = b.reservation and a.item=b.ordersItem
            --不允许再次配料
            update rc_pdOrders set clo=1 where potCode=@potCode and orderCode = @orderCode
            --全部配料后关闭订单
            declare @potNum int,@oldNum int
            select @potNum=count(1) from (select potCode from rc_pdOrders where orderCode=@orderCode group by potCode)b
            select @oldNum=count(1) from (select b.potCode from rc_pdML a inner join rc_pdMLs b on a.mlCode=b.mlCode where a.orderCode=@orderCode group by potCode)c
            if @potNum = @oldNum
                begin
                    update rc_pdOrder set endMark=1 where orderCode=@orderCode 
                end
            set @re=1
            commit tran  --执行事务前设置返回值为1
        end try 
        begin catch 
            rollback tran --回滚事务 
        end catch
    --事务结束 开启SQL消息记录
    drop table #pdml
    set nocount off
    select @re as re
  END

• PHP后台将结果返回给前端,跳出页面提示
  成功返回1,失败返回0,前端根据结果提示用户

parm = JSON.stringify(parm);
$.ajax({
    type: "POST",
    dataType: "json",
    url: "/ajaxServer/ajaxServer.php",
    data: { parm: parm },
    beforeSend: function () {
        //请求等待时动图,本地太快了基本没效果
        this.layerIndex = layer.load(0, { shade: false });
    },
    complete: function () {
        //请求完成关闭动画
        layer.close(this.layerIndex);
    },
    success: function (re) {
        console.log(JSON.stringify(re.msg));
        //let state = JSON.parse(re)["state"];
        if (re.state == 1) {
            layer.msg("保有存成功:" + re.msg, { icon: 1 });
        } else {
            layer.msg("保存失败:" + re.msg, { icon: 2 });
        }
    },
});