[PB]暫存 Code 參考

window_function 取得row資訊後做迴圈運算

long   ll_count

string  ls_seq_no, ls_out, ls_part, ls_partnum, ls_card, ls_postal, ls_addr1, ls_addr2, ls_addr3

decimal ld_out, ld_turn

tab_1.tabpage_1.dw_4.Reset()//重新產生dw4之前先清除

long ll_dw3_count,ll_dw3_rowcount,ll_dw3_row

string ls_dw3_out,ls_dw3_part,ls_dw3_pick

ll_dw3_rowcount  = tab_1.tabpage_1.dw_3.rowcount()

ls_seq_no = '000'

ll_count = 0

declare curd_lsf200 cursor for 

  select a.part_no, a.part_num, a.card_no, a.out_no, a.postal, a.addr1, a.addr2, a.addr3

from lsf200 a

where 1 = 1

  and a.proj_no = :fs_proj

  and isnull(a.lsp170_yn,'N') <> 'Y'

order by a.part_no, a.out_no, a.card_no;

open  curd_lsf200;

fetch curd_lsf200 into :ls_part, :ls_partnum, :ls_card, :ls_out, :ls_postal, :ls_addr1, :ls_addr2, :ls_addr3 ;

do while sqlca.sqlcode = 0

ll_dw3_count = 1

do while ll_dw3_count <= ll_dw3_rowcount

ll_dw3_row = tab_1.tabpage_1.dw_3.GetRowFromRowId(ll_dw3_count)

ls_dw3_out = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"out_no")

ls_dw3_part = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"part_no")

ls_dw3_pick = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"ls_pick")

if ls_dw3_pick = 'Y' and ls_dw3_out = ls_out and  ls_dw3_part = ls_part then

ll_count = ll_count + 1

ls_seq_no  = string( long(ls_seq_no) + 1 ,'000')

tab_1.tabpage_1.dw_4.insertrow(0)

tab_1.tabpage_1.dw_4.setitem(ll_count, "seq_no", ls_seq_no)

tab_1.tabpage_1.dw_4.setitem(ll_count, "part_no", ls_part)

tab_1.tabpage_1.dw_4.setitem(ll_count, "part_num", ls_partnum)

tab_1.tabpage_1.dw_4.setitem(ll_count, "card_no", ls_card)

tab_1.tabpage_1.dw_4.setitem(ll_count, "out_no", ls_out)

tab_1.tabpage_1.dw_4.setitem(ll_count, "postal", ls_postal)

tab_1.tabpage_1.dw_4.setitem(ll_count, "addr1", ls_addr1)

tab_1.tabpage_1.dw_4.setitem(ll_count, "addr2", ls_addr2)

tab_1.tabpage_1.dw_4.setitem(ll_count, "addr3", ls_addr3)

tab_1.tabpage_1.dw_4.setitem(ll_count, "ls_pick", 'Y')

end if

ll_dw3_count = ll_dw3_count+1

loop

fetch curd_lsf200 into :ls_part, :ls_partnum, :ls_card, :ls_out, :ls_postal, :ls_addr1, :ls_addr2, :ls_addr3 ;

loop 

close curd_lsf200;

return

buttenchecked

//IFITB00F-HD000000000860 新增條件：分公司別和部門別//業務員

//分公司

if dwo.name = "pb_comp" then

lstr.pc_cnt = 2

openwithparm( w_pick_company, lstr, gw_win )

lstr = Message.PowerObjectParm

if lstr.pc_cnt > 0 then

ls_code1 = lstr.ps_code1[1] 

tab_1.tabpage_1.dw_1.setitem( row, "comp_no",  ls_code1)

tab_1.tabpage_1.dw_1.setitem( row, "comp_name_a",   lstr.ps_code2[1]  )

tab_1.tabpage_1.dw_1.setcolumn("comp_no")

   else

tab_1.tabpage_1.dw_1.setfocus()

