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