gl_enter_monthcheck.pas

一个MRPII系统源代码版本

                +'                   end '
                +'         end      '
                +'         +case when KmProperty=1 then DebitBalance  '
                +'                           else -DebitBalance                   '
                +'           end                                       '
                +'         -case when KmProperty=1 then CreditBalance '
                +'                           else -CreditBalance                  '
                +'           end) as FirstBalance,                                      '
                +'         abs(case when KmProperty=1 then case when BalanceDirection=1 then FirstFBalance '
                +'                                          else -FirstFBalance '
                +'                                     end '
                +'              else case when BalanceDirection=2 then FirstFBalance '
                +'                        else -FirstFBalance '
                +'                   end '
                +'         end      '
                +'         +case when KmProperty=1 then FDebitBalance  '
                +'                           else -FDebitBalance                   '
                +'           end                                       '
                +'         -case when KmProperty=1 then FCreditBalance '
                +'                           else -FCreditBalance                  '
                +'           end) as FirstFBalance,                                      '
                +'        FirstAmountBalance+AmountBalance as FirstAmountBalance,                        '
                +'        0.0 as DebitBalance,0.0 as CreditBalance,0.0 as FDebitBalance,0.0 as FCreditBalance, '
                +'        0.0 as AmountBalance, case when Currency.IsMaster=1 then 1.0 else ExchRate.ExchRate  end  as FCurrencyRate                  '
                +'   Into #TmpBalance '
                +'   from Gl_AccountSubjectBalance '
                +'   join Gl_AccountSubject on Gl_AccountSubjectBalance.KmId=Gl_AccountSubject.KmId '
                +'   join Currency on Gl_AccountSubject.FCurrecy=Currency.CurrencyCode '
                +'   left join ExchRate on Gl_AccountSubject.FCurrecy=ExchRate.CurrencyCode '
                +'    and ExchRate.ExchMonth='+QuotedStr(NextMonth)  
                +'  where AccountPeriodYear='+Copy(Month,1,4)
                +'    and AccountPeriodMonth='+Copy(Month,6,2) ;
      ExecuteSql(AdoQry_Tmp,SqlText,1);
      SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then FirstBalance     '
                                   +'       When BalanceDirection=2 And KmProperty=1 Then -FirstBalance   '
                                   +'       When BalanceDirection=2 And KmProperty=2 Then -FirstBalance   '
                                   +'       When BalanceDirection=1 And KmProperty=2 Then FirstBalance    '
                                   +    ' end )                                                        '
                  +'   From #TmpBalance                                                   '
                  +'       Join Gl_AccountSubject ON #TmpBalance.KMID=Gl_AccountSubject.KMId '
                  +'   where #TmpBalance.kmid in (Select KmID       '
                  +'                         From Gl_AccountSubject              '
                  +'                         Where endkm=1 And kmType in (1))  ';
      ExecuteSql(AdoQry_Tmp,SqlText,0);
      Balance10 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
      //--负债类借贷方
      SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then -FirstBalance     '
                                   +'       When BalanceDirection=2 And KmProperty=1 Then FirstBalance   '
                                   +'       When BalanceDirection=2 And KmProperty=2 Then FirstBalance   '
                                   +'       When BalanceDirection=1 And KmProperty=2 Then -FirstBalance    '
                                   +    ' end )                                                        '
                  +'   From #TmpBalance                                                   '
                  +'       Join Gl_AccountSubject ON #TmpBalance.KMID=Gl_AccountSubject.KMId '
                  +'   where #TmpBalance.kmid in (Select KmID       '
                  +'                         From Gl_AccountSubject              '
                  +'                         Where endkm=1 And kmType in (2))  ';
      ExecuteSql(AdoQry_Tmp,SqlText,0);
      Balance20 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;

      //--权益类借贷方
      SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then -FirstBalance     '
                                   +'       When BalanceDirection=2 And KmProperty=1 Then FirstBalance   '
                                   +'       When BalanceDirection=2 And KmProperty=2 Then FirstBalance   '
                                   +'       When BalanceDirection=1 And KmProperty=2 Then -FirstBalance    '
                                   +    ' end )                                                        '
                  +'   From #TmpBalance                                                   '
                  +'       Join Gl_AccountSubject ON #TmpBalance.KMID=Gl_AccountSubject.KMId '
                  +'   where #TmpBalance.kmid in (Select KmID       '
                  +'                         From Gl_AccountSubject              '
                  +'                         Where endkm=1 And kmType in (3))  ';
      ExecuteSql(AdoQry_Tmp,SqlText,0);
      Balance30 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
      //--成本类借贷方
      SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then FirstBalance     '
                                   +'       When BalanceDirection=2 And KmProperty=1 Then -FirstBalance   '
                                   +'       When BalanceDirection=2 And KmProperty=2 Then -FirstBalance   '
                                   +'       When BalanceDirection=1 And KmProperty=2 Then FirstBalance    '
                                   +    ' end )                                                        '
                  +'   From #TmpBalance                                                   '
                  +'       Join Gl_AccountSubject ON #TmpBalance.KMID=Gl_AccountSubject.KMId '
                  +'   where #TmpBalance.kmid in (Select KmID       '
                  +'                         From Gl_AccountSubject              '
                  +'                         Where endkm=1 And kmType in (4))  ';
      ExecuteSql(AdoQry_Tmp,SqlText,0);
      Balance40 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;

      //--损益类借贷方
      SqlText := ' Select JAmount=Sum(Case When BalanceDirection=1 And KmProperty=1 Then -FirstBalance     '
                                   +'       When BalanceDirection=2 And KmProperty=1 Then FirstBalance   '
                                   +'       When BalanceDirection=2 And KmProperty=2 Then FirstBalance   '
                                   +'       When BalanceDirection=1 And KmProperty=2 Then -FirstBalance    '
                                   +    ' end )                                                        '
                  +'   From #TmpBalance                                                   '
                  +'       Join Gl_AccountSubject ON #TmpBalance.KMID=Gl_AccountSubject.KMId '
                  +'   where #TmpBalance.kmid in (Select KmID       '
                  +'                         From Gl_AccountSubject              '
                  +'                         Where endkm=1 And kmType in (5))  ';
      ExecuteSql(AdoQry_Tmp,SqlText,0);
      Balance50 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;

      //-----------------------------------------------------------------------
      If abs((Balance10+Balance40)-(Balance20+Balance30+Balance50))>0.000001 then 
      begin
        ExecuteSql(AdoQry_Tmp,' drop table #TmpBalance',1);
        DispInfo('试算不平衡，不能月结！',1);
        Abort;
      end;

      SqlText := 'insert into Gl_AccountSubjectBalance '
                +' (KmId,AccountPeriodYear,AccountPeriodMonth,BalanceDirection, '
                +'  FirstBalance, FirstFBalance,FirstAmountBalance,DebitBalance,'
                +'  CreditBalance,FDebitBalance,FCreditBalance,AmountBalance,FCurrencyRate,'
                +'  endFBalance,endBalance,endBalanceDirection )'
                +' select KmId,AccountPeriodYear,AccountPeriodMonth, '
                +'        BalanceDirection,FirstBalance,FirstFBalance,FirstAmountBalance,'
                +'        DebitBalance,CreditBalance,FDebitBalance,FCreditBalance,AmountBalance,'
                +'        FCurrencyRate,FirstFBalance,FirstBalance,BalanceDirection '
                +'  from #TmpBalance '
                +' drop table #TmpBalance ';
      ExecuteSql(AdoQry_Tmp,SqlText,1);
      SQlText := ' Update Gl_AccountPeriod '
                +'    set CurrentPeriod=0,FreezeMonth=1 '
                +'  where CurrentPeriod=1 '
                +' select * from Gl_AccountPeriod '
                +'  where AccountPeriodYear='+Copy(NextMonth,1,4)
                +'    and AccountPeriodMonth='+Copy(NextMonth,6,2)
                +'  If @@RowCount>0 '
                +'  update Gl_AccountPeriod '
                +'     set CurrentPeriod=1 '
                +'  where AccountPeriodYear='+Copy(NextMonth,1,4)
                +'    and AccountPeriodMonth='+Copy(NextMonth,6,2)
                +'  else '
                +'  insert Into Gl_Accountperiod(AccountPeriodYear,AccountPeriodMonth,'
                +'                               CurrentPeriod,FreezeMonth) '
                +'  Values('+Copy(NextMonth,1,4)+','
                +'         '+Copy(NextMonth,6,2)+','
                +'         1,0)';
      ExecuteSql(AdoQry_Tmp,SqlText,1)            ;
      Dbconnect.CommitTrans;
      Result := True;            
    Except
      If dbconnect.InTransaction then dbconnect.RollBackTrans;
      Result := False;
    end;
  Finally
    Screen.Cursor := CrDefault;
  end;
