frmbasetonew.frm

金算盘软件代码

    strSql = "INSERT INTO " & mstrNewUser & ".TransVoucher SELECT * FROM " & mstrOldUser & ".TransVoucher"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".TransVoucherDetail"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".TransVoucherDetail SELECT * FROM " & mstrOldUser & ".TransVoucherDetail"
    gclsBase.ExecSQL strSql
    
    '收款单、付款单、应收单、应付单
    strSql = "DELETE FROM " & mstrNewUser & ".Activity"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".Activity SELECT * FROM " & mstrOldUser & ".Activity " _
        & "WHERE intYear*100+bytPeriod>=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".ActivityDetail"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".ActivityDetail SELECT ActivityDetail.* " _
        & "FROM " & mstrOldUser & ".ActivityDetail," & mstrOldUser & ".Voucher " _
        & "WHERE " & mstrOldUser & ".ActivityDetail.lngActivityID=" & mstrOldUser & ".Activity.lngActivityID " _
        & "intYear*100+bytPeriod>=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
    gclsBase.ExecSQL strSql
    ActivityDataToInit
    
    '(工资)
    strSql = "DELETE FROM " & mstrNewUser & ".Salary"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".Salary SELECT * FROM " & mstrOldUser & ".Salary"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".SalaryAccount"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".SalaryAccount SELECT * FROM " & mstrOldUser & ".SalaryAccount"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".SalaryField"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".SalaryField SELECT * FROM " & mstrOldUser & ".SalaryField"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".SalaryFormula"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".SalaryFormula SELECT * FROM " & mstrOldUser & ".SalaryFormula"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".SalaryList"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".SalaryList SELECT * FROM " & mstrOldUser & ".SalaryList"
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".SalaryListSet"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".SalaryListSet SELECT * FROM " & mstrOldUser & ".SalaryListSet"
    gclsBase.ExecSQL strSql
    
    '(固定资产)
    strSql = "DELETE FROM " & mstrNewUser & ".FixedAux"
    gclsBase.ExecSQL strSql
    strSql = "DELETE FROM " & mstrNewUser & ".FixedCost"
    gclsBase.ExecSQL strSql
    strSql = "DELETE FROM " & mstrNewUser & ".FixedDepartment"
    gclsBase.ExecSQL strSql
    strSql = "DELETE FROM " & mstrNewUser & ".FixedBalance"
    gclsBase.ExecSQL strSql
    strSql = "DELETE FROM " & mstrNewUser & ".FixedAlter"
    gclsBase.ExecSQL strSql
    strSql = "DELETE FROM " & mstrNewUser & ".FixedCard"
    gclsBase.ExecSQL strSql

    strSql = "DELETE FROM " & mstrNewUser & ".FixedDepr1"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".FixedDepr1 SELECT * FROM " & mstrOldUser & ".FixedDepr1 " _
        & "WHERE intYear*100+bytPeriod>=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
    gclsBase.ExecSQL strSql
    
    strSql = "DELETE FROM " & mstrNewUser & ".FixedDepr2"
    gclsBase.ExecSQL strSql
    strSql = "INSERT INTO " & mstrNewUser & ".FixedDepr2 SELECT * FROM " & mstrOldUser & ".FixedDepr2 " _
        & "WHERE intYear*100+bytPeriod>=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
    gclsBase.ExecSQL strSql
    
    
    strSql = "SELECT Card.lngFixedCardID,CreateAlter.lngFixedAlterID As lngCreateFixedAlterID,CreateAlter.strDate As strCreateDate," _
        & "RecentAlter.lngFixedAlterID As lngRecentFixedAlterID,RecentAlter.strDate As strRecentDate " _
        & "FROM " & mstrNewUser & ".FixedCard Card," & mstrOldUser & ".FixedAlter CreateAlter," & mstrOldUser & ".FixedAlter RecentAlter " _
        & "WHERE Card.lngRecentFixedAlterID=RecentAlter.lngFixedAlterID " _
        & "AND Card.lngCreateFixedAlterID=CreateAlter.lngFixedAlterID " _
        & "AND NOT(bytAlterType=2 AND strDate<'" & mstrTurnDate & "')"
    Set recDetail = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    Do While Not recDetail.EOF
        strSql = "INSERT " & mstrNewUser & ".FixedCard SELECT * FROM " & mstrOldUser _
            & ".FixedCard WHERE lngFixedCardID=" & recDetail!lngFixedCardID
        gclsBase.ExecSQL strSql
        strSql = "INSERT INTO " & mstrNewUser & ".FixedAux SELECT * FROM " & mstrOldUser & ".FixedAux " _
            & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID
        gclsBase.ExecSQL strSql
        
        If recDetail!strRecentDate < mstrTurnDate Then
            '变动日期 < 结转日期
            strSql = "UPDATE " & mstrNewUser & ".FixedCard " _
                & "SET lngRecentFixedAlterID=lngCreateFixedAlterID " _
                & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID
            gclsBase.ExecSQL strSql
            strSql = "INSERT " & mstrNewUser & ".FixedAlter SELECT * FROM " & mstrOldUser _
                & ".FixedAlter WHERE lngFixedAlterID=" & recDetail!lngCreateFixedAlterID
            gclsBase.ExecSQL strSql
            strSql = "SELECT * FROM " & mstrOldUser & ".FixedBalance " _
                & " WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & " AND intYear*100+bytPeriod<=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod _
                & " ORDER BY intYear DESC,bytPeriod DESC"
            Set recOther = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            If Not recOther.EOF Then
                If recOther!intYear <> mintTurnYear Or recOther!bytPeriod <> mbytTurnPeriod Then
                    strSql = "UPDATE " & mstrNewUser & ".FixedAlter " _
                        & "SET intYear=" & mintTurnYear & ",bytPeriod=" & mbytTurnPeriod & ",blnIsInit=1," _
                        & "dblAmount=" & recOther!dblInitAmount + recOther!dblDebitAmount - recOther!dblCreditAmount & "," _
                        & "dblDeprection=" & recOther!dblInitDeprection + recOther!dblDeprection + recOther!dblAlterDeprection & "," _
                        & "dblDebitAmount=0,dblCreditAmount=0,dblAlterDeprection=0 " _
                        & "WHERE lngFixedAlterID=" & recDetail!lngCreateFixedAlterID
                    gclsBase.ExecSQL strSql
                    strSql = "INSERT INTO " & mstrNewUser & ".FixedBalance" _
                        & "(lngFixedCardID,intYear,bytPeriod,dblInitAmount,dblInitDeprection," _
                        & "dblInitAccumaWork,dblCalcAmount,intBeelinePeriod,intCalcUseAge,intCalcPeriod) " _
                        & "VALUES(" & recDetail!lngFixedCardID & "," _
                        & mintTurnYear & "," & mbytTurnPeriod & "," _
                        & recOther!dblInitAmount + recOther!dblDebitAmount - recOther!dblCreditAmount & "," _
                        & recOther!dblInitDeprection + recOther!dblDeprection + recOther!dblAlterDeprection & "," _
                        & recOther!dblInitAccumaWork + recOther!dblWork & "," _
                        & recOther!dblCalcAmount & "," _
                        & recOther!intBeelinePeriod & "," _
                        & recOther!intCalcUseAge & "," _
                        & recOther!intCalcPeriod + recOther!intPeriod & ")"
                    gclsBase.ExecSQL strSql
                End If
            End If
            recOther.Close
            AddFixedOther recDetail!lngCreateFixedAlterID, recDetail!lngRecentFixedAlterID
        ElseIf recDetail!strCreateDate >= mstrTurnDate And recDetail!strRecentDate >= mstrTurnDate Then
            '变动日期 > 结转日期 AND 变动日期 < 结转日期
            strSql = "INSERT " & mstrNewUser & ".FixedAlter SELECT * FROM " & mstrOldUser & ".FixedAlter " _
                & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & "AND (lngFixedAlterID=" & recDetail!lngCreateFixedAlterID & " OR strDate>='" & mstrTurnDate & "')"
            gclsBase.ExecSQL strSql
            strSql = "SELECT * FROM " & mstrOldUser & ".FixedBalance " _
                & " WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & " AND intYear*100+bytPeriod<=" & CLng(mintTurnYear) * 100 + mbytTurnPeriod _
                & " ORDER BY intYear DESC,bytPeriod DESC"
            Set recOther = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            If Not recOther.EOF Then
                If recOther!intYear <> mintTurnYear Or recOther!bytPeriod <> mbytTurnPeriod Then
                    strSql = "UPDATE " & mstrNewUser & ".FixedAlter " _
                        & "SET intYear=" & mintTurnYear & ",bytPeriod=" & mbytTurnPeriod & ",blnIsInit=1," _
                        & "dblAmount=" & recOther!dblInitAmount + recOther!dblDebitAmount - recOther!dblCreditAmount & "," _
                        & "dblDeprection=" & recOther!dblInitDeprection + recOther!dblDeprection + recOther!dblAlterDeprection & "," _
                        & "dblDebitAmount=0,dblCreditAmount=0,dblAlterDeprection=0 " _
                        & "WHERE lngFixedAlterID=" & recDetail!lngCreateFixedAlterID
                    gclsBase.ExecSQL strSql
                    strSql = "INSERT INTO " & mstrNewUser & ".FixedBalance" _
                        & "(lngFixedCardID,intYear,bytPeriod,dblInitAmount,dblInitDeprection," _
                        & "dblInitAccumaWork,dblCalcAmount,intBeelinePeriod,intCalcUseAge,intCalcPeriod) " _
                        & "VALUES(" & recDetail!lngFixedCardID & "," _
                        & mintTurnYear & "," & mbytTurnPeriod & "," _
                        & recOther!dblInitAmount + recOther!dblDebitAmount - recOther!dblCreditAmount & "," _
                        & recOther!dblInitDeprection + recOther!dblDeprection + recOther!dblAlterDeprection & "," _
                        & recOther!dblInitAccumaWork + recOther!dblWork & "," _
                        & recOther!dblCalcAmount & "," _
                        & recOther!intBeelinePeriod & "," _
                        & recOther!intCalcUseAge & "," _
                        & recOther!intCalcPeriod + recOther!intPeriod & ")"
                    gclsBase.ExecSQL strSql
                End If
            End If
            recOther.Close
            strSql = "INSERT INTO " & mstrNewUser & ".FixedBalance SELECT * FROM " & mstrOldUser & ".FixedBalance " _
                & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & " AND intYear*100+bytPeriod>" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
            gclsBase.ExecSQL strSql
            AddFixedOther recDetail!lngCreateFixedAlterID, recDetail!lngRecentFixedAlterID
        Else
            '变动日期 > 结转日期
            strSql = "INSERT " & mstrNewUser & ".FixedAlter SELECT * FROM " & mstrOldUser & ".FixedAlter " _
                & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & "AND lngFixedAlterID=" & recDetail!lngCreateFixedAlterID
            gclsBase.ExecSQL strSql
            strSql = "INSERT INTO " & mstrNewUser & ".FixedBalance SELECT * FROM " & mstrOldUser & ".FixedBalance " _
                & "WHERE lngFixedCardID=" & recDetail!lngFixedCardID _
                & " AND intYear*100+bytPeriod>" & CLng(mintTurnYear) * 100 + mbytTurnPeriod
            gclsBase.ExecSQL strSql
            AddFixedOther
        End If
        recDetail.MoveNext
    Loop
    recDetail.Close
    
    Set recDetail = Nothing
    Set recOther = Nothing
