public void SeparateTable(TableIniT aTable, string aPrimaryKey, string aWhereSelect, ToolStripProgressBar aPB1, MySqlConnection aConnHost, MySqlConnection aConnDesc)
{
string lsColumnName = "", lsData = "", lsDataType = "", lsInsertInto = "", lsDataInto = "", lsSQL = "", lsPrimaryKeyData = "", lsExeCData = "", lsPrimaryKeyData2 = "";
Int32 liCount = 0;
if (aWhereSelect == "")
{
lsSQL = "Select count(*) as cnt From " + aTable.ToString().ToLower();
}
else
{
lsSQL = "Select count(*) as cnt From " + aTable.ToString().ToLower() + " Where " + aWhereSelect;
}
MySqlCommand lsCommData = new MySqlCommand(lsSQL, aConnHost);
MySqlDataReader lsReadData;
lsReadData = lsCommData.ExecuteReader();
if (lsReadData.HasRows)
{
while (lsReadData.Read())
{
liCount = Convert.ToInt32(lsReadData["cnt"]);
}
}
lsReadData.Close();
aPB1.Minimum = 0;
aPB1.Maximum = liCount;
aPB1.Visible = true;
if (aWhereSelect == "")
{
lsSQL = "Select * From " + aTable.ToString().ToLower();
}
else
{
lsSQL = "Select * From " + aTable.ToString().ToLower() + " Where " + aWhereSelect;
}
MySqlCommand lsCommExeC = new MySqlCommand();
lsCommExeC.Connection = aConnDesc;
lsCommData.CommandText = lsSQL;
lsReadData = lsCommData.ExecuteReader();
if (lsReadData.HasRows)
{
Int32 i = 0, j = 0;
while (lsReadData.Read())
{
try
{
lsInsertInto = "Insert Into " + aTable.ToString().ToLower() + "(";
lsDataInto = " Values (";
for (Int32 k = 0; k <= lsReadData.FieldCount - 1; k++)
{
lsColumnName = lsReadData.GetName(k).ToString().ToLower();
lsInsertInto = lsInsertInto + " " + lsColumnName + ",";
lsDataType = lsReadData.GetFieldType(k).ToString().ToLower();
lsData = lsReadData[lsColumnName].ToString();
if (lsColumnName == aPrimaryKey)
{
lsPrimaryKeyData = lsData;
}
switch (lsDataType)
{
case "system.string":
{
lsData = lsData.Replace("'", "''");
lsDataInto = lsDataInto + " '" + lsData + "',";
break;
}
case "system.datetime":
{
if (lsData == "")
{
lsData = "null";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " '" + lsData + "',";
}
break;
}
case "system.double":
{
if (lsData == "")
{
lsData = "0";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " " + lsData + ",";
}
break;
}
case "system.decimal":
{
if (lsData == "")
{
lsData = "0";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " " + lsData + ",";
}
break;
}
case "system.int16":
{
if (lsData == "")
{
lsData = "0";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " " + lsData + ",";
}
break;
}
case "system.int32":
{
if (lsData == "")
{
lsData = "0";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " " + lsData + ",";
}
break;
}
case "system.uint32":
{
if (lsData == "")
{
lsData = "0";
lsDataInto = lsDataInto + " " + lsData + ",";
}
else
{
lsDataInto = lsDataInto + " " + lsData + ",";
}
break;
}
default:
{
lsDataInto = lsDataInto + " " + lsData + ",";
break;
}
}
}
lsInsertInto = lsInsertInto.Substring(0, lsInsertInto.Length - 1) + ") ";
lsDataInto = lsDataInto.Substring(0, lsDataInto.Length - 1) + ") ";
lsExeCData = lsInsertInto + lsDataInto;
lsSQL = "Delete From " + aTable.ToString().ToLower() + " Where " + aPrimaryKey + "='" + lsPrimaryKeyData + "'";
lsCommExeC.CommandText = lsSQL;
lsCommExeC.ExecuteNonQuery();
lsCommExeC.CommandText = lsExeCData;
lsCommExeC.ExecuteNonQuery();
i++;
aPB1.Value = i;
}
catch (Exception e)
{
string ls = "ไม่สามารถ SeparateTable ได้ ";
WriteLogError(ls, e, "", "SeparateTable");
MessageBox.Show(ls + " " + e.Message.ToString(), e.Source.ToString());
}
}
}
aPB1.Visible = false;
lsReadData.Close();
}
public ComboBox SelectCbo(ComboBox Cbo, string lsWhereCode, TableIniT aFlag)
{
string lsSQL = "", ls = "";
if (aFlag == TableIniT.YearName)
{
Int32 i = 2546;
ArrayList lsList = new ArrayList();
for (i = 2546; i <= System.DateTime.Now.Year + 543; i++)
{
lsList.Add(new CboState(i.ToString(), i.ToString()));
}
Cbo.DataSource = lsList;
Cbo.DisplayMember = "LongName";
Cbo.ValueMember = "ShortName";
Cbo.Text = "";
}
else if (Cbo.Name.ToString() == TableIniT.CboLinkPicture.ToString())
{
ArrayList lsList1 = new ArrayList();
lsSQL = "Select id, name From cboselect Where cboname = '" + Cbo.Name + "' Order by name";
MySqlCommand lsComm1 = new MySqlCommand(lsSQL, Gdb);
MySqlDataReader lsReader1;
lsReader1 = lsComm1.ExecuteReader();
if (lsReader1.HasRows)
{
while (lsReader1.Read())
{
//Cbo.Items.Add(lsReader.GetValue(1).ToString());
lsList1.Add(new CboState(lsReader1.GetValue(0).ToString().Trim(), lsReader1.GetValue(1).ToString().Trim()));
//lsSQL = lsReader.GetValue(1).ToString();
}
}
lsReader1.Close();
lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m "
+ "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee";
MySqlCommand lsComm2 = new MySqlCommand(lsSQL, Gdb);
MySqlDataReader lsReader2;
lsReader2 = lsComm2.ExecuteReader();
if (lsReader2.HasRows)
{
while (lsReader2.Read())
{
//Cbo.Items.Add(lsReader.GetValue(1).ToString());
lsList1.Add(new CboState(lsReader2.GetValue(0).ToString().Trim(), lsReader2.GetValue(1).ToString().Trim()));
//lsSQL = lsReader.GetValue(1).ToString();
}
}
if (aFlag == TableIniT.TypeRoom)
{
lsList1.Add(new CboState("-", "-"));
}
Cbo.DataSource = lsList1;
Cbo.DisplayMember = "LongName";
Cbo.ValueMember = "ShortName";
Cbo.Text = "";
lsReader2.Close();
}
else
{
string lsWhere = "";
switch (aFlag)
{
case TableIniT.Nationality:
lsSQL = "Select nationcode, nationname From nationality Order By nationname";
break;
case TableIniT.SubDistrict:
lsSQL = "Select s.subdistrictcode, concat(s.subdistrictnamet, ' / ', d.districtnamet, ' / ' , p.provnamet) From subdistrict s, district d, province1 p Where s.subdistrictnamet like '" + lsWhereCode + "%' and s.districtcode = d.districtcode and s.provcode = p.provcode order by p.provnamet, d.districtnamet";
break;
case TableIniT.CboDistrictFromProvCode:
lsSQL = "Select districtcode, districtnamet From district Where provcode = '" + lsWhereCode + "'";
break;
case TableIniT.CboDistrictFromSubDistrict:
lsSQL = "Select d.districtcode, d.districtnamet From district d Where districtcode = (Select districtcode From subdistrict Where subdistrictcode = '" + lsWhereCode + "') ";
break;
case TableIniT.District:
{
lsSQL = "Select districtcode, districtnamet From district Where provcode = '" + lsWhereCode + "' Order by districtnamet";
break;
}
case TableIniT.CboSubDistrictFromDistrict:
{
lsSQL = "Select subdistrictcode, subdistrictnamet From subdistrict Where districtcode = '" + lsWhereCode + "' Order by subdistrictnamet";
break;
}
case TableIniT.Province:
{
lsSQL = "Select provcode, provname From province Order by provname";
break;
}
case TableIniT.Member:
if (lsWhereCode == "")
{
lsWhere = "1 = 1";
}
else
{
lsWhere = " provcode = '" + lsWhereCode + "'";
}
lsSQL = "Select memid, memnamee1 From member Where " + lsWhere + " and flag = '1' and flagsale = '1' Order by memnamee1";
break;
case TableIniT.MemberFromRegCode:
lsSQL = "Select regisid, nameeng From regis Where regcode = '" + lsWhereCode + "' Order by nameeng";
break;
case TableIniT.Shift:
lsSQL = "Select shiftcode, shiftname From shift Order by shiftcode";
break;
case TableIniT.TypeRoom:
if (lsWhereCode == "")
{
//lsSQL = "Select plcode, plnamee From typeroom Order by plnamee";
lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m "
+ "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee";
}
else
{
lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m "
+ "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee";
}
break;
case TableIniT.Region:
lsSQL = "Select regioncode, regionname From region Order by sort1";
break;
case TableIniT.Staff:
lsSQL = "Select staffcode, staffname From staff Order by staffname";
break;
case TableIniT.Status:
lsSQL = "Select statuscode, statusname From status Where flag = '1' Order by statusname";
break;
case TableIniT.HotelChain:
lsSQL = "Select hotelchaincode, hotelchainname From hotelchain Order by hotelchainname";
break;
case TableIniT.TypeMem:
lsSQL = "Select tmemcode, tmemname From typemem Order by tmemname";
break;
case TableIniT.TypeBis:
lsSQL = "Select tbiscode, tbisname From typebis Order by tbisname";
break;
case TableIniT.GreenLeft:
lsSQL = "Select greencode, greenname From greenleft Order by greenname";
break;
case TableIniT.Location:
lsSQL = "Select locationcode, locationnamet From location Order by locationnamet";
break;
case TableIniT.MemberOwner:
lsSQL = "Select distinct ownerid, ownernamet From memberowner Order by ownernamet";
break;
case TableIniT.Star:
lsSQL = "Select starcode, starname From star Order by starname";
break;
case TableIniT.CboVoucherView:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboPrefix:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboMemViewFilter:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboShopView:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboTSend:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboLinkPicture:
lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name";
break;
case TableIniT.CboTypePayment:
lsSQL = "Select tpaycode, tpaynamee From typepayment Order by tpaynamee";
break;
case TableIniT.CboAddress:
lsSQL = "Select id, name From cboselect Where cboname = 'cboaddress' Order by name";
break;
case TableIniT.MonthName:
lsSQL = "Select monthid, monthnamee From monthname ";
break;
case TableIniT.MemberStatus:
lsSQL = "Select id, name From cboselect Where cboname = 'cbomemberstatus' Order by id";
break;
case TableIniT.Counter:
if (lsWhereCode == "ip")
{
lsSQL = "Select ip, countername From counter Where flag = '1' Order By counterid";
}
else if (lsWhereCode == "report")
{
lsSQL = "Select ip, countername From counter Order By counterid";
}
else
{
lsSQL = "Select counterid, countername From counter Where flag = '1' Order By counterid";
}
break;
}
//lsSQL = "Select nationcode, nationname From nationality ";
MySqlCommand lsComm = new MySqlCommand(lsSQL, Gdb);
MySqlDataReader lsReader;
lsReader = lsComm.ExecuteReader();
if (lsReader.HasRows)
{
ArrayList lsList = new ArrayList();
if (aFlag == TableIniT.TypeRoom)
{
lsList.Add(new CboState("-", "-"));
}
else if (aFlag == TableIniT.CboDistrictFromSubDistrict)
{
lsList.Add(new CboState("-", "-"));
}
while (lsReader.Read())
{
//Cbo.Items.Add(lsReader.GetValue(1).ToString());
ls = lsReader.GetValue(1).ToString().Trim();
lsList.Add(new CboState(lsReader.GetValue(0).ToString().Trim(), lsReader.GetValue(1).ToString().Trim()));
//lsSQL = lsReader.GetValue(1).ToString();
}
Cbo.DataSource = lsList;
Cbo.DisplayMember = "LongName";
Cbo.ValueMember = "ShortName";
Cbo.Text = "";
}
lsReader.Close();
switch (aFlag)
{
case TableIniT.CboDistrictFromSubDistrict:
{
//lsSQL = Cbo.SelectedValue.ToString();
//ls = Cbo.SelectedItem.ToString();
Cbo.SelectedValue = ls;
//Cbo.SelectedItem = ls;
//lsSQL = Cbo.SelectedText;
break;
}
}
}
return(Cbo);
}
public string SelectCboProvDistrSubDistr(ComboBox CboProv, ComboBox CboDistr, ComboBox CboSubDistr, TextBox TxtPostCode, string lsWhereCode, TableIniT aFlag)
{
Boolean lbFlagSubDistr = false;
string lsSQL = "", ls = "", lsReturn = "", lsProv = "", lsSubDistr = "";
switch (aFlag)
{
case TableIniT.SubDistrict:
lsSQL = "Select s.subdistrictcode, concat(s.subdistrictnamet, ' / ', d.districtnamet, ' / ' , p.provnamet) From subdistrict s, district d, province p Where s.subdistrictnamet like '" + lsWhereCode + "%' and s.districtcode = d.districtcode and s.provcode = p.provcode order by p.provnamet, d.districtnamet";
break;
case TableIniT.CboDistrictFromSubDistrict:
lsSQL = "Select d.districtcode, d.districtnamet, p.provcode, p.provnamet, pc.postcode, s.subdistrictcode, s.subdistrictnamet, p.provnamee, d.districtnamee, s.subdistrictnamee From district d, province p, subdistrict s , postcode pc Where d.districtcode = (Select districtcode From subdistrict Where subdistrictcode = '" + lsWhereCode + "') and d.provcode = p.provcode and pc.subdistrictcode = s.subdistrictcode and s.districtcode = d.districtcode and s.subdistrictcode = '" + lsWhereCode + "'";
break;
}
MySqlCommand lsComm = new MySqlCommand(lsSQL, Gdb);
MySqlDataReader lsReader;
lsReader = lsComm.ExecuteReader();
if (lsReader.HasRows)
{
ArrayList lsListSubDistr = new ArrayList();
ArrayList lsListDistr = new ArrayList();
ArrayList lsListProv = new ArrayList();
if (aFlag == TableIniT.TypeRoom)
{
lsListDistr.Add(new CboState("-", "-"));
}
else if (aFlag == TableIniT.CboDistrictFromSubDistrict)
{
lsListSubDistr.Add(new CboState("-", "-"));
lsListDistr.Add(new CboState("-", "-"));
lsListProv.Add(new CboState("-", "-"));
}
while (lsReader.Read())
{
ls = lsReader.GetValue(0).ToString().Trim();
lsProv = lsReader.GetValue(2).ToString().Trim();
lsSubDistr = lsReader.GetValue(4).ToString().Trim();
lsListSubDistr.Add(new CboState(lsReader.GetValue(5).ToString().Trim(), lsReader.GetValue(6).ToString().Trim()));
lsListDistr.Add(new CboState(lsReader.GetValue(0).ToString().Trim(), lsReader.GetValue(1).ToString().Trim()));
lsListProv.Add(new CboState(lsReader.GetValue(2).ToString().Trim(), lsReader.GetValue(3).ToString().Trim()));
TxtPostCode.Text = lsReader.GetValue(4).ToString().Trim();
lsSubDistrictNameE = lsReader["subdistrictnamee"].ToString();
lsDistrictNameE = lsReader["districtnamee"].ToString();
lsProvNameE = lsReader["provnamee"].ToString();
}
if (CboSubDistr.DataSource == null)
{
CboSubDistr.DataSource = lsListSubDistr;
CboSubDistr.DisplayMember = "LongName";
CboSubDistr.ValueMember = "ShortName";
CboSubDistr.Text = "";
lbFlagSubDistr = true;
}
CboDistr.DataSource = lsListDistr;
CboDistr.DisplayMember = "LongName";
CboDistr.ValueMember = "ShortName";
CboDistr.Text = "";
CboProv.DataSource = lsListProv;
CboProv.DisplayMember = "LongName";
CboProv.ValueMember = "ShortName";
CboProv.Text = "";
}
lsReader.Close();
switch (aFlag)
{
case TableIniT.CboDistrictFromSubDistrict:
{
CboDistr.SelectedValue = ls;
CboProv.SelectedValue = lsProv;
if (lbFlagSubDistr == true)
{
CboSubDistr.SelectedValue = lsSubDistr;
}
break;
}
}
return(lsReturn);
}
