datamodule.pas

电子充值系统:全球通的充值卡通过POS机传到销售点.

end;

procedure TDM.tbInputAllUserIDValidate(Sender: TField);
begin
  if tbInput.FieldByName('AllUserID').AsBoolean then
  begin
    tbInput.FieldByName('BeginUserID').AsString := '0000000000';
    tbInput.FieldByName('EndUserID').AsString := 'ZZZZZZZZZZ';
  end;
end;

procedure TDM.tbInputBeginProgramIDValidate(Sender: TField);
begin
  tbInput.FieldByName('BeginProgramName').AsString :=
          GetProgramName(tbInput.FieldByName('BeginProgramID').AsString);
end;

procedure TDM.tbInputEndProgramIDValidate(Sender: TField);
begin
  tbInput.FieldByName('EndProgramName').AsString :=
          GetProgramName(tbInput.FieldByName('EndProgramID').AsString);
end;

procedure TDM.tbInputAllProgramIDValidate(Sender: TField);
begin
  if tbInput.FieldByName('AllProgramID').AsBoolean then
  begin
    tbInput.FieldByName('BeginProgramID').AsString := '00000';
    tbInput.FieldByName('EndProgramID').AsString := 'ZZZZZ';
  end;
end;

procedure TDM.tbInputAllDepartmentIDValidate(Sender: TField);
begin
  if tbInput.FieldByName('AllDepartmentID').AsBoolean then
  begin
    tbInput.FieldByName('BeginDepartmentID').AsString := '0000';
    tbInput.FieldByName('EndDepartmentID').AsString := 'ZZZZ';
  end;
end;

procedure TDM.tbInputBeginDepartmentIDValidate(Sender: TField);
begin
  tbInput.FieldByName('BeginDepartmentName').AsString :=
          GetDepartmentName(tbInput.FieldByName('BeginDepartmentID').AsString);
end;

procedure TDM.tbInputEndDepartmentIDValidate(Sender: TField);
begin
  tbInput.FieldByName('EndDepartmentName').AsString :=
          GetDepartmentName(tbInput.FieldByName('EndDepartmentID').AsString);
end;

procedure TDM.tbInputDepartmentIDValidate(Sender: TField);
begin
  tbInput.FieldByName('DepartmentName').AsString :=
          GetDepartmentName(tbInput.FieldByName('DepartmentID').AsString);
end;

