<% ' ' %> <% main() Sub main() If Not(SessionCheck()) Then Exit Sub End If Dim KanjyoCD(10) Dim KanjyoMeisyo(10) Dim TekiyoCD(10) Dim Tekiyo(10) Dim KarikataKingaku(10) Dim KashikataKingaku(10) Dim ii Dim jj Dim kk Dim ss Dim tt jj = 0 tt = 0 Dim Query, Content, Content2, Content3 If Request("KaisyaCD") <> "" Then KaisyaCD = Request("KaisyaCD") End If '総勘定元帳は全科目をそのまま '補助簿はチェック区分に該当する科目を集計 Dim TmpCnt '残高設定一括コピー用:会社CDがNULLならば会社一覧ループで処理する Dim ShijoCD ShijoCD = Request("ShijoCD") Dim HyojiFlg HyojiFlg = Request("HyojiFlg") If "" & HyojiFlg = "" Then HyojiFlg = "1" '表示フラグが無い場合は通常処理なので同時同業会社も含める End If Dim CopyMoto CopyMoto = Request("CopyMoto") Dim TmpGyoshu '会社絞込み用業種CD変数 If CopyMoto <> "" Then KaisyaCD = "" 'コピー元があればNULLにする Query = "exec SPM会社取得 '" & GB_SystemCD & "','" & EnshuCD & "','" & CopyMoto & "'" Else Query = "exec SPM会社取得 '" & GB_SystemCD & "','" & EnshuCD & "','" & KaisyaCD & "'" End If Set Content = OpenQuery(Query) If Not(Content.BOF Or Content.EOF) Then TmpGyoshu = Content.Fields("業種CD") End If '会社CDがNULLならば指定業種の会社一覧ループで処理する Query = "exec SPM会社一覧2 '" & GB_SystemCD & "', '" & EnshuCD & "', '" & KaisyaCD & "', '" & TmpGyoshu & "', " & HyojiFlg 'Response.Write(Query) 'Exit Sub Set Content2 = OpenQuery(Query) Do While Not(Content2.BOF Or Content2.EOF) If ShijoCD = "" Or "" & Content2.Fields("市場CD") = ShijoCD Then '市場絞込み Query = "exec SPK伝票_振替伝票取得 '" & GB_SystemCD & "','" & EnshuCD & "', '" & Content2.Fields("会社CD") & "', '0000000', '" & ConvKaisyaGyosyuKubun(Content2.Fields("会社CD")) & "'" 'Response.Write(Query) 'Exit Sub Set Content = OpenQuery(Query) If Not(Content.BOF Or Content.EOF) Then '勘定科目配列(総勘定元帳用) Dim KamokuCD Dim KariKingaku Dim KashiKingaku Dim TekiyoTmp Dim Taishaku Dim Zandaka ReDim KamokuCD(0) ReDim KariKingaku(0) ReDim KashiKingaku(0) ReDim TekiyoTmp(0) ReDim Taishaku(0) ReDim Zandaka(0) 'チェック区分配列(補助簿用) Dim CheckKubun Dim CKariKingaku Dim CKashiKingaku Dim CTekiyo Dim CTaishaku Dim CZandaka ReDim CheckKubun(0) ReDim CKariKingaku(0) ReDim CKashiKingaku(0) ReDim CTekiyo(0) ReDim CTaishaku(0) ReDim CZandaka(0) '配列を拡張する(同一科目・同一区分は1レコードに集計するため重複を排除) Dim SonzaiFlg Do While Not(Content.BOF Or Content.EOF) '勘定科目配列 SonzaiFlg = 0 For TmpCnt = 1 To UBound(KamokuCD) If "" & Content.Fields("勘定CD") = KamokuCD(TmpCnt) Then SonzaiFlg = 1 Exit For End If Next If SonzaiFlg = 0 Then ReDim PreServe KamokuCD(UBound(KamokuCD) + 1) 'KamokuCD配列をひとつ増やす ReDim PreServe KariKingaku(UBound(KamokuCD)) 'KamokuCD配列はすでに増えてるので以下は+1しない ReDim PreServe KashiKingaku(UBound(KamokuCD)) ReDim PreServe TekiyoTmp(UBound(KamokuCD)) ReDim PreServe Taishaku(UBound(KamokuCD)) ReDim PreServe Zandaka(UBound(KamokuCD)) KamokuCD(UBound(KamokuCD)) = "" & Content.Fields("勘定CD") KariKingaku(UBound(KamokuCD)) = 0 KashiKingaku(UBound(KamokuCD)) = 0 TekiyoTmp(UBound(KamokuCD)) = "" & Content.Fields("摘要CD") Taishaku(UBound(KamokuCD)) = "" & Content.Fields("貸借区分") Zandaka(UBound(KamokuCD)) = 0 End If 'チェック区分配列 If "" & Content.Fields("チェック区分") <> "" Then 'チェック区分の無い科目は除外 SonzaiFlg = 0 For TmpCnt = 1 To UBound(CheckKubun) If "" & Content.Fields("チェック区分") = CheckKubun(TmpCnt) Then '「当座預金出納帳・買掛金元帳・売掛金元帳以外」若しくは「当座預金出納帳・買掛金元帳・売掛金元帳で摘要CDが同じ」であれば存在 If (CheckKubun(TmpCnt) <> "AB" And CheckKubun(TmpCnt) <> "AC" And CheckKubun(TmpCnt) <> "AD") Or "" & Content.Fields("摘要CD") = CTekiyo(TmpCnt) Then SonzaiFlg = 1 Exit For End If End If Next If SonzaiFlg = 0 Then ReDim PreServe CheckKubun(UBound(CheckKubun) + 1) 'CheckKubun配列をひとつ増やす ReDim PreServe CKariKingaku(UBound(CheckKubun)) 'CheckKubun配列はすでに増えてるので以下は+1しない ReDim PreServe CKashiKingaku(UBound(CheckKubun)) ReDim PreServe CTekiyo(UBound(CheckKubun)) ReDim PreServe CTaishaku(UBound(CheckKubun)) ReDim PreServe CZandaka(UBound(CheckKubun)) CheckKubun(UBound(CheckKubun)) = "" & Content.Fields("チェック区分") CKariKingaku(UBound(CheckKubun)) = 0 CKashiKingaku(UBound(CheckKubun)) = 0 CTekiyo(UBound(CheckKubun)) = "" & Content.Fields("摘要CD") CTaishaku(UBound(CheckKubun)) = "" & Content.Fields("貸借区分") CZandaka(UBound(CheckKubun)) = 0 End If End If Content.MoveNext Loop Content.MoveFirst '集計する Do While Not(Content.BOF Or Content.EOF) '勘定科目ごとに集計 For TmpCnt = 1 To UBound(KamokuCD) If "" & Content.Fields("勘定CD") = KamokuCD(TmpCnt) Then '金額がnullの場合は計算しないこと! If "" & Content.Fields("借方金額") <> "" Then KariKingaku(TmpCnt) = KariKingaku(TmpCnt) + Content.Fields("借方金額") End If If "" & Content.Fields("貸方金額") <> "" Then KashiKingaku(TmpCnt) = KashiKingaku(TmpCnt) + Content.Fields("貸方金額") End If Exit For End If Next 'チェック区分ごとに集計 If "" & Content.Fields("チェック区分") <> "" Then 'チェック区分の無い科目は除外 For TmpCnt = 1 To UBound(CheckKubun) If "" & Content.Fields("チェック区分") = CheckKubun(TmpCnt) Then '「当座預金出納帳・買掛金元帳・売掛金元帳以外」若しくは「当座預金出納帳・買掛金元帳・売掛金元帳で摘要CDが同じ」であれば集計 If (CheckKubun(TmpCnt) <> "AB" And CheckKubun(TmpCnt) <> "AC" And CheckKubun(TmpCnt) <> "AD") Or "" & Content.Fields("摘要CD") = CTekiyo(TmpCnt) Then If "" & Content.Fields("摘要CD") <> "" Then CTekiyo(TmpCnt) = "" & Content.Fields("摘要CD") '摘要CDがNULLで無ければ此処で取得 End If '金額がnullの場合は計算しないこと! If "" & Content.Fields("借方金額") <> "" Then CKariKingaku(TmpCnt) = CKariKingaku(TmpCnt) + Content.Fields("借方金額") End If If "" & Content.Fields("貸方金額") <> "" Then CKashiKingaku(TmpCnt) = CKashiKingaku(TmpCnt) + Content.Fields("貸方金額") End If End If End If Next End If Content.MoveNext Loop '総勘定元帳挿入(勘定科目ごと) For TmpCnt = 1 To UBound(KamokuCD) '残高計算 If Taishaku(TmpCnt) = "D" Then Zandaka(TmpCnt) = KariKingaku(TmpCnt) - KashiKingaku(TmpCnt) Else Zandaka(TmpCnt) = KashiKingaku(TmpCnt) - KariKingaku(TmpCnt) End If If KariKingaku(TmpCnt) = 0 Then KariKingaku(TmpCnt) = "null" End If If KashiKingaku(TmpCnt) = 0 Then KashiKingaku(TmpCnt) = "null" End If Query = "exec SPK帳簿_期首残高挿入元帳 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Query = Query & ", '" & KamokuCD(TmpCnt) & "'" Query = Query & ", '" & GB_KaikeiKaishibi & "'" Query = Query & ", ''" '仕丁 'Query = Query & ", ''" '摘要CD(取引先) Query = Query & ", '" & TekiyoTmp(TmpCnt) & "'" '摘要CD(取引先) Query = Query & ", '期首繰越'" Query = Query & ", ''" '相手勘定CD Query = Query & ", " & KariKingaku(TmpCnt) Query = Query & ", " & KashiKingaku(TmpCnt) Query = Query & ", " & Taishaku(TmpCnt) Query = Query & ", " & Zandaka(TmpCnt) Set Content = OpenQuery(Query) Response.Write(KamokuCD(TmpCnt) & ":" & KariKingaku(TmpCnt) & ":" & KashiKingaku(TmpCnt) & ":" & Taishaku(TmpCnt) & ":" & Zandaka(TmpCnt) & "
") Next Dim AAKariKingaku Dim AAKashiKingaku Dim AAZandaka Dim ABKariKingaku Dim AEKariKingaku Dim AEKashiKingaku Dim AETekiyo Dim AEZandaka Dim AFKariKingaku Dim AFKashiKingaku Dim AFTekiyo Dim AFZandaka Dim AGKariKingaku Dim AGKashiKingaku Dim AGTekiyo Dim AGZandaka Dim AHKariKingaku Dim AHKashiKingaku Dim AHTekiyo Dim AHZandaka '残高に"null"を代入しておく AAKariKingaku = "null" AAKashiKingaku = "null" AAZandaka = "null" AEKariKingaku = "null" AEKashiKingaku = "null" AEZandaka = "null" AFKariKingaku = "null" AFKashiKingaku = "null" AFZandaka = "null" AGKariKingaku = "null" AGKashiKingaku = "null" AGZandaka = "null" AHKariKingaku = "null" AHKashiKingaku = "null" AHZandaka = "null" '当座預金出納帳・買掛金元帳・売掛金元帳は取引先単位のレコード管理であるため '補助簿期首残高を挿入した後に勘定残高設定で取引先を変更して再度補助簿期首残高の更新を行うと '変更前の取引先の期首残高レコードが残ってしまい重複するので事前に取引先を選ばず全削除する Query = "exec SPK補助簿_期首残高削除 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Set Content = OpenQuery(Query) For TmpCnt = 1 To UBound(CheckKubun) '残高計算 If CTaishaku(TmpCnt) = "D" Then CZandaka(TmpCnt) = CKariKingaku(TmpCnt) - CKashiKingaku(TmpCnt) Else CZandaka(TmpCnt) = CKashiKingaku(TmpCnt) - CKariKingaku(TmpCnt) End If '残高計算後に貸借金額が0のレコードををSQLクエリ用"null"文字列へ入れ替える If CKariKingaku(TmpCnt) = 0 Then CKariKingaku(TmpCnt) = "null" End If If CKashiKingaku(TmpCnt) = 0 Then CKashiKingaku(TmpCnt) = "null" End If Response.Write(CheckKubun(TmpCnt) & ":" & CKariKingaku(TmpCnt) & ":" & CKashiKingaku(TmpCnt) & ":" & CTaishaku(TmpCnt) & ":" & CZandaka(TmpCnt) & ":" & CTekiyo(TmpCnt) & "
") If CheckKubun(TmpCnt) = "AB" And CTekiyo(TmpCnt) <> "" Then '当座預金出納帳挿入 If CopyMoto <> "" Then '残高一括コピーモードの際は当該会社が所持している先頭の当座口座に置き換える Query = "exec SPB銀行口座一覧 '" & GB_SystemCD & "', '" & EnshuCD & "', '" & Content2.Fields("会社CD") & "', '', 'T'" Set Content = OpenQuery(Query) If Not(Content.BOF Or Content.EOF) Then CTekiyo(TmpCnt) = "" & Content.Fields("銀行CD") End If End If Query = "exec SPK補助簿_期首残高挿入当座預金出納帳 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Query = Query & ", '" & GB_KaikeiKaishibi & "'" Query = Query & ", '期首繰越'" Query = Query & ", '" & CTekiyo(TmpCnt) & "'" Query = Query & ", ''" '小切手NO Query = Query & ", " & CKariKingaku(TmpCnt) Query = Query & ", " & CKashiKingaku(TmpCnt) Query = Query & ", '" & CTaishaku(TmpCnt) & "'" Query = Query & ", " & CZandaka(TmpCnt) Set Content = OpenQuery(Query) End If If CheckKubun(TmpCnt) = "AC" And CTekiyo(TmpCnt) <> "" Then '売掛金元帳挿入 Query = "exec SPK補助簿_期首残高挿入売掛金元帳 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Query = Query & ", '" & GB_KaikeiKaishibi & "'" Query = Query & ", '期首繰越'" Query = Query & ", '" & CTekiyo(TmpCnt) & "'" Query = Query & ", " & CKariKingaku(TmpCnt) Query = Query & ", " & CKashiKingaku(TmpCnt) Query = Query & ", '" & CTaishaku(TmpCnt) & "'" Query = Query & ", " & CZandaka(TmpCnt) Set Content = OpenQuery(Query) End If If CheckKubun(TmpCnt) = "AD" And CTekiyo(TmpCnt) <> "" Then '買掛金元帳挿入 Query = "exec SPK補助簿_期首残高挿入買掛金元帳 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Query = Query & ", '" & GB_KaikeiKaishibi & "'" Query = Query & ", '期首繰越'" Query = Query & ", '" & CTekiyo(TmpCnt) & "'" Query = Query & ", " & CKariKingaku(TmpCnt) Query = Query & ", " & CKashiKingaku(TmpCnt) Query = Query & ", '" & CTaishaku(TmpCnt) & "'" Query = Query & ", " & CZandaka(TmpCnt) Set Content = OpenQuery(Query) End If 'その他は代入のみしておく If CheckKubun(TmpCnt) = "AA" Then AAKariKingaku = CKariKingaku(TmpCnt) AAKashiKingaku = CKashiKingaku(TmpCnt) AAZandaka = CZandaka(TmpCnt) End If If CheckKubun(TmpCnt) = "AE" Then AEKariKingaku = CKariKingaku(TmpCnt) AEKashiKingaku = CKashiKingaku(TmpCnt) AETekiyo = CTekiyo(TmpCnt) AEZandaka = CZandaka(TmpCnt) End If If CheckKubun(TmpCnt) = "AF" Then AFKariKingaku = CKariKingaku(TmpCnt) AFKashiKingaku = CKashiKingaku(TmpCnt) AFTekiyo = CTekiyo(TmpCnt) AFZandaka = CZandaka(TmpCnt) End If If CheckKubun(TmpCnt) = "AG" Then AGKariKingaku = CKariKingaku(TmpCnt) AGKashiKingaku = CKashiKingaku(TmpCnt) AGTekiyo = CTekiyo(TmpCnt) AGZandaka = CZandaka(TmpCnt) End If If CheckKubun(TmpCnt) = "AH" Then AHKariKingaku = CKariKingaku(TmpCnt) AHKashiKingaku = CKashiKingaku(TmpCnt) AHTekiyo = CTekiyo(TmpCnt) AHZandaka = CZandaka(TmpCnt) End If Next 'その他補助簿挿入 Query = "exec SPK補助簿_期首残高挿入 '" & GB_SystemCD & "'," Query = Query & " '" & EnshuCD & "'" Query = Query & ", '" & Content2.Fields("会社CD") & "'" Query = Query & ", '" & GB_KaikeiKaishibi & "'" Query = Query & ", '期首繰越'" '現金出納帳 Query = Query & ", " & AAKariKingaku Query = Query & ", " & AAKashiKingaku Query = Query & ", " & AAZandaka '受取手形記入帳 Query = Query & ", " & AEZandaka Query = Query & ", ''" '手形種類区分 Query = Query & ", ''" '手形番号 Query = Query & ", ''" '支払人 Query = Query & ", '" & AETekiyo & "'" '振出人または裏書人 Query = Query & ", null" '振出日 Query = Query & ", null" '満期日 Query = Query & ", ''" '支払場所 Query = Query & ", null" '顛末日付 Query = Query & ", ''" '顛末摘要 '支払手形記入帳 Query = Query & ", " & AFZandaka Query = Query & ", ''" '手形種類区分 Query = Query & ", ''" '手形番号 Query = Query & ", '" & AFTekiyo & "'" '受取人 Query = Query & ", ''" '振出人 Query = Query & ", null" '振出日 Query = Query & ", null" '満期日 Query = Query & ", ''" '支払場所 Query = Query & ", null" '顛末日付 Query = Query & ", ''" '顛末摘要 '売上帳 Query = Query & ", '" & AGTekiyo & "'" Query = Query & ", ''" '商品CD Query = Query & ", null" '数量 Query = Query & ", null" '単価 Query = Query & ", 0" '内訳金額(未使用フィールド) Query = Query & ", " & AGZandaka Query = Query & ", 0" '返品区分(通常) '仕入帳 Query = Query & ", '" & AHTekiyo & "'" Query = Query & ", ''" '商品CD Query = Query & ", null" '数量 Query = Query & ", null" '単価 Query = Query & ", 0" '内訳金額(未使用フィールド) Query = Query & ", " & AHZandaka Query = Query & ", 0" '返品区分(通常) 'Response.Write(Query) 'Exit Sub Set Content = OpenQuery(Query) '期首在庫の仕入売上伝票レコードから商品有高帳に転記 '※繰越商品仕訳 → TSK伝票仕入売上明細F:D5:0000000 Dim TmpCD TmpCD = 1 Dim TmpShohinCD TmpShohinCD = "" Query = "" Query = Query & " " Query = Query & " SELECT * " Query = Query & " FROM TSK伝票仕入売上明細F " Query = Query & " INNER JOIN TSK伝票F " Query = Query & " ON TSK伝票仕入売上明細F.システムCD = TSK伝票F.システムCD " Query = Query & " AND TSK伝票仕入売上明細F.演習CD = TSK伝票F.演習CD " Query = Query & " AND TSK伝票仕入売上明細F.会社CD = TSK伝票F.会社CD " Query = Query & " AND TSK伝票仕入売上明細F.伝票区分 = TSK伝票F.伝票区分 " Query = Query & " AND TSK伝票仕入売上明細F.伝票番号 = TSK伝票F.伝票番号 " Query = Query & " WHERE TSK伝票仕入売上明細F.システムCD = '" & GB_SystemCD & "' " Query = Query & " AND TSK伝票仕入売上明細F.演習CD = '" & EnshuCD & "' " Query = Query & " AND TSK伝票仕入売上明細F.会社CD = '" & Content2.Fields("会社CD") & "' " Query = Query & " AND TSK伝票仕入売上明細F.伝票区分 = 'D5' " Query = Query & " AND TSK伝票仕入売上明細F.伝票番号 = '0000000' " Query = Query & " ORDER BY TSK伝票仕入売上明細F.商品CD " Query = Query & " , TSK伝票仕入売上明細F.仕入売上明細SEQ " Set Content = OpenQuery(Query) Do While Not(Content.BOF Or Content.EOF) If "" & Content.Fields("受注数量") <> "" And "" & Content.Fields("受注単価") <> "" Then If TmpShohinCD = Content.Fields("商品CD") Then TmpCD = TmpCD - 1 'マイナス採番 Else TmpCD = 0 TmpShohinCD = Content.Fields("商品CD") End If Query = "" Query = Query & " " Query = Query & " DELETE FROM TSK補助簿_商品有高帳F " Query = Query & " WHERE TSK補助簿_商品有高帳F.システムCD = '" & GB_SystemCD & "' " Query = Query & " AND TSK補助簿_商品有高帳F.演習CD = '" & EnshuCD & "' " Query = Query & " AND TSK補助簿_商品有高帳F.会社CD = '" & Content.Fields("会社CD") & "' " Query = Query & " AND TSK補助簿_商品有高帳F.商品CD = '" & Content.Fields("商品CD") & "' " Query = Query & " AND TSK補助簿_商品有高帳F.商品有高帳CD = " & TmpCD & " " Set Content3 = OpenQuery(Query) Query = "" Query = Query & " " Query = Query & " INSERT INTO TSK補助簿_商品有高帳F (システムCD, 演習CD, 会社CD, 商品CD, 商品有高帳CD, 商品有高帳計算区分, 取引日, 受払区分, 受入数量, 受入単価, 受入金額, 残高数量, 残高単価, 残高金額, 単位) " Query = Query & " VALUES ('" & GB_SystemCD & "', '" & EnshuCD & "', '" & Content.Fields("会社CD") & "', '" & Content.Fields("商品CD") & "', " & TmpCD & ", 'SS', '" & GB_KaikeiKaishibi & "', 'K', " & Content.Fields("受注数量") & ", " & Content.Fields("受注単価") & ", " & (Content.Fields("受注数量") * Content.Fields("受注単価")) & ", " & Content.Fields("受注数量") & ", " & Content.Fields("受注単価") & ", " & (Content.Fields("受注数量") * Content.Fields("受注単価")) & ", '') " Set Content3 = OpenQuery(Query) End If Content.MoveNext Loop If GB_Admin > 0 And Content2.Fields("学生番号") = "" Then '運用管理かつマスタ会社の場合は「MST:会社CD」で転記データを複製(SPM演習更新データコピーにおいてマスタとしてコピーされるデータ)(210616小野) Query = "exec SPM期首残高マスタ複製帳簿 '" & GB_SystemCD & "', '" & EnshuCD & "', '" & Content2.Fields("会社CD") & "' " Set Content = OpenQuery(Query) End If '仮 ' Query = "exec SPM記帳チェック挿入集計更新 '" & GB_SystemCD & "'," ' Query = Query & " '" & EnshuCD & "'," ' Query = Query & " '" & Content2.Fields("会社CD") & "'" ' Set Content = OpenQuery(Query) End If End If Content2.MoveNext Loop '会社一覧ループ終了 Response.Redirect("fk_kityochecksonyusyukeikoushin_bin.asp?KaisyaCD=" & KaisyaCD & "&ShijoCD=" & ShijoCD & "&CopyMoto=" & CopyMoto & "&HyojiFlg=" & HyojiFlg & "&Zandaka=tenki") End Sub %>