public int UpdatePortalTreeData(Hashtable ht, SqlTransaction trans)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append("update PortalTreeData ");
if (ht.ContainsKey("IsDelete") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["IsDelete"])))
{
sbSql4org.Append("set IsDelete=@IsDelete ");
List.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
else if (ht.ContainsKey("Display") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["Display"])))
{
sbSql4org.Append("set Display=@Display ");
List.Add(new SqlParameter("@Display", ht["Display"].ToString()));
}
sbSql4org.Append(" where 1=1 ");
if (ht.ContainsKey("ids") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["ids"])))
{
sbSql4org.Append(" and Id in (" + ht["ids"].ToString() + ") ");
}
int number = SQLHelp.ExecuteNonQuery(trans, CommandType.Text, sbSql4org.ToString(), List.ToArray());
return(number);
}
catch (Exception)
{
return(-1);
}
}
///// <summary>
///// 新增签到
///// </summary>
///// <param name="model"></param>
///// <returns></returns>
//public static int edit_sign_in(sign_in model)
//{
// int result = 0;
// SqlParameter[] param = {
// new SqlParameter("@id", model.id),
// new SqlParameter("@sign_userid", model.sign_userid),
// new SqlParameter("@sign_username", model.sign_username),
// new SqlParameter("@sign_date", model.sign_date),
// new SqlParameter("@sign_cust_id", model.sign_cust_id),
// new SqlParameter("@sign_location", model.sign_location),
// new SqlParameter("@sign_address", model.sign_address),
// new SqlParameter("@sign_offset", model.sign_offset)
// };
// param[0].Direction = ParameterDirection.Output;
// object obj = SQLHelp.ExecuteNonQuery("edit_sign_in", CommandType.StoredProcedure, param);
// result = Convert.ToInt32(param[0].Value.ToString());
// return result;
//}
/// <summary>
/// 新增跟进记录
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int add_follow_up(follow_up model, string picture)
{
int result = 0;
SqlParameter[] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@follow_userid", model.follow_userid == null?"":model.follow_userid),
new SqlParameter("@follow_username", model.follow_username == null?"":model.follow_username),
new SqlParameter("@follow_cust_id", model.follow_cust_id == null?0:model.follow_cust_id),
new SqlParameter("@follow_link_id", model.follow_link_id == null?0:model.follow_link_id),
new SqlParameter("@follow_date", model.follow_date == null?DateTime.Now:model.follow_date),
new SqlParameter("@follow_content", model.follow_content == null?"":model.follow_content),
new SqlParameter("@follow_type", model.follow_type == null?0:model.follow_type),
new SqlParameter("@follow_status", model.follow_status == null?"":model.follow_status),
new SqlParameter("@follow_remaindate", model.follow_remaindate == null?DateTime.Now:model.follow_remaindate),
new SqlParameter("@follow_address", model.follow_address == null?"":model.follow_address),
new SqlParameter("@picture", picture == null?"":picture)
};
param[0].Direction = ParameterDirection.Output;
object obj = SQLHelp.ExecuteNonQuery("add_follow_up", CommandType.StoredProcedure, param);
result = Convert.ToInt32(param[0].Value.ToString());
return(result);
}
/// <summary>
/// 编辑联系人edit_cust_linkman
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int add_cust_linkman(cust_linkman model)
{
int result = 0;
SqlParameter[] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@link_cust_id", model.link_cust_id == null?0:model.link_cust_id),
new SqlParameter("@link_name", model.link_name == null?"":model.link_name),
new SqlParameter("@link_department", model.link_department == null?"":model.link_department),
new SqlParameter("@link_position", model.link_position == null?"":model.link_position),
new SqlParameter("@link_level", model.link_level == null?0:model.link_level),
new SqlParameter("@link_sex", model.link_sex == null?"":model.link_sex),
new SqlParameter("@link_birthday", model.link_birthday),
new SqlParameter("@link_phonenumber", model.link_phonenumber == null?"":model.link_phonenumber),
new SqlParameter("@link_telephone", model.link_telephone == null?"":model.link_telephone),
new SqlParameter("@link_email", model.link_email == null?"":model.link_email),
new SqlParameter("@link_status", model.link_status == null?"":model.link_status),
new SqlParameter("@link_users", model.link_users == null?"0":model.link_users),
new SqlParameter("@link_usersname", model.link_usersname == null?"":model.link_usersname),
new SqlParameter("@link_cust_name", model.link_cust_name == null?"":model.link_cust_name)
};
param[0].Direction = ParameterDirection.Output;
object obj = SQLHelp.ExecuteNonQuery("add_cust_linkman", CommandType.StoredProcedure, param);
result = Convert.ToInt32(param[0].Value.ToString());
return(result);
}
public bool SetDesk(string AddIDS, string DelIDS, string IDCard)
{
StringBuilder stringBuilder = new StringBuilder();
string[] addArry = AddIDS.Split(',');
for (int i = 0; i < addArry.Length; i++)
{
if (addArry[i].Length > 0)
{
stringBuilder.Append("insert into User_Model_Rel(ModelID,UserIDCard) values(" + addArry[i] + ",'" + IDCard + "');");
}
}
string[] delArry = DelIDS.Split(',');
for (int i = 0; i < delArry.Length; i++)
{
if (delArry[i].Length > 0)
{
stringBuilder.Append("delete from User_Model_Rel where ModelID=" + delArry[i] + " and UserIDCard='" + IDCard + "';");
}
}
int result = SQLHelp.ExecuteNonQuery(stringBuilder.ToString(), CommandType.Text, null);
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 编辑工作报告
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int add_workreport(workreport model, string t_picture, string m_picture)
{
int result = 0;
SqlParameter[] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@report_userid", model.report_userid == null?"":model.report_userid),
new SqlParameter("@report_username", model.report_username == null?"":model.report_username),
new SqlParameter("@report_type", model.report_type == null?1:model.report_type),
new SqlParameter("@report_startdate", model.report_startdate == null?DateTime.Now:model.report_startdate),
new SqlParameter("@report_enddate", model.report_enddate == null?DateTime.Now:model.report_enddate),
new SqlParameter("@report_content", model.report_content == null?"":model.report_content),
new SqlParameter("@report_plan", model.report_plan == null?"":model.report_plan),
new SqlParameter("@report_cust_customer_array", model.report_cust_customer_array == null?"":model.report_cust_customer_array),
new SqlParameter("@report_cust_linkman_array", model.report_cust_linkman_array == null?"":model.report_cust_linkman_array),
new SqlParameter("@report_follow_up_array", model.report_follow_up_array == null?"":model.report_follow_up_array),
new SqlParameter("@report_sign_in_array", model.report_sign_in_array == null?"":model.report_sign_in_array),
new SqlParameter("@report_reader", model.report_reader == null?"":model.report_reader),
new SqlParameter("@report_sender", model.report_sender == null?"":model.report_sender),
new SqlParameter("@t_picture", t_picture == null?"":t_picture),
new SqlParameter("@m_picture", m_picture == null?"":m_picture)
};
param[0].Direction = ParameterDirection.Output;
object obj = SQLHelp.ExecuteNonQuery("add_workreport", CommandType.StoredProcedure, param);
result = Convert.ToInt32(param[0].Value.ToString());
return(result);
}
public int Add_RewardBatchDetail(int batchid, string achieve_Ids, string createUID)
{
int result = 0;
List <SqlParameter> pms = new List <SqlParameter>();
StringBuilder str = new StringBuilder();
pms.Add(new SqlParameter("@RewardBatch_Id", batchid));
pms.Add(new SqlParameter("@CreateUID", createUID));
string[] achArray = achieve_Ids.Split(',');
if (achArray.Length > 0)
{
for (int i = 0; i < achArray.Length; i++)
{
pms.Add(new SqlParameter("@Acheive_Id" + i, achArray[i]));
string achsql = "select Id from TPM_RewardBatchDetail where RewardBatch_Id=@RewardBatch_Id and Acheive_Id=@Acheive_Id" + i + " and IsDelete=0";
int allotid = Convert.ToInt32(SQLHelp.ExecuteScalar(achsql, CommandType.Text, pms.ToArray()));
if (allotid <= 0)
{
str.Append(@"insert into TPM_RewardBatchDetail(RewardBatch_Id,Acheive_Id,CreateUID)
values(@RewardBatch_Id,@Acheive_Id" + i + ",@CreateUID);");
}
}
result = SQLHelp.ExecuteNonQuery(str.ToString(), CommandType.Text, pms.ToArray());
}
return(result);
}
public int SetAdvertisingTop(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
StringBuilder sbSql4org_up = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
int id = Convert.ToInt32(ht["Id"]);
string mwhere = string.Empty;
if (!string.IsNullOrWhiteSpace(ht["MenuId"].SafeToString()))
{
mwhere += " and MenuId in (" + ht["MenuId"].SafeToString() + ")";
}
if (ht.ContainsKey("isPush") && !string.IsNullOrWhiteSpace(ht["isPush"].SafeToString()))
{
mwhere += " and isPush=" + ht["isPush"].SafeToString();
}
sbSql4org_up.Append("update Advertising set CreateTime=getdate(),SortId=(select max(SortId) from Advertising where 1=1 " + mwhere + ")+1 where Id=@Id");
SqlParameter[] param2 = new SqlParameter[]
{
new SqlParameter("@Id", id)
};
int number = SQLHelp.ExecuteNonQuery(sbSql4org_up.ToString(), CommandType.Text, param2);
return(number);
return(-999);
}
catch (Exception)
{
return(-999);
}
}
/// <summary>
/// 删除单条数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual bool Delete(T entity, int id)
{
string sql = string.Format("delete from {0} where id=@Id", entity.GetType().Name);
SqlParameter pms = new SqlParameter("@Id", id);
return(SQLHelp.ExecuteNonQuery(sql, CommandType.Text, pms) > 0);
}
/// <summary>
/// 删除单条数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public override bool Delete(MyResource entity, int id)
{
string sql = string.Format("delete from MyResource where id=@Id; delete from MyResource where '|'+code+'|' like '%|" + id + "|%'");
SqlParameter pms = new SqlParameter("@Id", id);
return(SQLHelp.ExecuteNonQuery(sql, CommandType.Text, pms) > 0);
}
/// <summary>
/// 编辑客户
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int add_cust_customer(cust_customer model, string link_ids)
{
int result = 0;
SqlParameter[] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@cust_parent_id", model.cust_parent_id == null?0:model.cust_parent_id),
new SqlParameter("@cust_name", model.cust_name == null?"":model.cust_name),
new SqlParameter("@cust_type", model.cust_type == null?0:model.cust_type),
new SqlParameter("@cust_level", model.cust_level == null?0:model.cust_level),
new SqlParameter("@cust_category", model.cust_category == null?0:model.cust_category),
new SqlParameter("@cust_address", model.cust_address == null?"":model.cust_address),
new SqlParameter("@cust_location", model.cust_location == null?"":model.cust_location),
new SqlParameter("@cust_users", model.cust_users == null?"":model.cust_users),
new SqlParameter("@cust_usersname", model.cust_usersname),
new SqlParameter("@cust_x", model.cust_x == null?0:model.cust_x),
new SqlParameter("@cust_y", model.cust_y == null?0:model.cust_y),
new SqlParameter("@link_ids", link_ids == null?"":link_ids)
};
param[0].Direction = ParameterDirection.Output;
object obj = SQLHelp.ExecuteNonQuery("add_cust_customer", CommandType.StoredProcedure, param);
result = Convert.ToInt32(param[0].Value.ToString());
return(result);
}
public int Admin_EditAllotReward(List <TPM_AllotReward> items)
{
int result = 0;
List <SqlParameter> pms = new List <SqlParameter>();
StringBuilder str = new StringBuilder();
if (items.Count() > 0)
{
for (int i = 0; i < items.Count; i++)
{
TPM_AllotReward item = items[i];
if (item.AllotMoney == null)
{
pms.Add(new SqlParameter("@AllotMoney" + i, DBNull.Value));
}
else
{
pms.Add(new SqlParameter("@AllotMoney" + i, item.AllotMoney));
}
pms.Add(new SqlParameter("@EditUID" + i, item.EditUID));
pms.Add(new SqlParameter("@BatchDetail_Id" + i, item.BatchDetail_Id));
pms.Add(new SqlParameter("@RewardUser_Id" + i, item.RewardUser_Id));
str.Append("update TPM_AllotReward set AllotMoney=@AllotMoney" + i + ",EditUID=@EditUID" + i + ",EditTime=getdate() where BatchDetail_Id=@BatchDetail_Id" + i + " and RewardUser_Id=@RewardUser_Id" + i + " and IsDelete=0;");
}
result = SQLHelp.ExecuteNonQuery(str.ToString(), CommandType.Text, pms.ToArray());
}
return(result);
}
public int Edit_AcheiveMember(List <TPM_AcheiveMember> items, int AcheiveReward_Id)
{
int result = 1;
List <SqlParameter> up_pms = new List <SqlParameter>(), pms = new List <SqlParameter>();
up_pms.Add(new SqlParameter("@AcheiveReward_Id", AcheiveReward_Id));
SQLHelp.ExecuteNonQuery("update TPM_AcheiveMember set IsDelete=1 where AcheiveReward_Id=@AcheiveReward_Id ", CommandType.Text, up_pms.ToArray());
string str = "";
if (items.Count() > 0)
{
for (int i = 0; i < items.Count; i++)
{
TPM_AcheiveMember item = items[i];
str += "update TPM_AcheiveMember set Score=@Score" + i + ",Sort=@Sort" + i + ",EditUID=@EditUID" + i + ",IsDelete=0 where Id=@Id" + i + ";";
pms.Add(new SqlParameter("@Id" + i, item.Id));
if (item.Score == null)
{
pms.Add(new SqlParameter("@Score" + i, DBNull.Value));
}
else
{
pms.Add(new SqlParameter("@Score" + i, item.Score));
}
pms.Add(new SqlParameter("@Sort" + i, item.Sort));
pms.Add(new SqlParameter("@EditUID" + i, item.EditUID));
}
result = SQLHelp.ExecuteNonQuery(str, CommandType.Text, pms.ToArray());
}
return(result);
}
public int Add_ModifyRecord(int reasonId, List <TPM_ModifyRecord> items)
{
int result = 0;
List <SqlParameter> pms = new List <SqlParameter>();
StringBuilder str = new StringBuilder();
if (items.Count() > 0)
{
pms.Add(new SqlParameter("@Reason_Id", reasonId));
for (int i = 0; i < items.Count; i++)
{
TPM_ModifyRecord item = items[i];
pms.Add(new SqlParameter("@Type" + i, item.Type));
pms.Add(new SqlParameter("@RelationId" + i, item.RelationId));
pms.Add(new SqlParameter("@Acheive_Id" + i, item.Acheive_Id));
pms.Add(new SqlParameter("@Content" + i, item.Content));
pms.Add(new SqlParameter("@ModifyUID" + i, item.ModifyUID));
pms.Add(new SqlParameter("@CreateUID" + i, item.CreateUID));
str.Append(@"insert into TPM_ModifyRecord(Type,RelationId,Acheive_Id,Content,Reason_Id,ModifyUID,CreateUID)
values(@Type" + i + ",@RelationId" + i + ",@Acheive_Id" + i + ",@Content" + i + ",@Reason_Id,@ModifyUID" + i + ",@CreateUID" + i + ");");
}
result = SQLHelp.ExecuteNonQuery(str.ToString(), CommandType.Text, pms.ToArray());
}
return(result);
}
public int Edit_RewardUserInfo(List <TPM_RewardUserInfo> items, int BookId)
{
int result = 1;
List <SqlParameter> up_pms = new List <SqlParameter>(), pms = new List <SqlParameter>();
up_pms.Add(new SqlParameter("@BookId", BookId));
SQLHelp.ExecuteNonQuery("update TPM_RewardUserInfo set IsDelete=1 where BookId=@BookId ", CommandType.Text, up_pms.ToArray());
string str = "";
if (items.Count() > 0)
{
for (int i = 0; i < items.Count; i++)
{
TPM_RewardUserInfo item = items[i];
str += "update TPM_RewardUserInfo set ULevel=@ULevel" + i + ",Sort=@Sort" + i + ",WordNum=@WordNum" + i + ",EditUID=@EditUID" + i + ",IsDelete=0 where Id=@Id" + i + ";";
pms.Add(new SqlParameter("@Id" + i, item.Id));
pms.Add(new SqlParameter("@ULevel" + i, item.ULevel));
pms.Add(new SqlParameter("@Sort" + i, item.Sort));
if (item.WordNum == null)
{
pms.Add(new SqlParameter("@WordNum" + i, DBNull.Value));
}
else
{
pms.Add(new SqlParameter("@WordNum" + i, item.WordNum));
}
pms.Add(new SqlParameter("@EditUID" + i, item.EditUID));
}
result = SQLHelp.ExecuteNonQuery(str, CommandType.Text, pms.ToArray());
}
return(result);
}
public bool EditRoleMenu(Hashtable ht)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append("delete from RoleOfMenu where RoleId=@RoleId");
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@RoleId", ht["RoleId"].ToString())
};
int number = SQLHelp.ExecuteNonQuery(trans, CommandType.Text, sbSql4org.ToString(), param);
if (!string.IsNullOrWhiteSpace(Convert.ToString(ht["MenuIds"])))
{
string[] MenuIds = ht["MenuIds"].ToString().Split(',');
StringBuilder sbSql4org_ins = new StringBuilder();
sbSql4org_ins.Append(" INSERT INTO RoleOfMenu(RoleId,MenuId)");
for (int i = 0; i < MenuIds.Length; i++)
{
if (!string.IsNullOrWhiteSpace(MenuIds[i]))
{
if (i + 1 == MenuIds.Length)
{
sbSql4org_ins.Append(" SELECT @RoleId" + i + ",@MenuId" + i);
}
else
{
sbSql4org_ins.Append(" SELECT @RoleId" + i + ",@MenuId" + i + " union");
}
List.Add(new SqlParameter("@RoleId" + i, ht["RoleId"].ToString()));
List.Add(new SqlParameter("@MenuId" + i, MenuIds[i]));
}
}
int num = SQLHelp.ExecuteNonQuery(trans, CommandType.Text, sbSql4org_ins.ToString(), List.ToArray());
if (num > 0)
{
trans.Commit();
return(true);
}
else
{
trans.Rollback();
return(false);
}
}
else
{
trans.Commit();
return(true);
}
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
public int SendMessage(Hashtable ht, List <Com_Message> list)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> spList = new List <SqlParameter>();
sbSql4org.Append("INSERT INTO Com_Message(Title,Contents,[Type],IsDelete,CreateTime,CreateUID,Receiver,Href,Status,IsSend,ReceiverEmail,CreateName,ReceiverName,Timing,FilePath)");
for (int i = 0; i < list.Count; i++)
{
if (i + 1 == list.Count)//最后一条
{
sbSql4org.Append(@" select @Title" + i + ",@Contents" + i + ",@Type" + i + ",@IsDelete" + i + ",@CreateTime" + i + ",@CreateUID" + i + ",@Receiver" + i + ",@Href" + i + ",@Status" + i + ",@IsSend" + i + ",@ReceiverEmail" + i + ",@CreateName" + i + ",@ReceiverName" + i + ",@Timing" + i + ",@FilePath" + i);
}
else
{
sbSql4org.Append(@" select @Title" + i + ",@Contents" + i + ",@Type" + i + ",@IsDelete" + i + ",@CreateTime" + i + ",@CreateUID" + i + ",@Receiver" + i + ",@Href" + i + ",@Status" + i + ",@IsSend" + i + ",@ReceiverEmail" + i + ",@CreateName" + i + ",@ReceiverName" + i + ",@Timing" + i + ",@FilePath" + i + " union all ");
}
spList.Add(new SqlParameter("@Title" + i, ht["Title"].ToString()));
string href = "";
if (!string.IsNullOrWhiteSpace(list[i].Href))
{
href = "<br/><h3><a href=" + list[i].Href + ">点击此处可查看详细信息</a></h3>";
}
spList.Add(new SqlParameter("@Contents" + i, ht["Contents"].ToString() + href));
spList.Add(new SqlParameter("@Type" + i, ht["Type"].ToString()));
spList.Add(new SqlParameter("@IsDelete" + i, ((int)SysStatus.正常).ToString()));
spList.Add(new SqlParameter("@CreateTime" + i, ht["CreateTime"].ToString()));
spList.Add(new SqlParameter("@CreateUID" + i, ht["CreateUID"].ToString()));
spList.Add(new SqlParameter("@Receiver" + i, list[i].Receiver));
spList.Add(new SqlParameter("@Href" + i, list[i].Href));
spList.Add(new SqlParameter("@Status" + i, ((int)MessageStatus.未读).ToString()));
spList.Add(new SqlParameter("@IsSend" + i, ((int)MessageIsSend.未发送).ToString()));
spList.Add(new SqlParameter("@ReceiverEmail" + i, list[i].ReceiverEmail));
spList.Add(new SqlParameter("@CreateName" + i, ht["CreateName"].ToString()));
spList.Add(new SqlParameter("@ReceiverName" + i, list[i].ReceiverName));
spList.Add(new SqlParameter("@Timing" + i, ht["Timing"].ToString()));
spList.Add(new SqlParameter("@FilePath" + i, Convert.ToString(ht["FilePath"])));
}
int number = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org.ToString(), spList.ToArray());
if (number > 0)
{
trans.Commit();
return(number);
}
else
{
trans.Rollback();
return(-1);
}
}
catch (Exception ex)
{
trans.Rollback();
return(-1);
}
}
}
public bool DelVote(Hashtable ht)
{
using (SqlTransaction tans = SQLHelp.BeginTransaction())
{
try
{
string sql = "delete from System_Vote where Id=@Id";
if (ht.ContainsKey("Id") && !string.IsNullOrWhiteSpace(ht["Id"].SafeToString()))
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int number = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param);
if (number < 0)
{
tans.Rollback();
}
sql = "select * from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_sel = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, param_sel);
sql = "delete from System_VoteDetails where VoteId=@Id";
SqlParameter[] param_del = new SqlParameter[] {
new SqlParameter("@Id", ht["Id"].ToString())
};
int num = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, param_del);
if (dt != null && dt.Rows.Count > 0)
{
string detailsId = "";
foreach (DataRow row in dt.Rows)
{
detailsId += row["Id"].ToString() + ",";
}
if (detailsId.Length > 0)
{
detailsId = detailsId.Substring(0, detailsId.Length - 1);
sql = "delete from System_VoteRecord where DetailsId in (" + detailsId + ")";
int ok = SQLHelp.ExecuteNonQuery(sql, CommandType.Text, null);
tans.Commit();
return(true);
}
}
else
{
tans.Commit();
return(true);
}
}
return(false);
}
catch (Exception)
{
tans.Rollback();
return(false);
}
}
}
public int Edit_AchieveStatus(int id, int status)
{
string str = "update TPM_AcheiveRewardInfo set Status=@Status where Id=@Id";
List <SqlParameter> pms = new List <SqlParameter>();
pms.Add(new SqlParameter("@Id", id));
pms.Add(new SqlParameter("@Status", status));
return(SQLHelp.ExecuteNonQuery(str, CommandType.Text, pms.ToArray()));
}
public int DelAcheiveRewardInfo(int id)
{
string str = @"update TPM_AcheiveRewardInfo set IsDelete=1 where Id=@Id;
update TPM_RewardUserInfo set IsDelete=1 where RIId=@Id";
List <SqlParameter> pms = new List <SqlParameter>();
pms.Add(new SqlParameter("@Id", id));
return(SQLHelp.ExecuteNonQuery(str, CommandType.Text, pms.ToArray()));
}
public int ReaderMessage(Hashtable ht)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
StringBuilder sbSql4org = new StringBuilder();
if (ht.ContainsKey("Status") && !string.IsNullOrWhiteSpace(ht["Status"].ToString()))
{
sbSql4org.Append("update Com_Message set [Status]=" + ht["Status"].ToString() + " where Id in (");
}
else if (ht.ContainsKey("IsDelete") && !string.IsNullOrWhiteSpace(ht["IsDelete"].ToString()))
{
sbSql4org.Append("update Com_Message set [IsDelete]=" + ht["IsDelete"].ToString() + " where Id in (");
}
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrWhiteSpace(ht["ids"].ToString()))
{
var ids = ht["ids"].ToString().Split(',');
var strsql = "";
for (int i = 0; i < ids.Length; i++)
{
strsql += "@ids" + i + ",";
list.Add(new SqlParameter("@ids" + i, ids[i]));
}
if (!string.IsNullOrWhiteSpace(strsql))
{
strsql = strsql.Substring(0, strsql.Length - 1);
}
sbSql4org.Append(strsql + ")");
if (ht.ContainsKey("Receiver") && !string.IsNullOrEmpty(ht["Receiver"].ToString()))
{
sbSql4org.Append(" and Receiver=@Receiver");
list.Add(new SqlParameter("@Receiver", ht["Receiver"].ToString()));
}
int number = SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sbSql4org.ToString(), list.ToArray());
if (number > 0)
{
trans.Commit();
return(number);
}
else
{
trans.Rollback();
return(-1);
}
}
}
catch (Exception)
{
trans.Rollback();
return(-1);
}
}
return(0);
}
public Int32 RemoveEmployee(Int32 UserID, Int32 CurrentUserID)
{
List <SqlParameter> sqParam = new List <SqlParameter>();
sqParam.Add(new SqlParameter("@UserId", UserID));
sqParam.Add(new SqlParameter("@UpdatedBy", CurrentUserID));
Int32 i = SQLHelp.ExecuteNonQuery("Usp_DeleteEmployee", sqParam);
return(i);
}
public int DelRewBatchByRankId(int rank_Id)
{
string str = "update TPM_RewardBatch set IsDelete=1 where Rank_Id=@Rank_Id ";
List <SqlParameter> op_pms = new List <SqlParameter>();
op_pms.Add(new SqlParameter("@Rank_Id", rank_Id));
int result = SQLHelp.ExecuteNonQuery(str, CommandType.Text, op_pms.ToArray());
return(result);
}
public Int32 encryptPassword(String Username, String EncryptedPassword)
{
List <SqlParameter> sqParam = new List <SqlParameter>();
sqParam.Add(new SqlParameter("@Username", Username));
sqParam.Add(new SqlParameter("@Password", EncryptedPassword));
Int32 i = SQLHelp.ExecuteNonQuery("Usp_EncryptEmployeePassword", sqParam);
return(i);
}
public void sendEmail(int type)
{
using (SqlTransaction trans = SQLHelp.BeginTransaction())
{
try
{
string sql = "select * from System_Message where isSend=" + (int)isSend.未发送 + " and IsDelete!=" + (int)SysStatus.除 + " and [Type]=" + type + " and Timing=" + (int)MessageTiming.定时发送;
DataTable dt = SQLHelp.ExecuteDataTable(sql, CommandType.Text);
string ids = "";
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DateTime pushDate = Convert.ToDateTime(row["CreateTime"]);
DateTime curDate = DateTime.Now;
TimeSpan ts = pushDate - curDate;
if (ts.Minutes <= 2)
{
ids += row["Id"].ToString() + ",";
}
if (!string.IsNullOrWhiteSpace(ids) && ids.Length > 0)
{
ids = ids.Substring(0, ids.Length - 1);
string upSql = "update System_Message set isSend=" + (int)isSend.已发送 + " where Id in (" + ids + ")";
int number = SQLHelp.ExecuteNonQuery(upSql, CommandType.Text);
trans.Commit();
}
}
foreach (DataRow row in dt.Rows)
{
string Subject = string.IsNullOrWhiteSpace(Convert.ToString(row["Title"])) ? "无题" : row["Title"].ToString();
string Body = string.IsNullOrWhiteSpace(Convert.ToString(row["Contents"])) ? "无内容" : row["Contents"].ToString();
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["ReceiverEmail"])))
{
string href = "";
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["CreatorName"])))
{
Body += "<br/><h4>发件人：" + row["CreatorName"].ToString() + "</h4>";
}
if (!string.IsNullOrWhiteSpace(Convert.ToString(row["Href"])))
{
href = "<br/><h3><a href=" + row["Href"].ToString() + ">点击此处可查看详细信息</a></h3>";
}
SendMailMessage.SendMessage(Subject, Body + href, row["ReceiverEmail"].ToString());
}
}
}
}
catch (Exception ex)
{
_logger.Info("EmailPost：" + ex.Message);
trans.Rollback();
}
}
}
public int DelDocByRelId(int type, int relationid)
{
string str = "update Sys_Document set IsDelete=1 where Type=@Type and RelationId=@RelationId";
List <SqlParameter> op_pms = new List <SqlParameter>();
op_pms.Add(new SqlParameter("@Type", type));
op_pms.Add(new SqlParameter("@RelationId", relationid));
int result = SQLHelp.ExecuteNonQuery(str, CommandType.Text, op_pms.ToArray());
return(result);
}
public int Del_RewardBatch(int itemid)
{
int result = 0;
List <SqlParameter> pms = new List <SqlParameter>();
StringBuilder str = new StringBuilder();
str.Append("update TPM_RewardBatch set IsDelete=1 where Id=@Id;update TPM_RewardBatchDetail set IsDelete=1 where RewardBatch_Id=@Id ");
pms.Add(new SqlParameter("@Id", itemid));
result = SQLHelp.ExecuteNonQuery(str.ToString(), CommandType.Text, pms.ToArray());
return(result);
}
public int[] Oper_AuditAllotReward(TPM_AuditReward audModel, List <TPM_AllotReward> items)
{
int result = 0;
List <SqlParameter> pms = new List <SqlParameter>();
StringBuilder str = new StringBuilder();
pms.Add(new SqlParameter("@Acheive_Id", audModel.Acheive_Id));
pms.Add(new SqlParameter("@RewardBatch_Id", audModel.RewardBatch_Id));
string sql = "select Id from TPM_AuditReward where Acheive_Id=@Acheive_Id and RewardBatch_Id=@RewardBatch_Id and IsDelete=0";
int auditid = Convert.ToInt32(SQLHelp.ExecuteScalar(sql, CommandType.Text, pms.ToArray()));
if (auditid > 0)
{
pms.Add(new SqlParameter("@Status", audModel.Status));
str.Append("update TPM_AuditReward set Status=@Status where Id=@Audit_Id;");
}
else
{
auditid = new TPM_AuditRewardDal().Add(audModel);
}
pms.Add(new SqlParameter("@Audit_Id", auditid));
if (items.Count() > 0)
{
for (int i = 0; i < items.Count; i++)
{
TPM_AllotReward item = items[i];
if (item.AllotMoney == null)
{
pms.Add(new SqlParameter("@AllotMoney" + i, DBNull.Value));
}
else
{
pms.Add(new SqlParameter("@AllotMoney" + i, item.AllotMoney));
}
pms.Add(new SqlParameter("@RewardUser_Id" + i, item.RewardUser_Id));
pms.Add(new SqlParameter("@CreateUID" + i, item.CreateUID));
pms.Add(new SqlParameter("@EditUID" + i, item.CreateUID));
string allotsql = "select Id from TPM_AllotReward where Audit_Id=@Audit_Id and RewardUser_Id=@RewardUser_Id" + i + " and IsDelete=0";
int allotid = Convert.ToInt32(SQLHelp.ExecuteScalar(allotsql, CommandType.Text, pms.ToArray()));
if (allotid > 0)
{
pms.Add(new SqlParameter("@Id" + i, allotid));
str.Append("update TPM_AllotReward set AllotMoney=@AllotMoney" + i + ",EditUID=@EditUID" + i + " where Id=@Id" + i + ";");
}
else
{
str.Append(@"insert into TPM_AllotReward(Audit_Id,AllotMoney,RewardUser_Id,CreateUID)
values(@Audit_Id,@AllotMoney" + i + ",@RewardUser_Id" + i + ",@CreateUID" + i + ");");
}
}
result = SQLHelp.ExecuteNonQuery(str.ToString(), CommandType.Text, pms.ToArray());
}
return(new int[] { result, auditid });
}
public static int edit_report_sender(int table_id, string report_reader, string report_sender)
{
SqlParameter[] param =
{
new SqlParameter("@table_id", table_id),
new SqlParameter("@report_reader", report_reader),
new SqlParameter("@report_sender", report_sender)
};
object obj = SQLHelp.ExecuteNonQuery("edit_report_sender", CommandType.StoredProcedure, param);
return(Convert.ToInt32(obj));;
}
public int Edit_Rank(List <TMP_RewardRank> items)
{
string str = "";
List <SqlParameter> pms = new List <SqlParameter>();
for (int i = 0; i < items.Count; i++)
{
TMP_RewardRank item = items[i];
str += "update TMP_RewardRank set Score=@Score" + i + " where Id=@Id" + i + ";";
pms.Add(new SqlParameter("@Id" + i, item.Id));
pms.Add(new SqlParameter("@Score" + i, item.Score));
}
return(SQLHelp.ExecuteNonQuery(str, CommandType.Text, pms.ToArray()));
}
/// <summary>
/// 更新单挑数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual bool Update(T entity, SqlTransaction trans = null)
{
string sql = string.Empty;
List <SqlParameter> pms = DalUpdateHelp(entity, out sql);
if (trans == null)
{
return(SQLHelp.ExecuteNonQuery(sql, System.Data.CommandType.Text, pms.ToArray()) > 0);
}
else
{
return(SQLHelp.ExecuteNonQuery(trans, System.Data.CommandType.Text, sql, pms.ToArray()) > 0);
}
}