protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request["paperid"] != null)
{
string pid = Request["paperid"].ToString();
string viewrs = Request["viewrs"].ToString();
string viewdata = string.Empty;
if (Request["viewdata"] != null)
{
viewdata = Request["viewdata"].ToString();
}
if (!string.IsNullOrEmpty(pid))
{
Database objDB = new SqlDatabase(M_CONSTR);
if (objDB != null)
{
DataSet ds = objDB.ExecuteDataSet(CommandType.Text,
@"SELECT * FROM Table_PAPER WHERE PAPERID=" + pid);
if (ds != null && ds.Tables[0].Rows.Count.Equals(1))
{
string pType = ds.Tables[0].Rows[0]["PaperClassID"].ToString();
if (pType.Equals("1"))//完整版问卷
{
if (!string.IsNullOrEmpty(viewrs))
{
if (!string.IsNullOrEmpty(viewdata))
{
Response.Redirect("~/report/dbbgdata.aspx?paperid=" + pid + "&viewdata=true");
}
Response.Redirect("~/report/dbbg.aspx?paperid=" + pid);
}
Response.Redirect("wz.aspx?paperid=" + pid);
}
else if (pType.Equals("2"))//讲师版问卷
{
if (!string.IsNullOrEmpty(viewrs))
{
if (!string.IsNullOrEmpty(viewdata))
{
Response.Redirect("~/report/jsbgdata.aspx?paperid=" + pid + "&viewdata=true");
}
Response.Redirect("~/report/NotAllow.aspx");
}
Response.Redirect("js.aspx?paperid=" + pid);
}
}
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Database objDB = new SqlDatabase(M_CONSTR);
if (objDB != null)
{
//所属部门
DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_Group");
ddlTGroup.DataSource = ds.Tables[0];
ddlTGroup.DataTextField = "GroupName";
ddlTGroup.DataValueField = "GroupID";
ddlTGroup.DataBind();
ddlTGroup.Items.Insert(0, (new ListItem("--全部--", "0")));
ddlTGroup.SelectedIndex = 0;
//讲师
ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER");
ddlTeacher.DataSource = ds.Tables[0];
ddlTeacher.DataTextField = "TeacherName";
ddlTeacher.DataValueField = "TeacherID";
ddlTeacher.DataBind();
//ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
//ddlTeacher.SelectedIndex = 0;
}
}
}
public static DataTable GetResources(int jobId, int deptId)
{
SqlDatabase db = new SqlDatabase(connString);
string sql = @" SELECT U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
t.TitleName as Title
FROM AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
WHERE U.Active=1 AND U.UserId NOT IN (
SELECT UserId FROM Assignments WHERE JobId=@job_id
AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP)))
AND U.DeptId=@dept_id AND realPerson='Y'
AND UserId NOT IN (
SELECT UserId
FROM timeEntry
WHERE JobId=@job_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
)
ORDER BY FullName";
DbCommand command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Resources";
command.Dispose();
return t;
}
protected void Page_Load( object sender, EventArgs e )
{
SqlDatabase db = new SqlDatabase( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"] );
// get game information
int gameid = Convert.ToInt32( Request["GameId"] );
// DataSet dsGameInfo = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
// "spGetGameDetails", gameid );
DataSet dsGameInfo = db.ExecuteDataSet( "spGetGameDetails", gameid );
int hometeamid = (int)dsGameInfo.Tables[0].Rows[0]["hometeamid"];
int awayteamid = (int)dsGameInfo.Tables[0].Rows[0]["visitorteamid"];
lblPageTitle.Text = "Week " + dsGameInfo.Tables[0].Rows[0]["Week"].ToString()
+ ": " + dsGameInfo.Tables[0].Rows[0]["visitor"].ToString()
+ " " + dsGameInfo.Tables[0].Rows[0]["visitorscore"].ToString()
+ " @ " + dsGameInfo.Tables[0].Rows[0]["home"].ToString()
+ " " + dsGameInfo.Tables[0].Rows[0]["homescore"].ToString();
lblHome.Text = dsGameInfo.Tables[0].Rows[0]["home"].ToString();
lblAway.Text = dsGameInfo.Tables[0].Rows[0]["visitor"].ToString();
if( dsGameInfo.Tables[0].Rows[0]["HomeWins"] != DBNull.Value )
{
lblGameScore.Text = dsGameInfo.Tables[0].Rows[0]["visitor"].ToString()
+ " wins " + dsGameInfo.Tables[0].Rows[0]["visitorwins"].ToString()
+ " games, " + dsGameInfo.Tables[0].Rows[0]["home"].ToString()
+ " wins " + dsGameInfo.Tables[0].Rows[0]["homewins"].ToString()
+ " games";
}
// get the team boxes
//DataSet dsHome = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
// "spGetTeamContributions", gameid, hometeamid );
DataSet dsHome = db.ExecuteDataSet( "spGetTeamContributions", gameid, hometeamid );
AddSummaryData( dsHome );
dgHome.DataSource = dsHome;
dgHome.DataBind();
//DataSet dsAway = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
// "spGetTeamContributions", gameid, awayteamid );
DataSet dsAway = db.ExecuteDataSet( "spGetTeamContributions", gameid, awayteamid );
AddSummaryData( dsAway );
dgAway.DataSource = dsAway;
dgAway.DataBind();
}
protected void btnlist_Click(object sender, EventArgs e)
{
int test = 0;
DataSet ds;
Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=d9deq4jc9e.database.windows.net;Database=kadsysv2;User id=jegan;Password=DBXPose!123;");
//Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=bgtnhy\sql2008;Database=KadSysV2;User id=kaduser;Password=kad123;");
ds = objDataBase.ExecuteDataSet(CommandType.Text, TextBox1.Text);
objDataBase = null;
Response.Write(ds.Tables[0].Rows[0][0].ToString());
Response.Write("Done");
}
protected void ddlTGroup_SelectedIndexChanged(object sender, EventArgs e)
{
ddlTeacher.Items.Clear();
Database objDB = new SqlDatabase(M_CONSTR);
DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER where groupid=" + ddlTGroup.SelectedValue);
ddlTeacher.DataSource = ds.Tables[0];
ddlTeacher.DataTextField = "TeacherName";
ddlTeacher.DataValueField = "TeacherID";
ddlTeacher.DataBind();
ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
ddlTeacher.SelectedIndex = 0;
}
private static DataTable AllocatedJobs(int region, int startWeek)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridJobListSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@RegionId", DbType.Int32, region);
db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
DataTable dt = null;
dt = db.ExecuteDataSet(command).Tables[0].Copy();
dt.TableName = "Jobs";
command.Dispose();
return dt;
}
public DataSet ObtenerDataSet(string conexion, string procedimiento, List<DbParameter> parametros)
{
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
DataSet ds = bd.ExecuteDataSet(cmd);
ObtenerParametrosOut(parametros, cmd);
return ds;
}
private static DataTable Availability(int empId, int weekNumber)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_EmpGridAvailability");
db.AddInParameter(command, "@userId", DbType.Int32, empId);
db.AddInParameter(command, "@weekNumber", DbType.Int32, weekNumber);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Availability";
command.Dispose();
return t;
}
private static DataTable AllocatedUsers(int region, int startWeek, int deptId, string resType)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridUserListSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@RegionId", DbType.Int32, region);
db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
db.AddInParameter(command, "@DepartmentId", DbType.Int32, deptId);
db.AddInParameter(command, "@ResourceType", DbType.String, resType);
DataTable dt = null;
dt = db.ExecuteDataSet(command).Tables[0].Copy();
dt.TableName = "User";
command.Dispose();
return dt;
}
private static DataTable AllocatedDepts(int clientId, int startWeek, int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridDeptSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Depts";
command.Dispose();
return t;
}
public static DataTable ClientTeams(int clientId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand(@"
SELECT TeamID, Name, Description
FROM ALOC_Teams
WHERE ClientID=@client_id
ORDER BY Name
");
command.CommandType = CommandType.Text;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Teams";
command.Dispose();
return t;
}
private static DataTable AllocatedJobs( int clientId, int startWeek, string resourceType )
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "ALOC__TeamGridJobListSelect" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@ClientId", DbType.Int32, clientId );
db.AddInParameter( command, "@StartWeek", DbType.Int32, startWeek );
db.AddInParameter( command, "@Type", DbType.String, resourceType );
DataTable t = new DataTable();
t = db.ExecuteDataSet( command ).Tables[0].Copy();
t.TableName = "Job";
command.Dispose();
return t;
}
public DataTable ObtenerDataTable(string conexion, string procedimiento, List<DbParameter> parametros)
{
DataTable dt = null;
var bd = new SqlDatabase(conexion);
DbCommand cmd = bd.GetStoredProcCommand(procedimiento);
EstablecerParametros(parametros, bd, cmd);
DataSet ds = bd.ExecuteDataSet(cmd);
ObtenerParametrosOut(parametros, cmd);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
return dt;
}
private static DataTable AllocatedEmps(int clientId, int startWeek, int jobId, string resType, int region)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridEmpsSelect");
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
//TODO: why is this setting @dept_id to Zero?
db.AddInParameter(command, "@dept_id", DbType.Int32, 0);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
db.AddInParameter(command, "@resource_type", DbType.String, resType);
db.AddInParameter(command, "@region", DbType.Int32, region);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Emps";
command.Dispose();
return t;
}
public static DataTable GetRestaurantInfo()
{
SqlDatabase objSqlDatabase = new SqlDatabase(_ConStr);
try
{
string commandText = "SELECT * FROM Settings";
DataSet ds = objSqlDatabase.ExecuteDataSet(System.Data.CommandType.Text, commandText);
if (ds != null)
{
return ds.Tables[0];
}
else
{
return null;
}
}
catch (SqlException)
{
return null;
}
}
/***************** Follow Function Created by levinknight 2006.06.07 *****************/
#region GetAllRules
public string[] GetAllRules()
{
string rules = string.Empty;
DbCommand cmd = dbRules.GetStoredProcCommand("dbo.GetAllRules");
using (DataSet ds = dbRules.ExecuteDataSet(cmd))
{
foreach (DataRow rule in ds.Tables[0].Rows)
{
rules += (string)rule["Name"] + ",";
}
if (rules.Length > 0)
{
rules = rules.Substring(0, rules.Length - 1);
return(rules.Split(','));
}
return(new string[0]);
}
}
public static DataTable GetAssigned(int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
string sql = @" SELECT U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
D.Name AS DeptName, TimeBilled =
CASE
WHEN A.EndDate IS NULL OR A.EndDate>DATEADD(s, 2, CURRENT_TIMESTAMP) THEN 0
ELSE 1
END
FROM AllocableUsers U INNER JOIN AllDepartments D ON U.DeptId=D.DeptId
INNER JOIN Assignments A ON (U.UserId=A.UserId AND A.JobId=@job_id)
WHERE realPerson='Y'
ORDER BY Fullname
";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(cmd).Tables[0].Copy();
t.TableName = "Assigned";
cmd.Dispose();
return t;
}
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
string strDate = dtMonth.Value;
DateTime dt = DateTime.Parse(strDate);
int month = dt.Month;
int year = dt.Year;
Database objDB = new SqlDatabase(M_CONSTR);
DataSet ds = objDB.ExecuteDataSet(CommandType.Text,
@"SELECT
PaperID as 序号, PaperName as 问卷名称,
(select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
(select RegionName from Table_REGION where RegionID = p.RegionID) as 区域,
(select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位,
(select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
(select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
(select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
TrainingDays as 培训天数,
StudentCounts as 学员数量,
SubmitTimes as 问卷数量,
'100' as 满意度总分,
(select cast((convert(decimal,sum(convert(int,qanswer)))*100/convert(decimal,(count(*) * 5))) as decimal(10,2)) from table_result where qid <18 and paperid = p.paperid) as 平均分
FROM Table_PAPER p where YEAR(p.CreateTime)=" + year.ToString()+ @"and MONTH(p.CreateTime)=" +month.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
CreateExcel(ds, year.ToString() + "年" + month.ToString() + "月" + Guid.NewGuid().ToString().Substring(0, 5) + ".xls");
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
public void GetSourceFromTicket()
{
MakeTicket();
TicketProcess ticketProcess = new TicketProcess();
List<Ticket> tickets = ticketProcess.GetTickets();
Source source = tickets[0].DecryptedSource;
string value = source.Value;
string connectionString = source.ConnectionString;
if (source.Provider == "System.Data.SqlClient")
{
Database db = new SqlDatabase(source.ConnectionString);
DataSet ds = db.ExecuteDataSet(CommandType.Text, source.Value);
Assert.IsTrue(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count >= 0);
}
else
{
throw new NotSupportedException();
}
}
public DataTable GetAttributeValueList(int Id, int attrId)
{
StringBuilder sBulider = new StringBuilder();
sBulider.Append("SELECT DISTINCT pvav.Name FROM Nop_ProductVariantAttributeValue pvav");
sBulider.Append(" WHERE pvav.ProductVariantAttributeID ");
sBulider.Append(" IN ( SELECT pvp.ProductVariantAttributeID FROM Nop_ProductVariant_ProductAttribute_Mapping pvp ");
sBulider.Append(" WHERE pvp.ProductAttributeID =" + attrId.ToString() + " AND pvp.ProductVariantID ");
sBulider.Append(" IN( SELECT pv.ProductVariantID FROM Nop_ProductVariant pv WHERE pv.ProductID ");
sBulider.Append(" IN( SELECT ProductId from Nop_Product where ProductId ");
sBulider.Append(" IN (SELECT pc.ProductID from Nop_Product_Category_Mapping pc ");
sBulider.Append(" WHERE pc.CategoryId in (select cate.CategoryID from Nop_Category cate ");
sBulider.Append(" WHERE cate.CategoryID=" + Id.ToString() + " or cate.ParentCategoryID=" + Id.ToString() + "))"+ " and deleted = 0" + ")))");
string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
SqlDatabase db = new SqlDatabase(connectionStr);
DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
DataSet ds = db.ExecuteDataSet(dbCommand);
return ds.Tables[0];
}
private static void MoveAllocations(int userId, int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand cmd = db.GetSqlStringCommand(@"SELECT UserId
FROM AllocableUsers
WHERE DeptId=(SELECT DeptId FROM AllocableUsers WHERE UserId=@user_id)
AND FirstName='TBD' AND realPerson='N'");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
object val = db.ExecuteScalar(cmd);
int tbdUserId = -1;
if (val != null && val != DBNull.Value)
tbdUserId = Convert.ToInt32(val);
if (tbdUserId < 0)
throw new Exception("Could not find TBD user in same department as user with userid " + userId);
cmd = db.GetSqlStringCommand(@" SELECT AllocationId, WeekNumber
FROM Allocations
WHERE JobId=@job_id AND UserId=@tbd_user AND WeekNumber>=dbo.fnGetWeekNumber()+1");
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@tbd_user", DbType.Int32, tbdUserId);
DataTable tbdAllocs = db.ExecuteDataSet(cmd).Tables[0];
//
foreach (DataRow row in tbdAllocs.Rows)
{
//add the existing user's alloc'd minutes to these
//TODO: why would this ever try to put NULL into AnyMins???
cmd = db.GetSqlStringCommand(@" UPDATE Allocations
SET AnyMins=IsNull(AnyMins,0)+IsNull((SELECT IsNull(AnyMins,0) FROM Allocations
WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num),0)
WHERE AllocationId=@alloc_id");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
db.AddInParameter(cmd, "@alloc_id", DbType.Int32, row["AllocationId"]);
db.ExecuteNonQuery(cmd);
//now delete the allocation for the user
cmd = db.GetSqlStringCommand(@" DELETE FROM Allocations WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
db.ExecuteNonQuery(cmd);
}
//we've now moved any colliding allocation records...do an update of the user-id on the rest
cmd = db.GetSqlStringCommand(@" UPDATE Allocations
SET UserId=@tbd_id, AllocNote=''
WHERE JobId=@job_id AND UserId=@user_id AND WeekNumber>=dbo.fnGetWeekNumber()+1");
db.AddInParameter(cmd, "@tbd_id", DbType.Int32, tbdUserId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.ExecuteNonQuery(cmd);
}
private static DataSet ExecuteDataSet(SqlDatabase database, DbCommand command, MethodType type)
{
switch (type)
{
case MethodType.One:
{
// No need to open the connection; just make the call.
return database.ExecuteDataSet(command);
}
case MethodType.Two:
{
return database.ExecuteDataSet(CommandType.Text, "SELECT * FROM Employees");
}
case MethodType.Three:
{
return database.ExecuteDataSet("SalesByCategory", "Beverages", "1998");
}
default:
throw new NotSupportedException();
}
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="commandType">Type of the command.</param>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
public override DataSet ExecuteDataSet(CommandType commandType, string commandText)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteDataSet(commandType, commandText);
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="commandWrapper">The command wrapper.</param>
/// <returns></returns>
public override DataSet ExecuteDataSet(DbCommand commandWrapper)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteDataSet(commandWrapper);
}
/// <summary>
/// Executes the data set.
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <returns></returns>
public override DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteDataSet(storedProcedureName, parameterValues);
}
public DataTable GetSpecList(int Id, int SpecificationAttributeID)
{
StringBuilder sBulider = new StringBuilder();
sBulider.Append("SELECT NSAO.SpecificationAttributeOptionID,NSAO.Name FROM Nop_SpecificationAttributeOption NSAO");
sBulider.Append(" WHERE NSAO.SpecificationAttributeID =" + SpecificationAttributeID.ToString());
sBulider.Append(" AND NSAO.SpecificationAttributeOptionID");
sBulider.Append(" IN( SELECT DISTINCT NPSAM.SpecificationAttributeOptionID FROM Nop_Product_SpecificationAttribute_Mapping NPSAM WHERE NPSAM.AllowFiltering=1 AND NPSAM.ProductID ");
sBulider.Append(" IN (SELECT NPCM.ProductID from Nop_Product_Category_Mapping NPCM ");
sBulider.Append(" WHERE NPCM.CategoryId in (select NC.CategoryID from Nop_Category NC ");
sBulider.Append(" where NC.CategoryID=" + Id.ToString() + " or NC.ParentCategoryID=" + Id.ToString() + ")))");
string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
SqlDatabase db = new SqlDatabase(connectionStr);
DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
DataSet ds = db.ExecuteDataSet(dbCommand);
return ds.Tables[0];
}
public DataTable GetAttributeValueList(int attrId)
{
StringBuilder sBulider = new StringBuilder();
sBulider.Append("select distinct Name from Nop_ProductVariantAttributeValue ");
sBulider.Append("where ProductVariantAttributeID ");
sBulider.Append("in (");
sBulider.Append("select ProductVariantAttributeID from Nop_ProductVariant_ProductAttribute_Mapping ");
sBulider.Append("where ProductAttributeID = " + attrId.ToString() + " and ProductVariantID ");
sBulider.Append("in (");
sBulider.Append("select ProductVariantId from Nop_ProductVariant where ProductId ");
sBulider.Append("in (");
sBulider.Append("SELECT TOP 30 ProductId from Nop_Product where deleted = 0 order by CreatedOn desc )))");
string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
SqlDatabase db = new SqlDatabase(connectionStr);
DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
DataSet ds = db.ExecuteDataSet(dbCommand);
return ds.Tables[0];
}
public DataTable GetSpecList(int SpecificationAttributeID)
{
StringBuilder sBulider = new StringBuilder();
sBulider.Append("select SpecificationAttributeOptionID, Name from Nop_SpecificationAttributeOption");
sBulider.Append(" where SpecificationAttributeID =" + SpecificationAttributeID.ToString());
sBulider.Append(" and SpecificationAttributeOptionID");
sBulider.Append(" in (select distinct SpecificationAttributeOptionID from Nop_Product_SpecificationAttribute_Mapping where AllowFiltering=1 AND ProductID ");
sBulider.Append(" in (select top 30 productId from Nop_Product where Deleted = 0 order by CreatedOn desc))");
string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
SqlDatabase db = new SqlDatabase(connectionStr);
DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
DataSet ds = db.ExecuteDataSet(dbCommand);
return ds.Tables[0];
}
private DataSet GetCachedData(string CacheKeyName, int NoOfTopRecords, string SQLQueryOrSP, CommandType cmdType, params SqlParameter[] commandParameters)
{
DataSet dsCached = new DataSet();
try
{
if (HttpContext.Current.Cache.Get(CacheKeyName) != null)
{
dsCached = (DataSet)HttpContext.Current.Cache[CacheKeyName];
}
else
{
Database dbObj = new SqlDatabase(this.connectionStr);
DbCommand command = dbObj.GetStoredProcCommand(SQLQueryOrSP);
if (commandParameters != null)
{
for (int index = 0; index < commandParameters.Length; index++)
{
dbObj.AddInParameter(command, commandParameters[index].ParameterName, commandParameters[index].DbType, commandParameters[index].Value);
}
}
dsCached = dbObj.ExecuteDataSet(command);
HttpContext.Current.Cache.Insert(CacheKeyName, dsCached, null, dtCacheTime, TimeSpan.Zero);
}
if (NoOfTopRecords > 0)
{
DataSet ds = new DataSet();
ds = dsCached.Clone();
for (int j = 0; j < NoOfTopRecords; j++)
{
ds.Tables[0].Rows.Add(dsCached.Tables[0].Rows[j].ItemArray);
}
dsCached = ds;
}
}
catch (SqlException ex)
{
}
catch (Exception ex)
{
}
return dsCached;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Database objDB = new SqlDatabase(M_CONSTR);
int n = Int32.Parse(Request["paperid"].ToString());
string pTitle = objDB.ExecuteScalar(CommandType.Text, @"SELECT PaperName FROM Table_PAPER where paperid=" + n.ToString()).ToString();
tt.Text = pTitle;
lblPaperNm.Text = pTitle;
Label1.Text = getval(n, 1, 1, false);
Label2.Text = getval(n, 1, 1, true);
Label3.Text = getval(n, 1, 2, false);
Label4.Text = getval(n, 1, 2, true);
Label5.Text = getval(n, 1, 3, false);
Label6.Text = getval(n, 1, 3, true);
Label7.Text = getval(n, 1, 4, false);
Label8.Text = getval(n, 1, 4, true);
Label9.Text = getval(n, 1, 5, false);
Label10.Text = getval(n, 1, 5, true);
Label11.Text = getval(n, 2, 1, false);
Label12.Text = getval(n, 2, 1, true);
Label13.Text = getval(n, 2, 2, false);
Label14.Text = getval(n, 2, 2, true);
Label15.Text = getval(n, 2, 3, false);
Label16.Text = getval(n, 2, 3, true);
Label17.Text = getval(n, 2, 4, false);
Label18.Text = getval(n, 2, 4, true);
Label19.Text = getval(n, 2, 5, false);
Label20.Text = getval(n, 2, 5, true);
Label21.Text = getval(n, 3, 1, false);
Label22.Text = getval(n, 3, 1, true);
Label23.Text = getval(n, 3, 2, false);
Label24.Text = getval(n, 3, 2, true);
Label25.Text = getval(n, 3, 3, false);
Label26.Text = getval(n, 3, 3, true);
Label27.Text = getval(n, 3, 4, false);
Label28.Text = getval(n, 3, 4, true);
Label29.Text = getval(n, 3, 5, false);
Label30.Text = getval(n, 3, 5, true);
Label31.Text = getval(n, 4, 1, false);
Label32.Text = getval(n, 4, 1, true);
Label33.Text = getval(n, 4, 2, false);
Label34.Text = getval(n, 4, 2, true);
Label35.Text = getval(n, 4, 3, false);
Label36.Text = getval(n, 4, 3, true);
Label37.Text = getval(n, 4, 4, false);
Label38.Text = getval(n, 4, 4, true);
Label39.Text = getval(n, 4, 5, false);
Label40.Text = getval(n, 4, 5, true);
Label41.Text = getval(n, 5, 1, false);
Label42.Text = getval(n, 5, 1, true);
Label43.Text = getval(n, 5, 2, false);
Label44.Text = getval(n, 5, 2, true);
Label45.Text = getval(n, 5, 3, false);
Label46.Text = getval(n, 5, 3, true);
Label47.Text = getval(n, 5, 4, false);
Label48.Text = getval(n, 5, 4, true);
Label49.Text = getval(n, 5, 5, false);
Label50.Text = getval(n, 5, 5, true);
Label51.Text = getval(n, 6, 1, false);
Label52.Text = getval(n, 6, 1, true);
Label53.Text = getval(n, 6, 2, false);
Label54.Text = getval(n, 6, 2, true);
Label55.Text = getval(n, 6, 3, false);
Label56.Text = getval(n, 6, 3, true);
Label57.Text = getval(n, 6, 4, false);
Label58.Text = getval(n, 6, 4, true);
Label59.Text = getval(n, 6, 5, false);
Label60.Text = getval(n, 6, 5, true);
Label61.Text = getval(n, 7, 1, false);
Label62.Text = getval(n, 7, 1, true);
Label63.Text = getval(n, 7, 2, false);
Label64.Text = getval(n, 7, 2, true);
Label65.Text = getval(n, 7, 3, false);
Label66.Text = getval(n, 7, 3, true);
Label67.Text = getval(n, 7, 4, false);
Label68.Text = getval(n, 7, 4, true);
Label69.Text = getval(n, 7, 5, false);
Label70.Text = getval(n, 7, 5, true);
Label71.Text = getval(n, 8, 1, false);
Label72.Text = getval(n, 8, 1, true);
Label73.Text = getval(n, 8, 2, false);
Label74.Text = getval(n, 8, 2, true);
Label75.Text = getval(n, 8, 3, false);
Label76.Text = getval(n, 8, 3, true);
Label77.Text = getval(n, 8, 4, false);
Label78.Text = getval(n, 8, 4, true);
Label79.Text = getval(n, 8, 5, false);
Label80.Text = getval(n, 8, 5, true);
Label81.Text = getval(n, 9, 1, false);
Label82.Text = getval(n, 9, 1, true);
Label83.Text = getval(n, 9, 2, false);
Label84.Text = getval(n, 9, 2, true);
Label85.Text = getval(n, 9, 3, false);
Label86.Text = getval(n, 9, 3, true);
Label87.Text = getval(n, 9, 4, false);
Label88.Text = getval(n, 9, 4, true);
Label89.Text = getval(n, 9, 5, false);
Label90.Text = getval(n, 9, 5, true);
Label91.Text = getval(n, 10, 1, false);
Label92.Text = getval(n, 10, 1, true);
Label93.Text = getval(n, 10, 2, false);
Label94.Text = getval(n, 10, 2, true);
Label95.Text = getval(n, 10, 3, false);
Label96.Text = getval(n, 10, 3, true);
Label97.Text = getval(n, 10, 4, false);
Label98.Text = getval(n, 10, 4, true);
Label99.Text = getval(n, 10, 5, false);
Label100.Text = getval(n, 10, 5, true);
Label101.Text = getval(n, 11, 1, false);
Label102.Text = getval(n, 11, 1, true);
Label103.Text = getval(n, 11, 2, false);
Label104.Text = getval(n, 11, 2, true);
Label105.Text = getval(n, 11, 3, false);
Label106.Text = getval(n, 11, 3, true);
Label107.Text = getval(n, 11, 4, false);
Label108.Text = getval(n, 11, 4, true);
Label109.Text = getval(n, 11, 5, false);
Label110.Text = getval(n, 11, 5, true);
Label111.Text = getval(n, 12, 1, false);
Label112.Text = getval(n, 12, 1, true);
Label113.Text = getval(n, 12, 2, false);
Label114.Text = getval(n, 12, 2, true);
Label115.Text = getval(n, 12, 3, false);
Label116.Text = getval(n, 12, 3, true);
Label117.Text = getval(n, 12, 4, false);
Label118.Text = getval(n, 12, 4, true);
Label119.Text = getval(n, 12, 5, false);
Label120.Text = getval(n, 12, 5, true);
Label121.Text = getval(n, 13, 1, false);
Label122.Text = getval(n, 13, 1, true);
Label123.Text = getval(n, 13, 2, false);
Label124.Text = getval(n, 13, 2, true);
Label125.Text = getval(n, 13, 3, false);
Label126.Text = getval(n, 13, 3, true);
Label127.Text = getval(n, 13, 4, false);
Label128.Text = getval(n, 13, 4, true);
Label129.Text = getval(n, 13, 5, false);
Label130.Text = getval(n, 13, 5, true);
Label131.Text = getval(n, 14, 1, false);
Label132.Text = getval(n, 14, 1, true);
Label133.Text = getval(n, 14, 2, false);
Label134.Text = getval(n, 14, 2, true);
Label135.Text = getval(n, 14, 3, false);
Label136.Text = getval(n, 14, 3, true);
Label137.Text = getval(n, 14, 4, false);
Label138.Text = getval(n, 14, 4, true);
Label139.Text = getval(n, 14, 5, false);
Label140.Text = getval(n, 14, 5, true);
Label141.Text = getval(n, 15, 1, false);
Label142.Text = getval(n, 15, 1, true);
Label143.Text = getval(n, 15, 2, false);
Label144.Text = getval(n, 15, 2, true);
Label145.Text = getval(n, 15, 3, false);
Label146.Text = getval(n, 15, 3, true);
Label147.Text = getval(n, 15, 4, false);
Label148.Text = getval(n, 15, 4, true);
Label149.Text = getval(n, 15, 5, false);
Label150.Text = getval(n, 15, 5, true);
Label151.Text = getval(n, 16, 1, false);
Label152.Text = getval(n, 16, 1, true);
Label153.Text = getval(n, 16, 2, false);
Label154.Text = getval(n, 16, 2, true);
Label155.Text = getval(n, 16, 3, false);
Label156.Text = getval(n, 16, 3, true);
Label157.Text = getval(n, 16, 4, false);
Label158.Text = getval(n, 16, 4, true);
Label159.Text = getval(n, 16, 5, false);
Label160.Text = getval(n, 16, 5, true);
Label161.Text = getval(n, 17, 1, false);
Label162.Text = getval(n, 17, 1, true);
Label163.Text = getval(n, 17, 2, false);
Label164.Text = getval(n, 17, 2, true);
Label165.Text = getval(n, 17, 3, false);
Label166.Text = getval(n, 17, 3, true);
Label167.Text = getval(n, 17, 4, false);
Label168.Text = getval(n, 17, 4, true);
Label169.Text = getval(n, 17, 5, false);
Label170.Text = getval(n, 17, 5, true);
DataSet ds = objDB.ExecuteDataSet(CommandType.Text, @"SELECT row_number() over (order by uid) as rowNm
,[Q1Answer]
,[Q2Answer]
,[Q3Answer]
,[Q4Answer]
,[Q5Answer]
,[Q6Answer]
,[Q7Answer]
,[Q8Answer]
,[Q9Answer]
,[Q10Answer]
,[Q11Answer]
,[Q12Answer]
,[Q13Answer]
,[Q14Answer]
,[Q15Answer]
,[Q16Answer]
,[Q17Answer]
,[QSum]
FROM [Table_sumary]
where pid=" + n.ToString());
dcwjDataSet_detail dsd = new dcwjDataSet_detail();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
dsd.dtable.AdddtableRow(
ds.Tables[0].Rows[i][0].ToString(),
ds.Tables[0].Rows[i][1].ToString(),
ds.Tables[0].Rows[i][2].ToString(),
ds.Tables[0].Rows[i][3].ToString(),
ds.Tables[0].Rows[i][4].ToString(),
ds.Tables[0].Rows[i][5].ToString(),
ds.Tables[0].Rows[i][6].ToString(),
ds.Tables[0].Rows[i][7].ToString(),
ds.Tables[0].Rows[i][8].ToString(),
ds.Tables[0].Rows[i][9].ToString(),
ds.Tables[0].Rows[i][10].ToString(),
ds.Tables[0].Rows[i][11].ToString(),
ds.Tables[0].Rows[i][12].ToString(),
ds.Tables[0].Rows[i][13].ToString(),
ds.Tables[0].Rows[i][14].ToString(),
ds.Tables[0].Rows[i][15].ToString(),
ds.Tables[0].Rows[i][16].ToString(),
ds.Tables[0].Rows[i][17].ToString(),
ds.Tables[0].Rows[i][18].ToString());
}
}
DataSet dsInfo = objDB.ExecuteDataSet(CommandType.Text, @"SELECT
PaperName as 问卷名称,
(select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
(select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
(select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
(select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
TrainingDays as 培训天数,
(select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位
FROM Table_PAPER p where p.PaperID="+n.ToString());
if (dsInfo != null && dsInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsInfo.Tables[0].Rows.Count; i++)
{
dsd.PaperInfo.AddPaperInfoRow(
dsInfo.Tables[0].Rows[i][0].ToString(),
dsInfo.Tables[0].Rows[i][1].ToString(),
dsInfo.Tables[0].Rows[i][2].ToString(),
dsInfo.Tables[0].Rows[i][3].ToString(),
dsInfo.Tables[0].Rows[i][4].ToString(),
dsInfo.Tables[0].Rows[i][5].ToString(),
dsInfo.Tables[0].Rows[i][6].ToString());
}
}
ReportDataSource rds0 = new ReportDataSource("ds_sumary", (DataTable)dsd.dtable);
ReportDataSource rds1 = new ReportDataSource("ds_pInfo", (DataTable)dsd.PaperInfo);
ReportViewer1.LocalReport.DataSources.Add(rds0);
ReportViewer1.LocalReport.DataSources.Add(rds1);
ReportViewer1.LocalReport.Refresh();
}
}