//initializing the leader handler
public void BeginHandling()
{
try
{
email = DB_Handler.GetEmail(userName, dbConn);
// command = " SELECT T.Number, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
// " FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number";
command = " SELECT T.Number, T.Issue_Date, I.Assigned,I.Status, I.Due_Date" +
" FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number AND T.Assigner ='" + userName + "'";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount-1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");// end of reading from the database
writer.Flush();
DB_Handler.DisposeAll(sqlCommand, dbReader);
WaitForQueries();
}
catch
{
Console.WriteLine(" The Database is down please try again later");//for debugging server
DB_Handler.DisposeAll(sqlCommand, dbReader);
}
}
public XDocument formatTasks(OdbcDataReader r)
{
xdoc = new XDocument();
XElement title, notes, startdatetime, enddatetime, place, id;
XElement root = new XElement("message");
XAttribute type = new XAttribute("type", "tasks");
XAttribute errorStatus = new XAttribute("error_status", "0");
root.Add(type);
XElement tasks = new XElement("tasks");
while (r.Read())
{
XElement task = new XElement("task");
id = new XElement("id", r.GetValue(0).ToString());
task.Add(id);
title = new XElement("title", r.GetValue(1).ToString());
task.Add(title);
notes = new XElement("notes", r.GetValue(2).ToString());
task.Add(notes);
startdatetime = new XElement("startdatetime", r.GetValue(3).ToString());
task.Add(startdatetime);
enddatetime = new XElement("enddatetime", r.GetValue(4).ToString());
task.Add(enddatetime);
place = new XElement("place", r.GetValue(5).ToString());
task.Add(place);
tasks.Add(task);
}
root.Add(tasks);
root.Add(errorStatus);
xdoc.Add(root);
return xdoc;
}
/// <summary>
/// DisplayRecords: This function displays the records in the view if any.
/// </summary>
static void DisplayRecords()
{
Console.Write("\tRecords in Prescirption list...");
try
{
reader = (OdbcDataReader)command.ExecuteReader();
// read the returned resultset
while (reader.Read())
{
/*
* This view consists the following fields:
* rxdef_id (TINYINT)
* drug_name (CHARACTER 50)
* description (CHARACTER 50)
* rx_date (DATE)
* For the purpose of keeping it simple, we are displaying the rxdef_id and drug_name on the console (the first two fields)
*/
Console.WriteLine("\n\t\t{0} {1} ", reader.GetInt16(0), reader.GetString(1));
}
// close the reader
reader.Close();
}
catch (Exception e)
{
ExceptionDisplay(e);
}
}
//initializing the leader handler
public void BeginHandling()
{
try
{
//writer.WriteLine("I am not done");
command = " SELECT T.Number, T.Assigner, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
" FROM Ticket AS T, Ticket_Information AS I" +
" WHERE T.Number=I.Number AND I.Assigned='" + userName +
"' AND (I.Status = 'Assigned' OR I.Status='Waiting' OR I.Status='Work_In_Progress') AND I.Due_Date > #" + System.DateTime.Now + "#";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
//Console.WriteLine("I am done");
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount - 1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");// end of reading from the database
writer.Flush();
DB_Handler.DisposeAll(sqlCommand, dbReader);
WaitForQueries();
//do not forget to close the reader
}
catch
{
Console.WriteLine(" The Database is down please try again later");//for debugging server
}
}
/// <summary>
/// DisplayRecords: This function displays the records in the view if any.
/// </summary>
static void DisplayRecords()
{
Console.Write("\tRecord in getpatientbalance stored procedure");
try
{
reader = (OdbcDataReader)command.ExecuteReader();
// read the returned resultset
while (reader.Read())
{
/*
* This view consists the following fields:
* pat_id
* pat_0_30_days
* pat_31_60_days
* pat_61_90_days
* pat_91_120_days
* pat_121_plus_days
* last_pmt_date
* last_pmt_amt
* last_pmt_type
* last_pmt_desc
*/
Console.WriteLine("\npat_id:{0}\npat_0_30_days: {1}\npat_31_60_days: {2} \npat_61_90_days: {3}\npat_91_120_plus_days: {4} \npat_121_plus_days: {5}\nlast_pmt_date: {6} \nlast_pmt_amt: {7}\nlast_pmt_type: {8}\nlast_pmt_desc: {9}",
reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
}
// close the reader
reader.Close();
}
catch (Exception e)
{
ExceptionDisplay(e);
}
}
static List <etf_epf_record> GetListETF(string filter = "")
{
System.Data.Odbc.OdbcConnection l_objConnection = new System.Data.Odbc.OdbcConnection();
System.Data.Odbc.OdbcCommand l_objCmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader l_objRdr = null;
l_objConnection.ConnectionString = "DSN=PRODDSN_LIVE;PWD=INTERNET;UID=INTERNET";
l_objConnection.Open();
l_objCmd.Connection = l_objConnection;
l_objCmd.CommandType = CommandType.Text;
string sql = string.Empty;
if (string.IsNullOrEmpty(filter))
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' order by etdate";
}
else
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' and etref in " + filter + " order by etdate";
}
l_objCmd.CommandText = sql;
if (l_objRdr != null)
{
l_objRdr.Close();
}
l_objRdr = l_objCmd.ExecuteReader();
List <etf_epf_record> list = new List <etf_epf_record>();
if (l_objRdr.HasRows)
{
while (l_objRdr.Read())
{
etf_epf_record file = new etf_epf_record();
file.etref = l_objRdr["etref"].ToString().Trim();
file.etreg = l_objRdr["etreg"].ToString().Trim();
file.etamt = l_objRdr["etamt"].ToString().Trim();
file.etpflg = l_objRdr["etpflg"].ToString().Trim();
file.etdate = l_objRdr["etdate"].ToString().Trim();
file.ettime = l_objRdr["ettime"].ToString().Trim();
file.etptyp = l_objRdr["etptyp"].ToString().Trim();
file.etfrpd = l_objRdr["etfrpd"].ToString().Trim(); //From Period
file.ettopd = l_objRdr["ettopd"].ToString().Trim(); //To Period
file.etnomb = l_objRdr["etnomb"].ToString().Trim(); //EMployeed
file.etadat = l_objRdr["etadat"].ToString().Trim();
file.etcopr = l_objRdr["etcopr"].ToString().Trim();
file.ettran = l_objRdr["ettran"].ToString().Trim();
list.Add(file);
}
}
return(list);
}
public static void InsertData(OdbcDataReader read)
{
string table = @"d:\documents and settings\axkhan2\desktop\437proj\mytariff.dbf";
string query = "insert into " + table + "values (@ln, @orgcity, @destcity, @orgzone, @destzone, @ocountry, @dcountry, @orgcountry, @destcountry)"; //, @origin, @dest, @min, @rate, @rate, @basert, @frmwt, @towt, @cur, @validfrm, @validtill, @filename
OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=d:\documents and settings\axkhan2\desktop\437proj\");
try
{
OleDbCommand cmd = new OleDbCommand(query, con);
con.Open();
while(read.Read())
{
cmd.Parameters.Add(new OleDbParameter("@ln", Convert.ToInt32(read[0].ToString())));
cmd.Parameters.Add(new OleDbParameter("@orgcity", read[2].ToString()));
cmd.Parameters.Add(new OleDbParameter("@destcity", read[5].ToString()));
cmd.Parameters.Add(new OleDbParameter("@orgzone", read[4].ToString()));
cmd.Parameters.Add(new OleDbParameter("@destzone", read[7].ToString()));
cmd.Parameters.Add(new OleDbParameter("@ocountry", read[2].ToString()));
cmd.Parameters.Add(new OleDbParameter("@dcountry", read[6].ToString()));
cmd.Parameters.Add(new OleDbParameter("@orgcountry", "NL"));
cmd.Parameters.Add(new OleDbParameter("@destcountry", "FI"));
/*cmd.Parameters.Add(new OleDbParameter("origin", String.Empty));
cmd.Parameters.Add(new OleDbParameter("dest", String.Empty));
cmd.Parameters.Add(new OleDbParameter("min", double.Parse(read[10].ToString())));
cmd.Parameters.Add(new OleDbParameter("basert", null));
cmd.Parameters.Add(new OleDbParameter("rate", null));
cmd.Parameters.Add(new OleDbParameter("frmwt", null));
cmd.Parameters.Add(new OleDbParameter("towt", null));
cmd.Parameters.Add(new OleDbParameter("cur", null));
cmd.Parameters.Add(new OleDbParameter("validfrm", null));
cmd.Parameters.Add(new OleDbParameter("validtill", null));
*/
/*cmd.Parameters.Add(new OleDbParameter("rate", double.Parse(read["rate"].ToString())));
cmd.Parameters.Add(new OleDbParameter("frmwt", double.Parse(read["from_wt"].ToString())));
cmd.Parameters.Add(new OleDbParameter("towt", double.Parse(read["to_wt"].ToString())));
cmd.Parameters.Add(new OleDbParameter("cur", read["currency"].ToString()));
cmd.Parameters.Add(new OleDbParameter("validfrm", DateTime.Parse(read["valid_from"].ToString())));
cmd.Parameters.Add(new OleDbParameter("validtill", DateTime.Parse(read["valid_till"].ToString())));
*/
cmd.Parameters.Add(new OleDbParameter("validtill", "mytariff.xls"));
cmd.ExecuteNonQuery();
}
}
catch(OleDbException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
con.Close();
}
}
public Hashtable Read_Values(string[] Tag_Name, DateTime Time_Stamp)
{
Hashtable Result = new Hashtable();
string Tag_cond = "(FALSE"; // Makes it easier to add OR conditions.
foreach (string Tag in Tag_Name)
{
if (Sanitize(Tag))
{
Tag_cond += "\n OR NAME = '" + Tag + "'";
}
}
Tag_cond += ")";
Cmdr.CommandText =
@"SELECT
NAME, VALUE, STATUS
FROM
History
WHERE
" + Tag_cond + @"
AND TS > CAST('" + Time_Stamp.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND TS < CAST('" + Time_Stamp.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND PERIOD = 000:0:01.0;
";
#if DEBUG
DateTime Start_Time = DateTime.Now;
#endif
System.Data.Odbc.OdbcDataReader DR = Cmdr.ExecuteReader(System.Data.CommandBehavior.SingleResult);
while (DR.Read())
{
if (DR.GetValue(2).ToString() == "0") // if status is good
{
Result.Add(DR.GetValue(0).ToString(), DR.GetValue(1));
}
}
DR.Close();
#if DEBUG
double Run_Time = (DateTime.Now - Start_Time).TotalMilliseconds;
Console.WriteLine("Read {0} tags from IP21 in {1} ms", Tag_Name.Length, Run_Time);
foreach (DictionaryEntry p in Result)
{
Console.WriteLine("{0}: {1}", p.Key, p.Value);
}
#endif
return(Result);
}
private void ListTables(string Database)
{
if (OdbcCon.State == ConnectionState.Open)
{
OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT * FROM [mydb].[dbo].[user]", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
Console.WriteLine("Content in Table [" + Database + "].[dbo].[user] :\r\n");
while (OdbcDR.Read())
{
Console.WriteLine("Values >> " + OdbcDR[0] + " , " + OdbcDR[1] + " , " + OdbcDR[2] + " , " + OdbcDR[3] + " , " + OdbcDR[4] + "\r\n");
}
}
}
private static void PrintResults(OdbcDataReader reader) {
for (int ii = 0; ii < reader.FieldCount; ii += 1) {
System.Console.Write("{0}{1}",
reader.GetName(ii),
ii + 1 < reader.FieldCount ? "\t" : "\n");
}
while (reader.Read()) {
for (int ii = 0; ii < reader.FieldCount; ii += 1) {
System.Console.Write("{0}{1}",
reader.GetValue(ii),
ii + 1 < reader.FieldCount ? "\t" : "\n");
}
}
}
public static List<List<string>> convertOdbcDataToList(OdbcDataReader reader)
{
List<List<string>> lr = new List<List<string>>();
while(reader.Read())
{
List<string> lsItem = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
lsItem.Add(reader[i].ToString());
}
lr.Add(lsItem);
}
return lr;
}
public static void LVLoad(System.Windows.Forms.ListView LV,
System.Data.Odbc.OdbcDataReader reader,
bool bUpdateHeaders)
{
int i = 0, nFields = 0, nRows = 0;
string s;
try
{
LV.BeginUpdate();
LV.Sorting = SortOrder.None;
LV.Items.Clear();
if (bUpdateHeaders == true)
{
LV.Columns.Clear();
nFields = reader.FieldCount;
for (i = 0; i < nFields; i++)
{
LV.Columns.Add(reader.GetName(i));
}
}
nRows = 0;
while (reader.Read())
{
s = reader.GetValue(0).ToString();
LV.Items.Add(s);
for (i = 1; i < nFields; i++)
{
s = reader.GetValue(i).ToString();
LV.Items[nRows].SubItems.Add(s);
}
nRows += 1;
}
LV.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
LV.EndUpdate();
}catch (Exception ex) { MessageBox.Show(ex.Message); }
finally
{
}
}
// SQLGetRows
public List <string[]> SQLGetRows(string command)
{
List <string[]> rows = new List <string[]>();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string[] row = new string[sqlDataReader.FieldCount];
for (int i = 0; i < row.Length; i++)
{
row[i] = sqlDataReader[i].ToString();
}
rows.Add(row);
}
sqlDataReader.Close();
sqlDataReader = null;
return(rows);
}
public bool IsExists(string sQuery)
{
m_dataReader = null;
m_odbcCommand.CommandText = sQuery;
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (m_dataReader.Read())
return true;
}
catch
{
;
}
finally
{
CloseDB(1);
}
return false;
}
public DataSet ConvertDataReaderToDataSet(System.Data.Odbc.OdbcDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create data table in runtime
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
DataColumn column1 = new DataColumn("id", typeof(int));
dataTable.Columns.Add(column1);
DataColumn column2 = new DataColumn("dato", typeof(DateTime));
dataTable.Columns.Add(column2);
DataColumn column3 = new DataColumn("editor", typeof(string));
dataTable.Columns.Add(column3);
DataColumn column4 = new DataColumn("origin", typeof(int));
dataTable.Columns.Add(column4);
DataColumn column5 = new DataColumn("medarbejderid", typeof(string));
dataTable.Columns.Add(column5);
DataColumn column6 = new DataColumn("jobid", typeof(int));
dataTable.Columns.Add(column6);
DataColumn column7 = new DataColumn("aktnavn", typeof(string));
dataTable.Columns.Add(column7);
DataColumn column8 = new DataColumn("timer", typeof(double));
dataTable.Columns.Add(column8);
DataColumn column9 = new DataColumn("tdato", typeof(DateTime));
dataTable.Columns.Add(column9);
DataColumn column10 = new DataColumn("lto", typeof(string));
dataTable.Columns.Add(column10);
DataColumn column11 = new DataColumn("timerkom", typeof(string));
dataTable.Columns.Add(column11);
dataSet.Tables.Add(dataTable);
// Fill the data table from reader data
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
dataRow["id"] = reader.GetValue(0);
dataRow["dato"] = reader.GetValue(1);
dataRow["editor"] = reader.GetValue(2);
dataRow["origin"] = reader.GetValue(3);
dataRow["medarbejderid"] = reader.GetValue(4);
dataRow["jobid"] = reader.GetValue(5);
dataRow["aktnavn"] = reader.GetValue(6);
dataRow["timer"] = reader.GetValue(7);
dataRow["tdato"] = reader.GetValue(8);
dataRow["lto"] = reader.GetValue(9); //10 Overfort omited here
dataRow["timerkom"] = reader.GetValue(10); //11
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}while (reader.NextResult());
return(dataSet);
}
public bool getUserLogginStatus(int itsId)
{
try
{
char itsChar;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT status FROM users WHERE userID=\'" + itsId + "\'";
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsChar = itsDataReader.GetChar(0);
if(itsChar == 'Y')
{
itsCommand.Dispose();
itsDataReader.Close();
return true;
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
}
else
{
itsDataReader.Close();
itsCommand.Dispose();
return false;
}
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return false;
}
internal void LoadApprovalInward(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("kpsid");
dt.Columns.Add("kpid");
dt.Columns.Add("id");
dt.Columns.Add("Select", System.Type.GetType("System.Boolean"));
dt.Columns.Add("DC Number");
dt.Columns.Add("Invoice Number");
dt.Columns.Add("Rec Rolls", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Yarn", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Date");
dt.Columns.Add("Fabric");
// dt.Columns.Add("Invoice");
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_YarnInwardLog.img as imag,nexusGarments_KnittingProcess.id as kpsid,nexusGarments_KnittingProgram.id as kpid ,nexusGarments_YarnInwardLog.recyarn,nexusGarments_YarnInwardLog.id,nexusGarments_YarnInwardLog.recdate, nexusGarments_YarnInwardLog.recrolls, nexusGarments_YarnInwardLog.recweight, nexusGarments_KnittingProcess.DCNo,nexusGarments_YarnInwardLog.invoiceno, nexusGarments_Master_fabric.fabricName FROM nexusGarments_YarnInwardLog INNER JOIN nexusGarments_KnittingProcess ON nexusGarments_YarnInwardLog.dcid = nexusGarments_KnittingProcess.id INNER JOIN nexusGarments_KnittingProgram ON nexusGarments_KnittingProcess.mapid = nexusGarments_KnittingProgram.id INNER JOIN nexusGarments_Master_fabric ON nexusGarments_KnittingProgram.Fabricid = nexusGarments_Master_fabric.fabricId WHERE (nexusGarments_YarnInwardLog.isApproved = 'False') ";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
//byte[] imageData = (byte[])m_dataReader["imag"];
////Initialize image variable
//Image newImage;
////Read image data into a memory stream
//using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
//{
// ms.Write(imageData, 0, imageData.Length);
// //Set image variable value using memory stream.
// newImage = Image.FromStream(ms, true);
//}
dt.Rows.Add(m_dataReader["kpsid"].ToString().Trim(), m_dataReader["kpid"].ToString().Trim(), m_dataReader["id"].ToString().Trim(), "False", m_dataReader["dcno"].ToString().Trim(), m_dataReader["invoiceno"].ToString().Trim(), m_dataReader["recrolls"].ToString().Trim(), m_dataReader["recweight"].ToString().Trim(), m_dataReader["recyarn"].ToString().Trim(), m_dataReader["recdate"].ToString().Trim(), m_dataReader["fabricname"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
internal void LoadBank(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("id");
dt.Columns.Add("Buyer");
m_dataReader = null;
m_odbcCommand.CommandText = "Select id,BankName from nexusGarments_Master_Bank";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
dt.Rows.Add(m_dataReader["id"].ToString().Trim(), m_dataReader["BankName"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
internal void knittingdc(string dt)
{
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_Master_Style.ProcessStyle, nexusGarments_KnittingProcess.id, nexusGarments_KnittingProcess.DCNo, nexusGarments_KnittingProcess.delWeight, nexusGarments_KnittingProcess.delBags, nexusGarments_KnittingProcess.deldate, nexusGarments_Master_YarnSupplier.Supplier, nexusGarments_Master_KnittingCompany.Company, nexusGarments_KnittingProgram.dia, nexusGarments_KnittingProgram.gsm, nexusGarments_KnittingProgram.gg, nexusGarments_KnittingProgram.ll, nexusGarments_Master_fabric.fabricName, nexusGarmrnts_Master_Counts.Counts, nexusGarments_Master_KnittingCompany.Address1 ,(SELECT Empname FROM nexusGarments_Master_Employee where id=(select preparedby from nexusGarments_KnittingProcess where id=" + dt + ")) as preparedby,(SELECT EmpSignature FROM nexusGarments_Master_Employee where id=(select preparedby from nexusGarments_KnittingProcess where id=" + dt + ")) as preparedSignature,(SELECT Empname FROM nexusGarments_Master_Employee where id=(select ApprovedBy from nexusGarments_KnittingProcess where id=" + dt + ")) as approvedby,(SELECT EmpSignature FROM nexusGarments_Master_Employee where id=(select approvedby from nexusGarments_KnittingProcess where id=" + dt + ")) as approvedSignature FROM nexusGarments_KnittingProcess INNER JOIN nexusGarments_KnittingProgram ON nexusGarments_KnittingProcess.mapid = nexusGarments_KnittingProgram.id INNER JOIN nexusGarments_Master_KnittingCompany ON nexusGarments_KnittingProgram.Companyid = nexusGarments_Master_KnittingCompany.id INNER JOIN nexusGarments_YarnSource ON nexusGarments_KnittingProgram.poid = nexusGarments_YarnSource.id INNER JOIN nexusGarments_YarnPurchase ON nexusGarments_YarnSource.id = nexusGarments_YarnPurchase.Ysource INNER JOIN nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id INNER JOIN nexusGarments_Master_fabric ON nexusGarments_KnittingProgram.Fabricid = nexusGarments_Master_fabric.fabricId INNER JOIN nexusGarmrnts_Master_Counts ON nexusGarments_YarnPurchase.CountsId = nexusGarmrnts_Master_Counts.id INNER JOIN nexusGarments_Master_Style ON nexusGarments_YarnPurchase.StyleId = nexusGarments_Master_Style.id where nexusGarments_KnittingProcess.id='" + dt + "' ORDER BY nexusGarments_KnittingProcess.id DESC";
try
{
supplier = "Supplier";
counts = "Counts";
//Report.KnittingDC sd = new NaveenaAccounts.Report.KnittingDC();
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
supplier = supplier +","+ m_dataReader["supplier"].ToString().Trim();
counts = counts +","+ m_dataReader["counts"].ToString().Trim();
delbags = m_dataReader["delBags"].ToString().Trim();
delweight = m_dataReader["delweight"].ToString().Trim();
dia = m_dataReader["dia"].ToString().Trim();
ll = m_dataReader["ll"].ToString().Trim();
gg = m_dataReader["gg"].ToString().Trim();
company = m_dataReader["Company"].ToString().Trim();
gsm = m_dataReader["gsm"].ToString().Trim();
style = m_dataReader["processstyle"].ToString().Trim();
fabric= m_dataReader["fabricname"].ToString().Trim();
dcno = m_dataReader["dcno"].ToString().Trim();
preparedby = m_dataReader["preparedby"].ToString().Trim();
Approved = m_dataReader["approvedby"].ToString().Trim();
byte[] Presign = (byte[])m_dataReader["preparedSignature"];
byte[] appSign = (byte[])m_dataReader["approvedSignature"];
//Initialize image variable
//Read image data into a memory stream
using (MemoryStream ms = new MemoryStream(Presign, 0, Presign.Length))
{
ms.Write(Presign, 0, Presign.Length);
//Set image variable value using memory stream.
PreparedSign = Image.FromStream(ms, true);
}
using (MemoryStream ms = new MemoryStream(appSign, 0, appSign.Length))
{
ms.Write(appSign, 0, appSign.Length);
//Set image variable value using memory stream.
ApprovedSign = Image.FromStream(ms, true);
}
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
}
//sd.barcodeDCNo.Text = dcno;
//sd.lblbags.Text = delbags;
//sd.lblCompany.Text = company;
//sd.lblCounts.Text = counts;
//sd.lblDia.Text = dia;
//sd.lblFabric.Text = fabric;
//sd.lblGG.Text = gg;
//sd.lblGSM.Text = gsm;
//sd.lblLL.Text = ll;
//sd.lblMill.Text = supplier;
//sd.lblStyle.Text = style;
//sd.lblWeight.Text = delweight;
//sd.lblPreparedBy.Text = preparedby;
//sd.lblApprovedBy.Text = Approved;
//sd.ptrApprovedBySign.Image = ApprovedSign;
//sd.ptrPreparedBySign.Image = PreparedSign;
//sd.ShowPreview();
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
internal void KnittingReport(DataTable dt)
{
bool isset=false;
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("DC Number");
dt.Columns.Add("Date", System.Type.GetType("System.DateTime"));
dt.Columns.Add("Style");
dt.Columns.Add("Counts");
dt.Columns.Add("Yarn Supplier");
dt.Columns.Add("Knitting Company");
dt.Columns.Add("Del Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Balance", System.Type.GetType("System.Double"));
dt.Columns.Add("Prec");
dt.Columns.Add("Closed", System.Type.GetType("System.Boolean"));
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_KnittingProcess.recweight,nexusGarments_KnittingProcess.isclosed, nexusGarments_KnittingProcess.DCNo, nexusGarments_KnittingProcess.deldate, nexusGarments_Master_Style.ProcessStyle, nexusGarmrnts_Master_Counts.Counts, nexusGarments_Master_YarnSupplier.Supplier, nexusGarments_Master_KnittingCompany.Company, nexusGarments_KnittingProcess.delWeight, nexusGarments_KnittingProcess.delBags, nexusGarments_Master_KnittingCompany.id FROM nexusGarments_KnittingProcess INNER JOIN nexusGarments_KnittingProgram ON nexusGarments_KnittingProcess.mapid = nexusGarments_KnittingProgram.id INNER JOIN nexusGarments_ProcessWeight ON nexusGarments_KnittingProgram.Ksource = nexusGarments_ProcessWeight.id INNER JOIN nexusGarments_YarnSource ON nexusGarments_KnittingProgram.poid = nexusGarments_YarnSource.id INNER JOIN nexusGarments_YarnPurchase ON nexusGarments_YarnSource.id = nexusGarments_YarnPurchase.Ysource INNER JOIN nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id INNER JOIN nexusGarmrnts_Master_Counts ON nexusGarments_YarnPurchase.CountsId = nexusGarmrnts_Master_Counts.id INNER JOIN nexusGarments_Master_Style ON nexusGarments_YarnPurchase.styleid = nexusGarments_Master_Style.id INNER JOIN nexusGarments_Master_KnittingCompany ON nexusGarments_KnittingProgram.Companyid = nexusGarments_Master_KnittingCompany.id ";
// if (Company != "")
// {
// isset = true;
//m_odbcCommand.CommandText = m_odbcCommand.CommandText + " nexusGarments_KnittingProgram.companyid =" +Company;
// }
// if (fromdate != "")
// {
// if (isset)
// {
// m_odbcCommand.CommandText = m_odbcCommand.CommandText + " and ";
// }
// m_odbcCommand.CommandText = m_odbcCommand.CommandText + " nexusGarments_KnittingProcess.deldate >=" + fromdate + "'";
// isset = true;
// }
// if (todate != "")
// {
// if (isset)
// {
// m_odbcCommand.CommandText = m_odbcCommand.CommandText + " and ";
// }
// m_odbcCommand.CommandText = m_odbcCommand.CommandText + " nexusGarments_KnittingProcess.deldate <=" + todate + "'";
// isset = true;
// }
string id = "";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
if (id != m_dataReader["dcno"].ToString().Trim())
{
id = m_dataReader["dcno"].ToString().Trim();
dt.Rows.Add(m_dataReader["dcno"].ToString().Trim(), m_dataReader["deldate"].ToString().Trim().Substring(0, 10), m_dataReader["processstyle"].ToString().Trim(), m_dataReader["counts"].ToString().Trim(), m_dataReader["supplier"].ToString().Trim(), m_dataReader["company"].ToString().Trim(), m_dataReader["delweight"].ToString().Trim(), m_dataReader["recweight"].ToString().Trim(), Convert.ToDouble(m_dataReader["delweight"].ToString().Trim()) - Convert.ToDouble(m_dataReader["recweight"].ToString().Trim()), Convert.ToInt32(Convert.ToDouble(m_dataReader["recweight"].ToString().Trim()) * 100 / Convert.ToDouble(m_dataReader["delweight"].ToString().Trim())), m_dataReader["isClosed"].ToString().Trim());
}
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
private static void DataTableFromDataReaderDataTypes(DataTable dataTypesTable, OdbcDataReader dataReader, OdbcConnection connection)
{
DataTable?schemaTable;
//
// Build a DataTable from the reader
schemaTable = dataReader.GetSchemaTable();
// vstfdevdiv:479715 Handle cases where reader is empty
if (null == schemaTable)
{
throw ADP.OdbcNoTypesFromProvider();
}
object[] getTypeInfoValues = new object[schemaTable.Rows.Count];
DataRow dataTypesRow;
DataColumn typeNameColumn = dataTypesTable.Columns[DbMetaDataColumnNames.TypeName] !;
DataColumn providerDbTypeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.ProviderDbType] !;
DataColumn columnSizeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.ColumnSize] !;
DataColumn createParametersColumn = dataTypesTable.Columns[DbMetaDataColumnNames.CreateParameters] !;
DataColumn dataTypeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.DataType] !;
DataColumn isAutoIncermentableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsAutoIncrementable] !;
DataColumn isCaseSensitiveColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsCaseSensitive] !;
DataColumn isFixedLengthColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedLength] !;
DataColumn isFixedPrecisionScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedPrecisionScale] !;
DataColumn isLongColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsLong] !;
DataColumn isNullableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsNullable] !;
DataColumn isSearchableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchable] !;
DataColumn isSearchableWithLikeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchableWithLike] !;
DataColumn isUnsignedColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsUnsigned] !;
DataColumn maximumScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.MaximumScale] !;
DataColumn minimumScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.MinimumScale] !;
DataColumn literalPrefixColumn = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralPrefix] !;
DataColumn literalSuffixColumn = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralSuffix] !;
DataColumn SQLTypeNameColumn = dataTypesTable.Columns[OdbcMetaDataColumnNames.SQLType] !;
const int indexTYPE_NAME = 0;
const int indexDATA_TYPE = 1;
const int indexCOLUMN_SIZE = 2;
const int indexCREATE_PARAMS = 5;
const int indexAUTO_UNIQUE_VALUE = 11;
const int indexCASE_SENSITIVE = 7;
const int indexFIXED_PREC_SCALE = 10;
const int indexNULLABLE = 6;
const int indexSEARCHABLE = 8;
const int indexUNSIGNED_ATTRIBUTE = 9;
const int indexMAXIMUM_SCALE = 14;
const int indexMINIMUM_SCALE = 13;
const int indexLITERAL_PREFIX = 3;
const int indexLITERAL_SUFFIX = 4;
const int SQL_DATE_V2 = 9;
const int SQL_TIME_V2 = 10;
TypeMap?typeMap;
while (dataReader.Read())
{
dataReader.GetValues(getTypeInfoValues);
dataTypesRow = dataTypesTable.NewRow();
ODBC32.SQL_TYPE sqlType;
dataTypesRow[typeNameColumn] = getTypeInfoValues[indexTYPE_NAME];
dataTypesRow[SQLTypeNameColumn] = getTypeInfoValues[indexDATA_TYPE];
sqlType = (ODBC32.SQL_TYPE)(int) Convert.ChangeType(getTypeInfoValues[indexDATA_TYPE], typeof(int), null);
// if the driver is pre version 3 and it returned the v2 SQL_DATE or SQL_TIME types they need
// to be mapped to their v3 equivalent
if (connection.IsV3Driver == false)
{
if ((int)sqlType == SQL_DATE_V2)
{
sqlType = ODBC32.SQL_TYPE.TYPE_DATE;
}
else if ((int)sqlType == SQL_TIME_V2)
{
sqlType = ODBC32.SQL_TYPE.TYPE_TIME;
}
}
try
{
typeMap = TypeMap.FromSqlType(sqlType);
}
// FromSqlType will throw an argument exception if it does not recognize the SqlType.
// This is not an error since the GetTypeInfo DATA_TYPE may be a SQL data type or a driver specific
// type. If there is no TypeMap for the type its not an error but it will degrade our level of
// understanding of/ support for the type.
catch (ArgumentException)
{
typeMap = null;
}
// if we have a type map we can determine the dbType and the CLR type if not leave them null
if (typeMap != null)
{
dataTypesRow[providerDbTypeColumn] = typeMap._odbcType;
dataTypesRow[dataTypeColumn] = typeMap._type.FullName;
// setting isLong and isFixedLength only if we have a type map because for provider
// specific types we have no idea what the types attributes are if GetTypeInfo did not
// tell us
switch (sqlType)
{
case ODBC32.SQL_TYPE.LONGVARCHAR:
case ODBC32.SQL_TYPE.WLONGVARCHAR:
case ODBC32.SQL_TYPE.LONGVARBINARY:
case ODBC32.SQL_TYPE.SS_XML:
dataTypesRow[isLongColumn] = true;
dataTypesRow[isFixedLengthColumn] = false;
break;
case ODBC32.SQL_TYPE.VARCHAR:
case ODBC32.SQL_TYPE.WVARCHAR:
case ODBC32.SQL_TYPE.VARBINARY:
dataTypesRow[isLongColumn] = false;
dataTypesRow[isFixedLengthColumn] = false;
break;
case ODBC32.SQL_TYPE.CHAR:
case ODBC32.SQL_TYPE.WCHAR:
case ODBC32.SQL_TYPE.DECIMAL:
case ODBC32.SQL_TYPE.NUMERIC:
case ODBC32.SQL_TYPE.SMALLINT:
case ODBC32.SQL_TYPE.INTEGER:
case ODBC32.SQL_TYPE.REAL:
case ODBC32.SQL_TYPE.FLOAT:
case ODBC32.SQL_TYPE.DOUBLE:
case ODBC32.SQL_TYPE.BIT:
case ODBC32.SQL_TYPE.TINYINT:
case ODBC32.SQL_TYPE.BIGINT:
case ODBC32.SQL_TYPE.TYPE_DATE:
case ODBC32.SQL_TYPE.TYPE_TIME:
case ODBC32.SQL_TYPE.TIMESTAMP:
case ODBC32.SQL_TYPE.TYPE_TIMESTAMP:
case ODBC32.SQL_TYPE.GUID:
case ODBC32.SQL_TYPE.SS_VARIANT:
case ODBC32.SQL_TYPE.SS_UTCDATETIME:
case ODBC32.SQL_TYPE.SS_TIME_EX:
case ODBC32.SQL_TYPE.BINARY:
dataTypesRow[isLongColumn] = false;
dataTypesRow[isFixedLengthColumn] = true;
break;
case ODBC32.SQL_TYPE.SS_UDT:
default:
// for User defined types don't know if its long or if it is
// variable length or not so leave the fields null
break;
}
}
dataTypesRow[columnSizeColumn] = getTypeInfoValues[indexCOLUMN_SIZE];
dataTypesRow[createParametersColumn] = getTypeInfoValues[indexCREATE_PARAMS];
if ((getTypeInfoValues[indexAUTO_UNIQUE_VALUE] == DBNull.Value) ||
(Convert.ToInt16(getTypeInfoValues[indexAUTO_UNIQUE_VALUE], null) == 0))
{
dataTypesRow[isAutoIncermentableColumn] = false;
}
else
{
dataTypesRow[isAutoIncermentableColumn] = true;
}
dataTypesRow[isCaseSensitiveColumn] = BooleanFromODBC(getTypeInfoValues[indexCASE_SENSITIVE]);
dataTypesRow[isFixedPrecisionScaleColumn] = BooleanFromODBC(getTypeInfoValues[indexFIXED_PREC_SCALE]);
if (getTypeInfoValues[indexNULLABLE] != DBNull.Value)
{
//Use Convert.ToInt16 instead of direct cast to short because the value will be Int32 in some cases
switch ((ODBC32.SQL_NULLABILITY)Convert.ToInt16(getTypeInfoValues[indexNULLABLE], null))
{
case ODBC32.SQL_NULLABILITY.NO_NULLS:
dataTypesRow[isNullableColumn] = false;
break;
case ODBC32.SQL_NULLABILITY.NULLABLE:
dataTypesRow[isNullableColumn] = true;
break;
case ODBC32.SQL_NULLABILITY.UNKNOWN:
dataTypesRow[isNullableColumn] = DBNull.Value;
break;
}
}
if (DBNull.Value != getTypeInfoValues[indexSEARCHABLE])
{
//Use Convert.ToInt16 instead of direct cast to short because the value will be Int32 in some cases
short searchableValue = Convert.ToInt16(getTypeInfoValues[indexSEARCHABLE], null);
switch (searchableValue)
{
case (short)ODBC32.SQL_SEARCHABLE.UNSEARCHABLE:
dataTypesRow[isSearchableColumn] = false;
dataTypesRow[isSearchableWithLikeColumn] = false;
break;
case (short)ODBC32.SQL_SEARCHABLE.LIKE_ONLY:
dataTypesRow[isSearchableColumn] = false;
dataTypesRow[isSearchableWithLikeColumn] = true;
break;
case (short)ODBC32.SQL_SEARCHABLE.ALL_EXCEPT_LIKE:
dataTypesRow[isSearchableColumn] = true;
dataTypesRow[isSearchableWithLikeColumn] = false;
break;
case (short)ODBC32.SQL_SEARCHABLE.SEARCHABLE:
dataTypesRow[isSearchableColumn] = true;
dataTypesRow[isSearchableWithLikeColumn] = true;
break;
}
}
dataTypesRow[isUnsignedColumn] = BooleanFromODBC(getTypeInfoValues[indexUNSIGNED_ATTRIBUTE]);
//For assignment to the DataSet, don't cast the data types -- let the DataSet take care of any conversion
if (getTypeInfoValues[indexMAXIMUM_SCALE] != DBNull.Value)
{
dataTypesRow[maximumScaleColumn] = getTypeInfoValues[indexMAXIMUM_SCALE];
}
if (getTypeInfoValues[indexMINIMUM_SCALE] != DBNull.Value)
{
dataTypesRow[minimumScaleColumn] = getTypeInfoValues[indexMINIMUM_SCALE];
}
if (getTypeInfoValues[indexLITERAL_PREFIX] != DBNull.Value)
{
dataTypesRow[literalPrefixColumn] = getTypeInfoValues[indexLITERAL_PREFIX];
}
if (getTypeInfoValues[indexLITERAL_SUFFIX] != DBNull.Value)
{
dataTypesRow[literalSuffixColumn] = getTypeInfoValues[indexLITERAL_SUFFIX];
}
dataTypesTable.Rows.Add(dataTypesRow);
}
}
internal void washingdc(string dt)
{
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_knittingprogram.dia,nexusGarments_PrintingColor.ColorName, nexusGarments_WashingProcess.deldate, nexusGarments_WashingProcess.id, nexusGarments_WashingProcess.dcno, nexusGarments_KWashingCompany.Company, nexusGarments_Master_fabric.fabricName, nexusGarments_Master_Style.ProcessStyle, nexusGarments_WashingProcess.delWeight, nexusGarments_WashingProcess.delrolls, nexusGarments_Master_YarnSupplier.Supplier, nexusGarmrnts_Master_Counts.Counts ,(SELECT Empname FROM nexusGarments_Master_Employee where id=(select preparedby from nexusGarments_WashingProcess where dcno=" + dt + ")) as preparedby,(SELECT EmpSignature FROM nexusGarments_Master_Employee where id=(select preparedby from nexusGarments_WashingProcess where dcno=" + dt + ")) as preparedSignature,(SELECT Empname FROM nexusGarments_Master_Employee where id=(select ApprovedBy from nexusGarments_WashingProcess where dcno=" + dt + ")) as approvedby,(SELECT EmpSignature FROM nexusGarments_Master_Employee where id=(select approvedby from nexusGarments_WashingProcess where dcno=" + dt + ")) as approvedSignature FROM nexusGarments_WashingProcess INNER JOIN nexusGarments_WashingProgram ON nexusGarments_WashingProcess.mapid = nexusGarments_WashingProgram.id INNER JOIN nexusGarments_PrintingColor ON nexusGarments_WashingProgram.Colorid = nexusGarments_PrintingColor.id INNER JOIN nexusGarments_KWashingCompany ON nexusGarments_WashingProgram.dyingComp = nexusGarments_KWashingCompany.id INNER JOIN nexusGarments_Master_fabric ON nexusGarments_WashingProgram.fabricid = nexusGarments_Master_fabric.fabricId INNER JOIN nexusGarments_Master_Style ON nexusGarments_WashingProgram.styleid = nexusGarments_Master_Style.id INNER JOIN nexusGarments_ProcessWeight ON nexusGarments_WashingProgram.ksource = nexusGarments_ProcessWeight.id INNER JOIN nexusGarments_KnittingProgram ON nexusGarments_ProcessWeight.id = nexusGarments_KnittingProgram.Ksource INNER JOIN nexusGarments_YarnSource ON nexusGarments_KnittingProgram.poid = nexusGarments_YarnSource.id INNER JOIN nexusGarments_YarnPurchase ON nexusGarments_YarnSource.id = nexusGarments_YarnPurchase.Ysource INNER JOIN nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id INNER JOIN nexusGarmrnts_Master_Counts ON nexusGarments_YarnPurchase.CountsId = nexusGarmrnts_Master_Counts.id where nexusGarments_WashingProcess.dcno=" + dt + " ORDER BY nexusGarments_Master_YarnSupplier.supplier DESC";
try
{
supplier = "";string suppt = "";
string Fcolor = "";
counts = ""; string countst = "";
//Report.DyingDC sd = new NaveenaAccounts.Report.DyingDC();
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
if (suppt != m_dataReader["supplier"].ToString().Trim())
{
suppt = m_dataReader["supplier"].ToString().Trim();
supplier = supplier + "," + m_dataReader["supplier"].ToString().Trim();
}
if (countst != m_dataReader["counts"].ToString().Trim())
{
countst = m_dataReader["counts"].ToString().Trim();
counts = counts + "," + m_dataReader["counts"].ToString().Trim();
}
delbags = m_dataReader["delrolls"].ToString().Trim();
delweight = m_dataReader["delweight"].ToString().Trim();
dia = m_dataReader["dia"].ToString().Trim();
company = m_dataReader["Company"].ToString().Trim();
Fcolor = m_dataReader["colorname"].ToString().Trim();
style = m_dataReader["processstyle"].ToString().Trim();
fabric = m_dataReader["fabricname"].ToString().Trim();
dcno = m_dataReader["dcno"].ToString().Trim();
preparedby = m_dataReader["preparedby"].ToString().Trim();
Approved = m_dataReader["approvedby"].ToString().Trim();
byte[] Presign = (byte[])m_dataReader["preparedSignature"];
byte[] appSign = (byte[])m_dataReader["approvedSignature"];
//Initialize image variable
//Read image data into a memory stream
using (MemoryStream ms = new MemoryStream(Presign, 0, Presign.Length))
{
ms.Write(Presign, 0, Presign.Length);
//Set image variable value using memory stream.
PreparedSign = Image.FromStream(ms, true);
}
using (MemoryStream ms = new MemoryStream(appSign, 0, appSign.Length))
{
ms.Write(appSign, 0, appSign.Length);
//Set image variable value using memory stream.
ApprovedSign = Image.FromStream(ms, true);
}
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
}
//XtraMessageBox
//sd.barcodeDCNo.Text = dt;
//sd.lblRolls.Text = delbags;
//sd.lblCompany.Text = company;
//sd.lblSupplier.Text = supplier;
//sd.lblCounts.Text = counts;
//sd.lblDia.Text = dia;
//sd.lblFabric.Text = fabric;
//sd.lblColor.Text = Fcolor;
//sd.lblStyle.Text = style;
//sd.lblWeight.Text = delweight;
//sd.lblPreparedBy.Text = preparedby;
//sd.lblApprovedBy.Text = Approved;
//sd.ptrApprovedBySign.Image = ApprovedSign;
//sd.ptrPreparedBySign.Image = PreparedSign;
//sd.lblLabTipCap.Visible = false;
//sd.lblTooltip.Visible = false;
//sd.ShowPreview();
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
// DeriveParametersFromStoredProcedure (
// OdbcConnection connection,
// OdbcCommand command);
//
// Uses SQLProcedureColumns to create an array of OdbcParameters
//
private static OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command)
{
List <OdbcParameter> rParams = new List <OdbcParameter>();
// following call ensures that the command has a statement handle allocated
CMDWrapper cmdWrapper = command.GetStatementHandle();
OdbcStatementHandle hstmt = cmdWrapper.StatementHandle !;
int cColsAffected;
// maps an enforced 4-part qualified string as follows
// parts[0] = null - ignored but removal would be a run-time breaking change from V1.0
// parts[1] = CatalogName (optional, may be null)
// parts[2] = SchemaName (optional, may be null)
// parts[3] = ProcedureName
//
string quote = connection.QuoteChar(ADP.DeriveParameters);
string?[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, SR.ODBC_ODBCCommandText, false);
if (null == parts[3])
{ // match Everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace
parts[3] = command.CommandText;
}
// note: native odbc appears to ignore all but the procedure name
ODBC32.SQLRETURN retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null);
// Note: the driver does not return an error if the given stored procedure does not exist
// therefore we cannot handle that case and just return not parameters.
if (ODBC32.SQLRETURN.SUCCESS != retcode)
{
connection.HandleError(hstmt, retcode);
}
using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default))
{
reader.FirstResult();
cColsAffected = reader.FieldCount;
// go through the returned rows and filter out relevant parameter data
//
while (reader.Read())
{
// devnote: column types are specified in the ODBC Programmer's Reference
// COLUMN_TYPE Smallint 16bit
// COLUMN_SIZE Integer 32bit
// DECIMAL_DIGITS Smallint 16bit
// NUM_PREC_RADIX Smallint 16bit
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1);
switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1))
{
case ODBC32.SQL_PARAM.INPUT:
parameter.Direction = ParameterDirection.Input;
break;
case ODBC32.SQL_PARAM.OUTPUT:
parameter.Direction = ParameterDirection.Output;
break;
case ODBC32.SQL_PARAM.INPUT_OUTPUT:
parameter.Direction = ParameterDirection.InputOutput;
break;
case ODBC32.SQL_PARAM.RETURN_VALUE:
parameter.Direction = ParameterDirection.ReturnValue;
break;
default:
Debug.Fail("Unexpected Parametertype while DeriveParamters");
break;
}
parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType;
parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE - 1);
switch (parameter.OdbcType)
{
case OdbcType.Decimal:
case OdbcType.Numeric:
parameter.ScaleInternal = (byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS - 1);
parameter.PrecisionInternal = (byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX - 1);
break;
}
rParams.Add(parameter);
}
}
retcode = hstmt.CloseCursor();
return(rParams.ToArray());
}
private void DataTableFromDataReaderDataTypes(DataTable dataTypesTable, OdbcDataReader dataReader, OdbcConnection connection) {
DataTable schemaTable = null;
//
// Build a DataTable from the reader
schemaTable = dataReader.GetSchemaTable();
// vstfdevdiv:479715 Handle cases where reader is empty
if (null == schemaTable) {
throw ADP.OdbcNoTypesFromProvider();
}
object[] getTypeInfoValues = new object[schemaTable.Rows.Count];
DataRow dataTypesRow;
DataColumn typeNameColumn = dataTypesTable.Columns[DbMetaDataColumnNames.TypeName];
DataColumn providerDbTypeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.ProviderDbType];
DataColumn columnSizeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.ColumnSize];
DataColumn createParametersColumn = dataTypesTable.Columns[DbMetaDataColumnNames.CreateParameters];
DataColumn dataTypeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.DataType];
DataColumn isAutoIncermentableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsAutoIncrementable];
DataColumn isCaseSensitiveColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsCaseSensitive];
DataColumn isFixedLengthColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedLength];
DataColumn isFixedPrecisionScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedPrecisionScale];
DataColumn isLongColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsLong];
DataColumn isNullableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsNullable];
DataColumn isSearchableColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchable];
DataColumn isSearchableWithLikeColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchableWithLike];
DataColumn isUnsignedColumn = dataTypesTable.Columns[DbMetaDataColumnNames.IsUnsigned];
DataColumn maximumScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.MaximumScale];
DataColumn minimumScaleColumn = dataTypesTable.Columns[DbMetaDataColumnNames.MinimumScale];
DataColumn literalPrefixColumn = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralPrefix];
DataColumn literalSuffixColumn = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralSuffix];
DataColumn SQLTypeNameColumn = dataTypesTable.Columns[OdbcMetaDataColumnNames.SQLType];
const int indexTYPE_NAME = 0;
const int indexDATA_TYPE = 1;
const int indexCOLUMN_SIZE = 2;
const int indexCREATE_PARAMS = 5;
const int indexAUTO_UNIQUE_VALUE = 11;
const int indexCASE_SENSITIVE = 7;
const int indexFIXED_PREC_SCALE = 10;
const int indexNULLABLE = 6;
const int indexSEARCHABLE = 8;
const int indexUNSIGNED_ATTRIBUTE = 9;
const int indexMAXIMUM_SCALE = 14;
const int indexMINIMUM_SCALE = 13;
const int indexLITERAL_PREFIX = 3;
const int indexLITERAL_SUFFIX = 4;
const int SQL_DATE_V2 = 9;
const int SQL_TIME_V2 = 10;
TypeMap typeMap ;
while (dataReader.Read()) {
dataReader.GetValues(getTypeInfoValues);
dataTypesRow = dataTypesTable.NewRow();
ODBC32.SQL_TYPE sqlType;
dataTypesRow[typeNameColumn] = getTypeInfoValues[indexTYPE_NAME];
dataTypesRow[SQLTypeNameColumn] = getTypeInfoValues[indexDATA_TYPE];
sqlType = (ODBC32.SQL_TYPE)(Int32) Convert.ChangeType(getTypeInfoValues[indexDATA_TYPE],
typeof(Int32),
(System.IFormatProvider)null);
// if the driver is pre version 3 and it returned the v2 SQL_DATE or SQL_TIME types they need
// to be mapped to thier v3 equlivants
if (connection.IsV3Driver == false) {
if ((int)sqlType == SQL_DATE_V2) {
sqlType = ODBC32.SQL_TYPE.TYPE_DATE;
}
else if ((int)sqlType == SQL_TIME_V2) {
sqlType = ODBC32.SQL_TYPE.TYPE_TIME;
}
}
try {
typeMap = TypeMap.FromSqlType(sqlType);
}
// FromSqlType will throw an argument exception if it does not recognize the SqlType.
// This is not an error since the GetTypeInfo DATA_TYPE may be a SQL data type or a driver specific
// type. If there is no TypeMap for the type its not an error but it will degrade our level of
// understanding of/ support for the type.
catch (ArgumentException) {
typeMap = null;
}
// if we have a type map we can determine the dbType and the CLR type if not leave them null
if (typeMap != null) {
dataTypesRow[providerDbTypeColumn] = typeMap._odbcType;
dataTypesRow[dataTypeColumn] = typeMap._type.FullName;
// setting isLong and isFixedLength only if we have a type map because for provider
// specific types we have no idea what the types attributes are if GetTypeInfo did not
// tell us
switch (sqlType) {
case ODBC32.SQL_TYPE.LONGVARCHAR:
case ODBC32.SQL_TYPE.WLONGVARCHAR:
case ODBC32.SQL_TYPE.LONGVARBINARY:
case ODBC32.SQL_TYPE.SS_XML:
dataTypesRow[isLongColumn] = true;
dataTypesRow[isFixedLengthColumn] = false;
break;
case ODBC32.SQL_TYPE.VARCHAR:
case ODBC32.SQL_TYPE.WVARCHAR:
case ODBC32.SQL_TYPE.VARBINARY:
dataTypesRow[isLongColumn] = false;
dataTypesRow[isFixedLengthColumn] = false;
break;
case ODBC32.SQL_TYPE.CHAR:
case ODBC32.SQL_TYPE.WCHAR:
case ODBC32.SQL_TYPE.DECIMAL:
case ODBC32.SQL_TYPE.NUMERIC:
case ODBC32.SQL_TYPE.SMALLINT:
case ODBC32.SQL_TYPE.INTEGER:
case ODBC32.SQL_TYPE.REAL:
case ODBC32.SQL_TYPE.FLOAT:
case ODBC32.SQL_TYPE.DOUBLE:
case ODBC32.SQL_TYPE.BIT:
case ODBC32.SQL_TYPE.TINYINT:
case ODBC32.SQL_TYPE.BIGINT:
case ODBC32.SQL_TYPE.TYPE_DATE:
case ODBC32.SQL_TYPE.TYPE_TIME:
case ODBC32.SQL_TYPE.TIMESTAMP:
case ODBC32.SQL_TYPE.TYPE_TIMESTAMP:
case ODBC32.SQL_TYPE.GUID:
case ODBC32.SQL_TYPE.SS_VARIANT:
case ODBC32.SQL_TYPE.SS_UTCDATETIME:
case ODBC32.SQL_TYPE.SS_TIME_EX:
case ODBC32.SQL_TYPE.BINARY:
dataTypesRow[isLongColumn] = false;
dataTypesRow[isFixedLengthColumn] = true;
break;
case ODBC32.SQL_TYPE.SS_UDT:
default:
// for User defined types don't know if its long or or if it is
// varaible length or not so leave the fields null
break;
}
}
dataTypesRow[columnSizeColumn] = getTypeInfoValues[indexCOLUMN_SIZE];
dataTypesRow[createParametersColumn] = getTypeInfoValues[indexCREATE_PARAMS];
if ((getTypeInfoValues[indexAUTO_UNIQUE_VALUE] == DBNull.Value) ||
(Convert.ToInt16(getTypeInfoValues[indexAUTO_UNIQUE_VALUE], null) == 0)) {
dataTypesRow[isAutoIncermentableColumn] = false;
}
else {
dataTypesRow[isAutoIncermentableColumn] = true;
}
dataTypesRow[isCaseSensitiveColumn] = BooleanFromODBC(getTypeInfoValues[indexCASE_SENSITIVE]);
dataTypesRow[isFixedPrecisionScaleColumn] = BooleanFromODBC(getTypeInfoValues[indexFIXED_PREC_SCALE]);
if (getTypeInfoValues[indexNULLABLE] != DBNull.Value) {
//Use Convert.ToInt16 instead of direct cast to short because the value will be Int32 in some cases
switch ((ODBC32.SQL_NULLABILITY)Convert.ToInt16(getTypeInfoValues[indexNULLABLE], null)) {
case ODBC32.SQL_NULLABILITY.NO_NULLS:
dataTypesRow[isNullableColumn] = false;
break;
case ODBC32.SQL_NULLABILITY.NULLABLE:
dataTypesRow[isNullableColumn] = true;
break;
case ODBC32.SQL_NULLABILITY.UNKNOWN:
dataTypesRow[isNullableColumn] = DBNull.Value;
break;
}
}
if ( DBNull.Value != getTypeInfoValues[indexSEARCHABLE]){
//Use Convert.ToInt16 instead of direct cast to short because the value will be Int32 in some cases
Int16 searchableValue = Convert.ToInt16(getTypeInfoValues[indexSEARCHABLE], null);
switch (searchableValue){
case (Int16)ODBC32.SQL_SEARCHABLE.UNSEARCHABLE:
dataTypesRow[isSearchableColumn] = false;
dataTypesRow[isSearchableWithLikeColumn] = false;
break;
case (Int16)ODBC32.SQL_SEARCHABLE.LIKE_ONLY:
dataTypesRow[isSearchableColumn] = false;
dataTypesRow[isSearchableWithLikeColumn] = true;
break;
case (Int16)ODBC32.SQL_SEARCHABLE.ALL_EXCEPT_LIKE:
dataTypesRow[isSearchableColumn] = true;
dataTypesRow[isSearchableWithLikeColumn] = false;
break;
case (Int16)ODBC32.SQL_SEARCHABLE.SEARCHABLE:
dataTypesRow[isSearchableColumn] = true;
dataTypesRow[isSearchableWithLikeColumn] = true;
break;
}
}
dataTypesRow[isUnsignedColumn] = BooleanFromODBC(getTypeInfoValues[indexUNSIGNED_ATTRIBUTE]);
//For assignment to the DataSet, don't cast the data types -- let the DataSet take care of any conversion
if (getTypeInfoValues[indexMAXIMUM_SCALE] != DBNull.Value ) {
dataTypesRow[maximumScaleColumn] = getTypeInfoValues[indexMAXIMUM_SCALE];
}
if (getTypeInfoValues[indexMINIMUM_SCALE] != DBNull.Value ) {
dataTypesRow[minimumScaleColumn] = getTypeInfoValues[indexMINIMUM_SCALE];
}
if (getTypeInfoValues[indexLITERAL_PREFIX] != DBNull.Value ) {
dataTypesRow[literalPrefixColumn] = getTypeInfoValues[indexLITERAL_PREFIX];
}
if (getTypeInfoValues[indexLITERAL_SUFFIX] != DBNull.Value ) {
dataTypesRow[literalSuffixColumn] = getTypeInfoValues[indexLITERAL_SUFFIX];
}
dataTypesTable.Rows.Add(dataTypesRow);
}
}
public char getUserOnlineStatus(string id)
{
try
{
char itsChar;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT status FROM users WHERE userID=\'" + id + "\'";;
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsChar = itsDataReader.GetChar(0);
itsDataReader.Close();
itsCommand.Dispose();
return itsChar;
}
else
{
itsDataReader.Close();
itsCommand.Dispose();
return 'Z';
}
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return 'Z';
}
public ArrayList getDGPContacts(string userId)
{
//Console.WriteLine("UserID: " + userId);
try
{
ArrayList friendList = new ArrayList();
itsCommand = new OdbcCommand("SELECT user FROM friends WHERE friend=\'" + userId + "\'",itsConnection);
itsDataReader = itsCommand.ExecuteReader();
for(int i = 0; itsDataReader.Read(); i++)
{
// Console.WriteLine("Add friend: " + itsDataReader.GetString(0));
friendList.Add(itsDataReader.GetString(0));
}
itsCommand.Dispose();
itsDataReader.Close();
return friendList;
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return null;
}
public int readInt( string sqlCommand)
{
try
{
int returnInt = 0;
itsCommand = new OdbcCommand("", itsConnection);
itsCommand.CommandText = sqlCommand;
itsDataReader = itsCommand.ExecuteReader();
while(itsDataReader.Read())
{
returnInt = itsDataReader.GetInt32(0);
}
itsCommand.Dispose();
itsDataReader.Close();
return returnInt;
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return 0;
}
public string read( string sqlCommand)
{
try
{
string returnString = "";
itsCommand = new OdbcCommand("", itsConnection);
itsCommand.CommandText = sqlCommand;
itsDataReader = itsCommand.ExecuteReader();
while(itsDataReader.Read())
{
if(returnString != "")
{
returnString += "\n";
}
returnString += itsDataReader.GetString(0);
}
itsCommand.Dispose();
itsDataReader.Close();
return string.Copy(returnString);
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return "";
}
internal void LoadWashingReport(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("DC Number");
dt.Columns.Add("Date", System.Type.GetType("System.DateTime"));
dt.Columns.Add("Fabric");
dt.Columns.Add("Style");
dt.Columns.Add("Company");
dt.Columns.Add("Del Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Balance", System.Type.GetType("System.Double"));
dt.Columns.Add("Prec %");
dt.Columns.Add("Closed", System.Type.GetType("System.Boolean"));
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_WashingProcess.isclosed,nexusGarments_WashingProcess.deldate,nexusGarments_WashingProcess.dcno, nexusGarments_KWashingCompany.Company, nexusGarments_Master_Style.ProcessStyle,nexusGarments_Master_fabric.fabricName, nexusGarments_WashingProcess.delWeight, nexusGarments_WashingProcess.recWeight FROM nexusGarments_WashingProcess INNER JOIN nexusGarments_WashingProgram ON nexusGarments_WashingProcess.mapid = nexusGarments_WashingProgram.id INNER JOIN nexusGarments_KWashingCompany ON nexusGarments_WashingProgram.dyingComp = nexusGarments_KWashingCompany.id INNER JOIN nexusGarments_Master_fabric ON nexusGarments_WashingProgram.fabricid = nexusGarments_Master_fabric.fabricId INNER JOIN nexusGarments_Master_Style ON nexusGarments_WashingProgram.styleid = nexusGarments_Master_Style.id";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
dt.Rows.Add(m_dataReader["dcno"].ToString().Trim(), m_dataReader["deldate"].ToString().Trim().Substring(0, 10), m_dataReader["fabricname"].ToString().Trim(), m_dataReader["processStyle"].ToString().Trim(), m_dataReader["company"].ToString().Trim(), m_dataReader["delweight"].ToString().Trim(), m_dataReader["recweight"].ToString().Trim(), Convert.ToDouble(m_dataReader["delweight"].ToString().Trim()) - Convert.ToDouble(m_dataReader["recweight"].ToString().Trim()), Convert.ToInt32(Convert.ToDouble(m_dataReader["recweight"].ToString().Trim()) * 100 / Convert.ToDouble(m_dataReader["delweight"].ToString().Trim())), m_dataReader["isclosed"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
public bool testNameAndPassword(string name, string password, Client client)
{
try
{
int itsInloggId;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT userID FROM users WHERE email=\'"+ name + "\'";
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsInloggId = itsDataReader.GetInt32(0);
itsDataReader.Close();
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
itsCommand.CommandText = "SELECT password FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'";
itsDataReader = itsCommand.ExecuteReader();
itsDataReader.Read();
string databasePassword = itsDataReader.GetString(0);
if(string.Compare(databasePassword, password, true) == 0)
{
itsDataReader.Close();
itsCommand.CommandText = "SELECT * FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'";
itsDataReader = itsCommand.ExecuteReader();
itsDataReader.Read();
client.Id = itsDataReader.GetString(0);
client.Email = name;
//client.IpNumber = itsDataReader.GetString(3);
client.DisplayName = itsDataReader.GetString(4);
itsCommand.Dispose();
itsDataReader.Close();
return true;
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return false;
}
internal void LoadYarnApproval(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("dcid");
dt.Columns.Add("id");
dt.Columns.Add("Select", System.Type.GetType("System.Boolean"));
dt.Columns.Add("Supplier");
dt.Columns.Add("Process Style");
dt.Columns.Add("Invoice No");
dt.Columns.Add("Order Quantity", System.Type.GetType("System.Double"));
dt.Columns.Add("Gross Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Net Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Bags");
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_YarnInward.invoiceno,nexusGarments_YarnInward.DcId, nexusGarments_YarnInward.id, nexusGarments_YarnPurchase.dcno,nexusGarments_Master_YarnSupplier.Supplier, nexusGarments_Master_Style.ProcessStyle, nexusGarments_YarnPurchase.OrderQuantity, nexusGarments_YarnInward.GrossWeight, nexusGarments_YarnInward.NetWeight, nexusGarments_YarnInward.bags FROM nexusGarments_YarnInward INNER JOIN nexusGarments_YarnPurchase ON nexusGarments_YarnInward.DcId = nexusGarments_YarnPurchase.id INNER JOIN nexusGarments_Master_Style ON nexusGarments_YarnPurchase.StyleId = nexusGarments_Master_Style.id INNER JOIN nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id where nexusGarments_YarnInward.isapproved='false'";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
dt.Rows.Add(m_dataReader["dcid"].ToString().Trim(), m_dataReader["id"].ToString().Trim(), "False", m_dataReader["Supplier"].ToString().Trim(), m_dataReader["ProcessStyle"].ToString().Trim(), m_dataReader["invoiceno"].ToString().Trim(), m_dataReader["OrderQuantity"].ToString().Trim(), m_dataReader["GrossWeight"].ToString().Trim(), m_dataReader["NetWeight"].ToString().Trim(), m_dataReader["bags"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
public void UpdateSuccessor()
{
//Updating the Status of the succeding sequence
command = "SELECT Sequence FROM Ticket_Information WHERE Number=" + ticketNum + " AND Assigned='" + userName + "'";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
dbReader.Read();
int seq = int.Parse(dbReader.GetString(0));
command = "SELECT MAX(Sequence) FROM Ticket_Information WHERE Number=" + ticketNum;
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
dbReader.Read();
int seq2 = int.Parse(dbReader.GetString(0));
if (seq < seq2)// means there are successors
{
command = "SELECT Assigned FROM Ticket_Information WHERE Number=" + ticketNum+" AND Sequence="+(++seq);
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
dbReader.Read();
successor = dbReader.GetString(0);
string[] tokens = allFiles.Split('#');
Directory.CreateDirectory(successor + "/" + ticketNum);
for (int k = 0; k < tokens.Length; k++)
{
try
{
File.Copy((userName + "/" + ticketNum + "/" + tokens[k]), (successor + "/" + ticketNum + "/" + tokens[k]),true);
}
catch(Exception ww)
{
Console.WriteLine(ww);//gives an exception but still works !!!!
}
}
command = " Update Ticket_Information SET Status='Assigned' , Attachment= Attachment + '" + allFiles + "' WHERE Number=" + ticketNum + " AND Sequence =" + seq;//sequece has been updated earlier
sqlCommand = new OdbcCommand(command, dbConn);
sqlCommand.ExecuteNonQuery();// even if no one preceeding
}
}
internal void LoadYarnDCInfo(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("PO Number");
dt.Columns.Add("Style");
dt.Columns.Add("Supplier");
dt.Columns.Add("Process ID");
dt.Columns.Add("PO Date", System.Type.GetType("System.DateTime"));
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT DISTINCT nexusGarments_YarnPurchase.dcno,nexusGarments_YarnPurchase.podate, nexusGarmants_Master_Process.ProcessID, nexusGarments_Master_Style.ProcessStyle,nexusGarments_Master_YarnSupplier.Supplier FROM nexusGarmants_Master_Process INNER JOIN nexusGarments_YarnPurchase ON nexusGarmants_Master_Process.id = nexusGarments_YarnPurchase.ProcessId INNER JOIN nexusGarments_Master_Style ON nexusGarments_YarnPurchase.StyleId = nexusGarments_Master_Style.id INNER JOIN nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id where nexusGarmants_Master_Process.isclosed <>'True'";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
dt.Rows.Add(m_dataReader["dcno"].ToString().Trim(), m_dataReader["ProcessStyle"].ToString().Trim(), m_dataReader["Supplier"].ToString().Trim(), m_dataReader["ProcessID"].ToString().Trim(), m_dataReader["podate"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
public void GetTicketInfo()
{
ticketNum = int.Parse(reader.ReadLine());//get the ticket number you want to handle
command = "SELECT T.Number, T.Issue_Date,I.Assigned, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements, I.Attachment " +
"FROM Ticket_Information AS I, Ticket AS T WHERE T.Number=" + ticketNum + " AND T.Number=I.Number AND I.Assigned='"+userName+"'";//name,email,pass
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount - 1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by ##
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");//sending the end of tickets info
writer.Flush();
}
internal void LoadYarnDyingApproval(DataTable dt)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("id");
dt.Columns.Add("dcid");
dt.Columns.Add("mapid");
dt.Columns.Add("Select", System.Type.GetType("System.Boolean"));
dt.Columns.Add("Invoice");
dt.Columns.Add("DC Number");
dt.Columns.Add("Company");
dt.Columns.Add("Color");
dt.Columns.Add("Rec Rolls", System.Type.GetType("System.Double"));
dt.Columns.Add("Rec Weight", System.Type.GetType("System.Double"));
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_YarnDyingProcess.dcno, nexusGarments_YarnDyingLog.invoiceno, nexusGarments_YarnDyingProcess.id, nexusGarments_YarnDyingProcess.mapid, nexusGarments_YarnDyingCompany.Company, nexusGarments_PrintingColor.ColorName, nexusGarments_YarnDyingLog.recrolls, nexusGarments_YarnDyingLog.recweight, nexusGarments_YarnDyingLog.recdate, nexusGarments_YarnDyingLog.id AS Expr1 FROM nexusGarments_YarnDyingLog INNER JOIN nexusGarments_YarnDyingProcess ON nexusGarments_YarnDyingLog.dcid = nexusGarments_YarnDyingProcess.id INNER JOIN nexusGarments_YarnDyingProgram ON nexusGarments_YarnDyingProcess.mapid = nexusGarments_YarnDyingProgram.id INNER JOIN nexusGarments_YarnDyingCompany ON nexusGarments_YarnDyingProgram.companyid = nexusGarments_YarnDyingCompany.id INNER JOIN nexusGarments_PrintingColor ON nexusGarments_YarnDyingProgram.colorid = nexusGarments_PrintingColor.id where nexusGarments_YarnDyingLog.isapproved='False'";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
dt.Rows.Add(m_dataReader["Expr1"].ToString().Trim(), m_dataReader["id"].ToString().Trim(), m_dataReader["mapid"].ToString().Trim(), "False", m_dataReader["invoiceno"].ToString().Trim(), m_dataReader["dcno"].ToString().Trim(), m_dataReader["Company"].ToString().Trim(), m_dataReader["ColorName"].ToString().Trim(), m_dataReader["recrolls"].ToString().Trim(), m_dataReader["recweight"].ToString().Trim());
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
// DeriveParametersFromStoredProcedure (
// OdbcConnection connection,
// OdbcCommand command);
//
// Uses SQLProcedureColumns to create an array of OdbcParameters
//
static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) {
List<OdbcParameter> rParams = new List<OdbcParameter>();
// following call ensures that the command has a statement handle allocated
CMDWrapper cmdWrapper = command.GetStatementHandle();
OdbcStatementHandle hstmt = cmdWrapper.StatementHandle;
int cColsAffected;
// maps an enforced 4-part qualified string as follows
// parts[0] = null - ignored but removal would be a run-time breaking change from V1.0
// parts[1] = CatalogName (optional, may be null)
// parts[2] = SchemaName (optional, may be null)
// parts[3] = ProcedureName
//
string quote = connection.QuoteChar(ADP.DeriveParameters);
string[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, Res.ODBC_ODBCCommandText, false);
if (null == parts[3]) { // match everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace
parts[3] = command.CommandText;
}
// note: native odbc appears to ignore all but the procedure name
ODBC32.RetCode retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null);
// Note: the driver does not return an error if the given stored procedure does not exist
// therefore we cannot handle that case and just return not parameters.
if (ODBC32.RetCode.SUCCESS != retcode) {
connection.HandleError(hstmt, retcode);
}
using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default)) {
reader.FirstResult();
cColsAffected = reader.FieldCount;
// go through the returned rows and filter out relevant parameter data
//
while (reader.Read()) {
// devnote: column types are specified in the ODBC Programmer's Reference
// COLUMN_TYPE Smallint 16bit
// COLUMN_SIZE Integer 32bit
// DECIMAL_DIGITS Smallint 16bit
// NUM_PREC_RADIX Smallint 16bit
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME-1);
switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE-1)){
case ODBC32.SQL_PARAM.INPUT:
parameter.Direction = ParameterDirection.Input;
break;
case ODBC32.SQL_PARAM.OUTPUT:
parameter.Direction = ParameterDirection.Output;
break;
case ODBC32.SQL_PARAM.INPUT_OUTPUT:
parameter.Direction = ParameterDirection.InputOutput;
break;
case ODBC32.SQL_PARAM.RETURN_VALUE:
parameter.Direction = ParameterDirection.ReturnValue;
break;
default:
Debug.Assert(false, "Unexpected Parametertype while DeriveParamters");
break;
}
parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE-1))._odbcType;
parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE-1);
switch(parameter.OdbcType){
case OdbcType.Decimal:
case OdbcType.Numeric:
parameter.ScaleInternal = (Byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS-1);
parameter.PrecisionInternal = (Byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX-1);
break;
}
rParams.Add (parameter);
}
}
retcode = hstmt.CloseCursor();
return rParams.ToArray();;
}
internal void LoadYarnInvoicePicture(string p, DevExpress.XtraEditors.PictureEdit pictureEdit1)
{
m_dataReader = null;
m_odbcCommand.CommandText = "select img from nexusGarments_YarnInward where id=" + p;
try
{
string id = "0";
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
byte[] imageData = (byte[])m_dataReader["img"];
//Initialize image variable
Image newImage;
//Read image data into a memory stream
using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
{
ms.Write(imageData, 0, imageData.Length);
//Set image variable value using memory stream.
newImage = Image.FromStream(ms, true);
}
pictureEdit1.Image = newImage;
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
internal void loadYarnPurchaceGrid(DataTable dt, string dcno)
{
dt.Columns.Clear();
dt.Rows.Clear();
dt.Columns.Add("id");
dt.Columns.Add("Counts");
dt.Columns.Add("Mill");
dt.Columns.Add("Order Quantity");
dt.Columns.Add("Avil Quantity");
dt.Columns.Add("Bags", System.Type.GetType("System.Double"));
dt.Columns.Add("Gross Weight", System.Type.GetType("System.Double"));
dt.Columns.Add("Net Weight", System.Type.GetType("System.Double"));
m_dataReader = null;
m_odbcCommand.CommandText = "SELECT nexusGarments_YarnPurchase.id,nexusGarments_YarnPurchase.AvilQuantity,nexusGarments_Master_YarnSupplier.Supplier,nexusGarments_YarnPurchase.orderquantity, nexusGarmrnts_Master_Counts.Counts, nexusGarments_Master_Style.ProcessStyle FROM nexusGarments_YarnPurchase INNER JOIN nexusGarments_Master_YarnSupplier AS nexusGarments_Master_YarnSupplier ON nexusGarments_YarnPurchase.SupplierId = nexusGarments_Master_YarnSupplier.id INNER JOIN nexusGarmrnts_Master_Counts ON nexusGarments_YarnPurchase.CountsId = nexusGarmrnts_Master_Counts.id INNER JOIN nexusGarments_Master_Style ON nexusGarments_YarnPurchase.StyleId = nexusGarments_Master_Style.id where nexusGarments_YarnPurchase.dcno='" + dcno + "'";
try
{
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (m_dataReader.Read())
{
// lblMake_new.Text = m_dataReader["make"].ToString();
// ddlHour.Items.Add(m_dataReader["cfrom"].ToString());
// lblSubject.Text = m_dataReader[sel.Trim().ToString()].ToString();
//lblgtotal.Text = m_dataReader["name"].ToString() + " " + m_dataReader["lname"].ToString();
dt.Rows.Add(m_dataReader["id"].ToString().Trim(), m_dataReader["counts"].ToString().Trim(), m_dataReader["Supplier"].ToString().Trim(), m_dataReader["orderquantity"].ToString().Trim(), m_dataReader["AvilQuantity"].ToString().Trim(), 0, 0, 0);
}
}
catch (Exception e)
{
}
finally
{
CloseDB(1);
}
}
private void DataTableFromDataReaderDataTypes(DataTable dataTypesTable, OdbcDataReader dataReader, OdbcConnection connection)
{
DataTable schemaTable = null;
schemaTable = dataReader.GetSchemaTable();
if (schemaTable == null)
{
throw ADP.OdbcNoTypesFromProvider();
}
object[] values = new object[schemaTable.Rows.Count];
DataColumn column19 = dataTypesTable.Columns[DbMetaDataColumnNames.TypeName];
DataColumn column18 = dataTypesTable.Columns[DbMetaDataColumnNames.ProviderDbType];
DataColumn column17 = dataTypesTable.Columns[DbMetaDataColumnNames.ColumnSize];
DataColumn column16 = dataTypesTable.Columns[DbMetaDataColumnNames.CreateParameters];
DataColumn column15 = dataTypesTable.Columns[DbMetaDataColumnNames.DataType];
DataColumn column6 = dataTypesTable.Columns[DbMetaDataColumnNames.IsAutoIncrementable];
DataColumn column14 = dataTypesTable.Columns[DbMetaDataColumnNames.IsCaseSensitive];
DataColumn column5 = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedLength];
DataColumn column13 = dataTypesTable.Columns[DbMetaDataColumnNames.IsFixedPrecisionScale];
DataColumn column4 = dataTypesTable.Columns[DbMetaDataColumnNames.IsLong];
DataColumn column3 = dataTypesTable.Columns[DbMetaDataColumnNames.IsNullable];
DataColumn column2 = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchable];
DataColumn column = dataTypesTable.Columns[DbMetaDataColumnNames.IsSearchableWithLike];
DataColumn column12 = dataTypesTable.Columns[DbMetaDataColumnNames.IsUnsigned];
DataColumn column11 = dataTypesTable.Columns[DbMetaDataColumnNames.MaximumScale];
DataColumn column10 = dataTypesTable.Columns[DbMetaDataColumnNames.MinimumScale];
DataColumn column9 = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralPrefix];
DataColumn column8 = dataTypesTable.Columns[DbMetaDataColumnNames.LiteralSuffix];
DataColumn column7 = dataTypesTable.Columns[OdbcMetaDataColumnNames.SQLType];
while (dataReader.Read())
{
TypeMap map;
dataReader.GetValues(values);
DataRow row = dataTypesTable.NewRow();
row[column19] = values[0];
row[column7] = values[1];
ODBC32.SQL_TYPE sqltype = (ODBC32.SQL_TYPE)((short)((int)Convert.ChangeType(values[1], typeof(int), null)));
if (!connection.IsV3Driver)
{
if (sqltype == ~ODBC32.SQL_TYPE.WLONGVARCHAR)
{
sqltype = ODBC32.SQL_TYPE.TYPE_DATE;
}
else if (sqltype == ~ODBC32.SQL_TYPE.GUID)
{
sqltype = ODBC32.SQL_TYPE.TYPE_TIME;
}
}
try
{
map = TypeMap.FromSqlType(sqltype);
}
catch (ArgumentException)
{
map = null;
}
if (map != null)
{
row[column18] = map._odbcType;
row[column15] = map._type.FullName;
switch (sqltype)
{
case ODBC32.SQL_TYPE.SS_TIME_EX:
case ODBC32.SQL_TYPE.SS_UTCDATETIME:
case ODBC32.SQL_TYPE.SS_VARIANT:
case ODBC32.SQL_TYPE.GUID:
case ODBC32.SQL_TYPE.WCHAR:
case ODBC32.SQL_TYPE.BIT:
case ODBC32.SQL_TYPE.TINYINT:
case ODBC32.SQL_TYPE.BIGINT:
case ODBC32.SQL_TYPE.BINARY:
case ODBC32.SQL_TYPE.CHAR:
case ODBC32.SQL_TYPE.NUMERIC:
case ODBC32.SQL_TYPE.DECIMAL:
case ODBC32.SQL_TYPE.INTEGER:
case ODBC32.SQL_TYPE.SMALLINT:
case ODBC32.SQL_TYPE.FLOAT:
case ODBC32.SQL_TYPE.REAL:
case ODBC32.SQL_TYPE.DOUBLE:
case ODBC32.SQL_TYPE.TIMESTAMP:
case ODBC32.SQL_TYPE.TYPE_DATE:
case ODBC32.SQL_TYPE.TYPE_TIME:
case ODBC32.SQL_TYPE.TYPE_TIMESTAMP:
goto Label_02F8;
case ODBC32.SQL_TYPE.SS_XML:
case ODBC32.SQL_TYPE.WLONGVARCHAR:
case ODBC32.SQL_TYPE.LONGVARBINARY:
case ODBC32.SQL_TYPE.LONGVARCHAR:
goto Label_02BC;
case ODBC32.SQL_TYPE.WVARCHAR:
case ODBC32.SQL_TYPE.VARBINARY:
case ODBC32.SQL_TYPE.VARCHAR:
goto Label_02DA;
}
}
goto Label_0314;
Label_02BC:
row[column4] = true;
row[column5] = false;
goto Label_0314;
Label_02DA:
row[column4] = false;
row[column5] = false;
goto Label_0314;
Label_02F8:
row[column4] = false;
row[column5] = true;
Label_0314:
row[column17] = values[2];
row[column16] = values[5];
if ((values[11] == DBNull.Value) || (Convert.ToInt16(values[11], null) == 0))
{
row[column6] = false;
}
else
{
row[column6] = true;
}
row[column14] = this.BooleanFromODBC(values[7]);
row[column13] = this.BooleanFromODBC(values[10]);
if (values[6] != DBNull.Value)
{
switch (((ODBC32.SQL_NULLABILITY)((ushort)Convert.ToInt16(values[6], null))))
{
case ODBC32.SQL_NULLABILITY.NO_NULLS:
row[column3] = false;
break;
case ODBC32.SQL_NULLABILITY.NULLABLE:
row[column3] = true;
break;
case ODBC32.SQL_NULLABILITY.UNKNOWN:
row[column3] = DBNull.Value;
break;
}
}
if (DBNull.Value != values[8])
{
switch (Convert.ToInt16(values[8], null))
{
case 0:
row[column2] = false;
row[column] = false;
break;
case 1:
row[column2] = false;
row[column] = true;
break;
case 2:
row[column2] = true;
row[column] = false;
break;
case 3:
row[column2] = true;
row[column] = true;
break;
}
}
row[column12] = this.BooleanFromODBC(values[9]);
if (values[14] != DBNull.Value)
{
row[column11] = values[14];
}
if (values[13] != DBNull.Value)
{
row[column10] = values[13];
}
if (values[3] != DBNull.Value)
{
row[column9] = values[3];
}
if (values[4] != DBNull.Value)
{
row[column8] = values[4];
}
dataTypesTable.Rows.Add(row);
}
}
