//订单的状态在草稿、文件已上传、订单已委托 三个状态发生时记录到订单状态变更日志 public static void add_list_time(int status, string ordercode, JObject json_user) { if (status != 0 && status != 10) { return; } string sql_search = @"select count(*) from list_times where code = '{0}' and status = '{1}'"; string sql_insert = @"insert into list_times(id,code,userid,realname,status,times,type,ispause) values(list_times_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'0','0')"; string sql = ""; int i = 0; int CreateUserId = json_user.Value <Int32>("ID"); string CreateUserName = json_user.Value <string>("REALNAME"); int[] status_array = new int[] { 0, 10 }; foreach (int status_tmp in status_array) { if (status >= status_tmp) { sql = string.Format(sql_search, ordercode, status_tmp); i = Convert.ToInt32(DBMgr.GetDataTable(sql).Rows[0][0]); if (i == 0) { sql = string.Format(sql_insert, ordercode, CreateUserId, CreateUserName, status_tmp); DBMgr.ExecuteNonQuery(sql); } } } }
//订单删除 公共方法 by panhuaguo 2016-08-30 含国内业务 public static string deleteorder(string ordercode) { string json = "{success:false}"; //删除订单随附文件 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 sql = "select * from list_attachment where ordercode='" + ordercode + "'"; DataTable dt = DBMgr.GetDataTable(sql); foreach (DataRow dr in dt.Rows) { ftp.DeleteFile(dr["FILENAME"] + ""); } sql = "delete from list_attachment where ordercode='" + ordercode + "'"; DBMgr.ExecuteNonQuery(sql); //删除list_times信息 sql = "delete from list_times where code='" + ordercode + "'"; DBMgr.ExecuteNonQuery(sql); //删除集装箱信息 sql = "delete from list_predeclcontainer where ordercode='" + ordercode + "'"; DBMgr.ExecuteNonQuery(sql); //删除订单信息 sql = "delete from list_order where code='" + ordercode + "'"; DBMgr.ExecuteNonQuery(sql); json = "{success:true}"; return(json); }
//集装箱及报关车号列表更新 public static void predeclcontainer_update(string ordercode, string containertruck) { DBMgr.ExecuteNonQuery("delete from list_predeclcontainer where ORDERCODE = '" + ordercode + "'"); if (!string.IsNullOrEmpty(containertruck)) { JArray ja = (JArray)JsonConvert.DeserializeObject(containertruck); for (int i = 0; i < ja.Count; i++) { string sql = @"insert into list_predeclcontainer(ID,ORDERCODE,CONTAINERORDER,CONTAINERNO,CONTAINERSIZE,CONTAINERSIZEE,CONTAINERWEIGHT, CONTAINERTYPE,HSCODE,FORMATNAME,CDCARNO,CDCARNAME,UNITNO,ELESHUT) values(LIST_PREDECLCONTAINER_id.Nextval,'{0}','{1}','{2}','{3}', '{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')"; sql = string.Format(sql, ordercode, i, ja[i].Value <string>("CONTAINERNO"), ja[i].Value <string>("CONTAINERSIZE"), ja[i].Value <string>("CONTAINERSIZEE"), ja[i].Value <string>("CONTAINERWEIGHT"), ja[i].Value <string>("CONTAINERTYPE"), ja[i].Value <string>("HSCODE"), ja[i].Value <string>("FORMATNAME"), ja[i].Value <string>("CDCARNO"), ja[i].Value <string>("CDCARNAME"), ja[i].Value <string>("UNITNO"), ja[i].Value <string>("ELESHUT")); DBMgr.ExecuteNonQuery(sql); } } }
//订单新增或者更新时对随附文件表的操作 非国内业务都会用到 封装by panhuaguo 2016-08-03 //originalfileids 这个字符串存储的是订单修改时原始随附文件id用逗号分隔 public static void Update_Attachment(string ordercode, string filedata, string originalfileids, JObject json_user) { if (!string.IsNullOrEmpty(filedata)) { 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); JArray jarry = JsonConvert.DeserializeObject <JArray>(filedata); string sql = ""; foreach (JObject json in jarry) { if (string.IsNullOrEmpty(json.Value <string>("ID"))) { string filename = "/" + json.Value <string>("FILETYPE") + "/" + ordercode + "/" + json.Value <string>("ORIGINALNAME"); string sizes = json.Value <string>("SIZES"); string filetypename = json.Value <string>("FILETYPENAME"); string extname = json.Value <string>("ORIGINALNAME").ToString().Substring(json.Value <string>("ORIGINALNAME").ToString().LastIndexOf('.') + 1); sql = @"insert into LIST_ATTACHMENT (id,filename,originalname,filetype,uploadtime,uploaduserid,customercode,ordercode, sizes,filetypename,filesuffix,IETYPE) values(List_Attachment_Id.Nextval,'{0}','{1}','{2}',sysdate,{3},'{4}','{5}','{6}','{7}','{8}','{9}')"; sql = string.Format(sql, filename, json.Value <string>("ORIGINALNAME"), json.Value <string>("FILETYPE"), json_user.Value <string>("ID"), json_user.Value <string>("CUSTOMERCODE"), ordercode, sizes, filetypename, extname, json.Value <string>("IETYPE")); DBMgr.ExecuteNonQuery(sql); } else//如果ID已经存在 说明是已经存在的记录,不需要做任何处理 { originalfileids = originalfileids.Replace(json.Value <string>("ID") + ",", ""); } } //从数据库和文档库删除在前端移除的随附文件记录 string[] idarray = originalfileids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); foreach (string id in idarray) { sql = @"select * from LIST_ATTACHMENT where ID='" + id + "'"; DataTable dt = DBMgr.GetDataTable(sql); if (dt.Rows.Count > 0) { ftp.DeleteFile(dt.Rows[0]["FILENAME"] + ""); } sql = @"delete from LIST_ATTACHMENT where ID='" + id + "'"; DBMgr.ExecuteNonQuery(sql); } } }
//提交后订单修改时记录字段信息变更情况 public static void Insert_FieldUpdate_History(string ordercode, JObject json_new, JObject json_user, string busitype) { //国内订单已受理后前端为空时能修改的字段 string JsonFieldComments = ""; switch (busitype) { case "40": //国内出口 case "41": //国内进口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',GOODSNUM:'件数',PACKKIND:'包装',GOODSGW:'毛重',GOODSNW:'净重',CONTRACTNO:'合同号',FILINGNUMBER:'账册备案号', LAWCONDITION:'法检状况',CLEARANCENO:'通关单号',ASSOCIATEPEDECLNO:'出口报关单',REPUNITCODE:'报关申报单位',INSPUNITCODE:'报检申报单位',ENTRUSTREQUEST:'需求备注'}"; break; case "10": //空运出口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',TOTALNO:'总单号',DIVIDENO:'分单号',GOODSNUM:'件数',PACKKIND:'包装',GOODSGW:'毛重'',GOODSNW:'净重',CONTRACTNO:'合同号',ARRIVEDNO:'运抵编号',TURNPRENO:'转关预录号', CLEARANCENO:'通关单号',DECLCARNO:'报关车号',ENTRUSTREQUEST:'需求备注',LAWCONDITION:'法检状况',CHECKEDGOODSNUM:'确认件数',CHECKEDWEIGHT:'确认毛重', WEIGHTCHECK:'需重量确认',ISWEIGHTCHECK:'重量确认',SELFCHECK:'需自审',ISSELFCHECK:'自审确认'}"; break; case "11": //空运进口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',TOTALNO:'总单号',GOODSNUM:'件数',PACKKIND:'包装',GOODSNW:'净重',CONTRACTNO:'合同号',TURNPRENO:'转关预录号', CLEARANCENO:'通关单号',DECLCARNO:'报关车号',ENTRUSTREQUEST:'需求备注',LAWCONDITION:'法检状况'}"; break; case "20": //海运出口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',PACKKIND:'包装',GOODSNW:'净重',CONTRACTNO:'合同号',SECONDLADINGBILLNO:'提单号',ARRIVEDNO:'运抵编号', LAWCONDITION:'法检状况',CLEARANCENO:'通关单号',CONTAINERNO:'集装箱号',DECLCARNO:'报关车号',TURNPRENO:'转关预录号',ENTRUSTREQUEST:'需求备注'}"; break; case "21": //海运进口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',PACKKIND:'包装',GOODSNW:'净重',CONTRACTNO:'合同号',SECONDLADINGBILLNO:'国检提单号',SECONDLADINGBILLNO:'海关提单号',TRADEWAYCODES_ZS:'贸易方式', TURNPRENO:'转关预录号',WOODPACKINGID:'木质包装',CLEARANCENO:'通关单号',LAWCONDITION:'法检状况',CONTAINERNO:'集装箱号',DECLCARNO:'报关车号',ENTRUSTREQUEST:'需求备注'}"; break; case "30": //陆运出口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',FILGHTNO:'航次号',CONTRACTNO:'合同号',PACKKIND:'包装',GOODSNW:'净重',ARRIVEDNO:'运抵编号',LAWCONDITION:'法检状况', CLEARANCENO:'通关单号',CONTAINERNO:'集装箱号',DECLCARNO:'报关车号',TURNPRENO:'转关预录号',TOTALNO:'总单号',DIVIDENO:'分单号',ENTRUSTREQUEST:'需求备注'}"; break; case "31": //陆运进口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',FILGHTNO:'航次号',DIVIDENO:'分单号',GOODSNUM:'件数',PACKKIND:'包装',GOODSNW:'净重',CONTRACTNO:'合同号',MANIFEST:'载货清单号', CLEARANCENO:'通关单号',LAWCONDITION:'法检状况',CONTAINERNO:'集装箱号',DECLCARNO:'报关车号',ENTRUSTREQUEST:'需求备注'}"; break; case "50": //特殊区域出口 case "51": //特殊区域进口 JsonFieldComments = @"{SPECIALRELATIONSHIP:'特殊关系确认',PRICEIMPACT:'价格影响确认',PAYPOYALTIES:'支付特许权使用费确认', CUSNO:'客户编号',PACKKIND:'包装',GOODSNW:'净重',CONTRACTNO:'合同号',TURNPRENO:'对方转关号',LAWCONDITION:'法检状况',CLEARANCENO:'通关单号', GOODSTYPEID:'货物类型',CONTAINERNO:'集装箱号',DECLCARNO:'报关车号',ENTRUSTREQUEST:'需求备注',BUSITYPE:'业务类型'}"; break; } string sql = "select * from list_order where CODE = '" + ordercode + "'"; DataTable dt = DBMgr.GetDataTable(sql); IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式 iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; string ori_json = JsonConvert.SerializeObject(dt, iso).TrimStart('[').TrimEnd(']'); JObject json_key = JsonConvert.DeserializeObject <JObject>(JsonFieldComments); JObject json_ori = JsonConvert.DeserializeObject <JObject>(ori_json); foreach (JProperty jp in json_key.Properties()) { if (jp.Name == "SPECIALRELATIONSHIP" || jp.Name == "PRICEIMPACT" || jp.Name == "PAYPOYALTIES" || jp.Name == "SPECIALRELATIONSHIP" || jp.Name == "LAWCONDITION" || jp.Name == "WEIGHTCHECK" || jp.Name == "ISWEIGHTCHECK" || jp.Name == "SELFCHECK" || jp.Name == "ISSELFCHECK") { if (!json_ori.Value <bool>(jp.Name) && json_new.Value <string>(jp.Name) == "on") { sql = @"insert into list_updatehistory(id,ORDERCODE,USERID,UPDATETIME,NEWFIELD,NAME,CODE,FIELD,FIELDNAME,TYPE) values (LIST_UPDATEHISTORY_ID.nextval,'{0}','{1}',sysdate,'{2}','{3}','{4}','{5}','{6}','1')"; sql = string.Format(sql, ordercode, json_user.Value <string>("ID"), json_new.Value <string>(jp.Name), json_user.Value <string>("NAME"), ordercode, jp.Name, jp.Value); DBMgr.ExecuteNonQuery(sql); } } else { if (string.IsNullOrEmpty(json_ori.Value <string>(jp.Name)) && !string.IsNullOrEmpty(json_new.Value <string>(jp.Name))) { sql = @"insert into list_updatehistory(id,ORDERCODE,USERID,UPDATETIME,NEWFIELD,NAME,CODE,FIELD,FIELDNAME,TYPE) values (LIST_UPDATEHISTORY_ID.nextval,'{0}','{1}',sysdate,'{2}','{3}','{4}','{5}','{6}','1')"; sql = string.Format(sql, ordercode, json_user.Value <string>("ID"), json_new.Value <string>(jp.Name), json_user.Value <string>("NAME"), ordercode, jp.Name, jp.Value); DBMgr.ExecuteNonQuery(sql); } } } }
public static void Update_Attachment_ForEnterprise(string entorder_id, string filedata, string originalfileids, JObject json_user) { if (!string.IsNullOrEmpty(filedata)) { string webFilePath = ConfigurationManager.AppSettings["WebFilePath"]; 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); JArray jarry = JsonConvert.DeserializeObject <JArray>(filedata); string sql = ""; string remote = DateTime.Now.ToString("yyyy-MM-dd"); IDatabase db = SeRedis.redis.GetDatabase(); foreach (JObject json in jarry) { if (string.IsNullOrEmpty(json.Value <string>("ID"))) { string filename = "/" + remote + "/" + json.Value <string>("NEWNAME"); string sizes = json.Value <string>("SIZES"); string filetypename = json.Value <string>("FILETYPENAME"); string extname = json.Value <string>("ORIGINALNAME").ToString().Substring(json.Value <string>("ORIGINALNAME").ToString().LastIndexOf('.') + 1); try { string[] split = json.Value <string>("NEWNAME").Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries); string oldName = webFilePath + json.Value <string>("NEWNAME"); string newName = webFilePath + split[0] + "_0." + split[1]; FileInfo fi = new FileInfo(oldName); fi.MoveTo(Path.Combine(newName)); StreamReader sr = new StreamReader(newName, Encoding.GetEncoding("BIG5")); String line; FileStream fs = new FileStream(oldName, FileMode.Create); while ((line = sr.ReadLine()) != null) { byte[] dst = Encoding.UTF8.GetBytes(line); fs.Write(dst, 0, dst.Length); fs.WriteByte(13); fs.WriteByte(10); } fs.Flush(); fs.Close(); } catch (Exception) { throw; } sql = @"insert into LIST_ATTACHMENT (id,filename,originalname,filetype,uploadtime,uploaduserid,customercode,entid, sizes,filetypename,filesuffix) values(List_Attachment_Id.Nextval,'{0}','{1}','{2}',sysdate,{3},'{4}','{5}','{6}','{7}','{8}')"; sql = string.Format(sql, filename, json.Value <string>("ORIGINALNAME"), json.Value <string>("FILETYPE"), json_user.Value <string>("ID"), json_user.Value <string>("CUSTOMERCODE"), entorder_id, sizes, filetypename, extname); DBMgr.ExecuteNonQuery(sql); if (json.Value <string>("ORIGINALNAME").IndexOf(".txt") > 0 || json.Value <string>("ORIGINALNAME").IndexOf(".TXT") > 0) { db.ListRightPush("compal_sheet_topdf_queen", "{ENTID:'" + entorder_id + "',FILENAME:'" + filename + "'}"); } } else//如果ID已经存在 说明是已经存在的记录,不需要做任何处理 { originalfileids = originalfileids.Replace(json.Value <string>("ID") + ",", ""); } } //从数据库和文档库删除在前端移除的随附文件记录 string[] idarray = originalfileids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); foreach (string id in idarray) { sql = @"select * from LIST_ATTACHMENT where ID='" + id + "'"; DataTable dt = DBMgr.GetDataTable(sql); if (dt.Rows.Count > 0) { ftp.DeleteFile(dt.Rows[0]["FILENAME"] + ""); } sql = @"delete from LIST_ATTACHMENT where ID='" + id + "'"; DBMgr.ExecuteNonQuery(sql); //移除转PDF的缓存 db.ListRemove("compal_sheet_topdf_queen", "{ENTID:'" + id + "',FILENAME:'" + dt.Rows[0]["FILENAME"].ToString() + "'}"); } } }
/// <summary> /// 更新修改 list_times /// </summary> /// <param name="order"></param> public void CreateOrdertimes(Models.OrderEn order) { //更新修改 list_times if (order.Status != 1 && order.Status != 10 && order.Status != 15) { return; } string ins = ""; int i = 0; //如果是创建的时候直接委托或直接上传文件: if (order.Status == 10) { //文件上传 ins = @"select count(*) from list_times where code = '{0}' and status = '{1}'"; ins = string.Format(ins, order.Code, 1); i = Convert.ToInt32(DBMgr.GetDataTable(ins).Rows[0][0]); if (i == 0) { //插入草稿 ins = "insert into list_times(id,code,userid,realname,status,times,type,ispause) values(list_times_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'1','0')"; ins = string.Format(ins, order.Code, order.CreateUserId, order.CreateUserName, 1); DBMgr.ExecuteNonQuery(ins); } } if (order.Status == 15) { //提交委托 ins = @"select count(*) from list_times where code = '{0}' and status = '{1}'"; ins = string.Format(ins, order.Code, 1); i = Convert.ToInt32(DBMgr.GetDataTable(ins).Rows[0][0]); if (i == 0) { //插入草稿 ins = "insert into list_times(id,code,userid,realname,status,times,type,ispause) values(list_times_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'1','0')"; ins = string.Format(ins, order.Code, order.CreateUserId, order.CreateUserName, 1); DBMgr.ExecuteNonQuery(ins); } ins = @"select count(*) from list_times where code = '{0}' and status = '{1}'"; ins = string.Format(ins, order.Code, 10); i = Convert.ToInt32(DBMgr.GetDataTable(ins).Rows[0][0]); if (i == 0) { //插入文件已上传 ins = "insert into list_times(id,code,userid,realname,status,times,type,ispause) values(list_times_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'1','0')"; ins = string.Format(ins, order.Code, order.CreateUserId, order.CreateUserName, 10); DBMgr.ExecuteNonQuery(ins); } } //插入当前 ins = @"select count(*) from list_times where code = '{0}' and status = '{1}'"; ins = string.Format(ins, order.Code, order.Status); i = Convert.ToInt32(DBMgr.GetDataTable(ins).Rows[0][0]); if (i == 0) { ins = "insert into list_times(id,code,userid,realname,status,times,type,ispause) values(list_times_id.nextval,'{0}','{1}','{2}','{3}',sysdate,'1','0')"; ins = string.Format(ins, order.Code, order.CreateUserId, order.CreateUserName, order.Status); DBMgr.ExecuteNonQuery(ins); } }
/// <summary> /// 创建订单预配信息 /// </summary> public int CreateOrderPreMatch(Models.OrderEn order) { DataTable dt = null; int num = 0; string str = string.Empty; List <PreOrderEn> list = new List <PreOrderEn>(); if (!string.IsNullOrEmpty(order.BusiType) && !string.IsNullOrEmpty(order.BusiUnitCode) && !string.IsNullOrEmpty(order.CustomerCode)) { string sel = @"select * from config_preorder where (instr(','||busitypes||',',','||'" + order.BusiType + "'||',')>0 or busitypes is null) and (instr(','||customerids||',',','||'" + order.CustomerCode + "'||',')>0 or customerids is null) and (instr(','||busiunitcodes||',',','||'" + order.BusiUnitCode + "'||',')>0 or busiunitcodes is null) and enabled=1"; dt = DBMgr.GetDataTable(sel); } string cs = string.Empty, mo = string.Empty, co = string.Empty, decl = string.Empty, insp = string.Empty, regu = string.Empty; if (dt == null) { return(0); } if (dt.Rows.Count > 0) { foreach (DataRow p in dt.Rows) { switch (Convert.ToInt16(p["PreType"])) { case (int)PostEnum.CS: cs += p["UserId"] + ","; break; case (int)PostEnum.MO: mo += p["UserId"] + ","; break; case (int)PostEnum.CO: co += p["UserId"] + ","; break; case (int)PostEnum.DECL: decl += p["UserId"] + ","; break; case (int)PostEnum.INSP: insp += p["UserId"] + ","; break; case (int)PostEnum.REGU: regu += p["UserId"] + ","; break; } } } string ins = @"insert into list_cspond(id,ordercode,precs,premo,preco,predecl,preinsp,preregu,priority,isback,status,correspondenceno,associateno,busitype) values(list_cspond_id.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')"; ins = string.Format(ins, order.Code, cs, mo, co, decl, insp, regu, order.Priority, 0, order.Status, order.CorrespondNo, order.AssociateNo, order.BusiType); num = DBMgr.ExecuteNonQuery(ins); return(num); }