End Function

Private Sub AddFixedOther(Optional lngChangeID As Long, Optional lngChangeToID As Long)
    Dim strSql As String
    Dim recDetail As rdoResultset
    Dim lngFixedAlterID As Long
    
    strSql = "DELETE FROM " & mstrNewUser & ".FixedAccount"
    gclsBase.ExecSQL strSql
    
    strSql = "SELECT lngFixedAlterID FROM " & mstrNewUser & ".FixedAlter"
    Set recDetail = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    Do While Not recDetail.EOF
        If recDetail!lngFixedAlterID = lngChangeID Then
            lngFixedAlterID = lngChangeToID
        Else
            lngFixedAlterID = recDetail!lngFixedAlterID
        End If
        strSql = "INSERT INTO " & mstrNewUser & ".FixedAccount SELECT * FROM " & mstrOldUser & ".FixedAccount " _
            & "WHERE lngFixedAlterID=" & lngFixedAlterID
        gclsBase.ExecSQL strSql
        strSql = "INSERT INTO " & mstrNewUser & ".FixedCost SELECT * FROM " & mstrOldUser & ".FixedCost " _
            & "WHERE lngFixedAlterID=" & lngFixedAlterID
        gclsBase.ExecSQL strSql
        strSql = "INSERT INTO " & mstrNewUser & ".FixedDepartment SELECT * FROM " & mstrOldUser & ".FixedDepartment " _
            & "WHERE lngFixedAlterID=" & lngFixedAlterID
        gclsBase.ExecSQL strSql
        recDetail.MoveNext
    Loop
    recDetail.Close
    Set recDetail = Nothing
    
    If lngChangeID > 0 And lngChangeToID > 0 Then
        strSql = "UPDATE " & mstrNewUser & ".FixedAccount SET lngFixedAlterID=" & lngChangeID _
            & " WHERE lngFixedAlterID=" & lngChangeToID
        gclsBase.ExecSQL strSql
        strSql = "UPDATE " & mstrNewUser & ".FixedDepartment SET lngFixedAlterID=" & lngChangeID _
            & " WHERE lngFixedAlterID=" & lngChangeToID
        gclsBase.ExecSQL strSql
        strSql = "UPDATE " & mstrNewUser & ".FixedCost SET lngFixedAlterID=" & lngChangeID _
            & " WHERE lngFixedAlterID=" & lngChangeToID
        gclsBase.ExecSQL strSql
    End If
End Sub

'应收应付期初数据的转入(来自业务与业务明细）
Private Function ActivityDataToInit() As Boolean
    Dim strSql As String
    Dim recDetail As rdoResultset
    Dim lngVoucherTypeID As Long
    Dim lngReceiptNo As Long
    Dim lngAutoID As Long
    
    strSql = "SELECT Max(lngARAPInitID) As lngID FROM " & mstrNewUser & ".ARAPInit1"
    Set recDetail = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    If Not recDetail.EOF Then
        lngAutoID = recDetail!lngID