virtual public void db_multipleInsertQuery(XVar qstringArray, XVar table = null, XVar isIdentityOffNeeded = null)
{
DbConnection connection = connectionsPool.FreeConnection;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
DbCommand cmd = GetCommand();
cmd.Connection = connection;
try
{
foreach (var qstring in qstringArray.GetEnumerator())
{
if (GlobalVars.dDebug)
{
MVCFunctions.EchoToOutput(qstring.Value.ToString() + "<br />");
}
GlobalVars.strLastSQL = qstring.Value;
cmd.CommandText = qstring.Value.ToString();
cmd.ExecuteNonQuery();
}
}
finally
{
connection.Close();
}
}
/// <summary>
/// Delete a record from the table
/// </summary>
/// <returns></returns>
public void Delete()
{
XVar deleteFields = "";
XVar tableinfo = GlobalVars.dal_info[this.m_infoKey];
// prepare parameters
foreach (var f in tableinfo.GetEnumerator())
{
var fieldInfo = this.GetType().GetField(f.Value["varname"]);
if (fieldInfo != null && fieldInfo.GetValue(this) != null)
{
this.Param[f.Key] = fieldInfo.GetValue(this);
}
foreach (var paramValue in this.Param.GetEnumerator())
{
if (MVCFunctions.strtoupper(paramValue.Key) != MVCFunctions.strtoupper(f.Key))
{
continue;
}
deleteFields = MVCFunctions.Concat(deleteFields, _connection.addFieldWrappers(f.Key), "=", this.PrepareValue(paramValue.Value, f.Value["type"]), " and ");
break;
}
}
// do delete
if (deleteFields)
{
deleteFields = MVCFunctions.substr(deleteFields, 0, -5);
XVar dalSQL = MVCFunctions.Concat("delete from ", _connection.addTableWrappers(this.m_TableName), " where ", deleteFields);
_connection.exec(dalSQL);
}
// cleanup
this.Reset();
}
/// <summary>
/// Changes php arrays into xml text recursively
/// </summary>
/// <param name="arg_arr_array">the array to be changed into XML</param>
/// <param name="arg_int_pad_number">the number of pads of the current tag</param>
/// <param name="arg_str_pad">the indentation pad text</param>
/// <returns>xml text</returns>
private object[] prv_array_to_xml(XVar arg_arr_array)
{
List <XElement> elements = new List <XElement>();
foreach (var item in arg_arr_array.GetEnumerator())
{
XElement xElement = new XElement("attr");
xElement.Add(new XAttribute("value", item.Key.ToString()));
if ((item.Value as XVar).IsArray())
{
xElement.Add(prv_array_to_xml(item.Value));
}
else
{
if (item.Value is bool || item.Value is XVar && item.Value.Value is bool)
{
xElement.Value = ((bool)item.Value) ? "true" : "false";
}
else
{
xElement.Value = item.Value;
}
}
elements.Add(xElement);
}
return(elements.ToArray());
}
public XVar FetchByID()
{
XVar tableinfo = GlobalVars.dal_info[this.m_infoKey];
XVar dal_where = "";
foreach (var f in tableinfo.GetEnumerator())
{
var fieldInfo = this.GetType().GetField(f.Value["varname"]);
if (fieldInfo != null && fieldInfo.GetValue(this) != null)
{
this.Value[f.Value["varname"]] = fieldInfo.GetValue(this);
}
foreach (var fieldValue in this.Param.GetEnumerator())
{
if (MVCFunctions.strtoupper(fieldValue.Key) != MVCFunctions.strtoupper(f.Key))
{
continue;
}
dal_where = MVCFunctions.Concat(_connection.addFieldWrappers(f.Key), "=", this.PrepareValue(fieldValue.Value, f.Value["type"]), " and ");
break;
}
}
// cleanup
this.Reset();
// construct and run SQL
if (dal_where)
{
dal_where = MVCFunctions.Concat(" where ", MVCFunctions.substr(dal_where, 0, -5));
}
XVar dalSQL = MVCFunctions.Concat("select * from ", _connection.addTableWrappers(this.m_TableName), dal_where);
return(_connection.query(dalSQL).getQueryHandle());
}
public static XVar DBLookup(XVar sql)
{
var connection = CommonFunctions.getDefaultConnection();
XVar data = connection.query(sql).fetchAssoc();
if (data)
{
return(data.GetEnumerator().First().Value); // return first value
}
return(null);
}
public static XVar ExportExcelInit(XVar arrData, XVar arrWidth)
{
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sheet1");
int cellIndex = 1;
foreach (var cell in arrData.GetEnumerator())
{
ws.Cells[1, cellIndex].Value = cell.Value;
cellIndex++;
}
return(new XVar("excelObj", pck, "rowIndex", 1));
}
public static void ExportExcelTotals(XVar arrTotal, XVar arrTotalMessage, XVar iNumberOfRows, XVar objPHPExcel)
{
ExcelWorksheet ws = ((ExcelPackage)objPHPExcel["excelObj"].Value).Workbook.Worksheets["Sheet1"];
objPHPExcel["rowIndex"]++;
int rowIndex = objPHPExcel["rowIndex"];
int cellIndex = 1;
foreach (var cell in arrTotal.GetEnumerator())
{
ws.Cells[rowIndex, cellIndex].Value = (( XVar)arrTotalMessage[cell.Key]).Value + (( XVar)cell.Value).Value.ToString();
cellIndex++;
}
}
/// <summary>
/// Reset table object
/// </summary>
public void Reset()
{
this.Value = XVar.Array();
this.Param = XVar.Array();
XVar tableinfo = GlobalVars.dal_info[this.m_infoKey];
// prepare parameters
foreach (var f in tableinfo.GetEnumerator())
{
var fieldInfo = this.GetType().GetField(f.Value["varname"]);
if (fieldInfo != null)
{
fieldInfo.SetValue(this, null);
}
}
}
/// <summary>
/// Add new record to the table
/// </summary>
public void Add()
{
XVar insertFields = "";
XVar insertValues = "";
XVar tableinfo = GlobalVars.dal_info[this.m_infoKey];
XVar blobs = XVar.Array();
// prepare parameters
foreach (var f in tableinfo.GetEnumerator())
{
var fieldInfo = this.GetType().GetField(f.Value["varname"]);
if (fieldInfo != null && fieldInfo.GetValue(this) != null)
{
this.Value[f.Key] = fieldInfo.GetValue(this);
}
foreach (var fieldValue in this.Value.GetEnumerator())
{
if (MVCFunctions.strtoupper(fieldValue.Key) != MVCFunctions.strtoupper(f.Key))
{
continue;
}
insertFields = MVCFunctions.Concat(insertFields, _connection.addFieldWrappers(f.Key), ",");
insertValues = MVCFunctions.Concat(insertValues, this.PrepareValue(fieldValue.Value, f.Value["type"]), ",");
if (_connection.dbType == Constants.nDATABASE_Oracle)
{
if (CommonFunctions.IsBinaryType(f.Value["type"]))
{
blobs[fieldValue.Key] = fieldValue.Value;
}
}
break;
}
}
// prepare and exec SQL
if (insertFields != "" && insertValues != "")
{
insertFields = MVCFunctions.substr(insertFields, 0, -1);
insertValues = MVCFunctions.substr(insertValues, 0, -1);
XVar dalSQL = MVCFunctions.Concat("insert into ", _connection.addTableWrappers(this.m_TableName), " (", insertFields, ") values (", insertValues, ")");
this.Execute_Query(blobs, dalSQL, tableinfo);
}
// cleanup
this.Reset();
}
public static void ExportExcelRecord(XVar arrdata, XVar datatype, XVar row, XVar objPHPExcel, dynamic pageObj)
{
XVar rowObj = new XVar("data", arrdata, "datatype", datatype);
objPHPExcel.Add(rowObj);
int cellIndex = 1;
ExcelWorksheet ws = ((ExcelPackage)objPHPExcel["excelObj"].Value).Workbook.Worksheets["Sheet1"];
objPHPExcel["rowIndex"]++;
int rowIndex = objPHPExcel["rowIndex"];
foreach (var cell in arrdata.GetEnumerator())
{
if (datatype[cell.Key] == "binary")
{
if (cell.Value == null)
{
continue;
}
Image img = Image.FromStream(new MemoryStream((byte[])cell.Value.Value));
InsertImage(img, ws, cellIndex, rowIndex);
}
else if (datatype[cell.Key] == "file")
{
if (!cell.Value || !File.Exists(pageObj.pSet.getUploadFolder(cell.Key) + cell.Value.ToString()))
{
continue;
}
Image img = Image.FromFile(pageObj.pSet.getUploadFolder(cell.Key) + cell.Value.ToString());
InsertImage(img, ws, cellIndex, rowIndex);
}
else
{
// try to get correct cell type
var obj = ((XVar)cell.Value).Value;
var type = pageObj.pSet.getFieldType(cell.Key);
if (CommonFunctions.IsDateFieldType(type))
{
ws.Cells[objPHPExcel["rowIndex"], cellIndex].Style.Numberformat.Format = GlobalVars.locale_info["LOCALE_SSHORTDATE"].ToString()
+ (CommonFunctions.IsDateTimeFieldType(type) ? " hh:mm:ss" : "");
}
else if (CommonFunctions.IsNumberType(type) && obj is string)
{
double objDouble = 0;
int objInt = 0;
if (CommonFunctions.IsFloatType(type) && double.TryParse(obj as string, out objDouble))
{
obj = objDouble;
}
else if (int.TryParse(obj as string, out objInt))
{
obj = objInt;
}
}
ws.Cells[objPHPExcel["rowIndex"], cellIndex].Value = obj;
}
cellIndex++;
}
}
public override void db_multipleInsertQuery(XVar qstringArray, XVar table = null, XVar isIdentityOffNeeded = null)
{
try
{
DbConnection connection = connectionsPool.FreeConnection;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
DbCommand cmd = GetCommand();
cmd.Connection = connection;
if (isIdentityOffNeeded)
{
cmd.CommandText = "SET IDENTITY_INSERT " + table.ToString() + " ON";
cmd.Prepare();
cmd.ExecuteNonQuery();
}
foreach (var qstring in qstringArray.GetEnumerator())
{
if (GlobalVars.dDebug)
{
MVCFunctions.EchoToOutput(qstring.Value.ToString() + "<br />");
}
GlobalVars.strLastSQL = qstring.Value;
cmd.CommandText = qstring.Value.ToString();
cmd.ExecuteNonQuery();
}
if (isIdentityOffNeeded)
{
cmd.CommandText = "SET IDENTITY_INSERT " + table.ToString() + " OFF";
cmd.Prepare();
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = "select @@IDENTITY as indent";
cmd.Prepare();
RunnerDBReader rdr = cmd.ExecuteReader();
rdr.Connection = cmd.Connection;
if (rdr.Read())
{
lastInsertedID = new XVar(rdr["indent"]);
}
}
connection.Close();
}
catch (Exception e)
{
if (!silentMode)
{
if (!MVCFunctions.HandleError())
{
throw e;
}
}
}
}
/// <summary>
/// Update record in the table
/// </summary>
public void Update()
{
XVar tableinfo = GlobalVars.dal_info[this.m_infoKey];
XVar updateParam = "";
XVar updateValue = "";
XVar blobs = XVar.Array();
foreach (var f in tableinfo.GetEnumerator())
{
var fieldInfo = this.GetType().GetField(f.Value["varname"]);
if (fieldInfo != null && fieldInfo.GetValue(this) != null)
{
if (f.Value["key"] != null)
{
this.Param[f.Value["varname"]] = fieldInfo.GetValue(this);
}
else
{
this.Value[f.Value["varname"]] = fieldInfo.GetValue(this);
}
}
if (f.Value["key"] == null && !MVCFunctions.array_change_key_case(this.Param, Constants.CASE_UPPER).KeyExists(MVCFunctions.strtoupper(f.Key)))
{
foreach (var fieldValue in this.Value.GetEnumerator())
{
if (MVCFunctions.strtoupper(fieldValue.Key) != MVCFunctions.strtoupper(f.Key))
{
continue;
}
updateValue = MVCFunctions.Concat(updateValue, _connection.addFieldWrappers(f.Key), "=", this.PrepareValue(fieldValue.Value, f.Value["type"]), ", ");
if (_connection.dbType == Constants.nDATABASE_Oracle)
{
if (CommonFunctions.IsBinaryType(f.Value["type"]))
{
blobs[f.Key] = fieldValue.Value;
}
}
break;
}
}
else
{
foreach (var fieldValue in this.Param.GetEnumerator())
{
if (MVCFunctions.strtoupper(fieldValue.Key) != MVCFunctions.strtoupper(f.Key))
{
continue;
}
updateParam = MVCFunctions.Concat(updateParam, _connection.addFieldWrappers(f.Key), "=", this.PrepareValue(fieldValue.Value, f.Value["type"]), " and ");
break;
}
}
}
// construct SQL and do update
if (updateParam)
{
updateParam = MVCFunctions.substr(updateParam, 0, -5);
}
if (updateValue)
{
updateValue = MVCFunctions.substr(updateValue, 0, -2);
}
if (updateValue && updateParam)
{
XVar dalSQL = MVCFunctions.Concat("update ", _connection.addTableWrappers(this.m_TableName), " set ", updateValue, " where ", updateParam);
this.Execute_Query(blobs, dalSQL, tableinfo);
}
// cleanup
this.Reset();
}
