ERP導出（自定義格式表格）R報表開發代碼

来源:https://www.cnblogs.com/sanmulx/archive/2023/07/07/17534431.html
-Advertisement-
Play Games

按照正常流程新建程式，畫面修改上傳，程式下載修改 導入JAVA包，在global.import下 IMPORT com IMPORT JAVA java.net.URL IMPORT JAVA org.apache.poi.ss.util.CellRangeAddress IMPORT JAVA o ...


按照正常流程新建程式，畫面修改上傳，程式下載修改

導入JAVA包，在global.import下

 

IMPORT com
IMPORT JAVA java.net.URL
IMPORT JAVA org.apache.poi.ss.util.CellRangeAddress
IMPORT JAVA org.apache.poi.ss.util.RegionUtil
IMPORT JAVA java.io.InputStream
IMPORT JAVA java.io.FileInputStream
IMPORT JAVA java.io.FileOutputStream
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFWorkbook
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFSheet
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFRow
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCell
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCellStyle
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFFont
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFDataFormat
IMPORT JAVA com.fourjs.fgl.lang.FglDecimal
IMPORT JAVA com.fourjs.fgl.lang.FglDate
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFWorkbook
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFSheet
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFRow
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCell
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCellStyle
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFFont
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFDataFormat
IMPORT JAVA org.apache.poi.xssf.streaming.SXSSFWorkbook
IMPORT JAVA org.apache.poi.ss.usermodel.CellStyle
IMPORT JAVA org.apache.poi.ss.usermodel.Sheet
IMPORT JAVA org.apache.poi.ss.usermodel.Row
IMPORT JAVA org.apache.poi.ss.usermodel.Cell
IMPORT JAVA org.apache.poi.ss.usermodel.Font
IMPORT JAVA org.apache.poi.ss.usermodel.DataFormat   
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFWorkbook
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFSheet
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFRow
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCell
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFCellStyle
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFFont
IMPORT JAVA org.apache.poi.xssf.usermodel.XSSFDataFormat

 

在global.variable下添加全局變數

 

DEFINE g_showmsg                       STRING
DEFINE l_xlsfile                       STRING
DEFINE l_xlsname                       STRING
DEFINE l_target                        STRING
DEFINE fo                              FileOutputStream
DEFINE import_file                     InputStream
DEFINE workbook                        XSSFWorkbook
DEFINE sheet                           XSSFSheet
DEFINE row                             XSSFRow
DEFINE cell                            XSSFCell
DEFINE cell_xls                        HSSFCell
DEFINE font1                           XSSFFont
DEFINE font2                           XSSFFont
DEFINE font3                           XSSFFont
DEFINE font4                           XSSFFont
DEFINE format1         DataFormat
DEFINE g_num           FLOAT

 

在DIALOG ATTRIBUTES下添加查詢欄位邏輯

 

         ON ACTION controlp INFIELD xcag004
            #add-point:ON ACTION controlp INFIELD xcag004 name="construct.c.xcag004"
            #應用 a08 樣板自動產生(Version:3)
            #開窗c段
            INITIALIZE g_qryparam.* TO NULL
            LET g_qryparam.state = 'c' 
            LET g_qryparam.reqry = FALSE
            CALL q_xcag004()                           #呼叫開窗
            DISPLAY g_qryparam.return1 TO xcag004  #顯示到畫面上
            NEXT FIELD xcag004                     #返回原欄位
    



            #END add-point
 
 
         #應用 a01 樣板自動產生(Version:2)
         BEFORE FIELD xcag004
            #add-point:BEFORE FIELD xcag004 name="construct.b.xcag004"

            #END add-point
 
 
         #應用 a02 樣板自動產生(Version:2)
         AFTER FIELD xcag004
            
            #add-point:AFTER FIELD xcag004 name="construct.a.xcag004"

            #END add-point

 

在process下的process.count_progress下添加調用方法邏輯

 

      CALL cxcr600_create_tmp()
      CALL cxcr600_export_excel()

 

寫自己的方法，供上面調用

 

PRIVATE FUNCTION cxcr600_create_tmp()
     DROP TABLE cxcr600_tmp; 
     CREATE TEMP TABLE cxcr600_tmp(
       t_xcah020            LIKE   xcah_t.xcah020,    #bom階層
       t_xcah004            LIKE   xcah_t.xcah040,    #主件料號
       t_xcah004_desc       LIKE   type_t.chr200,     #主件品名
       t_xcah004_1_desc     LIKE   type_t.chr200,     #主件規格
       t_xcaeud015_sum      LIKE   xcae_t.xcaeud015,  #合計工時
       t_xcah031_a_sum      LIKE   type_t.num20_6,    #合計材料費
       t_price_ws           LIKE   type_t.num20_6,    #未稅單價
       t_xcah031_b_sum      LIKE   type_t.num20_6,    #合計人工費
       t_xcah031_b_szb      LIKE   type_t.num20_6,    #人工費用占比
       t_xcah031_a_szb      LIKE   type_t.num20_6,    #材料費用占比
       t_xcah022            LIKE   xcah_t.xcah022,    #元件料號
       t_xcah022_desc       LIKE   type_t.chr200,     #元件品名
       t_xcah022_1_desc     LIKE   type_t.chr200,     #元件規格
       t_xcaeud015          LIKE   xcae_t.xcaeud015,  #各階段標準工時
       t_xcaeud016          LIKE   xcae_t.xcaeud016,  #各階段標準機時
       t_xcah031_b          LIKE   type_t.num20_6,    #各階段人力費
       t_xcah031_a          LIKE   type_t.num20_6,    #各階段材料費
       t_xcah026            LIKE   xcah_t.xcah026,    #工時占比（各階段/總）
       t_xcah025            LIKE   xcah_t.xcah025     #材料占比（各階段/總）
     );

END FUNCTION

