using ExpressBase.Common;

using ExpressBase.Common.Constants;

using ExpressBase.Common.Data;

using ExpressBase.Common.Extensions;

using ExpressBase.Common.LocationNSolution;

using ExpressBase.Common.Objects;

using ExpressBase.Common.Structures;

using ExpressBase.Security;

using ExpressBase.Objects;

using ExpressBase.Objects.Objects;

using ExpressBase.Objects.Objects.DVRelated;

using ExpressBase.Objects.ServiceStack_Artifacts;

using ExpressBase.Objects.WebFormRelated;

using Jurassic;

using Jurassic.Library;

using Microsoft.CodeAnalysis.CSharp.Scripting;

using Microsoft.CodeAnalysis.Scripting;

using Newtonsoft.Json;

using ServiceStack;

using ServiceStack.Messaging;

using System;

using System.Collections.Generic;

using System.Data.Common;

using System.Linq;

using System.Net;

using System.Globalization;

using ExpressBase.Objects.Helpers;

using Newtonsoft.Json.Linq;

using System.IO;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using ServiceStack.Text;

using System.Text;

using ServiceStack.Redis;

namespace ExpressBase.ServiceStack.Services

{

[Authenticate]

public class WebFormServices : EbBaseService

{

public WebFormServices(IEbConnectionFactory _dbf, IMessageProducer _mqp, PooledRedisClientManager pooledRedisManager) : base(_dbf, _mqp, pooledRedisManager) { }

//========================================== FORM TABLE CREATION ==========================================

public CreateWebFormTableResponse Any(CreateWebFormTableRequest request)

{

if (request.WebObj is EbWebForm)

{

EbWebForm Form = request.WebObj as EbWebForm;

Form.AfterRedisGet_All(this);

if (Form.EnableSqlRetriver)

{

Form.SolutionObj = request.SoluObj ?? this.GetSolutionObject(request.SolnId);

CreateFormDataSqlRetrival(Form);

}

if (Form.DataPushers.Count > 0)

{

foreach (EbDataPusher pusher in Form.DataPushers)

{

if (pusher is EbApiDataPusher)

continue;

EbWebForm _form = this.GetWebFormObject(pusher.FormRefId, null, null);

TableSchema _tableDest = _form.FormSchema.Tables.Find(e => e.TableName.Equals(_form.FormSchema.MasterTable));

//_table.Columns.Add(new ColumnSchema { ColumnName = "eb_push_id", EbDbType = (int)EbDbTypes.String, Control = new EbTextBox { Name = "eb_push_id", Label = "Push Id" } });// multi push id

//_table.Columns.Add(new ColumnSchema { ColumnName = "eb_src_id", EbDbType = (int)EbDbTypes.Decimal, Control = new EbNumeric { Name = "eb_src_id", Label = "Source Id" } });// source master table id

if (_tableDest != null)

{

if (pusher is EbBatchFormDataPusher batchDp)

{

TableSchema _tableSrc = Form.FormSchema.Tables.Find(e => e.ContainerName == batchDp.SourceDG);

if (_tableSrc != null)

{

string cName = _tableSrc.TableName + FormConstants._id;

if (!_tableDest.Columns.Exists(e => e.ColumnName == cName))

{

_tableDest.Columns.Add(new ColumnSchema

{

ColumnName = cName,

EbDbType = (int)EbDbTypes.Int32,

Control = new EbNumeric { Name = cName }

});

}

cName = _tableDest.TableName + FormConstants._id;

if (!_tableSrc.Columns.Exists(e => e.ColumnName == cName))

{

_tableSrc.Columns.Add(new ColumnSchema

{

ColumnName = cName,

EbDbType = (int)EbDbTypes.Int32,

Control = new EbNumeric { Name = cName }

});

}

}

}

Form.FormSchema.Tables.Add(_tableDest);

}

}

}

CreateWebFormTables(Form, request);

InsertDataIfRequired(Form.FormSchema, Form.RefId);

}

return new CreateWebFormTableResponse { };

}

private void CreateFormDataSqlRetrival(EbWebForm Form)

{

(string srcTableQuery, string destTableQuery) = Form.GetFormDataQueries(this.EbConnectionFactory.DataDB, this);

string[] ref_id_parts = Form.RefId.Split("-");

string[] queries4PriTable = srcTableQuery.Trim().TrimEnd(';').Split(';');

string[] queries4DataPushers = destTableQuery.Trim().Length > 0 ? destTableQuery.Trim().TrimEnd(';').Split(';') : new string[0];

string[] queries4PowSelect = Form.GetFormDataPsSelectQueries(this);

string FnString = GetFunctionString_4_FormDataRetrieval(Form.DisplayName, Convert.ToInt32(ref_id_parts[3]), Convert.ToInt32(ref_id_parts[4]), Form.TableName, queries4PriTable, queries4DataPushers, queries4PowSelect);

this.EbConnectionFactory.DataDB.DoNonQuery(FnString);

}

static string GetFunctionString_4_FormDataRetrieval(string form_displayname, int form_id, int form_ver_id, string primaryTableName,

string[] queries4PriTable, string[] queries4DataPushers, string[] queries4PowSelect)

{

StringBuilder stringBuilder = new StringBuilder();

stringBuilder.Append($@"

CREATE OR REPLACE FUNCTION public.eb_udf_{form_displayname.ToLower().Replace(" ", "_").Replace("-", "_").Replace("&", "_")}_{form_id}_{form_ver_id}_get_form_data(id__in integer, include_datapusher__in boolean)

RETURNS SETOF refcursor

LANGUAGE plpgsql

AS $$

DECLARE");

for (int i = 0; i < queries4PriTable.Length; i++)

stringBuilder.Append($@"

ref{i} refcursor:= 'ref{i}';");

for (int i = queries4PriTable.Length; i < queries4PriTable.Length + queries4PowSelect.Length; i++)

stringBuilder.Append($@"

ref{i} refcursor:= 'ref{i}';");

for (int i = queries4PriTable.Length + queries4PowSelect.Length; i < queries4PriTable.Length + queries4PowSelect.Length + queries4DataPushers.Length; i++)

stringBuilder.Append($@"

ref{i} refcursor:= 'ref{i}';");

stringBuilder.Append(@"

BEGIN");

for (int i = 0; i < queries4PriTable.Length; i++)

{

stringBuilder.Append($@"

OPEN ref{i} FOR

{queries4PriTable[i].Replace($"@{primaryTableName}_id", "id__in")};

RETURN NEXT ref{i};

");

}

for (int i = queries4PriTable.Length; i < queries4PriTable.Length + queries4PowSelect.Length; i++)

{

stringBuilder.Append($@"

OPEN ref{i} FOR

{queries4PowSelect[i - queries4PriTable.Length]};

RETURN NEXT ref{i};

");

}

for (int i = queries4PriTable.Length + queries4PowSelect.Length; i < queries4PriTable.Length + queries4PowSelect.Length + queries4DataPushers.Length; i++)

{

stringBuilder.Append($@"

IF include_datapusher__in THEN

OPEN ref{i} FOR

{queries4DataPushers[i - queries4PriTable.Length - queries4PowSelect.Length].Replace($"@{primaryTableName}_id", "id__in")};

RETURN NEXT ref{i};

END IF;

");

}

stringBuilder.Append(@"

END;

$$");

return stringBuilder.ToString();

}

public CreateMyProfileTableResponse Any(CreateMyProfileTableRequest request)

{

List<TableColumnMeta> listNamesAndTypes = new List<TableColumnMeta>

{

new TableColumnMeta { Name = "eb_users_id", Type = this.EbConnectionFactory.DataDB.VendorDbTypes.Int32 }

};

if (request.UserTypeForms != null)

{

foreach (EbProfileUserType eput in request.UserTypeForms)

{

if (string.IsNullOrEmpty(eput.RefId))

continue;

EbWebForm _form = EbFormHelper.GetEbObject<EbWebForm>(eput.RefId, null, this.Redis, this, this.PooledRedisManager);

string Msg = string.Empty;

CreateOrAlterTable(_form.TableName, listNamesAndTypes, ref Msg);

Console.WriteLine("CreateMyProfileTableRequest - WebForm Resp msg: " + Msg);

}

}

if (request.UserTypeMobPages != null)

{

foreach (EbProfileUserType eput in request.UserTypeMobPages)

{

if (string.IsNullOrEmpty(eput.RefId))

continue;

EbMobilePage _mobPage = EbFormHelper.GetEbObject<EbMobilePage>(eput.RefId, null, this.Redis, this, this.PooledRedisManager);

if (!(_mobPage.Container is EbMobileForm))

continue;

string Msg = string.Empty;

CreateOrAlterTable((_mobPage.Container as EbMobileForm).TableName, listNamesAndTypes, ref Msg);

Console.WriteLine("CreateMyProfileTableRequest - MobileForm Resp msg: " + Msg);

}

}

return new CreateMyProfileTableResponse { };

}

//Review control related data

private void InsertDataIfRequired(WebFormSchema _schema, string _refId)

{

EbReview reviewCtrl = (EbReview)_schema.ExtendedControls.Find(e => e is EbReview);

if (reviewCtrl == null || _refId == null)

return;

int[] stageIds = new int[reviewCtrl.FormStages.Count];

string selQ = @"SELECT id FROM eb_stages WHERE form_ref_id = @form_ref_id AND COALESCE(eb_del, 'F') = 'F' ORDER BY id; ";

EbDataTable dt = this.EbConnectionFactory.DataDB.DoQuery(selQ, new DbParameter[] { this.EbConnectionFactory.DataDB.GetNewParameter("form_ref_id", EbDbTypes.String, _refId) });

for (int i = 0; i < dt.Rows.Count && i < stageIds.Length; i++)

{

int.TryParse(dt.Rows[i][0].ToString(), out stageIds[i]);

}

string fullQ = $@"UPDATE eb_stage_actions SET eb_del = 'T' WHERE COALESCE(eb_del, 'F') = 'F' AND eb_stages_id IN (SELECT id FROM eb_stages WHERE form_ref_id = @form_ref_id AND COALESCE(eb_del, 'F') = 'F');

UPDATE eb_stages SET eb_del = 'T' WHERE form_ref_id = @form_ref_id AND COALESCE(eb_del, 'F') = 'F' AND id NOT IN ({stageIds.Join(",")}); ";

List<DbParameter> param = new List<DbParameter>();

param.Add(this.EbConnectionFactory.DataDB.GetNewParameter("form_ref_id", EbDbTypes.String, _refId));

for (int i = 0; i < stageIds.Length; i++)

{

EbReviewStage reviewStage = reviewCtrl.FormStages[i] as EbReviewStage;

string stageid = "(SELECT eb_currval('eb_stages_id_seq'))";

if (stageIds[i] == 0)

{

fullQ += $@"INSERT INTO eb_stages(stage_name, stage_unique_id, form_ref_id, eb_del)

VALUES (@stage_name_{i}, @stage_unique_id_{i}, @form_ref_id, 'F'); ";

if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.MYSQL)

fullQ += $"SELECT eb_persist_currval('eb_stages_id_seq'); ";

}

else

{

fullQ += $@"UPDATE eb_stages SET stage_name = @stage_name_{i}, stage_unique_id = @stage_unique_id_{i}

WHERE form_ref_id = @form_ref_id AND id = {stageIds[i]}; ";

stageid = stageIds[i].ToString();

}

param.Add(this.EbConnectionFactory.DataDB.GetNewParameter($"stage_name_{i}", EbDbTypes.String, reviewStage.Name));

param.Add(this.EbConnectionFactory.DataDB.GetNewParameter($"stage_unique_id_{i}", EbDbTypes.String, reviewStage.EbSid));

for (int j = 0; j < reviewStage.StageActions.Count; j++)

{

EbReviewAction reviewAction = reviewStage.StageActions[j] as EbReviewAction;

fullQ += $@"INSERT INTO eb_stage_actions(action_name, action_unique_id, eb_stages_id, eb_del)

VALUES (@action_name_{i}_{j}, @action_unique_id_{i}_{j}, {stageid}, 'F'); ";

param.Add(this.EbConnectionFactory.DataDB.GetNewParameter($"action_name_{i}_{j}", EbDbTypes.String, reviewAction.Name));

param.Add(this.EbConnectionFactory.DataDB.GetNewParameter($"action_unique_id_{i}_{j}", EbDbTypes.String, reviewAction.EbSid));

}

}

this.EbConnectionFactory.DataDB.DoNonQuery(fullQ, param.ToArray());

}

private void CreateWebFormTables(EbWebForm Form, CreateWebFormTableRequest request)

{

WebFormSchema _schema = Form.FormSchema;

Form.SolutionObj = request.SoluObj ?? this.GetSolutionObject(request.SolnId);

EbSystemColumns ebs = Form.SolutionObj.SolutionSettings?.SystemColumns ?? new EbSystemColumns(EbSysCols.Values);// Solu Obj is null

IVendorDbTypes vDbTypes = this.EbConnectionFactory.DataDB.VendorDbTypes;

string Msg = string.Empty;

foreach (TableSchema _table in _schema.Tables.FindAll(e => !e.DoNotPersist))

{

List<TableColumnMeta> _listNamesAndTypes = new List<TableColumnMeta>();

if (_table.Columns.Count > 0 && _table.TableType != WebFormTableTypes.Review)

{

bool CurrencyCtrlFound = false;

foreach (ColumnSchema _column in _table.Columns)

{

if (_column.Control is EbAutoId)

{

_listNamesAndTypes.Add(new TableColumnMeta { Name = _column.ColumnName, Type = vDbTypes.GetVendorDbTypeStruct((EbDbTypes)_column.EbDbType), Unique = true, Control = _column.Control, Label = _column.Control.Label });

_listNamesAndTypes.Add(new TableColumnMeta { Name = _column.ColumnName + "_ebbkup", Type = vDbTypes.GetVendorDbTypeStruct((EbDbTypes)_column.EbDbType), Label = _column.Control.Label + "_ebbkup" });

}

else if (_column.Control.DoNotPersist || _column.Control.IsSysControl)

continue;

else

{

_listNamesAndTypes.Add(new TableColumnMeta { Name = _column.ColumnName, Type = vDbTypes.GetVendorDbTypeStruct((EbDbTypes)_column.EbDbType), Label = _column.Control.Label, Control = _column.Control });

if (_column.Control is EbPhone _ebPhCtrl && _ebPhCtrl.Sendotp)

_listNamesAndTypes.Add(new TableColumnMeta { Name = _column.ColumnName + FormConstants._verified, Type = vDbTypes.Boolean, Default = "F", Label = _column.Control.Label + "_verified" });

else if (_column.Control is EbEmailControl _ebEmCtrl && _ebEmCtrl.Sendotp)

_listNamesAndTypes.Add(new TableColumnMeta { Name = _column.ColumnName + FormConstants._verified, Type = vDbTypes.Boolean, Default = "F", Label = _column.Control.Label + "_verified" });

if ((_column.Control is EbNumeric numCtrl && numCtrl.InputMode == NumInpMode.Currency) ||

(_column.Control is EbDGNumericColumn numCol && numCol.InputMode == NumInpMode.Currency))

CurrencyCtrlFound = true;

}

}

if (_table.TableName == _schema.MasterTable)

{

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_ver_id], Type = vDbTypes.Int32 });// id refernce to the parent table will store in this column - foreignkey

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_lock], Type = vDbTypes.GetVendorDbTypeStruct(ebs.GetDbType(SystemColumns.eb_lock)), Default = ebs.GetBoolFalse(SystemColumns.eb_lock, false), Label = "Lock ?" });// lock to prevent editing

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_push_id], Type = vDbTypes.String, Label = "Multi push id" });// multi push id - for data pushers

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_src_id], Type = vDbTypes.Int32, Label = "Source id" });// source id - for data pushers

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_src_ver_id], Type = vDbTypes.Int32, Label = "Source version id" });// source version id - for data pushers

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_ro], Type = vDbTypes.GetVendorDbTypeStruct(ebs.GetDbType(SystemColumns.eb_ro)), Default = ebs.GetBoolFalse(SystemColumns.eb_ro, false), Label = "Read Only?" });// Readonly

}

