public int Add(SqlTransaction trans, StocktakeModel model) { StringBuilder builder = new StringBuilder(); builder.Append("INSERT INTO Sm_Stocktake "); builder.Append("(StocktakeId,StocktakeCode,StocktakeName,TreasuryCode,StocktakeDate,InputUser,InputDate,BeginDate,EndDate,State,Note,FlowState) "); builder.Append("values (@StocktakeId,@StocktakeCode,@StocktakeName,@TreasuryCode,@StocktakeDate,@InputUser,@InputDate,@BeginDate,@EndDate,"); builder.Append("@State,@Note,@FlowState)"); SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500), new SqlParameter("@StocktakeCode", SqlDbType.NVarChar, 500), new SqlParameter("@StocktakeName", SqlDbType.NVarChar, 500), new SqlParameter("@TreasuryCode", SqlDbType.NVarChar, 0x200), new SqlParameter("@StocktakeDate", SqlDbType.NVarChar, 100), new SqlParameter("@InputUser", SqlDbType.NVarChar, 500), new SqlParameter("@InputDate", SqlDbType.DateTime), new SqlParameter("@BeginDate", SqlDbType.DateTime), new SqlParameter("@EndDate", SqlDbType.DateTime), new SqlParameter("@State", SqlDbType.Int, 4), new SqlParameter("@Note", SqlDbType.Text), new SqlParameter("@FlowState", SqlDbType.Int) }; commandParameters[0].Value = model.Id; commandParameters[1].Value = model.Code; commandParameters[2].Value = model.Name; commandParameters[3].Value = model.TreasuryCode; commandParameters[4].Value = model.StocktakeDate; commandParameters[5].Value = model.InputUser; commandParameters[6].Value = model.InputDate; commandParameters[7].Value = model.BeginDate; commandParameters[8].Value = model.EndDate; commandParameters[9].Value = model.State; commandParameters[10].Value = model.Note; commandParameters[11].Value = model.FlowState; if (trans == null) { return(SqlHelper.ExecuteNonQuery(CommandType.Text, builder.ToString(), commandParameters)); } return(SqlHelper.ExecuteNonQuery(trans, CommandType.Text, builder.ToString(), commandParameters)); }
protected void Page_Load(object sender, EventArgs e) { if (!base.IsPostBack) { StocktakeModel stocktakeModel = new StocktakeModel(); stocktakeModel = this.StocktakeBll.GetById(this.stocktakeId); this.lblCode.Text = stocktakeModel.Code; this.lblName.Text = stocktakeModel.Name; string stocktakeDate = stocktakeModel.StocktakeDate; string str = stocktakeDate.Substring(0, 4); string str2 = stocktakeDate.Substring(4, 2); this.lblStocktakeDate.Text = str + "年" + str2 + "月"; this.lblInputDate.Text = stocktakeModel.InputDate.ToString("yyyy-MM-dd HH:mm:ss"); this.lblPerson.Text = stocktakeModel.InputUser; this.lblEndTime.Text = stocktakeModel.EndDate.ToString("yyyy-MM-dd"); this.lblTName.Text = stocktakeModel.TreasuryName; this.lblExplain.Text = stocktakeModel.Note; if (!string.IsNullOrEmpty(stocktakeModel.BeginDate.ToString())) { this.lblStateTime.Text = stocktakeModel.BeginDate.ToString("yyyy-MM-dd"); } List <StocktakeDetailModel> dataSource = new List <StocktakeDetailModel>(); dataSource = this.stocktakeDetailBll.GetByStocktakeId(this.stocktakeId); this.gvwStocktake.DataSource = dataSource; this.gvwStocktake.DataBind(); } }
public StocktakeModel GetEditModel(string treasuryCode) { string cmdText = "\r\n\t\t\t\tSELECT StocktakeId,StocktakeCode,StocktakeName,TreasuryCode,\r\n\t\t\t\t\tStocktakeDate,InputUser,InputDate,BeginDate,EndDate,LockDate,State,Note,FlowState \r\n\t\t\t\tFROM dbo.Sm_Stocktake WHERE TreasuryCode=@treasuryCode AND (FlowState='-1' OR FlowState='0')\r\n\t\t\t"; SqlParameter parameter = new SqlParameter("@treasuryCode", SqlDbType.NVarChar, 500) { Value = treasuryCode }; StocktakeModel model = null; using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, cmdText, new SqlParameter[] { parameter })) { if (reader.Read()) { model = new StocktakeModel { Id = reader["stocktakeId"].ToString(), Code = reader["StocktakeCode"].ToString(), Name = reader["StocktakeName"].ToString(), TreasuryCode = reader["TreasuryCode"].ToString(), StocktakeDate = reader["StocktakeDate"].ToString(), InputUser = reader["InputUser"].ToString(), InputDate = Convert.ToDateTime(reader["InputDate"].ToString()), BeginDate = Convert.ToDateTime(reader["BeginDate"].ToString()), EndDate = Convert.ToDateTime(reader["EndDate"].ToString()), State = Convert.ToInt32(reader["State"]), FlowState = Convert.ToInt32(reader["FlowState"]), Note = reader["Note"].ToString() }; } } return(model); }
protected void btnOverrule_Click(object sender, EventArgs e) { StocktakeModel editModel = this.stocktakeBll.GetEditModel(this.tvTreasury.SelectedValue); editModel.State = 0; this.stocktakeBll.Update(null, editModel); base.RegisterScript("alert('系统提示:\\n\\n驳回成功!');"); this.BindGridView(); }
public int LockStocktake(SqlTransaction trans, StocktakeModel model) { StringBuilder builder = new StringBuilder(); builder.Append("UPDATE Sm_Stocktake SET LockDate=@LockDate,State=@State WHERE StocktakeId=@StocktakeId "); SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500), new SqlParameter("@LockDate", SqlDbType.DateTime), new SqlParameter("@State", SqlDbType.Int, 4) }; commandParameters[0].Value = model.Id; commandParameters[1].Value = model.LockDate; commandParameters[2].Value = model.State; return(SqlHelper.ExecuteNonQuery(trans, CommandType.Text, builder.ToString(), commandParameters)); }
private void UpdateStocktake(SqlTransaction trans, int state) { StocktakeModel byId = this.stocktakeBll.GetById(this.hfldStocktakeId.Value); byId.Name = this.txtName.Text.Trim(); byId.StocktakeDate = this.ddlYear.SelectedValue + this.ddlMonth.SelectedValue; byId.Note = this.txtExplain.Text.Trim(); byId.State = state; byId.LockDate = byId.LockDate; this.stocktakeBll.Update(trans, byId); }
public List <StocktakeModel> GetByTreasuryCode(string treasuryCode) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT StocktakeId,StocktakeCode,StocktakeName,TreasuryCode,Tname,StocktakeDate,InputUser,InputDate,BeginDate,EndDate,LockDate,State,FlowState, "); builder.Append("Note FROM Sm_Stocktake LEFT JOIN dbo.Sm_Treasury ON TreasuryCode=Tcode WHERE TreasuryCode=@TreasuryCode ORDER BY InputDate DESC"); SqlParameter parameter = new SqlParameter("@TreasuryCode", SqlDbType.NVarChar, 500) { Value = treasuryCode }; List <StocktakeModel> list = new List <StocktakeModel>(); using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), new SqlParameter[] { parameter })) { while (reader.Read()) { StocktakeModel item = new StocktakeModel { Id = reader["StocktakeId"].ToString(), Code = reader["StocktakeCode"].ToString(), Name = reader["StocktakeName"].ToString(), TreasuryCode = reader["TreasuryCode"].ToString(), TreasuryName = reader["Tname"].ToString(), StocktakeDate = reader["StocktakeDate"].ToString(), InputUser = reader["InputUser"].ToString(), InputDate = DBHelper.GetDateTime(reader["InputDate"]), BeginDate = DBHelper.GetDateTime(reader["BeginDate"]), EndDate = DBHelper.GetDateTime(reader["EndDate"]), FlowState = DBHelper.GetInt(reader["FlowState"]) }; if (reader["LockDate"].ToString() != "") { item.LockDate = DBHelper.GetDateTime(reader["LockDate"]); } item.State = DBHelper.GetInt(reader["State"]); if (item.State == 0) { item.StateName = "挂起"; } else if (item.State == 1) { item.StateName = "未锁定"; } else if (item.State == 2) { item.StateName = "<span style='color:#008B45;' state=2>已锁定</span>"; } item.Note = reader["Note"].ToString(); list.Add(item); } } return(list); }
protected void BindGridView() { this.gvwStocktake.DataSource = this.stocktakeBll.GetByTreasuryCode(this.tvTreasury.SelectedValue); this.gvwStocktake.DataBind(); this.hfldIsFirst.Value = this.stocktakeBll.IsFirst(this.tvTreasury.SelectedValue).ToString(); this.hfldIsAdd.Value = this.stocktakeBll.IsAdd(this.tvTreasury.SelectedValue).ToString(); StocktakeModel editModel = this.stocktakeBll.GetEditModel(this.tvTreasury.SelectedValue); if (editModel != null) { if (editModel.State == 1) { this.btnLock.Attributes.Remove("disabled"); this.btnOverrule.Attributes.Remove("disabled"); } else { this.btnLock.Attributes.Add("disabled", "disabled"); this.btnOverrule.Attributes.Add("disabled", "disabled"); } this.hfldState.Value = editModel.FlowState.ToString(); } else { this.btnLock.Attributes.Add("disabled", "disabled"); this.btnOverrule.Attributes.Add("disabled", "disabled"); } StocktakeModel lastStocktakeModel = this.stocktakeBll.GetLastStocktakeModel(this.tvTreasury.SelectedValue); if (lastStocktakeModel == null) { this.hfldLastStocktakeDate.Value = "false"; return; } string stocktakeDate = lastStocktakeModel.StocktakeDate; if (!(stocktakeDate != "")) { this.hfldLastStocktakeDate.Value = "false"; return; } DateTime.Now.ToString("yyyyMM"); if (DateTime.Now.ToString("yyyyMM") == stocktakeDate) { this.hfldLastStocktakeDate.Value = "true"; return; } this.hfldLastStocktakeDate.Value = "false"; }
public StocktakeModel GetById(string stocktakeId) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT StocktakeId,StocktakeCode,StocktakeName,TreasuryCode,Tname,StocktakeDate,InputUser,InputDate,BeginDate,EndDate,LockDate,State,Note "); builder.Append("FROM Sm_Stocktake LEFT JOIN dbo.Sm_Treasury ON TreasuryCode=Tcode WHERE StocktakeId=@StocktakeId "); SqlParameter parameter = new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500) { Value = stocktakeId }; StocktakeModel model = new StocktakeModel(); using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), new SqlParameter[] { parameter })) { if (!reader.Read()) { return(model); } model.Id = stocktakeId; model.Code = reader["StocktakeCode"].ToString(); model.Name = reader["StocktakeName"].ToString(); model.TreasuryCode = reader["TreasuryCode"].ToString(); model.TreasuryName = reader["Tname"].ToString(); model.StocktakeDate = reader["StocktakeDate"].ToString(); model.InputUser = reader["InputUser"].ToString(); model.InputDate = Convert.ToDateTime(reader["InputDate"].ToString()); model.BeginDate = Convert.ToDateTime(reader["BeginDate"].ToString()); model.EndDate = Convert.ToDateTime(reader["EndDate"].ToString()); if (reader["LockDate"].ToString() != "") { model.LockDate = Convert.ToDateTime(reader["LockDate"].ToString()); } model.State = Convert.ToInt32(reader["State"]); if (Convert.ToInt32(reader["State"]) == 0) { model.StateName = "挂起"; } else if (Convert.ToInt32(reader["State"]) == 1) { model.StateName = "未锁定"; } else if (Convert.ToInt32(reader["State"]) == 2) { model.StateName = "已锁定"; } model.Note = reader["Note"].ToString(); } return(model); }
private void AddStocktake(SqlTransaction trans, int state) { StocktakeModel stocktakeModel = new StocktakeModel(); stocktakeModel.Id = this.hfldStocktakeId.Value; stocktakeModel.Code = this.txtCode.Text.Trim(); stocktakeModel.Name = this.txtName.Text.Trim(); stocktakeModel.TreasuryCode = this.tCode; stocktakeModel.StocktakeDate = this.ddlYear.SelectedValue + this.ddlMonth.SelectedValue; stocktakeModel.InputUser = this.txtPerson.Text.Trim(); stocktakeModel.BeginDate = Convert.ToDateTime(this.txtBeginDate.Text.Trim()); stocktakeModel.InputDate = Convert.ToDateTime(this.DateInTime.Text.Trim()); stocktakeModel.EndDate = Convert.ToDateTime(this.txtEndDate.Text.Trim()); stocktakeModel.State = state; stocktakeModel.FlowState = -1; stocktakeModel.Note = this.txtExplain.Text.Trim(); this.stocktakeBll.Add(trans, stocktakeModel); }
public int UpdateState(SqlTransaction trans, StocktakeModel model) { StringBuilder builder = new StringBuilder(); builder.Append("UPDATE Sm_Stocktake SET StocktakeName=@StocktakeName,StocktakeDate=@StocktakeDate,EndDate=@EndDate,State=@State, "); builder.Append("Note=@Note WHERE StocktakeId=@StocktakeId "); SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@StocktakeId", SqlDbType.NVarChar, 500), new SqlParameter("@StocktakeName", SqlDbType.NVarChar, 500), new SqlParameter("@StocktakeDate", SqlDbType.NVarChar, 100), new SqlParameter("@EndDate", SqlDbType.DateTime), new SqlParameter("@State", SqlDbType.Int, 4), new SqlParameter("@Note", SqlDbType.Text) }; commandParameters[0].Value = model.Id; commandParameters[1].Value = model.Name; commandParameters[2].Value = model.StocktakeDate; commandParameters[3].Value = model.EndDate; commandParameters[4].Value = model.State; commandParameters[5].Value = model.Note; if (trans == null) { return(SqlHelper.ExecuteNonQuery(CommandType.Text, builder.ToString(), commandParameters)); } return(SqlHelper.ExecuteNonQuery(trans, CommandType.Text, builder.ToString(), commandParameters)); }
public StocktakeModel GetLastStocktakeModel(string treasuryCode) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT TOP 1 StocktakeId,StocktakeCode,StocktakeName,TreasuryCode,StocktakeDate,InputUser,InputDate,BeginDate,EndDate,LockDate,State,Note "); builder.Append("FROM Sm_Stocktake WHERE TreasuryCode=@treasuryCode ORDER BY LockDate DESC"); SqlParameter parameter = new SqlParameter("@treasuryCode", SqlDbType.NVarChar, 500) { Value = treasuryCode }; StocktakeModel model = new StocktakeModel(); using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), new SqlParameter[] { parameter })) { if (!reader.Read()) { return(model); } model.Id = reader["StocktakeId"].ToString(); model.Code = reader["StocktakeCode"].ToString(); model.Name = reader["StocktakeName"].ToString(); model.TreasuryCode = reader["TreasuryCode"].ToString(); model.StocktakeDate = reader["StocktakeDate"].ToString(); model.InputUser = reader["InputUser"].ToString(); model.InputDate = Convert.ToDateTime(reader["InputDate"].ToString()); model.BeginDate = Convert.ToDateTime(reader["BeginDate"].ToString()); model.EndDate = Convert.ToDateTime(reader["EndDate"].ToString()); if (reader["LockDate"].ToString() != "") { model.LockDate = Convert.ToDateTime(reader["LockDate"].ToString()); } model.State = Convert.ToInt32(reader["State"]); model.Note = reader["Note"].ToString(); } return(model); }
public void CommitEvent(object key) { StocktakeModel byId = this.stocktakeBll.GetById(key.ToString()); List <StocktakeDetailModel> byStocktakeId = this.stocktakeDetailBll.GetByStocktakeId(byId.Id); using (SqlConnection connection = new SqlConnection(SqlHelper.ConnectionString)) { connection.Open(); SqlTransaction trans = connection.BeginTransaction(); foreach (StocktakeDetailModel model2 in byStocktakeId) { decimal num = model2.StocktakeNum - model2.BookNum; if (num < 0M) { num = model2.BookNum - model2.StocktakeNum; List <TreasuryStockModel> listArray = this.treasuryStockBll.GetListArray(string.Concat(new object[] { " where scode='", model2.ResourceCode, "' and sprice=", model2.Price, " and corp='", model2.SupplierId, "' and tcode='", byId.TreasuryCode, "' order by intime asc" })); decimal num2 = 0M; foreach (TreasuryStockModel model3 in listArray) { num2 += model3.snumber; } foreach (TreasuryStockModel model4 in listArray) { if (model4.snumber >= num) { model4.snumber -= num; if (model4.snumber == 0M) { this.stocktakeDetailBll.Delete(trans, model4.tsid); } else { this.stocktakeDetailBll.Update(trans, model4); } break; } if (model4.snumber < num) { num -= model4.snumber; this.stocktakeDetailBll.Delete(trans, model4.tsid); } } } else if (num > 0M) { TreasuryStockModel model = new TreasuryStockModel { tsid = Guid.NewGuid().ToString(), scode = model2.ResourceCode, tcode = byId.TreasuryCode, sprice = model2.Price, snumber = num, isfirst = false, corp = model2.SupplierId, incode = byId.Code, intime = DateTime.Today, intype = 0 }; this.stocktakeDetailBll.AddTreasuryStock(trans, model); } } byId.LockDate = DateTime.Now; byId.State = 2; this.stocktakeBll.LockStocktake(trans, byId); trans.Commit(); } }
public void LockStocktake(SqlTransaction trans, StocktakeModel model) { this.stocktake.LockStocktake(trans, model); }
public void Update(SqlTransaction trans, StocktakeModel model) { this.stocktake.UpdateState(trans, model); }
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); }
protected void btnLock_Click(object sender, EventArgs e) { StocktakeModel editModel = this.stocktakeBll.GetEditModel(this.tvTreasury.SelectedValue); List <StocktakeDetailModel> byStocktakeId = this.stocktakeDetailBll.GetByStocktakeId(editModel.Id); using (SqlConnection sqlConnection = new SqlConnection(SqlHelper.ConnectionString)) { sqlConnection.Open(); SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(); try { foreach (StocktakeDetailModel current in byStocktakeId) { decimal num = current.StocktakeNum - current.BookNum; if (num < 0m) { num = current.BookNum - current.StocktakeNum; List <TreasuryStockModel> listArray = this.treasuryStockBll.GetListArray(string.Concat(new object[] { " where scode='", current.ResourceCode, "' and sprice=", current.Price, " and corp='", current.SupplierId, "' and tcode='", editModel.TreasuryCode, "' order by intime asc" })); decimal d = 0m; foreach (TreasuryStockModel current2 in listArray) { d += current2.snumber; } using (List <TreasuryStockModel> .Enumerator enumerator3 = listArray.GetEnumerator()) { while (enumerator3.MoveNext()) { TreasuryStockModel current3 = enumerator3.Current; if (current3.snumber >= num) { current3.snumber -= num; if (current3.snumber == 0m) { this.stocktakeDetailBll.Delete(sqlTransaction, current3.tsid); break; } this.stocktakeDetailBll.Update(sqlTransaction, current3); break; } else { if (current3.snumber < num) { num -= current3.snumber; this.stocktakeDetailBll.Delete(sqlTransaction, current3.tsid); } } } continue; } } if (num > 0m) { TreasuryStockModel treasuryStockModel = new TreasuryStockModel(); treasuryStockModel.tsid = Guid.NewGuid().ToString(); treasuryStockModel.scode = current.ResourceCode; treasuryStockModel.tcode = this.tvTreasury.SelectedValue; treasuryStockModel.sprice = current.Price; treasuryStockModel.snumber = num; treasuryStockModel.isfirst = false; treasuryStockModel.corp = current.SupplierId; treasuryStockModel.incode = editModel.Code; treasuryStockModel.intime = DateTime.Today; treasuryStockModel.intype = 0; this.stocktakeDetailBll.AddTreasuryStock(sqlTransaction, treasuryStockModel); } } editModel.LockDate = DateTime.Now; editModel.State = 2; this.stocktakeBll.LockStocktake(sqlTransaction, editModel); sqlTransaction.Commit(); base.RegisterScript("alert('系统提示:\\n\\n锁定成功!');"); base.RegisterScript("location='StocktakeList.aspx?tCode=" + this.tvTreasury.SelectedValue + "';"); } catch (Exception) { sqlTransaction.Rollback(); throw; } } }
protected void Page_Load(object sender, EventArgs e) { if (!base.IsPostBack) { if (this.ddlYear != null) { this.AddItem(this.ddlYear, "year"); } if (this.ddlMonth != null) { this.AddItem(this.ddlMonth, "month"); } if (this.action.Equals("Add")) { this.hfldStocktakeId.Value = Guid.NewGuid().ToString(); this.txtCode.Text = DateTime.Now.ToString("yyyyMMddHHmmss"); this.DateInTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); this.txtPerson.Text = PageHelper.QueryUser(this, base.UserCode); this.txtTName.Text = this.tName; this.txtEndDate.Text = this.endDate; if (this.isFirst == "True") { this.ddlYear.SelectedIndex = this.ddlYear.Items.Count - 1; this.ddlMonth.SelectedValue = DateTime.Now.Month.ToString("00"); DateTime initializeDate = this.stocktakeBll.GetInitializeDate(this.tCode); if (initializeDate.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd")) { this.txtBeginDate.Text = initializeDate.ToString("yyyy-MM-dd"); } else { string text = this.stocktakeBll.GetStorageDate(this.tCode).ToString("yyyy-MM-dd"); string text2 = this.stocktakeBll.GetAllocationDate(this.tCode).ToString("yyyy-MM-dd"); if (text != DateTime.Now.ToString("yyyy-MM-dd") && text2 != DateTime.Now.ToString("yyyy-MM-dd") && Convert.ToDateTime(text).CompareTo(Convert.ToDateTime(text2)) < 0) { this.txtBeginDate.Text = text; } else { if (text != DateTime.Now.ToString("yyyy-MM-dd") && text2 != DateTime.Now.ToString("yyyy-MM-dd") && Convert.ToDateTime(text).CompareTo(Convert.ToDateTime(text2)) > 0) { this.txtBeginDate.Text = text2; } else { if (text != DateTime.Now.ToString("yyyy-MM-dd") && text2 == DateTime.Now.ToString("yyyy-MM-dd")) { this.txtBeginDate.Text = text; } else { if (text == DateTime.Now.ToString("yyyy-MM-dd") && text2 != DateTime.Now.ToString("yyyy-MM-dd")) { this.txtBeginDate.Text = text2; } else { this.txtBeginDate.Text = DateTime.Now.ToString("yyyy-MM-dd"); } } } } } } else { StocktakeModel lastStocktakeModel = this.stocktakeBll.GetLastStocktakeModel(this.tCode); string stocktakeDate = lastStocktakeModel.StocktakeDate; string str = stocktakeDate.Substring(0, 4); string str2 = stocktakeDate.Substring(4, 2); string value = str + "-" + str2; string text3 = Convert.ToDateTime(value).AddMonths(1).ToString("yyyyMM"); string selectedValue = text3.Substring(0, 4); string selectedValue2 = text3.Substring(4, 2); this.ddlYear.SelectedValue = selectedValue; this.ddlMonth.SelectedValue = selectedValue2; DateTime dateTime = lastStocktakeModel.BeginDate.AddDays(1.0); this.txtBeginDate.Text = dateTime.ToString("yyyy-MM-dd"); } this.txtName.Text = this.ddlYear.SelectedValue + "年" + this.ddlMonth.SelectedValue + "月的盘点单"; List <StocktakeDetailModel> dataSource = new List <StocktakeDetailModel>(); dataSource = this.stocktakeDetailBll.GetByTreasuryCode(this.tCode, Convert.ToBoolean(this.isFirst), Convert.ToDateTime(this.endDate)); this.gvwStocktake.DataSource = dataSource; this.gvwStocktake.DataBind(); } else { StocktakeModel editModel = this.stocktakeBll.GetEditModel(this.tCode); this.hfldStocktakeId.Value = editModel.Id; this.txtCode.Text = editModel.Code; this.DateInTime.Text = editModel.InputDate.ToString("yyyy-MM-dd HH:mm:ss"); this.txtPerson.Text = editModel.InputUser; this.txtName.Text = editModel.Name; this.txtTName.Text = this.tName; this.txtBeginDate.Text = editModel.BeginDate.ToString("yyyy-MM-dd"); this.txtEndDate.Text = editModel.EndDate.ToString("yyyy-MM-dd"); string stocktakeDate2 = editModel.StocktakeDate; string selectedValue3 = stocktakeDate2.Substring(0, 4); string selectedValue4 = stocktakeDate2.Substring(4, 2); this.ddlYear.SelectedValue = selectedValue3; this.ddlMonth.SelectedValue = selectedValue4; this.hfldState.Value = editModel.State.ToString(); this.txtExplain.Text = editModel.Note; List <StocktakeDetailModel> dataSource2 = new List <StocktakeDetailModel>(); dataSource2 = this.stocktakeDetailBll.GetByStocktakeId(editModel.Id); this.gvwStocktake.DataSource = dataSource2; this.gvwStocktake.DataBind(); } this.hfldTCode.Value = this.tCode; this.hfldToday.Value = DateTime.Now.ToString("yyyy/MM"); } }
public void Add(SqlTransaction trans, StocktakeModel model) { this.stocktake.Add(trans, model); }