Esempio n. 1
0
        public void LastUpdateonUPNwithflag(string upn, string dfe, string adno, string pupil_id)
        {
            try
            {
                String          currentdatetime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                string          sql;
                MySqlConnection MyConn2;
                MySqlRead       Msql = new MySqlRead();
                MyConn2 = new MySqlConnection("datasource=" + Msql.GetServerName() + ";port=3306;username="******";password="******";SslMode=none");
                MyConn2.Open();
                MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", MyConn2); // Setting tiimeout on mysqlServer
                cmd.ExecuteNonQuery();
                if (upn.Length > 0)
                {
                    sql = "update  " + Msql.GetDBName() + "." + "virtual_upn set lastupdated= '" + currentdatetime + "', dfe_number='" + dfe + "', sims_flag='1', pupil_id='" + pupil_id + "'   where upn='" + upn + "';";
                }
                else
                {
                    sql = "update  " + Msql.GetDBName() + "." + "virtual_upn set lastupdated= '" + currentdatetime + "', dfe_number='" + dfe + "', sims_flag='1', pupil_id='" + pupil_id + "'   where adno='" + adno + "';";
                }

                MySqlCommand     MyCommandlst = new MySqlCommand(sql, MyConn2);
                MySqlDataAdapter MyAdapterlst = new MySqlDataAdapter();
                MyAdapterlst.SelectCommand = MyCommandlst;
                MyCommandlst.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteToFile("update Date  " + ex.Message + " " + DateTime.Now);
            }
        }
