火神三层中间件 多数据集+SQL语句混合提交示例代码

风神 2021-5-10 358

这是一个入库单的示例代码,需要靠SQL语句改变库存值:


procedure TfraModuleKuCun.BtnRuKuOKClick(Sender: TObject);
const
  RuKuDMXSql: string = ' select * from dRuKuDMX  where 1=2';
  KuCunChangeSql: string = 'select * from dKuCunChange where 1=2';
  KuCunChangeSqlMX: string = 'select * from dKuCunChangeMX where 1=2';
  UpdateSkuKuCunSql: string = 'update bShangPinSKU set ShuLiang=ShuLiang +%d where id=%s;';
  InsertKuCunChangeSql: string = 'INSERT INTO dKuCunChange ( ID,NO, Type,  YuanGongID,  YuanGongMC, ShuLiang, ShangPinId, ShopId ) ' + //
    ' VALUES ( %s, %s,%s,%s,  %s,( SELECT sum(ShuLiang)as ShuLiang FROM bShangPinSKU WHERE ShangPinID =%s) +%d, %s,%s ) ;';
var
  isOpenData: Boolean;
  vShangPinId, vDanWeiId, vKey, vRuKuDId, vKuCunChangeDanJvId, vMainImgUrl: string;
  vErrMsg, vRuKuDDanHao: string;
  vShangPinImgItem: TShangPinPhotoClass;
  vSumShuLiang: Integer;
  vSumJinE: Currency;
  vFireList: TList<TFireQuery>;
