篠崎愛(Shinozaki Ai) : 시노자키 아이

분류 전체보기 +84

REPLACE  PROCEDURE LDVIDB.UP_ROUNGE_USE_CUST  

(

  IN ST_CD  CHAR(4)    --관리점코드     --원 소스 ST_CD

, IN LOUNGE_CD  CHAR(2)    --라운지코드  

, IN FAMY_GB CHAR(1) --가족구분  

, IN LOUNGE_ST CHAR(8)    --라운지이용기간 - 시작    

, IN LOUNGE_ET CHAR(8)    --라운지이용기간 - 종료    

)

/*

수정일: 2014-1-27

작성자: 김우종


시스템명: VIP

업무명: 마이포지션

화면ID: RG2300M

메뉴: 마이포지션 -> 우수고객서비사관리 -> 라운지이용내역조회


SP설명: 라운지 이용내역 조회

SP실행: CALL LDVIDB.UP_ROUNGE_USE_CUST('0001' , '1111', '1', '20111111' , '20141212');

*/

DYNAMIC RESULT SETS 2

BEGIN

BEGIN

  DECLARE cur1 CURSOR WITH RETURN ONLY FOR

LOCKING ROW FOR ACCESS

  SELECT

   SUBSTR(B.USE_DT,1, 4) || '-' || SUBSTR(B.USE_DT, 5,2) || '-' || SUBSTR(B.USE_DT, 7,2)

  AS USE_DT--이용일자       

    , SUBSTR(B.ETR_TM, 1, 2) || ':' || SUBSTR(B.ETR_TM, 3, 2)

    AS ETR_TM--입실시간 

, A.KOR_NM AS CUST_NM --고객이름

, A.CUST_NO AS CUST_NUM --고객번호

--, A.RGST_NO AS RGST_NO  

, (CASE A.FAMY_GB WHEN '1' THEN '본인' 

    WHEN '2' THEN '가족' 

   ELSE '' END) AS ME_FAMILY --가족구분

,(SELECT sel_lvl_nm   

FROM   LDVMDB.VI_SEL_LVL_CD_V

WHERE sel_flg_cd = '1' 

AND   use_yn = 'Y'

AND sel_lvl_cd = A.SEL_LVL_CD)                       AS RNK_NM --등급

, (SELECT b_code_nm  

   FROM LDVMDB.VI_BASE_CODE_V 

    WHERE p_code ='MY016'

    AND b_code = A.INNER_GRD)  AS INNER_RNK_NM --내부등급

        ,CASE B.MVG_ROOM_ST_CD WHEN '9999' THEN '전점'

          ELSE COALESCE ((SELECT store_nm

  FROM   LDVMDB.PO_ST_V

      WHERE  store_cd = B.MVG_ROOM_ST_CD),'')   END ST_CD --이용점

FROM LDVMDB.VI_MVG_CUST_V AS A 

INNER JOIN  LDVMDB.VI_MVG_ROOM_V AS B 

ON  A.CUST_NO = B.INTG_CUST_ID

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간 

AND (ST_CD = :ST_CD OR :ST_CD = '9999')

   AND (B.LOUNGE_CD = :LOUNGE_CD OR  :LOUNGE_CD= '99') --라운지코드

AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0') 

ORDER BY B.USE_DT DESC;

 OPEN cur1;

END;


BEGIN

DECLARE cur2 CURSOR WITH RETURN ONLY FOR

LOCKING ROW FOR ACCESS

SELECT COUNT(A.CUST_NO) AS CNT  

,  SUM(B.ACCMPNY_CNT)       AS CNT2   --동반고객수       

 FROM LDVMDB.VI_MVG_CUST_V AS A 

      INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B 

   ON A.CUST_NO = B.INTG_CUST_ID  

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간  

AND (ST_CD = :ST_CD OR :ST_CD = '9999')  

  AND B.LOUNGE_CD = :LOUNGE_CD OR  :LOUNGE_CD= '99' --라운지코드    

  AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0');

OPEN CUR2;

END;

END;

REPLACE  PROCEDURE LDVIDB.UP_NOTICE_GETNOTICELIST   --Notice_GetNoticeList

(

  IN SearchOp VARCHAR(7)

 ,IN SearchText VARCHAR(1000)

)

/*

수정일: 2014-1-10

작성자: 김우종


시스템명: VIP

업무명:  시스템관리

화면ID: CM0100M

메뉴: 시스템관리 -> 공지사항 관리 -> 공지사항 


SP설명: 권한 코드

SP실행: CALL LDVIDB.UP_NOTICE_GETNOTICELIST('ALL','명동점');

*/


DYNAMIC RESULT SETS 1

BEGIN


  DECLARE   STRSQL     VARCHAR(8000);

  SET STRSQL    =  ' ';

  

IF SearchOp = 'ALL'  THEN-- 전체

SET STRSQL =

