public bool DeleteHouseByID(HouseInfo model)
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_ID", model.ID.ToSqlValue()),
};
param[0].Direction = ParameterDirection.Output;
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_DeleteHouseByID", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(true);
}
}
private HouseInfo GetModelFromDataReader(SqlDataReader dr)
{
HouseInfo model = new HouseInfo();
model.ID = dr["ID"].ToInt32();
model.HouseNo = dr["HOUSENO"].ToDBString();
model.HouseName = dr["HOUSENAME"].ToDBString();
model.HouseType = dr["HOUSETYPE"].ToInt32();
model.ContactUser = dr["CONTACTUSER"].ToDBString();
model.ContactPhone = dr["CONTACTPHONE"].ToDBString();
model.AreaCount = dr["AREACOUNT"].ToInt32();
model.AreaUsingCount = dr["AREAUSINGCOUNT"].ToInt32();
model.Address = dr["ADDRESS"].ToDBString();
model.LocationDesc = dr["LOCATIONDESC"].ToDBString();
model.HouseStatus = dr["HouseStatus"].ToInt32();
model.WarehouseID = dr["WAREHOUSEID"].ToInt32();
model.IsDel = dr["ISDEL"].ToInt32();
model.Creater = dr["CREATER"].ToDBString();
model.CreateTime = dr["CREATETIME"].ToDateTime();
model.Modifyer = dr["MODIFYER"].ToDBString();
model.ModifyTime = dr["MODIFYTIME"].ToDateTimeNull();
if (Common_Func.readerExists(dr, "WarehouseNo"))
{
model.WarehouseNo = dr["WarehouseNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "WarehouseName"))
{
model.WarehouseName = dr["WarehouseName"].ToDBString();
}
if (Common_Func.readerExists(dr, "StrHouseStatus"))
{
model.StrHouseStatus = dr["StrHouseStatus"].ToDBString();
}
model.AreaRate = model.AreaCount >= 1 ? model.AreaUsingCount.ToDecimal() / model.AreaCount.ToDecimal() : 0;
return(model);
}
public bool ExistsHouseNo(HouseInfo model, bool bIncludeDel)
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_HouseNo", model.HouseNo.ToSqlValue()),
new SqlParameter("@IncludeDel", bIncludeDel.ToSqlValue()),
};
param[0].Direction = ParameterDirection.Output;
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_ExistsHouseNo", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(string.IsNullOrEmpty(ErrorMsg));
}
}
public bool GetHouseList(ref List <HouseInfo> modelList, HouseInfo model, UserInfo user, ref string strError)
{
List <HouseInfo> lstModel = new List <HouseInfo>();
try
{
using (SqlDataReader dr = Common_DB.QueryAll("V_House", GetFilterSql(model, user), "*", "Order By WarehouseNo,HouseNo,ID Desc"))
{
while (dr.Read())
{
lstModel.Add(GetModelFromDataReader(dr));
}
}
modelList = lstModel;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
private SqlParameter[] GetParameterFromModel(HouseInfo model)
{
int i;
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_ID", model.ID.ToSqlValue()),
new SqlParameter("@v_HouseNo", model.HouseNo.ToSqlValue()),
new SqlParameter("@v_HouseName", model.HouseName.ToSqlValue()),
new SqlParameter("@v_HouseType", model.HouseType.ToSqlValue()),
new SqlParameter("@v_ContactUser", model.ContactUser.ToSqlValue()),
new SqlParameter("@v_ContactPhone", model.ContactPhone.ToSqlValue()),
new SqlParameter("@v_AreaCount", model.AreaCount.ToSqlValue()),
new SqlParameter("@v_AreaUsingCount", model.AreaUsingCount.ToSqlValue()),
new SqlParameter("@v_Address", model.Address.ToSqlValue()),
new SqlParameter("@v_LocationDesc", model.LocationDesc.ToSqlValue()),
new SqlParameter("@v_HouseStatus", model.HouseStatus.ToSqlValue()),
new SqlParameter("@v_WarehouseID", model.WarehouseID.ToSqlValue()),
new SqlParameter("@v_IsDel", model.IsDel.ToSqlValue()),
new SqlParameter("@v_Creater", model.Creater.ToSqlValue()),
new SqlParameter("@v_CreateTime", model.CreateTime.ToSqlValue()),
new SqlParameter("@v_Modifyer", model.Modifyer.ToSqlValue()),
new SqlParameter("@v_ModifyTime", model.ModifyTime.ToSqlValue()),
};
i = 0;
param[i++].Direction = ParameterDirection.Output;
param[i++].Direction = ParameterDirection.InputOutput;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.InputOutput;
param[i++].Direction = ParameterDirection.Input;
param[i++].Direction = ParameterDirection.InputOutput;
i = 0;
param[i++].Size = 1000;
param[i++].Size = 18;
param[i++].Size = 20;
param[i++].Size = 50;
param[i++].Size = 18;
param[i++].Size = 50;
param[i++].Size = 50;
param[i++].Size = 18;
param[i++].Size = 18;
param[i++].Size = 50;
param[i++].Size = 200;
param[i++].Size = 18;
param[i++].Size = 18;
param[i++].Size = 18;
param[i++].Size = 50;
param[i++].Size = 30;
param[i++].Size = 50;
param[i++].Size = 30;
return(param);
}
private string GetFilterSql(HouseInfo model, UserInfo user)
{
try
{
string strSql = " Where ISNULL(IsDel,1) = 1 ";
bool hadWhere = true;
if (!string.IsNullOrEmpty(model.WarehouseNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (WarehouseNo LIKE '%" + model.WarehouseNo + "%' OR WarehouseName Like '%" + model.WarehouseNo + "%') ";
hadWhere = true;
}
//if (!string.IsNullOrEmpty(model.WarehouseName))
//{
// strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
// strSql += " WarehouseName LIKE '%" + model.WarehouseName + "%' ";
// hadWhere = true;
//}
if (!string.IsNullOrEmpty(model.HouseNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (HouseNo LIKE '%" + model.HouseNo + "%' OR HouseName Like '%" + model.HouseNo + "%') ";
hadWhere = true;
}
//if (!string.IsNullOrEmpty(model.HouseName))
//{
// strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
// strSql += " HouseName LIKE '%" + model.HouseName + "%' ";
// hadWhere = true;
//}
if (!string.IsNullOrEmpty(model.AreaNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " ID In (Select HouseID From T_Area Where AreaNo LIKE '%" + model.AreaNo + "%' OR AreaName Like '%" + model.AreaNo + "%') ";
hadWhere = true;
}
//if (!string.IsNullOrEmpty(model.AreaName))
//{
// strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
// strSql += " ID In (Select HouseID From T_Area Where AreaName LIKE '%" + model.AreaName + "%') ";
// hadWhere = true;
//}
if (model.WarehouseID >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " WarehouseID = " + model.WarehouseID + " ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.Address))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " Address Like '%" + model.Address + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.Creater))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " Creater Like '%" + model.Creater + "%' ";
hadWhere = true;
}
if (model.StartTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " ";
hadWhere = true;
}
if (model.EndTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " ";
hadWhere = true;
}
return(strSql);
}
catch
{
return(string.Empty);
}
}