PRIVATE FUNCTION cxcr600_ins_tmp(p_imaa001_a)
    DEFINE p_imaa001_a     LIKE imaa_t.imaa001
    DEFINE l_sql           STRING
    DEFINE l_dex           LIKE type_t.num5
    DEFINE l_docno         DYNAMIC ARRAY OF RECORD 
       l_xcah020            LIKE   xcah_t.xcah020,    #bom階層    
       l_xcah004            LIKE   xcah_t.xcah040,    #主件料號
       l_xcah004_desc       LIKE   type_t.chr200,     #主件品名
       l_xcah004_1_desc     LIKE   type_t.chr200,     #主件規格
       l_xcaeud015_sum      LIKE   xcae_t.xcaeud015,  #合計工時
       l_xcah031_a_sum      LIKE   type_t.num20_6,    #合計材料費
       l_price_ws           LIKE   type_t.num20_6,    #未稅單價
       l_xcah031_b_sum      LIKE   type_t.num20_6,    #合計人工費       
       l_xcah031_b_szb      LIKE   type_t.num20_6,    #人工費用占比
       l_xcah031_a_szb      LIKE   type_t.num20_6,    #材料費用占比
       l_xcah022            LIKE   xcah_t.xcah022,    #元件料號
       l_xcah022_desc       LIKE   type_t.chr200,     #元件品名
       l_xcah022_1_desc     LIKE   type_t.chr200,     #元件規格
       l_xcaeud015          LIKE   xcae_t.xcaeud015,  #各階段標準工時
       l_xcaeud016          LIKE   xcae_t.xcaeud016,  #各階段標準機時
       l_xcah031_b          LIKE   type_t.num20_6,    #各階段人力費
       l_xcah031_a          LIKE   type_t.num20_6,    #各階段材料費
       l_xcah026            LIKE   xcah_t.xcah026,    #工時占比（各階段/總）
       l_xcah025            LIKE   xcah_t.xcah025     #材料占比（各階段/總）
    END RECORD
    DEFINE l_num           LIKE type_t.num5

    
    LET l_sql = "DELETE FROM cxcr600_tmp "
    EXECUTE IMMEDIATE l_sql
    
    #插入數據
    LET l_sql = "INSERT INTO cxcr600_tmp
                 SELECT xcah020,xcag004,a.imaal003,a.imaal004,0,0,0,0,0,0,xcah022,b.imaal003,b.imaal004,0,0,0,0,0,0 FROM xcag_t
                 LEFT JOIN xcah_t ON xcagent = xcahent AND xcagsite = xcahsite AND xcag001 = xcah001 AND xcag004 = xcah004
                 LEFT JOIN imaal_t a ON a.imaalent = xcagent AND a.imaal001 = xcag004 AND a.imaal002 = '",g_dlang,"'
                 LEFT JOIN imaal_t b ON b.imaalent = xcahent AND b.imaal001 = xcah022 AND b.imaal002 = '",g_dlang,"'
                 WHERE xcagent = ",g_enterprise," AND xcagsite = '",g_site,"' AND xcah004 = '",p_imaa001_a,"' 
                 AND xcag001 = '101' AND xcah023 = 'A'
                 ORDER BY xcah020 ASC "
    DISPLAY l_sql
    EXECUTE IMMEDIATE l_sql
    
    LET l_sql = " SELECT * FROM cxcr600_tmp "
    PREPARE l_upd_code FROM l_sql
    DECLARE l_upd_curs CURSOR FOR l_upd_code 
 
    CALL l_docno.clear()     #在進入迴圈之前把二維數組清空
    LET l_dex = 1            #初始化   
 
    FOREACH l_upd_curs INTO l_docno[l_dex].*
      
      #判斷如果不屬於A或者B料號，就刪除，因為存在C，D料號屬於A類型
       SELECT COUNT(*) INTO l_num 
       FROM cxcr600_tmp
       WHERE t_xcah022=l_docno[l_dex].l_xcah022 
       AND (t_xcah022 LIKE 'A%' OR t_xcah022 LIKE 'B%')
     IF cl_null(l_num) OR (l_num = 0) THEN
       #刪除此料號
       DELETE FROM cxcr600_tmp 
       WHERE t_xcah004 = l_docno[l_dex].l_xcah004 
       AND t_xcah022 = l_docno[l_dex].l_xcah022  
     END IF
      LET l_num = 0
      LET l_dex = l_dex + 1
    
    END FOREACH
    
    
END FUNCTION

