/// <summary> /// /// select a set of rows from table t_RBSR_AUFW_u_OrgValue1252. /// </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="TcodeAssignmentSetID"></param> /// <returns>returnListOrgValue1252ByTcodeAssignmentSet[]</returns> public returnListOrgValue1252ByTcodeAssignmentSet[] ListOrgValue1252ByTcodeAssignmentSet(int?maxRowsToReturn, string extendedCriteria, string[] extendedParameters, string extendedSortOrder, int TcodeAssignmentSetID) { returnListOrgValue1252ByTcodeAssignmentSet[] rv = null; DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); string runTimeCriteria = (extendedCriteria != null ? extendedCriteria.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"fieldname\"", "\"c_u_FieldName\"").Replace("\"rangelow\"", "\"c_u_RangeLow\"").Replace("\"rangehigh\"", "\"c_u_RangeHigh\"").Replace("\"editstatus\"", "\"c_u_EditStatus\"").Replace("\"tcodeassignmentset\"", "\"c_r_TcodeAssignmentSet\"").Replace("\"saprole\"", "\"c_r_SAProle\""); string runTimeSortOrder = (extendedSortOrder != null ? extendedSortOrder.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"fieldname\"", "\"c_u_FieldName\"").Replace("\"rangelow\"", "\"c_u_RangeLow\"").Replace("\"rangehigh\"", "\"c_u_RangeHigh\"").Replace("\"editstatus\"", "\"c_u_EditStatus\"").Replace("\"tcodeassignmentset\"", "\"c_r_TcodeAssignmentSet\"").Replace("\"saprole\"", "\"c_r_SAProle\""); if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0) { if (_dbConnection.Driver.ToLower().StartsWith("sql")) { cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_FieldName\", \"c_u_RangeLow\", \"c_u_RangeHigh\", \"c_r_TcodeAssignmentSet\", \"c_r_SAProle\", \"c_u_EditStatus\" FROM \"t_RBSR_AUFW_u_OrgValue1252\" WHERE \"c_r_TcodeAssignmentSet\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder); } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_FieldName\", \"c_u_RangeLow\", \"c_u_RangeHigh\", \"c_r_TcodeAssignmentSet\", \"c_r_SAProle\", \"c_u_EditStatus\" FROM \"t_RBSR_AUFW_u_OrgValue1252\" WHERE \"c_r_TcodeAssignmentSet\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder) + " LIMIT " + maxRowsToReturn.Value; } } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_FieldName\", \"c_u_RangeLow\", \"c_u_RangeHigh\", \"c_r_TcodeAssignmentSet\", \"c_r_SAProle\", \"c_u_EditStatus\" FROM \"t_RBSR_AUFW_u_OrgValue1252\" WHERE \"c_r_TcodeAssignmentSet\"=?" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " and (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder); } cmd.Parameters.Add("1_TcodeAssignmentSetID", OdbcType.Int); cmd.Parameters["1_TcodeAssignmentSetID"].Value = (object)TcodeAssignmentSetID; 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 <returnListOrgValue1252ByTcodeAssignmentSet> rvl = new List <returnListOrgValue1252ByTcodeAssignmentSet>(); while (dr.Read()) { returnListOrgValue1252ByTcodeAssignmentSet cr = new returnListOrgValue1252ByTcodeAssignmentSet(); 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 'FieldName'"); } else { cr.FieldName = dr.GetString(1); } if (dr.IsDBNull(2)) { cr.RangeLow = null; } else { cr.RangeLow = dr.GetString(2); } if (dr.IsDBNull(3)) { cr.RangeHigh = null; } else { cr.RangeHigh = dr.GetString(3); } if (dr.IsDBNull(4)) { throw new Exception("Value 'null' is not allowed for 'TcodeAssignmentSetID'"); } else { cr.TcodeAssignmentSetID = dr.GetInt32(4); } if (dr.IsDBNull(5)) { throw new Exception("Value 'null' is not allowed for 'SAProleID'"); } else { cr.SAProleID = dr.GetInt32(5); } if (dr.IsDBNull(6)) { throw new Exception("Value 'null' is not allowed for 'EditStatus'"); } else { cr.EditStatus = dr.GetInt32(6); } rvl.Add(cr); } dr.Close(); dr.Dispose(); rv = rvl.ToArray(); cmd.Dispose(); DBClose(); return(rv); }
protected void Page_Load(object sender, EventArgs e) { if (IsPostBack) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "msg1", " randomTable();", true); } 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"]; is_mobile = Request.QueryString["is_mobile"] != null && Request.QueryString["is_mobile"].ToString() == "1"; string column_17 = ""; if (Request.QueryString["Column_17"] != null) { column_17 = HttpUtility.UrlDecode(Request.QueryString["Column_17"], Encoding.GetEncoding("windows-1251")); } Column_13 = Request.QueryString["Column_13"] != null ? Request.QueryString["Column_13"].ToString() : ""; Column_14 = Request.QueryString["Column_14"] != null ? Request.QueryString["Column_14"].ToString() : ""; HiddenFieldColumn_13.Value = Column_13; HiddenFieldColumn_14.Value = Column_14; HiddenFieldColumn_17.Value = column_17.ToString(); var Column_5 = Request.QueryString["Column_1"] != null ? Request.QueryString["Column_1"].ToString() : ""; 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(); } } } }
public DataSet GetFormDataP2009(string strPage) { 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 "P2009Page1": //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 "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 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 "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_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 "Page4" case "P2009Page4": 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 [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 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(); break; #endregion #region "Page5" case "P2009Page5": 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 [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 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 "Page6" case "P2009Page6": 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 [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 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 "Page7" case "P2009Page7": 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 "Page8" case "P2009Page8": 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,PPL_OTH_IN_OTHER," + "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,PPL_OTHER_EXP, " + "PPL_NET_PROFIT,PPL_DISALLOWED_EXP FROM [P_PROFIT_AND_LOSS] 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("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 "Page9" case "P2009Page9": 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); }
//===========================GetDetails_Business_License=========================== public Details GetDetails_Business_License(string id) { OdbcConnection dbConn = new OdbcConnection(); dbConn.ConnectionString = ComPlusconstring; dbConn.Open(); OdbcCommand dbComm = default(OdbcCommand); OdbcDataReader dbread = default(OdbcDataReader); string strSQL = null; strSQL = "SELECT b.bus_id as bus_id, b.license as license, b.stat as stat, b.category as category, cu.e_name, a.adrs1, "; strSQL = strSQL + "a.city, a.state_id, a.zip, ca.e_adrs1 as cust_adrs1, ca.e_adrs2 as cust_adrs2, ca.e_city as cust_city, "; strSQL = strSQL + "ca.e_state as cust_state, ca.e_zip as cust_zip, owner.e_name as owner_name, "; strSQL = strSQL + "a.o_entity_id, a.o_phone_ins, b.expiration, b_entity_id, cblrise.*, cblrise.state_id as sein, "; strSQL = strSQL + "establish, units, a.* "; strSQL = strSQL + "from cblbusiness a, cbllicense b, cpbentity cu, cpbentity_addr ca, cpbentity owner, cpbentity_addr owner_addr, outer cblrise "; strSQL = strSQL + "where a.bus_id = b.bus_id "; strSQL = strSQL + "and a.b_entity_id = cu.entity_id "; strSQL = strSQL + "and a.b_entity_id = ca.entity_id "; strSQL = strSQL + "and a.m_addr_no = ca.e_adrs_ins and a.o_entity_id = owner.entity_id "; strSQL = strSQL + "and a.o_entity_id = owner_addr.entity_id and a.o_addr_no = owner_addr.e_adrs_ins and a.bus_id = cblrise.bus_id "; strSQL += "AND b.license = '" + id + "'"; dbComm = new OdbcCommand(strSQL, dbConn); dbread = dbComm.ExecuteReader(); Details results = new Details(); Helpers helps = new Helpers(); while (dbread.Read()) { results.a = Helpers.Get_AccType(dbread["license"].ToString()); results.b = dbread["license"].ToString(); results.c = dbread["category"].ToString(); results.d = dbread["bus_id"].ToString(); results.e = dbread["e_name"].ToString(); results.f = dbread["owner_name"].ToString(); results.g = helps.Get_Phone(dbread["o_entity_id"].ToString(), dbread["o_phone_ins"].ToString()); results.h = dbread["o_type"].ToString(); results.i = dbread["expiration"].ToString(); results.j = dbread["adrs1"].ToString(); results.k = dbread["city"].ToString() + ", " + dbread["state_id"].ToString() + ", " + dbread["zip"].ToString(); results.l = dbread["cust_adrs1"].ToString(); results.m = dbread["cust_adrs2"].ToString(); results.n = dbread["cust_city"].ToString() + ", " + dbread["cust_state"].ToString(); results.o = dbread["cust_zip"].ToString(); results.p = helps.Get_Phone(dbread["b_entity_id"].ToString(), "1"); results.q = dbread["fei_number"].ToString(); results.r = dbread["sein"].ToString(); results.s = dbread["establish"].ToString(); results.t = dbread["sic_code"].ToString(); results.u = dbread["units"].ToString(); results.v = dbread["usr1"].ToString(); results.w = dbread["usr2"].ToString(); results.x = dbread["usr3"].ToString(); results.y = dbread["usr4"].ToString(); results.z = dbread["usr5"].ToString(); results.aa = dbread["usr6"].ToString(); results.bb = dbread["usr7"].ToString(); } dbread.Close(); dbComm.Dispose(); dbConn.Close(); dbConn = null; return(results); }
/// <summary> /// /// select a row from table t_RBSR_AUFW_u_ReconcDiffItem. /// </summary> /// <param name="ID"></param> /// <returns>returnGetReconcDiffItem</returns> public returnGetReconcDiffItem GetReconcDiffItem(int ID) { returnGetReconcDiffItem rv = new returnGetReconcDiffItem(); DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); cmd.CommandText = "select \"c_id\",\"c_u_DiffType\",\"c_u_Comment\",\"c_u_Status\",\"c_u_AssignedUser\",\"c_r_ReconcReport\",\"c_u_DiffObject\",\"c_u_RoleName\",\"c_u_Detail\",\"c_u_Platform\" from \"t_RBSR_AUFW_u_ReconcDiffItem\" 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 'DiffType'"); } else { rv.DiffType = dr.GetString(1); } if (dr.IsDBNull(2)) { rv.Comment = null; } else { rv.Comment = dr.GetString(2); } if (dr.IsDBNull(3)) { rv.Status = null; } else { rv.Status = dr.GetString(3); } if (dr.IsDBNull(4)) { rv.AssignedUser = null; } else { rv.AssignedUser = dr.GetString(4); } if (dr.IsDBNull(5)) { throw new Exception("Value 'null' is not allowed for 'ReconcReportID'"); } else { rv.ReconcReportID = dr.GetInt32(5); } if (dr.IsDBNull(6)) { throw new Exception("Value 'null' is not allowed for 'DiffObject'"); } else { rv.DiffObject = dr.GetString(6); } if (dr.IsDBNull(7)) { rv.RoleName = null; } else { rv.RoleName = dr.GetString(7); } if (dr.IsDBNull(8)) { rv.Detail = null; } else { rv.Detail = dr.GetString(8); } if (dr.IsDBNull(9)) { rv.Platform = null; } else { rv.Platform = dr.GetString(9); } } dr.Close(); dr.Dispose(); if (drctr != 1) { throw new Exception("Operation selected no rows!"); } cmd.Dispose(); DBClose(); return(rv); }
private void FillAllChartsAndGrids(string QueryDate, string QueryLocation1, string QueryLocation2) { string connectionString = WebConfigurationManager.ConnectionStrings["procyk01ConnectionString"].ConnectionString; try { //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ string sql = "SELECT complaint, CT "; sql += "FROM complaints "; sql += "WHERE (complaintdate = '" + QueryDate + "') AND (neighborhood = '" + QueryLocation1 + "') "; if (CheckBox1.Checked == true) { sql += "AND complaint NOT IN ('GENERAL CONSTRUCTION', 'NONCONST', 'PAINT - PLASTER', 'PLUMBING', 'ELECTRIC', 'HEATING') "; } ; sql += "ORDER BY CT DESC LIMIT 10"; OdbcConnection con1 = new OdbcConnection(connectionString); OdbcCommand cmdO = new OdbcCommand(sql, con1); con1.Open(); OdbcDataReader reader1 = cmdO.ExecuteReader(); Chart1.DataSource = reader1; Chart1.DataBind(); Chart1.Series["Series1"].ToolTip = "#VALX\nCount: #VALY"; reader1.Close(); con1.Close(); //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ string sql2 = "SELECT complaint, CT "; sql2 += "FROM complaints "; sql2 += "WHERE (complaintdate = '" + QueryDate + "') AND (neighborhood = '" + QueryLocation2 + "') "; if (CheckBox2.Checked == true) { sql2 += "AND complaint NOT IN ('GENERAL CONSTRUCTION', 'NONCONST', 'PAINT - PLASTER', 'PLUMBING', 'ELECTRIC', 'HEATING') "; } ; sql2 += "ORDER BY CT DESC LIMIT 10"; OdbcConnection con2 = new OdbcConnection(connectionString); OdbcCommand cmd2 = new OdbcCommand(sql2, con2); con2.Open(); OdbcDataReader reader2 = cmd2.ExecuteReader(); Chart2.DataSource = reader2; Chart2.DataBind(); Chart2.Series["Series1"].ToolTip = "#VALX\nCount: #VALY"; reader2.Close(); con2.Close(); //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ string sql3 = "SELECT Neighborhood, ZipCode, AvgIncome, BelowPovertyLine, Population "; sql3 += "FROM Demographics WHERE (Neighborhood = '" + QueryLocation1 + "') "; sql3 += "ORDER BY ZipCode"; OdbcConnection con3 = new OdbcConnection(connectionString); OdbcCommand cmd3 = new OdbcCommand(sql3, con3); con3.Open(); OdbcDataReader reader3 = cmd3.ExecuteReader(); GridView2.DataSource = reader3; GridView2.DataBind(); reader3.Close(); con3.Close(); //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ string sql4 = "SELECT Neighborhood, ZipCode, AvgIncome, BelowPovertyLine, Population "; sql4 += "FROM Demographics WHERE (Neighborhood = '" + QueryLocation2 + "') "; sql4 += "ORDER BY ZipCode"; OdbcConnection con4 = new OdbcConnection(connectionString); OdbcCommand cmd4 = new OdbcCommand(sql4, con4); con4.Open(); OdbcDataReader reader4 = cmd4.ExecuteReader(); GridView1.DataSource = reader4; GridView1.DataBind(); reader4.Close(); con4.Close(); Label1.Text = QueryLocation1; Label2.Text = QueryLocation2; } finally { //con1.Close(); } }
/// <summary> /// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />. /// <seealso cref="IWorkflowScript" /> /// </summary> /// <param name="app"></param> /// <param name="args"></param> public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args) { try { // Initialize global settings IntializeScript(ref app, ref args); //get and clean LicenseType and Application # keywords for passing to LicEase database KeywordType kwtAppNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamAppNum); string strAppNum = ""; if (kwtAppNum != null) { KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtAppNum); if (keyRecFileNum != null) { Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtAppNum); if (kwdFileNum != null) { strAppNum = CleanSeedKW(kwdFileNum.ToString()); } } } if (strAppNum == "") { throw new Exception(string.Format("Application Number is blank!")); } //access Config Item for LicEase User string gUSER = ""; if (app.Configuration.TryGetValue("LicEaseUser", out gUSER)) { } //access Config Item for LicEase Password string gPASS = ""; if (app.Configuration.TryGetValue("LicEasePassword", out gPASS)) { } /* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */ //access Config Item for LicEase UAT ODBC string gODBC = ""; if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC)) { } /* UNCOMMENT THIS SECTION WHEN MOVING TO PROD * //access Config Item for LicEase PROD ODBC * string gODBC = ""; * if (app.Configuration.TryGetValue("LicEasePROD", out gODBC)) * { * } */ string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS); app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString)); StringBuilder strSql = new StringBuilder(); strSql.Append(@"select c.file_nbr as File_Number "); strSql.Append(@" from lic c, appl a "); strSql.Append(@" where a.clnt_cde = '4011' "); strSql.Append(@" and a.applc_nbr = '"); strSql.Append(strAppNum); strSql.Append(@"' and c.lic_id = a.lic_id "); app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString())); using (OdbcConnection con = new OdbcConnection(connectionString)) { try { con.Open(); using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con)) using (OdbcDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { string strFileNum = ""; reader.Read(); strFileNum = reader["File_Number"].ToString(); Keyword kwdFileNum = null; if (!String.IsNullOrEmpty(strFileNum)) { KeywordType kwtFileNum = app.Core.KeywordTypes.Find(gSaveToFileNum); if (kwtFileNum != null) { kwdFileNum = CreateKeywordHelper(kwtFileNum, strFileNum); } } using (DocumentLock documentLock = _currentDocument.LockDocument()) { // Ensure lock was obtained if (documentLock.Status != DocumentLockStatus.LockObtained) { throw new Exception("Document lock not obtained"); } // Create keyword modifier object to hold keyword changes KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier(); // Add update keyword call to keyword modifier object //Note Overloads available for use //(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue) if (kwdFileNum != null) { keyModifier.AddKeyword(kwdFileNum); } // Apply keyword change to the document keyModifier.ApplyChanges(); string output = String.Format("Keyword: '{0}' Value: '{1}', {3}added to Document {2}.", gSaveToFileNum, strFileNum, _currentDocument.ID, Environment.NewLine); //Output the results to the OnBase Diagnostics Console app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output); } } else { throw new Exception(string.Format("No records found in database for {0}='{1}' ", gParamAppNum, strAppNum)); } } } catch (Exception ex) { throw new ApplicationException("Error during database operations!", ex); } finally { if (con.State == ConnectionState.Open) { con.Close(); } } } } catch (Exception ex) { // Handle exceptions and log to Diagnostics Console and document history HandleException(ex, ref app, ref args); } finally { // Log script execution end app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info, string.Format("End Script - [{0}]", ScriptName)); } }
protected void get_attendance(object sender, EventArgs e) { String sub1 = "", sub2 = "", sub3 = "", sub4 = "", sub5 = ""; String sem = semlist.SelectedValue; String year = yearlist.SelectedValue; String table = "mca" + sem + "semattendance"; tbl_attend.Rows.Clear(); try { String strcon = System.Configuration.ConfigurationManager.ConnectionStrings["studentdbconnectionstring"].ConnectionString.ToString(); OdbcConnection con = new OdbcConnection(strcon); OdbcCommand get_subjects = new OdbcCommand("Select * from " + table + " where rollno ='0' ", con); OdbcCommand cmd = new OdbcCommand("Select * from " + table + " where rollno ='" + rollno + "' and year='" + year + "' order by id asc ", con); con.Open(); OdbcDataReader subject = get_subjects.ExecuteReader(); OdbcDataReader rd = cmd.ExecuteReader(); while (subject.Read()) { sub1 = subject["s1"].ToString(); sub2 = subject["s2"].ToString(); sub3 = subject["s3"].ToString(); sub4 = subject["s4"].ToString(); sub5 = subject["s5"].ToString(); } TableHeaderRow thr = new TableHeaderRow(); TableHeaderCell thc = new TableHeaderCell(); thc.Text = "Month"; thr.Cells.Add(thc); TableHeaderCell thcs1 = new TableHeaderCell(); thcs1.Text = sub1; thr.Cells.Add(thcs1); TableHeaderCell thcs2 = new TableHeaderCell(); thcs2.Text = sub2; thr.Cells.Add(thcs2); TableHeaderCell thcs3 = new TableHeaderCell(); thcs3.Text = sub3; thr.Cells.Add(thcs3); TableHeaderCell thcs4 = new TableHeaderCell(); thcs4.Text = sub4; thr.Cells.Add(thcs4); TableHeaderCell thcs5 = new TableHeaderCell(); thcs5.Text = sub5; thr.Cells.Add(thcs5); tbl_attend.Rows.Add(thr); while (rd.Read()) { TableRow tr = new TableRow(); TableHeaderCell month = new TableHeaderCell(); month.Text = rd["month"].ToString(); tr.Cells.Add(month); TableCell s1 = new TableCell(); s1.Text = rd["s1"].ToString() + " %"; if (Convert.ToInt32(rd["s1"]) < minimumpercentage) { s1.ForeColor = System.Drawing.Color.Red; } tr.Cells.Add(s1); TableCell s2 = new TableCell(); s2.Text = rd["s2"].ToString() + " %"; if (Convert.ToInt32(rd["s2"]) < minimumpercentage) { s2.ForeColor = System.Drawing.Color.Red; } tr.Cells.Add(s2); TableCell s3 = new TableCell(); s3.Text = rd["s3"].ToString() + " %"; if (Convert.ToInt32(rd["s3"]) < minimumpercentage) { s3.ForeColor = System.Drawing.Color.Red; } tr.Cells.Add(s3); TableCell s4 = new TableCell(); s4.Text = rd["s4"].ToString() + " %"; if (Convert.ToInt32(rd["s4"]) < minimumpercentage) { s4.ForeColor = System.Drawing.Color.Red; } tr.Cells.Add(s4); TableCell s5 = new TableCell(); s5.Text = rd["s5"].ToString() + " %"; if (Convert.ToInt32(rd["s5"]) < minimumpercentage) { s5.ForeColor = System.Drawing.Color.Red; } tr.Cells.Add(s5); tbl_attend.Rows.Add(tr); } con.Close(); } catch (Exception ae) { //Response.Write("<script>alert('Data Not Found')</script>"); } }
/// <summary> /// /// select a row from table t_RBSR_AUFW_u_AdditionalBusRole. /// </summary> /// <param name="ID"></param> /// <returns>returnGetAdditionalBusRole</returns> public returnGetAdditionalBusRole GetAdditionalBusRole(int ID) { returnGetAdditionalBusRole rv = new returnGetAdditionalBusRole(); DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); cmd.CommandText = "select \"c_id\",\"c_u_idAdditionalBusRole\",\"c_r_BusRole\",\"c_u_Comment\",\"c_u_RecertificationInterval\",\"c_u_RecertificationStartDate\",\"c_u_ExpirationDate\" from \"t_RBSR_AUFW_u_AdditionalBusRole\" where \"c_id\"= ?"; cmd.Parameters.Add("c_id", OdbcType.Int); cmd.Parameters["c_id"].Value = (object)ID; cmd.Connection = _dbConnection; OdbcDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr.IsDBNull(0)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'"); } else { rv.ID = dr.GetInt32(0); } if (dr.IsDBNull(1)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'idAdditionalBusRole'"); } else { rv.idAdditionalBusRole = dr.GetInt32(1); } if (dr.IsDBNull(2)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'BusRoleID'"); } else { rv.BusRoleID = dr.GetInt32(2); } if (dr.IsDBNull(3)) { rv.Comment = null; } else { rv.Comment = dr.GetString(3); } if (dr.IsDBNull(4)) { rv.RecertificationInterval = null; } else { rv.RecertificationInterval = dr.GetString(4); } if (dr.IsDBNull(5)) { rv.RecertificationStartDate = null; } else { rv.RecertificationStartDate = dr.GetDateTime(5); } if (dr.IsDBNull(6)) { rv.ExpirationDate = null; } else { rv.ExpirationDate = dr.GetDateTime(6); } } dr.Close(); dr.Dispose(); cmd.Dispose(); DBClose(); return(rv); }
static void Main(string[] args) { _pgmName = System.AppDomain.CurrentDomain.FriendlyName; // alternative: System.Diagnostics.Process.GetCurrentProcess().ProcessName // Read configuration file _driver = ConfigurationManager.AppSettings["Driver"]; _server = ConfigurationManager.AppSettings["Server"]; _database = ConfigurationManager.AppSettings["Database"]; _table = ConfigurationManager.AppSettings["Table"]; _options = ConfigurationManager.AppSettings["Options"]; _query = ConfigurationManager.AppSettings["Query"]; // Parse command line bool show_help = false; OptionSet p = new OptionSet() .Add( "s:|server|S|Server", "The SQL Server instance to connect to.", v => { if (v != null) { _server = v; } } ) .Add( "d=|database|D|Database", "The SQL Server database to connect to.", v => { if (v != null) { _database = v; } } ) .Add( "t=|table|T|Table", "The SQL Server table whose columns will be returned.", v => { if (v != null) { _table = v; } } ) .Add( "v", "Increase debug message verbosity.", v => { if (v != null) { ++_verbosity; } } ) .Add( "?|h|help", "Display this help message.", v => show_help = (v != null) ); List <string> extra; try { extra = p.Parse(args); } catch (OptionException e) { Console.Write(String.Format("{0}: ", _pgmName)); Console.WriteLine(e.Message); Console.WriteLine(String.Format("Try '{0} --help' for more information.", _pgmName)); return; } if (show_help || (_driver == null || _server == null || _database == null || _table == null || _query == null)) { ShowHelp(p); return; } // Main processing List <string> columns = new List <string>(); try { using (OdbcConnection connection = new OdbcConnection(GetConnectionString())) { // Connect to the database. Debug("Connection String: {0}", connection.ConnectionString); connection.Open(); using (OdbcCommand cmd = new OdbcCommand(GetQueryString(), connection)) { Debug("Query String: {0}", cmd.CommandText); using (OdbcDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) { for (int col = 0; col < reader.FieldCount; col++) { columns.Add(reader.GetName(col).ToString()); // Gets the column name } reader.Close(); } } connection.Close(); } PrintColumnNames(columns); } catch (Exception e) { Console.WriteLine(e.Message); } }
void Cartelera() // Obtiene los datos de las funciones de la BD { //Gustavo Perez int noCartelera = 0; if (formatoPeli == "Formato" && idiomaPeli == "Idioma") // sin ningun flitro { try { conn.Open(); OdbcCommand command = new OdbcCommand("" + "select A.idfuncion," + "(SELECT titulo FROM peliculas WHERE idPelicula = A.idPelicula) titulo," + "(SELECT formato FROM peliculas WHERE idPelicula = A.idPelicula) formato ," + "(SELECT clasificación FROM peliculas WHERE idPelicula = A.idPelicula) clasificacion," + "(SELECT sinopsis FROM peliculas WHERE idPelicula = A.idPelicula) sinopsis, A.horaFuncion " + "from funciones A where A.Cine = '" + cineSeleccionado + "' " + "AND (SELECT semanaEstrenoInicio from peliculas WHERE idPelicula = A.idPelicula) <= '" + fechaPeli + "'" + "AND(SELECT semanaEstrenoFin from peliculas WHERE idPelicula = A.idPelicula) >= '" + fechaPeli + "'", conn); OdbcDataReader funciones = command.ExecuteReader(); int pos = 1; while (funciones.Read()) { salas[pos] = funciones.GetValue(0).ToString(); // funcion pos++; salas[pos] = funciones.GetValue(1).ToString(); //titulo pos++; salas[pos] = funciones.GetValue(2).ToString(); //formato pos++; salas[pos] = funciones.GetValue(3).ToString(); // clasificacion pos++; salas[pos] = funciones.GetValue(4).ToString(); //sinopsis pos++; salas[pos] = funciones.GetValue(5).ToString(); //hora pos++; numSalas++; noCartelera++; } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } } else if (formatoPeli != "Formato" && idiomaPeli == "Idioma") // filtrando formato { try { conn.Open(); OdbcCommand command = new OdbcCommand("" + "select A.idfuncion," + "(SELECT titulo FROM peliculas WHERE idPelicula = A.idPelicula) titulo," + "(SELECT formato FROM peliculas WHERE idPelicula = A.idPelicula) formato ," + "(SELECT clasificación FROM peliculas WHERE idPelicula = A.idPelicula) clasificacion," + "(SELECT sinopsis FROM peliculas WHERE idPelicula = A.idPelicula) sinopsis, A.horaFuncion " + "from funciones A, peliculas where A.Cine = '" + cineSeleccionado + "'AND A.idPelicula = peliculas.idPelicula " + "AND formato ='" + formatoPeli + "'" + "AND (SELECT semanaEstrenoInicio from peliculas WHERE idPelicula = A.idPelicula) <= '" + fechaPeli + "'" + "AND(SELECT semanaEstrenoFin from peliculas WHERE idPelicula = A.idPelicula) >= '" + fechaPeli + "'", conn); OdbcDataReader funciones = command.ExecuteReader(); int pos = 1; while (funciones.Read()) { salas[pos] = funciones.GetValue(0).ToString(); // funcion pos++; salas[pos] = funciones.GetValue(1).ToString(); //titulo pos++; salas[pos] = funciones.GetValue(2).ToString(); //formato pos++; salas[pos] = funciones.GetValue(3).ToString(); // clasificacion pos++; salas[pos] = funciones.GetValue(4).ToString(); //sinopsis pos++; salas[pos] = funciones.GetValue(5).ToString(); //hora pos++; numSalas++; noCartelera++; } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } } else if (formatoPeli == "Formato" && idiomaPeli != "Idioma") // filtrando idioma { try { conn.Open(); OdbcCommand command = new OdbcCommand("" + "select A.idfuncion," + "(SELECT titulo FROM peliculas WHERE idPelicula = A.idPelicula) titulo," + "(SELECT formato FROM peliculas WHERE idPelicula = A.idPelicula) formato ," + "(SELECT clasificación FROM peliculas WHERE idPelicula = A.idPelicula) clasificacion," + "(SELECT sinopsis FROM peliculas WHERE idPelicula = A.idPelicula) sinopsis, A.horaFuncion " + "from funciones A, peliculas where A.Cine = '" + cineSeleccionado + "' " + "AND A.idPelicula = peliculas.idPelicula AND idioma ='" + idiomaPeli + "'" + "AND (SELECT semanaEstrenoInicio from peliculas WHERE idPelicula = A.idPelicula) <= '" + fechaPeli + "'" + "AND(SELECT semanaEstrenoFin from peliculas WHERE idPelicula = A.idPelicula) >= '" + fechaPeli + "'", conn); OdbcDataReader funciones = command.ExecuteReader(); int pos = 1; while (funciones.Read()) { salas[pos] = funciones.GetValue(0).ToString(); // funcion pos++; salas[pos] = funciones.GetValue(1).ToString(); //titulo pos++; salas[pos] = funciones.GetValue(2).ToString(); //formato pos++; salas[pos] = funciones.GetValue(3).ToString(); // clasificacion pos++; salas[pos] = funciones.GetValue(4).ToString(); //sinopsis pos++; salas[pos] = funciones.GetValue(5).ToString(); //hora pos++; numSalas++; noCartelera++; } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } } else if (formatoPeli != "Formato" && idiomaPeli != "Idioma") // filtrando idioma y formato { try { conn.Open(); OdbcCommand command = new OdbcCommand("" + "select A.idfuncion," + "(SELECT titulo FROM peliculas WHERE idPelicula = A.idPelicula) titulo," + "(SELECT formato FROM peliculas WHERE idPelicula = A.idPelicula) formato ," + "(SELECT clasificación FROM peliculas WHERE idPelicula = A.idPelicula) clasificacion," + "(SELECT sinopsis FROM peliculas WHERE idPelicula = A.idPelicula) sinopsis, A.horaFuncion " + "from funciones A, peliculas where A.Cine = '" + cineSeleccionado + "'AND A.idPelicula = peliculas.idPelicula AND formato ='" + formatoPeli + "'" + "AND idioma ='" + idiomaPeli + "'" + "AND (SELECT semanaEstrenoInicio from peliculas WHERE idPelicula = A.idPelicula) <= '" + fechaPeli + "'" + "AND(SELECT semanaEstrenoFin from peliculas WHERE idPelicula = A.idPelicula) >= '" + fechaPeli + "'", conn); OdbcDataReader funciones = command.ExecuteReader(); int pos = 1; while (funciones.Read()) { salas[pos] = funciones.GetValue(0).ToString(); // funcion pos++; salas[pos] = funciones.GetValue(1).ToString(); //titulo pos++; salas[pos] = funciones.GetValue(2).ToString(); //formato pos++; salas[pos] = funciones.GetValue(3).ToString(); // clasificacion pos++; salas[pos] = funciones.GetValue(4).ToString(); //sinopsis pos++; salas[pos] = funciones.GetValue(5).ToString(); //hora pos++; numSalas++; noCartelera++; } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } } if (noCartelera == 0) { MessageBox.Show("No Hay peliculas con esas Preferencias..."); this.Close(); th = new Thread(opennewform2); th.SetApartmentState(ApartmentState.STA); th.Start(); } else { tmrCartelera.Enabled = true; } }
private void Btn_guardar_Click(object sender, EventArgs e) { bool bValidacionTipoDeporte = false; bool bIngresoCorrecto = true; string host = Dns.GetHostName(); IPAddress[] IP = Dns.GetHostAddresses(host); string sFecha = DateTime.Now.ToString("yyy/MM/dd"); string sHora = DateTime.Now.ToString("hh:mm:ss"); string scodigoTipoDeporte = ""; if (Cbo_deportes.SelectedItem != null) { string snombreTipoDeporte = Cbo_deportes.SelectedItem.ToString(); //consulta el codigo del tipo del deporte obteniendo el texto del combobox con el nombre del tipo de deporte try { OdbcCommand sql = new OdbcCommand("Select PK_idDeporte from tbl_deportes where nombre_deporte = '" + snombreTipoDeporte + "'", nueva.conectar()); OdbcDataReader almacena = sql.ExecuteReader(); while (almacena.Read() == true) { scodigoTipoDeporte = almacena.GetString(0); } almacena.Close(); sql.Connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } else { bValidacionTipoDeporte = true; } if (bBoton_ingreso == true) { try { if ((Txt_nombreCampeonato.Text == "" || Txt_descripcionCampeonato.Text == "" || Txt_precioCampeonato.Text == "" || Txt_cantidadEquipos.Text == "")) { MessageBox.Show("Hacen Falta Campos Por Llenar", "INFORMACION", MessageBoxButtons.OK, MessageBoxIcon.Information); bIngresoCorrecto = false; } else { cmd = new OdbcCommand("INSERT INTO tbl_campeonatos(FK_idDeporte, nombre_campeonato, descripcion_campeonato, precio_campeonato, cantidad_equipos, estado_campeonato) VALUES ('" + scodigoTipoDeporte + "', '" + Txt_nombreCampeonato.Text + "', '" + Txt_descripcionCampeonato.Text + "', '" + Txt_precioCampeonato.Text + "', '" + Txt_cantidadEquipos.Text + "', 1)", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } catch (OdbcException ex) { MessageBox.Show(ex.Message); bIngresoCorrecto = false; } if (bIngresoCorrecto) { MessageBox.Show("Campeonato Ingresado Correctamente"); Txt_nombreCampeonato.Text = ""; Txt_descripcionCampeonato.Text = ""; Txt_precioCampeonato.Text = ""; Txt_cantidadEquipos.Text = ""; habilitarTodo(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Ingreso de Campeonato", "tbl_campeonatos", frm_login.IP[2].ToString()); } } else if (bBoton_modificar == true) { //bool bIngresoCorrecto = true; try { if ((Txt_nombreCampeonato.Text == "" || Txt_descripcionCampeonato.Text == "" || Txt_precioCampeonato.Text == "" || Txt_cantidadEquipos.Text == "")) { MessageBox.Show("Hacen Falta Campos Por Llenar", "INFORMACION", MessageBoxButtons.OK, MessageBoxIcon.Information); bIngresoCorrecto = false; } else { if (Rdb_habilitado.Checked == true) { cmd = new OdbcCommand("UPDATE tbl_campeonatos SET FK_idDeporte='" + scodigoTipoDeporte + "', nombre_campeonato='" + Txt_nombreCampeonato.Text + "', descripcion_campeonato='" + Txt_descripcionCampeonato.Text + "', precio_campeonato='" + Txt_precioCampeonato.Text + "', cantidad_equipos='" + Txt_cantidadEquipos.Text + "', estado_campeonato=1 WHERE PK_idCampeonato = '" + Txt_codigoCampeonato.Text + "'", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } else if (Rbd_deshabilitado.Checked == true) { cmd = new OdbcCommand("UPDATE tbl_campeonatos SET FK_idDeporte='" + scodigoTipoDeporte + "', nombre_campeonato='" + Txt_nombreCampeonato.Text + "', descripcion_campeonato='" + Txt_descripcionCampeonato.Text + "', precio_campeonato='" + Txt_precioCampeonato.Text + "', cantidad_equipos='" + Txt_cantidadEquipos.Text + "', estado_campeonato=0 WHERE PK_idCampeonato = '" + Txt_codigoCampeonato.Text + "'", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } } catch (OdbcException ex) { MessageBox.Show(ex.Message); bIngresoCorrecto = false; } if (bIngresoCorrecto) { MessageBox.Show("Campeonato Modificado Correctamente"); Txt_nombreCampeonato.Text = ""; Txt_descripcionCampeonato.Text = ""; Txt_precioCampeonato.Text = ""; Txt_cantidadEquipos.Text = ""; Rdb_habilitado.Checked = false; Rbd_deshabilitado.Checked = false; habilitarTodo(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Modificacion de Campeonato", "tbl_campeonatos", frm_login.IP[2].ToString()); } } else if (bBoton_eliminar == true) { //bool ingresoCorrecto = true; try { cmd = new OdbcCommand("UPDATE tbl_campeonatos SET estado_campeonato=0 WHERE PK_idCampeonato ='" + Txt_codigoCampeonato.Text + "'", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (OdbcException ex) { MessageBox.Show(ex.Message); bIngresoCorrecto = false; } if (bIngresoCorrecto) { MessageBox.Show("Campeonato Eliminado Correctamente"); Txt_nombreCampeonato.Text = ""; Txt_descripcionCampeonato.Text = ""; Txt_precioCampeonato.Text = ""; Txt_cantidadEquipos.Text = ""; Rdb_habilitado.Checked = false; Rbd_deshabilitado.Checked = false; habilitarTodo(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Eliminacion de Campeonato", "tbl_campeonatos", frm_login.IP[2].ToString()); } } }
private void Btn_guardar_Click(object sender, EventArgs e) { string host = Dns.GetHostName(); IPAddress[] IP = Dns.GetHostAddresses(host); string sFecha = DateTime.Now.ToString("yyy/MM/dd"); string sHora = DateTime.Now.ToString("hh:mm:ss"); if (boton_ingreso == true) { bool ingresoCorrecto = true; try { if (Txt_nombreEntrenador.Text == "" || Txt_apellidoEntrenador.Text == "" || Txt_especialidadEntrenador.Text == "") { MessageBox.Show("Hacen Falta Campos Por Llenar", "INFORMACION", MessageBoxButtons.OK, MessageBoxIcon.Information); ingresoCorrecto = false; } else { cmd = new OdbcCommand("INSERT INTO tbl_entrenadores(nombre_entrenador, apellido_entrenador, especialidad_entrenador, estado_entrenador) VALUES ('" + Txt_nombreEntrenador.Text + "', '" + Txt_apellidoEntrenador.Text + "', '" + Txt_especialidadEntrenador.Text + "' , 1)", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } catch (OdbcException ex) { MessageBox.Show(ex.Message); ingresoCorrecto = false; } if (ingresoCorrecto) { MessageBox.Show("Entrenador Ingresado Correctamente"); Txt_nombreEntrenador.Text = ""; Txt_apellidoEntrenador.Text = ""; Txt_especialidadEntrenador.Text = ""; Cbo_equipo.Text = ""; habilitarTodo(); int iLastId = 0; OdbcCommand sql = new OdbcCommand("SELECT PK_idEntrenador FROM tbl_entrenadores ORDER by PK_idEntrenador DESC LIMIT 1", nueva.conectar()); OdbcDataReader almacena = sql.ExecuteReader(); while (almacena.Read() == true) { iLastId = almacena.GetInt32(0); } almacena.Close(); sql.Connection.Close(); cmd = new OdbcCommand("INSERT INTO tbl_equipos_entrenadores(PK_idEntrenador, PK_idEquipo) VALUES (" + iLastId.ToString() + ", " + lIdEquipo[Cbo_equipo.SelectedIndex].ToString() + " )", nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Ingreso de Entrenador", "tbl_entrenadores", frm_login.IP[2].ToString()); } } else if (boton_modificar == true) { bool ingresoCorrecto = true; try { if (Txt_nombreEntrenador.Text == "" || Txt_apellidoEntrenador.Text == "" || Txt_especialidadEntrenador.Text == "") { MessageBox.Show("Hacen Falta Campos Por Llenar", "INFORMACION", MessageBoxButtons.OK, MessageBoxIcon.Information); ingresoCorrecto = false; } else { if (Rdb_habilitado.Checked == true) { cmd = new OdbcCommand("UPDATE tbl_entrenadores SET nombre_entrenador='" + Txt_nombreEntrenador.Text + "', apellido_entrenador='" + Txt_apellidoEntrenador.Text + "', especialidad_entrenador='" + Txt_especialidadEntrenador.Text + "' , estado_entrenador=1 " + " WHERE PK_idEntrenador=" + Txt_codigoEntrenador.Text , nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd = new OdbcCommand("UPDATE tbl_equipos_entrenadores SET PK_idEquipo=" + lIdEquipo[Cbo_equipo.SelectedIndex].ToString() + " WHERE PK_idEntrenador =" + Txt_codigoEntrenador.Text, nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } else if (Rbd_deshabilitado.Checked == true) { cmd = new OdbcCommand("UPDATE tbl_entrenadores SET nombre_entrenador='" + Txt_nombreEntrenador.Text + "', apellido_entrenador='" + Txt_apellidoEntrenador.Text + "', especialidad_entrenador='" + Txt_especialidadEntrenador.Text + "' , estado_entrenador=0 " + " WHERE PK_idEntrenador=" + Txt_codigoEntrenador.Text , nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd = new OdbcCommand("UPDATE tbl_equipos_entrenadores SET PK_idEquipo=" + lIdEquipo[Cbo_equipo.SelectedIndex].ToString() + " WHERE PK_idEntrenador =" + Txt_codigoEntrenador.Text, nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } } catch (OdbcException ex) { MessageBox.Show(ex.Message); ingresoCorrecto = false; } if (ingresoCorrecto) { MessageBox.Show("Entrenador Modificado Correctamente"); Txt_nombreEntrenador.Text = ""; Txt_apellidoEntrenador.Text = ""; Txt_especialidadEntrenador.Text = ""; Cbo_equipo.Text = ""; Rdb_habilitado.Checked = false; Rbd_deshabilitado.Checked = false; habilitarTodo(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Modificacion de Entrenador", "tbl_entrenadores", frm_login.IP[2].ToString()); } } else if (boton_eliminar == true) { bool ingresoCorrecto = true; try { cmd = new OdbcCommand("UPDATE tbl_entrenadores SET estado_entrenador=0 WHERE PK_idEntrenador =" + Txt_codigoEntrenador.Text, nueva.conectar()); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch (OdbcException ex) { MessageBox.Show(ex.Message); ingresoCorrecto = false; } if (ingresoCorrecto) { MessageBox.Show("Entrenador Eliminado Correctamente"); Txt_nombreEntrenador.Text = ""; Txt_apellidoEntrenador.Text = ""; Txt_especialidadEntrenador.Text = ""; Cbo_equipo.Text = ""; Rdb_habilitado.Checked = false; Rbd_deshabilitado.Checked = false; habilitarTodo(); //REGISTRO BITACORA MANTENIMIENTOS----------------------------------------- Bitacora.CrearBitacoraMantenimiento(frm_login.nombre_usuarioparabitacora, "Eliminacion de Entrenador", "tbl_entrenadores", frm_login.IP[2].ToString()); } } }
public ActionResult getSupplierLocation(string search) { LocationClass supplier = 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}, 'DROP' , '{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()) { supplier.x = double.Parse(myReader["LON"].ToString()); supplier.y = double.Parse(myReader["LAT"].ToString()); supplier.name = myReader["FACILITYNAME"]?.ToString(); supplier.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(supplier), "application/json", System.Text.Encoding.UTF8); return(data); }
private void textBoxSO_Input(object sender, KeyEventArgs e) { if (Keys.Enter == e.KeyCode && 6 == textBoxSO.TextLength && Convert.ToInt32(textBoxSO.Text) > 200000) { salesOrder = textBoxSO.Text; pieceList.Clear(); // get created work orders string query = "select dnjob, dnpart from cmsdat.cjobh where dnord#=" + salesOrder; ExcoODBC solarsoft = ExcoODBC.Instance; solarsoft.Open(Database.CMSDAT); OdbcDataReader reader = solarsoft.RunQuery(query); while (reader.Read()) { Piece piece = new Piece(); piece.workOrder = reader[0].ToString().Trim(); piece.part = reader[1].ToString().Trim(); pieceList.Add(piece); } reader.Close(); // if no pieces, do nothing if (0 == pieceList.Count) { MessageBox.Show("Not an active order!"); return; } // get order details query = "select dcbcus, dcbnam, dcodat, dccdat from cmsdat.ocrh where dcord#=" + salesOrder; reader = solarsoft.RunQuery(query); if (reader.Read()) { customerID = reader[0].ToString().Trim(); customerName = reader[1].ToString().Trim(); textBoxOrderDate.Text = Convert.ToDateTime(reader[2]).ToString("yyyy-MM-dd"); textBoxReqDate.Text = Convert.ToDateTime(reader[3]).ToString("yyyy-MM-dd"); } reader.Close(); // check if sub parts are needed foreach (Piece piece in pieceList) { if ("HD" == piece.part.Substring(0, 2) && 7 == piece.workOrder.Length) { query = "select efmtlp from cmsdat.cjobdm where efjob#='" + piece.workOrder + "'"; reader = solarsoft.RunQuery(query); while (reader.Read()) { bool hasSubPart = false; foreach (Piece subPart in pieceList) { if (reader[0].ToString().Trim() == subPart.part) { hasSubPart = true; break; } } if (!hasSubPart) { Piece newPiece = new Piece(); newPiece.part = reader[0].ToString().Trim(); newPiece.workOrder = "New"; pieceList.Add(newPiece); } } reader.Close(); break; } } // display in textBoxPart richTextBoxPart.Clear(); richTextBoxPart.Text = customerID + " (" + customerName + ")" + Environment.NewLine; foreach (Piece piece in pieceList) { richTextBoxPart.Text += piece.workOrder + ", " + piece.part + Environment.NewLine; } // display in features and options richTextBoxFeaturesAndOptions.Clear(); richTextBoxFeaturesAndOptions.Text = string.Empty; foreach (Piece piece in pieceList) { if (7 == piece.workOrder.Length) { richTextBoxFeaturesAndOptions.Text += piece.workOrder + ":" + Environment.NewLine; query = "select r0fcde, r0ocde, r0qtyr FROM cmsdat.food where r0lstn='" + piece.workOrder + "'"; reader = solarsoft.RunQuery(query); while (reader.Read()) { richTextBoxFeaturesAndOptions.Text += reader[0].ToString().Trim() + ", "; richTextBoxFeaturesAndOptions.Text += reader[1].ToString().Trim() + ", "; richTextBoxFeaturesAndOptions.Text += reader[2].ToString().Trim() + Environment.NewLine; // check number of cavities if ("CAVITIES" == reader[0].ToString().Trim() && "HOLES" == reader[1].ToString().Trim()) { textBoxNumOfCavs.Text = reader[2].ToString().Trim(); } } reader.Close(); richTextBoxFeaturesAndOptions.Text += Environment.NewLine; } } // get details of pieces foreach (Piece piece in pieceList) { // hollow die sub parts if (8 == piece.workOrder.Length) { // backer if (piece.part.Contains("BAC")) { checkBoxBacker.Checked = true; groupBoxBacker.Enabled = true; query = "select efqppc from cmsdat.cjobdm where efjob#='" + piece.workOrder + "'"; reader = solarsoft.RunQuery(query); if (reader.Read()) { textBoxThkBacker.Text = (Convert.ToDouble(reader[0]) * 25.4).ToString("F2"); } reader.Close(); } // plate else if (piece.part.Contains("PLA")) { checkBoxPlate.Checked = true; groupBoxPlate.Enabled = true; } // mandrel else if (piece.part.Contains("MAN")) { checkBoxMandrel.Checked = true; groupBoxMandrel.Enabled = true; } else { throw new Exception("Invalid hollow piece type " + piece.part); } } // parent piece or solid die else { } } } // enable save button buttonSave.Enabled = true; }
/// <summary> /// /// select a set of rows from table t_RBSR_AUFW_u_AdditionalBusRole. /// </summary> /// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param> /// <param name="BusRoleID"></param> /// <returns>returnListAdditionalBusRoleByBusRole[]</returns> public returnListAdditionalBusRoleByBusRole[] ListAdditionalBusRoleByBusRole(int?maxRowsToReturn, int BusRoleID) { returnListAdditionalBusRoleByBusRole[] rv = null; DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0) { if (_dbConnection.Driver.ToLower().StartsWith("sql")) { cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_idAdditionalBusRole\", \"c_r_BusRole\", \"c_u_Comment\", \"c_u_RecertificationInterval\", \"c_u_RecertificationStartDate\", \"c_u_ExpirationDate\" FROM \"t_RBSR_AUFW_u_AdditionalBusRole\" WHERE \"c_r_BusRole\"=?"; } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_idAdditionalBusRole\", \"c_r_BusRole\", \"c_u_Comment\", \"c_u_RecertificationInterval\", \"c_u_RecertificationStartDate\", \"c_u_ExpirationDate\" FROM \"t_RBSR_AUFW_u_AdditionalBusRole\" WHERE \"c_r_BusRole\"=?" + " LIMIT " + maxRowsToReturn.Value; } } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_idAdditionalBusRole\", \"c_r_BusRole\", \"c_u_Comment\", \"c_u_RecertificationInterval\", \"c_u_RecertificationStartDate\", \"c_u_ExpirationDate\" FROM \"t_RBSR_AUFW_u_AdditionalBusRole\" WHERE \"c_r_BusRole\"=?"; } cmd.Parameters.Add("1_BusRoleID", OdbcType.Int); cmd.Parameters["1_BusRoleID"].Value = (object)BusRoleID; OdbcDataReader dr = cmd.ExecuteReader(); List <returnListAdditionalBusRoleByBusRole> rvl = new List <returnListAdditionalBusRoleByBusRole>(); while (dr.Read()) { returnListAdditionalBusRoleByBusRole cr = new returnListAdditionalBusRoleByBusRole(); if (dr.IsDBNull(0)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'"); } else { cr.ID = dr.GetInt32(0); } if (dr.IsDBNull(1)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'idAdditionalBusRole'"); } else { cr.idAdditionalBusRole = dr.GetInt32(1); } if (dr.IsDBNull(2)) { cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'BusRoleID'"); } else { cr.BusRoleID = dr.GetInt32(2); } if (dr.IsDBNull(3)) { cr.Comment = null; } else { cr.Comment = dr.GetString(3); } if (dr.IsDBNull(4)) { cr.RecertificationInterval = null; } else { cr.RecertificationInterval = dr.GetString(4); } if (dr.IsDBNull(5)) { cr.RecertificationStartDate = null; } else { cr.RecertificationStartDate = dr.GetDateTime(5); } if (dr.IsDBNull(6)) { cr.ExpirationDate = null; } else { cr.ExpirationDate = dr.GetDateTime(6); } rvl.Add(cr); } dr.Close(); dr.Dispose(); rv = rvl.ToArray(); cmd.Dispose(); DBClose(); return(rv); }
public MainDialog() { InitializeComponent(); CancelButton = buttonCancel; // temp List <SteelData> steelData = new List <SteelData>(); string query = "select distinct heat from dbo.d_steel where cuttime >= '2013-03-01 00:00:00' order by heat"; ExcoODBC decade = ExcoODBC.Instance; decade.Open(Database.DECADE); OdbcDataReader reader = decade.RunQuery(query); while (reader.Read()) { SteelData data = new SteelData(); data.id = reader[0].ToString(); steelData.Add(data); } reader.Close(); for (int i = 0; i < steelData.Count; i++) { List <string> soList = new List <string>(); query = "select distinct ordernumber from dbo.d_steel where heat='" + steelData[i].id + "' order by ordernumber"; decade = ExcoODBC.Instance; decade.Open(Database.DECADE); reader = decade.RunQuery(query); while (reader.Read()) { soList.Add(reader[0].ToString()); } reader.Close(); query = "select sum(byqty) from cmsdat.stkt, cmsdat.hjobh where bydref='PRODUCTION BACKFLUSH UTILITY' and bytunt='IN' and byjref=dnjob and (dnord#="; foreach (string so in soList) { if (so.Contains(soList[soList.Count - 1])) { query += so + ")"; } else { query += so + " or dnord#="; } } double result = 0.0; ExcoODBC solarsoft = ExcoODBC.Instance; solarsoft.Open(Database.CMSDAT); reader = solarsoft.RunQuery(query); if (reader.Read() && reader[0] != DBNull.Value) { result += Convert.ToDouble(reader[0]); } reader.Close(); query = "select sum(byqty) from cmsdat.stkt, cmsdat.cjobh where bydref='PRODUCTION BACKFLUSH UTILITY' and bytunt='IN' and byjref=dnjob and (dnord#="; foreach (string so in soList) { if (so.Contains(soList[soList.Count - 1])) { query += so + ")"; } else { query += so + " or dnord#="; } } reader = solarsoft.RunQuery(query); if (reader.Read()) { if (reader[0] != DBNull.Value) { result += Convert.ToDouble(reader[0]); } } reader.Close(); SteelData data = steelData[i]; data.usage = result.ToString("F2"); steelData[i] = data; } StreamWriter writer = File.CreateText("aa.txt"); foreach (SteelData data in steelData) { writer.Write(data.id + '\t' + data.usage + Environment.NewLine); } writer.Close(); //// get missing wo ////for (int i = 1; i < dataList.Count; i++) ////{ //// Data data = dataList[i]; //// if (data.wo.Length == 9) //// { //// while (Convert.ToInt32(dataList[i].wo) - 1 > Convert.ToInt32(dataList[i-1].wo)) //// { //// Data newData = new Data(); //// newData.wo = "00" + (Convert.ToInt32(dataList[i].wo) - 1).ToString(); //// dataList.Insert(i, newData); //// } //// } //// else if (data.wo.Length == 7) //// { //// if (dataList[i - 1].wo.Length != 9) //// { //// while (Convert.ToInt32(dataList[i].wo) - 1 > Convert.ToInt32(dataList[i - 1].wo)) //// { //// Data newData = new Data(); //// newData.wo = "00" + (Convert.ToInt32(dataList[i].wo) - 1).ToString(); //// dataList.Insert(i, newData); //// } //// } //// else //// { //// i++; //// } //// } ////} //// get so ////List<Data> tempData = new List<Data>(); //for (int i = 0; i < dataList.Count; i++) //{ // Data data = dataList[i]; // query = "select dnord# from cmsdat.cjobh where dnjob='" + dataList[i].wo + "' and dnstat!='N'"; // reader = solarsoft.RunQuery(query); // if (reader.Read()) // { // data.so = reader[0].ToString(); // } // else // { // reader.Close(); // query = "select dnord# from cmsdat.hjobh where dnjob='" + dataList[i].wo + "'"; // reader = solarsoft.RunQuery(query); // if (reader.Read()) // { // data.so = reader[0].ToString(); // } // else // { // //data.wo = "NULL"; // data.so = "NULL"; // //data.steel = "NULL"; // } // } // reader.Close(); //if (!data.wo.Contains("NULL")) //{ // if (data.steel==null) // { // tempData.Add(data); // } //} // dataList[i] = data; //} //StreamWriter writer = File.CreateText("aa.txt"); //foreach (Data data in dataList) //{ // writer.Write(data.wo + '\t' + data.so + '\t' + data.steel + '\t' + data.stock + '\t' + data.length + '\t' + data.part + Environment.NewLine); //} //writer.Close(); }
public RoundsList(int sectionID, int roundNumber) { using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString)) { connection.Open(); if (AppData.IsIndividual) { string SQLString = $"SELECT [Table], NSPair, EWPair, LowBoard, HighBoard, South, West FROM RoundData WHERE Section={sectionID} AND Round={roundNumber}"; OdbcCommand cmd = new OdbcCommand(SQLString, connection); OdbcDataReader reader = null; try { ODBCRetryHelper.ODBCRetry(() => { reader = cmd.ExecuteReader(); while (reader.Read()) { Round round = new Round() { TableNumber = reader.GetInt32(0), NumberNorth = reader.GetInt32(1), NumberEast = reader.GetInt32(2), LowBoard = reader.GetInt32(3), HighBoard = reader.GetInt32(4), NumberSouth = reader.GetInt32(5), NumberWest = reader.GetInt32(6) }; Add(round); } }); } finally { reader.Close(); cmd.Dispose(); } } else // Not individual { string SQLString = $"SELECT [Table], NSPair, EWPair, LowBoard, HighBoard FROM RoundData WHERE Section={sectionID} AND Round={roundNumber}"; OdbcCommand cmd = new OdbcCommand(SQLString, connection); OdbcDataReader reader = null; try { ODBCRetryHelper.ODBCRetry(() => { reader = cmd.ExecuteReader(); while (reader.Read()) { Round round = new Round() { TableNumber = reader.GetInt32(0), NumberNorth = reader.GetInt32(1), NumberEast = reader.GetInt32(2), LowBoard = reader.GetInt32(3), HighBoard = reader.GetInt32(4), }; Add(round); } }); } finally { reader.Close(); cmd.Dispose(); } } } }
private static async Task startCrawlerasync() { //the url of the page we want to test var url = "http://www.automobile.tn/neuf/bmw.3/"; var httpClient = new HttpClient(); var html = await httpClient.GetStringAsync(url); var htmlDocument = new HtmlDocument(); htmlDocument.LoadHtml(html); // a list to add all the list of cars and the various prices var cars = new List <Carro>(); var divs = htmlDocument.DocumentNode.Descendants("div") .Where(node => node.GetAttributeValue("class", "").Equals("article_new_car article_last_modele")).ToList(); foreach (var div in divs) { var car = new Carro { Model = div.Descendants("h2").FirstOrDefault().InnerText, Price = div.Descendants("div").FirstOrDefault().InnerText, Link = div.Descendants("a").FirstOrDefault().ChildAttributes("href").FirstOrDefault().Value, ImageUrl = div.Descendants("img").FirstOrDefault().ChildAttributes("src").FirstOrDefault().Value }; cars.Add(car); } // Connection string string MyConnection = "DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=crawlerdemo;User Id=root;Password="******"datasource=localhost;username=root;password="******"insert into carinfor(Model,Price,Link,ImageUrl) value(?,?,?,?);"; OdbcCommand cmd = new OdbcCommand(query, con); cmd.Parameters.Add("?Model", OdbcType.VarChar).Value = cars[i].Model; cmd.Parameters.Add("?Price", OdbcType.VarChar).Value = cars[i].Price; cmd.Parameters.Add("?Link", OdbcType.VarChar).Value = cars[i].Link; cmd.Parameters.Add("?ImageUrl", OdbcType.VarChar).Value = cars[i].ImageUrl; OdbcDataReader reader = cmd.ExecuteReader(); reader.Close(); } count = 0; } } catch (Exception ex) { Console.WriteLine(ex.Message); } con.Close(); Console.WriteLine("Successful...."); Console.WriteLine("Press Enter to exit the program..."); ConsoleKeyInfo keyinfor = Console.ReadKey(true); if (keyinfor.Key == ConsoleKey.Enter) { System.Environment.Exit(0); } }
public void SendEMail(string Status, int UserCheck) { string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString; EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"]; DEPARTMENT_NO = Request.QueryString["DEP_NO"]; MANAGER = Request.QueryString["MNG"]; HRMANAGER = Request.QueryString["HRMNG"]; GMANAGER = Request.QueryString["GM"]; string date; date = DateTime.Now.Date.ToString("dd-MMMM-yyyy"); // Requester string sql = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " + "FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " + "USERS_INFORMATIONS.EMPLOYEE_NO = '" + TextBox2.Text + "'"; // Approval (MNG) //string sql2 = "SELECT FULL_USER_NAME FROM USERS_INFORMATIONS WHERE EMPLOYEE_NO = '" + EMPLOYEE_NO + "'"; //DEP_NO string sql2 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " + "FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " + "USERS_INFORMATIONS.EMPLOYEE_NO = '" + MANAGER + "'"; // Approval (GM MNG) string sql3 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " + "FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " + "USERS_INFORMATIONS.EMPLOYEE_NO = '" + GMANAGER + "'"; // Approval (HR MNG) string sql4 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " + "FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " + "USERS_INFORMATIONS.EMPLOYEE_NO = '" + HRMANAGER + "'"; OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand(sql, conn); OdbcCommand cmd2 = new OdbcCommand(sql2, conn); OdbcCommand cmd3 = new OdbcCommand(sql3, conn); OdbcCommand cmd4 = new OdbcCommand(sql4, conn); cmd.Connection.Open(); OdbcDataReader read = cmd.ExecuteReader(); OdbcDataReader read2 = cmd2.ExecuteReader(); OdbcDataReader read3 = cmd3.ExecuteReader(); OdbcDataReader read4 = cmd4.ExecuteReader(); read.Read(); read2.Read(); read3.Read(); read4.Read(); if (read.HasRows) // Requester { EmpName = read["FULL_USER_NAME"].ToString(); EmpEmail = read["EMAIL"].ToString(); Depart = read["DEP_NAME"].ToString(); } if (read2.HasRows) // Approval (MNG) { EmpName2 = read2["FULL_USER_NAME"].ToString(); EmpEmail2 = read2["EMAIL"].ToString(); //AUTHORNAME = read2["FULL_USER_NAME"].ToString(); } if (read3.HasRows) // Approval (GM MNG) { EmpName3 = read3["FULL_USER_NAME"].ToString(); EmpEmail3 = read3["EMAIL"].ToString(); //AUTHORNAME = read3["FULL_USER_NAME"].ToString(); } if (read4.HasRows) // Approval (HR MNG) { EmpName4 = read4["FULL_USER_NAME"].ToString(); EmpEmail4 = read4["EMAIL"].ToString(); //AUTHORNAME = read4["FULL_USER_NAME"].ToString(); } // pass values and send e-mails //if (MANAGER == EMPLOYEE_NO) if (UserCheck == 1) { Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" + "&ApprovalName=" + EmpName3 + "&ApprovalEmail=" + EmpEmail3 + "&REQNAME=Travel Request (TRF)&AUTHORNAME=" + EmpName2 + ""); } else //if (GMANAGER == EMPLOYEE_NO) if (UserCheck == 2) { Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" + "&ApprovalName=" + EmpName4 + "&ApprovalEmail=" + EmpEmail4 + "&REQNAME=Travel Request (TRF)&AUTHORNAME=" + EmpName3 + ""); } else //if (HRMANAGER == EMPLOYEE_NO) if (UserCheck == 3) { Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" + "&ApprovalName=&ApprovalEmail=&REQNAME=Travel Request (TRF)&AUTHORNAME=" + EmpName4 + ""); } cmd.Connection.Close(); cmd2.Connection.Close(); }
//===========================GetDetails_Alarm_Billing=========================== public Details GetDetails_Alarm_Billing(string id) { OdbcConnection dbConn = new OdbcConnection(); dbConn.ConnectionString = ComPlusconstring; dbConn.Open(); OdbcCommand dbComm = default(OdbcCommand); OdbcDataReader dbread = default(OdbcDataReader); string strSQL = null; strSQL = "SELECT a.acct_no, a.acct_type, a.stat, b.e_name as cust_name, a.p_adrs, a.p_city, a.p_state, a.p_zip, d.e_adrs1 as b_adrs1, d.e_adrs2 as b_adrs2, d.e_adrs3 as b_adrs3, d.e_city as b_city, d.e_state as b_state, d.e_zip as b_zip, e.e_phone_no as h_phone, a.parcel_id, i.e_name as owner_name, balance, c.e_adrs1 as own_addy, c.e_adrs2 as own_addy2, c.e_city as o_city, c.e_state as o_state, c.e_zip as o_zip "; strSQL = strSQL + "from cabacct a, cpbentity b, cpbentity_addr c, cpbentity_addr d, outer cpbentity_phone e, cpbentity f, cpbentity i "; strSQL = strSQL + "where a.m_entity_id = b.entity_id "; strSQL = strSQL + "and a.o_entity_id = c.entity_id "; strSQL = strSQL + "and a.o_addr_no = c.e_adrs_ins "; strSQL = strSQL + "and a.m_entity_id = e.entity_id "; strSQL = strSQL + "and a.m_phone_ins = e.e_phone_ins "; strSQL = strSQL + "and a.m_entity_id = d.entity_id "; strSQL = strSQL + "and a.m_addr_no = d.e_adrs_ins "; strSQL = strSQL + "and a.occup_id = f.entity_id "; strSQL = strSQL + "and a.o_entity_id = i.entity_id "; strSQL += "AND a.acct_no = '" + id + "'"; dbComm = new OdbcCommand(strSQL, dbConn); dbread = dbComm.ExecuteReader(); Details results = new Details(); while (dbread.Read()) { results.a = dbread["acct_no"].ToString(); results.b = dbread["acct_type"].ToString(); results.c = dbread["stat"].ToString(); results.d = dbread["cust_name"].ToString(); results.e = dbread["p_adrs"].ToString(); results.f = dbread["p_city"].ToString(); results.g = dbread["p_state"].ToString(); results.h = dbread["p_zip"].ToString(); results.i = dbread["b_adrs1"].ToString(); results.j = dbread["b_adrs2"].ToString(); results.k = dbread["b_adrs3"].ToString(); results.l = dbread["b_city"].ToString(); results.m = dbread["b_state"].ToString(); results.n = dbread["b_zip"].ToString(); results.o = dbread["h_phone"].ToString(); results.p = dbread["parcel_id"].ToString(); results.q = dbread["balance"].ToString(); results.r = dbread["owner_name"].ToString(); results.s = dbread["own_addy"].ToString(); results.t = dbread["own_addy2"].ToString(); results.u = dbread["o_city"].ToString(); results.v = dbread["o_state"].ToString(); results.w = dbread["o_zip"].ToString(); } dbread.Close(); dbComm.Dispose(); dbConn.Close(); dbConn = null; return(results); }
protected String[,] LoadStudentList(DBManager db, string courseID) { // Load all information Course c = new Course(); c.LoadFromDB(db); String sqlCount = "SELECT Count(*) "; String sql = "SELECT r.regis_date, s.firstname, s.surname, s.email, s.tel, s.school ,r.discounted_cost as discounted_cost, r.note as note, r.status as status, r.regis_id as regis_id "; String sqlWhere = " FROM student s, registration r " + " WHERE s.student_id=r.student_id AND s.is_active=1 AND r.course_id=" + courseID + " ORDER BY r.status, s.firstname, s.surname"; int num = db.QueryCount(sqlCount + sqlWhere); String[,] result = new String[num, 9]; int n = 0; OdbcDataReader reader = db.Query(sql + sqlWhere); while (reader.Read()) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); switch (name) { case "regis_date": result[n, 0] = new DateTime(reader.GetDateTime(i).Ticks).ToString(); break; case "firstname": result[n, 1] = reader.GetString(i); break; case "surname": result[n, 1] = result[n, 1] + " " + reader.GetString(i); break; case "school": result[n, 2] = reader.GetString(i); break; case "tel": result[n, 3] = reader.GetString(i); break; case "email": result[n, 4] = reader.GetString(i); break; case "discounted_cost": result[n, 5] = reader.GetInt32(i).ToString(); break; case "status": result[n, 6] = (reader.GetInt32(i) == 0?"ปกติ":"ยกเลิก"); break; case "note": result[n, 7] = reader.GetString(i); break; case "regis_id": result[n, 8] = reader.GetInt32(i).ToString(); break; } } n++; } return(result); }
//===========================Get_UB_ComPlus=========================== public Details GetDetails_ACIS_Historical(string id, string id1) { OdbcConnection dbConn = new OdbcConnection(); dbConn.ConnectionString = ComPlusconstring; dbConn.Open(); OdbcCommand dbComm = default(OdbcCommand); OdbcDataReader dbread = default(OdbcDataReader); string strSQL = null; strSQL = "select a.*, b.*, c.*, d.*, e.*, f.e_name as prop_owner, f.e_adrs1 as po_adrs1, f.e_adrs2 as po_adrs2, f.e_city as po_city, f.e_state as po_state, f.e_zip as po_zip "; strSQL += "from cubaccount a, cpbentity b, cpbentity_addr c, cpbparcel d, outer cpbentity_phone e, cnb_propowner f "; strSQL += "where a.c_entity_id = b.entity_id "; strSQL += "and a.c_entity_id = c.entity_id "; strSQL += "and a.c_addr_ins = c.e_adrs_ins "; strSQL += "and a.parcel_id = d.parcel_id "; strSQL += "and a.c_entity_id = e.entity_id "; strSQL += "and a.c_phone_ins = e.e_phone_ins "; strSQL += "and d.prop_key = f.prop_key "; strSQL += "AND a.cust_no = '" + id + "'"; strSQL += "AND a.cust_ser = '" + id1 + "'"; dbComm = new OdbcCommand(strSQL, dbConn); dbread = dbComm.ExecuteReader(); Details results = new Details(); while (dbread.Read()) { results.a = dbread["cust_no"].ToString(); results.b = dbread["cust_ser"].ToString(); results.c = dbread["stat"].ToString(); results.d = dbread["e_name"].ToString(); results.e = dbread["p_adrs"].ToString(); results.f = dbread["p_city"].ToString(); results.g = dbread["p_state"].ToString(); results.h = dbread["p_zip"].ToString(); results.i = dbread["e_adrs1"].ToString(); results.j = dbread["e_adrs2"].ToString(); results.k = dbread["e_adrs3"].ToString(); results.l = dbread["e_city"].ToString(); results.m = dbread["e_state"].ToString(); results.n = dbread["e_zip"].ToString(); results.o = "????"; results.p = dbread["e_mail_code"].ToString(); results.q = dbread["e_phone_no"].ToString(); results.r = "????"; results.s = "????"; results.t = dbread["route"].ToString(); results.u = dbread["own_flag"].ToString(); results.v = dbread["tax_x"].ToString(); results.w = dbread["penalty_x"].ToString(); results.x = dbread["discon_x"].ToString(); results.y = dbread["user1"].ToString(); results.z = dbread["user2"].ToString(); results.aa = dbread["user3"].ToString(); results.bb = dbread["user4"].ToString(); results.cc = dbread["munic"].ToString(); results.dd = dbread["credit"].ToString(); results.ee = dbread["balance"].ToString(); results.ff = dbread["read_pd"].ToString(); results.gg = dbread["e_house_num"].ToString(); results.hh = dbread["e_street"].ToString(); results.ii = dbread["e_street_cd"].ToString(); results.jj = dbread["from_dt"].ToString(); results.kk = dbread["to_dt"].ToString(); results.ll = dbread["prop_owner"].ToString(); results.mm = dbread["po_adrs1"].ToString(); results.nn = dbread["po_adrs2"].ToString(); results.oo = dbread["po_city"].ToString(); results.pp = dbread["po_state"].ToString(); results.qq = dbread["po_zip"].ToString(); results.rr = dbread["parcel_id"].ToString(); } dbread.Close(); dbComm.Dispose(); dbConn.Close(); dbConn = null; return(results); }
protected void DoListCourse(string searchStr, bool isNewSearch, string filter) { // get Page int pg = 1; if ((!isNewSearch) && (Request["pg"] != null)) { pg = Int32.Parse(Request["pg"]); } if ((filter == null) || (filter.Length == 0) || ((!filter.Equals("all")) && (!filter.Equals("only_close")))) { filter = "only_open"; } string[,] bgclass = new string[, ] { { "class=\"spec\"", "class=\"td1\"" }, { "class=\"specalt\"", "class=\"alt\"" } }; listCourse = new List <Course>(); DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC); string qSearchSQL = Course.GetQSearchSQL(searchStr); //filter string curDate = "'" + DateTime.Now.ToString("yyyy-MM-dd", ci) + "'"; // "'2011-05-01'"; string filterSQL = ""; if (filter.Equals("only_open")) { filterSQL = "( end_date > " + curDate + " or course_type='คอร์ส DVD' )"; } else if (filter.Equals("only_close")) { filterSQL = "( end_date < " + curDate + " or course_type='คอร์ส DVD' )"; } if (filterSQL.Length > 0) { if (qSearchSQL.Trim().Length > 0) { qSearchSQL = qSearchSQL + " AND " + filterSQL; } else { qSearchSQL = filterSQL; } } // Add WHERE if (qSearchSQL.Trim().Length > 0) { qSearchSQL = " WHERE " + qSearchSQL; } int numRec = db.QueryCount("SELECT Count(*) FROM course c " + qSearchSQL); // add join condition qSearchSQL = qSearchSQL + ((qSearchSQL.Trim().Length > 0)?" AND ":" WHERE ") + " c.teacher_id=t.teacher_id"; String subQuery = " (SELECT count(*) FROM registration r WHERE r.status=0 AND r.course_id=c.course_id) "; OdbcDataReader reader = db.Query("SELECT c.*,t.firstname as teacher_firstname, t.surname as teacher_surname, " + subQuery + " AS num_registered " + " FROM course c, teacher t " + qSearchSQL + " order by bts_course_id desc LIMIT " + Config.TBRECORD_PER_PAGE + " OFFSET " + (((pg - 1) * Config.TBRECORD_PER_PAGE))); int i = 0; while (reader.Read()) { Course course = Course.CreateForm(reader); // byte[] raw = Encoding.Default.GetBytes(course._firstname); // course._firstname = Encoding.GetEncoding("tis-620").GetString(raw); string divtxt = Config.URL_PIC_COURSE + "/" + course._img; outBuf.Append("<tr>"); outBuf.Append("<th scope=\"row\" abbr=\"Model\" " + bgclass[i % 2, 0] + ">" + course._btsCourseID + (course._numRegistered >= course._seatLimit ? "<br><font color=red>[เต็ม]</font>":"") + "</th>"); outBuf.Append("<td " + bgclass[i % 2, 1] + " ><a href=\"CourseManage.aspx?actPage=view&targetID=" + course._courseID + "\" >" + "<b>" + course._courseName + "</b></a><br>" + course._courseDesc + "</td>"); outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + StringUtil.Int2StrComma(course._cost) + " </td>"); outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + course._startdate.ToString("dd/MM/yyyy", ci) + " - " + course._enddate.ToString("dd/MM/yyyy", ci) + "<br>" + course._dayOfWeek + " " + course._opentime + " </td>"); outBuf.Append("<td " + bgclass[i % 2, 1] + " >" + course._teacher._firstname + " " + course._teacher._surname + " </td>"); outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center> "); outBuf.Append("<a href=\"javascript:setVal('actPage','view');setVal('targetID','" + course._courseID + "');doSubmit()\"><img src=\"img/sys/view.gif\" border=0 alt=\"View detail\"></a> "); outBuf.Append("<a href=\"javascript:setVal('actPage','edit');setVal('targetID','" + course._courseID + "');doSubmit()\"><img src=\"img/sys/edit.gif\" border=0 alt=\"Edit\"></a> "); outBuf.Append("<a href=\"javascript:if (confirm('Delete this course?')) { setVal('actPage','delete');setVal('targetID','" + course._courseID + "');doSubmit(); }\"><img src=\"img/sys/delete.gif\" border=0 alt=\"Delete\"></a> "); outBuf.Append("</td>"); outBuf.Append("</tr>\n"); i++; } db.Close(); // calculate max page int maxpg = numRec / Config.TBRECORD_PER_PAGE; if (maxpg < 1) { maxpg = 1; } else if (numRec % Config.TBRECORD_PER_PAGE > 0) { maxpg++; } // Generate Page Navi HTML outBuf2.Append("<b>Page</b> "); outBuf2.Append(String.Format("<a href=\"CourseManage.aspx?pg={0}&qsearch={1}\">{2}</a><< ", "1", searchStr, "First")); for (i = pg - 5; i <= pg + 5; i++) { if ((i <= 0) || (i > maxpg)) { continue; } if (i == pg) { outBuf2.Append("<b>" + i + "</b> "); } else { outBuf2.Append(String.Format("<a href=\"CourseManage.aspx?pg={0}&qsearch={1}&filter={2}\">{0}</a> ", i.ToString(), searchStr, filter)); } } outBuf2.Append(String.Format(" >><a href=\"CourseManage.aspx?pg={0}&qsearch={1}\">{2}</a> ", maxpg.ToString(), searchStr, "Last")); // <a href="#">1</a> <b>2</b> <a href="#">3</a> <a href="#">4</a> }
/// <summary> /// /// select a set of rows from table t_RBSR_AUFW_u_ReconcDiffItem. /// </summary> /// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param> /// <param name="ReconcReportID"></param> /// <returns>returnListReconcDiffItemByReconcReport[]</returns> public returnListReconcDiffItemByReconcReport[] ListReconcDiffItemByReconcReport(int?maxRowsToReturn, int ReconcReportID) { returnListReconcDiffItemByReconcReport[] rv = null; DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0) { if (_dbConnection.Driver.ToLower().StartsWith("sql")) { cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_DiffType\", \"c_u_Comment\", \"c_u_Status\", \"c_u_AssignedUser\", \"c_r_ReconcReport\", \"c_u_DiffObject\", \"c_u_RoleName\", \"c_u_Detail\", \"c_u_Platform\" FROM \"t_RBSR_AUFW_u_ReconcDiffItem\" WHERE \"c_r_ReconcReport\"=?"; } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_DiffType\", \"c_u_Comment\", \"c_u_Status\", \"c_u_AssignedUser\", \"c_r_ReconcReport\", \"c_u_DiffObject\", \"c_u_RoleName\", \"c_u_Detail\", \"c_u_Platform\" FROM \"t_RBSR_AUFW_u_ReconcDiffItem\" WHERE \"c_r_ReconcReport\"=?" + " LIMIT " + maxRowsToReturn.Value; } } else { cmd.CommandText = "SELECT \"c_id\", \"c_u_DiffType\", \"c_u_Comment\", \"c_u_Status\", \"c_u_AssignedUser\", \"c_r_ReconcReport\", \"c_u_DiffObject\", \"c_u_RoleName\", \"c_u_Detail\", \"c_u_Platform\" FROM \"t_RBSR_AUFW_u_ReconcDiffItem\" WHERE \"c_r_ReconcReport\"=?"; } cmd.Parameters.Add("1_ReconcReportID", OdbcType.Int); cmd.Parameters["1_ReconcReportID"].Value = (object)ReconcReportID; OdbcDataReader dr = cmd.ExecuteReader(); List <returnListReconcDiffItemByReconcReport> rvl = new List <returnListReconcDiffItemByReconcReport>(); while (dr.Read()) { returnListReconcDiffItemByReconcReport cr = new returnListReconcDiffItemByReconcReport(); 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 'DiffType'"); } else { cr.DiffType = dr.GetString(1); } if (dr.IsDBNull(2)) { cr.Comment = null; } else { cr.Comment = dr.GetString(2); } if (dr.IsDBNull(3)) { cr.Status = null; } else { cr.Status = dr.GetString(3); } if (dr.IsDBNull(4)) { cr.AssignedUser = null; } else { cr.AssignedUser = dr.GetString(4); } if (dr.IsDBNull(5)) { throw new Exception("Value 'null' is not allowed for 'ReconcReportID'"); } else { cr.ReconcReportID = dr.GetInt32(5); } if (dr.IsDBNull(6)) { throw new Exception("Value 'null' is not allowed for 'DiffObject'"); } else { cr.DiffObject = dr.GetString(6); } if (dr.IsDBNull(7)) { cr.RoleName = null; } else { cr.RoleName = dr.GetString(7); } if (dr.IsDBNull(8)) { cr.Detail = null; } else { cr.Detail = dr.GetString(8); } if (dr.IsDBNull(9)) { cr.Platform = null; } else { cr.Platform = dr.GetString(9); } rvl.Add(cr); } dr.Close(); dr.Dispose(); rv = rvl.ToArray(); cmd.Dispose(); DBClose(); return(rv); }
// Carrega consultas realizadas nos últimos 6 meses public static DataTable gridCarregaAgendaPass(string _rh) { string sangue = ""; string urina = ""; string fezes = ""; string rx = ""; string ecg = ""; string outros = ""; using (OdbcConnection cnn = new OdbcConnection(ConfigurationManager.ConnectionStrings["HospubConn"].ToString())) { string dataPass = ""; int dtAtual = Convert.ToInt32(DateTime.Now.ToString("yyyyMM")); int dtAno = Convert.ToInt32(DateTime.Now.ToString("yyyy")); int dtMes = Convert.ToInt32(DateTime.Now.ToString("MM")); if (dtMes <= 6) { dtMes = dtMes + 6; dtAno = dtAno - 1; if (dtMes <= 3) { dataPass = Convert.ToString(dtAno) + "0" + Convert.ToString(dtMes); } else { dataPass = Convert.ToString(dtAno) + Convert.ToString(dtMes); } } else { dtMes = dtMes - 6; dataPass = Convert.ToString(dtAno) + "0" + Convert.ToString(dtMes); } OdbcCommand cmm = cnn.CreateCommand(); cmm.CommandText = "select am12121, am1212a, am1201a, am1201b, am1201c, am1204, am1203,am1210, am1208 from am12 where am12121 in(" + dataPass + "," + dtAtual + ") and am1206 = " + _rh + " Order By am12121 desc"; cnn.Open(); OdbcDataReader dr1 = cmm.ExecuteReader(); DataTable dt = new DataTable(); dt.Columns.Add("Data", System.Type.GetType("System.String")); dt.Columns.Add("Consulta", System.Type.GetType("System.String")); dt.Columns.Add("Especialidade", System.Type.GetType("System.String")); dt.Columns.Add("Profissional", System.Type.GetType("System.String")); dt.Columns.Add("Saída", System.Type.GetType("System.String")); dt.Columns.Add("Recursos", System.Type.GetType("System.String")); while (dr1.Read()) { string data = dr1.GetString(0); string ano = data.Substring(0, 4); string mes = data.Substring(4, 2); string dia = dr1.GetString(1).TrimEnd('.'); data = dia + "/" + mes + "/" + ano; string consulta = dr1.GetString(2) + dr1.GetString(3) + dr1.GetString(4); string espec = dr1.GetString(5); espec = Especialidade.getespec(espec); string profissional = Profissional.getNome(dr1.GetString(6)); string saida = dr1.GetString(7).TrimEnd('.'); if (saida.Equals("0")) { saida = "Faltou"; } else if (saida.Equals("1")) { saida = "Alta"; } else if (saida.Equals("4")) { saida = "Encam. Interno"; } else if (saida.Equals("5")) { saida = "Outros"; } else if (saida.Equals("6")) { saida = "Ret. Obrigatório"; } else if (saida.Equals("7")) { saida = "Retorno"; } string recursos = dr1.GetString(8); if (recursos != "") { sangue = recursos.Substring(1, 1); if (sangue.Equals("1")) { sangue = "Sangue"; } else { sangue = ""; } urina = recursos.Substring(2, 1); if (urina.Equals("1")) { urina = "Urina"; } else { urina = ""; } fezes = recursos.Substring(4, 1); if (fezes.Equals("1")) { fezes = "Fezes"; } else { fezes = ""; } outros = recursos.Substring(9, 1); if (outros.Equals("1")) { outros = "Outros"; } else { outros = ""; } ecg = recursos.Substring(10, 1); if (ecg.Equals("1")) { ecg = "ECG"; } else { ecg = ""; } rx = recursos.Substring(8, 1); if (rx.Equals("1")) { rx = "Raio-X"; } else { rx = ""; } } recursos = sangue + " " + urina + " " + fezes + " " + outros + " " + ecg + " " + rx; recursos = recursos.TrimStart(); recursos = recursos.TrimEnd(); dt.Rows.Add(new String[] { data, consulta, espec, profissional, saida, recursos }); } return(dt); } }
public static T ReadAsT <T>(OdbcDataReader dr) { return(JsonConvert.DeserializeObject <T>(ReadAsJson(dr))); }
public void ProcessRequest(HttpContext context) { if (context.Session["AFWACSESSION"] == null) { throw new Exception("Must be in a legal R-AF session"); } session = context.Session["AFWACSESSION"] as AFWACsession; string mode = context.Request.Params["mode"]; // mode=dumpraf // mode=compare string requestorUserEID = context.Request.Params["usereid"]; string requestorUserName = context.Request.Params["username"]; string pathInput = context.Request.Params["pathinput"]; bool boolReportInternalIdmRoles = false; bool boolSaveToHistory = bool.Parse(context.Request.Params["save"]); bool boolDoCompare = (mode == "compare"); bool boolCaseSens = false; string CSVlistOfInterestingRoles = "-3"; string CSVlistOfInterestingSubprocesses = "-3"; // // New format for the CSVlist: // Still comma-separated. // But now each item is either: // an integer // "SP/###" where ### is the ID number for a subprocess // Our goal is to split this into two CSV lists: // list of interesting roles // list of interesting subprocesses (meaning all its roles are interesting) // So here we must tokenize the incoming role list. StringTok.StringTokenizer TK = new StringTok.StringTokenizer(context.Request.Params["roles"], ","); string curnode; while (TK.HasMoreTokens()) { curnode = TK.NextToken(); if (curnode.StartsWith("SP/")) { curnode = curnode.Substring(3); CSVlistOfInterestingSubprocesses += ("," + curnode); } else { CSVlistOfInterestingRoles += ("," + curnode); } } int errcountEntitlements = 0; int errcountRoleMetadata = 0; DataTable dt_dumpFromSAP = null; if (boolDoCompare) { string pathTempFolder = Path.GetDirectoryName(pathInput); string pathTempFile = Path.GetFileName(pathInput); // Load the CSV file that shows the world as SAP claims it should be. dt_dumpFromSAP = HELPERS.LoadCsv(pathTempFolder, System.IO.Path.GetFileName(pathTempFile)); } OdbcCommand cmd = new OdbcCommand(); cmd.Connection = HELPERS.NewOdbcConn(); Workbook book = null; WorksheetRow row; Worksheet sheetMetadata = null; Worksheet sheetDeltas = null; if (boolSaveToHistory) { ENGINEreport = new IReconcReport(HELPERS.NewOdbcConn()); IDreport = ENGINEreport.NewReconcReport(DateTime.Now, session.idUser); ENGINEreport.SetReconcReport (IDreport, DateTime.Now, "Comparison via upload of file " + pathInput, session.idUser, "SAP"); ENGINEreportDiffItem = new IReconcDiffItem(HELPERS.NewOdbcConn()); } else if (boolDoCompare) { book = new Workbook(); context.Response.ContentType = "text/xml"; context.Response.AddHeader("Content-Disposition", "filename=RAFsapReconcile.xls;attachment"); sheetMetadata = book.Worksheets.Add("Metadata"); row = sheetMetadata.Table.Rows.Add(); row.Cells.Add("Run Date:"); row.Cells.Add(DateTime.Now.ToUniversalTime().ToLongDateString() + " UTC"); row = sheetMetadata.Table.Rows.Add(); row.Cells.Add("Run Time:"); row.Cells.Add(DateTime.Now.ToUniversalTime().ToLongTimeString() + " UTC"); // row = sheetMetadata.Table.Rows.Add(); // row.Cells.Add("User EID:"); // row.Cells.Add(requestorUserEID); row = sheetMetadata.Table.Rows.Add(); row.Cells.Add("User Name:"); row.Cells.Add(requestorUserName); row = sheetMetadata.Table.Rows.Add(); row = sheetMetadata.Table.Rows.Add(); row.Cells.Add("STATISTICS"); sheetDeltas = book.Worksheets.Add("Reconciliation"); row = RecordDelta(sheetDeltas, "Role Name", "Platform", "Difference", "Action", "Difference Type", ""); } else { // This is for mode==dumpraf (export entitlements only) context.Response.ContentType = "text/csv"; context.Response.AddHeader("Content-Disposition", "filename=RAF_SAPexport.csv;attachment"); } string extraconds = " AND ((TEASS.c_u_EditStatus & 4)<>4) "; // Perform a massive SQL select to obtain all active TCODE entitlements in R-AF string SQL = @" SELECT TENT.c_id as EntID, BROL.c_u_Name as SAProlename, RTRIM(LTRIM(BROL.c_u_Description)) as SAProleDescription, BROL.c_u_Platform as platform, UPPER(TENT.c_u_TCode) as tcode, BROL.c_u_System as sapsystem, SUBPR.c_u_Name as subprname FROM t_RBSR_AUFW_u_TcodeAssignment TEASS LEFT OUTER JOIN t_RBSR_AUFW_u_TcodeEntitlement TENT ON TEASS.c_r_TcodeEntitlement = TENT.c_id LEFT OUTER JOIN t_RBSR_AUFW_u_SAProle BROL ON BROL.c_id = TEASS.c_r_SAPRole LEFT OUTER JOIN t_RBSR_AUFW_u_TcodeAssignmentSet TEASET ON TEASET.c_id = TEASS.c_r_TcodeAssignmentSet LEFT OUTER JOIN t_RBSR_AUFW_u_SubProcess SUBPR ON TEASET.c_r_SubProcess = SUBPR.c_id WHERE (TEASET.c_u_Status = 'ACTIVE') AND (SUBPR.c_r_Process NOT IN (7)) AND ( (BROL.c_id IN (" + CSVlistOfInterestingRoles + ")) OR " + "(BROL.c_r_SubProcess IN (" + CSVlistOfInterestingSubprocesses + ")) ) " + extraconds + " ORDER BY TEASS.c_r_SAProle;"; cmd.CommandText = SQL; OdbcDataReader dr = cmd.ExecuteReader(); // Key: SAProlename + '\001' + platform + "\001" + tcodeautomappedtoUPPERCASE // Value: code meaning: // 1 means active in RAF but not (yet) seen in incoming data from SAP // This is really the only value ever seen. // If one of these is seen in the incoming data, this item is removed from the dict. Dictionary <string, int> DICTactiveRoleplatToTcode = new Dictionary <string, int>(); string keyDelimiter = "|=|"; // These map role names to role descriptions //Dictionary<string, string> DICTactiveBroles = new Dictionary<string, string>(); //Dictionary<string, string> DICTidmBroles = new Dictionary<string, string>(); while (dr.Read()) { int IDwsentrow = (int)(dr.GetValue(0)); string brolename = dr.GetValue(1).ToString(); string bdescr = dr.GetValue(2).ToString(); string platform = dr.GetValue(3).ToString(); string tcode = dr.GetValue(4).ToString().ToUpper(); string sapsystem = dr.GetValue(5).ToString(); string subpr = dr.GetValue(6).ToString(); string key = brolename + keyDelimiter + platform + keyDelimiter + tcode; if (!boolDoCompare) { context.Response.Write(CSVquoteize(brolename)); context.Response.Write(","); context.Response.Write(CSVquoteize(sapsystem)); context.Response.Write(","); context.Response.Write(CSVquoteize(platform)); context.Response.Write(","); context.Response.Write(CSVquoteize(tcode)); context.Response.Write(","); context.Response.Write(CSVquoteize(subpr)); context.Response.Write("\n"); } else { if (!DICTactiveRoleplatToTcode.ContainsKey(key)) { DICTactiveRoleplatToTcode.Add(key, 1); } } } // We have completed the reading of the output from the SELECT. // Now comes the comparison. if (boolDoCompare) { Queue <string> QUEUE_idmRowsLackingActiveMatch = new Queue <string>(); foreach (DataRow idmrow in dt_dumpFromSAP.Rows) { string idmrsrcRolename = idmrow[0].ToString().Trim(); string idmrsrcPlatform = idmrow[1].ToString().Trim(); string idmrsrcValue = idmrow[2].ToString().Trim(); string idmrsrcObjtype = idmrow[3].ToString().Trim().ToLower(); switch (idmrsrcObjtype) { case "tcode": idmrsrcValue = idmrsrcValue.ToUpper(); break; default: throw new Exception("Only TCode data is currently supported in the incoming data from SAP"); } string idmrsrcPrivForCompare = idmrsrcRolename + keyDelimiter + idmrsrcPlatform + keyDelimiter + idmrsrcValue; if (DICTactiveRoleplatToTcode.ContainsKey(idmrsrcPrivForCompare)) { DICTactiveRoleplatToTcode.Remove(idmrsrcPrivForCompare); } else { RecordDelta (sheetDeltas, idmrsrcRolename, idmrsrcPlatform, idmrsrcValue, "Remove", "tcode", null); errcountEntitlements++; } } // Now we are looking for info that was in RAF but unmatched in the incoming. // These turn into "ADD" instructions. foreach (string keytoadd in DICTactiveRoleplatToTcode.Keys) { StringTok.StringTokenizer TK2 = new StringTok.StringTokenizer(keytoadd, keyDelimiter); string reportRolename = TK2.NextToken(); string reportPlatform = TK2.NextToken(); string reportTcode = TK2.NextToken(); RecordDelta(sheetDeltas, reportRolename, reportPlatform, reportTcode, "Add", "tcode", null); errcountEntitlements++; } if (book != null) { row = sheetMetadata.Table.Rows.Add(); row.Cells.Add(" - Tcode deltas:"); row.Cells.Add(errcountEntitlements.ToString()); book.Save(context.Response.OutputStream); } else { context.Response.Redirect("../Page_Reconc_History.aspx"); } } }
private void button1_Click(object sender, EventArgs e) { if (txtUsuario.Text.Trim() == "" || txtClave.Text.Trim() == "") { MessageBox.Show("Faltan campos por llenar"); } else { OdbcCommand cmd; string sIdUsuario = ""; string sNivelPrivilegios = ""; try { cmd = new OdbcCommand("{ call procedimientoLogin (?,?)}", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@usuario", OdbcType.Text).Value = txtUsuario.Text.Trim(); cmd.Parameters.Add("@pass", OdbcType.Text).Value = txtClave.Text.Trim(); OdbcDataReader almacena = cmd.ExecuteReader(); if (almacena.HasRows) { while (almacena.Read()) { sIdUsuario = almacena.GetInt32(0).ToString(); sNivelPrivilegios = almacena.GetString(1).ToString(); } string sInsertar = "INSERT INTO tbl_bitacora_seguridad (PK_idUsuario, " + "accion, " + "fecha, " + "hora, " + "IP) " + "VALUES(" + sIdUsuario + "" + ",'Inicio Sesión en el sistema " + "','" + DateTime.Now.ToString("yyy/MM/dd") + "'" + ",'" + DateTime.Now.ToString("hh:mm:ss") + "'" + ",'" + myIP + "')"; OdbcCommand sqlInsertar = new OdbcCommand(sInsertar, con); sqlInsertar.ExecuteNonQuery(); sInsertar = "UPDATE tbl_usuario SET" + " ultima_sesion = '" + DateTime.Now.ToString("yyy/MM/dd hh:mm:ss") + "' WHERE PK_idUsuario =" + sIdUsuario; sqlInsertar = new OdbcCommand(sInsertar, con); sqlInsertar.ExecuteNonQuery(); this.Visible = false; MDI mdi = new MDI(con, sIdUsuario, sNivelPrivilegios); mdi.Show(); } else { MessageBox.Show("Nombre de usuario desconocido o contraseña incorrecta"); } almacena.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } }
/// <summary> /// /// select a row from table t_RBSR_AUFW_u_OrgValue1252. /// </summary> /// <param name="ID"></param> /// <returns>returnGetOrgValue1252</returns> public returnGetOrgValue1252 GetOrgValue1252(int ID) { returnGetOrgValue1252 rv = new returnGetOrgValue1252(); DBConnect(); OdbcCommand cmd = _dbConnection.CreateCommand(); cmd.CommandText = "select \"c_id\",\"c_u_FieldName\",\"c_u_RangeLow\",\"c_u_RangeHigh\",\"c_r_TcodeAssignmentSet\",\"c_r_SAProle\",\"c_u_EditStatus\" from \"t_RBSR_AUFW_u_OrgValue1252\" 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 'FieldName'"); } else { rv.FieldName = dr.GetString(1); } if (dr.IsDBNull(2)) { rv.RangeLow = null; } else { rv.RangeLow = dr.GetString(2); } if (dr.IsDBNull(3)) { rv.RangeHigh = null; } else { rv.RangeHigh = dr.GetString(3); } if (dr.IsDBNull(4)) { throw new Exception("Value 'null' is not allowed for 'TcodeAssignmentSetID'"); } else { rv.TcodeAssignmentSetID = dr.GetInt32(4); } if (dr.IsDBNull(5)) { throw new Exception("Value 'null' is not allowed for 'SAProleID'"); } else { rv.SAProleID = dr.GetInt32(5); } if (dr.IsDBNull(6)) { throw new Exception("Value 'null' is not allowed for 'EditStatus'"); } else { rv.EditStatus = dr.GetInt32(6); } } dr.Close(); dr.Dispose(); if (drctr != 1) { throw new Exception("Operation selected no rows!"); } cmd.Dispose(); DBClose(); return(rv); }