/// <summary>
/// 根据类型获取设备收入
/// </summary>
/// <param name="id">设备id</param>
/// <param name="type">类型</param>
/// <param name="year">年份</param>
/// <returns>各个设备收入、服务人次</returns>
public Dictionary <string, double> GetServiceHisIncomesByEquipmentID(int id, int type, int year)
{
Dictionary <string, double> result = new Dictionary <string, double>();
sqlStr = string.Format("SELECT {0} AS ObjectID , SUM(s.Income) AS Incomes " +
" FROM tblServiceHis s " +
" WHERE s.EquipmentID = @ID ", (type == ReportDimension.AcceptanceMonth? " DATEPART(MONTH,s.TransDate) " : "DATEPART(YEAR,s.TransDate) "));
if (type == ReportDimension.AcceptanceMonth)
{
sqlStr += " AND DATEPART(YEAR,s.TransDate) = @Year GROUP BY DATEPART(MONTH,s.TransDate) ";
}
else
{
sqlStr += " GROUP BY DATEPART(YEAR,s.TransDate) ";
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ID", SqlDbType.Int).Value = id;
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
result.Add(SQLUtil.TrimNull(dr[0]), SQLUtil.ConvertDouble(dr[1]));
}
return(result);
}
}
}
public List <ServiceHisInfo> GetServiceHis(int year)
{
List <ServiceHisInfo> result = new List <ServiceHisInfo>();
sqlStr = "SELECT s.EquipmentID, SUM(s.Income) " +
" FROM tblServiceHis s" +
" LEFT JOIN tblEquipment e ON e.ID=s.EquipmentID" +
" WHERE DATEPART(YEAR,s.TransDate) = @Year And e.DepartmentID NOT IN (4, 9)" +
" GROUP BY s.EquipmentID ";
using (SqlCommand command = DatabaseConnection.GetCommand(sqlStr))
{
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new ServiceHisInfo()
{
EquipmentID = SQLUtil.ConvertInt(dr[0]), Income = SQLUtil.ConvertDouble(dr[1])
});
}
return(result);
}
}
}
/// <summary>
/// 估价执行：零件清单Info
/// </summary>
/// <param name="dr">数据</param>
public ValComponentInfo(DataRow dr)
: this()
{
this.User.ID = SQLUtil.ConvertInt(dr["UserID"]);
this.User.Name = SQLUtil.TrimNull(dr["UserName"]);
this.InSystem = SQLUtil.ConvertBoolean(dr["InSystem"]);
this.Equipment.ID = SQLUtil.ConvertInt(dr["EquipmentID"]);
this.Equipment.Name = SQLUtil.TrimNull(dr["Name"]);
this.Equipment.AssetCode = SQLUtil.TrimNull(dr["AssetCode"]);
this.EquipmentType.ID = SQLUtil.ConvertInt(dr["EquipmentType"]);
this.EquipmentType.Name = LookupManager.GetEquipmentTypeDesc(this.EquipmentType.ID);
this.Equipment.FujiClass2.Name = SQLUtil.TrimNull(dr["FujiClass2Name"]);
this.Component.ID = SQLUtil.ConvertInt(dr["ComponentID"]);
this.Component.Name = this.Component.ID == 0 ? ComponentType.WholeMachine : SQLUtil.TrimNull(dr["ComponentName"]);
this.Component.Type.ID = SQLUtil.ConvertInt(dr["TypeID"]);
this.Component.StdPrice = SQLUtil.ConvertDouble(dr["StdPrice"]);
this.Component.SecondsPer = SQLUtil.ConvertDouble(dr["SecondsPer"]);
this.Component.TotalSeconds = SQLUtil.ConvertInt(dr["TotalSeconds"]);
this.Component.Usage = SQLUtil.ConvertInt(dr["ComponentUsage"]);
this.Qty = SQLUtil.ConvertInt(dr["Qty"]);
this.Usage = SQLUtil.ConvertInt(dr["Usage"]);
this.Seconds = SQLUtil.ConvertDouble(dr["Seconds"]);
this.UsedSeconds = SQLUtil.ConvertDouble(dr["UsedSeconds"]);
this.IncludeContract = SQLUtil.ConvertBoolean(dr["IncludeContract"]);
this.UsageRefere = SQLUtil.ConvertInt(dr["UsageRefere"]);
if (dr.Table.Columns.Contains("UseageDate"))
{
this.UseageDate = SQLUtil.ConvertDateTime(dr["UseageDate"]);
}
}
/// <summary>
/// 获取耗材修改字段dt
/// </summary>
/// <param name="newInfo">修改后的耗材信息</param>
/// <returns>耗材修改字段dt</returns>
public DataTable GetChangedFields(ConsumableInfo newInfo)
{
DataTable dt = new DataTable();
dt.Columns.Add("FieldName");
dt.Columns.Add("OldValue");
dt.Columns.Add("NewValue");
if (this.FujiClass2.ID != newInfo.FujiClass2.ID)
{
dt.Rows.Add("ConsumableFujiClass2", SQLUtil.TrimNull(this.FujiClass2.Name), SQLUtil.TrimNull(newInfo.FujiClass2.Name));
}
if (this.Name != SQLUtil.TrimNull(newInfo.Name))
{
dt.Rows.Add("ConsumableName", this.Name, newInfo.Name);
}
if (this.Description != SQLUtil.TrimNull(newInfo.Description))
{
dt.Rows.Add("ConsumableDescription", this.Description, SQLUtil.TrimNull(newInfo.Description));
}
if (this.Type.ID != newInfo.Type.ID)
{
dt.Rows.Add("ConsumableType", this.Type.Name, LookupManager.GetConsumabletypeDesc(newInfo.Type.ID));
}
if (this.StdPrice != newInfo.StdPrice)
{
dt.Rows.Add("ConsumableStdPrice", SQLUtil.ConvertDouble(this.StdPrice), SQLUtil.ConvertDouble(newInfo.StdPrice));
}
if (this.ReplaceTimes != newInfo.ReplaceTimes)
{
dt.Rows.Add("ConsumableReplaceTimes", SQLUtil.ConvertDouble(this.ReplaceTimes), SQLUtil.ConvertDouble(newInfo.ReplaceTimes));
}
if (this.CostPer != newInfo.CostPer)
{
dt.Rows.Add("ConsumableCostPer", SQLUtil.ConvertDouble(this.CostPer), SQLUtil.ConvertDouble(newInfo.CostPer));
}
if (this.IsIncluded != newInfo.IsIncluded)
{
dt.Rows.Add("ConsumableIsIncluded", SQLUtil.ConvertBoolean(this.IsIncluded) ? "是" : "否", SQLUtil.ConvertBoolean(newInfo.IsIncluded) ? "是" : "否");
}
if (this.IncludeContract != newInfo.IncludeContract)
{
dt.Rows.Add("ConsumableIncludeContract", SQLUtil.ConvertBoolean(this.IncludeContract) ? "是" : "否", SQLUtil.ConvertBoolean(newInfo.IncludeContract) ? "是" : "否");
}
if (this.IsActive != newInfo.IsActive)
{
dt.Rows.Add("ConsumableIsActive", SQLUtil.ConvertBoolean(this.IsActive) ? "启用" : "停用", SQLUtil.ConvertBoolean(newInfo.IsActive) ? "启用" : "停用");
}
return(dt);
}
/// <summary>
/// 医院等级info
/// </summary>
/// <param name="dr">数据</param>
public HospitalLevelInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.Description = SQLUtil.TrimNull(dr["Description"]);
this.Factor = SQLUtil.ConvertDouble(dr["Factor"]);
}
/// <summary>
/// 设备成本info
/// </summary>
/// <param name="dr">dr</param>
public ValEqptOutputInfo(DataRow dr)
: this()
{
if (dr.Table.Columns.Contains("UserID"))
{
this.User.ID = SQLUtil.ConvertInt(dr["UserID"]);
}
if (dr.Table.Columns.Contains("UserName"))
{
this.User.Name = SQLUtil.TrimNull(dr["UserName"]);
}
if (dr.Table.Columns.Contains("EquipmentID"))
{
this.Equipment.ID = SQLUtil.ConvertInt(dr["EquipmentID"]);
}
this.Year = SQLUtil.ConvertInt(dr["Year"]);
this.Month = SQLUtil.ConvertInt(dr["Month"]);
if (dr.Table.Columns.Contains("ContractAmount"))
{
this.ContractAmount = SQLUtil.ConvertDouble(dr["ContractAmount"]);
}
if (dr.Table.Columns.Contains("Repair3partyCost"))
{
this.Repair3partyCost = SQLUtil.ConvertDouble(dr["Repair3partyCost"]);
}
}
public ServiceHisInfo(DataRow dr)
: this()
{
this.EquipmentID = SQLUtil.ConvertInt(dr["EquipmentID"]);
this.TransDate = SQLUtil.ConvertDateTime(dr["TransDate"]);
this.Income = SQLUtil.ConvertDouble(dr["Income"]);
}
/// <summary>
/// 获取作业报告信息
/// </summary>
/// <param name="dr">The dr.</param>
public DispatchReportInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.Dispatch.ID = SQLUtil.ConvertInt(dr["DispatchID"]);
this.Type.ID = SQLUtil.ConvertInt(dr["TypeID"]);
this.Type = LookupManager.GetDispatchReportType(this.Type.ID);
this.FaultCode = SQLUtil.TrimNull(dr["FaultCode"]);
this.FaultDesc = SQLUtil.TrimNull(dr["FaultDesc"]);
this.SolutionCauseAnalysis = SQLUtil.TrimNull(dr["SolutionCauseAnalysis"]);
this.SolutionWay = SQLUtil.TrimNull(dr["SolutionWay"]);
this.IsPrivate = SQLUtil.ConvertBoolean(dr["IsPrivate"]);
this.ServiceProvider.ID = SQLUtil.ConvertInt(dr["ServiceProvider"]);
this.ServiceProvider.Name = ServiceProviders.GetDescByID(this.ServiceProvider.ID);
this.SolutionResultStatus.ID = SQLUtil.ConvertInt(dr["SolutionResultStatusID"]);
this.SolutionResultStatus.Name = LookupManager.GetSolutionResultStatusDesc(this.SolutionResultStatus.ID);
this.SolutionUnsolvedComments = SQLUtil.TrimNull(dr["SolutionUnsolvedComments"]);
this.DelayReason = SQLUtil.TrimNull(dr["DelayReason"]);
this.Comments = SQLUtil.TrimNull(dr["Comments"]);
this.FujiComments = SQLUtil.TrimNull(dr["FujiComments"]);
this.Status.ID = SQLUtil.ConvertInt(dr["StatusID"]);
this.Status.Name = LookupManager.GetDispatchDocStatusDesc(this.Status.ID);
this.PurchaseAmount = SQLUtil.ConvertDouble(dr["PurchaseAmount"]); this.ServiceScope = SQLUtil.ConvertBoolean(dr["ServiceScope"]);
this.ServiceScope = SQLUtil.ConvertBoolean(dr["ServiceScope"]);
this.Result = SQLUtil.TrimNull(dr["Result"]);
this.IsRecall = SQLUtil.ConvertBoolean(dr["IsRecall"]);
this.AcceptanceDate = SQLUtil.ConvertDateTime(dr["AcceptanceDate"]);
this.EquipmentStatus.ID = SQLUtil.ConvertInt(dr["EquipmentStatus"]);
this.EquipmentStatus.Name = MachineStatuses.GetMachineStatusesDesc(this.EquipmentStatus.ID);
}
/// <summary>
/// 各部门设备信息（包括收入、支出、服务人次）
/// </summary>
/// <returns>各部门设备信息</returns>
public List <DepartPerformanceModel> IncomeExpenseByDepartment(string date)
{
List <DepartPerformanceModel> models = new List <DepartPerformanceModel>();
DepartPerformanceModel model = null;
int curYear = DateTime.Today.Year;
if (!string.IsNullOrEmpty(date))
{
curYear = Convert.ToDateTime(date).Year;
}
if (WebConfig.ISDEMO)
{
curYear = new DateTime(2019, 11, 12).Year;
}
int lastYear = curYear - 1;
DataTable dtEquipmentCount = this.equipmentDao.GetEquipmentInfoCountsByDepartment();
List <ServiceHisCountInfo> serviceInfosThisYear = this.serviceHisDao.GetServiceHisCountsByDepartment(curYear);
List <ServiceHisCountInfo> accessoryExpensesThisYear = this.serviceHisDao.GetAccessoryExpenseByDepartment(curYear);
List <ServiceHisCountInfo> serviceInfosLastYear = this.serviceHisDao.GetServiceHisCountsByDepartment(lastYear);
List <ServiceHisCountInfo> accessoryExpensesLastYear = this.serviceHisDao.GetAccessoryExpenseByDepartment(lastYear);
ServiceHisCountInfo serviceInfo = null;
foreach (DataRow dr in dtEquipmentCount.Rows)
{
model = new DepartPerformanceModel();
model.Department = LookupManager.GetDepartments(SQLUtil.ConvertInt(dr["DepartmentID"]));
model.EquipmentCount = SQLUtil.ConvertInt(dr["Counts"]);
model.EquipmentAmount = SQLUtil.ConvertDouble(dr["Amounts"]);
serviceInfo = (from ServiceHisCountInfo temp in serviceInfosThisYear where temp.ObjectID == model.Department.ID select temp).FirstOrDefault();
if (serviceInfo != null)
{
model.ServiceCount = serviceInfo.ServiceCount;
model.Incomes = serviceInfo.Incomes;
}
serviceInfo = (from ServiceHisCountInfo temp in accessoryExpensesThisYear where temp.ObjectID == model.Department.ID select temp).FirstOrDefault();
if (serviceInfo != null)
{
model.Expenses = serviceInfo.Expenses;
}
serviceInfo = (from ServiceHisCountInfo temp in serviceInfosLastYear where temp.ObjectID == model.Department.ID select temp).FirstOrDefault();
if (serviceInfo != null)
{
model.LastIncomes = serviceInfo.Incomes;
}
serviceInfo = (from ServiceHisCountInfo temp in accessoryExpensesLastYear where temp.ObjectID == model.Department.ID select temp).FirstOrDefault();
if (serviceInfo != null)
{
model.LastExpenses = serviceInfo.Expenses;
}
models.Add(model);
}
return(models);
}
/// <summary>
/// 故障率Info
/// </summary>
/// <param name="dr">dr</param>
public FaultRateInfo(DataRow dr)
: this()
{
this.ObjectTypeID = SQLUtil.ConvertEnum <ObjectType>(dr["ObjectTypeID"]);
this.ObjectID = SQLUtil.ConvertInt(dr["ObjectID"]);
this.Year = SQLUtil.ConvertInt(dr["Year"]);
this.Month = SQLUtil.ConvertEnum <DateTimeMonth>(dr["Month"]);
this.Rate = SQLUtil.ConvertDouble(dr["Rate"]);
}
public ReportServiceInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.DispatchReportID = SQLUtil.ConvertInt(dr["DispatchReportID"]);
this.Service.ID = SQLUtil.ConvertInt(dr["ServiceID"]);
this.Service.Name = SQLUtil.TrimNull(dr["Name"]);
this.Service.Supplier.Name = SQLUtil.TrimNull(dr["SupplierName"]);
this.Service.Price = SQLUtil.ConvertDouble(dr["Price"]);
}
/// <summary>
/// 估价执行条件：备用机清单Info
/// </summary>
/// <param name="dr">数据</param>
public ValSpareInfo(DataRow dr)
: this()
{
this.User.ID = SQLUtil.ConvertInt(dr["UserID"]);
this.User.Name = SQLUtil.TrimNull(dr["UserName"]);
this.FujiClass2.ID = SQLUtil.ConvertInt(dr["FujiClass2ID"]);
this.FujiClass2.Name = SQLUtil.TrimNull(dr["FujiClass2Name"]);
this.Price = SQLUtil.ConvertDouble(dr["Price"]);
this.QtyEnter = SQLUtil.ConvertInt(dr["QtyEnter"]);
this.QtyEval = SQLUtil.ConvertInt(dr["QtyEval"]);
}
public ReportAccessoryInfo(DataRow dr)
: this()
{
this.DispatchReportID = SQLUtil.ConvertInt(dr["ServiceReportID"]);
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.Source.ID = SQLUtil.ConvertInt(dr["Category"]);
this.SupplierID = SQLUtil.ConvertInt(dr["SupplierID"]);
this.NewSerialCode = SQLUtil.TrimNull(dr["New No#"]);
this.OldSerialCode = SQLUtil.TrimNull(dr["Detach No#"]);
this.Qty = SQLUtil.ConvertInt(dr["Quantity"]);
this.Amount = SQLUtil.ConvertDouble(dr["NewPrice"]);
}
/// <summary>
/// 获取未报废的设备总数量、采购金额总数
/// </summary>
/// <returns>设备总数量、采购金额总数</returns>
public Tuple <int, double> GetEquipmentInfoCounts()
{
sqlStr = "SELECT Count(ID) AS Counts,SUM(PurchaseAmount) AS Amounts FROM tblEquipment WHERE EquipmentStatusID <> @EquipmentStatusID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@EquipmentStatusID", SqlDbType.NVarChar).Value = EquipmentInfo.EquipmentStatuses.Scrap;
DataRow dr = GetDataRow(command);
return(new Tuple <int, double>(SQLUtil.ConvertInt(dr["Counts"]), SQLUtil.ConvertDouble(dr["Amounts"])));
}
}
/// <summary>
/// 服务库info
/// </summary>
/// <param name="dr">dr</param>
public InvServiceInfo(DataRow dr)
: this()
{
if (dr.Table.Columns.Contains("ID"))
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
}
this.FujiClass2.ID = SQLUtil.ConvertInt(dr["FujiClass2ID"]);
if (dr.Table.Columns.Contains("FujiClass2Name"))
{
this.FujiClass2.Name = SQLUtil.TrimNull(dr["FujiClass2Name"]);
}
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.TotalTimes = SQLUtil.ConvertInt(dr["TotalTimes"]);
this.Price = SQLUtil.ConvertDouble(dr["Price"]);
this.StartDate = SQLUtil.ConvertDateTime(dr["StartDate"]);
this.EndDate = SQLUtil.ConvertDateTime(dr["EndDate"]);
if (dr.Table.Columns.Contains("SupplierID"))
{
this.Supplier.ID = SQLUtil.ConvertInt(dr["SupplierID"]);
}
if (dr.Table.Columns.Contains("SupplierName"))
{
this.Supplier.Name = SQLUtil.TrimNull(dr["SupplierName"]);
}
this.Purchase.ID = SQLUtil.ConvertInt(dr["PurchaseID"]);
this.Purchase.Name = LookupManager.GetObjectOID(ObjectTypes.PurchaseOrder, this.Purchase.ID);
if (dr.Table.Columns.Contains("PurchaseDate"))
{
this.PurchaseDate = SQLUtil.ConvertDateTime(dr["PurchaseDate"]);
}
if (dr.Table.Columns.Contains("Comments"))
{
this.Comments = SQLUtil.TrimNull(dr["Comments"]);
}
if (dr.Table.Columns.Contains("AddDate"))
{
this.AddDate = SQLUtil.ConvertDateTime(dr["AddDate"]);
}
if (dr.Table.Columns.Contains("AvaibleTimes"))
{
this.AvaibleTimes = SQLUtil.ConvertInt(dr["AvaibleTimes"]);
}
if (dr.Table.Columns.Contains("UpdateDate"))
{
this.UpdateDate = SQLUtil.ConvertDateTime(dr["UpdateDate"]);
}
if (dr.Table.Columns.Contains("Inbounded"))
{
this.Inbounded = SQLUtil.ConvertBoolean(dr["Inbounded"]);
}
}
/// <summary>
/// 估价执行条件:维保对耗材的覆盖情况Info
/// </summary>
/// <param name="dr">数据</param>
public ValConsumableInfo(DataRow dr)
: this()
{
this.User.ID = SQLUtil.ConvertInt(dr["UserID"]);
this.User.Name = SQLUtil.TrimNull(dr["UserName"]);
this.Consumable.FujiClass2.ID = SQLUtil.ConvertInt(dr["FujiClass2ID"]);
this.Consumable.FujiClass2.Name = SQLUtil.TrimNull(dr["FujiClass2Name"]);
this.Consumable.ID = SQLUtil.ConvertInt(dr["ConsumableID"]);
this.Consumable.Name = SQLUtil.TrimNull(dr["ConsumableName"]);
this.Consumable.CostPer = SQLUtil.ConvertDouble(dr["CostPer"]);
this.Consumable.ReplaceTimes = SQLUtil.ConvertDouble(dr["ReplaceTimes"]);
this.IncludeContract = SQLUtil.ConvertBoolean(dr["IncludeContract"]);
}
/// <summary>
/// 获取开机率
/// </summary>
/// <returns>开机率</returns>
public double GetEquipmentBootRate()
{
sqlStr = "SELECT SUM(CASE WHEN UsageStatusID=@Running THEN 1 ELSE 0 END )*1.0/COUNT(ID) FROM tblEquipment WHERE EquipmentStatusID <> @EquipmentStatusID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("Running", SqlDbType.Int).Value = EquipmentInfo.UsageStatuses.Running;
command.Parameters.Add("@EquipmentStatusID", SqlDbType.NVarChar).Value = EquipmentInfo.EquipmentStatuses.Scrap;
DataRow dr = GetDataRow(command);
return(SQLUtil.ConvertDouble(dr[0]));
}
}
public ReportConsumableInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.DispatchReportID = SQLUtil.ConvertInt(dr["DispatchReportID"]);
this.InvConsumable.ID = SQLUtil.ConvertInt(dr["InvConsumableID"]);
this.InvConsumable.Consumable.ID = SQLUtil.ConvertInt(dr["ConsumableID"]);
this.InvConsumable.Consumable.Name = SQLUtil.TrimNull(dr["ConsumableName"]);
this.InvConsumable.LotNum = SQLUtil.TrimNull(dr["LotNum"]);
this.InvConsumable.Supplier.Name = SQLUtil.TrimNull(dr["SupplierName"]);
this.InvConsumable.Price = SQLUtil.ConvertDouble(dr["Price"]);
this.Qty = SQLUtil.ConvertDouble(dr["Qty"]);
}
/// <summary>
/// 估价参数info
/// </summary>
/// <param name="dr">数据</param>
public ValParameterInfo(DataRow dr)
: this()
{
this.SystemCost = SQLUtil.ConvertDouble(dr["SystemCost"]);
this.MonthlyHours = SQLUtil.ConvertDouble(dr["MonthlyHours"]);
this.UnitCost = SQLUtil.ConvertDouble(dr["UnitCost"]);
this.SmallConsumableCost = SQLUtil.ConvertDouble(dr["SmallConsumableCost"]);
this.HospitalLevel.ID = SQLUtil.ConvertInt(dr["HospitalLevel"]);
this.HospitalLevel = LookupManager.GetHospitalLevel(this.HospitalLevel.ID);
this.HospitalFactor1 = SQLUtil.ConvertDouble(dr["HospitalFactor1"]);
this.HospitalFactor2 = SQLUtil.ConvertDouble(dr["HospitalFactor2"]);
this.HospitalFactor3 = SQLUtil.ConvertDouble(dr["HospitalFactor3"]);
}
public ReportComponentInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.DispatchReportID = SQLUtil.ConvertInt(dr["DispatchReportID"]);
this.NewComponent.ID = SQLUtil.ConvertInt(dr["NewID"]);
this.NewComponent.SerialCode = SQLUtil.TrimNull(dr["NewSerialCode"]);
this.NewComponent.Price = SQLUtil.ConvertDouble(dr["NewPrice"]);
this.NewComponent.Component.ID = SQLUtil.ConvertInt(dr["ComponentID"]);
this.NewComponent.Component.Name = SQLUtil.TrimNull(dr["ComponentName"]);
this.OldComponent.ID = SQLUtil.ConvertInt(dr["OldID"]);
this.OldComponent.SerialCode = SQLUtil.TrimNull(dr["OldSerialCode"]);
this.OldComponent.Price = SQLUtil.ConvertDouble(dr["OldPrice"]);
}
public ContractInfo(DataRow dr)
: this()
{
this.ContractNum = SQLUtil.TrimNull(dr["Number"]);
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.Type.ID = SQLUtil.ConvertInt(dr["TypeID"]);
this.Scope.ID = SQLUtil.ConvertInt(dr["ServiceScopeID"]);
this.ScopeComments = SQLUtil.TrimNull(dr["ScopeComments"]);
this.Amount = SQLUtil.ConvertDouble(dr["Amount"]);
this.ProjectNum = SQLUtil.TrimNull(dr["ProjectNum"]);
this.StartDate = SQLUtil.ConvertDateTime(dr["StartDate"]);
this.EndDate = SQLUtil.ConvertDateTime(dr["EndDate"]);
this.Comments = SQLUtil.TrimNull(dr["Note"]);
this.SupplierID = SQLUtil.ConvertInt(dr["SupplierID"]);
}
public ReportMaterialInfo(DataRow dr)
: this()
{
this.DispatchReport.ID = SQLUtil.ConvertInt(dr["DispatchReportID"]);
this.ObjectType.ID = SQLUtil.ConvertInt(dr["ObjectType"]);
this.ObjectType.Name = LookupManager.GetObjectTypeDescription(this.ObjectType.ID);
this.Object.ID = SQLUtil.ConvertInt(dr["ObjectID"]);
this.Object.Name = SQLUtil.TrimNull(dr["ObjectName"]);
this.Equipment.ID = SQLUtil.ConvertInt(dr["EquipmentID"]);
this.Equipment.Name = SQLUtil.TrimNull(dr["EquipmentName"]);
this.User.ID = SQLUtil.ConvertInt(dr["UserID"]);
this.User.Name = SQLUtil.TrimNull(dr["UserName"]);
this.Qty = SQLUtil.ConvertDouble(dr["Qty"]);
this.UsedDate = SQLUtil.ConvertDateTime(dr["UsedDate"]);
}
/// <summary>
/// 获取折旧率(采购日期的下个月开始计算折旧率、若折旧率超过1 返回 1.否返回采购日期下月第一天与当前时间“月数”差值 比 预定折旧年限*12)
/// </summary>
/// <returns>折旧率</returns>
public double GetEquipmentDepreciationRate()
{
string sqlDiffMonth = "(DATEDIFF(MONTH, PurchaseDate, GETDATE())-1)";
sqlStr = " SELECT AVG(CASE WHEN PurchaseDate IS NULL OR {0} <= 0 THEN 0.0 ELSE (CASE WHEN {0} >= (DepreciationYears*12) THEN 1.0 ELSE {0}*1.0/(DepreciationYears*12) END)END ) " +
" FROM tblEquipment WHERE EquipmentStatusID <> @EquipmentStatusID ";
sqlStr = string.Format(sqlStr, sqlDiffMonth);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@EquipmentStatusID", SqlDbType.NVarChar).Value = EquipmentInfo.EquipmentStatuses.Scrap;
return(SQLUtil.ConvertDouble(GetDataRow(command)[0]));
}
}
/// <summary>
/// 备用机库info
/// </summary>
/// <param name="dr">dr</param>
public InvSpareInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.FujiClass2.ID = SQLUtil.ConvertInt(dr["FujiClass2ID"]);
if (dr.Table.Columns.Contains("FujiClass2Name"))
{
this.FujiClass2.Name = SQLUtil.TrimNull(dr["FujiClass2Name"]);
}
this.SerialCode = SQLUtil.TrimNull(dr["SerialCode"]);
this.Price = SQLUtil.ConvertDouble(dr["Price"]);
this.StartDate = SQLUtil.ConvertDateTime(dr["StartDate"]);
this.EndDate = SQLUtil.ConvertDateTime(dr["EndDate"]);
this.AddDate = SQLUtil.ConvertDateTime(dr["AddDate"]);
this.UpdateDate = SQLUtil.ConvertDateTime(dr["UpdateDate"]);
}
/// <summary>
/// Initializes a new instance of the <see cref="ServiceHisCountInfo"/> class.
/// </summary>
/// <param name="dr">The dr.</param>
public ServiceHisCountInfo(DataRow dr)
: this()
{
this.ObjectID = SQLUtil.ConvertInt(dr["ObjectID"]);
if (dr.Table.Columns.Contains("ServiceCount"))
{
this.ServiceCount = SQLUtil.ConvertInt(dr["ServiceCount"]);
}
if (dr.Table.Columns.Contains("Incomes"))
{
this.Incomes = SQLUtil.ConvertDouble(dr["Incomes"]);
}
if (dr.Table.Columns.Contains("Expenses"))
{
this.Expenses = SQLUtil.ConvertDouble(dr["Expenses"]);
}
}
public EquipmentInfo(DataRow dr)
: this()
{
this.EquipmentLevel.ID = SQLUtil.ConvertInt(dr["EquipmentLevel"]);
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.EquipmentCode = SQLUtil.TrimNull(dr["Model"]);
this.SerialCode = SQLUtil.TrimNull(dr["SerialNo#"]);
this.ManufacturerID = SQLUtil.ConvertInt(dr["ManufacturerID"]);
this.EquipmentClass1 = SQLUtil.ConvertInt(dr["EquipmentClass1"]);
this.EquipmentClass2 = SQLUtil.ConvertInt(dr["EquipmentClass2"]);
this.EquipmentClass3 = SQLUtil.ConvertInt(dr["EquipmentClass3"]);
this.ResponseTimeLength = SQLUtil.ConvertInt(dr["StandardResponseTime(minutes)"]);
this.FixedAsset = SQLUtil.ConvertBoolean(dr["FixedAsset"]);
this.AssetCode = SQLUtil.TrimNull(dr["AssetNumber"]);
this.AssetLevel.ID = SQLUtil.ConvertInt(dr["AssetClassID"]);
this.DepreciationYears = SQLUtil.ConvertInt(dr["DepreciationYears"]);
this.ValidityStartDate = SQLUtil.ConvertDateTime(dr["DurationofCertificateStartDate"]);
this.ValidityEndDate = SQLUtil.ConvertDateTime(dr["DurationofCertificateEndDate"]);
this.SaleContractName = SQLUtil.TrimNull(dr["SalesContract"]);
this.SupplierID = SQLUtil.ConvertInt(dr["DealerID"]);
this.PurchaseWay = SQLUtil.TrimNull(dr["PurchaseType"]);
this.PurchaseAmount = SQLUtil.ConvertDouble(dr["PurchaseAmount"]);
this.PurchaseDate = SQLUtil.ConvertDateTime(dr["PurchaseDate"]);
this.IsImport = SQLUtil.ConvertBoolean(dr["ProductionDistrict"]);
this.Department.ID = SQLUtil.ConvertInt(dr["DepartmentID"]);
this.InstalSite = SQLUtil.TrimNull(dr["InstallationLocation"]);
this.InstalStartDate = SQLUtil.ConvertDateTime(dr["InstalStartDate"]);
this.InstalEndDate = SQLUtil.ConvertDateTime(dr["InstalEndDate"]);
this.Accepted = SQLUtil.ConvertBoolean(dr["AcceptanceStatus"]);
this.AcceptanceDate = SQLUtil.ConvertDateTime(dr["AcceptanceDate"]);
this.UsageStatus.ID = SQLUtil.ConvertInt(dr["StatusofUse"]);
this.EquipmentStatus.ID = SQLUtil.ConvertInt(dr["EquipmentStatusID"]);
this.ScrapDate = SQLUtil.ConvertDateTime(dr["DisposalDate"]);
this.MaintenancePeriod = SQLUtil.ConvertInt(dr["MaintenancePeriod"]);
this.MaintenanceType.ID = SQLUtil.ConvertInt(dr["MaintenanceTypeID"]);
this.PatrolPeriod = SQLUtil.ConvertInt(dr["PatrolPeriod"]);
this.PatrolType.ID = SQLUtil.ConvertInt(dr["PatrolTypeID"]);
this.CorrectionPeriod = SQLUtil.ConvertInt(dr["CalibrationPeriod"]);
this.CorrectionType.ID = SQLUtil.ConvertInt(dr["CalibrationTypeID"]);
this.MandatoryTestStatus.ID = SQLUtil.ConvertInt(dr["InspectionFlag"]);
this.MandatoryTestDate = SQLUtil.ConvertDateTime(dr["InspectionDate"]);
this.RecallFlag = SQLUtil.ConvertBoolean(dr["RecallFlag"]);
this.RecallDate = SQLUtil.ConvertDateTime(dr["RecallDate"]);
this.CreateDate = SQLUtil.ConvertDateTime(dr["CreateDate"]);
this.CreateUserID = SQLUtil.ConvertInt(dr["CreateUserID"]);
}
/// <summary>
/// 获取零配件信息
/// </summary>
/// <param name="dr">The dr.</param>
public ReportAccessoryInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.DispatchReportID = SQLUtil.ConvertInt(dr["DispatchReportID"]);
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.Source.ID = SQLUtil.ConvertInt(dr["SourceID"]);
this.Source.Name = LookupManager.GetAccessorySourceTypeDesc(this.Source.ID);
if (dr.Table.Columns.Contains("SupplierID"))
{
this.Supplier.ID = SQLUtil.ConvertInt(dr["SupplierID"]);
this.Supplier.Name = SQLUtil.TrimNull(dr["SupplierName"]);
}
this.NewSerialCode = SQLUtil.TrimNull(dr["NewSerialCode"]);
this.OldSerialCode = SQLUtil.TrimNull(dr["OldSerialCode"]);
this.Qty = SQLUtil.ConvertInt(dr["Qty"]);
this.Amount = SQLUtil.ConvertDouble(dr["Amount"]);
}
/// <summary>
/// 获取数据库Dictionary(string, double)public class Detail
/// </summary>
/// <param name="command">SqlCommand</param>
/// <returns>数据库Dictionary(string, double)类型数据</returns>
protected Dictionary <string, double> GetStringDoubleDictionary(SqlCommand command)
{
Dictionary <string, double> dicCount = new Dictionary <string, double>();
using (DataTable dt = new DataTable())
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
dicCount.Add(SQLUtil.TrimNull(dr[0]), SQLUtil.ConvertDouble(dr[1]));
}
}
return(dicCount);
}
}
public void ImportContract(List <EntityInfo> infos)
{
sqlStr = "INSERT INTO tblContract(SupplierID,ContractNum," +
"Name,TypeID,ScopeID,ScopeComments,Amount,ProjectNum,StartDate,EndDate,Comments)" +
"VALUES(@SupplierID,@ContractNum,@Name,@TypeID,@ScopeID,@ScopeComments,@Amount," +
"@ProjectNum,@StartDate,@EndDate,@Comments)";
using (SqlCommand command = DatabaseConnection.GetCommand(sqlStr))
{
command.Parameters.Add("@SupplierID", SqlDbType.Int);
command.Parameters.Add("@ContractNum", SqlDbType.VarChar);
command.Parameters.Add("@Name", SqlDbType.NVarChar);
command.Parameters.Add("@TypeID", SqlDbType.Int);
command.Parameters.Add("@ScopeID", SqlDbType.Int);
command.Parameters.Add("@ScopeComments", SqlDbType.NVarChar);
command.Parameters.Add("@Amount", SqlDbType.Decimal);
command.Parameters.Add("@ProjectNum", SqlDbType.NVarChar);
command.Parameters.Add("@StartDate", SqlDbType.DateTime);
command.Parameters.Add("@EndDate", SqlDbType.DateTime);
command.Parameters.Add("@Comments", SqlDbType.NVarChar);
foreach (ContractInfo info in infos)
{
if (string.IsNullOrEmpty(info.Name))
{
return;
}
command.Parameters["@SupplierID"].Value = info.SupplierID;
command.Parameters["@ContractNum"].Value = SQLUtil.TrimNull(info.ContractNum);
command.Parameters["@Name"].Value = SQLUtil.TrimNull(info.Name);
command.Parameters["@TypeID"].Value = SQLUtil.ConvertInt(info.Type.ID);
command.Parameters["@ScopeID"].Value = SQLUtil.ConvertInt(info.Scope.ID);
command.Parameters["@ScopeComments"].Value = SQLUtil.TrimNull(info.ScopeComments);
command.Parameters["@Amount"].Value = SQLUtil.ConvertDouble(info.Amount);
command.Parameters["@ProjectNum"].Value = SQLUtil.TrimNull(info.ProjectNum);
command.Parameters["@StartDate"].Value = SQLUtil.ConvertDateTime(info.StartDate);
command.Parameters["@EndDate"].Value = SQLUtil.ConvertDateTime(info.EndDate);
command.Parameters["@Comments"].Value = SQLUtil.TrimNull(info.Comments);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 获取合同信息
/// </summary>
/// <param name="dr">dataRow</param>
public ContractInfo(DataRow dr)
: this()
{
this.ID = SQLUtil.ConvertInt(dr["ID"]);
this.ContractNum = SQLUtil.TrimNull(dr["ContractNum"]);
this.Name = SQLUtil.TrimNull(dr["Name"]);
this.Type.ID = SQLUtil.ConvertInt(dr["TypeID"]);
this.Type.Name = Manager.LookupManager.GetContractTypeDesc(this.Type.ID);
this.Scope.ID = SQLUtil.ConvertInt(dr["ScopeID"]);
this.Scope.Name = LookupManager.GetContractScopeDesc(this.Scope.ID);
this.ScopeComments = SQLUtil.TrimNull(dr["ScopeComments"]);
this.Amount = SQLUtil.ConvertDouble(dr["Amount"]);
this.ProjectNum = SQLUtil.TrimNull(dr["ProjectNum"]);
this.StartDate = SQLUtil.ConvertDateTime(dr["StartDate"]);
this.EndDate = SQLUtil.ConvertDateTime(dr["EndDate"]);
this.Comments = SQLUtil.TrimNull(dr["Comments"]);
this.Supplier.ID = SQLUtil.ConvertInt(dr["SupplierID"]);
this.Supplier.Name = SQLUtil.TrimNull(dr["supplierName"]);
}