public bool IsThisShiftMethodIsInActionOrEnded(int id)
        {
            bool      status     = true;
            DataTable dataHolder = new DataTable();

            string       CorrectedDate = null;
            tbldaytiming StartTime     = db.tbldaytimings.Where(m => m.IsDeleted == 0).SingleOrDefault();
            TimeSpan     Start         = StartTime.StartTime;

            if (Start <= DateTime.Now.TimeOfDay)
            {
                CorrectedDate = DateTime.Now.ToString("yyyy-MM-dd");
            }
            else
            {
                CorrectedDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            }

            MsqlConnection mc = new MsqlConnection();

            mc.open();
            String           sql = "SELECT * FROM tblShiftPlanner WHERE (( StartDate <='" + CorrectedDate + "' AND EndDate >='" + CorrectedDate + "') OR ( EndDate <'" + CorrectedDate + "' )) AND ShiftMethodID = " + id + " ORDER BY ShiftPlannerID ASC";
            MySqlDataAdapter da  = new MySqlDataAdapter(sql, mc.msqlConnection);

            da.Fill(dataHolder);
            mc.close();

            if (dataHolder.Rows.Count == 0)
            {
                status = false;
            }
            return(status);
        }
        bool DoesThisMachineBelongToThisFactor(string FactorName, int FactorID, int machineID)
        {
            bool   tick = false;
            String sql  = null;

            if (FactorName == "Plant")
            {
                sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND PlantID = " + FactorID;
            }
            else if (FactorName == "Shop")
            {
                sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND ShopID = " + FactorID;
            }
            else if (FactorName == "Cell")
            {
                sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND CellID = " + FactorID;
            }
            else if (FactorName == "Machine")
            {
                sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND MachineID = " + FactorID;
            }
            MsqlConnection mc = new MsqlConnection();

            mc.open();
            DataTable        dataHolder = new DataTable();
            MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);

            da.Fill(dataHolder);
            mc.close();
            if (dataHolder.Rows.Count > 0)
            {
                tick = true;
            }
            return(tick);
        }
Example #3
0
        public JsonResult FetchMenuStyles()
        {
            List <DropdownClass> dropdownList = new List <DropdownClass>();

            DataTable dt = new DataTable();

            try
            {
                using (MsqlConnection mc = new MsqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand("GetMenuStyles", mc.msqlConnection))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            mc.open();
                            cmd.CommandType = CommandType.StoredProcedure;
                            sda.Fill(dt);
                            mc.close();
                        }
                    }
                }
            }
            catch (Exception ex) { }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DropdownClass dropdownClass = new DropdownClass();

                dropdownClass.Value = Convert.ToInt32(dt.Rows[i][0]);
                dropdownClass.Text  = dt.Rows[i][1].ToString();

                dropdownList.Add(dropdownClass);
            }
            return(Json(dropdownList, JsonRequestBehavior.AllowGet));
        }
        //not using
        protected string GetShiftForMachine_Date(int machineId, DateTime datetimevariable)
        {
            string shift      = null;
            string datestring = datetimevariable.Date.ToString("yyyy-MM-dd");
            string timestring = datetimevariable.Hour + " : " + datetimevariable.Minute + ":" + datetimevariable.Second;

            //1. Get shiftMethod from shiftPlanner for that date & machine.
            //2. Get Factor and Based on that find if any of them had a plan .

            String         sql = "SELECT * FROM tblshiftplanner WHERE   StartDate <='" + datestring + "' AND EndTime >= '" + datestring + "' ";
            MsqlConnection mc  = new MsqlConnection();

            mc.open();
            DataTable        dataHolder = new DataTable();
            MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);

            da.Fill(dataHolder);
            mc.close();

            string factor   = null;
            int    factorID = 0;

            for (int i = 0; i < dataHolder.Rows.Count; i++)
            {
                string ShiftMethodName = null;
                int    FactorID = 0, ShiftMethodsID = 0;
                bool   tick = false;
                if (!DBNull.Value.Equals(dataHolder.Rows[i][4]))
                {
                    ShiftMethodsID  = Convert.ToInt32(dataHolder.Rows[i][3]);
                    FactorID        = Convert.ToInt32(dataHolder.Rows[i][4]);
                    ShiftMethodName = "Plant";
                    tick            = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
                }
                else if (!DBNull.Value.Equals(dataHolder.Rows[i][5]))
                {
                    ShiftMethodsID  = Convert.ToInt32(dataHolder.Rows[i][3]);
                    FactorID        = Convert.ToInt32(dataHolder.Rows[i][5]);
                    ShiftMethodName = "Shop";
                    tick            = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
                }
                else if (!DBNull.Value.Equals(dataHolder.Rows[i][6]))
                {
                    ShiftMethodsID  = Convert.ToInt32(dataHolder.Rows[i][3]);
                    FactorID        = Convert.ToInt32(dataHolder.Rows[i][6]);
                    ShiftMethodName = "Cell";
                    tick            = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
                }
                else if (!DBNull.Value.Equals(dataHolder.Rows[i][7]))
                {
                    ShiftMethodsID  = Convert.ToInt32(dataHolder.Rows[i][3]);
                    FactorID        = Convert.ToInt32(dataHolder.Rows[i][7]);
                    ShiftMethodName = "Machine";
                    tick            = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
                }
            }

            return(shift);
        }
Example #5
0
        public void CalculateUtilization(int PlantID, int ShopID, int CellID, int MachineID, DateTime FromDate, DateTime Enddate)
        {
            var getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0).ToList();

            if (MachineID != 0)
            {
                getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.MachineID == MachineID).ToList();
            }
            else if (CellID != 0)
            {
                getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.CellID == CellID).ToList();
            }
            else if (ShopID != 0)
            {
                getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.ShopID == ShopID).ToList();
            }

            int            dateDifference = Enddate.Subtract(FromDate).Days;
            MsqlConnection mc             = new MsqlConnection();

            for (int i = 0; i <= dateDifference; i++)
            {
                DateTime QueryDate = FromDate.AddDays(i);

                foreach (var Machine in getMachineList)
                {
                    var GetUtilList = Serverdb.tbl_UtilReport.Where(m => m.MachineID == Machine.MachineID && m.CorrectedDate == QueryDate.Date).ToList();
                    if (GetUtilList.Count == 0)
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].SP_UtilData", mc.msqlConnection))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@Cdate", QueryDate.Date);
                            cmd.Parameters.AddWithValue("@MachineID", Machine.MachineID);
                            mc.open();
                            cmd.ExecuteNonQuery();
                            mc.close();
                        }
                        //Serverdb.Database.
                        //Serverdb.SP_UtilData(QueryDate.Date, Machine.MachineID);
                    }
                }
            }
        }
Example #6
0
        public string GetCheckboxesForUser(int roleId)
        {
            List <UserMenus> menus = new List <UserMenus>();
            string           res   = "";

            DataTable dt = new DataTable();

            try
            {
                using (MsqlConnection mc = new MsqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand("GetCheckboxesForUser", mc.msqlConnection))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            mc.open();
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@roleId", roleId);
                            sda.Fill(dt);
                            mc.close();
                        }
                    }
                }
            }
            catch (Exception ex) { }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserMenus menu = new UserMenus();

                menu.Id       = dt.Rows[i]["MenuName"].ToString();
                menu.MenuId   = Convert.ToInt32(dt.Rows[i]["MenuId"]);
                menu.MenuName = dt.Rows[i]["MenuName"].ToString();

                string status = Convert.ToString(dt.Rows[i]["MenuStatus"]);
                menu.MenuStatus = status;

                menus.Add(menu);
            }

            res = JsonConvert.SerializeObject(menus);

            return(res);
        }
