Exemple #1
0
        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();
        }
Exemple #3
0
        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();
            }
        }