PRIVATE FUNCTION cxcr600_get_data(p_imaa001_a)
    DEFINE p_imaa001_a     LIKE imaa_t.imaa001
    DEFINE l_sql           STRING
    DEFINE l_dex           LIKE type_t.num5           #數組迴圈變數
    DEFINE l_num           LIKE type_t.num5           #判斷是否夾治具
    DEFINE l_num1          LIKE xcah_t.xcah025        #上階主件底數
    DEFINE l_num2          LIKE xcah_t.xcah026        #上階組成用量
    DEFINE l_money_sh      LIKE type_t.num20_6        #主件損耗
    
    DEFINE l_docno         DYNAMIC ARRAY OF RECORD  
       l_xcah004            LIKE   xcah_t.xcah040,    #主件料號
       l_xcah004_desc       LIKE   type_t.chr200,     #主件品名
       l_xcah004_1_desc     LIKE   type_t.chr200,     #主件規格
       l_xcaeud015_sum      LIKE   xcae_t.xcaeud015,  #合計工時
       l_xcah031_a_sum      LIKE   type_t.num20_6,    #合計材料費
       l_price_ws           LIKE   type_t.num20_6,    #未稅單價
       l_xcah031_b_sum      LIKE   type_t.num20_6,    #合計人工費
       l_xcah031_b_szb      LIKE   type_t.num20_6,    #人工費用占比
       l_xcah031_a_szb      LIKE   type_t.num20_6,    #材料費用占比
       l_xcah022            LIKE   xcah_t.xcah022,    #元件料號
       l_xcah022_desc       LIKE   type_t.chr200,     #元件品名
       l_xcah022_1_desc     LIKE   type_t.chr200,     #元件規格
       l_xcaeud015          LIKE   xcae_t.xcaeud015,  #各階段標準工時
       l_xcaeud016          LIKE   xcae_t.xcaeud016,  #各階段標準機時
       l_xcah031_b          LIKE   type_t.num20_6,    #各階段人力費
       l_xcah031_a          LIKE   type_t.num20_6,    #各階段材料費
       l_xcah026            LIKE   xcah_t.xcah026,    #工時占比（各階段/總）
       l_xcah025            LIKE   xcah_t.xcah025     #材料占比（各階段/總）
    END RECORD
    
   
    #======================================頁簽==============================================================
    CALL cxcr600_ins_tmp(p_imaa001_a)
    
    LET l_sql = " SELECT t_xcah004,t_xcah004_desc,t_xcah004_1_desc,t_xcaeud015_sum,t_xcah031_a_sum,
                  t_price_ws,t_xcah031_b_sum,t_xcah031_b_szb,t_xcah031_a_szb,t_xcah022,t_xcah022_desc,
                  t_xcah022_1_desc,t_xcaeud015,t_xcaeud016,t_xcah031_b,t_xcah031_a,t_xcah026,t_xcah025 
                  FROM cxcr600_tmp ORDER BY t_xcah020 ASC "
     
    PREPARE l_get_code FROM l_sql
    DECLARE l_get_curs CURSOR FOR l_get_code 
   
    CALL l_docno.clear()     #在進入迴圈之前把二維數組清空
    LET l_dex = 1            #初始化   
    LET l_money_sh = 0       #初始化損耗
    FOREACH l_get_curs INTO l_docno[l_dex].*
      
      #1、標準工時 取階段料號對應的工時和
      SELECT SUM(xcaeud015) INTO l_docno[l_dex].l_xcaeud015 FROM xcae_t 
      WHERE xcaeent = g_enterprise AND xcaesite = g_site 
      AND xcaestus = 'Y' AND xcae002 = l_docno[l_dex].l_xcah004
      AND xcaeud001 = l_docno[l_dex].l_xcah022
      
      IF cl_null(l_docno[l_dex].l_xcaeud015) THEN 
            LET l_docno[l_dex].l_xcaeud015 = 0
      END IF
      
      #2、標準機時 取階段料號對應的機時和
      SELECT SUM(xcaeud016) INTO l_docno[l_dex].l_xcaeud016 FROM xcae_t 
      WHERE xcaeent = g_enterprise AND xcaesite = g_site 
      AND xcaestus = 'Y' AND xcae002 = l_docno[l_dex].l_xcah004
      AND xcaeud001 = l_docno[l_dex].l_xcah022
      
      IF cl_null(l_docno[l_dex].l_xcaeud016) THEN 
            LET l_docno[l_dex].l_xcaeud016 = 0
      END IF
      
      #3、人力成本 取階段料號對應的成本直接人工+間接一、二
      SELECT (xcah031b+xcah031d+xcah031e) INTO l_docno[l_dex].l_xcah031_b FROM xcah_t
      WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
      AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah022 = l_docno[l_dex].l_xcah022
      
      #4、材料成本 如果是夾治具，直接去成本材料費；不是的話，取它對應的下階材料費加上它自身的損耗
      #取數判斷是否夾治具
      SELECT imaaud011 INTO l_num FROM imaa_t
      WHERE imaaent = g_enterprise AND imaastus = 'Y'
      AND imaa001 = l_docno[l_dex].l_xcah022
      
      SELECT xcah025 INTO l_num1 FROM xcah_t
      WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
      AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah022 = l_docno[l_dex].l_xcah022
      
      SELECT xcah026 INTO l_num2 FROM xcah_t
      WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
      AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah022 = l_docno[l_dex].l_xcah022
      
      #損耗=單位材料*組成用量/主件底數/(1-損耗)-單位材料*組成用量/主件底數 更新到主件中去
      SELECT (xcah030a*xcah026/xcah025/(1-xcah027/100)-xcah030a*xcah026/xcah025) INTO l_money_sh FROM xcah_t
      WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
      AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah040 = l_docno[l_dex].l_xcah022
      AND xcah023 = 'A' AND xcah022 IN (
                                           SELECT imaa001 FROM imaa_t
                                           WHERE imaaent = g_enterprise AND imaastus = 'Y'
                                           AND (imaaud011 = 0 OR imaaud011 IS NULL)
                                           )
      IF cl_null(l_money_sh) THEN
         LET l_money_sh = 0
      END IF
      #判斷是否夾治具料號，如果是直接取夾治具材料成本，否則的話取下階材料和
      IF l_num <> 0 THEN
         SELECT xcah031a INTO l_docno[l_dex].l_xcah031_a FROM xcah_t
         WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
         AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah022 = l_docno[l_dex].l_xcah022
      ELSE
         SELECT SUM(xcah031a/l_num1*l_num2) INTO l_docno[l_dex].l_xcah031_a FROM xcah_t
         WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
         AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah023 = 'M' AND xcah040 = l_docno[l_dex].l_xcah022
         AND xcah020 IN (
                        SELECT xcah020+1 FROM xcah_t
                        WHERE xcahent = g_enterprise AND xcahsite = g_site AND xcah001 = '101'
                        AND xcah004 = l_docno[l_dex].l_xcah004 AND xcah022 = l_docno[l_dex].l_xcah022
                        )
         #如果下階無材料，費用為空，另它等於零
         IF cl_null(l_docno[l_dex].l_xcah031_a) THEN 
            LET l_docno[l_dex].l_xcah031_a = 0
         END IF
         #費用加上它的損耗
         LET l_docno[l_dex].l_xcah031_a = l_docno[l_dex].l_xcah031_a+l_money_sh
         LET l_money_sh = 0
      END IF
      
      IF cl_null(l_docno[l_dex].l_xcah031_a) THEN 
         LET l_docno[l_dex].l_xcah031_a = 0
      END IF
      
      #5、合計人工 取axci006裡面主件直接+間接
      SELECT (xcag102b+xcag102d+xcag102e) INTO l_docno[l_dex].l_xcah031_b_sum FROM xcag_t
      WHERE xcagent = g_enterprise AND xcagsite = g_site 
      AND xcag001 = '101' AND xcag004 = l_docno[l_dex].l_xcah004
      
      
      #6、合計材料 直接取axci006主件材料費
      SELECT xcag102a INTO l_docno[l_dex].l_xcah031_a_sum FROM xcag_t
      WHERE xcagent = g_enterprise AND xcagsite = g_site 
      AND xcag001 = '101' AND xcag004 = l_docno[l_dex].l_xcah004
      
      #7、合計工時 取axci005裡面所有工時和
      SELECT SUM(xcaeud015) INTO l_docno[l_dex].l_xcaeud015_sum FROM xcae_t
      WHERE xcaeent = g_enterprise AND xcaesite = g_site 
      AND xcaestus = 'Y' AND xcae002 = l_docno[l_dex].l_xcah004
      
      #8、未稅單價
      LET l_docno[l_dex].l_price_ws = cxcr600_get_price(l_docno[l_dex].l_xcah004)
      
      #9、主件人工占比
      LET l_docno[l_dex].l_xcah031_b_szb = l_docno[l_dex].l_xcah031_b_sum/l_docno[l_dex].l_price_ws
      
      
      #10、主件材料占比
      LET l_docno[l_dex].l_xcah031_a_szb = l_docno[l_dex].l_xcah031_a_sum/l_docno[l_dex].l_price_ws
      
      #11、工時占比
      LET l_docno[l_dex].l_xcah026= l_docno[l_dex].l_xcaeud015/l_docno[l_dex].l_xcaeud015_sum
      
      #12、材料占比
      LET l_docno[l_dex].l_xcah025 = l_docno[l_dex].l_xcah031_a/l_docno[l_dex].l_xcah031_a_sum

      LET l_dex = l_dex + 1
    END FOREACH

    CALL l_docno.deleteElement(l_docno.getLength())
    
    
    RETURN l_docno
    
    
    
