private void SelectVoucher(string aMonth, string aYear)
{
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
Pb1.Visible = true;
Int32 i = 0, j = 0, liCountVoucherMain = 0, liCountDaysMain = 0, liCountPaxMain = 0;
double ldoDepositAMT = 0, ldoDeposit = 0;
string lsSQL = "";
//PaintGrdMain();
DateTime ld = new DateTime();
//Connection lsConn = new Connection();
//lsConn.ConnectDatabase();
Int32 li = Convert.ToInt32(aMonth);
lsStartDate = aYear + "-" + li.ToString("00") + "-01";
ld = lsGdb.LastDayofMonth(Convert.ToDateTime(lsStartDate));
lsStartDate = (Convert.ToInt16(aYear) - 543) + "-" + li.ToString("00") + "-01";
lsEndDate = lsGdb.SelectDateMySQL(ld);
lsSQL = "Select count(*) as cnt From accrecvoucher v "
+ "Where v.checkindate >= '" + lsStartDate + "' and v.checkindate <= '"
+ lsEndDate + "' and v.flag in ('1','2') Group By hotelcode ";
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
MySqlDataReader lsReadMain;
lsReadMain = lsComm.ExecuteReader();
if (lsReadMain.HasRows)
{
while (lsReadMain.Read())
{
j++;
}
}
lsReadMain.Close();
GrdView.Sheets[0].RowCount = j + 1;
Pb1.Minimum = 0;
Pb1.Maximum = j;
lsSQL = "Select v.hotelcode as hotelcode, count(v.hotelcode) voucher, sum(v.visitt)as Days, "
+ "sum(personintrip) as PAX, sum(depositamt) as Deposit, counter1 as counter, sum(v.depositamt - (v.roomrate1*v.visitt*"
+ ldoAccountMemberRate + "/100)) as depositpay From accrecvoucher v "
+ "Where v.checkindate >= '" + lsStartDate + "' and v.checkindate <= '"
+ lsEndDate + "' and v.flag in ('1','2') Group By hotelcode ";
lsComm.CommandText = lsSQL;
lsReadMain = lsComm.ExecuteReader();
if (lsReadMain.HasRows)
{
while (lsReadMain.Read())
{
ldoDeposit = Convert.ToDouble(lsReadMain["Deposit"]);
lsSQL = lsIniT.SelectInitial(lsIniT.TblMember, lsReadMain["hotelcode"].ToString(), Initial.WhereSelect.aCodetoName);
GrdView.Sheets[0].SetText(i, liColMainHotelCode, lsReadMain["hotelcode"].ToString());
GrdView.Sheets[0].SetText(i, liColMainMemNamee1, lsSQL);
GrdView.Sheets[0].SetText(i, liColMainVoucher, lsReadMain["voucher"].ToString());
GrdView.Sheets[0].SetText(i, liColMainDays, lsReadMain["Days"].ToString());
GrdView.Sheets[0].SetText(i, liColMainPax, lsReadMain["PAX"].ToString());
GrdView.Sheets[0].SetText(i, liColMainDepositAMT, ldoDeposit.ToString());
GrdView.Sheets[0].SetText(i, liColMainDepositPay, lsReadMain["depositpay"].ToString());
GrdView.Sheets[0].SetText(i, liColMainTypeGen, Convert.ToString(TypeGen.PDF));
GrdView.Sheets[0].SetText(i, liColMainEMail, lsIniT.SelectInitial(lsIniT.TblMemberEmailAccount, lsReadMain["hotelcode"].ToString(), Initial.WhereSelect.aCodetoName));
liCountVoucherMain = liCountVoucherMain + Convert.ToInt32(lsReadMain["voucher"]);
liCountDaysMain = liCountDaysMain + Convert.ToInt32(lsReadMain["days"]);
liCountPaxMain = liCountPaxMain + Convert.ToInt32(lsReadMain["pax"]);
if (ldoDeposit < Convert.ToDouble(lsReadMain["depositpay"]))
{
GrdView.Sheets[0].Cells[i, liColMainDepositPay].ForeColor = Color.Green;
GrdView.Sheets[0].Cells[i, liColMainDepositPay].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.Sheets[0].SetNote(i, liColMainDepositPay, "ÃѺà§Ô¹à¾ÔèÁ");
}
else
{
GrdView.Sheets[0].Cells[i, liColMainDepositPay].ForeColor = Color.Red;
GrdView.Sheets[0].Cells[i, liColMainDepositPay].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.Sheets[0].SetNote(i, liColMainDepositPay, "¨èÒÂà§Ô¹");
}
ldoDepositAMT = ldoDepositAMT + ldoDeposit;
if ((i % 2) != 0)
{
GrdView.Sheets[0].Rows[i].BackColor = Color.LightGoldenrodYellow;
}
i++;
Pb1.Value = i;
}
}
lsReadMain.Close();
if (GrdView.Sheets[0].RowCount > 29)
{
GrdView.VerticalScrollBarPolicy = FarPoint.Win.Spread.ScrollBarPolicy.Always;
}
//lsConn.Gdb.Close();
Pb1.Visible = false;
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
}
private void SelectVoucher(string aMonth)
{
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
Int32 i = 0, j = 0, liCountVoucherMain = 0, liCountDaysMain = 0, liCountPaxMain = 0, liRow = 0;
double ldoDepositAMT = 0, ldoDeposit = 0;
string lsSQL = "", lsHotelName = "", lsCounterName = "";
//PaintGrdMain();
DateTime ld = new DateTime();
//MySqlConnection lsConn = new MySqlConnection();
//lsConn.ConnectDatabase();
//lsConn = lsGdb.Gdb;
Int32 li = Convert.ToInt32(aMonth);
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 = "Select v.hotelcode as hotelcode, ifnull(count(v.hotelcode),0) as voucher, ifnull(sum(v.visitt),0) as Days, "
+ "ifnull(sum(personintrip),0) as PAX, ifnull(sum(depositamt),0) as Deposit, counter1 as counter From voucher v "
+ "Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '"
+ lsEndDate + "' and v.flag in ('1','2','4') Group By hotelcode ";
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
MySqlDataAdapter lsAdap = new MySqlDataAdapter();
DataSet lsDSMain = new DataSet();
DataTable lsDTHotel = new DataTable();
DataTable lsDTVoucher = new DataTable();
lsDTHotel = lsDSMain.Tables.Add("hotel");
lsDTVoucher = lsDSMain.Tables.Add("voucher");
lsDTHotel.Columns.AddRange(new DataColumn[] { new DataColumn("hotelcode", typeof(string)),
new DataColumn("memnamee1", typeof(string)), new DataColumn("voucher", typeof(string)),
new DataColumn("Days", typeof(string)), new DataColumn("PAX", typeof(string)),
new DataColumn("DepositAMT", typeof(string)), new DataColumn("counter", typeof(string)) });
MySqlDataReader lsReadMain;
lsReadMain = lsComm.ExecuteReader();
if (lsReadMain.HasRows)
{
while (lsReadMain.Read())
{
liRow++;
ldoDeposit = Convert.ToDouble(lsReadMain["Deposit"]);
liCountVoucherMain = liCountVoucherMain + Convert.ToInt32(lsReadMain["voucher"]);
liCountDaysMain = liCountDaysMain + Convert.ToInt32(lsReadMain["days"]);
liCountPaxMain = liCountPaxMain + Convert.ToInt32(lsReadMain["pax"]);
ldoDepositAMT = ldoDepositAMT + ldoDeposit;
lsHotelName = "";
lsHotelName = lsIniT.SelectInitial(lsIniT.TblMember, lsReadMain["hotelcode"].ToString(), Initial.WhereSelect.aCodetoName);
lsDTHotel.Rows.Add(new object[] { lsReadMain["hotelcode"].ToString(), lsHotelName, lsReadMain["voucher"].ToString(),
lsReadMain["days"].ToString(), lsReadMain["pax"].ToString(), ldoDeposit.ToString("0,000.00") });
}
lsDTHotel.Rows.Add(new object[] { "", "", liCountVoucherMain.ToString(), liCountDaysMain.ToString(), liCountPaxMain.ToString(), ldoDepositAMT.ToString() });
}
lsReadMain.Close();
lsDTVoucher.Columns.AddRange(new DataColumn[] { new DataColumn("counter", typeof(string)),
new DataColumn("voucher", typeof(string)),
new DataColumn("Days", typeof(string)), new DataColumn("PAX", typeof(string)),
new DataColumn("DepositAMT", typeof(string)), new DataColumn("-", typeof(string)) });
lsSQL = "Select ifnull(count(v.hotelcode),0) voucher, ifnull(sum(v.visitt),0) as Days, "
+ "ifnull(sum(personintrip),0) as PAX, ifnull(sum(depositamt),0) as Deposit, ifnull(counter1,0) as Counter, hotelcode From voucher v "
+ "Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '"
+ lsEndDate + "' and v.flag in ('1','2') Group By hotelcode, counter1 Order By counter1";
MySqlDataReader lsReadVoucher;
MySqlCommand lsComm1 = new MySqlCommand(lsSQL, lsGdb.Gdb);
lsReadVoucher = lsComm1.ExecuteReader();
if (lsReadVoucher.HasRows)
{
while (lsReadVoucher.Read())
{
lsCounterName = lsIniT.SelectInitial(lsIniT.TblCounter, lsReadVoucher["Counter"].ToString(), Initial.WhereSelect.aCodetoName);
lsDTVoucher.Rows.Add(new object[] { lsCounterName, lsReadVoucher["voucher"].ToString(),
lsReadVoucher["days"].ToString(), lsReadVoucher["pax"].ToString(), lsReadVoucher["Deposit"].ToString(), lsReadVoucher["hotelcode"].ToString() });
}
}
lsReadVoucher.Close();
lsDSMain.Relations.Add("root", lsDSMain.Tables["hotel"].Columns["hotelcode"], lsDSMain.Tables["voucher"].Columns["-"]);
GrdView.Sheets[0].DataSource = lsDSMain;
PaintGrdMain();
if (GrdView.Sheets[0].RowCount > 29)
{
GrdView.VerticalScrollBarPolicy = FarPoint.Win.Spread.ScrollBarPolicy.Always;
}
if ((i % 2) != 0)
{
GrdView.Sheets[0].Rows[i].BackColor = Color.LightGoldenrodYellow;
}
TxtVoucherSum.Text = liCountVoucherMain.ToString("0,000");
TxtVoucherUse.Text = "0";
TxtDays.Text = liCountDaysMain.ToString("0,000");
TxtPAX.Text = liCountPaxMain.ToString("0,000");
TxtDeposit.Text = ldoDepositAMT.ToString("0,000.00");
GrdView.Sheets[0].RowCount = liRow + 1;
//lsConn.Gdb.Close();
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
}
public Int32 SeparateMember(Boolean FlagHD)
{
Int32 i = 0;
DateTime ldVoidDate;
string lsSQL = "", lsPl = "", lsCounterid = "", lsStrSelect = "", lsStrUpdate = "";
Member lstblMember = new Member();
PaintGrdRecMember();
lsCounterid = lsIniT.SelectInitial(lsIniT.TblCounter, CboCounter.Text, Initial.WhereSelect.aNametoCode);
if (FlagHD)
{
lsSQL = "Select count(*) as cnt From member Where flagsend <> '2'";
}
else
{
lsSQL = "Select count(*) as cnt From member Where flagsend" + lsCounterid + " <> '2'";
}
MySqlCommand lsCommConter1 = new MySqlCommand(lsSQL, lsConnSelect1);
MySqlDataReader lsReadCounter1;
lsReadCounter1 = lsCommConter1.ExecuteReader();
if (lsReadCounter1.HasRows)
{
while (lsReadCounter1.Read())
{
Lb1.Items.Add("ÁÕ¢éÍÁÙÅ Member " + lsReadCounter1["cnt"].ToString() + lsConnSelect1.ConnectionString);
Application.DoEvents();
Pb1.Maximum = Convert.ToInt32(lsReadCounter1["cnt"].ToString());
}
}
lsReadCounter1.Close();
if (FlagHD)
{
lsSQL = "Select * From member Where flagsend <> '2'";
}
else
{
lsSQL = "Select * From member Where flagsend" + lsCounterid + " <> '2'";
}
MySqlCommand lsCommConter = new MySqlCommand(lsSQL, lsConnSelect1);
MySqlDataReader lsReadCounter;
lsReadCounter = lsCommConter.ExecuteReader();
//MessageBox.Show("bbbbbbbbb", "aaaa");
if (lsReadCounter.HasRows)
{
while (lsReadCounter.Read())
{
try
{
lstblMember.MemID = lsReadCounter["memid"].ToString();
lstblMember.MemNameE1 = lsReadCounter["memnamee1"].ToString();
lstblMember.MemNameE2 = lsReadCounter["memnamee2"].ToString();
lstblMember.MemNameT = lsReadCounter["memnamet"].ToString();
lstblMember.Remark = lsReadCounter["remark"].ToString();
lstblMember.NumRoom = Convert.ToInt32(lsReadCounter["numroom"].ToString());
lstblMember.PriceEnd = Convert.ToDecimal(lsReadCounter["priceend"].ToString());
lstblMember.PriceStart = Convert.ToDecimal(lsReadCounter["pricestart"].ToString());
lstblMember.HotelRating = Convert.ToInt32(lsReadCounter["hotelrating"].ToString());
lstblMember.Deposit = Convert.ToDecimal(lsReadCounter["deposit"]);
lstblMember.HotelChain = lsReadCounter["hotelchaincode"].ToString();
lstblMember.RegionCode = lsReadCounter["regioncode"].ToString();
lstblMember.TMemCode = lsReadCounter["tmemcode"].ToString();
lstblMember.TBisCode = lsReadCounter["tbiscode"].ToString();
lstblMember.FlagGreenLeft = lsReadCounter["flaggreenleft"].ToString();
lstblMember.ProvCode = lsReadCounter["provcode"].ToString();
lstblMember.TypePriceBaht = lsReadCounter["typepricebaht"].ToString();
lstblMember.Location = lsReadCounter["locationcode"].ToString();
lstblMember.FlagSend = "1";
lstblMember.EMailAccount = lsReadCounter["emailaccount"].ToString();
lstblMember.FlagSale = lsReadCounter["flagsale"].ToString();
lstblMember.NationCode = lsReadCounter["nationcode"].ToString();
try
{
ldVoidDate = DateTime.Parse(lsReadCounter["startdate"].ToString());
lstblMember.StartDate = lsGdb.SelectDateMySQL(ldVoidDate);
}
catch
{
lstblMember.StartDate = "2007-01-01";
}
try
{
ldVoidDate = DateTime.Parse(lsReadCounter["enddate"].ToString());
lstblMember.EndDate = lsGdb.SelectDateMySQL(ldVoidDate);
}
catch
{
lstblMember.EndDate = "2007-01-01";
}
try
{
ldVoidDate = DateTime.Parse(lsReadCounter["regisdate"].ToString());
lstblMember.RegisDate = lsGdb.SelectDateMySQL(ldVoidDate);
}
catch
{
lstblMember.RegisDate = "2007-01-01";
}
lstblMember.SKK9ID = lsReadCounter["skk9id"].ToString();
lstblMember.EMailAccount = lsReadCounter["emailaccount"].ToString();
lstblMember.CreateMember(lsConnUpdate1);
//MessageBox.Show("222 lsCounterid " + lsCounterid, "ee", MessageBoxButtons.OK);
//lsPl = lstblMember.SendPriceList(lstblMember.MemID, lsStrSelect, lsStrUpdate, lsDatabase);
//MessageBox.Show("lsCounterid " + lsCounterid, "ee", MessageBoxButtons.OK);
lstblMember.UpdateFlagSend(lstblMember.MemID, lsCounterid, lsConnSelect2);
i++;
GrdRec.ActiveSheet.AddRows(0, 1);
GrdRec.ActiveSheet.Cells[0, 0].Text = lstblMember.MemID;
GrdRec.ActiveSheet.Cells[0, 1].Text = lstblMember.MemNameE1;
GrdRec.ActiveSheet.Cells[0, 2].Text = lsPl;
//GrdRec.ActiveSheet.Cells[0, 3].Text = lstblMember.MAC;
//lsGdb.SeparateTable(Connection.TableIniT.MemberPriceList, "memcode", "refid = '" + lstblMember .MemID + "'", lsConnSelect1, lsConnUpdate1);
Pb1.Value = i;
Application.DoEvents();
}
catch (Exception e)
{
i++;
string ls = "äÁèÊÒÁÒöºÑ¹·Ö¡¢éÍÁÙÅ SeparateMember ä´é " + lsSQL;
lsGdb.WriteLogError(ls, e, lsSQL, "SeparateMember");
MessageBox.Show(ls + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK);
}
}
}
lsReadCounter.Close();
return(i);
}
private void SelectVoucher(string aStartDate, string aEndDate, string aFlag)
{
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
PaintGrdView();
//if (lbPageLoad == false)
//{
string lsSQL = "", lsStartDate = "", lsEndDate = "", lsMemNameE = "";
string lsFlag = "", lsWhereCondition = "", lsStaffName = "", lsStatusName = "", lsPrefix = "";
string lsCounterID = lsIniT.SelectInitial(lsIniT.TblCounter, CboCounter.Text, Initial.WhereSelect.aNametoCode);
Int32 i = 0;
double ldoRoomRate = 0, ldoRoomRate1 = 0;
lsFlag = aFlag;
lsStartDate = aStartDate;
lsEndDate = aEndDate;
//MySqlConnection Conn = new MySqlConnection("Data Source="+lsServer +";Database="+lsDatabase+";User ID=root;Password=Ekartc2c5");
//Conn.Open();
if (TxtVouNo.Text != "")
{
lsWhereCondition = "Where vouno = '" + TxtVouNo.Text + "'";
}
else
{
lsWhereCondition = ConditionWhereVoucher(lsStartDate, lsEndDate, lsFlag, lsCounterID);
}
lsSQL = "Select count(*) as cnt From voucher " + lsWhereCondition;
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
while (lsRead.Read())
{
i = Convert.ToInt32(lsRead.GetValue(0).ToString());
}
lsRead.Close();
GrdView.ActiveSheet.RowCount = 0;
GrdView.ActiveSheet.RowCount = i + 1;
//lsSQL = "Select vouno, guestfirstname, guestlastname, voudate, "
// + "counter1, mac, memnamee1, roomno, "
// + "confirmremark, roomrate, roomrate1, checkindate, visitt, checkoutdate "
// + "From voucher v, member m "
// + "Where v.flag <> '3' and voudate >= '" + lsStartDate + "' and voudate <= '"
// + lsEndDate + "' " + lsWhereCounter + " and v.hotelcode = m.memid Order By vouno";
lsSQL = "Select vouno, guestfirstname, guestlastname, voudate, "
+ "counter1, mac, roomno, hotelcode, "
+ "confirmremark, roomrate, roomrate1, checkindate, visitt, checkoutdate, roomrate, roomrate1, staffcode, statuscode, prefix "
+ "From voucher v "
+ lsWhereCondition + " Order By vouno";
lsComm.CommandText = lsSQL;
DateTime ldVouDate = new DateTime();
DateTime ldCheckInDate = new DateTime();
DateTime ldCheckOutDate = new DateTime();
Int32 liVisitT = 0, li = 0;
lsRead = lsComm.ExecuteReader();
i = 0;
Pb1.Minimum = 0;
Pb1.Maximum = GrdView.ActiveSheet.RowCount;
Pb1.Visible = true;
SL1.Visible = true;
while (lsRead.Read())
{
//i++;
try
{
lsSQL = lsRead["vouno"].ToString();
if (lsSQL == "50003119673")
{
lsSQL = lsRead["prefix"].ToString() + " " + lsRead["guestfirstname"].ToString();
//MessageBox.Show("aaaa", "aaaa", MessageBoxButtons.OK);
}
ldVouDate = Convert.ToDateTime(lsRead["voudate"]);
ldCheckInDate = Convert.ToDateTime(lsRead["checkindate"]);
ldCheckOutDate = Convert.ToDateTime(lsRead["checkoutdate"]);
liVisitT = Convert.ToInt32(lsRead["visitt"]);
li = DateTime.Compare(ldCheckOutDate, ldCheckInDate);
lsSQL = lsRead["guestfirstname"].ToString();
lsMemNameE = lsIniT.SelectInitial(lsIniT.TblMember, lsRead["hotelcode"].ToString(), Initial.WhereSelect.aCodetoName);
lsStaffName = lsIniT.SelectInitial(lsIniT.TblStaff, lsRead["staffcode"].ToString(), Initial.WhereSelect.aCodetoName);
lsStatusName = lsIniT.SelectInitial(lsIniT.TblStatus, lsRead["statuscode"].ToString(), Initial.WhereSelect.aCodetoName);
lsPrefix = lsIniT.SelectInitial(lsIniT.TblPrefix, lsRead["prefix"].ToString(), Initial.WhereSelect.aCodetoName);
ldoRoomRate = Convert.ToDouble(lsRead["roomrate"]);
ldoRoomRate1 = Convert.ToDouble(lsRead["roomrate1"]);
GrdView.ActiveSheet.SetText(i, liColVouNo, lsRead["vouno"].ToString());
GrdView.ActiveSheet.SetText(i, liColGuestName, lsPrefix + " " + lsRead["guestlastname"].ToString() + " " + " " + lsRead["guestfirstname"].ToString());
//GrdView.Sheets[0].Cells[i, liColGuestName].Text = lsPrefix + " " + lsRead["guestlastname"].ToString() + " " + lsSQL;
GrdView.ActiveSheet.SetText(i, liColVouDate, ldVouDate.ToShortDateString());
lsSQL = lsIniT.SelectInitial(lsIniT.TblCounter, lsRead["counter1"].ToString(), Initial.WhereSelect.aCodetoName);
GrdView.ActiveSheet.SetText(i, liColCounter, lsSQL);
GrdView.ActiveSheet.SetText(i, liColHotel, lsMemNameE);
lsSQL = lsRead["mac"].ToString();
if (lsSQL.Length == 9)
{
lsSQL = lsRead["mac"].ToString().Substring(6, 3);
}
else
{
lsSQL = lsRead["mac"].ToString();
}
GrdView.ActiveSheet.SetText(i, liColMac, lsSQL);
lsSQL = lsRead["roomno"].ToString();
GrdView.ActiveSheet.SetText(i, liColRoom, lsSQL);
GrdView.ActiveSheet.SetText(i, liColRoomRate, ldoRoomRate.ToString());
GrdView.ActiveSheet.SetText(i, liColRoomRate1, ldoRoomRate1.ToString());
GrdView.ActiveSheet.SetText(i, liColStaffName, lsStaffName);
GrdView.ActiveSheet.SetText(i, liColStatus, lsStatusName);
lsSQL = lsRead.GetValue(0).ToString();
if (i == 12)
{
lsSQL = lsRead["roomno"].ToString();
}
if (lsRead["roomno"].ToString() == "-") // roomno
{
GrdView.ActiveSheet.Cells[i, liColVouNo].ForeColor = Color.Red;
GrdView.ActiveSheet.Cells[i, liColVouNo].Font = new Font("Microsoft Sans Serif", 8, System.Drawing.FontStyle.Italic | FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColVouNo, "RoomNO äÁèÁÕ¤èÒ");
}
if (lsRead.GetValue(8).ToString() == "-") // confirmremark
{
GrdView.ActiveSheet.Cells[i, liColRoom].ForeColor = Color.Red;
GrdView.ActiveSheet.Cells[i, liColRoom].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColRoom, "Confirm äÁèÁÕ¤èÒ");
}
if (ldoRoomRate > ldoRoomRate1) // roomrate
{
GrdView.ActiveSheet.Cells[i, liColGuestName].ForeColor = Color.Magenta;
GrdView.ActiveSheet.Cells[i, liColGuestName].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColGuestName, "Room Rate ÁÒ¡¡ÇèÒ Room Rate++");
}
else if (ldoRoomRate < ldoRoomRate1)
{
GrdView.ActiveSheet.Cells[i, liColGuestName].ForeColor = Color.MediumSeaGreen;
GrdView.ActiveSheet.Cells[i, liColGuestName].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColGuestName, "Room Rate ¹éÍ¡ÇèÒ Room Rate++");
}
if (ldVouDate != ldCheckInDate)
{
GrdView.ActiveSheet.Cells[i, liColGuestName].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColGuestName, "Çѹ·Õè Voucher Date äÁèµéͧ¡Ñº Check in Date");
}
if (li != liVisitT)
{
GrdView.ActiveSheet.Cells[i, liColHotel].ForeColor = Color.Tomato;
GrdView.ActiveSheet.Cells[i, liColHotel].Font = new Font("Microsoft Sans Serif", 8, FontStyle.Bold);
GrdView.CellNoteIndicatorVisible = true;
GrdView.ActiveSheet.SetNote(i, liColHotel, "¨Ó¹Ç¹Çѹ¢Í§ Check in Date ¡Ñº Check out Date äÁèà·èҡѺ ¨Ó¹Ç¹Çѹ·Õèà¢éҾѡ");
}
if ((i % 2) != 0)
{
GrdView.ActiveSheet.Rows[i].BackColor = Color.LightGoldenrodYellow;
}
//if ((i%100)==0)
//{
// //Application.DoEvents();
//}
i++;
//GrdView.ActiveSheet.SetActiveCell(i, 1);
Pb1.Value = i;
SL1.Text = i.ToString() + " / " + (GrdView.ActiveSheet.RowCount - 1);
}
catch (Exception e)
{
i++;
MessageBox.Show(lsRead["vouno"].ToString() + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK);
}
//Application.DoEvents();
}
lsRead.Close();
if (i > 29)
{
GrdView.VerticalScrollBarPolicy = FarPoint.Win.Spread.ScrollBarPolicy.Always;
}
Pb1.Visible = false;
SL1.Visible = false;
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
//}
}
public Boolean GenSaleDailyKingPower(string aYYYYMMDDHHMMSS)
{
Boolean lbReturn = false;
string lsError = "", lsSQL = "";
try
{
Initial lsIniT = new Initial();
if (lsGdb.Gdb.State == System.Data.ConnectionState.Closed)
{
lsGdb.ConnectDatabase();
}
lsIniT.CreateTblInitial(lsGdb.Gdb);
SalesHeader lsSH = new SalesHeader();
SalesDetail lsSD = new SalesDetail();
SaleSPayment lsSPa = new SaleSPayment();
SumPayment lsSPy = new SumPayment();
SumSales lsSSa = new SumSales();
//KingPower lsGen = new KingPower();
double ldoMulti = 0;
DateTime ldSaleDate, ldVoidDate, ldReturnDate;
Int32 i = 0, j = 0;
string lsDataSH1_10 = "", lsDataSH11_20 = "", lsDataSH21_34 = "", lsPlNameE = "";
string lsDataSD1_10 = "", lsDataSD11_23 = "";
string lsDataSPa1_11 = "", lsTime = "";
string lsDataSPy1_8 = "";
string lsDataSSa1_8 = "";
string[] lsDataSH = new string[200];
string[] lsDataSD = new string[200];
string[] lsDataSPa = new string[200];
string[] lsDataSPy = new string[200];
string[] lsDataSSa = new string[200];
string[] lsMaster = new string[2000];
string[] lsData = new string[2000];
//ArrayList lsList = new ArrayList();
string lsFileName = "", lsVouDate = "", lsPath = "D://thahr30//", lsMemID = "", lsVatRate = "";
IniFile lsIni = new IniFile("thahr30.ini");
lsSH.ShopCode = lsIni.GetString("kingpower", "shopcode", "0");
lsSD.ShopCode = lsSH.ShopCode;
lsSPa.ShopCode = lsSH.ShopCode;
lsSPy.ShopCode = lsSH.ShopCode;
lsSSa.ShopCode = lsSH.ShopCode;
lsSH.Branch_Code = lsIni.GetString("kingpower", "branch_code", "0");
lsSD.Branch_Code = lsSH.Branch_Code;
lsSPa.Branch_Code = lsSH.Branch_Code;
lsSPy.Branch_Code = lsSH.Branch_Code;
lsSSa.Branch_Code = lsSH.Branch_Code;
lsSD.Std_Cate_Code = lsIni.GetString("kingpower", "std_cate_code", "0");
lsSD.Vat_Rate = lsIni.GetString("thahr30", "vatrate", "0");
lsSD.Unit_Code = lsIni.GetString("kingpower", "unit_code", "0");
lsSPa.Currency_Code = lsIni.GetString("kingpower", "currency_code", "THB");
lsSH.Vat_Type = lsIni.GetString("kingpower", "vat_type", "1");
lsSD.Vat_Type = lsSH.Vat_Type;
ldoMulti = Convert.ToDouble(lsIni.GetString("kingpower", "ratekingpower", "15.00"));
lsSPy.Currency_Code = lsSPa.Currency_Code;
lsSPa.Rate = lsIni.GetString("thahr30", "USTOBAHT", "0");
lsSPy.Rate = lsSPa.Rate;
string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost");
string lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3");
string lsUserName = lsIni.GetString("thahr30", "username", "janepop");
string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5");
lsVatRate = lsIni.GetString("thahr30", "vatrate", "7.00");
lsPath = lsIni.GetString("kingpower", "pathtextfilekingpower", "D://thahr30//text//");
lsFileName = lsSH.ShopCode + "_SALES_" + aYYYYMMDDHHMMSS.Substring(0, 4) + aYYYYMMDDHHMMSS.Substring(5, 2) + aYYYYMMDDHHMMSS.Substring(8, 8);
MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******"");
conn.Open();
//conn = lsGdb.Gdb;
MySqlConnection conn1 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******"");
conn1.Open();
//conn1 = lsGdb.Gdb;
string lsDate = aYYYYMMDDHHMMSS.Substring(0, 4) + "-" + aYYYYMMDDHHMMSS.Substring(5, 2) + "-" + aYYYYMMDDHHMMSS.Substring(8, 2);
lsSQL = "Select v.vouno, v.staffcode, v.voudate, v.counter1, v.couno, v.shiftcode, v.restime, v.provcode, " //0-7
+ "v.hotelcode, v.guestfirstname, v.nationcode, v.roomcode, v.visitt, v.pax, v.roomrate, " //8-14
+ "v.statuscode, v.depositamt, v.checkindate, v.checkoutdate, v.confirmperson, v.remark, " // 15-20
+ "v.flag, v.resrooms, v.flagsend, v.guestlastname, v.roomno, v.mac, v.flagconfirm, " // 21-27
+ "v.confirmremark, v.personintrip, v.prefix, v.roomrate1, v.pay_type, v.currency_code, m.memnamee1, v.memplcode, v.voiddate, v.returndate, v.remarkreturn " // 28-34
+ "From voucher v, member m Where v.flag <> '4' and v.voudate = '" + lsDate + "' and m.memid = v.hotelcode and v.memplcode not in ('-','') ";
lsSQL = lsSQL + "Union ";
lsSQL = lsSQL + "Select v.vouno, v.staffcode, v.voudate, v.counter1, v.couno, v.shiftcode, v.restime, v.provcode, " //0-7
+ "v.hotelcode, v.guestfirstname, v.nationcode, v.roomcode, v.visitt, v.pax, v.roomrate, " //8-14
+ "v.statuscode, v.depositamt, v.checkindate, v.checkoutdate, v.confirmperson, v.remark, " // 15-20
+ "v.flag, v.resrooms, v.flagsend, v.guestlastname, v.roomno, v.mac, v.flagconfirm, " // 21-27
+ "v.confirmremark, v.personintrip, v.prefix, v.roomrate1, v.pay_type, v.currency_code, m.memnamee1, v.memplcode, v.voiddate, v.returndate, v.remarkreturn " // 28-34
+ "From voucher v, member m Where v.flag = '5' and v.returndate = '" + lsDate + "' and m.memid = v.hotelcode and v.memplcode not in ('-','') ";
MySqlCommand lsComm = new MySqlCommand(lsSQL, conn);
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
StreamWriter lsSW = new StreamWriter(lsPath + lsFileName + ".Txt");
lsTextSaleFileName = lsPath + lsFileName + ".Txt";
lsDataSH[0] = "[SALESHEADER]";
lsDataSD[0] = "[SALESDETAIL]";
lsDataSPa[0] = "[SALESPAYMENT]";
lsDataSPy[0] = "[SUMPAYMENT]";
lsDataSSa[0] = "[SUMSALES]";
lsSPy.Sale_Date = lsVouDate;
lsSSa.Sale_Date = lsSPy.Sale_Date;
if (lsRead.HasRows)
{
//lsVat_Rate = lsMaster.lsVatRate;
double ldoAmt_Inv_Vat = 0, ldoAmt_EXC_Vat = 0, ldoVat_Amt = 0, ldoAmt = 0;
while (lsRead.Read())
{
i++;
lsSH.Branch_Code = lsRead["counter1"].ToString();
lsSD.Branch_Code = lsSH.Branch_Code;
lsSPa.Branch_Code = lsSH.Branch_Code;
lsSPy.Branch_Code = lsSH.Branch_Code;
lsSSa.Branch_Code = lsSH.Branch_Code;
lsSH.Sale_NO = lsRead["vouno"].ToString();
lsSD.Sale_NO = lsSH.Sale_NO;
lsSPa.Sale_NO = lsSH.Sale_NO;
lsSH.POS_NO = lsRead["mac"].ToString();
lsSD.POS_NO = lsSH.POS_NO;
lsSPa.POS_NO = lsSH.POS_NO;
lsSH.Sale_TYPE = "1";
lsSD.Sale_TYPE = lsSH.Sale_TYPE;
lsSPa.Sale_TYPE = lsSH.Sale_TYPE;
ldSaleDate = Convert.ToDateTime(lsRead["voudate"]);
if (lsRead["flag"].ToString() == "3")
{
//MySqlDbType.Datetime aaa;
//aaa = lsRead.GetMySqlDateTime(36);
//ldVoidDate = Convert.ToDateTime(lsRead.GetMySqlDateTime(36));
Boolean lbVoid = lsRead.GetMySqlDateTime(36).IsValidDateTime;
lsSQL = lsRead.GetMySqlDateTime(36).ToString();
if (lbVoid)
{
ldVoidDate = Convert.ToDateTime(lsSQL);
lsSH.Void_Date = ldVoidDate.Year.ToString("0000") + "-" + ldVoidDate.Month.ToString("00") + "-" + ldVoidDate.Day.ToString("00") + " " + ldVoidDate.Hour.ToString("00") + ":" + ldVoidDate.Minute.ToString("00") + ":" + ldVoidDate.Second.ToString("00");
}
else
{
lsSH.Void_Date = "";
}
}
else
{
lsSH.Void_Date = "";
}
lsSH.Void_Reason = "";
lsSH.Sale_Date = ldSaleDate.Year.ToString("0000") + "-" + ldSaleDate.Month.ToString("00") + "-" + ldSaleDate.Day.ToString("00");
if (lsRead["flag"].ToString() == "5")
{
Boolean lbReturnVoucher = lsRead.GetMySqlDateTime(37).IsValidDateTime;
if (lbReturnVoucher)
{
ldReturnDate = Convert.ToDateTime(lsRead["returndate"]);
lsSH.Return_Reason = lsRead["remarkreturn"].ToString();
lsSH.Sale_Date = ldReturnDate.Year.ToString("0000") + "-" + ldReturnDate.Month.ToString("00") + "-" + ldReturnDate.Day.ToString("00");
}
else
{
lsSH.Return_Reason = "";
}
}
else
{
}
lsSD.Sale_Date = lsSH.Sale_Date;
lsSPa.Sale_Date = lsSH.Sale_Date;
lsSD.SEQ = "1";
lsSH.Shift_NO = lsRead["shiftcode"].ToString();
lsMemID = lsRead["hotelcode"].ToString();
if (lsMemID == lsMem)
{
j++;
}
else
{
lsMem = lsMemID;
j = 1;
}
lsPlNameE = lsRead["memplcode"].ToString();
lsPlNameE = lsIniT.SelectInitial(lsIniT.TblTypeRoom, lsPlNameE.Substring(5, 2), Initial.WhereSelect.aCodetoName);
lsSD.Prod_Serv_Name = lsRead.GetValue(34).ToString() + " [" + lsPlNameE + "]";
lsSD.Prod_Serv_code = lsRead["memplcode"].ToString();
lsSD.Prod_Serv_QTY = lsRead.GetValue(13).ToString();
lsSD.Prod_Serv_QTY = "1";
lsTime = "";
lsTime = lsRead["restime"].ToString();
if (lsTime.Length < 5)
{
lsTime = "00:00";
}
lsSH.Doc_Date = lsSH.Sale_Date + " " + lsTime + ":00";
lsSH.Create_Date = lsSH.Doc_Date;
lsSH.Trans_Date = lsSH.Doc_Date;
lsSH.Member_ID = "";
lsSH.SVC_ID = "";
lsSH.Name = lsRead["guestfirstname"].ToString() + " " + lsRead["guestlastname"].ToString();
lsSH.Flight_NO = "";
lsSH.Flight_Date = "";
lsSH.Nation_Code = lsRead["nationcode"].ToString();
lsSH.Nation_Code = "";
lsSH.PassPort_NO = "";
lsSH.Birth_Date = "";
lsSH.Sex = "";
//lsSH.Vat_Type = "1";
lsSD.Vat_Type = lsSH.Vat_Type;
ldoAmt = Convert.ToDouble(lsRead[lsCalAmt]);
ldoAmt = (ldoAmt * ldoMulti) / 100;
if (lsFlagVAT == "exclude")
{
ldoAmt_EXC_Vat = ldoAmt;
ldoAmt_Inv_Vat = ldoAmt + (ldoAmt * (Convert.ToDouble(lsVatRate) / 100));
}
else
{
ldoAmt_Inv_Vat = ldoAmt;
ldoAmt_EXC_Vat = ldoAmt_Inv_Vat / 1.07;
}
lsError = lsSH.Name;
lsSH.AMT_EXC_VAT = ldoAmt_EXC_Vat.ToString("0.00");
lsSD.AMT_EXC_VAT = lsSH.AMT_EXC_VAT;
lsSH.AMT_INC_VAT = ldoAmt_Inv_Vat.ToString("0.00");
lsSD.AMT_INC_VAT = lsSH.AMT_INC_VAT;
if (lsFlagVAT == "exclude")
{
lsSPa.Amount = lsSH.AMT_EXC_VAT;
lsSPa.Baht_AMT = lsSH.AMT_EXC_VAT;
}
else
{
lsSPa.Amount = lsSH.AMT_INC_VAT;
lsSPa.Baht_AMT = lsSH.AMT_INC_VAT;
}
lsSD.AOT_Price_Inc_VAT = lsSD.AMT_INC_VAT;
lsSD.AOT_Price_Exc_VAT = lsSD.AMT_EXC_VAT;
ldoVat_Amt = ldoAmt_Inv_Vat - ldoAmt_EXC_Vat;
lsSH.VAT_AMT = ldoVat_Amt.ToString("0.00");
lsSD.VAT_AMT = lsSH.VAT_AMT;
lsSH.Pro_Code = "";
lsSD.Pro_Code = "";
lsSH.Disc_Vat_AMT = "";
lsSH.Disc_AMT_Exc_Vat = "";
lsSD.Disc_Price_Exc_VAT = lsSH.Disc_AMT_Exc_Vat;
lsSH.Disc_AMT_INC_VAT = "";
lsSD.Disc_Price_INC_VAT = lsSH.Disc_AMT_INC_VAT;
lsSH.Service_Charge = "";
lsSD.Service_Charge = lsSH.Service_Charge;
lsSH.Ref_Sale_NO = "";
lsSH.Ref_POS_NO = "";
lsSH.Ref_Sale_Date = "";
lsSH.Return_Reason = "";
switch (lsRead["hotelcode"].ToString())
{
case "01":
lsSPa.Pay_TYPE = "1";
break;
case "02":
lsSPa.Pay_TYPE = "2";
break;
case "03":
lsSPa.Pay_TYPE = "3";
break;
default:
lsSPa.Pay_TYPE = "1";
break;
}
if (lsSD.Sale_NO == "49001000094")
{
lsSQL = "";
}
lsDataSH1_10 = lsSH.ShopCode + "|" + lsSH.Branch_Code + "|" + lsSH.Sale_NO + "|" + lsSH.POS_NO + "|" + lsSH.Sale_TYPE + "|" + lsSH.Sale_Date + "|" + lsSH.Shift_NO + "|" + lsSH.Doc_Date + "|" + lsSH.Create_Date + "|" + lsSH.Trans_Date;
lsDataSH11_20 = lsSH.Member_ID + "|" + lsSH.SVC_ID + "|" + lsSH.Name + "|" + lsSH.Flight_NO + "|" + lsSH.Flight_Date + "|" + lsSH.Nation_Code + "|" + lsSH.PassPort_NO + "|" + lsSH.Birth_Date + "|" + lsSH.Sex + "|" + lsSH.Vat_Type;
lsDataSH21_34 = lsSH.AMT_EXC_VAT + "|" + lsSH.VAT_AMT + "|" + lsSH.AMT_INC_VAT + "|" + lsSH.Pro_Code + "|" + lsSH.Disc_Vat_AMT + "|" + lsSH.Disc_AMT_Exc_Vat + "|" + lsSH.Disc_AMT_INC_VAT + "|" + lsSH.Service_Charge + "|" + lsSH.Void_Date + "|" + lsSH.Void_Reason + "|" + lsSH.Ref_Sale_NO + "|" + lsSH.Ref_POS_NO + "|" + lsSH.Ref_Sale_Date + "|" + lsSH.Return_Reason;
lsDataSH[i] = lsDataSH1_10 + "|" + lsDataSH11_20 + "|" + lsDataSH21_34;
lsDataSD1_10 = lsSD.ShopCode + "|" + lsSD.Branch_Code + "|" + lsSD.Sale_NO + "|" + lsSD.POS_NO + "|" + lsSD.Sale_TYPE + "|" + lsSD.Sale_Date + "|" + lsSD.SEQ + "|" + lsSD.Std_Cate_Code + "|" + lsSD.Prod_Serv_code + "|" + lsSD.Prod_Serv_Name;
lsDataSD11_23 = lsSD.Vat_Type + "|" + lsSD.Vat_Rate + "|" + lsSD.Prod_Serv_QTY + "|" + lsSD.Unit_Code + "|" + lsSD.AOT_Price_Exc_VAT + "|" + lsSD.AOT_Price_Inc_VAT + "|" + lsSD.AMT_EXC_VAT + "|" + lsSD.VAT_AMT + "|" + lsSD.AMT_INC_VAT + "|" + lsSD.Pro_Code + "|" + lsSD.Disc_Price_Exc_VAT + "|" + lsSD.Disc_Price_INC_VAT + "|" + lsSD.Service_Charge;
lsDataSD[i] = lsDataSD1_10 + "|" + lsDataSD11_23;
lsDataSPa1_11 = lsSPa.ShopCode + "|" + lsSPa.Branch_Code + "|" + lsSPa.Sale_NO + "|" + lsSPa.POS_NO + "|" + lsSPa.Sale_TYPE + "|" + lsSPa.Sale_Date + "|" + lsSPa.Pay_TYPE + "|" + lsSPa.Currency_Code + "|" + lsSPa.Rate + "|" + lsSPa.Amount + "|" + lsSPa.Baht_AMT;
lsDataSPa[i] = lsDataSPa1_11;
lsSQL = "Select * From member Where memid = '" + lsSD.Prod_Serv_code + "' and pricestart <> " + ldoAmt_Inv_Vat;
MySqlCommand lsCommPrice = new MySqlCommand(lsSQL, conn1);
MySqlDataReader lsReadPrice;
lsReadPrice = lsCommPrice.ExecuteReader();
lsError = lsRead["vouno"].ToString();
if (lsReadPrice.HasRows)
{
j++;
//lsData = lsGen.GenMasterFileKingPower(lsReadPrice, ldoAmt_Inv_Vat);
lsData = GenMasterFileKingPower(lsReadPrice, ldoAmt_Inv_Vat);
foreach (string lsD in lsData)
{
if (lsD != null)
{
lsMaster[j] = lsD;
}
}
}
lsReadPrice.Close();
}
}
lsRead.Close();
double ldoAmount = 0;
lsSQL = "Select pay_type, counter1, currency_code, sum(roomrate) as roomrate, voudate, sum(roomrate1) as roomrate1 From voucher, member "
+ "Where voudate = '" + lsDate + "' and voucher.flag <> '4' and memplcode not in ('-','') and hotelcode = memid Group By pay_type, currency_code, counter1, voudate ";
MySqlCommand lsCommSpy = new MySqlCommand(lsSQL, conn);
MySqlDataReader lsReadSpy;
lsReadSpy = lsCommSpy.ExecuteReader();
if (lsReadSpy.HasRows)
{
i = 0;
while (lsReadSpy.Read())
{
i++;
lsSPy.Pay_TYPE = lsReadSpy["pay_type"].ToString();
lsSPy.Currency_Code = lsReadSpy["currency_code"].ToString();
ldSaleDate = Convert.ToDateTime(lsReadSpy["voudate"]);
lsSPy.Sale_Date = ldSaleDate.Year.ToString("0000") + "-" + ldSaleDate.Month.ToString("00") + "-" + ldSaleDate.Day.ToString("00");
lsSPy.Branch_Code = lsReadSpy["counter1"].ToString();
//ldoAmount = Convert.ToDouble(lsReadSpy["roomrate"]);
//ldoAmount = (ldoAmount * ldoMulti) / 100;
ldoAmount = Convert.ToDouble(lsReadSpy[lsCalAmt]);
ldoAmount = (ldoAmount * ldoMulti) / 100;
if (lsFlagVAT == "exclude")
{
ldoAmount = ldoAmount;
}
else
{
//ldoAmount = ldoAmount / 1.07;
ldoAmount = ldoAmount + (ldoAmount * (Convert.ToDouble(lsVatRate) / 100));
}
lsSPy.Amount = ldoAmount.ToString("0.00");
lsSPy.Baht_AMT = lsSPy.Amount;
lsDataSPy1_8 = lsSPy.ShopCode + "|" + lsSPy.Branch_Code + "|" + lsSPy.Sale_Date + "|" + lsSPy.Pay_TYPE + "|" + lsSPy.Currency_Code.ToUpper() + "|" + lsSPy.Rate + "|" + lsSPy.Amount + "|" + lsSPy.Amount;
lsDataSPy[i] = lsDataSPy1_8;
}
}
lsReadSpy.Close();
lsSQL = "Select counter1, sum(roomrate) as roomrate, sum(roomrate1) as roomrate1, voudate From voucher, member "
+ "Where voucher.flag <> '4' and voudate = '" + lsDate + "' and memplcode not in ('-','') and hotelcode = memid Group By counter1, voudate ";
MySqlCommand lsCommSSa = new MySqlCommand(lsSQL, conn);
MySqlDataReader lsReadSSa;
lsReadSSa = lsCommSSa.ExecuteReader();
if (lsReadSSa.HasRows)
{
i = 0;
while (lsReadSSa.Read())
{
i++;
lsSSa.Branch_Code = lsReadSSa["counter1"].ToString();
ldSaleDate = Convert.ToDateTime(lsReadSSa["voudate"]);
lsSSa.Sale_Date = ldSaleDate.Year.ToString("0000") + "-" + ldSaleDate.Month.ToString("00") + "-" + ldSaleDate.Day.ToString("00");
//ldoAmount = Convert.ToDouble(lsReadSSa["amt"]);
//ldoAmount = (ldoAmount * ldoMulti) / 100;
ldoAmount = Convert.ToDouble(lsReadSSa[lsCalAmt]);
ldoAmount = (ldoAmount * ldoMulti) / 100;
if (lsFlagVAT == "exclude")
{
ldoAmount = ldoAmount;
}
else
{
//ldoAmount = ldoAmount / 1.07;
ldoAmount = ldoAmount + (ldoAmount * (Convert.ToDouble(lsVatRate) / 100));
}
lsSSa.Sale_Header_AMT = ldoAmount.ToString("0.00");
lsSSa.Net_Sale_Header_AMT = ldoAmount.ToString("0.00");
lsSSa.Sale_DTL_AMT = ldoAmount.ToString("0.00");
lsSSa.Net_Sale_DTL_AMT = ldoAmount.ToString("0.00");
lsSSa.Payment_AMT = ldoAmount.ToString("0.00");
lsDataSSa1_8 = lsSSa.ShopCode + "|" + lsSSa.Branch_Code + "|" + lsSSa.Sale_Date + "|" + lsSSa.Sale_Header_AMT + "|" + lsSSa.Net_Sale_Header_AMT + "|" + lsSSa.Sale_DTL_AMT + "|" + lsSSa.Net_Sale_DTL_AMT + "|" + lsSSa.Payment_AMT;
lsDataSSa[i] = lsDataSSa1_8;
}
}
lsReadSpy.Close();
//lsSW.WriteLine(lsDataD[0]);
foreach (string lsD in lsDataSH)
{
if (lsD != null)
{
lsSW.WriteLine(lsD);
}
}
foreach (string lsD in lsDataSD)
{
if (lsD != null)
{
lsSW.WriteLine(lsD);
}
}
foreach (string lsD in lsDataSPa)
{
if (lsD != null)
{
lsSW.WriteLine(lsD);
}
}
foreach (string lsD in lsDataSPy)
{
if (lsD != null)
{
lsSW.WriteLine(lsD);
}
}
foreach (string lsD in lsDataSSa)
{
if (lsD != null)
{
lsSW.WriteLine(lsD);
}
}
lsSW.Close();
conn.Close();
conn1.Close();
//KingPower lsGen = new KingPower();
//lsGen.CreateTextFile(lsMaster, aYYYYMMDDHHMMSS);
CreateTextProductFile(lsMaster, aYYYYMMDDHHMMSS);
lbReturn = true;
}
catch (Exception e)
{
string ls = "äÁèÊÒÁÒöàµÃÕÂÁ¢éÍÁÙÅ Print ä´é ";
lsGdb.WriteLogError(ls, e, lsError, "GenSaleDailyKingPower ");
//MessageBox.Show(ls + " " + eAcc.Message.ToString(), eAcc.Source.ToString(), MessageBoxButtons.OK);
}
return(lbReturn);
}
private string[] GenMasterFileKingPower(MySqlDataReader aRead)
{
Int32 i = 0, j = 0;
double ldoPriceStart;
string[] lsData = new string[3000];
string lsSQL = "", lsVouDate = "", lsDay = "", lsTRoomName = "";
string lsData1_10 = "", lsData11_23 = "", lsMemID = "", lsMemName = "";
string lsUserName = lsIni.GetString("thahr30", "username", "janepop");
string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5");
string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost");
string lsDatabase = lsIni.GetString("thahr30", "databasename", "localhost");
string StrConn = "Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******"";
MySqlConnection Conn = new MySqlConnection(StrConn);
MasterKingPower lsMaster = new MasterKingPower();
double ldoMulti = 0;
try
{
//lsIniT.CreateTblTypeRoom(lsGdb.Gdb);
lsMaster.ReVat = 1.07;
lsMaster.ShopCode = lsIni.GetString("kingpower", "shopcode", "0");
lsMaster.Std_Cate_Code = lsIni.GetString("kingpower", "std_cate_code", "0");
lsMaster.Branch_Code = lsIni.GetString("kingpower", "branch_code", "0");
lsMaster.ShpBnd_Code = lsIni.GetString("kingpower", "shpbnd_code", "0");
lsMaster.Currency_code = lsIni.GetString("kingpower", "currency_code", "0");
lsMaster.Rate = lsIni.GetString("kingpower", "rate", "0");
lsMaster.Unit_Code = lsIni.GetString("kingpower", "unit_code", "0");
lsMaster.VatRate = lsIni.GetString("thahr30", "vatrate", "0");
lsMaster.UStoBaht = lsIni.GetString("thahr30", "USTOBAHT", "0");
ldoMulti = Convert.ToDouble(lsIni.GetString("kingpower", "ratekingpower", "15.00"));
//lsMaster.ReVat = lsMaster.Vat_Rate;
Conn.Open();
lsDay = Convert.ToString(System.DateTime.Now.Day - 1);
lsVouDate = System.DateTime.Now.Year.ToString("0000") + "-" + System.DateTime.Now.Month.ToString("00") + "-" + lsDay;
if (aRead.HasRows)
{
//lsVat_Rate = lsMaster.lsVatRate;
lsMaster.Vat_Type = lsIni.GetString("kingpower", "vat_type", "1");
lsMaster.ReQuest_Date = System.DateTime.Now.Year.ToString("0000") + "-" + System.DateTime.Now.Month.ToString("00") + "-" + System.DateTime.Now.Day.ToString("00");
//lsMaster.ReQuest_Date = "2006-09-28";
lsMaster.Request_Exc_Vat = "";
lsMaster.Request_Inc_Vat = "";
lsMaster.RequestEFF_SDate = System.DateTime.Now.Year.ToString("0000") + "-" + System.DateTime.Now.Month.ToString("00") + "-" + System.DateTime.Now.Day.ToString("00") + " " + System.DateTime.Now.Hour.ToString("00") + ":" + System.DateTime.Now.Minute.ToString("00") + ":" + System.DateTime.Now.Second.ToString("00");
/*if ((System.DateTime.Now.Year.ToString("0000") + "-" + System.DateTime.Now.Month.ToString("00") + "-" + System.DateTime.Now.Day.ToString("00") + " " + System.DateTime.Now.Hour.ToString("00") + ":" + System.DateTime.Now.Minute.ToString("00") + ":" + System.DateTime.Now.Second.ToString("00")) > (System.DateTime.Now.AddYears(1).Year.ToString("0000") + "-09-27 23:59:59"))
* {
* lsMaster.RequestEFF_EDate = System.DateTime.Now.AddYears(1).Year.ToString("0000") + "-09-27 23:59:59";
* lsMaster.Ref_Price_Date = "2006-09-27";
* }
* else
* {*/
lsMaster.RequestEFF_EDate = System.DateTime.Now.AddYears(1).Year.ToString("0000") + "-09-27 23:59:59";
lsMaster.Ref_Price_Date = System.DateTime.Now.AddYears(1).Year.ToString("0000") + "-09-27";
//}
//lsMaster.RequestEFF_SDate = "2006-09-28 00:00:00";
//lsMaster.RequestEFF_EDate = "2011-09-27 23:59:59";
//lsMaster.RequestEFF_EDate = DateTime.Now.AddYears(1).tos;
lsMaster.Ref_Price = "7.0";
lsMaster.Ref_Price_SRC = "Don Muang";
//lsMaster.Ref_Price_Date = "2006-09-27";
lsMaster.Ref_Code_1 = "";
lsMaster.Ref_Code_2 = "";
lsMaster.Ref_Code_3 = "";
lsMaster.Ref_Code_4 = "";
lsMaster.Ref_Code_5 = "";
while (aRead.Read())
{
lsMemID = aRead.GetValue(0).ToString();
if (lsMemID == "-")
{
lsSQL = "";
}
lsSQL = "select * From memberpricelist Where memid = '" + lsMemID + "' and flagsendkingpower = '1'";
MySqlCommand Comm = new MySqlCommand(lsSQL, Conn);
MySqlDataReader Read;
Read = Comm.ExecuteReader();
if (Read.HasRows)
{
j = 0;
while (Read.Read())
{
i++;
if (Read["flagoldkingpower"].ToString() == "1")
{
lsMaster.Trans_Type = "1";
}
else
{
lsMaster.Trans_Type = "2";
}
lsSQL = aRead.GetValue(1).ToString();
lsMemName = aRead.GetValue(1).ToString();
lsMaster.Ref_Price_SRC = lsMemName;
ldoPriceStart = Convert.ToDouble(aRead.GetValue(12));
lsSQL = Convert.ToString(aRead.GetValue(12));
decimal cc = Convert.ToDecimal(aRead.GetValue(12));
//ldoPriceStart = (Convert.ToDouble(aRead.GetValue(12)) * Convert.ToDouble(lsMaster.UStoBaht));
if (Read["remark"].ToString() == "US$")
{
ldoPriceStart = (Convert.ToDouble(Read["pricestart"]) * Convert.ToDouble(lsMaster.UStoBaht));
}
else
{
ldoPriceStart = Convert.ToDouble(Read["pricestart"]);
}
ldoPriceStart = (ldoPriceStart * ldoMulti) / 100;
//decimal bbb = Convert.ToDecimal(ldoPriceStart);
decimal aa = decimal.Round(Convert.ToDecimal(ldoPriceStart * lsMaster.ReVat), 2); //lsMaster.Request_Inc_Vat = Convert.ToString(decimal.Round(Convert.ToDecimal(ldoPriceStart), 2));
//lsMaster.Request_Inc_Vat = ldoPriceStart.ToString("0.00");
//lsMaster.Vat_Type;
//lsMaster.Request_Inc_Vat = ldoPriceStart.ToString("0.00");
if (lsMaster.Vat_Type == "2")
{
decimal aaa = Convert.ToDecimal(ldoPriceStart + (ldoPriceStart * Convert.ToDouble(lsMaster.VatRate) / 100));
lsMaster.Request_Inc_Vat = aaa.ToString("0.00");
lsMaster.Request_Exc_Vat = ldoPriceStart.ToString("0.00");
}
else
{
lsMaster.Request_Inc_Vat = Convert.ToString(aa);
lsMaster.Request_Exc_Vat = Convert.ToString(ldoPriceStart);
}
//lsMaster.Request_Inc_Vat = lsMaster.Request_Inc_Vat;
lsMaster.Ref_Price = lsMaster.Request_Exc_Vat;
lsTRoomName = lsIniT.SelectInitial(lsIniT.TblTypeRoom, Read["plcode"].ToString(), Initial.WhereSelect.aCodetoName);
j++;
lsData1_10 = lsMaster.ShopCode + "|" + lsMaster.Std_Cate_Code + "|" + lsMemID + Read["plcode"].ToString() + "|" + lsMemName + "[" + lsTRoomName + "]" + "|" + lsMaster.ShpBnd_Code + "|" + lsMaster.BarCode + "|" + lsMaster.Trans_Type + "|" + lsMaster.Vat_Type + "|" + lsMaster.VatRate + "|" + lsMaster.Unit_Code;
lsData11_23 = lsMaster.ReQuest_Date + "|" + lsMaster.Request_Exc_Vat + "|" + lsMaster.Request_Inc_Vat + "|" + lsMaster.RequestEFF_SDate + "|" + lsMaster.RequestEFF_EDate + "|" + lsMaster.Ref_Price + "|" + lsMaster.Ref_Price_SRC + "|" + lsMaster.Ref_Price_Date + "|" + lsMaster.Ref_Code_1 + "|" + lsMaster.Ref_Code_2 + "|" + lsMaster.Ref_Code_3 + "|" + lsMaster.Ref_Code_4 + "|" + lsMaster.Ref_Code_5;
lsData[i] = lsData1_10 + "|" + lsData11_23;
}
}
Read.Close();
}
}
Conn.Close();
}
catch (Exception e)
{
string ls = "äÁèÊÒÁÒöàµÃÕÂÁ¢éÍÁÙÅ Print ä´é ";
lsGdb.WriteLogError(ls, e, "", "GenMasterFileKingPower ");
//MessageBox.Show(ls + " " + eAcc.Message.ToString(), eAcc.Source.ToString(), MessageBoxButtons.OK);
}
return(lsData);
}
private Boolean SelectMember(string aColumn, string aMem)
{
if (lsGdb.Gdb.State == ConnectionState.Closed)
{
lsGdb.ConnectDatabase();
}
else
{
lsGdb.Gdb.Close();
lsGdb.Gdb.Open();
//lsGdb.ConnectDatabase();
}
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Cursor.Show();
PaintGrdView();
Boolean lbReturn = true;
string lsSQL = "", lsTMem = "", lsRegionName = "", lsSearch = "";
Int32 i = 0, j = 0;
//IniFile lsIni = new IniFile("thahr30.ini");
//string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost");
//string lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3");
lsSearch = aMem.Replace("'", "''");
if (aMem == "AA")
{
lsSQL = "Select count(*) cnt "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
+ "Order By m.memnamee1";
}
else if (aMem == "modify")
{
lsSQL = "Select count(*) cnt "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
+ "Where m.flag = '1' "//+ "Where flagoutlook = '1' "
+ "Order By m.memnamee1";
}
else
{
lsSQL = "Select count(*) cnt "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
+ "Where " + aColumn + " like '" + lsSearch + "%' "
+ "Order By m.memnamee1";
}
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
while (lsRead.Read())
{
i = Convert.ToInt32(lsRead["cnt"].ToString());
}
lsRead.Close();
GrdView.ActiveSheet.RowCount = i + 1;
if (aMem == "AA")
{
lsSQL = "Select memid, memnamee1, numroom, m.tmemcode, hotelchainname, star, m.regioncode, p.provnamet, m.flagresign, m.flagsale "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
//+ "Where c.contactid = (select c.contactid From contact c where c.refid = m.memid and c.flagskk9 ='1' having max(contactid)) "
+ "Order By m.memnamee1";
}
else if (aMem == "")
{
lsSQL = "Select memid, memnamee1, numroom, m.tmemcode, hotelchainname, star, m.regioncode, p.provnamet, m.flagresign, m.flagsale "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
+ "Where m.flag = '1' "//+ "Where flagoutlook = '1' "
+ "Order By m.memnamee1";
}
else
{
lsSQL = "Select memid, memnamee1, numroom, m.tmemcode, hotelchainname, star, m.regioncode, p.provnamet, m.flagresign, m.flagsale "
+ "From member m left join hotelchain h on h.hotelchaincode =m.hotelchaincode left join province p on m.provcode = p.provcode "
+ "Where " + aColumn + " like '" + lsSearch + "%' "
+ "Order By m.memnamee1";
}
lsComm.CommandText = lsSQL;
lsRead = lsComm.ExecuteReader();
i = 0;
Pb1.Minimum = 0;
Pb1.Maximum = GrdView.ActiveSheet.RowCount;
Pb1.Visible = true;
SL1.Visible = true;
if (lsRead.HasRows)
{
while (lsRead.Read())
{
j = i;
//if (lsRead["memid"].ToString() == "00774")
//{
// lsSQL = "";
//}
lsTMem = lsIniT.SelectInitial(lsIniT.TblTypeMem, lsRead["tmemcode"].ToString(), Initial.WhereSelect.aCodetoName);
lsRegionName = lsIniT.SelectInitial(lsIniT.TblRegion, lsRead["regioncode"].ToString(), Initial.WhereSelect.aCodetoName);
GrdView.ActiveSheet.SetText(i, liColMemID, lsRead["memid"].ToString());
GrdView.ActiveSheet.SetText(i, liColMemNameE, lsRead["memnamee1"].ToString());
GrdView.ActiveSheet.SetText(i, liColRoom, lsRead["numroom"].ToString());
GrdView.ActiveSheet.SetText(i, liColRegion, lsRegionName);
GrdView.ActiveSheet.SetText(i, liColTMem, lsTMem);
GrdView.ActiveSheet.SetText(i, liColHotelChain, lsRead["hotelchainname"].ToString());
GrdView.ActiveSheet.SetText(i, liColStar1, lsRead["star"].ToString());
GrdView.ActiveSheet.SetText(i, liColProvNameT, lsRead["provnamet"].ToString());
//GrdView.ActiveSheet.SetText(i, liColContactNameSkk9, lsRead["contactnamet"].ToString() + " " + lsRead["contactsurnamet"].ToString());
//GrdView.ActiveSheet.SetActiveCell(i, 1);
Pb1.Value = i;
SL1.Text = i.ToString() + " / " + (GrdView.ActiveSheet.RowCount - 1);
j = i % 2;
if ((j % 2) != 0)
{
GrdView.ActiveSheet.Rows[i].BackColor = Color.LightGoldenrodYellow;
}
if (lsRead["flagresign"].ToString() == "1")
{
GrdView.ActiveSheet.Rows[i].BackColor = Color.RosyBrown;
}
else if (lsRead["flagsale"].ToString() == "0")
{
GrdView.ActiveSheet.Rows[i].BackColor = Color.BurlyWood;
}
switch (lsRead["star"].ToString())
{
case "1":
{
GrdView.ActiveSheet.SetValue(i, liColStar1, Img1.Images[0]);
break;
}
case "2":
{
GrdView.ActiveSheet.SetValue(i, liColStar1, Img2.Images[0]);
break;
}
case "3":
{
GrdView.ActiveSheet.SetValue(i, liColStar1, Img3.Images[0]);
break;
}
case "4":
{
GrdView.ActiveSheet.SetValue(i, liColStar1, Img4.Images[0]);
break;
}
case "5":
{
GrdView.ActiveSheet.SetValue(i, liColStar1, Img5.Images[0]);
break;
}
}
i++;
//Application.DoEvents();
}
}
else
{
lbReturn = false;
}
Pb1.Visible = false;
SL1.Visible = false;
lsRead.Close();
Cursor.Current = System.Windows.Forms.Cursors.Default;
Cursor.Show();
return(lbReturn);
}
public void SumTypeMember(Boolean aCreateIniT)
{
string lsAccess = "", lsTMemCode = "", lsTmemNameE = "", lsVouDate = "";
double ldoCnt = 0, ldoSum = 0;
if (lsGdb.Gdb.State == System.Data.ConnectionState.Closed)
{
lsGdb.ConnectDatabase();
}
if (aCreateIniT)
{
lsIniT.CreateTblInitial(lsGdb.Gdb);
}
lsAccess = lsIni.GetString("thahr30", "accessdatabase", "0");
lsSQL = "Delete From rptvoucher";
OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lsAccess + ";");
acc.Open();
OleDbCommand accCom = new OleDbCommand(lsSQL, acc);
accCom.ExecuteNonQuery();
lsSQL = "Select count(*) as cnt, sum(numroom) as numroom, tmemcode From member Where flag = '1' Group by tmemcode";
MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb);
MySqlDataReader lsRead;
lsRead = lsComm.ExecuteReader();
if (lsRead.HasRows)
{
//j= lsRead.RecordsAffected;
lsVouDate = DateTime.Today.Day.ToString("00") + "/" + DateTime.Today.Month.ToString("00") + "/" + DateTime.Today.Year.ToString("0000");
while (lsRead.Read())
{
try
{
lsTMemCode = lsRead["tmemcode"].ToString();
ldoCnt = Convert.ToDouble(lsRead["cnt"]);
ldoSum = Convert.ToDouble(lsRead["numroom"]);
lsTmemNameE = lsIniT.SelectInitial(lsIniT.TblTypeMem, lsTMemCode, Initial.WhereSelect.aCodetoName);
lsSQL = "Insert Into rptvoucher(vouno, fullname, deposit, rate1, checkin, checkout)"
+ "Values('" + lsTMemCode + "','" + lsTmemNameE + "'," + ldoCnt + "," + ldoSum + ",'" + lsVouDate + "','" + lsVouDate + "')";
OleDbCommand accCom2 = new OleDbCommand(lsSQL, acc);
accCom2.ExecuteNonQuery();
}
catch (Exception e)
{
//lsSQL = "";
string ls = "ÁÕ¢éÍÁÙźҧáË觼Դ¾ÅÒ´ ÃÒ§ҹÍÒ¨ä´é¢éÍÁÙÅ·ÕèäÁè¶Ù¡µéͧ ";
lsGdb.WriteLogError(ls, e, lsSQL, "SumTypeMember ");
MessageBox.Show(ls + "\n" + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK);
}
}
lsRead.Close();
acc.Close();
acc.Dispose();
accCom.Dispose();
}
}