<%@ Page language="c#" Debug="true"%><!--#include file="SchoolsWShared.aspx"--><%
//! Support file only, run SchoolsW.html instead !
// This file is used as both Data_Url and Upload_Url
// Generates data for TreeGrid when no data received or saves received changes to database
// Single file, without using TreeGridFramework.asp
 
// --- Database initialization ---
TGSchools TG = new TGSchools(Context);
System.Data.OleDb.OleDbDataReader R;
   
/*string Path = System.IO.Path.GetDirectoryName(Context.Request.PhysicalPath);
string IP = Context.Request.ServerVariables["REMOTE_HOST"];
string CopyFile = Path + "\\..\\ServerDLL\\tmp\\" + IP + "Database.mdb";
string OrigFile = Path + "\\Database.mdb";
bool Copy = System.IO.File.Exists(CopyFile);
string File = Copy ? CopyFile : OrigFile; // If copy file does not exist, opens orig file
string ConnStr1 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=\"";
string ConnStr2 = "\";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=\"Microsoft.Jet.OLEDB.4.0\";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(ConnStr1 + File + ConnStr2);
Conn.Open();
System.Data.OleDb.OleDbCommand Cmd = Conn.CreateCommand();
System.Data.OleDb.OleDbDataReader R;*/


// --- Input parameters initalization ---
string User = TG.GetParam("User").ToLower();
string Pass = TG.GetParam("Pass");
bool NewUser = TG.GetParam("New") == "1";

Response.Write("<Grid>");
bool Err = false;

// --- Adding new user ---
if(NewUser)
{
   R = TG.Read("SELECT Pass FROM Schools_Users WHERE Name=" + ToSql(User));
   if(!R.Read())   //Ok, possible
   {
      TG.Copy();
      TG.Exec("INSERT INTO Schools_Users(Name,Pass) VALUES (" + ToSql(User) + "," + ToSql(Pass) + ")");
      Response.Write("<IO Message='User " + ToXmlString(User) + " has been added successfuly'/>");
   }
   else
   {
      Response.Write ("<IO Result='-1' Message='User name already exists !'/><Lang><Text StartErr='User name already exists !'/></Lang></Grid>");
      Err = true;
   }
}

// --- Password verification ---
if (!Err && User!="")
{
   R = TG.Read ("SELECT Pass FROM Schools_Users WHERE Name=" + ToSql(User));
   if(!R.Read() || Pass!=R[0].ToString())
   {
      Response.Write("<IO Result='-1' Message='Wrong user name or password !'/><Lang><Text StartErr='Wrong user name or password !'/></Lang></Grid>");
      Err = true;
   }
   R.Close();
}
bool Admin = User=="admin";   // @@@ Or change this code to another admin

