/// <summary>
/// Returns the full list of invoices from the database
/// </summary>
/// <returns></returns>
public ObservableCollection<clsInvoice> invoicesCollection()
{
try
{
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet();
db = new clsDataAccess();
ObservableCollection<clsInvoice> col_Invoices = new ObservableCollection<clsInvoice>();
clsInvoice invoice;
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
for (int i = 0; i < iRet; i++)
{
invoice = new clsInvoice();
invoice.InvoiceNum = ds.Tables[0].Rows[i][0].ToString();
invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString();
col_Invoices.Add(invoice);
}
return col_Invoices;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
public ObservableCollection<clsItem> itemsCollection()
{
clsDataAccess db;
ObservableCollection<clsItem> col_Items;
db = new clsDataAccess();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
DataSet ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";
col_Items = new ObservableCollection<clsItem>();
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//Creates Flight objects based on the data pulled from the query than adds the object to a list
for (int i = 0; i < iRet; i++)
{
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString();
col_Items.Add(items);
}
return col_Items;
}
DataSet ds; // Dataset to hold the returned data from queries
/// <summary>
/// Returns the full list of jewelry items from the database
/// </summary>
/// <returns></returns>
public ObservableCollection<clsItem> itemsCollection()
{
try
{
db = new clsDataAccess();
ObservableCollection<clsItem> col_Items = new ObservableCollection<clsItem>();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//Creates item objects based on the data pulled from the query than adds the object to a list
for (int i = 0; i < iRet; i++)
{
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString();
col_Items.Add(items);
}
return col_Items;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
public frmUpdate(object sender)
{
this.InitializeComponent();
wnd_mainWindow = (MainWindow)sender;
db = new clsDataAccess();
UpdateBox();
}
public DataTable GetProdMonth()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select CurrentProductionMonth Prodmonth 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);
}
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);
}
/// <summary>
/// constructor. populates the different flights that are available into the combo box
/// </summary>
public MainWindow()
{
InitializeComponent();
try
{
Application.Current.ShutdownMode = ShutdownMode.OnMainWindowClose;
//create instance of reservations
clsDataAccess db = new clsDataAccess();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
DataSet ds = new DataSet(); //Holds the return values
FlightViewModel cFlight;
//sql statment to get flight info
sSQL = "SELECT Flight_ID, Flight_Number, Aircraft_Type FROM FLIGHT";
//extract flight info into dataset
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//loop through data and create flight class
for (int i = 0; i < iRet; i++)
{
cFlight = new FlightViewModel();
cFlight.ID = ds.Tables[0].Rows[i][0].ToString();
cFlight.FlightNumber = ds.Tables[0].Rows[i][1].ToString();
cFlight.AircraftType = ds.Tables[0].Rows[i][2].ToString();
FlightCombo.Items.Add(cFlight);
}
//disable all controls
DisableControls();
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
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);
}
/// <summary>
/// Gets a list of all invoices.
/// </summary>
/// <returns>A List of all Invoices.</returns>
public static List <Invoice> GetInvoices()
{
try
{
clsDataAccess db = new clsDataAccess();
string sSQL = clsMainSQL.GetInvoices();
int numRows = 0;
DataSet ds = db.ExecuteSQLStatement(sSQL, ref numRows);
var tableszero = ds.Tables[0];
List <Invoice> invoiceList = new List <Invoice>();
for (int i = 0; i < tableszero.Rows.Count; i++)
{
var rowEntry = tableszero.Rows[i];
invoiceList.Add(new Invoice()
{
InvoiceNumber = int.TryParse(rowEntry.ItemArray[0].ToString(), out int inNumber) ? inNumber : 0,
InvoiceDate = DateTime.Parse(rowEntry.ItemArray[1].ToString()),
TotalCost = double.TryParse(rowEntry.ItemArray[2].ToString(), out double totalCost) ? totalCost : 0,
Items = GetItems(inNumber),
});
}
public DataTable GetDuplicateSampleData(string _shaft)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select PayLimit ");
sb.AppendLine("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);
}
/// <summary>
/// Constructor of a new instance of the clsSearchLogic object
/// </summary>
public clsSearchLogic()
{
try
{
// initialize data access class
db = new clsDataAccess();
searchSql = new clsSearchSql(); // initialize a new search sql class
iINum = 0; // no invoices yet
ds = new DataSet(); // new dataset
invoiceList = new List <clsInvoiceObj>(); // new list of invoices objects
sSelectedInvoiceNum = ""; // not selected
sSelectedInvoiceDate = ""; // not selected
sSelectedTotal = ""; // not selected
}
catch (Exception ex)
{
//This is the top level method so we want to handle the exception
HandleError(MethodInfo.GetCurrentMethod().DeclaringType.Name,
MethodInfo.GetCurrentMethod().Name, ex.Message);
}
} // end of constructor
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 LoadBHID()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine(" select 'BH'+Convert(Varchar(50),max(replace(replace(Oreflowid,'BH', ''),'B',''))+1) BHID from Oreflowentities where OreflowCode = 'BH' ");
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 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 LoadReefTypes()
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select ReefID, Description from Reef where selected = 1 ");
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 getTotalShiftsList(string _BeginDate, string _EndDate, string _calendarCode)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select count(WorkingDay) from Caltype ");
sb.AppendLine("where CalendarCode = '" + _calendarCode + "' and WorkingDay = 'Y' and ");
sb.AppendLine("CalendarDate >= '" + _BeginDate + "' and ");
sb.AppendLine("CalendarDate <= '" + _EndDate + "'");
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 GetHoistingPlanningData(string _millMonth, string _shaft)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select isnull(PlanTons,0) PlanTons, isnull(PlanBeltGrade,0) PlanBeltGrade, isnull(PlanGold,0) PlanGold ");
sb.AppendLine("from PlanHoist ");
sb.AppendLine("where MillMonth = '" + _millMonth + "' and ");
sb.AppendLine("OreflowID = '" + _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 GetSections(string _prodMonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("Select SectionID, Name ");
sb.AppendLine("from Section s where s.Prodmonth = '" + _prodMonth + "' ");
sb.AppendLine("and Hierarchicalid = 4 ");
sb.AppendLine("order by Hierarchicalid, SECTIONid asc ");
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 GetMillingData(string _millMonth, string _mill)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select distinct(cl.CalendarDate) CalendarDate, TotalShifts, ");
sb.AppendLine("Treated = isnull(convert(numeric(13,0),b.TonsTreated), 0), ");
sb.AppendLine("ToPlant = isnull(convert(numeric(13,0),b.TonsToPlant), 0), WorkingDay, ");
sb.AppendLine("PlannedTonsTreated = isnull(convert(numeric(13,0),b.PlannedTonsTreated), 0), ");
sb.AppendLine("PlannedTonsToPlant = isnull(convert(numeric(13,0),b.PlannedTonsToPlant), 0)");
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 + "' ");
sb.AppendLine("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 BookingMilling b on ");
sb.AppendLine("b.CalendarDate = cl.CalendarDate and ");
sb.AppendLine("b.OreflowID = '" + _mill + "' and b.MillMonth = '" + _millMonth + "' ");
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);
}
// save invoice
// update invoice
/// <summary>
/// Get invoice by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal void GetInvoice(int id)
{
DataSet ds = new DataSet();
clsData = new clsDataAccess();
Invoice inv = new Invoice();
inv.Items = new List <Item>();
try
{
string sSQL = String.Format(clsMainSQL.GetInvoice, id);
int iRet = 0;
ds = clsData.ExecuteSQLStatement(sSQL, ref iRet);
for (int i = 0; i < iRet; i++)
{
inv.InvoiceNumber = ds.Tables[0].Rows[i][0].ToString();
inv.InvoiceDate = DateTime.Parse(ds.Tables[0].Rows[i][1].ToString());
inv.Items.Add(new Item()
{
Code = ds.Tables[0].Rows[i][2].ToString(),
Description = ds.Tables[0].Rows[i][3].ToString(),
Cost = int.Parse(ds.Tables[0].Rows[i][4].ToString())
});
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
CurrentInvoice = inv;
}
/// <summary>
/// Get all total charges
/// </summary>
public static List <string> GetCboTotCharge()
{
try
{
List <string> totChargeList = new List <string>();
int numRows = 0;
clsDataAccess db;
db = new clsDataAccess();
DataSet ds = db.ExecuteSQLStatement(clsSearchSQL.GetInvoiceTotCharge(), ref numRows);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
totChargeList.Add(ds.Tables[0].Rows[i][0].ToString());
}
return(totChargeList);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
public DataTable GetStopingData(string _prodMonth, string _activity, string _section)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select ''Status,divisioncode ExeName, 'xxx' Costareaid, W.Workplaceid, ");
sb.AppendLine("w.Description, s.Prodmonth,cs.StopeTypeDesc Type, 'KR' Type2, ");
sb.AppendLine("s.LedgeSqm Lsqm, s.StopeSqm SSQM, s.LedgeSqmOSF LOSF, s.StopeSqmOSF SOSF, ");
sb.AppendLine("s.LedgeSqmOS LOS, s.StopeSqmOS SOS, s.FLTotal Facelength, ");
sb.AppendLine("s.LedgeFL LFL, s.StopeFL FFL, s.FLOSTotal OSFL, s.CleanSqm SQMClean, ");
sb.AppendLine("s.CleanDist DistFromFace, s.CW ChnWidth, s.FW FWWidth, s.HW HWWidth, ");
sb.AppendLine("s.SWSQM StpWidth, s.CrewMorning, s.CrewAfternoon, s.CrewEvening, ");
sb.AppendLine("'Miner' Hierarchicalname, sc.name, ");
sb.AppendLine("case when M2NOTTOBEPAIDFORMINER is null then 0 else M2NOTTOBEPAIDFORMINER end M2NOTTOBEPAIDFORMINER, ");
sb.AppendLine("case when MINSPRETARGETSW2 is null then 0 else MINSPRETARGETSW2 end MINSPRETARGETSW2, ");
sb.AppendLine("case when MidmonthIndicator is null then 0 else MidmonthIndicator end MidmonthIndicator, ");
sb.AppendLine("case when DISTFROMFACEMIDMONTH is null then 0 else DISTFROMFACEMIDMONTH end DISTFROMFACEMIDMONTH, ");
sb.AppendLine("case when SweepingPenalty is null then 0 else SweepingPenalty end SweepingPenalty, SF1SF2 ");
sb.AppendLine("from survey s inner join workplace w on w.WorkplaceID = s.workplaceid ");
sb.AppendLine("inner join Code_StopeTypes cs on cs.StopeTypeID = s.SType ");
sb.AppendLine("inner join SECTION_COMPLETE sc on s.prodmonth = sc.prodmonth and s.sectionid = sc.sectionid ");
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 DataTable UnlockLockSurveyData(string _prodMonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("update Survey_Locks ");
sb.AppendLine("set Locked = 0");
sb.AppendLine("");
sb.AppendLine(" where 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 GetReefType(string _oreflowID)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine("select o.ReefType ");
sb.AppendLine("from dbo.OREFLOWENTITIES o ");
sb.AppendLine("left outer join oreflowentities oe ");
sb.AppendLine("on o.parentoreflowid = oe.oreflowid ");
sb.AppendLine("where o.oreflowcode = 'BH' and o.OreFlowID = '" + _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 LockSurveyData(string _prodMonth)
{
clsDataAccess newData = new clsDataAccess();
try
{
sb.Clear();
sb.AppendLine(" begin try insert into Survey_Locks values ( ");
sb.AppendLine(" '" + _prodMonth + "',1,getdate(), ");
sb.AppendLine(" '" + TUserInfo.UserID.ToString() + "' ");
sb.AppendLine(" ) end try begin catch update Survey_Locks set Locked = 1, datelocked = getdate(),LockedByID = '" + TUserInfo.UserID.ToString() + "' where prodmonth = '" + _prodMonth + "' end catch");
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);
}
/// <summary>
/// Adds a new invoice to the database
/// </summary>
/// <param name="date"></param>
/// <param name="totalCharge"></param>
/// <param name="items"></param>
public void addInvoice(string date, string totalCharge, ObservableCollection<clsItem> items)
{
try
{
db = new clsDataAccess();
int iRet = 0;
string sSQL = "INSERT INTO Invoices( InvoiceDate, TotalCharge) VALUES(#" +
date + "#, " + totalCharge + ")";
db.ExecuteNonQuery(sSQL);
string sSQLInvoiceNumber = "SELECT MAX(InvoiceNum) FROM Invoices";
DataSet newInvoice = db.ExecuteSQLStatement(sSQLInvoiceNumber, ref iRet);
string invoiceNumber = newInvoice.Tables[0].Rows[0][0].ToString();
int i = 1;
foreach (clsItem item in items)
{
string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " +
"VALUES('" + invoiceNumber + "', '" + i + "' , '" + item.ItemCode + "')";
db.ExecuteNonQuery(sSQLLineItem);
i += 1;
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
public CustomerController(IConfiguration iconfig)
{
_config = iconfig;
cls = new clsDataAccess(_config);
}
public ReportController(IConfiguration iconfig)
{
_config = iconfig;
cls = new clsDataAccess(_config);
}
/// <summary>
/// Constructor for InvoiceDataMaster.
/// </summary>
public InvoiceDataMaster()
{
DataAccess = new clsDataAccess();
DataSQL = new InvoiceSQL();
}
public clsSearchSQL()
{
db = new clsDataAccess();
}
public TransactionController(IConfiguration iconfig)
{
_config = iconfig;
cls = new clsDataAccess(_config);
}
public void SaveData(string _id, string _problemCode, string action)
{
clsDataAccess newData = new clsDataAccess();
try
{
if (action == "Add")
{
if (_problemCode != "")
{
sb.Clear();
sb.AppendLine("select * from CheckTrammingProblems where ID = '" + _id + "'");
newData.ConnectionString = theData.ConnectionString;
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
newData.queryReturnType = MWDataManager.ReturnType.DataTable;
newData.SqlStatement = sb.ToString();
newData.ExecuteInstruction();
DataTable dtProblems = newData.ResultsDataTable;
if (dtProblems.Rows.Count > 0)
{
string _problem = dtProblems.Rows[0]["ProblemCode"].ToString();
if (_problemCode.Contains(_problem))
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", "Cannot insert duplicate rows", Color.Red);
}
}
else
{
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
sb.Clear();
sb.AppendLine("insert into CheckTrammingProblems (ProblemCode) values ");
sb.AppendLine("('" + _problemCode + "') ");
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", "Data was saved", Color.CornflowerBlue);
}
}
else
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", "Required fields cannot be blank", Color.Red);
}
}
else if (action == "Edit")
{
if (_problemCode != "")
{
newData.queryExecutionType = MWDataManager.ExecutionType.GeneralSQLStatement;
sb.Clear();
sb.AppendLine("update CheckTrammingProblems ");
sb.AppendLine("set ProblemCode = '" + _problemCode + "' ");
sb.AppendLine("where ID = '" + _id + "'");
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", "Data was saved", Color.CornflowerBlue);
}
else
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Error", "Required fields cannot be blank", Color.Red);
}
}
}
catch (Exception _exception)
{
Mineware.Systems.Global.sysNotification.TsysNotification.showNotification("Exception Error", _exception.Message, Color.Red);
}
}
/// <summary>
/// Returns the collection of items from a certain invoice
/// </summary>
/// <param name="invoiceNumber"></param>
/// <returns></returns>
public ObservableCollection<clsItem> invoiceItems(string invoiceNumber)
{
try
{
db = new clsDataAccess();
ObservableCollection<clsItem> col_Items = new ObservableCollection<clsItem>();
List<string> itemCode = new List<string>();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode FROM LineItems "
+ "WHERE InvoiceNum = " + invoiceNumber;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
// adds all of the different item codes on the invoice to a list
for (int i = 0; i < iRet; i++)
{
itemCode.Add(ds.Tables[0].Rows[i]["ItemCode"].ToString());
}
if (itemCode.Count > 0)
{
foreach (string code in itemCode)
{
sSQL = "SELECT ItemCode, ItemDesc, Cost FROM ItemDesc "
+ "WHERE ItemCode = '" + code + "'";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[0][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[0]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[0]["Cost"].ToString();
col_Items.Add(items);
}
}
return col_Items;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Updates an existing invoice in the database
/// </summary>
/// <param name="invoiceNum"></param>
/// <param name="totalCharge"></param>
/// <param name="items"></param>
public void updateInvoice(string invoiceNum, string totalCharge, ObservableCollection<clsItem> items)
{
try
{
db = new clsDataAccess();
string sSQL = "UPDATE Invoices " +
"SET TotalCharge = '" + totalCharge +
"' WHERE InvoiceNum = " + invoiceNum;
db.ExecuteNonQuery(sSQL);
sSQL = "Delete FROM LineItems WHERE InvoiceNum = " + invoiceNum;
db.ExecuteNonQuery(sSQL);
int i = 1;
foreach (clsItem item in items)
{
string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " +
"VALUES('" + invoiceNum + "', '" + i + "' , '" + item.ItemCode + "')";
db.ExecuteNonQuery(sSQLLineItem);
i += 1;
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
public clsMainSql()
{
_dataAccess = new clsDataAccess();
}