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);
}
public Boolean SeparateVoucher(Boolean FlagHD, Int32 aFlagSend)
{
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
Pb1.Minimum = 0;
Boolean lbReturn = true;
Int32 i = 0;
DateTime ldDate = new DateTime();
Voucher lstblVou = new Voucher();
string lsServer = "", lsDatabase = "", lsSQL = "";
string lsStartDate = "", lsEndDate = "", lsCounter = "", lsStrSelect = "", lsStrUpdate = "";
lsStartDate = lsGdb.SelectDateMySQL(TxtStartDate.Value);
lsEndDate = lsGdb.SelectDateMySQL(TxtEndDate.Value);
lsCounter = lsGdb.SelectCounter("counteridfromcountername", "countername", CboCounter.Text, lsGdb.Gdb);
lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost");
lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3");
string lsDatabaseServerName = lsIni.GetString("thahr30", "databaseservername", "thahr3");
string lsUserName = lsIni.GetString("thahr30", "username", "janepop");
string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5");
if (FlagHD)
{
lsStrSelect = lsServer;
lsStrUpdate = TxtIP.Text;
//lsDatabase = lsDatabaseServerName;
}
else
{
lsStrSelect = TxtIP.Text;
lsStrUpdate = "localhost";
}
MySqlConnection lsConnSelect = new MySqlConnection("Data Source=" + lsStrSelect + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******"");
MySqlConnection lsConnUpdate = new MySqlConnection("Data Source=" + lsStrUpdate + ";Database=" + lsDatabaseServerName + ";User ID=" + lsUserName + ";Password="******"");
try
{
lsConnSelect.Open();
}
catch (Exception e)
{
lbReturn = false;
string ls = "äÁèÊÒÁÒöµÔ´µèÍ Counter SeparateMember ä´é " + lsSQL;
lsGdb.WriteLogError(ls, e, lsSQL, "SeparateMember");
MessageBox.Show("äÁèÊÒÁÒöµÔ´µèÍ°Ò¹¢éÍÁÙÅ " + lsStrSelect + " " + e.Message.ToString(), e.Source.ToString());
}
try
{
lsConnUpdate.Open();
}
catch (Exception e)
{
lbReturn = false;
string ls = "äÁèÊÒÁÒöµÔ´µèÍ Counter SeparateMember ä´é " + lsSQL;
lsGdb.WriteLogError(ls, e, lsSQL, "SeparateMember");
MessageBox.Show("äÁèÊÒÁÒöµÔ´µèÍ°Ò¹¢éÍÁÙÅ " + lsStrUpdate + " " + e.Message.ToString(), e.Source.ToString());
}
if (aFlagSend == 1)
{
}
else
{
if (FlagHD)
{
lsSQL = "Select count(*) as cnt From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '"
+ lsEndDate + "' Order By counter1, vouno";
}
else
{
lsSQL = "Select count(*) as cnt From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '"
+ lsEndDate + "' and counter1 = '" + lsCounter + "' Order By counter1, vouno";
}
}
MySqlCommand lsCommConter1 = new MySqlCommand(lsSQL, lsConnSelect);
MySqlDataReader lsReadCounter1;
lsReadCounter1 = lsCommConter1.ExecuteReader();
if (lsReadCounter1.HasRows)
{
while (lsReadCounter1.Read())
{
Pb1.Maximum = Convert.ToInt32(lsReadCounter1["cnt"].ToString());
}
}
lsReadCounter1.Close();
if (aFlagSend == 1)
{
}
else
{
if (FlagHD)
{
lsSQL = "Select * From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '"
+ lsEndDate + "' Order By counter1, vouno";
}
else
{
lsSQL = "Select * From voucher Where voudate >= '" + lsStartDate + "' and voudate <= '"
+ lsEndDate + "' and counter1 = '" + lsCounter + "' Order By counter1, vouno";
}
}
MySqlCommand lsCommConter = new MySqlCommand(lsSQL, lsConnSelect);
MySqlDataReader lsReadCounter;
lsReadCounter = lsCommConter.ExecuteReader();
if (lsReadCounter.HasRows)
{
while (lsReadCounter.Read())
{
try
{
lstblVou.TableName = "voucher";
//lstblVou.HostName = "office";
//lsSendVoucher.ConnectDatabase();
lstblVou.VouNO = lsReadCounter["vouno"].ToString();
lstblVou.VouDate = Convert.ToDateTime(lsReadCounter["voudate"].ToString());
lstblVou.VisitT = Convert.ToInt32(lsReadCounter["visitt"].ToString());
lstblVou.GuestFirstName = lsReadCounter["guestfirstname"].ToString();
lstblVou.GuestLastName = lsReadCounter["guestlastname"].ToString();
lstblVou.ShiftCode = lsReadCounter["shiftcode"].ToString();
lstblVou.ResRooms = Convert.ToInt32(lsReadCounter["resrooms"].ToString());
lstblVou.ProvCode = lsReadCounter["provcode"].ToString();
lstblVou.HotelCode = lsReadCounter["hotelcode"].ToString();
lstblVou.RoomCode = lsReadCounter["roomcode"].ToString();
lstblVou.ResTime = lsReadCounter["restime"].ToString();
if (lsReadCounter.GetValue(17).ToString() != "")
{
lstblVou.CheckInTime = Convert.ToDateTime(lsReadCounter["checkindate"].ToString());
}
else
{
lstblVou.CheckInTime = ldDate;
}
if (lsReadCounter.GetValue(18).ToString() != "")
{
lstblVou.CheckOutTime = Convert.ToDateTime(lsReadCounter["checkoutdate"].ToString());
}
else
{
lstblVou.CheckOutTime = ldDate;
}
lstblVou.PersoninTrip = Convert.ToInt32(lsReadCounter["personintrip"].ToString());
lstblVou.DepositAMT = Convert.ToDecimal(lsReadCounter["depositamt"].ToString());
lstblVou.RoomRate = Convert.ToDecimal(lsReadCounter["roomrate"].ToString());
lstblVou.RoomRate1 = Convert.ToDecimal(lsReadCounter["roomrate1"].ToString());
lstblVou.StaffCode = lsReadCounter["staffcode"].ToString();
lstblVou.StatusCode = lsReadCounter["statuscode"].ToString();
lstblVou.ConfirmPerson = lsReadCounter["confirmperson"].ToString();
lstblVou.Counter1 = lsReadCounter["counter1"].ToString();
lstblVou.CouNO = lsReadCounter["couno"].ToString();
lstblVou.NationCode = lsReadCounter["nationcode"].ToString();
lstblVou.Remark = lsReadCounter["remark"].ToString();
lstblVou.Flag = lsReadCounter["flag"].ToString();
lstblVou.RoomNO = lsReadCounter["roomno"].ToString();
lstblVou.MAC = lsReadCounter["mac"].ToString();
lstblVou.MemPlCode = lsReadCounter["memplcode"].ToString();
lstblVou.PriceEnd = Convert.ToDecimal(lsReadCounter["priceend"]);
lstblVou.Taxi = lsReadCounter["taxi"].ToString();
lstblVou.Pay_Type = lsReadCounter["pay_type"].ToString();
lstblVou.CreaditCardID = lsReadCounter["cardid"].ToString();
if (lstblVou.Taxi == "")
{
lstblVou.Taxi = "false";
}
lstblVou.Breakfast = lsReadCounter["breakfast"].ToString();
if (lstblVou.Breakfast == "")
{
lstblVou.Breakfast = "false";
}
lstblVou.CreateVoucher(lsConnUpdate);
lstblVou.UpdateFlagSend(lsConnUpdate);
i++;
GrdRec.ActiveSheet.AddRows(0, 1);
GrdRec.ActiveSheet.Cells[0, 0].Text = lstblVou.VouNO;
GrdRec.ActiveSheet.Cells[0, 1].Text = lstblVou.GuestFirstName + " " + lstblVou.GuestLastName;
GrdRec.ActiveSheet.Cells[0, 2].Text = lstblVou.VouDate.ToShortDateString();
GrdRec.ActiveSheet.Cells[0, 3].Text = lstblVou.MAC;
Pb1.Value = i;
if (i > 15)
{
GrdRec.ScrollBarTrackPolicy = FarPoint.Win.Spread.ScrollBarTrackPolicy.Vertical;
}
}
catch (Exception e)
{
lbReturn = false;
i++;
string ls = "äÁèÊÒÁÒöºÑ¹·Ö¡¢éÍÁÙÅ SeparateVoucher ä´é " + lsSQL;
lsGdb.WriteLogError(ls, e, lsSQL, "SeparateVoucher");
MessageBox.Show("" + e.Message.ToString(), "" + e.Source.ToString(), MessageBoxButtons.OK);
}
Application.DoEvents();
}
}
lsReadCounter.Close();
lsConnSelect.Close();
lsConnUpdate.Close();
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
return(lbReturn);
}
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;
}
