private object[] LoadDataSource()
{
string byTimeType = UCGroupConditions1.UserSelectByTimeType;
string exceptionOrDuty = UCGroupConditions1.UserExceptionOrDuty;
string dataField = "ExceptionCode";
if (exceptionOrDuty == NewReportExceptionOrDuty.Exception)
{
dataField = "ExceptionDesc";
}
else if (exceptionOrDuty == NewReportExceptionOrDuty.Duty)
{
dataField = "DutyDesc";
}
//内部SQL
ReportSQLEngine engineInner = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engineInner.DetailedCoreTable = GetCoreTableDetail();
engineInner.Formular = "SUM(lostmanhour) AS lostmanhour";
engineInner.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTableInner, byTimeType, true, 0);
engineInner.GroupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTableInner, "X");
engineInner.GroupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTableInner, "Y");
if (engineInner.GroupFieldsY.Trim().Length > 0)
{
engineInner.GroupFieldsY += ",";
}
engineInner.GroupFieldsY += dataField;
//外部SQL
string groupFields = string.Empty;
groupFields += this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "X");
if (groupFields.IndexOf("dweek") == 0 || groupFields.IndexOf("dmonth") == 0)
{
groupFields += ",year";
}
groupFields += "," + dataField;
string groupFieldsY = this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "Y");
if (groupFieldsY.Trim().Length > 0)
{
groupFields += "," + groupFieldsY;
}
ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engine.DetailedCoreTable = engineInner.GetReportSQL();
engine.Formular = groupFields + ",**.lostmanhour, **.lostmanhour / SUM(**.lostmanhour) OVER() AS lostmanhourpercent";
engine.WhereCondition = string.Empty;
engine.GroupFieldsX = string.Empty;
engine.GroupFieldsY = string.Empty;
engine.OrderFields = groupFields;
return(engine.GetReportDataSource(byTimeType, 0));;
}
private object[] LoadDataSource(bool isForCompare, bool roundDate)
{
string inputOutput = UCWhereConditions1.UserSelectInputOutput;
string byTimeType = UCGroupConditions1.UserSelectByTimeType;
string compareType = UCGroupConditions1.UserSelectCompareType;
string completeType = UCGroupConditions1.UserSelectCompleteType;
if (!isForCompare)
{
compareType = string.Empty;
}
//用于环比同期比时的修改时间过滤条件
int dateAdjust = 0;
if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0)
{
dateAdjust = -12;
}
else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0)
{
dateAdjust = -1;
}
bool bigSSChecked = UCGroupConditions1.BigSSChecked;
bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked;
bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked;
//拼内部SQL
string groupFieldsXInner = this.UCGroupConditions1.GetGroupFieldList(preferredTableInner, "X");
string groupFieldsYInner = this.UCGroupConditions1.GetGroupFieldList(preferredTableInner, "Y");
if (groupFieldsYInner.Trim().Length > 0)
{
groupFieldsYInner += ",";
}
groupFieldsYInner += "tblmesentitylist.tpcode";
if (groupFieldsYInner.IndexOf("shiftday") < 0 && byTimeType != NewReportByTimeType.ShiftDay)
{
groupFieldsYInner += ",**.shiftday";
}
ReportSQLEngine engineInner = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engineInner.DetailedCoreTable = GetDetailedCoreTable();
engineInner.Formular = GetFormularInner(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engineInner.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTableInner, byTimeType, roundDate, dateAdjust);
engineInner.GroupFieldsX = groupFieldsXInner;
engineInner.GroupFieldsY = groupFieldsYInner;
//拼外部SQL
string groupFieldsX = this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "X");
string groupFieldsY = this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "Y");
ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engine.DetailedCoreTable = engineInner.GetReportSQL();
engine.Formular = GetFormular(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engine.WhereCondition = string.Empty;
engine.HavingCondition = GetHavingCondition(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engine.GroupFieldsX = groupFieldsX;
engine.GroupFieldsY = groupFieldsY;
return(engine.GetReportDataSource(byTimeType, dateAdjust));;
}
private object[] LoadDataSource(bool isForCompare, bool roundDate)
{
string inputOutput = UCWhereConditions1.UserSelectInputOutput;
string byTimeType = UCGroupConditions1.UserSelectByTimeType;
string compareType = UCGroupConditions1.UserSelectCompareType;
string completeType = UCGroupConditions1.UserSelectCompleteType;
if (!isForCompare)
{
compareType = string.Empty;
}
//用于环比同期比时的修改时间过滤条件
int dateAdjust = 0;
if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0)
{
dateAdjust = -12;
}
else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0)
{
dateAdjust = -1;
}
bool bigSSChecked = UCGroupConditions1.BigSSChecked;
bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked;
bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked;
//内部SQL
string groupFieldsYInner = this.UCGroupConditions1.GetGroupFieldList(preferredTableInner, "Y");
if (groupFieldsYInner.Trim().Length > 0)
{
groupFieldsYInner += ",";
}
groupFieldsYInner += "**.ecsgcode";
ReportSQLEngine engineInner = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engineInner.DetailedCoreTable = GetCoreTableDetail();
engineInner.Formular = GetFormular(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engineInner.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTableInner, byTimeType, roundDate, dateAdjust);
engineInner.GroupFieldsX = string.Empty;;
engineInner.GroupFieldsY = groupFieldsYInner;
//外部SQL
string groupFields = string.Empty;
groupFields += this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "Y");
if (groupFields.Trim().Length > 0)
{
groupFields += ",";
}
groupFields += "**.ecsgcode";
ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engine.DetailedCoreTable = engineInner.GetReportSQL();
engine.Formular = groupFields + ",**.errorcount, **.errorcount / SUM(**.errorcount) OVER() AS errorpercent";
engine.WhereCondition = string.Empty;
engine.GroupFieldsX = string.Empty;
engine.GroupFieldsY = string.Empty;
engine.OrderFields = groupFields;
return(engine.GetReportDataSource(byTimeType, dateAdjust));;
}
private string GetCoreTableDetail(bool isForCompare, bool roundDate)
{
string returnValue = string.Empty;
string inputOutput = UCWhereConditions1.UserSelectInputOutput;
string byTimeType = UCGroupConditions1.UserSelectByTimeType;
string compareType = UCGroupConditions1.UserSelectCompareType;
string completeType = UCGroupConditions1.UserSelectCompleteType;
if (!isForCompare)
{
compareType = string.Empty;
}
//用于环比同期比时的修改时间过滤条件
int dateAdjust = 0;
if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0)
{
dateAdjust = -12;
}
else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0)
{
dateAdjust = -1;
}
bool bigSSChecked = UCGroupConditions1.BigSSChecked;
bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked;
bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked;
string groupFieldsXForError = this.UCGroupConditions1.GetGroupFieldList(preferredTableForError, "X");
string groupFieldsYForError = this.UCGroupConditions1.GetGroupFieldList(preferredTableForError, "Y");
ReportSQLEngine engineForError = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engineForError.CoreTableAlias = GetCoreTableAliasForError();
engineForError.DetailedCoreTable = GetCoreTableDetailForError();
engineForError.Formular = GetFormularForError(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engineForError.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTableForError, byTimeType, roundDate, dateAdjust);
engineForError.GroupFieldsX = groupFieldsXForError;
engineForError.GroupFieldsY = groupFieldsYForError;
string errorSQL = engineForError.GetReportSQL();
string groupFieldsXForOutput = this.UCGroupConditions1.GetGroupFieldList(preferredTableForOutput, "X");
string groupFieldsYForOutput = this.UCGroupConditions1.GetGroupFieldList(preferredTableForOutput, "Y");
ReportSQLEngine engineForOutput = new ReportSQLEngine(this.DataProvider, this.languageComponent1);
engineForOutput.CoreTableAlias = GetCoreTableAliasForOutput();
engineForOutput.Formular = GetFormularForOutput(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked);
engineForOutput.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTableForOutput, byTimeType, roundDate, dateAdjust);
engineForOutput.GroupFieldsX = groupFieldsXForOutput;
engineForOutput.GroupFieldsY = groupFieldsYForOutput;
string outputSQL = engineForOutput.GetReportSQL();
//抓到SQL后,把错误的地方替换掉
string replaceWhere = "AND tblline2crew.sscode = tblrptsoqty.sscode AND tblline2crew.shiftcode = tblrptsoqty.shiftcode";
string replaceAt = "";
if (outputSQL.Contains(replaceWhere))
{
replaceAt = " left outer join tblmesentitylist tblmesentitylist on tblmesentitylist.serial = tblrptsoqty.tblmesentitylist_serial "
+ "AND tblline2crew.shiftcode = tblmesentitylist.shiftcode AND tblline2crew.sscode = tblmesentitylist.sscode ";
outputSQL = outputSQL.Replace(replaceWhere, replaceAt);
}
if (outputSQL.Contains("tblitemclass tblitemclass ON tblitemclass.firstclass = tblrptsoqty.firstclass"))
{
replaceWhere = "tblitemclass tblitemclass ON tblitemclass.firstclass = tblrptsoqty.firstclass";
replaceAt = " tblmaterial tblmaterial ON tblmaterial.mcode = tblrptsoqty.itemcode "
+ "LEFT OUTER JOIN tblitemclass tblitemclass ON tblitemclass.itemgroup = tblmaterial.mgroup ";
outputSQL = outputSQL.Replace(replaceWhere, replaceAt);
}
string groupFieldAliasX = this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "X");
string groupFieldAliasY = this.UCGroupConditions1.GetGroupFieldAliasList(preferredTable, "Y");
returnValue += "SELECT " + GetFieldWithTableName("a", groupFieldAliasX, groupFieldAliasY) + ",output,errorcount " + "\r\n";
returnValue += "FROM " + "\r\n";
returnValue += "(" + outputSQL + ") a " + "\r\n";
returnValue += "LEFT OUTER JOIN (" + errorSQL + ") b " + "\r\n";
returnValue += GetJoinOnSQL("a", "b", groupFieldAliasX, groupFieldAliasY);
return(returnValue);
}
