/// <summary>
/// 通过PubishID得到 title、content、releaseDate信息
/// </summary>
public DataTable GetBidPlacardInfo(string publishID)
{
string strSql = @" select Title ,Contents ,PublishDate from BidPlacard where publishID = '" + publishID + "'";
DataTable dt = _da.GetDataTable(strSql);
return(dt);
}
public string GetMaxID(string MRPlanType)
{
// strSql = "SELECT CategoryID FROM MR_BI_Category WHERE CategoryID = '"+MRPlanType+"'";
// DataTable dt = _da.GetDataTable(strSql);
strSql = "SELECT dbo.f_GetMrNO() ";
return(_da.GetDataTable(strSql).Rows[0][0].ToString());
}
// Add by ZZH on 2008-1-21 添加验证是否可以删除的方法
public DataTable CheckState(String strTenderID)
{
String strSql = " Select Max(Contract.State) as CheckState From TCStrategy Inner Join Contract On TCStrategy.TenderID = Contract.TenderNumber Where TCStrategy.TenderID ='" + strTenderID + "'";
DataTable dt = _da.GetDataTable(strSql);
return(dt);
}
// Add by ZZH on 2008-1-21 添加验证是否可以删除的方法
public DataTable CheckState(String strTenderID)
{
String strSql = " Select Max(PurchaseOrder.ApproveStatus) as CheckState From TCStrategy Inner Join PurchaseOrder On TCStrategy.TenderID = PurchaseOrder.TenderID Where TCStrategy.TenderID ='" + strTenderID + "'";
DataTable dt = _da.GetDataTable(strSql);
return(dt);
}
/// <summary>
/// 验证申请编号是否存在
/// wanglijie on 2008-02-03
/// </summary>
/// <param name="MRNO"></param>
/// <returns></returns>
public string CheckMRNO(string MRNO)
{
string sSql = "SELECT * FROM MR_MaterialRequisition WHERE MRNO = '" + MRNO + "'";
if (_da.GetDataTable(sSql).Rows.Count > 0)
{
return("ExistMRNO");
}
return("");
}
public String GetDescription(String idStr)
{
String sqlStr = " Select CatalogDescription From MaterialCatalog Where CatalogID='" + idStr + "'";
DataTable dt = _da.GetDataTable(sqlStr);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0]["CatalogDescription"] == DBNull.Value ? "" : dt.Rows[0]["CatalogDescription"].ToString());
}
return(String.Empty);
}
public string GetSelectTenderID(string ITBpkvalue)
{
string SelectSql = " SELECT TenderID FROM ITBDocument WHERE ITBIDKey = '" + ITBpkvalue + "'";
DataTable dt_Temp = _da.GetDataTable(SelectSql);
if (dt_Temp.Rows.Count > 0)
{
return(dt_Temp.Rows[0][0].ToString());
}
else
{
return("");
}
}
public DataTable GetPrintData(string sSRID)
{
string sSelectSql = "SELECT * FROM v_Report_SRPrint WHERE SRIDKey = '" + sSRID + "'";
DataTable dtData = _da.GetDataTable(sSelectSql);
return(dtData);
}
//*********************************************************
public string GetTypeID(string sTenderID)
{
string sSql = " SELECT MRTypeID FROM TCStrategy WHERE TenderID = '" + sTenderID + "'";
DataTable dt = _da.GetDataTable(sSql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("");
}
}
/// <summary>
/// 得到ServiceRequistion 的IDKEY
/// </summary>
/// <param name="SIDkey"></param>
/// <returns></returns>
public string GetTenderID(string PublishID)
{
string strSql = @"select TenderID from BidPlacard
where BidPlacard.PublishID ='" + PublishID + "'";
DataTable dt = _da.GetDataTable(strSql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]["TenderID"].ToString());
}
else
{
return("");
}
}
public System.Data.DataTable GetVendorInfoByVendorID(string sIDkey)
{
string strSql = "SELECT Vendor.VendorNo,Vendor.VendorName,Vendor.Address,Vendor.Telphone,Vendor.Fax,Vendor.Email " +
"FROM Vendor where Vendor.IDKey = '" + sIDkey + "'";
System.Data.DataTable dt = _da.GetDataTable(strSql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 通过外键(主表的主键)来获得子表的主键*****解决了VoucherEdit控件不能作为子控件的Bug
/// <param name="ParentKey"></param>
/// <returns></returns>
public string GetIDKey(string ParentKeyFieldName, string ParentValue, string ChildTableName)
{
string IDKey = string.Empty;
string SelectSql = " SELECT ID FROM " + ChildTableName + " WHERE " + ParentKeyFieldName + " = '" + ParentValue + "'";
System.Data.DataTable dataTable = _da.GetDataTable(SelectSql);
if (dataTable.Rows.Count > 0)
{
IDKey = Convert.ToString(dataTable.Rows[0][0]);
}
return(IDKey);
}
public DataTable GetMaterial(string sIDKey)
{
string strSql = "";
if (sIDKey != null && sIDKey.Length > 0)
{
strSql = @"select * from WH_MaterialVendor where WH_MaterialVendor.VendorID = '" + sIDKey + "'";
}
else
{
strSql = @"select * from WH_MaterialVendor where 1 > 2 ";
}
DataTable dt = _da.GetDataTable(strSql);
return(dt);
}
public DataTable GetMaterialInfos(string sItemCode)
{
string strSql = @"select MaterialName , UOMID from Material
where ItemCode = '" + sItemCode + "'";
Cnwit.Utility.DataAcess da = Common.GetProjectDataAcess.GetDataAcess();
return(da.GetDataTable(strSql));
}
public int UpdataDataTable_MaterialList(DataTable dataTable, int nPOSeed, string sWHID)
{
string SelectSql = string.Empty;
DataTable dt_Temp;
string BINID = GetBINIDFromWHID(sWHID);
foreach (DataRow dr in dataTable.Rows)
{
if (dr.RowState != DataRowState.Deleted)
{
SelectSql = @"
SELECT
Material.ItemCode ,
Material.MaterialName ,
MaterialUOM.UOMID ,
MaterialUOM.MaterialUomID
From
Material inner join MaterialUOM on Material.ItemCode = MaterialUOM.ItemCode
WHERE
Material.ItemCode = '" + dr["ItemCode"].ToString() + "'";
dt_Temp = _da.GetDataTable(SelectSql);
if (dt_Temp.Rows.Count > 0)
{
dr["BINID"] = BINID;
dr["WH_AdjustInMaterial__BINID"] = BINID;
dr["WH_AdjustInMaterial__ItemCode"] = dr["ItemCode"];
dr["MaterialName"] = dt_Temp.Rows[0]["MaterialName"];
dr["MaterialUomID"] = dt_Temp.Rows[0]["MaterialUomID"];
dr["WH_AdjustInMaterial__MaterialUomID"] = dt_Temp.Rows[0]["UOMID"];
if (dr["RowStatus"].ToString() == "NEW")
{
if (dr["POID"].ToString().Length == 0)
{
dr["POID"] = CreatePOID(nPOSeed);
nPOSeed++;
}
}
}
}
}
// dataTable.AcceptChanges();
return(nPOSeed);
}
public int GetMREnquiryPriceState(string strPkValue, string UserID, string popedomNew)
{
String strSql = "SELECT * FROM MR_EnquiryPrice WHERE charindex(','+convert(varchar,MR_EnquiryPrice.Status)+',','," + popedomNew + ",')>0 AND MR_EnquiryPrice.EnquiryPriceID='" + strPkValue + "' AND MR_EnquiryPrice.CreateBy= '" + UserID + "'";
DataTable dtMREnquiryPriceState = _da.GetDataTable(strSql);
return(dtMREnquiryPriceState.Rows.Count);
}
public void GetIsTCVendorList(DataTable dtTemp, string sTenderID)
{
strSql = "SELECT Vendor.IDKey,Vendor.VendorName,ITBDocument.ITBIDKey FROM TCStrategyVendor,ITBDocument,Vendor " +
" WHERE TCStrategyVendor.VendorCode = Vendor.IDkey AND ITBDocument.TenderID=TCStrategyVendor.TenderID AND " +
" ITBDocument.TenderID= '" + sTenderID + "' ";
DataTable dt = _da.GetDataTable(strSql);
int nCount = dt.Rows.Count;
for (int i = 0; i < nCount; i++)
{
DataRow dr = dtTemp.NewRow();
dr["ID"] = System.Guid.NewGuid().ToString();
dr["ITBDocumentID"] = dt.Rows[i]["ITBIDKey"].ToString();
dr["VendorID"] = dt.Rows[i]["IDKey"].ToString();
dr["VendorName"] = dt.Rows[i]["VendorName"].ToString();
dr["RowStatus"] = "New";
dtTemp.Rows.Add(dr);
}
}
public int ExistsItemCode(string sItemCode, string sWHID)
{
string strSql = @"select ItemCode from MaxMinMaterial
where ItemCode = '" + sItemCode + "' and WHID='" + sWHID + "'";
Cnwit.Utility.DataAcess da = Common.GetProjectDataAcess.GetDataAcess();
DataTable dt = da.GetDataTable(strSql);
return(dt.Rows.Count);
}
public int GetPurchaseOrderState(string strPkValue, string UserID, string popedomNew)
{
String strSql = "SELECT * FROM PurchaseOrder WHERE charindex(','+convert(varchar,PurchaseOrder.ApproveStatus)+',','," + popedomNew + ",')>0 AND PurchaseOrder.POID='" + strPkValue + "' AND PurchaseOrder.CreateBy= '" + UserID + "'";
DataTable dtPurchaseOrderState = _da.GetDataTable(strSql);
return(dtPurchaseOrderState.Rows.Count);
}
//循环的给审批通过的供应商发送邮件
// Add by ZZH on 2008-1-15 添加删除记录时PutIn表中的记录状态为-1
public String UpdateRecordStateOnPutIn(String pkValue)
{
String strSql = " Select PutINIDKey From TCMeetingReport Where ID='" + pkValue + "'";
DataTable dt = _da.GetDataTable(strSql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UpDatePutInState(-1, Convert.ToString(row["PutINIDKey"]));
}
}
return("");
}
public void ResumptionLastExchangeRate(string pkName)
{
DataTable dt = new DataTable();
string sSql = "Select CurrencyIDFrom, CurrencyIDTo From BI_CurrencyExchangeRate Where IDKey = '" + pkName + "' And Status = '0'";
dt = da.GetDataTable(sSql);
if (dt != null && dt.Rows.Count > 0)
{
string strCurrencyIDFrom = string.Empty;
string strCurrencyIDTo = string.Empty;
strCurrencyIDFrom = dt.Rows[0][0].ToString();
strCurrencyIDTo = dt.Rows[0][1].ToString();
StringBuilder sb = new StringBuilder();
sb.Append("Update BI_CurrencyExchangeRate Set Status = '0' ");
sb.Append(" Where Status = '1' And CurrencyIDFrom='" + strCurrencyIDFrom + "' And CurrencyIDTo ='" + strCurrencyIDTo + "'");
sb.Append(" And DateFrom = ( Select Top 1 DateFrom From BI_CurrencyExchangeRate B Where CurrencyIDFrom = '" + strCurrencyIDFrom + "' And CurrencyIDTo = '" + strCurrencyIDTo + "' And Status = '1' Order By DateFrom DESC)");
da.ExecuteDMLSQL(sb.ToString());
}
}
/// <summary>
/// 通过获得参考标书文件列表
/// </summary>
/// <param name="strITBIDKey">标书编号</param>
/// <returns>数据表</returns>
public DataTable GetRefITBDocumentTable(string strITBIDKey, string strFilter)
{
// 选择标书数据库中状态为历史纪录（State=5）的所有标书的附件
string SelectSql = @" SELECT
WH_CheckStock.CheckStockNO ,
Attachments.IDKey,
Attachments.AttachName ,
Attachments.AttachSize ,
Attachments.UploadTime ,
Attachments.AttachAddr
FROM WH_CheckStock,Attachments
WHERE WH_CheckStock.CheckStockID = Attachments.ObjectiveID " ;
DataTable dt_Temp = _da.GetDataTable(SelectSql);
return(dt_Temp);
}
public DataTable GetIssueInfos(string sIssueID)
{
string strSql = @"
SELECT
WH_Issue.WHID , WH_BI_WareHouse.WHName ,
WH_Issue.DepID , BI_Department.DepartmentName ,
WH_Issue.EmployeeIDReceive , BI_Employee.FullName , WH_Issue.AFEID, WH_BI_AFE.AFEDescription
FROM
WH_Issue inner join WH_BI_WareHouse
on WH_Issue.WHID = WH_BI_WareHouse.WHID
inner join BI_Department
on WH_Issue.DepID = BI_Department.IDKey
inner join BI_Employee
on WH_Issue.EmployeeIDReceive = BI_Employee.IDKey
inner join WH_BI_AFE on WH_BI_AFE.AFEID = WH_Issue.AFEID
WHERE
WH_Issue.IssueID = '" + sIssueID + "'";
return(_da.GetDataTable(strSql));
}
/// <summary>
///
/// </summary>
/// <param name="CatalogID"></param>
/// <returns></returns>
public decimal GetNextDeep(string CatalogID)
{
DataTable dtGetDeep;
decimal objReNum = 0;
if (CatalogID != null)
{
if (CatalogID.Length != 0)
{
string strSql = "Select CatalogDeep From MaterialCatalog Where CatalogID ='" + CatalogID + "'";
dtGetDeep = _da.GetDataTable(strSql);
if (dtGetDeep != null)
{
if (dtGetDeep.Rows.Count != 0)
{
objReNum = System.Convert.ToDecimal(dtGetDeep.Rows[0]["CatalogDeep"]);
}
}
}
}
return(objReNum + 1);
}
public string GetObjectTypeID(string SIDkey)
{
strSql = "SELECT ObjectiveType FROM PutIn WHERE IDKey='" + SIDkey + "'";
return(_da.GetDataTable(strSql).Rows[0]["ObjectiveType"].ToString());
}
public DataTable GetFileURL(string pkValue)
{
string sSql = " select * from V_InventoryReport where AttachmentIDKey = '" + pkValue + "' ";
return(_da.GetDataTable(sSql));
}
public DataTable GetRecord(String strPKValue)
{
//商务评标有其特殊性，这里判断其对应的合同里是否有记录，有记录则不允许删除
String mrType = String.Empty;
String strSql = String.Empty;
String sqlStr = " Select TCStrategy.MRTypeID From TCStrategy Inner Join CommEvaluation On TCStrategy.TenderID = CommEvaluation.TenderID Where CommEvaluation.IDKey='" + strPKValue + "'";
DataTable dtt = _da.GetDataTable(sqlStr);
if (dtt != null && dtt.Rows.Count > 0)
{
mrType = dtt.Rows[0]["MRTypeID"] == DBNull.Value ? "0" : dtt.Rows[0]["MRTypeID"].ToString();
}
switch (Convert.ToInt32(mrType))
{
case (int)StrategyType.MR:
strSql = " Select Count(*) as State From PurchaseOrder Inner Join TCStrategy On PurchaseOrder.TenderID=TCStrategy.TenderID Inner Join CommEvaluation On CommEvaluation.TenderID = TCStrategy.TenderID Where CommEvaluation.IDKey='" + strPKValue + "'";
break;
case (int)StrategyType.SR:
strSql = " Select Count(*) as State From Contract Inner Join TCStrategy On Contract.TenderNumber=TCStrategy.TenderID Inner Join CommEvaluation On CommEvaluation.TenderID = TCStrategy.TenderID Where CommEvaluation.IDKey='" + strPKValue + "'";
break;
}
DataTable dt = new DataTable();
if (strSql.Length > 0)
{
dt = _da.GetDataTable(strSql);
}
return(dt);
}
public string GetMaxID()
{
strSql = "SELECT dbo.f_GetSRNO() ";
return(_da.GetDataTable(strSql).Rows[0][0].ToString());
}
public DataTable GetEnauiryPriceId(String bidEvaluationId)
{
String strSql = "Select EnquiryPriceId From MR_BIDEvaluation Where BIDEvaluationID='" + bidEvaluationId + "'";
DataTable dtEnquiryPriceId = _da.GetDataTable(strSql);
return(dtEnquiryPriceId);
}
public DataTable GetMR_SDTInfo(string strPKValue)
{
string strSql = "Select * from MR_SDT Where SDTIDKey='" + strPKValue + "'";
return(_da.GetDataTable(strSql));
}