Esempio n. 2
0
        public bool checkRecordonServer(string pid, string vdateid, MySqlConnection MyConn2)
        {
            string    actualdateid = vdateid;
            string    pupilid      = pid;
            DataTable dtDuplicate;
            MySqlRead Msql = new MySqlRead();

            MyConn2.Open();
            MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", MyConn2); // Setting tiimeout on mysqlServer

            cmd.ExecuteNonQuery();
            string           sql         = "select * from " + Msql.GetDBName() + "." + "virtual_pup_attendance where actual_date_id=" + actualdateid + " and pupil_id=" + pupilid + ";";
            MySqlCommand     MyCommand21 = new MySqlCommand(sql, MyConn2);
            MySqlDataAdapter MyAdapter1  = new MySqlDataAdapter();

            MyAdapter1.SelectCommand = MyCommand21;
            dtDuplicate = new DataTable();
            MyAdapter1.Fill(dtDuplicate);
            MyConn2.Close();

            if (dtDuplicate.Rows.Count > 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 3
0
        public DataTable GetFirstmatchupn()
        {
            Msql          = new MySqlRead();
            MyConnection2 = Msql.connectionString();
            MyConn2       = new MySqlConnection("datasource=" + Msql.GetServerName() + ";port=3306;username="******";password="******";SslMode=none");
            String           Query      = "select a.upn, a.school_id, a.adno from  " + Msql.GetDBName() + "." + "virtual_upn a , " + Msql.GetDBName() + "." + "pupils_unique b where (a.upn =b.upn And  sims_flag IS NULL) OR  (a.adno=b.adno And  sims_flag IS NULL);";
            MySqlCommand     MyCommand2 = new MySqlCommand(Query, MyConn2);
            MySqlDataAdapter MyAdapter  = new MySqlDataAdapter();

            MyAdapter.SelectCommand = MyCommand2;
            DataTable dtUpnmatch = new DataTable();

            MyAdapter.Fill(dtUpnmatch);
            return(dtUpnmatch);
        }
Esempio n. 4
0
        public void updateDFE(string upn, string dfe)
        {
            MySqlConnection MyConn2;
            MySqlRead       Msql = new MySqlRead();

            MyConn2 = new MySqlConnection("datasource=" + Msql.GetServerName() + ";port=3306;username="******";password="******";SslMode=none");
            MyConn2.Open();

            MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", MyConn2); // Setting tiimeout on mysqlServer

            cmd.ExecuteNonQuery();

            string           sql          = "update  " + Msql.GetDBName() + "." + "virtual_upn set dfe_number ='" + dfe + "' where upn='" + upn + "'";
            MySqlCommand     MyCommanddfe = new MySqlCommand(sql, MyConn2);
            MySqlDataAdapter MyAdapterdfe = new MySqlDataAdapter();

            MyAdapterdfe.SelectCommand = MyCommanddfe;
            MyCommanddfe.ExecuteNonQuery();
        }
Esempio n. 5
0
        public int updateintoServer(string pid, string vdateid, MySqlConnection MyConn2, Boolean am, Boolean pm, string AM, string PM)
        {
            try
            {
                string    actualdateid = vdateid;
                string    amtime       = AM;
                string    pmtime       = PM;
                string    pupilid      = pid;
                MySqlRead Msql         = new MySqlRead();
                string    sql;
                MyConn2.Open();

                MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", MyConn2); // Setting tiimeout on mysqlServer
                cmd.ExecuteNonQuery();

                if (pmtime.Contains("\\"))
                {
                    pmtime = pmtime + "\\";
                }
                if (am == true)
                {
                    sql = "update  " + Msql.GetDBName() + "." + "virtual_pup_attendance set am= '" + amtime + "'  where actual_date_id=" + actualdateid + " and pupil_id=" + pupilid + ";";
                }
                else
                {
                    sql = "update  " + Msql.GetDBName() + "." + "virtual_pup_attendance set  pm= '" + pmtime + "' where actual_date_id=" + actualdateid + " and pupil_id=" + pupilid + ";";
                }

                MySqlCommand     MyCommand21 = new MySqlCommand(sql, MyConn2);
                MySqlDataAdapter MyAdapter1  = new MySqlDataAdapter();
                MyAdapter1.SelectCommand = MyCommand21;
                int rowuCount = MyCommand21.ExecuteNonQuery();
                // totupdatecount = totupdatecount + rowuCount;
                MyConn2.Close();
                return(rowuCount);
            }
            catch (Exception ex)
            {
                readAll.WriteToFile("update " + ex.Message + " " + DateTime.Now);
                return(-1);
            }
        }
Esempio n. 6
0
        public string CheckConnectionServer()
        {
            try
            {
                Msql          = new MySqlRead();
                MyConnection2 = Msql.connectionString();
                MyConn2       = new MySqlConnection("datasource=" + Msql.GetServerName() + ";port=3306;username="******";password="******";SslMode=none");
                MyConn2.Open();

                String           Query      = "select * from  " + Msql.GetDBName() + "." + "virtual_upn;";
                MySqlCommand     MyCommand2 = new MySqlCommand(Query, MyConn2);
                MySqlDataAdapter MyAdapter  = new MySqlDataAdapter();
                MyAdapter.SelectCommand = MyCommand2;
                DataTable dtUpnmatch = new DataTable();
                MyAdapter.Fill(dtUpnmatch);
                MyConn2.Close();
                return("true");
            }
            catch (Exception ex)
            {
                return("false " + ex.Message);
            }
        }
Esempio n. 7
0
        public void updateFirstAttendanceOnServer()
        {
            try
            {
                Msql    = new MySqlRead();
                readAll = new ReadAllData();
                Att     = new Attendance();
                SIMSServiceProject.SIMSSettings frm = new SIMSServiceProject.SIMSSettings();
                if (SIMSInterface.LoginHelper.SIMSlogin(readAll.GetServer(), readAll.GetDBName(), readAll.GetUser(), readAll.GetPass()))
                {
                    MyConnection2 = Msql.connectionString();
                    MyConn2       = new MySqlConnection("datasource=" + Msql.GetServerName() + ";port=3306;username="******";password="******";SslMode=none");
                    // currentfile = FileName; //readAll.GetXmlpath() + "stud_attendance" + System.DateTime.Now.Date.ToString("dd/MM/yyyy").Replace('/', '_') + ".xml";
                    if (FileName.Length > 0 && FDB.Rows.Count > 0)
                    {
                        virtual_pup_attendance = new DataTable {
                            TableName = "virtual_pup_attendance"
                        };
                        virtual_pup_attendance.Columns.Add(new DataColumn("actual_date_id", typeof(Int32)));
                        virtual_pup_attendance.Columns.Add(new DataColumn("am", typeof(System.String)));
                        virtual_pup_attendance.Columns.Add(new DataColumn("pm", typeof(System.String)));
                        virtual_pup_attendance.Columns.Add(new DataColumn("pupil_id", typeof(Int32)));
                        WriteToFile("Time matched and updation start.... " + DateTime.Now);

                        DataSet ds = new DataSet();
                        ////// Diffirent files logic//////////////////////////


                        ///////////////////////////////////////////////////

                        //  ds.ReadXml(currentfile);
                        DataTable studdtbl = FDB;

                        for (int k = 0; k < studdtbl.Rows.Count; k++)
                        {
                            String UPN      = studdtbl.Rows[k][8].ToString();
                            String PeopleID = studdtbl.Rows[k][0].ToString();
                            String SCHOOLID = studdtbl.Rows[k][10].ToString();
                            String ADNO     = studdtbl.Rows[k][9].ToString();
                            String Query;
                            if (UPN.Length > 0)
                            {
                                Query = "select id from " + Msql.GetDBName() + "." + "pupils_unique where upn='" + UPN + "' and school_id='" + SCHOOLID + "' ;";
                            }
                            else
                            {
                                Query = "select id from " + Msql.GetDBName() + "." + "pupils_unique where adno='" + ADNO + "' and school_id='" + SCHOOLID + "' ;";
                            }
                            MySqlCommand     MyCommand2 = new MySqlCommand(Query, MyConn2);
                            MySqlDataAdapter MyAdapter  = new MySqlDataAdapter();
                            MyAdapter.SelectCommand = MyCommand2;
                            dTpupil = new DataTable();
                            MyAdapter.Fill(dTpupil);
                            if (dTpupil.Rows.Count > 0)
                            {
                                PID = dTpupil.Rows[0][0].ToString();
                            }

                            //if (readAll.timeDaymatch() == true)
                            //{
                            //    uploadphotos(SCHOOLID, readAll.GetXmlpath(), Convert.ToInt16(PeopleID), Convert.ToInt16(PID));
                            //}

                            String       ADate          = studdtbl.Rows[k][5].ToString(); // Event start date
                            string       convertedADate = ADate.Substring(0, 10);
                            String       Query1         = "select id from " + Msql.GetDBName() + "." + "virtual_attendance_date where actual_date='" + convertedADate + "' ;";
                            MySqlCommand MyCommand21    = new MySqlCommand(Query1, MyConn2);
                            MyConn2.Open();
                            MySqlDataAdapter MyAdapter1 = new MySqlDataAdapter();
                            MyAdapter1.SelectCommand = MyCommand21;
                            dTattdate = new DataTable();
                            MyAdapter1.Fill(dTattdate);
                            MyConn2.Close();

                            if (dTattdate.Rows.Count > 0)
                            {
                                VDID = dTattdate.Rows[0][0].ToString();
                            }

                            if (PID.Length > 0 && VDID.Length > 0)
                            {
                                if (studdtbl.Rows[k][1].ToString() == "AM")
                                {
                                    AM     = studdtbl.Rows[k][2].ToString();
                                    AMflag = true;
                                }
                                if (studdtbl.Rows[k][1].ToString() == "PM")
                                {
                                    PM     = studdtbl.Rows[k][2].ToString();
                                    PMflag = true;
                                }
                                bool InsertServer = Att.checkRecordonServer(PID, VDID, MyConn2);
                                if (InsertServer == false)
                                {
                                    int ins = Att.insertintoServer(PID, VDID, MyConn2, AMflag, PMflag, AM, PM);
                                    totins = totins + ins;
                                }
                                else
                                {
                                    int upd = Att.updateintoServer(PID, VDID, MyConn2, AMflag, PMflag, AM, PM);
                                    totups = totups + upd;
                                }
                                string dfe = SIMSInterface.School.DCSFNumber();
                                Att.LastUpdateonUPNwithflag(studdtbl.Rows[k][8].ToString(), dfe, studdtbl.Rows[k][9].ToString(), PID);
                                virtual_pup_attendance.Clear();
                                PID  = "";
                                VDID = "";
                                dTpupil.Clear();
                                dTattdate.Clear();
                                AM     = "";
                                AMflag = false;

                                PMflag = false;
                                PM     = "";
                            }
                            //String[] FName = FileName.Split('.');
                            //String filename = FName[0] + "updated_" + "" + RecordCount + "_" + ".xml";
                            //System.IO.File.Move(FileName, filename);
                        }
                    }
                    //if (FileName.Length > 0)
                    //{
                    //    int totcnt = totins + totups;
                    //    String[] FName1 = FileName.Split('.');
                    //    String filename1 = FName1[0] + "updated_" + "" + totcnt + "_" + ".xml";
                    //    System.IO.File.Move(FileName, filename1);

                    //}

                    WriteToFile("Total " + totins + " Records Inserted and " + totups + " updated on server   .." + DateTime.Now);
                    FileName = "";
                    FDB.Clear();
                    readAll.DeleteToEmailLog("");
                    totins = 0;
                    totups = 0;
                }
                else
                {
                    //   WriteToFile("Waiting for the time match.." + DateTime.Now);
                }
            }
            catch (Exception ex)
            {
                SendEmail("server", SIMSInterface.School.DCSFNumber(), "Error due to " + ex.Message);
                WriteToFile(ex.Message + " " + DateTime.Now);
            }
        }