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="name"></param>
/// <param name="pwd"></param>
/// <param name="customer"></param>
/// <returns></returns>
public static WGUserEn Login(string name, string pwd, string customer, string openid, string nickname)
{
using (DBSession db = new DBSession())
{
pwd = pwd.Trim2().ToSHA1();
string sql = @"select su.id as GWYUSERID,su.name GWYUSERCODE,su.realname as GWYUSERNAME,csc.code as CUSTOMERCODE,csc.hscode,csc.iscompany,csc.iscustomer,csc.isreceiver
from sys_user su left join cusdoc.sys_customer csc on su.customerid=csc.id where su.name='{0}' and su.password='******' and csc.code='{2}' and su.enabled=1";
sql = string.Format(sql, name.Trim2(), pwd, customer.Trim2().ToUpper());
WGUserEn user = db.QuerySignleEntity <WGUserEn>(sql);
if (user != null)
{
user.WCOpenID = openid;
user.WCNickName = nickname;
}
return(user);
}
}
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 static bool saveModifyFlag(string preinspcode, int modifyflag, WGUserEn user)
{
bool bf = false;
try
{
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 = "";
if (modifyflag == 1)
{
sql = @",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 2)
{
sql = @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 3)
{
sql = @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')";
}
sql = @"update list_inspection set modifyflag=" + modifyflag + sql + " where code='{0}'";
sql = string.Format(sql, preinspcode, userid, realname, DateTime.Now);
db.ExecuteSignle(sql);
bf = true;
}
}
catch (Exception ex)
{
LogHelper.Write("saveModifyFlag_sql:" + ex.Message + "——code:" + preinspcode + " modifyflag:" + modifyflag);
}
return(bf);
}
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 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 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);
}
}
/// <summary>
/// 新增订阅信息
/// </summary>
/// <param name="type"></param>
/// <param name="status"></param>
/// <param name="cusno"></param>
/// <param name="declarationcode"></param>
/// <param name="cusno"></param>
/// <param name="userid"></param>
/// <param name="username"></param>
/// <param name="openid"></param>
/// <param name="codetype"></param>
/// <returns></returns>
public static bool insertSubscribe(string type, string[] status, string cusno, string declarationcode, int userid, string username, string openid, string codetype, string ordercode)
{
try
{
string sql = @"insert into wechat_subscribe(id,cusno,declarationcode,userid,username,substime,substype,status,openid,statusvalue,codetype,ordercode)
values(wechat_subscribe_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'{4}','{5}','{6}','{7}','{8}','{9}')";
List <string> sqls = new List <string>();
for (int i = 0; i < status.Length; i++)
{
string statusvalue = SwitchHelper.switchValue(type, status[i]);
sqls.Add(string.Format(sql, cusno, declarationcode, userid, username, type, status[i], openid, statusvalue, codetype, ordercode));
}
using (DBSession db = new DBSession())
{
return(db.ExecuteBatch(sqls) > 0 ? true : false);
}
}
catch (Exception ex)
{
LogHelper.Write("SubscribeModel_insertSubscribe:" + ex.Message + "——code:" + cusno + declarationcode);
return(false);
}
}
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 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 bool saveModifyFlag(string predelcode, int modifyflag, WGUserEn user)
{
bool bf = false;
try
{
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 = "";
sql = @"select code,ordercode,declarationcode from list_declaration ld where ld.code='" + predelcode + "'";
DataTable dt_decl = db.QuerySignle(sql);
string ordercode = dt_decl.Rows[0]["ordercode"].ToString();
if (modifyflag == 1)//删单1
{
sql = @"select ld.code,ld.ordercode from list_declaration ld inner join config_filesplit cfs on ld.busiunitcode=cfs.busiunitcode and cfs.filetype='53' and ld.code='" + predelcode + "'";
DataTable dt = db.QuerySignle(sql);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(ordercode))
{
sql = @"update list_attachmentdetail t1 set t1.filetypeid='162' where t1.ordercode='" + ordercode + "' and t1.filetypeid='53'";
db.ExecuteSignle(sql);
}
}
}
}
if (modifyflag == 2)//改单2
{
DateTime time = DateTime.Now;
sql = @"update list_declaration_after set dataconfirm='1',dataconfirmusertime=to_date('" + time + "','yyyy-MM-dd HH24:mi:ss') where code='" + predelcode + "' and xzlb like '报关单%'";
db.ExecuteSignle(sql);
}
//改单完成3
//修改删改单标志
sql = @"update list_declaration set modifyflag=" + modifyflag;
//if (modifyflag == 1) { sql += ",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
//if (modifyflag == 2) { sql += ",modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
//if (modifyflag == 3) { sql += ",modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')"; }
if (modifyflag == 1)
{
sql += @",delorderuserid='{1}',delorderusername='******',delordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 2)
{
sql += @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid='{1}',modorderusername='******',modordertime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')
,modfinishuserid=null,modfinishusername=null,modfinishtime=null";
}
if (modifyflag == 3)
{
sql += @",delorderuserid=null,delorderusername=null,delordertime=null
,modorderuserid=null,modorderusername=null,modordertime=null
,modfinishuserid='{1}',modfinishusername='******',modfinishtime=to_date('{3}','yyyy-MM-dd HH24:mi:ss')";
}
sql += " where code='{0}'";
sql = string.Format(sql, predelcode, userid, realname, DateTime.Now);
db.ExecuteSignle(sql);
//修改订单的报关状态
sql = "select customsstatus from list_declaration where ordercode='" + ordercode + "' and isinvalid=0 and modifyflag<>1";
bool flag = true;
DataTable dt_order_status = db.QuerySignle(sql);
if (dt_order_status != null)
{
if (dt_order_status.Rows.Count > 0)
{
foreach (DataRow dr in dt_order_status.Rows)
{
if (dr["customsstatus"].ToString2() == "" || (dr["customsstatus"].ToString2() != "已结关" && dr["customsstatus"].ToString2() != "已放行"))
{
flag = false;
break;
}
}
}
}
if (flag)
{
sql = "update list_order set declstatus=160,sitepassusername='******',sitepasstime=sysdate,siteapplyuserid=-2 where code='" + ordercode + "' and declstatus<=160";
db.ExecuteSignle(sql);
}
//---------------------------------------------------------------------------------------------------------------
//保存操作记录list_times
//sql = @"insert into list_times(id,code,userid,realname,times,type,ispause)
//values(list_times_id.nextval,'" + predelcode + "','" + userid + "','" + realname + "',sysdate,'1'," + modifyflag + ")";
//db.ExecuteSignle(sql);
//调用缓存接口redis_DeclarationLog
MethodSvc.MethodServiceClient msc = new MethodSvc.MethodServiceClient();
msc.redis_DeclarationLog(ordercode, predelcode, dt_decl.Rows[0]["declarationcode"].ToString(), "", "0");
sql = @"select code,entrusttype,declstatus,inspstatus from list_order lo where lo.code='" + ordercode + "'";
DataTable dt_order = db.QuerySignle(sql);
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)
{
//add 20180115 费用异常接口
msc.FinanceExceptionOrder(ordercode, username, "list_declaration.modifyflag修改为" + modifyflag.ToString());
}
}
else
{
if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160)
{
//add 20180115 费用异常接口
msc.FinanceExceptionOrder(dt_decl.Rows[0]["ordercode"].ToString(), username, "list_declaration.modifyflag修改为" + modifyflag.ToString());
}
}
bf = true;
}
}
catch (Exception ex)
{
LogHelper.Write("saveModifyFlag_sql:" + ex.Message + "——code:" + predelcode + " modifyflag:" + modifyflag);
}
return(bf);
}
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);
}
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);
}
/// <summary>
/// 预制单_获取最新的N条订阅条信息
/// </summary>
/// <returns></returns>
public static DataTable getNewSubscribeInfo_Decl(string subscribestart, string subscribeend, string declarationcode, string istrigger, string busitype, string busiunit,
string ordercode, string cusno, string contract, string submitstart, string submitend, int pagesize, int lastnum, int userId, out string sum)
{
sum = "0";
try
{
using (DBSession db = new DBSession())
{
string strWhere = " ws.codetype=3 and ";
if (!string.IsNullOrEmpty(subscribestart))
{
strWhere += "ws.substime > to_date('" + subscribestart + "','yyyy-mm-dd hh24:mi:ss') and ";
}
if (!string.IsNullOrEmpty(subscribeend))
{
strWhere += "ws.substime < to_date('" + subscribeend + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and ";
}
if (!string.IsNullOrEmpty(declarationcode))
{
strWhere += " ld.declarationcode like '%" + declarationcode + "%' and ";
}
int TRIGGERSTATUS = 0;
if (istrigger == "已触发")
{
TRIGGERSTATUS = 1;
}
else if (istrigger == "已推送")
{
TRIGGERSTATUS = 2;
}
strWhere += " ws.TRIGGERSTATUS=" + TRIGGERSTATUS + " and ";
if (!string.IsNullOrEmpty(busitype))
{
strWhere += " lo.busitype in (" + busiunit + ") and ";
}
if (!string.IsNullOrEmpty(busiunit))
{
strWhere += " (lo.busiunitcode like '%" + busiunit + "%' or lo.busiunitname like '%" + busiunit + "%') and ";
}
if (!string.IsNullOrEmpty(ordercode))
{
strWhere += " lo.code like '%" + ordercode + "%' and ";
}
if (!string.IsNullOrEmpty(cusno))
{
strWhere += " lo.cusno like '%" + cusno + "%' and ";
}
if (!string.IsNullOrEmpty(contract))
{
strWhere += " lo.contractno like '%" + contract + "%' and ";
}
if (!string.IsNullOrEmpty(submitstart))
{
strWhere += "lo.submittime > to_date('" + submitstart + "','yyyy-mm-dd hh24:mi:ss') and ";
}
if (!string.IsNullOrEmpty(submitend))
{
strWhere += "lo.submittime < to_date('" + submitend + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and ";
}
if (userId > 0)
{
strWhere += " ws.userid=" + userId + " and ";
}
strWhere += " ws.isinvalid=0 and lo.isinvalid=0 and ld.isinvalid=0";
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.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";
sql = string.Format(sql, strWhere, lastnum, lastnum + pagesize);
string sumSql = @"select count(1) 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}";
DataTable dt = db.QuerySignle(string.Format(sumSql, strWhere));
if (dt != null && dt.Rows.Count > 0)
{
sum = dt.Rows[0][0].ToString2();
}
return(db.QuerySignle(sql));
}
}
catch (Exception ex)
{
LogHelper.Write("SubscribeModel_getSubscribeInfo_Decl:" + ex.Message);
return(null);
}
}
/// <summary>
/// 查询业务信息
/// </summary>
/// <param name="declstatus"></param>
/// <param name="inspstatus"></param>
/// <param name="inout"></param>
/// <param name="busitype"></param>
/// <param name="customs"></param>
/// <param name="sitedeclare"></param>
/// <param name="logisticsstatus"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
public DataTable getOrder(string submittime_s, string submittime_e, string declarationcode, string customarea, string ispass, string ischeck, string busitype,
string modifyflag, string auditflag, string busiunit, string ordercode, string cusno, string divideno, string contractno, string passtime_s, string passtime_e,
int itemsperLoad, int lastIndex, string customerCode, string hscode, out string sum)
{
DataTable dt = new DataTable();
sum = "0";
try
{
using (DBSession db = new DBSession())
{
string sql = @"with newtab as
( select * from ( select rownum as rown ,tab.* from
(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,'' as sum
from list_order lo left join list_declaration ld on lo.code=ld.ordercode {0} order by lo.submittime desc) tab
where rownum<={1}) t1
where t1.rown>{2})
select nt.*,sb.name as busitypename,sr.name as repwayname from newtab nt
left join cusdoc.sys_busitype sb on nt.busitype=sb.code
left join cusdoc.sys_repway sr on nt.repwayid=sr.code";
string strWhere = " where lo.submittime is not null and lo.cusno is not null and lo.isinvalid=0 and ld.isinvalid=0";
if (!string.IsNullOrEmpty(busiunit))
{
strWhere += " and (lo.BUSIUNITCODE like '%" + busiunit + "%' or lo.BUSIUNITNAME like '%" + busiunit + "%')";
}
if (!string.IsNullOrEmpty(submittime_s))
{
strWhere += " and lo.submittime>=to_date('" + submittime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(submittime_e))
{
strWhere += " and lo.submittime<=to_date('" + submittime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(declarationcode))
{
strWhere += " and ld.declarationcode like '%" + declarationcode + "%'";
}
if (!string.IsNullOrEmpty(customarea))
{
strWhere += " and lo.customareacode like '%" + customarea + "%'";
}
if (!string.IsNullOrEmpty(ispass))
{
if (ispass == "放行")
{
strWhere += " and lo.declstatus=" + (int)DeclStatusEnum.SitePass;
}
if (ispass == "未放行")
{
strWhere += " and lo.declstatus<" + (int)DeclStatusEnum.SitePass;
}
}
if (!string.IsNullOrEmpty(ischeck))
{
if (ischeck == "查验")
{
strWhere += " and lo.ischeck=1";
}
if (ischeck == "未查验")
{
strWhere += " and lo.ischeck=0";
}
}
if (!string.IsNullOrEmpty(busitype))
{
strWhere += " and lo.busitype in (" + busitype + ")";
}
if (!string.IsNullOrEmpty(modifyflag))
{
if (modifyflag == "删单")
{
strWhere += " and ld.modifyflag=1";
}
if (modifyflag == "改单")
{
strWhere += " and ld.modifyflag=2";
}
if (modifyflag == "改单完成")
{
strWhere += " and ld.modifyflag=3";
}
}
if (!string.IsNullOrEmpty(auditflag))
{
strWhere += " and lo.auditflag=1";
}
if (!string.IsNullOrEmpty(ordercode))
{
strWhere += " and lo.code like '%" + ordercode + "%'";
}
if (!string.IsNullOrEmpty(cusno))
{
strWhere += " and lo.cusno like '%" + cusno + "%'";
}
if (!string.IsNullOrEmpty(divideno))
{
strWhere += " and lo.divideno like '%" + divideno + "%'";
}
if (!string.IsNullOrEmpty(contractno))
{
strWhere += " and lo.CONTRACTNO like '%" + contractno + "%'";
}
if (!string.IsNullOrEmpty(passtime_s))
{
strWhere += " and lo.sitepasstime>=to_date('" + passtime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(passtime_e))
{
strWhere += " and lo.sitepasstime<=to_date('" + passtime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
}
//当前登录用户权限控制
if (!string.IsNullOrEmpty(customerCode) && !string.IsNullOrEmpty(hscode))
{
strWhere += " and (lo.busiunitcode='" + hscode + "' or lo.customercode='" + customerCode + "')";
}
else if (!string.IsNullOrEmpty(customerCode))
{
strWhere += " and lo.customercode='" + customerCode + "'";
}
else if (!string.IsNullOrEmpty(hscode))
{
strWhere += " and lo.busiunitcode='" + hscode + "'";
}
string sumSql = @"select count(1) from list_order lo left join list_declaration ld on lo.code=ld.ordercode {0}";
DataTable sdt = db.QuerySignle(string.Format(sumSql, strWhere));
if (sdt != null && sdt.Rows.Count > 0)
{
sum = sdt.Rows[0][0].ToString2();
}
sql = string.Format(sql, strWhere, lastIndex + itemsperLoad, lastIndex);
dt = db.QuerySignle(sql);
}
}
catch (Exception ex)
{
LogHelper.Write("ListOrderModel_getOrder异常：" + ex.Message);
}
return(dt);
}
public static string SaveFile(string mediaIds, string ordercode)
{
string str = "false";
if (!string.IsNullOrEmpty(mediaIds))
{
string url = string.Format("http://file.api.weixin.qq.com/cgi-bin/media/get?access_token={0}&media_id={1}", ModelWeChat.TokenModel.AccessToken, mediaIds);
HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url);
using (WebResponse wr = req.GetResponse())
{
string dicPath = System.AppDomain.CurrentDomain.BaseDirectory;
string filename = Guid.NewGuid() + ".jpg";
string filepath = @"uploadimage\" + filename;
WebClient mywebclient = new WebClient();
mywebclient.DownloadFile(wr.ResponseUri, dicPath + filepath);
if (File.Exists(dicPath + filepath))//ftp 到文件服务器,然后往数据库插入一笔记录
{
FileInfo fi = new FileInfo(dicPath + filepath);
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 ftppath = "/67/" + ordercode + "/" + filename;
bool bf = ftp.UploadFile(dicPath + filepath, ftppath, true);;
if (bf)
{
using (DBSession db = new DBSession())
{
List <string> sqls = new List <string>();
int uploaduserid = 763;
string customercode = "KSJSBGYXGS";
string sql = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,uploaduserid,customercode,ordercode
,sizes,filetypename,filesuffix)
values(List_Attachment_Id.Nextval,'{0}','{1}','{2}',sysdate,{3},'{4}','{5}'
,'{6}','{7}','{8}')";
sql = string.Format(sql
, ftppath, filename, "67", uploaduserid, customercode, ordercode
, fi.Length, "查验文件", ".jpg");
string sql2 = "update list_order set checkpic=1 where code='" + ordercode + "'";
sqls.Add(sql); sqls.Add(sql2);
int i = db.ExecuteBatch(sqls);
if (i > 0)//插入成功，后删除本地文件
{
str = "success";
fi.Delete();
}
else//插入失败后，远程删除文件，本地文件暂且留着
{
ftp.DeleteFile(ftppath);
}
}
}//ftp失败，本地文件暂且留着
}
}
}
return(str);
}
public static DataSet getDeclareInfo_my(string reptime_s, string reptime_e, string declcode, string customsstatus, string modifyflag, string busitype, string ischeck
, string ispass, string busiunit, string ordercode, string cusno, string tradeway, string contractno, string blno
, string submittime_s, string submittime_e, string sitepasstime_s, string sitepasstime_e
, int start, int itemsPerLoad, string customerCode, string hscode)//
{
DataSet ds = new DataSet();
using (DBSession db = new DBSession())
{
string where = "";
if (!string.IsNullOrEmpty(reptime_s))
{
where += " and lda.reptime>=to_date('" + reptime_s + " 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(reptime_e))
{
where += " and lda.reptime<=to_date('" + reptime_e + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(declcode))
{
where += " and lda.declarationcode like '%" + declcode + "%'";
}
if (!string.IsNullOrEmpty(customsstatus))
{
if (customsstatus == "已结关")
{
where += " and det.CUSTOMSSTATUS='已结关'";
}
if (customsstatus == "未结关")
{
where += " and det.CUSTOMSSTATUS!='已结关' and det.CUSTOMSSTATUS!='删单或异常'";
}
}
if (!string.IsNullOrEmpty(modifyflag))
{
where += " and det.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.declstatus=" + (int)DeclStatusEnum.SitePass;
}
if (ispass == "未放行")
{
where += " and ort.declstatus<" + (int)DeclStatusEnum.SitePass;
}
}
if (!string.IsNullOrEmpty(busiunit))
{
where += " and (lda.BUSIUNITCODE like '%" + busiunit + "%' or lda.BUSIUNITNAME like '%" + busiunit + "%')";
}
if (!string.IsNullOrEmpty(ordercode))
{
where += " and det.ORDERCODE like '%" + ordercode + "%'";
}
if (!string.IsNullOrEmpty(cusno))
{
where += " and ort.CUSNO like '%" + cusno + "%'";
}
if (!string.IsNullOrEmpty(tradeway))
{
where += " and lda.trademethod like '%" + tradeway + "%'";
}
if (!string.IsNullOrEmpty(contractno))
{
where += " and ort.CONTRACTNO like '%" + contractno + "%'";
}
if (!string.IsNullOrEmpty(blno))
{
where += " and lda.BLNO like '%" + blno + "%'";
}
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') ";
}
//当前登录用户权限控制
if (!string.IsNullOrEmpty(customerCode) && !string.IsNullOrEmpty(hscode))
{
where += " and (ort.busiunitcode='" + hscode + "' or ort.customercode='" + customerCode + "')";
}
else if (!string.IsNullOrEmpty(customerCode))
{
where += " and ort.customercode='" + customerCode + "'";
}
else if (!string.IsNullOrEmpty(hscode))
{
where += " and ort.busiunitcode='" + hscode + "'";
}
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,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 ";
if (busitype.Contains("'40'") || busitype.Contains("'41'"))//国内业务
{
tempsql += @" 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";
}
tempsql += @" where (det.STATUS=130 or det.STATUS=110) and det.isinvalid=0 and ort.isinvalid=0" + where
+ @" and a.ordercode is null";
if (busitype.Contains("'40'") || busitype.Contains("'41'"))//国内业务
{
tempsql += @" and b.ASSOCIATENO is null";
}
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);
}