Example #7
0
        public string GetDashboardMenus()
        {
            string res    = "";
            int    roleId = Convert.ToInt32(Session["RoleID"]);

            List <DashboardMenu> dashboardMenus = new List <DashboardMenu>();

            DataTable dt = new DataTable();

            try
            {
                using (MsqlConnection mc = new MsqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand("GetDashboardMenus", mc.msqlConnection))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            mc.open();
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@roleId", roleId);
                            sda.Fill(dt);
                            mc.close();
                        }
                    }
                }
            }
            catch (Exception ex) { }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DashboardMenu dashboardMenu = new DashboardMenu();

                dashboardMenu.MenuName  = dt.Rows[i]["MenuName"].ToString();
                dashboardMenu.MenuURL   = dt.Rows[i]["MenuURL"].ToString();
                dashboardMenu.ImageURL  = dt.Rows[i]["ImageUrl"].ToString();
                dashboardMenu.ColourDiv = dt.Rows[i]["ColourDiv"].ToString();
                dashboardMenu.Style     = dt.Rows[i]["Style"].ToString();

                dashboardMenus.Add(dashboardMenu);
            }
            res = JsonConvert.SerializeObject(dashboardMenus);

            return(res);
        }
Example #8
0
        public ActionResult Index(string PlantID = null, string CDate = null, string ShopID = null, string CellID = null, string WorkCenterID = null)
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }
            ViewBag.Logout = Session["Username"];
            ViewBag.roleid = Session["RoleID"];
            String Username = Session["Username"].ToString();
            //1: get list of all 999 which have duration greater than 120 sec
            //2: Construct new list in form of a class(UnIdentifiedLossCorrection) and send to view .
            string CorrectedDate = DateTime.Now.ToString("yyyy-MM-dd");

            if (DateTime.Now.Date.Hour < 6)
            {
                CorrectedDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            }
            if (!string.IsNullOrEmpty(CDate))
            {
                CorrectedDate = CDate;
            }

            //var noCodeList = db.tbllossofentries.Where(m => m.MessageCodeID == 999 && m.CorrectedDate == CorrectedDate).OrderByDescending(m => m.CorrectedDate).ToList();
            //if (!string.IsNullOrEmpty(WorkCenterID))
            //{
            //    noCodeList = db.tbllossofentries.Where(m => m.MessageCodeID == 999 && m.MachineID == WorkCenterID && m.CorrectedDate == CorrectedDate).OrderByDescending(m => m.CorrectedDate).ToList();
            //}

            int    plantId = 0, shopId = 0, cellId = 0, wcId = 0;
            string Header = null, query = null;

            if (string.IsNullOrEmpty(WorkCenterID))
            {
                if (string.IsNullOrEmpty(CellID))
                {
                    if (string.IsNullOrEmpty(ShopID))
                    {
                        if (string.IsNullOrEmpty(PlantID))
                        {//donothing
                        }
                        else
                        {
                            plantId = Convert.ToInt32(PlantID);
                            string Macs      = null;
                            var    SubWCData = db.tblmachinedetails.Where(m => m.PlantID == plantId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
                            foreach (var subMacs in SubWCData)
                            {
                                if (Macs == null)
                                {
                                    Macs = Convert.ToString(subMacs.MachineID);
                                }
                                else
                                {
                                    Macs += "," + Convert.ToString(subMacs.MachineID);
                                }
                            }
                            query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999  order by MachineID";
                        }
                    }
                    else
                    {
                        shopId = Convert.ToInt32(ShopID);
                        string Macs      = null;
                        var    SubWCData = db.tblmachinedetails.Where(m => m.ShopID == shopId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
                        foreach (var subMacs in SubWCData)
                        {
                            if (Macs == null)
                            {
                                Macs = Convert.ToString(subMacs.MachineID);
                            }
                            else
                            {
                                Macs += "," + Convert.ToString(subMacs.MachineID);
                            }
                        }
                        query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999   order by MachineID";
                    }
                }
                else
                {
                    cellId = Convert.ToInt32(CellID);
                    string Macs      = null;
                    var    SubWCData = db.tblmachinedetails.Where(m => m.CellID == cellId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
                    foreach (var subMacs in SubWCData)
                    {
                        if (Macs == null)
                        {
                            Macs = Convert.ToString(subMacs.MachineID);
                        }
                        else
                        {
                            Macs += "," + Convert.ToString(subMacs.MachineID);
                        }
                    }
                    query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999   order by MachineID";
                }
            }
            else
            {
                wcId  = Convert.ToInt32(WorkCenterID);
                query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + wcId + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999 ";
            }

            DataTable LossCodesData = new DataTable();

            if (!string.IsNullOrEmpty(PlantID))
            {
                using (MsqlConnection mcLossCodes = new MsqlConnection())
                {
                    mcLossCodes.open();
                    SqlDataAdapter daLossCodesData = new SqlDataAdapter(query, mcLossCodes.msqlConnection);
                    daLossCodesData.Fill(LossCodesData);
                    mcLossCodes.close();
                }
            }

            List <UnIdentifiedLossCorrection> UnIdentified = new List <UnIdentifiedLossCorrection>();

            for (int i = 0; i < LossCodesData.Rows.Count; i++)
            {
                int    macID      = Convert.ToInt32(LossCodesData.Rows[i][1]);
                int    LossID     = Convert.ToInt32(LossCodesData.Rows[i][0]);
                int    LossCodeID = Convert.ToInt32(LossCodesData.Rows[i][2]);
                string MacName    = Convert.ToString(db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.MachineID == macID).Select(m => m.MachineInvNo).FirstOrDefault());
                UnIdentified.Add(new UnIdentifiedLossCorrection {
                    LossID = LossID, MachineName = MacName, Level1 = LossCodeID, SDateTime = Convert.ToDateTime(LossCodesData.Rows[i][3]), EDateTime = Convert.ToDateTime(LossCodesData.Rows[i][4])
                });
            }

            ViewData["PlantID"]      = new SelectList(db.tblplants.Where(m => m.IsDeleted == 0), "PlantID", "PlantName", PlantID);
            ViewData["ShopID"]       = new SelectList(db.tblshops.Where(m => m.IsDeleted == 0 && m.PlantID == 1), "ShopID", "ShopName", ShopID);
            ViewData["CellID"]       = new SelectList(db.tblcells.Where(m => m.IsDeleted == 0 && m.PlantID == 999), "CellID", "CellName", CellID);
            ViewData["WorkCenterID"] = new SelectList(db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.PlantID == 999), "MachineID", "MachineInvNo", WorkCenterID);

            ViewData["Level1"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 1 && m.MessageType != "Breakdown" && m.MessageType != "PM"), "LossCodeID", "LossCodeDesc", 999);
            ViewData["Level2"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 2 && m.MessageType == "Nothing"), "LossCodeID", "LossCode"); //Just send an empty List
            ViewData["Level3"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 3 && m.MessageType == "Nothing"), "LossCodeID", "LossCode"); //Just send an empty List
            return(View(UnIdentified.ToList()));
        }
