Axapta : Update ต้นทุนค่าแรงเมื่อเพิ่มเงินเดือนพนักงาน

ต้องทำใน Local ก่อน และ Backup ข้อมูลทั้ง Table ในของจริงก่อนจะเอาเข้า
1. Up แบบ Import File ทำไฟล์ แล้วเอาเข้ากรณีเพิ่มใหม่หรือยังไม่มี MC
https://intranet.sci.com/blog.php?u=578&b=1129

2. แบบมี ข้อมูลอยู่แล้วแต่ต้องการ Update ค่าแรง ใช้ Code ทำเป็น Job Run ทำไฟล์ โดยใช้ EmpId,ต้นทุนค่าแรง
Code

    static void UpdateCostcategoryOT(Args _args)
    {
            AsciiIO             myFile;
            AsciiIO             OKFile;
            AsciiIO             NOFile;
            int                 i,n;
            int                 intRecId;
            container           RecData;
            container           contTmp;
            ItemId              myMC;
            EmplId              myEmplId;
            Price               myCost;
            InventTable         inventTable;
            RouteCostCategory   routeCostCategory;
   
            ;
   
            myFile=new AsciiIO("D:\\2018_Update.csv","r");
            myfile.inFieldDelimiter(",");
            while(myFile.status() == IO_Status::Ok)
                RecData += myFile.read();
            myFile = null;
   
   
            OKFile=new AsciiIO("D:\\OK-Update.txt","a");
            NOFile=new AsciiIO("D:\\NO-Update.txt","a");
            for(i=1; i <= conlen(RecData); i++){
   
                myCost =  conpeek(RecData, i);
                i++;
                myEmplId =  conpeek(RecData, i);
   
                SELECT * FROM routeCostCategory
                WHERE routeCostCategory.EmplId == myEmplId;
   
                if(routeCostCategory.RecId){
                    //print(strfmt("%1 : %2 \t OK",i,myEmplId));
                    OKFile.write(strfmt("%1\t%2\t%3",myEmplId, routeCostCategory.CostPrice, myCost));
                    ttsbegin;
   
    // #####  Cost Amount ######
                    WHILE
                    SELECT FORUPDATE * FROM routeCostCategory
                    WHERE routeCostCategory.EmplId == myEmplId
                    {
                        routeCostCategory.CostPrice = myCost;
                        routeCostCategory.update();
                    }
    // #####  Cost Amount OT 1.5 ######
                    WHILE
                    SELECT FORUPDATE * FROM routeCostCategory
                    WHERE routeCostCategory.EmplId == myEmplId
                    && (routeCostCategory.CostCategoryId LIKE '*1.5' || routeCostCategory.CostCategoryId LIKE 'OT1.5*')
                    {
                        routeCostCategory.CostPrice = myCost * 1.5;
                        routeCostCategory.update();
                    }
    // #####  Cost Amount OT 2.0 ######
                    WHILE
                    SELECT FORUPDATE * FROM routeCostCategory
                    WHERE routeCostCategory.EmplId == myEmplId
                    && (routeCostCategory.CostCategoryId LIKE '*2.0' || routeCostCategory.CostCategoryId LIKE 'OT2.0*')
                    {
                        routeCostCategory.CostPrice = myCost * 2.0;
                        routeCostCategory.update();
                    }
    // #####  Cost Amount OT 3.0 ######
                    WHILE
                    SELECT FORUPDATE * FROM routeCostCategory
                    WHERE routeCostCategory.EmplId == myEmplId
                    && (routeCostCategory.CostCategoryId LIKE '*3.0' || routeCostCategory.CostCategoryId LIKE 'OT3.0*')
                    {
                        routeCostCategory.CostPrice = myCost * 3.0;
                        routeCostCategory.update();
                    }
                    ttscommit;
   
                }else{
                    //print(strfmt("%1 : %2 \t NO",i,myEmplId));
                    NoFile.write(strfmt("%1,%2",myEmplId, myCost));
                }
   
   
   
            }
            OKFile = null;
            NOFile = null;
            box::info(" Complete","File2SQL");
    //    pause;
   
    }