end;        

procedure TFrm_Gl_Enter_MonthCheck.Btn_CancelMonthCheckClick(
  Sender: TObject);
var Month :String;  //取消月结月份
    NextMonth : String;  //当前会计期间
begin
  inherited;
  If DispInfo('确认取消月结吗？',2)<>'y' then Exit;
  {取出要取消月结的月份}
  ExecuteSql(AdoQry_Tmp,'select * from Gl_AccountPeriod '
                      +'  where CurrentPeriod=1 ',0);
  Month := FormatDateTime('yyyy.mm',IncMonth(EnCodeDate(AdoQry_Tmp.fieldbyname('AccountPeriodYear').AsInteger,
                                               AdoQry_Tmp.fieldbyname('AccountPeriodMonth').AsInteger,
                                               1),-1)
                          );
  NextMonth := FormatDateTime('yyyy.mm',(EnCodeDate(AdoQry_Tmp.fieldbyname('AccountPeriodYear').AsInteger,
                                               AdoQry_Tmp.fieldbyname('AccountPeriodMonth').AsInteger,
                                               1))
                          );
  ExecuteSQl(AdoQry_Tmp,'select * from Gl_AccountSubjectBalance'
                       +' where AccountPeriodYear='+Copy(Month,1,4)
                       +'   and AccountPeriodMonth='+Copy(Month,6,2),
             0);
  IF AdoQry_Tmp.RecordCount<=0 then 
  begin
    DispInfo(Month+'月还没月结！',3);
    Abort;
  end;
  ExecuteSql(AdoQry_Tmp,' select * from Gl_Credence '
                      +'   where TallyFlag=1 '
                      +'     and DatePArt(yy,CredenceDate)='+Copy(NextMonth,1,4)
                      +'     and DatePArt(mm,CredenceDate)='+Copy(NextMonth,6,2)
                      ,0);
  If AdoQry_Tmp.RecordCount>0 then 
  begin
    DispInfo(Month+'下一个月有已过帐凭证，不能取消月结！',3);
    Abort;
  end;                      
  If CancelMonthSum(Month) then 
  begin
    DispInfo('取消月结完毕！',3);
    Abort;
  end
  else begin
    DispInfo('取消月结失败,请重试！',1);
    Btn_CancelMonthCheck.Enabled := False;
  end;
  
