public override XVar db_gettablelist()
{
XVar ret = XVar.Array();
var dbcon = ((System.Data.OleDb.OleDbConnection)conn.connect().Value);
try
{
dbcon.Open();
System.Data.DataTable schemaTable = dbcon.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
foreach (System.Data.DataRow row in schemaTable.Rows)
{
if (row["TABLE_TYPE"].ToString() == "TABLE" || row["TABLE_TYPE"].ToString() == "VIEW")
{
ret.Add(row["TABLE_NAME"].ToString());
}
}
}
finally
{
dbcon.Close();
}
return(ret);
}
public override XVar db_gettablelist()
{
XVar ret = XVar.Array();
var dbcon = ((System.Data.Odbc.OdbcConnection)conn.connect().Value);
try
{
dbcon.Open();
System.Data.DataTable schemaTable = dbcon.GetSchema("Tables");
foreach (System.Data.DataRow row in schemaTable.Rows)
{
String schema = row["TABLE_SCHEM"].ToString(),
table = row["TABLE_NAME"].ToString(),
type = row["TABLE_TYPE"].ToString();
if (schema != "")
{
table = schema + "." + table;
}
if (type == "TABLE" || type == "VIEW")
{
ret.Add(table);
}
}
}
finally
{
dbcon.Close();
}
return(ret);
}
public static XVar getImportExcelFields(XVar data)
{
XVar fields = XVar.Array();
if (data == null)
{
return(fields);
}
try
{
ExcelPackage xlsPack = data.Value as ExcelPackage;
var worksheet = xlsPack.Workbook.Worksheets[1];
int cellIndex = 1;
while (worksheet.Cells[1, cellIndex].Value != null)
{
fields.Add(XVar.Pack(worksheet.Cells[1, cellIndex].Value));
cellIndex++;
}
}
catch (Exception ex)
{
MVCFunctions.import_error_handler(ex);
}
return(fields);
}
// not yet implemented
public static XVar getPreviewDataFromExcel(XVar fileHandle, XVar fieldsData)
{
XVar previewData = XVar.Array();
XVar tableData = XVar.Array();
var remainNumOfPreviewRows = 100;
try
{
ExcelPackage xlsPack = fileHandle.Value as ExcelPackage;
foreach (var record in IterateSheetPreview(xlsPack))
{
if (remainNumOfPreviewRows < 0)
{
break;
}
XVar row = XVar.Array();
foreach (KeyValuePair <XVar, dynamic> field in record.GetEnumerator())
{
row.Add(field.Value);
}
tableData.Add(row);
remainNumOfPreviewRows--;
}
}
catch (Exception ex)
{
MVCFunctions.import_error_handler(ex);
}
previewData["tableData"] = tableData;
return(previewData);
}
public static XVar parceCSVLine(XVar line, XVar delimiter, XVar removeBOM = null)
{
var linestr = line.ToString();
if (linestr.Length < 3)
{
return(false);
}
if (linestr.Substring(0, 3) == "\xEF\xBB\xBF")
{
linestr = linestr.Substring(3);
}
CsvReader csvRdr = new CsvReader(new StringReader(linestr), false, delimiter.ToString()[0]);
try
{
if (csvRdr.ReadNextRecord())
{
XVar row = XVar.Array();
for (int i = 0; i < csvRdr.FieldCount; i++)
{
row.Add(csvRdr[i]);
}
return(row);
}
}
catch (Exception ex)
{
return(false);
}
return(false);
}
/// <summary>
/// Returns list of table names
/// </summary>
/// <returns></returns>
public XVar GetTablesList()
{
this.FillTablesList();
XVar res = XVar.Array();
foreach (var tbl in this.lstTables.GetEnumerator())
{
res.Add(tbl.Value["name"]);
}
return(res);
}
public override XVar db_gettablelist()
{
XVar ret = XVar.Array();
XVar strSQL = "sp_tables";
var rs = conn.query(strSQL);
XVar data;
while (data = rs.fetchAssoc())
{
if (data["TABLE_OWNER"].ToUpper() != "SYS" && data["TABLE_OWNER"].ToUpper() != "INFORMATION_SCHEMA")
{
ret.Add(MVCFunctions.Concat(data["TABLE_OWNER"], ".", data["TABLE_NAME"]));
}
}
return(ret);
}
public XVar GetDBTableKeys()
{
if (!GlobalVars.dal_info.KeyExists(this.m_infoKey) || !MVCFunctions.is_array(GlobalVars.dal_info[this.m_infoKey]))
{
return(XVar.Array());
}
XVar ret = XVar.Array();
foreach (var f in GlobalVars.dal_info[this.m_infoKey].GetEnumerator())
{
if (f.Value["key"] != null)
{
ret.Add(f.Key);
}
}
return(ret);
}
public static XVar getImportCVSFields(XVar uploadfile)
{
XVar csvFields = XVar.Array();
try
{
CsvReader csvRdr = new CsvReader(new StreamReader((uploadfile.Value as HttpPostedFile).InputStream), true);
foreach (var header in csvRdr.GetFieldHeaders())
{
csvFields.Add(header);
}
}
catch (Exception ex)
{
MVCFunctions.import_error_handler(ex);
}
return(csvFields);
}
public static XVar getFileNamesFromDir(XVar dirPath)
{
XVar names = XVar.Array();
var dirPathStr = dirPath.ToString().Replace('/', '\\');
if (!System.IO.Directory.Exists(dirPathStr))
{
return(names);
}
foreach (var file in System.IO.Directory.EnumerateFiles(dirPathStr))
{
names.Add(file);
}
return(names);
}
public static IEnumerable <XVar> IterateSheetPreview(ExcelPackage xlsPack)
{
foreach (var worksheet in xlsPack.Workbook.Worksheets)
{
for (int row = 1; row <= worksheet.Dimension.End.Row; ++row)
{
XVar arr = XVar.Array();
for (int i = 0; i < worksheet.Dimension.End.Column; i++)
{
var cell = worksheet.Cells[row, i + 1];
XVar val = null;
if (cell != null && cell.Value != null)
{
if (cell.Value is DateTime) // sometimes date stored in a DateTime
{
val = ((DateTime)cell.Value).ToString("yyyy-MM-dd H:mm:ss");
}
else if (Regex.IsMatch(cell.Style.Numberformat.Format, ("([ymdHis])"))) // sometimes date stored in a weird number
{
cell.Style.Numberformat.Format = "yyyy-MM-dd H:mm:ss";
val = cell.Text;
}
else if (cell.IsRichText)
{
val = cell.RichText.Text;
}
else
{
val = new XVar(cell.Value.ToString());
}
}
arr.Add(MVCFunctions.runner_htmlspecialchars(val));
}
yield return(arr);
}
}
}
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 static IEnumerable <XVar> IterateSheet(ExcelPackage xlsPack, XVar fieldsData, bool useFirstLine)
{
XVar previewData = null;
foreach (var worksheet in xlsPack.Workbook.Worksheets)
{
for (int row = (useFirstLine? 1 : 2); row <= worksheet.Dimension.End.Row; ++row)
{
XVar arr = XVar.Array();
for (int i = 0; i < worksheet.Dimension.End.Column; i++)
{
if (!fieldsData.KeyExists(i))
{
continue;
}
var cell = worksheet.Cells[row, i + 1];
XVar val = null;
if (cell != null && cell.Value != null)
{
bool dateCorrectlyExtracted = false;
if (cell.Value is DateTime) // sometimes date stored in a DateTime
{
val = ((DateTime)cell.Value).ToString("yyyy-MM-dd H:mm:ss");
dateCorrectlyExtracted = true;
}
else if (Regex.IsMatch(cell.Style.Numberformat.Format, ("([ymdHis])"))) // sometimes date stored in a weird number
{
cell.Style.Numberformat.Format = "yyyy-MM-dd H:mm:ss";
val = cell.Text;
dateCorrectlyExtracted = true;
}
else if (cell.IsRichText)
{
val = cell.RichText.Text;
}
else
{
val = new XVar(cell.Value.ToString());
}
// the following code block looks suspicious (!)
if (fieldsData.KeyExists(i) && fieldsData[i]["dateTimeType"] && // column should have date value,
!dateCorrectlyExtracted && // but in excel it looks awfull
(row != 0 || !useFirstLine)) // also we still dont know actual format and it is not header row
{
previewData = XVar.Array();
// so try to guess format from the value
previewData["dateFormat"] = ImportPage.extractDateFormat(val);
}
}
if (previewData != null)
{
arr.Add(MVCFunctions.runner_htmlspecialchars(val));
}
else
{
arr[fieldsData[i]["fName"]] = val;
}
}
yield return(arr);
}
}
}
