private void ImportExcelData(string strFilePath, bool bJudge, SqlConnection oneConn, SqlCommand oneComm) { string strConn = null; if (bJudge) { strConn = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } else { strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } OleDbConnection myConn = new OleDbConnection(strConn); myConn.Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConn); DataTable myTable = new DataTable(); myAdapter.Fill(myTable); myAdapter.Dispose(); myConn.Dispose(); if (myTable.Rows.Count == 0) { MessageBox.Show("There is no data to upload.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information); myAdapter.Dispose(); myTable.Clear(); myTable.Dispose(); return; } oneComm.CommandText = @"SELECT * FROM B_MultiUser"; string strUserName = Convert.ToString(oneComm.ExecuteScalar()); if (!String.IsNullOrEmpty(strUserName)) { if (String.Compare(strUserName.Trim().ToUpper(), funcLib.getCurrentUserName().Trim().ToUpper()) != 0) { MessageBox.Show(strUserName + " is handling RM Balance/Drools Balance data. Please wait for his/her to finish the process.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); oneComm.Dispose(); oneConn.Dispose(); return; } } else { oneComm.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName().ToUpper(); oneComm.CommandText = @"INSERT INTO B_MultiUser([UserName]) VALUES(@UserName)"; oneComm.ExecuteNonQuery(); oneComm.Parameters.RemoveAt("@UserName"); } oneComm.CommandText = @"SELECT [Item No], [Lot No], [RM EHB], [BGD No], [Customs Balance], [Available RM Balance] FROM C_RMBalance"; SqlDataAdapter oneAdapter = new SqlDataAdapter(); oneAdapter.SelectCommand = oneComm; DataTable dtRmBal = new DataTable(); oneAdapter.Fill(dtRmBal); oneAdapter.Dispose(); for (int i = 0; i < myTable.Rows.Count; i++) { string strAdjAvailableQty = myTable.Rows[i]["ADJ Available Qty"].ToString().Trim(); string strAdjCustomsQty = myTable.Rows[i]["ADJ Customs Qty"].ToString().Trim(); decimal dAdjAvailableQty = 0.0M, dAdjCustomsQty = 0.0M; if (!String.IsNullOrEmpty(strAdjAvailableQty)) { dAdjAvailableQty = Math.Round(Convert.ToDecimal(strAdjAvailableQty), 6); } if (!String.IsNullOrEmpty(strAdjCustomsQty)) { dAdjCustomsQty = Math.Round(Convert.ToDecimal(strAdjCustomsQty), 6); } if (dAdjAvailableQty != 0.0M || dAdjCustomsQty != 0.0M) { string strItemNo = myTable.Rows[i]["Item No"].ToString().Trim().ToUpper(); string strLotNo = myTable.Rows[i]["Lot No"].ToString().Trim().ToUpper(); string strRmEhb = myTable.Rows[i]["RM EHB"].ToString().Trim().ToUpper(); string strBgdNo = myTable.Rows[i]["BGD No"].ToString().Trim().ToUpper(); DataRow[] dr = dtRmBal.Select("[Item No]='" + strItemNo + "' AND [Lot No]='" + strLotNo + "' AND [RM EHB]='" + strRmEhb + "' AND [BGD No]='" + strBgdNo + "'"); if (dr.Length > 0) { string strCustomsBal = sqlLib.doubleFormat(Double.Parse(dr[0]["Customs Balance"].ToString().Trim())); string strAvailRMBal = sqlLib.doubleFormat(Double.Parse(dr[0]["Available RM Balance"].ToString().Trim())); decimal dCustomsBalance = Convert.ToDecimal(strCustomsBal) + dAdjCustomsQty; decimal dAvailableRMBalance = Convert.ToDecimal(strAvailRMBal) + dAdjAvailableQty; oneComm.Parameters.Add("@CustomsBalance", SqlDbType.Decimal).Value = dCustomsBalance; oneComm.Parameters.Add("@AvailableRMBalance", SqlDbType.Decimal).Value = dAvailableRMBalance; oneComm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = strItemNo; oneComm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = strLotNo; oneComm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = strRmEhb; oneComm.Parameters.Add("@BGDNo", SqlDbType.NVarChar).Value = strBgdNo; oneComm.CommandText = @"UPDATE C_RMBalance SET [Customs Balance] = @CustomsBalance, [Available RM Balance] = @AvailableRMBalance " + "WHERE [Item No] = @ItemNo AND [Lot No] = @LotNo AND [RM EHB] = @RMEHB AND [BGD No] = @BGDNo"; oneComm.ExecuteNonQuery(); oneComm.Parameters.Clear(); oneComm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = strItemNo; oneComm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = strLotNo; oneComm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = strRmEhb; oneComm.Parameters.Add("@BGDNo", SqlDbType.NVarChar).Value = strBgdNo; oneComm.Parameters.Add("@AdjAvailableQty", SqlDbType.Decimal).Value = dAdjAvailableQty; oneComm.Parameters.Add("@AdjCustomsQty", SqlDbType.Decimal).Value = dAdjCustomsQty; oneComm.Parameters.Add("@Creater", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName(); oneComm.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy")); oneComm.CommandText = @"INSERT INTO L_RMBalance_Adjustment([Item No], [Lot No], [RM EHB], [BGD No], [ADJ Available Qty], [ADJ Customs Qty], [Creater], " + "[Created Date]) VALUES(@ItemNo, @LotNo, @RMEHB, @BGDNo, @AdjAvailableQty, @AdjCustomsQty, @Creater, @CreatedDate)"; oneComm.ExecuteNonQuery(); oneComm.Parameters.Clear(); } } } dtRmBal.Dispose(); myTable.Dispose(); oneComm.CommandText = @"DELETE FROM B_MultiUser"; oneComm.ExecuteNonQuery(); }
private void ImportExcelData(string strFilePath, bool bJudge) { string strConn; if (bJudge) { strConn = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } else { strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } OleDbConnection myConn = new OleDbConnection(strConn); myConn.Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$] WHERE [GongDan No] IS NOT NULL AND [GongDan No] <> ''", myConn); DataTable dtFileGD = new DataTable(); myAdapter.Fill(dtFileGD); myAdapter = new OleDbDataAdapter("SELECT DISTINCT [GongDan No] FROM [Sheet1$]", myConn); DataTable dtFileGDN = new DataTable(); myAdapter.Fill(dtFileGDN); myAdapter.Dispose(); myConn.Dispose(); if (dtFileGD.Rows.Count == 0) { MessageBox.Show("There is no data to upload.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); dtFileGD.Dispose(); dtFileGDN.Dispose(); return; } if (MessageBox.Show("Are you sure to upload the file to batch update existing data?", "Prompt", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { dtFileGD.Dispose(); dtFileGDN.Dispose(); return; } SqlConnection Conn = new SqlConnection(SqlLib.StrSqlConnection); if (Conn.State == ConnectionState.Closed) { Conn.Open(); } SqlCommand Comm = new SqlCommand(); Comm.Connection = Conn; string strGongDan = null; foreach (DataRow dr in dtFileGDN.Rows) { strGongDan += "'" + dr[0].ToString().Trim() + "',"; } strGongDan = strGongDan.Remove(strGongDan.Length - 1); Comm.CommandText = "DELETE FROM M_DailyGongDan WHERE [GongDan No] IN (" + strGongDan + ")"; Comm.ExecuteNonQuery(); dtFileGDN.Dispose(); SqlLib sqlLib = new SqlLib(); foreach (DataRow dr in dtFileGD.Rows) { Comm.Parameters.Add("@ActualStartDate", SqlDbType.NVarChar).Value = dr["Actual Start Date"].ToString().Trim(); Comm.Parameters.Add("@ActualEndDate", SqlDbType.NVarChar).Value = dr["Actual End Date"].ToString().Trim(); Comm.Parameters.Add("@BatchPath", SqlDbType.NVarChar).Value = dr["Batch Path"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BatchNo", SqlDbType.NVarChar).Value = dr["Batch No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BomInCustoms", SqlDbType.NVarChar).Value = dr["BOM In Customs"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = dr["GongDan No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@FgNo", SqlDbType.NVarChar).Value = dr["FG No"].ToString().Trim(); Comm.Parameters.Add("@FgDescription", SqlDbType.NVarChar).Value = dr["FG Description"].ToString().Trim(); Comm.Parameters.Add("@LineNo", SqlDbType.Int).Value = Convert.ToInt32(dr["Line No"].ToString().Trim()); Comm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = dr["Item No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@ItemDescription", SqlDbType.NVarChar).Value = dr["Item Description"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = dr["Lot No"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@InventoryType", SqlDbType.NVarChar).Value = dr["Inventory Type"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@RMCategory", SqlDbType.NVarChar).Value = dr["RM Category"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = dr["RM EHB"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@BgdNo", SqlDbType.NVarChar).Value = dr["BGD No"].ToString().Trim(); Comm.Parameters.Add("@IeType", SqlDbType.NVarChar).Value = dr["IE Type"].ToString().Trim(); Comm.Parameters.Add("@OrderNo", SqlDbType.NVarChar).Value = dr["Order No"].ToString().Trim().ToUpper(); string strGongDanQty = dr["GongDan Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strGongDanQty)) { Comm.Parameters.Add("@GongDanQty", SqlDbType.Int).Value = 0; } else { Comm.Parameters.Add("@GongDanQty", SqlDbType.Int).Value = Convert.ToInt32(strGongDanQty); } string strOrderPrice = dr["Order Price"].ToString().Trim(); if (String.IsNullOrEmpty(strOrderPrice)) { Comm.Parameters.Add("@OrderPrice", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@OrderPrice", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(strOrderPrice), 2); } Comm.Parameters.Add("@OrderCurr", SqlDbType.NVarChar).Value = dr["Order Currency"].ToString().Trim().ToUpper(); string strTotalRmQty = dr["Total RM Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strTotalRmQty)) { Comm.Parameters.Add("@TotalRmQty", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@TotalRmQty", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strTotalRmQty))), 6); } string strTotalRmCost = dr["Total RM Cost(USD)"].ToString().Trim(); if (String.IsNullOrEmpty(strTotalRmCost)) { Comm.Parameters.Add("@TotalRmCost", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@TotalRmCost", SqlDbType.Decimal).Value = Convert.ToDecimal(strTotalRmCost); } string strRmUsedQty = dr["RM Used Qty"].ToString().Trim(); if (String.IsNullOrEmpty(strRmUsedQty)) { Comm.Parameters.Add("@RmUsedQty", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@RmUsedQty", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strRmUsedQty))), 6); } Comm.Parameters.Add("@RMCurr", SqlDbType.NVarChar).Value = dr["RM Currency"].ToString().Trim().ToUpper(); string strRmPrice = dr["RM Price"].ToString().Trim(); if (String.IsNullOrEmpty(strRmPrice)) { Comm.Parameters.Add("@RMPrice", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@RMPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(strRmPrice); } string strConsmpt = dr["Consumption"].ToString().Trim(); if (String.IsNullOrEmpty(strConsmpt)) { Comm.Parameters.Add("@Consumption", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@Consumption", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strConsmpt))), 6); } string strDroolsQ = dr["Drools Quota"].ToString().Trim(); if (String.IsNullOrEmpty(strDroolsQ)) { Comm.Parameters.Add("@DroolsQuota", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@DroolsQuota", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strDroolsQ))), 6); } string strDroolsR = dr["Drools Rate"].ToString().Trim(); if (String.IsNullOrEmpty(strDroolsR)) { Comm.Parameters.Add("@DroolsRate", SqlDbType.Decimal).Value = 0.0M; } else { Comm.Parameters.Add("@DroolsRate", SqlDbType.Decimal).Value = Math.Round(Convert.ToDecimal(sqlLib.doubleFormat(double.Parse(strDroolsR))), 6); } Comm.Parameters.Add("@ChnName", SqlDbType.NVarChar).Value = dr["CHN Name"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@DroolsEHB", SqlDbType.NVarChar).Value = dr["Drools EHB"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@Destination", SqlDbType.NVarChar).Value = dr["Destination"].ToString().Trim().ToUpper(); Comm.Parameters.Add("@Creater", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName(); Comm.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy")); Comm.CommandText = "INSERT INTO M_DailyGongDan([Actual Start Date], [Actual End Date], [Batch Path], [Batch No], [BOM In Customs], [GongDan No], " + "[FG No], [FG Description], [Line No], [Item No], [Item Description], [Lot No], [Inventory Type], [RM Category], [RM EHB], " + "[BGD No], [IE Type], [Order No], [GongDan Qty], [Order Price], [Order Currency], [Total RM Qty], [Total RM Cost(USD)], " + "[RM Used Qty], [RM Currency], [RM Price], [Consumption], [Drools Quota], [Drools Rate], [CHN Name], [Drools EHB], [Destination], " + "[Creater], [Created Date]) VALUES(@ActualStartDate, @ActualEndDate, @BatchPath, @BatchNo, @BomInCustoms, @GongDanNo, @FgNo, " + "@FgDescription, @LineNo, @ItemNo, @ItemDescription, @LotNo, @InventoryType, @RMCategory, @RMEHB, @BgdNo, @IeType, @OrderNo, " + "@GongDanQty, @OrderPrice, @OrderCurr, @TotalRmQty, @TotalRmCost, @RmUsedQty, @RMCurr, @RMPrice, @Consumption, @DroolsQuota, " + "@DroolsRate, @ChnName, @DroolsEHB, @Destination, @Creater, @CreatedDate)"; Comm.ExecuteNonQuery(); Comm.Parameters.Clear(); } sqlLib.Dispose(0); dtFileGD.Dispose(); Comm.Dispose(); if (Conn.State == ConnectionState.Open) { Conn.Close(); Conn.Dispose(); } MessageBox.Show("Upload successfully.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information); }