public static string LoadASN(string com, string no)
{
DbNewAssistant q = new DbNewAssistant("TPEBIZ01");
string sql = string.Format(@"SELECT FRAMEWORK.PUBLIC_PKG.TimeZone_Converter('HQ','ACI', b2bsenttime) SENTTIME, ISANO, ' ' STATUS, copyflag FLAG FROM b2b.b2b_856_m WHERE companyid = '{0}' and pono = '{1}'"
, com
, no
);
DataTable dt = q.DoSelect(sql);
if (dt.Rows.Count > 0)
{
DataRow rw = dt.Rows[0];
if (rw.Field<Nullable<DateTime>>("SENTTIME") == null && rw.Field<string>("FLAG") == null)
rw.SetField("STATUS", " ");
else if (rw.Field<Nullable<DateTime>>("SENTTIME") == null && rw.Field<string>("FLAG") == "N")
rw.SetField("STATUS", "ASN data is ready, not yet been sent.");
else if (rw.Field<Nullable<DateTime>>("SENTTIME") != null && rw.Field<string>("FLAG") == "U")
rw.SetField("STATUS", "EDI ASN has been sent.");
}
return JsonConvert.SerializeObject(dt);
}
public void LoadSRMSessionFromURL()
{
string mCompanyID = Request.QueryString["p1"].ToString();
string mUserID = Request.QueryString["p2"].ToString();
string mToken = Request.QueryString["p3"].ToString();
string mStartPage = Request.QueryString["p4"].ToString();
if (Session["insSUser"] != null)
{
SUser insSUser = (SUser)Session["insSUser"];
if (insSUser.CompanyID == mCompanyID && insSUser.ID == mUserID && insSUser.Token == mToken)
{
Response.Redirect(mStartPage, false);
return;
}
}
DbNewAssistant DNB = new DbNewAssistant("B2B_ASUSB2B_DB");
string sql = string.Format(@"SELECT *
FROM ASUSB2B.B2BPT_USER_LOG
WHERE USER_ID='{0}' AND SESSION_ID='{1}' AND WORK_STATUS='Y'", mUserID, mToken);
DataTable dt = DNB.DoSelect(sql);
if (dt.Rows.Count > 0)
{
SUser insSUser = new SUser();
insSUser.ID = mUserID;
insSUser.CompanyID = mCompanyID;
insSUser.Token = mToken;
insSUser.SessionID = Session.SessionID;
insSUser.IP = Request.UserHostAddress;
Session["insSUser"] = insSUser;
}
Response.Redirect(mStartPage);
}
public static string LoadCHG(string com, string no)
{
DbNewAssistant q = new DbNewAssistant("ETEAMQC1");
//com = "BESTBUY";
//no = "NCRDFY";
//FRAMEWORK.PUBLIC_PKG.TimeZone_Converter('HQ','ACI',d.cust_requestdate)
string sql = string.Format("SELECT m.revision REV, d.lineno LNO, d.qty_ordered ORIGINAL_QTY, d.unitprice UPC, d.buyerpartno BPART, d.asuspartno APART, d.change_type CTYPE, d.upccode UPO, d.qty_leftreceive NEW_QTY, d.cust_requestdate NEW_REQUEST_DATE FROM b2b.b2b_850_m m, b2b.b2b_850_d d WHERE m.jobreference = d.jobreference AND m.companyid = '{0}' AND m.pono = '{1}' AND m.revision > 0 ORDER BY m.revision, d.lineno"
, com
, no
);
DataTable dt = q.DoSelect(sql);
return JsonConvert.SerializeObject(dt);
}
public void ProcessRequest(HttpContext context)
{
string company = context.Request["com"] ?? "";
string isano = context.Request["doc"] ?? "";
string sql = string.Format(@"SELECT filename FROM ehub.ftplog WHERE companyid = '{0}' and docid = '{1}'"
,company
,isano
);
string sql2 = string.Format(@"SELECT class2, class3 FROM asusb2b.b2bpt_dict WHERE dict_type = 'EDI_Path' AND class1 = '{0}'"
,company
);
DbNewAssistant q = new DbNewAssistant("TPEBIZ01_EHUB");
DbNewAssistant q2 = new DbNewAssistant("B2B_ASUSB2B_DB");
DataTable dt = q.DoSelect(sql);
DataTable dt2 = q2.DoSelect(sql2);
if (dt.Rows.Count == 0)
context.Response.Write("File Not Found.");
else if (dt2.Rows.Count == 0)
context.Response.Write("Path Not Found.");
else
{
string fileName = dt.Rows[0].Field<string>("filename");
string filePath = string.Format(@"\\{0}\{1}\",
dt2.Rows[0].Field<string>("class2"),
dt2.Rows[0].Field<string>("class3")
);
System.Web.HttpResponse response = context.Response;
response.ClearContent();
response.Clear();
response.ContentType = "text/plain";
response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ";");
response.TransmitFile(filePath + fileName);
//response.TransmitFile(@"\\172.21.130.230\Asus_B2BProd_WM8\ediAS2Log\amazon\outbound\AS2-ASUS-AMAZON-X12-5010-856534602-0agm2l00a0jla6ur000pu7er.edi");
response.Flush();
response.End();
}
}
public static string LoadList(string filter, int stRow, int edRow, string order, string cst_list)
{
//for order by
List<string[]> fieldMap = new List<string[]>
{
new string [2]{"CPO","A.pono"},
new string [2]{"ODATE","A.podate"},
new string [2]{"SO","B.doc_id"},
new string [2]{"DNO","C.doc_id"},
new string [2]{"STATUS","A.podate"},
new string [2]{"CID","A.companyid"},
new string [2]{"OORG","F.branch"}
};
DbNewAssistant q = new DbNewAssistant("TPEBIZ01");
dynamic f = JsonConvert.DeserializeObject(filter);
//設定STATUS欄位
/*
1. b2b_850_m 有值, 但是 b2b_doc_m or b2b_doc_d 並沒有值 -> EDI PO received
2. b2b_doc_d.doc_type 有 POR -> Order import processing
3. b2b_doc_d.doc_type 有 SO -> Order import successful
4. b2b_doc_d.doc_type 有 PCF -> Order confirmed
5. b2b_doc_d.doc_type 有 ASN -> EDI ASN sent
6. b2b_doc_d.doc_type 有 INV -> EDI INV sent
*/
string sql = @"SELECT A.companyid CID, A.pono CPO, A.podate ODATE, B.doc_id SO, C.doc_id DNO,
CASE NVL(G.doc_type, 'X') WHEN 'X' THEN 'EDI PO received' WHEN 'POR' THEN 'Order import processing' WHEN 'SO' THEN 'Order import successful' WHEN 'PCF' THEN 'Order confirmed' WHEN 'ASN' THEN 'EDI ASN sent' WHEN 'INV' THEN 'EDI INV sent' END STATUS
, A.companyid COM, F.branch OORG
FROM b2b.b2b_850_m A
LEFT JOIN b2b.b2b_doc_d B ON B.doc_type = 'SO' AND A.pono = B.po_no
LEFT JOIN b2b.b2b_doc_d C ON C.doc_type = 'ASN' AND A.pono = C.po_no
LEFT JOIN b2b.b2b_856_m D ON A.pono = D.pono AND A.companyid = D.companyid
LEFT JOIN b2b.b2b_810_m E ON A.pono = E.pono AND A.companyid = E.companyid
LEFT JOIN b2b.b2b_doc_m F on A.pono = F.po_no
LEFT JOIN (select A.PO_NO, b.doc_type from
(select po_no, max(seq) seq from b2b.b2b_doc_d group by po_no) A
left join b2b.b2b_doc_d B on A.po_no = B.po_no and A.seq = B.seq) G on A.pono = G.po_no
WHERE 1 = 1 ";
//where
if (f.CST != "*")
sql += string.Format("AND A.companyid = '{0}' ", f.CST);
else
{
dynamic c = JsonConvert.DeserializeObject(cst_list);
string str_cst = "";
foreach (var cst in c)
{
str_cst += string.Format("'{0}', ", cst.TEXT);
}
if (str_cst.Length > 0)
sql += string.Format("AND A.companyid in ({0}) ", str_cst.Remove(str_cst.Length - 2));
}
if (f.PO != "")
sql += string.Format("AND A.pono = '{0}' ", f.PO);
if (f.SO != "")
sql += string.Format("AND B.doc_id = '{0}' ", f.SO);
if (f.ISA != "")
sql += string.Format("AND (A.confirm_isano = '{0}' OR D.isano = '{0}' OR E.isano = '{0}') ", f.ISA);
if (f.stDate == null && f.edDate == null)
f.edDate = DateTime.Now.ToShortDateString();
if (f.stDate != null || f.edDate != null)
{
DateTime start =
(f.stDate == null ? Convert.ToDateTime(f.edDate).AddDays(-90) : Convert.ToDateTime(f.stDate));
DateTime end =
(f.edDate == null ? Convert.ToDateTime(f.stDate).AddDays(90) : Convert.ToDateTime(f.edDate));
sql += string.Format("AND A.b2brectime between FRAMEWORK.PUBLIC_PKG.TimeZone_Converter('ACI','HQ', TO_DATE('{0} 00:00:00', 'YYYY/MM/DD HH24:MI:SS')) AND FRAMEWORK.PUBLIC_PKG.TimeZone_Converter('ACI','HQ', TO_DATE('{1} 23:59:59', 'YYYY/MM/DD HH24:MI:SS'))",
start.ToString("yyyy/MM/dd"),
end.ToString("yyyy/MM/dd"));
}
//order by
sql += string.Format(" ORDER BY {0} {1}"
, fieldMap.Where(x=>x[0] == order.Replace("-","")).First()[1]
, order.Contains("-") ? "DESC" : "ASC"
);
//paging
sql = string.Format(@"SELECT ROWNUM ""ROWID"", T.* FROM ({0}) T", sql, stRow, edRow);
DataTable dt = q.DoSelect(sql);
dynamic rtnPackage = new
{
itemCount = dt.Rows.Count,
items = dt.Rows.Count > 0 ? dt.AsEnumerable().Where(x => x.Field<decimal>("ROWID") >= stRow && x.Field<decimal>("ROWID") <= edRow).CopyToDataTable() : new DataTable()
};
return JsonConvert.SerializeObject(rtnPackage);
//return @"[{""CPO"":""1ZLKIRMD"",""ODATE"":""07/23/2015"",""SO"":""311212"",""DNO"":""311213"",""STATUS"":""EDI INV Sent""}]";
}
private void PreparePageReflectionInfo()
{
DbNewAssistant q = new DbNewAssistant("B2B_ASUSB2B_DB");
Customer = JsonConvert.SerializeObject(q.DoSelect(
@"SELECT class1 TEXT, class2 ""VALUE"" FROM asusb2b.b2bpt_dict WHERE dict_type = 'CusMap'"
));
//Customer = @"{""TEXT"":""BESTBUY"",""VALUE"":""BBY""}";
}
protected void Page_Load(object sender, EventArgs e)
{
DbNewAssistant q = new DbNewAssistant("B2B_ASUSB2B_DB");
DataTable dt = q.DoSelect(
@"SELECT class1 TEXT, class2 ""VALUE"" FROM asusb2b.b2bpt_dict WHERE dict_type = 'CusMap'"
);
if (!IsPostBack)
{
PreparePageReflectionInfo();
}
}
public static string SaveEdit(string param)
{
dynamic objRow = JsonConvert.DeserializeObject(param);
DbNewAssistant q_T01 = new DbNewAssistant("B2B_ASUSB2B_DB");
DbNewAssistant q_QC = new DbNewAssistant("TPEBIZ01");
string sql_T01 = string.Format(@"Insert into ASUSB2B.B2BPT_DICT (DICT_ID, DICT_TYPE, CLASS1, CLASS2, CLASS3, CLASS4, SEQ, COPYFLAG, CREATE_USER, CREATE_TIME) Values ((SELECT NVL(MAX(DICT_ID),0) + 1 FROM ASUSB2B.B2BPT_DICT WHERE DICT_TYPE = 'ACI-DISTI' AND CLASS1 = 'UPCMATCHING'), 'ACI-DISTI', 'UPCMATCHING', '{0}', '{1}', '{2}', (SELECT NVL(MAX(SEQ),0) + 1 FROM ASUSB2B.B2BPT_DICT WHERE DICT_TYPE = 'ACI-DISTI' AND CLASS1 = 'UPCMATCHING' AND CLASS4 = '{2}'), 'Y', 'EDILIFEQ', sysdate)",
objRow.UPCCODE,
objRow.ITEMNO_temp,
objRow.BLOC
);
string sql_QC = string.Format(@"update b2b.b2b_850_d set asuspartno = '{0}' where jobreference = '{1}' and lineno = '{2}'"
, objRow.ITEMNO_temp
, objRow.JREF
, objRow.LINENO
);
q_T01.DoCommand(sql_T01);
q_QC.DoCommand(sql_QC);
return "";
}
public static string ReSubmit(string param)
{
dynamic objRow = JsonConvert.DeserializeObject(param);
string flag = objRow.FLAG ?? "";
string msg = (objRow.ERRORMSG ?? "").ToString().TrimEnd().TrimStart();
//msg = "EDI price is not match the price book";
Assembly ass = Assembly.Load(AssemblyName.Value);
Type cls = ass.GetType(AssemblyName.Value + "." + msg.Replace(' ','_'));
ReSubmitLogic logic = new DefaultLogic();
if (cls != null)
logic = (ReSubmitLogic)Activator.CreateInstance(cls);
logic.RowObject = objRow;
logic.Do();
try
{
DbNewAssistant q = new DbNewAssistant("TPEBIZ01");
string sql = "";
sql = string.Format("SELECT company_id FROM b2b.b2b_doc_m WHERE po_no = '{0}' AND serial = '{1}' AND branch = '{2}'", objRow.CSTPO, objRow.HD_SCODE, objRow.OPUNIT);
string companyid = q.DoSelect(sql).Rows[0].Field<string>("company_id");
sql = string.Format("DELETE FROM b2b.b2b_doc_d WHERE po_no = '{0}' AND company_id = '{1}' AND branch = '{2}'", objRow.CSTPO, companyid, objRow.OPUNIT);
q.DoCommand(sql);
sql = string.Format("DELETE FROM b2b.b2b_doc_m WHERE po_no = '{0}' AND serial = '{1}' AND branch = '{2}'", objRow.CSTPO, objRow.HD_SCODE, objRow.OPUNIT);
q.DoCommand(sql);
}
catch (IndexOutOfRangeException ex)
{
return "data not found";
}
return "";
}
public static string LoadList(string pono, string oorg)
{
DbNewAssistant q = new DbNewAssistant("ETEAMQA6");
string sql = string.Format(@"SELECT
m.podate PODATE,
oohi.customer_name CSTN,
oohi.bill_to_location BLOC,
oohi.copyflag FLAG ,
m.pono CSTPO,
m.currency DAR,
oohi.order_org OPUNIT,
oohi.ship_to_location SHIPLOC,
oohi.order_number SONO,
ooli.order_line_num LINENO,
ooli.ordered_quantity QTY,
ooli.unit_selling_price UNITPRICE,
oohi.credit_user_remark SHIPDATE,
oohi.status_message ERRORMSG,
ooli.upccode UPCCODE,
oohi.jobreference JREF,
oohi.orig_sys_document_ref HD_SCODE,
CASE
WHEN oohi.copyflag = 'F' AND TRIM(oohi.status_message) = 'Asus part number is null' THEN 'Y'
ELSE 'N'
END EDIT
FROM b2b.b2b_850_m m, b2b.b2b_doc_m dm, b2b.xx_om_order_header_int oohi, b2b.xx_om_order_line_int ooli
WHERE m.pono = dm.po_no
AND dm.serial = oohi.orig_sys_document_ref
AND oohi.orig_sys_document_ref = ooli.orig_sys_document_ref
AND m.pono = '{0}'
AND dm.branch = '{1}'
AND oohi.copyflag IN ('Y','N','F')", pono, oorg);
//WHEN oohi.copyflag = 'Y' AND TRIM(oohi.status_message) = 'Success' THEN 'Y'
DataTable dt = q.DoSelect(sql);
return JsonConvert.SerializeObject(dt);
}
