private void GetData(string policyinfo)
{
try
{
string connStringSql = "Data Source=210.14.138.25,1436;Initial Catalog=PBDB;User ID=pb_user;Password=pb_userQWER@2468";
policyinfo = HttpUtility.UrlDecode(policyinfo, Encoding.GetEncoding("gb2312"));
//OnErrorNew("policyinfo内容：" + policyinfo, false);
#region 测试数据
//policyinfo = "<root><pol event=\"11\" id=\"ffffffff-fff1-9446-2935-201208022131\" rate=\"5.8\" aircom=\"TV\" from=\"CTU\" to=\"LXA/HGH/XNN\" flightno=\"\" flighscope=\"0\" routetype=\"1\" policytype=\"BSP-ET\" fromtime=\"2012-8-2 0:00:00\" totime=\"2012-8-31 23:59:59\" applyclass=\"R;V;G;Q;J;L;K;H;M;B;Y;A;F\" printtickfromtime=\"2012-08-02 00:00:00\" printticktotime=\"2012-08-31 23:59:59\" worktimefrom=\"0800\" worktimeto=\"2300\" paytype=\"1\" changerecord=\"0\" automata=\"0\" contype=\"1\" isspecial=\"0\" note=\"退票参考时间：1700RMK TJ AUTH CTU363\" center=\"\" isshared=\"0\" policyweek=\"1,2,3,4,5,6,0\" providerid=\"E0D76E0C-F910-41D9-8834-39884BA8F31C\" weekendworktimef=\"0830\" weekendworktimet=\"2230\" refundworktimeto=\"1700\" refundweekendworktimeto=\"1600\" officeid=\"CTU363\" /><pol event=\"11\" id=\"ffffffff-fff1-2208-2938-201208022132\" rate=\"5.8\" aircom=\"TV\" from=\"CKG\" to=\"XNN/LXA\" flightno=\"\" flighscope=\"0\" routetype=\"2\" policytype=\"B2B-ET\" fromtime=\"2012-8-2 0:00:00\" totime=\"2012-8-31 23:59:59\" applyclass=\"V;G;Q;J;L;K;H;M;B;Y;F\" printtickfromtime=\"2012-08-02 00:00:00\" printticktotime=\"2012-08-31 23:59:59\" worktimefrom=\"0800\" worktimeto=\"2300\" paytype=\"1\" changerecord=\"0\" automata=\"0\" contype=\"1\" isspecial=\"0\" note=\"退票参考时间：1700RMK TJ AUTH CTU363\" center=\"\" isshared=\"0\" policyweek=\"1,2,3,4,5,6,0\" providerid=\"E0D76E0C-F910-41D9-8834-39884BA8F31C\" weekendworktimef=\"0830\" weekendworktimet=\"2230\" refundworktimeto=\"1700\" refundweekendworktimeto=\"1600\" officeid=\"CTU363\" /><pol event=\"11\" id=\"ffffffff-fff1-4221-2940-201208022134\" rate=\"5.8\" aircom=\"TV\" from=\"XNN\" to=\"CKG/CTU\" flightno=\"\" flighscope=\"0\" routetype=\"1\" policytype=\"BSP-ET\" fromtime=\"2012-8-2 0:00:00\" totime=\"2012-8-31 23:59:59\" applyclass=\"V;G;Q;J;L;K;H;M;B;Y;F;D;Z;P;W;E;T\" printtickfromtime=\"2012-08-02 00:00:00\" printticktotime=\"2012-08-31 23:59:59\" worktimefrom=\"0800\" worktimeto=\"2300\" paytype=\"1\" changerecord=\"0\" automata=\"0\" contype=\"1\" isspecial=\"0\" note=\"退票参考时间：1700RMK TJ AUTH CTU363\" center=\"\" isshared=\"0\" policyweek=\"1,2,3,4,5,6,0\" providerid=\"E0D76E0C-F910-41D9-8834-39884BA8F31C\" weekendworktimef=\"0830\" weekendworktimet=\"2230\" refundworktimeto=\"1700\" refundweekendworktimeto=\"1600\" officeid=\"CTU363\" /></root>";
#endregion
policyinfo = policyinfo.Replace("", "");
StringReader rea = new StringReader(policyinfo);
DataSet ds = new DataSet();
XmlTextReader xmlReader = new XmlTextReader(rea);
ds.ReadXml(xmlReader);
string insertStr = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["event"].ToString().Trim() != "")
{
if (ds.Tables[0].Rows[i]["event"].ToString().Trim() == "11")//添加政策
{
try
{
DeletebookPolicy(connStringSql, "Tb_Ticket_bookPolicy", " 1=1 and PolicyId='" + ds.Tables[0].Rows[i]["id"].ToString().Trim() + "'");
Tb_Ticket_BookPolicy bookPolicy = new Tb_Ticket_BookPolicy();
bookPolicy.PolicyId = ds.Tables[0].Rows[i]["id"].ToString().Trim();
bookPolicy.PolicyState = 1;
bookPolicy.FromCity = ds.Tables[0].Rows[i]["from"].ToString().Trim().Replace("/", ",");
bookPolicy.ToCity = ds.Tables[0].Rows[i]["to"].ToString().Trim().Replace("/", ",");
bookPolicy.Airlines = ds.Tables[0].Rows[i]["aircom"].ToString().Trim();
bookPolicy.TripType = Convert.ToInt32(ds.Tables[0].Rows[i]["routetype"].ToString().Trim());
bookPolicy.A1 = Convert.ToInt32(ds.Tables[0].Rows[i]["isspecial"].ToString().Trim());
if (ds.Tables[0].Rows[i]["flighscope"].ToString().Trim() == "0")//全部
{
bookPolicy.Flight = "";
bookPolicy.NoFlight = "";
}
if (ds.Tables[0].Rows[i]["flighscope"].ToString().Trim() == "1")//适用航班
{
bookPolicy.Flight = ds.Tables[0].Rows[i]["flightno"].ToString().Trim();
bookPolicy.NoFlight = "";
}
else if (ds.Tables[0].Rows[i]["flighscope"].ToString().Trim() == "2")//不适用航班
{
bookPolicy.Flight = "";
bookPolicy.NoFlight = ds.Tables[0].Rows[i]["flightno"].ToString().Trim();
}
bookPolicy.EtcLimit = ds.Tables[0].Rows[i]["policyweek"].ToString().Trim();
if (ds.Tables[0].Rows[i]["policytype"].ToString().Trim() == "BSP-ET")
{
bookPolicy.TicketType = 2;
}
else if (ds.Tables[0].Rows[i]["policytype"].ToString().Trim() == "B2B-ET")
{
bookPolicy.TicketType = 1;
}
else
{
bookPolicy.TicketType = 0;
}
string sss = ds.Tables[0].Rows[i]["applyclass"].ToString().Trim();
if (!sss.StartsWith("/"))
{
sss = "/" + sss;
}
if (!sss.EndsWith("/"))
{
sss = sss + "/";
}
bookPolicy.Shipping = sss.Replace(";", "/");
decimal ra = decimal.Parse(ds.Tables[0].Rows[i]["rate"].ToString().Trim());
bookPolicy.PReturn = decimal.Parse((ra / 100).ToString("f3"));
bookPolicy.GTicketTimeE = ds.Tables[0].Rows[i]["printtickfromtime"].ToString().Trim() + "-" + ds.Tables[0].Rows[i]["printticktotime"].ToString().Trim();
bookPolicy.EffectiveDate = DateTime.Parse(ds.Tables[0].Rows[i]["fromTime"].ToString().Trim());
bookPolicy.ExpiryDate = DateTime.Parse(ds.Tables[0].Rows[i]["toTime"].ToString().Trim());
bookPolicy.GYHXNumber = ds.Tables[0].Rows[i]["providerid"].ToString().Trim();
bookPolicy.GYPTNumber = ds.Tables[0].Rows[i]["providerid"].ToString().Trim();
bookPolicy.Remark = ds.Tables[0].Rows[i]["note"].ToString().Trim();
bookPolicy.IsAuGTicket = Convert.ToInt32(ds.Tables[0].Rows[i]["automata"].ToString().Trim());
bookPolicy.AddTime = DateTime.Now;
bookPolicy.PolicySource = 5;
bookPolicy.PolicySourceName = "票盟";
bookPolicy.AddCpyName = "";
bookPolicy.AddCpyNo = "";
string f = ds.Tables[0].Rows[i]["worktimefrom"].ToString().Trim();
string t = ds.Tables[0].Rows[i]["worktimeto"].ToString().Trim();
if (f.Length == 2)
{
f = f + "00";
}
if (t.Length == 2)
{
t = t + "00";
}
if (f.IndexOf(":") < 0)
{
f = f.Substring(0, 2) + ":" + f.Substring(2, 2);
}
if (t.IndexOf(":") < 0)
{
t = t.Substring(0, 2) + ":" + t.Substring(2, 2);
}
bookPolicy.ProviderWorkTime = f + "-" + t;;
DeletebookPolicyNew(connStringSql, InsertbookPolicy(connStringSql, "Tb_Ticket_BookPolicy", bookPolicy));
//insertStr = insertStr + InsertbookPolicy(connStringSql, "Tb_Ticket_bookPolicy", bookPolicy);
OnErrorNew("添加SQL语句完成（添加政策）PolicyId：" + bookPolicy.PolicyId, false);
}
catch (Exception ex)
{
OnErrorNew("插入失败（添加政策），原因：" + ex.Message.ToString(), false);
}
}
if (ds.Tables[0].Rows[i]["event"].ToString().Trim() == "13")//删除政策
{
try
{
DeletebookPolicy(connStringSql, "Tb_Ticket_bookPolicy", " 1=1 and PolicyId='" + ds.Tables[0].Rows[i]["id"].ToString().Trim() + "'");
OnErrorNew("删除PolicyId完成（删除政策）PolicyId：" + ds.Tables[0].Rows[i]["id"].ToString().Trim(), false);
}
catch (Exception ex)
{
OnErrorNew("删除PolicyId失败，原因：" + ex.Message.ToString(), false);
}
}
}
}
//OnErrorNew("开始执行存储过程：" + DateTime.Now.ToString(), false);
//string sqlCollect = "exec Policy '" + insertStr + "'";
//DeletebookPolicyNew(connStringSql, sqlCollect);
//OnErrorNew("执行存储过程完成：" + DateTime.Now.ToString(), false);
Response.Write("0");//通知票盟成功
}
catch (Exception ex)
{
Response.Write("1");//通知票盟失败
OnErrorNew("执行失败，原因：" + ex.Message.ToString(), false);
}
}
private string InsertbookPolicy(string connectionString, string TableName, Tb_Ticket_BookPolicy bookPolicy)
{
string queryString = "insert into " + TableName + " (PolicySource,PolicySourceName,PolicyId,PolicyState,FromCity,ToCity,Airlines,TripType,Flight,NoFlight,EtcLimit,TicketType,Shipping,PReturn,GTicketTimeE,EffectiveDate,ExpiryDate,GYHXNumber,InsertDate,UpdateDate,GYPTNumber,IsAuGTicket,Remark,AddTime,AddCpyNo,AddCpyName,ProviderWorkTime,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20) values (" + bookPolicy.PolicySource + ",'" + bookPolicy.PolicySourceName + "','" + bookPolicy.PolicyId + "'," + bookPolicy.PolicyState + ",'" + bookPolicy.FromCity + "','" + bookPolicy.ToCity + "','" + bookPolicy.Airlines + "'," + bookPolicy.TripType + ",'" + bookPolicy.Flight + "','" + bookPolicy.NoFlight + "','" + bookPolicy.EtcLimit + "'," + bookPolicy.TicketType + ",'" + bookPolicy.Shipping + "'," + bookPolicy.PReturn + ",'" + bookPolicy.GTicketTimeE + "','" + bookPolicy.EffectiveDate + "','" + bookPolicy.ExpiryDate + "','" + bookPolicy.GYHXNumber + "',getdate(),'" + bookPolicy.UpdateDate + "','" + bookPolicy.GYPTNumber + "'," + bookPolicy.IsAuGTicket + ",'" + bookPolicy.Remark + "','" + bookPolicy.AddTime + "','" + bookPolicy.AddCpyNo + "','" + bookPolicy.AddCpyName + "','" + bookPolicy.ProviderWorkTime + "'," + bookPolicy.A1 + "," + bookPolicy.A2 + "," + bookPolicy.A3 + "," + bookPolicy.A4 + "," + bookPolicy.A5 + "," + bookPolicy.A6 + ",'" + bookPolicy.A7 + "','" + bookPolicy.A8 + "','" + bookPolicy.A9 + "','" + bookPolicy.A10 + "','" + bookPolicy.A11 + "','" + bookPolicy.A12 + "','" + bookPolicy.A13 + "','" + bookPolicy.A14 + "'," + bookPolicy.A15 + "," + bookPolicy.A16 + "," + bookPolicy.A17 + "," + bookPolicy.A18 + ",'" + bookPolicy.A19 + "','" + bookPolicy.A20 + "') ";
return(queryString);
}
