/// <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);
}
