public Organization GetOrganization(string orgCode)
{
string sql = @"SELECT D1.[OrgCode],D1.[OrgName],D1.[ParentCode],D1.[Path],D1.[Remark],D1.[OrgType],D1.[UnitName],D1.[UnitCode]
,D1.[Sequence],D1.[LastUpdateUserUID],D1.[LastUpdateUserName],D1.[LastUpdateTime],D2.[OrgName] as ParentName
FROM Organizations D1
LEFT JOIN Organizations D2 ON D1.ParentCode =D2.OrgCode
WHERE D1.OrgCode=@OrgCode";
SqlParameter p = new SqlParameter("@OrgCode", orgCode);
Organization item = null;
using (IDataReader reader = base.ExcuteDataReader(sql, p))
{
if (reader.Read())
{
item = new Organization();
item.OrgCode = reader.GetString(0);
item.OrgName = reader.GetString(1);
if (!reader.IsDBNull(2))
{
item.ParentCode = reader.GetString(2);
}
item.Path = reader.GetString(3);
if (!reader.IsDBNull(4))
{
item.Remark = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
item.OrgType = reader.GetByte(5);
}
item.UnitName = reader.IsDBNull(6) ? null : reader.GetString(6);
item.UnitCode = reader.IsDBNull(7) ? null : reader.GetString(7);
item.Sequence = reader.GetInt32(8);
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
item.ParentName = reader.IsDBNull(12) ? null : reader.GetString(12);
}
}
return item;
}
public List<Organization> GetChildOrgsByParentCode(string parentCode)
{
string sql = @"SELECT A.[OrgCode],A.[OrgName],ISNULL(B.ChildCount,0) as ChildCount
FROM [Organizations] A
LEFT JOIN (SELECT COUNT(1) as ChildCount ,ParentCode From [Organizations] Group By ParentCode) B ON A.OrgCode=b.ParentCode
where A.ParentCode=@ParentCode Order by A.[Sequence]";
SqlParameter p = new SqlParameter("@ParentCode", parentCode);
List<Organization> list = new List<Organization>();
using (IDataReader reader = base.ExcuteDataReader(sql, p))
{
while (reader.Read())
{
Organization item = new Organization();
item.OrgCode = reader.GetString(0);
item.OrgName = reader.GetString(1);
item.HasChild = reader.GetInt32(2) > 0;
item.IsNew = false;
list.Add(item);
}
}
return list;
}
public void SaveOrgInfo(Organization d)
{
StoredProcedure sp = StoredProcedures.SP_SaveOrgInfo(d.OrgCode, d.OrgName, d.ParentCode, d.Remark,d.OrgType,d.Sequence, d.LastUpdateUserUID, d.LastUpdateUserName);
base.SPExecuteNonQuery(sp);
}
public void Save(Organization item)
{
if(item.IsNew)
{
Insert(item);
}
else
{
Update(item);
}
}
public void Update(Organization item)
{
if(item.ChangedPropertyCount>0)
{
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.Append("UPDATE [Organizations] SET ");
Dictionary<string,string> cols =new Dictionary<string,string>();
cols.Add("OrgName","[OrgName]");
cols.Add("ParentCode","[ParentCode]");
cols.Add("Path","[Path]");
cols.Add("Remark","[Remark]");
cols.Add("Sequence","[Sequence]");
cols.Add("OrgType","[OrgType]");
cols.Add("UnitName","[UnitName]");
cols.Add("UnitCode","[UnitCode]");
cols.Add("LastUpdateUserUID","[LastUpdateUserUID]");
cols.Add("LastUpdateUserName","[LastUpdateUserName]");
cols.Add("LastUpdateTime","[LastUpdateTime]");
int i = 0;
//UPDATE COLUMNS
foreach (string p in item.ChangedPropertyList)
{
if(!cols.ContainsKey(p))
{
continue;
}
if (i > 0)
{
sqlbuilder.Append(",");
}
sqlbuilder.AppendFormat("{0}=@{1}", cols[p], p);
i++;
}
//WHERE;
sqlbuilder.Append(" WHERE [OrgCode]=@OrgCode");
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@OrgCode",item.OrgCode));
if(item.IsChanged("OrgName"))
{
SPParams.Add(new SqlParameter("@OrgName",item.OrgName));
}
if(item.IsChanged("ParentCode"))
{
SPParams.Add(new SqlParameter("@ParentCode",item.ParentCode));
}
if(item.IsChanged("Path"))
{
SPParams.Add(new SqlParameter("@Path",item.Path));
}
if(item.IsChanged("Remark"))
{
SPParams.Add(new SqlParameter("@Remark",item.Remark));
}
if(item.IsChanged("Sequence"))
{
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
}
if(item.IsChanged("OrgType"))
{
SPParams.Add(new SqlParameter("@OrgType",item.OrgType));
}
if(item.IsChanged("UnitName"))
{
SPParams.Add(new SqlParameter("@UnitName",item.UnitName));
}
if(item.IsChanged("UnitCode"))
{
SPParams.Add(new SqlParameter("@UnitCode",item.UnitCode));
}
if(item.IsChanged("LastUpdateUserUID"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
}
if(item.IsChanged("LastUpdateUserName"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
}
if(item.IsChanged("LastUpdateTime"))
{
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
}
base.ExecuteNonQuery(sqlbuilder.ToString(), SPParams.ToArray());
}
}
public List<Organization> QueryAll()
{
string sql ="SELECT [OrgCode],[OrgName],[ParentCode],[Path],[Remark],[Sequence],[OrgType],[UnitName],[UnitCode],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [Organizations]";
List<Organization> list =new List<Organization>();
using(IDataReader reader = base.ExcuteDataReader(sql))
{
while(reader.Read())
{
Organization item =new Organization();
item.OrgCode = reader.GetString(0);
item.OrgName = reader.GetString(1);
if(!reader.IsDBNull(2))
{
item.ParentCode = reader.GetString(2);
}
item.Path = reader.GetString(3);
if(!reader.IsDBNull(4))
{
item.Remark = reader.GetString(4);
}
item.Sequence = reader.GetInt32(5);
item.OrgType = reader.GetByte(6);
if(!reader.IsDBNull(7))
{
item.UnitName = reader.GetString(7);
}
if(!reader.IsDBNull(8))
{
item.UnitCode = reader.GetString(8);
}
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
list.Add(item);
}
}
return list;
}
public void Insert(Organization item)
{
string sql="INSERT INTO [Organizations] ([OrgCode],[OrgName],[ParentCode],[Path],[Remark],[Sequence],[OrgType],[UnitName],[UnitCode],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime]) VALUES (@OrgCode,@OrgName,@ParentCode,@Path,@Remark,@Sequence,@OrgType,@UnitName,@UnitCode,@LastUpdateUserUID,@LastUpdateUserName,@LastUpdateTime)";
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@OrgCode",item.OrgCode));
SPParams.Add(new SqlParameter("@OrgName",item.OrgName));
SPParams.Add(new SqlParameter("@ParentCode",item.ParentCode));
SPParams.Add(new SqlParameter("@Path",item.Path));
SPParams.Add(new SqlParameter("@Remark",item.Remark));
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
SPParams.Add(new SqlParameter("@OrgType",item.OrgType));
SPParams.Add(new SqlParameter("@UnitName",item.UnitName));
SPParams.Add(new SqlParameter("@UnitCode",item.UnitCode));
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
base.ExecuteNonQuery(sql, SPParams.ToArray());
}
public Organization Get(string key)
{
string sql = "SELECT [OrgCode],[OrgName],[ParentCode],[Path],[Remark],[Sequence],[OrgType],[UnitName],[UnitCode],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [Organizations] WHERE [OrgCode]=@OrgCode";
SqlParameter p =new SqlParameter("@OrgCode",key);
Organization item =null;
using(IDataReader reader = base.ExcuteDataReader(sql,p))
{
if(reader.Read())
{
item =new Organization();
item.OrgCode = reader.GetString(0);
item.OrgName = reader.GetString(1);
if(!reader.IsDBNull(2))
{
item.ParentCode = reader.GetString(2);
}
item.Path = reader.GetString(3);
if(!reader.IsDBNull(4))
{
item.Remark = reader.GetString(4);
}
item.Sequence = reader.GetInt32(5);
item.OrgType = reader.GetByte(6);
if(!reader.IsDBNull(7))
{
item.UnitName = reader.GetString(7);
}
if(!reader.IsDBNull(8))
{
item.UnitCode = reader.GetString(8);
}
item.LastUpdateUserUID = reader.GetString(9);
item.LastUpdateUserName = reader.GetString(10);
item.LastUpdateTime = reader.GetDateTime(11);
}
}
return item;
}
public ActionResult EditOrg(string id, string parentCode, string parentName)
{
Organization Org;
if (!string.IsNullOrEmpty(id))
{
Org = sysManageService.GetOrgInfo(id);
if (Org == null)
{
throw new Exception("参数错误，不存在对应的角色");
}
}
else
{
Org = new Organization();
if (parentCode == "-1")
{
Organization root = sysManageService.GetRootOrganization();
Org.ParentCode = root.OrgCode;
Org.ParentName = root.OrgName;
Org.OrgType = 0;
}
else
{
Org.ParentCode = parentCode;
Org.ParentName = parentName;
Org.OrgType = 1;
}
}
return View(Org);
}
public JsonResult SaveOrgInfo(string id, Organization Organization)
{
JsonReturnMessages msg = new JsonReturnMessages();
try
{
if (string.IsNullOrEmpty(id))
{
Organization.IsNew = true;
}
else
{
Organization.IsNew = false;
}
Organization.LastUpdateUserUID = base.UserId;
Organization.LastUpdateUserName = base.CurrentUser.FullName;
Organization.LastUpdateTime = DateTime.Now;
sysManageService.SaveOrgInfo(Organization);
msg.IsSuccess = true;
msg.Msg = "操作成功";
}
catch (BizException bizex)
{
msg.IsSuccess = false;
msg.Msg = bizex.Message;
}
catch (Exception ex)
{
msg.IsSuccess = false;
msg.Msg = "操作失败：" + ex.Message;
}
return Json(msg);
}