else

_listNamesAndTypes.Add(new TableColumnMeta { Name = _schema.MasterTable + "_id", Type = vDbTypes.Int32 });// id refernce to the parent table will store in this column - foreignkey

if (_table.TableType == WebFormTableTypes.Grid)

{

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_row_num], Type = vDbTypes.Int32 });// data grid row number

}

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_created_by], Type = vDbTypes.Int32, Label = "Created By" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_created_at], Type = vDbTypes.DateTime, Label = "Created At" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_lastmodified_by], Type = vDbTypes.Int32, Label = "Last Modified By" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_lastmodified_at], Type = vDbTypes.DateTime, Label = "Last Modified At" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_del], Type = vDbTypes.GetVendorDbTypeStruct(ebs.GetDbType(SystemColumns.eb_del)), Default = ebs.GetBoolFalse(SystemColumns.eb_del, false) });// delete

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_void], Type = vDbTypes.GetVendorDbTypeStruct(ebs.GetDbType(SystemColumns.eb_void)), Default = ebs.GetBoolFalse(SystemColumns.eb_void, false), Label = "Void ?" });// cancel //only ?

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_loc_id], Type = vDbTypes.Int16, Label = "Location" });// location id //only ?

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_signin_log_id], Type = vDbTypes.Int32, Label = "Log Id" });

