//private string alias_table_name = null;
internal StringBuilder SQLCreateView_InDataBase(List<SQLTable> lTable)
{
StringBuilder SQL_View = new StringBuilder("");
try
{
if (this.m_Fkey.Count > 0)
{
jListClear();
if (this.m_Table_View == null)
{
m_Table_View = new Table_View();
}
m_Table_View.View_Name = TableName + const_DataBaseViewSuffix;
SQL_View.Append("\n\nCREATE VIEW " + ViewName + " AS ");
SQL_View.Append("\nSELECT ");
SQL_View.Append("\n " + TableName + ".ID");
SQLTable xtbl = new SQLTable(this);
xtbl.CreateTableTree(lTable);
string alias_table_name = this.TableName;
foreach (Column col in xtbl.Column)
{
if (!col.IsIdentity)
{
if (col.fKey != null)
{
if (col.fKey.fTable != null)
{
col.fKey.fTable.BasicColumns(ref SQL_View, ref m_Table_View, alias_table_name);
if (!m_Table_View.defined)
{
string Column_Name_In_View = col.ownerTable.TableName + VIEW_TableName2ColumnName_SEPARATOR + col.Name;
Table_View.ColumnNames x_Table_View_ColumnNames = new Table_View.ColumnNames();
x_Table_View_ColumnNames.Name = Column_Name_In_View;
x_Table_View_ColumnNames.Name_in_language = col.Name_in_language;
this.m_Table_View.View_ColumnNames_List.Add(x_Table_View_ColumnNames);
}
}
}
else
{
string Column_Name_In_View = col.ownerTable.TableName + VIEW_TableName2ColumnName_SEPARATOR + col.Name;
SQL_View.Append(",\n " + col.ownerTable.TableName + "." + col.Name + " AS " + Column_Name_In_View);
if (!m_Table_View.defined)
{
Table_View.ColumnNames x_Table_View_ColumnNames = new Table_View.ColumnNames();
x_Table_View_ColumnNames.Name = Column_Name_In_View;
x_Table_View_ColumnNames.Name_in_language = col.Name_in_language;
this.m_Table_View.View_ColumnNames_List.Add(x_Table_View_ColumnNames);
}
}
}
}
m_Table_View.defined = true;
SQL_View.Append("\nFROM " + TableName);
foreach (Column col in xtbl.Column)
{
if (!col.IsIdentity)
{
if (col.fKey != null)
{
if (col.fKey.fTable != null)
{
col.fKey.fTable.GetJoins(col.nulltype, this, alias_table_name, alias_table_name);
}
}
}
}
CreateJoins(ref SQL_View, false);
}
}
catch (Exception Ex)
{
LogFile.Error.Show("ERROR:SQLTable:SQLCreateView_InDataBase:Exception=" + Ex.Message);
}
return SQL_View;
}
/// <summary>
/// 获取设计表详情【学生答题的初始化答卷】
/// </summary>
/// <param name="context"></param>
public void Get_Eva_TableDetail(HttpContext context)
{
int intSuccess = (int)errNum.Success;
HttpRequest Request = context.Request;
//表格ID
int table_Id = RequestHelper.int_transfer(Request, "table_Id");
int RoomID = RequestHelper.int_transfer(Request, "RoomID");
int ReguID = RequestHelper.int_transfer(Request, "ReguID");
string UserID = RequestHelper.string_transfer(Request, "UserID");
//没有教学反馈
bool no_eduType = RequestHelper.bool_transfer(Request, "no_eduType");
Table_View table_view = new Table_View();
try
{
//首先指定试卷
var table = Constant.Eva_Table_List.FirstOrDefault(i => i.Id == table_Id);
if (table != null)
{
table_view.Table_Id = (int)table.Id;
table_view.IsEnable = (int)table.IsEnable;
table_view.Name = table.Name;
table_view.IsScore = (int)table.IsScore;
//搜集试卷题
List <Eva_TableDetail_S> details = (from det in Constant.Eva_TableDetail_List
where det.Eva_table_Id == table_Id
orderby det.Id
select new Eva_TableDetail_S()
{
Id = det.Id,
Indicator_Id = det.Indicator_Id,
IndicatorType_Id = det.IndicatorType_Id,
Eva_table_Id = det.Eva_table_Id,
IndicatorType_Name = det.IndicatorType_Name,
Name = det.Name,
OptionA = det.OptionA,
OptionA_S = det.OptionA_S,
OptionB = det.OptionB,
OptionB_S = det.OptionB_S,
OptionC = det.OptionC,
OptionC_S = det.OptionC_S,
OptionD = det.OptionD,
OptionD_S = det.OptionD_S,
OptionE = det.OptionE,
OptionE_S = det.OptionE_S,
OptionF = det.OptionF,
OptionF_S = det.OptionF_S,
QuesType_Id = det.QuesType_Id,
RootID = det.RootID,
Root = det.Root,
Sort = det.Sort,
Type = det.Type,
OptionF_S_Max = det.OptionF_S_Max,
}).ToList();
if (RoomID > 0)
{
var model = (from r in Constant.CourseRoom_List
where r.Id == RoomID
join section in Constant.StudySection_List on r.StudySection_Id equals section.Id
join regu in Constant.Eva_Regular_List on ReguID equals regu.Id
select new { r, section, regu }).ToList();
if (model.Count > 0)
{
var room = model[0].r;
var section = model[0].section;
var regu = model[0].regu;
table_view.Info = new
{
SectionID = room.StudySection_Id,
DisplayName = section.DisPlayName,
ReguID = regu.Id,
ReguName = regu.Name,
CourseID = room.Coures_Id,
CourseName = room.CouresName,
TeacherUID = room.TeacherUID,
TeacherName = room.TeacherName,
DepartmentName = room.DepartmentName,
RoomID = RoomID,
ClassName = room.ClassName,
ClassID = room.ClassID,
IsInClass = Constant.Class_StudentInfo_List.Count(i => i.UniqueNo == UserID && i.Class_Id == room.ClassID) > 0 ? true : false,
};
}
}
//表详情
table_view.Table_Detail_Dic_List = (from ps in details
group ps by ps.RootID
into g
select new Table_Detail_Dic {
Root = g.FirstOrDefault(i => i.RootID == g.Key).Root, Eva_TableDetail_List = g.ToList()
}).ToList();
//表头
table_view.Table_Header_List = (from header in Constant.Eva_Table_Header_List
where header.Table_Id == table.Id
orderby header.Sort
select new
Table_Header {
Header = header.Name_Key, CustomCode = header.Custom_Code, Value = header.Name_Value, Type = (int)header.Type, Id = (int)header.Id
}).ToList();
}
//返回所有表格数据
jsonModel = JsonModel.get_jsonmodel(intSuccess, "success", table_view);
}
catch (Exception ex)
{
LogHelper.Error(ex);
}
finally
{
//无论后端出现什么问题，都要给前端有个通知【为防止jsonModel 为空 ,全局字段 jsonModel 特意声明之后进行初始化】
context.Response.Write("{\"result\":" + Constant.jss.Serialize(jsonModel) + "}");
}
}
private void BasicColumns(ref StringBuilder SQL_View, ref Table_View parent_Table_View,string AliasTableName)
{
if (this.m_Table_View == null)
{
this.m_Table_View = new Table_View();
}
string alias_table_name = AliasTableName + SQLTable.VIEW_TableName_SEPARATOR + this.TableName_Abbreviation;
foreach (Column col in Column)
{
if (col.IsIdentity)
{
string Column_Name_In_View = alias_table_name + SQLTable.VIEW_TableName2ColumnName_SEPARATOR + col.Name;
SQL_View.Append(",\n" + alias_table_name + "." + col.Name + " AS " + Column_Name_In_View);
if (!this.m_Table_View.defined)
{
Table_View.ColumnNames x_Table_View_ColumnNames = new Table_View.ColumnNames();
x_Table_View_ColumnNames.Name = Column_Name_In_View;
x_Table_View_ColumnNames.Name_in_language = col.Name_in_language;
this.m_Table_View.View_ColumnNames_List.Add(x_Table_View_ColumnNames);
}
}
else
{
if (col.fKey != null)
{
if (col.fKey.fTable != null)
{
col.fKey.fTable.BasicColumns(ref SQL_View, ref m_Table_View, alias_table_name);
if (!m_Table_View.defined)
{
string Column_Name_In_View = alias_table_name + SQLTable.VIEW_TableName2ColumnName_SEPARATOR + col.Name;
Table_View.ColumnNames x_Table_View_ColumnNames = new Table_View.ColumnNames();
x_Table_View_ColumnNames.Name = Column_Name_In_View;
x_Table_View_ColumnNames.Name_in_language = col.Name_in_language;
this.m_Table_View.View_ColumnNames_List.Add(x_Table_View_ColumnNames);
}
}
}
else
{
string Column_Name_In_View = alias_table_name + SQLTable.VIEW_TableName2ColumnName_SEPARATOR + col.Name;
SQL_View.Append(",\n" + alias_table_name + "." + col.Name + " AS " + Column_Name_In_View);
if (!this.m_Table_View.defined)
{
Table_View.ColumnNames x_Table_View_ColumnNames = new Table_View.ColumnNames();
x_Table_View_ColumnNames.Name = Column_Name_In_View;
x_Table_View_ColumnNames.Name_in_language = col.Name_in_language;
this.m_Table_View.View_ColumnNames_List.Add(x_Table_View_ColumnNames);
}
}
}
}
this.m_Table_View.defined = true;
parent_Table_View.View_List.Add(this.m_Table_View);
}