private string SaveVoucher()
{
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
string lsReturn = "";
string lsSQL = "", lsVoucher = "";
int i = 0;
string ddd = "";
try
{
Pb1.Visible = true;
Pb1.Minimum = 0;
Connection lsGdb1 = new Connection();
lsGdb1.ConnectDatabase();
DateTime ld = new DateTime();
DateTime ldVouDate = new DateTime();
//Voucher lstblVou;// = new Voucher();
Voucher lstblVou = new Voucher();
//List<Voucher> lstblVouList = new List<Voucher>();
Int32 li = Convert.ToInt32(CboMonth.ComboBox.SelectedValue.ToString());
lsStartDate = CboYear.Text + "-" + li.ToString("00") + "-01";
ld = lsGdb.LastDayofMonth(Convert.ToDateTime(lsStartDate));
lsStartDate = (Convert.ToInt16(CboYear.Text) - 543) + "-" + li.ToString("00") + "-01";
lsEndDate = ld.Year.ToString("0000") + "-" + ld.Month.ToString("00") + "-" + ld.Day.ToString("00");
lsSQL = "Delete From accrecvoucher Where voudate >= '" + lsStartDate + "' and voudate <= '" + lsEndDate + "'";
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
lsComm.ExecuteNonQuery();
lsSQL = "Select count(*) as cnt From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '" + lsEndDate + "'";
lsComm.CommandText = lsSQL;
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
if (lsRead.HasRows)
{
while (lsRead.Read())
{
Pb1.Maximum = Convert.ToInt16(lsRead["cnt"]);
}
}
lsRead.Close();
for (int j = 0; j < Pb1.Maximum; j += 100)
{
if (lsGdb.Gdb.State == ConnectionState.Closed)
{
if (lsGdb.ConnectDatabase() == false)
{
//CloseForm();
}
}
else
{
lsGdb.Gdb.Close();
lsGdb.Gdb.Open();
//lsGdb.ConnectDatabase();
}
lsComm.Dispose();
lsSQL = "Select * From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '" + lsEndDate + "' limit " + j + "," + "100 ";
lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
//lsComm.CommandText = lsSQL;
lsRead = lsComm.ExecuteReader();
if (lsRead.HasRows)
{
while (lsRead.Read())
{
//lstblVou = new Voucher();
lstblVou.TableName = "accrecvoucher";
lstblVou.HostName = "local";
lstblVou.VouDate = Convert.ToDateTime(lsRead["voudate"]);
lstblVou.VouNO = lsRead["vouno"].ToString();
lstblVou.GuestFirstName = lsRead["guestfirstname"].ToString();
lstblVou.GuestLastName = lsRead["guestlastname"].ToString();
lstblVou.ResRooms = Convert.ToInt32(lsRead["resrooms"]);
lstblVou.ProvCode = lsRead["provcode"].ToString();
lstblVou.HotelCode = lsRead["hotelcode"].ToString();
lstblVou.RoomCode = lsRead["roomcode"].ToString();
lstblVou.ResTime = lsRead["restime"].ToString();
lsSQL = lsRead["shiftcode"].ToString();
lstblVou.ShiftCode = lsRead["shiftcode"].ToString();
//lstblVou.CheckOutTime = aaaa;
lsSQL = lsRead["checkindate"].ToString();
lstblVou.CheckInTime = Convert.ToDateTime(lsRead["checkindate"].ToString());
lsSQL = lsRead["checkoutdate"].ToString();
lstblVou.CheckOutTime = Convert.ToDateTime(lsRead["checkoutdate"].ToString());
lstblVou.DepositAMT = Convert.ToDecimal(lsRead["depositamt"].ToString());
lsSQL = lsRead["roomrate"].ToString();
lstblVou.RoomRate = Convert.ToDecimal(lsRead["roomrate"].ToString());
lstblVou.VisitT = Convert.ToInt32(lsRead["visitt"].ToString());
lstblVou.StaffCode = lsRead["staffcode"].ToString();
lstblVou.StatusCode = lsRead["statuscode"].ToString();
lstblVou.ConfirmPerson = lsRead["confirmperson"].ToString();
lstblVou.Remark = lsRead["remark"].ToString();
lstblVou.RoomNO = lsRead["roomno"].ToString();
lstblVou.NationCode = lsRead["nationcode"].ToString();
lstblVou.PreFix = Convert.ToInt32(lsRead["prefix"].ToString());
lstblVou.MAC = lsRead["mac"].ToString();
lstblVou.Counter1 = lsRead["counter1"].ToString();
lstblVou.PersoninTrip = Convert.ToInt32(lsRead["personintrip"].ToString());
lstblVou.RoomRate1 = Convert.ToDecimal(lsRead["roomrate1"]);
lstblVou.MemPlCode = lsRead["memplcode"].ToString();
lstblVou.PriceEnd = Convert.ToDecimal(lsRead["priceend"].ToString());
lstblVou.Taxi = lsRead["taxi"].ToString();
lstblVou.Breakfast = lsRead["breakfast"].ToString();
lstblVou.Flag = lsRead["flag"].ToString();
//lstblVouList.Add(lstblVou);
lsVoucher = lstblVou.CreateVoucher(lsGdb1.Gdb);
i++;
if (i > 1289)
{
//i++;
}
Pb1.Value = i;
}
}
lsRead.Close();
}
/*for (int j = 0; j < Pb1.Maximum;j++)
* {
* lsVoucher=lstblVouList[0].CreateVoucher(lsGdb1.Gdb);
* lstblVouList.Remove(lstblVouList[0]);
* Pb1.Value = j;
* }*/
lsGdb1.Gdb.Close();
lsReturn = lsVoucher;
}
catch (Exception e)
{
lsSQL = "";
string ls = "äÁèÊÒÁÒöºÑ¹·Ö¡¢éÍÁÙÅä´é " + lsSQL;
lsGdb.WriteLogError(ls, e, lsSQL, "SaveVoucherAcc ");
MessageBox.Show(ls + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK);
}
Pb1.Visible = false;
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
return(lsReturn);
}
private void BtnSend_Click(object sender, EventArgs e)
{
DialUP lsDial = new DialUP();
Int32 liConnect = lsDial.DialUp();
Int32 i = 0;
DateTime ldDate = new DateTime();
string lsSQL = "Select * From voucher Where flagsend = '2' Order By mac, vouno";
BtnSend.Enabled = false;
lsGdb.ConnectDatabase();
switch (CboTSend.Text)
{
case "·ÕèÂѧäÁèä´éÊè§":
lsSQL = "Select * From voucher Where flagsend = '2' Order By mac, vouno";
break;
case "µÑé§áµèÇѹ·Õè - ¶Ö§Çѹ·Õè":
string lsStartDate, lsEndDate;
lsStartDate = TxtStartDate.Value.Year.ToString() + "-" + TxtStartDate.Value.Month.ToString() + "-" + TxtStartDate.Value.Day.ToString();
lsEndDate = TxtEndDate.Value.Year.ToString() + "-" + TxtEndDate.Value.Month.ToString() + "-" + TxtEndDate.Value.Day.ToString();
lsSQL = "Select * From voucher Where voudate >= '"
+ lsStartDate + "' and voudate <= '" + lsEndDate + "' Order By mac, vouno";
break;
}
MySqlConnection ConnRemote = new MySqlConnection("Data Source=localhost;Database=thahr3;User ID=root;Password=Ekartc2c5");
ConnRemote.Open();
MySqlCommand lsComm = new MySqlCommand(lsSQL, ConnRemote);
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
Voucher lsSendVoucher = new Voucher();
//lsSendVoucher.ConnectDatabase();
while (lsRead.Read())
{
lsSendVoucher.TableName = "sendvoucher";
lsSendVoucher.HostName = "office";
//lsSendVoucher.ConnectDatabase();
lsSendVoucher.VouNO = lsRead["vouno"].ToString();
lsSendVoucher.VouDate = Convert.ToDateTime(lsRead["voudate"].ToString());
lsSendVoucher.VisitT = Convert.ToInt32(lsRead["visitt"].ToString());
lsSendVoucher.GuestFirstName = lsRead["guestfirstname"].ToString();
lsSendVoucher.GuestLastName = lsRead["guestlastname"].ToString();
lsSendVoucher.ShiftCode = lsRead["shiftcode"].ToString();
lsSendVoucher.ResRooms = Convert.ToInt32(lsRead["resrooms"].ToString());
lsSendVoucher.ProvCode = lsRead["provcode"].ToString();
lsSendVoucher.HotelCode = lsRead["hotelcode"].ToString();
lsSendVoucher.RoomCode = lsRead["roomcode"].ToString();
lsSendVoucher.ResTime = lsRead["restime"].ToString();
if (lsRead.GetValue(17).ToString() != "")
{
lsSendVoucher.CheckInTime = Convert.ToDateTime(lsRead["checkindate"].ToString());
}
else
{
lsSendVoucher.CheckInTime = ldDate;
}
if (lsRead.GetValue(18).ToString() != "")
{
lsSendVoucher.CheckOutTime = Convert.ToDateTime(lsRead["checkoutdate"].ToString());
}
else
{
lsSendVoucher.CheckOutTime = ldDate;
}
lsSendVoucher.DepositAMT = Convert.ToDecimal(lsRead["depositamt"].ToString());
lsSendVoucher.RoomRate = Convert.ToDecimal(lsRead["roomrate"].ToString());
lsSendVoucher.StaffCode = lsRead["staffcode"].ToString();
lsSendVoucher.StatusCode = lsRead["statuscode"].ToString();
lsSendVoucher.ConfirmPerson = lsRead["confirmperson"].ToString();
lsSendVoucher.Counter1 = lsRead["counter1"].ToString();
lsSendVoucher.CouNO = lsRead["couno"].ToString();
lsSendVoucher.NationCode = lsRead["nationcode"].ToString();
lsSendVoucher.Remark = lsRead["remark"].ToString();
lsSendVoucher.Flag = lsRead["flag"].ToString();
lsSendVoucher.RoomNO = lsRead["roomno"].ToString();
lsSendVoucher.MAC = lsRead["mac"].ToString();
lsSendVoucher.CreateVoucher(ConnRemote);
lsSendVoucher.UpdateFlagSend(ConnRemote);
i++;
GrdSend.ActiveSheet.Cells[i, 0].Text = lsSendVoucher.VouNO;
GrdSend.ActiveSheet.Cells[i, 1].Text = lsSendVoucher.GuestFirstName + " " + lsSendVoucher.GuestLastName;
GrdSend.ActiveSheet.Cells[i, 2].Text = lsSendVoucher.VouDate.ToShortDateString();
GrdSend.ActiveSheet.Cells[i, 3].Text = lsSendVoucher.MAC;
}
lsRead.Close();
BtnSend.Enabled = true;
}