protected void GridLIS_RowClick(object sender, GridRowClickEventArgs e)
{
string code = GridLIS.Rows[e.RowIndex].DataKeys[1].ToString();
DataTable lisDT = DbHelperOra.Query(@"SELECT B.CODE,
B.NAME,
T.SEQNO,
T.LICENSEID,
T.LICENSENAME,
T.GDNAME,
T.GDSEQ,
T.OPERTIME,
nvl(decode(T.FLAG,'N','已保存','S','已提交','Y','已审核','R','已驳回'),'待录入') FLAG,
decode(T.PICNUM,'','【'||0||'】','【'||T.PICNUM||'】')PICNUM
FROM (SELECT T.SEQNO, T.LICENSEID, T.LICENSENAME, T.GDSEQ, T.GDNAME,T.OPERTIME,T.FLAG,T.PICNUM
FROM DOC_LICENSE_LOG T
WHERE GDSEQ = '" + code + "' AND T.ISCUR='N') T,DOC_LICENSE B WHERE B.CODE = T.LICENSEID(+) AND B.OBJUSER = '******'").Tables[0];
GridCertype.DataSource = lisDT;
GridCertype.DataBind();
}
private void DataSearch()
{
string expirenum = "";
if (nbxExpire.Text.Length <= 0)
{
expirenum = "30";
nbxExpire.Text = "30";
}
else
{
expirenum = nbxExpire.Text;
}
string sql = string.Format(@"select seqno,lictype,begrq,endrq,docid,memo,warning,
DECODE(SIGN(WARNING),
1， '已过期' || WARNING || '天',0, '今天过期',
-1,
'还有' || ABS(WARNING) || '天到期') tishi
FROM (select seqno,
'商品证照' lictype,
begrq,
endrq,
docid,
memo,
(trunc(sysdate) - trunc(endrq)) warning
from doc_license_goods
where (trunc(sysdate) - trunc(endrq)) >= -{0}
union all
select seqno,
'机构证照' lictype,
begrq,
endrq,
docid,
memo,
(trunc(sysdate) - trunc(endrq)) warning
from doc_license_supplier
where (trunc(sysdate) - trunc(endrq)) >= -{0}
union all
select seqno,
'客户关系证照' lictype,
begrq,
endrq,
docid,
memo,
(trunc(sysdate) - trunc(endrq)) warning
from doc_license_relationsup
where (trunc(sysdate) - trunc(endrq)) >= -{0}
union all
select grantid seqno,
'授权证照' lictype,
beginsqrq begrq,
endsqrq endrq,
docid,
memo,
(trunc(sysdate) - trunc(endsqrq)) warning
from doc_license_grant
where (trunc(sysdate) - trunc(endsqrq)) >= -{0})", Convert.ToInt32(expirenum));
string query = "";
StringBuilder strSql = new StringBuilder(sql);
if (ddlIsWarn.SelectedValue.Equals("ALLExpire"))
{
}
if (tbxDOCID.Text.Length > 0)
{
ddlIsWarn.SelectedValue = "ALLExpire";
query = tbxDOCID.Text.Trim();
strSql.AppendFormat("where docid LIKE '%{0}%'", query);
}
if (ddlIsWarn.SelectedValue.Equals("ISExpire"))
{
strSql.AppendFormat("where warning >= 0");
}
if (ddlIsWarn.SelectedValue.Equals("NOExpire"))
{
strSql.AppendFormat("where warning < 0");
}
int total = 0;
DataTable dtData = PubFunc.DbGetPage(GridCertype.PageIndex, GridCertype.PageSize, strSql.ToString(), ref total);
GridCertype.RecordCount = total;
GridCertype.DataSource = dtData;
GridCertype.DataBind();
}
private void HistoryDataSearch()
{
string strSQL = "SELECT SUPID,SUPNAME,decode(flag,'Y','已审核','N','已保存','S','已提交','R','已驳回','待录入')flag, ";
string strISBEGIN = @"";
string strSQLBEG = @"FROM(select ta.supid,ta.supname,(select flag from doc_license_log where supid=ta.supid and rownum=1) flag, ";
string strSQLEND = @"from (select a.supid, a.supname, b.code, b.name
from doc_supplier a, doc_license b where a.issupplier = 'Y' and b.objuser='******') ta, doc_license_log tb
where ta.supid = tb.supid(+) and ta.name = tb.licensename(+)";
string strsql = "group by ta.supid, ta.supname order by ta.supid, ta.supname)";
StringBuilder sb = new StringBuilder();
StringBuilder sb1 = new StringBuilder();
string strSQLAFTER = @"select distinct 'sum(decode(tb.licensename,''' || tb.name || ''',tb.picnum, 0))" + "\"" + "' || tb.name ||'" + "\"" + "'from doc_license tb where tb.objuser='******'";
strISBEGIN = @"select 'DECODE(SIGN(" + "\"" + "'|| tb.name ||'" + "\"" + "), 0，''未上传'',''已上传'') " + "\"" + "' || tb.name ||'" + "\"" + "'from doc_license tb where tb.objuser='******'";
DataTable mydtable = DbHelperOra.Query(strISBEGIN).Tables[0];
if (mydtable.Rows.Count > 0)
{
foreach (DataRow dr in mydtable.Rows)
{
sb1.Append(dr[0].ToString());
sb1.Append(",");
}
}
else
{
Alert.Show("请先维护供应商证照再上传证照图片！");
return;
}
sb1 = sb1.Remove(sb1.Length - 1, 1);
DataTable dtafter = DbHelperOra.Query(strSQLAFTER).Tables[0];
if (dtafter.Rows.Count > 0)
{
foreach (DataRow dr in dtafter.Rows)
{
sb.Append(dr[0].ToString());
sb.Append(",");
FineUIPro.BoundField bf;
bf = new FineUIPro.BoundField();
Match M = Regex.Match(dr[0].ToString(), "\".*\"");
bf = new FineUIPro.BoundField();
bf.ColumnID = M.ToString().Replace("\"", "");
bf.DataField = M.ToString().Replace("\"", "");
bf.HeaderText = M.ToString().Replace("\"", "");
bf.TextAlign = FineUIPro.TextAlign.Center;
bf.Width = 96;
GridLIS.Columns.Add(bf);
}
}
if (!string.IsNullOrWhiteSpace(txtName.Text))
{
strSQLEND = strSQLEND + " and (ta.supid like '%" + txtName.Text + "%' or ta.supname like '%" + txtName.Text + "%')";
}
strSQL = strSQL + sb1 + strSQLBEG + sb.ToString().TrimEnd(',') + strSQLEND + strsql;
if (sb.ToString().Length == 0)
{
strSQL = "select 1 from dual where 1=2 ";
}
int total = 0;
DataTable dt = PubFunc.DbGetPage(GridLIS.PageIndex, GridLIS.PageSize, strSQL, ref total);
GridLIS.RecordCount = total;
GridLIS.DataSource = dt;
GridLIS.DataBind();
if (dt.Rows.Count > 0)
{
DataTable lisDT = DbHelperOra.Query(@"SELECT B.CODE,
B.NAME,
T.SEQNO,
T.LICENSEID,
T.LICENSENAME,
T.SUPNAME,
T.SUPID,
T.OPERTIME,
nvl(decode(T.FLAG,'N','已保存','S','已提交','Y','已审核','R','已驳回'),'待录入') FLAG,
decode(T.PICNUM,'','【'||0||'】','【'||T.PICNUM||'】')PICNUM
FROM (SELECT T.SEQNO, T.LICENSEID, T.LICENSENAME, T.SUPID, T.SUPNAME,T.OPERTIME,T.FLAG,T.PICNUM
FROM DOC_LICENSE_LOG T
WHERE SUPID = '" + dt.Rows[0][0].ToString() + "' AND T.ISCUR='N') T,DOC_LICENSE B WHERE B.CODE = T.LICENSEID(+) AND B.OBJUSER = '******'").Tables[0];
GridCertype.DataSource = lisDT;
GridCertype.DataBind();
}
}