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"];
}
}
}
}