말없제이™'

[WPF] DataTemplate 안에서 ContextMenu의 바인딩

출처 : http://chanun.tistory.com/6
데이터 템플릿 안에있는 ContextMenu에, ViewModel에 있는 속성이나 커맨드 바인딩 방법중 하나 입니다.

1. ContextMenu를 갖게 되는 패널의 Tag속성에 ViewModel DataContext 바인딩
2. ContextMenu에 PlacementTarget.Tag 값을 DataContext로 지정한다.
3. MenuItem에 해당 Command 혹은 속성 바인딩

※ 동시에 DataTemplate의 DataContext나 그 속성을 사용해야 될경우 아래 CommandParameter 바인딩 처럼 구현하면 된다.

Xaml
<DataTemplate >
 <Grid Tag="{Binding Path=DataContext, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type UserControl}}}">
  <Grid.ContextMenu>
   <ContextMenu DataContext="{Binding Path=PlacementTarget.Tag, RelativeSource={RelativeSource Self}}" >
    <MenuItem
     Header="아이템1"
     Command="{Binding Item1Command}"
     CommandParameter="{Binding DataContext, RelativeSource={RelativeSource TemplatedParent}}"
     />
    <MenuItem
     Header="아이템2"
     Command="{Binding Item2Command}"
     CommandParameter="{Binding DataContext, RelativeSource={RelativeSource TemplatedParent}}"
     />
   </ContextMenu>
  </Grid.ContextMenu>
 </Grid>
</DataTemplate>

※ ContextMenu.PlacementTarget 
ContextMenu가 열리는 위치의 기준이 되는 UIElement를 가져오거나 설정합니다. 이 속성은 종속성 속성입니다.

위에서 전체말고 바로상위단 선택값 파라메타 값 넘김.

출처 : http://stackoverflow.com/questions/8154202/pass-command-parameter-from-the-xaml

 ...
CommandParameter="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type ContextMenu}}, Path=PlacementTarget.SelectedItem}"

간단한것이 MVVM패턴에서는 바로 안먹혀서. 요리 조리 하다 시간 많이 걸림.

구글 검색 참조 : http://stackoverflow.com/questions/12540457/moving-an-item-up-and-down-in-a-wpf-list-box

//-- CS
private void btnUp_Click(object sender, EventArgs e)     
{     
if (radListBox1.SelectedIndex > 0)     
{     
int index = radListBox1.SelectedIndex;     
RadListBoxItem current = radListBox1.SelectedItem as RadListBoxItem;     
radListBox1.Items.Remove(current);     
radListBox1.Items.Insert(index - 1, current);     
radListBox1.SelectedIndex = index - 1;     
}     
}     

private void btnDown_Click(object sender, EventArgs e)     
{     
if ((radListBox1.SelectedIndex > -1) && (radListBox1.SelectedIndex < radListBox1.Items.Count - 1))     
{     
int index = radListBox1.SelectedIndex;     
RadListBoxItem current = radListBox1.SelectedItem as RadListBoxItem;     
radListBox1.Items.Remove(current);     
radListBox1.Items.Insert(index + 1, current);     
radListBox1.SelectedIndex = index + 1;     
}     
}    

//맞도록 변경해보장

//MVVM 패턴일경우 radListBox1.Items.Remove 요기서 ItemsSource 잡혀있어서 못쓴다고 날리다.
//MVVM에 맞게 수정 - 어언 7년전 C#에서 var 쓴다고 욕들먹은은 1인.

//-- XMAL
<Button
DockPanel.Dock="Top"
Content="▽"
Width="18"
Height="18"
Margin="0,0,0,5"
Command="{Binding DataContext.SltGrpItemDown, RelativeSource = {RelativeSource AncestorType={x:Type telerik:RadTabControl},AncestorLevel=2}}"
CommandParameter="{Binding ElementName=Model1SelectValue}"
/>
<telerik:RadListBox
Name="Model1SelectValue"
DockPanel.Dock="Right"
Margin="10,3,3,3"
ItemsSource="{Binding IChildSelection}"
>

