public void updatedb(List <dynamic> obj, DateTime indbT, string qrcode, string useddate)
{
DataTable objDT = new DataTable();
string myConnStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnectionStringBuilder sqlcsb = new SqlConnectionStringBuilder(myConnStr);
string completeConnStr = sqlcsb.ConnectionString;
string sql = "";
string indbt = string.Format("{0:yyyy-MM-dd HH:mm:ss}", indbT);
getdataFapi g = new getdataFapi();
try
{
using (SqlConnection _con = new SqlConnection(completeConnStr))
{
_con.Open();
for (int i = 0; i < obj.Count; i++)
{
g.TK_STATUS = obj[i]["status"].ToString();
if (g.TK_STATUS == "S000")
{
g.TK_FEEDBACKMEMO = "成功";
g.TK_FEEDBACK = "Y";
}
else if (g.TK_STATUS == "E003")
{
g.TK_FEEDBACKMEMO = "票券不存在,或非該場次票券";
g.TK_FEEDBACK = "Y";
}
else if (g.TK_STATUS == "E004")
{
g.TK_FEEDBACKMEMO = "該票券已使用";
g.TK_FEEDBACK = "Y";
}
else if (g.TK_STATUS == "E005")
{
g.TK_FEEDBACKMEMO = "該票券已無效";
g.TK_FEEDBACK = "Y";
}
else
{
g.TK_FEEDBACKMEMO = "ERROR";
g.TK_FEEDBACK = "N";
}
g.TK_PRICETYPES = obj[i]["priceType"].ToString();
//每五分鐘更新UsedDate
sql = string.Format(@"update cTicketWhitelist set TK_FEEDBACK='{0}',TK_FEEDBACKMEMO='{1}',MODIFYDT='{2:yyyy-MM-dd HH:mm:ss}',MODIFYID='{3}' where TK_QRCODE='{4}' and MODIFYDT = ''", g.TK_FEEDBACK, g.TK_FEEDBACKMEMO, DateTime.Now, "admin", qrcode);
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
_con.Close();
}
}
catch (Exception e)
{
// GetAllProducts();
}
}
public void insertdb(List <dynamic> obj, DateTime indbT)
{
DataTable objDT = new DataTable();
string myConnStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnectionStringBuilder sqlcsb = new SqlConnectionStringBuilder(myConnStr);
string completeConnStr = sqlcsb.ConnectionString;
//int totalcount = 300;
string sql = "";
//int insidecnt = 0;
//int dbin = 0;
//nt dbout = 0;
//string indbt = string.Format("{0:HH:mm}", indbT);
//string indbtup = string.Format("{0:HH:mm}", indbT.AddMinutes(-1));
string indbt = string.Format("{0:yyyy-MM-dd HH:mm:ss}", indbT);
getdataFapi g = new getdataFapi();
try
{
using (SqlConnection _con = new SqlConnection(completeConnStr))
{
_con.Open();
for (int i = 0; i < obj.Count; i++)
{
g.TK_QRCODE = obj[i]["QR_Code"].ToString();//obj["data"][i]["TK_QRCODE"].ToString();
g.TK_ORDERNO = obj[i]["Orders_No"].ToString();
g.TK_PRICETYPES = obj[i]["PriceTypes"].ToString();
g.TK_PRICE = Convert.ToInt32(obj[i]["Price"].ToString());
g.TK_ORDERDT = obj[i]["Order_datetime"].ToString();
g.TK_PLACE = "ibon";
g.TK_STATUS = obj[i]["Order_Type"].ToString(); //B:購票 R:退票
g.TK_USED_DT = "";
g.TK_END_DT = "";
g.TK_FEEDBACK = "";
g.TK_FEEDBACKMEMO = "";
g.CREATEDT = indbt;
g.CREATEID = "admin";
g.MODIFYDT = null;
g.MODIFYID = null;
if (!GetDataDB(g.TK_QRCODE))
{
if (g.TK_STATUS == "R")
{
sql = string.Format(@"insert into cTicketWhitelist
(TK_QRCODE
,TK_ORDERNO
,TK_PRICETYPES
,TK_PRICE
,TK_ORDERDT
,TK_PLACE
,TK_STATUS
,TK_USED_DT
,TK_END_DT
,TK_FEEDBACK
,TK_FEEDBACKMEMO
,CREATEDT
,CREATEID
,MODIFYDT
,MODIFYID
)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"
, g.TK_QRCODE
, g.TK_ORDERNO
, g.TK_PRICETYPES
, g.TK_PRICE
, g.TK_ORDERDT
, g.TK_PLACE
, "X"
, g.TK_USED_DT
, g.TK_END_DT
, g.TK_FEEDBACK
, g.TK_FEEDBACKMEMO
, g.CREATEDT
, g.CREATEID
, g.MODIFYDT
, g.MODIFYID
); //1:售出 2:已使用 X:作廢
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
else
{
sql = string.Format(@"insert into cTicketWhitelist
(TK_QRCODE
,TK_ORDERNO
,TK_PRICETYPES
,TK_PRICE
,TK_ORDERDT
,TK_PLACE
,TK_STATUS
,TK_USED_DT
,TK_END_DT
,TK_FEEDBACK
,TK_FEEDBACKMEMO
,CREATEDT
,CREATEID
,MODIFYDT
,MODIFYID
)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"
, g.TK_QRCODE
, g.TK_ORDERNO
, g.TK_PRICETYPES
, g.TK_PRICE
, g.TK_ORDERDT
, g.TK_PLACE
, "1"
, g.TK_USED_DT
, g.TK_END_DT
, g.TK_FEEDBACK
, g.TK_FEEDBACKMEMO
, g.CREATEDT
, g.CREATEID
, g.MODIFYDT
, g.MODIFYID
);
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
}
//else
//{
// sql = string.Format(@"insert into cTicketWhitelist
// (TK_QRCODE
// ,TK_ORDERNO
// ,TK_PRICETYPES
// ,TK_PRICE
// ,TK_ORDERDT
// ,TK_PLACE
// ,TK_STATUS
// ,TK_USED_DT
// ,TK_END_DT
// ,TK_FEEDBACK
// ,TK_FEEDBACKMEMO
// ,CREATEDT
// ,CREATEID
// ,MODIFYDT
// ,MODIFYID
// )
// values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"
// ,g.TK_QRCODE
// ,g.TK_ORDERNO
// ,g.TK_PRICETYPES
// ,g.TK_PRICE
// ,g.TK_ORDERDT
// ,g.TK_PLACE
// ,g.TK_STATUS
// ,g.TK_USED_DT
// ,g.TK_END_DT
// ,g.TK_FEEDBACK
// ,g.TK_FEEDBACKMEMO
// ,g.CREATEDT
// ,g.CREATEID
// ,g.MODIFYDT
// ,g.MODIFYID
// );
//}
//SqlCommand cmd = new SqlCommand(sql, _con);
//cmd.ExecuteNonQuery();
}
_con.Close();
}
}
catch (Exception e)
{
// GetAllProducts();
}
}
private async void CreateProductAsync()
{
string beginT = string.Format("{0:yyyy/MM/dd HH:00:00}", DateTime.Now);
string endT = string.Format("{0:yyyy/MM/dd HH:00:00}", DateTime.Now.AddHours(1));
string Pro_code = (string)checkBox1.Text.ToString();
string Pro_code2 = (string)checkBox2.Text.ToString();
WTicket ticket = new WTicket
{
Products_Code = "B01LITNY",
Orders_STIME = beginT,
Orders_ETIME = endT,
Block = Block
};
Block++;
HttpClient client = new HttpClient();
HttpResponseMessage response = await client.PostAsync("http://210.61.47.134/QWARE_TICKET_ADMIN/WebServices/ibonTicket.asmx/GetTicketData", new StringContent(
new JavaScriptSerializer().Serialize(ticket), Encoding.UTF8, "application/json"));
response.EnsureSuccessStatusCode();
string myConnStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
if (response.IsSuccessStatusCode)
{
// Get the response
var customerJsonString = await response.Content.ReadAsStringAsync();
// Deserialise the data (include the Newtonsoft JSON Nuget package if you don't already have it)
var deserialized = JsonConvert.DeserializeObject <IEnumerable <WTicketResp> >(customerJsonString);
// Do something with it
getdataFapi g = new getdataFapi();
string sql = "";
try
{
using (SqlConnection _con = new SqlConnection(myConnStr))
{
_con.Open();
foreach (WTicketResp wTicketResp in deserialized)
{
g.TK_QRCODE = wTicketResp.QR_Code;
g.TK_ORDERNO = wTicketResp.Orders_No;
g.TK_PRICETYPES = wTicketResp.PriceTypes;
g.TK_PRICE = Convert.ToInt32(wTicketResp.Price);
g.TK_ORDERDT = wTicketResp.Order_datetime;
g.TK_PLACE = "ibon";
g.TK_STATUS = wTicketResp.Order_Type; //B:購票 R:退票
g.TK_USED_DT = "";
g.TK_END_DT = "";
g.TK_FEEDBACK = "";
g.TK_FEEDBACKMEMO = "";
g.CREATEDT = string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);;
g.CREATEID = "admin";
g.MODIFYDT = null;
g.MODIFYID = null;
if (!GetDataDB(g.TK_QRCODE))
{
if (g.TK_STATUS == "R")
{
sql = string.Format(@"insert into cTicketWhitelist
(TK_QRCODE
,TK_ORDERNO
,TK_PRICETYPES
,TK_PRICE
,TK_ORDERDT
,TK_PLACE
,TK_STATUS
,TK_USED_DT
,TK_END_DT
,TK_FEEDBACK
,TK_FEEDBACKMEMO
,CREATEDT
,CREATEID
,MODIFYDT
,MODIFYID
)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"
, g.TK_QRCODE
, g.TK_ORDERNO
, g.TK_PRICETYPES
, g.TK_PRICE
, g.TK_ORDERDT
, g.TK_PLACE
, "X"
, g.TK_USED_DT
, g.TK_END_DT
, g.TK_FEEDBACK
, g.TK_FEEDBACKMEMO
, g.CREATEDT
, g.CREATEID
, g.MODIFYDT
, g.MODIFYID
); //1:售出 2:已使用 X:作廢
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
else
{
sql = string.Format(@"insert into cTicketWhitelist
(TK_QRCODE
,TK_ORDERNO
,TK_PRICETYPES
,TK_PRICE
,TK_ORDERDT
,TK_PLACE
,TK_STATUS
,TK_USED_DT
,TK_END_DT
,TK_FEEDBACK
,TK_FEEDBACKMEMO
,CREATEDT
,CREATEID
,MODIFYDT
,MODIFYID
)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')"
, g.TK_QRCODE
, g.TK_ORDERNO
, g.TK_PRICETYPES
, g.TK_PRICE
, g.TK_ORDERDT
, g.TK_PLACE
, "1"
, g.TK_USED_DT
, g.TK_END_DT
, g.TK_FEEDBACK
, g.TK_FEEDBACKMEMO
, g.CREATEDT
, g.CREATEID
, g.MODIFYDT
, g.MODIFYID
);
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
}
}
_con.Close();
}
}
catch (Exception e)
{
}
}
// return URI of the created resource.
//return response.Headers.Location;
}
private async void CreateProductAsync()
{
DataTable DT = new DataTable();
GetDataDB(ref DT);
QTicket[] qtic = new QTicket[DT.Rows.Count];
for (int i = 0; i < DT.Rows.Count; i++)
{
qtic[i] = new QTicket
{
TK_QRCODE = DT.Rows[i]["TK_QRCODE"].ToString(),
TK_USED_DT = DT.Rows[i]["TK_USED_DT"].ToString(),
};
}
HttpClient client = new HttpClient();
HttpResponseMessage response = await client.PostAsync("http://*****:*****@"update cTicketWhitelist set TK_FEEDBACK='{0}',TK_FEEDBACKMEMO='{1}',MODIFYDT='{2:yyyy-MM-dd HH:mm:ss}',MODIFYID='{3}' where TK_QRCODE='{4}'", g.TK_FEEDBACK, g.TK_FEEDBACKMEMO, DateTime.Now, "admin", qTicketResp.TK_QRCODE);
SqlCommand cmd = new SqlCommand(sql, _con);
cmd.ExecuteNonQuery();
}
_con.Close();
}
}
catch (Exception e)
{
}
}
// return URI of the created resource.
//return response.Headers.Location;
}