Sales Comparison between an Year and
Previous Years Based on Quantity Ordered
Compare the SalesQty of an year with the previous year
Qty.
So that create a Form with fields item
id,name,QtyOrdered,QtyOrderedPrv1,QtyOrderedPrv2, QtyOrderedPrv3.
Add two controls “Month” and “Year”. Month
should show month lookup and Year should show a lookup of , years
from 2011-2014.
When you are selecting a month and year in the two
controls , it should show the Selected Year's month's Qty ordered
and previous year's same month's Qty Ordered.
Step1
Creat a new table with fields item
id,name,QtyOrdered,QtyOrderedPrv1,QtyOrderedPrv2, QtyOrderedPrv3.
Make it as a temporary table by giving “TableType:In
memory”
Step2
Add
the table as a new form's data sourse and add all the fields to a new
Group-->Grid
Add another Group and add two combo boxes Month and Year
Under a ButtonGroup create an OK button
Step3
Month combo box cotrol's lookup can be done by assigning
the property “EnumType : MonthsOfYeaR”.
For year lookup no EDT look up is available.So use a
custom look by wrting the following code in “init()” method of
the form.
public
void
init()
{
int
i,curyear;
;
super();
for(i=0;i<5;i++)
{
curyear
= 2010+i;
yearcomb.insert(int2str(curyear),i);
}
}
Step4
Get SalesQty,name
and item id, for the selected year and month into respective fields
QtyOrdered, item id,name in the temporary table
Then get the
Previous Yerars's only SalesQty for first selected itemid and
name(check “group by” in program) and assign them to
QtyOrderedPrv1,QtyOrderedPrv2, QtyOrderedPrv3 in the temporary
table.
For the above purposes write the following codes
1) under Form level methods
SalesLineTab
populateRecords(date
startDate,date
endDate)
{
SalesLine s1;
SalesLineTab
s2;
InventTable
i1;
int
i2;
while
select
i1
{
while
select
sum(QtyOrdered)
from
s1 group
by
ItemId,Name
where
s1.ItemId == i1.ItemId
&&
(s1.ConfirmedDlv >= startDate && s1.ConfirmedDlv <=
endDate)
{
s2.ItemId
= s1.ItemId;
s2.Name
= s1.Name;
s2.QtyOrdered
= s1.QtyOrdered;
s2.insert();
}
}
ttsBegin;
for(i2=0;i2<3;i2++)
{
startDate
= prevYr(startDate);
endDate
= prevYr(endDate);
while
select
forupdate
s2
{
while
select
sum(QtyOrdered)
from
s1
where
s1.ItemId == s2.ItemId
&&
(s1.ConfirmedDlv >= startDate && s1.ConfirmedDlv <=
endDate)
{
if(i2==0)
s2.QtyOrderedPrv1=s1.QtyOrdered;
if(i2==1)
s2.QtyOrderedPrv2=s1.QtyOrdered;
if(i2==2)
s2.QtyOrderedPrv3=s1.QtyOrdered;
s2.update();
}
}
}
ttsCommit;
return
s2;
2.under
clicked() method of the OK button[ to create startDate
and endDate
]
void
clicked()
{
int
b,y;
date
startDate1, endDate1;
switch(MonthEnum.selection())
{
case
MonthEnum.selection()==MonthsOfYear::January:
b
= enum2int(MonthsOfYear::January);
break;
case
MonthsOfYear::February:
b
= enum2int(MonthsOfYear::February);
break;
case
MonthsOfYear::March:
b
= enum2int(MonthsOfYear::March);
break;
case
MonthsOfYear::April:
b
= enum2int(MonthsOfYear::April);
break;
case
MonthsOfYear::May:
b
= enum2int(MonthsOfYear::May);
break;
case
MonthsOfYear::June:
b
= enum2int(MonthsOfYear::June);
break;
case
MonthsOfYear::July:
b
= enum2int(MonthsOfYear::July);
break;
case
MonthsOfYear::August:
b
= enum2int(MonthsOfYear::August);
break;
case
MonthsOfYear::September:
b
= enum2int(MonthsOfYear::September);
break;
case
MonthsOfYear::October:
b
= enum2int(MonthsOfYear::October);
break;
case
MonthsOfYear::November:
b
= enum2int(MonthsOfYear::November);
break;
case
MonthsOfYear::December:
b
= enum2int(MonthsOfYear::December);
break;
}
y
= str2int(YearComb.valueStr());
startDate1
= mkDate(1,b,y)
;
endDate1
= mkDate(31,b,y);
super();
SalesLineTab.setTmpData(element.populateRecords(startDate1,endDate1));
// to populate the records that updated to the
temporay table at run time
SalesLineTab_ds.executeQuery();
// refresh the data souese
}
Step5
Now
you open the form and select the year(2014) and month(january) from
lookups and Click the Ok button
Now
see the form...