Example #9
0
        public ActionResult Index(IList <UnIdentifiedLossCorrection> UnIdentifiedL)
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }
            string MacIDs        = null;
            int    i             = 0;
            string CorrectedDate = null;

            if (UnIdentifiedL != null)
            {
                foreach (var row in UnIdentifiedL)
                {
                    int LossCodeID = 0, LossID = 0;
                    if (row.Level3 != 0)
                    {
                        LossCodeID = Convert.ToInt32(row.Level3);
                        LossID     = Convert.ToInt32(row.LossID);
                    }
                    else if (row.Level2 != 0)
                    {
                        LossCodeID = Convert.ToInt32(row.Level2);
                        LossID     = Convert.ToInt32(row.LossID);
                    }
                    else if (row.Level1 != 0 && row.Level1 != 999)
                    {
                        LossCodeID = Convert.ToInt32(row.Level1);
                        LossID     = Convert.ToInt32(row.LossID);
                    }

                    var LossData = db.tbllossofentries.Find(LossID);
                    if (LossData != null)
                    {
                        //Get MacNumber if it doesn't exist in String(MacIDs)
                        string MacID = Convert.ToString(LossData.MachineID);
                        if (MacIDs == null)
                        {
                            MacIDs += MacID;
                        }
                        else
                        {
                            MacIDs += "," + MacID;
                        }

                        if (i == 0)
                        {
                            CorrectedDate = LossData.CorrectedDate;
                            i++;
                        }

                        LossData.MessageCodeID = LossCodeID;
                        string LossName = Convert.ToString(db.tbllossescodes.Where(m => m.LossCodeID == LossCodeID).Select(m => m.LossCode).FirstOrDefault());
                        LossData.MessageCode     = LossName;
                        LossData.MessageDesc     = LossName;
                        db.Entry(LossData).State = EntityState.Modified;
                        db.SaveChanges();
                    }
                }
            }

            //Delete Data from tblworeport table and tbloeedashboardvariables
            string         StartDate    = Convert.ToString(CorrectedDate);
            MsqlConnection mcDeleteRows = new MsqlConnection();

            try
            {
                try
                {
                    DataTable LossCodesData = new DataTable();
                    string    query         = @"SELECT Distinct(HMIID) FROM i_facility_taml.dbo.tblworeport WHERE MachineID IN ( " + MacIDs + ") and CorrectedDate = '" + CorrectedDate + "';";
                    using (MsqlConnection mcLossCodes = new MsqlConnection())
                    {
                        mcLossCodes.open();
                        SqlDataAdapter daLossCodesData = new SqlDataAdapter(query, mcLossCodes.msqlConnection);
                        daLossCodesData.Fill(LossCodesData);
                        mcLossCodes.close();
                    }

                    mcDeleteRows.open();
                    SqlCommand cmdDeleteWOData = new SqlCommand("DELETE FROM i_facility_taml.dbo.tblworeport WHERE MachineID IN ( " + MacIDs + ") and CorrectedDate = '" + CorrectedDate + "';", mcDeleteRows.msqlConnection);
                    cmdDeleteWOData.ExecuteNonQuery();

                    for (int l = 0; l < LossCodesData.Rows.Count; l++)
                    {
                        SqlCommand cmdDeleteWOLosses = new SqlCommand("DELETE FROM i_facility_taml.dbo.tblwolossess WHERE HMIID = '" + LossCodesData.Rows[l][0] + "' ", mcDeleteRows.msqlConnection);
                        cmdDeleteWOLosses.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                }

                try
                {
                    SqlCommand cmdDeleteOEEData = new SqlCommand("DELETE FROM i_facility_taml.dbo.tbloeedashboardvariables WHERE WCID IN ( " + MacIDs + ") and StartDate = '" + StartDate + "';", mcDeleteRows.msqlConnection);
                    cmdDeleteOEEData.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                }
            }
            catch (Exception e)
            {
            }
            finally
            {
                mcDeleteRows.close();
            }

            //Insert New Data
            try
            {
                CalOEE_WODetails cwod = new CalOEE_WODetails();
                cwod.CalculateOEEForYesterday(Convert.ToDateTime(CorrectedDate), Convert.ToDateTime(CorrectedDate));
                cwod.CalWODataForYesterday(Convert.ToDateTime(CorrectedDate), Convert.ToDateTime(CorrectedDate));
            }
            catch (Exception e)
            {
            }
            return(RedirectToAction("Index"));
        }
        public ActionResult Index()
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }

            Session["colordata"] = null;
            ViewBag.Logout       = Session["Username"];
            ViewBag.roleid       = Session["RoleID"];

            //calculating Corrected Date
            TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
            TimeSpan RealCurrntHour  = System.DateTime.Now.TimeOfDay;
            string   CorrectedDate   = DateTime.Now.Date.ToString("yyyy-MM-dd");

            if (RealCurrntHour < currentHourMint)
            {
                CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
            }

            // getting all machine details and their count.
            var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
            int mc      = macData.Count();

            ViewBag.macCount = mc;

            int[] macid       = new int[mc];
            int   macidlooper = 0;

            foreach (var v in macData)
            {
                macid[macidlooper++] = v.MachineID;
            }
            Session["macid"] = macid;
            ViewBag.macCount = mc;

            int[,] maindata = new int[mc, 5];
            //int[,] maindata = new int[mc, 6];
            // write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.

            using (MsqlConnection mc1 = new MsqlConnection())
            {
                mc1.open();
                //SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                //SqlCommand cmd1 = " SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '2019-06-21'and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID",
                //SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(convert (int,MachineOffTime) )as op,sum(convert (int,OperatingTime))as o,sum(convert (int,IdleTime)) as it,sum(convert (int,BreakdownTime))as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "' and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);

                SqlCommand cmd1 = new SqlCommand("SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '" + CorrectedDate + "' and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);

                SqlDataReader datareader      = cmd1.ExecuteReader();
                int           maindatalooper1 = 0;

                while (datareader.Read())
                {
                    int maindatalooper2 = 0;
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
                    maindatalooper1++;
                }
                mc1.close();
            }
            Session["colordata"] = maindata;
            //var tblMainDT = db.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
            //return View(tblMainDT.ToList());

            //Get Modes for All Machines for Today
            //List<tbllivemodedb> tblModeDT = db.tblmodes.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();

            List <tbllivemodedb> tblModeDT     = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
            List <tbllivemodedb> tblModeDTCurr = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();

            //Get Latest Mode for each machine and Update the DurationInSec Column
            List <tbllivemodedb> CurrentModesOfAllMachines = (from row in tblModeDT
                                                              where row.IsCompleted == 0
                                                              select row).ToList();
            int PrvMachineID = 0;

            foreach (var row in tblModeDTCurr)
            {
                // DateTime startDateTime = Convert.ToDateTime( row.StartTime);
                // int DurInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
                // //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
                // int ModeID = row.ModeID;
                //foreach ( var tom in tblModeDT.Where(w => w.ModeID == ModeID)) {
                //             tom.DurationInSec = DurInSec;
                //         }

                if (PrvMachineID != row.MachineID)
                {
                    DateTime startDateTime = Convert.ToDateTime(row.StartTime);
                    int      DurInSec      = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
                    //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
                    int ModeID = row.ModeID;
                    row.DurationInSec = DurInSec;
                    tblModeDT.Add(row);
                    //foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
                    //{

                    //}
                    PrvMachineID = row.MachineID;
                }
            }
            List <DBMode> ShowMode = new List <DBMode>();

            //Update DurationInSec to Minutes
            foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
            {
                DBMode ShowModeItem = new DBMode();
                ShowModeItem.ColorCode        = MainRow.ColorCode;
                ShowModeItem.CorrectedDate    = MainRow.CorrectedDate;
                ShowModeItem.DurationInSec    = MainRow.DurationInSec / 60.00;
                ShowModeItem.EndTime          = MainRow.EndTime;
                ShowModeItem.InsertedBy       = MainRow.InsertedBy;
                ShowModeItem.InsertedOn       = MainRow.InsertedOn;
                ShowModeItem.IsCompleted      = MainRow.IsCompleted;
                ShowModeItem.IsDeleted        = MainRow.IsDeleted;
                ShowModeItem.MachineID        = MainRow.MachineID;
                ShowModeItem.Mode             = MainRow.Mode;
                ShowModeItem.ModeID           = MainRow.ModeID;
                ShowModeItem.ModifiedBy       = MainRow.ModifiedBy;
                ShowModeItem.ModifiedOn       = MainRow.ModifiedOn;
                ShowModeItem.StartTime        = MainRow.StartTime;
                ShowModeItem.tblmachinedetail = MainRow.tblmachinedetail;
                ShowMode.Add(ShowModeItem);
                MainRow.DurationInSec = Convert.ToInt32(MainRow.DurationInSec / 60);
            }
            ;

            List <string> ShopNames = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.ShopNo).Distinct().ToList();

            ViewBag.DistinctShops = ShopNames;

            //return View(tblModeDT);
            return(View(ShowMode));
        }