//_listNamesAndTypes.Add(new TableColumnMeta { Name = "eb_default", Type = vDbTypes.Boolean, Default = "F" });

if (Form.CancelReason)

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_void_reason], Type = vDbTypes.String, Label = "Cancel Reason" });

if (CurrencyCtrlFound)

{

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_currency_id], Type = vDbTypes.Int32, Label = "Currency Id" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_currency_xid], Type = vDbTypes.String, Label = "Currency Xid" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_xrate1], Type = vDbTypes.Decimal, Label = "Xrate1" });

_listNamesAndTypes.Add(new TableColumnMeta { Name = ebs[SystemColumns.eb_xrate2], Type = vDbTypes.Decimal, Label = "Xrate1" });

}

int _rowaff = CreateOrAlterTable(_table.TableName, _listNamesAndTypes, ref Msg);

if (_table.TableName == _schema.MasterTable && !request.IsImport && (request.WebObj as EbWebForm).AutoDeployTV)

{

if (_schema.ExtendedControls.Find(e => e is EbReview) != null)

_listNamesAndTypes.Add(new TableColumnMeta { Name = "eb_approval", Label = "Approval" });

if (!request.IsImport)

CreateOrUpdateDsAndDv(request, _listNamesAndTypes);

}

}

}

if (!request.DontThrowException && !Msg.IsEmpty())

