private void Export()
{
string path = "d";
//string path = GetSavePath();
if (path != "")
{
if (chkAllRecords.Checked)
{
LoadReport(ConvertToExcel.GetReportData(Uti.GetAllStudents(), path));
}
else
{
LoadReport(ConvertToExcel.GetReportData(Uti.GetIndividualStudent(PassData.studentvalue), path));
}
}
}
protected void DbInit(string StrSearchType)
{
string Str成衣庫存sql = @"
select C.cus_item_no 款號
,A.item_no 料號
,A.stockroom 儲位
,B.color_ename 顏色英
,substring(A.lot_nbr,0,CHARINDEX('/',A.lot_nbr)) as 顏色代號 ,substring(A.lot_nbr,CHARINDEX('/',A.lot_nbr)+1,len(A.lot_nbr)) as 尺寸
,A.oh_qty as 成品庫存量
,A.lot_nbr 顏色尺寸
from inv_ord_balance as A left join bas_color as B on
substring(A.lot_nbr,1,CHARINDEX('/',A.lot_nbr)-1) = B.color_no and A.site=B.site and B.cus_id='AMZ'
left join ordc_bah1 as C on A.site=C.site and A.ord_nbr =C.ord_nbr
where
A.item_no like 'AMZ%' and
A.oh_qty>0
";
string Str原料庫存sql = @"
select
C.cus_item_no 款號
,C.ord_nbr 訂單號碼
,A.item_no 料號
,B.item_spk 料號規格
,F.color_ename
--,A.stockroom 儲位
--,E.stockroom_name 儲位名稱
,A.oh_qty as 在手量碼
,D.cloth_y_weight as 碼重
,cast(round((sum(A.oh_qty)*D.cloth_y_weight/1000*2.2046),2) as decimal(9,2)) as 在手量磅
,cast(round((sum(A.oh_qty)*D.cloth_y_weight/1000),2) as decimal(9,2)) as 在手量公斤
from inv_ord_balance as A
left join bas_item_master as B on A.site=B.site and A.item_no=B.item_no
left join ordc_bah1 as C on A.site=C.site and A.ord_nbr=C.ord_nbr
--left join bas_stockroom as E on A.site=E.site and A.stockroom=E.stockroom
left join purc_purchase_detail as D on A.ord_nbr = D.ord_nbr and A.item_no = D.item_no
left join bas_color as F on right(A.item_no,CHARINDEX('-', REVERSE(A.item_no))-1) =F.color_no and F.cus_id='AMZ'
where A.ord_nbr = C.ord_nbr and
--A.stockroom = E.stockroom and
A.item_no = B.item_no and
C.agents ='AMZ' and
A.item_no like 'F%' and
A.oh_qty >0
group by
C.cus_item_no
,C.ord_nbr
,A.item_no
,B.item_spk
--,A.stockroom
--,E.stockroom_name
,A.oh_qty
,D.cloth_y_weight
,F.color_ename
";
string StrVNN原料庫存sql = @"
select
C.cus_item_no 款號
,C.ord_nbr 訂單號碼
,A.item_no 料號
,B.item_spk 料號規格
,F.color_ename
,A.stockroom 儲位
,E.stockroom_name 儲位名稱
,A.oh_qty as 在手量碼
,D.cloth_y_weight as 碼重
,cast(round((sum(A.oh_qty)*D.cloth_y_weight/1000*2.2046),2) as decimal(9,2)) as 在手量磅
,cast(round((sum(A.oh_qty)*D.cloth_y_weight/1000),2) as decimal(9,2)) as 在手量公斤
from inv_ord_balance as A
left join bas_item_master as B on A.site=B.site and A.item_no=B.item_no
left join ordc_bah1 as C on A.site=C.site and A.ord_nbr=C.ord_nbr
left join bas_stockroom as E on A.site=E.site and A.stockroom=E.stockroom
left join purc_purchase_detail as D on A.ord_nbr = D.ord_nbr and A.item_no = D.item_no
left join bas_color as F on right(A.item_no,CHARINDEX('-', REVERSE(A.item_no))-1) =F.color_no and F.cus_id='AMZ'
where A.ord_nbr = C.ord_nbr and
A.stockroom = E.stockroom and
A.item_no = B.item_no and
C.agents ='AMZ' and
A.item_no like 'F%' and
A.oh_qty >0
group by
C.cus_item_no
,C.ord_nbr
,A.item_no
,B.item_spk
,A.stockroom
,E.stockroom_name
,A.oh_qty
,D.cloth_y_weight
,F.color_ename
";
//下載成衣庫存
if (StrSearchType == "越南主料庫存")
{
DataTable dtVNN = new DataTable(), dtTW = new DataTable(), dtCompare = new DataTable();
//dtVNN = F_庫存資料(越南成衣庫存, Str成衣庫存sql + " and A.stockroom = 'A031008' ", strVNNConnectString);
//dtTW = F_庫存資料(台灣成衣庫存, Str成衣庫存sql + " and A.site='GGF' ", strConnectString);
//dtCompare = dtVNN.Clone();
//DataColumn dataColumn不同數量 = new DataColumn();
//dataColumn不同數量.ColumnName = "台灣數量";
//dataColumn不同數量.DataType = System.Type.GetType("System.Decimal");
//dtCompare.Columns.Add(dataColumn不同數量);
//foreach (DataRow item in dtVNN.Rows)
//{
// DataRow dr = dtCompare.NewRow();
// for (int i = 0; i < item.ItemArray.Length-1; i++)
// {
// dr[i] = item[i];
// }
// DataView dataView = new DataView(dtTW);
// dataView.RowFilter = " 料號 = '" + item["料號"] + "' and 顏色尺寸 ='" + item["顏色尺寸"] + "'";
// dr["台灣數量"] = dataView.Count > 0 ? dataView[0][6].ToString() : "0";
// dtCompare.Rows.Add(dr);
//}
dtVNN = F_庫存資料(Str原料庫存sql, strVNNConnectString);
dtTW = F_庫存資料(Str原料庫存sql, strConnectString);
dtCompare = dtVNN.Clone();
DataColumn dataColumn台灣在手碼 = new DataColumn();
dataColumn台灣在手碼.ColumnName = "台灣在手碼";
dataColumn台灣在手碼.DataType = System.Type.GetType("System.Decimal");
dtCompare.Columns.Add(dataColumn台灣在手碼);
DataColumn dataColumn台灣碼 = new DataColumn();
dataColumn台灣碼.ColumnName = "台灣碼";
dataColumn台灣碼.DataType = System.Type.GetType("System.Decimal");
dtCompare.Columns.Add(dataColumn台灣碼);
DataColumn dataColumn台灣在手磅 = new DataColumn();
dataColumn台灣在手磅.ColumnName = "台灣在手磅";
dataColumn台灣在手磅.DataType = System.Type.GetType("System.Decimal");
dtCompare.Columns.Add(dataColumn台灣在手磅);
DataColumn dataColumn台灣在手公斤 = new DataColumn();
dataColumn台灣在手公斤.ColumnName = "台灣在手公斤";
dataColumn台灣在手公斤.DataType = System.Type.GetType("System.Decimal");
dtCompare.Columns.Add(dataColumn台灣在手公斤);
foreach (DataRow item in dtVNN.Rows)
{
DataRow dr = dtCompare.NewRow();
for (int i = 0; i < item.ItemArray.Length; i++)
{
dr[i] = item[i];
}
DataView dataView = new DataView(dtTW);
dataView.RowFilter = " 料號 = '" + item["料號"] + "' and 款號 ='" + item["款號"] + "'";
if (dataView.Count > 0)
{
try
{
//dr["台灣儲位"] = !string.IsNullOrEmpty(dataView[0][5].ToString()) ? dataView[0][5].ToString() : "";
dr["台灣在手碼"] = !string.IsNullOrEmpty(dataView[0][5].ToString()) ? dataView[0][5].ToString() : "0";
dr["台灣碼"] = !string.IsNullOrEmpty(dataView[0][6].ToString()) ? dataView[0][6].ToString() : "0";
dr["台灣在手磅"] = !string.IsNullOrEmpty(dataView[0][7].ToString()) ? dataView[0][7].ToString() : "0";
dr["台灣在手公斤"] = !string.IsNullOrEmpty(dataView[0][8].ToString()) ? dataView[0][8].ToString() : "0";
}
catch (Exception)
{
F_ErrorShow($"台灣資料轉換異常，請確認台灣資料，料號：{item["料號"]}，款號：{item["款號"]}");
}
}
else
{
//dr["台灣儲位"] = "";
dr["台灣在手碼"] = "0";
dr["台灣碼"] = "0";
dr["台灣在手磅"] = "0";
dr["台灣在手公斤"] = "0";
}
dtCompare.Rows.Add(dr);
}
if (dtCompare.Rows.Count > 0)
{
ConvertToExcel convertToExcel = new ConvertToExcel();
convertToExcel.ExportExcelByCloseExcel(dtCompare, "越南主料庫存");
}
else
{
F_ErrorShow("越南連線異常，請稍後在試");
}
}
//越南主料庫存
else if (StrSearchType == "越南原料庫存")
{
DataTable dt = new DataTable();
dt = F_庫存資料(StrVNN原料庫存sql, strVNNConnectString);
if (dt.Rows.Count > 0)
{
ConvertToExcel convertToExcel = new ConvertToExcel();
convertToExcel.ExportExcelByCloseExcel(dt, "越南原料庫存");
}
else
{
F_ErrorShow("越南連線異常，請稍後在試");
}
}
else
{
DataTable dt = new DataTable();
dt = F_庫存資料(Str成衣庫存sql, strVNNConnectString);
if (dt.Rows.Count > 0)
{
ConvertToExcel convertToExcel = new ConvertToExcel();
convertToExcel.ExportExcelByCloseExcel(dt, "AMZ成衣庫存比較");
}
else
{
F_ErrorShow("越南連線異常，請稍後在試");
}
}
}
