public DataTable getCalendarList(string _calendarCode)
{
clsDataAccess newData = new clsDataAccess();
try
{
if (_calendarCode == "Mill" || _calendarCode == "MillAFGP" || _calendarCode == "MILL")
{
sb.Clear();
sb.AppendLine("select MillMonth [Month], StartDate, EndDate, TotalShifts,CalendarCode, ");
sb.AppendLine("NextMonth = case when convert(varchar(2), ");
sb.AppendLine("DATEPART(month, DATEADD(MONTH,+1,convert(date, convert(varchar(4),MillMonth) ");
sb.AppendLine("+ '-' + substring(MillMonth,5,2) + '-01')) ");
sb.AppendLine(")) < 10 then Convert(varchar(4),DATEPART(Year, DATEADD(MONTH,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),MillMonth) + '-' + substring(MillMonth,5,2) + '-01'))))+ ");
sb.AppendLine("'0' + CONVERT(varchar(2),DATEPART(MONTH, DATEADD(MONTH,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),MillMonth) + '-' + substring(MillMonth,5,2) + '-01')) )) ");
sb.AppendLine("else Convert(varchar(4),DATEPART(Year, DATEADD(MONTH,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),MillMonth) + '-' + substring(MillMonth,5,2) + '-01'))))+ ");
sb.AppendLine("CONVERT(varchar(2),DATEPART(MONTH, DATEADD(MONTH,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),MillMonth) + '-' + substring(MillMonth,5,2) + '-01')))) end, ");
sb.AppendLine("NextFirstDate = DATEADD(Day,+1,EndDate), Status = '' ");
sb.AppendLine("from CALENDARMILL order by [MillMonth] desc");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
else
{
sb.Clear();
sb.AppendLine("select [Month], StartDate, EndDate, TotalShifts,CalendarCode, ");
sb.AppendLine("NextMonth = case when convert(varchar(2), ");
sb.AppendLine("DATEPART(month, DATEADD(MONth,+1,convert(date, convert(varchar(4),Month) ");
sb.AppendLine("+ '-' + substring(Month,5,2) + '-01')) ");
sb.AppendLine(")) < 10 then Convert(varchar(4),DATEPART(Year, DATEADD(MONth,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),Month) + '-' + substring(Month,5,2) + '-01'))))+ ");
sb.AppendLine("'0' + CONVERT(varchar(2),DATEPART(month, DATEADD(MONth,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),Month) + '-' + substring(Month,5,2) + '-01')) )) ");
sb.AppendLine("else Convert(varchar(4),DATEPART(Year, DATEADD(MONth,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),Month) + '-' + substring(Month,5,2) + '-01'))))+ ");
sb.AppendLine("CONVERT(varchar(2),DATEPART(month, DATEADD(MONth,+1, ");
sb.AppendLine("convert(date, convert(varchar(4),Month) + '-' + substring(Month,5,2) + '-01')))) end, ");
sb.AppendLine("NextFirstDate = DATEADD(Day,+1,EndDate), Status = '' ");
sb.AppendLine("from CALENDAROTHER where CalendarCode = '" + _calendarCode + "' order by [Month] desc");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public void SaveData(string _millMonth, string _oreflowID, int _planTons,
decimal _planBeltGrade, int _planGold)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from PlanHoist");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
if (newData.ResultsDataTable.Rows.Count == 0)
{
sb.Clear();
sb.AppendLine("insert into PlanHoist(MillMonth, OreflowID, PlanTons, PlanBeltGrade, PlanGold) values ");
sb.AppendLine("('" + _millMonth + "', '" + _oreflowID + "', '" + _planTons + "', '" + _planBeltGrade + "', '" + _planGold + "')");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
else if (newData.ResultsDataTable.Rows.Count != 0)
{
sb.Clear();
sb.AppendLine("update PlanHoist ");
sb.AppendLine("set PlanTons = '" + _planTons + "', ");
sb.AppendLine("PlanBeltGrade = '" + _planBeltGrade + "' ,");
sb.AppendLine("PlanGold = '" + _planGold + "' ");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "'");
sb.AppendLine(" ");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
theData.SqlStatement = "[sp_BOOKINGHoistingCycle]";
theData.queryExecutionType = MWDataManager.ExecutionType.StoreProcedure;
theData.queryReturnType = MWDataManager.ReturnType.DataTable;
SqlParameter[] _paramCollection9 =
{
theData.CreateParameter("@MillMonth", SqlDbType.Int, 6, _millMonth),
theData.CreateParameter("@Shaft", SqlDbType.VarChar, 10, _oreflowID),
theData.CreateParameter("@PlanTons", SqlDbType.Int, 10, _planTons),
theData.CreateParameter("@PlanBeltGrade", SqlDbType.Decimal, 10, _planBeltGrade),
theData.CreateParameter("@PlanGold", SqlDbType.Int, 10, _planGold),
};
theData.ParamCollection = _paramCollection9;
theData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Hoisting Booking Data was saved", Color.CornflowerBlue);
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
}
public void SavePlannedData(string _millMonth, string _oreflowID, int _tonsTreated, int _tonsToPlant)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from PLANMilling");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
if (newData.ResultsDataTable.Rows.Count == 0)
{
sb.Clear();
sb.AppendLine("insert into PLANMilling(MillMonth, OreflowID, TonsTreated, TonsToPlant) values ");
sb.AppendLine("('" + _millMonth + "', '" + _oreflowID + "', '" + _tonsTreated + "', '" + _tonsToPlant + "')");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Milling Booking Data was saved", Color.CornflowerBlue);
}
else if (newData.ResultsDataTable.Rows.Count != 0)
{
sb.Clear();
sb.AppendLine("update PLANMilling ");
sb.AppendLine("set TonsTreated = '" + _tonsTreated + "', ");
sb.AppendLine("TonsToPlant = '" + _tonsToPlant + "'");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "'");
sb.AppendLine(" ");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
theData.SqlStatement = "[sp_BOOKINGMillingCycle]";
theData.queryExecutionType = MWDataManager.ExecutionType.StoreProcedure;
theData.queryReturnType = MWDataManager.ReturnType.DataTable;
SqlParameter[] _paramCollection9 =
{
theData.CreateParameter("@MillMonth", SqlDbType.Int, 6, _millMonth),
theData.CreateParameter("@OreflowID", SqlDbType.VarChar, 10, _oreflowID),
theData.CreateParameter("@TonsTreated", SqlDbType.Int, 10, _tonsTreated),
theData.CreateParameter("@TonsToPlant", SqlDbType.Int, 10, _tonsToPlant),
};
theData.ParamCollection = _paramCollection9;
theData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Milling Booking Data was saved", Color.CornflowerBlue);
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Exception Error", _exception.Message, Color.Red);
}
}
public void SaveActualData(string _millMonth, string _shaft, DateTime _calendarDate,
int _reefTons, decimal _wasteTons, decimal _beltGrade)
{
decimal _gold = Convert.ToDecimal((_beltGrade * _reefTons) / 1000);
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from BOOKINGHoist ");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _shaft + "' and CalendarDate = '" + _calendarDate + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
if (newData.ResultsDataTable.Rows.Count == 0)
{
sb.Clear();
sb.AppendLine(" insert into BOOKINGHoist (MillMonth, OreflowID, CalendarDate, ReefTons, WasteTons, BeltGrade, Gold) values ");
sb.AppendLine("('" + _millMonth + "', '" + _shaft + "', '" + _calendarDate + "',");
sb.AppendLine("'" + _reefTons + "', '" + _wasteTons + "', '" + _beltGrade + "', '" + _gold + "') ");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Hoisting Booking Data was saved", Color.CornflowerBlue);
}
else if (newData.ResultsDataTable.Rows.Count != 0)
{
sb.Clear();
sb.AppendLine(" update BOOKINGHoist set ");
sb.AppendLine(" ReefTons = '" + _reefTons + "', ");
sb.AppendLine(" WasteTons = '" + _wasteTons + "', ");
sb.AppendLine(" BeltGrade = '" + _beltGrade + "', ");
sb.AppendLine(" Gold = '" + _gold + "' ");
sb.AppendLine(" where MillMonth = '" + _millMonth + "' and OreflowID = '" + _shaft + "' and CalendarDate = '" + _calendarDate + "'");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Hoisting Booking Data was saved", Color.CornflowerBlue);
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
}
public DataTable getSectionScreenListOther(string _prodmonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine(" select s.ProdMonth, s.SectionID, s.Name, s.HierarchicalID, s.ReportToSectionID,");
sb.AppendLine(" h.Description Occupation, Status = '', s.EmployeeNo IndustryNo ");
sb.AppendLine(" from SectionOther s, HierarchOther h");
sb.AppendLine(" where s.Hierarchicalid = h.Hierarchicalid and");
sb.AppendLine(" ProdMonth = '" + _prodmonth + "' order by s.Hierarchicalid, s.SectionID");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
if (newData.ResultsDataTable.Rows.Count == 0)
{
if (MessageBox.Show("Would you like to copy the Sections from the previous month? All Sections for this month will be overwritten", "Copy Sections", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
sb.Clear();
sb.AppendLine("INSERT INTO SectionOther (Prodmonth, SectionID, Name, ReportToSectionid, Hierarchicalid, EmployeeNo)");
sb.AppendLine(" select ");
sb.AppendLine(" (" + _prodmonth + ") Prodmonth,[SectionID],[Name],[ReportToSectionid],[Hierarchicalid],EmployeeNo");
sb.AppendLine(" from SectionOther WHERE");
sb.AppendLine(" ProdMonth = [dbo].[GetPrevProdMonth] (" + _prodmonth + ") ");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
sb.Clear();
sb.AppendLine(" select s.ProdMonth, s.SectionID, s.Name, s.HierarchicalID, s.ReportToSectionID,");
sb.AppendLine(" h.Description Occupation, Status = ''");
sb.AppendLine(" from SectionOther s, HierarchOther h");
sb.AppendLine(" where s.Hierarchicalid = h.Hierarchicalid and");
sb.AppendLine(" ProdMonth = '" + _prodmonth + "' order by s.Hierarchicalid, s.SectionID");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetStandardCRMData(string _shaft)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select CRM, MeanValue, StdDev, ");
sb.AppendLine("UpperWarningLimit = (MeanValue + 2 * (StdDev/2)), ");
sb.AppendLine("LowerWarningLimit = (MeanValue - 2 * StdDev), ");
sb.AppendLine("UpperFailureLimit = (MeanValue + 3 * (StdDev/2)), ");
sb.AppendLine("LowerFailureLimit = (MeanValue - 3 * (StdDev/2)) ");
sb.AppendLine("from tblStandardCRM where Shaft = '" + _shaft + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetBusinessPlanData()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
sb.AppendLine(" ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetSurveyLockData()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select ");
sb.AppendLine("distinct(p.ProdMonth) ProdMonth,");
sb.AppendLine("isnull(sl.Locked, 0) Locked,");
sb.AppendLine("sl.DateLocked,");
sb.AppendLine("sl.LockedByID");
sb.AppendLine("from PLANNING p");
sb.AppendLine("left outer join survey_locks sl on p.ProdMonth = sl.ProdMonth");
sb.AppendLine("order by p.ProdMonth desc");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetDevelopmentData(string _prodMonth, string _activity, string _section)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select ''Status,divisioncode ExeName, 'xxx' Costareaid,s.Prodmonth, W.Workplaceid, ");
sb.AppendLine("w.Description, s.TotalMetres Adv_Total, s.MeasWidth, s.MeasHeight, ");
sb.AppendLine("s.CubicsMetres [Extra Units], 'Miner' Hierarchicalname, sc.Name, s.CrewMorning, ");
sb.AppendLine("e.Description Description2, s.cw, ");
sb.AppendLine("case when MetersNotToBePaid is NULL then 0 else MetersNotToBePaid end MetersNotToBePaid, ");
sb.AppendLine("CleaningCrew,TrammingCrew,HlgeMaintainanceCrew,RiggerCrew,RseCleaningCrew ");
sb.AppendLine("from survey s inner join workplace w on w.WorkplaceID = s.workplaceid ");
sb.AppendLine("inner join SECTION_COMPLETE sc ");
sb.AppendLine("on s.prodmonth = sc.prodmonth ");
sb.AppendLine("and s.sectionid = sc.sectionid inner join endtype e on e.EndTypeID = w.EndTypeID ");
sb.AppendLine("where s.activity = '" + _activity + "' and s.Prodmonth ='" + _prodMonth + "' and sc.Sectionid_2='" + _section + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public void GetSysSettings(string systemDBTag, string connection)
{
var _dbMan = new clsDataAccess();
_dbMan.ConnectionString = TConnections.GetConnectionString(systemDBTag, connection);
_dbMan.SqlStatement = "Select * from tbl_SysSet";
_dbMan.queryExecutionType = ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = ReturnType.DataTable;
var DataResult = _dbMan.ExecuteInstruction();
foreach (DataRow dr in _dbMan.ResultsDataTable.Rows)
{
_currentProductionMonth = Convert.ToInt32(dr["CurrentProductionMonth"].ToString());
_RunDate = Convert.ToDateTime(dr["Rundate"].ToString());
_RepDir = dr["REPDIR"].ToString();
_AdjBook = dr["AdjBook"].ToString();
_Banner = dr["BANNER"].ToString();
_CheckMeas = dr["CheckMeas"].ToString();
_BlastQual = Convert.ToInt32(Math.Round(Convert.ToDecimal(dr["percblastqualification"].ToString()), 0));
_AColor = Convert.ToInt32(dr["A_Color"].ToString());
_BColor = Convert.ToInt32(dr["B_Color"].ToString());
_SColor = Convert.ToInt32(dr["S_Color"].ToString());
}
}
public DataTable GetSection(string _prodMonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select a.* from ");
sb.AppendLine("(select distinct(SectionId_2) SectionID, SectionId_2 + ': ' + Name_2 Name ");
sb.AppendLine("from [Section_Complete] ");
sb.AppendLine("where ProdMonth = '" + _prodMonth + "'");
sb.AppendLine("union ");
sb.AppendLine("select distinct(SectionId_5) SectionID, SectionId_5 + ': ' + Name_5 Name ");
sb.AppendLine("from [Section_Complete] ");
sb.AppendLine("where ProdMonth = '" + _prodMonth + "') a ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetSections(string _prodmonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("Select distinct b.SectionID_2 SectionID, b.SectionID_2 + ' : ' + Name_2 Name ");
sb.AppendLine("from PLANMONTH a ");
sb.AppendLine("inner join SECTION_COMPLETE b on ");
sb.AppendLine("a.PRODMONTH = b.PRODMONTH and ");
sb.AppendLine("a.SECTIONID = b.SECTIONID ");
sb.AppendLine("where a.PRODMONTH = '" + _prodmonth + "' ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetBoxHole(DataTable thePlanningData)
{
clsDataAccess newData = new clsDataAccess();
try
{
foreach (DataRow dr in thePlanningData.Rows)
{
sb.Clear();
sb.AppendLine("select o.oreflowid BoxHoleID, o.name BH from workplace w ");
sb.AppendLine("inner join oreflowentities o on o.parentoreflowid = w.oreflowid ");
sb.AppendLine("where [description] = '" + dr["WorkplaceDesc"] + "' and o.oreflowcode = 'BH'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable LoadBoxholes()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select o.OreFlowID, o.OreFlowCode, o.Name Name,isnull(o.LevelNumber, '') LevelNumber, ");
sb.AppendLine("o.Inactive, o.SectionID, o.ReefType, isnull(o.BoxDistance, 0.0) BoxDistance, ");
sb.AppendLine("oe.name lvl, Status = '' ");
sb.AppendLine("from dbo.OREFLOWENTITIES o");
sb.AppendLine("left outer join oreflowentities oe");
sb.AppendLine("on o.parentoreflowid = oe.oreflowid where o.oreflowcode = 'BH'");
sb.AppendLine("order by o.oreflowid");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable getGroupedSamplingData(string _workplaceID)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select w.*, s1.*, Status = '' from workplace w left outer join ");
sb.AppendLine("(Select workplaceid, max(CalendarDate) CalendarDate");
sb.AppendLine("from sampling group by workplaceid) s");
sb.AppendLine("on w.workplaceid = s.workplaceid left outer join");
sb.AppendLine("sampling s1 on s.workplaceid = s1.workplaceid and");
sb.AppendLine("s.calendardate = s1.calendardate");
sb.AppendLine("where w.reefwaste = 'R' and s1.WorkplaceID = '" + _workplaceID + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public void SaveActualData(string _millMonth, string _oreflowID, DateTime _calendarDate, decimal _actualTonsTreated, decimal _actualTonsToPlant)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from BOOKINGMilling ");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "' and CalendarDate = '" + _calendarDate + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
if (newData.ResultsDataTable.Rows.Count == 0)
{
sb.Clear();
sb.AppendLine(" insert into BOOKINGMilling (MillMonth, OreflowID, CalendarDate, TonsTreated, TonsToPlant) values ");
sb.AppendLine("('" + _millMonth + "', '" + _oreflowID + "', '" + _calendarDate + "'");
sb.AppendLine("'" + _actualTonsTreated + "', '" + _actualTonsToPlant + "') ");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Milling Booking Data was saved", Color.CornflowerBlue);
}
else if (newData.ResultsDataTable.Rows.Count != 0)
{
sb.Clear();
sb.AppendLine(" update BOOKINGMilling set ");
sb.AppendLine(" TonsTreated = '" + _actualTonsTreated + "', ");
sb.AppendLine(" TonsToPlant = '" + _actualTonsToPlant + "' ");
sb.AppendLine(" where MillMonth = '" + _millMonth + "' and OreflowID = '" + _oreflowID + "' and CalendarDate = '" + _calendarDate + "'");
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "Milling Booking Data was saved", Color.CornflowerBlue);
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
}
public void SaveDuplicateSampleData(string _shaft, decimal _payLimit, string _action)
{
clsDataAccess newData = new clsDataAccess();
try
{
if (_action == "Add")
{
sb.Clear();
sb.AppendLine("select * from tblDuplicateSample where Shaft = '" + _shaft + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
DataTable dtAMIS = newData.ResultsDataTable;
if (dtAMIS.Rows.Count == 0)
{
sb.Clear();
sb.AppendLine("insert into tblDuplicateSample (Shaft, PayLimit) values ");
sb.AppendLine("('" + _shaft + "', '" + _payLimit + "') ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Data Saved", "AMIS Data was saved", Color.CornflowerBlue);
}
else if (dtAMIS.Rows.Count > 0)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", "Data Already exists for this Shaft", Color.Red);
}
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Exception Error", _exception.Message, Color.Red);
}
}
//gets all the fields from the SYSSET table
public void GetSysSettings(string systemDBTag, string connection)
{
var _dbMan = new clsDataAccess();
_dbMan.ConnectionString = TConnections.GetConnectionString(systemDBTag, connection);
_dbMan.SqlStatement = "Select * from SysSet";
_dbMan.queryExecutionType = ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = ReturnType.DataTable;
var DataResult = _dbMan.ExecuteInstruction();
foreach (DataRow dr in _dbMan.ResultsDataTable.Rows)
{
var millmonth = dr["CurrentMillMonth"].ToString();
if (millmonth == "")
{
millmonth = "0";
}
_millMonth = Convert.ToInt32(millmonth);
var SubB = _dbMan.ResultsDataTable;
_currentProductionMonth = Convert.ToInt32(dr["CurrentProductionMonth"].ToString());
_prodMonth = Convert.ToInt32(dr["CurrentProductionMonth"].ToString());
ProdMonth = Convert.ToInt32(dr["CurrentProductionMonth"].ToString());
GlobalVar.ProdMonth = Convert.ToInt32(dr["CurrentProductionMonth"].ToString());
_mine = dr["BANNER"].ToString();
_moHierarchicalId = Convert.ToInt32(dr["MOHIERARCHICALID"].ToString());
_RunDate = Convert.ToDateTime(dr["Rundate"].ToString());
_isCentralizedDatabase = Convert.ToInt32(dr["IsCentralizedDatabase"].ToString());
_enableUranium = false;
var outvalue = string.Empty;
if (dr.TryGetColumnFromRow("EnableUranium", out outvalue))
{
_enableUranium = Convert.ToBoolean(outvalue);
}
if (dr.TryGetColumnFromRow("CheckMeas", out outvalue))
{
ConvertDayOfWeek(outvalue);
_canReconBooking = _reconDayOfWeek != null;
}
}
}
public DataTable GetTopPanelsData(string _prodMonth, string _sectionID)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("SELECT distinct a.Workplaceid WPID,Description Workplace , ");
sb.AppendLine("convert(numeric(13,0),isnull(CMGT,0)) CMGT, ");
sb.AppendLine("convert(numeric(13,3),isnull(Kg,0)) KG, ");
sb.AppendLine("case when c.WorkplaceID is null then Cast(0 as Bit) else Cast(1 as Bit) end as 'Selected', ");
sb.AppendLine("Activity = Convert(numeric(7,0), a.Activity) ");
sb.AppendLine("FROM PLANMONTH a ");
sb.AppendLine("inner join SECTION_COMPLETE sc on ");
sb.AppendLine("sc.Prodmonth = a.Prodmonth and ");
sb.AppendLine("sc.SectionID = a.Sectionid ");
sb.AppendLine("inner join WORKPLACE b ");
sb.AppendLine("on a.Workplaceid = b.WorkplaceID ");
sb.AppendLine("left join TopPanelsSelected c on ");
sb.AppendLine("a.Prodmonth = c.Prodmonth and ");
sb.AppendLine("a.Workplaceid = c.WorkplaceID and ");
sb.AppendLine("c.SectionID = '" + _sectionID + "' ");
sb.AppendLine("where a.Prodmonth = '" + _prodMonth + "' AND Plancode = 'MP' and");
if (_sectionID == "GM")
{
sb.AppendLine("sc.Sectionid_5 = '" + _sectionID + "'");
}
else
{
sb.AppendLine("sc.Sectionid_2 = '" + _sectionID + "' ");
}
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public void GetYear(string systemDBTag, string connection)
{
var _dbMan = new clsDataAccess();
_dbMan.ConnectionString = TConnections.GetConnectionString(systemDBTag, connection);
var sb = new StringBuilder();
sb.AppendLine("select distinct [Year] [Year] from CalendarReport");
_dbMan.SqlStatement = sb.ToString();
_dbMan.queryExecutionType = ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = ReturnType.DataTable;
_dbMan.ExecuteInstruction();
var dt = _dbMan.ResultsDataTable;
_year = Convert.ToInt32(dt.Rows[0]["Year"].ToString());
}
public DataTable GetPlanningLockData(string _prodMonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select Prodmonth, sectionid_2, sectionid_2 + ':' + name_2 Section");
sb.AppendLine("from Section_Complete");
sb.AppendLine(string.Format("where Prodmonth = '{0:0}'", _prodMonth));
sb.AppendLine("group by Prodmonth, sectionid_2, name_2");
sb.AppendLine("order by Prodmonth, sectionid_2, name_2");
sb.AppendLine("select k.Prodmonth, sc.sectionid_2");
sb.AppendLine("from Kriging k left outer join ");
sb.AppendLine("PLANMONTHLocked p on");
sb.AppendLine("k.workplaceid = p.workplaceid");
sb.AppendLine("inner join Section_Complete sc");
sb.AppendLine("on sc.Prodmonth = k.Prodmonth");
sb.AppendLine("and sc.SectionID = k.Sectionid");
sb.AppendLine(string.Format("where k.Prodmonth = '{0:0}'", _prodMonth));
sb.AppendLine("group by k.Prodmonth, sc.sectionid_2");
sb.AppendLine("order by k.Prodmonth, sc.sectionid_2");
sb.AppendLine("select p.Prodmonth, sc.sectionid_2");
sb.AppendLine("from PLANMONTH p");
sb.AppendLine("inner join Section_Complete sc");
sb.AppendLine(" on sc.Prodmonth = p.Prodmonth");
sb.AppendLine(" and sc.SectionID = p.Sectionid");
sb.AppendLine(string.Format("where p.Prodmonth = '{0:0}'", _prodMonth));
sb.AppendLine("group by p.Prodmonth, sc.sectionid_2");
sb.AppendLine("order by p.Prodmonth, sc.sectionid_2");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetCalendarCodes(string _calendarCode)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select CalendarCode from CODE_CALENDAR where [Description] != 'Section Calendar'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetShaft()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select distinct [Description] Name from PAS_Central_Test.dbo.CODE_WPDIVISION");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetDuplicateSample(string _shaft)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from tblDuplicateSample where Shaft = '" + _shaft + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetPlanningStoragesData(string _prodMonth, string _mill)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select * from PLANNING_STORAGES ");
sb.AppendLine("where ProdMonth = '" + _prodMonth + "' and OreFlowID = '" + _mill + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable LoadLevels()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select Distinct OreflowID, LevelNumber from Oreflowentities ");
sb.AppendLine(" where OreflowCode = 'Lvl' ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable LoadMONames()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select distinct Name from SECTION ");
sb.AppendLine("where Hierarchicalid = 4");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable GetHoistingBookingData(string _millMonth, string _shaft)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select distinct(cl.CalendarDate) CalendarDate, TotalShifts, ");
sb.AppendLine("isnull(b.ReefTons, 0) ReefTons, ");
sb.AppendLine("isnull(b.WasteTons, 0) WasteTons, ");
sb.AppendLine("isnull(b.BeltGrade, 0) BeltGrade, ");
sb.AppendLine("isnull(b.Gold, 0) Gold, ");
sb.AppendLine("isnull(b.PlanTons, 0) PlanTons, ");
sb.AppendLine("isnull(b.PlanBeltGrade, 0) PlanBeltGrade, ");
sb.AppendLine("isnull(b.PlanGold, 0) PlanGold, WorkingDay, PlanTons * TotalShifts PlanMonthTons, ");
sb.AppendLine("PlanBeltGrade PlanMonthBeltGrade, PlanGold * TotalShifts PlanMonthGold ");
sb.AppendLine("from CODE_CALENDAR cc ");
sb.AppendLine("inner join CALENDARMILL cm on ");
sb.AppendLine("cc.CalendarCode = cm.CalendarCode and ");
sb.AppendLine("cm.MillMonth = '" + _millMonth + "' inner join CALTYPE cl on ");
sb.AppendLine("cl.CalendarCode = cc.CalendarCode and ");
sb.AppendLine("cl.CalendarDate >= cm.StartDate and ");
sb.AppendLine("cl.CalendarDate <= cm.EndDate ");
sb.AppendLine("left join BookingHoist b on ");
sb.AppendLine("b.CalendarDate = cl.CalendarDate and b.millmonth = cm.millmonth and ");
sb.AppendLine("b.OreflowID = '" + _shaft + "' ");
sb.AppendLine("where cc.Description = 'Mill Calendar' and cl.Workingday = 'Y'");
sb.AppendLine("order by cl.CalendarDate ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable LoadInact()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select Distinct Inactive ");
sb.AppendLine("from OREFLOWENTITIES ");
sb.AppendLine("where Inactive != '' ");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
public DataTable LoadSections()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select SectionID,Name");
sb.AppendLine("from SECTION ");
sb.AppendLine("where Prodmonth = (select CurrentProductionMonth from SYSSET)");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", _exception.Message, Color.Red);
}
return(newData.ResultsDataTable);
}