throw new FormException(Msg);

}

public int CreateOrAlterTable(string tableName, List<TableColumnMeta> listNamesAndTypes, ref string Msg)

{

int status = -1;

//checking for space in column name, table name

if (string.IsNullOrWhiteSpace(tableName) || tableName.Contains(CharConstants.SPACE))

throw new FormException("Table creation failed - Invalid table name: " + tableName);

foreach (TableColumnMeta entry in listNamesAndTypes)

if (entry.Name.Contains(CharConstants.SPACE))

throw new FormException("Table creation failed : Invalid column name" + entry.Name);

var isTableExists = this.EbConnectionFactory.DataDB.IsTableExists(this.EbConnectionFactory.DataDB.IS_TABLE_EXIST, new DbParameter[] { this.EbConnectionFactory.DataDB.GetNewParameter("tbl", EbDbTypes.String, tableName) });

if (!isTableExists)

{

string cols = string.Join(CharConstants.COMMA + CharConstants.SPACE.ToString(), listNamesAndTypes.Select(x => x.Name + CharConstants.SPACE + x.Type.VDbType.ToString() + (x.Default.IsNullOrEmpty() ? "" : (" DEFAULT '" + x.Default + "'"))).ToArray());

string sql = string.Empty;

if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.ORACLE)////////////

{

sql = "CREATE TABLE @tbl(id NUMBER(10), @cols)".Replace("@cols", cols).Replace("@tbl", tableName);

this.EbConnectionFactory.DataDB.CreateTable(sql);//Table Creation

CreateSquenceAndTrigger(tableName);

}

else if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.PGSQL)

{

sql = "CREATE TABLE @tbl( id SERIAL PRIMARY KEY, @cols)".Replace("@cols", cols).Replace("@tbl", tableName);

this.EbConnectionFactory.DataDB.CreateTable(sql);

}

else if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.MYSQL)

{

sql = "CREATE TABLE @tbl( id INTEGER AUTO_INCREMENT PRIMARY KEY, @cols)".Replace("@cols", cols).Replace("@tbl", tableName);

this.EbConnectionFactory.DataDB.CreateTable(sql);

}

status = 0;

}

else

