private void DataSearch()
{
int total = 0;
string strSearch = "";
string sql = "";
if (DbHelperOra.Exists("SELECT 1 FROM DAT_YRK_EXT WHERE BILLNO IN(" + Billno() + ") AND NVL(STR1,'#') <> '#'"))
{
sql = String.Format(@"SELECT A.billno seqno,C.GDSEQ,C.GDNAME,C.GDSPEC,C.BZHL,C.UNIT,f_getunitname(C.UNIT) UNITNAME,1 SSSL,C.HSJJ,A.PH,
A.RQ_SC,A.YXQZ,C.PIZNO PZWH,C.PRODUCER,f_getproducername(C.PRODUCER) PRODUCERNAME,A.STR1,C.JXTAX,C.ISGZ
FROM DAT_YRK_EXT A,DOC_GOODS C
WHERE A.GDSEQ = C.GDSEQ AND A.BILLNO IN ({0}) AND NVL(A.STR1,'#') <>'#' AND C.FLAG IN('Y','T')
UNION ALL
SELECT A.billno,C.GDSEQ,C.GDNAME,C.GDSPEC,C.BZHL,C.UNIT,f_getunitname(C.UNIT) UNITNAME,COUNT(1) SSSL,C.HSJJ,A.PH,A.RQ_SC,A.YXQZ,C.PIZNO PZWH,C.PRODUCER,f_getproducername(C.PRODUCER) PRODUCERNAME,A.STR1,C.JXTAX,C.ISGZ
FROM DAT_YRK_EXT A,DOC_GOODS C
WHERE A.GDSEQ = C.GDSEQ AND A.BILLNO IN ({0}) AND NVL(A.STR1,'#') ='#' AND C.FLAG IN('Y','T')
GROUP BY C.GDSEQ,C.GDNAME,C.GDSPEC,C.BZHL,C.UNIT,C.HSJJ,A.PH,A.RQ_SC,A.YXQZ,C.PIZNO,C.PRODUCER,A.STR1,C.JXTAX,A.billno", Billno());
}
else
{
sql = string.Format(@"SELECT A.SEQNO,
F_GETUNITNAME(C.UNIT) UNITNAME,
F_GETPRODUCERNAME(C.PRODUCER) PRODUCERNAME,
F_GETSUPNAME(b.supid) SUPNAME,
F_GETSUPNAME(C.SUPPLIER) SUPPLIERNAME,B.HSJE,
C.GDSEQ,C.GDNAME,C.GDSPEC,C.BZHL,C.HSJJ,C.ZPBH,'{1}' HWID,C.JXTAX,C.PIZNO PZWH,C.PRODUCER,b.supid,C.UNIT,B.SSSL,B.PH,B.RQ_SC,B.YXQZ,
NVL(D.ISJF,'Y') ISJF,B.ISGZ
FROM DAT_YRK_DOC A, DAT_YRK_COM B , DOC_GOODS C,DOC_GOODSCFG D
WHERE A.SEQNO = B.SEQNO
AND B.GDSEQ = C.GDSEQ
AND D.GDSEQ=B.GDSEQ
AND A.DEPTID=D.DEPTID
AND A.SEQNO IN({0}) AND A.FLAG = 'Y' AND C.FLAG IN('Y','T')
AND EXISTS(SELECT 1 FROM DOC_GOODSCFG WHERE DEPTID = '{1}' AND GDSEQ = B.GDSEQ)", Billno(), docDEPTID.SelectedValue);
}
if (!string.IsNullOrWhiteSpace(trbSearch.Text))
{
strSearch = string.Format(" AND (C.GDSEQ LIKE '%{0}%' OR C.GDNAME LIKE '%{0}%' OR C.ZJM LIKE '%{0}%' OR C.BARCODE LIKE '%{0}%')", trbSearch.Text.ToUpper());
}
//strSearch += " ORDER BY B.ROWNO";
sql += strSearch;
StringBuilder strSql = new StringBuilder(sql);
DataTable dtData = PubFunc.DbGetPage(GoodsInfo.PageIndex, GoodsInfo.PageSize, strSql.ToString(), ref total);
GoodsInfo.RecordCount = total;
GoodsInfo.DataSource = dtData;
GoodsInfo.DataBind();
}
private void DataSearch()
{
int total = 0;
string strSearch = "";
//非定数
string sql = string.Format(@"SELECT * FROM
(SELECT (A.KCSL - NVL(B.DSSL,0)) KSSPSL,'非定数' isds,
F_GETUNITNAME(C.UNIT) UNITNAME,
F_GETPRODUCERNAME(C.PRODUCER) PRODUCERNAME,
F_GETSUPNAME(C.SUPPLIER) SUPPLIERNAME,
'' DSCODE,C.GDSEQ,C.GDNAME,C.GDSPEC,C.BZHL,C.HSJJ,C.ZPBH,'{0}' HWID,C.JXTAX,C.PIZNO PZWH,C.PRODUCER,C.UNIT,C.ZJM,C.BARCODE
FROM (SELECT GDSEQ,SUM(KCSL) KCSL FROM DAT_GOODSSTOCK A WHERE KCSL>0 AND A.DEPTID ='{0}' GROUP BY GDSEQ) A,
(SELECT GDSEQ,SUM(DSHL) DSSL FROM DAT_GOODSDS_LOG WHERE DEPTIN = '{0}' AND FLAG = 'N' AND SL > 0 AND DSHL>0 GROUP BY GDSEQ) B,
DOC_GOODS C
WHERE A.GDSEQ = B.GDSEQ(+) AND C.GDSEQ = A.GDSEQ AND C.FLAG IN('Y','T') AND C.ISGZ = 'N' AND A.KCSL> NVL(B.DSSL,0)
AND EXISTS (SELECT 1 FROM DOC_GOODSCFG WHERE DEPTID = '{1}' AND GDSEQ = C.GDSEQ)", docDEPTOUT.SelectedValue, docDEPTID.SelectedValue);
sql += string.Format(@" union all
select PZ.DSHL,'定数',
F_GETUNITNAME(SP.UNIT) UNITNAME,
F_GETPRODUCERNAME(SP.PRODUCER) PRODUCERNAME,
F_GETSUPNAME(SUPPLIER) SUPPLIERNAME,
PZ.BARCODE DSCODE,SP.GDSEQ,SP.GDNAME,SP.GDSPEC,SP.BZHL,SP.HSJJ,SP.ZPBH,'{0}' HWID,SP.JXTAX,SP.PIZNO PZWH,SP.PRODUCER,SP.UNIT,SP.ZJM,SP.BARCODE
from DOC_GOODS SP, DAT_GOODSDS_LOG PZ
where SP.gdseq = PZ.gdseq AND SP.ISGZ = 'N' AND SP.FLAG IN('Y','T') AND PZ.FLAG='N' AND PZ.DEPTIN = '{0}' AND PZ.SL > 0 AND PZ.DSHL>0
AND EXISTS(SELECT 1 FROM DOC_GOODSCFG WHERE DEPTID = '{1}' AND GDSEQ = SP.GDSEQ))", docDEPTOUT.SelectedValue, docDEPTID.SelectedValue);
if (!string.IsNullOrWhiteSpace(trbSearch.Text))
{
strSearch = string.Format(" WHERE (GDSEQ LIKE '%{0}%' OR GDNAME LIKE '%{0}%' OR ZJM LIKE '%{0}%' OR BARCODE LIKE '%{0}%')", trbSearch.Text.ToUpper());
}
sql += strSearch;
StringBuilder strSql = new StringBuilder(sql);
strSql.AppendFormat(" ORDER BY {0} {1}", GoodsInfo.SortField, GridGoods.SortDirection);
DataTable dtData = PubFunc.DbGetPage(GoodsInfo.PageIndex, GoodsInfo.PageSize, strSql.ToString(), ref total);
GoodsInfo.RecordCount = total;
GoodsInfo.DataSource = dtData;
GoodsInfo.DataBind();
}