//gets all the fields from the SYSSET table
public void GetSysSettings()
{
MWDataManager.clsDataAccess _dbMan = new MWDataManager.clsDataAccess();
_dbMan.ConnectionString = ConfigurationManager.AppSettings["SQLConnectionStr"];
_dbMan.SqlStatement = "select * from tbl_sysset ";
_dbMan.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = MWDataManager.ReturnType.DataTable;
_dbMan.ExecuteInstruction();
DataTable SubB = _dbMan.ResultsDataTable;
SysSettings.ProdMonth = Convert.ToInt32(SubB.Rows[0]["currentproductionmonth"].ToString());
SysSettings.MillMonth = Convert.ToInt32(SubB.Rows[0]["currentmillmonth"].ToString());
SysSettings.Banner = SubB.Rows[0]["Banner"].ToString();
SysSettings.StdAdv = Convert.ToDecimal(SubB.Rows[0]["stpadv"].ToString());
SysSettings.CheckMeas = SubB.Rows[0]["CheckMeas"].ToString();
SysSettings.PlanType = SubB.Rows[0]["PlanType"].ToString();
SysSettings.CleanShift = SubB.Rows[0]["CleanShift"].ToString();
SysSettings.AdjBook = SubB.Rows[0]["AdjBook"].ToString();
SysSettings.BlastQual = Convert.ToInt32(Math.Round(Convert.ToDecimal(SubB.Rows[0]["percblastqualification"].ToString()), 0));
SysSettings.DSOrg = SubB.Rows[0]["dsorg"].ToString();
SysSettings.CHkMeasLevel = SubB.Rows[0]["checkmeaslvl"].ToString();
SysSettings.PlanNotes = SubB.Rows[0]["PlanNotes"].ToString();
SysSettings.CylePlan = "Y";
SysSettings.RepDir = SubB.Rows[0]["RepDir"].ToString();
SysSettings.HGrade = Convert.ToDecimal(SubB.Rows[0]["stopingpaylimit"].ToString());
SysSettings.ServDir = SubB.Rows[0]["ServerPath"].ToString();
SysSettings.Vampsqm = Convert.ToInt32(Math.Round(Convert.ToDecimal(SubB.Rows[0]["vampsqm"].ToString()), 0));
SysSettings.FatFreeShift = Convert.ToInt32(Math.Round(Convert.ToDecimal(SubB.Rows[0]["FatFreeShift"].ToString()), 0));
SysSettings.RepDirImage = SubB.Rows[0]["RepDir"].ToString();
}
//gets all the fields from the SYSSET table
public void GetSysSettings()
{
MWDataManager.clsDataAccess _dbMan = new MWDataManager.clsDataAccess();
_dbMan.ConnectionString = "";
_dbMan.SqlStatement = "select * from sysset ";
_dbMan.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = MWDataManager.ReturnType.DataTable;
_dbMan.ExecuteInstruction();
DataTable SubB = _dbMan.ResultsDataTable;
SysSettings.ProdMonth = Convert.ToInt32(SubB.Rows[0]["currentproductionmonth"].ToString());
SysSettings.MillMonth = Convert.ToInt32(SubB.Rows[0]["currentmillmonth"].ToString());
SysSettings.Banner = SubB.Rows[0]["Banner"].ToString();
SysSettings.StdAdv = Convert.ToDecimal(SubB.Rows[0]["stpadv"].ToString());
SysSettings.CheckMeas = SubB.Rows[0]["CheckMeas"].ToString();
SysSettings.PlanType = SubB.Rows[0]["PlanType"].ToString();
SysSettings.CleanShift = SubB.Rows[0]["CleanShift"].ToString();
SysSettings.AdjBook = SubB.Rows[0]["AdjBook"].ToString();
SysSettings.BlastQual = Convert.ToInt32(Math.Round(Convert.ToDecimal(SubB.Rows[0]["percblastqualification"].ToString()), 0));
SysSettings.DSOrg = SubB.Rows[0]["dsorg"].ToString();
SysSettings.CHkMeasLevel = SubB.Rows[0]["checkmeaslvl"].ToString();
SysSettings.PlanNotes = SubB.Rows[0]["PlanNotes"].ToString();
}
//sets the logged on user information
public void SetUserInfo()
{
MWDataManager.clsDataAccess _dbMan = new MWDataManager.clsDataAccess();
_dbMan.ConnectionString = ConfigurationManager.AppSettings["SQLConnectionStr"];
_dbMan.SqlStatement = "select c.*, b.hierarchicalid, isnull(GeolSampWorksOrder,'N') SampWO, " +
"CompLogin, Lvl1, Lvl2, Lvl3, Lvl4, Lvl5, Lvl6, Lvl7, Lvl8, CreateNotes " +
"from CpmUsers c " +
"left outer join (select * from Section where prodmonth = (select currentproductionmonth from sysset)) b " +
"on c.passectionid = b.sectionid " +
"left outer join cpmusers_Department_Geoscience d " +
"on c.userid = d.userid " +
"left outer join CPMUsers_Department_Survey e " +
"on c.userid = e.userid " +
"where c.UserID = '" + TUserInfo.UserID + "'";
_dbMan.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = MWDataManager.ReturnType.DataTable;
_dbMan.ExecuteInstruction();
DataTable SubA = _dbMan.ResultsDataTable;
clsUserInfo.UserName = SubA.Rows[0]["username"].ToString();
clsUserInfo.UserBookSection = SubA.Rows[0]["Passectionid"].ToString();
clsUserInfo.Hier = Convert.ToInt32(SubA.Rows[0]["hierarchicalid"]);
clsUserInfo.Tram = SubA.Rows[0]["Tram"].ToString();
clsUserInfo.Hoist = SubA.Rows[0]["Hoist"].ToString();
clsUserInfo.mill = SubA.Rows[0]["mill"].ToString();
clsUserInfo.book = SubA.Rows[0]["pasbook"].ToString();
clsUserInfo.dropraise = SubA.Rows[0]["dropraise"].ToString();
clsUserInfo.sys = SubA.Rows[0]["systemadmin"].ToString();
clsUserInfo.plan = SubA.Rows[0]["pasplan"].ToString();
clsUserInfo.samp = SubA.Rows[0]["sampling"].ToString();
clsUserInfo.Surv = SubA.Rows[0]["survey"].ToString();
clsUserInfo.Expl = SubA.Rows[0]["Explosive"].ToString();
clsUserInfo.DiamondDril = SubA.Rows[0]["DiamondDrilling"].ToString();
clsUserInfo.TempBackDateBooking = SubA.Rows[0]["ChiefAuth"].ToString();
clsUserInfo.TempCycleChange = SubA.Rows[0]["ProdmanAuth"].ToString();
clsUserInfo.PlanAuth = SubA.Rows[0]["ChiefPlanAuth"].ToString();
clsUserInfo.CalChange = SubA.Rows[0]["CalChange"].ToString();
clsUserInfo.MOMeas = SubA.Rows[0]["FinManAuth"].ToString();
clsUserInfo.GradeAuth = SubA.Rows[0]["HrManAuth"].ToString();
clsUserInfo.RBPlan = SubA.Rows[0]["ReefBoringPlanning"].ToString();
clsUserInfo.RBBook = SubA.Rows[0]["ReefBoringBooking"].ToString();
clsUserInfo.NSBook = SubA.Rows[0]["NSBooking"].ToString();
clsUserInfo.SampWO = SubA.Rows[0]["SampWO"].ToString();
clsUserInfo.SNCreate = SubA.Rows[0]["CreateNotes"].ToString();
}
//private DataTable _SumOn;
//public DataTable SumOn
//{
// get
// {
// _SumOn = loadSumOn();
// return _SumOn;
// }
//}
public DataTable LoadAllLevel()
{
MWDataManager.clsDataAccess Bus_Logic = new MWDataManager.clsDataAccess();
Bus_Logic.ConnectionString = TConnections.GetConnectionString(systemDBTag, UserCurrentInfo.Connection);
Bus_Logic.ConnectionString = TConnections.GetConnectionString(systemDBTag, UserCurrentInfo.Connection);
Bus_Logic.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
Bus_Logic.queryReturnType = MWDataManager.ReturnType.DataTable;
Bus_Logic.SqlStatement = "select HierarchicalID from section where prodmonth = '" + Prodmonth.ToString("yyyyMM") + "' and name= '" + _NAME + "'";
Bus_Logic.ExecuteInstruction();
if (Bus_Logic.ResultsDataTable.Rows.Count > 0)
{
int _hierID = Convert.ToInt32(Bus_Logic.ResultsDataTable.Rows[0]["HierarchicalID"]);
Bus_Logic.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
Bus_Logic.queryReturnType = MWDataManager.ReturnType.DataTable;
//if (Thelevel == null)
//{
// Thelevel = "0";
//}
Bus_Logic.SqlStatement = "select distinct HierarchicalID, " +
"Description = (select " +
"case when Hierarchicalid = 1 then 'Business Coach' " +
"when Hierarchicalid = 2 then 'Mine Manager' " +
"when Hierarchicalid = 3 then 'Mining Manager' " +
"when Hierarchicalid = 4 then 'Mine Overseer' " +
"when Hierarchicalid = 5 then 'Coach' " +
"when Hierarchicalid = 6 then 'Miner' " +
"end) from Section where HierarchicalID > " + _hierID + " and " +
"ProdMonth = '" + Prodmonth.ToString("yyyyMM") + "' " +
"union \r\n " +
"select 7 HierarchicalID, 'Workplace' Description " +
"order by HierarchicalID";
Bus_Logic.ExecuteInstruction();
}
//else
//Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", "There's no data for your selection", Color.Red);
return(Bus_Logic.ResultsDataTable);
}
public bool get_Activity_Reports()
{
bool _executionResult = false;
try
{
MWDataManager.clsDataAccess Bus_Logic = new MWDataManager.clsDataAccess();
Bus_Logic.ConnectionString = TConnections.GetConnectionString(systemDBTag, UserCurrentInfo.Connection);
Bus_Logic.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
switch (Bus_Logic.queryReturnType)
{
case ReturnType.DataTable:
Bus_Logic.queryReturnType = MWDataManager.ReturnType.DataTable;
break;
case ReturnType.SQLDataReader:
Bus_Logic.queryReturnType = MWDataManager.ReturnType.SQLDataReader;
break;
}
Bus_Logic.SqlStatement = "SELECT 0 Code, 'Stoping' [Desc] " +
"UNION " +
"SELECT 1 Code, 'Development' [Desc] " +
"union " +
"select 8 Code, 'Sweepings' [Desc] ";
Bus_Logic.ExecuteInstruction();
_executionResult = true;
}
catch (Exception except)
{
_executionResult = false;
throw new ApplicationException(except.Message, except);
}
finally
{
}
return(_executionResult);
}
public DataTable loadRunDate(string _whatCalen, string Prodmonth)
{
if (Prodmonth != null)
{
if (_whatCalen == "P")
{
MWDataManager.clsDataAccess _loadRunDate = new MWDataManager.clsDataAccess();
_loadRunDate.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
_loadRunDate.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_loadRunDate.queryReturnType = MWDataManager.ReturnType.DataTable;
_loadRunDate.SqlStatement = "SELECT Max(EndDate) EDate FROM SECTION_COMPLETE SC " +
"inner join SECCAL on " +
"SC.PRODMONTH = SECCAL.PRODMONTH and " +
"SC.SECTIONID_1 = SECCAL.SECTIONID " +
"WHERE SC.PRODMONTH = '" + Prodmonth + "' ";
_loadRunDate.ExecuteInstruction();
return(_loadRunDate.ResultsDataTable);
}
else
{
MWDataManager.clsDataAccess _loadRunDate = new MWDataManager.clsDataAccess();
_loadRunDate.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
_loadRunDate.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_loadRunDate.queryReturnType = MWDataManager.ReturnType.DataTable;
_loadRunDate.SqlStatement = "select max(EndDate) EDate from CALENDARMILL " +
"WHERE MillMonth= '" + Prodmonth + "' ";
_loadRunDate.ExecuteInstruction();
return(_loadRunDate.ResultsDataTable);
}
}
else
{
return(null);
}
}
private void set_Dates()
{
MWDataManager.clsDataAccess BMEBL = new MWDataManager.clsDataAccess();
BMEBL.queryReturnType = MWDataManager.ReturnType.DataTable;
BMEBL.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
string whatCalen;
if (reportSettings.WhatMonth.ToString() == "Production Month")
{
whatCalen = "P";
}
else
{
whatCalen = "M";
}
DataTable dtEndDate = new DataTable();
dtEndDate = loadRunDate(whatCalen, reportSettings.Prodmonth.ToString("yyyyMM"));
if (dtEndDate.Rows.Count > 0)
{
if (dtEndDate.Rows[0]["EDate"] is DBNull)
{
}
else
{
dteEndDate = Convert.ToDateTime(dtEndDate.Rows[0]["EDate"].ToString());
if (DateTime.Now < dteEndDate)
{
dteEndDate = DateTime.Now;
}
}
}
reportSettings.Showuntil = dteEndDate;
iShowuntil.Properties.Value = reportSettings.Showuntil;
}
public DataTable GetSections(string ProdMonth, string HierId, string SectionId)
{
MWDataManager.clsDataAccess _dbMan = new MWDataManager.clsDataAccess();
_dbMan.ConnectionString = "";
_dbMan.SqlStatement = " Select SECTIONid, Name, Hierarchicalid Hier " +
"from Section s where s.Prodmonth = '" + ProdMonth.ToString() + "' and HierarchicalType = 'Pro' ";
if (HierId.ToString() != "NO")
{
_dbMan.SqlStatement = _dbMan.SqlStatement + " and Hierarchicalid = '" + HierId.ToString() + "' ";
}
_dbMan.SqlStatement = _dbMan.SqlStatement + "and Sectionid like '" + SectionId.ToString() + "%' ";
_dbMan.SqlStatement = _dbMan.SqlStatement + " order by SECTIONid ";
_dbMan.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_dbMan.queryReturnType = MWDataManager.ReturnType.DataTable;
_dbMan.ResultsTableName = "GetSections";
_dbMan.ExecuteInstruction();
DataTable dt1 = _dbMan.ResultsDataTable;
return(dt1);
}
private void createReport(Object theReportSettings)
{
string theTypeMonth = "";
if (reportSettings.WhatMonth.ToString() == "Production Month")
{
theTypeMonth = "P";
}
else
{
theTypeMonth = "M";
}
string theReefWaste = "";
if (reportSettings.ShowType.ToString() == "Reef")
{
theReefWaste = "0";
}
else if (reportSettings.ShowType.ToString() == "Waste")
{
theReefWaste = "1";
}
string theWorkCap = "";
if (reportSettings.MSType.ToString() == "Main")
{
theWorkCap = "M";
}
else if (reportSettings.MSType.ToString() == "Secondary")
{
theWorkCap = "S";
}
string theAccountCode = "";
if (reportSettings.CastType.ToString() == "Working")
{
theAccountCode = "0";
}
else if (reportSettings.CastType.ToString() == "Secondary")
{
theAccountCode = "1";
}
string theDate = reportSettings.Prodmonth.ToString("yyyyMM");
MWDataManager.clsDataAccess _PlannedVsBookedDay = new MWDataManager.clsDataAccess();
try
{
_PlannedVsBookedDay.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
_PlannedVsBookedDay.SqlStatement = "sp_PlannedVsBooked_Daily";
_PlannedVsBookedDay.queryExecutionType = MWDataManager.ExecutionType.StoreProcedure;
_PlannedVsBookedDay.ResultsTableName = "PlannedVsBookedDay";
SqlParameter[] _paramCollection9 =
{
_PlannedVsBookedDay.CreateParameter("@TypeMonth", SqlDbType.VarChar, 1, theTypeMonth),
_PlannedVsBookedDay.CreateParameter("@Prodmonth", SqlDbType.Int, 7, theDate),
_PlannedVsBookedDay.CreateParameter("@SectionName", SqlDbType.VarChar, 60, reportSettings.NAME),
_PlannedVsBookedDay.CreateParameter("@RunDate", SqlDbType.VarChar, 10, reportSettings.Showuntil.ToString("yyyy-MM-dd")),
_PlannedVsBookedDay.CreateParameter("@SumLevel", SqlDbType.VarChar, 1, reportSettings.HierarchicalID),
_PlannedVsBookedDay.CreateParameter("@Activity", SqlDbType.VarChar, 1, reportSettings.Desc),
_PlannedVsBookedDay.CreateParameter("@Account", SqlDbType.VarChar, 1, theAccountCode),
_PlannedVsBookedDay.CreateParameter("@ReefWaste", SqlDbType.VarChar, 1, theReefWaste),
_PlannedVsBookedDay.CreateParameter("@WorkCap", SqlDbType.VarChar, 1, theWorkCap),
_PlannedVsBookedDay.CreateParameter("@Unit", SqlDbType.VarChar, 20, reportSettings.Type),
};
_PlannedVsBookedDay.ParamCollection = _paramCollection9;
_PlannedVsBookedDay.queryReturnType = MWDataManager.ReturnType.DataTable;
_PlannedVsBookedDay.ExecuteInstruction();
}
catch (Exception _exception)
{
throw new ApplicationException("Report Section:PlannedVsBooked:" + _exception.Message, _exception);
}
MWDataManager.clsDataAccess _PlannedVsBookedProg = new MWDataManager.clsDataAccess();
try
{
_PlannedVsBookedProg.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
_PlannedVsBookedProg.SqlStatement = "sp_PlannedVsBooked_Prog";
_PlannedVsBookedProg.queryExecutionType = MWDataManager.ExecutionType.StoreProcedure;
_PlannedVsBookedProg.ResultsTableName = "PlannedVsBookedProg";
SqlParameter[] _paramCollection9 =
{
_PlannedVsBookedProg.CreateParameter("@TypeMonth", SqlDbType.VarChar, 1, theTypeMonth),
_PlannedVsBookedProg.CreateParameter("@Prodmonth", SqlDbType.Int, 7, theDate),
_PlannedVsBookedProg.CreateParameter("@SectionName", SqlDbType.VarChar, 60, reportSettings.NAME),
_PlannedVsBookedProg.CreateParameter("@RunDate", SqlDbType.VarChar, 10, reportSettings.Showuntil.ToString("yyyy-MM-dd")),
_PlannedVsBookedProg.CreateParameter("@SumLevel", SqlDbType.VarChar, 1, reportSettings.HierarchicalID),
_PlannedVsBookedProg.CreateParameter("@Activity", SqlDbType.VarChar, 1, reportSettings.Desc),
_PlannedVsBookedProg.CreateParameter("@Account", SqlDbType.VarChar, 1, theAccountCode),
_PlannedVsBookedProg.CreateParameter("@ReefWaste", SqlDbType.VarChar, 1, theReefWaste),
_PlannedVsBookedProg.CreateParameter("@WorkCap", SqlDbType.VarChar, 1, theWorkCap),
_PlannedVsBookedProg.CreateParameter("@Unit", SqlDbType.VarChar, 20, reportSettings.Type),
};
_PlannedVsBookedProg.ParamCollection = _paramCollection9;
_PlannedVsBookedProg.queryReturnType = MWDataManager.ReturnType.DataTable;
_PlannedVsBookedProg.ExecuteInstruction();
}
catch (Exception _exception)
{
throw new ApplicationException("Report Section:PlannedVsBooked:" + _exception.Message, _exception);
}
string whatDate = "";
if (theTypeMonth == "P")
{
whatDate = "Production Month : " + reportSettings.Prodmonth.ToString("yyyyMM") + " until " + reportSettings.Showuntil.ToString("yyyy-MM-dd");
}
else
{
whatDate = "Mill Month : " + reportSettings.Prodmonth.ToString("yyyyMM") + " until " + reportSettings.Showuntil.ToString("yyyy-MM-dd");
}
DataSet PlannedVsBookedset = new DataSet();
DataTable dt = new DataTable();
PlannedVsBookedset.Clear();
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Banner", typeof(string));
dt.Columns.Add("TheDate", typeof(string));
MWDataManager.clsDataAccess _dbManCheck = new MWDataManager.clsDataAccess();
_dbManCheck.ConnectionString = TConnections.GetConnectionString(theSystemDBTag, UserCurrentInfo.Connection);
_dbManCheck.SqlStatement = "select CheckMeas,Banner from SysSet ";
_dbManCheck.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
_dbManCheck.queryReturnType = MWDataManager.ReturnType.DataTable;
// _dbManLosses.ResultsTableName = "Days"; //get table name
_dbManCheck.ExecuteInstruction();
SysSettings.CheckMeas = _dbManCheck.ResultsDataTable.Rows[0][0].ToString();
SysSettings.Banner = _dbManCheck.ResultsDataTable.Rows[0][1].ToString();
dt.Rows.Add(reportSettings.Type, SysSettings.Banner, whatDate);
Report theReport = new Report();
if (_PlannedVsBookedDay.ResultsDataTable.Rows.Count == 0 || _PlannedVsBookedProg.ResultsDataTable.Rows.Count == 0)
{
MessageBox.Show("There is no data for your selection", "", MessageBoxButtons.OK);
theReport.Prepare();
ActiveReport.SetReport = theReport;
ActiveReport.isDone = true;
}
else
{
PlannedVsBookedset.Tables.Add(_PlannedVsBookedDay.ResultsDataTable);
PlannedVsBookedset.Tables.Add(_PlannedVsBookedProg.ResultsDataTable);
PlannedVsBookedset.Tables.Add(dt);
theReport.RegisterData(PlannedVsBookedset);
theReport.Load(TGlobalItems.ReportsFolder + "\\PlannedVsBooked.frx");
theReport.SetParameterValue("logo", TGlobalItems.ClientLogo);
theReport.SetParameterValue("logoMineware", TGlobalItems.CompanyLogo);
//theReport.Design();
if (TParameters.DesignReport)
{
theReport.Design();
}
theReport.Prepare();
ActiveReport.SetReport = theReport;
ActiveReport.isDone = true;
}
}
