private void SearchReportData(string strType, string strsql)
{
//if(Ds.Tables[strType].Rows.Count>0)
// Ds.Tables[strType].Clear();
using (SqlConnection conn = new SqlConnection(strConnectString))
{
conn.Open();
//Create a SqlConnection to the Northwind database.
SqlCommand command = new SqlCommand(strsql, conn);
command.CommandType = CommandType.Text;
try
{
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.SelectCommand = command;
adapter.Fill(Ds, strType);
}
catch (Exception ex)
{
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
Log.ErrorLog(ex, strType + " Search Data", "TAX001.aspx");
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('資料搜尋錯誤:\\n" + ex.Message + "\\n請洽MIS Stone');</script>");
}
finally
{
conn.Close();
}
}
}
public DataTable SQLToDataSet(string strConnectString, string strSql, string strTableName, string strFunctionName)
{
DataTable Dt = new DataTable();
using (SqlConnection conn = new SqlConnection(strConnectString))
{
Dt.TableName = strTableName;
conn.Open();
//Create a SqlConnection to the Northwind database.
SqlCommand command = new SqlCommand(strSql, conn);
command.CommandType = CommandType.Text;
try
{
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.SelectCommand = command;
adapter.Fill(Dt);
}
catch (Exception ex)
{
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
Log.ErrorLog(ex, strTableName + " Search Data", strFunctionName);
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('資料搜尋錯誤:\\n" + ex.Message + "\\n請洽MIS Stone');</script>");
}
finally
{
conn.Close();
}
}
return(Dt);
}
protected void SaveBT2_Click(object sender, EventArgs e)
{
int iGVCount = 0;
string suid = "";
iGVCount = AcrTicketGV.Rows.Count;
for (int i = 0; i < iGVCount; i++)
{
CheckBox chk = (CheckBox)AcrTicketGV.Rows[i].Cells[0].FindControl("CheckBox1");
if (chk.Checked)
{
suid += string.Format(",{0} ", AcrTicketGV.Rows[i].Cells[1].Text);
}
}
if (suid.Length > 0 && string.IsNullOrEmpty(TicketBT.Text.Trim()) == false)
{
Boolean bSessuceCheck = false;
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createphone");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
command1.CommandText = string.Format(@"UPDATE [dbo].[acr_trn_check] SET [ticket] = @ticket WHERE uid in ({0}) ", suid.Substring(1));
command1.Parameters.Add("@ticket", SqlDbType.NVarChar).Value = TicketBT.Text.Trim();
command1.ExecuteNonQuery();
command1.Parameters.Clear();
transaction1.Commit();
bSessuceCheck = true;
TicketBT.Text = "";
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error", "TAX003.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error2", "TAX003.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
if (bSessuceCheck)
{
DbInit();
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('資料更新失敗，請跟MIS確認');</script>");
}
}
}
}
else
{
string sAlertMessage = "";
if (string.IsNullOrEmpty(TicketBT.Text.Trim()))
{
sAlertMessage = "發票號碼未填";
}
if (suid.Length == 0)
{
sAlertMessage += (sAlertMessage.Length > 0) ? string.Format("\\n{0}", "未勾選應收單據"): string.Format("{0}", "未勾選應收單據");
}
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('" + sAlertMessage + "');</script>");
}
}
//protected void PhoneGV_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
//{
// //SELECT uid,[phone] as '分機'
// // ,[name] as '員工姓名'
// // ,[empolyee_no] as '員工編號'
// // ,[eng_name] as '英文姓名'
// // ,[email] as 'Email'
// // ,[skype_account] as 'Skype'
// // ,[location] as '位置'
// // FROM[dbo].[GGFPhoneNumber]
// UidLB.Text= PhoneGV.Rows[e.NewSelectedIndex].Cells[1].Text.ToString();
// PhoneLB.Text = PhoneGV.Rows[e.NewSelectedIndex].Cells[2].Text.ToString();
// NameTB.Text = PhoneGV.Rows[e.NewSelectedIndex].Cells[3].Text.ToString().Replace(" ", "");
// NumberBT.Text = PhoneGV.Rows[e.NewSelectedIndex].Cells[4].Text.ToString().Replace(" ", "");
// EngName.Text = PhoneGV.Rows[e.NewSelectedIndex].Cells[5].Text.ToString().Replace(" ", "");
// EmailTB.Text= PhoneGV.Rows[e.NewSelectedIndex].Cells[6].Text.ToString().Replace(" ", "");
// SkypeBT.Text = PhoneGV.Rows[e.NewSelectedIndex].Cells[7].Text.ToString().Replace(" ", "");
// LocationDDL.SelectedValue = PhoneGV.Rows[e.NewSelectedIndex].Cells[8].Text.ToString();
// POPPanel_ModalPopupExtender.Show();
//}
protected void SaveBT_Click(object sender, EventArgs e)
{
POPPanel_ModalPopupExtender.Hide();
if (string.IsNullOrEmpty(NameTB.Text) == false)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createphone");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
command1.CommandText = @"
UPDATE [dbo].[GGFPhoneNumber]
SET [name] = @name
,[empolyee_no] = @empolyee_no
,[eng_name] = @eng_name
,[email] = @email
,[skype_account] = @skype_account
,[location] = @location
,[modify_day] = getdate()
,[modifier] = 'Progrma'
WHERE uid = @uid
";
command1.Parameters.Add("@uid", SqlDbType.Int).Value = UidLB.Text;
command1.Parameters.Add("@name", SqlDbType.NVarChar).Value = NameTB.Text.Trim();
command1.Parameters.Add("@empolyee_no", SqlDbType.NVarChar).Value = NumberBT.Text.Trim();
command1.Parameters.Add("@eng_name", SqlDbType.NVarChar).Value = EngName.Text.Trim();
command1.Parameters.Add("@email", SqlDbType.NVarChar).Value = EmailTB.Text.Trim();
command1.Parameters.Add("@skype_account", SqlDbType.NVarChar).Value = SkypeBT.Text.Trim();
command1.Parameters.Add("@location", SqlDbType.NVarChar).Value = LocationDDL.SelectedValue.ToString();
command1.ExecuteNonQuery();
command1.Parameters.Clear();
transaction1.Commit();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error", "TAX002.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error2", "TAX002.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('請輸入名稱');</script>");
}
}
protected void ConvertBT_Click(object sender, EventArgs e)
{
//測試
if (Ds.Tables.Contains("SelectedAcr"))
{
if (Ds.Tables["SelectedAcr"].Rows.Count > 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('已有結轉資料:\\n請洽MIS Stone');</script>");
}
}
else
{
if (Ds.Tables.Contains("purc_pkd_for_acr") && Ds.Tables.Contains("purc_pkm_for_acr"))
{
//單頭單身要有資料
if (Ds.Tables["purc_pkd_for_acr"].Rows.Count > 0)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createpkd");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
for (int i = 0; i < Ds.Tables["purc_pkd_for_acr"].Rows.Count; i++)
{
string strtimestamp = DateTime.Now.ToString("yyyyMMddHHmmssffffff");
#region 表頭
command1.CommandText = @"INSERT INTO [dbo].[purc_pkd_for_acr]
(
[timestamp],[site],[pak_nbr],[pak_seq],[CheckCreateDate],[cus_item_no],[rec_nbr]
,[rec_seq],[stockroom],[rs_type],[uncount_qty],[coint_qty],[item_no],[box_no],[box_qty]
,[tot_net_wt],[tot_gross_wt],[cube_feet],[rec_unit],[pur_price],[rec_qty],[customs_decleartion_price]
,[customs_decleartion_amt],[clear_Customs_price],[currency_id],[combine],[pkd_status],[filter_creator]
,[filter_dept],[creator],[create_date],[modifier],[modify_date],[vendor_id]
)
VALUES
(
@timestamp,@site,@pak_nbr,@pak_seq,@CheckCreateDate,@cus_item_no,@rec_nbr
,@rec_seq,@stockroom,@rs_type,@uncount_qty,@coint_qty,@item_no,@box_no,@box_qty
,@tot_net_wt,@tot_gross_wt,@cube_feet,@rec_unit,@pur_price,@rec_qty,@customs_decleartion_price
,@customs_decleartion_amt,@clear_Customs_price,@currency_id,@combine,@pkd_status,@filter_creator
,@filter_dept,@creator,@create_date,@modifier,@modify_date,@vendor_id
)";
command1.Parameters.Add("@create_timestamp", SqlDbType.VarChar).Value = strtimestamp;
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["site"];
command1.Parameters.Add("@pak_nbr", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["pak_nbr"];
command1.Parameters.Add("@pak_seq", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["pak_seq"];
command1.Parameters.Add("@CheckCreateDate", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["CheckCreateDate"];
command1.Parameters.Add("@cus_item_no", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["cus_item_no"];
command1.Parameters.Add("@rec_nbr", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["rec_nbr"];
command1.Parameters.Add("@rec_seq", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["rec_seq"];
command1.Parameters.Add("@stockroom", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["stockroom"];
command1.Parameters.Add("@rs_type", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["rs_type"];
command1.Parameters.Add("@uncount_qty", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["uncount_qty"];
command1.Parameters.Add("@coint_qty", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["coint_qty"];
command1.Parameters.Add("@item_no", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["item_no"];
command1.Parameters.Add("@box_no", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["box_no"];
command1.Parameters.Add("@box_qty", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["box_qty"];
command1.Parameters.Add("@tot_net_wt", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["tot_net_wt"];
command1.Parameters.Add("@tot_gross_wt", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["tot_gross_wt"];
command1.Parameters.Add("@cube_feet", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["cube_feet"];
command1.Parameters.Add("@rec_unit", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["rec_unit"];
command1.Parameters.Add("@pur_price", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["pur_price"];
command1.Parameters.Add("@rec_qty", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["rec_qty"];
command1.Parameters.Add("@customs_decleartion_price", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["customs_decleartion_price"];
command1.Parameters.Add("@customs_decleartion_amt", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["customs_decleartion_amt"];
command1.Parameters.Add("@clear_Customs_price", SqlDbType.Int).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["clear_Customs_price"];
command1.Parameters.Add("@currency_id", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["currency_id"];
command1.Parameters.Add("@combine", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["combine"];
command1.Parameters.Add("@pkd_status", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["pkd_status"];
command1.Parameters.Add("@filter_creator", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["filter_creator"];
command1.Parameters.Add("@filter_dept", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["filter_dept"];
command1.Parameters.Add("@creator", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["creator"];
command1.Parameters.Add("@create_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["create_date"];
command1.Parameters.Add("@modifier", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["modifier"];
command1.Parameters.Add("@modify_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["modify_date"];
command1.Parameters.Add("@vendor_id", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkd_for_acr"].Rows[i]["vendor_id"];
#endregion
command1.ExecuteNonQuery();
command1.Parameters.Clear();
for (int j = 0; j < Ds.Tables["purc_pkm_for_acr"].Rows.Count; j++)
{
command1.CommandText = @"INSERT INTO [dbo].[purc_pkm_for_acr]
(
[timestamp],[site],[pak_nbr],[pak_date],[sono],[carrier_id]
,[pick_port],[port_of_destination],[owner],[sender],[addressee],[vessel],[voyage],[cabinet_no]
,[cabinet_type],[dta_date],[etd_date],[etc_date],[arrival_date],[pkm_status],[tot_box_qty]
,[tot_net_wt],[tot_gross_wt],[tot_cube_feet],[filter_creator],[filter_dept],[creator],[create_date]
,[modifier],[modify_date],[so_nbr],[decl_no],[bol_no],[ship_cost],[ins_amt],[pur_kind],[vendor_id]
)
VALUES
(
@timestamp,@site,@pak_nbr,@pak_date,@sono,@carrier_id
,@pick_port,@port_of_destination,@owner,@sender,@addressee,@vessel,@voyage,@cabinet_no
,@cabinet_type,@dta_date,@etd_date,@etc_date,@arrival_date,@pkm_status,@tot_box_qty
,@tot_net_wt,@tot_gross_wt,@tot_cube_feet,@filter_creator,@filter_dept,@creator,@create_date
,@modifier,@modify_date,@so_nbr,@decl_no,@bol_no,@ship_cost,@ins_amt,@pur_kind,@vendor_id
)";
#region 表身
command1.Parameters.Add("@create_timestamp", SqlDbType.VarChar).Value = strtimestamp;
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["site"];
command1.Parameters.Add("@pak_nbr", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["pak_nbr"];
command1.Parameters.Add("@pak_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["pak_date"];
command1.Parameters.Add("@sono", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["sono"];
command1.Parameters.Add("@carrier_id", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["carrier_id"];
command1.Parameters.Add("@pick_port", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["pick_port"];
command1.Parameters.Add("@port_of_destination", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["port_of_destination"];
command1.Parameters.Add("@owner", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["owner"];
command1.Parameters.Add("@sender", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["sender"];
command1.Parameters.Add("@addressee", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["addressee"];
command1.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["vessel"];
command1.Parameters.Add("@voyage", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["voyage"];
command1.Parameters.Add("@cabinet_no", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["cabinet_no"];
command1.Parameters.Add("@cabinet_type", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["cabinet_type"];
command1.Parameters.Add("@dta_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["dta_date"];
command1.Parameters.Add("@etd_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["etd_date"];
command1.Parameters.Add("@etc_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["etc_date"];
command1.Parameters.Add("@arrival_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["arrival_date"];
command1.Parameters.Add("@pkm_status", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["pkm_status"];
command1.Parameters.Add("@tot_box_qty", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["tot_box_qty"];
command1.Parameters.Add("@tot_net_wt", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["tot_net_wt"];
command1.Parameters.Add("@tot_gross_wt", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["tot_gross_wt"];
command1.Parameters.Add("@tot_cube_feet", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["tot_cube_feet"];
command1.Parameters.Add("@filter_creator", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["filter_creator"];
command1.Parameters.Add("@filter_dept", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["filter_dept"];
command1.Parameters.Add("@creator", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["creator"];
command1.Parameters.Add("@create_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["create_date"];
command1.Parameters.Add("@modifier", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["modifier"];
command1.Parameters.Add("@modify_date", SqlDbType.DateTime).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["modify_date"];
command1.Parameters.Add("@so_nbr", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["so_nbr"];
command1.Parameters.Add("@decl_no", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["decl_no"];
command1.Parameters.Add("@bol_no", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["bol_no"];
command1.Parameters.Add("@ship_cost", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["ship_cost"];
command1.Parameters.Add("@ins_amt", SqlDbType.Int).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["ins_amt"];
command1.Parameters.Add("@pur_kind", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["pur_kind"];
command1.Parameters.Add("@vendor_id", SqlDbType.NVarChar).Value = Ds.Tables["purc_pkm_for_acr"].Rows[j]["vendor_id"];
#endregion
command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
}
transaction1.Commit();
DBInit2();//Search Data
DBInit();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error", "TAX002.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error2", "TAX002.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
}
else
{
}
}
}
protected void ConvertBT_Click(object sender, EventArgs e)
{
//測試
if (Ds.Tables.Contains("SelectedAcr"))
{
if (Ds.Tables["SelectedAcr"].Rows.Count > 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('已有結轉資料:\\n請洽MIS Stone');</script>");
}
}
else
{
if (Ds.Tables.Contains("Acr"))
{
if (Ds.Tables["Acr"].Rows.Count > 0)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createOrder");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
for (int i = 0; i < Ds.Tables["Acr"].Rows.Count; i++)
{
command1.CommandText = @"INSERT INTO [dbo].[acr_trn_check]
([create_timestamp],[site],[kind],[acr_nbr],[acr_date],[transaction_code],[transaction_class],[reverse_code]
,[offset_method],[cus_id],[invoice_nbr],[employee_no],[dept_no],[area_no],[pay_term],[carrier_id],[transatn_term],[currency_id],[exchange_rate]
,[offset_exchange],[offset_currency],[local_invoice_no],[tax_code],[tax_amt],[affect_column],[discount_rate],[discount_amt],[cash_flag],[data_source]
,[acr_seq],[order_nbr],[order_seq],[adjust_code],[reference_no],[foreign_amt],[base_amt],[remark40],[reason],[rs_type],[item_no],[stockroom],[unit]
,[unit_price],[acr_qty],[detail_amt],[iss_main_bank],[iss_branch_bank],[iss_bank_account],[cash_main_bank],[cash_branch_bank],[cash_bank_account]
,[note_nbr],[note_type],[note_owner],[note_city],[note_issuer],[acr_counter_date],[acr_due_date],[note_due_date],[note_rec_date],[posted_bal]
,[posted_nms],[posted_ais],[acr_status],[create_date],[modifier],[modify_date],[project_id],[adv_tax_code],[account_date],[team_dept],[keyin_user]
,[issuer],[ord_nbr],[ord_seq],[cust_po_nbr],[prod_nbr],[ord_class],[item_statistic],[style_no])
VALUES
( @create_timestamp,@site,@kind,@acr_nbr,@acr_date,@transaction_code,@transaction_class,@reverse_code
,@offset_method,@cus_id,@invoice_nbr,@employee_no,@dept_no,@area_no,@pay_term,@carrier_id,@transatn_term,@currency_id,@exchange_rate
,@offset_exchange,@offset_currency,@local_invoice_no,@tax_code,@tax_amt,@affect_column,@discount_rate,@discount_amt,@cash_flag,@data_source
,@acr_seq,@order_nbr,@order_seq,@adjust_code,@reference_no,@foreign_amt,@base_amt,@remark40,@reason,@rs_type,@item_no,@stockroom,@unit
,@unit_price,@acr_qty,@detail_amt,@iss_main_bank,@iss_branch_bank,@iss_bank_account,@cash_main_bank,@cash_branch_bank,@cash_bank_account
,@note_nbr,@note_type,@note_owner,@note_city,@note_issuer,@acr_counter_date,@acr_due_date,@note_due_date,@note_rec_date,@posted_bal
,@posted_nms,@posted_ais,@acr_status,@create_date,@modifier,@modify_date,@project_id,@adv_tax_code,@account_date,@team_dept,@keyin_user
,@issuer,@ord_nbr,@ord_seq,@cust_po_nbr,@prod_nbr,@ord_class,@item_statistic,@style_no)
";
#region SQLData
command1.Parameters.Add("@create_timestamp", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["create_timestamp"];
//command1.Parameters.Add("@CheckFlag", SqlDbType.NVarChar).Value = "NA";//NA新增狀態
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["site"];
command1.Parameters.Add("@kind", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["kind"];
command1.Parameters.Add("@acr_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["acr_nbr"];
command1.Parameters.Add("@acr_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["acr_date"];
command1.Parameters.Add("@transaction_code", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["transaction_code"];
command1.Parameters.Add("@transaction_class", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["transaction_class"];
command1.Parameters.Add("@reverse_code", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["reverse_code"];
command1.Parameters.Add("@offset_method", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["offset_method"];
command1.Parameters.Add("@cus_id", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cus_id"];
command1.Parameters.Add("@invoice_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["invoice_nbr"];
command1.Parameters.Add("@employee_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["employee_no"];
command1.Parameters.Add("@dept_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["dept_no"];
command1.Parameters.Add("@area_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["area_no"];
command1.Parameters.Add("@pay_term", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["pay_term"];
command1.Parameters.Add("@carrier_id", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["carrier_id"];
command1.Parameters.Add("@transatn_term", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["transatn_term"];
command1.Parameters.Add("@currency_id", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["currency_id"];
command1.Parameters.Add("@exchange_rate", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["exchange_rate"];
command1.Parameters.Add("@offset_exchange", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["offset_exchange"];
command1.Parameters.Add("@offset_currency", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["offset_currency"];
command1.Parameters.Add("@local_invoice_no", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["local_invoice_no"];
command1.Parameters.Add("@tax_code", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["tax_code"];
command1.Parameters.Add("@tax_amt", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["tax_amt"];
command1.Parameters.Add("@affect_column", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["affect_column"];
command1.Parameters.Add("@discount_rate", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["discount_rate"];
command1.Parameters.Add("@discount_amt", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["discount_amt"];
command1.Parameters.Add("@cash_flag", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cash_flag"];
command1.Parameters.Add("@data_source", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["data_source"];
command1.Parameters.Add("@acr_seq", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["acr_seq"];
command1.Parameters.Add("@order_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["order_nbr"];
command1.Parameters.Add("@order_seq", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["order_seq"];
command1.Parameters.Add("@adjust_code", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["adjust_code"];
command1.Parameters.Add("@reference_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["reference_no"];
command1.Parameters.Add("@foreign_amt", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["foreign_amt"];
command1.Parameters.Add("@base_amt", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["base_amt"];
command1.Parameters.Add("@remark40", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["remark40"];
command1.Parameters.Add("@reason", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["reason"];
command1.Parameters.Add("@rs_type", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["rs_type"];
command1.Parameters.Add("@item_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["item_no"];
command1.Parameters.Add("@stockroom", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["stockroom"];
command1.Parameters.Add("@unit", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["unit"];
command1.Parameters.Add("@unit_price", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["unit_price"];
command1.Parameters.Add("@acr_qty", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["acr_qty"];
command1.Parameters.Add("@detail_amt", SqlDbType.Int).Value = Ds.Tables["Acr"].Rows[i]["detail_amt"];
command1.Parameters.Add("@iss_main_bank", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["iss_main_bank"];
command1.Parameters.Add("@iss_branch_bank", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["iss_branch_bank"];
command1.Parameters.Add("@iss_bank_account", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["iss_bank_account"];
command1.Parameters.Add("@cash_main_bank", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cash_main_bank"];
command1.Parameters.Add("@cash_branch_bank", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cash_branch_bank"];
command1.Parameters.Add("@cash_bank_account", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cash_bank_account"];
command1.Parameters.Add("@note_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["note_nbr"];
command1.Parameters.Add("@note_type", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["note_type"];
command1.Parameters.Add("@note_owner", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["note_owner"];
command1.Parameters.Add("@note_city", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["note_city"];
command1.Parameters.Add("@note_issuer", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["note_issuer"];
command1.Parameters.Add("@acr_counter_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["acr_counter_date"];
command1.Parameters.Add("@acr_due_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["acr_due_date"];
command1.Parameters.Add("@note_due_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["note_due_date"];
command1.Parameters.Add("@note_rec_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["note_rec_date"];
command1.Parameters.Add("@posted_bal", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["posted_bal"];
command1.Parameters.Add("@posted_nms", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["posted_nms"];
command1.Parameters.Add("@posted_ais", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["posted_ais"];
command1.Parameters.Add("@acr_status", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["acr_status"];
command1.Parameters.Add("@create_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["create_date"];
command1.Parameters.Add("@modifier", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["modifier"];
command1.Parameters.Add("@modify_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["modify_date"];
command1.Parameters.Add("@project_id", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["project_id"];
command1.Parameters.Add("@adv_tax_code", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["adv_tax_code"];
command1.Parameters.Add("@account_date", SqlDbType.DateTime).Value = Ds.Tables["Acr"].Rows[i]["account_date"];
command1.Parameters.Add("@team_dept", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["team_dept"];
command1.Parameters.Add("@keyin_user", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["keyin_user"];
command1.Parameters.Add("@issuer", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["issuer"];
command1.Parameters.Add("@ord_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["ord_nbr"];
command1.Parameters.Add("@ord_seq", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["ord_seq"];
command1.Parameters.Add("@cust_po_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["cust_po_nbr"];
command1.Parameters.Add("@prod_nbr", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["prod_nbr"];
command1.Parameters.Add("@ord_class", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["ord_class"];
command1.Parameters.Add("@item_statistic", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["item_statistic"];
command1.Parameters.Add("@style_no", SqlDbType.NVarChar).Value = Ds.Tables["Acr"].Rows[i]["style_no"];
#endregion
command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
transaction1.Commit();
DBInit2();//Search Data
DBInit();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error", "TAX001.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error2", "TAX001.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
}
}
}
protected void DeleteBT_Click(object sender, EventArgs e)
{
if (Ds.Tables["SelectedAcr"].Rows.Count > 0)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createOrder");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
string strwhere = "";
for (int i = 0; i < Ds.Tables["SelectedAcr"].Rows.Count; i++)
{
if (i > 0)
{
strwhere += " , ";
}
strwhere += " '" + Ds.Tables["SelectedAcr"].Rows[i]["uid"] + "' ";
}
command1.CommandText = @"UPDATE [dbo].[acr_trn_check]
SET
[CheckFlag] = 'CA'
,[CheckModifyDate] =getdate()
WHERE uid in (" + strwhere + ") ";
command1.ExecuteNonQuery();
command1.Parameters.Clear();
transaction1.Commit();
DBInit2();//Search Data
DBInit();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Delete Error", "TAX001.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Delete Error2", "TAX001.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
}
protected void ConvertBT_Click(object sender, EventArgs e)
{
//POPPanel_ModalPopupExtender.Show();
//測試
if (Ds.Tables.Contains("SelectedAcr"))
{
if (Ds.Tables["SelectedAcr"].Rows.Count > 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('已有結轉資料\\n請先刪除再重新節轉');</script>");
}
}
else
{
if (Ds.Tables.Contains("PKM"))
{
//單頭單身要有資料
if (Ds.Tables["PKM"].Rows.Count > 0)
{
using (SqlConnection conn1 = new SqlConnection(strConnectString))
{
SqlCommand command1 = conn1.CreateCommand();
SqlTransaction transaction1;
conn1.Open();
transaction1 = conn1.BeginTransaction("createpkd");
command1.Connection = conn1;
command1.Transaction = transaction1;
ReferenceCode.SysLog Log = new ReferenceCode.SysLog();
try
{
foreach (DataRow pkdDT in Ds.Tables["PKM"].Rows)
{
string strtimestamp = DateTime.Now.ToString("yyyyMMddHHmmssffffff");
#region 表頭
command1.CommandText = @"INSERT INTO [dbo].[purc_pkd_for_acr]
([timestamp],[site],[pak_nbr],[pak_seq],[cus_item_no],[rec_nbr]
,[rec_seq],[stockroom],[rs_type],[uncount_qty],[coint_qty],[item_no]
,[box_no],[box_qty],[tot_net_wt],[tot_gross_wt],[cube_feet],[rec_unit]
,[pur_price],[rec_qty],[customs_decleartion_price],[customs_decleartion_amt]
,[clear_Customs_price],[currency_id],[combine],[pkd_status],[filter_creator]
,[filter_dept],[creator],[create_date],[modifier],[modify_date],[vendor_id])
select
@timestamp,[site],[pak_nbr],[pak_seq],[cus_item_no],[rec_nbr]
,[rec_seq],[stockroom],[rs_type],[uncount_qty],[coint_qty]
,[item_no],[box_no],[box_qty],[tot_net_wt],[tot_gross_wt]
,[cube_feet],[rec_unit],[pur_price],[rec_qty],[customs_decleartion_price]
,[customs_decleartion_amt],[clear_Customs_price],[currency_id],[combine]
,[pkd_status],[filter_creator],[filter_dept],[creator]
,[create_date],[modifier],[modify_date],[vendor_id]
from [dbo].[purc_pkd]
where [site]=@site and [pak_nbr]=@pak_nbr
";
command1.Parameters.Add("@timestamp", SqlDbType.VarChar).Value = strtimestamp;
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = pkdDT["site"];
command1.Parameters.Add("@pak_nbr", SqlDbType.NVarChar).Value = pkdDT["pak_nbr"];
#endregion
command1.ExecuteNonQuery();
command1.Parameters.Clear();
command1.CommandText = @"INSERT INTO [dbo].[purc_pkd_for_acr]
([timestamp],[site],[pak_nbr],[pak_seq],[cus_item_no],[rec_nbr]
,[rec_seq],[stockroom],[rs_type],[uncount_qty],[coint_qty],[item_no]
,[box_no],[box_qty],[tot_net_wt],[tot_gross_wt],[cube_feet]
,[rec_unit],[pur_price],[rec_qty],[customs_decleartion_price]
,[customs_decleartion_amt],[clear_Customs_price],[currency_id]
,[combine],[pkd_status],[filter_creator],[filter_dept],[creator]
,[create_date],[modifier],[modify_date],[vendor_id])
select
@timestamp,[site],[pak_nbr],[pak_seq],[cus_item_no],[rec_nbr]
,[rec_seq],[stockroom],[rs_type],[uncount_qty],[coint_qty],[item_no]
,[box_no],[box_qty],[tot_net_wt],[tot_gross_wt],[cube_feet]
,[rec_unit],[pur_price],[rec_qty],[customs_decleartion_price]
,[customs_decleartion_amt],[clear_Customs_price],[currency_id]
,[combine],[pkd_status],[filter_creator],[filter_dept],[creator]
,[create_date],[modifier],[modify_date],[vendor_id]
from [purc_pkd]
where [site]=@site and [pak_nbr]=@pak_nbr
";
#region 表身
command1.Parameters.Add("@timestamp", SqlDbType.VarChar).Value = strtimestamp;
command1.Parameters.Add("@site", SqlDbType.NVarChar).Value = pkdDT["site"];
command1.Parameters.Add("@pak_nbr", SqlDbType.NVarChar).Value = pkdDT["pak_nbr"];
#endregion
command1.ExecuteNonQuery();
command1.Parameters.Clear();
}
transaction1.Commit();
DBInit2();//Search Data
DBInit();
}
catch (Exception ex1)
{
try
{
Log.ErrorLog(ex1, "Insert Error", "TAX002.aspx");
}
catch (Exception ex2)
{
Log.ErrorLog(ex2, "Insert Error2", "TAX002.aspx");
}
finally
{
transaction1.Rollback();
}
}
finally
{
conn1.Close();
conn1.Dispose();
command1.Dispose();
}
}
}
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('沒有結轉資料');</script>");
}
}
}
