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>"); } } }