private void goodsDataBind()
{
string strSqlGoodSum = @"SELECT sum(c.dhs) dhs,sum((g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) * c.dhs )) as JE,COUNT(1) DPS
FROM DAT_CART c
left join doc_goods g on c.gdseq=g.gdseq
where c.USERID='{0}'";
DataTable dt = DbHelperOra.Query(string.Format(strSqlGoodSum, UserAction.UserID)).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
this.LiteralDhs.Text = dt.Rows[0]["DPS"].ToString();
this.LiteralJe.Text = dt.Rows[0]["JE"].ToString();
}
string strSqlGood = @"SELECT c.seqno,c.userid,c.gdseq,c.dhs,g.GDSEQ,";
//判断系统默认设置显示的商品名
if (PubFunc.DbGetPara("ShowName") == "HIS")
{
strSqlGood += "NVL(g.HISNAME,g.GDNAME) GDNAME,NVL(g.STR3,g.GDSPEC) GDSPEC,";
}
else
{
strSqlGood += "g.GDNAME,g.GDSPEC,";
}
strSqlGood += @"g.isflag5,g.BZHL,f_getunitname(g.UNIT) UNIT,g.CATID,g.PIZNO,c.deptid,f_getproducername(g.producer) producername,
--g.hsjj as price,
--(g.hsjj)*c.dhs as JE,
p.picpath,
g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) as price,
(g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) * c.dhs ) JE,
F_GETUNITNAME(DECODE(g.UNIT_SELL,
'D',
g.UNIT_DABZ,
'Z',
g.UNIT_ZHONGBZ,
g.UNIT)) UNIT_SELL_NAME,
DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL) BZHL_SELL
FROM DAT_CART c
left join doc_goods g on c.gdseq=g.gdseq
left join (select a.*,Row_Number() OVER (partition by gdseq ORDER BY rownum ) HANGHAO
from doc_goodspicture a) p
on c.gdseq=p.gdseq and p.HANGHAO=1 and p.flag='Y'and nvl(p.str2,'N') = 'N'
where c.USERID='{0}'";
strSqlGood = string.Format(strSqlGood, UserAction.UserID);
DataTable dtInfo = DbHelperOra.Query(strSqlGood).Tables[0];
DataTable dtnew = dtInfo.Clone();
foreach (DataRow row in dtInfo.Rows)
{
string path = row["PICPATH"].ToString();
if (!string.IsNullOrWhiteSpace(path))
{
if (File.Exists(Request.PhysicalApplicationPath + (path.Substring(2)).Replace("/", @"\")))
{
row["PICPATH"] = "http://" + Request.Url.Authority + path.Substring(1);
}
else
{
row["PICPATH"] = ApiUtil.GetConfigCont("PIC_PATH") + "/captcha/GetPictures.aspx?picpath=" + path;
}
}
else
{
row["PICPATH"] = "/res/images/noPic.jpg";
}
dtnew.Rows.Add(row.ItemArray);
}
RepeaterGoods.DataSource = dtnew;
RepeaterGoods.DataBind();
}
private void goodsDataBind()
{
strNextPage = "?page=" + (intPageIndex + 1).ToString();
strLastPage = "?page=" + (intPageIndex - 1).ToString();
string strWhere = "";
if (DbHelperOra.Exists("SELECT 1 FROM SYS_DEPT WHERE TYPE IN ('3','4') AND CODE = '" + UserAction.UserDept + "'"))
{
Object obj;
if (HttpContext.Current.Request.Cookies["DEPTOUT"] == null)
{
obj = DbHelperOra.GetSingle("select nvl((SELECT A.STOCK FROM SYS_DEPT A WHERE A.CODE = '" + UserAction.UserDept + "'),(select value from sys_para where code = 'DEFDEPT')) from dual");
}
else
{
obj = HttpContext.Current.Request.Cookies["DEPTOUT"].Value;
}
strWhere = " GDSEQ IN(SELECT A.GDSEQ FROM DOC_GOODSCFG A,DOC_GOODSCFG B ,doc_goods C WHERE A.GDSEQ = B.GDSEQ AND A.GDSEQ=C.GDSEQ AND C.FLAG='Y' AND A.ISCFG IN ('Y','1') AND A.DEPTID = '" + UserAction.UserDept + "' AND B.DEPTID = '" + obj.ToString() + "') ";
}
else
{
strWhere = " 1=2";
}
if (Request.QueryString["k"] != null && !"".Equals(Request.QueryString["k"]))
{
strWhere += " and (gdname like '%" + Request.QueryString["k"].Trim() + "%' OR GDSEQ like '%" + Request.QueryString["k"].Trim() + "%' OR ZJM like '%" + Request.QueryString["k"].Trim().ToUpper() + "%')";
strNextPage = strNextPage + "&k=" + Request.QueryString["k"].Trim();
strLastPage = strLastPage + "&k=" + Request.QueryString["k"].Trim();
}
if (Request.QueryString["catid"] != null && !"".Equals(Request.QueryString["catid"]))
{
strNextPage = strNextPage + "&catid=" + Request.QueryString["catid"].Trim();
strLastPage = strLastPage + "&catid=" + Request.QueryString["catid"].Trim();
string catIds = Request.QueryString["catid"].Trim(',');
if (!String.IsNullOrWhiteSpace(catIds))
{
if (catIds.IndexOf(",") > 0)
{
string[] catIdsArray = catIds.Split(',');
catIds = "";
foreach (string catId in catIdsArray)
{
catIds += "'" + catId + "',";
}
catIds = catIds.Trim(',');
}
else
{
catIds = "'" + catIds + "'";
}
strWhere += " and CATID0 IN (" + catIds + ")";
}
}
//取得有效行
object rowno = DbHelperOra.GetSingle("SELECT MIN(HANGHAO) FROM ( SELECT ROWNUM HANGHAO FROM doc_goodspicture WHERE flag='Y' and nvl(str2,'N') = 'N' ORDER BY ROWNUM)");
int pageNum = AspNetPager1.PageSize;
string strSqlCount = @"select count(1) from
(select g.GDSEQ,g.GDNAME,g.GDSPEC,g.BZHL,g.UNIT,g.CATID0,g.PIZNO,p.picpath,ZJM from doc_goods g
left join (SELECT GDSEQ, MAX(PICPATH) PICPATH
FROM DOC_GOODSPICTURE
WHERE FLAG = 'Y'
AND NVL(STR2, 'N') = 'N'
GROUP BY GDSEQ) p on g.gdseq=p.gdseq and rownum = 1 where g.flag = 'Y') where {0}";
strSqlCount = string.Format(strSqlCount, strWhere);
AspNetPager1.RecordCount = Convert.ToInt32(DbHelperOra.GetSingle(strSqlCount));
this.LiteralNum.Text = AspNetPager1.RecordCount.ToString();
this.LiteralPageNum.Text = (Math.Ceiling(AspNetPager1.RecordCount / 10.0)).ToString();
if (AspNetPager1.RecordCount >= 0 && AspNetPager1.RecordCount < 10)
{
strLastPage = "<a title='上一页' href='javascript:void(0);' class='sort_page_arrow'><</a>";
strNextPage = "<a hreCeilingf='javascript:void(0);' title='下一页' class='sort_page_arrow'>></a>";
}
else
{
if (intPageIndex == 1)
{
strLastPage = "<a title='上一页' href='javascript:void(0);' class='sort_page_arrow'><</a>";
strNextPage = "<a href='" + strNextPage + "' title='下一页' class='sort_page_arrow'>></a>";
}
else if (intPageIndex == (AspNetPager1.RecordCount / pageNum + 1))
{
strLastPage = "<a title='上一页' href='" + strLastPage + "' class='sort_page_arrow'><</a>";
strNextPage = "<a href='javascript:void(0);' title='下一页' class='sort_page_arrow'>></a>";
}
else
{
strLastPage = "<a title='上一页' href='" + strLastPage + "' class='sort_page_arrow'><</a>";
strNextPage = "<a href='" + strNextPage + "' title='下一页' class='sort_page_arrow'>></a>";
}
}
this.LiteralLastPage.Text = strLastPage;
this.LiteralNextPage.Text = strNextPage;
string strSqlGood = @"select * from
(select t.*,ROWNUM rn from
(select GDSEQ,GDNAME,GDSPEC,BZHL,UNIT,UNITNAME,CATID0,PIZNO,HSJJ,producername,picpath,flag,isflag5 from
(select g.GDSEQ,";
//判断系统默认设置显示的商品名
if (PubFunc.DbGetPara("ShowName") == "HIS")
{
strSqlGood += "NVL(g.HISNAME,g.GDNAME) GDNAME,NVL(g.STR3,g.GDSPEC) GDSPEC,";
}
else
{
strSqlGood += "g.GDNAME,g.GDSPEC,";
}
strSqlGood += @"g.isflag5,g.BZHL,
--g.UNIT,
DECODE(g.UNIT_SELL, 'D', g.UNIT_DABZ, 'Z', g.UNIT_ZHONGBZ, g.UNIT) UNIT,
--u.NAME UNITNAME,
F_GETUNITNAME(DECODE(g.UNIT_SELL, 'D', g.UNIT_DABZ, 'Z', g.UNIT_ZHONGBZ, g.UNIT)) UNITNAME,
g.CATID0,
g.PIZNO,
--g.HSJJ,
g.hsjj * (DECODE(g.UNIT_SELL, 'D', g.NUM_DABZ, 'Z', g.NUM_ZHONGBZ, g.BZHL)) as HSJJ,
f_getproducername(g.producer) producername,
p.picpath,ZJM,g.flag from doc_goods g
left join (SELECT GDSEQ, MAX(PICPATH)PICPATH
FROM DOC_GOODSPICTURE
WHERE FLAG = 'Y'
AND NVL(STR2, 'N') = 'N'
GROUP BY GDSEQ) p
on g.gdseq = p.gdseq
left join doc_goodsunit u
on g.unit = u.code)
where {0}) t
where t.flag = 'Y') where rn> " + (intPageIndex - 1) * pageNum + " and rn<= " + intPageIndex * pageNum;
strSqlGood = string.Format(strSqlGood, strWhere);
DataTable dt = DbHelperOra.Query(strSqlGood).Tables[0];
DataTable dtnew = dt.Clone();
foreach (DataRow row in dt.Rows)
{
string path = row["PICPATH"].ToString();
if (!string.IsNullOrWhiteSpace(path))
{
if (File.Exists(Request.PhysicalApplicationPath + (path.Substring(2)).Replace("/", @"\")))
{
row["PICPATH"] = "http://" + Request.Url.Authority + path.Substring(1);
}
else
{
row["PICPATH"] = ApiUtil.GetConfigCont("PIC_PATH") + "/captcha/GetPictures.aspx?picpath=" + path;
}
}
else
{
row["PICPATH"] = "/res/images/noPic.jpg";
}
dtnew.Rows.Add(row.ItemArray);
}
RepeaterGoods.DataSource = dtnew;
RepeaterGoods.DataBind();
}