{

var colSchema = this.EbConnectionFactory.DataDB.GetColumnSchema(tableName);

string sql = string.Empty;

foreach (TableColumnMeta entry in listNamesAndTypes)

{

bool isFound = false;

foreach (EbDataColumn dr in colSchema)

{

if (entry.Name.ToLower() == (dr.ColumnName.ToLower()))

{

if (entry.Type.EbDbType != dr.Type && !(

(entry.Type.EbDbType.ToString().Equals("Boolean") && dr.Type.ToString().Equals("String")) ||

(entry.Type.EbDbType.ToString().Equals("BooleanOriginal") && dr.Type.ToString().Equals("Boolean")) ||

(entry.Type.EbDbType.ToString().Equals("Decimal") && (dr.Type.ToString().Equals("Int32") || dr.Type.ToString().Equals("Int64"))) ||

(entry.Type.EbDbType.ToString().Equals("Int32") && dr.Type.ToString().Equals("Decimal")) ||

(entry.Type.EbDbType.ToString().Equals("Int16") && dr.Type.ToString().Equals("Int32")) ||

(entry.Type.EbDbType.ToString().Equals("DateTime") && dr.Type.ToString().Equals("Date")) ||

(entry.Type.EbDbType.ToString().Equals("Date") && dr.Type.ToString().Equals("DateTime")) ||

(entry.Type.EbDbType.ToString().Equals("Time") && dr.Type.ToString().Equals("DateTime"))

))

Msg += $"Type mismatch found '{dr.Type}' instead of '{entry.Type.EbDbType}' for {tableName}.{entry.Name}; ";

//Msg += string.Format("Already exists '{0}' Column for {1}.{2}({3}); ", dr.Type.ToString(), tableName, entry.Name, entry.Type.EbDbType);

isFound = true;

break;

}

}

if (!isFound)

{

sql += entry.Name + " " + entry.Type.VDbType.ToString() + " " + (entry.Default.IsNullOrEmpty() ? "" : (" DEFAULT '" + entry.Default + "'")) + ",";

}

}

bool appendId = false;

var existingIdCol = colSchema.FirstOrDefault(o => o.ColumnName.ToLower() == "id");

if (existingIdCol == null)

appendId = true;

if (!sql.IsEmpty() || appendId)

{

if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.ORACLE)/////////////////////////

{

sql = (appendId ? "id NUMBER(10)," : "") + sql;

if (!sql.IsEmpty())

{

sql = "ALTER TABLE @tbl ADD (" + sql.Substring(0, sql.Length - 1) + ")";

sql = sql.Replace("@tbl", tableName);

int _aff = this.EbConnectionFactory.DataDB.UpdateTable(sql);

if (appendId)

CreateSquenceAndTrigger(tableName);

}

}

else if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.PGSQL)

{

sql = (appendId ? "id SERIAL PRIMARY KEY," : "") + sql;

if (!sql.IsEmpty())

{

sql = "ALTER TABLE @tbl ADD COLUMN " + (sql.Substring(0, sql.Length - 1)).Replace(",", ", ADD COLUMN ");

sql = sql.Replace("@tbl", tableName);

this.EbConnectionFactory.DataDB.UpdateTable(sql);

}

}

else if (this.EbConnectionFactory.DataDB.Vendor == DatabaseVendors.MYSQL)

{

sql = (appendId ? "id INTEGER AUTO_INCREMENT PRIMARY KEY," : "") + sql;

if (!sql.IsEmpty())

{

sql = "ALTER TABLE @tbl ADD COLUMN " + (sql.Substring(0, sql.Length - 1)).Replace(",", ", ADD COLUMN ");

sql = sql.Replace("@tbl", tableName);

this.EbConnectionFactory.DataDB.UpdateTable(sql);

}

}

status = 1;

}

}

return status;

//throw new FormException("Table creation failed - Table name: " + tableName);

}

private void CreateSquenceAndTrigger(string tableName)

