//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)); }