tab_1.tabpage_1.dw_1.setcolumn("comp_no")

   end if

tab_1.tabpage_1.dw_1.setfocus()

tab_1.tabpage_1.dw_1.setcolumn("comp_no")

goto buttonclicked_exit

end if

//部門別

if dwo.name = "pb_dept" then

lstr.pc_cnt = 2

openwithparm( w_pick_dept, lstr, gw_win )

lstr = Message.PowerObjectParm

// 有挑選資料

if lstr.pc_cnt > 0 then

ls_code1 = lstr.ps_code1[1]

tab_1.tabpage_1.dw_1.setitem(row, "dept_no", ls_code1)

   tab_1.tabpage_1.dw_1.setitem(row, "dept_name", lstr.ps_code2[1])

tab_1.tabpage_1.dw_1.setcolumn("dept_no")

else

tab_1.tabpage_1.dw_1.setfocus()

tab_1.tabpage_1.dw_1.setcolumn("dept_no")

   end if

tab_1.tabpage_1.dw_1.setfocus()

tab_1.tabpage_1.dw_1.setcolumn("dept_no")

goto buttonclicked_exit

end if

//業務員

IF dwo.name = "pb_emp" THEN  

lstr.pc_cnt = 1

lstr.ps_where="isnull(baf004.emp_yn,'N') = 'Y' and isnull(baf004.leave_date,'') = ''"

openwithparm( w_pick_emp, lstr, gw_win )

lstr = Message.PowerObjectParm

     IF lstr.pc_cnt > 0 THEN 

is_change = "Y"

   tab_1.tabpage_1.dw_1.setitem( row, "emp_id"   ,   lstr.ps_code1[1])

      tab_1.tabpage_1.dw_1.setitem( row, "emp_cname"  , lstr.ps_code2[1])

//    tab_1.tabpage_1.dw_1.setitem( row, "comp_no"  ,   lstr.ps_code3[1])

// tab_1.tabpage_1.dw_1.setitem( row, "comp_name_a", lstr.ps_code4[1])

//

//      tab_1.tabpage_1.dw_1.setitem( row, "dept_no"  ,   lstr.ps_code5[1])  

// tab_1.tabpage_1.dw_1.setitem( row, "dept_name",   lstr.ps_code6[1]) 

//debug

//err_msg(63,2,'@1'+lstr.ps_code1[1]+'AAA'+lstr.ps_code2[1]+'AAA'+lstr.ps_code3[1]+'AAA'+lstr.ps_code4[1]+'AAA'+lstr.ps_code5[1]+'AAA'+lstr.ps_code6[1]+'AAA'+lstr.ps_code7[1]+'AAA'+'@')

//is_err = 'Y'

  ELSE 

tab_1.tabpage_1.dw_1.setfocus()

tab_1.tabpage_1.dw_1.setcolumn("emp_no")

goto buttonclicked_exit

   END if

//tab_1.tabpage_1.dw_1.setcolumn("eng_emp")

//tab_1.tabpage_1.dw_1.setfocus()

goto buttonclicked_exit

END if

itemchange

//IFITB00F-HD000000000860 新增條件：分公司別和部門別//業務員

//分公司

case "comp_no"

//ls_compno ,ls_compnm 

ls_compno = tab_1.tabpage_1.dw_1.getitemstring(row,"comp_no")

select comp_name_a into :ls_compnm from pu_company where comp_no = :ls_compno     

using sqlca;

if ls_compnm <> ''

tab_1.tabpage_1.dw_1.setitem( row, "comp_name_a", ls_compnm  )

tab_1.tabpage_1.dw_1.setcolumn("comp_no")

   else

err_msg(2,2,"@@1" + "此~公~司~代~號~不~存~在~﹐請~重~新~輸~入~﹗ " + "@")

tab_1.tabpage_1.dw_1.setitem( row, "comp_no", ""  )

goto itemchanged_exit

   end if

//部門別 ls_deptno, ls_deptnm