3. เปลี่ยนแท่นเปลี่ยนคน ต้องทำ
3.1. ได้ไฟล์มาทำไฟล์ เอารหัสพนักงานเข้าไป Check ว่ามีพนังานอยู่หรือไม่ ถ้าไม่มีตรวจสอบอาจใส่รหัสผิด หรือต้องเพิ่มข้อมูลใหม่ในข้อ 1.
ใช้ MC,EmpId
Code

    static void UpdateCostcategoryCheckMC(Args _args)
    {
            AsciiIO             myFile;
            AsciiIO             OKFile;
            AsciiIO             NOFile;
            int                 i,n;
            int                 intRecId;
            container           RecData;
            container           contTmp;
            ItemId              myMC;
            EmplId              myEmplId;
            Price               myCost;
            InventTable         inventTable;
            RouteCostCategory   routeCostCategory;
   
            ;
   
            myFile=new AsciiIO("D:\\CheckMC.csv","r");
            myfile.inFieldDelimiter(",");
            while(myFile.status() == IO_Status::Ok)
                RecData += myFile.read();
            myFile = null;
   
   
            OKFile=new AsciiIO("D:\\CheckMCOK.txt","a");
            NOFile=new AsciiIO("D:\\CheckMCNO.txt","a");
            for(i=1; i <= conlen(RecData); i++){
   
                myMC =  conpeek(RecData,i);
                i++;
                myEmplId =  conpeek(RecData,i);
   
                SELECT * FROM routeCostCategory
                WHERE routeCostCategory.CostCategoryId == myMC;
                if(routeCostCategory.RecId){
                    print(strfmt("%1 : %2 \t OK",i,myEmplId));
                    OKFile.write(strfmt("%1\t%2\t%3",myMC, myEmplId, routeCostCategory.EmplId));
                    /*ttsbegin;
                    WHILE SELECT FORUPDATE * FROM routeCostCategory
                    WHERE routeCostCategory.EmplId == myEmplId
                    {
                    routeCostCategory.CostPrice = myCost;
                    routeCostCategory.update();
                    }
                    ttscommit;*/
                }else{
                    print(strfmt("%1 : %2 \t NO",i,myEmplId));
                    NoFile.write(strfmt("%1,%2",myMC, myEmplId));
                }
   
   
   
            }
            OKFile = null;
            NOFile = null;
            box::info(" Complete","File2SQL");
    //    pause;
   
    }


3.2. ได้ไฟล์ที่จะ Update เปลี่ยนแท่น Format : MC,EmpId,EmpIdAX เขียน Code Update เปลี่ยนแท่น จะเปลี่ยนจาก Emp อีกคนเป็นอีกคน ทุก รายการที่ MC เดียวกัน แล้วค่อยไป Update ค่าแรงในข้อ 2.
Code    
    static void UpdateCostcategoryMC(Args _args)
    {
            AsciiIO             myFile;
            AsciiIO             OKFile;
            AsciiIO             NOFile;
            int                 i,n;
            int                 intRecId;
            container           RecData;
            container           contTmp;
            ItemId              myMC;
            ItemId              myMCUpdate;
            EmplId              myEmplId;
            EmplId              myEmplIdAX;
            Price               myCost;
            InventTable         inventTable;
            RouteCostCategory   routeCostCategory;
   
            ;
   
            myFile=new AsciiIO("D:\\UpdateMC.csv","r");
            myfile.inFieldDelimiter(",");
            while(myFile.status() == IO_Status::Ok)
                RecData += myFile.read();
            myFile = null;
   
            OKFile=new AsciiIO("D:\\UpdateMCOK.txt","a");
            NOFile=new AsciiIO("D:\\UpdateMCNO.txt","a");
            for(i=1; i <= conlen(RecData); i++){
   
                myMC = conpeek(RecData,i);
                i++;
                myEmplId =  conpeek(RecData,i);
                i++;
                myEmplIdAX =  conpeek(RecData,i);
   
                myMCUpdate = strfmt('*%1*', myMC);
                ttsbegin;
                WHILE
                SELECT FORUPDATE * FROM routeCostCategory
                WHERE routeCostCategory.EmplId == myEmplIdAX
                    && routeCostCategory.CostCategoryId LIKE myMCUpdate
                {
                    if(routeCostCategory.RecId){
   
                        //print(strfmt("%1 : %2 \t OK",i,myEmplId));
                        OKFile.write(strfmt("%1\t%2\t%3",myMC, myEmplId, myEmplIdAX));
   
                        routeCostCategory.EmplId = myEmplId;
                        routeCostCategory.update();
                    } else {
                        //print(strfmt("%1 : %2 \t NO",i,myEmplId));
                        NoFile.write(strfmt("%1,%2",myMC, myEmplId, myEmplIdAX));
                    }
                }
                ttscommit;
            }
            OKFile = null;
            NOFile = null;
            box::info(" Complete","File2SQL");
    //    pause;
   
    }



4.พนักงานออกไปแล้ว เขียน Code Reset รหัสและค่าแรง โดยเปลี่ยนรหัสพนักงาน
Code

    static void UpdateCostcategoryByEmpId(Args _args)
    {
            EmplId              myEmplId;
            Price               myCost;
            InventTable         inventTable;
            RouteCostCategory   routeCostCategory;
   
            ;
   
            ttsbegin;
                WHILE SELECT FORUPDATE * FROM routeCostCategory
                WHERE routeCostCategory.EmplId == '4273-5'
                {
                routeCostCategory.EmplId = '';
                routeCostCategory.CostPrice = 0;
                routeCostCategory.update();
                }
            ttscommit;
   
            box::info(" Complete","File2SQL");
    }