//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);
}
}
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);
}
public DataTable getProblemTypeLinks(string Problem_Type, int _activity)
{
bool HasError = false;
try
{
TheData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
TheData.SqlStatement = "select \n" +
"a.ProblemID,\n" +
"a.Description,\n" +
"Linked = Case when b.ProblemTypeID is null then Cast(0 as Bit) else Cast(1 as Bit) end\n" +
" from CODE_PROBLEM a left join \n" +
"(select a.*, b.ProblemID from CODE_PROBLEM_TYPE a left join PROBLEM_TYPE b \n" +
"on a.Activity = b.Activity and\n" +
"a.ProblemTypeID = b.ProblemTypeID\n" +
"where a.Deleted IN ('Y', 'N')\n" +
"and a.Activity = " + _activity + "\n" +
"and a.ProblemTypeID = '" + Problem_Type + "') b on\n" +
"a.ProblemID = b.ProblemID and\n" +
"a.Activity = b.Activity\n" +
"where \n" +
"a.Deleted IN ('Y', 'N') and\n" +
"a.Activity = " + _activity;
TheData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
TheData.queryReturnType = MWDataManager.ReturnType.DataTable;
TheData.ExecuteInstruction();
}
catch
{
HasError = true;
}
if (HasError == true)
{
return(null);
}
else
{
return(TheData.ResultsDataTable);
}
}
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;
}
}