/// <summary>
/// 增加一条数据
/// </summary>
public int Add(LPWeb.Model.Regions model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Regions(");
strSql.Append("Name,[Desc],Enabled,GroupID)");
strSql.Append(" values (");
strSql.Append("@Name,@Desc,@Enabled,@GroupID)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Name", SqlDbType.NVarChar, 50),
new SqlParameter("@Desc", SqlDbType.NVarChar, 500),
new SqlParameter("@Enabled", SqlDbType.Bit, 1),
new SqlParameter("@GroupID", SqlDbType.Int)
};
parameters[0].Value = model.Name;
parameters[1].Value = model.Desc;
parameters[2].Value = model.Enabled;
parameters[3].Value = model.GroupID;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(1);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public LPWeb.Model.Regions GetModel(int RegionId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 RegionId,Name,[Desc],Enabled,GroupID from Regions ");
strSql.Append(" where RegionId=@RegionId ");
SqlParameter[] parameters =
{
new SqlParameter("@RegionId", SqlDbType.Int, 4)
};
parameters[0].Value = RegionId;
LPWeb.Model.Regions model = new LPWeb.Model.Regions();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["RegionId"].ToString() != "")
{
model.RegionId = int.Parse(ds.Tables[0].Rows[0]["RegionId"].ToString());
}
model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
model.Desc = ds.Tables[0].Rows[0]["Desc"].ToString();
if (ds.Tables[0].Rows[0]["Enabled"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["Enabled"].ToString() == "1") || (ds.Tables[0].Rows[0]["Enabled"].ToString().ToLower() == "true"))
{
model.Enabled = true;
}
else
{
model.Enabled = false;
}
}
if (ds.Tables[0].Rows[0]["GroupID"].ToString() != "")
{
model.GroupID = int.Parse(ds.Tables[0].Rows[0]["GroupID"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(LPWeb.Model.Regions model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Regions set ");
strSql.Append("Name=@Name,");
strSql.Append("Desc=@Desc,");
strSql.Append("Enabled=@Enabled");
strSql.Append("GroupID=@GroupID");
strSql.Append(" where RegionId=@RegionId ");
SqlParameter[] parameters =
{
new SqlParameter("@Name", SqlDbType.NVarChar, 50),
new SqlParameter("@Desc", SqlDbType.NVarChar, 500),
new SqlParameter("@Enabled", SqlDbType.Bit, 1),
new SqlParameter("@GroupID", SqlDbType.Int)
};
parameters[0].Value = model.Name;
parameters[1].Value = model.Desc;
parameters[2].Value = model.Enabled;
parameters[3].Value = model.GroupID;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
public void SaveRegionAndMembersBase(int iRegionId, bool bEnabled, string sDesc, int groupId, string sDivisionMemberIDs, string sExectives)
{
Collection <SqlCommand> SqlCmdList = new Collection <SqlCommand>();
#region SQL for 更新Regions信息
string sSql = "UPDATE Regions set Enabled=@Enabled, [Desc]=@Desc,GroupID=@groupId where RegionId=" + iRegionId;
SqlCommand SqlCmd = new SqlCommand(sSql);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Enabled", SqlDbType.Bit, bEnabled);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Desc", SqlDbType.NVarChar, sDesc);
DbHelperSQL.AddSqlParameter(SqlCmd, "@groupId", SqlDbType.Int, groupId);
SqlCmdList.Add(SqlCmd);
#endregion
string sSql5 = "update Divisions set RegionID=null where RegionID = " + iRegionId.ToString();
SqlCommand SqlCmd5 = new SqlCommand(sSql5);
SqlCmdList.Add(SqlCmd5);
if (sDivisionMemberIDs != string.Empty)
{
// 清空新添加members的ID信息
string sSql5x = "update Divisions set RegionID=null where RegionID in (" + iRegionId + ")";
SqlCommand SqlCmd5x = new SqlCommand(sSql5x);
SqlCmdList.Add(SqlCmd5x);
#region
LPWeb.Model.Regions model = GetModel(iRegionId);
// 获取Group信息
string sRegionID = null;
string sSql6 = string.Empty;
sSql6 = "update Divisions set RegionID= " + iRegionId.ToString() + " where DivisionID in (" + sDivisionMemberIDs + ")";
SqlCommand SqlCmd6 = new SqlCommand(sSql6);
SqlCmdList.Add(SqlCmd6);
//更新该region下所有Divisions 下的Branch下的RegionID字段
string sSql12 = "update Branches set RegionID=NULL where RegionID in (" + iRegionId + ")";
SqlCommand SqlCmd12 = new SqlCommand(sSql12);
SqlCmdList.Add(SqlCmd12);
//更新该region下所有Divisions 下的Branch下的RegionID字段
string sSql11 = "update Branches set RegionID=" + iRegionId + " where DivisionID in (" + sDivisionMemberIDs + ")";
SqlCommand SqlCmd11 = new SqlCommand(sSql11);
SqlCmdList.Add(SqlCmd11);
#endregion
}
//更新RegionExecutives 表
//1.首先删除RegionExecutives表中region所有的Executive关系记录
string sSql7 = "Delete RegionExecutives where RegionID = " + iRegionId.ToString();
SqlCommand SqlCmd7 = new SqlCommand(sSql7);
SqlCmdList.Add(SqlCmd7);
//2.将sExectives增加到RegionExecutives表
string sSqlInsertModel = "INSERT INTO RegionExecutives VALUES({0},{1}) ";
string sSql8 = "";
var executives = sExectives.Split(',');
int e;
int flag = 0;
foreach (var executive in executives) //循环executives将所有的executive记录到表中
{
if (!int.TryParse(executive, out e)) //检查该userid是否合法
{
continue;
}
flag++;
sSql8 += string.Format(sSqlInsertModel, iRegionId, executive);
}
if (flag > 0)
{
SqlCommand SqlCmd8 = new SqlCommand(sSql8);
SqlCmdList.Add(SqlCmd8);
}
#region 更新 group 表
string sSql9 = "EXEC GroupRelationInfoUpdate 'Region'," + iRegionId.ToString() + "," + groupId.ToString();
SqlCommand sqlcmd9 = new SqlCommand(sSql9);
SqlCmdList.Add(sqlcmd9);
////更新原来的group为空
//string sSql9 = "UPDATE Groups SET RegionID=NULL,OrganizationType=NULL,DivisionID=NULL,BranchID=NULL WHERE RegionID=" + iRegionId;
//SqlCommand sqlcmd9 = new SqlCommand(sSql9);
//SqlCmdList.Add(sqlcmd9);
//sSql9 = "UPDATE Groups SET";
////设置新的group属于当前region
//string sSql10 = "UPDATE Groups SET RegionID=" + iRegionId + ",OrganizationType='Region',DivisionID=NULL,BranchID=NULL WHERE GroupID=" + groupId;
//SqlCommand sqlcmd10 = new SqlCommand(sSql10);
//SqlCmdList.Add(sqlcmd10);
#endregion
#region 批量执行SQL
SqlConnection SqlConn = null;
SqlTransaction SqlTrans = null;
try
{
SqlConn = DbHelperSQL.GetOpenConnection();
SqlTrans = SqlConn.BeginTransaction();
foreach (SqlCommand xSqlCmd in SqlCmdList)
{
DbHelperSQL.ExecuteNonQuery(xSqlCmd, SqlTrans);
}
SqlTrans.Commit();
}
catch (Exception ex)
{
SqlTrans.Rollback();
throw ex;
}
finally
{
if (SqlConn != null)
{
SqlConn.Close();
}
}
#endregion
}
protected void btnSave_Click(object sender, EventArgs e)
{
#region set Default
if (!string.IsNullOrEmpty(hifId.Value))
{
bool IsCancel = false;
var val = hifId.Value.Split(':');
if (val.Count() != 2)
{
return;
}
IsCancel = Convert.ToBoolean(val[1]);
PointFolders folder = new PointFolders();
folder.SetDefaultPoint(val[0], this.iBranchID, IsCancel);
}
#endregion
bool bEnabled = this.ckbEnabled.Checked;
string sDesc = this.txbDescription.Text.Trim();
string sFolderIDs = this.hdnFolderIDs.Value;
string sManagers = this.hdnManagers.Value;
LPWeb.Model.Branches model = new LPWeb.Model.Branches();
model = branchManager.GetModel(iBranchID);
int iOldGroupID = Convert.ToInt32(model.GroupID);
model.BranchId = iBranchID;
model.Enabled = ckbEnabled.Checked;
model.Desc = txbDescription.Text.Trim();
model.Name = ddlBranchName.SelectedItem.Text;
if (ddlGroupAccess.Items.Count > 0)
{
model.GroupID = int.Parse(ddlGroupAccess.SelectedValue);
}
else
{
model.GroupID = 0;
}
model.BranchAddress = txbAddress.Text.Trim();
model.City = txbCity.Text.Trim();
model.Zip = txbZip.Text.Trim();
model.BranchState = ddlState.SelectedValue.Trim();
if (this.fuldWebLogo.HasFile && this.fuldWebLogo.FileName.ToUpper().IndexOf(".TIF") < 0)
{
model.WebsiteLogo = fuldWebLogo.FileBytes;
}
else
{
model.WebsiteLogo = null;
}
model.License1 = txbLicense1.Text.Trim();
model.License2 = txbLicense2.Text.Trim();
model.License3 = txbLicense3.Text.Trim();
model.License4 = txbLicense4.Text.Trim();
model.License5 = txbLicense5.Text.Trim();
model.Disclaimer = txbDisclaimer.Text.Trim();
model.Phone = txbPhone.Text.Trim();
model.Fax = txbFax.Text.Trim();
model.Email = txbEmail.Text.Trim();
model.WebURL = txbWebURL.Text.Trim();
var oldHomeBranch = model.HomeBranch;
model.HomeBranch = chkHomeBranch.Checked;
try
{
if (oldHomeBranch != model.HomeBranch)
{
branchManager.SetOtherHomeBranchFalse(model.BranchId);
}
branchManager.SaveBranchAndMembersBase(model, sFolderIDs, sManagers);
//Save point folder enable status
PointFolders pointFolderMgr = new PointFolders();
if (this.hdnDisableFolderIDs.Value.Trim() != "")
{
pointFolderMgr.UpdatePointFolderEnabled(this.hdnDisableFolderIDs.Value, false);
}
if (this.hdnEnableFolderIDs.Value.Trim() != "")
{
pointFolderMgr.UpdatePointFolderEnabled(this.hdnEnableFolderIDs.Value, true);
}
//this.divManager.SaveDivisionAndMembersBase(this.iDivisionID, bEnabled, sDesc, sBranchMemberIDs, sExectives);
//Save group folder info
model = branchManager.GetModel(model.BranchId);
GroupFolder groupFolder = new GroupFolder();
if (model.GroupID != 0)
{
groupFolder.DoSaveGroupFolder(Convert.ToInt32(model.GroupID), model.BranchId, "branch", iOldGroupID);
}
if (model.RegionID != 0 && model.RegionID != null)
{
Regions regMgr = new Regions();
LPWeb.Model.Regions regionModel = regMgr.GetModel(Convert.ToInt32(model.RegionID));
if (regionModel.GroupID != null && regionModel.GroupID != 0)
{
groupFolder.DoSaveGroupFolder(Convert.ToInt32(regionModel.GroupID), Convert.ToInt32(model.RegionID), "region", Convert.ToInt32(regionModel.GroupID));
}
}
if (model.DivisionID != 0 && model.DivisionID != null)
{
Divisions divMgr = new Divisions();
LPWeb.Model.Divisions divModel = divMgr.GetModel(Convert.ToInt32(model.DivisionID));
if (divModel.GroupID != null && divModel.GroupID != 0)
{
groupFolder.DoSaveGroupFolder(Convert.ToInt32(divModel.GroupID), Convert.ToInt32(model.DivisionID), "division", Convert.ToInt32(divModel.GroupID));
}
}
PageCommon.WriteJsEnd(this, "Branch saved successfully.", PageCommon.Js_RefreshSelf);
}
catch
{
PageCommon.WriteJsEnd(this, "Failed to save the record.", PageCommon.Js_RefreshSelf);
}
}
