public void UpdateTransferBin2BinMaterial(DataTable dtTransferBin2BinMaterial, PriceType enWHPriceType, string sWHID) { //string BINID = GetBINIDFromWHID(sWHID); 缺省库位 foreach (DataRow drTransferBin2BinMaterial in dtTransferBin2BinMaterial.Rows) { if (drTransferBin2BinMaterial.RowState != DataRowState.Deleted) { string sSql = @"SELECT WH_InStoreMaterialDetail.*,MaterialUOM.MaterialUomID,Material.MaterialName,WH_InStoreMaterialDetail.QuantityInBin - WH_InStoreMaterialDetail.PreserveQuantity AS TransferQuantityInBin From WH_InStoreMaterialDetail LEFT JOIN Material ON Material.ItemCode = WH_InStoreMaterialDetail.ItemCode LEFT JOIN MaterialUOM on WH_InStoreMaterialDetail.UOMID = MaterialUOM.UOMID AND MaterialUOM.ItemCode = WH_InStoreMaterialDetail.ItemCode AND MaterialUOM.IsBaseUOM = 1 WHERE WH_InStoreMaterialDetail.InStockMaterialID = '" + drTransferBin2BinMaterial["InStockMaterialID"].ToString() + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); if (dtTempInfo.Rows.Count > 0) { //原库位 drTransferBin2BinMaterial["BINIDOld"] = dtTempInfo.Rows[0]["BINID"]; drTransferBin2BinMaterial["WH_TransferBin2BinMaterial__BINIDOld"] = dtTempInfo.Rows[0]["BINID"]; //原库存 // drTransferBin2BinMaterial["TransferQuantityOld"] = dtTempInfo.Rows[0]["QuantityInBin"] ; drTransferBin2BinMaterial["TransferQuantityOld"] = dtTempInfo.Rows[0]["TransferQuantityInBin"]; //单位 drTransferBin2BinMaterial["MaterialUomID"] = dtTempInfo.Rows[0]["MaterialUomID"]; drTransferBin2BinMaterial["WH_TransferBin2BinMaterial__MaterialUomID"] = dtTempInfo.Rows[0]["UOMID"]; //订单编号 drTransferBin2BinMaterial["POID"] = dtTempInfo.Rows[0]["POID"]; drTransferBin2BinMaterial["WH_TransferBin2BinMaterial__POID"] = dtTempInfo.Rows[0]["POID"]; //drTransferBin2BinMaterial["WH_TransferBin2BinMaterial__BINIDNew"] = BINID; //drTransferBin2BinMaterial["BINIDNew"] = BINID; if (enWHPriceType == PriceType.TYPE_PO) { //基本单位单价(本) drTransferBin2BinMaterial["UnitPriceNatural"] = dtTempInfo.Rows[0]["UnitPricePONatural"]; //基本单位单价(核) drTransferBin2BinMaterial["UnitPriceStandard"] = dtTempInfo.Rows[0]["UnitPricePOStandard"]; } else if (enWHPriceType == PriceType.TYPE_Average) { //基本单位单价(本) drTransferBin2BinMaterial["UnitPriceNatural"] = dtTempInfo.Rows[0]["AveragePriceNatural"]; //基本单位单价(核) drTransferBin2BinMaterial["UnitPriceStandard"] = dtTempInfo.Rows[0]["AveragePriceStandard"]; } drTransferBin2BinMaterial["ItemCode"] = dtTempInfo.Rows[0]["ItemCode"]; //物资名称 drTransferBin2BinMaterial["MaterialName"] = dtTempInfo.Rows[0]["MaterialName"]; } } } }
/// <summary> /// 通过 ITBIDKey 来获得对应策略的类型 /// </summary> /// <param name="ITBIDKey">ITBIDKey</param> /// <returns>0:无数据,1:MR,2:SR</returns> public int GetTypeByITB(string ITBIDKey) { int iType = 0; string sSelectSql = "SELECT MRTypeID FROM TCStrategy INNER JOIN ITBDocument ON TCStrategy.TenderID = ITBDocument.TenderID WHERE ITBDocument.ITBIDKey = '" + ITBIDKey + "' "; DataTable dtData = BaseDataAccess.GetDataTable(sSelectSql); if (dtData.Rows.Count > 0) { iType = Convert.ToInt32(dtData.Rows[0][0]); } return(iType); }
public bool HashChildren(string strConID) { string sql = "select sum(a) as RelateRow from(select count(*) a from BT_DocumentModel where ParentID = '" + strConID + "'union all select count(*) a from DocumentModel where TypeID= '" + strConID + "')tb"; System.Data.DataTable dt = BaseDataAccess.GetDataTable(sql); if ((int)dt.Rows[0][0] > 0) { return(true); } else { return(false); } }
/// <summary> /// 检查报价单编号是否存在 /// </summary> /// <param name="sQuotationNo"></param> /// <returns></returns> public string GetQuotationIDByNo(string sQuotationNo) { string sQuotationID = string.Empty; string sSelect = " SELECT QuotationPriceID FROM MR_QuotationPrice WHERE QuotationPriceNO = '" + sQuotationNo + "'"; DataTable dt = BaseDataAccess.GetDataTable(sSelect); if (dt.Rows.Count > 0) { sQuotationID = Convert.ToString(dt.Rows[0][0]); } return(sQuotationID); }
public void UpdateAdjustOutMaterial(DataTable dtAdjustOutMaterial, PriceType enWHPriceType) { foreach (DataRow drAdjustOutMaterial in dtAdjustOutMaterial.Rows) { if (drAdjustOutMaterial.RowState != DataRowState.Deleted) { string sSql = @"SELECT WH_InStoreMaterialDetail.*,Material.MaterialName,MaterialUOM.MaterialUomID,QuantityReject From WH_InStoreMaterialDetail LEFT JOIN Material on Material.ItemCode = WH_InStoreMaterialDetail.ItemCode LEFT JOIN MaterialUOM on WH_InStoreMaterialDetail.UOMID = MaterialUOM.UOMID AND MaterialUOM.ItemCode = WH_InStoreMaterialDetail.ItemCode AND MaterialUOM.IsBaseUOM = 1 LEFT JOIN WH_AdjustOutMaterial ON WH_AdjustOutMaterial.InStockMaterialID = WH_InStoreMaterialDetail.InStockMaterialID WHERE WH_InStoreMaterialDetail.InStockMaterialID = '" + drAdjustOutMaterial["InStockMaterialID"].ToString() + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); if (dtTempInfo.Rows.Count > 0) { //订单编号 drAdjustOutMaterial["POID"] = dtTempInfo.Rows[0]["POID"]; drAdjustOutMaterial["WH_AdjustOutMaterial__POID"] = dtTempInfo.Rows[0]["POID"]; //库存 drAdjustOutMaterial["QuantityInBin"] = dtTempInfo.Rows[0]["QuantityInBin"]; //库位 drAdjustOutMaterial["BINID"] = dtTempInfo.Rows[0]["BINID"]; drAdjustOutMaterial["WH_AdjustOutMaterial__BINID"] = dtTempInfo.Rows[0]["BINID"]; //制造编号 drAdjustOutMaterial["PartNO"] = dtTempInfo.Rows[0]["PartNO"]; drAdjustOutMaterial["ItemCode"] = dtTempInfo.Rows[0]["ItemCode"]; //盘亏数量 // drAdjustOutMaterial["QuantityReject"] = dtTempInfo.Rows[0]["QuantityReject"] ; //单位 drAdjustOutMaterial["MaterialUomID"] = dtTempInfo.Rows[0]["MaterialUomID"]; drAdjustOutMaterial["WH_AdjustOutMaterial__MaterialUomID"] = dtTempInfo.Rows[0]["UomID"]; if (enWHPriceType == PriceType.TYPE_PO) { //基本单位单价(本) drAdjustOutMaterial["UnitPriceNatural"] = dtTempInfo.Rows[0]["UnitPricePONatural"]; //基本单位单价(核) drAdjustOutMaterial["UnitPriceStandard"] = dtTempInfo.Rows[0]["UnitPricePOStandard"]; } else if (enWHPriceType == PriceType.TYPE_Average) { //基本单位单价(本) drAdjustOutMaterial["UnitPriceNatural"] = dtTempInfo.Rows[0]["AveragePriceNatural"]; //基本单位单价(核) drAdjustOutMaterial["UnitPriceStandard"] = dtTempInfo.Rows[0]["AveragePriceStandard"]; } //物资名称 drAdjustOutMaterial["MaterialName"] = dtTempInfo.Rows[0]["MaterialName"]; } } } }
public bool GetIssueNo(string sIssueNo) { string strSql = @"SELECT IssueNo FROM WH_Issue WHERE IssueNo = '" + sIssueNo + "'"; DataTable dtIssue = BaseDataAccess.GetDataTable(strSql); if (dtIssue.Rows.Count > 0) { return(true); } else { return(false); } }
/// <summary> /// 通过ITBIDKey获得TenderID /// </summary> /// <param name="ITBIDKey"></param> /// <returns></returns> public string GetTenderID(string ITBIDKey) { string SelectSql = " select TenderID from ITBDocument where ITBIDKey = '" + ITBIDKey + "'"; DataTable dt_Temp = BaseDataAccess.GetDataTable(SelectSql); if (dt_Temp.Rows.Count > 0) { return(dt_Temp.Rows[0][0].ToString()); } else { return(""); } }
//获得物料单位 public DataTable GetMaterialUOM(string strMaterialUomID) { string strSql = @"SELECT * FROM MaterialUOM WHERE MaterialUomID = '" + strMaterialUomID + "'"; DataTable dtMaterialUOM = dtMaterialUOM = BaseDataAccess.GetDataTable(strSql); if (dtMaterialUOM != null) { if (dtMaterialUOM.Rows.Count != 0) { return(dtMaterialUOM); } } return(dtMaterialUOM); }
/// <summary> /// 通过 TenderID 来获得对应策略的类型 /// </summary> /// <param name="ITBIDKey">TenderID</param> /// <returns>0:无数据,1:MR,2:SR</returns> public int GetTypeByStrategy(string TenderID) { int iType = 0; string sSelectSql = "SELECT MRTypeID FROM TCStrategy WHERE TenderID = '" + TenderID + "' "; DataTable dtData = BaseDataAccess.GetDataTable(sSelectSql); if (dtData.Rows.Count > 0) { iType = Convert.ToInt32(dtData.Rows[0][0]); } return(iType); }
//人员归属部门IDKey public string GetDepartFromEm(string EmpID) { string strSql = " select IDKey from BI_Department where IDKey in (select DepartmentID from BI_DepartmentEmployee where EmployeeID = '" + EmpID + "')"; DataTable dtDepartmentName = BaseDataAccess.GetDataTable(strSql); string strDepartmentName = ""; if (dtDepartmentName.Rows.Count > 0) { strDepartmentName = dtDepartmentName.Rows[0]["IDKey"].ToString(); } return(strDepartmentName); }
//判断是否还有子节点 public bool CheckhaveSub(string DepID) { string strSql = "SELECT * FROM BI_Department where PDepartmentID = '" + DepID + "'"; DataTable dtDepartmentName = BaseDataAccess.GetDataTable(strSql); if (dtDepartmentName.Rows.Count > 0) { return(true); } else { return(false); } }
//物资编码计算 public string GetItemCode(string MaterialUomID) { string sSql = @"Select * From MaterialUOM WHERE MaterialUomID ='" + MaterialUomID + "'"; DataTable dtBaseUnit = BaseDataAccess.GetDataTable(sSql); string strItemCode = ""; if (dtBaseUnit != null) { if (dtBaseUnit.Rows.Count != 0) { strItemCode = dtBaseUnit.Rows[0]["ItemCode"].ToString(); } } return(strItemCode); }
public DataTable GetRptData_ReportReturn(string sWhere) { string sSql = string.Empty; sSql += "SELECT dbo.WH_ReturnMaterial.BINID," + "dbo.WH_ReturnMaterial.POID, " + "dbo.WH_ReturnMaterial.FactReturnQuantity, " + "dbo.WH_Return.EmployeeReceive, " + "dbo.WH_ReturnMaterial.UnitPriceNatural," + "dbo.WH_ReturnMaterial.UnitPriceStandard," + "dbo.WH_ReturnMaterial.ItemCode, " + "dbo.Material.MaterialName, " + "dbo.WH_Return.ReturnNo," + "dbo.WH_Return.ReturnDate, " + "dbo.BI_Department.DepartmentName, " + "dbo.Material.UOMID, " + "dbo.WH_ReturnMaterial.FactReturnQuantity * dbo.WH_ReturnMaterial.UnitPriceNatural AS TotalNatural, " + "dbo.WH_ReturnMaterial.FactReturnQuantity * dbo.WH_ReturnMaterial.UnitPriceStandard AS TotalStandard," + "dbo.WH_BI_WareHouse.WHID " + "FROM " + "dbo.WH_ReturnMaterial INNER JOIN dbo.Material ON dbo.WH_ReturnMaterial.ItemCode = dbo.Material.ItemCode " + " INNER JOIN dbo.WH_Return ON dbo.WH_Return.ReturnID = dbo.WH_ReturnMaterial.ReturnID " + " INNER JOIN dbo.BI_Department ON dbo.BI_Department.IDKey = dbo.WH_Return.DepID " + " INNER JOIN dbo.MaterialUOM ON dbo.MaterialUOM.ItemCode = dbo.Material.ItemCode " + " INNER JOIN dbo.WH_BI_WareHouse ON dbo.WH_BI_WareHouse.WHID = dbo.WH_Return.WHIDIssue " + " INNER JOIN dbo.BI_Employee ON dbo.BI_Employee.IDKey = dbo.WH_Return.EmployeeReceive"; if (sWhere != null && sWhere.Length > 0) { sSql += " WHERE " + sWhere + ""; } // // if ( sShowCount != null && sShowCount.Length != 0 ) // { // try // { // Convert.ToInt32 ( sShowCount ) ; // sSql = "SELECT TOP " + sShowCount + " * FROM ( " + sSql + " ) Contract" ; // } // catch // {} // } // // sSql += " ORDER BY Contract.ContractTotal DESC"; return(BaseDataAccess.GetDataTable(sSql)); }
/// <summary> /// 根据TenderID来获得每项对应的SR状态 /// </summary> /// <param name="dt_Source"></param> public string GetTenderStateByTenderIDs(string strTenderID) { string SelectSql = @" SELECT BT_TenderStatus.TypeDescription FROM BT_TenderStatus INNER JOIN TCStrategy ON TCStrategy.status = BT_TenderStatus.IDKey WHERE TCStrategy.TenderID = '" + strTenderID + "'"; string strSRState = string.Empty; DataTable dt_Temp = BaseDataAccess.GetDataTable(SelectSql); if (dt_Temp.Rows.Count > 0) { strSRState = Convert.ToString(dt_Temp.Rows[0]["TypeDescription"]); } return(strSRState); }
public DataTable GetEnquiryMaterial(String mRMaterialID) { string sSql = @"SELECT MR_MaterialRequisition.MRNO, MR_Material.ItemCode, MR_Material.MaterialName, MR_Material.ProductStandard, MR_Material.MFG, MR_Material.PartNO, MR_Material.MaterialUomID, MaterialUOM.UOMID, MRQuantity - isnull(QuantityInEnquiry,0) as CanEnquiryQuantity From MR_Material join MR_MaterialRequisition on MR_Material.MRID = MR_MaterialRequisition.MRID join MaterialUOM on MaterialUOM.ItemCode = MR_Material.ItemCode and IsBaseUOM = 1 WHERE MR_Material.MRMaterialID = '" + mRMaterialID + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); return(dtTempInfo); }
//获得物资单位系数信息 public decimal GetMultipleOfBaseUOM(string sItemCode, string sMaterialUomID) { DataTable dtMaterialUOM = new DataTable(); decimal decMultipleOfBaseUOM = 0; if (sItemCode.Length != 0 && sMaterialUomID.Length != 0) { string sSelectSql = "SELECT MultipleOfBaseUOM FROM MaterialUOM WHERE ItemCode = '" + sItemCode + "' AND MaterialUomID = '" + sMaterialUomID + "'"; dtMaterialUOM = BaseDataAccess.GetDataTable(sSelectSql); } if (dtMaterialUOM != null) { if (dtMaterialUOM.Rows.Count != 0) { decMultipleOfBaseUOM = Convert.ToDecimal(dtMaterialUOM.Rows[0]["MultipleOfBaseUOM"]); } } return(decMultipleOfBaseUOM); }
public void UpdateTransferDEP2DEPMaterial(DataTable dtTransferDEP2DEPMaterial) { foreach (DataRow drTransferDEP2DEPMaterial in dtTransferDEP2DEPMaterial.Rows) { if (drTransferDEP2DEPMaterial.RowState != DataRowState.Deleted) { string sSql = @"SELECT WH_IssueMaterial.*,(WH_IssueMaterial.FactIssuedQuantity -isnull(t.FactIssuedQuantity,0)) as CanTransferNum, MaterialUOM.UOMID,Material.MaterialName From WH_IssueMaterial LEFT JOIN MaterialUOM on WH_IssueMaterial.MaterialUomID = MaterialUOM.MaterialUomID left join Material ON WH_IssueMaterial.ItemCode = Material.ItemCode left join (select isnull(sum(FactIssuedQuantity),0) as FactIssuedQuantity ,WH_TransferDEP2DEPMaterial.IssueMaterialID from WH_TransferDEP2DEPMaterial join WH_TransferDEP2DEP on WH_TransferDEP2DEP.TransferDEP2DEPID = WH_TransferDEP2DEPMaterial.TransferDEP2DEPID where WH_TransferDEP2DEP.Status = 4 group by WH_TransferDEP2DEPMaterial.IssueMaterialID ) t on t.IssueMaterialID = WH_IssueMaterial.IssueMaterialID WHERE WH_IssueMaterial.IssueMaterialID = '" + drTransferDEP2DEPMaterial["IssueMaterialID"].ToString() + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); if (dtTempInfo.Rows.Count > 0) { drTransferDEP2DEPMaterial["ItemCode"] = dtTempInfo.Rows[0]["ItemCode"]; drTransferDEP2DEPMaterial["MaterialName"] = dtTempInfo.Rows[0]["MaterialName"]; //可转数量 drTransferDEP2DEPMaterial["IssueQuantity"] = dtTempInfo.Rows[0]["CanTransferNum"]; //单位 drTransferDEP2DEPMaterial["MaterialUomID"] = dtTempInfo.Rows[0]["MaterialUomID"]; drTransferDEP2DEPMaterial["WH_TransferDEP2DEPMaterial__MaterialUomID"] = dtTempInfo.Rows[0]["UOMID"]; //订单编号 drTransferDEP2DEPMaterial["POID"] = dtTempInfo.Rows[0]["POID"]; drTransferDEP2DEPMaterial["WH_TransferDEP2DEPMaterial__POID"] = dtTempInfo.Rows[0]["POID"]; //基本单位单价(本) drTransferDEP2DEPMaterial["UnitPriceNatural"] = dtTempInfo.Rows[0]["UnitPriceNatural"]; //基本单位单价(核) drTransferDEP2DEPMaterial["UnitPriceStandard"] = dtTempInfo.Rows[0]["UnitPriceStandard"]; } } } }
public DataTable GetIssueMaterialBaseDetial(string strInStockMaterialID, string sDepID) { string sSql = " SELECT WH_InStoreMaterialDetail.*,MaterialUOM.MaterialUomID,Material.MaterialName AS MaterialName," + " ISNULL(WH_InStoreMaterialDetail.QuantityInBin,0) - ISNULL(WH_InStoreMaterialDetail.PreserveQuantity,0) + ISNULL(t.CanIssueQuantityPreserve,0) as CanIssueQuantityInBin," + " ISNULL(t.CanIssueQuantityPreserve,0) AS CanIssueQuantityPreserve " + " From WH_InStoreMaterialDetail inner JOIN MaterialUOM " + " on WH_InStoreMaterialDetail.UOMID = MaterialUOM.UOMID " + " AND MaterialUOM.ItemCode = WH_InStoreMaterialDetail.ItemCode AND MaterialUOM.IsBaseUOM = 1 " + " inner JOIN Material on Material.ItemCode = WH_InStoreMaterialDetail.ItemCode " + " left join " + " ( " + " SELECT a.InStockMaterialID,(ISNULL(QuantityPreserve,0)-Isnull(IssueFactQuantityPreserve,0)) " + " as CanIssueQuantityPreserve " + " from " + " (SELECT InStockMaterialID,SUM(CASE WH_Preserve.IsPreserve WHEN 1 THEN QuantityPreserve ELSE -ISNULL(QuantityByCanceled,0) END) as QuantityPreserve FROM " + " WH_PreserveMaterial " + " JOIN " + " WH_Preserve " + " ON WH_PreserveMaterial.PreserveID = WH_Preserve.PreserveID " + " where WH_Preserve.DepID ='" + sDepID + "' AND WH_Preserve.Status = " + (int)ApproveState.State_Approved + " " + " group by InStockMaterialID " + " ) a " + " LEFT JOIN " + " ( " + " SELECT InStockMaterialID,Sum(PreserveQuantityInFact) as IssueFactQuantityPreserve FROM " + " WH_IssueMaterial " + " JOIN " + " WH_Issue " + " ON WH_IssueMaterial.IssueID = WH_Issue.IssueID " + " where WH_Issue.DepID ='" + sDepID + "' AND WH_Issue.Status = " + (int)ApproveState.State_Approved + " " + " group by InStockMaterialID " + " ) b " + " ON a.InStockMaterialID = b.InStockMaterialID " + " ) t on WH_InStoreMaterialDetail.InStockMaterialID = t.InStockMaterialID " + " WHERE WH_InStoreMaterialDetail.InStockMaterialID = '" + strInStockMaterialID + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); return(dtTempInfo); }
public override List <Ticks> GetSavedTicks(ConfigSettings settings, string tableName) { Dictionary <string, bool> symbols = new Dictionary <string, bool>(); List <Ticks> retTick = new List <Ticks>(); string sql = "SELECT id,symbol,datetime(time),open,high,low,close,volume FROM {0}"; BaseDataAccess sqlite = DataAccessFactory.GetDatabase("SQLite", settings); DataTable dt = sqlite.GetDataTable(string.Format(sql, tableName)); Ticks t = null; foreach (DataRow row in dt.Rows) { var symbol = row[1].ToString(); if (!symbols.ContainsKey(symbol)) { if (symbols.Count() > 0) { retTick.Add(t); } symbols.Add(symbol, true); t = new Ticks(); t.Symbol = symbol; } t.TickGroup.Add(new Tick() { Date = DateTime.Parse(row[2].ToString()), Close = Double.Parse(row[6].ToString()), High = Double.Parse(row[4].ToString()), Low = Double.Parse(row[5].ToString()), Open = Double.Parse(row[3].ToString()), Volume = Int32.Parse(row[7].ToString()) }); } retTick.Add(t); return(retTick); }
//**************************************************** #region 通过获得参考标书文件列表 /// <summary> /// 通过获得参考标书文件列表 /// </summary> /// <param name="strITBIDKey">标书编号</param> /// <returns>数据表</returns> public DataTable GetRefITBDocumentTable(string strITBIDKey, string strFilter) { // string SelectSql = @" SELECT // ITBDocument.ITBNumber , // ITBDocument.SRID As SRName, // ITBDocument.ObjectName As ProjectName, // Attachments.IDKey, // Attachments.AttachName , // Attachments.AttachSize , // Attachments.UploadTime , // Attachments.AttachAddr // FROM ITBDocument,Attachments // WHERE ITBDocument.ITBIDKey = Attachments.ObjectiveID // AND Attachments.ObjectiveType = 'Topis.TendorOperation.ITB' // AND ITBDocument.ITBIDKey = '"+strITBIDKey+"' AND ( ITBDocument.ITBNumber LIKE '%"+strFilter+"%' OR ITBDocument.SRID LIKE '%"+strFilter+"%' OR ITBDocument.ObjectName LIKE '%"+strFilter+"%' OR Attachments.AttachName LIKE '%"+strFilter+"%' )" ; // =================== Modified by Liujun at 12.19 ================ // // 选择标书数据库中状态为历史纪录(State=5)的所有标书的附件 string SelectSql = @" SELECT ITBDocument.ITBNumber , ITBDocument.ObjectName As ProjectName, Attachments.IDKey, Attachments.AttachName , Attachments.AttachSize , Attachments.UploadTime , Attachments.AttachAddr FROM ITBDocument,Attachments WHERE ITBDocument.ITBIDKey = Attachments.ObjectiveID AND ITBDocument.State = " + (int)TenderState.State_ContractSinged + " AND ( ITBDocument.ITBNumber LIKE '%" + strFilter + "%' OR ITBDocument.TenderID LIKE '%" + strFilter + "%' OR ITBDocument.ObjectName LIKE '%" + strFilter + "%' OR Attachments.AttachName LIKE '%" + strFilter + "%' )"; // AND (Attachments.ObjectiveType = 'Topis.TendorOperation.ITB' OR Attachments.ObjectiveType = 'Topis.ProcurementManagement.ITBDocumentDatabase.ITBDocument' ) DataTable dt_Temp = BaseDataAccess.GetDataTable(SelectSql); return(dt_Temp); }
public string getAFEinfo(string afeNo) { string strSql = "SELECT AFEDescription FROM WH_BI_AFE WHERE (AFEID = '" + afeNo + "')"; return(BaseDataAccess.GetDataTable(strSql).Rows[0][0].ToString()); }
public DataTable GetRptData_ReportPreserve(string sWhere) { //一行 string sSql = string.Empty; sSql += "SELECT dbo.Material.UOMID, dbo.Material.MaterialName, " + "dbo.BI_Department.DepartmentName, dbo.WH_Preserve.PreserveDate, " + "dbo.WH_Preserve.PreserveNO, dbo.WH_PreserveMaterial.ItemCode," + "dbo.WH_PreserveMaterial.POID, dbo.WH_PreserveMaterial.BINID, " + "dbo.WH_PreserveMaterial.QuantityPreserve, dbo.WH_BI_WareHouse.WHID," + "dbo.POMaterial.UnitPrice, dbo.POMaterial.UnitPrice AS SumPrice, " + "dbo.POMaterial.UnitPrice * Z.StandardExchangeRate AS StandardUnitPrice," + "dbo.POMaterial.UnitPrice * Z.NaturalExchangeRate AS NaturalUnitPrice, " + "dbo.WH_PreserveMaterial.QuantityPreserve * dbo.POMaterial.UnitPrice * Z.StandardExchangeRate " + "AS StandardSumPrice, " + "dbo.WH_PreserveMaterial.QuantityPreserve * dbo.POMaterial.UnitPrice * Z.NaturalExchangeRate " + "AS NaturalSumPrice " + "FROM dbo.WH_PreserveMaterial INNER JOIN " + "dbo.Material ON " + "dbo.WH_PreserveMaterial.ItemCode = dbo.Material.ItemCode INNER JOIN " + "dbo.WH_Preserve ON " + "dbo.WH_Preserve.PreserveID = dbo.WH_PreserveMaterial.PreserveID INNER JOIN " + "dbo.POMaterial ON dbo.WH_PreserveMaterial.POID = dbo.POMaterial.POID AND " + "dbo.WH_PreserveMaterial.ItemCode = dbo.POMaterial.ItemCode INNER JOIN " + "dbo.MaterialUOM ON " + "dbo.MaterialUOM.ItemCode = dbo.Material.ItemCode INNER JOIN " + "dbo.WH_BI_WareHouse ON " + "dbo.WH_BI_WareHouse.WHID = dbo.WH_Preserve.WHID INNER JOIN " + "dbo.BI_Department ON " + "dbo.BI_Department.IDKey = dbo.WH_Preserve.DepID INNER JOIN " + "dbo.BI_Employee ON " + "dbo.BI_Employee.IDKey = dbo.WH_Preserve.EmployeeID LEFT OUTER JOIN " + " (SELECT A.ItemCode, A.POID, B.StandardExchangeRate, " + " C.NaturalExchangeRate " + " FROM (SELECT POMaterial.ItemCode, PurchaseOrder.POID, " + " PurchaseOrder.ContractTotalCostCUR, POMaterial.UnitPrice " + " FROM POMaterial LEFT JOIN " + " PurchaseOrder ON PurchaseOrder.POID = POMaterial.POID) A LEFT " + " JOIN " + " (SELECT BI_CurrencyExchangeRate.CurrencyIDFrom, " + " BI_CurrencyExchangeRate.ExchangeRate AS StandardExchangeRate " + " FROM BI_SysCurrency LEFT JOIN " + " BI_CurrencyExchangeRate ON " + " BI_CurrencyExchangeRate.CurrencyIDTo = BI_SysCurrency.StandardCurrencySymbol) " + " B ON A.ContractTotalCostCUR = B.CurrencyIDFrom LEFT JOIN " + " (SELECT BI_CurrencyExchangeRate.CurrencyIDFrom, " + " BI_CurrencyExchangeRate.ExchangeRate AS NaturalExchangeRate " + " FROM BI_SysCurrency LEFT JOIN " + " BI_CurrencyExchangeRate ON " + " BI_CurrencyExchangeRate.CurrencyIDTo = BI_SysCurrency.NaturalCurrencySymbol) " + " C ON A.ContractTotalCostCUR = C.CurrencyIDFrom) Z ON " + " dbo.WH_PreserveMaterial.ItemCode = Z.ItemCode AND " + " dbo.WH_PreserveMaterial.POID = Z.POID "; if (sWhere != null && sWhere.Length > 0) { sSql += " WHERE " + sWhere + ""; } // // if ( sShowCount != null && sShowCount.Length != 0 ) // { // try // { // Convert.ToInt32 ( sShowCount ) ; // sSql = "SELECT TOP " + sShowCount + " * FROM ( " + sSql + " ) Contract" ; // } // catch // {} // } // // sSql += " ORDER BY Contract.ContractTotal DESC"; return(BaseDataAccess.GetDataTable(sSql)); }
public DataTable CheckIsTransferQuantity(string sql) { DataTable dtTempInfo = BaseDataAccess.GetDataTable(sql); return(dtTempInfo); }
public void UpdatePreserveMaterial(DataTable dtPreserveMaterial, string sDepID) { foreach (DataRow drPreserveMaterial in dtPreserveMaterial.Rows) { if (drPreserveMaterial.RowState != DataRowState.Deleted) { string sSql = " SELECT WH_InStoreMaterialDetail.*,MaterialUOM.MaterialUomID, " + " ISNULL(FactIssuedPreQuantity,0) AS FactIssuedPreQuantity, " + " ISNULL(PreserveQuantitySum,0) - ISNULL(FactIssuedPreQuantity,0) as CanBackPreQuantity," + " ISNULL(WH_InStoreMaterialDetail.QuantityInBin,0) - ISNULL(WH_InStoreMaterialDetail.PreserveQuantity,0 ) as CanPreQuantity," + " ISNULL(PreserveQuantitySum,0) AS PreserveQuantitySum, Material.MaterialName "+ " From WH_InStoreMaterialDetail LEFT JOIN MaterialUOM "+ " on WH_InStoreMaterialDetail.UOMID = MaterialUOM.UOMID "+ " AND MaterialUOM.ItemCode = WH_InStoreMaterialDetail.ItemCode AND MaterialUOM.IsBaseUOM = 1 "+ " Left JOIN Material ON Material.ItemCode = WH_InStoreMaterialDetail.ItemCode "+ " left JOIN " + " (SELECT InStockMaterialID, "+ " SUM(PreserveQuantityInFact) "+ " AS FactIssuedPreQuantity "+ " FROM WH_IssueMaterial "+ " left join WH_Issue on WH_IssueMaterial.IssueID = WH_Issue.IssueID "+ " where DepID = '"+ sDepID + "' AND WH_Issue.Status = " + (int)ApproveState.State_Approved + " " + " GROUP BY InStockMaterialID "+ " ) a "+ " ON "+ " WH_InStoreMaterialDetail.InStockMaterialID = a.InStockMaterialID "+ " left JOIN "+ " ( "+ " SELECT InStockMaterialID,SUM(CASE WH_Preserve.IsPreserve WHEN 1 THEN QuantityPreserve ELSE -QuantityByCanceled END) AS PreserveQuantitySum "+ " FROM WH_PreserveMaterial left join WH_Preserve "+ " on WH_PreserveMaterial.PreserveID = WH_Preserve.PreserveID "+ " where DepID = '"+ sDepID + "' AND WH_Preserve.Status = " + (int)ApproveState.State_Approved + " " + " GROUP BY InStockMaterialID "+ " ) b "+ " ON WH_InStoreMaterialDetail.InStockMaterialID = b.InStockMaterialID "+ " WHERE WH_InStoreMaterialDetail.InStockMaterialID = '" + drPreserveMaterial["InStockMaterialID"].ToString() + "'"; DataTable dtTempInfo = BaseDataAccess.GetDataTable(sSql); if (dtTempInfo.Rows.Count > 0) { //原库位 drPreserveMaterial["BINID"] = dtTempInfo.Rows[0]["BINID"]; drPreserveMaterial["WH_PreserveMaterial__BINID"] = dtTempInfo.Rows[0]["BINID"]; //单位 drPreserveMaterial["MaterialUomID"] = dtTempInfo.Rows[0]["MaterialUomID"]; drPreserveMaterial["WH_PreserveMaterial__MaterialUomID"] = dtTempInfo.Rows[0]["UOMID"]; //订单编号 drPreserveMaterial["POID"] = dtTempInfo.Rows[0]["POID"]; drPreserveMaterial["WH_PreserveMaterial__POID"] = dtTempInfo.Rows[0]["POID"]; //预留数量 //drPreserveMaterial["QuantityPreserve"] = dtTempInfo.Rows[0]["PreserveQuantitySum"] ; //实发数量 drPreserveMaterial["QuantityIssuedFact"] = dtTempInfo.Rows[0]["FactIssuedPreQuantity"]; //库存数量 drPreserveMaterial["QuantityInBin"] = dtTempInfo.Rows[0]["CanPreQuantity"]; //物资编码 drPreserveMaterial["ItemCode"] = dtTempInfo.Rows[0]["ItemCode"]; //物资名称 drPreserveMaterial["MaterialName"] = dtTempInfo.Rows[0]["MaterialName"]; } } } }