Example #11
0
        public ActionResult Index()
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }

            Session["CellId"]    = 1;
            Session["colordata"] = null;
            ViewBag.Logout       = Session["Username"];
            ViewBag.roleid       = Session["RoleID"];

            //calculating Corrected Date
            //TimeSpan currentHourMint = new TimeSpan(06, 59, 59);
            TimeSpan currentHourMint  = new TimeSpan(05, 59, 59);
            TimeSpan RealCurrntHour   = System.DateTime.Now.TimeOfDay;
            string   CorrectedDate    = DateTime.Now.Date.ToString("yyyy-MM-dd");
            DateTime correctedDate    = DateTime.Now.Date;
            string   PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");

            if (RealCurrntHour < currentHourMint)
            {
                CorrectedDate    = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
                correctedDate    = DateTime.Now.AddDays(-1).Date;
                PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
            }
            //correctedDate = Convert.ToDateTime("2019-05-21");
            //CorrectedDate = "2019-05-21";
            //PrvCorrectedDate = "2019-05-20";
            // getting all machine details and their count.
            var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
            int mc      = macData.Count();

            ViewBag.macCount = mc;

            int[] macid       = new int[mc];
            int   macidlooper = 0;

            foreach (var v in macData)
            {
                macid[macidlooper++] = v.MachineID;
            }
            Session["macid"] = macid;
            ViewBag.macCount = mc;

            int[,] maindata = new int[mc, 16];
            //int[,] maindata = new int[mc, 6];
            // write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.

            using (MsqlConnection mc1 = new MsqlConnection())
            {
                mc1.open();
                SqlCommand    cmd1            = new SqlCommand("SELECT MachineID,sum(OperatingTime)as ot,sum(Delay) as dl,sum(Alarm)as al,sum(MDI) as mdi,sum(JOG) as jog,sum(HND) as hnd,sum(EDIT) as edit,sum(RTN) as rtn,sum(ETC) as etc,sum(PowerOff) as po FROM " + databaseName + ".tblworkmodemimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                SqlDataReader datareader      = cmd1.ExecuteReader();
                int           maindatalooper1 = 0;

                while (datareader.Read())
                {
                    int maindatalooper2 = 0;
                    int OperatingTime   = datareader.GetInt32(1);
                    int Delay           = datareader.GetInt32(2);
                    int Alarm           = datareader.GetInt32(3);
                    int MDI             = datareader.GetInt32(4);
                    int JOG             = datareader.GetInt32(5);
                    int HND             = datareader.GetInt32(6);
                    int EDIT            = datareader.GetInt32(7);
                    int RTN             = datareader.GetInt32(8);
                    int ETC             = datareader.GetInt32(9);
                    int PowerOff        = datareader.GetInt32(10);
                    int TotalTime       = OperatingTime + Delay + Alarm + MDI + JOG + HND + EDIT + RTN + ETC + PowerOff;
                    if (TotalTime == 0)
                    {
                        TotalTime = 1;
                    }
                    int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OperatingTime) / Convert.ToDouble(TotalTime)) * 100));
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(5);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(6);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(7);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(8);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(9);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(10);
                    maindata[maindatalooper1, maindatalooper2++] = UtilPer;
                    maindata[maindatalooper1, maindatalooper2++] = TotalTime;
                    maindatalooper1++;
                }
                datareader.Close();
                SqlCommand    Prvcmd1            = new SqlCommand("SELECT MachineID,sum(OperatingTime)as ot,sum(Delay) as dl,sum(Alarm)as al,sum(MDI) as mdi,sum(JOG) as jog,sum(HND) as hnd,sum(EDIT) as edit,sum(RTN) as rtn,sum(ETC) as etc,sum(PowerOff) as po FROM " + databaseName + ".tblworkmodemimics where CorrectedDate='" + PrvCorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                SqlDataReader Prvdatareader      = Prvcmd1.ExecuteReader();
                int           Prvmaindatalooper1 = 0;
                while (Prvdatareader.Read())
                {
                    int OperatingTime = Prvdatareader.GetInt32(1);
                    int Delay         = Prvdatareader.GetInt32(2);
                    int Alarm         = Prvdatareader.GetInt32(3);
                    int MDI           = Prvdatareader.GetInt32(4);
                    int JOG           = Prvdatareader.GetInt32(5);
                    int HND           = Prvdatareader.GetInt32(6);
                    int EDIT          = Prvdatareader.GetInt32(7);
                    int RTN           = Prvdatareader.GetInt32(8);
                    int ETC           = Prvdatareader.GetInt32(9);
                    int PowerOff      = Prvdatareader.GetInt32(10);
                    int TotalTime     = OperatingTime + Delay + Alarm + MDI + JOG + HND + EDIT + RTN + ETC + PowerOff;
                    if (TotalTime == 0)
                    {
                        TotalTime = 1;
                    }
                    int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OperatingTime) / Convert.ToDouble(TotalTime)) * 100));
                    maindata[Prvmaindatalooper1, 13] = UtilPer;
                    maindata[Prvmaindatalooper1, 14] = OperatingTime;
                    maindata[Prvmaindatalooper1, 15] = TotalTime;
                    Prvmaindatalooper1++;
                }
                Prvdatareader.Close();
                mc1.close();
            }
            Session["colordata"] = maindata;

            //Get Modes for All Machines for Today
            List <tbllivemode> tblModeDT     = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1 && m.IsDeleted == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
            List <tbllivemode> tblModeDTCurr = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0 && m.IsDeleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
            //Get Latest Mode for each machine and Update the DurationInSec Column
            List <tbllivemode> CurrentModesOfAllMachines = (from row in tblModeDT
                                                            where row.IsCompleted == 0
                                                            orderby row.ModeID descending
                                                            select row).ToList().OrderByDescending(m => m.ModeID).ToList();
            int PrvMachineID = 0;

            foreach (var row in tblModeDTCurr)
            {
                if (PrvMachineID != row.MachineID)
                {
                    DateTime startDateTime = Convert.ToDateTime(row.StartTime);
                    int      DurInSec      = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
                    //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
                    int ModeID = row.ModeID;
                    row.DurationInSec = DurInSec;
                    tblModeDT.Add(row);
                    //foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
                    //{

                    //}
                    PrvMachineID = row.MachineID;
                }
                if (row.ModeType == "SETUP")
                {
                    DateTime StartTime = Convert.ToDateTime(row.StartTime);
                    DateTime EndTime   = DateTime.Now;
                    try
                    {
                        EndTime = Convert.ToDateTime(row.LossCodeEnteredTime);
                    }
                    catch { }
                    int DurInSec = Convert.ToInt32(EndTime.Subtract(StartTime).TotalSeconds);
                    int ModeID   = row.ModeID;
                    row.DurationInSec = DurInSec;
                    tblModeDT.Add(row);
                }
            }
            //Update DurationInSec to Minutes
            foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
            {
                int GetDur = (int)MainRow.DurationInSec / 60;
                if (MainRow.ModeType == "SETUP")
                {
                    GetDur = (int)Convert.ToDateTime(MainRow.LossCodeEnteredTime).Subtract(Convert.ToDateTime(MainRow.StartTime)).TotalSeconds / 60;
                }
                if (GetDur < 1)
                {
                    // if the duration is sec is greatethan the 50 then consider as 1 minutes
                    int LessThan1min = (int)MainRow.DurationInSec;
                    if (LessThan1min > 50)
                    {
                        GetDur = 1;
                    }
                    else
                    {
                        GetDur = 0;
                    }
                }
                MainRow.DurationInSec = GetDur;
            }
            ;
            List <string> ShopNames = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.tblcell.CelldisplayName).Distinct().ToList();

            ViewBag.DistinctShops = ShopNames;
            return(View(tblModeDT.OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList()));
        }
