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