private TrainNum getTrainNum(DateTime date, string trainNum)
{
TrainNum train = new TrainNum();
string sql = "SELECT * FROM TrainNum WHERE trainCode='" + trainNum + "' AND startDate='" + date.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "'";
DataTable dt = new DataTable();
dt = SqlHelper.ExecuteDataTable(sql);
train.TrainType = dt.DefaultView.Table.Rows[0][1].ToString();
train.trainCode = dt.DefaultView.Table.Rows[0][2].ToString();
train.startSite = dt.DefaultView.Table.Rows[0][3].ToString();
train.endSite = dt.DefaultView.Table.Rows[0][4].ToString();
train.startTime = dt.DefaultView.Table.Rows[0][5].ToString();
train.endTime = dt.DefaultView.Table.Rows[0][6].ToString();
train.spendTime = dt.DefaultView.Table.Rows[0][7].ToString();
train.startDate = date;
train.swz_num = int.Parse(dt.DefaultView.Table.Rows[0][9].ToString());
train.yd_num = int.Parse(dt.DefaultView.Table.Rows[0][10].ToString());
train.ed_num = int.Parse(dt.DefaultView.Table.Rows[0][11].ToString());
train.yz_num = int.Parse(dt.DefaultView.Table.Rows[0][12].ToString());
train.yw_num = int.Parse(dt.DefaultView.Table.Rows[0][13].ToString());
train.wz_num = int.Parse(dt.DefaultView.Table.Rows[0][14].ToString());
train.rz_num = int.Parse(dt.DefaultView.Table.Rows[0][15].ToString());
train.gr_num = int.Parse(dt.DefaultView.Table.Rows[0][16].ToString());
train.rw_num = int.Parse(dt.DefaultView.Table.Rows[0][17].ToString());
train.dw_num = int.Parse(dt.DefaultView.Table.Rows[0][18].ToString());
train.qt_num = int.Parse(dt.DefaultView.Table.Rows[0][19].ToString());
train.weekday = dt.DefaultView.Table.Rows[0][20].ToString();
return(train);
}
private void update_Click(object sender, EventArgs e)
{
TrainNum train = new TrainNum();
train.TrainType = this.trainType.Text;
train.trainCode = this.trainCode.Text;
train.startSite = this.startSite.Text;
train.endSite = this.endSite.Text;
train.startTime = this.startTime.Text;
train.endTime = this.endTime.Text;
train.spendTime = this.spendTime.Text;
train.swz_num = int.Parse(this.swz.Text);
train.yd_num = int.Parse(this.ydz.Text);
train.ed_num = int.Parse(this.edz.Text);
train.gr_num = int.Parse(this.gjrw.Text);
train.rw_num = int.Parse(this.rwydw.Text);
train.dw_num = int.Parse(this.dw.Text);
train.yz_num = int.Parse(this.yz.Text);
train.rz_num = int.Parse(this.rz.Text);
train.wz_num = int.Parse(this.wz.Text);
train.yw_num = int.Parse(this.ywedw.Text);
train.startDate = DateTime.Parse(this.dateTimePicker1.Text);
train.weekday = this.weekday.Text;
train.qt_num = 0;
if (updateTrain(train) > 0)
{
MessageBox.Show("修改成功", "提示", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("修改失败，请再次尝试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void FrmPurchase_Load(object sender, EventArgs e)
{
#region CheckBox加载
//int x=30, y=20;
// List<string> personList = getAllPasserger(userName);
// foreach (string personName in personList)
// {
// Font font=new Font("微软雅黑", 12);
// CheckBox chkbox = new CheckBox();
// chkbox.Location = new Point(x, y);
// chkbox.Name = "chk"+personList.IndexOf(personName);
// chkbox.Text = personName.ToString();
// chkbox.Font = font;
// chkbox.CheckedChanged+=new EventHandler(chkbox_CheckedChanged);
// this.groupBox1.Controls.Add(chkbox);
// if (x<900)
// {
// x += 170;
// }
// else
// {
// break;
// }
// }
string name = pName;
#endregion
Purchase purchase = GetPersonInfo(userName, name);
this.dgvselect.Rows[0].Cells[0].Value = purchase.ID.ToString();
this.dgvselect.Rows[0].Cells[3].Value = purchase.PersonName;
this.dgvselect.Rows[0].Cells[4].Value = purchase.IDType;
this.dgvselect.Rows[0].Cells[5].Value = purchase.IDNum;
this.dgvselect.Rows[0].Cells[6].Value = purchase.PhoneNum;
DataGridViewComboBoxCell cell = this.dgvselect.Rows[0].Cells[1] as DataGridViewComboBoxCell;
if (cell != null)
{
cell.Items.Add("成人票");
cell.Items.Add("学生票");
cell.Items.Add("儿童票");
cell.Items.Add("军残票");
cell.Value = "成人票";
}
TrainNum train = getTrainNum(date, trainNum);
string startSite = train.startSite;
string endSite = train.endSite;
List <Purchase> purchases = GetPriceInfo(startSite, endSite);
DataGridViewComboBoxCell seatCell = this.dgvselect.Rows[0].Cells[2] as DataGridViewComboBoxCell;
for (int i = 0; i < purchases.Count; i++)
{
if (seatCell != null)
{
seatCell.Items.Add(purchases[i].SeatType);
}
}
seatCell.Value = seatCell.Items[0].ToString();
}
private int changeOrder(TrainNum train, Purchase purchase, string userName)
{
string steatType = purchase.SeatType.Split('（')[0];
decimal price = decimal.Parse(purchase.SeatType.Substring(3, 4));
string sql = "UPDATE dbo.TicketOrder SET TrainType='" + train.TrainType + "',trainCode='" + train.trainCode + "',startSite='" + train.startSite + "',endSite='" + train.endSite + "',startTime='" + train.startTime + "',endTime='" + train.endTime + "',startDate='" + train.startDate.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "',personName='" + purchase.PersonName + "',IDType='" + purchase.IDType + "',IDNum='" + purchase.IDNum + "',carriageNum='" + random(2, 8) + "',seatNum='" + random(1, 50) + "',seatType='" + steatType + "',passengerType='" + purchase.TicketType + "',ticketPrice=" + price + ",userName='******',phoneNum='" + purchase.PhoneNum + "',orderState=0,orderDate='" + DateTime.Today.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "' where userName='******'AND personName='" + purchase.PersonName + "'AND startDate = '" + train.startDate + "'AND trainCode = '" + train.trainCode + "'";
return(SqlHelper.ExecuteNonQuery(sql));
}
private int submitOrder(TrainNum train, Purchase purchase, string userName, string orderNum)
{
string steatType = purchase.SeatType.Split('（')[0];
decimal price = decimal.Parse(purchase.SeatType.Substring(3, 4));
string sql = "INSERT INTO TicketOrder(TrainType,trainCode,startSite,endSite,startTime,endTime,startDate,personName,IDType,IDNum,carriageNum,seatNum,seatType,passengerType,ticketPrice,userName,phoneNum,orderState,orderDate)VALUES('" + train.TrainType + "','" + train.trainCode + "','" + train.startSite + "','" + train.endSite + "','" + train.startTime + "','" + train.endTime + "','" + train.startDate.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "','" + purchase.PersonName + "','" + purchase.IDType + "','" + purchase.IDNum + "','" + random(2, 8) + "','" + random(1, 50) + "','" + steatType + "','" + purchase.TicketType + "'," + price + ",'" + userName + "','" + purchase.PhoneNum + "',0,'" + DateTime.Today.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "')";
return(SqlHelper.ExecuteNonQuery(sql));
}
public FrmOrderConf(TrainNum trainNum, Purchase purchase, string userName)
{
this.StartPosition = FormStartPosition.CenterScreen;
InitializeComponent();
this.purchase = purchase;
this.userName = userName;
this.train = trainNum;
}
public FrmChangeOrderConf(TrainNum trainNum, Purchase purchase, string userName, decimal margin)
{
this.StartPosition = FormStartPosition.CenterScreen;
InitializeComponent();
this.purchase = purchase;
this.userName = userName;
this.train = trainNum;
this.margin = margin;
}
public FrmChangeCheckInfo(TrainNum trainNum, Purchase purchase, string userName, Order order)
{
this.StartPosition = FormStartPosition.CenterScreen;
InitializeComponent();
this.purchase = purchase;
this.userName = userName;
this.train = trainNum;
this.order = order;
}
private void get_Click(object sender, EventArgs e)
{
TrainNum trainNum = getTrainNum(this.dateTimePicker1.Text, this.trainCode.Text);
this.swz.Text = trainNum.swz_num.ToString();
this.ydz.Text = trainNum.yd_num.ToString();
this.edz.Text = trainNum.ed_num.ToString();
this.gjrw.Text = trainNum.gr_num.ToString();
this.rwydw.Text = trainNum.rw_num.ToString();
this.dw.Text = trainNum.dw_num.ToString();
this.rr.Text = trainNum.rz_num.ToString();
this.aa.Text = trainNum.yz_num.ToString();
this.wz.Text = trainNum.wz_num.ToString();
this.ywedw.Text = trainNum.yw_num.ToString();
}
private void add_Click(object sender, EventArgs e)
{
Carriage carriage = new Carriage();
carriage.trainType = this.trainType.Text.ToCharArray()[0];
carriage.trainCode = this.trainCode.Text;
carriage.carriageNum = int.Parse(this.carriageNum.Text);
carriage.carriageType = this.carriageType.Text;
carriage.seatCount = int.Parse(this.seatNum.Text);
carriage.startDate = DateTime.Parse(this.dateTimePicker1.Text);
TrainNum trainNum = getTrainNum(this.dateTimePicker1.Text, this.trainCode.Text);
TrainSeat seat = new TrainSeat();
seat.seatType = this.seatType.Text;
seat.trainCode = this.trainCode.Text;
seat.carriageNum = this.carriageNum.Text;
seat.carriageType = this.carriageType.Text;
seat.carriageSeatNum = int.Parse(this.seatNum.Text);
seat.seatType = this.seatType.Text;
seat.startSite = trainNum.startSite;
seat.endSite = trainNum.endSite;
seat.startDate = this.dateTimePicker1.Text;
if (addCarriage(carriage) > 0)
{
for (int i = 1; i <= int.Parse(this.seatNum.Text); i++)
{
if (addSeat(seat, i) > 0)
{
MessageBox.Show("添加成功", "提示", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("添加座位失败，请再次尝试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
deleteSeat(seat);
}
}
}
else
{
MessageBox.Show("添加失败，请再次尝试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private List <TrainNum> getTrainNum(string date, string startSite, string endSite)
{
List <TrainNum> trains = new List <TrainNum>();
string sql = "SELECT * FROM TrainNum WHERE startSite='" + startSite + "' AND endSite='" + endSite + "' AND startDate='" + date + "'";
DataTable dt = new DataTable();
dt = SqlHelper.ExecuteDataTable(sql);
for (int i = 0; i < dt.DefaultView.Table.Rows.Count; i++)
{
TrainNum train = new TrainNum();
train.TrainType = dt.DefaultView.Table.Rows[i][1].ToString();
train.trainCode = dt.DefaultView.Table.Rows[i][2].ToString();
train.startSite = dt.DefaultView.Table.Rows[i][3].ToString();
train.endSite = dt.DefaultView.Table.Rows[i][4].ToString();
train.startTime = dt.DefaultView.Table.Rows[i][5].ToString();
train.endTime = dt.DefaultView.Table.Rows[i][6].ToString();
train.spendTime = dt.DefaultView.Table.Rows[i][7].ToString();
train.startDate = DateTime.Parse(date);
train.swz_num = int.Parse(dt.DefaultView.Table.Rows[i][9].ToString());
train.yd_num = int.Parse(dt.DefaultView.Table.Rows[i][10].ToString());
train.ed_num = int.Parse(dt.DefaultView.Table.Rows[i][11].ToString());
train.yz_num = int.Parse(dt.DefaultView.Table.Rows[i][12].ToString());
train.yw_num = int.Parse(dt.DefaultView.Table.Rows[i][13].ToString());
train.wz_num = int.Parse(dt.DefaultView.Table.Rows[i][14].ToString());
train.rz_num = int.Parse(dt.DefaultView.Table.Rows[i][15].ToString());
train.gr_num = int.Parse(dt.DefaultView.Table.Rows[i][16].ToString());
train.rw_num = int.Parse(dt.DefaultView.Table.Rows[i][17].ToString());
train.dw_num = int.Parse(dt.DefaultView.Table.Rows[i][18].ToString());
train.qt_num = int.Parse(dt.DefaultView.Table.Rows[i][19].ToString());
train.weekday = dt.DefaultView.Table.Rows[i][20].ToString();
train.startToendSite = train.startSite + " - " + train.endSite;
train.startToendTime = train.startTime + " - " + train.endTime;
trains.Add(train);
}
return(trains);
}
private void btnSubmit_Click(object sender, EventArgs e)
{
Purchase purchase = new Purchase();
TrainNum train = getTrainNum(date, trainNum);
purchase.ID = int.Parse(this.dgvselect.Rows[0].Cells[0].Value.ToString());
purchase.TicketType = this.dgvselect.Rows[0].Cells[1].Value.ToString();
purchase.SeatType = this.dgvselect.Rows[0].Cells[2].Value.ToString();
purchase.PersonName = this.dgvselect.Rows[0].Cells[3].Value.ToString();
purchase.IDType = this.dgvselect.Rows[0].Cells[4].Value.ToString();
purchase.IDNum = this.dgvselect.Rows[0].Cells[5].Value.ToString();
purchase.PhoneNum = this.dgvselect.Rows[0].Cells[6].Value.ToString();
FrmChangeCheckInfo checkInfo = new FrmChangeCheckInfo(train, purchase, userName, order);
this.Hide();
DialogResult dr = checkInfo.ShowDialog();
if (dr == DialogResult.Cancel)
{
this.Show();
}
}
private TrainNum getTrainNum(string date, string trainCode
)
{
string sql = "SELECT * FROM TrainNum WHERE trainCode='" + trainCode + "' AND startDate='" + date + "'";
DataTable dt = new DataTable();
dt = SqlHelper.ExecuteDataTable(sql);
#region 获取车次信息
TrainNum train = new TrainNum();
train.TrainType = dt.DefaultView.Table.Rows[0][1].ToString();
train.trainCode = dt.DefaultView.Table.Rows[0][2].ToString();
train.startSite = dt.DefaultView.Table.Rows[0][3].ToString();
train.endSite = dt.DefaultView.Table.Rows[0][4].ToString();
train.startTime = dt.DefaultView.Table.Rows[0][5].ToString();
train.endTime = dt.DefaultView.Table.Rows[0][6].ToString();
train.spendTime = dt.DefaultView.Table.Rows[0][7].ToString();
train.startDate = DateTime.Parse(date);
train.swz_num = int.Parse(dt.DefaultView.Table.Rows[0][9].ToString());
train.yd_num = int.Parse(dt.DefaultView.Table.Rows[0][10].ToString());
train.ed_num = int.Parse(dt.DefaultView.Table.Rows[0][11].ToString());
train.yz_num = int.Parse(dt.DefaultView.Table.Rows[0][12].ToString());
train.yw_num = int.Parse(dt.DefaultView.Table.Rows[0][13].ToString());
train.wz_num = int.Parse(dt.DefaultView.Table.Rows[0][14].ToString());
train.rz_num = int.Parse(dt.DefaultView.Table.Rows[0][15].ToString());
train.gr_num = int.Parse(dt.DefaultView.Table.Rows[0][16].ToString());
train.rw_num = int.Parse(dt.DefaultView.Table.Rows[0][17].ToString());
train.dw_num = int.Parse(dt.DefaultView.Table.Rows[0][18].ToString());
train.qt_num = int.Parse(dt.DefaultView.Table.Rows[0][19].ToString());
train.weekday = dt.DefaultView.Table.Rows[0][20].ToString();
train.startToendSite = train.startSite + " - " + train.endSite;
train.startToendTime = train.startTime + " - " + train.endTime;
#endregion
return(train);
}
private int getTrain(TrainNum train)
{
string sql = " SELECT COUNT(*) FROM dbo.TrainNum WHERE trainCode='" + train.trainCode + "' AND startDate='" + train.startDate + "'";
return(int.Parse(SqlHelper.ExecuteScalar(sql).ToString()));
}
private int addTrain(TrainNum train)
{
string sql = "INSERT INTO dbo.TrainNum(TrainType,trainCode,startSite,endSite,startTime,endTime,spendTime,startDate,swz_num,yd_num,ed_num,yz_num,yw_num,wz_num,rz_num,gr_num,rw_num,dw_num,qt_num,weekdays)VALUES('" + train.TrainType + "', '" + train.trainCode + "', '" + train.startSite + "', '" + train.endSite + "', '" + train.startTime + "', '" + train.endTime + "', '" + train.spendTime + "',' " + train.startDate + "', " + train.swz_num + ", " + train.yd_num + ", " + train.ed_num + ", " + train.yz_num + ", " + train.yw_num + ", " + train.wz_num + ", " + train.rz_num + ", " + train.gr_num + ", " + train.rw_num + ", " + train.dw_num + ", " + train.qt_num + ", '" + train.weekday + "')";
return(SqlHelper.ExecuteNonQuery(sql));
}
private int updateOrderState(Purchase purchase, TrainNum train)
{
string sql = "UPDATE TicketOrder SET orderState = 2 WHERE userName='******'AND personName='" + purchase.PersonName + "'AND startDate = '" + train.startDate + "'AND trainCode = " + train.trainCode + "'";
return(SqlHelper.ExecuteNonQuery(sql));
}
private int getOrderSum(TrainNum train, string userName, string personName)
{
string sql = " SELECT count(*) FROM TicketOrder WHERE trainCode='" + train.trainCode + "' AND userName='******' AND startDate='" + train.startDate.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "'AND personName='" + personName + "'";
return(int.Parse(SqlHelper.ExecuteScalar(sql).ToString()));
}
public TrainUpdate(TrainNum train)
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
this.train = train;
}
private int updateTrain(TrainNum train)
{
string sql = "UPDATE dbo.TrainNum SET TrainType='" + train.TrainType + "',trainCode='" + train.trainCode + "',startSite='" + train.startSite + "',endSite='" + train.endSite + "',startTime='" + train.startTime + "',endTime='" + train.endTime + "',spendTime='" + train.spendTime + "',startDate='" + train.startDate + "', swz_num =" + train.swz_num + ", yd_num =" + train.yd_num + ", yw_num =" + train.yw_num + ", wz_num =" + train.wz_num + ", rz_num =" + train.rz_num + ", gr_num =" + train.gr_num + ", rw_num =" + train.rw_num + ", dw_num =" + train.dw_num + ", qt_num =" + train.qt_num + ", weekdays = '" + train.weekday + "' where trainCode='" + train.trainCode + "'";
return(SqlHelper.ExecuteNonQuery(sql));
}