begin
  with QryRuKuDMX do
  begin
    DataInfo.SQL.Text := RuKuDMXSql;
    if not OpenData then
    begin
      dm.ShowTip(DataInfo.ErrMsg);
      exit;
    end;
  end;

  with QryKuCunChangeMX do
  begin
    DataInfo.SQL.Text := KuCunChangeSqlMX;
    if not OpenData then
    begin
      dm.ShowTip(DataInfo.ErrMsg);
      exit;
    end;
  end;
  if (FDMemRuKuDMX.Active = false) or (FDMemRuKuDMX.IsEmpty) then
  begin
    ShowMessage('请添加记录');
    exit;
  end;
  //商品入库-入库单-入库明细-库存变动表-变动明细表-商品SKU表
  //克隆数据集副本
  FDMemRuKuDMX_Copy.Close;
  FDMemRuKuDMX_Copy.CloneCursor(FDMemRuKuDMX);
  //根据商品ID处理数据
  FDQrySum.Close;
  FDQrySum.open; //汇总每个商品的总数量
  vRuKuDDanHao := dm.GetDanHao;
  QrySQL.DataInfo.SQL.Clear;
  with FDMemRuKuDMX_Copy do
  begin
    FDQrySum.First;
    while not FDQrySum.Eof do
    begin
      vKuCunChangeDanJvId := Dm.FireConnection1.GetGUID;
      vShangPinId := FDQrySum.FieldByName('ShangPinId').AsString;
      //库存变动表  插入SQL语句
      QrySQL.DataInfo.SQL.Add(Format(InsertKuCunChangeSql, [vKuCunChangeDanJvId.QuotedString, vRuKuDDanHao.QuotedString, '入库'.QuotedString, //
        Dm.UserId.QuotedString, Dm.UserName.QuotedString, vShangPinId.QuotedString, FDQrySum.FieldByName('ShuLiang').AsInteger, vShangPinId.QuotedString, Dm.ShopID.QuotedString]));
      //库存变动明细表
      Filtered := False;
      Filter := 'ShangPinId=' + vShangPinId.QuotedString;
      Filtered := True;
      while not Eof do
      begin
        QryKuCunChangeMX.Append;
        QryKuCunChangeMX.FieldByName('ID').AsString := Dm.FireConnection1.GetGUID;
        QryKuCunChangeMX.FieldByName('SKUId').AsString := FieldByName('SKUId').AsString;
        QryKuCunChangeMX.FieldByName('ShuLiang').AsInteger := FieldByName('ShuLiang').AsInteger;
        QryKuCunChangeMX.FieldByName('ShopId').AsString := dm.ShopID;
        QryKuCunChangeMX.FieldByName('DanJuId').AsString := vKuCunChangeDanJvId;
        QryKuCunChangeMX.Post;
        Next;
      end;
      FDQrySum.Next;
    end;
    //根据供货商分单据
    QrySumByDanWei.Close;
    QrySumByDanWei.open;
    QrySumByDanWei.First;
    while not QrySumByDanWei.Eof do
    begin
      vRuKuDId := dm.FireConnection1.GetGUID;
      vSumJinE := 0;
      vSumShuLiang := 0;
      vDanWeiId := QrySumByDanWei.FieldByName('DanWeiId').AsString;

      Filtered := False;
      Filter := 'DanWeiId=' + vDanWeiId.QuotedString;
      Filtered := True;
      First;
      while not Eof do
      begin
        vSumJinE := vSumJinE + FieldByName('ShuLiang').AsInteger * FieldByName('ChengBenJ').AsInteger;
        vSumShuLiang := vSumShuLiang + FieldByName('ShuLiang').AsInteger;
        //入库单明细表
        QryRuKuDMX.Append;
        QryRuKuDMX.FieldByName('ID').AsString := Dm.FireConnection1.GetGUID;
        QryRuKuDMX.FieldByName('SKUId').AsString := FieldByName('SKUId').AsString;
        QryRuKuDMX.FieldByName('ShuLiang').AsInteger := FieldByName('ShuLiang').AsInteger;
        QryRuKuDMX.FieldByName('JinE').AsCurrency := FieldByName('ShuLiang').AsInteger * FieldByName('ChengBenJ').AsInteger;
        QryRuKuDMX.FieldByName('ShopId').AsString := dm.ShopID;
        QryRuKuDMX.FieldByName('RuKuDId').AsString := vRuKuDId;
        QryRuKuDMX.Post;
        //修改库存
        QrySQL.DataInfo.SQL.Add(Format(UpdateSkuKuCunSql, [FieldByName('ShuLiang').AsInteger, FieldByName('SKUId').AsString.QuotedString]));
        Next;
      end;
      //入库单
      QryRuKuD.Append;
      QryRuKuD.FieldByName('ID').AsString := vRuKuDId;
      QryRuKuD.FieldByName('DanHao').AsString := vRuKuDDanHao;
      QryRuKuD.FieldByName('CreateDate').AsDateTime := Dm.FireConnection1.GetServerTime;
      QryRuKuD.FieldByName('ShopId').AsString := dm.ShopID;
      QryRuKuD.FieldByName('DanWeiId').AsString := vDanWeiId;
      QryRuKuD.FieldByName('ShuLiang').AsInteger := vSumShuLiang; //
      QryRuKuD.FieldByName('JinE').AsCurrency := vSumJinE;
      QryRuKuD.FieldByName('YuanGongId').AsString := dm.UserId;
      QryRuKuD.Post;
      //
      QrySumByDanWei.Next;
    end;
  end;
  vFireList := TList<TFireQuery>.create;
  try
    vFireList.add(QryRuKuDMX);
    vFireList.add(QryRuKuD);
    vFireList.add(QryKuCunChangeMX);
    vFireList.add(QrySQL);
    //火神三层多表提交数据    数据集+Sql语句混合提交
    if dm.FireConnection1.SaveQueryAll(vFireList, vErrMsg) then
    begin
      FDMemRuKuDMX.Close;
      FDMemRuKuDMX_Copy.close;
      FDMemRuKuDMX.CreateDataSet;
      SwitchToRuKuDPage;
      ShowMessage('保存成功')
    end
    else
      ShowMessage(vErrMsg);
  finally
    vFireList.Free;
  end;

end;



DelphiTop论坛申明 1、本网站名称:DelphiTop论坛  网址:www.DelphiTop.com
2、专注于互联网分享精神,专注收藏与分享。你薅网友,我就薅你。
3、本站资源,如发现链接失效,可联系QQ 16643506进行反馈,我们会第一时间更新。
最新回复 (1)
  • dacsd 2021-5-11 0
    2
    多数据集混合提交,如果其中有一个表提交不成功,其他的表会不会回滚。也就是所有的表都没有提交成功。
返回