目的: V60202D- 匯出 – 多檔案 - 選項
處理說明:1> // 選項- 匯出內容: 1-一般資料匯出/2-盤點資料匯出
var cmp_rd_XLSOUT = get_cmp_rdgrp1('匯出資料', 'sub_rd_XLSOUT', 100);
var cmp_rd_XLSOUT1 = get_cmp_rd0('一般資料匯出', 'sub_rd_XLSOUT', 250, '1',"0 5 0 5",250);
var cmp_rd_XLSOUT2 = get_cmp_rd0('盤點資料匯出', 'sub_rd_XLSOUT', 250, '2', "0 5 0 5", 250);
cmp_rd_XLSOUT.items.push(cmp_rd_XLSOUT1, cmp_rd_XLSOUT2,);
cmp_rd_XLSOUT1.checked = true;
var cmp_rd_XLSOUT = get_cmp_rdgrp1('匯出資料', 'sub_rd_XLSOUT', 100);
var cmp_rd_XLSOUT1 = get_cmp_rd0('一般資料匯出', 'sub_rd_XLSOUT', 250, '1',"0 5 0 5",250);
var cmp_rd_XLSOUT2 = get_cmp_rd0('盤點資料匯出', 'sub_rd_XLSOUT', 250, '2', "0 5 0 5", 250);
cmp_rd_XLSOUT.items.push(cmp_rd_XLSOUT1, cmp_rd_XLSOUT2,);
cmp_rd_XLSOUT1.checked = true;
2>radioGroup 設為 Disabled
//預設一般資料 --> 盤點選項 , 設為 Disabled
Ext.getCmp('sub_rd_CHK').setDisabled(true);
Ext.getCmp("sub_rd_XLSOUT").on('change', function (me, newValue, oldValue, eOpts) {
if (newValue == "1") { //一般資料 --> 不可點 [盤點]選項
Ext.getCmp('sub_rd_CHK').setDisabled(true);
}
else //盤點資料 --> 可點 [盤點]選項
Ext.getCmp('sub_rd_CHK').setDisabled(false);
}
); // end of cmp_sub_rd1.on("change"
3> 匯出 - 多檔案 選項
public void XLSOUT()
{
if (Tmp_rd_XLSOUT == "1")
XLSOUT_1(); //一般資料
else
XLSOUT_2(); //盤點資料
void XLSOUT_1() { };
void XLSOUT_2() { };
void XLSOUT_2() { };
}
1>*.js
//radiogroup1 -下載匯入格式 - 選項:
// 選項- 匯出內容: 1-一般資料匯出/2-盤點資料匯出
var cmp_rd_XLSOUT = get_cmp_rdgrp1('匯出資料', 'sub_rd_XLSOUT', 100);
var cmp_rd_XLSOUT1 = get_cmp_rd0('一般資料匯出', 'sub_rd_XLSOUT', 250, '1',"0 5 0 5",250);
var cmp_rd_XLSOUT2 = get_cmp_rd0('盤點資料匯出', 'sub_rd_XLSOUT', 250, '2', "0 5 0 5", 250);
cmp_rd_XLSOUT.items.push(cmp_rd_XLSOUT1, cmp_rd_XLSOUT2,);
cmp_rd_XLSOUT1.checked = true;
function get_cmp_rdgrp1(par_caption, par_id, par_l_width, par_layout, par_flex) {
if (checkisnull(par_flex)) {
par_flex = 10;
}
if (checkisnull(par_layout)) {
par_layout = 'vbox'; //預設直向 radio
}
var rtn_cmp =
{ //已完工, 未完工 --> 兩者不能同時勾選
xtype: 'radiogroup', fieldLabel: par_caption,
labelWidth: par_l_width,
layout: par_layout,
width: '100%',
id: par_id,
items: [],
style: { // radioGroup 加框線
border: '1px solid gray',
padding: '1px'
},
}
return rtn_cmp;
}
2>*.cs
//[匯出]鈕 - XLSOUT()
[HttpPost]
public void XLSOUT()
{
//取得參數值
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//選項-匯出資料 1/2: 一般資料匯出/盤點資料匯出
string Tmp_rd_XLSOUT = nvc["sub_rd_XLSOUT"];
//選項-過滤條件 1/2: 全部/依過濾條件
string Tmp_rd_COND = nvc["sub_rd_COND"];
//選項-盤點 1/2: 全部/未盤點/已盤點
string Tmp_rd_CHK = nvc["sub_rd_CHK"];
string Tmp_Sql = "";
string Tmp_Str = "";
string Tmp_Str1 = "";
int row = 0;
string Tmp_RtnMsg;
string Tmp_FName = "V60202_一般資料匯出格式.xlsx"; //套表的檔案名稱 ,
//documentPath = c:\\inetpub\wwwroot\TLS5\TLSWEB_AMM5\document\
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\"; // 取得實實的路徑
string Tmp_oFName = "V60202_一般資料匯出_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
string Tmp_poFName = documentPath + Tmp_oFName;
MemoryStream mstream = new MemoryStream();
HttpCookie MyCookie;
FileStream fs = null;
Workbook wk = null;
try
{
if (Tmp_rd_XLSOUT == "1")
XLSOUT_1(); //一般資料
else
XLSOUT_2(); //盤點資料
} //try
catch (Exception e)
{
//Console.WriteLine(e);
Tmp_Str = "錯誤:匯出檔案失敗,訊息如下:<br>"
+ e.Message;
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
}
finally
{
if (wk != null)
wk.Dispose();
fs.Close();
fs.Dispose();
}
//Tmp_Cnt = 6;
Tmp_Str = "已匯出完成!!(" + row.ToString() + "筆)<br>"
+ "檔案名稱(" + Tmp_oFName + ")";
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_oFName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(mstream.ToArray());
HttpContext.Current.Response.End();
void XLSOUT_1()
{
Tmp_FName = "V60202_一般資料匯出格式.xlsx"; //套表的檔案名稱 ,
Tmp_oFName = "V60202_一般資料匯出_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
Tmp_poFName = documentPath + Tmp_oFName;
// 取得 Template SS_FILES
fs = myfunc.get_SS_FILES(Tmp_FName);
wk = new Workbook();
wk.LoadFromStream(fs);
//2 > 主機.Local檔案 XLS 讀入 FStream
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
//取得匯出資料
DataTable Tmp_dt1 = new DataTable();
int Tmp_cnt1; //= dt1.Rows.Count;
//Panel11
string Tmp_MATNR = nvc["s_MATNR"];
string Tmp_chk_MATNR = nvc["s_chk_MATNR"];
string Tmp_WH = nvc["s_WH"];
string Tmp_SP = nvc["s_SP"];
string Tmp_CLASS = nvc["s_CLASS"];
//panel12
string Tmp_PROJ = nvc["s_PROJ"]; //WBS
string Tmp_CHKER = nvc["s_CHKER"];
string Tmp_CHKDT1 = "";
if (!myfunc.checkisnull(nvc["s_CHKDT1"]))
{
Tmp_CHKDT1 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_CHKDT1"]));
};
string Tmp_CHKDT2 = "";
if (!myfunc.checkisnull(nvc["s_CHKDT2"]))
{
Tmp_CHKDT2 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_CHKDT2"]));
};
//Panel13
string Tmp_MAKTX = nvc["s_MAKTX"]; //名稱
string Tmp_NAME = nvc["s_NAME"]; //俗稱
string Tmp_MODEL = nvc["s_MODEL"]; //規格
string Tmp_RMK = nvc["s_RMK"]; //備註
//panel14
string Tmp_chk_TERM = nvc["s_chk_TERM"]; //時限器材
string Tmp_chk_CLASS = nvc["s_chk_CLASS"]; //膠+漆
string Tmp_chk_IS_LIMWG = nvc["s_chk_IS_LIMWG"]; //需管制消耗量
string Tmp_chk_IS_LIMTM = nvc["s_chk_IS_LIMTM"]; //需管制曝露時數
string Tmp_chk_BELOW_WARM = nvc["s_chk_BELOW_WARM"]; //低於安全存量
//panel15
string Tmp_rd_VFDAT = nvc["s_rd_VFDAT"]; //屆期30天/屆期60天/屆期90天, 只能選一種
string Tmp_chk_SDS_EDT = nvc["s_chk_SDS_EDT"]; //SDS屆期30天內
string Tmp_SAPDB = nvc["s_SAPDB"]; //SAPDDB --> ERPI/SAP
OracleCommand cmd = new OracleCommand();
Tmp_Sql = " SELECT CPNO,MATNR ,WH ,SP , "
+ " MAKTX,NAME,QTY ,UNIT , "
+ " CLASS ,TERM ,NORM ,MODEL ,DEG , "
+ " LIFNR ,CHARG ,MFGBATCH ,BKTXT , "
+ " VFDAT ,PROJ ,NSN ,PN , "
+ " CAP ,COL ,CON ,TEMP ,CF , "
+ " AB ,UPJ ,UP ,STAT ,RMK "
+ " FROM AMM_COMP "
+ " WHERE 1=1 "
//+ " AND IS_VALID='Y' "
;
//若匯出條件: 依過濾條件
if (Tmp_rd_COND == "2")
{
//panel11
//物料編號
if (!myfunc.checkisnull(Tmp_MATNR))
{
if (Tmp_chk_MATNR == "true") //精準搜尋
Tmp_Sql = Tmp_Sql + " AND MATNR=" + myfunc.AA(Tmp_MATNR);
else
Tmp_Sql = Tmp_Sql + " AND MATNR LIKE " + myfunc.AA("%" + Tmp_MATNR + "%");
};
//庫房
if (!myfunc.checkisnull(Tmp_WH))
{
Tmp_Sql = Tmp_Sql + " AND WH=" + myfunc.AA(Tmp_WH);
};
//儲存位置
if (!myfunc.checkisnull(Tmp_SP))
{
Tmp_Sql = Tmp_Sql + " AND SP=" + myfunc.AA(Tmp_SP);
};
//類別
if (!myfunc.checkisnull(Tmp_CLASS))
{
Tmp_Sql = Tmp_Sql + " AND CLASS=" + myfunc.AA(Tmp_CLASS);
};
//panel12
//PROJ - WBS
if (!myfunc.checkisnull(Tmp_PROJ))
{
Tmp_Sql = Tmp_Sql + " AND PROJ=" + myfunc.AA(Tmp_PROJ);
};
//CHKER
if (!myfunc.checkisnull(Tmp_CHKER))
{
Tmp_Sql = Tmp_Sql + " AND CHKER=" + myfunc.AA(Tmp_CHKER);
};
//CHKDT1 ~ CHKDT2
//盤點日期
if (!(myfunc.checkisnull(Tmp_CHKDT1) || myfunc.checkisnull(Tmp_CHKDT2)))
{
if (!myfunc.checkisnull(Tmp_CHKDT1))
{
Tmp_Sql = Tmp_Sql + "AND (trunc(CHKDT)>= " + myfunc.AA(Tmp_CHKDT1) + ")";
};
if (!myfunc.checkisnull(Tmp_CHKDT2))
{
Tmp_Sql = Tmp_Sql + "AND (trunc(CHKDT)<= " + myfunc.AA(Tmp_CHKDT2) + ")";
};
}; // if (!(myfunc.checkisnull(Tmp_CHKDT1) || myfunc.checkisnull(Tmp_CHKDT2)))
//panel13
//名稱 s_MAKTX
if (!myfunc.checkisnull(Tmp_MAKTX))
{
Tmp_Sql = Tmp_Sql + " AND MAKTX LIKE " + myfunc.AA("%" + Tmp_MAKTX + "%");
};
//俗名 s_NAME
if (!myfunc.checkisnull(Tmp_NAME))
{
Tmp_Sql = Tmp_Sql + " AND NAME LIKE " + myfunc.AA("%" + Tmp_NAME + "%");
};
//規格 s_MODEL
if (!myfunc.checkisnull(Tmp_MODEL))
{
Tmp_Sql = Tmp_Sql + " AND MODEL LIKE " + myfunc.AA("%" + Tmp_MODEL + "%");
};
//備註 s_RMK
if (!myfunc.checkisnull(Tmp_RMK))
{
Tmp_Sql = Tmp_Sql + " AND RMK LIKE " + myfunc.AA("%" + Tmp_RMK + "%");
};
//panel14
if (Tmp_chk_TERM == "true") //時限器材
{
Tmp_Sql = Tmp_Sql + " AND TERM='Y' ";
}
if (Tmp_chk_CLASS == "true") //膠漆料
{
Tmp_Sql = Tmp_Sql + " AND ( (CLASS='B') OR (CLASS='G') ) ";
}
if (Tmp_chk_IS_LIMWG == "true") //需管制消耗量
{
Tmp_Sql = Tmp_Sql + " AND IS_LIMWG='Y' ";
}
if (Tmp_chk_IS_LIMTM == "true") //需管制曝露時數
{
Tmp_Sql = Tmp_Sql + " AND IS_LIMTM='Y' ";
}
if (Tmp_chk_BELOW_WARM == "true") //低於安全存量
{
Tmp_Sql = Tmp_Sql + " AND (MATNR, WH, SP) IN "
+ " ( SELECT MATNR, WH, SP "
+ " FROM "
+ " (SELECT MATNR, WH, SP, "
+ " SUM(QTY - ( SELECT NVL(SUM(QTY), 0) FROM AMM_COMPQTY WHERE EFF = 'Y' "
+ " AND CPNO = A.CPNO "
+ " AND MATNR = A.MATNR)) AS QTY1, "
+ " (SELECT NVL(QTY, 0) FROM AMM_WARM WHERE MATNR = A.MATNR AND WH = A.WH AND ROWNUM = 1) AS SAFEQTY "
+ " FROM AMM_COMP A "
+ " WHERE 1 = 1 "
+ " AND WH =" + myfunc.AA(Tmp_WH)
+ " AND SP<> 'FL' "
+ " AND QTY> 0 "
+ " GROUP BY MATNR,WH,SP ) "
+ " WHERE QTY1<SAFEQTY "
+ " ) ";
}
//panel15
//屆期30天/屆期60天/屆期90天/
if (!myfunc.checkisnull(Tmp_rd_VFDAT) && (Tmp_rd_VFDAT != "0"))
{
Tmp_Sql = Tmp_Sql + " AND VFDAT IS NOT NULL ";
switch (Tmp_rd_VFDAT)
{
case "1":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+30,'YYYYMMDD') ";
break;
case "2":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+60,'YYYYMMDD') ";
break;
case "3":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+90,'YYYYMMDD') ";
break;
}
}; //end of //屆期30天/屆期60天/屆期90天/
//SDS屆期30天內
if (Tmp_chk_SDS_EDT == "true")
{
Tmp_Sql = Tmp_Sql + " AND ( ( SDS_EDT <(sysdate+30) ) OR ( SDS_EDT2 < (sysdate+30) ) ) ";
}
} // end of rd_COND='2' 依過濾條件
Tmp_Sql = Tmp_Sql + " ORDER BY MATNR,WH,SP ";
Tmp_dt1 = myfunc.SqlOpen(Tmp_dt1, Tmp_Sql);
Tmp_cnt1 = Tmp_dt1.Rows.Count;
//AMM_COMP
//string Tmp_CPNO, Tmp_MATNR, Tmp_WH, Tmp_SP;
//string Tmp_MAKTX, Tmp_NAME, Tmp_QTY, Tmp_UNIT;
//string Tmp_CLASS, Tmp_TERM, Tmp_NORM, Tmp_MODEL, Tmp_DEG;
//string Tmp_VFDAT, Tmp_PROJ, Tmp_NSN, Tmp_PN;
//string Tmp_AB, Tmp_UPJ, Tmp_UP, Tmp_STAT,Tmp_RMK;
string Tmp_CPNO;
string Tmp_QTY, Tmp_UNIT;
string Tmp_TERM, Tmp_NORM, Tmp_DEG;
string Tmp_LIFNR, Tmp_CHARG, Tmp_MFGBATCH, Tmp_BKTXT;
string Tmp_VFDAT, Tmp_NSN, Tmp_PN;
string Tmp_CAP, Tmp_COL, Tmp_CON, Tmp_TEMP, Tmp_CF;
string Tmp_AB, Tmp_UPJ, Tmp_UP, Tmp_STAT;
for (int i = 0; i < Tmp_dt1.Rows.Count; i++)
{
//物料號碼 名稱 庫房 儲存位置 類別
Tmp_MATNR = Tmp_dt1.Rows[i]["MATNR"].ToString();
Tmp_MAKTX = Tmp_dt1.Rows[i]["MAKTX"].ToString();
Tmp_WH = Tmp_dt1.Rows[i]["WH"].ToString();
Tmp_SP = Tmp_dt1.Rows[i]["SP"].ToString();
Tmp_CLASS = Tmp_dt1.Rows[i]["CLASS"].ToString();
//時限器材 俗稱 規格 存量等級 數量 單位
Tmp_TERM = Tmp_dt1.Rows[i]["TERM"].ToString();
Tmp_NAME = Tmp_dt1.Rows[i]["NAME"].ToString();
Tmp_MODEL = Tmp_dt1.Rows[i]["MODEL"].ToString();
Tmp_DEG = Tmp_dt1.Rows[i]["DEG"].ToString();
Tmp_QTY = Tmp_dt1.Rows[i]["QTY"].ToString();
Tmp_UNIT = Tmp_dt1.Rows[i]["UNIT"].ToString();
//供應商 批次 廠商批號 接收批號 屆期日
Tmp_LIFNR = Tmp_dt1.Rows[i]["LIFNR"].ToString();
Tmp_CHARG = Tmp_dt1.Rows[i]["CHARG"].ToString();
Tmp_MFGBATCH = Tmp_dt1.Rows[i]["MFGBATCH"].ToString();
Tmp_BKTXT = Tmp_dt1.Rows[i]["BKTXT"].ToString();
Tmp_VFDAT = Tmp_dt1.Rows[i]["VFDAT"].ToString();
//代用料號 代用件號 容量 顏色 色號
Tmp_NSN = Tmp_dt1.Rows[i]["NSN"].ToString();
Tmp_PN = Tmp_dt1.Rows[i]["PN"].ToString();
Tmp_CAP = Tmp_dt1.Rows[i]["CAP"].ToString();
Tmp_COL = Tmp_dt1.Rows[i]["COL"].ToString();
Tmp_CON = Tmp_dt1.Rows[i]["CON"].ToString();
//待溫 成化 AB二劑包裝 使用專案 使用位置 備註
Tmp_TEMP = Tmp_dt1.Rows[i]["TEMP"].ToString();
Tmp_CF = Tmp_dt1.Rows[i]["CF"].ToString();
Tmp_AB = Tmp_dt1.Rows[i]["AB"].ToString();
Tmp_UPJ = Tmp_dt1.Rows[i]["UPJ"].ToString();
Tmp_UP = Tmp_dt1.Rows[i]["UP"].ToString();
Tmp_RMK = Tmp_dt1.Rows[i]["RMK"].ToString();
//寫入 EXCEL檔案
row = row + 1;
//物料號碼 名稱 庫房 儲存位置 類別
ws.Range[myfunc.GetExcelPos(0, row)].Text = Tmp_MATNR;
ws.Range[myfunc.GetExcelPos(1, row)].Text = Tmp_MAKTX;
ws.Range[myfunc.GetExcelPos(2, row)].Text = Tmp_WH;
ws.Range[myfunc.GetExcelPos(3, row)].Text = Tmp_SP;
ws.Range[myfunc.GetExcelPos(4, row)].Text = Tmp_CLASS;
//時限器材 俗稱 規格 存量等級 數量 單位
ws.Range[myfunc.GetExcelPos(5, row)].Text = Tmp_TERM;
ws.Range[myfunc.GetExcelPos(6, row)].Text = Tmp_NAME;
ws.Range[myfunc.GetExcelPos(7, row)].Text = Tmp_MODEL;
ws.Range[myfunc.GetExcelPos(8, row)].Text = Tmp_DEG;
ws.Range[myfunc.GetExcelPos(9, row)].Text = Tmp_QTY;
ws.Range[myfunc.GetExcelPos(10, row)].Text = Tmp_UNIT;
//供應商 批次 廠商批號 接收批號 屆期日
ws.Range[myfunc.GetExcelPos(11, row)].Text = Tmp_LIFNR;
ws.Range[myfunc.GetExcelPos(12, row)].Text = Tmp_CHARG;
ws.Range[myfunc.GetExcelPos(13, row)].Text = Tmp_MFGBATCH;
ws.Range[myfunc.GetExcelPos(14, row)].Text = Tmp_BKTXT;
ws.Range[myfunc.GetExcelPos(15, row)].Text = Tmp_VFDAT;
//代用料號 代用件號 容量 顏色 色號
ws.Range[myfunc.GetExcelPos(16, row)].Text = Tmp_NSN;
ws.Range[myfunc.GetExcelPos(17, row)].Text = Tmp_PN;
ws.Range[myfunc.GetExcelPos(18, row)].Text = Tmp_CAP;
ws.Range[myfunc.GetExcelPos(19, row)].Text = Tmp_COL;
ws.Range[myfunc.GetExcelPos(20, row)].Text = Tmp_CON;
//待溫 成化 AB二劑包裝 使用專案 使用位置 備註
ws.Range[myfunc.GetExcelPos(21, row)].Text = Tmp_TEMP;
ws.Range[myfunc.GetExcelPos(22, row)].Text = Tmp_CF;
ws.Range[myfunc.GetExcelPos(23, row)].Text = Tmp_AB;
ws.Range[myfunc.GetExcelPos(24, row)].Text = Tmp_UPJ;
ws.Range[myfunc.GetExcelPos(25, row)].Text = Tmp_UP;
ws.Range[myfunc.GetExcelPos(26, row)].Text = Tmp_RMK;
} // for i=
wk.SaveToStream(mstream, FileFormat.Version2007);
wk.SaveToFile(Tmp_poFName, FileFormat.Version2007);
} // end of XLSOUT_1
//盤點資料匯出
void XLSOUT_2()
{
Tmp_FName = "V60202_盤點資料匯出格式.xlsx"; //套表的檔案名稱 ,
Tmp_oFName = "V60202_盤點資料匯出_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
Tmp_poFName = documentPath + Tmp_oFName;
// 取得 Template SS_FILES
fs = myfunc.get_SS_FILES(Tmp_FName);
wk = new Workbook();
wk.LoadFromStream(fs);
//2 > 主機.Local檔案 XLS 讀入 FStream
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
//取得匯出資料
DataTable Tmp_dt1 = new DataTable();
int Tmp_cnt1; //= dt1.Rows.Count;
//Panel11
string Tmp_MATNR = nvc["s_MATNR"];
string Tmp_chk_MATNR = nvc["s_chk_MATNR"];
string Tmp_WH = nvc["s_WH"];
string Tmp_SP = nvc["s_SP"];
string Tmp_CLASS = nvc["s_CLASS"];
//panel12
string Tmp_PROJ = nvc["s_PROJ"]; //WBS
string Tmp_CHKER = nvc["s_CHKER"];
string Tmp_CHKDT1 = "";
if (!myfunc.checkisnull(nvc["s_CHKDT1"]))
{
Tmp_CHKDT1 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_CHKDT1"]));
};
string Tmp_CHKDT2 = "";
if (!myfunc.checkisnull(nvc["s_CHKDT2"]))
{
Tmp_CHKDT2 = string.Format("{0:yyyy/MM/dd}", DateTime.Parse(nvc["s_CHKDT2"]));
};
//Panel13
string Tmp_MAKTX = nvc["s_MAKTX"]; //名稱
string Tmp_NAME = nvc["s_NAME"]; //俗稱
string Tmp_MODEL = nvc["s_MODEL"]; //規格
string Tmp_RMK = nvc["s_RMK"]; //備註
//panel14
string Tmp_chk_TERM = nvc["s_chk_TERM"]; //時限器材
string Tmp_chk_CLASS = nvc["s_chk_CLASS"]; //膠+漆
string Tmp_chk_IS_LIMWG = nvc["s_chk_IS_LIMWG"]; //需管制消耗量
string Tmp_chk_IS_LIMTM = nvc["s_chk_IS_LIMTM"]; //需管制曝露時數
string Tmp_chk_BELOW_WARM = nvc["s_chk_BELOW_WARM"]; //低於安全存量
//panel15
string Tmp_rd_VFDAT = nvc["s_rd_VFDAT"]; //屆期30天/屆期60天/屆期90天, 只能選一種
string Tmp_chk_SDS_EDT = nvc["s_chk_SDS_EDT"]; //SDS屆期30天內
string Tmp_SAPDB = nvc["s_SAPDB"]; //SAPDDB --> ERPI/SAP
OracleCommand cmd = new OracleCommand();
Tmp_Sql = " SELECT DISTINCT MATNR ,WH ,SP "
+ " FROM AMM_COMP "
+ " WHERE 1=1 "
// + " AND IS_VALID='Y' "
;
////選項-盤點 1/2/3: 全部/未盤點/已盤點
string Tmp_rd_CHK = nvc["sub_rd_CHK"];
switch (Tmp_rd_CHK)
{
case "2":
{
Tmp_Sql = Tmp_Sql + " AND CHK='N' ";
break;
}
case "3":
{
Tmp_Sql = Tmp_Sql + " AND CHK='Y' ";
break;
}
}
//若匯出條件: 依過濾條件
if (Tmp_rd_COND == "2")
{
//panel11
//物料編號
if (!myfunc.checkisnull(Tmp_MATNR))
{
if (Tmp_chk_MATNR == "true") //精準搜尋
Tmp_Sql = Tmp_Sql + " AND MATNR=" + myfunc.AA(Tmp_MATNR);
else
Tmp_Sql = Tmp_Sql + " AND MATNR LIKE " + myfunc.AA("%" + Tmp_MATNR + "%");
};
//庫房
if (!myfunc.checkisnull(Tmp_WH))
{
Tmp_Sql = Tmp_Sql + " AND WH=" + myfunc.AA(Tmp_WH);
};
//儲存位置
if (!myfunc.checkisnull(Tmp_SP))
{
Tmp_Sql = Tmp_Sql + " AND SP=" + myfunc.AA(Tmp_SP);
};
//類別
if (!myfunc.checkisnull(Tmp_CLASS))
{
Tmp_Sql = Tmp_Sql + " AND CLASS=" + myfunc.AA(Tmp_CLASS);
};
//panel12
//PROJ - WBS
if (!myfunc.checkisnull(Tmp_PROJ))
{
Tmp_Sql = Tmp_Sql + " AND PROJ=" + myfunc.AA(Tmp_PROJ);
};
//CHKER
if (!myfunc.checkisnull(Tmp_CHKER))
{
Tmp_Sql = Tmp_Sql + " AND CHKER=" + myfunc.AA(Tmp_CHKER);
};
//CHKDT1 ~ CHKDT2
//盤點日期
if (!(myfunc.checkisnull(Tmp_CHKDT1) || myfunc.checkisnull(Tmp_CHKDT2)))
{
if (!myfunc.checkisnull(Tmp_CHKDT1))
{
Tmp_Sql = Tmp_Sql + "AND (trunc(CHKDT)>= " + myfunc.AA(Tmp_CHKDT1) + ")";
};
if (!myfunc.checkisnull(Tmp_CHKDT2))
{
Tmp_Sql = Tmp_Sql + "AND (trunc(CHKDT)<= " + myfunc.AA(Tmp_CHKDT2) + ")";
};
}; // if (!(myfunc.checkisnull(Tmp_CHKDT1) || myfunc.checkisnull(Tmp_CHKDT2)))
//panel13
//名稱 s_MAKTX
if (!myfunc.checkisnull(Tmp_MAKTX))
{
Tmp_Sql = Tmp_Sql + " AND MAKTX LIKE " + myfunc.AA("%" + Tmp_MAKTX + "%");
};
//俗名 s_NAME
if (!myfunc.checkisnull(Tmp_NAME))
{
Tmp_Sql = Tmp_Sql + " AND NAME LIKE " + myfunc.AA("%" + Tmp_NAME + "%");
};
//規格 s_MODEL
if (!myfunc.checkisnull(Tmp_MODEL))
{
Tmp_Sql = Tmp_Sql + " AND MODEL LIKE " + myfunc.AA("%" + Tmp_MODEL + "%");
};
//備註 s_RMK
if (!myfunc.checkisnull(Tmp_RMK))
{
Tmp_Sql = Tmp_Sql + " AND RMK LIKE " + myfunc.AA("%" + Tmp_RMK + "%");
};
//panel14
if (Tmp_chk_TERM == "true") //時限器材
{
Tmp_Sql = Tmp_Sql + " AND TERM='Y' ";
}
if (Tmp_chk_CLASS == "true") //膠漆料
{
Tmp_Sql = Tmp_Sql + " AND ( (CLASS='B') OR (CLASS='G') ) ";
}
if (Tmp_chk_IS_LIMWG == "true") //需管制消耗量
{
Tmp_Sql = Tmp_Sql + " AND IS_LIMWG='Y' ";
}
if (Tmp_chk_IS_LIMTM == "true") //需管制曝露時數
{
Tmp_Sql = Tmp_Sql + " AND IS_LIMTM='Y' ";
}
if (Tmp_chk_BELOW_WARM == "true") //低於安全存量
{
Tmp_Sql = Tmp_Sql + " AND (MATNR, WH, SP) IN "
+ " ( SELECT MATNR, WH, SP "
+ " FROM "
+ " (SELECT MATNR, WH, SP, "
+ " SUM(QTY - ( SELECT NVL(SUM(QTY), 0) FROM AMM_COMPQTY WHERE EFF = 'Y' "
+ " AND CPNO = A.CPNO "
+ " AND MATNR = A.MATNR)) AS QTY1, "
+ " (SELECT NVL(QTY, 0) FROM AMM_WARM WHERE MATNR = A.MATNR AND WH = A.WH AND ROWNUM = 1) AS SAFEQTY "
+ " FROM AMM_COMP A "
+ " WHERE 1 = 1 "
+ " AND WH =" + myfunc.AA(Tmp_WH)
+ " AND SP<> 'FL' "
+ " AND QTY> 0 "
+ " GROUP BY MATNR,WH,SP ) "
+ " WHERE QTY1<SAFEQTY "
+ " ) ";
}
//panel15
//屆期30天/屆期60天/屆期90天/
if (!myfunc.checkisnull(Tmp_rd_VFDAT) && (Tmp_rd_VFDAT != "0"))
{
Tmp_Sql = Tmp_Sql + " AND VFDAT IS NOT NULL ";
switch (Tmp_rd_VFDAT)
{
case "1":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+30,'YYYYMMDD') ";
break;
case "2":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+60,'YYYYMMDD') ";
break;
case "3":
Tmp_Sql = Tmp_Sql + " AND VFDAT <= TO_CHAR(sysdate+90,'YYYYMMDD') ";
break;
}
}; //end of //屆期30天/屆期60天/屆期90天/
//SDS屆期30天內
if (Tmp_chk_SDS_EDT == "true")
{
Tmp_Sql = Tmp_Sql + " AND ( ( SDS_EDT <(sysdate+30) ) OR ( SDS_EDT2 < (sysdate+30) ) ) ";
}
} // end of rd_COND='2' 依過濾條件
Tmp_Sql = Tmp_Sql + " ORDER BY MATNR,WH,SP ";
Tmp_dt1 = myfunc.SqlOpen(Tmp_dt1, Tmp_Sql);
Tmp_cnt1 = Tmp_dt1.Rows.Count;
//系統庫存數量
string Tmp_SUM_QTY;
for (int i = 0; i < Tmp_dt1.Rows.Count; i++)
{
//物料號碼 名稱 庫房 儲存位置 類別
Tmp_MATNR = Tmp_dt1.Rows[i]["MATNR"].ToString();
Tmp_WH = Tmp_dt1.Rows[i]["WH"].ToString();
Tmp_SP = Tmp_dt1.Rows[i]["SP"].ToString();
//計算[系統庫存數量]
Tmp_Sql = " SELECT nvl(SUM(SQTY),0) "
+ " FROM ("
+ " SELECT MATNR,WH,SP,"
+ " (A.QTY - (SELECT NVL(SUM(QTY), 0) FROM AMM_COMPQTY WHERE EFF = 'Y' AND CPNO = A.CPNO AND MATNR = A.MATNR)) as SQTY "
+ " FROM AMM_COMP A "
+ " WHERE 1=1 "
//+ " AND IS_VALID='Y' "
+ " AND MATNR=" + myfunc.AA(Tmp_MATNR)
+ " AND WH=" + myfunc.AA(Tmp_WH);
if (!myfunc.checkisnull(Tmp_SP))
Tmp_Sql = Tmp_Sql + " AND SP=" + myfunc.AA(Tmp_SP);
//+ " AND (A.QTY - (SELECT NVL(SUM(QTY), 0) FROM AMM_COMPQTY WHERE EFF = 'Y' AND CPNO = A.CPNO AND MATNR = A.MATNR))>0 "
Tmp_Sql = Tmp_Sql+ " ) ";
Tmp_SUM_QTY = myfunc.SqlValue(Tmp_Sql);
//寫入 EXCEL檔案
row = row + 1;
//物料號碼 名稱 庫房 儲存位置 類別
ws.Range[myfunc.GetExcelPos(0, row)].Text = Tmp_MATNR;
ws.Range[myfunc.GetExcelPos(1, row)].Text = Tmp_WH;
ws.Range[myfunc.GetExcelPos(2, row)].Text = Tmp_SP;
ws.Range[myfunc.GetExcelPos(3, row)].Text = Tmp_SUM_QTY;
} // for i=
wk.SaveToStream(mstream, FileFormat.Version2007);
wk.SaveToFile(Tmp_poFName, FileFormat.Version2007);
} // end of void XLSOUT2()
} // public void XLSOUT()
沒有留言:
張貼留言