private DataTable getTemplateList() { try { sb.Clear(); sb.AppendLine("select TemplateID, TemplateName, "); sb.AppendLine("Case when Activity = 0 then 'Stoping' else "); sb.AppendLine("Case when Activity = 1 then 'Development' "); sb.AppendLine("end end Activity "); sb.AppendLine("from PlanProt_Template ORDER BY Activity,TemplateName"); theData.SqlStatement = sb.ToString(); theData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement; theData.queryReturnType = MWDataManager.ReturnType.DataTable; theData.ExecuteInstruction(); } catch (Exception e) { Global.sysMessages.sysMessagesClass message = new Global.sysMessages.sysMessagesClass(); message.viewMessage(MessageType.Error, "ERROR LOADING TEMPLATE LIST", "Mineware.Systems.Production", "clsPlanProtTemplateData", "getTemplateList", e.Message, ButtonTypes.OK, MessageDisplayType.FullScreen); } return(theData.ResultsDataTable); }
public DataTable get_Sysset() { theData.ConnectionString = connectionString; theData.SqlStatement = " select *, theRunDate = Convert(varchar(10), RunDate, 120) from Sysset "; theData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement; theData.queryReturnType = MWDataManager.ReturnType.DataTable; theData.ExecuteInstruction(); clsDataResult errorMsg = theData.ExecuteInstruction(); if (errorMsg.success == false) { _sysMessagesClass.viewMessage(MessageType.Error, "SQL ERROR", DBTag, "clsProblemsFeport", "get_Sysset", errorMsg.Message, ButtonTypes.OK, MessageDisplayType.FullScreen); return(theData.ResultsDataTable); } else { return(theData.ResultsDataTable); } }
public DataTable getSecCal(string prodmonth) { try { string theMonth = ""; theData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement; theData.queryReturnType = MWDataManager.ReturnType.DataTable; sb.Clear(); sb.AppendLine(" select theMonth = substring(convert(varchar(6),dateadd(month,-1, "); sb.AppendLine(" cast(substring('" + prodmonth + "',1,4) + '-'+substring('" + prodmonth + "',5,2) +'-01' as date))),1,4) "); sb.AppendLine(" + "); sb.AppendLine(" substring(convert(varchar(10),dateadd(month,-1, "); sb.AppendLine(" cast(substring('" + prodmonth + "',1,4) + '-'+substring('" + prodmonth + "',5,2) +'-01' as date))),6,2) "); theData.SqlStatement = sb.ToString(); theData.ExecuteInstruction(); DataTable y = theData.ResultsDataTable; if (y.Rows.Count != 0) { theMonth = y.Rows[0]["theMonth"].ToString(); } theData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement; theData.queryReturnType = MWDataManager.ReturnType.DataTable; sb.Clear(); sb.AppendLine("select s.sectionid, s.name, calendarcode, 0 Status, TestDate = GetDate(), "); sb.AppendLine("CASE WHEN begindate IS NULL THEN '1900-01-01' else convert(varchar(10),begindate,120) end begindate,"); sb.AppendLine("CASE WHEN enddate IS NULL THEN '1900-01-01' else convert(varchar(10),enddate,120) end enddate,"); sb.AppendLine("Isnull(totalshifts,0) totalshifts from section s left outer join seccal sc"); sb.AppendLine("on s.sectionid = sc.sectionid and s.prodmonth = sc.prodmonth"); sb.AppendLine("where s.prodmonth = '" + prodmonth + "' and s.hierarchicalid = 5 "); sb.AppendLine("order by s.sectionid"); theData.SqlStatement = sb.ToString(); theData.ExecuteInstruction(); if (theData.ResultsDataTable.Rows.Count == 0) { Global.sysMessages.sysMessagesClass message = new Global.sysMessages.sysMessagesClass(); message.viewMessage(MessageType.Info, "NO DATA", "There are no sections for production month " + prodmonth + ".\r\n Please add the sections in the orgstructure in the system settings", ButtonTypes.OK, MessageDisplayType.FullScreen); } else { sb.Clear(); sb.AppendLine("select s.ProdMonth,s.sectionid, s.name, isnull(CalendarCode,'') calendarcode, 0 Status, TestDate = GetDate(), "); sb.AppendLine(" BDate = CASE WHEN sc.BeginDate IS NULL then "); sb.AppendLine(" case when aa.NewConvBeginDate = '' then '' "); sb.AppendLine(" else aa.NewConvBeginDate end "); sb.AppendLine(" else convert(varchar(10),sc.begindate,120) end , "); sb.AppendLine(" CASE WHEN sc.EndDate IS NULL THEN '' else convert(varchar(10),sc.EndDate,120) end EDate , "); sb.AppendLine(" isnull(TotalShifts, 0) totalshifts, "); sb.AppendLine(" begindate = CASE WHEN sc.BeginDate IS NULL then "); sb.AppendLine(" case when aa.NewConvBeginDate = '' then GetDate() "); sb.AppendLine(" else aa.NewBeginDate end "); sb.AppendLine(" else sc.begindate end , "); sb.AppendLine(" CASE WHEN sc.EndDate IS NULL THEN GetDate() else sc.EndDate end enddate, "); sb.AppendLine(" aa.NewConvBeginDate, aa.NewBeginDate, '" + theMonth + "' PrevMonth "); sb.AppendLine(" from Section s "); sb.AppendLine(" left outer join Seccal sc on "); sb.AppendLine(" s.SectionID = sc.SectionID and s.ProdMonth = sc.ProdMonth "); sb.AppendLine(" left outer join "); sb.AppendLine(" ( "); sb.AppendLine(" select s.SectionID, NewConvBeginDate = case when sc.EndDate is null then '' else "); sb.AppendLine(" convert(varchar(10),DateAdd(Day,1,sc.EndDate),120) end, "); sb.AppendLine(" NewBeginDate = case when sc.EndDate is null then GetDate() else DateAdd(Day,1,sc.EndDate) end "); sb.AppendLine(" from Section s "); sb.AppendLine(" left outer join Seccal sc on "); sb.AppendLine(" s.SectionID = sc.SectionID and "); sb.AppendLine(" s.ProdMonth = sc.ProdMonth "); sb.AppendLine(" where s.ProdMonth = " + theMonth + " and s.HierarchicalID = 5 "); sb.AppendLine(" ) aa on aa.SectionID = s.SectionID "); sb.AppendLine(" where s.ProdMonth = " + prodmonth + " and s.HierarchicalID = 5 "); theData.SqlStatement = sb.ToString(); theData.ExecuteInstruction(); } return(theData.ResultsDataTable); } catch (Exception e) { Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", e.Message, Color.Red); return(null); } }