public bool _ExportExportCustom(TM.Connection.Oracle Oracle, TM.Connection.OleDBF FoxPro, Common.DefaultObj obj, Models.EXPORT_CUSTOM EXPORT_CUSTOM)
{
try
{
//Remove Old File
TM.IO.FileDirectory.Delete($"{obj.DataSource}{EXPORT_CUSTOM.TABLE_NAME}.dbf", false);
//Create New File
FoxPro.Connection.Query(EXPORT_CUSTOM.QUERY_CREATE);
var data = Oracle.Connection.Query(EXPORT_CUSTOM.QUERY_SELECT.Replace("$kyhoadon", obj.KYHD.ToString())).ToList();
var qry = $"SELECT * FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}' AND TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM}";
var EXPORT_TABLE = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
var ColumnExport = new Dictionary <string, string>();
FoxPro.Connection.InsertList3(EXPORT_CUSTOM.TABLE_NAME, EXPORT_TABLE, data);
//Execute Query
FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}");
_ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_END);
//FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}");
//if (EXPORT_CUSTOM.QUERY_END != null)
//{
// var QUERY_END = EXPORT_CUSTOM.QUERY_END.Trim(';').Split(';');
// foreach (var i in QUERY_END)
// FoxPro.Connection.Query(i.Trim().TrimStart('\n').Replace("$table", EXPORT_CUSTOM.TABLE_NAME));
//}
//Delete .BAK
FileManagerController.RemoveFileSource(obj.DataSource, false);
return(true);
}
catch (Exception) { throw; }
}
public JsonResult CreateExportSave(ExportCustom obj, List <Models.EXPORT_TABLE> DataList)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
if (string.IsNullOrEmpty(obj.ExportName) && string.IsNullOrEmpty(obj.ExportTableName))
{
return(Json(new { danger = "Vui lòng nhập tên và tên bảng kết xuất!" }, JsonRequestBehavior.AllowGet));
}
var checkUpdate = true;
var tables = new List <string>();
var cols = new List <string>();
var qry = "";
var qry_table = "FROM ";
var qry_create = $"CREATE TABLE {obj.ExportTableName}(";//CreateTable(Common.Directories.HDData, ExportTableName, data);
var qry_select = "SELECT ";
var qry_insert = "INSERT INTO";
var qry_Condition = !string.IsNullOrEmpty(obj.Condition) ? $"WHERE {obj.Condition}" : "";
qry = $"SELECT * FROM EXPORT_CUSTOM WHERE LOWER(NAME)=N'{obj.ExportName.ToLower()}' AND TYPE_NAME='{obj.ExportType}'";
var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry);
if (EXPORT_CUSTOM == null)
{
EXPORT_CUSTOM = new Models.EXPORT_CUSTOM();
EXPORT_CUSTOM.ID = Guid.NewGuid().ToString("N");
EXPORT_CUSTOM.NAME = obj.ExportName;
EXPORT_CUSTOM.TABLE_NAME = obj.ExportTableName.ToLower();
checkUpdate = false;
}
EXPORT_CUSTOM.TABLE_LIST = ",";
//
if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.DEFAULT.ToString())
{
foreach (var i in DataList)
{
if (!tables.Contains(i.TABLE_NAME))
{
tables.Add(i.TABLE_NAME);
}
//Create
if (!cols.Contains(i.COLUMN_NAME_EXPORT))
{
cols.Add(i.COLUMN_NAME_EXPORT);
qry_create += $"[{i.COLUMN_NAME_EXPORT}] {i.COLUMN_TYPE_EXPORT}({i.COLUMN_LENGTH_EXPORT}),";
}
//Select
qry_select += $"{i.TABLE_NAME}.{i.COLUMN_NAME},";
//
i.APP_KEY = EXPORT_CUSTOM.ID;
i.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM;
i.COLUMN_TYPE = i.COLUMN_TYPE.ToLower();
i.COLUMN_TYPE_EXPORT = i.COLUMN_TYPE_EXPORT.ToLower();
i.ORDERS = index;
i.FLAG = 1;
index++;
}
foreach (var i in tables)
{
qry_table += $"{i},";
EXPORT_CUSTOM.TABLE_LIST += $"{i},";
}
//
qry_create = $"{qry_create.Trim(',')})";
qry_select = $"{qry_select.Trim(',')} {qry_table.Trim(',')} {qry_Condition}";
//
if (obj.chkUpdateQuery)
{
EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate;
EXPORT_CUSTOM.QUERY_SELECT = obj.ExportQuerySelect;
//EXPORT_CUSTOM.QUERY_INSERT = ExportQueryInsert;
EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd;
}
else
{
EXPORT_CUSTOM.QUERY_CREATE = qry_create;
EXPORT_CUSTOM.QUERY_SELECT = qry_select;
EXPORT_CUSTOM.QUERY_INSERT = qry_insert;
}
}
else if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.MODULES.ToString())
{
EXPORT_CUSTOM.TABLE_LIST = !string.IsNullOrEmpty(obj.ExportTableList) ? ("," + obj.ExportTableList.Trim().Trim(',') + ",") : "";
EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate;
EXPORT_CUSTOM.QUERY_SELECT = ""; // qry_select;
EXPORT_CUSTOM.QUERY_INSERT = ""; // qry_insert;
EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd;
}
else
{
return(Json(new { danger = "Sai định dạng kết suất, Vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet));
}
EXPORT_CUSTOM.TYPE_NAME = obj.ExportType.ToString();
EXPORT_CUSTOM.CONDITION = obj.Condition;
EXPORT_CUSTOM.FLAG = 1;
if (checkUpdate)
{
Oracle.Connection.Update(EXPORT_CUSTOM);
//Remove EXPORT_TABLE Old
qry = $"DELETE FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}'";
Oracle.Connection.Query(qry);
}
else
{
Oracle.Connection.Insert(EXPORT_CUSTOM);
}
//var x = new Models.TEST();
//x.ID = Guid.NewGuid().ToString("N");
//x.NAME = "tuanmjnh";
//x.LEVELS = 2;
//x.MONEY = 3.5M;
//x.TIME = DateTime.Now;
//Oracle.Connection.Insert(x);
//Insert EXPORT_TABLE New
if (DataList != null && DataList.Count > 0)
{
Oracle.Connection.Insert(DataList);
}
return(Json(new { data = DataList, success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}