'SELECT 

  notice_id

   ,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm    

   ,Ntitle

   , usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

   FROM LDVMDB.VI_NOTICE_V a

   WHERE use_yn = ''Y''

   ORDER BY  notice_id DESC; ';

END IF;


IF SearchOp = 'TITLE' THEN-- 제목

SET STRSQL =

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

 SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

    use_yn = ''Y'' AND NTitle LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;


IF  SearchOp = 'CONTE'  THEN-- 내용

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND contents LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;


IF SearchOp = 'USER' THEN  -- 사용자

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;

IF SearchOp = 'Total' THEN  -- 전체 공지만 조회

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''

AND sys_cd = 00

ORDER BY  notice_id DESC;';

END IF;

IF SearchOp = 'Sys' THEN  -- 시스템 공지만 조회

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' 

AND usr_id LIKE ''%' || SearchText || '%''

AND sys_cd <> ''00''

ORDER BY  notice_id DESC;';

END IF;


BEGIN

DECLARE CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR SQL_STATEMENT;

PREPARE SQL_STATEMENT FROM STRSQL;

OPEN CUR1;

END;

END;

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간 




시작 시간 종료 시간 사이에 값 조회 .

  1. </P><P>// Create data table  
  2. DataTable dt = new DataTable();  
  3. dt.Columns.Add("First Name");  
  4. dt.Columns.Add("Second Name");  
  5. dt.Columns.Add("Salary");  
  6. dt.Columns.Add("Taxes");  
  7.   
  8. dt.Rows.Add("Joe","Smith",120000,1000);  
  9. dt.Rows.Add("Mariah","Carry",1500000,10000);  
  10.   
  11.   
  12. // Bind Datasource  
  13. ultraGrid1.DataSource = dt;  
  14.   
  15.   
  16. //  Create Groups  
  17. Infragistics.Win.UltraWinGrid.UltraGridGroup group1 = new Infragistics.Win.UltraWinGrid.UltraGridGroup(  "Personal Info",1); // key + unique id  
  18. Infragistics.Win.UltraWinGrid.UltraGridGroup group2 = new Infragistics.Win.UltraWinGrid.UltraGridGroup(  "Salary Info",2);              
  19.   
  20. // Add Groups to BAND  
  21. ultraGrid1.DisplayLayout.Bands[0].Groups.Add( group1 );  
  22. ultraGrid1.DisplayLayout.Bands[0].Groups.Add( group2 );  
  23.   
  24. // *** Assign columns to groups **  
  25.   
  26. // option 1 Through gropus Columns  
  27. group1.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[0] );  
  28. group1.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[1] );  
  29. group2.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[2] );  
  30. group2.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[3] );  
  31.   
  32. // option 2 Columns group  
  33. //ultraGrid1.DisplayLayout.Bands[0].Columns[0] .Group = group1;  
  34.  </P>  


            DialogResult dr = MessageBox.Show("종료하시겠습니까?", "알림", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);


            if (dr == DialogResult.OK)

            {

                MessageBox.Show("정상적으로 종료되었습니다.");

            }

            else

            {

                MessageBox.Show("종료안한다고?");

            }

            DataTable dt = new DataTable();

            dt.Columns.Add("INSERT_ST");

            dt.Columns.Add("CUST_NUM");

            dt.Columns.Add("RETURN_WHY");

            GridHandler.FillGrid(gridList, dt);

                콤보박스이름.SelectedIndex = 설정 값;

SELECT COUNT(A.CUST_NO) AS CNT  

,  SUM(B.ACCMPNY_CNT) AS CNT2   --동반고객수       

 FROM LDVMDB.VI_MVG_CUST_V AS A 

      INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B 

   ON A.CUST_NO = B.INTG_CUST_ID  

WHERE  B.USE_DT BETWEEN :ROUNGE_ST AND :ROUNGE_ET --이용기간  

>> AND (ST_CD = :ST_CD OR :ST_CD = '9999')  

>>   AND B.LOUNGE_CD = :LOUNGE_CD OR  :LOUNGE_CD= '99' --라운지코드    

  AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0');



라벨이름.text = 넣을 문자

  // 이미지파일을여는것이다

    // 포멧은파일을우선으로했으나다른모든파일도검색이될수있게

    // 구현하였습니다

    OpenFileDialog openFile = new OpenFileDialog();

    openFile.DefaultExt = "jpg"

    openFile.Filter = "Graphics interchange Format (*.jpg)|*.jpg|All files(*.*)|*.*"

    openFile.ShowDialog();

    // 만약파일이선택되었으면

    if (openFile.FileName.Length > 0)

    {

       // 피쳐박스에뿌려주기

       Image image = Image.FromFile(openFile.FileName);

       // Image 클래스뿐아니라위에서상속된클래스로도

       // 이미지를보여줄수있다

       Bitmap image = new Bitmap(openFile.FileName);

       //Image image = Image.FromStream(stream);

       // 여기서은이미지정보가포함된스트림이다

       picbox_main.Image = image;

    }