upublicvar.~pas

天涯進銷存系統

    SavePartData.First;
    while not Eof  do
    begin
      //打开仓库信息
      OpenData('Select * From StockPart where PartCode='
               +#39+Fieldbyname('PartCode').asstring+#39+
               ' and DepotCode='+#39+Trim(DepotCode)+#39,
               sdsPublic);
       //如果库存上没有本商品新增
      if sdsPublic.IsEmpty then
      begin
        sdsPublic.Append;
        sdsPublic.FieldByName('PartCode').AsString:=Fieldbyname('PartCode').asstring;
        sdsPublic.FieldByName('DepotCode').AsString:=Trim(DepotCode);
        sdsPublic.FieldByName('DepotName').AsString:=Trim(DepotName);
        sdsPublic.FieldByName('BILLQUANTITY').AsFloat:=Fieldbyname('Quantity').asFloat;
        if (InType=1) or (InType=4) then
          sdsPublic.FieldByName('STOCKQUANTITY').AsFloat:=Fieldbyname('Quantity').asFloat
        else
          sdsPublic.FieldByName('STOCKQUANTITY').AsFloat:=0;
      end
      else //如果仓库有此商品
      begin
        sdsPublic.Edit;
        if (inType=0) or (inType=3) then
          sdsPublic.FieldByName('BILLQUANTITY').AsFloat:=
          sdsPublic.FieldByName('BILLQUANTITY').AsFloat+
          Fieldbyname('Quantity').asFloat
        else
        if (inType=1) or (inType=4) then
        begin
          sdsPublic.FieldByName('STOCKQUANTITY').AsFloat:=
          sdsPublic.FieldByName('STOCKQUANTITY').AsFloat+
          Fieldbyname('Quantity').asFloat;
        end;
      end;
      sdsPublic.ApplyUpdates(-1);
      //打开商品信息
      OpenData('select Code, OutPoutPrice, BillQuantity, StockQuantity, Del, '+
               'StockPrice, SalePrice, StockDate, CostPrice From PARTinfo where '+
               'Code='+#39+Fieldbyname('PartCode').asstring+#39, sdsPub);
      if not sdsPub.IsEmpty then
      begin
        sdsPub.Edit;
        //如果是库存库
        if inType=4 then  //商品移仓
        begin
          sdsPub.Fieldbyname('StockQuantity').asfloat:=
                 sdsPub.Fieldbyname('StockQuantity').asfloat+
                 Fieldbyname('Quantity').asFloat;
        end
        else
        if inType=1  then
        begin
          CostSum:=sdsPub.FieldByName('StockQuantity').AsFloat*
                   sdsPub.FieldByName('CostPrice').AsFloat+
                   Fieldbyname('TotalSum').asFloat;
          sdsPub.Fieldbyname('StockQuantity').asfloat:=
                   sdsPub.Fieldbyname('StockQuantity').asfloat+
                   Fieldbyname('Quantity').asFloat;
          if sdsPub.FieldByName('StockQuantity').AsFloat<>0 then
             sdsPub.FieldByName('CostPrice').AsFloat:=CostSum/
             sdsPub.FieldByName('StockQuantity').AsFloat;
        end
        else
        begin
          sdsPub.Fieldbyname('BillQuantity').asfloat:=
                 sdsPub.Fieldbyname('BillQuantity').asfloat+
                 Fieldbyname('Quantity').asFloat;
          if inType=0 then
          begin
            sdsPub.FieldByName('StockPrice').AsFloat:=
                   FieldByName('Price').AsFloat;
            sdsPub.FieldByName('StockDate').asDateTime:=BillDate;
          end
        end;
        sdsPub.FieldByName('Del').asstring:='1';
        sdsPub.ApplyUpdates(-1);
      end;
      Next;
    end;
    except
      Application.MessageBox('数据提交不成功。',Hintinfo,$30);
      Abort;
    end;
  end;
end;

procedure PartOutPutDataSave(DepotCode, DepotName : string;
                             BillDate: TDate; OutType: Integer);
//var
//  COSTSum: Double;
begin
  with SavePartData, dmData do
  begin
    SavePartData.First;
    try
    if IsEmpty then
    begin
      Application.MessageBox('没有可出库的数据。',Hintinfo,$30);
      Abort;
    end;
    while not Eof  do
    begin
      OpenData('Select * From StockPart where PartCode='
               +#39+Fieldbyname('PartCode').asstring+#39+
               ' and DepotCode='+#39+Trim(DepotCode)+#39,
               sdsPublic);
       //如果库存上没有本商品则新增
      if sdsPublic.IsEmpty then
      begin
        sdsPublic.Append;
        sdsPublic.FieldByName('PartCode').AsString:=Fieldbyname('PartCode').asstring;
        sdsPublic.FieldByName('DepotCode').AsString:=Trim(DepotCode);
        sdsPublic.FieldByName('DepotName').AsString:=Trim(DepotName);
        sdsPublic.FieldByName('BILLQUANTITY').AsFloat:=-Fieldbyname('Quantity').asFloat;
        sdsPublic.FieldByName('STOCKQUANTITY').AsFloat:=0;
      end
      else
      begin
        sdsPublic.Edit;
        sdsPublic.FieldByName('BILLQUANTITY').AsFloat:=
        sdsPublic.FieldByName('BILLQUANTITY').AsFloat-
                  Fieldbyname('Quantity').asFloat
      end;
      sdsPublic.ApplyUpdates(-1);
      //打开商品信息
      OpenData('select Code, BillQuantity, StockQuantity, Del, '+
               'SaleDate, SalePrice From Partinfo where '+
               'Code='+#39+Fieldbyname('PartCode').asstring+#39, sdsPub);
      sdsPub.Edit;
      sdsPub.FieldByName('BillQuantity').Asfloat:=
             sdsPub.FieldByName('BillQuantity').Asfloat-
             Fieldbyname('Quantity').asFloat;
      if OutType=1 then
      begin
        sdsPub.FieldByName('SalePrice').AsFloat:=
               FieldByName('Price').AsFloat;
        sdsPub.FieldByName('SaleDate').asDateTime:=BillDate;
      end;
{      if OutType=3 then
      begin
        //退货时核算成本单价?
        {CostSum:=sdsPub.FieldByName('StockQuantity').AsFloat*
                 sdsPub.FieldByName('CostPrice').AsFloat-
                 Fieldbyname('TotalSum').asFloat; }
{        if (sdsPub.FieldByName('StockQuantity').AsFloat-
            Fieldbyname('Quantity').asFloat)>0 then
        begin
          sdsPub.FieldByName('CostPrice').AsFloat:=CostSum/
          (sdsPub.FieldByName('StockQuantity').AsFloat-
          Fieldbyname('Quantity').asFloat);
        end
        else
        begin
          sdsPub.FieldByName('CostPrice').AsFloat:=0;
        end;
      end;}
      SetDataDelInfo('PartInfo','Code', Fieldbyname('PartCode').asstring);
      sdsPub.ApplyUpdates(-1);
      Next;
    end;
    except
      Application.MessageBox('数据提交不成功。',Hintinfo,$30);
      Abort;
    end;
  end;
end;

procedure SetPaymentSum(Value: Integer; Code: string; Sum : Extended);
var
  SqlText: string;
begin
  case Value of
  1: //采购付款
    begin
      SqlText:='Update ProviderInfo set ARREARAGESUM=ARREARAGESUM+'+
           FloatToStr(Sum)+', PAYMENTSUM=ARREARAGESUM-PREPAYSUM-REIMBURSESUM where Code='+#39+Code+#39;
    end;
  2: //销售收款
    begin
      SqlText:='Update ClientInfo set ARREARAGESUM=ARREARAGESUM+'+
           FloatToStr(Sum)+', PAYMENTSUM=ARREARAGESUM-PREPAYSUM-REIMBURSESUM where Code='+#39+Code+#39;
    end;
  3: //采购退货收款
    begin
      SqlText:='Update ProviderInfo set REIMBURSESUM=REIMBURSESUM+'+
           FloatToStr(Sum)+', PAYMENTSUM=ARREARAGESUM-PREPAYSUM-REIMBURSESUM where Code='+#39+Code+#39;
    end;
  4://销售退货付款
    begin
      SqlText:='Update ClientInfo set REIMBURSESUM=REIMBURSESUM+'+
           FloatToStr(Sum)+', PAYMENTSUM=ARREARAGESUM-PREPAYSUM-REIMBURSESUM where Code='+#39+Code+#39;
    end;
  5: //调入付款
    begin
      SqlText:='Update FilialeInfo set INPUTSUM=INPUTSUM+'+
           FloatToStr(Sum)+' where FilialeCode='+#39+Code+#39;
    end;
  6: //调出收款
    begin
      SqlText:='Update FilialeInfo set OUTPUTSUM=OUTPUTSUM+'+
           FloatToStr(Sum)+' where FilialeCode='+#39+Code+#39;
    end;
  end;
  with dmData.SQLQuery do
  begin
    Close;
    SQL.Text:=SqlText;
    ExecSQL;
    Close;
  end;
end;

procedure SetOrderBillExcInfo(OrderCode: string; Cds : TSimpleDataSet); //更新订货单已执行的数据
var
  sql: string;
begin
  with dmData do
  begin
    while not Cds.Eof do
    begin
      if Cds.Fieldbyname('OrderID').asstring<>'' then
      begin
        Sql:='select ID, BillCode, Quantity, ReceiptQur, isExecute From OrderBillBody '+
             ' where BillCode='+#39+OrderCode+#39+
             ' and ID='+#39+Cds.Fieldbyname('OrderID').asstring+#39;
        OpenData(Sql,sdsPublic);
        if not sdsPublic.IsEmpty then
        begin
          sdsPublic.Edit;
          //如果数量大于订货数量
          if Cds.FieldByName('Quantity').asfloat>
             sdsPublic.FieldByName('Quantity').asfloat-
             sdsPublic.FieldByName('ReceiptQur').asfloat then
             sdsPublic.FieldByName('ReceiptQur').asfloat:=
             sdsPublic.FieldByName('Quantity').asfloat
          else
             sdsPublic.FieldByName('ReceiptQur').asfloat:=
             sdsPublic.FieldByName('ReceiptQur').asfloat+
             Cds.FieldByName('Quantity').asfloat;
             //如果数量和订货相同，则完成收货
          if sdsPublic.FieldByName('Quantity').asfloat-
             sdsPublic.FieldByName('ReceiptQur').asfloat=0 then
             sdsPublic.FieldByName('isExecute').asstring:='1';
             sdsPublic.ApplyUpdates(-1);
        end;
      end;  
      Cds.Next;
    end;
    OpenData('select BillCode From OrderBillBody '+
             'where isExecute='''+'0'''+' and BillCode='+#39+OrderCode+#39,
              sdsPublic);
    if sdsPublic.IsEmpty then
    begin
      SQLQuery.Close;
      SQLQuery.SQL.Text:='Update OrderBillHand Set isExecute='''+'1'''+' where BillCode='+#39+OrderCode+#39;
      SQLQuery.ExecSQL;
      SQLQuery.Close;
    end;
  end;
end;

procedure SetBillResInfo(OrderCode, BillTableName : string; Cds : TSimpleDataSet); //更新单据退货数量已执行的数据
var
  sql: string;
begin
  with dmData do
  begin
    while not Cds.Eof do
    begin
      Sql:='select BillCode, ID, Quantity, ReimburseQry From '+BillTableName+
           ' where BillCode='+#39+OrderCode+#39+
           ' and ID='+#39+Cds.Fieldbyname('OrderID').asstring+#39;
      OpenData(Sql, sdsPublic);
      if not sdsPublic.IsEmpty then
      begin
        sdsPublic.Edit;
        //如果数量大于订货数量
        if Cds.FieldByName('Quantity').asfloat>
           sdsPublic.FieldByName('Quantity').asfloat-
           sdsPublic.FieldByName('ReimburseQry').asfloat then
          sdsPublic.FieldByName('ReimburseQry').asfloat:=
          sdsPublic.FieldByName('Quantity').asfloat
        else
          sdsPublic.FieldByName('ReimburseQry').asfloat:=
          sdsPublic.FieldByName('ReimburseQry').asfloat+
          Cds.FieldByName('Quantity').asfloat;
        sdsPublic.ApplyUpdates(-1);
      end;
      Cds.Next;
    end;
  end;
end;

procedure PrintView(FileName :string; Report :TfrReport);
begin
  if not Assigned(FrmPrintView) then
  FrmPrintView:=TFrmPrintView.Create(Application);
  Report.LoadFromFile(ReportPath+FileName);
  Report.Preview:=FrmPrintView.RepPreview;
  Report.PrepareReport;
  Report.ShowPreparedReport;
  FrmPrintView.Show;
end;

procedure UserLogonkey(Frm: TForm; FrmName: string);
var
 i, j: Integer;
 s: string;
begin
  if isLogon=False then
  begin
    Application.Terminate;
  end;
  j:=0;
  with Frm do
  begin
    for i:=0 to ComponentCount -1 do
    begin
      if  (Components[i] is TAction) then
      if TAction(Components[i]).Name='Action'+IntToStr(j) then
      begin
        s:=Copy(UserKey, j+1, 1);
        if s='1' then (Components[i] as TAction).Enabled:=True
        else  (Components[i] as TAction).Enabled:=False ;
        j:=j+1;
      end;
    end;
  end;  
end;


function isExist(TableName, KeyField, NewValue, OldValue, Value :string):Boolean;
var
  sql:string;
begin
  sql:='select '+KeyField+' From '+TableName+' where '+KeyField+'='+#39+NewValue+#39;
  if value<>'' then sql:=sql+value;
  OpenData(sql, dmData.sdsPublic);
  if not(dmData.sdsPublic.IsEmpty) and
    (dmData.sdsPublic.Fields[0].AsString<>OldValue) then
    Result:=True
  else
    Result:=False;
end;

function GetBillID(Ts :string; TD: string):string;
var
  Nows :string;
begin
  Nows:=FormatDateTime('yyyymmdd',StrToDate(TD));
  try
    with dmData.sdsPublic do
    begin
      Close;
      DataSet.CommandText:='select * from BillID where BillType='+#39+Ts+#39+' and BillDate='+#39+TD+#39;
      Open;
      if isEmpty then
      begin
        Result:='001';
        Append;
        Fieldbyname('BillNo').asinteger:=1;
        Fieldbyname('BillType').asstring:=Ts;
        Fieldbyname('BillDate').asstring:=TD;
        ApplyUpdates(-1);
      end
      else
      begin
        Result:=IntToStr(Fieldbyname('BillNo').asinteger+1);
        if Length(Result)=1 then
        Result:='00'+Result
        else
        if Length(Result)=2 then
        Result:='0'+Result;
        Edit;
        Fieldbyname('BillNo').asinteger:=Fieldbyname('BillNo').asinteger+1;
        ApplyUpdates(-1);
      end;
    Close;
    end;
  except
    Application.MessageBox('打开数据错误。',Errorinfo,$10);
    Abort;
  end;
  Result:=Nows+Result;
end;

function GetCostprice(Code: string): Double;
begin
  OpenData('Select COSTPRICE from Partinfo where Code='+#39+Code+#39,
          dmData.sdsPublic);
  Result:=dmData.sdsPublic.Fields[0].AsFloat; 
end;

end.