protected void addrowStage(object sender, EventArgs e)
{
if (ddlcertificate.Items.Count > 1)
{
lblerror.Visible = false;
FpSpreadstage.Sheets[0].RowCount = FpSpreadstage.Sheets[0].RowCount + 1;
string sqlcmd = string.Empty;
//sqlcmd = "select distinct textcode,textval from textvaltable where textcriteria in ('Sdis','dis') "; //modified by prabha textcriteria='Sdis'
sqlcmd = "select * from District_Stage";//Modified by rajasekar on 11/05/2018
DataSet dnew = new DataSet();
dnew = dset.select_method_wo_parameter(sqlcmd, "text");
FarPoint.Web.Spread.ComboBoxCellType cmbcol = new FarPoint.Web.Spread.ComboBoxCellType();
cmbcol.ShowButton = true;
cmbcol.UseValue = true;
cmbcol.DataSource = dnew;
//cmbcol.DataTextField = "textval";
//cmbcol.DataValueField = "textcode";
cmbcol.DataTextField = "District_Name"; //Modified by rajasekar on 11/05/2018
cmbcol.DataValueField = "District_Code"; //Modified by rajasekar on 11/05/2018
FpSpreadstage.Sheets[0].Columns[1].CellType = cmbcol;
FpSpreadstage.Sheets[0].Cells[0, 4].Value = 0;
Buttondelete.Enabled = false;
btnsave.Enabled = true;
FpSpreadstage.Sheets[0].PageSize = FpSpreadstage.Sheets[0].RowCount;
FpSpreadstage.SaveChanges();
}
else
{
lblerror.Visible = false;
lblerror.Text = "Enter the District Name";
}
}
public void loadstage()
{
FarPoint.Web.Spread.TextCellType tb = new FarPoint.Web.Spread.TextCellType();
//FpSpreadstage.Sheets[0].AutoPostBack = true;
//FpSpreadstage.ActiveSheetView.SheetCorner.Cells[0, 0].Text = "S.No";
//FpSpreadstage.ActiveSheetView.SheetCorner.DefaultStyle.Font.Bold = true;
FpSpreadstage.ActiveSheetView.DefaultRowHeight = 25;
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Size = FontUnit.Small;
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Bold = false;
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Bold = false;
FpSpreadstage.Sheets[0].RowHeader.Visible = false;
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Size = FontUnit.Small;
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Small;
FpSpreadstage.VerticalScrollBarPolicy = FarPoint.Web.Spread.ScrollBarPolicy.AsNeeded;
FpSpreadstage.HorizontalScrollBarPolicy = FarPoint.Web.Spread.ScrollBarPolicy.AsNeeded;
FarPoint.Web.Spread.ButtonCellType btnmodify = new FarPoint.Web.Spread.ButtonCellType();
FarPoint.Web.Spread.CheckBoxCellType cbxd = new FarPoint.Web.Spread.CheckBoxCellType();
FarPoint.Web.Spread.ComboBoxCellType cf = new FarPoint.Web.Spread.ComboBoxCellType();
cf.AutoPostBack = true;
cbxd.AutoPostBack = true;
btnmodify.Text = "Remove";
FpSpreadstage.Sheets[0].RowCount = 0;
FpSpreadstage.Sheets[0].ColumnCount = 5;
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 1].Text = "District";
FpSpreadstage.Sheets[0].Columns[1].CellType = cf;
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Stage Name";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Address";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Select";
FpSpreadstage.Sheets[0].Columns[4].CellType = cbxd;
//FpSpreadstage.Sheets[0].Columns[2].CellType = btnmodify;
FpSpreadstage.Sheets[0].Columns[3].Visible = true;
FpSpreadstage.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpreadstage.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Center;
//FpSpreadstage.Sheets[0].Columns[2].Font.Bold = true;
FpSpreadstage.Sheets[0].Columns[0].Width = 40;
FpSpreadstage.Sheets[0].Columns[1].Width = 200;
FpSpreadstage.Sheets[0].Columns[2].Width = 350;
FpSpreadstage.Sheets[0].Columns[3].Width = 340;
FpSpreadstage.Sheets[0].Columns[4].Width = 50;
FpSpreadstage.Sheets[0].PageSize = 100;
FpSpreadstage.Width = 1000;
FpSpreadstage.Height = 391;
FpSpreadstage.SaveChanges();
FpSpreadstage.CommandBar.Visible = false;
}
protected void btnMainGo_Click(object sender, EventArgs e)
{
FpSpreadstage.Sheets[0].RowCount = 0;
string typeall = string.Empty;
string veh_all = string.Empty;
if (ddlvehicletypeview.Text == "-1")
{
for (int i = 0; i < ddlvehicletypeview.Items.Count; i++)
{
if (i > 0)
{
if (typeall == "")
{
typeall = ddlvehicletypeview.Items[i].Value.ToString();
}
else
{
typeall = typeall + "','" + ddlvehicletypeview.Items[i].Value.ToString();
}
}
}
}
else
{
typeall = ddlvehicletypeview.Text.ToString();
}
if (ddltypeview.Text == "-1")
{
for (int i = 0; i < ddltypeview.Items.Count; i++)
{
if (i > 0)
{
if (veh_all == "")
{
//modified by srinath 17/2/2014
string getstagename = ddltypeview.Items[i].Text.ToString();
getstagename = getstagename.Replace("'", "''");
veh_all = getstagename;
}
else
{ //modified by srinath 17/2/2014
string getstagename = ddltypeview.Items[i].Text.ToString();
getstagename = getstagename.Replace("'", "''");
veh_all = veh_all + "','" + getstagename;
}
}
}
}
else
{
veh_all = ddltypeview.Text.ToString();
}
int temp = -1;
int temptemp = 0;
string getdate = "";
getdate = "select * from Stage_Master where District in('" + typeall + "') and Stage_Name in('" + veh_all + "')";
SqlDataAdapter dr = new SqlDataAdapter(getdate, con);
DataSet dt = new DataSet();
dr.Fill(dt);
if (dt.Tables[0].Rows.Count > 0)
{
Btnprint.Enabled = true;
Buttondelete.Enabled = false;
FpSpreadstage.Sheets[0].RowCount = dt.Tables[0].Rows.Count;
string sqlcmd = string.Empty;
//select distinct textcode,textval from textvaltable where textcriteria in ('Sdis','dis') modified by prabha
sqlcmd = "select distinct textcode,textval from textvaltable where textcriteria in ('Sdis','dis')";
DataSet dnew = new DataSet();
dnew = dset.select_method_wo_parameter(sqlcmd, "text");
for (int i2 = 0; i2 < dt.Tables[0].Rows.Count; i2++)
{
temp++;
temptemp = temp + 1;
FpSpreadstage.Sheets[0].Cells[i2, 0].Text = Convert.ToInt32(temptemp).ToString();
FpSpreadstage.Sheets[0].Cells[i2, 2].Text = dt.Tables[0].Rows[i2]["Stage_Name"].ToString();
FpSpreadstage.Sheets[0].Cells[i2, 2].Tag = dt.Tables[0].Rows[i2]["Stage_id"].ToString();
FpSpreadstage.Sheets[0].Cells[i2, 3].Text = dt.Tables[0].Rows[i2]["Address"].ToString();
FarPoint.Web.Spread.ComboBoxCellType cmbcol = new FarPoint.Web.Spread.ComboBoxCellType();
cmbcol.ShowButton = true;
cmbcol.UseValue = true;
cmbcol.DataSource = dnew;
cmbcol.DataTextField = "textval";
cmbcol.DataValueField = "textcode";
FpSpreadstage.Sheets[0].Columns[1].CellType = cmbcol;
FpSpreadstage.Sheets[0].Cells[i2, 1].Text = Convert.ToString(dt.Tables[0].Rows[i2]["District"]);
FpSpreadstage.Sheets[0].Cells[i2, 4].Value = 0;
}
FpSpreadstage.Sheets[0].PageSize = FpSpreadstage.Sheets[0].RowCount;
btnsave.Enabled = true;
Buttondelete.Enabled = true;
}
FpSpreadstage.SaveChanges();
}
protected void ButtonsaveRoute_Click(object sender, EventArgs e)
{
lblerror.Visible = false;
FpSpreadstage.SaveChanges();
ArrayList a1 = new ArrayList();
ArrayList a2 = new ArrayList();
Hashtable hatdis = new Hashtable();
string colcode = "";
string degreecode = "";
if (FpSpreadstage.Sheets[0].RowCount > 0)
{
for (int i = 0; i < FpSpreadstage.Rows.Count; i++)
{
colcode = FpSpreadstage.Sheets[0].Cells[i, 2].Text.ToString();
degreecode = FpSpreadstage.Sheets[0].Cells[i, 1].Text.ToString();
if (!hatdis.Contains(colcode))
{
hatdis.Add(colcode, colcode + ',' + degreecode);
}
//modified by srinath 16/2/2014
if (i != 0)
{
if (a1.Contains(colcode))
{
if (hatdis.Contains(colcode))
{
string disval = hatdis[colcode].ToString();
if (disval == colcode + ',' + degreecode)
{
a2.Add(colcode);
//srinath 19mar2013===============================
lblerror.Visible = true;
lblerror.Text = " '" + degreecode + '-' + colcode + "' Stage is Already Exist";
FpSpreadstage.Visible = true;
return;
//================================================
}
}
}
}
a1.Add(colcode);
}
int count = a2.ToArray().GroupBy(q => q).Count(q => q.Count() > 1);
if (a2.Count > 0)
{
lblerror.Visible = true;
lblerror.Text = "Enter different Stage Name and District";
FpSpreadstage.Visible = true;
return;
}
else
{
lblerror.Visible = false;
}
}
else
{
lblerror.Visible = true;
lblerror.Text = "Enter the Stage Details";
return;
}
if (FpSpreadstage.Sheets[0].RowCount > 0)
{
//First Delete And Insert and Update Query=====================================
//con.Close();
//con.Open();
//string deletequery = "";
//deletequery = "delete from Stage_Master";
//SqlCommand del = new SqlCommand(deletequery, con);
//del.ExecuteNonQuery();
//con.Close();
//===================================================================================
FpSpreadstage.SaveChanges();
for (int inew = 0; inew < FpSpreadstage.Sheets[0].RowCount; inew++)
{
string stagename = Convert.ToString(FpSpreadstage.Sheets[0].Cells[inew, 2].Text);
stagename = stagename.Replace("'", "''");
string stage_id = Convert.ToString(FpSpreadstage.Sheets[0].Cells[inew, 2].Tag);
string Address = Convert.ToString(FpSpreadstage.Sheets[0].Cells[inew, 3].Text);
string District = Convert.ToString(FpSpreadstage.GetEditValue(inew, 1));
if (District == "System.Object")
{
District = FpSpreadstage.Sheets[0].GetValue(inew, 1).ToString();
}
if (stagename != "")
{
//Update chehk========================
if (stage_id != "")
{
string chk_stage_id = string.Empty;
chk_stage_id = "select * from stage_master where Stage_id = '" + stage_id + "'";
DataSet cht_id = new DataSet();
cht_id = da.select_method_wo_parameter(chk_stage_id, "text");
if (cht_id.Tables[0].Rows.Count > 0)
{
string old_stageName = cht_id.Tables[0].Rows[0]["Stage_Name"].ToString();
old_stageName = old_stageName.Replace("'", "''");
string old_address = cht_id.Tables[0].Rows[0]["Address"].ToString();
string old_Dist = cht_id.Tables[0].Rows[0]["District"].ToString();
if (old_stageName == stagename && old_address == Address && old_Dist == District)
{
con.Close();
con.Open();
string updatequery = string.Empty;
updatequery = "update Stage_Master set Stage_Name = '" + stagename + "',Address = '" + Address + "',District = '" + District + "' where Stage_Name = '" + old_stageName + "' and Address = '" + old_address + "' and District = '" + old_Dist + "'";
SqlCommand cmd_new = new SqlCommand(updatequery, con);
cmd_new.ExecuteNonQuery();
con.Close();
}
else
{
con.Close();
con.Open();
string updatequery = string.Empty;
updatequery = "update Stage_Master set Stage_Name = '" + stagename + "',Address = '" + Address + "',District = '" + District + "' where Stage_Name = '" + old_stageName + "' and Address = '" + old_address + "' and District = '" + old_Dist + "'";
SqlCommand cmd_new = new SqlCommand(updatequery, con);
cmd_new.ExecuteNonQuery();
con.Close();
}
}
else
{
con.Close();
con.Open();
string sqlquery = "";
sqlquery = "insert into Stage_Master(Stage_Name,Address,District) values('" + stagename + "','" + Address + "','" + District + "')";
SqlCommand cmd = new SqlCommand(sqlquery, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
con.Close();
con.Open();
string sqlquery = "";
sqlquery = "insert into Stage_Master(Stage_Name,Address,District) values('" + stagename + "','" + Address + "','" + District + "')";
SqlCommand cmd = new SqlCommand(sqlquery, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//clear();
GetRecord();
ScriptManager.RegisterStartupScript(this, typeof(Page), UniqueID, "alert('Saved successfully')", true);
}
else
{
lblerror.Visible = true;
lblerror.Text = "Enter the Stage Details";
}
}
public void GetRecord()
{
int temp = -1;
int temptemp = 0;
string getdate = "";
getdate = "select * from Stage_Master";
SqlDataAdapter dr = new SqlDataAdapter(getdate, con);
DataSet dt = new DataSet();
dr.Fill(dt);
if (dt.Tables[0].Rows.Count > 0)
{
Btnprint.Enabled = true;
Buttondelete.Enabled = false;
FpSpreadstage.Sheets[0].RowCount = dt.Tables[0].Rows.Count;
string sqlcmd = string.Empty;
sqlcmd = "select * from District_Stage";//Modified by rajasekar on 11/05/2018
//sqlcmd = "select distinct textcode,textval from textvaltable where textcriteria in ('Sdis','dis')"; //modified by prabha on 21dec2017
DataSet dnew = new DataSet();
dnew = dset.select_method_wo_parameter(sqlcmd, "text");
ddltypeview.Items.Clear();
ddltypeview.Items.Insert(0, new ListItem("All", "-1"));
for (int i2 = 0; i2 < dt.Tables[0].Rows.Count; i2++)
{
temp++;
temptemp = temp + 1;
FpSpreadstage.Sheets[0].Cells[i2, 0].Text = Convert.ToInt32(temptemp).ToString();
FpSpreadstage.Sheets[0].Cells[i2, 2].Text = dt.Tables[0].Rows[i2]["Stage_Name"].ToString();
FpSpreadstage.Sheets[0].Cells[i2, 2].Tag = dt.Tables[0].Rows[i2]["Stage_id"].ToString();
FpSpreadstage.Sheets[0].Cells[i2, 3].Text = dt.Tables[0].Rows[i2]["Address"].ToString();
FarPoint.Web.Spread.ComboBoxCellType cmbcol = new FarPoint.Web.Spread.ComboBoxCellType();
cmbcol.ShowButton = true;
cmbcol.UseValue = true;
cmbcol.DataSource = dnew;
//cmbcol.DataTextField = "textval";
//cmbcol.DataValueField = "textcode";
cmbcol.DataTextField = "District_Name"; //Modified by rajasekar on 11/05/2018
cmbcol.DataValueField = "District_Code"; //Modified by rajasekar on 11/05/2018
FpSpreadstage.Sheets[0].Columns[1].CellType = cmbcol;
FpSpreadstage.Sheets[0].Cells[i2, 1].Text = Convert.ToString(dt.Tables[0].Rows[i2]["District"]);
FpSpreadstage.Sheets[0].Cells[i2, 4].Value = 0;
ddltypeview.Items.Add(dt.Tables[0].Rows[i2]["Stage_Name"].ToString());
}
//30Nov2013========================================================
ddlcertificate.Items.Clear();
ddlcertificate.DataSource = dnew;
//ddlcertificate.DataTextField = "textval";
//ddlcertificate.DataValueField = "textcode";
ddlcertificate.DataTextField = "District_Name"; //Modified by rajasekar on 11/05/2018
ddlcertificate.DataValueField = "District_Code"; //Modified by rajasekar on 11/05/2018
ddlcertificate.DataBind();
ddlcertificate.Items.Insert(0, "");
ddlvehicletypeview.Items.Clear();
ddlvehicletypeview.DataSource = dnew.Tables[0];
//ddlvehicletypeview.DataTextField = "textval";
//ddlvehicletypeview.DataValueField = "textcode";
ddlvehicletypeview.DataTextField = "District_Name"; //Modified by rajasekar on 11/05/2018
ddlvehicletypeview.DataValueField = "District_Code"; //Modified by rajasekar on 11/05/2018
ddlvehicletypeview.DataBind();
ddlvehicletypeview.Items.Insert(0, new ListItem("All", "-1"));
//=================================================================
FpSpreadstage.Sheets[0].PageSize = FpSpreadstage.Sheets[0].RowCount;
}
FpSpreadstage.SaveChanges();
}