END FUNCTION

PRIVATE FUNCTION cxcr600_export_excel()
 DEFINE style2                    XSSFCellStyle
 DEFINE style3                    XSSFCellStyle
 DEFINE style3_5                  XSSFCellStyle
 DEFINE style4                    XSSFCellStyle
 DEFINE style4_1                  XSSFCellStyle
 DEFINE style4_3                  XSSFCellStyle
 DEFINE style4_3_1                XSSFCellStyle
 DEFINE style5                    XSSFCellStyle
 DEFINE style6                    XSSFCellStyle
 DEFINE style7                    XSSFCellStyle
 DEFINE style8                    XSSFCellStyle
 DEFINE style9                    XSSFCellStyle
 DEFINE workbook                  XSSFWorkbook
 DEFINE l_str                     STRING
 DEFINE l_cnt                     LIKE type_t.num10
 DEFINE l_i                       LIKE type_t.num5
 DEFINE l_n                       LIKE type_t.num5
 DEFINE l_n2                      LIKE type_t.num5
 DEFINE l_fileloc                 STRING
 DEFINE l_length                  LIKE type_t.num10
 DEFINE region                    CellRangeAddress
 DEFINE l_m                       LIKE type_t.num5
 DEFINE l_flag                    LIKE type_t.chr100
 DEFINE l_n1                      LIKE type_t.num5
 DEFINE l_num                     FLOAT
 DEFINE li_font_size              LIKE type_t.num5
 DEFINE l_time                    LIKE pmaa_t.pmaacrtdt
 DEFINE l_string                  LIKE type_t.chr200
 DEFINE l_dex                     LIKE type_t.num5
 DEFINE l_m_n                     LIKE type_t.num5
 DEFINE l_number                  FLOAT
 DEFINE l_sql                     STRING
 DEFINE l_row                     LIKE type_t.num10
 DEFINE l_row1                    LIKE type_t.num10
 DEFINE l_sum_days                LIKE type_t.num5
 DEFINE l_imaa001                 LIKE imaa_t.imaa001             
 DEFINE r_docno         DYNAMIC ARRAY OF RECORD  
       l_xcah004            LIKE   xcah_t.xcah040,    #主件料號
       l_xcah004_desc       LIKE   type_t.chr200,     #主件品名
       l_xcah004_1_desc     LIKE   type_t.chr200,     #主件規格
       l_xcaeud015_sum      LIKE   xcae_t.xcaeud015,  #合計工時
       l_xcah031_a_sum      LIKE   type_t.num20_6,    #合計材料費
       l_price_ws           LIKE   type_t.num20_6,    #未稅單價
       l_xcah031_b_sum      LIKE   type_t.num20_6,    #合計人工費
       l_xcah031_b_szb      LIKE   type_t.num20_6,    #人工費用占比
       l_xcah031_a_szb      LIKE   type_t.num20_6,    #材料費用占比
       l_xcah022            LIKE   xcah_t.xcah022,    #元件料號
       l_xcah022_desc       LIKE   type_t.chr200,     #元件品名
       l_xcah022_1_desc     LIKE   type_t.chr200,     #元件規格
       l_xcaeud015          LIKE   xcae_t.xcaeud015,  #各階段標準工時
       l_xcaeud016          LIKE   xcae_t.xcaeud016,  #各階段標準機時
       l_xcah031_b          LIKE   type_t.num20_6,    #各階段人力費
       l_xcah031_a          LIKE   type_t.num20_6,    #各階段材料費
       l_xcah026            LIKE   xcah_t.xcah026,    #工時占比（各階段/總）
       l_xcah025            LIKE   xcah_t.xcah025     #材料占比（各階段/總）
 END RECORD
 DEFINE l_date       LIKE type_t.dat
 DEFINE l_day        LIKE type_t.num5
 DEFINE l_field      LIKE type_t.chr200
 DEFINE l_i_dex      LIKE type_t.num10

 CALL ui.Interface.frontCall("standard","opendir",["C:", "File Browser"],[g_showmsg])
 
 LET workbook = XSSFWorkbook.create()   #創建工作簿
 
 #設定EXCEL單元格式1
 LET style2 = workbook.createCellStyle()
 CALL style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style2.setAlignment(XSSFCellStyle.ALIGN_CENTER)    
 CALL style2.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style2.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style2.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style2.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style2.setWrapText(true)
 
 #設定EXCEL單元格式2 包涵背景顏色 
 LET style3 = workbook.createCellStyle()
 #CALL style3.setFillForegroundColor(IndexedColors.LIME.getIndex())
 CALL style3.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style3.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 CALL style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style3.setAlignment(XSSFCellStyle.ALIGN_CENTER)   
 CALL style3.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style3.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style3.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style3.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style3.setWrapText(true)
 