//-- ViewModel
/// <summary>
/// 그룹들 위로 올리기
/// </summary>
public void OnSltGrpItemUp(object param)
{
//빈값이거나 타입값이 아니면 취소.
if (param == null) return;

var vrParam = (Telerik.Windows.Controls.RadListBox)param;

var vrCllct = vrParam.ItemsSource as Collection<DBSelections>;
var vrSlct = vrParam.SelectedItem;

if (vrSlct == null)
{
System.Windows.MessageBox.Show("항목을 선택하세요.");
return;
}

if (vrParam.SelectedIndex > 0)
{
int index = vrParam.SelectedIndex;
vrCllct.Remove(vrSlct);
vrCllct.Insert(index - 1, vrSlct);
vrParam.SelectedIndex = index - 1;
}

}

/// <summary>
/// 그룹들 아래로 내리기
/// </summary>
public void OnSltGrpItemDown(object param)
{
//빈값이거나 타입값이 아니면 취소.
if (param == null) return;

var vrParam = (Telerik.Windows.Controls.RadListBox)param;

var vrCllct = vrParam.ItemsSource as Collection<DBSelections>;
var vrSlct = vrParam.SelectedItem;

if (vrSlct == null)
{
System.Windows.MessageBox.Show("항목을 선택하세요.");
return;
}

if ((vrParam.SelectedIndex > -1) && (vrParam.SelectedIndex < vrParam.Items.Count - 1))     
{
int index = vrParam.SelectedIndex;
vrCllct.Remove(vrSlct);
vrCllct.Insert(index + 1, vrSlct);
vrParam.SelectedIndex = index + 1;
}

}

출처 : http://blog.naver.com/kyumi0705/20112791363

원본출처에 정리가 잘 되어있습니다.

======

GROUP BY 해서 기준으로 MAX,MIN으로 구한 키값을 서브쿼리로 하니 속도가 엄청 느려져. 아.. 이건아니다 싶어서,

조회를 해보니 나오네요~~

응용참조

SELECT
CSM.UnitNo
, CSM.StrYmd
, CASE
 WHEN ROW_NUMBER() OVER(PARTITION BY CSM.UnitNo ORDER BY CSM.UnitNo, CSM.StrYmd) = 1
 THEN 'Y' ELSE '' END AS ViewYN
, CSM.TermKey
FROM #T_PD AS CSM

한번더 감싸서 ViewYN = 'Y'인것만 가져오면 됨. 순간 bool이 속도가 빠르지 않을까하는 생각이 문득--

=======

★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)

결과

MAIN_CODE     GOODS_SEQ    OFFER_MASTER_SEQ     MODEL_CD    RANK

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

1                  1                 1                             AAA            1

1                  1                 2                             AAA            2

1                  2                 4                             BBB            3

1                  2                 5                             BBB            4

2                  1                 1                             AAA            1

2                  1                 4                             AAA            2

2                  2                 5                             BBB            3

2                  2                 6                             BBB            4

2                  3                 7                             CCC            5

2                  3                 9                             CCC            6

★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기.

TEAM_CD     SCORE     PALY_DATE

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

AAA            90          2010/08/01

AAA            50          2010/08/02

AAA            60          2010/08/03

AAA            50          2010/08/04

BBB            50          2010/08/01

BBB            90          2010/08/02

BBB            95          2010/08/03

BBB            100         2010/08/04

방법 1. RANK() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          6          2010/08/04

BBB            50          6          2010/08/01

방법 2. ROW_NUMBER() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          4          2010/08/02

AAA            60          5          2010/08/03

AAA            50          6          2010/08/02

AAA            50          7          2010/08/04

BBB            50          8          2010/08/01

방법 3. DENSE_RANK() OVER

결과

TEAM_CD     SCORE     RANK     PALY_DATE

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

BBB            100        1          2010/08/04

BBB            95          2          2010/08/03

AAA            90          3          2010/08/01

BBB            90          3          2010/08/02

