public static DataSet Detail(string ordercode) { using (DBSession db = new DBSession()) { string sql = ""; DataSet ds = new DataSet(); DataTable dt_order = new DataTable(); sql = @"select ort.code ,ort.submittime,ort.submitusername,ort.inspmoendtime,ort.inspmoendname ,ort.inspsiteapplytime,ort.inspsiteapplyusername,ort.inspcoendtime,ort.inspcoendname ,ort.inspchecktime,ort.inspcheckname,ort.insppreendtime,ort.insppreendname ,ort.inspsitepasstime,ort.inspsitepassusername,ort.insprependtime,ort.insprependname ,ort.inspcheckpic,ort.fumigationtime,ort.fumigationname from list_order ort where ort.isinvalid=0 and code='" + ordercode + "'"; dt_order = db.QuerySignle(sql); //dt_order.TableName = "order"; ds.Tables.Add(dt_order); DataTable dt_insp = new DataTable(); sql = @"select li.APPROVALCODE,li.INSPECTIONCODE,li.CLEARANCECODE,li.MODIFYFLAG,li.INSPSTATUS from list_inspection li where li.isinvalid=0 and li.ordercode='" + ordercode + "'"; dt_insp = db.QuerySignle(sql); //dt_insp.TableName = "insp"; ds.Tables.Add(dt_insp); return(ds); } }
/// <summary> /// 订单_获取本人未推送的所有订阅信息 /// </summary> /// <returns></returns> public static DataTable getSubscribeInfo_Order(int userId) { try { using (DBSession db = new DBSession()) { string strWhere = " (subws.codetype=1 or subws.codetype=2) and (subws.TRIGGERSTATUS=0 or subws.TRIGGERSTATUS=1) and "; if (userId > 0) { strWhere += " subws.userid=" + userId + " and"; } strWhere += " subws.isinvalid=0"; string sql = @"select lo.busiunitname,lo.busitype,lo.divideno,lo.repwayid,lo.contractno,lo.goodsnum,lo.goodsgw,to_char(lo.declstatus) as declstatus,to_char(lo.inspstatus) as inspstatus,lo.logisticsname, ws.cusno,ws.triggerstatus, ws.substype,ws.status as substatus ,'' as sublogstatus,ws.statusvalue,sb.name as businame,sr.name as repwayname from wechat_subscribe ws left join list_order lo on ws.cusno=lo.cusno left join cusdoc.sys_busitype sb on lo.busitype=sb.code left join cusdoc.sys_repway sr on lo.repwayid=sr.code where ws.cusno in ( select cusno from ( select rownum as rown ,tab.* from (select * from (select cusno,substime, ROW_NUMBER() OVER(partition by cusno order by substime desc) as rnum from wechat_subscribe subws where {0} and subws.cusno is not null) newws where newws.rnum=1 order by newws.substime desc ) tab where rownum<={1}) t1 where t1.rown>{2}) and (ws.codetype=1 or ws.codetype=2) and ws.isinvalid=0 order by ws.cusno,ws.substype,ws.statusvalue"; sql = string.Format(sql, strWhere, 100, 0); return(db.QuerySignle(sql)); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Order:" + ex.Message); return(null); } }
/// <summary> /// 判断用户是否已经存在, /// </summary> /// <param name="usercode"></param> /// <param name="openid"></param> /// <param name="nickname"></param> /// <returns></returns> public static bool UserExsit(string usercode, string openid, string nickname) { using (DBSession db = new DBSession()) { string sql = "select wcopenid,wcnickname,isunbind from wechat_user where gwyusercode='" + usercode + "' and wcopenid<>'" + openid + "' order by unbindtime desc "; DataTable dt = db.QuerySignle(sql); if (dt != null && dt.Rows.Count > 0) { //已经存在则向之前的用户推送消息 string exsql = @"insert into wechat_loginexceptioninfo(id,loginopenid,loginnickname,usercode,oldopenid,oldnickname) values(wechat_loginexceptioninfo_id.nextval,'{0}','{1}','{2}','{3}','{4}')"; exsql = string.Format(exsql, openid, nickname, usercode, dt.Rows[0]["wcopenid"].ToString2(), dt.Rows[0]["wcnickname"].ToString2()); db.ExecuteSignle(exsql); if (dt.Rows[0]["isunbind"].ToString2() == "1") { return(false);//已经解绑,可以登录 } else { return(true);//未解绑,不可登录 } } return(false); } }
/// <summary> /// 订单_获取本人未推送的所有订阅信息 /// </summary> /// <returns></returns> public static DataTable getNewSubscribeInfo_Order(int userId) { try { using (DBSession db = new DBSession()) { string strWhere = " (ws.codetype=1 or ws.codetype=2) and ws.TRIGGERSTATUS=0 and ws.userid=" + userId + " and"; strWhere += " ws.isinvalid=0 and lo.isinvalid=0"; string sql = @"with newt as( select * from (select tab.*,rownum as rown from (select lo.busiunitname,lo.busitype,lo.divideno,lo.repwayid,lo.contractno,lo.goodsnum,lo.goodsgw,to_char(lo.declstatus) as declstatus, to_char(lo.inspstatus) as inspstatus,lo.logisticsname, ws.cusno,ws.triggerstatus, ws.substype,ws.status,ws.pushtime,ws.id,ws.ordercode, ws.statusvalue,ws.substime,'' as sum from wechat_subscribe ws left join list_order lo on ws.cusno=lo.cusno where {0} order by substime desc ) tab ) where rown>{1} and rown<={2}) select newt.*,sb.name as businame,sr.name as repwayname from newt left join cusdoc.sys_busitype sb on newt.busitype=sb.code left join cusdoc.sys_repway sr on newt.repwayid=sr.code order by newt.substime desc"; sql = string.Format(sql, strWhere, 0, 1000); return(db.QuerySignle(sql)); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Order:" + ex.Message); return(null); } }
/// <summary> /// 预制单_获取本人未推送的所有订阅信息 /// </summary> /// <returns></returns> public static DataTable getSubscribeInfo_Decl(int userId) { try { using (DBSession db = new DBSession()) { string strWhere = " subws.codetype=3 and (subws.TRIGGERSTATUS=0 or subws.TRIGGERSTATUS=1) and "; if (userId > 0) { strWhere += " subws.userid=" + userId + " and "; } strWhere += " subws.isinvalid=0"; string sql = @"select ld.declarationcode,ld.goodsnum,ld.goodsgw,ld.tradecode,ld.modifyflag,ld.customsstatus,ld.transname,ws.declarationcode, ws.cusno,ws.triggerstatus, ws.substype,ws.status as substatus ,ws.statusvalue,cbd.name as tradename from wechat_subscribe ws left join list_declaration ld on ws.declarationcode=ld.declarationcode left join cusdoc.base_decltradeway cbd on ld.tradecode=cbd.code where ws.declarationcode in ( select declarationcode from ( select rownum as rown ,tab.* from (select * from (select declarationcode,substime, ROW_NUMBER() OVER(partition by declarationcode order by substime desc) as rnum from wechat_subscribe subws where {0} and declarationcode is not null) newws where newws.rnum=1 order by newws.substime desc ) tab where rownum<={1}) t1 where t1.rown>{2} ) and ws.codetype=3 and ws.isinvalid=0 and ws.declarationcode is not null order by ws.declarationcode,ws.substype,ws.statusvalue"; return(db.QuerySignle(string.Format(sql, strWhere, 100, 0))); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Decl:" + ex.Message); return(null); } }
/// <summary> /// 获取预制单订阅状态 /// </summary> /// <param name="declarationcode"></param> /// <returns></returns> public static DataTable getDeclstatus(string declarationCode) { using (DBSession db = new DBSession()) { string sql = "select customsstatus from list_declaration where declarationcode='" + declarationCode + "'"; return(db.QuerySignle(sql)); } }
public static DataSet Detail(string ordercode) { using (DBSession db = new DBSession()) { string sql = ""; DataSet ds = new DataSet(); DataTable dt_order = new DataTable(); sql = @"select ort.code ,ort.submittime,ort.submitusername,ort.moendtime,ort.moendname ,ort.siteapplytime,ort.siteapplyusername,ort.coendtime,ort.coendname ,ort.declchecktime,ort.declcheckname,ort.preendtime,ort.preendname ,ort.sitepasstime,ort.sitepassusername,ort.rependtime,ort.rependname ,ort.checkpic,ort.auditflagtime,ort.auditflagname from list_order ort where ort.isinvalid=0 and code='" + ordercode + "'"; dt_order = db.QuerySignle(sql); //dt_order.TableName = "order"; ds.Tables.Add(dt_order); DataTable dt_decl = new DataTable(); sql = @"select det.code,det.declarationcode,det.GOODSNUM,det.GOODSGW,det.tradecode,det.TRANSNAME,det.VOYAGENO,det.modifyflag,det.CUSTOMSSTATUS from list_declaration det where det.isinvalid=0 and ordercode='" + ordercode + "'"; /*sql = @"select det.code,lda.declarationcode,lda.GOODSNUM,lda.GOODSGW,lda.TRADEMETHOD,lda.TRANSNAME,lda.VOYAGENO,det.modifyflag,det.CUSTOMSSTATUS * from list_declaration det * left join (select code,associateno,isinvalid,busitype,cusno from list_order where code='{0}') ort on det.ordercode = ort.code * left join list_declaration_after lda on det.code=lda.code and lda.csid=1 * left join (select ordercode from list_declaration ld where ld.isinvalid=0 and ld.STATUS!=130 and ld.STATUS!=110) a on det.ordercode=a.ordercode * left join list_verification lv on lda.declarationcode=lv.declarationcode * left join ( * select ASSOCIATENO from list_order l inner join list_declaration i on l.code=i.ordercode * where l.ASSOCIATENO is not null and l.isinvalid=0 and i.isinvalid=0 and (i.STATUS!=130 and i.STATUS!=110) * ) b on ort.ASSOCIATENO=b.ASSOCIATENO * where (det.STATUS=130 or det.STATUS=110) and det.isinvalid=0 and ort.isinvalid=0 and det.CUSTOMSSTATUS!='删单或异常' * and a.ordercode is null * and b.ASSOCIATENO is null";*/ dt_decl = db.QuerySignle(sql); //dt_decl.TableName = "decl"; ds.Tables.Add(dt_decl); return(ds); } }
/// <summary> /// 获取业务报关状态 /// </summary> /// <param name="cusno"></param> /// <returns></returns> public static DataTable getOrderstatus(string ordercode) { using (DBSession db = new DBSession()) { string sql = "select declstatus from list_order where code='" + ordercode + "'"; return(db.QuerySignle(sql)); } }
public static DataTable AssCon(string predelcode) { using (DBSession db = new DBSession()) { DataTable dt = null; DataTable dt1 = new DataTable(); dt1 = db.QuerySignle("select ordercode from list_declaration where code='" + predelcode + "'"); string ordercode = dt1.Rows[0][0].ToString(); DataTable dt2 = new DataTable(); dt2 = db.QuerySignle("select code from list_order where associateno=(select associateno from list_order where code='" + ordercode + "') and code!='" + ordercode + "'"); //关联订单不存在 if (dt2 == null) { return(dt); } if (dt2.Rows.Count <= 0) { return(dt); } string ordercode_con = dt2.Rows[0][0].ToString(); string tempsql = @"select det.code,det.modifyflag,det.CUSTOMSSTATUS ,lda.declarationcode,lda.BLNO,lda.CONSIGNEESHIPPER,lda.CONSIGNEESHIPPERNAME,lda.CONTRACTNO,lda.TRADEMETHOD,lda.TRANSNAME,lda.VOYAGENO,to_char(lda.reptime,'yyyy-mm-dd') reptime ,lda.GOODSNUM,lda.GOODSGW ,ort.busitype,ort.cusno from list_declaration det left join (select code,associateno,isinvalid,busitype,cusno from list_order where code='{0}') ort on det.ordercode = ort.code left join list_declaration_after lda on det.code=lda.code and lda.csid=1 left join (select ordercode from list_declaration ld where ld.isinvalid=0 and ld.STATUS!=130 and ld.STATUS!=110) a on det.ordercode=a.ordercode left join list_verification lv on lda.declarationcode=lv.declarationcode left join ( select ASSOCIATENO from list_order l inner join list_declaration i on l.code=i.ordercode where l.ASSOCIATENO is not null and l.isinvalid=0 and i.isinvalid=0 and (i.STATUS!=130 and i.STATUS!=110) ) b on ort.ASSOCIATENO=b.ASSOCIATENO where (det.STATUS=130 or det.STATUS=110) and det.isinvalid=0 and ort.isinvalid=0 and det.CUSTOMSSTATUS!='删单或异常' and a.ordercode is null and b.ASSOCIATENO is null"; string sql = string.Format(tempsql, ordercode_con); return(db.QuerySignle(sql)); } }
public DataTable getDeclPath(string orderCode) { using (DBSession db = new DBSession()) { string sql = "select filename,declcode from list_attachment where ordercode='" + orderCode + "' and filetype=61 order by declcode"; DataTable dt = db.QuerySignle(sql); return(dt); } }
public static DataTable FileConsult(string preinspcode) { using (DBSession db = new DBSession()) { string sql = "select filename,inspcode from list_attachment where ordercode=(select ordercode from list_inspection where code='" + preinspcode + "') and filetype=62 order by inspcode"; DataTable dt = db.QuerySignle(sql); return(dt); } }
public DataSet getOrderDetail(string code) { using (DBSession db = new DBSession()) { DataSet ds = new DataSet(); //业务信息 string sql = @"select lo.busiunitname,lo.busitype,sr.name as busitypename,lo.divideno,lo.goodsnum,lo.goodsgw,lo.cusno,lo.contractno,lo.declcheckname,lo.inspcheckname, lo.code,lo.totalno,lo.entrusttype,lo.busitype,lo.submittime,lo.submitusername,lo.moendtime,lo.moendname,lo.coendtime,lo.coendname, lo.preendtime,lo.preendname,lo.rependtime,lo.rependname,lo.siteapplytime,lo.siteapplyusername,lo.sitepasstime,lo.sitepassusername, lo.inspmoendtime,lo.inspmoendname,lo.inspcoendtime,lo.inspcoendname,lo.insppreendtime,lo.insppreendname,lo.insprependtime,lo.insprependname, lo.inspsiteapplytime,lo.inspsiteapplyusername,lo.inspsitepasstime,lo.inspsitepassusername,lo.auditflagtime,lo.auditflagname,lo.fumigationtime, lo.fumigationname,lo.declchecktime,lo.inspchecktime from list_order lo left join cusdoc.sys_busitype sr on sr.enabled=1 and sr.code=lo.busitype where lo.code='" + code + "'"; DataTable dt1 = db.QuerySignle(sql); dt1.TableName = "OrderTable"; ds.Tables.Add(dt1); //报关单信息 sql = @" select ld.code,ld.declarationcode,ld.goodsnum,ld.goodsgw,ld.tradecode,ld.transname,to_char(ld.modifyflag) as modifyflag,ld.customsstatus, cbd.name as tradename from list_declaration ld left join cusdoc.base_decltradeway cbd on ld.tradecode=cbd.code where ld.isinvalid=0 and ld.ordercode='" + code + "'"; DataTable dt2 = db.QuerySignle(sql); dt2.TableName = "DeclTable"; ds.Tables.Add(dt2); //报检单信息 if (dt1.Rows[0]["entrusttype"].ToString2() == "02" || dt1.Rows[0]["entrusttype"].ToString2() == "03") { sql = @"select li.approvalcode,li.inspectioncode,li.clearancecode,to_char(li.modifyflag) as modifyflag,li.inspstatus from list_inspection li where li.isinvalid=0 and li.ordercode='" + code + "'"; DataTable dt3 = db.QuerySignle(sql); dt3.TableName = "InspTable"; ds.Tables.Add(dt3); } //物流信息 if (!string.IsNullOrEmpty(dt1.Rows[0]["totalno"].ToString2()) && !string.IsNullOrEmpty(dt1.Rows[0]["divideno"].ToString2())) { sql = @"select ll.totalno,ll.divideno,ll.operater,ll.operate_type,ll.operate_result,ll.operate_date from list_logisticsstatus ll where ll.totalno='{0}' and ll.divideno='{1}' order by ll.operate_type,ll.operate_date"; DataTable dt4 = db.QuerySignle(string.Format(sql, dt1.Rows[0]["totalno"], dt1.Rows[0]["divideno"])); dt4.TableName = "LogisticsTable"; ds.Tables.Add(dt4); } return(ds); } }
/*public static DataSet getdeclcontainerdata(string ordercode) * { * using (DBSession db = new DBSession()) * { * string sql = ""; DataSet ds = new DataSet(); * * DataTable dt_order = new DataTable(); * sql = @"select ort.code,ort.cusno,ort.declchecktime,ort.declcheckname,ort.declcheckid,ort.ischeck * from list_order ort where ort.isinvalid=0 and code='" + ordercode + "'"; * dt_order = db.QuerySignle(sql); * ds.Tables.Add(dt_order); * * DataTable dt_predeclcontainer = new DataTable(); * sql = @"select lp.CONTAINERNO,lp.CONTAINERSIZEE * from list_predeclcontainer lp * where lp.isinvalid=0 and lp.ordercode='" + ordercode + "'"; * dt_predeclcontainer = db.QuerySignle(sql); * ds.Tables.Add(dt_predeclcontainer); * return ds; * } * }*/ public static DataTable getdeclcontainerdata(string ordercode) { using (DBSession db = new DBSession()) { string sql = @"select lp.CONTAINERNO,lp.CONTAINERSIZEE from list_predeclcontainer lp where lp.isinvalid=0 and lp.ordercode='" + ordercode + "'"; return(db.QuerySignle(sql)); } }
public static DataTable picfileconsult(string ordercode) { DataTable dt = new DataTable(); using (DBSession db = new DBSession()) { string sql = "select filename from list_attachment where filetype=67 and ordercode='" + ordercode + "'"; dt = db.QuerySignle(sql); } return(dt); }
public static DataTable getloadcheckdata(string ordercode) { using (DBSession db = new DBSession()) { string sql = @"select ort.ischeck,to_char(ort.declchecktime,'yyyyMMdd HH24:mi') declchecktime,ort.declcheckid,ort.declcheckname,ort.checkremark declcheckremark ,ort.auditflag,to_char(ort.auditflagtime,'yyyyMMdd HH24:mi') auditflagtime,ort.auditflagid,ort.auditflagname,ort.auditcontent from list_order ort where ort.isinvalid=0 and code='" + ordercode + "'"; return(db.QuerySignle(sql)); } }
public static DataTable getloadcheckdata(string ordercode) { using (DBSession db = new DBSession()) { string sql = @"select ort.inspischeck,to_char(ort.inspchecktime,'yyyyMMdd HH24:mi') inspchecktime,ort.inspcheckid,ort.inspcheckname,ort.inspcheckremark inspcheckremark ,ort.isfumigation,to_char(ort.fumigationtime,'yyyyMMdd HH24:mi') fumigationtime,ort.fumigationid,ort.fumigationname from list_order ort where ort.isinvalid=0 and code='" + ordercode + "'"; return(db.QuerySignle(sql)); } }
/// <summary> /// 订单_获取最新的N条订阅条信息 /// </summary> /// <returns></returns> public static DataTable getSubscribeInfo_Order(string starttime, string endtime, string flag, int pagesize, int lastnum, string cusno, int userId) { try { using (DBSession db = new DBSession()) { string strWhere = " (subws.codetype=1 or subws.codetype=2) and "; if (!string.IsNullOrEmpty(cusno)) { strWhere += " subws.cusno='" + cusno + "' and "; } if (!string.IsNullOrEmpty(starttime)) { strWhere += " subws.SUBSTIME>to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and "; } if (!string.IsNullOrEmpty(endtime)) { strWhere += " subws.SUBSTIME<to_date('" + endtime + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and "; } if (flag == "已触发") { strWhere += " (subws.TRIGGERSTATUS=1 or subws.TRIGGERSTATUS=2) and"; } else if (flag == "未触发") { strWhere += " subws.TRIGGERSTATUS=0 and"; } if (userId > 0) { strWhere += " subws.userid=" + userId + " and"; } strWhere += " subws.isinvalid=0"; string sql = @"select lo.busiunitname,lo.busitype,lo.divideno,lo.repwayid,lo.contractno,lo.goodsnum,lo.goodsgw,to_char(lo.declstatus) as declstatus,to_char(lo.inspstatus) as inspstatus,lo.logisticsname, ws.cusno,ws.triggerstatus, ws.substype,ws.status as substatus ,'' as sublogstatus,ws.statusvalue,sb.name as businame,sr.name as repwayname from wechat_subscribe ws left join list_order lo on ws.cusno=lo.cusno left join cusdoc.sys_busitype sb on lo.busitype=sb.code left join cusdoc.sys_repway sr on lo.repwayid=sr.code where ws.cusno in ( select cusno from ( select rownum as rown ,tab.* from (select * from (select cusno,substime, ROW_NUMBER() OVER(partition by cusno order by substime desc) as rnum from wechat_subscribe subws where {0} and subws.cusno is not null) newws where newws.rnum=1 order by newws.substime desc ) tab where rownum<={1}) t1 where t1.rown>{2}) and (ws.codetype=1 or ws.codetype=2) and ws.isinvalid=0 order by ws.cusno,ws.substype,ws.statusvalue"; sql = string.Format(sql, strWhere, lastnum + pagesize, lastnum); return(db.QuerySignle(sql)); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Order:" + ex.Message); return(null); } }
/// <summary> /// 查询订阅详情 /// </summary> /// <param name="starttime"></param> /// <param name="endtime"></param> /// <param name="flag"></param> /// <returns></returns> public static DataTable getSubscribeInfo(string ordercodes) { using (DBSession db = new DBSession()) { string sql = @"select lo.busiunitname,lo.busitype,lo.cusno,lo.divideno,lo.repwayid,lo.contractno,lo.goodsnum,lo.goodsgw,lo.declstatus,lo.inspstatus, lo.logisticsname, ws.substype,ws.status,ws.statusvalue,sb.name as businame,sr.name as repwayname from wechat_subscribe ws left join list_order lo on ws.cusno=lo.code left join cusdoc.sys_busitype sb on lo.busitype=sb.code left join cusdoc.sys_repway sr on lo.repwayid=sr.code where ws.cusno in ({0}) and ws.isinvalid=0 order by ws.cusno,ws.substype,ws.statusvalue"; return(db.QuerySignle(string.Format(sql, ordercodes))); } }
/// <summary> /// 预制单_获取最新的N条订阅条信息 /// </summary> /// <returns></returns> public static DataTable getSubscribeInfo_Decl(string starttime, string endtime, string flag, int pagesize, int lastnum, string declarationcode, int userId) { try { using (DBSession db = new DBSession()) { string strWhere = " subws.codetype=3 and "; if (!string.IsNullOrEmpty(declarationcode)) { strWhere += " subws.declarationcode='" + declarationcode + "' and "; } if (!string.IsNullOrEmpty(starttime)) { strWhere += " subws.SUBSTIME>to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and "; } if (!string.IsNullOrEmpty(endtime)) { strWhere += " subws.SUBSTIME<to_date('" + endtime + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and "; } if (flag == "已触发") { strWhere += " (subws.TRIGGERSTATUS=1 or subws.TRIGGERSTATUS=2) and"; } else if (flag == "未触发") { strWhere += " subws.TRIGGERSTATUS=0 and"; } if (userId > 0) { strWhere += " subws.userid=" + userId + " and "; } strWhere += " subws.isinvalid=0"; string sql = @"select ld.declarationcode,ld.goodsnum,ld.goodsgw,ld.tradecode,ld.modifyflag,ld.customsstatus,ld.transname,ws.declarationcode, ws.cusno,ws.triggerstatus, ws.substype,ws.status as substatus ,ws.statusvalue,cbd.name as tradename from wechat_subscribe ws left join list_declaration ld on ws.declarationcode=ld.declarationcode left join cusdoc.base_decltradeway cbd on ld.tradecode=cbd.code where ws.declarationcode in ( select declarationcode from ( select rownum as rown ,tab.* from (select * from (select declarationcode,substime, ROW_NUMBER() OVER(partition by declarationcode order by substime desc) as rnum from wechat_subscribe subws where {0} and declarationcode is not null) newws where newws.rnum=1 order by newws.substime desc ) tab where rownum<={1}) t1 where t1.rown>{2} ) and ws.codetype=3 and ws.isinvalid=0 and ws.declarationcode is not null order by ws.declarationcode,ws.substype,ws.statusvalue"; return(db.QuerySignle(string.Format(sql, strWhere, lastnum + pagesize, lastnum))); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Decl:" + ex.Message); return(null); } }
public static DataTable getHsCodeInfoDetail(string id) { using (DBSession db = new DBSession(true)) { string sql = @"select (SELECT name from base_declproductunit where code = t.LEGALUNIT) as LEGALUNITNAME ,(SELECT name from base_declproductunit where code = t.SECONDUNIT) as SECONDUNITNAME ,HSCODE||EXTRACODE AS HSCODEEXTRACODE ,t.name,t.HSCODE,t.EXTRACODE,t.LEGALUNIT,t.SECONDUNIT,t.elements,t.FAVORABLERATE,t.VATRATE,t.EXPORTREBATRATE,t.GENERALRATE ,t.CUSTOMREGULATORY,t.INSPECTIONREGULATORY from BASE_COMMODITYHS t where t.id='{0}' and t.yearid=(select id from cusdoc.base_year where kind=11 and customarea=2300 and enabled=1)"; sql = string.Format(sql, id); return(db.QuerySignle(sql)); } }
public DataTable getSubsInfo(string code) { using (DBSession db = new DBSession()) { string sql = @"select lo.code,lo.submittime,lo.busiunitname,lo.busitype,lo.cusno,lo.divideno,lo.repwayid,lo.contractno,lo.goodsnum,lo.goodsgw, to_char(lo.ischeck) as ischeck,to_char(lo.checkpic) as checkpic,to_char(lo.declstatus) as declstatus,to_char(lo.inspstatus) as inspstatus, to_char(lo.lawflag) as lawflag,to_char(lo.inspischeck) as inspischeck,lo.logisticsstatus,lo.logisticsname,lo.customareacode, sb.name as busitypename,sr.name as repwayname from list_order lo left join cusdoc.sys_busitype sb on lo.busitype=sb.code left join cusdoc.sys_repway sr on lo.repwayid=sr.code where lo.isinvalid=0 and lo.code='{0}'"; return(db.QuerySignle(string.Format(sql, code))); } }
public static DataTable AssCon(string preinspcode) { using (DBSession db = new DBSession()) { DataTable dt = null; DataTable dt1 = new DataTable(); dt1 = db.QuerySignle("select ordercode from list_inspection where code='" + preinspcode + "'"); string ordercode = dt1.Rows[0][0].ToString(); DataTable dt2 = new DataTable(); dt2 = db.QuerySignle("select code from list_order where associateno=(select associateno from list_order where code='" + ordercode + "') and code!='" + ordercode + "'"); //关联订单不存在 if (dt2 == null) { return(dt); } if (dt2.Rows.Count <= 0) { return(dt); } string ordercode_con = dt2.Rows[0][0].ToString(); string tempsql = @"select li.CODE,li.ORDERCODE,li.INSPECTIONCODE,li.TRADEWAY,(select NAME from cusdoc.BASE_TRADEWAY bt WHERE enabled=1 and bt.code=li.TRADEWAY) as TRADEWAYNAME ,li.rependtime REPTIME,li.APPROVALCODE,li.MODIFYFLAG,li.CLEARANCECODE,li.INSPSTATUS ,ort.BUSITYPE,ort.BUSIUNITCODE,ort.BUSIUNITNAME,ort.CONTRACTNO,ort.GOODSNUM,ort.GOODSGW,ort.CUSNO,ort.SUBMITTIME from list_inspection li left join (select * from list_order where code='{0}') ort on li.ordercode = ort.code where li.isinvalid=0 and ort.isinvalid=0"; string sql = string.Format(tempsql, ordercode_con); return(db.QuerySignle(sql)); } }
public DataTable getSubsInfo(string declarationCode) { string sql = @"select det.code,det.modifyflag,det.CUSTOMSSTATUS ,lda.declarationcode,lda.BLNO,lda.CONSIGNEESHIPPER,lda.CONSIGNEESHIPPERNAME,lda.CONTRACTNO,lda.TRADEMETHOD,lda.TRANSNAME,lda.VOYAGENO,to_char(lda.reptime,'yyyy-mm-dd') reptime ,lda.GOODSNUM,lda.GOODSGW ,ort.busitype,ort.cusno,ort.code ordercode from list_declaration det left join list_order ort on det.ordercode = ort.code left join list_declaration_after lda on det.code=lda.code and lda.csid=1 left join (select ordercode from list_declaration ld where ld.isinvalid=0 and ld.STATUS!=130 and ld.STATUS!=110) a on det.ordercode=a.ordercode left join list_verification lv on lda.declarationcode=lv.declarationcode where (det.STATUS=130 or det.STATUS=110) and det.isinvalid=0 and ort.isinvalid=0 and a.ordercode is null and lda.declarationcode ='{0}'"; using (DBSession db = new DBSession()) { return(db.QuerySignle(string.Format(sql, declarationCode))); } }
/// <summary> /// 获取触发状态(0已订阅,1已触发,2已推送) /// </summary> /// <param name="cusno"></param> /// <returns></returns> public static DataTable GetTriggerstatus(string cusno, string checkedStatus, string type, string declarationcode, int userid, string ordercode) { using (DBSession db = new DBSession()) { string sql; if (type.Equals("报关状态")) { sql = "select triggerstatus from wechat_subscribe where ordercode='" + ordercode + "' and status = '" + checkedStatus + "' and substype = '" + type + "' and declarationcode = '" + declarationcode + "' and userid=" + userid; } else { sql = "select triggerstatus from wechat_subscribe where ordercode='" + ordercode + "' and status = '" + checkedStatus + "' and substype = '" + type + "' and userid=" + userid; } return(db.QuerySignle(sql)); } }
// public static string Pass(string ordercode) // { // string userid = "763"; string realname = "昆山吉时报关有限公司"; // using (DBSession db = new DBSession()) // { // string sql = "select to_char(sitepasstime,'yyyy/mm/dd hh24:mi:ss') as sitepasstime from list_order where code='" + ordercode + "'"; // DataTable dt = db.QuerySignle(sql); // string curtime = dt.Rows[0]["SITEPASSTIME"].ToString(); // if (curtime != "") // { // return curtime.Left(curtime.Length - 3).Replace("/", ""); // } // curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); // sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=160 where code='{0}'"; // sql = string.Format(sql, ordercode, userid, realname, curtime); // int i = db.ExecuteSignle(sql); // if (i > 0) // { // MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient(); // msc.redis_OrderStatusLog(ordercode); // //add 20180115 保存操作记录list_times //// sql = @"insert into list_times(id,code,userid,realname,times,type,ispause) //// values(list_times_id.nextval,'" + ordercode + "','" + userid + "','" + realname + "',sysdate,'0',0)"; //// db.ExecuteSignle(sql); // //add 20180119 保存历史记录 // sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE // ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) // values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' // ,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')"; // sql = string.Format(sql, ordercode, userid, curtime, realname, ordercode, "SITEPASSTIME", "报关放行"); // db.ExecuteSignle(sql); // return curtime.Left(curtime.Length - 3).Replace("/", ""); // } // else // { // return ""; // } // /*string curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); // string sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss') where code='{0}'"; // sql = string.Format(sql, ordercode, "763", "昆山吉时报关有限公司", curtime); // int i = db.ExecuteSignle(sql); // if (i > 0) // { // return curtime.Left(curtime.Length - 3).Replace("/", ""); // } // else // { // return ""; // }*/ // } // } public static string Passall(string ordercode, WGUserEn user) { string userid = user.GwyUserID.ToString(); string username = user.GwyUserCode; string realname = user.GwyUserName; //string userid = "763"; string username = "******"; string realname = "昆山吉时报关有限公司"; using (DBSession db = new DBSession()) { string sql = "select to_char(sitepasstime,'yyyy/mm/dd hh24:mi:ss') as sitepasstime from list_order where code='" + ordercode + "'"; DataTable dt = db.QuerySignle(sql); string curtime = dt.Rows[0]["SITEPASSTIME"].ToString(); if (curtime != "") { return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'',\"ISEXISTS\":'Y'}"); } curtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); sql = "update list_order set sitepassuserid='{1}',sitepassusername='******',sitepasstime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=160 where code='{0}' and declstatus<=160"; sql = string.Format(sql, ordercode, userid, realname, curtime); int i = db.ExecuteSignle(sql); if (i > 0) { MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient(); msc.redis_OrderStatusLog(ordercode); //add 20180119 保存历史记录 sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' ,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')"; sql = string.Format(sql, ordercode, userid, curtime, realname, ordercode, "SITEPASSTIME", "报关放行"); db.ExecuteSignle(sql); return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'S',\"ISEXISTS\":'N'}"); } else { return("{\"ORDERCODE\":'" + ordercode + "',\"CURTIME\":'" + curtime.Left(curtime.Length - 3).Replace("/", "") + "',\"FLAG\":'E',\"ISEXISTS\":'N'}"); } } }
/// <summary> /// /// /// <summary> /// 预制单_获取最新的N条订阅条信息 /// </summary> /// <returns></returns> public static DataTable getNewSubscribeInfo_Decl(int userId) { try { using (DBSession db = new DBSession()) { // string strWhere = " ws.isinvalid=0 and lo.isinvalid=0 and ld.isinvalid=0 and ws.codetype=3 and ws.TRIGGERSTATUS=0 and ws.userid=" + userId; // string sql = @"with newt // as( // select * from // (select tab.*,rownum as rown from // (select ld.goodsnum,ld.goodsgw,ld.tradecode,ld.modifyflag,ld.customsstatus,ld.transname, // ws.declarationcode, ws.cusno,ws.triggerstatus, ws.substype,ws.status ,ws.statusvalue,ws.pushtime,ws.id,ws.ordercode // ws.substime,'' as sum from wechat_subscribe ws // left join list_order lo on ws.cusno=lo.cusno // left join list_declaration ld on ld.declarationcode=ws.declarationcode // where {0} order by ws.substime desc // )tab ) where rown>{1} and rown<={2}) // select newt.*,cbd.name as tradename from newt // left join cusdoc.base_decltradeway cbd on newt.tradecode=cbd.code // order by newt.substime desc"; string sql = @"select ld.goodsnum,ld.goodsgw,ld.tradecode,ld.modifyflag,ld.customsstatus,ld.transname, ws.declarationcode, ws.cusno,ws.triggerstatus, ws.substype,ws.status ,ws.statusvalue,ws.pushtime,ws.id,ws.ordercode, ws.substime,'' as sum,cbd.name as tradename from wechat_subscribe ws left join list_declaration ld on ld.declarationcode=ws.declarationcode left join cusdoc.base_decltradeway cbd on ld.tradecode=cbd.code where ws.isinvalid=0 and ld.isinvalid=0 and ws.codetype=3 and ws.TRIGGERSTATUS=0 and ws.userid=1124 order by ws.substime desc"; sql = string.Format(sql); return(db.QuerySignle(sql)); } } catch (Exception ex) { LogHelper.Write("SubscribeModel_getSubscribeInfo_Decl:" + ex.Message); return(null); } }
public static DataTable getHsCodeInfo(string hscode, string commodityname, int start, int itemsPerLoad) { using (DBSession db = new DBSession(true)) { string where = ""; if (!string.IsNullOrEmpty(hscode)) { where += " and t.HSCODE||t.EXTRACODE like '%" + hscode + "%'"; } if (!string.IsNullOrEmpty(commodityname)) { where += " and t.NAME like '%" + commodityname + "%'"; } string tempsql = @"select (SELECT name from base_declproductunit where code = t.LEGALUNIT) as LEGALUNITNAME ,(SELECT name from base_declproductunit where code = t.SECONDUNIT) as SECONDUNITNAME ,HSCODE||EXTRACODE AS HSCODEEXTRACODE ,t.ID,t.name,t.HSCODE,t.EXTRACODE,t.LEGALUNIT,t.SECONDUNIT,t.elements,t.FAVORABLERATE from BASE_COMMODITYHS t where 1=1 and t.yearid=(select id from cusdoc.base_year where kind=11 and customarea=2300 and enabled=1)" + where; string pageSql = @"SELECT * FROM ( SELECT tt.*, ROWNUM AS rowno FROM ({0} ORDER BY {1} {2}) tt WHERE ROWNUM <= {4}) table_alias WHERE table_alias.rowno >= {3}"; string sql = string.Format(pageSql, tempsql, "id", "desc", start + 1, start + itemsPerLoad); return(db.QuerySignle(sql)); } }
public static string check_audit_save(string ordercode, string checktime, string checkname, string checkid, string checkremark , string auditflagtime, string auditflagname, string auditflagid, string auditcontent) { MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient(); System.Uri Uri = new Uri("ftp://" + ConfigurationManager.AppSettings["FTPServer"] + ":" + ConfigurationManager.AppSettings["FTPPortNO"]); string UserName = ConfigurationManager.AppSettings["FTPUserName"]; string Password = ConfigurationManager.AppSettings["FTPPassword"]; FtpHelper ftp = new FtpHelper(Uri, UserName, Password); string username = "******"; string userid = "763"; string realname = "昆山吉时报关有限公司"; string sql = ""; string resultmsg = "[]"; string feoremark = "";//记录是否需要调用费用接口 List <string> delfile = new List <string>(); DBSession db = new DBSession(); sql = @"select code,entrusttype,declstatus,inspstatus,ischeck,auditflag,checkpic from list_order lo where lo.code='" + ordercode + "'"; DataTable dt_order = db.QuerySignle(sql); string db_ischeck = dt_order.Rows[0]["ISCHECK"].ToString(); string db_auditflag = dt_order.Rows[0]["AUDITFLAG"].ToString(); string db_checkpic = dt_order.Rows[0]["CHECKPIC"].ToString(); int ischeck = 0; int auditflag = 0; int checkpic = db_checkpic == "1" ? 1 : 0; DataTable dt = db.QuerySignle("select * from list_attachment where ordercode='" + ordercode + "' and filetype='67'"); foreach (DataRow dr in dt.Rows) { delfile.Add(dr["FILENAME"] + ""); } db.Dispose(); try { db = new DBSession(); db.BeginTransaction(); if (checktime != "") { ischeck = 1; if (db_ischeck != "1") { feoremark += "list_order.ischeck查验标志为1"; sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' ,'" + ordercode + "','" + userid + "','1','" + realname + "','" + ordercode + "','ISCHECK','报关查验'" + ")"; db.QuerySignle(sql); } sql = @"update list_order set ischeck=1,declcheckid='{1}',declcheckname='{2}',declchecktime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),checkremark='{4}' where code='{0}'"; sql = string.Format(sql, ordercode, checkid, checkname, checktime, checkremark); db.QuerySignle(sql); } else { checkpic = 0; if (db_ischeck == "1") { feoremark += "list_order.ischeck查验标志为0"; sql = @"update list_order set ischeck=0,declcheckid=null,declcheckname=null,declchecktime=null,checkpic=0,checkremark='' where code='" + ordercode + "'"; db.QuerySignle(sql); sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' ,'" + ordercode + "','" + userid + "','0','" + realname + "','" + ordercode + "','ISCHECK','报关查验'" + ")"; db.QuerySignle(sql); } sql = "delete LIST_ATTACHMENT where ordercode='" + ordercode + "' and filetype='67'"; db.QuerySignle(sql); } if (auditflagtime != "") { auditflag = 1; if (db_auditflag != "1") { feoremark += "list_order.auditflag稽核标志修改为1"; sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' ,'" + ordercode + "','" + userid + "','1','" + realname + "','" + ordercode + "','AUDITFLAG','稽核标志'" + ")"; db.QuerySignle(sql); } sql = @"update list_order set auditflag=1,auditflagid='{1}',auditflagname='{2}',auditflagtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),auditcontent='{4}' where code='{0}'"; sql = string.Format(sql, ordercode, auditflagid, auditflagname, auditflagtime, auditcontent); db.QuerySignle(sql); } else { if (db_auditflag == "1") { feoremark += "list_order.auditflag稽核标志修改为0"; sql = @"update list_order set auditflag=0,auditflagid=null,auditflagname=null,auditflagtime=null,auditcontent='' where code='" + ordercode + "'"; db.QuerySignle(sql); sql = @"insert into list_updatehistory(id,UPDATETIME,TYPE ,ORDERCODE,USERID,NEWFIELD,NAME,CODE,FIELD,FIELDNAME) values(LIST_UPDATEHISTORY_ID.nextval,sysdate,'1' ,'" + ordercode + "','" + userid + "','0','" + realname + "','" + ordercode + "','AUDITFLAG','稽核标志'" + ")"; db.QuerySignle(sql); } } db.Commit(); foreach (string item in delfile)//提交之后删除文件 { ftp.DeleteFile(item); } resultmsg = "[{\"ISCHECK\":" + ischeck + ",\"DECLCHECKTIME\":'" + checktime + "',\"CHECKPIC\":" + checkpic + ",\"AUDITFLAG\":" + auditflag + ",\"AUDITFLAGTIME\":'" + auditflagtime + "'}]"; } catch (Exception ex) { db.Rollback(); } finally { db.Dispose(); } //============================================================================================================费用接口 if (feoremark != "") { //add 20180115 费用异常接口 if (dt_order.Rows[0]["entrusttype"].ToString() == "03") { if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160 && Convert.ToInt32(dt_order.Rows[0]["inspstatus"].ToString()) >= 120) { msc.FinanceExceptionOrder(ordercode, username, feoremark); } } else { if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160) { msc.FinanceExceptionOrder(ordercode, username, feoremark); } } } //============================================================================================================ return(resultmsg); }
public static DataSet getSiteDeclareInfo(string siteapplytime_s, string siteapplytime_e, string declcode, string customareacode, string ispass, string ischeck, string busitype , string modifyflag, string auditflag, string busiunit, string ordercode, string cusno, string divideno, string contractno , string submittime_s, string submittime_e, string sitepasstime_s, string sitepasstime_e , int start, int itemsPerLoad, string customercode)// { DataSet ds = new DataSet(); using (DBSession db = new DBSession()) { string where = ""; string where_dec = ""; if (!string.IsNullOrEmpty(siteapplytime_s)) { where += " and ort.siteapplytime>=to_date('" + siteapplytime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(siteapplytime_e)) { where += " and ort.siteapplytime<=to_date('" + siteapplytime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(declcode)) { where_dec += " and det.declarationcode like '%" + declcode + "%'"; } //where_dec if (!string.IsNullOrEmpty(customareacode)) { where += " and ort.customareacode like '%" + customareacode + "%'"; } if (!string.IsNullOrEmpty(ispass)) { if (ispass == "放行") { where += " and ort.declstatus=" + (int)DeclStatusEnum.SitePass; } if (ispass == "未放行") { where += " and ort.declstatus<" + (int)DeclStatusEnum.SitePass; } } if (!string.IsNullOrEmpty(ischeck)) { if (ischeck == "查验") { where += " and ort.ischeck=1"; } if (ischeck == "未查验") { where += " and ort.ischeck=0"; } } if (!string.IsNullOrEmpty(busitype)) { where += " and ort.busitype in (" + busitype + ")"; } if (!string.IsNullOrEmpty(modifyflag)) { where_dec += " and det.modifyflag='" + modifyflag + "'"; } //where_dec if (!string.IsNullOrEmpty(auditflag)) { where += " and ort.auditflag=1"; } if (!string.IsNullOrEmpty(busiunit)) { where += " and (ort.BUSIUNITCODE like '%" + busiunit + "%' or ort.BUSIUNITNAME like '%" + busiunit + "%')"; } if (!string.IsNullOrEmpty(ordercode)) { where += " and ort.code like '%" + ordercode + "%'"; } if (!string.IsNullOrEmpty(cusno)) { where += " and ort.cusno like '%" + cusno + "%'"; } if (!string.IsNullOrEmpty(divideno)) { where += " and ort.divideno like '%" + divideno + "%'"; } if (!string.IsNullOrEmpty(contractno)) { where += " and ort.CONTRACTNO like '%" + contractno + "%'"; } if (!string.IsNullOrEmpty(submittime_s)) { where += " and ort.submittime>=to_date('" + submittime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(submittime_e)) { where += " and ort.submittime<=to_date('" + submittime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(sitepasstime_s)) { where += " and ort.sitepasstime>=to_date('" + sitepasstime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(sitepasstime_e)) { where += " and ort.sitepasstime<=to_date('" + sitepasstime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } where += " and ort.receiverunitcode='" + customercode + "'"; string tempsql = @"select ort.busiunitname,ort.busitype,ort.code ,ort.totalno,ort.divideno,ort.secondladingbillno,ort.landladingno,ort.associatepedeclno,ort.repwayid ,(select name from cusdoc.sys_repway where enabled=1 and code=ort.repwayid and rownum=1) repwayname,ort.cusno ,to_char(ort.siteapplytime,'yyyyMMdd HH24:mi') siteapplytime,ort.goodsnum,ort.goodsgw,ort.contractno ,to_char(ort.declchecktime,'yyyyMMdd HH24:mi') declchecktime,ort.ischeck,ort.associateno ,to_char(ort.sitepasstime,'yyyyMMdd HH24:mi') sitepasstime,ort.checkpic,ort.correspondno ,ort.checkremark declcheckremark,ort.auditflag,ort.auditcontent from list_order ort where ort.entrusttype in('01','03') and ort.isinvalid=0" + where //+ @"and exists (select 1 from list_declaration det where det.isinvalid=0" + where_dec + ")"; + @"and ort.code in (select ordercode from list_declaration det where det.isinvalid=0" + where_dec + ")"; string pageSql = @"SELECT * FROM ( SELECT tt.*, ROWNUM AS rowno FROM ({0} ORDER BY {1} {2}) tt WHERE ROWNUM <= {4}) table_alias WHERE table_alias.rowno >= {3}"; string sql = string.Format(pageSql, tempsql, "ort.submittime", "desc", start + 1, start + itemsPerLoad); DataTable dt = db.QuerySignle(sql); ds.Tables.Add(dt); string sql_count = @"select count(1) sum from (" + tempsql + ") a"; DataTable dt_count = db.QuerySignle(sql_count); ds.Tables.Add(dt_count); } return(ds); }
public static DataSet getInspectionInfo(string reptime_s, string reptime_e, string inspcode, string modifyflag, string busitype, string ischeck , string ispass, string lawflag, string isneedclearance, string busiunit, string contractno, string ordercode, string cusno, string divideno , string customareacode, string approvalcode, string submittime_s, string submittime_e, string sitepasstime_s, string sitepasstime_e , int start, int itemsPerLoad, string customercode)// { DataSet ds = new DataSet(); using (DBSession db = new DBSession()) { string where = ""; if (!string.IsNullOrEmpty(reptime_s)) { where += " and li.rependtime>=to_date('" + reptime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(reptime_e)) { where += " and li.rependtime<=to_date('" + reptime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(inspcode)) { where += " and li.inspectioncode like '%" + inspcode + "%'"; } if (!string.IsNullOrEmpty(modifyflag)) { where += " and li.modifyflag='" + modifyflag + "'"; } if (!string.IsNullOrEmpty(busitype)) { where += " and ort.busitype in (" + busitype + ")"; } if (!string.IsNullOrEmpty(ischeck)) { if (ispass == "查验") { where += " and ort.ischeck=1"; } if (ispass == "未查验") { where += " and ort.ischeck=0"; } } if (!string.IsNullOrEmpty(ispass)) { if (ispass == "放行") { where += " and ort.inspstatus=" + (int)DeclStatusEnum.SitePass; } if (ispass == "未放行") { where += " and ort.inspstatus<" + (int)DeclStatusEnum.SitePass; } } if (!string.IsNullOrEmpty(lawflag)) { where += " and ort.lawflag=1"; } if (!string.IsNullOrEmpty(isneedclearance)) { where += " and ort.isneedclearance=1"; } if (!string.IsNullOrEmpty(busiunit)) { where += " and (ort.BUSIUNITCODE like '%" + busiunit + "%' or ort.BUSIUNITNAME like '%" + busiunit + "%')"; } if (!string.IsNullOrEmpty(contractno)) { where += " and ort.CONTRACTNO like '%" + contractno + "%'"; } if (!string.IsNullOrEmpty(ordercode)) { where += " and ort.code like '%" + ordercode + "%'"; } if (!string.IsNullOrEmpty(cusno)) { where += " and ort.cusno like '%" + cusno + "%'"; } if (!string.IsNullOrEmpty(divideno)) { where += " and ort.divideno like '%" + divideno + "%'"; } if (!string.IsNullOrEmpty(customareacode)) { where += " and ort.customareacode like '%" + customareacode + "%'"; } if (!string.IsNullOrEmpty(submittime_s)) { where += " and ort.submittime>=to_date('" + submittime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(submittime_e)) { where += " and ort.submittime<=to_date('" + submittime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(sitepasstime_s)) { where += " and ort.sitepasstime>=to_date('" + sitepasstime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "; } if (!string.IsNullOrEmpty(sitepasstime_e)) { where += " and ort.sitepasstime<=to_date('" + sitepasstime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "; } where += " and ort.receiverunitcode='" + customercode + "'"; string tempsql = @"select li.CODE,li.ORDERCODE,li.INSPECTIONCODE,li.TRADEWAY,(select NAME from cusdoc.BASE_TRADEWAY bt WHERE enabled=1 and bt.code=li.TRADEWAY) as TRADEWAYNAME ,to_char(li.rependtime,'yyyy-mm-dd') REPTIME,li.APPROVALCODE,li.MODIFYFLAG,li.CLEARANCECODE,li.INSPSTATUS ,ort.BUSITYPE,ort.BUSIUNITCODE,ort.BUSIUNITNAME,ort.CONTRACTNO,ort.GOODSNUM,ort.GOODSGW,ort.CUSNO,ort.SUBMITTIME from list_inspection li left join list_order ort on li.ordercode = ort.code where li.isinvalid=0 and ort.isinvalid=0" + where; string pageSql = @"SELECT * FROM ( SELECT tt.*, ROWNUM AS rowno FROM ({0} ORDER BY {1} {2}) tt WHERE ROWNUM <= {4}) table_alias WHERE table_alias.rowno >= {3}"; string sql = string.Format(pageSql, tempsql, "ort.submittime", "desc", start + 1, start + itemsPerLoad); DataTable dt = db.QuerySignle(sql); ds.Tables.Add(dt); string sql_count = @"select count(1) sum from (" + tempsql + ") a"; DataTable dt_count = db.QuerySignle(sql_count); ds.Tables.Add(dt_count); } return(ds); }