private void SaveNewMovieToDatabase()
{
dt = DateTime.Now;
string query = "INSERT INTO film VALUES (" + CalculateMovieId() + ",'" + txtTitle.Text + "','" + txtDescription.Text + "'," +
txtReleaseYear.Text + "," + cbxLanguages.SelectedIndex + 1 + ",NULL," + txtRentalDuration.Text + "," + txtRentalRate.Text +
"," + txtLength.Text + "," + txtReplacementCost.Text + "," + cbxRatings.SelectedIndex + 1 + ",NULL,'" + dt.ToString("yyyy-MM-dd H:mm:ss") + "',NULL);";
try { command.CommandText = query; reader = command.ExecuteReader(); reader?.Close(); }
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void fillList()
{
AnimeList _anime;
try
{
cmd.CommandText = Queries.LoadComboBox();
reader = cmd.ExecuteReader();
_animeList.Clear();
while (reader.Read())
{
_anime = new AnimeList(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString());
_animeList.Add(_anime);
}
}
catch (Exception)
{
MessageBox.Show("We can't load the list. Please restart the program. If problem exist. Please contact with Administrator: [email protected]", "Can't load list...", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader?.Close();
}
public ActionResult getUserNeedHelpLocation(string search)
{
LocationClass userneedhelp = new LocationClass();
SearchClass current = JsonConvert.DeserializeObject <SearchClass>(search);
//Insert record
OdbcConnection OdbcConn =
new OdbcConnection("Dsn=VHHAL202;uid=HACKT03;pwd=gfZhA7dQ");
OdbcCommand myCommand = new OdbcCommand();
myCommand.Connection = OdbcConn;
OdbcConn.Open();
string sqlGetUser = "******"HACKT03\".\"USERS\" where NAME = '" + current.name + "'";
myCommand.CommandText = sqlGetUser;
string id = "";
var reader = myCommand.ExecuteReader();
if (reader.Read())
{
//we hvae an existing record
id = reader["userid"].ToString();
reader.Close();
}
else
{
reader.Close();
//Insert user
string sqlInsert = $"insert into \"HACKT03\".\"USERS\" values(id_seq.NEXTVAL, '{current.name}', '', NEW ST_Point('Point ({current.location.x} {current.location.y})', 4326))";
myCommand.CommandText = sqlInsert;
myCommand.ExecuteNonQuery();
sqlGetUser = "******"HACKT03\".\"USERS\" where NAME = '" + current.name + "'";
myCommand.CommandText = sqlGetUser;
reader = myCommand.ExecuteReader();
if (reader.Read())
{
//we hvae an existing record
id = reader["userid"].ToString();
}
reader.Close();
}
foreach (var item in current.items)
{
myCommand.CommandText = $"INSERT INTO \"HACKT03\".\"TRANSACTIONS\" VALUES(id_seq_trans.NEXTVAL, {id}, 'PICK' , '{item.productType}', {item.amount}, 'Finished', '')";
myCommand.ExecuteNonQuery();
}
string sql = "SELECT TOP 1 * FROM (select A.NAME AS \"USERNAME\",B.ID AS \"FACILITYID\", B.NAME AS \"FACILITYNAME\" , B.LOCATION.ST_X() AS \"LON\", B.LOCATION.ST_Y() AS \"LAT\", A.LOCATION.ST_Distance(B.LOCATION, 'meter') AS \"DISTANCE\" from \"HACKT03\".\"USERS\" A, \"HACKT03\".\"FACILITIES\" B where A.NAME = '" + current.name + "') TEMP ORDER BY DISTANCE";
myCommand.CommandText = sql;
OdbcDataReader myReader = myCommand.ExecuteReader();
try
{
//Get Neerest Center
while (myReader.Read())
{
userneedhelp.x = double.Parse(myReader["LON"].ToString());
userneedhelp.y = double.Parse(myReader["LAT"].ToString());
userneedhelp.name = myReader["FACILITYNAME"]?.ToString();
userneedhelp.distance = Math.Round(0.000621371 * double.Parse(myReader["DISTANCE"].ToString()), 2);
}
}
finally
{
myReader.Close();
OdbcConn.Close();
}
//Return it
// supplier.x = -95.4805;
// supplier.y = 29;
var data = Content(JsonConvert.SerializeObject(userneedhelp), "application/json", System.Text.Encoding.UTF8);
return(data);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_SAProle.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetSAProle</returns>
public returnGetSAProle GetSAProle(int ID)
{
returnGetSAProle rv = new returnGetSAProle();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_Name\",\"c_u_Description\",\"c_r_SubProcess\",\"c_u_System\",\"c_u_Platform\",\"c_u_RoleActivity\",\"c_u_RoleType\",\"c_u_Comment\" from \"t_RBSR_AUFW_u_SAProle\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
int drctr = 0;
while (dr.Read())
{
drctr++;
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
else
{
rv.Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
rv.Description = null;
}
else
{
rv.Description = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'SubProcessID'");
}
else
{
rv.SubProcessID = dr.GetInt32(3);
}
if (dr.IsDBNull(4))
{
throw new Exception("Value 'null' is not allowed for 'System'");
}
else
{
rv.System = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
throw new Exception("Value 'null' is not allowed for 'Platform'");
}
else
{
rv.Platform = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
rv.RoleActivity = null;
}
else
{
rv.RoleActivity = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
rv.RoleType = null;
}
else
{
rv.RoleType = dr.GetString(7);
}
if (dr.IsDBNull(8))
{
rv.Comment = null;
}
else
{
rv.Comment = dr.GetString(8);
}
}
dr.Close();
dr.Dispose();
if (drctr != 1)
{
throw new Exception("Operation selected no rows!");
}
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// Verify database method is intended to validate if correct values are stored in backend systems.
/// </summary>
/// <param name="data">This will be record number for which points to sql query stored in test case/ test data worksheet.
/// E.g. which data column mentiods 2, it means locate a worksheet named "DBTestData", find row, in which first column contains value 2,
/// in that row, locate column 'SqlQuery' and perform actions
/// </param>
/// <param name="databaseType">This is a hint telling which database server is to be used</param>
/// <returns></returns>
public static bool VerifyDatabase(string data, string databaseType)
{
bool result = false;
//Retrieve Sql query to be executed from test data
string sqlQuery = Utility.GetVariable("[td]SQLQuery");
//Create a new connection string based on type of database specified in Parameters.ini file
string connectionString = getDBConnectionString(databaseType);
try
{
string param = "[td]param";
string expectedValue = string.Empty;
string actualValue = string.Empty;
//replace the variables in the query by their values.
sqlQuery = Utility.ReplaceVariablesInString(sqlQuery);
query = sqlQuery;
ConnectionString = connectionString;
//using odbc connection to work with multiple database types
OdbcDataReader reader = null;
//execute the query on the database.
reader = executeQueryTillTimeout();
//Handling when no data was retrieved from database
if (!reader.HasRows)
{
Property.Remarks = "No data could be retrieved from database, please check sql query" + "." + "\n" +
"Connection String: " + connectionString + "\n" +
"Sql Query Used: " + sqlQuery;
comm.Dispose();
reader.Close();
conn.Close();
return(false);
}
//Compare output of each column with expected values from parameters
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//Retrieve expected value from parameters
expectedValue = Utility.GetVariable(param + (i + 1).ToString());
expectedValue = Utility.ReplaceVariablesInString(expectedValue);
//Retrieve actual value from output of query
actualValue = reader[i].ToString();
//Compare both expected and actual values, these should equal
if (Utility.DoKeywordMatch(expectedValue, actualValue))
{
result = true;
Property.Remarks = "Actual value " + actualValue + " of column '" + reader.GetName(i) +
"' matches with expected value " + expectedValue + "\n" +
"Connection String: " + connectionString + "\n" +
"Sql Query Used: " + sqlQuery;
}
else
{
result = false;
Property.Remarks = "Actual value " + actualValue + " of column '" + reader.GetName(i) +
"' does not matches with expected value " + expectedValue + "\n" +
"Connection String: " + connectionString + "\n" +
"Sql Query Used: " + sqlQuery;
break;
}
}
//Break loop as only first record needs to be validated
break;
}
comm.Dispose();
reader.Close();
conn.Close();
return(result);
}
catch (TimeoutException)
{
Property.Remarks = Utility.GetCommonMsgVariable("KRYPTONERRCODE0058").Replace("{MSG}", errorMessage) + "\n" + "Connection String: " + connectionString + "\n" +
"Sql Query Used: " + sqlQuery;
return(false);
}
catch (Exception e)
{
//throw e;
//Custom messaging to be returned
result = false;
Property.Remarks = "Exception in database verification: " + e.Message + "\n" +
"Connection String: " + connectionString + "\n" +
"Sql Query Used: " + sqlQuery;
return(result);
}
}
public static void ReadData(string connectionString, string fileName)
{
logger.Info("Application has started Reading Access DB");
//Club data
string queryStringClub = "SELECT * FROM Team";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryStringClub, connection);
connection.Open();
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
DataTransfer.ProcessClubData(reader);
// Call Close when done reading.
reader.Close();
}
//Standard events
string queryStringResults = "select Full_Eventname,Rnd_ltr,Results.First_name,Results.Last_name,Results.Team_Abbr,Results.Reg_no,Athlete.Birth_date,Athlete.Ath_Sex, Res_markDisplay,Res_wind,Res_place from Results inner join Athlete on Athlete.Ath_no=Results.Ath_no where Full_Eventname not like '%relay%' and Event_name not like '%pentathlon%' and Full_Eventname not like '%masters%' ";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryStringResults, connection);
connection.Open();
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
DataTransfer.ProcessResultsData(reader, fileName);
reader.Close();
}
//Relay events and sprint Medley
string queryStringRelayResults = "select Full_Eventname,Relay_Ltr, a.First_name,a.Last_name, r.Team_Abbr,Res_markDisplay,Res_wind,Res_place ,a.Birth_date,a.Reg_no,a.Ath_sex from Results r inner join Athlete a ON a.Ath_no = r.RelayLeg1_Ath_no OR a.Ath_no = r.RelayLeg2_Ath_no OR a.Ath_no = r.RelayLeg3_Ath_no OR a.Ath_no = r.RelayLeg4_Ath_no where Full_Eventname like '%relay%' OR Full_Eventname like '%Sprint Medley%'";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryStringRelayResults, connection);
connection.Open();
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
DataTransfer.ProcessResultsDataRelay(reader, fileName);
// Call Close when done reading.
reader.Close();
}
//combined events
string queryStringCombinedResults = "select Event_name,MultiSubEvent_name, Rnd_ltr,Results.First_name,Results.Last_name,Results.Team_Abbr,Athlete.Reg_no, Athlete.Birth_date,Athlete.Ath_Sex, Res_markDisplay,Res_wind,Res_place,Event_score,Results.Event_dist,Divisions.Div_name from (Results inner join Athlete on Athlete.Ath_no = Results.Ath_no) inner join Divisions on Divisions.Div_no = Results.Div_no where Event_name like '%athlon%' ";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryStringCombinedResults, connection);
connection.Open();
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
DataTransfer.ProcessResultsDataCombined(reader, fileName);
// Call Close when done reading.
reader.Close();
}
//Master events
string queryStringMasterResults = "select Full_Eventname, Rnd_ltr,Results.First_name,Results.Last_name,Results.Team_Abbr,Athlete.Reg_no,Athlete.Birth_date,Athlete.Ath_Sex, Res_markDisplay,Res_wind,Res_place,Divisions.Div_name from (Results inner join Athlete on Athlete.Ath_no=Results.Ath_no ) inner join Divisions on Divisions.Div_no = Results.Div_no where Full_Eventname like '%masters%' ";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryStringMasterResults, connection);
connection.Open();
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
DataTransfer.ProcessResultsDataMasters(reader, fileName);
// Call Close when done reading.
reader.Close();
}
}
// Proceed to insert of TIDs to MERCHBIN file
private void button5_Click(object sender, EventArgs e)
{
string sublstItm1;
string sublstItm2;
string chkOriginID = " ";
string route_hostID = " ";
string chkOriginID2 = " ";
string route_hostID2 = " ";
string templKarf = "1111 ";
OdbcCommand comCursor = conn.CreateCommand();
//OdbcDataReader readerCursor = new OdbcDataReader();
foreach (ListViewItem item in listView1.Items)
{
//for (int i = 0; i < item.SubItems.Count; i++)
// {
chkOriginID = "0";
chkOriginID2 = "0";
sublstItm1 = item.SubItems[0].Text.ToString();
sublstItm2 = item.SubItems[1].Text.ToString();
textBox1.Text = item.SubItems[0].Text.ToString();
textBox2.Text = item.SubItems[1].Text.ToString();
//Transform Bank into HostID
string chkOrigin = textBox1.Text;
switch (chkOrigin)
{
case "PIRAEUS":
chkOriginID = "1";
route_hostID = "NET_ABC";
break;
case "ETHNIKI":
chkOriginID = "6";
route_hostID = "NET_NTBN";
break;
case "ALPHABANK":
chkOriginID = "202";
route_hostID = "NET_CLBICALPHA";
break;
case "EUROBANK":
chkOriginID = "205";
route_hostID = "NET_CLBICEBNK";
break;
}
//Transform Bank into DEST
string chkOrigin2 = textBox2.Text;
switch (chkOrigin2)
{
case "PIRAEUS":
chkOriginID2 = "1";
route_hostID2 = "NET_ABC";
break;
case "ETHNIKI":
chkOriginID2 = "6";
route_hostID2 = "NET_NTBN";
break;
case "ALPHABANK":
chkOriginID2 = "202";
route_hostID2 = "NET_CLBICALPHA";
break;
case "EUROBANK":
chkOriginID2 = "205";
route_hostID2 = "NET_CLBICEBNK";
break;
}
textBoxFr.Text.ToString();
textBoxTo.Text.ToString();
//OdbcCommand comCursor = conn.CreateCommand();
if (chkOriginID == "1" || chkOriginID == "6" || chkOriginID == "202" || chkOriginID == "205")
{
// Check if trxs will routed to same bank id so to change DESTPORT
if (chkOriginID == chkOriginID2)
{
comCursor.CommandText = "declare @tid varchar(16) " +
"declare @name varchar(20) " +
"declare @mid varchar(16) " +
"declare @uploadhost int " +
"declare @DESTPORT varchar(20) " +
"declare merch_cursor cursor for " +
" select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
" where mid = ? " +
" and a.uploadhostid = ? " +
" and a.uploadhostid = b.uploadhostid " +
"open merch_cursor; " +
" if @@ERROR > 0 " +
" return; " +
" fetch next from merch_cursor into @tid, @name " +
" while @@FETCH_STATUS = 0 " +
" begin " +
" insert into dbo.MERCHBINS_TEST " +
" (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX, GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX) " +
" (select @TID, DESTPORT, BINLOWER, BINUPPER," +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX from dbo.MERCHBINS_TEST " +
" where TID = " + templKarf + " and DESTPORT = ?) " +
" fetch next from merch_cursor " +
" into @tid, @name " +
" end " +
" CLOSE merch_cursor " +
" deallocate merch_cursor ";
}
else
{
comCursor.CommandText = "declare @tid varchar(16) " +
"declare @name varchar(20) " +
"declare @mid varchar(16) " +
"declare @uploadhost int " +
"declare @DESTPORT varchar(20) " +
"declare merch_cursor cursor for " +
" select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
" where mid = " + midDescr.Text +
" and a.uploadhostid = " + chkOriginID +
" and a.uploadhostid = b.uploadhostid " +
"open merch_cursor " +
" if @@ERROR > 0 " +
" return; " +
" fetch next from merch_cursor into @tid, @name " +
" while @@FETCH_STATUS = 0 " +
" begin " +
" insert into dbo.MERCHBINS_TEST " +
" (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX, GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX) " +
" (select @TID, '" + route_hostID2 + "', BINLOWER, BINUPPER, " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX from dbo.MERCHBINS_TEST " +
" where TID = " + templKarf + " and DESTPORT = '" + route_hostID + "') " +
" fetch next from merch_cursor " +
" into @tid, @name " +
" end " +
" CLOSE merch_cursor " +
" deallocate merch_cursor ";
//comCursor.CommandText = "declare @tid varchar(16) " +
// "declare @name varchar(20) " +
// "declare @mid varchar(16) " +
// "declare @uploadhost int " +
// "declare @DESTPORT varchar(20) " +
// "declare merch_cursor cursor for " +
// " select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
// " where mid = ? " +
// " and a.uploadhostid = ? " +
// " and a.uploadhostid = b.uploadhostid " +
// "open merch_cursor " +
// " if @@ERROR > 0 " +
// " return; " +
// " fetch next from merch_cursor into @tid, @name " +
// " while @@FETCH_STATUS = 0 " +
// " begin " +
// " insert into dbo.MERCHBINS_TEST " +
// " (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX, GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX) " +
// " (select @TID, '" + route_hostID2 + "', BINLOWER, BINUPPER, " +
// textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
// " GRACEMIN, GRACEMAX, ALLOWED, AMOUNTMIN, AMOUNTMAX from dbo.MERCHBINS_TEST " +
// " where TID = " + templKarf + " and DESTPORT = ?) " +
// " fetch next from merch_cursor " +
// " into @tid, @name " +
// " end " +
// " CLOSE merch_cursor " +
// " deallocate merch_cursor ";
}
comCursor.Parameters.Add("@mid", OdbcType.VarChar, 16).Value = midDescr.Text;
comCursor.Parameters.Add("@uploadhost", OdbcType.Int).Value = chkOriginID;
comCursor.Parameters.Add("@DESTPORT", OdbcType.VarChar, 20).Value = route_hostID;
OdbcDataReader readerCursor1 = comCursor.ExecuteReader();
readerCursor1.Close();
}
else if (chkOrigin == "KOYBAS")
{
comCursor.CommandText = "declare @tid varchar(16) " +
"declare @name varchar(20) " +
"declare @mid varchar(16) " +
"declare @uploadhost int " +
"declare merch_cursor cursor for " +
" select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
" where mid = ? " +
" and a.uploadhostid = ? " +
" and a.uploadhostid = b.uploadhostid " +
"open merch_cursor " +
" if @@ERROR > 0 " +
" return; " +
" fetch next from merch_cursor into @tid, @name " +
" while @@FETCH_STATUS = 0 " +
" begin " +
" insert into dbo.MERCHBINS_TEST (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX,gracemin,gracemax,allowed, AMOUNTMIN, AMOUNTMAX) " +
" values (@TID, @name, '4', '6', " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" 0, 99, 'Y', 0 , 999999999) " +
" insert into dbo.MERCHBINS_TEST (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX,gracemin,gracemax,allowed, AMOUNTMIN, AMOUNTMAX) " +
" values (@TID, @name, '9', '9', " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" 0, 99, 'Y', 0 , 999999999) " +
" insert into dbo.MERCHBINS_TEST (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX,gracemin,gracemax,allowed, AMOUNTMIN, AMOUNTMAX) " +
" values (@TID, @name, '222100', '272099', " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" 0, 99, 'Y', 0 , 999999999) " +
" fetch next from merch_cursor " +
" into @tid, @name " +
" end " +
" CLOSE merch_cursor " +
" deallocate merch_cursor ";
comCursor.Parameters.Add("@mid", OdbcType.VarChar, 16).Value = midDescr.Text;
comCursor.Parameters.Add("@uploadhost", OdbcType.Int).Value = chkOriginID2;
OdbcDataReader readerCursor2 = comCursor.ExecuteReader();
readerCursor2.Close();
}
else if (chkOrigin == "MEALSANDMORE")
{
comCursor.CommandText = "declare @tid varchar(16) " +
"declare @name varchar(20) " +
"declare @mid varchar(16) " +
"declare @uploadhost int " +
"declare merch_cursor cursor for " +
" select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
" where mid = ? " +
" and a.uploadhostid = ? " +
" and a.uploadhostid = b.uploadhostid " +
"open merch_cursor " +
" if @@ERROR > 0 " +
" return " +
" fetch next from merch_cursor into @tid, @name " +
" while @@FETCH_STATUS = 0 " +
" begin " +
" insert into dbo.MERCHBINS_TEST (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX,gracemin,gracemax,allowed, AMOUNTMIN, AMOUNTMAX) " +
" values (@TID, @name, '502259', '502259', " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" 0, 99, 'Y', 0 , 999999999) " +
" fetch next from merch_cursor " +
" into @tid, @name " +
" end " +
" CLOSE merch_cursor " +
" deallocate merch_cursor ";
comCursor.Parameters.Add("@mid", OdbcType.VarChar, 16).Value = midDescr.Text;
comCursor.Parameters.Add("@uploadhost", OdbcType.Int).Value = chkOriginID2;
OdbcDataReader readerCursor3 = comCursor.ExecuteReader();
readerCursor3.Close();
}
else if (chkOrigin == "TICKETRESTAURANT")
{
comCursor.CommandText = "declare @tid varchar(16) " +
"declare @name varchar(20) " +
"declare @mid varchar(16) " +
"declare @uploadhost int " +
"declare merch_cursor cursor for " +
" select TID, uploadhostname from dbo.MERCHANTS_test a, dbo.uploadhosts b " +
" where @mid = ? " +
" and @a.uploadhostid = ? " +
" and a.uploadhostid = b.uploadhostid " +
"open merch_cursor " +
" if @@ERROR > 0 " +
" return " +
" fetch next from merch_cursor into @tid, @name " +
" while @@FETCH_STATUS = 0 " +
" begin " +
" insert into dbo.MERCHBINS_TEST (TID, DESTPORT, BINLOWER, BINUPPER, INSTMIN, INSTMAX,gracemin,gracemax,allowed, AMOUNTMIN, AMOUNTMAX) " +
" values (@TID, @name, '534228', '534228', " +
textBoxFr.Text.ToString() + "," + textBoxTo.Text.ToString() + "," +
" 0, 99, 'Y', 0 , 999999999) " +
" fetch next from merch_cursor " +
" into @tid, @name " +
" end " +
" CLOSE merch_cursor " +
" deallocate merch_cursor ";
comCursor.Parameters.Add("@mid", OdbcType.VarChar, 16).Value = midDescr.Text;
comCursor.Parameters.Add("@uploadhost", OdbcType.Int).Value = chkOriginID2;
OdbcDataReader readerCursor4 = comCursor.ExecuteReader();
readerCursor4.Close();
}
//}
}
MessageBox.Show("Insert completed!");
}
private void Btn_agregar_Click(object sender, EventArgs e)
{
if (Cbo_equiposLocales.SelectedIndex == Cbo_equiposVisitantes.SelectedIndex)
{
MessageBox.Show("No puede colocar a un equipo a jugar contra si mismo", "Error");
}
else
{
bool bEquipo1, bEquipo2;
bEquipo1 = verificarEquipoLocal();
bEquipo2 = verificarEquipoVisitante();
if (bEquipo1 || bEquipo2)
{
if (bEquipo1)
{
MessageBox.Show("El equipo " + Cbo_equiposLocales.Text + " ya cuenta con un partido programado en este campeonato y jornada", "Error");
}
if (bEquipo2)
{
MessageBox.Show("El equipo " + Cbo_equiposVisitantes.Text + " ya cuenta con un partido programado en este campeonato y jornada", "Error");
}
}
else
{
bool bCampoOcupado;
bCampoOcupado = verificarCampoOcupado();
if (bCampoOcupado)
{
MessageBox.Show("El campo ya se encuentra ocupado por otro partido", "Error");
}
else
{
//Insertar partido_encabezado
bool ingresoCorrecto = true;
try
{
OdbcCommand cmd = new OdbcCommand();
cmd = new OdbcCommand("INSERT INTO tbl_partido_encabezado(PK_idCampeonato, PK_idJornada, FK_idCampo, fecha_partido, hora_partido, estado_partido) VALUES (" +
lIdCampeonato[Cbo_campeonatos.SelectedIndex] + " , " +
lIdJornada[Cbo_jornadas.SelectedIndex] + " , " +
lIdCampo[Cbo_campos.SelectedIndex] + " , '" +
Dtp_fecha.Text + "' , '" +
Dtp_hora.Text + "' , " +
"1" + ")"
, nueva.conectar());
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message);
ingresoCorrecto = false;
}
if (ingresoCorrecto)
{
bool bIngresoCorrectoDetalle = true;
int iIdPartido = 0;
try
{
OdbcCommand sql = new OdbcCommand("Select PK_idPartido FROM tbl_partido_encabezado ORDER BY PK_idPartido DESC LIMIT 1"
, nueva.conectar());
OdbcDataReader almacena = sql.ExecuteReader();
while (almacena.Read() == true)
{
iIdPartido = almacena.GetInt32(0);
}
almacena.Close();
sql.Connection.Close();
//Insertar partido_detalle;
OdbcCommand cmd = new OdbcCommand("INSERT INTO tbl_partido_detalle(PK_idPartido, PK_idEquipo) VALUES (" +
iIdPartido.ToString() + " , " +
lidEquipoLocal[Cbo_equiposLocales.SelectedIndex] + ")"
, nueva.conectar());
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd = new OdbcCommand("INSERT INTO tbl_partido_detalle(PK_idPartido, PK_idEquipo) VALUES (" +
iIdPartido.ToString() + " , " +
lIdEquipoVisitante[Cbo_equiposVisitantes.SelectedIndex] + ")"
, nueva.conectar());
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message);
bIngresoCorrectoDetalle = false;
}
if (bIngresoCorrectoDetalle)
{
MessageBox.Show("Partido programado correctamente", "Exito");
}
//REGISTRO BITACORA MANTENIMIENTOS-----------------------------------------
Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Programó Partido Manualmente", "tbl_calendario_encabeza", frm_login.IP[2].ToString());
}
}
}
}
llenarDataGrid();
}
// get account data for a certain period
public override void GetAccounts(int iFiscalYear)
{
// make sure stores 2-digit year number
if (iFiscalYear > 2000)
{
iFiscalYear -= 2000;
}
this.iFiscalYear = iFiscalYear;
// account map
Dictionary <int, ExcoAccount> accountMap = new Dictionary <int, ExcoAccount>();
#region plant 41
// build query string for the given year
string query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt01+aj4ob01, aj4tt02+aj4ob02-(aj4tt01+aj4ob01), aj4tt03+aj4ob03-(aj4tt02+aj4ob02), aj4tt04+aj4ob04-(aj4tt03+aj4ob03), aj4tt05+aj4ob05-(aj4tt04+aj4ob04), aj4tt06+aj4ob06-(aj4tt05+aj4ob05), aj4tt07+aj4ob07-(aj4tt06+aj4ob06), aj4tt08+aj4ob08-(aj4tt07+aj4ob07), aj4tt09+aj4ob09-(aj4tt08+aj4ob08), aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4tt11+aj4ob11-(aj4tt10+aj4ob10), aj4tt12+aj4ob12-(aj4tt11+aj4ob11), aj4cb01, aj4cb02, aj4cb03, aj4cb04, aj4cb05, aj4cb06, aj4cb07, aj4cb08, aj4cb09, aj4cb10, aj4cb11, aj4cb12 from cmsdat.glmt left join cmsdat.mast on aj4comp=41 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + iFiscalYear;
// run query
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
OdbcDataReader reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader["aj4gl#1"]) % 100;
int iGLNo2 = Convert.ToInt32(reader["aj4gl#2"]);
string sTitle = reader["aztitl"].ToString();
int iYear = Convert.ToInt32(reader["aj4ccyy"]) - 2000;
// determine if a mapped account
ExcoAccount account;
bool bIsNewAccount = false;
int iKey = iGLNo1 * 1000000 + iGLNo2;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 12; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual41[i] = new ExcoMoney(calendar, Convert.ToDouble(reader["aj4tt" + (i + 1).ToString("D2")]), sCurrency);
account.budget41[i] = new ExcoMoney(calendar, Convert.ToDouble(reader["aj4cb" + (i + 1).ToString("D2")]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
#endregion
#region plant 49
// build query string for the given year
query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt01+aj4ob01, aj4tt02+aj4ob02-(aj4tt01+aj4ob01), aj4tt03+aj4ob03-(aj4tt02+aj4ob02), aj4tt04+aj4ob04-(aj4tt03+aj4ob03), aj4tt05+aj4ob05-(aj4tt04+aj4ob04), aj4tt06+aj4ob06-(aj4tt05+aj4ob05), aj4tt07+aj4ob07-(aj4tt06+aj4ob06), aj4tt08+aj4ob08-(aj4tt07+aj4ob07), aj4tt09+aj4ob09-(aj4tt08+aj4ob08), aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4tt11+aj4ob11-(aj4tt10+aj4ob10), aj4tt12+aj4ob12-(aj4tt11+aj4ob11), aj4cb01, aj4cb02, aj4cb03, aj4cb04, aj4cb05, aj4cb06, aj4cb07, aj4cb08, aj4cb09, aj4cb10, aj4cb11, aj4cb12 from cmsdat.glmt left join cmsdat.mast on aj4comp=49 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + iFiscalYear;
// run query
reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader["aj4gl#1"]) % 100;
int iGLNo2 = Convert.ToInt32(reader["aj4gl#2"]);
string sTitle = reader["aztitl"].ToString();
int iYear = Convert.ToInt32(reader["aj4ccyy"]) - 2000;
// determine if a mapped account
ExcoAccount account;
int iKey = iGLNo1 * 1000000 + iGLNo2;
bool bIsNewAccount = false;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 12; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual49[i] = new ExcoMoney(calendar, Convert.ToDouble(reader["aj4tt" + (i + 1).ToString("D2")]), sCurrency);
account.budget49[i] = new ExcoMoney(calendar, Convert.ToDouble(reader["aj4cb" + (i + 1).ToString("D2")]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
#endregion
#region plant 04
// build query string for the given year
query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt01+aj4ob01, aj4tt02+aj4ob02-(aj4tt01+aj4ob01), aj4tt03+aj4ob03-(aj4tt02+aj4ob02), aj4tt04+aj4ob04-(aj4tt03+aj4ob03), aj4tt05+aj4ob05-(aj4tt04+aj4ob04), aj4tt06+aj4ob06-(aj4tt05+aj4ob05), aj4tt07+aj4ob07-(aj4tt06+aj4ob06), aj4tt08+aj4ob08-(aj4tt07+aj4ob07), aj4tt09+aj4ob09-(aj4tt08+aj4ob08), aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4cb01, aj4cb02, aj4cb03, aj4cb04, aj4cb05, aj4cb06, aj4cb07, aj4cb08, aj4cb09, aj4cb10 from cmsdat.glmt left join cmsdat.mast on aj4comp=4 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + iFiscalYear;
// run query
reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader[1]);
int iGLNo2 = Convert.ToInt32(reader[2]);
string sTitle = reader[3].ToString();
int iYear = Convert.ToInt32(reader[0]) - 2000;
// determine if this is a mapped account
ExcoAccount account;
int iKey = iGLNo1 * 1000000 + iGLNo2;
bool bIsNewAccount = false;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 9; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual04[i + 3] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 4]), sCurrency);
account.budget04[i + 3] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 14]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
// build query string for the previous year
query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4tt11+aj4ob11-(aj4tt10+aj4ob10), aj4tt12+aj4ob12-(aj4tt11+aj4ob11), aj4cb10, aj4cb11, aj4cb12 from cmsdat.glmt left join cmsdat.mast on aj4comp=4 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + (iFiscalYear - 1);
// run query
reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader[1]);
int iGLNo2 = Convert.ToInt32(reader[2]);
string sTitle = reader[3].ToString();
int iYear = Convert.ToInt32(reader[0]) - 2000;
// determine if this is a mapped account
ExcoAccount account;
int iKey = iGLNo1 * 1000000 + iGLNo2;
bool bIsNewAccount = false;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 3; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual04[i] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 4]), sCurrency);
account.budget04[i] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 7]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
#endregion
#region plant 48
// build query string for the given year
query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt01+aj4ob01, aj4tt02+aj4ob02-(aj4tt01+aj4ob01), aj4tt03+aj4ob03-(aj4tt02+aj4ob02), aj4tt04+aj4ob04-(aj4tt03+aj4ob03), aj4tt05+aj4ob05-(aj4tt04+aj4ob04), aj4tt06+aj4ob06-(aj4tt05+aj4ob05), aj4tt07+aj4ob07-(aj4tt06+aj4ob06), aj4tt08+aj4ob08-(aj4tt07+aj4ob07), aj4tt09+aj4ob09-(aj4tt08+aj4ob08), aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4cb01, aj4cb02, aj4cb03, aj4cb04, aj4cb05, aj4cb06, aj4cb07, aj4cb08, aj4cb09, aj4cb10 from cmsdat.glmt left join cmsdat.mast on aj4comp=48 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + iFiscalYear;
// run query
reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader[1]);
int iGLNo2 = Convert.ToInt32(reader[2]);
string sTitle = reader[3].ToString();
int iYear = Convert.ToInt32(reader[0]) - 2000;
// determine if this is a mapped account
ExcoAccount account;
int iKey = iGLNo1 * 1000000 + iGLNo2;
bool bIsNewAccount = false;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 9; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual48[i + 3] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 4]), sCurrency);
account.budget48[i + 3] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 14]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
// build query string for the previous year
query = "select aj4ccyy, aj4gl#1, aj4gl#2, aztitl, aj4tt10+aj4ob10-(aj4tt09+aj4ob09), aj4tt11+aj4ob11-(aj4tt10+aj4ob10), aj4tt12+aj4ob12-(aj4tt11+aj4ob11), aj4cb10, aj4cb11, aj4cb12 from cmsdat.glmt left join cmsdat.mast on aj4comp=48 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#1>400 and aj4gl#2=azgl#2 and aj4ccyy=20" + (iFiscalYear - 1);
// run query
reader = database.RunQuery(query);
while (reader.Read())
{
int iGLNo1 = Convert.ToInt32(reader[1]);
int iGLNo2 = Convert.ToInt32(reader[2]);
string sTitle = reader[3].ToString();
int iYear = Convert.ToInt32(reader[0]) - 2000;
// determine if this is a mapped account
ExcoAccount account;
int iKey = iGLNo1 * 1000000 + iGLNo2;
bool bIsNewAccount = false;
if (99999999 == iKey)
{
continue;
}
if (accountMap.ContainsKey(iKey))
{
account = accountMap[iKey];
}
else
{
account = new ExcoAccount(iGLNo1, iGLNo2, sTitle, iFiscalYear);
bIsNewAccount = true;
}
// get data
for (int i = 0; i < 3; i++)
{
ExcoCalendar calendar = new ExcoCalendar(iYear, i + 1, true, iPlantID);
account.actual48[i] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 4]), sCurrency);
account.budget48[i] = new ExcoMoney(calendar, Convert.ToDouble(reader[i + 7]), sCurrency);
}
// add to map if this is a new account
if (bIsNewAccount)
{
accountMap.Add(iKey, account);
}
}
reader.Close();
#endregion
}
internal void GetSchuelerMitBlauemBrief(string connectionStringAtlantis, string aktSj)
{
Schuelers schuelers = new Schuelers();
foreach (var person in this)
{
using (OdbcConnection connection = new OdbcConnection(connectionStringAtlantis))
{
connection.Open();
if (connection != null)
{
OdbcCommand command = connection.CreateCommand();
command.CommandText = @"SELECT
DBA.schue_sj.pu_id as ID,
DBA.schue_sj.s_jahrgang AS Jahrgang,
DBA.adresse.s_typ_adr as Typ,
DBA.klasse.klasse as Klasse,
DBA.schueler.name_1 as Vorname,
DBA.schueler.name_2 as Nachname,
DBA.adresse.name_2 AS EVorname,
DBA.adresse.name_1 AS ENachname,
DBA.schueler.dat_geburt as Geburtsdatum,
DBA.schueler.s_geschl as Geschlecht,
DBA.adresse.strasse AS Strasse,
DBA.adresse.plz AS Plz,
DBA.adresse.ort AS Ort,
DBA.adresse.sorge_berechtigt_jn,
DBA.adresse.s_anrede,
DBA.schueler.s_erzb_1_art,
DBA.schueler.s_erzb_2_art,
DBA.schueler.id_hauptadresse,
DBA.adresse.hauptadresse_jn,
DBA.adresse.anrede_text,
DBA.schueler.anrede_text,
DBA.adresse.name_3,
DBA.adresse.plz_postfach as PlzPostfach,
DBA.adresse.postfach as Postfach,
DBA.adresse.s_titel_ad,
DBA.adresse.s_sorgerecht,
DBA.adresse.brief_adresse,
DBA.schue_sj.kl_id,
DBA.adresse.s_famstand_adr
FROM((DBA.schue_sj JOIN DBA.klasse ON DBA.schue_sj.kl_id = DBA.klasse.kl_id) JOIN DBA.schueler ON DBA.schue_sj.pu_id = DBA.schueler.pu_id) JOIN DBA.adresse ON DBA.schueler.pu_id = DBA.adresse.pu_id
WHERE vorgang_schuljahr = '" + aktSj + @"' AND schue_sj.pu_id = " + person.SchlüsselExtern + ";";
OdbcDataReader reader = command.ExecuteReader();
int fCount = reader.FieldCount;
while (reader.Read())
{
var idAtlantis = Convert.ToInt32(reader.GetValue(0));
var jahrgang = reader.GetValue(1).ToString();
string typ = reader.GetValue(2).ToString(); // 0 = Schüler V = Vater M = Mutter
var klasse = reader.GetValue(3).ToString();
var vorname = reader.GetValue(5).ToString();
var nachname = reader.GetValue(4).ToString();
var evorname = reader.GetValue(7).ToString();
var enachname = reader.GetValue(6).ToString();
var geburtsdatum = DateTime.ParseExact(reader.GetValue(8).ToString(), "dd.MM.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
var volljaehrig = geburtsdatum.AddYears(18) > DateTime.Now ? false : true;
var geschlecht34 = reader.GetValue(9).ToString();
var geschlechtMw = geschlecht34 == "3" ? "m" : "w";
var strasse = reader.GetValue(10).ToString();
var plz = reader.GetValue(11).ToString();
var ort = reader.GetValue(12).ToString();
var sorgeberechtigtJn = reader.GetValue(13).ToString();
var anrede = reader.GetValue(14).ToString();
var fachs = (from t in this where t.SchlüsselExtern == idAtlantis select t.Fach).ToList();
if (volljaehrig)
{
Console.WriteLine("Der Schüler " + vorname + " " + nachname + " (Klasse:" + klasse + ") soll gemahnt werden, obwohl er volljährig ist. Der Schüler wird ignoriert.");
}
else
{
if (!jahrgang.EndsWith("1"))
{
Console.WriteLine("Der Schüler " + vorname + " " + nachname + " (Klasse:" + klasse + ") soll gemahnt werden, obwohl er nicht im ersten Jahrgang ist. Das wird ignoriert.");
}
else
{
schuelers.Add(new Schueler(idAtlantis, typ, klasse, jahrgang, nachname, vorname, evorname, enachname, geburtsdatum, volljaehrig, geschlechtMw, sorgeberechtigtJn, anrede, plz, ort, strasse, fachs));
}
}
}
reader.Close();
command.Dispose();
}
}
}
try
{
foreach (var schueler in (from s in schuelers select s.IdAtlantis).Distinct())
{
using (StreamWriter outputFile = new StreamWriter(@"C:\Users\bm\Berufskolleg Borken\Terminplanung - Documents\Blaue Briefe\Steuerdatei.csv"))
{
outputFile.WriteLine("Anrede,Klasse,Nachname,Vorname,ENachname,EVorname,Strasse,Plz,Ort,SorgeberechtigtJn,Jahrgang,Fach1,Fach2,Fach3,Fach4");
foreach (var erziehungsberechtigter in (from e in schuelers where e.IdAtlantis == schueler where e.Typ != "0" select e).ToList())
{
outputFile.WriteLine(
erziehungsberechtigter.Anrede + "," +
erziehungsberechtigter.Klasse + "," +
erziehungsberechtigter.Nachname + "," +
erziehungsberechtigter.Vorname + "," +
erziehungsberechtigter.ENachname + "," +
erziehungsberechtigter.EVorname + "," +
erziehungsberechtigter.Strasse + "," +
erziehungsberechtigter.Plz + "," +
erziehungsberechtigter.Ort + "," +
erziehungsberechtigter.SorgeberechtigtJn + "," +
erziehungsberechtigter.Jahrgang + "," +
RenderFachs(erziehungsberechtigter.Fachs)
);
}
}
EditorOeffnen(@"C:\Users\bm\Berufskolleg Borken\Terminplanung - Documents\Blaue Briefe\Steuerdatei.csv");
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
Console.ReadKey();
}
}
// get piece's info
public void GetInfo(Dictionary <string, Dictionary <int, SeqTemplate> > seqTemplateMap, Dictionary <string, Scrap> scrapMap, Dictionary <int, List <Task> > sparkMap, Dictionary <int, List <Task> > wireMap, DateTime date)
{
if (!seqTemplateMap.ContainsKey(wo))
{
return;
}
Dictionary <int, SeqTemplate> seqMap = seqTemplateMap[wo];
foreach (SeqTemplate seqTemplate in seqMap.Values)
{
Seq seq = new Seq(seqTemplate.seqNum, seqTemplate.runtime, type, part, wo, so);
// get description based on sequence number
seq.GetSeqInfo(sparkMap, wireMap, date);
seq.runtimeList[0].seqNum = seq.seqNum;
seq.runtimeList[0].part = part;
seq.runtimeList[0].woNum = wo;
seq.runtimeList[0].soNum = so;
seq.runtimeList[0].type = type;
seq.runtimeList[0].stdTime = seq.stdTime;
seqList.Add(seq);
}
// check scrap
if (scrapMap.ContainsKey(wo))
{
Scrap scrap = scrapMap[wo];
foreach (Seq seq in seqList)
{
if (seq.seqNum <= scrap.seq)
{
RunTime runtime = seq.runtimeList[seq.runtimeList.Count - 1].DeepCopy();
runtime.taskList.Clear();
seq.runtimeList.Add(runtime);
}
}
}
// get solarsoft time
ExcoODBC odbcConnection = ExcoODBC.Instance;
odbcConnection.Open(Database.CMSDAT);
foreach (Seq seq in seqList)
{
if (seq.seqNum != 320 && seq.seqNum != 320 && seq.seqNum != 330 && seq.seqNum != 340 && seq.seqNum != 350 && seq.seqNum != 360)
{
string query = "select fwdept, fwresc, fwtime, fwdate, fwqtyc from cmsdat.jcsta where fwjobn='" + wo + "' and fwoseq=" + seq.seqNum.ToString() + " order by fwdate";
OdbcDataReader reader = odbcConnection.RunQuery(query);
int i = 0;
while (reader.Read())
{
seq.runtimeList[i].quantity += Convert.ToInt32(reader["fwqtyc"]);
seq.runtimeList[i].seqNum = seq.seqNum;
seq.runtimeList[i].part = part;
seq.runtimeList[i].woNum = wo;
seq.runtimeList[i].empNum = 99999;
seq.runtimeList[i].depCode = reader["fwdept"].ToString().Trim().Replace("001", "");;
seq.runtimeList[i].resCode = reader["fwresc"].ToString().Trim();;
seq.runtimeList[i].soNum = so;
seq.runtimeList[i].stdTime = seq.stdTime;
seq.runtimeList[i].slsTime += Convert.ToDouble(reader["fwtime"]);
seq.runtimeList[i].slsDate = Convert.ToDateTime(reader["fwdate"].ToString().Trim());
seq.runtimeList[i].hasSolarsoftTime = true;
if (seq.runtimeList.Count > i + 1)
{
i++;
}
}
reader.Close();
// adjust quantity when meets scrap
if (seq.runtimeList.Count > 1)
{
if (seq.runtimeList.Count > 1 && seq.runtimeList[0].quantity > 1)
{
if (seq.runtimeList[1].quantity < 1)
{
seq.runtimeList[0].quantity = 1;
seq.runtimeList[1].quantity = 1;
}
}
}
}
else
{
if (!seqTemplateMap[seq.woNum].ContainsKey(seq.seqNum))
{
throw new Exception("failed to find sequence number " + seq.seqNum.ToString() + " in sequence template " + wo);
}
seq.runtimeList[0].quantity = seqTemplateMap[seq.woNum][seq.seqNum].compQuant;
seq.runtimeList[0].seqNum = seq.seqNum;
seq.runtimeList[0].part = part;
seq.runtimeList[0].woNum = wo;
seq.runtimeList[0].soNum = so;
seq.runtimeList[0].stdTime = seq.stdTime;
if (seq.runtimeList[0].quantity > 0)
{
seq.runtimeList[0].slsTime = seq.runtimeList[0].stdTime;
seq.runtimeList[0].hasSolarsoftTime = true;
}
}
}
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_FuncApplNotes.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="extendedCriteria">Statement appended to the end of the WHERE-clause</param>
/// <param name="extentedParameters">Values bound to the query (?) marks in <code>extendedCriteria</code></param>
/// <param name="extendedSortOrder">Statement appended to the end of the ORDER BY-clause</param>
/// <param name="BusRoleID"></param>
/// <returns>returnListFuncAppl_NotesByBusRole[]</returns>
public returnListFuncAppl_NotesByBusRole[] ListFuncAppl_NotesByBusRole(int?maxRowsToReturn, string extendedCriteria, string[] extendedParameters, string extendedSortOrder, int BusRoleID)
{
returnListFuncAppl_NotesByBusRole[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
string runTimeCriteria = (extendedCriteria != null ? extendedCriteria.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"refapplication\"", "\"c_u_REFapplication\"").Replace("\"comment\"", "\"c_u_Comment\"").Replace("\"busrole\"", "\"c_r_BusRole\"");
string runTimeSortOrder = (extendedSortOrder != null ? extendedSortOrder.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"refapplication\"", "\"c_u_REFapplication\"").Replace("\"comment\"", "\"c_u_Comment\"").Replace("\"busrole\"", "\"c_r_BusRole\"");
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_REFapplication\", \"c_u_Comment\", \"c_r_BusRole\" FROM \"t_RBSR_AUFW_u_FuncApplNotes\" WHERE \"FuncApplNotes\".\"@id\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_REFapplication\", \"c_u_Comment\", \"c_r_BusRole\" FROM \"t_RBSR_AUFW_u_FuncApplNotes\" WHERE \"FuncApplNotes\".\"@id\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder) + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_REFapplication\", \"c_u_Comment\", \"c_r_BusRole\" FROM \"t_RBSR_AUFW_u_FuncApplNotes\" WHERE \"FuncApplNotes\".\"@id\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
cmd.Parameters.Add("1_BusRoleID", OdbcType.Int);
cmd.Parameters["1_BusRoleID"].Value = (object)BusRoleID;
for (int i = 0; i < extendedParameters.Length; i++)
{
cmd.Parameters.Add("@ExtendedParam_" + i.ToString(), OdbcType.NVarChar);
cmd.Parameters["@ExtendedParam_" + i.ToString()].Value = (object)extendedParameters[i];
}
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListFuncAppl_NotesByBusRole> rvl = new List <returnListFuncAppl_NotesByBusRole>();
while (dr.Read())
{
returnListFuncAppl_NotesByBusRole cr = new returnListFuncAppl_NotesByBusRole();
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'REFapplication'");
}
else
{
cr.REFapplication = dr.GetInt32(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'Comment'");
}
else
{
cr.Comment = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'BusRoleID'");
}
else
{
cr.BusRoleID = dr.GetInt32(3);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_SAPauthField.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="extendedCriteria">Statement appended to the end of the WHERE-clause</param>
/// <param name="extentedParameters">Values bound to the query (?) marks in <code>extendedCriteria</code></param>
/// <param name="extendedSortOrder">Statement appended to the end of the ORDER BY-clause</param>
/// <returns>returnListSAPauthField[]</returns>
public returnListSAPauthField[] ListSAPauthField(int?maxRowsToReturn, string extendedCriteria, string[] extendedParameters, string extendedSortOrder)
{
returnListSAPauthField[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
string runTimeCriteria = (extendedCriteria != null ? extendedCriteria.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"name\"", "\"c_u_Name\"").Replace("\"description\"", "\"c_u_Description\"").Replace("\"status\"", "\"c_u_Status\"");
string runTimeSortOrder = (extendedSortOrder != null ? extendedSortOrder.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"name\"", "\"c_u_Name\"").Replace("\"description\"", "\"c_u_Description\"").Replace("\"status\"", "\"c_u_Status\"");
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_Name\", \"c_u_Description\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_SAPauthField\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Name\", \"c_u_Description\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_SAPauthField\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder) + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Name\", \"c_u_Description\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_SAPauthField\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
for (int i = 0; i < extendedParameters.Length; i++)
{
cmd.Parameters.Add("@ExtendedParam_" + i.ToString(), OdbcType.NVarChar);
cmd.Parameters["@ExtendedParam_" + i.ToString()].Value = (object)extendedParameters[i];
}
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListSAPauthField> rvl = new List <returnListSAPauthField>();
while (dr.Read())
{
returnListSAPauthField cr = new returnListSAPauthField();
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
else
{
cr.Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cr.Description = null;
}
else
{
cr.Description = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'Status'");
}
else
{
cr.Status = dr.GetString(3);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
public double GetMonthlyBudget(ExcoCustomer customer, int plant, ExcoCalendar calendar)
{
double value = 0.0;
try
{
if (1 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (3 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (5 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (4 == plant)
{
int year;
int period;
if (calendar.GetFiscalMonth() >= 10)
{
year = calendar.GetFiscalYear() + 1;
period = calendar.GetFiscalMonth() - 9;
}
else
{
year = calendar.GetFiscalYear();
period = calendar.GetFiscalMonth() + 3;
}
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + period.ToString("D2") + " from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + period.ToString("D2") + "from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + period.ToString("D2") + "from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
}
catch
{
return(0.0);
}
return(value);
}
public int MySQLquery(string[] sa, string MySQLcmd, int btn)
{
int r = 0;
OdbcCommand MyCommand = new OdbcCommand(MySQLcmd, this.MySQLconnection);
try
{
if (this.MySQLconnection.State == ConnectionState.Closed)
{
MessageBox.Show(StringsDictionary.KeyValue("FormDB_MySQLquery_closed", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_closed_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
#region if btn=1
if (btn == 1)
{
try
{
r = MyCommand.ExecuteNonQuery();
this.toolStripStatusLabel1.Text =
StringsDictionary.KeyValue("FormDB_insertrecord", this.culture);
return(r);
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion if btn=1
#region if btn=2
else if (btn == 2)
{
try
{
MyDataReader = MyCommand.ExecuteReader();
r = MyDataReader.RecordsAffected;
liste.Clear();
while (MyDataReader.Read())
{
liste.Add("prefix:\t\t " + MyDataReader.GetString(0) + "/" + MyDataReader.GetByte(1).ToString());
liste.Add("netname:\t " + MyDataReader.GetString(2));
liste.Add("person:\t\t " + MyDataReader.GetString(3));
liste.Add("organization:\t " + MyDataReader.GetString(4));
liste.Add("as-num:\t\t " + MyDataReader.GetString(5));
liste.Add("phone:\t\t " + MyDataReader.GetString(6));
liste.Add("email:\t\t " + MyDataReader.GetString(7));
liste.Add("status:\t\t " + MyDataReader.GetString(8));
liste.Add("created:\t\t " + MyDataReader.GetString(9));
liste.Add("last-updated:\t " + MyDataReader.GetString(10));
liste.Add("");
if (sa[0] != "")
{
this.textBox7.Text = MyDataReader.GetString(0) + "/" + MyDataReader.GetByte(1).ToString();
this.textBox1.Text = MyDataReader.GetString(2);
this.textBox2.Text = MyDataReader.GetString(3);
this.textBox3.Text = MyDataReader.GetString(4);
this.textBox6.Text = MyDataReader.GetString(5);
this.textBox4.Text = MyDataReader.GetString(6);
this.textBox5.Text = MyDataReader.GetString(7);
this.comboBox1.SelectedItem = MyDataReader.GetString(8);
}
}
this.listBox1.Items.AddRange(liste.ToArray());
MyDataReader.Close();
if (MyDataReader is IDisposable)
{
MyDataReader.Dispose();
}
return(r);
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion btn2
#region if btn=3
else if (btn == 3)
{
try
{
r = MyCommand.ExecuteNonQuery();
}
catch (System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}
#endregion btn=3
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(r);
}
/// <summary>
// This method will fetch the data from a SQL database. It will be passed a query or a sql file
/// that will contain the queries as a string. These queries will be performed on the database and the results
/// will be saved to dictionary.
/// </summary>
/// <param name="selectQuery">It is a string that may either be a SQL query or the SQL file name.
/// In case it is a query, it will be performed on the specified database.
/// In case it is name of the SQL file, it will be read and queries inside this file will be performed on the database.</param>
/// <returns>True on success else false</returns>
public static bool GetDataFromDatabase(string selectQuery, string databaseType)
{
//Create a new connection string based on type of database specified in Parameters.ini file
string connectionString = getDBConnectionString(databaseType);
//Get location where SQL queries are stored
string sqlQueryPath = Path.GetFullPath(Property.SqlQueryFilePath);
// Check whether given argument is a query or sql query file.
if (selectQuery.Split('.')[1].ToLower() == "sql")
{
try
{
StreamReader readFile = new StreamReader(Path.Combine(sqlQueryPath, selectQuery));
selectQuery = readFile.ReadToEnd();
readFile.Close();
}
catch (Exception exception)
{
Property.Remarks = Utility.GetCommonMsgVariable("KRYPTONERRCODE0026").Replace("{MSG}", exception.Message);
return(false);
}
}
try
{
//replace the variables in the query by their values.
selectQuery = Utility.ReplaceVariablesInString(selectQuery);
query = selectQuery;
ConnectionString = connectionString;
OdbcDataReader reader = null;
reader = executeQueryTillTimeout();
bool isData = false;
//now set new variables with fetched values from database.
#region Store retrieved data into variables
DataTable queryResults = new DataTable();
queryResults.Load(reader);
if (queryResults.Rows.Count > 0)
{
isData = true;
int randomNumber = Utility.RandomNumber(1, queryResults.Rows.Count);
DataRow finalRecord = queryResults.Rows[randomNumber - 1];
for (int i = 0; i < queryResults.Columns.Count; i++)
{
Utility.SetVariable(queryResults.Columns[i].ColumnName, finalRecord[i].ToString());
//If values are being retrieved, show all retrived values in comments column
Property.Remarks = Property.Remarks +
queryResults.Columns[i].ColumnName + ":=" + finalRecord[i].ToString() + "; ";
}
}
//This section is replaced by section above in order to store possible unique values
#endregion
Property.Remarks = Property.Remarks + " Sql Query Used: " + selectQuery;
comm.Dispose();
reader.Close();
conn.Close();
if (isData == false)
{
Property.Remarks = "No data could be retrieved from database, please check sql query" + "." + "\n" +
"Connection String: " + connectionString + "\n" +
"Sql Query Used: " + selectQuery;
return(false);
}
}
catch (TimeoutException)
{
Property.Remarks = Utility.GetCommonMsgVariable("KRYPTONERRCODE0058").Replace("{MSG}", errorMessage);
return(false);
}
catch (Exception exception)
{
Property.Remarks = Utility.GetCommonMsgVariable("KRYPTONERRCODE0027").Replace("{MSG}", exception.Message);
Property.Remarks = Property.Remarks + " Sql Query used : " + selectQuery;
return(false);
}
return(true);
}
public void Run()
{
List <Invoice> invoiceList = new List <Invoice>();
ExcoCalendar calendar = new ExcoCalendar(DateTime.Now.Year - 2000, DateTime.Now.Month, false, 5);
// build connection
string query = "select dhincr, dhinv#, dhidat, dhbnam, dhbcs#, dhord#, dhterr, dhcurr, dhpost, dhtoti from cmsdat.oih where dhplnt='005' and (dharyr=" + calendar.GetFiscalYear().ToString() + " or (dhidat>='2012-10-01' and dharyr=0 and dharpr=0)) order by dhinv#";
ExcoODBC solarsoft = ExcoODBC.Instance;
solarsoft.Open(Database.CMSDAT);
OdbcDataReader reader = solarsoft.RunQuery(query);
// get main data
while (reader.Read())
{
Invoice invoice = new Invoice();
invoice.invoiceNumber = Convert.ToInt32(reader["dhinv#"]);
invoice.creditNote = reader["dhincr"].ToString().Trim();
invoice.invoiceDate = Convert.ToDateTime(reader["dhidat"]);
invoice.salesOrder = reader["dhord#"].ToString().Trim();
invoice.customerName = reader["dhbnam"].ToString().Trim();
invoice.customerID = reader["dhbcs#"].ToString().Trim();
invoice.currency = reader["dhcurr"].ToString().Trim();
if (string.Empty == reader["dhtoti"].ToString())
{
invoice.invoiceTotal = 0.0;
}
else
{
invoice.invoiceTotal = Convert.ToDouble(reader["dhtoti"]);
}
invoice.posted = reader["dhpost"].ToString().Trim();
invoiceList.Add(invoice);
}
reader.Close();
// die sale
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
query = "select sum(dipric*diqtsp) as sale from cmsdat.oid where diglcd='SAL' and diinv#=" + invoice.invoiceNumber.ToString();
reader = solarsoft.RunQuery(query);
if (reader.Read() && string.Empty != reader["sale"].ToString())
{
invoice.dieSale = Convert.ToDouble(reader["sale"]);
}
else
{
invoice.dieSale = 0.0;
}
reader.Close();
invoiceList[i] = invoice;
}
// discount
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
query = "select sum(fldext) as value from cmsdat.ois where flinv#=" + invoice.invoiceNumber.ToString() + " and (fldisc like 'D%' or fldisc like 'M%')";
reader = solarsoft.RunQuery(query);
if (reader.Read() && string.Empty != reader["value"].ToString())
{
invoice.discount = Convert.ToDouble(reader["value"]);
}
else
{
invoice.discount = 0.0;
}
reader.Close();
invoiceList[i] = invoice;
}
// fast track
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
query = "select sum(fldext) as value from cmsdat.ois where flinv#=" + invoice.invoiceNumber.ToString() + " and fldisc like 'F%'";
reader = solarsoft.RunQuery(query);
if (reader.Read() && string.Empty != reader["value"].ToString())
{
invoice.fastTrack = Convert.ToDouble(reader["value"]);
}
else
{
invoice.fastTrack = 0.0;
}
reader.Close();
invoiceList[i] = invoice;
}
// frieght
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
query = "select sum(dipric*diqtsp) as frt from cmsdat.oid where diglcd='FRT' and diinv#=" + invoice.invoiceNumber.ToString();
reader = solarsoft.RunQuery(query);
if (reader.Read() && string.Empty != reader["frt"].ToString())
{
invoice.freight = Convert.ToDouble(reader["frt"]);
}
else
{
invoice.freight = 0.0;
}
reader.Close();
invoiceList[i] = invoice;
}
// surcharge
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
query = "select sum(fldext) as value from cmsdat.ois where flinv#=" + invoice.invoiceNumber.ToString() + " and (fldisc like 'S%' or fldisc like 'P%')";
reader = solarsoft.RunQuery(query);
if (reader.Read() && string.Empty != reader["value"].ToString())
{
invoice.surcharge = Convert.ToDouble(reader["value"]);
}
else
{
invoice.surcharge = 0.0;
}
reader.Close();
invoiceList[i] = invoice;
}
// check missing invoices
int prevInvoice = 0;
for (int i = 1; i < invoiceList.Count; i++)
{
if (0 == prevInvoice)
{
prevInvoice = invoiceList[i].invoiceNumber;
}
else
{
prevInvoice++;
while (invoiceList[i].invoiceNumber != prevInvoice)
{
Invoice emptyInvoice = new Invoice();
emptyInvoice.invoiceNumber = prevInvoice;
// check if posted to other year
query = "select dhidat from cmsdat.oih where dhinv#=" + prevInvoice.ToString();
reader = solarsoft.RunQuery(query);
if (reader.Read())
{
emptyInvoice.invoiceDate = Convert.ToDateTime(reader["dhidat"]);
}
reader.Close();
invoiceList.Insert(i, emptyInvoice);
prevInvoice++;
i++;
}
}
}
// process into monthly lists
List <Invoice> janList = new List <Invoice>();
List <Invoice> febList = new List <Invoice>();
List <Invoice> marList = new List <Invoice>();
List <Invoice> aprList = new List <Invoice>();
List <Invoice> mayList = new List <Invoice>();
List <Invoice> junList = new List <Invoice>();
List <Invoice> julList = new List <Invoice>();
List <Invoice> augList = new List <Invoice>();
List <Invoice> sepList = new List <Invoice>();
List <Invoice> octList = new List <Invoice>();
List <Invoice> novList = new List <Invoice>();
List <Invoice> decList = new List <Invoice>();
int list = 0;
for (int i = 0; i < invoiceList.Count; i++)
{
Invoice invoice = invoiceList[i];
// empty invoice
if (null == invoice.creditNote)
{
switch (list)
{
case 10:
janList.Add(invoice);
break;
case 20:
febList.Add(invoice);
break;
case 30:
marList.Add(invoice);
break;
case 40:
aprList.Add(invoice);
break;
case 50:
mayList.Add(invoice);
break;
case 60:
junList.Add(invoice);
break;
case 70:
julList.Add(invoice);
break;
case 80:
augList.Add(invoice);
break;
case 90:
sepList.Add(invoice);
break;
case 100:
octList.Add(invoice);
break;
case 110:
novList.Add(invoice);
break;
case 120:
decList.Add(invoice);
break;
}
}
else
{
// valid invoice
if (1 == invoice.invoiceDate.Month)
{
janList.Add(invoice);
list = 10;
}
else if (2 == invoice.invoiceDate.Month)
{
febList.Add(invoice);
list = 20;
}
else if (3 == invoice.invoiceDate.Month)
{
marList.Add(invoice);
list = 30;
}
else if (4 == invoice.invoiceDate.Month)
{
aprList.Add(invoice);
list = 40;
}
else if (5 == invoice.invoiceDate.Month)
{
mayList.Add(invoice);
list = 50;
}
else if (6 == invoice.invoiceDate.Month)
{
junList.Add(invoice);
list = 60;
}
else if (7 == invoice.invoiceDate.Month)
{
julList.Add(invoice);
list = 70;
}
else if (8 == invoice.invoiceDate.Month)
{
augList.Add(invoice);
list = 80;
}
else if (9 == invoice.invoiceDate.Month)
{
sepList.Add(invoice);
list = 90;
}
else if (10 == invoice.invoiceDate.Month)
{
octList.Add(invoice);
list = 100;
}
else if (11 == invoice.invoiceDate.Month)
{
novList.Add(invoice);
list = 110;
}
else if (12 == invoice.invoiceDate.Month)
{
decList.Add(invoice);
list = 120;
}
}
}
// write to excel
// create excel
Excel.Application excel = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
Excel.Workbook book = excel.Workbooks.Add(misValue);
Excel.Worksheet julSheet = book.Worksheets[1];
julSheet.Name = "July " + calendar.GetCalendarYear().ToString();
Excel.Worksheet augSheet = book.Worksheets[2];
augSheet.Name = "August " + calendar.GetCalendarYear().ToString();
Excel.Worksheet sepSheet = book.Worksheets[3];
sepSheet.Name = "September " + calendar.GetCalendarYear().ToString();
Excel.Worksheet junSheet = book.Worksheets.Add();
junSheet.Name = "June " + calendar.GetCalendarYear().ToString();
Excel.Worksheet maySheet = book.Worksheets.Add();
maySheet.Name = "May " + calendar.GetCalendarYear().ToString();
Excel.Worksheet aprSheet = book.Worksheets.Add();
aprSheet.Name = "April " + calendar.GetCalendarYear().ToString();
Excel.Worksheet marSheet = book.Worksheets.Add();
marSheet.Name = "March " + calendar.GetCalendarYear().ToString();
Excel.Worksheet febSheet = book.Worksheets.Add();
febSheet.Name = "Feburary " + calendar.GetCalendarYear().ToString();
Excel.Worksheet janSheet = book.Worksheets.Add();
janSheet.Name = "January " + calendar.GetCalendarYear().ToString();
Excel.Worksheet decSheet = book.Worksheets.Add();
decSheet.Name = "December " + calendar.GetCalendarYear().ToString();
Excel.Worksheet novSheet = book.Worksheets.Add();
novSheet.Name = "November " + calendar.GetCalendarYear().ToString();
Excel.Worksheet octSheet = book.Worksheets.Add();
octSheet.Name = "October " + calendar.GetCalendarYear().ToString();
// fill sheet
FillSheet(1, janSheet, janList);
FillSheet(2, febSheet, febList);
FillSheet(3, marSheet, marList);
FillSheet(4, aprSheet, aprList);
FillSheet(5, maySheet, mayList);
FillSheet(6, junSheet, junList);
FillSheet(7, julSheet, julList);
FillSheet(8, augSheet, augList);
FillSheet(9, sepSheet, sepList);
FillSheet(10, octSheet, octList);
FillSheet(11, novSheet, novList);
FillSheet(12, decSheet, decList);
// select active sheet
if (1 == DateTime.Now.Month)
{
janSheet.Select();
}
else if (2 == DateTime.Now.Month)
{
febSheet.Select();
}
else if (3 == DateTime.Now.Month)
{
marSheet.Select();
}
else if (4 == DateTime.Now.Month)
{
aprSheet.Select();
}
else if (5 == DateTime.Now.Month)
{
maySheet.Select();
}
else if (6 == DateTime.Now.Month)
{
junSheet.Select();
}
else if (7 == DateTime.Now.Month)
{
julSheet.Select();
}
else if (8 == DateTime.Now.Month)
{
augSheet.Select();
}
else if (9 == DateTime.Now.Month)
{
sepSheet.Select();
}
else if (10 == DateTime.Now.Month)
{
octSheet.Select();
}
else if (11 == DateTime.Now.Month)
{
novSheet.Select();
}
else if (12 == DateTime.Now.Month)
{
decSheet.Select();
}
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Invoice Register Texas " + DateTime.Today.ToString("MM-dd-yyyy") + ".xlsx");
File.Delete(path);
book.SaveAs(path, Excel.XlFileFormat.xlOpenXMLWorkbook);
excel.Quit();
System.Diagnostics.Process.Start(path);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// przygotujFiltry();
}
listaProjektow();
return;
string sql = "";
string sql2 = "";
try
{
String con = ConfigurationManager.ConnectionStrings["MySQLConnStr5.1"].ConnectionString;
using (OdbcConnection connection = new OdbcConnection(con))
{
connection.Open();
sql = @"select schema_name from information_schema.SCHEMATA where ( SCHEMA_NAME like 'eu-%' or SCHEMA_NAME like 'pr-%')
order by SCHEMA_NAME asc;";
using (OdbcCommand command = new OdbcCommand(sql, connection))
using (OdbcDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ltListaProjektow.Text += "<p>";
ltListaProjektow.Text += @"<a class=""btn btn-default";
try
{
sql2 = "select o.note from `" + dr[0].ToString() + "`.t_object o where o.Name='Projekt-Nazwa';";
// Response.Write("sql2: " + sql2 + "<br>");
using (OdbcCommand command2 = new OdbcCommand(sql2, connection))
using (OdbcDataReader dr2 = command2.ExecuteReader())
{
while (dr2.Read())
{
ltListaProjektow.Text += " href=hld.aspx?schema=" + dr[0].ToString() + ">";
ltListaProjektow.Text += dr[0].ToString() + " " + dr2[0].ToString();
ltListaProjektow.Text += @"</a></p>";
break;
}
dr2.Close();
}
}
catch (Exception exc)
{
ltListaProjektow.Text += "a/>";
ltListaProjektow.Text += dr[0].ToString() + " exc:" + exc.Message;
ltListaProjektow.Text += @"</p>";
}
}
dr.Close();
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write("Błąd: " + ex.Message + "<br>");
Response.Write("sql: " + sql + "<br>");
Response.Write("sql2: " + sql2 + "<br>");
}
}
//Retrieve the Original URL passing FriendlyURL and idAction
public string retrieveOriginalURL(string FriendlyURL, string idAction)
{
if (FriendlyURL.CompareTo("/") == 0)
{
return("Default.aspx");
}
General g = new General();
string url = "";
if (g.FolderInstalled.Length > 0)
{
FriendlyURL = FriendlyURL.Replace("/" + g.FolderInstalled.ToLower(), "");
}
//Response.Write(FriendlyURL);
/*foreach (string[,] fURL in ReservedFURL)
* {
* if (fURL[0,0].ToString() == FriendlyURL) return fURL[0,1].ToString();
* }*/
if (g.DB != null)
{
string SQL = "SELECT * "
+ "FROM friendlyurltab "
+ "WHERE friendlyurl='" + FriendlyURL + "'";
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
SQL = "SELECT a.* "
+ "FROM articletab a "
+ "WHERE a.idarticle=" + result["idarticle"].ToString();
//OdbcCommand cmd = new OdbcCommand(SQL, g.DB.connDB);
OdbcCommand cmd2 = g.DB.PrepareSQL(SQL);
OdbcDataReader result2 = cmd2.ExecuteReader();
if (result2.HasRows)
{
if (g.FolderInstalled.Length > 0)
{
url = "/" + g.FolderInstalled;
}
}
url += "/Default.aspx?action=" + idAction + "&idA=" + result["idarticle"].ToString()
+ "&idS=" + result2["idsubject"].ToString() + "&idL=" + result2["idlanguage"].ToString();
result2.Close();
result2.Dispose();
cmd2.Dispose();
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
g.ErrorMSG = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
}
}
return(url);
}
//
// UpdateFailureCount
// A helper method that performs the checks and updates associated with
// password failure tracking.
//
private void UpdateFailureCount(string username, string failureType)
{
OdbcConnection conn = new OdbcConnection(ConnectionString);
OdbcCommand cmd = new OdbcCommand("SELECT FailedPasswordAttemptCount, " +
" FailedPasswordAttemptWindowStart, " +
" FailedPasswordAnswerAttemptCount, " +
" FailedPasswordAnswerAttemptWindowStart " +
" FROM Users " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
OdbcDataReader reader = null;
DateTime windowStart = new DateTime();
int failureCount = 0;
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
if (failureType == "password")
{
failureCount = reader.GetInt32(0);
windowStart = reader.GetDateTime(1);
}
if (failureType == "passwordAnswer")
{
failureCount = reader.GetInt32(2);
windowStart = reader.GetDateTime(3);
}
}
reader.Close();
DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);
if (failureCount == 0 || DateTime.Now > windowEnd)
{
// First password failure or outside of PasswordAttemptWindow.
// Start a new password failure count from 1 and a new window starting now.
if (failureType == "password")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAttemptCount = ?, " +
" FailedPasswordAttemptWindowStart = ? " +
" WHERE Username = ? AND ApplicationName = ?";
if (failureType == "passwordAnswer")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAnswerAttemptCount = ?, " +
" FailedPasswordAnswerAttemptWindowStart = ? " +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", OdbcType.Int).Value = 1;
cmd.Parameters.Add("@WindowStart", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
throw new Exception("Unable to update failure count and window start.");
}
else
{
if (failureCount++ >= MaxInvalidPasswordAttempts)
{
// Password attempts have exceeded the failure threshold. Lock out
// the user.
cmd.CommandText = "UPDATE Users " +
" SET IsLockedOut = ?, LastLockedOutDate = ? " +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = true;
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
throw new Exception("Unable to lock out user.");
}
else
{
// Password attempts have not exceeded the failure threshold. Update
// the failure counts. Leave the window the same.
if (failureType == "password")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAttemptCount = ?" +
" WHERE Username = ? AND ApplicationName = ?";
if (failureType == "passwordAnswer")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAnswerAttemptCount = ?" +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", OdbcType.Int).Value = failureCount;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
throw new Exception("Unable to update failure count.");
}
}
}
catch (OdbcException)
{
// Handle Exception
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
}
public DataSet GetFormDataP2015(string strPage)
{
//LEESH FEB 2013
string strQuery = "";
OdbcCommand cmdOdbc = new OdbcCommand();
OdbcDataAdapter daOdbc;
DataTable dtTemp = new DataTable();
DataSet dsData = new DataSet();
try
{
if (connOdbc.State == ConnectionState.Closed)
{
connOdbc.Open();
}
switch (strPage)
{
#region "Page1"
case "P2015Page1":
//strQuery= "SELECT PT_NAME, PT_REF_NO, " &
strQuery = "SELECT PT_NAME, PT_REF_NO, " +
"PT_REGISTER_NO, PT_NO_PARTNERS, PT_APPORTIONMENT, PT_COMPLIANCE, " +
"PT_REG_ADDRESS1 , PT_REG_ADDRESS2 ,PT_REG_ADDRESS3,PT_REG_POSTCODE,PT_REG_CITY,PT_REG_STATE," +
"PT_BUS_ADDRESS1 , PT_BUS_ADDRESS2 , PT_BUS_ADDRESS3 ,PT_BUS_POSTCODE,PT_BUS_CITY,PT_BUS_STATE," +
"PT_COR_ADDRESS1 , PT_COR_ADDRESS2 , PT_COR_ADDRESS3 ,PT_COR_POSTCODE, PT_COR_CITY,PT_COR_STATE," +
"PT_ACC_ADDRESS1 ,PT_ACC_ADDRESS2, PT_ACC_ADDRESS3,PT_ACC_POSTCODE, PT_ACC_CITY,PT_ACC_STATE," +
"PT_EMPLOYER_NO2,PT_PRE_PARTNER,PT_TEL1 + PT_TEL2,PT_MOBILE1 + PT_MOBILE2,PT_EMAIL, PT_BWA " +
"FROM TAXP_PROFILE where PT_REF_NO=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_INPUT_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page2"
case "P2015Page2": //Pendapatan Perniagaan
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND [P_YA]=? "; //LeeCC Partnership
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader drP = cmdOdbc.ExecuteReader();
string strTemp = "";
if (drP.HasRows)
{
while (drP.Read())
{
strTemp = drP.GetString(0);
}
}
//drP.Dispose;
//cmdOdbc.Dispose();
strQuery = "SELECT PI_TYPE,PI_PIONEER_INCOME,PI_INCOME_LOSS,PI_P_BEBEFIT,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? ORDER BY [PS_SOURCENO]";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr = cmdOdbc.ExecuteReader();
string strTemp10 = "";
string strTemp11 = "";
if (dr.HasRows)
{
while (dr.Read())
{
strTemp10 = dr.GetString(6);
strTemp11 = dr.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp10 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp10));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
string strTemp12 = "";
string strTemp13 = "";
if (dr2.HasRows)
{
while (dr2.Read())
{
strTemp12 = dr2.GetString(6);
strTemp13 = dr2.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp12 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp13));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp12));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
break;
#endregion
//#region "Page3"
//case "P2015Page3": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND P_YA=? ";//LeeCC Partnership
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
// string strTempP3 = "";
// if (drP3.HasRows)
// {
// while (drP3.Read())
// { strTempP3 = drP3.GetString(0); }
// }
// ////empty A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
// "FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]=3 and [PI_TYPE]<>'Yes'";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// //empty
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=3 ";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// ////>S3 A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
// "FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]>=3 and [PI_TYPE]<>'Yes'";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// //>S3
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO>=3 ";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// break;
//#endregion
#region "Comment it for next project"
//#region "Page2"
//case "P2009Page2": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP = cmdOdbc.ExecuteReader();
// string strTemp = "";
// if (drP.HasRows)
// {
// while (drP.Read())
// { strTemp = drP.GetString(0);}
// }
// //drP.Dispose;
// //cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes'";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// //cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// // cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT TOP 1 PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] = (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes')";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// OdbcDataReader dr = cmdOdbc.ExecuteReader();
// string strTemp6 = "";
// if (dr.HasRows)
// {
// while (dr.Read())
// { strTemp6 = dr.GetString(0); }
// }
// dr.Close();
// cmdOdbc.Dispose();
// if (strTemp6 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? AND [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp6));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// strQuery = "SELECT TOP 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
// string strTemp1 = "";
// string strTemp7 = "";
// if (dr2.HasRows)
// {
// while (dr2.Read())
// {
// strTemp1 = dr2.GetString(0);
// strTemp7 = dr2.GetString(1);
// }
// }
// dr2.Close();
// cmdOdbc.Dispose();
// if (strTemp1 != "" && strTemp7 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp7));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp1));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
//#region "Page3"
//case "P2009Page3": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
// string strTempP3 = "";
// if (drP3.HasRows)
// {
// while (drP3.Read())
// { strTempP3 = drP3.GetString(0); }
// }
// ////empty A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_REF_NO]=" +
// "(SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes') and [PI_TYPE]='' ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr3 = cmdOdbc.ExecuteReader();
// string strTemp8 = "";
// if (dr3.HasRows)
// {
// while (dr3.Read())
// {
// strTemp8 = dr3.GetString(0);
// }
// }
// dr3.Close();
// cmdOdbc.Dispose();
// //empty
// if (strTemp8 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@sourceno", strTemp8));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// ////>S3 A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
// "FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] not in (SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') " +
// "ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr4 = cmdOdbc.ExecuteReader();
// string strTemp9 = "";
// string strTemp10 = "";
// if (dr4.HasRows)
// {
// while (dr4.Read())
// {
// strTemp9 = dr4.GetString(0);
// strTemp10 = dr4.GetString(1);
// }
// }
// dr4.Close();
// cmdOdbc.Dispose();
// //>S3
// if (strTemp9 != "" && strTemp10 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp10));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@source", strTemp9));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
#endregion
#region "Page3"
case "P2015Page3":
strQuery = "SELECT P_DIVISIBLE_INT_DIS,P_DIVISIBLE_RENT_ROY_PRE,P_DIVISIBLE_NOTLISTED,P_DIVISIBLE_ADD_43,P_TAXDED_110," +
" P_TAXDED_132,P_TAXDED_133,P_DIVISIBLE_ADD_43 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND P_YA=? "; //LeeCC Partnership
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr1 = cmdOdbc.ExecuteReader();
string strTemp2 = "";
if (dr1.HasRows)
{
while (dr1.Read())
{
strTemp2 = dr1.GetString(0);
}
}
//drP.Dispose;
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PY_INCOME_TYPE,PY_PAYMENT_YEAR,PY_AMOUNT,PY_EPF FROM [PRECEDING_YEAR] WHERE [P_KEY] = ? order by [PY_DKEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp2));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PRECEDING_YEAR");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT P_DIV_MALDIV,P_TAX_MALDIV FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//share disposal & irb
strQuery = "SELECT [P_KEY],[P_DP_DISPOSAL],[P_DP_DECLARE] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("PARTNERSHIP_INCOME_DISPOSAL");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page4"
case "P2015Page4":
strQuery = "SELECT P_DIVS_EXP_1,P_DIVS_EXP_3,P_DIVS_EXP_4,P_DIVS_EXP_5,P_DIVS_EXP_8 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND P_YA=? "; //LeeCC Partnership
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr8 = cmdOdbc.ExecuteReader();
string strTemp3 = "";
if (dr8.HasRows)
{
while (dr8.Read())
{
strTemp3 = dr8.GetString(0);
}
}
dr8.Close();
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PC_CL_CODE,PC_AMOUNT FROM [P_OTHER_CLAIMS] WHERE [P_KEY] = ? order by [PC_KEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_P_OTHER_CLAIMS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page5"
case "P2015Page5":
strQuery = "SELECT P_WITHTAX_107A_GROSS,P_WITHTAX_107A_TAX," +
" P_WITHTAX_109_GROSS, P_WITHTAX_109_TAX," +
" P_WITHTAX_109A_GROSS,P_WITHTAX_109A_TAX," +
" P_WITHTAX_109B_GROSS,P_WITHTAX_109B_TAX," +
" P_WITHTAX_109F_GROSS,P_WITHTAX_109F_TAX" +
" FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND P_YA=? "; //LeeCC Partnership
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr6 = cmdOdbc.ExecuteReader();
string strTemp4 = "";
if (dr6.HasRows)
{
while (dr6.Read())
{
strTemp4 = dr6.GetString(0);
}
}
else
{
strTemp4 = "0";
}
// dr4.Dispose;
cmdOdbc.Dispose();
strQuery = "SELECT P_CP30_ASAL,convert(nvarchar(20), P_CP30_ASAL_DATE,103), P_CP30_PINDAAN, convert(nvarchar(20), P_CP30_PINDAAN_DATE,103) FROM [PARTNERSHIP_INCOME] where [P_KEY]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp4));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page6"
case "P2015Page6":
strQuery = "SELECT [PT_KEY] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr5 = cmdOdbc.ExecuteReader();
string strTemp5 = "0";
if (dr5.HasRows)
{
while (dr5.Read())
{
strTemp5 = dr5.GetString(0);
}
}
cmdOdbc.Dispose();
dr5.Close();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND [P_YA]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr9 = cmdOdbc.ExecuteReader();
string strTemp9 = "0";
if (dr9.HasRows)
{
while (dr9.Read())
{
strTemp9 = dr9.GetString(0);
}
}
cmdOdbc.Dispose();
dr9.Close();
strQuery = "select PN_PREFIX As [PREFIX],PN_REF_NO AS [REFERENCE_NO]," +
"PN_NAME AS [NAME],PN_IDENTITY AS [IDENTITY],PN_COUNTRY AS [COUNTRY]," +
"PN_DATE_APPOINTNENT AS [DATE_APPOINTMENT]," +
"PN_DATE_CESSATION AS [DATE_CESSATION],PN_SHARE AS [SHARE], " +
"PN_BENEFIT_1 AS [BENEFIT_1],PN_BENEFIT_2 AS [BENEFIT_2],PN_BENEFIT_3 AS [BENEFIT_3]," +
"CP_B_ADJ_INCOMELOSS AS [ADJUSTED_AMOUNT], CP_B_BAL_CHARGE AS [BALANCING_CHARGE]," +
"CP_B_BAL_ALLOWANCE AS [BALANCING_ALLOWANCE], CP_B_7A_ALLOWANCE AS [SCHEDULE_7A]," +
"CP_B_EXP_ALLOWANCE AS [EXPORT_ALLOWANCE] " +
"from CP30 a INNER JOIN TAXP_PARTNERS b on a.CP_REF_NO = b.PN_REF_NO " +
"WHERE [PT_KEY] = ? AND [P_KEY] = ? order by PN_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PartnerKey", strTemp5));
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp9));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PARTNERS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [PT_PRE_PARTNER] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PREPARTNER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page7"
case "P2015Page7":
strQuery = "SELECT [PS_CODE] FROM [TAXP_PSOURCE] where PS_REF_NO=? and PS_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_TAXP_PSOURCE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PPL_SALES,PPL_OP_STK,PPL_PURCHASES_COST,PPL_CLS_STK, PPL_COGS," +
"PPL_OTH_BSIN,PPL_OTH_IN_DIVIDEND,PPL_OTH_IN_INTEREST,PPL_OTH_IN_RENTAL_ROYALTY,cast(PPL_OTH_IN_OTHER as money)+cast(PPL_NONTAX_IN_TOTAL as money)," +
"PPL_EXP_LOANINTEREST,PPL_EXP_SALARY,PPL_EXP_RENTAL,PPL_EXP_CONTRACT,PPL_EXP_COMMISSION," +
"PPL_BAD_DEBTS,PPL_TRAVEL,PPL_EXP_REPAIR_MAINT,PPL_EXP_PRO_ADV, cast(PPL_OTHER_EXP as money) + cast(PPL_LAWYER_COST as money) + cast(PPL_EXP_INT_RES as money), " +
"PPL_NET_PROFIT,PPL_DISALLOWED_EXP FROM [P_PROFIT_AND_LOSS] where P_REF_NO=? and P_YA=?"; //LeeCC Partnership
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT BS_LAND,BS_MACHINERY,BS_TRANSPORT,BS_OTH_FA," +
"BS_INVESTMENT, BS_STOCK,BS_TRADE_DEBTORS,BS_OTH_DEBTORS,BS_CASH,BS_BANK,BS_OTH_CA," +
"BS_LOAN, BS_TRADE_CR,BS_OTHER_CR,BS_OTH_LIAB,BS_LT_LIAB, " +
"BS_CAPITALACCOUNT,BS_BROUGHT_FORWARD,BS_CY_PROFITLOSS,BS_DRAWING, BS_CARRIED_FORWARD " +
"FROM [P_BALANCE_SHEET] where P_REF_NO=? and P_YA=? order by BS_SOURCENO";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page8"
case "P2015Page8":
if (connOdbcB.State == ConnectionState.Closed)
{
connOdbcB.Open();
}
strQuery = "select ta_add_line1, ta_add_line2, ta_add_line3, ta_add_postcode, ta_add_city, ta_add_state, ta_tel_no, TA_MOBILE, ta_email from taxa_profile where ta_co_name=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbcB);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxagent", strTaxAgent));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P8_FIRM");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
}
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.ToString()); }
return(dsData);
//LEESH END
}
public static bool Initialize(OdbcConnectionStringBuilder connectionString)
{
if (connectionString == null)
{
return(false);
}
using (OdbcConnection connection = new OdbcConnection(connectionString.ToString()))
{
try
{
connection.Open();
// Check sections
int sectionID = 0;
string SQLString = "SELECT ID, Letter, [Tables], Winners FROM Section";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sectionID = reader.GetInt32(0);
string sectionLetter = reader.GetString(1);
int numTables = reader.GetInt32(2);
if (sectionID < 1 || sectionID > 4 || (sectionLetter != "A" && sectionLetter != "B" && sectionLetter != "C" && sectionLetter != "D"))
{
reader.Close();
MessageBox.Show("Database contains incorrect Sections. Maximum 4 Sections labelled A, B, C, D", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (numTables > 30)
{
reader.Close();
MessageBox.Show("Database contains > 30 Tables in a Section", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
reader.Close();
if (sectionID == 0)
{
MessageBox.Show("Database contains no Sections", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Add column 'BiddingStarted' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD BiddingStarted YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'BiddingComplete' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD BiddingComplete YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'BiddingStarted' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD PlayComplete YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'Name' to table 'PlayerNumbers' if it doesn't already exist
SQLString = "ALTER TABLE PlayerNumbers ADD [Name] VARCHAR(30)";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'Round' to table 'PlayerNumbers' if it doesn't already exist
SQLString = "ALTER TABLE PlayerNumbers ADD [Round] SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Ensure that all Round values are set to 0 to start with
SQLString = "UPDATE PlayerNumbers SET [Round]=0";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
// Check if this is an individual event, when RoundData will have a 'South' column
bool individualEvent = true;
SQLString = $"SELECT TOP 1 South FROM RoundData";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteScalar();
}
catch (OdbcException)
{
individualEvent = false;
}
// If this is an individual event, add extra columns South and West to ReceivedData and IntermediateData if they don't exist
if (individualEvent)
{
SQLString = "ALTER TABLE ReceivedData ADD South SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE ReceivedData ADD West SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE IntermediateData ADD South SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE IntermediateData ADD West SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
}
// Add a new column 'TabPlayPairNo' to table 'PlayerNumbers' if it doesn't exist and populate it if possible
SQLString = "ALTER TABLE PlayerNumbers ADD TabPlayPairNo SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "SELECT Section, [Table], Direction FROM PlayerNumbers";
cmd = new OdbcCommand(SQLString, connection);
reader = cmd.ExecuteReader();
OdbcCommand cmd2 = new OdbcCommand();
while (reader.Read())
{
int section = reader.GetInt32(0);
int table = reader.GetInt32(1);
string direction = reader.GetString(2);
if (individualEvent)
{
switch (direction)
{
case "N":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "S":
SQLString = $"SELECT South FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "E":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "W":
SQLString = $"SELECT West FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
}
}
else
{
switch (direction)
{
case "N":
case "S":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "E":
case "W":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
}
}
cmd2 = new OdbcCommand(SQLString, connection);
object queryResult = cmd2.ExecuteScalar();
string pairNo = queryResult.ToString();
SQLString = $"UPDATE PlayerNumbers SET TabPlayPairNo={pairNo} WHERE Section={section} AND [Table]={table} AND Direction='{direction}'";
cmd2 = new OdbcCommand(SQLString, connection);
cmd2.ExecuteNonQuery();
}
cmd2.Dispose();
// Add various columns to table 'Settings' if they don't already exist and set defaults
SQLString = "ALTER TABLE Settings ADD ShowResults YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowResults=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD ShowPercentage YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowPercentage=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD LeadCard YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET LeadCard=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2ValidateLeadCard YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2ValidateLeadCard=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2NumberEntryEachRound YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2NumberEntryEachRound=NO";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2ViewHandRecord YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2ViewHandRecord=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2Ranking SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2Ranking=1";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2NameSource SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowResults=0";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD PollInterval SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = $"UPDATE Settings SET PollInterval={Properties.Settings.Default.PollInterval}";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Check if any previous results in database
object Result;
SQLString = "SELECT * FROM ReceivedData";
cmd = new OdbcCommand(SQLString, connection);
Result = cmd.ExecuteScalar();
if (Result != null)
{
MessageBox.Show("Database contains previous results", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
cmd.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
return(true);
}
private string Column_9 = ""; //кампания
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bool is_mobile = true;
string loginid;
string fio_client;
HiddenFieldOut_ID.Value = Request.QueryString["Out_ID"];
string debug = Request.QueryString["debug"];
HF_Fio.Value = Request.QueryString["an"];
HF_Out_ID.Value = Request.QueryString["aid"];
HF_cid.Value = Request.QueryString["Campaign_ID"];
HF_Abonent_ID.Value = Request.QueryString["Abonent_ID"];
//Label2_2.Text = Request.QueryString["OperatorName"];
is_mobile = Request.QueryString["is_mobile"] != null && Request.QueryString["is_mobile"].ToString() == "1";
if (Request.QueryString["Column_17"] != null)
{
Column_17 = HttpUtility.UrlDecode(Request.QueryString["Column_17"], Encoding.GetEncoding("windows-1251"));
}
Column_10 = Request.QueryString["Column_10"] != null ? Request.QueryString["Column_10"].ToString() : "";
Column_11 = Request.QueryString["Column_11"] != null ? Request.QueryString["Column_11"].ToString() : "";
Column_12 = Request.QueryString["Column_12"] != null ? Request.QueryString["Column_12"].ToString() : "";
Column_13 = Request.QueryString["Column_13"] != null ? Request.QueryString["Column_13"].ToString() : "";
Column_14 = Request.QueryString["Column_14"] != null ? Request.QueryString["Column_14"].ToString() : "";
Column_7 = Request.QueryString["Column_7"] != null ? Request.QueryString["Column_7"].ToString() : "";
Column_9 = Request.QueryString["Column_9"] != null ? Request.QueryString["Column_9"].ToString() : "";
HiddenFieldColumn_11.Value = Column_11;
HiddenFieldColumn_12.Value = Column_12;
HiddenFieldColumn_13.Value = Column_13;
HiddenFieldColumn_14.Value = Column_14;
HiddenFieldColumn_17.Value = Column_17.ToString();
HiddenFieldColumn_9.Value = Column_9.ToString();
LabelColumn_8.Text = Request.QueryString["Column_8"] != null ? Request.QueryString["Column_8"].ToString() : "";
IterateControls(Page, Column_11, "Column_11");
IterateControls(Page, Column_9, "Column_9");
IterateControls(Page, Column_13, "Column_13");
var Is1 = Column_9 != "" && Column_11 != "" && Column_13 != "";
var Is2 = Column_9 == "" && Column_11 != "" && Column_13 == "";
var Is3 = !Is1 && !Is2;
if (Is3)
{
}
else if (Is2)
{
}
else
{
}
//Panel1_2.Visible = Column_10 == "";
//Panel1.Visible = !Panel1_2.Visible;
Column_15 = Request.QueryString["Column_15"] != null ? Request.QueryString["Column_15"].ToString() : "";
HiddenFieldColumn_15.Value = Column_15;
//LabelCamp_1.Text = Column_15;
//LabelCamp_2.Text = Column_15;
// PanelOtr_2.Visible = !(Column_15 != "");
// PanelOtr_1.Visible = (Column_15 != "");
// PanelOtr_2_2.Visible = !(Column_15 != "");
// PanelOtr_1_2.Visible = (Column_15 != "");
//Button12_1.CommandName = Column_15 != "" ? "Panel5_camp" : "Panel4";
// LabelCamp_Otr_1.Text = LabelCamp_Otr_2.Text = Column_15;
HiddenField1.Value = debug;
if (debug == null)
{
loginid = Request.QueryString["loginid"];
fio_client = HttpUtility.UrlDecode(Request.QueryString["an"], Encoding.GetEncoding("windows-1251"));
}
else
{
HF_Out_ID.Value = "1111";
loginid = "1001";
fio_client = "Иванов Иван Иванович";
}
IterateControls(Page, fio_client, "fio");
SqlConnection conn = null;
ConnectionStringSettings settingsSCCS =
ConfigurationManager.ConnectionStrings["SCCSConnectionString"];
// If found, return the connection string.
try
{
//узнаем имя оператора
if (debug == null && 1 == 0)
{
OdbcConnection myOdbcConnection = new OdbcConnection(settingsSCCS.ConnectionString);
OdbcCommand myOdbcCommand = new OdbcCommand("Select GivenName,SurName from blue.dbo.Agent where TelsetLoginID='" + loginid + "'", myOdbcConnection);
myOdbcCommand.CommandType = CommandType.Text;
myOdbcCommand.Connection.Open();
OdbcDataReader myOdbcReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
//Label26.Text = "[unknown]";
while (myOdbcReader.Read())
{
//Label1.Text = myOdbcReader.GetString(0) + " " + myOdbcReader.GetString(1);
}
myOdbcReader.Close();
myOdbcConnection.Close();
}
else
{
//Label1.Text = "Василий пупкин";
}
}
catch (SqlException SqlEx)
{
throw new Exception("An error occurred while transaction", SqlEx);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
}
static void Main(string[] args)
{
//Set variables
string connectionString = ConfigurationManager.ConnectionStrings["OdbcConnection"].ToString();
string filepath_root = ConfigurationManager.ConnectionStrings["rootPath"].ToString();
string QueryString = @"SELECT [OrganisationID]
FROM[CMA_Consistency].[CMA].[Participants]";
string filepath = "";
string Filename = "";
OdbcCommand command = new OdbcCommand();
//Get the LPs from the database
List <string> LP_list = new List <string>();
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
command.Connection = connection;
connection.Open();
command.CommandText = QueryString;
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
LP_list.Add(reader[0].ToString());
}
// Call Close when done reading.
reader.Close();
}
//Get the table names from SQL where the results are
QueryString = @"SELECT t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'Comp';";
List <string> Table_list = new List <string>();
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
command.Connection = connection;
connection.Open();
command.CommandText = QueryString;
// Execute the DataReader and access the data.
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Table_list.Add(reader[0].ToString());
}
// Call Close when done reading.
reader.Close();
}
foreach (var table in Table_list)
{
Console.WriteLine(table);
foreach (var LP in LP_list)
{
QueryString = @"SELECT * FROM [CMA_Consistency].[Comp].[" + table + "] WHERE LP_LP = '" + LP + "'";
filepath = filepath_root + LP + @"\";
System.IO.Directory.CreateDirectory(filepath);
Console.WriteLine(LP);
Filename = table + "_" + LP + ".csv";
SQLtoCSV.SQLToCSV(connectionString, QueryString, filepath, Filename);
}
}
}
public Form1()
{
conn = new OdbcConnection("DSN=" + ConnectionString);
try
{
conn.Open();
}
catch
{
MessageBox.Show("Cannot connect to ODBC " + ConnectionString);
exit = true;
// return;
}
if (exit == true)
{
return;
}
InitializeComponent();
listView1.Columns.Add("Originator");
listView1.Columns.Add("Route");
listView1.Columns.Add("Instal-From");
listView1.Columns.Add("Instal-To");
listView2.Columns.Add("Bank");
listView2.Columns.Add("Load Balance");
listView2.Columns.Add("Totals By");
textBoxHidden_Route.Visible = false;
textBoxFr.Text = " ";
textBoxTo.Text = " ";
int[] arrBal = new int[4];
arrBal[0] = 0;
arrBal[1] = 1;
arrBal[2] = 50;
arrBal[3] = 1000000;
//string[,] loadBalArr = new string[4, 2];
//Array.Clear(loadBalArr, 4, 2);
//comboMID
OdbcCommand comboFillMids = new OdbcCommand(
"SELECT MID FROM dbo.MERCHANTS_test " +
"where mid like '00%' group by [MID]", conn);
OdbcDataReader reader = comboFillMids.ExecuteReader();
while (reader.Read())
{
string word = reader.GetString(0);
this.comboGroup.Items.Add(word);
}
reader.Close();
//comboBnkRoute
OdbcCommand comboFillBnks = new OdbcCommand(
"SELECT BANKDEscr FROM dbo.ROUTEBANK where uploadhostid <> ' '",
conn);
OdbcDataReader reader2 = comboFillBnks.ExecuteReader();
while (reader2.Read())
{
string BankRout = reader2.GetString(0);
this.comboBnkRoute.Items.Add(BankRout);
this.comboBoxLD.Items.Add(BankRout);
}
reader2.Close();
// Combo Box for installemnts
for (int i = 0; i < 100; i++)
{
comboBox_InstalFr.Items.Add(i);
comboBox_InstalTo.Items.Add(i);
}
//Combo box for Load balance Percentage
for (int i = 0; i < 4; i++)
{
comboBoxPrcntge.Items.Add(arrBal[i]);
}
// List Box Originator
OdbcCommand listBoxOrigin = new OdbcCommand(
"Select bankdescr_short From dbo.ROUTEBANK",
conn);
OdbcDataReader reader3 = listBoxOrigin.ExecuteReader();
while (reader3.Read())
{
string originDescr = reader3.GetString(0);
this.listBoxOrigin.Items.Add(originDescr);
}
reader3.Close();
}
public RankingList(int deviceNumber)
{
DeviceNumber = deviceNumber;
Device device = AppData.DeviceList[deviceNumber];
Direction = device.Direction;
PairNumber = device.PairNumber;
if (AppData.IsIndividual)
{
PairOrPlayer = "Player";
}
else
{
PairOrPlayer = "Pair";
}
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
string SQLString = $"SELECT Orientation, Number, Score, Rank FROM Results WHERE Section={device.SectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader1 = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
Ranking ranking = new Ranking
{
Orientation = reader1.GetString(0),
PairNumber = reader1.GetInt32(1),
Score = reader1.GetString(2),
Rank = reader1.GetString(3)
};
ranking.ScoreDecimal = Convert.ToDouble(ranking.Score);
Add(ranking);
}
});
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S02") // Any error other than results table doesn't exist
{
throw (e);
}
}
finally
{
reader1.Close();
cmd.Dispose();
}
if (Count == 0) // Results table either doesn't exist or contains no entries, so try to calculate rankings
{
if (AppData.IsIndividual)
{
InsertRange(0, CalculateIndividualRankingFromReceivedData(connection, device.SectionID));
}
else
{
InsertRange(0, CalculateRankingFromReceivedData(connection, device.SectionID));
}
}
}
// Make sure that ranking list is sorted into presentation order
Sort((x, y) =>
{
int sortValue = y.Orientation.CompareTo(x.Orientation); // N's first then E's
if (sortValue == 0)
{
sortValue = y.ScoreDecimal.CompareTo(x.ScoreDecimal);
}
if (sortValue == 0)
{
sortValue = x.PairNumber.CompareTo(y.PairNumber);
}
return(sortValue);
});
// Work out if one- or two-winner movement
TwoWinners = !AppData.IsIndividual && Exists(x => x.Orientation == "E");
}
private void button9_Click(object sender, EventArgs e)
{
Array.Clear(loadBalArr, 4, 3);
int i = 0;
foreach (ListViewItem item in listView2.Items)
{
string tmpLD_Descr = item.SubItems[0].Text.ToString();
switch (tmpLD_Descr)
{
case "PIRAEUS":
loadBalArr[i, 0] = "NET_ABC";
break;
case "ETHNIKI":
loadBalArr[i, 0] = "NET_NTBN";
break;
case "ALPHABANK":
loadBalArr[i, 0] = "NET_CLBICALPHA";
break;
case "EUROBANK":
loadBalArr[i, 0] = "NET_CLBICEBNK";
break;
}
loadBalArr[i, 1] = item.SubItems[1].Text.ToString();
loadBalArr[i, 2] = item.SubItems[2].Text.ToString();
i++;
}
OdbcCommand comLdBal_Cursor = conn.CreateCommand();
// Delete LB from LoadBal file so to insert new selections
comLdBal_Cursor.CommandText = "delete dbo.LOADBALANCE_test where balancegroup in (select 'M'+tid from dbo.MERCHANTS_test where mid = ?)";
comLdBal_Cursor.Parameters.Add("mid", OdbcType.Char).Value = midDescr.Text;
OdbcDataReader readerCursor5 = comLdBal_Cursor.ExecuteReader();
readerCursor5.Close();
comLdBal_Cursor.CommandText =
"declare @TID varchar(16), @BALGROUP varchar(20)" +
"DECLARE merch_cursor CURSOR FOR " +
" SELECT distinct(tid) FROM dbo.MERCHANTS_test WHERE mid = ? " +
" OPEN merch_cursor " +
" if @@ERROR > 0 " +
" return " +
" FETCH NEXT FROM merch_cursor INTO @TID " +
" WHILE @@FETCH_STATUS = 0 " +
" BEGIN " +
" SET @BALGROUP = 'M'+@TID " +
" INSERT INTO dbo.LOADBALANCE_test (BALANCEGROUP, DESTPORT, LOADTYPE, LOADVALUE) " +
" VALUES (@BALGROUP, " +
loadBalArr[0, 0] + "," + loadBalArr[0, 2] + loadBalArr[0, 1] + ")" +
" INSERT INTO LOADBALANCE (BALANCEGROUP, DESTPORT, LOADTYPE, LOADVALUE) " +
" VALUES (@BALGROUP, " +
loadBalArr[1, 0] + "," + loadBalArr[1, 2] + loadBalArr[1, 1] + ")" +
" INSERT INTO LOADBALANCE (BALANCEGROUP, DESTPORT, LOADTYPE, LOADVALUE) " +
" VALUES (@BALGROUP, " +
loadBalArr[2, 0] + "," + loadBalArr[2, 2] + loadBalArr[2, 1] + ")" +
" INSERT INTO LOADBALANCE (BALANCEGROUP, DESTPORT, LOADTYPE, LOADVALUE) " +
" VALUES (@BALGROUP, " +
loadBalArr[3, 0] + "," + loadBalArr[3, 2] + loadBalArr[3, 1] + ")" +
" FETCH NEXT FROM merch_cursor " +
" into @TID " +
" END " +
" CLOSE merch_cursor " +
" DEALLOCATE merch_cursor " +
" GO ";
comLdBal_Cursor.Parameters.Add("mid", OdbcType.Char).Value = midDescr.Text;
OdbcDataReader readerCursor6 = comLdBal_Cursor.ExecuteReader();
readerCursor6.Close();
}
/// <summary>
/// Returns all scheduled <c>IImagingTask</c>s for the <c>plateID</c>.
/// </summary>
public IImagingTask[] GetImagingTasks(Formulatrix.Integrations.ImagerLink.IRobot robot, string plateID)
{
// Check arguments - do it up front to avoid possible inconsistencies later
if (null == robot)
{
throw new System.NullReferenceException("robot must not be null");
}
if (null == plateID)
{
throw new System.NullReferenceException("plateID must not be null");
}
// Log the call to the method
if (_log.IsDebugEnabled)
{
string msg = "Called " + this + ".GetImagingTasks(robot=" + RobotUtils.iRobotToString(robot) + ", plateID=\"" + plateID + "\")";
_log.Debug(msg);
}
// Declare the array that will be populated and returned - default to a zero-length array
IImagingTask[] iImagingTasks = new IImagingTask[0];
// Ensure connected to database - may throw an exception but we don't need/want to catch it
if (System.Data.ConnectionState.Closed.Equals(_platedbConnection.State))
{
//connectToDB();
}
// Try and read the imaging tasks from the db
try
{
// TODO - unnecessary check? Can connectToDB fail without throwing an exception?
if (System.Data.ConnectionState.Open.Equals(_platedbConnection.State))
{
// Update the parameters
_command.Parameters["@p1"].Value = plateID;
_command.Parameters["@p2"].Value = robot.Name;
// Execute the select
OdbcDataReader dataReader = _command.ExecuteReader();
// Read the data reader's rows into the ProjectList
if (dataReader.HasRows)
{
// Get an ArrayList to hold the tasks
System.Collections.ArrayList tasks = new System.Collections.ArrayList();
// Loop over all the returned rows
while (dataReader.Read())
{
// Create and populate a new ImagingTask
global::OPPF.Integrations.ImagerLink.Scheduling.ImagingTask task = new global::OPPF.Integrations.ImagerLink.Scheduling.ImagingTask();
// .NET < 2.0
//task.DateToImage = dataReader.GetDateTime(0);
// .NET >= 2.0 Only!
task.SetDateToImage(DateTime.SpecifyKind(dataReader.GetDateTime(0), DateTimeKind.Utc));
if (dataReader.IsDBNull(1))
{
task.SetDateImaged(DateTime.MinValue);
}
else
{
// .NET < 2.0
//task.DateImaged = dataReader.GetDateTime(1);
// .NET >= 2.0 Only!
task.SetDateImaged(DateTime.SpecifyKind(dataReader.GetDateTime(1), DateTimeKind.Utc));
//task.DateImaged = dataReader.GetDateTime(1).ToLocalTime();
}
task.SetPriority(dataReader.GetInt32(2));
task.SetState((Formulatrix.Integrations.ImagerLink.Scheduling.ImagingState)dataReader.GetInt32(3));
task.SetInQueue(true);
// Store this task in the ArrayList
tasks.Add(task);
//_log.Debug("> Got task: DateToImage=" + task.DateToImage + ", Priority=" + task.Priority + ", State=" + task.State + ", InQueue= " + task.InQueue);
}
// Convert ArrayList to IImagingTask[]
iImagingTasks = (IImagingTask[])tasks.ToArray(typeof(IImagingTask));
}
// Close the dataReader
dataReader.Close();
}
}
catch (Exception e)
{
// Log it
string msg = "Exception " + e.Message + " during direct db part of getImagingTasks() for plate " + plateID + " - will fail down to webservice call";
_log.Error(msg, e);
// Don't rethrow - fail down to webservice call
}
// If we got no or not enough tasks
if ((null == iImagingTasks) || (iImagingTasks.GetLength(0) < MIN_IMAGING_TASKS))
{
// Hit the webservice, which may cause a schedule to be created
iImagingTasks = GetImagingTasksFromWebService(robot, plateID);
}
// Set an appropriate value for InQueue
setInQueue(iImagingTasks);
// Ensure we never return null
if (null == iImagingTasks)
{
// Fix it
iImagingTasks = new IImagingTask[0];
// Log it
_log.Warn("Fixed null iImagingTasks at end of WSPlate.getImagingTasks() for plate " + plateID);
}
// Return the array of IImagingTasks
return(iImagingTasks);
}
private void Command1_Click(object sender, EventArgs e)
{
nowConnected = false;
if (File.Exists(Environment.CurrentDirectory + "\\RemLog"))
{
StreamReader file = new StreamReader(Environment.CurrentDirectory + "\\RemLog");
string line = "";
int counter = 1;
while ((line = file.ReadLine()) != null)
{
switch (counter++)
{
case 1:
UID = line;
break;
case 2:
PWD = line;
break;
case 3:
UType = line;
break;
default:
break;
}
}
if (UID == txtUser.Text & PWD == txtPwd.Text)
{
nowConnected = true;
}
else
{
MessageBox.Show("Invalid Login");
txtUser.Focus();
txtUser.SelectionStart = 0;
txtUser.SelectionLength = txtUser.Text.Length;
}
}
else
{
CN.OpenConnection();
cmd = new System.Data.Odbc.OdbcCommand("SELECT * FROM UserMaster WHERE UserName='******' AND EncodedPass='******'", CN.DBConnection);
Rs1 = cmd.ExecuteReader();
if (Rs1.HasRows)
{
StreamWriter file = new StreamWriter(Environment.CurrentDirectory + "\\RemLog");
UID = Rs1["UserName"].ToString();
PWD = Rs1["EncodedPass"].ToString();
UType = Rs1["UserType"].ToString();
Rs1.Close();
CN.closeconnection();
file.WriteLine(UID);
file.WriteLine(PWD);
file.WriteLine(UType);
file.Close();
nowConnected = true;
}
else
{
Rs1.Close();
CN.closeconnection();
MessageBox.Show("Invalid Login");
txtUser.Focus();
txtUser.SelectionStart = 0;
txtUser.SelectionLength = txtUser.Text.Length;
}
}
if (nowConnected == true)
{
RetrieveDialogType frmRet = new RetrieveDialogType();
//this.Hide();
if (UType == "1")
{
//frmAd.ShowDialog();
}
if (UType == "2")
{
frmRet.ShowDialog();
}
if (UType == "3")
{
//frmRet.BackColor = Color.SlateBlue
//frmRet.Retrieve.BackColor = Color.DarkSlateBlue
//frmRet.Retrieve.ForeColor = Color.White
//frmRet.btnViewReport.BackColor = Color.DarkSlateBlue
//frmRet.btnViewReport.ForeColor = Color.White
//frmRet.btnRefresh.BackColor = Color.DarkSlateBlue
//frmRet.btnRefresh.ForeColor = Color.White
//frmRet.DataGridView1.BackgroundColor = Color.White
frmRet.ShowDialog();
}
this.Close();
}
}
private static List <Ranking> CalculateIndividualRankingFromReceivedData(OdbcConnection conn, int sectionID)
{
List <Ranking> rankingList = new List <Ranking>();
List <Result> resList = new List <Result>();
string SQLString = $"SELECT Board, PairNS, PairEW, South, West, [NS/EW], Contract, Result FROM ReceivedData WHERE Section={sectionID}";
OdbcCommand cmd = new OdbcCommand(SQLString, conn);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Result result = new Result()
{
BoardNumber = reader.GetInt32(0),
PairNS = reader.GetInt32(1),
PairEW = reader.GetInt32(2),
South = reader.GetInt32(3),
West = reader.GetInt32(4),
DeclarerNSEW = reader.GetString(5),
Contract = reader.GetString(6),
TricksTakenSymbol = reader.GetString(7)
};
if (result.Contract.Length > 2) // Testing for unplayed boards and corrupt ReceivedData table
{
result.CalculateScore();
resList.Add(result);
}
}
});
}
catch (OdbcException)
{
return(null);
}
finally
{
reader.Close();
cmd.Dispose();
}
// Calculate MPs
List <Result> currentBoardResultList = new List <Result>();
int currentBoard;
int currentScore;
foreach (Result result in resList)
{
currentScore = result.Score;
currentBoard = result.BoardNumber;
currentBoardResultList = resList.FindAll(x => x.BoardNumber == currentBoard);
result.MatchpointsNS = 2 * currentBoardResultList.FindAll(x => x.Score < currentScore).Count + currentBoardResultList.FindAll(x => x.Score == currentScore).Count - 1;
result.MatchpointsMax = 2 * currentBoardResultList.Count - 2;
result.MatchpointsEW = result.MatchpointsMax - result.MatchpointsNS;
}
// Add up MPs for each pair, creating Ranking List entries as we go
foreach (Result result in resList)
{
Ranking rankingListFind = rankingList.Find(x => x.PairNumber == result.PairNS);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNumber = result.PairNS,
Orientation = "0",
MP = result.MatchpointsNS,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsNS;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNumber == result.PairEW);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNumber = result.PairEW,
Orientation = "0",
MP = result.MatchpointsEW,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsEW;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNumber == result.South);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNumber = result.South,
Orientation = "0",
MP = result.MatchpointsNS,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsNS;
rankingListFind.MPMax += result.MatchpointsMax;
}
rankingListFind = rankingList.Find(x => x.PairNumber == result.West);
if (rankingListFind == null)
{
Ranking ranking = new Ranking()
{
PairNumber = result.West,
Orientation = "0",
MP = result.MatchpointsEW,
MPMax = result.MatchpointsMax
};
rankingList.Add(ranking);
}
else
{
rankingListFind.MP += result.MatchpointsEW;
rankingListFind.MPMax += result.MatchpointsMax;
}
}
// Calculate percentages
foreach (Ranking ranking in rankingList)
{
if (ranking.MPMax == 0)
{
ranking.ScoreDecimal = 50.0;
}
else
{
ranking.ScoreDecimal = 100.0 * ranking.MP / ranking.MPMax;
}
ranking.Score = ranking.ScoreDecimal.ToString("0.##");
}
// Calculate ranking
rankingList.Sort((x, y) => y.ScoreDecimal.CompareTo(x.ScoreDecimal));
foreach (Ranking ranking in rankingList)
{
double currentScoreDecimal = ranking.ScoreDecimal;
int rank = rankingList.FindAll(x => x.ScoreDecimal > currentScoreDecimal).Count + 1;
ranking.Rank = rank.ToString();
if (rankingList.FindAll(x => x.ScoreDecimal == currentScoreDecimal).Count > 1)
{
ranking.Rank += "=";
}
}
return(rankingList);
}