//------------------------------------------------------------------------------------------------------------------
// --- Saves data ---
string XML = Request["TGData"];
if (XML != "" && XML != null)
{
   if(User=="") Response.Write ("<IO Result='-1' Message='The user have not permission to save data !'/></Grid>");  //Attack or bug
   else
   {
      TG.Copy();
      System.Xml.XmlElement Ch = GetChanges(XML);
      if (Ch!=null) foreach (System.Xml.XmlElement I in Ch)
      {
         string[] ids = GetIds(I);    // User$Def$Ident
         string id = " Owner=" + ToSql(ids[0]) + " AND Id=" + ids[2];
         if(ids[1]!="Main") // Child row (Address, Phone, Link, Map)
         {
            if ((I.GetAttribute("Added")=="1" || I.GetAttribute("Changed")=="1") && I.HasAttribute("CCountry"))
            {
               TG.Exec("UPDATE Schools_Schools SET " + ids[1] + " = " + ToSql(I,"CCountry") + " WHERE" + id);
            }
         }      
         else if (I.GetAttribute("Deleted")=="1")
         {
            TG.Exec("DELETE FROM Schools_Schools WHERE" + id);
            TG.Exec("DELETE FROM Schools_Ratings WHERE" + id);
         }     
         else if (I.GetAttribute("Added")=="1")
         {
            TG.Exec("INSERT INTO Schools_Schools(Owner,Id,Name,Country,State,County,Town,SLevel,Type,FromGrade,ToGrade,Enrollment,Students) VALUES (" 
               + ToSql(I,"CUser") + "," + ToSql(ids[2]) + "," + ToSql(I,"CName") + ","
               + I.GetAttribute("CCountry") + "," + I.GetAttribute("CState") + "," + I.GetAttribute("CCounty") + ","
               + ToSql(I,"CTown") + "," 
               + I.GetAttribute("CLevel") + "," + I.GetAttribute("CType") + ","
               + I.GetAttribute("CGrade1") + "," + I.GetAttribute("CGrade2") + ","
               + I.GetAttribute("CEnrollment") + "," + I.GetAttribute("CStudents")
               + ")");
         }     
         else if (I.GetAttribute("Changed")=="1")
         {
            string Str = "";
            Str += ToSqlUpdateString("Name", I, "CName");
            Str += ToSqlUpdateNumber("Country", I, "CCountry");
            Str += ToSqlUpdateNumber("State", I, "CState");
            Str += ToSqlUpdateNumber("County", I, "CCounty");
            Str += ToSqlUpdateString("Town", I, "CTown");
            Str += ToSqlUpdateNumber("SLevel", I, "CLevel");
            Str += ToSqlUpdateNumber("Type", I, "CType");
            Str += ToSqlUpdateNumber("FromGrade", I, "CGrade1");
            Str += ToSqlUpdateNumber("ToGrade", I, "CGrade2");
            Str += ToSqlUpdateNumber("Enrollment", I, "CEnrollment");
            Str += ToSqlUpdateNumber("Students", I, "CStudents");
            string Str2 = "";
            if (Admin) Str2 += ToSqlUpdateString("Owner", I, "CUser");
            if (Admin) Str2 += ToSqlUpdateString("Id", I, "Ident");
            Str = Str + Str2;
            if(Str != "") TG.Exec("UPDATE Schools_Schools SET " + TrimSQL(Str) + " WHERE " + id);
            if(Str2 != "") TG.Exec("UPDATE Schools_Ratings SET " + TrimSQL(Str2) + " WHERE " + id);   //Updates changes in user/id in Ratings
         }
      }
   }
   Response.Write ("<IO Result='0'/></Grid>");
}
//------------------------------------------------------------------------------------------------------------------
// --- Reads data ---
else if (!Err)
{
   string Str = "";
   if (User == "") Str += "<Cfg Adding='0' Deleting='0' Editing='0'/><Toolbar Save='0'/>";
   else 
   {
      Str += "<Def><D Name='R' CUser='" + User.Replace("&","&amp;").Replace("'","&apos;").Replace("<","&lt;") + "'/></Def>";
      Str += "<Cols><C Name='CRating' Button='None'/></Cols>";
   }
   if(!Admin) Str += "<RightCols><C Name='CUser' Visible='0' CanHide='0'/></RightCols>";
   
   Str += "<Body><B>";
   string SQL = "SELECT * FROM Schools_Schools";
   if (User!="" && !Admin) SQL += " WHERE Owner='" + User.Replace("'","''") + "'";
   R = TG.Read(SQL);
   while(R.Read())
   {
      string id = " Ident=" + ToXml(R,"ID") + " CUser=" + ToXml(R,"Owner");
      Str += "<I Def='Main'" + id;
      Str += ToXml("CName",R,"Name");
      Str += ToXml("CCountry",R,"Country");
      Str += ToXml("CState",R,"State");
      Str += ToXml("CCounty",R,"County");
      Str += ToXml("CTown",R,"Town");
      Str += ToXml("CLevel",R,"SLevel");
      Str += ToXml("CType",R,"Type");
      Str += ToXml("CGrade1",R,"FromGrade");
      Str += ToXml("CGrade2",R,"ToGrade");
      Str += ToXml("CEnrollment",R,"Enrollment");
      Str += ToXml("CStudents",R,"Students");
      Str += ">\n";
      Str += "<I Def='Address'" + id + ToXml("CCountry",R,"Address") + "/>";
      Str += "<I Def='Phone'" + id + ToXml("CCountry",R,"Phone") + "/>";
      Str += "<I Def='Link'" + id + ToXml("CCountry",R,"Link") + "/>";
      Str += "<I Def='Map'" + id + ToXml("CCountry",R,"Map") + "/>";
      Str += "<I Def='Reviews'" + id + ToXml("Count",R,"RatingCnt") + ToXml("CRatingsum",R,"RatingSum") + ">";
      Str += "</I>";
      Str += "</I>\n";
   }
   Str += "</B></Body></Grid>";
   Response.Write(Str);
}

TG.Dispose();
// --------------------------------------------------------------------------
%>