procedure TDM.AdjustDataBaseData(CompanyID,BeginPeriodDate,EndPeriodDate:String);
begin
  try
    //调整销售主表的合计金额与其明细表合计金额一致
    with DM.qyTemp1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select M.DeliveryID,MAX(M.AccountReceivable) AS AccountReceivable, ');
      SQL.Add('SUM(D.SalesAmount) AS SalesAmount ');
      SQL.Add('from DeliveryMaster M,DeliveryDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.DeliveryDate>=:BeginDeliveryDate ');
      SQL.Add('AND  M.DeliveryDate<=:EndDeliveryDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.DeliveryID=D.DeliveryID ');
      SQL.Add('AND  M.DeliveryProperty=''1'' ');
      SQL.Add('GROUP BY  M.CompanyID,M.DeliveryID ');
      SQL.Add('HAVING MAX(M.AccountReceivable)<= SUM(D.SalesAmount) - 0.005 ');
      SQL.Add('OR MAX(M.AccountReceivable)>= SUM(D.SalesAmount) + 0.005 ');
      SQL.Add('UNION ');
      SQL.Add('select M.DeliveryID,MAX(M.AccountReceivable) AS AccountReceivable, ');
      SQL.Add('SUM(D.SalesAmount) AS SalesAmount ');
      SQL.Add('from DeliveryMaster M,DeliveryDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.DeliveryDate>=:BeginDeliveryDate ');
      SQL.Add('AND  M.DeliveryDate<=:EndDeliveryDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.DeliveryID=D.DeliveryID ');
      SQL.Add('AND  M.DeliveryProperty=''2'' ');
      SQL.Add('GROUP BY  M.CompanyID,M.DeliveryID ');
      SQL.Add('HAVING MAX(M.AccountReceivable)<= SUM(-D.SalesAmount) - 0.005 ');
      SQL.Add('OR MAX(M.AccountReceivable)>= SUM(-D.SalesAmount) + 0.005 ');
      ParamByName('CompanyID').AsString := CompanyID;
      ParamByName('BeginDeliveryDate').AsString := BeginPeriodDate;
      ParamByName('EndDeliveryDate').AsString := EndPeriodDate;
      Open;
    end;
    DM.qyTemp1.First;
    while  not DM.qyTemp1.Eof do
    begin
      with DM.qyExecute do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE DeliveryMaster SET AccountReceivable=iif(DeliveryProperty=''1'',:SalesAmount, -:SalesAmount), ');
          SQL.Add('SubTotal=:SalesAmount, ');
          SQL.Add('Amount=:SalesAmount, ');
          SQL.Add('Sales=:SalesAmount ');
          SQL.Add('WHERE CompanyID= :CompanyID ');
          SQL.Add('AND DeliveryID=:DeliveryID ');
          ParamByName('CompanyID').AsString := CompanyID;
          ParamByName('SalesAmount').AsString := DM.qyTemp1.FieldByName('SalesAmount').AsString ;
          ParamByName('DeliveryID').AsString := DM.qyTemp1.FieldByName('DeliveryID').AsString ;
          ExecSQL;
        end;
        DM.qyTemp1.Next;
     end;

    //调整供应主表的合计金额与其明细表合计金额一致
    with DM.qyTemp1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select M.PurchaseID,MAX(M.AccountPayable) AS AccountPayable, ');
      SQL.Add('SUM(D.PurchaseAmount) AS PurchaseAmount ');
      SQL.Add('from PurchaseMaster M,PurchaseDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.PurchaseDate>=:BeginPurchaseDate ');
      SQL.Add('AND  M.PurchaseDate<=:EndPurchaseDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.PurchaseID=D.PurchaseID ');
      SQL.Add('AND  M.PurchaseProperty=''5'' ');
      SQL.Add('GROUP BY  M.CompanyID,M.PurchaseID ');
      SQL.Add('HAVING MAX(M.AccountPayable)<= SUM(D.PurchaseAmount) - 0.005 ');
      SQL.Add('OR MAX(M.AccountPayable)>= SUM(D.PurchaseAmount) + 0.005 ');
      SQL.Add('UNION ');
      SQL.Add('select M.PurchaseID,MAX(M.AccountPayable),SUM(D.PurchaseAmount) ');
      SQL.Add('from PurchaseMaster M,PurchaseDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.PurchaseDate>=:BeginPurchaseDate ');
      SQL.Add('AND  M.PurchaseDate<=:EndPurchaseDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.PurchaseID=D.PurchaseID ');
      SQL.Add('AND  M.PurchaseProperty=''6'' ');
      SQL.Add('GROUP BY  M.CompanyID,M.PurchaseID ');
      SQL.Add('HAVING MAX(M.AccountPayable)<= SUM(-D.PurchaseAmount) - 0.005 ');
      SQL.Add('OR MAX(M.AccountPayable)>= SUM(-D.PurchaseAmount) + 0.005 ');
      ParamByName('CompanyID').AsString := CompanyID;
      ParamByName('BeginPurchaseDate').AsString := BeginPeriodDate;
      ParamByName('EndPurchaseDate').AsString := EndPeriodDate;
      Open;
    end;
    DM.qyTemp1.First;
    while  not DM.qyTemp1.Eof do
    begin
      with DM.qyExecute do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE PurchaseMaster SET AccountPayable=iif(PurchaseProperty=''5'',:PurchaseAmount, -:PurchaseAmount), ');
          SQL.Add('SubTotal=:PurchaseAmount, ');
          SQL.Add('Amount=:PurchaseAmount ');
          SQL.Add('WHERE CompanyID= :CompanyID ');
          SQL.Add('AND PurchaseID=:PurchaseID ');
          ParamByName('CompanyID').AsString := CompanyID;
          ParamByName('PurchaseAmount').AsString := DM.qyTemp1.FieldByName('PurchaseAmount').AsString ;
          ParamByName('PurchaseID').AsString := DM.qyTemp1.FieldByName('PurchaseID').AsString ;
          ExecSQL;
        end;
        DM.qyTemp1.Next;
     end;

  //调整应付主表的合计金额与其明细表合计金额一致
    with DM.qyTemp1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select M.PaymentID,MAX(M.TotalBalance) AS TotalBalance, ');
      SQL.Add('SUM(D.Balance) AS Balance ');
      SQL.Add('from AccountPayableMaster M,AccountPayableDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.PayDate>=:BeginPayDate ');
      SQL.Add('AND  M.PayDate<=:EndPayDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.PaymentID=D.PaymentID ');
      SQL.Add('GROUP BY  M.CompanyID,M.PaymentID ');
      SQL.Add('HAVING MAX(M.TotalBalance)<= SUM(D.Balance) - 0.005 ');
      SQL.Add('OR MAX(M.TotalBalance)>= SUM(D.Balance) + 0.005 ');
      ParamByName('CompanyID').AsString := CompanyID;
      ParamByName('BeginPayDate').AsString := BeginPeriodDate;
      ParamByName('EndPayDate').AsString := EndPeriodDate;
      Open;
    end;
    DM.qyTemp1.First;
    while  not DM.qyTemp1.Eof do
    begin
      with DM.qyExecute do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE AccountPayableMaster SET TotalBalance=:Balance ');
          SQL.Add('WHERE CompanyID= :CompanyID ');
          SQL.Add('AND PaymentID=:PaymentID ');
          ParamByName('CompanyID').AsString := CompanyID;
          ParamByName('Balance').AsString := DM.qyTemp1.FieldByName('Balance').AsString ;
          ParamByName('PaymentID').AsString := DM.qyTemp1.FieldByName('PaymentID').AsString ;
          ExecSQL;
        end;
        DM.qyTemp1.Next;
     end;

  //调整应收主表的合计金额与其明细表合计金额一致
    with DM.qyTemp1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select M.ReceiveID,MAX(M.TotalBalance) AS TotalBalance, ');
      SQL.Add('SUM(D.Balance) AS Balance ');
      SQL.Add('from AccountReceivableMaster M,AccountReceivableDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.ReceiveDate>=:BeginReceiveDate ');
      SQL.Add('AND  M.ReceiveDate<=:EndReceiveDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.ReceiveID=D.ReceiveID ');
      SQL.Add('GROUP BY  M.CompanyID,M.ReceiveID ');
      SQL.Add('HAVING MAX(M.TotalBalance)<= SUM(D.Balance) - 0.005 ');
      SQL.Add('OR MAX(M.TotalBalance)>= SUM(D.Balance) + 0.005 ');
      ParamByName('CompanyID').AsString := CompanyID;
      ParamByName('BeginReceiveDate').AsString := BeginPeriodDate;
      ParamByName('EndReceiveDate').AsString := EndPeriodDate;
      Open;
    end;
    DM.qyTemp1.First;
    while  not DM.qyTemp1.Eof do
    begin
      with DM.qyExecute do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE AccountReceivableMaster SET TotalBalance=:Balance ');
          SQL.Add('WHERE CompanyID= :CompanyID ');
          SQL.Add('AND ReceiveID=:ReceiveID ');
          ParamByName('CompanyID').AsString := CompanyID;
          ParamByName('Balance').AsString := DM.qyTemp1.FieldByName('Balance').AsString ;
          ParamByName('ReceiveID').AsString := DM.qyTemp1.FieldByName('ReceiveID').AsString ;
          ExecSQL;
        end;
        DM.qyTemp1.Next;
     end;

    //调整产成品入库/领料主表的合计金额与其明细表合计金额一致
    with DM.qyTemp1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select M.MorvPickID,MAX(M.SubTotal) AS SubTotal, ');
      SQL.Add('SUM(D.mpAmount) AS mpAmount ');
      SQL.Add('from MorvPickMaster M,MorvPickDetail D ');
      SQL.Add('WHERE  M.CompanyID= :CompanyID ');
      SQL.Add('AND  M.MorvPickDate>=:BeginMorvPickDate ');
      SQL.Add('AND  M.MorvPickDate<=:EndMorvPickDate ');
      SQL.Add('AND  M.CompanyID=D.CompanyID ');
      SQL.Add('AND  M.MorvPickID=D.MorvPickID ');
      SQL.Add('GROUP BY  M.CompanyID,M.MorvPickID ');
      SQL.Add('HAVING MAX(M.SubTotal)<= SUM(D.mpAmount) - 0.005 ');
      SQL.Add('OR MAX(M.SubTotal)>= SUM(D.mpAmount) + 0.005 ');
      ParamByName('CompanyID').AsString := CompanyID;
      ParamByName('BeginMorvPickDate').AsString := BeginPeriodDate;
      ParamByName('EndMorvPickDate').AsString := EndPeriodDate;
      Open;
    end;
    DM.qyTemp1.First;
    while  not DM.qyTemp1.Eof do
    begin
      with DM.qyExecute do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE MorvPickMaster SET SubTotal=:mpAmount ');
          SQL.Add('WHERE CompanyID= :CompanyID ');
          SQL.Add('AND MorvPickID=:MorvPickID ');
          ParamByName('CompanyID').AsString := CompanyID;
          ParamByName('mpAmount').AsString := DM.qyTemp1.FieldByName('mpAmount').AsString ;
          ParamByName('MorvPickID').AsString := DM.qyTemp1.FieldByName('MorvPickID').AsString ;
          ExecSQL;
        end;
        DM.qyTemp1.Next;
     end;

    //MyInformation('初始化数据库完成！');
  except
    //MyError('初始化数据库失敗！');
    raise;
  end;
end;

procedure tdm.DataBaseUpdate();
begin

  {  with DM.qyExecute do
      begin
        Close;
        SQL.Clear;
        ExecSQL;
      end;
      DM.qyTemp1.Next;
   }
end;
end.