/// <summary>
/// 插入项目档案
/// </summary>
/// <param name="model"></param>
/// <param name="ht"></param>
/// <param name="ID"></param>
/// <returns></returns>
public static bool InsertProjectInfo(ProjectInfoModel model, Hashtable ht, out string ID,string startDate,string endDate)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ID = "0";
try
{
bool succ = false;
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
LogInfoModel logModel = InitLogInfo(model.ProjectNo, 0);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
succ = ProjectInfoDBHelper.InsertProjectInfo(model, ht,out ID,startDate,endDate);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, 0, ex);
return false;
}
}
/// <summary>
/// 获取项目信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetProjectInfo(ProjectInfoModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder infoSql = new StringBuilder();
infoSql.AppendLine("select a.ProjectNo,a.ID,a.ProjectName,a.CustID,a.CustLinkMan,a.LinkTel,");
infoSql.AppendLine(" CASE a.StartDate WHEN NULL THEN '' ELSE CONVERT(CHAR(10), a.StartDate,23) END as StartDate,a.LinkMan,a.Tel,");
infoSql.AppendLine(" CASE a.EndDate WHEN NULL THEN '' ELSE CONVERT(CHAR(10), a.EndDate,23) END as EndDate,a.Address,a.OverView,a.Remark,");
infoSql.AppendLine(" c.EmployeeName as LinkManName,Convert(numeric(14," + ((UserInfoUtil)SessionUtil.Session["UserInfo"]).SelPoint + "),a.Investment) as Investment,");
infoSql.AppendLine(" d.CustName,a.CanViewUser,[dbo].[getEmployeeNameString](a.CanViewUser) as CanViewUserName,");
infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
infoSql.AppendLine(" a.ExtField11,a.ExtField12,a.ExtField13,a.ExtField14,a.ExtField15,a.ExtField16,a.ExtField17,a.ExtField18,a.ExtField19,a.ExtField20 ");
infoSql.AppendLine("from officedba.ProjectInfo a");
infoSql.AppendLine("left join officedba.EmployeeInfo c on a.LinkMan=c.ID");
infoSql.AppendLine("left join officedba.CustInfo d on a.CustID=d.ID");
infoSql.AppendLine("Where a.ID=@ID");
//过滤单据：显示当前用户拥有权限查看的单据
int empid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
infoSql.AppendLine(" and ( charindex('," + empid + ",' , ','+a.CanViewUser+',')>0 or a.Creator=" + empid + " OR a.CanViewUser='' OR a.CanViewUser is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = infoSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 项目档案详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetProjectInfo(ProjectInfoModel model)
{
try
{
return ProjectInfoDBHelper.GetProjectInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通过检索条件查询项目档案列表信息
/// </summary>
/// <param name="model"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetProjectInfoListBycondition(ProjectInfoModel model,string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return ProjectInfoDBHelper.GetProjectInfoListBycondition(model,EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(ProjectInfoModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.ProjectInfo set ";
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND ProjectNo = @ProjectNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@ProjectNo", model.ProjectNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
/// <summary>
/// 修改项目档案
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool UpdateProjectInfo(ProjectInfoModel model, Hashtable htExtAttr,string StartDate,string EndDate)
{
//获取登陆用户ID
ArrayList listADD = new ArrayList();
if (model.ID <= 0)
{
return false;
}
#region 主生产计划单修改SQL语句
StringBuilder sqlEdit = new StringBuilder();
sqlEdit.AppendLine("UPDATE officedba.ProjectInfo");
sqlEdit.AppendLine(" SET ProjectName = @ProjectName");
sqlEdit.AppendLine(" ,CustID = @CustID");
sqlEdit.AppendLine(" ,CustLinkMan = @CustLinkMan");
sqlEdit.AppendLine(" ,LinkTel = @LinkTel");
sqlEdit.AppendLine(" ,LinkMan = @LinkMan");
sqlEdit.AppendLine(" ,Tel = @Tel");
sqlEdit.AppendLine(" ,Address = @Address");
if (!string.IsNullOrEmpty(StartDate))
{
sqlEdit.AppendLine(" ,StartDate = @StartDate");
}
if (!string.IsNullOrEmpty(EndDate))
{
sqlEdit.AppendLine(" ,EndDate = @EndDate");
}
sqlEdit.AppendLine(" ,OverView = @OverView");
sqlEdit.AppendLine(" ,Remark = @Remark");
sqlEdit.AppendLine(" ,CanViewUser = @CanViewUser");
sqlEdit.AppendLine(" ,Investment = @Investment");
sqlEdit.AppendLine(" ,ModifiedDate = getdate()");
sqlEdit.AppendLine(" WHERE ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlEdit.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectName", model.ProjectName));
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
comm.Parameters.Add(SqlHelper.GetParameter("@CustLinkMan", model.CustLinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkTel", model.LinkTel));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkMan", model.LinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@Tel", model.Tel));
comm.Parameters.Add(SqlHelper.GetParameter("@Address", model.Address));
if (!string.IsNullOrEmpty(StartDate))
{
comm.Parameters.Add(SqlHelper.GetParameter("@StartDate", StartDate));
}
if (!string.IsNullOrEmpty(EndDate))
{
comm.Parameters.Add(SqlHelper.GetParameter("@EndDate", EndDate));
}
comm.Parameters.Add(SqlHelper.GetParameter("@OverView", model.OverView));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", model.CanViewUser));
comm.Parameters.Add(SqlHelper.GetParameter("@Investment", model.Investment));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 项目档案插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertProjectInfo(ProjectInfoModel model, Hashtable htExtAttr, out string ID, string StartDate, string EndDate)
{
ArrayList listADD = new ArrayList();
bool result = false;
#region 项目档案添加SQL语句
StringBuilder sqlPro = new StringBuilder();
sqlPro.AppendLine("INSERT INTO officedba.ProjectInfo");
sqlPro.AppendLine(" (CompanyCD");
sqlPro.AppendLine(" ,ProjectNo");
sqlPro.AppendLine(" ,ProjectName");
sqlPro.AppendLine(" ,CustID");
sqlPro.AppendLine(" ,CustLinkMan");
sqlPro.AppendLine(" ,LinkTel");
sqlPro.AppendLine(" ,LinkMan");
sqlPro.AppendLine(" ,Tel");
sqlPro.AppendLine(" ,Address");
if (!string.IsNullOrEmpty(StartDate))
{
sqlPro.AppendLine(" ,StartDate");
}
if (!string.IsNullOrEmpty(EndDate))
{
sqlPro.AppendLine(" ,EndDate");
}
sqlPro.AppendLine(" ,OverView");
sqlPro.AppendLine(" ,Remark");
sqlPro.AppendLine(" ,Creator");
sqlPro.AppendLine(" ,CreateDate");
sqlPro.AppendLine(" ,Investment");
sqlPro.AppendLine(" ,ModifiedDate");
sqlPro.AppendLine(" ,CanViewUser)");
sqlPro.AppendLine(" VALUES");
sqlPro.AppendLine(" (@CompanyCD");
sqlPro.AppendLine(" ,@ProjectNo");
sqlPro.AppendLine(" ,@ProjectName");
sqlPro.AppendLine(" ,@CustID");
sqlPro.AppendLine(" ,@CustLinkMan");
sqlPro.AppendLine(" ,@LinkTel");
sqlPro.AppendLine(" ,@LinkMan");
sqlPro.AppendLine(" ,@Tel");
sqlPro.AppendLine(" ,@Address");
if (!string.IsNullOrEmpty(StartDate))
{
sqlPro.AppendLine(" ,@StartDate");
}
if (!string.IsNullOrEmpty(EndDate))
{
sqlPro.AppendLine(" ,@EndDate");
}
sqlPro.AppendLine(" ,@OverView");
sqlPro.AppendLine(" ,@Remark");
sqlPro.AppendLine(" ,@Creator");
sqlPro.AppendLine(" ,@CreateDate");
sqlPro.AppendLine(" ,@Investment");
sqlPro.AppendLine(" ,getdate()");
sqlPro.AppendLine(" ,@CanViewUser)");
sqlPro.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlPro.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectNo", model.ProjectNo));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectName", model.ProjectName));
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
comm.Parameters.Add(SqlHelper.GetParameter("@CustLinkMan", model.CustLinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkTel", model.LinkTel));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkMan", model.LinkMan));
comm.Parameters.Add(SqlHelper.GetParameter("@Tel", model.Tel));
comm.Parameters.Add(SqlHelper.GetParameter("@Address", model.Address));
if (!string.IsNullOrEmpty(StartDate))
{
comm.Parameters.Add(SqlHelper.GetParameter("@StartDate", StartDate));
}
if (!string.IsNullOrEmpty(EndDate))
{
comm.Parameters.Add(SqlHelper.GetParameter("@EndDate", EndDate));
}
comm.Parameters.Add(SqlHelper.GetParameter("@OverView", model.OverView));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@Investment", model.Investment));
comm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", model.CanViewUser));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
listADD.Add(comm);
#endregion
try
{
#region 扩展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
ID = comm.Parameters["@ID"].Value.ToString();
result = true;
}
else
{
ID = "0";
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通过检索条件查询项目档案列表
/// </summary>
/// <param name="model"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetProjectInfoListBycondition(ProjectInfoModel model,string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select a.ID,a.ProjectNo,a.ProjectName,a.CustID,b.CustName,");
searchSql.AppendLine(" a.LinkMan,c.EmployeeName as LinkManName,a.ModifiedDate,");
searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.StartDate, 23),'') as StartDate,");
searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.EndDate, 23),'') as EndDate,Convert(numeric(14," + ((UserInfoUtil)SessionUtil.Session["UserInfo"]).SelPoint + "),a.Investment) as Investment ");
searchSql.AppendLine("from officedba.ProjectInfo a");
searchSql.AppendLine("left join officedba.CustInfo b on a.CustID=b.ID");
searchSql.AppendLine("left join officedba.EmployeeInfo c on a.LinkMan=c.ID");
searchSql.AppendLine("where a.CompanyCD=@CompanyCD");
int empid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
searchSql.AppendLine(" and ( charindex('," + empid + ",' , ','+a.CanViewUser+',')>0 or a.Creator=" + empid + " OR a.CanViewUser='' OR a.CanViewUser is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//单据编号
if (!string.IsNullOrEmpty(model.ProjectNo))
{
searchSql.AppendLine("and a.ProjectNo like @ProjectNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectNo", "%" + model.ProjectNo + "%"));
}
//项目名称
if (!string.IsNullOrEmpty(model.ProjectName))
{
searchSql.AppendLine(" and a.ProjectName like @ProjectName");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectName", "%" + model.ProjectName + "%"));
}
//客户ID
if (model.CustID > 0)
{
searchSql.AppendLine(" and a.CustID=@CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", model.CustID.ToString()));
}
//我方负责人
if (model.LinkMan > 0)
{
searchSql.AppendLine(" and a.LinkMan=@LinkMan ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@LinkMan", model.LinkMan.ToString()));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}