Example #12
0
        public ActionResult ImportMachineDetails(HttpPostedFileBase file)
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }

            ////start logging
            //String Username = Session["Username"].ToString();
            //int UserID = Convert.ToInt32(Session["UserId"]);
            //string CompleteModificationdetail = "Import PriorityAlarm";
            //Action = "ImportPriorityAlarm";
            //ActiveLogStorage Obj = new ActiveLogStorage();
            //Obj.SaveActiveLog(Action, Controller, Username, UserID, CompleteModificationdetail);
            ////End

            //Deleting Excel file
            #region
            string        fileLocation1 = Server.MapPath("~/Content/");
            DirectoryInfo di            = new DirectoryInfo(fileLocation1);
            FileInfo[]    files         = di.GetFiles("*.xlsx").Where(p => p.Extension == ".xlsx").ToArray();
            foreach (FileInfo file1 in files)
            {
                try
                {
                    file1.Attributes = FileAttributes.Normal;
                    System.IO.File.Delete(file1.FullName);
                }
                catch { }
            }
            #endregion

            DataSet ds = new DataSet();
            if (Request.Files["file"].ContentLength > 0)
            {
                string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["file"].SaveAs(fileLocation);
                    string excelConnectionString = string.Empty;
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                            fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        //excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        //excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=Excel 8.0;HDR=YES;";

                        //excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        //"Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"" ;

                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                                                fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                    }
                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();
                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return(null);
                    }
                    String[] excelSheets = new String[dt.Rows.Count];
                    int      t           = 0;
                    //excel data saves in temp file here.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    string          query            = string.Format("Select * from [{0}]", excelSheets[0]);
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                    excelConnection1.Close();
                    excelConnection.Close();
                }
                if (fileExtension.ToString().ToLower().Equals(".xml"))
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["FileUpload"].SaveAs(fileLocation);
                    XmlTextReader xmlreader = new XmlTextReader(fileLocation);
                    // DataSet ds = new DataSet();
                    ds.ReadXml(xmlreader);
                    xmlreader.Close();
                }

                string Errors = null;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string a = ds.Tables[0].Rows[i][0].ToString();

                    MsqlConnection mc1 = new MsqlConnection();
                    mc1.open();
                    string dat = DateTime.Now.ToString();
                    dat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    SqlCommand cmd2 = null;
                    try
                    {
                        //cmd2 = new SqlCommand("INSERT INTO tblmachinedetails(InsertedBy,InsertedOn," +
                        //    "IsDeleted, MachineInvNo, IPAddress, ControllerType,MachineModel,MachineMake,ModelType" +
                        //",MachineDispName,IsParameters,ShopNo,IsPCB) VALUES" +
                        //    "(" + Convert.ToInt32(Session["UserId"]) + ",'" + dat + "',0,'" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" +
                        //    "" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "','" +
                        //"" + ds.Tables[0].Rows[i][5].ToString() + "','" + ds.Tables[0].Rows[i][6].ToString() + "','" + ds.Tables[0].Rows[i][7].ToString() + "'," +
                        //"" + Convert.ToInt32(ds.Tables[0].Rows[i][8]) + ",'" + ds.Tables[0].Rows[i][9].ToString() + "')", mc1.msqlConnection);

                        cmd2 = new SqlCommand("INSERT INTO tblmachinedetails (InsertedBy,InsertedOn,IsDeleted,MachineType, MachineInvNo, IPAddress, ControllerType,MachineModel,MachineMake,ModelType,MachineDispName,IsParameters,ShopNo,IsPCB) VALUES( '" + Convert.ToInt32(Session["UserId"]) + "','" + dat + "'," + 0 + "," + 2 + ",'" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "','" + ds.Tables[0].Rows[i][5].ToString() + "','" + ds.Tables[0].Rows[i][6].ToString() + "','" + ds.Tables[0].Rows[i][7].ToString() + "','" + ds.Tables[0].Rows[i][8].ToString() + "','" + ds.Tables[0].Rows[i][9].ToString() + "')", mc1.msqlConnection);

                        cmd2.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        Errors = e.ToString();
                    }

                    mc1.close();
                }
                Session["MachErrDetails"] = Errors;
            }


            return(RedirectToAction("Index"));
        }
        public string GetShiftForMachine(int machineID, DateTime datetimevariable)
        {
            string shift      = null;
            string datestring = Convert.ToDateTime(datetimevariable).ToString("yyyy-MM-dd");
            string timestring = Convert.ToDateTime(datetimevariable).Hour + " : " + Convert.ToDateTime(datetimevariable).Minute + ":" + Convert.ToDateTime(datetimevariable).Second;

            //shift for today or any other day.(Even for future if plan is set).
            if (datestring == DateTime.Now.Date.ToString("yyyy-MM-dd"))
            {
                string         datetimevariablestring = Convert.ToDateTime(datetimevariable).ToString("yyyy-MM-dd HH:mm:ss");
                String         sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID + " AND StartTime <='" + datetimevariablestring + "' AND EndTime >= '" + datetimevariablestring + "' ORDER BY StartTime ASC";
                MsqlConnection mc  = new MsqlConnection();
                mc.open();
                DataTable        dataHolder = new DataTable();
                MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);
                da.Fill(dataHolder);
                mc.close();
                if (dataHolder.Rows.Count != 0)
                {
                    shift = dataHolder.Rows[0][2].ToString();
                }
            }
            else
            {
                //1. Get shiftMethod from shiftPlanner for that date & machine.
                //2. Get Factor and Based on that find if any of them had a plan .

                String         sql = "SELECT * FROM tblshiftplanner WHERE   StartDate <='" + datestring + "' AND EndTime >= '" + datestring + "' ";
                MsqlConnection mc  = new MsqlConnection();
                mc.open();
                DataTable        dataHolder = new DataTable();
                MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);
                da.Fill(dataHolder);
                mc.close();

                int ShiftMethodsID = 0;
                for (int i = 0; i < dataHolder.Rows.Count; i++)
                {
                    string FactorName = null;
                    int    FactorID   = 0;
                    bool   tick       = false;
                    if (!DBNull.Value.Equals(dataHolder.Rows[i][4]))
                    {
                        ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
                        FactorID       = Convert.ToInt32(dataHolder.Rows[i][4]);
                        FactorName     = "Plant";
                        tick           = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
                    }
                    else if (!DBNull.Value.Equals(dataHolder.Rows[i][5]))
                    {
                        ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
                        FactorID       = Convert.ToInt32(dataHolder.Rows[i][5]);
                        FactorName     = "Shop";
                        tick           = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
                    }
                    else if (!DBNull.Value.Equals(dataHolder.Rows[i][6]))
                    {
                        ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
                        FactorID       = Convert.ToInt32(dataHolder.Rows[i][6]);
                        FactorName     = "Cell";
                        tick           = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
                    }
                    else if (!DBNull.Value.Equals(dataHolder.Rows[i][7]))
                    {
                        ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
                        FactorID       = Convert.ToInt32(dataHolder.Rows[i][7]);
                        FactorName     = "Machine";
                        tick           = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
                    }
                    if (tick)
                    {
                        break;
                    }
                }

                //now get shift based on shiftmethod and time from tblshiftdetails

                String         sql1 = "SELECT * FROM tblshiftdetails WHERE ShiftMethodID = " + ShiftMethodsID + "AND ShiftStartTime <='" + timestring + "' AND ShiftEndTime >= '" + timestring + "' ORDER BY ShiftStartTime ASC";
                MsqlConnection mc1  = new MsqlConnection();
                mc1.open();
                DataTable        dataHolder1 = new DataTable();
                MySqlDataAdapter da1         = new MySqlDataAdapter(sql1, mc1.msqlConnection);
                da1.Fill(dataHolder1);
                mc1.close();
                if (dataHolder1.Rows.Count > 0)
                {
                    shift = dataHolder1.Rows[0][1].ToString();
                }
            }

            return(shift);
        }
