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; } bool bigSSChecked = UCGroupConditions1.BigSSChecked; bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked; bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); engine.DetailedCoreTable = GetCoreTableDetail(); engine.Formular = GetFormular(); engine.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, 0); engine.GroupFieldsX = groupFieldsX; engine.GroupFieldsY = groupFieldsY; return(engine.GetReportDataSource(byTimeType, 0)); }
private object[] LoadDataSource() { int dateAdjust = 0; bool roundDate = false; string byTimeType = UCGroupConditions1.UserSelectByTimeType; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); ReportSQLHelper sqlHelper = new ReportSQLHelper(this.DataProvider); string sql = sqlHelper.GetPerformanceReportSQL( UCGroupConditions1.FacCodeChecked, UCGroupConditions1.FacCodeChecked || !UCGroupConditions1.SSChecked, UCGroupConditions1.ExcludeReworkOutputChecked, UCGroupConditions1.ExcludeLostManHourChecked, UCGroupConditions1.ShowIncludeIndirectManHour && UCGroupConditions1.IncludeIndirectManHourChecked, UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, dateAdjust), groupFieldsX, groupFieldsY, sqlHelper.GetFormularForManHourPerProduct(UCGroupConditions1.ExcludeLostManHourChecked, UCGroupConditions1.ShowIncludeIndirectManHour && UCGroupConditions1.IncludeIndirectManHourChecked) ); ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); return(engine.GetReportDataSource(sql, byTimeType, dateAdjust)); }
private object[] LoadDataSource(bool isForCompare, bool roundDate) { string byTimeType = UCGroupConditions1.UserSelectByTimeType; string compareType = UCGroupConditions1.UserSelectCompareType; if (!isForCompare) { compareType = string.Empty; } string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); //用于环比同期比时的修改时间过滤条件 int dateAdjust = 0; if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0) { dateAdjust = -12; } else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0) { dateAdjust = -1; } ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); engine.DetailedCoreTable = GetCoreTableDetail(); engine.Formular = GetFormular(compareType); engine.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, dateAdjust); engine.GroupFieldsX = groupFieldsX; engine.GroupFieldsY = groupFieldsY; return(engine.GetReportDataSource(byTimeType, dateAdjust));; }
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 string GetDetailedCoreTable() { string returnValue = string.Empty; if (UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked) { returnValue = ReportSQLEngine.GetDetailedCoreForRptOpQty(); } else { returnValue = ReportSQLEngine.GetDetailedCoreForRptSoQty(); } return(returnValue); }
private object[] LoadDataSource() { string byTimeType = UCGroupConditions1.UserSelectByTimeType; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); engine.DetailedCoreTable = GetDetailedCoreTable(); engine.Formular = GetFormular(); engine.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, false, 0); engine.GroupFieldsX = groupFieldsX; engine.GroupFieldsY = groupFieldsY; 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; } bool bigSSChecked = UCGroupConditions1.BigSSChecked; bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked; bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); //用于环比同期比时的修改时间过滤条件 int dateAdjust = 0; if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0) { dateAdjust = -12; } else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0) { dateAdjust = -1; } ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); engine.DetailedCoreTable = GetDetailedCoreTable(); engine.Formular = GetFormular(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked); engine.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, dateAdjust); 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 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; } bool bigSSChecked = UCGroupConditions1.BigSSChecked; bool opResChecked = UCGroupConditions1.OPChecked || UCGroupConditions1.ResChecked; bool segSSChecked = UCGroupConditions1.SegChecked || UCGroupConditions1.SSChecked; string groupFieldsX = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "X"); string groupFieldsY = this.UCGroupConditions1.GetGroupFieldList(preferredTable, "Y"); groupFieldsY = OrderFieldsY(groupFieldsY); if (string.IsNullOrEmpty(groupFieldsX)) { groupFieldsX += "**.OPCODE || '-' || tblop.opdesc as opcode"; } else { groupFieldsX += ",**.OPCODE || '-' || tblop.opdesc as opcode"; } //用于环比同期比时的修改时间过滤条件 int dateAdjust = 0; if (string.Compare(compareType, NewReportCompareType.LastYear, true) == 0) { dateAdjust = -12; } else if (string.Compare(compareType, NewReportCompareType.Previous, true) == 0) { dateAdjust = -1; } ReportSQLEngine engine = new ReportSQLEngine(this.DataProvider, this.languageComponent1); engine.DetailedCoreTable = GetCoreTableDetail(); engine.Formular = GetFormular(inputOutput, compareType, completeType, bigSSChecked, opResChecked, segSSChecked); engine.WhereCondition = this.UCWhereConditions1.GetWhereSQLStatement(preferredTable, byTimeType, roundDate, dateAdjust); //if (engine.WhereCondition.IndexOf("itemtype_finishedproduct") >= 0) //{ // engine.WhereCondition = engine.WhereCondition.Replace("itemtype_finishedproduct", "FINISHEDSTR"); //} //if (engine.WhereCondition.IndexOf("itemtype_semimanufacture") >= 0) //{ // engine.WhereCondition = engine.WhereCondition.Replace("itemtype_semimanufacture", "SEMIFINISHEDSTR"); //} engine.GroupFieldsX = groupFieldsX; engine.GroupFieldsY = groupFieldsY; 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); }