private void InsertData(string path)
{
bool isErr = false;
try
{
PHDS_HaulierUpload.TransportationDataTable dtHaulierUp = new PHDS_HaulierUpload.TransportationDataTable();
/*DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Haulier_Abbr"), new DataColumn("Po_No"), new DataColumn("Delivery_Ref"), new DataColumn("Delivery_Date")
,new DataColumn("Vendor_Code"), new DataColumn("Vendor_Name"), new DataColumn("Collection_Point"), new DataColumn("Delivery_Location")
,new DataColumn("RateType"), new DataColumn("No_Of_Qty"), new DataColumn("Rate_Per_Uint"), new DataColumn("Currency")
,new DataColumn("Addition_Cost"), new DataColumn("Addition_Cost_Reason"), new DataColumn("Total_Cost"), new DataColumn("Year_Week_No")
,new DataColumn("Remark1"), new DataColumn("Remark2"), new DataColumn("Fuel_Rate") ,new DataColumn("Trans_Type")
});*/
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties='Excel 8.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text;'";
string connectionStringXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + " ; Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text\"";
connectionString = (path.ToLower().IndexOf("xlsx") > 0) ? connectionStringXLSX : connectionString;
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Open) conn.Close();
conn.Open();
try
{
ViewState["HaulierUploadInsert"] = null;
int strUserId = int.Parse(Request["id"]);
string strSheet = "Normal";
string fileName = path.Split('\\').Length > 0 ? path.Split('\\')[path.Split('\\').Length - 1] : "";
//DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
#region Insert
for (int index = 0; index < 2; index++)
//foreach (DataRow drSheet in dtSheet.Rows)
{
try
{
strSheet = (index == 0) ? "Normal" : "Dummy";
string sql = "select * from [" + strSheet + "$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
PHDS_HaulierUpload.TransportationRow dr = null;
while (drRead.Read())
{
if (!string.IsNullOrEmpty(drRead[0].ToString().Trim()))
{
#region Insert Row
try
{
dr = dtHaulierUp.NewTransportationRow();
string s = drRead[4].ToString().Trim() != "" ? int.Parse(drRead[4].ToString()).ToString("00000") : "00000";//String.Format("{0:00000}", );
dr.PO_No = drRead[1].ToString().Trim();
dr.Haulier_Abbr = drRead[0].ToString().Trim();
dr.UserID = strUserId;
ViewState["HaulierAbbr"] = drRead[0].ToString().Trim();
try
{
dr.Delivery_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "M/d/yyyy", null).ToString("dd/MM/yyyy"); //drRead[3].ToString().Trim().Split(' ')[0];//
}
catch
{
dr.Delivery_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "d/M/yyyy", null).ToString("dd/MM/yyyy"); //drRead[3].ToString().Trim().Split(' ')[0];//
}
dr.Delivery_Ref = drRead[2].ToString().Trim();
dr.Vendor_Code = s;
dr.Vendor_Name = drRead[5].ToString().Trim();
dr.Collection_Point = drRead[6].ToString().Trim();
dr.Delivery_Location = drRead[7].ToString().Trim();
dr.DC_No = 0;
dr.RateType = drRead[8].ToString().Trim();
dr.Currency = drRead[11].ToString().Trim();
dr.Additional_Cost_Reason = drRead[13].ToString();//.Trim() == "" ? 0 : decimal.Parse(drRead[13].ToString().Trim());
dr.Year_Week_OnFile = drRead[15].ToString();
dr.Year_Week_Upload = txtWeek.Text;
dr.Remark1 = drRead[16].ToString().Trim();
dr.Remark2 = drRead[17].ToString().Trim();
dr.FileName = fileName;
dr.Calc_Date = DateTime.Now;
dr.RC_RateCardID = 0;
dr.RC_Sell_Rate = 0;
dr.Sell_Fuel_Rate = 0;
dr.Total_Cost_Charging = 0;
dr.StampTime = DateTime.Now;
dr.No_Of_Qty = drRead[9].ToString().Trim() == "" ? 0 : int.Parse(drRead[9].ToString().Trim());
dr.Rate_Per_Unit = drRead[10].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[10].ToString().Trim());
dr.Additional_Cost = drRead[12].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[12].ToString().Trim());
dr.Total_Cost = drRead[14].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[14].ToString().Trim());
dr.Fuel_Rate = drRead[18].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[18].ToString().Trim());
dr.Trans_Type = IsErrCaseII(dr); // case II , III ,IV
if (IsErrYearWeek(dr))
{
isErr = true;
dr.status = "errYearWeek";
}
}
catch (Exception ex)
{
dr.status = "err";
dr.Remark2 = ex.Message;
PH_ExceptionManager.WriteError("prepare data >> err message : " + ex.Message);
}
dtHaulierUp.Rows.Add(dr);
PH_HaulierUpload.PH_HaulierUp_InsertTMP(AppCode.strConnDB, dr);
#endregion
}
}
}
catch (Exception ex) { lblErr.Text += ex.Message; ; PH_ExceptionManager.WriteError("Verlify Data >>" + " Row Index : " + index.ToString() + " err message : " + ex.Message); }
}
PH_HaulierUpload.PH_HaulierUp_Verify(AppCode.strConnDB, txtWeek.Text, (string)ViewState["HaulierAbbr"], Request["id"]);
ViewState["HaulierUploadInsert"] = dtHaulierUp;
GetHaulierData(true);
btnClear.Enabled = true;
#endregion
}
catch (Exception ex)
{
lblErr.Text = ex.Message;
PH_ExceptionManager.WriteError("Verlify Data >>" + ex.Message);
}
finally
{
// lblErr.Text = "";
}
}
catch (Exception ex)
{
throw new Exception("InsertData>>" + ex.Message);
}
}
private void InsertData(string path)
{
bool isErr = false;
try
{
PHDS_HaulierUpload.TransportationDataTable dtHaulierUp = new PHDS_HaulierUpload.TransportationDataTable();
/*DataTable dt = new DataTable();
* dt.Columns.AddRange(new DataColumn[] { new DataColumn("Haulier_Abbr"), new DataColumn("Po_No"), new DataColumn("Delivery_Ref"), new DataColumn("Delivery_Date")
* ,new DataColumn("Vendor_Code"), new DataColumn("Vendor_Name"), new DataColumn("Collection_Point"), new DataColumn("Delivery_Location")
* ,new DataColumn("RateType"), new DataColumn("No_Of_Qty"), new DataColumn("Rate_Per_Uint"), new DataColumn("Currency")
* ,new DataColumn("Addition_Cost"), new DataColumn("Addition_Cost_Reason"), new DataColumn("Total_Cost"), new DataColumn("Year_Week_No")
* ,new DataColumn("Remark1"), new DataColumn("Remark2"), new DataColumn("Fuel_Rate") ,new DataColumn("Trans_Type")
* });*/
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties='Excel 8.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text;'";
string connectionStringXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + " ; Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text\"";
connectionString = (path.ToLower().IndexOf("xlsx") > 0) ? connectionStringXLSX : connectionString;
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
try
{
ViewState["HaulierUploadInsert"] = null;
int strUserId = int.Parse(Request["id"]);
string strSheet = "Normal";
string fileName = path.Split('\\').Length > 0 ? path.Split('\\')[path.Split('\\').Length - 1] : "";
//DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
#region Insert
for (int index = 0; index < 2; index++)
//foreach (DataRow drSheet in dtSheet.Rows)
{
try
{
strSheet = (index == 0) ? "Normal" : "Dummy";
string sql = "select * from [" + strSheet + "$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
PHDS_HaulierUpload.TransportationRow dr = null;
while (drRead.Read())
{
if (!string.IsNullOrEmpty(drRead[0].ToString().Trim()))
{
#region Insert Row
try
{
dr = dtHaulierUp.NewTransportationRow();
string s = drRead[4].ToString().Trim() != "" ? int.Parse(drRead[4].ToString()).ToString("00000") : "00000";//String.Format("{0:00000}", );
dr.PO_No = drRead[1].ToString().Trim();
dr.Haulier_Abbr = drRead[0].ToString().Trim();
dr.UserID = strUserId;
ViewState["HaulierAbbr"] = drRead[0].ToString().Trim();
try
{
dr.Delivery_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "M/d/yyyy", null).ToString("dd/MM/yyyy"); //drRead[3].ToString().Trim().Split(' ')[0];//
}
catch
{
dr.Delivery_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "d/M/yyyy", null).ToString("dd/MM/yyyy"); //drRead[3].ToString().Trim().Split(' ')[0];//
}
dr.Delivery_Ref = drRead[2].ToString().Trim();
dr.Vendor_Code = s;
dr.Vendor_Name = drRead[5].ToString().Trim();
dr.Collection_Point = drRead[6].ToString().Trim();
dr.Delivery_Location = drRead[7].ToString().Trim();
dr.DC_No = 0;
dr.RateType = drRead[8].ToString().Trim();
dr.Currency = drRead[11].ToString().Trim();
dr.Additional_Cost_Reason = drRead[13].ToString();//.Trim() == "" ? 0 : decimal.Parse(drRead[13].ToString().Trim());
dr.Year_Week_OnFile = drRead[15].ToString();
dr.Year_Week_Upload = txtWeek.Text;
dr.Remark1 = drRead[16].ToString().Trim();
dr.Remark2 = drRead[17].ToString().Trim();
dr.FileName = fileName;
dr.Calc_Date = DateTime.Now;
dr.RC_RateCardID = 0;
dr.RC_Sell_Rate = 0;
dr.Sell_Fuel_Rate = 0;
dr.Total_Cost_Charging = 0;
dr.StampTime = DateTime.Now;
dr.No_Of_Qty = drRead[9].ToString().Trim() == "" ? 0 : int.Parse(drRead[9].ToString().Trim());
dr.Rate_Per_Unit = drRead[10].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[10].ToString().Trim());
dr.Additional_Cost = drRead[12].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[12].ToString().Trim());
dr.Total_Cost = drRead[14].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[14].ToString().Trim());
dr.Fuel_Rate = drRead[18].ToString().Trim() == "" ? 0 : decimal.Parse(drRead[18].ToString().Trim());
dr.Trans_Type = IsErrCaseII(dr); // case II , III ,IV
if (IsErrYearWeek(dr))
{
isErr = true;
dr.status = "errYearWeek";
}
}
catch (Exception ex)
{
dr.status = "err";
dr.Remark2 = ex.Message;
PH_ExceptionManager.WriteError("prepare data >> err message : " + ex.Message);
}
dtHaulierUp.Rows.Add(dr);
PH_HaulierUpload.PH_HaulierUp_InsertTMP(AppCode.strConnDB, dr);
#endregion
}
}
}
catch (Exception ex) { lblErr.Text += ex.Message;; PH_ExceptionManager.WriteError("Verlify Data >>" + " Row Index : " + index.ToString() + " err message : " + ex.Message); }
}
PH_HaulierUpload.PH_HaulierUp_Verify(AppCode.strConnDB, txtWeek.Text, (string)ViewState["HaulierAbbr"], Request["id"]);
ViewState["HaulierUploadInsert"] = dtHaulierUp;
GetHaulierData(true);
btnClear.Enabled = true;
#endregion
}
catch (Exception ex)
{
lblErr.Text = ex.Message;
PH_ExceptionManager.WriteError("Verlify Data >>" + ex.Message);
}
finally
{
// lblErr.Text = "";
}
}
catch (Exception ex)
{
throw new Exception("InsertData>>" + ex.Message);
}
}
