private string GenerateOneQueryFile(DataTableObject item, string attribute)
{
//===========get multiple===========
string vars = "";
foreach (var v in item.RowList)
{
vars += "@" + v.ColumnName + ",";
}
vars = vars.Remove(vars.Length - 1);
string str = "function Get" + item.TableName + "By" + attribute + "($" + attribute + ")\n{\n";
str += "\t$query = \"CALL sp_" + item.TableName + "_BY_" + attribute + "(\" . $" + attribute + " . \")\";\n";
str += "\tif ($result = connect()->query($query)) {\n";
str += "\t $emparray = array();\n";
str += "\t while ($row = mysqli_fetch_assoc($result))\n\t{\n";
str += "\t $emparray[] = $row;\n";
str += "\t}\n";
str += "\t$result->free();\n";
str += "\tconnect()->close();\n";
str += "\treturn json_encode($emparray);}\n}";
return(str);
}
private string GenerateInsert(DataTableObject item)
{
string variables = "", pass = "", passVariables = "";
foreach (var col in item.RowList)
{
if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName)
{
variables += " '" + col.ColumnName + "' : " + "$('#" + col.ColumnName + "').val() ,";
pass += col.ColumnName.ToLower() + " ,";
passVariables += " '" + col.ColumnName + "' : " + col.ColumnName.ToLower() + " ,";
}
}
if (!String.IsNullOrEmpty(variables))
{
variables = variables + "***";
variables = variables.Replace(",***", "");
pass = pass + "***";
pass = pass.Replace(",***", "");
passVariables = passVariables + "***";
passVariables = passVariables.Replace(",***", "");
}
string str = "function create" + item.TableName.ToLower() + "()\n{";
str += "\n\tvar d = { " + variables + " };";
str += "\n\tvar data = JSON.stringify(d);";
str += "\n\t$.ajax({";
str += "\n\ttype: \"GET\",";
str += "\n\turl: '" + site_root + "Create" + item.TableName + ".php',";
str += "\n\tdata: { 'data':data},";
str += "\n\tcontentType: \"application/json; charset=utf-8\",";
str += "\n\tdataType: \"json\",";
str += "\n\tsuccess: successFunc,";
str += "\n\terror: errorFunc";
str += "\n\t});\n}\n\n";
str += "function create" + item.TableName.ToLower() + "v(" + pass + ")\n{";
str += "\n\tvar d = { " + passVariables + " };";
str += "\n\tvar data = JSON.stringify(d);";
str += "\n\t$.ajax({";
str += "\n\ttype: \"GET\",";
str += "\n\turl: '" + site_root + "Create" + item.TableName + ".php',";
str += "\n\tdata: { 'data':data},";
str += "\n\tcontentType: \"application/json; charset=utf-8\",";
str += "\n\tdataType: \"json\",";
str += "\n\tsuccess: successFunc,";
str += "\n\terror: errorFunc";
str += "\n\t});\n}";
if (item.TableName == "Identity")
{
str = GenerateIdentity(item);
}
return(str);
}
/// <summary>
/// Set column value parameter
/// </summary>
/// <param name="dataTable"></param>
/// <param name="prefix"></param>
/// <returns></returns>
private string Set(DataTableObject dataTable, string prefix)
{
string str = "";
foreach (var row in dataTable.RowList)
{
str += row.ColumnName + " = " + prefix + row.ColumnName + ",\n";
}
str = str.Substring(0, str.Length - 2);
return(str);
}
private string GenerateFetchQuery(DataTableObject item)
{
//Arrays Results
string str = "function GetAll" + item.TableName + "()\n{";
str += "\n\t$query='CALL sp_" + item.TableName + "_Select();';\n";
str += "\t if ($result = connect()->query($query)) {\n";
str += "\t $emparray = array();\n";
str += "\t while ($row = mysqli_fetch_assoc($result))\n{\n";
str += "\t $emparray[] = $row;\n";
str += "\t }\n";
str += "\t $result->free();\n";
str += "\t connect()->close();\n";
str += "\t return json_encode($emparray);\n}\n}";
return(str);
}
private string GenerateGetAll(DataTableObject item)
{
//Arrays Results
string str = "<?php \n\n include_once 'global.php';\n\n ";
str += "\n\t$query='CALL sp_" + item.TableName + "_Select();';\n";
str += "\t if ($result = connect()->query($query)) {\n";
str += "\t $emparray = array();\n";
str += "\t while ($row = mysqli_fetch_assoc($result))\n{\n";
str += "\t $emparray[] = $row;\n";
str += "\t }\n";
str += "\t $result->free();\n";
str += "\t connect()->close();\n";
str += "\t echo json_encode($emparray);\n}\n\n?>";
return(str);
}
/// <summary>
/// Get one result stored procedure
/// </summary>
/// <param name="dataTable"></param>
/// <param name="attribute"></param>
/// <param name="datatype"></param>
/// <returns></returns>
private string GenerateFetchOne(DataTableObject dataTable, string attribute, string datatype)
{
string str = "";
str += "DELIMITER $$\n";
str += "DROP PROCEDURE IF EXISTS sp_" + dataTable.TableName + "_BY_" + attribute + "$$\n";
str += "CREATE PROCEDURE sp_" + dataTable.TableName + "_BY_" + attribute + "(\n";
str += "IN p" + attribute + " " + datatype + "\n)\n";
str += "LANGUAGE SQL\n";
str += "DETERMINISTIC\n";
str += "SQL SECURITY DEFINER\n";
str += "COMMENT 'A procedure to select all'\n";
str += "BEGIN\n";
str += "SELECT * FROM " + dataTable.TableName + " WHERE " + attribute + " = p" + attribute + ";\n";
str += "END$$\n\n";
return(str);
}
/// <summary>
/// Select all the content from a table
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GenerateFetchQuery(DataTableObject dataTable)
{
string str = "";
str += "DELIMITER $$\n";
str += "DROP PROCEDURE IF EXISTS sp_" + dataTable.TableName + "_Select$$\n";
str += "CREATE PROCEDURE sp_" + dataTable.TableName + "_Select()\n";
str += "LANGUAGE SQL\n";
str += "DETERMINISTIC\n";
str += "SQL SECURITY DEFINER\n";
str += "COMMENT 'A procedure to select all'\n";
str += "BEGIN\n";
str += "SELECT * FROM " + dataTable.TableName + ";\n";
str += "END$$\n\n";
return(str);
}
/// <summary>
/// Generate update table by primary stored procedure
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GenerateUpdate(DataTableObject dataTable)
{
string str = "DELIMITER $$\n";
str += "DROP PROCEDURE IF EXISTS sp_Update_" + dataTable.TableName + "$$\n";
str += "CREATE PROCEDURE sp_Update_" + dataTable.TableName + "(";
str += DeclareNoAuto(dataTable);
str += ")\n";
str += "BEGIN\n";
str += "UPDATE " + dataTable.TableName + "\nSET\n";
str += Set(dataTable, "p");
str += "\nWHERE " + dataTable.PrimaryKey + " = p" + dataTable.PrimaryKey;
str += ";\nEND$$";
return(str);
}
/// <summary>
/// Resolve default values: Some values can not be updated, such as record create date
/// </summary>
/// <param name="dataTable"></param>
/// <param name="prefix"></param>
/// <returns></returns>
private string ColumnsNoAuto(DataTableObject dataTable, string prefix)
{
string str = "";
foreach (var row in dataTable.RowList)
{
var isForeign = row.ColumnName.StartsWith("FK_", StringComparison.InvariantCulture);
var isPrimary = row.ColumnName.EndsWith("_PK", StringComparison.InvariantCulture);
var datebool = row.ColumnName.ToLower().Contains("adddate");
if (!isPrimary && !isForeign && !datebool && row.DataType != null && row.DataType.ToString() != "timestamp" && dataTable.PrimaryKey != row.ColumnName)
{
str += prefix + "" + row.ColumnName + ",\n";
}
}
if (!String.IsNullOrEmpty(str))
{
str = str.Substring(0, str.Length - 2);
}
return(str);
}
/// <summary>
/// Generate insert into a table stored procedure
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GenerateInsert(DataTableObject dataTable)
{
string str = "";
str += "DELIMITER $$\n";
str += "DROP PROCEDURE IF EXISTS sp_Insert_" + dataTable.TableName + "$$\n";
str += "CREATE PROCEDURE sp_Insert_" + dataTable.TableName + "(\n";
str += DeclareNoAuto(dataTable);
str += ")\n";
str += "BEGIN\n";
str += "INSERT INTO " + dataTable.TableName + "(\n";
str += ColumnsNoAuto(dataTable, "");
str += ")\n";
str += "VALUES(\n";
str += ColumnsNoAuto(dataTable, "p");
str += ");\nSELECT LAST_INSERT_ID();\n";
str += "END$$";
return(str);
}
private string GenerateUpdate(DataTableObject item)
{
string variables = "", pass = "";
foreach (var col in item.RowList)
{
if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName)
{
if (col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal")
{
variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n";
pass += "\t$" + col.ColumnName + " . \",\" . ";
}
else if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime")
{
variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n";
pass += "\t\"'\" . $" + col.ColumnName + " . \"'\" . \",\" . ";
}
}
}
if (!String.IsNullOrEmpty(pass))
{
pass = pass + "***";
pass = pass.Replace(" . \",\" . ***", "");
}
string str = "<?php\n\n";
str += "include_once 'global.php';\n";
str += "\n\t $data = $_GET['data'];\n";
str += "\n" + variables;
str += "\n\t $query=\"CALL sp_Update_" + item.TableName + "(\" . " + pass + "\")\";\n";
str += "\t if (mysqli_query(connect(), $query)){\n";
str += "\t $last_id = mysqli_insert_id(connect());\n";
str += "\t echo $last_id;\n" +
"\tmysqli_close(connect());}\n\n?>";
return(str);
}
/// <summary>
/// Generate delete record by primary id stored procedure
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GenerateDelete(DataTableObject dataTable)
{
string declare = "", set = "";
declare += "IN pRecordStatus int,\n";
set = "RecordStatus = pRecordStatus\n";
string str = "DELIMITER $$\n";
str += "DROP PROCEDURE IF EXISTS sp_Delete_" + dataTable.TableName + "$$\n";
str += "CREATE PROCEDURE sp_Delete_" + dataTable.TableName + "(\n";
str += "IN pRecordStatus int";
str += ")\n";
str += "BEGIN\n";
str += "UPDATE " + dataTable.TableName + "\nSET\n";
str += set;
str += "\nWHERE " + dataTable.PrimaryKey + " = p" + dataTable.PrimaryKey + ";";
str += "\nEND$$";
return(str);
}
//private string GenerateGetAll(DataTableObject item)
//{
// string str = "";
// //"function getall" + item.TableName.ToLower() + "()\n{";
// //str += "\n\t$.ajax({";
// //str += "\n\ttype: \"GET\",";
// //str += "\n\turl: '" + site_root + "GetAll" + item.TableName + ".php',";
// //str += "\n\tcontentType: \"application/json; charset=utf-8\",";
// //str += "\n\tdataType: \"json\",";
// //str += "\n\tasync: true,";
// //str += "\n\tcomplete: completed,";
// //str += "\n\tsuccess: successFunc,";
// //str += "\n\terror: errorFunc";
// //str += "\n\t});\n}\n\n";
// str += "\n\tasync function getall" + item.TableName.ToLower() + "()\n{";
// str += "\n\tconst result = await $.ajax({";
// str += "\n\turl: '" + site_root + "GetAll" + item.TableName + ".php',";
// str += "\n\ttype: 'GET'";
// str += "\n\t});";
// str += "return result;";
// str += "\n\t}\n\n";
// return str;
//}
private string GenerateDelete(DataTableObject item)
{
string variables = "", pass = "";
foreach (var col in item.RowList)
{
if (col.DataType != null && col.ColumnName != "AddDate")
{
if (col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal")
{
variables += "\t$" + col.ColumnName + " = " + "$data['" + col.ColumnName + "'];\n";
pass += "\t$" + col.ColumnName + " . \",\" . ";
}
else if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime")
{
variables += "\t$" + col.ColumnName + " = " + "$data['" + col.ColumnName + "'];\n";
pass += "\t\"'\" . $" + col.ColumnName + " \"'\" . \",\" . ";
}
}
}
if (!String.IsNullOrEmpty(pass))
{
pass = pass.Remove(pass.Length - 6);
}
string str = "<?php\n\n";
str += "\n\t $data = $_POST['data'];\n";
str += "\n" + variables;
str += "\t $query=\"CALL sp_Delete_" + item.TableName + "(\" . " + pass + "\")\";\n";
str += "\t if (mysqli_query(connect(), $query)) \n{\n";
str += "\t $last_id = mysqli_insert_id(connect());\n";
str += "\t return json_encode($last_id);\n" +
"\t mysqli_close(connect());}\n\n?>";
return(str);
}
private string GenerateIdentity(DataTableObject item)
{
string columns = "", pass = "";
foreach (var col in item.RowList)
{
if (col.DataType != null)
{
if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime")
{
columns += "'\" . $" + col.ColumnName + " . \"',";
pass += "$" + col.ColumnName + ",";
}
else
{
if (item.PrimaryKey != col.ColumnName)
{
if (col.DataType == null)
{
columns += "\" . $" + col.ColumnName + " . \",";
pass += "$" + col.ColumnName + ",";
}
else
{
if (col.DataType.ToString().ToLower() != "timestamp")
{
if (col.ColumnName == "RecordStatus")
{
columns += "\" . 1 . \",";
}
else
{
columns += "\" . $" + col.ColumnName + " . \",";
pass += "$" + col.ColumnName + ",";
}
}
}
}
}
}
}
if (!String.IsNullOrEmpty(columns))
{
columns = columns.Trim().Remove(columns.Length - 1);
}
if (!String.IsNullOrEmpty(pass))
{
pass = pass.Trim().Remove(pass.Length - 1);
}
string str = "function Create" + item.TableName + "(" + pass + ")\n{";
str += "\t //$data = json_decode($data);\n";
str += "\n\t $query=\"CALL sp_Insert_" + item.TableName + "(" + columns + ")\";\n";
str += "\t $result = mysqli_query(connect(), $query) or die('Query fail');// . mysqli_error());\n";
str += "\t$last_id = -1;\n";
str += "\t while ($row = mysqli_fetch_array($result))\n";
str += "\t { $last_id = $row[0]; }\n";
str += "\t return $last_id;\n";
str += "\t mysqli_close(connect());\n}";
return(str);
}
private string GenerateInsert(DataTableObject item)
{
string variables = "\n", variables2 = "\n", pass = "", varDeclare = "\n";
foreach (var col in item.RowList)
{
if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName)
{
if (col.DataType.ToString() == "float" || col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal" || col.DataType.ToString() == "bit" || col.DataType.ToString() == "double")
{
varDeclare += "\t$" + col.ColumnName + " = null;\n";
variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n";
variables2 += "\t$" + col.ColumnName + " = " + "$data[0]->" + col.ColumnName + ";\n";
pass += "\t$" + col.ColumnName + " . \",\" . ";
}
else if ((col.DataType.ToString() == "mediumblob" || col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date" || col.DataType.ToString() == "datetime") || col.DataType.ToString() == "datetime")
{
varDeclare += "\t$" + col.ColumnName + " = null;\n";
variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n";
variables2 += "\t$" + col.ColumnName + " = " + "$data[0]->" + col.ColumnName + ";\n";
pass += "\t\"'\" . $" + col.ColumnName + " . \"'\" . \",\" . ";
}
}
}
if (!String.IsNullOrEmpty(variables))
{
variables = variables.Remove(variables.Length - 1);
}
if (!String.IsNullOrEmpty(pass))
{
pass = pass + "***";
pass = pass.Replace(" . \",\" . ***", "");
}
string baseCode = "$data1 = $_POST['data'];//website";
baseCode += "$data = null;";
baseCode += varDeclare;
baseCode += "if ($data1 != null) \n";
baseCode += "{\n";
baseCode += "\t$data2 = json_encode($data1); \n";
baseCode += "\t$data = json_decode($data2);\n";
baseCode += variables2;
baseCode += "} \n";
baseCode += "else\n";
baseCode += "{\n";
baseCode += "\t$data1 = file_get_contents('php://input');\n";
baseCode += "\t$data = json_decode($data1);\n";
baseCode += variables;
baseCode += "\n}\n\n";
string str = "<?php\n\n";
str += "include_once 'global.php';\n";
str += baseCode;
// str += "\n\t$d = file_get_contents('php://input');\n";
//str += "\n\t$data = json_decode($d);\n";
// str += variables +"\n\n";
str += "\t$query=\"CALL sp_Insert_" + item.TableName + "(\"." + pass + " . \")\";\n";
str += "\t $result = mysqli_query(connect(), $query) or die('Query fail');// . mysqli_error());\n";
str += "\t$last_id = -1;\n";
str += "\t while ($row = mysqli_fetch_array($result))\n";
str += "\t { $last_id = $row[0]; }\n";
str += "\t echo json_encode(array('ID' => $last_id), JSON_FORCE_OBJECT);\n";
str += "\t mysqli_close(connect());\n\n?>";
if (item.TableName == "Identity")
{
str = GenerateIdentity(item);
}
return(str);
}
/// <summary>
/// Read sql file and convert it to DataTableObject
/// </summary>
/// <param name="sqlLocation">Full file location</param>
/// <returns></returns>
public List <DataTableObject> Get(string sqlLocation)
{
if (!File.Exists(sqlLocation))
{
throw new Exception("Specified MySql file does not exists");
}
var list = new List <DataTableObject>();
var fileLines = File.ReadAllLines(sqlLocation);
var dtCount = File.ReadAllText(sqlLocation).Split(new string[] { "create table" }, StringSplitOptions.None).Length;
Boolean canStartCollecting = false;
foreach (string line in fileLines)
{
if (!String.IsNullOrEmpty(line))
{
if (line.ToLower().Contains(("create table").ToLower()))
{
objTable = new DataTableObject();
objTable.RowList = new List <DataRow>();
var temp = line.TrimStart().Split(' ');
objTable.TableName = temp[2];
canStartCollecting = true;
}
if (canStartCollecting)
{
var str = line.TrimStart().Split(' ');
if (!line.ToLower().Contains(("create table").ToLowerInvariant()) && !line.Contains(");") && line.Trim() != ")" && line.Trim() != "(")
{
if (line.ToLower().Contains(("AUTO_INCREMENT").ToLower()))
{
objTable.PrimaryKey = str[0];
objTable.PrimaryKeyDataType = str[1];
}
if (str[0].ToLower() != "constraint" && str[0].Length != 0)
{
var row = new DataRow();
row.ColumnName = str[0];
row.WholeDataType = str[1];
if (str[1].ToLower().Contains(("varchar").ToLowerInvariant()))
{
row.DataType = "varchar";
row.Length = str[1].Split('(', ')')[1];
}
else if (str[1].ToLower().Contains(("decimal").ToLowerInvariant()))
{
row.DataType = "varchar";
row.Length = str[1].Split('(', ')')[1];
}
else
{
row.DataType = str[1];
}
if (str.Length > 2)
{
row.IsNull = false;
}
else
{
row.IsNull = true;
}
objTable.RowList.Add(row);
}
else
{
if (str.Length > 5)
{
var row = new DataRow();
row.ColumnName = str[1];
foreach (var i in str[6])
{
if (i != '(')
{
row.TableRef += i;
}
else
{
break;
}
}
row.WholeDataType = str[1];
row.TableRefID = str[4].Split('(', ')')[1];
objTable.RowList.Add(row);
}
}
}
else
{
if (line.Contains(");"))
{
list.Add(objTable); canStartCollecting = false; objTable = null;
}
}
}
}
}
for (int i = 0; i < list.Count; i++)
{
var obj = list[i];
for (int f = 0; f < list.Count; f++)
{
var trr = list[f].TableName;
for (int e = 0; e < list[f].RowList.Count; e++)
{
if ((list[i].TableName + list[i].PrimaryKey == list[f].RowList[e].ColumnName) || ("Parent" + list[i].TableName + list[i].PrimaryKey == list[f].RowList[e].ColumnName))
{
list[f].RowList[e].TableRef = list[i].TableName;
}
}
}
}
return(list);
}