case "dept_no"

   ls_deptno = tab_1.tabpage_1.dw_1.getitemstring(row,"dept_no")

select dept_name into :ls_deptnm  from baf002 where dept_no = :ls_deptno  ;

//select comp_name_a into :ls_compnm from pu_company where comp_no = :ls_compno     

using sqlca;

if ls_compnm <> ''

tab_1.tabpage_1.dw_1.setitem( row, "dept_name", ls_deptnm  )

   else

err_msg(2,2,"@@1" + "此~部~門~代~號~不~存~在~﹐請~重~新~輸~入~﹗ " + "@")

tab_1.tabpage_1.dw_1.setitem( row, "dept_no", ""  )

goto itemchanged_exit

   end if

//業務員

// ls_empid, ls_empnm

case "emp_id"

ls_empid = tab_1.tabpage_1.dw_1.getitemstring(row,"emp_id")

select emp_cname into :ls_empnm from baf004 

where emp_id = :ls_empid 

and isnull(baf004.emp_yn,'N') = 'Y' 

and isnull(baf004.leave_date,'') ='';

using sqlca;

if ls_compnm <> ''

tab_1.tabpage_1.dw_1.setitem( row, "emp_cname", ls_empnm  )

   else

err_msg(2,2,"@@1" + "此~員~工~代~號~不~存~在~﹐請~重~新~輸~入~﹗ " + "@")

tab_1.tabpage_1.dw_1.setitem( row, "dept_no", ""  )

goto itemchanged_exit

   end if

[PB]the column prefix '.' does not match

 

the column prefix '.' does not match

1. select table full name

2. add ligin propertiel

[SQL][SSIS]List for SQL Server SSIS project and packages name

 SELECT 

b.[name] as [專案名稱]

,a.[name] as [dtsx名稱]

FROM SSISDB.catalog.packages as a

left join SSISDB.catalog.projects as b on a.project_id = b.project_id

order by b.[name] asc  ,a.[name] asc

[PB]練習語法(不斷更新)

20221013    li_control = 1 非常重要，可直接選點選該行

//當kind = 1 (LC)必須在phf170s有資料 =0筆 清空欄位並錯誤訊息  

//當kind = 2 (TT)必須在phf170s沒有資料 < 1筆 否則清空欄位並錯誤訊息  

long  ll_row2 

string ls_order_no2 , ls_seq_no2

int ll_cnt

ll_row2 = tab_1.tabpage_1.dw_1s.getrow()

ls_order_no2    = tab_1.tabpage_1.dw_1s.getitemstring(ll_row2,"order_no")

select count(*) into :ll_cnt 

from phf170s

where phf170s.order_no = :ls_order_no2

using sqlca;

if ls_kind = '1' and ll_cnt=0 then

err_msg(2,2,"@@1"+"此~訂~購~單~不~為~類~別~LC"+"@")

li_control = 1

tab_1.tabpage_1.dw_1s.setcolumn("order_no")

tab_1.tabpage_1.dw_1s.setitem(row, "order_no", '')

goto itemchanged_exit

elseif ls_kind = '2' and ll_cnt>0 then

err_msg(2,2,"@@1"+"此~訂~購~單~不~為~類~別~TT"+"@")

li_control = 1

tab_1.tabpage_1.dw_1s.setcolumn("order_no")

tab_1.tabpage_1.dw_1s.setitem(row, "order_no", '')

goto itemchanged_exit

end if

20220905 要另外解開不然所有按鈕不能按

20220901 選取全部column迴圈設定(會排除按鈕butten)                

ll_col = long(tab_1.tabpage_1.dw_1.describe("datawindow.column.count"))

for ll_i = 1 to ll_col

tab_1.tabpage_1.dw_1.SetTabOrder(ll_i, 0)

next

20220830 在pb中insert  

insert into trf100_mail_20220830 ( send_no, keyin_emp,     crt_time,  crt_user ) 

  values ( :ls_send_no, :ls_keyin_emp, getdate(), :ls_loginuid)

