using System;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;

namespace Payroll.BO
{
	/// <summary>
	/// Summary description for BORunSQL.
	/// </summary>
	public class BORunSQL
	{
		public BORunSQL()
		{
			//
			// TODO: Add constructor logic here
			//
		}
        private string _messageNote="\n\n\nNOTE: Check your SQL syntax. For multiple sql statments, seperate the individual query with Pipe (|)";
		
        public void RunSQL(string FilePath)
		{
			DARunSQL oDA=new DARunSQL();
			if(!File.Exists(FilePath+"/Scripts.txt"))
			{
				return;
			}
			try
			{
				SQLHelper.BeginTran();	
				
				string line=null;
				StreamReader strReader=new StreamReader(FilePath+"/Scripts.txt");
				
			
				while(((line=strReader.ReadLine()) != null)) 
				{
					oDA.RunSQL(line.ToString());	
					
				}
				strReader.Close();
				SQLHelper.CommitTran();
				File.Delete(FilePath+"/Scripts.txt");
			}
			catch(Exception eh)
			{
				SQLHelper.RollbackTran();
                throw new Exception(eh.Message);
			
			}
		}

		public void RunCustomSQL(string FilePath)
		{
			DARunSQL oDA=new DARunSQL();
			if(!File.Exists(FilePath))
			{
				return;
			}
			try
			{
				SQLHelper.BeginTran();	
				
				string line=null;
				StreamReader strReader=new StreamReader(FilePath);
				
			
				while(((line=strReader.ReadLine()) != null)) 
				{
					oDA.RunSQL(line.ToString());	
				}
				strReader.Close();
				SQLHelper.CommitTran();
				//File.Delete(FilePath+"/Scripts.txt");
			}
            catch (Exception eh)
			{
				SQLHelper.RollbackTran();
                throw new Exception(eh.Message);
			
			}
		}

