public int CreateKBProject(KBProject proj)
{
if (proj == null)
{
throw new ArgumentNullException("KBProject Insert Model is Null");
}
int rows = 0;
List <ChartProperty> chartPropertyList;
try
{
_db.KBProjects.Add(proj);
rows = _db.SaveChanges();
chartPropertyList = _db.ChartProperties.Where(cp => cp.ChartId == proj.ChartId).ToList();
foreach (var item in chartPropertyList)
{
_db.ProjectMappings.Add(new ProjectMapping()
{
CPId = item.CPId,
ProjectId = proj.ProjectId
});
}
rows = _db.SaveChanges();
}
catch (Exception err)
{
throw err;
}
return(rows);
}
public ActionResult Save(string projName, string projSQL, string chartType, string connType)
{
KBProject project = new KBProject();
project.CId = Convert.ToInt32(connType);
project.ChartId = Convert.ToByte(chartType);
project.ProjectName = projName;
project.ProjectSQL = projSQL;
project.IsEnable = true;
JSONResult result = new JSONResult();
if (iProj.CreateKBProject(project) > 0)
{
result.Code = "1";
result.Message = SuccessMessage;
}
else
{
result.Code = "0";
result.Message = ErrorMessage;
}
return(Json(result));
}
public ActionResult GetProjectChartPropertyList(int id)
{
KBProject project = iProj.GetKBProjectById(id);
List <ProjectMapping> mappingList = iProjectMapping.GetProjectMappingsById(id);
List <ChartProperty> chartPropertiesList = iProperty.GetChartPropertyList(project.ChartId);
List <ChartPropertyProjectViewModel> chartPropProjList = new List <ChartPropertyProjectViewModel>();
ChartPropertyProjectViewModel chartProperty = null;
ProjectMapping mapping = null;
foreach (ChartProperty item in chartPropertiesList)
{
chartProperty = new ChartPropertyProjectViewModel();
chartProperty.CPId = item.CPId;
chartProperty.CPName = item.CPName;
mapping = mappingList.SingleOrDefault(mp => mp.CPId == item.CPId);
if (mapping != null)
{
chartProperty.Column = mapping.PropertyName;
}
chartPropProjList.Add(chartProperty);
}
var data = JsonConvert.SerializeObject(chartPropProjList, new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(Json(new { total = chartPropProjList.Count, rows = data }, JsonRequestBehavior.AllowGet));
}
public DataTable TestSQL(string sql, int projId)
{
//string conn = DBConnHelper.GetSQLConn();
ADONETReaderHelper readerHelper = new ADONETReaderHelper();
KBProject project = _db.KBProjects.SingleOrDefault(pj => pj.ProjectId == projId);
if (project == null)
{
return(new DataTable());
}
Conn conn = project.Conn;
if (conn == null)
{
return(new DataTable());
}
switch (conn.DBType)
{
//SQL SERVER
case "1":
return(readerHelper.ExecuteSQL(sql, conn.ConnStr, DBConnType.SQLServer));
//ORACLE
default:
return(readerHelper.ExecuteSQL(sql, conn.ConnStr, DBConnType.Oracle));
}
}
public ActionResult GetById(int id)
{
KBProject proj = iProj.GetKBProjectById(id);
return(PartialView("ProjEdit", proj));
//return Json(proj, JsonRequestBehavior.AllowGet);
}
public ActionResult GetProjectChartProperty(int id)
{
KBProject project = iProj.GetKBProjectById(id);
DataTable table = iProj.TestSQL(project.ProjectSQL, project.ProjectId);
List <SelectListItem> selectList = new List <SelectListItem>();
if (table != null && table.Rows.Count > 0)
{
selectList = ConvertDataColumnToSelectItemList(table.Columns);
}
return(Json(selectList, JsonRequestBehavior.AllowGet));
}
public string GetUrlById(KBProject proj)
{
KBProject projData = null;
projData = _db.KBProjects.SingleOrDefault(k => k.ProjectId == proj.ProjectId);
if (projData == null)
{
return(string.Empty);
}
return(projData.Url);
}
public int RemoveKBProjectById(int id)
{
KBProject proj = null;
int rows = 0;
//using (ConnContext db = new ConnContext())
//{
proj = _db.KBProjects.SingleOrDefault(c => c.ProjectId == id);
proj.IsEnable = false;
rows = _db.SaveChanges();
//}
return(rows);
}
public int UpdateUrlById(KBProject proj)
{
KBProject projData = null;
int rows = 0;
projData = _db.KBProjects.SingleOrDefault(k => k.ProjectId == proj.ProjectId);
if (projData != null && string.IsNullOrEmpty(projData.Url))
{
projData.Url = proj.Url;
rows = _db.SaveChanges();
}
return(rows);
}
public ActionResult Display(string id)
{
string json = string.Empty;
KBProject projData = iProj.GetProjectByURL(id);
DataTable table = iProj.TestSQL(projData.ProjectSQL, projData.ProjectId);
Lazy <C3JSLine> line = null;
Lazy <C3JSBar> bar = null;
switch (projData.Chart.ChartType)
{
case "Line":
ChartProperty chartPtyCatalog =
iChartProperty.GetChartPropertyByCatalog(projData.ChartId);
ChartProperty chartPtyData =
iChartProperty.GetChartPropertyByData(projData.ChartId);
//取得專案設定的Chart對應屬性
List <ProjectMapping> projMappingList =
iProjectMapping.GetProjectMappingsById(projData.ProjectId);
//取得project的catalog col name
ProjectMapping projMappingCatalog =
projMappingList.SingleOrDefault(pm => pm.CPId == chartPtyCatalog.CPId);
//取得project的data col name
ProjectMapping projMappingData =
projMappingList.SingleOrDefault(pm => pm.CPId == chartPtyData.CPId);
line = new Lazy <C3JSLine>(() => new C3JSLine(table, projData.Chart.ChartScript));
json = line.Value.Generate(
projMappingList.Where(pm => pm.CPId == projMappingData.CPId).Select(pmc => pmc.PropertyName).ToArray(),
projMappingCatalog.PropertyName);
break;
case "Bar":
//bar = new Lazy<C3JSBar>(() => new C3JSBar(table, projData.Chart.ChartScript));
//bar.Value.Generate()
break;
default:
break;
}
ViewData["Script"] = json;
return(View());
}
public KBProject UpdateKBProjectById(KBProject proj)
{
KBProject projData = null;
//using (ConnContext db = new ConnContext())
//{
projData = _db.KBProjects.SingleOrDefault(k => k.ProjectId == proj.ProjectId);
projData.ProjectName = proj.ProjectName;
projData.ProjectSQL = proj.ProjectSQL;
projData.CId = proj.CId;
projData.ChartId = proj.ChartId;
_db.SaveChanges();
return(projData);
//}
}
public ActionResult CreateURL(int id)
{
int rows = 0;
RandomString randomStr = new RandomString();
JSONResult result = new JSONResult();
KBProject project = new KBProject();
project.ProjectId = id;
string val;
string url = iProj.GetUrlById(project);
if (string.IsNullOrEmpty(url))
{
val = randomStr.GetRandomString();
project.Url = val;
try
{
rows = iProj.UpdateUrlById(project);
result.Code = "0";
result.Message = project.Url;
}
catch (Exception err)
{
result.Code = "-1";
result.Message = err.Message;
}
}
else
{
result.Code = "0";
result.Message = url;
}
result.ResponseData = string.Format("http://{0}{1}{2}",
Request.Url.Authority,
System.Web.Configuration.WebConfigurationManager.AppSettings["URL"],
url);
return(Json(result, JsonRequestBehavior.AllowGet));
}
public int RemoveConnById(int id)
{
Conn proj = null;
int rows = 0;
//using (ConnContext db = new ConnContext())
//{
KBProject kbProj = _db.KBProjects.SingleOrDefault(k => k.CId == id && k.IsEnable == true);
if (kbProj != null)
{
return(-1);
}
proj = _db.Conns.SingleOrDefault(c => c.CId == id);
proj.IsEnable = false;
rows = _db.SaveChanges();
//}
return(rows);
}