end;

function TFrm_Gl_Enter_MonthCheck.CancelMonthSum(Month: String): Boolean;
var SqlText : String;
    PriorMonth : String;
    NextMonth : String;
begin
  Result := False;
  NextMonth := FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(Month+'.01'),1));
                          
  Try
    Screen.Cursor := CrHourGlass;
    try
      Dbconnect.beginTrans;
      SqlText := 'delete from Gl_AccountSubjectBalance'
                +' where AccountPeriodYear='+Copy(NextMonth,1,4)
                +'   and AccountPeriodMonth='+Copy(NextMonth,6,2);
      ExecuteSql(AdoQry_Tmp,SqlTExt,1);
      PriorMonth := FormatDateTime('yyyy.mm',IncMonth(EnCodeDate(StrToInt(Copy(Month,1,4)),
                                                   StrToInt(Copy(Month,6,2)),
                                                   1),-1));

      SqlText := ' Update Gl_AccountPeriod'
                +'   set CurrentPeriod=0 '
                +'  from Gl_AccountPeriod '
                +'  where AccountPeriodYear='+Copy(NextMonth,1,4)
                +'    and AccountPeriodMonth='+Copy(NextMonth,6,2)
                +' Update Gl_AccountPeriod '
                +'    set CurrentPeriod=1,FreezeMonth=0 '
                +'  from Gl_AccountPeriod '
                +'  where AccountPeriodYear='+Copy(Month,1,4)
                +'    and AccountPeriodMonth='+Copy(Month,6,2);
      Executesql(AdoQry_Tmp,SqlText,1);
      dbconnect.CommitTrans;
      Result := True;
    Except
      If Dbconnect.InTransaction then Dbconnect.RollBackTrans;
      Result := False;
    end;
  Finally
    Screen.Cursor := CrDefault;
  end;  
end;
end.