admin.bas

This a complete inventory management system that performs the functions of Purchases, Sales and paym

Set rst = New ADODB.Recordset
rst.Open "select a_LastCRNNo from aCompanySetup where a_Sno=1", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        gNewCreditNote = CStr(Val(Null2Blank(rst("a_LastCRNNo"))) + 1)
    Else
        gNewCreditNote = "1"
    End If
    For i = 1 To 5 - Len(gNewCreditNote)
        gNewCreditNote = "0" & gNewCreditNote
    Next
    gNewCreditNote = "CR" & gNewCreditNote
    NewCreditNote = gNewCreditNote
End Function
Public Function NewJVNo() As Variant
Dim rst As ADODB.Recordset
Dim gNewJVNo As String
gNewJVNo = ""
Set rst = New ADODB.Recordset
rst.Open "select a_LastJVNo from aCompanySetup where a_Sno=1", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        gNewJVNo = CStr(Val(Null2Blank(rst("a_LastJVNo"))) + 1)
    Else
        gNewJVNo = "1"
    End If
    For i = 1 To 5 - Len(gNewJVNo)
        gNewJVNo = "0" & gNewJVNo
    Next
    gNewJVNo = "JV" & gNewJVNo
    NewJVNo = gNewJVNo
End Function

Public Function BRID2BRDebitAmt(vBRID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select sum(p_Debit) as Debit from t_Statement where p_BranchId='" & Trim(vBRID) & "'", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("Debit")) = False Then
            BRID2BRDebitAmt = Val(Null2Blank(rst("Debit")))
        Else
            BRID2BRDebitAmt = Val("0")
        End If
    Else
        BRID2BRDebitAmt = Val("0")
    End If
End Function

Public Function BRID2BRCreditAmt(vBRID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select sum(p_Credit) as Credit from t_Statement where p_BranchId='" & Trim(vBRID) & "'", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("Credit")) = False Then
            BRID2BRCreditAmt = Val(Null2Blank(rst("Credit")))
        Else
            BRID2BRCreditAmt = Val("0")
        End If
    Else
        BRID2BRCreditAmt = Val("0")
    End If
End Function

Public Function BRID2BRManager1(vBRID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select (r_BFName + ' ' + r_BLName) as ManagerName from aBranchManager where r_ID='" & Trim(vBRID) & "' and r_Type=1", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("ManagerName")) = False Then
            BRID2BRManager1 = Null2Blank(rst("ManagerName"))
        Else
            BRID2BRManager1 = ""
        End If
    Else
        BRID2BRManager1 = ""
    End If
End Function

Public Function BRID2BRManager2(vBRID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select (r_BFName + ' ' + r_BLName) as ManagerName from aBranchManager where r_ID='" & Trim(vBRID) & "' and r_Type=2", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("ManagerName")) = False Then
            BRID2BRManager2 = " / " & Null2Blank(rst("ManagerName"))
        Else
            BRID2BRManager2 = ""
        End If
    Else
        BRID2BRManager2 = ""
    End If
End Function
Public Function NewPmtRecNo() As Variant
    Dim rst As ADODB.Recordset
    Dim gNewPmtRecNo As String
    gNewPmtRecNo = ""
    Set rst = New ADODB.Recordset
    rst.Open "select a_LastPmtRecNo from aCompanySetup where a_Sno=1", CNimanager
    
    If Not rst.EOF Or Not rst.BOF Then
        gNewPmtRecNo = CStr(Val(rst("a_LastPmtRecNo")) + 1)
    Else
        gNewPmtRecNo = "1"
    End If
    
    For i = 1 To 5 - Len(gNewPmtRecNo)
        gNewPmtRecNo = "0" & gNewPmtRecNo
    Next
    gNewPmtRecNo = "RC" & gNewPmtRecNo
    NewPmtRecNo = gNewPmtRecNo
End Function

Public Function NewJVNoBrToBr() As Variant
    Dim rst As ADODB.Recordset
    Dim gNewJVNoBrToBr As String
    gNewJVNoBrToBr = ""
    Set rst = New ADODB.Recordset
    rst.Open "select a_LastJVNoBrToBr from aCompanySetup where a_Sno=1", CNimanager
    
    If Not rst.EOF Or Not rst.BOF Then
        gNewJVNoBrToBr = CStr(Val(rst("a_LastJVNoBrToBr")) + 1)
    Else
        gNewJVNoBrToBr = "1"
    End If
    
    For i = 1 To 5 - Len(gNewJVNoBrToBr)
        gNewJVNoBrToBr = "0" & gNewJVNoBrToBr
    Next
    gNewJVNoBrToBr = "JV" & gNewJVNoBrToBr
    NewJVNoBrToBr = gNewJVNoBrToBr
End Function


Public Function NewPmtMadeNo() As Variant
    Dim rst As ADODB.Recordset
    Dim gNewPmtMadeNo As String
    gNewPmtMadeNo = ""
    Set rst = New ADODB.Recordset
    rst.Open "select a_LastPmtMadeNo from aCompanySetup where a_Sno=1", CNimanager
    
    If Not rst.EOF Or Not rst.BOF Then
        gNewPmtMadeNo = CStr(Val(rst("a_LastPmtMadeNo")) + 1)
    Else
        gNewPmtMadeNo = "1"
    End If
    
    For i = 1 To 5 - Len(gNewPmtMadeNo)
        gNewPmtMadeNo = "0" & gNewPmtMadeNo
    Next
    gNewPmtMadeNo = "PY" & gNewPmtMadeNo
    NewPmtMadeNo = gNewPmtMadeNo
End Function

Public Function VNID2VNName(pVNId As Variant) As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "select * from aVendorMaster where r_ID='" & Trim(pVNId) & "'", CNimanager
If Not rst.EOF Or Not rst.BOF Then
    VNID2VNName = Null2Blank(Trim(rst("r_Name")))
Else
    VNID2VNName = ""
End If
End Function

Public Function GetVndCreditPeriod(pVId As Variant) As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
    rst.Open "select * from aManufacturer where m_ManId='" & pVId & "'", CNimanager
    If Not rst.EOF And Not rst.BOF Then
        GetVndCreditPeriod = Val(Null2Blank(rst("m_CreditPeriod")))
    Else
        GetVndCreditPeriod = 0
    End If
End Function

Public Sub SendVendorBill2Statement(vSNo As Variant)
    Dim rst As ADODB.Recordset
    Dim gQuery As String
    Set rst = New ADODB.Recordset
    rst.Open "select * from t_VendorBills where v_SNo=" & Val(vSNo), CNimanager
If Not rst.EOF And Not rst.BOF Then
    gQuery = "insert into t_VendorStatement(p_Date, p_VendorId, p_ByID, p_Credit, p_Debit, p_ChequeNo, p_Desc, p_Type, p_ByTransactionID) values" & _
    "('" & CDate(rst("v_DueDate")) & "','" & rst("v_VID") & "','HO'," & Val(rst("v_Amount")) & ",0,'" & rst("v_BillNo") & "','Bill due for payment, Bill creation Date : " & Format(CDate(rst("v_Date")), "dd-MMM-yyyy") & "','BIL','')"
    CNimanager.Execute (gQuery)
    gQuery = "Update t_VendorBills set v_IsCredited=1 where v_SNo=" & rst("v_SNo")
    CNimanager.Execute (gQuery)
End If
End Sub

Public Function GetProductStock(vPID As Variant) As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
    rst.Open "select * from aBookMaster where b_ID='" & vPID & "'", CNimanager
        If Not rst.EOF And Not rst.BOF Then
            GetProductStock = Val(Null2Blank(rst("b_Qty")))
        Else
            GetProductStock = Val("0")
        End If
End Function


Public Function VID2VBal(vVID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select m_OpBal as Bal from aManufacturer where m_ManId='" & Trim(vVID) & "'", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        VID2VBal = Val(Null2Blank(rst("Bal")))
    End If
End Function

Public Function VID2BRDebitAmt(vVID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select sum(p_Debit) as Debit from t_VendorStatement where p_VendorId='" & Trim(vVID) & "'", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("Debit")) = False Then
            VID2BRDebitAmt = Val(Null2Blank(rst("Debit")))
        Else
            VID2BRDebitAmt = Val("0")
        End If
    Else
        VID2BRDebitAmt = Val("0")
    End If
End Function

Public Function VID2BRCreditAmt(vVID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select sum(p_Credit) as Credit from t_VendorStatement where p_VendorId='" & Trim(vVID) & "'", CNimanager
    If Not rst.EOF Or Not rst.BOF Then
        If IsNull(rst("Credit")) = False Then
            VID2BRCreditAmt = Val(Null2Blank(rst("Credit")))
        Else
            VID2BRCreditAmt = Val("0")
        End If
    Else
        VID2BRCreditAmt = Val("0")
    End If
End Function

Public Function GetTaxPerc() As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "select distinct(r_Tax) from rptBill", CNimanager
    If Not rst.EOF And Not rst.BOF Then
        GetTaxPerc = Val(Null2Blank(rst("r_Tax")))
    Else
        getraxperc = 0
    End If
End Function

Public Function GetBRWeeklyNo(vBRID As Variant) As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select (isnull(r_LastBRWeeklyNo,0) + 1) as NewWeeklyNo from aBranchMaster where r_ID='" & Trim(vBRID) & "'", CNimanager
        If Not rst.EOF And Not rst.BOF Then
            GetBRWeeklyNo = Val(Null2Blank(rst("NewWeeklyNo")))
        Else
            GetBRWeeklyNo = 0
        End If
End Function

Public Function TRID2VNName(pTRId As Variant) As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "select * from aTransporter where t_Id='" & Trim(pTRId) & "'", CNimanager
If Not rst.EOF Or Not rst.BOF Then
    TRID2VNName = Null2Blank(Trim(rst("t_Name")))
Else
    TRID2VNName = ""
End If
End Function

Public Function GetLevelComPer(pLvl As Integer) As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
    rst.Open "select * from aCommissionSetup where M_Level=" & pLvl, CNimanager, 3, 1
    If Not rst.EOF And Not rst.BOF Then
        GetLevelComPer = Val(Null2Blank(rst("M_ComPer")))
    Else
        GetLevelComPer = 0
    End If
End Function

Public Function checkDateRange(pDate As Date) As Boolean
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select a_CurrYearFrom, a_CurrYearTo from acompanySetup where a_Sno=1", CNimanager, 3, 1
        If Not rst.EOF And Not rst.BOF Then
            If pDate >= CDate(rst("a_CurrYearFrom")) And pDate <= CDate(rst("a_CurrYearTo")) Then
                checkDateRange = True
            Else
                checkDateRange = False
            End If
        Else
            checkDateRange = False
        End If
rst.Close
Set rst = Nothing
End Function