Example #14
0
        //public ActionResult Index(string CorrectedDate, string PrvCorrectedDate)
        //{
        //    if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
        //    {
        //        return RedirectToAction("Login", "Login", null);
        //    }

        //    Session["colordata"] = null;
        //    ViewBag.Logout = Session["Username"];
        //    ViewBag.roleid = Session["RoleID"];
        //    ViewBag.StartDate = CorrectedDate;
        //    DateTime correcteDate = Convert.ToDateTime(CorrectedDate);
        //    calculating Corrected Date
        //    TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
        //    TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
        //    string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
        //    if (RealCurrntHour < currentHourMint)
        //    {
        //        CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
        //    }

        //    getting all machine details and their count.
        //   var macData = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
        //    int mc = macData.Count();
        //    ViewBag.macCount = mc;

        //    int[] macid = new int[mc];
        //    int macidlooper = 0;
        //    foreach (var v in macData)
        //    {
        //        macid[macidlooper++] = v.MachineID;
        //    }
        //    Session["macid"] = macid;
        //    ViewBag.macCount = mc;

        //    int[,] maindata = new int[mc, 5];
        //    int[,] maindata = new int[mc, 6];
        //    write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.

        //    using (MsqlConnection mc1 = new MsqlConnection())
        //    {
        //        mc1.open();
        //        SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
        //        SqlCommand cmd1 = " SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '2019-06-21'and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID",
        //        SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(convert (int,MachineOffTime) )as op,sum(convert (int,OperatingTime))as o,sum(convert (int,IdleTime)) as it,sum(convert (int,BreakdownTime))as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "' and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);

        //        SqlCommand cmd1 = new SqlCommand("SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM [i_facility_WeirMinerals].[dbo].tblmimics where CorrectedDate = '" + CorrectedDate + "' and MachineID IN(select distinct(MachineID) from [i_facility_WeirMinerals].[dbo].tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);

        //        SqlDataReader datareader = cmd1.ExecuteReader();
        //        int maindatalooper1 = 0;

        //        while (datareader.Read())
        //        {
        //            int maindatalooper2 = 0;
        //            maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
        //            maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
        //            maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
        //            maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
        //            maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
        //            maindatalooper1++;
        //        }
        //        mc1.close();
        //    }
        //    Session["colordata"] = maindata;
        //    var tblMainDT = Serverdb.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
        //    return View(tblMainDT.ToList());

        //    Get Modes for All Machines for Today
        //    List < tbllivemodedb > tblModeDT = Serverdb.tblmodes.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();

        //    List < tblmode > tblModeDT = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
        //    List<tblmode> tblModeDTCurr = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();

        //    Get Latest Mode for each machine and Update the DurationInSec Column
        //    List < tblmode > CurrentModesOfAllMachines = (from row in tblModeDT
        //                                                  where row.IsCompleted == 0
        //                                                  select row).ToList();
        //    int PrvMachineID = 0;
        //    foreach (var row in tblModeDTCurr)
        //    {
        //        DateTime startDateTime = Convert.ToDateTime(row.StartTime);
        //        int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
        //        //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
        //        int ModeID = row.ModeID;
        //        foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
        //        {
        //            tom.DurationInSec = DurInSec;
        //        }

        //        if (PrvMachineID != row.MachineID)
        //        {
        //            DateTime startDateTime = Convert.ToDateTime(row.StartTime);
        //            int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
        //            row.DurationInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
        //            int ModeID = row.ModeID;
        //            row.DurationInSec = DurInSec;
        //            tblModeDT.Add(row);
        //            foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
        //            {

        //            }
        //            PrvMachineID = row.MachineID;
        //        }
        //    }
        //    List<DBMode> ShowMode = new List<DBMode>();
        //    Update DurationInSec to Minutes
        //    foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
        //    {
        //        DBMode ShowModeItem = new DBMode();
        //        ShowModeItem.ColorCode = MainRow.ColorCode;
        //        ShowModeItem.CorrectedDate = Convert.ToString(MainRow.CorrectedDate);
        //        ShowModeItem.DurationInSec = MainRow.DurationInSec / 60.00;
        //        ShowModeItem.EndTime = MainRow.EndTime;
        //        ShowModeItem.InsertedBy = MainRow.InsertedBy;
        //        ShowModeItem.InsertedOn = MainRow.InsertedOn;
        //        ShowModeItem.IsCompleted = MainRow.IsCompleted;
        //        ShowModeItem.IsDeleted = MainRow.IsDeleted;
        //        ShowModeItem.MachineID = MainRow.MachineID;
        //        ShowModeItem.Mode = MainRow.MacMode;
        //        ShowModeItem.ModeID = MainRow.ModeID;
        //        ShowModeItem.ModifiedBy = MainRow.ModifiedBy;
        //        ShowModeItem.ModifiedOn = MainRow.ModifiedOn;
        //        ShowModeItem.StartTime = MainRow.StartTime;
        //        ShowModeItem.tblmachinedetail = MainRow.tblmachinedetail;
        //        ShowMode.Add(ShowModeItem);
        //        MainRow.DurationInSec = Convert.ToInt32(MainRow.DurationInSec / 60);
        //    };

        //    List<string> ShopNames = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.ShopNo).Distinct().ToList();
        //    ViewBag.DistinctShops = ShopNames;

        //    return View(tblModeDT);
        //    return View(ShowMode);
        //}

        public ActionResult Index(string CorrectedDate, string PrvCorrectedDate)
        {
            //if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            //{
            //    return RedirectToAction("Login", "Login", null);
            //}

            Session["CellId"]    = 1;
            Session["colordata"] = null;
            ViewBag.Logout       = Session["Username"];
            ViewBag.roleid       = Session["RoleID"];

            DateTime correcteDate = Convert.ToDateTime(CorrectedDate);
            //    //calculating Corrected Date
            //    TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
            //    TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
            //    //string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            //    //if (RealCurrntHour < currentHourMint)
            //    //{
            //    //    CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
            //    //}

            //calculating Corrected Date
            TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
            TimeSpan RealCurrntHour  = System.DateTime.Now.TimeOfDay;
            //string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            //DateTime correctedDate = DateTime.Now.Date;
            //string PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
            //if (RealCurrntHour < currentHourMint)
            //{
            //    CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
            //    correctedDate = DateTime.Now.AddDays(-1).Date;
            //    PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
            //}

            // getting all machine details and their count.
            var macData = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
            int mc      = macData.Count();

            ViewBag.macCount = mc;

            //int[] macid = new int[mc];
            //int macidlooper = 0;
            //foreach (var v in macData)
            //{
            //    macid[macidlooper++] = v.MachineID;
            //}
            //Session["macid"] = macid;
            //ViewBag.macCount = mc;

            //int[,] maindata = new int[mc, 10];
            //int[,] maindata = new int[mc, 6];
            // write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
            List <Models.MachineStatus> objmachstatUpdate = new List <Models.MachineStatus>();

            using (MsqlConnection mc1 = new MsqlConnection())
            {
                mc1.open();
                SqlCommand    cmd1       = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM " + databaseName + ".tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                SqlDataReader datareader = cmd1.ExecuteReader();
                // int maindatalooper1 = 0;
                List <Models.MachineStatus> objmachstat = new List <Models.MachineStatus>();

                while (datareader.Read())
                {
                    MachineStatus macstatus = new MachineStatus();
                    //int maindatalooper2 = 0;
                    int MachineOffTime = datareader.GetInt32(1);
                    int OpertTime      = datareader.GetInt32(2);
                    int IdleTime       = datareader.GetInt32(3);
                    int BDTime         = datareader.GetInt32(4);
                    int TotalTime      = MachineOffTime + OpertTime + IdleTime + BDTime;
                    if (TotalTime == 0)
                    {
                        TotalTime = 1;
                    }
                    int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OpertTime) / Convert.ToDouble(TotalTime)) * 100));
                    macstatus.MachineID = datareader.GetInt32(0);
                    var cellname   = Serverdb.tblmachinedetails.Where(m => m.MachineID == macstatus.MachineID).Select(m => m.tblcell.CelldisplayName).FirstOrDefault();
                    var machinedet = Serverdb.tblmachinedetails.Where(m => m.MachineID == macstatus.MachineID).FirstOrDefault();
                    macstatus.CellName       = cellname;
                    macstatus.MachineOffTime = datareader.GetInt32(1);
                    macstatus.OperatingTime  = datareader.GetInt32(2);
                    macstatus.IdleTime       = datareader.GetInt32(3);
                    macstatus.BreakdownTime  = datareader.GetInt32(4);;
                    macstatus.Utilization    = UtilPer;
                    macstatus.TotalTime      = TotalTime;
                    macstatus.machinedet     = machinedet;
                    //maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
                    //maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
                    //maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
                    //maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
                    //maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
                    //maindata[maindatalooper1, maindatalooper2++] = UtilPer;
                    //maindata[maindatalooper1, maindatalooper2++] = TotalTime;
                    //maindatalooper1++;
                    objmachstat.Add(macstatus);
                }
                datareader.Close();

                SqlCommand    Prvcmd1       = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM " + databaseName + ".tblmimics where CorrectedDate='" + PrvCorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                SqlDataReader Prvdatareader = Prvcmd1.ExecuteReader();
                //  int Prvmaindatalooper1 = 0;
                while (Prvdatareader.Read())
                {
                    int MachineOffTime = Prvdatareader.GetInt32(1);
                    int OpertTime      = Prvdatareader.GetInt32(2);
                    int IdleTime       = Prvdatareader.GetInt32(3);
                    int BDTime         = Prvdatareader.GetInt32(4);
                    int TotalTime      = MachineOffTime + OpertTime + IdleTime + BDTime;
                    if (TotalTime == 0)
                    {
                        TotalTime = 1;
                    }
                    int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OpertTime) / Convert.ToDouble(TotalTime)) * 100));
                    foreach (var row in objmachstat)
                    {
                        if (row.MachineID == Prvdatareader.GetInt32(0))
                        {
                            row.PrevOperatingTime = OpertTime;
                            row.PrevTotalTime     = TotalTime;
                            row.PrevUtilization   = UtilPer;
                            objmachstatUpdate.Add(row);
                        }
                    }

                    //maindata[Prvmaindatalooper1, 7] = UtilPer;
                    //maindata[Prvmaindatalooper1, 8] = OpertTime;
                    //maindata[Prvmaindatalooper1, 9] = TotalTime;
                    //Prvmaindatalooper1++;
                }
                Prvdatareader.Close();
                mc1.close();
            }
            // Session["colordata"] = maindata;
            objmachstatUpdate        = objmachstatUpdate.OrderBy(m => m.CellName).ToList();
            Session["MachineStauts"] = objmachstatUpdate.OrderBy(m => m.CellName).ToList();

            //Get Modes for All Machines for Today
            List <tbllivemode> tbllivemodeDT     = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
            List <tbllivemode> tbllivemodeDTCurr = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
            //Get Latest Mode for each machine and Update the DurationInSec Column
            List <tbllivemode> CurrentModesOfAllMachines = (from row in tbllivemodeDT
                                                            where row.IsCompleted == 0
                                                            orderby row.ModeID descending
                                                            select row).ToList().OrderByDescending(m => m.ModeID).ToList();
            int PrvMachineID = 0;

            foreach (var row in tbllivemodeDTCurr)
            {
                if (PrvMachineID != row.MachineID)
                {
                    DateTime startDateTime = Convert.ToDateTime(row.StartTime);
                    int      DurInSec      = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
                    //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
                    int ModeID = row.ModeID;
                    row.DurationInSec = DurInSec;
                    tbllivemodeDT.Add(row);
                    //foreach (var tom in tbllivemodeDT.Where(w => w.ModeID == ModeID))
                    //{

                    //}
                    PrvMachineID = row.MachineID;
                }
                if (row.ModeType == "SETUP")
                {
                    DateTime StartTime = Convert.ToDateTime(row.StartTime);
                    DateTime EndTime   = DateTime.Now;
                    try
                    {
                        EndTime = Convert.ToDateTime(row.LossCodeEnteredTime);
                    }
                    catch { }
                    int DurInSec = Convert.ToInt32(EndTime.Subtract(StartTime).TotalSeconds);
                    int ModeID   = row.ModeID;
                    row.DurationInSec = DurInSec;
                    tbllivemodeDT.Add(row);
                }
            }
            //Update DurationInSec to Minutes
            foreach (var MainRow in tbllivemodeDT.Where(m => m.DurationInSec > 0))
            {
                int GetDur = (int)MainRow.DurationInSec / 60;
                if (MainRow.ModeType == "SETUP")
                {
                    GetDur = (int)Convert.ToDateTime(MainRow.LossCodeEnteredTime).Subtract(Convert.ToDateTime(MainRow.StartTime)).TotalSeconds / 60;
                }
                if (GetDur < 1)
                {
                    GetDur = 0;
                }
                MainRow.DurationInSec = GetDur;
            }
            ;
            List <string> ShopNames = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.tblmachinedetail.tblcell.CelldisplayName).Select(m => m.tblmachinedetail.tblcell.CelldisplayName).Distinct().ToList();

            //  List<int> ShopID = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.tblcell.CellID).Distinct().ToList();
            ViewBag.DistinctShops = ShopNames.OrderBy(m => m).ToList();
            // ViewBag.DistinctShopID = ShopID;

            //List<int> macCountshopwise = new List<int>();
            //foreach(int shop in ShopID)
            //{
            //    int MacCount = Serverdb.tblmachinedetails.Where(m => m.tblcell.CellID == shop && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList().Count;
            //    macCountshopwise.Add(MacCount);
            //}
            // ViewBag.machinecountbyshop = macCountshopwise;
            //ViewBag.machinecountbyshopCount = macCountshopwise.Count;
            return(View(tbllivemodeDT.ToList()));
        }
