[SQL]PIVOT使用，將公司階層轉向給 report 頁尾使用+UNPIVOT
select 'SIGSEQ',[050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200] from
(
SELECT
A.FLOWDPTY003
--,A.FLOWDPTY007
--,B.JOBTITLEID
,C.JOBTITLENM
FROM [dbo].[FLOWDPTY] AS A
left join [IFLOW2].[dbo].[VW_HPMUSER] AS B ON A.FLOWDPTY007 = B.USERID AND A.FLOWDPTY008 = B.COMPID
left join [IFLOW2].[dbo].[VW_HPMJOBTITLE] AS C ON B.JOBTITLEID = C.JOBTITLEID AND B.COMPID =C.COMPID
where A.FLOWDPTY001='IFFIB05F'
AND A.FLOWDPTY002 = @I_CHR_SHEETNO
AND A.FLOWDPTY003 BETWEEN '050' AND '200'
) as t
PIVOT(
Max(JOBTITLENM)
for [FLOWDPTY003] in ([050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200])
) as p;
轉向後
原始
(
SELECT
A.FLOWDPTY003
--,A.FLOWDPTY007
--,B.JOBTITLEID
,C.JOBTITLENM
FROM [dbo].[FLOWDPTY] AS A
left join [IFLOW2].[dbo].[VW_HPMUSER] AS B ON A.FLOWDPTY007 = B.USERID AND A.FLOWDPTY008 = B.COMPID
left join [IFLOW2].[dbo].[VW_HPMJOBTITLE] AS C ON B.JOBTITLEID = C.JOBTITLEID AND B.COMPID =C.COMPID
where A.FLOWDPTY001='IFFIB05F'
AND A.FLOWDPTY002 = @I_CHR_SHEETNO
AND A.FLOWDPTY003 BETWEEN '050' AND '200'
) as t
PIVOT(
Max(JOBTITLENM)
for [FLOWDPTY003] in ([050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200])
) as p;
轉向後
原始
UNPIVOT
SELECT * FROM (
SELECT
A.CCID
,'工作檔' AS VISION
,@SQL_COMPID AS COMPID
,A.DATAYEAR
,A.SEG1
,A.SEG2
,C.SEG2NM
,A.SEG3
,C.SEG3NM
,A.SEG4
,C.SEG4NM AS SEG4NM
,A.SEG5
,C.SEG5NM AS SEG5NM
,A.SEG6
,C.SEG6NM AS SEG6NM
, A.AMT_M1
, A.AMT_M2
, A.AMT_M3
, A.AMT_M4
, A.AMT_M5
, A.AMT_M6
, A.AMT_M7
, A.AMT_M8
, A.AMT_M9
, A.AMT_M10
, A.AMT_M11
, A.AMT_M12
FROM dbo.BJMF AS A
LEFT JOIN dbo.V_ALLACCID_FROM_GL AS C ON A.CCID = C.CCID
WHERE
A.DATAYEAR = @I_CHR_DATAYEAR
AND A.YEARTYPE = @I_CHR_YEARTYPE
and A.SEG1 = @SQL_COMPID
and A.SEG2 = CASE WHEN @I_CHR_SEG2 <> '' THEN @I_CHR_SEG2 ELSE A.SEG2 END
and A.SEG3 = CASE WHEN @I_CHR_SEG3 <> '' THEN @I_CHR_SEG3 ELSE A.SEG3 END
and A.SEG4 = CASE WHEN @I_CHR_SEG4 <> '' THEN @I_CHR_SEG4 ELSE A.SEG4 END
and A.SEG5 = CASE WHEN @I_CHR_SEG5 <> '' THEN @I_CHR_SEG5 ELSE A.SEG5 END
and A.SEG6 = CASE WHEN @I_CHR_SEG6 <> '' THEN @I_CHR_SEG6 ELSE A.SEG6 END
) AS P
UNPIVOT
(
MONTH_AMT FOR DATAMONTH IN (AMT_M1,AMT_M2,AMT_M3,AMT_M4,AMT_M5,AMT_M6,AMT_M7,AMT_M8,AMT_M9,AMT_M10,AMT_M11,AMT_M12)
) AS pv
[ASP.NET] Label & TextBox 字串轉換 => 符合列印
原因： Label才能符合列印換頁，自動換行等等
lbFILEEXP.Visible = true;
ftbFILEEXP.Visible = false;
lbFILEEXP.Text = ftbFILEEXP.Text.Replace("\r\n", "<br>"); //換行符號
lbFILEEXP.Text = lbFILEEXP.Text.Replace("\t", " ");
lbFILEEXP.Visible = true;
ftbFILEEXP.Visible = false;
lbFILEEXP.Text = ftbFILEEXP.Text.Replace("\r\n", "<br>"); //換行符號
lbFILEEXP.Text = lbFILEEXP.Text.Replace("\t", " ");
//tab表示4個空白，英文無法自動斷行
lbFILEEXP.Text = lbFILEEXP.Text.Replace(" ", " "); //1個空白，連續英文無法自動斷行
lbFILEEXP.Text = lbFILEEXP.Text.Replace(" ", " "); //1個空白，連續英文無法自動斷行
[ASP.NET]色碼表
色 碼 表
十六進位值排列
<font color="#RRGGBB">
| #FFFFFF | #DDDDDD | #AAAAAA | #888888 | #666666 | #444444 | #000000 |
| #FFB7DD | #FF88C2 | #FF44AA | #FF0088 | #C10066 | #A20055 | #8C0044 |
| #FFCCCC | #FF8888 | #FF3333 | #FF0000 | #CC0000 | #AA0000 | #880000 |
| #FFC8B4 | #FFA488 | #FF7744 | #FF5511 | #E63F00 | #C63300 | #A42D00 |
| #FFDDAA | #FFBB66 | #FFAA33 | #FF8800 | #EE7700 | #CC6600 | #BB5500 |
| #FFEE99 | #FFDD55 | #FFCC22 | #FFBB00 | #DDAA00 | #AA7700 | #886600 |
| #FFFFBB | #FFFF77 | #FFFF33 | #FFFF00 | #EEEE00 | #BBBB00 | #888800 |
| #EEFFBB | #DDFF77 | #CCFF33 | #BBFF00 | #99DD00 | #88AA00 | #668800 |
| #CCFF99 | #BBFF66 | #99FF33 | #77FF00 | #66DD00 | #55AA00 | #227700 |
| #99FF99 | #66FF66 | #33FF33 | #00FF00 | #00DD00 | #00AA00 | #008800 |
| #BBFFEE | #77FFCC | #33FFAA | #00FF99 | #00DD77 | #00AA55 | #008844 |
| #AAFFEE | #77FFEE | #33FFDD | #00FFCC | #00DDAA | #00AA88 | #008866 |
| #99FFFF | #66FFFF | #33FFFF | #00FFFF | #00DDDD | #00AAAA | #008888 |
| #CCEEFF | #77DDFF | #33CCFF | #00BBFF | #009FCC | #0088A8 | #007799 |
| #CCDDFF | #99BBFF | #5599FF | #0066FF | #0044BB | #003C9D | #003377 |
| #CCCCFF | #9999FF | #5555FF | #0000FF | #0000CC | #0000AA | #000088 |
| #CCBBFF | #9F88FF | #7744FF | #5500FF | #4400CC | #2200AA | #220088 |
| #D1BBFF | #B088FF | #9955FF | #7700FF | #5500DD | #4400B3 | #3A0088 |
| #E8CCFF | #D28EFF | #B94FFF | #9900FF | #7700BB | #66009D | #550088 |
| #F0BBFF | #E38EFF | #E93EFF | #CC00FF | #A500CC | #7A0099 | #660077 |
| #FFB3FF | #FF77FF | #FF3EFF | #FF00FF | #CC00CC | #990099 | #770077 |
以色系排列
紅
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| maroon | #800000 | |
| darkred | #8B0000 | |
| brown | #A52A2A | |
| firebrick | #B22222 | |
| crimson | #DC143C | |
| red | #FF0000 |
桃紅~紛紅
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| mediumvioletred | #C71585 | |
| palevioletred | #D87093 | |
| deeppink | #FF1493 | |
| fuchsia(magenta) | #FF00FF | |
| hotpink | #FF69B4 | |
| pink | #FFC0CB | |
| lightpink | #FFB6C1 | |
| mistyrose | #FFE4E1 | |
| lavenderblush | #FFF0F5 |
紫
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| indigo | #4B0082 | |
| purple | #800080 | |
| darkmagenta | #8B008B | |
| darkorchid | #9932CC | |
| blueviolet | #8A2BE2 | |
| darkviolet | #9400D3 | |
| slateblue | #6A5ACD | |
| mediumpurple | #9370DB | |
| mediumslateblue | #7B68EE | |
| mediumorchid | #BA55D3 | |
| violet | #EE82EE | |
| plum | #DDA0DD | |
| thistle | #D8BFD8 | |
| lavender | #E6E6FA |
褐~橘~米白
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| saddlebrown | #8B4513 | |
| sienna | #A0522D | |
| chocolate | #D2691E | |
| indianred | #CD5C5C | |
| rosybrown | #BC8F8F | |
| lightcorol | #F08080 | |
| salmon | #FA8072 | |
| lightsalmon | #FFA07A | |
| orangered | #FF4500 | |
| tomato | #FF6347 | |
| coral | #FF7F50 | |
| darkorange | #FF8C00 | |
| sandybrown | #F4A460 | |
| peru | #CD853F | |
| tan | #D2B48C | |
| burlywood | #DEB887 | |
| wheat | #F5DEB3 | |
| moccasin | #FFE4B5 | |
| navajowhite | #FFDEAD | |
| peachpuff | #FFDAB9 | |
| bisque | #FFE4C4 | |
| antuquewhite | #FAEBD7 | |
| papayawhip | #FFEFD5 | |
| cornsilk | #FFF8DC | |
| oldlace | #FDF5E6 | |
| linen | #FAF0E6 | |
| seashell | #FFF5EE | |
| snow | #FFFAFA | |
| floralwhite | #FFFAF0 | |
| ivory | #FFFFF0 | |
| mintcream | #F5FFFA |
金~黃
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| darkgoldenrod | #B8860B | |
| goldenrod | #DAA520 | |
| gold | #FFD700 | |
| yellow | #FFFF00 | |
| darkkhaki | #BDB76B | |
| khaki | #F0E68C | |
| palegoldenrod | #EEE8AA | |
| beige | #F5F5DC | |
| lemonchiffon | #FFFACD | |
| lightgoldenrodyellow | #FAFAD2 | |
| lightyellow | #FFFFE0 |
綠
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| darkslategray | #2F4F4F | |
| darkolivegreen | #556B2F | |
| olive | #808000 | |
| darkgreen | #006400 | |
| forestgreen | #228B22 | |
| seagreen | #2E8B57 | |
| green(teal) | #008080 | |
| lightseagreen | #20B2AA | |
| madiumaquamarine | #66CDAA | |
| mediumseagreen | #3CB371 | |
| darkseagreen | #8FBC8F | |
| yellowgreen | #9ACD32 | |
| limegreen | #32CD32 | |
| lime | #00FF00 | |
| chartreuse | #7FFF00 | |
| lawngreen | #7CFC00 | |
| greenyellow | #ADFF2F | |
| mediumspringgreen | #00FA9A | |
| springgreen | #00FF7F | |
| lightgreen | #90EE90 | |
| palegreen | #98F898 | |
| aquamarine | #7FFFD4 | |
| honeydew | #F0FFF0 |
藍
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| midnightblue | #191970 | |
| navy | #000080 | |
| darkblue | #00008B | |
| darkslateblue | #483D8B | |
| mediumblue | #0000CD | |
| royalblue | #4169E1 | |
| dodgerblue | #1E90FF | |
| cornflowerblue | #6495ED | |
| deepskyblue | #00BFFF | |
| lightskyblue | #87CEFA | |
| lightsteelblue | #B0C4DE | |
| lightblue | #ADD8E6 | |
| steelblue | #4682B4 | |
| darkcyan | #008B8B | |
| cadetblue | #5F9EA0 | |
| darkturquoise | #00CED1 | |
| mediumturquoise | #48D1CC | |
| turquoise | #40E0D0 | |
| skyblue | #87CECB | |
| powderblue | #B0E0E6 | |
| paleturquoise | #AFEEEE | |
| lightcyan | #E0FFFF | |
| azure | #F0FFFF | |
| aliceblue | #F0F8FF | |
| aqua(cyan) | #00FFFF |
黑~灰~白
| 顏色名稱 | 代碼 |
顏色
|
|---|---|---|
| black | #000000 | |
| dimgray | #696969 | |
| gray | #808080 | |
| slategray | #708090 | |
| lightslategray | #778899 | |
| darkgray | #A9A9A9 | |
| silver | #C0C0C0 | |
| lightgray | #D3D3D3 | |
| gainsboro | #DCDCDC | |
| whitesmoke | #F5F5F5 | |
| ghostwhite | #F8F8FF | |
| white | #FFFFFF |
以顏色名稱的字母順序排列
| aliceblue | antiquewhite | aqua | aquamarine |
| azure | beige | bisque | black |
| blanchedalmond | blue | blueviolet | brown |
| burlywood | cadetblue | chartreuse | chocolate |
| coral | cornflowerblue | cornsilk | crimson |
| cyan | darkblue | darkcyan | darkgoldenrod |
| darkgray | darkgreen | darkkhaki | darkmagenta |
| darkolivegreen | darkorange | darkorchid | darkred |
| darksalmon | darkseagreen | darkslateblue | darkslategray |
| darkturquoise | darkviolet | deeppink | deepskyblue |
| dimgray | dodgerblue | firebrick | floralwhite |
| forestgreen | fuchsia | gainsboro | ghostwhite |
| gold | goldnrod | gray | green |
| greenyellow | honeydew | hotpink | indianred |
| indigo | ivory | khaki | lavender |
| lavenderblush | lawngreen | lemonchiffon | lightblue |
| lightcoral | lightcyan | lightgoldenrodyellow | lightgreen |
| lightgrey | lightpink | lightsalmon | lightseagreen |
| lightskyblue | lightslategray | lightsteelblue | lightyellow |
| lime | limegreen | linen | magenta |
| maroon | mediumaquamarine | mediumblue | mediumorchid |
| mediumpurple | mediumseagreen | mediumslateblue | mediumspringgreen |
| mediumturquoise | mediumvioletred | midlightblue | mintcream |
| mistyrose | moccasin | navajowhite | navy |
| oldlace | olive | olivedrab | orange |
| orangered | orchid | palegoldenrod | palegreen |
| paleturquoise | palevioletred | papaywhip | peachpuff |
| peru | pink | plum | powderblue |
| purple | red | rosybrown | royalblue |
| saddlebrown | salmon | sandybrown | seagreen |
| seashell | sienna | silver | skyblue |
| slateblue | slategray | snow | springgreen |
| steelblue | tan | teal | thistle |
| tomato | turquoise | violet | wheat |
| white | whitesmoke | yellow | yellowgreen |
[C#]gridview控制項值pageload時重新傳給DB
//fgvIFGAA10FD1不在編輯區製作的資料要重新load
ftbMTDT.Text = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
ftbMTPGMID.Text = "IFGAA30F";
ftbMTUSERID.Text = this.Master.User.USERID;
for (int i = 0; i < fgvD80.Rows.Count; i++)
{
DataTable dt = new DataTable() ;
dt = fgvD80.TempTable;//這裡被唯讀要解決
foreach (DataColumn dc in dt.Columns)
{
dc.ReadOnly = false;
}
TextBox tbVENDORNUM2 = (TextBox)fgvD80.Rows[i].FindControl("tbVENDORNUM2");
TextBox tbVENDORPAYMODE =(TextBox)fgvD80.Rows[i].FindControl("tbVENDORPAYMODE");
TextBox tbVENDORPAYDATE = (TextBox)fgvD80.Rows[i].FindControl("tbVENDORPAYDATE");
dt.Rows[i]["VENDORNUM2"] = tbVENDORNUM2.Text.Trim();
dt.Rows[i]["VENDORAMTS"] = Convert.ToInt32(tbVENDORNUM2.Text.Trim()) * Convert.ToInt32(dt.Rows[i]["VENDORAMT"].ToString());
dt.Rows[i]["VENDORPAYMODE"] = tbVENDORPAYMODE.Text.Trim();
dt.Rows[i]["VENDORPAYDATE"] = tbVENDORPAYDATE.Text.Trim();
dt.Rows[i]["MTUSERID"] = this.Master.User.USERID;
dt.Rows[i]["MTPGMID"] = "IFGAA30F";
dt.Rows[i]["MTDT"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
//注意順序，先AcceptChanges再做DataBind
fgvD80.TempTable.AcceptChanges();
fgvD80.DataBind(fgvD80.TempTable);
ftbMTDT.Text = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
ftbMTPGMID.Text = "IFGAA30F";
ftbMTUSERID.Text = this.Master.User.USERID;
for (int i = 0; i < fgvD80.Rows.Count; i++)
{
DataTable dt = new DataTable() ;
dt = fgvD80.TempTable;//這裡被唯讀要解決
foreach (DataColumn dc in dt.Columns)
{
dc.ReadOnly = false;
}
TextBox tbVENDORNUM2 = (TextBox)fgvD80.Rows[i].FindControl("tbVENDORNUM2");
TextBox tbVENDORPAYMODE =(TextBox)fgvD80.Rows[i].FindControl("tbVENDORPAYMODE");
TextBox tbVENDORPAYDATE = (TextBox)fgvD80.Rows[i].FindControl("tbVENDORPAYDATE");
dt.Rows[i]["VENDORNUM2"] = tbVENDORNUM2.Text.Trim();
dt.Rows[i]["VENDORAMTS"] = Convert.ToInt32(tbVENDORNUM2.Text.Trim()) * Convert.ToInt32(dt.Rows[i]["VENDORAMT"].ToString());
dt.Rows[i]["VENDORPAYMODE"] = tbVENDORPAYMODE.Text.Trim();
dt.Rows[i]["VENDORPAYDATE"] = tbVENDORPAYDATE.Text.Trim();
dt.Rows[i]["MTUSERID"] = this.Master.User.USERID;
dt.Rows[i]["MTPGMID"] = "IFGAA30F";
dt.Rows[i]["MTDT"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
//注意順序，先AcceptChanges再做DataBind
fgvD80.TempTable.AcceptChanges();
fgvD80.DataBind(fgvD80.TempTable);
訂閱：
文章 (Atom)