private void GetKeeperMaterial(CheckDetailsInfo model) { ////获取保管员信息 //List<OutStockDetails_Model> lstKeeper = GetKeeperList(model.Keeper); //if (lstKeeper == null || lstKeeper.Count <= 0) // throw new Exception("获取不到对应的保管员信息!"); //Material_SAP msap = new Material_SAP(); //string strError = string.Empty; //List<Material_Model> lstMaterial = new List<Material_Model>(); //if (!msap.GetMaterialNoByKeeperForSAP(lstKeeper, ref lstMaterial, ref strError)) // throw new Exception(strError); //if (lstMaterial == null || lstMaterial.Count <= 0) // throw new Exception("获取不到保管员对应的SAP物料信息!"); ////是否需要更新WMS保管员信息 ////if (!_db.NeedUpdateKeeperInfo(lstMaterial)) return; ////更新WMS保管员信息 ////while (lstMaterial.Count >= 20) ////{ //// _db.UpdateKeeperInfo(lstKeeper, lstMaterial.GetRange(0, 20)); //// lstMaterial.RemoveRange(0, 20); ////} //if (lstMaterial.Count >= 1) // _db.UpdateKeeperInfo(lstKeeper, lstMaterial); }
public bool ProfitLossAnalyse(ref List <CheckDetailsInfo> modelList, CheckDetailsInfo model, UserInfo user, ref string strError) { strError = "方法已弃用"; return(false); //List<CheckDetailsInfo> lstModel = new List<CheckDetailsInfo>(); //try //{ // using (SqlDataReader dr = _db.GetCheckDetailsListByCheckID(model.CheckID)) // { // while (dr.Read()) // { // lstModel.Add(GetModelFromDataReader(dr)); // } // } // modelList = lstModel; // return true; //} //catch (Exception ex) //{ // strError = ex.Message; // return false; //} //finally //{ //} }
public bool GetCheckAnalyseListByPage(ref List <CheckDetailsInfo> modelList, CheckDetailsInfo model, ref DividPage page, UserInfo user, ref string strError) { if (!_db.UpdateCheckAnalyse(model, ref strError)) { return(false); } List <CheckDetailsInfo> lstModel = new List <CheckDetailsInfo>(); try { using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "V_CheckAnalyse", GetFilterSql(model, user), "*", "Order by OperationTime desc")) { while (dr.Read()) { lstModel.Add(GetModelFromDataReader(dr)); } } modelList = lstModel; return(true); } catch (Exception ex) { strError = ex.Message; return(false); } finally { } }
private string GetFieldsSql(CheckDetailsInfo model, UserInfo user) { string strSql = "*"; switch (model.CheckType) { case 1: strSql = "CheckID,CheckNo, WarehouseNo, WarehouseName, Max(AccountQty) AccountQty, Max(StockTime) StockTime "; break; case 2: strSql = "CheckID,CheckNo, WarehouseNo, WarehouseName, HouseNo, HouseName, Max(AccountQty) AccountQty, Max(StockTime) StockTime "; break; case 3: strSql = "CheckID,CheckNo, WarehouseNo, WarehouseName, HouseNo, HouseName, AreaNo, AreaName, Max(AccountQty) AccountQty, Max(StockTime) StockTime "; break; case 4: strSql = "CheckID,CheckNo, WarehouseNo, WarehouseName, MaterialNo, MaterialDesc,MaterialStd, Max(AccountQty) AccountQty, Max(StockTime) StockTime "; break; case 5: strSql = "CheckID,CheckNo, Keeper, WarehouseNo, WarehouseName, MaterialNo, MaterialDesc,MaterialStd, Max(AccountQty) AccountQty, Max(StockTime) StockTime "; break; default: break; } return(strSql); }
public bool GetCheckDetailsListByPage(ref List <CheckDetailsInfo> modelList, CheckDetailsInfo model, ref DividPage page, UserInfo user, ref string strError) { List <CheckDetailsInfo> lstModel = new List <CheckDetailsInfo>(); try { using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "V_CheckDetails", GetFilterAndGroupSql(model, user), GetFieldsSql(model, user), "Order By CheckID Desc")) { while (dr.Read()) { lstModel.Add(GetModelFromDataReader(dr)); } } modelList = lstModel; return(true); } catch (Exception ex) { strError = ex.Message; return(false); } finally { } }
public bool GetCheckDetailsByID(ref CheckDetailsInfo model, UserInfo user, ref string strError) { try { using (SqlDataReader dr = _db.GetCheckDetailsByID(model)) { if (dr.Read()) { model = (GetModelFromDataReader(dr)); return(true); } else { strError = "找不到任何数据"; return(false); } } } catch (Exception ex) { strError = ex.Message; return(false); } finally { } }
internal bool UpdateCheckAnalyse(CheckDetailsInfo model, ref string strError) { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 1000), new SqlParameter("@v_ID", SqlDbType.Int), new SqlParameter("@v_CheckType", SqlDbType.Int), }; param[0].Direction = ParameterDirection.Output; param[1].Value = model.CheckID; param[2].Value = model.CheckType; OperationSql.ExecuteNonQuery2(CommandType.StoredProcedure, "Proc_GetCheckAnalyse", param); strError = param[0].Value.ToDBString(); if (strError.StartsWith("执行错误")) { //throw new Exception(strError); return(false); } else { return(true); } }
public SqlDataReader GetCheckDetailsByID(CheckDetailsInfo model) { string strSql = string.Empty; strSql = string.Format("SELECT * FROM V_CheckDetails WHERE ID = {0}", model.ID); return(OperationSql.ExecuteReader(CommandType.Text, strSql, null)); }
private string GetFilterSql(CheckDetailsInfo model, UserInfo user) { try { string strSql = ""; bool hadWhere = false; if (model.ID >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " ID = " + model.ID + " "; hadWhere = true; } if (model.CheckID >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " CheckID = " + model.CheckID + " "; hadWhere = true; } if (!string.IsNullOrEmpty(model.AreaNo)) { switch (model.CheckType) { case 1: strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (AreaNo LIKE '%" + model.AreaNo + "%' OR AreaName LIKE '%" + model.AreaNo + "%') "; hadWhere = true; break; case 2: strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (HouseNo LIKE '%" + model.AreaNo + "%' OR HouseName LIKE '%" + model.AreaNo + "%') "; hadWhere = true; break; case 3: strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (WarehouseNo LIKE '%" + model.AreaNo + "%' OR WarehouseName LIKE '%" + model.AreaNo + "%') "; hadWhere = true; break; case 4: case 5: strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (MaterialNo LIKE '%" + model.AreaNo + "%' OR MaterialDesc LIKE '%" + model.AreaNo + "%') "; hadWhere = true; break; } } if (model.Status >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " Status = " + model.Status + " "; hadWhere = true; } if (!string.IsNullOrEmpty(model.Operator)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " Operator Like '%" + model.Operator + "%' "; hadWhere = true; } if (model.ProfitLoss >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " ProfitLoss = " + model.ProfitLoss + " "; hadWhere = true; } if (model.HaveDiff >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); if (model.HaveDiff.ToBoolean()) { strSql += " ProfitLoss <> 1 "; } else { strSql += " ProfitLoss = 1 "; } hadWhere = true; } if (model.StartTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " OperationTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " "; hadWhere = true; } if (model.EndTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " OperationTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " "; hadWhere = true; } return(strSql); } catch { return(string.Empty); } }
private bool GetSelectListFilterSql(CheckDetailsInfo model, UserInfo user, ref string ViewName, ref string FiltSql, ref string SortSql) { string strSql = ""; bool hadWhere = false; try { 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.HouseNo)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (HouseNo LIKE '%" + model.HouseNo + "%' OR HouseName LIKE '%" + model.HouseNo + "%') "; 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.MaterialNo)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (MaterialNo LIKE '%" + model.MaterialNo + "%' OR MaterialDesc LIKE '%" + model.MaterialNo + "%' OR MaterialStd LIKE '%" + model.MaterialNo + "%')"; hadWhere = true; } if (model.HaveDiff >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " HaveDiff = " + model.HaveDiff + " "; hadWhere = true; } if (model.AccountQty >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " AccountQty = " + model.AccountQty + " "; hadWhere = true; } if (model.CheckYetMonth >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " ISNULL(CheckTime,cast('1900-01-01' as datetime)) <= add_months(" + DateTime.Now.Date.ToSqlTimeString() + ",-" + model.CheckYetMonth + ") "; hadWhere = true; } if (!string.IsNullOrEmpty(model.Keeper) && model.CheckType == 5) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " Keeper = '" + model.Keeper + "' "; hadWhere = true; } if (model.StartTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " StockTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " "; hadWhere = true; } if (model.EndTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " isnull(StockTime,cast('1900-01-01' as datetime)) <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " "; hadWhere = true; } } catch { strSql = string.Empty; } switch (model.CheckType) { case 1: ViewName = "V_CheckWarehouse V"; FiltSql = strSql; SortSql = "Order By ISNULL(StockTime,cast('1900-01-01' as datetime)) desc,WarehouseNo,WarehouseName"; return(true); case 2: ViewName = "V_CheckHouse V"; FiltSql = strSql; SortSql = "Order By ISNULL(StockTime,cast('1900-01-01' as datetime)) desc,HouseNo,HouseName"; return(true); case 3: ViewName = "V_CheckArea V"; FiltSql = strSql; SortSql = "Order By ISNULL(StockTime,cast('1900-01-01' as datetime)) desc,AreaNo,AreaName"; return(true); case 4: ViewName = "V_CheckMaterial V"; FiltSql = strSql; SortSql = "Order By ISNULL(StockTime,cast('1900-01-01' as datetime)) desc,MaterialNo,MaterialDesc"; return(true); case 5: GetKeeperMaterial(model); ViewName = "V_CheckKeeper V"; FiltSql = strSql; SortSql = "Order By ISNULL(StockTime,cast('1900-01-01' as datetime)) desc,Keeper,MaterialNo,MaterialDesc"; return(true); default: return(false); } }
public bool GetCheckDetailsSelectListByPage(ref List <CheckDetailsInfo> modelList, CheckDetailsInfo model, ref DividPage page, UserInfo user, ref string strError) { List <CheckDetailsInfo> lstModel = new List <CheckDetailsInfo>(); try { string ViewName = string.Empty; string FiltSql = string.Empty; string SortSql = string.Empty; if (!GetSelectListFilterSql(model, user, ref ViewName, ref FiltSql, ref SortSql)) { strError = "获取盘点选择视图错误"; return(false); } using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, ViewName, FiltSql, "V.*", SortSql)) { while (dr.Read()) { lstModel.Add(GetModelFromDataReader(dr)); } } modelList = lstModel; return(true); } catch (Exception ex) { strError = ex.Message; return(false); } finally { } }
private CheckDetailsInfo GetModelFromDataReader(SqlDataReader dr) { CheckDetailsInfo model = new CheckDetailsInfo(); if (Common_Func.readerExists(dr, "ID")) { model.ID = dr["ID"].ToInt32(); } if (Common_Func.readerExists(dr, "CheckID")) { model.CheckID = dr["CheckID"].ToInt32(); } if (Common_Func.readerExists(dr, "WarehouseNo")) { model.WarehouseNo = dr["WarehouseNo"].ToDBString(); } if (Common_Func.readerExists(dr, "HouseNo")) { model.HouseNo = dr["HouseNo"].ToDBString(); } if (Common_Func.readerExists(dr, "AreaNo")) { model.AreaNo = dr["AreaNo"].ToDBString(); } if (Common_Func.readerExists(dr, "MaterialNo")) { model.MaterialNo = dr["MaterialNo"].ToDBString(); } if (Common_Func.readerExists(dr, "MaterialDesc")) { model.MaterialDesc = dr["MaterialDesc"].ToDBString(); } if (Common_Func.readerExists(dr, "MaterialStd")) { model.MaterialStd = dr["MaterialStd"].ToDBString(); } if (Common_Func.readerExists(dr, "Keeper")) { model.Keeper = dr["Keeper"].ToDBString(); } if (Common_Func.readerExists(dr, "AccountQty")) { model.AccountQty = dr["AccountQty"].ToDecimal(); } if (Common_Func.readerExists(dr, "ScanQty")) { model.ScanQty = dr["ScanQty"].ToDecimal(); } if (Common_Func.readerExists(dr, "Status")) { model.Status = dr["Status"].ToInt32(); } if (Common_Func.readerExists(dr, "StockTime")) { model.StockTime = dr["StockTime"].ToDateTimeNull(); } if (Common_Func.readerExists(dr, "Operator")) { model.Operator = dr["Operator"].ToDBString(); } if (Common_Func.readerExists(dr, "OperationTime")) { model.OperationTime = dr["OperationTime"].ToDateTimeNull(); } if (Common_Func.readerExists(dr, "ProfitLoss")) { model.ProfitLoss = dr["ProfitLoss"].ToInt32(); } if (Common_Func.readerExists(dr, "DifferenceQty")) { model.DifferenceQty = dr["DifferenceQty"].ToDecimal(); } //if (Common_Func.readerExists(dr, "IsDel")) model.IsDel = dr["ISDEL"].ToInt32(); //if (Common_Func.readerExists(dr, "Creater")) model.Creater = dr["CREATER"].ToDBString(); //if (Common_Func.readerExists(dr, "CreateTime")) model.CreateTime = dr["CREATETIME"].ToDateTime(); //if (Common_Func.readerExists(dr, "Modifyer")) model.Modifyer = dr["MODIFYER"].ToDBString(); //if (Common_Func.readerExists(dr, "ModifyTime")) model.ModifyTime = dr["MODIFYTIME"].ToDateTimeNull(); if (Common_Func.readerExists(dr, "CheckNo")) { model.CheckNo = dr["CheckNo"].ToDBString(); } if (Common_Func.readerExists(dr, "strCheckType")) { model.strCheckType = dr["strCheckType"].ToDBString(); } if (Common_Func.readerExists(dr, "WarehouseName")) { model.WarehouseName = dr["WarehouseName"].ToDBString(); } if (Common_Func.readerExists(dr, "HouseName")) { model.HouseName = dr["HouseName"].ToDBString(); } if (Common_Func.readerExists(dr, "AreaName")) { model.AreaName = dr["AreaName"].ToDBString(); } if (Common_Func.readerExists(dr, "StrStatus")) { model.StrStatus = dr["StrStatus"].ToDBString(); } if (Common_Func.readerExists(dr, "IsChecked")) { model.BIsChecked = dr["IsChecked"].ToBoolean(); } if (Common_Func.readerExists(dr, "StrProfitLoss")) { model.StrProfitLoss = dr["StrProfitLoss"].ToDBString(); } if (Common_Func.readerExists(dr, "StrHaveDiff")) { model.StrHaveDiff = dr["StrHaveDiff"].ToDBString(); } return(model); }
private string GetFilterAndGroupSql(CheckDetailsInfo model, UserInfo user) { try { string strSql = ""; bool hadWhere = false; if (model.ID >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " ID = " + model.ID + " "; hadWhere = true; } if (model.CheckID >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " CheckID = " + model.CheckID + " "; 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.HouseNo)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (HouseNo LIKE '%" + model.HouseNo + "%' OR HouseName LIKE '%" + model.HouseNo + "%') "; 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.MaterialNo)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (MaterialNo LIKE '%" + model.MaterialNo + "%' OR MaterialDesc LIKE '%" + model.MaterialNo + "%' OR MaterialStd LIKE '%" + model.MaterialNo + "%')"; hadWhere = true; } if (model.Status >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " Status = " + model.Status + " "; hadWhere = true; } if (!string.IsNullOrEmpty(model.Operator)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " Operator Like '%" + model.Operator + "%' "; hadWhere = true; } if (model.ProfitLoss >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " ProfitLoss = " + model.ProfitLoss + " "; 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.ToOracleTimeString() + " "; // hadWhere = true; //} //if (model.EndTime != null) //{ // strSql = Common_Func.AddWhereAnd(strSql, hadWhere); // strSql += " CreateTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToOracleTimeString() + " "; // hadWhere = true; //} switch (model.CheckType) { case 1: strSql += " group by CheckID,CheckNo, WarehouseNo, WarehouseName "; break; case 2: strSql += " group by CheckID,CheckNo, WarehouseNo, WarehouseName, HouseNo, HouseName "; break; case 3: strSql += " group by CheckID,CheckNo, WarehouseNo, WarehouseName, HouseNo, HouseName, AreaNo, AreaName "; break; case 4: strSql += " group by CheckID,CheckNo, WarehouseNo, WarehouseName,MaterialNo ,MaterialDesc,MaterialStd"; break; case 5: strSql += " group by CheckID,CheckNo, Keeper, WarehouseNo, WarehouseName, MaterialNo "; break; default: break; } return(strSql); } catch { return(string.Empty); } }