/// <summary>
/// A method for queries which give an answer or a result.
/// Like Select. With this method it's also possible to define the columns which are needed for the given table.
/// For Each row the needed column is seperated with '|'.
/// At the moment only string and int64 values are implemented. Every other type is ignored.
/// </summary>
/// <param name="query">The select query.</param>
/// <param name="columns">A list of pairs witch asks for the the given column and type of column in the order of the list.</param>
/// <returns></returns>
public override List <List <object> > ReadQuery(string query, List <KeyValuePair <int, Type> > columns)
{
try
{
using (OleDbConnection oleDbConnection = new OleDbConnection(_connectionString))
{
using (var cmd = new OleDbCommand(query, oleDbConnection))
{
cmd.Connection = oleDbConnection;
oleDbConnection.Open();
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
List <List <object> > result = new List <List <object> >();
while (rdr.Read())
{
List <object> row = new List <object>();
foreach (KeyValuePair <int, Type> column in columns)
{
if (column.Value == typeof(int))
{
row.Add(rdr.GetInt32(column.Key));
}
else if (column.Value == typeof(string))
{
row.Add(rdr.GetString(column.Key));
}
else if (column.Value == typeof(double))
{
row.Add(rdr.GetFloat(column.Key));
}
else if (column.Value == typeof(float))
{
row.Add(rdr.GetFloat(column.Key));
}
else if (column.Value == typeof(DateTime))
{
row.Add(rdr.GetDateTime(column.Key));
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
}
result.Add(row);
}
rdr.Close();
oleDbConnection.Close();
return(result);
}
}
}
}
catch (Exception e)
{
throw e;
}
}
// function for load column data
private void getEditClmData()
{
try
{
/* TbClms = " Clm_No,Clm_Name,Clm_SX,Clm_SY,Clm_Wi,Clm_Hi,Clm_Nm_X,Clm_Nm_Y,Clm_Hight,Clm_Breadth,Clm_Length,";
* TbClms = TbClms + "Clm_Pit_Depth,Clm_Pit_Breadth,Clm_Pit_Length,Clm_Pit_Mat_Thick,Clm_Sand_Rat,Clm_Stone_Rat,";
* TbClms = TbClms + "Clm_Cement_Rat,Clm_NoRod ";*/
int ftv = 0, incv = 0;
float FtinV = 0;
string Qry = "select * from Window_Tab where Win_Name ='" + HomeFrm.EdtCntrl + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
WinName = Dr.GetString(1);
txtWinLeft.Text = Dr.GetInt16(2).ToString();
txtWinTop.Text = Dr.GetInt16(3).ToString();
txtNameLeft.Text = Dr.GetInt16(6).ToString();
txtNameTop.Text = Dr.GetInt16(7).ToString();
FtinV = Dr.GetFloat(8);
FtToIn(FtinV, out ftv, out incv);
txtWinHeightFt.Text = ftv.ToString();
txtWinHeightIn.Text = incv.ToString();
FtinV = Dr.GetFloat(9);
FtToIn(FtinV, out ftv, out incv);
txtWinLengthFt.Text = ftv.ToString();
txtWinLengthIn.Text = incv.ToString();
cmbWallName.Text = Dr.GetString(10);
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Can\'t load all edit column data\n " + ex.Message, "Error", MessageBoxButtons.OK);
}
}
private void getBeamsList()
{
try
{
BmsHit = new Dictionary <string, float>();
string Qry = "select Bm_Name,Bm_Hight from Beam_Tab;";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
cmbBms.Items.Add(Dr.GetString(0));
BmsHit.Add(Dr.GetString(0), Dr.GetFloat(1));
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
}
}
public static List <Student> getList()
{
string query = "SELECT [name1], [name2], [age], [ball], [nameGroup] FROM (students INNER JOIN groups ON students.idgroup = groups.id)";
OleDbConnection myConn = new OleDbConnection(stringConnect);
OleDbCommand comm = new OleDbCommand(query, myConn);
myConn.Open();
OleDbDataReader reader = comm.ExecuteReader();
List <Student> list = new List <Student>();
while (reader.Read())
{
list.Add(new Student
{
Name1 = reader.GetString(0),
Name2 = reader.GetString(1),
Age = reader.GetInt32(2),
Ball = reader.GetFloat(3),
Group = reader.GetString(4)
});
}
reader.Close();
myConn.Close();
return(list);
}
public static List <Student> getList(string tablename, double avgBall)
{
// способ 1 – через конкатенацию строк
// string query = "SELECT * FROM " + tablename;
// query += " WHERE ball>=" + avgBall.ToString() + " ORDER BY ball DESC";
// способ 2 – через форматирование строки запроса
string query =
String.Format("SELECT * FROM {0} WHERE ball>={1} ORDER BY ball DESC", tablename, avgBall);
OleDbConnection myConn = new OleDbConnection(stringConnect);
myConn.Open();
OleDbCommand comm = new OleDbCommand(query, myConn);
OleDbDataReader reader = comm.ExecuteReader();
List <Student> list = new List <Student>();
while (reader.Read())
{
list.Add(new Student
{
Id = reader.GetInt32(0),
Name1 = reader.GetString(1),
Name2 = reader.GetString(2),
Age = reader.GetInt32(3),
Ball = reader.GetFloat(4)
});
}
reader.Close();
myConn.Close();
return(list);
}
public static List <Student> getList(string tablename)
{
string query = "SELECT * FROM " + tablename;
OleDbConnection myConn = new OleDbConnection(stringConnect);
OleDbCommand comm = new OleDbCommand(query, myConn);
myConn.Open();
OleDbDataReader reader = comm.ExecuteReader();
List <Student> list = new List <Student>();
while (reader.Read())
{
list.Add(new Student
{
Id = reader.GetInt32(0),
Name1 = reader.GetString(1),
Name2 = reader.GetString(2),
Age = reader.GetInt32(3),
Ball = reader.GetFloat(4)
});
}
reader.Close();
myConn.Close();
return(list);
}
// get the total beams thickness related to wall
private float getWlBmThick()
{
float tk = 0;
try
{
string Qry = "select * from Wall_Beam where WL_Name='" + cmbWallName.Text + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
tk = tk + Dr.GetFloat(2);
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception)
{
return(0);
}
return(tk);
}
public double[][] QueryLeiJiXuQian(string sSQL, string sIICFilePath)
{
double[][] dReturnValue = new double[2][];
using (OleDbConnection sqlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sIICFilePath + ";Persist Security Info=True"))
{
List <double> l = new List <double>();
OleDbCommand sqlcom = new OleDbCommand(sSQL, sqlconn);
sqlconn.Open();
OleDbDataReader sdr = sqlcom.ExecuteReader();
while (sdr.Read())
{
l.Add(sdr.GetFloat(0));
}
sdr.Close();
sqlconn.Close();
dReturnValue[0] = new double[l.Count];
dReturnValue[1] = new double[l.Count];
dReturnValue[0] = l.ToArray();
for (int i = 0; i < l.Count; i++)
{
dReturnValue[1][i] = double.Parse((((double)(i + 1) / l.Count) * 100).ToString("F02"));
}
}
return(dReturnValue);
}
// методы
public List <Student> getList()
{
List <Student> list = new List <Student>();
string query = "SELECT * FROM students";
using (OleDbConnection myConn = new OleDbConnection(stringConnect))
{
myConn.Open();
using (OleDbCommand comm = new OleDbCommand(query, myConn))
{
OleDbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
list.Add(new Student
{
id = reader.GetInt32(0),
name1 = reader.GetString(1),
name2 = reader.GetString(2),
age = reader.GetInt32(3),
ball = reader.GetFloat(4), // GetFloat - для одинарной точности
idGroup = reader.GetInt32(5)
});
}
}
}
return(list);
}
Flow BuildFlow(OleDbDataReader reader, TimeSpan duration)
{
int i = 0;
DateTime startTime = reader.GetDateTime(i++);
Double rate = reader.GetFloat(i++);
return(new Flow(new TimeFrame(startTime, duration), rate));
}
public static float GetFloatSafe(this OleDbDataReader reader, int index)
{
if (!reader.IsDBNull(index))
{
return(reader.GetFloat(index));
}
return(0);
}
// geting the Windows related wall data
private void getWallData()
{
try
{
string Cmm = "Wl_Hight,Wl_Breadth,Wl_Length,Wl_Sand_Rat,Wl_Cement_Rat,Wl_Plstr,Wl_Pls_Length,Wl_Pls_Sand_Rat,";
Cmm = Cmm + "Wl_Pls_Cement_Rat,Wl_Plstr_Side";
string Qry = "select " + Cmm + " from Wall_Tab where Wl_Name ='" + cmbWallName.Text + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
WL_H = Dr.GetFloat(0);
WL_B = Dr.GetFloat(1);
WL_L = Dr.GetFloat(2);
WL_SR = Dr.GetFloat(3);
WL_CR = Dr.GetFloat(4);
WL_PL = Dr.GetBoolean(5);
if (WL_PL == true)
{
WL_PT = Dr.GetFloat(6);
WL_PSR = Dr.GetFloat(7);
WL_PCR = Dr.GetFloat(8);
WL_PSD = Dr.GetString(9);
}
else
{
WL_PT = 0;
WL_PSR = 0;
WL_PCR = 0;
WL_PSD = "One";
}
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Can\'t load all edit wall data.\n" + ex.Message, "Error", MessageBoxButtons.OK);
}
}
public float GetFloat(int i)
{
if (SDR != null)
{
return(SDR.GetFloat(i));
}
else
{
return(ODR.GetFloat(i));
}
}
// for load data of Editing Beam
private void getEditBeamData()
{
try
{
int ftv = 0, incv = 0;
float FtinV = 0;
string Qry = "select * from Beam_Tab where Bm_Name ='" + HomeFrm.EdtCntrl + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
BmName = Dr.GetString(1);
txtStartX.Text = Dr.GetInt16(2).ToString();
txtStartY.Text = Dr.GetInt16(3).ToString();
txtEndX.Text = Dr.GetInt16(4).ToString();
txtEndY.Text = Dr.GetInt16(5).ToString();
txtNmX.Text = Dr.GetInt16(6).ToString();
txtNmY.Text = Dr.GetInt16(7).ToString();
FtinV = Dr.GetFloat(8);
FtToIn(FtinV, out ftv, out incv);
txtBmHeightFt.Text = ftv.ToString();
txtBmHeightIn.Text = incv.ToString();
FtinV = Dr.GetFloat(9);
FtToIn(FtinV, out ftv, out incv);
txtBmBreadthFt.Text = ftv.ToString();
txtBmBreadthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(10);
FtToIn(FtinV, out ftv, out incv);
txtBmLengthFt.Text = ftv.ToString();
txtBmLengthIn.Text = incv.ToString();
txtSand.Text = Dr.GetFloat(11).ToString();
txtStone.Text = Dr.GetFloat(12).ToString();
txtCement.Text = Dr.GetFloat(13).ToString();
txtRods.Text = Dr.GetInt16(14).ToString();
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Can\'t load all edit Beam Data.\n" + ex.Message, "Error", MessageBoxButtons.OK);
}
}
// geting the total volume of Windows
private float getWinDrVol()
{
float vl = 0;
float WDH, WDL;
try
{
string Qry = "select Win_Height,Win_Length from Window_Tab where Win_WallName='" + cmbWallName.Text + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
WDH = Dr.GetFloat(0);
WDL = Dr.GetFloat(1);
vl = vl + (WL_B * WDH * WDL);
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception)
{
return(0);
}
return(vl);
}
public void WczytajTransakcjeU(Konto konto)
{
conn.Open();
var zapytanie = $"Select * from Transakcje WHERE Użytkownik='{konto.nazwa}'";
OleDbCommand komenda = new OleDbCommand(zapytanie, conn);
OleDbDataReader reader = komenda.ExecuteReader();
transakcje.Clear();
while (reader.Read())
{
Transakcja transakcja = new Transakcja(
reader.GetString(0), // paragon
reader.GetString(1), // uzytkownik
reader.GetFloat(2) // kwota
);
transakcje.Add(transakcja);
}
conn.Close();
}
private void getEditWallBeamDate()
{
try
{
string LBmN;
float bmwid;
string Qry = "select * from Wall_Beam where WL_Name ='" + HomeFrm.EdtCntrl + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
LBmN = Dr.GetString(0);
bmwid = Dr.GetFloat(2);
if (cmbBms.Items.Contains(LBmN))
{
lstBms.Items.Add(LBmN);
TotSlBmHt = TotSlBmHt + bmwid;
}
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception)
{
;
}
}
protected void viewProdInfo()
{
string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="
+ Server.MapPath("~/ebookstoredb.mdb");
using (OleDbConnection con = new OleDbConnection(conString))
{
con.Open();
string query = "SELECT * FROM product where ID=@id";
using (OleDbCommand cmd = new OleDbCommand(query, con))
{
cmd.Parameters.AddWithValue("@id", Request.QueryString["book_id"]);
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
id = rdr.GetInt32(0);
BookNameL.Text = rdr.GetString(1);
BookDescriptionL.Text = rdr.GetString(2);
BookPriceL.Text = rdr.GetFloat(3).ToString();
}
con.Close();
}
}
public List <WorkData> GetWorkData(DateTime date, out List <Auto> autok, out CimOsszesito osszCim)
{
DateTime tempDate;
object realm3;
float rm3;
string auth = "";
string host;
string tmpRsz;
string tmpRow;
List <WorkData> data = new List <WorkData>();
WorkData tempData = null;
Dictionary <string, Auto> autoData = new Dictionary <string, Auto>();
autok = new List <Auto>();
osszCim = new CimOsszesito();
OleDbDataReader latReader = null;
OleDbDataReader kapReader = null;
OleDbTransaction trans = null;
Auto fordAuto;
int forduloszam;
int kapacitas;
int counter = 0;
if (connection == null)
{
GetConnection();
}
try
{
Auto.AutoIndex = 0;
System.Net.ServicePointManager.CertificatePolicy = new MyPolicy();
//if (Properties.Settings.Default.userName != "" && Properties.Settings.Default.password != "")
{
auth = string.Format("{0}:{1}", Properties.Settings.Default.userName, Properties.Settings.Default.password);
}
host = string.Format("{0}{1}&ok=OK", Properties.Settings.Default.updateURL, date.ToString("yyyy-MM"));
try
{
using (WebClient client = new WebClient(), cl2 = new WebClient())
{
cl2.Headers.Set("Authorization", "Basic " + Convert.ToBase64String(Encoding.UTF8.GetBytes(auth)));
client.Headers.Set("Authorization", "Basic " + Convert.ToBase64String(Encoding.UTF8.GetBytes(auth)));
using (StreamReader reader = new StreamReader(cl2.OpenRead(new Uri(string.Format("https://213.178.99.161/new/letoltes.php?honap={0}&ok=OK", date.ToString("yyyy-MM"))))))
{
}
using (Stream dataStream = client.OpenRead(host))
{
using (StreamReader reader = new StreamReader(dataStream, Encoding.UTF7))
{
while (!reader.EndOfStream)
{
tmpRow = reader.ReadLine().Replace('õ', 'ő').Replace('û', 'ű').Replace('Õ', 'Ő').Replace('Û', 'Ű');
string[] parts = tmpRow.Split('\t');
if (parts.Length == 1)
{
if (parts[0].Trim() == "")
{
continue;
}
if (tempData != null)
{
tempData.Megjegyzes += " " + parts[0];
}
}
if (parts.Length != 14)
{
continue;
}
try
{
tempDate = DateTime.Parse(parts[4]);
}
catch (FormatException)
{
continue;
}
if (tempDate.Date > date.Date)
{
break;
}
if (tempDate.Date == date.Date)
{
tempData = new WorkData();
tempData.WorksheetNumber = long.Parse(parts[0]);
tempData.Number = counter;
tmpRsz = parts[1];
if (tmpRsz == "")
{
tmpRsz = "???";
}
try
{
kapacitas = int.Parse(parts[8]);
}
catch (Exception)
{
kapacitas = 5;
}
if (autoData.ContainsKey(tmpRsz))
{
fordAuto = autoData[tmpRsz];
}
else
{
fordAuto = new Auto(tmpRsz);
getCar.Parameters[0].Value = tmpRsz;
kapReader = getCar.ExecuteReader();
if (kapReader.Read())
{
fordAuto.Kapacitas = kapReader.GetByte(0);
fordAuto.Fogyasztas = kapReader.GetFloat(1);
fordAuto.Lizingdij = kapReader.GetFloat(2);
}
else
{
fordAuto.Kapacitas = kapacitas;
}
kapReader.Close();
fordAuto.Index = Auto.AutoIndex++;
fordAuto.Sofor = parts[2];
fordAuto.Seged = parts[3];
autoData.Add(tmpRsz, fordAuto);
}
try
{
tempData.IranyitoSzam = int.Parse(parts[5]);
}
catch (FormatException)
{
}
tempData.Utca = parts[6];
tempData.HazSzam = parts[7];
try
{
selectProbUtca.Parameters[0].Value = tempData.Utca;
if (selectProbUtca.ExecuteScalar() != null)
{
tempData.Problematic = true;
}
else
{
selectProbCim.Parameters[0].Value = tempData.Cim;
if (selectProbCim.ExecuteScalar() != null)
{
tempData.Problematic = true;
}
}
}
catch (OleDbException hadit)
{
}
getRealVolume.Parameters[0].Value = tempData.Cim;
realm3 = getRealVolume.ExecuteScalar();
try
{
rm3 = (realm3 == DBNull.Value || realm3 == null) ? 0 : float.Parse(realm3.ToString());
}
catch (FormatException)
{
rm3 = 0;
}
tempData.TenylegesKobmeter = (int)Math.Ceiling(rm3);
tempData.WorkCapacity = kapacitas;
try
{
tempData.Napszak = int.Parse(parts[11]);
}
catch (FormatException)
{
}
if (tempData.Napszak > 3 || tempData.Napszak < 1)
{
using (NapszakCorrector nc = new NapszakCorrector(tempData))
{
nc.ShowDialog();
}
}
try
{
tempData.CsoHossz = int.Parse(parts[9]);
}
catch (FormatException)
{
}
tempData.CsoStr = parts[9];
tempData.Megjegyzes = parts[13];
osszCim.UpdateWith(tempData);
/*if (tempData.Jozsai)
* {
* forduloszam = fordAuto.GetNapszakFordulok(tempData.Napszak - 1);
* fordAuto.SetNapszakFordulok(tempData.Napszak - 1, forduloszam + 1);
* fordAuto.AddJozsaiFuvar(tempData);
* }*/
GetLatLng(tempData, trans);
data.Add(tempData);
counter++;
}
}
}
}
}
foreach (string s in autoData.Keys)
{
autok.Add(autoData[s]);
}
}
catch (WebException se)
{
MessageBox.Show("A program nem tudott kapcsolatba lépni a távoli géppel.\nEllenőrízze az internetkapcsolatot és a Beállítások panelen levő értékeket!",
"Hiba", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
catch (OleDbException ex)
{
MessageBox.Show("Adatbázis elérési hiba.\nKérem ellenőrizze, hogy az adatbázist más alkalmazás nem használja-e!", "Hiba", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
}
catch (Exception e)
{
AppLogger.WriteException(e);
AppLogger.WriteEvent("A kivétel elkapva.");
data.Clear();
osszCim = new CimOsszesito();
if (ExceptionOccured != null)
{
ExceptionOccured();
}
}
finally
{
if (latReader != null)
{
latReader.Close();
}
if (kapReader != null)
{
kapReader.Close();
}
}
return(data);
}
protected void Page_Load(object sender, EventArgs e)
{
paramSexo = "";
paramNome = "";
try {
paramSexo = Request["RadioButtonList1"];
paramNome = Request["TextBox1"];
if (paramSexo == null)
{
paramSexo = "";
}
if (paramNome == null)
{
paramNome = "";
}
}
catch (Exception) {};
Label1.Text = "Registros encontrados:";
String filtro1 = "", filtro2 = "";
if (paramSexo.Equals("Todos"))
{
filtro1 = " TRUE ";
}
else if (paramSexo.Equals("Masculino"))
{
filtro1 = " (sexo='M') ";
}
else if (paramSexo.Equals("Feminino"))
{
filtro1 = " (sexo='F') ";
}
else
{
filtro1 = " FALSE ";
Label1.Text = "";
}
filtro2 = " (nomeprof LIKE '%" + paramNome + "%') ";
try {
conexao = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Escola.mdb");
conexao.Open();
string sql = "select * from professores WHERE "
+ filtro1 + " AND " + filtro2 + " ORDER BY nomeprof";
stm = new OleDbCommand(sql, conexao);
dr = stm.ExecuteReader();
String strHTML = "";
if (dr.HasRows) //mostramos o cabeçalho da <table> somente se temos registros
{
strHTML = "<TABLE name=profs border=1 width='65%'> <TR style='font-size: 14px; font-family: verdana; text-align: center; font-weight: 900; color: #009;'>"
+ "<TD> Código </TD><TD> Nome </TD>"
+ "<TD> Sexo </TD><TD> Salário </TD></TR>";
}
while (dr.Read())
{
int codigo = dr.GetInt16(0); //codprof
String nome = dr.GetString(1); //nome
String sexo = dr.GetString(2); //sexo
float salario = dr.GetFloat(3); //salário
strHTML += "<TR><TD style='font-size: 12px; font-family: verdana; text-align: center;'>" + codigo
+ "</TD><TD style='font-size: 12px; font-family: verdana; text-align: left;'> "
+ nome + "</TD><TD style='font-size: 12px; font-family: verdana; text-align: center;'>"
+ sexo + "</TD><TD style='font-size: 12px; font-family: verdana; text-align: center;'>"
+ salario + "</TD></TR>";
}
strHTML += "</TABLE> <br/><br/><br/>";
LiteralControl lc = new LiteralControl(strHTML);
Panel2.Controls.Add(lc);
stm.Dispose();
dr.Close();
conexao.Close();
} catch (Exception exc) {
Label1.Text = "Erro no processamento do BD - " + exc.Message;
}
}
// get the Material calculation essensial data
private void getCalData()
{
string prop_name;
float prp_val;
try
{
string Qry = "select * from WorkMat;";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
prop_name = Dr.GetString(0);
prp_val = Dr.GetFloat(1);
if (prop_name == "Brick_Brd")
{
Brk_brd = prp_val;
}
else if (prop_name == "Brick_Hi")
{
Brk_thick = prp_val;
}
else if (prop_name == "Brick_Len")
{
Brk_len = prp_val;
}
else if (prop_name == "Cement")
{
CmtVl = prp_val;
}
else if (prop_name == "Clm_Rod_Len")
{
CmRdLen = prp_val;
}
else if (prop_name == "Clm_Rod_Thick")
{
CmRdThick = prp_val;
}
else if (prop_name == "Ring_Rod_Len")
{
RgRdLen = prp_val;
}
else if (prop_name == "Ring_Rod_Thick")
{
RgRdThick = prp_val;
}
else if (prop_name == "Sand")
{
SndVl = prp_val;
}
else if (prop_name == "Stone")
{
StnVl = prp_val;
}
else
{
MessageBox.Show("No proper field to save...", "Information", MessageBoxButtons.OK);
}
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK);
}
}
public List <Auto> GetCars()
{
OleDbTransaction trans = null;
OleDbDataReader kapReader = null;
List <Auto> ret = new List <Auto>();
Auto tmp;
try
{
if (connection == null)
{
GetConnection();
}
trans = connection.BeginTransaction();
allCars.Transaction = trans;
kapReader = allCars.ExecuteReader();
while (kapReader.Read())
{
tmp = new Auto(kapReader.GetString(0));
tmp.Kapacitas = kapReader.GetByte(1);
tmp.Fogyasztas = kapReader.GetFloat(3);
tmp.Lizingdij = kapReader.GetFloat(2);
ret.Add(tmp);
}
trans.Commit();
}
catch (OleDbException ex)
{
MessageBox.Show("Adatbázis elérési hiba.\nKérem ellenőrizze, hogy az adatbázist más alkalmazás nem használja-e!", "Hiba", MessageBoxButtons.OK, MessageBoxIcon.Error);
AppLogger.WriteException(ex);
AppLogger.WriteEvent("A kivétel elkapva.");
trans.Rollback();
}
catch (InvalidOperationException inv)
{
}
catch (Exception e)
{
AppLogger.WriteException(e);
AppLogger.WriteEvent("A kivétel elkapva.");
trans.Rollback();
}
finally
{
if (kapReader != null)
{
kapReader.Close();
}
trans = null;
}
return(ret);
}
// function for load column data
private void getEditClmData()
{
try
{
int ftv = 0, incv = 0;
float FtinV = 0;
string Qry = "select * from Column_Tab where Clm_Name ='" + HomeFrm.EdtCntrl + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
clmName = Dr.GetString(1);
txtClmPosLeft.Text = Dr.GetInt16(2).ToString();
txtClmPosTop.Text = Dr.GetInt16(3).ToString();
txtNamePosLeft.Text = Dr.GetInt16(6).ToString();
txtNamePosTop.Text = Dr.GetInt16(7).ToString();
FtinV = Dr.GetFloat(8);
FtToIn(FtinV, out ftv, out incv);
txtClmHeightFt.Text = ftv.ToString();
txtClmHeightIn.Text = incv.ToString();
FtinV = Dr.GetFloat(9);
FtToIn(FtinV, out ftv, out incv);
txtClmBreadthFt.Text = ftv.ToString();
txtClmBreadthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(10);
FtToIn(FtinV, out ftv, out incv);
txtClmLengthFt.Text = ftv.ToString();
txtClmLengthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(11);
FtToIn(FtinV, out ftv, out incv);
txtBsDepthFt.Text = ftv.ToString();
txtBsDepthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(12);
FtToIn(FtinV, out ftv, out incv);
txtBsBreadthFt.Text = ftv.ToString();
txtBsBreadthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(13);
FtToIn(FtinV, out ftv, out incv);
txtBsLengthFt.Text = ftv.ToString();
txtBsLengthIn.Text = incv.ToString();
FtinV = Dr.GetFloat(14);
FtToIn(FtinV, out ftv, out incv);
txtBsMatThickFt.Text = ftv.ToString();
txtBsMatThickIn.Text = incv.ToString();
txtSand.Text = Dr.GetFloat(15).ToString();
txtStone.Text = Dr.GetFloat(16).ToString();
txtCement.Text = Dr.GetFloat(17).ToString();
txtRods.Text = Dr.GetInt16(18).ToString();
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Can\'t load all edit column data\n " + ex.Message, "Error", MessageBoxButtons.OK);
}
}
private void getEditWallData()
{
try
{
int ftv = 0, incv = 0;
float FtinV = 0;
string Qry = "select * from Wall_Tab where Wl_Name ='" + HomeFrm.EdtCntrl + "';";
OleDbConnection con = new OleDbConnection(ConStr);
OleDbCommand cmd = new OleDbCommand(Qry, con);
con.Open();
OleDbDataReader Dr = null;
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
WlNo = Dr.GetInt32(0);
WlName = Dr.GetString(1);
txtStartX.Text = Dr.GetInt16(2).ToString();
txtStartY.Text = Dr.GetInt16(3).ToString();
txtEndX.Text = Dr.GetInt16(4).ToString();
txtEndY.Text = Dr.GetInt16(5).ToString();
txtNmX.Text = Dr.GetInt16(6).ToString();
txtNmY.Text = Dr.GetInt16(7).ToString();
FtinV = Dr.GetFloat(8);
FtToIn(FtinV, out ftv, out incv);
txtWlHeightFt.Text = ftv.ToString();
txtWlHeightIn.Text = incv.ToString();
cmbBreadth.Text = Dr.GetFloat(9).ToString();
FtinV = Dr.GetFloat(10);
FtToIn(FtinV, out ftv, out incv);
txtWlLengthFt.Text = ftv.ToString();
txtWlLengthIn.Text = incv.ToString();
txtSand.Text = Dr.GetFloat(11).ToString();
txtCement.Text = Dr.GetFloat(12).ToString();
bool Pls = Dr.GetBoolean(13);
if (Pls == true)
{
txtPlstTk.Text = Dr.GetFloat(14).ToString();
txtPlstSand.Text = Dr.GetFloat(15).ToString();
txtPlstCement.Text = Dr.GetFloat(16).ToString();
string Sides = Dr.GetString(17);
if (Sides == "One")
{
rbOne.Checked = true;
}
else
{
rbBoth.Checked = true;
}
}
}
}
if (Dr != null)
{
Dr.Close();
}
if (con != null)
{
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Can\'t load all edit wall data.\n" + ex.Message, "Error", MessageBoxButtons.OK);
}
}
public List <pelangganModels> GetListHutang(string kdPelanggan)
{
List <pelangganModels> dList = new List <pelangganModels>();
string SqlString = @"Select a.id,a.kode as no_trans,a.tgl_registrasi,a.nama,a.alamat,a.kode as kd_pelanggan,
a.batas_kredit,a.bunga as persen_bunga, a.waktu as jangka_waktu ,
a.tbunga as bunga_per_bulan, a.bunga2 as pokok_per_bulan,
a.pinjaman as sisa_pinjaman, a.angsuran as angsuran_per_bulan ,
b.simwajib,b.simpanan as simpanan_tmk
from anggota as b LEFT join pelanggan as a on a.kota = b.noang
where a.pinjaman > 10 and b.noang = ? ";
string ConnStr = ManageString.GetConnStr();
using (OleDbConnection conn = new OleDbConnection(ConnStr))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("noang", kdPelanggan);
using (OleDbDataReader aa = cmd.ExecuteReader())
{
if (aa.HasRows)
{
//Log.Debug(DateTime.Now + " GetPelangganREPO ====>>>>>> Jumlah Data : " + aa.Cast<object>().Count());
//Log.Debug(DateTime.Now + " aa READ >>>>>> " + aa.Read().ToString());
while (aa.Read())
{
//Log.Debug(DateTime.Now + " NOTRANS ====>>>>>> " + aa["no_trans"].ToString());
//string f_0 = aa.GetFieldType(0).ToString();
//string f_1 = aa.GetFieldType(1).ToString();
//string f_2 = aa.GetFieldType(2).ToString();
//string f_3 = aa.GetFieldType(3).ToString();
//string f_4 = aa.GetFieldType(4).ToString();
//string f_5 = aa.GetFieldType(5).ToString();
//string f_6 = aa.GetFieldType(6).ToString();
//string f_7 = aa.GetFieldType(7).ToString();
//string f_8 = aa.GetFieldType(8).ToString();
//string f_9 = aa.GetFieldType(9).ToString();
//string f_10 = aa.GetFieldType(10).ToString();
//string f_11 = aa.GetFieldType(11).ToString();
//string f_12 = aa.GetFieldType(12).ToString();
//Log.Debug(DateTime.Now + " F_0 : " + f_0 + "\n" + " F_1 : " + f_1 + "\n" + " F_2 : " + f_2 + "\n" +
// " F_3 : " + f_3 + "\n" + " F_4 : " + f_4 + "\n" + " F_5 : " + f_5 + "\n" +
// " F_6 : " + f_6 + "\n" + " F_7 : " + f_7 + "\n" + " F_8 : " + f_8 + "\n" +
// " F_9 : " + f_9 + "\n" + " F_10 : " + f_10 + "\n" + " F_11 : " + f_11 + "\n" +
// " F_12 : " + f_12);
pelangganModels item = new pelangganModels();
item.id = aa.GetInt32(0);
item.no_trans = aa.GetString(1);
item.tgl_registrasi = aa.GetDateTime(2);
item.nama = aa.GetString(3);
item.alamat = aa.GetString(4);
item.kd_pelanggan = aa.GetString(5);
item.batas_kredit = aa.GetDecimal(6);
item.persen_bunga = Math.Round(Convert.ToDecimal(aa.GetFloat(7)), 2);
item.jangka_waktu = aa.GetInt16(8);
item.bunga_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(9)), 2);
item.pokok_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(10)), 2);
item.sisa_pinjaman = Math.Round(Convert.ToDecimal(aa.GetDouble(11)), 2);
item.angsuran_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(12)), 2);
item.simpanan_wajib = Math.Round(Convert.ToDecimal(aa.GetDouble(13)), 2);
item.simpanan_tmk = Math.Round(Convert.ToDecimal(aa.GetDouble(14)), 2);
dList.Add(item);
}
//Log.Debug(DateTime.Now + " GetPelangganREPO ====>>>>>> Jumlah LIST : " + dList.Count());
}
}
}
}
return(dList);
}
FixtureProfile BuildFixtureProfile(OleDbDataReader reader)
{
int i = -1;
FixtureClass fixtureClass;
if (reader.IsDBNull(++i))
{
fixtureClass = FixtureClasses.Unclassified;
}
else
{
fixtureClass = FixtureClassFromDatabaseId(reader.GetInt32(i));
}
string name;
if (reader.IsDBNull(++i))
{
name = null;
}
else
{
name = reader.GetString(i);
}
double?minVolume;
if (reader.IsDBNull(++i))
{
minVolume = null;
}
else
{
minVolume = (double)reader.GetFloat(i);
}
double?maxVolume;
if (reader.IsDBNull(++i))
{
maxVolume = null;
}
else
{
maxVolume = (double)reader.GetFloat(i);
}
double?minPeak;
if (reader.IsDBNull(++i))
{
minPeak = null;
}
else
{
minPeak = (double)reader.GetFloat(i);
}
double?maxPeak;
if (reader.IsDBNull(++i))
{
maxPeak = null;
}
else
{
maxPeak = (double)reader.GetFloat(i);
}
TimeSpan?minDuration;
if (reader.IsDBNull(++i))
{
minDuration = null;
}
else
{
minDuration = new TimeSpan(0, 0, (reader.GetInt32(i)));
}
TimeSpan?maxDuration;
if (reader.IsDBNull(++i))
{
maxDuration = null;
}
else
{
maxDuration = new TimeSpan(0, 0, (reader.GetInt32(i)));
}
double?minMode;
if (reader.IsDBNull(++i))
{
minMode = null;
}
else
{
minMode = (double)reader.GetFloat(i);
}
double?maxMode;
if (reader.IsDBNull(++i))
{
maxMode = null;
}
else
{
maxMode = (double)reader.GetFloat(i);
}
int?minModeFrequency;
if (reader.IsDBNull(++i))
{
minModeFrequency = null;
}
else
{
minModeFrequency = reader.GetInt32(i);
}
int?maxModeFrequency;
if (reader.IsDBNull(++i))
{
maxModeFrequency = null;
}
else
{
maxModeFrequency = reader.GetInt32(i);
}
return(new FixtureProfile(name, fixtureClass, minVolume, maxVolume, minPeak, maxPeak,
minDuration, maxDuration, minMode, maxMode));
}
/// <summary>
/// Test CUBRID data types Get...()
/// </summary>
private static void Test_Various_DataTypes()
{
using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = TestCasesOld.connString;
conn.Open();
TestCasesOld.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, ";
sql += "c_smallint smallint, ";
sql += "c_integer integer, ";
sql += "c_bigint bigint, ";
sql += "c_numeric numeric(15,1), ";
sql += "c_float float, ";
sql += "c_decimal decimal(15,3), ";
sql += "c_double double, ";
sql += "c_char char, ";
sql += "c_varchar varchar(4096), ";
sql += "c_time time, ";
sql += "c_date date, ";
sql += "c_timestamp timestamp, ";
sql += "c_datetime datetime, ";
sql += "c_bit bit(1), ";
sql += "c_varbit bit varying(4096), ";
sql += "c_monetary monetary, ";
sql += "c_string string";
sql += ")";
TestCasesOld.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, ";
sql += "11, ";
sql += "111, ";
sql += "1111, ";
sql += "1.1, ";
sql += "1.11, ";
sql += "1.111, ";
sql += "1.1111, ";
sql += "'a', ";
sql += "'abcdfghijk', ";
sql += "TIME '13:15:45 pm', ";
sql += "DATE '00-10-31', ";
sql += "TIMESTAMP '13:15:45 10/31/2008', ";
sql += "DATETIME '13:15:45 10/31/2008', ";
sql += "B'0', ";
sql += "B'0', ";
sql += "123456789, ";
sql += "'qwerty'";
sql += ")";
TestCasesOld.ExecuteSQL(sql, conn);
sql = "select * from t";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
try
{
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetInt16(1) == 11);
Debug.Assert(reader.GetInt32(2) == 111);
Debug.Assert(reader.GetInt64(3) == 1111);
Debug.Assert(reader.GetDecimal(4) == (decimal)1.1);
Debug.Assert(reader.GetFloat(5) == (float)1.11); //"Single"
Debug.Assert(reader.GetDecimal(6) == (decimal)1.111);
Debug.Assert(reader.GetDouble(7) == (double)1.1111);
//We use GetString() because GetChar() is not supported or System.Data.OleDb.
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getchar
Debug.Assert(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Debug.Assert(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
//GetGateTime cannot cast just the time value in a DateTime object, so we use TimeSpan
Debug.Assert(reader.GetTimeSpan(10) == new TimeSpan(13, 15, 45)); //"TimeSpan"
Debug.Assert(reader.GetDateTime(11) == new DateTime(2000, 10, 31)); //"DateTime"
Debug.Assert(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
Console.WriteLine(reader.GetValue(13));
Debug.Assert(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
//The GetByte() method does not perform any conversions and the driver does not give tha data as Byte
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getbyte
//Use GetValue() or GetBytes() methods to retrieve BIT coulumn value
// Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit(1)" in CUBRID)
//Or
Byte[] value = new Byte[1];
reader.GetBytes(14, 0, value, 0, 1);
// Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit(1)" in CUBRID)
//Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit varying(4096)" in CUBRID)
//Or
// reader.GetBytes(15, 0, value, 0, 1);
// Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit varying(4096)" in CUBRID)
Debug.Assert(reader.GetDouble(16) == 123456789.0); //"Double" ("Monetary" in CUBRID)
Debug.Assert(reader.GetString(17) == "qwerty"); //"String"
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
TestCasesOld.ExecuteSQL("drop table if exists t", conn);
}
}
private void copyMatCardTable()
{
string sql = "SELECT ID, NППЗаказа, NППДетали, NДеталиОснастки, НаименованиеДетали, Материал, КузнечнаяОперация, РазмерыДетали, Количество, КоличествоДеталей, ФрезОбр, СтрогОбр, ШлифОбр, ВремяФрез, ВремяСтрог, ВремяШлиф, ВесЗаготовки, ВесВсего, ДополнДляЗакНар, ДатаРедактТехн, Склад, КодЕдИзм, CODEMAT, annealing, term, strogterm, ВесДетали, ordered, Материа FROM МатериальнаяКарта WHERE NППЗаказа>=80000";
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.connStr);
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
MaterialCard m = new MaterialCard();
m.matCardId = r.GetInt32(0);
m.orderId = r.GetInt32(1);
if (r[2] != DBNull.Value)
{
m.detailNumber = r.GetInt16(2);
}
if (r[3] != DBNull.Value)
{
m.detailTONumber = r.GetString(3);
}
if (r[4] != DBNull.Value)
{
m.detailTOTitle = r.GetString(4);
}
if (r[5] != DBNull.Value)
{
m.materialTitle = r.GetString(5);
}
if (r[6] != DBNull.Value)
{
m.blackSmithOp = r.GetString(6);
}
if (r[7] != DBNull.Value)
{
m.detailSize = r.GetString(7);
}
m.blankQty = r.GetFloat(8);
m.detailQty = r.GetFloat(9);
if (r[10] != DBNull.Value)
{
m.millingOp = r.GetString(10);
}
if (r[11] != DBNull.Value)
{
m.planerOp = r.GetString(11);
}
if (r[12] != DBNull.Value)
{
m.grindingOp = r.GetString(12);
}
if (r[13] != DBNull.Value)
{
m.millingTime = r.GetFloat(13);
}
if (r[14] != DBNull.Value)
{
m.planerTime = r.GetFloat(14);
}
if (r[15] != DBNull.Value)
{
m.grindingTime = r.GetFloat(15);
}
if (r[16] != DBNull.Value)
{
m.weight = r.GetFloat(16);
}
if (r[17] != DBNull.Value)
{
m.weightTotal = r.GetFloat(17);
}
if (r[18] != DBNull.Value)
{
m.supplementToOrder = r.GetBoolean(18);
}
if (r[19] != DBNull.Value)
{
m.technologyEditTime = r.GetDateTime(19);
}
if (r[20] != DBNull.Value)
{
m.storeId = r.GetInt16(20);
}
if (r[21] != DBNull.Value)
{
m.unitOfMeasureId = r.GetInt16(21);
}
if (r[22] != DBNull.Value)
{
m.materialCode = r.GetInt32(22);
}
if (r[23] != DBNull.Value)
{
m.annealing = r.GetBoolean(23);
}
if (r[24] != DBNull.Value)
{
m.thermalOp = r.GetString(24);
}
if (r[25] != DBNull.Value)
{
m.planerThermalOp = r.GetString(25);
}
if (r[26] != DBNull.Value)
{
m.detailWeight = r.GetDouble(26);
}
if (r[27] != DBNull.Value)
{
m.ordered = r.GetBoolean(27);
}
string json = JsonConvert.SerializeObject(m, Formatting.None, new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore
});
// MessageBox.Show(json);
MaterialCard m2 = rest.saveEntity <MaterialCard>(m);
if (m2.matCardId < 1)
{
break;
}
}
r.Close();
cmd.Dispose();
conn.Close();
MessageBox.Show("Done!");
}
//Onglet Recapitulatif
private void btnCreeReca_Click(object sender, EventArgs e)
{
try
{
//Initialisation des variables
connec.Open();
string mois = dtpReca.Value.Month.ToString();
string annee = dtpReca.Value.Year.ToString();
string text = "____________________________________________________";
string typeNull = "Type NULL";
float montant = 0;
int recette = 0;
int percu = 0;
int indentation = 20;
int hauteurDesLignes = 720;
List <string> un = new List <string>();
List <string> de = new List <string>();
List <string> tr = new List <string>();
List <string> qu = new List <string>();
List <string> ci = new List <string>();
List <string> si = new List <string>();
List <Boolean> nbTransaction = new List <Boolean>();
pdfDocument myDoc = new pdfDocument("Recapitulatif_" + mois + "_" + annee, "Pique_Sous");
pdfPage myPage = myDoc.addPage();
//SQL
OleDbCommand cd1 = new OleDbCommand("SELECT [Transaction].* FROM [Transaction] WHERE MONTH([dateTransaction]) = " + dtpReca.Value.Month.ToString(), connec);
OleDbDataReader dr1 = cd1.ExecuteReader();
//Lecture de la base
while (dr1.Read())
{
nbTransaction.Add(dr1.GetBoolean(4));
if (dr1.GetBoolean(4) == true)
{
recette++;
}
if (dr1.GetBoolean(5) == true)
{
percu++;
}
montant = montant + dr1.GetFloat(3);
un.Add(dr1[1].ToString().Substring(0, 11));
de.Add(dr1[2].ToString());
tr.Add(dr1[3].ToString());
qu.Add(dr1[4].ToString());
ci.Add(dr1[5].ToString());
if (dr1[6].ToString() != "")
{
si.Add(dr1[6].ToString());
}
else
{
si.Add(typeNull);
}
}
for (int i = 0; i < si.Count; i++)
{
if (si[i] != typeNull)
{
//Pour eviter de faire une requette avec une jointure
OleDbCommand cd2 = new OleDbCommand("SELECT [TypeTransaction].* FROM [TypeTransaction] where [codeType] = " + si[i], connec);
OleDbDataReader dr2 = cd2.ExecuteReader();
while (dr2.Read())
{
si[i] = dr2[1].ToString();
}
}
}
//Creation du text dans le PDF
myPage.addText("Recapitulatif du : " + mois + "_" + annee, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText("Dépenses", indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - (30 + 25 * un.Count);
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText("Recette : " + recette.ToString(), indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(" Depenses : " + montant.ToString(), indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText("Reste a persevoir : " + percu.ToString(), indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText("Somme total dépensée : -" + montant.ToString(), indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText("nombres de transactions : " + nbTransaction.Count.ToString(), indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
hauteurDesLignes = hauteurDesLignes - 30;
myPage.addText(text, indentation, hauteurDesLignes, myDoc.getFontReference(predefinedFont.csHelvetica), 20);
//Initiallisation tableau PDF
pdfTable myTable = new pdfTable(myDoc);
myTable.borderSize = 1;
myTable.borderColor = sharpPDF.pdfColor.Black;
myTable.tableHeader.addColumn(90);
myTable.tableHeader.addColumn(120);
myTable.tableHeader.addColumn(70);
myTable.tableHeader.addColumn(70);
myTable.tableHeader.addColumn(70);
myTable.tableHeader.addColumn(110);
pdfTableRow myRow = myTable.createRow();
//Remplissage de la première ligne du tableau
myRow[0].addText("Date Transaction");
myRow[1].addText("Description");
myRow[2].addText("Montant");
myRow[3].addText("Recette ?");
myRow[4].addText("Perçu ?");
myRow[5].addText("Type de dépence ?");
myTable.addRow(myRow);
//Remplissage des ligne du tableau
for (int i = 0; i < un.Count; i++)
{
myRow = myTable.createRow();
myRow[0].addText(un[i]);
myRow[1].addText(de[i]);
myRow[2].addText(tr[i]);
myRow[3].addText(qu[i]);
myRow[4].addText(ci[i]);
myRow[5].addText(si[i]);
myTable.addRow(myRow);
}
//Place le tableau
myTable.coordY = 650;
myTable.coordX = 20;
myPage.addTable(myTable);
//creer le PDF
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
myDoc.createPDF(fbd.SelectedPath + @"\Recapitulatif_" + mois + "_" + annee + ".pdf");
}
myPage = null;
myDoc = null;
connec.Close();
MessageBox.Show("PDF créé");
}
catch (InvalidOperationException erreur)
{
MessageBox.Show("Erreur de chaine de connexion ! pdf");
MessageBox.Show(erreur.Message);
}
catch (OleDbException erreur)
{
MessageBox.Show("Erreur de requete SQL ! pdf");
MessageBox.Show(erreur.Message);
}
}
public void DoTestTypes(DbTypeParametersCollection row)
{
testTypesInvocations++;
exp = null;
string rowId = "43970_" + this.testTypesInvocations.ToString();
OleDbDataReader rdr = null;
OleDbConnection con = null;
try
{
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
while (rdr.Read())
{
//Run over all the columns in the result set row.
//For each column, try to read it as a float.
for (int i = 0; i < row.Count; i++)
{
if (row[i].Value.GetType() == typeof(float)) //The value in the result set should be a float.
{
try
{
BeginCase(string.Format("Calling GetFloat() on a field of dbtype {0}", row[i].DbTypeName));
float retFloat = rdr.GetFloat(i);
Compare(row[i].Value, retFloat);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
else //The value in the result set should NOT be float. In this case an Invalid case exception should be thrown.
{
try
{
BeginCase(string.Format("Calling GetFloat() on a field of dbtype {0}", row[i].DbTypeName));
float retFloat = rdr.GetFloat(i);
ExpectedExceptionNotCaught("InvalidCastException");
}
catch (InvalidCastException ex)
{
ExpectedExceptionCaught(ex);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
}
}
}
finally
{
row.ExecuteDelete(rowId);
if ((rdr != null) && (!rdr.IsClosed))
{
rdr.Close();
}
if ((con != null) && (con.State != ConnectionState.Closed))
{
con.Close();
}
}
}