# LET style7 = workbook.createCellStyle()
# #CALL style7.setFillForegroundColor(IndexedColors.LIME.getIndex())
# CALL style7.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex())
# CALL style7.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
# CALL style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
# CALL style7.setAlignment(XSSFCellStyle.ALIGN_CENTER)   
# CALL style7.setBorderBottom(XSSFCellStyle.BORDER_THIN)
# CALL style7.setBorderLeft(XSSFCellStyle.BORDER_THIN)
# CALL style7.setBorderTop(XSSFCellStyle.BORDER_THIN)
# CALL style7.setBorderRight(XSSFCellStyle.BORDER_THIN)
# CALL style7.setWrapText(true)
 
 
 
 #小計欄位
 LET style3_5 = workbook.createCellStyle()
 CALL style3_5.setFillForegroundColor(IndexedColors.YELLOW.getIndex())
 CALL style3_5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 CALL style3_5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style3_5.setAlignment(XSSFCellStyle.ALIGN_CENTER)   
 CALL style3_5.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style3_5.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style3_5.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style3_5.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style3_5.setWrapText(true)
    
 #設定EXCEL單元格式2 包涵背景顏色,保留小數位
 LET  style4 = workbook.createCellStyle()
 #CALL style4.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
 CALL style4.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 LET format1 = workbook.createDataFormat()
 CALL style4.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style4.setDataFormat(format1.getFormat("0.000000"))
 CALL style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style4.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style4.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style4.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style4.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style4.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style4.setWrapText(true)
 
 LET  style4_1 = workbook.createCellStyle()
 #CALL style4.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
 CALL style4_1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 LET format1 = workbook.createDataFormat()
 CALL style4_1.setFillForegroundColor(IndexedColors.RED.getIndex())
 CALL style4_1.setDataFormat(format1.getFormat("0.000000"))
 CALL style4_1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style4_1.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style4_1.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style4_1.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style4_1.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style4_1.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style4_1.setWrapText(true)
 
 LET li_font_size = 11
 LET  style4_3 = workbook.createCellStyle()
 LET font2 = workbook.createFont()
 CALL font2.setFontName("Microsoft YaHei")       
 CALL font2.setFontHeightInPoints(li_font_size)     
 CALL font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
 CALL style4_3.setFont(font2)
 CALL style4_3.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style4_3.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 LET format1 = workbook.createDataFormat()
 CALL style4_3.setDataFormat(format1.getFormat("0.00%"))
 CALL style4_3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style4_3.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style4_3.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style4_3.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style4_3.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style4_3.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style4_3.setWrapText(true)
 
 LET li_font_size = 11
 LET  style4_3_1 = workbook.createCellStyle()
 LET font2 = workbook.createFont()
 CALL font2.setFontName("Microsoft YaHei")       
 CALL font2.setFontHeightInPoints(li_font_size)     
 CALL font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
 CALL style4_3_1.setFont(font2)
 CALL style4_3_1.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
 CALL style4_3_1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 LET format1 = workbook.createDataFormat()
 CALL style4_3_1.setDataFormat(format1.getFormat("0.00%"))
 CALL style4_3_1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style4_3_1.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style4_3_1.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style4_3_1.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style4_3_1.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style4_3_1.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style4_3_1.setWrapText(true)
 
 #設定小計的黃色小數點
 LET  style6 = workbook.createCellStyle()
 CALL style6.setFillForegroundColor(IndexedColors.YELLOW.getIndex())
 CALL style6.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 LET format1 = workbook.createDataFormat()
 CALL style6.setDataFormat(format1.getFormat("0.00"))
 CALL style6.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style6.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style6.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style6.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style6.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style6.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style6.setWrapText(true)
 
 #第一行標題
 LET li_font_size = 28 
 #字體設定
 LET  font1 = workbook.createFont() #創建字體
 #CALL font1.setFontName("Microsoft JhengHei")#字體類型，比如宋體、雅黑等等
 CALL font1.setFontName("Microsoft YaHei")
 CALL font1.setFontHeightInPoints(li_font_size)#字體大小     
 CALL font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)#字體加粗
 #字體加粗，加大，標題欄目
 LET  style5 = workbook.createCellStyle()
 #CALL style5.setFillForegroundColor(IndexedColors.LIME.getIndex())
 CALL style5.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 CALL style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)
 CALL style5.setAlignment(XSSFCellStyle.ALIGN_CENTER)   
 CALL style5.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style5.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style5.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style5.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style5.setFont(font1) #設置上述創建字體
 CALL style5.setWrapText(true)
 
 #第三行標題
 LET li_font_size = 11
 LET style7 = workbook.createCellStyle()
 LET font3 = workbook.createFont()
 CALL font3.setFontName("Microsoft YaHei")       
 CALL font3.setFontHeightInPoints(li_font_size)     
 CALL font3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
 CALL style7.setFont(font3)
 CALL style7.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex())
 CALL style7.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 CALL style7.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style7.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style7.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style7.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)   
 CALL style7.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 CALL style7.setWrapText(true)
 
 #第二行標題
 LET li_font_size = 18
 LET style8 = workbook.createCellStyle()
 LET font4 = workbook.createFont()
 CALL font4.setFontName("Microsoft YaHei")       
 CALL font4.setFontHeightInPoints(li_font_size)     
 CALL font4.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD)
 CALL style8.setFont(font4)
 CALL style8.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style8.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
 CALL style8.setBorderBottom(XSSFCellStyle.BORDER_THIN)
 CALL style8.setBorderLeft(XSSFCellStyle.BORDER_THIN)
 CALL style8.setBorderTop(XSSFCellStyle.BORDER_THIN)
 CALL style8.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style8.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)   
 CALL style8.setAlignment(XSSFCellStyle.ALIGN_CENTER)
 
 #最後一行的文件編碼
 LET style9 = workbook.createCellStyle()
 CALL style9.setFont(font2)
 CALL style9.setFillForegroundColor(IndexedColors.WHITE.getIndex())
 CALL style9.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND)
