Using X++ Find out List purchase price agreements with MOQ

I often have received the data requirement from the Soucing department.

such as below.

 

Hi Jimmy,

Could you please based on as below standard run out list with all supplier, unit price, MOQ, Last 12 months purchase QTY us? 

Thanks!

 ...................

Hi Jimmy

 

Thanks for your work. I have two questions as follows: 

1.       Can you please check if we have any other cards (CRD-) which are not included in 5 rages. If so, please list them in your table. 

2.       Have you got MOQ 5K, 10K and 20K prices? If so, please list in your table. 

Thanks

 ...........

static void Jimmy_PurchPriceForMOQWithCRD(Args _args)
{
    priceDiscTable  _priceDiscTable;
    test            test;
    int             i;
    InventTable     IT;
    VendTable       VendTable;
    UTCDateTime     CreateDateTime = DateTimeUtil::addMonths(DateTimeUtil::newDateTime(today(),0),-12);

void getPrice(VendAccount       _VendAccount,
              ItemId            _ItemId)
{
    priceDiscTable priceDiscTable;
;
    while select priceDiscTable
            order by priceDiscTable.QuantityAmount,priceDiscTable.Amount desc
            where priceDiscTable.ItemCode       == TableGroupAll::Table
            &&    priceDiscTable.AccountCode    == TableGroupAll::Table
            &&    priceDiscTable.relation       == PriceType::PricePurch
            &&    priceDiscTable.Module         == ModuleInventCustVend::Vend
            &&    priceDiscTable.ItemRelation   == _ItemId
            &&    priceDiscTable.AccountRelation== _VendAccount
            /*
            &&   (systemdateget() >= priceDiscTable.fromDate || !priceDiscTable.fromDate) &&
                 (systemdateget() <= priceDiscTable.toDate   || !priceDiscTable.toDate  )
            &&   (priceDiscTable.QuantityAmount == 1         ||  priceDiscTable.QuantityAmount == 2000
            ||    priceDiscTable.QuantityAmount == 5000      ||  priceDiscTable.QuantityAmount == 10000
            ||    priceDiscTable.QuantityAmount == 20000)
            */
         {
            switch(priceDiscTable.QuantityAmount)
            {
                case 1 :
                    test.Price01  = priceDiscTable.Amount;break;
                case 2000 :
                    test.Price02 = priceDiscTable.Amount;break;
                case 5000 :
                    test.Price03 = priceDiscTable.Amount;break;
                case 10000 :
                    test.Price04 = priceDiscTable.Amount;break;
                case 20000 :
                    test.Price05 = priceDiscTable.Amount;break;
                case 30000 :
                    test.Price06 = priceDiscTable.Amount;break;
                case 50000 :
                    test.Price07 = priceDiscTable.Amount;break;
                case 80000 :
                    test.Price08 = priceDiscTable.Amount;break;
                case 100000 :
                    test.Price09 = priceDiscTable.Amount;break;
                case 150000 :
                    test.Price10 = priceDiscTable.Amount;break;
                default :
                    break;
            }
         }
}

PurchQty getPurchQty(VendAccount   _VendAccount,
                      ItemId        _ItemId)
{
    purchLine   PurchLine;
    ;
    select sum(PurchQty) from PurchLine
        where PurchLine.VendAccount == _VendAccount
        &&    PurchLine.ItemId      == _ItemId
        &&    PurchLine.createdDateTime >= CreateDateTime;
    return PurchLine.PurchQty;
}
;
    delete_from test;
//请再帮忙增加编号为LABT和PET和PVC开头的的物料到附属的表格中。
    while select _priceDiscTable
        group by AccountRelation,ItemRelation,Currency //,fromDate,toDate
        where _priceDiscTable.ItemCode       == TableGroupAll::Table
        &&    _priceDiscTable.AccountCode    == TableGroupAll::Table
        &&    _priceDiscTable.relation       == PriceType::PricePurch
        &&    _priceDiscTable.Module         == ModuleInventCustVend::Vend
       // &&    _priceDiscTable.AccountRelation== "30299"
        &&    _priceDiscTable.ItemRelation   like "CRD*"
        /*
        &&   (systemdateget() >= _priceDiscTable.fromDate || !_priceDiscTable.fromDate) &&
             (systemdateget() <= _priceDiscTable.toDate   || !_priceDiscTable.toDate  )
        &&   (_priceDiscTable.QuantityAmount == 1         ||  _priceDiscTable.QuantityAmount == 2000
        ||    _priceDiscTable.QuantityAmount == 5000      ||  _priceDiscTable.QuantityAmount == 10000
        ||    _priceDiscTable.QuantityAmount == 20000)
        */

     {
        i++;
        test.Price01    = 0;
        test.Price02    = 0;
        test.Price03    = 0;
        test.Price04    = 0;
        test.Price05    = 0;
        test.Price06    = 0;
        test.Price07    = 0;
        test.Price08    = 0;
        test.Price09    = 0;
        test.Price10    = 0;

        test.ItemId     = _priceDiscTable.ItemRelation;
        test.Name       = InventTable::find(test.ItemId).ItemName;
        test.VendAccount = _priceDiscTable.AccountRelation;
        VendTable = VendTable::find(test.VendAccount);
        test.VendName    = VendTable.ChineseName ? VendTable.ChineseName : VendTable.Name;

        test.BomId       = _priceDiscTable.Currency;
        test.CustName    = Tec_PrimaryVendor::find(test.ItemId).PrimaryVendorId;
        if(test.VendAccount == test.CustName)
            test.IsPic       = Noyes::Yes;
        else
            test.IsPic       = Noyes::No;
        test.FromDate        = _priceDiscTable.FromDate;
        test.ToDate          = _priceDiscTable.ToDate;
        getPrice(test.VendAccount,test.ItemId);
        test.Qty             = getPurchQty(test.VendAccount,test.ItemId);
        test.doinsert();
     }
     info(strfmt("Total updated %1 records!",i));
}
//也请帮忙导出像附件一样的BOX-，CPP-，LAB-，BLS- 对应价格及其它
/*
select a.ITEMID,NAME, a.VENDACCOUNT,a.VENDNAME,a.BOMID,a.ISPIC,qty,
a.PRICE01,a.PRICE02,a.PRICE03,a.PRICE04,a.PRICE05,a.PRICE06,a.PRICE07,a.PRICE08,a.PRICE09,a.PRICE10
from TEST a
order by a.ITEMID,a.VENDACCOUNT,a.PRICE01,a.PRICE02,a.PRICE03,a.PRICE04,a.PRICE05,a.PRICE06,a.PRICE07,a.PRICE08,a.PRICE09,a.PRICE10
go
*/