internal bool AreaOfCheck(int checkID, AreaInfo area)
{
string strSql = string.Empty;
strSql = string.Format("select count(1) from t_check where id = {0} and checktype in (1,2,3,5)", checkID);
object obj = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
if (obj.ToInt32() <= 0)
{
return(true);
}
int checktype = 0;
strSql = string.Format("select CheckType from t_check where id = {0}", checkID);
checktype = OperationSql.ExecuteScalar(CommandType.Text, strSql, null).ToInt32();
switch (checktype)
{
case 1:
strSql = string.Format("select count(1) from v_CheckDetails where checkid = {0} and warehouseno = '{1}' ", checkID, area.WarehouseNo);
obj = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
if (obj.ToInt32() <= 0)
{
return(false);
}
break;
case 2:
strSql = string.Format("select count(1) from v_CheckDetails where checkid = {0} and warehouseno = '{1}' and houseno = '{2}' ", checkID, area.WarehouseNo, area.HouseNo);
obj = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
if (obj.ToInt32() <= 0)
{
return(false);
}
break;
case 3:
strSql = string.Format("select count(1) from v_CheckDetails where checkid = {0} and areano = '{1}' ", checkID, area.AreaNo);
obj = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
if (obj.ToInt32() <= 0)
{
return(false);
}
break;
case 5:
//strSql = string.Format("select count(1) from v_CheckDetails A join v_area B on A.WAREHOUSENO = B.warehouseno where A.checkid = {0} and A.warehouseno = '{1}' and B.areano = '{2}' ", checkID, area.WarehouseNo, area.AreaNo);
strSql = string.Format("select count(1) from v_CheckDetails where checkid = {0} and nvl(warehouseno,'{1}') = '{1}' ", checkID, area.WarehouseNo);
obj = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
if (obj.ToInt32() <= 0)
{
return(false);
}
break;
}
return(true);
}
public SqlDataReader GetAreaByAreaNo(AreaInfo model)
{
string strSql = string.Empty;
strSql = string.Format("SELECT * FROM V_Area WHERE AreaNo = '{0}'", model.AreaNo);
return(OperationSql.ExecuteReader(CommandType.Text, strSql, null));
}
public SqlDataReader IsAreaChecking(AreaInfo model)
{
string strSql = string.Empty;
strSql = string.Format("select areastatus,CheckID from T_AREA where areano='{0}'", model.AreaNo);
return(OperationSql.ExecuteReader(CommandType.Text, strSql, null));
}
public string GetCheckAreaForAndroid(int checkID, string strAreaNo, string strUserJson)
{
AreaInfo model = new AreaInfo();
string strError = string.Empty;
try
{
if (checkID <= 0)
{
model.Status = "E";
model.Message = "盘点信息获取失败!";
return(JSONHelper.ObjectToJson(model));
}
UserInfo user = JSONHelper.JsonToObject <UserInfo>(strUserJson);
if (user == null || string.IsNullOrEmpty(user.UserNo))
{
model.Status = "E";
model.Message = "用户信息获取失败!";
return(JSONHelper.ObjectToJson(model));
}
model.AreaNo = strAreaNo;
if (!GetAreaByNo(ref model, user, ref strError))
{
model.Status = "E";
model.Message = strError;
return(JSONHelper.ObjectToJson(model));
}
if (model.AreaStatus != 1 && model.CheckID != 0 && model.CheckID != checkID)
{
model.Status = "E";
model.Message = "该货位已经被锁定";
return(JSONHelper.ObjectToJson(model));
}
if (!_db.AreaOfCheck(checkID, model))
{
model.Status = "E";
model.Message = "扫描货位不属于当前盘点!";
return(JSONHelper.ObjectToJson(model));
}
model.Status = "S";
return(JSONHelper.ObjectToJson(model));
}
catch (Exception ex)
{
model.Status = "E";
model.Message = "Web异常：" + ex.Message + ex.StackTrace;
//if (Common_Func.IsOracleError(model.Message, ref strError)) model.Message = strError;
return(JSONHelper.ObjectToJson(model));
}
}
public bool ExistsAreaNo(AreaInfo model, bool bIncludeDel, UserInfo user, ref string strError)
{
try
{
return(_db.ExistsAreaNo(model, bIncludeDel));
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
public bool DeleteAreaByID(AreaInfo model, UserInfo user, ref string strError)
{
try
{
return(_db.DeleteAreaByID(model));
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
private AreaInfo GetModelFromDataReader(SqlDataReader dr)
{
AreaInfo model = new AreaInfo();
model.ID = dr["ID"].ToInt32();
model.AreaNo = dr["AREANO"].ToDBString();
model.AreaName = dr["AREANAME"].ToDBString();
model.AreaType = dr["AREATYPE"].ToInt32();
model.ContactUser = dr["CONTACTUSER"].ToDBString();
model.ContactPhone = dr["CONTACTPHONE"].ToDBString();
model.Address = dr["ADDRESS"].ToDBString();
model.LocationDesc = dr["LOCATIONDESC"].ToDBString();
model.AreaStatus = dr["AreaStatus"].ToInt32();
model.HouseID = dr["HOUSEID"].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, "HouseNo"))
{
model.HouseNo = dr["HouseNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "isHost"))
{
model.isHost = dr["isHost"].ToInt32() == 1;
}
if (Common_Func.readerExists(dr, "HouseName"))
{
model.HouseName = dr["HouseName"].ToDBString();
}
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, "StrAreaStatus"))
{
model.StrAreaStatus = dr["StrAreaStatus"].ToDBString();
}
return(model);
}
public bool SaveArea(ref AreaInfo model)
{
SqlParameter[] param = GetParameterFromModel(model);
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_SaveArea", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
model.ID = param[1].Value.ToInt32();
model.CreateTime = param[param.Length - 3].Value.ToDateTime();
model.ModifyTime = param[param.Length - 1].Value.ToDateTimeNull();
return(true);
}
}
public bool SaveArea(ref AreaInfo model, UserInfo user, ref string strError)
{
try
{
if (model.ID <= 0)
{
model.Creater = user.UserNo;
}
else
{
model.Modifyer = user.UserNo;
}
return(_db.SaveArea(ref model));
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
public string GetAreaInfo(string strAreaNo, string strUserJson)
{
string strErrMsg = string.Empty;
AreaInfo Info = new AreaInfo();
Info.AreaNo = strAreaNo;
#region MyRegion
try
{
if (string.IsNullOrEmpty(strAreaNo))
{
return(GetReturnJson(false, Info, "货位编码不能为空！"));
}
UserInfo userModel = JSONUtil.JSONHelper.JsonToObject <UserInfo>(strUserJson);
if (userModel == null || string.IsNullOrEmpty(userModel.UserNo))
{
return(GetReturnJson(false, Info, "没有获取用户信息！"));
}
bool res = _db.ExistsAreaNo(Info, false);
if (res)
{
res = GetAreaByAreaNo(ref Info, userModel, ref strErrMsg);
}
return(GetReturnJson(res, Info, strErrMsg));
}
catch (Exception ex)
{
strErrMsg = ex.Message;
return(GetReturnJson(false, Info, strErrMsg));
}
finally
{
}
#endregion
}
public bool DeleteAreaByID(AreaInfo 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_DeleteAreaByID", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(true);
}
}
public bool ExistsAreaNo(AreaInfo model, bool bIncludeDel, ref string ErrorMsg)
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_AreaNo", model.AreaNo.ToSqlValue()),
new SqlParameter("@IncludeDel", bIncludeDel.ToSqlValue()),
};
param[0].Direction = ParameterDirection.Output;
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_ExistsAreaNo", param);
ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(string.IsNullOrEmpty(ErrorMsg));
}
}
public bool GetAreaInfoByAreaNo(string strAreaNo, ref AreaInfo Info, ref string strErrMsg)
{
strErrMsg = string.Empty;
Info = new AreaInfo();
Info.AreaNo = strAreaNo;
#region MyRegion
try
{
if (string.IsNullOrEmpty(strAreaNo))
{
strErrMsg = "货位编码不能为空";
return(false);
}
bool res = _db.ExistsAreaNo(Info, false);
if (res)
{
res = GetAreaByAreaNo(ref Info, null, ref strErrMsg);
}
else
{
strErrMsg = "货位不存在！";
}
return(res);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
return(false);
}
finally
{
}
#endregion
}
public bool GetAreaList(ref List <AreaInfo> modelList, AreaInfo model, UserInfo user, ref string strError)
{
List <AreaInfo> lstModel = new List <AreaInfo>();
try
{
using (SqlDataReader dr = Common_DB.QueryAll("V_Area", GetFilterSql(model, user), "*", "Order By WarehouseNo,HouseNo,AreaNo,ID Desc"))
{
while (dr.Read())
{
lstModel.Add(GetModelFromDataReader(dr));
}
}
modelList = lstModel;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
private string GetReturnJson(bool bSucc, AreaInfo Info, string strErrMsg)
{
Info.Status = bSucc == true ? "S" : "E";
Info.Message = strErrMsg;
return(JSONUtil.JSONHelper.ObjectToJson <AreaInfo>(Info));
}
private SqlParameter[] GetParameterFromModel(AreaInfo model)
{
int i;
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_ID", model.ID.ToSqlValue()),
new SqlParameter("@v_AreaNo", model.AreaNo.ToSqlValue()),
new SqlParameter("@v_AreaName", model.AreaName.ToSqlValue()),
new SqlParameter("@v_AreaType", model.AreaType.ToSqlValue()),
new SqlParameter("@v_ContactUser", model.ContactUser.ToSqlValue()),
new SqlParameter("@v_ContactPhone", model.ContactPhone.ToSqlValue()),
new SqlParameter("@v_Address", model.Address.ToSqlValue()),
new SqlParameter("@v_LocationDesc", model.LocationDesc.ToSqlValue()),
new SqlParameter("@v_AreaStatus", model.AreaStatus.ToSqlValue()),
new SqlParameter("@v_HouseID", model.HouseID.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.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 = 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(AreaInfo 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 += " (AreaNo LIKE '%" + model.AreaNo + "%' OR AreaName Like '%" + model.AreaNo + "%') ";
hadWhere = true;
}
//if (!string.IsNullOrEmpty(model.AreaName))
//{
// strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
// strSql += " AreaName LIKE '%" + model.AreaName + "%' ";
// hadWhere = true;
//}
if (model.HouseID >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " HouseID = " + model.HouseID + " ";
hadWhere = true;
}
if (model.AreaType >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " AreaType = " + model.AreaType + " ";
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);
}
}