{

string sqnceSql = "CREATE SEQUENCE @name_sequence".Replace("@name", tableName);

string trgrSql = string.Format(@"CREATE OR REPLACE TRIGGER {0}_on_insert

BEFORE INSERT ON {0}

FOR EACH ROW

BEGIN

SELECT {0}_sequence.nextval INTO :new.id FROM dual;

END;", tableName);

this.EbConnectionFactory.DataDB.CreateTable(sqnceSql);//Sequence Creation

this.EbConnectionFactory.DataDB.CreateTable(trgrSql);//Trigger Creation

}

private void CreateOrUpdateDsAndDv(CreateWebFormTableRequest request, List<TableColumnMeta> listNamesAndTypes)

{

IEnumerable<TableColumnMeta> _list = listNamesAndTypes.Where(x => x.Name != "eb_del" && x.Name != "eb_ver_id" && !(x.Name.Contains("_ebbkup")) && x.Name != "eb_push_id" && x.Name != "eb_src_id" && x.Name != "eb_lock" && x.Name != "eb_signin_log_id" && !(x.Control is EbFileUploader) && x.Name != "eb_approval");

string cols = string.Join(CharConstants.COMMA + "\n \t ", _list.Select(x => x.Name).ToArray());

EbTableVisualization dv = null;

string AutogenId = (request.WebObj as EbWebForm).AutoGeneratedVizRefId;

if (AutogenId.IsNullOrEmpty())

{

var dsid = CreateDataReader(request, cols);

var dvrefid = CreateDataDataVisualization(request, listNamesAndTypes, dsid);

(request.WebObj as EbWebForm).AutoGeneratedVizRefId = dvrefid;

SaveFormObject(request);

}

else

{

dv = EbFormHelper.GetEbObject<EbTableVisualization>(AutogenId, null, Redis, this, this.PooledRedisManager);

UpdateDataReader(request, cols, dv, AutogenId);

UpdateDataVisualization(request, listNamesAndTypes, dv, AutogenId);

}

}

private string CreateDataReader(CreateWebFormTableRequest request, string cols)

{

EbDataReader drObj = new EbDataReader();

drObj.Sql = "SELECT \n \t id,@colname@ FROM @tbl \n WHERE eb_del='F'".Replace("@tbl", request.WebObj.TableName).Replace("@colname@", cols);

drObj.FilterDialogRefId = "";

drObj.Name = request.WebObj.Name + "_AutoGenDR";

drObj.DisplayName = request.WebObj.DisplayName + "_AutoGenDR";

drObj.Description = request.WebObj.Description;

return CreateNewObjectRequest(request, drObj);

}

private string CreateDataDataVisualization(CreateWebFormTableRequest request, List<TableColumnMeta> listNamesAndTypes, string dsid)

{

DVColumnCollection columns = GetDVColumnCollection(listNamesAndTypes, request);

var dvobj = new EbTableVisualization();

dvobj.Name = request.WebObj.Name + "_AutoGenDV";

dvobj.DisplayName = request.WebObj.DisplayName + " List";

dvobj.Description = request.WebObj.Description;

dvobj.DataSourceRefId = dsid;

dvobj.Columns = columns;

dvobj.DSColumns = columns;

dvobj.ColumnsCollection.Add(columns);

dvobj.NotVisibleColumns = columns.FindAll(x => !x.bVisible);

dvobj.AutoGen = true;

dvobj.OrderBy = new List<DVBaseColumn>();

dvobj.RowGroupCollection = new List<RowGroupParent>();

dvobj.OrderBy.Add(columns.Get("eb_created_at"));

SingleLevelRowGroup _rowgroup = new SingleLevelRowGroup();

_rowgroup.DisplayName = "By Location";

_rowgroup.Name = "groupbylocation";

_rowgroup.RowGrouping.Add(columns.Get("eb_loc_id"));

dvobj.RowGroupCollection.Add(_rowgroup);

_rowgroup = new SingleLevelRowGroup();

_rowgroup.DisplayName = "By Created By";

_rowgroup.Name = "groupbycreatedby";

_rowgroup.RowGrouping.Add(columns.Get("eb_created_by"));

dvobj.RowGroupCollection.Add(_rowgroup);

dvobj.BeforeSave(this, Redis);

return CreateNewObjectRequest(request, dvobj);

}

private string CreateNewObjectRequest(CreateWebFormTableRequest request, EbObject dvobj)

{

string _rel_obj_tmp = string.Join(",", dvobj.DiscoverRelatedRefids());

EbObject_Create_New_ObjectRequest ds1 = (new EbObject_Create_New_ObjectRequest

{

Name = dvobj.Name,

Description = dvobj.Description,

Json = EbSerializers.Json_Serialize(dvobj),

Status = ObjectLifeCycleStatus.Live,

IsSave = false,

Tags = "",

Apps = request.Apps,

SolnId = request.SolnId,

WhichConsole = request.WhichConsole,

UserId = request.UserId,

SourceObjId = "0",

SourceVerID = "0",

DisplayName = dvobj.DisplayName,

SourceSolutionId = request.SolnId,

Relations = _rel_obj_tmp

});

var myService = base.ResolveService<EbObjectService>();

var res = myService.Post(ds1);

return res.RefId;

}

private void UpdateDataReader(CreateWebFormTableRequest request, string cols, EbTableVisualization dv, string AutogenId)

{

dv.AfterRedisGet(Redis, this);

EbDataReader drObj = dv.EbDataSource;

drObj.Sql = "SELECT \n \t id,@colname@ FROM @tbl \n WHERE eb_del='F'".Replace("@tbl", request.WebObj.TableName).Replace("@colname@", cols);

drObj.FilterDialogRefId = "";

drObj.Name = request.WebObj.Name + "_AutoGenDR";

drObj.DisplayName = request.WebObj.DisplayName + "_AutoGenDR";

drObj.Description = request.WebObj.Description;

SaveObjectRequest(request, drObj);

}

private void UpdateDataVisualization(CreateWebFormTableRequest request, List<TableColumnMeta> listNamesAndTypes, EbTableVisualization dvobj, string AutogenId)

{

DVColumnCollection columns = UpdateDVColumnCollection(listNamesAndTypes, request, dvobj);

dvobj.Name = request.WebObj.Name + "_AutoGenDV";

dvobj.DisplayName = request.WebObj.DisplayName + " List";

dvobj.Description = request.WebObj.Description;

dvobj.Columns = columns;

dvobj.DSColumns = columns;

dvobj.ColumnsCollection[0] = columns;

dvobj.NotVisibleColumns = columns.FindAll(x => !x.bVisible);

UpdateOrderByObject(ref dvobj);

UpdateRowGroupObject(ref dvobj);

UpdateInfowindowObject(ref dvobj);

//notchecked for formlink, treeview, customcolumn

dvobj.BeforeSave(this, Redis);

SaveObjectRequest(request, dvobj);

}

private void SaveFormObject(CreateWebFormTableRequest request)

{

EbWebForm obj = request.WebObj as EbWebForm;

obj.BeforeSave(this);

SaveObjectRequest(request, obj);

}

private void SaveObjectRequest(CreateWebFormTableRequest request, EbObject obj)

{

string _rel_obj_tmp = string.Join(",", obj.DiscoverRelatedRefids());

EbObject_SaveRequest ds = new EbObject_SaveRequest

{

RefId = obj.RefId,

Name = obj.Name,

Description = obj.Description,

Json = EbSerializers.Json_Serialize(obj),

Relations = _rel_obj_tmp,

Tags = "",

Apps = request.Apps,

DisplayName = obj.DisplayName,

SolnId = request.SolnId

};

var myService = base.ResolveService<EbObjectService>();

EbObject_SaveResponse res = myService.Post(ds);

}

private DVColumnCollection GetDVColumnCollection(List<TableColumnMeta> listNamesAndTypes, CreateWebFormTableRequest request)

{

IVendorDbTypes vDbTypes = this.EbConnectionFactory.DataDB.VendorDbTypes;

var Columns = new DVColumnCollection();

int index = 0;

DVBaseColumn col = new DVNumericColumn { Data = index, Name = "id", sTitle = "id", Type = EbDbTypes.Decimal, bVisible = false, sWidth = "100px", ClassName = "tdheight" };

Columns.Add(col);

foreach (TableColumnMeta column in listNamesAndTypes)

{

if (column.Name != "eb_del" && column.Name != "eb_ver_id" && !(column.Name.Contains("_ebbkup")) && column.Name != "eb_push_id" && column.Name != "eb_src_id" && column.Name != "eb_lock" && column.Name != "eb_signin_log_id" && !(column.Control is EbFileUploader))

{

DVBaseColumn _col = null;

ControlClass _control = null;

bool _autoresolve = false;

Align _align = Align.Auto;

int charlength = 0;

index++;

EbDbTypes _RenderType = column.Type.EbDbType;

if (column.Control is EbPowerSelect)

{

_control = new ControlClass

{

DataSourceId = (column.Control as EbPowerSelect).DataSourceId,

ValueMember = (column.Control as EbPowerSelect).ValueMember

};

if ((column.Control as EbPowerSelect).RenderAsSimpleSelect)

{

_control.DisplayMember.Add((column.Control as EbPowerSelect).DisplayMember);

}

else

{

_control.DisplayMember = (column.Control as EbPowerSelect).DisplayMembers;

}

_autoresolve = true;

_align = Align.Center;

_RenderType = EbDbTypes.String;

}

else if (column.Control is EbTextBox)

{

if ((column.Control as EbTextBox).TextMode == TextMode.MultiLine)

{

charlength = 20;

}

}

else if (column.Name == "eb_void")

{

column.Type = vDbTypes.String;//T or F

_RenderType = EbDbTypes.Boolean;

}

else if (column.Name == "eb_created_by" || column.Name == "eb_lastmodified_by" || column.Name == "eb_loc_id")

{

_RenderType = EbDbTypes.String;

}

if (column.Name == "eb_approval")

{

_col = new DVApprovalColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = EbDbTypes.String,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

RenderType = EbDbTypes.String,

IsCustomColumn = true,

FormRefid = request.WebObj.RefId,

FormDataId = new List<DVBaseColumn> { col }

};

}

else if (_RenderType == EbDbTypes.String)

_col = new DVStringColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

ColumnQueryMapping = _control,

AutoResolve = _autoresolve,

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.Int16 || _RenderType == EbDbTypes.Int32 || _RenderType == EbDbTypes.Int64 || _RenderType == EbDbTypes.Double || _RenderType == EbDbTypes.Decimal || _RenderType == EbDbTypes.VarNumeric)

_col = new DVNumericColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

ColumnQueryMapping = _control,

AutoResolve = _autoresolve,

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.Boolean || _RenderType == EbDbTypes.BooleanOriginal)

_col = new DVBooleanColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.DateTime || _RenderType == EbDbTypes.Date || _RenderType == EbDbTypes.Time)

