public static int SaveAgendaInfo(PersonalLinkman model)
{
if (ConstUtil.EDIT_FLAG_INSERT.Equals(model.EditFlag))
{
return AddressBookDBHelper.InsertAgendaInfo(model);
}
else if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag))
{
return AddressBookDBHelper.UpdateAgendaInfo(model) == true ? 0 : -1;
}
return -1;
}
public static DataTable SearchAddressBook(PersonalLinkman model, int pageindex, int pagesize, string orderby, ref int count)
{
UserInfoUtil userinfo = ((UserInfoUtil)SessionUtil.Session["UserInfo"]);
DataTable dt = new DataTable();
string sqlstr = "select * from officedba.PersonalLinkman where Creator=" + userinfo.EmployeeID;
SqlCommand comm = new SqlCommand();
if (model.Birthday != null && model.Birthday != "")
{
sqlstr += " and Birthday=@Birthday ";
comm.Parameters.AddWithValue("@Birthday", SqlDbType.DateTime);
comm.Parameters["@Birthday"].Value = Convert.ToDateTime(model.Birthday);
}
if (model.Name != null && model.Name != "")
{
sqlstr += " and LinkmanName like @Name ";
comm.Parameters.AddWithValue("@Name", SqlDbType.VarChar);
comm.Parameters["@Name"].Value = "%" + model.Name + "%";
}
if (model.MobilePhone != null && model.MobilePhone != "")
{
sqlstr += " and MobilePhone=@MobilePhone ";
comm.Parameters.AddWithValue("@MobilePhone", SqlDbType.VarChar);
comm.Parameters["@MobilePhone"].Value = model.MobilePhone;
}
if (model.CompanyPhone != null && model.CompanyPhone != "")
{
sqlstr += " and CompanyPhone=@CompanyPhone ";
comm.Parameters.AddWithValue("@CompanyPhone", SqlDbType.VarChar);
comm.Parameters["@CompanyPhone"].Value = model.CompanyPhone;
}
if (model.QQ != null && model.QQ != "")
{
sqlstr += " and QQ=@QQ ";
comm.Parameters.AddWithValue("@QQ", SqlDbType.VarChar);
comm.Parameters["@QQ"].Value = model.QQ;
}
if (model.CompanyName != null && model.CompanyName != "")
{
sqlstr += " and CompanyName like @CompanyName ";
comm.Parameters.AddWithValue("@CompanyName", SqlDbType.VarChar);
comm.Parameters["@CompanyName"].Value = "%" + model.CompanyName + "%";
}
if (model.LinkmanGroupID != 0)
{
sqlstr += " and LinkmanGroupID=@LinkmanGroupID ";
comm.Parameters.AddWithValue("@LinkmanGroupID", SqlDbType.VarChar);
comm.Parameters["@LinkmanGroupID"].Value = model.LinkmanGroupID;
}
SqlParameter[] parmlist = new SqlParameter[comm.Parameters.Count];
int i = 0;
foreach (SqlParameter sp in comm.Parameters)
{
parmlist[i] = new SqlParameter(sp.ParameterName, sp.DbType);
parmlist[i].Value = sp.Value;
i++;
}
return dt = SqlHelper.CreateSqlByPageExcuteSql(sqlstr, pageindex, pagesize, orderby, parmlist, ref count);
}
public static int InsertAgendaInfo(PersonalLinkman model) {
string SQLstr = "";
SQLstr += @"INSERT INTO [officedba].[PersonalLinkman]
([CompanyCD]
,[Creator]
,[LinkmanGroupID]
,[LinkmanName]
,[Sex]
,[CompanyName]
,[Birthday]
,[MobilePhone]
,[CompanyPhone]
,[Email]
,[Fax]
,[QQ]
,[ICQ]
,[MSN]
,[CompanyWebsite]
,[CompanyAddress]
,[principalship]
,[Remark])
VALUES
(@CompanyCD
,@Creator
,@LinkmanGroupID
,@Name
,@Sex
,@CompanyName
,@Birthday
,@MobilePhone
,@CompanyPhone
,@Email
,@Fax
,@QQ
,@ICQ
,@MSN
,@CompanyWebsite
,@CompanyAddress
,@principalship
,@Remark );select @SourceID = @@IDENTITY ";
if (model.Birthday == "") {
SQLstr = SQLstr.Replace(",[Birthday]", "").Replace(",@Birthday","");
}
SqlCommand comm = new SqlCommand();
comm.CommandText = SQLstr;
comm.Parameters.AddWithValue("@CompanyCD", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
comm.Parameters.AddWithValue("@LinkmanGroupID", SqlDbType.Int);
comm.Parameters.AddWithValue("@Name", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Sex", SqlDbType.Char);
comm.Parameters.AddWithValue("@CompanyName", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Birthday", SqlDbType.DateTime);
comm.Parameters.AddWithValue("@MobilePhone", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyPhone", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Email", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Fax", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@QQ", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@ICQ", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@MSN", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyWebsite", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyAddress", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@principalship", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Remark", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@SourceID", SqlDbType.Int);
comm.Parameters["@CompanyCD"].Value = model.CompanyCD;
comm.Parameters["@Creator"].Value = model.Creator;
comm.Parameters["@LinkmanGroupID"].Value = model.LinkmanGroupID;
comm.Parameters["@Name"].Value = model.Name;
comm.Parameters["@Sex"].Value = model.Sex;
comm.Parameters["@CompanyName"].Value = model.CompanyName;
if (model.Birthday == "")
{
comm.Parameters["@Birthday"].Value = DateTime.Now;
}
else {
comm.Parameters["@Birthday"].Value = Convert.ToDateTime(model.Birthday);
}
comm.Parameters["@MobilePhone"].Value = model.MobilePhone;
comm.Parameters["@CompanyPhone"].Value = model.CompanyPhone;
comm.Parameters["@Email"].Value = model.Email;
comm.Parameters["@Fax"].Value = model.Fax;
comm.Parameters["@QQ"].Value = model.QQ;
comm.Parameters["@ICQ"].Value = model.ICQ;
comm.Parameters["@MSN"].Value = model.MSN;
comm.Parameters["@CompanyWebsite"].Value = model.CompanyWebsite;
comm.Parameters["@CompanyAddress"].Value = model.CompanyAddress;
comm.Parameters["@principalship"].Value = model.principalship;
comm.Parameters["@Remark"].Value = model.Remark;
comm.Parameters["@SourceID"].Direction = ParameterDirection.Output;
if (SqlHelper.ExecuteTransWithCommand(comm))
{
int temp=-1;
if (comm.Parameters["@SourceID"].Value != null)
temp = Convert.ToInt32(comm.Parameters["@SourceID"].Value);
return temp;
}
else {
return -1;
}
}
public static XBase.Model.Personal.AddressBook.PersonalLinkman GetPersonalLinkmanModel(int ID)
{
XBase.Model.Personal.AddressBook.PersonalLinkman model = new PersonalLinkman();
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ");
strSql.Append(" ID,CompanyCD,Creator,LinkmanGroupID,LinkmanName,Sex,CompanyName,Birthday,MobilePhone,CompanyPhone,Email,Fax,QQ,ICQ,MSN,CompanyWebsite,CompanyAddress,principalship,Remark ");
strSql.Append(" from officedba.PersonalLinkman ");
strSql.Append(" where ID=" + ID + " ");
DataSet ds = new DataSet();
DataTable dt = SqlHelper.ExecuteSql(strSql.ToString());
ds.Tables.Add(dt);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
{
model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
}
model.CompanyCD = ds.Tables[0].Rows[0]["CompanyCD"].ToString();
if (ds.Tables[0].Rows[0]["Creator"].ToString() != "")
{
model.Creator = int.Parse(ds.Tables[0].Rows[0]["Creator"].ToString());
}
if (ds.Tables[0].Rows[0]["LinkmanGroupID"].ToString() != "")
{
model.LinkmanGroupID = int.Parse(ds.Tables[0].Rows[0]["LinkmanGroupID"].ToString());
}
model.Name = ds.Tables[0].Rows[0]["LinkmanName"].ToString();
model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
model.CompanyName = ds.Tables[0].Rows[0]["CompanyName"].ToString();
if (ds.Tables[0].Rows[0]["Birthday"].ToString() != "")
{
model.Birthday = ds.Tables[0].Rows[0]["Birthday"].ToString();
}
model.MobilePhone = ds.Tables[0].Rows[0]["MobilePhone"].ToString();
model.CompanyPhone = ds.Tables[0].Rows[0]["CompanyPhone"].ToString();
model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
model.Fax = ds.Tables[0].Rows[0]["Fax"].ToString();
model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
model.ICQ = ds.Tables[0].Rows[0]["ICQ"].ToString();
model.MSN = ds.Tables[0].Rows[0]["MSN"].ToString();
model.CompanyWebsite = ds.Tables[0].Rows[0]["CompanyWebsite"].ToString();
model.CompanyAddress = ds.Tables[0].Rows[0]["CompanyAddress"].ToString();
model.principalship = ds.Tables[0].Rows[0]["principalship"].ToString();
model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
}
return model;
}
public static bool UpdateAgendaInfo(PersonalLinkman model)
{
string SQLstr = "";
SQLstr += @"UPDATE [officedba].[PersonalLinkman]
SET [CompanyCD] = @CompanyCD
,[Creator] = @Creator
,[LinkmanGroupID] = @LinkmanGroupID
,[LinkmanName] = @Name
,[Sex] = @Sex
,[CompanyName] = @CompanyName
,[Birthday] = @Birthday
,[MobilePhone] =@MobilePhone
,[CompanyPhone] = @CompanyPhone
,[Email] = @Email
,[Fax] = @Fax
,[QQ] = @QQ
,[ICQ] = @ICQ
,[MSN] = @MSN
,[CompanyWebsite] = @CompanyWebsite
,[CompanyAddress] = @CompanyAddress
,[principalship] = @principalship
,[Remark] = @Remark
WHERE ID=" + model.ID;
if (model.Birthday == "")
{
SQLstr = SQLstr.Replace(",[Birthday] = @Birthday", "");
}
SqlCommand comm = new SqlCommand();
comm.CommandText = SQLstr;
comm.Parameters.AddWithValue("@CompanyCD", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
comm.Parameters.AddWithValue("@LinkmanGroupID", SqlDbType.Int);
comm.Parameters.AddWithValue("@Name", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Sex", SqlDbType.Char);
comm.Parameters.AddWithValue("@CompanyName", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Birthday", SqlDbType.DateTime);
comm.Parameters.AddWithValue("@MobilePhone", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyPhone", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Email", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Fax", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@QQ", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@ICQ", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@MSN", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyWebsite", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@CompanyAddress", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@principalship", SqlDbType.VarChar);
comm.Parameters.AddWithValue("@Remark", SqlDbType.VarChar);
comm.Parameters["@CompanyCD"].Value = model.CompanyCD;
comm.Parameters["@Creator"].Value = model.Creator;
comm.Parameters["@LinkmanGroupID"].Value = model.LinkmanGroupID;
comm.Parameters["@Name"].Value = model.Name;
comm.Parameters["@Sex"].Value = model.Sex;
comm.Parameters["@CompanyName"].Value = model.CompanyName;
if (model.Birthday == "")
{
comm.Parameters["@Birthday"].Value = DateTime.Now;
}
else
{
comm.Parameters["@Birthday"].Value = Convert.ToDateTime(model.Birthday);
}
comm.Parameters["@MobilePhone"].Value = model.MobilePhone;
comm.Parameters["@CompanyPhone"].Value = model.CompanyPhone;
comm.Parameters["@Email"].Value = model.Email;
comm.Parameters["@Fax"].Value = model.Fax;
comm.Parameters["@QQ"].Value = model.QQ;
comm.Parameters["@ICQ"].Value = model.ICQ;
comm.Parameters["@MSN"].Value = model.MSN;
comm.Parameters["@CompanyWebsite"].Value = model.CompanyWebsite;
comm.Parameters["@CompanyAddress"].Value = model.CompanyAddress;
comm.Parameters["@principalship"].Value = model.principalship;
comm.Parameters["@Remark"].Value = model.Remark;
return SqlHelper.ExecuteTransWithCommand(comm);
}