        public void Select(string sSQL, DataSet oDataSet, bool isSafeExecution)
        {

            DARunSQL oDA = new DARunSQL();
            string sTable = "";
            if (sSQL == "")
            {
                return;
            }
            int nErrLine = 0;
            try
            {
                EGlobal.SetStatusBusy("Executing Select commands....");
                string[] line = sSQL.Split('|');

                for (int i = 0; i < line.Length; i++)
                {
                    nErrLine = i + 1;
                    if (line[i].Trim() == "") continue;
                    string sline = line[i].Trim();
                    string checkline = sline.ToLower();
                    if (isSafeExecution)
                    {
                        if (checkline.StartsWith("delete") || checkline.IndexOf(" delete ") != -1 || checkline.IndexOf("delete ") != -1)
                            throw new Exception("DELETE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("update") || checkline.IndexOf(" update ") != -1 || (checkline.IndexOf("update ") != -1 && checkline.IndexOf(" set ") != -1))
                            throw new Exception("UPDATE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("insert") || checkline.IndexOf(" insert ") != -1 || checkline.IndexOf("insert ") != -1)
                            throw new Exception("INSERT Command execution is not allowed for Safe Execution");
                    }



                    string[] sTableQry = sline.Split('@');
                    if (sTableQry.Length > 1)
                    {
                        sTable = sTableQry[1].Trim();
                        oDA.SelectCommand(sTableQry[0],  oDataSet, sTable);
                    }
                    else
                    {
                        sTable = "Table " + i.ToString();
                        oDA.SelectCommand(sline,  oDataSet, sTable);
                    }
                }

                EGlobal.SetStatusBusy("Execution Completed");
            }
            catch (Exception eh)
            {
                throw new Exception(eh.Message + "\nError in sql block or line " + Convert.ToString(nErrLine + 1) + _messageNote);

            }
        }

		public void Select(string sSQL, string sDataSource, string sDB, string sProvider,string sUserId,string sPassword, DataSet oDataSet,bool isSafeExecution)
		{
			
			DARunSQL oDA=new DARunSQL();
			string sTable="";
			if(sSQL=="")
			{
				return ;
			}
			int nErrLine=0;
            try
            {
                EGlobal.SetStatusBusy("Executing Select commands....");
                string[] line = sSQL.Split('|');

                for (int i = 0; i < line.Length; i++)
                {
                    nErrLine = i + 1;
                    if (line[i].Trim() == "") continue;
                    string sline = line[i].Trim();
                    string checkline = sline.ToLower();
                    if (isSafeExecution)
                    {
                        if (checkline.StartsWith("delete") || checkline.IndexOf(" delete ") != -1 || checkline.IndexOf("delete ") != -1)
                            throw new Exception("DELETE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("update") || checkline.IndexOf(" update ") != -1 || (checkline.IndexOf("update ") != -1 && checkline.IndexOf(" set ")!=-1))
                            throw new Exception("UPDATE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("insert") || checkline.IndexOf(" insert ") != -1 || checkline.IndexOf("insert ") != -1)
                            throw new Exception("INSERT Command execution is not allowed for Safe Execution");
                    }



                    string[] sTableQry = sline.Split('@');
                    if (sTableQry.Length > 1)
                    {
                        sTable = sTableQry[1].Trim();
                        oDA.SelectCommand(sTableQry[0], sDataSource, sDB, sProvider, sUserId.Trim(), sPassword.Trim(), oDataSet, sTable);
                    }
                    else
                    {
                        sTable = "Table " + i.ToString();
                        oDA.SelectCommand(sline, sDataSource, sDB, sProvider, sUserId.Trim(), sPassword.Trim(), oDataSet, sTable);
                    }
                }

                EGlobal.SetStatusBusy("Execution Completed");
            }
            catch (Exception eh)
            {
                throw new Exception(eh.Message + "\nError in sql block or line " + Convert.ToString(nErrLine + 1) + _messageNote);

            }
		}
		public string ExecuteQuery(string sSQL,string sDataSource, string sDB,string sProvider,string sUserId,string sPassword,bool isSafeExecution,bool isCheck)
		{
			DARunSQL oDA=new DARunSQL();
			if(sSQL=="")
			{
				return "";
			}
			int nErrLine=0;
			try
			{
				
				string[] line=sSQL.Split('|');
                
				int nTotalLine=line.Length;

				string sMSG="";

				for(int i=0;i<line.Length;i++) 
				{
					nErrLine=i;
					if(line[i].Trim()=="") continue;
                    string sline = line[i].Trim();
                    string checkline = sline.ToLower();
                    if (isSafeExecution)
                    {
                        if (checkline.StartsWith("delete") || checkline.IndexOf(" delete ") != -1 || checkline.IndexOf("delete ") != -1)
                            throw new Exception("DELETE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("update") || checkline.IndexOf(" update ") != -1 || (checkline.IndexOf("update ") != -1 && checkline.IndexOf(" set ") != -1))
                            throw new Exception("UPDATE Command execution is not allowed for Safe Execution");

                        if (checkline.StartsWith("insert") || checkline.IndexOf(" insert ") != -1 || checkline.IndexOf("insert ") != -1)
                            throw new Exception("INSERT Command execution is not allowed for Safe Execution");
                    }

                    int num = oDA.ExecuteQuery(sline, sDataSource, sDB, sProvider, sUserId.Trim(), sPassword.Trim(), isCheck);
					sMSG += "Affected row (s) " + num.ToString() + "\n ";
					EGlobal.SetStatusBusy("Total executed " + i.ToString() + " of total " + nTotalLine.ToString());
				}
				EGlobal.SetStatusBusy("Successfully executed lines " + nTotalLine.ToString());
				return sMSG;
			}
			catch(Exception eh)
			{
                EGlobal.SetStatusBusy("Error on line number " + Convert.ToString(nErrLine + 1));
                throw new Exception(eh.Message + "\nError in sql block or line " + Convert.ToString(nErrLine + 1) + _messageNote);
			}
		}
		
	}
}