private void timer6_Tick(object sender, EventArgs e)
{
if (!working6)
{
working6 = true;
try
{
PdfReader pdfReader;
string sql = @"select * from FILEMANAGE where businessno is not null and filetype='CustomsFile' and valid IS NULL and filename is null
and createtime >= to_date('2016-10-1 0:00:00','yyyy-mm-dd hh24:mi:ss') and createtime < to_date('2016-11-10 0:00:00','yyyy-mm-dd hh24:mi:ss')
and ROWNUM<=5";
DataTable dt_filemanage = DBMgrMov.GetDataTable(sql);
string ordercode = string.Empty;
//int attachmentid=2;and createtime > to_date('2015-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') and ROWNUM<=5";
foreach (DataRow dr in dt_filemanage.Rows)
{
string fileid = string.Empty;
string filemanageid = string.Empty;
ordercode = dr["businessno"].ToString();
DataTable dt_list = DBMgr.GetDataTable("select * from list_attachment where ORDERCODE='" + ordercode + "' and filetype=44");
if (dt_list.Rows.Count == 0)
{
fileid = dr["fileid"].ToString();
filemanageid = dr["id"].ToString();
DataTable dt_fileitem = DBMgrMov.GetDataTable("select * from fileitem where id='" + fileid + "'");
//DataTable dt_filemanagedetail = DBMgrMov.GetDataTable("select * from filemanagedetail where filemanageid='" + filemanageid + "'");
if (dt_fileitem.Rows.Count != 0)
{
string fileitemPath = dt_fileitem.Rows[0]["path"].ToString().Trim();
string fileitemName = dt_fileitem.Rows[0]["name"].ToString().Trim();
string item_id = dt_fileitem.Rows[0]["id"].ToString().Trim();
//if (!Directory.Exists(direc_pdf + @"\item\"+fileitemPath))
//{
// Directory.CreateDirectory(direc_pdf + @"\item\"+fileitemPath);
//}
//bool success = ftp.DownloadFile(fileitemPath + fileitemName, direc_pdf + @"\item\" + fileitemPath + fileitemName);
string filename = @"/item/" + fileitemPath + "/" + fileitemName;
string originalname = fileitemName;
int filetype = 44;
int sizes = Convert.ToInt32(dt_fileitem.Rows[0]["filesize"].ToString());
int isupload = 1;
string filesuffix = dt_fileitem.Rows[0]["extname"].ToString();
string filetypename = "订单文件";
int splitstatus = 1;
//if (dt_filemanagedetail.Rows.Count != 0)
//{
// splitstatus = 1;
//}
string url = "http://172.20.70.98:7003/Document/" + fileitemPath + "/" + item_id + "_" + fileitemName;
int filepages = 0;
System.Uri uri = new Uri(url);
//pdfReader = new PdfReader(@"d:\ftpserver\" + filename);
pdfReader = new PdfReader(uri);
filepages = pdfReader.NumberOfPages;
pdfReader.Close();
//更新数据附件数据表
string sqlupdate = "insert into list_attachment (filename,originalname,filetype,sizes,ordercode,filesuffix,filetypename,splitstatus,isupload,filepages,uploadtime,ordercount) "
+ "values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',to_date('" + dr["createtime"].ToString() + "','yyyy-MM-dd HH24:mi:ss'),0)";
sqlupdate = string.Format(sqlupdate, filename, originalname, filetype, sizes, ordercode, filesuffix, filetypename, splitstatus, isupload, filepages);
int updateSuccess = DBMgr.ExecuteNonQuery(sqlupdate);
//获取attachmentid
// DataTable dt_fileitem_new = DBMgrMov.GetDataTable("select id from list_attachment where ordercode='" + ordercode + "'");
//if (dt_fileitem_new.Rows.Count > 1)
//{
// attachmentid = Convert.ToInt32(dt_fileitem_new.Rows[0]["id"].ToString());
//}
}
}
//if (dt_filemanagedetail.Rows.Count!=0)
//{
// foreach (DataRow dr_filemanagedetail in dt_filemanagedetail.Rows)
// {
// string filename=dr_filemanagedetail["filename"].ToString().Replace(@"D:\RW\Files\AppFiles\Portal\","");
// string path=filename.Substring(0,filename.IndexOf(@"\")+1);
// //if (!Directory.Exists(direc_pdf + @"\detail\" + path))
// // {
// // Directory.CreateDirectory(direc_pdf + @"\detail\" + path);
// // }
// //bool success=ftp.DownloadFile(filename, direc_pdf + @"\detail\" + filename);
// string sourcefilename = @"\detail\" + filename;
// string filename_a=filename.Substring(filename.IndexOf(@"\") + 1, filename.Length);
// int filetypeid=0;
// string subtypename=dr_filemanagedetail["subtypename"].ToString();
// switch (subtypename)
// {
// case "箱单":
// filetypeid=52;
// break;
// case "其他1":
// filetypeid=54;
// break;
// case "货物清单":
// filetypeid=101;
// break;
// case "提运单":
// filetypeid=49;
// break;
// case "委托协议":
// filetypeid = 53;
// break;
// case "发票":
// filetypeid=51;
// break;
// case "合同":
// filetypeid=50;
// break;
// }
// string pages = dr_filemanagedetail["pages"].ToString();
// string spliteusername = dr_filemanagedetail["spliteusername"].ToString();
// string splitetime = dr_filemanagedetail["splitetime"].ToString();
// //更新详细文件表
// string sqlupdate = "insert into list_attachmentdetail (sourcefilename,filename,attachmentid,filetypeid,splitetime,ordercode,pages,spliteusername) "
// + "values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')";
// sqlupdate = string.Format(sqlupdate, sourcefilename, filename_a, attachmentid, filetypeid,splitetime,ordercode,pages,spliteusername);
// DBMgrMov.ExecuteNonQuery(sqlupdate);
// }
//}
DBMgrMov.ExecuteNonQuery("update FILEMANAGE set filename='Y' where businessno='" + ordercode + "' and filetype='CustomsFile' and valid IS NULL and filename is null");
}
}
catch (Exception ex)
{
this.button6.Text = ex.Message;
working6 = false;
}
working6 = false;
}
}
private bool update_entorder(FileStruct fs, string directory, string contractno)
{
bool content = false;
#region
try
{
string enterprisecode = string.Empty;
string enterprisename = string.Empty;
string prefix = fs.Name.Substring(0, 3);
string entid = string.Empty;
if (prefix == "E1P" || prefix == "E1B" || prefix == "IMP" || prefix == "IMB")
{
enterprisecode = "3223640003";//海关10位编码 空运出口
enterprisename = "仁宝电子科技(昆山)有限公司";
}
if (prefix == "E1W" || prefix == "E2W" || prefix == "E1D" || prefix == "E2D" || prefix == "E7D" || prefix == "IMW" || prefix == "IMD" || prefix == "LMW" || prefix == "LMD" || prefix == "IAD" || prefix == "IEW" || prefix == "IED" || prefix == "E7W" || prefix == "LDD" || prefix == "LGW" || prefix == "LGD" || prefix == "LDW")
{
enterprisecode = "3223640047";
enterprisename = "仁宝信息技术(昆山)有限公司";
}
if (prefix == "E1C" || prefix == "E1Q" || prefix == "E1O" || prefix == "IMQ" || prefix == "IMC" || prefix == "E2Q" || prefix == "E2C" || prefix == "LGC")
{
enterprisecode = "3223640038";
enterprisename = "仁宝资讯工业(昆山)有限公司";
}
if (prefix == "IVS" || prefix == "EAS")
{
enterprisecode = "3223660037";
enterprisename = "昆山柏泰电子技术服务有限公司";
}
//code是企业编号 仁宝格式 E1Q1603927_sheet.txt
int start = fs.Name.LastIndexOf("_");
int end = fs.Name.LastIndexOf(".");
string suffix = fs.Name.Substring(end + 1, 3).ToUpper();//文件扩展名
string filetype = fs.Name.Substring(start + 1, end - start - 1).ToUpper();
int filetypeid = 0;
switch (filetype)
{
case "CONTRACT":
filetypeid = 50;
break;
case "INVOICE":
filetypeid = 51;
break;
case "PACKING":
filetypeid = 52;
break;
case "SHEET":
filetypeid = 44;
break;
default:
filetypeid = 50;
break;
}
sql = "select * from ent_order where code='" + contractno + "'";
DataTable dt_ent = DBMgr.GetDataTable(sql);
if (dt_ent.Rows.Count == 0)
{
sql = "select ENT_ORDER_ID.Nextval from dual";
entid = DBMgr.GetDataTable(sql).Rows[0][0] + "";
sql = @"insert into ent_order(ID,CODE,CREATETIME,SUBMITTIME,UNITCODE,ENTERPRISECODE,ENTERPRISENAME,FILEDECLAREUNITCODE,FILEDECLAREUNITNAME,
FILERECEVIEUNITCODE,FILERECEVIEUNITNAME,TEMPLATENAME,CUSTOMDISTRICTCODE,CUSTOMDISTRICTNAME) VALUES
('{3}','{0}',sysdate,sysdate,(select fun_AutoQYBH(sysdate) from dual),'{1}','{2}','{4}','{5}','{6}','{7}','COMPAL01','2369','昆山综保')";
sql = string.Format(sql, contractno, enterprisecode, enterprisename, entid, "3223980002", "江苏飞力达国际物流股份有限公司", "3223980002", "江苏飞力达国际物流股份有限公司");
DBMgr.ExecuteNonQuery(sql);
}
else
{
entid = dt_ent.Rows[0]["ID"] + "";
}
//写入随附文件表
sql = @"select * from list_attachment where originalname='" + fs.Name + "' and entid='" + entid + "'";
DataTable dt_att = DBMgr.GetDataTable(sql);//因为客户有可能会重复传文件,此是表记录不需要变化，替换文件即可
if (dt_att.Rows.Count > 0)
{
sql = "delete from list_attachment where id='" + dt_att.Rows[0]["ID"] + "'";
DBMgr.ExecuteNonQuery(sql);
}
//dt_att = DBMgr.GetDataTable("select LIST_ATTACHMENT_ID.Nextval ATTACHMENTID from dual");
sql = @"insert into list_attachment(ID,FILENAME,ORIGINALNAME,UPLOADTIME,FILETYPE,SIZES,ENTID,FILESUFFIX,UPLOADUSERID,CUSTOMERCODE,isupload) values(
LIST_ATTACHMENT_ID.Nextval,'{0}','{1}',sysdate,'{2}','{3}','{4}','{5}','404','{6}','1')";
sql = string.Format(sql, "/" + directory + "/" + fs.Name, fs.Name, filetypeid, fs.Size, entid, suffix, enterprisecode);
int result = DBMgr.ExecuteNonQuery(sql);
if (result > 0 && fs.Name.IndexOf(".txt") > 0)
{
db.ListRightPush("compal_sheet_topdf_queen", "{ENTID:'" + entid + "',FILENAME:" + "'/" + directory + "/" + fs.Name + "'}");//保存随附文件ID到队列
}
content = true;
}
#endregion
catch (Exception ex)
{
this.button3.Text = "database_" + ex.Message;
content = false;
}
return(content);
}
private void redistotable()
{
if (db.KeyExists("declareall"))
{
string json = db.ListLeftPop("declareall");
if (!string.IsNullOrEmpty(json))
{
try
{
JObject jo = (JObject)JsonConvert.DeserializeObject(json);
sql = @"insert into redis_declareall (ID,DECLARATIONCODE,TRADECODE,TRANSNAME,GOODSNUM,GOODSGW,SHEETNUM,COMMODITYNUM,
CUSTOMSSTATUS,MODIFYFLAG,PREDECLCODE,CUSNO,OLDDECLARATIONCODE,ISDEL,DIVIDEREDISKEY) values (REDIS_DECLAREALL_ID.Nextval,
'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')";
sql = string.Format(sql, jo.Value <string>("DECLARATIONCODE"), jo.Value <string>("TRADECODE"), jo.Value <string>("TRANSNAME"),
jo.Value <string>("GOODSNUM"), jo.Value <string>("GOODSGW"), jo.Value <string>("SHEETNUM"), jo.Value <string>("COMMODITYNUM"),
jo.Value <string>("CUSTOMSSTATUS"), jo.Value <string>("MODIFYFLAG"), jo.Value <string>("PREDECLCODE"), jo.Value <string>("CUSNO"),
jo.Value <string>("OLDDECLARATIONCODE"), jo.Value <string>("ISDEL"), jo.Value <string>("DIVIDEREDISKEY"));
DBMgr.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
this.button5.Text = ex.Message;
db.ListRightPush("declareall", json);
}
}
}
if (db.KeyExists("inspectionall"))
{
string json = db.ListLeftPop("inspectionall");
if (!string.IsNullOrEmpty(json))
{
try
{
JObject jo = (JObject)JsonConvert.DeserializeObject(json);
sql = @"insert into REDIS_INSPECTIONALL (ID,APPROVALCODE,INSPECTIONCODE,TRADEWAY,CLEARANCECODE,SHEETNUM,
COMMODITYNUM,INSPSTATUS,MODIFYFLAG,PREINSPCODE,CUSNO, OLDINSPECTIONCODE ,ISDEL,ISNEEDCLEARANCE,
LAWFLAG,DIVIDEREDISKEY) values (REDIS_INSPECTIONALL_ID.Nextval,
'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')";
sql = string.Format(sql, jo.Value <string>("APPROVALCODE"), jo.Value <string>("INSPECTIONCODE"), jo.Value <string>("TRADEWAY"),
jo.Value <string>("CLEARANCECODE"), jo.Value <string>("SHEETNUM"), jo.Value <string>("COMMODITYNUM"), jo.Value <string>("INSPSTATUS"),
jo.Value <string>("MODIFYFLAG"), jo.Value <string>("PREINSPCODE"), jo.Value <string>("CUSNO"), jo.Value <string>("OLDINSPECTIONCODE"),
jo.Value <string>("ISDEL"), jo.Value <string>("ISNEEDCLEARANCE"), jo.Value <string>("LAWFLAG"), jo.Value <string>("DIVIDEREDISKEY"));
DBMgr.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
this.button5.Text = ex.Message;
db.ListRightPush("inspectionall", json);
}
}
}
if (db.KeyExists("statuslogall"))
{
string json = db.ListLeftPop("statuslogall");
if (!string.IsNullOrEmpty(json))
{
try
{
JObject jo = (JObject)JsonConvert.DeserializeObject(json);
sql = @"insert into redis_statuslogall (ID,TYPE,CUSNO,STATUSCODE,STATUSVALUE,DIVIDEREDISKEY) values (REDIS_STATUSLOGALL_ID.Nextval,
'{0}','{1}','{2}','{3}','{4}')";
sql = string.Format(sql, jo.Value <string>("TYPE"), jo.Value <string>("CUSNO"), jo.Value <string>("STATUSCODE"),
jo.Value <string>("STATUSVALUE"), jo.Value <string>("DIVIDEREDISKEY"));
DBMgr.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
this.button5.Text = ex.Message;
db.ListRightPush("statuslogall", json);
}
}
}
}
private void timer2_Tick(object sender, EventArgs e)
{
if (!working2)
{
working2 = true;
if (db.KeyExists("recognizetask"))
{
string json = db.ListLeftPop("recognizetask");
if (!string.IsNullOrEmpty(json))
{
try
{
JObject jo = (JObject)JsonConvert.DeserializeObject(json);
//只有PDF文件才会进行条形码识别
sql = @"select t.* from list_attachment t where t.ordercode='" + jo.Value <string>("ordercode") + "' and upper(t.filesuffix)='PDF'";
DataTable dt = DBMgr.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
guid = Guid.NewGuid().ToString();
ConvertPDF2Image(direc_pdf + dt.Rows[0]["FILENAME"], direc_img, guid, 1, 1, ImageFormat.Jpeg, Definition.Ten);
BarcodeDecoder barcodeDecoder = new BarcodeDecoder();
if (File.Exists(direc_img + guid + ".Jpeg"))
{
System.Drawing.Bitmap image = new System.Drawing.Bitmap(direc_img + guid + ".Jpeg");
Dictionary <DecodeOptions, object> decodingOptions = new Dictionary <DecodeOptions, object>();
List <BarcodeFormat> possibleFormats = new List <BarcodeFormat>(10);
//possibleFormats.Add(BarcodeFormat.DataMatrix);
//possibleFormats.Add(BarcodeFormat.QRCode);
//possibleFormats.Add(BarcodeFormat.PDF417);
//possibleFormats.Add(BarcodeFormat.Aztec);
//possibleFormats.Add(BarcodeFormat.UPCE);
//possibleFormats.Add(BarcodeFormat.UPCA);
possibleFormats.Add(BarcodeFormat.Code128);
//possibleFormats.Add(BarcodeFormat.Code39);
//possibleFormats.Add(BarcodeFormat.ITF14);
//possibleFormats.Add(BarcodeFormat.EAN8);
possibleFormats.Add(BarcodeFormat.EAN13);
//possibleFormats.Add(BarcodeFormat.RSS14);
//possibleFormats.Add(BarcodeFormat.RSSExpanded);
//possibleFormats.Add(BarcodeFormat.Codabar);
//possibleFormats.Add(BarcodeFormat.MaxiCode);
decodingOptions.Add(DecodeOptions.TryHarder, true);
decodingOptions.Add(DecodeOptions.PossibleFormats, possibleFormats);
Result decodedResult = barcodeDecoder.Decode(image, decodingOptions);
//while (decodedResult == null)
//{
// System.Threading.Thread.Sleep(500);
//}
if (decodedResult != null)//有些PDF文件并无条形码
{
barcode = decodedResult.Text;
barconvert();//编码转换
sql = "update list_order set cusno='" + barcode + "' where code='" + jo.Value <string>("ordercode") + "'";
DBMgr.ExecuteNonQuery(sql);
}
}
}
}
catch (Exception ex)
{
this.button2.Text = ex.Message;
this.button2.Text = json + "识别条码失败!";
//db.ListRightPush("recognizetask", json);
working2 = false;
}
}
}
working2 = false;
}
}