private void Import(string txtFileName)
{
if (txtFileName != string.Empty)
{
try
{
String name = "Sheet1"; // default Sheet1
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
txtFileName +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
System.Data.DataTable data = new System.Data.DataTable();
sda.Fill(data);
//// grd_order.DataSource = data;
//Hashtable httruncate = new Hashtable();
//DataTable dttruncate = new System.Data.DataTable();
//httruncate.Add("@Trans", "TRUNCATE");
//dttruncate = dataaccess.ExecuteSP("Sp_Temp_Order", httruncate);
grd_OrderEntry.Rows.Clear();
for (int i = 0; i < data.Rows.Count; i++)
{
if (data.Rows[i]["Date"].ToString() != "" &&
data.Rows[i]["Time"].ToString() != "" &&
data.Rows[i]["Order Number"].ToString() != "" &&
data.Rows[i]["Client"].ToString() != "" &&
data.Rows[i]["Sub process"].ToString() != "" &&
data.Rows[i]["Order Type"].ToString() != "" &&
data.Rows[i]["Property Address"].ToString() != "" &&
data.Rows[i]["State"].ToString() != "" &&
data.Rows[i]["County"].ToString() != "" &&
data.Rows[i]["Borrower_First"].ToString() != "" &&
data.Rows[i]["Borrower_Last"].ToString() != "" &&
data.Rows[i]["Task"].ToString() != ""
)
{
Date = data.Rows[i]["Date"].ToString();
Orderno = data.Rows[i]["Order Number"].ToString();
Client = data.Rows[i]["Client"].ToString();
Subprocess = data.Rows[i]["Sub process"].ToString();
Ordertype = data.Rows[i]["Order Type"].ToString();
State = data.Rows[i]["State"].ToString();
County = data.Rows[i]["County"].ToString();
Task = data.Rows[i]["Task"].ToString();
Borrower_Firstname = data.Rows[i]["Borrower_First"].ToString();
Borrower_Lastname = data.Rows[i]["Borrower_Last"].ToString();
Address = data.Rows[i]["Property Address"].ToString();
value = 0;
grd_OrderEntry.Rows.Add();
grd_OrderEntry.Rows[i].Cells[0].Value = i + 1;
grd_OrderEntry.Rows[i].Cells[1].Value = data.Rows[i]["Order Number"].ToString();
grd_OrderEntry.Rows[i].Cells[2].Value = data.Rows[i]["APN"].ToString();
grd_OrderEntry.Rows[i].Cells[3].Value = data.Rows[i]["Order Type"].ToString();
grd_OrderEntry.Rows[i].Cells[4].Value = data.Rows[i]["Client"].ToString();
grd_OrderEntry.Rows[i].Cells[5].Value = data.Rows[i]["Sub process"].ToString();
grd_OrderEntry.Rows[i].Cells[6].Value = data.Rows[i]["Client Order Ref"].ToString();
grd_OrderEntry.Rows[i].Cells[7].Value = data.Rows[i]["Task"].ToString();
grd_OrderEntry.Rows[i].Cells[8].Value = data.Rows[i]["Borrower_First"].ToString();
grd_OrderEntry.Rows[i].Cells[9].Value = data.Rows[i]["Borrower_Last"].ToString();
grd_OrderEntry.Rows[i].Cells[10].Value = data.Rows[i]["Property Address"].ToString();
grd_OrderEntry.Rows[i].Cells[11].Value = data.Rows[i]["County"].ToString();
grd_OrderEntry.Rows[i].Cells[12].Value = data.Rows[i]["State"].ToString();
grd_OrderEntry.Rows[i].Cells[13].Value = data.Rows[i]["Date"].ToString();
grd_OrderEntry.Rows[i].Cells[14].Value = data.Rows[i]["Time"].ToString();
grd_OrderEntry.Rows[i].Cells[15].Value = data.Rows[i]["Comments"].ToString();
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.White;
//Change Date into DateTime
DateTime Received_date;
string date_received;
if (Date != "")
{
try
{
Received_date = Convert.ToDateTime(Date.ToString());
date_received = Received_date.ToString("MM/dd/yyyy");
}
catch
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[13].Style.ForeColor = Color.White;
}
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[13].Style.ForeColor = Color.White;
}
//Duplication of Records
for (int j = 0; j < i; j++)
{
string Order_no = data.Rows[j]["Order Number"].ToString();
if (Orderno == Order_no)
{
value = 1;
break;
}
else
{
value = 0;
}
}
if (value == 1)
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[1].Style.ForeColor = Color.White;
}
//OrderType Taken and Add
Hashtable htType = new Hashtable();
DataTable dtType = new System.Data.DataTable();
htType.Add("@Trans", "GETType");
htType.Add("@Order_Type", Ordertype);
dtType = dataaccess.ExecuteSP("Sp_Order_Get_Details", htType);
if (dtType.Rows.Count > 0)
{
Ordertypeid = int.Parse(dtType.Rows[0]["Order_Type_ID"].ToString());
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[3].Style.ForeColor = Color.White;
}
//Get State
Hashtable htbarowerstate = new Hashtable();
DataTable dtbarrowerstate = new System.Data.DataTable();
htbarowerstate.Add("@Trans", "GETSTATE_BY_ABR");
htbarowerstate.Add("@state_name", State);
dtbarrowerstate = dataaccess.ExecuteSP("Sp_Order_Get_Details", htbarowerstate);
if (dtbarrowerstate.Rows.Count > 0)
{
Stateid = int.Parse(dtbarrowerstate.Rows[0]["State_ID"].ToString());
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[11].Style.ForeColor = Color.White;
}
//get County
Hashtable htBarcounty = new Hashtable();
DataTable dtbarcounty = new System.Data.DataTable();
htBarcounty.Add("@Trans", "GET_COUNTY");
htBarcounty.Add("@state_Id", Stateid);
htBarcounty.Add("@County_Name", County);
dtbarcounty = dataaccess.ExecuteSP("Sp_Order_Get_Details", htBarcounty);
if (dtbarcounty.Rows.Count > 0)
{
Countyid = int.Parse(dtbarcounty.Rows[0]["County_ID"].ToString());
}
else
{
Hashtable htcounty = new Hashtable();
DataTable dtcounty = new System.Data.DataTable();
htcounty.Add("@Trans", "ADDCOUNTY");
htcounty.Add("@state_Id", Stateid);
htcounty.Add("@County", County);
dtcounty = dataaccess.ExecuteSP("Sp_Order_Get_Details", htcounty);
Hashtable htBarcounty1 = new Hashtable();
DataTable dtbarcounty1 = new System.Data.DataTable();
htBarcounty1.Add("@Trans", "GETCOUNTY");
htBarcounty1.Add("@State", Stateid);
htBarcounty1.Add("@County_Name", County);
dtbarcounty1 = dataaccess.ExecuteSP("Sp_Order_Get_Details", htBarcounty1);
Countyid = int.Parse(dtbarcounty1.Rows[0]["County_ID"].ToString());
}
//get_Order_Status
Hashtable htorderstatus = new Hashtable();
DataTable dtorderstatus = new System.Data.DataTable();
htorderstatus.Add("@Trans", "GET_ORDER_STATUS");
htorderstatus.Add("@Order_Status", Task);
dtorderstatus = dataaccess.ExecuteSP("Sp_Order_Get_Details", htorderstatus);
if (dtorderstatus.Rows.Count > 0)
{
Taskid = int.Parse(dtorderstatus.Rows[0]["Order_Status_ID"].ToString());
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[7].Style.ForeColor = Color.White;
}
//get_Client
Clientid = 0;
Hashtable ht_Client = new Hashtable();
DataTable dt_Client = new System.Data.DataTable();
ht_Client.Add("@Trans", "SELECT_Client_ID_Name");
ht_Client.Add("@Client_Name", Client);
dt_Client = dataaccess.ExecuteSP("Sp_Client", ht_Client);
if (dt_Client.Rows.Count > 0)
{
Clientid = int.Parse(dt_Client.Rows[0]["Client_Id"].ToString());
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[4].Style.ForeColor = Color.White;
}
//get_Subprocess
Hashtable ht_subprocess = new Hashtable();
DataTable dt_subprocess = new System.Data.DataTable();
ht_subprocess.Add("@Trans", "SUBPROCESSID");
ht_subprocess.Add("@Client_Id", Clientid);
ht_subprocess.Add("@Sub_ProcessName", Subprocess);
dt_subprocess = dataaccess.ExecuteSP("Sp_Client_SubProcess", ht_subprocess);
if (dt_subprocess.Rows.Count > 0)
{
Subprocessid = int.Parse(dt_subprocess.Rows[0]["Subprocess_Id"].ToString());
}
else
{
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Red;
grd_OrderEntry.Rows[i].Cells[5].Style.ForeColor = Color.White;
}
//check order number exist
Hashtable htcheck = new Hashtable();
DataTable dtcheck = new System.Data.DataTable();
htcheck.Add("@Trans", "ORDER_NUMBER");
htcheck.Add("@Client_Order_Number", Orderno);
dtcheck = dataaccess.ExecuteSP("Sp_Order", htcheck);
//Check_order = int.Parse(dtcheck.Rows[0]["count"].ToString());
if (dtcheck.Rows.Count > 0)
{
int Orderid = int.Parse(dtcheck.Rows[0]["Order_ID"].ToString());
grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor = Color.Cyan;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
private void btn_Import_Click(object sender, EventArgs e)
{
checkvalue = 0;
int Entervalue = 0;
int OrderInsert = 0;
for (int i = 0; i < grd_OrderEntry.Rows.Count; i++)
{
if (grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor != Color.White)
{
Entervalue = 1;
}
}
if (Entervalue == 1)
{
MessageBox.Show("Check the Errors and Already Existed Items in Excel");
}
if (Entervalue != 1)
{
for (int i = 0; i < grd_OrderEntry.Rows.Count; i++)
{
if (grd_OrderEntry.Rows[i].DefaultCellStyle.BackColor != System.Drawing.Color.Cyan)
{
//grd_OrderEntry.Rows.Add();
//grd_OrderEntry.Rows[i].Cells[0].Value = i + 1;
string Ordernumber = grd_OrderEntry.Rows[i].Cells[1].Value.ToString();
string APN = grd_OrderEntry.Rows[i].Cells[2].Value.ToString();
string OrderType = grd_OrderEntry.Rows[i].Cells[3].Value.ToString();
string Client = grd_OrderEntry.Rows[i].Cells[4].Value.ToString();
string SubProcess = grd_OrderEntry.Rows[i].Cells[5].Value.ToString();
string clientref = grd_OrderEntry.Rows[i].Cells[6].Value.ToString();
string Task = grd_OrderEntry.Rows[i].Cells[7].Value.ToString();
string Borrower = grd_OrderEntry.Rows[i].Cells[8].Value.ToString();
string Address = grd_OrderEntry.Rows[i].Cells[9].Value.ToString();
string County = grd_OrderEntry.Rows[i].Cells[10].Value.ToString();
string State = grd_OrderEntry.Rows[i].Cells[11].Value.ToString();
string Date = grd_OrderEntry.Rows[i].Cells[12].Value.ToString();
string Time = grd_OrderEntry.Rows[i].Cells[13].Value.ToString();
string Comments = grd_OrderEntry.Rows[i].Cells[14].Value.ToString();
//Change Date into DateTime
DateTime Received_date;
string date_received;
if (Date != "")
{
Received_date = Convert.ToDateTime(Date.ToString());
date_received = Received_date.ToString("MM/dd/yyyy");
}
else
{
Received_date = Convert.ToDateTime("01/01/1990");
date_received = Received_date.ToString("MM/dd/yyyy");
}
//get_max order number
Hashtable htmax = new Hashtable();
DataTable dtmax = new System.Data.DataTable();
htmax.Add("@Trans", "MAX_ORDER_NO");
dtmax = dataaccess.ExecuteSP("Sp_Order", htmax);
if (dtmax.Rows.Count > 0)
{
MAX_ORDER_NUMBER = "DRN" + "-" + dtmax.Rows[0]["ORDER_NUMBER"].ToString();
}
//OrderType Taken and Add
Hashtable htType = new Hashtable();
DataTable dtType = new System.Data.DataTable();
htType.Add("@Trans", "GETType");
htType.Add("@Order_Type", Ordertype);
dtType = dataaccess.ExecuteSP("Sp_Order_Get_Details", htType);
if (dtType.Rows.Count > 0)
{
Ordertype_id = int.Parse(dtType.Rows[0]["Order_Type_ID"].ToString());
}
//Get State name by ABR
Hashtable htbarowerstate = new Hashtable();
DataTable dtbarrowerstate = new System.Data.DataTable();
htbarowerstate.Add("@Trans", "GETSTATE_BY_ABR");
htbarowerstate.Add("@state_name", State);
dtbarrowerstate = dataaccess.ExecuteSP("Sp_Order_Get_Details", htbarowerstate);
if (dtbarrowerstate.Rows.Count > 0)
{
State_id = int.Parse(dtbarrowerstate.Rows[0]["State_ID"].ToString());
}
//get County
Hashtable htBarcounty = new Hashtable();
DataTable dtbarcounty = new System.Data.DataTable();
htBarcounty.Add("@Trans", "GET_COUNTY");
htBarcounty.Add("@state_Id", Stateid);
htBarcounty.Add("@County_Name", County);
dtbarcounty = dataaccess.ExecuteSP("Sp_Order_Get_Details", htBarcounty);
if (dtbarcounty.Rows.Count > 0)
{
County_id = int.Parse(dtbarcounty.Rows[0]["County_ID"].ToString());
}
Hashtable htcounty = new Hashtable();
DataTable dtcounty = new DataTable();
htcounty.Add("@Trans", "GET_COUNTY_TYPE");
htcounty.Add("@County", County_id);
dtcounty = dataaccess.ExecuteSP("Sp_Order", htcounty);
if (dtcounty.Rows.Count > 0)
{
Assign_County_Type = dtcounty.Rows[0]["County_Type"].ToString();
}
else
{
}
if (Assign_County_Type == "TIER 1")
{
Assign_County_Type_ID = 1;
}
else if (Assign_County_Type == "TIER 2")
{
Assign_County_Type_ID = 2;
}
//get_Order_Status
Hashtable htorderstatus = new Hashtable();
DataTable dtorderstatus = new System.Data.DataTable();
htorderstatus.Add("@Trans", "GET_ORDER_STATUS");
htorderstatus.Add("@Order_Status", Task);
dtorderstatus = dataaccess.ExecuteSP("Sp_Order_Get_Details", htorderstatus);
if (dtorderstatus.Rows.Count > 0)
{
Task_id = int.Parse(dtorderstatus.Rows[0]["Order_Status_ID"].ToString());
}
//get_Client
//Client_id = 0;
Hashtable ht_Client = new Hashtable();
DataTable dt_Client = new System.Data.DataTable();
ht_Client.Add("@Trans", "SELECT_Client_ID_Name");
ht_Client.Add("@Client_Name", Client);
dt_Client = dataaccess.ExecuteSP("Sp_Client", ht_Client);
if (dt_Client.Rows.Count > 0)
{
Client_id = int.Parse(dt_Client.Rows[0]["Client_Id"].ToString());
}
//get_Subprocess
Hashtable ht_subprocess = new Hashtable();
DataTable dt_subprocess = new System.Data.DataTable();
ht_subprocess.Add("@Trans", "SUBPROCESSID");
ht_subprocess.Add("@Client_Id", Client_id);
ht_subprocess.Add("@Sub_ProcessName", Subprocess);
dt_subprocess = dataaccess.ExecuteSP("Sp_Client_SubProcess", ht_subprocess);
if (dt_subprocess.Rows.Count > 0)
{
Subprocessid = int.Parse(dt_subprocess.Rows[0]["Subprocess_Id"].ToString());
}
//check order number exist
Hashtable htcheck = new Hashtable();
DataTable dtcheck = new System.Data.DataTable();
htcheck.Add("@Trans", "CHECK_ORDER_NUMBER");
htcheck.Add("@Client_Order_Number", Ordernumber);
//htcheck.Add("@State", State_id);
// htcheck.Add("@Borrower_Name", Borrower);
// htcheck.Add("@county", County_id);
// htcheck.Add("@Address", Address);
dtcheck = dataaccess.ExecuteSP("Sp_Order", htcheck);
Checkorder = int.Parse(dtcheck.Rows[0]["count"].ToString());
if (Checkorder <= 0)
{
OrderInsert = 1;
Hashtable htinsertrec = new Hashtable();
DataTable dtinsertrec = new System.Data.DataTable();
htinsertrec.Add("@Trans", "INSERT");
htinsertrec.Add("@Sub_ProcessId", Subprocessid);
htinsertrec.Add("@Placed_By", User_id);
htinsertrec.Add("@Order_Type", Ordertype_id);
htinsertrec.Add("@Order_Number", MAX_ORDER_NUMBER);
htinsertrec.Add("@APN", APN);
htinsertrec.Add("@Client_Order_Number", Ordernumber);
htinsertrec.Add("@Order_Status", Taskid);
htinsertrec.Add("@Client_Order_Ref", clientref);
//htinsertrec.Add("@Search_Type", lblSearch_Type.Text);
htinsertrec.Add("@Order_Progress", 8);
htinsertrec.Add("@Zip", 0);
htinsertrec.Add("@Date", date_received);
htinsertrec.Add("@Borrower_Name", Borrower);
htinsertrec.Add("@Address", Address);
htinsertrec.Add("@County", County_id);
htinsertrec.Add("@State", State_id);
htinsertrec.Add("@Recived_Date", date_received);
htinsertrec.Add("@Recived_Time", Time);
htinsertrec.Add("@Notes", Comments);
htinsertrec.Add("@Order_Assign_Type", Assign_County_Type_ID);
htinsertrec.Add("@Inserted_By", User_id);
htinsertrec.Add("@Inserted_date", DateTime.Now);
htinsertrec.Add("@status", "True");
dtinsertrec = dataaccess.ExecuteSP("Sp_Order", htinsertrec);
}
}
if (OrderInsert == 1)
{
MessageBox.Show("Order Imported Successfully");
grd_OrderEntry.Rows.Clear();
}
}
}
}