/// <summary>
/// booking import primary Key
/// </summary>
/// <returns>取得新增的 primary Key</returns>
private int GetExcelIdex(string StrHeadTableName)
{
Int32 HeadID = 0;
string sql =
string.Format(@"INSERT INTO {0}
(建立日期)
VALUES(@建立日期);
SELECT CAST(scope_identity() AS int)", StrHeadTableName);
using (SqlConnection conn = new SqlConnection(strConnectString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@建立日期", SqlDbType.NVarChar);
cmd.Parameters["@建立日期"].Value = DateTime.Now.ToString("yyyy/MM/dd");
try
{
conn.Open();
HeadID = (Int32)cmd.ExecuteScalar();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "GetHead id Error" + Str匯入定義Table + ":" + Session["FileName"].ToString() + ":", StrProgram);
}
}
return((int)HeadID);
}
public DataTable DbInit(string StrType)
{
DataTable Dt = new DataTable();
using (SqlConnection Conn = new SqlConnection(strConnectString))
{
//SqlCommand command1 = conn1.CreateCommand();
//SqlTransaction transaction1;
//conn1.Open();
//transaction1 = conn1.BeginTransaction("createSelect"+ StrType);
//try
//{
// command1.Connection = conn1;
// command1.Transaction = transaction1;
// #region 查詢
// string Str搜尋參數 = "";
// string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
// string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
// command1.CommandText = string.Format(@"SELECT from ( select {2} ) a left join View版套借出表 b on a.sam_nbr = b.sam_nbr
// where {1} in ( {0} ) and a.site='GGF'
// ", string.Join(",", parameters), Str搜尋參數, 字串處理.轉換虛擬Select(SearchBT.Text, "sam_nbr"));
// //command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
// for (int i = 0; i < StrArrary.Length; i++)
// command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
// command1.ExecuteNonQuery();
// SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
// Dt.Load(dr);
// #endregion
// //transaction1.Commit();
//}
//catch (Exception ex)
//{
// Log.ErrorLog(ex, "Error", StrProgram);
// transaction1.Rollback();
// throw;
//}
//finally
//{
// conn1.Close();
// transaction1.Dispose();
//}
try
{
SqlDataAdapter myAdapter = new SqlDataAdapter(string.Format(@"SELECT * from ( {0} ) a left join View版套借出 b on a.打樣單號 = b.sam_nbr
", 字串處理.轉換虛擬Select(MutiTB.Text, "打樣單號")), Conn);
myAdapter.Fill(Dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
}
}
return(Dt);
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = new SqlCommand();
conn1.Open();
try
{
command1.Connection = conn1;
#region 查詢
string Str搜尋參數 = "款號";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format($@"SELECT [id], [DataModifyDate], [款號], [料號], [Qty], [file_name], [Reason], [ReasonCode] FROM [GGF收料報告]
where DataModifyDate > getdate()-9 {(!string.IsNullOrEmpty(MutiTB.Text)?" and "+Str搜尋參數 +string.Format( " in ({0}) ", string.Join(",", parameters)):"")}");
if (!string.IsNullOrEmpty(MutiTB.Text))
{
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
}
}
#endregion
if (dt.Rows.Count > 0)
{
GV.DataSource = dt;
GV.DataBind();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"SELECT d* from
where {1} in ( {0} ) and a.site='GGF'
", string.Join(",", parameters), Str搜尋參數);
command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
//ReportViewer1.Visible = true;
//ReportViewer1.ProcessingMode = ProcessingMode.Local;
//ReportDataSource source = new ReportDataSource("採購單料號訂單資料", dt);
//ReportViewer1.LocalReport.DataSources.Clear();
//ReportViewer1.LocalReport.DataSources.Add(source);
//ReportViewer1.DataBind();
//ReportViewer1.LocalReport.Refresh();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "sam_nbr";
//switch (StatusDDL.SelectedItem.Text)
//{
// case "打版室借出":
// Str搜尋日期 = "打版室借出時間";
// break;
// case "樣品室收到":
// Str搜尋日期 = "樣品室收到時間";
// break;
// case "樣品室還回":
// Str搜尋日期 = "樣品室還回時間";
// break;
// case "轉借TD":
// Str搜尋日期 = "轉借TD時間";
// break;
// case "打版室收回":
// Str搜尋日期 = "打版室收回時間";
// break;
// default:
// break;
//}
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@" select a.[sam_nbr],b.cus_style_no
,[SamCreateDate]
,[RentCreateDate]
,[RentModifyDate]
,[IsDelete]
,[IsClose]
,[借出狀態]
,[打版室借出時間]
,[樣品室收到時間]
,[樣品室還回時間]
,[轉借TD時間]
,[打版室收回時間]
,MappingData AS 狀態
FROM [dbo].[GGFSampleRent] a left join samc_reqm b on a.sam_nbr=b.sam_nbr
LEFT JOIN
dbo.Mapping AS d ON d.UsingDefine = 'GGFSampleRent' AND a.借出狀態 = d.Data
where convert(nvarchar(10),[RentCreateDate],120) between '{1}' and '{2}' {3} {0} "
, (string.IsNullOrEmpty(MutiTB.Text)) ? "" : string.Format(" and a.[sam_nbr] in ({0})", string.Join(",", parameters))
, DateRangeTB.Text.Substring(0, 10)
, DateRangeTB.Text.Substring(13, 10)
, (未歸還CB.Checked) ? " and IsDelete =0 and IsClose=0 and [打版室收回時間] is null " : "");
if (!string.IsNullOrEmpty(MutiTB.Text))
{
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportDataSource source = new ReportDataSource("View版套借出表", dt);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(source);
ReportViewer1.DataBind();
ReportViewer1.LocalReport.Refresh();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
/// <summary>
/// 將Session的資料上傳到資料庫
/// </summary>
public void F_UpLoad()
{
//有錯誤資料不給匯入
//if (SearchTB.Text.Trim() != "" && F_CheckData() && Session["ExcelError"] == null)
if (Session["ExcelError"] == null)
{
if (Session["Excel"] != null)
{
DataTable dt = (DataTable)Session["Excel"];
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("CreateBas_customs_rateImport");
command1.Connection = conn1;
command1.Transaction = transaction1;
try
{
DateTime dtTime = Convert.ToDateTime(Session["三巡時間年"].ToString() + "-" + Session["三巡時間月"].ToString() + "-01");
string str三巡時間巡 = Session["str三巡時間巡"].ToString();
string str三巡時間年 = Session["三巡時間年"].ToString();
string str三巡時間月 = Session["三巡時間月"].ToString();
DateTime dt三巡起, dt三巡迄;
switch (str三巡時間巡)
{
case "1":
dt三巡起 = Convert.ToDateTime(str三巡時間年 + "-" + str三巡時間月 + "-01");
dt三巡迄 = Convert.ToDateTime(str三巡時間年 + "-" + str三巡時間月 + "-10");
break;
case "2":
dt三巡起 = Convert.ToDateTime(str三巡時間年 + "-" + str三巡時間月 + "-11");
dt三巡迄 = Convert.ToDateTime(str三巡時間年 + "-" + str三巡時間月 + "-20");
break;
default:
dt三巡起 = Convert.ToDateTime(str三巡時間年 + "-" + str三巡時間月 + "-21");
dt三巡迄 = dtTime.AddMonths(1).AddDays(-1);
break;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
command1.CommandText = string.Format(@"INSERT INTO [dbo].[bas_customs_rate]
([site]
,[year_month]
,[period_type]
,[base_currency]
,[work_currency]
,[start_date]
,[end_date]
,[import_rate]
,[export_rate]
,[creator]
,[create_date]
,[modifier]
,[modify_date])
VALUES
(@site
,@year_month
,@period_type
,'NTD'
,@work_currency
,@start_date
,@end_date
,@import_rate
,@export_rate
,@creator
,getdate()
,@creator
,getdate()
)
");
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = 公司別DDL.SelectedValue;
command1.Parameters.Add("@year_month", SqlDbType.NVarChar).Value = str三巡時間年 + str三巡時間月;
command1.Parameters.Add("@period_type", SqlDbType.NVarChar).Value = str三巡時間巡;
command1.Parameters.Add("@work_currency", SqlDbType.NVarChar).Value = dt.Rows[i]["外匯幣別"].ToString();
command1.Parameters.Add("@start_date", SqlDbType.DateTime).Value = dt三巡起;
command1.Parameters.Add("@end_date", SqlDbType.DateTime).Value = dt三巡迄;
command1.Parameters.Add("@import_rate", SqlDbType.Decimal).Value = dt.Rows[i]["買進匯率"].ToString();
command1.Parameters.Add("@export_rate", SqlDbType.Decimal).Value = dt.Rows[i]["賣出匯率"].ToString();
command1.Parameters.Add("@creator", SqlDbType.NVarChar).Value = "104056";
command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
transaction1.Commit();
}
#region 匯入ErrorLog
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Import Excel Error :" + Session["FileName"].ToString(), "Finance016.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error Error:" + Session["FileName"].ToString(), "Finance016.aspx");
}
finally
{
transaction1.Rollback();
F_ErrorShow("匯入失敗請連絡MIS");
}
}
#endregion
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
Session.RemoveAll();
DataGV.DataSource = null;
DataGV.DataBind();
F_Show(false);
}
}
}
}
else
{
if (Session["ExcelError"] != null)
{
F_ErrorShow("請修正錯誤資料");
}
//else if (SearchTB.Text.Trim() != "")
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('當日已有匯入資料');</script>");
//else
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('請選擇匯入日期');</script>");
}
}
/// <summary>
/// 將Session的資料上傳到資料庫
/// </summary>
public void F_UpLoad()
{
//有錯誤資料不給匯入
//if (SearchTB.Text.Trim() != "" && F_CheckData() && Session["ExcelError"] == null)
if (Session["ExcelError"] == null)
{
if (Session["Excel"] != null)
{
DataTable dt = (DataTable)Session["Excel"];
int iIndex = 0;
//取得塞入資料流水號(TableName,程式)
if (iIndex > 0)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
command1.Connection = conn1;
command1.Transaction = transaction1;
try
{
#region 匯入單頭
//iIndex = addid.GetExcelIdex("AMZForcastHead", "TempExcelImport.aspx");
//command1.CommandText = string.Format(@"INSERT INTO [dbo].[工段總表明細]
// ([uid]
// ,[閱卷序號]
// ,[款號]
// ,[組別]
// ,[日期]
// ,[工號]
// ,[工段]
// ,[數量])
// VALUES
// ({0}
// ,@閱卷序號
// ,@款號
// ,@組別
// ,@日期
// ,@工號
// ,@工段
// ,@數量
// )
// ", iIndex);
//command1.Parameters.Add("@閱卷序號", SqlDbType.NVarChar).Value = dt.Rows[i]["閱卷序號"].ToString();
//command1.Parameters.Add("@款號", SqlDbType.NVarChar).Value = dt.Rows[i]["款號"].ToString();
//command1.Parameters.Add("@組別", SqlDbType.NVarChar).Value = dt.Rows[i]["組別"].ToString();
//command1.Parameters.Add("@日期", SqlDbType.NVarChar).Value = dt.Rows[i]["日期"].ToString();
//command1.Parameters.Add("@工號", SqlDbType.NVarChar).Value = dt.Rows[i]["工號"].ToString();
//command1.Parameters.Add("@工段", SqlDbType.NVarChar).Value = dt.Rows[i]["工段"].ToString();
//command1.Parameters.Add("@數量", SqlDbType.NVarChar).Value = dt.Rows[i]["數量"].ToString();
//command1.ExecuteNonQuery();
#endregion
#region 匯入明細
for (int i = 0; i < dt.Rows.Count; i++)
{
//command1.CommandText = string.Format(@"INSERT INTO [dbo].[工段總表明細]
// ([uid]
// ,[閱卷序號]
// ,[款號]
// ,[組別]
// ,[日期]
// ,[工號]
// ,[工段]
// ,[數量])
// VALUES
// ({0}
// ,@閱卷序號
// ,@款號
// ,@組別
// ,@日期
// ,@工號
// ,@工段
// ,@數量
// )
// ", iIndex);
//command1.Parameters.Add("@閱卷序號", SqlDbType.NVarChar).Value = dt.Rows[i]["閱卷序號"].ToString();
//command1.Parameters.Add("@款號", SqlDbType.NVarChar).Value = dt.Rows[i]["款號"].ToString();
//command1.Parameters.Add("@組別", SqlDbType.NVarChar).Value = dt.Rows[i]["組別"].ToString();
//command1.Parameters.Add("@日期", SqlDbType.NVarChar).Value = dt.Rows[i]["日期"].ToString();
//command1.Parameters.Add("@工號", SqlDbType.NVarChar).Value = dt.Rows[i]["工號"].ToString();
//command1.Parameters.Add("@工段", SqlDbType.NVarChar).Value = dt.Rows[i]["工段"].ToString();
//command1.Parameters.Add("@數量", SqlDbType.NVarChar).Value = dt.Rows[i]["數量"].ToString();
//command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
////上傳成功更新Head狀態
//command1.CommandText = string.Format(@"UPDATE [dbo].[Productivity_Head] SET [Flag] = 1 ,[Date] = @Date WHERE uid = {0} ", iIndex);
//command1.Parameters.Add("@Date", SqlDbType.NVarChar).Value = Session["Date"].ToString();
//command1.ExecuteNonQuery();
#endregion
transaction1.Commit();
}
#region 匯入ErrorLog
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Import Excel Error :" + Session["FileName"].ToString(), "VN002.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error Error:" + Session["FileName"].ToString(), "VN002.aspx");
}
finally
{
transaction1.Rollback();
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('匯入失敗請連絡MIS');</script>");
}
}
#endregion
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
Session.RemoveAll();
//Label1.Text = "資料上傳成功";
}
}
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('單頭匯入失敗請連絡MIS');</script>");
}
}
}
else
{
if (Session["ExcelError"] != null)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('請修正錯誤資料');</script>");
}
//else if (SearchTB.Text.Trim() != "")
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('當日已有匯入資料');</script>");
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('請選擇匯入日期');</script>");
}
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "cus_item_no";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"select
a.site,
dbo.F_NationName(e.site,nation_no) as '產區'
,dbo.F_VendorName(d.site,c.vendor_id) as '代工廠'
,a.pur_nbr as '採購單號'
,a.pur_seq as '採購序號'
,g.transatn_term as '交易條件'
,cus_item_no as '款號'
,pur_qty as '採購量'
,b.vendor_id as '廠商代號'
,i.UnCountQty as '不計價總量'
,i.RecQty as '已入庫量'
,a.pur_unit as '單位'
,a.pur_price as '單價'
,a.pur_amt as '金額'
,employee_name as '業務'
,a.overage_allow as '允收上限'
,h.item_no as '料號'
,a.org_item_no as '原始料號'
,f.item_name as '料號名稱'
,case when b.pur_kind = 'M' then '主料' when b.pur_kind = 'S' then '副料' else b.pur_kind end as '料號別'
,h.color_cname,h.color_ename ,f.item_spk as '料號規格'
,d.transatn_term 訂單交易條件
from purc_purchase_detail a
left join purc_purchase_master b on a.site=b.site and a.pur_nbr=b.pur_nbr
left join ordc_bah1 c on c.site=b.site and c.ord_nbr=b.ord_nbr
left join ordc_bah2 d on d.site=c.site and d.ord_nbr=c.ord_nbr
left join bas_employee e on e.site=b.site and b.buyer=e.employee_no
left join bas_vendor_mgt g on b.vendor_id=g.vendor_id and b.site=g.site
left join bas_item_master f on a.item_no =f.item_no and a.site=f.site
left join v_color h on h.item_no =a.item_no and h.site=a.site
left join View入庫數量 i on a.site=i.site and a.pur_nbr=i.pur_nbr and a.pur_seq =i.pur_seq
where {0} a.site='GGF' and pur_head_status<>'CA' and bah_status<>'CA'
and pur_detail_status <> 'CA' and item_status <>'CA'
{1} {2} {3} {4}"
, (!string.IsNullOrEmpty(MutiTB.Text))? Str搜尋參數 + " in ( " + string.Join(",", parameters) + " and " : ""
, (!string.IsNullOrEmpty(客戶TB.Text)) ? string.Format(" and cus_id = '{0}'", 客戶TB.Text) : ""
, (!string.IsNullOrEmpty(款號TB.Text)) ? string.Format(" and cus_item_no = '{0}'", 款號TB.Text) : ""
, string.Format(" and a.ord_nbr in (select ord_nbr from ordc_bat where last_date between '{0}' and '{1}' and bat_status<>'CA' and cancel_yn='N' )", DateRangeTB.Text.Substring(0, 10), DateRangeTB.Text.Substring(13, 10))
, (!string.IsNullOrEmpty(工廠DDL.SelectedValue)) ? string.Format(" and c.vendor_id ='{0}'", 工廠DDL.SelectedValue) : ""
);
//command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
if (MutiTB.Text.Length > 0)
{
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
//transaction1.Rollback();
//throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
Session["Sales022Data"] = dt;
GV.DataSource = dt;
GV.DataBind();
ExportBT.Visible = true;
}
else
{
ExportBT.Visible = false;
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "cus_item_no";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"select cus_item_no,ord_nbr,ord_qty
,LEFT(CONVERT(varchar,inv_date,112),6) as '入庫月份',sum(SumQty) as '當月入庫量'
from [192.168.0.152].VGG_GGF.dbo.View成衣入庫
where {1} in ( {0} ) and SumQty >0
group by cus_item_no,ord_nbr,ord_qty,LEFT(CONVERT(varchar,inv_date,112),6)
", string.Join(",", parameters), Str搜尋參數);
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportDataSource source = new ReportDataSource("View成品入庫查詢", dt);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(source);
ReportViewer1.DataBind();
ReportViewer1.LocalReport.Refresh();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "cus_item_no";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"
SELECT cus_item_no,0 as 'ord_seq','' as 'Cus_PO',
'' as 'color_cname'
,'' as 'color_ename'
,0 as 'size1_qty',
[size1], 0 as 'size2_qty'
,[size2], 0 as 'size3_qty'
,[size3], 0 as 'size4_qty'
,[size4], 0 as 'size5_qty'
,[size5], 0 as 'size6_qty'
,[size6], 0 as 'size7_qty'
,[size7], 0 as 'size8_qty'
,[size8], 0 as 'size9_qty'
,[size9], 0 as 'size10_qty'
,[size10], 0 as 'size11_qty'
,[size11], 0 as 'size12_qty'
,[size12], 0 as 'size13_qty'
,[size13], 0 as 'size14_qty'
,[size14], 0 as 'size15_qty'
,[size15]
FROM [dbo].[ordc_orders_size_side] a left join ordc_bah1 b on a.site=b.site and a.ord_nbr =b.ord_nbr
where {1} in ( {0} ) and size_flag=2 and input_way=1
union all
SELECT cus_item_no
,a.[ord_seq],
c.cust_po_nbr,
[color_cname]
,[color_ename]
,[color_qty1],convert(varchar(20),[color_price1])
,[color_qty2],convert(varchar(20),[color_price2])
,[color_qty3],convert(varchar(20),[color_price3])
,[color_qty4],convert(varchar(20),[color_price4])
,[color_qty5],convert(varchar(20),[color_price5])
,[color_qty6],convert(varchar(20),[color_price6])
,[color_qty7],convert(varchar(20),[color_price7])
,[color_qty8],convert(varchar(20),[color_price8])
,[color_qty9],convert(varchar(20),[color_price9])
,[color_qty10],convert(varchar(20),[color_price10])
,[color_qty11],convert(varchar(20),[color_price11])
,[color_qty12],convert(varchar(20),[color_price12])
,[color_qty13],convert(varchar(20),[color_price13])
,[color_qty14],convert(varchar(20),[color_price14])
,[color_qty15],convert(varchar(20),[color_price15])
FROM [dbo].[ordc_ration] a left join ordc_bah1 b on a.site=b.site and a.ord_nbr =b.ord_nbr left join ordc_bat c on a.ord_nbr=c.ord_nbr and a.site=c.site and a.ord_seq=c.ord_seq
where {1} in ( {0} )
", string.Join(",", parameters), Str搜尋參數);
//command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportDataSource source = new ReportDataSource("訂單多單價", dt);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(source);
ReportViewer1.DataBind();
ReportViewer1.LocalReport.Refresh();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
protected void AddBT_Click(object sender, EventArgs e)
{
string strCheck = CheckData();
if (strCheck == "")
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("InsertGGFRequestMark");
command1.Connection = conn1;
command1.Transaction = transaction1;
try
{
//TypeLB.Text = i.ToString();
command1.CommandText = string.Format(@"INSERT INTO [dbo].[GGFRequestMark]
([uid]
,[修改人員]
,[工號]
,[處理類別]
,[Creator]
,[處理時間]
,[件數]
,[備註]
,[原因碼]
,[原因]
)
VALUES
(@uid
,@修改人員
,@工號
,@處理類別
,@Creator
,@處理時間
,@件數
,@備註
,@原因碼
,@原因
)
");
command1.Parameters.Add("@uid", SqlDbType.Int).Value = Session["uid"].ToString();
command1.Parameters.Add("@修改人員", SqlDbType.NVarChar).Value = UserDDL.SelectedItem.Text;;
command1.Parameters.Add("@工號", SqlDbType.NVarChar).Value = UserDDL.SelectedValue;
command1.Parameters.Add("@處理類別", SqlDbType.Int).Value = TypeDDL.SelectedValue;
command1.Parameters.Add("@Creator", SqlDbType.NVarChar).Value = 使用者資料.取得使用者名稱();
command1.Parameters.Add("@處理時間", SqlDbType.NVarChar).Value = DateTB.Text;
command1.Parameters.Add("@件數", SqlDbType.NVarChar).Value = 件數TB.Text;
command1.Parameters.Add("@備註", SqlDbType.NVarChar).Value = 備註TB.Text;
command1.Parameters.Add("@原因碼", SqlDbType.NVarChar).Value = 原因碼DDL.SelectedValue.Trim();
command1.Parameters.Add("@原因", SqlDbType.NVarChar).Value = 原因碼DDL.SelectedItem.Text.Trim();
command1.ExecuteNonQuery();
command1.Parameters.Clear();
transaction1.Commit();
ClearData();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error :" + Session["SampleNbr"].ToString(), "Sample008.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error Error:" + Session["SampleNbr"].ToString(), "Sample008.aspx");
}
finally
{
transaction1.Rollback();
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('新增失敗請連絡MIS');</script>");
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('" + strCheck + "');</script>");
}
}
/// <summary>
/// 將Session的資料上傳到資料庫
/// </summary>
public void F_UpLoad()
{
if (Session["Excel"] != null)
{
DataTable dt = (DataTable)Session["Excel"];
int iIndex = 0;
//取得塞入資料流水號(TableName,程式)
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
command1.Connection = conn1;
command1.Transaction = transaction1;
int testi = 0, testj = 0;
try
{
#region 匯入
for (int i = 0; i < dt.Rows.Count; i++)
{
testi = i;
DateTime dt匯入時間 = Convert.ToDateTime(StartDayTB.Text);
iIndex = addid.GetExcelIdex("AMZForcastHead", "TempExcelImport.aspx");
command1.CommandText = string.Format(@"INSERT INTO [dbo].[AMZForcastHead]
([uid]
,[款號]
,[顏色]
,[尺寸]
,[匯入起始日期]
,[匯入結束日期]
,[訂單單位]
,[可用庫存])
VALUES
({0}
,@款號
,@顏色
,@尺寸
,@匯入起始日期
,@匯入結束日期
,@訂單單位
,@可用庫存)
", iIndex);
command1.Parameters.Add("@款號", SqlDbType.NVarChar).Value = dt.Rows[i]["款號"].ToString();
command1.Parameters.Add("@顏色", SqlDbType.NVarChar).Value = dt.Rows[i]["顏色"].ToString();
command1.Parameters.Add("@尺寸", SqlDbType.NVarChar).Value = dt.Rows[i]["尺寸"].ToString();
command1.Parameters.Add("@匯入起始日期", SqlDbType.VarChar).Value = dt匯入時間.ToString("yyyyMMdd");
command1.Parameters.Add("@匯入結束日期", SqlDbType.VarChar).Value = dt匯入時間.AddDays(7 * 26).ToString("yyyyMMdd");
command1.Parameters.Add("@訂單單位", SqlDbType.Int).Value = dt.Rows[i]["訂單單位"].ToString();
int i可用庫存量 = 0;
if (!string.IsNullOrEmpty(dt.Rows[i]["可用庫存"].ToString()))
{
int.TryParse(dt.Rows[i]["可用庫存"].ToString(), out i可用庫存量);
}
command1.Parameters.Add("@可用庫存", SqlDbType.Int).Value = i可用庫存量;
command1.ExecuteNonQuery();
command1.Parameters.Clear();
#region 匯入26週明細
for (int j = 1; j < 27; j++)
{
testj = j;
int weekqty = 0;
if (!string.IsNullOrEmpty(dt.Rows[i]["Week" + (j).ToString()].ToString()))
{
weekqty = Convert.ToInt16(dt.Rows[i]["Week" + (j).ToString()].ToString());
}
if (weekqty > 0)
{
command1.CommandText = string.Format(@"INSERT INTO [dbo].[AMZForcastLine]
([uid]
,[日期]
,[年度]
,[周數]
,[數量])
VALUES
({0}
,@日期
,@年度
,@周數
,@數量)
", iIndex);
command1.Parameters.Add("@日期", SqlDbType.DateTime).Value = dt匯入時間.AddDays((j - 1) * 7);
command1.Parameters.Add("@年度", SqlDbType.Int).Value = dt匯入時間.AddDays((j - 1) * 7).Year;
command1.Parameters.Add("@周數", SqlDbType.Int).Value = GetWeekOfYear(dt匯入時間.AddDays((j - 1) * 7));
//week1~~26
command1.Parameters.Add("@數量", SqlDbType.Int).Value = weekqty;
command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
}
#endregion
}
#endregion
//transaction1.Rollback();
transaction1.Commit();
}
#region 匯入ErrorLog
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Import Excel Error :" + Session["FileName"].ToString() + "testi=" + testi.ToString() + ",testj=" + testj.ToString(), "Sales011.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error Error:" + Session["FileName"].ToString(), "Sales011.aspx");
}
finally
{
transaction1.Rollback();
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('匯入失敗請連絡MIS');</script>");
}
}
#endregion
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
Session.RemoveAll();
//Label1.Text = "資料上傳成功";
}
}
}
}
protected void DbInit()
{
DataTable dt = new DataTable(), dt2 = new DataTable();
ErrorGV.DataSource = null;
ErrorGV.DataBind();
SamGV.DataSource = null;
SamGV.DataBind();
try
{
using (SqlConnection Conn = new SqlConnection(strConnectString))
{
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("CheckData").ToString(), Conn);
myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
}
#region query 使用 In
// using (SqlConnection conn1 = new SqlConnection(strConnectString))
// {
// SqlCommand command1 = conn1.CreateCommand();
// SqlTransaction transaction1;
// conn1.Open();
// transaction1 = conn1.BeginTransaction("createExcelImport");
// try
// {
// command1.Connection = conn1;
// command1.Transaction = transaction1;
// #region 匯入
// string[] parameters = SamArray.Select((s, i) => "@sam_nbr" + i.ToString()).ToArray();
// command1.CommandText = string.Format(@"SELECT a.receipt_date AS 發版日期,
// b.cus_name AS 客戶名稱, a.cus_style_no AS 款號,
// a.sam_nbr AS 打樣單號, dbo.F_DateToNull(a.samc_fin_date) AS 打版完成日期,
// a.samc_remark60 AS 備註, a.plan_fin_date AS 預計完成日,
// a.online_date AS 上線日期, a.samc_plan_fin_date AS 打版預計完成日,
// a.plan_fin_date AS 預計完日, dbo.F_DateToNull(a.last_date) AS 需求日
// ,dbo.F_DateToNull(a.samc_fin_date) 打版完成日
//from samc_reqm a left join bas_cus_master b on a.site=b.site and a.cus_id=b.cus_id
// where sam_nbr in ( {0} ) and a.site='GGF'
// ", string.Join(",", parameters));
// command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
// for (int i = 0; i < SamArray.Length; i++)
// command1.Parameters.AddWithValue(parameters[i], SamArray[i]);
// //command1.Parameters.Add("@sam_nbr", SqlDbType.DateTime).Value = DateRangeTB.Text;
// command1.ExecuteNonQuery();
// SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
// dt.Load(dr);
// #endregion
// //transaction1.Commit();
// }
// catch (Exception ex)
// {
// Log.ErrorLog(ex, "上傳失敗", StrProgram);
// transaction1.Rollback();
// throw;
// }
// finally
// {
// conn1.Close();
// transaction1.Dispose();
// }
// }
#endregion
if (dt.Rows.Count > 0)
{
DataView dv = new DataView(dt);
dv.RowFilter = " 打樣單狀態 = '無資料' or 打版完成日 = '無打版完成日'";
ErrorGV.DataSource = dv.ToTable();
ErrorGV.DataBind();
dv.RowFilter = String.Empty;
dv.RowFilter = " 打樣單狀態 = '有打樣單' and 打版完成日 <> '無打版完成日' ";
dt2 = dv.ToTable();
dv.RowFilter = String.Empty;
DataTable dt4 = new DataTable();
dv.RowFilter = "打版完成日 = '無打版完成日' and 打樣單狀態 = '有打樣單' ";
dt4 = dv.ToTable();
if (dt4.Rows.Count > 0)
{
SystemFunction systemFunction = new SystemFunction();
StringBuilder sb = new StringBuilder();
sb.Append(@"<h1><strong>樣品主副料到齊，未收到版套</strong></h1>
<table style='width: 498px; height: 180px;' border='1'>
<tbody>
<tr style='height: 52px;'>
<td style='width: 136px; height: 52px; text-align: center;'>
<h1><strong>打樣單號</strong></h1>
</td>
<td style='width: 143px; height: 52px; text-align: center;'>
<h1><strong>款號</strong></h1>
</td>
<td style='width: 130px; height: 52px;'>
<h1 style='text-align: center;'><strong>客戶代號</strong></h1>
</td>
</tr>");
foreach (DataRow item in dt4.Rows)
{
sb.AppendFormat(@"<tr style='height: 53.4688px;'>
<td style='width: 136px; height: 53.4688px; text-align: center;'><strong>{0}</strong></td>
<td style='width: 143px; height: 53.4688px; text-align: center;'><strong>{1}</strong></td>
<td style='width: 130px; height: 53.4688px; text-align: center;'><strong>{2}</strong></td>
</tr>", item["打樣單號"].ToString(), item["款號"].ToString(), item["客戶代號"].ToString());
}
sb.Append("</tbody></ table > ");
systemFunction.SendMail("[email protected];[email protected];", "樣品主副料到期，未收到版套", sb.ToString());
}
if (dt2.Rows.Count > 0)
{
if (UpDate(dt2))
{
using (SqlConnection Conn = new SqlConnection(strConnectString))
{
DataTable dt3 = new DataTable();
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("Search", dt2).ToString(), Conn);
myAdapter.Fill(dt3); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
SamGV.DataSource = dt3;
SamGV.DataBind();
}
}
}
//TESTGV.DataBind();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
catch (Exception ex)
{
Log.ErrorLog(ex, "資料搜尋異常", StrProgram);
F_ErrorShow("資料搜尋異常");
//throw;
}
}
protected void DbInit(string strtype = null)
{
DataTable dt = new DataTable();
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "";
string Strsql = "";
string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
Strsql = ((MutiTB.Text.Length > 0) ? Str搜尋參數 + " in (" + string.Join(",", parameters) + ") ":"");
if (SearchTB.Text.Length > 0)
{
Strsql = (MutiTB.Text.Length > 0) ? $" ( {Strsql} or item_no = '{SearchTB.Text}' " : $" item_no ='{SearchTB.Text}'";
}
Strsql = Strsql.Length > 0 ? " where " + Strsql : "";
command1.CommandText = string.Format($@"select * from View_AMZStock {Strsql} "
);
for (int i = 0; i < StrArrary.Length; i++)
{
command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
}
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
GV.DataSource = dt;
GV.DataBind();
ExportBT.Visible = true;
}
else
{
ExportBT.Visible = false;
F_ErrorShow("搜尋不到資料");
}
if (!string.IsNullOrEmpty(strtype))
{
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "AMZ_Stock");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", "AMZ_Stock"));
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
//using (SqlConnection Conn = new SqlConnection(strConnectString))
//{
// SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql().ToString(), Conn);
// myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//}
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
string Str搜尋參數 = "";
//string[] StrArrary = 字串處理.SplitEnter(MutiTB.Text);
//string[] parameters = 字串處理.QueryParameter(MutiTB.Text, Str搜尋參數);
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"
select a.shp_nbr,a.客戶 ,a.style_no,a.開航日,a.出貨數量/a.unit as 實際數量,a.出貨數量,a.出貨金額,a.pak_unit ,a.工廠名稱,預交量 as 訂單預交量 from (
select A.客戶,A.style_no,A.open_date as 開航日,sum(A.出貨數量) as 出貨數量,sum(A.出貨金額) as 出貨金額,B.pak_unit,A.工廠名稱
,case when pak_unit like 'PAK%' then RIGHT(pak_unit,1) else 1 end as unit,A.shp_nbr,預交量
from View出口DDP as A left join ordc_bah2 as B on A.site=B.site and A.ord_nbr=B.ord_nbr
where A.ord_nbr = B.ord_nbr and
A.shp_nbr between 'SB{0}0000' and 'SB{1}9999' {2}
group by A.客戶,A.style_no,A.open_date,B.pak_unit,A.shp_nbr,A.工廠名稱,預交量
) a
", DateRangeTB.Text.Substring(2, 4), DateRangeTB.Text.Substring(11, 4), string.IsNullOrEmpty(客戶搜尋TB.Text)?"":$" and upper(客戶) = @客戶");
if (!string.IsNullOrEmpty(客戶搜尋TB.Text))
{
command1.Parameters.Add("@客戶", SqlDbType.NVarChar).Value = 客戶搜尋TB.Text.ToUpper();
}
//for (int i = 0; i < StrArrary.Length; i++)
// command1.Parameters.AddWithValue(parameters[i], StrArrary[i]);
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
transaction1.Rollback();
//throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportDataSource source = new ReportDataSource("實際出貨資料", dt);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(source);
ReportViewer1.DataBind();
ReportViewer1.LocalReport.Refresh();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable();
#region query 使用 In
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createExcelImport");
try
{
command1.Connection = conn1;
command1.Transaction = transaction1;
#region 查詢
//string[] ParaFromDatatable =
command1.CommandText = string.Format(@"
select a.inv_nbr 庫存調撥單, b.inv_seq 庫存調撥序號,b.item_no 料號,d.item_spk 料號規格,b.ord_nbr 轉出號碼
,e.cus_item_no 轉出款號,b.ord_nbr_to 轉入號碼,f.cus_item_no 轉入款號
,case when c.pur_unit = 'KGS' then (b.transaction_qty*c.cloth_y_weight)/1000
when c.pur_unit ='LBS' then (b.transaction_qty*c.cloth_y_weight*2.2046)/1000
else b.transaction_qty end as 調撥數量
,case when c.pur_unit = 'KGS' or c.pur_unit ='LBS' then c.pur_unit
else b.transaction_unit end as 調撥單位
,c.pur_price 採購單價,c.pur_unit 採購單位
from
inv_transaction_master a left join
inv_transaction_detail b on a.site=b.site and a.inv_nbr=b.inv_nbr
left join (select distinct site,item_no,ord_nbr,pur_price,pur_unit,cloth_y_weight from purc_purchase_detail) c on b.site=c.site and b.item_no=c.item_no and b.ord_nbr=c.ord_nbr
left join bas_item_master d on b.site=d.site and b.item_no=d.item_no
left join ordc_bah1 e on b.ord_nbr=e.ord_nbr and b.site=e.site
left join ordc_bah1 f on b.ord_nbr_to=f.ord_nbr and b.site=f.site
where b.inv_detail_status is not null and b.ord_nbr <> b.ord_nbr_to {0} {1}
order by a.inv_nbr,b.inv_seq "
, (!string.IsNullOrEmpty(款號TB.Text)) ? string.Format(" and e.cus_item_no = '{0}'", 款號TB.Text) : ""
, (!string.IsNullOrEmpty(轉入款號TB.Text)) ? string.Format(" and f.cus_item_no = '{0}'", 轉入款號TB.Text) : ""
);
command1.ExecuteNonQuery();
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
#endregion
//transaction1.Commit();
}
catch (Exception ex)
{
Log.ErrorLog(ex, "Error", StrProgram);
//transaction1.Rollback();
//throw;
}
finally
{
conn1.Close();
transaction1.Dispose();
}
}
#endregion
if (dt.Rows.Count > 0)
{
Session["Sales023Data"] = dt;
GV.DataSource = dt;
GV.DataBind();
ExportBT.Visible = true;
}
else
{
ExportBT.Visible = false;
F_ErrorShow("搜尋不到資料");
}
}
protected void DbInit()
{
DataTable dt = new DataTable(), dt2 = new DataTable();
try
{
using (SqlConnection Conn = new SqlConnection(strConnectString))
{
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("CheckData").ToString(), Conn);
myAdapter.Fill(dt); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
}
#region query 使用 In
// using (SqlConnection conn1 = new SqlConnection(strConnectString))
// {
// SqlCommand command1 = conn1.CreateCommand();
// SqlTransaction transaction1;
// conn1.Open();
// transaction1 = conn1.BeginTransaction("createExcelImport");
// try
// {
// command1.Connection = conn1;
// command1.Transaction = transaction1;
// #region 匯入
// string[] parameters = SamArray.Select((s, i) => "@sam_nbr" + i.ToString()).ToArray();
// command1.CommandText = string.Format(@"SELECT a.receipt_date AS 發版日期,
// b.cus_name AS 客戶名稱, a.cus_style_no AS 款號,
// a.sam_nbr AS 打樣單號, dbo.F_DateToNull(a.samc_fin_date) AS 打版完成日期,
// a.samc_remark60 AS 備註, a.plan_fin_date AS 預計完成日,
// a.online_date AS 上線日期, a.samc_plan_fin_date AS 打版預計完成日,
// a.plan_fin_date AS 預計完日, dbo.F_DateToNull(a.last_date) AS 需求日
// ,dbo.F_DateToNull(a.samc_fin_date) 打版完成日
//from samc_reqm a left join bas_cus_master b on a.site=b.site and a.cus_id=b.cus_id
// where sam_nbr in ( {0} ) and a.site='GGF'
// ", string.Join(",", parameters));
// command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
// for (int i = 0; i < SamArray.Length; i++)
// command1.Parameters.AddWithValue(parameters[i], SamArray[i]);
// //command1.Parameters.Add("@sam_nbr", SqlDbType.DateTime).Value = DateRangeTB.Text;
// command1.ExecuteNonQuery();
// SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
// dt.Load(dr);
// #endregion
// //transaction1.Commit();
// }
// catch (Exception ex)
// {
// Log.ErrorLog(ex, "上傳失敗", StrProgram);
// transaction1.Rollback();
// throw;
// }
// finally
// {
// conn1.Close();
// transaction1.Dispose();
// }
// }
#endregion
if (dt.Rows.Count > 0)
{
DataView dv = new DataView(dt);
dv.RowFilter = " 打樣單狀態 <> '有打樣單' or 打樣處理 <> '有資料'";
ErrorGV.DataSource = dv.ToTable();
ErrorGV.DataBind();
dv.RowFilter = String.Empty;
dv.RowFilter = " 打樣單狀態 = '有打樣單' and 打樣處理 = '有資料'";
dt2 = dv.ToTable();
if (dt2.Rows.Count > 0)
{
if (UpDate(dt2))
{
using (SqlConnection Conn = new SqlConnection(strConnectString))
{
DataTable dt3 = new DataTable();
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("Search").ToString(), Conn);
myAdapter.Fill(dt3); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
SamGV.DataSource = dt3;
SamGV.DataBind();
}
}
}
//TESTGV.DataBind();
}
else
{
F_ErrorShow("搜尋不到資料");
}
}
catch (Exception ex)
{
Log.ErrorLog(ex, "資料搜尋異常", StrProgram);
F_ErrorShow("資料搜尋異常");
//throw;
}
}