# CALL style9.setBorderBottom(XSSFCellStyle.BORDER_THIN)
# CALL style9.setBorderLeft(XSSFCellStyle.BORDER_THIN)
# CALL style9.setBorderTop(XSSFCellStyle.BORDER_THIN)
# CALL style9.setBorderRight(XSSFCellStyle.BORDER_THIN)
 CALL style9.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER)   
 CALL style9.setAlignment(XSSFCellStyle.ALIGN_CENTER)


 LET l_str = "成本明細"
 LET sheet = workbook.createSheet(l_str)
#=======================================頁面開始第一行=====================================================    
    LET row = sheet.createRow(0)    #創建行
    CALL row.setHeightInPoints(40)  #設置行高
    
    FOR l_i = 0 TO 8
      LET cell = row.createCell(l_i)    
      CALL cell.setCellStyle(style5)
      LET l_str = "在制項目標準成本明細"
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
    END FOR
    #合併單元格
    LET region = CellRangeAddress.create(0,0,0,8) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
    CALL sheet.addMergedRegion(region)                  #執行上述單元格條件合併
    LET region = CellRangeAddress.create(0,0,9,17) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
    CALL sheet.addMergedRegion(region)                  #執行上述單元格條件合併
    
    #第二行
    LET row = sheet.createRow(1)    #創建行
    CALL row.setHeightInPoints(30)  #設置行高
    FOR l_i = 0 TO 8
      LET cell = row.createCell(l_i)    
      CALL cell.setCellStyle(style8)
      LET l_str = "成品"
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
    END FOR
    FOR l_i = 9 TO 17
      LET cell = row.createCell(l_i)    
      CALL cell.setCellStyle(style8)
      LET l_str = "各工段"
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
    END FOR
    #合併單元格
    LET region = CellRangeAddress.create(1,1,0,8) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
    CALL sheet.addMergedRegion(region)                  #執行上述單元格條件合併
    LET region = CellRangeAddress.create(1,1,9,17) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
    CALL sheet.addMergedRegion(region)                  #執行上述單元格條件合併
    #第三行
    LET row = sheet.createRow(2)    #創建行
    CALL row.setHeightInPoints(20)  #設置行高
    FOR l_i = 0 TO 17
      CASE l_i 
        WHEN 0  LET l_str = '主件料號'
        WHEN 1  LET l_str = '主件品名'
        WHEN 2  LET l_str = '主件規格'
        WHEN 3  LET l_str = '合計工時(秒)'
        WHEN 4  LET l_str = '合計材料費'
        WHEN 5  LET l_str = '未稅單價'
        WHEN 6  LET l_str = '合計人工費'
        WHEN 7  LET l_str = '人力占比'
        WHEN 8  LET l_str = '材料占比'
        WHEN 9  LET l_str = '料號'
        WHEN 10 LET l_str = '品名'
        WHEN 11 LET l_str = '規格'
        WHEN 12 LET l_str = '標準工時(秒)'
        WHEN 13 LET l_str = '標準機時(秒)'
        WHEN 14 LET l_str = '人力成本'
        WHEN 15 LET l_str = '材料成本'
        WHEN 16 LET l_str = '工時占比'
        WHEN 17 LET l_str = '材料占比'
      END CASE 
      LET cell = row.createCell(l_i)    
      CALL cell.setCellStyle(style7)
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
    END FOR
    
 LET l_sql = "SELECT DISTINCT xcag004 FROM xcag_t
              WHERE xcagent = ",g_enterprise," AND xcagsite = '",g_site,"' 
              AND xcag001 = '101' AND ",g_master.wc
 PREPARE l_imaa001_code FROM l_sql
 DECLARE l_imaa001_curs CURSOR FOR l_imaa001_code 
     
 LET l_imaa001 = ''
 LET l_row1 = 2
 #開始迴圈
 FOREACH l_imaa001_curs INTO l_imaa001
    #傳遞參數取數據
    CALL cxcr600_get_data(l_imaa001) RETURNING r_docno
    #第三行下麵的數據
    LET l_cnt = r_docno.getLength()  #獲取數組總筆數
    
    FOR l_i = 1 TO l_cnt
       #創建行
       LET l_row = l_i + l_row1
       LET row = sheet.createRow(l_row)    #創建行
       CALL row.setHeightInPoints(20)      #設置行高
       
       #主件料號
       LET cell = row.createCell(0)    
       CALL cell.setCellStyle(style3)
       LET l_str = r_docno[l_i].l_xcah004
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(0,l_length)
       
       #主件品名
       LET cell = row.createCell(1)    
       CALL cell.setCellStyle(style3)   
       LET l_str = r_docno[l_i].l_xcah004_desc
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(1,l_length)
       
       #主件規格
       LET cell = row.createCell(2)    
       CALL cell.setCellStyle(style3)   
       LET l_str = r_docno[l_i].l_xcah004_1_desc
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(2,l_length)
       
       #合計工時
       LET cell = row.createCell(3)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcaeud015_sum
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(3,l_length)
       
       #合計材料費
       LET cell = row.createCell(4)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcah031_a_sum
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(4,l_length)
       
       #未稅單價
       LET cell = row.createCell(5)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_price_ws
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(5,l_length)
       
       #合計人工費
       LET cell = row.createCell(6)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcah031_b_sum
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(6,l_length)
       
       #人力占比
       LET cell = row.createCell(7)    
       CALL cell.setCellStyle(style4_3)   
       LET l_num = r_docno[l_i].l_xcah031_b_szb
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(7,l_length)
       
       #材料占比
       LET cell = row.createCell(8)    
       CALL cell.setCellStyle(style4_3)   
       LET l_num = r_docno[l_i].l_xcah031_a_szb
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(8,l_length)
       
       #料號
       LET cell = row.createCell(9)    
       CALL cell.setCellStyle(style3)   
       LET l_str = r_docno[l_i].l_xcah022
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(9,l_length)
       
       #品名
       LET cell = row.createCell(10)    
       CALL cell.setCellStyle(style3)   
       LET l_str = r_docno[l_i].l_xcah022_desc
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(10,l_length)
       
       #規格
       LET cell = row.createCell(11)    
       CALL cell.setCellStyle(style3)   
       LET l_str = r_docno[l_i].l_xcah022_1_desc
       CALL cell.setCellValue(l_str)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(11,l_length)
              
       #標準工時
       LET cell = row.createCell(12)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcaeud015
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(12,l_length)
       
       #標準機時
       LET cell = row.createCell(13)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcaeud016
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(13,l_length)
       
       #人力成本
       LET cell = row.createCell(14)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcah031_b
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(14,l_length)
       
       #材料成本
       LET cell = row.createCell(15)    
       CALL cell.setCellStyle(style4)   
       LET l_num = r_docno[l_i].l_xcah031_a
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(15,l_length)
       
       #工時占比
       LET cell = row.createCell(16)    
       CALL cell.setCellStyle(style4_3)   
       LET l_num = r_docno[l_i].l_xcah026
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(16,l_length)
             
       #材料占比
       LET cell = row.createCell(17)    
       CALL cell.setCellStyle(style4_3)   
       LET l_num = r_docno[l_i].l_xcah025
       CALL cell.setCellValue(l_num)
       LET l_length = (11.33+0.72)*300
       CALL sheet.setColumnWidth(17,l_length)
       
    END FOR
    
    #合併行

    #起始行
    LET l_n2 = l_row1+1
    #客戶分組合併
    FOR l_i = 0 TO 8
    FOR l_n = 1 TO l_cnt
      #當前行比較上一行的客戶數據對比
      IF l_n > 1  THEN
         LET l_n1 = l_n - 1 #上一筆
         IF r_docno[l_n].l_xcah004 <> r_docno[l_n-1].l_xcah004 THEN
           LET l_m_n = l_n1 + l_row1   
           #第l_i列
           LET region = CellRangeAddress.create(l_n2,l_m_n,l_i,l_i) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
           CALL sheet.addMergedRegion(region)                 #執行上述單元格條件合併
         END IF
       
      END IF
      
      IF l_n = l_cnt AND l_n > 1 THEN   #到最後一行了
         IF r_docno[l_n].l_xcah004 = r_docno[l_n-1].l_xcah004 THEN
            LET l_m_n = l_n + l_row1
            LET region = CellRangeAddress.create(l_n2,l_m_n,l_i,l_i) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
            CALL sheet.addMergedRegion(region)                 #執行上述單元格條件合併
         END IF
             
      END IF
    END FOR
    END FOR 
    LET l_row1 = l_row1+l_cnt
    
    LET l_imaa001 = '' 
    
 END FOREACH 
 LET l_row = l_row1+1
 LET row = sheet.createRow(l_row)    #創建行
    CALL row.setHeightInPoints(20)  #設置行高
    
