62,041
社区成员
发帖
与我相关
我的任务
分享
public static string GetBasicInfoTypes(SqlConnection cn, string VTNAME, string CNAME, string CSNAME, String VendorCode)
{
StringBuilder sb = new StringBuilder();
string SVTNAME = StringHelper.EmptyOrUnescapedStringViaUrlDecode(VTNAME);
string SCNAME = StringHelper.EmptyOrUnescapedStringViaUrlDecode(CNAME);
string SCSNAME = StringHelper.EmptyOrUnescapedStringViaUrlDecode(CSNAME);
sb.Append(@"
SELECT ROW_NUMBER() over(order by X.Score desc) as rows,
X.BasicInfoGUID,X.TB_SQM_Vendor_TypeCID,X.TB_SQM_CommodityCID,X.TB_SQM_Commodity_SubCID,
X.VTNAME,X.CNAME,X.CSNAME,
X.PlantCode,X.VendorCode,X.ISChoose,X.Score,X.Status
FROM (
SELECT BasicInfoGUID,
BI.VendorCode as Ven,
B.VendorCode ,
B.PlantCode,
TB_SQM_Vendor_TypeCID,
TB_SQM_Commodity_SubCID,
VT.CNAME AS VTNAME,
C.CNAME AS CNAME,
CS.TB_SQM_CommodityCID,
CS.CNAME AS CSNAME,
BI.IsChoose AS ISChoose,
Score,
ISNULL(AC.Status, 'None') AS Status
FROM TB_SQM_Manufacturers_BasicInfo BI
LEFT OUTER JOIN TB_SQM_Approve_Case AC ON BI.ChooseCaseID = AC.CaseID,
TB_SQM_Vendor_Type VT,
TB_SQM_Commodity_Sub CS,
TB_SQM_Commodity C,
TB_SQM_Member_Vendor_Map B
WHERE BI.TB_SQM_Vendor_TypeCID = VT.CID
AND BI.TB_SQM_Commodity_SubTB_SQM_CommodityCID = C.CID
AND BI.TB_SQM_Commodity_SubTB_SQM_CommodityCID = CS.TB_SQM_CommodityCID
AND BI.TB_SQM_Commodity_SubCID = CS.CID
AND B.[MemberGUID] = BI.[BasicInfoGUID]
--and BI.VendorCode = @VendorCode
) X
INNER JOIN [dbo].[TB_SQM_Member_Plant] ON [TB_SQM_Member_Plant].[MemberGUID] = X.Ven
");
if (SVTNAME.Equals(string.Empty) || SCNAME.Equals(string.Empty) || SCSNAME.Equals(string.Empty))
{
return "";
}
else
{
sb.Append(" Where X.TB_SQM_Vendor_TypeCID = @VTNAME and X.TB_SQM_CommodityCID = @CNAME ");
sb.Append(" and X.TB_SQM_Commodity_SubCID = @CSNAME");
}
sb.Append(" order by X.Score DESC");
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), cn))
{
cmd.Parameters.Add(new SqlParameter("@VTNAME", StringHelper.NullOrEmptyStringIsDBNull(VTNAME)));
cmd.Parameters.Add(new SqlParameter("@CNAME", StringHelper.NullOrEmptyStringIsDBNull(CNAME)));
cmd.Parameters.Add(new SqlParameter("@CSNAME", StringHelper.NullOrEmptyStringIsDBNull(CSNAME)));
cmd.Parameters.Add(new SqlParameter("@VendorCode", VendorCode));
//cmd.Parameters.Add(new SqlParameter("@VendorCode", VendorCode));
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
}
}
return JsonConvert.SerializeObject(dt);
}