commit using sqlca;  //不用等到全部程式完成，直接insert

20220111

取得dw row   while loop 讀出(計算或寫入DB)

long ll_dw3_count,ll_dw3_rowcount,ll_dw3_row

ll_dw3_rowcount  = tab_1.tabpage_1.dw_3.rowcount()

ll_dw3_count = 1

do while ll_dw3_count <= ll_dw3_rowcount

ll_dw3_row = tab_1.tabpage_1.dw_3.GetRowFromRowId(ll_dw3_count)

ls_dw3_out = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"out_no")

ls_dw3_part = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"part_no")

ls_dw3_pick = tab_1.tabpage_1.dw_3.getitemstring(ll_dw3_row,"ls_pick")

ll_dw3_count = ll_dw3_count+1

loop

20220111

產生亂數 Rand ( n )   EX：1~32767

20211228  提示訊息選是或否才執行

//確認刪掉的有符合資產檔累計折舊=0才有以刪除

li_rtn = wf_check_del(ls_proj,ls_auto_nos,ls_auto_noe,ls_auto_dates,ls_auto_datee)

if li_rtn > 0 then //回傳0以上表示累計折舊大於零的筆數

err_msg(2,2,"@@1"+"累計折舊大於零無法刪除!"+"@")

//tab_1.tabpage_1.dw_1.setcolumn("out_date")

goto buttonclicked_exit

else

ls_rtn2 = wf_del_count(ls_proj,ls_auto_nos,ls_auto_noe,ls_auto_dates,ls_auto_datee)

if MessageBox('提示信息','確定刪除所選資料共'+ls_rtn2+'筆',Question! ,YesNo! , 1 ) = 1 then

wf_del(ls_proj,ls_auto_nos,ls_auto_noe,ls_auto_dates,ls_auto_datee)

MessageBox('提示信息', '資料已刪除共'+ls_rtn2+'筆')

else

MessageBox('提示信息','放棄刪除')

end if

end if

20211201

//messagebox轉型數字=>文字

messagebox("percent", string(value)+"%")

20211117 

find_text  請注意  powerbuilder6.5 不支援中文查找

20211116 

tab_1.tabpage_1.dw_1.settaborder("cust_no",  0) 

//將taborder設定為0 => 不能點選object , 設為大於0可以點選

tab_1.tabpage_1.dw_1.modify( " cust_no.background.color =  '" + gs_ucolor +  "'" )

//改變背景顏色

[SSIS] 錯誤: "ODBC 來源" 驗證失敗，傳回驗證狀態 "VS_NEEDSNEWMETADATA"

將固定名稱的欄位，往下搬，不要在第一列