{

_col = new DVDateTimeColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

if (_RenderType == EbDbTypes.Time)

(_col as DVDateTimeColumn).Format = DateFormat.Time;

else if (_RenderType == EbDbTypes.DateTime)

(_col as DVDateTimeColumn).Format = DateFormat.DateTime;

}

Columns.Add(_col);

}

}

List<DVBaseColumn> _formid = new List<DVBaseColumn>() { col };

Columns.Add(new DVActionColumn

{

Data = (index + 1),//index+1 for serial column in datavis service

Name = "eb_action",

sTitle = "Action",

Type = EbDbTypes.String,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

LinkRefId = request.WebObj.RefId,

LinkType = LinkTypeEnum.Popout,

FormMode = WebFormDVModes.View_Mode,

FormId = _formid,

Align = Align.Center,

IsCustomColumn = true

});

return Columns;

}

private DVColumnCollection UpdateDVColumnCollection(List<TableColumnMeta> listNamesAndTypes, CreateWebFormTableRequest request, EbTableVisualization dv)

{

IVendorDbTypes vDbTypes = this.EbConnectionFactory.DataDB.VendorDbTypes;

var Columns = new DVColumnCollection();

int index = 0;

foreach (TableColumnMeta column in listNamesAndTypes)

{

DVBaseColumn _col = dv.Columns.Find(x => x.Name == column.Name);

if (_col == null && column.Name != "eb_del" && column.Name != "eb_ver_id" && !(column.Name.Contains("_ebbkup")) && column.Name != "eb_push_id" && column.Name != "eb_src_id" && column.Name != "eb_lock" && column.Name != "eb_signin_log_id" && !(column.Control is EbFileUploader))

{

index++;

ControlClass _control = null;

bool _autoresolve = false;

Align _align = Align.Auto;

int charlength = 0;

EbDbTypes _RenderType = column.Type.EbDbType;

if (column.Control is EbPowerSelect)

{

_control = new ControlClass

{

DataSourceId = (column.Control as EbPowerSelect).DataSourceId,

ValueMember = (column.Control as EbPowerSelect).ValueMember

};

if ((column.Control as EbPowerSelect).RenderAsSimpleSelect)

{

_control.DisplayMember.Add((column.Control as EbPowerSelect).DisplayMember);

}

else

{

_control.DisplayMember = (column.Control as EbPowerSelect).DisplayMembers;

}

_autoresolve = true;

_align = Align.Center;

_RenderType = EbDbTypes.String;

}

else if (column.Control is EbTextBox)

{

if ((column.Control as EbTextBox).TextMode == TextMode.MultiLine)

{

charlength = 20;

}

}

if (column.Name == "eb_approval")

{

_col = new DVApprovalColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = EbDbTypes.String,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

RenderType = EbDbTypes.String,

IsCustomColumn = true,

FormRefid = request.WebObj.RefId,

FormDataId = new List<DVBaseColumn> { dv.Columns.Get("id") }

};

}

else if (_RenderType == EbDbTypes.String)

_col = new DVStringColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

ColumnQueryMapping = _control,

AutoResolve = _autoresolve,

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.Int16 || _RenderType == EbDbTypes.Int32 || _RenderType == EbDbTypes.Int64 || _RenderType == EbDbTypes.Double || _RenderType == EbDbTypes.Decimal || _RenderType == EbDbTypes.VarNumeric)

_col = new DVNumericColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

ColumnQueryMapping = _control,

AutoResolve = _autoresolve,

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.Boolean || _RenderType == EbDbTypes.BooleanOriginal)

