protected void Button1_Click(object sender, EventArgs e)
{
try
{
//==== Create SQL Table for storage
TableCreate();
foreach (GridViewRow row in GridView1.Rows)
{
//===== Accessing the checkbox
CheckBox cb = (CheckBox)row.FindControl("ChkSelect");
if (cb != null && cb.Checked)
{
int ID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
//== Search for the records and store in datatbase
string SQLP = "SELECT * FROM tbl_defn where ID ='" + Convert.ToString(ID) + "'";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd1 = new SqlCommand(SQLP, cn);
SqlDataReader r = cmd1.ExecuteReader();
while (r.Read())
{
string SQL = "INSERT INTO tbl_TargetDefine (grouptype,code,description) VALUES(@grouptype,@code,@description)";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmd = new SqlCommand(SQL, cn);
cmd.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = r["code"].ToString();
cmd.Parameters.AddWithValue("@description", SqlDbType.NVarChar).Value = r["description"].ToString();
cmd.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = r["grouptype"].ToString();
cmd.ExecuteReader();
}
}
}
}
catch (Exception ex)
{
webMessage.Show("ERROR :" + ex.Message.ToString());
return;
}
}
public static DataTable GetFacilityName(string State, string LGA)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(ConnectAll.ConnectMe()))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_GetFacilityName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@State", SqlDbType.NVarChar).Value = State;
cmd.Parameters.AddWithValue("@LGA", SqlDbType.NVarChar).Value = LGA;
cmd.Connection = con;
var sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(dt);
return(dt);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
foreach (GridViewRow row in GridView1.Rows)
{
TextBox Tx = (TextBox)row.FindControl("txtTarget");
if (Tx.Text != string.Empty)
{
string mCode, mGroup, mDescrip, mMonth, mYear;
// Button btn = sender as Button;
// GridViewRow row = btn.NamingContainer as GridViewRow;
// string pk = GridView1.DataKeys[row.RowIndex].Values["Id"].ToString();
mMonth = drpMonth.SelectedItem.Text.Trim();
mYear = drpYear.SelectedItem.Text.Trim();
TextBox ss = GridView1.Rows[row.RowIndex].Cells[3].FindControl("txtTarget") as TextBox;
mCode = GridView1.Rows[row.RowIndex].Cells[0].Text;
mGroup = GridView1.Rows[row.RowIndex].Cells[1].Text;
mDescrip = GridView1.Rows[row.RowIndex].Cells[2].Text;
string SQL = "INSERT INTO tbl_TargetAssin (grouptype,code,description,myears,months,targetvalue,facname)";
SQL += " VALUES (@grouptype,@code,@description,@myears,@months,@targetvalue,@facname)";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(SQL, cn);
cmd.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = mGroup.Trim();
cmd.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = mCode.Trim();
cmd.Parameters.AddWithValue("@description", SqlDbType.NVarChar).Value = mDescrip.Trim();
cmd.Parameters.AddWithValue("@myears", SqlDbType.NVarChar).Value = mYear.Trim();
cmd.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = mMonth.Trim();
cmd.Parameters.AddWithValue("@targetvalue", SqlDbType.Int).Value = Convert.ToInt32(ss.Text.Trim());
cmd.Parameters.AddWithValue("@facname", SqlDbType.NVarChar).Value = drpFacname.SelectedItem.Text.Trim();
cmd.ExecuteNonQuery();
}
}
webMessage.Show("Records saved sucessfully");
}
catch (Exception ex)
{
webMessage.Show("Error Saving Record :" + ex.Message.ToString());
return;
}
}
private void FillGrd()
{
using (SqlConnection Sqlcon = new SqlConnection(ConnectAll.ConnectMe()))
{
string strSelQuery = "SELECT distinct [statename],[lga] FROM tbl_states order by statename asc";
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSelQuery;
SqlDataAdapter SqlAda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
SqlAda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
private void CREATTable()
{
try
{
string SQLT = string.Concat("CREATE TABLE tbl_PROGACHIVE (ID INT IDENTITY (1,1) NOT NULL, GROUPTYPE NVARCHAR(50),code NVARCHAR(50),MONTHS NVARCHAR(50),",
"mYEAR NVARCHAR(50),DESCRIP NVARCHAR(MAX),TARGETVALUE INT, JANUARY INT, FEBRUARY INT, MARCH INT, APRIL INT, MAY INT, JUNE INT,",
"JULY INT, AUGUST INT, SEPTEMBER INT, OCTOBER INT, NOVEMBER INT, DECEMBER INT)");
int done = 0;
using (SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe()))
{
cn.Open();
SqlCommand cmd = new SqlCommand(SQLT, cn);
done = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
webMessage.Show("Error check for temp table :" + ex.Message);
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
string SQL = "INSERT INTO tbl_states (statename,lga) Values (@statename,@lga)";
try
{
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@statename", SqlDbType.NVarChar).Value = TextBox1.Text.Trim();
cmd.Parameters.AddWithValue("@lga", SqlDbType.NVarChar).Value = TextBox2.Text.Trim();
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
LblErr.Visible = true;
LblErr.Text = "Document not saved......." + ex.Message.ToString().Trim();
}
}
protected void Loadgrd()
{
try
{
string SQL = "Select * from tbl_grpname order by fname asc";
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataAdapter AD = new SqlDataAdapter(cmd);
DataSet DS = new DataSet();
AD.Fill(DS);
GridView1.DataSource = DS;
GridView1.DataBind();
}
catch (Exception ex)
{
lblerr.Visible = true;
lblerr.Text = "Error " + ex.Message.ToString().Trim();
return;
}
}
//===== Search Button
protected void ImageButton3_Click(object sender, ImageClickEventArgs e)
{
try
{
string SQL = "";
if (drpstate2.SelectedItem.Text.Trim() != "" && drpLGA2.SelectedItem.Text.Trim() != "")
{
SQL = " Select * from tbl_facility where statename='" + drpstate2.SelectedItem.Text.Trim() + "' and lga ='" + drpLGA2.SelectedItem.Text.Trim() + "'";
}
else if (drpstate2.SelectedItem.Text.Trim() != "" && drpLGA2.SelectedItem.Text.Trim() != "" && TextBox2.Text != "")
{
SQL = " Select * from tbl_facility where statename='" + drpstate2.SelectedItem.Text.Trim() + "' and lga ='" + drpLGA2.SelectedItem.Text.Trim() + "' and " +
"Facname ='" + TextBox2.Text.ToUpper() + "'";
}
else if (drpstate2.SelectedItem.Text.Trim() != "")
{
SQL = " Select * from tbl_facility where statename='" + drpstate2.SelectedItem.Text.Trim() + "'";
}
else
{
SQL = " Select * from tbl_facility where facname='" + TextBox2.Text.Trim() + "'";
}
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataAdapter AD = new SqlDataAdapter(cmd);
DataSet DS = new DataSet();
AD.Fill(DS);
GridView2.DataSource = DS;
GridView2.DataBind();
}
catch (Exception ex)
{
lblerr2.Visible = true;
lblerr2.Text = " " + ex.Message.ToString().Trim();
return;
}
}
// ===============
void Fillgrd()
{
try
{
string sql = "select * from tbl_facility order by facname asc";
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter sqlad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sqlad.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblerr.Visible = true;
lblerr.Text = " " + ex.Message.ToString().Trim();
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
try
{
string SQL = "INSERT INTO tbl_grpname (fname,fid) VALUES(@fname,@fid)";
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@fname", SqlDbType.NVarChar).Value = TextBox1.Text.Trim();
cmd.Parameters.AddWithValue("@fid", SqlDbType.NVarChar).Value = TextBox2.Text.Trim();
cmd.ExecuteNonQuery();
CLS();
Loadgrd();
}
catch (Exception ex)
{
lblerr.Visible = true;
lblerr.Text = "Error " + ex.Message.ToString().Trim();
return;
}
}
protected void drpLGA_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
drpFacname.Items.Clear();
drpFacname.Items.Add("");
string sql = "Select * from tbl_facility where statename ='" + drpStates.SelectedItem.Text.Trim() + "' and lga ='" + drpLGA.SelectedItem.Text.Trim() + "' order by facname asc";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
drpFacname.Items.Add(r["facname"].ToString());
}
}
catch (Exception ex)
{
webMessage.Show("Error : " + ex.Message.ToString());
return;
}
}
// code to check the compatibily of column and get the row item
protected void ChkLoadData(DataRow r, string c)
{
string names;
if (c.Trim() == "state" || c.Trim() == "LGA" || c.Trim() == "months" || c.Trim() == "years" || c.Trim() == "Facname")
{
}
string SQL = "Select * from tbl_defn where code='" + c.Trim() + "' and grouptype = '";
using (SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe()))
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmd = new SqlCommand(SQL, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
names = rd["description"].ToString();
}
}
}
protected void btnAddTextBox_Click(object sender, EventArgs e)
{
// Handled in preInit due to event sequencing.
string sql = "select * from tbl_art1";
DataTable dt;
using (SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe()))
{
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
dt = new DataTable("tbl_htc2");
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
} //dc loop
} //row loop
} // while loop
} // using
}
private void GetThematic(string thematicname, out string fname)
{
fname = string.Empty;
string sql = "Select * from tbl_grpname where fname='" + thematicname.Trim() + "'";
try
{
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
fname = rd["FID"].ToString();
}
}
catch (Exception ex)
{
webMessage.Show("Error :" + ex.Message.ToString());
return;
}
}
//This returns the Programm ABRIVIATION Name in the Table
private void GetThematic(string thematicname, out string fname)
{
fname = string.Empty;
string sql = "Select * from tbl_grpname where fname='" + thematicname.Trim() + "'";
try
{
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
fname = rd["FID"].ToString();
}
}
catch (Exception ex)
{
Response.Write("<script language=javascript>alert('" + ex.Message.ToString() + "' );</script>");
return;
}
}
//=======
protected void ImageButton4_Click(object sender, ImageClickEventArgs e)
{
string SQL = "";
try
{
if (TextBox3.Text != "" && TextBox4.Text != "")
{
SQL = "Select * from tbl_grpname where fname ='" + TextBox3.Text.Trim() + "' and fid='" + TextBox4.Text.Trim() + "'";
}
else if (TextBox4.Text != "")
{
SQL = "Select * from tbl_grpname where fid ='" + TextBox4.Text.Trim() + "'";
}
else
{
SQL = "Select * from tbl_grpname where fname ='" + TextBox3.Text.Trim() + "'";
}
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataAdapter AD = new SqlDataAdapter(cmd);
DataSet DS = new DataSet();
AD.Fill(DS);
GridView2.DataSource = DS;
GridView2.DataBind();
con.Close();
cmd.Dispose();
}
catch (Exception ex)
{
lblerr2.Visible = true;
lblerr2.Text = "Error " + ex.Message.Trim();
return;
}
}
protected void drpstate2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string SQL, SQL1 = "";
if (drpstate2.SelectedItem.Text.Trim() == "Ebonyi")
{
SQL1 = "Abakaliki";
SQL = "SELECT * from tbl_states where capitals ='" + SQL1 + "'";
}
else
{
SQL = "SELECT * from tbl_states where statename ='" + drpstate2.SelectedItem.Text.Trim() + "'";
}
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader R = cmd.ExecuteReader();
drpLGA2.Items.Clear();
drpLGA2.Items.Add("");
while (R.Read())
{
drpLGA2.Items.Add(R["lga"].ToString());
}
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblerr.Visible = true;
lblerr.Text = " " + ex.Message.ToString().Trim();
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string SQL = "Select * from tbl_grpname where fname='" + DropDownList1.SelectedItem.Text.Trim() + "'";
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
lblAbrv.Visible = true;
lblAbrv.Text = r["fid"].ToString();
}
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblerr.Visible = true;
lblerr.Text = "Error :" + ex.Message.Trim();
return;
}
}
private void LoadFac()
{
try
{
string SQL = " Select distinct * from tbl_facility order by facname asc";
SqlConnection con = new SqlConnection(ConnectAll.ConnectMe());
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader dr = cmd.ExecuteReader();
DropDownList1.ClearSelection();
DropDownList1.Items.Add("");
while (dr.Read())
{
DropDownList1.Items.Add(dr["facname"].ToString());
}
dr.Close();
cmd.Dispose();
con.Close();
}catch (Exception e)
{
lblerr.Text = "Error : " + e.Message.ToString();
return;
}
}//===== End
protected void drpStates_SelectedIndexChanged(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(3000);
string SQL, SQL1 = "";
if (drpStates.SelectedItem.Text.Trim() == "Ebonyi")
{
SQL1 = "Abakaliki";
SQL = "SELECT * from tbl_states where capitals ='" + SQL1 + "' order by lga asc";
}
else
{
SQL = "SELECT * from tbl_states where statename ='" + drpStates.SelectedItem.Text.Trim() + "' order by lga asc";
}
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(SQL, cn);
SqlDataReader r = cmd.ExecuteReader();
try
{
drpLGA.ClearSelection();
drpLGA.Items.Clear();
drpLGA.Items.Add("");
while (r.Read())
{
drpLGA.Items.Add(r["lga"].ToString());
}
}
catch (Exception ex)
{
Response.Write("<script language=javascript>alert('" + ex.Message.ToString() + "' );</script>");
return;
}
}
/*
* // http://localhost:26994/image/bar-ajax-loader.gif
* */
protected void Button1_Click(object sender, EventArgs e)
{
this.lblMsg.Visible = this.Image2.Visible = true;
this.Timer1.Enabled = true;
DropTable();
CREATTable();
//return;
string mFile11 = drpProgramArea.SelectedItem.Text.Trim();
// Console.WriteLine("Hello \n");
string mFile1 = string.Empty;
string mTableName = string.Empty;
GetThematic(mFile11, out mFile1);
//string SQL = "SELECT * FROM tbl_TargetAssin where grouptype ='" + mFile1.Trim() + "' and facname ='" + drpFacname.SelectedItem.Text.Trim() +
// "' and (myears = '" + drpYears.SelectedItem.Text.Trim() + "') and (myears= '" + drpYears2.SelectedItem.Text.Trim() +
// "' and months IN ('" + drpMonths.SelectedItem.Text.Trim() + "' , '" + drpMonths2.SelectedItem.Text.Trim() + "'))";
string SQL = string.Concat("SELECT * FROM tbl_TargetAssin where grouptype ='" + mFile1.Trim() + "' and facname ='" + drpFacname.SelectedItem.Text.Trim() + "' and ",
"myears Between '" + drpYears.SelectedItem.Text.Trim() + "' and '" + drpYears2.SelectedItem.Text.Trim() + "' and months between ",
"'" + drpMonths.SelectedItem.Text.Trim() + "' and '" + drpMonths2.SelectedItem.Text.Trim() + "'");
try
{
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(SQL, cn);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string SQLA = "INSERT INTO tbl_PROGACHIVE (GROUPTYPE,code,MONTHS,mYEAR,DESCRIP,TARGETVALUE) VALUES(@GROUPTYPE,@code,@MONTHS,@mYEAR,@DESCRIP,@TARGETVALUE) ";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmds = new SqlCommand(SQLA, cn);
cmds.Parameters.AddWithValue("@GROUPTYPE", SqlDbType.NVarChar).Value = r["grouptype"].ToString();
cmds.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = r["code"].ToString();
cmds.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = r["months"].ToString();
cmds.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = r["myears"].ToString();
cmds.Parameters.AddWithValue("@descrip", SqlDbType.NVarChar).Value = r["DESCRIPTION"].ToString();
cmds.Parameters.AddWithValue("@targetvalue", SqlDbType.Int).Value = Convert.ToInt32(r["targetvalue"].ToString());
cmds.ExecuteNonQuery();
}
//for (int i=1; i < DT.Rows.Count; i++)
//{
//}
string Colname = string.Empty;
DataTable DT = new DataTable();
DT = GetDatafromDatabase();
string SQLP = "Select * from tbl_PROGACHIVE WHERE (myear= '" + drpYears.SelectedItem.Text.Trim() + "') OR (myear= '" + drpYears2.SelectedItem.Text.Trim() +
"' and months IN ('" + drpMonths.SelectedItem.Text.Trim() + "' , '" + drpMonths2.SelectedItem.Text.Trim() + "'))";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdP = new SqlCommand(SQLP, cn);
SqlDataReader rp = cmdP.ExecuteReader();
while (rp.Read())
{
int Values = 0;
foreach (DataColumn col in DT.Columns)
{
foreach (DataRow row in DT.Rows)
{
string codep = rp["code"].ToString();
Colname = col.ColumnName.ToString();
if (codep.Trim() == Colname.Trim())
{
string UpdateMonth = rp["months"].ToString().ToUpper();;
if (UpdateMonth == "JANUARY")
{
string SQLUPDATEJAN = "UPDATE tbl_PROGACHIVE SET JANUARY=@JANUARY WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd1 = new SqlCommand(SQLUPDATEJAN, cn);
vcmd1.Parameters.AddWithValue("@JANUARY", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd1.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd1.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd1.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd1.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd1.ExecuteNonQuery();
}
else if (UpdateMonth == "FEBRUARY")
{
string SQLUPDATEFEB = "UPDATE tbl_PROGACHIVE SET FEBRUARY=@FEBRUARY WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd2 = new SqlCommand(SQLUPDATEFEB, cn);
vcmd2.Parameters.AddWithValue("@FEBRUARY", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd2.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd2.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd2.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd2.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd2.ExecuteNonQuery();
}
else if (UpdateMonth == "MARCH")
{
string SQLUPDATEMAR = "UPDATE tbl_PROGACHIVE SET MARCH=@MARCH WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd3 = new SqlCommand(SQLUPDATEMAR, cn);
vcmd3.Parameters.AddWithValue("@MARCH", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd3.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd3.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd3.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd3.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd3.ExecuteNonQuery();
}
else if (UpdateMonth == "APRIL")
{
string SQLUPDATEAPR = "UPDATE tbl_PROGACHIVE SET APRIL=@APRIL WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd4 = new SqlCommand(SQLUPDATEAPR, cn);
vcmd4.Parameters.AddWithValue("@APRIL", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd4.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd4.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd4.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd4.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd4.ExecuteNonQuery();
}
else if (UpdateMonth == "MAY")
{
string SQLUPDATEMAY = "UPDATE tbl_PROGACHIVE SET MAY=@MAY WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd5 = new SqlCommand(SQLUPDATEMAY, cn);
vcmd5.Parameters.AddWithValue("@MAY", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd5.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd5.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd5.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd5.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd5.ExecuteNonQuery();
}
else if (UpdateMonth == "JUNE")
{
string SQLUPDATEJUNE = "UPDATE tbl_PROGACHIVE SET JUNE=@JUNE WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd6 = new SqlCommand(SQLUPDATEJUNE, cn);
vcmd6.Parameters.AddWithValue("@JUNE", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd6.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd6.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd6.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd6.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd6.ExecuteNonQuery();
}
else if (UpdateMonth == "JULY")
{
string SQLUPDATEJULY = "UPDATE tbl_PROGACHIVE SET JULY=@JULY WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd7 = new SqlCommand(SQLUPDATEJULY, cn);
vcmd7.Parameters.AddWithValue("@JULY", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd7.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd7.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd7.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd7.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd7.ExecuteNonQuery();
}
else if (UpdateMonth == "AUGUST")
{
string SQLUPDATEAUG = "UPDATE tbl_PROGACHIVE SET AUGUST=@AUGUST WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd8 = new SqlCommand(SQLUPDATEAUG, cn);
vcmd8.Parameters.AddWithValue("@AUGUST", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd8.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd8.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd8.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd8.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd8.ExecuteNonQuery();
}
else if (UpdateMonth == "SEPTEMBER")
{
string SQLUPDATESEPT = "UPDATE tbl_PROGACHIVE SET SEPTEMBER=@SEPTEMBER WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd9 = new SqlCommand(SQLUPDATESEPT, cn);
vcmd9.Parameters.AddWithValue("@SEPTEMBER", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd9.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd9.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd9.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd9.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd9.ExecuteNonQuery();
}
else if (UpdateMonth == "OCTOBER")
{
string SQLUPDATEOCT = "UPDATE tbl_PROGACHIVE SET OCTOBER=@OCTOBER WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd10 = new SqlCommand(SQLUPDATEOCT, cn);
vcmd10.Parameters.AddWithValue("@OCTOBER", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd10.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd10.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd10.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd10.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd10.ExecuteNonQuery();
}
else if (UpdateMonth == "NOVEMBER")
{
string SQLUPDATE = "UPDATE tbl_PROGACHIVE SET NOVEMBER=@NOVEMBER WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd11 = new SqlCommand(SQLUPDATE, cn);
vcmd11.Parameters.AddWithValue("@NOVEMBER", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd11.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd11.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd11.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd11.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd11.ExecuteNonQuery();
}
else if (UpdateMonth == "DECEMBER")
{
string SQLUPDATEDEC = "UPDATE tbl_PROGACHIVE SET DECEMBER=@DECEMBER WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand vcmd12 = new SqlCommand(SQLUPDATEDEC, cn);
vcmd12.Parameters.AddWithValue("@DECEMBER", SqlDbType.Int).Value = Convert.ToInt32(row[col].ToString());
vcmd12.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = rp["grouptype"].ToString();
vcmd12.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rp["code"].ToString();
vcmd12.Parameters.AddWithValue("@months", SqlDbType.NVarChar).Value = rp["months"].ToString();
vcmd12.Parameters.AddWithValue("@myear", SqlDbType.NVarChar).Value = rp["myear"].ToString();
vcmd12.ExecuteNonQuery();
}
//string SQLUPDATE = "UPDATE tbl_PROGARCHIVE SET WHERE grouptype=@grouptype and code=@code and months=@months and myear =@myear";
}
}
}
}
}
catch (Exception ex)
{
webMessage.Show("Error : " + ex.Message.ToString());
return;
}
}
protected void Button2_Click(object sender, EventArgs e)
{
DropTable();
CREATTable();
string SQLH = "SELECT * FROM tbl_defn WHERE grouptype = '" + "HTC" + "' ";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(SQLH, cn);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string SQLIN = "INSERT INTO tbl_TempReport(grouptype,code,descrip) VALUES(@grouptype,@code,@descrip) ";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdINS = new SqlCommand(SQLIN, cn);
cmdINS.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = r["grouptype"].ToString();
cmdINS.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = r["code"].ToString();
cmdINS.Parameters.AddWithValue("@descrip", SqlDbType.NVarChar).Value = r["description"].ToString();
cmdINS.ExecuteNonQuery();
}
DataTable dt = GetDatafromDatabase(); //===== returns a DataTable
string SQLT = "SELECT * FROM tbl_TempReport";
string colname;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdT = new SqlCommand(SQLT, cn);
SqlDataReader rt = cmdT.ExecuteReader();
while (rt.Read())
{
// For each row, print the values of each column.
foreach (DataColumn column in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
colname = column.ColumnName.ToString();
//if (colname == "ID")
//{
// // break;
// rt.NextResult();
//}
//else
//{
string m_Code = rt["code"].ToString();
if (m_Code == colname)
{
//insert into tbl_TempReport to match the appropriate column
string SQLP = "UPDATE tbl_TempReport SET VALUEP = @VALUEP WHERE code = @code";
SqlCommand cmdp = new SqlCommand(SQLP, cn);
cmdp.Parameters.AddWithValue("@VALUEP", SqlDbType.Int).Value = row[column].ToString();
cmdp.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rt["code"].ToString();
cmdp.ExecuteNonQuery();
}
//ListBox1.Items.Add(row[column.ColumnName].ToString());
//}
//======
}
}
}
//========== Write to Excel
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWBK;
Microsoft.Office.Interop.Excel.Worksheet xlWS;
object misValue = System.Reflection.Missing.Value;
try
{
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
string SQLL = "SELECT * FROM tbl_TempReport ";
SqlCommand cmd1 = new SqlCommand(SQLL, cn);
SqlDataAdapter DA = new SqlDataAdapter(cmd1);
cmd1.Connection = cn;
DA.SelectCommand = cmd1;
DA.Fill(ds);
//=====================================================
string directoryPath = Server.MapPath(string.Format("~/{0}/", "XLS"));
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWBK = xlApp.Workbooks.Add(misValue);
xlWS = (Microsoft.Office.Interop.Excel.Worksheet)xlWBK.Worksheets.get_Item(1);
xlApp.Visible = true;
xlWS.Columns.Font.Bold = true;
for (int l = 0; l <= ds.Tables[0].Columns.Count - 1; l++)
{
xlWS.Columns.AutoFit();
xlWS.Columns.Font.Bold = true;
xlWS.Cells[1, l + 1] = ds.Tables[0].Columns[l].ColumnName.Trim();
}
xlWS.Columns.Font.Bold = false;
for (int i = 1; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
var data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWS.Columns.AutoFit();
xlWS.Cells[i + 1, j + 1] = data;
}
}
//string path = @"C:\EMR_INTERFACE\XLS\"; // HttpContext.Current.Server.MapPath("/");
xlWBK.SaveAs(directoryPath + "HTC" + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlWBK.Close(true, misValue, misValue);
//xlApp.Quit();
cn.Close();
//releaseObject(xlWS);
//releaseObject(xlWBK);
//releaseObject(xlApp);
}
catch (Exception ex)
{
}//======== Excel
}
protected void btnChart_Click(object sender, EventArgs e)
{
string st = string.Empty;
string mMnth = string.Empty;
string mYr = string.Empty;
int Cnt = 0;
string sql = "Select * from tbl_ART1";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
st = r["grouptype"].ToString();
mMnth = r["months"].ToString();
mYr = r["years"].ToString();
Cnt += Convert.ToInt32(r["TOTALG1"].ToString());
}
Chart1.Series[0].ChartType = (SeriesChartType)DropDownList1.SelectedIndex;
Chart1.Series[0].YValueMembers = "Volume1";
Chart1.Series[1].YValueMembers = "Volume2";
Chart1.Series[0].XValueMember = "Date";
// Chart1.ChartAreas = "Div1";
#region Sample Chart
////============================ Sample
// double[] yValues = { 15, 60, 12, 13 };
// string[] xValues = { "September", "October", "November", "December" };
// // Create a pie chart
// Chart chart = new Chart();
// // Build a pie series
// Series series = new Series("Default");
// series.ChartType = SeriesChartType.Pie;
// series["PieLabelStyle"] = "Disabled";
// chart.Series.Add(series);
// // Define the chart area
// ChartArea chartArea = new ChartArea();
// ChartArea3DStyle areaStyle = new ChartArea3DStyle(chartArea);
// areaStyle.Rotation = 0;
// Axis yAxis = new Axis(chartArea, AxisName.Y);
// Axis xAxis = new Axis(chartArea, AxisName.X);
// // Bind the data to the chart
// chart.Series["Default"].Points.DataBindXY(xValues, yValues);
// // Save the chart as a 300 x 200 image
// chart.Width = new System.Web.UI.WebControls.Unit(300, System.Web.UI.WebControls.UnitType.Pixel);
// chart.Height = new System.Web.UI.WebControls.Unit(200, System.Web.UI.WebControls.UnitType.Pixel);
// string filename = Server.MapPath("~") + "/Chart.png";
// chart.SaveImage(filename, ChartImageFormat.Png);
#endregion
}
private void DisplayExcel()
{
//========== Write to Excel
// Microsoft.Office.Interop.Excel.Application xlApp;
ExcelPackage ps = new ExcelPackage();
ps.Workbook.Worksheets.Add("DATA_SHEET");
ExcelWorksheet ws = ps.Workbook.Worksheets[1];
//Microsoft.Office.Interop.Excel.Workbook xlWBK;
//Microsoft.Office.Interop.Excel.Worksheet xlWS;
object misValue = System.Reflection.Missing.Value;
ExcelRange ChartRange = ws.Cells["A1:C1"];
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
try
{
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
string SQLL = "SELECT descrip,valuep FROM tbl_TempReport ";
SqlCommand cmd1 = new SqlCommand(SQLL, cn);
SqlDataAdapter DA = new SqlDataAdapter(cmd1);
cmd1.Connection = cn;
DA.SelectCommand = cmd1;
DA.Fill(ds);
//=====================================================
string directoryPath = Server.MapPath(string.Format("~/{0}/", "XLS"));
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
//xlApp = new Microsoft.Office.Interop.Excel.Application();
//xlWBK = xlApp.Workbooks.Add(misValue);
//xlWS = (Microsoft.Office.Interop.Excel.Worksheet)xlWBK.Worksheets.get_Item(1);
//xlApp.Visible = false;
//ChartRange = xlWS.get_Range("A1","C2");
//ChartRange.Font.Bold = true;
ws.Cells[1, 1].Value = "Facility Name :" + drpFacname.SelectedItem.Text.Trim();
ws.Cells[2, 1].Value = "Period From :" + drpMonths.SelectedItem.Text.Trim() + "/" + drpYears.SelectedItem.Text.Trim() + " " +
"TO :" + drpMonths2.SelectedItem.Text.Trim() + "/" + drpYears2.SelectedItem.Text.Trim();
//xlWS.Columns.Font.Bold = false;
//ChartRange = xlWS.get_Range("A4", "B4");
//ChartRange.Font.Bold = true;
ws.Cells["A4"].Value = "Description";
ws.Cells["B4"].Value = "Data";
//for (int t =4; t <= 4; t++)
//{
// for (int l = 0; l <= ds.Tables[0].Columns.Count - 1; l++)
// {
// ws.Cells[t, l + 1].Value = ds.Tables[0].Columns[l].ColumnName.Trim();
// }
//}
int p = 4;
int cnt = 4;
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
ws.Cells.Style.WrapText = false;
var data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
ws.Column(1).AutoFit();
ws.Cells[p + 1, j + 1].Value = data;
}
p += 1;
cnt = p;
}
//ChartRange = xlWS.get_Range("A4", Convert.ToString(cnt));
//ChartRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
//string path = @"C:\EMR_INTERFACE\XLS\"; // HttpContext.Current.Server.MapPath("/");
string PathsTo = directoryPath + drpProgramArea.SelectedItem.Text.Trim() + "-" + drpYears.SelectedItem.Text.Trim() + "-" + drpYears2.SelectedItem.Text + ".xls";
//xlWBK.SaveAs(PathsTo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//============ Download Excel file =====================
//Generate A File with Random name
Byte[] bin = ps.GetAsByteArray();
string files = PathsTo;
File.WriteAllBytes(files, bin);
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", files));
Response.ContentType = "application/ms-excel";
Response.WriteFile(files);
Response.End();
//========================================================================
//xlWBK.Close(true, misValue, misValue);
//xlApp.Quit();
cn.Close();
//releaseObject(xlWS);
//releaseObject(xlWBK);
//releaseObject(xlApp);
}
catch (Exception ex)
{
// webMessage.Show("Error Displaying Excel Content :" + ex.Message.ToString());
}//======== Excel
}
protected void Button1_Click(object sender, EventArgs e)
{
DropTable();
CREATTable();
//==================================================
string mFile1 = drpProgramArea.SelectedItem.Text.Trim();
// Console.WriteLine("Hello \n");
string mFile = string.Empty;
string mTableName = string.Empty;
FindTable.GetThematic(mFile1, out mFile); //<====== return groupType
//===============================================================
//string SQLH = "SELECT * FROM tbl_defn WHERE grouptype = '" + mFile + "' ";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
//SqlCommand cmd = new SqlCommand(SQLH, cn);
//SqlDataReader r = cmd.ExecuteReader();
//while (r.Read())
//{
var Dat = new DataTable();
Dat = FindTable.SearchTableDefine(mFile);
foreach (DataRow r in Dat.Rows)
{
//insert via procedure
FindTable.InsertTempReport(r["grouptype"].ToString(), r["code"].ToString(), r["description"].ToString());
}
var Mnth1 = FindTable.GetMonths(drpMonths.SelectedItem.Text.Trim());
var Mnth2 = FindTable.GetMonths(drpMonths2.SelectedItem.Text.Trim());
//DataTable dt = GetDatafromDatabase(drpProgramArea.SelectedItem.Text.Trim(),); //===== returns a DataTable
DataTable dt = FindTable.GetDatafromDatabase(drpProgramArea.SelectedItem.Text.Trim(), drpStates.SelectedItem.Text.Trim(),
drpLGA.SelectedItem.Text.Trim(), drpFacname.SelectedItem.Text.Trim(),
drpYears.SelectedItem.Text.Trim(), drpYears2.SelectedItem.Text.Trim(),
Mnth1, Mnth2);
string SQLT = "SELECT * FROM tbl_TempReport";
string colname;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdT = new SqlCommand(SQLT, cn);
SqlDataReader rt = cmdT.ExecuteReader();
while (rt.Read())
{
//==== For each row, print the values of each column.
int mValue = 0;
foreach (DataColumn column in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
colname = column.ColumnName.ToString();
string m_Code = rt["code"].ToString();
if (m_Code == colname)
{
// mValue = Convert.ToInt32((! string.IsNullOrEmpty(rt["ValueP"].ToString()) ? rt["ValueP"].ToString() :"0") );
if (!string.IsNullOrEmpty(row[column].ToString()))
{
//&& mValue > 0
if (m_Code == colname)
{
mValue += Convert.ToInt32(row[column].ToString());
}
else
{
mValue = Convert.ToInt32(row[column].ToString());
}
}
//============ insert into tbl_TempReport to match the appropriate column ====================
string SQLP = "UPDATE tbl_TempReport SET VALUEP = @VALUEP WHERE code = @code";
SqlCommand cmdp = new SqlCommand(SQLP, cn);
cmdp.Parameters.AddWithValue("@VALUEP", SqlDbType.Int).Value = mValue;
cmdp.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rt["code"].ToString();
cmdp.ExecuteNonQuery();
}
}
}
}
//==============================================================================================
//========== Display the Data to Excel ========================
//==============================================================================================
DisplayExcel(); //
}
public static DataTable GetDatafromDatabase(string ProgArea, string State, string LGA, string FacName, string Year1, string Year2, Int32 Month1, Int32 Month2)
{
string mFile11 = ProgArea;
// Console.WriteLine("Hello \n");
string mFile1 = string.Empty;
string mTableName = string.Empty;
GetThematic(mFile11, out mFile1);
if (mFile1 == "PMTCT")
{
mTableName = "VW_PMTCT";
}
if (mFile1 == "HTC")
{
mTableName = "VW_HTC";
}
if (mFile1 == "TB")
{
mTableName = "VW_TB";
}
if (mFile1 == "LAB")
{
mTableName = "VW_LAB";
}
if (mFile1 == "ART")
{
mTableName = "VW_ART";
}
if (mFile1 == "CARE")
{
mTableName = "VW_CARE";
}
if (mFile1 == "OVC")
{
mTableName = "tbl_OVC";
}
if (mFile1 == "GN")
{
mTableName = "tbl_GendNorm";
}
if (mFile1 == "PEP")
{
mTableName = "tbl_PEP";
}
if (mFile1 == "PP")
{
mTableName = "tbl_PP";
}
if (mFile1 == "HIVI")
{
mTableName = "tbl_HIVI";
}
if (mFile1 == "TRAINING")
{
mTableName = "tbl_TRAINING";
}
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(ConnectAll.ConnectMe()))
{
con.Open();
string SQLL = "Select * from " + mTableName + " WHERE states='" + State + "' and lga ='" + LGA +
"' and Facname ='" + FacName + "' AND years = '" + Year1 + "' OR (years = '" + Year2 +
"' and months IN ('" + Month1 + "' , '" + Month2 + "'))";
SqlCommand cmd = new SqlCommand(SQLL, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
}
return(dt);
}
public DataTable GetDatafromDatabase()
{
string mFile11 = drpProgramArea.SelectedItem.Text.Trim();
// Console.WriteLine("Hello \n");
string mFile1 = string.Empty;
string mTableName = string.Empty;
GetThematic(mFile11, out mFile1);
if (mFile1 == "PMTCT")
{
mTableName = "VW_PMTCT";
}
if (mFile1 == "HTC")
{
mTableName = "VW_HTC";
}
if (mFile1 == "TB")
{
mTableName = "VW_TB";
}
if (mFile1 == "LAB")
{
mTableName = "VW_LAB";
}
if (mFile1 == "ART")
{
mTableName = "VW_ART";
}
if (mFile1 == "CARE")
{
mTableName = "VW_CARE";
}
if (mFile1 == "OVC")
{
mTableName = "tbl_OVC";
}
if (mFile1 == "GN")
{
mTableName = "tbl_GendNorm";
}
if (mFile1 == "PEP")
{
mTableName = "tbl_PEP";
}
if (mFile1 == "PP")
{
mTableName = "tbl_PP";
}
if (mFile1 == "HIVI")
{
mTableName = "tbl_HIVI";
}
if (mFile1 == "TRAINING")
{
mTableName = "tbl_TRAINING";
}
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(ConnectAll.ConnectMe()))
{
con.Open();
string SQLL = "Select * from " + mTableName + " WHERE states='" + drpStates.SelectedItem.Text.Trim() + "' and lga ='" + drpLGA.SelectedItem.Text.Trim() +
"' and Facname ='" + drpFacname.SelectedItem.Text.Trim() + "' AND (years= '" + drpYears.SelectedItem.Text.Trim() + "') OR (years= '" + drpYears2.SelectedItem.Text.Trim() +
"' and months IN ('" + drpMonths.SelectedItem.Text.Trim() + "' , '" + drpMonths2.SelectedItem.Text.Trim() + "'))";
SqlCommand cmd = new SqlCommand(SQLL, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
}
return(dt);
}