Next Chapter 56 Sales Register
PROJECT SOURCE CODE
SPONSORED LINKS
Sales Register
'******************************************************************
'******************************************************************
'** Author : Samee Ullah Siddiqui
'** Subject : Creating Sale Register
'** Date : Friday, September, 05, 2003
'** Modified : Saturday, September, 06, 2003
'******************************************************************
'******************************************************************
Private Sub CancelCmd_Click()
Unload Me
End Sub
Private Sub chkPartyWise_Click()
txtParty.Enabled = (chkPartyWise.Value = vbChecked)
End Sub
Private Sub chkSalesman_Click()
txtSalesman.Enabled = (chkSalesman.Value = vbChecked)
End Sub
Private Sub cmbCashCredit_Click()
chkPartyWise.Enabled = (cmbCashCredit.ListIndex = 1)
End Sub
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If Not KeyCode = vbKeyReturn Then Exit Sub
If Me.ActiveControl.Name = "txtParty" Then Exit Sub
If Me.ActiveControl.Name = "txtSalesman" Then Exit Sub
SendKeys "{TAB}"
End Sub
Private Sub Form_Load()
DtFrom = SelectedCompany.FromDate
DtTo = Date
'DEFAULT VALUE - ALL
cmbCashCredit.ListIndex = 2
cmbsale_return.ListIndex = 2
cmbTaxable.ListIndex = 2
With PartySrch
.DBConnectString = ConnectString
.SQLString = "SELECT ACCode, ACDESC,acalias FROM ACMAST WHERE RECTYPE = 'A' AND CASH = 'R'"
.WithAlias = True
Set .BoundTextBox = txtParty
.PopulateList
End With
With SalesManSrch
.DBConnectString = ConnectString
.SQLString = "SELECT ACCode, ACDESC FROM ACMAST WHERE GRCODE = '000029'" 'Salesman Code = 000029
Set .BoundTextBox = txtSalesman
.PopulateList
End With
End Sub
Private Sub OKCmd_Click()
On Error GoTo ErHand
Dim SQL, PartyType, SaleOrSaleReturn, Taxable, PartyCode, SalesMan As String
Dim ProdValue, DiscountValue, TaxValue, TotalValue, NetValue As Double
If (chkPartyWise.Value = vbChecked And cmbCashCredit.Text = "Credit") And txtParty.Tag = "" Then MsgBox "Please select party from party list.", vbCritical, "Missing Value": Exit Sub
If chkSalesman.Value = vbChecked And txtSalesman.Tag = "" Then MsgBox "Please select salesman from salesman list.", vbCritical, "Missing Value": Exit Sub
'Decide Party Type
If cmbCashCredit.Text = "Cash" Then
PartyType = " AND (LEFT(SALE.VNO,1) ='C')"
ElseIf cmbCashCredit.Text = "Credit" Then
PartyType = " AND (LEFT(SALE.VNO,1) ='R')"
Else
PartyType = ""
End If
'Decide Sale Return
If cmbsale_return.Text = "Sale" Then
SaleOrSaleReturn = " AND (SALE.VTYPE='SV')"
ElseIf cmbsale_return.Text = "Sale Return" Then
SaleOrSaleReturn = " AND (SALE.VTYPE='SR')"
Else
SaleOrSaleReturn = ""
End If
'Decide Taxable or Non Taxable
If cmbTaxable.Text = "Taxable" Then
Taxable = " AND (NOT ISNULL(SALE.TAXPER) AND SALE.TAXPER <> 0)"
ElseIf cmbTaxable.Text = "Non Taxable" Then
Taxable = " AND (ISNULL(SALE.TAXPER) OR SALE.TAXPER = 0)"
Else
Taxable = ""
End If
PartyCode = IIf(chkPartyWise.Value = vbChecked And cmbCashCredit.Text = "Credit", " AND (SALE.ACCODE = '" & txtParty.Tag & "')", "")
SalesMan = IIf(chkSalesman.Value = vbChecked, " AND (SALE.SALESMANCODE = '" & txtSalesman.Tag & "')", "")
SQL = "SHAPE {SELECT SALE.ID, CASE WHEN SALE.VTYPE='SV' THEN 'Sale' ELSE 'Sale Return' END AS VOUCHERTYPE, SALE.VNO, SALE.VDATE, SALE.ACCODE AS PARTYCODE, SALE.ACDESC AS PARTYNAME, SALE.SALESMANCODE, ACMAST.ACDESC AS SALESMANNAME, (PROD_VALUE + DISCOUNT ) AS ProdValue, SALE.DISCPER, SALE.DISCOUNT, (PROD_VALUE + DISCOUNT ) - SALE.DISCOUNT AS TOTALAMT, SALE.TAXPER, SALE.TAXAMT AS TaxAmount, SALE.NET_PAYABLE, SALE.REMARK " & _
"FROM SALE LEFT JOIN ACMAST ON SALE.SALESMANCODE = ACMAST.ACCODE WHERE (SALE.VDATE BETWEEN '" & DtFrom & "' AND '" & DtTo & "')" & PartyType & SaleOrSaleReturn & Taxable & PartyCode & SalesMan & " ORDER BY SALE.VDATE, SALE.VNO;} " & _
"AS SaleRegisCmd COMPUTE SaleRegisCmd, SUM(SaleRegisCmd.'NET_PAYABLE') AS SubTotalNetPayable, SUM(SaleRegisCmd.'ProdValue') AS SubTotalProductValue, SUM(SaleRegisCmd.'DISCOUNT') AS SubTotalDiscount, SUM(SaleRegisCmd.'TaxAmount') AS SubTotalTaxAmount, SUM(SaleRegisCmd.'TOTALAMT') AS SumTotalTotal BY 'VoucherType'"
If DE.SaleRegisConn.State = adStateOpen Then DE.SaleRegisConn.Close
DE.SaleRegisConn.Open ShapeConnectString
If DE.rsSaleRegisCmd_Grouping.State = adStateOpen Then DE.rsSaleRegisCmd_Grouping.Close
DE.rsSaleRegisCmd_Grouping.Open SQL, DE.SaleRegisConn, adOpenStatic, adLockOptimistic
Set SaleRegisRpt.DataSource = DE
SaleRegisRpt.Sections("PageHeader").Controls("SalesmanLbl").Caption = IIf(chkSalesman.Value = vbChecked, "Salesman: " & txtSalesman, "")
SaleRegisRpt.Sections("PageHeader").Controls("SalesmanLbl").Visible = chkSalesman.Value = vbChecked
'Calculate Sale-Sale Return
If DE.rsSaleRegisCmd_Grouping.RecordCount > 0 Then
DE.rsSaleRegisCmd_Grouping.MoveFirst 'Sale Values
ProdValue = DE.rsSaleRegisCmd_Grouping("SubTotalProductValue").Value
DiscountValue = DE.rsSaleRegisCmd_Grouping("SubTotalDiscount").Value
TaxValue = DE.rsSaleRegisCmd_Grouping("SubTotalTaxAmount").Value
NetValue = DE.rsSaleRegisCmd_Grouping("SubTotalNetPayable").Value
TotalValue = DE.rsSaleRegisCmd_Grouping("SumTotalTotal").Value
If DE.rsSaleRegisCmd_Grouping.RecordCount = 2 Then
DE.rsSaleRegisCmd_Grouping.MoveNext 'Sale Return Values
ProdValue = ProdValue - DE.rsSaleRegisCmd_Grouping("SubTotalProductValue").Value
DiscountValue = DiscountValue - DE.rsSaleRegisCmd_Grouping("SubTotalDiscount").Value
TaxValue = TaxValue - DE.rsSaleRegisCmd_Grouping("SubTotalTaxAmount").Value
NetValue = NetValue - DE.rsSaleRegisCmd_Grouping("SubTotalNetPayable").Value
TotalValue = TotalValue - DE.rsSaleRegisCmd_Grouping("SumTotalTotal").Value
End If
SaleRegisRpt.Sections("ReportFooter").Controls("ProdLbl").Caption = Format(CStr(ProdValue), "#0.#0")
SaleRegisRpt.Sections("ReportFooter").Controls("DisLbl").Caption = Format(CStr(DiscountValue), "#0.#0")
SaleRegisRpt.Sections("ReportFooter").Controls("TaxLbl").Caption = Format(CStr(IIf(IsNull(TaxValue), 0, TaxValue)), "#0.#0")
SaleRegisRpt.Sections("ReportFooter").Controls("PayableLbl").Caption = Format(CStr(NetValue), "#0.#0")
SaleRegisRpt.Sections("ReportFooter").Controls("TotalLbl").Caption = Format(CStr(TotalValue), "#0.#0")
DE.rsSaleRegisCmd_Grouping.MoveFirst
End If
If Option1(0).Value Then SaleRegisRpt.Show Else SaleRegisRpt.PrintReport
ErHand:
ErrHandler "SaleRegisFrm.CmdPrint"
End Sub