public List <StocktakeDetailModel> GetByStocktakeId(string StocktakeId)
{
List <StocktakeDetailModel> list = new List <StocktakeDetailModel>();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT DetailId,StocktakeId,ResourceCode,ResourceName,Specification,Unit,Price,SupplierId,Supplier,LastMonthNum,StorageNum,");
builder.Append("FirstStorageNum,OutReserveNum,TransferringInNum,TransferringOutNum,WastageNum,RefundingNum,BookNum,StocktakeNum,InputDate,Note");
builder.Append(" FROM Sm_Stocktake_Detail WHERE StocktakeId=@StocktakeId ORDER BY ResourceCode");
SqlParameter parameter = new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500)
{
Value = StocktakeId
};
using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), new SqlParameter[] { parameter }))
{
while (reader.Read())
{
StocktakeDetailModel item = new StocktakeDetailModel {
Id = reader["DetailId"].ToString(),
StocktakeId = reader["StocktakeId"].ToString(),
ResourceCode = reader["ResourceCode"].ToString(),
ResourceName = reader["ResourceName"].ToString(),
Specification = reader["Specification"].ToString(),
Unit = reader["Unit"].ToString(),
Price = Convert.ToDecimal(reader["Price"]),
SupplierId = reader["SupplierId"].ToString(),
Supplier = reader["Supplier"].ToString(),
LastMonthNum = Convert.ToDecimal(reader["LastMonthNum"]),
StorageNum = Convert.ToDecimal(reader["StorageNum"]),
FirstStorageNum = Convert.ToDecimal(reader["FirstStorageNum"]),
OutReserveNum = Convert.ToDecimal(reader["OutReserveNum"]),
TransferringInNum = Convert.ToDecimal(reader["TransferringInNum"]),
TransferringOutNum = Convert.ToDecimal(reader["TransferringOutNum"]),
RefundingNum = Convert.ToDecimal(reader["RefundingNum"]),
BookNum = Convert.ToDecimal(reader["BookNum"]),
StocktakeNum = Convert.ToDecimal(reader["StocktakeNum"]),
WastageNum = Convert.ToDecimal(reader["WastageNum"]),
InputDate = Convert.ToDateTime(reader["InputDate"]),
Note = reader["Note"].ToString()
};
list.Add(item);
}
}
return(list);
}
public List <StocktakeDetailModel> GetByTreasuryCode(string treasuryCode, bool isFirst, DateTime endTime)
{
DateTime now = DateTime.Now;
string id = "";
Stocktake stocktake = new Stocktake();
StocktakeModel lastStocktakeModel = new StocktakeModel();
if (!isFirst)
{
lastStocktakeModel = stocktake.GetLastStocktakeModel(treasuryCode);
now = lastStocktakeModel.EndDate;
id = lastStocktakeModel.Id;
}
List <StocktakeDetailModel> list = new List <StocktakeDetailModel>();
StringBuilder builder = new StringBuilder();
builder.Append(" WITH SCTE AS --库存物资").AppendLine();
builder.Append("(").AppendLine();
builder.Append(" SELECT scode, sprice, corp, SUM(snumber) AS num ").AppendLine();
builder.Append(" FROM Sm_Treasury_Stock").AppendLine();
builder.Append(" WHERE tcode =@treasuryCode AND intime < @EndTime").AppendLine();
builder.Append(" GROUP BY scode, sprice, corp").AppendLine();
builder.Append(" ), SSCTE AS -- 入库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT ST.scode, ST.sprice, ST.corp, SUM(ST.number) AS num").AppendLine();
builder.Append(" FROM Sm_Storage_Stock AS ST").AppendLine();
builder.Append(" INNER JOIN Sm_Storage AS S ON S.scode = ST.stcode").AppendLine();
builder.Append(" WHERE tcode =@treasuryCode ").AppendLine();
builder.Append(" AND ST.number != 0 AND IsFirst=0 ").AppendLine();
if (!isFirst)
{
builder.Append(" AND intime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND intime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY ST.scode, ST.sprice, ST.corp").AppendLine();
builder.Append(" ), SOCTE AS --出库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT OS.scode, OS.sprice, OS.corp, SUM(OS.number) AS num").AppendLine();
builder.Append(" FROM Sm_out_Stock AS OS").AppendLine();
builder.Append(" INNER JOIN Sm_OutReserve AS O ON O.orcode = OS.orcode").AppendLine();
builder.Append(" WHERE tcode =@treasuryCode ").AppendLine();
if (!isFirst)
{
builder.Append(" AND IsOutTime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND IsOutTime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY OS.scode, OS.sprice, OS.corp").AppendLine();
builder.Append(" ),FirstStorageInfo AS --甲供入库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT ST.scode, ST.sprice, ST.corp, SUM(ST.number) AS num").AppendLine();
builder.Append(" FROM Sm_Storage_Stock AS ST").AppendLine();
builder.Append(" INNER JOIN Sm_Storage AS S ON S.scode = ST.stcode").AppendLine();
builder.Append(" WHERE tcode =@treasuryCode ").AppendLine();
builder.Append(" AND ST.number != 0 AND IsFirst=1 ").AppendLine();
if (!isFirst)
{
builder.Append(" AND intime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND intime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY ST.scode, ST.sprice, ST.corp").AppendLine();
builder.Append(" ),TransferringInInfo AS --调拨入库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT SAS.scode, SAS.sprice, SAS.corp, SUM(SAS.number) AS num").AppendLine();
builder.Append(" FROM Sm_Allocation_Stock AS SAS").AppendLine();
builder.Append(" INNER JOIN Sm_Allocation AS SA ON SA.Acode = SAS.Acode").AppendLine();
builder.Append(" WHERE TcodeB =@treasuryCode AND Isinb='1'").AppendLine();
if (!isFirst)
{
builder.Append(" AND Isintime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND Isintime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY SAS.scode, SAS.sprice, SAS.corp").AppendLine();
builder.Append(" ),TransferringOutInfo AS --调拨出库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT SAS.scode, SAS.sprice, SAS.corp, SUM(SAS.number) AS num").AppendLine();
builder.Append(" FROM Sm_Allocation_Stock AS SAS").AppendLine();
builder.Append(" INNER JOIN Sm_Allocation AS SA ON SA.Acode = SAS.Acode").AppendLine();
builder.Append(" WHERE TcodeA =@treasuryCode AND IsOutA='1'").AppendLine();
if (!isFirst)
{
builder.Append(" AND Isouttime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND Isouttime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY SAS.scode, SAS.sprice, SAS.corp").AppendLine();
builder.Append(" ),WastageInfo AS --报损出库").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT BS.ResourceCode scode, BS.sprice, BS.corp, SUM(BS.number) AS num").AppendLine();
builder.Append(" FROM Sm_Wastage_Stock AS BS ").AppendLine();
builder.Append(" INNER JOIN Sm_Wastage AS R ON R.WastageCode = BS.WastageCode ").AppendLine();
builder.Append(" WHERE TreasuryCode =@treasuryCode AND IsOut='1'").AppendLine();
if (!isFirst)
{
builder.Append(" AND Isouttime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND Isouttime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY BS.ResourceCode, BS.sprice, BS.corp").AppendLine();
builder.Append(" ),RefundingInfo AS --退库数量").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT BS.scode, BS.sprice, BS.corp, SUM(BS.number) AS num").AppendLine();
builder.Append(" FROM Sm_back_Stock AS BS").AppendLine();
builder.Append(" INNER JOIN Sm_Refunding AS R ON R.Rcode = BS.Rcode").AppendLine();
builder.Append(" WHERE Tcode =@treasuryCode ").AppendLine();
if (!isFirst)
{
builder.Append(" AND Isintime BETWEEN @BeginTime AND @EndTime").AppendLine();
}
else
{
builder.Append(" AND Isintime < @EndTime").AppendLine();
}
builder.Append(" GROUP BY BS.scode, BS.sprice, BS.corp").AppendLine();
builder.Append(" )").AppendLine();
builder.Append(" ,InitInfo AS --初始化").AppendLine();
builder.Append(" (").AppendLine();
if (!isFirst)
{
builder.Append(" SELECT ResourceCode scode,Price sprice,SupplierId corp,StocktakeNum num FROM Sm_Stocktake_Detail WHERE StocktakeId=@StocktakeId").AppendLine();
}
else
{
builder.Append(" SELECT scode, sprice, corp, SUM(snumber) AS num ").AppendLine();
builder.Append(" FROM Sm_Treasury_Stock").AppendLine();
builder.Append(" WHERE tcode =@treasuryCode and type='I'").AppendLine();
builder.Append(" GROUP BY scode, sprice, corp").AppendLine();
}
builder.Append(" )").AppendLine();
builder.Append(" SELECT Scode,ResourceName,Specification,UnitName,Brand,ModelNumber,TechnicalParameter,Sprice,Corp,CorpName,LastMonthNum,StorageNum,").AppendLine();
builder.Append(" OutReserveNum,FirstStorageNum,TransferringInNum,TransferringOutNum,WastageNum,RefundingNum,").AppendLine();
builder.Append(" (LastMonthNum+StorageNum-OutReserveNum+FirstStorageNum+TransferringInNum-TransferringOutNum-WastageNum+RefundingNum) ").AppendLine();
builder.Append(" AS BookNum,").AppendLine();
builder.Append(" (LastMonthNum+StorageNum-OutReserveNum+FirstStorageNum+TransferringInNum-TransferringOutNum-WastageNum+RefundingNum)").AppendLine();
builder.Append(" AS StocktakeNum,'' AS Note").AppendLine();
builder.Append(" FROM (").AppendLine();
builder.Append(" SELECT S.Scode,ResourceName,Specification,[Name] AS UnitName, ").AppendLine();
builder.Append(" ISNULL(Res_Resource.Brand,'') Brand, ISNULL(ModelNumber,'') ModelNumber, ISNULL(TechnicalParameter,'') TechnicalParameter, ").AppendLine();
builder.Append(" S.Sprice,S.Corp,CorpName,ISNULL(SSCTE.num,0.000) AS StorageNum, ").AppendLine();
builder.Append(" ISNULL(SOCTE.num,0.000) AS OutReserveNum,ISNULL(FirstStorageInfo.num,0.000) AS FirstStorageNum,").AppendLine();
builder.Append(" ISNULL(TransferringInInfo.num,0.000) AS TransferringInNum,").AppendLine();
builder.Append(" ISNULL(TransferringOutInfo.num,0.000) AS TransferringOutNum,ISNULL(RefundingInfo.num,0.000) AS RefundingNum,").AppendLine();
builder.Append(" ISNULL(WastageInfo.num,0.000) AS WastageNum,").AppendLine();
builder.Append(" ISNULL(InitInfo.num,0.000) AS LastMonthNum").AppendLine();
builder.Append(" FROM").AppendLine();
builder.Append(" (").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM SCTE").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM SSCTE").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM SOCTE").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM FirstStorageInfo").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM TransferringInInfo").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM TransferringOutInfo").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM WastageInfo").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM RefundingInfo").AppendLine();
builder.Append(" UNION ").AppendLine();
builder.Append(" SELECT scode, sprice, corp FROM InitInfo").AppendLine();
builder.Append(" ) AS S").AppendLine();
builder.Append(" LEFT JOIN SCTE ON S.scode = SCTE.scode AND S.sprice=SCTE.sprice AND S.corp=SCTE.corp").AppendLine();
builder.Append(" LEFT JOIN SSCTE ON S.scode = SSCTE.scode AND S.sprice=SSCTE.sprice AND S.corp=SSCTE.corp").AppendLine();
builder.Append(" LEFT JOIN SOCTE ON S.scode = SOCTE.scode AND S.sprice=SOCTE.sprice AND S.corp=SOCTE.corp").AppendLine();
builder.Append(" LEFT JOIN FirstStorageInfo ON S.scode = FirstStorageInfo.scode AND S.sprice=FirstStorageInfo.sprice AND S.corp=FirstStorageInfo.corp").AppendLine();
builder.Append(" LEFT JOIN TransferringInInfo ON S.scode = TransferringInInfo.scode AND S.sprice=TransferringInInfo.sprice AND S.corp=TransferringInInfo.corp").AppendLine();
builder.Append(" LEFT JOIN TransferringOutInfo ON S.scode = TransferringOutInfo.scode AND S.sprice=TransferringOutInfo.sprice AND S.corp=TransferringOutInfo.corp").AppendLine();
builder.Append(" LEFT JOIN WastageInfo ON S.scode = WastageInfo.scode AND S.sprice=WastageInfo.sprice AND S.corp=WastageInfo.corp").AppendLine();
builder.Append(" LEFT JOIN RefundingInfo ON S.scode = RefundingInfo.scode AND S.sprice=RefundingInfo.sprice AND S.corp=RefundingInfo.corp").AppendLine();
builder.Append(" LEFT JOIN InitInfo ON S.scode = InitInfo.scode AND S.sprice=InitInfo.sprice AND S.corp=InitInfo.corp").AppendLine();
builder.Append(" LEFT JOIN Res_Resource ON S.Scode=ResourceCode LEFT JOIN ").AppendLine();
builder.Append(" XPM_Basic_ContactCorp ON S.Corp=CorpId left join Res_Unit ON Unit=UnitId ) Tab").AppendLine();
builder.Append(" ORDER BY SCODE").AppendLine();
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@treasuryCode", SqlDbType.NVarChar, 0x200), new SqlParameter("@BeginTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500) };
commandParameters[0].Value = treasuryCode;
commandParameters[1].Value = now.AddDays(1.0);
commandParameters[2].Value = endTime.AddDays(1.0);
commandParameters[3].Value = id;
using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), commandParameters))
{
while (reader.Read())
{
StocktakeDetailModel item = new StocktakeDetailModel {
Id = "",
StocktakeId = "",
ResourceCode = reader["Scode"].ToString(),
ResourceName = reader["ResourceName"].ToString(),
Specification = reader["Specification"].ToString(),
Unit = reader["UnitName"].ToString(),
Price = Convert.ToDecimal(reader["Sprice"]),
SupplierId = reader["Corp"].ToString(),
Supplier = reader["CorpName"].ToString(),
LastMonthNum = Convert.ToDecimal(reader["LastMonthNum"]),
StorageNum = Convert.ToDecimal(reader["StorageNum"]),
FirstStorageNum = Convert.ToDecimal(reader["FirstStorageNum"]),
OutReserveNum = Convert.ToDecimal(reader["OutReserveNum"]),
TransferringInNum = Convert.ToDecimal(reader["TransferringInNum"]),
TransferringOutNum = Convert.ToDecimal(reader["TransferringOutNum"]),
RefundingNum = Convert.ToDecimal(reader["RefundingNum"]),
BookNum = Convert.ToDecimal(reader["BookNum"]),
StocktakeNum = Convert.ToDecimal(reader["StocktakeNum"]),
WastageNum = Convert.ToDecimal(reader["WastageNum"]),
Note = reader["Note"].ToString()
};
list.Add(item);
}
}
return(list);
}