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