#    FOR l_i = 0 TO 2
      LET cell = row.createCell(1)    
      CALL cell.setCellStyle(style9)
      LET l_str = "表單編號：7-6-2-T_002（ZJA） V01"
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
#    END FOR
#    FOR l_i = 15 TO 17
      LET cell = row.createCell(16)    
      CALL cell.setCellStyle(style9)
      LET l_str = "惠州至精精密技術有限公司"
      CALL cell.setCellValue(l_str)
      LET l_length = (11.33+0.72)*300
      CALL sheet.setColumnWidth(l_i,l_length)
#    END FOR
#    #合併單元格
#    LET region = CellRangeAddress.create(l_row,l_row,0,2) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
#    CALL sheet.addMergedRegion(region)                  #執行上述單元格條件合併
#    LET region = CellRangeAddress.create(l_row,l_row,15,17) #單元格合併（起始行號，終止行號， 起始列號，終止列號）0-4共有五個單元格，對應實際表格即為將第一行的第一列到第五列合併
#    CALL sheet.addMergedRegion(region)
 
 CALL sheet.createFreezePane( 9, 3, 0, 0 )   #凍結視窗
 CALL cxcr600_drop_tmp()
 
 LET l_time = cl_get_current()
 LET l_string = ''
 SELECT to_char(l_time,'yyyymmddhh24miss') INTO l_string FROM DUAL
  
 LET l_xlsname = l_string,"cxcr600.xlsx"
 LET l_xlsfile = FGL_GETENV("TEMPDIR")||'/',l_xlsname
 LET fo = FileOutputStream.create(l_xlsfile)
 CALL workbook.write(fo)
 CALL fo.close()
 
 
 LET l_target = os.Path.join(FGL_GETENV("TEMPDIR"),l_xlsname)
 LET l_fileloc = os.Path.join(g_showmsg,l_xlsname CLIPPED)
 CALL FGL_PUTFILE(l_target ,l_fileloc)
 
 LET l_str = cl_getmsg('adz-00701',g_lang)
 LET l_str = cl_str_replace(l_str,'%1',l_xlsname)
 CALL cl_ask_confirm3("",l_str)
 
END FUNCTION

