Example #1
0
        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);
        }
Example #2
0
        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);
        }