You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
445 lines
25 KiB
445 lines
25 KiB
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Repository;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Application.SRM
|
|
{
|
|
public class BicDoc_BidResultApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public DataTable GetGrid(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string sql = @" SELECT
|
|
Distinct
|
|
b.ID,
|
|
a.BidCode ,
|
|
a.BidName ,
|
|
a.BidUser as F_RealName ,
|
|
a.StarTime ,
|
|
a.EndTime ,
|
|
a.BidStatus ,
|
|
a.Remark ,
|
|
u2.F_RealName as OpenName,
|
|
a.OpenTime ,
|
|
u3.VenName as Bidder ,
|
|
b.BidTime ,
|
|
CASE WHEN d.ZBVendorCode = '{0}' THEN '中标' ELSE '未中标' END AS IsDids,
|
|
u4.F_RealName as AwardUser ,
|
|
a.BidTime as AwardTime,
|
|
a.WorkPoint
|
|
FROM ICSBidDoc a
|
|
LEFT JOIN ICSBidDocBD e on a.BidCode=e.BidCode
|
|
LEFT JOIN ICSBidInfo b ON a.BidCode=b.BidCode and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN (
|
|
SELECT X.* FROM ICSBidInfoDetail X
|
|
inner JOIN (
|
|
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a
|
|
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
|
|
GROUP BY a.BDID,a.MUSERName
|
|
) Y
|
|
ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID AND X.MUSERName=Y.MUSERName) d ON d.InfoID = b.ID AND d.BDID = e.ID and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN ICSVendor c ON b.SupplierCode = c.VenCode and b.WorkPoint=c.WorkPoint
|
|
LEFT JOIN Sys_SRM_User u1 ON a.BidUser = u1.F_Account and a.WorkPoint=u1.F_Location
|
|
LEFT JOIN Sys_SRM_User u2 ON a.Tenders = u2.F_Account and a.WorkPoint=u2.F_Location
|
|
LEFT JOIN ICSVendor u3 ON b.SupplierCode = u3.VenCode and b.WorkPoint=u3.WorkPoint
|
|
LEFT JOIN Sys_SRM_User u4 ON a.Bidder = u4.F_Account and a.WorkPoint=u4.F_Location
|
|
WHERE 1=1 and a.BidCode<>'' AND a.BidStatus<>'已关闭' AND a.BidStatus<>'已保存'";
|
|
sql = string.Format(sql, VenCode);
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BidCode"].ToString()))
|
|
{
|
|
sql += " and a.BidCode like '%" + queryParam["BidCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
|
|
{
|
|
sql += " and b.BidTime >= '" + queryParam["BeginDate"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
|
|
{
|
|
sql += " and b.BidTime <= '" + queryParam["EndDate"].ToString() + "' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and u3.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetSubGridJson(string BidCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT a.ASNDETAILID, a.LOTNO,a.ITEMCODE,a.ITEMNAME,c.CartonNO,b.LOTQTY,
|
|
a.STNO,b.VenderLotNO,b.ORDERNO,b.TransNO,b.TransLine,d.INVDESC,d.INVTYPE,e.MEMO,d.INVSTD,d.INVPARSETYPE
|
|
FROM dbo.ICSASNDETAIL a
|
|
LEFT JOIN dbo.ICSITEMLot b ON a.LOTNO =b.LotNO AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSITEMLot2Carton c ON b.LotNO=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSINVENTORY d ON a.ITEMCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN dbo.ICSPO_PoMain e ON b.TransNO=e.POCode AND b.TransLine=e.PORow AND a.WorkPoint=e.WorkPoint
|
|
WHERE b.TYPE='采购原料' AND a.STNO='" + BidCode + "' and a.WorkPoint in (" + WorkPoint + ") ";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetICSBidDocInfo(string BidCode,string WorkPoint)
|
|
{
|
|
string sql = @"SELECT ID, BidCode, BidCurrenCY, BidName, BidUser, StarTime, EndTime, BidStatus,
|
|
BidType, Remark, Tenders, OpenTime, Bidder, BidTime, LogTime,
|
|
LogUser, WorkPoint, MUSER, MUSERName, MTIME, ENTTRIBUTE1 from dbo.ICSBidDoc
|
|
WHERE 1=1
|
|
and BidCode='" + BidCode + "' and WorkPoint='" + WorkPoint + "'";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetSubGridJsonResult(string BidCode, string WorkPoint, ref Pagination jqgridparam)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string sql = @"SELECT distinct a.ID,a.BidCode,a.RowNo,a.Quantity,a.DeliveryTime,
|
|
a.InvCode,b.INVNAME AS InvName,b.InvUnit AS Unit,'ERP' as Source,
|
|
d.CastPrice,a.ENTTRIBUTE1,a.ItemType,a.Remark,
|
|
CASE WHEN d.IsCast =1 THEN '是' ELSE '否' END AS IsCasts,
|
|
CASE WHEN d.ZBVendorCode = '{2}' THEN '中标' ELSE '未中标' END AS IsDids,f.F_RealName
|
|
FROM ICSBidDocBD a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN ICSBidInfo c ON c.BidCode = a.BidCode AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = a.ID AND a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode AND a.WorkPoint=e.WorkPoint
|
|
LEFT JOIN Sys_SRM_User f ON f.F_VenCode = e.VenCode AND a.WorkPoint=f.F_Location
|
|
WHERE a.WorkPoint = '{0}' AND c.BidCode='{1}'";
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and f.F_VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
|
|
}
|
|
sql = string.Format(sql, WorkPoint, BidCode, MUSER);
|
|
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetGridCourse(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string wor = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"SELECT
|
|
a.ID,
|
|
a.BidCode ,
|
|
a.BidName ,
|
|
a.BidUser AS BidUser,
|
|
a.StarTime ,
|
|
a.EndTime ,
|
|
a.BidStatus ,
|
|
a.BidStatus as BidStatuss ,
|
|
a.Remark ,
|
|
c.F_RealName AS Tenders,
|
|
a.OpenTime ,
|
|
d.F_RealName AS Bidder,
|
|
a.BidTime ,
|
|
a.WorkPoint
|
|
FROM ICSBidDoc a
|
|
LEFT JOIN Sys_SRM_User b ON a.BidUser=b.F_Account and a.WorkPoint=b.F_Location
|
|
LEFT JOIN Sys_SRM_User c ON a.Tenders=c.F_Account and a.WorkPoint=c.F_Location
|
|
LEFT JOIN Sys_SRM_User d ON a.Bidder=d.F_Account and a.WorkPoint=d.F_Location
|
|
where 1=1 AND a.BidCode <>''
|
|
";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BidCode"].ToString()))
|
|
{
|
|
sql += " and a.BidCode like '%" + queryParam["BidCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["StarTime"].ToString()))
|
|
{
|
|
sql += " and a.StarTime >= '" + queryParam["StarTime"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["EndTime"].ToString()))
|
|
{
|
|
sql += " and a.StarTime <= '" + queryParam["EndTime"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["selShow"].ToString()))
|
|
{
|
|
string selShow = queryParam["selShow"].ToString();
|
|
if (selShow == "1")
|
|
sql += " and BidStatus='已关闭'";
|
|
else if (selShow == "2")
|
|
sql += "and BidStatus='已决标'";
|
|
else if (selShow == "3")
|
|
sql += "and BidStatus='招标中'";
|
|
else if (selShow == "4")
|
|
sql += "and BidStatus='已结束'";
|
|
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and b.F_Account in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetSubGridJsonCourse(string BidCode, string WorkPoint, ref Pagination jqgridparam)
|
|
{
|
|
|
|
string sql = @"SELECT distinct a.*,b.INVNAME AS InvName,b.INVSTD AS InvStd,b.INVUOM AS Unit,'ERP' as Source,d.ZBVendorCode
|
|
FROM ICSBidDocBD a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.INVCODE and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN ICSBidInfoDetail d ON d.BDID = a.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.BidCode = '{0}' and a.WorkPoint='{1}' ";
|
|
sql = string.Format(sql, BidCode, WorkPoint);
|
|
return Repository().FindTablePageBySql(sql.ToString(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetBidCourseInfo(string BidCode, string WorkPoint,string ID)
|
|
{
|
|
string sql = @"SELECT a.ID,a.InvCode,b.INVNAME AS InvName,b.INVSTD AS InvStd,b.InvUnit AS InvUnit,
|
|
a.Quantity,a.DeliveryTime,a.ENTTRIBUTE1,a.ItemType,a.Remark,c.BidCode,c.BidName
|
|
FROM ICSBidDocBD a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.INVCODE
|
|
LEFT JOIN ICSBidDoc c ON a.BidCode = c.BidCode
|
|
WHERE a.BidCode='{0}' AND a.ID ='{1}' AND a.WorkPoint='{2}'";
|
|
sql = string.Format(sql, BidCode,ID, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public DataTable GetSubGridJsonCourseInfo(string BidCode, string WorkPoint, string ID,ref Pagination jqgridparam)
|
|
{
|
|
|
|
string sql = @" SELECT b.ID,c.VenCode cVenCode,c.VenName cVenName,'ERP'as Source,b.CastPrice,
|
|
b.IsDid,a.ID AS InfoID,b.BDID,d.BidStatus,b.ENTTRIBUTE1 AS Remark,
|
|
(CASE d.BidStatus WHEN '已决标' THEN (CASE b.IsDid WHEN '1' THEN '中标' WHEN '0' THEN '未中标' ELSE '' END) ELSE ''END) AS ISDIDStatus
|
|
FROM ICSBidInfo a
|
|
LEFT JOIN (
|
|
SELECT X.* FROM ICSBidInfoDetail X
|
|
inner JOIN (
|
|
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a
|
|
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
|
|
GROUP BY a.BDID,a.MUSERName
|
|
) Y ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID AND X.MUSERName=Y.MUSERName) b ON b.InfoID = a.ID AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSVendor c ON a.SupplierCode = c.VenCode and a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSBidDoc d ON d.BidCode = a.BidCode and d.WorkPoint=a.WorkPoint
|
|
WHERE a.BidCode='{0}' AND b.BDID='{1}'
|
|
AND b.IsCast = 1
|
|
--AND a.IsSub = 1
|
|
AND a.WorkPoint='{2}' ";
|
|
sql = string.Format(sql, BidCode,ID,WorkPoint);
|
|
return Repository().FindTablePageBySql(sql.ToString(), ref jqgridparam);
|
|
}
|
|
|
|
//public DataTable BidCourseHistoryInfo(string BidCode, string WorkPoint, string InfoID, string BDID, ref Pagination jqgridparam)
|
|
//{
|
|
// string sql = @" SELECT
|
|
// a.ID,
|
|
// b.BidCode,
|
|
// b.BidName,
|
|
// e.INVCODE,
|
|
// e.INVNAME,
|
|
// g.cVenCode,
|
|
// g.cVenName,
|
|
// CASE WHEN a.IsCast = 1 THEN '是' ELSE '否' END AS IsCasts,
|
|
// a.CastPrice,
|
|
// a.MTIME,
|
|
// a.MUSERName
|
|
// FROM ICSBidInfoLog a
|
|
// LEFT JOIN ICSBidDoc b ON a.BidCode = b.BidCode and a.WorkPoint=b.WorkPoint
|
|
// LEFT JOIN dbo.Sys_SRM_User c ON b.BidUser = c.F_Account and b.WorkPoint=c.F_Location
|
|
// LEFT JOIN ICSBidDocBD d ON a.BDID = d.ID and a.WorkPoint=d.WorkPoint
|
|
// LEFT JOIN ICSINVENTORY e ON d.InvCode = e.INVCODE and d.WorkPoint=e.WorkPoint
|
|
// LEFT JOIN ICSBidInfo f ON a.InfoID = f.ID and a.WorkPoint=f.WorkPoint
|
|
// LEFT JOIN dbo.ICSVendor g ON f.SupplierCode = g.cVenCode and f.WorkPoint=g.WorkPoint
|
|
// WHERE a.BidCode='{0}' AND a.InfoID='{1}'
|
|
// AND a.BDID='{2}' AND a.WorkPoint = '{3}' ";
|
|
// sql = string.Format(sql, BidCode, InfoID, BDID, WorkPoint);
|
|
// return Repository().FindTableBySql(sql.ToString());
|
|
//}
|
|
|
|
public DataTable BidCourseHistoryInfo(string BidCode, string WorkPoint, string InfoID, string BDID, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
string sql = @" SELECT
|
|
a.ID,
|
|
b.BidCode,
|
|
b.BidName,
|
|
e.INVCODE,
|
|
e.INVNAME,
|
|
g.VenCode cVenCode,
|
|
g.VenName cVenName,
|
|
CASE WHEN a.IsCast = 1 THEN '是' ELSE '否' END AS IsCasts,
|
|
a.CastPrice,
|
|
a.MTIME,
|
|
a.MUSERName
|
|
FROM ICSBidInfoLog a
|
|
LEFT JOIN ICSBidDoc b ON a.BidCode = b.BidCode and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.Sys_SRM_User c ON b.BidUser = c.F_Account and b.WorkPoint=c.F_Location
|
|
LEFT JOIN ICSBidDocBD d ON a.BDID = d.ID and a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN ICSINVENTORY e ON d.InvCode = e.INVCODE and d.WorkPoint=e.WorkPoint
|
|
LEFT JOIN ICSBidInfo f ON a.InfoID = f.ID and a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSVendor g ON f.SupplierCode = g.VenCode and f.WorkPoint=g.WorkPoint
|
|
WHERE a.BidCode='{0}' AND a.InfoID='{1}'
|
|
AND a.BDID='{2}' AND a.WorkPoint = '{3}' ";
|
|
sql = string.Format(sql, BidCode, InfoID, BDID, WorkPoint);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable BidResultINVENTORY(string BidCode,string WorkPoint, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.RowNo,b.INVCODE,b.INVNAME,b.INVSTD,b.INVUOM,'ERP' as Source ,a.DeliveryTime,a.Quantity from ICSBidDocBD a
|
|
left join dbo.ICSINVENTORY b on a.InvCode=b.INVCODE and a.WorkPoint=b.WorkPoint
|
|
where a.BidCode='{0}' and a.WorkPoint='{1}'";
|
|
sql = string.Format(sql, BidCode, WorkPoint);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable BidResultVendor(string BidCode, string WorkPoint, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.RowNo,a.SupplierCode,b.cVenName ,'ERP' as Source from ICSBidDocGYS a
|
|
left join dbo.ICSVendor b on a.SupplierCode=b.cVenCode and a.WorkPoint=b.WorkPoint
|
|
where a.BidCode='{0}' and a.WorkPoint='{1}'";
|
|
sql = string.Format(sql, BidCode, WorkPoint);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
public DataTable BidCourseHistoryInfoCahrs(string BidCode, string WorkPoint, string ID, string InvCode)
|
|
{
|
|
// string sql = @" SELECT
|
|
//DISTINCT
|
|
//ROW_NUMBER() OVER(PARTITION BY g.cVenCode ORDER BY a.MTIME ASC) AS num,f.SupplierCode, a.CastPrice,a.MTIME
|
|
// FROM ICSBidInfoLog a
|
|
// LEFT JOIN ICSBidDoc b ON a.BidCode = b.BidCode and a.WorkPoint=b.WorkPoint
|
|
// LEFT JOIN dbo.Sys_SRM_User c ON b.BidUser = c.F_Account and b.WorkPoint=c.F_Location
|
|
// LEFT JOIN ICSBidDocBD d ON a.BDID = d.ID and a.WorkPoint=d.WorkPoint
|
|
// LEFT JOIN ICSINVENTORY e ON d.InvCode = e.INVCODE and d.WorkPoint=e.WorkPoint
|
|
// LEFT JOIN ICSBidInfo f ON a.InfoID = f.ID and a.WorkPoint=f.WorkPoint
|
|
// LEFT JOIN dbo.ICSVendor g ON f.SupplierCode = g.cVenCode and f.WorkPoint=g.WorkPoint
|
|
// WHERE a.BidCode='{0}'
|
|
// AND a.BDID='{1}' AND a.WorkPoint = '{2}' AND d.InvCode='{3}' ORDER BY a.MTIME ASC ";
|
|
// sql = string.Format(sql, BidCode, ID, WorkPoint, InvCode);
|
|
string sql = @"SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY g.VenCode ORDER BY a.MTIME ASC) AS num,
|
|
g.VenName AS SupplierCode, a.CastPrice,a.MTIME
|
|
FROM ICSBidInfoLog a
|
|
LEFT JOIN ICSBidDoc b ON a.BidCode = b.BidCode and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.Sys_SRM_User c ON b.BidUser = c.F_Account and b.WorkPoint=c.F_Location
|
|
LEFT JOIN ICSBidDocBD d ON a.BDID = d.ID and a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN ICSINVENTORY e ON d.InvCode = e.INVCODE and d.WorkPoint=e.WorkPoint
|
|
LEFT JOIN ICSBidInfo f ON a.InfoID = f.ID and a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSVendor g ON f.SupplierCode = g.VenCode and f.WorkPoint=g.WorkPoint
|
|
WHERE a.BidCode='{0}'
|
|
AND a.BDID='{1}' AND a.WorkPoint = '{2}' AND d.InvCode='{3}' ORDER BY a.MTIME ASC ";
|
|
sql = string.Format(sql, BidCode, ID, WorkPoint, InvCode);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
public DataTable BidCourseHistoryInfoInvCode(string BidCode, string WorkPoint, string ID)
|
|
{
|
|
string sql = @" SELECT
|
|
DISTINCT
|
|
f.SupplierCode
|
|
FROM ICSBidInfoLog a
|
|
LEFT JOIN ICSBidDoc b ON a.BidCode = b.BidCode and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.Sys_SRM_User c ON b.BidUser = c.F_Account and b.WorkPoint=c.F_Location
|
|
LEFT JOIN ICSBidDocBD d ON a.BDID = d.ID and a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN ICSINVENTORY e ON d.InvCode = e.INVCODE and d.WorkPoint=e.WorkPoint
|
|
LEFT JOIN ICSBidInfo f ON a.InfoID = f.ID and a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSVendor g ON f.SupplierCode = g.cVenCode and f.WorkPoint=g.WorkPoint
|
|
WHERE a.BidCode='{0}'
|
|
AND a.BDID='{1}' AND a.WorkPoint = '{2}' ";
|
|
sql = string.Format(sql, BidCode, ID, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public DataTable ExportAll(string BidCode, string BeginDate, string EndDate, string selShow)
|
|
{
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT
|
|
a.BidCode AS 招标编号 ,
|
|
a.BidName AS 标案名称 ,
|
|
a.BidUser AS 招标负责人,
|
|
a.StarTime AS 投标开始时间 ,
|
|
a.EndTime AS 投标截止时间 ,
|
|
a.BidStatus AS 招标状态 ,
|
|
a.Remark AS 招标备注 ,
|
|
c.F_RealName AS 招标人,
|
|
a.OpenTime AS 开标日期 ,
|
|
d.F_RealName AS 决标人,
|
|
a.BidTime AS 决标时间 ,
|
|
a.WorkPoint AS 站点
|
|
FROM ICSBidDoc a
|
|
LEFT JOIN Sys_SRM_User b ON a.BidUser=b.F_Account and a.WorkPoint=b.F_Location
|
|
LEFT JOIN Sys_SRM_User c ON a.Tenders=c.F_Account and a.WorkPoint=c.F_Location
|
|
LEFT JOIN Sys_SRM_User d ON a.Bidder=d.F_Account and a.WorkPoint=d.F_Location
|
|
where 1=1 AND a.BidCode <>''";
|
|
|
|
if (!string.IsNullOrWhiteSpace(BidCode))
|
|
{
|
|
sql += " and a.BidCode like '%" + BidCode + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(BeginDate))
|
|
{
|
|
sql += " and a.StarTime >= '" + BeginDate + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(EndDate))
|
|
{
|
|
sql += " and a.StarTime <= '" + EndDate + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(selShow))
|
|
{
|
|
string selShows = selShow;
|
|
if (selShows == "1")
|
|
sql += " and BidStatus='已关闭'";
|
|
else if (selShows == "2")
|
|
sql += "and BidStatus='已决标'";
|
|
else if (selShows == "3")
|
|
sql += "and BidStatus='招标中'";
|
|
else if (selShows == "4")
|
|
sql += "and BidStatus='已结束'";
|
|
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and b.F_Account in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
}
|
|
}
|