Sales Comparison between an Year and Previous Years Based on Quantity Ordered

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...