// public static string Siteapply(string ordercode)
// {
// string userid = "763"; string username = "******"; string realname = "昆山吉时报关有限公司";
// using (DBSession db = new DBSession())
// {
// string sql = "select to_char(siteapplytime,'yyyy/mm/dd hh24:mi:ss') as siteapplytime from list_order where code='" + ordercode + "'";
// DataTable dt = db.QuerySignle(sql);
// string curtime = dt.Rows[0]["SITEAPPLYTIME"].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 siteapplyuserid='{1}',siteapplyusername='******',siteapplytime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=150 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, "SITEAPPLYTIME", "现场报关");
// db.ExecuteSignle(sql);
// sql = @"select code,entrusttype,declstatus,inspstatus from list_order lo where lo.code='" + ordercode + "'";
// DataTable dt_order = db.QuerySignle(sql);
// //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, "list_order.siteapplytime现场报关");
// }
// }
// else
// {
// if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160)
// {
// msc.FinanceExceptionOrder(ordercode, username, "list_order.siteapplytime现场报关");
// }
// }
// 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 siteapplyuserid='{1}',siteapplyusername='******',siteapplytime=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 Siteapplyall(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(siteapplytime,'yyyy/mm/dd hh24:mi:ss') as siteapplytime from list_order where code='" + ordercode + "'";
DataTable dt = db.QuerySignle(sql);
string curtime = dt.Rows[0]["SITEAPPLYTIME"].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 siteapplyuserid='{1}',siteapplyusername='******',siteapplytime=to_date('{3}','yyyy-MM-dd HH24:mi:ss'),declstatus=150 where code='{0}' and declstatus<=150";
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, "SITEAPPLYTIME", "现场报关");
db.ExecuteSignle(sql);
sql = @"select code,entrusttype,declstatus,inspstatus from list_order lo where lo.code='" + ordercode + "'";
DataTable dt_order = db.QuerySignle(sql);
//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, "list_order.siteapplytime现场报关");
}
}
else
{
if (Convert.ToInt32(dt_order.Rows[0]["declstatus"].ToString()) >= 160)
{
msc.FinanceExceptionOrder(ordercode, username, "list_order.siteapplytime现场报关");
}
}
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'}");
}
}
}
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 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);
}