AAA            60          4          2010/08/03

AAA            50          5          2010/08/02

AAA            50          5          2010/08/04

BBB            50          5          2010/08/01

★ 분석용 함수

RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용)

DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함)

ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공

CUME_DIST - 분산값

PERCENT_RANK - 백분율

NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시

FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환.

LAST_VALUE - 정렬된 값중에서 마지막 값을 반환.

★ OVER() 에 사용되는 OPTION

1. PARTITION BY
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.

링크드서버도 연결안되면,

간략히 ~~~..

SELECT *
 FROM OPENROWSET(
 'SQLNCLI',
 'Server=***;UID=**;PWD=***',
 'SELECT * FROM TABLE ')

SQL구문에서 여러줄결과값을 한줄로 보여주기

함수... 함수를 사용하면됨

그러나 함수사용 제약이 있을경우

ComCd ComNm                ComSubCd ComSubNm
----- -------------------- -------- --------------------

A001  모니터                  17       17인치모니터
A001  모니터                  19       19인치모니터
A001  모니터                  22       22인치모니터
A002  책상                   22       22형책상
A002  책상                   27       27형책상

항목을 구지 아래와 같이 보여야 되는 사항이라면

ComSubCd ComNm
-------- ----------------------------------------------------
17       17인치모니터
19       19인치모니터
22       모니터-22인치모니터; 책상-22형책상;
27       27형책상

(4개 행이 영향을 받음)

 FOR XML PATH('')) 사용하심~~.

--- 예제

DECLARE @D_Code VARCHAR(50)
SELECT @D_Code = '';

SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
AND ( ComCd LIKE @D_Code+'%' OR ComNm LIKE @D_Code+'%')
GROUP BY ComCd, ComNm,ComSubCd, ComSubNm
;

--유형서브코드
WITH #T_INSU2 AS
(
SELECT *
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
)
, #T_INCD AS
(
SELECT ComCd, ComNm
FROM #T_INSU2
GROUP BY ComCd, ComNm
)
, #T_SUBCD AS
(
SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM #T_INSU2
GROUP BY ComCd, ComNm, ComSubCd, ComSubNm
)
, #T_SUBDIST AS
(
SELECT ComSubCd, ComSubNm, COUNT(1) AS SubCnt
FROM #T_SUBCD
GROUP BY ComSubCd, ComSubNm
)
, #T_SUBCNT AS
(
SELECT ComSubCd, COUNT(1) AS SubCnt
FROM #T_SUBDIST
GROUP BY ComSubCd
)
SELECT
 SB.ComSubCd
 , CASE SB.SubCnt
 WHEN '0' THEN ''
 WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd ) 
 ELSE (
 SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
 END AS 'ComNm'
FROM #T_SUBCNT AS SB
WHERE ComSubCd LIKE @D_Code+'%'
OR CASE SB.SubCnt
 WHEN '0' THEN ''
 WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd ) 
 ELSE (
 SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
 END LIKE '%'+@D_Code+'%'
;

이전날자와 비교하여 날짜차이 찾기

빠진이빨찾기II
참조 : http://www.gooper.com/ss/index.php?mid=tnt_db&sort_index=regdate&order_type=desc&comment_srl=3990&document_srl=1764

. 자 ~~ 응용

WITH T_EPA AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY ErPatCalcYmd) AS RowNum
, ErPatCalcYmd
FROM MP_ErPatDailyCalc WITH(NOLOCK)
GROUP BY ErPatCalcYmd
)
, T_EPB AS
(
SELECT
A.RowNum
, A.ErPatCalcYmd
, B.ErPatCalcYmd AS BeErPatCalcYmd
, DATEDIFF(DD,CONVERT(DATETIME, B.ErPatCalcYmd), CONVERT(DATETIME, A.ErPatCalcYmd) ) AS DaysCout
FROM T_EPA AS A
LEFT OUTER JOIN T_EPA AS B
ON A.RowNum = B.RowNum+1
)
SELECT * FROM T_EPB
WHERE DaysCout > 1