public bool GetTempMaterialListByPage(ref List <TempMaterialInfo> modelList, TempMaterialInfo model, ref DividPage page, UserInfo user, ref string strError)
{
if (page == null)
{
page = new DividPage();
}
List <TempMaterialInfo> lstModel = new List <TempMaterialInfo>();
try
{
using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "V_TempMaterial", GetFilterSql(model, user), "*", "Order By CreateTime Desc,TempMaterialNo,MaterialNo,ID Desc"))
{
while (dr.Read())
{
lstModel.Add(GetModelFromDataReader(dr));
}
}
modelList = lstModel;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
finally
{
}
}
private TempMaterialInfo GetModelFromDataReader(SqlDataReader dr)
{
TempMaterialInfo model = new TempMaterialInfo();
model.ID = dr["ID"].ToInt32();
model.TempMaterialNo = dr["TempMaterialNo"].ToDBString();
model.TempMaterialDesc = dr["TempMaterialDesc"].ToDBString();
model.MaterialNo = dr["MaterialNo"].ToDBString();
model.MaterialDesc = dr["MaterialDesc"].ToDBString();
model.SapMaterialDoc = dr["SapMaterialDoc"].ToDBString();
model.ReplaceUser = dr["ReplaceUser"].ToDBString();
model.ReplaceTime = dr["ReplaceTime"].ToDateTimeNull();
model.TempMaterialStatus = dr["TempMaterialStatus"].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, "StrTempMaterialStatus"))
{
model.StrTempMaterialStatus = dr["StrTempMaterialStatus"].ToDBString();
}
model.Unit = string.Empty;
model.IsRohs = 2;
return(model);
}
public bool GetTempMaterialByID(ref TempMaterialInfo model, UserInfo user, ref string strError)
{
try
{
using (SqlDataReader dr = _db.GetTempMaterialByID(model))
{
if (dr.Read())
{
model = (GetModelFromDataReader(dr));
return(true);
}
else
{
strError = "找不到任何数据";
return(false);
}
}
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
finally
{
}
}
public bool SaveTempMaterial(ref TempMaterialInfo model, UserInfo user, ref string strError)
{
try
{
if (model.ID <= 0)
{
model.Creater = user.UserNo;
}
else
{
model.Modifyer = user.UserNo;
}
bool bResult = _db.SaveTempMaterial(ref model);
if (bResult)
{
if (!GetTempMaterialByID(ref model, user, ref strError))
{
strError = string.Format("{0}{1}{2}", "保存成功,获取保存数据失败", Environment.NewLine, strError);
}
}
return(bResult);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
private SqlParameter[] GetParameterFromModel(TempMaterialInfo model)
{
int i;
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_ID", model.ID.ToSqlValue()),
new SqlParameter("@v_TempMaterialNo", model.TempMaterialNo.ToSqlValue()),
new SqlParameter("@v_TempMaterialDesc", model.TempMaterialDesc.ToSqlValue()),
new SqlParameter("@v_MaterialNo", model.MaterialNo.ToSqlValue()),
new SqlParameter("@v_MaterialDesc", model.MaterialDesc.ToSqlValue()),
new SqlParameter("@v_SapMaterialDoc", model.SapMaterialDoc.ToSqlValue()),
new SqlParameter("@v_ReplaceUser", model.ReplaceUser.ToSqlValue()),
new SqlParameter("@v_ReplaceTime", model.ReplaceTime.ToSqlValue()),
new SqlParameter("@v_TempMaterialStatus", model.TempMaterialStatus.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.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.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 = 20;
param[i++].Size = 50;
param[i++].Size = 50;
param[i++].Size = 20;
param[i++].Size = 30;
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);
}
public SqlDataReader GetTempMaterialByNo(TempMaterialInfo model)
{
string strSql = string.Empty;
strSql = string.Format("SELECT * FROM V_TempMaterial WHERE TempMaterialNo = '{0}'", string.IsNullOrEmpty(model.MaterialNo) ? model.TempMaterialNo : model.MaterialNo);
return(OperationSql.ExecuteReader(CommandType.Text, strSql, null));
}
public SqlDataReader GetTempMaterialByID(TempMaterialInfo model)
{
string strSql = string.Empty;
strSql = string.Format("SELECT * FROM V_TempMaterial WHERE ID = {0}", model.ID);
return(OperationSql.ExecuteReader(CommandType.Text, strSql, null));
}
private TempMaterialInfo TransferTaskDetailToTempMaterial(TaskDetails_Model TM)
{
TempMaterialInfo model = new TempMaterialInfo();
model.MaterialNo = TM.MaterialNo;
model.MaterialDesc = TM.MaterialDesc;
model.Unit = TM.Unit;
model.IsRohs = TM.IsROHS;
return(model);
}
public bool DeleteTempMaterialByID(TempMaterialInfo model, UserInfo user, ref string strError)
{
try
{
return(_db.DeleteTempMaterialByID(model));
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
public bool ExistsTempMaterialNo(TempMaterialInfo model, bool bIncludeDel, UserInfo user, ref string strError)
{
try
{
return(_db.ExistsTempMaterialNo(model, bIncludeDel));
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
private string GetFilterSql(TempMaterialInfo model, UserInfo user)
{
try
{
string strSql = " Where ISNULL(IsDel,1) = 1 ";
bool hadWhere = true;
if (!string.IsNullOrEmpty(model.TempMaterialNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (TempMaterialNo Like '%" + model.TempMaterialNo + "%' or TempMaterialDesc Like '%" + model.TempMaterialNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.MaterialNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (MaterialNo Like '%" + model.MaterialNo + "%' or MaterialDesc Like '%" + model.MaterialNo + "%') ";
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);
}
}
public string GetTempMaterialNo(TempMaterialInfo model)
{
string strSql = string.Empty;
if (model.ID <= 0)
{
strSql = "SELECT 'L' || TO_CHAR(SYSDATE,'YYYYMM') || CONCAT(SUBSTR('00000',1,5-LENGTH(SEQ_TEMPMATERIALNO.NEXTVAL)),SEQ_TEMPMATERIALNO.NEXTVAL) FROM DUAL";
}
else
{
strSql = string.Format("SELECT TempMaterialNo FROM T_TEMPMATERIAL WHERE ID = {0}", model.ID);
}
object o;
o = OperationSql.ExecuteScalar(CommandType.Text, strSql, null);
return(o.ToDBString());
}
public bool GetTempMaterialNo(ref TempMaterialInfo model, UserInfo user, ref string strError)
{
try
{
string menuNo = _db.GetTempMaterialNo(model);
if (string.IsNullOrEmpty(menuNo))
{
return(false);
}
model.TempMaterialNo = menuNo;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
public bool SaveTempMaterial(ref TempMaterialInfo model)
{
SqlParameter[] param = GetParameterFromModel(model);
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_SaveTempMaterial", 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 DeleteTempMaterialByID(TempMaterialInfo 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_DeleteTempMaterialByID", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(true);
}
}
public bool ExistsTempMaterialNo(TempMaterialInfo model, bool bIncludeDel)
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000),
new SqlParameter("@v_TempMaterialNo", model.TempMaterialNo.ToSqlValue()),
new SqlParameter("@IncludeDel", bIncludeDel.ToSqlValue()),
};
param[0].Direction = ParameterDirection.Output;
OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_ExistsTempMaterialNo", param);
string ErrorMsg = param[0].Value.ToDBString();
if (ErrorMsg.StartsWith("执行错误"))
{
throw new Exception(ErrorMsg);
}
else
{
return(string.IsNullOrEmpty(ErrorMsg));
}
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="type">获取类型 1-仅SAP; 2-仅临时物料; 3-先临时再SAP</param>
/// <param name="user"></param>
/// <param name="strError"></param>
/// <returns></returns>
public bool GetMaterialInfo(ref TempMaterialInfo model, int type, UserInfo user, ref string strError)
{
Task_Func TF = new Task_Func();
TaskDetails_Model TM = new TaskDetails_Model();
SqlDataReader dr = null;
TempMaterialInfo temp = new TempMaterialInfo();
try
{
switch (type)
{
case 1:
//if (TF.GetMaterialInfoForSAP(model.MaterialNo, ref TM, ref strError))
//{
// temp = TransferTaskDetailToTempMaterial(TM);
//}
//else
{
return(false);
}
break;
case 2:
dr = _db.GetTempMaterialByNo(model);
if (dr != null && dr.Read())
{
temp = GetModelFromDataReader(dr);
temp.MaterialNo = temp.TempMaterialNo;
temp.MaterialDesc = temp.TempMaterialDesc;
}
else
{
strError = "没有获取到临时物料信息！";
return(false);
}
break;
case 3:
dr = _db.GetTempMaterialByNo(model);
if (dr != null && dr.Read())
{
temp = GetModelFromDataReader(dr);
temp.MaterialNo = temp.TempMaterialNo;
temp.MaterialDesc = temp.TempMaterialDesc;
}
else
{
//if (TF.GetMaterialInfoForSAP(model.MaterialNo, ref TM, ref strError))
//{
// temp = TransferTaskDetailToTempMaterial(TM);
//}
//else
{
strError = "没有获取到物料信息！";
return(false);
}
}
break;
default:
strError = "传入类型错误";
return(false);
}
model = temp;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
}