/// <summary>
/// 交付物责任人列表
/// Created:20170526(ChengMengjia)
/// </summary>
/// <param name="jbxxID"></param>
/// <returns></returns>
public List <DeliverablesWork> GetManagerWorks(string jbxxID)
{
if (string.IsNullOrEmpty(jbxxID))
{
return(new List <DeliverablesWork>());
}
StringBuilder sql = new StringBuilder();
sql.Append("select a.*,b.Name ManagerName from DeliverablesWork a inner join Stakeholders b on substr(a.Manager,1,36)=substr(b.ID,1,36) and b.status=1 ");
sql.Append(" where a.JBXXID=@JBXXID and a.Status=@Status");
List <QueryField> qf = new List <QueryField>();
qf.Add(new QueryField()
{
Name = "JBXXID", Type = QueryFieldType.String, Value = jbxxID.Substring(0, 36)
});
qf.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
List <DeliverablesWork> list = JsonHelper.TableToList <DeliverablesWork>(dt);
return(list);
}
protected void Application_BeginRequest(object sender, EventArgs e)
{
//Check if db connectionstring is present
if (String.IsNullOrEmpty(TenantContext.TenantKey) || !DBConnectionStringIsPresentInWebConfig(TenantContext.TenantKey))
{
return;
}
// Should setup the db the first time it's hit
if (SetupDBExecuted.Find(n => n == TenantContext.TenantKey) == null)
{
SetupDB();
InsertResources();
SetupDBExecuted.Add(TenantContext.TenantKey);
}
if (!SetupDBExecuted.Contains(TenantContext.TenantKey))
{
InsertResources();
SetupDBExecuted.Add(TenantContext.TenantKey);
}
ISession session = NHHelper.GetSessionFactoryFor(TenantContext.TenantKey).OpenSession();
CurrentSessionContext.Bind(session);
}
/// <summary>
/// 保存已经赋值的PNodeList
/// </summary>
/// <param name="PNodeList"></param>
/// <returns></returns>
public JsonResult SavePNodeList(List <PNode> PNodeList)
{
JsonResult jsonreslut = new JsonResult();
ISession s = NHHelper.GetCurrentSession();;
s.BeginTransaction();
try
{
PNodeList = PNodeList.OrderBy(t => t.ParentID).ThenBy(t => t.No).ToList();
if (PNodeList != null && PNodeList.Count != 0)
{
foreach (var item in PNodeList)
{
s.Update(item);
}
}
jsonreslut.result = true;
jsonreslut.msg = "操作成功！";
}
catch (Exception ex)
{
LogHelper.WriteException(ex, LogType.BussinessDLL);
s.Transaction.Rollback();
jsonreslut.result = false;
jsonreslut.msg = "操作失败！";
}
s.Transaction.Commit();
s.Close();
return(jsonreslut);
}
public DataTable getExcel()
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
List <QueryField> qf = new List <QueryField>();
sql.Append("select * from low_value ");
dt = NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0];
return(dt);
}
public void Init()
{
_currentSession = NHHelper.GetSessionFactoryFor("Default").OpenSession();
_tx = _currentSession.BeginTransaction();
_currentSession.CreateQuery("DELETE FROM Resource").ExecuteUpdate();
DefaultResourceCreation resourceCreation = new DefaultResourceCreation(_currentSession);
resourceCreation.InsertOrUpdate();
//currentSession.Flush();
}
private void InsertResources()
{
ISession session = NHHelper.GetSessionFactoryFor(TenantContext.TenantKey).OpenSession();
var resourceCreation =
ObjectFactory.With("currentSession").EqualTo(session).GetInstance <NHResourceCreation>(TenantContext.TenantKey);
resourceCreation.InsertOrUpdate();
session.Flush();
session.Close();
}
static SetupDBFixtureTest()
{
//Create Schema File
NHHelper.CreateShemaExportFile("sqlSchemaScript.sql", "Default");
CreateDb script = new CreateDb(sqlRootFolderPath, dbName, serverName, "sqlSchemaScript.sql");
//script.Execute("Default");
server = new Server(serverName);
}
public void CanGetSessioFactoryForTenant()
{
// Act
ISessionFactory sessionFactory = NHHelper.GetSessionFactoryFor("Default");
// Assert
ISession session = sessionFactory.OpenSession();
var resources = session.QueryOver <Resource>().List();
Assert.IsNotNull(resources);
}
public Repository()
{
try
{
_session = NHHelper.GetSessionFactoryFor(TenantContext.TenantKey).GetCurrentSession();
}
catch (NullReferenceException)
{
_session = NHHelper.GetSessionFactoryFor(TenantContext.TenantKey).OpenSession();
}
_trx = _session.BeginTransaction();
}
public void FixtureSetup()
{
var nhConfig = ConfigurationExtensions.GetNhConfig();
mockSolr = MockRepository.GenerateMock <ISolrOperations <Entity> >();
var provider = MockRepository.GenerateMock <IServiceProvider>();
var mapper = MockRepository.GenerateMock <IReadOnlyMappingManager>();
provider.Expect(x => x.GetService(typeof(IReadOnlyMappingManager))).Return(mapper);
NHHelper.SetListener(nhConfig, GetSolrNetListener(mockSolr));
new SchemaExport(nhConfig).Execute(false, true, false);
sessionFactory = nhConfig.BuildSessionFactory();
}
/// <summary>
/// Registers SolrNet's NHibernate listeners
/// </summary>
/// <param name="config">NHibernate configuration</param>
/// <param name="autoCommit">if set to <c>true</c> [auto commit].</param>
/// <param name="parameters">The add parameters to use when adding a document to the index.</param>
/// <returns></returns>
public Configuration Configure(Configuration config, bool autoCommit, AddParameters parameters)
{
foreach (var t in mapper.GetRegisteredTypes())
{
var listenerType = typeof(SolrNetListener <>).MakeGenericType(t);
var solrType = typeof(ISolrOperations <>).MakeGenericType(t);
var solr = provider.GetService(solrType);
var listener = (IListenerSettings)Activator.CreateInstance(listenerType, solr);
listener.Commit = autoCommit;
listener.AddParameters = parameters;
NHHelper.SetListener(config, listener);
}
return(config);
}
private void SetupDB()
{
// Setup DB
string sqlRootFolder = ConfigurationManager.AppSettings["SqlRootFolder"];
string sqlServerName = ConfigurationManager.AppSettings["SqlServerName"];
NHHelper.CreateShemaExportFile(HttpContext.Current.Server.MapPath("CreateSchema.sql"), TenantContext.TenantKey);
CreateDb dbsetup = new CreateDb(sqlRootFolder, TenantContext.DbName, sqlServerName, HttpContext.Current.Server.MapPath("CreateSchema.sql"));
if (!dbsetup.DBExist())
{
dbsetup.Execute(TenantContext.TenantKey);
}
}
protected override void OnInitialize()
{
base.OnInitialize();
var pendingDownloads = Shell.PendingDownloads;
foreach (var attachment in pendingDownloads.OfType <Attachment>().ToArray())
{
attachment.Session = Session;
attachment.Entry = NHHelper.Reassociate(Session, attachment, attachment.Entry);
}
Mail.TrackIsNew(UiScheduler, CurrentItem);
mails = Session.Query <Mail>().ToList();
Items.Recalculate();
}
protected override IDictionary <string, string> GetResourcesFromDB(string language)
{
var session = NHHelper.GetSessionFactoryFor(TenantContext.TenantKey).GetCurrentSession();
var query = from r in session.Query <Resource>()
where r.Language == language
select new { r.Label, r.Value };
var ret = new Dictionary <string, string>();
foreach (var item in query)
{
if (!ret.ContainsKey(item.Label))
{
ret.Add(item.Label, item.Value);
}
}
return(ret);
}
public string getSheetNo()
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
List <QueryField> qf = new List <QueryField>();
MsgBox mb = new MsgBox();
sql.Append("select right('0000000'+ cast(MAX(sheetNo)+1 as varchar),7) as sheetNo from low_value ");
dt = NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0];
string s = dt.Rows[0]["sheetNo"].ToString();
if (string.IsNullOrEmpty(s))
{
s = "0000001";
}
return(s);
}
/// <summary>
/// 本周完成工作查询
/// Created:20170508(ChengMengjia)
/// </summary>
/// <param name="ProjectId"></param>
/// <param name="startDay"></param>
/// <param name="endDay"></param>
/// <param name="NeedWork"></param>
/// <param name="NeedLCB"></param>
/// <param name="NeedJFW"></param>
/// <returns></returns>
public DataTable GetFinishedWork(string ProjectId, DateTime startDay, DateTime endDay, bool NeedWork, bool NeedLCB, bool NeedJFW)
{
StringBuilder sql = new StringBuilder();
List <QueryField> qf = new List <QueryField>();
sql.Append(" select '' Type,'' Name,'' Desc,'' Result,'' Person,'' Status ");
if (NeedWork)
{
//本周工作
sql.Append(" union all ");
sql.Append(" select p.Name Type,r.Name,r.Desc,r.DealResult Result,'' Person,d.Name Status from Routine r ");
sql.Append(" inner join PNode p on r.NodeID = substr(p.ID,1,36) and p.Status = 1 ");
sql.Append(" left join DictItem d on r.FinishStatus = d.No and d.DictNo = " + (int)CommonDLL.DictCategory.WorkHandleStatus);
sql.Append(" where r.status = 1 and r.FinishStatus=2 and p.PID=@PID and date(r.StartDate) >= date(@startDate) and (date(r.EndDate) <= date(@endDate) or r.EndDate is null )");
}
if (NeedLCB)
{
//本周里程碑
sql.Append(" union all ");
sql.Append(" select '里程碑' Type,m.name,m.Condition Desc,m.Remark Result,'' Person,d.Name Status from Milestones m ");
sql.Append(" left join DictItem d on d.DictNo=" + (int)DictCategory.Milestones_FinshStatus + " and m.FinishStatus=D.No ");
sql.Append(" where m.status=1 and m.PID=@PID and date(m.FinishDate) >= date(@startDate) and date(m.FinishDate) <= date(@endDate) ");
}
if (NeedJFW)
{
//本周交付物
sql.Append(" union all ");
sql.Append(" select parent.Name Type,j.name,j.Desc,'' Result,j.Manager Person,'' Status ");
sql.Append(" from DeliverablesJBXX j inner join PNode n on j.NodeID=substr(n.ID,1,36) and n.status=1");
sql.Append(" left join PNode parent on n.ParentID=substr(parent.ID,1,36) and parent.status=1");
sql.Append(" where j.status=1 and n.PID=@PID and date(j.StarteDate) >= date(@startDate) and (date(j.EndDate) <= date(@endDate) or j.EndDate is null ) ");
}
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = startDay.ToString("yyyy-MM-dd")
});
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = endDay.ToString("yyyy-MM-dd")
});
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = ProjectId
});
return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]);
}
/// <summary>
/// 存在的问题查询
/// Created:20170508(ChengMengjia)
/// </summary>
/// <param name="ProjectId"></param>
/// <param name="startDay"></param>
/// <param name="NeedFinished"></param>
/// <param name="NeedUnfinish"></param>
/// <returns></returns>
public DataTable GetTroubleList(string ProjectId, DateTime startDay, bool NeedFinished, bool NeedUnfinish)
{
StringBuilder sql = new StringBuilder();
List <QueryField> qf = new List <QueryField>();
sql.Append(" select '' Name,'' Desc,'' Result,'' Person,'' Status,'' HandleDate ");
if (NeedFinished)
{
sql.Append(" union all ");
sql.Append(" select r.Name,r.Desc,r.HandleResult Result,s.Name Person,d.Name Status,strftime('%Y-%m-%d',r.HandleDate) HandleDate from Trouble r ");
sql.Append(" inner join PNode p on r.NodeID = substr(p.ID,1,36) and p.Status = 1 ");
sql.Append(" left join Stakeholders s on r.HandleMan = substr(s.ID,1,36) ");
sql.Append(" left join DictItem d on r.HandleStatus = d.No and d.DictNo = " + (int)CommonDLL.DictCategory.TroubleHandleStatus);
sql.Append(" where r.status = 1 and p.PID=@PID and r.HandleStatus=2 ");
sql.Append(" and date(r.HandleDate) >= date(@startDate) and date(r.HandleDate) <= date(@endDate) ");
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = startDay.ToString("yyyy-MM-dd")
});
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = startDay.AddDays(6).ToString("yyyy-MM-dd")
});
}
if (NeedUnfinish)
{
sql.Append(" union all ");
sql.Append(" select r.Name,r.Desc,r.HandleResult Result,s.Name Person,d.Name Status,strftime('%Y-%m-%d',r.HandleDate) HandleDate from Trouble r ");
sql.Append(" inner join PNode p on r.NodeID = substr(p.ID,1,36) and p.Status = 1 ");
sql.Append(" left join Stakeholders s on r.HandleMan = substr(s.ID,1,36) ");
sql.Append(" left join DictItem d on r.HandleStatus = d.No and d.DictNo = " + (int)CommonDLL.DictCategory.TroubleHandleStatus);
sql.Append(" where r.status = 1 and p.PID=@PID and r.HandleStatus=1 ");
sql.Append(" and date(r.EndDate) <= date(@endDate2) ");
qf.Add(new QueryField()
{
Name = "endDate2", Type = QueryFieldType.String, Value = startDay.AddDays(13).ToString("yyyy-MM-dd")
});
}
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = ProjectId
});
return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]);
}
public void FixtureSetup()
{
var nhConfig = ConfigurationExtensions.GetNhConfig();
mockSolr = new MSolrOperations <Entity>();
var mapper = new MReadOnlyMappingManager();
var provider = new MServiceProvider();
provider.getService += t => {
if (t == typeof(IReadOnlyMappingManager))
{
return(mapper);
}
throw new ArgumentException("Unexpected");
};
NHHelper.SetListener(nhConfig, GetSolrNetListener(mockSolr));
new SchemaExport(nhConfig).Execute(false, true, false);
sessionFactory = nhConfig.BuildSessionFactory();
}
/// <summary>
/// 获取底层节点（带状态）
/// Created:20170605(ChengMengjia)
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public List <PNode> GetNodesWithStatus(string ProjectID)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select a.*,CASE WHEN b.cc is Null then ( case a.PType ");
//交付物
sql.Append(" when 1 then (case when d.PType=5 then 1 ");
sql.Append(" when c.EndDate<date('now') and (d.PType is null or d.PType<>5) then 3 ");
sql.Append(" when c.StarteDate>=date('now','+1 day') and (d.PType is null or d.PType<>5) then 0 else 2 end) ");
//日常
sql.Append(" when 2 then (case when e.FinishStatus=3 then 1 ");
sql.Append(" when e.EndDate<date('now') and (e.FinishStatus is null or e.FinishStatus<>3) then 3 ");
sql.Append(" when e.StartDate>=date('now','+1 day') and (e.FinishStatus is null or e.FinishStatus<>3) then 0 else 2 end ) ");
//问题
sql.Append(" when 3 then (case when f.HandleStatus=3 then 1 ");
sql.Append(" when f.EndDate<date('now') and (f.HandleStatus is null or f.HandleStatus<>3) then 3 ");
sql.Append(" when f.StarteDate>=date('now','+1 day') and (f.HandleStatus is null or f.HandleStatus<>3) then 0 else 2 end ) ");
sql.Append(" else null end) else null end FinishStatus from pnode a ");
sql.Append(" left join (select parentid,count(*)cc from pnode where status=1 and PID=@PID group by parentid)b on a.id=b.parentid ");
sql.Append(" left join DeliverablesJBXX c on c.NodeID=substr(a.ID,1,36) and c.Status=1 and a.PType=1 ");
sql.Append(" left join NodeProgress d on d.NodeID=substr(a.ID,1,36) and d.Status=1 ");
sql.Append(" left join Routine e on e.NodeID=substr(a.ID,1,36) and e.Status=1 and a.PType=2 ");
sql.Append(" left join Trouble f on f.NodeID=substr(a.ID,1,36) and f.Status=1 and a.PType=3 ");
sql.Append(" where a.status=1 and a.PID=@PID ");
List <QueryField> qf = new List <QueryField>();
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = ProjectID
});
DataSet ds = NHHelper.ExecuteDataset(sql.ToString(), qf);
if (ds == null || ds.Tables.Count == 0)
{
return(new List <PNode>());
}
else
{
return(JsonHelper.TableToList <PNode>(ds.Tables[0]));
}
}
protected void Application_EndRequest(object sender, EventArgs e)
{
//Check if db connectionstring is present
if (String.IsNullOrEmpty(TenantContext.TenantKey) || !DBConnectionStringIsPresentInWebConfig(TenantContext.TenantKey))
{
return;
}
if (ConfigurationManager.ConnectionStrings["db" + TenantContext.TenantKey.ToLower()] == null)
{
return;
}
var session = CurrentSessionContext.Unbind(NHHelper.GetSessionFactoryFor(TenantContext.TenantKey));
if (session != null)
{
session.Dispose();
}
}
public MsgBox caculate()
{
DataTable dt = new DataTable();
StringBuilder sql = new StringBuilder();
List <QueryField> qf = new List <QueryField>();
DateTime st = new DateTime(DateTime.Now.Year, 1, 1, 0, 0, 0);
DateTime et = new DateTime(DateTime.Now.Year, 12, 31, 23, 59, 59);
qf.Add(new QueryField()
{
Name = "st", Type = QueryFieldType.DateTime, Value = st
});
qf.Add(new QueryField()
{
Name = "et", Type = QueryFieldType.DateTime, Value = et
});
MsgBox mb = new MsgBox();
sql.Append("select COUNT(*) as qty from repair r ");
sql.Append("where r.isDone=1 ");
sql.Append("and r.UPDATED>@st ");
sql.Append("and r.UPDATED<@et ");
try
{
dt = NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0];
int qty = int.Parse(dt.Rows[0]["qty"].ToString());
year_repair yr = new year_repair();
yr.ID = Guid.NewGuid().ToString();
yr.qty = qty;
yr.year = DateTime.Now.Year.ToString();
new Repository <year_repair>().Insert(yr);
mb.status = true;
mb.msg = "计算完成。";
}
catch (Exception e)
{
mb.status = false;
mb.msg = e.Message;
}
return(mb);
}
/// <summary>
/// 获取全部信息
/// </summary>
/// <returns></returns>
public DataTable GetCostList(string PID)
{
List <QueryField> qf = new List <QueryField>();
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
qf.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
StringBuilder sql = new StringBuilder();
sql.Append(" select Tag, Total,(Used+Transit) as Used,transit ,Remaining,id,created,Explanation from Cost");
sql.Append(" where PID=@PID and Status=@Status");
sql.Append(" order by CREATED");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(dt);
}
public void Init()
{
TenantContext.SetTenantContextProvider(new StubTenantContextProvider());
TenantResources.SetTenantResourceProvider(new NHCachedResourceProvider(new DefaultCacheService()));
testResource = new Resource()
{
Label = "testResource",
Value = "value for test resource",
Language = TenantContext.Language
};
NHHelper.IsWeb = false;
NHHelper.ClearCache();
currentSession = NHHelper.GetSessionFactoryFor("Default").OpenSession();
CurrentSessionContext.Bind(currentSession);
tx = currentSession.BeginTransaction();
currentSession.Save(
testResource
);
}
/// <summary>
/// 获取周报发送记录
/// Created:20170509(ChengMengjia)
/// </summary>
/// <param name="ProjectID"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="query"></param>
/// <returns></returns>
public DataTable getWeeklyHistory(string ProjectID, string start, string end, string query)
{
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append("select * from Report_Weekly where PID=@ProjectID and status=1 ");
qf.Add(new QueryField()
{
Name = "ProjectID", Type = QueryFieldType.String, Value = ProjectID
});
if (!string.IsNullOrEmpty(start))
{
sql.Append(" and CREATED>=@start");
qf.Add(new QueryField()
{
Name = "start", Type = QueryFieldType.String, Value = DateTime.Parse(start).ToString("yyyy-MM-dd 00:00:00")
});
}
if (!string.IsNullOrEmpty(end))
{
sql.Append(" and CREATED<=@end");
qf.Add(new QueryField()
{
Name = "end", Type = QueryFieldType.String, Value = DateTime.Parse(end).ToString("yyyy-MM-dd 23:59:59")
});
}
if (!string.IsNullOrEmpty(query))
{
sql.Append(" and Title+Content like @query ");
qf.Add(new QueryField()
{
Name = "query", Type = QueryFieldType.String, Value = "%" + query + "%"
});
}
sql.Append(" order by CREATED ");
return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]);
}
/// <summary>
/// 获取全部信息
/// </summary>
/// <returns></returns>
public DataTable GetIncomeList(string PID)
{
List <QueryField> qf = new List <QueryField>();
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
qf.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
StringBuilder sql = new StringBuilder();
sql.Append(" select i.*,d.Name as FinishStatusName from Income i");
sql.Append(" left join DictItem d on d.No = i.FinishStatus and d.DictNo = " + (int)DictCategory.EarningStatus);
sql.Append(" where PID=@PID and Status=@Status");
sql.Append(" order by Created");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(dt);
}
public virtual bool IsPriceExists()
{
return(NHHelper.IsExists(() => String.IsNullOrEmpty(Price?.Name)));
}
public virtual bool IsAddressExists()
{
return(NHHelper.IsExists(() => String.IsNullOrEmpty(Address?.Name)));
}
public WorkerNH()
{
_session = NHHelper.OpenSession();
}
public void CanConfigureNHibernateTest()
{
var sessionFactory = NHHelper.GetSessionFactoryFor("Default");
}
public void CanWriteSchemaTest()
{
NHHelper.CreateShemaExportFile("BackToOwnerDBCreate.sql", "Default");
}