/// <summary>
/// 初始化加载数据
/// </summary>
/// <param name="AccountNumber">储户账号</param>
/// <param name="vId">VarietyID</param>
private void LoadData(string AccountNumber,string vId)
{
//查询账户
//DataTable dt = common.getDepInfo(AccountNumber, Convert.ToBoolean(Session["ISHQ"]), Session["WB_ID"].ToString());
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,CunID as BD_Address_CunID,strAddress,strName,PhoneNO,ISSendMessage,BankCardNO,dt_Update,");
strSql.Append(" numState,dt_Add,");
strSql.Append(" CASE (IDCard) WHEN '' THEN '未填写' ELSE '******' END as IDCard");
strSql.Append(" FROM dbo.Depositor ");
strSql.Append(" where 1=1");
strSql.Append(" and ISClosing=0");
strSql.Append(string.Format(" and AccountNumber='{0}' ", AccountNumber));
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
if (dt != null && dt.Rows.Count != 0)
{
string numState = dt.Rows[0]["numState"].ToString();
if (numState == "0")
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的账户已经申请挂失!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_numState.InnerText = dt.Rows[0]["numState"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
}
//存粮信息
string sql = string.Format(@" SELECT A.ID,A.StorageNumber,convert(varchar(10),A.StorageDate,120) AS StorageDate, A.AccountNumber,B.strName
AS VarietyID,A.Price_ShiChang,A.Price_DaoQi,A.CurrentRate,C.strName AS TimeID,A.StorageFee,a.VarietyID as vid
FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageVariety B ON A.VarietyID=B.ID
INNER JOIN dbo.StorageTime C ON A.TimeID=C.ID
where AccountNumber='{0}'", AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
sql += " and a.VarietyID=" + vId;
}
var dtStorage = SQLHelper.ExecuteDataTable(sql.ToString());
DataColumn dcstrlixi = new DataColumn("strlixi", typeof(string));
DataColumn dcnumlixi = new DataColumn("numlixi", typeof(string));
dtStorage.Columns.Add(dcstrlixi);
dtStorage.Columns.Add(dcnumlixi);
for (int i = 0; i < dtStorage.Rows.Count; i++)
{
Dictionary<string, string> dicLixi = common.GetLiXi_html(dtStorage.Rows[i]["ID"]);
string strlixi = dicLixi["strLixi"];
string numlixi = dicLixi["numLixi"];
dtStorage.Rows[i]["strlixi"] = strlixi;
dtStorage.Rows[i]["numlixi"] = numlixi;
}
//价值总额
numTotol = common.GetLiXiTotal(dtStorage);
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
StorageList.Style.Add("display", "block");
Repeater1.DataSource = dtStorage;
Repeater1.DataBind();
}
else
{
StorageList.Style.Add("display", "none");
}
//兑换
string exchangeSql = string.Format(@"select g.BusinessName,s.strName,g.VarietyCount,g.GoodName,g.VarietyInterest,g.Money_DuiHuan,g.dt_Exchange,g.GoodPrice,g.GoodCount from GoodExchange as g
inner join Dep_StorageInfo as d on g.Dep_SID=d.ID inner join
StorageVariety as s
on s.ID=d.VarietyID
where g.Dep_AccountNumber='{0}'", AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
exchangeSql += " and d.VarietyID=" + vId;
}
var dtExchange = SQLHelper.ExecuteDataTable(exchangeSql);
if (dtExchange != null && dtExchange.Rows.Count > 0)
{
exchangeList.Style.Add("display", "block");
R_exchange.DataSource = dtExchange;
R_exchange.DataBind();
}
else
{
exchangeList.Style.Add("display", "none");
}
//分时批量兑换
string exchangeGroupSql = string.Format(@"SELECT BusinessName,s.strName,g.VarietyCount,g.GoodName,g.GoodPrice,g.GoodCount,g.VarietyInterest,g.Money_DuiHuan,dt_Exchange FROM dbo.GoodExchangeGroup as g
inner join Dep_StorageInfo as d
on g.Dep_SID=d.ID
inner join StorageVariety as s on s.ID=d.VarietyID
where g.Dep_AccountNumber='{0}'",AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
exchangeGroupSql += " and s.ID="+ vId;
}
var dtExchangeGroup = SQLHelper.ExecuteDataTable(exchangeGroupSql);
if (dtExchangeGroup != null && dtExchangeGroup.Rows.Count > 0)
{
goodExchangeGroup.Style.Add("display", "block");
R_goodExchangeGroup.DataSource = dtExchangeGroup;
R_goodExchangeGroup.DataBind();
}
else
{
goodExchangeGroup.Style.Add("display", "none");
}
//存转销
string sqlStorageSell = string.Format(@"select s.BusinessName,VarietyName,s.VarietyCount,s.StorageDate,s.VarietyInterest,s.StorageMoney ,s.VarietyMoney ,s.dt_Sell from StorageSell as s
where s.Dep_AccountNumber='{0}'", AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
sqlStorageSell += " and VarietyID=" + vId;
}
var dtStorageSell = SQLHelper.ExecuteDataTable(sqlStorageSell);
if (dtStorageSell != null && dtStorageSell.Rows.Count > 0)
{
SellList.Style.Add("display", "block");
R_Sell.DataSource = dtStorageSell;
R_Sell.DataBind();
}
else
{
SellList.Style.Add("display", "none");
}
//产品换购
string sqlStorageShopping = string.Format(@"select s.BusinessName,s.VarietyName,s.VarietyCount,s.StorageDate,s.VarietyInterest,s.VarietyMoney,s.dt_Sell from StorageShopping as s
where Dep_AccountNumber={0}", AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
sqlStorageShopping += " and VarietyID=" + vId;
}
var dtStorageShop = SQLHelper.ExecuteDataTable(sqlStorageShopping);
if (dtStorageShop != null && dtStorageShop.Rows.Count > 0)
{
ShoppingList.Style.Add("display", "block");
R_Shopping.DataSource = dtStorageShop;
R_Shopping.DataBind();
}
else
{
ShoppingList.Style.Add("display", "none");
}
//修改记录
string sqlUpdateStorage = string.Format(@"select s.VarietyName,s.AccountNumber,s.StorageNumberRaw,s.StorageNumber,
s.StorageNumberChange,w.strName,u.strLoginName,s.createDate from SV_UpdateRecord as s
inner join WB as w on w.ID=s.WBID
inner join Users as u on u.ID=s.UserID
where s.AccountNumber={0}",AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
sqlUpdateStorage += " and s.VarietyID=" + vId;
}
var dtUpdateStorage=SQLHelper.ExecuteDataTable(sqlUpdateStorage);
if (dtUpdateStorage != null && dtUpdateStorage.Rows.Count > 0)
{
sv_updateStorage.Style.Add("display", "block");
r_updateStorage.DataSource = dtUpdateStorage;
r_updateStorage.DataBind();
}
else
{
sv_updateStorage.Style.Add("display", "none");
}
//退还记录
string sqlReturnStorage = string.Format(@"select s.VarietyName,s.AccountNumber,s.StorageNumberRaw,s.StorageNumber,
s.returnNumber,w.strName,u.strLoginName,s.createDate from SV_ReturnRecord as s
inner join WB as w on w.ID=s.WBID
inner join Users as u on u.ID=s.UserID
where s.AccountNumber='{0}' ", AccountNumber);
if (!string.IsNullOrWhiteSpace(vId))
{
sqlReturnStorage += " and s.VarietyID="+ vId;
}
var dtReturnStorage = SQLHelper.ExecuteDataTable(sqlReturnStorage);
if (dtReturnStorage != null && dtReturnStorage.Rows.Count > 0)
{
sv_returnStorage.Style.Add("display", "block");
R_ReturnStorage.DataSource = dtReturnStorage;
R_ReturnStorage.DataBind();
}
else
{
sv_returnStorage.Style.Add("display", "none");
}
}
private void GetDepositor(string dtDate)
{
StorageList.Style.Add("display", "none");
exchangeList.Style.Add("display", "none");
SellList.Style.Add("display", "none");
StorageLog.Style.Add("display", "none");
exchangeLog.Style.Add("display", "none");
SellLog.Style.Add("display", "none");
//获取存粮业务日志
StringBuilder strSqlStorage = new StringBuilder();
strSqlStorage.Append(" SELECT A.AccountNumber,B.strRealName as UserID, C.strName AS VarietyID,StorageNumber,CONVERT(varchar(100), A.StorageDate, 23) AS StorageDate,");
strSqlStorage.Append(" A.Price_ShiChang,D.strName AS TimeID,B.strRealName AS UserID");
strSqlStorage.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.Users B ON A.UserID=B.ID");
strSqlStorage.Append(" INNER JOIN dbo.StorageVariety C ON A.VarietyID=C.ID");
strSqlStorage.Append(" INNER JOIN dbo.StorageTime D ON A.TimeID=D.ID");
strSqlStorage.Append(" WHERE CONVERT(varchar(100), StorageDate, 23)=CONVERT(varchar(100), '" + dtDate.ToString() + "', 23)");//2015-01-01格式日期
strSqlStorage.Append(" ");
DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
StorageList.Style.Add("display", "block");
Repeater1.DataSource = dtStorage;
Repeater1.DataBind();
}
else
{
StorageLog.Style.Add("display", "block");
}
//获取兑换信息
StringBuilder strSqlEx = new StringBuilder();
strSqlEx.Append(" SELECT A.ID, B.strRealName AS UserID, Dep_AccountNumber AS AccountNumber , BusinessName,GoodName,GoodPrice,GoodCount,UnitName,VarietyCount,Money_DuiHuan");
strSqlEx.Append(" FROM dbo.GoodExchange A INNER JOIN dbo.Users B ON A.UserID=B.ID");
strSqlEx.Append(" where ISReturn=0"); //查询没有退还记录的商品
strSqlEx.Append(" and CONVERT(varchar(100), dt_Exchange, 23)=CONVERT(varchar(100), '" + dtDate.ToString() + "', 23)");
DataTable dtEx = SQLHelper.ExecuteDataTable(strSqlEx.ToString());
if (dtEx != null && dtEx.Rows.Count != 0)
{
exchangeList.Style.Add("display", "block");
R_exchange.DataSource = dtEx;
R_exchange.DataBind();
}
else
{
exchangeLog.Style.Add("display", "block");
}
//获取存转销信息
StringBuilder strSqlSell = new StringBuilder();
strSqlSell.Append(" SELECT A.ID, B.strRealName AS UserID,A.Dep_AccountNumber AS AccountNumber, BusinessName,CONVERT(varchar(100), dt_Sell, 111) AS dt_Sell,VarietyName,VarietyCount,StorageDate,VarietyInterest,VarietyMoney");
strSqlSell.Append(" FROM dbo.StorageSell A INNER JOIN dbo.Users B ON A.UserID=B.ID");
strSqlSell.Append(" where ISReturn=0"); //查询没有退还记录的商品
strSqlSell.Append(" and CONVERT(varchar(100), dt_Sell, 23)=CONVERT(varchar(100), '" + dtDate.ToString() + "', 23)");
DataTable dtSell = SQLHelper.ExecuteDataTable(strSqlSell.ToString());
if (dtSell != null && dtSell.Rows.Count != 0)
{
SellList.Style.Add("display", "block");
R_Sell.DataSource = dtSell;
R_Sell.DataBind();
}
else
{
SellLog.Style.Add("display", "block");
}
}
private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
StorageList.Style.Add("display", "none");
exchangeList.Style.Add("display", "none");
SellList.Style.Add("display", "none");
ShoppingList.Style.Add("display", "none");
//查询账户
DataTable dt = common.getDepInfo(AccountNumber, Convert.ToBoolean(Session["ISHQ"]), Session["WB_ID"].ToString());
if (dt != null && dt.Rows.Count != 0)
{
string numState = dt.Rows[0]["numState"].ToString();
if (numState == "0")
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的账户已经申请挂失!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_numState.InnerText = dt.Rows[0]["numState"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "js", "ShowDepositorInfo()", true);
//获取存粮信息
DataTable dtStorage = common.getDepStorageInfo(AccountNumber);
//价值总额
numTotol = common.GetLiXiTotal(dtStorage);
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
StorageList.Style.Add("display", "block");
Repeater1.DataSource = dtStorage;
Repeater1.DataBind();
}
//获取兑换信息
StringBuilder strSqlEx = new StringBuilder();
strSqlEx.Append(" SELECT ID, BusinessName,GoodName,GoodPrice,GoodCount,UnitName,VarietyCount,Money_DuiHuan");
strSqlEx.Append(" ,CASE ISReturn WHEN 0 THEN '兑换' ELSE '退还兑换' END AS ISReturn");
strSqlEx.Append(" FROM dbo.GoodExchange");
strSqlEx.Append(" where 1=1");
strSqlEx.Append(" AND Dep_AccountNumber='" + AccountNumber + "'");
strSqlEx.Append(" order by dt_Exchange desc");
DataTable dtEx = SQLHelper.ExecuteDataTable(strSqlEx.ToString());
if (dtEx != null && dtEx.Rows.Count != 0)
{
exchangeList.Style.Add("display", "block");
R_exchange.DataSource = dtEx;
R_exchange.DataBind();
}
//获取存转销信息
StringBuilder strSqlSell = new StringBuilder();
strSqlSell.Append(" SELECT ID, BusinessName,CONVERT(varchar(100), dt_Sell, 111) AS dt_Sell,VarietyName,VarietyCount,StorageDate,VarietyInterest,VarietyMoney");
strSqlSell.Append(" ,CASE ISReturn WHEN 0 THEN '存转销' ELSE '退还存转销' END AS ISReturn");
strSqlSell.Append(" FROM dbo.StorageSell ");
strSqlSell.Append(" where 1=1");//查询没有退还记录的商品
strSqlSell.Append(" AND Dep_AccountNumber='" + AccountNumber + "'");
strSqlSell.Append(" order by dt_Sell desc");
DataTable dtSell = SQLHelper.ExecuteDataTable(strSqlSell.ToString());
if (dtSell != null && dtSell.Rows.Count != 0)
{
SellList.Style.Add("display", "block");
R_Sell.DataSource = dtSell;
R_Sell.DataBind();
}
//获取换购信息
StringBuilder strSqlShopping = new StringBuilder();
strSqlShopping.Append(" SELECT ID, BusinessName,CONVERT(varchar(100), dt_Sell, 111) AS dt_Sell,VarietyName,VarietyCount,StorageDate,VarietyInterest,VarietyMoney");
strSqlShopping.Append(" ,CASE ISReturn WHEN 0 THEN '换购' ELSE '退还换购' END AS ISReturn");
strSqlShopping.Append(" FROM dbo.StorageShopping ");
strSqlShopping.Append(" where 1=1");//查询没有退还记录的商品
strSqlShopping.Append(" AND Dep_AccountNumber='" + AccountNumber + "'");
strSqlShopping.Append(" order by dt_Sell desc");
DataTable dtShopping = SQLHelper.ExecuteDataTable(strSqlShopping.ToString());
if (dtShopping != null && dtShopping.Rows.Count != 0)
{
ShoppingList.Style.Add("display", "block");
R_Shopping.DataSource = dtShopping;
R_Shopping.DataBind();
}
}
else
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的储户不存在!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
}