//get he indirects for the indirect dropdown list
public static List <string> getIndirectIds(string instance)
{
//open con to the api
conOpen();
List <string> indProjets = new List <string>();
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("INDIRECT", "SELECT ID, DESCRIPTION FROM VMFG.INDIRECT");
gen.Execute();
if (gen.Tables["INDIRECT"].Rows.Count > 0)
{
for (var i = 0; i <= gen.Tables["INDIRECT"].Rows.Count - 1; i++)
{
indProjets.Add(gen.Tables["INDIRECT"].Rows[i]["ID"].ToString() + "-" + gen.Tables["INDIRECT"].Rows[i]["DESCRIPTION"].ToString());
}
Dbms.Close(instName);
return(indProjets);
}
Dbms.Close(instName);
return(indProjets);
}
public IActionResult LargeFilter(UserHomeViewModel zeuserHomeViewModel)
{
QueryGenerally queryGenerally = new QueryGenerally(db);
GeneralQuery generalQuery = new GeneralQuery()
{
Country = zeuserHomeViewModel.Country,
Keyword = zeuserHomeViewModel.Keyword,
MaxPrice = zeuserHomeViewModel.MaxPrice,
MinPrice = zeuserHomeViewModel.MinPrice,
NumberOfBathrooms = zeuserHomeViewModel.NumberOfBathrooms,
NumberofBedrooms = zeuserHomeViewModel.NumberofBedrooms,
PropertyStatus = zeuserHomeViewModel.PropertyStatus,
PropertyType = zeuserHomeViewModel.PropertyType
};
UserHomeViewModel userGeneralViewModel = new UserHomeViewModel()
{
SliderProperties = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType).Where(m => m.isDealClosed == true && m.isDisplayed == true),
OurServices = db.OurServices.OrderByDescending(m => m.Id),
RecentProperties = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType).OrderByDescending(m => m.Id),
PopularProperties = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType).Where(m => m.isDealClosed),
PropertyModes = db.propertyModes,
PropertyTypes = db.PropertyTypes,
Testimonials = db.Testimonials.Include(m => m.User).Where(m => m.isApproved == true),
AllProperties = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType),
MenuProperties = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType).OrderByDescending(m => m.Id).Take(10),
SocialMedia = db.SocialMedias.OrderByDescending(m => m.Id).FirstOrDefault()
};
userGeneralViewModel.RecentProperties = queryGenerally.GetPropertieszz(generalQuery);
return(View("PropertyList", userGeneralViewModel));
}
//gets leg 'description'. This is called when loading the tickets for the html table
public static string getSimpleLeg(string baseId, string subId, string instance)
{
//open con to the api
conOpen();
string leg = "";
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("WORKORDER", "Select BASE_ID, SUB_ID, PART_ID FROM VMFG.WORK_ORDER WHERE BASE_ID = ? AND SUB_ID = ?");
gen.Parameters[0] = baseId;
gen.Parameters[1] = subId;
gen.Execute();
if (gen.Tables["WORKORDER"].Rows.Count > 0)
{
for (var i = 0; i <= gen.Tables["WORKORDER"].Rows.Count - 1; i++)
{
leg = gen.Tables["WORKORDER"].Rows[i]["PART_ID"].ToString();
}
}
Dbms.Close(instName);
return(leg);
}
//get the legs for the product
public static List <string> getWorkOrderLegs(string baseId, string instance)
{
//open con to the api
conOpen();
List <string> workOrders = new List <string>();
GeneralQuery gen = new GeneralQuery(instName);
gen.Prepare("WORKORDER", "Select BASE_ID, SUB_ID, PART_ID FROM VMFG.WORK_ORDER WHERE BASE_ID = ?");
gen.Parameters[0] = baseId;
gen.Execute();
if (gen.Tables["WORKORDER"].Rows.Count > 0)
{
for (var i = 0; i <= gen.Tables["WORKORDER"].Rows.Count - 1; i++)
{
workOrders.Add(gen.Tables["WORKORDER"].Rows[i]["SUB_ID"].ToString() + " " + gen.Tables["WORKORDER"].Rows[i]["PART_ID"].ToString());
}
Dbms.Close(instName);
return(workOrders);
}
Dbms.Close(instName);
return(workOrders);
}
//check the amount of hours to set overtime and banking options
public static decimal checkBankingHr(string date, string instance)
{
//open con to the api
conOpen();
if (date == null)
{
date = DateTime.Now.Date.ToString("yyyy-MM-dd");
}
decimal hours = 0;
GeneralQuery gen = null;
Instance currInstance = Dbms.GetInstanceInfo(instName);
gen = new GeneralQuery(instance);
gen.Prepare("LABOR_TICKET", "SELECT SUM(HOURS_WORKED) FROM VMFG.LABOR_TICKET WHERE DateDiff(wk,TRANSACTION_DATE,'" + date + "') = 0 AND SUBSTRING(REVERSE(DESCRIPTION), CHARINDEX(',',REVERSE(DESCRIPTION)) + 1, 2) = 'KB'");
gen.Execute();
if (gen.Tables["LABOR_TICKET"].Rows.Count > 0 && !gen.Tables["LABOR_TICKET"].Rows[0]["Column1"].ToString().Equals(""))
{
hours = Decimal.Parse(gen.Tables["LABOR_TICKET"].Rows[0]["Column1"].ToString());
Dbms.Close(instName);
return(hours);
}
Dbms.Close(instName);
return(hours);
}
//gets the date range avaible for new tickets, update and delete
public static Model.DatePeriodModel getDateRange(string instance)
{
//open con to the api
conOpen();
Model.DatePeriodModel dp = new Model.DatePeriodModel();
GeneralQuery gen = null;
gen = new GeneralQuery(instName);
gen.Prepare("ACCOUNT_PERIOD", "SELECT MIN(BEGIN_DATE) FROM VMFG.ACCOUNT_PERIOD WHERE STATUS = 'A' AND YEAR(BEGIN_DATE) = YEAR(GETDATE()) AND CALENDAR_ID = 'BACH'");
gen.Execute();
if (gen.Tables["ACCOUNT_PERIOD"].Rows.Count > 0)
{
dp.min = DateTime.Parse(gen.Tables["ACCOUNT_PERIOD"].Rows[0]["Column1"].ToString());
}
gen.Prepare("ACCOUNT_PERIOD", "SELECT MAX(END_DATE) FROM VMFG.ACCOUNT_PERIOD WHERE STATUS = 'A' AND YEAR(END_DATE) = YEAR(GETDATE()) AND CALENDAR_ID = 'BACH'");
gen.Execute();
if (gen.Tables["ACCOUNT_PERIOD"].Rows.Count > 0)
{
dp.max = DateTime.Parse(gen.Tables["ACCOUNT_PERIOD"].Rows[0]["Column1"].ToString());
}
Dbms.Close(instName);
return(dp);
}
//get the seq number/opn for the product/leg
public static List <string> getSeqNumber(string baseId, string subId, string instance)
{
//open con to the api
conOpen();
List <string> seqNumbers = new List <string>();
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("OPERATION", "SELECT SEQUENCE_NO, RESOURCE_ID FROM VMFG.OPERATION WHERE WORKORDER_BASE_ID = ? AND WORKORDER_SUB_ID = ?");
gen.Parameters[0] = baseId;
gen.Parameters[1] = subId;
gen.Execute();
if (gen.Tables["OPERATION"].Rows.Count > 0)
{
for (var i = 0; i <= gen.Tables["OPERATION"].Rows.Count - 1; i++)
{
seqNumbers.Add(gen.Tables["OPERATION"].Rows[i]["SEQUENCE_NO"].ToString() + " " + gen.Tables["OPERATION"].Rows[i]["RESOURCE_ID"].ToString());
}
Dbms.Close(instName);
return(seqNumbers);
}
Dbms.Close(instName);
return(seqNumbers);
}
//get the product
public static Model.WorkOrderModel getWorkOrderId(string baseId, string instance)
{
//open con to the api
conOpen();
Model.WorkOrderModel wo = new Model.WorkOrderModel();
string query = "Select BASE_ID, LOT_ID, SPLIT_ID, STATUS FROM VMFG.WORK_ORDER WHERE USER_9 LIKE '%,' + ? + ',%'";
GeneralQuery gen = null;
gen = new GeneralQuery(instName);
gen.Prepare("WORK_ORDER", query);
gen.Parameters[0] = baseId;
gen.Execute();
if (gen.Tables["WORK_ORDER"].Rows.Count > 0)
{
wo.baseId = gen.Tables["WORK_ORDER"].Rows[0]["BASE_ID"].ToString();
wo.lotId = gen.Tables["WORK_ORDER"].Rows[0]["LOT_ID"].ToString();
wo.splitId = gen.Tables["WORK_ORDER"].Rows[0]["SPLIT_ID"].ToString();
wo.status = gen.Tables["WORK_ORDER"].Rows[0]["STATUS"].ToString();
}
Dbms.Close(instName);
return(wo);
}
//gets the collum RUN_COST_PER_HR
public static string getHourlyCost(string baseId, string subId, string seqNo, string instance)
{
//open con to the api
conOpen();
string hc = "";
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("OPERATION", "SELECT RUN_COST_PER_HR FROM VMFG.OPERATION WHERE WORKORDER_BASE_ID = ? AND WORKORDER_SUB_ID = ? AND SEQUENCE_NO = ?");
gen.Parameters[0] = baseId;
gen.Parameters[1] = subId;
gen.Parameters[2] = Int32.Parse(seqNo);
gen.Execute();
if (gen.Tables["OPERATION"].Rows.Count > 0)
{
hc = gen.Tables["OPERATION"].Rows[0]["RUN_COST_PER_HR"].ToString();
}
Dbms.Close(instName);
return(hc);
}
public JsonResult CourseListJson(int page, int limit)
{
var where = GeneralQuery.Get(Request.QueryString);
var query = Project.GetList(where);
var data = new JsonReturn(query, page, limit);
return(Json(data, JsonRequestBehavior.AllowGet));
}
//get the last day for the period
public static DateTime getPeriodLastDay(string instance)
{
//open con to the api
conOpen();
DateTime dt = new DateTime();
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("ACCOUNT_PERIOD", "SELECT END_DATE FROM VMFG.ACCOUNT_PERIOD WHERE STATUS = 'A' AND GETDATE() BETWEEN BEGIN_DATE AND END_DATE AND CALENDAR_ID = 'BACH'");
gen.Execute();
if (gen.Tables["ACCOUNT_PERIOD"].Rows.Count > 0)
{
dt = DateTime.Parse(gen.Tables["ACCOUNT_PERIOD"].Rows[0]["END_DATE"].ToString());
}
Dbms.Close(instName);
return(dt);
}
//list all tickets for specific date
public static List <Model.LaborTicketModel> listLaborTicket(string date, string employeeId, string instance)
{
//open con to the api
conOpen();
List <Model.LaborTicketModel> tickets = new List <Model.LaborTicketModel>();
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("LABOR_TICKET", "SELECT TRANSACTION_ID, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, OPERATION_SEQ_NO, RESOURCE_ID, HOURS_WORKED, MULTIPLIER_1, DESCRIPTION, INDIRECT_ID FROM VMFG.LABOR_TICKET WHERE TRANSACTION_DATE = ? AND EMPLOYEE_ID = ?");
gen.Parameters[0] = date;
gen.Parameters[1] = employeeId;
gen.Execute();
if (gen.Tables["LABOR_TICKET"].Rows.Count > 0)
{
for (var i = 0; i <= gen.Tables["LABOR_TICKET"].Rows.Count - 1; i++)
{
Model.LaborTicketModel lt = new Model.LaborTicketModel();
lt.ltnum = gen.Tables["LABOR_TICKET"].Rows[i]["TRANSACTION_ID"].ToString();
lt.woind = gen.Tables["LABOR_TICKET"].Rows[i]["WORKORDER_BASE_ID"].ToString() + "/" + gen.Tables["LABOR_TICKET"].Rows[i]["WORKORDER_LOT_ID"].ToString() + "/" + gen.Tables["LABOR_TICKET"].Rows[i]["WORKORDER_SPLIT_ID"].ToString();
lt.leg = gen.Tables["LABOR_TICKET"].Rows[i]["WORKORDER_SUB_ID"].ToString();
lt.seqNum = gen.Tables["LABOR_TICKET"].Rows[i]["OPERATION_SEQ_NO"].ToString();
lt.resource = gen.Tables["LABOR_TICKET"].Rows[i]["RESOURCE_ID"].ToString();
lt.hoursworked = gen.Tables["LABOR_TICKET"].Rows[i]["HOURS_WORKED"].ToString();
lt.overtime = gen.Tables["LABOR_TICKET"].Rows[i]["MULTIPLIER_1"].ToString();
lt.description = gen.Tables["LABOR_TICKET"].Rows[i]["DESCRIPTION"].ToString();
lt.indirectId = gen.Tables["LABOR_TICKET"].Rows[i]["INDIRECT_ID"].ToString();
tickets.Add(lt);
}
Dbms.Close(instName);
return(tickets);
}
Dbms.Close(instName);
return(tickets);
}
//check if the date value is really inside the available range
//this is to avoid CLIENT SIDE manipulation
public static bool checkDate(string date, string instance)
{
//open con to the api
conOpen();
//Model.DatePeriodModel dp = new Model.DatePeriodModel();
GeneralQuery gen = null;
gen = new GeneralQuery(instName);
gen.Prepare("ACCOUNT_PERIOD", "SELECT * FROM VMFG.ACCOUNT_PERIOD WHERE ? BETWEEN BEGIN_DATE AND END_DATE AND STATUS = 'A'");
gen.Parameters[0] = date;
gen.Execute();
if (gen.Tables["ACCOUNT_PERIOD"].Rows.Count > 0)
{
Dbms.Close(instName);
return(true);
}
Dbms.Close(instName);
return(false);
}
//loads the indirect when a bugzilla item is indirect
public static string getSimpleIndirect(string id, string instance)
{
//open con to the api
conOpen();
string indirect = "";
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("INDIRECT", "SELECT ID, DESCRIPTION FROM VMFG.INDIRECT WHERE ID = ?");
gen.Parameters[0] = id;
gen.Execute();
if (gen.Tables["INDIRECT"].Rows.Count > 0)
{
Dbms.Close(instName);
return(indirect = gen.Tables["INDIRECT"].Rows[0]["ID"].ToString() + "-" + gen.Tables["INDIRECT"].Rows[0]["DESCRIPTION"].ToString());
}
Dbms.Close(instName);
return(indirect);
}
//gets the employee ID, first name and last name based on the windows account name
public static Model.EmployeeModel getEmployeeID(string name, string instance)
{
//open con to the
conOpen();
Model.EmployeeModel em = new Model.EmployeeModel();
GeneralQuery gen = null;
//Instance currInstance = Dbms.GetInstanceInfo(instance);
gen = new GeneralQuery(instName);
gen.Prepare("EMPLOYEE", "SELECT ID, FIRST_NAME, LAST_NAME FROM VMFG.EMPLOYEE WHERE EMAIL_ADDR = ?");
gen.Parameters[0] = name;
gen.Execute();
if (gen.Tables["EMPLOYEE"].Rows.Count > 0)
{
em.id = gen.Tables["EMPLOYEE"].Rows[0]["ID"].ToString();
em.firstname = gen.Tables["EMPLOYEE"].Rows[0]["FIRST_NAME"].ToString();
em.lastname = gen.Tables["EMPLOYEE"].Rows[0]["LAST_NAME"].ToString();
}
Dbms.Close(instName);
return(em);
}
public IQueryable <Property> GetPropertieszz(GeneralQuery generalQuery)
{
var result = db.Properties.Include(m => m.PropertyMode).Include(m => m.PropertyType).AsQueryable();
if (generalQuery != null)
{
if (!string.IsNullOrEmpty(generalQuery.Keyword))
{
result = result.Where(m => m.Address.Contains(generalQuery.Keyword) ||
m.Address.Contains(generalQuery.Keyword) ||
m.GoogleMapAddress.Contains(generalQuery.Keyword) ||
m.Description.Contains(generalQuery.Keyword) ||
m.PropertyMode.Name.Contains(generalQuery.Keyword)
);
}
if (!string.IsNullOrEmpty(generalQuery.Country))
{
result = result.Where(m => m.Address.Contains(generalQuery.Country) ||
m.Address.Contains(generalQuery.Country) ||
m.GoogleMapAddress.Contains(generalQuery.Country)
);
}
if (!string.IsNullOrEmpty(generalQuery.NumberOfBathrooms.ToString()))
{
if (generalQuery.NumberOfBathrooms > 1 && generalQuery.NumberOfBathrooms < 4)
{
result = result.Where(m => m.bathrooms == generalQuery.NumberOfBathrooms);
}
else if (generalQuery.NumberOfBathrooms == 4)
{
result = result.Where(m => m.bathrooms > 3);
}
else
{
}
}
if (!string.IsNullOrEmpty(generalQuery.NumberofBedrooms.ToString()))
{
if (generalQuery.NumberofBedrooms > 0 && generalQuery.NumberofBedrooms < 4)
{
result = result.Where(m => m.BedRooms == generalQuery.NumberofBedrooms);
}
else if (generalQuery.NumberofBedrooms == 4)
{
result = result.Where(m => m.BedRooms > 3);
}
}
if (!string.IsNullOrEmpty(generalQuery.PropertyStatus.ToString()))
{
result = result.Where(m => m.PropertyModeId == generalQuery.PropertyStatus);
}
if (!string.IsNullOrEmpty(generalQuery.PropertyType.ToString()))
{
result = result.Where(m => m.PropertyTypeId == generalQuery.PropertyType);
}
}
return(result);
}
public void ExportToExcel(Int64 OGPCode)
{
DataTable dt = GeneralQuery.GetDocumentPickList(OGPCode);
//create a new byte array
byte[] bin;
string FileName = "Pick-List-" + DateTime.Now.ToString();
//create a new excel document
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new worksheet
ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(FileName);
//add the contents of the datatable to the excel file
ws.Cells["A1"].LoadFromDataTable(dt, true);
//auto fix the columns
ws.Cells[ws.Dimension.Address].AutoFitColumns();
//loop all the columns
for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
//make all columns just a bit wider, it would sometimes not fit
ws.Column(col).Width = ws.Column(col).Width + 1;
var cell = ws.Cells[1, col];
//make the text bold
cell.Style.Font.Bold = true;
//make the background of the cell gray
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF"));
//make the header text upper case
cell.Value = ((string)cell.Value).ToUpper();
}
//convert the excel package to a byte array
bin = excelPackage.GetAsByteArray();
}
//clear the buffer stream
Response.ClearHeaders();
Response.Clear();
Response.Buffer = true;
//set the correct contenttype
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//set the correct length of the data being send
Response.AddHeader("content-length", bin.Length.ToString());
//set the filename for the excel package
Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + ".xlsx\"");
//send the byte array to the browser
Response.OutputStream.Write(bin, 0, bin.Length);
//cleanup
Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
public ActionResult Course()
{
var query = new GeneralQuery();
return(View(query));
}
