コード例 #1
0
        /// <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");
            }


        }
コード例 #2
0
        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");
            }
        }
コード例 #3
0
        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;
            }
        }