protected void Page_Load(object sender, EventArgs e)
{
//create database sample
//go
//use sample
//go
//create table products (id int, name varchar(100), price int)
//go
//insert into products values(1, 'racecare', 10)
//insert into products values (2, 'balloons', 110)
//insert into products values (3, 'webcam', 12)
//go
//select id,name,price from products
// 1.) Inital
SqlConnection con = new SqlConnection("data source=CATROOM\\SQLEXPRESS; database=sample; integrated security=SSPI");
try
{
SqlCommand cmd = new SqlCommand("select id,name,price from products", con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
GridView1.DataSource = sdr;
GridView1.DataBind();
}
catch
{
}
finally
{
con.Close();
}
//2.) using the using command
string CS2 = "data source=CATROOM\\SQLEXPRESS; database=sample; integrated security=SSPI";
using (SqlConnection con2 = new SqlConnection(CS2))
{
SqlCommand cmd2 = new SqlCommand("select id,name,price from products", con2);
con2.Open();
SqlDataReader sdr = cmd2.ExecuteReader();
GridView2.DataSource = sdr;
GridView2.DataBind();
}
//3.) connection string in app config
string CS3 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
//Can also call by index (bad practice)
//ConfigurationManager.ConnectionStrings[0];
using (SqlConnection con3 = new SqlConnection(CS3))
{
SqlCommand cmd3 = new SqlCommand("select id,name,price from products", con3);
con3.Open();
SqlDataReader sdr = cmd3.ExecuteReader();
GridView3.DataSource = sdr;
GridView3.DataBind();
}
//4.) SQL command class
// ExecuteReader- use when tsql retunrs multiple rows
// ExecuteNonquery- use when perform Insert/Upd/Del
// ExeculateScalar- when returns single scalar value
string CS4 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
//ExecuteReader
using (SqlConnection con4 = new SqlConnection(CS4))
{
SqlCommand cmd4 = new SqlCommand("select id,name,price from products", con4);
con4.Open();
SqlDataReader sdr = cmd4.ExecuteReader();
GridView4.DataSource = sdr;
GridView4.DataBind();
}
// ExeculateScalar (return single value)
using (SqlConnection con5 = new SqlConnection(CS4))
{
SqlCommand cmd5 = new SqlCommand("select count(*) from products", con5);
con5.Open();
int count = (int)cmd5.ExecuteScalar();
Response.Write("Using ExecuteScalar total rows count= " + count.ToString());
Response.Write("<br>");
}
// ExeculateNonQuery (for CRUD)
using (SqlConnection con6 = new SqlConnection(CS4))
{
con6.Open();
SqlCommand cmd6 = new SqlCommand("delete from products where id=4", con6);
int returnValueOfRowsChanged = cmd6.ExecuteNonQuery();
// reuse cmd object
cmd6 = new SqlCommand("insert into products values(4, 'boop', 121)", con6);
returnValueOfRowsChanged = cmd6.ExecuteNonQuery();
Response.Write("Using ExeculateNonQuery total rows inserted= " + returnValueOfRowsChanged.ToString());
Response.Write("<br>");
}
//create procedure spListProducts
//@id int
//as
//begin
//select* from products
//where id = @id
//end
//5.) Call stored procedure (help prevent sql injection)
using (SqlConnection con7 = new SqlConnection(CS4))
{
SqlCommand cmd7 = new SqlCommand("spListProducts", con7);
cmd7.CommandType = System.Data.CommandType.StoredProcedure;
cmd7.Parameters.AddWithValue("@id", 3);
con7.Open();
SqlDataReader sdr = cmd7.ExecuteReader();
GridView5.DataSource = sdr;
GridView5.DataBind();
}
//create procedure spListProductsOutput
//@id int,
//@count int out
//as
//begin
//select @count = count(*) from products where id = @id
//end
//5.) Call stored procedure with output parameters
using (SqlConnection con8 = new SqlConnection(CS4))
{
SqlCommand cmd8 = new SqlCommand("spListProductsOutput", con8);
cmd8.CommandType = System.Data.CommandType.StoredProcedure;
cmd8.Parameters.AddWithValue("@id", 3);
SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@count";
outputParameter.SqlDbType = System.Data.SqlDbType.Int;
outputParameter.Direction = System.Data.ParameterDirection.Output;
cmd8.Parameters.Add(outputParameter);
con8.Open();
SqlDataReader sdr = cmd8.ExecuteReader();
con.Open();
int returnOutputValue = (int)outputParameter.Value;
Response.Write("Output parameter value= " + returnOutputValue.ToString());
Response.Write("<br>");
}
//6.) SQLDataReader
using (SqlConnection con9 = new SqlConnection(CS4))
{
SqlCommand cmd9 = new SqlCommand("select id,name,price from products", con9);
con9.Open();
using (SqlDataReader sdr = cmd9.ExecuteReader())
{
DataTable table = new DataTable();
table.Columns.Add("id");
table.Columns.Add("name");
table.Columns.Add("price");
table.Columns.Add("discountprice");
while (sdr.Read())
{
DataRow datarow = table.NewRow();
int originalPrice = (int)sdr["price"];
double discountedPrice = originalPrice * .9;
datarow["id"] = sdr["ID"];
datarow["name"] = sdr["name"];
datarow["price"] = originalPrice;
datarow["discountprice"] = discountedPrice;
table.Rows.Add(datarow);
}
GridView6.DataSource = table;
GridView6.DataBind();
}
}
//7.) SQLDataReader NextResult
using (SqlConnection con10 = new SqlConnection(CS4))
{
SqlCommand cmd10 = new SqlCommand("select id,price from products;select id,name from products", con10);
con10.Open();
using (SqlDataReader sdr = cmd10.ExecuteReader())
{
GridView7.DataSource = sdr;
GridView7.DataBind();
while (sdr.NextResult())
{
GridView8.DataSource = sdr;
GridView8.DataBind();
}
}
}
//8.) SQLDataApater and DataSet
using (SqlConnection con10 = new SqlConnection(CS4))
{
SqlDataAdapter da = new SqlDataAdapter("select id,price,name from products", con10);
DataSet ds = new DataSet(); //in memory representation of table in memory
da.Fill(ds);
GridView9.DataSource = ds;
GridView9.DataBind();
}
//create procedure spListTwoResults
//as
//begin
//select* from products
//select id from products
//end
//9.) DataSet in asp.net
using (SqlConnection con11 = new SqlConnection(CS4))
{
SqlDataAdapter da = new SqlDataAdapter("spListTwoResults", con11);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet(); //in memory representation of table in memory
da.Fill(ds);
GridView10.DataSource = ds.Tables[0];
GridView10.DataBind();
GridView11.DataSource = ds.Tables[1];
GridView11.DataBind();
}
//10.) Caching dataset
using (SqlConnection con11 = new SqlConnection(CS4))
{
SqlDataAdapter da = new SqlDataAdapter("spListTwoResults", con11);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet(); //in memory representation of table in memory
da.Fill(ds);
GridView10.DataSource = ds.Tables[0];
GridView10.DataBind();
GridView11.DataSource = ds.Tables[1];
GridView11.DataBind();
}
//Create Table tblStudents
//(
// ID int identity primary key,
// Name nvarchar(50),
// Gender nvarchar(20),
// TotalMarks int
//)
//Insert into tblStudents values('Mark Hastings', 'Male', 900)
//Insert into tblStudents values('Pam Nicholas', 'Female', 760)
//Insert into tblStudents values('John Stenson', 'Male', 980)
//Insert into tblStudents values('Ram Gerald', 'Male', 990)
//Insert into tblStudents values('Ron Simpson', 'Male', 440)
//Insert into tblStudents values('Able Wicht', 'Male', 320)
//Insert into tblStudents values('Steve Thompson', 'Male', 983)
//Insert into tblStudents values('James Bynes', 'Male', 720)
//Insert into tblStudents values('Mary Ward', 'Female', 870)
//Insert into tblStudents values('Nick Niron', 'Male', 680)
//11.) CommandBuilder
using (SqlConnection con11 = new SqlConnection(CS4))
{
SqlDataAdapter da = new SqlDataAdapter("spListTwoResults", con11);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet(); //in memory representation of table in memory
da.Fill(ds);
GridView10.DataSource = ds.Tables[0];
GridView10.DataBind();
GridView11.DataSource = ds.Tables[1];
GridView11.DataBind();
}
}//end pageload
protected void Page_Load(object sender, EventArgs e)
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
connect.Open();
if (Session["reg"] != null)
{
SqlCommand cmds = new SqlCommand("select name from stud where reg = '" + Session["reg"].ToString() + "'", connect);
SqlDataAdapter da = new SqlDataAdapter(cmds);
DataSet ds1 = new DataSet();
da.Fill(ds1);
int ij = ds1.Tables[0].Rows.Count;
if (ij > 0)
{
SqlCommand cmd1 = new SqlCommand("select id from stud where reg = '" + Session["reg"].ToString() + "' and pass='******'", connect);
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
DataSet ds2 = new DataSet();
da1.Fill(ds2);
int j = ds2.Tables[0].Rows.Count;
if (j > 0)
{
bt1.Text = ds1.Tables[0].Rows[0][0].ToString();
bt2.Visible = true;
}
else
{
bt1.Text = "Login";
}
}
else
{
bt1.Text = "Login";
}
}
else
{
bt1.Text = "Login";
}
if (Session["reg"] == null)
{
bt2.Visible = false;
}
else if (Session["reg"].ToString() == "admin")
{
bt1.Text = "admin";
Response.Write("Admin");
}
Table1.Visible = false;
Table2.Visible = false;
Table3.Visible = false;
Response.Write("Successful");
{
Response.Clear();
if (true)
{
// Response.Write("Connected");
DateTime dt = DateTime.Now;
var date1 = dt.Date;
date1 = date1.AddDays(-5);
var date2 = dt.Date;
date2 = date2.AddDays(30);
SqlCommand cmd5 = new SqlCommand("select Venue, Program_Type, Mont as Month, Start_Date, End_Date, Details, Contact from news where Start_Date <= '" + date2 + "' and Start_Date >= '" + date1 + "' order by Start_Date", connect);
cmd5.ExecuteNonQuery();
SqlDataAdapter da5 = new SqlDataAdapter(cmd5);
DataSet ds5 = new DataSet();
da5.Fill(ds5);
int ii = ds5.Tables[0].Rows.Count;
if (ii > 0)
{
GridView4.DataSource = (ds5);
GridView4.DataBind();
}
else
{
Response.Clear();
Response.Write("<script language=\"javascript\" type=\"text/javascript\">alert('Information Not Found');</script>");
Response.Write("Information Not Found");
}
}
}
if (!IsPostBack)
{
/* DropDownList2.Items.Clear();
* // DropDownList1.DataValueField = "stf";
*
* SqlCommand cmd2 = new SqlCommand("select distinct Mont from news", connect);
* cmd2.ExecuteNonQuery();
* SqlDataAdapter da1 = new SqlDataAdapter(cmd2);
* DataSet ds2 = new DataSet();
* da1.Fill(ds2);
* DropDownList2.DataSource = ds2;
* DropDownList2.DataBind();
* DropDownList2.DataTextField = "Mont";
* DropDownList2.DataValueField = "Mont";
* DropDownList2.DataBind();*/
}
if (!IsPostBack)
{
DropDownList1.Items.Clear();
// DropDownList1.DataValueField = "stf";
SqlCommand cmd1 = new SqlCommand("select distinct Venue from news", connect);
cmd1.ExecuteNonQuery();
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
DropDownList1.DataSource = ds1;
DropDownList1.DataBind();
DropDownList1.DataTextField = "Venue";
DropDownList1.DataValueField = "Venue";
DropDownList1.DataBind();
}
if (!IsPostBack)
{
DropDownList3.Items.Clear();
// DropDownList1.DataValueField = "stf";
SqlCommand cmd3 = new SqlCommand("select distinct Program_Type from news", connect);
cmd3.ExecuteNonQuery();
SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
DropDownList3.DataSource = ds3;
DropDownList3.DataBind();
DropDownList3.DataTextField = "Program_Type";
DropDownList3.DataValueField = "Program_Type";
DropDownList3.DataBind();
}
}
private void Bindgrid4()
{
GridView4.DataSource = pwp.QueryWeekPlanDetail(new Guid(WeekPlanID.Text));
GridView4.DataBind();
UpdatePanel6.Update();
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception)
{
con.Close();
return;
throw;
}
string pNumber = TextBoxNumber.Text;
string pName = TextBoxName.Text;
string personalID = TextBoxPersonalID.Text;
string pType = "T";
string category = TextBoxCategory.Text;
string materials = TextBoxMaterials.Text;
string sqlStrPiece = "INSERT INTO Piece_T (PieceNumber, PieceName, PieceDescription, YearOfMade, EntryDay, EntryMonth, EntryYear, EntryHour, CountryOfOrigin, PersonalID, PieceType) VALUES("
+ pNumber + ", '" + pName + "', ";
string sqlStrTools = "INSERT INTO Tools_T (TPieceNumber, Category, ToolsOwner) VALUES(" + pNumber + ", '" + category + "', ";
string sqlStrToolsMaterials;
if (!string.IsNullOrEmpty(TextBoxDecription.Text))
{
sqlStrPiece += "'" + TextBoxDecription.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYoM.Text))
{
sqlStrPiece += TextBoxYoM.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxDay.Text))
{
sqlStrPiece += TextBoxDay.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxMonth.Text))
{
sqlStrPiece += TextBoxMonth.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYear.Text))
{
sqlStrPiece += TextBoxYear.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxHour.Text))
{
sqlStrPiece += TextBoxHour.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxCoO.Text))
{
sqlStrPiece += "'" + TextBoxCoO.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxOwner.Text))
{
sqlStrTools += "'" + TextBoxOwner.Text + "');";
}
else
{
sqlStrTools += "NULL);";
}
sqlStrPiece += personalID + ", '" + pType + "');";
SqlDataAdapter adapter1 = new SqlDataAdapter();
SqlCommand execPiece = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand.ExecuteNonQuery();
execPiece.Dispose();
SqlDataAdapter adapter2 = new SqlDataAdapter();
SqlCommand execTools = new SqlCommand(sqlStrTools, con);
adapter2.InsertCommand = new SqlCommand(sqlStrTools, con);
adapter2.InsertCommand.ExecuteNonQuery();
execTools.Dispose();
using (StringReader reader = new StringReader(materials))
{
string line;
while ((line = reader.ReadLine()) != null)
{
sqlStrToolsMaterials = "INSERT INTO Tools_Materials_T (TPieceNumber, Material) VALUES(" + pNumber + ", '" + line + "');";
SqlDataAdapter adapter3 = new SqlDataAdapter();
SqlCommand execTMat = new SqlCommand(sqlStrToolsMaterials, con);
adapter3.InsertCommand = new SqlCommand(sqlStrToolsMaterials, con);
adapter3.InsertCommand.ExecuteNonQuery();
execTMat.Dispose();
}
}
DataSet ds1 = new DataSet();
string sqlstr = "select * from Piece_T";
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds1);
GridView3.DataSource = ds1;
GridView3.DataBind();
DataSet ds2 = new DataSet();
sqlstr = "select * from Tools_T";
da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds2);
GridView4.DataSource = ds2;
GridView4.DataBind();
DataSet ds3 = new DataSet();
sqlstr = "select * from Tools_Materials_T";
da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds3);
GridView5.DataSource = ds3;
GridView5.DataBind();
}
void PostDoosage()
{
GridView4.DataSource = ClassDataManager.LoadDosage(drugi);
GridView4.DataBind();
}
protected void Button4_Click(object sender, EventArgs e)
{
string accessToken = ((Button)sender).CommandArgument;
Int16 operationCount = 0;
if (IsPostBack)
{
// Get the host web's URL.
sharepointUrl = new Uri(Request.QueryString["SPHostUrl"]);
}
// Create the parent request
var batchRequest = new BatchODataRequest(String.Format("{0}/_api/", sharepointUrl)); // ctor adds "$batch"
batchRequest.SetHeader("Authorization", "Bearer " + accessToken);
using (var oDataMessageWriter = new ODataMessageWriter(batchRequest))
{
var oDataBatchWriter = oDataMessageWriter.CreateODataBatchWriter();
oDataBatchWriter.WriteStartBatch();
oDataBatchWriter.WriteStartChangeset();
// Create the list deleting operation
var deleteOperation = oDataBatchWriter.CreateOperationRequestMessage(
"DELETE", new Uri(sharepointUrl.ToString() + "/_api/Web/lists/getbytitle(\'" + OldList.Text + "\')"));
deleteOperation.SetHeader("If-Match", "\"1\"");
oDataBatchWriter.WriteEndChangeset();
operationCount++;
// Create the query operation
var queryOperationMessage3 = oDataBatchWriter.CreateOperationRequestMessage(
"GET", new Uri(sharepointUrl.ToString() + "/_api/Web/lists"));
operationCount++;
oDataBatchWriter.WriteEndBatch();
oDataBatchWriter.Flush();
}
// Parse the response and bind the data to the UI controls
var oDataResponse = batchRequest.GetResponse();
using (var oDataReader = new ODataMessageReader(oDataResponse))
{
var oDataBatchReader = oDataReader.CreateODataBatchReader();
while (oDataBatchReader.Read())
{
switch (oDataBatchReader.State)
{
case ODataBatchReaderState.Initial:
// Optionally, handle the start of a batch payload.
break;
case ODataBatchReaderState.Operation:
// Encountered an operation (either top-level or in a changeset)
var operationResponse = oDataBatchReader.CreateOperationResponseMessage();
// Response ATOM markup parsing and presentation
using (var stream = operationResponse.GetStream())
{
switch (operationCount)
{
case 2: // The "delete list" operation
if (operationResponse.StatusCode == 200)
{
DeleteListResponse.Text = "Your list was deleted!";
}
else
{
DeleteListResponse.Text = "Your list was not deleted. Status returned: " + operationResponse.StatusCode.ToString();
}
operationCount--;
break;
case 1: // The "List of Lists" operation
// Bind data to the grid on the page.
// In a production app, check operationResponse.StatusCode and handle non-200 statuses.
// For simplicity, this sample assumes status 200 (the list items are returned).
List <XElement> entries = SharePointDataHelpers.ListDataHelper.ExtractListItemsFromATOMResponse(stream);
var itemTitles = SharePointDataHelpers.ListDataHelper.GetItemTitles(entries);
GridView4.DataSource = itemTitles;
GridView4.DataBind();
operationCount--;
break;
}
};
break;
case ODataBatchReaderState.ChangesetStart:
// Optionally, handle the start of a change set.
break;
case ODataBatchReaderState.ChangesetEnd:
// When this sample was created, SharePoint did not support "all or nothing" transactions.
// If that changes in the future this is where you would commit the transaction.
break;
case ODataBatchReaderState.Exception:
// In a producition app handle exeception. Omitted for simplicity in this sample app.
break;
}
}
}
GridView3.Visible = false;
}
protected void BtnSearch_Click(object sender, EventArgs e)
{
string KeyWord = SystemSet.CheckMSSQLLike(SystemSet.ReplaceBlank(TxFirmName.Text));
string KeyWord1 = SystemSet.CheckMSSQLLike(SystemSet.ReplaceBlank(TxSpecialty.Text));
#region 搜尋條件
string x = "select * from FirmM where ";
string xx = "依 ";
int[] SelectCondition = new int[5];
for (int i = 0; i < 5; i++)
{
SelectCondition[i] = 0;
}
if (KeyWord.Trim() != "")
{
SelectCondition[1] = 1;
}
if (DDL_Select2.SelectedValue.ToString() != "0")
{
SelectCondition[2] = 1;
}
if (KeyWord1.Trim() != "")
{
//if (TB_Select32.Text.Trim() != "")
//{
// SelectCondition[3] = 2;
//}
//else
//{
SelectCondition[3] = 1;
//}
}
if (DDL_Select4.SelectedValue.ToString() != "0")
{
SelectCondition[4] = 1;
}
for (int i = 1; i < 5; i++)
{
if (SelectCondition[i] != 0)
{
if (SelectCondition[0] != 0)
{
DropDownList myDDL = (DropDownList)Pnl_Search.FindControl("DDL_Op" + SelectCondition[0].ToString());
//MsgBox(myDDL.ID);
x += myDDL.SelectedValue + " ";
xx += myDDL.SelectedItem.Text + " ";
}
switch (i)
{
case 1:
x += "Name like '%" + KeyWord.Trim() + "%' ";
xx += "[廠商名稱]=(關鍵字)" + KeyWord.Trim() + " ";
break;
case 2:
x += "Type ='" + DDL_Select2.SelectedValue.ToString() + "' ";
xx += "[廠商類型]=" + DDL_Select2.SelectedValue.ToString() + " ";
break;
case 3:
x += "SupportItem like '%" + KeyWord1.Trim() + "%' ";
xx += "[廠商專長/供給材料]=(關鍵字)" + KeyWord1.Trim() + " ";
//if (SelectCondition[i] == 2)
//{
// //x += DDL_Op3in.SelectedValue.ToString() + " ";
// //xx += DDL_Op3in.SelectedItem.Text + " ";
// x += "SupportItem like '%" + TB_Select32.Text.Trim() + "%' ";
// xx += "(關鍵字)" + TB_Select32.Text.Trim() + " ";
//}
break;
case 4:
x += "Location ='" + DDL_Select4.SelectedValue.ToString() + "' ";
xx += "[所在縣市]=" + DDL_Select4.SelectedValue.ToString() + " ";
break;
}
SelectCondition[0] = i;
}
}
//if (SelectCondition[0] == 0)
//{
// MsgBox("請至少輸入一種搜尋條件!");
//}
xx += "之搜尋結果";
//LB_Status.Text = xx;
//SqlDataSourceSelect.ConnectionString = Utility.DBconnection.connect_string(Session["DatabaseName"].ToString());
if (SelectCondition[0] != 0)
{
SqlDataSource6.SelectCommand = x;
}
else
{
SqlDataSource6.SelectCommand = "select * from FirmM ";
}
//GridView0.DataSourceID = "SqlDataSourceSelect";
GridView4.DataBind();
#endregion
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception)
{
con.Close();
return;
throw;
}
string pNumber = TextBoxNumber.Text;
string pName = TextBoxName.Text;
string personalID = TextBoxPersonalID.Text;
string pType = "S";
string sculptor = TextBoxSculptor.Text;
string sqlStrPiece = "INSERT INTO Piece_T (PieceNumber, PieceName, PieceDescription, YearOfMade, EntryDay, EntryMonth, EntryYear, EntryHour, CountryOfOrigin, PersonalID, PieceType) VALUES("
+ pNumber + ", '" + pName + "', ";
string sqlStrSculpture = "INSERT INTO Sculpture_T (SPieceNumber, Sculptor, Width, Height, Depth, Material) VALUES(" + pNumber + ", '" + sculptor + "', ";
if (!string.IsNullOrEmpty(TextBoxDecription.Text))
{
sqlStrPiece += "'" + TextBoxDecription.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYoM.Text))
{
sqlStrPiece += TextBoxYoM.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxDay.Text))
{
sqlStrPiece += TextBoxDay.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxMonth.Text))
{
sqlStrPiece += TextBoxMonth.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYear.Text))
{
sqlStrPiece += TextBoxYear.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxHour.Text))
{
sqlStrPiece += TextBoxHour.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxCoO.Text))
{
sqlStrPiece += "'" + TextBoxCoO.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxWidth.Text))
{
sqlStrSculpture += TextBoxWidth.Text + ", ";
}
else
{
sqlStrSculpture += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxHeight.Text))
{
sqlStrSculpture += TextBoxHeight.Text + ", ";
}
else
{
sqlStrSculpture += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxDepth.Text))
{
sqlStrSculpture += TextBoxDepth.Text + ", ";
}
else
{
sqlStrSculpture += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxMaterial.Text))
{
sqlStrSculpture += "'" + TextBoxMaterial.Text + "');";
}
else
{
sqlStrSculpture += "NULL);";
}
sqlStrPiece += personalID + ", '" + pType + "');";
SqlDataAdapter adapter1 = new SqlDataAdapter();
SqlCommand execPiece = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand.ExecuteNonQuery();
execPiece.Dispose();
SqlDataAdapter adapter2 = new SqlDataAdapter();
SqlCommand execPainting = new SqlCommand(sqlStrSculpture, con);
adapter2.InsertCommand = new SqlCommand(sqlStrSculpture, con);
adapter2.InsertCommand.ExecuteNonQuery();
execPainting.Dispose();
DataSet ds1 = new DataSet();
string sqlstr = "select * from Piece_T";
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds1);
GridView3.DataSource = ds1;
GridView3.DataBind();
DataSet ds2 = new DataSet();
sqlstr = "select * from Sculpture_T";
da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds2);
GridView4.DataSource = ds2;
GridView4.DataBind();
}
//初始化试卷，从数据库中将试题取出
protected void InitData()
{
DataBase DB = new DataBase();
int paperID = int.Parse(Session["PaperID"].ToString());
SqlParameter[] Params1 = new SqlParameter[2];
Params1[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperID); //试卷编号
Params1[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "单选题"); //题目类型
DataSet ds1 = DB.GetDataSet("Proc_PaperDetail", Params1);
if (ds1.Tables[0].Rows.Count > 0)//判断下是否有单选题
{
GridView11.DataSource = ds1;
GridView11.DataBind();
((Label)GridView11.HeaderRow.FindControl("Label27")).Text = ((Label)GridView11.Rows[0].FindControl("Label4")).Text;
}
SqlParameter[] Params2 = new SqlParameter[2];
Params2[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperID); //试卷编号
Params2[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "多选题"); //题目类型
DataSet ds2 = DB.GetDataSet("Proc_PaperDetail", Params2);
if (ds2.Tables[0].Rows.Count > 0)
{
GridView2.DataSource = ds2;
GridView2.DataBind();
((Label)GridView2.HeaderRow.FindControl("Label28")).Text = ((Label)GridView2.Rows[0].FindControl("Label8")).Text;
}
SqlParameter[] Params3 = new SqlParameter[2];
Params3[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperID); //试卷编号
Params3[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "判断题"); //题目类型
DataSet ds3 = DB.GetDataSet("Proc_PaperDetail", Params3);
if (ds3.Tables[0].Rows.Count > 0)
{
GridView3.DataSource = ds3;
GridView3.DataBind();
((Label)GridView3.HeaderRow.FindControl("Label29")).Text = ((Label)GridView3.Rows[0].FindControl("Label12")).Text;
}
SqlParameter[] Params4 = new SqlParameter[2];
Params4[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperID); //试卷编号
Params4[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "填空题"); //题目类型
DataSet ds4 = DB.GetDataSet("Proc_PaperDetail", Params4);
if (ds4.Tables[0].Rows.Count > 0)
{
GridView4.DataSource = ds4;
GridView4.DataBind();
((Label)GridView4.HeaderRow.FindControl("Label45")).Text = ((Label)GridView4.Rows[0].FindControl("Label17")).Text;
}
SqlParameter[] Params5 = new SqlParameter[2];
Params5[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperID); //试卷编号
Params5[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "问答题"); //题目类型
DataSet ds5 = DB.GetDataSet("Proc_PaperDetail", Params5);
if (ds5.Tables[0].Rows.Count > 0)
{
GridView5.DataSource = ds5;
GridView5.DataBind();
((Label)GridView5.HeaderRow.FindControl("Label31")).Text = ((Label)GridView5.Rows[0].FindControl("Label37")).Text;
}
}
protected void GridView4_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
GridView4.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
//--------------------------------BUGS
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Bugs' "
+ " From Caso "
+ " Where categoria = 1", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData);
GridView1.DataSource = (tblData);
GridView1.DataBind();
//--------------------------------Mejoras
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Mejoras' "
+ " From Caso "
+ " Where categoria = 2", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData2);
GridView2.DataSource = (tblData2);
GridView2.DataBind();
//--------------------------------INVESTIGACIONES
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Investigaciones' "
+ " From Caso "
+ " Where categoria = 3", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData3);
GridView3.DataSource = (tblData3);
GridView3.DataBind();
//--------------------------------Actividades
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Actividades' "
+ " From Caso "
+ " Where categoria = 4", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData4);
GridView4.DataSource = (tblData4);
GridView4.DataBind();
//--------------------------------Activos
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos Activos' "
+ " From Caso "
+ " Where estado = 1", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData5);
GridView5.DataSource = (tblData5);
GridView5.DataBind();
//--------------------------------Reactivados
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos Reactivados' "
+ " From Caso "
+ " Where estado = 2", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData6);
GridView6.DataSource = (tblData6);
GridView6.DataBind();
//--------------------------------Cerrados
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos Cerrados' "
+ " From Caso "
+ " Where estado = 3", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData7);
GridView7.DataSource = (tblData7);
GridView7.DataBind();
//--------------------------------Reactivados
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos Resueltos' "
+ " From Caso "
+ " Where estado = 4", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData8);
GridView8.DataSource = (tblData8);
GridView8.DataBind();
//--------------------------------Criticos
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos Criticos' "
+ " From Caso "
+ " Where prioridad = 1", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData9);
GridView9.DataSource = (tblData9);
GridView9.DataBind();
//--------------------------------Deben Arreglarse
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos que Deben Arreglarse' "
+ " From Caso "
+ " Where prioridad = 2", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData10);
GridView10.DataSource = (tblData10);
GridView10.DataBind();
//--------------------------------Arreglarse si hay tiempo
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos que Deben Arreglarse si hay tiempo' "
+ " From Caso "
+ " Where prioridad = 3", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData11);
GridView11.DataSource = (tblData11);
GridView11.DataBind();
//--------------------------------Reactivados
cn = Conexion.conectar();
cmd = new SqlCommand("Select COUNT(categoria) As 'Casos que no deben arreglarse' "
+ " From Caso "
+ " Where prioridad = 4", cn.getSqlConnection());
sda = new SqlDataAdapter(cmd);
sda.Fill(tblData12);
GridView12.DataSource = (tblData12);
GridView12.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["username"] == null || Session["function"] == null)
{
Response.Redirect("~/Index.aspx");
}
Panel1.Visible = false;
Panel2.Visible = false;
Panel3.Visible = false;
Panel4.Visible = false;
Panel5.Visible = false;
Panel6.Visible = false;
Panel7.Visible = false;
OpUserSelect ous = new OpUserSelect();
OperationResult obj = OperationManager.Singleton.executeOperation(ous);
if ((obj == null) || (!obj.Status))
{
return;
}
else
{
DbItem[] items = obj.DbItems;
UserDb[] users = items.Cast <UserDb>().ToArray();
GridView1.DataSource = obj.DbItems;
GridView1.DataBind();
}
OpContactSelect ocs = new OpContactSelect();
OperationResult obj2 = OperationManager.Singleton.executeOperation(ocs);
if ((obj2 == null) || (!obj2.Status))
{
return;
}
else
{
DbItem[] items = obj2.DbItems;
ContactDb[] contacts = items.Cast <ContactDb>().ToArray();
GridView2.DataSource = obj2.DbItems;
GridView2.DataBind();
}
OpGallerySelect ogs = new OpGallerySelect();
OperationResult obj3 = OperationManager.Singleton.executeOperation(ogs);
if ((obj3 == null) || (!obj3.Status))
{
return;
}
else
{
DbItem[] items = obj3.DbItems;
GalleryDb[] galleries = items.Cast <GalleryDb>().ToArray();
GridView3.DataSource = obj3.DbItems;
GridView3.DataBind();
}
OpAboutSelect oas = new OpAboutSelect();
OperationResult obj4 = OperationManager.Singleton.executeOperation(oas);
if ((obj4 == null) || (!obj4.Status))
{
return;
}
else
{
DbItem[] items = obj4.DbItems;
AboutDb[] abouts = items.Cast <AboutDb>().ToArray();
GridView4.DataSource = obj4.DbItems;
GridView4.DataBind();
}
OpHomeSelect ohs = new OpHomeSelect();
OperationResult obj5 = OperationManager.Singleton.executeOperation(ohs);
if ((obj5 == null) || (!obj5.Status))
{
return;
}
else
{
DbItem[] items = obj5.DbItems;
HomeDb[] homes = items.Cast <HomeDb>().ToArray();
GridView5.DataSource = obj5.DbItems;
GridView5.DataBind();
}
OpHistorySelect ohis = new OpHistorySelect();
OperationResult obj6 = OperationManager.Singleton.executeOperation(ohis);
if ((obj6 == null) || (!obj6.Status))
{
return;
}
else
{
DbItem[] items = obj6.DbItems;
HistoryDb[] histories = items.Cast <HistoryDb>().ToArray();
GridView6.DataSource = obj6.DbItems;
GridView6.DataBind();
}
OpPriceSelect ops = new OpPriceSelect();
OperationResult obj7 = OperationManager.Singleton.executeOperation(ops);
if ((obj7 == null) || (!obj7.Status))
{
return;
}
else
{
DbItem[] items = obj7.DbItems;
PriceDb[] prices = items.Cast <PriceDb>().ToArray();
GridView7.DataSource = obj7.DbItems;
GridView7.DataBind();
}
OpModernDaySelect omds = new OpModernDaySelect();
OperationResult obj8 = OperationManager.Singleton.executeOperation(omds);
if ((obj8 == null) || (!obj8.Status))
{
return;
}
else
{
DbItem[] items = obj8.DbItems;
ModernDayDb[] moderns = items.Cast <ModernDayDb>().ToArray();
GridView8.DataSource = obj8.DbItems;
GridView8.DataBind();
}
}
protected void Btn_Submit_Click(object sender, EventArgs e)
{
if (HF_MainCategory.Value != "")
{
if (HF_Delete.Value == "1")
{
// Al.CatPro.mainCategory = MainCategory.Text.Trim().ToString();
Al.CatPro.mainCategoryID = Convert.ToInt32(HF_MainCategory.Value);
HF_MainCategory.Value = "";
HF_Delete.Value = "";
string result = Al.MAinCategoryDelete();
GridView1.DataBind();
MainCategory.Text = "";
Button1.Text = "Save";
}
else
{
Al.CatPro.mainCategory = MainCategory.Text.Trim().ToString();
Al.CatPro.mainCategoryID = Convert.ToInt32(HF_MainCategory.Value);
HF_MainCategory.Value = "";
Al.MainCategoryEdit();
GridView1.DataBind();
Button1.Text = "Save";
}
}
else if (HF_subCategory1.Value != "")
{
if (HF_Delete.Value == "1")
{
// Al.CatPro.subCategory1 = SubCategory1.Text.Trim().ToString();
Al.CatPro.subCategory1_ID = Convert.ToInt32(HF_subCategory1.Value);
HF_subCategory1.Value = "";
HF_Delete.Value = "";
Al.SubCategory1Delete();
GridView2.DataBind();
SubCategory1.Text = "";
Button1.Text = "Save";
}
else
{
Al.CatPro.subCategory1 = SubCategory1.Text.Trim().ToString();
Al.CatPro.subCategory1_ID = Convert.ToInt32(HF_subCategory1.Value);
HF_subCategory1.Value = "";
Al.SubCategory1Edit();
GridView2.DataBind();
Button1.Text = "Save";
SubCategory1.Text = "";
}
}
else if (HF_subCategory2.Value != "")
{
if (HF_Delete.Value == "1")
{
Al.CatPro.subCategory2_ID = Convert.ToInt32(HF_subCategory2.Value);
HF_subCategory2.Value = "";
HF_Delete.Value = "";
Al.SubCategory2Delete();
GridView3.DataBind();
Button1.Text = "Save";
SubCategory2.Text = "";
}
else
{
Al.CatPro.subCategory2 = SubCategory2.Text.Trim().ToString();
Al.CatPro.subCategory2_ID = Convert.ToInt32(HF_subCategory2.Value);
HF_subCategory2.Value = "";
Al.SubCategory2Edit();
GridView3.DataBind();
Button1.Text = "Save";
SubCategory2.Text = "";
}
}
else if (HF_subCategory3.Value != "")
{
if (HF_Delete.Value == "1")
{
Al.CatPro.subCategory3_ID = Convert.ToInt32(HF_subCategory3.Value);
HF_subCategory3.Value = "";
HF_Delete.Value = "";
Al.SubCategory3Delete();
GridView4.DataBind();
Button1.Text = "Save";
SubCategory3.Text = "";
}
else
{
Al.CatPro.subCategory3 = SubCategory3.Text.Trim().ToString();
Al.CatPro.subCategory3_ID = Convert.ToInt32(HF_subCategory3.Value);
HF_subCategory3.Value = "";
Al.SubCategory3Edit();
GridView4.DataBind();
Button1.Text = "Save";
SubCategory3.Text = "";
}
}
else
{
if (MainCategory.Text != "")
{
Al.CatPro.mainCategory = MainCategory.Text.Trim().ToString();
string result = Al.CategoryInsert();
//LblMainCategory.Text = result;
GridView1.DataBind();
}
if (SubCategory1.Text != "")
{
Al.CatPro.subCategory1 = SubCategory1.Text.Trim().ToString();
string result = Al.SubCategory1Insert();
//LblSubCategory1.Text = result;
GridView2.DataBind();
}
if (SubCategory2.Text != "")
{
Al.CatPro.subCategory2 = SubCategory2.Text.Trim().ToString();
string result = Al.SubCategory2Insert();
//LblSubCategory2.Text = result;
GridView3.DataBind();
}
if (SubCategory3.Text != "")
{
Al.CatPro.subCategory3 = SubCategory3.Text.Trim().ToString();
string result = Al.SubCategory3Insert();
//LblSubCategory3.Text = result;
GridView4.DataBind();
}
}
}
private void BindGrid3()
{
mssqlconn.Close();
mssqlconn.Open();
string sql1 = "select * from edit_year where yearnme ='" + DropDownList1.SelectedValue.ToString() + "'";
SqlCommand cmd1 = new SqlCommand(sql1, mssqlconn);
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{
Label4.Text = dr1[0].ToString();
Label9.Text = DropDownList2.SelectedValue.ToString();
}
mssqlconn.Close();
string str = Label12.Text;
int q = str.Length;
int w = q - 12;
str = str.Substring(0, 10);
string str1 = Label13.Text;
int q1 = str1.Length;
int w1 = q - 12;
str1 = str1.Substring(0, 10);
if (Label3.Text != "sec" && Label2.Text == "hodemail")
{
if (Convert.ToInt32(Label4.Text) <= DateTime.Today.Year)
{
if (DropDownList2.SelectedIndex <= DateTime.Today.Month)
{
// ConnectionString to NorthWind Database.\
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// Create SQLDataSource.
SqlDataSource sqlDataSource = new SqlDataSource();
sqlDataSource.ID = "SqlDataSourceAlloRpt";
this.Page.Controls.Add(sqlDataSource);
// Bind ConnectionString to SQLDataSource.
sqlDataSource.ConnectionString = cs;
// Retrieve records with only 5 Columns from Employees table of NorthWind Database.
sqlDataSource.SelectCommand = "SELECT [psno], [name], [dept], [sec], SUM([totalhrs]) AS [totalhrs] FROM otentrytable WHERE [toamnt]='" + Label9.Text + "' AND [toayear]='" + Label8.Text + "' AND ([otentrydt])BETWEEN '" + str + "' AND '" + str1 + "' AND [toapprove]='" + Label5.Text + "' AND [dept]='" + Label1.Text + "' AND [sec]='" + Label3.Text + "' AND [previousot]='" + Label5.Text + "' GROUP BY [psno],[name],[dept],[sec] ORDER BY [psno] ASC";
Label4.Text = DropDownList1.SelectedValue.ToString();
// Bind SQLDataSource to GridView after retrieving the records.
GridView4.DataSource = sqlDataSource;
GridView4.DataBind();
GridView4.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
GridView3.Visible = false;
Button6.Enabled = true;
Button6.Visible = true;
norec.Visible = false;
Button4.Visible = false;
Button3.Visible = false;
Button5.Visible = false;
}
else
{
norec.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
Button4.Enabled = false;
Button3.Visible = false;
}
}
else
{
norec.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
Button3.Enabled = false;
}
}
else if (Label2.Text != "hodemail" && Label3.Text == "sec")
{
if (Convert.ToInt32(Label4.Text) <= DateTime.Today.Year)
{
if (DropDownList2.SelectedIndex <= DateTime.Today.Month)
{
if (DropDownList3.SelectedIndex == 0)
{
// ConnectionString to NorthWind Database.\
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// Create SQLDataSource.
SqlDataSource sqlDataSource = new SqlDataSource();
sqlDataSource.ID = "SqlDataSourceAlloRpt";
this.Page.Controls.Add(sqlDataSource);
// Bind ConnectionString to SQLDataSource.
sqlDataSource.ConnectionString = cs;
// Retrieve records with only 5 Columns from Employees table of NorthWind Database.
sqlDataSource.SelectCommand = "SELECT [psno], [name], [dept], [sec], SUM([totalhrs]) AS [totalhrs] FROM otentrytable WHERE [toamnt]='" + Label9.Text + "' AND [toayear]='" + Label8.Text + "' AND ([otentrydt])BETWEEN '" + str + "' AND '" + str1 + "' AND [toapprove]='" + Label5.Text + "' AND [dept]='" + Label1.Text + "' AND [previousot]='" + Label5.Text + "' GROUP BY [psno],[name],[dept],[sec] ORDER BY [psno] ASC";
Label4.Text = DropDownList1.SelectedValue.ToString();
// Bind SQLDataSource to GridView after retrieving the records.
GridView4.DataSource = sqlDataSource;
GridView4.DataBind();
GridView4.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
GridView3.Visible = false;
Button6.Enabled = true;
Button6.Visible = true;
norec.Visible = false;
Button4.Visible = false;
Button3.Visible = false;
Button5.Visible = false;
}
else
{
// ConnectionString to NorthWind Database.\
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// Create SQLDataSource.
SqlDataSource sqlDataSource = new SqlDataSource();
sqlDataSource.ID = "SqlDataSourceAlloRpt";
this.Page.Controls.Add(sqlDataSource);
// Bind ConnectionString to SQLDataSource.
sqlDataSource.ConnectionString = cs;
// Retrieve records with only 5 Columns from Employees table of NorthWind Database.
sqlDataSource.SelectCommand = "SELECT [psno], [name], [dept], [sec], SUM([totalhrs]) AS [totalhrs] FROM otentrytable WHERE [toamnt]='" + Label9.Text + "' AND [toayear]='" + Label8.Text + "' AND ([otentrydt])BETWEEN '" + str + "' AND '" + str1 + "' AND [toapprove]='" + Label5.Text + "' AND [dept]='" + Label1.Text + "' AND [sec]='" + DropDownList3.SelectedItem.ToString() + "' AND [previousot]='" + Label5.Text + "' GROUP BY [psno],[name],[dept],[sec] ORDER BY [psno] ASC";
Label4.Text = DropDownList1.SelectedValue.ToString();
// Bind SQLDataSource to GridView after retrieving the records.
GridView4.DataSource = sqlDataSource;
GridView4.DataBind();
GridView4.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
GridView3.Visible = false;
Button6.Enabled = true;
Button6.Visible = true;
norec.Visible = false;
Button4.Visible = false;
Button3.Visible = false;
Button5.Visible = false;
}
}
else
{
norec.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
Button4.Enabled = false;
Button3.Visible = false;
}
}
else
{
norec.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
Button3.Enabled = false;
}
}
else
{
norec.Visible = true;
GridView1.Visible = false;
GridView2.Visible = false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//comprobar permiso para ver toda la info
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][5].ToString() == "False")
{
Response.Redirect("Inicio.aspx");
}
//permiso para anular pagos
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][2].ToString() == "False")
{
GridView4.Visible = false;
Label3.Visible = false;
Button8.Visible = false;
}
else
{
GridView4.Visible = true;
Label3.Visible = true;
Button8.Visible = true;
}
//fin de permiso para anular pagos
//permiso para cancelar solicitud
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][6].ToString() == "False")
{
Button2.Visible = false;
}
//fin de permiso
//permiso para precalificar
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][7].ToString() == "False")
{
Button1.Visible = false;
}
//permiso para aprobar prestamo
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][6].ToString() == "False")
{
Button2.Visible = false;
}
//permiso para ver garatias
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][10].ToString() == "False")
{
GridView2.Visible = false;
}
//genrar cheque
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][10].ToString() == "False")
{
Button7.Visible = false;
//permiso para genrar abonos si puede genrar cheque tambien abonos
Button9.Visible = false;
}
Button5.Visible = false;
string valor2 = Request.QueryString["Valor"];
int idprestamo = Convert.ToInt32(valor2);
string tipopre = presta.InfoTabUNO(idprestamo).Rows[0][0].ToString();
string desembolso = presta.InfoTabUNO(idprestamo).Rows[0][1].ToString();
//Precalificar
if (tipopre == "False" && desembolso == "False")
{
Button1.Text = "Precalificar préstamo";
DivHistoPagos.Visible = false;
InformaPagos.Visible = true;
ContratoAviso.Visible = true;
llenar();
}
//desembolsar
if (tipopre == "True" && desembolso == "False")
{
//permiso para desembolsar
if (permiso.CONOCERPERMISO(Convert.ToInt32(Session["idempleado"])).Rows[0][9].ToString() == "False")
{
Button1.Visible = false;
}
Button1.Text = "Realizar desembolso";
DivHistoPagos.Visible = false;
InformaPagos.Visible = true;
ContratoAviso.Visible = true;
llenar();
}
if (tipopre == "True" && desembolso == "True")
{
DivHistoPagos.Visible = true;
InformaPagos.Visible = false;
ContratoAviso.Visible = false;
contra.Visible = true;
if (!IsPostBack)
{
InfoFULLContratoTableAdapter con = new InfoFULLContratoTableAdapter();
DataTable tabla = con.GetDataInfoFullContrato(idprestamo);
ReportViewer1.LocalReport.EnableExternalImages = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/ReportContrato.rdlc");
ReportDataSource fuente = new ReportDataSource("DataSet1", tabla);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(fuente);
ReportViewer1.LocalReport.Refresh();
}
llenar();
LabelCuotasPendientes.Text = r.CuotasPendientesInfoPresta(idprestamo).Rows[0][0].ToString();
string fecha1 = DateTime.Now.ToString("dd/MM/yyyy");
// DateTime fecha = DateTime.ParseExact(fecha1, "dd/MM/yyyy", null);
DateTime fecha = Convert.ToDateTime(fecha1);
if (r.ProximaFechaInfoPResta(fecha, idprestamo).Rows.Count != 0)
{
LabelFechaPago.Text = r.ProximaFechaInfoPResta(fecha, idprestamo).Rows[0][0].ToString();
}
if (r.SaldoTotalInfoPresta(idprestamo).Rows.Count != 0)
{
LabelSaldoPendiente.Text = r.SaldoTotalInfoPresta(idprestamo).Rows[0][0].ToString();
LabelSaldo.Text = r.SaldoTotalInfoPresta(idprestamo).Rows[0][0].ToString();
}
if (r.SaldoCapitalInfoPresta(idprestamo).Rows.Count != 0)
{
LabelSaldoCapital.Text = r.SaldoCapitalInfoPresta(idprestamo).Rows[0][0].ToString();
}
if (abo.InfoAbono(idprestamo).Rows.Count != 0)
{
LabelAbono.Text = abo.InfoAbono(idprestamo).Rows[0][0].ToString();
}
else
{
LabelAbono.Text = "Q.00";
}
Button2.Visible = false;
Button1.Visible = false;
GridView3.DataSource = presta.TablaPagosTab(idprestamo);
GridView3.DataBind();
Button5.Visible = true;
Button6.Visible = true;
Button7.Visible = true;
Button9.Visible = true;
GridView4.DataSource = presta.TabPagosEfectuados(idprestamo);
GridView4.Columns[0].Visible = false;
GridView4.DataBind();
if (presta.TOTAlPagosTab4(idprestamo).Rows[0][0].ToString() != "0")
{
Label3.Visible = true;
textoc.Visible = true;
Label3.Text = "Q. " + presta.TOTAlPagosTab4(idprestamo).Rows[0][0].ToString();
Button8.Visible = true;
}
}
//infotab2
LabelNombreTab2.Text = presta.InfoTab2(idprestamo).Rows[0][0].ToString();
LabelCuI.Text = presta.InfoTab2(idprestamo).Rows[0][1].ToString();
LabelEstadoCivil.Text = presta.InfoTab2(idprestamo).Rows[0][2].ToString();
LabelProfesion.Text = presta.InfoTab2(idprestamo).Rows[0][3].ToString();
LabelTelefonos.Text = presta.InfoTab2(idprestamo).Rows[0][4].ToString();
LabelDireccionPer.Text = presta.InfoTab2(idprestamo).Rows[0][5].ToString();
LabelDomicilio.Text = presta.InfoTab2(idprestamo).Rows[0][6].ToString();
LabelLugarTrabajo.Text = presta.InfoTab2(idprestamo).Rows[0][7].ToString();
LabelDirTrabajo.Text = presta.InfoTab2(idprestamo).Rows[0][8].ToString();
LabelTelTrabajo.Text = presta.InfoTab2(idprestamo).Rows[0][9].ToString();
LabelTiempoLaborar.Text = presta.InfoTab2(idprestamo).Rows[0][10].ToString();
LabelObservacionesTrabajo.Text = presta.InfoTab2(idprestamo).Rows[0][11].ToString();
LabelMontoPre.Text = presta.InfoTab2(idprestamo).Rows[0][12].ToString();
LabelDeudaTotalPre.Text = presta.InfoTab2(idprestamo).Rows[0][13].ToString();
LabelPeriodPre.Text = presta.InfoTab2(idprestamo).Rows[0][14].ToString();
LabelGestorPre.Text = presta.InfoTab2(idprestamo).Rows[0][15].ToString();
LabelOficinaPre.Text = presta.InfoTab2(idprestamo).Rows[0][16].ToString();
//referencias tab2
GridView1.DataSource = ver.Listadore(Convert.ToInt32(presta.InfoTab2(idprestamo).Rows[0][17].ToString()));
GridView1.DataBind();
//Garantias tab3
GridView2.DataSource = presta.GarantiasPrestamo(idprestamo);
GridView2.DataBind();
}
void PostTreatment()
{
GridView4.DataSource = ClassDataManager.LoadTreatments(illnessc);
GridView4.DataBind();
}
protected void odswierz()
{
string idDzialu = (string)Session["id_dzialu"];
id_dzialu.Text = (string)Session["txt_dzialu"];
try
{
cm.log.Info("okrr: start generowania tabeli 2");
DataTable tabelka01 = dr.generuj_dane_do_tabeli_wierszy2018(Date1.Date, Date2.Date, (string)Session["id_dzialu"], 2, 20, 20, tenPlik);
Session["tabelka002"] = tabelka01;
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " Generowanie tabel 2" + ex.Message);
}
try
{
cm.log.Info("okrr: start generowania tabeli 1");
DataTable tabela01 = Tabela01(int.Parse(idDzialu));
Session["tabelka001"] = tabela01;
GridView1.DataSource = null;
GridView1.DataSourceID = null;
GridView1.DataSource = tabela01;
GridView1.DataBind();
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " Generowanie tabel 1" + ex.Message);
}
try
{
cm.log.Info("okrr: start generowania tabeli 3");
DataTable tabela03 = Tabela03(int.Parse(idDzialu));
Session["tabelka003"] = tabela03;
GridView3.DataSource = null;
GridView3.DataSourceID = null;
GridView3.DataSource = tabela03;
GridView3.DataBind();
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " Generowanie tabel 3" + ex.Message);
}
try
{
cm.log.Info("okrr: start generowania tabeli 4");
DataTable tabela04 = Tabela04(int.Parse(idDzialu));
Session["tabelka004"] = tabela04;
GridView4.DataSource = null;
GridView4.DataSourceID = null;
GridView4.DataSource = tabela04;
GridView4.DataBind();
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " Generowanie tabel 4" + ex.Message);
}
try
{
cm.log.Info("okrr: start generowania tabeli 5");
DataTable tabela05 = Tabela05(int.Parse(idDzialu));
Session["tabelka005"] = tabela05;
GridView5.DataSource = null;
GridView5.DataSourceID = null;
GridView5.DataSource = tabela05;
GridView5.DataBind();
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " Generowanie tabel 5" + ex.Message);
}
try
{
Label11.Visible = cl.debug(int.Parse(idDzialu));
infoLabel2.Visible = cl.debug(int.Parse(idDzialu));
infoLabel3.Visible = cl.debug(int.Parse(idDzialu));
infoLabel4.Visible = cl.debug(int.Parse(idDzialu));
}
catch
{
Label11.Visible = false;
infoLabel2.Visible = false;
infoLabel3.Visible = false;
infoLabel4.Visible = false;
}
Label3.Text = cl.nazwaSadu((string)Session["id_dzialu"]);
}
public void getgrid()
{
try
{
dt0 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
d1 = dt0.ToString("MM/dd/yyyy");
d2 = dt2.ToString("MM/dd/yyyy");
DateTime lowdate = GetLowDate(Convert.ToDateTime(d1));
DateTime highdate = GetHighDate(Convert.ToDateTime(d2));
DateTime ylowdate = lowdate.AddDays(-1);
DateTime yhighdate = highdate.AddDays(-1);
SqlConnection con = new SqlConnection(connStr);
string strAM, strPM, strpam, strppm;
DataTable Report = new DataTable();
DataTable Sample = new DataTable();
DataTable Reportpm = new DataTable();
DataTable Reportall = new DataTable();
string PLANTC = ddl_Plantname.SelectedItem.Text;
string[] words = PLANTC.Split('_');
string plantcode = words[0];
//CC WISE
if (ddltype.SelectedItem.Value == "0" || ddltype.SelectedItem.Value == "2")
{
Sample.Columns.Add("SAMP&DIP MILK");
Report.Columns.Add("Route NAME");
Report.Columns.Add("AM MILK").DataType = typeof(double);
Report.Columns.Add("(+/-)").DataType = typeof(double);
//Report.Columns.Add("KG Fat").DataType = typeof(double);
//Report.Columns.Add("Fat(+/-)").DataType = typeof(double);
//Report.Columns.Add("KG Snf").DataType = typeof(double);
//Report.Columns.Add("Snf(+/-)").DataType = typeof(double);
Report.Columns.Add("D S Time");
Report.Columns.Add("D E Time");
Report.Columns.Add("MBRT");
Report.Columns.Add("ACIDITY");
Report.Columns.Add("AM KMs");
Report.Columns.Add("AM TP Cost");
Reportpm.Columns.Add("Route NAME");
Reportpm.Columns.Add("AM MILK").DataType = typeof(double);
Reportpm.Columns.Add("(+/-)").DataType = typeof(double);
Reportpm.Columns.Add("DSTime");
Reportpm.Columns.Add("DETime");
Reportpm.Columns.Add("MBRT");
Reportpm.Columns.Add("ACIDITY");
Reportpm.Columns.Add("PM KMs");
Reportpm.Columns.Add("PM TP Cost");
Reportall.Columns.Add("Route NAME");
Reportall.Columns.Add("MILK").DataType = typeof(double);
Reportall.Columns.Add("(+/-)").DataType = typeof(double);
Reportall.Columns.Add("TP Perday");
Reportall.Columns.Add("TP Cost");
// route details
string route = "Select Route_ID,Route_Name, PLANT_CODE from Route_Master WHERE Company_code='" + ccode + "' AND PLANT_CODE='" + plantcode + "' ORDER BY Route_ID ";
DataTable dtr = new DataTable();
SqlDataAdapter dar = new SqlDataAdapter(route, con);
dar.Fill(dtr);
strAM = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') AND (Sessions = 'AM') GROUP BY Plant_Code, Route_id, Prdate, Sessions ORDER BY Prdate, Route_id";
DataTable dtam = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(strAM, con);
da1.Fill(dtam);
strPM = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') AND (Sessions = 'PM') GROUP BY Plant_Code, Route_id, Prdate, Sessions ORDER BY Prdate, Route_id";
DataTable dtpm = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(strPM, con);
da.Fill(dtpm);
string ysterdayam = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') AND (Sessions = 'AM') GROUP BY Plant_Code, Route_id, Prdate, Sessions ORDER BY Prdate, Route_id";
DataTable dtyyam = new DataTable();
SqlDataAdapter dayy1 = new SqlDataAdapter(ysterdayam, con);
dayy1.Fill(dtyyam);
string ysterdaypm = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') AND (Sessions = 'PM') GROUP BY Plant_Code, Route_id, Prdate, Sessions ORDER BY Prdate, Route_id";
DataTable dtyypm = new DataTable();
SqlDataAdapter dayy2 = new SqlDataAdapter(ysterdaypm, con);
dayy2.Fill(dtyypm);
string strall = "SELECT SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') GROUP BY Plant_Code, Route_id ORDER BY Route_id";
DataTable dtall = new DataTable();
SqlDataAdapter daall = new SqlDataAdapter(strall, con);
daall.Fill(dtall);
string stryall = "SELECT SUM(Milk_ltr) AS MILKLTR, Plant_Code, Route_id, SUM(Milk_kg) AS KG FROM Procurementimport WHERE (Plant_Code = '" + plantcode + "') AND (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') GROUP BY Plant_Code, Route_id ORDER BY Route_id";
DataTable dtyall = new DataTable();
SqlDataAdapter dayall = new SqlDataAdapter(stryall, con);
dayall.Fill(dtyall);
string otherdtls = "SELECT Tid, VehicleSettime, VehicleInttime, Plant_code, Route_id, MBRT, Date, Session, acidity FROM RouteTimeMaintain WHERE (Plant_Code = '" + plantcode + "') AND (Date BETWEEN '" + lowdate + "' AND '" + highdate + "')";
DataTable dtothers = new DataTable();
SqlDataAdapter daothers = new SqlDataAdapter(otherdtls, con);
daothers.Fill(dtothers);
if (dtr.Rows.Count > 0)
{
foreach (DataRow dr in dtr.Rows)
{
DataRow newrow = Report.NewRow();
string routeid = dr["Route_ID"].ToString();
string routename = dr["Route_Name"].ToString();
newrow["Route NAME"] = routename;
string plantScode = dr["PLANT_CODE"].ToString();
foreach (DataRow dram in dtam.Select("Route_id='" + routeid + "'"))
{
double yesterdayammilk = 0;
foreach (DataRow dryam in dtyyam.Select("Route_id='" + routeid + "'"))
{
double.TryParse(dryam["MILKLTR"].ToString(), out yesterdayammilk);
}
foreach (DataRow dro in dtothers.Select("Route_id='" + routeid + "' AND Session='AM'"))
{
newrow["D S Time"] = dro["VehicleSettime"].ToString();
newrow["D E Time"] = dro["VehicleInttime"].ToString();
newrow["MBRT"] = dro["MBRT"].ToString();
newrow["ACIDITY"] = dro["acidity"].ToString();
}
double ammilk = 0;
double.TryParse(dram["MILKLTR"].ToString(), out ammilk);
double diffmilk = ammilk - yesterdayammilk;
newrow["AM MILK"] = ammilk;
newrow["(+/-)"] = Math.Round(diffmilk, 0);
newrow["AM KMs"] = "";
newrow["AM TP Cost"] = "";
Report.Rows.Add(newrow);
}
DataRow newrowPM = Reportpm.NewRow();
newrowPM["Route NAME"] = routename;
foreach (DataRow drpm in dtpm.Select("Route_id='" + routeid + "'"))
{
double yesterdaypmmilk = 0;
foreach (DataRow drypm in dtyyam.Select("Route_id='" + routeid + "'"))
{
double.TryParse(drypm["MILKLTR"].ToString(), out yesterdaypmmilk);
}
foreach (DataRow dro in dtothers.Select("Route_id='" + routeid + "' AND Session='PM'"))
{
newrowPM["DSTime"] = dro["VehicleSettime"].ToString();
newrowPM["DETime"] = dro["VehicleInttime"].ToString();
newrowPM["MBRT"] = dro["MBRT"].ToString();
newrowPM["ACIDITY"] = dro["acidity"].ToString();
}
double pmmilk = 0;
double.TryParse(drpm["MILKLTR"].ToString(), out pmmilk);
double diffmilk = pmmilk - yesterdaypmmilk;
newrowPM["AM MILK"] = pmmilk;
newrowPM["(+/-)"] = diffmilk;
newrowPM["PM KMs"] = "";
newrowPM["PM TP Cost"] = "";
Reportpm.Rows.Add(newrowPM);
}
DataRow newall = Reportall.NewRow();
newall["Route NAME"] = routename;
foreach (DataRow drall in dtall.Select("Route_id='" + routeid + "'"))
{
double yesterdayallmilk = 0;
foreach (DataRow dryall in dtyall.Select("Route_id='" + routeid + "'"))
{
double.TryParse(dryall["MILKLTR"].ToString(), out yesterdayallmilk);
}
double milk = 0;
double.TryParse(drall["MILKLTR"].ToString(), out milk);
double diffmilk = milk - yesterdayallmilk;
newall["MILK"] = milk;
newall["(+/-)"] = Math.Round(diffmilk, 0);
newall["TP Perday"] = "9:30";
newall["TP Cost"] = "11:00";
Reportall.Rows.Add(newall);
}
}
DataRow newTotal = Report.NewRow();
newTotal["Route NAME"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
if (val == 0.0)
{
}
else
{
newTotal[dc.ToString()] = val;
}
}
}
Report.Rows.Add(newTotal);
DataRow newTotalpm = Reportpm.NewRow();
newTotalpm["Route NAME"] = "Total";
double valpm = 0.0;
foreach (DataColumn dc in Reportpm.Columns)
{
if (dc.DataType == typeof(Double))
{
valpm = 0.0;
double.TryParse(Reportpm.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valpm);
if (valpm == 0.0)
{
}
else
{
newTotalpm[dc.ToString()] = valpm;
}
}
}
Reportpm.Rows.Add(newTotalpm);
DataRow newTotalall = Reportall.NewRow();
newTotalall["Route NAME"] = "Total";
double valall = 0.0;
foreach (DataColumn dc in Reportall.Columns)
{
if (dc.DataType == typeof(Double))
{
valall = 0.0;
double.TryParse(Reportall.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valall);
if (valall == 0.0)
{
}
else
{
newTotalall[dc.ToString()] = valall;
}
}
}
Reportall.Rows.Add(newTotalall);
}
}
// PLANT WISE
else
{
Sample.Columns.Add("SAMP&DIP MILK");
Report.Columns.Add("CC NAME");
Report.Columns.Add("AM MILK").DataType = typeof(double);
Report.Columns.Add("(+/-)").DataType = typeof(double);
Report.Columns.Add("D S Time");
Report.Columns.Add("D E Time");
Report.Columns.Add("MBRT");
Report.Columns.Add("ACIDITY");
Report.Columns.Add("AM KMs");
Report.Columns.Add("AM TP Cost");
Reportpm.Columns.Add("CC NAME");
Reportpm.Columns.Add("AM MILK").DataType = typeof(double);
Reportpm.Columns.Add("(+/-)").DataType = typeof(double);
Reportpm.Columns.Add("DSTime");
Reportpm.Columns.Add("DETime");
Reportpm.Columns.Add("MBRT");
Reportpm.Columns.Add("ACIDITY");
Reportpm.Columns.Add("PM KMs");
Reportpm.Columns.Add("PM TP Cost");
Reportall.Columns.Add("CC NAME");
Reportall.Columns.Add("MILK").DataType = typeof(double);
Reportall.Columns.Add("(+/-)").DataType = typeof(double);
Reportall.Columns.Add("TP Perday");
Reportall.Columns.Add("TP Cost");
// Plant details
string route = "Select Plant_Code, Plant_Name from Plant_Master WHERE Company_Code='" + ccode + "' ORDER BY Plant_Code ";
DataTable dtr = new DataTable();
SqlDataAdapter dar = new SqlDataAdapter(route, con);
dar.Fill(dtr);
strAM = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') AND (Sessions = 'AM') GROUP BY Plant_Code, Prdate, Sessions ORDER BY Prdate,Plant_Code ";
DataTable dtam = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(strAM, con);
da1.Fill(dtam);
strPM = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') AND (Sessions = 'PM') GROUP BY Plant_Code, Prdate, Sessions ORDER BY Prdate, Plant_Code";
DataTable dtpm = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(strPM, con);
da.Fill(dtpm);
string ysterdayam = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') AND (Sessions = 'AM') GROUP BY Plant_Code, Prdate, Sessions ORDER BY Prdate, Plant_Code";
DataTable dtyyam = new DataTable();
SqlDataAdapter dayy1 = new SqlDataAdapter(ysterdayam, con);
dayy1.Fill(dtyyam);
string ysterdaypm = "SELECT Sessions, SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG, Prdate FROM Procurementimport WHERE (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') AND (Sessions = 'PM') GROUP BY Plant_Code, Prdate, Sessions ORDER BY Prdate, Plant_Code";
DataTable dtyypm = new DataTable();
SqlDataAdapter dayy2 = new SqlDataAdapter(ysterdaypm, con);
dayy2.Fill(dtyypm);
string strall = "SELECT SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG FROM Procurementimport WHERE (Prdate BETWEEN '" + lowdate + "' AND '" + highdate + "') GROUP BY Plant_Code ORDER BY Plant_Code";
DataTable dtall = new DataTable();
SqlDataAdapter daall = new SqlDataAdapter(strall, con);
daall.Fill(dtall);
string stryall = "SELECT SUM(Milk_ltr) AS MILKLTR, Plant_Code, SUM(Milk_kg) AS KG FROM Procurementimport WHERE (Prdate BETWEEN '" + ylowdate + "' AND '" + yhighdate + "') GROUP BY Plant_Code ORDER BY Plant_Code";
DataTable dtyall = new DataTable();
SqlDataAdapter dayall = new SqlDataAdapter(stryall, con);
dayall.Fill(dtyall);
string otherdtls = "SELECT Tid, VehicleSettime, VehicleInttime, Plant_code, Route_id, MBRT, Date, Session, acidity FROM RouteTimeMaintain WHERE (Date BETWEEN '" + lowdate + "' AND '" + highdate + "')";
DataTable dtothers = new DataTable();
SqlDataAdapter daothers = new SqlDataAdapter(otherdtls, con);
daothers.Fill(dtothers);
if (dtr.Rows.Count > 0)
{
foreach (DataRow dr in dtr.Rows)
{
DataRow newrow = Report.NewRow();
string routeid = dr["Plant_Code"].ToString();
string routename = dr["Plant_Name"].ToString();
newrow["CC NAME"] = routename;
string plantScode = dr["Plant_Code"].ToString();
foreach (DataRow dram in dtam.Select("Plant_code='" + routeid + "'"))
{
double yesterdayammilk = 0;
foreach (DataRow dryam in dtyyam.Select("Plant_code='" + routeid + "'"))
{
double.TryParse(dryam["MILKLTR"].ToString(), out yesterdayammilk);
}
foreach (DataRow dro in dtothers.Select("Plant_code='" + routeid + "' AND Session='AM'"))
{
newrow["D S Time"] = dro["VehicleSettime"].ToString();
newrow["D E Time"] = dro["VehicleInttime"].ToString();
newrow["MBRT"] = dro["MBRT"].ToString();
newrow["ACIDITY"] = dro["acidity"].ToString();
}
double ammilk = 0;
double.TryParse(dram["MILKLTR"].ToString(), out ammilk);
double diffmilk = ammilk - yesterdayammilk;
newrow["AM MILK"] = ammilk;
newrow["(+/-)"] = Math.Round(diffmilk, 0);
newrow["AM KMs"] = "";
newrow["AM TP Cost"] = "";
Report.Rows.Add(newrow);
}
DataRow newrowPM = Reportpm.NewRow();
newrowPM["CC NAME"] = routename;
foreach (DataRow drpm in dtpm.Select("Plant_code='" + routeid + "'"))
{
double yesterdaypmmilk = 0;
foreach (DataRow drypm in dtyyam.Select("Plant_code='" + routeid + "'"))
{
double.TryParse(drypm["MILKLTR"].ToString(), out yesterdaypmmilk);
}
foreach (DataRow dro in dtothers.Select("Plant_code='" + routeid + "' AND Session='PM'"))
{
newrowPM["DSTime"] = dro["VehicleSettime"].ToString();
newrowPM["DETime"] = dro["VehicleInttime"].ToString();
newrowPM["MBRT"] = dro["MBRT"].ToString();
newrowPM["ACIDITY"] = dro["acidity"].ToString();
}
double pmmilk = 0;
double.TryParse(drpm["MILKLTR"].ToString(), out pmmilk);
double diffmilk = pmmilk - yesterdaypmmilk;
newrowPM["AM MILK"] = pmmilk;
newrowPM["(+/-)"] = diffmilk;
newrowPM["PM KMs"] = "";
newrowPM["PM TP Cost"] = "";
Reportpm.Rows.Add(newrowPM);
}
DataRow newall = Reportall.NewRow();
newall["CC NAME"] = routename;
foreach (DataRow drall in dtall.Select("Plant_code='" + routeid + "'"))
{
double yesterdayallmilk = 0;
foreach (DataRow dryall in dtyall.Select("Plant_code='" + routeid + "'"))
{
double.TryParse(dryall["MILKLTR"].ToString(), out yesterdayallmilk);
}
double milk = 0;
double.TryParse(drall["MILKLTR"].ToString(), out milk);
double diffmilk = milk - yesterdayallmilk;
newall["MILK"] = milk;
newall["(+/-)"] = Math.Round(diffmilk, 0);
newall["TP Perday"] = "";
newall["TP Cost"] = "";
Reportall.Rows.Add(newall);
}
}
DataRow newTotal = Report.NewRow();
newTotal["CC NAME"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
if (val == 0.0)
{
}
else
{
newTotal[dc.ToString()] = val;
}
}
}
Report.Rows.Add(newTotal);
DataRow newTotalpm = Reportpm.NewRow();
newTotalpm["CC NAME"] = "Total";
double valpm = 0.0;
foreach (DataColumn dc in Reportpm.Columns)
{
if (dc.DataType == typeof(Double))
{
valpm = 0.0;
double.TryParse(Reportpm.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valpm);
if (valpm == 0.0)
{
}
else
{
newTotalpm[dc.ToString()] = valpm;
}
}
}
Reportpm.Rows.Add(newTotalpm);
DataRow newTotalall = Reportall.NewRow();
newTotalall["CC NAME"] = "Total";
double valall = 0.0;
foreach (DataColumn dc in Reportall.Columns)
{
if (dc.DataType == typeof(Double))
{
valall = 0.0;
double.TryParse(Reportall.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valall);
if (valall == 0.0)
{
}
else
{
newTotalall[dc.ToString()] = valall;
}
}
}
Reportall.Rows.Add(newTotalall);
}
}
DataRow smprow = Sample.NewRow();
smprow["SAMP&DIP MILK"] = "1";
Sample.Rows.Add(smprow);
GridView1.DataSource = Report;
GridView1.DataBind();
GridView2.DataSource = Reportpm;
GridView2.DataBind();
GridView3.DataSource = Reportall;
GridView3.DataBind();
GridView4.DataSource = Sample;
GridView4.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
lblmsg.Visible = true;
}
}
protected void BtnAll_Click(object sender, EventArgs e)
{
GridView4.DataBind();
}
public override void DataBind()
{
if (string.IsNullOrEmpty(Request.QueryString["mode"]))
{
string StrSql = @" Select M.MasterID, M.Term, M.StudyYear, D.DeptName, D.DeptCode, SD.MainSubDeptCode, SD.MainSubDeptName, MD.MainDeptCode, MD.MainDeptName
From ftYearMaster M Inner Join Department D On M.DeptCode = D.DeptCode
Inner Join MainSubDepartment SD On D.MainSubDeptCode = SD.MainSubDeptCode
Inner Join MainDepartment MD ON SD.MainDeptCode = MD.MainDeptCode
Where M.DelFlag = 0 ";
if (ddlSearchYear.SelectedIndex != 0)
{
StrSql += " And M.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
if (ddlSearchMainDept.SelectedIndex != 0)
{
StrSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "'";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
StrSql += " And SD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "'";
}
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql += " And D.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (txtSearch.Text != "")
{
StrSql += " And M.Term Like '%" + txtSearch.Text + "%' Or M.StudyYear Like '%" + txtSearch.Text + "%' ";
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By M.StudyYear Asc, M.Term "));
GridView1.DataSource = dv;
GridView1.DataBind();
lblSearchTotal.InnerText = dv.Count.ToString();
}
else
{
if (Request.QueryString["mode"] == "5")
{
string StrSql = @" Select M.MasterID, M.Term, M.StudyYear, MD.ItemID, IsNull(MD.SetMoney, 0) SetMoney, IsNull(MD.Price, 0 ) Price,
IsNull(MD.Amount, 0) Amount, IsNull(MD.SetMoney, 0) + (IsNull(MD.Price, 0) * IsNull(MD.Amount, 0)) As TotalAmount,
C.ClassName, BD.BudgetDetailTypeName, BT.BudgetTypeName
From ftYearMaster M Inner Join ftYearMasterDetail MD On M.MasterID = MD.MasterID
Inner Join ftYearClass C On MD.ClassID = C.ClassID
Inner Join ftYearBudgetTypeDetail BD On MD.BudgetDetailTypeID = BD.BudgetDetailTypeID
Inner JOin ftYearBudgetType BT On BD.BudgetTypeID = BT.BudgetTypeID
Inner Join Department D On M.DeptCode = D.DeptCode
Inner Join MainSubDepartment SD On D.MainSubDeptCode = SD.MainSubDeptCode
Inner Join MainDepartment MDt ON SD.MainDeptCode = MDt.MainDeptCode
Where M.DelFlag = 0 And M.StudyYear = '" + ddlSearchStudyYear2.SelectedValue + "' And M.Term = '" + ddlSearchTerm.SelectedValue + "' And M.MasterID = '" + Request.QueryString["mid"] + "' ";
if (ddlSearchClass.SelectedIndex != 0)
{
StrSql += " And C.ClassID = '" + ddlSearchClass.SelectedValue + "' ";
}
if (ddlSearchBudgetType.SelectedIndex != 0)
{
StrSql += " And BT.BudgetTypeID = '" + ddlSearchBudgetType.SelectedValue + "' ";
}
if (ddlSearchMainDept2.SelectedIndex != 0)
{
StrSql += " And MDt.MainDeptCode = '" + ddlSearchMainDept2.SelectedValue + "' ";
}
if (ddlSearchMainSubDept2.SelectedIndex != 0)
{
StrSql += " And SD.MainSubDeptCode = '" + ddlSearchMainSubDept2.SelectedValue + "' ";
}
if (ddlSearchDept2.SelectedIndex != 0)
{
StrSql += " And D.DeptCode = '" + ddlSearchDept2.SelectedValue + "'";
}
if (txtSearch2.Text != "")
{
StrSql += " And (C.ClassName Like '%" + txtSearch2.Text + "%' Or BD.BudgetDetailTypeName Like '%" + txtSearch2.Text + "%' Or BT.BudgetTypeName Like '%" + txtSearch2.Text + "%') ";
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By M.StudyYear Asc, M.Term, C.ClassID, BT.BudgetTypeID, BD.BudgetDetailTypeID"));
GridView2.DataSource = dv;
GridView2.DataBind();
lblSearchTotal2.InnerText = dv.Count.ToString();
StrSql = @" Select IsNull(Sum(IsNull(MD.Amount, 0)), 0) TotalStudent, IsNull(Sum(IsNull(MD.Price, 0)), 0) TotalPrice,
IsNull(Sum((IsNull(MD.Amount, 0) * IsNull(MD.Price, 0))), 0) SumTotal
From ftYearMaster M Inner Join ftYearMasterDetail MD On M.MasterID = MD.MasterID
Where M.DelFlag = 0 And M.MasterID = '{0}' And MD.BudgetDetailTypeID = 5 " ;
DataView dvMoneyActivity = Conn.Select(string.Format(StrSql, Request.QueryString["mid"]));
if (dvMoneyActivity.Count > 0)
{
lblTotalStudent.Text = Convert.ToInt32(dvMoneyActivity[0]["TotalStudent"]).ToString("#,##0");
lblTotalPrice.Text = Convert.ToInt32(dvMoneyActivity[0]["TotalPrice"]).ToString("#,##0.00");
lblSumTotal.Text = Convert.ToInt32(dvMoneyActivity[0]["SumTotal"]).ToString("#,##0.00");
decimal SumToal = 0;
decimal SetMoneyAcType = 0;
if ((!string.IsNullOrEmpty(lblSumTotal.Text)) && (!string.IsNullOrEmpty(txtSetMoneyAcType.Text)))
{
SumToal = Convert.ToDecimal(lblSumTotal.Text);
SetMoneyAcType = Convert.ToDecimal(txtSetMoneyAcType.Text);
}
lblSumTotalAll.Text = (SumToal + SetMoneyAcType).ToString("#,##0.00");
}
}
else
{
if (Request.QueryString["mode"] == "6")
{
string StrSql = @" Select M.MasterID, M.Term, M.StudyYear, MD.ItemID, IsNull(MD.SetMoney, 0) SetMoney, IsNull(MD.Price, 0 ) Price,
IsNull(MD.Amount, 0) Amount, IsNull(MD.SetMoney, 0) + (IsNull(MD.Price, 0) * IsNull(MD.Amount, 0)) As TotalAmount,
C.ClassName, BD.BudgetDetailTypeName, BT.BudgetTypeName
From ftYearMaster M Inner Join ftYearMasterDetail MD On M.MasterID = MD.MasterID
Inner Join ftYearClass C On MD.ClassID = C.ClassID
Inner Join ftYearBudgetTypeDetail BD On MD.BudgetDetailTypeID = BD.BudgetDetailTypeID
Inner JOin ftYearBudgetType BT On BD.BudgetTypeID = BT.BudgetTypeID
Where M.DelFlag = 0 And M.StudyYear = '" + lblStudyYear.Text + "' And M.Term = '" + lblTerm.Text + "' And M.MasterID = '" + Request.QueryString["mid"] + "' ";
DataView dv = Conn.Select(string.Format(StrSql + " Order By M.StudyYear Asc, M.Term, C.ClassID, BT.BudgetTypeID, BD.BudgetDetailTypeID"));
GridView4.DataSource = dv;
GridView4.DataBind();
GridView4.Visible = true;
}
}
}
}
protected void ButtonUpdateBook_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception)
{
con.Close();
return;
throw;
}
if (string.IsNullOrEmpty(TextBoxBookDes.Text))
{
TextBoxBookDes.Text = "NULL";
}
else
{
TextBoxBookDes.Text = "'" + TextBoxBookDes.Text + "'";
}
SqlCommand c1 = new SqlCommand("Update Item_T set ItemName='" + TextBoxBookName.Text + "', ItemDescription=" + TextBoxBookDes.Text +
", Price=" + TextBoxBookPrice.Text + ", TaxPercentage=" + TextBoxBookTaxPer.Text +
", ISTaxNumber=" + TextBoxBookSupTax.Text + " where Barcode=" + TextBoxFirstBookBarcode.Text, con);
c1.ExecuteNonQuery();
SqlCommand c4 = new SqlCommand("Update Book_T set Author='" + TextBoxBookAuthor.Text + "', Genre='" + TextBoxBookGenre.Text + "' where BBarcode=" + TextBoxFirstBookBarcode.Text, con);
c4.ExecuteNonQuery();
DataSet ds = new DataSet();
string sqlstr = "select * from Item_T where Barcode=" + TextBoxFirstBookBarcode.Text;
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds);
GridView4.DataSource = ds;
GridView4.DataBind();
DataSet ds1 = new DataSet();
string sqlstr1 = "select * from Book_T where BBarcode=" + TextBoxFirstBookBarcode.Text;
SqlDataAdapter da1 = new SqlDataAdapter(sqlstr1, con);
da1.Fill(ds1);
GridView5.DataSource = ds1;
GridView5.DataBind();
con.Close();
bookUpdate.Visible = false;
newBook.Visible = true;
}
protected void change(object sender, GridViewCommandEventArgs e)
{
try
{
DataSet flagds = new DataSet();
int row = Convert.ToInt32(e.CommandArgument);
// GridView3.Rows[row].Cells[columnIndex].Style.BackColor = Color.Red;
string code = ((GridView3.Rows[row].FindControl("code") as Label).Text);
ds.Clear();
string sql = "";
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
ArrayList arr_staf = new ArrayList();
dt1.Columns.Add("SNo", typeof(int));
dt1.Columns.Add("Date", typeof(string));
dt1.Columns.Add("Time", typeof(string));
dt1.Columns.Add("Count", typeof(string));
string fdtime = "";
string tdtime = "";
string dtime = DateTime.Now.ToString("HH:mm:ss");
string firstdate = Convert.ToString(tbstart_date.Text);
string seconddate = Convert.ToString(tbend_date.Text);
string[] splitdate = firstdate.Split('/');
string[] splitdate2 = seconddate.Split('/');
fdtime = splitdate[2].ToString() + "-" + splitdate[1].ToString() + "-" + splitdate[0].ToString() + " " + "00:00:00";
tdtime = splitdate2[2].ToString() + "-" + splitdate2[1].ToString() + "-" + splitdate2[0].ToString() + " " + dtime;
string sqlflag = "select flag from logindetails where staff_code='" + code + "'";
string valueflag = "";
flagds.Clear();
flagds.Dispose();
flagds = da.select_method_wo_parameter(sqlflag, "Text");
if (flagds.Tables[0].Rows.Count > 0)
{
valueflag = flagds.Tables[0].Rows[0][0].ToString();
}
if (valueflag == "2")
{
sql = "SELECT DISTINCT ld.staff_code, count(ld.staff_code) AS count , CONVERT(VARCHAR, cast(ld.dateandtime as date), 101) as date, um.Stud_Name as Full_Name FROM logindetails ld, registration um where ld.staff_code=um.Roll_No and ld.dateandtime between '" + fdtime + "' and '" + tdtime + "' and ld.staff_code='" + code + "' GROUP BY ld.staff_code,cast(dateandtime as date), um.Stud_Name;";
}
else if (valueflag == "1")
{
sql = "SELECT DISTINCT ld.staff_code, count(ld.staff_code) AS count , CONVERT(VARCHAR, cast(ld.dateandtime as date), 101) as date,um.Full_Name,um.Description FROM logindetails ld, UserMaster um where ld.staff_code=um.User_id and ld.dateandtime between '" + fdtime + "' and '" + tdtime + "' and ld.staff_code='" + code + "' GROUP BY ld.staff_code,cast(dateandtime as date), um.Full_Name,um.Description;";
}
else if (valueflag == "0")
{
sql = "SELECT DISTINCT ld.staff_code, count(ld.staff_code) AS count , CONVERT(VARCHAR, cast(ld.dateandtime as date), 101) as date,um.Full_Name,um.Description FROM logindetails ld, UserMaster um where ld.staff_code=um.User_id and ld.dateandtime between '" + fdtime + "' and '" + tdtime + "' and ld.staff_code='" + code + "' GROUP BY ld.staff_code,cast(dateandtime as date), um.Full_Name,um.Description;";
}
DataSet ds1 = new DataSet();
ds = da.select_method_wo_parameter(sql, "Text");
int count = 0;
int count1 = 1;
for (int d = 0; d < ds.Tables[0].Rows.Count; d++)
{
count++;
count = count + 1;
sql = " select LTRIM(RIGHT(CONVERT(CHAR(20), dateandtime, 22), 11)) as Time from logindetails where staff_code='" + code + "' and cast(dateandtime as date) = '" + ds.Tables[0].Rows[d]["date"].ToString() + "' order by Time asc";
ds1 = da.select_method_wo_parameter(sql, "Text");
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
DataRow dtrow1 = dt1.NewRow();
dtrow1[0] = count1;
dtrow1[1] = ds.Tables[0].Rows[d]["date"].ToString();
dtrow1[3] = ds.Tables[0].Rows[d]["count"].ToString();
dtrow1[2] = ds1.Tables[0].Rows[i]["Time"].ToString();
dt1.Rows.Add(dtrow1);
}
count1++;
}
GridView4.DataSource = dt1;
GridView4.DataBind();
GridView4.Visible = true;
}
catch (Exception ex)
{
lblerr.Text = ex.ToString();
lblerr.Visible = true;
}
}
protected void grdvw_List_RowEditing(object sender, GridViewEditEventArgs e)
{
strSelectedId = grdvw_List.Rows[e.NewEditIndex].Cells[1].Text;
txt_ItemName.Text = grdvw_List.Rows[e.NewEditIndex].Cells[2].Text;
if (grdvw_List.Rows[e.NewEditIndex].Cells[2].Text.Trim() != " ")
{
txt_AccessTime.Text = grdvw_List.Rows[e.NewEditIndex].Cells[4].Text;
}
else
{
txt_AccessTime.Text = "";
}
txt_person0.Text = grdvw_List.Rows[e.NewEditIndex].Cells[3].Text;
txt_ItemName.ReadOnly = true;
txt_person0.ReadOnly = true;
//备注绑定
//项目接收备注显示
string remarkstr = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='1' order by t_Y_Detail.id";
DataSet ds_Remark1 = new MyDataOp(remarkstr).CreateDataSet();
string strtemp = "select Name,UserID from t_R_UserInfo";
DataSet ds_User = new MyDataOp(strtemp).CreateDataSet();
foreach (DataRow dr in ds_Remark1.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView1.DataSource = ds_Remark1;
GridView1.DataBind();
string remarkstr2 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='2' order by t_Y_Detail.id";
DataSet ds_Remark2 = new MyDataOp(remarkstr2).CreateDataSet();
foreach (DataRow dr in ds_Remark2.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView2.DataSource = ds_Remark2;
GridView2.DataBind();
string remarkstr3 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='3' order by t_Y_Detail.id";
DataSet ds_Remark3 = new MyDataOp(remarkstr3).CreateDataSet();
foreach (DataRow dr in ds_Remark3.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView3.DataSource = ds_Remark3;
GridView3.DataBind();
string remarkstr4 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='4' order by t_Y_Detail.id";
DataSet ds_Remark4 = new MyDataOp(remarkstr4).CreateDataSet();
foreach (DataRow dr in ds_Remark4.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView4.DataSource = ds_Remark4;
GridView4.DataBind();
string remarkstr5 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='5' order by t_Y_Detail.id";
DataSet ds_Remark5 = new MyDataOp(remarkstr5).CreateDataSet();
foreach (DataRow dr in ds_Remark5.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView5.DataSource = ds_Remark5;
GridView5.DataBind();
string remarkstr6 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='6' order by t_Y_Detail.id";
DataSet ds_Remark6 = new MyDataOp(remarkstr6).CreateDataSet();
foreach (DataRow dr in ds_Remark6.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView6.DataSource = ds_Remark6;
GridView6.DataBind();
string remarkstr7 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='7' order by t_Y_Detail.id";
DataSet ds_Remark7 = new MyDataOp(remarkstr7).CreateDataSet();
foreach (DataRow dr in ds_Remark7.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView7.DataSource = ds_Remark7;
GridView7.DataBind();
//有回退的，则显示回退备注，非回退，不显示
string backremarkstr = "select createdate 备注时间,t_Y_BackInfo.remark 备注及意见,userid 用户名 from t_Y_BackInfo inner join t_Y_FlowInfo on t_Y_BackInfo.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_BackInfo.functionid where t_Y_BackInfo.itemid='" + strSelectedId + "' and t_Y_BackInfo.functionid='9' order by t_Y_BackInfo.id";
// string backremarkstr = "select * from t_Y_BackInfo where itemid='" + strSelectedId + "' and functionid='2'";
DataSet ds_Remark_back = new MyDataOp(backremarkstr).CreateDataSet();
foreach (DataRow dr in ds_Remark_back.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView_back.DataSource = ds_Remark_back;
GridView_back.DataBind();
//回退的数据编辑，显示前面的备注信息，只读
string remarkstr_now = "select ItemName 项目类型, name 阶段,CreateDate 备注时间,bz 备注及意见,userid 用户名,flag,t_Y_Detail.id from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='8' order by t_Y_Detail.id";
DataSet ds_Remark_now = new MyDataOp(remarkstr_now).CreateDataSet();
foreach (DataRow dr in ds_Remark_now.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView_now.DataSource = ds_Remark_now;
GridView_now.DataBind();
DataRow[] dr_remark = ds_Remark_now.Tables[0].Select("flag=0");
if (dr_remark.Length > 0)
{
txt_Remark_now.Text = dr_remark[0][3].ToString();
SelectedId = dr_remark[0][6].ToString();
}
if (ds_Remark_back.Tables[0].Rows.Count > 0)
{
Panel_back.Visible = true;
}
else
{
Panel_back.Visible = false;
}
ds_Remark1.Dispose();
ds_Remark2.Dispose();
ds_Remark3.Dispose();
ds_Remark4.Dispose();
ds_Remark5.Dispose();
ds_Remark6.Dispose();
ds_Remark7.Dispose();
// ds_Remark8.Dispose();
// ds_Remark9.Dispose();
//ds_Remark10.Dispose();
//ds_Remark11.Dispose();
//ds_Remark12.Dispose();
//ds_Remark13.Dispose();
//ds_Remark14.Dispose();
ds_Remark_back.Dispose();
ds_Remark_now.Dispose();
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "showAddEdit();", true);
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "setorder")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
Panel9.Visible = true;
Panel10.Visible = true;
GridView4.DataSource = dp.Query_ConnectedWorkOrder(new Guid(PPID.Text));
GridView4.DataBind();
GridView5.DataSource = dp.Query_WorkOrder(TextBox20.Text);
GridView5.DataBind();
UpdatePanel9.Update();
UpdatePanel10.Update();
}
if (e.CommandName == "getorder")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
Panel9.Visible = false;
Panel10.Visible = false;
Panel12.Visible = true;
GridView9.DataSource = dp.Query_ConnectedWorkOrder(new Guid(PPID.Text));
GridView9.DataBind();
UpdatePanel12.Update();
UpdatePanel10.Update();
UpdatePanel9.Update();
}
if (e.CommandName == "setview")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
DropDownList3.Items.Clear();
SqlDataReader myReader = dp.Query_CapableDep(new Guid(PPID.Text));
SqlDataReader myReader2 = dp.Query_CapableDepDone(new Guid(PPID.Text));
while (myReader.Read())
{
string a = myReader["BDOS_Name"].ToString();
if (Session["Department"].ToString().Contains(myReader["BDOS_Name"].ToString()))
{
DropDownList3.Items.Add(new ListItem(myReader["BDOS_Name"].ToString(), myReader["PPDS_ID"].ToString()));//增加Item
}
}
while (myReader2.Read())
{
ListItem li = DropDownList3.Items.FindByText(myReader2["BDOS_Name"].ToString());
DropDownList3.Items.Remove(li);
}
if (DropDownList3.Items.Count > 0)
{
Panel11.Visible = true;
UpdatePanel11.Update();
}
else
{
ScriptManager.RegisterStartupScript(Page, typeof(Page), "alert", "alert('处理意见已填写或您没有相应的权限！')", true);
}
}
if (e.CommandName == "getview")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
GridView10.DataSource = dp.GetAuditSuggest(new Guid(PPID.Text));
GridView10.DataBind();
Panel15.Visible = true;
UpdatePanel15.Update();
}
if (e.CommandName == "track")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
Panel13.Visible = true;
Panel14.Visible = false;
Panel15.Visible = false;
UpdatePanel13.Update();
UpdatePanel14.Update();
UpdatePanel15.Update();
}
if (e.CommandName == "audit")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
GridView8.DataSource = dp.GetAuditSuggest(new Guid(PPID.Text));
GridView8.DataBind();
Panel14.Visible = true;
UpdatePanel14.Update();
}
if (e.CommandName == "mod")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
switchlabel.Text = "修改";
PPID.Text = e.CommandArgument.ToString();
Panel3.Visible = true;
UpdatePanel3.Update();
UpdatePanel4.Update();
}
if (e.CommandName == "del")
{
GridViewRow row = ((LinkButton)e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
PPID.Text = e.CommandArgument.ToString();
dp.Delete_DefectProduct(new Guid(PPID.Text));
GridView1.DataSource = dp.Query_DefectProduct();
GridView1.DataBind();
Panel4.Visible = false;
Panel10.Visible = false;
Panel14.Visible = false;
UpdatePanel2.Update();
UpdatePanel3.Update();
UpdatePanel4.Update();
UpdatePanel10.Update();
UpdatePanel14.Update();
}
}
protected void GetTokenasUser_Click(object sender, EventArgs e)
{
List <string> Selectedvips = new List <string>();
DataTable VipTokenTable = new DataTable();
VipTokenTable.Columns.Add("Site Name");
VipTokenTable.Columns.Add("VIP");
VipTokenTable.Columns.Add("TargetIdentifier");
VipTokenTable.Columns.Add("TokenResponse");
if (DropDownList1.SelectedItem != null)
{
foreach (GridViewRow gvrow in GridView1.Rows)
{
var checkbox = gvrow.FindControl("CheckBoxSiteVip") as CheckBox;
if (checkbox.Checked)
{
Label1.Text = "";
AdfsSqlHelper stsName = new AdfsSqlHelper();
string FarmEndpoint = stsName.GetFarmName();
Selectedvips.Add((gvrow.FindControl("LabelVip") as Label).Text);
DataRow row = VipTokenTable.NewRow();
foreach (object vip in Selectedvips)
{
row["Site Name"] = (gvrow.FindControl("LabelSite") as Label).Text;
row["VIP"] = vip.ToString();
String appliesTo = DropDownList1.SelectedItem.Text;
String federationServer = vip.ToString();
String endpoint = "https://" + federationServer + "/adfs/services/trust/2005/usernamemixed";
string username = TextBox2.Text;
string password = TextBox3.Text.Protect();
//Crafted RST. Do not alter.
String RST = String.Format("<s:Envelope xmlns:s=" +
"\"http://www.w3.org/2003/05/soap-envelope\"" +
" " + "xmlns:a=" + "\"http://www.w3.org/2005/08/addressing\"" +
" " + "xmlns:u=" + "\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd\"" +
"><s:Header><a:Action s:mustUnderstand=" +
"\"1\"" + ">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue" +
"</a:Action><a:ReplyTo><a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address></a:ReplyTo><a:To s:mustUnderstand=" +
"\"1\"" + ">" + endpoint + "</a:To><o:Security s:mustUnderstand=" +
"\"1\"" + " " + "xmlns:o=" + "\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\"" +
"><o:UsernameToken><o:Username>" + username + "</o:Username><o:Password Type=" +
"\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText\"" +
">" + password.Unprotect() + "</o:Password></o:UsernameToken></o:Security></s:Header><s:Body><t:RequestSecurityToken xmlns:t=" +
"\"http://schemas.xmlsoap.org/ws/2005/02/trust\"" + "><wsp:AppliesTo xmlns:wsp=" +
"\"http://schemas.xmlsoap.org/ws/2004/09/policy\"" + "><a:EndpointReference><a:Address>" + appliesTo +
"</a:Address></a:EndpointReference></wsp:AppliesTo><t:KeySize>0</t:KeySize><t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey</t:KeyType><t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</t:RequestType><t:TokenType>urn:oasis:names:tc:SAML:1.0:assertion</t:TokenType></t:RequestSecurityToken></s:Body></s:Envelope>");
//String ComputedRST = String.Format(RST, endpoint, appliesTo);
HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://" + FarmEndpoint + "/adfs/services/trust/2005/usernamemixed");//url and Host header
FieldInfo field_ServicePoint_ProxyServicePoint = (typeof(ServicePoint))
.GetField("m_ProxyServicePoint", BindingFlags.NonPublic | BindingFlags.Instance);
req.Proxy = new WebProxy(vip.ToString() + ":443");//server IP and port
field_ServicePoint_ProxyServicePoint.SetValue(req.ServicePoint, false);
req.Referer = "https://" + FarmEndpoint;
req.Headers.Add("Name", "https://" + FarmEndpoint + "/adfs/services/trust/2005/usernamemixed");
req.Method = "POST";
req.ContentType = "application/soap+xml";
//req.ContentType = "application/x-www-form-urlencoded;charset=UTF-8";
req.KeepAlive = true;
req.AllowAutoRedirect = false;
byte[] data = Encoding.UTF8.GetBytes(RST);
req.ContentLength = data.Length;
req.Credentials = CredentialCache.DefaultCredentials;
req.Credentials = CredentialCache.DefaultNetworkCredentials;
try
{
Stream dataStream = req.GetRequestStream();
dataStream.Write(data, 0, data.Length);
dataStream.Close();
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
byte[] result = null;
int byteCount = Convert.ToInt32(resp.ContentLength);
using (BinaryReader reader = new BinaryReader(resp.GetResponseStream()))
{
result = reader.ReadBytes(byteCount);
row["TargetIdentifier"] = DropDownList1.SelectedItem.Value;
//Dirt way to extract claims
string claims = System.Text.Encoding.UTF8.GetString(result);
XmlDocument xmltest = new XmlDocument();
xmltest.LoadXml(claims);
string xmltoken = xmltest.InnerText;
if (xmltoken.Contains("SAML:1"))
{
int first = xmltoken.IndexOf("tc:SAML:1.0:cm");
int last = xmltoken.LastIndexOf("tc:SAML:1.0:cm");
string Finaltoken = xmltoken.Substring(first, last - first);
row["TokenResponse"] = Finaltoken;
}
else
{
row["TokenResponse"] = xmltoken;
}
}
}
catch (Exception TokenExp)
{
row["TargetIdentifier"] = DropDownList1.SelectedItem.Value;
row["TokenResponse"] = TokenExp.Message.ToString();
}
}
VipTokenTable.Rows.Add(row);
}
else if (Selectedvips.Count == 0)
{
Label1.Text = "Please select a VIP";
Label1.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FF0000");
}
}//foreach
GridView4.DataSource = VipTokenTable;
GridView4.DataBind();
}
else
{
Label4.Text = "You have not selected an RP Identifier.";
Label4.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FF0000");
GridView4.DataSource = null;
GridView4.DataBind();
}
}
private void BindGridView2()
{
address.LoginName = Session["UserName"].ToString();
GridView4.DataSource = address.ShowAllOfficialContact();
GridView4.DataBind();
}
protected void btnEmail_Click(object sender, EventArgs e)
{
strEmail = txtEmail.Text;
DataSourceSelectArguments srUserID_IDStr = new DataSourceSelectArguments();
DataView dvUserID_IDStr = (DataView)SqlDataSource5.Select(srUserID_IDStr);
if (dvUserID_IDStr.Count != 0)
{
strUserID_ID = dvUserID_IDStr[0][0].ToString();
}
DataSourceSelectArguments srUserID_SSOStr = new DataSourceSelectArguments();
DataView dvUserID_SSOStr = (DataView)SqlDataSource14.Select(srUserID_SSOStr);
if (dvUserID_SSOStr.Count != 0)
{
strUserID_SSO = dvUserID_SSOStr[0][0].ToString();
}
DataSourceSelectArguments srTokenUser = new DataSourceSelectArguments();
DataView dvTokenUser = (DataView)SqlDataSource4.Select(srTokenUser);
if (dvTokenUser.Count != 0)
{
strTokenUser = dvTokenUser[0][0].ToString();
}
DataSourceSelectArguments srTokenPassStr = new DataSourceSelectArguments();
DataView dvTokenPassStr = (DataView)SqlDataSource12.Select(srTokenPassStr);
if (dvTokenPassStr.Count != 0)
{
strTokenPass = dvTokenPassStr[0][0].ToString();
}
DataSourceSelectArguments srOrgTempStr = new DataSourceSelectArguments();
DataView dvOrgTempStr = (DataView)SqlDataSource110.Select(srOrgTempStr);
if (dvOrgTempStr.Count != 0)
{
strOrgTemp = dvOrgTempStr[0][0].ToString();
}
DataSourceSelectArguments srOrgTempIDStr = new DataSourceSelectArguments();
DataView dvOrgTempIDStr = (DataView)SqlDataSource118.Select(srOrgTempIDStr);
if (dvOrgTempIDStr.Count != 0)
{
strOrgTempID = dvOrgTempIDStr[0][0].ToString();
}
DataSourceSelectArguments srOrgTempNameStr = new DataSourceSelectArguments();
DataView dvOrgTempNameStr = (DataView)SqlDataSource124.Select(srOrgTempNameStr);
if (dvOrgTempNameStr.Count != 0)
{
strOrgTempName = dvOrgTempNameStr[0][0].ToString();
}
DataSourceSelectArguments srOrgIDStr = new DataSourceSelectArguments();
DataView dvOrgIDStr = (DataView)SqlDataSource111.Select(srOrgIDStr);
if (dvOrgIDStr.Count != 0)
{
strOrgID = dvOrgIDStr[0][0].ToString();
}
DataSourceSelectArguments srOrgAccessRequestedStr = new DataSourceSelectArguments();
DataView dvOrgAccessRequestedStr = (DataView)SqlDataSource112.Select(srOrgAccessRequestedStr);
if (dvOrgAccessRequestedStr.Count != 0)
{
strOrgAccessRequested = dvOrgAccessRequestedStr[0][0].ToString();
}
DataSourceSelectArguments srOrgModAccessRequestedStr = new DataSourceSelectArguments();
DataView dvOrgModAccessRequestedStr = (DataView)SqlDataSource113.Select(srOrgModAccessRequestedStr);
if (dvOrgModAccessRequestedStr.Count != 0)
{
strOrgModAccessRequested = dvOrgModAccessRequestedStr[0][0].ToString();
}
DataSourceSelectArguments srOrgLicAccessRequestedStr = new DataSourceSelectArguments();
DataView dvOrgLicAccessRequestedStr = (DataView)SqlDataSource114.Select(srOrgLicAccessRequestedStr);
if (dvOrgLicAccessRequestedStr.Count != 0)
{
strOrgLicAccessRequested = dvOrgLicAccessRequestedStr[0][0].ToString();
}
DataSourceSelectArguments srOrgMembershipUserIDStr = new DataSourceSelectArguments();
DataView dvOrgMembershipUserIDStr = (DataView)SqlDataSource115.Select(srOrgMembershipUserIDStr);
if (dvOrgMembershipUserIDStr.Count != 0)
{
strOrgMembershipUserID = dvOrgMembershipUserIDStr[0][0].ToString();
}
DataSourceSelectArguments srUserPasswordExpiryDt = new DataSourceSelectArguments();
DataView dvUserPasswordExpiryDt = (DataView)SqlDataSource116.Select(srUserPasswordExpiryDt);
if (dvUserPasswordExpiryDt.Count != 0)
{
strDateUserPasswordExpiry = dvUserPasswordExpiryDt[0][0].ToString();
}
DataSourceSelectArguments srUserPasswordChangedDt = new DataSourceSelectArguments();
DataView dvUserPasswordChangedDt = (DataView)SqlDataSource126.Select(srUserPasswordChangedDt);
if (dvUserPasswordChangedDt.Count != 0)
{
strDateUserPasswordChanged = dvUserPasswordChangedDt[0][0].ToString();
}
DataSourceSelectArguments srUserPasswordResetTokenDt = new DataSourceSelectArguments();
DataView dvUserPasswordResetTokenDt = (DataView)SqlDataSource122.Select(srUserPasswordResetTokenDt);
if (dvUserPasswordResetTokenDt.Count != 0)
{
strDatePasswordResetToken = dvUserPasswordResetTokenDt[0][0].ToString();
}
DataSourceSelectArguments srDateRegisteredDt = new DataSourceSelectArguments();
DataView dvDateRegisteredDt = (DataView)SqlDataSource121.Select(srDateRegisteredDt);
if (dvDateRegisteredDt.Count != 0)
{
strDateRegistered = dvDateRegisteredDt[0][0].ToString();
}
DataSourceSelectArguments srUserTempDetailsStr = new DataSourceSelectArguments();
DataView dvUserTempDetailsStr = (DataView)SqlDataSource117.Select(srUserTempDetailsStr);
if (dvUserTempDetailsStr.Count != 0)
{
strUserID_TempID = dvUserTempDetailsStr[0][0].ToString();
}
DataSourceSelectArguments srOrganisationRequestStr = new DataSourceSelectArguments();
DataView dvOrganisationRequestStr = (DataView)SqlDataSource119.Select(srOrganisationRequestStr);
if (dvOrganisationRequestStr.Count != 0)
{
strOrganisationRequest = dvOrganisationRequestStr[0][0].ToString();
}
DataSourceSelectArguments srUserID_UPMStr = new DataSourceSelectArguments();
DataView dvUserID_UPMStr = (DataView)SqlDataSource120.Select(srUserID_UPMStr);
if (dvUserID_UPMStr.Count != 0)
{
strUserID_UPM = dvUserID_UPMStr[0][0].ToString();
}
DataSourceSelectArguments srEventTypeRejectedStr = new DataSourceSelectArguments();
DataView dvEventTypeRejectedStr = (DataView)SqlDataSource123.Select(srEventTypeRejectedStr);
if (dvEventTypeRejectedStr.Count != 0)
{
strEventTypeRejected = dvEventTypeRejectedStr[0][0].ToString();
}
dtDateToday = DateTime.Now;
/*
* Password expiry notification and countdown
*
*/
dtDateUserPasswordExpiry = null;
if (!string.IsNullOrEmpty(strDateUserPasswordExpiry))
{
dtDateUserPasswordExpiry = DateTime.ParseExact(strDateUserPasswordExpiry, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
dtDateUserPasswordExpiryForSubtraction = DateTime.ParseExact(strDateUserPasswordExpiry, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
if (dtDateUserPasswordExpiry != null && dtDateToday > dtDateUserPasswordExpiry && strOrgID != "7c310be0-c20f-de11-b526-0022642a33b2")
{
divPasswordExpiry.Visible = true;
lblPasswordExpiry.Text = "EDEN password has expired";
lblPasswordExpiry.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divPasswordExpiry.Visible = false;
}
/*
* Registration expiry
*
*/
if (!string.IsNullOrEmpty(strDateRegistered))
{
dtDateRegistered = DateTime.ParseExact(strDateRegistered, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
tsDateRegistrationValidTimeRemaining = dtDateRegistered.AddHours(+24).Subtract(DateTime.Now);
}
if (strUserID_SSO == null && dtDateRegistered != null && strDateRegistered != null && dtDateRegistered < DateTime.Now.AddHours(-24))
{
divRegistrationExpiry.Visible = true;
lblRegistrationExpiry.Text = "<i>Registration token has expired.</i> <br />- User's registration must be deleted to allow re-registration";
lblRegistrationExpiry.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divRegistrationExpiry.Visible = false;
}
if (strUserID_SSO == null && dtDateRegistered != null && strDateRegistered != null && dtDateRegistered > DateTime.Now.AddHours(-24))
{
divRegistrationExpiry.Visible = true;
lblRegistrationExpiry.Text = "Registration token is valid.<br />- The account verification link can be copied and emailed to the user if the notification has been blocked or re-routed. <br/>- This link is valid until " + dtDateRegistered.AddHours(+24) + ". Remaining time: <b>" + tsDateRegistrationValidTimeRemaining.ToString(@"hh\:mm") + "</b> hours";
lblRegistrationExpiry.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
else
{
divRegistrationExpiry.Visible = false;
}
if (dtDateRegistered == null)
{
divRegistrationExpiry.Visible = false;
}
if (strUserID_SSO != null)
{
divRegistrationExpiry.Visible = false;
}
/*
* Password reset token expiry
*
*/
//dtDatePasswordResetToken='';
if (!string.IsNullOrEmpty(strDatePasswordResetToken))
{
dtDatePasswordResetToken = DateTime.ParseExact(strDatePasswordResetToken, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
tsDatePasswordResetTokenTimeRemaining = dtDatePasswordResetToken.AddHours(+24).Subtract(DateTime.Now);
}
if (strUserID_SSO != null && dtDatePasswordResetToken != null && strDatePasswordResetToken != null && dtDatePasswordResetToken < DateTime.Now.AddHours(-24))
{
divPasswordResetTokenExpiry.Visible = true;
lblPasswordResetTokenExpiry.Text = "<br /><i>Password reset token has expired.</i> <br />- The user will need to be begin the password reset process again and generate a new token.";
lblPasswordResetTokenExpiry.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divPasswordResetTokenExpiry.Visible = false;
}
if (strUserID_SSO != null && dtDatePasswordResetToken != null && dtDatePasswordResetToken > DateTime.Now.AddHours(-24))
{
divPasswordResetTokenExpiry.Visible = true;
lblPasswordResetTokenExpiry.Text = "<br />Password reset token is valid.<br />- The password reset token can be copied and emailed to the user.<br/>- This token is valid until " + dtDatePasswordResetToken.AddHours(+24) + ". Remaining time: <b>" + tsDatePasswordResetTokenTimeRemaining.ToString(@"hh\:mm") + "</b> hours (after which the user will need to be begin the password reset process again and generate a new token).";
lblPasswordResetTokenExpiry.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
if (dtDatePasswordResetToken == null)
{
divPasswordResetTokenExpiry.Visible = false;
}
if (strUserID_ID == null &&
strUserID_SSO == null &&
strUserID_TempID == null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID == null &&
strTokenUser == null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divEmailNotFound.Visible = true;
lblEmailNotFound.Text = "Email address not found";
lblEmailNotFound.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divEmailNotFound.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO == null &&
strUserID_TempID == null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID == null &&
strTokenUser != null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divUserNoConfirmationClicked.Visible = true;
lblUserNoConfirmationClicked.Text = "Status: User has not clicked confirmation email and signed in";
lblUserNoConfirmationClicked.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserNoConfirmationClicked.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID == null &&
strTokenUser != null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divUserConfirmSignedNoStep1.Visible = true;
lblUserConfirmSignedNoStep1.Text = "Status: EDEN Portal Access Request - Step 1: User has clicked confirmation email, but has not selected an organisation";
lblUserConfirmSignedNoStep1.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserConfirmSignedNoStep1.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID != null &&
strTokenUser != null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divUserConfirmSignedStep2.Visible = true;
divUserConfirmTempOrgDetails.Visible = true;
lblUserConfirmSignedStep2.Text = "Status: EDEN Portal Access Request - Step 2: User has selected an organisation, but has not yet added their personal and contact details";
lblUserConfirmSignedStep2.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserConfirmSignedStep2.Visible = false;
divUserConfirmTempOrgDetails.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strOrgID == null &&
strOrgTemp != null &&
strTokenUser != null
)
{
divUserConfirmSignedLAStep2.Visible = true;
divUserConfirmTempOrgDetails.Visible = true;
lblUserConfirmSignedLAStep2.Text = "Status: EDEN Portal Access Request - Step 2: User has selected an organisation, but has not yet added their personal and contact details";
lblUserConfirmSignedLAStep2.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserConfirmSignedLAStep2.Visible = false;
divUserConfirmTempOrgDetails.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID != null &&
strOrgAccessRequested == null &&
strTokenUser != null &&
strOrgMembershipUserID == null &&
strEventTypeRejected != "Organisation Membership Request Rejected"
)
{
divUserConfirmSignedStep3.Visible = true;
divUserConfirmTempOrgDetails.Visible = true;
lblUserConfirmSignedStep3.Text = "Status: EDEN Portal Access Request - Step 3: User has selected an organisation, but has not yet selected a module and submitted the access request";
lblUserConfirmSignedStep3.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserConfirmSignedStep3.Visible = false;
divUserConfirmTempOrgDetails.Visible = false;
}
//if (strUserID_SSO != null && strOrgID == null && strUserID_TempID != null && strOrgTemp != null && strOrgAccessRequested == null)
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strOrgID == null &&
strOrgTemp != null &&
strOrgAccessRequested == null &&
strTokenUser != null &&
strOrgMembershipUserID == null &&
strOrganisationRequest == null &&
strEventTypeRejected == null
)
{
divUserConfirmSignedLAStep3.Visible = true;
divUserConfirmTempOrgDetails.Visible = true;
lblUserConfirmSignedLAStep3.Text = "Status: EDEN Portal Access Request - Step 3: User has selected an organisation, but has not yet selected a module and submitted the access request";
lblUserConfirmSignedLAStep3.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserConfirmSignedLAStep3.Visible = false;
divUserConfirmTempOrgDetails.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strOrgID == null &&
strOrgTemp != null &&
strOrgAccessRequested == null &&
strTokenUser != null &&
strOrgMembershipUserID == null &&
strOrganisationRequest != null
)
{
divUserNewOrgCreate.Visible = true;
lblUserNewOrgCreate.Text = "Status: User has selected a module and requested to register a <i>new</i> organisation on EDEN. The request is now with the EPA for approval";
lblUserNewOrgCreate.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
else
{
divUserNewOrgCreate.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID != null &&
strOrgAccessRequested != null &&
strTokenUser != null &&
strOrgMembershipUserID == null
)
{
divUserRegCompleteNoApproval.Visible = true;
divUserConfirmTempOrgDetails.Visible = true;
lblUserRegCompleteNoApproval.Text = "Status: User has selected an organisation, selected a module and submitted the access request. The org admin(s) can now process this request";
lblUserRegCompleteNoApproval.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
else
{
divUserRegCompleteNoApproval.Visible = false;
divUserConfirmTempOrgDetails.Visible = false;
}
//if (strUserID_SSO != null && strOrgID == null && strUserID_TempID != null && strOrgTemp != null && strOrgAccessRequested != null)
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strOrgID == null &&
strOrgTemp != null &&
strOrgAccessRequested != null &&
strTokenUser != null &&
strOrgMembershipUserID == null
)
{
divUserRegCompleteNoApprovalLA.Visible = true;
lblUserRegCompleteNoApprovalLA.Text = "Status: User has selected an organisation, selected a module and submitted the access request. The org admin(s) can now process this request";
lblUserRegCompleteNoApprovalLA.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
else
{
divUserRegCompleteNoApprovalLA.Visible = false;
}
if (strOrgModAccessRequested != null && strOrgID != null)
{
divModuleAccessPending.Visible = true;
lblModuleAccessPending.Text = "Status: One or more module access requests by this user currently Pending Approval";
lblModuleAccessPending.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divModuleAccessPending.Visible = false;
}
if (strOrgID != null)
{
divUserConfirmSignedNoStep1.Visible = false;
}
if (strUserID_TempID != null)
{
divPasswordExpiry.Visible = false;
}
if (strOrgModAccessRequested != null && strOrgLicAccessRequested != null)
{
GridView16.Visible = true;
GridView17.Visible = false;
}
if (strOrgModAccessRequested == strOrgLicAccessRequested)
{
GridView16.Visible = true;
GridView17.Visible = true;
}
if (strOrgMembershipUserID == null)
{
GridView23.Visible = true;
}
else
{
GridView23.Visible = false;
}
if (strOrgMembershipUserID == null)
{
GridView22.Visible = true;
}
else
{
GridView22.Visible = false;
}
if (strUserID_SSO == null & strTokenUser != null)
{
divVerificationLink.Visible = true;
}
else
{
divVerificationLink.Visible = false;
}
if (strUserID_SSO != null)
{
divVerificationLink.Visible = false;
}
if (strUserID_SSO != null)
{
divUserExternalLinks.Visible = true;
}
else
{
divUserExternalLinks.Visible = false;
}
if (strTokenPass == null)
{
divPasswordReset.Visible = false;
}
else
{
divPasswordReset.Visible = true;
}
/*
* EPA Users
*/
if (strUserID_ID == null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strUserID_UPM != null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID == null &&
strTokenUser == null &&
strTokenPass == null &&
strOrgAccessRequested == null &&
strOrgModAccessRequested == null &&
strOrgLicAccessRequested == null &&
strOrgMembershipUserID == null &&
strOrganisationRequest == null &&
strEventTypeRejected == null
)
{
divUserEPAStep1.Visible = true;
lblUserEPAStep1.Text = "Status: EDEN Portal Access Request - Step 1: EPA user has signed up, but has not selected an organisation";
lblUserEPAStep1.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserEPAStep1.Visible = false;
}
if (strUserID_ID == null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strUserID_UPM != null &&
strOrgTemp != null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divUserEPAStep2.Visible = true;
lblUserEPAStep2.Text = "Status: EDEN Portal Access Request - Step 2: EPA user has selected organisation, but has not yet added their personal and contact details";
lblUserEPAStep2.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserEPAStep2.Visible = false;
}
if (strUserID_ID == null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strUserID_UPM != null &&
strOrgTemp != null &&
strOrgAccessRequested == null &&
strOrgMembershipUserID == null
)
{
divUserEPAStep3.Visible = true;
lblUserEPAStep3.Text = "Status: EDEN Portal Access Request - Step 3: EPA user has added their personal and contact details, but has not yet selected a module";
lblUserEPAStep3.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserEPAStep3.Visible = false;
}
if (strUserID_ID == null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strUserID_UPM != null &&
strOrgTemp != null &&
strOrgAccessRequested != null
)
{
divUserRegCompleteNoApprovalEPA.Visible = true;
lblUserRegCompleteNoApprovalEPA.Text = "EPA user has selected an organisation, selected a module and submitted the access request. The EPA org admin(s) can now process this request";
lblUserRegCompleteNoApprovalEPA.ForeColor = System.Drawing.ColorTranslator.FromHtml("#4d9900");
}
else
{
divUserRegCompleteNoApprovalEPA.Visible = false;
}
if (strUserID_ID == null &&
strUserID_SSO != null &&
strUserID_TempID == null &&
strUserID_UPM != null &&
strOrgID == null &&
strOrgTemp == null &&
strOrgTempID == null &&
strTokenUser == null &&
strTokenPass == null &&
strOrgAccessRequested == null &&
strOrgModAccessRequested == null &&
strOrgLicAccessRequested == null &&
strOrgMembershipUserID == null &&
strOrganisationRequest == null &&
strEventTypeRejected == "Organisation Rejected"
)
{
divUserRejectedLA.Visible = true;
lblUserRejectedLA.Text = "Status: User has signed up, but the organisation has been <b>rejected</b><br /> - This email address must be manually deleted from the SSO to be used again to register.";
lblUserRejectedLA.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserRejectedLA.Visible = false;
}
if (strUserID_ID != null &&
strUserID_SSO != null &&
strUserID_TempID != null &&
strEventTypeRejected == "Organisation Rejected"
)
{
divOrgRejected.Visible = true;
lblOrgRejected.Text = "Status: User has signed up, but the <b>organisation creation request</b> has been <b>rejected</b><br /> - This email address must be manually deleted from the SSO to be used again to register.";
lblOrgRejected.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divOrgRejected.Visible = false;
}
if (
strEventTypeRejected == "Organisation Membership Request Rejected"
)
{
divUserRejectedLA.Visible = true;
lblUserRejectedLA.Text = "Status: User has signed up, but the <b>organisation membership request</b> has been <b>rejected</b><br /> - This email address must be manually deleted from the SSO to be used again to register.";
lblUserRejectedLA.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserRejectedLA.Visible = false;
}
if (
strEventTypeRejected == "Module Authorisation Rejected"
)
{
divUserRejectedLA.Visible = true;
lblUserRejectedLA.Text = "Status: User's organisation access has been approved, but a module authorisation request has been rejected. (This may have been rectified - please check the notifications below).<br />";
lblUserRejectedLA.ForeColor = System.Drawing.ColorTranslator.FromHtml("#E60000");
}
else
{
divUserRejectedLA.Visible = false;
}
tsDatePasswordExpiryLessDatePasswordReset = dtDateUserPasswordExpiryForSubtraction - dtDatePasswordResetToken;
int passwordReset_Expiry_DifferenceInDays = tsDatePasswordExpiryLessDatePasswordReset.Days;
if (tsDatePasswordExpiryLessDatePasswordReset.Days == 90)
{
divPasswordResetTokenExpiry.Visible = false;
}
divTokens.Visible = true;
divSeperatorCurrent.Visible = true;
divSeperatorPending.Visible = true;
divSeperatorLicences.Visible = true;
divSeperatorTokens.Visible = true;
txtCopyVerificationLink.Text = "https://account.edenireland.ie/signup/confirmemail?userId=" + strUserID_ID + "&token=" + strTokenUser;
txtCopyPasswordResetLink.Text = "https://account.edenireland.ie/passwordreset/resetpassword?userId=" + strUserID_SSO + "&token=" + strTokenPass;
GridView1.DataBind();
GridView2.DataBind();
GridView3.DataBind();
GridView4.DataBind();
GridView5.DataBind();
GridView6.DataBind();
GridView7.DataBind();
GridView8.DataBind();
GridView9.DataBind();
GridView10.DataBind();
GridView11.DataBind();
GridView12.DataBind();
GridView13.DataBind();
GridView14.DataBind();
GridView15.DataBind();
GridView16.DataBind();
GridView17.DataBind();
GridView18.DataBind();
GridView19.DataBind();
GridView20.DataBind();
GridView21.DataBind();
GridView22.DataBind();
GridView23.DataBind();
GridView24.DataBind();
}
protected void BindGridviewData()
{
ViewState["75+"] = 0;
ViewState["50+"] = 0;
ViewState["50-"] = 0;
DataTable dt = new DataTable();
dt.Columns.Add("Roll No", typeof(string));
String search = "SELECT * from fac_attendence where courseid='" + Session["courseid"] + "' and groupid='" + Session["groupid"] + "' and facid='" + Session["id"] + "'";
SqlCommand cmd = new SqlCommand(search, conn);
SqlDataReader reader = cmd.ExecuteReader();
String[] attid = new String[50];
String[] attdate = new String[50];
int numofatt = 0;
//int par = 0;
while (reader.Read())
{
String[] temp1 = reader["date"].ToString().Split(' ');
attdate[numofatt] = temp1[0];
dt.Columns.Add(temp1[0], typeof(string));
//String [] temp1 =reader["date"].ToString().Split(' ');
//attdate[numofatt] = temp1[0]+" ";
attid[numofatt] = reader["attid"].ToString();
//par ;
numofatt++;
}
dt.Columns.Add("Total attendance", typeof(string));
dt.Columns.Add("Pacentage", typeof(string));
reader.Close();
search = "SELECT * from student_course where courseid='" + Session["courseid"] + "' and groupid='" + Session["groupid"] + "' ORDER BY studentid";
cmd = new SqlCommand(search, conn);
reader = cmd.ExecuteReader();
DataRow dtrow;
while (reader.Read())
{
dtrow = dt.NewRow();
dtrow["Roll No"] = reader["studentid"].ToString();
int total_att = 0;
for (int i = 0; i < numofatt; i++)
{
String temp = "SELECT att from attendence where studentid='" + reader["studentid"].ToString() + "' and attid='" + attid[i] + "'";
SqlCommand cmd1 = new SqlCommand(temp, conn1);
dtrow[attdate[i]] = cmd1.ExecuteScalar().ToString();
total_att = total_att + (int)cmd1.ExecuteScalar();
}
dtrow["Total attendance"] = total_att;
double pas = total_att * 100;
pas = pas / numofatt;
if (pas >= 75)
{
ViewState["75+"] = Convert.ToInt32(ViewState["75+"].ToString()) + 1;
}
if (pas >= 50 && pas < 75)
{
ViewState["50+"] = Convert.ToInt32(ViewState["50+"].ToString()) + 1;
}
if (pas < 50)
{
ViewState["50-"] = Convert.ToInt32(ViewState["50-"].ToString()) + 1;
}
dtrow["Pacentage"] = Math.Round(Convert.ToDecimal(pas), 2) + "%";
dt.Rows.Add(dtrow);
}
//Bind Data to Columns
GridView4.DataSource = dt;
GridView4.DataBind();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Modify")
{
var row = ((LinkButton) e.CommandSource).Parent.Parent as GridViewRow;
GridView1.SelectedIndex = row.RowIndex;
Label1.Text = "修改";
TextBox11.Text = row.Cells[1].Text;
TextBox12.Text = row.Cells[2].Text;
if (!row.Cells[4].Text.Contains("&"))
{
DropDownList1.SelectedIndex =
DropDownList1.Items.IndexOf(DropDownList1.Items.FindByText(row.Cells[2].Text));
}
TextBox13.Text = row.Cells[3].Text;
if (row.Cells[5].ToolTip!="")
{
TextBox14.Text = row.Cells[5].ToolTip;
}
else
{
TextBox14.Text = row.Cells[5].Text;
}
ElementID.Text = e.CommandArgument.ToString();
Panel5.Visible = true;
UpdatePanel5.Update();
}
if (e.CommandName == "De")
{
int a = me.DeleteHSF(new Guid(e.CommandArgument.ToString()));
ScriptManager.RegisterStartupScript(Page, typeof (Page), "alert",
a > 0 ? "alert('删除成功！');" : "alert('失败了诶...');", true);
bind();
}
if (e.CommandName == "SetDetail")
{
HSFID.Text = e.CommandArgument.ToString();
Panel4.Visible = true;
GridView3.DataSource = ele.Query("",new Guid(HSFID.Text));
GridView3.DataBind();
UpdatePanel4.Update();
}
if (e.CommandName == "Details")
{
HSFID.Text = e.CommandArgument.ToString();
Panel3.Visible = true;
GridView2.DataSource = me.QueryDetail(new Guid(HSFID.Text));
GridView2.DataBind();
UpdatePanel3.Update();
}
if (e.CommandName == "copy")
{
HSFID.Text = e.CommandArgument.ToString();
GridView4.DataSource = me.QueryMaterial(TextBox4.Text, TextBox5.Text);
GridView4.DataBind();
Panel3.Visible = false;
Panel4.Visible = false;
Panel5.Visible = false;
Panel6.Visible = true;
Panel7.Visible = true;
UpdatePanel3.Update();
UpdatePanel4.Update();
UpdatePanel5.Update();
UpdatePanel6.Update();
}
}
protected void Button36_Click(object sender, EventArgs e)
{
GridView4.DataSource = bom.Query_PBC(TextBox16.Text);
GridView4.DataBind();
UpdatePanel4.Update();
}
