private void 存檔_Click(object sender, EventArgs e)
{
this.Validate();
SqlTransaction transaction;
transaction = _QueryConn.BeginTransaction("Transaction");
_QueryComm.Transaction = transaction;
_QueryComm.CommandText = " DELETE BUGDA_固定科目 WHERE BGYM=@BGYM AND KIND='S' ";
_QueryComm.CommandType = CommandType.Text;
_QueryComm.Parameters.Clear();
_QueryComm.Parameters.Add("@BGYM", SqlDbType.VarChar).Value = BGYM;
_QueryComm.ExecuteNonQuery();
//string DEPT = "";
foreach (DataRow oRow in Ds.Tables[1].Rows)
{
foreach (DataColumn OColumn in oRow.Table.Columns)
{
if (OColumn.ToString() != "BUGNO" && OColumn.ToString() != "BUGNA")
{
string[] sArray = OColumn.ToString().Split('_');
if (!string.IsNullOrEmpty(oRow[OColumn.ToString()].ToString().Trim()) && Convert.ToInt32(oRow[OColumn.ToString()].ToString()) > 0)
{
_QueryComm.CommandText = " INSERT INTO BUGDA_固定科目 (BGDEP,BGYM,BGNO,BGAMT1,CRUSER,CRDATE,KIND) " +
" VALUES (@BGDEP,@BGYM,@BGNO,@BGAMT1,@CRUSER,GETDATE(),'S') ";
_QueryComm.CommandType = CommandType.Text;
_QueryComm.Parameters.Clear();
_QueryComm.Parameters.Add("@BGDEP", SqlDbType.VarChar).Value = sArray[1].ToString();
_QueryComm.Parameters.Add("@BGYM", SqlDbType.VarChar).Value = BGYM;
_QueryComm.Parameters.Add("@BGNO", SqlDbType.VarChar).Value = oRow["BUGNO"].ToString();
_QueryComm.Parameters.Add("@BGAMT1", SqlDbType.Decimal).Value = Convert.ToDecimal(oRow[OColumn.ToString()].ToString());
_QueryComm.Parameters.Add("@CRUSER", SqlDbType.VarChar).Value = GonGinVariable.ApplicationUser;
_QueryComm.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
// 計算總合
int totle = 0;
foreach (DataRow oRow in Ds.Tables[1].Rows)
{
if (oRow["BUGNO"].ToString() != "總合")
{
totle += Convert.ToInt32(oRow[2].ToString());
}
}
foreach (DataRow oRow in Ds.Tables[1].Rows)
{
if (oRow["BUGNO"].ToString() == "總合")
{
oRow[2] = totle;
}
}
toolStripButton1.Visible = true;
存檔.Visible = false;
naviDataGridView1.ReadOnly = true;
MessageBox.Show("存檔成功！！");
comboBox1.Enabled = true;
}
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;
}
}