private void exportToExcel_Click(object sender, EventArgs e) { string lsExcelFileName = lsIni.GetString("thahr30", "exporttoexcelfilename", "member.xls"); string lsExcelFileNameWithHeader = lsIni.GetString("thahr30", "exporttoexcelfilewithheader", "0"); switch (lsExcelFileNameWithHeader) { case "0": { GrdView.SaveExcel(Application.StartupPath + "\\" + lsExcelFileName, FarPoint.Win.Spread.Model.IncludeHeaders.None); break; } case "1": { GrdView.SaveExcel(Application.StartupPath + "\\" + lsExcelFileName, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly); break; } } Process myProcess = new Process(); string myDocumentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); myProcess.StartInfo.FileName = Application.StartupPath + "\\" + lsExcelFileName; myProcess.StartInfo.CreateNoWindow = true; myProcess.Start(); Cursor.Current = System.Windows.Forms.Cursors.Default; Cursor.Show(); }
public VoucherReturn() { InitializeComponent(); lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost"); lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3"); lsCounter = lsIni.GetString("thahr30", "counter", "001"); }
private void Main_Load(object sender, EventArgs e) { string lsLoginOn = lsIni.GetString("thahr30", "loginon", "0"); string lsPrivileges = lsIni.GetString("thahr30", "privilegeson", "0"); Boolean lbCancel = false; if (lsLoginOn == "1") { StaffLogin frm = new StaffLogin(); frm.ShowDialog(this); if (frm.StaffName == "loginfail" || frm.StaffName == "") { CloseForm(); lbCancel = true; } else { lsStaffID = frm.StaffID; lsGdb.Gdb = frm.Connnection; if (lsPrivileges == "1") { SelectStaffPrivileges(lsStaffID); } SL1.Items[0].Text = "Database " + lsGdb.Gdb.Database + " Version " + lsGdb.Gdb.ServerVersion + " "; } lsStaffName = frm.StaffName; this.Text = "Staff name " + frm.StaffName + lsLastUpdate; frm.Dispose(); } //this.Text = "Staff name " + frm.StaffName + lsLastUpdate; if (lbCancel == false) { //CreateGraph(zg1); } }
private void button9_Click(object sender, EventArgs e) { Int32 i = 0; IniFile lsIni = new IniFile("thahr30.ini"); string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost"); string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); string lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3"); string lsUserName = lsIni.GetString("thahr30", "username", "janepop"); string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5"); string lsSQL = "Select memid, memnamee1 From member where memid = '00707'"; string lsMemId = ""; MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn.Open(); MySqlConnection conn1 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn1.Open(); MySqlConnection conn2 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn2.Open(); MySqlDataReader lsRead; MySqlCommand lsComm = new MySqlCommand(lsSQL, conn); lsSQL = ""; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { lsMemId = lsRead["memid"].ToString(); //lsSQL = "Select depositamt, memplcode From voucher Where hotelcode = '" + lsMemId + "'"; lsSQL = "Select max(noteid) noteid From membernote Where refid = '" + lsMemId + "'"; MySqlCommand Comm1 = new MySqlCommand(lsSQL, conn1); MySqlDataReader lsRead1; lsRead1 = Comm1.ExecuteReader(); if (lsRead1.Read()) { lsSQL = lsRead1["noteid"].ToString(); if (lsSQL != "") { lsSQL = "Update member Set noteid = '" + lsRead1["noteid"].ToString() + "' Where memid = '" + lsMemId + "'"; MySqlCommand Comm2 = new MySqlCommand(lsSQL, conn2); Comm2.ExecuteNonQuery(); } } else { //j++; //textBox2.Text = j.ToString(); } i++; textBox1.Text = i.ToString(); lsRead1.Close(); Application.DoEvents(); } } lsRead.Close(); MessageBox.Show("OK", "", MessageBoxButtons.OK); }
private void MemberSendEmail_Load(object sender, EventArgs e) { TxtFrom.Text = iniFile.GetString("thahr30", "emailmemberfrom", "*****@*****.**"); string SMTP = iniFile.GetString("thahr30", "smtp", "mail.asianet.co.th"); TxtSMTP.Text = SMTP; //emailsendwait = txtEmailSendWait.Value = Convert.ToInt32(iniFile.GetString("thahr30", "emailsendwait", "4")); PaintGrdView(); }
private void button4_Click(object sender, EventArgs e) { 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"); string lsUserName = lsIni.GetString("thahr30", "username", "janepop"); string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5"); string lsSQL = "Select memid, memnamee1 From member "; string lsMemId = ""; MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn.Open(); MySqlConnection conn1 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn1.Open(); MySqlConnection conn2 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn2.Open(); MySqlDataReader lsRead; MySqlCommand lsComm = new MySqlCommand(lsSQL, conn); lsSQL = ""; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { lsMemId = lsRead["memid"].ToString(); lsSQL = "Select provcode From hotel Where hotelcode = '" + lsMemId + "'"; MySqlCommand Comm1 = new MySqlCommand(lsSQL, conn1); MySqlDataReader lsRead1; lsRead1 = Comm1.ExecuteReader(); if (lsRead1.Read()) { lsSQL = "Update member Set provcode = '" + lsRead1["provcode"].ToString() + "' Where memid = '" + lsMemId + "'"; MySqlCommand Comm2 = new MySqlCommand(lsSQL, conn2); Comm2.ExecuteNonQuery(); i++; textBox1.Text = i.ToString(); } else { j++; textBox2.Text = j.ToString(); } lsRead1.Close(); } } lsRead.Close(); }
private void ShopView_Load(object sender, EventArgs e) { Pb1.Visible = false; //lsGdb.ConnectDatabase(); flagcounterlink=1 if (lsGdb.Gdb.State == ConnectionState.Closed) { lsGdb.ConnectDatabase(); } string lsCounterLink = lsIni.GetString("thahr30", "flagcounterlink", "0"); if (lsCounterLink == "1") { BtnRec.Enabled = false; } lsIniT.CreateTblInitial(lsGdb.Gdb); lsGdb.SelectCbo(CboYear.ComboBox, "", Connection.TableIniT.YearName); lsGdb.SelectCbo(CboMonth.ComboBox, "", Connection.TableIniT.MonthName); GrdView.Top = this.Top + 35; GrdView.Left = this.Left + 15; GrdView.Height = this.Height - 100; GrdView.Width = this.Width - 25; GbSum.Top = 37; GbSum.Left = 700; CboMonth.ComboBox.SelectedValue = DateTime.Today.Month.ToString(); CboYear.ComboBox.SelectedValue = Convert.ToString(DateTime.Today.Year + 543); GrdView.Sheets.Count = 2; PaintGrdMain(); PaintGrdMainSumDate(); VisibleGbSum(true); SelectVoucher(CboMonth.ComboBox.SelectedValue.ToString()); SelectVoucherSumDay(CboMonth.ComboBox.SelectedValue.ToString()); }
private void KingPowerWeb_Load(object sender, EventArgs e) { string lsURL; lsURL = lsIni.GetString("thahr30", "kingpowerintranet", "http://10.0.0.1/kps/"); //MessageBox.Show(lsURL, "lsURL"); Web.Navigate(lsURL); }
private void ServiceIP_Load(object sender, EventArgs e) { string lsURL; lsURL = lsIni.GetString("thahr30", "myipaddress", "http://www.myipaddress.com"); //MessageBox.Show(lsURL, "lsURL"); Web.Navigate(lsURL); }
private void MemberSendEmailTO_Load(object sender, EventArgs e) { PaintGrdView(); //ini = new IniFile(Environment.CurrentDirectory + "\\thahr30.ini"); string aaa = "D:\thahr30\thahr30.ini"; //aaa = aaa.Replace("EXE", "ini"); //aaa = aaa.ToLower(); string accessemail = ini.GetString("thahr30", "accessdatabaseemail", "D:\\thahr30\\email.mdb"); string sql = ""; OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessemail + ";"); acc.Open(); OleDbCommand accCom = new OleDbCommand(sql, acc); sql = "Select locaprovname From members Group By locaprovname Order By locaprovname"; accCom.CommandText = sql; OleDbDataReader rsacc; rsacc = accCom.ExecuteReader(); if (rsacc.HasRows) { while (rsacc.Read()) { CboProvince.Items.Add(rsacc["locaprovname"].ToString()); } } rsacc.Close(); sql = "Select regname From members Group By regname Order By regname"; accCom.CommandText = sql; rsacc = accCom.ExecuteReader(); if (rsacc.HasRows) { while (rsacc.Read()) { cboRegion.Items.Add(rsacc["regname"].ToString()); } } rsacc.Close(); sql = "Select typecode From members Group By typecode Order By typecode"; accCom.CommandText = sql; rsacc = accCom.ExecuteReader(); if (rsacc.HasRows) { while (rsacc.Read()) { cboType.Items.Add(rsacc["typecode"].ToString()); } } rsacc.Close(); acc.Close(); }
private void PrepareDateMaster(string aMaster) { 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"); string lsSQL = "Select * From member "; MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); try { conn.Open(); if (aMaster == "masterall") { lsSQL = "Select * From member "; } else { lsSQL = "Select * From member Where memid = '" + CboMember.SelectedValue.ToString() + "'"; } MySqlCommand lsComm = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead; lsRead = lsComm.ExecuteReader(); //lsGen.CreateTextFile("master", lsRead); //lsSendVoucher.ConnectDatabase(); if (lsGen.CreateTextProductFile("master", lsRead)) { lsTextFileName = lsGen.TextFileName; MessageBox.Show("àµÃÕÂÁ¢éÍÁÙÅ àÃÕºÃéÍÂ", "Prepare Data All", MessageBoxButtons.OK); } lsRead.Close(); } catch (MySqlException e) { string ls = "äÁèÊÒÁÒöÊÃéÒ§ àµÃÕÂÁ¢éÍÁÙÅ Master ä´é "; lsGdb.WriteLogError(ls, e, "", "KingPowerGenData "); MessageBox.Show(ls + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK); } }
public Boolean getConnectThaiHotels() { IniFile lsIni = new IniFile("thahr30.ini"); Boolean lbReturn = true; 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"); string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); if (HostName == "local") { lsHostName = lsServer; lsDatabaseName = lsDatabase; lsUserName = "******"; lsPassword = "******"; } else { lsHostName = lsServer; lsDatabaseName = lsDatabase; } if (Gdb != null) { Gdb.Close(); } try { Gdb = new MySqlConnection("Data Source=" + lsHostName + ";Database=" + lsDatabaseName + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ";Port=3306;Connect Timeout=3000;"); Gdb.Open(); } catch (MySqlException ex) { lbReturn = false; MessageBox.Show("Error connecting to the server: " + ex.Message + "\n" + Gdb.ConnectionString); } return(lbReturn); }
public void PrintGrd(FarPoint.Win.Spread.FpSpread Grd) { string lsSQL = "", lsCol = "", lsAccess = "", ls = "", lsAddress = ""; Boolean lbFlag = false; lsAccess = lsIni.GetString("thahr30", "accessdatabase", "0"); lsSQL = "Delete From rptreport"; OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lsAccess + ";"); acc.Open(); OleDbCommand accCom = new OleDbCommand(lsSQL, acc); accCom.ExecuteNonQuery(); for (int i = 0; i <= Grd.Sheets[0].RowCount - 1; i++) { if (i == 279) { MessageBox.Show("i=" + i.ToString()); } try { lsSQL = Grd.Sheets[0].GetText(i, 0); lsAddress = ""; lsAddress = Grd.Sheets[0].GetText(i, 14) + " " + Grd.Sheets[0].GetText(i, 15) + " " + Grd.Sheets[0].GetText(i, 16) + " " + Grd.Sheets[0].GetText(i, 17) + " " + Grd.Sheets[0].GetText(i, 18); lbFlag = Convert.ToBoolean(Grd.Sheets[0].GetValue(i, 0)); if (lbFlag) { lsSQL = "Insert Into rptreport(code, name, col1, " + "col2, col3, col4, " + "col5, col6, col7, " + "col8, col9, col10, " + "col11, col12, col13) " + "Values('" + Grd.Sheets[0].GetText(i, 1).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 2).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 10).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 4).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 12).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 3).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 5).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 9).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 12).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 7).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 8).Replace("'", "''") + "','" + lsAddress.Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 13).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 18).Replace("'", "''") + "','" + Grd.Sheets[0].GetText(i, 23).Replace("'", "''") + "')"; OleDbCommand accCom2 = new OleDbCommand(lsSQL, acc); accCom2.ExecuteNonQuery(); } } catch (Exception e) { MessageBox.Show(ls + "\n" + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK); } } acc.Close(); }
private void Timer_Tick(object sender, EventArgs eArgs) { string lsSMTP = lsIni.GetString("thahr30", "smtp", "mail.asianet.co.th"); Boolean aa = false; TxtEmailSubject.Text = GetSubjectSendEMail(); //if (aa) //{ SendEMail(lsSMTP); //} }
public string SelectDate(DateTime aDate, FlagDate aFlag, IniFile aIniFile) { string lsReturn = ""; string lsFlag = ""; switch (aFlag) { case FlagDate.DateMySQL: { lsReturn = SelectDateMySQL(aDate); break; } case FlagDate.DateShow: { lsFlag = aIniFile.GetString("thahr30", "flagdate", "0"); switch (lsFlag) { case "0": { lsReturn = SelectDateBudda(aDate); break; } case "1": { lsReturn = SelectDateEra(aDate); break; } } break; } case FlagDate.DateBuddhism: { lsReturn = aDate.Day.ToString("00") + "/" + aDate.Month.ToString("00") + "/" + (Convert.ToInt32(aDate.Year.ToString("0000")) + 543); break; } } return(lsReturn); }
public ArrayList MemberSendEmailSelect(string flagtype, string flagsubtype) { ArrayList sendemail = new ArrayList(); string cnt = ""; IniFile ini = new IniFile("thahr30.ini"); string accessemail = ini.GetString("thahr30", "accessdatabaseemail", "D:\\thahr30\\email.mdb"); string sql = "", where = ""; OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessemail + ";"); acc.Open(); OleDbCommand accCom = new OleDbCommand(sql, acc); int i = flagsubtype.IndexOf("("); string subtype = ""; if (i > 1) { subtype = flagsubtype.Substring(0, i); } else { subtype = flagsubtype; } sql = "Select email From members "; if (flagtype.Equals("province")) { if (!flagsubtype.Equals("")) { where = " Where locaprovname = '" + subtype + "'"; } else { where = " "; } } else if (flagtype.Equals("region")) { if (!flagsubtype.Equals("")) { where = " Where regname = '" + subtype + "'"; } else { where = " "; } } else { if (!flagsubtype.Equals("")) { where = " Where typecode = '" + subtype + "'"; } else { where = " "; } } accCom.CommandText = sql + where; OleDbDataReader rsacc; rsacc = accCom.ExecuteReader(); if (rsacc.HasRows) { while (rsacc.Read()) { sendemail.Add(rsacc["email"].ToString()); } } rsacc.Close(); acc.Close(); return(sendemail); }
private void button8_Click(object sender, EventArgs e) { int i = 0, liContactID = 0; string lsMemNameT = "", lsMemID = ""; IniFile lsIni = new IniFile("thahr30.ini"); string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost"); string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); string lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3"); string lsUserName = lsIni.GetString("thahr30", "username", "janepop"); string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5"); OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\thahr30\\thahr20.mdb;"); acc.Open(); MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn.Open(); string lsSQL = "Select count(*) as cnt From email "; OleDbCommand accCom = new OleDbCommand(lsSQL, acc); OleDbDataReader lsAccRead1; lsAccRead1 = accCom.ExecuteReader(); if (lsAccRead1.HasRows) { while (lsAccRead1.Read()) { Pb1.Minimum = 0; lsSQL = lsAccRead1.GetValue(0).ToString(); Pb1.Maximum = Convert.ToInt32(lsAccRead1.GetValue(0).ToString()); } } textBox1.Text = Pb1.Maximum.ToString(); lsAccRead1.Close(); //lsSQL = "Select * From regis1"; //accCom.CommandText = lsSQL; Pb1.Visible = true; SL1.Visible = true; OleDbDataReader lsRead; MySqlCommand lsCom = new MySqlCommand("delete from contact Where flagold = '2'", conn); lsCom.ExecuteNonQuery(); lsSQL = "Select * From email"; accCom.CommandText = lsSQL; lsRead = accCom.ExecuteReader(); while (lsRead.Read()) { i++; lsMemID = ""; lsMemNameT = lsRead["nameeng"].ToString(); lsMemNameT = lsMemNameT.Replace("'", "''"); lsSQL = "Select distinct memid From member Where memnamee1 like '%" + lsMemNameT + "%'"; MySqlCommand lsComm4 = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead2 = lsComm4.ExecuteReader(); while (lsRead2.Read()) { lsMemID = lsRead2["memid"].ToString(); } lsRead2.Close(); if (lsMemID != "") { //lsSQL = "Update member Set flagrestaurant = '" // + lsRead["vrestaurant"].ToString() + "', flagspa = '" // + lsRead["vspa"].ToString() + "', flagmeeting = '" // + lsRead["vmeeting"].ToString() + "', flagbusiness = '" // + lsRead["vbusiness"].ToString() + "', flagfitness = '" + lsRead["vfitness"].ToString() + "' Where memid = '" + lsRead["regisid"].ToString() + "'"; ; lsSQL = "select max(contactid) as cnt From contact "; MySqlCommand lsComm5 = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead1 = lsComm5.ExecuteReader(); while (lsRead1.Read()) { liContactID = Convert.ToInt32(lsRead1["cnt"]); } lsRead1.Close(); liContactID++; i++; lsSQL = "Insert Into contact(contactid, contactnamet, refid, email, flagold) " + "Values(" + liContactID + ",'E-Mail " + lsMemNameT + i.ToString() + "','" + lsMemID + "','" + lsRead["e-mail"].ToString() + "','2')"; MySqlCommand lsComm6 = new MySqlCommand(lsSQL, conn); lsComm6.ExecuteNonQuery(); OleDbCommand lsCom8 = new OleDbCommand("update email Set flag = '1' Where nameeng = '" + lsMemNameT + "'", acc); lsCom8.ExecuteNonQuery(); //liContactID++; //lsSQL = "Insert Into contact(contactid, contactnamet, contactnamee, contactsurnamet, contactsurnamee," // + "positiont, positione, refid) " // + "Values(" + liContactID + ",'" + lsRead["name1"].ToString() + "','" + lsRead["name2"].ToString() + "','-','-','-','-','" + lsRead["regisid"].ToString() + "')"; //MySqlCommand lsComm7 = new MySqlCommand(lsSQL, conn); //lsComm7.ExecuteNonQuery(); Application.DoEvents(); textBox3.Text = i.ToString(); } else { OleDbCommand lsCom9 = new OleDbCommand("update email Set flag = '0' Where nameeng = '" + lsMemNameT + "'", acc); lsCom9.ExecuteNonQuery(); } } lsRead.Close(); }
private void ImportVoucherOldData(string aFlag) { long i = 0; DateTime ldVouDate = new DateTime(); DateTime ldResTime = new DateTime(); DateTime ldCheckInDate = new DateTime(); DateTime ldCheckOutDate = new DateTime(); double ldoRoomRate1 = 0; string lsVouDate = "", lsResTime = "", lsCheckInDate = "", lsCheckOutDate = "", lsRoomNo = "", lsflagConfirm = "", lsConfirmRemark = "", lsFlag = "1", lsConfirmPerson = ""; string lsName = ""; IniFile lsIni = new IniFile("thahr30.ini"); 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"); OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\thahr30\\thahr20" + aFlag + ".mdb;"); acc.Open(); OleDbConnection acc1 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\thahr30\\thahr20" + aFlag + ".mdb;"); acc1.Open(); MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn.Open(); string lsSQL = "Select count(*) as cnt From voucher "; OleDbCommand accCom = new OleDbCommand(lsSQL, acc); OleDbDataReader lsAccRead1; lsAccRead1 = accCom.ExecuteReader(); if (lsAccRead1.HasRows) { while (lsAccRead1.Read()) { Pb1.Minimum = 0; lsSQL = lsAccRead1.GetValue(0).ToString(); Pb1.Maximum = Convert.ToInt32(lsAccRead1.GetValue(0).ToString()); } } lsAccRead1.Close(); Pb1.Visible = true; SL1.Visible = true; OleDbDataReader lsReadAcc; MySqlCommand lsCom = new MySqlCommand("Delete From voucher Where flagolddata = '" + aFlag + "'", conn); lsCom.ExecuteNonQuery(); lsSQL = "Select * From voucher"; accCom.CommandText = lsSQL; lsReadAcc = accCom.ExecuteReader(); while (lsReadAcc.Read()) { i++; lsName = lsReadAcc["guestname"].ToString(); lsName = lsName.Replace("\\", ""); ldVouDate = Convert.ToDateTime(lsReadAcc["voudate"]); lsVouDate = ldVouDate.Year.ToString("0000") + "-" + ldVouDate.Month.ToString("00") + "-" + ldVouDate.Day.ToString("00"); ldCheckInDate = Convert.ToDateTime(lsReadAcc["checkin"]); lsCheckInDate = ldCheckInDate.Year.ToString("0000") + "-" + ldCheckInDate.Month.ToString("00") + "-" + ldCheckInDate.Day.ToString("00"); ldCheckOutDate = Convert.ToDateTime(lsReadAcc["checkout"]); lsCheckOutDate = ldCheckOutDate.Year.ToString("0000") + "-" + ldCheckOutDate.Month.ToString("00") + "-" + ldCheckOutDate.Day.ToString("00"); ldResTime = Convert.ToDateTime(lsReadAcc["restime"]); lsResTime = ldResTime.Hour.ToString("00") + ":" + ldResTime.Minute.ToString("00") + ":" + ldResTime.Second.ToString("00"); if (lsReadAcc["statuscode"].ToString() == "06") { lsflagConfirm = "3"; } if (lsReadAcc["statuscode"].ToString() == "02") { lsflagConfirm = "3"; } if (lsReadAcc["statuscode"].ToString() == "03") { lsflagConfirm = "3"; } if (lsReadAcc["statuscode"].ToString() == "01") { lsflagConfirm = "4"; } //lsSQL = "Select * From actinfo Where voucherno = '" + lsReadAcc["voucherno"].ToString() + "'"; //OleDbCommand accCom1 = new OleDbCommand(lsSQL, acc1); //OleDbDataReader lsReadAcc1; //accCom1.CommandText = lsSQL; //lsReadAcc1 = accCom1.ExecuteReader(); //lsConfirmPerson = "-"; //ldoRoomRate1 = 0; //while (lsReadAcc1.Read()) //{ // lsConfirmPerson = lsReadAcc1["confirmperson"].ToString(); // ldoRoomRate1 = Convert.ToDouble(lsReadAcc1["roomrate1"]); //} //lsReadAcc1.Close(); lsSQL = "Insert Into voucher(vouno, staffcode, voudate, counter1, " + "shiftcode, restime, provcode, hotelcode, " + "guestfirstname, nationcode, roomcode, visitt, " + "pax, roomrate, statuscode, depositamt, " + "checkindate, checkoutdate, confirmperson, flagsend, " + "roomno, mac, flagconfirm, " + "confirmremark, roomrate1, flagolddata, flag, couno) " + "Values('" + lsReadAcc["voucherno"].ToString() + "-" + i.ToString("000000") + "','" + lsReadAcc["Staffcode"].ToString() + "','" + lsVouDate + "','" + lsReadAcc["counter1"].ToString() + "','" + lsReadAcc["shiftcode"].ToString() + "','" + lsResTime + "','" + lsReadAcc["provcode"].ToString() + "','" + lsReadAcc["hotelcode"].ToString() + "','" + lsName + "','" + lsReadAcc["nationcode"].ToString() + "','" + lsReadAcc["roomcode"].ToString() + "'," + lsReadAcc["days1"].ToString() + "," + lsReadAcc["pax"].ToString() + "," + lsReadAcc["roomrate"].ToString() + ",'" + lsReadAcc["statuscode"].ToString() + "'," + lsReadAcc["depositamt"].ToString() + ",'" + lsCheckInDate + "','" + lsCheckOutDate + "','" + lsConfirmPerson + "','2','" + lsRoomNo + "','" + lsReadAcc["counter1"].ToString() + "','" + lsflagConfirm + "','" + lsConfirmRemark + "'," + ldoRoomRate1 + ",'" + aFlag + "','" + lsFlag + "','')"; MySqlCommand lsComm1 = new MySqlCommand(lsSQL, conn); lsComm1.ExecuteNonQuery(); //Pb1.Value = Convert.ToInt32(i); } lsReadAcc.Close(); //MessageBox.Show("OK", "", MessageBoxButtons.OK); }
private void button3_Click(object sender, EventArgs e) { 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"); string lsUserName = lsIni.GetString("thahr30", "username", "janepop"); string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5"); string lsSQL = "Select hotelcode, roomrate, vouno, counter1 From voucher Where memplcode in ('-','')"; string lsVouNo = "", lsMemId = "", lsPlCode = "", lsMemPlCode = ""; MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn.Open(); MySqlConnection conn1 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn1.Open(); MySqlConnection conn2 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn2.Open(); MySqlConnection conn3 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******""); conn3.Open(); MySqlCommand lsComm = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead; lsSQL = ""; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { //MySqlDataAdapter aaa = new MySqlDataAdapter(); while (lsRead.Read()) { lsVouNo = lsRead["vouno"].ToString(); lsSQL = "Select plcode From memberpricelist Where memid = '" + lsRead["hotelcode"].ToString() + "' and pricestart <= " + lsRead["roomrate"].ToString() + " and priceend >= " + lsRead["roomrate"].ToString(); MySqlCommand lsComm1 = new MySqlCommand(lsSQL, conn1); MySqlDataReader lsRead1; lsRead1 = lsComm1.ExecuteReader(); if (lsVouNo == "") { lsSQL = ""; } if (lsVouNo == "49002000023") { lsSQL = lsRead["hotelcode"].ToString(); //lsSQL = lsRead3["plcode"].ToString(); } if (lsRead1.HasRows) { i++; while (lsRead1.Read()) { textBox1.Text = i.ToString(); listBox2.Items.Add(i.ToString() + " " + lsRead["hotelcode"].ToString() + lsRead1 ["plcode"].ToString()); lsSQL = "Update voucher Set memplcode = '" + lsRead["hotelcode"].ToString() + lsRead1 ["plcode"].ToString() + "' Where vouno = '" + lsVouNo + "' and counter1 = '" + lsRead["counter1"].ToString() + "'"; MySqlCommand lsComm2 = new MySqlCommand(lsSQL, conn2); lsComm2.ExecuteNonQuery(); } } else { j++; listBox1.Items.Add(j.ToString() + " " + lsRead["hotelcode"].ToString()); textBox2.Text = j.ToString(); lsSQL = "Select plcode From memberpricelist Where memid = '" + lsRead["hotelcode"].ToString() + "' and priceend <= " + lsRead["roomrate"].ToString(); MySqlCommand lsComm3 = new MySqlCommand(lsSQL, conn2); MySqlDataReader lsRead3; lsRead3 = lsComm3.ExecuteReader(); if (lsRead3.HasRows) { lsMemId = lsRead["hotelcode"].ToString(); while (lsRead3.Read()) { //lsSQL = lsRead["hotelcode"].ToString() + lsRead3["plcode"].ToString(); lsPlCode = lsRead3["plcode"].ToString(); lsMemPlCode = lsMemId + lsPlCode; listBox3.Items.Add(lsRead["hotelcode"].ToString() + lsRead3["plcode"].ToString()); lsSQL = "Update voucher Set memplcode = '" + lsMemPlCode + "' Where vouno = '" + lsVouNo + "'"; MySqlCommand lsComm4 = new MySqlCommand(lsSQL, conn3); lsComm4.ExecuteNonQuery(); } } lsRead3.Close(); } lsRead1.Close(); Application.DoEvents(); } } lsRead.Close(); }
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 void SendEMail(string aServer) { Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; Cursor.Show(); string lsSQL = "", lsDate = "", lsBody = "", lsSubject = ""; Int32 liCnt = 0, liDays = 0, liResRooms = 0, liCntCounter = 0, liDaysCounter = 0, liResRoomsCounter = 0; double ldoDepositAmt = 0, ldoDepositAmtCounter = 0; lsDate = lsGdb.SelectDateMySQL(TxtStartDate.Value); Connection lsConn = new Connection(); lsConn.ConnectDatabase(); MySqlConnection lsConn1 = new MySqlConnection(); lsConn1 = lsConn.Gdb; lsSubject = lsSubject + " " + lsGdb.SelectDateBudda(TxtStartDate.Value); lsSQL = "Select ifnull(count(*),0) as cnt, ifnull(sum(depositamt),0) as depositamt, ifnull(sum(visitt),0) as visitt, ifnull(sum(resrooms),0) as resrooms From voucher Where voudate = '" + lsDate + "' and flag in('1','2')"; MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb); MySqlDataReader lsRead; lsRead = lsComm.ExecuteReader(); //TxtBody.SelectionBullet = true; if (lsRead.HasRows) { while (lsRead.Read()) { liCnt = Convert.ToInt32(lsRead["cnt"]); liDays = Convert.ToInt32(lsRead["visitt"]); liResRooms = Convert.ToInt32(lsRead["resrooms"]); ldoDepositAmt = Convert.ToDouble(lsRead["depositamt"]); } } lsRead.Close(); lsBody = "Dear committee\n"; lsBody = lsBody + "Date : " + lsGdb.SelectDate(TxtStartDate.Value, Connection.FlagDate.DateShow, lsIni) + "\n"; lsBody = lsBody + "\n"; lsBody = lsBody + "Voucher total = " + liCnt + "\n"; lsBody = lsBody + " Days = " + liDays + "\n"; lsBody = lsBody + " Rooms = " + liResRooms + "\n"; lsBody = lsBody + " Deposit = " + ldoDepositAmt.ToString("0,000.00") + "\n"; lsBody = lsBody + "\n"; lsSQL = "Select * From counter Where flag = '1'"; MySqlCommand lsComm1 = new MySqlCommand(lsSQL, lsGdb.Gdb); MySqlDataReader lsRead1; lsRead1 = lsComm1.ExecuteReader(); if (lsRead1.HasRows) { while (lsRead1.Read()) { lsSQL = "Select ifnull(count(*),0) as cnt, ifnull(sum(depositamt),0) as depositamt, ifnull(sum(visitt),0) as visitt, ifnull(sum(resrooms),0) as resrooms From voucher Where voudate = '" + lsDate + "' and counter1 = '" + lsRead1["counterid"].ToString() + "' and flag in('1','2')"; MySqlCommand lsComm2 = new MySqlCommand(lsSQL, lsConn.Gdb); MySqlDataReader lsRead2; lsRead2 = lsComm2.ExecuteReader(); if (lsRead2.HasRows) { while (lsRead2.Read()) { liCntCounter = Convert.ToInt32(lsRead2["cnt"]); liDaysCounter = Convert.ToInt32(lsRead2["visitt"]); liResRoomsCounter = Convert.ToInt32(lsRead2["resrooms"]); ldoDepositAmtCounter = Convert.ToDouble(lsRead2["depositamt"]); lsBody = lsBody + "Counter : " + lsRead1["countername"].ToString() + "\n"; lsBody = lsBody + "Voucher total = " + liCntCounter + "\n"; lsBody = lsBody + " Days = " + liDaysCounter + "\n"; lsBody = lsBody + " Rooms = " + liResRoomsCounter + "\n"; lsBody = lsBody + " Deposit = " + ldoDepositAmtCounter.ToString("0,000.00") + "\n"; lsBody = lsBody + "\n"; lsSQL = ""; } } lsRead2.Close(); } } lsRead1.Close(); lsConn.Gdb.Close(); string lsTO = ""; string lsSMTP = lsIni.GetString("thahr30", "smtp", "mail.asianet.co.th"); string lsFrom = lsIni.GetString("thahr30", "emailfrom", "mail.asianet.co.th"); string lsTo = lsIni.GetString("thahr30", "emailto", "mail.asianet.co.th"); lsSubject = lsIni.GetString("thahr30", "emailsubjectdailyreport", "mail.asianet.co.th"); try { //lsTO = ""; //lsSubject = ""; //lsSubject = lsSubject; MailMessage lsEMail = new MailMessage(); MailAddress lsFromAddress = new MailAddress(lsFrom); Application.DoEvents(); lsEMail.From = lsFromAddress; lsEMail.To.Add(lsTO); //lsEMail.CC.Add("*****@*****.**"); lsEMail.Subject = lsSubject; lsEMail.Body = lsBody; SmtpClient lsSMTP1 = new SmtpClient(aServer); lsSMTP1.Credentials = CredentialCache.DefaultNetworkCredentials; lsSMTP1.Send(lsEMail); Application.DoEvents(); } catch (Exception e) { lsGdb.WriteLogError("", e, "", "SendEMail "); MessageBox.Show(e.Message.ToString(), e.Source.ToString()); } MessageBox.Show("send e-amil success", "E-Mail", MessageBoxButtons.OK); Cursor.Current = System.Windows.Forms.Cursors.Default; Cursor.Show(); }
public void CreateMonthlySummaryDeposit(string aStartDate, string aEndDate, string aFrHotal, string aToHotel, string aCounter, string aShift, Boolean aFlagBKK) { string lsSQL = ""; Int32 j = 0; Pb1.Visible = true; SL1.Visible = true; if (lsGdb.Gdb.State == System.Data.ConnectionState.Closed) { lsGdb.ConnectDatabase(); } //lsIniT.CreateTblInitial(lsGdb.Gdb); string lsStartDate = "", lsEndDate = ""; double ldoAmount = 0; DateTime ldStartDate = new DateTime(); DateTime ldEndDate = new DateTime(); DateTime ldEndDateCheck = new DateTime(); Int32 liDay = 0, k = 0; ldStartDate = Convert.ToDateTime(aStartDate); lsStartDate = (ldStartDate.Year) + "-" + ldStartDate.Month.ToString("00") + "-01"; //lsStartDate = lsGdb.SelectDateMySQL(ldStartDate); ldEndDate = lsGdb.LastDayofMonth(Convert.ToDateTime(aStartDate)); lsEndDate = lsGdb.SelectDateMySQL(ldEndDate); string lsName = "", lsCode = "", lsDate = ""; string lsRate15 = lsIni.GetString("kingpower", "ratekingpower", "15.00"); string lsRate20 = lsIni.GetString("kingpower", "ratekingpowervoucher", "20.00"); DateTime ldDate = new DateTime(); string lsAccess = lsIni.GetString("thahr30", "accessdatabase", "0"); lsSQL = "Delete From rptreport"; lsGdb.ConnectDatabase(); OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lsAccess + ";"); acc.Open(); OleDbCommand accCom = new OleDbCommand(lsSQL, acc); accCom.ExecuteNonQuery(); if (aFrHotal != "" && aToHotel != "") { if (aFlagBKK == true) { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 >= '" + aFrHotal + "' and m.memnamee1 <= '" + aToHotel + "' and v.provcode = '100000' Group By v.hotelcode Order By v.hotelcode"; } else { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 >= '" + aFrHotal + "' and m.memnamee1 <= '" + aToHotel + "' and v.provcode <> '100000' Group By v.hotelcode Order By v.hotelcode"; } } else if (aFrHotal == "" && aToHotel != "") { if (aFlagBKK == true) { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 <= '" + aToHotel + "' and v.provcode = '100000' Group By v.hotelcode Order By v.hotelcode"; } else { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 <= '" + aToHotel + "' and v.provcode <> '100000' Group By v.hotelcode Order By v.hotelcode"; } } else if (aFrHotal != "" && aToHotel == "") { if (aFlagBKK == true) { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 >= '" + aFrHotal + "' and v.provcode = '100000' Group By v.hotelcode Order By v.hotelcode"; } else { lsSQL = "Select v.hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.memnamee1 >= '" + aFrHotal + "' and v.provcode <> '100000' Group By v.hotelcode Order By v.hotelcode"; } } else { if (aFlagBKK == true) { lsSQL = "Select m.memid as hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.provcode = '100000' Group By m.memid Order By m.memid"; } else { lsSQL = "Select m.memid as hotelcode, m.memnamee1 From voucher v, member m Where v.voudate >= '" + lsStartDate + "' and v.voudate <= '" + lsEndDate + "' and v.flag in ('1','2') and v.hotelcode = m.memid and m.provcode <> '100000' Group By m.memid Order By m.memid"; } } MySqlCommand lsComm = new MySqlCommand(lsSQL, lsGdb.Gdb); MySqlDataReader lsRead; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { k++; lsName = lsRead["memnamee1"].ToString(); lsName = lsName.Replace("'", "''"); lsSQL = "Insert Into rptreport(code, name) Values('" + lsRead["hotelcode"].ToString() + "','" + lsName + "')"; OleDbCommand accCom2 = new OleDbCommand(lsSQL, acc); accCom2.ExecuteNonQuery(); } } lsRead.Close(); Pb1.Maximum = k; Pb1.Minimum = 0; lsSQL = "Select code, name From rptreport"; OleDbCommand lsAccCom1 = new OleDbCommand(lsSQL, acc); OleDbCommand accComUpdate = new OleDbCommand(lsSQL, acc); OleDbDataReader lsAccRead; lsAccRead = lsAccCom1.ExecuteReader(); ldEndDateCheck = ldEndDate; ldEndDate = Convert.ToDateTime(aEndDate); if (lsAccRead.HasRows) { MySqlCommand lsComm1 = new MySqlCommand(); lsComm1.Connection = lsGdb.Gdb; MySqlDataReader lsRead1; while (lsAccRead.Read()) { try { j++; liDay = ldStartDate.Day; ldDate = ldStartDate.AddDays(-1); Pb1.Value = j; //Application.DoEvents(); for (Int32 i = liDay; i <= ldEndDate.Day; i++) { ldDate = ldDate.AddDays(1); lsDate = lsGdb.SelectDateMySQL(ldDate); lsCode = lsAccRead["code"].ToString(); lsName = lsAccRead["name"].ToString(); SL1.Text = lsCode + " " + ldDate.ToShortDateString(); Application.DoEvents(); lsSQL = "Select sum(ifnull(roomrate1,0)) as sumdeposit From voucher Where flag in ('1','2') and voudate = '" + lsDate + "' and hotelcode = '" + lsCode + "'"; lsComm1.CommandText = lsSQL; lsRead1 = lsComm1.ExecuteReader(); if (lsRead1.HasRows) { while (lsRead1.Read()) { lsSQL = lsRead1["sumdeposit"].ToString(); if (lsRead1["sumdeposit"].ToString() == "") { ldoAmount = 0; } else { ldoAmount = lsRead1.GetDouble("sumdeposit"); //ldoAmount = ldoAmount * (Convert.ToDouble(lsRate15) / 100) * (Convert.ToDouble(lsRate20) / 100); } lsSQL = "Update rptreport Set " + i.ToString("00") + " = " + ldoAmount + " Where code = '" + lsCode + "'"; accComUpdate.CommandText = lsSQL; accComUpdate.ExecuteNonQuery(); } } lsRead1.Close(); } Application.DoEvents(); } catch (Exception e) { string ls = "ÁÕ¢éÍÁÙźҧáË觼Դ¾ÅÒ´ ÃÒ§ҹÍÒ¨ä´é¢éÍÁÙÅ·ÕèäÁè¶Ù¡µéͧ "; lsGdb.WriteLogError(ls, e, lsSQL, "CreateMonthlySummaryDeposit "); MessageBox.Show(ls + "\n" + " " + e.Message.ToString(), e.Source.ToString(), MessageBoxButtons.OK); } } } lsAccRead.Close(); if (Chk23.Checked == true) { lsSQL = "Select code, name From rptreport"; lsAccCom1.CommandText = lsSQL; lsAccRead = lsAccCom1.ExecuteReader(); if (lsAccRead.HasRows) { j = 0; lsStartDate = lsStartDate.Substring(0, lsStartDate.Length - 2) + "01"; ldEndDate = lsGdb.LastDayofMonth(Convert.ToDateTime(aStartDate)); lsEndDate = lsGdb.SelectDateMySQL(ldEndDate); while (lsAccRead.Read()) { lsCode = lsAccRead["code"].ToString(); lsSQL = "Select sum(ifnull(roomrate1,0)) as sumdeposit From voucher " + "Where flag in ('1','2') and voudate >= '" + lsStartDate + "' and voudate <= '" + lsEndDate + "' and hotelcode = '" + lsCode + "'"; lsComm.CommandText = lsSQL; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { lsSQL = lsRead["sumdeposit"].ToString(); if (lsRead["sumdeposit"].ToString() == "") { ldoAmount = 0; } else { ldoAmount = lsRead.GetDouble("sumdeposit"); //ldoAmount = ldoAmount * (Convert.ToDouble(lsRate15) / 100) * (Convert.ToDouble(lsRate20) / 100); } lsSQL = "Update rptreport Set 01 = " + ldoAmount + " Where code = '" + lsCode + "'"; accComUpdate.CommandText = lsSQL; accComUpdate.ExecuteNonQuery(); } } j++; Pb1.Value = j; lsRead.Close(); } } lsAccRead.Close(); } acc.Close(); acc.Dispose(); Pb1.Visible = false; SL1.Visible = false; }
private void ExporttoWord(string aFlag, string aSort) { if (lsGdb.Gdb.State == ConnectionState.Closed) { lsGdb.ConnectDatabase(); } else { lsGdb.Gdb.Close(); lsGdb.Gdb.Open(); //lsGdb.ConnectDatabase(); } string sql = "Select count(*) cnt From meetingcontact Where meetingid = '" + TxtMeetingID.Text + "' and contactname <> '' and flagvisit = '1'"; if (aFlag == "1") { sql = "Select count(*) cnt From meetingcontact Where meetingid = '" + TxtMeetingID.Text + "' and contactname <> ''"; } else { sql = "Select count(*) cnt From meetingcontact Where meetingid = '" + TxtMeetingID.Text + "' and contactname <> '' and flagvisit = '1'"; } Int32 liCnt = 0, i = 0; MySqlCommand lsComm = new MySqlCommand(sql, lsGdb.Gdb); MySqlDataReader lsRead; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { liCnt = Convert.ToInt32(lsRead["cnt"].ToString()); } } lsRead.Close(); if (liCnt == 0) { MessageBox.Show("äÁèÁÕ¢éÍÁÙÅ", "Export"); return; } string[,] lsArr = new string[liCnt + 1, 4]; if (aFlag == "1") { if (aSort == "memnamet") { sql = "Select mc.contactname, mc.companyname, mc.tmemnamet, mc.memnamet, m.skk9id " + "From meetingcontact mc left join member m on mc.memid = m.memid " + "Where mc.meetingid = '" + TxtMeetingID.Text + "' and mc.contactname <> '' Order By mc.tmemnamet, mc.memnamet"; } else { sql = "Select mc.contactname, mc.companyname, mc.tmemnamet, mc.memnamet, m.skk9id " + "From meetingcontact mc left join member m on mc.memid = m.memid " + "Where mc.meetingid = '" + TxtMeetingID.Text + "' and mc.contactname <> '' Order By mc.tmemnamet, mc.memnamet"; } } else { if (aSort == "memnamet") { sql = "Select mc.contactname, mc.companyname, mc.tmemnamet, mc.memnamet, m.skk9id " + "From meetingcontact mc left join member m on mc.memid = m.memid " + "Where mc.meetingid = '" + TxtMeetingID.Text + "' and mc.contactname <> '' and mc.flagvisit = '1' Order By tmemnamet, mc.memnamet"; } else { sql = "Select mc.contactname, mc.companyname, mc.tmemnamet, mc.memnamet, m.skk9id " + "From meetingcontact mc left join member m on mc.memid = m.memid " + "Where mc.meetingid = '" + TxtMeetingID.Text + "' and mc.contactname <> '' and mc.flagvisit = '1' Order By mc.tmemnamet, mc.companyname"; } } lsComm.CommandText = sql; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { lsArr[i, 0] = lsRead["contactname"].ToString(); lsArr[i, 1] = lsRead["memnamet"].ToString(); lsArr[i, 2] = lsRead["tmemnamet"].ToString(); lsArr[i, 3] = lsRead["skk9id"].ToString(); i++; } } lsRead.Close(); string lsMeetingFileName = lsIni.GetString("thahr30", "exportmeetingfilename", "meeting.doc"); object oMissing = System.Reflection.Missing.Value; object oEndOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */ //Start Word and create a new document. Word._Application oWord; Word._Document oDoc; oWord = new Word.Application(); oWord.Visible = true; oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing); //Insert a paragraph at the beginning of the document. Word.Paragraph oPara1; oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing); oPara1.Range.Text = TxtMeetingNameT.Text; oPara1.Range.Font.Bold = 1; oPara1.Format.SpaceAfter = 24; //24 pt spacing after paragraph. oPara1.Range.InsertParagraphAfter(); oPara1.Range.Text = TxtDescription.Text; oPara1.Range.Font.Bold = 1; oPara1.Format.SpaceAfter = 24; //24 pt spacing after paragraph. oPara1.Range.InsertParagraphAfter(); oPara1.Range.Text = TxtPlace.Text; //Insert a paragraph at the end of the document. //Word.Paragraph oPara2; object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range; //oPara2 = oDoc.Content.Paragraphs.Add(ref oRng); //oPara2.Range.Text = "Heading 2"; //oPara2.Format.SpaceAfter = 6; //oPara2.Range.InsertParagraphAfter(); ////Insert another paragraph. //Word.Paragraph oPara3; //oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range; //oPara3 = oDoc.Content.Paragraphs.Add(ref oRng); //oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:"; //oPara3.Range.Font.Bold = 0; //oPara3.Format.SpaceAfter = 24; //oPara3.Range.InsertParagraphAfter(); //Insert a 3 x 5 table, fill it with data, and make the first row //bold and italic. Word.Table oTable; Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range; oTable = oDoc.Tables.Add(wrdRng, liCnt, 5, ref oMissing, ref oMissing); oTable.Range.ParagraphFormat.SpaceAfter = 6; oTable.Rows[1].Range.Font.Bold = 1; oTable.Rows[1].Range.Font.Italic = 1; oTable.Columns[1].Width = oWord.InchesToPoints(1); //Change width of columns 1 & 2 oTable.Columns[2].Width = oWord.InchesToPoints(3); oTable.Columns[3].Width = oWord.InchesToPoints(3); oTable.Columns[4].Width = oWord.InchesToPoints(4); oTable.Columns[5].Width = oWord.InchesToPoints(5); int r, c; string strText; for (r = 0; r <= liCnt - 1; r++) { for (c = 0; c <= 5; c++) { strText = "r" + r + "c" + c; switch (c) { case 1: { oTable.Cell(r, c).Range.Text = r.ToString(); break; } case 2: { oTable.Cell(r, c).Range.Text = lsArr[r, 0].ToString(); break; } case 3: { oTable.Cell(r, c).Range.Text = lsArr[r, 1].ToString(); break; } case 4: { oTable.Cell(r, c).Range.Text = lsArr[r, 2].ToString(); break; } case 5: { oTable.Cell(r, c).Range.Text = lsArr[r, 3].ToString(); break; } } } } //Add text after the chart. wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range; wrdRng.InsertParagraphAfter(); wrdRng.InsertAfter("THE END."); //Process myProcess = new Process(); //string myDocumentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); //myProcess.StartInfo.FileName = Application.StartupPath + "\\" + lsMeetingFileName; //myProcess.StartInfo.CreateNoWindow = true; //myProcess.Start(); Cursor.Current = System.Windows.Forms.Cursors.Default; Cursor.Show(); MessageBox.Show("Export to Word success", "Export"); }
private void ConfigureCrystalReports(string aReportName) { try { Rpt.Height = this.Height; Rpt.Width = this.Width; Rpt.Top = this.Top; Rpt.Left = this.Left; Rpt.Visible = true; //Rpt.ReportSource = "D:\\ThaHr30\\ThaHr30\\Voucher.rpt"; Rpt.DisplayGroupTree = false; //Rpt.DisplayStatusBar = true; Rpt.DisplayToolbar = true; //Rpt.SelectionFormula = ""; Rpt.ShowPrintButton = true; custReport = new ReportDocument(); string reportPath = Application.StartupPath + "\\" + aReportName + ".rpt"; //custReport.SetParameterValue("line1", lsPara); //reportPath = "d:\\thahr30\\" + "voucher1.rpt"; Controls.Add(Rpt); //rdOrders.RecordSelectionFormula = "{Orders.Ship Via} = 'UPS'"; custReport.Load(reportPath); //foreach (CrystalDecisions.CrystalReports.Engine.Table tblLogon in custReport.Database.Tables){ // CrystalDecisions.Shared.TableLogOnInfo rptLogon = tblLogon.LogOnInfo; // //rptLogon.ConnectionInfo.ServerName = "ª×èÍ Server ¢Í§¤Ø³"; // //rptLogon.ConnectionInfo.UserID = "ª×èͼÙéãªé§Ò¹¢Í§¤Ø³"; // //rptLogon.ConnectionInfo.Password = "******"; // rptLogon.ConnectionInfo.DatabaseName = "C:\\ThaHr30\\thahr30.mdb"; // //rptLogon.ConnectionInfo.IntegratedSecurity = False; // tblLogon.ApplyLogOnInfo(rptLogon); //} //custReport.DataDefinition.FormulaFields["line1"].Text = "\"" + lsHLine1 + "\""; switch (lsReportName) { case "rptsalereport": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptsalereportcounter": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmeetingsign": { string a = "ãºÅ§ª×èÍ"; custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptdailypayinrecordpayment": { custReport.DataDefinition.FormulaFields["line1"].Text = "\"" + lsHLine1 + "\""; custReport.DataDefinition.FormulaFields["line2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptdailypayinrecordsu": { custReport.DataDefinition.FormulaFields["line1"].Text = "\"" + lsHLine1 + "\""; custReport.DataDefinition.FormulaFields["line2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsHLine3 + "\""; custReport.DataDefinition.FormulaFields["hleft2"].Text = "\"" + lsHLeft2 + "\""; break; } case "rptsumtypememberregion": { custReport.DataDefinition.FormulaFields["line2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptsumtypememberregion1": { custReport.DataDefinition.FormulaFields["line2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysalereport": { custReport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit01_11": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit12_22": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit23_28": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit23_29": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit23_30": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptmonthlysummarydeposit23_31": { custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsHLine1 + "\""; string lsHLine2 = lsIni.GetString("report", "rptmonthlysummarydeposi", "DAILY SUMMARY DEPOSIT REPORT "); custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsHLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsHLine3 + "\""; break; } case "rptskk9": { string lsrptskk9HLine1 = "", lsrptskk9HLine2 = "", lsrptskk9HLine3 = "", lsrptskk9FLine1 = "", lsrptskk9FLine2 = "", lsrptskk9FLine3 = ""; lsrptskk9HLine1 = lsIni.GetString("report", "skk9hline1", " "); lsrptskk9HLine2 = lsIni.GetString("report", "skk9hline2", " "); lsrptskk9HLine3 = lsIni.GetString("report", "skk9hline3", " "); lsrptskk9FLine1 = lsIni.GetString("report", "skk9fline1", " "); lsrptskk9FLine2 = lsIni.GetString("report", "skk9fline2", " "); lsrptskk9FLine3 = lsIni.GetString("report", "skk9fline3", " "); custReport.DataDefinition.FormulaFields["hline1"].Text = "\"" + lsrptskk9HLine1 + "\""; custReport.DataDefinition.FormulaFields["hline2"].Text = "\"" + lsrptskk9HLine2 + "\""; custReport.DataDefinition.FormulaFields["hline3"].Text = "\"" + lsrptskk9HLine3 + "\""; custReport.DataDefinition.FormulaFields["fline1"].Text = "\"" + lsrptskk9FLine1 + "\""; custReport.DataDefinition.FormulaFields["fline2"].Text = "\"" + lsrptskk9FLine2 + "\""; custReport.DataDefinition.FormulaFields["fline3"].Text = "\"" + lsrptskk9FLine3 + "\""; break; } } Rpt.ReportSource = custReport; //Rpt.ParameterFieldInfo = paramFields; custReport.Refresh(); Rpt.ShowCloseButton = true; Rpt.Show(); //Application.DoEvents(); //Rpt.Refresh(); } catch (Exception e) { MessageBox.Show(e.Message, "Report could not be created", MessageBoxButtons.OK); } //SetCurrentValuesForParameterField(customersByCityReport, arrayList); }
private void button10_Click(object sender, EventArgs e) { { int i = 0, liContactID = 0; Boolean lbFlag = false; IniFile lsIni = new IniFile("thahr30.ini"); string lsServer = lsIni.GetString("thahr30", "serverdatabasename", "localhost"); string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); string lsDatabase = lsIni.GetString("thahr30", "databasename", "thahr3"); string lsUserName = lsIni.GetString("thahr30", "username", "janepop"); string lsPassword = lsIni.GetString("thahr30", "password", "Ekartc2c5"); OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\thahr30\\thahr20.mdb;"); acc.Open(); MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn.Open(); string lsSQL = "Select count(*) as cnt From regis "; OleDbCommand accCom = new OleDbCommand(lsSQL, acc); OleDbDataReader lsAccRead1; lsAccRead1 = accCom.ExecuteReader(); if (lsAccRead1.HasRows) { while (lsAccRead1.Read()) { Pb1.Minimum = 0; lsSQL = lsAccRead1.GetValue(0).ToString(); Pb1.Maximum = Convert.ToInt32(lsAccRead1.GetValue(0).ToString()); } } lsAccRead1.Close(); //lsSQL = "Select * From regis1"; //accCom.CommandText = lsSQL; Pb1.Visible = true; SL1.Visible = true; OleDbDataReader lsRead; MySqlCommand lsCom = new MySqlCommand("truncate table memberowner", conn); lsCom.ExecuteNonQuery(); lsSQL = "Select * From regis"; accCom.CommandText = lsSQL; lsRead = accCom.ExecuteReader(); while (lsRead.Read()) { i++; lsSQL = "select ownernamet From memberowner Where ownernamet = '" + lsRead["companyname"].ToString() + "'"; MySqlCommand lsComm7 = new MySqlCommand(lsSQL, conn); lsComm7.ExecuteNonQuery(); MySqlDataReader lsRead2 = lsComm7.ExecuteReader(); if (lsRead2.HasRows == false) { lbFlag = true; } else { lbFlag = false; } lsRead2.Close(); if (lbFlag == true) { lsSQL = "select count(*) as cnt From memberowner "; MySqlCommand lsComm5 = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead1 = lsComm5.ExecuteReader(); while (lsRead1.Read()) { liContactID = Convert.ToInt32(lsRead1["cnt"]); } lsRead1.Close(); liContactID++; lsSQL = "Insert Into memberowner(refid, ownerid, ownernamet) " + "Values('" + lsRead["regisid"].ToString() + "'," + liContactID + ",'" + lsRead["companyname"].ToString() + "')"; MySqlCommand lsComm6 = new MySqlCommand(lsSQL, conn); lsComm6.ExecuteNonQuery(); lsSQL = "Update member Set ownerid = " + liContactID + " Where memid = '" + lsRead["regisid"].ToString() + "'"; MySqlCommand lsComm8 = new MySqlCommand(lsSQL, conn); lsComm8.ExecuteNonQuery(); } } lsRead.Close(); } }
private Boolean SelectStaff() { 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 = ""; 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"); lsSQL = "Select count(*) as cnt From staff Where flagstaff = '" + flagstaff + "'"; MySqlCommand Comm1 = new MySqlCommand(lsSQL, lsGdb.Gdb); MySqlDataReader lsRead1; lsRead1 = Comm1.ExecuteReader(); while (lsRead1.Read()) { i = Convert.ToInt32(lsRead1["cnt"].ToString()); } lsRead1.Close(); GrdView.ActiveSheet.RowCount = i + 1; lsSQL = "Select * From staff Where flagstaff = '" + flagstaff + "' Order By staffname"; MySqlCommand Comm = new MySqlCommand(lsSQL, lsGdb.Gdb); MySqlDataReader lsRead; lsRead = Comm.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; GrdView.ActiveSheet.SetText(i, liColStaffID, lsRead["staffcode"].ToString()); GrdView.ActiveSheet.SetText(i, liColSatffName, lsRead["staffname"].ToString() + " " + lsRead ["staffsurname"].ToString()); GrdView.ActiveSheet.SetText(i, liColIDCard, lsRead["id"].ToString()); //GrdView.ActiveSheet.SetText(i, liColStaffType, lsRead["username"].ToString()); GrdView.ActiveSheet.SetText(i, liColUserName, lsRead["username"].ToString()); 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; } 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 SendEMail(string aServer, Boolean aFlag, string aTO, string aReportName) { Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; Cursor.Show(); string lsTO = "", lsSubject = "", lsStartDate = "", lsEndDate = "", lsLine2 = "", lsLine1 = "", lsLine3 = ""; string lsPath = Application.StartupPath + "\\Counter\\", lsFileName = ""; string reportPath = "", lsDay = ""; if (aReportName == "rptdailypayinrecordsuemail") { reportPath = Application.StartupPath + "\\rptdailypayinrecordsuemail.rpt"; } else { reportPath = Application.StartupPath + "\\RptDailyPayinRecordPayment.rpt"; } lsStartDate = TxtDate.Value.Year.ToString() + "-" + TxtDate.Value.Month.ToString("00") + "-" + TxtDate.Value.Day.ToString("00"); lsDay = TxtDate.Value.Day.ToString("00") + "-" + TxtDate.Value.Month.ToString("00") + "-" + Convert.ToString(TxtDate.Value.Year + 543); lsEndDate = lsStartDate; Report lsRpt = new Report(); try { TxtTo.Text = aTO; //TxtTo.Text = "*****@*****.**"; lsLine1 = lsIni.GetString("thahr30", "companyname", "Thai Hotels "); lsLine2 = lsIni.GetString("report", aReportName, "DAILY SUMMARY DEPOSIT REPORT "); lsLine3 = "For date : " + lsDay;// + " to " + TxtDate.Value.Day.ToString("00") + "-" + TxtDate.Value.Month.ToString("00") + "-" + TxtDate.Value.Year.ToString(); if (aReportName == "rptdailypayinrecordsuemail") { lsFileName = "dailypayinrecord" + lsDay + ".pdf"; } else { lsFileName = "dailycash-credit" + lsDay + ".pdf";//DAILY PAY IN RECORD REPORT GROUP BY PAYMENT } lsRpt.CreateVoucherAcc(aReportName, lsStartDate, lsEndDate, "", "", "", "", true); if (Directory.Exists(lsPath) == false) { Directory.CreateDirectory(lsPath); } if (File.Exists(lsPath + "\\" + lsFileName)) { File.Delete(lsPath + "\\" + lsFileName); } ReportDocument RptExport = new ReportDocument(); RptExport.Load(reportPath); RptExport.DataDefinition.FormulaFields["line1"].Text = "\"" + lsLine1 + "\""; RptExport.DataDefinition.FormulaFields["line2"].Text = "\"" + lsLine2 + "\""; RptExport.DataDefinition.FormulaFields["line3"].Text = "\"" + lsLine3 + "\""; Application.DoEvents(); RptExport.ExportToDisk(ExportFormatType.PortableDocFormat, lsPath + "\\" + lsFileName); RptExport.Dispose(); Application.DoEvents(); lsTO = ""; lsSubject = ""; lsTO = TxtTo.Text; if (aReportName == "rptdailypayinrecordsuemail") { lsSubject = TxtSubject.Text.Replace("/", "-"); } else { lsSubject = "Daily Cash/Credit " + lsDay; } MailMessage lsEMail = new MailMessage(); MailAddress lsFromAddress = new MailAddress(TxtFrom.Text); Attachment lsAttach = new Attachment(lsPath + "\\" + lsFileName); Application.DoEvents(); lsEMail.From = lsFromAddress; lsEMail.To.Add(lsTO); //lsEMail.CC.Add("*****@*****.**"); lsEMail.Subject = lsSubject; lsEMail.Body = TxtBody.Text; lsEMail.Attachments.Add(lsAttach); SmtpClient lsSMTP = new SmtpClient(aServer); lsSMTP.Credentials = CredentialCache.DefaultNetworkCredentials; lsSMTP.Send(lsEMail); Application.DoEvents(); } catch (Exception e) { lsGdb.WriteLogError("", e, "", "SendEMail "); MessageBox.Show(e.Message.ToString(), e.Source.ToString()); } if (aFlag) { MessageBox.Show("send e-amil success", "E-Mail", MessageBoxButtons.OK); } Cursor.Current = System.Windows.Forms.Cursors.Default; Cursor.Show(); }
private void button1_Click(object sender, EventArgs e) { Connection lsGdb = new Connection(); Int32 i = 0, liNumRoom = 0, liAddressID = 0; string lsSubDistrict = "", lsDistrict = "", lsTele = "", lsFax = "", lsWeb = "", lsEmail = "", lsPostCode = "", lsSoi = ""; string lsMemID = "", lsMemNameT = "", lsMemNameE1 = "", lsTmemCode = "", lsRegCode = "", lsStar = "", lsMemNameE2 = "", lsFlagGreenLeft = ""; string lsFlagSale = "", lsTbisCode = "", lsRemark = "", lsNationCode = "", lsHotelChainCode = "", lsTypePricebaht = "", lsLine1 = "", lsProvCode = "", lsAddressCode = "", lsStartDate = "", lsEndDate = "", lsRegisDate = "", lsSKK9ID = ""; decimal ldoPriceStart = 0, ldoPriceEnd = 0; IniFile lsIni = new IniFile("thahr30.ini"); string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); 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"); OleDbConnection acc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\thahr30\\thahr20.mdb;"); acc.Open(); MySqlConnection conn = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn.Open(); string lsSQL = "Select count(*) as cnt From regis "; OleDbCommand accCom = new OleDbCommand(lsSQL, acc); OleDbDataReader lsAccRead1; lsAccRead1 = accCom.ExecuteReader(); if (lsAccRead1.HasRows) { while (lsAccRead1.Read()) { Pb1.Minimum = 0; lsSQL = lsAccRead1.GetValue(0).ToString(); Pb1.Maximum = Convert.ToInt32(lsAccRead1.GetValue(0).ToString()); } } lsAccRead1.Close(); Pb1.Visible = true; SL1.Visible = true; OleDbDataReader lsRead; MySqlCommand lsCom = new MySqlCommand("truncate table address", conn); lsCom.ExecuteNonQuery(); lsSQL = "Select * From regis"; accCom.CommandText = lsSQL; lsRead = accCom.ExecuteReader(); while (lsRead.Read()) { i++; lsMemID = lsRead.GetValue(0).ToString(); lsMemNameT = lsRead.GetValue(3).ToString(); //lsMemNameT = textBox1.Text; lsMemNameE1 = lsRead.GetValue(1).ToString(); lsMemNameE2 = lsRead["nameeng2"].ToString(); lsTmemCode = lsRead["typecode"].ToString(); lsRegCode = lsRead["regcode"].ToString(); lsStar = lsRead["class1"].ToString(); lsStartDate = lsGdb.SelectDateMySQL(Convert.ToDateTime(lsRead["inoperdate"])); lsEndDate = lsGdb.SelectDateMySQL(Convert.ToDateTime(lsRead["expiredate"])); lsRegisDate = lsGdb.SelectDateMySQL(Convert.ToDateTime(lsRead["regisdate"])); lsFlagGreenLeft = lsRead["flaggreenleft"].ToString(); liNumRoom = Convert.ToInt32(lsRead["size1"]); lsSQL = lsRead.GetValue(90).ToString(); if (lsRead.GetValue(90).ToString() != "") { ldoPriceStart = Convert.ToDecimal(lsRead["frroomprice"].ToString()); } else { ldoPriceStart = 0; } if (lsRead.GetValue(90).ToString() != "") { ldoPriceEnd = Convert.ToDecimal(lsRead["toroomprice"].ToString()); } else { ldoPriceEnd = 0; } lsFlagSale = lsRead["sale"].ToString(); lsTbisCode = lsRead["tbiscode"].ToString(); lsRemark = lsRead["remark"].ToString(); lsNationCode = lsRead["nationcode"].ToString(); lsHotelChainCode = lsRead["chaincode"].ToString(); lsSKK9ID = lsRead["membercode"].ToString(); lsTypePricebaht = "1"; if (ldoPriceStart > 0) { lsTypePricebaht = "0"; } lsSQL = "Update member Set memnamet = '" + lsMemNameT + "', memnamee1 = '" + lsMemNameE1 + "', tmemcode = '" + lsTmemCode + "', numroom = " + liNumRoom + ", regioncode = '" + lsRegCode + "', star = '" + lsStar + "', memnamee2 = '" + lsMemNameE2 + "', flaggreenleft = '" + lsFlagGreenLeft + "', flagsale = '" + lsFlagSale + "', tbiscode = '" + lsTbisCode + "', remark = '" + lsRemark + "', nationcode = '" + lsNationCode + "', hotelchaincode = '" + lsHotelChainCode + "', pricestart=" + ldoPriceStart + ", priceend = " + ldoPriceEnd + ", typepricebaht =" + lsTypePricebaht + ", startdate = '" + lsStartDate + "', enddate = '" + lsEndDate + "', regisdate = '" + lsRegisDate + "', skk9id = '" + lsSKK9ID + "' Where memid = '" + lsMemID + "'"; MySqlCommand lsCom2 = new MySqlCommand(lsSQL, conn); lsCom2.ExecuteNonQuery(); lsSQL = "select count(*) as cnt From address "; MySqlCommand lsComm5 = new MySqlCommand(lsSQL, conn); MySqlDataReader lsRead1 = lsComm5.ExecuteReader(); while (lsRead1.Read()) { liAddressID = Convert.ToInt32(lsRead1["cnt"]); } lsRead1.Close(); liAddressID++; lsSoi = lsRead["locasoi"].ToString(); lsSQL = lsRead["locasubdistrict"].ToString(); lsSubDistrict = lsRead["locasubdistrict"].ToString().Replace("µÓºÅ", ""); lsDistrict = lsRead["locadistrict"].ToString(); lsEmail = lsRead["locaemail"].ToString(); lsWeb = lsRead["locaweb"].ToString(); lsTele = lsRead["locatele"].ToString(); lsFax = lsRead["locafax"].ToString(); lsPostCode = lsRead["locapostcode"].ToString(); lsLine1 = lsRead["locano"].ToString() + " " + lsSoi + " " + lsRead["locaroad"].ToString(); //+ " " + lsSubDistrict + " " + lsDistrict; lsProvCode = lsRead["locaprovcode"].ToString(); lsSQL = "Delete From address Where refid = '" + lsMemID + "' and addressid = " + liAddressID; MySqlCommand lsComm3 = new MySqlCommand(lsSQL, conn); lsComm3.ExecuteNonQuery(); lsSQL = "Insert Into address (refid, addressid, addressname, line1, " + "provcode, subdistrictcode, districtcode, " + "fax, email, telephone, website, postcode, addresscode) " + "Values('" + lsMemID + "'," + liAddressID + ",'·ÕèÍÂÙè¢Í§âçáÃÁ','" + lsLine1 + "','" + lsProvCode + "','" + lsSubDistrict + "','" + lsDistrict + "','" + lsFax + "','" + lsEmail + "','" + lsTele + "','" + lsWeb + "','" + lsPostCode + "','106')"; MySqlCommand lsComm4 = new MySqlCommand(lsSQL, conn); lsComm4.ExecuteNonQuery(); liAddressID++; lsSoi = lsRead["mailsoi"].ToString(); lsSQL = lsRead["mailsubdistrict"].ToString(); lsSubDistrict = lsRead["mailsubdistrict"].ToString(); lsDistrict = lsRead["maildistrict"].ToString(); lsEmail = lsRead["mailemail"].ToString(); lsWeb = lsRead["mailweb"].ToString(); lsTele = lsRead["mailtele"].ToString(); lsFax = lsRead["mailfax"].ToString(); lsPostCode = lsRead["mailpostcode"].ToString(); lsLine1 = lsRead["mailno"].ToString() + " " + lsSoi + " " + lsRead["mailroad"].ToString(); //+ " " + lsSubDistrict + " " + lsDistrict; lsProvCode = lsRead["mailprovcode"].ToString(); lsSQL = "Delete From address Where refid = '" + lsMemID + "' and addressid = " + liAddressID; MySqlCommand lsComm7 = new MySqlCommand(lsSQL, conn); lsComm7.ExecuteNonQuery(); lsSQL = "Insert Into address (refid, addressid, addressname, line1, " + "provcode, subdistrictcode, districtcode, " + "fax, email, telephone, website, postcode, addresscode) " + "Values('" + lsMemID + "'," + liAddressID + ",'·ÕèÍÂÙèÊÓËÃѺÊè§àÍ¡ÊÒÃ','" + lsLine1 + "','" + lsProvCode + "','" + lsSubDistrict + "','" + lsDistrict + "','" + lsFax + "','" + lsEmail + "','" + lsTele + "','" + lsWeb + "','" + lsPostCode + "','107')"; MySqlCommand lsComm6 = new MySqlCommand(lsSQL, conn); lsComm6.ExecuteNonQuery(); liAddressID++; lsSoi = lsRead["bkksoi"].ToString(); lsSQL = lsRead["bkksubdistrict"].ToString(); lsSubDistrict = lsRead["bkksubdistrict"].ToString(); lsDistrict = lsRead["bkkdistrict"].ToString(); lsEmail = lsRead["bkkemail"].ToString(); lsWeb = lsRead["bkkweb"].ToString(); lsTele = lsRead["bkktele"].ToString(); lsFax = lsRead["bkkfax"].ToString(); lsPostCode = lsRead["bkkpostcode"].ToString(); lsLine1 = lsRead["bkkno"].ToString() + " " + lsSoi + " " + lsRead["mailroad"].ToString(); //+ " " + lsSubDistrict + " " + lsDistrict; lsProvCode = lsRead["bkkprovcode"].ToString(); lsSQL = "Delete From address Where refid = '" + lsMemID + "' and addressid = " + liAddressID; MySqlCommand lsComm8 = new MySqlCommand(lsSQL, conn); lsComm8.ExecuteNonQuery(); lsSQL = "Insert Into address (refid, addressid, addressname, line1, " + "provcode, subdistrictcode, districtcode, " + "fax, email, telephone, website, postcode, addresscode) " + "Values('" + lsMemID + "'," + liAddressID + ",'·ÕèÍÂÙè¢Í§Êӹѡ§Ò¹','" + lsLine1 + "','" + lsProvCode + "','" + lsSubDistrict + "','" + lsDistrict + "','" + lsFax + "','" + lsEmail + "','" + lsTele + "','" + lsWeb + "','" + lsPostCode + "','108')"; MySqlCommand lsComm9 = new MySqlCommand(lsSQL, conn); lsComm9.ExecuteNonQuery(); liAddressID++; lsSoi = lsRead["locasoit"].ToString(); lsSQL = lsRead["locasubdistrictt"].ToString(); lsSubDistrict = lsRead["locasubdistrictt"].ToString().Replace("µÓºÅ", ""); lsSubDistrict = lsSubDistrict.Replace("á¢Ç§", ""); lsDistrict = lsRead["locadistrictt"].ToString(); lsEmail = lsRead["locaemail"].ToString(); lsWeb = lsRead["locaweb"].ToString(); lsTele = lsRead["locatele"].ToString(); lsFax = lsRead["locafax"].ToString(); lsPostCode = lsRead["locapostcode"].ToString(); lsLine1 = lsRead["locano"].ToString() + " " + lsSoi + " " + lsRead["locaroadt"].ToString(); //+ " " + lsSubDistrict + " " + lsDistrict; lsProvCode = lsRead["locaprovcode"].ToString(); lsSQL = "Delete From address Where refid = '" + lsMemID + "' and addressid = " + liAddressID; MySqlCommand lsComm10 = new MySqlCommand(lsSQL, conn); lsComm10.ExecuteNonQuery(); lsSQL = "Insert Into address (refid, addressid, addressname, line1, " + "provcode, subdistrictcode, districtcode, " + "fax, email, telephone, website, postcode, addresscode) " + "Values('" + lsMemID + "'," + liAddressID + ",'·ÕèÍÂÙè¢Í§âçáÃÁÀÒÉÒä·Â','" + lsLine1 + "','" + lsProvCode + "','" + lsSubDistrict + "','" + lsDistrict + "','" + lsFax + "','" + lsEmail + "','" + lsTele + "','" + lsWeb + "','" + lsPostCode + "','109')"; MySqlCommand lsComm11 = new MySqlCommand(lsSQL, conn); lsComm11.ExecuteNonQuery(); lsSQL = "Update member Set addressid = '" + liAddressID + "' Where memid = '" + lsMemID + "'"; MySqlCommand lsComm31 = new MySqlCommand(lsSQL, conn); lsComm31.ExecuteNonQuery(); liAddressID++; lsSoi = lsRead["mailsoit"].ToString(); lsSubDistrict = lsRead["mailsubdistrictt"].ToString().Replace("µÓºÅ", ""); lsSubDistrict = lsSubDistrict.Replace("á¢Ç§", ""); lsDistrict = lsRead["maildistrictt"].ToString(); lsEmail = lsRead["mailemail"].ToString(); lsWeb = lsRead["mailweb"].ToString(); lsTele = lsRead["mailtele"].ToString(); lsFax = lsRead["mailfax"].ToString(); lsPostCode = lsRead["mailpostcode"].ToString(); lsLine1 = lsRead["mailno"].ToString() + " " + lsSoi + " " + lsRead["mailroadt"].ToString();// +" " + lsSubDistrict + " " + lsDistrict; lsProvCode = lsRead["mailprovcode"].ToString(); lsSQL = "Delete From address Where refid = '" + lsMemID + "' and addressid = " + liAddressID; MySqlCommand lsComm12 = new MySqlCommand(lsSQL, conn); lsComm12.ExecuteNonQuery(); lsSQL = "Insert Into address (refid, addressid, addressname, line1, " + "provcode, subdistrictcode, districtcode, " + "fax, email, telephone, website, postcode, addresscode) " + "Values('" + lsMemID + "'," + liAddressID + ",'·ÕèÍÂÙèÊÓËÃѺÊè§àÍ¡ÊÒÃÀÒÉÒä·Â','" + lsLine1 + "','" + lsProvCode + "','" + lsSubDistrict + "','" + lsDistrict + "','" + lsFax + "','" + lsEmail + "','" + lsTele + "','" + lsWeb + "','" + lsPostCode + "','110')"; MySqlCommand lsComm13 = new MySqlCommand(lsSQL, conn); lsComm13.ExecuteNonQuery(); SL1.Text = i.ToString() + " " + Pb1.Maximum.ToString(); Pb1.Value = i; Application.DoEvents(); } lsRead.Close(); lsSQL = "update address a, subdistrict s set a.subdistrictcode = s.subdistrictcode where a.subdistrictcode = s.subdistrictnamet"; MySqlCommand lsComm14 = new MySqlCommand(lsSQL, conn); lsComm14.ExecuteNonQuery(); lsSQL = "update address a, district d set a.districtcode = d.districtcode where a.districtcode = d.districtnamet"; MySqlCommand lsComm15 = new MySqlCommand(lsSQL, conn); lsComm15.ExecuteNonQuery(); acc.Close(); conn.Close(); Pb1.Visible = false; SL1.Visible = false; }
public Boolean UpdateMeetingContactWithoutSelect(Int32 aMeeting, ToolStripProgressBar aPb1, MySqlConnection Conn) { Boolean lbReturn = false; string lsSQL = "", lsContactName, lsCompanyName = "", lsTypeMeeting = "", lsPositionName = "", lsRemark = ""; Int32 liMax = 0, liMeetingContactID = 0, liCnt = 0, li = 0, liLine = 5; MySqlDataReader lsRead, lsRead1; aPb1.Visible = true; string lsCharSet = lsIni.GetString("thahr30", "charset", "tis620"); 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"); MySqlConnection conn1 = new MySqlConnection("Data Source=" + lsServer + ";Database=" + lsDatabase + ";User ID=" + lsUserName + ";Password="******";CHARSET=" + lsCharSet + ""); conn1.Open(); lsSQL = "Delete From meetingcontact Where remark = 'default'"; MySqlCommand lsComm = new MySqlCommand(lsSQL, Conn); MySqlCommand lsComm1 = new MySqlCommand(lsSQL, conn1); lsComm.ExecuteNonQuery(); lsSQL = "select max(meetingcontactid) as cnt From meetingcontact "; lsComm.CommandText = lsSQL; lsRead = lsComm.ExecuteReader(); while (lsRead.Read()) { if (lsRead["cnt"].ToString() == "") { liMax = 0; } else { liMax = Convert.ToInt32(lsRead["cnt"].ToString()); } } lsRead.Close(); liMax++; liMeetingContactID = liMax; lsSQL = "Select count(*) cnt From member Where flag <> '3' "; lsComm.CommandText = lsSQL; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { aPb1.Maximum = Convert.ToInt32(lsRead["cnt"]); } } lsRead.Close(); lsSQL = "select memnamee1 from member Where flag <> '3' Order By memnamee1"; lsComm.CommandText = lsSQL; lsRead = lsComm.ExecuteReader(); if (lsRead.HasRows) { while (lsRead.Read()) { lsCompanyName = lsRead["memnamee1"].ToString(); lsCompanyName = lsCompanyName.Replace("'", "''"); lsSQL = "Select count(*) as cnt from meetingcontact Where meetingid = '" + aMeeting + "' and companyname = '" + lsCompanyName + "'"; lsComm1.CommandText = lsSQL; lsRead1 = lsComm1.ExecuteReader(); liCnt = 0; if (lsRead1.HasRows) { while (lsRead1.Read()) { liCnt = Convert.ToInt32(lsRead1["cnt"].ToString()); } } lsRead1.Close(); for (Int32 i = 0; i <= (liLine - liCnt); i++) { lsSQL = "Insert Into meetingcontact(meetingid, meetingcontactid, contactname, companyname, " + "typemeeting, positionname, remark) " + "Values('" + aMeeting + "', '" + liMeetingContactID + "', '', '" + lsCompanyName + "','','','default')"; lsComm1.CommandText = lsSQL; lsComm1.ExecuteNonQuery(); liMeetingContactID++; } li++; aPb1.Value = li; } } lsRead.Close(); aPb1.Visible = false; return(lbReturn); }
public Boolean CreateTextProductFile(string aFlag, MySqlDataReader aRead) { if (aFlag == "master") { MasterKingPower lsMaster = new MasterKingPower(); if (lsGdb.Gdb.State == System.Data.ConnectionState.Closed) { lsGdb.ConnectDatabase(); } if (lsIniT.TblMember.Count == 0) { lsIniT.CreateTblMember(lsGdb.Gdb); } if (lsIniT.TblNationality.Count == 0) { lsIniT.CreateTblNationality(lsGdb.Gdb); } if (lsIniT.TblCounter.Count == 0) { lsIniT.CreateTblCounter(lsGdb.Gdb); } if (lsIniT.TblShift.Count == 0) { lsIniT.CreateTblShift(lsGdb.Gdb); } if (lsIniT.TblTypeMem.Count == 0) { lsIniT.CreateTblTypeMem(lsGdb.Gdb); } if (lsIniT.TblTypeRoom.Count == 0) { lsIniT.CreateTblTypeRoom(lsGdb.Gdb); } //lsIniT.CreateTblInitial(lsGdb.Gdb); lsMaster.ShopCode = lsIni.GetString("kingpower", "shopcode", "0"); string lsFileName = "", lsPath = "D://thahr30//"; lsFileName = lsMaster.ShopCode + "_Product_" + 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"); lsPath = lsIni.GetString("kingpower", "pathtextfilekingpower", "D://thahr30//text//"); lsTextFileName = lsPath + lsFileName + ".Txt"; StreamWriter lsSW = new StreamWriter(lsPath + lsFileName + ".Txt"); //KingPower lsGen = new KingPower(); string[] lsData = new string[2000]; lsData = GenMasterFileKingPower(aRead); //if (lsData[1] != "") //{ // MessageBox.Show("àµÃÕÂÁ¢éÍÁÙÅ·Ñé§ËÁ´ àÃÕºÃéÍÂ", "Prepare Data All", MessageBoxButtons.OK); //} lsSW.WriteLine("[Product]"); foreach (string lsD in lsData) { if (lsD != null) { lsSW.WriteLine(lsD); } } //lsSW.WriteLine(lsData); lsSW.Close(); } return(true); }