Example #15
0
        public string GetSideMenubar()
        {
            int roleId = Convert.ToInt32(Session["RoleID"]);

            string res = "";

            List <SideMenubar> sideMenubars = new List <SideMenubar>();

            DataTable dt = new DataTable();

            try
            {
                using (MsqlConnection mc = new MsqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand("GetSideMenubar", mc.msqlConnection))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            mc.open();
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@roleId", roleId);
                            sda.Fill(dt);
                            mc.close();
                        }
                    }
                }
            }
            catch (Exception ex) { }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int menuId = Convert.ToInt32(dt.Rows[i]["MenuId"]);

                SideMenubar sideMenubar = new SideMenubar();

                sideMenubar.MenuName = dt.Rows[i]["MenuName"].ToString();
                sideMenubar.RoleId   = roleId;

                var imageurl = db.sidebar_menus.Where(m => m.MenuName == sideMenubar.MenuName).Select(m => m.ImageURL).FirstOrDefault();
                sideMenubar.ImageURL = imageurl;

                var values = db.sidebar_menus.Where(m => m.SubMenuName != null && m.MenuId == menuId && m.IsDeleted == 0).OrderBy(m => m.id).Select(m => new { m.SubMenuName, m.SubMenuURL }).ToList();

                if (values.Count == 0)
                {
                    //sideMenubar.MenuURL = dt.Rows[i]["MenuURL"].ToString();
                    var menuurl = db.sidebar_menus.Where(m => m.MenuName == sideMenubar.MenuName).Select(m => m.MenuURL).FirstOrDefault();
                    sideMenubar.MenuURL = menuurl;
                }
                List <SubMenus> subMenusList = new List <SubMenus>();
                foreach (var item in values)
                {
                    SubMenus subMenus = new SubMenus();

                    subMenus.SubMenuName = item.SubMenuName;
                    subMenus.SubMenuURL  = item.SubMenuURL;

                    subMenusList.Add(subMenus);
                }

                sideMenubar.SubMenus = subMenusList;
                sideMenubars.Add(sideMenubar);
            }
            res = JsonConvert.SerializeObject(sideMenubars);

            return(res);
        }