PRIVATE FUNCTION cxcr600_get_price(p_imaa001)
  DEFINE  p_imaa001    LIKE  imaa_t.imaa001
  DEFINE  p_time       LIKE  type_t.chr100
  DEFINE  r_price      LIKE  type_t.num20_6
  DEFINE  r_price_ws   LIKE  type_t.num20_6
  DEFINE  r_rate       LIKE  type_t.num20_6
  DEFINE  l_date       LIKE  type_t.dat
  DEFINE  l_bz         LIKE  type_t.chr100    #幣種
  DEFINE  l_exrate     LIKE  type_t.num20_6   #匯率
  DEFINE  l_xmdt008    LIKE  xmdt_t.xmdt008   #含稅否
  
  
  LET l_date = g_today
  LET r_price = 0
  LET r_rate  = 0 
  LET l_exrate = 0
  LET l_xmdt008 = ''
  
  #判斷核價單是否存在單價
  SELECT NVL(xmdu011,0),NVL(xmdu012,0),xmdt005,xmdt008 INTO r_price,r_rate,l_bz,l_xmdt008 FROM (
  SELECT xmdt015,xmdu011,xmdu012,xmdt005,xmdt008 FROM xmdu_t 
  LEFT JOIN xmdt_t ON xmdtent = xmduent AND xmdtsite = xmdusite AND xmdtdocno = xmdudocno
  WHERE xmdtent = g_enterprise AND xmdtsite = g_site AND xmdtstus = 'Y' AND xmdu002 = p_imaa001 AND xmdu011 <> 0
  AND xmdt015 <= l_date ORDER BY XMDT015 DESC ) WHERE rownum = 1
  #存在對應的幣種
  IF NOT cl_null(l_bz) THEN
    LET g_prog = 'axmt540'
    CALL s_axmt540_get_exchange('2',l_bz,l_date) RETURNING l_exrate
    LET g_prog = 'cxcr600'
  END IF
  #獲取到單價
  IF NOT cl_null(r_price) AND r_price <> 0 AND NOT cl_null(l_bz) THEN
     IF l_xmdt008 = 'Y' THEN   #含稅單價
       LET r_price = r_price*l_exrate
       LET r_price_ws = r_price/(1+(r_rate/100))
       RETURN r_price_ws
     END IF 
     IF l_xmdt008 = 'N' THEN   #不含稅單價
       LET r_price_ws = r_price*l_exrate
       LET r_price = r_price*l_exrate*(1+(r_rate/100))
       RETURN r_price_ws
     END IF
  END IF
  
  #當核價單沒有單價的時候，從訂單去尋找
  SELECT NVL(xmdc015,0)*xmda016,NVL(xmdc017,0) INTO r_price,r_rate FROM (
  SELECT xmdc001,xmdc015,xmdc017,xmdadocdt,xmda016 FROM xmdc_t 
  LEFT JOIN xmda_t ON xmdcent = xmdaent AND xmdcsite = xmdasite AND xmdadocno = xmdcdocno
  WHERE xmdaent = g_enterprise AND xmdasite = g_site AND xmdastus NOT IN  ('N','X') AND xmdc001 = p_imaa001 AND xmdc015 <> 0
  AND xmdadocdt <= l_date ORDER BY xmdadocdt DESC) WHERE rownum = 1
  
  #獲取到單價
  IF NOT cl_null(r_price) AND r_price <> 0 THEN
     LET r_price_ws = r_price/(1+(r_rate/100))
     RETURN r_price_ws
  END IF
  
  LET r_price = 0
  LET r_price_ws  = 0

  RETURN r_price_ws
END FUNCTION

PRIVATE FUNCTION cxcr600_drop_tmp()
  DROP TABLE cxcr600_tmp;
END FUNCTION

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • python multiprocessing庫使用記錄
    # python multiprocessing庫使用記錄 需求是想並行調用形式化分析工具proverif，同時發起對多個query的分析（378個）。實驗室有40核心80線程的伺服器（雙cpu，至強gold 5218R*2)。 觀察到單個命令在分析時記憶體占用不大，且只使用單核心執行，因此考慮同時調 ...
  • 使用Python打造屬於你的ikun音樂播放器，簡直不要太好用
    首先聲明，我不是小黑子，我不是小黑子！ 作為一個ikun，時刻都在想著我們家姐姐！ 這不上次用Python做了一個ikun飛機大戰，今天再給大家整活一手，Python tkinter開發一個專屬ikun音樂播放器，這樣就能時刻看到姐姐了。 咱們來看看效果 代碼實現 今天要做的就是上面的簡易音樂播放器 ...
  • 靜態鏈接——編譯和鏈接
    # 一、編譯和鏈接的過程 ## 1、GCC生成可執行文件的總體過程 在日常的開發過程中，IDE總是會幫我們將編譯和鏈接合併，一鍵式的執行,即使在liunx中，使用命令行來編譯一個源文件也只是簡單的一句"gcc hello.c"。我們並沒有過多的關註編譯和鏈接的運行機制和機理，我想從本質出發，深入瞭解 ...
  • 多線程知識：三個線程如何交替列印ABC迴圈100次
    本文博主給大家講解一道網上非常經典的多線程面試題目。關於三個線程如何交替列印ABC迴圈100次的問題。 > 下文實現代碼都基於Java代碼在單個JVM內實現。 ## 問題描述 給定三個線程，分別命名為A、B、C，要求這三個線程按照順序交替列印ABC，每個字母列印100次，最終輸出結果為： ``` A ...
  • [滲透測試]—2.1 常見的安全術語和概念
    # 背景 業務開發需要判斷業務狀態是否在30、40、50、60的集合內，所以寫了以下代碼 ``` int[] inLiq = {30,40,50,60}; return Arrays.asList(inLiq).contains(o.getOrderStatus()); ``` 自我Review代碼 ...
  • Java 對象創建流程、this 關鍵字
    # Java 對象創建流程、this 關鍵字 # 1. Java 對象記憶體創建流程 > ## 1. 先載入類信息(.class 的文件)，只會載入一次 > > ## 2. 在堆空間里分配對象的記憶體空間 > > ## 3.1 進行預設的初始化即數據類型本身的預設值 > > ## 3.2 進行顯式的初始 ...
  • VSCode中打開NodeJS項目自動切換對應版本的配置
    這幾年搞了不少靜態站點，有的是Hexo的，有的是VuePress的。由於不同的主題對於NodeJS的版本要求不同，所以本機上不少NodeJS的版本。 關於如何管理多個NodeJS版本，很早之前就寫過用nvm來管理的相關文章，這裡就不贅述了，有需要的可以看這篇[Node.js環境搭建](https:/ ...
  • 學習C++這一篇就夠了（進階篇）
    **在Python Web開發領域，Django框架的地位猶如璀璨的明星，其全面、高效和安全的特性使其在全球範圍內廣受歡迎。本文將全面解析Django框架的預設文件，並深入探討每個文件及其組成的意義和用途，透徹展示這個強大框架的文件結構和設計原理。** 首先，讓我們看一下創建一個新的Django項目 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類