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);
}