Example #16
0
        public ActionResult ImportMailDetails(HttpPostedFileBase file)
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }

            ViewBag.Logout = Session["Username"];
            ViewBag.roleid = Session["RoleID"];
            String Username = Session["Username"].ToString();

            ////start logging
            //String Username = Session["Username"].ToString();
            //int UserID = Convert.ToInt32(Session["UserId"]);
            //string CompleteModificationdetail = "Import PriorityAlarm";
            //Action = "ImportPriorityAlarm";
            //ActiveLogStorage Obj = new ActiveLogStorage();
            //Obj.SaveActiveLog(Action, Controller, Username, UserID, CompleteModificationdetail);
            ////End

            //Deleting Excel file
            #region
            string        fileLocation1 = Server.MapPath("~/Content/");
            DirectoryInfo di            = new DirectoryInfo(fileLocation1);
            FileInfo[]    files         = di.GetFiles("*.xlsx").Where(p => p.Extension == ".xlsx").ToArray();
            foreach (FileInfo file1 in files)
            {
                try
                {
                    file1.Attributes = FileAttributes.Normal;
                    System.IO.File.Delete(file1.FullName);
                }
                catch { }
            }
            #endregion

            DataSet ds = new DataSet();
            if (Request.Files["file"].ContentLength > 0)
            {
                string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["file"].SaveAs(fileLocation);
                    string excelConnectionString = string.Empty;
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                            fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                                                fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();
                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return(null);
                    }
                    String[] excelSheets = new String[dt.Rows.Count];
                    int      t           = 0;
                    //excel data saves in temp file here.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    string          query            = string.Format("Select * from [{0}]", excelSheets[0]);
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                    excelConnection1.Close();
                    excelConnection.Close();
                }
                if (fileExtension.ToString().ToLower().Equals(".xml"))
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["FileUpload"].SaveAs(fileLocation);
                    XmlTextReader xmlreader = new XmlTextReader(fileLocation);
                    // DataSet ds = new DataSet();
                    ds.ReadXml(xmlreader);
                    xmlreader.Close();
                }

                string Errors = null;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Errors = null;
                    string Name      = ds.Tables[0].Rows[i][0].ToString();
                    string EmailID   = ds.Tables[0].Rows[i][1].ToString();
                    string ContactNo = ds.Tables[0].Rows[i][2].ToString();

                    if ((!string.IsNullOrEmpty(Name.Trim())) && (!string.IsNullOrEmpty(EmailID.Trim())) && (!string.IsNullOrEmpty(ContactNo.Trim())))
                    {
                        var doesNameExists = db.tblmailids.Where(m => m.IsDeleted == 0 && m.Name == Name).ToList();
                        if (doesNameExists.Count > 0)
                        {
                            Errors += " Duplicate Name : " + Name + "\n";
                        }
                        else if (Name.Length > 30)
                        {
                            Errors += "  Name : " + Name + "\n";
                        }
                        var doesMailIdExists = db.tblmailids.Where(m => m.EmailID == EmailID).ToList();
                        if (doesMailIdExists.Count > 0)
                        {
                            Errors += " Duplicate Email ID :" + EmailID + "\n";
                        }
                        else if ((!EmailID.EndsWith("@tasl.aero")) || EmailID.Length > 50)
                        {
                            Errors += " Invalid EmailID : " + EmailID + "\n";
                        }
                        var doesCnoExists = db.tblmailids.Where(m => m.IsDeleted == 0 && m.ContactNo == ContactNo).ToList();
                        if (doesCnoExists.Count > 0)
                        {
                            Errors += " Duplicate Contact Number " + ContactNo + "\n";
                        }
                        else if ((ContactNo.Contains("[a-zA-Z]+")) || ContactNo.Length > 10)
                        {
                            Errors += " No Alphabets are Allowed in ContactNo. : " + ContactNo + "\n";
                        }


                        if (Errors == null)
                        {
                            MsqlConnection mc1 = new MsqlConnection();
                            mc1.open();
                            string dat = DateTime.Now.ToString();
                            dat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                            SqlCommand cmd2 = null;
                            try
                            {
                                cmd2 = new SqlCommand("INSERT INTO `i_facility_taml.dbo`.`tblmailids`(`Name`,`EmailID`,`ContactNo`,`IsDeleted`,`CreatedOn`,`CreatedBy`) "
                                                      + " VALUES('" + Name + "' , '" + EmailID + "' ,'" + ContactNo + "',0,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' , '" + Session["UserId"] + "')", mc1.msqlConnection);
                                cmd2.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Errors = e.ToString();
                            }
                            finally
                            {
                                mc1.close();
                            }
                        }
                        else
                        {
                            Session["Error"] += Errors;
                        }
                    }
                }
            }


            return(RedirectToAction("Index"));
        }
Example #17
0
        public ActionResult Index()
        {
            if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
            {
                return(RedirectToAction("Login", "Login", null));
            }

            Session["colordata"] = null;
            ViewBag.Logout       = Session["Username"];
            ViewBag.roleid       = Session["RoleID"];

            //calculating Corrected Date
            TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
            TimeSpan RealCurrntHour  = System.DateTime.Now.TimeOfDay;
            string   CorrectedDate   = DateTime.Now.Date.ToString("yyyy-MM-dd");

            if (RealCurrntHour < currentHourMint)
            {
                CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
            }

            // getting all machine details and their count.
            var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
            int mc      = macData.Count();

            ViewBag.macCount = mc;

            int[] macid       = new int[mc];
            int   macidlooper = 0;

            foreach (var v in macData)
            {
                macid[macidlooper++] = v.MachineID;
            }
            Session["macid"] = macid;
            ViewBag.macCount = mc;

            int[,] maindata = new int[mc, 5];
            //int[,] maindata = new int[mc, 6];
            // write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.

            using (MsqlConnection mc1 = new MsqlConnection())
            {
                mc1.open();
                SqlCommand    cmd1            = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM mazakdaq.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
                SqlDataReader datareader      = cmd1.ExecuteReader();
                int           maindatalooper1 = 0;

                while (datareader.Read())
                {
                    int maindatalooper2 = 0;
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
                    maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
                    maindatalooper1++;
                }
                mc1.close();
            }
            Session["colordata"] = maindata;
            // var tblMainDT = db.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
            var tblMainDT = db.tbllivedailyprodstatus.Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);

            return(View(tblMainDT.ToList()));
        }
        public int GetShiftDuration(int machineID, string shift = null)
        {
            int totalminutes = 0;

            if (shift != null)
            {
                String         sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID + " AND ShiftName = '" + shift + "'";
                MsqlConnection mc  = new MsqlConnection();
                mc.open();
                DataTable        dataHolder = new DataTable();
                MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);
                da.Fill(dataHolder);
                mc.close();
                if (dataHolder.Rows.Count != 0)
                {
                    for (int i = 0; i < dataHolder.Rows.Count; i++)
                    {
                        TimeSpan tss      = (System.TimeSpan)dataHolder.Rows[i][3];
                        TimeSpan tse      = (System.TimeSpan)dataHolder.Rows[i][4];
                        TimeSpan finalTS1 = new TimeSpan(0, 0, 0, 0);
                        int      sHour    = tss.Hours;
                        int      eHour    = tse.Hours;

                        if (eHour < sHour)
                        {
                            finalTS1 = tse.Subtract(tss);
                        }
                        else
                        {
                            finalTS1 = tss.Subtract(tse);
                        }
                        totalminutes += Convert.ToInt32(finalTS1.TotalMinutes);
                    }
                }
            }
            else
            {
                String         sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID;
                MsqlConnection mc  = new MsqlConnection();
                mc.open();
                DataTable        dataHolder = new DataTable();
                MySqlDataAdapter da         = new MySqlDataAdapter(sql, mc.msqlConnection);
                da.Fill(dataHolder);
                mc.close();
                if (dataHolder.Rows.Count != 0)
                {
                    for (int i = 0; i < dataHolder.Rows.Count; i++)
                    {
                        TimeSpan tss      = (System.TimeSpan)dataHolder.Rows[i][3];
                        TimeSpan tse      = (System.TimeSpan)dataHolder.Rows[i][4];
                        TimeSpan finalTS1 = new TimeSpan(0, 0, 0, 0);
                        int      sHour    = tss.Hours;
                        int      eHour    = tse.Hours;

                        if (eHour < sHour)
                        {
                            finalTS1 = tse.Subtract(tss);
                        }
                        else
                        {
                            finalTS1 = tss.Subtract(tse);
                        }
                        totalminutes += Convert.ToInt32(finalTS1.TotalMinutes);
                    }
                }
            }

            return(totalminutes);
        }