private void 控管視窗_Load(object sender, EventArgs e) { SqlDataAdapter Ad = new SqlDataAdapter(" SELECT SEL=CAST(0 AS BIT),FULLDATE=CONVERT(varchar(100), FULLDATE, 111),預算申請,預算部門 FROM PEN007 " + " WHERE PEN007001 = '1' AND PEN007006 = '0' AND FULLDATE BETWEEN CONVERT(varchar(100), GETDATE(), 111) AND CONVERT(varchar(100), GETDATE() + 14, 111) " + " ORDER BY FULLDATE ", GonGinVariable.SqlConnectString); Ad.SelectCommand.CommandType = CommandType.Text; Ad.SelectCommand.Parameters.Clear(); Ad.SelectCommand.CommandTimeout = 600; Dt_date.Clear(); Ad.Fill(Dt_date); GonGinCheckOfDataDuplication 公告 = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString, "AMDCONFIG", "FTYPE", "FVALUE", "FVALUE", " FID = '574' "); if (string.IsNullOrEmpty(公告.傳回值.Trim())) { checkBox1.Checked = false; } else { checkBox1.Checked = true; } naviTextBox1.Text = 公告.傳回值二; naviDataGridView1.DataSource = Dt_date.DefaultView; }
public 申請資料判斷(string 階部門, string 狀態) { InitializeComponent(); _QueryConn = new SqlConnection(GonGinVariable.SqlConnectString); _QueryComm = _QueryConn.CreateCommand(); if (_QueryConn.State == ConnectionState.Closed) { _QueryConn.Open(); } naviTextBox1.Text = 階部門; 部門 = 階部門; // A預算申請、B預算追加 if (狀態 == "A") { 預算.Visible = true; 預算.Checked = true; // 預算申請超過20號,就預設下個月份 if (Convert.ToInt32(DateTime.Now.ToString("yyyyMMdd").Substring(6, 2).ToString()) > 20) { naviTextBox11.Text = DateTime.Now.AddMonths(1).ToString("yyyyMMdd").Substring(0, 6); } else { naviTextBox11.Text = DateTime.Now.ToString("yyyyMMdd").Substring(0, 6); } } else if (狀態 == "B") { 追加.Visible = true; 追加.Checked = true; naviTextBox11.Text = DateTime.Now.ToString("yyyyMMdd").Substring(0, 6); } // 如果月份已結案,自動跳至下個月 GonGinCheckOfDataDuplication 結案狀態 = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString, "BUGDA_簽核", "SCTRL", "SCTRL", "SCTRL", "BGDEP = '" + naviTextBox1.Text + "' AND BGYM = '" + naviTextBox11.Text + "'"); if (結案狀態.傳回值 == "C") { naviTextBox11.Text = DateTime.Now.AddMonths(1).ToString("yyyyMMdd").Substring(0, 6); } }
private void naviDataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e) { DataGridView Dgv = (DataGridView)sender; if (e.RowIndex < 0 || e.ColumnIndex < 0 || Dgv.Rows.Count == 0) { return; } // 輸入品項帶資料 if (Dgv.Columns[e.ColumnIndex].Name.Equals("FDWG") && !string.IsNullOrEmpty(Dgv.Rows[e.RowIndex].Cells["FDWG"].Value.ToString())) { GonGinCheckOfDataDuplication BB = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString, "BUGDA_ITEM", "FCDS", "FSIZ", "FSMT", "FDWG = '" + Dgv.Rows[e.RowIndex].Cells["FDWG"].Value.ToString() + "'"); Dgv.Rows[e.RowIndex].Cells["FCDS"].Value = BB.傳回值; Dgv.Rows[e.RowIndex].Cells["FSIZ"].Value = BB.傳回值二; //Dgv.Rows[e.RowIndex].Cells["FSMT"].Value = BB.傳回值三; } }
public Excel匯入視窗(string DEPT) { InitializeComponent(); _QueryConn = new SqlConnection(GonGinLibrary.GonGinVariable.SqlConnectString); _QueryComm = _QueryConn.CreateCommand(); if (_QueryConn.State == ConnectionState.Closed) { _QueryConn.Open(); } GonGinGetServerDate ServerDate = new GonGinGetServerDate(GonGinVariable.SqlConnectString); ServerTime = ServerDate.日期; Dt_結果.Columns.Add("NUM", typeof(int)); Dt_結果.Columns.Add("UID", typeof(string)); Dt_結果.Columns.Add("單據單號", typeof(string)); Dt_結果.Columns.Add("品項", typeof(string)); Dt_結果.Columns.Add("名稱", typeof(string)); Dt_結果.Columns.Add("規格尺寸", typeof(string)); Dt_結果.Columns.Add("數量", typeof(double)); Dt_結果.Columns.Add("單價", typeof(double)); Dt_結果.Columns.Add("金額", typeof(double)); Dt_結果.Columns.Add("備註", typeof(string)); naviTextBox3.Text = "預算"; naviTextBox2.Text = ServerTime.AddMonths(+1).ToString("yyyyMMdd").Substring(0, 6); this.DEPT = DEPT; GonGinCheckOfDataDuplication DataDuplication = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString.ToString(), "AMDDEPT", "DEPTNAME", "DEPTNAME", "DEPTNAME", "DEPTNO = '" + DEPT + "'"); if (!string.IsNullOrEmpty(DataDuplication.傳回值.Trim())) { DEPTNAME = DataDuplication.傳回值; label1.Text = "匯入部門:" + DataDuplication.傳回值 + "(" + DEPT + ")"; } 科目下拉.Columns.Add("BUGNO", typeof(String)); 科目下拉.Columns.Add("BUGNA", typeof(String)); }
static void Main(string[] args) { //---------------------------------------------------------------------------------------- // 資料環境建立及取得 //---------------------------------------------------------------------------------------- GonGinSystemEnquipment SystemEnquipment = new GonGinSystemEnquipment(); // 取得註冊檔資料(公邦準IP及資料庫名) if (!SystemEnquipment.GetLoginRegistry()) { MessageBox.Show("無法由註冊檔取得執行環境設定值,請更新登入程式或請與資訊室連絡 !!", "訊息提示", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.Exit(); } // 取得資料庫連絡字串,並傳遞給 GonGinVariable.SqlConnectString 變數 SystemEnquipment.GetSqlConnectString(System.Reflection.Assembly.GetExecutingAssembly().GetName().Name + "(" + System.Environment.MachineName + ")"); // 取得命令上的參數值 // Y Y Y Y Y Y Y 20030101 1439 if (args.Length > 0) { GonGinVariable.AuthorizatioString = args[0].ToString() + args[1].ToString() + args[2].ToString() + args[3].ToString() + args[4].ToString() + args[5].ToString() + args[6].ToString(); // YYYYYYY GonGinVariable.ApplicationUser = args[8].ToString(); // 系統使用者 if (args[7].ToString() != "20030101") { Application.Exit(); } // 取得系統操作者姓名 GonGinCheckOfDataDuplication GetUserName = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString.ToString(), "PERSON", "PNAME", "PNAME", "PNAME", "PENNO = '" + GonGinVariable.ApplicationUser + "'"); GonGinVariable.ApplicationUserName = GetUserName.傳回值; // 執行表單 Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new PUR2007F()); } else { // 2593、1819、3292 // 允許某些電腦不用帶DOS參數即可不受權限控管(在設計時方便) if (System.Environment.MachineName.CompareTo("AC1297") == 0 || System.Environment.MachineName.CompareTo("AC2155-6") == 0) { GonGinVariable.AuthorizatioString = "YYYYYYY"; GonGinVariable.ApplicationUser = "******"; GonGinVariable.ApplicationUserName = "******"; } else { GonGinVariable.AuthorizatioString = "NNNNNNN"; GonGinVariable.ApplicationUser = "******"; GonGinVariable.ApplicationUserName = "******"; } // 取得系統操作者姓名 GonGinLibrary.GonGinGetPERSON GetUserName = new GonGinGetPERSON(GonGinVariable.SqlConnectString, GonGinVariable.ApplicationUser); GonGinVariable.ApplicationUserName = GetUserName.員工姓名; GonGinVariable.ApplicationUserDeptNo = GetUserName.部門編號; GonGinVariable.ApplicationUserDeptName = GetUserName.部門名稱; // 程式設計階段時執行用 ( 設計階段時先將註記拿掉 ) Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new PUR2007F()); } }
private void 存檔_Click(object sender, EventArgs e) { if (Dt_結果.Rows.Count < 1) { MessageBox.Show("無明細無法匯入!!", "提示訊息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } GonGinCheckOfDataDuplication 資料 = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString, "BUGDA_簽核", "SCTRL", "SCTRL", "SCTRL", "BGDEP = '" + DEPT + "' AND BGYM = '" + naviTextBox2.Text + "' AND BGTYPE ='" + naviTextBox3.Text + "'"); if (資料.傳回值 != "Y") { MessageBox.Show(DEPTNAME + "的" + naviTextBox2.Text + naviTextBox3.Text + "已結案!!", "提示訊息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } _QueryComm.CommandText = " SELECT 起始流程= ISNULL(MAX(起始流程),0) FROM FLOW_STEP WHERE 流程名稱='部門預算' AND " + " 單據單號 = (SELECT 單據單號 FROM BUGDA_簽核 WHERE BGDEP = @BGDEP AND BGYM = @BGYM AND BGTYPE = @BGTYPE) AND SCTRL = 'Y' "; _QueryComm.CommandType = CommandType.Text; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@BGYM", SqlDbType.VarChar).Value = naviTextBox2.Text; _QueryComm.Parameters.Add("@BGTYPE", SqlDbType.VarChar).Value = naviTextBox3.Text; _QueryComm.Parameters.Add("@BGDEP", SqlDbType.VarChar).Value = DEPT; _QueryDr = _QueryComm.ExecuteReader(); if (_QueryDr.HasRows) { _QueryDr.Read(); if (Convert.ToDouble(_QueryDr["起始流程"].ToString()) > 0) { MessageBox.Show("主管已確認,無法匯入!!", "提示訊息", MessageBoxButtons.OK, MessageBoxIcon.Error); _QueryDr.Close(); return; } } _QueryDr.Close(); if (DialogResult.No == MessageBox.Show("確定導入" + DEPTNAME + "," + naviTextBox2.Text + "的" + naviTextBox4.Text + naviTextBox3.Text + "?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information)) { return; } string UID = ""; double 總金額 = 0; double 追加後預算 = 0; string AA = ""; try { SqlTransaction transaction; transaction = _QueryConn.BeginTransaction("Transaction"); _QueryComm.Transaction = transaction; foreach (DataRow oRow in Dt_結果.Rows) { UID = oRow["UID"].ToString(); AA = "錯誤1"; _QueryComm.CommandText = " INSERT INTO BUGDA_簽核品項 (NUM,UID,FDWG,FCDS,BUGNO,數量,金額,CRUSER,CRDATE,FSMT,FSIZ,REMARK,單據單號) " + " VALUES(@NUM, @UID, @FDWG, @FCDS, @BUGNO, @數量, @金額, @CRUSER, GETDATE(), @FSMT, @FSIZ, @REMARK, @單據單號) "; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@NUM", SqlDbType.Int).Value = Convert.ToInt32(oRow["NUM"].ToString()); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = oRow["UID"].ToString(); _QueryComm.Parameters.Add("@FDWG", SqlDbType.VarChar).Value = oRow["品項"].ToString(); _QueryComm.Parameters.Add("@FCDS", SqlDbType.VarChar).Value = oRow["名稱"].ToString(); _QueryComm.Parameters.Add("@BUGNO", SqlDbType.VarChar).Value = naviTextBox4.Text; if (string.IsNullOrEmpty(oRow["數量"].ToString().Trim())) { _QueryComm.Parameters.Add("@數量", SqlDbType.Decimal).Value = 0; } else { _QueryComm.Parameters.Add("@數量", SqlDbType.Decimal).Value = Convert.ToDecimal(oRow["數量"].ToString()); } if (string.IsNullOrEmpty(oRow["單價"].ToString().Trim())) { _QueryComm.Parameters.Add("@FSMT", SqlDbType.Decimal).Value = 0; } else { _QueryComm.Parameters.Add("@FSMT", SqlDbType.Decimal).Value = Convert.ToDecimal(oRow["單價"].ToString()); } if (string.IsNullOrEmpty(oRow["金額"].ToString().Trim())) { _QueryComm.Parameters.Add("@金額", SqlDbType.Decimal).Value = 0; } else { _QueryComm.Parameters.Add("@金額", SqlDbType.Decimal).Value = Convert.ToDecimal(oRow["金額"].ToString()); } _QueryComm.Parameters.Add("@CRUSER", SqlDbType.VarChar).Value = GonGinVariable.ApplicationUser; _QueryComm.Parameters.Add("@FSIZ", SqlDbType.VarChar).Value = oRow["規格尺寸"].ToString(); _QueryComm.Parameters.Add("@REMARK", SqlDbType.VarChar).Value = oRow["備註"].ToString(); _QueryComm.Parameters.Add("@單據單號", SqlDbType.VarChar).Value = oRow["單據單號"].ToString(); _QueryComm.ExecuteNonQuery(); } AA = "錯誤2"; _QueryComm.CommandText = " SELECT 總金額=SUM(金額) FROM BUGDA_簽核品項 A WHERE UID = @UID "; _QueryComm.CommandType = CommandType.Text; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = UID; _QueryDr = _QueryComm.ExecuteReader(); if (_QueryDr.HasRows) { _QueryDr.Read(); 總金額 = Convert.ToDouble(_QueryDr["總金額"].ToString()); } _QueryDr.Close(); if (naviTextBox3.Text == "預算") { AA = "錯誤3"; _QueryComm.CommandText = " UPDATE BUGDA_簽核明細 SET BGAMT1 = @BGAMT1,AMDUSR=@AMDUSR,AMDDAY=GETDATE() WHERE UID = @UID "; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = UID; _QueryComm.Parameters.Add("@BGAMT1", SqlDbType.Float).Value = 總金額; _QueryComm.Parameters.Add("@AMDUSR", SqlDbType.VarChar).Value = GonGinVariable.ApplicationUser; _QueryComm.ExecuteNonQuery(); } else { AA = "錯誤4"; _QueryComm.CommandText = " UPDATE BUGDA_簽核明細 SET BGBMT2 = @BGBMT2,AMDUSR=@AMDUSR,AMDDAY=GETDATE() WHERE UID = @UID "; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = UID; _QueryComm.Parameters.Add("@BGBMT2", SqlDbType.Float).Value = 總金額; _QueryComm.Parameters.Add("@AMDUSR", SqlDbType.VarChar).Value = GonGinVariable.ApplicationUser; _QueryComm.ExecuteNonQuery(); } AA = "錯誤5"; _QueryComm.CommandText = " SELECT 追加後預算 = BGAMT1 + BGBMT2 FROM BUGDA_簽核明細 A WHERE UID = @UID "; _QueryComm.CommandType = CommandType.Text; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = UID; _QueryDr = _QueryComm.ExecuteReader(); if (_QueryDr.HasRows) { _QueryDr.Read(); 追加後預算 = Convert.ToDouble(_QueryDr["追加後預算"].ToString()); } _QueryDr.Close(); AA = "錯誤6"; _QueryComm.CommandText = " UPDATE BUGDA_簽核明細 SET 追加後預算 = @追加後預算 WHERE UID = @UID "; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@UID", SqlDbType.VarChar).Value = UID; _QueryComm.Parameters.Add("@追加後預算", SqlDbType.Float).Value = 追加後預算; _QueryComm.ExecuteNonQuery(); transaction.Commit(); MessageBox.Show("匯入成功,之後請確認資料是否正確!!"); this.DialogResult = DialogResult.Yes; } catch (Exception ex) { MessageBox.Show(AA + ex.Message.ToString(), "提示訊息", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } }
private void 匯入(string xx) { int Z = 0; dt.Clear(); Dt_結果.Clear(); //string qs1 = "select * from[" + A1 + "$]"; //string qs2 = "select * from[" + A2 + "$]"; //string qs3 = "select * from[" + A3 + "$]"; cs1 = "Data Source=" + xx + ";"; cs2 = "Provider=" + (xx.Contains("xlsx") ? "Microsoft.ACE.OLEDB.12.0;" : (xx.Contains("XLSX") ? "Microsoft.ACE.OLEDB.12.0;" : "Microsoft.Jet.OLEDB.4.0;")); using (OleDbConnection cn = new OleDbConnection(cs1 + cs2 + cs3 + cs4 + cs5)) { cn.Open(); System.Data.DataTable Table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string UID = ""; string 單據單號 = ""; int NUM = 0; string AAA = ""; _QueryComm.CommandText = " SELECT UID,單據單號,NUM = ISNULL((SELECT MAX(NUM) FROM BUGDA_簽核品項 B WHERE A.UID=B.UID),0) FROM BUGDA_簽核明細 A " + " WHERE BUGNO = @BUGNO AND BGDEP = @BGDEP AND BGYM = @BGYM AND BGTYPE = @BGTYPE "; _QueryComm.CommandType = CommandType.Text; _QueryComm.Parameters.Clear(); _QueryComm.Parameters.Add("@BGYM", SqlDbType.VarChar).Value = naviTextBox2.Text; _QueryComm.Parameters.Add("@BGTYPE", SqlDbType.VarChar).Value = naviTextBox3.Text; _QueryComm.Parameters.Add("@BGDEP", SqlDbType.VarChar).Value = DEPT; _QueryComm.Parameters.Add("@BUGNO", SqlDbType.VarChar).Value = naviTextBox4.Text; _QueryDr = _QueryComm.ExecuteReader(); if (_QueryDr.HasRows) { _QueryDr.Read(); UID = _QueryDr["UID"].ToString(); 單據單號 = _QueryDr["單據單號"].ToString(); NUM = Convert.ToInt32(_QueryDr["NUM"].ToString()); } _QueryDr.Close(); if (string.IsNullOrEmpty(單據單號) || 單據單號 == null) { MessageBox.Show("還未產生" + naviTextBox2.Text + "的" + naviTextBox4.Text + "預算申請!!", "更新訊息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int i = 0; i < Table.Rows.Count; i++) { SheetName = Table.Rows[i][2].ToString().Replace("'", "").Replace("$", "").Trim().Replace("_xlnm#_FilterDatabase", ""); string qs = "select * from[" + SheetName + "$]"; //try //{ dt = new DataTable(); using (OleDbDataAdapter dr = new OleDbDataAdapter(qs, cn)) { dr.Fill(dt); } //int n; //foreach (DataRow oRow in dt.Rows) //{ // if (oRow[0].ToString() != "序" && !int.TryParse(oRow[0].ToString(), out n)) // oRow.Delete(); //} //dt.AcceptChanges(); // 更改欄位名稱 //int j = 0; foreach (DataRow oRow in dt.Select("F1='品項'")) { foreach (DataColumn OColumn in oRow.Table.Columns) { //if (oRow[OColumn].ToString() == "預估") //{ // OColumn.ColumnName = "單價" + j; // j++; //} //else //{ if (!string.IsNullOrEmpty(dt.Rows[0][OColumn].ToString().Trim())) { OColumn.ColumnName = dt.Rows[0][OColumn].ToString(); } //} } } dt.AcceptChanges(); DataTable Dt暫存 = dt.Copy(); //foreach (DataRow oRow in Dt暫存.Rows) //{ // foreach (DataColumn OColumn in dt.Columns) // { // //MessageBox.Show(Dt暫存.Rows[1][OColumn.ToString()].ToString()); // //if (Dt暫存.Rows[0][OColumn].ToString().Substring(0,1) == "F") // if (oRow[OColumn.ToString()].ToString() == "-") // oRow[OColumn.ToString()] = "1"; // } //} foreach (DataColumn OColumn in dt.Columns) { if (string.IsNullOrEmpty(Dt暫存.Rows[0][OColumn.ToString()].ToString().Trim())) { Dt暫存.Columns.Remove(OColumn.ToString()); } } Dt暫存.Rows[0].Delete(); // 移除不必要的 //Dt暫存.Columns.Remove("單"); //Dt暫存.Columns.Remove("單價1"); //Dt暫存.Rows[0].Delete(); Dt暫存.AcceptChanges(); foreach (DataRow oRow in Dt暫存.Rows) { if (!string.IsNullOrEmpty(oRow["品項"].ToString().Trim()) && !string.IsNullOrEmpty(oRow["名稱"].ToString().Trim())) { // 取BUGDA_ITEM,名稱、規格 GonGinCheckOfDataDuplication 資料 = new GonGinCheckOfDataDuplication(GonGinVariable.SqlConnectString, "BUGDA_ITEM", "FDWG", "FCDS", "FSIZ", "FDWG='" + oRow["品項"].ToString() + "'"); NUM++; Dt_結果.Rows.Add(); Dt_結果.Rows[Z]["NUM"] = NUM; Dt_結果.Rows[Z]["UID"] = UID; Dt_結果.Rows[Z]["單據單號"] = 單據單號; Dt_結果.Rows[Z]["品項"] = oRow["品項"].ToString(); if (string.IsNullOrEmpty(資料.傳回值二.Trim())) { Dt_結果.Rows[Z]["名稱"] = oRow["名稱"].ToString(); } else { Dt_結果.Rows[Z]["名稱"] = 資料.傳回值二; } if (string.IsNullOrEmpty(資料.傳回值三.Trim())) { Dt_結果.Rows[Z]["規格尺寸"] = oRow["規格尺寸"].ToString(); } else { Dt_結果.Rows[Z]["規格尺寸"] = 資料.傳回值三; } Dt_結果.Rows[Z]["數量"] = oRow["數量"].ToString(); Dt_結果.Rows[Z]["單價"] = oRow["單價"].ToString(); Dt_結果.Rows[Z]["金額"] = Convert.ToDouble(oRow["數量"].ToString()) * Convert.ToDouble(oRow["單價"].ToString()); Dt_結果.Rows[Z]["備註"] = oRow["備註"].ToString(); Z++; } } // } //catch (Exception ex) //{ // MessageBox.Show(ex.Message); //} } naviDataGridView1.DataSource = Dt_結果.DefaultView; } }