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);
}
