frmcreatedatabasenewyear.frm

一个用VB写的财务软件源码

''''    "                      select @sikjqj,@cPzzl,@cPzbh,@dPzrq,0,0,null,null,0,null,null,null,null,0,null,null,null,@cFx,tcf_vouchersource.citemcode,cast((tcf_vouchersource.csymbol+'1') as decimal(15,2)) * @nje,0,-1,null,null from tcf_vouchersource,tcf_item where tcf_vouchersource.siyear=" & sNewYear & "  and tcf_vouchersource.citemcode=tcf_item.ctypecode+tcf_item.citemcode and tcf_vouchersource.ckmdm=@cKmdm and tcf_item.siyear=" & sNewYear & " and tcf_item.bflowin=@siFlowin" & vbCrLf & _
''''    "                  select @sikjqj= @sikjqj2" & vbCrLf & _
''''    "                  select @cPzzl= @cPzzl2" & vbCrLf & _
''''    "                  select @cPzbh = @cpzbh2" & vbCrLf & _
''''    "                  select @dPzrq = @dpzrq2"
''''
''''  sSql(22) = sSql(22) & " select @nJe =@nJe2 " & vbCrLf & _
''''    "                 select @cFx =@cFx2" & vbCrLf & _
''''    "                 select @cKmdm=@ckmdm2" & vbCrLf & _
''''    "                 select @cKmmc=@ckmmc2" & vbCrLf & _
''''    "             End" & vbCrLf & _
''''    "         End" & vbCrLf & _
''''    "         Else" & vbCrLf & _
''''    "        begin" & vbCrLf & _
''''    "            if @cFx='借'" & vbCrLf & _
''''    "            begin" & vbCrLf & _
''''    "                set @cFx='贷'" & vbCrLf & _
''''    "                set @siFlowin= 0" & vbCrLf & _
''''    "            End" & vbCrLf & _
''''    "            Else" & vbCrLf & _
''''    "            begin" & vbCrLf & _
''''    "                set @cFx='借'" & vbCrLf & _
''''    "                    set @siFlowin=1" & vbCrLf & _
''''    "            End" & vbCrLf & _
''''    "             insert into tcf_oneone" & sNewYear & "" & vbCrLf & _
''''    "                  select @sikjqj,@cPzzl,@cPzbh,@dPzrq,0,0,null,null,0,null,null,null,null,0,null,null,null,@cFx,tcf_vouchersource.citemcode,cast((tcf_vouchersource.csymbol+'1') as decimal(15,2)) * @nje,0,-1,null,null from tcf_vouchersource,tcf_item where tcf_vouchersource.siyear=" & sNewYear & "  and tcf_vouchersource.citemcode=tcf_item.ctypecode+tcf_item.citemcode and tcf_vouchersource.ckmdm=@cKmdm and tcf_item.siyear=" & sNewYear & " and tcf_item.bflowin=@siFlowin" & vbCrLf & _
''''    "             select @sikjqj= @sikjqj2" & vbCrLf & _
''''    "             select @cPzzl =@cpzzl2" & vbCrLf & _
''''    "             select @cPzbh = @cpzbh2" & vbCrLf & _
''''    "             select @dPzrq = @dpzrq2" & vbCrLf & _
''''    "             select @nJe =@nJe2 " & vbCrLf
''''
''''                 sSql(22) = sSql(22) & "select @cFx =@cFx2" & vbCrLf & _
''''    "            select @cKmdm=@ckmdm2" & vbCrLf & _
''''    "            select @cKmmc=@cKmmc2" & vbCrLf & _
''''    "        End" & vbCrLf & _
''''    "        End" & vbCrLf & _
''''    "   End" & vbCrLf & _
''''    "   if @@error<>0" & vbCrLf & _
''''    "   select @cPzbh" & vbCrLf & _
''''    "   Close mycur" & vbCrLf & _
''''    "   deallocate mycur"
''''
''''        Case "ORACLE"
''''        sSql(22) = "create or replace procedure sp_cf_auto" & sNewYear & " " & vbCrLf & _
''''    "   (ProcDate in Varchar2,EndDate in Varchar2)    " & vbCrLf & _
''''    "   AS " & vbCrLf & _
''''    "   sikjqj smallint; " & vbCrLf & _
''''    "   cPzzl varchar(4); " & vbCrLf & _
''''    "   cPzbh char(4); " & vbCrLf & _
''''    "   dPzrq date; " & vbCrLf & _
''''    "   nJe   decimal(15,2); " & vbCrLf & _
''''    "   cFx char(2); " & vbCrLf & _
''''    "   cKmdm char(40); " & vbCrLf & _
''''    "   ckmmc varchar(40); " & vbCrLf & _
''''    "   sikjqj2 smallint; " & vbCrLf & _
''''    "   cPzzl2 varchar(4); " & vbCrLf & _
''''    "   cPzbh2 char(4); " & vbCrLf & _
''''    "   dPzrq2 date; " & vbCrLf & _
''''    "   nJe2   decimal(15,2); " & vbCrLf & _
''''    "   cFx2 char(2); " & vbCrLf & _
''''    "   cKmdm2 char(40); " & vbCrLf & _
''''    "   ckmmc2 varchar(40); " & vbCrLf & _
''''    "   siFlowin smallint; " & vbCrLf & _
''''    "   CURSOR Pz_cur  IS SELECT pz.kjqj kjqj,pz.Pzzl pzzl,pz.pzbh pzbh,pz.pzrq pzrq, " & vbCrLf & _
''''    "              pz.je je,pz.fx fx,pz.kmdm kmdm,km.kmmc kmmc " & vbCrLf & _
''''    "       FROM tZW_Pzsj" & sNewYear & " Pz, tzw_km" & sNewYear & " Km " & vbCrLf
''''
''''    sSql(22) = sSql(22) & " WHERE pz.bcashflow = - 1 AND pz.bspecial = 0 AND bSplitRec=0 AND  " & vbCrLf & _
''''    "             rtrim(km.kmdm,' ') = rtrim(pz.kmdm,' ') AND km.isxjllkm = 0 AND " & vbCrLf & _
''''    "             to_char(Pz.pzrq,'yyyy-mm-dd')>=ProcDate AND to_char(Pz.pzrq,'yyyy-mm-dd')<= EndDate " & vbCrLf & _
''''    "       ORDER BY pz.kjqj, pz.pzzl, pz.pzbh, pz.je DESC; " & vbCrLf & _
''''    "   v_pz Pz_cur%ROWTYPE; " & vbCrLf & _
''''    "   BEGIN " & vbCrLf & _
''''    "        OPEN Pz_cur; " & vbCrLf & _
''''    "       FETCH  Pz_cur INTO v_pz; " & vbCrLf & _
''''    "       sikjqj:=v_pz.kjqj; " & vbCrLf & _
''''    "       cpzzl:=v_pz.pzzl; " & vbCrLf & _
''''    "       cpzbh:=v_pz.pzbh; " & vbCrLf & _
''''    "       dpzrq:=v_pz.pzrq; " & vbCrLf & _
''''    "       nje:=v_pz.je; " & vbCrLf & _
''''    "       cfx:=v_Pz.fx; " & vbCrLf & _
''''    "       ckmdm:=v_pz.kmdm; " & vbCrLf & _
''''    "       ckmmc:=v_pz.kmmc; " & vbCrLf & _
''''    "       WHILE Pz_cur%FOUND LOOP " & vbCrLf & _
''''    "           FETCH pz_cur INTO v_pz; " & vbCrLf & _
''''    "           IF Pz_cur%notfound THEN " & vbCrLf & _
''''    "               IF cFx='借'THEN " & vbCrLf & _
''''    "                   cFx:='贷'; " & vbCrLf & _
''''    "                   siFlowin:= 0; " & vbCrLf & _
''''    "               Else " & vbCrLf
''''
''''                 sSql(22) = sSql(22) & " cFx:='贷'; " & vbCrLf & _
''''    "                   siFlowin:= 1; " & vbCrLf & _
''''    "               END IF; " & vbCrLf & _
''''    "               insert into tcf_oneone" & sNewYear & " " & vbCrLf & _
''''    "                  select sikjqj,cPzzl,cPzbh,dPzrq,0,0,null,null,0,null,null,null,null,0,null,null,null," & vbCrLf & _
''''    "                  cFx,tcf_source.ctypecode || tcf_source.citemcode," & vbCrLf & _
''''    "                  to_number (tcf_source.csymbol||'1') * nje,0,-1," & vbCrLf & _
''''    "                  null,null" & vbCrLf & _
''''    "                  From tcf_source, tcf_item" & vbCrLf & _
''''    "                  Where tcf_source.siyear = " & sNewYear & "" & vbCrLf & _
''''    "                  and tcf_source.ctypecode=tcf_item.ctypecode" & vbCrLf & _
''''    "                  and instr(tcf_source.vcformula,rtrim(cKmdm,' ')||'='||rtrim(ckmmc,' '),1,1)>0" & vbCrLf & _
''''    "                  and tcf_source.citemcode=tcf_item.citemcode" & vbCrLf & _
''''    "                  and tcf_item.siyear=" & sNewYear & "" & vbCrLf & _
''''    "                  and tcf_item.bflowin=siFlowin" & vbCrLf & _
''''    "                  and tcf_source.vcGetMethod='凭证';" & vbCrLf
''''        sSql(22) = sSql(22) & _
''''    "           Else " & vbCrLf & _
''''    "               sikjqj2:=v_pz.kjqj; " & vbCrLf & _
''''    "               cpzzl2:=v_pz.pzzl; " & vbCrLf & _
''''    "               cpzbh2:=v_pz.pzbh; " & vbCrLf & _
''''    "               dpzrq2:=v_pz.pzrq; " & vbCrLf & _
''''    "               nje2:=v_pz.je; " & vbCrLf & _
''''    "               cFx2:=v_Pz.fx; " & vbCrLf & _
''''    "               ckmdm2:=v_pz.kmdm; " & vbCrLf & _
''''    "               ckmmc2:=v_pz.kmmc; " & vbCrLf & _
''''    "               If sikjqj = sikjqj2 And cPzzl = cpzzl2 And cPzbh = cPzbh2 Then " & vbCrLf & _
''''    "                   If cFx != cFx2 Then " & vbCrLf
''''
''''                    sSql(22) = sSql(22) & " nJe:=nJe-nJe2; " & vbCrLf & _
''''    "                   Else " & vbCrLf & _
''''    "                       IF cFx='借' THEN " & vbCrLf & _
''''    "                           cFx:='贷'; " & vbCrLf & _
''''    "                           siFlowin:= 0; " & vbCrLf & _
''''    "                       Else " & vbCrLf & _
''''    "                           cFx:='借'; " & vbCrLf & _
''''    "                           siFlowin:=1; " & vbCrLf & _
''''    "                       END IF; " & vbCrLf & _
''''    "                       insert into tcf_oneone" & sNewYear & " " & vbCrLf
''''                sSql(22) = sSql(22) & _
''''      "                  select sikjqj,cPzzl,cPzbh,dPzrq,0,0,null,null,0,null,null,null,null,0,null,null,null," & vbCrLf & _
''''    "                    cFx,tcf_source.ctypecode || tcf_source.citemcode," & vbCrLf & _
''''    "                    to_number (tcf_source.csymbol||'1') * nje,0,-1," & vbCrLf & _
''''    "                    null,null" & vbCrLf & _
''''    "                    From tcf_source, tcf_item" & vbCrLf & _
''''    "                    Where tcf_source.siyear = " & sNewYear & "" & vbCrLf & _
''''    "                    and tcf_source.ctypecode=tcf_item.ctypecode" & vbCrLf & _
''''    "                    and instr(tcf_source.vcformula,rtrim(cKmdm,' ')||'='||rtrim(ckmmc,' '),1,1)>0" & vbCrLf & _
''''    "                    and tcf_source.citemcode=tcf_item.citemcode" & vbCrLf & _
''''    "                    and tcf_item.siyear=" & sNewYear & "" & vbCrLf & _
''''    "                    and tcf_item.bflowin=siFlowin" & vbCrLf & _
''''    "                    and tcf_source.vcGetMethod='凭证';" & vbCrLf & _
''''    "                           sikjqj:=sikjqj2; " & vbCrLf & _
''''    "                           cPzzl:=cPzzl2; " & vbCrLf & _
''''    "                           cPzbh:=cpzbh2; " & vbCrLf & _
''''    "                           dPzrq:=dpzrq2; " & vbCrLf & _
''''    "                           nJe:=nJe2; " & vbCrLf
''''
''''                        sSql(22) = sSql(22) & " cFx:=cFx2; " & vbCrLf & _
''''    "                           cKmdm:=ckmdm2; " & vbCrLf & _
''''    "                           ckmmc:=ckmmc2; " & vbCrLf & _
''''    "                   END IF; " & vbCrLf & _
''''    "                  Else " & vbCrLf & _
''''    "                           IF cFx='借' THEN " & vbCrLf & _
''''    "                               cFx:='贷'; " & vbCrLf & _
''''    "                               siFlowin:= 0; " & vbCrLf & _
''''    "                           Else " & vbCrLf & _
''''    "                               cFx:='借'; " & vbCrLf & _
''''    "                               siFlowin:=1; " & vbCrLf & _
''''    "                           END IF; " & vbCrLf & _
''''    "                           insert into tcf_oneone" & sNewYear & " " & vbCrLf
''''            sSql(22) = sSql(22) & _
''''      "                  select sikjqj,cPzzl,cPzbh,dPzrq,0,0,null,null,0,null,null,null,null,0,null,null,null," & vbCrLf & _
''''    "                  cFx,tcf_source.ctypecode || tcf_source.citemcode," & vbCrLf & _
''''    "                  to_number (tcf_source.csymbol||'1') * nje,0,-1," & vbCrLf & _
''''    "                  null,null" & vbCrLf & _
''''    "                  From tcf_source, tcf_item" & vbCrLf & _
''''    "                  Where tcf_source.siyear = " & sNewYear & "" & vbCrLf & _
''''    "                  and tcf_source.ctypecode=tcf_item.ctypecode" & vbCrLf & _
''''    "                  and instr(tcf_source.vcformula,rtrim(cKmdm,' ')||'='||rtrim(ckmmc,' '),1,1)>0" & vbCrLf & _
''''    "                  and tcf_source.citemcode=tcf_item.citemcode" & vbCrLf & _
''''    "                  and tcf_item.siyear=" & sNewYear & "" & vbCrLf & _
''''    "                  and tcf_item.bflowin=siFlowin" & vbCrLf & _
''''    "                  and tcf_source.vcGetMethod='凭证';" & vbCrLf & _
''''    "                           sikjqj:=sikjqj2; " & vbCrLf & _
''''    "                           cPzzl:=cPzzl2; " & vbCrLf
''''
''''                        sSql(22) = sSql(22) & " cPzbh:=cpzbh2; " & vbCrLf & _
''''    "                           dPzrq:=dpzrq2; " & vbCrLf & _
''''    "                           nJe:=nJe2; " & vbCrLf & _
''''    "                           cFx:=cFx2; " & vbCrLf & _
''''    "                           cKmdm:=ckmdm2; " & vbCrLf & _
''''    "                           ckmmc:=ckmmc2; " & vbCrLf & _
''''    "               END IF; " & vbCrLf & _
''''    "           END IF; " & vbCrLf & _
''''    "       END LOOP; " & vbCrLf & _
''''    "       CLOSE Pz_cur; " & vbCrLf & _
''''    "       UPDATE tzw_pzsj" & sNewYear & " set bsplitRec=2 WHERE bcashflow = - 1 AND bspecial = 0 " & vbCrLf & _
''''    "                  AND  to_char(pzrq,'yyyy-mm-dd')>=ProcDate AND to_char(pzrq,'yyyy-mm-dd')<= EndDate; " & vbCrLf & _
''''    "       commit; " & vbCrLf & _
''''    "   END; "
''''
''''    End Select
'''''****************** end 第一存储过程
''''
'''''****************** add 第二存储过程
''''Select Case g_FLAT
''''       Case "SQL"
''''sSql(23) = "CREATE PROCEDURE sp_cf_SplitOne" & sNewYear & " " & vbCrLf & _
''''    "   @kjqj smallint, " & vbCrLf & _
''''    "   @pzzl char(4), " & vbCrLf & _
''''    "   @pzbh char(4) " & vbCrLf & _
''''    "   AS " & vbCrLf & _
''''    "   declare @sikjqj smallint " & vbCrLf & _
''''    "   declare @cPzzl varchar(4) " & vbCrLf & _
''''    "   declare @cPzbh char(4) " & vbCrLf & _
''''    "   declare @dPzrq datetime " & vbCrLf & _
''''    "   declare @nJe   decimal(15,2) " & vbCrLf & _
''''    "   declare @cFx char(2) " & vbCrLf & _
''''    "   declare @cKmdm char(40) " & vbCrLf & _
''''    "   declare @cKmmc char(100) " & vbCrLf & _
''''    "   declare @siSubid smal