TransDate transDate = contract.parmTransDate();
FromDate FromWeek = contract.parmThisWeekFrom();
ToDate ToWeek = contract.parmThisWeekTo();
FromDate PrevFromWeek = contract.parmPrevWeekFrom();
ToDate PrevToWeek = contract.parmPrevWeekTo();
FromDate fromDate = dateStartMth(transDate);
ToDate toDate = transDate;
int noOfFridayCurrMth=0;
int noOfFridayPrevMth=0;
//Previous Month
FromDate startPrevMthDate =dateStartMth(prevMth(transDate));
ToDate endPrevMthDate =dateEndMth(prevMth(transDate));
//Current Month
FromDate startMthDate = dateStartMth(transDate);
ToDate endMthDate = dateEndMth(transDate);
HcmWorker hcmWorker;
smmSalesUnit smmSalesUnit;
DirPartyTable dirPartyTable;
smmSalesTarget smmSalesTarget;
smmSalesTargetTrans smmSalesTargetTrans;
while select SalesPersonWorker, count(RecId)
from smmSalesTarget
group by smmSalesTarget.SalesPersonWorker, smmSalesUnit.ParentId, dirPartyTable.Name
join ParentId, count(RecId)
from smmSalesUnit
where smmSalesUnit.SalesUnitId == smmSalesTarget.SalesUnitId
&& smmSalesUnit.Active == 1
join sum(AmountMST)
from smmSalesTargetTrans
where smmSalesTargetTrans.RefRecId == smmSalesTarget.RecId
&& smmSalesTargetTrans.TargetDate >= startPrevMthDate //Current Month Start Date
&& smmSalesTargetTrans.TargetDate <= endMthDate //Current Month End Date
join count(RecId)
from hcmWorker
where hcmWorker.RecId == smmSalesTarget.SalesPersonWorker
join Name, count(RecId)
from dirPartyTable
where dirPartyTable.RecId == hcmWorker.Person
{
tmpTable.initValue();
tmpTable.SalesPersonWorker = smmSalesTarget.SalesPersonWorker;
//tmpTable.Product = smmSalesUnit.CustGroup;
tmpTable.Region = smmSalesUnit.ParentId;
// Current Month Target
if (smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
{
tmpTable.SalesMonthlyTarget = smmSalesTargetTrans.AmountMST;
if (tmpTable.SalesMonthlyTarget)
{
//Daily Target
tmpTable.SalesDailyTarget = smmSalesTargetTrans.AmountMST/(noOfDaysCurrMth - noOfFridayCurrMth);
}
}
// Previous Month Target
if (smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
{
tmpTable.SalesPrevMonthlyTarget = smmSalesTargetTrans.AmountMST;
}
// Current Week Target
if (FromWeek!=dateNull() && ToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
{
tmpTable.SalesWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
}
// Previous Week Target
if (PrevFromWeek!=dateNull() && PrevToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
{
tmpTable.SalesPrevWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
}
tmpTable.SalesMan = dirPartyTable.Name;
while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable
group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId
join ParentId, count(RecId) from SmmSalesUnitTable
where SmmSalesUnitTable.SalesUnitId == SmmSalesTargetTable.SalesUnitId
&& SmmSalesUnitTable.Active == 1
join sum(AmountMST) from SmmSalesTargetTransTable
where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
&& (SmmSalesTargetTransTable.TargetDate>=startMthDate
&& SmmSalesTargetTransTable.TargetDate<=endMthDate)
{
tmpTable.initValue();
tmpTable.SalesPersonWorker = SmmSalesTargetTable.SalesPersonWorker;
tmpTable.Region = SmmSalesUnitTable.ParentId;
tmpTable.SalesMan = dirPartyTable.Name;
tmpTable.SalesMonthlyTarget = SmmSalesTargetTransTable.AmountMST;
tmpTable.SalesDailyTarget = SmmSalesTargetTransTable.AmountMST/(noOfDaysCurrMth - noOfFridayCurrMth);
while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable
group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
join ParentId, count(RecId)
from SmmSalesUnitTable
where SmmSalesUnitTable.SalesUnitId == SmmSalesTargetTable.SalesUnitId
&& SmmSalesUnitTable.Active == 1
join count(RecId) from hcmWorker
where hcmWorker.RecId == SmmSalesTargetTable.SalesPersonWorker
join Name, count(RecId) from dirPartyTable
where dirPartyTable.RecId == hcmWorker.Person
join sum(AmountMST) from SmmSalesTargetTransTable
where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
&& (SmmSalesTargetTransTable.TargetDate>=startMthDate && SmmSalesTargetTransTable.TargetDate<=endMthDate)
{
tmpTable.initValue();
tmpTable.SalesPersonWorker = SmmSalesTargetTable.SalesPersonWorker;
tmpTable.Region = SmmSalesUnitTable.ParentId;
tmpTable.SalesMan = dirPartyTable.Name;
tmpTable.SalesMonthlyTarget = SmmSalesTargetTransTable.AmountMST;
while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable
group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
join ParentId, count(RecId)
from SmmSalesUnitTable
where SmmSalesUnitTable.SalesUnitId == SmmSalesTargetTable.SalesUnitId
&& SmmSalesUnitTable.Active == 1
join count(RecId) from hcmWorker
where hcmWorker.RecId == SmmSalesTargetTable.SalesPersonWorker
join Name, count(RecId) from dirPartyTable
where dirPartyTable.RecId == hcmWorker.Person
{
tmpTable.initValue();
tmpTable.SalesPersonWorker = SmmSalesTargetTable.SalesPersonWorker;
tmpTable.Region = SmmSalesUnitTable.ParentId;
tmpTable.SalesMan = dirPartyTable.Name;
Select sum(AmountMST) from SmmSalesTargetTransTable Where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
&& (SmmSalesTargetTransTable.TargetDate>=startMthDate && SmmSalesTargetTransTable.TargetDate<=endMthDate);
tmpTable.SalesMonthlyTarget = SmmSalesTargetTransTable.AmountMST;
while select SalesPersonWorker, count(RecId)
from SmmSalesTargetTable group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
join ParentId, count(RecId)
from SmmSalesUnitTable
where SmmSalesUnitTable.SalesUnitId == SmmSalesTargetTable.SalesUnitId
&& SmmSalesUnitTable.Active == 1
join sum(AmountMST) from SmmSalesTargetTransTable
where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
&& (SmmSalesTargetTransTable.TargetDate>=startMthDate && SmmSalesTargetTransTable.TargetDate<=endMthDate)
join count(RecId)
from hcmWorker
where hcmWorker.RecId == SmmSalesTargetTable.SalesPersonWorker
join Name, count(RecId)
from dirPartyTable
where dirPartyTable.RecId == hcmWorker.Person
{
tmpTable.initValue();
tmpTable.SalesPersonWorker = SmmSalesTargetTable.SalesPersonWorker;
tmpTable.Region = SmmSalesUnitTable.ParentId;
tmpTable.SalesMan = dirPartyTable.Name;
tmpTable.SalesMonthlyTarget = SmmSalesTargetTransTable.AmountMST;
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,232 Super User 2024 Season 2
Martin Dráb 230,064 Most Valuable Professional
nmaenpaa 101,156