_col = new DVBooleanColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

else if (_RenderType == EbDbTypes.DateTime || _RenderType == EbDbTypes.Date || _RenderType == EbDbTypes.Time)

{

_col = new DVDateTimeColumn

{

Data = index,

Name = column.Name,

sTitle = column.Label,

Type = column.Type.EbDbType,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

Align = _align,

AllowedCharacterLength = charlength,

RenderType = _RenderType

};

if (_RenderType == EbDbTypes.Time)

(_col as DVDateTimeColumn).Format = DateFormat.Time;

else if (_RenderType == EbDbTypes.DateTime)

(_col as DVDateTimeColumn).Format = DateFormat.DateTime;

}

Columns.Add(_col);

}

else

{

if (_col != null)

{

if (column.Name == "eb_void")

{

column.Type = vDbTypes.String;//T or F

_col.RenderType = EbDbTypes.Boolean;

}

else if (column.Name == "eb_created_by" || column.Name == "eb_lastmodified_by" || column.Name == "eb_loc_id")

{

_col.RenderType = EbDbTypes.String;

}

else

{

if (_col.RenderType == EbDbTypes.Time)

(_col as DVDateTimeColumn).Format = DateFormat.Time;

else if (_col.RenderType == EbDbTypes.DateTime)

(_col as DVDateTimeColumn).Format = DateFormat.DateTime;

_col.RenderType = column.Type.EbDbType;

_col.Type = column.Type.EbDbType;

}

if (column.Control is EbPowerSelect)

{

var _control = new ControlClass

{

DataSourceId = (column.Control as EbPowerSelect).DataSourceId,

ValueMember = (column.Control as EbPowerSelect).ValueMember

};

if ((column.Control as EbPowerSelect).RenderAsSimpleSelect)

{

_control.DisplayMember.Add((column.Control as EbPowerSelect).DisplayMember);

}

else

{

_control.DisplayMember = (column.Control as EbPowerSelect).DisplayMembers;

}

_col.ColumnQueryMapping = _control;

_col.AutoResolve = true;

_col.Align = Align.Center;

_col.RenderType = EbDbTypes.String;

}

else if (column.Control is EbTextBox)

{

if ((column.Control as EbTextBox).TextMode == TextMode.MultiLine)

{

_col.AllowedCharacterLength = 20;

}

}

_col.Data = ++index;

Columns.Add(_col);

}

}

}

Columns.Add(dv.Columns.Get("id"));

DVBaseColumn Col = dv.Columns.Get("eb_action");

DVBaseColumn actcol = null;

if (Col == null || Col is DVStringColumn)

{

actcol = new DVActionColumn

{

Name = "eb_action",

sTitle = "Action",

Type = EbDbTypes.String,

bVisible = true,

sWidth = "100px",

ClassName = "tdheight",

LinkRefId = request.WebObj.RefId,

LinkType = LinkTypeEnum.Popout,

FormMode = WebFormDVModes.View_Mode,

FormId = new List<DVBaseColumn> { dv.Columns.Get("id") },

Align = Align.Center,

IsCustomColumn = true

};

}

else

actcol = Col;