[NOPI][C#]Import EXCEL to C# datatable(from xls or xlsx)

need ddl 

npoi vision 2.1.3.1

aspx   

<td colspan="3">     

                  <telerik:RadAsyncUpload ID="Q_btnEXCELChk" runat="server" MaxFileInputsCount="1" Localization-Remove="移除"

                    Localization-Select="瀏覽" ><%--Style="padding: 8px 0px 0px 0px !important; width: 220px!important; font-family: Microsoft JhengHei;"--%>

                </telerik:RadAsyncUpload>

                   

                </td>

                <td colspan="3">

                       <asp:Label ID="Label2" runat="server" Text="" Width="20%"></asp:Label>

                    <asp:Button ID="Q_btnEXCEL" runat="server" Text="匯入預算" OnClick="Q_btnEXCEL_Click"/>

                </td>

--------------------------------------------

 aspx.cs

using NPOI.HSSF.UserModel;

using NPOI.XSSF.UserModel;

using ICSharpCode.SharpZipLib.Zip;

       

protected void Q_btnEXCEL_Click(object sender, EventArgs e)

    {

        string MSG = string.Empty;

        UploadedFile file = Q_btnEXCELChk.UploadedFiles[0];

        //string FileNm = DateTime.Now.ToString("yyyyMMddhhmmss_") + this.USER.EMPNO + ".xls";

        //string fileth = "~/Apps/HDGBJ/Upload" + FileNm;

        //Q_btnEXCELChk.UploadedFiles[0].SaveAs(Server.MapPath(fileth));

        DataTable dtImport = new DataTable();

        if (file.GetExtension() == ".xls")

        {

            HSSFWorkbook workbook_I = new HSSFWorkbook(file.InputStream);

            HSSFSheet sheet_I = (HSSFSheet)workbook_I.GetSheetAt(0);

            if (sheet_I != null)

            {

                int DataRowFrom = 1;

                int DataColFrom = 0;

                int HeaderRowCount = 1;

                int RowCount = sheet_I.PhysicalNumberOfRows;

                //* 實際匯入資料筆數

                int DataRowCount = RowCount - HeaderRowCount;

                //* 表頭資料列的欄位總數 

                int DataColCount = sheet_I.GetRow(0).PhysicalNumberOfCells;

                #region 欄位

                dtImport.Columns.Add("SEG", typeof(string));

                dtImport.Columns.Add("CCID", typeof(string));

                dtImport.Columns.Add("ACCIDNM", typeof(string));

                dtImport.Columns.Add("M1", typeof(string));

                dtImport.Columns.Add("M2", typeof(string));

                dtImport.Columns.Add("M3", typeof(string));

                dtImport.Columns.Add("M4", typeof(string));

                dtImport.Columns.Add("M5", typeof(string));

                dtImport.Columns.Add("M6", typeof(string));

                dtImport.Columns.Add("M7", typeof(string));

                dtImport.Columns.Add("M8", typeof(string));

                dtImport.Columns.Add("M9", typeof(string));

                dtImport.Columns.Add("M10", typeof(string));

                dtImport.Columns.Add("M11", typeof(string));

                dtImport.Columns.Add("M12", typeof(string));

                //dtImport.Columns.Add("ITEMS", typeof(Int32));           

                //dtImport.Columns.Add("TRANSFEE", typeof(decimal));      

                #endregion

                #region 塞值--先塞入datatable在做處理

                for (int i = 1; DataRowCount > i; i++)  //列計數

                {

                    DataRow drImport = dtImport.NewRow();

                    for (int j = 0; 15 > j; j++)  //資料欄位計數

                    {

                        drImport[j] = sheet_I.GetRow(i).GetCell(j);

                        //drImport.Table.Rows[i-1][j]= sheet_I.GetRow(i).GetCell(j);

                        //dtImport.Rows[i-1][j] = 

                    }

                    dtImport.Rows.Add(drImport);

                }

                #endregion

            }

        }

        else if (file.GetExtension() == ".xlsx")

        {

            ///建立Excel 2007檔案

            XSSFWorkbook workbook_I = new XSSFWorkbook(file.InputStream);

            XSSFSheet sheet_I = (XSSFSheet)workbook_I.GetSheetAt(0);

            if (sheet_I != null)

            {

                int DataRowFrom = 1;

                int DataColFrom = 0;

                int HeaderRowCount = 1;

                int RowCount = sheet_I.PhysicalNumberOfRows;

                //* 實際匯入資料筆數

                int DataRowCount = RowCount - HeaderRowCount;

                //* 表頭資料列的欄位總數 

                int DataColCount = sheet_I.GetRow(0).PhysicalNumberOfCells;

                #region 欄位

                dtImport.Columns.Add("SEG", typeof(string));

                dtImport.Columns.Add("CCID", typeof(string));

                dtImport.Columns.Add("ACCIDNM", typeof(string));

                dtImport.Columns.Add("M1", typeof(string));

                dtImport.Columns.Add("M2", typeof(string));

                dtImport.Columns.Add("M3", typeof(string));

                dtImport.Columns.Add("M4", typeof(string));

                dtImport.Columns.Add("M5", typeof(string));

                dtImport.Columns.Add("M6", typeof(string));

                dtImport.Columns.Add("M7", typeof(string));

                dtImport.Columns.Add("M8", typeof(string));

                dtImport.Columns.Add("M9", typeof(string));

                dtImport.Columns.Add("M10", typeof(string));

                dtImport.Columns.Add("M11", typeof(string));

                dtImport.Columns.Add("M12", typeof(string));

                //dtImport.Columns.Add("ITEMS", typeof(Int32));           

                //dtImport.Columns.Add("TRANSFEE", typeof(decimal));      

                #endregion

                #region 塞值--先塞入datatable在做處理

                for (int i = 1; DataRowCount > i; i++)  //列計數

                {

                    DataRow drImport = dtImport.NewRow();

                    for (int j = 0; 15 > j; j++)  //資料欄位計數

                    {

                        drImport[j] = sheet_I.GetRow(i).GetCell(j);

                        //drImport.Table.Rows[i-1][j]= sheet_I.GetRow(i).GetCell(j);

                        //dtImport.Rows[i-1][j] = 

                    }

                    dtImport.Rows.Add(drImport);

                }

                #endregion

            }

        }

        else

        {

            this.Master.AjaxManager.Alert("檔案格式錯誤");

            MSG = "檔案格式錯誤";

            //檔案錯誤

        }

        if (MSG == "")

        {

            //整理datatable

            if (dtImport.Rows.Count > 0)

            {

                //移除沒有CCID的資料列

                for (int i = 0; dtImport.Rows.Count > i; i++)

                {

                    if (dtImport.Rows[i]["CCID"].ToString() == "")

                    {

                        // dt.Rows[i].Delete();

                        dtImport.Rows.RemoveAt(i);

                    }

                }

         

                

                db.Inport(dtImport, COMPID, Q_ddlDATAYEAR.SelectedValue.ToString(), Q_ddlYEARTYPE.SelectedValue.ToString());

            }

        }

    }

[SQL][sybase]use SQL server OPEN QUERY Sybase with parameter

 Declare @ps_houses char(2)

Declare @ps_housee char(2)

Declare @ps_dates char(8)

Declare @ps_datee char(8)

set @ps_houses = '01' 

set @ps_housee = '31'

set @ps_dates =  (select convert(varchar(8),getdate(),112))

set @ps_datee = (select convert(varchar(8),getdate(),112))

Declare @SQL_str varchar(3000)

set @SQL_str='SELECT distinct   

trf100s.send_no,      

         trf100s.tranio_no,      

         trf100s.raw_no,      

         trf100s.part_no,      

         saf300s.send_qty,      

         saf300s.scan_qty,    

         saf300s.send_qty - saf300s.scan_qty   as no_scanqty ,   

baf008.cust_no,   

baf008.cust_sname,

trf100.house_no,

baf014.house_name

    FROM trf100s  

LEFT JOIN trf100  ON trf100s.send_no = trf100.send_no   

LEFT JOIN baf008  ON baf008.cust_no = trf100s.cust_no   

LEFT JOIN saf300s ON trf100s.send_no = saf300s.send_no    

          and trf100s.tranio_no = saf300s.tranio_no   

and trf100s.part_no = saf300s.part_no

LEFT JOIN baf014 ON trf100.house_no = baf014.house_no    

   WHERE     

   saf300s.send_qty - saf300s.scan_qty   > 0   

and left(saf300s.tranio_no,2) <> ''DI''     

    and saf300s.barcode_yn = ''Y''      

and trf100.house_no between ''' + @ps_houses +''' and ''' +@ps_housee  + ''' and convert(char(8),trf100.send_date,112) between '''+ @ps_dates + ''' and '''+ @ps_datee

SET @SQL_str = 'SELECT * into TMP_LHDBTRP100 FROM OpenQuery(LHDB, ''' + REPLACE(rtrim(@SQL_str)+'''', '''', '''''')+ ''')'

    EXEC(@SQL_str)