You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
447 lines
18 KiB
C#
447 lines
18 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.SoftMng.Models.MsSoftRequire;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
using HcUtility.Comm;
|
|
using DSWeb.MvcShipping.DAL.MsBaseInfoDAL;
|
|
|
|
|
|
namespace DSWeb.SoftMng.DAL.MsSoftRequire
|
|
{
|
|
public class MsSoftRequireDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<SoftRequire> GetDataList(string strCondition, string userid, string sort = null)
|
|
{
|
|
|
|
var isopen = MsBaseInfoDAL.GetUserModuleEnable("modsoftModifyAudit", userid);
|
|
var isopenstr = "";
|
|
if (isopen == false)
|
|
{
|
|
isopenstr = " (( INPUTBY='" + userid + "') OR (MODIFYBY='" + userid + "') OR (SALE='" + userid + "')) ";
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
strCondition = strCondition + " and " + isopenstr;
|
|
else
|
|
strCondition = isopenstr;
|
|
}
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT GID,BSNO,BSSTATUS,SOFTVER,MODULENAME,EMERGENCYCLASS,CUSTOMERNAME,CUSTDATE,REQUIRETYPE");
|
|
strSql.Append(",REQUIREDETAIL,AUDITBY,AUDITTIME,AUDITRESULT,MODIFYBY,MODIFYSTTIME,MODIFYEDTIME,MODIFYDETAIL");
|
|
strSql.Append(",REMARKS,INPUTBY,INPUTTIME,OVERBY,OVERTIME,PROJECTID,SALE,SALESTATUS,SALEAUDITTIME");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.SALE) as SALEREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.AUDITBY) as AUDITBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.MODIFYBY) as MODIFYBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.OVERBY) as OVERBYREF");
|
|
strSql.Append(",(select PROJECTNAME from [op_softproject] where GID=op_softrequire.PROJECTID) as PROJECTNAME");
|
|
strSql.Append(" from op_softrequire ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else {
|
|
strSql.Append(" order by INPUTTIME desc");
|
|
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
static public string GetDataListStr(string strCondition, string sort = null)
|
|
{
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT GID,BSNO,BSSTATUS,SOFTVER,MODULENAME,EMERGENCYCLASS,CUSTOMERNAME,CUSTDATE,REQUIRETYPE");
|
|
strSql.Append(",REQUIREDETAIL,AUDITBY,AUDITTIME,AUDITRESULT,MODIFYBY,MODIFYSTTIME,MODIFYEDTIME,MODIFYDETAIL");
|
|
strSql.Append(",REMARKS,INPUTBY,INPUTTIME,OVERBY,OVERTIME,PROJECTID,SALE,SALESTATUS,SALEAUDITTIME");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.SALE) as SALEREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.AUDITBY) as AUDITBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.MODIFYBY) as MODIFYBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire.OVERBY) as OVERBYREF");
|
|
strSql.Append(",(select PROJECTNAME from [op_softproject] where GID=op_softrequire.PROJECTID) as PROJECTNAME");
|
|
strSql.Append(" from op_softrequire ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by EMERGENCYCLASS,CUSTDATE,BSNO");
|
|
|
|
}
|
|
return strSql.ToString();
|
|
}
|
|
|
|
|
|
static public SoftRequire GetData(string condition, string companyid)
|
|
{
|
|
SoftRequire data = null;
|
|
var list = GetDataList(condition,companyid);
|
|
if (list.Count > 0)
|
|
data = list[0];
|
|
|
|
if (data == null)
|
|
{
|
|
data = new SoftRequire();
|
|
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
|
|
private static List<SoftRequire> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<SoftRequire>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
SoftRequire data = new SoftRequire();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.BSNO = Convert.ToString(reader["BSNO"]);
|
|
data.BSSTATUS = Convert.ToString(reader["BSSTATUS"]);
|
|
data.SOFTVER = Convert.ToString(reader["SOFTVER"]);
|
|
data.PROJECTID = Convert.ToString(reader["PROJECTID"]);
|
|
data.PROJECTNAME = Convert.ToString(reader["PROJECTNAME"]);
|
|
data.MODULENAME = Convert.ToString(reader["MODULENAME"]);
|
|
data.EMERGENCYCLASS = Convert.ToString(reader["EMERGENCYCLASS"]);
|
|
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
|
|
if (reader["CUSTDATE"] != DBNull.Value)
|
|
data.CUSTDATE = Convert.ToDateTime(reader["CUSTDATE"]).ToString("yyyy-MM-dd");
|
|
data.REQUIRETYPE = Convert.ToString(reader["REQUIRETYPE"]);
|
|
data.REQUIREDETAIL = Convert.ToString(reader["REQUIREDETAIL"]);
|
|
data.AUDITBY = Convert.ToString(reader["AUDITBY"]);
|
|
data.AUDITBYREF = Convert.ToString(reader["AUDITBYREF"]);
|
|
data.AUDITRESULT = Convert.ToString(reader["AUDITRESULT"]);
|
|
if (reader["AUDITTIME"] != DBNull.Value)
|
|
data.AUDITTIME = Convert.ToDateTime(reader["AUDITTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.MODIFYBY = Convert.ToString(reader["MODIFYBY"]);
|
|
data.MODIFYBYREF = Convert.ToString(reader["MODIFYBYREF"]);
|
|
data.MODIFYDETAIL = Convert.ToString(reader["MODIFYDETAIL"]);
|
|
if (reader["MODIFYSTTIME"] != DBNull.Value)
|
|
data.MODIFYSTTIME = Convert.ToDateTime(reader["MODIFYSTTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
if (reader["MODIFYEDTIME"] != DBNull.Value)
|
|
data.MODIFYEDTIME = Convert.ToDateTime(reader["MODIFYEDTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
data.INPUTBYREF = Convert.ToString(reader["INPUTBYREF"]);
|
|
if (reader["INPUTTIME"] != DBNull.Value)
|
|
data.INPUTTIME = Convert.ToDateTime(reader["INPUTTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.OVERBY = Convert.ToString(reader["OVERBY"]);
|
|
data.OVERBYREF = Convert.ToString(reader["OVERBYREF"]);
|
|
if (reader["OVERTIME"] != DBNull.Value)
|
|
data.OVERTIME = Convert.ToDateTime(reader["OVERTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.SALE = Convert.ToString(reader["SALE"]);
|
|
data.SALEREF = Convert.ToString(reader["SALEREF"]);
|
|
data.SALESTATUS = Convert.ToString(reader["SALESTATUS"]);
|
|
if (reader["SALEAUDITTIME"] != DBNull.Value)
|
|
data.SALEAUDITTIME = Convert.ToDateTime(reader["SALEAUDITTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult DeleteSoftRequire(List<SoftRequire> headData)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
if (headData != null)
|
|
{
|
|
foreach (var enumValue in headData)
|
|
{
|
|
|
|
var cmdDelete = db.GetSqlStringCommand("delete from op_softrequire where GID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
}
|
|
}
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region 软件修改明细
|
|
|
|
static public List<SoftRequireDetail> GetDetailList(string strCondition, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT GID,LINKGID,MODIFYFILE,MODIFYBY,MODIFYTIME,MODIFYDETAIL,REMARKS,INPUTBY,INPUTTIME ");
|
|
strSql.Append(",(select ShowName from [user] where GID=op_softrequire_detail.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(" from op_softrequire_detail ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by INPUTTIME");
|
|
|
|
}
|
|
return SetDetailData(strSql);
|
|
}
|
|
|
|
static public SoftRequireDetail GetDetailData(string condition)
|
|
{
|
|
SoftRequireDetail data = null;
|
|
var list = GetDetailList(condition);
|
|
if (list.Count > 0)
|
|
data = list[0];
|
|
|
|
if (data == null)
|
|
{
|
|
data = new SoftRequireDetail();
|
|
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
|
|
private static List<SoftRequireDetail> SetDetailData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<SoftRequireDetail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
SoftRequireDetail data = new SoftRequireDetail();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.LINKGID = Convert.ToString(reader["LINKGID"]);
|
|
data.MODIFYFILE = Convert.ToString(reader["MODIFYFILE"]);
|
|
data.MODIFYDETAIL = Convert.ToString(reader["MODIFYDETAIL"]);
|
|
if (reader["MODIFYTIME"] != DBNull.Value)
|
|
data.MODIFYTIME = Convert.ToDateTime(reader["MODIFYTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
data.INPUTBYREF = Convert.ToString(reader["INPUTBYREF"]);
|
|
if (reader["INPUTTIME"] != DBNull.Value)
|
|
data.INPUTTIME = Convert.ToDateTime(reader["INPUTTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
public static DBResult SaveDetail(List<SoftRequireDetail> bodyList, string PID, string userid)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"insert into op_softrequire_detail (GID,LINKGID,MODIFYFILE,MODIFYBY,MODIFYTIME,MODIFYDETAIL,REMARKS,INPUTBY,INPUTTIME)
|
|
values (@GID,@LINKGID,@MODIFYFILE,@MODIFYBY,@MODIFYTIME,@MODIFYDETAIL,@REMARKS,@INPUTBY,@INPUTTIME) ");
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update op_softrequire_detail set MODIFYFILE=@MODIFYFILE,MODIFYTIME=@MODIFYTIME,MODIFYDETAIL=@MODIFYDETAIL,REMARKS=@REMARKS where GID=@GID ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
if (enumValue.GID == "*" || enumValue.GID == "")
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString());
|
|
db.AddInParameter(cmdInsert, "@LINKGID", DbType.String, PID);
|
|
db.AddInParameter(cmdInsert, "@MODIFYFILE", DbType.String, enumValue.MODIFYFILE);
|
|
db.AddInParameter(cmdInsert, "@MODIFYBY", DbType.String, enumValue.MODIFYBY);
|
|
db.AddInParameter(cmdInsert, "@MODIFYTIME", DbType.String, enumValue.MODIFYTIME);
|
|
db.AddInParameter(cmdInsert, "@MODIFYDETAIL", DbType.String, enumValue.MODIFYDETAIL);
|
|
db.AddInParameter(cmdInsert, "@REMARKS", DbType.String, enumValue.REMARKS);
|
|
db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, userid);
|
|
db.AddInParameter(cmdInsert, "@INPUTTIME", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
else
|
|
{
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID);
|
|
db.AddInParameter(cmdUpdate, "@MODIFYFILE", DbType.String, enumValue.MODIFYFILE);
|
|
db.AddInParameter(cmdUpdate, "@MODIFYTIME", DbType.String, enumValue.MODIFYTIME);
|
|
db.AddInParameter(cmdUpdate, "@MODIFYDETAIL", DbType.String, enumValue.MODIFYDETAIL);
|
|
db.AddInParameter(cmdUpdate, "@REMARKS", DbType.String, enumValue.REMARKS);
|
|
db.ExecuteNonQuery(cmdUpdate, tran);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "保存成功" + result.Message;
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
|
|
|
|
public static DBResult DeleteDetail(List<SoftRequireDetail> headData)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
if (headData != null)
|
|
{
|
|
foreach (var enumValue in headData)
|
|
{
|
|
|
|
var cmdDelete = db.GetSqlStringCommand("delete from op_softrequire_detail where GID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
}
|
|
}
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
public static List<SoftProject> GetSoftProject(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT * from op_softproject");
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
strSql.Append(" order by PROJECTNAME ");
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
var evList = new List<SoftProject>();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
SoftProject Data = new SoftProject();
|
|
Data.GID = Convert.ToString(reader["GID"]);
|
|
Data.PROJECTNAME = Convert.ToString(reader["PROJECTNAME"]);
|
|
Data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
Data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
|
|
evList.Add(Data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return evList;
|
|
}
|
|
|
|
|
|
}
|
|
}
|