private void DataBindDC()
{
DataTable dt = PH_RateCardInfo.PH_DC_SelAll(AppCode.strConnDB);
ddlDC.DataTextField = "dc_name";
ddlDC.DataValueField = "dc_abbr";
ddlDC.DataSource = dt;
ddlDC.DataBind();
}
private void DataBindRateType()
{
DataTable dt = PH_RateCardInfo.PH_RateType_SelAll(AppCode.strConnDB);
ddlRateType.DataTextField = "ratetype";
ddlRateType.DataValueField = "ratetype_id";
ddlRateType.DataSource = dt;
ddlRateType.DataBind();
}
private void DataBindColl()
{
DataTable dt = PH_RateCardInfo.PH_Collection_SelAll(AppCode.strConnDB);
ddlCollectionPoint.DataTextField = "collection_point";
ddlCollectionPoint.DataValueField = "coll_id";
ddlCollectionPoint.DataSource = dt;
ddlCollectionPoint.DataBind();
}
private void DataBindData(bool isNew)
{
DataTable dt = null;
lblText.Text = PH_RateCardInfo.PH_RateCard_Count(AppCode.strConnDB);
if (!isNew && ViewState["RateCardInfo"] != null)
{
dt = (DataTable)ViewState["RateCardInfo"];
}
else
{
dt = PH_RateCardInfo.PH_RateCard_SelByVendorName(AppCode.strConnDB, txtFillVendorName.Text);
}
ViewState["RateCardInfo"] = dt;
gvData.DataSource = dt;
gvData.DataBind();
}
protected void gvData_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtEndDate = (TextBox)gvData.Rows[e.RowIndex].FindControl("txtEndDate");
string message = "";
if (txtEndDate.Text != "" && DateTime.ParseExact(txtEndDate.Text, "dd/MM/yyyy", null) > DateTime.Now)
{
message = PH_RateCardInfo.PH_RateCard_Update(AppCode.strConnDB, gvData.DataKeys[e.RowIndex].Value.ToString(), DateTime.ParseExact(txtEndDate.Text, "dd/MM/yyyy", null)) > 0 ? "Save Data Successfull" : "Save Data Not Successfull";
gvData.EditIndex = -1;
DataBindData(true);
}
else
{
message = "end date ต้องมากกว่าวันปัจจุบัน";
}
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "alertmsg", "alert('" + message + "');", true);
}
protected void btnAddSubmit_Click(object sender, EventArgs e)
{
try {
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Vendor_Code"), new DataColumn("Vendor_Name"), new DataColumn("StartDate"), new DataColumn("EndDate")
, new DataColumn("Collection_Point"), new DataColumn("DC_ABBR"), new DataColumn("Transporter_Name"), new DataColumn("Buy_Rate")
, new DataColumn("Buy_RateType"), new DataColumn("Buy_RateType_Info"), new DataColumn("Load_Type"), new DataColumn("Transporter_Desc")
, new DataColumn("Sell_Rate"), new DataColumn("Sell_RateType"), new DataColumn("Currency"), new DataColumn("Fuel_Rate_From")
, new DataColumn("Fuel_Rate_To") });
dt.Rows.Add(string.Format("{0:00000}", txtVendorCode.Text), txtVendorName.Text, DateTime.Now.ToString("MM/dd/yyyy"), null
, ddlCollectionPoint.SelectedItem, ddlDC.SelectedValue, "NSL", txtBuyRate.Text
, ddlRateType.SelectedItem, ddlRateType.SelectedItem, "", ""
, txtSellRate.Text, ddlRateType.SelectedItem, "THB", txtRateFrom.Text
, txtRateTo.Text);
string message = PH_RateCardInfo.PH_RateCard_Insert(AppCode.strConnDB, dt.Rows[dt.Rows.Count - 1]) > 0 ? "Save Data Successfull" : "Save Data Not Successfull";
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "alertmsg", "alert('" + message + "');", true);
DataBindData(true);
txtVendorCode.Text = "";
txtVendorName.Text = "";
ddlCollectionPoint.ClearSelection();
ddlDC.ClearSelection();
txtBuyRate.Text = "";
ddlRateType.ClearSelection();
ddlRateType.ClearSelection();
txtSellRate.Text = "";
ddlRateType.ClearSelection();
txtRateFrom.Text = "";
txtRateTo.Text = "";
}
catch (Exception ex)
{
PrimaryHaul_WS.PH_ExceptionManager.WriteError(ex.Message);
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "alertmsg", "alert('Save Data Not Successfull');", true);
}
}
private bool InsertData(string path)
{
DataTable dt = new DataTable();
DataTable dtColl = new DataTable();
DataTable dtRate = new DataTable();
dtRate.Columns.AddRange(new DataColumn[] { new DataColumn("RateType"), new DataColumn("StampTime") });
dtColl.Columns.AddRange(new DataColumn[] { new DataColumn("Collection_Point"), new DataColumn("StampTime") });
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Vendor_Code"), new DataColumn("Vendor_Name"), new DataColumn("StartDate"), new DataColumn("EndDate")
, new DataColumn("Collection_Point"), new DataColumn("DC_ABBR"), new DataColumn("Transporter_Name"), new DataColumn("Buy_Rate")
, new DataColumn("Buy_RateType"), new DataColumn("Buy_RateType_Info"), new DataColumn("Load_Type"), new DataColumn("Transporter_Desc")
, new DataColumn("Sell_Rate"), new DataColumn("Sell_RateType"), new DataColumn("Currency"), new DataColumn("Fuel_Rate_From")
, new DataColumn("Fuel_Rate_To") });
//Response.Write(path);
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties=Excel 8.0;";
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
{
#region Insert
int iCount = 0, iSuccess = 0, iErr = 0;
string strTempRate = "", strTempColl = "";
string sql = "select * from [Combine$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
while (drRead.Read())
{
iCount++;
if (drRead[0].ToString() != "" && drRead[1].ToString() != "")
{
try {
if (strTempColl != drRead[4].ToString())
{
PH_RateCardInfo.PH_CollectionPoint_Insert(AppCode.strConnDB, drRead[4].ToString()); //dtColl.Rows.Add(drRead[4], DateTime.Now);
}
if (strTempRate != drRead[8].ToString())
{
PH_RateCardInfo.PH_RateType_Insert(AppCode.strConnDB, drRead[8].ToString()); //dtRate.Rows.Add(drRead[8], DateTime.Now);
}
dt.Rows.Add(string.Format("{0:00000}", drRead[0].ToString()), drRead[1], drRead[2], drRead[3]
, drRead[4], drRead[5], drRead[6], drRead[7]
, drRead[8], drRead[9], drRead[10], drRead[11]
, drRead[12], drRead[13], drRead[14], drRead[16]
, drRead[17]);
if (PH_RateCardInfo.PH_RateCard_Insert(AppCode.strConnDB, dt.Rows[dt.Rows.Count - 1]) > 0)
{
iSuccess++;
}
else
{
iErr++;
}
strTempRate = drRead[8].ToString();
strTempColl = drRead[4].ToString();
}
catch (Exception ex)
{
iErr++;
PrimaryHaul_WS.PH_ExceptionManager.WriteError(ex.Message);
}
}
}
lblErr.Text = "count=" + iCount.ToString() + " success=" + iSuccess + " error=" + iErr;
//gvData.DataSource = dt;
//gvData.DataBind();
DataBindData(true);
conn.Close();
return(true);
#endregion
}
catch (Exception ex)
{
conn.Close();
PrimaryHaul_WS.PH_ExceptionManager.WriteError(ex.Message);
return(false);
}
}
